// Copyright (c) 2006-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 <hal.h>

#include <stdlib.h>

#include <sqldb.h>

#include "SqlSrvStrings.h"

#include "sqlite3.h"

#include "SqliteSymbian.h"

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

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

RFs   TheFs;

TBuf<200> TheTestTitle;

TBuf<256> TheCmd;

TParse TheParse;

TBuf<8> TheDriveName;

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

_LIT(KDbEncodingUtf8text,  "UTF8  encoded db");

_LIT(KDbEncodingUtf16text, "UTF16 encoded db");

TFileName TheSecureDbName;

TFileName TheNonSecureDbName;

TFileName TheNonSecureDbName2;

TFileName TheNonSecureTmpDbName;

TFileName TheSglRecDbFileName;

enum TDbEncoding

	{

	EDbUtf8,

	EDbUtf16

	};

TDbEncoding TheDbEncoding;

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

_LIT(KCreateDbScript, "z:\\test\\contacts_schema_to_vendors.sql");

_LIT(KFillDbScript, "z:\\test\\add_simple_contacts.sql");

_LIT8(KCommitStr8, "COMMIT;");

_LIT16(KCommitStr16, "COMMIT;");

_LIT8(KUpdateSql8, "UPDATE IDENTITYTABLE SET CM_FIRSTNAME=:V1, CM_LASTNAME=:V2,CM_COMPANYNAME=:V3 WHERE PARENT_CMID=:ID");

_LIT16(KUpdateSql16, "UPDATE IDENTITYTABLE SET CM_FIRSTNAME=:V1, CM_LASTNAME=:V2,CM_COMPANYNAME=:V3 WHERE PARENT_CMID=:ID");

_LIT8(KUpdateSql2_8, "UPDATE IDENTITYTABLE SET CM_FIRSTNAME='%S%d',CM_LASTNAME='%S%d',CM_COMPANYNAME='%S%d' WHERE PARENT_CMID=%d");

_LIT16(KUpdateSql2_16, "UPDATE IDENTITYTABLE SET CM_FIRSTNAME='%S%d',CM_LASTNAME='%S%d',CM_COMPANYNAME='%S%d' WHERE PARENT_CMID=%d");

_LIT8(KSelectSql8, "SELECT CM_FIRSTNAME, CM_LASTNAME, CM_COMPANYNAME FROM IDENTITYTABLE WHERE PARENT_CMID > 50");

_LIT16(KSelectSql16, "SELECT CM_FIRSTNAME, CM_LASTNAME, CM_COMPANYNAME FROM IDENTITYTABLE WHERE PARENT_CMID > 50");

_LIT8(KDeleteSql8, "DELETE FROM IDENTITYTABLE WHERE PARENT_CMID > 50");

_LIT16(KDeleteSql16, "DELETE FROM IDENTITYTABLE WHERE PARENT_CMID > 50");

extern TPtrC GetFirstSqlStmt(TPtr& aString);

_LIT(KFirstName, "FirstName-");

_LIT(KLastName, "LastName-");

_LIT(KCompanyName, "CompanyName-");

_LIT(KFirstName2, "12345678-");

_LIT(KLastName2, "ABCDEFGHIJK-");

_LIT(KCompanyName2, "KKKKKKKKKK10-");

const char* KRawPrmName1 = ":V1";

const char* KRawPrmName2 = ":V2";

const char* KRawPrmName3 = ":V3";

const char* KRawPrmName4 = ":ID";

_LIT(KPrmName1, ":V1");	

_LIT(KPrmName2, ":V2");	

_LIT(KPrmName3, ":V3");	

_LIT(KPrmName4, ":ID");	

const TInt KTestTecordCount = 1000;

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

void TestEnvDestroy()

	{

	(void)RSqlDatabase::Delete(TheNonSecureTmpDbName);

	(void)RSqlDatabase::Delete(TheNonSecureDbName2);

	(void)RSqlDatabase::Delete(TheNonSecureDbName);

	(void)RSqlDatabase::Delete(TheSecureDbName);

	TheFs.Close();

	}

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

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

//Test macros and functions

