本文共 16212 字,大约阅读时间需要 54 分钟。
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。
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 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应用程序,这意味着您可以并在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
标记,它将应用更改。
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.
这是没有变更集的变更日志文件的第一个版本。
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 lockINFO 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 lockLiquibase 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.propertiesdriver: com.mysql.jdbc.Driverclasspath: ../lib/mysql-connector-java-5.1.21-bin.jarurl: jdbc:mysql://localhost/applicationchangeLogFile: db.changelog.xmlusername: dbuserpassword: 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:
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 updateINFO 8/4/12 7:16 AM:liquibase: Successfully acquired change log lockINFO 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 74msINFO 8/4/12 7:16 AM:liquibase: Successfully released change log lockLiquibase 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: PRIDefault: 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: UNIDefault: 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
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
列。
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:
以下是为数据库中新创建的表生成更改日志文件的步骤:
Take a backup of database using mysqldump
使用mysqldump
备份数据库
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
附加一个新的变更集,用于在数据库中创建缺少的表。
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列,这将导致数据丢失。
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。
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 LockLiquibase Rollback Successful
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
是一个有用的标记,可让您检查数据库中的各种条件。
select count(*) from user
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 in their manual.
前提条件还有许多其他方便的功能,例如检查正在使用哪个数据库系统,当前用户的用户名等,您可以在其手册中 。
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 which you should definitely read.
在本文中,我们讨论了数据库重构和变更管理工具Liquibase。 Liquibase最重要的用途是与团队中的多个开发人员同步数据库更改。 当您在本地数据库中进行某些更改时,其他开发人员可以提取这些更改并应用到他们的计算机中。 您的更改将在每个变更集中进行版本控制,如果出现问题,您可以轻松回滚到以前的状态。 除此之外,您可以使用Liquibase进行数据库迁移和部署。 我没有在本文中介绍Liquibase的所有出色功能,但是Liquibase有 ,您一定要阅读。
Image via
图片来自
翻译自:
liquibase 使用
转载地址:http://hmrgb.baihongyu.com/