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

//

// Description:

// Store/Load database system settings (database settings, security policies)

// 

//

#include <f32file.h>

#include <f32file64.h>

#include <utf.h>				//CnvUtfConverter

#include <sqldb.h>				//RSqlSecurityPolicy enum items

#include "SqlSrvDbSysSettings.h"

#include "SqlUtil.h"			//Sql2OsErrCode()

#include "sqlite3.h"			//SQLITE API

#include "SqliteSymbian.h"		//sqlite3SymbianLastOsError()

#include "SqlSecurityImpl.h"	//CSqlSecurityPolicy

#include "SqlSrvStatementUtil.h"//Global SQL execution functions

#include "SqlSrvCollation.h"	//TSqlCollate

#include "SqlSrvStrings.h"		//System table names

#include "SqlSrvUtil.h"			//Global functions

#include "SqlSrvFileData.h"		//TSqlSrvFileData

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

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

/////////////////////////////        Local const data   ///////////////////////////////////////////////////////

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

enum

	{

	ESqlSystemVersion1 = 1,	//Base version

	ESqlSystemVersion2 = 2,	//New field into "Settings" table - "Reserved" (unused, set to 0)

	ESqlSystemVersion3 = 3,	//New field into "Settings" table - "CollationDllName"

				            //Existing field in "Settings" table - "Reserved" - now used to store processed version of db config file

	ESqlSystemVersion4 = 4	//"CompactionMode" field added

	};

//Current version of the system settings - reflects changes in the system tables.

const TInt KSqlSystemVersion = ESqlSystemVersion4;

//The database names in all statements are quoted to avoid the "sql injection" threat.

//(At the moment there is no way to pass an invalid database name for these statements, because the database has to be attached

// first and a parameter binding is used there. So, the quoting is just for safety and against changes in the future)

_LIT(KCreateSettingsSql,"CREATE TABLE \"%S\".symbian_settings(Id INTEGER,Reserved INTEGER,CollationDllName TEXT, CompactionMode INTEGER);\x0");

_LIT(KDropSettingsSql, "DROP TABLE IF EXISTS \"%S\".symbian_settings;\x0");

_LIT(KInsertSettingsSql, "INSERT INTO \"%S\".symbian_settings VALUES(%d, %d,'%S',%d);\x0");

_LIT(KUpdateCollationSettingsSql, "UPDATE \"%S\".symbian_settings SET CollationDllName='%S';\x0");

_LIT(KUpdateFileVersionSettingsSql, "UPDATE \"%S\".symbian_settings SET Reserved=%d;\x0");

_LIT(KGetSettingsSql, "SELECT * FROM \"%S\".symbian_settings;\x0");

_LIT(KGetIndexSql, "SELECT name FROM \"%S\".sqlite_master WHERE type = 'index' AND sql LIKE '%%%S%%'\x0");

_LIT(KSettingsTableCheckSql, "SELECT name from \"%S\".sqlite_master WHERE type = 'table' AND name = 'symbian_settings';\x0");

_LIT(KReindexSql, "REINDEX \"%S\";\x0");

_LIT8(KCreateSecuritySql,"CREATE TABLE symbian_security(Id INTEGER PRIMARY KEY AUTOINCREMENT,ObjectType INTEGER,ObjectName TEXT,PolicyType INTEGER,PolicyData BLOB);\x0");

_LIT8(KInsertSecuritySql, "INSERT INTO symbian_security(ObjectType,ObjectName,PolicyType,PolicyData) VALUES(:V1,:V2,:V3,:V4);\x0");

_LIT8(KGetSecuritySql, "SELECT * FROM symbian_security;\x0");

_LIT8(KBeginTransactionSql, "BEGIN;\x0");

_LIT8(KCommitTransactionSql, "COMMIT;\x0");

_LIT8(KRollbackTransactionSql, "ROLLBACK;\x0");

//KInsertSecuritySql statement - parameter indices

const TInt KObjTypePrmIdx = 1;

const TInt KObjNamePrmIdx = 2;

const TInt KObjPolicyTypePrmIdx = 3;

const TInt KObjPolicyDataPrmIdx = 4;

