liquibase 使用

Most of the applications we develop are managed using some sort of version control system. But what about the databases used by those applications? More often we make the changes to our development, test, and production databases manually. Such an approach may work for an application with only one or two developers, but in a larger team with a number of developers, it becomes difficult to share the changes with everyone.

我们开发的大多数应用程序都是使用某种版本控制系统进行管理的。 但是那些应用程序使用的数据库呢？ 通常，我们会手动更改开发，测试和生产数据库。 这种方法可能只适用于只有一个或两个开发人员的应用程序，但是在具有多个开发人员的更大团队中，很难与所有人共享更改。

In this article we’ll discuss Liquibase, an open-source tool for managing and versioning database schema changes. It helps us to organize incremental database changes into different change sets and apply them to the database.

在本文中，我们将讨论Liquibase，这是一种用于管理数据库架构更改和对其进行版本控制的开源工具。 它有助于我们将增量数据库更改组织到不同的更改集中，并将其应用于数据库。

Liquibase is not the only database versioning/migration tool. There are many solutions, like Doctrine 2 migrations, Rails AR migrations, DBDeploy, and so on. The first two options are excellent solutions but they are platform specific. DBDeploy is relatively simple, but it isn’t as feature rich as Liquibase. Liquibase solves many problems that are not addressed by different database migration tools like supporting multiple developers, different DBMS systems, branching, and so on.

Liquibase不是唯一的数据库版本控制/迁移工具。 有许多解决方案，例如Doctrine 2迁移，Rails AR迁移，DBDeploy等。 前两个选项是出色的解决方案，但它们特定于平台。 DBDeploy相对简单，但是功能不如Liquibase丰富。 Liquibase解决了许多不同数据库迁移工具无法解决的问题，例如支持多个开发人员，不同的DBMS系统，分支等。

Also, a serious drawback in most of the tools is that they are not change aware. Instead of focusing the changes made, they compare two snapshots of database schema for generating a migration script. So for example, renaming columns is treated as a drop+add operation which may result in data loss. Liquibase is change aware.

而且，大多数工具的严重缺陷是它们不了解更改。 他们没有关注所做的更改，而是比较了数据库架构的两个快照以生成迁移脚本。 因此，例如，重命名列被视为拖放操作，这可能会导致数据丢失。 Liquibase具有更改意识。

Let’s take a look at how to use Liquibase in our projects.

让我们看一下如何在我们的项目中使用Liquibase。

Liquibase如何工作 (How Liquibase Works)

If you are using Mac with brew, installing Liquibase is simple. Just run brew install Liquibase and you’re done. The same is the case for Ubuntu, sudo apt-get install liquibase will do it for you. The Liquibase binary is a cross-platform Java application, which means you can download the JAR and use it on Windows, Mac, or Linux. It’s a good idea to keep it in your project folder so that anyone in the project can use it without any installations.

如果将Mac与brew一起使用，则安装Liquibase很简单。 只需运行brew install Liquibase ，就可以完成。 Ubuntu也是如此， sudo apt-get install liquibase会为您完成。 Liquibase二进制文件是跨平台的Java应用程序，这意味着您可以下载JAR并在Windows，Mac或Linux上使用它。 最好将其保存在项目文件夹中，以便项目中的任何人都可以使用它而无需进行任何安装。

When using Liquibase, you store database changes in XML files, commonly known as changelog files. Changes can be kept in a single file or multiple files to be included in a master changelog file. The second option is recommended as it allows greater flexibility when organizing changes.

使用Liquibase时，您将数据库更改存储在XML文件(通常称为changelog文件)中。 更改可以保存在单个文件中，也可以保存在要包含在主变更日志文件中的多个文件中。 建议使用第二个选项，因为它在组织更改时具有更大的灵活性。

In a changelog file, you organize changes in different changesets. A changeset can contain one or more changes that you want to apply to your database. Each changeset can be uniquely identified using the id and author attributes along with the class path of changelog file. Liquibase creates a table (databasechangelog) in your database to keep track of successfully applied changes. Liquibase runs through each change set one by one and checks if they have already been applied by comparing the checksum in the databasechangelog table. It will apply a change if it hasn’t already been run or if it has a runAlways tag on it.

