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

This article mainly introduces the example analysis of IOS database upgrade data migration, the article is very detailed, has a certain reference value, interested friends must read it!

Detailed explanation of an example of data Migration in IOS Database upgrade

Summary:

I encountered the reference scenario of database version upgrade a long time ago. The practice at that time was to simply delete the old database files and rebuild 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 that 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, and we will encounter similar situations in the future. We all want to do better, don't we?

Ideally, the database is upgraded and the table structure, primary keys and constraints change. After the new table structure is established, the data is automatically retrieved from the old table, and the same fields are mapped and migrated. In most business scenarios, the database version upgrade only involves the addition or subtraction of fields and the modification of primary key constraints, so the solution to be implemented below is to do an implementation from the most basic and commonly used business scenarios. As for more complex scenarios, they can be expanded on this basis to meet their expectations.

Type selection and stereotyping

After searching on the Internet, there is no simple and complete solution for database upgrade and data migration, and some ideas have been found.

1. Erase old data and rebuild tables

Pros: simplicity

Cons: data loss

two。 Modify the table structure on the basis of the existing table

Advantages: ability to retain data

Disadvantages: the rules are cumbersome, to establish a database field configuration file, then read the configuration file, execute SQL to modify table structure, constraints and primary keys, etc., it becomes cumbersome and troublesome to upgrade across multiple versions of the database

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 the data table.

Advantages: it can retain data, support table structure modification, constraint and primary key change, and is relatively simple to implement.

Disadvantages: there are many steps to implement.

Taken together, the third method is a more reliable one.

Main steps

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

Get the old tables in the database

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 and new tables and compare the fields of the tables 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 crux of the problem is the SQL statement of the corresponding step. The main SQL statements used below are analyzed below:

Get the old tables in the database

SELECT * from sqlite_master WHERE type='table'

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

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 the dataset (0.03 seconds)

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

-- modify the t _ message table to the tasking message _ bak table ALTER TABLE t_message RENAME TO t_message_bak

Get table field information

-- get the field information of the tweemessagebak table PRAGMA table_info ('tweemessageroombak')

The table field information obtained is as follows, as shown below: | cid | name | type | notnull | dflt_value | Competition | for these database fields, we only need to use name, that is, the field name.

Sqlite > PRAGMA table_info ('tasking messageroombak') +-+ | cid | name | type | notnull | dflt_value | Competition | +- -+ | 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 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 fields messageID, messageType, messageJsonContent, retriveTimeString, postTimeString and readState in the tweak message _ Bak table to the t_message table.

Code implementation

Next comes the implementation step of the code.

/ / 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) / / 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 the table name, add the suffix "_ bak", and treat 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 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] / / traversing the old table and the new table, comparing the fields NSDictionary* migrationInfos = [self generateMigrationInfosWithOldTables:existsTables newTables:newAddedTables] of the table to be migrated; / / data migration processing [migrationInfos enumerateKeysAndObjectsUsingBlock: ^ (NSString* newTableName, NSArray* publicColumns, BOOL * _ Nonnull stop) {NSMutableString* colunmsString = [NSMutableString new]; for (int I = 0; I 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='table'"; 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='table' AND name NOT LIKE'% _ bak'"; FMResultSet * rs = [db executeQuery:sql]; while ([rs next]) {NSString* tablename = [rs stringForColumn:@ "name"]; [newAddedTables addObject:tablename];}}]; return newAddedTables } these are all the contents of the article "sample Analysis of IOS Database upgrade data Migration". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.