85 SELECT "------------------------------------" AS " "; |
85 SELECT "------------------------------------" AS " "; |
86 SELECT "CREATE VIEW COLLECTION" AS " "; |
86 SELECT "CREATE VIEW COLLECTION" AS " "; |
87 DROP VIEW IF EXISTS COLLECTION; |
87 DROP VIEW IF EXISTS COLLECTION; |
88 CREATE VIEW COLLECTION |
88 CREATE VIEW COLLECTION |
89 AS SELECT |
89 AS SELECT |
90 "" AS "COLLECTION_NAME", |
90 "" AS "COLLECTION_NAME", |
91 "" AS "COL_LOCNAME", |
91 "" AS "COL_LOCNAME", |
|
92 "" AS "COL_APP_GROUP_NAME", |
92 "" AS "ICON"; |
93 "" AS "ICON"; |
93 |
94 |
94 SELECT " CREATE TRIGGER collection_insert" AS " "; |
95 SELECT " CREATE TRIGGER collection_insert" AS " "; |
95 CREATE TRIGGER collection_insert INSTEAD OF INSERT ON COLLECTION |
96 CREATE TRIGGER collection_insert INSTEAD OF INSERT ON COLLECTION |
96 BEGIN |
97 BEGIN |
99 VALUES ( new.ICON); |
100 VALUES ( new.ICON); |
100 -- DELETE IF ICON IS NULL OR IF IT IS DUPLICATE |
101 -- DELETE IF ICON IS NULL OR IF IT IS DUPLICATE |
101 DELETE FROM CA_ICON WHERE ICON_ID = last_insert_rowid() AND |
102 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 (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 ENTRY |
104 INSERT INTO CA_ENTRY (EN_TEXT, EN_ROLE, EN_TYPE_NAME, EN_FLAGS, EN_ICON_ID ) |
105 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 VALUES ( new.COLLECTION_NAME , 2, "collection", 4, (SELECT ICON_ID FROM CA_ICON WHERE IC_FILENAME = new.ICON )); |
106 -- LOCALIZATION |
107 -- LOCALIZATION |
107 INSERT INTO CA_LOCALIZATION_TEXT ( LT_TABLE_NAME, LT_ATTRIBUTE_NAME, LT_STRING_ID, LT_ROW_ID ) |
108 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 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 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 -- RELATION TO MAIN COLLECTION (ADD TO GROUP: ) |
111 INSERT INTO CA_GROUP_ENTRY (GE_GROUP_ID, GE_ENTRY_ID, GE_POSITION) |
112 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 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 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 (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.COL_APP_GROUP_NAME ); |
115 END; |
119 END; |
116 |
120 |
117 -- CREATE VIEW ITEM_TO_COLLECTION |
121 -- CREATE VIEW ITEM_TO_COLLECTION |
118 SELECT "------------------------------------" AS " "; |
122 SELECT "------------------------------------" AS " "; |
119 SELECT "CREATE VIEW ITEM_TO_COLLECTION" AS " "; |
123 SELECT "CREATE VIEW ITEM_TO_COLLECTION" AS " "; |
130 CREATE TRIGGER item_to_collection_insert_app INSTEAD OF INSERT ON ITEM_TO_COLLECTION WHEN new.ITEM_TYPE="application" |
134 CREATE TRIGGER item_to_collection_insert_app INSTEAD OF INSERT ON ITEM_TO_COLLECTION WHEN new.ITEM_TYPE="application" |
131 BEGIN |
135 BEGIN |
132 -- INSERT ENTRY |
136 -- INSERT ENTRY |
133 INSERT OR IGNORE INTO CA_ENTRY (EN_TEXT, EN_ROLE, EN_TYPE_NAME, EN_UID ) |
137 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 ); |
138 VALUES ( new.ITEM_NAME, 1, "application", new.ITEM_DEST ); |
135 -- RELATION TO MAIN COLLECTION (ADD TO GROUP: ) |
139 -- RELATION TO MAIN COLLECTION (ADD TO GROUP: ) |
136 INSERT INTO CA_GROUP_ENTRY (GE_GROUP_ID, GE_ENTRY_ID, GE_POSITION) |
140 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) , |
141 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), |
142 (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)) ); |
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)) ); |
140 END; |
144 END; |
150 -- INSERT ENTRY |
154 -- INSERT ENTRY |
151 INSERT OR IGNORE INTO CA_ENTRY (EN_TEXT, EN_ROLE, EN_TYPE_NAME, EN_ICON_ID ) |
155 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 )); |
156 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 ) |
157 INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE ) |
154 VALUES ( ( SELECT last_insert_rowid() ) ,"url",new.ITEM_DEST); |
158 VALUES ( ( SELECT last_insert_rowid() ) ,"url",new.ITEM_DEST); |
155 -- RELATION TO MAIN COLLECTION (ADD TO GROUP: ) |
159 -- RELATION TO MAIN COLLECTION (ADD TO GROUP: ) |
156 INSERT INTO CA_GROUP_ENTRY (GE_GROUP_ID, GE_ENTRY_ID, GE_POSITION) |
160 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) , |
161 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), |
162 (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)) ); |
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)) ); |
160 END; |
164 END; |
161 |
165 |
162 |
166 |
163 -- CREATE VIEW WIDGET _TO_COLLECTION |
167 -- CREATE VIEW WIDGET _TO_COLLECTION |
164 SELECT "------------------------------------" AS " "; |
168 SELECT "------------------------------------" AS " "; |
165 SELECT "CREATE VIEW WIDGET_TO_COLLECTION" AS " "; |
169 SELECT "CREATE VIEW WIDGET_TO_COLLECTION" AS " "; |
166 DROP VIEW IF EXISTS WIDGET_TO_COLLECTION; |
170 DROP VIEW IF EXISTS WIDGET_TO_COLLECTION; |
167 CREATE VIEW WIDGET_TO_COLLECTION |
171 CREATE VIEW WIDGET_TO_COLLECTION |
168 AS SELECT |
172 AS SELECT |
193 VALUES ( ( SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.ITEM_NAME ) ,"widget:library",new.LIBRARY); |
197 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 ) |
198 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); |
199 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 ) |
200 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); |
201 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: ) |
202 -- RELATION TO MAIN COLLECTION (ADD TO GROUP: ) |
199 INSERT INTO CA_GROUP_ENTRY (GE_GROUP_ID, GE_ENTRY_ID, GE_POSITION) |
203 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) , |
204 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), |
205 (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)) ); |
206 (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; |
207 END; |
204 |
|
205 |
|
206 |
|
207 |
208 |
208 SELECT "INSERT INITIAL DATA TO CA_DB_PROPERTIES" AS " "; |
209 SELECT "INSERT INITIAL DATA TO CA_DB_PROPERTIES" AS " "; |
209 |
210 |
210 SELECT "------------------------------------" AS " "; |
211 SELECT "------------------------------------" AS " "; |
211 INSERT INTO CA_DB_PROPERTIES ( DB_PROPERTY, DB_VALUE ) |
212 INSERT INTO CA_DB_PROPERTIES ( DB_PROPERTY, DB_VALUE ) |
213 INSERT INTO CA_DB_PROPERTIES ( DB_PROPERTY, DB_VALUE ) |
214 INSERT INTO CA_DB_PROPERTIES ( DB_PROPERTY, DB_VALUE ) |
214 VALUES ( 'Language', '' ); |
215 VALUES ( 'Language', '' ); |
215 INSERT INTO CA_DB_PROPERTIES ( DB_PROPERTY, DB_VALUE ) |
216 INSERT INTO CA_DB_PROPERTIES ( DB_PROPERTY, DB_VALUE ) |
216 VALUES ( 'QMfile', 'db_textmap_' ); |
217 VALUES ( 'QMfile', 'db_textmap_' ); |
217 |
218 |
218 |
219 -- insert neccessary items |
219 |
220 SELECT "INSERT menucollections ENTRY" AS " "; |
220 |
221 INSERT INTO CA_ENTRY (EN_TEXT, EN_ROLE, EN_TYPE_NAME, EN_ICON_ID, EN_UID ) |
|
222 VALUES ( 'AllCollections', 2, "menucollections", NULL , NULL ); |
|
223 |
|
224 INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE ) |
|
225 VALUES ( last_insert_rowid(), "appgroup_name", "AllCollections" ); |
|
226 |
|
227 SELECT "------------------------------------" AS " "; |
|
228 SELECT "INSERT Downloaded collection ENTRY" AS " "; |
|
229 INSERT INTO CA_ICON (IC_FILENAME) |
|
230 VALUES ( "qtg_large_applications_download"); |
|
231 |
|
232 INSERT INTO CA_ENTRY (EN_TEXT, EN_ROLE, EN_TYPE_NAME, EN_ICON_ID, EN_FLAGS ) |
|
233 VALUES ( 'Downloaded', 2, "collection::downloaded", last_insert_rowid() , 8 ); |
|
234 |
|
235 INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE ) |
|
236 VALUES ( last_insert_rowid(), "appgroup_name", " Downloaded" ); |
|
237 |
|
238 INSERT INTO CA_LOCALIZATION_TEXT ( LT_TABLE_NAME, LT_ATTRIBUTE_NAME, LT_STRING_ID, LT_ROW_ID ) |
|
239 VALUES ( 'CA_ENTRY', 'EN_TEXT', "txt_applib_dblist_downloaded", ( SELECT last_insert_rowid() ) ); |
|
240 |
|
241 INSERT INTO CA_GROUP_ENTRY (GE_GROUP_ID, GE_ENTRY_ID, GE_POSITION) |
|
242 VALUES ( (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TYPE_NAME = "menucollections") , |
|
243 (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TYPE_NAME = "collection::downloaded"), |
|
244 1 ); |
|
245 |
221 SELECT "------------------------------------" AS " "; |
246 SELECT "------------------------------------" AS " "; |
222 SELECT "castoragedb_create - END" AS " "; |
247 SELECT "castoragedb_create - END" AS " "; |
223 SELECT "------------------------------------" AS " "; |
248 SELECT "------------------------------------" AS " "; |
224 |
249 |