/*

* Copyright (c) 2008 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:  Class that directly interacts with Sqlite database.

*

*/

// System Includes

#include <s32strm.h>

// User Includes

#include "evtdatabase.h"

#include "evtdbnotifier.h"

#include "evtaction.h"

#include "evtconsts.h"

#include "evtdebug.h"

// Constant

const TInt KBufferLength = 512;

const TInt KMaxIntLength = 22;

// ================ Member funtions for CEvtDatabase class ==================

// ---------------------------------------------------------------------------

// CEvtDatabase::CEvtDatabase

// ---------------------------------------------------------------------------

//

CEvtDatabase::CEvtDatabase( )

    {

    }

// ---------------------------------------------------------------------------

// CEvtDatabase::~CEvtDatabase

// ---------------------------------------------------------------------------

//

CEvtDatabase::~CEvtDatabase()

    {

    // Close Database handle

    CloseDatabase();

    delete iDbNotifier;

    } 

// ---------------------------------------------------------------------------

// CEvtDatabase::NewL

// ---------------------------------------------------------------------------

//

CEvtDatabase* CEvtDatabase::NewL( )

    {

	CEvtDatabase* self = NewLC( );

	CleanupStack::Pop( self );

	return self;

    }

// ---------------------------------------------------------------------------

// CEvtDatabase::NewLC

// ---------------------------------------------------------------------------

//

CEvtDatabase* CEvtDatabase::NewLC( )

    {

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

	CleanupStack::PushL( self );

	self->ConstructL( );

	return self;

    } 

// ---------------------------------------------------------------------------

// CEvtDatabase::ConstructL

// ---------------------------------------------------------------------------

//

void CEvtDatabase::ConstructL()

    {

	EVTUIDEBUG( "+ CEvtDatabase::OpenDatabaseL()" );

	EVTUIDEBUG( "Calling OpenDatabaseL()" );

    // Open the handle to Database

    OpenDatabaseL( );

	EVTUIDEBUG( "Calling CEvtDbNotifier::NewL()" );

    // Notifier is instantiated to notify and listen to database change.

    iDbNotifier = CEvtDbNotifier::NewL( *this );

	EVTUIDEBUG( "+ CEvtDatabase::OpenDatabaseL()" );

    } 

// ---------------------------------------------------------------------------

// CEvtDatabase::CreateOpenDatabaseL

// ---------------------------------------------------------------------------

//

void CEvtDatabase::OpenDatabaseL( )

	{

	EVTUIDEBUG( "+ CEvtDatabase::OpenDatabaseL()" );

  	// First try to open the database.

	TInt err = iDb.Open( KEventsDbSecureFName() );

	// If the database is not present, then a database and schema has to be

  	// created

	if( KErrNotFound == err )

		{

		CreateOpenDatabaseL( );		// Create Database

		CreateSchemaL( );			// Create Schema

		}

	else if( KErrNone != err )

		User::LeaveIfError( err );	// Leave if Open fails for any other reason

	EVTUIDEBUG( "- CEvtDatabase::OpenDatabaseL()" );

	}

// ---------------------------------------------------------------------------

// CEvtDatabase::CloseDatabaseL

// ---------------------------------------------------------------------------

//

void CEvtDatabase::CloseDatabase()

	{

	EVTUIDEBUG( "+ CEvtDatabase::CloseDatabase()" );

	// Close db handle

	iDb.Close();

	EVTUIDEBUG( "- CEvtDatabase::CloseDatabase()" );

	}

// ---------------------------------------------------------------------------

// CEvtDatabase::CreateDatabaseL

// ---------------------------------------------------------------------------

//

void CEvtDatabase::CreateOpenDatabaseL( )

	{

	EVTUIDEBUG( "+ CEvtDatabase::CreateOpenDatabaseL()" );

	// To create a Secure Database, we first need to create the Security policy.

	// Security policy once defined for a database, can not be changed.

	TSecurityPolicy defaultPolicy( TSecurityPolicy::EAlwaysPass );

	RSqlSecurityPolicy securityPolicy;

	TInt err = securityPolicy.Create( defaultPolicy );

	User::LeaveIfError( err );

    // Define Security Policy

    const TSecurityPolicy KSchemaPolicy( ECapabilityLocation );

    const TSecurityPolicy KReadWritePolicy( ECapabilityLocation );

	// Set the security Policy for Schema and tables

	securityPolicy.SetDbPolicy( RSqlSecurityPolicy::ESchemaPolicy, 

															KSchemaPolicy );

	securityPolicy.SetDbPolicy( RSqlSecurityPolicy::EReadPolicy, 

															KReadWritePolicy );

	securityPolicy.SetDbPolicy( RSqlSecurityPolicy::EWritePolicy, 

															KReadWritePolicy );

	// CreateL will create and open the handle to database.

	iDb.CreateL( KEventsDbSecureFName(), securityPolicy );

	// Close the Security Policy

	securityPolicy.Close();

	EVTUIDEBUG( "- CEvtDatabase::CreateOpenDatabaseL()" );

	}

