|
1 // Copyright (c) 2005-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 <e32math.h> |
|
18 #include <bautils.h> |
|
19 #include <sqldb.h> |
|
20 |
|
21 /////////////////////////////////////////////////////////////////////////////////////// |
|
22 |
|
23 RTest TheTest(_L("t_sqllang test")); |
|
24 _LIT(KTestDir, "c:\\test\\"); |
|
25 |
|
26 _LIT(KTestDbName, "c:\\test\\t_sqllang_1.db"); |
|
27 _LIT(KTestDbName2, "c:\\test\\t_sqllang_2.db"); |
|
28 |
|
29 RSqlDatabase TheDb; |
|
30 |
|
31 /////////////////////////////////////////////////////////////////////////////////////// |
|
32 |
|
33 void DeleteTestFiles() |
|
34 { |
|
35 RSqlDatabase::Delete(KTestDbName2); |
|
36 RSqlDatabase::Delete(KTestDbName); |
|
37 } |
|
38 |
|
39 /////////////////////////////////////////////////////////////////////////////////////// |
|
40 /////////////////////////////////////////////////////////////////////////////////////// |
|
41 //Test macros and functions |
|
42 void Check(TInt aValue, TInt aLine) |
|
43 { |
|
44 if(!aValue) |
|
45 { |
|
46 DeleteTestFiles(); |
|
47 TheTest(EFalse, aLine); |
|
48 } |
|
49 } |
|
50 void Check(TInt aValue, TInt aExpected, TInt aLine) |
|
51 { |
|
52 if(aValue != aExpected) |
|
53 { |
|
54 DeleteTestFiles(); |
|
55 RDebug::Print(_L("*** Expected error: %d, got: %d\r\n"), aExpected, aValue); |
|
56 TheTest(EFalse, aLine); |
|
57 } |
|
58 } |
|
59 void Check2(TInt64 aValue, TInt64 aExpected, TInt aLine) |
|
60 { |
|
61 if(aValue != aExpected) |
|
62 { |
|
63 DeleteTestFiles(); |
|
64 RDebug::Print(_L("*** Expected error: %ld, got: %ld\r\n"), aExpected, aValue); |
|
65 TheTest(EFalse, aLine); |
|
66 } |
|
67 } |
|
68 #define TEST(arg) ::Check((arg), __LINE__) |
|
69 #define TEST2(aValue, aExpected) ::Check(aValue, aExpected, __LINE__) |
|
70 #define TEST3(aValue, aExpected) ::Check2(aValue, aExpected, __LINE__) |
|
71 |
|
72 /////////////////////////////////////////////////////////////////////////////////////// |
|
73 |
|
74 void CreateTestDir() |
|
75 { |
|
76 RFs fs; |
|
77 TInt err = fs.Connect(); |
|
78 TEST2(err, KErrNone); |
|
79 |
|
80 err = fs.MkDir(KTestDir); |
|
81 TEST(err == KErrNone || err == KErrAlreadyExists); |
|
82 |
|
83 fs.Close(); |
|
84 } |
|
85 |
|
86 /////////////////////////////////////////////////////////////////////////////////////// |
|
87 |
|
88 void PrintLastMsgIfError(TInt aErr) |
|
89 { |
|
90 if(aErr < 0 && SqlRetCodeClass(aErr) == ESqlDbError) |
|
91 { |
|
92 const TPtrC& msg = TheDb.LastErrorMessage(); |
|
93 RDebug::Print(_L("Last error msg: \"%S\"\r\n"), &msg); |
|
94 } |
|
95 } |
|
96 |
|
97 /** |
|
98 @SYMTestCaseID SYSLIB-SQL-CT-1611 |
|
99 @SYMTestCaseDesc Create a table with INTEGER, SMALLINT, REAl, DOUBLE PRECISION, FLOAT, DECIMAL,... |
|
100 columns. Insert some records, retrieve the column values, verify the column values, |
|
101 try some mathematical operations with the column values, executed in the SQL |
|
102 statement. |
|
103 @SYMTestPriority High |
|
104 @SYMTestActions Testing SQL engine behaviour with different numerical types. |
|
105 Testing mathematical operations with numeric columns. |
|
106 @SYMTestExpectedResults Test must not fail |
|
107 @SYMREQ REQ5792 |
|
108 REQ5793 |
|
109 */ |
|
110 void NumericDataTypesTest() |
|
111 { |
|
112 TEST2(TheDb.Create(KTestDbName), KErrNone); |
|
113 |
|
114 //Create a table with all possible numeric field types |
|
115 _LIT8(KSql1, "CREATE TABLE Tbl(A INTEGER, B SMALLINT, C REAL, D DOUBLE PRECISION, E FLOAT, \ |
|
116 F DECIMAL, G BIGINT, H TINYINT, I BIT, J NUMERIC, K MONEY, \ |
|
117 L SMALLMONEY)"); |
|
118 TInt err = TheDb.Exec(KSql1); |
|
119 PrintLastMsgIfError(err); |
|
120 TEST(err >= 0); |
|
121 //Insert one record in to the created table |
|
122 _LIT8(KSql2, "INSERT INTO Tbl(A,B,C,D,E,F,G,H,I,J,K,L) VALUES(2000000000, 30000, 123.45, 0.912E+55,\ |
|
123 1.34E-14, 1234.5678, 32000000000, 100, 7, 23.123456, 2123678.56, 11.45)"); |
|
124 err = TheDb.Exec(KSql2); |
|
125 PrintLastMsgIfError(err); |
|
126 TEST2(err, 1); |
|
127 //Get the inserted record data |
|
128 RSqlStatement stmt; |
|
129 err = stmt.Prepare(TheDb, _L("SELECT * FROM TBL")); |
|
130 PrintLastMsgIfError(err); |
|
131 TEST2(err, KErrNone); |
|
132 err = stmt.Next(); |
|
133 PrintLastMsgIfError(err); |
|
134 TEST2(err, KSqlAtRow); |
|
135 //Check column values |
|
136 TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("a"))) == 2000000000); |
|
137 TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("b"))) == 30000); |
|
138 TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("c"))) - 123.45) < 0.000001); |
|
139 //The next column value test is not working! The extracted column value is 0.9120000000000002E+55 |
|
140 //I guess the reason is that SQLITE uses manifest typing and "DOUBLE PRECISION" is not in its keyword |
|
141 //list. Maybe it is interpreted and stored as 4-byte float value. |
|
142 //TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("d"))) - 0.912E+55) < 0.000001); |
|
143 TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("e"))) - 1.34E-14) < 0.000001); |
|
144 TEST(stmt.ColumnReal(stmt.ColumnIndex(_L("f"))) == 1234.5678); |
|
145 TEST(stmt.ColumnInt64(stmt.ColumnIndex(_L("g"))) == 32000000000LL); |
|
146 TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("h"))) == 100); |
|
147 TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("i"))) == 7); |
|
148 TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("j"))) - 23.123456) < 0.000001); |
|
149 TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("k"))) - 2123678.56) < 0.000001); |
|
150 TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("l"))) - 11.45) < 0.000001); |
|
151 stmt.Close(); |
|
152 //The statement object has to be closed before TheDb.Exec() call, |
|
153 //otherwise the reported error is "database table is locked" |
|
154 //Insert second record in to the created table but inverse the column types! |
|
155 _LIT8(KSql3, "INSERT INTO Tbl(A, B, C, D, E, F, G, H, I, J,K,L) VALUES(\ |
|
156 -2.5,1.1,12,23,45,111,5.6,7.9,1.1,2,6,7)"); |
|
157 err = TheDb.Exec(KSql3); |
|
158 PrintLastMsgIfError(err); |
|
159 TEST2(err, 1); |
|
160 //Get the inserted record data |
|
161 err = stmt.Prepare(TheDb, _L("SELECT * FROM TBL")); |
|
162 PrintLastMsgIfError(err); |
|
163 TEST2(err, KErrNone); |
|
164 TEST2(stmt.Next(), KSqlAtRow); |
|
165 err = stmt.Next(); |
|
166 PrintLastMsgIfError(err); |
|
167 TEST2(err, KSqlAtRow); |
|
168 //No column value checking because SQLITE inverted the column types! |
|
169 //Check column values |
|
170 TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("a"))) - (-2.5)) < 0.000001); |
|
171 TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("b"))) - 1.1) < 0.000001); |
|
172 TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("c"))) == 12); |
|
173 TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("d"))) == 23); |
|
174 TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("e"))) == 45); |
|
175 TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("f"))) == 111); |
|
176 TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("g"))) - 5.6) < 0.000001); |
|
177 TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("h"))) - 7.9) < 0.000001); |
|
178 TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("i"))) - 1.1) < 0.000001); |
|
179 TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("j"))) == 2); |
|
180 TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("k"))) == 6); |
|
181 TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("l"))) == 7); |
|
182 |
|
183 stmt.Close(); |
|
184 //Insert third record in to the created table |
|
185 _LIT8(KSql4, "INSERT INTO Tbl(A,B,C, D, E, F, G, H, I,J, K, L) VALUES(\ |
|
186 2,3,123.45,1.5,2.5,1.56,320,100,7,23.123,212.56,11.45)"); |
|
187 err = TheDb.Exec(KSql4); |
|
188 PrintLastMsgIfError(err); |
|
189 TEST2(err, 1); |
|
190 //Issue a "SELECT" statement doing there some arithmetic operations and comparisons on the column values. |
|
191 err = stmt.Prepare(TheDb, _L("SELECT A, A-C AS A2, K + L AS A3, H*I AS A4, E/D AS A5 FROM TBL WHERE A > 0 AND A < 10")); |
|
192 PrintLastMsgIfError(err); |
|
193 TEST2(err, KErrNone); |
|
194 TEST2(stmt.Next(), KSqlAtRow); |
|
195 //Check column values |
|
196 TEST2(stmt.ColumnInt(stmt.ColumnIndex(_L("a"))), 2); |
|
197 TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("a2"))) - (2-123.45)) < 0.000001); |
|
198 TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("a3"))) - (212.56+11.45)) < 0.000001); |
|
199 TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("a4"))) == 700); |
|
200 TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("a5"))) - (2.5/1.5)) < 0.000001); |
|
201 //There should be no more records |
|
202 TEST2(stmt.Next(), KSqlAtEnd); |
|
203 stmt.Close(); |
|
204 |
|
205 TheDb.Close(); |
|
206 (void)RSqlDatabase::Delete(KTestDbName); |
|
207 } |
|
208 |
|
209 /** |
|
210 @SYMTestCaseID SYSLIB-SQL-CT-1630 |
|
211 @SYMTestCaseDesc Built-in functions test. |
|
212 abs(), coalesce(), ifnull(), last_insert_rowid(), length(), like(), lower(), |
|
213 max(), min(), nullif(), quote(), random(), round(), sqlite_version(), substr(), |
|
214 typeof(), upper(), avg(), count(), sun(). |
|
215 @SYMTestPriority High |
|
216 @SYMTestActions Built-in functions test. |
|
217 @SYMTestExpectedResults Test must not fail |
|
218 @SYMREQ REQ5792 |
|
219 REQ5793 |
|
220 */ |
|
221 void BuiltInFunctionsTest() |
|
222 { |
|
223 (void)RSqlDatabase::Delete(KTestDbName); |
|
224 TInt err = TheDb.Create(KTestDbName); |
|
225 TEST2(err, KErrNone); |
|
226 |
|
227 _LIT(KCreateSql, "CREATE TABLE A(Id Integer, \ |
|
228 F1 Integer DEFAULT 0, \ |
|
229 F2 Integer Default NULL, \ |
|
230 F3 TEXT Default Null, \ |
|
231 F4 TEXT Default Null)"); |
|
232 err = TheDb.Exec(KCreateSql); |
|
233 TEST(err >= 0); |
|
234 |
|
235 _LIT(KInsertSql1, "INSERT INTO A(Id, F1) VALUES(1, 100)"); |
|
236 err = TheDb.Exec(KInsertSql1); |
|
237 TEST2(err, 1); |
|
238 |
|
239 _LIT(KInsertSql2, "INSERT INTO A(Id, F1) VALUES(2, 1)"); |
|
240 err = TheDb.Exec(KInsertSql2); |
|
241 TEST2(err, 1); |
|
242 |
|
243 _LIT(KInsertSql3, "INSERT INTO A(Id, F3) VALUES(3, 'ABCD')"); |
|
244 err = TheDb.Exec(KInsertSql3); |
|
245 TEST2(err, 1); |
|
246 |
|
247 _LIT(KInsertSql4, "INSERT INTO A(Id, F4) VALUES(4, 'DCBA')"); |
|
248 err = TheDb.Exec(KInsertSql4); |
|
249 TEST2(err, 1); |
|
250 |
|
251 RSqlStatement stmt; |
|
252 |
|
253 //abs() test -------------------------------------------------------- |
|
254 err = stmt.Prepare(TheDb, _L("SELECT * FROM A WHERE F1 > ABS(-10)")); |
|
255 TEST2(err, KErrNone); |
|
256 err = stmt.Next(); |
|
257 TEST2(err, KSqlAtRow); |
|
258 TEST(stmt.ColumnInt(1) == 100); |
|
259 stmt.Close(); |
|
260 |
|
261 //coalesce() test -------------------------------------------------------- |
|
262 err = stmt.Prepare(TheDb, _L("SELECT COALESCE(F3, F4) AS F FROM A")); |
|
263 TEST2(err, KErrNone); |
|
264 |
|
265 err = stmt.Next(); |
|
266 TEST2(err, KSqlAtRow); |
|
267 TEST(stmt.IsNull(0)); |
|
268 |
|
269 err = stmt.Next(); |
|
270 TEST2(err, KSqlAtRow); |
|
271 TEST(stmt.IsNull(0)); |
|
272 |
|
273 _LIT(KTextVal1, "ABCD"); |
|
274 err = stmt.Next(); |
|
275 TEST2(err, KSqlAtRow); |
|
276 TPtrC colVal; |
|
277 err = stmt.ColumnText(0, colVal); |
|
278 TEST2(err, KErrNone); |
|
279 TEST(colVal == KTextVal1); |
|
280 |
|
281 _LIT(KTextVal2, "DCBA"); |
|
282 err = stmt.Next(); |
|
283 TEST2(err, KSqlAtRow); |
|
284 err = stmt.ColumnText(0, colVal); |
|
285 TEST2(err, KErrNone); |
|
286 TEST(colVal == KTextVal2); |
|
287 |
|
288 stmt.Close(); |
|
289 |
|
290 //ifnull() test -------------------------------------------------------- |
|
291 err = stmt.Prepare(TheDb, _L("SELECT IFNULL(F3, F4) AS F FROM A")); |
|
292 TEST2(err, KErrNone); |
|
293 |
|
294 err = stmt.Next(); |
|
295 TEST2(err, KSqlAtRow); |
|
296 TEST(stmt.IsNull(0)); |
|
297 |
|
298 err = stmt.Next(); |
|
299 TEST2(err, KSqlAtRow); |
|
300 TEST(stmt.IsNull(0)); |
|
301 |
|
302 err = stmt.Next(); |
|
303 TEST2(err, KSqlAtRow); |
|
304 err = stmt.ColumnText(0, colVal); |
|
305 TEST2(err, KErrNone); |
|
306 TEST(colVal == KTextVal1); |
|
307 |
|
308 err = stmt.Next(); |
|
309 TEST2(err, KSqlAtRow); |
|
310 err = stmt.ColumnText(0, colVal); |
|
311 TEST2(err, KErrNone); |
|
312 TEST(colVal == KTextVal2); |
|
313 |
|
314 stmt.Close(); |
|
315 |
|
316 //last_insert_rowid() test -------------------------------------------------------- |
|
317 err = stmt.Prepare(TheDb, _L("SELECT last_insert_rowid() AS F")); |
|
318 TEST2(err, KErrNone); |
|
319 err = stmt.Next(); |
|
320 TEST2(err, KSqlAtRow); |
|
321 TInt64 colVal64 = stmt.ColumnInt64(0); |
|
322 RDebug::Print(_L("Last insert row id=%d\r\n"), (TInt)colVal64); |
|
323 stmt.Close(); |
|
324 |
|
325 //length() test -------------------------------------------------------- |
|
326 err = stmt.Prepare(TheDb, _L("SELECT length(F4) AS L FROM A WHERE ID >= 3")); |
|
327 TEST2(err, KErrNone); |
|
328 |
|
329 err = stmt.Next(); |
|
330 TEST2(err, KSqlAtRow); |
|
331 TEST(stmt.ColumnInt(0) == 0); |
|
332 |
|
333 err = stmt.Next(); |
|
334 TEST2(err, KSqlAtRow); |
|
335 TEST(stmt.ColumnInt(0) == 4); |
|
336 |
|
337 stmt.Close(); |
|
338 |
|
339 //like() test -------------------------------------------------------- |
|
340 err = stmt.Prepare(TheDb, _L("SELECT Id FROM A WHERE F4 LIKE 'DC%'")); |
|
341 TEST2(err, KErrNone); |
|
342 err = stmt.Next(); |
|
343 TEST2(err, KSqlAtRow); |
|
344 TEST(stmt.ColumnInt(0) == 4); |
|
345 stmt.Close(); |
|
346 |
|
347 _LIT(KInsertSql5, "INSERT INTO A(Id, F4) VALUES(5, 'ab%cd')"); |
|
348 err = TheDb.Exec(KInsertSql5); |
|
349 TEST2(err, 1); |
|
350 |
|
351 err = stmt.Prepare(TheDb, _L("SELECT Id FROM A WHERE F4 LIKE 'ab/%cd' ESCAPE '/'")); |
|
352 TEST2(err, KErrNone); |
|
353 err = stmt.Next(); |
|
354 TEST2(err, KSqlAtRow); |
|
355 TEST(stmt.ColumnInt(0) == 5); |
|
356 stmt.Close(); |
|
357 |
|
358 err = stmt.Prepare(TheDb, _L8("SELECT Id FROM A WHERE F4 LIKE 'ab/%cd' ESCAPE '/'")); |
|
359 TEST2(err, KErrNone); |
|
360 err = stmt.Next(); |
|
361 TEST2(err, KSqlAtRow); |
|
362 TEST(stmt.ColumnInt(0) == 5); |
|
363 stmt.Close(); |
|
364 |
|
365 //lower() test -------------------------------------------------------- |
|
366 err = stmt.Prepare(TheDb, _L("SELECT LOWER(F3) FROM A WHERE F3 = 'ABCD'")); |
|
367 TEST2(err, KErrNone); |
|
368 err = stmt.Next(); |
|
369 TEST2(err, KSqlAtRow); |
|
370 err = stmt.ColumnText(0, colVal); |
|
371 TEST2(err, KErrNone); |
|
372 _LIT(KTextVal3, "abcd"); |
|
373 TEST(colVal == KTextVal3); |
|
374 stmt.Close(); |
|
375 |
|
376 //max() test -------------------------------------------------------- |
|
377 err = stmt.Prepare(TheDb, _L("SELECT MAX(F1) AS M FROM A")); |
|
378 TEST2(err, KErrNone); |
|
379 err = stmt.Next(); |
|
380 TEST2(err, KSqlAtRow); |
|
381 TEST(stmt.ColumnInt(0) == 100); |
|
382 stmt.Close(); |
|
383 |
|
384 //min() test -------------------------------------------------------- |
|
385 err = stmt.Prepare(TheDb, _L("SELECT MIN(F1) AS M FROM A")); |
|
386 TEST2(err, KErrNone); |
|
387 err = stmt.Next(); |
|
388 TEST2(err, KSqlAtRow); |
|
389 TEST(stmt.ColumnInt(0) == 0); |
|
390 stmt.Close(); |
|
391 |
|
392 //nullif() test -------------------------------------------------------- |
|
393 err = stmt.Prepare(TheDb, _L("SELECT NULLIF(3, 4) AS M")); |
|
394 TEST2(err, KErrNone); |
|
395 err = stmt.Next(); |
|
396 TEST2(err, KSqlAtRow); |
|
397 TEST(stmt.ColumnInt(0) == 3); |
|
398 stmt.Close(); |
|
399 |
|
400 err = stmt.Prepare(TheDb, _L("SELECT NULLIF(4, 4) AS M")); |
|
401 TEST2(err, KErrNone); |
|
402 err = stmt.Next(); |
|
403 TEST2(err, KSqlAtRow); |
|
404 TEST(stmt.IsNull(0)); |
|
405 stmt.Close(); |
|
406 |
|
407 //quote() test -------------------------------------------------------- |
|
408 err = stmt.Prepare(TheDb, _L("SELECT QUOTE(F4) AS M FROM A WHERE Id = 5")); |
|
409 TEST2(err, KErrNone); |
|
410 err = stmt.Next(); |
|
411 TEST2(err, KSqlAtRow); |
|
412 err = stmt.ColumnText(0, colVal); |
|
413 TEST2(err, KErrNone); |
|
414 _LIT(KTextVal4, "'ab%cd'"); |
|
415 TEST(colVal == KTextVal4); |
|
416 stmt.Close(); |
|
417 |
|
418 //random() test -------------------------------------------------------- |
|
419 err = stmt.Prepare(TheDb, _L("SELECT * FROM A WHERE Id > RANDOM(*)")); |
|
420 TEST2(err, KErrNone); |
|
421 stmt.Close(); |
|
422 |
|
423 //round() test -------------------------------------------------------- |
|
424 err = stmt.Prepare(TheDb, _L("SELECT ROUND(5.4) AS D")); |
|
425 TEST2(err, KErrNone); |
|
426 err = stmt.Next(); |
|
427 TEST2(err, KSqlAtRow); |
|
428 TEST(Abs(stmt.ColumnReal(0) - 5.0) < 0.000001); |
|
429 stmt.Close(); |
|
430 |
|
431 err = stmt.Prepare(TheDb, _L("SELECT ROUND(5.4321, 2) AS D")); |
|
432 TEST2(err, KErrNone); |
|
433 err = stmt.Next(); |
|
434 TEST2(err, KSqlAtRow); |
|
435 TEST(Abs(stmt.ColumnReal(0) - 5.43) < 0.000001); |
|
436 stmt.Close(); |
|
437 |
|
438 //sqlite_version() test -------------------------------------------------------- |
|
439 err = stmt.Prepare(TheDb, _L("SELECT sqlite_version(*) AS V")); |
|
440 TEST2(err, KErrNone); |
|
441 err = stmt.Next(); |
|
442 TEST2(err, KSqlAtRow); |
|
443 err = stmt.ColumnText(0, colVal); |
|
444 TEST2(err, KErrNone); |
|
445 RDebug::Print(_L("Database engine version: \"%S\"\r\n"), &colVal); |
|
446 stmt.Close(); |
|
447 |
|
448 //substr() test -------------------------------------------------------- |
|
449 err = stmt.Prepare(TheDb, _L("SELECT SUBSTR('abcd', 2, 2) AS S")); |
|
450 TEST2(err, KErrNone); |
|
451 err = stmt.Next(); |
|
452 TEST2(err, KSqlAtRow); |
|
453 err = stmt.ColumnText(0, colVal); |
|
454 TEST2(err, KErrNone); |
|
455 _LIT(KTextVal7, "bc"); |
|
456 TEST(colVal == KTextVal7); |
|
457 stmt.Close(); |
|
458 |
|
459 err = stmt.Prepare(TheDb, _L("SELECT SUBSTR('abcd', -3, 2) AS S")); |
|
460 TEST2(err, KErrNone); |
|
461 err = stmt.Next(); |
|
462 TEST2(err, KSqlAtRow); |
|
463 err = stmt.ColumnText(0, colVal); |
|
464 TEST2(err, KErrNone); |
|
465 TEST(colVal == KTextVal7); |
|
466 stmt.Close(); |
|
467 |
|
468 //typeof() test -------------------------------------------------------- |
|
469 _LIT(KInsertSql6, "INSERT INTO A(Id, F2) VALUES(6, 2)"); |
|
470 err = TheDb.Exec(KInsertSql6); |
|
471 TEST2(err, 1); |
|
472 |
|
473 err = stmt.Prepare(TheDb, _L("SELECT TYPEOF(F1 + F2) AS T FROM A WHERE ID > 4")); |
|
474 TEST2(err, KErrNone); |
|
475 |
|
476 err = stmt.Next(); |
|
477 TEST2(err, KSqlAtRow); |
|
478 err = stmt.ColumnText(0, colVal); |
|
479 TEST2(err, KErrNone); |
|
480 _LIT(KTextVal8, "null"); |
|
481 TEST(colVal == KTextVal8); |
|
482 |
|
483 err = stmt.Next(); |
|
484 TEST2(err, KSqlAtRow); |
|
485 err = stmt.ColumnText(0, colVal); |
|
486 TEST2(err, KErrNone); |
|
487 _LIT(KTextVal9, "integer"); |
|
488 TEST(colVal == KTextVal9); |
|
489 |
|
490 stmt.Close(); |
|
491 |
|
492 //upper() test -------------------------------------------------------- |
|
493 err = stmt.Prepare(TheDb, _L("SELECT UPPER('ghjk') AS U")); |
|
494 TEST2(err, KErrNone); |
|
495 err = stmt.Next(); |
|
496 TEST2(err, KSqlAtRow); |
|
497 err = stmt.ColumnText(0, colVal); |
|
498 TEST2(err, KErrNone); |
|
499 _LIT(KTextVal10, "GHJK"); |
|
500 TEST(colVal == KTextVal10); |
|
501 stmt.Close(); |
|
502 |
|
503 //avg() test -------------------------------------------------------- |
|
504 err = stmt.Prepare(TheDb, _L("SELECT AVG(F2) AS F FROM A")); |
|
505 TEST2(err, KErrNone); |
|
506 err = stmt.Next(); |
|
507 TEST2(err, KSqlAtRow); |
|
508 TEST(Abs(stmt.ColumnReal(0) - 2) < 0.000001); |
|
509 stmt.Close(); |
|
510 |
|
511 //count() test -------------------------------------------------------- |
|
512 err = stmt.Prepare(TheDb, _L("SELECT COUNT(F2) AS F FROM A")); |
|
513 TEST2(err, KErrNone); |
|
514 err = stmt.Next(); |
|
515 TEST2(err, KSqlAtRow); |
|
516 TEST(stmt.ColumnInt(0) == 1); |
|
517 stmt.Close(); |
|
518 |
|
519 err = stmt.Prepare(TheDb, _L("SELECT COUNT(*) AS F FROM A")); |
|
520 TEST2(err, KErrNone); |
|
521 err = stmt.Next(); |
|
522 TEST2(err, KSqlAtRow); |
|
523 TEST(stmt.ColumnInt(0) == 6); |
|
524 stmt.Close(); |
|
525 |
|
526 //sum() test -------------------------------------------------------- |
|
527 err = stmt.Prepare(TheDb, _L("SELECT SUM(F2) AS S FROM A")); |
|
528 TEST2(err, KErrNone); |
|
529 err = stmt.Next(); |
|
530 TEST2(err, KSqlAtRow); |
|
531 TEST(Abs(stmt.ColumnReal(0) - 2) < 0.000001); |
|
532 stmt.Close(); |
|
533 |
|
534 // ---------------------------------- |
|
535 TheDb.Close(); |
|
536 (void)RSqlDatabase::Delete(KTestDbName); |
|
537 } |
|
538 |
|
539 /** |
|
540 @SYMTestCaseID SYSLIB-SQL-CT-1632 |
|
541 @SYMTestCaseDesc "ATTACH DATABASE" test. |
|
542 The test creates two databases. The main database has |
|
543 Account(Id Integer, PersonId Integer, Value Integer) table. |
|
544 The attached database has Person(Id Integer, Name TEXT) table. |
|
545 The test inserts some records with a valid relation between them in both tables. |
|
546 Then the test opens tha main database and attaches the second database to the first one. |
|
547 The test prepares and executes SQL statement which retrieves column values from both |
|
548 Account and Person tables. The test checks the column values. |
|
549 @SYMTestPriority High |
|
550 @SYMTestActions "ATTACH DATABASE" test. |
|
551 @SYMTestExpectedResults Test must not fail |
|
552 @SYMREQ REQ5792 |
|
553 REQ5793 |
|
554 */ |
|
555 void AttachDatabaseTest() |
|
556 { |
|
557 // ------------------------------------------------------------ |
|
558 RSqlDatabase db2; |
|
559 TInt err = db2.Create(KTestDbName2); |
|
560 TEST2(err, KErrNone); |
|
561 |
|
562 err = db2.Exec(_L("CREATE TABLE Person(Id Integer, Name TEXT)")); |
|
563 TEST(err >= 0); |
|
564 |
|
565 err = db2.Exec(_L("INSERT INTO Person(Id, Name) VALUES(1, 'A')")); |
|
566 TEST2(err, 1); |
|
567 |
|
568 err = db2.Exec(_L("INSERT INTO Person(Id, Name) VALUES(2, 'B')")); |
|
569 TEST2(err, 1); |
|
570 |
|
571 err = db2.Exec(_L("INSERT INTO Person(Id, Name) VALUES(3, 'C')")); |
|
572 TEST2(err, 1); |
|
573 |
|
574 db2.Close(); |
|
575 |
|
576 // ------------------------------------------------------------ |
|
577 (void)RSqlDatabase::Delete(KTestDbName); |
|
578 err = TheDb.Create(KTestDbName); |
|
579 TEST2(err, KErrNone); |
|
580 |
|
581 err = TheDb.Exec(_L("CREATE TABLE Account(Id Integer, PersonId Integer, Value Integer)")); |
|
582 TEST(err >= 0); |
|
583 |
|
584 err = TheDb.Exec(_L("INSERT INTO ACCOUNT(Id, PersonId, Value) VALUES(1, 2, 20)")); |
|
585 TEST2(err, 1); |
|
586 |
|
587 err = TheDb.Exec(_L("INSERT INTO ACCOUNT(Id, PersonId, Value) VALUES(2, 1, 10)")); |
|
588 TEST2(err, 1); |
|
589 |
|
590 err = TheDb.Exec(_L("INSERT INTO ACCOUNT(Id, PersonId, Value) VALUES(3, 2, 25)")); |
|
591 TEST2(err, 1); |
|
592 |
|
593 err = TheDb.Exec(_L("INSERT INTO ACCOUNT(Id, PersonId, Value) VALUES(4, 3, 30)")); |
|
594 TEST2(err, 1); |
|
595 |
|
596 TheDb.Close(); |
|
597 |
|
598 // ------------------------------------------------------------ |
|
599 err = TheDb.Open(KTestDbName); |
|
600 TEST2(err, KErrNone); |
|
601 |
|
602 TBuf<100> sql; |
|
603 sql.Copy(_L("ATTACH DATABASE '")); |
|
604 sql.Append(KTestDbName2); |
|
605 sql.Append(_L("' AS DB2")); |
|
606 err = TheDb.Exec(sql); |
|
607 TEST(err >= 0); |
|
608 |
|
609 RSqlStatement stmt; |
|
610 err = stmt.Prepare(TheDb, _L("SELECT Account.Value, DB2.Person.Name FROM Account, DB2.Person \ |
|
611 WHERE Account.PersonId = DB2.Person.Id")); |
|
612 TEST2(err, KErrNone); |
|
613 |
|
614 TPtrC personName; |
|
615 _LIT(KName1, "A"); |
|
616 _LIT(KName2, "B"); |
|
617 _LIT(KName3, "C"); |
|
618 |
|
619 err = stmt.Next(); |
|
620 TEST2(err, KSqlAtRow); |
|
621 err = stmt.ColumnText(1, personName); |
|
622 TEST2(err, KErrNone); |
|
623 TEST(personName == KName2); |
|
624 TEST(stmt.ColumnInt(0) == 20); |
|
625 |
|
626 err = stmt.Next(); |
|
627 TEST2(err, KSqlAtRow); |
|
628 err = stmt.ColumnText(1, personName); |
|
629 TEST2(err, KErrNone); |
|
630 TEST(personName == KName1); |
|
631 TEST(stmt.ColumnInt(0) == 10); |
|
632 |
|
633 err = stmt.Next(); |
|
634 TEST2(err, KSqlAtRow); |
|
635 err = stmt.ColumnText(1, personName); |
|
636 TEST2(err, KErrNone); |
|
637 TEST(personName == KName2); |
|
638 TEST(stmt.ColumnInt(0) == 25); |
|
639 |
|
640 err = stmt.Next(); |
|
641 TEST2(err, KSqlAtRow); |
|
642 err = stmt.ColumnText(1, personName); |
|
643 TEST2(err, KErrNone); |
|
644 TEST(personName == KName3); |
|
645 TEST(stmt.ColumnInt(0) == 30); |
|
646 |
|
647 stmt.Close(); |
|
648 |
|
649 err = TheDb.Exec(_L("DETACH DATABASE DB2")); |
|
650 TEST(err >= 0); |
|
651 |
|
652 // ------------------------------------------------------------ |
|
653 TheDb.Close(); |
|
654 (void)RSqlDatabase::Delete(KTestDbName2); |
|
655 (void)RSqlDatabase::Delete(KTestDbName); |
|
656 } |
|
657 |
|
658 /** |
|
659 @SYMTestCaseID SYSLIB-SQL-UT-3502 |
|
660 @SYMTestCaseDesc Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low. |
|
661 The test creates a test table with INT32, INT64, DOUBLE, TEXT, and BINARY columns |
|
662 and inserts couple of records there. Then the test prepares a SELECT statement, |
|
663 retrieves all table rows, and to retrieve each column value, calls RSqlStatement::ColumnInt(). |
|
664 The column values are carefully chosen, so some of the ColumnInt() calls have to round |
|
665 or clamp the returned value. |
|
666 Summary: the test checks the ColumnInt() behaviour when the column value type is not INT32 and |
|
667 when the column value is too big or too small and cannot fit in 32 bits. |
|
668 @SYMTestPriority High |
|
669 @SYMTestActions Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low. |
|
670 @SYMTestExpectedResults Test must not fail |
|
671 @SYMDEF DEF109100 |
|
672 */ |
|
673 void ColumnIntTest() |
|
674 { |
|
675 (void)RSqlDatabase::Delete(KTestDbName); |
|
676 TInt err = TheDb.Create(KTestDbName); |
|
677 TEST2(err, KErrNone); |
|
678 err = TheDb.Exec(_L("CREATE TABLE A(I32 INTEGER,I64 INTEGER,D DOUBLE,T TEXT,B BINARY)")); |
|
679 TEST(err >= 0); |
|
680 |
|
681 err = TheDb.Exec(_L("INSERT INTO A VALUES(-5,-5000000000,-10000000000.0,'AAA',x'1122FF')")); |
|
682 TEST2(err, 1); |
|
683 err = TheDb.Exec(_L("INSERT INTO A VALUES(5,5000000000,10000000000.0,NULL,NULL)")); |
|
684 TEST2(err, 1); |
|
685 err = TheDb.Exec(_L("INSERT INTO A VALUES(5,2000000000,1000000000.0,NULL,NULL)")); |
|
686 TEST2(err, 1); |
|
687 err = TheDb.Exec(_L("INSERT INTO A VALUES(NULL,NULL,1.1234567890123456E+317,NULL,NULL)")); |
|
688 TEST2(err, 1); |
|
689 |
|
690 RSqlStatement stmt; |
|
691 err = stmt.Prepare(TheDb, _L("SELECT I32,I64,D,T,B FROM A")); |
|
692 TEST2(err, KErrNone); |
|
693 |
|
694 err = stmt.Next(); |
|
695 TEST2(err, KSqlAtRow); |
|
696 TInt val = stmt.ColumnInt(0); |
|
697 TEST2(val, -5); //"-5" is a 32-bit integer value |
|
698 val = stmt.ColumnInt(1); |
|
699 TEST2(val, KMinTInt); //"-5000000000" is a 64-bit integer, will be clamped to KMinTInt |
|
700 val = stmt.ColumnInt(2); |
|
701 TEST2(val, KMinTInt); //"-10000000000.0" is a 64-bit double, will be rounded to the nearest 32-bit integer |
|
702 val = stmt.ColumnInt(3); |
|
703 TEST2(val, 0); //"AAA" is a text string, cannot be converted to a 32-bit integer |
|
704 val = stmt.ColumnInt(4); |
|
705 TEST2(val, 0); //"1122FF" is a hex binary, cannot be converted to a 32-bit integer |
|
706 |
|
707 err = stmt.Next(); |
|
708 TEST2(err, KSqlAtRow); |
|
709 val = stmt.ColumnInt(0); |
|
710 TEST2(val, 5); //"5" is a 32-bit integer value |
|
711 val = stmt.ColumnInt(1); |
|
712 TEST2(val, KMaxTInt); //"5000000000" is a 64-bit integer, will be clamped to KMaxTInt |
|
713 val = stmt.ColumnInt(2); |
|
714 TEST2(val, KMaxTInt); //"10000000000.0" is a 64-bit double, will be rounded to the nearest 32-bit integer |
|
715 val = stmt.ColumnInt(3); |
|
716 TEST2(val, 0); //NULL column value |
|
717 val = stmt.ColumnInt(4); |
|
718 TEST2(val, 0); //NULL column value |
|
719 |
|
720 err = stmt.Next(); |
|
721 TEST2(err, KSqlAtRow); |
|
722 val = stmt.ColumnInt(0); |
|
723 TEST2(val, 5); |
|
724 val = stmt.ColumnInt(1); |
|
725 TEST2(val, 2000000000); |
|
726 val = stmt.ColumnInt(2); |
|
727 TEST2(val, 1000000000); |
|
728 |
|
729 err = stmt.Next(); |
|
730 TEST2(err, KSqlAtRow); |
|
731 val = stmt.ColumnInt(2); |
|
732 TEST2(val, KMinTInt); //"1.1234567890123456E+317" is too big and cannot fit in a 64-bit double |
|
733 |
|
734 stmt.Close(); |
|
735 |
|
736 TheDb.Close(); |
|
737 err = RSqlDatabase::Delete(KTestDbName); |
|
738 TEST2(err, KErrNone); |
|
739 } |
|
740 |
|
741 /** |
|
742 @SYMTestCaseID SYSLIB-SQL-UT-3503 |
|
743 @SYMTestCaseDesc Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low. |
|
744 The test creates a test table with INT32, INT64, DOUBLE, TEXT, and BINARY columns |
|
745 and inserts couple of records there. Then the test prepares a SELECT statement, |
|
746 retrieves all table rows, and to retrieve each column value, calls RSqlStatement::ColumnInt64(). |
|
747 The column values are carefully chosen, so some of the ColumnInt64() calls have to round |
|
748 or clamp the returned value. |
|
749 Summary: the test checks the ColumnInt64() behaviour when the column value type is not INT64 and |
|
750 when the column value is too big or too small and cannot be presented as 64-bits integer. |
|
751 @SYMTestPriority High |
|
752 @SYMTestActions Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low. |
|
753 @SYMTestExpectedResults Test must not fail |
|
754 @SYMDEF DEF109100 |
|
755 */ |
|
756 void ColumnInt64Test() |
|
757 { |
|
758 (void)RSqlDatabase::Delete(KTestDbName); |
|
759 TInt err = TheDb.Create(KTestDbName); |
|
760 TEST2(err, KErrNone); |
|
761 err = TheDb.Exec(_L("CREATE TABLE A(I32 INTEGER,I64 INTEGER,D DOUBLE,T TEXT,B BINARY)")); |
|
762 TEST(err >= 0); |
|
763 |
|
764 err = TheDb.Exec(_L("INSERT INTO A VALUES(-5,-5000000000,-10000000000.0,'AAA',x'1122FF')")); |
|
765 TEST2(err, 1); |
|
766 err = TheDb.Exec(_L("INSERT INTO A VALUES(5,5000000000,10000000000.0,NULL,NULL)")); |
|
767 TEST2(err, 1); |
|
768 err = TheDb.Exec(_L("INSERT INTO A VALUES(5,2000000000,1000000000.0,NULL,NULL)")); |
|
769 TEST2(err, 1); |
|
770 err = TheDb.Exec(_L("INSERT INTO A VALUES(NULL,NULL,1.1234567890123456E+317,NULL,NULL)")); |
|
771 TEST2(err, 1); |
|
772 |
|
773 RSqlStatement stmt; |
|
774 err = stmt.Prepare(TheDb, _L("SELECT I32,I64,D,T,B FROM A")); |
|
775 TEST2(err, KErrNone); |
|
776 |
|
777 err = stmt.Next(); |
|
778 TEST2(err, KSqlAtRow); |
|
779 TInt64 val = stmt.ColumnInt64(0); |
|
780 TEST3(val, -5); //"-5" is a 32-bit integer value |
|
781 val = stmt.ColumnInt64(1); |
|
782 TEST3(val, -5000000000LL); //"-5000000000" is a 64-bit integer |
|
783 val = stmt.ColumnInt64(2); |
|
784 TEST3(val, -10000000000LL); //"-10000000000.0" is a 64-bit double, will be rounded to the nearest 64-bit integer |
|
785 val = stmt.ColumnInt64(3); |
|
786 TEST3(val, 0); //"AAA" is a text string, cannot be converted to a 64-bit integer |
|
787 val = stmt.ColumnInt64(4); |
|
788 TEST3(val, 0); //"1122FF" is a hex binary, cannot be converted to a 64-bit integer |
|
789 |
|
790 err = stmt.Next(); |
|
791 TEST2(err, KSqlAtRow); |
|
792 val = stmt.ColumnInt64(0); |
|
793 TEST3(val, 5); //"5" is a 32-bit integer value |
|
794 val = stmt.ColumnInt64(1); |
|
795 TEST3(val, 5000000000LL); //"5000000000" is a 64-bit integer |
|
796 val = stmt.ColumnInt64(2); |
|
797 TEST3(val, 10000000000LL); //"10000000000.0" is a 64-bit double, will be rounded to the nearest 64-bit integer |
|
798 val = stmt.ColumnInt64(3); |
|
799 TEST3(val, 0); //NULL column value |
|
800 val = stmt.ColumnInt64(4); |
|
801 TEST3(val, 0); //NULL column value |
|
802 |
|
803 err = stmt.Next(); |
|
804 TEST2(err, KSqlAtRow); |
|
805 val = stmt.ColumnInt64(0); |
|
806 TEST3(val, 5); |
|
807 val = stmt.ColumnInt64(1); |
|
808 TEST3(val, 2000000000); |
|
809 val = stmt.ColumnInt64(2); |
|
810 TEST3(val, 1000000000); |
|
811 |
|
812 err = stmt.Next(); |
|
813 TEST2(err, KSqlAtRow); |
|
814 val = stmt.ColumnInt64(2); |
|
815 TEST3(val, KMinTInt64); //"1.1234567890123456E+317" is too big and cannot fit in a 64-bit double |
|
816 |
|
817 stmt.Close(); |
|
818 |
|
819 TheDb.Close(); |
|
820 err = RSqlDatabase::Delete(KTestDbName); |
|
821 TEST2(err, KErrNone); |
|
822 } |
|
823 |
|
824 /** |
|
825 @SYMTestCaseID SYSLIB-SQL-UT-3504 |
|
826 @SYMTestCaseDesc Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low. |
|
827 The test creates a test table with INT32, INT64, DOUBLE, TEXT, and BINARY columns |
|
828 and inserts couple of records there. Then the test prepares a SELECT statement, |
|
829 retrieves all table rows, and to retrieve each column value, calls RSqlStatement::ColumnReal(). |
|
830 Summary: the test checks the ColumnReal() behaviour when the column value type is not DOUBLE and |
|
831 when the column value is too big or too small and cannot be presented as 64-bits double. |
|
832 @SYMTestPriority High |
|
833 @SYMTestActions Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low. |
|
834 @SYMTestExpectedResults Test must not fail |
|
835 @SYMDEF DEF109100 |
|
836 */ |
|
837 void ColumnRealTest() |
|
838 { |
|
839 (void)RSqlDatabase::Delete(KTestDbName); |
|
840 TInt err = TheDb.Create(KTestDbName); |
|
841 TEST2(err, KErrNone); |
|
842 err = TheDb.Exec(_L("CREATE TABLE A(I32 INTEGER,I64 INTEGER,D DOUBLE,T TEXT,B BINARY)")); |
|
843 TEST(err >= 0); |
|
844 |
|
845 err = TheDb.Exec(_L("INSERT INTO A VALUES(-5,-5000000000,-10000000000.0,'AAA',x'1122FF')")); |
|
846 TEST2(err, 1); |
|
847 err = TheDb.Exec(_L("INSERT INTO A VALUES(5,5000000000,10000000000.0,NULL,NULL)")); |
|
848 TEST2(err, 1); |
|
849 err = TheDb.Exec(_L("INSERT INTO A VALUES(5,2000000000,1000000000.0,NULL,NULL)")); |
|
850 TEST2(err, 1); |
|
851 err = TheDb.Exec(_L("INSERT INTO A VALUES(NULL,NULL,1.1234567890123456E+317,NULL,NULL)")); |
|
852 TEST2(err, 1); |
|
853 |
|
854 const TReal KEpsilon = 0.000001; |
|
855 |
|
856 RSqlStatement stmt; |
|
857 err = stmt.Prepare(TheDb, _L("SELECT I32,I64,D,T,B FROM A")); |
|
858 TEST2(err, KErrNone); |
|
859 |
|
860 err = stmt.Next(); |
|
861 TEST2(err, KSqlAtRow); |
|
862 TReal val = stmt.ColumnReal(0); |
|
863 TEST(Abs(val - (-5)) < KEpsilon); |
|
864 val = stmt.ColumnReal(1); |
|
865 TEST(Abs(val - (-5000000000LL)) < KEpsilon); |
|
866 val = stmt.ColumnReal(2); |
|
867 TEST(Abs(val - (-10000000000LL)) < KEpsilon); |
|
868 val = stmt.ColumnReal(3); |
|
869 TEST(Abs(val) < 0.0001); //"AAA" is a text string, cannot be converted to a 64-bit double |
|
870 val = stmt.ColumnReal(4); |
|
871 TEST(Abs(val) < 0.0001); //"1122FF" is a hex binary, cannot be converted to a 64-bit double |
|
872 |
|
873 err = stmt.Next(); |
|
874 TEST2(err, KSqlAtRow); |
|
875 val = stmt.ColumnReal(0); |
|
876 TEST(Abs(val - 5) < KEpsilon); |
|
877 val = stmt.ColumnReal(1); |
|
878 TEST(Abs(val - 5000000000LL) < KEpsilon); |
|
879 val = stmt.ColumnReal(2); |
|
880 TEST(Abs(val - 10000000000LL) < KEpsilon); |
|
881 val = stmt.ColumnReal(3); |
|
882 TEST(Abs(val) < KEpsilon); //NULL column value |
|
883 val = stmt.ColumnReal(4); |
|
884 TEST(Abs(val) < KEpsilon); //NULL column value |
|
885 |
|
886 err = stmt.Next(); |
|
887 TEST2(err, KSqlAtRow); |
|
888 val = stmt.ColumnReal(0); |
|
889 TEST(Abs(val - 5) < KEpsilon); |
|
890 val = stmt.ColumnReal(1); |
|
891 TEST(Abs(val - 2000000000) < KEpsilon); |
|
892 val = stmt.ColumnReal(2); |
|
893 TEST(Abs(val - 1000000000) < KEpsilon); |
|
894 |
|
895 err = stmt.Next(); |
|
896 TEST2(err, KSqlAtRow); |
|
897 val = stmt.ColumnReal(2); |
|
898 TEST(Math::IsInfinite(val)); //"1.1234567890123456E+317" is too big and cannot fit in a 64-bit double |
|
899 |
|
900 stmt.Close(); |
|
901 |
|
902 TheDb.Close(); |
|
903 err = RSqlDatabase::Delete(KTestDbName); |
|
904 TEST2(err, KErrNone); |
|
905 } |
|
906 |
|
907 /** |
|
908 @SYMTestCaseID SYSLIB-SQL-UT-3505 |
|
909 @SYMTestCaseDesc Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low. |
|
910 The test creates a test table with INT32, INT64, DOUBLE, TEXT, and BINARY columns |
|
911 and inserts a record there. Then the test prepares a SELECT statement, |
|
912 retrieves all table rows, and to retrieve each column value, calls RSqlStatement::ColumnText(). |
|
913 Summary: the test checks the ColumnText() behaviour when the column value type is not TEXT. |
|
914 (In all non-TEXT cases the ,ethod is expected to return KNullDesC8) |
|
915 @SYMTestPriority High |
|
916 @SYMTestActions Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low. |
|
917 @SYMTestExpectedResults Test must not fail |
|
918 @SYMDEF DEF109100 |
|
919 */ |
|
920 void ColumnTextTest() |
|
921 { |
|
922 (void)RSqlDatabase::Delete(KTestDbName); |
|
923 TInt err = TheDb.Create(KTestDbName); |
|
924 TEST2(err, KErrNone); |
|
925 err = TheDb.Exec(_L("CREATE TABLE A(I32 INTEGER,I64 INTEGER,D DOUBLE,T TEXT,B BINARY)")); |
|
926 TEST(err >= 0); |
|
927 |
|
928 err = TheDb.Exec(_L("INSERT INTO A VALUES(-5,-5000000000,-10000000000.0,'AAA',x'1122FF')")); |
|
929 TEST2(err, 1); |
|
930 |
|
931 RSqlStatement stmt; |
|
932 err = stmt.Prepare(TheDb, _L("SELECT I32,I64,D,T,B FROM A")); |
|
933 TEST2(err, KErrNone); |
|
934 |
|
935 err = stmt.Next(); |
|
936 TEST2(err, KSqlAtRow); |
|
937 TPtrC val; |
|
938 err = stmt.ColumnText(0, val); |
|
939 TEST2(err, KErrNone); |
|
940 TEST(val == KNullDesC); |
|
941 err = stmt.ColumnText(1, val); |
|
942 TEST2(err, KErrNone); |
|
943 TEST(val == KNullDesC); |
|
944 err = stmt.ColumnText(2, val); |
|
945 TEST2(err, KErrNone); |
|
946 TEST(val == KNullDesC); |
|
947 err = stmt.ColumnText(3, val); |
|
948 TEST2(err, KErrNone); |
|
949 TEST2(val.Length(), 3); |
|
950 TEST(val == _L("AAA")); |
|
951 err = stmt.ColumnText(4, val); |
|
952 TEST2(err, KErrNone); |
|
953 TEST(val == KNullDesC); |
|
954 |
|
955 stmt.Close(); |
|
956 |
|
957 TheDb.Close(); |
|
958 err = RSqlDatabase::Delete(KTestDbName); |
|
959 TEST2(err, KErrNone); |
|
960 } |
|
961 |
|
962 /** |
|
963 @SYMTestCaseID SYSLIB-SQL-UT-3506 |
|
964 @SYMTestCaseDesc Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low. |
|
965 The test creates a test table with INT32, INT64, DOUBLE, TEXT, and BINARY columns |
|
966 and inserts a record there. Then the test prepares a SELECT statement, |
|
967 retrieves all table rows, and to retrieve each column value, calls RSqlStatement::ColumnBinary(). |
|
968 Summary: the test checks the ColumnBinary() behaviour when the column value type is not BINARY. |
|
969 (In all non-BINARY cases the method is expected to return KNullDesC8) |
|
970 @SYMTestPriority High |
|
971 @SYMTestActions Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low. |
|
972 @SYMTestExpectedResults Test must not fail |
|
973 @SYMDEF DEF109100 |
|
974 */ |
|
975 void ColumnBinaryTest() |
|
976 { |
|
977 (void)RSqlDatabase::Delete(KTestDbName); |
|
978 TInt err = TheDb.Create(KTestDbName); |
|
979 TEST2(err, KErrNone); |
|
980 err = TheDb.Exec(_L("CREATE TABLE A(I32 INTEGER,I64 INTEGER,D DOUBLE,T TEXT,B BINARY)")); |
|
981 TEST(err >= 0); |
|
982 |
|
983 err = TheDb.Exec(_L("INSERT INTO A VALUES(-5,-5000000000,-10000000000.0,'AAA',x'1122FF')")); |
|
984 TEST2(err, 1); |
|
985 |
|
986 RSqlStatement stmt; |
|
987 err = stmt.Prepare(TheDb, _L("SELECT I32,I64,D,T,B FROM A")); |
|
988 TEST2(err, KErrNone); |
|
989 |
|
990 err = stmt.Next(); |
|
991 TEST2(err, KSqlAtRow); |
|
992 TPtrC8 val; |
|
993 err = stmt.ColumnBinary(0, val); |
|
994 TEST2(err, KErrNone); |
|
995 TEST(val == KNullDesC8); |
|
996 err = stmt.ColumnBinary(1, val); |
|
997 TEST2(err, KErrNone); |
|
998 TEST(val == KNullDesC8); |
|
999 err = stmt.ColumnBinary(2, val); |
|
1000 TEST2(err, KErrNone); |
|
1001 TEST(val == KNullDesC8); |
|
1002 err = stmt.ColumnBinary(3, val); |
|
1003 TEST2(err, KErrNone); |
|
1004 TEST(val == KNullDesC8); |
|
1005 err = stmt.ColumnBinary(4, val); |
|
1006 TEST2(err, KErrNone); |
|
1007 TEST2(val.Length(), 3); |
|
1008 TEST(val[0] == 0x11); |
|
1009 TEST(val[1] == 0x22); |
|
1010 TEST(val[2] == 0xFF); |
|
1011 |
|
1012 stmt.Close(); |
|
1013 |
|
1014 TheDb.Close(); |
|
1015 err = RSqlDatabase::Delete(KTestDbName); |
|
1016 TEST2(err, KErrNone); |
|
1017 } |
|
1018 |
|
1019 void DoTestsL() |
|
1020 { |
|
1021 TheTest.Start(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1611 E011 numeric data types test ")); |
|
1022 NumericDataTypesTest(); |
|
1023 |
|
1024 TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1630 Built-in functions test ")); |
|
1025 BuiltInFunctionsTest(); |
|
1026 |
|
1027 TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1632 \"Attach database\" test ")); |
|
1028 AttachDatabaseTest(); |
|
1029 |
|
1030 TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-3502 \"RSqlStatement::ColumnInt()\" test ")); |
|
1031 ColumnIntTest(); |
|
1032 |
|
1033 TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-3503 \"RSqlStatement::ColumnInt64()\" test ")); |
|
1034 ColumnInt64Test(); |
|
1035 |
|
1036 TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-3504 \"RSqlStatement::ColumnReal()\" test ")); |
|
1037 ColumnRealTest(); |
|
1038 |
|
1039 TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-3505 \"RSqlStatement::ColumnText()\" test ")); |
|
1040 ColumnTextTest(); |
|
1041 |
|
1042 TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-3506 \"RSqlStatement::ColumnBinary()\" test ")); |
|
1043 ColumnBinaryTest(); |
|
1044 } |
|
1045 |
|
1046 TInt E32Main() |
|
1047 { |
|
1048 TheTest.Title(); |
|
1049 |
|
1050 CTrapCleanup* tc = CTrapCleanup::New(); |
|
1051 |
|
1052 __UHEAP_MARK; |
|
1053 |
|
1054 CreateTestDir(); |
|
1055 DeleteTestFiles(); |
|
1056 TRAPD(err, DoTestsL()); |
|
1057 DeleteTestFiles(); |
|
1058 TEST2(err, KErrNone); |
|
1059 |
|
1060 __UHEAP_MARKEND; |
|
1061 |
|
1062 TheTest.End(); |
|
1063 TheTest.Close(); |
|
1064 |
|
1065 delete tc; |
|
1066 |
|
1067 User::Heap().Check(); |
|
1068 return KErrNone; |
|
1069 } |