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

//

// Description:

//

#include <e32test.h>

#include <stdlib.h>

#include <bautils.h>

#include <hal.h>

#include <sqldb.h>

#include "sqlite3.h"

#include "SqliteSymbian.h"

#include "SqlResourceTester.h"

//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

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

/// This test works only if the whole SQL component is built with SYSLIBS_TEST macro defined! ///

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

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

#ifdef SYSLIBS_TEST	

extern TBool IsStatementSupported(const TDesC& aStatementIn, const TDesC& aDbName, TDes& aStatementOut);

RFs TheFs;

RSqlDatabase TheDb;

sqlite3 *TheDbHandle = NULL;

_LIT(KCfgDb1, "c:[1111C1C1]a.db"); // shared, secure db

_LIT(KCfgDb2, "c:[1111C1C1]b.db"); // shared, secure db (no config file for it)

_LIT(KCfgDb3, "c:\\private\\1111C1C1\\c.db"); // private, secure db

_LIT(KCfgDb4, "c:\\test\\d.db"); // public db

_LIT(KCfgDb5, "c:[1111C1C1]e.db"); // shared, secure db (config file created before db is created)

_LIT(KCfgDb1ConfigFilePath, "c:\\private\\10281e17\\cfg[1111C1C1]a.db.0%d"); // config file version %d for a.db

_LIT(KCfgDb3ConfigFileV01Path, "c:\\private\\10281e17\\cfgc.db.01"); // config file v01 for c.db (unsupported)

_LIT(KCfgDb4ConfigFileV01Path, "c:\\private\\10281e17\\cfgd.db.01"); // config file v01 for d.db (unsupported)

_LIT(KCfgDb5ConfigFileV01Path, "c:\\private\\10281e17\\cfg[1111C1C1]e.db.01"); // config file v01 for e.db

_LIT(KCfgDb1CorruptConfigFilePath, "c:\\private\\10281e17\\cfg[1111C1C1]a.db.invalidextension"); // invalid config file name syntax

// config file valid contents (used for v02 and others)

_LIT8(KCfgConfigFileValidStmt, "CREATE INDEX newIdx ON table1(i3)  ;"); 

// config file v03 empty contents

_LIT8(KCfgDb1ConfigFileV03EmptyStmt, ""); 

// config file v04 unsupported contents

_LIT8(KCfgDb1ConfigFileV04UnsupportedStmt, "DELETE FROM table1"); 

// config file v05 only whitespace contents

_LIT8(KCfgDb1ConfigFileV05OnlyWhitespaceStmt, " \r\n   \r\n"); 

// config file v06 invalid schema contents

_LIT8(KCfgDb1ConfigFileV06InvalidSchemaStmt, "CREATE INDEX thisIdx ON table999(i3)"); 

// config file v07 unsupported comment style

_LIT8(KCfgDb1ConfigFileV07InvalidCommentedStmt, "CREATE INDEX ind1 ON table1(i2) // create an index"); 

// config file v08 sequence of different statements

_LIT8(KCfgDb1ConfigFileV08SeqStmt, ";  CREATE INDEX IdxFirst ON table1(i3)\r\n;  DELETE FROM table1;INSERT INTO table1 (i1,i2,i3) values(6,7,8);;CREATE INDEX IdxSecond ON table1(i1);");

// config file v09 whitespace before and after statement

_LIT8(KCfgDb1ConfigFileV09WhitespacePreAndPostStmt, "  CREATE INDEX intIdx ON table1(i1)       "); 

// config file v10 valid contents

_LIT8(KCfgDb1ConfigFileV10ValidStmt, "CREATE INDEX i3Index ON table1(i3)\n"); 

// config file v11 valid contents (also tests that any amount spaces and tabs are allowed between 'CREATE' and 'INDEX')

_LIT8(KCfgDb1ConfigFileV11ValidStmt, "CREATE     INDEX i1Index ON table1(i1);\nCREATE	INDEX i2Index ON table1(i2)");

// config file v12 invalid stmt plus valid stmt

_LIT8(KCfgDb1ConfigFileV12InvalidPlusValidStmt, "CREATE UNIQUE INDEX uniqueIdx ON table1(i1);CREATE INDEX v12Idx ON table1(i2)");

// config file v13 supported SQL comment style