// ---------------------------------------------------------------------------

// CEvtDatabase::CreateSchemaL

// ---------------------------------------------------------------------------

//

void CEvtDatabase::CreateSchemaL()

	{

	EVTUIDEBUG( "+ CEvtDatabase::CreateSchemaL()" );

	// Create Tables in database

	CreateTablesL();

	// Create Triggers in database

	CreateTriggerL();

	// Create Indexes in database

	CreateIndexL();

	EVTUIDEBUG( "- CEvtDatabase::CreateSchemaL()" );

	}

// ---------------------------------------------------------------------------

// CEvtDatabase::CreateTablesL

// ---------------------------------------------------------------------------

//

void CEvtDatabase::CreateTablesL()

	{

	EVTUIDEBUG( "+ CEvtDatabase::CreateTablesL()" );

	// Leave if the creation of tables fail.

	User::LeaveIfError( 

			iDb.Exec( KCreateEventTable ) );

	User::LeaveIfError( 

			iDb.Exec( KCreateActionTable ) );

	EVTUIDEBUG( "- CEvtDatabase::CreateTablesL()" );

	}

// ---------------------------------------------------------------------------

// CEvtDatabase::CreateTriggerL

// ---------------------------------------------------------------------------

//

void CEvtDatabase::CreateTriggerL()

	{

	EVTUIDEBUG( "+ CEvtDatabase::CreateTriggerL()" );

	// Triggers are used to maintain the referential integrity between

	// Event and Action tables.

	// This avoids Insert, Update and Delete anamolies.

	// Leave if the creation of triggers fail.

	User::LeaveIfError( 

			iDb.Exec( KCreateTriggerActionEventInsert ) );

	User::LeaveIfError( 

			iDb.Exec( KCreateTriggerActionEventUpdate ) );

	User::LeaveIfError( 

			iDb.Exec( KCreateTriggerActionEventDelete ) );

	EVTUIDEBUG( "- CEvtDatabase::CreateTriggerL()" );

	}

// ---------------------------------------------------------------------------

// CEvtDatabase::CreateIndexL

// ---------------------------------------------------------------------------

//

void CEvtDatabase::CreateIndexL()

	{

	EVTUIDEBUG( "+ CEvtDatabase::CreateIndexL()" );

    // Indexes are created to improve the performance of select Queries

    // Following indexes are created

    // 1. Index to "Select Event based on Status and Sort by Subject"

    // 2. Index to "Select Event based on Unique Event Id"

    // 3. Index to "Select Event based on Event Id" - (LBT Engine's Id)

    // 4. Index to "Select Action based on Unique Event Id"

	// Leave if the creation of indexes fail.

	User::LeaveIfError( 

			iDb.Exec( KCreateEventStatusSubjectIndex ) );

	User::LeaveIfError( 

			iDb.Exec( KCreateEventEvtIdIndex ) );

	User::LeaveIfError( 

			iDb.Exec( KCreateActionEvtIdIndex ) );

	EVTUIDEBUG( "- CEvtDatabase::CreateIndexL()" );

	}

// ---------------------------------------------------------------------------

// CEvtDatabase::GetEventsL

// ---------------------------------------------------------------------------

//

void CEvtDatabase::GetEventsL( 

                            TEvtEventStatus    aEventStatus,

                            RPointerArray<CEvtBasicEventInfo>&     aEventArray )

    {

	EVTUIDEBUG( "+ CEvtDatabase::GetEventsL() - Status" );

    RSqlStatement stmt;

    // Prepare the SQL statement to Get Events based on Status

	stmt.Prepare( iDb, KSelectBasicEventOnStatus );

	// Set the Status parameter in SQL statement

	TInt paramIndex = stmt.ParameterIndex( KStatus );

	stmt.BindInt( paramIndex,aEventStatus );

	// Pass the SQL statement to get events.

    GetEventsL( stmt, aEventArray );

	// Close statement

	stmt.Close();

	EVTUIDEBUG( "- CEvtDatabase::GetEventsL() - Status" );

    }