//Default security policy - object type code and policy type code

const TInt KDefaultObjType = -2;

const TInt KDefaultPolicyType = -1;

//Database security policy - object type code

const TInt KDbObjType = -1;

//KGetSettingsSql sql statement - column indices

const TInt KSysVersionColIdx = 0;

const TInt KConfigFileVersionColIdx = 1;

const TInt KCollationDllNameColIdx = 2;

const TInt KCompactionModeColIdx = 3;

//KGetSecuritySql sql statement - column indices

const TInt KObjTypeColIdx = 1;

const TInt KObjNameColIdx = 2;

const TInt KObjPolicyTypeColIdx = 3;

const TInt KObjPolicyDataColIdx = 4;

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

/////////////////////////////        Local functions   ////////////////////////////////////////////////////////

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

//Finalzes the statement handle (aHandle argument). Used in stack cleanup operations.

//Panic SqlDb 4 In _DEBUG mode if aHandle argument is NULL.

static void FinalizeStatementHandle(void* aHandle)

	{

	sqlite3_stmt* stmtHandle = static_cast <sqlite3_stmt*> (aHandle);

	(void)sqlite3_finalize(stmtHandle);

	}

//Transaction rollback. Used in stack cleanup operations.

//Panic SqlDb 4 In _DEBUG mode if aDbHandle argument is NULL.

static void RollbackTransaction(void* aDbHandle)

	{

	(void)::DbExecStmt8(reinterpret_cast <sqlite3*> (aDbHandle), KRollbackTransactionSql);

	}

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

/////////////////////////////      TSqlDbSysSettings   ////////////////////////////////////////////////////////

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

/**

Initializes TSqlDbSysSettings data members with default values.

@param aDbHandle Database handle. TSqlDbSysSettings does not own aDbHandle argument.

@panic SqlDb 4 In _DEBUG mode if aDbHandle argument is NULL.

*/

TSqlDbSysSettings::TSqlDbSysSettings(sqlite3* aDbHandle) :

	iDbHandle(aDbHandle)

	{

	}

/**

Creates the database security policy table and stores the security policy in the table.

@param aSecurityPolicy   Database security policies container, which data needs to be persisted.

@leave KErrNoMemory, an out of memory condition has occurred.

                  Note that the function may also leave with some other database specific 

                  errors categorised as ESqlDbError, and other system-wide error codes.

@panic SqlDb 2 In _DEBUG mode if iDbHandle is NULL (uninitialized TSqlDbSysSettings object).

*/

void TSqlDbSysSettings::StoreSecurityPolicyL(const CSqlSecurityPolicy& aSecurityPolicyCon)

	{

	__SQLLEAVE_IF_ERROR(::DbExecStmt8(iDbHandle, KBeginTransactionSql()));

	CleanupStack::PushL(TCleanupItem(&RollbackTransaction, iDbHandle));

	__SQLLEAVE_IF_ERROR(::DbExecStmt8(iDbHandle, KCreateSecuritySql()));

	StoreSecurityPoliciesL(aSecurityPolicyCon);

	__SQLLEAVE_IF_ERROR(::DbExecStmt8(iDbHandle, KCommitTransactionSql()));

	CleanupStack::Pop();//TCleanupItem(&RollbackTransaction, iDbHandle)

	}

/**

Stores the database system settings in the settings table. The settings table is recreated.

@param aDbName Logical database name: "main" for the main database or attached database name,

@param aCollationDllName Collation dll name. It uniquely identifies the current collation method in use.

                         If the default collation method changes later then the database will be reindexed 

                         and the new collation dll name will replace the existing one in the settings table.

@param aDbConfigFileVersion Current config file version or KSqlNullDbConfigFileVersion

@param aCompactionMode Database compaction mode, one of TSqlCompactionMode enum item values (except ESqlCompactionNotSet)

@see TSqlCompactionMode

@leave KErrNoMemory, an out of memory condition has occurred.

                  Note that the function may also leave with some other database specific 

                  errors categorised as ESqlDbError, and other system-wide error codes.

@panic SqlDb 2 In _DEBUG mode if iDbHandle is NULL (uninitialized TSqlDbSysSettings object).

@panic SqlDb 4 In _DEBUG mode if aCompactionMode parameter value is invalid.

*/

