【译文】数据库管理工具Flyway

       

        公司以前的数据库版本管理是自研的软件大致思路是，通过boss_version表来记录版本号以及脚本执行历史 ，然后根据配置文件来配置版本号以及版本号中需要执行的脚本，通过工具和选择的版本号生成批处理任务，然后执行批处理文件完成执行脚本，这种方式虽然也管理了脚本，但是执行过程中需要人工参与处理，每次升级的时候也需要维护脚本执行顺序，

        Flyway是一款开源的数据库版本管理工具,用于管理我们的数据库版本，有效的帮我们解决了上面的问题

 

1. Why database migrations?（为什么数据库要迁移）

 

First, let's start from the beginning and assume we have a project called Shiny and its primary deliverable is a piece of software called Shiny

Soft that connects to a database called Shiny DB.

首先，让我们从头开始。假设我们有一个项目叫"Shiny " ，它主要是交付一个软件名叫 "Shiny Soft" 的软件，这个软件链接到名称为"Shiny DB" 的数据库

 

The simplest diagram to represent this could look something like this:

最简单的图形来反映这个项目，大概是这个样子

We have our software and our database. Great. And this could very well be all you need.

我们有我们的软件系统和我们的数据库，太好了，大概这就是你所有的需要

 

But on most projects, this simple view of the world very quickly translates into this:

但是大多数的项目中，因为时间变化很快，这个简单的图形就会演变成下面的样子：

We now not only have to deal with one copy of our environment, but with several. This presents a number of challenges.

现在，我们可能需要复制一套环境来解决新问题，但是可能需要复制多套环境，这也是我们当前面临的挑战

 

We have been pretty good at solving them on the code side.

对于软件，我们有很多好的方法来解决版本问题

• Version control is now universal with better tools everyday. 版本控制是最常用的而且功能一天比一天好用
• We have reproducible builds and continuous integration. 我们可以重复构建并且持续集成
• We have well defined release and deployment processes. 我们有明确的发布和部署流程

But what about the database? 那么现在，我们的数据库怎么管理

 

Well unfortunately we have not been doing so well there. Many projects still rely on manually applied sql scripts. And sometimes not even that (a quick sql statement here or there to fix a problem). And soon many questions arise:

不幸的是我们这方面做的并不好，很多项目仍然依赖手动执行脚本，有时候我们甚至需要执行sql语句来快速解决问题，很快出现了更多的问题

• What state is the database in on this machine? 这台机器上的数据库当前什么状态？
• Has this script already been applied or not? 脚本已经允许执行了吗？
• Has the quick fix in production been applied in test afterwards? 测试结束以后，这种快速脚本修复问题是被允许的吗？
• How do you set up a new database instance? 你如何初始化一个新数据库实例和其数据呢？

More often than not the answer to these questions is: We don't know.

通常我们对上述问题的答案的回答是：我不知道

 

Database migrations are a great way to regain control of this mess.

数据库迁移是解决这些混乱的一个好的方法

They allow you to: 它可以让我们

• Recreate a database from scratch 重新创建1个数据库
• Make it clear at all times what state a database is in 让数据库的处于一个清晰的状态下
• Migrate in a deterministic way from your current version of the database to a newer one 可以以一种确定的方式让你从一个版本升级到另一个版本

2. How Flyway works（Flyway 是如何工作的）

The easiest scenario is when you point Flyway to an empty database.

最简单的情况是你使用Flyway 指向的是一个空库

It will try to locate its schema history table. As the database is empty, Flyway won't find it and will create it instead.

Flyway 尝试通过schema历史表定位，当数据库中为空库的时候Flyway找不到这张表，则会创建这张表

 

You now have a database with a single empty table called flyway_schema_history by default:、

现在你有了一个数据库和一张叫flyway_schema_history 的空表

This table will be used to track the state of the database.

这张表将会用于跟踪数据库状态

 

Immediately afterwards Flyway will begin scanning the filesystem or the classpath of the application for migrations. They can be written in either Sql or Java.

接着， Flyway将会扫描应用的文件系统或者指定路径来查找migrations，它们可以是Sql或者Java编写

（这里的migrations可以理解为所有变更操作，Flyway可以自动的发现文件系统中或者Java指定路径下的migrations）

 

The migrations are then sorted based on their version number and applied in order:

然后migrations将基于版本号排序并按顺序执行

As each migration gets applied, the schema history table is updated accordingly:

每一个migration 被执行后， schema 历史表也会相应的更新

flyway_schema_history

installed_rank	version	description	type	script	checksum	installed_by	installed_on	execution_time	success
1	1	Initial Setup	SQL	V1__Initial_Setup.sql	1996767037	axel	2016-02-04 22:23:00.0	546	true
2	2	First Changes	SQL	V2__First_Changes.sql	1279644856	axel	2016-02-06 09:18:00.0	127	true

With the metadata and the initial state in place, we can now talk about migrating to newer versions.

有了元数据，我们现在来谈谈更新版本的方式

 

Flyway will once again scan the filesystem or the classpath of the application for migrations. The migrations are checked against the schema history table. If their version number is lower or equal to the one of the version marked as current, they are ignored.

Flyway 将会再次扫描应用的文件系统或者路径查找migrations，新增加的migrations 信息将会和schema历史表信息检测，如果版本号低于或者等于当前的记录，它们将会被忽略

 

The remaining migrations are the pending migrations: available, but not applied.

剩下的migrations 将会被认为是待迁移的migrations ：有效状态并且未执行

They are then sorted by version number and executed in order:

它们将会被按照版本号排序然后按顺序执行

The schema history table is updated accordingly:

schema历史表也会相应的按顺序更新

flyway_schema_history

installed_rank	version	description	type	script	checksum	installed_by	installed_on	execution_time	success
1	1	Initial Setup	SQL	V1__Initial_Setup.sql	1996767037	axel	2016-02-04 22:23:00.0	546	true
2	2	First Changes	SQL	V2__First_Changes.sql	1279644856	axel	2016-02-06 09:18:00.0	127	true
3	2.1	Refactoring	JDBC	V2_1__Refactoring		axel	2016-02-10 17:45:05.4	251	true

 

And that's it! Every time the need to evolve the database arises, whether structure (DDL) or reference data (DML), simply create a new migration with a version number higher than the current one. The next time Flyway starts, it will find it and upgrade the database accordingly.

 

这就是Flyway, 每当我们要更新数据库的时候，无论是DDL语言还是DML语言，只需要创建一个高于当前版本号的migration, 下一次Flyway开始运行的时候，Flyway将会发现它然后相应的升级数据库

 

1.Migrations

Migrations are most commonly written in SQL. This makes it easy to get started and leverage any existing scripts, tools and skills. It gives you access to the full set of capabilities of your database and eliminates the need to understand any intermediate translation layer.

很多迁移都是用SQL编写的，这让使用现有的脚本和工具等技术变得很容易，这个给你访问数据库全部能力的通道并且避免了去理解中间翻译层

 

SQL-based migrations are typically used for

• DDL changes (CREATE/ALTER/DROP statements for TABLES,VIEWS,TRIGGERS,SEQUENCES,…)
• Simple reference data changes (CRUD in reference data tables)
• Simple bulk data changes (CRUD in regular data tables)

 

基于SQL的migrations通常用于

• DDL更改(表、视图、触发器、序列的创建/更改/删除语句，…)
• 简单的数据更改（参考数据表的CRUD操作）
• 简单批量操作（常规数据表数据表的CRUD操作）

 

2.Naming

In order to be picked up by Flyway, SQL migrations must comply with the following naming pattern:

为了让Flyway发现， SQL migrations 必须满足下面的命名规范

The file name consists of the following parts:

• Prefix: V for versioned (configurable), U for undo (configurable) and R for repeatable migrations (configurable)
• Version: Version with dots or underscores separate as many parts as you like (Not for repeatable migrations)
• Separator: __ (two underscores) (configurable)
• Description: Underscores or spaces separate the words
• Suffix: .sql (configurable)

名称由下部分组成

• 前缀：V 表示版本， U for undo R 表示可重复执行的 migrations
• 版本：用点或者下划线来表示你的版本，（不适用 可重复执行的 migrations）
• 分割线 ：两个下划线
• 描述: 使用下划线或者空格来分割单词
• 后缀：后缀，例如.sql表示sql

Optionally versioned SQL migrations can also omit both the separator and the description.

The configuration option validateMigrationNaming determines how Flyway handles files that do not correspond with the naming pattern when carrying out a migration: if true then Flyway will simply ignore all such files, if false then Flyway will fail fast and list all files which need to be corrected.

 

3.Discovery

Flyway discovers Java-based migrations on the Java classpath in the packages referenced by the locations property.

Flyway可以在Java的路径下的包中发现migrations,