1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sql/TEST/t_sqlcollate.cpp Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,813 @@
1.4 +// Copyright (c) 2006-2009 Nokia Corporation and/or its subsidiary(-ies).
1.5 +// All rights reserved.
1.6 +// This component and the accompanying materials are made available
1.7 +// under the terms of "Eclipse Public License v1.0"
1.8 +// which accompanies this distribution, and is available
1.9 +// at the URL "http://www.eclipse.org/legal/epl-v10.html".
1.10 +//
1.11 +// Initial Contributors:
1.12 +// Nokia Corporation - initial contribution.
1.13 +//
1.14 +// Contributors:
1.15 +//
1.16 +// Description:
1.17 +//
1.18 +
1.19 +#include <e32test.h>
1.20 +#include <bautils.h>
1.21 +#include <sqldb.h>
1.22 +
1.23 +///////////////////////////////////////////////////////////////////////////////////////
1.24 +
1.25 +RTest TheTest(_L("t_sqlcollate test"));
1.26 +
1.27 +_LIT(KTestDir, "c:\\test\\");
1.28 +_LIT(KTestDbName1, "c:\\test\\t_sqlcollate.db");
1.29 +
1.30 +///////////////////////////////////////////////////////////////////////////////////////
1.31 +
1.32 +void DeleteTestFiles()
1.33 + {
1.34 + RSqlDatabase::Delete(KTestDbName1);
1.35 + }
1.36 +
1.37 +///////////////////////////////////////////////////////////////////////////////////////
1.38 +///////////////////////////////////////////////////////////////////////////////////////
1.39 +//Test macros and functions
1.40 +void Check(TInt aValue, TInt aLine)
1.41 + {
1.42 + if(!aValue)
1.43 + {
1.44 + DeleteTestFiles();
1.45 + TheTest(EFalse, aLine);
1.46 + }
1.47 + }
1.48 +void Check(TInt aValue, TInt aExpected, TInt aLine)
1.49 + {
1.50 + if(aValue != aExpected)
1.51 + {
1.52 + DeleteTestFiles();
1.53 + RDebug::Print(_L("*** Expected error: %d, got: %d\r\n"), aExpected, aValue);
1.54 + TheTest(EFalse, aLine);
1.55 + }
1.56 + }
1.57 +#define TEST(arg) ::Check((arg), __LINE__)
1.58 +#define TEST2(aValue, aExpected) ::Check(aValue, aExpected, __LINE__)
1.59 +
1.60 +///////////////////////////////////////////////////////////////////////////////////////
1.61 +
1.62 +void CreateTestDir()
1.63 + {
1.64 + RFs fs;
1.65 + TInt err = fs.Connect();
1.66 + TEST2(err, KErrNone);
1.67 +
1.68 + err = fs.MkDir(KTestDir);
1.69 + TEST(err == KErrNone || err == KErrAlreadyExists);
1.70 +
1.71 + fs.Close();
1.72 + }
1.73 +
1.74 +///////////////////////////////////////////////////////////////////////////////////////
1.75 +
1.76 +//Compare aLeft and aRight strings using collated comparison, level 3, default collation method.
1.77 +//This function is used when sorting the test names array in CollationTest1L().
1.78 +TInt StrSortC3(const TPtrC& aLeft, const TPtrC& aRight)
1.79 + {
1.80 + return aLeft.CompareC(aRight, 3, NULL);
1.81 + }
1.82 +
1.83 +//Compare aLeft and aRight strings using collated comparison, level 1, default collation method.
1.84 +//This function is used when sorting the test names array in CollationTest3L().
1.85 +TInt StrSortC1(const TPtrC& aLeft, const TPtrC& aRight)
1.86 + {
1.87 + return aLeft.CompareC(aRight, 1, NULL);
1.88 + }
1.89 +
1.90 +///////////////////////////////////////////////////////////////////////////////////////
1.91 +
1.92 +/**
1.93 +@SYMTestCaseID SYSLIB-SQL-CT-1609
1.94 +@SYMTestCaseDesc Create a table with a text column with default collation "CompareF"
1.95 + and insert some records there.
1.96 + Test how the searching operations work executing some SELECT SQL statements.
1.97 +@SYMTestPriority High
1.98 +@SYMTestActions Testing database search operations using "CompareF" and "CompareC3" collations.
1.99 +@SYMTestExpectedResults Test must not fail
1.100 +@SYMREQ REQ5907
1.101 +*/
1.102 +void CollationTest1L()
1.103 + {
1.104 + RSqlDatabase db;
1.105 + TInt err = db.Create(KTestDbName1);
1.106 + TEST2(err, KErrNone);
1.107 +
1.108 + //Create test database
1.109 + RDebug::Print(_L("###Create test database\r\n"));
1.110 + _LIT(KCreateSql, "CREATE TABLE A(Name VARCHAR(100) COLLATE CompareF); CREATE INDEX AIdx ON A(Name COLLATE CompareF);");
1.111 + err = db.Exec(KCreateSql);
1.112 + TEST(err >= 0);
1.113 +
1.114 + //Insert some records. The column "Name" of each record contains the same name but the name characters are
1.115 + //variation of upper/lower case letters.
1.116 + RDebug::Print(_L("###Insert some records\r\n"));
1.117 + _LIT(KInsertSql, "INSERT INTO A(Name) VALUES(");
1.118 + //Collation sort order: KNames[1] KNames[3] KNames[0] KNames[2]
1.119 + //Long "aaaa..." added to the end of each column value because SQLITE may use non-aligned strings
1.120 + //only when the string length is in [32..<cache_page_size>] interval.
1.121 + TPtrC KNames[] = {
1.122 + _L("aLex-aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"),
1.123 + _L("ALeX-aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"),
1.124 + _L("aleX-aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"),
1.125 + _L("Alex-aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa")};
1.126 + const TInt KInsertSqlStmtCnt = sizeof(KNames)/sizeof(KNames[0]);
1.127 + RArray<TPtrC> sortedNames;
1.128 + TLinearOrder<TPtrC> order(&StrSortC3);
1.129 +
1.130 + for(TInt i=0;i<KInsertSqlStmtCnt;++i)
1.131 + {
1.132 + err = sortedNames.InsertInOrder(KNames[i], order);
1.133 + TEST2(err, KErrNone);
1.134 +
1.135 + TBuf<128> sql(KInsertSql);
1.136 + sql.Append(_L("'"));
1.137 + sql.Append(KNames[i]);
1.138 + sql.Append(_L("')"));
1.139 + err = db.Exec(sql);
1.140 + TEST2(err, 1);
1.141 + }
1.142 +
1.143 + //The next "SELECT" statement must return a set containing all table records
1.144 + RDebug::Print(_L("###Select all records\r\n"));
1.145 + _LIT(KSelectSql1, "SELECT * FROM A WHERE NAME = 'alex-aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'");
1.146 + RSqlStatement stmt;
1.147 + err = stmt.Prepare(db, KSelectSql1);
1.148 + TEST2(err, KErrNone);
1.149 + TInt recCount = 0;
1.150 + while(stmt.Next() == KSqlAtRow)
1.151 + {
1.152 + ++recCount;
1.153 + TPtrC name = stmt.ColumnTextL(0);
1.154 + RDebug::Print(_L("%S\r\n"), &name);
1.155 + }
1.156 + stmt.Close();
1.157 + TEST(recCount == KInsertSqlStmtCnt);
1.158 +
1.159 + //The next "SELECT" statement must return a set containing all table records
1.160 + // this tests a LIKE clause with a bound parameter (with wildcards)
1.161 + RDebug::Print(_L("###Select all records (LIKE with wildcard)\r\n"));
1.162 + _LIT(KSelectSql1a, "SELECT * FROM A WHERE NAME LIKE :Val");
1.163 + _LIT(KSearchString,"alex-aaaa%");
1.164 + err = stmt.Prepare(db, KSelectSql1a);
1.165 + TEST2(err, KErrNone);
1.166 + TInt idx=stmt.ParameterIndex(_L(":Val"));
1.167 + err=stmt.BindText(idx,KSearchString);
1.168 + TEST2(err, KErrNone);
1.169 + recCount = 0;
1.170 + while(stmt.Next() == KSqlAtRow)
1.171 + {
1.172 + ++recCount;
1.173 + TPtrC name = stmt.ColumnTextL(0);
1.174 + RDebug::Print(_L("%S\r\n"), &name);
1.175 + }
1.176 + stmt.Close();
1.177 + TEST(recCount == KInsertSqlStmtCnt);
1.178 +
1.179 + //The next "SELECT" statement must return a set containing all table records
1.180 + // this tests a LIKE clause with a bound parameter (with no wildcards)
1.181 + RDebug::Print(_L("###Select all records (LIKE with no wildcard)\r\n"));
1.182 + _LIT(KSelectSql1b, "SELECT * FROM A WHERE NAME LIKE :Val");
1.183 + _LIT(KSearchStringA,
1.184 +"alex-aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa");
1.185 + err = stmt.Prepare(db, KSelectSql1b);
1.186 + idx=stmt.ParameterIndex(_L(":Val"));
1.187 + TEST2(err, KErrNone);
1.188 + err=stmt.BindText(idx,KSearchStringA);
1.189 + recCount = 0;
1.190 + while(stmt.Next() == KSqlAtRow)
1.191 + {
1.192 + ++recCount;
1.193 + TPtrC name = stmt.ColumnTextL(0);
1.194 + RDebug::Print(_L("%S\r\n"), &name);
1.195 + }
1.196 + stmt.Close();
1.197 + TEST(recCount == KInsertSqlStmtCnt);
1.198 +
1.199 + //The next "SELECT" statement must return a row
1.200 + // this tests a LIKE clause with a bound parameter and funny characters
1.201 + RDebug::Print(_L("###Select one records (LIKE with bound param with URL chars)\r\n"));
1.202 + err=db.Exec(_L("INSERT INTO A(Name) VALUES('http://a.b.c#d')"));
1.203 + TEST2(err,1);
1.204 + _LIT(KSelectSql1c, "SELECT * FROM A WHERE NAME LIKE :Val");
1.205 + _LIT(KSearchStringB,"http%");
1.206 + err = stmt.Prepare(db, KSelectSql1c);
1.207 + idx=stmt.ParameterIndex(_L(":Val"));
1.208 + TEST2(err, KErrNone);
1.209 + err=stmt.BindText(idx,KSearchStringB);
1.210 + recCount = 0;
1.211 + while(stmt.Next() == KSqlAtRow)
1.212 + {
1.213 + ++recCount;
1.214 + TPtrC name = stmt.ColumnTextL(0);
1.215 + RDebug::Print(_L("%S\r\n"), &name);
1.216 + }
1.217 + stmt.Close();
1.218 + TEST(recCount == 1);
1.219 +
1.220 +
1.221 + //The next "SELECT" statement must return a set containing all table records, folded comparison used for sorting
1.222 + RDebug::Print(_L("###Select all records, folded string comparison\r\n"));
1.223 + _LIT(KSelectSql2, "SELECT * FROM A WHERE NAME = 'alex-aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ORDER BY NAME COLLATE CompareF");
1.224 + err = stmt.Prepare(db, KSelectSql2);
1.225 + TEST2(err, KErrNone);
1.226 +
1.227 + recCount = 0;
1.228 + for(TInt j=0;j<KInsertSqlStmtCnt;++j)
1.229 + {
1.230 + err = stmt.Next();
1.231 + TEST2(err, KSqlAtRow);
1.232 + ++recCount;
1.233 + TPtrC name = stmt.ColumnTextL(0);
1.234 + RDebug::Print(_L("%S\r\n"), &name);
1.235 + TEST(name == KNames[j]);
1.236 + }
1.237 + stmt.Close();
1.238 + TEST(recCount == KInsertSqlStmtCnt);
1.239 +
1.240 + //The next "SELECT" statement must return a set containing all table records, collated comparison used for sorting
1.241 + RDebug::Print(_L("###Select all records, collated string comparison\r\n"));
1.242 + _LIT(KSelectSql3, "SELECT * FROM A ORDER BY NAME COLLATE CompareC3");
1.243 + err = stmt.Prepare(db, KSelectSql3);
1.244 + TEST2(err, KErrNone);
1.245 +
1.246 + for(TInt k=0;k<KInsertSqlStmtCnt;++k)
1.247 + {
1.248 + err = stmt.Next();
1.249 + TEST2(err, KSqlAtRow);
1.250 + TPtrC name = stmt.ColumnTextL(0);
1.251 + RDebug::Print(_L("%S\r\n"), &name);
1.252 + TEST(name == sortedNames[k]);
1.253 + }
1.254 +
1.255 + stmt.Close();
1.256 +
1.257 + //Cleanup
1.258 + sortedNames.Close();
1.259 + db.Close();
1.260 +
1.261 + //To debug database reindexing
1.262 + err = db.Open(KTestDbName1);
1.263 + TEST2(err, KErrNone);
1.264 + db.Close();
1.265 +
1.266 + RDebug::Print(_L("###Delete test database\r\n"));
1.267 + (void)RSqlDatabase::Delete(KTestDbName1);
1.268 + }
1.269 +
1.270 +/**
1.271 +@SYMTestCaseID SYSLIB-SQL-CT-1610
1.272 +@SYMTestCaseDesc Create a table with a text column with default collation "CompareC3"
1.273 + and insert some records there.
1.274 + Test how the searching operations work executing some SELECT SQL statements.
1.275 +@SYMTestPriority High
1.276 +@SYMTestActions Testing database sorting operations using "CompareC3" collation.
1.277 +@SYMTestExpectedResults Test must not fail
1.278 +@SYMREQ REQ5907
1.279 +*/
1.280 +void CollationTest2L()
1.281 + {
1.282 + RSqlDatabase db;
1.283 + TInt err = db.Create(KTestDbName1);
1.284 + TEST2(err, KErrNone);
1.285 +
1.286 + //Create test database
1.287 + RDebug::Print(_L("###Create test database\r\n"));
1.288 + _LIT(KCreateSql, "CREATE TABLE A(Name VARCHAR(100) COLLATE CompareC3)");
1.289 + err = db.Exec(KCreateSql);
1.290 + TEST(err >= 0);
1.291 +
1.292 + //Insert some records.
1.293 + RDebug::Print(_L("###Insert some records\r\n"));
1.294 + _LIT(KInsertSql, "INSERT INTO A(Name) VALUES(");
1.295 + TPtrC KNames[] = {
1.296 + _L("aAaA"),
1.297 + _L("AAaa"),
1.298 + _L("aaAA"),
1.299 + _L("aaaA")};
1.300 + const TInt KInsertSqlStmtCnt = sizeof(KNames)/sizeof(KNames[0]);
1.301 +
1.302 + for(TInt i=0;i<KInsertSqlStmtCnt;++i)
1.303 + {
1.304 + TBuf<128> sql(KInsertSql);
1.305 + sql.Append(_L("'"));
1.306 + sql.Append(KNames[i]);
1.307 + sql.Append(_L("')"));
1.308 + err = db.Exec(sql);
1.309 + TEST2(err, 1);
1.310 + }
1.311 +
1.312 + RSqlStatement stmt;
1.313 +
1.314 + //The next "SELECT" statement must return a set containing all table
1.315 + //records which Name column value is bigger than "aaAA"
1.316 + RDebug::Print(_L("###Select all records, which Name column value is bigger than 'aaAA'\r\n"));
1.317 + _LIT(KSelectSql2, "SELECT * FROM A WHERE NAME > 'aaAA'");
1.318 + err = stmt.Prepare(db, KSelectSql2);
1.319 + TEST2(err, KErrNone);
1.320 +
1.321 + while((err = stmt.Next()) == KSqlAtRow)
1.322 + {
1.323 + TPtrC name = stmt.ColumnTextL(0);
1.324 + RDebug::Print(_L("%S\r\n"), &name);
1.325 + TInt res = name.CompareC(KNames[2], 3, NULL);
1.326 + TEST(res > 0);
1.327 + }
1.328 + stmt.Close();
1.329 + TEST2(err, KSqlAtEnd);
1.330 +
1.331 + //Cleanup
1.332 + db.Close();
1.333 + RDebug::Print(_L("###Delete test database\r\n"));
1.334 + (void)RSqlDatabase::Delete(KTestDbName1);
1.335 + }
1.336 +
1.337 +/**
1.338 +@SYMTestCaseID SYSLIB-SQL-CT-1627
1.339 +@SYMTestCaseDesc Create a table with a text column "NAME" with default collation "CompareC0"
1.340 + and insert some records there. All inserted names are equal if compared at
1.341 + collation level 0, but some of them contain accented letters.
1.342 + Test how the searching operations work executing some SELECT SQL statements.
1.343 + Test how the sorting operations work executing some SELECT SQL statements.
1.344 +@SYMTestPriority High
1.345 +@SYMTestActions Testing database sorting operations using "CompareC0", "CompareC1", and "CompareC2" collations.
1.346 +@SYMTestExpectedResults Test must not fail
1.347 +@SYMREQ REQ5907
1.348 +*/
1.349 +void CollationTest3L()
1.350 + {
1.351 + RSqlDatabase db;
1.352 + TInt err = db.Create(KTestDbName1);
1.353 + TEST2(err, KErrNone);
1.354 +
1.355 + //Create test database
1.356 + RDebug::Print(_L("###Create test database\r\n"));
1.357 + _LIT(KCreateSql, "CREATE TABLE A(Name VARCHAR(100) COLLATE CompareC0)");
1.358 + err = db.Exec(KCreateSql);
1.359 + TEST(err >= 0);
1.360 +
1.361 + //Insert some records. Some of the inserted names have accented letters.
1.362 + //But all names are equal if compared at collation level 0.
1.363 + RDebug::Print(_L("###Insert some records\r\n"));
1.364 + _LIT(KInsertSql, "INSERT INTO A(Name) VALUES(");
1.365 + TBuf<10> name1(_L("Dvorak"));
1.366 + TBuf<10> name2;
1.367 + name2.SetLength(6);
1.368 + name2[0] = TChar('D');
1.369 + name2[1] = TChar('v');
1.370 + name2[2] = TChar('o');
1.371 + name2[3] = 0x0158;//LATIN SMALL LETTER R HACEK
1.372 + name2[4] = 0x00C1;//LATIN SMALL LETTER A ACUTE
1.373 + name2[5] = TChar('k');
1.374 + const TPtrC KNames[] = {name1, name2};
1.375 + const TInt KInsertSqlStmtCnt = sizeof(KNames)/sizeof(KNames[0]);
1.376 + RArray<TPtrC> sortedNames;
1.377 + TLinearOrder<TPtrC> order(&StrSortC1);
1.378 +
1.379 + //Insert the records. Also, check how many names are equal to 'dvorak' using collation level 0.
1.380 + _LIT(KTestName, "dvorak");
1.381 + TInt matchNameCnt = 0;
1.382 + for(TInt i=0;i<KInsertSqlStmtCnt;++i)
1.383 + {
1.384 + if(KNames[i].CompareC(KTestName, 0, NULL) == 0)
1.385 + {
1.386 + ++matchNameCnt;
1.387 + }
1.388 +
1.389 + err = sortedNames.InsertInOrder(KNames[i], order);
1.390 + TEST2(err, KErrNone);
1.391 +
1.392 + TBuf<128> sql(KInsertSql);
1.393 + sql.Append(_L("'"));
1.394 + sql.Append(KNames[i]);
1.395 + sql.Append(_L("')"));
1.396 + err = db.Exec(sql);
1.397 + TEST2(err, 1);
1.398 + }
1.399 +
1.400 + //The next "SELECT" statement must return a set, which record count must be matchNameCnt.
1.401 + RDebug::Print(_L("###Select all records, collated string comparison, level 0\r\n"));
1.402 + _LIT(KSelectSql2, "SELECT * FROM A WHERE NAME = 'dvorak'");
1.403 + RSqlStatement stmt;
1.404 + err = stmt.Prepare(db, KSelectSql2);
1.405 + TEST2(err, KErrNone);
1.406 +
1.407 + TInt recCount = 0;
1.408 + while((err = stmt.Next()) == KSqlAtRow)
1.409 + {
1.410 + TPtrC name = stmt.ColumnTextL(0);
1.411 + RDebug::Print(_L("%S\r\n"), &name);
1.412 + TEST(name == KNames[recCount]);
1.413 + ++recCount;
1.414 + }
1.415 + stmt.Close();
1.416 + TEST(recCount == matchNameCnt);
1.417 +
1.418 + //The next "SELECT" statement must return an ordered set containing all table records.
1.419 + RDebug::Print(_L("###Select all records, collated string comparison, level 1\r\n"));
1.420 + _LIT(KSelectSql3, "SELECT * FROM A WHERE NAME = 'dvorak' ORDER BY NAME COLLATE CompareC1 DESC");
1.421 + err = stmt.Prepare(db, KSelectSql3);
1.422 + TEST2(err, KErrNone);
1.423 +
1.424 + for(TInt k=0;k<KInsertSqlStmtCnt;++k)
1.425 + {
1.426 + err = stmt.Next();
1.427 + TEST2(err, KSqlAtRow);
1.428 + TPtrC name = stmt.ColumnTextL(0);
1.429 + RDebug::Print(_L("%S %S\r\n"), &name, &sortedNames[k]);
1.430 + TEST(name == sortedNames[KInsertSqlStmtCnt - k - 1]);//descending order
1.431 + }
1.432 + stmt.Close();
1.433 +
1.434 + //CompareC2 collation used in the SELECT statement
1.435 + err = stmt.Prepare(db, _L("SELECT NAME FROM A WHERE NAME = 'Dvorak' COLLATE CompareC2"));
1.436 + TEST2(err, KErrNone);
1.437 + err = stmt.Next();
1.438 + TEST2(err, KSqlAtRow);
1.439 + TPtrC name3 = stmt.ColumnTextL(0);
1.440 + TEST(name3 == name1);
1.441 + err = stmt.Next();
1.442 + TEST2(err, KSqlAtEnd);
1.443 + stmt.Close();
1.444 +
1.445 + //Cleanup
1.446 + sortedNames.Close();
1.447 + db.Close();
1.448 + RDebug::Print(_L("###Delete test database\r\n"));
1.449 + (void)RSqlDatabase::Delete(KTestDbName1);
1.450 + }
1.451 +
1.452 +/**
1.453 +@SYMTestCaseID SYSLIB-SQL-CT-1760
1.454 +@SYMTestCaseDesc Creating a test table with a text field. Inserting some records there and
1.455 + testing how LIKE operator works. The test cases include: accented text column values,
1.456 + using '_' and '%' wild cards, using ESCAPE clause, blank pattern string, blank ESCAPE
1.457 + string, multi-character ESCAPE string.
1.458 +@SYMTestPriority High
1.459 +@SYMTestActions Testing SQL LIKE operator and ESCAPE clause.
1.460 +@SYMTestExpectedResults Test must not fail
1.461 +@SYMREQ REQ5907
1.462 +*/
1.463 +void LikeTest1()
1.464 + {
1.465 + RSqlDatabase db;
1.466 + TInt err = db.Create(KTestDbName1);
1.467 + TEST2(err, KErrNone);
1.468 + //Create a test table and insert some records
1.469 + err = db.Exec(_L("CREATE TABLE A(Id INTEGER PRIMARY KEY, Name TEXT)"));
1.470 + TEST(err >= 0);
1.471 + err = db.Exec(_L("INSERT INTO A(Id, Name) VALUES(1, 'Dvorak')"));
1.472 + TEST2(err, 1);
1.473 + err = db.Exec(_L("INSERT INTO A(Id, Name) VALUES(2, 'Dvorák')"));
1.474 + TEST2(err, 1);
1.475 + //Create a statement object and issue a SELECT SQL statement + LIKE clause
1.476 + //Test case 1 = full name search with LIKE
1.477 + RSqlStatement stmt;
1.478 + err = stmt.Prepare(db, _L("SELECT COUNT(*) FROM A WHERE Name LIKE 'DVORAK'"));
1.479 + TEST2(err, KErrNone);
1.480 + err = stmt.Next();
1.481 + TEST2(err, KSqlAtRow);
1.482 + TInt cnt = stmt.ColumnInt(0);
1.483 + TEST2(cnt, 2);
1.484 + stmt.Close();
1.485 + //Test case 2 = wild card used sequence character in the search pattern + LIKE
1.486 + err = stmt.Prepare(db, _L("SELECT COUNT(*) FROM A WHERE Name LIKE '%RA%'"));
1.487 + TEST2(err, KErrNone);
1.488 + err = stmt.Next();
1.489 + TEST2(err, KSqlAtRow);
1.490 + cnt = stmt.ColumnInt(0);
1.491 + TEST2(cnt, 2);
1.492 + stmt.Close();
1.493 + //Test case 3 = wild card character used in the search pattern + LIKE
1.494 + err = stmt.Prepare(db, _L("SELECT COUNT(*) FROM A WHERE Name LIKE 'DV___K'"));
1.495 + TEST2(err, KErrNone);
1.496 + err = stmt.Next();
1.497 + TEST2(err, KSqlAtRow);
1.498 + cnt = stmt.ColumnInt(0);
1.499 + TEST2(cnt, 2);
1.500 + stmt.Close();
1.501 + //Insert one more record
1.502 + err = db.Exec(_L("INSERT INTO A(Id, Name) VALUES(3, 't_sqltest')"));
1.503 + TEST2(err, 1);
1.504 + //Test case 4 = wild card character used in the search pattern + LIKE + ESCAPE
1.505 + err = stmt.Prepare(db, _L("SELECT COUNT(*) FROM A WHERE Name LIKE 't/_sqlte__' ESCAPE '/'"));
1.506 + TEST2(err, KErrNone);
1.507 + err = stmt.Next();
1.508 + TEST2(err, KSqlAtRow);
1.509 + cnt = stmt.ColumnInt(0);
1.510 + TEST2(cnt, 1);
1.511 + stmt.Close();
1.512 + //Test case 5 = wild card character used in the search pattern + LIKE + ESCAPE without an escape character
1.513 + err = stmt.Prepare(db, _L("SELECT COUNT(*) FROM A WHERE Name LIKE 't/_sqltest' ESCAPE ''"));
1.514 + TEST2(err, KErrNone);
1.515 + err = stmt.Next();
1.516 + TEST(err != KErrNone);
1.517 + TEST2(::SqlRetCodeClass(err), ESqlDbError);
1.518 + TPtrC errMsg = db.LastErrorMessage();
1.519 + RDebug::Print(_L("!! error=\"%S\"\r\n"), &errMsg);
1.520 + stmt.Close();
1.521 + //Test case 6 = wild card character used in the search pattern + LIKE + ESCAPE with more than one escape characters
1.522 + err = stmt.Prepare(db, _L("SELECT COUNT(*) FROM A WHERE Name LIKE 't/_sqltest' ESCAPE '1234'"));
1.523 + TEST2(err, KErrNone);
1.524 + err = stmt.Next();
1.525 + TEST(err != KErrNone);
1.526 + TEST2(::SqlRetCodeClass(err), ESqlDbError);
1.527 + errMsg.Set(db.LastErrorMessage());
1.528 + RDebug::Print(_L("!! error=\"%S\"\r\n"), &errMsg);
1.529 + stmt.Close();
1.530 + //Test case 7 = blank pattern string
1.531 + err = stmt.Prepare(db, _L("SELECT COUNT(*) FROM A WHERE Name LIKE ''"));
1.532 + TEST2(err, KErrNone);
1.533 + err = stmt.Next();
1.534 + TEST2(err, KSqlAtRow);
1.535 + cnt = stmt.ColumnInt(0);
1.536 + TEST2(cnt, 0);
1.537 + stmt.Close();
1.538 +
1.539 + //Cleanup
1.540 + db.Close();
1.541 + RDebug::Print(_L("###Delete test database\r\n"));
1.542 + (void)RSqlDatabase::Delete(KTestDbName1);
1.543 + }
1.544 +
1.545 +/**
1.546 +@SYMTestCaseID SYSLIB-SQL-CT-1761
1.547 +@SYMTestCaseDesc Verifying that all 'LIKE + ESCAPE' test cases which work with the old DBMS component,
1.548 + pass successfully with the new SQL component. The test includes positive and negative test cases.
1.549 + Not all negative test cases from the old DBMS pass, because the DBMS supports limited implementation of
1.550 + the ESCAPE clause. Here the old negative tests were converted to a positive test cases.
1.551 +@SYMTestPriority High
1.552 +@SYMTestActions Testing SQL LIKE operator and ESCAPE clause.
1.553 +@SYMTestExpectedResults Test must not fail
1.554 +@SYMREQ REQ5907
1.555 +*/
1.556 +void LikeTest2()
1.557 + {
1.558 + RSqlDatabase db;
1.559 + TInt err = db.Create(KTestDbName1);
1.560 + TEST2(err, KErrNone);
1.561 + //Create a test table
1.562 + err = db.Exec(_L("CREATE TABLE A(Fld1 TEXT, Fld2 TEXT)"));
1.563 + TEST(err >= 0);
1.564 + //Insert some records
1.565 + err = db.Exec(_L("INSERT INTO A(Fld1, Fld2) VALUES('ACDC\\','BLAH')")); // Rec1
1.566 + TEST2(err, 1);
1.567 + err = db.Exec(_L("INSERT INTO A(Fld1) VALUES('ABCDEFGH')")); // Rec2
1.568 + TEST2(err, 1);
1.569 + err = db.Exec(_L("INSERT INTO A(Fld1) VALUES('A_CDEFGH')")); // Rec3
1.570 + TEST2(err, 1);
1.571 + err = db.Exec(_L("INSERT INTO A(Fld1) VALUES('A_%_CDEFGH')")); // Rec4
1.572 + TEST2(err, 1);
1.573 + err = db.Exec(_L("INSERT INTO A(Fld1) VALUES('A%CDEFGH')")); // Rec5
1.574 + TEST2(err, 1);
1.575 + err = db.Exec(_L("INSERT INTO A(Fld1, Fld2) VALUES('ADCDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOP','ADCB')")); //Rec6
1.576 + TEST2(err, 1);
1.577 + err = db.Exec(_L("INSERT INTO A(Fld1) VALUES('XZD\\FZX')")); // Rec7
1.578 + TEST2(err, 1);
1.579 + //Prepare SELECT query, step and check the record set content
1.580 + TPtrC res;
1.581 + //Test 1 - only Rec1 satisfies the WHILE condition
1.582 + RSqlStatement stmt;
1.583 + err = stmt.Prepare(db, _L("SELECT Fld2 FROM A WHERE Fld1 LIKE 'ACDC\\' AND Fld2 LIKE '%BL%'"));
1.584 + TEST2(err, KErrNone);
1.585 + err = stmt.Next();
1.586 + TEST2(err, KSqlAtRow);
1.587 + err = stmt.ColumnText(0, res);
1.588 + TEST2(err, KErrNone);
1.589 + TEST(res == _L("BLAH"));
1.590 + err = stmt.Next();
1.591 + TEST2(err, KSqlAtEnd);
1.592 + stmt.Close();
1.593 + //Test 2 - only Rec5 satisfies the WHILE condition
1.594 + err = stmt.Prepare(db, _L("SELECT Fld1 FROM A WHERE Fld1 LIKE '%A\\%C%' ESCAPE '\\'"));
1.595 + TEST2(err, KErrNone);
1.596 + err = stmt.Next();
1.597 + TEST2(err, KSqlAtRow);
1.598 + err = stmt.ColumnText(0, res);
1.599 + TEST2(err, KErrNone);
1.600 + TEST(res == _L("A%CDEFGH"));
1.601 + err = stmt.Next();
1.602 + TEST2(err, KSqlAtEnd);
1.603 + stmt.Close();
1.604 + //Test 3 - only Rec3 satisfies the WHILE condition
1.605 + err = stmt.Prepare(db, _L("SELECT Fld1 FROM A WHERE Fld1 LIKE '%A\\_C%' ESCAPE '\\'"));
1.606 + TEST2(err, KErrNone);
1.607 + err = stmt.Next();
1.608 + TEST2(err, KSqlAtRow);
1.609 + err = stmt.ColumnText(0, res);
1.610 + TEST2(err, KErrNone);
1.611 + TEST(res == _L("A_CDEFGH"));
1.612 + err = stmt.Next();
1.613 + TEST2(err, KSqlAtEnd);
1.614 + stmt.Close();
1.615 + //Test 4 - only Rec4 satisfies the WHILE condition
1.616 + err = stmt.Prepare(db, _L("SELECT Fld1 FROM A WHERE Fld1 LIKE '%A\\_\\%\\_C%' ESCAPE '\\'"));
1.617 + TEST2(err, KErrNone);
1.618 + err = stmt.Next();
1.619 + TEST2(err, KSqlAtRow);
1.620 + err = stmt.ColumnText(0, res);
1.621 + TEST2(err, KErrNone);
1.622 + TEST(res == _L("A_%_CDEFGH"));
1.623 + err = stmt.Next();
1.624 + TEST2(err, KSqlAtEnd);
1.625 + stmt.Close();
1.626 + //Test 5 - only Rec6 satisfies the WHILE condition
1.627 + err = stmt.Prepare(db, _L("SELECT Fld1, Fld2 FROM A WHERE Fld1 LIKE '%1234%'"));
1.628 + TEST2(err, KErrNone);
1.629 + err = stmt.Next();
1.630 + TEST2(err, KSqlAtRow);
1.631 + err = stmt.ColumnText(0, res);
1.632 + TEST2(err, KErrNone);
1.633 + TEST(res == _L("ADCDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOP"));
1.634 + err = stmt.ColumnText(1, res);
1.635 + TEST2(err, KErrNone);
1.636 + TEST(res == _L("ADCB"));
1.637 + err = stmt.Next();
1.638 + TEST2(err, KSqlAtEnd);
1.639 + stmt.Close();
1.640 + //Test 6 - only Rec1 satisfies the WHILE condition
1.641 + err = stmt.Prepare(db, _L("SELECT Fld1, Fld2 FROM A WHERE Fld1 LIKE '%AC%' AND Fld2 LIKE '_LA_'"));
1.642 + TEST2(err, KErrNone);
1.643 + err = stmt.Next();
1.644 + TEST2(err, KSqlAtRow);
1.645 + err = stmt.ColumnText(0, res);
1.646 + TEST2(err, KErrNone);
1.647 + TEST(res == _L("ACDC\\"));
1.648 + err = stmt.ColumnText(1, res);
1.649 + TEST2(err, KErrNone);
1.650 + TEST(res == _L("BLAH"));
1.651 + err = stmt.Next();
1.652 + TEST2(err, KSqlAtEnd);
1.653 + stmt.Close();
1.654 + //Test 7 - only Rec1 satisfies the WHILE condition
1.655 + err = stmt.Prepare(db, _L("SELECT Fld1, Fld2 FROM A WHERE Fld1 LIKE 'NOTINTABLE' OR Fld2 LIKE '_LA_'"));
1.656 + TEST2(err, KErrNone);
1.657 + err = stmt.Next();
1.658 + TEST2(err, KSqlAtRow);
1.659 + err = stmt.ColumnText(0, res);
1.660 + TEST2(err, KErrNone);
1.661 + TEST(res == _L("ACDC\\"));
1.662 + err = stmt.ColumnText(1, res);
1.663 + TEST2(err, KErrNone);
1.664 + TEST(res == _L("BLAH"));
1.665 + err = stmt.Next();
1.666 + TEST2(err, KSqlAtEnd);
1.667 + stmt.Close();
1.668 + //Test 8 - only Rec6 satisfies the WHILE condition
1.669 + err = stmt.Prepare(db, _L("SELECT Fld1, Fld2 FROM A WHERE Fld1 LIKE '%ADC%' AND Fld2 LIKE 'ADC_'"));
1.670 + TEST2(err, KErrNone);
1.671 + err = stmt.Next();
1.672 + TEST2(err, KSqlAtRow);
1.673 + err = stmt.ColumnText(0, res);
1.674 + TEST2(err, KErrNone);
1.675 + TEST(res == _L("ADCDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOPQRSTUVWXYZ123456789ACDEFGHIJKLMNOP"));
1.676 + err = stmt.ColumnText(1, res);
1.677 + TEST2(err, KErrNone);
1.678 + TEST(res == _L("ADCB"));
1.679 + err = stmt.Next();
1.680 + TEST2(err, KSqlAtEnd);
1.681 + stmt.Close();
1.682 + //Test 9 - only Rec5 satisfies the WHILE condition
1.683 + err = stmt.Prepare(db, _L("SELECT Fld1 FROM A WHERE Fld1 LIKE '%\\%C%' ESCAPE '\\'"));
1.684 + TEST2(err, KErrNone);
1.685 + err = stmt.Next();
1.686 + TEST2(err, KSqlAtRow);
1.687 + err = stmt.ColumnText(0, res);
1.688 + TEST2(err, KErrNone);
1.689 + TEST(res == _L("A%CDEFGH"));
1.690 + err = stmt.Next();
1.691 + TEST2(err, KSqlAtEnd);
1.692 + stmt.Close();
1.693 + //Test 10 - only Rec7 satisfies the WHILE condition
1.694 + err = stmt.Prepare(db, _L("SELECT Fld1 FROM A WHERE Fld1 LIKE '%D\\\\%' ESCAPE '\\'"));
1.695 + TEST2(err, KErrNone);
1.696 + err = stmt.Next();
1.697 + TEST2(err, KSqlAtRow);
1.698 + err = stmt.ColumnText(0, res);
1.699 + TEST2(err, KErrNone);
1.700 + TEST(res == _L("XZD\\FZX"));
1.701 + err = stmt.Next();
1.702 + TEST2(err, KSqlAtEnd);
1.703 + stmt.Close();
1.704 + //Test 11 - only Rec4 satisfies the WHILE condition
1.705 + err = stmt.Prepare(db, _L("SELECT * FROM A WHERE Fld1 LIKE '%A\\__\\_C%' ESCAPE '\\'"));
1.706 + TEST2(err, KErrNone);
1.707 + err = stmt.Next();
1.708 + TEST2(err, KSqlAtRow);
1.709 + err = stmt.ColumnText(0, res);
1.710 + TEST2(err, KErrNone);
1.711 + TEST(res == _L("A_%_CDEFGH"));
1.712 + err = stmt.Next();
1.713 + TEST2(err, KSqlAtEnd);
1.714 + stmt.Close();
1.715 + //Test 12 - only Rec5 satisfies the WHILE condition
1.716 + err = stmt.Prepare(db, _L("SELECT * FROM A WHERE Fld1 LIKE '%A%\\%C%' ESCAPE '\\'"));
1.717 + TEST2(err, KErrNone);
1.718 + err = stmt.Next();
1.719 + TEST2(err, KSqlAtRow);
1.720 + err = stmt.ColumnText(0, res);
1.721 + TEST2(err, KErrNone);
1.722 + TEST(res == _L("A%CDEFGH"));
1.723 + err = stmt.Next();
1.724 + TEST2(err, KSqlAtEnd);
1.725 + stmt.Close();
1.726 + //Test 13 - only Rec2 satisfies the WHILE condition
1.727 + err = stmt.Prepare(db, _L("SELECT * FROM A WHERE Fld1 LIKE 'ABC%' ESCAPE '\\'"));
1.728 + TEST2(err, KErrNone);
1.729 + err = stmt.Next();
1.730 + TEST2(err, KSqlAtRow);
1.731 + err = stmt.ColumnText(0, res);
1.732 + TEST2(err, KErrNone);
1.733 + TEST(res == _L("ABCDEFGH"));
1.734 + err = stmt.Next();
1.735 + TEST2(err, KSqlAtEnd);
1.736 + stmt.Close();
1.737 + //Test 14 - there is no record satisfying the WHILE condition
1.738 + err = stmt.Prepare(db, _L("SELECT * FROM A WHERE Fld1 LIKE 'A_C' ESCAPE '\\'"));
1.739 + TEST2(err, KErrNone);
1.740 + err = stmt.Next();
1.741 + TEST2(err, KSqlAtEnd);
1.742 + stmt.Close();
1.743 + //Test 15 - there is no record satisfying the WHILE condition
1.744 + err = stmt.Prepare(db, _L("SELECT * FROM A WHERE Fld1 LIKE 'A%C' ESCAPE '\\'"));
1.745 + TEST2(err, KErrNone);
1.746 + err = stmt.Next();
1.747 + TEST2(err, KSqlAtEnd);
1.748 + stmt.Close();
1.749 + //Test 16 - there is no record satisfying the WHILE condition
1.750 + err = stmt.Prepare(db, _L("SELECT * FROM A WHERE Fld1 LIKE '_A\\_C' ESCAPE '\\'"));
1.751 + TEST2(err, KErrNone);
1.752 + err = stmt.Next();
1.753 + TEST2(err, KSqlAtEnd);
1.754 + stmt.Close();
1.755 + //Test 17 - there is no record satisfying the WHILE condition
1.756 + err = stmt.Prepare(db, _L("SELECT * FROM A WHERE Fld1 LIKE '_A\\_C_' ESCAPE '\\'"));
1.757 + TEST2(err, KErrNone);
1.758 + err = stmt.Next();
1.759 + TEST2(err, KSqlAtEnd);
1.760 + stmt.Close();
1.761 + //Test 18 - there is no record satisfying the WHILE condition
1.762 + err = stmt.Prepare(db, _L("SELECT * FROM A WHERE Fld1 LIKE 'ABC' ESCAPE '\\'"));
1.763 + TEST2(err, KErrNone);
1.764 + err = stmt.Next();
1.765 + TEST2(err, KSqlAtEnd);
1.766 + stmt.Close();
1.767 + //Test 19 - there is no record satisfying the WHILE condition
1.768 + err = stmt.Prepare(db, _L("SELECT * FROM A WHERE Fld1 LIKE '%ABC' ESCAPE '\\'"));
1.769 + TEST2(err, KErrNone);
1.770 + err = stmt.Next();
1.771 + TEST2(err, KSqlAtEnd);
1.772 + stmt.Close();
1.773 + //Cleanup
1.774 + db.Close();
1.775 + RDebug::Print(_L("###Delete test database\r\n"));
1.776 + (void)RSqlDatabase::Delete(KTestDbName1);
1.777 + }
1.778 +
1.779 +void DoTestsL()
1.780 + {
1.781 + TheTest.Start(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1609 Folding & Collation test 1 "));
1.782 + CollationTest1L();
1.783 + TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1610 Folding & Collation test 2 "));
1.784 + CollationTest2L();
1.785 + TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1627 Collation test 3 "));
1.786 + CollationTest3L();
1.787 + TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1760 LIKE & ESCAPE test 1 "));
1.788 + LikeTest1();
1.789 + TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1761 LIKE & ESCAPE test 2 "));
1.790 + LikeTest2();
1.791 + }
1.792 +
1.793 +TInt E32Main()
1.794 + {
1.795 + TheTest.Title();
1.796 +
1.797 + CTrapCleanup* tc = CTrapCleanup::New();
1.798 +
1.799 + __UHEAP_MARK;
1.800 +
1.801 + CreateTestDir();
1.802 + DeleteTestFiles();
1.803 + TRAPD(err, DoTestsL());
1.804 + DeleteTestFiles();
1.805 + TEST2(err, KErrNone);
1.806 +
1.807 + __UHEAP_MARKEND;
1.808 +
1.809 + TheTest.End();
1.810 + TheTest.Close();
1.811 +
1.812 + delete tc;
1.813 +
1.814 + User::Heap().Check();
1.815 + return KErrNone;
1.816 + }