iOS Sqlite3 数据库的升级

在含有数据库的应用中，随着应用版本的迭代更新，以前设计的数据库表很可能不能满足现在的业务需求，所以我们的应用就需要考虑数据库版本更新，以应对当前的业务。在数据库升级的情况中，有需要给老版本的数据库表增加字段或者添加索引的情况，也有增加新表的情况，还有对旧表中的数据进行升级的情况等。下面使用Sqlite3对这些情况给出一种解决方案。

1、整体逻辑概述
1）初始数据库在应用程序中包含一份，然后在程序第一次运行的时候，拷贝到Document一份。
2）对数据库的版本作检查，如果当前数据库的版本没有应用程序的版本大，那么就考虑对数据库做版本升级操作。
3）在数据库升级的时候，让数据库配置表（一个存放数据库表信息的plist文件）和初始化数据库表中的信息做对比，初始化数据库表中没有的字段，在初始化数据库表中新增进去，对于新增的表也一样，需要在初始化数据库表中新建。之后，对数据库中的内容做检查，通过配置文件中的内容和当前版本信息，决定是否对其中的内容做升级和变迁操作。

2、具体实现
1）先看一下一些文件的配置和作用

这里做一些简要说明。
Database.db是一个初始数据库。这里有建立好的数据库文件。在程序第一次运行的时候，会将其从程序中拷贝到沙盒文件中。
tables.plist文件是数据库文件的配置表。具体内容如上图的右半部分。这是一个字典，第一级为数据库的表名，第二级为数据库表中字段。其中字段类型为Number类型，且值为1代表是该表的主键。
DatabaseForUpdate.plist文件是数据升级配置表文件，这个文件中配置了一些信息，该信息用来决定哪个版本一下的应用需要执行该数据库升级的文件，此数据库升级的文件是updateDatabase.sql.
updateDatabase.sql文件是数据库升级的文件。

2）下面给出一些具体的代码

YCHanddleDatabase.m文件

#import "YCHanddleDatabase.h"
#import "YCSystemConfigTool.h"
#import "NSString+Extension.h"
#import "YCSqlite.h"

#define MRDatabaseUpgradeFileName @"DatabaseForUpdate.plist"
#define BelowVersionNeedDatabaseUpgradeKey @"BelowVersionNeedDatabaseUpgrade"
#define DatabaseUpgradeSqlFileNameKey @"DatabaseUpgradeFileNameKey"
#define DatabaseUpgradeSpecificVersionKey @"DatabaseUpgradeSpecificVersion"

@implementation YCHanddleDatabase
- (instancetype)initWithDatabasePath:(NSString *)databasePath {
    if (self = [super init]) {
        NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject];
        NSString *databasePath = [documentPath stringByAppendingPathComponent:@"Database.db"];
        NSFileManager *fileManager = [NSFileManager defaultManager];

        if (![fileManager fileExistsAtPath:databasePath]) {
            NSString *localDatabasePath = [[[NSBundle mainBundle] bundlePath] stringByAppendingPathComponent:@"Database.db"];
           [fileManager copyItemAtPath:localDatabasePath toPath:databasePath error:nil];
            NSURL *fileUrl = [NSURL fileURLWithPath:databasePath];
            [fileUrl setResourceValue:[NSNumber numberWithBool:YES] forKey:NSURLIsExcludedFromBackupKey error:nil];
            [self checkoutDatabaseUpgrade];
            [YCSystemConfigTool setDatabaseConfigVersion:[[[NSBundle mainBundle] infoDictionary] objectForKey:@"CFBundleShortVersionString"]];

        } else {
            if (![[YCSystemConfigTool getDatabaseConfigVersioin] isEqualToString:[[[NSBundle mainBundle] infoDictionary] objectForKey:@"CFBundleShortVersionString"]]) {
                [self checkoutDatabaseUpgrade];
                [YCSystemConfigTool setDatabaseConfigVersion:[[[NSBundle mainBundle] infoDictionary] objectForKey:@"CFBundleShortVersionString"]];
            }
        }

    }
    return self;
}