_LIT8(KCfgDb1ConfigFileV13SQLCommentStmt, "CREATE INDEX v13Idx ON table1(i1) -- this is an SQL comment");

// config file v14 supported 'C' comment style

_LIT8(KCfgDb1ConfigFileV14CCommentStmt, "CREATE INDEX v14Idx ON table1(i3) /* this is a C comment */;");

//KLongDbName1 is "long" enough to allow "-journal" to be added at the end.

_LIT(KLongDbName1,  "c:[1111C1C1]a123456789a123456789a123456789a123456789a123456789a123456789a123456789a123456789a123456789a123456789a123456789a123456789a123456789a123456789a123456789a123456789a123456789a123456789a1234.db");

_LIT(KLongCfgName1, "c:\\private\\10281e17\\cfg[1111C1C1]a123456789a123456789a123456789a123456789a123456789a123456789a123456789a123456789a123456789a123456789a123456789a123456789a123456789a123456789a123456789a123456789a123456789a123456789a1234.db.01");

_LIT(KSqlSrvPrivatePath, "\\private\\10281e17\\");

_LIT(KResetCollationDllSql, "UPDATE symbian_settings SET CollationDllName='hjagafsff'");

_LIT(KAttachDb1, "Db1");

_LIT(KAttachDb2, "Db2");

_LIT(KAttachDb5, "Db5");

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

// Destroy functions

void KillSqlServer()

	{

	_LIT(KSqlSrvName, "sqlsrv.exe");

	TFullName name;

	//RDebug::Print(_L("Find and kill \"%S\" process.\n"), &aProcessName);

	TBuf<64> pattern(KSqlSrvName);

	TInt length = pattern.Length();

	pattern += _L("*");

	TFindProcess procFinder(pattern);

	while (procFinder.Next(name) == KErrNone)

		{

		if (name.Length() > length)

			{//If found name is a string containing aProcessName string.

			TChar c(name[length]);

			if (c.IsAlphaDigit() ||

				c == TChar('_') ||

				c == TChar('-'))

				{

				// If the found name is other valid application name

				// starting with aProcessName string.

				//RDebug::Print(_L(":: Process name: \"%S\".\n"), &name);

				continue;

				}

			}

		RProcess proc;

		if (proc.Open(name) == KErrNone)

			{

			proc.Kill(0);

			//RDebug::Print(_L("\"%S\" process killed.\n"), &name);

			}

		proc.Close();

		}

	}

void DeleteCfgFilesAndDbs()

	{

 	(void)RSqlDatabase::Delete(KLongDbName1);

 	(void)RSqlDatabase::Delete(KCfgDb1);

 	(void)RSqlDatabase::Delete(KCfgDb2);	

 	(void)RSqlDatabase::Delete(KCfgDb3);

 	(void)RSqlDatabase::Delete(KCfgDb4);

 	(void)RSqlDatabase::Delete(KCfgDb5);

 	(void)TheFs.Delete(KLongCfgName1);

 	(void)TheFs.Delete(KCfgDb3ConfigFileV01Path);

 	(void)TheFs.Delete(KCfgDb4ConfigFileV01Path);

 	(void)TheFs.Delete(KCfgDb5ConfigFileV01Path);

 	CFileMan* fileMan = 0;	

 	TRAPD(err, fileMan = CFileMan::NewL(TheFs));

 	if(KErrNone == err)

 		{

 		(void)fileMan->Delete(_L("c:\\private\\10281e17\\cfg[1111C1C1]a.db.*"));

		delete fileMan;			

 		}

  	}

void DestroyTestEnv()

	{

	if(TheDbHandle)

		{

		sqlite3_close(TheDbHandle);	

		TheDbHandle = NULL;

		}

	TheDb.Close();

	KillSqlServer();

	DeleteCfgFilesAndDbs();

	TheFs.Close();

	}

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

// 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();

		TheTest.Printf(_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__)

// OOM test functions

static TInt TheHandleCount1;

static TInt TheHandleCount2;

static TInt TheAllocatedCellsCount;

void MarkHandles()

	{

	RThread().HandleCount(TheHandleCount1, TheHandleCount2);

	}

