Detailed examples of IOS database upgrade and data migration-Mysql Tutorial-php.cn

This article mainly introduces relevant information on detailed examples of IOS database upgrade and data migration. Here are examples to help you solve the problems of database upgrade and data migration. Friends in need can refer to it. I hope it can help everyone.

Detailed explanation of examples of data migration for IOS database upgrade

Summary:

I have encountered database version upgrades a long time ago To quote the scenario, the approach at that time was to simply delete the old database files and rebuild the database and table structure. This violent upgrade method would lead to the loss of old data. Now it seems that this is not an elegant solution. Now it is The database is used again in the 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, right? ?

The ideal situation is: the database is upgraded and the table structure, primary keys and constraints are changed. After the new table structure is established, the data will be automatically retrieved from the old table, and the same fields will be mapped and migrated. 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 also based on the most basic and commonly used business scenarios. As for the more complicated ones, The scenarios can be expanded on this basis to meet your own expectations.

Selection and finalization

After searching online, there is no simple and complete solution for database upgrade and data migration, and I found some ideas

1. Clear old data and rebuild Table

Advantages: Simple
Disadvantages: Data loss

2. Modify the table structure based on the existing table

Advantages: Able to retain data
Disadvantages: The rules are relatively cumbersome. It is necessary to create a database field configuration file, then read the configuration file, execute SQL to modify the table structure, constraints, primary keys, etc., involving multiple versions. Database upgrade becomes cumbersome and troublesome

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 modification of table structure, changes of constraints and primary keys, and is relatively simple to implement.
Disadvantages: There are many steps to implement.

All things considered, the third method is a more reliable solution.

Main steps

Based on this idea, the main steps of database upgrade are analyzed as follows:

Get the database Old table
Modify the table name, add the suffix "_bak", and use the old table as a backup table
Create a new table
Get the newly created table
Traverse the old table and the new table, compare and extract the fields of the table that need 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 step. The main SQL statements used are analyzed below:

Get the old table in the database

SELECT * from sqlite_master WHERE type=&#39;table&#39;

Copy after login

The results are as follows. You can see that there are database fields such as type | name | tbl_name | rootpage | sql. We only need to use the name, which is the database name field.

sqlite> SELECT * from sqlite_master WHERE type=&#39;table&#39;
 ...> ;
+-------+---------------+---------------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 行于数据集 (0.03 秒)

Copy after login

Modify the table name, add the suffix "_bak", and use the old table as a backup table

-- 把t_message表修改为t_message_bak表 
ALTER TABLE t_message RENAME TO t_message_bak

Copy after login

Get table field information

-- 获取t_message_bak表的字段信息
PRAGMA table_info(&#39;t_message_bak&#39;)

Copy after login

sqlite> PRAGMA table_info(&#39;t_message_bak&#39;);
+------+--------------------+---------+---------+------------+------+
| 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 行于数据集 (0.01 秒)

Copy after login

Use subquery 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 after login

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

Code implementation

Then comes the steps to implement the code

// 创建新的临时表，把数据导入临时表，然后用临时表替换原表
- (void)baseDBVersionControl {
 NSString * version_old = ValueOrEmpty(MMUserDefault.dbVersion);
 NSString * version_new = [NSString stringWithFormat:@"%@", DB_Version];
 NSLog(@"dbVersionControl before: %@ after: %@",version_old,version_new);

 // 数据库版本升级
 if (version_old != nil && ![version_new isEqualToString:version_old]) {

  // 获取数据库中旧的表
  NSArray* existsTables = [self sqliteExistsTables];
  NSMutableArray* tmpExistsTables = [NSMutableArray array];

  // 修改表名,添加后缀“_bak”，把旧的表当做备份表
  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;

  // 创建新的表
  [self initTables];

  // 获取新创建的表
  NSArray* newAddedTables = [self sqliteNewAddedTables];

  // 遍历旧的表和新表，对比取出需要迁移的表的字段
  NSDictionary* migrationInfos = [self generateMigrationInfosWithOldTables:existsTables newTables:newAddedTables];

  // 数据迁移处理
  [migrationInfos enumerateKeysAndObjectsUsingBlock:^(NSString* newTableName, NSArray* publicColumns, BOOL * _Nonnull stop) {
   NSMutableString* colunmsString = [NSMutableString new];
   for (int i = 0; i* migrationInfos = [NSMutableDictionary dictionary];
 for (NSString* newTableName in newTables) {
  NSString* oldTableName = [NSString stringWithFormat:@"%@_bak", newTableName];
  if ([oldTables containsObject:oldTableName]) {
   // 获取表数据库字段信息
   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* 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* existsTables = [NSMutableArray array];
 [self.databaseQueue inDatabase:^(FMDatabase *db) {
  NSString* sql = @"SELECT * from sqlite_master WHERE type=&#39;table&#39;";
  FMResultSet *rs = [db executeQuery:sql];
  while ([rs next]) {
   NSString* tablename = [rs stringForColumn:@"name"];
   [existsTables addObject:tablename];
  }
 }];
 return existsTables;
}

- (NSArray*)sqliteNewAddedTables {
 __block NSMutableArray* newAddedTables = [NSMutableArray array];
 [self.databaseQueue inDatabase:^(FMDatabase *db) {
  NSString* sql = @"SELECT * from sqlite_master WHERE type=&#39;table&#39; AND name NOT LIKE '%_bak'";
  FMResultSet *rs = [db executeQuery:sql];
  while ([rs next]) {
   NSString* tablename = [rs stringForColumn:@"name"];
   [newAddedTables addObject:tablename];
  }
 }];
 return newAddedTables;
}

Copy after login

Related recommendations:

sql 2005 Database Upgrade 2008 Database and 2005 Data Attachment 2008 Data backup document

SQL server database upgrade version problem solution

Detailed explanation of the method summary of migrating Oracle database to MySQL (picture and text)

The above is the detailed content of Detailed examples of IOS database upgrade and data migration. For more information, please follow other related articles on the PHP Chinese website!