- add new recipe for "replaceable objects".

references #173, references #119

1 files changed, 513 insertions, 0 deletions

diff --git a/docs/build/cookbook.rst b/docs/build/cookbook.rst
index 541f595..42762e0 100644
--- a/docs/build/cookbook.rst
+++ b/docs/build/cookbook.rst
@@ -261,3 +261,516 @@ Then in ``env.py``::
 
 .. versionadded:: 0.7.5 Added :attr:`.Config.attributes`.
 
+.. _replaceable_objects:
+
+Replaceable Objects
+===================
+
+This recipe proposes a hypothetical way of dealing with
+what we might call a *replaceable* schema object.  A replaceable object
+is a schema object that needs to be created and dropped all at once.
+Examples of such objects include views, stored procedures, and triggers.
+
+Replaceable objects present a problem in that in order to make incremental
+changes to them, we have to refer to the whole definition at once.
+If we need to add a new column to a view, for example, we have to drop
+it entirely and recreate it fresh with the extra column added, referring to
+the whole structure; but to make it even tougher, if we wish to support
+downgrade operarations in our migration scripts,
+we need to refer to the *previous* version of that
+construct fully, and we'd much rather not have to type out the whole
+definition in multiple places.
+
+This recipe proposes that we may refer to the older version of a
+replaceable construct by directly naming the migration version in
+which it was created, and having a migration refer to that previous
+file as migrations run.   We will also demonstrate how to integrate this
+logic within the :ref:`operation_plugins` feature introduced in
+Alembic 0.8.  It may be very helpful to review
+this section first to get an overview of this API.
+
+The Replaceable Object Structure
+--------------------------------
+
+We first need to devise a simple format that represents the "CREATE XYZ" /
+"DROP XYZ" aspect of what it is we're building.  We will work with an object
+that represents a textual definition; while a SQL view is an object that we can define
+using a `table-metadata-like system <https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/Views>`_,
+this is not so much the case for things like stored procedures, where
+we pretty much need to have a full string definition written down somewhere.
+We'll use a simple value object called ``ReplaceableObject`` that can
+represent any named set of SQL text to send to a "CREATE" statement of
+some kind::
+
+    class ReplaceableObject(object):
+        def __init__(self, name, sqltext):
+            self.name = name
+            self.sqltext = sqltext
+
+Using this object in a migration script, assuming a Postgresql-style
+syntax, looks like::
+
+    customer_view = ReplaceableObject(
+        "customer_view",
+        "SELECT name, order_count FROM customer WHERE order_count > 0"
+    )
+
+    add_customer_sp = ReplaceableObject(
+        "add_customer_sp(name varchar, order_count integer)",
+        """
+        RETURNS integer AS $$
+        BEGIN
+            insert into customer (name, order_count)
+            VALUES (in_name, in_order_count);
+        END;
+        $$ LANGUAGE plpgsql;
+        """
+    )
+
+The ``ReplaceableObject`` class is only one very simplistic way to do this.
+The structure of how we represent our schema objects
+is not too important for the purposes of this example; we can just
+as well put strings inside of tuples or dictionaries, as well as
+that we could define any kind of series of fields and class structures we want.
+The only important part is that below we will illustrate how organize the
+code that can consume the structure we create here.
+
+Create Operations for the Target Objects
+----------------------------------------
+
+We'll use the :class:`.Operations` extension API to make new operations
+for create, drop, and replace of views and stored procedures.  Using this
+API is also optional; we can just as well make any kind of Python
+function that we would invoke from our migration scripts.
+However, using this API gives us operations
+built directly into the Alembic ``op.*`` namespace very nicely.
+
+The most intricate class is below.  This is the base of our "replaceable"
+operation, which includes not just a base operation for emitting
+CREATE and DROP instructions on a ``ReplaceableObject``, it also assumes
+a certain model of "reversibility" which makes use of references to
+other migration files in order to refer to the "previous" version
+of an object::
+
+    from alembic.operations import Operations, MigrateOperation
+
+    class ReversibleOp(MigrateOperation):
+        def __init__(self, target):
+            self.target = target
+
+        @classmethod
+        def invoke_for_target(cls, operations, target):
+            op = cls(target)
+            return operations.invoke(op)
+
+        def reverse(self):
+            raise NotImplementedError()
+
+        @classmethod
+        def _get_object_from_version(cls, operations, ident):
+            version, objname = ident.split(".")
+
+            module = operations.get_context().script.get_revision(version).module
+            obj = getattr(module, objname)
+            return obj
+
+        @classmethod
+        def replace(cls, operations, target, replaces=None, replace_with=None):
+
+            if replaces:
+                old_obj = cls._get_object_from_version(operations, replaces)
+                drop_old = cls(old_obj).reverse()
+                create_new = cls(target)
+            elif replace_with:
+                old_obj = cls._get_object_from_version(operations, replace_with)
+                drop_old = cls(target).reverse()
+                create_new = cls(old_obj)
+            else:
+                raise TypeError("replaces or replace_with is required")
+
+            operations.invoke(drop_old)
+            operations.invoke(create_new)
+
+The workings of this class should become clear as we walk through the
+example.   To create usable operations from this base, we will build
+a series of stub classes and use :meth:`.Operations.register_operation`
+to make them part of the ``op.*`` namespace::
+
+    @Operations.register_operation("create_view", "invoke_for_target")
+    @Operations.register_operation("replace_view", "replace")
+    class CreateViewOp(ReversibleOp):
+        def reverse(self):
+            return DropViewOp(self.target)
+
+
+    @Operations.register_operation("drop_view", "invoke_for_target")
+    class DropViewOp(ReversibleOp):
+        def reverse(self):
+            return CreateViewOp(self.view)
+
+
+    @Operations.register_operation("create_sp", "invoke_for_target")
+    @Operations.register_operation("replace_sp", "replace")
+    class CreateSPOp(ReversibleOp):
+        def reverse(self):
+            return DropSPOp(self.target)
+
+
+    @Operations.register_operation("drop_sp", "invoke_for_target")
+    class DropSPOp(ReversibleOp):
+        def reverse(self):
+            return CreateSPOp(self.target)
+
+To actually run the SQL like "CREATE VIEW" and "DROP SEQUENCE", we'll provide
+implementations using :meth:`.Operations.implementation_for`
+that run straight into :meth:`.Operations.execute`::
+
+    @Operations.implementation_for(CreateViewOp)
+    def create_view(operations, operation):
+        operations.execute("CREATE VIEW %s AS %s" % (
+            operation.target.name,
+            operation.target.sqltext
+        ))
+
+
+    @Operations.implementation_for(DropViewOp)
+    def drop_view(operations, operation):
+        operations.execute("DROP VIEW %s" % operation.target.name)
+
+
+    @Operations.implementation_for(CreateSPOp)
+    def create_sp(operations, operation):
+        operations.execute(
+            "CREATE FUNCTION %s %s" % (
+                operation.target.name, operation.target.sqltext
+            )
+        )
+
+
+    @Operations.implementation_for(DropSPOp)
+    def drop_sp(operations, operation):
+        operations.execute("DROP FUNCTION %s" % operation.target.name)
+
+All of the above code can be present anywhere within an application's
+source tree; the only requirement is that when the ``env.py`` script is
+invoked, it includes imports that ultimately call upon these classes
+as well as the :meth:`.Operations.register_operation` and
+:meth:`.Operations.implementation_for` sequences.
+
+Create Initial Migrations
+-------------------------
+
+We can now illustrate how these objects look during use.  For the first step,
+we'll create a new migration to create a "customer" table::
+
+    $ alembic revision -m "create table"
+
+We build the first revision as follows::
+
+    """create table
+
+    Revision ID: 3ab8b2dfb055
+    Revises:
+    Create Date: 2015-07-27 16:22:44.918507
+
+    """
+
+    # revision identifiers, used by Alembic.
+    revision = '3ab8b2dfb055'
+    down_revision = None
+    branch_labels = None
+    depends_on = None
+
+    from alembic import op
+    import sqlalchemy as sa
+
+
+    def upgrade():
+        op.create_table(
+            "customer",
+            sa.Column('id', sa.Integer, primary_key=True),
+            sa.Column('name', sa.String),
+            sa.Column('order_count', sa.Integer),
+        )
+
+
+    def downgrade():
+        op.drop_table('customer')
+
+For the second migration, we will create a view and a stored procedure
+which act upon this table::
+
+    $ alembic revision -m "create views/sp"
+
+This migration will use the new directives::
+
+    """create views/sp
+
+    Revision ID: 28af9800143f
+    Revises: 3ab8b2dfb055
+    Create Date: 2015-07-27 16:24:03.589867
+
+    """
+
+    # revision identifiers, used by Alembic.
+    revision = '28af9800143f'
+    down_revision = '3ab8b2dfb055'
+    branch_labels = None
+    depends_on = None
+
+    from alembic import op
+    import sqlalchemy as sa
+
+    from foo import ReplaceableObject
+
+    customer_view = ReplaceableObject(
+        "customer_view",
+        "SELECT name, order_count FROM customer WHERE order_count > 0"
+    )
+
+    add_customer_sp = ReplaceableObject(
+        "add_customer_sp(name varchar, order_count integer)",
+        """
+        RETURNS integer AS $$
+        BEGIN
+            insert into customer (name, order_count)
+            VALUES (in_name, in_order_count);
+        END;
+        $$ LANGUAGE plpgsql;
+        """
+    )
+
+
+    def upgrade():
+        op.create_view(customer_view)
+        op.create_sp(add_customer_sp)
+
+
+    def downgrade():
+        op.drop_view(customer_view)
+        op.drop_sp(add_customer_sp)
+
+
+We see the use of our new ``create_view()``, ``create_sp()``,
+``drop_view()``, and ``drop_sp()`` directives.  Running these to "head"
+we get the following (this includes an edited view of SQL emitted)::
+
+    $ alembic upgrade 28af9800143
+    INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
+    INFO  [alembic.runtime.migration] Will assume transactional DDL.
+    INFO  [sqlalchemy.engine.base.Engine] BEGIN (implicit)
+    INFO  [sqlalchemy.engine.base.Engine] select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
+    INFO  [sqlalchemy.engine.base.Engine] {'name': u'alembic_version'}
+    INFO  [sqlalchemy.engine.base.Engine] SELECT alembic_version.version_num
+    FROM alembic_version
+    INFO  [sqlalchemy.engine.base.Engine] {}
+    INFO  [sqlalchemy.engine.base.Engine] select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
+    INFO  [sqlalchemy.engine.base.Engine] {'name': u'alembic_version'}
+    INFO  [alembic.runtime.migration] Running upgrade  -> 3ab8b2dfb055, create table
+    INFO  [sqlalchemy.engine.base.Engine]
+    CREATE TABLE customer (
+        id SERIAL NOT NULL,
+        name VARCHAR,
+        order_count INTEGER,
+        PRIMARY KEY (id)
+    )
+
+
+    INFO  [sqlalchemy.engine.base.Engine] {}
+    INFO  [sqlalchemy.engine.base.Engine] INSERT INTO alembic_version (version_num) VALUES ('3ab8b2dfb055')
+    INFO  [sqlalchemy.engine.base.Engine] {}
+    INFO  [alembic.runtime.migration] Running upgrade 3ab8b2dfb055 -> 28af9800143f, create views/sp
+    INFO  [sqlalchemy.engine.base.Engine] CREATE VIEW customer_view AS SELECT name, order_count FROM customer WHERE order_count > 0
+    INFO  [sqlalchemy.engine.base.Engine] {}
+    INFO  [sqlalchemy.engine.base.Engine] CREATE FUNCTION add_customer_sp(name varchar, order_count integer)
+        RETURNS integer AS $$
+        BEGIN
+            insert into customer (name, order_count)
+            VALUES (in_name, in_order_count);
+        END;
+        $$ LANGUAGE plpgsql;
+
+    INFO  [sqlalchemy.engine.base.Engine] {}
+    INFO  [sqlalchemy.engine.base.Engine] UPDATE alembic_version SET version_num='28af9800143f' WHERE alembic_version.version_num = '3ab8b2dfb055'
+    INFO  [sqlalchemy.engine.base.Engine] {}
+    INFO  [sqlalchemy.engine.base.Engine] COMMIT
+
+We see that our CREATE TABLE proceeded as well as the CREATE VIEW and CREATE
+FUNCTION operations produced by our new directives.
+
+
+Create Revision Migrations
+--------------------------
+
+Finally, we can illustrate how we would "revise" these objects.
+Let's consider we added a new column ``email`` to our ``customer`` table::
+
+    $ alembic revision -m "add email col"
+
+The migration is::
+
+    """add email col
+
+    Revision ID: 191a2d20b025
+    Revises: 28af9800143f
+    Create Date: 2015-07-27 16:25:59.277326
+
+    """
+
+    # revision identifiers, used by Alembic.
+    revision = '191a2d20b025'
+    down_revision = '28af9800143f'
+    branch_labels = None
+    depends_on = None
+
+    from alembic import op
+    import sqlalchemy as sa
+
+
+    def upgrade():
+        op.add_column("customer", sa.Column("email", sa.String()))
+
+
+    def downgrade():
+        op.drop_column("customer", "email")
+
+
+We now need to recreate the ``customer_view`` view and the
+``add_customer_sp`` function.   To include downgrade capability, we will
+need to refer to the **previous** version of the construct; the
+``replace_view()`` and ``replace_sp()`` operations we've created make
+this possible, by allowing us to refer to a specific, previous revision.
+the ``replaces`` and ``replace_with`` arguments accept a dot-separated
+string, which refers to a revision number and an object name, such
+as ``"28af9800143f.customer_view"``.  The ``ReversibleOp`` class makes use
+of the :meth:`.Operations.get_context` method to locate the version file
+we refer to::
+
+    $ alembic revision -m "update views/sp"
+
+The migration::
+
+    """update views/sp
+
+    Revision ID: 199028bf9856
+    Revises: 191a2d20b025
+    Create Date: 2015-07-27 16:26:31.344504
+
+    """
+
+    # revision identifiers, used by Alembic.
+    revision = '199028bf9856'
+    down_revision = '191a2d20b025'
+    branch_labels = None
+    depends_on = None
+
+    from alembic import op
+    import sqlalchemy as sa
+
+    from foo import ReplaceableObject
+
+    customer_view = ReplaceableObject(
+        "customer_view",
+        "SELECT name, order_count, email "
+        "FROM customer WHERE order_count > 0"
+    )
+
+    add_customer_sp = ReplaceableObject(
+        "add_customer_sp(name varchar, order_count integer, email varchar)",
+        """
+        RETURNS integer AS $$
+        BEGIN
+            insert into customer (name, order_count, email)
+            VALUES (in_name, in_order_count, email);
+        END;
+        $$ LANGUAGE plpgsql;
+        """
+    )
+
+
+    def upgrade():
+        op.replace_view(customer_view, replaces="28af9800143f.customer_view")
+        op.replace_sp(add_customer_sp, replaces="28af9800143f.add_customer_sp")
+
+
+    def downgrade():
+        op.replace_view(customer_view, replace_with="28af9800143f.customer_view")
+        op.replace_sp(add_customer_sp, replace_with="28af9800143f.add_customer_sp")
+
+Above, instead of using ``create_view()``, ``create_sp()``,
+``drop_view()``, and ``drop_sp()`` methods, we now use ``replace_view()`` and
+``replace_sp()``.  The replace operation we've built always runs a DROP *and*
+a CREATE.  Running an upgrade to head we see::
+
+    $ alembic upgrade head
+    INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
+    INFO  [alembic.runtime.migration] Will assume transactional DDL.
+    INFO  [sqlalchemy.engine.base.Engine] BEGIN (implicit)
+    INFO  [sqlalchemy.engine.base.Engine] select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
+    INFO  [sqlalchemy.engine.base.Engine] {'name': u'alembic_version'}
+    INFO  [sqlalchemy.engine.base.Engine] SELECT alembic_version.version_num
+    FROM alembic_version
+    INFO  [sqlalchemy.engine.base.Engine] {}
+    INFO  [alembic.runtime.migration] Running upgrade 28af9800143f -> 191a2d20b025, add email col
+    INFO  [sqlalchemy.engine.base.Engine] ALTER TABLE customer ADD COLUMN email VARCHAR
+    INFO  [sqlalchemy.engine.base.Engine] {}
+    INFO  [sqlalchemy.engine.base.Engine] UPDATE alembic_version SET version_num='191a2d20b025' WHERE alembic_version.version_num = '28af9800143f'
+    INFO  [sqlalchemy.engine.base.Engine] {}
+    INFO  [alembic.runtime.migration] Running upgrade 191a2d20b025 -> 199028bf9856, update views/sp
+    INFO  [sqlalchemy.engine.base.Engine] DROP VIEW customer_view
+    INFO  [sqlalchemy.engine.base.Engine] {}
+    INFO  [sqlalchemy.engine.base.Engine] CREATE VIEW customer_view AS SELECT name, order_count, email FROM customer WHERE order_count > 0
+    INFO  [sqlalchemy.engine.base.Engine] {}
+    INFO  [sqlalchemy.engine.base.Engine] DROP FUNCTION add_customer_sp(name varchar, order_count integer)
+    INFO  [sqlalchemy.engine.base.Engine] {}
+    INFO  [sqlalchemy.engine.base.Engine] CREATE FUNCTION add_customer_sp(name varchar, order_count integer, email varchar)
+        RETURNS integer AS $$
+        BEGIN
+            insert into customer (name, order_count, email)
+            VALUES (in_name, in_order_count, email);
+        END;
+        $$ LANGUAGE plpgsql;
+
+    INFO  [sqlalchemy.engine.base.Engine] {}
+    INFO  [sqlalchemy.engine.base.Engine] UPDATE alembic_version SET version_num='199028bf9856' WHERE alembic_version.version_num = '191a2d20b025'
+    INFO  [sqlalchemy.engine.base.Engine] {}
+    INFO  [sqlalchemy.engine.base.Engine] COMMIT
+
+After adding our new ``email`` column, we see that both ``customer_view``
+and ``add_customer_sp()`` are dropped before the new version is created.
+If we downgrade back to the old version, we see the old version of these
+recreated again within the downgrade for this migration::
+
+    $ alembic downgrade 28af9800143
+    INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
+    INFO  [alembic.runtime.migration] Will assume transactional DDL.
+    INFO  [sqlalchemy.engine.base.Engine] BEGIN (implicit)
+    INFO  [sqlalchemy.engine.base.Engine] select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
+    INFO  [sqlalchemy.engine.base.Engine] {'name': u'alembic_version'}
+    INFO  [sqlalchemy.engine.base.Engine] SELECT alembic_version.version_num
+    FROM alembic_version
+    INFO  [sqlalchemy.engine.base.Engine] {}
+    INFO  [alembic.runtime.migration] Running downgrade 199028bf9856 -> 191a2d20b025, update views/sp
+    INFO  [sqlalchemy.engine.base.Engine] DROP VIEW customer_view
+    INFO  [sqlalchemy.engine.base.Engine] {}
+    INFO  [sqlalchemy.engine.base.Engine] CREATE VIEW customer_view AS SELECT name, order_count FROM customer WHERE order_count > 0
+    INFO  [sqlalchemy.engine.base.Engine] {}
+    INFO  [sqlalchemy.engine.base.Engine] DROP FUNCTION add_customer_sp(name varchar, order_count integer, email varchar)
+    INFO  [sqlalchemy.engine.base.Engine] {}
+    INFO  [sqlalchemy.engine.base.Engine] CREATE FUNCTION add_customer_sp(name varchar, order_count integer)
+        RETURNS integer AS $$
+        BEGIN
+            insert into customer (name, order_count)
+            VALUES (in_name, in_order_count);
+        END;
+        $$ LANGUAGE plpgsql;
+
+    INFO  [sqlalchemy.engine.base.Engine] {}
+    INFO  [sqlalchemy.engine.base.Engine] UPDATE alembic_version SET version_num='191a2d20b025' WHERE alembic_version.version_num = '199028bf9856'
+    INFO  [sqlalchemy.engine.base.Engine] {}
+    INFO  [alembic.runtime.migration] Running downgrade 191a2d20b025 -> 28af9800143f, add email col
+    INFO  [sqlalchemy.engine.base.Engine] ALTER TABLE customer DROP COLUMN email
+    INFO  [sqlalchemy.engine.base.Engine] {}
+    INFO  [sqlalchemy.engine.base.Engine] UPDATE alembic_version SET version_num='28af9800143f' WHERE alembic_version.version_num = '191a2d20b025'
+    INFO  [sqlalchemy.engine.base.Engine] {}
+    INFO  [sqlalchemy.engine.base.Engine] COMMIT