开源一个轻量级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数据自动升级的管理类