1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sql/TEST/t_sqllang.cpp Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,1069 @@
1.4 +// Copyright (c) 2005-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 <e32math.h>
1.21 +#include <bautils.h>
1.22 +#include <sqldb.h>
1.23 +
1.24 +///////////////////////////////////////////////////////////////////////////////////////
1.25 +
1.26 +RTest TheTest(_L("t_sqllang test"));
1.27 +_LIT(KTestDir, "c:\\test\\");
1.28 +
1.29 +_LIT(KTestDbName, "c:\\test\\t_sqllang_1.db");
1.30 +_LIT(KTestDbName2, "c:\\test\\t_sqllang_2.db");
1.31 +
1.32 +RSqlDatabase TheDb;
1.33 +
1.34 +///////////////////////////////////////////////////////////////////////////////////////
1.35 +
1.36 +void DeleteTestFiles()
1.37 + {
1.38 + RSqlDatabase::Delete(KTestDbName2);
1.39 + RSqlDatabase::Delete(KTestDbName);
1.40 + }
1.41 +
1.42 +///////////////////////////////////////////////////////////////////////////////////////
1.43 +///////////////////////////////////////////////////////////////////////////////////////
1.44 +//Test macros and functions
1.45 +void Check(TInt aValue, TInt aLine)
1.46 + {
1.47 + if(!aValue)
1.48 + {
1.49 + DeleteTestFiles();
1.50 + TheTest(EFalse, aLine);
1.51 + }
1.52 + }
1.53 +void Check(TInt aValue, TInt aExpected, TInt aLine)
1.54 + {
1.55 + if(aValue != aExpected)
1.56 + {
1.57 + DeleteTestFiles();
1.58 + RDebug::Print(_L("*** Expected error: %d, got: %d\r\n"), aExpected, aValue);
1.59 + TheTest(EFalse, aLine);
1.60 + }
1.61 + }
1.62 +void Check2(TInt64 aValue, TInt64 aExpected, TInt aLine)
1.63 + {
1.64 + if(aValue != aExpected)
1.65 + {
1.66 + DeleteTestFiles();
1.67 + RDebug::Print(_L("*** Expected error: %ld, got: %ld\r\n"), aExpected, aValue);
1.68 + TheTest(EFalse, aLine);
1.69 + }
1.70 + }
1.71 +#define TEST(arg) ::Check((arg), __LINE__)
1.72 +#define TEST2(aValue, aExpected) ::Check(aValue, aExpected, __LINE__)
1.73 +#define TEST3(aValue, aExpected) ::Check2(aValue, aExpected, __LINE__)
1.74 +
1.75 +///////////////////////////////////////////////////////////////////////////////////////
1.76 +
1.77 +void CreateTestDir()
1.78 + {
1.79 + RFs fs;
1.80 + TInt err = fs.Connect();
1.81 + TEST2(err, KErrNone);
1.82 +
1.83 + err = fs.MkDir(KTestDir);
1.84 + TEST(err == KErrNone || err == KErrAlreadyExists);
1.85 +
1.86 + fs.Close();
1.87 + }
1.88 +
1.89 +///////////////////////////////////////////////////////////////////////////////////////
1.90 +
1.91 +void PrintLastMsgIfError(TInt aErr)
1.92 + {
1.93 + if(aErr < 0 && SqlRetCodeClass(aErr) == ESqlDbError)
1.94 + {
1.95 + const TPtrC& msg = TheDb.LastErrorMessage();
1.96 + RDebug::Print(_L("Last error msg: \"%S\"\r\n"), &msg);
1.97 + }
1.98 + }
1.99 +
1.100 +/**
1.101 +@SYMTestCaseID SYSLIB-SQL-CT-1611
1.102 +@SYMTestCaseDesc Create a table with INTEGER, SMALLINT, REAl, DOUBLE PRECISION, FLOAT, DECIMAL,...
1.103 + columns. Insert some records, retrieve the column values, verify the column values,
1.104 + try some mathematical operations with the column values, executed in the SQL
1.105 + statement.
1.106 +@SYMTestPriority High
1.107 +@SYMTestActions Testing SQL engine behaviour with different numerical types.
1.108 + Testing mathematical operations with numeric columns.
1.109 +@SYMTestExpectedResults Test must not fail
1.110 +@SYMREQ REQ5792
1.111 + REQ5793
1.112 +*/
1.113 +void NumericDataTypesTest()
1.114 + {
1.115 + TEST2(TheDb.Create(KTestDbName), KErrNone);
1.116 +
1.117 + //Create a table with all possible numeric field types
1.118 + _LIT8(KSql1, "CREATE TABLE Tbl(A INTEGER, B SMALLINT, C REAL, D DOUBLE PRECISION, E FLOAT, \
1.119 + F DECIMAL, G BIGINT, H TINYINT, I BIT, J NUMERIC, K MONEY, \
1.120 + L SMALLMONEY)");
1.121 + TInt err = TheDb.Exec(KSql1);
1.122 + PrintLastMsgIfError(err);
1.123 + TEST(err >= 0);
1.124 + //Insert one record in to the created table
1.125 + _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,\
1.126 + 1.34E-14, 1234.5678, 32000000000, 100, 7, 23.123456, 2123678.56, 11.45)");
1.127 + err = TheDb.Exec(KSql2);
1.128 + PrintLastMsgIfError(err);
1.129 + TEST2(err, 1);
1.130 + //Get the inserted record data
1.131 + RSqlStatement stmt;
1.132 + err = stmt.Prepare(TheDb, _L("SELECT * FROM TBL"));
1.133 + PrintLastMsgIfError(err);
1.134 + TEST2(err, KErrNone);
1.135 + err = stmt.Next();
1.136 + PrintLastMsgIfError(err);
1.137 + TEST2(err, KSqlAtRow);
1.138 + //Check column values
1.139 + TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("a"))) == 2000000000);
1.140 + TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("b"))) == 30000);
1.141 + TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("c"))) - 123.45) < 0.000001);
1.142 + //The next column value test is not working! The extracted column value is 0.9120000000000002E+55
1.143 + //I guess the reason is that SQLITE uses manifest typing and "DOUBLE PRECISION" is not in its keyword
1.144 + //list. Maybe it is interpreted and stored as 4-byte float value.
1.145 + //TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("d"))) - 0.912E+55) < 0.000001);
1.146 + TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("e"))) - 1.34E-14) < 0.000001);
1.147 + TEST(stmt.ColumnReal(stmt.ColumnIndex(_L("f"))) == 1234.5678);
1.148 + TEST(stmt.ColumnInt64(stmt.ColumnIndex(_L("g"))) == 32000000000LL);
1.149 + TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("h"))) == 100);
1.150 + TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("i"))) == 7);
1.151 + TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("j"))) - 23.123456) < 0.000001);
1.152 + TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("k"))) - 2123678.56) < 0.000001);
1.153 + TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("l"))) - 11.45) < 0.000001);
1.154 + stmt.Close();
1.155 + //The statement object has to be closed before TheDb.Exec() call,
1.156 + //otherwise the reported error is "database table is locked"
1.157 + //Insert second record in to the created table but inverse the column types!
1.158 + _LIT8(KSql3, "INSERT INTO Tbl(A, B, C, D, E, F, G, H, I, J,K,L) VALUES(\
1.159 + -2.5,1.1,12,23,45,111,5.6,7.9,1.1,2,6,7)");
1.160 + err = TheDb.Exec(KSql3);
1.161 + PrintLastMsgIfError(err);
1.162 + TEST2(err, 1);
1.163 + //Get the inserted record data
1.164 + err = stmt.Prepare(TheDb, _L("SELECT * FROM TBL"));
1.165 + PrintLastMsgIfError(err);
1.166 + TEST2(err, KErrNone);
1.167 + TEST2(stmt.Next(), KSqlAtRow);
1.168 + err = stmt.Next();
1.169 + PrintLastMsgIfError(err);
1.170 + TEST2(err, KSqlAtRow);
1.171 + //No column value checking because SQLITE inverted the column types!
1.172 + //Check column values
1.173 + TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("a"))) - (-2.5)) < 0.000001);
1.174 + TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("b"))) - 1.1) < 0.000001);
1.175 + TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("c"))) == 12);
1.176 + TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("d"))) == 23);
1.177 + TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("e"))) == 45);
1.178 + TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("f"))) == 111);
1.179 + TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("g"))) - 5.6) < 0.000001);
1.180 + TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("h"))) - 7.9) < 0.000001);
1.181 + TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("i"))) - 1.1) < 0.000001);
1.182 + TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("j"))) == 2);
1.183 + TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("k"))) == 6);
1.184 + TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("l"))) == 7);
1.185 +
1.186 + stmt.Close();
1.187 + //Insert third record in to the created table
1.188 + _LIT8(KSql4, "INSERT INTO Tbl(A,B,C, D, E, F, G, H, I,J, K, L) VALUES(\
1.189 + 2,3,123.45,1.5,2.5,1.56,320,100,7,23.123,212.56,11.45)");
1.190 + err = TheDb.Exec(KSql4);
1.191 + PrintLastMsgIfError(err);
1.192 + TEST2(err, 1);
1.193 + //Issue a "SELECT" statement doing there some arithmetic operations and comparisons on the column values.
1.194 + 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"));
1.195 + PrintLastMsgIfError(err);
1.196 + TEST2(err, KErrNone);
1.197 + TEST2(stmt.Next(), KSqlAtRow);
1.198 + //Check column values
1.199 + TEST2(stmt.ColumnInt(stmt.ColumnIndex(_L("a"))), 2);
1.200 + TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("a2"))) - (2-123.45)) < 0.000001);
1.201 + TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("a3"))) - (212.56+11.45)) < 0.000001);
1.202 + TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("a4"))) == 700);
1.203 + TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("a5"))) - (2.5/1.5)) < 0.000001);
1.204 + //There should be no more records
1.205 + TEST2(stmt.Next(), KSqlAtEnd);
1.206 + stmt.Close();
1.207 +
1.208 + TheDb.Close();
1.209 + (void)RSqlDatabase::Delete(KTestDbName);
1.210 + }
1.211 +
1.212 +/**
1.213 +@SYMTestCaseID SYSLIB-SQL-CT-1630
1.214 +@SYMTestCaseDesc Built-in functions test.
1.215 + abs(), coalesce(), ifnull(), last_insert_rowid(), length(), like(), lower(),
1.216 + max(), min(), nullif(), quote(), random(), round(), sqlite_version(), substr(),
1.217 + typeof(), upper(), avg(), count(), sun().
1.218 +@SYMTestPriority High
1.219 +@SYMTestActions Built-in functions test.
1.220 +@SYMTestExpectedResults Test must not fail
1.221 +@SYMREQ REQ5792
1.222 + REQ5793
1.223 +*/
1.224 +void BuiltInFunctionsTest()
1.225 + {
1.226 + (void)RSqlDatabase::Delete(KTestDbName);
1.227 + TInt err = TheDb.Create(KTestDbName);
1.228 + TEST2(err, KErrNone);
1.229 +
1.230 + _LIT(KCreateSql, "CREATE TABLE A(Id Integer, \
1.231 + F1 Integer DEFAULT 0, \
1.232 + F2 Integer Default NULL, \
1.233 + F3 TEXT Default Null, \
1.234 + F4 TEXT Default Null)");
1.235 + err = TheDb.Exec(KCreateSql);
1.236 + TEST(err >= 0);
1.237 +
1.238 + _LIT(KInsertSql1, "INSERT INTO A(Id, F1) VALUES(1, 100)");
1.239 + err = TheDb.Exec(KInsertSql1);
1.240 + TEST2(err, 1);
1.241 +
1.242 + _LIT(KInsertSql2, "INSERT INTO A(Id, F1) VALUES(2, 1)");
1.243 + err = TheDb.Exec(KInsertSql2);
1.244 + TEST2(err, 1);
1.245 +
1.246 + _LIT(KInsertSql3, "INSERT INTO A(Id, F3) VALUES(3, 'ABCD')");
1.247 + err = TheDb.Exec(KInsertSql3);
1.248 + TEST2(err, 1);
1.249 +
1.250 + _LIT(KInsertSql4, "INSERT INTO A(Id, F4) VALUES(4, 'DCBA')");
1.251 + err = TheDb.Exec(KInsertSql4);
1.252 + TEST2(err, 1);
1.253 +
1.254 + RSqlStatement stmt;
1.255 +
1.256 + //abs() test --------------------------------------------------------
1.257 + err = stmt.Prepare(TheDb, _L("SELECT * FROM A WHERE F1 > ABS(-10)"));
1.258 + TEST2(err, KErrNone);
1.259 + err = stmt.Next();
1.260 + TEST2(err, KSqlAtRow);
1.261 + TEST(stmt.ColumnInt(1) == 100);
1.262 + stmt.Close();
1.263 +
1.264 + //coalesce() test --------------------------------------------------------
1.265 + err = stmt.Prepare(TheDb, _L("SELECT COALESCE(F3, F4) AS F FROM A"));
1.266 + TEST2(err, KErrNone);
1.267 +
1.268 + err = stmt.Next();
1.269 + TEST2(err, KSqlAtRow);
1.270 + TEST(stmt.IsNull(0));
1.271 +
1.272 + err = stmt.Next();
1.273 + TEST2(err, KSqlAtRow);
1.274 + TEST(stmt.IsNull(0));
1.275 +
1.276 + _LIT(KTextVal1, "ABCD");
1.277 + err = stmt.Next();
1.278 + TEST2(err, KSqlAtRow);
1.279 + TPtrC colVal;
1.280 + err = stmt.ColumnText(0, colVal);
1.281 + TEST2(err, KErrNone);
1.282 + TEST(colVal == KTextVal1);
1.283 +
1.284 + _LIT(KTextVal2, "DCBA");
1.285 + err = stmt.Next();
1.286 + TEST2(err, KSqlAtRow);
1.287 + err = stmt.ColumnText(0, colVal);
1.288 + TEST2(err, KErrNone);
1.289 + TEST(colVal == KTextVal2);
1.290 +
1.291 + stmt.Close();
1.292 +
1.293 + //ifnull() test --------------------------------------------------------
1.294 + err = stmt.Prepare(TheDb, _L("SELECT IFNULL(F3, F4) AS F FROM A"));
1.295 + TEST2(err, KErrNone);
1.296 +
1.297 + err = stmt.Next();
1.298 + TEST2(err, KSqlAtRow);
1.299 + TEST(stmt.IsNull(0));
1.300 +
1.301 + err = stmt.Next();
1.302 + TEST2(err, KSqlAtRow);
1.303 + TEST(stmt.IsNull(0));
1.304 +
1.305 + err = stmt.Next();
1.306 + TEST2(err, KSqlAtRow);
1.307 + err = stmt.ColumnText(0, colVal);
1.308 + TEST2(err, KErrNone);
1.309 + TEST(colVal == KTextVal1);
1.310 +
1.311 + err = stmt.Next();
1.312 + TEST2(err, KSqlAtRow);
1.313 + err = stmt.ColumnText(0, colVal);
1.314 + TEST2(err, KErrNone);
1.315 + TEST(colVal == KTextVal2);
1.316 +
1.317 + stmt.Close();
1.318 +
1.319 + //last_insert_rowid() test --------------------------------------------------------
1.320 + err = stmt.Prepare(TheDb, _L("SELECT last_insert_rowid() AS F"));
1.321 + TEST2(err, KErrNone);
1.322 + err = stmt.Next();
1.323 + TEST2(err, KSqlAtRow);
1.324 + TInt64 colVal64 = stmt.ColumnInt64(0);
1.325 + RDebug::Print(_L("Last insert row id=%d\r\n"), (TInt)colVal64);
1.326 + stmt.Close();
1.327 +
1.328 + //length() test --------------------------------------------------------
1.329 + err = stmt.Prepare(TheDb, _L("SELECT length(F4) AS L FROM A WHERE ID >= 3"));
1.330 + TEST2(err, KErrNone);
1.331 +
1.332 + err = stmt.Next();
1.333 + TEST2(err, KSqlAtRow);
1.334 + TEST(stmt.ColumnInt(0) == 0);
1.335 +
1.336 + err = stmt.Next();
1.337 + TEST2(err, KSqlAtRow);
1.338 + TEST(stmt.ColumnInt(0) == 4);
1.339 +
1.340 + stmt.Close();
1.341 +
1.342 + //like() test --------------------------------------------------------
1.343 + err = stmt.Prepare(TheDb, _L("SELECT Id FROM A WHERE F4 LIKE 'DC%'"));
1.344 + TEST2(err, KErrNone);
1.345 + err = stmt.Next();
1.346 + TEST2(err, KSqlAtRow);
1.347 + TEST(stmt.ColumnInt(0) == 4);
1.348 + stmt.Close();
1.349 +
1.350 + _LIT(KInsertSql5, "INSERT INTO A(Id, F4) VALUES(5, 'ab%cd')");
1.351 + err = TheDb.Exec(KInsertSql5);
1.352 + TEST2(err, 1);
1.353 +
1.354 + err = stmt.Prepare(TheDb, _L("SELECT Id FROM A WHERE F4 LIKE 'ab/%cd' ESCAPE '/'"));
1.355 + TEST2(err, KErrNone);
1.356 + err = stmt.Next();
1.357 + TEST2(err, KSqlAtRow);
1.358 + TEST(stmt.ColumnInt(0) == 5);
1.359 + stmt.Close();
1.360 +
1.361 + err = stmt.Prepare(TheDb, _L8("SELECT Id FROM A WHERE F4 LIKE 'ab/%cd' ESCAPE '/'"));
1.362 + TEST2(err, KErrNone);
1.363 + err = stmt.Next();
1.364 + TEST2(err, KSqlAtRow);
1.365 + TEST(stmt.ColumnInt(0) == 5);
1.366 + stmt.Close();
1.367 +
1.368 + //lower() test --------------------------------------------------------
1.369 + err = stmt.Prepare(TheDb, _L("SELECT LOWER(F3) FROM A WHERE F3 = 'ABCD'"));
1.370 + TEST2(err, KErrNone);
1.371 + err = stmt.Next();
1.372 + TEST2(err, KSqlAtRow);
1.373 + err = stmt.ColumnText(0, colVal);
1.374 + TEST2(err, KErrNone);
1.375 + _LIT(KTextVal3, "abcd");
1.376 + TEST(colVal == KTextVal3);
1.377 + stmt.Close();
1.378 +
1.379 + //max() test --------------------------------------------------------
1.380 + err = stmt.Prepare(TheDb, _L("SELECT MAX(F1) AS M FROM A"));
1.381 + TEST2(err, KErrNone);
1.382 + err = stmt.Next();
1.383 + TEST2(err, KSqlAtRow);
1.384 + TEST(stmt.ColumnInt(0) == 100);
1.385 + stmt.Close();
1.386 +
1.387 + //min() test --------------------------------------------------------
1.388 + err = stmt.Prepare(TheDb, _L("SELECT MIN(F1) AS M FROM A"));
1.389 + TEST2(err, KErrNone);
1.390 + err = stmt.Next();
1.391 + TEST2(err, KSqlAtRow);
1.392 + TEST(stmt.ColumnInt(0) == 0);
1.393 + stmt.Close();
1.394 +
1.395 + //nullif() test --------------------------------------------------------
1.396 + err = stmt.Prepare(TheDb, _L("SELECT NULLIF(3, 4) AS M"));
1.397 + TEST2(err, KErrNone);
1.398 + err = stmt.Next();
1.399 + TEST2(err, KSqlAtRow);
1.400 + TEST(stmt.ColumnInt(0) == 3);
1.401 + stmt.Close();
1.402 +
1.403 + err = stmt.Prepare(TheDb, _L("SELECT NULLIF(4, 4) AS M"));
1.404 + TEST2(err, KErrNone);
1.405 + err = stmt.Next();
1.406 + TEST2(err, KSqlAtRow);
1.407 + TEST(stmt.IsNull(0));
1.408 + stmt.Close();
1.409 +
1.410 + //quote() test --------------------------------------------------------
1.411 + err = stmt.Prepare(TheDb, _L("SELECT QUOTE(F4) AS M FROM A WHERE Id = 5"));
1.412 + TEST2(err, KErrNone);
1.413 + err = stmt.Next();
1.414 + TEST2(err, KSqlAtRow);
1.415 + err = stmt.ColumnText(0, colVal);
1.416 + TEST2(err, KErrNone);
1.417 + _LIT(KTextVal4, "'ab%cd'");
1.418 + TEST(colVal == KTextVal4);
1.419 + stmt.Close();
1.420 +
1.421 + //random() test --------------------------------------------------------
1.422 + err = stmt.Prepare(TheDb, _L("SELECT * FROM A WHERE Id > RANDOM(*)"));
1.423 + TEST2(err, KErrNone);
1.424 + stmt.Close();
1.425 +
1.426 + //round() test --------------------------------------------------------
1.427 + err = stmt.Prepare(TheDb, _L("SELECT ROUND(5.4) AS D"));
1.428 + TEST2(err, KErrNone);
1.429 + err = stmt.Next();
1.430 + TEST2(err, KSqlAtRow);
1.431 + TEST(Abs(stmt.ColumnReal(0) - 5.0) < 0.000001);
1.432 + stmt.Close();
1.433 +
1.434 + err = stmt.Prepare(TheDb, _L("SELECT ROUND(5.4321, 2) AS D"));
1.435 + TEST2(err, KErrNone);
1.436 + err = stmt.Next();
1.437 + TEST2(err, KSqlAtRow);
1.438 + TEST(Abs(stmt.ColumnReal(0) - 5.43) < 0.000001);
1.439 + stmt.Close();
1.440 +
1.441 + //sqlite_version() test --------------------------------------------------------
1.442 + err = stmt.Prepare(TheDb, _L("SELECT sqlite_version(*) AS V"));
1.443 + TEST2(err, KErrNone);
1.444 + err = stmt.Next();
1.445 + TEST2(err, KSqlAtRow);
1.446 + err = stmt.ColumnText(0, colVal);
1.447 + TEST2(err, KErrNone);
1.448 + RDebug::Print(_L("Database engine version: \"%S\"\r\n"), &colVal);
1.449 + stmt.Close();
1.450 +
1.451 + //substr() test --------------------------------------------------------
1.452 + err = stmt.Prepare(TheDb, _L("SELECT SUBSTR('abcd', 2, 2) AS S"));
1.453 + TEST2(err, KErrNone);
1.454 + err = stmt.Next();
1.455 + TEST2(err, KSqlAtRow);
1.456 + err = stmt.ColumnText(0, colVal);
1.457 + TEST2(err, KErrNone);
1.458 + _LIT(KTextVal7, "bc");
1.459 + TEST(colVal == KTextVal7);
1.460 + stmt.Close();
1.461 +
1.462 + err = stmt.Prepare(TheDb, _L("SELECT SUBSTR('abcd', -3, 2) AS S"));
1.463 + TEST2(err, KErrNone);
1.464 + err = stmt.Next();
1.465 + TEST2(err, KSqlAtRow);
1.466 + err = stmt.ColumnText(0, colVal);
1.467 + TEST2(err, KErrNone);
1.468 + TEST(colVal == KTextVal7);
1.469 + stmt.Close();
1.470 +
1.471 + //typeof() test --------------------------------------------------------
1.472 + _LIT(KInsertSql6, "INSERT INTO A(Id, F2) VALUES(6, 2)");
1.473 + err = TheDb.Exec(KInsertSql6);
1.474 + TEST2(err, 1);
1.475 +
1.476 + err = stmt.Prepare(TheDb, _L("SELECT TYPEOF(F1 + F2) AS T FROM A WHERE ID > 4"));
1.477 + TEST2(err, KErrNone);
1.478 +
1.479 + err = stmt.Next();
1.480 + TEST2(err, KSqlAtRow);
1.481 + err = stmt.ColumnText(0, colVal);
1.482 + TEST2(err, KErrNone);
1.483 + _LIT(KTextVal8, "null");
1.484 + TEST(colVal == KTextVal8);
1.485 +
1.486 + err = stmt.Next();
1.487 + TEST2(err, KSqlAtRow);
1.488 + err = stmt.ColumnText(0, colVal);
1.489 + TEST2(err, KErrNone);
1.490 + _LIT(KTextVal9, "integer");
1.491 + TEST(colVal == KTextVal9);
1.492 +
1.493 + stmt.Close();
1.494 +
1.495 + //upper() test --------------------------------------------------------
1.496 + err = stmt.Prepare(TheDb, _L("SELECT UPPER('ghjk') AS U"));
1.497 + TEST2(err, KErrNone);
1.498 + err = stmt.Next();
1.499 + TEST2(err, KSqlAtRow);
1.500 + err = stmt.ColumnText(0, colVal);
1.501 + TEST2(err, KErrNone);
1.502 + _LIT(KTextVal10, "GHJK");
1.503 + TEST(colVal == KTextVal10);
1.504 + stmt.Close();
1.505 +
1.506 + //avg() test --------------------------------------------------------
1.507 + err = stmt.Prepare(TheDb, _L("SELECT AVG(F2) AS F FROM A"));
1.508 + TEST2(err, KErrNone);
1.509 + err = stmt.Next();
1.510 + TEST2(err, KSqlAtRow);
1.511 + TEST(Abs(stmt.ColumnReal(0) - 2) < 0.000001);
1.512 + stmt.Close();
1.513 +
1.514 + //count() test --------------------------------------------------------
1.515 + err = stmt.Prepare(TheDb, _L("SELECT COUNT(F2) AS F FROM A"));
1.516 + TEST2(err, KErrNone);
1.517 + err = stmt.Next();
1.518 + TEST2(err, KSqlAtRow);
1.519 + TEST(stmt.ColumnInt(0) == 1);
1.520 + stmt.Close();
1.521 +
1.522 + err = stmt.Prepare(TheDb, _L("SELECT COUNT(*) AS F FROM A"));
1.523 + TEST2(err, KErrNone);
1.524 + err = stmt.Next();
1.525 + TEST2(err, KSqlAtRow);
1.526 + TEST(stmt.ColumnInt(0) == 6);
1.527 + stmt.Close();
1.528 +
1.529 + //sum() test --------------------------------------------------------
1.530 + err = stmt.Prepare(TheDb, _L("SELECT SUM(F2) AS S FROM A"));
1.531 + TEST2(err, KErrNone);
1.532 + err = stmt.Next();
1.533 + TEST2(err, KSqlAtRow);
1.534 + TEST(Abs(stmt.ColumnReal(0) - 2) < 0.000001);
1.535 + stmt.Close();
1.536 +
1.537 + // ----------------------------------
1.538 + TheDb.Close();
1.539 + (void)RSqlDatabase::Delete(KTestDbName);
1.540 + }
1.541 +
1.542 +/**
1.543 +@SYMTestCaseID SYSLIB-SQL-CT-1632
1.544 +@SYMTestCaseDesc "ATTACH DATABASE" test.
1.545 + The test creates two databases. The main database has
1.546 + Account(Id Integer, PersonId Integer, Value Integer) table.
1.547 + The attached database has Person(Id Integer, Name TEXT) table.
1.548 + The test inserts some records with a valid relation between them in both tables.
1.549 + Then the test opens tha main database and attaches the second database to the first one.
1.550 + The test prepares and executes SQL statement which retrieves column values from both
1.551 + Account and Person tables. The test checks the column values.
1.552 +@SYMTestPriority High
1.553 +@SYMTestActions "ATTACH DATABASE" test.
1.554 +@SYMTestExpectedResults Test must not fail
1.555 +@SYMREQ REQ5792
1.556 + REQ5793
1.557 +*/
1.558 +void AttachDatabaseTest()
1.559 + {
1.560 + // ------------------------------------------------------------
1.561 + RSqlDatabase db2;
1.562 + TInt err = db2.Create(KTestDbName2);
1.563 + TEST2(err, KErrNone);
1.564 +
1.565 + err = db2.Exec(_L("CREATE TABLE Person(Id Integer, Name TEXT)"));
1.566 + TEST(err >= 0);
1.567 +
1.568 + err = db2.Exec(_L("INSERT INTO Person(Id, Name) VALUES(1, 'A')"));
1.569 + TEST2(err, 1);
1.570 +
1.571 + err = db2.Exec(_L("INSERT INTO Person(Id, Name) VALUES(2, 'B')"));
1.572 + TEST2(err, 1);
1.573 +
1.574 + err = db2.Exec(_L("INSERT INTO Person(Id, Name) VALUES(3, 'C')"));
1.575 + TEST2(err, 1);
1.576 +
1.577 + db2.Close();
1.578 +
1.579 + // ------------------------------------------------------------
1.580 + (void)RSqlDatabase::Delete(KTestDbName);
1.581 + err = TheDb.Create(KTestDbName);
1.582 + TEST2(err, KErrNone);
1.583 +
1.584 + err = TheDb.Exec(_L("CREATE TABLE Account(Id Integer, PersonId Integer, Value Integer)"));
1.585 + TEST(err >= 0);
1.586 +
1.587 + err = TheDb.Exec(_L("INSERT INTO ACCOUNT(Id, PersonId, Value) VALUES(1, 2, 20)"));
1.588 + TEST2(err, 1);
1.589 +
1.590 + err = TheDb.Exec(_L("INSERT INTO ACCOUNT(Id, PersonId, Value) VALUES(2, 1, 10)"));
1.591 + TEST2(err, 1);
1.592 +
1.593 + err = TheDb.Exec(_L("INSERT INTO ACCOUNT(Id, PersonId, Value) VALUES(3, 2, 25)"));
1.594 + TEST2(err, 1);
1.595 +
1.596 + err = TheDb.Exec(_L("INSERT INTO ACCOUNT(Id, PersonId, Value) VALUES(4, 3, 30)"));
1.597 + TEST2(err, 1);
1.598 +
1.599 + TheDb.Close();
1.600 +
1.601 + // ------------------------------------------------------------
1.602 + err = TheDb.Open(KTestDbName);
1.603 + TEST2(err, KErrNone);
1.604 +
1.605 + TBuf<100> sql;
1.606 + sql.Copy(_L("ATTACH DATABASE '"));
1.607 + sql.Append(KTestDbName2);
1.608 + sql.Append(_L("' AS DB2"));
1.609 + err = TheDb.Exec(sql);
1.610 + TEST(err >= 0);
1.611 +
1.612 + RSqlStatement stmt;
1.613 + err = stmt.Prepare(TheDb, _L("SELECT Account.Value, DB2.Person.Name FROM Account, DB2.Person \
1.614 + WHERE Account.PersonId = DB2.Person.Id"));
1.615 + TEST2(err, KErrNone);
1.616 +
1.617 + TPtrC personName;
1.618 + _LIT(KName1, "A");
1.619 + _LIT(KName2, "B");
1.620 + _LIT(KName3, "C");
1.621 +
1.622 + err = stmt.Next();
1.623 + TEST2(err, KSqlAtRow);
1.624 + err = stmt.ColumnText(1, personName);
1.625 + TEST2(err, KErrNone);
1.626 + TEST(personName == KName2);
1.627 + TEST(stmt.ColumnInt(0) == 20);
1.628 +
1.629 + err = stmt.Next();
1.630 + TEST2(err, KSqlAtRow);
1.631 + err = stmt.ColumnText(1, personName);
1.632 + TEST2(err, KErrNone);
1.633 + TEST(personName == KName1);
1.634 + TEST(stmt.ColumnInt(0) == 10);
1.635 +
1.636 + err = stmt.Next();
1.637 + TEST2(err, KSqlAtRow);
1.638 + err = stmt.ColumnText(1, personName);
1.639 + TEST2(err, KErrNone);
1.640 + TEST(personName == KName2);
1.641 + TEST(stmt.ColumnInt(0) == 25);
1.642 +
1.643 + err = stmt.Next();
1.644 + TEST2(err, KSqlAtRow);
1.645 + err = stmt.ColumnText(1, personName);
1.646 + TEST2(err, KErrNone);
1.647 + TEST(personName == KName3);
1.648 + TEST(stmt.ColumnInt(0) == 30);
1.649 +
1.650 + stmt.Close();
1.651 +
1.652 + err = TheDb.Exec(_L("DETACH DATABASE DB2"));
1.653 + TEST(err >= 0);
1.654 +
1.655 + // ------------------------------------------------------------
1.656 + TheDb.Close();
1.657 + (void)RSqlDatabase::Delete(KTestDbName2);
1.658 + (void)RSqlDatabase::Delete(KTestDbName);
1.659 + }
1.660 +
1.661 +/**
1.662 +@SYMTestCaseID SYSLIB-SQL-UT-3502
1.663 +@SYMTestCaseDesc Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low.
1.664 + The test creates a test table with INT32, INT64, DOUBLE, TEXT, and BINARY columns
1.665 + and inserts couple of records there. Then the test prepares a SELECT statement,
1.666 + retrieves all table rows, and to retrieve each column value, calls RSqlStatement::ColumnInt().
1.667 + The column values are carefully chosen, so some of the ColumnInt() calls have to round
1.668 + or clamp the returned value.
1.669 + Summary: the test checks the ColumnInt() behaviour when the column value type is not INT32 and
1.670 + when the column value is too big or too small and cannot fit in 32 bits.
1.671 +@SYMTestPriority High
1.672 +@SYMTestActions Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low.
1.673 +@SYMTestExpectedResults Test must not fail
1.674 +@SYMDEF DEF109100
1.675 +*/
1.676 +void ColumnIntTest()
1.677 + {
1.678 + (void)RSqlDatabase::Delete(KTestDbName);
1.679 + TInt err = TheDb.Create(KTestDbName);
1.680 + TEST2(err, KErrNone);
1.681 + err = TheDb.Exec(_L("CREATE TABLE A(I32 INTEGER,I64 INTEGER,D DOUBLE,T TEXT,B BINARY)"));
1.682 + TEST(err >= 0);
1.683 +
1.684 + err = TheDb.Exec(_L("INSERT INTO A VALUES(-5,-5000000000,-10000000000.0,'AAA',x'1122FF')"));
1.685 + TEST2(err, 1);
1.686 + err = TheDb.Exec(_L("INSERT INTO A VALUES(5,5000000000,10000000000.0,NULL,NULL)"));
1.687 + TEST2(err, 1);
1.688 + err = TheDb.Exec(_L("INSERT INTO A VALUES(5,2000000000,1000000000.0,NULL,NULL)"));
1.689 + TEST2(err, 1);
1.690 + err = TheDb.Exec(_L("INSERT INTO A VALUES(NULL,NULL,1.1234567890123456E+317,NULL,NULL)"));
1.691 + TEST2(err, 1);
1.692 +
1.693 + RSqlStatement stmt;
1.694 + err = stmt.Prepare(TheDb, _L("SELECT I32,I64,D,T,B FROM A"));
1.695 + TEST2(err, KErrNone);
1.696 +
1.697 + err = stmt.Next();
1.698 + TEST2(err, KSqlAtRow);
1.699 + TInt val = stmt.ColumnInt(0);
1.700 + TEST2(val, -5); //"-5" is a 32-bit integer value
1.701 + val = stmt.ColumnInt(1);
1.702 + TEST2(val, KMinTInt); //"-5000000000" is a 64-bit integer, will be clamped to KMinTInt
1.703 + val = stmt.ColumnInt(2);
1.704 + TEST2(val, KMinTInt); //"-10000000000.0" is a 64-bit double, will be rounded to the nearest 32-bit integer
1.705 + val = stmt.ColumnInt(3);
1.706 + TEST2(val, 0); //"AAA" is a text string, cannot be converted to a 32-bit integer
1.707 + val = stmt.ColumnInt(4);
1.708 + TEST2(val, 0); //"1122FF" is a hex binary, cannot be converted to a 32-bit integer
1.709 +
1.710 + err = stmt.Next();
1.711 + TEST2(err, KSqlAtRow);
1.712 + val = stmt.ColumnInt(0);
1.713 + TEST2(val, 5); //"5" is a 32-bit integer value
1.714 + val = stmt.ColumnInt(1);
1.715 + TEST2(val, KMaxTInt); //"5000000000" is a 64-bit integer, will be clamped to KMaxTInt
1.716 + val = stmt.ColumnInt(2);
1.717 + TEST2(val, KMaxTInt); //"10000000000.0" is a 64-bit double, will be rounded to the nearest 32-bit integer
1.718 + val = stmt.ColumnInt(3);
1.719 + TEST2(val, 0); //NULL column value
1.720 + val = stmt.ColumnInt(4);
1.721 + TEST2(val, 0); //NULL column value
1.722 +
1.723 + err = stmt.Next();
1.724 + TEST2(err, KSqlAtRow);
1.725 + val = stmt.ColumnInt(0);
1.726 + TEST2(val, 5);
1.727 + val = stmt.ColumnInt(1);
1.728 + TEST2(val, 2000000000);
1.729 + val = stmt.ColumnInt(2);
1.730 + TEST2(val, 1000000000);
1.731 +
1.732 + err = stmt.Next();
1.733 + TEST2(err, KSqlAtRow);
1.734 + val = stmt.ColumnInt(2);
1.735 + TEST2(val, KMinTInt); //"1.1234567890123456E+317" is too big and cannot fit in a 64-bit double
1.736 +
1.737 + stmt.Close();
1.738 +
1.739 + TheDb.Close();
1.740 + err = RSqlDatabase::Delete(KTestDbName);
1.741 + TEST2(err, KErrNone);
1.742 + }
1.743 +
1.744 +/**
1.745 +@SYMTestCaseID SYSLIB-SQL-UT-3503
1.746 +@SYMTestCaseDesc Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low.
1.747 + The test creates a test table with INT32, INT64, DOUBLE, TEXT, and BINARY columns
1.748 + and inserts couple of records there. Then the test prepares a SELECT statement,
1.749 + retrieves all table rows, and to retrieve each column value, calls RSqlStatement::ColumnInt64().
1.750 + The column values are carefully chosen, so some of the ColumnInt64() calls have to round
1.751 + or clamp the returned value.
1.752 + Summary: the test checks the ColumnInt64() behaviour when the column value type is not INT64 and
1.753 + when the column value is too big or too small and cannot be presented as 64-bits integer.
1.754 +@SYMTestPriority High
1.755 +@SYMTestActions Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low.
1.756 +@SYMTestExpectedResults Test must not fail
1.757 +@SYMDEF DEF109100
1.758 +*/
1.759 +void ColumnInt64Test()
1.760 + {
1.761 + (void)RSqlDatabase::Delete(KTestDbName);
1.762 + TInt err = TheDb.Create(KTestDbName);
1.763 + TEST2(err, KErrNone);
1.764 + err = TheDb.Exec(_L("CREATE TABLE A(I32 INTEGER,I64 INTEGER,D DOUBLE,T TEXT,B BINARY)"));
1.765 + TEST(err >= 0);
1.766 +
1.767 + err = TheDb.Exec(_L("INSERT INTO A VALUES(-5,-5000000000,-10000000000.0,'AAA',x'1122FF')"));
1.768 + TEST2(err, 1);
1.769 + err = TheDb.Exec(_L("INSERT INTO A VALUES(5,5000000000,10000000000.0,NULL,NULL)"));
1.770 + TEST2(err, 1);
1.771 + err = TheDb.Exec(_L("INSERT INTO A VALUES(5,2000000000,1000000000.0,NULL,NULL)"));
1.772 + TEST2(err, 1);
1.773 + err = TheDb.Exec(_L("INSERT INTO A VALUES(NULL,NULL,1.1234567890123456E+317,NULL,NULL)"));
1.774 + TEST2(err, 1);
1.775 +
1.776 + RSqlStatement stmt;
1.777 + err = stmt.Prepare(TheDb, _L("SELECT I32,I64,D,T,B FROM A"));
1.778 + TEST2(err, KErrNone);
1.779 +
1.780 + err = stmt.Next();
1.781 + TEST2(err, KSqlAtRow);
1.782 + TInt64 val = stmt.ColumnInt64(0);
1.783 + TEST3(val, -5); //"-5" is a 32-bit integer value
1.784 + val = stmt.ColumnInt64(1);
1.785 + TEST3(val, -5000000000LL); //"-5000000000" is a 64-bit integer
1.786 + val = stmt.ColumnInt64(2);
1.787 + TEST3(val, -10000000000LL); //"-10000000000.0" is a 64-bit double, will be rounded to the nearest 64-bit integer
1.788 + val = stmt.ColumnInt64(3);
1.789 + TEST3(val, 0); //"AAA" is a text string, cannot be converted to a 64-bit integer
1.790 + val = stmt.ColumnInt64(4);
1.791 + TEST3(val, 0); //"1122FF" is a hex binary, cannot be converted to a 64-bit integer
1.792 +
1.793 + err = stmt.Next();
1.794 + TEST2(err, KSqlAtRow);
1.795 + val = stmt.ColumnInt64(0);
1.796 + TEST3(val, 5); //"5" is a 32-bit integer value
1.797 + val = stmt.ColumnInt64(1);
1.798 + TEST3(val, 5000000000LL); //"5000000000" is a 64-bit integer
1.799 + val = stmt.ColumnInt64(2);
1.800 + TEST3(val, 10000000000LL); //"10000000000.0" is a 64-bit double, will be rounded to the nearest 64-bit integer
1.801 + val = stmt.ColumnInt64(3);
1.802 + TEST3(val, 0); //NULL column value
1.803 + val = stmt.ColumnInt64(4);
1.804 + TEST3(val, 0); //NULL column value
1.805 +
1.806 + err = stmt.Next();
1.807 + TEST2(err, KSqlAtRow);
1.808 + val = stmt.ColumnInt64(0);
1.809 + TEST3(val, 5);
1.810 + val = stmt.ColumnInt64(1);
1.811 + TEST3(val, 2000000000);
1.812 + val = stmt.ColumnInt64(2);
1.813 + TEST3(val, 1000000000);
1.814 +
1.815 + err = stmt.Next();
1.816 + TEST2(err, KSqlAtRow);
1.817 + val = stmt.ColumnInt64(2);
1.818 + TEST3(val, KMinTInt64); //"1.1234567890123456E+317" is too big and cannot fit in a 64-bit double
1.819 +
1.820 + stmt.Close();
1.821 +
1.822 + TheDb.Close();
1.823 + err = RSqlDatabase::Delete(KTestDbName);
1.824 + TEST2(err, KErrNone);
1.825 + }
1.826 +
1.827 +/**
1.828 +@SYMTestCaseID SYSLIB-SQL-UT-3504
1.829 +@SYMTestCaseDesc Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low.
1.830 + The test creates a test table with INT32, INT64, DOUBLE, TEXT, and BINARY columns
1.831 + and inserts couple of records there. Then the test prepares a SELECT statement,
1.832 + retrieves all table rows, and to retrieve each column value, calls RSqlStatement::ColumnReal().
1.833 + Summary: the test checks the ColumnReal() behaviour when the column value type is not DOUBLE and
1.834 + when the column value is too big or too small and cannot be presented as 64-bits double.
1.835 +@SYMTestPriority High
1.836 +@SYMTestActions Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low.
1.837 +@SYMTestExpectedResults Test must not fail
1.838 +@SYMDEF DEF109100
1.839 +*/
1.840 +void ColumnRealTest()
1.841 + {
1.842 + (void)RSqlDatabase::Delete(KTestDbName);
1.843 + TInt err = TheDb.Create(KTestDbName);
1.844 + TEST2(err, KErrNone);
1.845 + err = TheDb.Exec(_L("CREATE TABLE A(I32 INTEGER,I64 INTEGER,D DOUBLE,T TEXT,B BINARY)"));
1.846 + TEST(err >= 0);
1.847 +
1.848 + err = TheDb.Exec(_L("INSERT INTO A VALUES(-5,-5000000000,-10000000000.0,'AAA',x'1122FF')"));
1.849 + TEST2(err, 1);
1.850 + err = TheDb.Exec(_L("INSERT INTO A VALUES(5,5000000000,10000000000.0,NULL,NULL)"));
1.851 + TEST2(err, 1);
1.852 + err = TheDb.Exec(_L("INSERT INTO A VALUES(5,2000000000,1000000000.0,NULL,NULL)"));
1.853 + TEST2(err, 1);
1.854 + err = TheDb.Exec(_L("INSERT INTO A VALUES(NULL,NULL,1.1234567890123456E+317,NULL,NULL)"));
1.855 + TEST2(err, 1);
1.856 +
1.857 + const TReal KEpsilon = 0.000001;
1.858 +
1.859 + RSqlStatement stmt;
1.860 + err = stmt.Prepare(TheDb, _L("SELECT I32,I64,D,T,B FROM A"));
1.861 + TEST2(err, KErrNone);
1.862 +
1.863 + err = stmt.Next();
1.864 + TEST2(err, KSqlAtRow);
1.865 + TReal val = stmt.ColumnReal(0);
1.866 + TEST(Abs(val - (-5)) < KEpsilon);
1.867 + val = stmt.ColumnReal(1);
1.868 + TEST(Abs(val - (-5000000000LL)) < KEpsilon);
1.869 + val = stmt.ColumnReal(2);
1.870 + TEST(Abs(val - (-10000000000LL)) < KEpsilon);
1.871 + val = stmt.ColumnReal(3);
1.872 + TEST(Abs(val) < 0.0001); //"AAA" is a text string, cannot be converted to a 64-bit double
1.873 + val = stmt.ColumnReal(4);
1.874 + TEST(Abs(val) < 0.0001); //"1122FF" is a hex binary, cannot be converted to a 64-bit double
1.875 +
1.876 + err = stmt.Next();
1.877 + TEST2(err, KSqlAtRow);
1.878 + val = stmt.ColumnReal(0);
1.879 + TEST(Abs(val - 5) < KEpsilon);
1.880 + val = stmt.ColumnReal(1);
1.881 + TEST(Abs(val - 5000000000LL) < KEpsilon);
1.882 + val = stmt.ColumnReal(2);
1.883 + TEST(Abs(val - 10000000000LL) < KEpsilon);
1.884 + val = stmt.ColumnReal(3);
1.885 + TEST(Abs(val) < KEpsilon); //NULL column value
1.886 + val = stmt.ColumnReal(4);
1.887 + TEST(Abs(val) < KEpsilon); //NULL column value
1.888 +
1.889 + err = stmt.Next();
1.890 + TEST2(err, KSqlAtRow);
1.891 + val = stmt.ColumnReal(0);
1.892 + TEST(Abs(val - 5) < KEpsilon);
1.893 + val = stmt.ColumnReal(1);
1.894 + TEST(Abs(val - 2000000000) < KEpsilon);
1.895 + val = stmt.ColumnReal(2);
1.896 + TEST(Abs(val - 1000000000) < KEpsilon);
1.897 +
1.898 + err = stmt.Next();
1.899 + TEST2(err, KSqlAtRow);
1.900 + val = stmt.ColumnReal(2);
1.901 + TEST(Math::IsInfinite(val)); //"1.1234567890123456E+317" is too big and cannot fit in a 64-bit double
1.902 +
1.903 + stmt.Close();
1.904 +
1.905 + TheDb.Close();
1.906 + err = RSqlDatabase::Delete(KTestDbName);
1.907 + TEST2(err, KErrNone);
1.908 + }
1.909 +
1.910 +/**
1.911 +@SYMTestCaseID SYSLIB-SQL-UT-3505
1.912 +@SYMTestCaseDesc Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low.
1.913 + The test creates a test table with INT32, INT64, DOUBLE, TEXT, and BINARY columns
1.914 + and inserts a record there. Then the test prepares a SELECT statement,
1.915 + retrieves all table rows, and to retrieve each column value, calls RSqlStatement::ColumnText().
1.916 + Summary: the test checks the ColumnText() behaviour when the column value type is not TEXT.
1.917 + (In all non-TEXT cases the ,ethod is expected to return KNullDesC8)
1.918 +@SYMTestPriority High
1.919 +@SYMTestActions Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low.
1.920 +@SYMTestExpectedResults Test must not fail
1.921 +@SYMDEF DEF109100
1.922 +*/
1.923 +void ColumnTextTest()
1.924 + {
1.925 + (void)RSqlDatabase::Delete(KTestDbName);
1.926 + TInt err = TheDb.Create(KTestDbName);
1.927 + TEST2(err, KErrNone);
1.928 + err = TheDb.Exec(_L("CREATE TABLE A(I32 INTEGER,I64 INTEGER,D DOUBLE,T TEXT,B BINARY)"));
1.929 + TEST(err >= 0);
1.930 +
1.931 + err = TheDb.Exec(_L("INSERT INTO A VALUES(-5,-5000000000,-10000000000.0,'AAA',x'1122FF')"));
1.932 + TEST2(err, 1);
1.933 +
1.934 + RSqlStatement stmt;
1.935 + err = stmt.Prepare(TheDb, _L("SELECT I32,I64,D,T,B FROM A"));
1.936 + TEST2(err, KErrNone);
1.937 +
1.938 + err = stmt.Next();
1.939 + TEST2(err, KSqlAtRow);
1.940 + TPtrC val;
1.941 + err = stmt.ColumnText(0, val);
1.942 + TEST2(err, KErrNone);
1.943 + TEST(val == KNullDesC);
1.944 + err = stmt.ColumnText(1, val);
1.945 + TEST2(err, KErrNone);
1.946 + TEST(val == KNullDesC);
1.947 + err = stmt.ColumnText(2, val);
1.948 + TEST2(err, KErrNone);
1.949 + TEST(val == KNullDesC);
1.950 + err = stmt.ColumnText(3, val);
1.951 + TEST2(err, KErrNone);
1.952 + TEST2(val.Length(), 3);
1.953 + TEST(val == _L("AAA"));
1.954 + err = stmt.ColumnText(4, val);
1.955 + TEST2(err, KErrNone);
1.956 + TEST(val == KNullDesC);
1.957 +
1.958 + stmt.Close();
1.959 +
1.960 + TheDb.Close();
1.961 + err = RSqlDatabase::Delete(KTestDbName);
1.962 + TEST2(err, KErrNone);
1.963 + }
1.964 +
1.965 +/**
1.966 +@SYMTestCaseID SYSLIB-SQL-UT-3506
1.967 +@SYMTestCaseDesc Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low.
1.968 + The test creates a test table with INT32, INT64, DOUBLE, TEXT, and BINARY columns
1.969 + and inserts a record there. Then the test prepares a SELECT statement,
1.970 + retrieves all table rows, and to retrieve each column value, calls RSqlStatement::ColumnBinary().
1.971 + Summary: the test checks the ColumnBinary() behaviour when the column value type is not BINARY.
1.972 + (In all non-BINARY cases the method is expected to return KNullDesC8)
1.973 +@SYMTestPriority High
1.974 +@SYMTestActions Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low.
1.975 +@SYMTestExpectedResults Test must not fail
1.976 +@SYMDEF DEF109100
1.977 +*/
1.978 +void ColumnBinaryTest()
1.979 + {
1.980 + (void)RSqlDatabase::Delete(KTestDbName);
1.981 + TInt err = TheDb.Create(KTestDbName);
1.982 + TEST2(err, KErrNone);
1.983 + err = TheDb.Exec(_L("CREATE TABLE A(I32 INTEGER,I64 INTEGER,D DOUBLE,T TEXT,B BINARY)"));
1.984 + TEST(err >= 0);
1.985 +
1.986 + err = TheDb.Exec(_L("INSERT INTO A VALUES(-5,-5000000000,-10000000000.0,'AAA',x'1122FF')"));
1.987 + TEST2(err, 1);
1.988 +
1.989 + RSqlStatement stmt;
1.990 + err = stmt.Prepare(TheDb, _L("SELECT I32,I64,D,T,B FROM A"));
1.991 + TEST2(err, KErrNone);
1.992 +
1.993 + err = stmt.Next();
1.994 + TEST2(err, KSqlAtRow);
1.995 + TPtrC8 val;
1.996 + err = stmt.ColumnBinary(0, val);
1.997 + TEST2(err, KErrNone);
1.998 + TEST(val == KNullDesC8);
1.999 + err = stmt.ColumnBinary(1, val);
1.1000 + TEST2(err, KErrNone);
1.1001 + TEST(val == KNullDesC8);
1.1002 + err = stmt.ColumnBinary(2, val);
1.1003 + TEST2(err, KErrNone);
1.1004 + TEST(val == KNullDesC8);
1.1005 + err = stmt.ColumnBinary(3, val);
1.1006 + TEST2(err, KErrNone);
1.1007 + TEST(val == KNullDesC8);
1.1008 + err = stmt.ColumnBinary(4, val);
1.1009 + TEST2(err, KErrNone);
1.1010 + TEST2(val.Length(), 3);
1.1011 + TEST(val[0] == 0x11);
1.1012 + TEST(val[1] == 0x22);
1.1013 + TEST(val[2] == 0xFF);
1.1014 +
1.1015 + stmt.Close();
1.1016 +
1.1017 + TheDb.Close();
1.1018 + err = RSqlDatabase::Delete(KTestDbName);
1.1019 + TEST2(err, KErrNone);
1.1020 + }
1.1021 +
1.1022 +void DoTestsL()
1.1023 + {
1.1024 + TheTest.Start(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1611 E011 numeric data types test "));
1.1025 + NumericDataTypesTest();
1.1026 +
1.1027 + TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1630 Built-in functions test "));
1.1028 + BuiltInFunctionsTest();
1.1029 +
1.1030 + TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1632 \"Attach database\" test "));
1.1031 + AttachDatabaseTest();
1.1032 +
1.1033 + TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-3502 \"RSqlStatement::ColumnInt()\" test "));
1.1034 + ColumnIntTest();
1.1035 +
1.1036 + TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-3503 \"RSqlStatement::ColumnInt64()\" test "));
1.1037 + ColumnInt64Test();
1.1038 +
1.1039 + TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-3504 \"RSqlStatement::ColumnReal()\" test "));
1.1040 + ColumnRealTest();
1.1041 +
1.1042 + TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-3505 \"RSqlStatement::ColumnText()\" test "));
1.1043 + ColumnTextTest();
1.1044 +
1.1045 + TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-3506 \"RSqlStatement::ColumnBinary()\" test "));
1.1046 + ColumnBinaryTest();
1.1047 + }
1.1048 +
1.1049 +TInt E32Main()
1.1050 + {
1.1051 + TheTest.Title();
1.1052 +
1.1053 + CTrapCleanup* tc = CTrapCleanup::New();
1.1054 +
1.1055 + __UHEAP_MARK;
1.1056 +
1.1057 + CreateTestDir();
1.1058 + DeleteTestFiles();
1.1059 + TRAPD(err, DoTestsL());
1.1060 + DeleteTestFiles();
1.1061 + TEST2(err, KErrNone);
1.1062 +
1.1063 + __UHEAP_MARKEND;
1.1064 +
1.1065 + TheTest.End();
1.1066 + TheTest.Close();
1.1067 +
1.1068 + delete tc;
1.1069 +
1.1070 + User::Heap().Check();
1.1071 + return KErrNone;
1.1072 + }