// Copyright (c) 2006-2010 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:

// NTT DOCOMO, INC - Fix for Bug 1915 "SQL server panics when using long column type strings"

//

// Description:

//

#include <e32test.h>

#include <bautils.h>

#include <s32stor.h>

#include <sqldb.h>

#include "SqlResourceProfiler.h"

#include "SqlResourceTester.h"

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

static RFs TheFs;

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

RSqlDatabase TheDb;

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

_LIT(KTestDatabase1, "c:\\test\\t_sqldefect_1.db");

_LIT(KTestDatabase2, "c:\\test\\t_sqldefect_2.db");

_LIT(KCorruptDb, "c:\\test\\t_SqlShortNonDb.db");

_LIT(KCorruptDbZ, "z:\\test\\t_SqlShortNonDb.db");	//Created outside this test app

_LIT(KSecureTestDb1, "c:[21212122]BBDb2.db");		//Created outside this test app

_LIT(KTestDatabase3, "c:\\test\\t_sqldefect_3.db");

_LIT(KTestDatabase4, "z:\\test\\t_inc095412.db");	//Created outside this test app

_LIT(KTestDatabase5, "c:\\test\\t_sqldefect_5.db");

_LIT(KTestDatabase6, "c:\\test\\t_def120237.db");

_LIT(KTestDatabase7, "c:\\test\\t_def144027.db");

_LIT(KTestDatabase7Journal, "c:\\test\\t_def144027.db-journal");

// This value has been found by performing the OOM test

// with an allocation limit of 2000 and then taking a value

// which is just above the allocation failure rate.

const TInt KDEF115954MaxAllocLimit = 1300;

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

//Deletes all created test files.

void DeleteTestFiles()

	{

	TheDb.Close();

	(void)TheFs.Delete(KTestDatabase7Journal);

	(void)RSqlDatabase::Delete(KTestDatabase7);

	(void)RSqlDatabase::Delete(KTestDatabase6);

	(void)RSqlDatabase::Delete(KTestDatabase5);

	(void)RSqlDatabase::Delete(KTestDatabase3);

	(void)RSqlDatabase::Delete(KTestDatabase2);

	(void)RSqlDatabase::Delete(KTestDatabase1);

	(void)RSqlDatabase::Delete(KCorruptDb);

	}

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

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

//Test macros and functions

void Check1(TInt aValue, TInt aLine)

	{

	if(!aValue)

		{

		DeleteTestFiles();

		RDebug::Print(_L("*** Line %d\r\n"), aLine);

		TheTest(EFalse, aLine);

		}

	}

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

	{

	if(aValue != aExpected)

		{

		DeleteTestFiles();

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

		TheTest(EFalse, aLine);

		}

	}

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

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

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

//Creates file session instance and the test directory

void CreateTestEnv()

    {

	TInt err = TheFs.Connect();

	TEST2(err, KErrNone);

	err = TheFs.MkDir(KTestDir);

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

	}

/**

@SYMTestCaseID			SYSLIB-SQL-CT-1763

@SYMTestCaseDesc		The test creates database 1 and attaches database 2.

					    Then the test prepares a SELECT sql statement which is supposed to

					    retrieve records from the attached database 2. Then the test tries to detach

					    database 2. The expectation is that the detaching operation must fail.

					    The database can be detached only when there are no alive sql statements prepared on it.

@SYMTestPriority		High

@SYMTestActions			SQL, "Detach database" test.

@SYMTestExpectedResults Test must not fail

@SYMREQ					REQ5792

                        REQ5793

*/	

void SqlDetachedDbTest()

	{

	TInt err = TheDb.Create(KTestDatabase1);

	TEST2(err, KErrNone);

	err = TheDb.Exec(_L8("CREATE TABLE A(Id INTEGER)"));	

	TEST(err >= 0);

	err = TheDb.Exec(_L8("INSERT INTO A(Id) VALUES(1)"));	

	TEST2(err, 1);

	TheDb.Close();	

	err = TheDb.Create(KTestDatabase2);

	TEST2(err, KErrNone);

	err = TheDb.Exec(_L8("CREATE TABLE B(N INTEGER)"));	

	TEST(err >= 0);

	err = TheDb.Exec(_L8("INSERT INTO B(N) VALUES(10)"));	

	TEST2(err, 1);

	err = TheDb.Exec(_L8("INSERT INTO B(N) VALUES(20)"));	

	TEST2(err, 1);

	TheDb.Close();	

	err = TheDb.Open(KTestDatabase1);

	TEST2(err, KErrNone);

	err = TheDb.Attach(KTestDatabase2, _L("Db2"));

	TEST2(err, KErrNone);

	RSqlStatement stmt;

	err = stmt.Prepare(TheDb, _L8("SELECT * FROM B"));

	TEST2(err, KErrNone);

	err = stmt.Next();

	TEST2(err, KSqlAtRow);

	err = TheDb.Detach(_L("Db2"));

	TEST(err != KErrNone);

	TPtrC errMsg = TheDb.LastErrorMessage();

	RDebug::Print(_L("Detach err: %S\r\n"), &errMsg);

	err = stmt.Next();

	TEST2(err, KSqlAtRow);

	stmt.Close();

	err = TheDb.Detach(_L("Db2"));

	TEST2(err, KErrNone);

	TheDb.Close();

	(void)RSqlDatabase::Delete(KTestDatabase2);

	(void)RSqlDatabase::Delete(KTestDatabase1);

	}

