/*------------------------------------------------------------------------------*
 * File Name:	oc_SQLite.h	 													*
 * Creation: 	Sophy 12/10/2009												*
 * Purpose: OriginC Header File to Access SQLite API							*
 * Copyright (c) OriginLab Corp. 2009											*
 * All Rights Reserved															*
 * 																				*
 * Modification Log:															*
 *------------------------------------------------------------------------------*/

#ifndef	_OC_SQLITE_H_
#define	_OC_SQLITE_H_

/********************************************************************************
 *									Notes										*
 *	Most of code in this files are copy from sqlite3.h, which is downloaded from*
 *		http://www.sqlite.org/sqlite-source-3_6_21.zip							*
 *	Those changed by OriginLab Corp are marked with OriginLab-Developer	CODEMARK*
********************************************************************************/

///OriginLab-Sophy 12/15/2009 LINK_FUNCTION_IN_SQLITE3_DLL
#pragma dll(sqlite3)

#define	SQLITE_API
///end LINK_FUNCTION_IN_SQLITE3_DLL

//SQLite result codes
#define SQLITE_OK           0   /* Successful result */
/* beginning-of-error-codes */
#define SQLITE_ERROR        1   /* SQL error or missing database */
#define SQLITE_INTERNAL     2   /* Internal logic error in SQLite */
#define SQLITE_PERM         3   /* Access permission denied */
#define SQLITE_ABORT        4   /* Callback routine requested an abort */
#define SQLITE_BUSY         5   /* The database file is locked */
#define SQLITE_LOCKED       6   /* A table in the database is locked */
#define SQLITE_NOMEM        7   /* A malloc() failed */
#define SQLITE_READONLY     8   /* Attempt to write a readonly database */
#define SQLITE_INTERRUPT    9   /* Operation terminated by sqlite3_interrupt()*/
#define SQLITE_IOERR       10   /* Some kind of disk I/O error occurred */
#define SQLITE_CORRUPT     11   /* The database disk image is malformed */
#define SQLITE_NOTFOUND    12   /* NOT USED. Table or record not found */
#define SQLITE_FULL        13   /* Insertion failed because database is full */
#define SQLITE_CANTOPEN    14   /* Unable to open the database file */
#define SQLITE_PROTOCOL    15   /* NOT USED. Database lock protocol error */
#define SQLITE_EMPTY       16   /* Database is empty */
#define SQLITE_SCHEMA      17   /* The database schema changed */
#define SQLITE_TOOBIG      18   /* String or BLOB exceeds size limit */
#define SQLITE_CONSTRAINT  19   /* Abort due to constraint violation */
#define SQLITE_MISMATCH    20   /* Data type mismatch */
#define SQLITE_MISUSE      21   /* Library used incorrectly */
#define SQLITE_NOLFS       22   /* Uses OS features not supported on host */
#define SQLITE_AUTH        23   /* Authorization denied */
#define SQLITE_FORMAT      24   /* Auxiliary database format error */
#define SQLITE_RANGE       25   /* 2nd parameter to sqlite3_bind out of range */
#define SQLITE_NOTADB      26   /* File opened that is not a database file */
#define SQLITE_ROW         100  /* sqlite3_step() has another row ready */
#define SQLITE_DONE        101  /* sqlite3_step() has finished executing */
/* end-of-error-codes */


//flags for file open
#define SQLITE_OPEN_READONLY         0x00000001  /* Ok for sqlite3_open_v2() */
#define SQLITE_OPEN_READWRITE        0x00000002  /* Ok for sqlite3_open_v2() */
#define SQLITE_OPEN_CREATE           0x00000004  /* Ok for sqlite3_open_v2() */
#define SQLITE_OPEN_DELETEONCLOSE    0x00000008  /* VFS only */
#define SQLITE_OPEN_EXCLUSIVE        0x00000010  /* VFS only */
#define SQLITE_OPEN_MAIN_DB          0x00000100  /* VFS only */
#define SQLITE_OPEN_TEMP_DB          0x00000200  /* VFS only */
#define SQLITE_OPEN_TRANSIENT_DB     0x00000400  /* VFS only */
#define SQLITE_OPEN_MAIN_JOURNAL     0x00000800  /* VFS only */
#define SQLITE_OPEN_TEMP_JOURNAL     0x00001000  /* VFS only */
#define SQLITE_OPEN_SUBJOURNAL       0x00002000  /* VFS only */
#define SQLITE_OPEN_MASTER_JOURNAL   0x00004000  /* VFS only */
#define SQLITE_OPEN_NOMUTEX          0x00008000  /* Ok for sqlite3_open_v2() */
#define SQLITE_OPEN_FULLMUTEX        0x00010000  /* Ok for sqlite3_open_v2() */
#define SQLITE_OPEN_SHAREDCACHE      0x00020000  /* Ok for sqlite3_open_v2() */
#define SQLITE_OPEN_PRIVATECACHE     0x00040000  /* Ok for sqlite3_open_v2() */
//end-of-flags for file open

