Shulou(Shulou.com)06/02 Report--

Introduction to Flyway function and sql naming convention

Flyway is an open source database sql script version management tool.

Its principle is: generate a version table in schema, record the sql script version number of this update, and ensure that the version number corresponds to the database.

You need to use the following version specification when defining sql script names:

1. Prefix (configurable, default: v)

2. Version

3. Delimiter (two underscores)

4. Description (underscore or space separation)

5. Suffix (configurable, default: SQL)

Such as: V1_Create_person_table.sql V2_Insert_persons.sql

Create_person_table and Insert_persons are both descriptions of script functions.

Introduction to Flyway directory structure and configuration

The specific integration process is as follows:

The directory structure is the same as that of maven, as follows:

You can see that there is a configuration file for flyway and a pom file for the project at the same level as src.

The content of the configuration file flyway.properties is as follows, the main configuration is some database connection information. It is clear at a glance:

Flyway.driver=oracle.jdbc.OracleDriver

Flyway.url=jdbc:oracle:thin:@192.168.2.200:1521/orcl

Flyway.user=qq

Flyway.password=qq

Flyway.schemas=FLYWAY

Flyway_table=qq_version

Flyway.placeholders.keyABC=valueXYZ

Flyway.placeholders.otherplaceholder=value123

Add the plugin information of flyway, as follows:

Remember that the driver package for the database must appear in the dependency information of the pom file:

Among them, the one under the lib directory is flyway's jar package flyway-commandline-4.0-sources.jar.

Keep in mind that flyway's sql script storage directory defaults to resources/db/ migration.

You can see that I have two scripts in the migration directory, as follows:

V1__Create_person_table.sql:

Create table PERSON (

NAME varchar (80) not null

);

V2__Insert_persons.sql:

INSERT INTO PERSON (NAME) VALUES ('Peter Meyer')

INSERT INTO PERSON (NAME) VALUES ('Peter Bonnd')

INSERT INTO PERSON (NAME) VALUES ('Klara Korn')

Introduction to Jenkins Project configuration

After the configuration is completed, you need to configure the project on jenkins. The only thing you need to pay attention to here is the construction parameters:

Mvn flyway:init (initialize Flyway metadata)

Mvn flyway:migrate (perform Flyway upgrade operation)

Mvn flyway:validate (verify the correctness of Flyway data)

Here are my build parameters:

-X-Dflyway.configFile=flyway.properties flyway:migrate-Dmaven.test.skip=true

Parameter description:-X: debug level build, print detailed construction process

-Dflyway.configFile= specifies the corresponding flyway configuration file

-Dmaven.test.skip=true skips test cases

Common error analysis 1. Build failed. Create schema does not have permission.

Remember that there is a parameter in the configuration file:

Flyway.schemas=qq

Here, the user must have the permission to create schema, so the user is granted dba permission:

Recompile execution passed.

two。 The generated table cannot be found after building:

Because schema_version records database version information, flyway protects it. If you want to view this table, you must put double quotation marks on the table name, such as: select * from "schema_version"

Here is an explanation:

1. For the construction of schema, when using jenkins to build, flyway will first check whether the schema exists. If there is no creation, it will be skipped. Therefore, it is recommended that you create a schema before using flyway to build.

two。 Since the default user name of oracle is uppercase when writing the configuration file, please also mark the schema in the configuration file as uppercase.

View the construction results

You can view the generated schema_version information in the database:

This table records the sql script version of the database. Subsequent updates will also get the current database version information from this table.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.