void TSqlDbSysSettings::StoreSettingsL(const TDesC& aDbName, const TDesC& aCollationDllName, TInt aDbConfigFileVersion, TSqlCompactionMode aCompactionMode)

	{

	HBufC* buf = HBufC::NewLC(Max((TInt)sizeof(KDropSettingsSql), 

							  Max((TInt)sizeof(KCreateSettingsSql), (TInt)sizeof(KInsertSettingsSql))) + 

							  aDbName.Length() + aCollationDllName.Length() + 10);

	TPtr sql = buf->Des();

	//Begin transaction

	__SQLLEAVE_IF_ERROR(::DbExecStmt8(iDbHandle, KBeginTransactionSql()));

	CleanupStack::PushL(TCleanupItem(&RollbackTransaction, iDbHandle));	

	//Recreate the "settings" table and store the default collation there (the DLL name)

	sql.Format(KDropSettingsSql(), &aDbName);

	__SQLLEAVE_IF_ERROR(::DbExecStmt16(iDbHandle, sql));

	sql.Format(KCreateSettingsSql, &aDbName);

	__SQLLEAVE_IF_ERROR(::DbExecStmt16(iDbHandle, sql));

	sql.Format(KInsertSettingsSql(), &aDbName, KSqlSystemVersion, aDbConfigFileVersion, &aCollationDllName, aCompactionMode);

	__SQLLEAVE_IF_ERROR(::DbExecStmt16(iDbHandle, sql));

	//Commit transaction

	__SQLLEAVE_IF_ERROR(::DbExecStmt8(iDbHandle, KCommitTransactionSql()));

	CleanupStack::Pop();//TCleanupItem(&RollbackTransaction, iDbHandle)

	CleanupStack::PopAndDestroy(buf);

	}

/**

Read security policies from security policy table.

The method does not guarantee that either the security policies will be read from the 

security table and stored in aSecurityPolicy parameter or aSecurityPolicy argument 

stays unchanged in a case of failure.

@param aSecurityPolicyCon Security policies container which needs to be initializeed with 

						  the database security policies.

@leave KErrNoMemory, an out of memory condition has occurred;

	   KErrGeneral, missing or invalid data in the system tables;

                  Note that the function may also leave with some other database specific 

                  errors categorised as ESqlDbError, and other system-wide error codes.

@panic SqlDb 2 In _DEBUG mode if iDbHandle is NULL (uninitialized TSqlDbSysSettings object).

*/

void TSqlDbSysSettings::LoadSecurityPolicyL(CSqlSecurityPolicy& aSecurityPolicyCon)

	{

	//Even if the version of the system settings is bigger than the current one (KSqlSystemVersion constant),

	//I think that all future modifications of the system tables shall not affect the already existing

	//fields. So it is correct to think that all information available in version 1 should be available 

	//(and accessible) in all future versions of the system settings.

	//Note: no attempt shall be made here to modify the system tables structure! There may be more than one 

	//      connection to the database being processed!

	//

	//Prepare statement handle

	sqlite3_stmt* stmtHandle = ::StmtPrepare8L(iDbHandle, KGetSecuritySql());

	CleanupStack::PushL(TCleanupItem(&FinalizeStatementHandle, stmtHandle));

	//Read the security policies

	TBool defaultPolicySet = EFalse;

	TInt dbPolicySetFlag = 0;

	TInt err;

	while((err = ::StmtNext(stmtHandle)) == KSqlAtRow)

		{

		TInt objType = KDefaultObjType - 1;

		TPtrC objName(KNullDesC);

		TInt policyType = RSqlSecurityPolicy::ESchemaPolicy - 1;

		TSecurityPolicy policy = ReadCurrSecurityPolicyL(stmtHandle, objType, objName, policyType);

		switch(objType)

			{

			case KDefaultObjType:

				if(defaultPolicySet)

					{

					__SQLLEAVE(KErrGeneral);//two "default policy" records in the table

					}

				StoreDefaultSecurityPolicy(aSecurityPolicyCon, policy, dbPolicySetFlag);

				defaultPolicySet = ETrue;

				break;

			case KDbObjType:

				StoreDbSecurityPolicyL(aSecurityPolicyCon, policyType, policy, dbPolicySetFlag);

				break;

			case RSqlSecurityPolicy::ETable:

				StoreDbObjSecurityPolicyL(aSecurityPolicyCon, objType, objName, policyType, policy);

				break;

			default:

				__SQLLEAVE(KErrGeneral);

				break;

			}//end of "switch(aObjType)"

		}

	CleanupStack::PopAndDestroy();//cleanupItem (statement handle)

	__SQLLEAVE_IF_ERROR(err);

	if(!defaultPolicySet)

		{

		__SQLLEAVE(KErrGeneral);//no default policy

		}

	}