void CheckHandles()

	{

	TInt endHandleCount1;

	TInt endHandleCount2;

	RThread().HandleCount(endHandleCount1, endHandleCount2);

	TEST2(TheHandleCount1, endHandleCount1);

	TEST2(TheHandleCount2, endHandleCount2);

	}

void MarkAllocatedCells()

	{

	TheAllocatedCellsCount = User::CountAllocCells();

	}

void CheckAllocatedCells()

	{

	TInt allocatedCellsCount = User::CountAllocCells();

	TEST2(allocatedCellsCount, TheAllocatedCellsCount);

	}

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

// Set up functions

RSqlSecurityPolicy CreateSecurityPolicy()

	{

	const TSecurityPolicy KDefaultPolicy(TSecurityPolicy::EAlwaysPass);

	RSqlSecurityPolicy policy;

	TInt err = policy.Create(KDefaultPolicy);

	TEST2(err, KErrNone);

	err = policy.SetDbPolicy(RSqlSecurityPolicy::ESchemaPolicy, KDefaultPolicy);

    TEST2(err, KErrNone);

    err = policy.SetDbPolicy(RSqlSecurityPolicy::EWritePolicy, KDefaultPolicy);

    TEST2(err, KErrNone);

    err = policy.SetDbPolicy(RSqlSecurityPolicy::EReadPolicy, KDefaultPolicy);

    TEST2(err, KErrNone);

    return policy;

	}

void DoCreateCfgFile(const TDesC& aFileName, const TDesC8& aData)

    {

    RFile file;

    TInt err = file.Create(TheFs, aFileName, EFileRead | EFileWrite);

    TEST2(err, KErrNone);

    err = file.Write(aData); 

    file.Close();   

    TEST2(err, KErrNone);

    }

// Create v01 of the test config files

void CreateCfgFiles()

	{

	TFileName fileName;

	TInt v1 = 1;

	fileName.Format(KCfgDb1ConfigFilePath, v1);

	// config file v01 contents for db1 (also tests that upper and lower case statements are both supported)

	_LIT8(KCfgDb1ConfigFile01Stmts, "CREATE INDEX idx ON table1(i1);CREATE INDEX idx2 ON table1(i2);INSERT INTO table1 (i1,i2,i3) values(5,8,9);DELETE FROM table1;create index multiidx ON TABLE1(i2,i3)");

    DoCreateCfgFile(fileName, KCfgDb1ConfigFile01Stmts);

    // config file v01 contents for db3 (will not be processed as db3 is not a shared, secure db)

    _LIT8(KCfgDb3ConfigFile01Stmts, "CREATE INDEX idx ON table3(i1)");

    DoCreateCfgFile(KCfgDb3ConfigFileV01Path, KCfgDb3ConfigFile01Stmts);

    // config file v01 contents for db4 (will not be processed as db4 is not a shared, secure db)

    _LIT8(KCfgDb4ConfigFile01Stmts, "CREATE INDEX idx ON table4(i1)");

    DoCreateCfgFile(KCfgDb4ConfigFileV01Path, KCfgDb4ConfigFile01Stmts);

    // create the config file for Db5 before the database has been created

    // config file v01 contents for db5 (will eventually be processed after db5 itself is created)

    _LIT8(KCfgDb5ConfigFile01Stmts, "CREATE INDEX idx ON table5(i1);\r\n");

    DoCreateCfgFile(KCfgDb5ConfigFileV01Path, KCfgDb5ConfigFile01Stmts);

	}

void DoCreateCfgDb(const TDesC& aFileName, const TDesC& aCreateTblSql, const TDesC& aInsertSql, 

                   RSqlSecurityPolicy* aPolicy = NULL)

    {

    TInt err = aPolicy ? TheDb.Create(aFileName, *aPolicy) : TheDb.Create(aFileName);

    TEST2(err, KErrNone);

    err = TheDb.Exec(aCreateTblSql);

    TEST(err >= 0);

    err = TheDb.Exec(aInsertSql);

    TEST(err == 1);

    TheDb.Close();

    }

// Create the test databases

