Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to change the database". In the daily operation, I believe that many people have doubts about how to change the database. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubt of "how to change the database"! Next, please follow the editor to study!

After thousands of difficult and dangerous development, the system has finally come online and entered a more thrilling and exciting stage of catching insects. In the process of tinkering and leaving a mountain of shit to posterity, we need to tidy up the database.

Come to think of it, we changed the names of several fields in the development environment, went through the baptism of the test environment, and made a pre-launch to accept the changes. As a result, just forget to operate one of the SQL online, all previous efforts have been wasted. If you are working on a project type and customers upgrade only once every six months, the management of these scripts will become even more messy.

We need to manage these database changes using tools like git, which can be changed automatically when the system starts up. Spread by word of mouth, it's unbelievable.

No one wants to carry the pot. Flipping through chat records? Does it work? It was all after the event, Zhuge Liang.

The difference between people and animals is the use of tools. The two that are often used are Liquibase and Flyway. However, Liquibase's migration script is too complex to write and takes a lot of time to maintain, far less than out-of-the-box (at the expense of cross-platform) like Flyway. If your project is not very complex and you are not familiar with Liquibase, it is recommended to choose flyway directly.

In general, database changes, there will be the following statements, we can use flyway to complete.

Statements used by DDL when building tables and indexes, such as CREATE, ALTER, DROP, etc.

DML is some common data manipulation statements, such as update, delete, insert

Statements used by DCL to set and manage permission information, such as grant, deny, revoke, etc.

Next, take flyway as an example to see how the version of the database has been changed.

1. Flyway migrate

First, use the command of mvn to create a demonstration project.

Mvn archetype:generate-B\-DarchetypeGroupId=org.apache.maven.archetypes\-DarchetypeArtifactId=maven-archetype-quickstart\-DarchetypeVersion=1.1\-DgroupId=foo\-DartifactId=bar\-Dversion=1.0-SNAPSHOT\-Dpackage=foobar

Add the following to the pom.xml file:

Org.flywaydb flyway-maven-plugin 7.3.1 jdbc:h3:file:./target/foobar sa com.h3database h3 1.4.200

Create a database change directory.

Mkdir-p src/main/resources/db/migration

Create a new database file, which we call the first version: src/main/resources/db/migration/V1__Create_person_table.sql

Create table PERSON (ID int not null, NAME varchar (100) not null)

Using the mvn command, you can complete the database update. Don't be afraid, this command is idempotent.

Mvn flyway:migrate

The terminal will output the following:

[INFO] Database: jdbc:h3:file:./target/foobar (H 21.4) [INFO] Successfully validated 1 migration (execution time 0015 00.009s) [INFO] Creating Schema History table: "PUBLIC". "flyway_schema_history" [INFO] Current version of schema "PUBLIC": > [INFO] Migrating schema "PUBLIC" to version 1-Create person table [INFO] Successfully applied 1 migration to schema "PUBLIC" (execution time 00v 00.038s)

Next, we prepare the second change, and again, we create the second version of the file: src/main/resources/db/migration/V2__Add_people.sql

The following is the contents of the SQL file:

Insert into PERSON (ID, NAME) values (1, 'Axel'); insert into PERSON (ID, NAME) values (2,' Mr. Foo'); insert into PERSON (ID, NAME) values (3,'Ms. Bar')

Execute mvn flyway:migrate again, and you can find the second version of DML information, which has been written to the database.

two。 How do you work?

So how does flyway achieve idempotency? We use DBeaver to open this H3 file.

At the JDBC connection, enter: jdbc:h3:/private/tmp/bar/target/foobar.mv.db; and select H2 Embedded mode. We found that in addition to the user-created PERSON table, there is also a table called flyway_schema_history in the database.

Let's see the contents. Using the mvn flyway:info command, you can see the same content.

As you can see, this idempotent operation is actually guaranteed by an automatically created state table. There is also a field called checksum, and Riemann stores a CRC32 value to determine whether your SQL file has been illegally tampered with (tampered with will not be passed).

Poked through this layer of window paper, all the magical things suddenly became clear. So if you want to use flyway, your account, you should at least give create table permission, otherwise you will need to build this table manually.

From the definition of SQL files above, we can also see that these files need to follow certain rules. Roughly as shown in the above figure, it includes:

Prefix

Version number

Separator

Version description

Flyway relies on this convention to make changes to the database table. Therefore, it is necessary to name Sql files in strict accordance with its requirements.

3. SpringBoot project integration

Add the coordinates of flyway to the pom. You can see that we did not provide a version number, indicating that it has been defined in the bom file. Its autoconfigure is provided by default in SpringBoot's autoconfigure package.

Org.flywaydb flyway-core...

Of course, we should take a look at the configuration items defined in the FlywayProperties file. You can see its prefix, which is spring.flyway.

The default DB change file is placed in classpath:db/migration, or we can customize one through locations configuration, such as classpath:cn/xjjdog/flyway. Of course, you can also define the name of that his table through the table attribute. Url, user, password, etc., can also be provided to store the his table in a different place from the business table. If not, the library defined by datasource will be used by default.

Therefore, the minimum configuration is to do nothing, just throw the change file under the change directory.

Spring: datasource: # jdbc configuration. Flyway: enable: true locations: classpath:cn/xjjdog/flyway at this point, the study on "how to change the database" is over. I hope I can solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.