ios mysql 修改数据库,iOS App版本更新伴随数据库表结构改变的处理

/*

数据库升级流程

1.每一次运行程序，判断数据库是否存在。如果不存在，创建数据库。如果存在，进行其他后续处理。

2.第一次安装app，创建数据库，并将版本号存入数据库中。同时保存一个当前版本号加1的字段到数据库中。

3.更新app，从数据库中读取到上一次保存的版本号字段。比如要升级为2，会直接从case2开始执行。修改完数据结构后，再一次将版本号字段存入数据库。

4.每一次数据库结构有更新，直接在后面加case语句即可。

*/

YSqliteManager.h

//

// YSqliteManager.h

// DatabaseUpdateDemo

//

// Created by *** on 2017/2/15.

// Copyright © 2017年 yuanzhiying. All rights reserved.

//

#import

#import

#import "YUserData.h"

@interface YSqliteManager : NSObject {

sqlite3 *database;//数据库

NSString *databasePath;//数据库文件路径

BOOL isOpend;//是否已经打开数据库

}

#pragma mark - 初始化

//初始化数据库

+ (YSqliteManager *)shareManager;

//打开数据库

- (BOOL)openDatabase;

//关闭数据库

- (void)closeDatabase;

#pragma mark - 所有表的操作

#pragma mark 用户表

//是否存在

- (BOOL)isExistUserWithUserID:(NSInteger)_userid;

//插入

- (void)insertUserInfoWithUserInfo:(YUserData *)_userinfo;

//更新

- (void)updateUserInfoWithUserInfo:(YUserData *)_userinfo UserID:(NSInteger)_userid;

//获取用户数据

- (YUserData *)getUserInfoWithUserID:(NSInteger)_userid;

//删除用户数据

- (void)deleteUserInfoDataWithUserID:(NSInteger)_userid;

@end

YSqliteManager.m

//

// YSqliteManager.m

// DatabaseUpdateDemo

//

// Created by *** on 2017/2/15.

// Copyright © 2017年 yuanzhiying. All rights reserved.

//

#import "YSqliteManager.h"

static YSqliteManager *sharedManager = nil;

static dispatch_once_t predicate;

static NSString *APP_DB_IDENTIFIER = @"iOS_DatabaseUpdateDemo";

//数据库保存目录

static NSString *databaseFilePath() {

NSArray *paths = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES);

NSString *directory = [paths objectAtIndex:0];

NSString *dbPath = [directory stringByAppendingPathComponent:[[[NSBundle mainBundle] bundleIdentifier] stringByAppendingString:@".db"]];

NSLog(@"dbPath:%@",dbPath);

[[NSFileManager defaultManager] createDirectoryAtPath:dbPath withIntermediateDirectories:YES attributes:nil error:nil];

return [[dbPath stringByAppendingPathComponent:APP_DB_IDENTIFIER] stringByAppendingString:@".sqlite"];

}

@implementation YSqliteManager

#pragma mark - 初始化

//单例

+ (YSqliteManager *)shareManager {

dispatch_once(&predicate, ^{

sharedManager = [[self alloc] init];

});

return sharedManager;

}

//构造方法

- (instancetype)init {

//初始化文件路径

databasePath = databaseFilePath();

//设置数据库版本为1

NSInteger dbVersion = 1;

if (self = [super init]) {

//判断本地有没有数据库文件

//如果数据库文件不存在

if (![[NSFileManager defaultManager] fileExistsAtPath:databasePath]) {

//不存在，初始化数据库

BOOL isCreateSuccess = [self initDatabase];

if (isCreateSuccess) {

//初始化数据库成功

isOpend = [self openDatabase];

}

} else {

//存在数据库，获取版本信息

isOpend = [self openDatabase];

dbVersion = [self getDatabaseVersion];

}

// NSLog(@"log dbVersion:%ld",(long)dbVersion);

/*

根据数据库版本号，逐级升级数据库

之后更新，只需在后面添加case即可(不可包含break，需要逐个case执行升级!!!)

*/

switch (dbVersion) {

case 1://用户第一次安装

{

//将新版本号存入数据库

if (![self isExistDatabaseVersion]) {

[self insertDatabaseVersionWithVersion:(dbVersion + 1)];

} else {

[self updateDatabaseVersionWithVersion:(dbVersion + 1)];

}

dbVersion = [self getDatabaseVersion];

}

case 2://修改数据库结构

{

/*

更新内容：1->2

1.user_info表增加age字段

*/

//执行对应更新sql语句组

[self updateDatabaseWithNewVersion:dbVersion];

//将新版本号存入数据库

if (![self isExistDatabaseVersion]) {

[self insertDatabaseVersionWithVersion:(dbVersion + 1)];

} else {

[self updateDatabaseVersionWithVersion:(dbVersion + 1)];

}

dbVersion = [self getDatabaseVersion];

}

case 3://修改数据库结构

{

/*

更新内容：2->3

1.user_info表增加sex字段

2.增加app_data表

*/

//执行对应更新sql语句组

[self updateDatabaseWithNewVersion:dbVersion];

//将新版本号存入数据库

if (![self isExistDatabaseVersion]) {

[self insertDatabaseVersionWithVersion:(dbVersion + 1)];

} else {

[self updateDatabaseVersionWithVersion:(dbVersion + 1)];

}

dbVersion = [self getDatabaseVersion];

}

default:

break;

}

}

return self;

}

