`
yidongkaifa
  • 浏览: 4039219 次
文章分类
社区版块
存档分类
最新评论

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();

}




分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics