// Copyright (c) 2007-2009 Nokia Corporation and/or its subsidiary(-ies).

// All rights reserved.

// This component and the accompanying materials are made available

// under the terms of "Eclipse Public License v1.0"

// which accompanies this distribution, and is available

// at the URL "http://www.eclipse.org/legal/epl-v10.html".

//

// Initial Contributors:

// Nokia Corporation - initial contribution.

//

// Contributors:

//

// Description:

//

#include <e32test.h>

#include <e32math.h>

#include <bautils.h>

#include <s32buf.h>				//MStreamBuf

#include <sqldb.h>

#include "SqlResourceProfiler.h"

///////////////////////////////////////////////////////////////////////////////////////

RTest TheTest(_L("t_sqlapi2 test"));

RSqlDatabase TheDb;

RSqlStatement TheStmt;

_LIT(KTestDir, "c:\\test\\");

_LIT(KTestDbName1, "c:\\test\\t_sqlapi2_1.db");

_LIT(KTestDbName2, "c:\\private\\1111C1EF\\t_sqlapi2_2.db");//t_sqlapi2 app - private database

_LIT(KDbInjectedName1, "DELETE FROM symbian_settings;c:\\test\\A.db");

_LIT(KDbInjectedName2, "c:\\test\\A.db;DELETE FROM symbian_settings;");

const TInt KBufLen = 8192;

TBuf<KBufLen> TheBuf;

///////////////////////////////////////////////////////////////////////////////////////

void DeleteTestFiles()

	{

	TheStmt.Close();

	TheDb.Close();

	(void)RSqlDatabase::Delete(KDbInjectedName2);

	(void)RSqlDatabase::Delete(KDbInjectedName1);

	(void)RSqlDatabase::Delete(KTestDbName2);

	(void)RSqlDatabase::Delete(KTestDbName1);

	}

///////////////////////////////////////////////////////////////////////////////////////

///////////////////////////////////////////////////////////////////////////////////////

//Test macros and functions

void Check(TInt aValue, TInt aLine)

	{

	if(!aValue)

		{

		DeleteTestFiles();

		TheTest(EFalse, aLine);

		}

	}

void Check(TInt aValue, TInt aExpected, TInt aLine)

	{

	if(aValue != aExpected)

		{

		DeleteTestFiles();

		RDebug::Print(_L("*** Expected error: %d, got: %d\r\n"), aExpected, aValue);

		TheTest(EFalse, aLine);

		}

	}

#define TEST(arg) ::Check((arg), __LINE__)

#define TEST2(aValue, aExpected) ::Check(aValue, aExpected, __LINE__)

///////////////////////////////////////////////////////////////////////////////////////

void CreateTestEnv()

    {

    RFs fs;

	TInt err = fs.Connect();

	TEST2(err, KErrNone);

	err = fs.MkDir(KTestDir);

	TEST(err == KErrNone || err == KErrAlreadyExists);

	err = fs.CreatePrivatePath(EDriveC);

	TEST(err == KErrNone || err == KErrAlreadyExists);

	fs.Close();

	}

///////////////////////////////////////////////////////////////////////////////////////

/**

@SYMTestCaseID			SYSLIB-SQL-UT-3512

@SYMTestCaseDesc		RSqlStatement::ColumnCount() - SELECT statements test

						The test creates a database with a table and then checks the ColumnCount()

						return result for the following statements:

						- select all columns;

						- select a subset;

						- select an expression;

						- select a constant;

						- multi-table select;

						- select a function;

						- select plus sub-query;

@SYMTestPriority		High

@SYMTestActions			RSqlStatement::ColumnCount() test

@SYMTestExpectedResults Test must not fail

@SYMREQ					REQ8035

*/

