The SQL migration mechanism in Keystone is interesting enough to warrant some attention. If you need to modify the SQL database in any of the Open Stack projects, you are going to use a similar approach. Here is a step by step I have recorded of a SQL upgrade script I am writing for a feature in Keystone.

SQL upgrade scripts are in keystone/common/sql/migrate_repo/versions and are number in the order in which they are executed. The initial tables for Keystone are created in the script 001_add_initial_tables.p

I am trying to Replace Tenant-User Membership with a default role. The first step is to create the default role.

To start with, I am going to create a new role called “Member” in the Roles table. If a role with this name already exists, I will leave it in place.

This makes downgrade a little tricky. I won’t know if the “member” role existed on upgrade. Ordinarily, I would remove this role, but I can’t without breaking some users deployments. Thus, the downgrade for this is going to be a no-op.

As of the time of this writing, the highest number script is 013. So to start, I create a new script with a sequence number of 014: 014_membership_role.py. I make it as simple as possible.

def upgrade(migrate_engine):
    pass

def downgrade(migrate_engine):
    pass

To run the upgrade tests from the command line.

./run_tests.sh test_sql_upgrade

That seems to imply that all is well. But we don’t really know if anything was done there. I, of course, ran in the debugger, so I could set a break point, but if you are not, all you know is it seems to work. Since we are espousing test driven design, lets make sure we run the upgrade test…at least for very limited values of “sure”.

Open up the file tests/test_sql_upgrade.py and add a new test: I will call it: upgrade_default_roles.

    def test_upgrade_default_roles(self):
        self.upgrade(13)
        self.upgrade(14)

And re-run the test. Since we are going to be focusing on this test, we can speed things up to run just this test:

./run_tests.sh test_sql_upgrade:SqlUpgradeTests.test_upgrade_default_roles
test_upgrade_default_roles (test_sql_upgrade.SqlUpgradeTests) ... ok

If you are paranoid, you will once again admit that you don’t know that this did anything. I like it when new test fail. So, to start with, I am going to explicitly throw an exception in my upgrade script.

from keystone import exception

def upgrade(migrate_engine):
    raise exception.NotImplemented()

def downgrade(migrate_engine):
    pass

Now my output starts with:

./run_tests.sh test_sql_upgrade:SqlUpgradeTests.test_upgrade_default_roles
test_upgrade_default_roles (test_sql_upgrade.SqlUpgradeTests) ... ERROR

And continues on with information to help in debugging.

OK, I run with my tests directory in a ram disk. This speeds up running the tests, but means that if I don’t commit my changes, I’ll lose them on a reboot. I also might get distracted and have to work on something else. So, I commit, to git.

young@ayoung530 keystone (project_member)]$ git status
# On branch project_member
# Changes not staged for commit:
#   (use "git add ..." to update what will be committed)
#   (use "git checkout -- ..." to discard changes in working directory)
#
#	modified:   tests/test_sql_upgrade.py
#
# Untracked files:
#   (use "git add ..." to include in what will be committed)
#
#	keystone/common/sql/migrate_repo/versions/014_membership_role.py
no changes added to commit (use "git add" and/or "git commit -a")
[ayoung@ayoung530 keystone (project_member)]$ git add tests/test_sql_upgrade.py  keystone/common/sql/migrate_repo/versions/014_membership_role.py
[ayoung@ayoung530 keystone (project_member)]$ git commit -m "start of default role work."
[project_member 2115c76] start of default role work.
 2 files changed, 13 insertions(+)
 create mode 100644 keystone/common/sql/migrate_repo/versions/014_membership_role.py

Now, if I come back to this branch later, I run my tests, they fail, and I am immediately back in the coding zone.

I can modify my upgrade script to always insert the new role. I’ll put in a corresponding downgrade script that deletes the row. Since my database is empty to start, I know this will succeed.

import sqlalchemy
import uuid
from keystone import exception

def upgrade(migrate_engine):
    meta = sqlalchemy.MetaData()
    meta.bind = migrate_engine
    role_table = sqlalchemy.Table('role', meta, autoload=True)
    conn = migrate_engine.connect()
    conn.execute(role_table.insert(),
                 id=uuid.uuid4().hex, name="Member", extra="{}")


def downgrade(migrate_engine):
    meta = sqlalchemy.MetaData()
    meta.bind = migrate_engine
    role_table = sqlalchemy.Table('role', meta, autoload=True)
    conn = migrate_engine.connect()
    conn.execute(role_table.delete().where(role_table.c.name=='Member'))

While running the test shows that this succeeds, it doesn’t do much. What I will now do is add a test that queries the table to make sure the new value is in there. The following test runs both the upgrade and the downgrade. This is an interim step. If we were going to keep the downgrade around, we would make a separate unit test for upgrade and for downgrade.

    def test_upgrade_default_roles(self):
        self.upgrade(13)
        session = self.Session()
        count = session.execute("select count(*) as c from role where name='Member'").fetchone()['c']
        self.assertEquals(0, count)
        self.upgrade(14)
        count = session.execute("select count(*) as c from role where name='Member'").fetchone()['c']
        self.assertEquals(1, count)
        self.downgrade(13)
        count = session.execute("select count(*) as c from role where name='Member'").fetchone()['c']
        self.assertEquals(0, count)

Of course, now other unit tests fail, but that is a tale for another day.