// 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 <bautils.h>

#include <sqldb.h>

#include <stdio.h>

#include <stdlib.h>

#include <string.h> 

#include "sqlite3.h"

#include "SqliteSymbian.h"

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

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

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

_LIT(KTestDbName, "c:\\test\\t_fserr.db");

_LIT(KPrivateTestDbName, "c:\\private\\212A2C27\\t_fserr2.db");

_LIT(KSecureTestDbName, "c:[212A2C27]t_fserr3.db");

TFileName TheRmvMediaDbFileName;//The name of the file used for tests on a removable media

RFs TheFs;

RSqlDatabase TheDb;

//The next constants are used in the "blob write" test

const TInt KWriteCnt = 9;

const TInt KBlobSize = 397 * KWriteCnt;

_LIT(KAttachDb, "AttachDb");

//In order to be able to compile the test, the following variables are defined (used inside the OS porting layer, when _SQLPROFILER macro is defined)

#ifdef _SQLPROFILER

TInt TheSqlSrvProfilerFileRead = 0;

TInt TheSqlSrvProfilerFileWrite = 0;

TInt TheSqlSrvProfilerFileSync = 0;

TInt TheSqlSrvProfilerFileSetSize = 0;

#endif

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

TBool FileExists(const TDesC& aFileName)

	{

	TEntry entry;

	return TheFs.Entry(aFileName, entry) == KErrNone;

	}

void DestroyTestEnv()

	{

	TheDb.Close();

    (void)RSqlDatabase::Delete(KSecureTestDbName);

    (void)RSqlDatabase::Delete(KPrivateTestDbName);

	(void)RSqlDatabase::Delete(KTestDbName);

	(void)RSqlDatabase::Delete(TheRmvMediaDbFileName);

	TheFs.Close();

	sqlite3SymbianLibFinalize();

	CloseSTDLIB();

	}

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

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

//Test macros and functions

