From 5ca7fa3abdf3595c761038d437337b483ad6e4b7 Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Fri, 11 Sep 2009 22:37:13 +0000
Subject: [PATCH] all about DDL events

---
 doc/build/builder/builders.py |   4 +-
 doc/build/metadata.rst        | 137 ++++++++++++++++++++++++++++++----
 doc/build/ormtutorial.rst     |   6 +-
 lib/sqlalchemy/schema.py      |   5 +-
 test/engine/test_ddlevents.py |   2 +-
 5 files changed, 134 insertions(+), 20 deletions(-)

diff --git a/doc/build/builder/builders.py b/doc/build/builder/builders.py
index e604278a32..ddc706a592 100644
--- a/doc/build/builder/builders.py
+++ b/doc/build/builder/builders.py
@@ -62,7 +62,7 @@ class PyConWithSQLLexer(RegexLexer):
             ],
             'sqlpopup':[
                 (
-                    r'(.*?\n)((?:PRAGMA|BEGIN|SELECT|INSERT|DELETE|ROLLBACK|COMMIT|UPDATE|CREATE|DROP|PRAGMA|DESCRIBE).*?(?:{stop}\n*|$))',
+                    r'(.*?\n)((?:PRAGMA|BEGIN|SELECT|INSERT|DELETE|ROLLBACK|COMMIT|ALTER|UPDATE|CREATE|DROP|PRAGMA|DESCRIBE).*?(?:{stop}\n?|$))',
                     bygroups(using(PythonConsoleLexer), Token.Sql.Popup), 
                     "#pop"
                 )
@@ -91,7 +91,7 @@ class PythonWithSQLLexer(RegexLexer):
             ],
             'sqlpopup':[
                 (
-                    r'(.*?\n)((?:PRAGMA|BEGIN|SELECT|INSERT|DELETE|ROLLBACK|COMMIT|UPDATE|CREATE|DROP|PRAGMA|DESCRIBE).*?(?:{stop}\n*|$))',
+                    r'(.*?\n)((?:PRAGMA|BEGIN|SELECT|INSERT|DELETE|ROLLBACK|COMMIT|ALTER|UPDATE|CREATE|DROP|PRAGMA|DESCRIBE).*?(?:{stop}\n?|$))',
                     bygroups(using(PythonLexer), Token.Sql.Popup), 
                     "#pop"
                 )
diff --git a/doc/build/metadata.rst b/doc/build/metadata.rst
index 091736d56c..38309f08dd 100644
--- a/doc/build/metadata.rst
+++ b/doc/build/metadata.rst
@@ -616,6 +616,14 @@ Note that some databases do not actively support check constraints such as MySQL
         CheckConstraint('col2 > col3 + 5', name='check1')
         )
     
+    {sql}mytable.create(engine)
+    CREATE TABLE mytable (
+        col1 INTEGER  CHECK (col1>5), 
+        col2 INTEGER, 
+        col3 INTEGER, 
+        CONSTRAINT check1  CHECK (col2 > col3 + 5)
+    ){stop}
+    
 Indexes
 -------
 
