// Copyright (c) 2006-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:

//

#include <e32test.h>

#include <bautils.h>

#include <sqldb.h>

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

RTest TheTest(_L("t_sqlcollate test"));

_LIT(KTestDir, "c:\\test\\");

_LIT(KTestDbName1, "c:\\test\\t_sqlcollate.db");

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

void DeleteTestFiles()

	{

	RSqlDatabase::Delete(KTestDbName1);

	}

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

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

//Test macros and functions

void Check(TInt aValue, TInt aLine)

	{

	if(!aValue)

		{

		DeleteTestFiles();

		TheTest(EFalse, aLine);

		}

	}

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

	{

	if(aValue != aExpected)

		{

		DeleteTestFiles();

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

		TheTest(EFalse, aLine);

		}

	}

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

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

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

void CreateTestDir()

    {

    RFs fs;

	TInt err = fs.Connect();

	TEST2(err, KErrNone);

	err = fs.MkDir(KTestDir);

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

	fs.Close();

	}

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

//Compare aLeft and aRight strings using collated comparison, level 3, default collation method.

//This function is used when sorting the test names array in CollationTest1L().

TInt StrSortC3(const TPtrC& aLeft, const TPtrC& aRight)

	{

	return aLeft.CompareC(aRight, 3, NULL);	

	}

//Compare aLeft and aRight strings using collated comparison, level 1, default collation method.

//This function is used when sorting the test names array in CollationTest3L().

TInt StrSortC1(const TPtrC& aLeft, const TPtrC& aRight)

	{

	return aLeft.CompareC(aRight, 1, NULL);	

	}

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

/**

@SYMTestCaseID			SYSLIB-SQL-CT-1609

@SYMTestCaseDesc		Create a table with a text column with default collation "CompareF"

						and insert some records there. 

						Test how the searching operations work executing some SELECT SQL statements.

@SYMTestPriority		High

@SYMTestActions			Testing database search operations using "CompareF" and "CompareC3" collations.

@SYMTestExpectedResults Test must not fail

@SYMREQ					REQ5907

*/	