/**

Loads the database system settings from the settings table.

If the settings table does not exist then it is created with the default settings

(the stored collation dll name will be empty and the stored database configuration 

file version will be 0).

@param aDbName Logical database name: "main" for the main database or attached database name,

@param aCollationDllName Output parameter, will contain the stored collation dll name,

@param aDbConfigFileVersion Output parameter, will contain the stored database config file version.

@param aCompactionMode Output parameter. Database compaction mode (one of TSqlCompactionMode enum item values except ESqlCompactionNotSet).

					   Note that the input value of this parameter might be ESqlCompactionManual if the this is a legacy database,

					   created not by the SQL server.

@see TSqlCompactionMode

@leave KErrNoMemory, an out of memory condition has occurred.

       Note that the function may also leave with some other database specific 

       errors categorised as ESqlDbError, and other system-wide error codes..

@panic SqlDb 2 In _DEBUG mode if iDbHandle is NULL (uninitialized TSqlDbSysSettings object).

@panic SqlDb 7 In _DEBUG mode if the stored compaction mode is invalid.

*/	

void TSqlDbSysSettings::LoadSettingsL(const TDesC& aDbName, TDes& aCollationDllName, TInt& aDbConfigFileVersion, TSqlCompactionMode& aCompactionMode)

	{

	aCollationDllName.Zero();

	aDbConfigFileVersion = KSqlNullDbConfigFileVersion;	

	if(aCompactionMode == ESqlCompactionNotSet)

		{

		aCompactionMode = KSqlDefaultCompactionMode;

		}

	//If the system settings table does not exist then create it now.

	//For a database created by the SQL Server this will only occur 

	//when the database is being created in an application's private data cage -

	//as part of this create call it is now being opened by the server.

	//An externally created database is likely to not contain the settings table 

	//and adding it here makes the database 'SQL Server-compatible'

	if(!SettingsTableExistsL(aDbName))

		{

		StoreSettingsL(aDbName, aCollationDllName, KSqlNullDbConfigFileVersion, aCompactionMode); // store empty collation dll name, then reindexing will occur

		}

	else

		{

		//Get the settings from the existing table

		TInt settingsVersion = 0;

		GetSettingsL(aDbName, aCollationDllName, aDbConfigFileVersion, settingsVersion, aCompactionMode);

		if(settingsVersion < KSqlSystemVersion)

			{

			//Recreate the settings table using the last version number format (this is what the old code did during reindexing)

			StoreSettingsL(aDbName, aCollationDllName, aDbConfigFileVersion, aCompactionMode); // store empty collation dll name, then reindexing will occur

			}

		}

	}

/**

Retrieves the database system settings from the settings table.

@param aDbName Logical database name: "main" for the main database or attached database name,

@param aCollationDllName Output parameter, will contain the stored collation dll name,

@param aDbConfigFileVersion Output parameter, will contain the stored database config file version,

@param aSettingsVersion Output parameter, will contain the version of the settings table.

@param aCompactionMode Output parameter. Database compaction mode (one of TSqlCompactionMode enum item values except ESqlCompactionNotSet).

@see TSqlCompactionMode

@leave KErrGeneral, either unable to retrieve the data from the settings table or the 

					stored table version or config file version is invalid or the stored compaction mode is invalid.

	   KErrOverflow, aCollationDllName is not large enough to store the name of the 

	   				 collation dll that is stored in the settings table.

       KErrNoMemory, an out of memory condition has occurred.

	   Note that the function may also leave with other system-wide error codes or SQL

	   errors of ESqlDbError type

@panic SqlDb 2 In _DEBUG mode if iDbHandle is NULL (uninitialized TSqlDbSysSettings object).

*/

