// Copyright (c) 2005-2009 Nokia Corporation and/or its subsidiary(-ies).

 // All rights reserved.

 // This component and the accompanying materials are made available

 // under the terms of "Eclipse Public License v1.0"

 // which accompanies this distribution, and is available

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

 //

 // Initial Contributors:

 // Nokia Corporation - initial contribution.

 //

 // Contributors:

 //

 // Description:

 // Testing DBMS ordering and searching functionality when the key field is unicode string.

 // The idea is to verify that collation level 0 is used when doing string based searching

 // and collation level > 0 is used when doing string based ordering.

 // 

 //

 #include <e32test.h>

 #include <f32file.h>

 #include <d32dbms.h>

 /////////////////////////////////////////////////////////////////

 //Globals

 _LIT(					KTestDatabase, "C:\\DBMS-TST\\T_DbmsStrComp.DB");

 static RTest			TheTest(_L("t_dbstrcmp"));

 static RFs				TheFs;

 static RDbNamedDatabase TheDb;

 static RDbs				TheDbSession;

 //Test table defs

 _LIT(KTestTableName1, "TABLE1");//EDbColText16 key field

 _LIT(KTestTableName2, "TABLE2");//EDbColLongText16 key field

 struct TColDef

 	{

 	const TText*	iName;

 	TDbColType		iType;

 	TInt			iAttributes;

 	};

 static TColDef const KColDefs1[]=

 	{

 		{_S("ID"), EDbColText16, 0},

 		{_S("DATA"), EDbColUint32, 0},

 		{0}

 	};

 static TColDef const KColDefs2[]=

 	{

 		{_S("ID"), EDbColLongText16, 0},

 		{_S("DATA"), EDbColUint32, 0},

 		{0}

 	};

 //Test strings

 const TInt KTestStrLen = 3; //The length of test strings

 typedef TBuf16<KTestStrLen> TNameBuf;

 //Test strings array - using upper and lower case - which will force the DBMS server to make

 //different decisions depending on what is the current case: ordering or searching.

 const TNameBuf KTestStr[] =

 	{

 	_L16("aaa"),

 	_L16("aAa"),

 	_L16("bbB"),

 	_L16("BbB")

 	};

 const TInt KTestStrCnt = sizeof(KTestStr) / sizeof(KTestStr[0]);

 ///////////////////////////////////////////////////////////////////////////////////////

 ///////////////////////////////////////////////////////////////////////////////////////

 //Destroy test environment - global functions

 //Deletes "aFullName" file.

 static TInt DeleteDataFile(const TDesC& aFullName)

 	{

 	RFs fsSession;

 	TInt err = fsSession.Connect();

 	if(err == KErrNone)

 		{

 		TEntry entry;

 		err = fsSession.Entry(aFullName, entry);

 		if(err == KErrNone)

 			{

 			RDebug::Print(_L("Deleting \"%S\" file.\n"), &aFullName);

 			err = fsSession.SetAtt(aFullName, 0, KEntryAttReadOnly);

 			if(err != KErrNone)

 				{

 				RDebug::Print(_L("Error %d changing \"%S\" file attributes.\n"), err, &aFullName);

 				}

 			err = fsSession.Delete(aFullName);

 			if(err != KErrNone)

 				{

 				RDebug::Print(_L("Error %d deleting \"%S\" file.\n"), err, &aFullName);

 				}

 			}

 		fsSession.Close();

 		}

 	else

 		{

 		RDebug::Print(_L("Error %d connecting file session. File: %S.\n"), err, &aFullName);

 		}

 	return err;

 	}

 ///////////////////////////////////////////////////////////////////////////////////////

 ///////////////////////////////////////////////////////////////////////////////////////

 //Tests macros and functions.

 //If (!aValue) then the test will be panicked, the test data files will be deleted.

 static void Check(TInt aValue, TInt aLine)

 	{

 	if(!aValue)

 		{

 		::DeleteDataFile(KTestDatabase);

 		TheTest(EFalse, aLine);

 		}

 	}

 //If (aValue != aExpected) then the test will be panicked, the test data files will be deleted.

 static void Check(TInt aValue, TInt aExpected, TInt aLine)

 	{

 	if(aValue != aExpected)

 		{

 		RDebug::Print(_L("*** Expected error: %d, got: %d\r\n"), aExpected, aValue);

 		::DeleteDataFile(KTestDatabase);

 		TheTest(EFalse, aLine);

 		}

 	}

 //Use these to test conditions.

 #define TEST(arg) ::Check((arg), __LINE__)

 #define TEST2(aValue, aExpected) ::Check(aValue, aExpected, __LINE__)

 ///////////////////////////////////////////////////////////////////////////////////////

 ///////////////////////////////////////////////////////////////////////////////////////

 //Global functions

 //Prepares the test directory.

 //TheFs.Connect() has to be called already.

 static void SetupTestDirectory()

     {

 	TInt err = TheFs.MkDir(KTestDatabase);

 	TEST(err == KErrNone || err == KErrAlreadyExists);

 	}

 //Leaves with info message printed out

 static void LeaveL(TInt aError, TInt aLine)

 	{

 	RDebug::Print(_L("*** Leave. Error: %d, Line: %d\r\n"), aError, aLine);

 	User::Leave(aError);

 	}

 //Leaves if aError < 0 with info message printed out

 static void LeaveIfErrorL(TInt aError, TInt aLine)

 	{

 	if(aError < KErrNone)

 		{

 		LeaveL(aError, aLine);

 		}

 	}

 //Use LEAVE() macro instead of User::Leave() and LEAVE_IF_ERROR() macro instead of

 //User::LeaveIfError(). They will print the line number, where the "leave" was called.

 #define LEAVE(aError) ::LeaveL(aError, __LINE__)

 #define LEAVE_IF_ERROR(aError) ::LeaveIfErrorL(aError, __LINE__)

 //Creates the test DBMS session

 static void CreateTestDbSession()

 	{

 	RDebug::Print(_L("Create DBMS session\n"));

 	TInt err = TheDbSession.Connect();

 	TEST2(err, KErrNone);

 	}

 //Creates the test database

 //TheDbSession instance has to be connected already.

 //TheFs.Connect() has to be called already.

 static void CreateTestDatabase(RDbs& aDbs, RDbNamedDatabase& aDb)

 	{

 	RDebug::Print(_L("Create test database\n"));

 	TInt err = aDb.Replace(TheFs, KTestDatabase);

 	TEST2(err, KErrNone);

 	TheDb.Close();

 	err = aDb.Open(aDbs, KTestDatabase);

 	TEST2(err, KErrNone);

 	}

 //Creates test table

 static void DoCreateTestTableL(RDbNamedDatabase& aDb, const TDesC& aTblName, const TColDef aColDefs[])

 	{

 	CDbColSet* colSet = CDbColSet::NewLC();

 	for(const TColDef* colDef=aColDefs;colDef->iName;++colDef)

 		{

 		TDbCol col(TPtrC(colDef->iName), colDef->iType);

 		col.iAttributes = colDef->iAttributes;

 		colSet->AddL(col);

 		}

 	TEST2(aDb.CreateTable(aTblName, *colSet), KErrNone);

 	CleanupStack::PopAndDestroy(colSet);

 	}

 //Creates test tables

 static void CreateTestTablesL(RDbNamedDatabase& aDb)

 	{

 	RDebug::Print(_L("Create test tables\n"));

 	::DoCreateTestTableL(aDb, KTestTableName1, KColDefs1);

 	::DoCreateTestTableL(aDb, KTestTableName2, KColDefs2);

 	}

 //Gets the value of the string field, which type may be EDbColText16 or EDbColLongText16

 void GetStrFieldValueL(RDbRowSet& aTbl, const TDesC& aTblName, TDes& aStrFldVal)

 	{

 	if(aTblName.CompareF(KTestTableName1) == 0)

 		{

 		aStrFldVal = aTbl.ColDes16(1);

 		}

 	else

 		{

 		RDbColReadStream blob;

 		blob.OpenLC(aTbl, 1);

 		blob.ReadL(aStrFldVal, aTbl.ColLength(1));

 		CleanupStack::PopAndDestroy();

 		}

 	}

 //Prints all table records

 static TInt PrintRecordsL(RDbRowSet& aTbl, const TDesC& aTblName)

 	{

 	RDebug::Print(_L("Table: %S\n"), &aTblName);

     aTbl.FirstL();

 	TInt rec = 0;

     while(aTbl.AtRow())

         {

         aTbl.GetL();

 		TNameBuf strFldVal;

 		GetStrFieldValueL(aTbl, aTblName, strFldVal);

 		TUint32 v = aTbl.ColUint32(2);

 		RDebug::Print(_L("   Record %d, Str: %S, Val: %d\n"), ++rec, &strFldVal, v);

         aTbl.NextL();

         }

 	return rec;

 	}

 //Checks if the records order (based on a string key field comparison) matches the order of the

 //strings in aTestStrArray

 static void AssertRecordsOrderL(RDbRowSet& aTbl, const TDesC& aTblName, const RArray<TNameBuf>& aTestStrArray)

 	{

     aTbl.FirstL();

 	TInt rec = 0;

     while(aTbl.AtRow())

         {

         aTbl.GetL();

 		TNameBuf strFldVal;

 		GetStrFieldValueL(aTbl, aTblName, strFldVal);

 		TEST(aTestStrArray[rec] == strFldVal);

 		++rec;

         aTbl.NextL();

         }

 	}

 //Adds test data to the specified table. Make sure that the records are not in

 //order (assuming that the first field will be the key).

 static void AddTestDataL(RDbNamedDatabase& aDb, const TDesC& aTblName)

 	{

 	RDbTable tbl;

 	CleanupClosePushL(tbl);

 	TEST2(tbl.Open(aDb, aTblName, RDbRowSet::EUpdatable), KErrNone);

 	for(TInt i=0;i<KTestStrCnt;++i)

 		{

 		tbl.InsertL();

 		tbl.SetColL(1, KTestStr[KTestStrCnt - i - 1]);

 		tbl.SetColL(2, i + 1);

 		tbl.PutL();

 		}

 	TEST(tbl.CountL() == KTestStrCnt);

 	(void)::PrintRecordsL(tbl, aTblName);

 	CleanupStack::PopAndDestroy(&tbl);

 	}

 //Adds the test data to test tables

 static void AddTestDataL(RDbNamedDatabase& aDb)

 	{

 	RDebug::Print(_L("Add data to test tables\n"));

 	::AddTestDataL(aDb, KTestTableName1);

 	::AddTestDataL(aDb, KTestTableName2);

 	}

 //Init test environment

 static void InitEnvL()

 	{

 	::CreateTestDbSession();

     //Create test database and tables. Add some test data to them.

 	::CreateTestDatabase(TheDbSession, TheDb);

 	::CreateTestTablesL(TheDb);

 	::AddTestDataL(TheDb);

 	}

 //String comparison function, used in FillStrArraySorted() function.

 static TInt CompareC(const TNameBuf& aName1, const TNameBuf& aName2)

     {

     return aName1.CompareC(aName2);

     }

 //Inserts all test string into an ordered array - aTestStrArray

 static void FillStrArraySortedL(RArray<TNameBuf>& aTestStrArray)

 	{

 	for(TInt i=0;i<KTestStrCnt;++i)

 		{

     	User::LeaveIfError(aTestStrArray.InsertInOrder(KTestStr[i], TLinearOrder<TNameBuf>(CompareC)));

 		}

 	}

 static void CreateIndexL(RDbNamedDatabase& aDb, const TDesC& aTblName, const TDesC& aColumnName)

 	{

 	RDebug::Print(_L("Create index. Table: %S, column: %S\n"), &aTblName, &aColumnName);

 	CDbKey* key = CDbKey::NewLC();

 	key->AddL(aColumnName);

 	key->MakeUnique();

 	key->SetComparison(EDbCompareCollated);

 	LEAVE_IF_ERROR(aDb.CreateIndex(aColumnName, aTblName, *key));

 	CleanupStack::PopAndDestroy(key);

 	}

 ///////////////////////////////////////////////////////////////////////////////////////

 ///////////////////////////////////////////////////////////////////////////////////////

 //Test cases

 //Test case 1. Check SELECT statement with ORDER BY clause when the key field is a string.

 static void OrderByTestL(RDbNamedDatabase& aDb, const TDesC& aTblName, const RArray<TNameBuf>& aTestStrArray)

 	{

     RDbView view;

     CleanupClosePushL(view);

     TBuf<128> sqlStmt;

     sqlStmt.Append(_L("SELECT ID, DATA FROM "));

     sqlStmt.Append(aTblName);

     sqlStmt.Append(_L(" ORDER BY ID"));

     User::LeaveIfError(view.Prepare(aDb, TDbQuery(sqlStmt, EDbCompareCollated), TDbWindow::EUnlimited));

     User::LeaveIfError(view.EvaluateAll());

 	(void)::PrintRecordsL(view, aTblName);

 	AssertRecordsOrderL(view, aTblName, aTestStrArray);

     CleanupStack::PopAndDestroy(&view);

 	}

 //Test case 2. Check SELECT statement with LIKE keyword when the key field is a string.

 static void LikeTestL(RDbNamedDatabase& aDb, const TDesC& aTblName)

 	{

     RDbView view;

     CleanupClosePushL(view);

     TBuf<128> sqlStmt;

     sqlStmt.Append(_L("SELECT ID, DATA FROM "));

     sqlStmt.Append(aTblName);

     sqlStmt.Append(_L(" WHERE ID LIKE 'B*'"));

     User::LeaveIfError(view.Prepare(aDb, TDbQuery(sqlStmt, EDbCompareCollated), TDbWindow::EUnlimited));

     User::LeaveIfError(view.EvaluateAll());

 	TInt cnt = ::PrintRecordsL(view, aTblName);

 	TEST(cnt == 2);

     CleanupStack::PopAndDestroy(&view);

 	}

 //Test case 3. Check SELECT statement with LIKE & ORDER BY keywords when the key field is a string.

 static void LikeOrderTestL(RDbNamedDatabase& aDb, const TDesC& aTblName, const RArray<TNameBuf>& aTestStrArray)

 	{

     RDbView view;

     CleanupClosePushL(view);

     TBuf<128> sqlStmt;

     sqlStmt.Append(_L("SELECT ID, DATA FROM "));

     sqlStmt.Append(aTblName);

     sqlStmt.Append(_L(" WHERE ID LIKE 'B*' ORDER BY ID"));

     User::LeaveIfError(view.Prepare(aDb, TDbQuery(sqlStmt, EDbCompareCollated), TDbWindow::EUnlimited));

     User::LeaveIfError(view.EvaluateAll());

 	TInt cnt = ::PrintRecordsL(view, aTblName);

 	TEST(cnt == 2);

 	AssertRecordsOrderL(view, aTblName, aTestStrArray);

     CleanupStack::PopAndDestroy(&view);

 	}

 //Test case 4. Indexed table. The index is a string field.

 static void IndexTestL(RDbNamedDatabase& aDb, const TDesC& aTblName, const RArray<TNameBuf>& aTestStrArray)

 	{

 	_LIT(KIdxName, "ID");

 	::CreateIndexL(aDb, aTblName, KIdxName);

 	RDbTable tbl;

 	CleanupClosePushL(tbl);

 	TEST2(tbl.Open(aDb, aTblName, RDbRowSet::EReadOnly), KErrNone);

 	TEST2(tbl.SetIndex(KIdxName), KErrNone);

 	(void)::PrintRecordsL(tbl, aTblName);

 	AssertRecordsOrderL(tbl, aTblName, aTestStrArray);

     CleanupStack::PopAndDestroy(&tbl);

 	}

 ///////////////////////////////////////////////////////////////////////////////////////

 ///////////////////////////////////////////////////////////////////////////////////////

 //The main test function.

 //Call your new test functions from here

 static void RunTestsL()

 	{

 	TheTest.Start(_L(" @SYMTestCaseID:SYSLIB-DBMS-LEGACY-DBMSSTRCOMP-0001 Init test environment "));

 	::InitEnvL();

     RArray<TNameBuf> testStrArray;

     CleanupClosePushL(testStrArray);

 	::FillStrArraySortedL(testStrArray);

 	TheTest.Next(_L("SELECT, ORDER BY, EDbColText16"));

 	::OrderByTestL(TheDb, KTestTableName1, testStrArray);

 	TheTest.Next(_L("SELECT, ORDER BY, EDbColLongText16"));

 	::OrderByTestL(TheDb, KTestTableName2, testStrArray);

 	TheTest.Next(_L("SELECT, LIKE, EDbColText16"));

 	::LikeTestL(TheDb, KTestTableName1);

 	TheTest.Next(_L("SELECT, LIKE, EDbColLongText16"));

 	::LikeTestL(TheDb, KTestTableName2);

     RArray<TNameBuf> testStrArray2;

     CleanupClosePushL(testStrArray2);

     testStrArray2.AppendL(testStrArray[2]);//"bbB"

     testStrArray2.AppendL(testStrArray[3]);//"BbB"

 	TheTest.Next(_L("SELECT, LIKE, ORDER BY, EDbColText16"));

 	::LikeOrderTestL(TheDb, KTestTableName1, testStrArray2);

 	TheTest.Next(_L("SELECT, LIKE, ORDER BY, EDbColLongText16"));

 	::LikeOrderTestL(TheDb, KTestTableName2, testStrArray2);

 	TheTest.Next(_L("Index, EDbColText16"));

 	::IndexTestL(TheDb, KTestTableName1, testStrArray);

 // Not possible to create a key with EDbColLongText16

 //	TheTest.Next(_L("Index, EDbColLongText16"));

 //	::IndexTestL(TheDb, KTestTableName2, testStrArray);

 	//Add tests here!

     CleanupStack::PopAndDestroy(&testStrArray2);

     CleanupStack::PopAndDestroy(&testStrArray);

 	}

 TInt E32Main()

 	{

 	TheTest.Title();

 	__UHEAP_MARK;

 	CTrapCleanup* trapCleanup = CTrapCleanup::New();

 	TEST(trapCleanup != NULL);

 	TInt err = TheFs.Connect();

 	TEST2(err, KErrNone);

 	::SetupTestDirectory();

 	::DeleteDataFile(KTestDatabase);

 	TRAP(err, ::RunTestsL());

 	TheDb.Close();

 	TheDbSession.Close();

 	TheFs.Close();

 	TEST2(err, KErrNone);

 	::DeleteDataFile(KTestDatabase);

 	TheTest.End();

 	TheTest.Close();

 	delete trapCleanup;

 	__UHEAP_MARKEND;

 	return 0;

 	}