void CollationTest1L()

	{

	RSqlDatabase db;

	TInt err = db.Create(KTestDbName1);

	TEST2(err, KErrNone);

	//Create test database

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

	_LIT(KCreateSql, "CREATE TABLE A(Name VARCHAR(100) COLLATE CompareF); CREATE INDEX AIdx ON A(Name COLLATE CompareF);");

	err = db.Exec(KCreateSql);

	TEST(err >= 0);

	//Insert some records. The column "Name" of each record contains the same name but the name characters are

	//variation of upper/lower case letters.

	RDebug::Print(_L("###Insert some records\r\n"));

	_LIT(KInsertSql, "INSERT INTO A(Name) VALUES(");

	//Collation sort order:    KNames[1] KNames[3] KNames[0] KNames[2]

	//Long "aaaa..." added to the end of each column value because SQLITE may use non-aligned strings

	//only when the string length is in [32..<cache_page_size>] interval.

	TPtrC KNames[] = {

		_L("aLex-aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"), 

		_L("ALeX-aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"), 

		_L("aleX-aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"), 

		_L("Alex-aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa")};

	const TInt KInsertSqlStmtCnt = sizeof(KNames)/sizeof(KNames[0]);

	RArray<TPtrC> sortedNames;

	TLinearOrder<TPtrC> order(&StrSortC3);

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

		{

		err = sortedNames.InsertInOrder(KNames[i], order);

		TEST2(err, KErrNone);

		TBuf<128> sql(KInsertSql);

		sql.Append(_L("'"));

		sql.Append(KNames[i]);

		sql.Append(_L("')"));

		err = db.Exec(sql);

		TEST2(err, 1);

		}

	//The next "SELECT" statement must return a set containing all table records

	RDebug::Print(_L("###Select all records\r\n"));

	_LIT(KSelectSql1, "SELECT * FROM A WHERE NAME = 'alex-aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'");

	RSqlStatement stmt;

	err = stmt.Prepare(db, KSelectSql1);

	TEST2(err, KErrNone);	

	TInt recCount = 0;

	while(stmt.Next() == KSqlAtRow)

		{

		++recCount;

		TPtrC name = stmt.ColumnTextL(0);

		RDebug::Print(_L("%S\r\n"), &name);

		}

	stmt.Close();

	TEST(recCount == KInsertSqlStmtCnt);

	//The next "SELECT" statement must return a set containing all table records

	// this tests a LIKE clause with a bound parameter (with wildcards)	

	RDebug::Print(_L("###Select all records (LIKE with wildcard)\r\n"));

	_LIT(KSelectSql1a, "SELECT * FROM A WHERE NAME LIKE :Val");

	_LIT(KSearchString,"alex-aaaa%");

	err = stmt.Prepare(db, KSelectSql1a);

	TEST2(err, KErrNone);

	TInt idx=stmt.ParameterIndex(_L(":Val"));

	err=stmt.BindText(idx,KSearchString);

	TEST2(err, KErrNone);

	recCount = 0;

	while(stmt.Next() == KSqlAtRow)

		{

		++recCount;

		TPtrC name = stmt.ColumnTextL(0);

		RDebug::Print(_L("%S\r\n"), &name);

		}

	stmt.Close();

	TEST(recCount == KInsertSqlStmtCnt);

	//The next "SELECT" statement must return a set containing all table records

	// this tests a LIKE clause with a bound parameter (with no wildcards)

	RDebug::Print(_L("###Select all records (LIKE with no wildcard)\r\n"));

	_LIT(KSelectSql1b, "SELECT * FROM A WHERE NAME LIKE :Val");

	_LIT(KSearchStringA,

"alex-aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa");

	err = stmt.Prepare(db, KSelectSql1b);

	idx=stmt.ParameterIndex(_L(":Val"));

	TEST2(err, KErrNone);

	err=stmt.BindText(idx,KSearchStringA);

	recCount = 0;

	while(stmt.Next() == KSqlAtRow)

		{

		++recCount;

		TPtrC name = stmt.ColumnTextL(0);

		RDebug::Print(_L("%S\r\n"), &name);

		}

	stmt.Close();

	TEST(recCount == KInsertSqlStmtCnt);

	//The next "SELECT" statement must return a row 

	// this tests a LIKE clause with a bound parameter and funny characters

	RDebug::Print(_L("###Select one records (LIKE with bound param with URL chars)\r\n"));

	err=db.Exec(_L("INSERT INTO A(Name) VALUES('http://a.b.c#d')"));

	TEST2(err,1);

	_LIT(KSelectSql1c, "SELECT * FROM A WHERE NAME LIKE :Val");

	_LIT(KSearchStringB,"http%");

	err = stmt.Prepare(db, KSelectSql1c);

	idx=stmt.ParameterIndex(_L(":Val"));

	TEST2(err, KErrNone);

	err=stmt.BindText(idx,KSearchStringB);

	recCount = 0;

	while(stmt.Next() == KSqlAtRow)

		{

		++recCount;

		TPtrC name = stmt.ColumnTextL(0);

		RDebug::Print(_L("%S\r\n"), &name);

		}

	stmt.Close();

	TEST(recCount == 1);

	//The next "SELECT" statement must return a set containing all table records, folded comparison used for sorting

	RDebug::Print(_L("###Select all records, folded string comparison\r\n"));

	_LIT(KSelectSql2, "SELECT * FROM A WHERE NAME = 'alex-aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ORDER BY NAME COLLATE CompareF");

	err = stmt.Prepare(db, KSelectSql2);

	TEST2(err, KErrNone);

	recCount = 0;

	for(TInt j=0;j<KInsertSqlStmtCnt;++j)

		{

		err = stmt.Next();

		TEST2(err, KSqlAtRow);

		++recCount;

		TPtrC name = stmt.ColumnTextL(0);

		RDebug::Print(_L("%S\r\n"), &name);

		TEST(name == KNames[j]);

		}

	stmt.Close();

	TEST(recCount == KInsertSqlStmtCnt);

	//The next "SELECT" statement must return a set containing all table records, collated comparison used for sorting

	RDebug::Print(_L("###Select all records, collated string comparison\r\n"));

	_LIT(KSelectSql3, "SELECT * FROM A ORDER BY NAME COLLATE CompareC3");

	err = stmt.Prepare(db, KSelectSql3);

	TEST2(err, KErrNone);

	for(TInt k=0;k<KInsertSqlStmtCnt;++k)

		{

		err = stmt.Next();

		TEST2(err, KSqlAtRow);

		TPtrC name = stmt.ColumnTextL(0);

		RDebug::Print(_L("%S\r\n"), &name);

		TEST(name == sortedNames[k]);

		}

	stmt.Close();

	//Cleanup	

	sortedNames.Close();

	db.Close();

	//To debug database reindexing

	err = db.Open(KTestDbName1);

	TEST2(err, KErrNone);

	db.Close();

	RDebug::Print(_L("###Delete test database\r\n"));

	(void)RSqlDatabase::Delete(KTestDbName1);

	}