void ColumnCountTest()

	{

	TInt err = TheDb.Create(KTestDbName1);

	TEST2(err, KErrNone);

	//Create table 1

	err = TheDb.Exec(_L("CREATE TABLE A(Id INTEGER,Name TEXT,Id2 INTEGER,Data BLOB)"));

	TEST(err >= 0);

	err = TheDb.Exec(_L("INSERT INTO A VALUES(1,'AAA',6234567890,x'11AAFD0C771188')"));

	TEST2(err, 1);

	//Select all columns (SELECT *)

	err = TheStmt.Prepare(TheDb, _L("SELECT * FROM A"));

	TEST2(err, KErrNone);

	TInt cnt = TheStmt.ColumnCount();

	TEST2(cnt, 4);

	TheStmt.Close();

	//Select all columns (SELECT a,b,c...)

	err = TheStmt.Prepare(TheDb, _L("SELECT Id,Name,Id2,Data FROM A"));

	TEST2(err, KErrNone);

	cnt = TheStmt.ColumnCount();

	TEST2(cnt, 4);

	TheStmt.Close();

	//Select column subset

	err = TheStmt.Prepare(TheDb, _L("SELECT Id,Name,Data FROM A"));

	TEST2(err, KErrNone);

	cnt = TheStmt.ColumnCount();

	TEST2(cnt, 3);

	TheStmt.Close();

	//Select column subset + expression

	err = TheStmt.Prepare(TheDb, _L("SELECT Id,Id+Id2 FROM A"));

	TEST2(err, KErrNone);

	cnt = TheStmt.ColumnCount();

	TEST2(cnt, 2);

	TheStmt.Close();

	//Select column subset + constant

	err = TheStmt.Prepare(TheDb, _L("SELECT Id,Id2,345.78 FROM A"));

	TEST2(err, KErrNone);

	cnt = TheStmt.ColumnCount();

	TEST2(cnt, 3);

	TheStmt.Close();

	//Select SQL function

	err = TheStmt.Prepare(TheDb, _L("SELECT COUNT(*) FROM A"));

	TEST2(err, KErrNone);

	cnt = TheStmt.ColumnCount();

	TEST2(cnt, 1);

	TheStmt.Close();

	//Create table 2

	err = TheDb.Exec(_L("CREATE TABLE B(Id INTEGER, S INTEGER)"));

	TEST(err >= 0);

	err = TheDb.Exec(_L("INSERT INTO B VALUES(1,25)"));

	TEST2(err, 1);

	//Multitable select

	err = TheStmt.Prepare(TheDb, _L("SELECT A.Id,B.S FROM A,B WHERE A.Id = B.Id"));

	TEST2(err, KErrNone);

	cnt = TheStmt.ColumnCount();

	TEST2(cnt, 2);

	TheStmt.Close();

	//Select + Subquery

	err = TheStmt.Prepare(TheDb, _L("SELECT Id FROM A WHERE (SELECT S FROM B WHERE A.Id = B.Id) > 10"));

	TEST2(err, KErrNone);

	cnt = TheStmt.ColumnCount();

	TEST2(cnt, 1);

	TheStmt.Close();

	//Cleanup

	TheDb.Close();

	(void)RSqlDatabase::Delete(KTestDbName1);

	}

/**

@SYMTestCaseID			SYSLIB-SQL-UT-3513

@SYMTestCaseDesc		RSqlStatement::ColumnCount() - DDL and DML statements test

						The test creates a database with a table and then checks the ColumnCount() return result for

						DML statements (INSERT/UPDATE/DELETE) and DDL statements (CREATE TABLE/INDEX, DROP TABLE?INDEX).

						The column count for DML and DDL statements should be 0.

@SYMTestPriority		High

@SYMTestActions			RSqlStatement::ColumnCount() test

@SYMTestExpectedResults Test must not fail

@SYMREQ					REQ8035

*/

