|
1 DROP TABLE IF EXISTS CA_ENTRY; |
|
2 DROP TABLE IF EXISTS CA_GROUP_ENTRY; |
|
3 DROP TABLE IF EXISTS CA_ICON; |
|
4 DROP TABLE IF EXISTS CA_LAUNCH; |
|
5 DROP TABLE IF EXISTS CA_ATTRIBUTE; |
|
6 DROP TABLE IF EXISTS CA_LOCALIZATION_TEXT; |
|
7 DROP TABLE IF EXISTS CA_DB_PROPERTIES; |
|
8 |
|
9 SELECT "CREATE TABLE CA_ENTRY" AS " "; |
|
10 SELECT "------------------------------------" AS " "; |
|
11 CREATE TABLE CA_ENTRY( ENTRY_ID INTEGER PRIMARY KEY, |
|
12 EN_TEXT TEXT NOT NULL COLLATE NOCASE, |
|
13 EN_DESCRIPTION TEXT COLLATE NOCASE, |
|
14 EN_ROLE INTEGER default 1, |
|
15 EN_TYPE_NAME TEXT NOT NULL, |
|
16 EN_ICON_ID INTEGER, |
|
17 EN_CREATION_TIME BIGINT default -1 NOT NULL, |
|
18 EN_UID INTEGER UNIQUE, |
|
19 EN_FLAGS INTEGER default 8, |
|
20 FOREIGN KEY(EN_ICON_ID) REFERENCES CA_ICON(ICON_ID) ); |
|
21 CREATE INDEX EntryText_CAIndex ON CA_ENTRY(EN_TEXT); |
|
22 CREATE INDEX EntryCreationTime_CAIndex ON CA_ENTRY(EN_CREATION_TIME); |
|
23 |
|
24 SELECT "CREATE TABLE CA_GROUP_ENTRY" AS " "; |
|
25 SELECT "------------------------------------" AS " "; |
|
26 CREATE TABLE CA_GROUP_ENTRY( GROUP_ENTRY_ID INTEGER PRIMARY KEY , |
|
27 GE_GROUP_ID INTEGER NOT NULL, |
|
28 GE_ENTRY_ID INTEGER NOT NULL, |
|
29 GE_POSITION INTEGER, |
|
30 UNIQUE( GE_GROUP_ID, GE_ENTRY_ID ), |
|
31 FOREIGN KEY(GE_GROUP_ID) REFERENCES CA_ENTRY(ENTRY_ID), |
|
32 FOREIGN KEY(GE_ENTRY_ID) REFERENCES CA_ENTRY(ENTRY_ID) ); |
|
33 CREATE INDEX GroupGE_POSITION_CAIndex ON CA_GROUP_ENTRY(GE_POSITION); |
|
34 CREATE TRIGGER update_position_in_ca_group_entry_after_remove AFTER DELETE ON CA_GROUP_ENTRY |
|
35 BEGIN |
|
36 UPDATE CA_GROUP_ENTRY SET GE_POSITION = GE_POSITION - 1 WHERE GE_POSITION > old.GE_POSITION AND GE_GROUP_ID = old.GE_GROUP_ID; |
|
37 END; |
|
38 CREATE TRIGGER update_position_in_ca_group_entry_before_insert BEFORE INSERT ON CA_GROUP_ENTRY |
|
39 BEGIN |
|
40 UPDATE CA_GROUP_ENTRY SET GE_POSITION = GE_POSITION + 1 WHERE GE_POSITION >= new.GE_POSITION AND GE_GROUP_ID = new.GE_GROUP_ID; |
|
41 END; |
|
42 |
|
43 SELECT "CREATE TABLE CA_ICON" AS " "; |
|
44 SELECT "------------------------------------" AS " "; |
|
45 CREATE TABLE CA_ICON( ICON_ID INTEGER PRIMARY KEY , |
|
46 IC_FILENAME TEXT, |
|
47 IC_SKIN_ID TEXT, |
|
48 IC_APP_ID TEXT, |
|
49 UNIQUE( IC_FILENAME, IC_SKIN_ID, IC_APP_ID ) ); |
|
50 |
|
51 SELECT "CREATE TABLE CA_LAUNCH" AS " "; |
|
52 SELECT "------------------------------------" AS " "; |
|
53 CREATE TABLE CA_LAUNCH( LAUNCH_ID INTEGER PRIMARY KEY , |
|
54 LA_ENTRY_ID INTEGER NOT NULL, |
|
55 LA_LAUNCH_TIME INTEGER NOT NULL, |
|
56 FOREIGN KEY(LA_ENTRY_ID) REFERENCES CA_ENTRY(ENTRY_ID) ); |
|
57 CREATE INDEX LaunchLaunchTime_CAIndex ON CA_LAUNCH(LA_LAUNCH_TIME); |
|
58 |
|
59 SELECT "CREATE TABLE CA_ATTRIBUTE" AS " "; |
|
60 SELECT "------------------------------------" AS " "; |
|
61 CREATE TABLE CA_ATTRIBUTE( ATTRIBUTE_ID INTEGER PRIMARY KEY , |
|
62 AT_ENTRY_ID INTEGER NOT NULL, |
|
63 AT_NAME TEXT NOT NULL, |
|
64 AT_VALUE TEXT NOT NULL, |
|
65 UNIQUE( AT_ENTRY_ID, AT_NAME ), |
|
66 FOREIGN KEY(AT_ENTRY_ID) REFERENCES CA_ENTRY(ENTRY_ID) ); |
|
67 |
|
68 SELECT "CREATE TABLE CA_LOCALIZATION_TEXT" AS " "; |
|
69 SELECT "------------------------------------" AS " "; |
|
70 CREATE TABLE CA_LOCALIZATION_TEXT( LOC_TEXT_ID INTEGER PRIMARY KEY , |
|
71 LT_TABLE_NAME TEXT , |
|
72 LT_ATTRIBUTE_NAME TEXT , |
|
73 LT_STRING_ID TEXT , |
|
74 LT_QMFILENAME TEXT, |
|
75 LT_ROW_ID INTEGER ); |
|
76 |
|
77 SELECT "CREATE TABLE CA_DB_PROPERTIES" AS " "; |
|
78 SELECT "------------------------------------" AS " "; |
|
79 CREATE TABLE CA_DB_PROPERTIES( DB_PROPERTIES_ID INTEGER PRIMARY KEY , |
|
80 DB_PROPERTY TEXT UNIQUE, |
|
81 DB_VALUE TEXT ); |
|
82 |
|
83 |
|
84 |
|
85 -- CREATE VIEW COLLECTION |
|
86 SELECT "------------------------------------" AS " "; |
|
87 SELECT "CREATE VIEW COLLECTION" AS " "; |
|
88 DROP VIEW IF EXISTS COLLECTION; |
|
89 CREATE VIEW COLLECTION |
|
90 AS SELECT |
|
91 "" AS "COLLECTION_NAME", |
|
92 "" AS "COL_LOCNAME", |
|
93 "" AS "ICON"; |
|
94 |
|
95 SELECT " CREATE TRIGGER collection_insert" AS " "; |
|
96 CREATE TRIGGER collection_insert INSTEAD OF INSERT ON COLLECTION |
|
97 BEGIN |
|
98 -- INSERT ICON |
|
99 INSERT INTO CA_ICON (IC_FILENAME) |
|
100 VALUES ( new.ICON); |
|
101 -- DELETE IF ICON IS NULL OR IF IT IS DUPLICATE |
|
102 DELETE FROM CA_ICON WHERE ICON_ID = last_insert_rowid() AND |
|
103 (IC_FILENAME IS NULL OR (SELECT ICON_ID FROM CA_ICON WHERE IC_FILENAME = new.ICON AND ICON_ID <> last_insert_rowid()) IS NOT NULL); |
|
104 -- INSERT ENTRY |
|
105 INSERT INTO CA_ENTRY (EN_TEXT, EN_ROLE, EN_TYPE_NAME, EN_FLAGS, EN_ICON_ID ) |
|
106 VALUES ( new.COLLECTION_NAME , 2, "collection", 12, (SELECT ICON_ID FROM CA_ICON WHERE IC_FILENAME = new.ICON )); |
|
107 -- LOCALIZATION |
|
108 INSERT INTO CA_LOCALIZATION_TEXT ( LT_TABLE_NAME, LT_ATTRIBUTE_NAME, LT_STRING_ID, LT_ROW_ID ) |
|
109 VALUES ( 'CA_ENTRY', 'EN_TEXT', new.COL_LOCNAME, ( SELECT last_insert_rowid() ) ); |
|
110 DELETE FROM CA_LOCALIZATION_TEXT WHERE LT_STRING_ID IS NULL AND LOC_TEXT_ID = last_insert_rowid() ; |
|
111 -- RELATION TO MAIN COLLECTION (ADD TO GROUP: ) |
|
112 INSERT INTO CA_GROUP_ENTRY (GE_GROUP_ID, GE_ENTRY_ID, GE_POSITION) |
|
113 VALUES ( (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TYPE_NAME = "menucollections") , |
|
114 (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.COLLECTION_NAME), |
|
115 (SELECT IFNULL(MAX(GE_POSITION)+1, 1) FROM CA_GROUP_ENTRY WHERE GE_GROUP_ID = (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TYPE_NAME = "menucollections")) ); |
|
116 -- ADD ATTRIBUTE |
|
117 INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE ) |
|
118 VALUES ( (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.COLLECTION_NAME AND (EN_TYPE_NAME = "collection::downloaded" OR EN_TYPE_NAME = "collection") ), "appgroup_name", new.COLLECTION_NAME ); |
|
119 END; |
|
120 |
|
121 -- CREATE VIEW ITEM_TO_COLLECTION |
|
122 SELECT "------------------------------------" AS " "; |
|
123 SELECT "CREATE VIEW ITEM_TO_COLLECTION" AS " "; |
|
124 DROP VIEW IF EXISTS ITEM_TO_COLLECTION; |
|
125 CREATE VIEW ITEM_TO_COLLECTION |
|
126 AS SELECT |
|
127 "" AS "ITEM_TYPE", |
|
128 "" AS "ITEM_NAME", |
|
129 "" AS "ICON", |
|
130 "" AS "ITEM_DEST", |
|
131 "" AS "COLLECTION_NAME"; |
|
132 |
|
133 SELECT " CREATE TRIGGER item_to_collection_insert_app" AS " "; |
|
134 CREATE TRIGGER item_to_collection_insert_app INSTEAD OF INSERT ON ITEM_TO_COLLECTION WHEN new.ITEM_TYPE="application" |
|
135 BEGIN |
|
136 -- INSERT ENTRY |
|
137 INSERT OR IGNORE INTO CA_ENTRY (EN_TEXT, EN_ROLE, EN_TYPE_NAME, EN_UID ) |
|
138 VALUES ( new.ITEM_NAME, 1, "application", new.ITEM_DEST ); |
|
139 -- RELATION TO MAIN COLLECTION (ADD TO GROUP: ) |
|
140 INSERT INTO CA_GROUP_ENTRY (GE_GROUP_ID, GE_ENTRY_ID, GE_POSITION) |
|
141 VALUES ( (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.COLLECTION_NAME) , |
|
142 (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.ITEM_NAME), |
|
143 (SELECT IFNULL(MAX(GE_POSITION)+1, 1) FROM CA_GROUP_ENTRY WHERE GE_GROUP_ID = (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.COLLECTION_NAME)) ); |
|
144 END; |
|
145 |
|
146 SELECT " CREATE TRIGGER item_to_collection_insert_url" AS " "; |
|
147 CREATE TRIGGER item_to_collection_insert_url INSTEAD OF INSERT ON ITEM_TO_COLLECTION WHEN new.ITEM_TYPE="url" |
|
148 BEGIN |
|
149 -- INSERT ICON |
|
150 INSERT OR IGNORE INTO CA_ICON (IC_FILENAME) |
|
151 VALUES ( new.ICON); |
|
152 DELETE FROM CA_ICON WHERE ICON_ID = last_insert_rowid() AND |
|
153 (IC_FILENAME IS NULL OR (SELECT ICON_ID FROM CA_ICON WHERE IC_FILENAME = new.ICON AND ICON_ID <> last_insert_rowid()) IS NOT NULL); |
|
154 -- INSERT ENTRY |
|
155 INSERT OR IGNORE INTO CA_ENTRY (EN_TEXT, EN_ROLE, EN_TYPE_NAME, EN_ICON_ID ) |
|
156 VALUES ( new.ITEM_NAME, 1, "url", (SELECT ICON_ID FROM CA_ICON WHERE IC_FILENAME = new.ICON )); |
|
157 INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE ) |
|
158 VALUES ( ( SELECT last_insert_rowid() ) ,"url",new.ITEM_DEST); |
|
159 -- RELATION TO MAIN COLLECTION (ADD TO GROUP: ) |
|
160 INSERT INTO CA_GROUP_ENTRY (GE_GROUP_ID, GE_ENTRY_ID, GE_POSITION) |
|
161 VALUES ( (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.COLLECTION_NAME) , |
|
162 (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.ITEM_NAME), |
|
163 (SELECT IFNULL(MAX(GE_POSITION)+1, 1) FROM CA_GROUP_ENTRY WHERE GE_GROUP_ID = (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.COLLECTION_NAME)) ); |
|
164 END; |
|
165 |
|
166 |
|
167 -- CREATE VIEW WIDGET _TO_COLLECTION |
|
168 SELECT "------------------------------------" AS " "; |
|
169 SELECT "CREATE VIEW WIDGET_TO_COLLECTION" AS " "; |
|
170 DROP VIEW IF EXISTS WIDGET_TO_COLLECTION; |
|
171 CREATE VIEW WIDGET_TO_COLLECTION |
|
172 AS SELECT |
|
173 "" AS "ITEM_TYPE", |
|
174 "" AS "ITEM_NAME", |
|
175 "" AS "ICON", |
|
176 "" AS "PACKAGE_UID", |
|
177 "" AS "LIBRARY", |
|
178 "" AS "URI", |
|
179 "" AS "COLLECTION_NAME" ; |
|
180 |
|
181 SELECT " CREATE TRIGGER insert_widget_to_collection" AS " "; |
|
182 CREATE TRIGGER item_to_collection_insert_widget INSTEAD OF INSERT ON WIDGET_TO_COLLECTION WHEN new.ITEM_TYPE="widget" |
|
183 BEGIN |
|
184 -- INSERT ICON |
|
185 INSERT OR IGNORE INTO CA_ICON (IC_FILENAME) |
|
186 VALUES ( new.ICON); |
|
187 DELETE FROM CA_ICON WHERE ICON_ID = last_insert_rowid() AND |
|
188 (IC_FILENAME IS NULL OR (SELECT ICON_ID FROM CA_ICON WHERE IC_FILENAME = new.ICON AND ICON_ID <> last_insert_rowid()) IS NOT NULL); |
|
189 -- INSERT ENTRY |
|
190 INSERT OR IGNORE INTO CA_ENTRY (EN_TEXT, EN_ROLE, EN_TYPE_NAME, EN_ICON_ID ) |
|
191 VALUES ( new.ITEM_NAME, 1, "widget", (SELECT ICON_ID FROM CA_ICON WHERE IC_FILENAME = new.ICON )); |
|
192 -- INSERT ATTRIBUTES |
|
193 INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE ) |
|
194 VALUES ( ( SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.ITEM_NAME ) ,"packageuid",new.PACKAGE_UID); |
|
195 INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE ) |
|
196 VALUES ( ( SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.ITEM_NAME ) ,"widget:library",new.LIBRARY); |
|
197 INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE ) |
|
198 VALUES ( ( SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.ITEM_NAME ) ,"widget:uri",new.URI); |
|
199 -- RELATION TO MAIN COLLECTION (ADD TO GROUP: ) |
|
200 INSERT INTO CA_GROUP_ENTRY (GE_GROUP_ID, GE_ENTRY_ID, GE_POSITION) |
|
201 VALUES ( (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.COLLECTION_NAME) , |
|
202 (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.ITEM_NAME), |
|
203 (SELECT IFNULL(MAX(GE_POSITION)+1, 1) FROM CA_GROUP_ENTRY WHERE GE_GROUP_ID = (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.COLLECTION_NAME)) ); |
|
204 END; |
|
205 |
|
206 |
|
207 |
|
208 |
|
209 SELECT "INSERT INITIAL DATA TO CA_DB_PROPERTIES" AS " "; |
|
210 |
|
211 SELECT "------------------------------------" AS " "; |
|
212 INSERT INTO CA_DB_PROPERTIES ( DB_PROPERTY, DB_VALUE ) |
|
213 VALUES ( 'Version', '00001' ); |
|
214 INSERT INTO CA_DB_PROPERTIES ( DB_PROPERTY, DB_VALUE ) |
|
215 VALUES ( 'Language', 'en_GB' ); |
|
216 INSERT INTO CA_DB_PROPERTIES ( DB_PROPERTY, DB_VALUE ) |
|
217 VALUES ( 'QMfile', 'db_textmap_' ); |
|
218 |
|
219 |
|
220 |
|
221 |
|
222 SELECT "------------------------------------" AS " "; |
|
223 SELECT "castorage.db_create - END" AS " "; |
|
224 SELECT "------------------------------------" AS " "; |
|
225 |