/**

@SYMTestCaseID			SYSLIB-SQL-UT-4034

@SYMTestCaseDesc		Corrupted database test.

						The test tries to open a corrupted database file which length is too short.

						The 'database open' operation is expected to fail with KSqlErrNotDb error.

@SYMTestPriority		High

@SYMTestActions			Corrupted database test.

@SYMTestExpectedResults Test must not fail

@SYMREQ					REQ5792

*/

void CorruptDbFileTest()

	{

	TInt err = TheDb.Open(KCorruptDb);

	TEST2(err, KSqlErrNotDb);

	}

/**

@SYMTestCaseID			SYSLIB-SQL-UT-4035

@SYMTestCaseDesc		Attach database with bad name.

						Attempt to attach a file which name cannot be parsed.

						The 'attach database' operation is expected to fail with KErrBadName error.

@SYMTestPriority		High

@SYMTestActions			Attach database with bad name.

@SYMTestExpectedResults Test must not fail

@SYMREQ					REQ5792

*/

void AttachBadDbFileNameTest()

	{

	TInt err = TheDb.Create(KTestDatabase1);

	TEST2(err, KErrNone);

	err = TheDb.Attach(_L("\"c:\\test\\d12345678.db\""), _L("Db10"));

	TEST2(err, KErrBadName);

	TheDb.Close();

	(void)RSqlDatabase::Delete(KTestDatabase1);

	}

/**

@SYMTestCaseID			SYSLIB-SQL-UT-4036

@SYMTestCaseDesc		Attach secure database. The client cannot pass the security checks.

						Attempt to attach a secure database. The client cannot pass the security checks.

						The 'attach database' operation is expected to fail with KErrPermissionDenied error.

@SYMTestPriority		High

@SYMTestActions			Attach secure database. The client cannot pass the security checks.

@SYMTestExpectedResults Test must not fail

@SYMREQ					REQ5794

*/

void AttachSecureDbTest()

	{

	TInt err = TheDb.Create(KTestDatabase1);

	TEST2(err, KErrNone);

	err = TheDb.Attach(KSecureTestDb1, _L("Db10"));

	TEST2(err, KErrPermissionDenied);

	TheDb.Close();

	(void)RSqlDatabase::Delete(KTestDatabase1);

	}

/**

@SYMTestCaseID			SYSLIB-SQL-UT-4032

@SYMTestCaseDesc		Test for defect INC091579 - SQL Panic 7 when streaming BLOB fields.

						The test creates a database with a table with a BLOB column. Then inserts

						a record. Then the test makes an attempt to read the BLOB column using a stream - 

						RSqlColumnReadStream. If the defect is not fixed, the code will panic.

@SYMTestPriority		High

@SYMTestActions			Test for defect INC091579 - SQL Panic 7 when streaming BLOB fields.

@SYMTestExpectedResults Test must not fail

@SYMDEF					INC091579

*/

