diff options
Diffstat (limited to 'doc/build/core/ddl.rst')
| -rw-r--r-- | doc/build/core/ddl.rst | 287 |
1 files changed, 287 insertions, 0 deletions
diff --git a/doc/build/core/ddl.rst b/doc/build/core/ddl.rst new file mode 100644 index 000000000..cee6f876e --- /dev/null +++ b/doc/build/core/ddl.rst @@ -0,0 +1,287 @@ +.. _metadata_ddl_toplevel: +.. _metadata_ddl: +.. module:: sqlalchemy.schema + +Customizing DDL +=============== + +In the preceding sections we've discussed a variety of schema constructs +including :class:`~sqlalchemy.schema.Table`, +:class:`~sqlalchemy.schema.ForeignKeyConstraint`, +:class:`~sqlalchemy.schema.CheckConstraint`, and +:class:`~sqlalchemy.schema.Sequence`. Throughout, we've relied upon the +``create()`` and :func:`~sqlalchemy.schema.MetaData.create_all` methods of +:class:`~sqlalchemy.schema.Table` and :class:`~sqlalchemy.schema.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. + +.. _schema_ddl_sequences: + +Controlling DDL Sequences +------------------------- + +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 + {sql}engine.execute(CreateTable(mytable)) + CREATE TABLE mytable ( + col1 INTEGER, + col2 INTEGER, + col3 INTEGER, + col4 INTEGER, + col5 INTEGER, + col6 INTEGER + ){stop} + +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`. + +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 + + 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 +:class:`~sqlalchemy.schema.CheckConstraint` will also be issued inline within +the table definition. + +The :class:`~sqlalchemy.schema.CheckConstraint` construct can also be +constructed externally and associated with the +:class:`~sqlalchemy.schema.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 :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: + +.. sourcecode:: python+sql + + from sqlalchemy import event + + event.listen( + users, + "after_create", + AddConstraint(constraint) + ) + event.listen( + users, + "before_drop", + DropConstraint(constraint) + ) + + {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 +: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:: + + event.listen( + users, + 'after_create', + AddConstraint(constraint).execute_if(dialect='postgresql') + ) + event.listen( + users, + 'before_drop', + DropConstraint(constraint).execute_if(dialect='postgresql') + ) + +Or to any set of dialects:: + + event.listen( + users, + "after_create", + AddConstraint(constraint).execute_if(dialect=('postgresql', 'mysql')) + ) + event.listen( + users, + "before_drop", + DropConstraint(constraint).execute_if(dialect=('postgresql', 'mysql')) + ) + +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: + +.. 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() + 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).execute_if(callable_=should_create) + ) + event.listen( + users, + "before_drop", + DropConstraint(constraint).execute_if(callable_=should_drop) + ) + + {sql}users.create(engine) + CREATE TABLE users ( + user_id SERIAL NOT NULL, + user_name VARCHAR(40) NOT NULL, + 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} + +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. + +.. _schema_api_ddl: + +DDL Expression Constructs API +----------------------------- + +.. autoclass:: DDLElement + :members: + :undoc-members: + + +.. autoclass:: DDL + :members: + :undoc-members: + + +.. autoclass:: CreateTable + :members: + :undoc-members: + + +.. autoclass:: DropTable + :members: + :undoc-members: + + +.. autoclass:: CreateColumn + :members: + :undoc-members: + + +.. autoclass:: CreateSequence + :members: + :undoc-members: + + +.. autoclass:: DropSequence + :members: + :undoc-members: + + +.. autoclass:: CreateIndex + :members: + :undoc-members: + + +.. autoclass:: DropIndex + :members: + :undoc-members: + + +.. autoclass:: AddConstraint + :members: + :undoc-members: + + +.. autoclass:: DropConstraint + :members: + :undoc-members: + + +.. autoclass:: CreateSchema + :members: + :undoc-members: + + +.. autoclass:: DropSchema + :members: + :undoc-members: + + |