/*
* 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:  Constants for project Storage API
*
*/


#ifndef EVTCONSTS_H
#define EVTCONSTS_H

//-- Database Names
_LIT(KEventsDbSecureFName, "c:[2001E663]EvtStorage.db");

//-- Config 
_LIT8(KServerConfigString, ";cache_size =  1024 ;page_size =1024 ;encoding =  \"UTF-8\" ");

//===============================
// Create Table:
//===============================
_LIT8(KCreateEventTable, "\
CREATE TABLE Event(\
EventId INTEGER,\
Status INTEGER,\
Subject VARCHAR(160) DEFAULT NULL,\
Place VARCHAR(160) DEFAULT NULL,\
Radius REAL,\
Description VARCHAR(160) DEFAULT NULL,\
Repeat BOOLEAN)\
");

_LIT8(KCreateActionTable, "\
CREATE TABLE Action(\
Id INTEGER PRIMARY KEY,\
Action VARCHAR(160) DEFAULT NULL,\
EvtId INTEGER)\
");
//===============================
// Indexes:
//===============================
_LIT8(KCreateEventStatusSubjectIndex, "CREATE INDEX EventStatus ON Event (Status)");
_LIT8(KCreateEventEvtIdIndex, "CREATE INDEX EventEvtID ON Event (EventId)");
_LIT8(KCreateActionEvtIdIndex, "CREATE INDEX ActionID ON Action (EvtId)");

//===============================
// Trigger: for Action and Place
//===============================
//-- Foreign Key Preventing insert
_LIT8(KCreateTriggerActionEventInsert, "\
CREATE TRIGGER fki_Action_EvtId_Event_EventId\
BEFORE INSERT ON [Action]\
FOR EACH ROW BEGIN\
  SELECT RAISE(ROLLBACK, 'insert on table \"Action\" violates foreign key constraint \"fki_Action_EvtId_Event_EventId\"')\
  WHERE NEW.EvtId IS NOT NULL AND (SELECT EventId FROM Event WHERE EventId = NEW.EvtId) IS NULL;\
END;\
");

//-- Foreign key preventing update
_LIT8(KCreateTriggerActionEventUpdate, "\
CREATE TRIGGER fku_Action_EvtId_Event_EventId\
BEFORE UPDATE ON [Action] \
FOR EACH ROW BEGIN\
    SELECT RAISE(ROLLBACK, 'update on table \"Action\" violates foreign key constraint \"fku_Action_EvtId_Event_EventId\"')\
      WHERE NEW.EvtId IS NOT NULL AND (SELECT EventId FROM Event WHERE EventId = NEW.EvtId) IS NULL;\
END;\
");

//-- Cascading Delete
_LIT8(KCreateTriggerActionEventDelete, "\
CREATE TRIGGER fkdc_Action_EvtId_Event_EventId\
BEFORE DELETE ON Event \
FOR EACH ROW BEGIN\
    DELETE FROM Action WHERE Action.EvtId = OLD.EventId;\
END;\
");

//-- Drop Trigger
_LIT8(KDropTriggerActionEventInsert, "\
DROP TRIGGER fki_Action_EvtId_Event_EventId;\
");
_LIT8(KDropTriggerActionEventUpdate, "\
DROP TRIGGER fku_Action_EvtId_Event_EventId;\
");
_LIT8(KDropTriggerActionEventDelete, "\
DROP TRIGGER fkdc_Action_EvtId_Event_EventId;\
");

//===============================
// Queries: 
//===============================

// Define Literals that are used in Query
_LIT(KId, ":IdVal");
_LIT(KEventId, ":EventIdVal");
_LIT(KStatus, ":StatusVal");
_LIT(KSubject, ":SubjectVal");
_LIT(KPlace, ":PlaceVal");
_LIT(KRadius, ":RadiusVal");
_LIT(KUnit, ":UnitVal");
_LIT(KDescription, ":DescVal");
_LIT(KRepeat, ":RepeatVal");
_LIT(KAction, ":ActionVal");
_LIT(KEvtId, ":EvtIdVal");

// Query to select Event based on LBT's event Id
_LIT8(KSelectEventFromEventId, "SELECT Event.Status,Event.Subject,Event.Place,Event.Radius,Event.Description,Event.Repeat,Action.Id,Action.Action,Action.EvtId FROM Event INNER JOIN Action ON (Event.EventId=:EventIdVal AND Event.EventId=Action.EvtId);");

