
moo: the dbmigrations management tool
-------------------------------------

The dbmigrations package exposes a library and a tool. The tool, called
"moo", is responsible for creating, installing, and reverting migrations
in your database backend.

At present, MySQL, PostgreSQL and Sqlite3 are the only supported database
backends.

The moo tool works by creating migration files in a specific location,
called a migration store, on your filesystem. This directory is where
all possible migrations for your project will be kept. Moo allows you to
create migrations that depend on each other. When you use moo to upgrade
your database schema, it determines which migrations are missing, what
their dependencies are, and installs the required migrations in the
correct order (based on dependencies).

Moo works by prompting you for new migration information. It then
creates a migration YAML file (whose format is described below), which
you then edit by hand.

When migrations are installed into your database, the set of installed
migrations is tracked by way of a migration table that is installed into
your database.

WARNING TO MYSQL USERS
----------------------

If you are using this library with MySQL, beware of the following
caveat!

Unlike PostgreSQL and SQLite, MySQL does not support transactional
Data Definition Language (DDL) statements such as CREATE TABLE. Since
dbmigrations is designed around the assumption that it's useful and
important to make migrations atomic, and since most migrations will have
at least some DDL in them, this means that dbmigrations cannot make
atomicity guarantees about your migrations if you are using MySQL as
your backend.  The MySQL docs have this to say:

http://dev.mysql.com/doc/refman/5.7/en/cannot-roll-back.html

This means that features like moo's "--test" flag will not do what you
expect, that mistakes in migrations will be tough to fix in development,
and that a problem in a migration in a production deployment COULD
HOSE YOUR DATABASE and BREAK RUNNING APPLICATIONS. Note that MySQL's
DDL statements cause an implicit commit, so any errors after each DDL
statement will have to be corrected by hand!

It is the author's opinion that this makes MySQL unfit for production
use and that you should strongly consider using some other backend such
as PostgreSQL which has much more robust support for transactional DDL.

You might be wondering why support for MySQL is included if it can be
so dangerous to use. Some users have requested and contributed support
for it, and of course we can't always choose to use a different backend
for preexisting systems. In those cases at least dbmigrations doesn't
make your problem any worse than it already is, provided you heed this
disclaimer. :)

You have been warned!

Getting started
---------------

 1. Create a directory in which to store migration files.

 2. Set an environment variable DBM_MIGRATION_STORE to the path to the
    directory you created in step 1.

 3. Set an environment variable DBM_DATABASE_TYPE to a supported
    backend. Valid values are "mysql", "postgresql" and "sqlite3".

 4. Set an environment variable DBM_DATABASE to a database connection
    string that is appropriate for the value of DBM_DATABASE_TYPE you
    chose. The contents of this depend on the value of
    DBM_DATABASE_TYPE, see the "Environment" documentation section for
    more information.

 5. Run "moo upgrade". This command will not actually install any
    migrations, since you have not created any, but it will attempt to
    connect to your database and install a migration-tracking table.

    If this step succeeds, you should see this output:

    Database is up to date.

 6. Create a migration with "moo new".  Here is an example output:

    $ moo new hello-world
    Selecting dependencies for new migration: hello-world
    
    Confirm: create migration 'hello-world'
      (No dependencies)
    Are you sure? (yn): y
    Migration created successfully: ".../hello-world.txt"

 7. Edit the migration you created. In this case, moo created a file
    $DBM_MIGRATION_STORE/hello_world.txt that looks like this:

      Description: (Description here.)
      Created: 2015-02-18 00:50:12.041176 UTC
      Depends:
      Apply: |
        (Apply SQL here.)
      
      Revert: |
        (Revert SQL here.)

    This migration has no valid apply or revert SQL yet; that's for you
    to provide.  You might edit the apply and revert fields as follows:

      Apply: |
        CREATE TABLE foo (a int);
      
      Revert: |
        DROP TABLE foo;

 8. Test the new migration with "moo test". This will install the
    migration in a transaction and roll it back. Here is example output:

    $ moo test hello-world
    Applying: hello-world... done.
    Reverting: hello-world... done.
    Successfully tested migrations.

 9. Install the migration. This can be done in one of two ways: with
    "moo upgrade" or with "moo apply".  Here are examples:

    $ moo apply hello-world
    Applying: hello-world... done.
    Successfully applied migrations.

    $ moo upgrade
    Applying: hello-world... done.
    Database successfully upgraded.

 10. List installed migrations with "moo list".

    $ moo list
    hello-world

 10. Revert the migration.

    $ moo revert hello-world
    Reverting: hello-world... done.
    Successfully reverted migrations.

 11. List migrations that have not been installed.

    $ moo upgrade-list
    Migrations to install:
      hello-world

Configuration file format
-------------------------

All moo commands accept a --config-file option which you can use to
specify the path to a configuration file containing your settings. This
approach is an alternative to setting environment variables. The
configuration file format uses the same environment variable names for
its fields. An example configuration is as follows:

  DBM_DATABASE_TYPE = "sqlite3"
  DBM_DATABASE = "/path/to/database.db"
  DBM_MIGRATION_STORE = "/path/to/migration/store"
  DBM_LINEAR_MIGRATIONS = on/off (or true/false; defaults to off)
  DBM_TIMESTAMP_FILENAMES = on/off (or true/false; defaults to off)

