student 表(name,age)
思想:
1.把student表重命名old_student表
2.创建新的表student(name,age,sex)
3.把旧表里的数据拷贝到新表里去
4.删除老的表
DBManager.h
#import <Foundation/Foundation.h>
@interface DBManager : NSObject
+ (instancetype)shareManager;
- (void)createDBWithName:(NSString *)dbName;
- (void)createTableWithName:(NSString *)tableName;
@end
DBManager.m
#import "DBManager.h"
#import <FMDB/FMDB.h>
//1.判断数据库版本号和保存数据库版本号
NSString * const kdbManagerVersion = @"DBManagerVersion";
const static NSInteger DB_MANAGER_VER = 2;
#ifdef DEBUG
#define debugLog(...) NSLog(__VA_ARGS__)
#define debugMethod() NSLog(@"%s", __func__)
#define debugError() NSLog(@"Error at %s Line:%d", __func__, __LINE__)
#else
#define debugLog(...)
#define debugMethod()
#define debugError()
#endif
#define PATH_OF_DOCUMENT [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0]
static NSString * const DEFAULT_DB_NAME = @"app.db";
static NSString *const CREATE_TABLE_SQL =
@"CREATE TABLE IF NOT EXISTS %@ ( \
id TEXT NOT NULL, \
json TEXT NOT NULL, \
createdTime TEXT NOT NULL, \
PRIMARY KEY(id)) \
";
static NSString *const UPDATE_ITEM_SQL = @"REPLACE INTO %@ (id, json, createdTime) values (?, ?, ?)";
static NSString *const QUERY_ITEM_SQL = @"SELECT json, createdTime from %@ where id = ? Limit 1";
static NSString *const SELECT_ALL_SQL = @"SELECT * from %@";
static NSString *const COUNT_ALL_SQL = @"SELECT count(*) as num from %@";
static NSString *const CLEAR_ALL_SQL = @"DELETE from %@";
static NSString *const DELETE_ITEM_SQL = @"DELETE from %@ where id = ?";
static NSString *const DELETE_ITEMS_SQL = @"DELETE from %@ where id in ( %@ )";
static NSString *const DELETE_ITEMS_WITH_PREFIX_SQL = @"DELETE from %@ where id like ? ";
static NSString *const DROP_TABLE_SQL = @" DROP TABLE '%@' ";
@interface DBManager ()
@property (nonatomic, strong) FMDatabaseQueue *dbQueue;
@property (nonatomic, strong) FMDatabase *db;
@end
@implementation DBManager
static DBManager *_manager = nil;
+ (instancetype)shareManager{
static dispatch_once_t onceToken;
dispatch_once(&onceToken, ^{
if (_manager == nil) {
_manager = [[DBManager alloc]init];
}
});
return _manager;
}
- (instancetype)init
{
if (self = [super init]) {
}
return self;
}
- (void)createDBWithName:(NSString *)dbName
{
NSString *path = [PATH_OF_DOCUMENT stringByAppendingPathComponent:dbName];
NSLog(@"---path:%@----",path);
if (_dbQueue) {
[self close];
}
_dbQueue = [FMDatabaseQueue databaseQueueWithPath:path];
}
- (void)createTableWithName:(NSString *)tableName
{
NSInteger oldVersion = [[NSUserDefaults standardUserDefaults] integerForKey:kdbManagerVersion];
NSLog(@"----oldVersion:%ld--------",oldVersion);
if (oldVersion >= DB_MANAGER_VER)
{
[self createNewTableWithName:tableName];
}
else
{
NSLog(@"-升级数据库---");
[self upgrade];
}
}
- (void)createNewTableWithName:(NSString *)tbName
{
NSString *sql = [NSString stringWithFormat:CREATE_TABLE_SQL ,tbName];
__block BOOL result;
[_dbQueue inDatabase:^(FMDatabase * _Nonnull db) {
result = [db executeUpdate:sql];
}];
if (result) {
NSLog(@"--表格创建成功--");
//[self saveDBVersion];
}
else
{
NSLog(@"--表格创建失败--");
}
}
//----------------------------------------------------------------
//MARK
//----------------------------------------------------------------
- (void)saveDBVersion
{
NSLog(@"----保存最终版本-----");
[[NSUserDefaults standardUserDefaults] setInteger:DB_MANAGER_VER forKey:kdbManagerVersion];
[[NSUserDefaults standardUserDefaults] synchronize];
}
//2.数据库升级
- (void)upgrade
{
// 获取旧版本号
NSInteger oldVersionNum = [[NSUserDefaults standardUserDefaults] integerForKey:kdbManagerVersion];
if (oldVersionNum >= DB_MANAGER_VER){
return;
}
// 升级
[self upgrade:oldVersionNum];
// 保存新版本号
[self saveDBVersion];
}
- (void)upgrade:(NSInteger)oldVersion
{
if (oldVersion >= DB_MANAGER_VER)
{
return;
}
switch (oldVersion) {
case 0:
[self upgradeFromOldToOne];
break;
case 1:
[self upgradeFromOldToTwo];
break;
case 2:
NSLog(@"----2变3- 未做--");
break;
default:
break;
}
oldVersion++;
// 判断是否需要升级
[self upgrade:oldVersion];
}
- (void)upgradeFromOldToOne
{
NSLog(@"---0到1----");
NSString *tbName = @"location";
// 执行版本0到版本1的更新
[_dbQueue inDatabase:^(FMDatabase * _Nonnull db) {
[db open];
NSString *sql = [NSString stringWithFormat:@"ALTER TABLE %@ RENAME TO %@", tbName,
[tbName stringByAppendingString:@"_Old"]];
[db executeUpdate:sql];
//创建新的表
NSString *executeStr = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@ ( \
id TEXT NOT NULL, \
json TEXT NOT NULL, \
createdTime TEXT NOT NULL, \
name TEXT, \
PRIMARY KEY(id)) \
",tbName];
BOOL bRet = [db executeUpdate:executeStr];
if (bRet)
{
// 从旧数据表把旧数据插入新的数据表中
NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO %@ SELECT * ,'' FROM %@", tbName, [tbName stringByAppendingString:@"_Old"]];
[db executeUpdate:insertSql];
}
// 删除旧的数据表
[db executeUpdate:[NSString stringWithFormat:@"DROP TABLE %@", [tbName stringByAppendingString:@"_Old"]]];
[db close];
}];
}
- (void)upgradeFromOldToTwo
{
NSLog(@"---老版本到2----");
NSString *tbName = @"location";
[_dbQueue inDatabase:^(FMDatabase * _Nonnull db) {
[db open];
NSString *sql = [NSString stringWithFormat:@"ALTER TABLE %@ RENAME TO %@", tbName,
[tbName stringByAppendingString:@"_Old"]];
[db executeUpdate:sql];
//创建新的表
NSString *executeStr = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@ ( \
id TEXT NOT NULL, \
json TEXT NOT NULL, \
createdTime TEXT NOT NULL, \
name TEXT, \
age TEXT, \
PRIMARY KEY(id)) \
",tbName];
BOOL bRet = [db executeUpdate:executeStr];
if (bRet)
{
// 从旧数据表把旧数据插入新的数据表中
NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO %@ SELECT * ,'' FROM %@", tbName, [tbName stringByAppendingString:@"_Old"]];
[db executeUpdate:insertSql];
}
// 删除旧的数据表
[db executeUpdate:[NSString stringWithFormat:@"DROP TABLE %@", [tbName stringByAppendingString:@"_Old"]]];
[db close];
}];
}
- (void)close
{
[_dbQueue close];
_dbQueue = nil;
}
