{
    "info": {
        "author": "Brendon Hogger",
        "author_email": "brendonh@gmail.com",
        "bugtrack_url": null,
        "classifiers": [],
        "description": "# Installation #\nIn your terminal (vagrant), do:\n\n```bash\ncd [repo]/protected/config\ncp db.json.sample db.json\ncd [repo]/protected/schema\nvirtualenv env\n. env/bin/activate\npip install -r requirements.txt\n```\n\nNext time, when you want to run schemup:\n\n```bash\n. env/bin/activate\npython update.py commit\n```\n\n\n# General #\n\nSchemup versions a database on a per-table basis. This means that table X can be at version 1, while table Y can be at version 2.\n\nAll versioning data is stored in a special table called `schemup_tables`. This table keeps other (versioned) tables' schema history, including what their latest schemas should look like (somewhat similar to git history).\n\nSchemup provides 2 main features: validation (schemas synchronization checking), and migration (schemas updating).\n\n\n\n# Version declaration #\n\nThis is basically just a map that states what version each table should be at. There are a couple of convenient helpers to build this map.\n\n## Storm ORM\n\nThis is achieved by using a decorator, and adding a special attribute `__version__` to model class declarations.\n\n```python\n    from storm.locals import *\n    from schemup.orms import storm\n\n    # Pass this to validate/upgrade commands. It should be a global\n    # shared among model files, if there are several of them\n    stormSchema = storm.StormSchema()\n\n    @stormSchema.versioned\n    class User(Storm):\n        __storm_table__ = \"user\"\n        __version__ = \"knn_1\"\n```\n\n## JSON file\n\nKeep the map in a json file.\n\n**`versions.json`**\n\n```json\n    {\n        \"users\": \"nta_6\",\n        \"message\": \"ntd_9\"\n    }\n```\n\n**`update.py`**\n\n```python\n    class DictSchema(object):\n        def __init__(self, path):\n            self.versions = json.load(open(path, \"r\"))\n\n        def getExpectedTableVersions(self):\n            return sorted(self.versions.iteritems())\n\n    # Pass this to validate/upgrade commands\n    dictSchema = DictSchema(\"versions.json\")\n```\n\n\n# Validation #\n\nSchemup helps keeping track, for each table, of the synchronization between 3 things:\n\n- The desired schema, declared in code, or data file (actually only version, no table structure).\n- The journaled schema (cached schema, recorded schema) in `schemup_tables` (both version and table structure).\n- The actual DB schema (table structure only, obviously).\n\nFull validation happens in 2 steps:\n\n## Checking recorded schema vs. desired schema (version mismatches) ##\n\nThis is done by simply comparing the versions declared in code with the latest version recorded in `schemup_tables`. Note that there is not (yet) an actually schema comparison.\n\nOut-of-sync tables detected by this validation indicate that the current schema in `schemup_tables` (and thus the actual schema, provided that they are in sync) need to be brought up-to-date with the desired schema (using Schemup migration feature).\n\n## Checking recorded schema vs. actual schema (schema mismatches) ##\n\nThis is done by getting the schema information from the DB (e.g. `information_schema.tables`), and compare them against the last recorded schema in `schemup_tables`.\n\nMismatches detected by this validation usually means the schema was changed outside of Schemup's control, which should be avoided.\n\n```python\n    from schemup import validator\n    from warp import runtime\n\n    conn = runtime.store.get_database().raw_connect()\n    dbSchema = postgres.PostgresSchema(conn)\n\n    errors = validator.findSchemaMismatches(dbSchema)\n    if errors:\n        print \"Schema mismatches, was the schema changed outside Schemup?\"\n```\n\n\n\n# Migration #\n\nSchemup migration feature attempts to bring the real schema (and `schemup_tables`) up-to-date with the current ORM schema, by applying a series of \"upgraders\".\n\nEach upgrader is responsible for bringing a table from one version to another, using an upgrading function that will be run on the DB schema.\n\nAn upgrader also has dependencies, which are the required versions of some tables before it can be run. For example, a foreign key referencing a table can only be added after the table is created.\n\nThere are 2 types of upgraders: those created from decorated Python functions, and those loaded from YAML files. There is a command to load both types from files under a directory.\n\n```python\n    from schemup import commands\n\n    # Load upgraders from .py & .yaml files under \"migration\" directory\n    commands.load(\"migrations\")\n```\n\nAfter getting all the necessary upgraders, the `upgrade` command can be used to carry out the migration.\n\n```python\n    from schemup import commands\n    from warp import runtime\n    from models import stormSchema\n\n    conn = runtime.store.get_database().raw_connect()\n    dbSchema = postgres.PostgresSchema(conn)\n\n    commands.upgrade(dbSchema, stormSchema)\n```\n\n## Python upgrading functions ##\n\nNote that the logic used by these functions must be immutable over time. Therefore application logic (functions, orm classes...) from other module must not be used directly, but copied for use only in the migrations; otherwise the migrations will be broken once application logic changes.\n\n```python\n    from schemup.upgraders import upgrader\n\n    @upgrader('user', 'bgh_2', 'bgh_3')\n    def user_add_email(dbSchema):\n        dbSchema.execute(\"ALTER TABLE user ADD email VARCHAR\")\n        # Or running arbitrary code here\n\n    @upgrader('order', None, 'knn_1', dependencies=[('user', 'bgh_1')])\n    def order_create(dbSchema):\n        dbSchema.execute(\"\"\"\n            CREATE TABLE order (\n                id integer NOT NULL PRIMARY KEY,\n                user_id integer NOT NULL,\n                CONSTRAINT order_user_id FOREIGN KEY (user_id) REFERENCES user(id)\n            )\n        \"\"\")\n```\n\n## Upgraders loaded from YAML files ##\n\nOne file can contain multiple blocks delineated by `---`. Each block corresponds to an upgrader. If a block's `from` key is omitted, it defaults to the previous block's `to` key.\n\n### One table per file ###\n\n**`user.yaml`**\n\n```yaml\n    ---\n    # Another upgrader\n\n    ---\n    table: user\n    from: bgh_2\n    to: bgh_3\n    sql: |\n      ALTER TABLE user ADD email VARCHAR\n\n    ---\n    # Another upgrader\n```\n\n**`order.yaml`**\n\n```yaml\n    ---\n    table: order\n    from: null\n    to: knn_1\n    depends:\n     - [ user, bgh_1 ]\n    sql: |\n      CREATE TABLE order (\n          id integer NOT NULL PRIMARY KEY,\n          user_id integer NOT NULL,\n          CONSTRAINT order_user_id FOREIGN KEY (user_id) REFERENCES user(id)\n      )\n```\n### One feature per file ###\n\n**`feature.add-rule-table.yaml`**\n\n```yaml\n    ---\n    table: questionnaire_rule\n    from: null\n    to: nta_1\n    depends:\n      - [questionnaire, nta_2]\n    sql: |\n      CREATE TABLE questionnaire_rule (\n        id SERIAL NOT NULL PRIMARY KEY,\n        created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),\n        issue TEXT,\n        requires TEXT[2][],\n        recommendations INTEGER[],\n        questionnaire_id INTEGER NOT NULL REFERENCES questionnaire(id) ON DELETE RESTRICT\n      );\n\n    ---\n    table: questionnaire\n    from: nta_3\n    to: nta_4\n    depends:\n      - [questionnaire_rule, nta_2]\n    sql: |\n      ALTER TABLE questionnaire\n        DROP COLUMN rules;\n```\n\n# Snapshoting #\n\n## Whole schema ##\n\nUse this when you have an existing database whose schema changes need to be kept track of with Schemup.\n\n- Add version declarations.\n- Add correct schema migrations. This ensures that a new instance can be created from scratch. If there is not enough time, a workaround can be used: put the schema dump in one of the migration, leaving the rest of the migrations no-op (e.g. `SELECT 1;`). For example:\n\n```yaml\n    ---\n    table: users\n    from: null\n    to: nta_1\n    sql: |\n        # The whole schema here\n\n    ---\n    table: message\n    from: nul\n    to: nta_1\n    sql: |\n        SELECT 1;\n\n    # Other tables\n```\n\n- Use the `snapshot` command.\n\n```python\n    from schemup.dbs import postgres\n    from schemup import commands\n    from warp.runtime import store\n    conn = store.get_database().raw_connect()\n    dbSchema = postgres.PostgresSchema(conn)\n    commands.snapshot(dbSchema, stormSchema)\n```\n\n## Single table (aka I mistakenly changed the schema in SQL shell) ##\n\nUse this when you mistakenly chang a table's schema outside of schemup (e.g. trying out DDL in SQL shell without rolling back the transaction). This creates a\nschema mismatch\n\n```python\n    from warp.common.schema import makeSchema\n    from warp.runtime import store\n    schema = makeSchema(store)\n    schema.setSchema(\"recommendation\", \"nta_5\")\n    schema.commit()\n```\n\n\n# Workflow #\n\n- When adding to an existing DB, use snapshotting.\n- When starting from scratch, provide upgraders with `from` equal to `None` (python) or `null` (yaml).\n- Version naming convention: programmer initials and integer id. Example: `bgh_1`, `bgh_2`, `knn_3`, `nta_4`, `knn_5`.\n- Migration organization: one-feature-per-file is preferred; initial schema can be in its own file.\n\n## Upgraders ##\n\n- When there are schema changes, bump model classes' `__version__`.\n- Put upgraders under `migrations` directory. Upgraders can be yaml files, or python files containing upgrader-decorated functions.\n- Test the migration manually on a dev DB.\n- Remember that Postgres DDL is transactional. Therefore it is a good idea to try out migration DDL in Postgres shell, wrapped in a transaction that will be rolled back.\n\n```sql\n    START TRANSACTION;\n    -- Try CREATE TABLE, ALTER TABLE... here\n    ROLLBACK;\n```\n\n## Migration ##\n\n- Back up the DB before doing migration.\n- Migration steps\n\n```python\n    from schemup.dbs import postgres\n    from schemup import commands\n    from warp.runtime import store\n\n    # Get current table versions, by ORM\n    from models import stormSchema\n\n    # Get schema\n    conn = store.get_database().raw_connect()\n    dbSchema = postgres.PostgresSchema(conn)\n\n    # Make sure the current DB is not \"dirty\"\n    validator.findSchemaMismatches(dbSchema)\n\n    # Load upgraders\n    commands.load(\"migrations\")\n\n    # Do upgrade\n    commands.upgrade(schema, stormSchema)\n\n    # Check if the schemas are in sync\n    commands.validate(runtime.schema, stormSchema)\n```\n\n## Shared dev machine ##\n\nSchemup works on a forward-only, no-branching (directed acyclic graph) basis. This creates a problem in using shared dev machines:\n\n- Supposed the main branch is at `user:a1`, `message:b1`.\n- Developer A add migration `user:a_1` to `user:a_2` on his topic branch and test it on dev.\n- Developer B add migration `message:b_1` to `message:b_2` and wants to test it on dev. He checks out his branch and runs the migration. Because `user` is at `a_2`, but the code wants it to be at `a_1`, schemup tries migrating `user` from `a_2` to `a_1` and fails not knowing how.\n\nThe best solution is to ensure that the DB's schema is the same before and after you test the code with new schema. For example:\n\n- Make a dump of the whole database before running schema migration.\n- Switch back to the branch the code was on previously after testing the new code.\n- Replace the current state of the database with the dump.\n\n## Snapshot-less application of schemup to existing DB ##\n\nThis method was by proposed Duy.\nThe idea is to use a dump as the DB's initial state, instead of a blank DB. The process looks like:\n\n- Start with no migrations, blank version declarations.\n- New instance are provisioned by the initial dump instead of just a blank DB.\n- Continue as normal.\n- New migrations should be written with the non-blank initial DB's state in mind. For example if the dump already contains a table `user`, its migrations should look like:\n\n```yaml\n    ---\n    table: user\n    from: null\n    to: lmd_1\n    sql: |\n        ALTER TABLE user ADD COLUMN age INTEGER DEFAULT NULL;\n```\n\nand not\n\n```yaml\n    ---\n    table: user\n    from: null\n    to: lmd_1\n    sql: |\n        CREATE TABLE user (\n            # ...\n        )\n\n    ---\n    table: user\n    from: lmd_1\n    to: lmd_2\n    sql: |\n        ALTER TABLE user ADD COLUMN age INTEGER DEFAULT NULL;\n```",
        "description_content_type": null,
        "docs_url": null,
        "download_url": "UNKNOWN",
        "downloads": {
            "last_day": -1,
            "last_month": -1,
            "last_week": -1
        },
        "home_page": "https://github.com/brendonh/schemup",
        "keywords": null,
        "license": "UNKNOWN",
        "maintainer": null,
        "maintainer_email": null,
        "name": "schemup",
        "package_url": "https://pypi.org/project/schemup/",
        "platform": "UNKNOWN",
        "project_url": "https://pypi.org/project/schemup/",
        "project_urls": {
            "Download": "UNKNOWN",
            "Homepage": "https://github.com/brendonh/schemup"
        },
        "release_url": "https://pypi.org/project/schemup/1.0.1/",
        "requires_dist": null,
        "requires_python": null,
        "summary": "Database-agnostic schema upgrade tools",
        "version": "1.0.1"
    },
    "last_serial": 1211751,
    "releases": {
        "0.1": [],
        "1.0": [
            {
                "comment_text": "",
                "digests": {
                    "md5": "abd015a667b442964fe7be06cca7a2ce",
                    "sha256": "1ea4260ba0413bbc81b3d66f553ef4d1c3f6c1254b3792b846729f2b2ed59764"
                },
                "downloads": -1,
                "filename": "schemup-1.0.zip",
                "has_sig": false,
                "md5_digest": "abd015a667b442964fe7be06cca7a2ce",
                "packagetype": "sdist",
                "python_version": "source",
                "requires_python": null,
                "size": 16956,
                "upload_time": "2014-09-03T16:14:25",
                "url": "https://files.pythonhosted.org/packages/c7/7f/e59fbfb16a17f4c811dacfb79e0ecb3eed85008f0fa8f7d3c2e70c7df803/schemup-1.0.zip"
            }
        ],
        "1.0.1": [
            {
                "comment_text": "",
                "digests": {
                    "md5": "65657e49e2727a5a66a354c1b4265a77",
                    "sha256": "7e3d7a1c4a4e7e3324ff554c68970cb20f79e5417853b4a47225b3fe03d487e1"
                },
                "downloads": -1,
                "filename": "schemup-1.0.1.zip",
                "has_sig": false,
                "md5_digest": "65657e49e2727a5a66a354c1b4265a77",
                "packagetype": "sdist",
                "python_version": "source",
                "requires_python": null,
                "size": 21660,
                "upload_time": "2014-09-03T16:23:32",
                "url": "https://files.pythonhosted.org/packages/20/5b/1dc3cbe475efe9257baf7dce02636fbc308ad2e75f9d9588085e714751ed/schemup-1.0.1.zip"
            }
        ]
    },
    "urls": [
        {
            "comment_text": "",
            "digests": {
                "md5": "65657e49e2727a5a66a354c1b4265a77",
                "sha256": "7e3d7a1c4a4e7e3324ff554c68970cb20f79e5417853b4a47225b3fe03d487e1"
            },
            "downloads": -1,
            "filename": "schemup-1.0.1.zip",
            "has_sig": false,
            "md5_digest": "65657e49e2727a5a66a354c1b4265a77",
            "packagetype": "sdist",
            "python_version": "source",
            "requires_python": null,
            "size": 21660,
            "upload_time": "2014-09-03T16:23:32",
            "url": "https://files.pythonhosted.org/packages/20/5b/1dc3cbe475efe9257baf7dce02636fbc308ad2e75f9d9588085e714751ed/schemup-1.0.1.zip"
        }
    ]
}