void Check1(TInt aValue, TInt aLine)

	{

	if(!aValue)

		{

		TestEnvDestroy();

		TheTest.Printf(_L("*** Line %d\r\n"), aLine);

		TheTest(EFalse, aLine);

		}

	}

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

	{

	if(aValue != aExpected)

		{

		TestEnvDestroy();

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

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

void TestEnvInit()

    {

	TInt err = TheFs.Connect();

	TEST2(err, KErrNone);

	err = TheFs.MkDir(TheNonSecureDbName);

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

	}

//Reads a SQL file and returns the file content as HBUFC string.

//The caller is responsible for destroying the returned HBUFC object.

template <class HBUFC> HBUFC* ReadSqlScript(const TDesC& aSqlFileName)

	{

	RFile file;

	TEST2(file.Open(TheFs, aSqlFileName, EFileRead), KErrNone);

	TInt size = 0;

	TEST2(file.Size(size), KErrNone);

	HBufC8* sql = HBufC8::New(size);

	TEST(sql != NULL);

	TPtr8 ptr = sql->Des();

	TEST2(file.Read(ptr, size), KErrNone);

	file.Close();

	HBUFC* sql2 = HBUFC::New(size);

	TEST(sql2 != NULL);

	sql2->Des().Copy(sql->Des());

	delete sql;

	return sql2;

	}

//Explicit ReadSqlScript() template instantiations.

template HBufC8* ReadSqlScript<HBufC8>(const TDesC&);

template HBufC16* ReadSqlScript<HBufC16>(const TDesC&);

//Searches for the next aCommitStr appearance in aSqlScript string and returns a PTRC object holding

//the SQL strings from the beginning of aSqlScript till the aCommitStr (including it).

template <class PTRC, class DESC> PTRC GetNextTrans(PTRC& aSqlScript, const DESC& aCommitStr)

	{

	PTRC res(NULL, 0);

	TInt pos = aSqlScript.FindF(aCommitStr);

	if(pos >= 0)

		{

		pos += aCommitStr.Length();

		res.Set(aSqlScript.Left(pos));

		aSqlScript.Set(aSqlScript.Mid(pos));

		}

	return res;

	}

//Explicit GetNextTrans() template instantiations.

template TPtrC8 GetNextTrans<TPtrC8, TDesC8>(TPtrC8&, const TDesC8&);

template TPtrC16 GetNextTrans<TPtrC16, TDesC16>(TPtrC16&, const TDesC16&);

//Prints aTicks parameter (converted to ms)

void PrintStats(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;

	TheTest.Printf(_L("####Execution time: %d ms\r\n"), us / 1000);

	}

void PrintFileSize(const TDesC& aFileName)

	{

	TParse parse;

	parse.Set(aFileName, &KSqlServerPrivateDir(), 0);

	RFile file;

	TInt err = file.Open(TheFs, parse.FullName(), EFileRead);

	TEST2(err, KErrNone);

	TInt size = 0;

	err = file.Size(size);

	TEST2(err, KErrNone);

	TheTest.Printf(_L("####FileSize: %d\r\n"), size);

	file.Close();

	}

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

///////     SQL SERVER performance tests

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

enum TDbType {ENonSecureDb, ESecureDb};

//Template class offering Create() and Open() methods for creating/opening a RSqlDatabase object

template <TDbType TYPE> class TDbHelper

	{

public:	

	static void Create(const TDesC& aDbName);

	static RSqlDatabase Open(const TDesC& aDbName);

	};

//Creates aDb database schema.

void CreateDbSchema(RSqlDatabase& aDb)

	{

	HBufC8* createDbScript = ReadSqlScript<HBufC8>(KCreateDbScript());

	TInt err = aDb.Exec(createDbScript->Des());

	TEST(err >= 0);	

	delete createDbScript;

	}

//Explicit TDbHelper class specialization for creating/opening a nonsecure RSqlDatabase object

template <> class TDbHelper<ENonSecureDb>

	{

public:	

	static void Create(const TDesC& aDbName)

		{

		RSqlDatabase::Delete(aDbName);

		RSqlDatabase db;

		TInt err = db.Create(aDbName, TheDbEncoding == EDbUtf16 ? NULL : &KDbEncodingUtf8);

		TEST2(err, KErrNone);

		CreateDbSchema(db);

		db.Close();

		}

	static RSqlDatabase Open(const TDesC& aDbName)

		{

		RSqlDatabase db;

		TInt err = db.Open(aDbName);

		TEST2(err, KErrNone);

		return db;

		}

	};

//Explicit TDbHelper class specialization for creating/opening a secure RSqlDatabase object

template <> class TDbHelper<ESecureDb>

	{

public:	

	static void Create(const TDesC& aDbName)

		{

		RSqlDatabase::Delete(aDbName);

		RSqlSecurityPolicy securityPolicy;

		TInt err = securityPolicy.Create(TSecurityPolicy(TSecurityPolicy::EAlwaysPass));

		TEST2(err, KErrNone);	

		securityPolicy.SetDbPolicy(RSqlSecurityPolicy::ESchemaPolicy, TSecurityPolicy(ECapabilityReadUserData, ECapabilityWriteUserData));

		securityPolicy.SetDbPolicy(RSqlSecurityPolicy::EWritePolicy, TSecurityPolicy(ECapabilityWriteUserData));

		securityPolicy.SetDbPolicy(RSqlSecurityPolicy::EReadPolicy, TSecurityPolicy(ECapabilityReadUserData));

		RSqlDatabase db;

		err = db.Create(aDbName, securityPolicy, TheDbEncoding == EDbUtf16 ? NULL : &KDbEncodingUtf8);

		TEST2(err, KErrNone);	

		securityPolicy.Close();

		CreateDbSchema(db);

		db.Close();

		}

	static RSqlDatabase Open(const TDesC& aDbName)

		{

		RSqlDatabase db;

		TInt err = db.Open(aDbName);

		TEST2(err, KErrNone);

		return db;

		}

	};

//Executes SQL script

template <class HBUFC, class PTRC, class DESC> void ExecuteSqlScript(RSqlDatabase& aDb, const TDesC& aScriptFileName, const DESC& aCommitStr)

	{

	HBUFC* fillDbScript = ReadSqlScript<HBUFC>(aScriptFileName);

	TUint32 start = User::FastCounter();

	PTRC ptr(fillDbScript->Des());

	PTRC sql(GetNextTrans<PTRC, DESC>(ptr, aCommitStr));

	while(sql.Length() > 0)

		{

		TInt err = aDb.Exec(sql);

		if(err == KErrNoMemory)

			{

			TheTest.Printf(_L("###ERROR 'Out of memory'! The test cannot be completed!\r\n"));

			return;	

			}

		TEST(err > 0);	

		sql.Set(GetNextTrans<PTRC, DESC>(ptr, aCommitStr));

		}

	TUint32 end = User::FastCounter();

	PrintStats(start, end);

	delete fillDbScript;

	}

//Explicit ExecuteSqlScript() template instantiations.

template void ExecuteSqlScript<HBufC8, TPtrC8, TDesC8>(RSqlDatabase&, const TDesC&, const TDesC8&);

template void ExecuteSqlScript<HBufC16, TPtrC16, TDesC16>(RSqlDatabase&, const TDesC&, const TDesC16&);

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

//"INSERT" test function

template <class HBUFC, class PTRC, class DESC, TDbType TYPE> void InsertTest(const TDesC& aDbFileName, const DESC& aCommitStr)

	{

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

	RSqlDatabase db = TDbHelper<TYPE>::Open(aDbFileName);

	ExecuteSqlScript<HBUFC, PTRC, DESC>(db, KFillDbScript, aCommitStr);

	db.Close();	

	}

//Explicit InsertTest() template instantiations.

template void InsertTest<HBufC8, TPtrC8, TDesC8, ENonSecureDb>(const TDesC&, const TDesC8&);

template void InsertTest<HBufC8, TPtrC8, TDesC8, ESecureDb>(const TDesC&, const TDesC8&);

template void InsertTest<HBufC16, TPtrC16, TDesC16, ENonSecureDb>(const TDesC&, const TDesC16&);

template void InsertTest<HBufC16, TPtrC16, TDesC16, ESecureDb>(const TDesC&, const TDesC16&);

//"UPDATE" test function (parametrized update)

template <class DESC, TDbType TYPE> void UpdateTest(const TDesC& aDbFileName, const DESC& aUpdateSql)

	{

	TheTest.Printf(_L("\"Update (parametrized)\" test\r\n"));

	RSqlDatabase db = TDbHelper<TYPE>::Open(aDbFileName);

	RSqlStatement stmt;

	TInt err = stmt.Prepare(db, aUpdateSql);

	TEST2(err, KErrNone);

	TInt firstNamePrmIdx = stmt.ParameterIndex(KPrmName1());

	TEST(firstNamePrmIdx >= 0);

	TInt lastNamePrmIdx = stmt.ParameterIndex(KPrmName2());

	TEST(lastNamePrmIdx >= 0);

	TInt companyNamePrmIdx = stmt.ParameterIndex(KPrmName3());

	TEST(companyNamePrmIdx >= 0);

	TInt idIdx = stmt.ParameterIndex(KPrmName4());

	TEST(idIdx >= 0);

	TUint32 start = User::FastCounter();

	for(TInt id=1;id<=KTestTecordCount;++id)

		{

		TBuf<20> buf;

		buf.Copy(KFirstName);

		buf.AppendNum(id);

		err = stmt.BindText(firstNamePrmIdx, buf);

		TEST2(err, KErrNone);

		buf.Copy(KLastName);

		buf.AppendNum(id);

		err = stmt.BindText(lastNamePrmIdx, buf);

		TEST2(err, KErrNone);

		buf.Copy(KCompanyName);

		buf.AppendNum(id);

		err = stmt.BindText(companyNamePrmIdx, buf);

		TEST2(err, KErrNone);

		err = stmt.BindInt(idIdx, id);

		TEST2(err, KErrNone);

		err = stmt.Exec();

		TEST(err > 0);	

		err = stmt.Reset();

		TEST2(err, KErrNone);

		}

	TUint32 end = User::FastCounter();

	PrintStats(start, end);

	stmt.Close();

	db.Close();

	}

//Explicit UpdateTest() template instantiations.

template void UpdateTest<TDesC8, ENonSecureDb>(const TDesC&, const TDesC8&);

template void UpdateTest<TDesC8, ESecureDb>(const TDesC&, const TDesC8&);

template void UpdateTest<TDesC16, ENonSecureDb>(const TDesC&, const TDesC16&);

template void UpdateTest<TDesC16, ESecureDb>(const TDesC&, const TDesC16&);

//"UPDATE" test function (without parameters) - SQL server

template <class BUF, class DESC, TDbType TYPE> void UpdateWPTest(const TDesC& aDbFileName, const DESC& aUpdateSql)

	{

	TheTest.Printf(_L("\"Update (without parameters)\" test\r\n"));

	RSqlDatabase db = TDbHelper<TYPE>::Open(aDbFileName);

	TBuf<200> fmtstr;

	fmtstr.Copy(aUpdateSql);

	TUint32 start = User::FastCounter();

	for(TInt id=1;id<=KTestTecordCount;++id)

		{

		TBuf<200> buf;

		buf.Format(fmtstr, &KFirstName2, id, &KLastName2, id, &KCompanyName2, id, id);

		BUF sql;

		sql.Copy(buf);

		TInt err = db.Exec(sql);

		TEST(err > 0);	

		}

	TUint32 end = User::FastCounter();

	PrintStats(start, end);

	db.Close();

	PrintFileSize(aDbFileName);

	}

//Explicit UpdateWPTest() template instantiations.

template void UpdateWPTest<TBuf8<200>, TDesC8, ENonSecureDb>(const TDesC&, const TDesC8&);

template void UpdateWPTest<TBuf8<200>, TDesC8, ESecureDb>(const TDesC&, const TDesC8&);

template void UpdateWPTest<TBuf16<200>, TDesC16, ENonSecureDb>(const TDesC&, const TDesC16&);

template void UpdateWPTest<TBuf16<200>, TDesC16, ESecureDb>(const TDesC&, const TDesC16&);

//"SELECT" test function

template <class DESC, TDbType TYPE> void SelectTest(const TDesC& aDbFileName, const DESC& aSelectSql)

	{

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

	RSqlDatabase db = TDbHelper<TYPE>::Open(aDbFileName);

	RSqlStatement stmt;

	TInt err = stmt.Prepare(db, aSelectSql);

	TEST2(err, KErrNone);

	TUint32 start = User::FastCounter();

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

		{

		TBuf<20> buf;

		err = stmt.ColumnText(0, buf);			

		TEST2(err, KErrNone);

		TEST(buf.Length() > 0);

		err = stmt.ColumnText(1, buf);			

		TEST2(err, KErrNone);

		TEST(buf.Length() > 0);

		err = stmt.ColumnText(2, buf);			

		TEST2(err, KErrNone);

		TEST(buf.Length() > 0);

		}

	TEST2(err, KSqlAtEnd);

	TUint32 end = User::FastCounter();

	PrintStats(start, end);

	stmt.Close();

	db.Close();	

	}

//Explicit SelectTest() template instantiations.

template void SelectTest<TDesC8, ENonSecureDb>(const TDesC&, const TDesC8&);

template void SelectTest<TDesC8, ESecureDb>(const TDesC&, const TDesC8&);

template void SelectTest<TDesC16, ENonSecureDb>(const TDesC&, const TDesC16&);

template void SelectTest<TDesC16, ESecureDb>(const TDesC&, const TDesC16&);

//"DELETE" test function

template <class DESC, TDbType TYPE> void DeleteTest(const TDesC& aDbFileName, const DESC& aDeleteSql)

	{

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

	RSqlDatabase db = TDbHelper<TYPE>::Open(aDbFileName);

	TUint32 start = User::FastCounter();

	TInt err = db.Exec(aDeleteSql);

	TEST(err > 0);	

	TUint32 end = User::FastCounter();

	PrintStats(start, end);

	db.Close();	

	}

//Explicit SelectTest() template instantiations.

template void DeleteTest<TDesC8, ENonSecureDb>(const TDesC&, const TDesC8&);

template void DeleteTest<TDesC8, ESecureDb>(const TDesC&, const TDesC8&);

template void DeleteTest<TDesC16, ENonSecureDb>(const TDesC&, const TDesC16&);

template void DeleteTest<TDesC16, ESecureDb>(const TDesC&, const TDesC16&);

//Performance test function: INSERT, UPDATE, SELECT

template <class HBUFC, class PTRC, class DESC, TDbType TYPE> void PerformanceTest(const TDesC& aDbFileName,

																				  const DESC& aCommitStr,

																				  const DESC& aUpdateSql,

																				  const DESC& aSelectSql,

																				  const DESC& aDeleteSql)

	{

	CFileMan* fm = NULL;

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

	TEST2(err, KErrNone);

	TDbHelper<TYPE>::Create(aDbFileName);

	InsertTest<HBUFC, PTRC, DESC, TYPE>(aDbFileName, aCommitStr);

	PrintFileSize(aDbFileName);

	(void)fm->Copy(aDbFileName, TheNonSecureTmpDbName);

	UpdateTest<DESC, TYPE>(aDbFileName, aUpdateSql);

	PrintFileSize(aDbFileName);

	SelectTest<DESC, TYPE>(aDbFileName, aSelectSql);

	DeleteTest<DESC, TYPE>(aDbFileName, aDeleteSql);

	PrintFileSize(aDbFileName);

	(void)fm->Copy(TheNonSecureTmpDbName, aDbFileName);

	(void)fm->Delete(TheNonSecureTmpDbName);

	delete fm;

	}

//Explicit PerformanceTest() template instantiations.

template void PerformanceTest<HBufC8, TPtrC8, TDesC8, ENonSecureDb>(const TDesC&, const TDesC8&, const TDesC8&, const TDesC8&, const TDesC8&);

template void PerformanceTest<HBufC8, TPtrC8, TDesC8, ESecureDb>(const TDesC&, const TDesC8&, const TDesC8&, const TDesC8&, const TDesC8&);

template void PerformanceTest<HBufC16, TPtrC16, TDesC16, ENonSecureDb>(const TDesC&, const TDesC16&, const TDesC16&, const TDesC16&, const TDesC16&);

template void PerformanceTest<HBufC16, TPtrC16, TDesC16, ESecureDb>(const TDesC&, const TDesC16&, const TDesC16&, const TDesC16&, const TDesC16&);

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

///////     SQLITE API used directly  

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

//Template class offering Create() and Open() methods for creating/opening a sqlite3 handle.

template <TDbEncoding TYPE> class TDbHelper2

	{

public:	

	static void Create(const TDesC& aDbName);

	static sqlite3* Open(const TDesC& aDbName);

	};

//If error - prints error message and panics the application

void PanicIfError(sqlite3* aDbHandle, TInt aErr)

	{

  	if(aErr != SQLITE_OK && aErr != SQLITE_DONE && aErr != SQLITE_ROW)

  		{

  		if(aDbHandle)

  			{

	  		TPtrC p((const TUint16*)sqlite3_errmsg16(aDbHandle));

	  		TheTest.Printf(_L("Database err %d, msg: %S\n\n"), aErr, &p);

			(void)sqlite3_close(aDbHandle);

  			}

    	TEST(0);

  		}

	}