#pragma mark - 数据库相关操作

//打开数据库

- (BOOL)openDatabase {

if (!isOpend) {

if (sqlite3_open([databasePath UTF8String], &database) != SQLITE_OK) {

sqlite3_close(database);

} else {

isOpend = YES;

}

}

return isOpend;

}

//关闭数据库

- (void)closeDatabase {

if (isOpend) {

sqlite3_close(database);

isOpend = NO;

}

}

//初始化数据库

- (BOOL)initDatabase {

NSString *path = [[NSBundle mainBundle] pathForResource:[NSString stringWithFormat:@"sql_default"] ofType:@"plist"];

NSArray *array = [[NSArray alloc] initWithContentsOfFile:path];

if (array != nil) {

if (sqlite3_open([databasePath UTF8String], &database) != SQLITE_OK) {

sqlite3_close(database);

return NO;

} else {

char *errorMsg = nil;

for (int i = 0; i < array.count; i ++) {

if (sqlite3_exec(database, [[array objectAtIndex:i] UTF8String], nil, nil, &errorMsg) != SQLITE_OK) {

sqlite3_free(errorMsg);

return NO;

}

sqlite3_free(errorMsg);

}

}

} else {

return NO;

}

return YES;

}

//执行更新sql语句组

- (void)updateDatabaseWithNewVersion:(NSInteger)_newversion {

NSString *path = [[NSBundle mainBundle] pathForResource:[NSString stringWithFormat:@"sql_update_%ld",(long)_newversion] ofType:@"plist"];

if ([[NSFileManager defaultManager] fileExistsAtPath:path]) {

NSArray *array = [[NSArray alloc] initWithContentsOfFile:path];

if (array != nil) {

if (sqlite3_open([databasePath UTF8String], &database) != SQLITE_OK) {

sqlite3_close(database);

return;

} else {

char *errorMsg = nil;

for (int i = 0; i < array.count; i ++) {

if (sqlite3_exec(database, [[array objectAtIndex:i] UTF8String], nil, nil, &errorMsg) != SQLITE_OK) {

sqlite3_free(errorMsg);

return;

}

sqlite3_free(errorMsg);

}

}

}

}

}

#pragma mark - 数据库版本号 操作

//是否存在版本号数据

- (BOOL)isExistDatabaseVersion {

@synchronized(sharedManager) {

BOOL state = NO;

if (isOpend) {

sqlite3_stmt *statement;

const char *sqlQuery = "SELECT COUNT(db_version_num) FROM db_version";

if (sqlite3_prepare_v2(database, sqlQuery, -1, &statement, nil) == SQLITE_OK) {

if (sqlite3_step(statement) == SQLITE_ROW) {

if (sqlite3_column_int(statement, 0) == 1) {

state = YES;

}

}

}

sqlite3_finalize(statement);

}

return state;

}

}

//插入数据库版本号

- (void)insertDatabaseVersionWithVersion:(NSInteger)_dbVersion {

@synchronized(sharedManager) {

if (isOpend) {

sqlite3_stmt *statement;

const char *sqlQuery = "INSERT INTO db_version(db_version_num) VALUES(?)";

if (sqlite3_prepare_v2(database, sqlQuery, -1, &statement, nil) == SQLITE_OK) {

sqlite3_bind_int64(statement, 1, _dbVersion);

if (sqlite3_step(statement) == SQLITE_DONE) {

// NSLog(@"插入成功");

}else{

// NSLog(@"插入失败");

}

}

sqlite3_finalize(statement);

}

}

}

//更新数据库新版本号

- (void)updateDatabaseVersionWithVersion:(NSInteger)_dbVersion {

@synchronized(sharedManager) {

if (isOpend) {

sqlite3_stmt *statement;

const char *sqlQuery = "UPDATE db_version SET db_version_num=?";

if (sqlite3_prepare_v2(database, sqlQuery, -1, &statement, nil) == SQLITE_OK) {

sqlite3_bind_int64(statement, 1, _dbVersion);

if (sqlite3_step(statement) == SQLITE_DONE) {

//更新成功

}

}

sqlite3_finalize(statement);

}

}

}

//获取数据库版本号

