--- a/contentstorage/castorage/data/castoragedb_create.sql Tue Jul 06 14:37:10 2010 +0300
+++ b/contentstorage/castorage/data/castoragedb_create.sql Wed Aug 18 10:05:49 2010 +0300
@@ -99,6 +99,9 @@
AS SELECT
"" AS "COLLECTION_NAME",
"" AS "COL_LOCNAME",
+ "" AS "TRANSLATION_FILE",
+ "" AS "COL_SHORT_NAME",
+ "" AS "COL_TITLE_NAME",
"" AS "COL_APP_GROUP_NAME",
"" AS "ICON_FILENAME",
"" AS "ICON_SKIN_ID",
@@ -112,24 +115,33 @@
VALUES ( new.ICON_FILENAME, new.ICON_SKIN_ID);
-- DELETE IF ICON IS NULL OR IF IT IS DUPLICATE
DELETE FROM CA_ICON WHERE ICON_ID = last_insert_rowid() AND
- ( (IC_FILENAME IS NULL AND IC_SKIN_ID IS NULL) OR
- (SELECT ICON_ID FROM CA_ICON WHERE IC_FILENAME = new.ICON_FILENAME AND ICON_ID <> last_insert_rowid()) IS NOT NULL OR
- (SELECT ICON_ID FROM CA_ICON WHERE IC_SKIN_ID = new.ICON_SKIN_ID AND ICON_ID <> last_insert_rowid()) IS NOT NULL);
+ ( (IC_FILENAME = "" AND IC_SKIN_ID ="") OR
+ (SELECT ICON_ID FROM CA_ICON WHERE IC_FILENAME = new.ICON_FILENAME AND IC_SKIN_ID = new.ICON_SKIN_ID AND ICON_ID <> last_insert_rowid()) IS NOT NULL);
-- INSERT ENTRY
INSERT INTO CA_ENTRY (EN_TEXT, EN_ROLE, EN_TYPE_NAME, EN_FLAGS, EN_ICON_ID )
- VALUES ( new.COLLECTION_NAME , 2, "collection", new.FLAGS, (SELECT ICON_ID FROM CA_ICON WHERE IC_FILENAME = new.ICON_FILENAME OR IC_SKIN_ID = new.ICON_SKIN_ID ));
+ VALUES ( new.COLLECTION_NAME , 2, "collection", new.FLAGS, (SELECT ICON_ID FROM CA_ICON WHERE IC_FILENAME = new.ICON_FILENAME AND IC_SKIN_ID = new.ICON_SKIN_ID ));
-- LOCALIZATION
INSERT INTO CA_LOCALIZATION_TEXT ( LT_TABLE_NAME, LT_ATTRIBUTE_NAME, LT_STRING_ID, LT_QMFILENAME, LT_ROW_ID )
- VALUES ( 'CA_ENTRY', 'EN_TEXT', new.COL_LOCNAME, ( SELECT DB_VALUE FROM CA_DB_PROPERTIES WHERE DB_PROPERTY = "QMfile" ), ( SELECT last_insert_rowid() ) );
- DELETE FROM CA_LOCALIZATION_TEXT WHERE LT_STRING_ID IS NULL AND LOC_TEXT_ID = last_insert_rowid() ;
+ VALUES ( 'CA_ENTRY', 'EN_TEXT', new.COLLECTION_NAME, new.TRANSLATION_FILE, ( SELECT MAX(ENTRY_ID) FROM CA_ENTRY ) );
+ DELETE FROM CA_LOCALIZATION_TEXT WHERE LT_QMFILENAME IS NULL AND LOC_TEXT_ID = last_insert_rowid() ;
+ INSERT INTO CA_LOCALIZATION_TEXT ( LT_TABLE_NAME, LT_ATTRIBUTE_NAME, LT_STRING_ID, LT_QMFILENAME, LT_ROW_ID )
+ VALUES ( 'CA_ATTRIBUTE', 'short_name', new.COL_SHORT_NAME, new.TRANSLATION_FILE, ( SELECT MAX(ENTRY_ID) FROM CA_ENTRY ) );
+ DELETE FROM CA_LOCALIZATION_TEXT WHERE ( LT_QMFILENAME IS NULL OR LT_STRING_ID IS NULL ) AND LOC_TEXT_ID = last_insert_rowid();
+ INSERT INTO CA_LOCALIZATION_TEXT ( LT_TABLE_NAME, LT_ATTRIBUTE_NAME, LT_STRING_ID, LT_QMFILENAME, LT_ROW_ID )
+ VALUES ( 'CA_ATTRIBUTE', 'title_name', new.COL_TITLE_NAME, new.TRANSLATION_FILE, ( SELECT MAX(ENTRY_ID) FROM CA_ENTRY ) );
+ DELETE FROM CA_LOCALIZATION_TEXT WHERE ( LT_QMFILENAME IS NULL OR LT_STRING_ID IS NULL ) AND LOC_TEXT_ID = last_insert_rowid();
-- RELATION TO MAIN COLLECTION (ADD TO GROUP: )
INSERT INTO CA_GROUP_ENTRY (GE_GROUP_ID, GE_ENTRY_ID, GE_POSITION)
VALUES ( (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TYPE_NAME = "menucollections") ,
(SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.COLLECTION_NAME),
(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")) );
- -- ADD ATTRIBUTE
+ -- ADD ATTRIBUTES
INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE )
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 );
+ INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE )
+ 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 );
+ INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE )
+ 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 );
END;
-- CREATE VIEW ITEM_TO_COLLECTION
@@ -140,6 +152,7 @@
AS SELECT
"" AS "ITEM_TYPE",
"" AS "ITEM_NAME",
+ "" AS "ITEM_SHORTNAME",
"" AS "ICON_FILENAME",
"" AS "ICON_SKIN_ID",
"" AS "ITEM_DEST",
@@ -161,18 +174,6 @@
SELECT " CREATE TRIGGER item_to_collection_insert_url" AS " ";
CREATE TRIGGER item_to_collection_insert_url INSTEAD OF INSERT ON ITEM_TO_COLLECTION WHEN new.ITEM_TYPE="url"
BEGIN
- -- INSERT ICON
- INSERT OR IGNORE INTO CA_ICON (IC_FILENAME, IC_SKIN_ID)
- VALUES ( new.ICON_FILENAME, new.ICON_SKIN_ID);
- DELETE FROM CA_ICON WHERE ICON_ID = last_insert_rowid() AND
- ( (IC_FILENAME IS NULL AND IC_SKIN_ID IS NULL) OR
- (SELECT ICON_ID FROM CA_ICON WHERE IC_FILENAME = new.ICON_FILENAME AND ICON_ID <> last_insert_rowid()) IS NOT NULL OR
- (SELECT ICON_ID FROM CA_ICON WHERE IC_SKIN_ID = new.ICON_SKIN_ID AND ICON_ID <> last_insert_rowid()) IS NOT NULL);
- -- INSERT ENTRY
- INSERT OR IGNORE INTO CA_ENTRY (EN_TEXT, EN_ROLE, EN_TYPE_NAME, EN_ICON_ID )
- 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 ));
- INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE )
- VALUES ( ( SELECT last_insert_rowid() ) ,"url",new.ITEM_DEST);
-- RELATION TO MAIN COLLECTION (ADD TO GROUP: )
INSERT INTO CA_GROUP_ENTRY (GE_GROUP_ID, GE_ENTRY_ID, GE_POSITION)
VALUES ( (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.COLLECTION_NAME) ,
@@ -203,12 +204,11 @@
INSERT OR IGNORE INTO CA_ICON (IC_FILENAME, IC_SKIN_ID)
VALUES ( new.ICON_FILENAME, new.ICON_SKIN_ID);
DELETE FROM CA_ICON WHERE ICON_ID = last_insert_rowid() AND
- ( (IC_FILENAME IS NULL AND IC_SKIN_ID IS NULL) OR
- (SELECT ICON_ID FROM CA_ICON WHERE IC_FILENAME = new.ICON_FILENAME AND ICON_ID <> last_insert_rowid()) IS NOT NULL OR
- (SELECT ICON_ID FROM CA_ICON WHERE IC_SKIN_ID = new.ICON_SKIN_ID AND ICON_ID <> last_insert_rowid()) IS NOT NULL);
+ ( (IC_FILENAME = "" AND IC_SKIN_ID = "") OR
+ (SELECT ICON_ID FROM CA_ICON WHERE IC_FILENAME = new.ICON_FILENAME AND IC_SKIN_ID = new.ICON_SKIN_ID AND ICON_ID <> last_insert_rowid()) IS NOT NULL);
-- INSERT ENTRY
INSERT OR IGNORE INTO CA_ENTRY (EN_TEXT, EN_ROLE, EN_TYPE_NAME, EN_ICON_ID )
- VALUES ( new.ITEM_NAME, 1, "widget", (SELECT ICON_ID FROM CA_ICON WHERE IC_FILENAME = new.ICON_FILENAME OR IC_SKIN_ID = new.ICON_SKIN_ID ));
+ VALUES ( new.ITEM_NAME, 1, "widget", (SELECT ICON_ID FROM CA_ICON WHERE IC_FILENAME = new.ICON_FILENAME AND IC_SKIN_ID = new.ICON_SKIN_ID ));
-- INSERT ATTRIBUTES
INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE )
VALUES ( ( SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.ITEM_NAME ) ,"packageuid",new.PACKAGE_UID);
@@ -225,6 +225,39 @@
+-- CREATE VIEW URL
+SELECT "------------------------------------" AS " ";
+SELECT "CREATE VIEW URL" AS " ";
+DROP VIEW IF EXISTS URL;
+CREATE VIEW URL
+ AS SELECT
+ "" AS "URL_TITLE",
+ "" AS "URL_SHORT_TITLE",
+ "" AS "ICON_FILENAME",
+ "" AS "ICON_SKIN_ID",
+ "" AS "URL_DEST";
+
+SELECT " CREATE TRIGGER insert_url" AS " ";
+CREATE TRIGGER insert_url INSTEAD OF INSERT ON URL
+BEGIN
+ -- INSERT ICON
+ INSERT OR IGNORE INTO CA_ICON (IC_FILENAME, IC_SKIN_ID)
+ VALUES ( new.ICON_FILENAME, new.ICON_SKIN_ID);
+ DELETE FROM CA_ICON WHERE ICON_ID = last_insert_rowid() AND
+ ( (IC_FILENAME = "" AND IC_SKIN_ID = "") OR
+ (SELECT ICON_ID FROM CA_ICON WHERE IC_FILENAME = new.ICON_FILENAME AND IC_SKIN_ID = new.ICON_SKIN_ID AND ICON_ID <> last_insert_rowid()) IS NOT NULL);
+ -- INSERT ENTRY
+ INSERT OR IGNORE INTO CA_ENTRY (EN_TEXT, EN_ROLE, EN_TYPE_NAME, EN_ICON_ID )
+ VALUES ( new.URL_TITLE, 1, "url", (SELECT ICON_ID FROM CA_ICON WHERE IC_FILENAME = new.ICON_FILENAME AND IC_SKIN_ID = new.ICON_SKIN_ID ) );
+ -- ADD ATTRIBUTES
+ INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE )
+ VALUES ( ( SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.URL_TITLE ), "url", new.URL_DEST );
+ INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE )
+ VALUES ( ( SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.URL_TITLE ) ,"short_name", new.URL_SHORT_TITLE );
+END;
+
+
+
-- insert neccessary items
SELECT "INSERT menucollections ENTRY" AS " ";
INSERT INTO CA_ENTRY (EN_TEXT, EN_ROLE, EN_TYPE_NAME, EN_ICON_ID, EN_UID )
@@ -235,23 +268,31 @@
SELECT "------------------------------------" AS " ";
SELECT "INSERT Downloaded collection ENTRY" AS " ";
-INSERT INTO CA_ICON (IC_FILENAME)
-VALUES ( "qtg_large_applications_download");
+INSERT INTO CA_ICON (IC_FILENAME, IC_SKIN_ID)
+VALUES ( "qtg_large_applications_download", "" );
INSERT INTO CA_ENTRY (EN_TEXT, EN_ROLE, EN_TYPE_NAME, EN_ICON_ID, EN_FLAGS )
-VALUES ( 'Downloaded', 2, "collection::downloaded", last_insert_rowid() , 8 );
+VALUES ( 'txt_applib_dblist_downloads', 2, "collection::downloaded", ( SELECT MAX(ICON_ID) FROM CA_ICON ) , 8 );
+
+INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE )
+VALUES ( ( SELECT MAX(ENTRY_ID) FROM CA_ENTRY ), "appgroup_name", "Downloaded" );
INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE )
-VALUES ( last_insert_rowid(), "appgroup_name", " Downloaded" );
+VALUES ( ( SELECT MAX(ENTRY_ID) FROM CA_ENTRY ), "title_name", "txt_applib_subtitle_downloads" );
INSERT INTO CA_LOCALIZATION_TEXT ( LT_TABLE_NAME, LT_ATTRIBUTE_NAME, LT_STRING_ID, LT_QMFILENAME, LT_ROW_ID )
-VALUES ( 'CA_ENTRY', 'EN_TEXT', "txt_applib_dblist_downloaded",( SELECT DB_VALUE FROM CA_DB_PROPERTIES WHERE DB_PROPERTY = "QMfile" ), ( SELECT last_insert_rowid() ) );
+VALUES ( 'CA_ENTRY', 'EN_TEXT', "txt_applib_dblist_downloads",( SELECT DB_VALUE FROM CA_DB_PROPERTIES WHERE DB_PROPERTY = "QMfile" ), ( SELECT MAX(ENTRY_ID) FROM CA_ENTRY ) );
+
+INSERT INTO CA_LOCALIZATION_TEXT ( LT_TABLE_NAME, LT_ATTRIBUTE_NAME, LT_STRING_ID, LT_QMFILENAME, LT_ROW_ID )
+VALUES ( 'CA_ATTRIBUTE', 'title_name', "txt_applib_subtitle_downloads",( SELECT DB_VALUE FROM CA_DB_PROPERTIES WHERE DB_PROPERTY = "QMfile" ), ( SELECT MAX(ENTRY_ID) FROM CA_ENTRY ) );
INSERT INTO CA_GROUP_ENTRY (GE_GROUP_ID, GE_ENTRY_ID, GE_POSITION)
VALUES ( (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TYPE_NAME = "menucollections") ,
(SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TYPE_NAME = "collection::downloaded"),
1 );
+
+
SELECT "------------------------------------" AS " ";
SELECT "castoragedb_create - END" AS " ";
SELECT "------------------------------------" AS " ";