从指定目录按顺序运行mysql迁移脚本,跟踪数据库中的当前版本。
migration-runner的Python项目详细描述
SQL迁移运行程序
python脚本从指定的文件夹按顺序运行sql迁移脚本, 每次迁移后更新数据库本身中的最新架构版本。
警告:此工具纯粹是作为ecs数字技术测试的解决方案创建的。 见PROBLEM 有关用例和任务要求的详细信息。
几乎可以肯定,not应该作为成熟的解决方案用于任何实际的用例 几乎每个用例都存在。请参阅NOTES的“问题概述”部分 关于这个话题的进一步评论。
要求
- python 2.7或3.5+
- 现有MySQL或MariaDB数据库,本地运行或远程主机运行。
- 名为
versionTable
的表,有一个名为“version”的int(11)
列。有关架构,请参见here。 - 包含要执行的SQL脚本以将数据库迁移到每个版本的目录。
- 每个迁移/版本应该有一个文件。
- 文件的命名应与模式匹配
VERSION.brief_description.sql
, 其中version是一个整数,表示执行该脚本后的数据库版本。
- 版本号应该是唯一的和连续的,以便得到一致的结果。
安装
要安装迁移运行程序,请在终端中运行此命令:
$ pip install migration_runner
这是安装迁移运行程序的首选方法,因为它将 始终安装最新的稳定版本。
如果您没有安装pip,这个Python installation guide 可以引导你完成这个过程。
有关从源代码构建的说明,请参见 documentation。
用法
使用--help
运行migration_runner
脚本以获取用法说明:
$ migration_runner --help
Usage: migration_runner [OPTIONS] SQL_DIRECTORY DB_USER DB_HOST DB_NAME DB_PASSWORD
A cli tool for executing SQL migrations in sequence.
Options:
-s, --single-file TEXT Filename of single SQL script to process.
-l, --loglevel LVL Either CRITICAL, ERROR, WARNING, INFO or DEBUG
-v, --version Show the version and exit.
--help Show this message and exit.
示例
成功使用:
$ migration_runner sql-migrations beveradb migration_runner_test.beveradb.tk test_user test_password
2019-02-10 22:16:30,394 - info: Starting with database version: 0
2019-02-10 22:16:30,395 - info: Migrations yet to be processed: 10 (out of 11 in dir)
2019-02-10 22:16:30,721 - info: Successfully upgraded database from version: 0 to 1 by executing migration in file: 'sql-migrations/001.create_migrations_version_table.sql'
2019-02-10 22:16:31,566 - info: Successfully upgraded database from version: 1 to 2 by executing migration in file: 'sql-migrations/2.set_current_version_to_1.sql'
2019-02-10 22:16:32,562 - info: Successfully upgraded database from version: 2 to 45 by executing migration in file: 'sql-migrations/045.createtable.sql'
2019-02-10 22:16:33,236 - info: Successfully upgraded database from version: 45 to 46 by executing migration in file: 'sql-migrations/046.create_seed_items.sql'
2019-02-10 22:16:34,173 - info: Successfully upgraded database from version: 46 to 48 by executing migration in file: 'sql-migrations/048.create_rooms.sql'
2019-02-10 22:16:34,849 - info: Successfully upgraded database from version: 48 to 49 by executing migration in file: 'sql-migrations/049 .rename-object-item.sql'
2019-02-10 22:16:36,258 - info: Successfully upgraded database from version: 49 to 51 by executing migration in file: 'sql-migrations/051-add-room-relations.sql'
2019-02-10 22:16:37,165 - info: Successfully upgraded database from version: 51 to 52 by executing migration in file: 'sql-migrations/052.create_customer_order.sql'
2019-02-10 22:16:38,299 - info: Successfully upgraded database from version: 52 to 54 by executing migration in file: 'sql-migrations/54-fix-customer-address-defaults.sql'
2019-02-10 22:16:39,150 - info: Successfully upgraded database from version: 54 to 55 by executing migration in file: 'sql-migrations/55exampleorder.sql'
2019-02-10 22:16:39,499 - info: Database version now 55 after processing 10 migrations. Remaining: 0.
无需处理:
$ migration_runner sql-migrations test_user beveradb.tk migration_runner_test test_password
2019-02-10 22:19:23,252 - info: Starting with database version: 55
2019-02-10 22:19:23,252 - info: Migrations yet to be processed: 0 (out of 11 in dir)
2019-02-10 22:19:23,252 - info: Database version now 55 after processing 0 migrations. Remaining: 0.
缺少参数:
$ migration_runner sql-migrations test_user beveradb.tk migration_runner_test
Usage: migration_runner [OPTIONS] SQL_DIRECTORY DB_USER DB_HOST DB_NAME
DB_PASSWORD
Try "migration_runner --help" for help.
Error: Missing argument "DB_PASSWORD".
调试输出:
$ migration_runner -l DEBUG sql-migrations test_user beveradb.tk migration_runner_test fake_password
2019-02-10 22:21:48,074 - debug: CLI execution start
2019-02-10 22:21:48,075 - debug: Migrations found: 11
2019-02-10 22:21:48,075 - debug: Connecting to database with details: user=test_user, password=fake_password, host=beveradb.tk, db=migration_runner_test
2019-02-10 22:20:37,731 - error: Database connection error: 1045 (28000): Access denied for user 'test_user' (using password: YES)