数据库FMDB详解
//
// DataBaseManage.h
// test_cache_01
//
// Created by jeffasd on 16/6/27.
// Copyright © 2016年 jeffasd. All rights reserved.
//
#import <Foundation/Foundation.h>
#import "PlayTopic.h"
@interface DataBaseManager : NSObject
+ (DataBaseManager *)shareDataBaseManger;
- (BOOL)updateTopicPlayTime:(PlayTopic *)playTopic;
- (BOOL)deleteOutOfDateHistory;
- (BOOL)updateTopicPlayTime:(PlayTopic *)playTopic Tid : (NSInteger)tid;
- (NSArray *)readAllData;
@end
//
// DataBaseManage.m
// test_cache_01
//
// Created by jeffasd on 16/6/27.
// Copyright © 2016年 jeffasd. All rights reserved.
//
#import "DataBaseManage.h"
#import "FMDB.h"
#import "PlayTopic.h"
//static NSString *const tableName = @"t_videoPlayHistory";
#define TABLENAME_HISTORY @"t_videoPlayHistory"
static NSString *const kCreateTable = @"CREATE TABLE IF NOT EXISTS t_videoPlayHistory(id integer PRIMARY KEY, topic blob NOT NULL, tid integer NOT NULL, playtime text NOT NULL);";
static NSString *const kUpdateSQL = @"CREATE TABLE IF NOT EXISTS t_videoPlayHistory(id integer PRIMARY KEY, topic blob NOT NULL, tid integer NOT NULL, playtime text NOT NULL);";
//static NSString *const kUpdateSQL = @"CREATE TABLE IF NOT EXISTS t_videoPlayHistory(id integer PRIMARY KEY, topic blob NOT NULL, tid integer NOT NULL, playtime text NOT NULL);";
static FMDatabase *_dataBase;
static int static_i = 34;
@implementation DataBaseManager
+ (instancetype)shareDataBaseManger{
static DataBaseManager *_dataBaseManager = nil;
if (_dataBaseManager == nil) {
static dispatch_once_t onceToken;
dispatch_once(&onceToken, ^{
_dataBaseManager = [[self alloc] init];
[self initDataBase];
});
}
return _dataBaseManager;
}
+ (BOOL)initDataBase
{
//数据库路径
NSString *dataBasePath = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"videoPlayHistory.sqlite"];
_dataBase = [FMDatabase databaseWithPath:dataBasePath];
[_dataBase open];
// create table
BOOL isSuccess = [_dataBase executeUpdate:@"CREATE TABLE IF NOT EXISTS t_videoPlayHistory(id integer PRIMARY KEY, topic blob NOT NULL, tid integer NOT NULL DEFAULT 0, playtime text NOT NULL DEFAULT 0);"];
return isSuccess;
}
//跟新播放记录如果记录不存在则插入一条新的记录 playTime 不用传 默认取本地播放时间
- (BOOL)updateTopicPlayTime:(PlayTopic *)playTopic Tid : (NSInteger)tid {
BOOL isSuccess = NO;
FMResultSet *set = [_dataBase executeQueryWithFormat:@"SELECT COUNT(*) FROM t_videoPlayHistory WHERE tid = %ld ORDER BY playtime DESC LIMIT 0, 1;", (long)tid];
//能取到这条帖子的播放记录 更新本帖子的时间戳
NSString *timeString = [self getCurrentTimeString];
if (set.next) {
isSuccess = [_dataBase executeUpdateWithFormat:@"UPDATE t_videoPlayHistory set playtime = %@ WHERE tid = %ld ORDER BY playtime DESC LIMIT 0, 1;",timeString, (long)tid];
}else{
//取不到这条帖子的播放记录 插入一条新的记录
NSData *data = [NSKeyedArchiver archivedDataWithRootObject:playTopic];
isSuccess = [_dataBase executeUpdateWithFormat:@"INSERT INTO t_videoPlayHistory(topic, tid, playtime) VALUES (%@, %ld, %@) ;", data, (long)tid, timeString];
}
// while (set.next) {
// NSData *playdata = [set objectForColumnName:@"topic"];
// PlayTopic *playtopic = [NSKeyedUnarchiver unarchiveObjectWithData:playdata];
// [allData addObject:playtopic];
// NSInteger tid = [set intForColumn:@"tid"];
// NSString *str = [set stringForColumn:@"playtime"];
// [allData addObject:[NSNumber numberWithInteger:tid]];
// [allData addObject:str];
// }
// if (set.columnCount != 0) {
// update t_student set age = 5 where age > 10 and name != ‘jack’
// select * from t_student order by age desc ; //降序
// select * from t_student limit 4, 8 ;
// 可以理解为:跳过最前面4条语句,然后取8条记录
// select * from t_student limit 7 ;
// 相当于select * from t_student limit 0, 7 ;
// 表示取最前面的7条记录
//SELECT * FROM t_videoPlayHistory WHERE tid = 34 ORDER BY playtime DESC LIMIT 0, 1;
// isSuccess = [_dataBase executeUpdateWithFormat:@"UPDATE t_videoPlayHistory set topic = %@, tid = %ld, playtime = %@ WHRER ;", data, (long)tid, playTime ];
// }
// isSuccess = [_dataBase executeUpdateWithFormat:@"UPDATE t_videoPlayHistory set topic = %@, tid = %ld, playtime = %@ WHRER ;", data, (long)tid, playTime ];
// isSuccess = [_dataBase executeUpdateWithFormat:@"INSERT INTO t_videoPlayHistory(topic, tid, playtime) VALUES (%@, %d, %@) ;", data, 34, @"hello world"];
return isSuccess;
}
//跟新播放记录如果记录不存在则插入一条新的记录
- (BOOL)updateTopicPlayTime:(PlayTopic *)playTopic{
NSData *data = [NSKeyedArchiver archivedDataWithRootObject:playTopic];
BOOL isSuccess = NO;
NSString *timeString = [self getCurrentTimeString];
isSuccess = [_dataBase executeUpdateWithFormat:@"INSERT INTO t_videoPlayHistory(topic, tid, playtime) VALUES (%@, %d, %@) ;", data, static_i++, timeString];
return isSuccess;
}
- (NSString *)getCurrentTimeString{
NSDate* dat = [NSDate dateWithTimeIntervalSinceNow:0];
NSTimeInterval a=[dat timeIntervalSince1970]*1000;
NSString *timeString = [NSString stringWithFormat:@"%.0f", a]; //转为字符型
return timeString;
}
- (NSString *)getSevenDayTimeOffsetString{
NSDate *date = [NSDate date];
#define Day 7
int dataOffset = - Day * 24 * 60 * 60;
// int dataOffset = -100;
NSDate *offsetDate = [date dateByAddingTimeInterval:dataOffset];
NSTimeInterval a=[offsetDate timeIntervalSince1970]*1000;
NSString *timeString = [NSString stringWithFormat:@"%.0f", a]; //转为字符型
return timeString;
}
- (NSArray *)readAllData{
// FMResultSet *set = [_dataBase executeQuery:@"SELECT * FROM t_videoPlayHistory WHERE tid = %d;", 34];
// FMResultSet *set = [_dataBase executeQueryWithFormat:@"SELECT * FROM t_videoPlayHistory WHERE tid = %d;", 34];
// SELECT * FROM t_videoPlayHistory WHERE playtime >= 1467084537336 ORDER BY playtime DESC
NSString *timeOffset = [self getSevenDayTimeOffsetString];
FMResultSet *set = [_dataBase executeQueryWithFormat:@"SELECT * FROM t_videoPlayHistory WHERE playtime >= %@ ORDER BY playtime DESC;", timeOffset];
NSMutableArray *allData = [NSMutableArray array];
while (set.next) {
NSData *playdata = [set objectForColumnName:@"topic"];
PlayTopic *playtopic = [NSKeyedUnarchiver unarchiveObjectWithData:playdata];
NSInteger tid = [set intForColumn:@"tid"];
NSString *str = [set stringForColumn:@"playtime"];
NSMutableDictionary *resultMutDic = [NSMutableDictionary dictionary];
[resultMutDic setValue:playtopic forKey:@"topic"];
[resultMutDic setValue:[NSNumber numberWithInteger:tid] forKey:@"tid"];
[resultMutDic setValue:str forKey:@"playtime"];
[allData addObject:resultMutDic];
}
NSLog(@"the allData is %@", allData);
return [allData copy];
}
- (BOOL)deleteOutOfDateHistory{
// DELETE FROM t_videoPlayHistory WHERE playtime < '1467088946365';
NSString *timeOffset = [self getSevenDayTimeOffsetString];
BOOL isSuccess = [_dataBase executeUpdateWithFormat:@"DELETE FROM t_videoPlayHistory WHERE playtime < %@;", timeOffset];
return isSuccess;
}
@end