void INC091579L()

	{

	//Create test database

	TInt err = TheDb.Create(KTestDatabase3);

	TEST2(err, KErrNone);

	_LIT8(KCreateStmt, "CREATE TABLE A(Fld1 INTEGER, Fld2 BLOB)"); 

	err = TheDb.Exec(KCreateStmt);

	TEST(err >= 0);

	//Insert 1 row, using a binary stream

	_LIT8(KInsertStmt, "INSERT INTO A(Fld1,Fld2) VALUES(:p1,:p2)"); 

	RSqlStatement stmt; 

	err = stmt.Prepare(TheDb, KInsertStmt);

	TEST2(err, KErrNone);

	err = stmt.BindInt(0, 1);

	TEST2(err, KErrNone);

	RSqlParamWriteStream out;

	err = out.BindBinary(stmt, 1);

	TEST2(err, KErrNone);

	const TInt KDataSize = 100;

	TUint16 columnData[KDataSize];

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

		{

		columnData[i] = (TUint16)i;

		}

	out.WriteL(columnData, KDataSize);

	out.CommitL();

	err = stmt.Exec();

	TEST2(err, 1);

	out.Close();

	stmt.Close();

	//Try to read the inserted row, using a binary stream

	_LIT8(KSelectStmt, "SELECT * FROM A WHERE Fld1=1"); 

	err = stmt.Prepare(TheDb, KSelectStmt);

	TEST2(err, KErrNone);

	err = stmt.Next();

	TEST2(err, KSqlAtRow);

	RSqlColumnReadStream in;

	TInt idx = stmt.ColumnIndex(_L("Fld2"));

	TInt type = stmt.ColumnType(idx);

	TInt size = stmt.ColumnSize(idx);

	err = in.ColumnBinary(stmt,idx); // panic occurs here, if the defect is not fixed

	TEST2(err, KErrNone);

	in.Close();

	//Cleanup

	stmt.Close();

	TheDb.Close();

	(void)RSqlDatabase::Delete(KTestDatabase3);

	}

/**

@SYMTestCaseID			SYSLIB-SQL-UT-4033

@SYMTestCaseDesc		Test for defect INC091580 - SQL returns bogus pointer when too much text in field.

						The test creates a database with a table with 2 TEXT columns. Then inserts

						a record using a prepared statement with parameters. 

						The second text column value is set twice calling RSqlStatement::BindText().

						Then the test makes an attempt to read the TEXT columns using a SELECT statement - 

						RSqlStatement. If the defect is not fixed, the RSqlStatement::ColumnText() returns a 

						bad value for the first TEXT column.

@SYMTestPriority		High

@SYMTestActions			Test for defect INC091580 - SQL returns bogus pointer when too much text in field.

@SYMTestExpectedResults Test must not fail

@SYMDEF					INC091580

*/

void INC091580L()

	{

	_LIT8(KCreateStmt, "CREATE TABLE A(Fld1 INTEGER, Fld2 TEXT, Fld3 TEXT)"); 

	TInt err = TheDb.Create(KTestDatabase3); 

	TEST2(err, KErrNone);

	err = TheDb.Exec(KCreateStmt);

	TEST(err >= 0);

	_LIT8(KInsertStmt,"INSERT INTO A(Fld1,Fld2,Fld3) VALUES(1,:p1,:p2)"); 

	RSqlStatement stmt; 

	err = stmt.Prepare(TheDb, KInsertStmt); 

	TEST2(err, KErrNone);

	err = stmt.BindText(0, _L("AAA"));// "AAA" assigned to p1 parameter

	TEST2(err, KErrNone);

	err = stmt.BindText(1,_L("123456789ABCD"));//"123456789ABCD" assigned to p2 parameter (Fld2 column)

	TEST2(err, KErrNone);

	err = stmt.BindText(1, _L("123456789ABCDE"));//"123456789ABCDE" assigned to p2 parameter (Fld3 column)

	TEST2(err, KErrNone);

	err = stmt.Exec();

	TEST(err >= 0);

	stmt.Close();

	_LIT8(KSelectStmt,"SELECT * FROM A WHERE Fld1=1"); 

	err = stmt.Prepare(TheDb, KSelectStmt); 

	TEST2(err, KErrNone);

	err = stmt.Next();

	TEST2(err, KSqlAtRow);

	TPtrC fld2 = stmt.ColumnTextL(1); // returns bad value if the defect is not fixed

	TPtrC fld3 = stmt.ColumnTextL(2);

	TEST(fld2 == _L("AAA"));

	TEST(fld3 == _L("123456789ABCDE"));

	//Cleanup

	stmt.Close();

	TheDb.Close();

	(void)RSqlDatabase::Delete(KTestDatabase3);

	}

/**

@SYMTestCaseID			SYSLIB-SQL-CT-1815

@SYMTestCaseDesc		KSqlErrFull test.

						Create a test database with a table, which first column is declared as

						"INTEGER PRIMARY KEY AUTOINCREMENT".

						Insert one record into the table, initializing the ROWID with 0x7FFFFFFFFFFFFFFF

						(KMaxTInt64). Try to insert one more record into the table. The operation must

						fails with KSqlErrDiskFull.

@SYMTestPriority		High

@SYMTestActions			Verifying that SQL server returns KSqlErrFull when there are no more available row ids.

@SYMTestExpectedResults Test must not fail

@SYMREQ					REQ5792

                        REQ5793

*/