在变更日志文件中，您可以组织不同变更集中的变更。 变更集可以包含一个或多个要应用于数据库的变更。 可以使用id和author属性以及changelog文件的类路径来唯一标识每个变更集。 Liquibase在数据库中创建一个表( databasechangelog )以跟踪成功应用的更改。 Liquibase逐个运行每个变更集，并通过比较databasechangelog表中的校验和来检查它们是否已应用。 如果尚未运行或具有runAlways标记，它将应用更改。

入门 (Getting Started)

For demonstration purposes I’ve created a database named application on my local MySQL server, as well as a changelog file. You can keep it in your project folder or in a separate location, but changelog files should be under version control.

出于演示目的，我在本地MySQL服务器上创建了一个名为application的数据库，以及一个changelog文件。 您可以将其保存在项目文件夹中或单独的位置，但更改日志文件应受版本控制。

Here’s the first version of our changelog file with no changesets.

这是没有变更集的变更日志文件的第一个版本。

<?xml version="1.0" encoding="UTF-8"?> 
<!--db.changelog.xml-->
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd">
 
</databaseChangeLog>

On the command-line navigate to the location where you are keeping the changelog file and run the following command:

在命令行上，导航到保存更改日志文件的位置，然后运行以下命令：

liquibase --driver=com.mysql.jdbc.Driver 
     --classpath=../lib/mysql-connector-java-5.1.21-bin.jar 
     --changeLogFile=db.changelog.xml 
     --url="jdbc:mysql://localhost/application" 
     --username=dbuser 
     --password=secret 
     update

If Liquibase can connect to database using the given username and password, it should create two tables in the application database, DATABASECHANGELOG and DATABASECHANGELOGLOCK, and show the following output:

如果Liquibase可以使用给定的用户名和密码连接到数据库，则应该在应用程序数据库中创建两个表DATABASECHANGELOG和DATABASECHANGELOGLOCK ，并显示以下输出：

INFO 8/2/12 10:19 AM:liquibase: Successfully acquired change log lock
INFO 8/2/12 10:19 AM:liquibase: Creating database history table with name: `DATABASECHANGELOG`
INFO 8/2/12 10:19 AM:liquibase: Reading from `DATABASECHANGELOG`
INFO 8/2/12 10:19 AM:liquibase: Reading from `DATABASECHANGELOG`
INFO 8/2/12 10:19 AM:liquibase: Successfully released change log lock
Liquibase Update Successful

In the above command, all of the parameters except classpath are required. driver specifies the class name of the database driver that we want to use. changeLogFile is the name of our database changelog. url specifies the JDBC database connection string which includes the server type, hostname, and database name. classpath is where you keep the classes, like the database connector, used by Liquibase.

在以上命令中，除classpath以外的所有参数都是必需的。 driver指定我们要使用的数据库驱动程序的类名。 changeLogFile是我们数据库更改日志的名称。 url指定JDBC数据库连接字符串，其中包括服务器类型，主机名和数据库名称。 classpath可以保存Liquibase使用的类，例如数据库连接器。

Instead of specifying command line parameters each time you run Liquibase, you can keep them in a Java properties file named liquibase.properties in the same directory. Then you can run just liquibase <command> The properties file would look like this:

您不必在每次运行Liquibase时都指定命令行参数，而可以将它们liquibase.properties在同一目录中名为liquibase.properties的Java属性文件中。 然后，您可以只运行liquibase <command>属性文件如下所示：

#liquibase.properties
driver: com.mysql.jdbc.Driver
classpath: ../lib/mysql-connector-java-5.1.21-bin.jar
url: jdbc:mysql://localhost/application
changeLogFile: db.changelog.xml
username: dbuser
password: secret

Next let’s create a user table with ID, name, and email fields by adding a changeset to db.changelog.xml. Here’s the updated XML:

接下来，通过将更改集添加到db.changelog.xml ，来创建具有ID，名称和电子邮件字段的用户表。 这是更新的XML：