// ---------------------------------------------------------------------------

// CEvtDatabase::GetEventsL

// ---------------------------------------------------------------------------

//

void CEvtDatabase::GetEventsL( 

                            RPointerArray<CEvtBasicEventInfo>&     aEventArray )

    {

	EVTUIDEBUG( "+ CEvtDatabase::GetEventsL() - without Status" );

    RSqlStatement stmt;

    // Prepare the SQL statement to Get Events based on Status

	stmt.Prepare( iDb, KSelectBasicEvent );

	// Pass the SQL statement to get events.

    GetEventsL( stmt, aEventArray );

	// Close statement

	stmt.Close();

	EVTUIDEBUG( "- CEvtDatabase::GetEventsL() - without Status" );

    } 

// ---------------------------------------------------------------------------

// CEvtDatabase::GetEventL

// ---------------------------------------------------------------------------

//

CEvtEvent* CEvtDatabase::GetEventL( TEvtEventId      aEventId )

    {

	EVTUIDEBUG( "+ CEvtDatabase::GetEventL()" );

	RSqlStatement stmt;

	CEvtEvent* evt = NULL;

	// Prepare the SQL statement to Get Events based on Id

	stmt.Prepare( iDb, KSelectEventFromEventId );

	// Set the Id parameter in SQL statement

	TInt paramIndex=stmt.ParameterIndex( KEventId );

	stmt.BindInt( paramIndex, aEventId );

	// Constants representing the columns in Query

	TInt columnIndexStatus=0; 

	TInt columnIndexSubject=1; 

	TInt columnIndexPlace=2; 

	TInt columnIndexRadius=3; 

	TInt columnIndexDescription=4; 

	TInt columnIndexRepeat=5; 

	TInt columnIndexActionId=6;

	TInt columnIndexAction=7;  

	TInt columnIndexActionEvtId=8; 

    // Flag to loop out after the first element is found in database

    // This is not required as we maintain unique Id for every event.

    // But to ensure that we do not allocate many events object to evt,

    // this flag is defined.

    TBool flag( ETrue );

	// Set the other attributes of the Requested Event

	while( flag && stmt.Next() == KSqlAtRow )

		{

		flag = EFalse;

        evt = CEvtEvent::NewLC();

    	// Set the Id of the Requested Event

    	evt->SetEventId(aEventId);

		TInt value = stmt.ColumnInt( columnIndexStatus );

		evt->SetEventStatus( static_cast<TEvtEventStatus>(value) );

		TPtrC subject=stmt.ColumnTextL( columnIndexSubject );

		evt->SetSubjectL(subject);

		TPtrC place=stmt.ColumnTextL( columnIndexPlace );

		evt->SetPlaceL(place);

		TReal realvalue = stmt.ColumnReal( columnIndexRadius );

        evt->SetRadius( realvalue );

		TPtrC desc=stmt.ColumnTextL( columnIndexDescription );

		evt->SetDescriptionL(desc);

		value = stmt.ColumnInt( columnIndexRepeat );

        evt->SetRepeat(value);

		TInt64 value64 = stmt.ColumnInt64( columnIndexActionId );

		if( value64 != 0 )

			{			

    		// Create action instance to set for Event

    		// ownership is not passed.

    		CEvtAction* actn = CEvtAction::NewLC();

			// Set the Unique Id for Action

			actn->SetId( value64 );

			// Set Action to an Event only if there is an Action String.

			TPtrC action = stmt.ColumnTextL( columnIndexAction );

	    	actn->SetActionL( action );

    		// Set the Event Id

			TUint32 value32 = stmt.ColumnInt( columnIndexActionEvtId );

	    	actn->SetEvtId( value32 );

        	// Set the Action to Event

	    	evt->SetActionL( actn );

    		// Pop action after setting.

    		CleanupStack::Pop( actn );

			}

		// Pop the Event object

		CleanupStack::Pop( evt );

		}

	stmt.Close();

	if( flag )

        User::Leave( KErrNotFound );	

	EVTUIDEBUG( "- CEvtDatabase::GetEventL()" );

	return evt;

    } 

// ---------------------------------------------------------------------------

// CEvtDatabase::UpdateEvent

// ---------------------------------------------------------------------------

//