void SqlErrFullTest()

	{

	TInt err = TheDb.Create(KTestDatabase1);

	TEST2(err, KErrNone);

	err = TheDb.Exec(_L8("CREATE TABLE A(Id INTEGER PRIMARY KEY AUTOINCREMENT, T TEXT)"));

	TEST(err >= 0);

	TBuf8<100> sql;

	sql.Copy(_L8("INSERT INTO A(ROWID,T) VALUES("));

	sql.AppendNum(KMaxTInt64);

	sql.Append(_L8(", 'TTT')"));

	err = TheDb.Exec(sql);

	TEST(err >= 0);

	err = TheDb.Exec(_L8("INSERT INTO A(T) VALUES('UUU')"));

	TEST2(err, KSqlErrFull);

	RSqlStatement stmt;

	err = stmt.Prepare(TheDb, _L8("SELECT ROWID FROM A"));

	TEST2(err, KErrNone);

	err = stmt.Next();

	TEST2(err, KSqlAtRow);

	TInt64 val = stmt.ColumnInt64(0);

	TEST(val == KMaxTInt64);

	err = stmt.Next();

	TEST2(err, KSqlAtEnd);

	stmt.Close();

	TheDb.Close();

	(void)RSqlDatabase::Delete(KTestDatabase1);

	}

/**

@SYMTestCaseID			SYSLIB-SQL-CT-1816-0001

@SYMTestCaseDesc		Test for defect INC094870 - Database became corrupted and cannot be opened.

						Case 1: Create a test database and set the database encoding to be UTF-8. Create a table.

						Close and open again the database. "Database open" operation should not panic.

						Case 2: Create a test database and set the database encoding to be UTF-8. Open two 

						connections to the database and check the encoding. It should be UTF-8.

@SYMTestPriority		High

@SYMTestActions			Test for defect INC094870 - Database became corrupted and cannot be opened.

@SYMTestExpectedResults Test must not fail

@SYMDEF					INC094870

*/

void INC094870L()

	{

	(void)RSqlDatabase::Delete(KTestDatabase3);

	//Test 1 - with a single database

	TInt err = TheDb.Create(KTestDatabase3);

	TEST2(err, KErrNone);

	err = TheDb.Exec(_L8("PRAGMA cache_size=1000"));

	TEST(err >= 0);

    err = TheDb.SetIsolationLevel(RSqlDatabase::EReadUncommitted);

	TEST(err >= 0);

    err = TheDb.Exec(_L8("PRAGMA encoding = \"UTF-8\";"));

	TEST(err >= 0);

    err = TheDb.Exec(_L8("PRAGMA case_sensitive_like = 0"));

	TEST(err >= 0);

    err = TheDb.Exec(_L8("CREATE TABLE RDFS_Namespace(NamespaceId        INTEGER NOT NULL,Uri                TEXT NOT NULL,ReadOnly           INTEGER NOT NULL,VID                INTEGER NOT NULL,PRIMARY KEY(NamespaceId),UNIQUE(Uri));"));

	TEST(err >= 0);

	//Close and reopen the database. The Open() call should not fail.

    TheDb.Close();

    err  = TheDb.Open(KTestDatabase3); // This call fails if the defect is not fixed

	TEST2(err, KErrNone);

    TheDb.Close();

	//Test 2 - with two database connections

	(void)RSqlDatabase::Delete(KTestDatabase3);

	_LIT8(KEncoding, "encoding=UTF-8");

	_LIT(KUtf8, "UTF-8");

	RSqlDatabase db1, db2;

	//Connection 1: Create a database with default encoding UTF-8.

	err = db1.Create(KTestDatabase3, &KEncoding);

	TEST2(err, KErrNone);

	TBuf<100> buf;

	//Connection 1: Check the database encoding

	TSqlScalarFullSelectQuery query(db1);

	err = query.SelectTextL(_L8("PRAGMA encoding"), buf);

	TEST2(err, KErrNone);

	TEST(buf.Find(KUtf8) >= 0);

	//Connection 1: Create a table

	err = db1.Exec(_L8("CREATE TABLE Tbl(Id INTEGER, T BLOB)"));

	TEST(err >= 0);

	//Connection 1: Check the database encoding

	err = query.SelectTextL(_L8("PRAGMA encoding"), buf);

	TEST2(err, KErrNone);

	TEST(buf.Find(KUtf8) >= 0);

	//Connection 2: open the same database

	err = db2.Open(KTestDatabase3);

	TEST2(err, KErrNone);

	//Connection 1: Check the database encoding

	err = query.SelectTextL(_L8("PRAGMA encoding"), buf);

	TEST2(err, KErrNone);

	TEST(buf.Find(KUtf8) >= 0);

	//Connection 2: Check the database encoding

	query.SetDatabase(db2);

	err = query.SelectTextL(_L8("PRAGMA encoding"), buf);

	TEST2(err, KErrNone);

	TEST(buf.Find(KUtf8) >= 0);

	//Cleanup

	db2.Close();

	db1.Close();

	(void)RSqlDatabase::Delete(KTestDatabase3);

	}

