|
1 /* |
|
2 * Copyright (c) 2008 Nokia Corporation and/or its subsidiary(-ies). |
|
3 * All rights reserved. |
|
4 * This component and the accompanying materials are made available |
|
5 * under the terms of "Eclipse Public License v1.0" |
|
6 * which accompanies this distribution, and is available |
|
7 * at the URL "http://www.eclipse.org/legal/epl-v10.html". |
|
8 * |
|
9 * Initial Contributors: |
|
10 * Nokia Corporation - initial contribution. |
|
11 * |
|
12 * Contributors: |
|
13 * |
|
14 * Description: Constants for project Storage API |
|
15 * |
|
16 */ |
|
17 |
|
18 |
|
19 #ifndef EVTCONSTS_H |
|
20 #define EVTCONSTS_H |
|
21 |
|
22 //-- Database Names |
|
23 _LIT(KEventsDbSecureFName, "c:[2001E663]EvtStorage.db"); |
|
24 |
|
25 //-- Config |
|
26 _LIT8(KServerConfigString, ";cache_size = 1024 ;page_size =1024 ;encoding = \"UTF-8\" "); |
|
27 |
|
28 //=============================== |
|
29 // Create Table: |
|
30 //=============================== |
|
31 _LIT8(KCreateEventTable, "\ |
|
32 CREATE TABLE Event(\ |
|
33 EventId INTEGER,\ |
|
34 Status INTEGER,\ |
|
35 Subject VARCHAR(160) DEFAULT NULL,\ |
|
36 Place VARCHAR(160) DEFAULT NULL,\ |
|
37 Radius REAL,\ |
|
38 Description VARCHAR(160) DEFAULT NULL,\ |
|
39 Repeat BOOLEAN)\ |
|
40 "); |
|
41 |
|
42 _LIT8(KCreateActionTable, "\ |
|
43 CREATE TABLE Action(\ |
|
44 Id INTEGER PRIMARY KEY,\ |
|
45 Action VARCHAR(160) DEFAULT NULL,\ |
|
46 EvtId INTEGER)\ |
|
47 "); |
|
48 //=============================== |
|
49 // Indexes: |
|
50 //=============================== |
|
51 _LIT8(KCreateEventStatusSubjectIndex, "CREATE INDEX EventStatus ON Event (Status)"); |
|
52 _LIT8(KCreateEventEvtIdIndex, "CREATE INDEX EventEvtID ON Event (EventId)"); |
|
53 _LIT8(KCreateActionEvtIdIndex, "CREATE INDEX ActionID ON Action (EvtId)"); |
|
54 |
|
55 //=============================== |
|
56 // Trigger: for Action and Place |
|
57 //=============================== |
|
58 //-- Foreign Key Preventing insert |
|
59 _LIT8(KCreateTriggerActionEventInsert, "\ |
|
60 CREATE TRIGGER fki_Action_EvtId_Event_EventId\ |
|
61 BEFORE INSERT ON [Action]\ |
|
62 FOR EACH ROW BEGIN\ |
|
63 SELECT RAISE(ROLLBACK, 'insert on table \"Action\" violates foreign key constraint \"fki_Action_EvtId_Event_EventId\"')\ |
|
64 WHERE NEW.EvtId IS NOT NULL AND (SELECT EventId FROM Event WHERE EventId = NEW.EvtId) IS NULL;\ |
|
65 END;\ |
|
66 "); |
|
67 |
|
68 //-- Foreign key preventing update |
|
69 _LIT8(KCreateTriggerActionEventUpdate, "\ |
|
70 CREATE TRIGGER fku_Action_EvtId_Event_EventId\ |
|
71 BEFORE UPDATE ON [Action] \ |
|
72 FOR EACH ROW BEGIN\ |
|
73 SELECT RAISE(ROLLBACK, 'update on table \"Action\" violates foreign key constraint \"fku_Action_EvtId_Event_EventId\"')\ |
|
74 WHERE NEW.EvtId IS NOT NULL AND (SELECT EventId FROM Event WHERE EventId = NEW.EvtId) IS NULL;\ |
|
75 END;\ |
|
76 "); |
|
77 |
|
78 //-- Cascading Delete |
|
79 _LIT8(KCreateTriggerActionEventDelete, "\ |
|
80 CREATE TRIGGER fkdc_Action_EvtId_Event_EventId\ |
|
81 BEFORE DELETE ON Event \ |
|
82 FOR EACH ROW BEGIN\ |
|
83 DELETE FROM Action WHERE Action.EvtId = OLD.EventId;\ |
|
84 END;\ |
|
85 "); |
|
86 |
|
87 //-- Drop Trigger |
|
88 _LIT8(KDropTriggerActionEventInsert, "\ |
|
89 DROP TRIGGER fki_Action_EvtId_Event_EventId;\ |
|
90 "); |
|
91 _LIT8(KDropTriggerActionEventUpdate, "\ |
|
92 DROP TRIGGER fku_Action_EvtId_Event_EventId;\ |
|
93 "); |
|
94 _LIT8(KDropTriggerActionEventDelete, "\ |
|
95 DROP TRIGGER fkdc_Action_EvtId_Event_EventId;\ |
|
96 "); |
|
97 |
|
98 //=============================== |
|
99 // Queries: |
|
100 //=============================== |
|
101 |
|
102 // Define Literals that are used in Query |
|
103 _LIT(KId, ":IdVal"); |
|
104 _LIT(KEventId, ":EventIdVal"); |
|
105 _LIT(KStatus, ":StatusVal"); |
|
106 _LIT(KSubject, ":SubjectVal"); |
|
107 _LIT(KPlace, ":PlaceVal"); |
|
108 _LIT(KRadius, ":RadiusVal"); |
|
109 _LIT(KUnit, ":UnitVal"); |
|
110 _LIT(KDescription, ":DescVal"); |
|
111 _LIT(KRepeat, ":RepeatVal"); |
|
112 _LIT(KAction, ":ActionVal"); |
|
113 _LIT(KEvtId, ":EvtIdVal"); |
|
114 |
|
115 // Query to select Event based on LBT's event Id |
|
116 _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);"); |
|
117 |
|
118 // Query to select All Events - Only Basic Information |
|
119 _LIT8(KSelectBasicEvent, "SELECT Event.EventId,Event.Status,Event.Subject,Event.Place,Event.Repeat FROM Event ORDER BY Status,Subject COLLATE CompareC3;"); |
|
120 |
|
121 // Query to select All Events based on Status - Only Basic Information |
|
122 _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;"); |
|
123 |
|
124 // Query to select All Events based on Status |
|
125 _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;"); |
|
126 |
|
127 // Query to check the prsence of Event |
|
128 _LIT8(KSelectEvent, "SELECT EventId FROM Event WHERE Event.EventId=:EventIdVal;"); |
|
129 |
|
130 // Query to select All Action based on Status |
|
131 _LIT8(KSelectAction, "SELECT Id FROM Action WHERE Action.Id=:IdVal;"); |
|
132 |
|
133 // Query to select Action based on Event Id |
|
134 _LIT8(KSelectActionFromEvent, "SELECT Id FROM Action WHERE Action.EvtId=:EvtIdVal;"); |
|
135 |
|
136 // Query to Insert an Event into Event Table |
|
137 _LIT8(KInsertEvent, "INSERT INTO Event(EventId,Status,Subject,Place,Radius,Description,Repeat) VALUES(:EventIdVal,:StatusVal,:SubjectVal,:PlaceVal,:RadiusVal,:DescVal,:RepeatVal)"); |
|
138 |
|
139 // Query to Insert an Action into Action Table |
|
140 _LIT8(KInsertAction, "INSERT INTO Action(Action,EvtId) VALUES(:ActionVal,:EvtIdVal)"); |
|
141 |
|
142 // Queries to Inserts using TSqlRowSetUtil |
|
143 _LIT(KInsertEventRowDesc, "INSERT INTO Event(EventId) VALUES(%ld)"); // Not Used |
|
144 _LIT(KInsertActionRowDesc, "INSERT INTO Action(EvtId) VALUES(%d)"); |
|
145 _LIT(KInsertEventDesc, "INSERT INTO Event(EventId,Status,Subject,Place,Radius,Description,Repeat) VALUES(%ld,%d,'%S','%S',%f,'%S',%d)"); |
|
146 _LIT(KInsertActionDesc, "INSERT INTO Action(Action,EvtId) VALUES('%S',%d)"); |
|
147 |
|
148 // Query to Update Event table based on Event/Trigger Id |
|
149 _LIT8(KUpdateEvent, "UPDATE Event SET Status=:StatusVal,Subject=:SubjectVal,Place=:PlaceVal,Radius=:RadiusVal,Description=:DescVal,Repeat=:RepeatVal WHERE EventId=:EventIdVal"); |
|
150 |
|
151 // Query to Update Action table based on Event/Trigger Id |
|
152 _LIT8(KUpdateAction, "UPDATE Action SET Action=:ActionVal,EvtId=:EvtIdVal WHERE Id=:IdVal"); |
|
153 |
|
154 // Query to Update Event status based on Event/Trigger Id |
|
155 _LIT8(KUpdateEventStatus, "UPDATE Event SET Status=:StatusVal WHERE EventId=:EventIdVal"); |
|
156 |
|
157 // Query to Delete Action based on Event/Trigger Id |
|
158 _LIT8(KDeleteAction, "DELETE FROM Action WHERE Action.EvtId=:EvtIdVal"); |
|
159 |
|
160 // Query to Delete Event based on Event/Trigger Id |
|
161 _LIT8(KDeleteEvent, "DELETE FROM Event WHERE Event.EventId=:EventIdVal"); |
|
162 |
|
163 // -- New Strings for Delete Multiple |
|
164 // OR String |
|
165 _LIT8(KOr, " OR "); |
|
166 |
|
167 // AND String |
|
168 _LIT8(KAnd, " AND "); |
|
169 |
|
170 // AND String |
|
171 _LIT8(KWhere, " WHERE "); |
|
172 |
|
173 // Action String Id |
|
174 _LIT8(KMultiActionId, "Action.EvtId = "); |
|
175 |
|
176 // Event String Id |
|
177 _LIT8(KMultiEventId, "Event.EventId = "); |
|
178 |
|
179 // Query to Delete Action based on Event/Trigger Id |
|
180 _LIT8(KDeleteMultiAction, "DELETE FROM Action WHERE "); |
|
181 |
|
182 // Query to Delete Event based on Event/Trigger Id |
|
183 _LIT8(KDeleteMultiEvent, "DELETE FROM Event WHERE "); |
|
184 |
|
185 // Query to Delete Action based on Event/Trigger Id |
|
186 _LIT8(KUpdateMultiStatus, "UPDATE Event SET Status = "); |
|
187 |
|
188 #endif // EVTCONSTS_H |
|
189 |
|
190 // End of File |