未加星标

MySQL and Liquibase

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二05 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

This post outlines about Liquibase (a database changeset management tool for Java applications), its installation, usage and execution with mysql.

WHAT IS LIQUIBASE?

Liquibase is an open source library to track database changes. Liquibase supports XML, JSON, and YAML files. This blog uses XML files for better understanding. When Liquibase runs, there are several commands it can execute. The most important one is the UPDATE command, which applies database changes from a file. Generally, every application development is built around a concept of a linear database version which starts at version 1. After a change is added, the version is incremented to 2, then 3, etc. This works well for projects where only single person adds the changesets and there is only one branch but fails when different developers add different versions to the database concurrently. Liquibase provides a solution to this issue by using a unique identification scheme for each changeset that is designed to guarantee uniqueness across developers.

WHAT ARE CHANGESETS

Changesets are units of work for Liquibase to apply. It is basically the SQL you want to apply to the database. Each changeset should be a single, independent unit of work. You should never have one changeset applying multiple changes unless it is absolutely necessary. Changelogs are how you tell Liquibase what changesets to apply and in what order. The order in which the changesets appear in the changelog is the order in which they will be executed.


MySQL and Liquibase
LIQUIBASE SPECIFIC TABLES

The first time you run Liquibase it creates two tables in the database: DATABASECHANGELOG and DATABASECHANGELOGLOCK. The lock table is used to prevent concurrent Liquibase runs from stepping on each other. DATABASECHANGELOG is used to keep track of what changesets have been applied to a database. The initial run will obviously be against an empty table, so all changesets will be applied. Liquibase uses a combination of id/author/filepath to create a changeset id.

Installation requirements:

Liquibase 3.x requires Java so let’s get installed first and other pre-requisites for executing Liquibase using Command line option and Maven Plugin.

Install JDK from this location Install JDK Add the location of the bin folder of the JDK installation for the Path variable in System Variables. The following is a typical value for the path variable: “C:\Program Files\Java\jdk1.7.0\bin” Install the Mysql Connector for Java from JDBC Driver for MySQL (Connector/J) To install Liquibase, download the compressed Liquibase Core file from http://www.liquibase.org/download to a local directory.The extracted files contain a liquibase.bat and liquibase shell script for windows and Mac/UNIX systems. Add the directory containing the liquibase.bat file to your system’s PATH. Test the installation by opening a command prompt and entering the following command:
Liquibase version
The result should be something like: Liquibase Version: 3.5.3

Liquibase can be executed in various ways depending on your requirement, depends on what works best for you.There are three main ways to run Liquibase:

Automatically on startup This method works best in environments where you have less control over the deployment process or if you want a simpler deployment process. You can set liquibase to run automatically on startup using built in Spring or Servlet Listener support or interacting with a simple Java API.

Manual deployment You can execute liquibase manually in command line application, or can be run on Ant or Maven (In this blog I will show how to execute using the command line and Maven Plugin). These interfaces allow you to execute Liquibase commands whenever you need, without being tied to application startup.

Executing SQL For those who need to know exactly what is being done to their database, Liquibase supports an “updateSQL” mode in the command line, Ant or Maven interfaces.

Now that you understood how liquibase works and about its installation, let’s get started with its execution. For better understanding I have broadly divided into four steps:

Create a database change log Create a change set inside the change log file. Run the change set against a database via the command line Verify the change in the database.

R unning liquibase using command line interface For demonstration purposes I’ve created a database named ‘liquibasetraining’ on my local MySQL server, as well as a changelog file (db.changelog-1.0.xml). You can keep it in your project folder or in a separate location, but changelog files should be under version control as shown below.

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


MySQL and Liquibase

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=c:/mysql-connector-java-5.1.21-bin.jar --changeLogFile=db.changelog-1.0.xml --url="jdbc:mysql://localhost:3306/liquibasetraining?autoReconnect=true&useSSL=false" --username=root --password=password123 update

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.

When Liquibase connects to the database using the given username and password, it should create two tables in the application database, DATABASECHANGELOG and DATABASECHANGELOGLOCK (as shown below). Each row represents a change made to the database. It contains useful details like id, author, filename, timestamp and tag information.


MySQL and 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 just run liquibase <command>.The properties file would look like this. Here we are using master.xml which has the changelog file db.changelog-1.0.xml.

changeLogFile=C:\liquibase\master.xml driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/liquibasetraining?autoReconnect=true&useSSL=false username=root password=password123

Next let’s create a user table order with id, Ordername and Qty fields by adding a changeset to db.changelog-1.0.xml. In this below example tag element means it applies a tag to the database for future rollback. Here’s the updated XML:

<?xml version="1.0" encoding="UTF-8"?> <databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd"> <changeSet id="1" author="indira"> <createTable tableName="order"> <column name="id" type="int"> <constraints primaryKey="true" nullable="false"/> </column> <column name="Ordername" type="varchar(50)"/> <column name="Qty" type="int"> <!--<constraints nullable="false"/>--> </column> </createTable> </changeSet> <changeSet id="tag-1.0" author="indira"> <tagDatabase tag="1.0" /> </changeSet> </databaseChangeLog>

Now run Liquibase using below command (using liquibase.properties file) and look at the result

liquibase --defaultsFile=c:\liquibase\liquibase.properties --classpath=c:\mysql-connector-java-5.1.40-bin.jar update

If you look at the database , you’ll see the following structure:


MySQL and Liquibase

Running Liquibase using Maven: Till now I showed you how to use command line commands with and without the liquibase.properties file. If the command line interface does not fit your needs, Liquibase can be run on Maven or Ant. Liquibase can be controlled via a Maven plug-in which can be obtained from the central Maven repository. It gives advantages like the control over dependencies, running tests, plugins, versioning your software, etc.