void TSqlDbSysSettings::GetSettingsL(const TDesC& aDbName, TDes& aCollationDllName, TInt& aDbConfigFileVersion, 

									 TInt& aSettingsVersion, TSqlCompactionMode& aCompactionMode)

	{

	HBufC* buf = HBufC::NewLC(sizeof(KGetSettingsSql) + aDbName.Length());

	TPtr sql = buf->Des();

	//Prepare statement handle

	sql.Format(KGetSettingsSql(), &aDbName);

	sqlite3_stmt* stmtHandle = ::StmtPrepare16L(iDbHandle, sql);

	CleanupStack::PushL(TCleanupItem(&FinalizeStatementHandle, stmtHandle));

	//Move to the first record

	TInt err = ::StmtNext(stmtHandle);

	__SQLLEAVE_IF_ERROR(err);

	//Check that it is a valid row. The error is checked on the previous line. 

	//The "if" bellow will check whether there is a valid record or not.

	if(err != KSqlAtRow)

		{

		__SQLLEAVE(KErrGeneral);

		}

	//Get the system settings version 

	aSettingsVersion = sqlite3_column_int(stmtHandle, KSysVersionColIdx);

	if(aSettingsVersion < ESqlSystemVersion1)

		{

		__SQLLEAVE(KErrGeneral);

		}

	if(aSettingsVersion > ESqlSystemVersion2)

		{

		//The "ConfigFileVersion" column exists and is used to store the most recent

		//version of the database's config file (if there is one) that has 

		//been successfully processed

		aDbConfigFileVersion = sqlite3_column_int(stmtHandle, KConfigFileVersionColIdx);

		if(aDbConfigFileVersion < KSqlNullDbConfigFileVersion)

			{

			__SQLLEAVE(KErrGeneral);

			}

		//The "CollationDllName" column exists and its value can be read.

        //The column type might be different than SQLITE_TEXT - malformed database.

        if(sqlite3_column_type(stmtHandle, KCollationDllNameColIdx) != SQLITE_TEXT)

            {

            __SQLLEAVE(KErrGeneral);   

            }

		const void* ptr = sqlite3_column_text16(stmtHandle, KCollationDllNameColIdx);

        //Null column value - this might be an indication of an "out of memory" problem, if the column text  

        //is in UTF8 format. (sqlite3_column_text16() may allocate memory for UTF8->UTF16 conversion)

        TPtrC16 src(reinterpret_cast <const TUint16*> (ptr));

        if(src.Length() > aCollationDllName.MaxLength())

            {

            __SQLLEAVE(KErrOverflow);	

            }

        aCollationDllName.Copy(src);

		}

	if(aSettingsVersion > ESqlSystemVersion3)

		{

		aCompactionMode = static_cast <TSqlCompactionMode> (sqlite3_column_int(stmtHandle, KCompactionModeColIdx));

		if(aCompactionMode != ESqlCompactionManual && aCompactionMode != ESqlCompactionBackground && aCompactionMode != ESqlCompactionAuto)

			{

			__SQLLEAVE(KErrGeneral);

			}

		}

	CleanupStack::PopAndDestroy();//TCleanupItem(&FinalizeStatementHandle, stmtHandle)	

	CleanupStack::PopAndDestroy(buf);

	}

/**

Deletes and recreates all database indexes which use user-defined collation methods, from scratch.

Also updates the database settings table with the name of the current collation dll.

This is all performed as a single atomic transaction.

@param aDbName Logical database name: "main" for the main database or attached database name,

@param aCurrentCollationDllName The name of the current collation dll.

@leave The function may leave with system-wide error codes or SQL errors of ESqlDbError type

@panic SqlDb 2 In _DEBUG mode if iDbHandle is NULL (uninitialized TSqlDbSysSettings object).

*/