//foundamental datatypes
#define SQLITE_INTEGER  1
#define SQLITE_FLOAT    2
#define SQLITE_BLOB     4
#define SQLITE_NULL     5
#define SQLITE_TEXT     3
#define SQLITE3_TEXT    3
//SQLite objects
///OriginLab-Sophy 12/10/2009 SQLITE_TYPE_TO_ORIGINC_INVOKABLE_TYPE
//I am not sure why need to typedef as void type, or else will got external runtime error. Maybe it's OC's problem.
//struct sqlite3;
//struct sqlite3_stmt;
//struct sqlite3_value;
typedef __int64 sqlite3_int64;
typedef void sqlite3;
typedef void sqlite3_stmt;
typedef void sqlite3_value;
typedef void sqlite3_blob;
///end SQLITE_TYPE_TO_ORIGINC_INVOKABLE_TYPE

//open database
SQLITE_API int sqlite3_open(const char* filename, sqlite3 **ppDb);
SQLITE_API int sqlite3_open_v2(const char* filename, sqlite3 **ppDb, int flags, const char* zVfs);

//close database
SQLITE_API int sqlite3_close(sqlite3 * pDb);

//execute sql statement
typedef	int(*SQliteCallBack)(void* p, int k, char** ppbuffer, char** ppbuffer2);
SQLITE_API int sqlite3_exec(sqlite3* pDb, const char* sql, SQliteCallBack callback, void* pv, char** errMsg);

//prepare SQL statement
SQLITE_API int sqlite3_prepare(sqlite3* pDb, const char* lpcszSQL, int nByte, sqlite3_stmt **ppStmt, const char** lpcszTail);

//evaluate the sql statement
SQLITE_API int sqlite3_step(sqlite3_stmt* pStmt);

//number of values in the current row of the result set
SQLITE_API int sqlite3_data_count(sqlite3_stmt* pStmt);

//number of columns in the result set
SQLITE_API int sqlite3_column_count(sqlite3_stmt* pStmt);


//number of rows in the result set
SQLITE_API int sqlite3_get_table(sqlite3* pDB, const char* sql, char*** pppResult, int* pnRows, int* pnCols, char** ppErrMsg);

//release table memory
SQLITE_API int sqlite3_free_table(char** ppResult);

//reset the statement
SQLITE_API int sqlite3_reset(sqlite3_stmt* pStmt);

//destroy a prepared stmtement object
SQLITE_API int sqlite3_finalize(sqlite3_stmt* pStmt);

//get next statement
SQLITE_API sqlite3_stmt* sqlite3_next_stmt(sqlite3* pDb, sqlite3_stmt* pStmt);

//get original sql string
SQLITE_API const char* sqlite3_sql(sqlite3_stmt* pStmt);

//get result value from a query
SQLITE_API const void* sqlite3_column_blob(sqlite3_stmt* pStmt, int iCol);
SQLITE_API int sqlite3_column_bytes(sqlite3_stmt* pStmt, int iCol);
SQLITE_API int sqlite3_column_bytes16(sqlite3_stmt* pStmt, int iCol);
SQLITE_API double sqlite3_column_double(sqlite3_stmt* pStmt, int iCol);
SQLITE_API int sqlite3_column_int(sqlite3_stmt* pStmt, int iCol);
SQLITE_API sqlite3_int64 sqlite3_column_int64(sqlite3_stmt* pStmt, int iCol);
SQLITE_API const unsigned char* sqlite3_column_text(sqlite3_stmt* pStmt, int iCol);
SQLITE_API const void* sqlite3_column_text16(sqlite3_stmt* pStmt, int iCol);
SQLITE_API int sqlite3_column_type(sqlite3_stmt* pStmt, int iCol);
SQLITE_API sqlite3_value* sqlite3_column_value(sqlite3_stmt* pStmt, int iCol);

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////// If Ony Want To Read Data From Database, NO Need To Care The Following Code ////////////////////////////////////////////////////////////////////
///////////////////////////////////////////// The Following Function Are For Advance User Who Want To Modify Data In The Database  //////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

//bind column
typedef void(*sqlite3_destructor_type)(void* pV);
#define	SQLITE_STATIC		((sqlite3_destructor_type)0)
#define	SQLITE_TRANSIENT	((sqlite3_destructor_type)-1)

SQLITE_API int sqlite3_bind_blob(sqlite3_stmt* pStmt, int iPara, const void* pVal, int nSize, sqlite3_destructor_type callback);
SQLITE_API int sqlite3_bind_double(sqlite3_stmt* pStmt, int iPara, double dVal);
SQLITE_API int sqlite3_bind_int(sqlite3_stmt* pStmt, int iPara, int nVal);
SQLITE_API int sqlite3_bind_int64(sqlite3_stmt* pStmt, int iPara, sqlite3_int64 n64Val);
SQLITE_API int sqlite3_bind_null(sqlite3_stmt* pStmt, int iPara);
SQLITE_API int sqlite3_bind_text(sqlite3_stmt* pStmt, int iPara, const char* pVal, int nSize, sqlite3_destructor_type callback);
SQLITE_API int sqlite3_bind_text16(sqlite3_stmt* pStmt, int iPara, const void* pVal, int nSize, sqlite3_destructor_type callback);
SQLITE_API int sqlite3_bind_value(sqlite3_stmt* pStmt, int iPara, const sqlite3_value*);
SQLITE_API int sqlite3_bind_zeroblob(sqlite3_stmt* pStmt, int iPara, int nSize);

SQLITE_API int sqlite3_bind_parameter_count(sqlite3_stmt* pStmt);
SQLITE_API int sqlite3_bind_parameter_index(sqlite3_stmt* pStmt, const char* pParamName);
SQLITE_API char* sqlite3_bind_parameter_name(sqlite3_stmt* pStmt, int nIndex);

//get information of a result column
SQLITE_API char* sqlite3_column_database_name(sqlite3_stmt* pStmt, int iCol);
SQLITE_API char* sqlite3_column_table_name(sqlite3_stmt* pStmt, int iCol);
SQLITE_API char* sqlite3_column_origin_name(sqlite3_stmt* pStmt, int iCol);
SQLITE_API const char* sqlite3_column_name(sqlite3_stmt* pStmt, int iCol);
SQLITE_API void* sqlite3_column_name16(sqlite3_stmt* pStmt, int iCol);
SQLITE_API char* sqlite3_column_decltype(sqlite3_stmt* pStmt, int iCol);


//blob relative
SQLITE_API int sqlite3_blob_open(sqlite3_blob* pDb, const char* lpcszDb, const char* lpcszTable, const char* lpcszColumn, sqlite3_int64 nRow, int flags, sqlite3_blob **ppBlob);
SQLITE_API int sqlite3_blob_read(sqlite3_blob* pBlob, void* pVal, int nSize, int iOffset);
SQLITE_API int sqlite3_blob_write(sqlite3_blob* pBlob, const void* pVal, int nSize, int iOffset);
SQLITE_API int sqlite3_blob_close(sqlite3_blob* pBlob);

//diagnose utils
//Status Parameters
#define SQLITE_STATUS_MEMORY_USED          0
#define SQLITE_STATUS_PAGECACHE_USED       1
#define SQLITE_STATUS_PAGECACHE_OVERFLOW   2
#define SQLITE_STATUS_SCRATCH_USED         3
#define SQLITE_STATUS_SCRATCH_OVERFLOW     4
#define SQLITE_STATUS_MALLOC_SIZE          5
#define SQLITE_STATUS_PARSER_STACK         6
#define SQLITE_STATUS_PAGECACHE_SIZE       7
#define SQLITE_STATUS_SCRATCH_SIZE         8


SQLITE_API int sqlite3_status(int nStatus, int* pnCurrent, int* pHighWater, int resetFlag);
SQLITE_API sqlite3_int64 sqlite3_memory_used();
SQLITE_API sqlite3_int64 sqlite3_memory_highwater(int resetFlag);
SQLITE_API int sqlite3_release_memory(int nBytes);

SQLITE_API int sqlite3_initalize();
SQLITE_API int sqlite3_shutdown();
SQLITE_API int sqlite3_os_init();
SQLITE_API int sqlite3_os_end();

//memory allocation
SQLITE_API void* sqlite3_malloc(int nSize);
SQLITE_API void* sqlite3_realloc(void* pBuffer, int nNewSize);
SQLITE_API void sqlite3_free(void* pBuffer);

//lib version
SQLITE_API const char* sqlite3_libversion();
SQLITE_API const char* sqlite3_sourceid();
SQLITE_API int sqlite3_libversion_number();

//error code & message
SQLITE_API int sqlite3_errcode(sqlite3* pDb);
SQLITE_API int sqlite3_extended_errcode(sqlite3* pDb);
SQLITE_API const char* sqlite3_errmsg(sqlite3* pDb);
SQLITE_API const char* sqlite3_errmsg16(sqlite3* pDb);

//create / redefine sql functions
typedef void sqlite3_context;
typedef void(*XFunction)(sqlite3_context* ctx, int n, sqlite3_value** ppVal);
typedef void(*XStep)(sqlite3_context* ctx, int n, sqlite3_value** ppVal);
typedef void(*XFinal)(sqlite3_context* ctx);

SQLITE_API int sqlite3_create_function(sqlite3* pDb, const char* FunctionName, int nArgs, int nTextRep, void* pApp, XFunction xf, XStep xs, XFinal final);
#endif	//_OC_SQLITE_H_

/*Usage:
//Notes: Put this "oc_SQLite.h" at Origin_EXE_Folder\OriginC\System
//download sqlite3.dll from http://www.sqlite.org/download.html
//extract the zipped file and put sqlite3.dll at Origin_EXE_Folder.
//Notes: SQLite allow user to get field value as any kind of type, so can get integer as text.
#include <oc_SQLite.h>
///SQLite ODBC Reading
#define	STR_DATABASE_FILE	"E:\\DataSet1.1.db"
#define	STR_QUERY_STRING	"select * from PINGVELOCITIES limit 80"
#define	WKS_ROW_INCRE_STEP	10
#define	MAX_BUFFER_LEN		256
void My_SQLite_ODBC()
{
	int nRet;
	LPCSTR lpcszSQLiteFile = STR_DATABASE_FILE; //the sqlite database file.
	sqlite3* pDB = NULL; //pointer to the database
	nRet = sqlite3_open(lpcszSQLiteFile, &pDB);
	if ( nRet != SQLITE_OK )
	{
		printf("%s : %s\n", "Fail to open sqlite database file", lpcszSQLiteFile);
		return;
	}
	
	char* pEnd;
	LPCSTR lpcszSQL = STR_QUERY_STRING;
	sqlite3_stmt* pStmt = NULL; //pointer to the sql statement.
	
	nRet = sqlite3_prepare(pDB, lpcszSQL, strlen(lpcszSQL), &pStmt, &pEnd);
	if ( nRet != SQLITE_OK )
	{
		printf("%s : %s\n", "Fail to execute the following statement : ", lpcszSQL);
		sqlite3_close(pDB);
		return;
	}
	
	int iRow = 0, iCol = 0, nCols = 0, nFieldType = 0;
	nCols = sqlite3_column_count(pStmt);
	
	//get data into active worksheet
	Worksheet wks = Project.ActiveLayer();
	_prepare_wks(wks, WKS_ROW_INCRE_STEP, nCols);
	
	while( (nRet = sqlite3_step(pStmt)) == SQLITE_ROW ) //has more rows
	{
		if ( iRow >= wks.GetNumRows() )
			_prepare_wks(wks, iRow + WKS_ROW_INCRE_STEP);
		
		for ( iCol = 0; iCol < nCols; iCol++ )
		{
			nFieldType = sqlite3_column_type(pStmt, iCol);
			switch(nFieldType)
			{
			case SQLITE_INTEGER:
				sqlite3_int64 nVal = sqlite3_column_int64(pStmt, iCol);
				char buffer[MAX_BUFFER_LEN];
				sprintf(buffer, "%I64d", nVal);
				wks.SetCell(iRow, iCol, buffer);
				break;
				
			case SQLITE_FLOAT:
				double dVal = sqlite3_column_double(pStmt, iCol);
				wks.SetCell(iRow, iCol, dVal);
				break;
				
			case SQLITE_BLOB:
				int nSize = sqlite3_column_bytes(pStmt, iCol);
				byte* bVals = (byte*)sqlite3_column_blob(pStmt, iCol);
				vector<byte> vb(nSize);
				memcpy(vb, bVals, sizeof(byte) * nSize);
				string strVal;
				strVal.SetBytes(vb); //==> string will be terminated by '\0', if there exists '\0' in vb.
				wks.SetCell(iRow, iCol, strVal);
				break;
				
			case SQLITE_NULL:
				wks.SetCell(iRow, iCol, get_missing_value());
				break;
				
			case SQLITE_TEXT:
				int nSize = sqlite3_column_bytes(pStmt, iCol);
				const char* pData = (const char*)sqlite3_column_text(pStmt, iCol);
				wks.SetCell(iRow, iCol, pData);
				break;
				
			default:
				ASSERT(FALSE); //unknown type
				break;
			}
		}
		iRow++;
	}
	_prepare_wks(wks, iRow); //reset the number of rows according to query result set.
	sqlite3_finalize(pStmt);
	sqlite3_close(pDB);
}

static void _prepare_wks(Worksheet& wks, int nRows = -1, int nCols = -1)
{
	wks.SetSize(nRows, nCols);
}
*/

