Detailed description of instances for migrating IOS database upgrading data, and detailed description of ios database upgrading data

Detailed description of instances for migrating IOS database upgrading data, and detailed description of ios database upgrading data

Detailed description of IOS database upgrading data migration instances

Summary:

A long time ago, I encountered a reference scenario for database version upgrade. At that time, I simply deleted the old database file and rebuilt the database and table structure, this violent upgrade will lead to the loss of old data. Now it seems that this is not an elegant solution. Now a new project uses a database, I have to reconsider this problem. I hope to solve this problem in a more elegant way. We will encounter similar scenarios in the future. Do we all want to do better?

Ideally, the database is upgraded and the table structure, primary keys, and constraints change. After the new table structure is created, data is automatically retrieved from the old table, and the same fields are mapped to the migrated data, in most business scenarios, database version upgrades only involve adding or removing fields and modifying primary key constraints, therefore, the solution to be implemented below is to implement the most basic and common business scenarios. For more complex scenarios, it can be expanded on this basis, meet your expectations.

Type Selection finalized

After searching online, there is no simple and complete solution for Database Upgrade and data migration, and some ideas have been found.

1. Clear old data and recreate the table

Advantage: simple
Disadvantage: data loss

2. Modify the table structure based on the existing table

Advantage: data can be retained
Disadvantage: The rules are cumbersome. You need to create a database field configuration file, read the configuration file, and execute SQL statements to modify the table structure, constraints, and primary keys, database upgrades involving multiple versions become cumbersome and troublesome.

3. Create a temporary table, copy the old data to the temporary table, delete the old data table, and set the temporary table as a data table.

Advantages: data can be retained, and table structure modification, constraints, and primary key modification are supported, which is easy to implement.
Disadvantage: There are many implementation steps

The third method is a reliable solution.

Main Steps

According to this idea, the main steps for upgrading the database are as follows:

• Obtain the old table in the database
• Modify the table name, add the suffix "_ bak", and use the old table as a backup table.
• Create a new table
• Obtain the newly created table
• Traverse the old and new tables and compare the fields of the tables to be migrated.
• Data Migration
• Delete backup table
  

SQL Statement Analysis

These operations are related to database operations, so the key to the problem is the SQL statement corresponding to the step. The main SQL statements used in the following analysis are as follows:

Obtain the old table in the database

SELECT * from sqlite_master WHERE type='table'

The result is as follows. You can see the database fields such as type | name | tbl_name | rootpage | SQL. You only need to use the field name, that is, the database name.

