Detailed example of IOS database upgrade data migration

SELECT * from sqlite_master WHERE type='table'

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 rows in data set (0.03 seconds)

-- Change the t_message table to the t_message_bak table ALTER TABLE t_message RENAME TO t_message_bak

-- Get the field information of the t_message_bak table PRAGMA table_info('t_message_bak')

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 rows in data set (0.01 seconds)

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

// Create a new temporary table, import the 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 tables in the database NSArray* existsTables = [self sqliteExistsTables];
  NSMutableArray* tmpExistsTables = [NSMutableArray array];

  // Modify the table name, add the suffix "_bak", and use 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, compare and extract the fields of the table that needs 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 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*)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;
}