void CEvtDatabase::UpdateEventL( 

                            CEvtEvent&          aEvtEvent )

    {

	EVTUIDEBUG( "+ CEvtDatabase::UpdateEventL()" );

	// Update Event's Attributes.

    UpdateEventTableL( aEvtEvent );

	if( aEvtEvent.HasAction() )

		{

		aEvtEvent.Action().SetEvtId( aEvtEvent.EventId() );

		if( CheckIfActionPresentL( aEvtEvent.Action().Id() ) )

			UpdateActionL( aEvtEvent.Action() );

		else

			CreateActionL( aEvtEvent.Action() );

		}

	else

		{

			DeleteActionL( aEvtEvent.EventId() );

		}

	// Notify the database change for Modify

	iDbNotifier->DbChangedL( );

	EVTUIDEBUG( "- CEvtDatabase::UpdateEventL()" );

    } 

// ---------------------------------------------------------------------------

// CEvtDatabase::AddEventL

// ---------------------------------------------------------------------------

//

void CEvtDatabase::AddEventL( CEvtEvent&     aEvtEvent )

    {

	EVTUIDEBUG( "+ CEvtDatabase::AddEventL()" );

	RSqlStatement stmt;

	TInt paramIndex;

	// We need to create the streams to insert large texts.

	RSqlParamWriteStream subjectStream;

	RSqlParamWriteStream placeStream;

	RSqlParamWriteStream descStream;

	CleanupClosePushL( subjectStream );

	CleanupClosePushL( placeStream );

	CleanupClosePushL( descStream );

	// Prepare the SQL statement to Get Events based on Status

	stmt.Prepare( iDb, KInsertEvent );

	// Set Status

	paramIndex = stmt.ParameterIndex( KStatus );

	stmt.BindInt( paramIndex, aEvtEvent.EventStatus() );

	// Set Subject

	paramIndex = stmt.ParameterIndex( KSubject );

	if( KNullDesC() == aEvtEvent.Subject() )

		{

	    stmt.BindNull( paramIndex );

		}

	else

		{

		subjectStream.BindText( stmt, paramIndex );

		subjectStream.WriteL( aEvtEvent.Subject() );

		subjectStream.CommitL();

		}

	// Set Place

	paramIndex = stmt.ParameterIndex( KPlace );

	if( KNullDesC() == aEvtEvent.Place() )

		{

	    stmt.BindNull( paramIndex );

		}

	else

		{

		placeStream.BindText( stmt, paramIndex );

		placeStream.WriteL( aEvtEvent.Place() );

		placeStream.CommitL();

		}

	// Set Radius

	paramIndex = stmt.ParameterIndex( KRadius );

	stmt.BindReal( paramIndex, aEvtEvent.Radius() );

	// Set Description

	paramIndex = stmt.ParameterIndex( KDescription );

	if( KNullDesC() == aEvtEvent.Description() )

		{

	    stmt.BindNull( paramIndex );

		}

	else

		{

		descStream.BindText( stmt, paramIndex );

		descStream.WriteL( aEvtEvent.Description() );

		descStream.CommitL();

		}

	// Set Repeat

	paramIndex = stmt.ParameterIndex( KRepeat );

	stmt.BindInt( paramIndex, aEvtEvent.Repeat() );

    // Set Event Id

	paramIndex = stmt.ParameterIndex( KEventId );

	stmt.BindInt( paramIndex, aEvtEvent.EventId() );

	// Execute the Remove SQL statement

	User::LeaveIfError( stmt.Exec() );

	//Close the streams

	CleanupStack::PopAndDestroy( &descStream );

	CleanupStack::PopAndDestroy( &placeStream );

	CleanupStack::PopAndDestroy( &subjectStream );

	stmt.Close();

	// Insert into Action table If action is present for the event.  

	if( aEvtEvent.HasAction() )

		{

		aEvtEvent.Action().SetEvtId( aEvtEvent.EventId() );

			CreateActionL( aEvtEvent.Action() );

		}

	// Notify the database change for Insert

	iDbNotifier->DbChangedL( );

	EVTUIDEBUG( "- CEvtDatabase::AddEventL()" );

    }  

// ---------------------------------------------------------------------------

// CEvtDatabase::RemoveEventsL

// ---------------------------------------------------------------------------

//

void CEvtDatabase::RemoveEventsL( const RArray<TEvtEventId>&     aEventArray )

    {

	EVTUIDEBUG( "+ CEvtDatabase::RemoveEventsL()" ); 

    // Removing an Event in database should be done in an order of

    // - remove Action entry and then remove Event entry