|
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_ROW_ID INTEGER ); |
|
75 |
|
76 SELECT "CREATE TABLE CA_DB_PROPERTIES" AS " "; |
|
77 SELECT "------------------------------------" AS " "; |
|
78 CREATE TABLE CA_DB_PROPERTIES( DB_PROPERTIES_ID INTEGER PRIMARY KEY , |
|
79 DB_PROPERTY TEXT UNIQUE, |
|
80 DB_VALUE TEXT ); |
|
81 |
|
82 |
|
83 |
|
84 -- CREATE VIEW COLLECTION |
|
85 SELECT "------------------------------------" AS " "; |
|
86 SELECT "CREATE VIEW COLLECTION" AS " "; |
|
87 DROP VIEW IF EXISTS COLLECTION; |
|
88 CREATE VIEW COLLECTION |
|
89 AS SELECT |
|
90 "" AS "COLLECTION_NAME", |
|
91 "" AS "COL_LOCNAME", |
|
92 "" AS "ICON"; |
|
93 |
|
94 SELECT " CREATE TRIGGER collection_insert" AS " "; |
|
95 CREATE TRIGGER collection_insert INSTEAD OF INSERT ON COLLECTION |
|
96 BEGIN |
|
97 -- INSERT ICON |
|
98 INSERT INTO CA_ICON (IC_FILENAME) |
|
99 VALUES ( new.ICON); |
|
100 -- DELETE IF ICON IS NULL OR IF IT IS DUPLICATE |
|
101 DELETE FROM CA_ICON WHERE ICON_ID = last_insert_rowid() AND |
|
102 (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); |
|
103 -- INSERT ENTRY |
|
104 INSERT INTO CA_ENTRY (EN_TEXT, EN_ROLE, EN_TYPE_NAME, EN_FLAGS, EN_ICON_ID ) |
|
105 VALUES ( new.COLLECTION_NAME , 2, "collection", 12, (SELECT ICON_ID FROM CA_ICON WHERE IC_FILENAME = new.ICON )); |
|
106 -- LOCALIZATION |
|
107 INSERT INTO CA_LOCALIZATION_TEXT ( LT_TABLE_NAME, LT_ATTRIBUTE_NAME, LT_STRING_ID, LT_ROW_ID ) |
|
108 VALUES ( 'CA_ENTRY', 'EN_TEXT', new.COL_LOCNAME, ( SELECT last_insert_rowid() ) ); |
|
109 DELETE FROM CA_LOCALIZATION_TEXT WHERE LT_STRING_ID IS NULL AND LOC_TEXT_ID = last_insert_rowid() ; |
|
110 -- RELATION TO MAIN COLLECTION (ADD TO GROUP: ) |
|
111 INSERT INTO CA_GROUP_ENTRY (GE_GROUP_ID, GE_ENTRY_ID, GE_POSITION) |
|
112 VALUES ( (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TYPE_NAME = "menucollections") , |
|
113 (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.COLLECTION_NAME), |
|
114 (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")) ); |
|
115 END; |
|
116 |
|
117 -- CREATE VIEW ITEM_TO_COLLECTION |
|
118 SELECT "------------------------------------" AS " "; |
|
119 SELECT "CREATE VIEW ITEM_TO_COLLECTION" AS " "; |
|
120 DROP VIEW IF EXISTS ITEM_TO_COLLECTION; |
|
121 CREATE VIEW ITEM_TO_COLLECTION |
|
122 AS SELECT |
|
123 "" AS "ITEM_TYPE", |
|
124 "" AS "ITEM_NAME", |
|
125 "" AS "ICON", |
|
126 "" AS "ITEM_DEST", |
|
127 "" AS "COLLECTION_NAME"; |
|
128 |
|
129 SELECT " CREATE TRIGGER item_to_collection_insert_app" AS " "; |
|
130 CREATE TRIGGER item_to_collection_insert_app INSTEAD OF INSERT ON ITEM_TO_COLLECTION WHEN new.ITEM_TYPE="application" |
|
131 BEGIN |
|
132 -- INSERT ENTRY |
|
133 INSERT OR IGNORE INTO CA_ENTRY (EN_TEXT, EN_ROLE, EN_TYPE_NAME, EN_UID ) |
|
134 VALUES ( new.ITEM_NAME, 1, "application", new.ITEM_DEST ); |
|
135 -- RELATION TO MAIN COLLECTION (ADD TO GROUP: ) |
|
136 INSERT INTO CA_GROUP_ENTRY (GE_GROUP_ID, GE_ENTRY_ID, GE_POSITION) |
|
137 VALUES ( (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.COLLECTION_NAME) , |
|
138 (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.ITEM_NAME), |
|
139 (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)) ); |
|
140 END; |
|
141 |
|
142 SELECT " CREATE TRIGGER item_to_collection_insert_url" AS " "; |
|
143 CREATE TRIGGER item_to_collection_insert_url INSTEAD OF INSERT ON ITEM_TO_COLLECTION WHEN new.ITEM_TYPE="url" |
|
144 BEGIN |
|
145 -- INSERT ICON |
|
146 INSERT OR IGNORE INTO CA_ICON (IC_FILENAME) |
|
147 VALUES ( new.ICON); |
|
148 DELETE FROM CA_ICON WHERE ICON_ID = last_insert_rowid() AND |
|
149 (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); |
|
150 -- INSERT ENTRY |
|
151 INSERT OR IGNORE INTO CA_ENTRY (EN_TEXT, EN_ROLE, EN_TYPE_NAME, EN_ICON_ID ) |
|
152 VALUES ( new.ITEM_NAME, 1, "url", (SELECT ICON_ID FROM CA_ICON WHERE IC_FILENAME = new.ICON )); |
|
153 INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE ) |
|
154 VALUES ( ( SELECT last_insert_rowid() ) ,"url",new.ITEM_DEST); |
|
155 -- RELATION TO MAIN COLLECTION (ADD TO GROUP: ) |
|
156 INSERT INTO CA_GROUP_ENTRY (GE_GROUP_ID, GE_ENTRY_ID, GE_POSITION) |
|
157 VALUES ( (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.COLLECTION_NAME) , |
|
158 (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.ITEM_NAME), |
|
159 (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)) ); |
|
160 END; |
|
161 |
|
162 |
|
163 -- CREATE VIEW WIDGET _TO_COLLECTION |
|
164 SELECT "------------------------------------" AS " "; |
|
165 SELECT "CREATE VIEW WIDGET_TO_COLLECTION" AS " "; |
|
166 DROP VIEW IF EXISTS WIDGET_TO_COLLECTION; |
|
167 CREATE VIEW WIDGET_TO_COLLECTION |
|
168 AS SELECT |
|
169 "" AS "ITEM_TYPE", |
|
170 "" AS "ITEM_NAME", |
|
171 "" AS "ICON", |
|
172 "" AS "PACKAGE_UID", |
|
173 "" AS "LIBRARY", |
|
174 "" AS "LONG_NAME", |
|
175 "" AS "URI", |
|
176 "" AS "COLLECTION_NAME" ; |
|
177 |
|
178 SELECT " CREATE TRIGGER insert_widget_to_collection" AS " "; |
|
179 CREATE TRIGGER item_to_collection_insert_widget INSTEAD OF INSERT ON WIDGET_TO_COLLECTION WHEN new.ITEM_TYPE="widget" |
|
180 BEGIN |
|
181 -- INSERT ICON |
|
182 INSERT OR IGNORE INTO CA_ICON (IC_FILENAME) |
|
183 VALUES ( new.ICON); |
|
184 DELETE FROM CA_ICON WHERE ICON_ID = last_insert_rowid() AND |
|
185 (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); |
|
186 -- INSERT ENTRY |
|
187 INSERT OR IGNORE INTO CA_ENTRY (EN_TEXT, EN_ROLE, EN_TYPE_NAME, EN_ICON_ID ) |
|
188 VALUES ( new.ITEM_NAME, 1, "widget", (SELECT ICON_ID FROM CA_ICON WHERE IC_FILENAME = new.ICON )); |
|
189 -- INSERT ATTRIBUTES |
|
190 INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE ) |
|
191 VALUES ( ( SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.ITEM_NAME ) ,"packageuid",new.PACKAGE_UID); |
|
192 INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE ) |
|
193 VALUES ( ( SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.ITEM_NAME ) ,"widget:library",new.LIBRARY); |
|
194 INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE ) |
|
195 VALUES ( ( SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.ITEM_NAME ) ,"long_name",new.LONG_NAME); |
|
196 INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE ) |
|
197 VALUES ( ( SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.ITEM_NAME ) ,"widget:uri",new.URI); |
|
198 -- RELATION TO MAIN COLLECTION (ADD TO GROUP: ) |
|
199 INSERT INTO CA_GROUP_ENTRY (GE_GROUP_ID, GE_ENTRY_ID, GE_POSITION) |
|
200 VALUES ( (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.COLLECTION_NAME) , |
|
201 (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.ITEM_NAME), |
|
202 (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)) ); |
|
203 END; |
|
204 |
|
205 |
|
206 |
|
207 |
|
208 SELECT "INSERT INITIAL DATA TO CA_DB_PROPERTIES" AS " "; |
|
209 |
|
210 SELECT "------------------------------------" AS " "; |
|
211 INSERT INTO CA_DB_PROPERTIES ( DB_PROPERTY, DB_VALUE ) |
|
212 VALUES ( 'Version', '00001' ); |
|
213 INSERT INTO CA_DB_PROPERTIES ( DB_PROPERTY, DB_VALUE ) |
|
214 VALUES ( 'Language', '' ); |
|
215 INSERT INTO CA_DB_PROPERTIES ( DB_PROPERTY, DB_VALUE ) |
|
216 VALUES ( 'QMfile', 'db_textmap_' ); |
|
217 |
|
218 |
|
219 |
|
220 |
|
221 SELECT "------------------------------------" AS " "; |
|
222 SELECT "castoragedb_create - END" AS " "; |
|
223 SELECT "------------------------------------" AS " "; |
|
224 |