// 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 "sqldbaccessor.h"

#include "sqldbtransaction.h"

#include "urilist.h"

#include "tldlist.h"

#if defined (TESTDBNAME)

	_LIT( KDatabaseName, "[A00026FF]URIList.dat" );

#else

	_LIT( KDatabaseName, "[20009D70]URIList.dat" );	

#endif

CSqlDbAccessor::CSqlDbAccessor ()

	{

	}

CSqlDbAccessor::~CSqlDbAccessor ()

	{

	iDatabase.Close ();

	}

MDBAccessor* CSqlDbAccessor::NewL ( const RStringPool& aStringPool )

	{

	CSqlDbAccessor* self = new ( ELeave ) CSqlDbAccessor ();

	CleanupStack::PushL ( self );

	self->ConstructL ( aStringPool );

	CleanupStack::Pop ();

	return self;	

	}

void CSqlDbAccessor::ConstructL ( const RStringPool& aStringPool )

	{

	TInt result = iDatabase.Open ( KDatabaseName() );

	if ( result == KErrNotFound )

		{

		// Database is not existing. Create one.

		CreateL ( aStringPool );			

		}

	else 

		{

		User::LeaveIfError ( result );

		}

	}

/**

Creates a new database and its schemas.

*/		

void CSqlDbAccessor::CreateL ( const RStringPool& aStringPool )

	{

	// Create the URI List database. 

	// First create security policy	

	RSqlSecurityPolicy securityPolicy;

	CleanupClosePushL ( securityPolicy );

	// Create security policies container object using a default security policy.

	TSecurityPolicy defaultPolicy ( TSecurityPolicy::EAlwaysPass );

	User::LeaveIfError (securityPolicy.Create ( defaultPolicy )); 

	// Set up policy to apply to database schema and assign it

	TSecurityPolicy schemaPolicy ( TSecurityPolicy::EAlwaysPass );

	User::LeaveIfError (securityPolicy.SetDbPolicy ( RSqlSecurityPolicy::ESchemaPolicy, schemaPolicy ) );

	// Set up policy to apply to write activity on the database and assign it

	TSecurityPolicy writePolicy ( TSecurityPolicy::EAlwaysPass );

	User::LeaveIfError (securityPolicy.SetDbPolicy ( RSqlSecurityPolicy::EWritePolicy, writePolicy ));

	// Set up policy to apply to write activity to the database table named "URIList" and assign it

	TSecurityPolicy readPolicy ( TSecurityPolicy::EAlwaysPass );

	User::LeaveIfError (securityPolicy.SetDbPolicy(RSqlSecurityPolicy::EReadPolicy, readPolicy));

	User::LeaveIfError ( iDatabase.Create ( KDatabaseName, securityPolicy ) );	

	CreateSchemaL ( aStringPool );

	CleanupStack::PopAndDestroy (); // securityPolicy

	}

/**

Creates the schemas for the database. We create 2 tables ( URITbl & URIPropsTbl ), 1 view based 

on those 2 tables for easy retrieval of data and the triggers to update the view. In SQLite the

view updation is done via the triggers. By default in SQLite views are read-only

*/		