@@ -623,6 +631,8 @@ Indexes can be created anonymously (using an auto-generated name ``ix_<column la
 
 Note that the ``Index`` construct is created **externally** to the table which it corresponds, using ``Column`` objects and not strings.
 
+Below we illustrate a ``Table`` with several ``Index`` objects associated.  The DDL for "CREATE INDEX" is issued right after the create statements for the table:
+
 .. sourcecode:: python+sql
 
     meta = MetaData()
@@ -646,34 +656,135 @@ Note that the ``Index`` construct is created **externally** to the table which i
     # place a unique index on col5, col6
     Index('myindex', mytable.c.col5, mytable.c.col6, unique=True)
 
-The ``Index`` objects will be created along with the CREATE statements for the table itself.  An index can also be created on its own independently of the table:
-
-.. sourcecode:: python+sql
-
-    # create a table
-    sometable.create()
+    {sql}mytable.create(engine)
+    CREATE TABLE mytable (
+        col1 INTEGER, 
+        col2 INTEGER, 
+        col3 INTEGER, 
+        col4 INTEGER, 
+        col5 INTEGER, 
+        col6 INTEGER
+    )
+    CREATE INDEX ix_mytable_col1 ON mytable (col1)
+    CREATE UNIQUE INDEX ix_mytable_col2 ON mytable (col2)
+    CREATE UNIQUE INDEX myindex ON mytable (col5, col6)
+    CREATE INDEX idx_col34 ON mytable (col3, col4){stop}
 
-    # define an index
-    i = Index('someindex', sometable.c.col5)
+The ``Index`` object also supports its own ``create()`` method:
 
-    # create the index, will use the table's bound connectable if the ``bind`` keyword argument not specified
-    i.create()
+.. sourcecode:: python+sql
 
+    i = Index('someindex', mytable.c.col5)
+    {sql}i.create(engine)
+    CREATE INDEX someindex ON mytable (col5){stop}
+    
 Customizing DDL
 ===============
 
-In the preceding sections we've discussed a variety of schema constructs including ``Table``, ``ForeignKeyConstraint``, ``CheckConstraint``, and ``Sequence``.   Throughout, we've relied upon the ``create()`` and ``create_all()`` methods of ``Table`` and ``MetaData`` in order to issue data definition language (DDL) for all constructs.   When issued, a pre-determined order of operations is invoked, and DDL to create each table is created unconditionally including all constraints and other objects associated with it.   For more complex scenarios where database-specific DDL is required, SQLAlchemy offers two techniques which can be used to add any DDL based on any condition along with the standard generation of tables.
+In the preceding sections we've discussed a variety of schema constructs including ``Table``, ``ForeignKeyConstraint``, ``CheckConstraint``, and ``Sequence``.   Throughout, we've relied upon the ``create()`` and ``create_all()`` methods of ``Table`` and ``MetaData`` in order to issue data definition language (DDL) for all constructs.   When issued, a pre-determined order of operations is invoked, and DDL to create each table is created unconditionally including all constraints and other objects associated with it.   For more complex scenarios where database-specific DDL is 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.
 
 Controlling DDL Sequences
 -------------------------
 
-The ``sqlalchemy.schema`` package contains SQL expression constructs that provide DDL expressions.   For example, to produce a ``CREATE TABLE`` statement::
+The ``sqlalchemy.schema`` package contains SQL expression constructs that provide DDL expressions.   For example, to produce a ``CREATE TABLE`` statement:
+
+.. sourcecode:: python+sql
 
     from sqlalchemy.schema import CreateTable
-    engine.execute(CreateTable(mytable))
+    {sql}engine.execute(CreateTable(mytable))
+    CREATE TABLE mytable (
+        col1 INTEGER, 
+        col2 INTEGER, 
+        col3 INTEGER, 
+        col4 INTEGER, 
+        col5 INTEGER, 
+        col6 INTEGER
+    ){stop}
     
 Above, the ``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`.
 
+The DDL constructs all extend a common base class which provides the capability to be associated with an individual ``Table`` or ``MetaData`` object, to be invoked upon create/drop events.   Consider the example of a table which contains a CHECK constraint:
+
+.. sourcecode:: python+sql
+
+    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")
+                   )
+
+    {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}
+
+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 ``CheckConstraint`` will also be issued inline within the table definition.
+
+The ``CheckConstraint`` construct can also be constructed externally and associated with the ``Table`` afterwards::
+
+    constraint = CheckConstraint('length(user_name) >= 8',name="cst_user_name_length")
+    users.append_constraint(constraint)
+    
+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 ``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:
+
+.. sourcecode:: python+sql
+
+    AddConstraint(constraint).execute_at("after-create", users)
+    DropConstraint(constraint).execute_at("before-drop", users)
+
+    {sql}users.create(engine)
+    CREATE TABLE users (
+        user_id SERIAL NOT NULL, 
+        user_name VARCHAR(40) NOT NULL, 
+        PRIMARY KEY (user_id)
+    )
+
+    ALTER TABLE users ADD CONSTRAINT cst_user_name_length  CHECK (length(user_name) >= 8){stop}
+
+    {sql}users.drop(engine)
+    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 ``on`` attribute of a DDL event.  The ``on`` parameter is part of the constructor, and may be a string name of a database dialect name, a tuple containing dialect names, or a Python callable.   This will limit the execution of the item to just those dialects, or when the return value of the callable is ``True``.  So if our ``CheckConstraint`` was only supported by Postgresql and not other databases, we could limit it to just that dialect::
+
+    AddConstraint(constraint, on='postgresql').execute_at("after-create", users)
+    DropConstraint(constraint, on='postgresql').execute_at("before-drop", users)
+    
+Or to any set of dialects::
+
+    AddConstraint(constraint, on=('postgresql', 'mysql')).execute_at("after-create", users)
+    DropConstraint(constraint, on=('postgresql', 'mysql')).execute_at("before-drop", users)
+    
+When using a callable, the callable is passed the event name, the schema object operated upon, and the ``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:
+
+.. sourcecode:: python+sql
+
+    def should_create(event, schema_item, connection, **kw):
+        row = connection.execute("select relname from pg_class where relname='%s'" % schema_item.name).scalar()
+        return bool(row)
+    
+    def should_drop(event, schema_item, connection, **kw):
+        return not should_create(event, schema_item, connection, **kw)
+        
+    AddConstraint(constraint, on=should_create).execute_at("after-create", users)
+    DropConstraint(constraint, on=should_drop).execute_at("before-drop", users)
+
+    {sql}users.create(engine)
+    CREATE TABLE users (
+        user_id SERIAL NOT NULL, 
+        user_name VARCHAR(40) NOT NULL, 
+        PRIMARY KEY (user_id)
+    )
+
+    select relname from pg_class where relname='users'
+    ALTER TABLE users ADD CONSTRAINT cst_user_name_length  CHECK (length(user_name) >= 8){stop}
+    
+    {sql}users.drop(engine)
+    select relname from pg_class where relname='users'
+    DROP TABLE users{stop}
 
 Custom DDL
 ----------
diff --git a/doc/build/ormtutorial.rst b/doc/build/ormtutorial.rst
index ee751f95a8..dd2711d204 100644
--- a/doc/build/ormtutorial.rst
+++ b/doc/build/ormtutorial.rst
@@ -372,7 +372,7 @@ You can control the names using the ``label()`` construct for scalar attributes
     ...    print row.user_alias, row.name_label
     SELECT users_1.id AS users_1_id, users_1.name AS users_1_name, users_1.fullname AS users_1_fullname, users_1.password AS users_1_password, users_1.name AS name_label
     FROM users AS users_1
-    []
+    []{stop}
     <User('ed','Ed Jones', 'f8s7ccs')> ed
     <User('wendy','Wendy Williams', 'foobar')> wendy
     <User('mary','Mary Contrary', 'xxg527')> mary
@@ -387,8 +387,8 @@ Basic operations with ``Query`` include issuing LIMIT and OFFSET, most convenien
     SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password
     FROM users ORDER BY users.id
     LIMIT 2 OFFSET 1
-    []
-    {stop}<User('wendy','Wendy Williams', 'foobar')>
+    []{stop}
+    <User('wendy','Wendy Williams', 'foobar')>
     <User('mary','Mary Contrary', 'xxg527')>
 
 and filtering results, which is accomplished either with ``filter_by()``, which uses keyword arguments:
diff --git a/lib/sqlalchemy/schema.py b/lib/sqlalchemy/schema.py
index 8a05765dbb..9305c4b197 100644
--- a/lib/sqlalchemy/schema.py
+++ b/lib/sqlalchemy/schema.py
@@ -37,7 +37,10 @@ __all__ = ['SchemaItem', 'Table', 'Column', 'ForeignKey', 'Sequence', 'Index',
            'ForeignKeyConstraint', 'PrimaryKeyConstraint', 'CheckConstraint',
            'UniqueConstraint', 'DefaultGenerator', 'Constraint', 'MetaData',
            'ThreadLocalMetaData', 'SchemaVisitor', 'PassiveDefault',
-           'DefaultClause', 'FetchedValue', 'ColumnDefault', 'DDL']
+           'DefaultClause', 'FetchedValue', 'ColumnDefault', 'DDL',
+           'CreateTable', 'DropTable', 'CreateSequence', 'DropSequence',
+           'AddConstraint', 'DropConstraint',
+           ]
 __all__.sort()
 
 class SchemaItem(visitors.Visitable):
diff --git a/test/engine/test_ddlevents.py b/test/engine/test_ddlevents.py
index 434a5d873c..6fe170a23f 100644
--- a/test/engine/test_ddlevents.py
+++ b/test/engine/test_ddlevents.py
@@ -248,7 +248,7 @@ class DDLExecutionTest(TestBase):
         
         metadata.create_all(bind=nonpg_mock)
         strings = " ".join(str(x) for x in nonpg_mock.mock)
-        assert "my_test_constraint" not in strings
+        assert "my_test_constraint" not in strings  
         metadata.drop_all(bind=nonpg_mock)
         strings = " ".join(str(x) for x in nonpg_mock.mock)
         assert "my_test_constraint" not in strings
-- 
2.47.2