<?xml version="1.0" encoding="UTF-8"?>
<!--db.changelog.xml-->
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd">
 <changeSet id="1" author ="shameer">
  <createTable tableName="user">
   <column name="id" type="int">
    <constraints primaryKey="true" nullable="false" />
   </column>
   <column name="name" type="varchar(50)">
    <constraints nullable="false" />
   </column>
   <column name="email" type="varchar(128)">
    <constraints unique="true" nullable="false" />
   </column>
  </createTable>
 </changeSet>
</databaseChangeLog>

createTable takes the name of table in tableName attribute and columns as sub tags. You specify the columns in this table with the column tag with its name and datatype as required attributes. column supports a number of other useful attributes which may not make sense in all cases. For example, you can set a column to auto increment by using autoIncrement="true".

createTable将tableName属性中的表名称和列作为子标记。 您可以使用column标签及其name和datatype作为必需属性来指定此表中的column 。 column支持许多其他有用的属性，这些属性可能并非在所有情况下都有意义。 例如，您可以使用autoIncrement="true"将列设置为自动递增。

Any column constraints are specified with constraints tags. In our example, the id column has primary key and not null constraints, and the email column has a unique constraint.

任何列约束都使用constraints标签指定。 在我们的示例中， id列具有主键而不是null约束，而email列具有唯一约束。

Run Liquibase and look at the result:

运行Liquibase并查看结果：

liquibase update

INFO 8/4/12 7:16 AM:liquibase: Successfully acquired change log lock
INFO 8/4/12 7:16 AM:liquibase: Creating database history table with name: `DATABASECHANGELOG`
INFO 8/4/12 7:16 AM:liquibase: Reading from `DATABASECHANGELOG`
INFO 8/4/12 7:16 AM:liquibase: Reading from `DATABASECHANGELOG`
INFO 8/4/12 7:16 AM:liquibase: ChangeSet db.changelog.xml::1::shameer ran successfully in 74ms
INFO 8/4/12 7:16 AM:liquibase: Successfully released change log lock
Liquibase Update Successful

If you look at the database, you will see a user table with following structure:

如果查看数据库，将看到具有以下结构的user表：

*************** 1. row ***************
  Field: id
   Type: int(11)
   Null: NO
    Key: PRI
Default: NULL
  Extra: 
*************** 2. row ***************
  Field: name
   Type: varchar(50)
   Null: NO
    Key: 
Default: NULL
  Extra: 
*************** 3. row ***************
  Field: email
   Type: varchar(128)
   Null: NO
    Key: UNI
Default: NULL
  Extra: 

Did you notice that we don’t have an auto increment column in the table? We can add a second changeset to alter the table making the following changes:

您是否注意到我们表中没有自动递增列？ 我们可以添加第二个变更集来更改表，进行以下更改：

• Change id column to auto_increment

  将id列更改为auto_increment

• Rename column name to fullname

  重命名列name fullname name

• Add new column age

  添加新的列age

<changeSet id="2" author="shameer">
 <addAutoIncrement tableName="user" columnName="id" columnDataType="int" />
 <renameColumn tableName="user" oldColumnName="name" newColumnName="fullname" columnDataType="varchar(100)"/>
 <addColumn tableName="user">
  <column name="age" type="SMALLINT"/>
 </addColumn>
</changeSet>

Here, the addAutoIncrement tag will make the id column auto incrementing. renameColumn will rename a column by accepting the name of column to be changed in the oldColumnName attribute and the new name in newColumnName.

在这里， addAutoIncrement标记将使id列自动递增。 renameColumn将通过接受列的名称在要改变重命名列oldColumnName属性，并在新的名称newColumnName 。

Let’s run Liquibase once again and then look at the user table. You should see the two modified columns and a new age column.

让我们再次运行Liquibase，然后查看user表。 您应该看到两个已修改的列和一个新的age列。

生成变更日志文件 (Generating Changelog file)

What if you’ve already started your project without Liquibase, and then you realize its going to be hard to deal with database changes without some automation mechanism? Liquibase comes with a handy feature to generate changelog file from your existing schemas. Use the following command to generate changelog file with the name specified in liquibase.properties.

