Detailed Explanation of IOS Database Upgrade Data Migration Example

2021-09-05 01:07:46
OfStack

Summary:

A long time ago, I encountered the reference scenario of database version upgrade. At that time, the practice was to simply delete the old database files. Rebuilding the database and table structure, This escalation of violence will lead to the loss of old data. Now it seems that this is not an elegant solution. Now a database is used in a new project. 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. We all want to do better, don't we?

Ideally: Database upgrade, There are changes in table structure, primary keys and constraints, After the new table structure is established, data will be automatically retrieved from the old table. Mapping the same fields to migrate data, However, the database version upgrade in most business scenarios only involves the increase or decrease of fields and the modification of primary key constraints, so the following scheme to be implemented is also implemented from the most basic and commonly used business scenarios. As for more complex scenarios, it can be expanded on this basis to meet its own expectations.

Type selection and finalization

Search on the Internet, there is no simple and complete solution for database upgrade data migration, and I found some ideas

1. Clear old data and rebuild tables

Advantages: Simple
Disadvantages: Data loss

2. Modify the table structure based on the existing table

Advantages: Ability to retain data
Disadvantages: The rules are cumbersome. It is cumbersome and troublesome to establish a field configuration file of a database, then read the configuration file, execute SQL to modify table structure, constraints and primary keys, etc., and involve database upgrade across multiple versions

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

Advantages: It can retain data, support the modification of table structure, constraint and change of primary key, and it is relatively simple to implement
Disadvantages: There are many steps to implement

Considering comprehensively, the third method is a reliable scheme.

Main steps

According to this idea, the main steps of database upgrade under 1 are analyzed as follows:

Get the old table in the database Modify the table name, add the suffix "_ bak", and treat the old table as a backup table Create a new table Get the newly created table Traverse the old table and the new table, and compare the fields of the table to be migrated Data migration processing Delete backup table

Analysis of SQL statements used

These operations are related to database operations, so the key to the problem is the SQL statement corresponding to the steps. The main SQL statements used below are analyzed:

Get the old table in the database


SELECT * from sqlite_master WHERE type='table'

As a result, we can see that there are database fields such as type name tbl_name rootpage sql, and we only need to use name, that is, the database name field.


sqlite> SELECT * from sqlite_master WHERE type='table'
 ...> ;
+-------+---------------+---------------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| type | name   | tbl_name  | rootpage | sql                                                     |
+-------+---------------+---------------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table | t_message_bak | 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  | t_message  | 4  | CREATE TABLE t_message (
 messageID TEXT, 
 messageType INTEGER,
 messageJsonContent TEXT, 
 retriveTimeString INTEGER, 
 postTimeString INTEGER, 
 readState INTEGER, 
 addColumn INTEGER,
 PRIMARY KEY(messageID)
) |
+-------+---------------+---------------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2  Row on dataset  (0.03  Seconds )

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


--  Put t_message Table is modified to t_message_bak Table  
ALTER TABLE t_message RENAME TO t_message_bak

Get table field information


--  Get t_message_bak Field information for the table 
PRAGMA table_info('t_message_bak')

The obtained table field information is as follows. We can see that there are database fields such as cid name type notnull dflt_value pk. We only need to use name, that is, the field name


sqlite> PRAGMA table_info('t_message_bak');
+------+--------------------+---------+---------+------------+------+
| 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  Row on dataset  (0.01  Seconds )

Using subqueries for data migration processing


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 fields messageID, messageType, messageJsonContent, retriveTimeString, postTimeString, readState in the t_message_bak table to the t_message table

Code implementation

The next step is to implement the code


//  Create a new temporary table, import data into the temporary table, and then 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);

 //  Database version upgrade 
 if (version_old != nil && ![version_new isEqualToString:version_old]) {

  //  Get the old table in the database 
  NSArray* existsTables = [self sqliteExistsTables];
  NSMutableArray* tmpExistsTables = [NSMutableArray array];

  //  Modify table name , Add suffix " _bak ", using the old table as a backup table 
  for (NSString* tablename in existsTables) {
   [tmpExistsTables addObject:[NSString stringWithFormat:@"%@_bak", tablename]];
   [self.databaseQueue inDatabase:^(FMDatabase *db) {
    NSString* sql = [NSString stringWithFormat:@"ALTER TABLE %@ RENAME TO %@_bak", tablename, tablename];
    [db executeUpdate:sql];
   }];
  }
  existsTables = tmpExistsTables;

  //  Create a new table 
  [self initTables];

  //  Get the newly created table 
  NSArray* newAddedTables = [self sqliteNewAddedTables];

  //  Traverse the old table and the new table, and compare the fields of the table to be migrated 
  NSDictionary* migrationInfos = [self generateMigrationInfosWithOldTables:existsTables newTables:newAddedTables];

  //  Data migration processing 
  [migrationInfos enumerateKeysAndObjectsUsingBlock:^(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 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*)generateMigrationInfosWithOldTables:(NSArray*)oldTables newTables:(NSArray*)newTables {
 NSMutableDictionary<NSString*, NSArray* >* migrationInfos = [NSMutableDictionary dictionary];
 for (NSString* newTableName in newTables) {
  NSString* oldTableName = [NSString stringWithFormat:@"%@_bak", newTableName];
  if ([oldTables containsObject:oldTableName]) {
   //  Get table database field information 
   NSArray* oldTableColumns = [self sqliteTableColumnsWithTableName:oldTableName];
   NSArray* newTableColumns = [self sqliteTableColumnsWithTableName:newTableName];
   NSArray* publicColumns = [self publicColumnsWithOldTableColumns:oldTableColumns newTableColumns:newTableColumns];

   if (publicColumns.count > 0) {
    [migrationInfos setObject:publicColumns forKey:newTableName];
   }
  }
 }
 return migrationInfos;
}

- (NSArray*)publicColumnsWithOldTableColumns:(NSArray*)oldTableColumns newTableColumns:(NSArray*)newTableColumns {
 NSMutableArray* publicColumns = [NSMutableArray array];
 for (NSString* oldTableColumn in oldTableColumns) {
  if ([newTableColumns containsObject: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

sqlite Remove Table File Size Constant Problem

If you have any questions, please leave a message or go to this site community to exchange and discuss, thank you for reading, hope to help everyone, thank you for your support to this site!