Symaptic: os/persistentdata/persistentstorage/sql/TEST/t

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

     2 // All rights reserved.

     3 // This component and the accompanying materials are made available

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

     5 // which accompanies this distribution, and is available

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

     7 //

     8 // Initial Contributors:

     9 // Nokia Corporation - initial contribution.

    10 //

    11 // Contributors:

    12 //

    13 // Description:

    14 //

    16 #include <e32test.h>

    17 #include <bautils.h>

    18 #include <sqldb.h>

    20 ///////////////////////////////////////////////////////////////////////////////////////

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

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

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

    27 ///////////////////////////////////////////////////////////////////////////////////////

    29 void DeleteTestFiles()

    30 	{

    31 	RSqlDatabase::Delete(KTestDbName1);

    32 	}

    34 ///////////////////////////////////////////////////////////////////////////////////////

    35 ///////////////////////////////////////////////////////////////////////////////////////

    36 //Test macros and functions

    37 void Check(TInt aValue, TInt aLine)

    38 	{

    39 	if(!aValue)

    40 		{

    41 		DeleteTestFiles();

    42 		TheTest(EFalse, aLine);

    43 		}

    44 	}

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

    46 	{

    47 	if(aValue != aExpected)

    48 		{

    49 		DeleteTestFiles();

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

    51 		TheTest(EFalse, aLine);

    52 		}

    53 	}

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

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

    57 ///////////////////////////////////////////////////////////////////////////////////////

    59 void CreateTestDir()

    60     {

    61     RFs fs;

    62 	TInt err = fs.Connect();

    63 	TEST2(err, KErrNone);

    65 	err = fs.MkDir(KTestDir);

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

    68 	fs.Close();

    69 	}

    71 ///////////////////////////////////////////////////////////////////////////////////////

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

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

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

    76 	{

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

    78 	}

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

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

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

    83 	{

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

    85 	}

    87 ///////////////////////////////////////////////////////////////////////////////////////

    89 /**

    90 @SYMTestCaseID			SYSLIB-SQL-CT-1609

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

    92 						and insert some records there.

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

    94 @SYMTestPriority		High

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

    96 @SYMTestExpectedResults Test must not fail

    97 @SYMREQ					REQ5907

    98 */

    99 void CollationTest1L()

   100 	{

   101 	RSqlDatabase db;

   102 	TInt err = db.Create(KTestDbName1);

   103 	TEST2(err, KErrNone);

   105 	//Create test database

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

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

   108 	err = db.Exec(KCreateSql);

   109 	TEST(err >= 0);

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

   112 	//variation of upper/lower case letters.

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

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

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

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

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

   118 	TPtrC KNames[] = {

   119 		_L("aLex-aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"),

   120 		_L("ALeX-aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"),

   121 		_L("aleX-aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"),

   122 		_L("Alex-aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa")};

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

   124 	RArray<TPtrC> sortedNames;

   125 	TLinearOrder<TPtrC> order(&StrSortC3);

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

   128 		{

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

   130 		TEST2(err, KErrNone);

   132 		TBuf<128> sql(KInsertSql);

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

   134 		sql.Append(KNames[i]);

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

   136 		err = db.Exec(sql);

   137 		TEST2(err, 1);

   138 		}

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

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

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

   143 	RSqlStatement stmt;

   144 	err = stmt.Prepare(db, KSelectSql1);

   145 	TEST2(err, KErrNone);

   146 	TInt recCount = 0;

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

   148 		{

   149 		++recCount;

   150 		TPtrC name = stmt.ColumnTextL(0);

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

   152 		}

   153 	stmt.Close();

   154 	TEST(recCount == KInsertSqlStmtCnt);

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

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

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

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

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

   161 	err = stmt.Prepare(db, KSelectSql1a);

   162 	TEST2(err, KErrNone);

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

   164 	err=stmt.BindText(idx,KSearchString);

   165 	TEST2(err, KErrNone);

   166 	recCount = 0;

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

   168 		{

   169 		++recCount;

   170 		TPtrC name = stmt.ColumnTextL(0);

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

   172 		}

   173 	stmt.Close();

   174 	TEST(recCount == KInsertSqlStmtCnt);

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

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

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

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

   180 	_LIT(KSearchStringA,

   181 "alex-aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa");

   182 	err = stmt.Prepare(db, KSelectSql1b);

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

   184 	TEST2(err, KErrNone);

   185 	err=stmt.BindText(idx,KSearchStringA);

   186 	recCount = 0;

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

   188 		{

   189 		++recCount;

   190 		TPtrC name = stmt.ColumnTextL(0);

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

   192 		}

   193 	stmt.Close();

   194 	TEST(recCount == KInsertSqlStmtCnt);

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

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

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

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

   200 	TEST2(err,1);

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

   202 	_LIT(KSearchStringB,"http%");

   203 	err = stmt.Prepare(db, KSelectSql1c);

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

   205 	TEST2(err, KErrNone);

   206 	err=stmt.BindText(idx,KSearchStringB);

   207 	recCount = 0;

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

   209 		{

   210 		++recCount;

   211 		TPtrC name = stmt.ColumnTextL(0);

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

   213 		}

   214 	stmt.Close();

   215 	TEST(recCount == 1);

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

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

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

   221 	err = stmt.Prepare(db, KSelectSql2);

   222 	TEST2(err, KErrNone);

   224 	recCount = 0;

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

   226 		{

   227 		err = stmt.Next();

   228 		TEST2(err, KSqlAtRow);

   229 		++recCount;

   230 		TPtrC name = stmt.ColumnTextL(0);

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

   232 		TEST(name == KNames[j]);

   233 		}

   234 	stmt.Close();

   235 	TEST(recCount == KInsertSqlStmtCnt);

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

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

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

   240 	err = stmt.Prepare(db, KSelectSql3);

   241 	TEST2(err, KErrNone);

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

   244 		{

   245 		err = stmt.Next();

   246 		TEST2(err, KSqlAtRow);

   247 		TPtrC name = stmt.ColumnTextL(0);

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

   249 		TEST(name == sortedNames[k]);

   250 		}

   252 	stmt.Close();

   254 	//Cleanup

   255 	sortedNames.Close();

   256 	db.Close();

   258 	//To debug database reindexing

   259 	err = db.Open(KTestDbName1);

   260 	TEST2(err, KErrNone);

   261 	db.Close();

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

   264 	(void)RSqlDatabase::Delete(KTestDbName1);

   265 	}

   267 /**

   268 @SYMTestCaseID			SYSLIB-SQL-CT-1610

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

   270 						and insert some records there.

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

   272 @SYMTestPriority		High

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

   274 @SYMTestExpectedResults Test must not fail

   275 @SYMREQ					REQ5907

   276 */

   277 void CollationTest2L()

   278 	{

   279 	RSqlDatabase db;

   280 	TInt err = db.Create(KTestDbName1);

   281 	TEST2(err, KErrNone);

   283 	//Create test database

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

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

   286 	err = db.Exec(KCreateSql);

   287 	TEST(err >= 0);

   289 	//Insert some records.

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

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

   292 	TPtrC KNames[] = {

   293 		_L("aAaA"),

   294 		_L("AAaa"),

   295 		_L("aaAA"),

   296 		_L("aaaA")};

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

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

   300 		{

   301 		TBuf<128> sql(KInsertSql);

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

   303 		sql.Append(KNames[i]);

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

   305 		err = db.Exec(sql);

   306 		TEST2(err, 1);

   307 		}

   309 	RSqlStatement stmt;

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

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

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

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

   315 	err = stmt.Prepare(db, KSelectSql2);

   316 	TEST2(err, KErrNone);

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

   319 		{

   320 		TPtrC name = stmt.ColumnTextL(0);

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

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

   323 		TEST(res > 0);

   324 		}

   325 	stmt.Close();

   326 	TEST2(err, KSqlAtEnd);

   328 	//Cleanup

   329 	db.Close();

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

   331 	(void)RSqlDatabase::Delete(KTestDbName1);

   332 	}

   334 /**

   335 @SYMTestCaseID			SYSLIB-SQL-CT-1627

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

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

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

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

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

   341 @SYMTestPriority		High

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

   343 @SYMTestExpectedResults Test must not fail

   344 @SYMREQ					REQ5907

   345 */

   346 void CollationTest3L()

   347 	{

   348 	RSqlDatabase db;

   349 	TInt err = db.Create(KTestDbName1);

   350 	TEST2(err, KErrNone);

   352 	//Create test database

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

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

   355 	err = db.Exec(KCreateSql);

   356 	TEST(err >= 0);

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

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

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

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

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

   363 	TBuf<10> name2;

   364 	name2.SetLength(6);

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

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

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

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

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

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

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

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

   373 	RArray<TPtrC> sortedNames;

   374 	TLinearOrder<TPtrC> order(&StrSortC1);

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

   377 	_LIT(KTestName, "dvorak");

   378 	TInt matchNameCnt = 0;

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

   380 		{

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

   382 			{

   383 			++matchNameCnt;

   384 			}

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

   387 		TEST2(err, KErrNone);

   389 		TBuf<128> sql(KInsertSql);

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

   391 		sql.Append(KNames[i]);

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

   393 		err = db.Exec(sql);

   394 		TEST2(err, 1);

   395 		}

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

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

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

   400 	RSqlStatement stmt;

   401 	err = stmt.Prepare(db, KSelectSql2);

   402 	TEST2(err, KErrNone);

   404 	TInt recCount = 0;

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

   406 		{

   407 		TPtrC name = stmt.ColumnTextL(0);

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

   409 		TEST(name == KNames[recCount]);

   410 		++recCount;

   411 		}

   412 	stmt.Close();

   413 	TEST(recCount == matchNameCnt);

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

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

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

   418 	err = stmt.Prepare(db, KSelectSql3);

   419 	TEST2(err, KErrNone);

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

   422 		{

   423 		err = stmt.Next();

   424 		TEST2(err, KSqlAtRow);

   425 		TPtrC name = stmt.ColumnTextL(0);

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

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

   428 		}

   429 	stmt.Close();

   431 	//CompareC2 collation used in the SELECT statement

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

   433 	TEST2(err, KErrNone);

   434 	err = stmt.Next();

   435 	TEST2(err, KSqlAtRow);

   436 	TPtrC name3 = stmt.ColumnTextL(0);

   437 	TEST(name3 == name1);

   438 	err = stmt.Next();

   439 	TEST2(err, KSqlAtEnd);

   440 	stmt.Close();

   442 	//Cleanup

   443 	sortedNames.Close();

   444 	db.Close();

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

   446 	(void)RSqlDatabase::Delete(KTestDbName1);

   447 	}

   449 /**

   450 @SYMTestCaseID			SYSLIB-SQL-CT-1760

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

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

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

   454 						string, multi-character ESCAPE string.

   455 @SYMTestPriority		High

   456 @SYMTestActions			Testing SQL LIKE operator and ESCAPE clause.

   457 @SYMTestExpectedResults Test must not fail

   458 @SYMREQ					REQ5907

   459 */

   460 void LikeTest1()

   461 	{

   462 	RSqlDatabase db;

   463 	TInt err = db.Create(KTestDbName1);

   464 	TEST2(err, KErrNone);

   465 	//Create a test table and insert some records

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

   467 	TEST(err >= 0);

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

   469 	TEST2(err, 1);

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

   471 	TEST2(err, 1);

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

   473 	//Test case 1 = full name search with LIKE

   474 	RSqlStatement stmt;

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

   476 	TEST2(err, KErrNone);

   477 	err = stmt.Next();

   478 	TEST2(err, KSqlAtRow);

   479 	TInt cnt = stmt.ColumnInt(0);

   480 	TEST2(cnt, 2);

   481 	stmt.Close();

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

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

   484 	TEST2(err, KErrNone);

   485 	err = stmt.Next();

   486 	TEST2(err, KSqlAtRow);

   487 	cnt = stmt.ColumnInt(0);

   488 	TEST2(cnt, 2);

   489 	stmt.Close();

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

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

   492 	TEST2(err, KErrNone);

   493 	err = stmt.Next();

   494 	TEST2(err, KSqlAtRow);

   495 	cnt = stmt.ColumnInt(0);

   496 	TEST2(cnt, 2);

   497 	stmt.Close();

   498 	//Insert one more record

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

   500 	TEST2(err, 1);

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

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

   503 	TEST2(err, KErrNone);

   504 	err = stmt.Next();

   505 	TEST2(err, KSqlAtRow);

   506 	cnt = stmt.ColumnInt(0);

   507 	TEST2(cnt, 1);

   508 	stmt.Close();

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

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

   511 	TEST2(err, KErrNone);

   512 	err = stmt.Next();

   513 	TEST(err != KErrNone);

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

   515 	TPtrC errMsg = db.LastErrorMessage();

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

   517 	stmt.Close();

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

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

   520 	TEST2(err, KErrNone);

   521 	err = stmt.Next();

   522 	TEST(err != KErrNone);

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

   524 	errMsg.Set(db.LastErrorMessage());

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

   526 	stmt.Close();

   527 	//Test case 7 = blank pattern string

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

   529 	TEST2(err, KErrNone);

   530 	err = stmt.Next();

   531 	TEST2(err, KSqlAtRow);

   532 	cnt = stmt.ColumnInt(0);

   533 	TEST2(cnt, 0);

   534 	stmt.Close();

   536 	//Cleanup

   537 	db.Close();

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

   539 	(void)RSqlDatabase::Delete(KTestDbName1);

   540 	}

   542 /**

   543 @SYMTestCaseID			SYSLIB-SQL-CT-1761

   544 @SYMTestCaseDesc		Verifying that all 'LIKE + ESCAPE' test cases which work with the old DBMS component,

   545 						pass successfully with the new SQL component. The test includes positive and negative test cases.

   546 						Not all negative test cases from the old DBMS pass, because the DBMS supports limited implementation of

   547 						the ESCAPE clause. Here the old negative tests were converted to a positive test cases.

   548 @SYMTestPriority		High

   549 @SYMTestActions			Testing SQL LIKE operator and ESCAPE clause.

   550 @SYMTestExpectedResults Test must not fail

   551 @SYMREQ					REQ5907

   552 */

   553 void LikeTest2()

   554 	{

   555 	RSqlDatabase db;

   556 	TInt err = db.Create(KTestDbName1);

   557 	TEST2(err, KErrNone);

   558 	//Create a test table

   559 	err = db.Exec(_L("CREATE TABLE A(Fld1 TEXT, Fld2 TEXT)"));

   560 	TEST(err >= 0);

   561 	//Insert some records

   562 	err = db.Exec(_L("INSERT INTO A(Fld1, Fld2) VALUES('ACDC\\','BLAH')"));	// Rec1

   563 	TEST2(err, 1);

   564 	err = db.Exec(_L("INSERT INTO A(Fld1) VALUES('ABCDEFGH')"));			// Rec2

   565 	TEST2(err, 1);

   566 	err = db.Exec(_L("INSERT INTO A(Fld1) VALUES('A_CDEFGH')"));			// Rec3

   567 	TEST2(err, 1);

   568 	err = db.Exec(_L("INSERT INTO A(Fld1) VALUES('A_%_CDEFGH')"));			// Rec4

   569 	TEST2(err, 1);

   570 	err = db.Exec(_L("INSERT INTO A(Fld1) VALUES('A%CDEFGH')"));			// Rec5

   571 	TEST2(err, 1);

   572 	err = db.Exec(_L("INSERT INTO A(Fld1, Fld2) VALUES('ADCDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOP','ADCB')"));	//Rec6

   573 	TEST2(err, 1);

   574 	err = db.Exec(_L("INSERT INTO A(Fld1) VALUES('XZD\\FZX')"));			// Rec7

   575 	TEST2(err, 1);

   576 	//Prepare SELECT query, step and check the record set content

   577 	TPtrC res;

   578 	//Test 1 - only Rec1 satisfies the WHILE condition

   579 	RSqlStatement stmt;

   580 	err = stmt.Prepare(db, _L("SELECT Fld2 FROM A WHERE  Fld1 LIKE 'ACDC\\' AND Fld2 LIKE '%BL%'"));

   581 	TEST2(err, KErrNone);

   582 	err = stmt.Next();

   583 	TEST2(err, KSqlAtRow);

   584 	err = stmt.ColumnText(0, res);

   585 	TEST2(err, KErrNone);

   586 	TEST(res == _L("BLAH"));

   587 	err = stmt.Next();

   588 	TEST2(err, KSqlAtEnd);

   589 	stmt.Close();

   590 	//Test 2 - only Rec5 satisfies the WHILE condition

   591 	err = stmt.Prepare(db, _L("SELECT Fld1 FROM A WHERE  Fld1 LIKE '%A\\%C%' ESCAPE '\\'"));

   592 	TEST2(err, KErrNone);

   593 	err = stmt.Next();

   594 	TEST2(err, KSqlAtRow);

   595 	err = stmt.ColumnText(0, res);

   596 	TEST2(err, KErrNone);

   597 	TEST(res == _L("A%CDEFGH"));

   598 	err = stmt.Next();

   599 	TEST2(err, KSqlAtEnd);

   600 	stmt.Close();

   601 	//Test 3 - only Rec3 satisfies the WHILE condition

   602 	err = stmt.Prepare(db, _L("SELECT Fld1 FROM A WHERE  Fld1 LIKE '%A\\_C%' ESCAPE '\\'"));

   603 	TEST2(err, KErrNone);

   604 	err = stmt.Next();

   605 	TEST2(err, KSqlAtRow);

   606 	err = stmt.ColumnText(0, res);

   607 	TEST2(err, KErrNone);

   608 	TEST(res == _L("A_CDEFGH"));

   609 	err = stmt.Next();

   610 	TEST2(err, KSqlAtEnd);

   611 	stmt.Close();

   612 	//Test 4 - only Rec4 satisfies the WHILE condition

   613 	err = stmt.Prepare(db, _L("SELECT Fld1 FROM A WHERE  Fld1 LIKE '%A\\_\\%\\_C%' ESCAPE '\\'"));

   614 	TEST2(err, KErrNone);

   615 	err = stmt.Next();

   616 	TEST2(err, KSqlAtRow);

   617 	err = stmt.ColumnText(0, res);

   618 	TEST2(err, KErrNone);

   619 	TEST(res == _L("A_%_CDEFGH"));

   620 	err = stmt.Next();

   621 	TEST2(err, KSqlAtEnd);

   622 	stmt.Close();

   623 	//Test 5 - only Rec6 satisfies the WHILE condition

   624 	err = stmt.Prepare(db, _L("SELECT Fld1, Fld2 FROM A WHERE  Fld1 LIKE '%1234%'"));

   625 	TEST2(err, KErrNone);

   626 	err = stmt.Next();

   627 	TEST2(err, KSqlAtRow);

   628 	err = stmt.ColumnText(0, res);

   629 	TEST2(err, KErrNone);

   630 	TEST(res == _L("ADCDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOP"));

   631 	err = stmt.ColumnText(1, res);

   632 	TEST2(err, KErrNone);

   633 	TEST(res == _L("ADCB"));

   634 	err = stmt.Next();

   635 	TEST2(err, KSqlAtEnd);

   636 	stmt.Close();

   637 	//Test 6 - only Rec1 satisfies the WHILE condition

   638 	err = stmt.Prepare(db, _L("SELECT Fld1, Fld2 FROM A WHERE Fld1 LIKE '%AC%' AND Fld2 LIKE '_LA_'"));

   639 	TEST2(err, KErrNone);

   640 	err = stmt.Next();

   641 	TEST2(err, KSqlAtRow);

   642 	err = stmt.ColumnText(0, res);

   643 	TEST2(err, KErrNone);

   644 	TEST(res == _L("ACDC\\"));

   645 	err = stmt.ColumnText(1, res);

   646 	TEST2(err, KErrNone);

   647 	TEST(res == _L("BLAH"));

   648 	err = stmt.Next();

   649 	TEST2(err, KSqlAtEnd);

   650 	stmt.Close();

   651 	//Test 7 - only Rec1 satisfies the WHILE condition

   652 	err = stmt.Prepare(db, _L("SELECT Fld1, Fld2 FROM A WHERE Fld1 LIKE 'NOTINTABLE' OR Fld2 LIKE '_LA_'"));

   653 	TEST2(err, KErrNone);

   654 	err = stmt.Next();

   655 	TEST2(err, KSqlAtRow);

   656 	err = stmt.ColumnText(0, res);

   657 	TEST2(err, KErrNone);

   658 	TEST(res == _L("ACDC\\"));

   659 	err = stmt.ColumnText(1, res);

   660 	TEST2(err, KErrNone);

   661 	TEST(res == _L("BLAH"));

   662 	err = stmt.Next();

   663 	TEST2(err, KSqlAtEnd);

   664 	stmt.Close();

   665 	//Test 8 - only Rec6 satisfies the WHILE condition

   666 	err = stmt.Prepare(db, _L("SELECT Fld1, Fld2 FROM A WHERE  Fld1 LIKE '%ADC%' AND Fld2 LIKE 'ADC_'"));

   667 	TEST2(err, KErrNone);

   668 	err = stmt.Next();

   669 	TEST2(err, KSqlAtRow);

   670 	err = stmt.ColumnText(0, res);

   671 	TEST2(err, KErrNone);

   672 	TEST(res == _L("ADCDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOP"));

   673 	err = stmt.ColumnText(1, res);

   674 	TEST2(err, KErrNone);

   675 	TEST(res == _L("ADCB"));

   676 	err = stmt.Next();

   677 	TEST2(err, KSqlAtEnd);

   678 	stmt.Close();

   679 	//Test 9 - only Rec5 satisfies the WHILE condition

   680 	err = stmt.Prepare(db, _L("SELECT Fld1 FROM A WHERE  Fld1 LIKE '%\\%C%' ESCAPE '\\'"));

   681 	TEST2(err, KErrNone);

   682 	err = stmt.Next();

   683 	TEST2(err, KSqlAtRow);

   684 	err = stmt.ColumnText(0, res);

   685 	TEST2(err, KErrNone);

   686 	TEST(res == _L("A%CDEFGH"));

   687 	err = stmt.Next();

   688 	TEST2(err, KSqlAtEnd);

   689 	stmt.Close();

   690 	//Test 10 - only Rec7 satisfies the WHILE condition

   691 	err = stmt.Prepare(db, _L("SELECT Fld1 FROM A WHERE  Fld1 LIKE '%D\\\\%' ESCAPE '\\'"));

   692 	TEST2(err, KErrNone);

   693 	err = stmt.Next();

   694 	TEST2(err, KSqlAtRow);

   695 	err = stmt.ColumnText(0, res);

   696 	TEST2(err, KErrNone);

   697 	TEST(res == _L("XZD\\FZX"));

   698 	err = stmt.Next();

   699 	TEST2(err, KSqlAtEnd);

   700 	stmt.Close();

   701 	//Test 11 - only Rec4 satisfies the WHILE condition

   702 	err = stmt.Prepare(db, _L("SELECT * FROM A WHERE  Fld1 LIKE '%A\\__\\_C%' ESCAPE '\\'"));

   703 	TEST2(err, KErrNone);

   704 	err = stmt.Next();

   705 	TEST2(err, KSqlAtRow);

   706 	err = stmt.ColumnText(0, res);

   707 	TEST2(err, KErrNone);

   708 	TEST(res == _L("A_%_CDEFGH"));

   709 	err = stmt.Next();

   710 	TEST2(err, KSqlAtEnd);

   711 	stmt.Close();

   712 	//Test 12 - only Rec5 satisfies the WHILE condition

   713 	err = stmt.Prepare(db, _L("SELECT * FROM A WHERE  Fld1 LIKE '%A%\\%C%' ESCAPE '\\'"));

   714 	TEST2(err, KErrNone);

   715 	err = stmt.Next();

   716 	TEST2(err, KSqlAtRow);

   717 	err = stmt.ColumnText(0, res);

   718 	TEST2(err, KErrNone);

   719 	TEST(res == _L("A%CDEFGH"));

   720 	err = stmt.Next();

   721 	TEST2(err, KSqlAtEnd);

   722 	stmt.Close();

   723 	//Test 13 - only Rec2 satisfies the WHILE condition

   724 	err = stmt.Prepare(db, _L("SELECT * FROM A WHERE  Fld1 LIKE 'ABC%' ESCAPE '\\'"));

   725 	TEST2(err, KErrNone);

   726 	err = stmt.Next();

   727 	TEST2(err, KSqlAtRow);

   728 	err = stmt.ColumnText(0, res);

   729 	TEST2(err, KErrNone);

   730 	TEST(res == _L("ABCDEFGH"));

   731 	err = stmt.Next();

   732 	TEST2(err, KSqlAtEnd);

   733 	stmt.Close();

   734 	//Test 14 - there is no record satisfying the WHILE condition

   735 	err = stmt.Prepare(db, _L("SELECT * FROM A WHERE  Fld1 LIKE 'A_C' ESCAPE '\\'"));

   736 	TEST2(err, KErrNone);

   737 	err = stmt.Next();

   738 	TEST2(err, KSqlAtEnd);

   739 	stmt.Close();

   740 	//Test 15 - there is no record satisfying the WHILE condition

   741 	err = stmt.Prepare(db, _L("SELECT * FROM A WHERE  Fld1 LIKE 'A%C' ESCAPE '\\'"));

   742 	TEST2(err, KErrNone);

   743 	err = stmt.Next();

   744 	TEST2(err, KSqlAtEnd);

   745 	stmt.Close();

   746 	//Test 16 - there is no record satisfying the WHILE condition

   747 	err = stmt.Prepare(db, _L("SELECT * FROM A WHERE  Fld1 LIKE '_A\\_C' ESCAPE '\\'"));

   748 	TEST2(err, KErrNone);

   749 	err = stmt.Next();

   750 	TEST2(err, KSqlAtEnd);

   751 	stmt.Close();

   752 	//Test 17 - there is no record satisfying the WHILE condition

   753 	err = stmt.Prepare(db, _L("SELECT * FROM A WHERE  Fld1 LIKE '_A\\_C_' ESCAPE '\\'"));

   754 	TEST2(err, KErrNone);

   755 	err = stmt.Next();

   756 	TEST2(err, KSqlAtEnd);

   757 	stmt.Close();

   758 	//Test 18 - there is no record satisfying the WHILE condition

   759 	err = stmt.Prepare(db, _L("SELECT * FROM A WHERE  Fld1 LIKE 'ABC' ESCAPE '\\'"));

   760 	TEST2(err, KErrNone);

   761 	err = stmt.Next();

   762 	TEST2(err, KSqlAtEnd);

   763 	stmt.Close();

   764 	//Test 19 - there is no record satisfying the WHILE condition

   765 	err = stmt.Prepare(db, _L("SELECT * FROM A WHERE  Fld1 LIKE '%ABC' ESCAPE '\\'"));

   766 	TEST2(err, KErrNone);

   767 	err = stmt.Next();

   768 	TEST2(err, KSqlAtEnd);

   769 	stmt.Close();

   770 	//Cleanup

   771 	db.Close();

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

   773 	(void)RSqlDatabase::Delete(KTestDbName1);

   774 	}

   776 void DoTestsL()

   777 	{

   778 	TheTest.Start(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1609 Folding & Collation test 1 "));

   779 	CollationTest1L();

   780 	TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1610 Folding & Collation test 2 "));

   781 	CollationTest2L();

   782 	TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1627 Collation test 3 "));

   783 	CollationTest3L();

   784 	TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1760 LIKE & ESCAPE test 1 "));

   785 	LikeTest1();

   786 	TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1761 LIKE & ESCAPE test 2 "));

   787 	LikeTest2();

   788 	}

   790 TInt E32Main()

   791 	{

   792 	TheTest.Title();

   794 	CTrapCleanup* tc = CTrapCleanup::New();

   796 	__UHEAP_MARK;

   798 	CreateTestDir();

   799 	DeleteTestFiles();

   800 	TRAPD(err, DoTestsL());

   801 	DeleteTestFiles();

   802 	TEST2(err, KErrNone);

   804 	__UHEAP_MARKEND;

   806 	TheTest.End();

   807 	TheTest.Close();

   809 	delete tc;

   811 	User::Heap().Check();

   812 	return KErrNone;

   813 	}

author	sl
	Tue, 10 Jun 2014 14:32:02 +0200
changeset 1	260cb5ec6c19
permissions	-rw-r--r--