// 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;

	}