void CreateCfgDbs()

	{

    RSqlSecurityPolicy securityPolicy = CreateSecurityPolicy();

    DoCreateCfgDb(KCfgDb1, _L("CREATE TABLE table1(i1 INTEGER, i2 INTEGER, i3 INTEGER)"),

                           _L("INSERT INTO table1 (i1,i2,i3) values(1,2,3)"), &securityPolicy);

    DoCreateCfgDb(KCfgDb2, _L("CREATE TABLE table2(i1 INTEGER, i2 INTEGER, i3 INTEGER)"),

                           _L("INSERT INTO table2 (i1,i2,i3) values(4,5,6)"), &securityPolicy);

    securityPolicy.Close();

    DoCreateCfgDb(KCfgDb3, _L("CREATE TABLE table3(i1 INTEGER, i2 INTEGER)"),

                           _L("INSERT INTO table3 (i1,i2) values(7,8)"));

    DoCreateCfgDb(KCfgDb4, _L("CREATE TABLE table4(i1 INTEGER, i2 INTEGER, i3 INTEGER)"),

                           _L("INSERT INTO table4 (i1,i2,i3) values(9,10,11)"));

 	}

void CreateCfgFilesAndDbs()

	{

	CreateCfgFiles();

	CreateCfgDbs();

	// Must now kill the SQL Server so that the config files 

	// created above are found and processed when it restarts

	KillSqlServer();

 	}

// Create the Db5 test database (a config file for it already exists)

 void CreateDb5()

	{

 	RSqlSecurityPolicy securityPolicy = CreateSecurityPolicy();

    DoCreateCfgDb(KCfgDb5, _L("CREATE TABLE table5(i1 INTEGER, i2 INTEGER, i3 INTEGER)"),

                           _L("INSERT INTO table5 (i1,i2,i3) values(1,2,3)"), &securityPolicy);

 	securityPolicy.Close();

	}

void SetupTestEnv()

    {

	TInt err = TheFs.Connect();

	TEST2(err, KErrNone);

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

	err = TheFs.MkDir(KTestDir);

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

	err = TheFs.CreatePrivatePath(EDriveC);

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

	// Create the cfg dbs and config files

 	DeleteCfgFilesAndDbs(); // just incase any previous files are lingering

 	CreateCfgFilesAndDbs();

	}

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

//

TInt CalcTimeMs(TUint32 aStartTicks, TUint32 aEndTicks)

	{

	static TInt freq = 0;

	if(freq == 0)

		{

		TEST2(HAL::Get(HAL::EFastCounterFrequency, freq), KErrNone);

		}

	TInt64 diffTicks = (TInt64)aEndTicks - (TInt64)aStartTicks;

	if(diffTicks < 0)

		{

		diffTicks = KMaxTUint32 + diffTicks + 1;

		}

	const TInt KMicroSecIn1Sec = 1000000;

	TInt32 us = (diffTicks * KMicroSecIn1Sec) / freq;

	return us / 1000;

	}

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

// Config file replacement functions

void UpgradeDbConfigFile(TInt aCurrentVersion)

	{

    TInt newVersion = aCurrentVersion + 1;

    TEST(newVersion != 0 && newVersion != 1 && newVersion != 10 && newVersion != 11 && newVersion < 15);

    KillSqlServer();

	TFileName fname;

	fname.Format(KCfgDb1ConfigFilePath, aCurrentVersion);

	TInt err = TheFs.Delete(fname);

	TEST2(err, KErrNone);

	if(newVersion == 12)

	    {

        // also delete version 10 of file

	    fname.Format(KCfgDb1ConfigFilePath, 10);

        err = TheFs.Delete(fname);

        TEST2(err, KErrNone);   

	    }

	const TPtrC8 stmts[] = {KNullDesC8(), KNullDesC8(), 

	        KCfgConfigFileValidStmt(), 

	        KCfgDb1ConfigFileV03EmptyStmt(),

	        KCfgDb1ConfigFileV04UnsupportedStmt(),

	        KCfgDb1ConfigFileV05OnlyWhitespaceStmt(),

	        KCfgDb1ConfigFileV06InvalidSchemaStmt(),

	        KCfgDb1ConfigFileV07InvalidCommentedStmt(),

	        KCfgDb1ConfigFileV08SeqStmt(),

	        KCfgDb1ConfigFileV09WhitespacePreAndPostStmt(),

	        KNullDesC8(), KNullDesC8(),

	        KCfgDb1ConfigFileV12InvalidPlusValidStmt(),

	        KCfgDb1ConfigFileV13SQLCommentStmt(),

	        KCfgDb1ConfigFileV14CCommentStmt()};

    fname.Format(KCfgDb1ConfigFilePath, newVersion);

	DoCreateCfgFile(fname, stmts[newVersion]);

	}	

