- rework the "controlling DDL sequences" documentation to

refer mostly to the DDL object; this system is primarily useful
in that case, and not for built-in objects.  Reference that
the built-in case is not really viable right now. References #3442.

1 files changed, 124 insertions, 116 deletions

diff --git a/doc/build/core/ddl.rst b/doc/build/core/ddl.rst
index 0ba2f2806..820ba7b84 100644
--- a/doc/build/core/ddl.rst
+++ b/doc/build/core/ddl.rst
@@ -20,85 +20,100 @@ required, SQLAlchemy offers two techniques which can be used to add any DDL
 based on any condition, either accompanying the standard generation of tables
 or by itself.
 
-.. _schema_ddl_sequences:
-
-Controlling DDL Sequences
--------------------------
+Custom DDL
+----------
 
-The ``sqlalchemy.schema`` package contains SQL expression constructs that
-provide DDL expressions. For example, to produce a ``CREATE TABLE`` statement:
+Custom DDL phrases are most easily achieved using the
+:class:`~sqlalchemy.schema.DDL` construct. This construct works like all the
+other DDL elements except it accepts a string which is the text to be emitted:
 
 .. sourcecode:: python+sql
 
-    from sqlalchemy.schema import CreateTable
-    {sql}engine.execute(CreateTable(mytable))
-    CREATE TABLE mytable (
-        col1 INTEGER,
-        col2 INTEGER,
-        col3 INTEGER,
-        col4 INTEGER,
-        col5 INTEGER,
-        col6 INTEGER
-    ){stop}
+    event.listen(
+        metadata,
+        "after_create",
+        DDL("ALTER TABLE users ADD CONSTRAINT "
+            "cst_user_name_length "
+            " CHECK (length(user_name) >= 8)")
+    )
 
-Above, the :class:`~sqlalchemy.schema.CreateTable` construct works like any
-other expression construct (such as ``select()``, ``table.insert()``, etc.). A
-full reference of available constructs is in :ref:`schema_api_ddl`.
+A more comprehensive method of creating libraries of DDL constructs is to use
+custom compilation - see :ref:`sqlalchemy.ext.compiler_toplevel` for
+details.
 
-The DDL constructs all extend a common base class which provides the
-capability to be associated with an individual
-:class:`~sqlalchemy.schema.Table` or :class:`~sqlalchemy.schema.MetaData`
-object, to be invoked upon create/drop events. Consider the example of a table
-which contains a CHECK constraint:
 
-.. sourcecode:: python+sql
+.. _schema_ddl_sequences:
+
+Controlling DDL Sequences
+-------------------------
 
-    users = Table('users', metadata,
-                   Column('user_id', Integer, primary_key=True),
-                   Column('user_name', String(40), nullable=False),
-                   CheckConstraint('length(user_name) >= 8',name="cst_user_name_length")
-                   )
+The :class:`~.schema.DDL` construct introduced previously also has the
+ability to be invoked conditionally based on inspection of the
+database.  This feature is available using the :meth:`.DDLElement.execute_if`
+method.  For example, if we wanted to create a trigger but only on
+the Postgresql backend, we could invoke this as::
 
-    {sql}users.create(engine)
-    CREATE TABLE users (
-        user_id SERIAL NOT NULL,
-        user_name VARCHAR(40) NOT NULL,
-        PRIMARY KEY (user_id),
-        CONSTRAINT cst_user_name_length  CHECK (length(user_name) >= 8)
-    ){stop}
+    mytable = Table(
+        'mytable', metadata,
+        Column('id', Integer, primary_key=True),
+        Column('data', String(50))
+    )
 
-The above table contains a column "user_name" which is subject to a CHECK
-constraint that validates that the length of the string is at least eight
-characters. When a ``create()`` is issued for this table, DDL for the
-:class:`~sqlalchemy.schema.CheckConstraint` will also be issued inline within
-the table definition.
+    trigger = DDL(
+        "CREATE TRIGGER dt_ins BEFORE INSERT ON mytable "
+        "FOR EACH ROW BEGIN SET NEW.data='ins'; END"
+    )
 