Prerequisites for using Maven Plugin: Install Maven Plugin from: Maven repository . Add the bin directory of the created directory apache-maven-3.3.9 to the PATH environment variable. For more details on installation use this link Confirm with mvn version For Running Liquibase using Maven:

1. Create a Project You need a directory for your project to reside, for this we need to execute the following Maven command on your command line :

mvn archetype:generate -DgroupId=com.mycompany.app -DartifactId=my-app -archetypeArtifactId=maven-archetype-quickstart -DinteractiveMode=false

Running the archetype plugin will create a skeleton Maven project in the directory my-app along with a POM.xml.

2. Add the Liquibase Plugin to your POM The pom.xml file is the core of a project’s configuration in Maven. It is a single configuration file that contains the majority of information required to build a project in just the way you want. The POM structure is a bit complex, but it is not necessary to understand all of the tags but understanding important tags is enough to use it effectively. Once the POM.xml is generated, we will need to add the dependencies the database project relies on. For us, it’s the MySQL JDBC driver and off course Liquibase itself.

Configuration of the plugin is done via the Plugin section of the pom.xml, So i have added the Liquibase Maven Plugin in the section. Below is my POM.xml

<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>se.nrm.mediaserver</groupId> <artifactId>db</artifactId> <version>1.0-SNAPSHOT</version> <packaging>jar</packaging> <name>Liquibase-maven-test</name> <dependencies> <dependency> <groupId>org.liquibase</groupId> <artifactId>liquibase-core</artifactId> <version>3.5.3</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.37</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.liquibase</groupId> <artifactId>liquibase-maven-plugin</artifactId> <version>3.5.3</version> <configuration> <propertyFile>C:\my-app\src\main\resources\liquibase\liquibase.properties</propertyFile> </configuration> <executions> <execution> <goals> <goal>update</goal> </goals> </execution> </executions> </plugin> </plugins> </build> </project>

This is going to add the Liquibase Maven plugin to your project and point your build at a properties file for configuration.

Create a Liquibase Properties File: The property file should contain the following details

changeLogFile=C:/my-app/src/main/resources/liquibase/master_1.0.xml driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/mavenblogproject?autoReconnect=true&useSSL=false username=root password=password123

The best practice is always using a master_1.0.xml file as an entry file. In this master_1.0.xml file, there’s no logic defined, only a set of includes. In our example i have created two change log files db-changelog-1.1.xml(create tables) and db-changelog-1.2.xml(insert tables) as shown below

master_1.0.xml

<?xml version="1.0" encoding="UTF-8"?> <databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.9" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.9 http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd"> <include file="C:\my-app\src\main\resources\liquibase\db-changelog-1.1.xml"/> <include file="C:\my-app\src\main\resources\liquibase\db-changelog-1.2.xml"/> </databaseChangeLog>

db-changelog-1.1.xml

<?xml version="1.0" encoding="UTF-8"?> <databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd"> <changeSet id="1" author="indira"> <createTable tableName="clinicallab"> <column name="id" type="int" autoIncrement="true"> <constraints primaryKey="true" nullable="false"/> </column> <column name="labname" type="varchar(50)"/> <column name="lablocation" type="varchar(50)"> <constraints nullable="false"/> </column> </createTable> </changeSet> <changeSet id="2" author="indira"> <createTable tableName="Department"> <column name="deptid" type="int" autoIncrement="true"> <constraints primaryKey="true" nullable="false"/> </column> <column name="deptname" type="varchar(50)"/> <column name="deptlocation" type="varchar(50)"> <constraints nullable="false"/> </column> </createTable> </changeSet> <changeSet id="tag-1.1" author="indira"> <tagDatabase tag="1.1" /> </changeSet> </databaseChangeLog>

db-changelog-1.2.xml

<?xml version="1.0" encoding="UTF-8"?> <databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd"> <changeSet id="2-insert" author="indira"> <insert tableName="department"> <column name="deptid" value="01"/> <column name="deptname" value="sales"/> <column name="deptlocation" value="India"/> </insert> </changeSet> <changeSet id="tag-1.2" author="indira"> <tagDatabase tag="1.2insert" /> </changeSet> </databaseChangeLog>

Now, you can execute liquibase using Maven command. From the my-app directory execute below command

mvn liquibase:update
MySQL and Liquibase

Now check the database, in the databasechangelog table, all changesets are executed. New tables are created and data is populated in the schema mavenblogproject (as shown below)


MySQL and Liquibase

This blogs cover parts of the overall Liquibase-functionality. After reading this, you should be able to:

Understand how Liquibase works How to apply change log to a database How to execute Liquibase using command line How to execute Liquibase using Maven Plugin

Beyond tracking and applying changes to a database, Liquibase supports many other powerful commands for rolling back changes, generating SQL instead of applying changes and generating documentation. If you want to undo an update, liquibase.bat rollback allows you to roll back changesets based on number of changesets, to a given date, or to a given tag stored in the database.


MySQL and Liquibase

Indira Nellutla

I am an engineer with about 10 years of experience in software development and testing. Prior to joining Qxf2, I worked with reputed companies like Dell and Infosys. They helped me gain good experience in the manufacturing and healthcare domains. My QA career started at Dell. I got exposure to various testing tools, processes and methodologies, got an opportunity to work on various platforms like JMS Hermes, SOAP UI, data integration, queues, etc. I chose Qxf2 because it allowed remote working. My interests are vegetable gardening using organic methods, listening to music and reading books.

2013-2017,Indira Nellutla. All rights reserved.

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

主题: SQLMySQLXMLJavaQtSpringWindowsSOAUTHermes
分页:12
转载请注明
本文标题:MySQL and Liquibase
本站链接:http://www.codesec.net/view/529619.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(60)