void DowngradeDbConfigFile(TInt aCurrentVersion)

	{

	TEST(aCurrentVersion > 1);

	KillSqlServer();

	TFileName fname;

	fname.Format(KCfgDb1ConfigFilePath, aCurrentVersion);

	TInt err = TheFs.Delete(fname);

	TEST2(err, KErrNone);

    fname.Format(KCfgDb1ConfigFilePath, aCurrentVersion - 1);

    DoCreateCfgFile(fname, KCfgConfigFileValidStmt);

	}

void CreateCorruptDbConfigFile(TInt aCurrentVersion)

	{

	KillSqlServer();

	TFileName fname;

	fname.Format(KCfgDb1ConfigFilePath, aCurrentVersion);

	TInt err = TheFs.Delete(fname);

	TEST2(err, KErrNone);

    DoCreateCfgFile(KCfgDb1CorruptConfigFilePath, KCfgConfigFileValidStmt);

	}	

void CreateTwoVersionsOfConfigFile()

	{

	KillSqlServer();

	TInt err = TheFs.Delete(KCfgDb1CorruptConfigFilePath);

	TEST2(err, KErrNone);

    TInt nextVersion = 10;

    TFileName fname;

    fname.Format(KCfgDb1ConfigFilePath, nextVersion);

    DoCreateCfgFile(fname, KCfgDb1ConfigFileV10ValidStmt);

	++nextVersion;

    fname.Format(KCfgDb1ConfigFilePath, nextVersion);

    DoCreateCfgFile(fname, KCfgDb1ConfigFileV11ValidStmt);

	}

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

// DoDbCfgTests() functions

TInt GetDbCfgVersion(const TDesC& aDbName)

    {

    // Note: We have to use SQLite directly to access the settings

    // table as the SQL Server denies permission to access this table

    // as it is in a shared, secure database

    TParse parse;

    TInt err = parse.Set(aDbName, &KSqlSrvPrivatePath, 0);

    TEST2(err, KErrNone);

    TBuf8<KMaxFileName + 1> dbFileName;

    dbFileName.Copy(parse.FullName());

    sqlite3 *dbHandle = NULL;

    TInt rc = sqlite3_open((const char*)dbFileName.PtrZ(), &dbHandle);

    TEST2(rc, SQLITE_OK);

    _LIT(KGetDbCfgVersionSql, "SELECT Reserved AS DbCfgVersion FROM symbian_settings");

    TBuf<100> queryBuf;

    queryBuf.Copy(KGetDbCfgVersionSql);

    sqlite3_stmt* stmtHandle = NULL;

    const void* stmtTailZ = NULL;

    rc = sqlite3_prepare16_v2(dbHandle, queryBuf.PtrZ(), -1, &stmtHandle, &stmtTailZ);

    TEST2(rc, SQLITE_OK);

    rc = sqlite3_step(stmtHandle);

    TEST2(rc, SQLITE_ROW);

    TInt version = sqlite3_column_int(stmtHandle, 0);

    rc = sqlite3_step(stmtHandle);

    TEST2(rc, SQLITE_DONE);

    sqlite3_finalize(stmtHandle);

    sqlite3_close(dbHandle);

    return version;

    }

TBool GuessSystemSettingsTable(const TDesC& aDbName, TInt aVersion)

	{

	TInt dbCfgVersion = GetDbCfgVersion(aDbName);

	return dbCfgVersion == aVersion; 

	}

void CheckSystemSettingsTable(const TDesC& aDbName, TInt aVersion)

	{

    TInt dbCfgVersion = GetDbCfgVersion(aDbName);

    TEST2(dbCfgVersion, aVersion); 

	}

void CheckRecordCount(const TDesC& aTable)

	{

	// There should always be only 1 record in the table

	// in each database as INSERT and DELETE statements are

	// not supported in the config files

	TBuf<50> sql;

	sql.Format(_L("SELECT COUNT(*) FROM %S"), &aTable);

	TSqlScalarFullSelectQuery q(TheDb);

	TInt count = 0;

    TRAPD(err, count = q.SelectIntL(sql));