- (void)checkoutDatabaseUpgrade {
    NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject];
    NSString *databasePath = [documentPath stringByAppendingPathComponent:@"Database.db"];
    _database = [[YCSqlite alloc] initSqliteWithDbFilePath:databasePath];
    [self databaseUpgradeForTables];
    [self databaseUpgradeForMrigation];
}

-  (void)databaseUpgradeForTables {
    NSString *tablesPath = [[[NSBundle mainBundle] bundlePath] stringByAppendingPathComponent:@"tables.plist"];
    NSDictionary *tablesDic = [NSDictionary dictionaryWithContentsOfFile:tablesPath];
    NSArray *tableNames = tablesDic.allKeys;
    for (NSString *tableName in tableNames) {
        BOOL isExists = NO;

        NSString *sqlString = @"SELECT [sql] FROM [sqlite_master] WHERE [type]='table' AND [name]=:name";
        NSDictionary *params = @{@"name" : tableName};
        YCSqliteResultSet *resultSet = [_database qeuryWithSql:sqlString params:params];
        if ([resultSet hasNext]) {
            [self alterTableWithTablesDictionary:tablesDic tableName:tableName resultSet:resultSet];
            isExists = YES;
        }
        [resultSet close];
        if (!isExists) {
            NSMutableString *createTableSqlMutableString = [self createTableSqlWithTablesDictionary:tablesDic tableName:tableName];
            if (![_database executeSql:createTableSqlMutableString params:nil]) {
                NSLog(@"-----Database:%@-----", [_database errmsg]);
            }


        }
    }

}

- (void)alterTableWithTablesDictionary:(NSDictionary *)tablesDic tableName:(NSString *)tableName resultSet:(YCSqliteResultSet *)resultSet {
    NSString *createTableSqlString = [[resultSet stringValueAtIndex:0] lowercaseString];
    NSDictionary *clonumNameDictionary = [tablesDic valueForKey:tableName];
    for (NSString *columnKey in clonumNameDictionary.allKeys) {
        NSString *columnName = [columnKey lowercaseString];
        if ([createTableSqlString rangeOfString:columnName].location == NSNotFound) {
            NSString * columnType = @"VarChar";
            NSObject * columnObj = [clonumNameDictionary valueForKey:columnKey];
            if ([columnObj isKindOfClass:[NSString class]]) {
                columnType = @"text";
            } else if ([columnObj isKindOfClass:[NSNumber class]]) {
                columnType = @"integer";
            }
            BOOL isPrimaryKey = [[clonumNameDictionary valueForKey:columnKey] intValue] == 1;
            [_database executeSql:[NSString stringWithFormat:@"ALTER TABLE [%@] ADD COLUMN %@ %@;",tableName,columnName,columnType] params:nil];
            if (isPrimaryKey) {
                [_database executeSql:[NSString stringWithFormat:@"ALTER TABLE [%@] ADD PRIMARY KEY(%@);",tableName, columnName] params:nil];
            }

        }
    }

}

- (NSMutableString *)createTableSqlWithTablesDictionary:(NSDictionary *)tablesDic tableName:(NSString *)tableName {
    NSMutableString *createTableSqlMutableString = [NSMutableString stringWithCapacity:1024];
    [createTableSqlMutableString appendFormat:@"CREATE TABLE IF NOT EXISTS '%@' ",tableName];
    NSDictionary * columnDic = [tablesDic valueForKey:tableName];
    NSMutableString *primaryKeyString = [NSMutableString string];
    NSInteger i = 0;
    for (NSString *columnName in columnDic.allKeys) {
        NSString * columnType = @"text";
        NSObject * columnObj = [columnDic valueForKey:columnName];
        if ([columnObj isKindOfClass:[NSString class]]) {
            columnType = @"text";
        } else if ([columnObj isKindOfClass: [NSNumber class]]) {
            columnType = @"integer";
        }

        if (i == 0) {
            [createTableSqlMutableString appendFormat:@"('%@' %@",columnName,columnType];
        } else {
            [createTableSqlMutableString appendFormat:@",'%@' %@",columnName,columnType];
        }

        BOOL isPrimaryKey = [[columnDic valueForKey:columnName] intValue] == 1;
        if (isPrimaryKey) {
            if (primaryKeyString.length==0) {
                [primaryKeyString appendFormat:@"'%@'",columnName];
            } else {
                [primaryKeyString appendFormat:@",'%@'",columnName];
            }
        }
        i++;
    }//end for
    if (primaryKeyString.length > 0) {
        [createTableSqlMutableString appendFormat:@",PRIMARY KEY(%@)", primaryKeyString];
    }
    [createTableSqlMutableString appendString:@");"];
    return createTableSqlMutableString;
}