// Query to select All Events - Only Basic Information
_LIT8(KSelectBasicEvent, "SELECT Event.EventId,Event.Status,Event.Subject,Event.Place,Event.Repeat FROM Event ORDER BY Status,Subject COLLATE CompareC3;");

// Query to select All Events based on Status - Only Basic Information
_LIT8(KSelectBasicEventOnStatus, "SELECT Event.EventId,Event.Status,Event.Subject,Event.Place,Event.Repeat FROM Event WHERE Event.Status=:StatusVal ORDER BY Event.Subject COLLATE CompareC3;");

// Query to select All Events based on Status
_LIT8(KSelectEventOnStatus, "SELECT Event.EventId,Event.Status,Event.Subject,Event.Place,Event.Radius,Event.Description,Event.Repeat,Action.Action,Action.Id,Action.EvtId FROM Event INNER JOIN Action ON (Event.EventId=Action.EvtId) WHERE Event.Status=:StatusVal ORDER BY Event.Subject COLLATE CompareC3;");

// Query to check the prsence of Event
_LIT8(KSelectEvent, "SELECT EventId FROM Event WHERE Event.EventId=:EventIdVal;");

// Query to select All Action based on Status
_LIT8(KSelectAction, "SELECT Id FROM Action WHERE Action.Id=:IdVal;");

// Query to select Action based on Event Id
_LIT8(KSelectActionFromEvent, "SELECT Id FROM Action WHERE Action.EvtId=:EvtIdVal;");

// Query to Insert an Event into Event Table
_LIT8(KInsertEvent, "INSERT INTO Event(EventId,Status,Subject,Place,Radius,Description,Repeat) VALUES(:EventIdVal,:StatusVal,:SubjectVal,:PlaceVal,:RadiusVal,:DescVal,:RepeatVal)");

// Query to Insert an Action into Action Table
_LIT8(KInsertAction, "INSERT INTO Action(Action,EvtId) VALUES(:ActionVal,:EvtIdVal)");

// Queries to Inserts using TSqlRowSetUtil
_LIT(KInsertEventRowDesc, "INSERT INTO Event(EventId) VALUES(%ld)"); // Not Used
_LIT(KInsertActionRowDesc, "INSERT INTO Action(EvtId) VALUES(%d)");
_LIT(KInsertEventDesc, "INSERT INTO Event(EventId,Status,Subject,Place,Radius,Description,Repeat) VALUES(%ld,%d,'%S','%S',%f,'%S',%d)");
_LIT(KInsertActionDesc, "INSERT INTO Action(Action,EvtId) VALUES('%S',%d)");

// Query to Update Event table based on Event/Trigger Id
_LIT8(KUpdateEvent, "UPDATE Event SET Status=:StatusVal,Subject=:SubjectVal,Place=:PlaceVal,Radius=:RadiusVal,Description=:DescVal,Repeat=:RepeatVal WHERE EventId=:EventIdVal");

// Query to Update Action table based on Event/Trigger Id
_LIT8(KUpdateAction, "UPDATE Action SET Action=:ActionVal,EvtId=:EvtIdVal WHERE Id=:IdVal");

// Query to Update Event status based on Event/Trigger Id
_LIT8(KUpdateEventStatus, "UPDATE Event SET Status=:StatusVal WHERE EventId=:EventIdVal");

// Query to Delete Action based on Event/Trigger Id
_LIT8(KDeleteAction, "DELETE FROM Action WHERE Action.EvtId=:EvtIdVal");

// Query to Delete Event based on Event/Trigger Id
_LIT8(KDeleteEvent, "DELETE FROM Event WHERE Event.EventId=:EventIdVal");

// -- New Strings for Delete Multiple
// OR String
_LIT8(KOr, " OR ");

// AND String
_LIT8(KAnd, " AND ");

// AND String
_LIT8(KWhere, " WHERE ");

// Action String Id
_LIT8(KMultiActionId, "Action.EvtId = ");

// Event String Id
_LIT8(KMultiEventId, "Event.EventId = ");

// Query to Delete Action based on Event/Trigger Id
_LIT8(KDeleteMultiAction, "DELETE FROM Action WHERE ");

// Query to Delete Event based on Event/Trigger Id
_LIT8(KDeleteMultiEvent, "DELETE FROM Event WHERE ");

// Query to Delete Action based on Event/Trigger Id
_LIT8(KUpdateMultiStatus, "UPDATE Event SET Status = ");

#endif      // EVTCONSTS_H

// End of File