//database operation
打開數(shù)據(jù)庫
-(bool) opendatabase{
nsarray*paths = nssearchpathfordirectoriesindomains(nsdocumentdirectory, nsuserdomainmask,yes);
nsstring *documentsdirectory = [pathsobjectatindex:0];
nsstring *path = [documentsdirectorystringbyappendingpathcomponent:@mydb.sql];
nsfilemanager*filemanager = [nsfilemanagerdefaultmanager];
bool find = [filemanagerfileexistsatpath:path];
//找到數(shù)據(jù)庫文件mydb.sql
if (find) {
nslog(@database file have already existed.);
if(sqlite3_open([pathutf8string], &database_) !=sqlite_ok) {
sqlite3_close(database_);
nslog(@error: open database file.);
return no;
}
return yes;
}
if(sqlite3_open([path utf8string], &database_) ==sqlite_ok) {
//bfirstcreate_ = yes;
[selfcreatechannelstable:database_];//在后面實現(xiàn)函數(shù)createchannelstable
return yes;
}else {
sqlite3_close(database_);
nslog(@error: open database file.);
return no;
}
return no;
}
創(chuàng)建表
- (bool) createchannelstable:(sqlite3*)db{
char*sql = create table reports (id integer primary key,stime text,stitle text,scal text,sruntime text);
sqlite3_stmt *statement;
if(sqlite3_prepare_v2(db, sql, -1, &statement,nil) !=sqlite_ok) {
nslog(@error: failed to prepare statement:create reports table);
return no;
}
int success =sqlite3_step(statement);
sqlite3_finalize(statement);
if ( success !=sqlite_done) {
nslog(@error: failed to dehydrate:create table reports);
return no;
}
nslog(@create table 'reports' successed.);
return yes;
}
插入表
- (bool)insertonechannel:(nsstring*)stime mytitle:(nsstring*)stitle mycal:(nsstring*)scal myruntime:(nsstring*)sruntime
{
sqlite3_stmt *statement;
staticchar*sql = insert into reports (id,stime,stitle,scal,sruntime) values(null,?,?,?,?);
//問號的個數(shù)要和(cid,title,imagedata,imagelen)里面字段的個數(shù)匹配，代表未知的值，將在下面將值和字段關 聯(lián)。
int success =sqlite3_prepare_v2(database_, sql, -1, &statement,null);
if (success !=sqlite_ok) {
nslog(@error: failed to insert:channels);
return no;
}
//這里的數(shù)字1，2，3，4代表第幾個問號
//sqlite3_bind_text(statement, 1, stime, -1, sqlite_transient);
char*p = [stime cstringusingencoding:1];
sqlite3_bind_text(statement,1, [stime cstringusingencoding:1], -1,sqlite_transient);
sqlite3_bind_text(statement,2, [stitle cstringusingencoding:1], -1,sqlite_transient);
sqlite3_bind_text(statement,3, [scal cstringusingencoding:1], -1,sqlite_transient);
sqlite3_bind_text(statement,4, [sruntime cstringusingencoding:1], -1,sqlite_transient);
success =sqlite3_step(statement);
sqlite3_finalize(statement);
if (success ==sqlite_error) {
nslog(@error: failed to insert into the database with message.);
return no;
}
nslog(@insert one channel#############:id = _);
return yes;
}
查詢表
- (void) getchannels:(nsmutablearray*)fchannels{
sqlite3_stmt *statement =nil;
char*sql = select * from reports;
if (sqlite3_prepare_v2(database_, sql, -1, &statement,null) !=sqlite_ok) {
nslog(@error: failed to prepare statement with message:get channels.);
}
//查詢結果集中一條一條的遍歷所有的記錄，這里的數(shù)字對應的是列值。
while (sqlite3_step(statement) ==sqlite_row) {
//char* cid = (char*)sqlite3_column_text(statement, 1);
char* stime = (char*)sqlite3_column_text(statement,1);
char* stitle =(char*)sqlite3_column_text(statement,2);
char* scal = (char*)sqlite3_column_text(statement,3);
char* sruntime= (char*)sqlite3_column_text(statement,4);
//nsstring *tmp = [nsstring stringwithcstring:stitle encoding:1];
myreportitem* ri = [[myreportitemalloc]init];
ri.mytime= [nsstring stringwithcstring:stime encoding:1];