如果您已经在没有Liquibase的情况下启动了项目，然后又意识到如果没有某种自动化机制，它将很难处理数据库更改怎么办？ Liquibase附带了一个方便的功能，可以从您现有的模式中生成变更日志文件。 使用以下命令生成具有在liquibase.properties指定的名称的liquibase.properties日志文件。

liquibase generateChangeLog

The generated changelog will have create statements for all of the tables in your database.

生成的变更日志将具有数据库中所有表的create语句。

When we develop an application on a local machine, it’s easier to make changes directly in MySQL than creating changelog files for each change. But we’ll need to have those changes across multiple developers in the team. Luckily Liquibase also provides a facility to compare two databases and generate changelogs from them.

当我们在本地计算机上开发应用程序时，直接在MySQL中进行更改要比为每个更改创建更改日志文件容易。 但是我们需要在团队中的多个开发人员之间进行这些更改。 幸运的是，Liquibase还提供了一种比较两个数据库并从中生成变更日志的工具。

Here are the steps to generate change log file for newly created tables in a database:

以下是为数据库中新创建的表生成更改日志文件的步骤：

1. Take a backup of database using mysqldump

   使用mysqldump备份数据库

2. Create a temporary database
   创建一个临时数据库
3. Import the dump to a temporary database
   将转储导入到临时数据库
4. Drop all changes done in original database
   删除所有在原始数据库中完成的更改
5. Generate a changelog using Liquibase.
   使用Liquibase生成变更日志。

liquibase diffChangeLog 
--referenceUrl=jdbc:mysql://localhost/application_temp 
--referenceUsername=dbuser 
--referencePassword=secret

This will append a new changeset in db.changelog.xml for creating the missing tables in the database.

这将在db.changelog.xml附加一个新的变更集，用于在数据库中创建缺少的表。

<changeSet author="shameerc (generated)" id="1344051849388-1">
 <createTable tableName="temp">
  <column autoIncrement="true" name="id" type="INT">
   <constraints nullable="false" primaryKey="true"/>
  </column>
  <column name="name" type="VARCHAR(200)"/>
  <column name="value" type="TEXT"/>
 </createTable>
</changeSet>

Once generated, you should check and verify this against your local database before you commit it to version control. Also be cautious when generating changelog files like this since Liquibase will consider renamed columns as a drop+add column which will cause data loss.

生成后，在将其提交到版本控制之前，应针对本地数据库进行检查和验证。 在生成这样的变更日志文件时也要小心，因为Liquibase会将重命名的列视为drop + add列，这将导致数据丢失。

回滚更改 (Rolling Back Changes)

Up until now we’ve only looked at making changes to our database schema. But what if we need to undo changes after they have been applied to a database? We can easily rollback changes applied through Liquibase using rollback commands.

到目前为止，我们只研究了对数据库架构的更改。 但是，如果我们需要在将更改应用到数据库后撤消更改，该怎么办？ 我们可以使用回滚命令轻松回滚通过Liquibase应用的更改。

Most of the refactoring tags like createTable and renameColumn will automatically generate rollback statements. If that doesn’t satisfy your needs, you can add custom rollback statements to a changeset. You can rollback to certain number of previous changesets, to a given date, or a particular tag that you previously created.