- (void)databaseUpgradeForMrigation {
    NSMutableArray *databaseUpgradeConfigArray = [NSMutableArray arrayWithContentsOfFile:[[[NSBundle mainBundle] bundlePath] stringByAppendingPathComponent:MRDatabaseUpgradeFileName]];
    NSString *oldVersionString;
    if ([YCSystemConfigTool getDatabaseConfigVersioin] == nil) {
        oldVersionString = [[[NSBundle mainBundle] infoDictionary] objectForKey:@"CFBundleShortVersionString"];
    } else {
        oldVersionString = [YCSystemConfigTool getDatabaseConfigVersioin];
    }
    for (NSDictionary *dict in databaseUpgradeConfigArray) {
        if (dict[BelowVersionNeedDatabaseUpgradeKey] != nil) {
            NSString *version = dict[BelowVersionNeedDatabaseUpgradeKey];
            if (version.integerValue < 0 || [version isGreatThanVersionNumber:oldVersionString]) {
                [self databaseCaseFolderUpgradeForDataMigrationWithSqlFileName:dict[DatabaseUpgradeSqlFileNameKey]];
            }
        }
    }

}

- (void)databaseCaseFolderUpgradeForDataMigrationWithSqlFileName:(NSString *)sqlFileName
{
    NSString *localPath = [[NSBundle mainBundle] bundlePath];
    NSString *sqlStrings = [NSString stringWithContentsOfFile:[localPath stringByAppendingPathComponent:sqlFileName] encoding:NSUTF8StringEncoding error:nil];
    NSArray *sqls = [sqlStrings componentsSeparatedByString:@";"];
    for (NSString *sql in sqls) {
        if ([sql isEqualToString:@""]) {
            continue;
        }
        BOOL flag = [_database executeSql:sql params:nil];
        if (!flag) {
            NSLog(@"%@^^^^^^sqlite operation occur error", sql);
        }
    }
}

- (NSString *)getVersionWithDict:(NSDictionary *)dict
{
    NSString *version = nil;
    if (dict[BelowVersionNeedDatabaseUpgradeKey] != nil) {
        version = dict[BelowVersionNeedDatabaseUpgradeKey];
    } else if (dict[DatabaseUpgradeSpecificVersionKey]) {
        version = dict[DatabaseUpgradeSpecificVersionKey];
    } else {
        version = @"1.0.0";
    }
    return version;
}
@end

YCHanddleDatabase.h文件

#import <Foundation/Foundation.h>
#import "YCSqliteResultSet.h"
#import "YCSqlite.h"

@interface YCHanddleDatabase : NSObject

@property (nonatomic, strong) YCSqlite *database;
//数据文件的路径
@property (nonatomic, copy) NSString *databasePath;

- (instancetype)initWithDatabasePath:(NSString *)databasePath;
@end

YCHanddleDatabase文件中主要是一些数据库升级的逻辑。主要是两部分，第一部分是数据库完整表的建立，第二部分是数据升级的操作。分别集中在databaseUpgradeForTables和databaseUpgradeForMrigation 方法中。
下面再给出YCHanddleDatabase中涉及到的一些其他文件
YCSqlite.h文件

#import <Foundation/Foundation.h>
#import "YCSqliteResultSet.h"

@interface YCSqlite : NSObject

- (instancetype)initSqliteWithDbFilePath:(NSString *)dbFilePath;
- (YCSqliteResultSet *)qeuryWithSql:(NSString *)sqlString params:(NSDictionary *)params;
- (BOOL)executeSql:(NSString *)sqlString params:(NSDictionary *)params;
- (NSString *)errmsg;
@end

YCSqlite.m文件

#import "YCSqlite.h"
#include <sqlite3.h>


@interface YCSqlite()

