1 files changed, 641 insertions, 0 deletions
diff --git a/doc/source/versioning.rst b/doc/source/versioning.rst
new file mode 100644
index 0000000..dd413d3
--- /dev/null
+++ b/doc/source/versioning.rst
@@ -0,0 +1,641 @@
+.. _versioning-system:
+.. currentmodule:: migrate.versioning
+.. highlight:: console
+
+***********************************
+Database schema versioning workflow
+***********************************
+
+SQLAlchemy migrate provides the :mod:`migrate.versioning` API that is
+also available as the :ref:`migrate <command-line-usage>` command.
+
+Purpose of this package is frontend for migrations. It provides commands to
+manage migrate :term:`repository` and database selection as well as script
+versioning.
+
+
+Project setup
+=============
+
+.. _create_change_repository:
+
+Create a change repository
+--------------------------
+
+To begin, we'll need to create a :term:`repository` for our project.
+
+All work with repositories is done using the :ref:`migrate
+<command-line-usage>` command. Let's create our project's repository::
+
+ $ migrate create my_repository "Example project"
+
+This creates an initially empty :term:`repository` relative to current
+directory at :file:`my_repository/` named `Example project`.
+
+The :term:`repository` directory contains a sub directory :file:`versions` that
+will store the :ref:`schema versions <changeset-system>`, a configuration file
+:file:`migrate.cfg` that contains :ref:`repository configuration
+<repository_configuration>` and a script :ref:`manage.py
+<project_management_script>` that has the same functionality as the
+:ref:`migrate <command-line-usage>` command but is preconfigured with
+repository specific parameters.
+
+.. note::
+
+    Repositories are associated with a single database schema, and store
+    collections of change scripts to manage that schema. The scripts in a
+    :term:`repository` may be applied to any number of databases. Each
+    :term:`repository` has an unique name. This name is used to identify the
+    :term:`repository` we're working with.
+
+
+Version control a database
+--------------------------
+
+Next we need to declare database to be under version control. Information on a
+database's version is stored in the database itself; declaring a database to be
+under version control creates a table named **migrate_version** and associates
+it with your :term:`repository`.
+
+The database is specified as a `SQLAlchemy database url`_.
+
+.. _`sqlalchemy database url`:
+  http://www.sqlalchemy.org/docs/core/engines.html#database-urls
+
+The :option:`version_control` command assigns a specified database with a
+:term:`repository`::
+
+ $ python my_repository/manage.py version_control sqlite:///project.db my_repository
+
+We can have any number of databases under this :term:`repository's
+<repository>` version control.
+
+Each schema has a :term:`version` that SQLAlchemy Migrate manages. Each change
+script applied to the database increments this version number. You can retrieve
+a database's current :term:`version`::
+
+ $ python my_repository/manage.py db_version sqlite:///project.db my_repository
+ 0 
+
+A freshly versioned database begins at version 0 by default. This assumes the
+database is empty or does only contain schema elements (tables, views,
+constraints, indices, ...) that will not be affected by the changes in the
+:term:`repository`. (If this is a bad assumption, you can specify the
+:term:`version` at the time the database is put under version control, with the
+:option:`version_control` command.) We'll see that creating and applying change
+scripts changes the database's :term:`version` number.
+
+Similarly, we can also see the latest :term:`version` available in a
+:term:`repository` with the command::
+
+ $ python my_repository/manage.py version my_repository
+ 0
+
+We've entered no changes so far, so our :term:`repository` cannot upgrade a
+database past version 0.
+
+Project management script
+-------------------------
+
+.. _project_management_script:
+
+Many commands need to know our project's database url and :term:`repository`
+path - typing them each time is tedious. We can create a script for our project
+that remembers the database and :term:`repository` we're using, and use it to
+perform commands::
+
+ $ migrate manage manage.py --repository=my_repository --url=sqlite:///project.db
+ $ python manage.py db_version
+ 0
+
+The script :file:`manage.py` was created. All commands we perform with it are
+the same as those performed with the :ref:`migrate <command-line-usage>` tool,
+using the :term:`repository` and database connection entered above. The
+difference between the script :file:`manage.py` in the current directory and
+the script inside the repository is, that the one in the current directory has
+the database URL preconfigured.
+
+.. note::
+
+   Parameters specified in manage.py should be the same as in :ref:`versioning
+   api <versioning-api>`. Preconfigured parameter should just be omitted from
+   :ref:`migrate <command-line-usage>` command.
+
+
+Making schema changes
+=====================
+
+All changes to a database schema under version control should be done via
+change scripts - you should avoid schema modifications (creating tables, etc.)
+outside of change scripts. This allows you to determine what the schema looks
+like based on the version number alone, and helps ensure multiple databases
+you're working with are consistent.
+
+Create a change script
+----------------------
+
+Our first change script will create a simple table
+
+.. code-block:: python
+
+    account = Table(
+        'account', meta,
+	Column('id', Integer, primary_key=True),
+        Column('login', String(40)),
+        Column('passwd', String(40)),
+    )
+
+This table should be created in a change script. Let's create one::
+
+ $ python manage.py script "Add account table"
+
+This creates an empty change script at
+:file:`my_repository/versions/001_Add_account_table.py`. Next, we'll
+edit this script to create our table.
+
+
+Edit the change script
+----------------------
+
+Our change script predefines two functions, currently empty:
+:py:func:`upgrade` and :py:func:`downgrade`. We'll fill those in:
+
+.. code-block:: python
+
+    from sqlalchemy import Table, Column, Integer, String, MetaData
+
+    meta = MetaData()
+
+    account = Table(
+        'account', meta,
+        Column('id', Integer, primary_key=True),
+        Column('login', String(40)),
+        Column('passwd', String(40)),
+    )
+  
+
+    def upgrade(migrate_engine):
+        meta.bind = migrate_engine
+        account.create()
+
+
+    def downgrade(migrate_engine):
+        meta.bind = migrate_engine
+        account.drop()
+
+.. note::
+
+    The generated script contains * imports from sqlalchemy and migrate. You
+    should tailor the imports to fit your actual demand.
+
+As you might have guessed, :py:func:`upgrade` upgrades the database to the next
+version. This function should contain the :ref:`schema changes
+<changeset-system>` we want to perform (in our example we're creating a
+table).
+
+:py:func:`downgrade` should reverse changes made by :py:func:`upgrade`. You'll
+need to write both functions for every change script. (Well, you don't *have*
+to write downgrade, but you won't be able to revert to an older version of the
+database or test your scripts without it.) If you really don't want to support
+downgrades it is a good idea to raise a :py:class:`NotImplementedError` or some
+equivalent custom exception. If you let :py:func:`downgrade` pass silently you
+might observe undesired behaviour for subsequent downgrade operations if
+downgrading multiple :term:`versions <version>`.
+
+
+.. note::
+
+    As you can see, **migrate_engine** is passed to both functions.  You should
+    use this in your change scripts, rather than creating your own engine.
+
+.. warning::
+
+    You should be very careful about importing files from the rest of your
+    application, as your change scripts might break when your application
+    changes. Read more about `writing scripts with consistent behavior`_.
+
+
+Test the change script
+------------------------
+
+Change scripts should be tested before they are committed. Testing a script
+will run its :func:`upgrade` and :func:`downgrade` functions on a specified
+database; you can ensure the script runs without error. You should be testing
+on a test database - if something goes wrong here, you'll need to correct it by
+hand. If the test is successful, the database should appear unchanged after
+:func:`upgrade` and :func:`downgrade` run.
+
+To test the script::
+
+ $ python manage.py test
+ Upgrading... done
+ Downgrading... done
+ Success
+
+Our script runs on our database (:file:`sqlite:///project.db`, as specified in
+:file:`manage.py`) without any errors.
+
+Our :term:`repository's <repository>` :term:`version` is::
+
+ $ python manage.py version
+ 1
+
+.. note::
+
+    Due to #41 the database must be exactly one :term:`version` behind the
+    :term:`repository` :term:`version`.
+
+.. _production testing warning:
+
+.. warning::
+
+    The :option:`test` command executes actual scripts, be sure you are *NOT*
+    doing this on production database.
+    
+    If you need to test production changes you should:
+    
+        #. get a dump of your production database
+        #. import the dump into an empty database
+        #. run :option:`test` or :option:`upgrade` on that copy
+
+
+Upgrade the database
+--------------------
+
+Now, we can apply this change script to our database::
+
+ $ python manage.py upgrade
+ 0 -> 1...
+ done
+
+This upgrades the database (:file:`sqlite:///project.db`, as specified when we
+created :file:`manage.py` above) to the latest available :term:`version`. (We
+could also specify a version number if we wished, using the :option:`--version`
+option.) We can see the database's :term:`version` number has changed, and our
+table has been created::
+
+ $ python manage.py db_version
+ 1
+ $ sqlite3 project.db
+ sqlite> .tables
+ account migrate_version
+ sqlite> .schema account
+ CREATE TABLE account (
+    id INTEGER NOT NULL,
+    login VARCHAR(40),
+    passwd VARCHAR(40),
+    PRIMARY KEY (id)
+ );
+
+Our account table was created - success!
+
+Modifying existing tables
+-------------------------
+
+After we have initialized the database schema we now want to add another Column
+to the `account` table that we already have in our schema.
+
+First start a new :term:`changeset` by the commands learned above::
+
+ $ python manage.py script "Add email column"
+
+This creates a new :term:`changeset` template. Edit the resulting script
+:file:`my_repository/versions/002_Add_email_column.py`:
+
+.. code-block:: python
+
+    from sqlalchemy import Table, MetaData, String, Column
+
+
+    def upgrade(migrate_engine):
+        meta = MetaData(bind=migrate_engine)
+        account = Table('account', meta, autoload=True)
+        emailc = Column('email', String(128))
+        emailc.create(account)
+
+
+    def downgrade(migrate_engine):
+        meta = MetaData(bind=migrate_engine)
+        account = Table('account', meta, autoload=True)
+        account.c.email.drop()
+
+As we can see in this example we can (and should) use SQLAlchemy's schema
+reflection (autoload) mechanism to reference existing schema objects. We could
+have defined the table objects as they are expected before upgrade or downgrade
+as well but this would have been more work and is not as convenient.
+
+We can now apply the changeset to :file:`sqlite:///project.db`::
+
+ $ python manage.py upgrade
+ 1 -> 2...
+ done
+
+and get the following expected result::
+
+ $ sqlite3 project.db
+ sqlite> .schema account
+ CREATE TABLE account (
+    id INTEGER NOT NULL,
+    login VARCHAR(40),
+    passwd VARCHAR(40), email VARCHAR(128),
+    PRIMARY KEY (id)
+ );
+
+
+Writing change scripts
+======================
+
+As our application evolves, we can create more change scripts using a similar
+process.
+
+By default, change scripts may do anything any other SQLAlchemy program can do.
+
+SQLAlchemy Migrate extends SQLAlchemy with several operations used to change
+existing schemas - ie. ``ALTER TABLE`` stuff. See :ref:`changeset
+<changeset-system>` documentation for details.
+
+
+Writing scripts with consistent behavior
+----------------------------------------
+
+Normally, it's important to write change scripts in a way that's independent of
+your application - the same SQL should be generated every time, despite any
+changes to your app's source code. You don't want your change scripts' behavior
+changing when your source code does.
+
+.. warning:: 
+
+    **Consider the following example of what NOT to do**
+
+    Let's say your application defines a table in the :file:`model.py` file:
+
+    .. code-block:: python
+
+        from sqlalchemy import *
+ 
+        meta = MetaData()
+        table = Table('mytable', meta,
+            Column('id', Integer, primary_key=True),
+        )
+
+    ... and uses this file to create a table in a change script:
+
+    .. code-block:: python
+ 
+        from sqlalchemy import *
+        from migrate import *
+        import model
+
+        def upgrade(migrate_engine):
+            model.meta.bind = migrate_engine
+
+        def downgrade(migrate_engine):
+            model.meta.bind = migrate_engine 
+            model.table.drop()
+
+    This runs successfully the first time. But what happens if we change the
+    table definition in :file:`model.py`?
+
+    .. code-block:: python
+
+        from sqlalchemy import *
+ 
+        meta = MetaData()
+        table = Table('mytable', meta,
+            Column('id', Integer, primary_key=True),
+            Column('data', String(42)),
+        )
+
+    We'll create a new column with a matching change script
+
+    .. code-block:: python
+
+        from sqlalchemy import *
+        from migrate import *
+        import model
+
+        def upgrade(migrate_engine):
+            model.meta.bind = migrate_engine
+            model.table.create()
+
+        def downgrade(migrate_engine):
+            model.meta.bind = migrate_engine
+            model.table.drop()
+
+    This appears to run fine when upgrading an existing database - but the
+    first script's behavior changed! Running all our change scripts on a new
+    database will result in an error - the first script creates the table based
+    on the new definition, with both columns; the second cannot add the column
+    because it already exists.
+
+    To avoid the above problem, you should use SQLAlchemy schema reflection as
+    shown above or copy-paste your table definition into each change script
+    rather than importing parts of your application.
+
+    .. note::
+        Sometimes it is enough to just reflect tables with SQLAlchemy instead
+        of copy-pasting - but remember, explicit is better than implicit!
+
+
+Writing for a specific database
+-------------------------------
+
+Sometimes you need to write code for a specific database. Migrate scripts can
+run under any database, however - the engine you're given might belong to any
+database. Use engine.name to get the name of the database you're working with
+
+.. code-block:: python
+
+ >>> from sqlalchemy import *
+ >>> from migrate import *
+ >>> 
+ >>> engine = create_engine('sqlite:///:memory:')
+ >>> engine.name
+ 'sqlite'
+
+
+Writings .sql scripts
+---------------------
+
+You might prefer to write your change scripts in SQL, as .sql files, rather
+than as Python scripts. SQLAlchemy-migrate can work with that::
+
+ $ python manage.py version
+ 1
+ $ python manage.py script_sql postgresql
+
+This creates two scripts
+:file:`my_repository/versions/002_postgresql_upgrade.sql` and
+:file:`my_repository/versions/002_postgresql_downgrade.sql`, one for each
+*operation*, or function defined in a Python change script - upgrade and
+downgrade. Both are specified to run with PostgreSQL databases - we can add
+more for different databases if we like. Any database defined by SQLAlchemy may
+be used here - ex. sqlite, postgresql, oracle, mysql...
+
+
+.. _command-line-usage:
+
+Command line usage
+==================
+
+.. currentmodule:: migrate.versioning.shell
+
+:command:`migrate` command is used for API interface. For list of commands and
+help use::
+
+ $ migrate --help
+
+:command:`migrate` command executes :func:`main` function.
+For ease of usage, generate your own :ref:`project management script
+<project_management_script>`, which calls :func:`main
+<migrate.versioning.shell.main>` function with keywords arguments. You may want
+to specify `url` and `repository` arguments which almost all API functions
+require.
+
+If api command looks like::
+
+ $ migrate downgrade URL REPOSITORY VERSION [--preview_sql|--preview_py]
+
+and you have a project management script that looks like
+
+.. code-block:: python
+
+    from migrate.versioning.shell import main
+
+    main(url='sqlite://', repository='./project/migrations/')
+
+you have first two slots filed, and command line usage would look like::
+
+    # preview Python script
+    $ migrate downgrade 2 --preview_py
+
+    # downgrade to version 2
+    $ migrate downgrade 2
+
+.. versionchanged:: 0.5.4
+    Command line parsing refactored: positional parameters usage
+
+Whole command line parsing was rewriten from scratch with use of OptionParser.
+Options passed as kwargs to :func:`~migrate.versioning.shell.main` are now
+parsed correctly. Options are passed to commands in the following priority
+(starting from highest):
+
+- optional (given by :option:`--some_option` in commandline)
+- positional arguments
+- kwargs passed to :func:`migrate.versioning.shell.main`
+
+
+Python API
+==========
+
+.. currentmodule:: migrate.versioning.api
+
+All commands available from the command line are also available for
+your Python scripts by importing :mod:`migrate.versioning.api`. See the
+:mod:`migrate.versioning.api` documentation for a list of functions;
+function names match equivalent shell commands. You can use this to
+help integrate SQLAlchemy Migrate with your existing update process.
+
+For example, the following commands are similar:
+ 
+*From the command line*::
+
+ $ migrate help help
+ /usr/bin/migrate help COMMAND
+
+     Displays help on a given command.
+
+*From Python*
+
+.. code-block:: python
+
+ import migrate.versioning.api
+ migrate.versioning.api.help('help')
+ # Output:
+ # %prog help COMMAND
+ # 
+ #     Displays help on a given command.
+  
+
+.. _migrate.versioning.api: module-migrate.versioning.api.html
+
+.. _repository_configuration:
+
+
+Experimental commands
+=====================
+
+Some interesting new features to create SQLAlchemy db models from existing
+databases and vice versa were developed by Christian Simms during the
+development of SQLAlchemy-migrate 0.4.5. These features are roughly documented
+in a `thread in migrate-users`_.
+
+.. _`thread in migrate-users`:
+ http://groups.google.com/group/migrate-users/browse_thread/thread/a5605184e08abf33#msg_85c803b71b29993f
+
+Here are the commands' descriptions as given by ``migrate help <command>``:
+
+- ``compare_model_to_db``: Compare the current model (assumed to be a
+  module level variable of type sqlalchemy.MetaData) against the
+  current database.
+- ``create_model``: Dump the current database as a Python model to
+  stdout.
+- ``make_update_script_for_model``: Create a script changing the old
+  Python model to the new (current) Python model, sending to stdout.
+
+As this sections headline says: These features are *EXPERIMENTAL*. Take the
+necessary arguments to the commands from the output of ``migrate
+help <command>``.
+
+
+Repository configuration
+========================
+
+SQLAlchemy-migrate :term:`repositories <repository>` can be configured in their
+:file:`migrate.cfg` files.  The initial configuration is performed by the
+`migrate create` call explained in :ref:`Create a change repository
+<create_change_repository>`. The following options are available currently:
+
+- :option:`repository_id` Used to identify which repository this database is
+  versioned under. You can use the name of your project.
+- :option:`version_table` The name of the database table used to track the
+  schema version. This name shouldn't already be used by your project. If this
+  is changed once a database is under version control, you'll need to change
+  the table name in each database too.
+- :option:`required_dbs` When committing a change script, SQLAlchemy-migrate
+  will attempt to generate the sql for all supported databases; normally, if
+  one of them fails - probably because you don't have that database installed -
+  it is ignored and the commit continues, perhaps ending successfully.
+  Databases in this list MUST compile successfully during a commit, or the
+  entire commit will fail. List the databases your application will actually be
+  using to ensure your updates to that database work properly. This must be a
+  list; example: `['postgres', 'sqlite']`
+- :option:`use_timestamp_numbering` When creating new change scripts, Migrate
+  will stamp the new script with a version number. By default this is
+  latest_version + 1. You can set this to 'true' to tell Migrate to use the UTC
+  timestamp instead.
+
+  .. versionadded:: 0.7.2
+
+.. _custom-templates:
+
+
+Customize templates
+===================
+
+Users can pass ``templates_path`` to API functions to provide customized
+templates path.  Path should be a collection of templates, like
+``migrate.versioning.templates`` package directory.
+
+One may also want to specify custom themes. API functions accept
+``templates_theme`` for this purpose (which defaults to `default`)
+
+Example::
+	
+ /home/user/templates/manage $ ls
+ default.py_tmpl
+ pylons.py_tmpl
+
+ /home/user/templates/manage $ migrate manage manage.py --templates_path=/home/user/templates --templates_theme=pylons
+
+.. versionadded:: 0.6.0