sl@0: // Copyright (c) 2007-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 <e32debug.h>
sl@0: #include <e32test.h>
sl@0: #include <f32file.h>
sl@0: #include "sqlite3.h"
sl@0: #include <stdlib.h>
sl@0: #include "SqliteSymbian.h"
sl@0: #include "SqlAssert.h"
sl@0: 
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: 
sl@0: RTest TheTest(_L("t_SqlGetFirstStmt test"));
sl@0: RFs TheFs;
sl@0: sqlite3* TheSqliteDb;
sl@0: 
sl@0: _LIT(KTestDir, "c:\\test\\");
sl@0: _LIT(KTestDatabase, "c:\\test\\DEF104744.db");
sl@0: _LIT8(KTestDatabaseZ, "c:\\test\\DEF104744.db\x0");
sl@0: 
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: 
sl@0: //Deletes all created test files.
sl@0: void DestroyTestEnv()
sl@0: 	{
sl@0: 	if(TheSqliteDb)
sl@0: 		{
sl@0: 		sqlite3_close(TheSqliteDb);	
sl@0: 		TheSqliteDb = NULL;
sl@0: 		}
sl@0: 	(void)TheFs.Delete(KTestDatabase);
sl@0: 	TheFs.Close();
sl@0: 	}
sl@0: 
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: //Test macros and functions
sl@0: void Check1(TInt aValue, TInt aLine)
sl@0: 	{
sl@0: 	if(!aValue)
sl@0: 		{
sl@0: 		DestroyTestEnv();
sl@0: 		RDebug::Print(_L("*** Line %d\r\n"), aLine);
sl@0: 		TheTest(EFalse, aLine);
sl@0: 		}
sl@0: 	}
sl@0: void Check2(TInt aValue, TInt aExpected, TInt aLine)
sl@0: 	{
sl@0: 	if(aValue != aExpected)
sl@0: 		{
sl@0: 		DestroyTestEnv();
sl@0: 		RDebug::Print(_L("*** Line %d, Expected error: %d, got: %d\r\n"), aLine, aExpected, aValue);
sl@0: 		TheTest(EFalse, aLine);
sl@0: 		}
sl@0: 	}
sl@0: #define TEST(arg) ::Check1((arg), __LINE__)
sl@0: #define TEST2(aValue, aExpected) ::Check2(aValue, aExpected, __LINE__)
sl@0: 
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: 
sl@0: extern TPtrC GetFirstSqlStmt(TPtr& aString);
sl@0: 
sl@0: #define PTR_ARG(buf) const_cast <TUint16*> (buf.Ptr()), buf.Length(), buf.Length()
sl@0: 
sl@0: /**
sl@0: @SYMTestCaseID			SYSLIB-SQL-CT-1628
sl@0: @SYMTestCaseDesc		GetFirstSqlStmt() test
sl@0: 						Tests the GetFirstSqlStmt() behaviour with a set of various SQL statements.
sl@0: @SYMTestPriority		High
sl@0: @SYMTestActions			GetFirstSqlStmt() test
sl@0: @SYMTestExpectedResults Test must not fail
sl@0: @SYMREQ					REQ5792
sl@0:                         REQ5793
sl@0: */	
sl@0: void TestGetFirstSqlStmt()
sl@0: 	{
sl@0: 	TPtrC res;
sl@0: 
sl@0: 	TBuf<1> b2; b2.Append(TChar(0));
sl@0: 	TPtr p2(PTR_ARG(b2));
sl@0: 	res.Set(GetFirstSqlStmt(p2));
sl@0: 	//Expected result: res = "\x0", p2 is NULL
sl@0: 	TEST(res == b2);
sl@0: 	TEST(!p2.Ptr());
sl@0: 		
sl@0: 	TBuf<2> b3; b3.Append(TChar(' ')); b3.Append(TChar(0));
sl@0: 	TPtr p3(PTR_ARG(b3));
sl@0: 	res.Set(GetFirstSqlStmt(p3));
sl@0: 	//Expected result: res = " \x0", p3 is NULL
sl@0: 	TEST(res == b3);
sl@0: 	TEST(!p3.Ptr());
sl@0: 	
sl@0: 	TBuf<7> b4(_L(";; ;  ")); b4.Append(TChar(0));
sl@0: 	TPtr p4(PTR_ARG(b4));
sl@0: 	res.Set(GetFirstSqlStmt(p4));
sl@0: 	//Expected result: res = "\x0", p4 = "; ;  \x0"
sl@0: 	TEST(res.Length() == 1 && (TInt)res[0] == 0);
sl@0: 	TInt accLen = res.Length();
sl@0: 	TEST(p4 == b4.Right(b4.Length() - accLen));
sl@0: 
sl@0: 	res.Set(GetFirstSqlStmt(p4));
sl@0: 	//Expected result: res = "\x0", p4 = " ;  \x0"
sl@0: 	TEST(res.Length() == 1 && (TInt)res[0] == 0);
sl@0: 	accLen += res.Length();
sl@0: 	TEST(p4 == b4.Right(b4.Length() - accLen));
sl@0: 	
sl@0: 	res.Set(GetFirstSqlStmt(p4));
sl@0: 	//Expected result: res = " \x0", p4 = "  \x0"
sl@0: 	TEST((TInt)res[0] == (TInt)TChar(' ') && (TInt)res[1] == 0);
sl@0: 	accLen += res.Length();
sl@0: 	TEST(p4 == b4.Right(b4.Length() - accLen));
sl@0: 	
sl@0: 	res.Set(GetFirstSqlStmt(p4));
sl@0: 	//Expected result: res = "  \x0", p4 is NULL
sl@0: 	TEST((TInt)res[0] == (TInt)TChar(' ') && (TInt)res[1] == (TInt)TChar(' ') && (TInt)res[2] == 0);
sl@0: 	TEST(!p4.Ptr());
sl@0: 	
sl@0: 	TBuf<20> b5(_L("SELECT * FROM A")); b5.Append(TChar(0));
sl@0: 	TPtr p5(PTR_ARG(b5));
sl@0: 	res.Set(GetFirstSqlStmt(p5));
sl@0: 	//Expected result: res = "SELECT * FROM A\x0", p5 is NULL
sl@0: 	TEST(res == b5);
sl@0: 	TEST(!p5.Ptr());
sl@0: 	
sl@0: 	TBuf<20> b6(_L("SELECT * FROM A;")); b6.Append(TChar(0));
sl@0: 	TPtr p6(PTR_ARG(b6));
sl@0: 	res.Set(GetFirstSqlStmt(p6));
sl@0: 	//Expected result: res = "SELECT * FROM A\x0", p6 = "\x0"
sl@0: 	TEST(res == b6.Left(b6.Length() - 1));
sl@0: 	TEST(p6.Length() == 1 && p6[0] == 0);
sl@0: 
sl@0: 	TBuf<40> b7(_L("/** Comment */ SELECT * FROM A;")); b7.Append(TChar(0));
sl@0: 	TPtr p7(PTR_ARG(b7));
sl@0: 	res.Set(GetFirstSqlStmt(p7));
sl@0: 	//Expected result: res = "/** Comment */ SELECT * FROM A\x0", p7 = "\x0"
sl@0: 	TEST(res == b7.Left(b7.Length() - 1));
sl@0: 	TEST(p7.Length() == 1 && p7[0] == 0);
sl@0: 
sl@0: 	TBuf<40> b8(_L(" SELECT * FROM --Comment \r\n A;")); b8.Append(TChar(0));
sl@0: 	TPtr p8(PTR_ARG(b8));
sl@0: 	res.Set(GetFirstSqlStmt(p8));
sl@0: 	//Expected result: res = " SELECT * FROM --Comment \r\n A\x0", p8 = "\x0"
sl@0: 	TEST(res == b8.Left(b8.Length() - 1));
sl@0: 	TEST(p8.Length() == 1 && p8[0] == 0);
sl@0: 
sl@0: 	TBuf<40> b9(_L("SELECT * FROM A; SELECT * FROM B")); b9.Append(TChar(0));
sl@0: 	TPtr p9(PTR_ARG(b9));
sl@0: 	res.Set(GetFirstSqlStmt(p9));
sl@0: 	//Expected result: res = "SELECT * FROM A\x0", p9 = " SELECT * FROM B\x0"
sl@0: 	TEST(res.Left(res.Length() - 1) == b9.Left(res.Length() - 1) && (TInt)res[res.Length() - 1] == 0);
sl@0: 	accLen = res.Length();
sl@0: 	TEST(p9 == b9.Right(b9.Length() - accLen));
sl@0: 
sl@0: 	res.Set(GetFirstSqlStmt(p9));
sl@0: 	//Expected result: res = " SELECT * FROM B\x0", p9 is NULL
sl@0: 	TEST(res == b9.Right(b9.Length() - accLen));
sl@0: 	TEST(!p9.Ptr());
sl@0: 
sl@0: 	//Defect INC113060	
sl@0: 	TBuf<255> b10(_L("UPDATE Playlist SET Name=';',Time='2007-09-20 12:31:33' WHERE UniqueId=640397473"));
sl@0: 	TPtr p10(PTR_ARG(b10));
sl@0: 	res.Set(GetFirstSqlStmt(p10));
sl@0: 	//Expected results: res= original string
sl@0: 	TEST(res.Compare(b10)==0);
sl@0: 	TEST(!p10.Ptr());
sl@0: 	
sl@0: 	TBuf<255> firstStmt(_L("SELECT * FROM PlayList"));firstStmt.Append(TChar(0));
sl@0: 	TBuf<255> b11(_L("SELECT * FROM PlayList;UPDATE Playlist SET Name=';',Time='2007-09-20 12:31:33' WHERE UniqueId=640397473"));
sl@0: 	TPtr p11(PTR_ARG(b11));
sl@0: 	res.Set(GetFirstSqlStmt(p11));
sl@0: 	TEST(res.Compare(firstStmt)==0);
sl@0: 	TEST(p11.Compare(b10)==0);
sl@0: 	}
sl@0: 
sl@0: /**
sl@0: @SYMTestCaseID			SYSLIB-SQL-UT-3433
sl@0: @SYMTestCaseDesc		Test for DEF104744 - RSqlStatement::Next() SQL Server crashes on ORDER BY clause.
sl@0: 						The test creates a database with a table with 30 integer columns, then inserts 100
sl@0: 						records. After that, sets the soft heap limit to be very low - 10K 
sl@0: 						(to get sqlite3_release_memory() called by SQLITE ), creates a statement object 
sl@0: 						and attempts to retrieve the inserted records in descending order.
sl@0: @SYMTestPriority		High
sl@0: @SYMTestActions			Test for DEF104744 - RSqlStatement::Next() SQL Server crashes on ORDER BY clause.
sl@0: @SYMTestExpectedResults Test must not fail
sl@0: @SYMDEF					DEF104744
sl@0: */
sl@0: void DEF104744()
sl@0: 	{
sl@0: 	(void)TheFs.Delete(KTestDatabase);
sl@0: 	TheSqliteDb = NULL;
sl@0: 	TInt err = sqlite3_open((const char*)KTestDatabaseZ().Ptr(), &TheSqliteDb);
sl@0: 	TEST2(err, SQLITE_OK);
sl@0: 	
sl@0: 	_LIT8(KCreateTblSqlZ, "CREATE TABLE A1(F1 INTEGER,F2 INTEGER,F3 INTEGER,F4 INTEGER,F5 INTEGER,F6 INTEGER,F7 INTEGER,F8 INTEGER,F9 INTEGER,F10 INTEGER,F11 INTEGER,F12 INTEGER,F13 INTEGER,F14 INTEGER,F15 INTEGER,F16 INTEGER,F17 INTEGER,F18 INTEGER,F19 INTEGER,F20 INTEGER,F21 INTEGER,F22 INTEGER,F23 INTEGER,F24 INTEGER,F25 INTEGER,F26 INTEGER,F27 INTEGER,F28 INTEGER,F29 INTEGER,F30 INTEGER)\x0");
sl@0: 	err = sqlite3_exec(TheSqliteDb, (const char*)KCreateTblSqlZ().Ptr(), 0, 0, 0);
sl@0: 	TEST2(err, SQLITE_OK);
sl@0: 
sl@0: 	//Insert a 100 records
sl@0: 	const TInt KTestRecCnt = 100;
sl@0: 	_LIT8(KInsertSqlZ, "INSERT INTO A1(F1,F2 ,F3 ,F4 ,F5 ,F6 ,F7 ,F8 ,F9 ,F10,F11,F12,F13,F14,F15,F16,F17,F18,F19,F20,F21,F22,F23,F24,F25,F26,F27,F28,F29,F30) VALUES(:Prm1,:Prm2,4294967296,4294967296,4294967296,4294967296,4294967296,4294967296,4294967296,4294967296,4294967296,4294967296,4294967296,4294967296,4294967296,4294967296,4294967296,4294967296,4294967296,4294967296,4294967296,4294967296,4294967296,4294967296,4294967296,4294967296,4294967296,4294967296,4294967296,4294967296)\x0");
sl@0: 	sqlite3_stmt* stmt1 = NULL;
sl@0: 	err = sqlite3_prepare_v2(TheSqliteDb, (const char*)(KInsertSqlZ().Ptr()), -1, &stmt1, NULL);
sl@0: 	TEST2(err, SQLITE_OK);
sl@0: 	
sl@0: 	_LIT8(KBeginSqlZ, "BEGIN\x0");
sl@0: 	err = sqlite3_exec(TheSqliteDb, (const char*)KBeginSqlZ().Ptr(), 0, 0, 0);
sl@0: 	TEST2(err, SQLITE_OK);
sl@0: 	
sl@0: 	for(TInt i=0;i<KTestRecCnt;++i)
sl@0: 		{ 
sl@0: 		err = sqlite3_bind_int(stmt1, 1, i);
sl@0: 		TEST2(err, SQLITE_OK);
sl@0: 		err = sqlite3_bind_int(stmt1, 2, i + 1);
sl@0: 		TEST2(err, SQLITE_OK);
sl@0: 		err = sqlite3_step(stmt1);
sl@0: 		TEST2(err, SQLITE_DONE);
sl@0: 		err = sqlite3_reset(stmt1);
sl@0: 		TEST2(err, SQLITE_OK);
sl@0: 		TInt cnt = sqlite3_changes(TheSqliteDb);
sl@0: 		TEST2(cnt, 1);
sl@0: 		}
sl@0: 		
sl@0: 	_LIT8(KCommitSqlZ, "COMMIT\x0");
sl@0: 	err = sqlite3_exec(TheSqliteDb, (const char*)KCommitSqlZ().Ptr(), 0, 0, 0);
sl@0: 	TEST2(err, SQLITE_OK);
sl@0: 	sqlite3_finalize(stmt1);
sl@0: 
sl@0: 	sqlite3_soft_heap_limit(10 * 1024);//Setting very low soft heap limit - 10K
sl@0: 
sl@0: 	// Get the inserted record data in descending order.
sl@0: 	sqlite3_stmt* stmt2 = NULL;
sl@0: 	_LIT8(KSelectSqlZ,"SELECT * FROM A1 ORDER BY F1 DESC");
sl@0: 	err = sqlite3_prepare_v2(TheSqliteDb, (const char*)(KSelectSqlZ().Ptr()), -1, &stmt2, NULL);
sl@0: 	TEST2(err, SQLITE_OK);
sl@0: 	err = sqlite3_step(stmt2);
sl@0: 	TEST2(err, SQLITE_ROW);
sl@0: 	sqlite3_finalize(stmt2);
sl@0: 
sl@0: 	sqlite3_close(TheSqliteDb); 
sl@0: 	TheSqliteDb = NULL;
sl@0: 	(void)TheFs.Delete(KTestDatabase);
sl@0: 	}
sl@0: 
sl@0: enum TStmtType {EStmt8, EStmt16};
sl@0: 
sl@0: //This function attempts to execute sqlite3_prepare_v2() or sqlite3_prepare16_v2() in an "out of memory loop".
sl@0: //If the prepare call fails, the statement handle is expected to be NULL.
sl@0: void StmtHandleTest(TStmtType aStmtType)
sl@0: 	{
sl@0: 	TEST(TheSqliteDb != NULL);
sl@0: 	for(TInt failingAllocationNo=1;;++failingAllocationNo)
sl@0: 		{
sl@0: 		__UHEAP_SETFAIL(RHeap::EFailNext, failingAllocationNo);
sl@0: 		sqlite3_stmt* stmt = NULL;
sl@0: 		TInt err = SQLITE_NOMEM;
sl@0: 		if(aStmtType == EStmt8)
sl@0: 			{
sl@0: 			_LIT8(KSelectSqlZ,"SELECT * FROM A\x0");
sl@0: 			err = sqlite3_prepare_v2(TheSqliteDb, (const char*)(KSelectSqlZ().Ptr()), -1, &stmt, NULL);
sl@0: 			}
sl@0: 		else
sl@0: 			{
sl@0: 			_LIT(KSelectSqlZ,"SELECT * FROM A\x0");
sl@0: 			err = sqlite3_prepare16_v2(TheSqliteDb, (const char*)(KSelectSqlZ().Ptr()), -1, &stmt, NULL);
sl@0: 			}
sl@0: 		__UHEAP_SETFAIL(RHeap::ENone, 0);
sl@0: 		if(err != SQLITE_OK)
sl@0: 			{//The statement handle should be NULL if err is not SQLITE_OK
sl@0: 			TEST(!stmt);
sl@0: 			}
sl@0: 		else
sl@0: 			{
sl@0: 			TEST(stmt != NULL);
sl@0: 			sqlite3_finalize(stmt);
sl@0: 			break;
sl@0: 			}
sl@0: 		}
sl@0: 	}
sl@0: 
sl@0: /**
sl@0: @SYMTestCaseID			SYSLIB-SQL-UT-3466
sl@0: @SYMTestCaseDesc		Test for DEF105444 SQL, sqlite3_prepare_v2() demonstrates non-atomic behaviour (handle leak).
sl@0: 						The test creates a database with a table and inserts one record.
sl@0: 						After that the test attempts to prepare a SELECT statement handle inside an "ouut of memory"
sl@0: 						loop. If the prepare operation fails, the statement handle should be NULL.
sl@0: @SYMTestPriority		High
sl@0: @SYMTestActions			DEF105444 SQL, sqlite3_prepare_v2() demonstrates non-atomic behaviour (handle leak) 
sl@0: @SYMTestExpectedResults Test must not fail
sl@0: @SYMDEF					DEF105444
sl@0: */
sl@0: void DEF105444()
sl@0: 	{
sl@0: 	(void)TheFs.Delete(KTestDatabase);
sl@0: 	TheSqliteDb = NULL;
sl@0: 	TInt err = sqlite3_open((const char*)KTestDatabaseZ().Ptr(), &TheSqliteDb);
sl@0: 	TEST2(err, SQLITE_OK);
sl@0: 	
sl@0: 	_LIT8(KCreateTblSqlZ, "CREATE TABLE A(Id INTEGER)\x0");
sl@0: 	err = sqlite3_exec(TheSqliteDb, (const char*)KCreateTblSqlZ().Ptr(), 0, 0, 0);
sl@0: 	TEST2(err, SQLITE_OK);
sl@0: 
sl@0: 	_LIT8(KInsertSqlZ, "INSERT INTO A(Id) VALUES(1)\x0");
sl@0: 	err = sqlite3_exec(TheSqliteDb, (const char*)KInsertSqlZ().Ptr(), 0, 0, 0);
sl@0: 	TEST2(err, SQLITE_OK);
sl@0: 
sl@0: 	StmtHandleTest(EStmt8);
sl@0: 	StmtHandleTest(EStmt16);
sl@0: 	
sl@0: 	sqlite3_close(TheSqliteDb); 
sl@0: 	TheSqliteDb = NULL;
sl@0: 	(void)TheFs.Delete(KTestDatabase);
sl@0: 	}
sl@0: 
sl@0: void UtilFileNameTest()
sl@0: 	{
sl@0: #ifdef _ASSERTIONS	
sl@0: 	_LIT(KFileName, "bbb.db");
sl@0: 	
sl@0: 	_LIT(KFileName1, "c:\\aaa\\bbb.db\x0");
sl@0: 	TPtrC p1 = Util::Filename(KFileName1().Ptr());
sl@0: 	TEST(p1 == KFileName);
sl@0: 	
sl@0: 	_LIT(KFileName2, "c:/aaa/bbb.db\x0");
sl@0: 	TPtrC p2 = Util::Filename(KFileName2().Ptr());
sl@0: 	TEST(p2 == KFileName);
sl@0: 	
sl@0: 	_LIT(KFileName3, "bbb.db\x0");
sl@0: 	TPtrC p3 = Util::Filename(KFileName3().Ptr());
sl@0: 	TEST(p3 == KFileName);
sl@0: #else
sl@0: 	TheTest.Printf(_L(" === This test case works only if the test is built with the _ASSERTIONS macro defined!\r\n"));
sl@0: #endif	
sl@0: 	}
sl@0: 
sl@0: void DoTests()
sl@0: 	{
sl@0: 	TheTest.Start(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1628 GetFirstSqlStmt() test "));
sl@0: 	TestGetFirstSqlStmt();
sl@0: 	
sl@0: 	TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-3433 DEF104744 SQL Server crashes on ORDER BY clause "));
sl@0: 	DEF104744();
sl@0: 
sl@0: 	TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-3466 DEF105444 SQL, sqlite3_prepare_v2() demonstrates non-atomic behaviour (handle leak) "));
sl@0: 	DEF105444();
sl@0: 
sl@0: 	TheTest.Next(_L(" Util::Filename() test"));
sl@0: 	UtilFileNameTest();
sl@0: 	}
sl@0: 
sl@0: //Creates file session instance and the test directory
sl@0: void CreateTestEnv()
sl@0:     {
sl@0: 	TInt err = TheFs.Connect();
sl@0: 	TheTest(err == KErrNone);
sl@0: 
sl@0: 	err = TheFs.MkDir(KTestDir);
sl@0: 	TEST(err == KErrNone || err == KErrAlreadyExists);
sl@0: 	}
sl@0: 
sl@0: TInt E32Main()
sl@0: 	{
sl@0: 	TheSqliteDb = NULL;
sl@0: 	
sl@0: 	TheTest.Title();
sl@0: 	
sl@0: 	CTrapCleanup* tc = CTrapCleanup::New();
sl@0: 	
sl@0: 	__UHEAP_MARK;
sl@0: 
sl@0: 	CreateTestEnv();
sl@0: 	TInt err = sqlite3SymbianLibInit();
sl@0: 	TEST2(err, KErrNone);
sl@0: 	DoTests();
sl@0: 	sqlite3SymbianLibFinalize();
sl@0: 	DestroyTestEnv();
sl@0: 
sl@0: 	CloseSTDLIB();
sl@0: 
sl@0: 	__UHEAP_MARKEND;
sl@0: 	
sl@0: 	TheTest.End();
sl@0: 	TheTest.Close();
sl@0: 	
sl@0: 	delete tc;
sl@0: 
sl@0: 	User::Heap().Check();
sl@0: 	return KErrNone;
sl@0: 	}