@property (nonatomic, assign) sqlite3 *sqlite;
@property (nonatomic, assign) sqlite3_stmt *currentStmt;
@end
@implementation YCSqlite

- (instancetype)initSqliteWithDbFilePath:(NSString *)dbFilePath {
    if (self = [super init]) {
        if (SQLITE_OK == sqlite3_open([dbFilePath UTF8String], &_sqlite)  ) {
            return self;
        }
    }
    return nil;
}

- (YCSqliteResultSet *)qeuryWithSql:(NSString *)sqlString params:(NSDictionary *)params {
    if (_sqlite == NULL || sqlString == nil ) {
        return nil;
    }
    if (sqlite3_prepare_v2(_sqlite, [sqlString UTF8String], -1, &_currentStmt, NULL) != SQLITE_OK) {
        return nil;
    }
    [self sqliteBindParams:params stmt:_currentStmt destructorType:SQLITE_TRANSIENT];
    return [[YCSqliteResultSet alloc] initWithStmt:_currentStmt sqlite:_sqlite];
}

- (BOOL)executeSql:(NSString *)sqlString params:(NSDictionary *)params {
    if(_sqlite == NULL || sqlString == nil) {
        return NO;
    }

    if(sqlite3_prepare_v2(_sqlite, [sqlString UTF8String], -1, &_currentStmt, NULL) != SQLITE_OK){
        return NO;
    }
    [self sqliteBindParams:params stmt:_currentStmt destructorType:SQLITE_STATIC];
    int rs = sqlite3_step(_currentStmt);
    sqlite3_finalize(_currentStmt);

    return rs == SQLITE_OK || rs == SQLITE_ROW || rs == SQLITE_DONE;
}

- (void)sqliteBindParams:(NSDictionary *)params stmt:(sqlite3_stmt *)stmt destructorType:(sqlite3_destructor_type)type {
    int paramsCount = sqlite3_bind_parameter_count(stmt);
    for (int i = 1; i <= paramsCount; i++) {
        const char *paramName = sqlite3_bind_parameter_name(stmt, i);
        NSString *keyPath = nil;
        if (paramName) {
            keyPath = [NSString stringWithCString:(paramName + 1) encoding:NSUTF8StringEncoding];
        } else {
            keyPath = [NSString stringWithFormat:@"@%d",i-1];
        }

        id value = [params valueForKeyPath:keyPath];
        if (value == nil || [value isKindOfClass:[NSNull class]]) {
            sqlite3_bind_null(stmt, i);
        } else if ([value isKindOfClass:[NSData class]]) {
            sqlite3_bind_blob(stmt, i, [value bytes], (int)[value length], type);
        } else if ([value isKindOfClass:[NSDate class]]) {
            sqlite3_bind_double(stmt, i, [value timeIntervalSince1970]);
        }
        else if ([value isKindOfClass:[NSNumber class]]) {
            if (strcmp([value objCType], @encode(BOOL)) == 0) {
                sqlite3_bind_int(stmt, i, ([value boolValue] ? 1 : 0));
            } else if (strcmp([value objCType], @encode(int)) == 0) {
                sqlite3_bind_int64(stmt, i, [value longValue]);
            } else if (strcmp([value objCType], @encode(long)) == 0) {
                sqlite3_bind_int64(stmt, i, [value longValue]);
            } else if (strcmp([value objCType], @encode(long long)) == 0) {
                sqlite3_bind_int64(stmt, i, [value longLongValue]);
            } else if (strcmp([value objCType], @encode(unsigned long long)) == 0) {
                sqlite3_bind_int64(stmt, i, [value unsignedLongLongValue]);
            } else if (strcmp([value objCType], @encode(float)) == 0) {
                sqlite3_bind_double(stmt, i, [value floatValue]);
            } else if (strcmp([value objCType], @encode(double)) == 0) {
                sqlite3_bind_double(stmt, i, [value doubleValue]);
            } else {
                sqlite3_bind_text(stmt, i, [[value description] UTF8String], -1, type);
            }
        } else if ([value isKindOfClass:[NSString class]]) {
            sqlite3_bind_text(stmt, i, [value UTF8String], -1, type);
        } else {
            NSData *data = [NSPropertyListSerialization dataWithPropertyList:value format:NSPropertyListBinaryFormat_v1_0 options:0 error:nil];
            sqlite3_bind_blob(stmt, i, [data bytes], (int)[data length], type);
        }

    }
}

