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

This article introduces you how to simply analyze database migration, the content is very detailed, interested friends can use for reference, I hope it can be helpful to you.

Now NoSQL is popular, there is also a reason because there is no need to deliberately deal with table's schema, directly store data, so simple! So there will be no migration of database tables. Database table migration has always been a hassle, but I recently used sqlite3 to do a small project, so summarize the database migration plan.

Principle

Each change in the data table corresponds to a database version number

Data migration is gradual, for example, if you upgrade the database version from 1 to n, upgrade NMel once, versions 1 to 2, 2 to 3, until NMel 1 to n.

Implement

1. Use sqlite3's user_version to store a custom database version

/ * set version number * / PRAGMA user_version=1; / * read version number * / PRAGMA user_version

two。 All the database upgrade files, put in a file, directly use sql files, easy to view and manage directly. The file structure is as follows

File structure design

V1.sql v2.sql, v3.sql, etc. are the complete database definition files for each database version.

V1tov2.sql, v2tov3.sql, etc., are interval version database upgrade files. A process of upgrading data from m to n is to run v [m] tov [m + 1] .sql, v [m + 1] tov [m + 2] .sql until v [n-1] tov [n] .sql

Run.sh is the data migration script to run every time, including the current version number and migration logic

V2.sql to v[ n] .sql is not required, just to facilitate viewing the current data table design of *. If v[ n] .sql exists, you can also create a new database directly from this file.

3. The migration script is as follows, which is specified in the specific logic notes.

4. Both v[ n] .sql and v[ n-1] tov [n] .sql files need to set the data version to n through user_version. The demo of a v2tov3.sql is as follows:

Summary

Working with scen

At present, this scheme is suitable for business situations where the amount of data is small and downtime maintenance is acceptable, because downtime upgrades are needed, but this scheme is simple and clear enough to meet all the data upgrades between different versions.

Deficiency and prospect

This scenario does not take into account the rollback of failed data upgrades. Because it is a small business, it is more simple and easy to maintain. So in view of this situation, first of all, make sure that the upgrade script has been tested with enough online data. Second, once a problem occurs, online maintenance can be operated directly and scripts can be written. That said, because you haven't tested this kind of case that requires rollback, and you can't know what this case is like when writing an upgrade script, it's not necessarily feasible or appropriate to write the logic of rollback in advance.

When deploying, the data should be backed up before migration, and the amount of data is larger. Use incremental backup to save time. Backup has mature tools, and backup makes it easy to rollback if upgrade fails. The steps for deployment should be: pull code build (or pull docker image)-> backup database-> upgrade database-> run new code

The logic of data migration is the same for the case where sqlite3 is used in Android,iOS 's devices. Sql file structure design can be reused or written into code to manage. The migration script needs to be converted to Java or Objective-C,Swift code for native.

For larger businesses, multi-instance database migration can use Flyway

On how to simply analyze the database migration to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.