diff options
Diffstat (limited to 'doc/source/versioning.rst')
| -rw-r--r-- | doc/source/versioning.rst | 641 |
1 files changed, 0 insertions, 641 deletions
diff --git a/doc/source/versioning.rst b/doc/source/versioning.rst deleted file mode 100644 index 7b30cd3..0000000 --- a/doc/source/versioning.rst +++ /dev/null @@ -1,641 +0,0 @@ -.. _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 |