数据库FMDB详解

数据库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