-The :class:`~sqlalchemy.schema.CheckConstraint` construct can also be
-constructed externally and associated with the
-:class:`~sqlalchemy.schema.Table` afterwards::
+    event.listen(
+        mytable,
+        'after_create',
+        trigger.execute_if(dialect='postgresql')
+    )
+
+The :paramref:`.DDLElement.execute_if.dialect` keyword also accepts a tuple
+of string dialect names::
 
-    constraint = CheckConstraint('length(user_name) >= 8',name="cst_user_name_length")
-    users.append_constraint(constraint)
+    event.listen(
+        mytable,
+        "after_create",
+        trigger.execute_if(dialect=('postgresql', 'mysql'))
+    )
+    event.listen(
+        mytable,
+        "before_drop",
+        trigger.execute_if(dialect=('postgresql', 'mysql'))
+    )
 
-So far, the effect is the same. However, if we create DDL elements
-corresponding to the creation and removal of this constraint, and associate
-them with the :class:`.Table` as events, these new events
-will take over the job of issuing DDL for the constraint. Additionally, the
-constraint will be added via ALTER:
+The :meth:`.DDLElement.execute_if` method can also work against a callable
+function that will receive the database connection in use.  In the
+example below, we use this to conditionally create a CHECK constraint,
+first looking within the Postgresql catalogs to see if it exists:
 
 .. sourcecode:: python+sql
 
-    from sqlalchemy import event
+    def should_create(ddl, target, connection, **kw):
+        row = connection.execute(
+            "select conname from pg_constraint where conname='%s'" %
+            ddl.element.name).scalar()
+        return not bool(row)
+
+    def should_drop(ddl, target, connection, **kw):
+        return not should_create(ddl, target, connection, **kw)
 
     event.listen(
         users,
         "after_create",
-        AddConstraint(constraint)
+        DDL(
+            "ALTER TABLE users ADD CONSTRAINT "
+            "cst_user_name_length CHECK (length(user_name) >= 8)"
+        ).execute_if(callable_=should_create)
     )
     event.listen(
         users,
         "before_drop",
-        DropConstraint(constraint)
+        DDL(
+            "ALTER TABLE users DROP CONSTRAINT cst_user_name_length"
+        ).execute_if(callable_=should_drop)
     )
 
     {sql}users.create(engine)
@@ -108,61 +123,67 @@ constraint will be added via ALTER:
         PRIMARY KEY (user_id)
     )
 
+    select conname from pg_constraint where conname='cst_user_name_length'
     ALTER TABLE users ADD CONSTRAINT cst_user_name_length  CHECK (length(user_name) >= 8){stop}
 
     {sql}users.drop(engine)
+    select conname from pg_constraint where conname='cst_user_name_length'
     ALTER TABLE users DROP CONSTRAINT cst_user_name_length
     DROP TABLE users{stop}
 
-The real usefulness of the above becomes clearer once we illustrate the
-:meth:`.DDLElement.execute_if` method.  This method returns a modified form of
-the DDL callable which will filter on criteria before responding to a
-received event.   It accepts a parameter ``dialect``, which is the string
-name of a dialect or a tuple of such, which will limit the execution of the
-item to just those dialects.  It also accepts a ``callable_`` parameter which
-may reference a Python callable which will be invoked upon event reception,
-returning ``True`` or ``False`` indicating if the event should proceed.
-
-If our :class:`~sqlalchemy.schema.CheckConstraint` was only supported by
-Postgresql and not other databases, we could limit its usage to just that dialect::
+Using the built-in DDLElement Classes
+--------------------------------------
 
-    event.listen(
-        users,
-        'after_create',
-        AddConstraint(constraint).execute_if(dialect='postgresql')
-    )
-    event.listen(
-        users,
-        'before_drop',
-        DropConstraint(constraint).execute_if(dialect='postgresql')
-    )
+The ``sqlalchemy.schema`` package contains SQL expression constructs that
+provide DDL expressions. For example, to produce a ``CREATE TABLE`` statement:
 
-Or to any set of dialects::
+.. sourcecode:: python+sql
 