大多数重构标记(例如createTable和renameColumn将自动生成回滚语句。 如果那不能满足您的需求，则可以将自定义回滚语句添加到变更集。 您可以回滚到一定数量的先前变更集，给定日期或先前创建的特定标签。

liquibase rollbackCount 1

Liquibase allows us to add checkpoints to our database by tagging the current state of it. This helps us easily rollback to a specific state by simply calling its tag.

Liquibase允许我们通过标记数据库的当前状态来向数据库添加检查点。 这可以帮助我们通过简单地调用其标记轻松地回滚到特定状态。

liquibase tag checkpoint_1

You can then rollback to this state after making some changes, by issuing following command.

然后，可以通过发出以下命令在进行一些更改后回滚到此状态。

liquibase rollback checkpoint_1

Sometimes you may want to check the SQL applied for rolling back the changes. To achieve this, replace rollback with rollbackSql and rollbackCount with rollbackCountSql. Liquibase will print the result to standard output, which you can redirect to a file and save for later execution.

有时您可能需要检查应用于回滚更改SQL。 为此，请将rollback替换为rollbackSql并将rollbackCount替换为rollbackCountSql 。 Liquibase会将结果打印到标准输出，您可以将其重定向到文件并保存以供以后执行。

You can specify custom statements to be executed in a rollback tag for each changelog. When you perform a rollback, Liquibase will apply these statements instead of the default rollback SQL.

您可以在每个变更日志的rollback标记中指定要执行的自定义语句。 当执行回滚时，Liquibase将应用这些语句，而不是默认的回滚SQL。

<changeSet id="6" author="shameer">
 <modifyDataType columnName="value" newDataType="TEXT" tableName="temp"/>
 <rollback>
  <dropTable tableName="temp"/>
 </rollback>
</changeSet>

You can verify it by creating rollback SQL after applying the changeset.

您可以通过在应用变更集之后创建回滚SQL来进行验证。

liquibase rollbackCountSql 1

-- Lock Database
-- Rolling Back ChangeSet: db.changelog.xml::6::shameer::(Checksum: 3:29343b94088e34367e51a6633f572b81)
DROP TABLE `temp`;

DELETE FROM `DATABASECHANGELOG`  WHERE ID='6' AND AUTHOR='shameer' AND FILENAME='db.changelog.xml';

-- Release Database Lock
Liquibase Rollback Successful

有条件地应用更改 (Conditionally Applying Changes)

Sometimes you may want to check for a certain condition before executing a changeset, such as making sure a table contains no data before you drop it. preConditions is a useful tag that lets you check for various conditions in your database.

有时，您可能需要在执行变更集之前检查某种条件，例如在删除表之前确保表中没有数据。 preConditions是一个有用的标记，可让您检查数据库中的各种条件。

<changesSet id="8" author="shameer">
 <preConditions onFail="CONTINUE">
  <sqlCheck expectedResult="0">select count(*) from user</sqlCheck>
 </preConditions>
 <dropTable tableName="user"/>
</changesSet>

In this case, Liquibase will first check if the user table contains any records, and if so then it will continue without executing this changeset. If the condtion is critical, you can halt the execution by setting onFail="HALT" instead of CONTINUE.

在这种情况下，Liquibase将首先检查用户表是否包含任何记录，如果包含，则它将继续而不执行此更改集。 如果条件很关键，可以通过设置onFail="HALT"而不是CONTINUE来暂停执行。

Preconditions have many other handy features such as checking which database system is using, the username of current user, etc. which you can read more about in their manual.

前提条件还有许多其他方便的功能，例如检查正在使用哪个数据库系统，当前用户的用户名等，您可以在其手册中了解更多信息 。

摘要 (Summary)

In this article we have talked about Liquibase, a database refactoring and change management tool. The most important use of Liquibase is to sync database changes with multiple developers in a team; when you make some changes in local database, other developers can pull those changes and apply in their machines. Your changes will be versioned in each changesets and you can easily rollback to previous state if something goes wrong. Apart from that, you can use Liquibase for database migration and deployment. I’ve not covered all the great features of Liquibase in this article, but Liquibase has a very good manual which you should definitely read.

在本文中，我们讨论了数据库重构和变更管理工具Liquibase。 Liquibase最重要的用途是与团队中的多个开发人员同步数据库更改。 当您在本地数据库中进行某些更改时，其他开发人员可以提取这些更改并应用到他们的计算机中。 您的更改将在每个变更集中进行版本控制，如果出现问题，您可以轻松回滚到以前的状态。 除此之外，您可以使用Liquibase进行数据库迁移和部署。 我没有在本文中介绍Liquibase的所有出色功能，但是Liquibase有非常好的手册 ，您一定要阅读。

Image via Fotolia

图片来自Fotolia

翻译自: https://www.sitepoint.com/versioning-your-database-with-liquibase/

liquibase 使用