Sqlite> SELECT * from sqlite_master WHERE type = 'table'...>; + ------- + --------------- + ------------- + ---------- + response + | type | name | tbl_name | rootpage | SQL | + ------- + --------------- + ---------- + response + | table | external | t_message_bak | 2 | create table "t_message_bak" (messageID TEXT, messageType INTEGER, messageJsonContent TEXT, retriveTimeString INTEGER, postTimeString INTEGER, readState INTEGER, primary key (messageID) | table | t_message | 4 | create table t_message (messageID TEXT, messageType INTEGER, messageJsonContent TEXT, retriveTimeString INTEGER, postTimeString INTEGER, readState INTEGER, addColumn INTEGER, primary key (messageID )) | + ------- + --------------- + ------------- + ---------- + hour + 2 rows on the dataset (0.03 seconds)

Modify the table name, add the suffix "_ bak", and use the old table as a backup table.

-- Change t_message table to t_message_bak table alter table t_message rename to t_message_bak

Obtain table Field Information

-- Obtain the field information of the t_message_bak table PRAGMA table_info ('t_ message_bak ')

The obtained table fields are as follows: | cid | name | type | notnull | dflt_value | pk | these database fields, we only need to use the name field.

Sqlite> PRAGMA table_info ('t_ message_bak '); + ------ + certificate + --------- + ------------ + ------ + | cid | name | type | notnull | dflt_value | pk | + ------ + -------------------- + --------- + ------------ + ------ + | 0 | messageID | TEXT | 0 | NULL | 1 | 1 | messageType | INTEGER | 0 | NULL | 0 | 2 | messageJsonContent | TEXT | 0 | NULL | 0 | 3 | retriveTimeString | INTEGER | 0 | NULL | 0 | 4 | postTimeString | INTEGER | 0 | NULL | 0 | 5 | readState | INTEGER | 0 | NULL | 0 | + ------ + -------------------- + --------- + ------------ + ------ + 6 rows on the dataset (0.01 seconds)

Use subqueries for data migration

INSERT INTO t_message(messageID, messageType, messageJsonContent, retriveTimeString, postTimeString, readState) SELECT messageID, messageType, messageJsonContent, retriveTimeString, postTimeString, readState FROM t_message_bak

Copy the values of the messageID, messageType, messageJsonContent, retriveTimeString, postTimeString, and readState fields in the t_message_bak table to the t_message table.

Code Implementation

Next we will go to the implementation steps of the Code.

// Create a new temporary table, import data to the temporary table, and replace the original table with the temporary table-(void) baseDBVersionControl {NSString * version_old = ValueOrEmpty (MMUserDefault. dbVersion); NSString * version_new = [NSString stringWithFormat: @ "% @", DB_Version]; NSLog (@ "dbVersionControl before: % @ after: % @", version_old, version_new ); // upgrade the database version if (version_old! = Nil &&! [Version_new isw.tostring: version_old]) {// obtain the old table NSArray * existsTables = [self defined]; NSMutableArray * tmpExistsTables = [NSMutableArray array]; // modify the table name, add the suffix "_ bak" and use the old table as the backup table for (NSString * tablename in existsTables) {[tmpExistsTables addObject: [NSString stringWithFormat: @ "% @ _ bak ", tablename]; [self. databaseQueue inDatabase: ^ (FMDatabase * db) {NSString * SQL = [NSString string WithFormat: @ "alter table % @ rename to % @ _ bak", tablename, tablename]; [db executeUpdate: SQL] ;}];} existsTables = tmpExistsTables; // create a new table [self initTables]; // obtain the newly created table NSArray * newAddedTables = [self sqliteNewAddedTables]; // traverse the old and new tables, compare the field NSDictionary * migrationInfos = [self generateMigrationInfosWithOldTables: existsTables newTables: newAddedTables] In the table to be migrated; // data migration process [migrationInfos enumerate Struct: ^ (NSString * newTableName, NSArray * publicColumns, BOOL * _ Nonnull stop) {NSMutableString * colunmsString = [NSMutableString new]; for (int I = 0; I <publicColumns. count; I ++) {[colunmsString appendString: publicColumns [I]; if (I! = PublicColumns. count-1) {[colunmsString appendString: @ ","] ;}} NSMutableString * SQL = [NSMutableString new]; [SQL appendString: @ "INSERT INTO"]; [SQL appendString: newTableName]; [SQL appendString: @ "("]; [SQL appendString: colunmsString]; [SQL appendString: @ ")"]; [SQL appendString: @ "SELECT"]; [SQL appendString: colunmsString]; [SQL appendString: @ "FROM"]; [SQL appendFormat: @ "% @ _ bak", newTableName]; [self. databaseQueue inDatabase: ^ (FMDatabase * db) {[db executeUpdate: SQL] ;}]; // Delete the backup table [self. databaseQueue inDatabase: ^ (FMDatabase * db) {[db beginTransaction]; for (NSString * oldTableName in existsTables) {NSString * SQL = [NSString stringWithFormat: @ "drop table if exists % @", oldTableName]; [db executeUpdate: SQL];} [db commit] ;}]; MMUserDefault. dbVersion = version_new;} else {MMUserDefault. dbVersion = version_new; }}- (NSDictionary *) random :( NSArray *) oldTables newTables :( NSArray *) newTables {random <NSString *, NSArray *> * migrationInfos = [random dictionary]; for (NSString * newTableName in newTables) {NSString * oldTableName = [NSString stringWithFormat: @ "% @ _ bak", newTableName]; if ([oldTables containsObject: oldTableName]) {// obtain table database Field Information NSArray * oldTableColumns = [self defined: oldTableName]; NSArray * newTableColumns = [self defined: newTableName]; NSArray * publicColumns = [self publicColumnsWithOldTableColumns: oldTableColumns newTableColumns: newTableColumns]; if (publicColumns. count> 0) {[migrationInfos setObject: publicColumns forKey: newTableName] ;}}return migrationInfos ;}- (NSArray *) rows :( NSArray *) oldTableColumns newTableColumns :( NSArray *) newTableColumns {attributes * publicColumns = [inline array]; for (NSString * oldTableColumn in oldTableColumns) {if ([newTableColumns attributes: oldTableColumn]) {[publicColumns addObject: oldTableColumn];} return publicColumns;}-(NSArray *) sqliteTableColumnsWithTableName :( NSString *) tableName {_ block NSMutableArray <NSString *> * tableColumes = [NSMutableArray array]; [self. databaseQueue inDatabase: ^ (FMDatabase * db) {NSString * SQL = [NSString stringWithFormat: @ "PRAGMA table_info ('% @')", tableName]; FMResultSet * rs = [db executeQuery: SQL]; while ([rs next]) {NSString * columnName = [rs stringForColumn: @ "name"]; [tableColumes addObject: columnName] ;}}]; return tableColumes;}-(NSArray *) sqliteExistsTables {_ block NSMutableArray <NSString *> * existsTables = [NSMutableArray array]; [self. databaseQueue inDatabase: ^ (FMDatabase * db) {NSString * SQL = @ "SELECT * from sqlite_master WHERE type = 'table'"; FMResultSet * rs = [db executeQuery: SQL]; while ([rs next]) {NSString * tablename = [rs stringForColumn: @ "name"]; [existsTables addObject: tablename] ;}]; return existsTables ;} -(NSArray *) sqliteNewAddedTables {_ block NSMutableArray <NSString *> * newAddedTables = [NSMutableArray array]; [self. databaseQueue inDatabase: ^ (FMDatabase * db) {NSString * SQL = @ "SELECT * from sqlite_master WHERE type = 'table' AND name NOT LIKE '% _ bak '"; FMResultSet * rs = [db executeQuery: SQL]; while ([rs next]) {NSString * tablename = [rs stringForColumn: @ "name"]; [newAddedTables addObject: tablename] ;}}]; return newAddedTables ;}

Problem

The size of the table File deleted by sqlite remains unchanged.

If you have any questions, please leave a message or go to the community on this site for discussion. Thank you for reading this article. Thank you for your support!