- (BOOL)hasNext {
    if (_currentStmt) {
        return sqlite3_step(_currentStmt) == SQLITE_ROW;
    }
    return NO;
}

- (NSString *)errmsg {
    if(_sqlite){
        return [NSString stringWithUTF8String:sqlite3_errmsg(_sqlite)];
    }
    return nil;
}
@end

YCSqliteResultSet.h文件

#import <Foundation/Foundation.h>
#include <sqlite3.h>

@interface YCSqliteResultSet : NSObject

- (instancetype)initWithStmt:(sqlite3_stmt *)stmt sqlite:(sqlite3 *)sqlite;
- (BOOL)hasNext;
- (NSString *)stringValueAtIndex:(int)index;
- (void)close;
@end

YCSqliteResultSet.m文件

#import "YCSqliteResultSet.h"

@interface YCSqliteResultSet()
@property (nonnull, assign) sqlite3 *sqlite;
@property (nonnull, assign) sqlite3_stmt *stmt;
@property(nonatomic,readonly,getter = isClosed) BOOL closed;
@end
@implementation YCSqliteResultSet

- (instancetype)initWithStmt:(sqlite3_stmt *)stmt sqlite:(sqlite3 *)sqlite {
    if (stmt == NULL || sqlite == NULL) {
        return nil;
    }
    if (self = [super init]) {
        _sqlite = sqlite;
        _stmt = stmt;
    }
    return self;
}

- (BOOL)hasNext {
    if (_stmt == NULL || _sqlite == NULL) {
        return NO;
    }
    if(_stmt){
        return sqlite3_step(_stmt) == SQLITE_ROW;
    }
    return NO;
}

- (NSString *)stringValueAtIndex:(int)index {
    if(_stmt){
        const char *cString = (const char *)sqlite3_column_text(_stmt, index);
        return cString ? [NSString stringWithCString:cString encoding:NSUTF8StringEncoding] : nil;
    }
    return nil;
}

- (void)close {
    if(!_closed){
        if(_stmt){
            sqlite3_finalize(_stmt);
            _stmt = NULL;
        }
        _closed = YES;
    }
}
@end

YCSystemConfigTool.h文件

#import <Foundation/Foundation.h>
#define DocumentDirectory [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject]
#define SystemConfigPath [DocumentDirectory stringByAppendingPathComponent:@"systemConfig.plist"]
@interface YCSystemConfigTool : NSObject

+ (void)setDatabaseConfigVersion:(NSString *)version;
+ (NSString *)getDatabaseConfigVersioin;
@end

YCSystemConfigTool.m文件

#import "YCSystemConfigTool.h"
#define DatabaseVersionKey @"DatabaseVersion"

@implementation YCSystemConfigTool


+ (void)saveValue:(id)value forKey:(NSString *)key {
    if (!key) {
        return;
    }
    if (!value) {
        value = @"";
    }
    NSMutableDictionary *configDictionary = [NSMutableDictionary dictionaryWithContentsOfFile:SystemConfigPath];
    if (!configDictionary) {
        configDictionary = [NSMutableDictionary dictionary];
    }
    [configDictionary setValue:value forKey:key];
    [configDictionary writeToFile:SystemConfigPath atomically:YES];
}

+ (id)getValueByKey:(NSString *)key {
    if ([[NSFileManager defaultManager] fileExistsAtPath:SystemConfigPath]) {
        NSMutableDictionary *configDictionary = [NSMutableDictionary dictionaryWithContentsOfFile:SystemConfigPath];
        if (configDictionary) {
            return configDictionary[key];
        }
    }
    return nil;

}

+ (void)setDatabaseConfigVersion:(NSString *)version {
    [self saveValue:version forKey:DatabaseVersionKey];
}

+ (NSString *)getDatabaseConfigVersioin {
    return [self getValueByKey:DatabaseVersionKey];
}
@end

到此基本上给出了所有的文件。感兴趣的读者可以主要看YCHanddleDatabase.m文件中的逻辑。理解该逻辑实现数据库升级的功能就不难了。