再学SQLite3 API

这次进一步看了看SQLigte3 的API，不用上次写的类包装，而直接用C式写法做了一个练习。列在这里备忘。

module sqlite3Ex01;

import samsTools.utils;

import sqlite4D.sqlite3_imp;

import std.stdio;
import std.string;
import std.conv;

import core.stdc.stdlib;
import core.stdc.stdio;
import core.stdc.string;

bool createContacts(sqlite3* db)
{
	
	char* errmsg;
	
	db=initDB();
	if(db is null)
	{
		printf("error occurred.cann't create new table.\n");
		return false;
	}
	
	//printf("Init success.creating new table...\n");
	
	
	int ret=sqlite3_exec(db,toCstring("create table contacts (\n"
			" id integer primary key,	\n"
			" first_name char,			\n"
			" last_name char,			\n"
			" email char)"),
			null,
			null,
			&errmsg);

		if( ret!=SQLITE_OK)
		{
			printf("\n----------------------------------\n"
					"Create table 'contacts' failed because:\n%s.\n"
					"You can still use this table anyhow.\n"
					"------------------------------------\n",errmsg);
			sqlite3_free(errmsg);
			return false;
		}
		else
		{
			printf("contacts table was created.\n");
			return true;
		}
		closeDB(db);
		assert(0);
	
}
sqlite3* initDB(string dbname="progdb")
{
	
	sqlite* db;
	int result=sqlite3_open(toCstring(dbname),&db);

	
	if( result==SQLITE_OK)//just to make sure 'result' doesn't return other value 
	{
		printf("Successfully connected to database.\n");
		printf("Version:%s\n",sqlite3_libversion);
		
		return db;
	}
	else
	{
		printf("Error occurred.\nError code: %d\nError msg: %s\n",
			sqlite3_errcode(db),sqlite3_errmsg(db));
		sqlite3_free(db);
		
		return null;
	}
	
}
bool closeDB(sqlite3* db)
{
	
	if(sqlite3_close(db)!=SQLITE_OK)
		return false;
	return true;
}
void addRecordsToContacts(sqlite3* db)
{
	db=initDB();
	if(db !is null)
	{
		char* errmsg;
		char* first_name;
		char* last_name;
		char* email;
		char* qry;

		char input;//determine for more input
		
		do{
		string fname=askFor!(string)("Please enter first name:");
		first_name=toCstring(fname);
		printf("%s\n",first_name);
		
		string lname=askFor!(string)("Please enter last name:");
		last_name=toCstring(lname);
		printf("you entered:%s\n",last_name);

		string stremail=askFor!(string)("Please enter email:");
		email=toCstring(stremail);
		printf("You entered:%s\n",email);
		
		char* sql = sqlite3_mprintf(toCstring("INSERT INTO contacts(first_name,last_name,email)\n"
				"VALUES('%q','%q','%q');"),
			first_name,
			last_name,
			email);
			
		int ret=sqlite3_exec(db,sql,null,null,&errmsg);
	
		if(ret!=SQLITE_OK)
		{
			printf("SQL error:%s\n%s\n",errmsg,sql);
			sqlite3_free(errmsg);
			
		}
		else
		{
			printf("%s %s (%s) was inserted as ID %d\n",
				first_name,
				last_name,
				email,
				sqlite3_last_insert_rowid(db));
			
		}
		}while((input=askForChar("continue to add?(N/Y)"))=='y'|| input=='Y');
		
		closeDB(db);
	}
	
}
void menu()
{
	string menuStr="*** SQLite 3 Database System Exam ***\n"
				"\n"
				"1.	Create contact table\n"
				"2.	Add new record to contacts\n"
				"3.	Update record to contacts\n"
				"4.	Print contacts\n"
				"5.	Drop contacts\n"
				"6.	Quit\n--------------------------------------\n";

	write(menuStr);
	char input=askForChar("Your choice(1-6):");
	while(input!='6')
	{
		doMenuChoice(input);
		write(menuStr);
		input=askForChar("Your choice(1-6):");
	}

}
void updateContacts(sqlite3* db)
{
	db=initDB;
	char* errmsg;
	if(db !is null)
	{
		char* sql=sqlite3_mprintf(
			toCstring("UPDATE contacts "
			" SET first_name=lower(first_name),\n"
			"     last_name=lower(last_name)"));
		
		int ret=sqlite3_exec(db,sql,null,null,&errmsg);
		if( ret!=SQLITE_OK)
		{
			printf("Cann't update records because:\n%s\n",errmsg);
			sqlite3_free(errmsg);
				   
		}
		else
		{
			printf("%d row(s) were changed\n",sqlite3_changes(db));
		}
		closeDB(db);
	}
}