Alternatively, you may save your settings to "moo.cfg" file in the current
directory (probably a project root) and moo will load it automatically, if
present. Specifying --config-file disables this behavior.

If you use a config file (either the default one or the one specified with
--config-file option) but the environment variables are set, they will
override settings from the file. You may use this to have project settings
specified in a file and use environment to specify user-local configuration
options.

Migration file format
---------------------

A migration used by this package is a structured document in YAML
format containing these fields:

   Description: (optional) a textual description of the migration

  Dependencies: (required, but may be empty) a whitespace-separated
                list of migration names on which the migration
                depends; these names are the migration filenames
                without the filename extension

       Created: The UTC date and time at which this migration was
                created

         Apply: The SQL necessary to apply this migration to the
                database

        Revert: (optional) The SQL necessary to revert this migration
                from the database

The format of this file is somewhat flexible; please see the YAML 1.2
format specification for a full description of syntax features.  I
recommend appending "|" to the Apply and Revert fields if they contain
multi-line SQL that you want to keep that way, e.g.,

  Apply: |
    CREATE OR REPLACE FUNCTION ...
    ...
    ...

  Revert: |
    DROP TABLE foo;
    DROP TABLE bar;

Note that this is only *necessary* when concatenating the lines would
have a different meaning, e.g.,

  Apply:
    -- Comment here
    CREATE TABLE;

Without "|" on the "Apply:" line, the above text would be collapsed to
"-- Comment here CREATE TABLE;" which is probably not what you want.
For a full treatment of this behavior, see the YAML spec.

Environment
-----------

Moo depends on these environment variables / configuration file
settings:

  DBM_DATABASE_TYPE

    The type of database you'll be managing.  Supported values
    at present are "mysql", "postgresql" and "sqlite3". This will
    determine the format of DBM_DATABASE.

  DBM_DATABASE

    The database connection string for the database you'll be
    managing.  The connection strings for each supported database type
    are as follows:

    DBM_DATABASE_TYPE=postgresql:

      The format of this value is a PostgreSQL database connection
      string, i.e., that described at:

      http://www.postgresql.org/docs/8.1/static/libpq.html#LIBPQ-CONNECT

    DBM_DATABASE_TYPE=sqlite3:

      The format of this value is a filesystem path to the Sqlite3
      database to be used.

    DBM_DATABASE_TYPE=mysql:

      For MySQL, DBM_DATABASE should be a value of key value pairs,
      where each pair is formed by `key=value`, and each pair separated
      by a semicolon. Required keys are `host`, `user` and `database`,
      and you can optionally supply `port` and `password`.

      Example: DBM_DATABASE="host=localhost; user=root; database=cows"

  DBM_MIGRATION_STORE

    The path to the filesystem directory where your migrations will be
    kept.  moo will create new migrations in this directory and use
    the migrations in this directory when updating the database
    schema.  Initially, you'll probably set this to an extant (but
    empty) directory.  moo will not create it for you.

  DBM_LINEAR_MIGRATIONS

    If set to true/on, the linear migrations feature will be enabled.
    Defaults to off. See 'Linear migrations' section for more details.

  DBM_TIMESTAMP_FILENAMES

    If set to true/on, the migration filename for new migrations will
    have a timestamp embedded in it.

Commands
--------

  new <migration name>: create a new migration with the given name and
    save it in the migration store.  This command will prompt you for
    dependencies on other migrations (if the 'linear migrations'
    feature is disabled) and ask for confirmation before creating the
    migration in the store.  If you use the --no-ask flag, the migration
    will be created immediately with no dependencies.

  apply <migration name>: apply the specified migration (and its
    dependencies) to the database.  This operation will be performed
    in a single transaction which will be rolled back if an error
    occurs.  moo will output updates as each migration is applied.

  revert <migration name>: revert the specified migration (and its
    reverse dependencies -- the migrations which depend on it) from
    the database.  This operation will be performed in a single
    transaction which will be rolled back if an error occurs.  moo
    will output updates as each migration is reverted.

  test <migration name>: once you've created a migration, you might
    find it useful to test the migration to be sure that it is
    syntactically valid; the "test" command will apply the specified
    migration and revert it (if revert SQL is specified in the
    migration).  It will perform both of these operations in a
    transaction and then issue a rollback.

  upgrade: this will apply all migrations in the migration store which
    have not yet been applied to the database.  Each migration will be
    applied with its dependenciees in the correct order.  All of the
    migrations will be applied together in a single transaction.  By
    default, this transaction is committed; if you use the --test
    flag, the transaction will be rolled back, allowing you to test
    the entire upgrade process.

  upgrade-list: this will list the migrations that the "upgrade"
    command would apply if you were to run it.  In other words, this
    will list all migrations which have not yet been applied to the
    database.

  reinstall: this will revert, then reapply a migration, all in a
    transaction.  If --test is specified, the transaction will be
    rolled back; otherwise it will be committed.  This is mostly
    useful in development when a migration applies but is incorrect
    and needs to be tweaked and reapplied.

Linear migrations
-----------------

If you know that every migration needs to depend on all previous ones,
consider enabling this feature. When enabled, 'moo new' will automatically
select smallest subset of existing migrations that will make the new one
indirectly depend on every other already in the store. This in turn makes
the store linear-ish (in terms of order of execution) and helps managing the
migrations by always depending on previous work. Also, this may easily be used
to see how the database changed in time.
