object-c使用sqlite3升级数据库处理

#define YstenFriendDBManager_ValueOrEmpty(value) ((value)?(value):@"")

    // 设置版本号和表中个数  在需要的方法中调用
    [self versionControlWithNewDBVersion:DBVersion];
    
    
    // 设置数据库版本号和当前表中有多少字段
    - (void)setDBVersion:(NSString*)DBVersion andDBCount:(NSString *)DBCount{
         [[NSUserDefaults standardUserDefaults] setObject:DBVersion forKey:DB_Version_Key];
         [[NSUserDefaults standardUserDefaults] setObject:DBCount forKey:DB_Count_Key];
         [[NSUserDefaults standardUserDefaults] synchronize];
     }
    
    - (NSString*)DBVersion {
         return [[NSUserDefaults standardUserDefaults] objectForKey:DB_Version_Key];
     }
    - (NSString *)DBCount {
        return [[NSUserDefaults standardUserDefaults] objectForKey:DB_Count_Key];
    }
    // 数据库版本控制主要方法
    - (void)versionControlWithNewDBVersion:(NSString*)newDBVersion {
         // 获取旧版本号
         NSString * version_old = YstenFriendDBManager_ValueOrEmpty([self DBVersion]);
        // 获取新版本号
          NSString * version_new = [NSString stringWithFormat:@"%@", newDBVersion];
         NSLog(@"dbVersionControl before: %@ after: %@",version_old,version_new);
         NSMutableDictionary *migrationInfos = [NSMutableDictionary dictionary];
         // 数据库版本升级
          if (version_old != nil && ![version_new isEqualToString:version_old] ) {
              // 修改现在的表名添加后缀设置为旧表
             [self sqliteChangeTableName_back];
             // 取出旧表中所有的字段
            NSArray *oldTableColumns = [self sqliteOldTableColumnsWithTableName];
             // 创建新表
             [self initNewTables];
             // 取出新表中的所有字段
             NSArray *newTableColumns = [self sqliteNewTableColumnsWithTableName];
                // 旧表和新表对比
                NSArray* publicColumns = [self publicColumnsWithOldTableColumns:oldTableColumns newTableColumns:newTableColumns];
             // 遍历相同字段存字典
            for (NSString* newTableName in newTableColumns) {
             for (NSString* oldTableName in oldTableColumns) {
                if ([newTableName containsString:oldTableName]) {
                    if (publicColumns.count > 0) {
                        [migrationInfos setObject:publicColumns forKey:newTableName];
                    }
                }
            }
            // 数据迁移处理
            [self sqilte3MigrationToNewTables:migrationInfos];
            // 保存新版本数据库版本号 ps andDBCount废弃
            [self setDBVersion:newDBVersion andDBCount:@"10"];
        }
     }
    }
    // 获取数据库中旧的表
    - (NSArray*)sqliteExistsTables {
         _database= [self openDB];
        __block NSMutableArray<NSString*>* existsTables = [NSMutableArray array];
         NSString* sql = [NSString stringWithFormat:@"SELECT * from sqlite_master WHERE type='table' AND name  LIKE '%@'",'表名'];
        char *err;
        sqlite3_stmt *pStmt = 0x00;
         int result=  sqlite3_exec(_database, [sql UTF8String], NULL, NULL, &err);
        if (result==SQLITE_OK) {
             char *nameData = (char *)sqlite3_column_text(pStmt, 1);
             NSString *columnName = [[NSString alloc] initWithUTF8String:nameData];
            [existsTables addObject:columnName];
        }
        [self closeDB];
        return existsTables;
    }
    
    // 获取jiu表中所有字段名称
    -(NSArray*) sqliteOldTableColumnsWithTableName{
         _database = [self openDB];
        NSMutableArray* tableColumes = [[NSMutableArray alloc]init];
        sqlite3_stmt *pStmt = 0x00;
        NSString* tableName = [NSString stringWithFormat:@"%@_bak",'表名'];
        NSString* sql = [NSString stringWithFormat:@"PRAGMA table_info('%@')", tableName];
        sqlite3_prepare_v2(_database, sql.UTF8String, -1, &pStmt, nil);
        while (sqlite3_step(pStmt) == SQLITE_ROW) {
            char *nameData = (char *)sqlite3_column_text(pStmt, 1);
            NSString *columnName = [[NSString alloc] initWithUTF8String:nameData];
            [tableColumes addObject:columnName];
        }
        [self closeDB];
        return tableColumes;
    }
    
    // 修改表名后缀加old
    -(void) sqliteChangeTableName_back{
        _database = [self openDB];
        char *err;
        sqlite3_stmt *pStmt = 0x00;
        NSString* sql = [NSString stringWithFormat:@"ALTER TABLE '%@' RENAME TO '%@_bak'", '表名','表名'];
        int result = sqlite3_exec(_database, [sql UTF8String], NULL, pStmt, &err);
        if (result == SQLITE_OK) {
            NSLog(@"修改表名成功(%@)",'表名');
        }
        [self closeDB];
    }
    
    // 需要重写该方法
    -(void) initNewTables {
       // 重新定义自己需要的表
    }
    
    
    // 获取新表中所有字段名称
    -(NSArray*) sqliteNewTableColumnsWithTableName{
        _database = [self openDB];
        NSMutableArray* tableColumes = [[NSMutableArray alloc]init];
        sqlite3_stmt *pStmt = 0x00;
        NSString* sqlstring = [NSString stringWithFormat:@"PRAGMA table_info('%@')",'表名'];
        sqlite3_prepare_v2(_database, sqlstring.UTF8String, -1, &pStmt, nil);
        while (sqlite3_step(pStmt) == SQLITE_ROW) {
            char *nameData = (char *)sqlite3_column_text(pStmt, 1);
            NSString *columnName = [[NSString alloc] initWithUTF8String:nameData];
                [tableColumes addObject:columnName];
        }
        [self closeDB];
        return tableColumes;
    }
    
   // 提取新表和旧表的共同表字段，表字段相同列的才需要进行数据迁移处理
    - (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;
    } 
    
    // 删除旧表
    -(void)sqlite3DropOldTables:(NSString *)oldTableName {
        _database = [self openDB];
        NSString* sql = [NSString stringWithFormat:@"DROP TABLE IF EXISTS %@", oldTableName];
        char *err;
        int result=  sqlite3_exec(_database, [sql UTF8String], NULL, NULL, &err);
        if (result==SQLITE_OK) {
            NSLog(@"删除旧表成功");
        }else {
            NSLog(@"删除旧表失败");
        }
        [self closeDB];
    }
    
    - (NSString*)ystStringForColumn:(NSString*)columnName {
      return [self ystStringForColumnIndex:[self ystColumnIndexForName:columnName]];
    }
    
    - (NSString *)ystStringForColumnIndex:(int)columnIdx {
        sqlite3_stmt *pStmt = 0x00;
        if (sqlite3_column_type(pStmt, columnIdx) == SQLITE_NULL || (columnIdx < 0) || columnIdx >= sqlite3_column_count(pStmt)) {
            return nil;
        }
        const char *c = (const char *)sqlite3_column_text(pStmt, columnIdx);
        
        if (!c) {
            // null row.
            return nil;
        }
        return [NSString stringWithUTF8String:c];
    }
    
    - (int)ystColumnIndexForName:(NSString*)columnName {
        columnName = [columnName lowercaseString];
        NSNumber *n = [[self columnNameToIndexMap] objectForKey:columnName];
        if (n != nil) {
            return [n intValue];
        }
        NSLog(@"Warning: I could not find the column named '%@'.", columnName);
        
        return -1;
    }
    
    -(NSMutableDictionary *)columnNameToIndexMap{
        if (_columnNameToIndexMap) {
            sqlite3_stmt *pStmt = 0x00;
            int columnCount = sqlite3_column_count(pStmt);
            _columnNameToIndexMap = [[NSMutableDictionary alloc] initWithCapacity:(NSUInteger)columnCount];
            int columnIdx = 0;
            for (columnIdx = 0; columnIdx < columnCount; columnIdx++) {
                [_columnNameToIndexMap setObject:[NSNumber numberWithInt:columnIdx]
                                         forKey:[[NSString stringWithUTF8String:sqlite3_column_name(pStmt, columnIdx)] lowercaseString]];
            }
        }
        return _columnNameToIndexMap;
    }
    
    // 数据迁移处理
    -(void)sqilte3MigrationToNewTables:(NSMutableDictionary *)migrationInfos{
        _database = [self openDB];
        [migrationInfos enumerateKeysAndObjectsUsingBlock:^(NSString* newTableName, NSArray* publicColumns, BOOL * _Nonnull stop) {
            NSMutableString* colunmsString = [NSMutableString new];
            for (int i = 0; i<publicColumns.count; i++) {
                [colunmsString appendString:publicColumns[i]];
                if (i != publicColumns.count-1) {
                    [colunmsString appendString:@", "];
                }
            }
        
            NSString *sqlString = [NSString stringWithFormat:@"INSERT INTO '%@' ('%@') SELECT ('%@') FROM '%@_bak';",newTableName,colunmsString,colunmsString,newTableName];
            char *err;
            int result=  sqlite3_exec(self->_database, [sqlString UTF8String], NULL, NULL, &err);
            if (result==SQLITE_OK) {
                NSLog(@"数据迁移处理成功");
            }else{
                NSLog(@"数据迁移处理失败");
            }
        }];
        [self closeDB];
    }
复制代码

PS:本文不纯是本人思路，有bd来的，有fmdb封装的，集合了小弟的思想，有那些low或者不足请不吝赐教，如说你骂我，我就打死你。

转载于:https://juejin.im/post/5c86201f6fb9a049a62d6941