SQLite3编程初级入门
SQLite3编程初级入门
- #include "sqlite3.h"
- // DATABASE NAME
- #define DB_SQLITE3_TEST_FULLNAME "test.db"
- // TABLE NAME
- #define DB_TestTableName "test"
- static sqlite3* td_db=NULL;
- static char* pTempOutputMsg=NULL;
- // 1. open/creat database
- bool OpenDatabase(char *pInputDatabase)
- {
- if(SQLITE_OK != sqlite3_open(pInputDatabase,&td_db))
- {
- printf("%s\n",sqlite3_errmsg(td_db));
- return false;
- }
- sqlite3_exec(td_db,"PRAGMA synchronous = OFF",0,0,&pTempOutputMsg);//如果有定期备份的机制,而且少量数据丢失可接受,用OFF
- sqlite3_exec(td_db,"PRAGMA page_size = 4096",0,0,&pTempOutputMsg);//只有在未创建数据库时才能设置
- sqlite3_exec(td_db,"PRAGMA cache_size = 8000",0,0,&pTempOutputMsg); //建议改为8000
- sqlite3_exec(td_db,"PRAGMA case_sensitive_like=1",0,0,&pTempOutputMsg);//搜索中文字串
- return true;
- }
- // 2. create table.
- bool CreateTable(char tInputTableNo)
- {
- char pTempCmd[256];
- switch( tInputTableNo )
- {
- case 0:
- sprintf(pTempCmd, "create table %s %s",DB_TestTableName,
- " ("
- "idx integer, "
- "lang integer"
- ");");
- break;
- default:
- break;
- }
- //JPRINTF(("pTempCmd = %s \n",pTempCmd));
- if(SQLITE_OK!=sqlite3_exec(td_db,pTempCmd,NULL,NULL,&pTempOutputMsg))
- {
- printf("%s\n",sqlite3_errmsg(td_db));
- return false;
- }
- else
- return true;
- }
- // 3. insert records into table.
- bool InsertRecords(char* pInputTableName,int pInputInsertCnt)
- {
- int i;
- char pTempCmd[256];
- for(i=0;i<pInputInsertCnt;++i)
- {
- sprintf(pTempCmd,"insert into %s values(7351,%d,0,'WWW',30,3,0,0,0,0,1,2,3,4);",pInputTableName,i);
- //JPRINTF(("SQL: %s \n",pTempCmd));
- if( SQLITE_OK!=sqlite3_exec(td_db,pTempCmd,0,0,&pTempOutputMsg) )
- return false;
- }
- return true;
- }
- // 4. show records in the table.
- bool ShowRecords(char *pInputTableName)
- {
- sqlite3_stmt* stmt=NULL;
- char* szMsg=NULL;
- int one=0,two=0;
- char *pTempName;
- char pTempCmd[256];
- sprintf(pTempCmd,"select * from %s;",pInputTableName);
- if(SQLITE_OK!=sqlite3_prepare(td_db,pTempCmd,strlen(pTempCmd),&stmt,NULL))
- return false;
- printf("\n\tone\t\ttwo\t\tname\n");
- printf("\t--------------------\n");
- while(1)
- {
- if(SQLITE_ROW!=sqlite3_step(stmt))
- break;
- //sqlite3_column_text(stmt,0);
- one=sqlite3_column_int(stmt,0);
- two=sqlite3_column_int(stmt,1);
- pTempName=(char *)sqlite3_column_blob(stmt,2);
- printf("\t%d\t\t%d\t\t%s\n",one,two,pTempName);
- }
- sqlite3_finalize(stmt);
- printf("\n");
- return true;
- }
- // 5. delete the records from table.
- bool DeleteRecords(char *pInputTableName, char *pInputIndexName, int pInputIndexValue)
- {
- char pTempCmd[256];
- sprintf(pTempCmd,"delete from %s where %s=%d;",pInputTableName,pInputIndexName,pInputIndexValue);
- if(SQLITE_OK!=sqlite3_exec(td_db,pTempCmd,NULL,NULL,&pTempOutputMsg))
- {
- printf("%s\n",sqlite3_errmsg(td_db));
- return false;
- }
- else
- return true;
- }
- // 6. drop the point table.
- bool DropTable(char *pInputTableName)
- {
- char pTempCmd[256];
- sprintf(pTempCmd,"drop table %s;",pInputTableName);
- if(SQLITE_OK!=sqlite3_exec(td_db,pTempCmd,NULL,NULL,&pTempOutputMsg))
- {
- printf("%s\n",sqlite3_errmsg(td_db));
- return false;
- }
- else
- return true;
- }
- // 7 .Search database
- bool SearchDatabase(char *pInputTableName, char *pInputIndexName, int pInputIndexValue)
- {
- int nret;
- int one=0,two=0;
- char *pTempName;
- char pTempCmd[256];
- sqlite3_stmt* stmt=NULL;
- sprintf(pTempCmd,"select * from %s where %s=%d;",pInputTableName,pInputIndexName,pInputIndexValue);
- if(SQLITE_OK!=sqlite3_exec(td_db,pTempCmd,NULL,NULL,&pTempOutputMsg))
- {
- printf("%s\n",sqlite3_errmsg(td_db));
- return false;
- }
- printf("\n\tone\t\ttwo\t\tname\n");
- printf("\t--------------------\n");
- while(1)
- {
- if(SQLITE_ROW!=sqlite3_step(stmt))
- break;
- one=sqlite3_column_int(stmt,0);
- two=sqlite3_column_int(stmt,1);
- pTempName=(char *)sqlite3_column_blob(stmt,2);
- printf("\t%d\t\t%d\t\t%s\n",one,two,pTempName);
- }
- sqlite3_finalize(stmt);
- printf("\n");
- return true;
- }
- // 8. creat index on pointer table.
- bool CreatIndexOnDatabase(char *pInputIndexName,char *pInputTableName,char *pInputRawName)
- {
- char pTempCmd[256];
- sprintf(pTempCmd,"create index %s on %s(%s)",pInputIndexName,pInputTableName,pInputRawName);
- if(SQLITE_OK!=sqlite3_exec(td_db,pTempCmd,NULL,NULL,&pTempOutputMsg))
- {
- printf("%s\n",sqlite3_errmsg(td_db));
- return false;
- }
- else
- return true;
- }
- // 9. close database
- bool CloseDatabase()
- {
- sqlite3_close(td_db);
- td_db=NULL;
- return true;
- }
- int Db_Sqlite3_init(void)
- {
- char pTempString[256];
- if(!OpenDatabase(DB_SQLITE3_TEST_FULLNAME))
- {
- JPRINTF(("\nStep 1. Open database failed.\n"));
- return -1;
- }else
- JPRINTF(("\nStep 1. Open database succeeded.\n"));
- if( !CreateTable(0) )
- {
- JPRINTF(("Step 2. Create table failed.\n"));
- }else
- JPRINTF(("Step 2. Create table succeeded.\n"));
- if( !InsertRecords(DB_TestTableName,5) )
- {
- JPRINTF(("Step 3. Insert data to point table Failed.\n"));
- }else
- JPRINTF(("Step 3. Insert data to point table succeeded.\n"));
- if( !ShowRecords(DB_TestTableName))
- {
- JPRINTF(("Step 4. Read data from point table Failed.\n"));
- }else
- JPRINTF(("Step 4. Read data from point table succeeded.\n"));
- if( !DeleteRecords(DB_TestTableName,"lang",2) )
- {
- JPRINTF(("Step 5. Delete data from point table Failed.\n"));
- }else
- JPRINTF(("Step 5. Delete data from point table succeeded.\n"));
- // show again.
- if( !ShowRecords(DB_TestTableName))
- {
- JPRINTF(("Step 4. Read data from point table Failed.\n"));
- }else
- JPRINTF(("Step 4. Read data from point table succeeded.\n"));
- #if 0
- if( !DropTable(DB_TestTableName))
- {
- JPRINTF(("Step 6. delete point table Failed.\n"));
- }else
- JPRINTF(("Step 6. delete point table succeeded.\n"));
- #endif
- if( !SearchDatabase(DB_TestTableName,"lang",3) )
- {
- JPRINTF(("Step 7. Serch data from point table Failed.\n"));
- }else
- JPRINTF(("Step 7. Serch data from point table succeeded.\n"));
- if( !CreatIndexOnDatabase("lang_",DB_TestTableName,"lang") )
- {
- JPRINTF(("Step 8. create index on point table Failed.\n"));
- }else
- JPRINTF(("Step 8. create index on point table succeeded.\n"));
- CloseDatabase();
- }
评论暂时关闭