Think of to write this reason is I and a friend of mine in writing an open source project, I am responsible for persistent storage part of the database, the result after he saw my database Data storage structure was dumbfounded directly, because people are more simple and crude, directly to a Data Model file be lost after binary Data to the database. Then take out also can steal lazy, solution file is a Model Model, but my little partner is not willing to, I criticized, said I database “retrieval” value to write, well, I admit that I heard a face meng force, but seems to be quite reasonable 🤔, but according to the retrieval value he said, I think the words should be the data store more clearly, that will be divided into more fields [long SQL statements let me shiver in the corner 🙃], alas, the individual is really very lazy, think of these headaches, so we can not use a class according to the Model attribute generated SQL statement? The answer is yes, keep reading!
SQLite background:Structured Query Language (SQL) Structured LanguageSQLite is the mainstream embedded relational database at present. Its main characteristics are lightweight and cross-platform. Many embedded operating systems take it as the first database. It is based on C language development database! So we need to use HI c language syntax for database operations and access!
: * * * * the SQL statement is first built table, table contains a field name, table name, field corresponding storage type, then we have to to get familiar with our first of the seven basic data types and what are those constants types in the database type, such ability and each field, both to achieve storage, can save a space again
Storage types in SQL databasesMore, but please be patient, it’s not complicated】SQlite Database storage type
Evolution of ideas: If we want to use a class to generate SQL from Model properties, it must be universal, otherwise it doesn’t make a lot of sense, so we must get all the properties of the Model and the types of those properties, and then generate the corresponding fields in the database from those property names. The type of the property is processed to become the storage type in the database, so obviously we’re going to use the runtime again!
Now that we know about storage types, we’re going to get real. First, we’re going to use the runtime to parse how the data types we normally use are represented at runtime. I don’t need to go into details here, but I’ll just post the method how to get the corresponding property and property type of a class based on runtime:
/** Based on the class to be stored in the database, After the access to the attribute of the class and the class is stored into the database properties corresponding database storage type @ param class to store class * / - (void) disposePropertyWithClass class (class) {if(class && class ! = [NSObject class] && class ! = [MySqlStatementManager class]) { unsigned int outCount = 0; Ivar *ivars = class_copyIvarList(class, &outCount);for(int i = 0 ; i < outCount; i ++ ) { Ivar ivar = ivars[i]; / / run-time access to the property name nsstrings * key = [nsstrings stringWithUTF8String: ivar_getName (ivar)]; // Remove the underscore at the beginning of the attribute nameif ([key rangeOfString:@"_"].location == 0) { key = [key substringFromIndex:1]; } // The runtime gets the type NSString *type = [NSString stringWithUTF8String:ivar_getTypeEncoding(ivar)];
NSCharacterSet *set = [NSCharacterSet characterSetWithCharactersInString:@"@ / :; Selections () ", "", [] {} - * # % + = _ \ \ | ~ < > $euro ^ • '@ # $% ^ & * () _ +" \ ""];
type = [type stringByTrimmingCharactersInSet:set];
if(! self.propertyDic[type] {// If the attribute type is not one of the seven basic data types or a constant type such as int short, then the following LOG message is displayed: NSLog(@"The database does not support the storage of the %@ attribute of type %@ in the %@ class. The attribute data will be lost. Please re-create the attribute type of the class.",NSStringFromClass(class),key,type);
}else{// Pair the type in the property with some type in the database NSString *sqlSaveType = self.propertydic [type];
[self.propertyListDic setValue:sqlSaveType forKey:key]; } } free(ivars); }}Copy the codeSelf. PropertyDic is a dictionary used to classify all the types we commonly use as: The type of each attribute in our Model [KEY] —- The database storage type that each attribute should correspond to [Value] a fixed dictionary initialized previously, Self.propertylistdic = self.propertylistdic = self.propertylistdic = self.propertylistdic = self.propertyListdic = self.propertyListdic = self.propertyListdic = self.propertyListdic = self.propertyListdic = self.propertyListdic = self.propertyListdic = self.propertyListdic = self.propertyListdic = self.propertyListdic = self.propertyListdic = self.propertyListdic The name of each attribute in our Model [KEY] —- The storage type of each attribute in the database [Value]
Then AFTER I analyze our self. PropertyDic should look like this:
// Get the property type (KEY) corresponding to the model in combination with the runtime, then these types are stored in the database as the following (Value) type - (NSDictionary *)propertyDic {if (_propertyDic == nil) {
_propertyDic = @{@"NSString" : @"varchar(1024)"The @"NSMutableString" : @"varchar(1024)"The @"NSArray" : @"blob"The @"NSMutableArray" : @"blob"The @"NSDictionary" : @"blob"The @"NSMutableDictionary" : @"blob"The @"NSData" : @"blob"The @"NSMutableData" : @"blob"The @"NSDate" : @"datetime"The @"NSNumber" : @"varchar(1024)"The @"c" : @"varchar(128)"// May be BOOL or char @"i" : @"int"// Int @"s" : @"int"// Short type @"f" : @"float", // Float @"d" : @"numeric"// Double @"q" : @"int"// Long @"B" : @"bit"// On iPhone, the runtime BOOL retrieves type B. On MAC, the runtime BOOL retrieves type C"" : @""}; }return _propertyDic;
}
Copy the code** Some of you may have noticed that I set the array storage type to BLOB [binary big data] in _propertyDic. I know this is not appropriate, but SQLite does not support array storage, and I think the best way to store arrays is to create another table. This array is stored in this table, which will involve the knowledge of the PRIMARY KEY of the database. Only when the data is retrieved can we get a data model we need through the joint table query. If there is only one array in the model, if the array is stored in the array, it will be even more embarrassing. It may be that a model has to correspond to N tables to retrieve the complete data. Of course, the database does not support OC dictionary storage, so the rest of the scene you can imagine
** Conclusion about storage type :** In order to be lazy (forgive me for being so straightforward), I chose to archive the array directly into the database 🙃, there is a better way to welcome the comments section exchange
So now that we have the _propertyDic and _propertyListDic analysis of all the property names in the Model and what type the property should be stored in the database, it is time to generate the SQL string
Because SQL statement many, here I to build a table SQL as an example, other you can think about how to generate SQL:
#pragma mark - Create table
- (NSString *)greatTableWithTableName:(NSString *)tableName andClass:(Class)modelClass
{
[self disposePropertyWithClass:modelClass];
__block NSString *greatSql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@(",tableName];
[self.propertyListDic enumerateKeysAndObjectsUsingBlock:^(id _Nonnull key, id _Nonnull obj, BOOL * _Nonnull stop) {
greatSql = [NSString stringWithFormat:@"% @ % @ % @.",greatSql,key, obj]; }]; greatSql = [greatSql substringToIndex:greatSql.length - 1]; // Add closing parentheses greatSql = [NSString stringWithFormat:@"% @)",greatSql];
return greatSql;
}
Copy the codeCall as follows:
MySqlStatementManager *manger = [[MySqlStatementManager alloc] init];
NSString *sql = [manger greatTableWithTableName:@"Test" andClass:[TestModel class]];
NSLog(@"TestModel corresponds to the following database construction clause %@",sql);
Copy the code