void Check(TInt aValue, TInt aLine)

	{

	if(!aValue)

		{

		DestroyTestEnv();

		TheTest(EFalse, aLine);

		}

	}

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

	{

	if(aValue != aExpected)

		{

		DestroyTestEnv();

		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 SqliteCheck(sqlite3* aDbHandle, TInt aValue, TInt aExpected, TInt aLine)

	{

	if(aValue != aExpected)

		{

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

		if(aDbHandle)

			{

			const char* errMsg = sqlite3_errmsg(aDbHandle);

			TPtrC8 ptr8((const TUint8*)errMsg, strlen(errMsg));

			TBuf<200> buf;

			buf.Copy(ptr8);

			RDebug::Print(_L("*** SQLITE error message: \"%S\"\r\n"), &buf);

			}

		DestroyTestEnv();

		TheTest(EFalse, aLine);

		}

	}

#define SQLITE_TEST(aDbHandle, aValue, aExpected) ::SqliteCheck(aDbHandle, aValue, aExpected, __LINE__)

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

void SetupTestEnv()

    {

	TInt err = TheFs.Connect();

	TEST2(err, KErrNone);

	err = TheFs.MkDir(KTestDir);

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

	sqlite3SymbianLibInit();

	}

TBool CheckRecord(const TDesC& aDbName, TInt aId, const TDesC& aExpectedName, TBool aOpenDb = ETrue)

	{

	if(aOpenDb)

		{

		TEST2(TheDb.Open(aDbName), KErrNone);

		}

	TBuf<64> sql;

	sql.Copy(_L("SELECT Name FROM A WHERE Id="));

	sql.AppendNum(aId);

	TSqlScalarFullSelectQuery q(TheDb);

	TBuf<20> name;

	TRAPD(err, (void)q.SelectTextL(sql, name));

	TEST2(err, KErrNone);

	if(aOpenDb)

		{

		TheDb.Close();

		}

	return name == aExpectedName;

	}

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

/**

@SYMTestCaseID			SYSLIB-SQL-UT-3419

@SYMTestCaseDesc		Test for DEF103859 "SQLITE panic, _DEBUG mode, persistent file I/O error simulation".

						The test creates a test database with one table, inserts one record.

						Then the test attempts to update the existing record while simulating file I/O failures.

						After each test iteration the database content is tested and is expected to be the same

						as it was before the test. RSqlDatabase::Exec() is used for the update operation.

@SYMTestPriority		High

@SYMTestActions			Test for DEF103859 "SQLITE panic, _DEBUG mode, persistent file I/O error simulation".

@SYMTestExpectedResults The test must not fail

@SYMDEF					DEF103859

*/

void AlterDatabaseTest()

	{

	(void)RSqlDatabase::Delete(KTestDbName);

	TInt err = TheDb.Create(KTestDbName);

	TEST2(err, KErrNone);

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

	TEST(err >= 0);

	TheDb.Close();

	err = KErrNotFound;

	for(TInt cnt=1;err<KErrNone;++cnt)

		{

		TheTest.Printf(_L("%d \r"), cnt);		

		for (TInt fsError=KErrNotFound;fsError>=KErrDied;--fsError)

			{

			//Preprocessing

			TEST2(TheDb.Open(KTestDbName), KErrNone);

			(void)TheDb.Exec(_L("DELETE FROM A WHERE Id=1"));

			err = TheDb.Exec(_L("INSERT INTO A(Id,Name) VALUES(1,'Name')"));

			TEST2(err, 1);

			//The test

			(void)TheFs.SetErrorCondition(fsError, cnt);

			err = TheDb.Exec(_L("UPDATE A SET Name='Name2' WHERE Id=1"));

			(void)TheFs.SetErrorCondition(KErrNone);

			if(err < 1)

				{

				TheDb.Close();//close the database to recover from the last error

				//check the database content - all bets are off in a case of an I/O error. 

				//The existing record might have been updated.

				TEST(CheckRecord(KTestDbName, 1, _L("Name")) || CheckRecord(KTestDbName, 1, _L("Name2")));

				}

			else

				{

				TEST2(err, 1);

				//check the database content has been modified by the operation. 

				TEST(CheckRecord(KTestDbName, 1, _L("Name2"), EFalse));

				TheDb.Close();

				}

			}

		}

	(void)TheFs.SetErrorCondition(KErrNone);

	TEST2(err, 1);

	//check the database content (transaction durability). 

	TEST(CheckRecord(KTestDbName, 1, _L("Name2")));

	err = RSqlDatabase::Delete(KTestDbName);

	TEST2(err, KErrNone);

	TheTest.Printf(_L("\r\n"));

	}

/**

@SYMTestCaseID			SYSLIB-SQL-UT-3420

@SYMTestCaseDesc		Test for DEF103859 "SQLITE panic, _DEBUG mode, persistent file I/O error simulation".

						The test creates a test database with one table, inserts one record.

						Then the test attempts to update the existing record while simulating file I/O failures.

						After each test iteration the database content is tested and is expected to be the same

						as it was before the test. RSqlStatement::Exec() is used for the update operation.

@SYMTestPriority		High

@SYMTestActions			Test for DEF103859 "SQLITE panic, _DEBUG mode, persistent file I/O error simulation".

@SYMTestExpectedResults The test must not fail

@SYMDEF					DEF103859

*/

void AlterDatabaseTest2()

	{

	(void)RSqlDatabase::Delete(KTestDbName);

	TInt err = TheDb.Create(KTestDbName);

	TEST2(err, KErrNone);

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

	TEST(err >= 0);

	TheDb.Close();

	err = KErrNotFound;

	for(TInt cnt=1;err<KErrNone;++cnt)

		{

		TheTest.Printf(_L("%d \r"), cnt);		

		for (TInt fsError=KErrNotFound;fsError>=KErrDied;--fsError)

			{

			//Preprocessing

			TEST2(TheDb.Open(KTestDbName), KErrNone);

			(void)TheDb.Exec(_L("DELETE FROM A WHERE Id=1"));

			err = TheDb.Exec(_L("INSERT INTO A(Id,Name) VALUES(1,'Name')"));

			TEST2(err, 1);

			//The test

			(void)TheFs.SetErrorCondition(fsError, cnt);

			RSqlStatement stmt;

			err = stmt.Prepare(TheDb, _L("UPDATE A SET Name='Name2' WHERE Id=1"));

			if(err == KErrNone)

				{

				err = stmt.Exec();	

				}

			(void)TheFs.SetErrorCondition(KErrNone);

			stmt.Close();

			if(err < 1)

				{

				TheDb.Close();//close the database to recover from the last error

				//check the database content - all bets are off in a case of an I/O error. 

				//The existing record might have been updated.

				TEST(CheckRecord(KTestDbName, 1, _L("Name")) || CheckRecord(KTestDbName, 1, _L("Name2")));

				}

			else

				{

				TEST2(err, 1);

				//check the database content has been modified by the operation. 

				TEST(CheckRecord(KTestDbName, 1, _L("Name2"), EFalse));

				TheDb.Close();

				}

			}

		}

	(void)TheFs.SetErrorCondition(KErrNone);

	TEST2(err, 1);

	//check the database content has been modified by the operation. 

	TEST(CheckRecord(KTestDbName, 1, _L("Name2")));

	err = RSqlDatabase::Delete(KTestDbName);

	TEST2(err, KErrNone);

	TheTest.Printf(_L("\r\n"));

	}

void CreateTestSecurityPolicy(RSqlSecurityPolicy& aSecurityPolicy)

    {

    TSecurityPolicy alwaysPassPolicy(TSecurityPolicy::EAlwaysPass);

    TInt err = aSecurityPolicy.Create(alwaysPassPolicy);

    TEST2(err, KErrNone);

    err = aSecurityPolicy.SetDbPolicy(RSqlSecurityPolicy::ESchemaPolicy, alwaysPassPolicy);

    TEST2(err, KErrNone);

    err = aSecurityPolicy.SetDbPolicy(RSqlSecurityPolicy::EWritePolicy, alwaysPassPolicy);

    TEST2(err, KErrNone);

    err = aSecurityPolicy.SetDbPolicy(RSqlSecurityPolicy::EReadPolicy, alwaysPassPolicy);

    TEST2(err, KErrNone);

    }

/**

@SYMTestCaseID			SYSLIB-SQL-UT-3421

@SYMTestCaseDesc		Test for DEF103859 "SQLITE panic, _DEBUG mode, persistent file I/O error simulation".

						The test creates a test database with one table, inserts one record.

						Then the test attempts to open the database while simulating file I/O failures.

						At the end of the test the database content is tested and is expected to be the same

						as it was before the test. RSqlStatement::Open() is used in the test.

@SYMTestPriority		High

@SYMTestActions			Test for DEF103859 "SQLITE panic, _DEBUG mode, persistent file I/O error simulation".

@SYMTestExpectedResults The test must not fail

@SYMDEF					DEF103859

*/

void OpenDatabaseTest()

	{

    TPtrC dbName[] = {KTestDbName(), KPrivateTestDbName(), KSecureTestDbName()};

    const TInt KDbNameCnt = sizeof(dbName) / sizeof(dbName[0]);

    for(TInt k=0;k<KDbNameCnt;++k)

        {	

        TheTest.Printf(_L("Database: \"%S\"\r\n"), &dbName[k]);       

        (void)RSqlDatabase::Delete(dbName[k]);

        TInt err = KErrGeneral;

        if(k == (KDbNameCnt - 1))

            {

            RSqlSecurityPolicy policy;

            CreateTestSecurityPolicy(policy);

            err = TheDb.Create(dbName[k], policy);

            policy.Close();

            }

        else

            {

            err = TheDb.Create(dbName[k]);

            }

        TEST2(err, KErrNone);

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

        TEST(err >= 0);

        err = TheDb.Exec(_L("INSERT INTO A(Id,Name) VALUES(1,'Name')"));

        TEST2(err, 1);

        TheDb.Close();

        err = KErrNotFound;

        for(TInt cnt=1;err<KErrNone;++cnt)

            {		

            TheTest.Printf(_L("%d \r"), cnt);		

            for (TInt fsError=KErrNotFound;fsError>=KErrDied;--fsError)

                {

                (void)TheFs.SetErrorCondition(fsError, cnt);

                err = TheDb.Open(dbName[k]);

                (void)TheFs.SetErrorCondition(KErrNone);

                if(err != KErrNone)

                    {

                    TheDb.Close();//close the database to recover from the last error

                    //check the database content is still the same as before the "open" call

                    TEST(CheckRecord(dbName[k], 1, _L("Name")));

                    }

                else

                    {

                    TEST2(err, KErrNone);

                    //check the database content is still the same as before the operation, without closing the database

                    TEST(CheckRecord(dbName[k], 1, _L("Name"), EFalse));

                    TheDb.Close();

                    }

                }

            }

        (void)TheFs.SetErrorCondition(KErrNone);

        TEST2(err, KErrNone);

        //check the database content is the same as before the operation, after reopening the database.

        TEST(CheckRecord(dbName[k], 1, _L("Name")));

        err = RSqlDatabase::Delete(dbName[k]);

        TEST2(err, KErrNone);

        TheTest.Printf(_L("\r\n"));

        }

	}

/**

@SYMTestCaseID			SYSLIB-SQL-UT-3434

@SYMTestCaseDesc		Test for DEF104820 "SQL, RSqlDatabase::Create() does not delete the file if fails".

						Test for DEF103859 "SQLITE panic, _DEBUG mode, persistent file I/O error simulation".

						Then the test attempts to create a database while simulating file I/O failures.

						When the test succeeds, the test verifies that the database file does exist.

@SYMTestPriority		High

@SYMTestActions			Test for DEF104820 "SQL, RSqlDatabase::Create() does not delete the file if fails".

						Test for DEF103859 "SQLITE panic, _DEBUG mode, persistent file I/O error simulation".

@SYMTestExpectedResults The test must not fail

@SYMDEF					DEF103859

*/

void CreateDatabaseTest()

	{

	RSqlSecurityPolicy policy;

	CreateTestSecurityPolicy(policy);

	TPtrC dbName[] = {KTestDbName(), KPrivateTestDbName(), KSecureTestDbName()};

	const TInt KDbNameCnt = sizeof(dbName) / sizeof(dbName[0]);

	for(TInt k=0;k<KDbNameCnt;++k)

	    {

        TheTest.Printf(_L("Database: \"%S\"\r\n"), &dbName[k]);       

        TInt err = -1;

        for(TInt cnt=1;err<KErrNone;++cnt)

            {		

            TheTest.Printf(_L("%d \r"), cnt);		

            for (TInt fsError=KErrNotFound;fsError>=KErrDied;--fsError)

                {

                //Ideally, the database should be deleted by the SQL server, if RSqlDatabase::Create() fails.

                //But SetErrorCondition() makes the error persistent, so the SQL server will fail to delete the file.

                //This is the reason, RSqlDatabase::Delete()to be used, before simulating file I/O error.

                (void)RSqlDatabase::Delete(dbName[k]);

                (void)TheFs.SetErrorCondition(fsError, cnt);

                err = (k == (KDbNameCnt - 1)) ? TheDb.Create(dbName[k], policy) : TheDb.Create(dbName[k]);

                (void)TheFs.SetErrorCondition(KErrNone);

                TheDb.Close();

                //If err != KErrNone, the database file should have been already deleted by the server and here is 

                //the place to check that. But since the file I/O failure simulation makes the file I/O error 

                //persistent, the file cannot be deleted by the server, because the "file delete" operation also fails.

                }

            }

        (void)TheFs.SetErrorCondition(KErrNone);

        TheDb.Close();

        TEST2(err, KErrNone);

        if( k != (KDbNameCnt - 1))

            {

            TEST(FileExists(dbName[k]));

            }

        err = RSqlDatabase::Delete(dbName[k]);

        TEST2(err, KErrNone);

        TheTest.Printf(_L("\r\n"));

	    }

	policy.Close();

	}

/**

@SYMTestCaseID			SYSLIB-SQL-UT-3462

@SYMTestCaseDesc		Test for DEF105434 "SQL, persistent file I/O simulation, COMMIT problem".

						The test creates a test database with one table, inserts one record.

						Then the test attempts to retrieve the existing record while simulating file I/O failures.

						After each iteration, the database content is tested, that it has not been modified by the operation.

@SYMTestPriority		High

@SYMTestActions			Test for DEF105434 "SQL, persistent file I/O simulation, COMMIT problem".

@SYMTestExpectedResults The test must not fail

@SYMDEF					DEF105434

*/

void SelectRecordTest()

	{

	(void)RSqlDatabase::Delete(KTestDbName);

	TInt err = TheDb.Create(KTestDbName);

	TEST2(err, KErrNone);

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

	TEST(err >= 0);

	err = TheDb.Exec(_L("INSERT INTO A(Id,Name) VALUES(1,'Name')"));

	TEST2(err, 1);

	TheDb.Close();

	err = -1;

	for(TInt cnt=1;err<KErrNone;++cnt)

		{		

		TheTest.Printf(_L("%d \r"), cnt);		

		err = TheDb.Open(KTestDbName);

		TEST2(err, KErrNone);

		RSqlStatement stmt;

		(void)TheFs.SetErrorCondition(KErrGeneral, cnt);

		err = stmt.Prepare(TheDb, _L("SELECT * FROM A WHERE Id=?"));

		if(err == KErrNone)

			{

			err = stmt.BindInt(0, 1);

			if(err == KErrNone)

				{

				err = stmt.Next();

				TEST(err == KSqlAtRow || err < 0);

				if(err == KSqlAtRow)

					{

					TInt id = stmt.ColumnInt(0);

					TEST2(id, 1);

					TPtrC name;

					err = stmt.ColumnText(1, name);

					TEST2(err, KErrNone);

					TEST(name == _L("Name"));

					}

				}

			}

		(void)TheFs.SetErrorCondition(KErrNone);

		stmt.Close();

		TheDb.Close();

		//check the database content is the same as before the operation

		TEST(CheckRecord(KTestDbName, 1, _L("Name")));

		}

	(void)TheFs.SetErrorCondition(KErrNone);

	TEST(err >= 0);

	TheDb.Close();

	//check the database content is the same as before the operation, after reopening the database.

	TEST(CheckRecord(KTestDbName, 1, _L("Name")));

	err = RSqlDatabase::Delete(KTestDbName);

	TEST2(err, KErrNone);

	TheTest.Printf(_L("\r\n"));

	}

/**

@SYMTestCaseID			SYSLIB-SQL-UT-3463

@SYMTestCaseDesc		Test for DEF105434 "SQL, persistent file I/O simulation, COMMIT problem".

						The test creates a test database with one table, inserts one record.

						Then the test attempts to insert another while simulating file I/O failures.

						After each iteration, the database content is tested, that it has not been modified by the operation.

						If the operation succeeds, the database content is tested again to check that the inserted record is there.

@SYMTestPriority		High

@SYMTestActions			Test for DEF105434 "SQL, persistent file I/O simulation, COMMIT problem".

@SYMTestExpectedResults The test must not fail

@SYMDEF					DEF105434

*/

void InsertRecordTest()

	{

	(void)RSqlDatabase::Delete(KTestDbName);

	TInt err = TheDb.Create(KTestDbName);

	TEST2(err, KErrNone);

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

	TEST(err >= 0);

	err = TheDb.Exec(_L("INSERT INTO A(Id,Name) VALUES(1,'Name')"));

	TEST2(err, 1);

	TheDb.Close();

	err = -1;

	for(TInt cnt=1;err<KErrNone;++cnt)

		{		

		TheTest.Printf(_L("%d \r"), cnt);		

		err = TheDb.Open(KTestDbName);

		TEST2(err, KErrNone);

		RSqlStatement stmt;

		(void)TheFs.SetErrorCondition(KErrGeneral, cnt);

		err = stmt.Prepare(TheDb, _L("INSERT INTO A(Id,Name) VALUES(2, 'Name2')"));

		if(err == KErrNone)

			{

			err = TheDb.Exec(_L("BEGIN TRANSACTION"));

			if(err == KErrNone)

				{

				err = stmt.Exec();

				TEST(err == 1 || err < 0);

				if(err == 1)