-    event.listen(
-        users,
-        "after_create",
-        AddConstraint(constraint).execute_if(dialect=('postgresql', 'mysql'))
-    )
-    event.listen(
-        users,
-        "before_drop",
-        DropConstraint(constraint).execute_if(dialect=('postgresql', 'mysql'))
-    )
+    from sqlalchemy.schema import CreateTable
+    {sql}engine.execute(CreateTable(mytable))
+    CREATE TABLE mytable (
+        col1 INTEGER,
+        col2 INTEGER,
+        col3 INTEGER,
+        col4 INTEGER,
+        col5 INTEGER,
+        col6 INTEGER
+    ){stop}
 
-When using a callable, the callable is passed the ddl element, the
-:class:`.Table` or :class:`.MetaData`
-object whose "create" or "drop" event is in progress, and the
-:class:`.Connection` object being used for the
-operation, as well as additional information as keyword arguments. The
-callable can perform checks, such as whether or not a given item already
-exists. Below we define ``should_create()`` and ``should_drop()`` callables
-that check for the presence of our named constraint:
+Above, the :class:`~sqlalchemy.schema.CreateTable` construct works like any
+other expression construct (such as ``select()``, ``table.insert()``, etc.).
+All of SQLAlchemy's DDL oriented constructs are subclasses of
+the :class:`.DDLElement` base class; this is the base of all the
+objects corresponding to CREATE and DROP as well as ALTER,
+not only in SQLAlchemy but in Alembic Migrations as well.
+A full reference of available constructs is in :ref:`schema_api_ddl`.
+
+User-defined DDL constructs may also be created as subclasses of
+:class:`.DDLElement` itself.   The documentation in
+:ref:`sqlalchemy.ext.compiler_toplevel` has several examples of this.
+
+The event-driven DDL system described in the previous section
+:ref:`schema_ddl_sequences` is available with other :class:`.DDLElement`
+objects as well.  However, when dealing with the built-in constructs
+such as :class:`.CreateIndex`, :class:`.CreateSequence`, etc, the event
+system is of **limited** use, as methods like :meth:`.Table.create` and
+:meth:`.MetaData.create_all` will invoke these constructs unconditionally.
+In a future SQLAlchemy release, the DDL event system including conditional
+execution will taken into account for built-in constructs that currently
+invoke in all cases.
+
+We can illustrate an event-driven
+example with the :class:`.AddConstraint` and :class:`.DropConstraint`
+constructs, as the event-driven system will work for CHECK and UNIQUE
+constraints, using these as we did in our previous example of
+:meth:`.DDLElement.execute_if`:
 
 .. sourcecode:: python+sql
 
     def should_create(ddl, target, connection, **kw):
-        row = connection.execute("select conname from pg_constraint where conname='%s'" % ddl.element.name).scalar()
+        row = connection.execute(
+            "select conname from pg_constraint where conname='%s'" %
+            ddl.element.name).scalar()
         return not bool(row)
 
     def should_drop(ddl, target, connection, **kw):
@@ -194,26 +215,12 @@ that check for the presence of our named constraint:
     ALTER TABLE users DROP CONSTRAINT cst_user_name_length
     DROP TABLE users{stop}
 
-Custom DDL
-----------
-
-Custom DDL phrases are most easily achieved using the
-:class:`~sqlalchemy.schema.DDL` construct. This construct works like all the
-other DDL elements except it accepts a string which is the text to be emitted:
-
-.. sourcecode:: python+sql
-
-    event.listen(
-        metadata,
-        "after_create",
-        DDL("ALTER TABLE users ADD CONSTRAINT "
-            "cst_user_name_length "
-            " CHECK (length(user_name) >= 8)")
-    )
-
-A more comprehensive method of creating libraries of DDL constructs is to use
-custom compilation - see :ref:`sqlalchemy.ext.compiler_toplevel` for
-details.
+While the above example is against the built-in :class:`.AddConstraint`
+and :class:`.DropConstraint` objects, the main usefulness of DDL events
+for now remains focused on the use of the :class:`.DDL` construct itself,
+as well as with user-defined subclasses of :class:`.DDLElement` that aren't
+already part of the :meth:`.MetaData.create_all`, :meth:`.Table.create`,
+and corresponding "drop" processes.
 
 .. _schema_api_ddl:
 
@@ -233,6 +240,7 @@ DDL Expression Constructs API
     :members:
     :undoc-members:
 
+.. autoclass:: _CreateDropBase
 
 .. autoclass:: CreateTable
     :members: