|
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 // |
|
15 |
|
16 #include <e32test.h> |
|
17 #include <bautils.h> |
|
18 #include <sqldb.h> |
|
19 |
|
20 /////////////////////////////////////////////////////////////////////////////////////// |
|
21 |
|
22 RTest TheTest(_L("t_sqlcollate test")); |
|
23 |
|
24 _LIT(KTestDir, "c:\\test\\"); |
|
25 _LIT(KTestDbName1, "c:\\test\\t_sqlcollate.db"); |
|
26 |
|
27 /////////////////////////////////////////////////////////////////////////////////////// |
|
28 |
|
29 void DeleteTestFiles() |
|
30 { |
|
31 RSqlDatabase::Delete(KTestDbName1); |
|
32 } |
|
33 |
|
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__) |
|
56 |
|
57 /////////////////////////////////////////////////////////////////////////////////////// |
|
58 |
|
59 void CreateTestDir() |
|
60 { |
|
61 RFs fs; |
|
62 TInt err = fs.Connect(); |
|
63 TEST2(err, KErrNone); |
|
64 |
|
65 err = fs.MkDir(KTestDir); |
|
66 TEST(err == KErrNone || err == KErrAlreadyExists); |
|
67 |
|
68 fs.Close(); |
|
69 } |
|
70 |
|
71 /////////////////////////////////////////////////////////////////////////////////////// |
|
72 |
|
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 } |
|
79 |
|
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 } |
|
86 |
|
87 /////////////////////////////////////////////////////////////////////////////////////// |
|
88 |
|
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); |
|
104 |
|
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); |
|
110 |
|
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); |
|
126 |
|
127 for(TInt i=0;i<KInsertSqlStmtCnt;++i) |
|
128 { |
|
129 err = sortedNames.InsertInOrder(KNames[i], order); |
|
130 TEST2(err, KErrNone); |
|
131 |
|
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 } |
|
139 |
|
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); |
|
155 |
|
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); |
|
175 |
|
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); |
|
195 |
|
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); |
|
216 |
|
217 |
|
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); |
|
223 |
|
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); |
|
236 |
|
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); |
|
242 |
|
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 } |
|
251 |
|
252 stmt.Close(); |
|
253 |
|
254 //Cleanup |
|
255 sortedNames.Close(); |
|
256 db.Close(); |
|
257 |
|
258 //To debug database reindexing |
|
259 err = db.Open(KTestDbName1); |
|
260 TEST2(err, KErrNone); |
|
261 db.Close(); |
|
262 |
|
263 RDebug::Print(_L("###Delete test database\r\n")); |
|
264 (void)RSqlDatabase::Delete(KTestDbName1); |
|
265 } |
|
266 |
|
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); |
|
282 |
|
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); |
|
288 |
|
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]); |
|
298 |
|
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 } |
|
308 |
|
309 RSqlStatement stmt; |
|
310 |
|
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); |
|
317 |
|
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); |
|
327 |
|
328 //Cleanup |
|
329 db.Close(); |
|
330 RDebug::Print(_L("###Delete test database\r\n")); |
|
331 (void)RSqlDatabase::Delete(KTestDbName1); |
|
332 } |
|
333 |
|
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); |
|
351 |
|
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); |
|
357 |
|
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); |
|
375 |
|
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 } |
|
385 |
|
386 err = sortedNames.InsertInOrder(KNames[i], order); |
|
387 TEST2(err, KErrNone); |
|
388 |
|
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 } |
|
396 |
|
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); |
|
403 |
|
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); |
|
414 |
|
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); |
|
420 |
|
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(); |
|
430 |
|
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(); |
|
441 |
|
442 //Cleanup |
|
443 sortedNames.Close(); |
|
444 db.Close(); |
|
445 RDebug::Print(_L("###Delete test database\r\n")); |
|
446 (void)RSqlDatabase::Delete(KTestDbName1); |
|
447 } |
|
448 |
|
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(); |
|
535 |
|
536 //Cleanup |
|
537 db.Close(); |
|
538 RDebug::Print(_L("###Delete test database\r\n")); |
|
539 (void)RSqlDatabase::Delete(KTestDbName1); |
|
540 } |
|
541 |
|
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 } |
|
775 |
|
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 } |
|
789 |
|
790 TInt E32Main() |
|
791 { |
|
792 TheTest.Title(); |
|
793 |
|
794 CTrapCleanup* tc = CTrapCleanup::New(); |
|
795 |
|
796 __UHEAP_MARK; |
|
797 |
|
798 CreateTestDir(); |
|
799 DeleteTestFiles(); |
|
800 TRAPD(err, DoTestsL()); |
|
801 DeleteTestFiles(); |
|
802 TEST2(err, KErrNone); |
|
803 |
|
804 __UHEAP_MARKEND; |
|
805 |
|
806 TheTest.End(); |
|
807 TheTest.Close(); |
|
808 |
|
809 delete tc; |
|
810 |
|
811 User::Heap().Check(); |
|
812 return KErrNone; |
|
813 } |