/**

@SYMTestCaseID			SYSLIB-SQL-CT-1610

@SYMTestCaseDesc		Create a table with a text column with default collation "CompareC3"

						and insert some records there. 

						Test how the searching operations work executing some SELECT SQL statements.

@SYMTestPriority		High

@SYMTestActions			Testing database sorting operations using "CompareC3" collation.

@SYMTestExpectedResults Test must not fail

@SYMREQ					REQ5907

*/	

void CollationTest2L()

	{

	RSqlDatabase db;

	TInt err = db.Create(KTestDbName1);

	TEST2(err, KErrNone);

	//Create test database

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

	_LIT(KCreateSql, "CREATE TABLE A(Name VARCHAR(100) COLLATE CompareC3)");

	err = db.Exec(KCreateSql);

	TEST(err >= 0);

	//Insert some records.

	RDebug::Print(_L("###Insert some records\r\n"));

	_LIT(KInsertSql, "INSERT INTO A(Name) VALUES(");

	TPtrC KNames[] = {

		_L("aAaA"), 

		_L("AAaa"), 

		_L("aaAA"), 

		_L("aaaA")};

	const TInt KInsertSqlStmtCnt = sizeof(KNames)/sizeof(KNames[0]);

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

		{

		TBuf<128> sql(KInsertSql);

		sql.Append(_L("'"));

		sql.Append(KNames[i]);

		sql.Append(_L("')"));

		err = db.Exec(sql);

		TEST2(err, 1);

		}

	RSqlStatement stmt;

	//The next "SELECT" statement must return a set containing all table 

	//records which Name column value is bigger than "aaAA"

	RDebug::Print(_L("###Select all records, which Name column value is bigger than 'aaAA'\r\n"));

	_LIT(KSelectSql2, "SELECT * FROM A WHERE NAME > 'aaAA'");

	err = stmt.Prepare(db, KSelectSql2);

	TEST2(err, KErrNone);

	while((err = stmt.Next()) == KSqlAtRow)

		{

		TPtrC name = stmt.ColumnTextL(0);

		RDebug::Print(_L("%S\r\n"), &name);

		TInt res = name.CompareC(KNames[2], 3, NULL); 

		TEST(res > 0);

		}

	stmt.Close();

	TEST2(err, KSqlAtEnd);

	//Cleanup	

	db.Close();

	RDebug::Print(_L("###Delete test database\r\n"));

	(void)RSqlDatabase::Delete(KTestDbName1);

	}

/**

@SYMTestCaseID			SYSLIB-SQL-CT-1627

@SYMTestCaseDesc		Create a table with a text column "NAME" with default collation "CompareC0"

						and insert some records there. All inserted names are equal if compared at

						collation level 0, but some of them contain accented letters.

						Test how the searching operations work executing some SELECT SQL statements.

						Test how the sorting operations work executing some SELECT SQL statements.

@SYMTestPriority		High

@SYMTestActions			Testing database sorting operations using "CompareC0", "CompareC1", and "CompareC2" collations.

@SYMTestExpectedResults Test must not fail

@SYMREQ					REQ5907

*/	

void CollationTest3L()

	{

	RSqlDatabase db;

	TInt err = db.Create(KTestDbName1);

	TEST2(err, KErrNone);

	//Create test database

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

	_LIT(KCreateSql, "CREATE TABLE A(Name VARCHAR(100) COLLATE CompareC0)");

	err = db.Exec(KCreateSql);

	TEST(err >= 0);

	//Insert some records. Some of the inserted names have accented letters.

	//But all names are equal if compared at collation level 0.

	RDebug::Print(_L("###Insert some records\r\n"));

	_LIT(KInsertSql, "INSERT INTO A(Name) VALUES(");

	TBuf<10> name1(_L("Dvorak"));

	TBuf<10> name2;

	name2.SetLength(6);

	name2[0] = TChar('D'); 

	name2[1] = TChar('v'); 

	name2[2] = TChar('o'); 

	name2[3] = 0x0158;//LATIN SMALL LETTER R HACEK

	name2[4] = 0x00C1;//LATIN SMALL LETTER A ACUTE

	name2[5] = TChar('k');

	const TPtrC KNames[] = {name1, name2};

	const TInt KInsertSqlStmtCnt = sizeof(KNames)/sizeof(KNames[0]);

	RArray<TPtrC> sortedNames;

	TLinearOrder<TPtrC> order(&StrSortC1);

	//Insert the records. Also, check how many names are equal to 'dvorak' using collation level 0.

	_LIT(KTestName, "dvorak");

	TInt matchNameCnt = 0;

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

		{

		if(KNames[i].CompareC(KTestName, 0, NULL) == 0)

			{

			++matchNameCnt;	

			}

		err = sortedNames.InsertInOrder(KNames[i], order);

		TEST2(err, KErrNone);

		TBuf<128> sql(KInsertSql);

		sql.Append(_L("'"));

		sql.Append(KNames[i]);

		sql.Append(_L("')"));

		err = db.Exec(sql);

		TEST2(err, 1);

		}

	//The next "SELECT" statement must return a set, which record count must be matchNameCnt.

	RDebug::Print(_L("###Select all records, collated string comparison, level 0\r\n"));

	_LIT(KSelectSql2, "SELECT * FROM A WHERE NAME = 'dvorak'");

	RSqlStatement stmt;

	err = stmt.Prepare(db, KSelectSql2);

	TEST2(err, KErrNone);

	TInt recCount = 0;

	while((err = stmt.Next()) == KSqlAtRow)

		{

		TPtrC name = stmt.ColumnTextL(0);

		RDebug::Print(_L("%S\r\n"), &name);

		TEST(name == KNames[recCount]);

		++recCount;

		}

	stmt.Close();

	TEST(recCount == matchNameCnt);

	//The next "SELECT" statement must return an ordered set containing all table records.

	RDebug::Print(_L("###Select all records, collated string comparison, level 1\r\n"));

	_LIT(KSelectSql3, "SELECT * FROM A WHERE NAME = 'dvorak' ORDER BY NAME COLLATE CompareC1 DESC");

	err = stmt.Prepare(db, KSelectSql3);

	TEST2(err, KErrNone);

	for(TInt k=0;k<KInsertSqlStmtCnt;++k)

		{

		err = stmt.Next();

		TEST2(err, KSqlAtRow);

		TPtrC name = stmt.ColumnTextL(0);

		RDebug::Print(_L("%S %S\r\n"), &name, &sortedNames[k]);

		TEST(name == sortedNames[KInsertSqlStmtCnt - k - 1]);//descending order

		}

	stmt.Close();

	//CompareC2 collation used in the SELECT statement

	err = stmt.Prepare(db, _L("SELECT NAME FROM A WHERE NAME = 'Dvorak' COLLATE CompareC2"));

	TEST2(err, KErrNone);

	err = stmt.Next();

	TEST2(err, KSqlAtRow);

	TPtrC name3 = stmt.ColumnTextL(0);

	TEST(name3 == name1);

	err = stmt.Next();

	TEST2(err, KSqlAtEnd);

	stmt.Close();

	//Cleanup	

	sortedNames.Close();

	db.Close();

	RDebug::Print(_L("###Delete test database\r\n"));

	(void)RSqlDatabase::Delete(KTestDbName1);

	}

/**

@SYMTestCaseID			SYSLIB-SQL-CT-1760

@SYMTestCaseDesc		Creating a test table with a text field. Inserting some records there and

						testing how LIKE operator works. The test cases include: accented text column values,

						using '_' and '%' wild cards, using ESCAPE clause, blank pattern string, blank ESCAPE

						string, multi-character ESCAPE string.

@SYMTestPriority		High

@SYMTestActions			Testing SQL LIKE operator and ESCAPE clause.

@SYMTestExpectedResults Test must not fail

@SYMREQ					REQ5907

*/

