1 /*

     2 * Copyright (c) 2008-2009 Nokia Corporation and/or its subsidiary(-ies).

     3 * All rights reserved.

     4 * This component and the accompanying materials are made available

     5 * under the terms of the License "Eclipse Public License v1.0"

     6 * which accompanies this distribution, and is available

     7 * at the URL "http://www.eclipse.org/legal/epl-v10.html".

     8 *

     9 * Initial Contributors:

    10 * Nokia Corporation - initial contribution.

    11 *

    12 * Contributors:

    13 *

    14 * Description: 

    15 * scrdatabase.cpp

    16 * Implements the database handling which is mostly a replica of the SCR Data Layer API 

    17 * which performs all interaction with the underlying database.

    18 *

    19 */

    20 

    21 

    22 /**

    23  @file

    24  @internalComponent

    25  @released

    26 */

    27 

    28 #include "dbprocessor.h"

    29 #include "exception.h"

    30 #include "logs.h"

    31 #include "util.h"

    32 #include "symbiantypes.h"

    33 

    34 #include <string>

    35 #include <cassert>

    36 

    37 TDbLibrary* iLibraryHandler = NULL;

    38 

    39 TDbLibrary::TDbLibrary(const std::string& aDllPath)

    40 	{

    41 	LoadSqlLibrary(aDllPath);

    42 	LoadFunctions();

    43 	}

    44 

    45 /** 

    46 * Failing to unload the library is not really a critical failure, when a dll is unloaded the 

    47 * current process sends a notification to the dll to detach itself from the process, which 

    48 * means the dll can process memory cleanup operations before it unloads. The only case where 

    49 * freelibrary might throw an error is if the library was not loaded in the first place, an 

    50 * error which would be caught beforehand. Hence if the library cannot be unloaded corresponding 

    51 * error message is logged.

    52 */

    53 TDbLibrary::~TDbLibrary()

    54 	{

    55 	int retCode = FreeLibrary(sqLiteHndl);

    56 	if(retCode == 0)

    57 		{

    58 		LPCVOID lpMsgBuf;

    59 		

    60 		DWORD err = GetLastError();

    61 		FormatMessage	(	FORMAT_MESSAGE_ALLOCATE_BUFFER | FORMAT_MESSAGE_FROM_SYSTEM |

    62 							FORMAT_MESSAGE_IGNORE_INSERTS, NULL, err,

    63 							MAKELANGID(LANG_NEUTRAL, SUBLANG_DEFAULT), (LPTSTR) &lpMsgBuf,

    64 							0, NULL 

    65 						);

    66 		std::wstring wErrMsg((wchar_t*)lpMsgBuf);

    67 		std::string errMsg = Util::wstring2string(wErrMsg);

    68 		//LOGERROR(errMsg);

    69 		

    70 		}

    71 	}

    72 

    73 void TDbLibrary::LoadSqlLibrary(const std::string& aDllPath)

    74 	{

    75 	sqLiteHndl = LoadLibraryA(aDllPath.c_str());

    76  

    77 	// Check to see if the library was loaded successfully 

    78 

    79 	if (sqLiteHndl != 0)

    80 		{

    81 		//LOGINFO("Library successfully loaded!");

    82 		}

    83 	else

    84 		{

    85 		std::string errMsg("Failed to load SQLite library - ");

    86 		errMsg.append(aDllPath);

    87 		//LOGERROR(errMsg);

    88 		throw CException(errMsg,ExceptionCodes::ELibraryLoadError);

    89 		}

    90 	}

    91 

    92 void TDbLibrary::LoadFunctions()

    93 	{

    94 	sqlite3_open = (FnPtr_sqlite3_open)GetProcAddress(sqLiteHndl,"sqlite3_open");

    95 	VerifyLoadedFunction(sqlite3_open);

    96 	

    97 	sqlite3_prepare_v2	= (FnPtr_sqlite3_prepare_v2) GetProcAddress(sqLiteHndl,"sqlite3_prepare_v2");	

    98 	VerifyLoadedFunction(sqlite3_prepare_v2);

    99 	

   100 	sqlite3_step		= (FnPtr_sqlite3_step) GetProcAddress(sqLiteHndl,"sqlite3_step");

   101 	VerifyLoadedFunction(sqlite3_step);

   102 	

   103 	sqlite3_finalize	= (FnPtr_sqlite3_finalize) GetProcAddress(sqLiteHndl,"sqlite3_finalize");

   104 	VerifyLoadedFunction(sqlite3_finalize);

   105 	

   106 	sqlite3_bind_text	= (FnPtr_sqlite3_bind_text) GetProcAddress(sqLiteHndl,"sqlite3_bind_text");

   107 	VerifyLoadedFunction(sqlite3_bind_text);

   108 	

   109 	sqlite3_bind_text16 = (FnPtr_sqlite3_bind_text16) GetProcAddress(sqLiteHndl,"sqlite3_bind_text16");

   110 	VerifyLoadedFunction(sqlite3_bind_text16);

   111 	

   112 	sqlite3_bind_int	= (FnPtr_sqlite3_bind_int) GetProcAddress(sqLiteHndl,"sqlite3_bind_int");

   113 	VerifyLoadedFunction(sqlite3_bind_int);

   114 	

   115 	sqlite3_reset		= (FnPtr_sqlite3_reset) GetProcAddress(sqLiteHndl,"sqlite3_reset");

   116 	VerifyLoadedFunction(sqlite3_reset);

   117 	

   118 	sqlite3_clear_bindings		= (FnPtr_sqlite3_clear_bindings) GetProcAddress(sqLiteHndl,"sqlite3_clear_bindings");

   119 	VerifyLoadedFunction(sqlite3_clear_bindings);

   120 	

   121 	sqlite3_last_insert_rowid	= (FnPtr_sqlite3_last_insert_rowid) GetProcAddress(sqLiteHndl,"sqlite3_last_insert_rowid");

   122 	VerifyLoadedFunction(sqlite3_last_insert_rowid);

   123 	

   124 	sqlite3_extended_result_codes = (FnPtr_sqlite3_extended_result_codes) GetProcAddress(sqLiteHndl, "sqlite3_extended_result_codes" );

   125 	VerifyLoadedFunction(sqlite3_extended_result_codes);

   126 	

   127 	sqlite3_close			= (FnPtr_sqlite3_close)GetProcAddress(sqLiteHndl,"sqlite3_close");

   128 	VerifyLoadedFunction(sqlite3_close);

   129 	

   130 	sqlite3_errmsg			= (FnPtr_sqlite3_errmsg)GetProcAddress(sqLiteHndl,"sqlite3_errmsg");

   131 	VerifyLoadedFunction(sqlite3_errmsg);

   132 	

   133 	sqlite3_errcode			= (FnPtr_sqlite3_errcode)GetProcAddress(sqLiteHndl,"sqlite3_errcode");

   134 	VerifyLoadedFunction(sqlite3_errcode);

   135 	

   136 	sqlite3_bind_int64		= (FnPtr_sqlite3_bind_int64)GetProcAddress(sqLiteHndl,"sqlite3_bind_int64");

   137 	VerifyLoadedFunction(sqlite3_bind_int64);

   138 	

   139 	sqlite3_column_text16	= (FnPtr_sqlite3_column_text16)GetProcAddress(sqLiteHndl,"sqlite3_column_text16");

   140 	VerifyLoadedFunction(sqlite3_column_text16);

   141 	

   142 	sqlite3_column_bytes16	= (FnPtr_sqlite3_column_bytes16)GetProcAddress(sqLiteHndl,"sqlite3_column_bytes16");

   143 	VerifyLoadedFunction(sqlite3_column_bytes16);

   144 	

   145 	sqlite3_column_int64	= (FnPtr_sqlite3_column_int64)GetProcAddress(sqLiteHndl,"sqlite3_column_int64");

   146 	VerifyLoadedFunction(sqlite3_column_int64);

   147 	

   148 	sqlite3_column_int		= (FnPtr_sqlite3_column_int)GetProcAddress(sqLiteHndl,"sqlite3_column_int");

   149 	VerifyLoadedFunction(sqlite3_column_int);

   150 	

   151 	sqlite3_column_count	= (FnPtr_sqlite3_column_count)GetProcAddress(sqLiteHndl,"sqlite3_column_count");

   152 	VerifyLoadedFunction(sqlite3_column_count);

   153 	

   154 	sqlite3_column_type		= (FnPtr_sqlite3_column_type)GetProcAddress(sqLiteHndl,"sqlite3_column_type");

   155 	VerifyLoadedFunction(sqlite3_column_type);

   156 	

   157 	sqlite3_prepare16_v2	= (FnPtr_sqlite3_prepare16_v2)GetProcAddress(sqLiteHndl,"sqlite3_prepare16_v2");

   158 	VerifyLoadedFunction(sqlite3_prepare16_v2);

   159 

   160 	sqlite3_bind_blob = (FnPtr_sqlite3_bind_blob)GetProcAddress(sqLiteHndl,"sqlite3_bind_blob");

   161 	VerifyLoadedFunction(sqlite3_bind_blob);

   162 

   163 	}

   164 

   165 void TDbLibrary::VerifyLoadedFunction(void* aFnPtr)

   166 	{

   167 	if(aFnPtr != NULL)

   168 		return;

   169 

   170 	LPCVOID lpMsgBuf;

   171 		

   172 	DWORD err = GetLastError();

   173 	FormatMessage	(	FORMAT_MESSAGE_ALLOCATE_BUFFER | FORMAT_MESSAGE_FROM_SYSTEM |

   174 						FORMAT_MESSAGE_IGNORE_INSERTS, NULL, err,

   175 						MAKELANGID(LANG_NEUTRAL, SUBLANG_DEFAULT), (LPTSTR) &lpMsgBuf,

   176 						0, NULL 

   177 					);

   178 	std::wstring wErrMsg((wchar_t*)lpMsgBuf);

   179 	std::string errMsg = Util::wstring2string(wErrMsg);

   180 	//LOGERROR(errMsg);

   181 	throw CException(errMsg,ExceptionCodes::ELibraryLoadError);

   182 	}

   183 

   184 

   185 //

   186 // CDbProcessor

   187 // 

   188 

   189 // Constructor

   190 CDbProcessor::CDbProcessor(const std::string& aDllPath, const std::string& aDbName )

   191 	:iLibraryHandler(aDllPath)

   192 	{

   193 	

   194 	// Then open the SCR database

   195 	TInt err = iLibraryHandler.sqlite3_open(aDbName.c_str(), &iDbHandle);

   196 	

   197 	// Check the returned error code

   198 	CheckSqlErrCode(err);

   199 	// If comes here, means the database file has been opened successfully

   200 	// Now, enable the extended result codes feature of SQLite. In SQLite, this feature is 

   201 	// disabled by default for historical compatibility.

   202 	err = iLibraryHandler.sqlite3_extended_result_codes(iDbHandle, 0);

   203 	// Check the returned error code

   204 	CheckSqlErrCode(err,"Failed to activate the extended error mechanism.");

   205 	}

   206 

   207 // Destructor

   208 CDbProcessor::~CDbProcessor()

   209 	{

   210 	//LOGINFO("Closing the SCR database connection.");

   211 

   212 	// Close the db handle

   213 	TInt err = iLibraryHandler.sqlite3_close(iDbHandle);

   214 	

   215 	if(err != SQLITE_OK)

   216 		{

   217 		std::string errMsg("Failed to close the database handle.");

   218 		//LOGERROR(errMsg);

   219 		}

   220 	}

   221 

   222 

   223 CStatement* CDbProcessor::PrepareStatement(const std::string& aStatementStr)

   224 	{

   225 	// For statements which start with the SELECT key word, this function creates an sql statement  

   226 	// object and returns it.

   227 	sqlite3_stmt* stmtHandle = NULL; // Temporary statement handle

   228 	const char* stmtTail = NULL;	 // Pointer to unused portion of Sql statement.                         

   229 	TInt err = iLibraryHandler.sqlite3_prepare_v2(iDbHandle, aStatementStr.c_str(), aStatementStr.size(), &stmtHandle, &stmtTail);

   230 	

   231 	// Check the returned error code

   232 	CheckSqlErrCode(err);

   233 	// Since we expect single statement, stmtTail pointer should be NULL or point to zero.

   234 	if(stmtTail && stmtTail[0] != 0)

   235 		{

   236 		err = iLibraryHandler.sqlite3_finalize(stmtHandle);

   237 		std::string errMessage = "There is a problem with the provided SQL statement. It may contain more \

   238 								than one statement. Or It may not be terminated with semicolon. \

   239 								Or It may contain a space or invalid char after semicolon.";

   240 		//LOGERROR(errMessage);

   241 		CheckSqlErrCode(err,errMessage);

   242 		}

   243 	// stmtHandle can be NULL for statements like this: ";"

   244 	if(!stmtHandle)

   245 		{

   246 		throw CException(ExceptionCodes::ESqlArgumentError);

   247 		}

   248 

   249 	std::string str = "Query:";

   250 	//LOGINFO(str+aStatementStr);

   251 	// The statement object which carries handle to the result set of the sql statement

   252 	CStatement* stmtObj = new CStatement(stmtHandle,iLibraryHandler,*iDbHandle); 

   253 	

   254 	return stmtObj;	

   255 	}

   256 

   257 

   258 TInt64 CDbProcessor::LastInsertedId()

   259 	{

   260 	TInt retVal = (TInt)iLibraryHandler.sqlite3_last_insert_rowid(iDbHandle); 

   261 	// it is now expected that row ids in scr will require 64-bit storage, so cast the return value to TInt

   262 	if(retVal <= 0)

   263 		{

   264 		throw CException(ExceptionCodes::ESqlNotFoundError);

   265 		}

   266 	return retVal;

   267 	}

   268 

   269 void CDbProcessor::CheckSqlErrCode(TInt aErrorCode, std::string& aErrorMessage ) 

   270 	{

   271 	if(aErrorCode != SQLITE_OK)

   272 		{

   273 		TInt errorCode = iLibraryHandler.sqlite3_errcode(iDbHandle);

   274 		//LOGERROR(aErrorMessage);

   275 		throw CException(aErrorMessage,aErrorCode);

   276 		}

   277 	}

   278 

   279 void CDbProcessor::CheckSqlErrCode(TInt aErrorCode) 

   280 	{

   281 

   282 	if(aErrorCode != SQLITE_OK)

   283 		{

   284 		TInt errorCode = iLibraryHandler.sqlite3_errcode(iDbHandle);

   285 		const char* errMsg = iLibraryHandler.sqlite3_errmsg(iDbHandle);

   286 		CheckSqlErrCode(errorCode,errMsg);

   287 		}

   288 	}

   289 

   290 void CDbProcessor::CheckSqlErrCode(TInt aErrorCode, const char* aErrorMessage ) 

   291 	{

   292 	std::string errMsg(aErrorMessage);

   293 	CheckSqlErrCode(aErrorCode,errMsg);

   294 	}

   295 

   296 //

   297 // CStatement

   298 //

   299 

   300 // Constructor

   301 CStatement::CStatement(sqlite3_stmt* aStmtHandle, const TDbLibrary& aLibraryHandler , sqlite3& aDbHandle)

   302 	:iStmtHandle(aStmtHandle),

   303 	iLibraryHandler(aLibraryHandler),

   304 	iDbHandle(aDbHandle)

   305 	{

   306 	// Make sure that the statement handle is never NULL.

   307 	assert(iStmtHandle != NULL);

   308 	}

   309 

   310 // Destructor

   311 CStatement::~CStatement()

   312 	{

   313 	TInt err = iLibraryHandler.sqlite3_finalize(iStmtHandle);

   314 	if(SQLITE_OK != err)

   315 		{

   316 		std::string errMsg = "Failed to finalize the statement object.";

   317 		//LOGERROR(errMsg);

   318 		throw CException(errMsg,err);

   319 		}

   320 	}

   321 

   322 void CStatement::ExecuteStatement()

   323 	{

   324 	// If the statement doesn't return any result table, it should normally be executed

   325 	// with sqlite3_exec. However, sqlite does not have a 16-bit version of sqlite3_exec.  

   326 	// Therefore, the execution is made with PrepareStatementLC and ProcessNextRowL functions.

   327 	//LOGINFO("Executing the prepared SCR SQL statement.");

   328 	

   329 	// Now, execute and check if the function has completed successfully by calling ProcessNextRowL.

   330 	// If the function has failed, ProcessNextRowL will leave with one of the system wide error codes.

   331 	while(ProcessNextRow())

   332 		{}

   333 	}

   334 

   335 

   336 bool CStatement::ProcessNextRow()

   337 	{

   338 	TInt err = iLibraryHandler.sqlite3_step(iStmtHandle);

   339 	

   340 	switch(err)

   341 		{

   342 	case SQLITE_ROW: // A new row of data is ready for processing.

   343 		return true;

   344 	

   345 	case SQLITE_DONE: // The statement has finished executing successfully.

   346 		return false;

   347 	default:		 

   348 		CheckSqlErrCode(err);

   349 		}// End of switch

   350 	return false;

   351 	}

   352 

   353 void CStatement::BindInt(TInt aParameterIndex, TInt aParameterValue)

   354 	{

   355 	TInt err = iLibraryHandler.sqlite3_bind_int(iStmtHandle, aParameterIndex, aParameterValue);

   356 	CheckSqlErrCode(err);

   357 	}

   358 

   359 void CStatement::BindInt64(TInt aParameterIndex, TInt64 aParameterValue)

   360 	{

   361 	TInt err = iLibraryHandler.sqlite3_bind_int64(iStmtHandle, aParameterIndex, aParameterValue);

   362 	CheckSqlErrCode(err);

   363 	}

   364 

   365 void CStatement::BindStr(TInt aParameterIndex, const std::wstring &aParameterStr)

   366 	{

   367 	TInt err = iLibraryHandler.sqlite3_bind_text16(iStmtHandle, aParameterIndex, aParameterStr.c_str(), aParameterStr.size()*2, SQLITE_TRANSIENT);

   368 	// The fifth argument has the value SQLITE_TRANSIENT, it means that SQLite makes its own private copy of the data immediately

   369 	CheckSqlErrCode(err);

   370 	}

   371 

   372 void CStatement::BindBinary(TInt aParameterIndex, const std::string &aParameterStr)

   373 	{

   374 	TInt err = iLibraryHandler.sqlite3_bind_blob(iStmtHandle, aParameterIndex, aParameterStr.c_str(), aParameterStr.size(), SQLITE_TRANSIENT);

   375 	// The fifth argument has the value SQLITE_TRANSIENT, it means that SQLite makes its own private copy of the data immediately

   376 	CheckSqlErrCode(err);

   377 	}

   378 

   379 void CStatement::Reset()

   380 	{

   381 	TInt err = iLibraryHandler.sqlite3_reset(iStmtHandle);

   382 	CheckSqlErrCode(err);

   383 	err = iLibraryHandler.sqlite3_clear_bindings(iStmtHandle);

   384 	CheckSqlErrCode(err);

   385 	}

   386 

   387 void CStatement::CheckSqlErrCode(TInt aErrorCode) 

   388 	{

   389 	

   390 	if(aErrorCode != SQLITE_OK)

   391 		{

   392 		TInt errorCode = iLibraryHandler.sqlite3_errcode(&iDbHandle);

   393 		const char* errMsg = iLibraryHandler.sqlite3_errmsg(&iDbHandle);

   394 		std::string errStr(errMsg);

   395 		//LOGERROR(errStr);

   396 		throw CException(errStr,errorCode);

   397 		}

   398 	}

   399 

   400 int CStatement::IntColumn(int aColumnId ) const

   401 	{

   402 	return iLibraryHandler.sqlite3_column_int(iStmtHandle, aColumnId);

   403 	}

   404 

   405 std::wstring CStatement::StrColumn(int aColumnId ) const

   406 	{

   407 	std::wstring columnValue(static_cast<const wchar_t*>(iLibraryHandler.sqlite3_column_text16(iStmtHandle, aColumnId)));

   408 	return columnValue;

   409 	}

   410 

   411 TInt64 CStatement::Int64Column(int aColumnId ) const

   412 	{

   413 	return iLibraryHandler.sqlite3_column_int64(iStmtHandle, aColumnId);

   414 	}

   415 

   416 

   417