--- a/contentstorage/castorage/data/castoragedb_create.sql	Fri Jul 23 14:03:00 2010 +0300
+++ b/contentstorage/castorage/data/castoragedb_create.sql	Fri Aug 06 13:40:46 2010 +0300
@@ -99,6 +99,7 @@
   AS SELECT 
     "" AS "COLLECTION_NAME",
     "" AS "COL_LOCNAME",
+    "" AS "TRANSLATION_FILE",
     "" AS "COL_SHORT_NAME",
     "" AS "COL_TITLE_NAME",
     "" AS "COL_APP_GROUP_NAME", 
@@ -114,16 +115,21 @@
   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") , 
@@ -168,21 +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 ));
-  -- 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.ITEM_NAME ) ,"url",new.ITEM_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.ITEM_NAME ) ,"short_name", new.ITEM_SHORTNAME );
   -- 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) , 
@@ -213,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);
@@ -235,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 ) 
@@ -245,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 " ";