sl@0: // Copyright (c) 2005-2009 Nokia Corporation and/or its subsidiary(-ies).
sl@0: // All rights reserved.
sl@0: // This component and the accompanying materials are made available
sl@0: // under the terms of "Eclipse Public License v1.0"
sl@0: // which accompanies this distribution, and is available
sl@0: // at the URL "http://www.eclipse.org/legal/epl-v10.html".
sl@0: //
sl@0: // Initial Contributors:
sl@0: // Nokia Corporation - initial contribution.
sl@0: //
sl@0: // Contributors:
sl@0: //
sl@0: // Description:
sl@0: //
sl@0: 
sl@0: #include <e32test.h>
sl@0: #include <e32math.h>
sl@0: #include <bautils.h>
sl@0: #include <sqldb.h>
sl@0: 
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: 
sl@0: RTest TheTest(_L("t_sqllang test"));
sl@0: _LIT(KTestDir, "c:\\test\\");
sl@0: 
sl@0: _LIT(KTestDbName, "c:\\test\\t_sqllang_1.db");
sl@0: _LIT(KTestDbName2, "c:\\test\\t_sqllang_2.db");
sl@0: 
sl@0: RSqlDatabase TheDb;
sl@0: 
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: 
sl@0: void DeleteTestFiles()
sl@0: 	{
sl@0: 	RSqlDatabase::Delete(KTestDbName2);
sl@0: 	RSqlDatabase::Delete(KTestDbName);
sl@0: 	}
sl@0: 
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: //Test macros and functions
sl@0: void Check(TInt aValue, TInt aLine)
sl@0: 	{
sl@0: 	if(!aValue)
sl@0: 		{
sl@0: 		DeleteTestFiles();
sl@0: 		TheTest(EFalse, aLine);
sl@0: 		}
sl@0: 	}
sl@0: void Check(TInt aValue, TInt aExpected, TInt aLine)
sl@0: 	{
sl@0: 	if(aValue != aExpected)
sl@0: 		{
sl@0: 		DeleteTestFiles();
sl@0: 		RDebug::Print(_L("*** Expected error: %d, got: %d\r\n"), aExpected, aValue);
sl@0: 		TheTest(EFalse, aLine);
sl@0: 		}
sl@0: 	}
sl@0: void Check2(TInt64 aValue, TInt64 aExpected, TInt aLine)
sl@0: 	{
sl@0: 	if(aValue != aExpected)
sl@0: 		{
sl@0: 		DeleteTestFiles();
sl@0: 		RDebug::Print(_L("*** Expected error: %ld, got: %ld\r\n"), aExpected, aValue);
sl@0: 		TheTest(EFalse, aLine);
sl@0: 		}
sl@0: 	}
sl@0: #define TEST(arg) ::Check((arg), __LINE__)
sl@0: #define TEST2(aValue, aExpected) ::Check(aValue, aExpected, __LINE__)
sl@0: #define TEST3(aValue, aExpected) ::Check2(aValue, aExpected, __LINE__)
sl@0: 
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: 
sl@0: void CreateTestDir()
sl@0:     {
sl@0:     RFs fs;
sl@0: 	TInt err = fs.Connect();
sl@0: 	TEST2(err, KErrNone);
sl@0: 
sl@0: 	err = fs.MkDir(KTestDir);
sl@0: 	TEST(err == KErrNone || err == KErrAlreadyExists);
sl@0: 	
sl@0: 	fs.Close();
sl@0: 	}
sl@0: 	
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: 
sl@0: void PrintLastMsgIfError(TInt aErr)
sl@0: 	{
sl@0: 	if(aErr < 0 && SqlRetCodeClass(aErr) == ESqlDbError)
sl@0: 		{
sl@0: 		const TPtrC& msg = TheDb.LastErrorMessage();
sl@0: 		RDebug::Print(_L("Last error msg: \"%S\"\r\n"), &msg);	
sl@0: 		}
sl@0: 	}
sl@0: 
sl@0: /**
sl@0: @SYMTestCaseID			SYSLIB-SQL-CT-1611
sl@0: @SYMTestCaseDesc		Create a table with INTEGER, SMALLINT, REAl, DOUBLE PRECISION, FLOAT, DECIMAL,...
sl@0: 						columns. Insert some records, retrieve the column values, verify the column values,
sl@0: 						try some mathematical operations with the column values, executed in the SQL
sl@0: 						statement.
sl@0: @SYMTestPriority		High
sl@0: @SYMTestActions			Testing SQL engine behaviour with different numerical types. 
sl@0: 						Testing mathematical operations with numeric columns.
sl@0: @SYMTestExpectedResults Test must not fail
sl@0: @SYMREQ					REQ5792
sl@0:                         REQ5793
sl@0: */	
sl@0: void NumericDataTypesTest()
sl@0: 	{
sl@0: 	TEST2(TheDb.Create(KTestDbName), KErrNone);
sl@0: 	
sl@0: 	//Create a table with all possible numeric field types
sl@0: 	_LIT8(KSql1, "CREATE TABLE Tbl(A INTEGER, B SMALLINT, C REAL, D DOUBLE PRECISION, E FLOAT, \
sl@0: 					                    F DECIMAL, G BIGINT, H TINYINT, I BIT, J NUMERIC, K MONEY, \
sl@0: 					                    L SMALLMONEY)");
sl@0: 	TInt err = TheDb.Exec(KSql1);
sl@0: 	PrintLastMsgIfError(err);
sl@0: 	TEST(err >= 0);
sl@0: 	//Insert one record in to the created table
sl@0: 	_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,\
sl@0: 	                                    1.34E-14, 1234.5678, 32000000000, 100, 7, 23.123456, 2123678.56, 11.45)");
sl@0: 	err = TheDb.Exec(KSql2);
sl@0: 	PrintLastMsgIfError(err);
sl@0: 	TEST2(err, 1);
sl@0: 	//Get the inserted record data
sl@0: 	RSqlStatement stmt;
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT * FROM TBL"));
sl@0: 	PrintLastMsgIfError(err);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	err = stmt.Next();
sl@0: 	PrintLastMsgIfError(err);
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	//Check column values
sl@0: 	TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("a"))) == 2000000000);
sl@0: 	TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("b"))) == 30000);
sl@0: 	TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("c"))) - 123.45) < 0.000001);
sl@0: 	//The next column value test is not working! The extracted column value is 0.9120000000000002E+55
sl@0: 	//I guess the reason is that SQLITE uses manifest typing and "DOUBLE PRECISION" is not in its keyword
sl@0: 	//list. Maybe it is interpreted and stored as 4-byte float value.
sl@0: 	//TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("d"))) - 0.912E+55) < 0.000001);
sl@0: 	TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("e"))) - 1.34E-14) < 0.000001);
sl@0: 	TEST(stmt.ColumnReal(stmt.ColumnIndex(_L("f"))) == 1234.5678);
sl@0: 	TEST(stmt.ColumnInt64(stmt.ColumnIndex(_L("g"))) == 32000000000LL);
sl@0: 	TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("h"))) == 100);
sl@0: 	TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("i"))) == 7);
sl@0: 	TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("j"))) - 23.123456) < 0.000001);
sl@0: 	TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("k"))) - 2123678.56) < 0.000001);
sl@0: 	TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("l"))) - 11.45) < 0.000001);
sl@0: 	stmt.Close();
sl@0: 	//The statement object has to be closed before TheDb.Exec() call, 
sl@0: 	//otherwise the reported error is "database table is locked"
sl@0: 	//Insert second record in to the created table but inverse the column types!
sl@0: 	_LIT8(KSql3, "INSERT INTO Tbl(A,   B,  C, D, E, F,  G,  H,  I,  J,K,L) VALUES(\
sl@0: 										-2.5,1.1,12,23,45,111,5.6,7.9,1.1,2,6,7)");
sl@0: 	err = TheDb.Exec(KSql3);
sl@0: 	PrintLastMsgIfError(err);
sl@0: 	TEST2(err, 1);
sl@0: 	//Get the inserted record data
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT * FROM TBL"));
sl@0: 	PrintLastMsgIfError(err);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	TEST2(stmt.Next(), KSqlAtRow);
sl@0: 	err = stmt.Next();
sl@0: 	PrintLastMsgIfError(err);
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	//No column value checking because SQLITE inverted the column types!
sl@0: 	//Check column values
sl@0: 	TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("a"))) - (-2.5)) < 0.000001);
sl@0: 	TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("b"))) - 1.1) < 0.000001);
sl@0: 	TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("c"))) == 12);
sl@0: 	TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("d"))) == 23);
sl@0: 	TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("e"))) == 45);
sl@0: 	TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("f"))) == 111);
sl@0: 	TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("g"))) - 5.6) < 0.000001);
sl@0: 	TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("h"))) - 7.9) < 0.000001);
sl@0: 	TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("i"))) - 1.1) < 0.000001);
sl@0: 	TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("j"))) == 2);
sl@0: 	TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("k"))) == 6);
sl@0: 	TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("l"))) == 7);
sl@0: 	
sl@0: 	stmt.Close();
sl@0: 	//Insert third record in to the created table
sl@0: 	_LIT8(KSql4, "INSERT INTO Tbl(A,B,C,     D,  E,  F,   G,  H,  I,J,     K,     L) VALUES(\
sl@0: 	                                    2,3,123.45,1.5,2.5,1.56,320,100,7,23.123,212.56,11.45)");
sl@0: 	err = TheDb.Exec(KSql4);
sl@0: 	PrintLastMsgIfError(err);
sl@0: 	TEST2(err, 1);
sl@0: 	//Issue a "SELECT" statement doing there some arithmetic operations and comparisons on the column values.
sl@0: 	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"));
sl@0: 	PrintLastMsgIfError(err);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	TEST2(stmt.Next(), KSqlAtRow);
sl@0: 	//Check column values
sl@0: 	TEST2(stmt.ColumnInt(stmt.ColumnIndex(_L("a"))), 2);
sl@0: 	TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("a2"))) - (2-123.45)) < 0.000001);
sl@0: 	TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("a3"))) - (212.56+11.45)) < 0.000001);
sl@0: 	TEST(stmt.ColumnInt(stmt.ColumnIndex(_L("a4"))) == 700);
sl@0: 	TEST(Abs(stmt.ColumnReal(stmt.ColumnIndex(_L("a5"))) - (2.5/1.5)) < 0.000001);
sl@0: 	//There should be no more records
sl@0: 	TEST2(stmt.Next(), KSqlAtEnd);
sl@0: 	stmt.Close();
sl@0: 
sl@0: 	TheDb.Close();
sl@0: 	(void)RSqlDatabase::Delete(KTestDbName);
sl@0: 	}
sl@0: 
sl@0: /**
sl@0: @SYMTestCaseID			SYSLIB-SQL-CT-1630
sl@0: @SYMTestCaseDesc		Built-in functions test.
sl@0: 						abs(), coalesce(), ifnull(), last_insert_rowid(), length(), like(), lower(),
sl@0: 						max(), min(), nullif(), quote(), random(), round(), sqlite_version(), substr(),
sl@0: 						typeof(), upper(), avg(), count(), sun().
sl@0: @SYMTestPriority		High
sl@0: @SYMTestActions			Built-in functions test.
sl@0: @SYMTestExpectedResults Test must not fail
sl@0: @SYMREQ					REQ5792
sl@0:                         REQ5793
sl@0: */	
sl@0: void BuiltInFunctionsTest()
sl@0: 	{
sl@0: 	(void)RSqlDatabase::Delete(KTestDbName);
sl@0: 	TInt err = TheDb.Create(KTestDbName);
sl@0: 	TEST2(err, KErrNone);
sl@0: 
sl@0: 	_LIT(KCreateSql, "CREATE TABLE A(Id Integer, \
sl@0: 									 F1 Integer DEFAULT 0, \
sl@0: 									 F2 Integer Default NULL, \
sl@0: 									 F3 TEXT Default Null, \
sl@0: 									 F4 TEXT Default Null)");
sl@0: 	err = TheDb.Exec(KCreateSql);
sl@0: 	TEST(err >= 0);
sl@0: 
sl@0: 	_LIT(KInsertSql1, "INSERT INTO A(Id, F1) VALUES(1, 100)");
sl@0: 	err = TheDb.Exec(KInsertSql1);
sl@0: 	TEST2(err, 1);
sl@0: 	
sl@0: 	_LIT(KInsertSql2, "INSERT INTO A(Id, F1) VALUES(2, 1)");
sl@0: 	err = TheDb.Exec(KInsertSql2);
sl@0: 	TEST2(err, 1);
sl@0: 
sl@0: 	_LIT(KInsertSql3, "INSERT INTO A(Id, F3) VALUES(3, 'ABCD')");
sl@0: 	err = TheDb.Exec(KInsertSql3);
sl@0: 	TEST2(err, 1);
sl@0: 
sl@0: 	_LIT(KInsertSql4, "INSERT INTO A(Id, F4) VALUES(4, 'DCBA')");
sl@0: 	err = TheDb.Exec(KInsertSql4);
sl@0: 	TEST2(err, 1);
sl@0: 
sl@0: 	RSqlStatement stmt;
sl@0: 	
sl@0: 	//abs() test --------------------------------------------------------
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT * FROM A WHERE F1 > ABS(-10)"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	TEST(stmt.ColumnInt(1) == 100);
sl@0: 	stmt.Close();
sl@0: 
sl@0: 	//coalesce() test --------------------------------------------------------
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT COALESCE(F3, F4) AS F FROM A"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	TEST(stmt.IsNull(0));
sl@0: 	
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	TEST(stmt.IsNull(0));
sl@0: 	
sl@0: 	_LIT(KTextVal1, "ABCD");
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	TPtrC colVal;
sl@0: 	err = stmt.ColumnText(0, colVal);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	TEST(colVal == KTextVal1);
sl@0: 	
sl@0: 	_LIT(KTextVal2, "DCBA");
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	err = stmt.ColumnText(0, colVal);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	TEST(colVal == KTextVal2);
sl@0: 
sl@0: 	stmt.Close();
sl@0: 
sl@0: 	//ifnull() test --------------------------------------------------------
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT IFNULL(F3, F4) AS F FROM A"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	TEST(stmt.IsNull(0));
sl@0: 	
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	TEST(stmt.IsNull(0));
sl@0: 	
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	err = stmt.ColumnText(0, colVal);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	TEST(colVal == KTextVal1);
sl@0: 	
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	err = stmt.ColumnText(0, colVal);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	TEST(colVal == KTextVal2);
sl@0: 
sl@0: 	stmt.Close();
sl@0: 
sl@0: 	//last_insert_rowid() test --------------------------------------------------------
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT last_insert_rowid() AS F"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	TInt64 colVal64 = stmt.ColumnInt64(0);
sl@0: 	RDebug::Print(_L("Last insert row id=%d\r\n"), (TInt)colVal64);
sl@0: 	stmt.Close();
sl@0: 
sl@0: 	//length() test --------------------------------------------------------
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT length(F4) AS L FROM A WHERE ID >= 3"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	TEST(stmt.ColumnInt(0) == 0);
sl@0: 	
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	TEST(stmt.ColumnInt(0) == 4);
sl@0: 	
sl@0: 	stmt.Close();
sl@0: 
sl@0: 	//like() test --------------------------------------------------------
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT Id FROM A WHERE F4 LIKE 'DC%'"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	TEST(stmt.ColumnInt(0) == 4);
sl@0: 	stmt.Close();
sl@0: 
sl@0: 	_LIT(KInsertSql5, "INSERT INTO A(Id, F4) VALUES(5, 'ab%cd')");
sl@0: 	err = TheDb.Exec(KInsertSql5);
sl@0: 	TEST2(err, 1);
sl@0: 	
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT Id FROM A WHERE F4 LIKE 'ab/%cd' ESCAPE '/'"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	TEST(stmt.ColumnInt(0) == 5);
sl@0: 	stmt.Close();
sl@0: 
sl@0: 	err = stmt.Prepare(TheDb, _L8("SELECT Id FROM A WHERE F4 LIKE 'ab/%cd' ESCAPE '/'"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	TEST(stmt.ColumnInt(0) == 5);
sl@0: 	stmt.Close();
sl@0: 
sl@0: 	//lower() test --------------------------------------------------------
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT LOWER(F3) FROM A WHERE F3 = 'ABCD'"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	err = stmt.ColumnText(0, colVal);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	_LIT(KTextVal3, "abcd");
sl@0: 	TEST(colVal == KTextVal3);
sl@0: 	stmt.Close();
sl@0: 
sl@0: 	//max() test --------------------------------------------------------
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT MAX(F1) AS M FROM A"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	TEST(stmt.ColumnInt(0) == 100);
sl@0: 	stmt.Close();
sl@0: 	
sl@0: 	//min() test --------------------------------------------------------
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT MIN(F1) AS M FROM A"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	TEST(stmt.ColumnInt(0) == 0);
sl@0: 	stmt.Close();
sl@0: 
sl@0: 	//nullif() test --------------------------------------------------------
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT NULLIF(3, 4) AS M"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	TEST(stmt.ColumnInt(0) == 3);
sl@0: 	stmt.Close();
sl@0: 	
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT NULLIF(4, 4) AS M"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	TEST(stmt.IsNull(0));
sl@0: 	stmt.Close();
sl@0: 	
sl@0: 	//quote() test --------------------------------------------------------
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT QUOTE(F4) AS M FROM A WHERE Id = 5"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	err = stmt.ColumnText(0, colVal);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	_LIT(KTextVal4, "'ab%cd'");
sl@0: 	TEST(colVal == KTextVal4);
sl@0: 	stmt.Close();
sl@0: 
sl@0: 	//random() test --------------------------------------------------------
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT * FROM A WHERE Id > RANDOM(*)"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	stmt.Close();
sl@0: 
sl@0: 	//round() test --------------------------------------------------------
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT ROUND(5.4) AS D"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	TEST(Abs(stmt.ColumnReal(0) - 5.0) < 0.000001);
sl@0: 	stmt.Close();
sl@0: 
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT ROUND(5.4321, 2) AS D"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	TEST(Abs(stmt.ColumnReal(0) - 5.43) < 0.000001);
sl@0: 	stmt.Close();
sl@0: 
sl@0: 	//sqlite_version() test --------------------------------------------------------
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT sqlite_version(*) AS V"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	err = stmt.ColumnText(0, colVal);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	RDebug::Print(_L("Database engine version: \"%S\"\r\n"), &colVal);
sl@0: 	stmt.Close();
sl@0: 
sl@0: 	//substr() test --------------------------------------------------------
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT SUBSTR('abcd', 2, 2) AS S"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	err = stmt.ColumnText(0, colVal);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	_LIT(KTextVal7, "bc");
sl@0: 	TEST(colVal == KTextVal7);
sl@0: 	stmt.Close();
sl@0: 
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT SUBSTR('abcd', -3, 2) AS S"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	err = stmt.ColumnText(0, colVal);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	TEST(colVal == KTextVal7);
sl@0: 	stmt.Close();
sl@0: 
sl@0: 	//typeof() test --------------------------------------------------------
sl@0: 	_LIT(KInsertSql6, "INSERT INTO A(Id, F2) VALUES(6, 2)");
sl@0: 	err = TheDb.Exec(KInsertSql6);
sl@0: 	TEST2(err, 1);
sl@0: 
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT TYPEOF(F1 + F2) AS T FROM A WHERE ID > 4"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	err = stmt.ColumnText(0, colVal);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	_LIT(KTextVal8, "null");
sl@0: 	TEST(colVal == KTextVal8);
sl@0: 
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	err = stmt.ColumnText(0, colVal);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	_LIT(KTextVal9, "integer");
sl@0: 	TEST(colVal == KTextVal9);
sl@0: 	
sl@0: 	stmt.Close();
sl@0: 
sl@0: 	//upper() test --------------------------------------------------------
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT UPPER('ghjk') AS U"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	err = stmt.ColumnText(0, colVal);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	_LIT(KTextVal10, "GHJK");
sl@0: 	TEST(colVal == KTextVal10);
sl@0: 	stmt.Close();
sl@0: 
sl@0: 	//avg() test --------------------------------------------------------
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT AVG(F2) AS F FROM A"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	TEST(Abs(stmt.ColumnReal(0) - 2) < 0.000001);
sl@0: 	stmt.Close();
sl@0: 
sl@0: 	//count() test --------------------------------------------------------
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT COUNT(F2) AS F FROM A"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	TEST(stmt.ColumnInt(0) == 1);
sl@0: 	stmt.Close();
sl@0: 
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT COUNT(*) AS F FROM A"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	TEST(stmt.ColumnInt(0) == 6);
sl@0: 	stmt.Close();
sl@0: 
sl@0: 	//sum() test --------------------------------------------------------
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT SUM(F2) AS S FROM A"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	TEST(Abs(stmt.ColumnReal(0) - 2) < 0.000001);
sl@0: 	stmt.Close();
sl@0: 
sl@0: 	// ----------------------------------
sl@0: 	TheDb.Close();
sl@0: 	(void)RSqlDatabase::Delete(KTestDbName);
sl@0: 	}
sl@0: 
sl@0: /**
sl@0: @SYMTestCaseID			SYSLIB-SQL-CT-1632
sl@0: @SYMTestCaseDesc		"ATTACH DATABASE" test.
sl@0: 						The test creates two databases. The main database has 
sl@0: 						Account(Id Integer, PersonId Integer, Value Integer) table.
sl@0: 						The attached database has Person(Id Integer, Name TEXT) table.
sl@0: 						The test inserts some records with a valid relation between them in both tables.
sl@0: 						Then the test opens tha main database and attaches the second database to the first one.
sl@0: 						The test prepares and executes SQL statement which retrieves column values from both
sl@0: 						Account and Person tables. The test checks the column values.
sl@0: @SYMTestPriority		High
sl@0: @SYMTestActions			"ATTACH DATABASE" test.
sl@0: @SYMTestExpectedResults Test must not fail
sl@0: @SYMREQ					REQ5792
sl@0:                         REQ5793
sl@0: */	
sl@0: void AttachDatabaseTest()
sl@0: 	{
sl@0: 	// ------------------------------------------------------------
sl@0: 	RSqlDatabase db2;
sl@0: 	TInt err = db2.Create(KTestDbName2);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	
sl@0: 	err = db2.Exec(_L("CREATE TABLE Person(Id Integer, Name TEXT)"));
sl@0: 	TEST(err >= 0);
sl@0: 
sl@0: 	err = db2.Exec(_L("INSERT INTO Person(Id, Name) VALUES(1, 'A')"));
sl@0: 	TEST2(err, 1);
sl@0: 
sl@0: 	err = db2.Exec(_L("INSERT INTO Person(Id, Name) VALUES(2, 'B')"));
sl@0: 	TEST2(err, 1);
sl@0: 
sl@0: 	err = db2.Exec(_L("INSERT INTO Person(Id, Name) VALUES(3, 'C')"));
sl@0: 	TEST2(err, 1);
sl@0: 	
sl@0: 	db2.Close();
sl@0: 
sl@0: 	// ------------------------------------------------------------
sl@0: 	(void)RSqlDatabase::Delete(KTestDbName);
sl@0: 	err = TheDb.Create(KTestDbName);
sl@0: 	TEST2(err, KErrNone);
sl@0: 
sl@0: 	err = TheDb.Exec(_L("CREATE TABLE Account(Id Integer, PersonId Integer, Value Integer)"));
sl@0: 	TEST(err >= 0);
sl@0: 
sl@0: 	err = TheDb.Exec(_L("INSERT INTO ACCOUNT(Id, PersonId, Value) VALUES(1, 2, 20)"));
sl@0: 	TEST2(err, 1);
sl@0: 
sl@0: 	err = TheDb.Exec(_L("INSERT INTO ACCOUNT(Id, PersonId, Value) VALUES(2, 1, 10)"));
sl@0: 	TEST2(err, 1);
sl@0: 
sl@0: 	err = TheDb.Exec(_L("INSERT INTO ACCOUNT(Id, PersonId, Value) VALUES(3, 2, 25)"));
sl@0: 	TEST2(err, 1);
sl@0: 
sl@0: 	err = TheDb.Exec(_L("INSERT INTO ACCOUNT(Id, PersonId, Value) VALUES(4, 3, 30)"));
sl@0: 	TEST2(err, 1);
sl@0: 
sl@0: 	TheDb.Close();
sl@0: 	
sl@0: 	// ------------------------------------------------------------
sl@0: 	err = TheDb.Open(KTestDbName);	
sl@0: 	TEST2(err, KErrNone);
sl@0: 	
sl@0: 	TBuf<100> sql;
sl@0: 	sql.Copy(_L("ATTACH DATABASE '"));
sl@0: 	sql.Append(KTestDbName2);
sl@0: 	sql.Append(_L("' AS DB2"));
sl@0: 	err = TheDb.Exec(sql);
sl@0: 	TEST(err >= 0);
sl@0: 	
sl@0: 	RSqlStatement stmt;
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT Account.Value, DB2.Person.Name FROM Account, DB2.Person \
sl@0: 						   		  WHERE Account.PersonId = DB2.Person.Id"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 
sl@0: 	TPtrC personName;
sl@0: 	_LIT(KName1, "A");
sl@0: 	_LIT(KName2, "B");
sl@0: 	_LIT(KName3, "C");
sl@0: 
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	err = stmt.ColumnText(1, personName);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	TEST(personName == KName2);
sl@0: 	TEST(stmt.ColumnInt(0) == 20);
sl@0: 	
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	err = stmt.ColumnText(1, personName);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	TEST(personName == KName1);
sl@0: 	TEST(stmt.ColumnInt(0) == 10);
sl@0: 		
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	err = stmt.ColumnText(1, personName);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	TEST(personName == KName2);
sl@0: 	TEST(stmt.ColumnInt(0) == 25);
sl@0: 
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	err = stmt.ColumnText(1, personName);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	TEST(personName == KName3);
sl@0: 	TEST(stmt.ColumnInt(0) == 30);
sl@0: 		
sl@0: 	stmt.Close();
sl@0: 	
sl@0: 	err = TheDb.Exec(_L("DETACH DATABASE DB2"));
sl@0: 	TEST(err >= 0);
sl@0: 		
sl@0: 	// ------------------------------------------------------------
sl@0: 	TheDb.Close();
sl@0: 	(void)RSqlDatabase::Delete(KTestDbName2);
sl@0: 	(void)RSqlDatabase::Delete(KTestDbName);
sl@0: 	}
sl@0: 	
sl@0: /**
sl@0: @SYMTestCaseID			SYSLIB-SQL-UT-3502
sl@0: @SYMTestCaseDesc		Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low.
sl@0: 						The test creates a test table with INT32, INT64, DOUBLE, TEXT, and BINARY columns
sl@0: 						and inserts couple of records there. Then the test prepares a SELECT statement,
sl@0: 						retrieves all table rows, and to retrieve each column value, calls RSqlStatement::ColumnInt().
sl@0: 						The column values are carefully chosen, so some of the ColumnInt() calls have to round
sl@0: 						or clamp the returned value. 
sl@0: 						Summary: the test checks the ColumnInt() behaviour when the column value type is not INT32 and
sl@0: 								 when the column value is too big or too small and cannot fit in 32 bits.
sl@0: @SYMTestPriority		High
sl@0: @SYMTestActions			Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low.
sl@0: @SYMTestExpectedResults Test must not fail
sl@0: @SYMDEF					DEF109100
sl@0: */	
sl@0: void ColumnIntTest()
sl@0: 	{
sl@0: 	(void)RSqlDatabase::Delete(KTestDbName);
sl@0: 	TInt err = TheDb.Create(KTestDbName);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	err = TheDb.Exec(_L("CREATE TABLE A(I32 INTEGER,I64 INTEGER,D DOUBLE,T TEXT,B BINARY)"));
sl@0: 	TEST(err >= 0);
sl@0: 	
sl@0: 	err = TheDb.Exec(_L("INSERT INTO A VALUES(-5,-5000000000,-10000000000.0,'AAA',x'1122FF')"));
sl@0: 	TEST2(err, 1);
sl@0: 	err = TheDb.Exec(_L("INSERT INTO A VALUES(5,5000000000,10000000000.0,NULL,NULL)"));
sl@0: 	TEST2(err, 1);
sl@0: 	err = TheDb.Exec(_L("INSERT INTO A VALUES(5,2000000000,1000000000.0,NULL,NULL)"));
sl@0: 	TEST2(err, 1);
sl@0: 	err = TheDb.Exec(_L("INSERT INTO A VALUES(NULL,NULL,1.1234567890123456E+317,NULL,NULL)"));
sl@0: 	TEST2(err, 1);
sl@0: 
sl@0: 	RSqlStatement stmt;
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT I32,I64,D,T,B FROM A"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	TInt val = stmt.ColumnInt(0);	
sl@0: 	TEST2(val, -5);					//"-5" is a 32-bit integer value
sl@0: 	val = stmt.ColumnInt(1);
sl@0: 	TEST2(val, KMinTInt);			//"-5000000000" is a 64-bit integer, will be clamped to KMinTInt
sl@0: 	val = stmt.ColumnInt(2);
sl@0: 	TEST2(val, KMinTInt);			//"-10000000000.0" is a 64-bit double, will be rounded to the nearest 32-bit integer
sl@0: 	val = stmt.ColumnInt(3);
sl@0: 	TEST2(val, 0);					//"AAA" is a text string, cannot be converted to a 32-bit integer
sl@0: 	val = stmt.ColumnInt(4);
sl@0: 	TEST2(val, 0);					//"1122FF" is a hex binary, cannot be converted to a 32-bit integer
sl@0: 
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	val = stmt.ColumnInt(0);	
sl@0: 	TEST2(val, 5);					//"5" is a 32-bit integer value
sl@0: 	val = stmt.ColumnInt(1);
sl@0: 	TEST2(val, KMaxTInt);			//"5000000000" is a 64-bit integer, will be clamped to KMaxTInt
sl@0: 	val = stmt.ColumnInt(2);
sl@0: 	TEST2(val, KMaxTInt);			//"10000000000.0" is a 64-bit double, will be rounded to the nearest 32-bit integer
sl@0: 	val = stmt.ColumnInt(3);
sl@0: 	TEST2(val, 0);					//NULL column value
sl@0: 	val = stmt.ColumnInt(4);
sl@0: 	TEST2(val, 0);					//NULL column value
sl@0: 
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	val = stmt.ColumnInt(0);	
sl@0: 	TEST2(val, 5);					
sl@0: 	val = stmt.ColumnInt(1);
sl@0: 	TEST2(val, 2000000000);					
sl@0: 	val = stmt.ColumnInt(2);
sl@0: 	TEST2(val, 1000000000);					
sl@0: 	
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	val = stmt.ColumnInt(2);
sl@0: 	TEST2(val, KMinTInt);			//"1.1234567890123456E+317" is too big and cannot fit in a 64-bit double
sl@0: 	
sl@0: 	stmt.Close();
sl@0: 	
sl@0: 	TheDb.Close();
sl@0: 	err = RSqlDatabase::Delete(KTestDbName);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	}
sl@0: 
sl@0: /**
sl@0: @SYMTestCaseID			SYSLIB-SQL-UT-3503
sl@0: @SYMTestCaseDesc		Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low.
sl@0: 						The test creates a test table with INT32, INT64, DOUBLE, TEXT, and BINARY columns
sl@0: 						and inserts couple of records there. Then the test prepares a SELECT statement,
sl@0: 						retrieves all table rows, and to retrieve each column value, calls RSqlStatement::ColumnInt64().
sl@0: 						The column values are carefully chosen, so some of the ColumnInt64() calls have to round
sl@0: 						or clamp the returned value. 
sl@0: 						Summary: the test checks the ColumnInt64() behaviour when the column value type is not INT64 and
sl@0: 								 when the column value is too big or too small and cannot be presented as 64-bits integer.
sl@0: @SYMTestPriority		High
sl@0: @SYMTestActions			Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low.
sl@0: @SYMTestExpectedResults Test must not fail
sl@0: @SYMDEF					DEF109100
sl@0: */	
sl@0: void ColumnInt64Test()
sl@0: 	{
sl@0: 	(void)RSqlDatabase::Delete(KTestDbName);
sl@0: 	TInt err = TheDb.Create(KTestDbName);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	err = TheDb.Exec(_L("CREATE TABLE A(I32 INTEGER,I64 INTEGER,D DOUBLE,T TEXT,B BINARY)"));
sl@0: 	TEST(err >= 0);
sl@0: 	
sl@0: 	err = TheDb.Exec(_L("INSERT INTO A VALUES(-5,-5000000000,-10000000000.0,'AAA',x'1122FF')"));
sl@0: 	TEST2(err, 1);
sl@0: 	err = TheDb.Exec(_L("INSERT INTO A VALUES(5,5000000000,10000000000.0,NULL,NULL)"));
sl@0: 	TEST2(err, 1);
sl@0: 	err = TheDb.Exec(_L("INSERT INTO A VALUES(5,2000000000,1000000000.0,NULL,NULL)"));
sl@0: 	TEST2(err, 1);
sl@0: 	err = TheDb.Exec(_L("INSERT INTO A VALUES(NULL,NULL,1.1234567890123456E+317,NULL,NULL)"));
sl@0: 	TEST2(err, 1);
sl@0: 
sl@0: 	RSqlStatement stmt;
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT I32,I64,D,T,B FROM A"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	TInt64 val = stmt.ColumnInt64(0);	
sl@0: 	TEST3(val, -5);					//"-5" is a 32-bit integer value
sl@0: 	val = stmt.ColumnInt64(1);
sl@0: 	TEST3(val, -5000000000LL);		//"-5000000000" is a 64-bit integer
sl@0: 	val = stmt.ColumnInt64(2);
sl@0: 	TEST3(val, -10000000000LL);		//"-10000000000.0" is a 64-bit double, will be rounded to the nearest 64-bit integer
sl@0: 	val = stmt.ColumnInt64(3);
sl@0: 	TEST3(val, 0);					//"AAA" is a text string, cannot be converted to a 64-bit integer
sl@0: 	val = stmt.ColumnInt64(4);
sl@0: 	TEST3(val, 0);					//"1122FF" is a hex binary, cannot be converted to a 64-bit integer
sl@0: 
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	val = stmt.ColumnInt64(0);	
sl@0: 	TEST3(val, 5);					//"5" is a 32-bit integer value
sl@0: 	val = stmt.ColumnInt64(1);
sl@0: 	TEST3(val, 5000000000LL);		//"5000000000" is a 64-bit integer
sl@0: 	val = stmt.ColumnInt64(2);
sl@0: 	TEST3(val, 10000000000LL);		//"10000000000.0" is a 64-bit double, will be rounded to the nearest 64-bit integer
sl@0: 	val = stmt.ColumnInt64(3);
sl@0: 	TEST3(val, 0);					//NULL column value
sl@0: 	val = stmt.ColumnInt64(4);
sl@0: 	TEST3(val, 0);					//NULL column value
sl@0: 
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	val = stmt.ColumnInt64(0);	
sl@0: 	TEST3(val, 5);					
sl@0: 	val = stmt.ColumnInt64(1);
sl@0: 	TEST3(val, 2000000000);					
sl@0: 	val = stmt.ColumnInt64(2);
sl@0: 	TEST3(val, 1000000000);					
sl@0: 	
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	val = stmt.ColumnInt64(2);
sl@0: 	TEST3(val, KMinTInt64);			//"1.1234567890123456E+317" is too big and cannot fit in a 64-bit double
sl@0: 	
sl@0: 	stmt.Close();
sl@0: 	
sl@0: 	TheDb.Close();
sl@0: 	err = RSqlDatabase::Delete(KTestDbName);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	}
sl@0: 
sl@0: /**
sl@0: @SYMTestCaseID			SYSLIB-SQL-UT-3504
sl@0: @SYMTestCaseDesc		Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low.
sl@0: 						The test creates a test table with INT32, INT64, DOUBLE, TEXT, and BINARY columns
sl@0: 						and inserts couple of records there. Then the test prepares a SELECT statement,
sl@0: 						retrieves all table rows, and to retrieve each column value, calls RSqlStatement::ColumnReal().
sl@0: 						Summary: the test checks the ColumnReal() behaviour when the column value type is not DOUBLE and
sl@0: 								 when the column value is too big or too small and cannot be presented as 64-bits double.
sl@0: @SYMTestPriority		High
sl@0: @SYMTestActions			Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low.
sl@0: @SYMTestExpectedResults Test must not fail
sl@0: @SYMDEF					DEF109100
sl@0: */	
sl@0: void ColumnRealTest()
sl@0: 	{
sl@0: 	(void)RSqlDatabase::Delete(KTestDbName);
sl@0: 	TInt err = TheDb.Create(KTestDbName);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	err = TheDb.Exec(_L("CREATE TABLE A(I32 INTEGER,I64 INTEGER,D DOUBLE,T TEXT,B BINARY)"));
sl@0: 	TEST(err >= 0);
sl@0: 	
sl@0: 	err = TheDb.Exec(_L("INSERT INTO A VALUES(-5,-5000000000,-10000000000.0,'AAA',x'1122FF')"));
sl@0: 	TEST2(err, 1);
sl@0: 	err = TheDb.Exec(_L("INSERT INTO A VALUES(5,5000000000,10000000000.0,NULL,NULL)"));
sl@0: 	TEST2(err, 1);
sl@0: 	err = TheDb.Exec(_L("INSERT INTO A VALUES(5,2000000000,1000000000.0,NULL,NULL)"));
sl@0: 	TEST2(err, 1);
sl@0: 	err = TheDb.Exec(_L("INSERT INTO A VALUES(NULL,NULL,1.1234567890123456E+317,NULL,NULL)"));
sl@0: 	TEST2(err, 1);
sl@0: 
sl@0: 	const TReal KEpsilon = 0.000001;
sl@0: 
sl@0: 	RSqlStatement stmt;
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT I32,I64,D,T,B FROM A"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	TReal val = stmt.ColumnReal(0);	
sl@0: 	TEST(Abs(val - (-5)) < KEpsilon);		
sl@0: 	val = stmt.ColumnReal(1);
sl@0: 	TEST(Abs(val - (-5000000000LL)) < KEpsilon);
sl@0: 	val = stmt.ColumnReal(2);
sl@0: 	TEST(Abs(val - (-10000000000LL)) < KEpsilon);
sl@0: 	val = stmt.ColumnReal(3);
sl@0: 	TEST(Abs(val) < 0.0001);					//"AAA" is a text string, cannot be converted to a 64-bit double
sl@0: 	val = stmt.ColumnReal(4);
sl@0: 	TEST(Abs(val) < 0.0001);					//"1122FF" is a hex binary, cannot be converted to a 64-bit double
sl@0: 
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	val = stmt.ColumnReal(0);	
sl@0: 	TEST(Abs(val - 5) < KEpsilon);
sl@0: 	val = stmt.ColumnReal(1);
sl@0: 	TEST(Abs(val - 5000000000LL) < KEpsilon);
sl@0: 	val = stmt.ColumnReal(2);
sl@0: 	TEST(Abs(val - 10000000000LL) < KEpsilon);
sl@0: 	val = stmt.ColumnReal(3);
sl@0: 	TEST(Abs(val) < KEpsilon);					//NULL column value
sl@0: 	val = stmt.ColumnReal(4);
sl@0: 	TEST(Abs(val) < KEpsilon);					//NULL column value
sl@0: 
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	val = stmt.ColumnReal(0);	
sl@0: 	TEST(Abs(val - 5) < KEpsilon);		
sl@0: 	val = stmt.ColumnReal(1);
sl@0: 	TEST(Abs(val - 2000000000) < KEpsilon);
sl@0: 	val = stmt.ColumnReal(2);
sl@0: 	TEST(Abs(val - 1000000000) < KEpsilon);
sl@0: 	
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	val = stmt.ColumnReal(2);
sl@0: 	TEST(Math::IsInfinite(val));				//"1.1234567890123456E+317" is too big and cannot fit in a 64-bit double
sl@0: 	
sl@0: 	stmt.Close();
sl@0: 	
sl@0: 	TheDb.Close();
sl@0: 	err = RSqlDatabase::Delete(KTestDbName);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	}
sl@0: 
sl@0: /**
sl@0: @SYMTestCaseID			SYSLIB-SQL-UT-3505
sl@0: @SYMTestCaseDesc		Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low.
sl@0: 						The test creates a test table with INT32, INT64, DOUBLE, TEXT, and BINARY columns
sl@0: 						and inserts a record there. Then the test prepares a SELECT statement,
sl@0: 						retrieves all table rows, and to retrieve each column value, calls RSqlStatement::ColumnText().
sl@0: 						Summary: the test checks the ColumnText() behaviour when the column value type is not TEXT.
sl@0: 								 (In all non-TEXT cases the ,ethod is expected to return KNullDesC8)
sl@0: @SYMTestPriority		High
sl@0: @SYMTestActions			Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low.
sl@0: @SYMTestExpectedResults Test must not fail
sl@0: @SYMDEF					DEF109100
sl@0: */	
sl@0: void ColumnTextTest()
sl@0: 	{
sl@0: 	(void)RSqlDatabase::Delete(KTestDbName);
sl@0: 	TInt err = TheDb.Create(KTestDbName);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	err = TheDb.Exec(_L("CREATE TABLE A(I32 INTEGER,I64 INTEGER,D DOUBLE,T TEXT,B BINARY)"));
sl@0: 	TEST(err >= 0);
sl@0: 	
sl@0: 	err = TheDb.Exec(_L("INSERT INTO A VALUES(-5,-5000000000,-10000000000.0,'AAA',x'1122FF')"));
sl@0: 	TEST2(err, 1);
sl@0: 
sl@0: 	RSqlStatement stmt;
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT I32,I64,D,T,B FROM A"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	TPtrC val;
sl@0: 	err = stmt.ColumnText(0, val);	
sl@0: 	TEST2(err, KErrNone);		
sl@0: 	TEST(val == KNullDesC);
sl@0: 	err = stmt.ColumnText(1, val);
sl@0: 	TEST2(err, KErrNone);		
sl@0: 	TEST(val == KNullDesC);
sl@0: 	err = stmt.ColumnText(2, val);
sl@0: 	TEST2(err, KErrNone);		
sl@0: 	TEST(val == KNullDesC);
sl@0: 	err = stmt.ColumnText(3, val);
sl@0: 	TEST2(err, KErrNone);		
sl@0: 	TEST2(val.Length(), 3);
sl@0: 	TEST(val == _L("AAA"));
sl@0: 	err = stmt.ColumnText(4, val);
sl@0: 	TEST2(err, KErrNone);		
sl@0: 	TEST(val == KNullDesC);
sl@0: 	
sl@0: 	stmt.Close();
sl@0: 	
sl@0: 	TheDb.Close();
sl@0: 	err = RSqlDatabase::Delete(KTestDbName);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	}
sl@0: 	
sl@0: /**
sl@0: @SYMTestCaseID			SYSLIB-SQL-UT-3506
sl@0: @SYMTestCaseDesc		Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low.
sl@0: 						The test creates a test table with INT32, INT64, DOUBLE, TEXT, and BINARY columns
sl@0: 						and inserts a record there. Then the test prepares a SELECT statement,
sl@0: 						retrieves all table rows, and to retrieve each column value, calls RSqlStatement::ColumnBinary().
sl@0: 						Summary: the test checks the ColumnBinary() behaviour when the column value type is not BINARY.
sl@0: 								 (In all non-BINARY cases the method is expected to return KNullDesC8)
sl@0: @SYMTestPriority		High
sl@0: @SYMTestActions			Test for DEF109100: SQL, code coverage for TSqlBufRIterator is very low.
sl@0: @SYMTestExpectedResults Test must not fail
sl@0: @SYMDEF					DEF109100
sl@0: */	
sl@0: void ColumnBinaryTest()
sl@0: 	{
sl@0: 	(void)RSqlDatabase::Delete(KTestDbName);
sl@0: 	TInt err = TheDb.Create(KTestDbName);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	err = TheDb.Exec(_L("CREATE TABLE A(I32 INTEGER,I64 INTEGER,D DOUBLE,T TEXT,B BINARY)"));
sl@0: 	TEST(err >= 0);
sl@0: 	
sl@0: 	err = TheDb.Exec(_L("INSERT INTO A VALUES(-5,-5000000000,-10000000000.0,'AAA',x'1122FF')"));
sl@0: 	TEST2(err, 1);
sl@0: 
sl@0: 	RSqlStatement stmt;
sl@0: 	err = stmt.Prepare(TheDb, _L("SELECT I32,I64,D,T,B FROM A"));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	
sl@0: 	err = stmt.Next();
sl@0: 	TEST2(err, KSqlAtRow);
sl@0: 	TPtrC8 val;
sl@0: 	err = stmt.ColumnBinary(0, val);	
sl@0: 	TEST2(err, KErrNone);		
sl@0: 	TEST(val == KNullDesC8);
sl@0: 	err = stmt.ColumnBinary(1, val);
sl@0: 	TEST2(err, KErrNone);		
sl@0: 	TEST(val == KNullDesC8);
sl@0: 	err = stmt.ColumnBinary(2, val);
sl@0: 	TEST2(err, KErrNone);		
sl@0: 	TEST(val == KNullDesC8);
sl@0: 	err = stmt.ColumnBinary(3, val);
sl@0: 	TEST2(err, KErrNone);		
sl@0: 	TEST(val == KNullDesC8);
sl@0: 	err = stmt.ColumnBinary(4, val);
sl@0: 	TEST2(err, KErrNone);		
sl@0: 	TEST2(val.Length(), 3);
sl@0: 	TEST(val[0] == 0x11);
sl@0: 	TEST(val[1] == 0x22);
sl@0: 	TEST(val[2] == 0xFF);
sl@0: 	
sl@0: 	stmt.Close();
sl@0: 	
sl@0: 	TheDb.Close();
sl@0: 	err = RSqlDatabase::Delete(KTestDbName);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	}
sl@0: 	
sl@0: void DoTestsL()
sl@0: 	{	
sl@0: 	TheTest.Start(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1611 E011 numeric data types test "));		
sl@0: 	NumericDataTypesTest();
sl@0: 
sl@0: 	TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1630 Built-in functions test "));		
sl@0: 	BuiltInFunctionsTest();
sl@0: 
sl@0: 	TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1632 \"Attach database\" test "));		
sl@0: 	AttachDatabaseTest();
sl@0: 
sl@0: 	TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-3502 \"RSqlStatement::ColumnInt()\" test "));		
sl@0: 	ColumnIntTest();
sl@0: 
sl@0: 	TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-3503 \"RSqlStatement::ColumnInt64()\" test "));		
sl@0: 	ColumnInt64Test();
sl@0: 	
sl@0: 	TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-3504 \"RSqlStatement::ColumnReal()\" test "));		
sl@0: 	ColumnRealTest();
sl@0: 	
sl@0: 	TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-3505 \"RSqlStatement::ColumnText()\" test "));		
sl@0: 	ColumnTextTest();
sl@0: 
sl@0: 	TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-3506 \"RSqlStatement::ColumnBinary()\" test "));		
sl@0: 	ColumnBinaryTest();
sl@0: 	}
sl@0: 	
sl@0: TInt E32Main()
sl@0: 	{
sl@0: 	TheTest.Title();
sl@0: 	
sl@0: 	CTrapCleanup* tc = CTrapCleanup::New();
sl@0: 	
sl@0: 	__UHEAP_MARK;
sl@0: 	
sl@0: 	CreateTestDir();
sl@0: 	DeleteTestFiles();
sl@0: 	TRAPD(err, DoTestsL());
sl@0: 	DeleteTestFiles();
sl@0: 	TEST2(err, KErrNone);
sl@0: 
sl@0: 	__UHEAP_MARKEND;
sl@0: 	
sl@0: 	TheTest.End();
sl@0: 	TheTest.Close();
sl@0: 	
sl@0: 	delete tc;
sl@0: 	
sl@0: 	User::Heap().Check();
sl@0: 	return KErrNone;
sl@0: 	}