数据库版本管理

2017-07-04

没有接触 OpenStack 之前，一直以为只有代码、Office 文档之类有版本管理，没想到数据库也有版本管理，真是大开眼界。

基于 SQLAlchemy 的数据库版本管理工具有 SQLAlchemy-Migrate 和 Alembic。前者在 OpenStack 中用得最多，目前由 OpenStack 官方维护。

本文主要摘录自 SQLAlchemy-Migrate 官方样例，以后如有需要再做进一步探讨。

1. 新建一个数据库版本管理仓库

$ pwd
/home/xiehongfeng100/Migrate
$ migrate create my_repository "Example project"
$ tree my_repository 
my_repository
├── __init__.py
├── __init__.pyc
├── manage.py
├── migrate.cfg
├── README
└── versions
    ├── __init__.py
    └── __init__.pyc

1 directory, 7 files
$ cat my_repository/migrate.cfg 
[db_settings]
# Used to identify which repository this database is versioned under.
# You can use the name of your project.
repository_id=Example project	# 我们指定的项目名称，只是我们这里并没有项目；如有项目，可写为项目名称

# 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.
version_table=migrate_version

# When committing a change script, 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']
required_dbs=[]

# 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.
use_timestamp_numbering=False

2. 新建一个 sqlite3 数据库

$ sqlite3 project.db
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .databases
seq  name             file                                                      
---  ---------------  ----------------------------------------------------------
0    main             /home/xiehongfeng100/Migrate/project.db                   
sqlite> .quit

3. 关联仓库和数据库
关联仓库和数据库并为 manage.py 设置预设参数：

$ python my_repository/manage.py version_control sqlite:///project.db my_repository
$ migrate manage my_repository/manage.py --repository=my_repository --url=sqlite:///project.db
$ cat my_repository/manage.py 
#!/usr/bin/env python
from migrate.versioning.shell import main

if __name__ == '__main__':
    main(url='sqlite:///project.db', debug='False', repository='my_repository')

可以看一下现在的数据库版本为 0：

1	$ python my_repository/manage.py db_version

3. 新建一个版本脚本
我们打算新建一个数据库表 account，可以这样子做：

$ python my_repository/manage.py script "Add account table"
$ tree my_repository 
my_repository
├── __init__.py
├── __init__.pyc
├── manage.py
├── migrate.cfg
├── README
└── versions
    ├── 001_Add_account_table.py	# 自动添加的版本文件
    ├── __init__.py
    └── __init__.pyc
$ cat my_repository/versions/001_Add_account_table.py
from sqlalchemy import *
from migrate import *


def upgrade(migrate_engine):	# 数据库升级，一般是增改操作
    # Upgrade operations go here. Don't create your own engine; bind
    # migrate_engine to your metadata
    pass


def downgrade(migrate_engine):	# 数据库降低，一般是删除操作
    # Operations to reverse the above upgrade go here.
    pass

接下来，我们修改新增的文件内容为：

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()

数据库升级之前最好测试一下，确保如我们预期地工作：

$ python my_repository/manage.py test                
Upgrading...
done
Downgrading...
done
Success

接下来就可以升级数据库啦：

$ python my_repository/manage.py db_version 
0
$ python my_repository/manage.py upgrade
0 -> 1... 
done
$ python my_repository/manage.py db_version
1
$ sqlite3 project.db 
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .schema account
CREATE TABLE account (
	id INTEGER NOT NULL, 
	login VARCHAR(40), 
	passwd VARCHAR(40), 
	PRIMARY KEY (id)
);
sqlite> .quit

4. 编辑已有表
为已有表添加一个新的字段 email：

$ python my_repository/manage.py script "Add email column"
$ tree my_repository 
my_repository
├── __init__.py
├── __init__.pyc
├── manage.py
├── migrate.cfg
├── README
└── versions
    ├── 001_Add_account_table.py
    ├── 001_Add_account_table.pyc
    ├── 002_Add_email_column.py	# 自动添加的版本文件
    ├── __init__.py
    └── __init__.pyc

1 directory, 10 files

编辑新增的文件内容为：

# my_repository/versions/002_Add_email_column.py 

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()

升级：

$ python my_repository/manage.py test
Upgrading...
done
Downgrading...
done
Success
$ python my_repository/manage.py upgrade
1 -> 2... 
done
$ sqlite3 project.db 
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .schema account
CREATE TABLE account (
	id INTEGER NOT NULL, 
	login VARCHAR(40), 
	passwd VARCHAR(40), email VARCHAR(128),  # email 新增的字段
	PRIMARY KEY (id)
);
sqlite> .quit

4. 版本回滚
我们也可以对数据库进行降级操作：

$ python my_repository/manage.py downgrade 0	# 需指定要降级到的版本，这里我们是降级到版本 0
1 -> 0... 
done
$ python my_repository/manage.py db_version 
0
$ sqlite3 project.db 
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .schema account
sqlite> .quit