- (NSInteger)getDatabaseVersion {

@synchronized(sharedManager) {

NSInteger db_version_num = 0;

if (isOpend) {

sqlite3_stmt *statement;

const char *sqlQuery = "SELECT db_version_num FROM db_version";

if (sqlite3_prepare_v2(database, sqlQuery, -1, &statement, nil) == SQLITE_OK) {

if (sqlite3_step(statement) == SQLITE_ROW) {

db_version_num = sqlite3_column_int(statement, 0);

}

}

sqlite3_finalize(statement);

}

return db_version_num;

}

}

#pragma mark - 所有表的操作

#pragma mark 用户表

//是否存在用户

- (BOOL)isExistUserWithUserID:(NSInteger)_userid {

@synchronized(sharedManager) {

BOOL state = NO;

if (isOpend) {

sqlite3_stmt *statement;

const char *sqlQuery = "SELECT COUNT(user_id) FROM user_info WHERE user_id=?";

if (sqlite3_prepare_v2(database, sqlQuery, -1, &statement, nil) == SQLITE_OK) {

sqlite3_bind_int64(statement, 1, _userid);

if (sqlite3_step(statement) == SQLITE_ROW) {

if (sqlite3_column_int(statement, 0) == 1) {

state = YES;

}

}

}

sqlite3_finalize(statement);

}

return state;

}

}

//插入

- (void)insertUserInfoWithUserInfo:(YUserData *)_userinfo {

@synchronized(sharedManager) {

if (isOpend) {

sqlite3_stmt *statement;

const char *sqlQuery = "INSERT INTO user_info (user_id,user_name,age,sex) VALUES(?,?,?,?)";

if (sqlite3_prepare_v2(database, sqlQuery, -1, &statement, nil) == SQLITE_OK) {

sqlite3_bind_int64(statement, 1, _userinfo.userID);

sqlite3_bind_text(statement, 2, [_userinfo.userName UTF8String], -1, SQLITE_TRANSIENT);

sqlite3_bind_int64(statement, 3, _userinfo.age);

sqlite3_bind_text(statement, 4, [_userinfo.sex UTF8String], -1, SQLITE_TRANSIENT);

if (sqlite3_step(statement) == SQLITE_DONE) {

}

}

sqlite3_finalize(statement);

}

}

}

//更新

- (void)updateUserInfoWithUserInfo:(YUserData *)_userinfo UserID:(NSInteger)_userid {

@synchronized(sharedManager) {

if (isOpend) {

sqlite3_stmt *statement;

const char *sqlQuery = "UPDATE user_info set user_id=?,user_name=?,age=?,sex=? WHERE user_id=?";

if (sqlite3_prepare_v2(database, sqlQuery, -1, &statement, nil) == SQLITE_OK) {

sqlite3_bind_int64(statement, 1, _userinfo.userID);

sqlite3_bind_text(statement, 2, [_userinfo.userName UTF8String], -1, SQLITE_TRANSIENT);

sqlite3_bind_int64(statement, 3, _userinfo.age);

sqlite3_bind_text(statement, 4, [_userinfo.sex UTF8String], -1, SQLITE_TRANSIENT);

sqlite3_bind_int64(statement, 5, _userinfo.userID);

if (sqlite3_step(statement) == SQLITE_DONE) {

}

}

sqlite3_finalize(statement);

}

}

}

//获取用户数据

- (YUserData *)getUserInfoWithUserID:(NSInteger)_userid {

@synchronized(sharedManager) {

YUserData *tempUserInfo = nil;

if (isOpend) {

sqlite3_stmt *statement;

const char *sqlQuery = "SELECT user_id,user_name,age,sex FROM user_info WHERE user_id=?";

if (sqlite3_prepare_v2(database, sqlQuery, -1, &statement, nil) == SQLITE_OK) {

sqlite3_bind_int64(statement, 1, _userid);

while (sqlite3_step(statement) == SQLITE_ROW) {

tempUserInfo = [[YUserData alloc] init];

tempUserInfo.userID = sqlite3_column_int(statement, 0);

char *username = (char*)sqlite3_column_text(statement, 1);

if (username != nil) {

tempUserInfo.userName = [NSString stringWithUTF8String:username];

username = nil;

}

tempUserInfo.age = sqlite3_column_int(statement, 2);

char *sex = (char*)sqlite3_column_text(statement, 3);

if (sex != nil) {

tempUserInfo.sex = [NSString stringWithUTF8String:sex];

sex = nil;

}

}

}

sqlite3_finalize(statement);

}

return tempUserInfo;

}

}

//删除用户数据

- (void)deleteUserInfoDataWithUserID:(NSInteger)_userid {

@synchronized(sharedManager) {

if (isOpend) {

sqlite3_stmt *statement;

const char *sqlQuery = "DELETE FROM user_info WHERE user_id=?";

if (sqlite3_prepare_v2(database, sqlQuery, -1, &statement, nil) == SQLITE_OK) {

sqlite3_bind_int64(statement, 1, _userid);

if (sqlite3_step(statement) == SQLITE_DONE) {

}

}

sqlite3_finalize(statement);

}

}

}

@end

QQ20170215-174043@2x.png