里面的那些类不必细究，主要是数据库的代码100%可用。
数据库升级部分，使用switch，没有break，低版本一次向高版本修改。
// DB.h
//iukey
#import <Foundation/Foundation.h>
#import "sqlite3.h"
#import "User.h"
#import "ChatInfo.h"
#import "DescInfo.h"
@interface DBHelper : NSObject{
sqlite3* db;//数据库句柄
// @public DBHelper *instance;
} @property(nonatomic,assign)sqlite3* db;
- (BOOL)insertUserWithTUsersName:(NSString*)name account:(NSString*)account pwd:(NSString*)pwd;
- (NSMutableArray*)quary:(NSString*)str;//查询 - (NSString*)getFilePath;//获取数据库路径
- (BOOL)createDB;//创建数据库
- (BOOL)createTable:(NSString*) creteSql;//创建表
- (User*)getUserWithTUsersByAccount:(NSString* )account;
- (BOOL)updateUserPwdWithTUsers:(NSString*)pwd byAccount:(NSString*)account ;
//+ (DBHelper*) getDBhelperInstance;
- (BOOL)deleteItemWithTable:(NSString*)table_ ByKey:(NSString*)key_ ;
-(BOOL)insertChatRecordWithTChatRecordByChatInfo:(ChatInfo*)ci owner:(NSString *)owner;
- (int)getRecordCountWithTChatRecordByoneJid:(NSString* )oneJid anotherJid:(NSString* )anotherJid owner:(NSString *)owner;
- (NSMutableArray*)getChatInfoWithTChatRecordByoneJid:(NSString* )oneJid anotherJid:(NSString* )anotherJid fromIndex:(int)fromIndex count:(int)count owner:(NSString *)owner;
- (BOOL)deleteChatInfoWithTChatRecordByoneJid:(NSString* )oneJid anotherJid:(NSString* )anotherJid owner:(NSString *)owner; -(NSMutableDictionary*)getRecordCountNotREadWithTChatRecord:(NSString*)toJid owner:(NSString *)owner;
-(BOOL)updateRecordCountNotReadWithChatRecord:(NSString *)fromJid;
-(NSMutableArray*)getRecordNotReadWithTChatRecordFromJid:(NSString*)fromJid owner:(NSString *)owner;
//history
-(BOOL)insertHistoryRecordWithTHistoryRecordByDescInfo:(DescInfo*)di account:(NSString*)account routerjid:(NSString *)routerjid;
- (NSMutableArray*)getHistoryRecordWithTHistoryRecordByAccount:(NSString* )account routerjid:(NSString *)routerjid;
- (BOOL)deleteHistoryWithTHistoryRecordByDeviceType:(NSString *)deviceType account:(NSString* )account routerjid:(NSString *)routerjid;
- (BOOL)deleteHistoryWithTHistoryRecordById:(int)c_id account:(NSString* )account routerjid:(NSString *)routerjid;
- (NSMutableArray*)getHistoryRecordWithTHistoryRecordByUDN:(NSString* )UDN withAccount:(NSString* )account routerjid:(NSString *)routerjid;
-(BOOL)updateHistoryCountNotReadWithHistoryRecordByDeviceType:(NSString *)deviceType account:(NSString* )account routerjid:(NSString *)routerjid;
-(int)getHistoryCountNotReadWithHistoryRecordByAccount:(NSString *)account routerjid:(NSString *)routerjid; @end
// DB.m
//iukey
#import "DBHelper.h"
#import "YHConfig.h"
#import "DescInfo.h"
#import "FromJid.h"
// tid ----table index id
@implementation DBHelper static NSString *createTB_user=@"create table if not exists t_users (c_account text primary key ,c_name text,c_pwd text)"; /*
info_ key-value
db_version --1
...
*/
static NSString *createTB_info=@"create table if not exists t_info (c_key text primary key ,c_value text)";
/*
c_time 存储1970秒数
*/
static NSString *createTB_chat_record=@"create table if not exists t_chat_record (c_id integer primary key autoincrement,c_from_jid text,c_to_jid text,c_chat_time integer,c_msg text,c_has_read integer)"; static NSString *createTB_history_record=@"create table if not exists t_history_record (c_id integer primary key autoincrement,c_deviceType text,c_UDN text,c_friendlyName text,c_history_time integer,c_desc text)"; @synthesize db; - (id)init{
self = [super init];
int dbVersion =;
//检查是否存在数据库文件
if (![self isExistDB]) {
//不存在，则创建
[self createDB];
}else {
//若存在，检测数据库版本，则进行升级， char* info=NULL;
[self getDBInfoValueWithKey:"db_version" value:&info];
if(info == NULL){
return self;
}
dbVersion= atoi(info);
free (info); }
//升级数据库。若第一次创建，则从0开始升级。顺序升级，因此不可以有break
switch (dbVersion) {
case :
//第一次，新建并初始化各表
[self createTable:createTB_user];
//记录版本
[self createTable:createTB_info];
[self setDBInfoValueWithKey:"db_version" value:""];
[self createTable:createTB_chat_record];
[self createTable:createTB_history_record];
case :
[self setDBInfoValueWithKey:"db_version" value:""];
case :
{
NSString *modify=@"alter table t_history_record add column c_user text not null default ''";
[self setDBInfoValueWithKey:"db_version" value:""];
[self execSql:modify];
}
case :
{
NSString *modify=@"alter table t_chat_record add column c_owner text not null default ''";
[self setDBInfoValueWithKey:"db_version" value:""];
[self execSql:modify];
}
case :
{
NSString *modify=@"alter table t_history_record add column c_router text not null default ''";
[self setDBInfoValueWithKey:"db_version" value:""];
[self execSql:modify];
}
case :
{
NSString *modify=@"alter table t_history_record add column c_has_read integer not null default ''";
[self setDBInfoValueWithKey:"db_version" value:""];
[self execSql:modify];
}
//注：数据库升级时候，只需要一次添加case即可，同时更新<span style="font-family: Arial, Helvetica, sans-serif;">db_version值</span>
/*
case 3:
{
//先不加密
//1、将db文件移至portrait，并重命名yunho.db->_yunho.png
//2、得到所有的密码，使用base64存储
//3、用户名输入时候能自动检测是否有匹配的密码并实时的显示 // NSString *modify=@"alter table t_history_record add column c_user text not null default ''";
// [self setDBInfoValueWithKey:"db_version" value:" 4"];
// [self execSql:modify];
}
*/
default:
break;
}
return self;
} - (NSString*)getFilePath{//get db path
NSArray *documentsPaths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask , YES);
NSString *databaseFilePath=[[documentsPaths objectAtIndex:] stringByAppendingPathComponent:[YHConfig DBName ]];
return databaseFilePath ;
} #pragma mark db manage
- (BOOL)createDB{
int ret = sqlite3_open([[self getFilePath] UTF8String], &db);//打开数据库，数据库不存在则创建
if (SQLITE_OK == ret) {//创建成功
sqlite3_close(db);//关闭
return YES;
}else{
return NO;//创建失败
}
}
-(BOOL) isExistDB{
NSFileManager* fm = [[[NSFileManager alloc] init]autorelease];
return [fm fileExistsAtPath:[self getFilePath] ];
}
/*
create table dictionary(ID integer primary key autoincrement,en nvarchar(64),cn nvarchar(128),comment nvarchar(256))
*/
- (BOOL)dropTableWithTableName:(NSString*) tableName{
NSString* dropSql = [[NSString alloc] initWithFormat:@"delete table %@",tableName];
return [self execSql:[dropSql autorelease]];
}
- (BOOL)createTable:(NSString*) creteSql{
return [self execSql:creteSql];
}
-(BOOL) execSql:(NSString*) creteSql{
char* err;
const char* sql = [creteSql UTF8String];//创建表语句
if (sql==NULL) {
return NO;
}
if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String], &db)){
return NO;
} if (SQLITE_OK == sqlite3_exec(db, sql, NULL, NULL, &err)) {//执行创建表语句成功
sqlite3_close(db);
return YES;
}else{//创建表失败
return NO;
} }
//"select * from dictionary where en like ? or cn like ? or comment like ?;";//查询语句
//TODO
- (NSMutableArray*)quary:(NSString *) querySql{ if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String], &db)){
return nil;
}
const char* sql = [querySql UTF8String];//查询语句
sqlite3_stmt* stmt;
if (sqlite3_prepare_v2(db, sql, -, &stmt, nil)==SQLITE_OK) {//准备
// sqlite3_bind_text(stmt, 1,[[NSString stringWithFormat:@"%%%@%%",str]UTF8String], -1, NULL);
// sqlite3_bind_text(stmt, 2, [[NSString stringWithFormat:@"%%%@%%",str]UTF8String], -1, NULL);
// sqlite3_bind_text(stmt, 3, [[NSString stringWithFormat:@"%%%@%%",str]UTF8String], -1, NULL);
}else{
return nil;
}
NSMutableArray* arr =[[NSMutableArray alloc]init];//存放查询结果
while( SQLITE_ROW == sqlite3_step(stmt) ){//执行
char* _en = (char*)sqlite3_column_text(stmt, );
char* _cn = (char*)sqlite3_column_text(stmt, );
char* _comment = (char*)sqlite3_column_text(stmt, ); NSMutableDictionary* dict = [[NSMutableDictionary alloc]init];//单条纪录
[dict setObject:[NSString stringWithCString:_en encoding:NSUTF8StringEncoding] forKey:@"kEN"];
[dict setObject:[NSString stringWithCString:_cn encoding:NSUTF8StringEncoding] forKey:@"kCN"];
[dict setObject:[NSString stringWithCString:_comment encoding:NSUTF8StringEncoding] forKey:@"kCOMMENT"];
[arr addObject:dict];//插入到结果数组
[dict release];
}
sqlite3_finalize(stmt);
sqlite3_close(db);
return [arr autorelease];//返回查询结果数组
} #pragma mark table t_info manage
- (void)getDBInfoValueWithKey:(const char*)key value:(char**)value{
//TODO
if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String] , &db)){
printf("%s:%d query error..\n",__FUNCTION__,__LINE__);
return ;
}
const char* sql = "select * from t_info where c_key =?";//查询语句
sqlite3_stmt* stmt; int error = sqlite3_prepare_v2(db, sql, -, &stmt, nil);
if (error==SQLITE_OK) {//准备
sqlite3_bind_text(stmt, ,key, -, NULL);
}else{
printf("%s:%d query error.. %d\n",__FUNCTION__,__LINE__,error);
return;
} if( SQLITE_ROW == sqlite3_step(stmt) ){//执行
char* v= (char*)sqlite3_column_text(stmt, );
*value = strdup(v); }
sqlite3_finalize(stmt);
sqlite3_close(db);
}
- (BOOL)setDBInfoValueWithKey:(const char*)key value:(const char*)value {
char* info=NULL;
[self getDBInfoValueWithKey:key value:&info];
if (info!= NULL) {
//存在，则更新
[self updateDBInfoValueWithKey:key value:value];
}else{
//不存在，插入
[self insertDBInfoValueWithKey:key value:value]; }
free(info);
return YES;
} - (BOOL)insertDBInfoValueWithKey:(const char*)key value:(const char*)value{
int ret = ;
if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String], &db)){
return NO;
}
const char* sql = "insert into t_info(c_key,c_value) values(?,?);";
sqlite3_stmt* stmt;//
int result =sqlite3_prepare_v2(db, sql, -, &stmt, nil);
printf("%s\n",sqlite3_errmsg(db));
if (result==SQLITE_OK) {//准备语句
sqlite3_bind_text(stmt, , key, -, NULL);//绑定参数
sqlite3_bind_text(stmt, , value, -, NULL);
}else{
return NO;
}
if (SQLITE_DONE == (ret = sqlite3_step(stmt))) {//执行查询
sqlite3_finalize(stmt);
sqlite3_close(db);
return YES;
}else{
return NO;
}
} - (BOOL)updateDBInfoValueWithKey:(const char*)key value:(const char*)value{
int ret = ;
if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String], &db)){
return NO;
}
const char* sql = "update t_info set c_value = ? where c_key = ?;";
sqlite3_stmt* stmt;//
int result =sqlite3_prepare_v2(db, sql, -, &stmt, nil);
printf("%s\n",sqlite3_errmsg(db));
if (result==SQLITE_OK) {//准备语句
sqlite3_bind_text(stmt, , value, -, NULL);
sqlite3_bind_text(stmt, , key, -, NULL);
}else{
return NO;
}
ret = sqlite3_step(stmt);
printf("ret:%d\n",ret);
if (SQLITE_DONE ==ret ) {//执行查询
sqlite3_finalize(stmt);
sqlite3_close(db);
return YES;
}else{
return NO;
}
} #pragma mark table "t_users" manage
- (User*)getUserWithTUsersByAccount:(NSString* )account{ if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String] , &db)){
return nil;
} const char* sql = "select * from t_users where c_account = ?";//查询语句
sqlite3_stmt* stmt;
if (sqlite3_prepare_v2(db, sql, -, &stmt, nil)==SQLITE_OK) {//准备
sqlite3_bind_text(stmt, ,[account UTF8String], -, NULL);
}else{
return nil;
}
User* user = nil;
if( SQLITE_ROW == sqlite3_step(stmt) ){//执行
user = [[[User alloc]init]autorelease];
NSString *name=nil;
NSString *pwd= nil;
if (sqlite3_column_text(stmt, ) != NULL) {
name = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, )]; }
if (sqlite3_column_text(stmt, ) != NULL) {
pwd = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, )];
} user.name =name;
user.account= account;
user.pwd = pwd;
} sqlite3_finalize(stmt);
sqlite3_close(db);
return user;
} - (BOOL)insertUserWithTUsersName:(NSString*)name account:(NSString*)account pwd:(NSString*)pwd{
int ret = ;
if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String], &db)){//打开数据库
return NO;
}
const char* sql = "insert into t_users(c_name,c_account,c_pwd) values(?,?,?);";
sqlite3_stmt* stmt;//
int result =sqlite3_prepare_v2(db, sql, -, &stmt, nil);
printf("%s\n",sqlite3_errmsg(db));
if (result==SQLITE_OK) {//准备语句
sqlite3_bind_text(stmt, , [name UTF8String], -, NULL);//绑定参数
sqlite3_bind_text(stmt, , [account UTF8String], -, NULL);
sqlite3_bind_text(stmt, , [pwd UTF8String], -, NULL);
}else{
return NO;
}
if (SQLITE_DONE == (ret = sqlite3_step(stmt))) {//执行查询
sqlite3_finalize(stmt);
sqlite3_close(db);
return YES;
}else{
return NO;
}
} //根据account 修改用户 的name和pwd
- (BOOL)updateUserPwdWithTUsers:(NSString*)pwd byAccount:(NSString*)account {
int ret = ;
if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String], &db)){//打开数据库
return NO;
}
const char* sql = "update t_users set c_pwd = ? where c_account = ?";
sqlite3_stmt* stmt;//
int result =sqlite3_prepare_v2(db, sql, -, &stmt, nil);
printf("%s\n",sqlite3_errmsg(db));
if (result==SQLITE_OK) {//准备语句
sqlite3_bind_text(stmt, , [pwd UTF8String], -, NULL);
sqlite3_bind_text(stmt, , [account UTF8String], -, NULL);
}else{
return NO;
}
if (SQLITE_DONE == (ret = sqlite3_step(stmt))) {//执行查询
sqlite3_finalize(stmt);
sqlite3_close(db);
return YES;
}else{
return NO;
}
}
#pragma mark table "t_chat_record" manage
-(BOOL)insertChatRecordWithTChatRecordByChatInfo:(ChatInfo*)ci owner:(NSString *)owner{
int ret = ;
if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String], &db)){//打开数据库
return NO;
}
//@"create table if not exists t_chat_record (c_id text primary key ,c_from_jid text,c_to_jid text,c_chat_time integer,c_msg text)";
const char* sql = "insert into t_chat_record(c_from_jid,c_to_jid,c_chat_time,c_msg,c_has_read,c_owner) values(?,?,?,?,?,?);";
sqlite3_stmt* stmt;//
int result =sqlite3_prepare_v2(db, sql, -, &stmt, nil);
printf("%s\n",sqlite3_errmsg(db));
if (result==SQLITE_OK) {//准备语句
sqlite3_bind_text(stmt, , [[ci fromJid] UTF8String], -, NULL);//绑定参数
sqlite3_bind_text(stmt, , [[ci toJid] UTF8String], -, NULL);
sqlite3_bind_int(stmt, , (int)[ci.chatTime timeIntervalSince1970] );
sqlite3_bind_text(stmt, , [ci.msg UTF8String], -, NULL);
sqlite3_bind_int(stmt, , [ci hasRead]);
sqlite3_bind_text(stmt, , [owner UTF8String], -, NULL);
log4debug(@"%d",[ci hasRead]);
}else{
return NO;
}
if (SQLITE_DONE == (ret = sqlite3_step(stmt))) {//执行查询
sqlite3_finalize(stmt);
sqlite3_close(db);
return YES;
}else{
return NO;
} }
//update the count of chat record not read
-(BOOL)updateRecordCountNotReadWithChatRecord:(NSString *)fromJid
{
int ret = ;
if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String] , &db)){
return nil;
}
const char* sql = "update t_chat_record set c_has_read = 1 where c_from_jid = ?";//修改语句
sqlite3_stmt* stmt;
if (sqlite3_prepare_v2(db, sql, -, &stmt, nil)==SQLITE_OK) {//准备
sqlite3_bind_text(stmt, , [fromJid UTF8String], -, NULL); }else{
return nil;
}
if (SQLITE_DONE == (ret = sqlite3_step(stmt))) {//执行查询
sqlite3_finalize(stmt);
sqlite3_close(db);
return YES;
}else{
return NO;
} } -(NSMutableArray*)getRecordNotReadWithTChatRecordFromJid:(NSString*)fromJid owner:(NSString *)owner{
if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String] , &db)){
return nil;
}
const char* sql = "select c_chat_time,c_msg from t_chat_record where c_has_read = 0 and c_from_jid =? and c_owner =? order by c_chat_time asc";//查询语句
sqlite3_stmt* stmt;
if (sqlite3_prepare_v2(db, sql, -, &stmt, nil)==SQLITE_OK) {//准备
sqlite3_bind_text(stmt, ,[fromJid UTF8String], -, NULL);
sqlite3_bind_text(stmt, ,[owner UTF8String], -, NULL);
}else{
return nil;
} NSMutableArray* msgs=[[[NSMutableArray alloc]init]autorelease];
while ( SQLITE_ROW == sqlite3_step(stmt) ){//执行
NSString *msg=nil;
int chatTime = ;
if (sqlite3_column_text(stmt, ) != NULL) {
msg = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, )]; }
chatTime = sqlite3_column_int(stmt, );
NSDate * showTime = [NSDate dateWithTimeIntervalSince1970:chatTime];
[msgs addObject:showTime];
[msgs addObject:msg]; } sqlite3_finalize(stmt);
sqlite3_close(db);
return msgs ;
} //get the count of the chat record not read
-(NSMutableDictionary*)getRecordCountNotREadWithTChatRecord:(NSString*)toJid owner:(NSString *)owner
{
if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String] , &db)){
return nil;
}
const char* sql = "select c_from_jid, count(*) from t_chat_record where c_has_read = 0 and c_to_jid =? and c_owner =? group by c_from_jid ";//查询语句
sqlite3_stmt* stmt;
if (sqlite3_prepare_v2(db, sql, -, &stmt, nil)==SQLITE_OK) {//准备
sqlite3_bind_text(stmt, ,[toJid UTF8String], -, NULL);
sqlite3_bind_text(stmt, ,[owner UTF8String], -, NULL);
}else{
return nil;
} NSMutableDictionary* fis=[[[NSMutableDictionary alloc]init]autorelease];
while ( SQLITE_ROW == sqlite3_step(stmt) ){//执行
FromJid* fi = [[[FromJid alloc]init]autorelease];
NSString *fromJid=nil;
int noReadCount = ; if (sqlite3_column_text(stmt, ) != NULL) {
fromJid = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, )];
}
noReadCount = sqlite3_column_int(stmt, ); fi.fromJid = fromJid;
fi.noReadCount = noReadCount;
[fis setObject:fi forKey:fi.fromJid];
} sqlite3_finalize(stmt);
sqlite3_close(db);
return fis ; } //get the count of the chat record
- (int)getRecordCountWithTChatRecordByoneJid:(NSString* )oneJid anotherJid:(NSString* )anotherJid owner:(NSString *)owner
{
if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String] , &db)){
return nil;
}
//static NSString *createTB_chat_record=@"create table if not exists t_chat_record (c_id integer primary key autoincrement,c_from_jid text,c_to_jid text,c_chat_time integer,c_msg text)";
//不区分from&to，因此两个条件查询
const char* sql = "select count (*) from t_chat_record where ((c_from_jid = ? and c_to_jid=?) or (c_from_jid = ? and c_to_jid=?)) and c_owner = ?";//查询语句
sqlite3_stmt* stmt;
if (sqlite3_prepare_v2(db, sql, -, &stmt, nil)==SQLITE_OK) {//准备
sqlite3_bind_text(stmt, ,[oneJid UTF8String], -, NULL);
sqlite3_bind_text(stmt, ,[anotherJid UTF8String], -, NULL);
sqlite3_bind_text(stmt, ,[anotherJid UTF8String], -, NULL);
sqlite3_bind_text(stmt, ,[oneJid UTF8String], -, NULL);
sqlite3_bind_text(stmt, ,[owner UTF8String], -, NULL);
}else{
return nil;
}
int count=;
if ( SQLITE_ROW == sqlite3_step(stmt) ){//执行
count=sqlite3_column_int(stmt, );
} sqlite3_finalize(stmt);
sqlite3_close(db);
return count ;
} - (NSMutableArray*)getChatInfoWithTChatRecordByoneJid:(NSString* )oneJid anotherJid:(NSString* )anotherJid fromIndex:(int)fromIndex count:(int)count owner:(NSString *)owner
{
if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String] , &db)){
return nil;
}
//static NSString *createTB_chat_record=@"create table if not exists t_chat_record (c_id integer primary key autoincrement,c_from_jid text,c_to_jid text,c_chat_time integer,c_msg text)";
//不区分from&to，因此两个条件查询
const char* sql = "select * from t_chat_record where ((c_from_jid = ? and c_to_jid=?) or (c_from_jid = ? and c_to_jid=?)) and c_owner = ? order by c_chat_time asc limit ?,? ";//查询语句
sqlite3_stmt* stmt;
if (sqlite3_prepare_v2(db, sql, -, &stmt, nil)==SQLITE_OK) {//准备
sqlite3_bind_text(stmt, ,[oneJid UTF8String], -, NULL);
sqlite3_bind_text(stmt, ,[anotherJid UTF8String], -, NULL);
sqlite3_bind_text(stmt, ,[anotherJid UTF8String], -, NULL);
sqlite3_bind_text(stmt, ,[oneJid UTF8String], -, NULL);
sqlite3_bind_text(stmt, ,[owner UTF8String], -, NULL);
sqlite3_bind_int(stmt, , fromIndex);
sqlite3_bind_int(stmt, , count);
}else{
return nil;
}
NSMutableArray *cis=[[[NSMutableArray alloc]initWithCapacity:]autorelease];
while ( SQLITE_ROW == sqlite3_step(stmt) ){//执行
ChatInfo* ci = [[[ChatInfo alloc]init]autorelease];
NSString *fromJid=nil;
NSString *toJid= nil;
NSString *msg=nil;
int time = ;
if (sqlite3_column_text(stmt, ) != NULL) {
fromJid = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, )];
}
if (sqlite3_column_text(stmt, ) != NULL) {
toJid = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, )];
}
if (sqlite3_column_text(stmt, ) != NULL) {
time = sqlite3_column_int(stmt, );
}
if (sqlite3_column_text(stmt, ) != NULL) {
msg = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, )];
}
ci.fromJid=fromJid;
ci.toJid=toJid;
ci.chatTime=[NSDate dateWithTimeIntervalSince1970:time];
ci.msg=msg;
[cis addObject:ci];
} sqlite3_finalize(stmt);
sqlite3_close(db);
return cis ;
} - (BOOL)deleteChatInfoWithTChatRecordByoneJid:(NSString* )oneJid anotherJid:(NSString* )anotherJid owner:(NSString *)owner
{
int ret = ;
if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String], &db)){//打开数据库
return NO;
}
NSString* sql = [NSString stringWithFormat:@"delete from t_chat_record where ((c_from_jid = ? and c_to_jid=?) or (c_from_jid = ? and c_to_jid=?)) and c_owner = ?"];
sqlite3_stmt* stmt;//
int result =sqlite3_prepare_v2(db, [sql UTF8String], -, &stmt, nil);
printf("%s\n",sqlite3_errmsg(db));
if (result==SQLITE_OK) {//准备语句
sqlite3_bind_text(stmt, ,[oneJid UTF8String], -, NULL);
sqlite3_bind_text(stmt, ,[anotherJid UTF8String], -, NULL);
sqlite3_bind_text(stmt, ,[anotherJid UTF8String], -, NULL);
sqlite3_bind_text(stmt, ,[oneJid UTF8String], -, NULL);
sqlite3_bind_text(stmt, ,[owner UTF8String], -, NULL);
}else{
return NO;
}
if (SQLITE_DONE == (ret = sqlite3_step(stmt))) {//执行查询
sqlite3_finalize(stmt);
sqlite3_close(db);
return YES;
}else{
return NO;
} } #pragma mark table "t_history_record" manage
-(BOOL)insertHistoryRecordWithTHistoryRecordByDescInfo:(DescInfo*)di account:(NSString*)account routerjid:(NSString *)routerjid{
int ret = ;
if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String], &db)){//打开数据库
return NO;
}
//@"create table if not exists t_history_record (c_id text primary key ,c_deviceType text,c_UDN text,c_friendlyName text,c_history_time integer,c_desc text)";
const char* sql = "insert into t_history_record(c_deviceType,c_UDN,c_friendlyName,c_history_time,c_desc,c_user,c_router,c_has_read) values(?,?,?,?,?,?,?,?);";
sqlite3_stmt* stmt;//
int result =sqlite3_prepare_v2(db, sql, -, &stmt, nil);
printf("%s\n",sqlite3_errmsg(db));
if (result==SQLITE_OK) {//准备语句
sqlite3_bind_text(stmt, , [[di deviceType] UTF8String], -, NULL);//绑定参数
sqlite3_bind_text(stmt, , [[di deviceUDN] UTF8String], -, NULL);
sqlite3_bind_text(stmt, , [[di friendlyName] UTF8String], -, NULL);
sqlite3_bind_int(stmt, , (int)[di.time timeIntervalSince1970] );
sqlite3_bind_text(stmt, , [di.deviceDesc UTF8String], -, NULL);
sqlite3_bind_text(stmt, , [account UTF8String], -, NULL);
sqlite3_bind_text(stmt, , [routerjid UTF8String], -, NULL);
sqlite3_bind_int(stmt, , [di hasRead]);
}else{
return NO;
}
if (SQLITE_DONE == (ret = sqlite3_step(stmt))) {//执行查询
sqlite3_finalize(stmt);
sqlite3_close(db);
return YES;
}else{
return NO;
} }
- (NSMutableArray*)getHistoryRecordWithTHistoryRecordByUDN:(NSString* )UDN withAccount:(NSString* )account routerjid:(NSString *)routerjid
{
if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String] , &db)){
return nil;
}
const char* sql = "select * from t_history_record where c_UDN = ? and c_user =? and c_router =? order by c_history_time desc";//查询语句
sqlite3_stmt* stmt;
if (sqlite3_prepare_v2(db, sql, -, &stmt, nil)==SQLITE_OK) {//准备
sqlite3_bind_text(stmt, ,[UDN UTF8String], -, NULL);
sqlite3_bind_text(stmt, ,[account UTF8String], -, NULL);
sqlite3_bind_text(stmt, ,[routerjid UTF8String], -, NULL);
}else{
return nil;
}
NSMutableArray *dis=[[[NSMutableArray alloc]initWithCapacity:]autorelease];
while ( SQLITE_ROW == sqlite3_step(stmt) ){//执行
DescInfo* di = [[[DescInfo alloc]init]autorelease];
NSString* deviceType = nil;
NSString* friendlyName= nil;
NSString* deviceUDN= nil;
NSString* deviceDesc= nil;
NSDate* time= nil;
int c_id = ;
if (sqlite3_column_text(stmt, ) != NULL) {
c_id = sqlite3_column_int(stmt, ); }
if (sqlite3_column_text(stmt, ) != NULL) {
deviceType = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, )];
}
if (sqlite3_column_text(stmt, ) != NULL) {
deviceUDN = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, )];
}
if (sqlite3_column_text(stmt, ) != NULL) {
friendlyName = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, )];
}
if (sqlite3_column_text(stmt, ) != NULL) {
int i = sqlite3_column_int(stmt, );
time = [NSDate dateWithTimeIntervalSince1970:i];
// time = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, 4)];
}
if (sqlite3_column_text(stmt, ) != NULL) {
deviceDesc = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, )];
}
di.deviceType=deviceType;
di.deviceUDN=deviceUDN;
di.friendlyName=friendlyName;
di.time = time;
di.deviceDesc =deviceDesc;
di.c_id = c_id;
[dis addObject:di];
} sqlite3_finalize(stmt);
sqlite3_close(db);
return dis ; }
- (NSMutableArray*)getHistoryRecordWithTHistoryRecordByAccount:(NSString* )account routerjid:(NSString *)routerjid
{
if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String] , &db)){
return nil;
}
const char* sql = "select * from t_history_record where c_user = ? and c_router = ? order by c_history_time desc";//查询语句
sqlite3_stmt* stmt;
if (sqlite3_prepare_v2(db, sql, -, &stmt, nil)==SQLITE_OK) {//准备
sqlite3_bind_text(stmt, ,[account UTF8String], -, NULL);
sqlite3_bind_text(stmt, ,[routerjid UTF8String], -, NULL);
}else{
return nil;
}
NSMutableArray *dis=[[[NSMutableArray alloc]initWithCapacity:]autorelease];
while ( SQLITE_ROW == sqlite3_step(stmt) ){//执行
DescInfo* di = [[[DescInfo alloc]init]autorelease];
NSString* deviceType = nil;
NSString* friendlyName= nil;
NSString* deviceUDN= nil;
NSString* deviceDesc= nil;
NSDate* time= nil;
int c_id = ;
if (sqlite3_column_text(stmt, ) != NULL) {
c_id = sqlite3_column_int(stmt, ); }
if (sqlite3_column_text(stmt, ) != NULL) {
deviceType = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, )];
}
if (sqlite3_column_text(stmt, ) != NULL) {
deviceUDN = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, )];
}
if (sqlite3_column_text(stmt, ) != NULL) {
friendlyName = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, )];
}
if (sqlite3_column_text(stmt, ) != NULL) {
int i = sqlite3_column_int(stmt, );
time = [NSDate dateWithTimeIntervalSince1970:i];
// time = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, 4)];
}
if (sqlite3_column_text(stmt, ) != NULL) {
deviceDesc = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, )];
}
di.deviceType=deviceType;
di.deviceUDN=deviceUDN;
di.friendlyName=friendlyName;
di.time = time;
di.deviceDesc =deviceDesc;
di.c_id = c_id;
[dis addObject:di];
} sqlite3_finalize(stmt);
sqlite3_close(db);
return dis ; }
- (BOOL)deleteHistoryWithTHistoryRecordByDeviceType:(NSString *)deviceType account:(NSString* )account routerjid:(NSString *)routerjid
{
int ret = ;
if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String], &db)){//打开数据库
return NO;
}
NSString* sql = [NSString stringWithFormat:@"delete from t_history_record where c_deviceType = ? and c_user = ? and c_router = ?"];
sqlite3_stmt* stmt;//
int result =sqlite3_prepare_v2(db, [sql UTF8String], -, &stmt, nil);
printf("%s\n",sqlite3_errmsg(db));
if (result==SQLITE_OK) {//准备语句
sqlite3_bind_text(stmt, , [deviceType UTF8String], -, NULL);
sqlite3_bind_text(stmt, , [account UTF8String], -, NULL);
sqlite3_bind_text(stmt, , [routerjid UTF8String], -, NULL);
}else{
return NO;
}
if (SQLITE_DONE == (ret = sqlite3_step(stmt))) {//执行查询
sqlite3_finalize(stmt);
sqlite3_close(db);
return YES;
}else{
return NO;
}
}
- (BOOL)deleteHistoryWithTHistoryRecordById:(int)c_id account:(NSString* )account routerjid:(NSString *)routerjid {
int ret = ;
if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String], &db)){//打开数据库
return NO;
}
NSString* sql = [NSString stringWithFormat:@"delete from t_history_record where c_id = ? and c_user = ? and c_router = ?"];
sqlite3_stmt* stmt;//
int result =sqlite3_prepare_v2(db, [sql UTF8String], -, &stmt, nil);
printf("%s\n",sqlite3_errmsg(db));
if (result==SQLITE_OK) {//准备语句
// sqlite3_bind_text(stmt, 1, [c_id UTF8String], -1, NULL);
sqlite3_bind_int(stmt, , c_id);
sqlite3_bind_text(stmt, , [account UTF8String], -, NULL);
sqlite3_bind_text(stmt, , [routerjid UTF8String], -, NULL);
}else{
return NO;
}
if (SQLITE_DONE == (ret = sqlite3_step(stmt))) {//执行查询
sqlite3_finalize(stmt);
sqlite3_close(db);
return YES;
}else{
return NO;
} } //update the count of history record not read
-(BOOL)updateHistoryCountNotReadWithHistoryRecordByDeviceType:(NSString *)deviceType account:(NSString* )account routerjid:(NSString *)routerjid
{
int ret = ;
if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String] , &db)){
return nil;
}
NSString * sql = [NSString stringWithFormat:@"update t_history_record set c_has_read = 1 where c_deviceType = ? and c_user = ? and c_router = ?"];//查询语句
sqlite3_stmt* stmt;//
int result =sqlite3_prepare_v2(db, [sql UTF8String], -, &stmt, nil);
printf("%s\n",sqlite3_errmsg(db));
if (result==SQLITE_OK) {//准备
sqlite3_bind_text(stmt, , [deviceType UTF8String], -, NULL);
sqlite3_bind_text(stmt, , [account UTF8String], -, NULL);
sqlite3_bind_text(stmt, , [routerjid UTF8String], -, NULL);
}else{
return nil;
}
if (SQLITE_DONE == (ret = sqlite3_step(stmt))) {//执行查询
sqlite3_finalize(stmt);
sqlite3_close(db);
return YES;
}else{
return NO;
}
} -(int)getHistoryCountNotReadWithHistoryRecordByAccount:(NSString *)account routerjid:(NSString *)routerjid
{
if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String] , &db)){
return nil;
}
NSString * sql = [NSString stringWithFormat:@"select count (*) from t_history_record where c_has_read = 0 and c_user = ? and c_router = ?"];//查询语句
sqlite3_stmt* stmt;//
int result =sqlite3_prepare_v2(db, [sql UTF8String], -, &stmt, nil);
printf("%s\n",sqlite3_errmsg(db));
if (result==SQLITE_OK) {//准备
sqlite3_bind_text(stmt, , [account UTF8String], -, NULL);
sqlite3_bind_text(stmt, , [routerjid UTF8String], -, NULL);
}else{
return nil;
}
int count=;
if ( SQLITE_ROW == sqlite3_step(stmt) ){//执行
count=sqlite3_column_int(stmt, );
} sqlite3_finalize(stmt);
sqlite3_close(db);
return count ;
} #pragma mark manage normal tables
- (BOOL)deleteItemWithTable:(NSString*)table_ ByKey:(NSString*)key_ {
int ret = ;
if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String], &db)){//打开数据库
return NO;
} NSString* sql= [NSString stringWithFormat:@"delete from %@ where c_account = ?",table_]; sqlite3_stmt* stmt;//
int result =sqlite3_prepare_v2(db, [sql UTF8String], -, &stmt, nil);
printf("%s\n",sqlite3_errmsg(db));
if (result==SQLITE_OK) {//准备语句
sqlite3_bind_text(stmt, , [key_ UTF8String], -, NULL);
}else{
return NO;
}
if (SQLITE_DONE == (ret = sqlite3_step(stmt))) {//执行查询
sqlite3_finalize(stmt);
sqlite3_close(db);
return YES;
}else{
return NO;
}
} @end
转：http://blog.csdn.net/kafeidev/article/details/17028423
