#import <Foundation/Foundation.h>

#import <FMDB/FMDatabase.h>

@interface DYDB : NSObject {

}

@property(nonatomic, readonly) FMDatabase *database;

+ (DYDB *) sharedDB;

- (FMDatabase *) connect;

- (void) clearDB;

@end

#import “DYDB.h”

#define kDYDBObvName @”dyobv.sqlite”

@implementation DYDB

static DYDB *_sharedDB;

+ (DYDB *) sharedDB {

if (!_sharedDB) {

_sharedDB = [[DYDB alloc] init];

}

return _sharedDB;

}

- (id) init {

self = [super init];

if (self) {

NSString* docsdir = [NSSearchPathForDirectoriesInDomains( NSDocumentDirectory, NSUserDomainMask, YES) lastObject];

NSString *dbpath = [docsdir stringByAppendingPathComponent:kDYDBObvName];

DLog(@”database file path: %@”, dbpath);

_database = [FMDatabase databaseWithPath:dbpath];

}

return self;

}

- (FMDatabase *) connect {

if ([_database open]) {

return _database;

}

DLog(@”fail to open db…”);

return nil;

}

- (void) clearDB {

dispatch_async(dispatch_get_main_queue(), ^{

if([_database close]) {

_database = nil;

}

});

}

@end

#import “DYLogKeeper.h”

#import <FMDB/FMDatabase.h>

DYLogKeeper * rs2logkeeper(FMResultSet *rs);

@interface DYLogKeeper (DB)

+ (void) createSqliteTable;

+ (BOOL) insert: (DYLogKeeper *) logkeeper;

+ (BOOL) updateContent: (NSString *) content localId: (NSString *) localId;

+ (BOOL) remove: (DYLogKeeper *) logkeeperId;

+ (DYLogKeeper *) findById: (NSString *) localId;

+ (NSArray *) findOfStartDate: (NSDate *) start toDate:(NSDate *) toDate;

@end

#import “DYLogKeeper+DB.h”

#import “DYDB.h”

#import <FMDB/FMDatabase.h>

#import “DYUUID.h”

#import “NSDate+NSDateFormaterCategory.h”

DYLogKeeper * rs2logkeeper(FMResultSet *rs) {

DYLogKeeper *obj = [[DYLogKeeper alloc] init];

obj.localId = [rs stringForColumn:@”local_id”];

obj.logkeeperId = [rs stringForColumn:@”logkeeper_id”];

obj.addtime = [rs dateForColumn:@”add_time”];

obj.content = [rs stringForColumn:@”content”];

obj.deviceId = [rs stringForColumn:@”device_id”];

obj.deviceType = [rs intForColumn:@”device_type”];

obj.channel = [rs intForColumn:@”channel”];

return obj;

}

@implementation DYLogKeeper (DB)

+ (void) createSqliteTable {

DLog(@”check table is exists?”);

FMDatabase *db = [[DYDB sharedDB] connect];

NSString *existsSql = [NSString stringWithFormat:@”select count(name) as countNum from sqlite_master where type = ‘table’ and name = ‘%@’”, @”log_keepers” ];

DLog(@”%@”, existsSql);

FMResultSet *rs = [db executeQuery:existsSql];

if ([rs next]) {

NSInteger count = [rs intForColumn:@”countNum”];

DLog(@”The table count: %d”, count);

if (count == 1) {

DLog(@”log_keepers table is existed.”);

return;

}

DLog(@”log_keepers is not existed.”);

}

[rs close];

DLog(@”create table ….”);

NSString *filePath = [[NSBundle mainBundle] pathForResource:@”log_keepers_table” ofType:@”sql”];

DLog(@”logkeeper sql file: %@”, filePath);

NSError *error;

NSString *sql = [NSString stringWithContentsOfFile:filePath encoding:NSUTF8StringEncoding error:&error];

if (error != nil) {

DLog(@”fail to read sql file: %@”, [error description]);

return;

}

DLog(@”—sql: \n %@”, sql);

DLog(@”execute sql ….”);

if([db executeUpdate:sql]) {

DLog(@”create table succes….”);

} else {

DLog(@”fail to execute update sql..”);

}

[db close];

}

+ (BOOL) insert: (DYLogKeeper *) logkeeper {

DLog(@”insert logkeeper”);

DLog(@”convert int value to NSNumber …”);

logkeeper.localId = [DYUUID uuidString];

NSNumber *channelNum = [NSNumber numberWithInt:logkeeper.channel];

DLog(@”— channelNum: %@”, channelNum);

NSNumber *typeNumber = [NSNumber numberWithInt:logkeeper.deviceType];

DLog(@”— deviceType: %@”, typeNumber);

NSString *sql = @”insert into log_keepers(local_id, logkeeper_id, add_time, content, device_id, device_type, channel) values(?, ?, ?, ?, ?, ?, ?)”;

FMDatabase *db = [[DYDB sharedDB] connect];

if (db == nil) {

DLog(@”fail to create db..”);

}

BOOL ret = [db executeUpdate:sql, logkeeper.localId, logkeeper.logkeeperId,

logkeeper.addtime, logkeeper.content,

logkeeper.deviceId, typeNumber, channelNum];

[db close];

return ret;

}

+ (BOOL) updateContent:(NSString *)content

localId: (NSString *)localId {

DLog(@”update logkeeper content”);

NSString *sql = @”update log_keepers set content = ? where local_id = ?”;

FMDatabase *db = [[DYDB sharedDB] connect];

BOOL ret = [db executeUpdate:sql, content, localId];

[db close];

return ret;

}

- (BOOL) remove: (DYLogKeeper *) logkeeper {

DLog(@”remove logkeeper: %@”, logkeeper.localId);

NSString *sql = @”delete from log_keepers where local_id = ?”;

FMDatabase *db = [[DYDB sharedDB] connect];

BOOL ret = [db executeUpdate:sql, logkeeper.localId];

[db close];

return ret;

}

- (DYLogKeeper *) findById:(NSString *)localId {

DLog(@”find logkeeper by id: %@”, localId);

FMDatabase *db = [[DYDB sharedDB] connect];

FMResultSet *rs = [db executeQuery:@”select * from log_keepers where local_id = ?”, localId];

DYLogKeeper *ret;

if ([rs next]) {

ret = rs2logkeeper(rs);

}

[db close];

return ret;

}

+ (NSArray *) findOfStartDate: (NSDate *) start toDate:(NSDate *) toDate {

DLog(@”find logkeeper between date ….”);

NSString *sql = @”select * from log_keepers where add_time between ? and ?”;

FMDatabase *db = [[DYDB sharedDB] connect];

FMResultSet *rs = [db executeQuery:sql, start, toDate];

NSMutableArray *array = [NSMutableArray arrayWithCapacity:32];

while ([rs next]) {

DYLogKeeper *logkeeper = rs2logkeeper(rs);

[array addObject:logkeeper];

}

[rs close];

[db close];

return array;

}

@end

#import <Foundation/Foundation.h>

@interface DYLogKeeper : NSObject

@property(strong, nonatomic) NSString *localId;

@property(strong, nonatomic) NSDate *addtime;

@property(strong, nonatomic) NSString *deviceId;

@property(strong, nonatomic) NSString *content;

@property(strong, nonatomic) NSString *logkeeperId;

@property(nonatomic) int deviceType;

@property(nonatomic) int channel;

@end