void CSqlDbAccessor::CreateSchemaL ( const RStringPool& aStringPool )

	{

	RBuf8 stmtBuf;

	CleanupClosePushL ( stmtBuf );

	stmtBuf.CreateL ( KMaxDbStmtLen );

	const TDesC8& id ( aStringPool.String (URILIST::EId,URILIST::Table).DesC() );

	const TDesC8& scheme ( aStringPool.String (URILIST::EScheme,URILIST::Table).DesC() );

	const TDesC8& userInfo ( aStringPool.String (URILIST::EUserInfo,URILIST::Table).DesC() );

	const TDesC8& host ( aStringPool.String (URILIST::EHost,URILIST::Table).DesC() );

	const TDesC8& port ( aStringPool.String (URILIST::EPort,URILIST::Table).DesC() );

	const TDesC8& path ( aStringPool.String (URILIST::EPath,URILIST::Table).DesC() );

	const TDesC8& query ( aStringPool.String (URILIST::EQuery,URILIST::Table).DesC() );

	const TDesC8& fragments ( aStringPool.String (URILIST::EFragments,URILIST::Table).DesC() );

	const TDesC8& RefId ( aStringPool.String (URILIST::EURIRefId,URILIST::Table).DesC() );

	const TDesC8& propId ( aStringPool.String (URILIST::EPropId,URILIST::Table).DesC() );

	const TDesC8& serviceType (aStringPool.String (URILIST::EServiceType,URILIST::Table).DesC());

	const TDesC8& listType (aStringPool.String (URILIST::EListType,URILIST::Table).DesC());

	const TDesC8& permission (aStringPool.String (URILIST::EPermission,URILIST::Table).DesC());

	const TDesC8& favouriteName (aStringPool.String (URILIST::EFavouriteName,URILIST::Table).DesC());

	const TDesC8& KTldname ( aStringPool.String (TLDLIST::ETLDName,TLDLIST::Table).DesC() );

	const TDesC8& KTldListType ( aStringPool.String (TLDLIST::EListType,TLDLIST::Table).DesC() );

	const TDesC8& KCharacterSet ( aStringPool.String (TLDLIST::ECharacterSet,TLDLIST::Table).DesC() );

	_LIT8 ( KURITblCreateStmt, "CREATE TABLE %S ( \

								%S INTEGR, \

								%S TEXT,\

								%S TEXT, \

								%S TEXT, \

								%S TEXT, \

								%S TEXT, \

								%S TEXT, \

								%S TEXT );" );

	_LIT8 ( KURIPropTblCreateStmt, "CREATE TABLE %S ( \

									%S INTEGER, \

									%S INTEGER,\

									%S INTEGER, \

									%S INTEGER, \

									%S INTEGER, \

									%S TEXT );" );

	_LIT8 ( KTldTblCreateStmt, "CREATE TABLE %S ( \

								%S TEXT,\

								%S INTEGER,\

								%S BLOB );" );

	_LIT8 ( KViewStmt, "CREATE VIEW %S AS SELECT \

					T1.%S %S, T1.%S %S, T1.%S %S, T1.%S %S, T1.%S %S, T1.%S %S, T1.%S %S, T1.%S %S, \

					T2.%S %S, T2.%S %S, T2.%S %S, T2.%S %S, T2.%S %S \

					FROM \

					%S T1, %S T2 \

					WHERE \

					T1.%S = T2.%S;" );

	_LIT8 ( KTriggerInsertStmt, "CREATE TRIGGER %S INSTEAD OF INSERT ON %S \

							BEGIN \

							INSERT INTO %S \

							VALUES ( new.%S, new.%S, new.%S, new.%S, new.%S, new.%S, new.%S, new.%S, \

							new.%S, new.%S, new.%S, new.%S, new.%S); \

							END;" );

	_LIT8 ( KTriggerUpdateStmt, "CREATE TRIGGER %S INSTEAD OF UPDATE ON %S \

								BEGIN \

								UPDATE %S SET %S=new.%S, %S=new.%S \

								WHERE %S=old.%S; \

								END;" );

	_LIT8 ( KTriggerDeleteStmt, "CREATE TRIGGER %S INSTEAD OF DELETE ON %S \

								BEGIN \

								DELETE FROM %S WHERE %S=old.%S; \

								END;" );

	_LIT8 ( KTldTableIndexStmt, "CREATE INDEX %S ON %S ( %S, %S )" );

	stmtBuf.Format ( KURITblCreateStmt(), &(KUriTblName()), &id, &scheme, &userInfo, &host, &port, &path, &query, &fragments );

	ExecuteL ( stmtBuf );

	stmtBuf.Format ( KURIPropTblCreateStmt(), &(KUriPropsTblName()), &RefId, &propId, &serviceType, &listType, &permission, &favouriteName );	

	ExecuteL ( stmtBuf );

	stmtBuf.Format ( KViewStmt(), &(KViewName()), &id, &id, &scheme, &scheme, &userInfo, &userInfo, &host, &host, &port, &port,

					 &path, &path, &query, &query, &fragments, &fragments, &propId, &propId, &serviceType, &serviceType, 

					 &listType, &listType, &permission, &permission, &favouriteName, &favouriteName, &(KUriTblName()), &(KUriPropsTblName()), &id, &propId );

	ExecuteL ( stmtBuf );

	stmtBuf.Format ( KTriggerInsertStmt(), &(KTriggerInsertView()), &(KViewName()), &(KViewName()), &id, &scheme, &userInfo, &host, &port, &path, &query, &fragments, &propId, &serviceType, &listType, &permission, &favouriteName );

	ExecuteL ( stmtBuf );

	stmtBuf.Format ( KTriggerUpdateStmt(), &(KTriggerUpdateView()), &(KViewName()), &(KViewName()), &listType, &listType, &favouriteName, &favouriteName, &propId, &propId );

	ExecuteL ( stmtBuf );

	stmtBuf.Format ( KTriggerDeleteStmt(), &(KTriggerDeleteView()), &(KViewName()), &(KViewName()), &propId, &propId );		

	ExecuteL ( stmtBuf );

	stmtBuf.Format ( KTldTblCreateStmt(), &(KTldTblName()), &KTldname, &KTldListType, &KCharacterSet );

	ExecuteL ( stmtBuf );

	stmtBuf.Format ( KTldTableIndexStmt(), &(KTldTblIndex()), &(KTldTblName()), &KTldname, &KTldListType );

	ExecuteL ( stmtBuf );

	CleanupStack::PopAndDestroy ( ); // stmtBuf	

	}

/**

Executes the SQL statement and leave incase of error

*/

void CSqlDbAccessor::ExecuteL ( const TDesC8& aStmt )

	{

	User::LeaveIfError ( iDatabase.Exec ( aStmt ) );

	}

/**

Prepares a new SQL transaction object by providing a SQL statement.

*/

MDBTransaction* CSqlDbAccessor::PrepareTransactionL ( const TDesC8& aQueryStmt )

	{

	return CSqlDbTransaction::NewL ( iDatabase, aQueryStmt );

	}

/**

Scalar queries are queries which returns a single value upon completion. This function

executes a scalar query and returns the result

*/

TInt CSqlDbAccessor::ExecuteScalarQueryL ( const TDesC8& aQueryStmt )

	{

	TSqlScalarFullSelectQuery selectQuery ( iDatabase );

	return 	selectQuery.SelectIntL ( aQueryStmt );

	}

void CSqlDbAccessor::BeginTransactionL ()

	{

	_LIT8 ( KBeginTrans, "BEGIN TRANSACTION;" );

	ExecuteL ( KBeginTrans() );

	}

void CSqlDbAccessor::CommitTransactionL ()

	{

	_LIT8 ( KCommitTrans, "COMMIT TRANSACTION;" );

	ExecuteL ( KCommitTrans() );			

	}

void CSqlDbAccessor::RollbackTransaction ()

	{

	_LIT8 ( KRollbackTrans, "ROLLBACK TRANSACTION;" );

	TInt err = iDatabase.Exec ( KRollbackTrans() );			

	}

void CSqlDbAccessor::Release ()

	{

	delete this;			

	}