97 DROP VIEW IF EXISTS COLLECTION; |
97 DROP VIEW IF EXISTS COLLECTION; |
98 CREATE VIEW COLLECTION |
98 CREATE VIEW COLLECTION |
99 AS SELECT |
99 AS SELECT |
100 "" AS "COLLECTION_NAME", |
100 "" AS "COLLECTION_NAME", |
101 "" AS "COL_LOCNAME", |
101 "" AS "COL_LOCNAME", |
|
102 "" AS "COL_SHORT_NAME", |
|
103 "" AS "COL_TITLE_NAME", |
102 "" AS "COL_APP_GROUP_NAME", |
104 "" AS "COL_APP_GROUP_NAME", |
103 "" AS "ICON_FILENAME", |
105 "" AS "ICON_FILENAME", |
104 "" AS "ICON_SKIN_ID", |
106 "" AS "ICON_SKIN_ID", |
105 "" AS "FLAGS"; |
107 "" AS "FLAGS"; |
106 |
108 |
125 -- RELATION TO MAIN COLLECTION (ADD TO GROUP: ) |
127 -- RELATION TO MAIN COLLECTION (ADD TO GROUP: ) |
126 INSERT INTO CA_GROUP_ENTRY (GE_GROUP_ID, GE_ENTRY_ID, GE_POSITION) |
128 INSERT INTO CA_GROUP_ENTRY (GE_GROUP_ID, GE_ENTRY_ID, GE_POSITION) |
127 VALUES ( (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TYPE_NAME = "menucollections") , |
129 VALUES ( (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TYPE_NAME = "menucollections") , |
128 (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.COLLECTION_NAME), |
130 (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.COLLECTION_NAME), |
129 (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")) ); |
131 (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")) ); |
130 -- ADD ATTRIBUTE |
132 -- ADD ATTRIBUTES |
131 INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE ) |
133 INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE ) |
132 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 ); |
134 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 ); |
|
135 INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE ) |
|
136 VALUES ( (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.COLLECTION_NAME AND (EN_TYPE_NAME = "collection::downloaded" OR EN_TYPE_NAME = "collection") ), "short_name", new.COL_SHORT_NAME ); |
|
137 INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE ) |
|
138 VALUES ( (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.COLLECTION_NAME AND (EN_TYPE_NAME = "collection::downloaded" OR EN_TYPE_NAME = "collection") ), "title_name", new.COL_TITLE_NAME ); |
133 END; |
139 END; |
134 |
140 |
135 -- CREATE VIEW ITEM_TO_COLLECTION |
141 -- CREATE VIEW ITEM_TO_COLLECTION |
136 SELECT "------------------------------------" AS " "; |
142 SELECT "------------------------------------" AS " "; |
137 SELECT "CREATE VIEW ITEM_TO_COLLECTION" AS " "; |
143 SELECT "CREATE VIEW ITEM_TO_COLLECTION" AS " "; |
138 DROP VIEW IF EXISTS ITEM_TO_COLLECTION; |
144 DROP VIEW IF EXISTS ITEM_TO_COLLECTION; |
139 CREATE VIEW ITEM_TO_COLLECTION |
145 CREATE VIEW ITEM_TO_COLLECTION |
140 AS SELECT |
146 AS SELECT |
141 "" AS "ITEM_TYPE", |
147 "" AS "ITEM_TYPE", |
142 "" AS "ITEM_NAME", |
148 "" AS "ITEM_NAME", |
|
149 "" AS "ITEM_SHORTNAME", |
143 "" AS "ICON_FILENAME", |
150 "" AS "ICON_FILENAME", |
144 "" AS "ICON_SKIN_ID", |
151 "" AS "ICON_SKIN_ID", |
145 "" AS "ITEM_DEST", |
152 "" AS "ITEM_DEST", |
146 "" AS "COLLECTION_NAME"; |
153 "" AS "COLLECTION_NAME"; |
147 |
154 |
169 (SELECT ICON_ID FROM CA_ICON WHERE IC_FILENAME = new.ICON_FILENAME AND ICON_ID <> last_insert_rowid()) IS NOT NULL OR |
176 (SELECT ICON_ID FROM CA_ICON WHERE IC_FILENAME = new.ICON_FILENAME AND ICON_ID <> last_insert_rowid()) IS NOT NULL OR |
170 (SELECT ICON_ID FROM CA_ICON WHERE IC_SKIN_ID = new.ICON_SKIN_ID AND ICON_ID <> last_insert_rowid()) IS NOT NULL); |
177 (SELECT ICON_ID FROM CA_ICON WHERE IC_SKIN_ID = new.ICON_SKIN_ID AND ICON_ID <> last_insert_rowid()) IS NOT NULL); |
171 -- INSERT ENTRY |
178 -- INSERT ENTRY |
172 INSERT OR IGNORE INTO CA_ENTRY (EN_TEXT, EN_ROLE, EN_TYPE_NAME, EN_ICON_ID ) |
179 INSERT OR IGNORE INTO CA_ENTRY (EN_TEXT, EN_ROLE, EN_TYPE_NAME, EN_ICON_ID ) |
173 VALUES ( new.ITEM_NAME, 1, "url", (SELECT ICON_ID FROM CA_ICON WHERE IC_FILENAME = new.ICON_FILENAME OR IC_SKIN_ID = new.ICON_SKIN_ID )); |
180 VALUES ( new.ITEM_NAME, 1, "url", (SELECT ICON_ID FROM CA_ICON WHERE IC_FILENAME = new.ICON_FILENAME OR IC_SKIN_ID = new.ICON_SKIN_ID )); |
174 INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE ) |
181 -- ADD ATTRIBUTES |
175 VALUES ( ( SELECT last_insert_rowid() ) ,"url",new.ITEM_DEST); |
182 INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE ) |
|
183 VALUES ( ( SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.ITEM_NAME ) ,"url",new.ITEM_DEST ); |
|
184 INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE ) |
|
185 VALUES ( ( SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.ITEM_NAME ) ,"short_name", new.ITEM_SHORTNAME ); |
176 -- RELATION TO MAIN COLLECTION (ADD TO GROUP: ) |
186 -- RELATION TO MAIN COLLECTION (ADD TO GROUP: ) |
177 INSERT INTO CA_GROUP_ENTRY (GE_GROUP_ID, GE_ENTRY_ID, GE_POSITION) |
187 INSERT INTO CA_GROUP_ENTRY (GE_GROUP_ID, GE_ENTRY_ID, GE_POSITION) |
178 VALUES ( (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.COLLECTION_NAME) , |
188 VALUES ( (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.COLLECTION_NAME) , |
179 (SELECT ENTRY_ID FROM CA_ENTRY WHERE (EN_TEXT = new.ITEM_NAME AND EN_TYPE_NAME = new.ITEM_TYPE )), |
189 (SELECT ENTRY_ID FROM CA_ENTRY WHERE (EN_TEXT = new.ITEM_NAME AND EN_TYPE_NAME = new.ITEM_TYPE )), |
180 (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)) ); |
190 (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)) ); |