void printContacts(sqlite3* db)
{
	/* below code caused a compile error
	*** "can not convert 'int function(...) to intC function(...)"
	*** I don't know why and what the hell intC is.
	*** So I have to use another way sqlite3_prepare,step,finalize...
	*** If anybody know the reason why callback can not work,
	*** please let me know with thank!!
	
	int function(void* parg,int argc,char** argv,char** columnNames) callbacks;

	static int printTable( void* parg,int argc,char** argv,char** columnNames)
	{
		for(int i=0;i<argc;i++)
					{
						printf("%-10s\t%-8s\t%s\n",
						columnNames[i],columnNames[i+argc],argv[i]);
					}
						return 0;
		
	}
	
	callbacks=&printTable;
	printf("will print contents of contacts...\n");
	
	db=initDB;
	if(db ! is null)
	{
		char* errmsg;
		int ret=sqlite3_exec(db,toCstring("PRAGMA SHOW_DATATYPES=ON;"),null,null,null);
			ret=sqlite3_exec(db,toCstring("select * from contacts;"),
				&callbacks,//callbacks,printTable,&printTable all the same,can't compile!!
				null,&errmsg);
		if(ret!=SQLITE_OK)
		{
			printf("SQL error:%s\n",errmsg);
			sqlite3_free(db);
		}
		closeDB(db);
	}

	*/

	void error(char* msg)
	{
		printf("SQLite error:%s\n",msg);
	}
	
	char* sql=toCstring("select * from contacts;");
	uint cols;
	sqlite3_stmt* stmt;
	db=initDB;

	uint totalRecords;
	if(db)
	{
		int result=sqlite3_prepare_v2(db,
			sql,
			/*strlen(sql)+1,*/-1,
			&stmt,
			null);
		if( result!=SQLITE_OK)
		{
			sqlite3_finalize(stmt);
			error(sqlite3_errmsg(db));
			
		}
		cols=sqlite3_column_count(stmt);
		bool read()
		{
			result=sqlite3_step(stmt);

			if(result!= SQLITE_OK && result!=SQLITE_ROW && result!=SQLITE_DONE)
			{
				sqlite3_finalize(stmt);
				error(sqlite3_errmsg(db));
				return false;
			}
			if( result==SQLITE_DONE)
			{
				return false;
			}
			return true;
		}
		void printSepLine()
		{
			printf("------------------------------------"
				   "----------------------------------\n");
		}
		void printColumnHead()
		{
			printf("%-8s",sqlite3_column_name(stmt,0));
			for(int i=1;i<cols;i++)
			{
				printf("%-15s\t",sqlite3_column_name(stmt,i));
			}
			printf("\n");
			printSepLine;
		}
		
		void printColumnValue()
		{
			
			char* cValue=sqlite3_column_text(stmt,0);
			printf("%-8s",strlen(cValue)==0?toCstring("N/A"):cValue);
			for(int i=1;i<cols;i++)
			{
				cValue=sqlite3_column_text(stmt,i);
				printf("%-15s\t",strlen(cValue)==0?toCstring("N/A"):cValue);
			}
			printf("\n");
		}
		printColumnHead();
		while(read)
		{
			++totalRecords;
			printColumnValue();
		}
		printSepLine;
		printf("Total %8u records.\n\n",totalRecords);

		if(stmt)
		{
			sqlite3_finalize(stmt);
		}

		closeDB(db);
	}
}
void deleteContacts(sqlite3* db)
{
	printf("will delete table contacts...\n");
}

int doMenuChoice(char input)
{
	sqlite3* db;
	switch(input)
	{
		case '1':
		createContacts(db);
		return 1;
		break;
		case '2':
		addRecordsToContacts(db);
		return 2;
		break;
		case '3':
		updateContacts(db);
		return 3;
		break;
		case '4':
		printContacts(db);
		return 4;
		break;
		case '5':
		deleteContacts(db);
		return 5;
		break;
		case '6':
		return 6;
		break;
	}
	assert(0);
}

int main(string[] args)
{
	menu;
	pause;
	return 0;
}