preface
First of all, in the last article from 0 to get a face to OC database (2), explained how to save or update a model to the database, how to query the data in the database. Secondly, the contents of this article are as follows:
- Database update and data migration.
- Delete the data
Usage scenario: With the iteration of the project, the contents of the database will be more and more. What if one day, the data fields of the database will increase or decrease? For example, in the first version, we saved the student’s name, student number, age and grade. In version 10, we had to save one more item, the student’s height, and even the student’s weight, gender, and so on. How to do? Delete the previous database table, create a new database table, and then insert data again? If I input 10,000 students’ data, it will be a lot of work to start again, and the previous data will also be lost. So! We must implement database updates and data migration. To increase the field to increase, to decrease on the decrease, update it. The scene of deleting data is not to mention, there is a student transfer, we have to remove his data ~
Function implementation
Database update and data migration
When the user insertOrUpdate the model, if the new member variable or delete the member variable in the model, then we will fail to save the data, because the saved model fields do not correspond to the database table structure fields. At this point, we need to update the data. To implement the database update, we have to first a trace of our thinking:
First, determine whether to update -- obtain the SQL statement when the table corresponding to the database is created, get all the fields from it, get the ARRAY A -- obtain all the member variables in the model, get the array B -- compare the array AB, if it is equal, do not need to update the table, if it is not equal, update the table. And migrate the data and then perform the migration data step -- create a new temporary table based on the fields of the Model. create tableif not exists cwstu_tmp(stuNum integer, name text, age integer, address text, primary key(stuNum)); Insert into cwSTU_tmp (stuNum) select stuNum from CWStu; Update the old table to the new table by primary key. --update cwstu_tmpset name = (select name from cwstu where cwstu_tmp.stuNum = cwstu.stuNum);
-- update cwstu_tmp set age = (select age from cwstu wherecwstu_tmp.stuNum = cwstu.stuNum); Drop table drop tableifexists cwstu; Alter table cwSTU_tmp rename to cwSTu;Copy the codeIf all of the above statements are successfully executed, the data migration is complete. If half of the execution fails, then the database may have a temporary table for no reason, and some half-completed data. Obviously we need to avoid this problem, so we use database transactions
A brief introduction to database transactions:
So there are three common methods that we use: BEGIN TRANSACTION COMMIT TRANSACTION ROLLBACK TRANSACTION and then there are four basic properties ACID which WE won’t go into.
How to use transactions:
Before starting the execution of SQL statements, we start the transaction and execute the SQL statements one by one. If a SQL statement fails to execute, we roll back. When the rollback is performed, the previous operations will be cancelled and the database will return to the stage of starting the transaction.
How does a database perform data rollback? Sqlitie database rollback is implemented by rolling back the log. All transaction changes are recorded in the rollback log, and then the corresponding row in the database is written. When the rollback is performed, the state before the rollback is based on the rollback log. SVN and Git have a log every time you commit. If you want to revert to a version, you can simply revert to the corresponding log. It is also important to note that the transaction must be the same database and the same database operation handle.
The theory is finished, now we begin to code, with code – real ideas above
First get all the fields of the database table and encapsulate a method in CWSqliteTableTool
// Select * from table; Return + (NSArray *)allTableColumnNames:(NSString *)tableName uid:(NSString *)uid {NSString *queryCreateSqlStr = [NSString stringWithFormat:@"select sql from sqlite_master where type = 'table' and name = '%@'",tableName];
NSArray *dictArr = [CWDatabase querySql:queryCreateSqlStr uid:uid];
NSMutableDictionary *dict = dictArr.firstObject;
// NSLog(@"-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- % @",dict);
NSString *createSql = dict[@"sql"];
if (createSql.length == 0) {
return nil;
}
// sql = "CREATE TABLE Student(age integer,stuId integer,score real,height integer,name text, primary key(stuId))";
createSql = [createSql stringByReplacingOccurrencesOfString:@"\" " withString:@""];
createSql = [createSql stringByReplacingOccurrencesOfString:@"\n" withString:@""];
createSql = [createSql stringByReplacingOccurrencesOfString:@"\t" withString:@""];
NSString *nameTypeStr = [createSql componentsSeparatedByString:@"("] [1]; NSArray *nameTypeArray = [nameTypeStr componentsSeparatedByString:@","];
NSMutableArray *names = [NSMutableArray array];
for (NSString *nameType inNameTypeArray) {// Remove the primary keyif ([nameType containsString:@"primary"]) {
continue; } / / compression off the inside of the string @ "" only at the ends of the compression nsstrings * nameType2 = [nameType stringByTrimmingCharactersInSet: [NSCharacterSet characterSetWithCharactersInString:@""]];
// age integer
NSString *name = [nameType2 componentsSeparatedByString:@""].firstObject;
[names addObject:name];
}
[names sortUsingComparator:^NSComparisonResult(NSString *obj1, NSString *obj2) {
return [obj1 compare:obj2];
}];
return names;
}
Copy the codeThen get all the member variables in the model, in CWModelTool
+ (NSArray *)allIvarNames:(Class)cls { NSDictionary *dict = [self classIvarNameAndTypeDic:cls]; NSArray *names = dict.allKeys; / / sort names = [names sortedArrayUsingComparator: ^ NSComparisonResult (id _Nonnull obj1, id _Nonnull obj2) {return [obj1 compare:obj2];
}];
return names;
}
Copy the codeCompare two arrays to be equal; if they are equal, they do not need to be updated; otherwise, the database table is updated
+ (BOOL)isTableNeedUpdate:(Class) CLS uid:(NSString *)uid targetId:(NSString *)targetId {NSArray *modelNames = [CWModelTool allIvarNames:cls]; NSString *tableName = [CWModelTool tableName:cls targetId:targetId]; NSArray *tableNames = [self allTableColumnNames:tableName uid:uid];return! [modelNames isEqualToArray:tableNames]; }Copy the codeIn CWDatabase, we need to implement a method that uses transaction control to execute multiple SQL statements at once:
#pragma mark - transaction
+ (void)beginTransaction:(NSString *)uid {
[self execSQL:@"BEGIN TRANSACTION" uid:uid];
}
+ (void)commitTransaction:(NSString *)uid {
[self execSQL:@"COMMIT TRANSACTION" uid:uid];
}
+ (void)rollBackTransaction:(NSString *)uid {
[self execSQL:@"ROLLBACK TRANSACTION"uid:uid]; } // Execute multiple SQL statements + (BOOL)execSqls:(NSArray <NSString *>*) Sqls uid:(NSString *)uid {// Transaction control all statements must return success to be executed successfully [self beginTransaction:uid];for (NSString *sql in sqls) {
BOOL result = [self execSQL:sql uid:uid];
if (result == NO) {
[self rollBackTransaction:uid];
return NO;
}
}
[self commitTransaction:uid];
return YES;
}
Copy the codeFinish the above steps, next we will mainly complete the data migration of multiple SQL statement splicing, and then execute.
#pragma mark - Update database table structure, field rename, data migration+ (BOOL)updateTable:(Class) CLS uid:(NSString *)uid targetId:(NSString *)targetId{// 1. CWModelTool tmpTableName: CLS targetId:targetId] NSString *tmpTableName = [CWModelTool tmpTableName: CLS targetId:targetId]; NSString *tableName = [CWModelTool tableName:cls targetId:targetId];if(! [cls respondsToSelector:@selector(primaryKey)]) { NSLog(@"If you want to manipulate the model, you have to implement + (NSString *)primaryKey; This method tells me the primary key information.");
returnNO; } // Save all the SQL statements that need to be executedexecSqls = [NSMutableArray array]; NSString *primaryKey = [cls primaryKey]; Obtain all fields in a model and the type NSString *createTableSql = [NSString stringWithFormat:@"create table if not exists %@(%@, primary key(%@))",tmpTableName,[CWModelTool sqlColumnNamesAndTypesStr:cls],primaryKey];
[execSqls addObject:createTableSql]; //--insert into cwSTU_tmp (stuNum) select stuNum from CWStu; NSString *inserPrimaryKeyData = [NSString stringWithFormat:@"insert into %@(%@) select %@ from %@",tmpTableName,primaryKey,primaryKey,tableName];
[execSqls addObject:inserPrimaryKeyData]; / / 3. According to the primary key, all the data inserted into fear of the new table NSArray * oldNames = [CWSqliteTableTool allTableColumnNames: tableName uid: uid]; NSArray *newNames = [CWModelTool allIvarNames:cls]; NSDictionary *newNameToOldNameDic = @{};if ([cls respondsToSelector:@selector(newNameToOldNameDic)]) {
newNameToOldNameDic = [cls newNameToOldNameDic];
}
for (NSString *columnName innewNames) { NSString *oldName = columnName; // Find the old field name of the mappingif([newNameToOldNameDic[columnName] length] ! = 0) {if([oldNames containsObject:newNameToOldNameDic[columnName]]) { oldName = newNameToOldNameDic[columnName]; }} // If the old table contains new column names, it should be updated from the old table to the temporary tableif((! [oldNames containsObject:columnName] && [columnName isEqualToString:oldName]) ) {continue;
}
// --update cwstu_tmp set name = (select name from cwstu wherecwstu_tmp.stuNum = cwstu.stuNum); NSString *updateSql = [NSString stringWithFormat:@"update %@ set %@ = (select %@ from %@ where %@.%@ = %@.%@)",tmpTableName,columnName,oldName,tableName,tmpTableName,primaryKey,tableName,primaryKey];
[execSqls addObject:updateSql]; } // delete the old table NSString *deleteOldTable = [NSString stringWithFormat:@"drop table if exists %@",tableName];
[execSqls addObject:deleteOldTable]; NSString *renameTableName = [NSString stringWithFormat:@"alter table %@ rename to %@",tmpTableName,tableName];
[execSqls addObject:renameTableName];
BOOL result = [CWDatabase execSqls:execSqls uid:uid];
[CWDatabase closeDB];
return result;
}
Copy the codeTest code is not posted, the final test is no problem, of course we don’t have to complete part of this work, in order to more convenient to use our framework, we must put this method into the insert or update data the method, that is, when the user to save a data, we first give him determine whether need to update the database table structure, if need be, We move the database silently for him, and then insert or update data. Just like every successful man behind there is a silently paid woman, we will give the user to be the woman ~ 😁 we in the previous package insertOrUpdateModel: method to add a piece of code
#pragma mark Inserts or updates data+ (BOOL)insertOrUpdateModel:(id)model uid:(NSString *)uid targetId:(NSString *)targetId {Class CLS = [model class]; NSString *tableName = [CWModelTool tableName:cls targetId:targetId]; // Check whether the corresponding table exists in the database. If no, create a tableif(! [CWSqliteTableTool isTableExists:tableName uid:uid]) { [self createSQLTable:cls uid:uid targetId:targetId]; }else{// If the table exists, it checks whether the table needs to be updatedif ([CWSqliteTableTool isTableNeedUpdate:cls uid:uid targetId:targetId] ) {
BOOL result = [self updateTable:cls uid:uid targetId:targetId];
if(! result) { NSLog(@"Failed to update database table structure! Failed to insert or update data!");
returnNO; }}} // Here is the previous logic...... }Copy the codeTo delete data
After r we implemented the complex process, data deletion was a piece of cake. No more BB, directly on the code
+ (BOOL)deleteModel (id)model uid (NSString *)uid targetId (NSString *)targetId {Class CLS = [model class]; NSString *tableName = [CWModelTool tableName:cls targetId:targetId];if(! [cls respondsToSelector:@selector(primaryKey)]) { NSLog(@"If you want to manipulate the model, you have to implement + (NSString *)primaryKey; This method tells me the primary key information.");
return NO;
}
NSString *primaryKey = [cls primaryKey];
id primaryValue = [model valueForKeyPath:primaryKey];
NSString *deleteSql = [NSString stringWithFormat:@"delete from %@ where %@ = '%@'",tableName,primaryKey,primaryValue]; BOOL result = [CWDatabaseexecSQL:deleteSql uid:uid]; // Close the database [CWDatabase closeDB];return result;
}
Copy the codeDelete from %@ where %@ = ‘%@’. Add and, or. This is the same idea. Just a little more hard work
4. The end of this passage
Here, we merge database update and data migration operations into the insert method, becoming the woman behind the user’s silent contribution, and then data deletion, which is easy for us now, is also implemented. The next article, we want to achieve complex data types and object storage, such as NSArray NSDictionary, NSObject, CGRect, UIImage, etc… And arrays of nested models, nested dictionaries, and so on… Then in the final article we will deal with multi-threaded security.
The code of github address this time has a tag of 1.2.0. You can find the corresponding tag under Release and download it
Finally, if you feel useful, I hope you can like this article and give Github a star to encourage you. Thank you.
PS: Because I also encapsulate and write articles at the same time. Efficiency may be relatively low, there will be problems, welcome everyone to throw the issue to me, have a better idea, welcome everyone to leave a message!
Finally, I will provide you with the addresses of the last two articles.
Create a database for OC from 0 (1)
Create a database for OC from 0 (2)
And a zero-coupling imitation QQ sideslip framework: one line of code integrated with ultra-low coupling sideslip function
La la la la la. Life is more than… Continuous promotion 😁