/**

@SYMTestCaseID			SYSLIB-SQL-CT-1817-0001

@SYMTestCaseDesc		Test for defect INC095412 - Retrieving query results may corrupt heap.

						Open t_inc095412.db test database and prepare SQL query (the SQL statement is in the test code). 

						Execute RSqlStatement::Next() and then try to retrieve the text value of column number 1.

						If the defect is not fixed RSqlStatement::ColumnText() will panic.

@SYMTestPriority		High

@SYMTestActions			Test for defect INC095412 - Retrieving query results may corrupt heap.

@SYMTestExpectedResults Test must not fail

@SYMDEF					INC095412

*/

void INC095412()

	{

    TInt err = TheDb.Open(KTestDatabase4);

    TEST2(err, KErrNone);

    RSqlStatement stmt;

    err = stmt.Prepare(TheDb, _L("SELECT ObjId, Uri, InstanceOf, Flags, p1.PropertyId, p1.PropertyTypeId, p1.Value, p1.Source, p1.Confidence FROM Objects JOIN Properties p1 ON ObjId = p1.HostObjId WHERE  (  ( (NOT Flags%3) = ? AND (NOT Flags%11) = ? AND InstanceOf IN  ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?  )  ) OR  ( (NOT Flags%3) = ? AND (NOT Flags%11) = ? AND InstanceOf IN  ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?  )  ) OR  ( (NOT Flags%3) = ? AND (NOT Flags%11) = ? AND InstanceOf IN  ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?  )  ) OR  ( (NOT Flags%3) = ? AND (NOT Flags%11) = ? AND InstanceOf IN  ( ? , ? , ? , ?  )  )  );"));

    TEST2(err, KErrNone);

    const TInt KPrmValues[] = {0, 0, 33, 40, 47, 45, 43, 42, 50, 39, 51, 44, 41, 46, 38, 48, 49, 0, 0, 26, 40, 53, 54, 57, 52, 39, 58, 56, 41, 38, 55, 0, 0, 26, 40, 53, 54, 57, 52, 39, 58, 56, 41, 38, 55, 0, 0, 38, 40, 39, 41};

    for(TInt i=0;i<sizeof(KPrmValues)/sizeof(KPrmValues[0]);++i)

    	{

    	err = stmt.BindInt(i, KPrmValues[i]);

    	TEST2(err, KErrNone);

    	}

    err = stmt.Next();

    TEST2(err, KSqlAtRow);

    TPtrC ptr;

    stmt.ColumnText(1, ptr); // May corrupt heap and panic the test application if the defect is not fixed!

    stmt.Close();

    TheDb.Close();

	}

/**

@SYMTestCaseID			SYSLIB-SQL-UT-3427

@SYMTestCaseDesc		Test for DEF104242 - The SQL server fails to open database with default security policy only.

						The test plays with C:[21212125]T_OneDefPolicy.db secure database, which was created by

						an external tool and the database security policy table contains just a single record

						with the default security policy. The default security policy is set to be 

						TSecurityPolicy::EAlwaysPass. The test attempts to read from the database, modify the database

						content, modify the database schema.

@SYMTestPriority		High

@SYMTestActions			Test for DEF104242 - The SQL server fails to open database with default security policy only.

@SYMTestExpectedResults Test must not fail

@SYMDEF					DEF104242

*/

void DEF104242()

	{

	TInt err = TheDb.Open(_L("C:[21212125]T_OneDefPolicy.db"));

	TEST2(err, KErrNone);

	//Read

	RSqlStatement stmt;

	err = stmt.Prepare(TheDb, _L("SELECT t1key, data from t1"));

	TEST2(err, KErrNone);

	while((err = stmt.Next()) == KSqlAtRow)

		{

		TheTest.Printf(_L("t1key=%d\r\n"), stmt.ColumnInt(0));

		}

	TEST2(err, KSqlAtEnd);

	stmt.Close();

	//Write

	err = TheDb.Exec(_L("INSERT INTO t1(t1key) VALUES(12)"));

	TEST2(err, 1);

	//Schema

	err = TheDb.Exec(_L("CREATE TABLE AAA(Id INTEGER)"));

	TEST(err >= 0);

	//Cleanup

	TheDb.Close();

	}