开源一个轻量级iOS数据库自动升级的管理类
SpeSqliteManager4IOS
一个轻量级管理iOS数据自动升级的管理类，无需第三方组件支持。
设计亮点
1.以静制动:新增数据库表、字段、删除表都是修改plist即可，保持代码稳定性。
2.方便可控:提供加密/非加密两种数据库操作方式，根据需求灵活配置一个宏即可。
3.数据迁移:由非加密升级为加密方式，手动配置下加密版本号，即可自动提供数据迁移。
使用说明
1.目前写的逻辑只是用来只创建了一个db文件。
2.关于plist:
1.dbName:是保存到沙盒的数据库文件的名称。
2.dbVersion:数据库版本号,判断本地数据库文件是否升级就通过此key。
3.dbTables:想要创建的表名,每个表名下是具体的字段。
4.上面3个字段名最好不要改，如果修改了话，连同程序里的宏也请同时修改下。
3.对于已经已使用plist的应用，注意plist中的值不要乱改动。
4.修改表时:1.不用的表和字段作为冗余表和字段,不删。
关键代码 1.plist <?xml version=＂1.0＂ encoding=＂UTF-8＂?> dbName local.db dbVersion 1 app chat id INTEGER PRIMARY KEY AUTOINCREMENT uid TEXT 2.SpeIOSSqliteUpdateManager(核心管理类) #import ＂SpeEncryptDefine.h＂ #if ENCRPTYED #import #else #import #endif @interface SpeIOSSqliteUpdateManager : NSObject { sqlite3 *m_db; NSDictionary *m_sqlSettingDic;//在bundle中的数据库plist，即新plist NSDictionary *m_localSettingDic;//本地的数据库plist BOOL m_appUpdate;//冷启动是否是覆盖安装，如果是需要清除本地总的资源版本号 } SINGLETON_FOR_HEADER(SpeIOSSqliteUpdateManager) //创建或升级本地数据库 +(void)createOrUpdateDB; //db名 + (NSString *)dbName; + (sqlite3 *)db; - (NSString *)pathLocalDB; - (NSArray *)arrTables; -(BOOL)execSql:(NSString *)sql; - (BOOL)isAppUpdate; @end // // Created by Points on 15-04-03. // Copyright (c) 2015年 Points. All rights reserved. // #define KEY_DB_VERSION @＂dbVersion＂ #define KEY_DB_NAME @＂dbName＂ #define KEY_TABLES @＂dbTables＂ #define KEY_LOCAL_NAME @＂SpeSqlSetting.plist＂ #define KEY_SQL_SETTING_PATH [[NSBundle bundleForClass:self.class] pathForResource:@＂SpeSqlSetting＂ ofType:@＂plist＂] #import ＂SpeIOSSqliteUpdateManager.h＂ #import ＂SpeIOSSqliteUpdateManager+Backup.h＂ #import ＂SpeDesEncrypt.h＂ @implementation SpeIOSSqliteUpdateManager SINGLETON_FOR_CLASS(SpeIOSSqliteUpdateManager) #if ENCRPTYED SQLITE_API int sqlite3_key(sqlite3 *db, const void *pKey, int nKey); SQLITE_API int sqlite3_rekey(sqlite3 *db, const void *pKey, int nKey); #endif - (void)dealloc { sqlite3_close(m_db); } - (id)init{ if(self = [super init]){ m_appUpdate = NO; m_sqlSettingDic = [NSDictionary dictionaryWithContentsOfFile:KEY_SQL_SETTING_PATH]; NSString *database_path = [self pathLocalDB]; NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); NSString *documents = [paths objectAtIndex:0]; NSString *sqlPath = [documents stringByAppendingFormat:@＂/lz/%@＂,KEY_LOCAL_NAME]; NSString * _document = [documents stringByAppendingFormat:@＂/lz＂]; if(![[NSFileManager defaultManager]fileExistsAtPath: _document]){ [[NSFileManager defaultManager] createDirectoryAtPath:_document withIntermediateDirectories:YES attributes:nil error:nil]; } [self getLocalEncryptedPlist:sqlPath]; NSLog(@＂DB路径=%@＂,database_path); if([[NSFileManager defaultManager]fileExistsAtPath:database_path]){ //已经存在需要判断当前数据库版本是否小于24，如果是就需要走备份数据,再创建新数据库，再插入备份数据流程 [self startUpgrade]; }else{ [self openDB]; } if([self isNeedUpadte]){ m_appUpdate = YES; [self updateLocalDB]; #pragma mark // 两次循环找出被删除的表 [self dropTheTableDeteted]; [self encryptLoclPlist:sqlPath]; } } return self; } #pragma mark drop被删除的表(也就是对比新plist表，在本地沙盒数据库里面不存在的table) - (void)dropTheTableDeteted{ /*新plist里面所有的表名*/ NSMutableArray *m_sqlSettingTables = [NSMutableArray array]; // 获取plist文件对应的所有的key NSArray *m_sqlSettingDic_keys = m_sqlSettingDic.allKeys; // 遍历出 所有的value数的元素 for (NSString *key in m_sqlSettingDic_keys) { id id_value = m_sqlSettingDic[key]; if ([id_value isKindOfClass:[NSArray class]]) { NSArray *value_array = (NSArray *)id_value; // 把数组里面的内容遍历出来 for (int i = 0; i 0){ tableName = [arrKey firstObject]; } if(localSqlDic[tableName] == nil){ searchCount++; } else{ continue; } } //是新表 if(searchCount == arr.count){ [self createTable:tableDic]; } else{ NSArray *newSqlArr = tableDic[tableName]; for(NSDictionary *localSqlSetDic in arrLocal){ if([[localSqlSetDic.allKeys firstObject] isEqualToString:tableName]){ NSArray *localSqlArr = localSqlSetDic[tableName]; //判断是不是完全一致 if([newSqlArr isEqualToArray:localSqlArr]){ break; } else{ NSArray *addColumn = [newSqlArr subarrayWithRange:NSMakeRange(localSqlArr.count, newSqlArr.count-localSqlArr.count)]; //更新表字段 __block NSMutableString *alterSql = nil; for(NSDictionary *addColumnDic in addColumn){ alterSql = [NSMutableString stringWithFormat:@＂alter table %@ add column＂,tableName]; [addColumnDic enumerateKeysAndObjectsUsingBlock:^(id newParaKey, id newParaObj, BOOL * __unused stop) { [alterSql appendFormat:@＂ %@ %@＂,newParaKey,newParaObj]; [self execSql:alterSql]; }]; } break; } } } } } } /** * @brief 创建所有表 * * @return */ -(BOOL)createTable{ NSArray *arr = [self arrTables]; if(arr == nil || arr.count == 0{ return NO; } for(NSDictionary *tableDic in arr){ [self createTable:tableDic]; } return YES; } /** * @brief 创建表的具体逻辑 * * @return */ - (void)createTable:(NSDictionary *)tableDic{ __block NSMutableString *createSql = [NSMutableString stringWithString:@＂CREATE TABLE IF NOT EXISTS＂]; [tableDic enumerateKeysAndObjectsUsingBlock:^(id key, id obj, BOOL * __unused stop) { NSArray *arrColumn = obj; if(arrColumn == nil || arrColumn.count == 0){ return ; } [createSql appendFormat:@＂＂%@＂(＂,key]; for(NSDictionary *columnDic in arrColumn){ [columnDic enumerateKeysAndObjectsUsingBlock:^(id key, id obj, BOOL * __unused stop) { [createSql appendFormat:@＂%@ %@,＂,key,obj]; }]; } createSql = [NSMutableString stringWithString:[createSql substringToIndex:createSql.length-1]]; [createSql appendFormat:@＂)＂]; }]; [self execSql:createSql]; } - (void)dropTable:(NSString *)tableName{ NSString *dropSql = [NSString stringWithFormat:@＂DROP TABLE %@＂,tableName]; BOOL result = [self execSql:dropSql]; if (!result) { NSLog(@＂drop table %@ 失败＂,tableName); } } -(BOOL)execSql:(NSString *)sql{ char *err = NULL; if (sqlite3_exec(m_db, [sql UTF8String], NULL, NULL, &err) != SQLITE_OK){ NSLog(@＂数据库操作:%@失败!====%s＂,sql,err); return NO; } else{ NSLog(@＂操作数据成功==sql:%@＂,sql); } return YES; } - (sqlite3 *)dbHandle{ return m_db; } + (NSDictionary *)dictionaryWithJsonString:(NSString *)jsonString { if (jsonString == nil) { return nil; } NSData *jsonData = [jsonString dataUsingEncoding:NSUTF8StringEncoding]; NSError *err; NSDictionary *dic = [NSJSONSerialization JSONObjectWithData:jsonData options:NSJSONReadingMutableContainers error:&err]; if(err) { NSLog(@＂json解析失败：%@＂,err); return nil; } return dic; } - (NSString *)convert2JSONWithDictionary:(NSDictionary *)dic{ NSError *err; NSData *jsonData = [NSJSONSerialization dataWithJSONObject:dic options:0 error:&err]; NSString *jsonString; if (!jsonData) { NSLog(@＂%@＂,err); }else{ jsonString = [[NSString alloc]initWithData:jsonData encoding:NSUTF8StringEncoding]; } NSLog(@＂%@＂,jsonString); return jsonString; } - (BOOL)isAppUpdate{ return m_appUpdate; } @end 3.SpeIOSSqliteUpdateManager+Backup(数据迁移) // // // Created by Points on 2020/5/22. // SQLCipher无法对老数据加密。方案:备份老数据数据->删除老数据库->新建新数据库->插入备份数据->完成迁移 #import ＂SpeIOSSqliteUpdateManager.h＂ NS_ASSUME_NONNULL_BEGIN @interface SpeIOSSqliteUpdateManager (Backup) /// 获取要备份的数据 - (NSMutableArray *)startShiftSqlite3Data; /// 插入到新数据库 /// @param arrDatas 之前备份的数据 - (void)insertAllWillhiftSqlite3Data:(NSArray *)arrDatas; /// 处理本地数据库与新的plist的字段不统一的bug /** * @pragma 本地plist的数组 */ - (void)handleDBMatchNewPlist:(NSArray *)arrLocal; @end NS_ASSUME_NONNULL_END ``` ``` // // SpeIOSSqliteUpdateManager+Backup.m // // Created by Points on 2020/5/22. // #import ＂SpeIOSSqliteUpdateManager+Backup.h＂ @implementation SpeIOSSqliteUpdateManager (Backup) #pragma mark - 数据备份及重新插入新数据 /// 获取要备份的数据 - (NSMutableArray *)startShiftSqlite3Data{ NSArray *arr = [self arrTables]; NSMutableArray *arrDatas = [self arrWillhiftSqlite3Data:arr]; return arrDatas; } - (NSMutableArray *)arrWillhiftSqlite3Data:(NSArray *)arr{ NSMutableArray *arrTotal = [NSMutableArray array]; for(NSDictionary *tableDic in arr){ NSString *tableName = [tableDic.allKeys firstObject]; BOOL flag = NULL; NSArray *columns = [self filterPrimaryKey:[tableDic.allValues firstObject] hasPrimary:&flag]; NSMutableArray *arr = [NSMutableArray array]; @synchronized (self) { NSString *sql = [NSString stringWithFormat:@＂SELECT * FROM ＂%@＂ ＂,tableName]; sqlite3_stmt * statement; if (sqlite3_prepare_v2(m_db, [sql UTF8String], -1, &statement, nil) == SQLITE_OK){ while (sqlite3_step(statement) == SQLITE_ROW){ [arr addObject:[self row:statement columns:columns hasPrimary:flag]]; } } sqlite3_finalize(statement); } [arrTotal addObject:arr]; } return arrTotal; } - (NSArray *)filterPrimaryKey:(NSArray *)arrColumns hasPrimary:(BOOL *)hasPrimary{ NSMutableArray *arr = [NSMutableArray array]; *hasPrimary = NO; for(NSDictionary *value in arrColumns){ if([[value.allValues firstObject]rangeOfString:@＂PRIMARY＂].length==0 && [[value.allValues firstObject]rangeOfString:@＂primary＂].length==0){ [arr addObject:value]; }else{ *hasPrimary = YES; } } return arr; } - (NSMutableDictionary *)row:(sqlite3_stmt *) statement columns:(NSArray *)columns hasPrimary:(BOOL)hasPrimary { NSMutableDictionary *data = [NSMutableDictionary dictionary]; for(NSDictionary *column in columns){ int index = (int)[columns indexOfObject:column]; if([[column.allKeys firstObject]rangeOfString:@＂PRIMARY＂].length>0||[[column.allValues firstObject]rangeOfString:@＂primary＂].length>0){//先判断是否有主键 }else if([[column.allValues firstObject]isEqualToString:@＂TEXT＂]){//主键数据不要保存 char *v = sqlite3_column_text(statement, hasPrimary?(index+1):index); NSString *item = v== NULL ? @＂＂ :[NSString stringWithCString:v encoding:NSUTF8StringEncoding]; [data setValue:item forKey:[column.allKeys firstObject]]; }else if ([[column.allValues firstObject]isEqualToString:@＂INTEGER＂]){ int v = sqlite3_column_int(statement, hasPrimary?(index+1):index); [data setValue:[NSString stringWithFormat:@＂%d＂,v] forKey:[column.allKeys firstObject]]; }else if ([[column.allValues firstObject]isEqualToString:@＂BLOB＂]){ [data setValue:@＂＂ forKey:[column.allKeys firstObject]]; } } return data; } /// 插入到新数据库 /// @param arrDatas 之前备份的数据 - (void)insertAllWillhiftSqlite3Data:(NSArray *)arrDatas{ NSArray *arr = [self arrTables]; for(NSDictionary *tableDic in arr){ NSInteger index = [arr indexOfObject:tableDic]; NSString *tableName = [tableDic.allKeys firstObject]; BOOL *flag = NULL; NSArray *columns = [self filterPrimaryKey:[tableDic.allValues firstObject] hasPrimary:&flag]; //拼接sql语句 NSMutableString *sql = [NSMutableString stringWithFormat:@＂insert into ＂%@＂＂,tableName]; for (NSInteger i=0; i count) { //本地的数据库有更新 NSArray *addColumn = [array subarrayWithRange:NSMakeRange(count, array.count-count)]; //更新表字段 __block NSMutableString *alterSql = nil; for(NSDictionary *addColumnDic in addColumn) { alterSql = [NSMutableString stringWithFormat:@＂alter table %@ add column＂,tableName]; [addColumnDic enumerateKeysAndObjectsUsingBlock:^(id newParaKey, id newParaObj, BOOL * __unused stop) { [alterSql appendFormat:@＂ %@ %@＂,newParaKey,newParaObj]; [self execSql:alterSql]; }]; } } } } - (int)getColumnCount:(NSString *)tableName { @synchronized (self) { NSString *sqlQuery = [NSString stringWithFormat:@＂select * from %@＂,tableName]; sqlite3_stmt * statement; if (sqlite3_prepare_v2(m_db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK) { sqlite3_finalize(statement); return sqlite3_column_count(statement); } return 0; } } @end 4.集成方法: 1.导入如下几个文件，在plist根据自己的业务配置数据库名、版本号、表、表字段。 2.在主application中直接调用[SqliteDataManager sharedInstance]即可,自动创建和升级数据库。
使用效果
1.已在生产环境使用多年，效果良好。
喜欢的朋友可以移步至gitee查看源码：
SpeSqliteManager4IOS: 一个用来管理iOS数据自动升级的管理类
