#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",&pTempOutputMsg);//如果有定期备份的机制，而且少量数据丢失可接受，用OFF
	sqlite3_exec(td_db,"PRAGMA page_size = 4096",&pTempOutputMsg);//只有在未创建数据库时才能设置
	sqlite3_exec(td_db,"PRAGMA cache_size = 8000",&pTempOutputMsg); //建议改为8000
	sqlite3_exec(td_db,"PRAGMA case_sensitive_like=1",&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,&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,'WWW',30,3,1,2,4);",pInputTableName,i);
		//JPRINTF(("sql: %s \n",pTempCmd));
		if( sqlITE_OK!=sqlite3_exec(td_db,&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,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;",pInputIndexName,pInputIndexValue);

	if(sqlITE_OK!=sqlite3_exec(td_db,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,sqlite3_errmsg(td_db));
		return false;
	}
	else
		return true;
}

// 7 .Search database
bool SearchDatabase(char *pInputTableName,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;",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,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)",pInputRawName);

	if(sqlITE_OK!=sqlite3_exec(td_db,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,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_","lang") )
	{
		JPRINTF(("Step 8. create index on point table Failed.\n"));
	}else
		JPRINTF(("Step 8. create index on point table succeeded.\n"));

	CloseDatabase();

}