sl@0: // Copyright (c) 2005-2009 Nokia Corporation and/or its subsidiary(-ies).
sl@0: // All rights reserved.
sl@0: // This component and the accompanying materials are made available
sl@0: // under the terms of "Eclipse Public License v1.0"
sl@0: // which accompanies this distribution, and is available
sl@0: // at the URL "http://www.eclipse.org/legal/epl-v10.html".
sl@0: //
sl@0: // Initial Contributors:
sl@0: // Nokia Corporation - initial contribution.
sl@0: //
sl@0: // Contributors:
sl@0: //
sl@0: // Description:
sl@0: //
sl@0: 
sl@0: #include <e32std.h>
sl@0: #include <f32file.h>
sl@0: #include <e32test.h>
sl@0: #include <stdio.h>
sl@0: #include <stdlib.h>
sl@0: #include <string.h> 
sl@0: #include <hal.h>
sl@0: #include <utf.h>				//CnvUtfConverter
sl@0: #include "sqlite3.h"
sl@0: #include "SqliteSymbian.h"
sl@0: 
sl@0: #define UNUSED_VAR(a) a = (a)
sl@0: 
sl@0: //Forward declaration
sl@0: struct Mem;
sl@0: 
sl@0: static RTest 			TheTest(_L ("t_sqlite.exe"));
sl@0: static CTrapCleanup*	TheTrapCleanup = NULL;
sl@0: static RFs 				TheFs;
sl@0: 
sl@0: //File-local buffer used for converted to UTF16 (from UTF8) strings or
sl@0: //for a temporary file name buffer.
sl@0: static TUint16 TheFileName[KMaxFileName + 1];
sl@0: 
sl@0: _LIT(KTestDir, "c:\\test\\");
sl@0: 
sl@0: const char KDbName1[] = "c:\\test\\t_sqlite1.db";
sl@0: const char KDbName2[] = "c:\\test\\t_sqlite2.db";
sl@0: _LIT(KContactsFile, "c:\\test\\Contacts.DB");
sl@0: 
sl@0: sqlite3 *TheDb1 = NULL, *TheDb2 = NULL;
sl@0: 
sl@0: static TBuf<4096> TheBuf1;
sl@0: static TBuf<4096> TheBuf2;
sl@0: static TBuf8<512> TheBuf3;
sl@0: 
sl@0: static TInt TheCmpFCallCnt = 0;
sl@0: static TInt TheCmpCCallCnt = 0;
sl@0: 
sl@0: _LIT(KSimpleContactsSqlFile, "z:\\test\\add_simple_contacts.sql");
sl@0: 
sl@0: //In order to be able to compile the test, the following variables are defined (used inside the OS porting layer, when _SQLPROFILER macro is defined)
sl@0: #ifdef _SQLPROFILER
sl@0: TInt TheSqlSrvProfilerFileRead = 0;
sl@0: TInt TheSqlSrvProfilerFileWrite = 0;
sl@0: TInt TheSqlSrvProfilerFileSync = 0;
sl@0: TInt TheSqlSrvProfilerFileSetSize = 0;
sl@0: #endif
sl@0: 
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: 
sl@0: //Creates TPtrC object which points to the unicode presentation of aData.
sl@0: //aData argument is expected to point to UTF8 encoded, zero terminated string.
sl@0: //The function returns a TPtrC, non-zero terminated object pointing to the unicode presentation of aData.
sl@0: //If the length of the returned TPtrC object is 0 - it means that unicode conversion of aData failed, 
sl@0: //because the aData is too long or is NULL. 
sl@0: //Max allowed aData length is KMaxFileName (excluding terminating 0 character).
sl@0: static TPtrC ConvertToUtf16(const char *aData)
sl@0: 	{
sl@0: 	TPtrC ptr(0, 0);
sl@0: 	if(aData)
sl@0: 		{
sl@0: 		TInt len = mbstowcs(reinterpret_cast <wchar_t*> (TheFileName), aData, KMaxFileName + 1);
sl@0: 		//Check converted string length. If it is longer than KMaxFileName characters, then aData is too long.
sl@0: 		if(len > 0 && len <= KMaxFileName)
sl@0: 			{
sl@0: 			ptr.Set(TheFileName, len);
sl@0: 			}
sl@0: 		}
sl@0: 	return ptr;
sl@0: 	}
sl@0: 
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: 
sl@0: static void DeleteTestFiles()
sl@0: 	{
sl@0:    	sqlite3_close(TheDb2);
sl@0:    	sqlite3_close(TheDb1);
sl@0:    	
sl@0: 	TheFs.Delete(ConvertToUtf16(KDbName2));
sl@0: 	TheFs.Delete(ConvertToUtf16(KDbName1));
sl@0: 	TheFs.Delete(KContactsFile);
sl@0: 	}
sl@0: 
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: //Test macros and functions
sl@0: static void Check(TInt aValue, TInt aLine)
sl@0: 	{
sl@0: 	if(!aValue)
sl@0: 		{
sl@0: 		DeleteTestFiles();
sl@0: 		TheTest(EFalse, aLine);
sl@0: 		}
sl@0: 	}
sl@0: static void Check(TInt aValue, TInt aExpected, TInt aLine)
sl@0: 	{
sl@0: 	if(aValue != aExpected)
sl@0: 		{
sl@0: 		DeleteTestFiles();
sl@0: 		RDebug::Print(_L("*** Expected error: %d, got: %d\r\n"), aExpected, aValue);
sl@0: 		TheTest(EFalse, aLine);
sl@0: 		}
sl@0: 	}
sl@0: #define TEST(arg) ::Check((arg), __LINE__)
sl@0: #define TEST2(aValue, aExpected) ::Check(aValue, aExpected, __LINE__)
sl@0: 
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: 
sl@0: static void CreatePrivateDirs()
sl@0: 	{
sl@0: 	RFs fs;
sl@0: 	TEST2(fs.Connect(), KErrNone);
sl@0: 	for(TInt i=0;i<('Z'-'A');++i)
sl@0: 		{
sl@0: 		fs.CreatePrivatePath(i);
sl@0: 		}
sl@0: 	fs.Close();
sl@0: 	}
sl@0: 
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: 
sl@0: static void PrintConfig(const TDesC& aDbFilePath)
sl@0: 	{
sl@0: 	RDebug::Print(_L("================= Configuration ================\r\n"));	
sl@0: 	RDebug::Print(_L("Cache page size %dK, pages %d, total %dK\r\n"), SQLITE_DEFAULT_PAGE_SIZE/1024, SQLITE_DEFAULT_CACHE_SIZE, SQLITE_DEFAULT_PAGE_SIZE * SQLITE_DEFAULT_CACHE_SIZE/1024);	
sl@0: 	RDebug::Print(_L("Temp cache page size %dK, pages %d, total %dK\r\n"), SQLITE_DEFAULT_PAGE_SIZE/1024, SQLITE_DEFAULT_TEMP_CACHE_SIZE, SQLITE_DEFAULT_PAGE_SIZE * SQLITE_DEFAULT_TEMP_CACHE_SIZE/1024);	
sl@0: 	_LIT(K1, "On");
sl@0: 	_LIT(K2, "Off");
sl@0: 	RDebug::Print(_L("Autovacuum: %S\r\n"), SQLITE_DEFAULT_AUTOVACUUM ? &K1 : &K2);	
sl@0: 	#ifdef SQLITE_DEBUG
sl@0: 		RDebug::Print(_L("Debug: On\r\n"));	
sl@0: 	#else
sl@0: 		RDebug::Print(_L("Debug: Off\r\n"));	
sl@0: 	#endif
sl@0: 	RDebug::Print(_L("Db file: %S\r\n"), &aDbFilePath);
sl@0: 	RDebug::Print(_L("================================================\r\n"));	
sl@0: 	}
sl@0: 	
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: 
sl@0: static TInt callback(void */*NotUsed*/, TInt argc, char **argv, char **azColName)
sl@0: 	{
sl@0: 	TInt i;
sl@0: 	for(i=0; i<argc; i++)
sl@0: 		{
sl@0: 		TPtrC8 colName((const TUint8*)azColName[i]);
sl@0: 		TPtrC8 colVal((const TUint8*)(argv[i] ? argv[i] : "NULL"));
sl@0: 		TheBuf2.Copy(colName);
sl@0: 		TheBuf2.Append(_L(" = "));
sl@0: 		TheBuf1.Copy(colVal);
sl@0: 		TheBuf2.Append(TheBuf1);
sl@0:     	RDebug::Print(_L("%S\r\n"), &TheBuf2);
sl@0: 		}
sl@0:   	RDebug::Print(_L("\n"));
sl@0:   	return 0;
sl@0: 	}
sl@0: 
sl@0: static TInt Compare(void*, TInt size1, const void* p1, TInt size2, const void* p2)
sl@0: 	{
sl@0: 	TPtrC8 ptr1((TUint8*)p1, size1);
sl@0: 	TPtrC8 ptr2((TUint8*)p2, size2);
sl@0: 	
sl@0: 	TInt res = ptr1.CompareF(ptr2);
sl@0: 	return res;		
sl@0: 	}
sl@0: 
sl@0: static TInt Compare2(void*, TInt size1, const void* p1, TInt size2, const void* p2)
sl@0: 	{
sl@0: 	TPtrC8 ptr1((TUint8*)p1, size1);
sl@0: 	TPtrC8 ptr2((TUint8*)p2, size2);
sl@0: 	
sl@0: 	TInt res = -ptr1.Compare(ptr2);
sl@0: 	return res;		
sl@0: 	}
sl@0: 
sl@0: 
sl@0: static void Func(sqlite3_context* ctx, TInt nCnt, sqlite3_value** pp)
sl@0: 	{
sl@0: 	for(TInt i=0;i<nCnt;++i)
sl@0: 		{
sl@0: 		TInt v = sqlite3_value_int(*pp++);
sl@0: 		v = v;
sl@0: 		}
sl@0: 	sqlite3_result_int(ctx, 564);		
sl@0: 	}
sl@0: 
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: 
sl@0: //Create database, table, view, INSERT, SELECT, DELETE sql statements, register collations, 
sl@0: //user defined functions, CREATE INDEX, CREATE VIEW sql statements, GROUP BY, HAVING, EXCEPT,...
sl@0: static void DoTests1()
sl@0: 	{
sl@0: 	char *zErrMsg = 0;
sl@0: 	TInt rc;
sl@0: 
sl@0: 	rc = sqlite3_open(KDbName1, &TheDb1);
sl@0:   	if(rc)
sl@0:   		{
sl@0:   		TPtrC p = ConvertToUtf16(sqlite3_errmsg(TheDb1));
sl@0:   		RDebug::Print(_L("Can't open database, err %d, msg: %S\n"), rc, &p);
sl@0:     	TEST(0);
sl@0:   		}
sl@0: 
sl@0: 	rc = sqlite3_create_collation(TheDb1, "Clt1", SQLITE_UTF8, NULL, &Compare);
sl@0:   	if(rc)
sl@0:   		{
sl@0:   		TPtrC p = ConvertToUtf16(sqlite3_errmsg(TheDb1));
sl@0:   		RDebug::Print(_L("Can't create collation, err %d, msg: %S\n"), rc, &p);
sl@0:     	TEST(0);
sl@0:   		}
sl@0: 
sl@0: 	rc = sqlite3_create_collation(TheDb1, "Clt2", SQLITE_UTF8, NULL, &Compare2);
sl@0:   	if(rc)
sl@0:   		{
sl@0:   		TPtrC p = ConvertToUtf16(sqlite3_errmsg(TheDb1));
sl@0:   		RDebug::Print(_L("Can't create collation, err %d, msg: %S\n"), rc, &p);
sl@0:     	TEST(0);
sl@0:   		}
sl@0: 
sl@0: 	rc = sqlite3_create_function(TheDb1, "Func", 2, SQLITE_UTF8, NULL, Func, NULL, NULL);
sl@0:   	if(rc)
sl@0:   		{
sl@0:   		TPtrC p = ConvertToUtf16(sqlite3_errmsg(TheDb1));
sl@0:   		RDebug::Print(_L("Can't create UDF, err %d, msg: %S\n"), rc, &p);
sl@0:     	TEST(0);
sl@0:   		}
sl@0: 
sl@0: 	rc = sqlite3_exec(TheDb1, "\
sl@0: 	             BEGIN TRANSACTION;\
sl@0: 	             CREATE TABLE Pn(Id INTEGER PRIMARY KEY AUTOINCREMENT,\
sl@0: 	                             Name TEXT NOT NULL,\
sl@0: 	                             Surname TEXT NOT NULL COLLATE Clt1);\
sl@0: 	             CREATE INDEX PnSurname ON Pn(Surname);\
sl@0: 	             CREATE TABLE Addr(Id INTEGER PRIMARY KEY AUTOINCREMENT,\
sl@0: 	                               PnId INTEGER,\
sl@0: 	                               CityId INTEGER,\
sl@0: 	                               Location TEXT DEFAULT 'address');\
sl@0: 	             CREATE INDEX AddrPnId ON Addr(PnId);\
sl@0: 	             CREATE INDEX AddrCityId ON Addr(CityId);\
sl@0: 	             CREATE TABLE City(ID INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT Default 'None');\
sl@0: 	             CREATE TABLE Shop(Id INTEGER, Name TEXT);\
sl@0: 	             CREATE TABLE Sales(ShopId INTEGER, M MONEY, D TIMESTAMP, D1 DATE, T1 TIME);\
sl@0: 	             CREATE TRIGGER TrgPnDel BEFORE DELETE ON Pn \
sl@0: 	             BEGIN \
sl@0: 					SELECT Func(2,8);\
sl@0: 	                SELECT CASE WHEN ((SELECT COUNT(*) FROM Addr WHERE Addr.PnId = old.Id) > 0) \
sl@0: 	                            THEN RAISE (ABORT, 'Foreign key constraint violation!!!') \
sl@0: 	                END;\
sl@0: 	             END;\
sl@0: 	             CREATE VIEW V1 AS SELECT * FROM Pn;\
sl@0: 	             CREATE VIEW V2 AS SELECT * FROM Addr;\
sl@0: 	             CREATE VIEW V3 AS SELECT * FROM Sales;\
sl@0: 	             CREATE VIEW V4 AS SELECT * FROM Shop;\
sl@0: 	             COMMIT TRANSACTION;", callback, 0, &zErrMsg);
sl@0: 	             
sl@0: 	if(rc != SQLITE_OK)
sl@0: 		{
sl@0:   		TPtrC p = ConvertToUtf16(zErrMsg);
sl@0:   		RDebug::Print(_L("SQL error %d, msg: %S\n"), rc, &p);
sl@0:     	sqlite3_free(zErrMsg);
sl@0:     	TEST(0);
sl@0: 		}
sl@0: 
sl@0: 	rc = sqlite3_exec(TheDb1, 
sl@0: 	"BEGIN TRANSACTION;\
sl@0: 	 INSERT INTO Pn(Name, Surname) VALUES('Aaaa1', 'Aaaa2');\
sl@0: 	 INSERT INTO Pn(Name, Surname) VALUES('Bbbb1', 'Bbbb2');\
sl@0: 	 INSERT INTO Pn(Name, Surname) VALUES('Cccc1', 'Cccc2');\
sl@0: 	 INSERT INTO Pn(Name, Surname) VALUES('Dddd1', 'Dddd2');\
sl@0: 	 INSERT INTO City(Name) VALUES('London');\
sl@0: 	 INSERT INTO City(Name) VALUES('Manchester');\
sl@0: 	 INSERT INTO Addr(PnId, CityId, Location) SELECT Pn.Id, City.Id, 'Here' FROM Pn, City WHERE Pn.Surname = 'Cccc2' AND City.Name = 'London';\
sl@0: 	 INSERT INTO Addr(PnId, CityId, Location) SELECT Pn.Id, City.Id, 'There' FROM Pn, City WHERE Pn.Surname = 'Bbbb2' AND City.Name = 'Manchester';\
sl@0: 	 INSERT INTO Shop(Id, Name) VALUES(1, 'Shop-1');\
sl@0: 	 INSERT INTO Shop(Id, Name) VALUES(2, 'Shop-2');\
sl@0: 	 INSERT INTO Shop(Id, Name) VALUES(3, 'Shop-3');\
sl@0: 	 INSERT INTO Sales(ShopId, M, D, D1, T1) VALUES(1, 123.0, '2005-01-01', CURRENT_DATE, '12:34:17');\
sl@0: 	 INSERT INTO Sales(ShopId, M, D, D1, T1) VALUES(2, 100.0, '2005-01-01', '2005-01-27', '12:34:18');\
sl@0: 	 INSERT INTO Sales(ShopId, M, D, D1, T1) VALUES(2, 200.0, '2005-01-02', '2005-01-28', '12:34:19');\
sl@0: 	 INSERT INTO Sales(ShopId, M, D, D1, T1) VALUES(3, 200.0, '2005-01-03', '2005-01-29', '12:34:23');\
sl@0: 	 COMMIT TRANSACTION;", callback, 0, &zErrMsg);
sl@0: 	if(rc != SQLITE_OK)
sl@0: 		{
sl@0:   		TPtrC p = ConvertToUtf16(zErrMsg);
sl@0:   		RDebug::Print(_L("SQL error %d, msg: %S\n"), rc, &p);
sl@0:     	sqlite3_free(zErrMsg);
sl@0:     	TEST(0);
sl@0: 		}
sl@0: 
sl@0: 	rc = sqlite3_exec(TheDb1, "SELECT COUNT(*) FROM Shop", callback, 0, &zErrMsg);
sl@0: 	if(rc != SQLITE_OK)
sl@0: 		{
sl@0:   		TPtrC p = ConvertToUtf16(zErrMsg);
sl@0:   		RDebug::Print(_L("SQL error %d, msg: %S\n"), rc, &p);
sl@0:     	sqlite3_free(zErrMsg);
sl@0:     	TEST(0);
sl@0: 		}
sl@0: 
sl@0: 	for(TInt i=0;i<500;++i)
sl@0: 		{
sl@0: 		_LIT8(KSqlStmt, "INSERT INTO Shop(Id, Name) VALUES(%d, 'Shop-%d')\x0");
sl@0: 		TheBuf3.Format(KSqlStmt, i+1, i+1);
sl@0: 		rc = sqlite3_exec(TheDb1, (const char*)TheBuf3.Ptr(), callback, 0, &zErrMsg);
sl@0: 		if(rc != SQLITE_OK)
sl@0: 			{
sl@0: 	  		TPtrC p = ConvertToUtf16(zErrMsg);
sl@0: 	  		RDebug::Print(_L("SQL error %d, msg: %S\n"), rc, &p);
sl@0: 	    	sqlite3_free(zErrMsg);
sl@0:     		TEST(0);
sl@0: 			}
sl@0: 		}
sl@0: 
sl@0: 	_LIT8(KSqlStmt2, "DELETE FROM Shop WHERE Id > 400\x0");
sl@0: 	rc = sqlite3_exec(TheDb1, (const char*)KSqlStmt2().Ptr(), callback, 0, &zErrMsg);
sl@0: 	if(rc != SQLITE_OK)
sl@0: 		{
sl@0:   		TPtrC p = ConvertToUtf16(zErrMsg);
sl@0:   		RDebug::Print(_L("SQL error %d, msg: %S\n"), rc, &p);
sl@0:     	sqlite3_free(zErrMsg);
sl@0:    		TEST(0);
sl@0: 		}
sl@0: 
sl@0: 	_LIT8(KSqlStmt3, "SELECT COUNT(*) FROM Shop\x0");
sl@0: 	rc = sqlite3_exec(TheDb1, (const char*)KSqlStmt3().Ptr(), callback, 0, &zErrMsg);
sl@0: 	if(rc != SQLITE_OK)
sl@0: 		{
sl@0:   		TPtrC p = ConvertToUtf16(zErrMsg);
sl@0:   		RDebug::Print(_L("SQL error %d, msg: %S\n"), rc, &p);
sl@0:     	sqlite3_free(zErrMsg);
sl@0:    		TEST(0);
sl@0: 		}
sl@0: 
sl@0: 	rc = sqlite3_exec(TheDb1, "SELECT Pn.Surname, Addr.Location, City.Name FROM Pn \
sl@0: 	      INNER JOIN Addr ON Pn.Id = Addr.PnId \
sl@0: 	      INNER JOIN City ON Addr.CityId = City.Id \
sl@0: 	      ORDER BY Surname COLLATE Clt2", callback, 0, &zErrMsg);
sl@0: 	if(rc != SQLITE_OK)
sl@0: 		{
sl@0:   		TPtrC p = ConvertToUtf16(zErrMsg);
sl@0:   		RDebug::Print(_L("SQL error %d, msg: %S\n"), rc, &p);
sl@0:     	sqlite3_free(zErrMsg);
sl@0:    		TEST(0);
sl@0: 		}
sl@0: 	      
sl@0: 	rc = sqlite3_exec(TheDb1, "SELECT * FROM Addr", callback, 0, &zErrMsg);
sl@0: 	if(rc != SQLITE_OK)
sl@0: 		{
sl@0:   		TPtrC p = ConvertToUtf16(zErrMsg);
sl@0:   		RDebug::Print(_L("SQL error %d, msg: %S\n"), rc, &p);
sl@0:     	sqlite3_free(zErrMsg);
sl@0:    		TEST(0);
sl@0: 		}
sl@0: 	
sl@0: 	rc = sqlite3_exec(TheDb1, "SELECT * FROM Sales WHERE D1 > '2005-08-27'", callback, 0, &zErrMsg);
sl@0: 	if(rc != SQLITE_OK)
sl@0: 		{
sl@0:   		TPtrC p = ConvertToUtf16(zErrMsg);
sl@0:   		RDebug::Print(_L("SQL error %d, msg: %S\n"), rc, &p);
sl@0:     	sqlite3_free(zErrMsg);
sl@0:    		TEST(0);
sl@0: 		}
sl@0: 	
sl@0: 	rc = sqlite3_exec(TheDb1, "SELECT V1.* FROM V1 EXCEPT SELECT V1.* FROM V1 WHERE V1.Id = 2", callback, 0, &zErrMsg);
sl@0: 	if(rc != SQLITE_OK)
sl@0: 		{
sl@0:   		TPtrC p = ConvertToUtf16(zErrMsg);
sl@0:   		RDebug::Print(_L("SQL error %d, msg: %S\n"), rc, &p);
sl@0:     	sqlite3_free(zErrMsg);
sl@0:    		TEST(0);
sl@0: 		}
sl@0: 
sl@0: 	rc = sqlite3_exec(TheDb1, "SELECT V3.D, SUM(V3.M) FROM V3 GROUP BY V3.D HAVING SUM(V3.M) > 210.0", callback, 0, &zErrMsg);
sl@0: 	if(rc != SQLITE_OK)
sl@0: 		{
sl@0:   		TPtrC p = ConvertToUtf16(zErrMsg);
sl@0:   		RDebug::Print(_L("SQL error %d, msg: %S\n"), rc, &p);
sl@0:     	sqlite3_free(zErrMsg);
sl@0:    		TEST(0);
sl@0: 		}
sl@0: 	
sl@0: 	rc = sqlite3_exec(TheDb1, "SELECT V4.Name, SUM(V3.M) FROM V4, V3 WHERE V4.Id = V3.ShopId GROUP BY V4.Id", callback, 0, &zErrMsg);
sl@0: 	if(rc != SQLITE_OK)
sl@0: 		{
sl@0:   		TPtrC p = ConvertToUtf16(zErrMsg);
sl@0:   		RDebug::Print(_L("SQL error %d, msg: %S\n"), rc, &p);
sl@0:     	sqlite3_free(zErrMsg);
sl@0:    		TEST(0);
sl@0: 		}
sl@0: 
sl@0: 	sqlite3_close(TheDb1);
sl@0: 	TheFs.Delete(ConvertToUtf16(KDbName1));
sl@0: 	}
sl@0: 
sl@0: //Generic SQL tests.
sl@0: static void DoTests2()
sl@0: 	{
sl@0: 	TheFs.Delete(ConvertToUtf16(KDbName1));
sl@0: 	
sl@0: 	char *zErrMsg = 0;
sl@0: 	TInt rc;
sl@0: 
sl@0: 	rc = sqlite3_open(KDbName1, &TheDb1);
sl@0:   	if(rc != SQLITE_OK)
sl@0:   		{
sl@0:   		TPtrC p = ConvertToUtf16(sqlite3_errmsg(TheDb1));
sl@0:   		RDebug::Print(_L("Can't open database, err %d, msg: %S\n"), rc, &p);
sl@0:     	TEST(0);
sl@0:   		}
sl@0:   		
sl@0: 	rc = sqlite3_exec(TheDb1, "PRAGMA auto_vacuum = 1", callback, 0, &zErrMsg);
sl@0: 	if(rc != SQLITE_OK)
sl@0: 		{
sl@0:   		TPtrC p = ConvertToUtf16(zErrMsg);
sl@0:   		RDebug::Print(_L("SQL error %d, msg: %S\n"), rc, &p);
sl@0:     	sqlite3_free(zErrMsg);
sl@0:    		TEST(0);
sl@0: 		}
sl@0: 	
sl@0: 	rc = sqlite3_exec(TheDb1, "\
sl@0: 	             BEGIN TRANSACTION;\
sl@0: 	             CREATE TABLE Shop(Id INTEGER, Name TEXT);\
sl@0: 	             CREATE INDEX ShopName ON Shop(Name);\
sl@0: 	             COMMIT TRANSACTION;", callback, 0, &zErrMsg);
sl@0: 	if(rc != SQLITE_OK)
sl@0: 		{
sl@0:   		TPtrC p = ConvertToUtf16(zErrMsg);
sl@0:   		RDebug::Print(_L("SQL error %d, msg: %S\n"), rc, &p);
sl@0:     	sqlite3_free(zErrMsg);
sl@0:    		TEST(0);
sl@0: 		}
sl@0: 		
sl@0: 	rc = sqlite3_exec(TheDb1, "BEGIN TRANSACTION", callback, 0, &zErrMsg);
sl@0: 	if(rc != SQLITE_OK)
sl@0: 		{
sl@0:   		TPtrC p = ConvertToUtf16(zErrMsg);
sl@0:   		RDebug::Print(_L("SQL error %d, msg: %S\n"), rc, &p);
sl@0:     	sqlite3_free(zErrMsg);
sl@0:    		TEST(0);
sl@0: 		}
sl@0: 	
sl@0: 	for(TInt i=0;i<1000;++i)
sl@0: 		{
sl@0: 		_LIT8(KSqlStmt, "INSERT INTO Shop(Id, Name) VALUES(%d, 'Shop-%d')\x0");
sl@0: 		TheBuf3.Format(KSqlStmt, i, i);
sl@0: 		rc = sqlite3_exec(TheDb1, (const char*)TheBuf3.Ptr(), callback, 0, &zErrMsg);
sl@0: 		if(rc != SQLITE_OK)
sl@0: 			{
sl@0: 	  		TPtrC p = ConvertToUtf16(zErrMsg);
sl@0: 	  		RDebug::Print(_L("SQL error %d, msg: %S\n"), rc, &p);
sl@0: 	    	sqlite3_free(zErrMsg);
sl@0: 	   		TEST(0);
sl@0: 			}
sl@0: 		}
sl@0: 		
sl@0: 	rc = sqlite3_exec(TheDb1, "COMMIT TRANSACTION", callback, 0, &zErrMsg);
sl@0: 	if(rc != SQLITE_OK)
sl@0: 		{
sl@0:   		TPtrC p = ConvertToUtf16(zErrMsg);
sl@0:   		RDebug::Print(_L("SQL error %d, msg: %S\n"), rc, &p);
sl@0:     	sqlite3_free(zErrMsg);
sl@0:    		TEST(0);
sl@0: 		}
sl@0: 
sl@0: 	rc = sqlite3_exec(TheDb1, "SELECT COUNT(*) FROM Shop", callback, 0, &zErrMsg);
sl@0: 	if(rc != SQLITE_OK)
sl@0: 		{
sl@0:   		TPtrC p = ConvertToUtf16(zErrMsg);
sl@0:   		RDebug::Print(_L("SQL error %d, msg: %S\n"), rc, &p);
sl@0:     	sqlite3_free(zErrMsg);
sl@0:    		TEST(0);
sl@0: 		}
sl@0: 
sl@0: 	rc = sqlite3_exec(TheDb1, "BEGIN TRANSACTION", callback, 0, &zErrMsg);
sl@0: 	if(rc != SQLITE_OK)
sl@0: 		{
sl@0:   		TPtrC p = ConvertToUtf16(zErrMsg);
sl@0:   		RDebug::Print(_L("SQL error %d, msg: %S\n"), rc, &p);
sl@0:     	sqlite3_free(zErrMsg);
sl@0:    		TEST(0);
sl@0: 		}
sl@0: 	
sl@0: 	rc = sqlite3_exec(TheDb1, "DELETE FROM Shop WHERE Id > 100", callback, 0, &zErrMsg);
sl@0: 	if(rc != SQLITE_OK)
sl@0: 		{
sl@0:   		TPtrC p = ConvertToUtf16(zErrMsg);
sl@0:   		RDebug::Print(_L("SQL error %d, msg: %S\n"), rc, &p);
sl@0:     	sqlite3_free(zErrMsg);
sl@0:    		TEST(0);
sl@0: 		}
sl@0: 		
sl@0: 	rc = sqlite3_exec(TheDb1, "COMMIT TRANSACTION", callback, 0, &zErrMsg);
sl@0: 	if(rc != SQLITE_OK)
sl@0: 		{
sl@0:   		TPtrC p = ConvertToUtf16(zErrMsg);
sl@0:   		RDebug::Print(_L("SQL error %d, msg: %S\n"), rc, &p);
sl@0:     	sqlite3_free(zErrMsg);
sl@0:    		TEST(0);
sl@0: 		}
sl@0: 
sl@0: 	rc = sqlite3_exec(TheDb1, "SELECT COUNT(*) FROM Shop", callback, 0, &zErrMsg);
sl@0: 	if(rc != SQLITE_OK)
sl@0: 		{
sl@0:   		TPtrC p = ConvertToUtf16(zErrMsg);
sl@0:   		RDebug::Print(_L("SQL error %d, msg: %S\n"), rc, &p);
sl@0:     	sqlite3_free(zErrMsg);
sl@0:    		TEST(0);
sl@0: 		}
sl@0: 
sl@0: 	sqlite3_close(TheDb1);
sl@0: 	TheFs.Delete(ConvertToUtf16(KDbName1));
sl@0: 	}
sl@0: 
sl@0: //Accented column names test
sl@0: static void AccentedColumnNamesTestL()
sl@0: 	{
sl@0: 	TheFs.Delete(ConvertToUtf16(KDbName1));
sl@0: 
sl@0: 	//Open database
sl@0: 	TPtrC tmp = ConvertToUtf16(KDbName1);
sl@0: 	TBuf<KMaxFileName + 1> fname;
sl@0: 	fname.Copy(tmp);
sl@0: 	TInt rc = sqlite3_open16(fname.PtrZ(), &TheDb1);//!!!!16-bit encoding!!!!!
sl@0:   	if(rc != SQLITE_OK)
sl@0:   		{
sl@0:   		TPtrC p = ConvertToUtf16(sqlite3_errmsg(TheDb1));
sl@0:   		RDebug::Print(_L("Can't open database, err %d, msg: %S\n"), rc, &p);
sl@0:     	TEST(0);
sl@0:   		}
sl@0: 	
sl@0: 	//Create table
sl@0: 	_LIT(KSqlStrZ, "CREATE TABLE abc(col_\u00C4 integer, col_A text)\0");
sl@0: //	_LIT(KSqlStrZ, "CREATE TABLE abc(col_a integer, col_A text)\0");
sl@0: //	_LIT(KSqlStrZ, "CREATE TABLE abc(col_Ä integer, col_ä text)\0");
sl@0: //	_LIT(KSqlStrZ, "CREATE TABLE abc(col_Ä integer, col_A\x308 text)\0");
sl@0: 	sqlite3_stmt* stmtHandle = NULL;
sl@0: 	const void* stmtTailZ = NULL;
sl@0: 	rc = sqlite3_prepare16_v2(TheDb1, KSqlStrZ().Ptr(), -1, &stmtHandle, &stmtTailZ);
sl@0: 	if(rc != SQLITE_OK)
sl@0: 		{
sl@0: 		const void* errMsgZ = sqlite3_errmsg16(TheDb1);
sl@0: 		TPtrC msg(reinterpret_cast <const TText16*> (errMsgZ), wcslen(reinterpret_cast <const wchar_t*> (errMsgZ)));
sl@0: 		RDebug::Print(_L("'sqlite3_prepare16_v2()' failed, err %d, error msg: \"%S\"\r\n"), rc, &msg);
sl@0:     	TEST(0);
sl@0: 		}
sl@0: 	rc = sqlite3_step(stmtHandle);
sl@0: 	TEST(rc == SQLITE_DONE);
sl@0: 
sl@0: 	sqlite3_finalize(stmtHandle);
sl@0: 	stmtHandle = NULL;
sl@0: 
sl@0: 	//Select from the table
sl@0: 	_LIT(KSqlStrZ2, "SELECT * FROM abc WHERE :prm_\u00C4 = col_\u00C4 and :prm_\u00E4 = col_A and :prm_A = col_A and :prm_a = col_\u00C4\0");
sl@0: 	rc = sqlite3_prepare16_v2(TheDb1, KSqlStrZ2().Ptr(), -1, &stmtHandle, &stmtTailZ);
sl@0: 	if(rc != SQLITE_OK)
sl@0: 		{
sl@0: 		const void* errMsgZ = sqlite3_errmsg16(TheDb1);
sl@0: 		TPtrC msg(reinterpret_cast <const TText16*> (errMsgZ), wcslen(reinterpret_cast <const wchar_t*> (errMsgZ)));
sl@0: 		RDebug::Print(_L("'sqlite3_prepare16_v2()' failed, err %d, error msg: \"%S\"\r\n"), rc, &msg);
sl@0:     	TEST(0);
sl@0: 		}
sl@0: 
sl@0: 	//parameter checks
sl@0: 	TBuf<16> prmNames[5];
sl@0:  	TInt prmCount = sqlite3_bind_parameter_count(stmtHandle);
sl@0:  	for(TInt i=1;i<=prmCount;++i)
sl@0:  		{
sl@0:  		const char* prmNameZ = sqlite3_bind_parameter_name(stmtHandle, i);
sl@0: 		TPtrC8 name8(reinterpret_cast <const TUint8*> (prmNameZ), strlen(prmNameZ));
sl@0: 		HBufC* name = CnvUtfConverter::ConvertToUnicodeFromUtf8L(name8);
sl@0: 		prmNames[i] = *name;
sl@0: 		delete name;
sl@0:  		}
sl@0:  	TInt prmIndex0_1 = sqlite3_bind_parameter_index(stmtHandle, ":prm_Ä");
sl@0:  	prmIndex0_1 = prmIndex0_1;
sl@0:  	TInt prmIndex1_1 = sqlite3_bind_parameter_index(stmtHandle, ":prm_ä");
sl@0:  	prmIndex1_1 = prmIndex1_1;
sl@0:  	TInt prmIndex2_1 = sqlite3_bind_parameter_index(stmtHandle, ":prm_A");
sl@0:  	prmIndex2_1 = prmIndex2_1;
sl@0:  	TInt prmIndex3_1 = sqlite3_bind_parameter_index(stmtHandle, ":prm_a");
sl@0:  	prmIndex3_1 = prmIndex3_1;
sl@0: 	
sl@0: 	TBuf8<16> name8;
sl@0: 	
sl@0: 	HBufC8* name = CnvUtfConverter::ConvertFromUnicodeToUtf8L(prmNames[1]);
sl@0: 	name8 = *name;
sl@0: 	delete name;
sl@0:  	TInt prmIndex0_2 = sqlite3_bind_parameter_index(stmtHandle, (const char*)name8.PtrZ());
sl@0:  	prmIndex0_2 = prmIndex0_2;
sl@0:  	
sl@0: 	name = CnvUtfConverter::ConvertFromUnicodeToUtf8L(prmNames[2]);
sl@0: 	name8 = *name;
sl@0: 	delete name;
sl@0:  	TInt prmIndex1_2 = sqlite3_bind_parameter_index(stmtHandle, (const char*)name8.PtrZ());
sl@0:  	prmIndex1_2 = prmIndex1_2;
sl@0:  	
sl@0: 	name = CnvUtfConverter::ConvertFromUnicodeToUtf8L(prmNames[3]);
sl@0: 	name8 = *name;
sl@0: 	delete name;
sl@0:  	TInt prmIndex2_2 = sqlite3_bind_parameter_index(stmtHandle, (const char*)name8.PtrZ());
sl@0:  	prmIndex2_2 = prmIndex2_2;
sl@0:  	
sl@0: 	name = CnvUtfConverter::ConvertFromUnicodeToUtf8L(prmNames[4]);
sl@0: 	name8 = *name;
sl@0: 	delete name;
sl@0:  	TInt prmIndex3_2 = sqlite3_bind_parameter_index(stmtHandle, (const char*)name8.PtrZ());
sl@0:  	prmIndex3_2 = prmIndex3_2;
sl@0: 	
sl@0: 	//Column checks
sl@0: 	TPtrC colName1(_L("col_\u00C4"));
sl@0: 	TPtrC colName2(_L("col_A"));
sl@0: 	
sl@0: 	const void* p = sqlite3_column_name16(stmtHandle, 0);
sl@0: 	TPtrC pp1((const TUint16*)p, wcslen(reinterpret_cast <const wchar_t*> (p)));
sl@0: 	TEST(colName1 == pp1);
sl@0: 	
sl@0: 	p = sqlite3_column_name16(stmtHandle, 1);
sl@0: 	TPtrC pp2((const TUint16*)p, wcslen(reinterpret_cast <const wchar_t*> (p)));
sl@0: 	TEST(colName2 == pp2);
sl@0: 		
sl@0: 	sqlite3_finalize(stmtHandle);
sl@0: 	
sl@0: 	sqlite3_close(TheDb1);
sl@0: 	TheFs.Delete(ConvertToUtf16(KDbName1));
sl@0: 	}
sl@0: 
sl@0: //////////////////////////////////////////////////////////////////////////////////////////////////
sl@0: //////////////////////////////////////////////////////////////////////////////////////////////////
sl@0: //////////////////////////////////////////////////////////////////////////////////////////////////
sl@0: //////////////              "Add Contacts" test case                  ////////////////////////////
sl@0: //////////////////////////////////////////////////////////////////////////////////////////////////
sl@0: 
sl@0: //Read SQL file - 8-bit, zero-terminated string
sl@0: static char* ReadSQL2(const TDesC& aSqlFileName)
sl@0: 	{
sl@0: 	RFile file;
sl@0: 	TEST2(file.Open(TheFs, aSqlFileName, EFileRead), KErrNone);
sl@0: 	
sl@0: 	TInt size = 0;
sl@0: 	TEST2(file.Size(size), KErrNone);
sl@0: 	
sl@0: 	char* sql = new char [size + 1];
sl@0: 	TEST(sql != NULL);
sl@0: 	
sl@0: 	TPtr8 ptr((TUint8*)sql, size + 1);
sl@0: 	TEST2(file.Read(ptr, size), KErrNone);
sl@0: 	*(sql + size) = 0;
sl@0: 
sl@0: 	file.Close();
sl@0: 	return sql;
sl@0: 	}
sl@0: 
sl@0: //Read SQL file - 16-bit, zero-terminated string
sl@0: static HBufC* ReadSQL16(const TDesC& aSqlFileName)
sl@0: 	{
sl@0: 	RFile file;
sl@0: 	TEST2(file.Open(TheFs, aSqlFileName, EFileRead), KErrNone);
sl@0: 	
sl@0: 	TInt size = 0;
sl@0: 	TEST2(file.Size(size), KErrNone);
sl@0: 
sl@0: 	char* sql = new char [size];
sl@0: 	TEST(sql != NULL);
sl@0: 	TPtr8 ptr((TUint8*)sql, size);
sl@0: 	TEST2(file.Read(ptr, size), KErrNone);
sl@0: 	
sl@0: 	HBufC* sql16 = HBufC::New(size + 1);
sl@0: 	TEST(sql16 != NULL);
sl@0: 	TPtr16 ptr16 = sql16->Des();
sl@0: 	ptr16.Copy(ptr);
sl@0: 	ptr16.Append(TChar(0));
sl@0: 
sl@0: 	delete [] sql;
sl@0: 	file.Close();
sl@0: 	return sql16;
sl@0: 	}
sl@0: 
sl@0: //Prints file size in bytes
sl@0: static void PrintFileSize(const TDesC& aPath)
sl@0: 	{
sl@0: 	RFile file;
sl@0: 	TEST2(file.Open(TheFs, aPath, EFileRead), KErrNone);
sl@0: 	TInt size = 0;
sl@0: 	TEST2(file.Size(size), KErrNone);
sl@0: 	file.Close();
sl@0: 	RDebug::Print(_L("File \"%S\", size: %d\r\n"), &aPath, size);
sl@0: 	}
sl@0: 
sl@0: //Executes 8-bit SQL statement	
sl@0: static void ExecSql(sqlite3* aDbHandle, const char* aSqlStmt, const TDesC& aMsg)
sl@0: 	{
sl@0: 	TEST(aDbHandle != NULL);
sl@0: 	TEST(aSqlStmt != NULL);
sl@0: 	
sl@0: 	char* errMsg = NULL;
sl@0: 	TTime t1;
sl@0: 	t1.UniversalTime();
sl@0: 	TInt rc = sqlite3_exec(aDbHandle, aSqlStmt, callback, 0, &errMsg);
sl@0: 	if(rc != SQLITE_OK)
sl@0: 		{
sl@0: 		TPtrC8 ptr8((const TUint8*)errMsg, strlen(errMsg));
sl@0: 		TheBuf1.Copy(ptr8);
sl@0: 		RDebug::Print(_L("'sqlite3_exec()' failed, err %d, error msg: \"%S\"\t\n"), rc, &TheBuf1);
sl@0: 		TEST(0);
sl@0: 		}
sl@0: 	TTime t2;
sl@0: 	t2.UniversalTime();
sl@0: 	TTimeIntervalMicroSeconds diffTime = t2.MicroSecondsFrom(t1); 
sl@0: 	diffTime = diffTime.Int64() / 1000;
sl@0: 	RDebug::Print(_L("%S, time: %d ms\r\n"), &aMsg, (TInt)diffTime.Int64());
sl@0: 	}
sl@0: 
sl@0: //This function searches aString argument for ';' occurences.
sl@0: //Every time when it finds a ';' character, the function places a 0 right after the ';' and
sl@0: //tests the just created, zero-terminated substring if it is a comlpete SQL statement.
sl@0: //If it is a SQL statement, the function returns it and modifies aString argument to point right after the found
sl@0: //SQL string. If it is not SQL statement, the function will continue the searching.
sl@0: //If there is no ';' inside aString argument, the function returns the same string as a return result and
sl@0: //modifies aString argument - sets it to TPtr(NULL, 0, 0).
sl@0: //
sl@0: //The function expects aString argument to be zero-terminated.
sl@0: static TPtrC GetFirstSqlStmt(TPtr& aString)
sl@0: 	{
sl@0: 	const TChar KDelimitier(';');
sl@0: 	TPtr str(const_cast <TUint16*> (aString.Ptr()), aString.Length(), aString.Length());
sl@0: 	TInt afterDelimitierPos = 0;
sl@0: 	TInt pos;
sl@0: 	while((pos = str.Locate(KDelimitier) + 1) > 0 && pos < str.Length())
sl@0: 		{
sl@0: 		//There is a possibility that the string which terminates with the found ';' is SQL statement.
sl@0: 		//Zero terminate the string placing a zero right after ';' character and test it using sqlite3_complete16()
sl@0: 		//call. If it is not SQL string, restore the original character and continue searching.
sl@0: 		afterDelimitierPos += pos;
sl@0: 		TChar ch = aString[afterDelimitierPos];
sl@0: 		aString[afterDelimitierPos] = 0;
sl@0: 		TInt res = sqlite3_complete16(aString.Ptr());
sl@0: 		aString[afterDelimitierPos] = ch;
sl@0: 		if(res)
sl@0: 			{
sl@0: 			str.Set(const_cast <TUint16*> (aString.Ptr()), afterDelimitierPos, afterDelimitierPos);	
sl@0: 			//Replace the found ';' character with 0.
sl@0: 			str[afterDelimitierPos - 1] = 0;
sl@0: 			aString.Set(const_cast <TUint16*> (aString.Ptr()) + afterDelimitierPos, aString.Length() - afterDelimitierPos, aString.Length() - afterDelimitierPos);
sl@0: 			return str;
sl@0: 			}
sl@0: 		str.Set(const_cast <TUint16*> (str.Ptr()) + pos, str.Length() - pos, str.Length() - pos);	
sl@0: 		}
sl@0: 	//aString argument does not contain valid SQL statement or there is no ';' character inside aString.
sl@0: 	//Set aString to TPtr(NULL, 0, 0) and return the original string.
sl@0: 	aString.Set(NULL, 0, 0);
sl@0: 	return str;
sl@0: 	}
sl@0: 	
sl@0: //Executes 16-bit SQL statement	
sl@0: static void ExecSql16(sqlite3* aDbHandle, TDes& aSqlStmtZ, const TDesC& aMsg)
sl@0: 	{
sl@0: 	TEST(aDbHandle != NULL);
sl@0: 		
sl@0: 	TTime t1;
sl@0: 	t1.UniversalTime();
sl@0: 	
sl@0: 	TPtr sqlLeftZ(const_cast <TUint16*> (aSqlStmtZ.Ptr()), aSqlStmtZ.Length(), aSqlStmtZ.Length());
sl@0: 	while(sqlLeftZ.Length() > 0)
sl@0: 		{
sl@0: 		TPtrC sql = GetFirstSqlStmt(sqlLeftZ);
sl@0: 		sqlite3_stmt* stmtHandle = NULL;
sl@0: 		const void* stmtTailZ = NULL;
sl@0: 		TInt err = sqlite3_prepare16_v2(aDbHandle, sql.Ptr(), -1, &stmtHandle, &stmtTailZ);
sl@0: 		__ASSERT_ALWAYS(!stmtTailZ || User::StringLength((const TUint16*)stmtTailZ) == 0, User::Invariant());
sl@0: 		if(stmtHandle)
sl@0: 			{
sl@0: 			if(err == SQLITE_OK)
sl@0: 				{
sl@0: 				while((err = sqlite3_step(stmtHandle)) == SQLITE_ROW)
sl@0: 					{
sl@0: 					}
sl@0: 				}
sl@0: 			sqlite3_finalize(stmtHandle);
sl@0: 			}
sl@0: 		if(err != SQLITE_DONE && err != SQLITE_OK)
sl@0: 			{
sl@0: 			const void* errMsgZ = sqlite3_errmsg16(aDbHandle);
sl@0: 			TPtrC msg(reinterpret_cast <const TText16*> (errMsgZ), wcslen(reinterpret_cast <const wchar_t*> (errMsgZ)));
sl@0: 			RDebug::Print(_L("'sqlite3_exec16()' failed, err %d, error msg: \"%S\"\t\n"), err, &msg);
sl@0: 			TEST(0);
sl@0: 			}
sl@0: 		}
sl@0: 	TTime t2;
sl@0: 	t2.UniversalTime();
sl@0: 	TTimeIntervalMicroSeconds diffTime = t2.MicroSecondsFrom(t1); 
sl@0: 	diffTime = diffTime.Int64() / 1000;
sl@0: 	RDebug::Print(_L("%S, time: %d ms\r\n"), &aMsg, (TInt)diffTime.Int64());
sl@0: 	}
sl@0: 
sl@0: //////////////////////////////////////////////////////////////////////////////////////////////////
sl@0: //////////////////////////////////////////////////////////////////////////////////////////////////
sl@0: //////////////////////////////////////////////////////////////////////////////////////////////////
sl@0: //////////////              "Search" test case                        ////////////////////////////
sl@0: //////////////////////////////////////////////////////////////////////////////////////////////////
sl@0: 
sl@0: struct TPnName
sl@0: 	{
sl@0: 	TPnName(const TDesC8& aFirstName, const TDesC8& aSurname) :
sl@0: 		iFirstName(aFirstName),
sl@0: 		iSurname(aSurname)
sl@0: 		{
sl@0: 		}
sl@0: 	const TPtrC8 iFirstName;
sl@0: 	const TPtrC8 iSurname;
sl@0: 	};
sl@0: 
sl@0: const TInt KNamesCnt = 100;
sl@0: 	
sl@0: const TPnName KNames[KNamesCnt] = 
sl@0: 	{
sl@0: 		TPnName(_L8("Kauh"), _L8("Mollel")),
sl@0: 		TPnName(_L8("Be"), _L8("Balcalertthawnd")),
sl@0: 		TPnName(_L8("Joba"), _L8("Hah")),
sl@0: 		TPnName(_L8("Mal"), _L8("Sinianna")),
sl@0: 		TPnName(_L8("Alip"), _L8("Hanittrinke")),
sl@0: 		TPnName(_L8("Ris"), _L8("Aba")),
sl@0: 		TPnName(_L8("Nirindrilo"), _L8("Oangah")),
sl@0: 		TPnName(_L8("An"), _L8("Mck")),
sl@0: 		TPnName(_L8("Ris"), _L8("Jat")),
sl@0: 		TPnName(_L8("Ja"), _L8("R")),
sl@0: 		TPnName(_L8("Pary"), _L8("Sawngethwnes")),
sl@0: 		TPnName(_L8("Main"), _L8("Stonstc")),
sl@0: 		TPnName(_L8("Joldan"), _L8("Misonialonss")),
sl@0: 		TPnName(_L8("Ja"), _L8("Beetth")),
sl@0: 		TPnName(_L8("An"), _L8("Magill")),
sl@0: 		TPnName(_L8("Ste"), _L8("Hakegstolbebilance")),
sl@0: 		TPnName(_L8("Laelefattal"), _L8("Bume")),
sl@0: 		TPnName(_L8("Anortoausl"), _L8("Kenoonssssoninals")),
sl@0: 		TPnName(_L8("Sthnill"), _L8("Huere")),
sl@0: 		TPnName(_L8("Elatandy"), _L8("Miadhelbi")),
sl@0: 		TPnName(_L8("Nevieohageridik"), _L8("Baronirgeriallyemo")),
sl@0: 		TPnName(_L8("Dertrry"), _L8("Miches")),
sl@0: 		TPnName(_L8("Tan-"), _L8("Sonagutlly")),
sl@0: 		TPnName(_L8("Mazianer"), _L8("Wi")),
sl@0: 		TPnName(_L8("Kesadrin"), _L8("Swixohar")),
sl@0: 		TPnName(_L8("Juhnn"), _L8("Vezuins")),
sl@0: 		TPnName(_L8("Geri"), _L8("Okun-Mamar")),
sl@0: 		TPnName(_L8("Jol"), _L8("Hadir")),
sl@0: 		TPnName(_L8("Lon"), _L8("Fonernginire")),
sl@0: 		TPnName(_L8("Brrk"), _L8("El")),
sl@0: 		TPnName(_L8("So"), _L8("Thanas")),
sl@0: 		TPnName(_L8("Timon"), _L8("Matarol")),
sl@0: 		TPnName(_L8("Clicartif"), _L8("Sandhinth")),
sl@0: 		TPnName(_L8("Dan"), _L8("Brl")),
sl@0: 		TPnName(_L8("An"), _L8("Danss")),
sl@0: 		TPnName(_L8("Y"), _L8("Gianstes")),
sl@0: 		TPnName(_L8("Gralilas"), _L8("Beny")),
sl@0: 		TPnName(_L8("Vamean"), _L8("Matesstel")),
sl@0: 		TPnName(_L8("Ch"), _L8("Inrinez")),
sl@0: 		TPnName(_L8("Ra"), _L8("Lusieing")),
sl@0: 		TPnName(_L8("Gerik"), _L8("Mawoshar")),
sl@0: 		TPnName(_L8("Nobrd"), _L8("Kerokilirtsoug")),
sl@0: 		TPnName(_L8("Norichnik"), _L8("Balmo")),
sl@0: 		TPnName(_L8("Anddra"), _L8("Fit")),
sl@0: 		TPnName(_L8("Maily"), _L8("Tanyerohetsphinbr")),
sl@0: 		TPnName(_L8("Frsa"), _L8("Huntorrenerkh")),
sl@0: 		TPnName(_L8("Gi"), _L8("Spandaveees")),
sl@0: 		TPnName(_L8("Jollminenipaninderal"), _L8("Vartzury")),
sl@0: 		TPnName(_L8("Ankshr"), _L8("Terawloleral")),
sl@0: 		TPnName(_L8("An"), _L8("La")),
sl@0: 		TPnName(_L8("Ma"), _L8("Brnd")),
sl@0: 		TPnName(_L8("Sonerdalmon"), _L8("Bo")),
sl@0: 		TPnName(_L8("Nis"), _L8("Tapeworrt")),
sl@0: 		TPnName(_L8("Shand"), _L8("Hacllik")),
sl@0: 		TPnName(_L8("San"), _L8("Sh")),
sl@0: 		TPnName(_L8("Mico"), _L8("Javiaros")),
sl@0: 		TPnName(_L8("Hub"), _L8("Warey")),
sl@0: 		TPnName(_L8("Mambew"), _L8("Maw")),
sl@0: 		TPnName(_L8("Honik"), _L8("Fantscerstetoringu")),
sl@0: 		TPnName(_L8("Da"), _L8("Saneelur")),
sl@0: 		TPnName(_L8("Aberecalahayondorttelin"), _L8("Futtesesoxok")),
sl@0: 		TPnName(_L8("Dor"), _L8("Lelek")),
sl@0: 		TPnName(_L8("Matin"), _L8("Fure")),
sl@0: 		TPnName(_L8("Niasietolf"), _L8("Jonones")),
sl@0: 		TPnName(_L8("Das"), _L8("Hoeonds")),
sl@0: 		TPnName(_L8("Anchn"), _L8("Svss")),
sl@0: 		TPnName(_L8("Dor"), _L8("Bolunatrk")),
sl@0: 		TPnName(_L8("Casah"), _L8("Brilllundonsssoug")),
sl@0: 		TPnName(_L8("Iapew"), _L8("Bagukak")),
sl@0: 		TPnName(_L8("Lieni"), _L8("MoncNicel")),
sl@0: 		TPnName(_L8("Adewalyary"), _L8("Buradesorobbrerans")),
sl@0: 		TPnName(_L8("Tos"), _L8("Gis")),
sl@0: 		TPnName(_L8("Vi"), _L8("Berk")),
sl@0: 		TPnName(_L8("Jorya"), _L8("upmarone")),
sl@0: 		TPnName(_L8("Iatew"), _L8("Hend")),
sl@0: 		TPnName(_L8("Liag"), _L8("Brsmall")),
sl@0: 		TPnName(_L8("Al"), _L8("Spahay")),
sl@0: 		TPnName(_L8("El"), _L8("Sy")),
sl@0: 		TPnName(_L8("Pary"), _L8("Trl")),
sl@0: 		TPnName(_L8("Br"), _L8("Usouroneis")),
sl@0: 		TPnName(_L8("Sirnilly"), _L8("Olay")),
sl@0: 		TPnName(_L8("Fell"), _L8("Bouphies")),
sl@0: 		TPnName(_L8("Man"), _L8("Haz")),
sl@0: 		TPnName(_L8("Dare"), _L8("Was")),
sl@0: 		TPnName(_L8("Fahnahopephrtex"), _L8("Gat")),
sl@0: 		TPnName(_L8("Har"), _L8("Handfffebinneickiasse")),
sl@0: 		TPnName(_L8("Gerlai"), _L8("Boravirg")),
sl@0: 		TPnName(_L8("Miss"), _L8("Us")),
sl@0: 		TPnName(_L8("Caushatattoatot"), _L8("Wes")),
sl@0: 		TPnName(_L8("Eizicay"), _L8("Gunbss")),
sl@0: 		TPnName(_L8("Pan"), _L8("Hilesertatickesobss-")),
sl@0: 		TPnName(_L8("Anaw"), _L8("Mangar")),
sl@0: 		TPnName(_L8("Korba"), _L8("Siansolan")),
sl@0: 		TPnName(_L8("Darl"), _L8("Haginijelso")),
sl@0: 		TPnName(_L8("Ral"), _L8("Veddddkisocackeluisowowone")),
sl@0: 		TPnName(_L8("La"), _L8("Wawethl")),
sl@0: 		TPnName(_L8("Y"), _L8("Wisonkend")),
sl@0: 		TPnName(_L8("Evimiat"), _L8("JondepssooncClille")),
sl@0: 		TPnName(_L8("Rin"), _L8("DulatoliacKark")),
sl@0: 		TPnName(_L8("Shegeiew"), _L8("Ass"))
sl@0: 	};
sl@0: 	
sl@0: /////////////////////////////////////////////////////////////
sl@0: //  16-bit strings
sl@0: /////////////////////////////////////////////////////////////
sl@0: 
sl@0: static TInt CmpF16(void*, TInt size1, const void* p1, TInt size2, const void* p2)
sl@0: 	{
sl@0: 	++TheCmpFCallCnt;
sl@0: 	
sl@0: 	TPtrC16 ptr1((TUint16*)p1, size1/2);
sl@0: 	TPtrC16 ptr2((TUint16*)p2, size2/2);
sl@0: 	
sl@0: 	TInt res = ptr1.CompareF(ptr2);
sl@0: 	return res;		
sl@0: 	}
sl@0: 
sl@0: static TInt CmpC16(void*, TInt size1, const void* p1, TInt size2, const void* p2)
sl@0: 	{
sl@0: 	++TheCmpCCallCnt;
sl@0: 	
sl@0: 	TPtrC16 ptr1((TUint16*)p1, size1/2);
sl@0: 	TPtrC16 ptr2((TUint16*)p2, size2/2);
sl@0: 	TInt res = ptr1.CompareC(ptr2);
sl@0: 	return res;		
sl@0: 	}
sl@0: 
sl@0: static void SearchDbTest16(const TDesC& aDbFilePath)
sl@0: 	{
sl@0: 	TheFs.Delete(aDbFilePath);
sl@0: 	
sl@0: 	TBuf<KMaxFileName + 1> fname;
sl@0: 	fname.Copy(aDbFilePath);
sl@0: 	//Open database
sl@0: 	RDebug::Print(_L("Open database\r\n"));
sl@0: 	sqlite3 *dbHandle = NULL;
sl@0: 	TInt rc = sqlite3_open16(fname.PtrZ(), &dbHandle);//!!!!16-bit encoding!!!!!
sl@0: 	if(rc != SQLITE_OK)
sl@0: 		{
sl@0: 		const void* errMsgZ = sqlite3_errmsg16(dbHandle);
sl@0: 		TPtrC msg(reinterpret_cast <const TText16*> (errMsgZ), wcslen(reinterpret_cast <const wchar_t*> (errMsgZ)));
sl@0: 		RDebug::Print(_L("'sqlite3_open()' failed, file %S, err %d, error msg: \"%S\"\t\n"), &aDbFilePath, rc, &msg);
sl@0: 		TEST(0);
sl@0: 		}
sl@0: 	TEST(dbHandle != NULL);
sl@0: 	//Create "CompareF" collation	
sl@0: 	RDebug::Print(_L("Create \"CompareF\" collation\r\n"));
sl@0: 	_LIT(KCmpF, "CmpF16\x0");
sl@0: 	rc = sqlite3_create_collation16(dbHandle, (const char*)(KCmpF().Ptr()), SQLITE_UTF16 | SQLITE_UTF16_ALIGNED, NULL, &CmpF16);
sl@0:   	if(rc)
sl@0:   		{
sl@0: 		const void* errMsgZ = sqlite3_errmsg16(dbHandle);
sl@0: 		TPtrC msg(reinterpret_cast <const TText16*> (errMsgZ), wcslen(reinterpret_cast <const wchar_t*> (errMsgZ)));
sl@0: 		RDebug::Print(_L("Err=%S\r\n"), &msg);
sl@0: 		TEST(0);
sl@0:   		}
sl@0: 	//Create "CompareC" collation	
sl@0: 	RDebug::Print(_L("Create \"CompareC\" collation\r\n"));
sl@0: 	_LIT(KCmpC, "CmpC16\x0");
sl@0: 	rc = sqlite3_create_collation16(dbHandle, (const char*)(KCmpC().Ptr()), SQLITE_UTF16 | SQLITE_UTF16_ALIGNED, NULL, &CmpC16);
sl@0:   	if(rc)
sl@0:   		{
sl@0: 		const void* errMsgZ = sqlite3_errmsg16(dbHandle);
sl@0: 		TPtrC msg(reinterpret_cast <const TText16*> (errMsgZ), wcslen(reinterpret_cast <const wchar_t*> (errMsgZ)));
sl@0: 		RDebug::Print(_L("Err=%S\r\n"), &msg);
sl@0: 		TEST(0);
sl@0:   		}
sl@0: 	//Create database schema
sl@0: 	TheCmpFCallCnt = TheCmpCCallCnt = 0;
sl@0: 	RDebug::Print(_L("Create database schema\r\n"));
sl@0: 	HBufC16* createSqlZ = ReadSQL16(_L("z:\\test\\contacts_schema_to_vendors.sql"));
sl@0: 	TPtr sql = createSqlZ->Des();
sl@0: 	ExecSql16(dbHandle, sql, _L("Create schema"));
sl@0: 	delete createSqlZ;
sl@0: 	RDebug::Print(_L("CmpF() call cnt %d, CmpC() call cnt %d\r\n"), TheCmpFCallCnt, TheCmpCCallCnt);
sl@0: 	//Add 1001 "simple" contacts
sl@0: 	TheCmpFCallCnt = TheCmpCCallCnt = 0;
sl@0: 	RDebug::Print(_L("Add 1001 \"simple\" contacts\r\n"));
sl@0: 	HBufC16* addSqlZ = ReadSQL16(KSimpleContactsSqlFile);
sl@0: 	RDebug::Print(_L("--\r\n"));
sl@0: 	sql.Set(addSqlZ->Des());
sl@0: 	ExecSql16(dbHandle, sql, _L("Add simple contacts"));
sl@0: 	delete addSqlZ;
sl@0: 	RDebug::Print(_L("CmpF() call cnt %d, CmpC() call cnt %d\r\n"), TheCmpFCallCnt, TheCmpCCallCnt);
sl@0: 	//Print the number of records
sl@0: 	RDebug::Print(_L("Print the number of records\r\n"));
sl@0: 	TBuf<40> testSql(_L("SELECT COUNT(*) FROM CONTACTS"));
sl@0: 	testSql.Append(TChar(0));
sl@0: 	ExecSql16(dbHandle, testSql, _L("--"));
sl@0: 	
sl@0: 	//Create index: "First name, Last name"
sl@0: 	TheCmpFCallCnt = TheCmpCCallCnt = 0;
sl@0: 	RDebug::Print(_L("Create index: \"First name, Last name\"\r\n"));
sl@0: 	TBuf<100> createIndexStmt(_L("CREATE INDEX Idx1 ON identitytable(cm_firstname COLLATE CmpC16, cm_lastname COLLATE CmpC16)"));
sl@0: 	createIndexStmt.Append(TChar(0));
sl@0: 	ExecSql16(dbHandle, createIndexStmt, _L("Create index"));
sl@0: 	RDebug::Print(_L("CmpF() call cnt %d, CmpC() call cnt %d\r\n"), TheCmpFCallCnt, TheCmpCCallCnt);
sl@0: 
sl@0: 	RDebug::Print(_L("Close database\r\n"));
sl@0:    	sqlite3_close(dbHandle);
sl@0: 
sl@0: 	PrintFileSize(aDbFilePath);
sl@0: 	}
sl@0: 
sl@0: /////////////////////////////////////////////////////////////
sl@0: //  8-bit strings
sl@0: /////////////////////////////////////////////////////////////
sl@0: 
sl@0: static TInt CmpF8(void*, TInt size1, const void* p1, TInt size2, const void* p2)
sl@0: 	{
sl@0: 	++TheCmpFCallCnt;
sl@0: 	TPtrC8 ptr1((TUint8*)p1, size1);
sl@0: 	TPtrC8 ptr2((TUint8*)p2, size2);
sl@0: 	
sl@0: 	TInt res = ptr1.CompareF(ptr2);
sl@0: 	return res;		
sl@0: 	}
sl@0: 
sl@0: static TInt CmpC8(void*, TInt size1, const void* p1, TInt size2, const void* p2)
sl@0: 	{
sl@0: 	TPtrC8 ptr1((TUint8*)p1, size1);
sl@0: 	TPtrC8 ptr2((TUint8*)p2, size2);
sl@0: 	TInt res = ptr1.CompareC(ptr2);
sl@0: 	return res;		
sl@0: 	}
sl@0: 
sl@0: static void SearchDbTest8(const TDesC& aDbFilePath, const TDesC& aAddContactsFile, const TDesC& aMsg)
sl@0: 	{
sl@0: 	TheFs.Delete(aDbFilePath);
sl@0: 	
sl@0: 	TBuf8<KMaxFileName + 1> fname;
sl@0: 	fname.Copy(aDbFilePath);
sl@0: 	
sl@0: 	RDebug::Print(_L("%S\r\n"), &aMsg);
sl@0: 	RDebug::Print(_L("Open database\r\n"));
sl@0: 	sqlite3 *dbHandle = NULL;
sl@0: 	TInt rc = sqlite3_open((const char*)fname.PtrZ(), &dbHandle);
sl@0: 	if(rc != SQLITE_OK)
sl@0: 		{
sl@0: 		const void* errMsgZ = sqlite3_errmsg16(dbHandle);
sl@0: 		TPtrC msg(reinterpret_cast <const TText16*> (errMsgZ), wcslen(reinterpret_cast <const wchar_t*> (errMsgZ)));
sl@0: 		RDebug::Print(_L("'sqlite3_open()' failed, file %S, err %d, error msg: \"%S\"\r\n"), &aDbFilePath, rc, &msg);
sl@0: 		TEST(0);
sl@0: 		}
sl@0: 	TEST(dbHandle != NULL);
sl@0: 	
sl@0: 	RDebug::Print(_L("Create 'CompareF' collation\r\n"));
sl@0: 	_LIT8(KCmpF, "CmpF8\x0");
sl@0: 	rc = sqlite3_create_collation(dbHandle, (const char*)(KCmpF().Ptr()), SQLITE_UTF8, NULL, &CmpF8);
sl@0:   	if(rc)
sl@0:   		{
sl@0: 		const void* errMsgZ = sqlite3_errmsg16(dbHandle);
sl@0: 		TPtrC msg(reinterpret_cast <const TText16*> (errMsgZ), wcslen(reinterpret_cast <const wchar_t*> (errMsgZ)));
sl@0: 		RDebug::Print(_L("'sqlite3_create_collation()' failed, file %S, err %d, error msg: \"%S\"\r\n"), &aDbFilePath, rc, &msg);
sl@0: 		TEST(0);
sl@0:   		}
sl@0: 
sl@0: 	RDebug::Print(_L("Create 'CompareC' collation\r\n"));
sl@0: 	_LIT8(KCmpC, "CmpC8\x0");
sl@0: 	rc = sqlite3_create_collation(dbHandle, (const char*)(KCmpC().Ptr()), SQLITE_UTF8, NULL, &CmpC8);
sl@0:   	if(rc)
sl@0:   		{
sl@0: 		const void* errMsgZ = sqlite3_errmsg16(dbHandle);
sl@0: 		TPtrC msg(reinterpret_cast <const TText16*> (errMsgZ), wcslen(reinterpret_cast <const wchar_t*> (errMsgZ)));
sl@0: 		RDebug::Print(_L("'sqlite3_create_collation()' failed, file %S, err %d, error msg: \"%S\"\r\n"), &aDbFilePath, rc, &msg);
sl@0: 		TEST(0);
sl@0:   		}
sl@0: 
sl@0: 	RDebug::Print(_L("Create database schema\r\n"));
sl@0: 	char* createSqlZ = ReadSQL2(_L("z:\\test\\contacts_schema_to_vendors.sql"));
sl@0: 	ExecSql(dbHandle, createSqlZ, _L("Create schema"));
sl@0: 	delete [] createSqlZ;
sl@0: 
sl@0: 	RDebug::Print(_L("Add 1001 contacts\r\n"));
sl@0: 	char* addSqlZ = ReadSQL2(aAddContactsFile);
sl@0: 	ExecSql(dbHandle, addSqlZ, _L("Add contacts"));
sl@0: 	delete [] addSqlZ;
sl@0: 
sl@0: 	RDebug::Print(_L("Print the number of records\r\n"));
sl@0: 	const char testSql[] = {"SELECT COUNT(*) FROM CONTACTS"};
sl@0: 	ExecSql(dbHandle, testSql, _L("SELECT COUNT(*)"));
sl@0: 	
sl@0: 	RDebug::Print(_L("Create index (using 'CompareF' collation): 'FirstName, Surname'\r\n"));
sl@0: 	_LIT8(KCreateIndexStmt, "CREATE INDEX Idx1 ON identitytable(cm_firstname COLLATE CmpF8, cm_lastname COLLATE CmpF8)\x0");
sl@0: 	ExecSql(dbHandle, (const char*)(KCreateIndexStmt().Ptr()), _L("Create index"));
sl@0: 	RDebug::Print(_L("CompareF() called %d times\r\n"), TheCmpFCallCnt);
sl@0: 
sl@0: /* BEGIN OF - TEST CASE 1 "Select all contacts which first name begins with 'a' " */	
sl@0: 
sl@0: 	RDebug::Print(_L("Prepare 'Select all contacts where the first name begins with 'A'' SQL string\r\n"));
sl@0: 	_LIT8(KSearchStmt, "SELECT cm_firstname, cm_lastname FROM identitytable WHERE cm_firstname LIKE 'A%'\x0");
sl@0: 	sqlite3_stmt* stmtHandle = NULL;
sl@0: 	const char* stmtTailZ = NULL;
sl@0: 	TTime t1;
sl@0: 	t1.UniversalTime();
sl@0: 	rc = sqlite3_prepare_v2(dbHandle, (const char*)(KSearchStmt().Ptr()), -1, &stmtHandle, &stmtTailZ);
sl@0: 	TEST2(rc, SQLITE_OK);
sl@0: 	TEST(stmtHandle != NULL);
sl@0: 	TEST(!stmtTailZ || strlen(stmtTailZ) == 0);
sl@0: 	TTime t2;
sl@0: 	t2.UniversalTime();
sl@0: 	TTimeIntervalMicroSeconds diffTime = t2.MicroSecondsFrom(t1); 
sl@0: 	diffTime = diffTime.Int64() / 1000;
sl@0: 	TInt t = (TInt)diffTime.Int64();
sl@0: 	RDebug::Print(_L("'Prepare SQL statement' time: %d ms\r\n"), t);
sl@0: 
sl@0: 	RDebug::Print(_L("Step the prepared SQL statement\r\n"));
sl@0: 	TInt totalCmpFCnt = 0;
sl@0: 	t1.UniversalTime();
sl@0: 	TheCmpFCallCnt = 0;
sl@0: 	TInt recordCnt = 0;
sl@0: 	while((rc = sqlite3_step(stmtHandle)) == SQLITE_ROW)
sl@0: 		{
sl@0: 		//const TUint8* firstName = sqlite3_column_text(stmtHandle, 0);
sl@0: 		//TPtrC8 p(firstName, strlen((const char*)firstName));
sl@0: 		//TBuf<100> p1; p1.Copy(p);
sl@0: 		//const TUint8* surname = sqlite3_column_text(stmtHandle, 1);
sl@0: 		//p.Set(surname, strlen((const char*)surname));
sl@0: 		//TBuf<100> p2; p2.Copy(p);
sl@0: 		//RDebug::Print(_L("Found rec: %S, %S\r\n"), &p1, &p2);
sl@0: 		++recordCnt;
sl@0: 		}
sl@0: 	totalCmpFCnt += TheCmpFCallCnt;
sl@0: 	TEST(rc == SQLITE_OK || rc == SQLITE_DONE);
sl@0: 	t2.UniversalTime();
sl@0: 	diffTime = t2.MicroSecondsFrom(t1); 
sl@0: 	diffTime = diffTime.Int64() / 1000;
sl@0: 	t = (TInt)diffTime.Int64();
sl@0: 	RDebug::Print(_L("'Stepping' time: %d ms, found records: %d\r\n"), t, recordCnt);
sl@0: 	RDebug::Print(_L("Total 'search' ('CompareF' used) operations=%d\r\n"), totalCmpFCnt);
sl@0: 
sl@0: 	sqlite3_finalize(stmtHandle);
sl@0: 	stmtHandle = NULL;
sl@0: 
sl@0: /* END OF - TEST CASE 1 "Select all contacts which first name begins with 'a' " */	
sl@0: 
sl@0: /*	BEGIN OF - TEST CASE 2 "Do 100 searches in 1001 contacts" */
sl@0: 
sl@0: 	RDebug::Print(_L("Prepare 'SELECT FirstName, Surname...' SQL string\r\n"));
sl@0: 	_LIT8(KSearchStmt2, "SELECT cm_firstname, cm_lastname FROM identitytable WHERE cm_firstname = :Prm1 AND cm_lastname = :Prm2\x0");
sl@0: 	stmtHandle = NULL;
sl@0: 	stmtTailZ = NULL;
sl@0: 	t1.UniversalTime();
sl@0: 	rc = sqlite3_prepare_v2(dbHandle, (const char*)(KSearchStmt2().Ptr()), -1, &stmtHandle, &stmtTailZ);
sl@0: 	TEST2(rc, SQLITE_OK);
sl@0: 	TEST(stmtHandle != NULL);
sl@0: 	TEST(!stmtTailZ || strlen(stmtTailZ) == 0);
sl@0: 	t2.UniversalTime();
sl@0: 	diffTime = t2.MicroSecondsFrom(t1); 
sl@0: 	diffTime = diffTime.Int64() / 1000;
sl@0: 	t = (TInt)diffTime.Int64();
sl@0: 	RDebug::Print(_L("'Prepare SQL statement' time: %d ms\r\n"), t);
sl@0: 	
sl@0: 	TInt idxPrm1 = sqlite3_bind_parameter_index(stmtHandle, ":Prm1");
sl@0: 	TEST(idxPrm1 > 0);
sl@0: 	TInt idxPrm2 = sqlite3_bind_parameter_index(stmtHandle, ":Prm2");
sl@0: 	TEST(idxPrm2 > 0);
sl@0: 	
sl@0: 	RDebug::Print(_L("Do %d searches using the prepared SQL statement\r\n"), KNamesCnt);
sl@0: 	totalCmpFCnt = 0;
sl@0: 	t1.UniversalTime();
sl@0: 	for(TInt i=0;i<KNamesCnt;++i)
sl@0: 		{
sl@0: 		const TDesC8& firstName = KNames[i].iFirstName;
sl@0: 		rc = sqlite3_bind_text(stmtHandle, idxPrm1, (const char*)firstName.Ptr(), firstName.Length(), SQLITE_STATIC);
sl@0: 		TEST2(rc, SQLITE_OK);
sl@0: 		
sl@0: 		const TDesC8& surname = KNames[i].iSurname;
sl@0: 		rc = sqlite3_bind_text(stmtHandle, idxPrm2, (const char*)surname.Ptr(), surname.Length(), SQLITE_STATIC);
sl@0: 		TEST2(rc, SQLITE_OK);
sl@0: 		
sl@0: 		TheCmpFCallCnt = 0;
sl@0: 		TInt recordCnt = 0;
sl@0: 		while((rc = sqlite3_step(stmtHandle)) == SQLITE_ROW)
sl@0: 			{
sl@0: 			++recordCnt;
sl@0: 			}
sl@0: 		totalCmpFCnt += TheCmpFCallCnt;
sl@0: 		TEST(recordCnt == 1);
sl@0: 		rc = sqlite3_reset(stmtHandle);
sl@0: 		TEST2(rc, SQLITE_OK);
sl@0: 		}
sl@0: 	t2.UniversalTime();
sl@0: 	diffTime = t2.MicroSecondsFrom(t1); 
sl@0: 	diffTime = diffTime.Int64() / 1000;
sl@0: 	t = (TInt)diffTime.Int64();
sl@0: 	RDebug::Print(_L("'Search' time: %d ms\r\n"), t);
sl@0: 	RDebug::Print(_L("Total 'search' ('CompareF' used) operations=%d, average_per_iter=%d\r\n"), totalCmpFCnt, totalCmpFCnt/KNamesCnt);
sl@0: 
sl@0: 	sqlite3_finalize(stmtHandle);
sl@0: 	stmtHandle = NULL;
sl@0: 	
sl@0: /* END OF - TEST CASE 2 "Do 100 searches in 1001 contacts" */	
sl@0: 
sl@0: 	t1.UniversalTime();
sl@0: 	sqlite3_finalize(stmtHandle);
sl@0: 	t2.UniversalTime();
sl@0: 	diffTime = t2.MicroSecondsFrom(t1); 
sl@0: 	diffTime = diffTime.Int64() / 1000;
sl@0: 	t = (TInt)diffTime.Int64();
sl@0: 	RDebug::Print(_L("'Finalize SQL statement' time: %d ms\r\n"), t);
sl@0: 
sl@0:    	sqlite3_close(dbHandle);
sl@0: 	PrintFileSize(aDbFilePath);
sl@0: 	}
sl@0: 
sl@0: static void TwoDatabasesTest()
sl@0: 	{
sl@0: 	(void)sqlite3SymbianLastOsError();
sl@0: 	sqlite3 *dbHandle1 = NULL;
sl@0: 	TInt rc1 = sqlite3_open(KDbName1, &dbHandle1);
sl@0: 	if(rc1 != SQLITE_OK)
sl@0: 		{
sl@0: 		rc1 = sqlite3SymbianLastOsError();
sl@0: 		}
sl@0: 
sl@0: 	(void)sqlite3SymbianLastOsError();
sl@0: 	sqlite3 *dbHandle2 = NULL;
sl@0: 	TInt rc2 = sqlite3_open(KDbName1, &dbHandle2);
sl@0: 	if(rc2 != SQLITE_OK)
sl@0: 		{
sl@0: 		rc2 = sqlite3SymbianLastOsError();
sl@0: 		}
sl@0: 	
sl@0:    	sqlite3_close(dbHandle2);
sl@0: 
sl@0:    	sqlite3_close(dbHandle1);
sl@0: 	
sl@0: 	(void)TheFs.Delete(ConvertToUtf16(KDbName1));
sl@0: 	
sl@0: 	TEST2(rc1, KErrNone);
sl@0: 	TEST2(rc2, KErrInUse);//EFileRead | EFileWrite file open mode!
sl@0: 	}
sl@0: 
sl@0: static void QuickTest()
sl@0: 	{
sl@0: 	(void)TheFs.Delete(ConvertToUtf16(KDbName1));
sl@0: 
sl@0: 	sqlite3 *dbHandle1 = NULL;
sl@0: 	TInt err = sqlite3_open(KDbName1, &dbHandle1);
sl@0: 	TEST2(err, SQLITE_OK);
sl@0: 
sl@0: 	err = sqlite3_exec(dbHandle1, "CREATE TABLE A(Fld1 INTEGER, Fld2 TEXT);", 0, 0, 0);
sl@0: 	TEST2(err, SQLITE_OK);
sl@0: 
sl@0: 	sqlite3_stmt* stmtHandle = NULL;
sl@0: 	_LIT(KSql1, "INSERT INTO A(Fld1, Fld2) VALUES(1, :Val)\x0");
sl@0: 	err = sqlite3_prepare16_v2(dbHandle1, KSql1().Ptr(), -1, &stmtHandle, NULL);
sl@0: 	TEST2(err, SQLITE_OK);
sl@0: 
sl@0: 	_LIT(KPrmData, "U012");
sl@0: 	err = sqlite3_bind_text16(stmtHandle, 1, (const void*)KPrmData().Ptr(), KPrmData().Length() * sizeof(TText), SQLITE_STATIC);
sl@0: 	TEST2(err, SQLITE_OK);
sl@0: 
sl@0: 	while((err = sqlite3_step(stmtHandle)) == SQLITE_ROW)
sl@0: 		{
sl@0: 		}
sl@0: 	if(err == SQLITE_ERROR)	//It may be "out of memory" problem
sl@0: 		{
sl@0: 		err = sqlite3_reset(stmtHandle);
sl@0: 		__ASSERT_ALWAYS(err != SQLITE_OK, User::Invariant());
sl@0: 		}
sl@0: 
sl@0:    	sqlite3_finalize(stmtHandle);
sl@0:    	
sl@0: 	_LIT(KSql2, "SELECT * FROM A WHERE Fld1 = 1\x0");
sl@0: 	err = sqlite3_prepare16_v2(dbHandle1, KSql2().Ptr(), -1, &stmtHandle, NULL);
sl@0: 	TEST2(err, SQLITE_OK);
sl@0: 
sl@0: 	err = sqlite3_step(stmtHandle);
sl@0: 	TEST2(err, SQLITE_ROW);
sl@0: 
sl@0: 	const void* data = sqlite3_column_text16(stmtHandle, 1);
sl@0: 	UNUSED_VAR(data);
sl@0: 	TInt charLength  = sqlite3_column_bytes16(stmtHandle, 1) / sizeof(TUint16);
sl@0: 	UNUSED_VAR(charLength);
sl@0:    	
sl@0:    	sqlite3_finalize(stmtHandle);
sl@0:    	
sl@0:    	sqlite3_close(dbHandle1);
sl@0: 
sl@0: 	(void)TheFs.Delete(ConvertToUtf16(KDbName1));
sl@0: 	}
sl@0: 
sl@0: //////////////////////////////////////////////////////////////////////////////////////////////////
sl@0: 
sl@0: static void TemdDbTest()
sl@0: 	{
sl@0: 	sqlite3 *dbHandle = NULL;
sl@0: 	TInt rc = sqlite3_open(KDbName1, &dbHandle);
sl@0: 	if(rc != SQLITE_OK)
sl@0: 		{
sl@0: 		const void* errMsgZ = sqlite3_errmsg16(dbHandle);
sl@0: 		TPtrC msg(reinterpret_cast <const TText16*> (errMsgZ), wcslen(reinterpret_cast <const wchar_t*> (errMsgZ)));
sl@0: 		RDebug::Print(_L("'sqlite3_open()' failed, err %d, error msg: \"%S\"\r\n"), rc, &msg);
sl@0: 		TEST(0);
sl@0: 		}
sl@0: 	TEST(dbHandle != NULL);
sl@0: 
sl@0: 	char *zErrMsg = 0;
sl@0: 
sl@0: 	_LIT8(KSql1, "CREATE TEMP TABLE A(F1 INTEGER)\x0");
sl@0: 	rc = sqlite3_exec(dbHandle, reinterpret_cast <const char*> (KSql1().Ptr()), callback, 0, &zErrMsg);
sl@0: 	if(rc != SQLITE_OK)
sl@0: 		{
sl@0:   		TPtrC p = ConvertToUtf16(zErrMsg);
sl@0:   		RDebug::Print(_L("SQL error %d, msg: %S\n"), rc, &p);
sl@0:     	sqlite3_free(zErrMsg);
sl@0:     	TEST(0);
sl@0: 		}
sl@0: 
sl@0: 	_LIT8(KSql2, "INSERT INTO A(F1) VALUES(2)\x0");
sl@0: 	rc = sqlite3_exec(dbHandle, reinterpret_cast <const char*> (KSql2().Ptr()), callback, 0, &zErrMsg);
sl@0: 	if(rc != SQLITE_OK)
sl@0: 		{
sl@0:   		TPtrC p = ConvertToUtf16(zErrMsg);
sl@0:   		RDebug::Print(_L("SQL error %d, msg: %S\n"), rc, &p);
sl@0:     	sqlite3_free(zErrMsg);
sl@0:     	TEST(0);
sl@0: 		}
sl@0: 
sl@0:    	sqlite3_close(dbHandle);
sl@0: 	(void)TheFs.Delete(ConvertToUtf16(KDbName1));
sl@0: 	}
sl@0: 
sl@0: //////////////////////////////////////////////////////////////////////////////////////////////////
sl@0: //////////////////////////////////////////////////////////////////////////////////////////////////
sl@0: //////////////////////////////////////////////////////////////////////////////////////////////////
sl@0: 
sl@0: static void CreateTestDir()
sl@0:     {
sl@0:     RFs fs;
sl@0: 	TInt err = fs.Connect();
sl@0: 	TEST2(err, KErrNone);
sl@0: 
sl@0: 	err = fs.MkDir(KTestDir);
sl@0: 	TEST(err == KErrNone || err == KErrAlreadyExists);
sl@0: 	
sl@0: 	fs.Close();
sl@0: 	}
sl@0: 
sl@0: static void DoTestsL()
sl@0: 	{
sl@0: 	TheTest.Next(_L("Attempt to open twice the same database file in the same thread"));
sl@0: 	TwoDatabasesTest();
sl@0: 
sl@0: 	TheTest.Next(_L("TEMP database test"));
sl@0: 	TemdDbTest();
sl@0: 
sl@0: 	TheTest.Next(_L("Quick test"));
sl@0: 	QuickTest();
sl@0: 
sl@0: 	TheTest.Next(_L("SQLite tests 1"));
sl@0: 	DoTests1();
sl@0: 	
sl@0: 	TheTest.Next(_L("SQLite tests 2"));
sl@0: 	DoTests2();
sl@0: 
sl@0: 	TheTest.Next(_L("Accented column names"));
sl@0: 	AccentedColumnNamesTestL();
sl@0: 	
sl@0: 	TFileName fname;
sl@0: 	User::CommandLine(fname);
sl@0: 	TParse parse;
sl@0: 	parse.Set(fname, &KContactsFile, 0);
sl@0: 	const TDesC& dbFilePath = parse.FullName();
sl@0: 	
sl@0: 	PrintConfig(dbFilePath);
sl@0: 
sl@0: 	TheTest.Next(_L("String searching tests. 16-bit strings. Simple contacts"));
sl@0: 	SearchDbTest16(dbFilePath);
sl@0: 
sl@0: 	TheTest.Next(_L("String searching tests. 8-bit strings. Simple contacts"));
sl@0: 	SearchDbTest8(dbFilePath, KSimpleContactsSqlFile, _L("Search simple contacts"));
sl@0: 	}
sl@0: 
sl@0: TInt E32Main()
sl@0: 	{
sl@0: 	TheTest.Title();
sl@0: 	TheTest.Start(_L(" @SYMTestCaseID:SYSLIB-SQL-LEGACY-T_SQLITE-0001 SQLite tests "));
sl@0: 	
sl@0: 	TheTrapCleanup = CTrapCleanup::New ();
sl@0: 	__ASSERT_ALWAYS(TheTrapCleanup != NULL, User::Invariant());
sl@0: 
sl@0: 	__UHEAP_MARK;
sl@0: 
sl@0: 	CreateTestDir();
sl@0: 	
sl@0: 	TheTest(TheFs.Connect() == KErrNone);
sl@0: 	DeleteTestFiles();
sl@0: 
sl@0: 	CreatePrivateDirs();
sl@0: 
sl@0: 	//Init sqlite library
sl@0: 	sqlite3SymbianLibInit();
sl@0: 
sl@0: 	TRAPD(err, DoTestsL());
sl@0: 	
sl@0: 	sqlite3SymbianLibFinalize();
sl@0: 
sl@0: 	CloseSTDLIB();
sl@0: 	
sl@0: 	DeleteTestFiles();
sl@0: 	
sl@0: 	TEST2(err, KErrNone);
sl@0: 	
sl@0: 	TheFs.Close();
sl@0: 
sl@0: 	__UHEAP_MARKEND;
sl@0: 
sl@0: 	delete TheTrapCleanup;
sl@0: 
sl@0: 
sl@0: 	TheTest.End();
sl@0: 	TheTest.Close();
sl@0: 
sl@0: 
sl@0: 	return KErrNone;
sl@0: 	}