void LikeTest1()

	{

	RSqlDatabase db;

	TInt err = db.Create(KTestDbName1);

	TEST2(err, KErrNone);

	//Create a test table and insert some records

	err = db.Exec(_L("CREATE TABLE A(Id INTEGER PRIMARY KEY, Name TEXT)"));

	TEST(err >= 0);

	err = db.Exec(_L("INSERT INTO A(Id, Name) VALUES(1, 'Dvorak')"));

	TEST2(err, 1);

	err = db.Exec(_L("INSERT INTO A(Id, Name) VALUES(2, 'Dvorák')"));

	TEST2(err, 1);

	//Create a statement object and issue a SELECT SQL statement + LIKE clause

	//Test case 1 = full name search with LIKE

	RSqlStatement stmt;

	err = stmt.Prepare(db, _L("SELECT COUNT(*) FROM A WHERE Name LIKE 'DVORAK'"));

	TEST2(err, KErrNone);

	err = stmt.Next();

	TEST2(err, KSqlAtRow);

	TInt cnt = stmt.ColumnInt(0);

	TEST2(cnt, 2);

	stmt.Close();

	//Test case 2 = wild card used sequence character in the search pattern + LIKE

	err = stmt.Prepare(db, _L("SELECT COUNT(*) FROM A WHERE Name LIKE '%RA%'"));

	TEST2(err, KErrNone);

	err = stmt.Next();

	TEST2(err, KSqlAtRow);

	cnt = stmt.ColumnInt(0);

	TEST2(cnt, 2);

	stmt.Close();

	//Test case 3 = wild card character used in the search pattern + LIKE

	err = stmt.Prepare(db, _L("SELECT COUNT(*) FROM A WHERE Name LIKE 'DV___K'"));

	TEST2(err, KErrNone);

	err = stmt.Next();

	TEST2(err, KSqlAtRow);

	cnt = stmt.ColumnInt(0);

	TEST2(cnt, 2);

	stmt.Close();

	//Insert one more record

	err = db.Exec(_L("INSERT INTO A(Id, Name) VALUES(3, 't_sqltest')"));

	TEST2(err, 1);

	//Test case 4 = wild card character used in the search pattern + LIKE + ESCAPE

	err = stmt.Prepare(db, _L("SELECT COUNT(*) FROM A WHERE Name LIKE 't/_sqlte__' ESCAPE '/'"));

	TEST2(err, KErrNone);

	err = stmt.Next();

	TEST2(err, KSqlAtRow);

	cnt = stmt.ColumnInt(0);

	TEST2(cnt, 1);

	stmt.Close();

	//Test case 5 = wild card character used in the search pattern + LIKE + ESCAPE without an escape character

	err = stmt.Prepare(db, _L("SELECT COUNT(*) FROM A WHERE Name LIKE 't/_sqltest' ESCAPE ''"));

	TEST2(err, KErrNone);

	err = stmt.Next();

	TEST(err != KErrNone);

	TEST2(::SqlRetCodeClass(err), ESqlDbError);

	TPtrC errMsg = db.LastErrorMessage();

	RDebug::Print(_L("!! error=\"%S\"\r\n"), &errMsg);

	stmt.Close();

	//Test case 6 = wild card character used in the search pattern + LIKE + ESCAPE with more than one escape characters

	err = stmt.Prepare(db, _L("SELECT COUNT(*) FROM A WHERE Name LIKE 't/_sqltest' ESCAPE '1234'"));

	TEST2(err, KErrNone);

	err = stmt.Next();

	TEST(err != KErrNone);

	TEST2(::SqlRetCodeClass(err), ESqlDbError);

	errMsg.Set(db.LastErrorMessage());

	RDebug::Print(_L("!! error=\"%S\"\r\n"), &errMsg);

	stmt.Close();

	//Test case 7 = blank pattern string

	err = stmt.Prepare(db, _L("SELECT COUNT(*) FROM A WHERE Name LIKE ''"));