void ColumnCountTest2()

	{

	TInt err = TheDb.Create(KTestDbName1);

	TEST2(err, KErrNone);

	//Create table

	err = TheDb.Exec(_L("CREATE TABLE A(Id INTEGER,Name TEXT,Id2 INTEGER,Data BLOB)"));

	TEST(err >= 0);

	err = TheDb.Exec(_L("INSERT INTO A VALUES(1,'AAA',6234567890,x'11AAFD0C771188')"));

	TEST2(err, 1);

	//INSERT statement

	err = TheStmt.Prepare(TheDb, _L("INSERT INTO A(Id,Id2) VALUES(:P1,:P2)"));

	TEST2(err, KErrNone);

	TInt cnt = TheStmt.ColumnCount();

	TEST2(cnt, 0);

	TheStmt.Close();

	//UPDATE statement

	err = TheStmt.Prepare(TheDb, _L("UPDATE A SET Id2=100 WHERE Id=:P1"));

	TEST2(err, KErrNone);

	cnt = TheStmt.ColumnCount();

	TEST2(cnt, 0);

	TheStmt.Close();

	//DELETE statement

	err = TheStmt.Prepare(TheDb, _L("DELETE FROM A WHERE Id=:P1"));

	TEST2(err, KErrNone);

	cnt = TheStmt.ColumnCount();

	TEST2(cnt, 0);

	TheStmt.Close();

	//CREATE TABLE statement

	err = TheStmt.Prepare(TheDb, _L("CREATE TABLE B AS SELECT * FROM A"));

	TEST2(err, KErrNone);

	cnt = TheStmt.ColumnCount();

	TEST2(cnt, 0);

	TheStmt.Close();

	//DROP TABLE statement

	err = TheStmt.Prepare(TheDb, _L("DROP TABLE A"));

	TEST2(err, KErrNone);

	cnt = TheStmt.ColumnCount();

	TEST2(cnt, 0);

	TheStmt.Close();

	//CREATE INDEX statement

	err = TheStmt.Prepare(TheDb, _L("CREATE INDEX I ON A(Id)"));

	TEST2(err, KErrNone);

	cnt = TheStmt.ColumnCount();

	TEST2(cnt, 0);

	err = TheStmt.Exec();

	TEST(err >= 0);

	TheStmt.Close();

	//DROP INDEX statement

	err = TheStmt.Prepare(TheDb, _L("DROP INDEX I"));

	TEST2(err, KErrNone);

	cnt = TheStmt.ColumnCount();

	TEST2(cnt, 0);

	TheStmt.Close();

	//CREATE TRIGGER statement

	err = TheStmt.Prepare(TheDb,

			_L("CREATE TRIGGER Trg BEFORE DELETE ON A \

	             BEGIN \

	                SELECT CASE WHEN ((SELECT Id2 FROM A WHERE A.Id = old.Id) > 0) \

	                            THEN RAISE (ABORT, 'Id2 > 0') \

	                END;\

	             END;"));

	TEST2(err, KErrNone);

	cnt = TheStmt.ColumnCount();

	TEST2(cnt, 0);

	TheStmt.Close();

	//CREATE VIEW statement

	err = TheStmt.Prepare(TheDb, _L("CREATE VIEW V AS SELECT * FROM A"));

	TEST2(err, KErrNone);

	cnt = TheStmt.ColumnCount();

	TEST2(cnt, 0);

	err = TheStmt.Exec();

	TEST(err >= 0);

	TheStmt.Close();

	//DROP VIEW statement

	err = TheStmt.Prepare(TheDb, _L("DROP VIEW V"));

	TEST2(err, KErrNone);

	cnt = TheStmt.ColumnCount();

	TEST2(cnt, 0);

	TheStmt.Close();

	//Cleanup

	TheDb.Close();

	(void)RSqlDatabase::Delete(KTestDbName1);

	}

/**

@SYMTestCaseID			SYSLIB-SQL-UT-3514

@SYMTestCaseDesc		RSqlStatement::DeclaredColumnType() test

						The test creates a database with a table and then checks the DeclaredColumnType() return result for:

						- select all column from the table and check their types;

						- multi-table select plus column type checks;

						- select expression - the expected column type is ESqlInt;

						- select constant - the expected column type is ESqlInt;

@SYMTestPriority		High

@SYMTestActions			RSqlStatement::ColumnCount() test

@SYMTestExpectedResults Test must not fail

@SYMREQ					REQ8035

*/

void DeclaredColumnTypeTest()

	{

	TInt err = TheDb.Create(KTestDbName1);

	TEST2(err, KErrNone);

	const char* KColTypeNames[] =

		{"INTEGER", "LONG INTEGER", "INT", "SHORT INT", "SMALL INT", "TINY INT", "SHORT", "INT64",

		"TEXT", "LONGTEXT", "CLOB", "CHAR", "CHARACTER(20)", "LONG TEXT",

		"BINARY", "LONG BINARY", "LONGBINARY", "BLOB", "LONGBLOB", "LONG BLOB",

		"REAL", "FLOAT", "DOUBLE", "LONG DOUBLE",

		"LONG LONG", "BOO HOO"};

	const TSqlColumnType KColTypes[] =

		{ESqlInt,ESqlInt,ESqlInt,ESqlInt,ESqlInt,ESqlInt,ESqlInt,ESqlInt,

		 ESqlText,ESqlText,ESqlText,ESqlText,ESqlText,ESqlText,

		 ESqlBinary,ESqlBinary,ESqlBinary,ESqlBinary,ESqlBinary,ESqlBinary,

		 ESqlReal,ESqlReal,ESqlReal,ESqlReal,

		 ESqlInt,ESqlInt};

	const TInt KColTypeCnt = sizeof(KColTypes) / sizeof(KColTypes[0]);

	TEST2(sizeof(KColTypeNames) / sizeof(KColTypeNames[0]), KColTypeCnt);

	//Create table 1

	TBuf8<512> sql;

	sql.Copy(_L8("CREATE TABLE T("));

	for(TInt i=0;i<KColTypeCnt;++i)

		{

		sql.Append(TChar('A'));

		sql.AppendNum(i + 1);

		sql.Append(TChar(' '));

		sql.Append((TUint8*)KColTypeNames[i], User::StringLength((TUint8*)KColTypeNames[i]));

		sql.Append(TChar(','));

		}

	sql.Replace(sql.Length() - 1, 1, _L8(")"));

	err = TheDb.Exec(sql);

	TEST(err >= 0);

	//Select all columns (SELECT *)

	err = TheStmt.Prepare(TheDb, _L("SELECT * FROM T"));

	TEST2(err, KErrNone);

	TInt cnt = TheStmt.ColumnCount();

	TEST2(cnt, KColTypeCnt);

	TSqlColumnType colType;

	for(TInt i=0;i<KColTypeCnt;++i)

		{

		TInt err = TheStmt.DeclaredColumnType(i, colType);

		TEST2(err, KErrNone);

		TEST2(colType, KColTypes[i]);

		}

	TheStmt.Close();

	//Create table 2

	err = TheDb.Exec(_L8("CREATE TABLE T2(Id INTEGER, DATA BLOB)"));

	TEST(err >= 0);

	//Multi-table select

	err = TheStmt.Prepare(TheDb, _L("SELECT T.A1,T2.Id,T.A9,T2.Data FROM T,T2"));

	TEST2(err, KErrNone);

	err = TheStmt.DeclaredColumnType(0, colType);

	TEST2(err, KErrNone);

	TEST2(colType, ESqlInt);

	err = TheStmt.DeclaredColumnType(1, colType);

	TEST2(err, KErrNone);

	TEST2(colType, ESqlInt);

	err = TheStmt.DeclaredColumnType(2, colType);

	TEST2(err, KErrNone);

	TEST2(colType, ESqlText);

	err = TheStmt.DeclaredColumnType(3, colType);

	TEST2(err, KErrNone);

	TEST2(colType, ESqlBinary);

	TheStmt.Close();

	//Select expression

	err = TheStmt.Prepare(TheDb, _L("SELECT (Id + Data) AS RES FROM t2"));

	TEST2(err, KErrNone);

	err = TheStmt.DeclaredColumnType(0, colType);

	TEST2(err, KErrNone);

	TEST2(colType, ESqlInt);

	TheStmt.Close();

	//Select constant

	err = TheStmt.Prepare(TheDb, _L("SELECT (Id + Data) AS RES, 55.89 FROM t2"));

	TEST2(err, KErrNone);

	err = TheStmt.DeclaredColumnType(1, colType);

	TEST2(err, KErrNone);

	TEST2(colType, ESqlInt);

	TheStmt.Close();

	//Cleanup

	TheDb.Close();

	(void)RSqlDatabase::Delete(KTestDbName1);

	}

/**

@SYMTestCaseID			SYSLIB-SQL-UT-4017

@SYMTestCaseDesc		RSqlStatement::ColumnName(TInt, TPtrC&) test

						The test creates a database with a table and then checks the ColumnName() return result for:

						- select all column from the table and check their names;

						- multi-table select plus column name checks;

						- select expression - the expected column name is RES

						- select constant - the expected column type is 55.89 

@SYMTestPriority		High

@SYMTestActions			RSqlStatement::ColumnName() test

@SYMTestExpectedResults Test must not fail

@SYMCR				    RMAD-7B7EV5

                        Add SQL Server APIs to retrieve column and parameter names

*/	

void ColumnNameTest()

	{

	TInt err = TheDb.Create(KTestDbName1);

	TEST2(err, KErrNone);

	const char* KColTypeNames[] = 

		{"INTEGER", "LONG INTEGER", "INT", "SHORT INT", "SMALL INT", "TINY INT", "SHORT", "INT64",

		"TEXT", "LONGTEXT", "CLOB", "CHAR", "CHARACTER(20)", "LONG TEXT",

		"BINARY", "LONG BINARY", "LONGBINARY", "BLOB", "LONGBLOB", "LONG BLOB",

		"REAL", "FLOAT", "DOUBLE", "LONG DOUBLE",

		"LONG LONG", "BOO HOO"};

	const TSqlColumnType KColTypes[] = 

		{ESqlInt,ESqlInt,ESqlInt,ESqlInt,ESqlInt,ESqlInt,ESqlInt,ESqlInt,

		 ESqlText,ESqlText,ESqlText,ESqlText,ESqlText,ESqlText,

		 ESqlBinary,ESqlBinary,ESqlBinary,ESqlBinary,ESqlBinary,ESqlBinary,

		 ESqlReal,ESqlReal,ESqlReal,ESqlReal,

		 ESqlInt,ESqlInt};

	const TInt KColTypeCnt = sizeof(KColTypes) / sizeof(KColTypes[0]);

	TEST2(sizeof(KColTypeNames) / sizeof(KColTypeNames[0]), KColTypeCnt);

	//Create table 1

	TBuf8<512> sql;

	sql.Copy(_L8("CREATE TABLE T("));

	for(TInt i=0;i<KColTypeCnt;++i)

		{

		sql.Append(TChar('A'));

		sql.AppendNum(i + 1);

		sql.Append(TChar(' '));

		sql.Append((TUint8*)KColTypeNames[i], User::StringLength((TUint8*)KColTypeNames[i]));

		sql.Append(TChar(','));

		}

	sql.Replace(sql.Length() - 1, 1, _L8(")"));

	err = TheDb.Exec(sql);

	TEST(err >= 0);

	//Select all columns (SELECT *)

	err = TheStmt.Prepare(TheDb, _L("SELECT * FROM T"));

	TEST2(err, KErrNone);

	TInt cnt = TheStmt.ColumnCount();

	TEST2(cnt, KColTypeCnt);

	TPtrC colName;

	TBuf<128> expectedColName;

	for(TInt i=0;i<KColTypeCnt;++i)

		{

		expectedColName.Zero();

		expectedColName.Append(TChar('A'));

		expectedColName.AppendNum(i + 1);

		TInt err = TheStmt.ColumnName(i, colName);

		TEST2(err, KErrNone);

		TEST2(colName.Compare(expectedColName), 0);

		TSqlColumnType type;

		err = TheStmt.DeclaredColumnType(i, type);

		TEST2(err, KErrNone);

		TEST2(type, KColTypes[i]);

		}

	TheStmt.Close();

	//Create table 2

	err = TheDb.Exec(_L8("CREATE TABLE T2(Id INTEGER, DATA BLOB)"));

	TEST(err >= 0);

	//Multi-table select

	err = TheStmt.Prepare(TheDb, _L("SELECT T.A1,T2.Id,T.A9,T2.DATA FROM T,T2"));

	TEST2(err, KErrNone);

	err = TheStmt.ColumnName(0, colName);

	TEST2(err, KErrNone);

	TEST2(colName.Compare(_L("A1")), 0);

	err = TheStmt.ColumnName(1, colName);

	TEST2(err, KErrNone);

	TEST2(colName.Compare(_L("Id")), 0);

	err = TheStmt.ColumnName(2, colName);

	TEST2(err, KErrNone);

	TEST2(colName.Compare(_L("A9")), 0);

	err = TheStmt.ColumnName(3, colName);

	TEST2(err, KErrNone);

	TEST2(colName.Compare(_L("DATA")), 0);

	TheStmt.Close();

	//Select expression

	err = TheStmt.Prepare(TheDb, _L("SELECT (Id + Data) AS RES FROM t2"));

	TEST2(err, KErrNone);

	err = TheStmt.ColumnName(0, colName);

	TEST2(err, KErrNone);

	TEST2(colName.Compare(_L("RES")), 0);

	//Too big column index

    err = TheStmt.ColumnName(1323, colName);

    TEST2(err, KErrNotFound);

    //Negative column index 

    err = TheStmt.ColumnName(-100, colName);

    TEST2(err, KErrNotFound);

	TheStmt.Close();

	//Select constant

	err = TheStmt.Prepare(TheDb, _L("SELECT (Id + Data) AS RES, 55.89 FROM t2"));

	TEST2(err, KErrNone);

	err = TheStmt.ColumnName(1, colName);

	TEST2(err, KErrNone);

	TEST2(colName.Compare(_L("55.89")), 0);

	TheStmt.Close();

	//Cleanup

	TheDb.Close();

	(void)RSqlDatabase::Delete(KTestDbName1);

	}

/**

@SYMTestCaseID			SYSLIB-SQL-UT-4018

@SYMTestCaseDesc		RSqlStatement::ParameterName(TInt, TPtrC&) and RSqlStatement::ParamName(TInt, TPtrC&) test

						DML test:

						The test creates a database with a table and prepares an insert query.

						The test then checks the ParameterName() and ParamName() return result for:

						- Named parameters - return the named param

						- Unnamed parameters - return ?<param-index>

@SYMTestPriority		High

@SYMTestActions			RSqlStatement::ParameterName() and RSqlStatement::ParamName() test

@SYMTestExpectedResults Test must not fail

@SYMCR					RMAD-7B7EV5

                        Add SQL Server APIs to retrieve column and parameter names

*/	

void ParamNameTest()

	{

	TInt err = TheDb.Create(KTestDbName1);

	TEST2(err, KErrNone);

	const char* KColTypeNames[] = 

		{"INTEGER", "TEXT"};

	const TInt KColTypeCnt = sizeof(KColTypeNames) / sizeof(KColTypeNames[0]);

	//Create table 1

	TBuf8<256> sql;

	sql.Copy(_L8("CREATE TABLE T("));

	for(TInt i=0;i<KColTypeCnt;++i)

		{

		sql.Append(TChar('A'));

		sql.AppendNum(i + 1);

		sql.Append(TChar(' '));

		sql.Append((TUint8*)KColTypeNames[i], User::StringLength((TUint8*)KColTypeNames[i]));

		sql.Append(TChar(','));

		}

	sql.Replace(sql.Length() - 1, 1, _L8(")"));

	err = TheDb.Exec(sql);

	TEST(err >= 0);

	TheStmt.Close();

	// Create insert statement, then check param names

	err = TheStmt.Prepare(TheDb, _L("INSERT INTO T (A1, A2) VALUES (:prm1, :prm2)"));

	TEST2(err, KErrNone);

	TPtrC paramName;

	TBuf<128> expectedParamName;

	for(TInt i=0;i<KColTypeCnt;++i)

		{

		expectedParamName.Zero();

		expectedParamName.Append(_L(":prm"));