void TSqlDbSysSettings::ReindexDatabaseL(const TDesC& aDbName, const TDesC& aCurrentCollationDllName)

	{	

	//Allocate memory for the SQL statements

	HBufC* buf = HBufC::NewLC(Max((TInt)sizeof(KUpdateCollationSettingsSql), (TInt)sizeof(KReindexSql)) + 

							  aDbName.Length() + aCurrentCollationDllName.Length());

	TPtr sql = buf->Des();

	//Begin transaction

	__SQLLEAVE_IF_ERROR(::DbExecStmt8(iDbHandle, KBeginTransactionSql()));

	CleanupStack::PushL(TCleanupItem(&RollbackTransaction, iDbHandle));	

	const TSqlCollationUtil collationUtil(iDbHandle);

	//Issue a "REINDEX" command for each collation that has at least one index using it

	for(TInt i = collationUtil.CollationCount()-1; i >= 0 ;--i)

		{

		TPtrC collationName = collationUtil.CollationName(i);

		if(IndexExistsL(aDbName, collationName))

			{

			sql.Format(KReindexSql(), &collationName);

			__SQLLEAVE_IF_ERROR(::DbExecStmt16(iDbHandle, sql));

			}

		}

	//Update the settings table to store the current collation DLL name

	sql.Format(KUpdateCollationSettingsSql(), &aDbName, &aCurrentCollationDllName);

	__SQLLEAVE_IF_ERROR(::DbExecStmt16(iDbHandle, sql));

	//Commit transaction

	__SQLLEAVE_IF_ERROR(::DbExecStmt8(iDbHandle, KCommitTransactionSql()));

	CleanupStack::Pop(); //TCleanupItem(&RollbackTransaction, iDbHandle)

	CleanupStack::PopAndDestroy(buf);

	}

/**

Performs any necessary configuration file updates to the database if a 

configuration file exists for the database that has not yet been processed.

If a configuration file is processed then the database settings table will 

be updated with the version of the configuration file that was processed.

@param aStoredDbConfigFileVersion The configuration file version that is

								  currently stored in the settings table

@param aFileData The database file data

@param aDbName Logical database name: "main" for the main database or attached database name

@leave One of the system-wide error codes or SQL errors of ESqlDbError type

@panic SqlDb 2 In _DEBUG mode if iDbHandle is NULL (uninitialized TSqlDbSysSettings object)

*/

void TSqlDbSysSettings::ConfigureDatabaseL(TInt aStoredDbConfigFileVersion, const TSqlSrvFileData& aFileData, 

										   const TDesC& aDbName)

	{	

	if(!aFileData.IsSecureFileNameFmt())

		{

		//As a first implementation, config files will only be supported for 

		//shared, secure databases - not for private, secure databases or public databases

		return;	

		}

	//Determine whether there is a configuration file 

	//for this database that has not yet been processed

	const CDbConfigFiles* dbConfigFiles = aFileData.DbConfigFiles();

	if(dbConfigFiles)

		{

		//Get the database file name and extension to search for a corresponding config file

		TPtrC dbName = aFileData.FileName();

		TParse parse;

		__SQLLEAVE_IF_ERROR(parse.Set(dbName, NULL, NULL));

		TPtrC dbFileNameAndExt = parse.NameAndExt();

		HBufC* matchingConfigFile = dbConfigFiles->FindConfigFile(dbFileNameAndExt);

		if(matchingConfigFile)

			{

			//There exists a config file for this database - now check whether its

			//version is greater than the version stored in the settings table and 

			//only process the file if it is

			TFileName configFileName;

			configFileName.Copy(matchingConfigFile->Des());

			TInt offset = configFileName.LocateReverse('.') ;

			if(KErrNotFound != offset)

				{

				TInt lengthOfExt = configFileName.Length() - offset - 1;

				TPtrC versionNum = configFileName.Right(lengthOfExt);				

				TLex lex(versionNum);

				TInt fileVersion = 0;

				if(KErrNone == lex.Val(fileVersion))

					{

					if(fileVersion > aStoredDbConfigFileVersion)

						{