contentstorage/castorage/data/castoragedb_create.sql
author Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
Thu, 27 May 2010 13:11:12 +0300
changeset 80 397d00875918
parent 73 4bc7b118b3df
child 103 b99b84bcd2d1
permissions -rw-r--r--
Revision: 201019 Kit: 2010121
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
61
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
     1
DROP TABLE IF EXISTS CA_ENTRY;
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
     2
DROP TABLE IF EXISTS CA_GROUP_ENTRY;
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
     3
DROP TABLE IF EXISTS CA_ICON;
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
     4
DROP TABLE IF EXISTS CA_LAUNCH;
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
     5
DROP TABLE IF EXISTS CA_ATTRIBUTE;
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
     6
DROP TABLE IF EXISTS CA_LOCALIZATION_TEXT;
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
     7
DROP TABLE IF EXISTS CA_DB_PROPERTIES;
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
     8
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
     9
SELECT "CREATE TABLE CA_ENTRY" AS " ";
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    10
SELECT "------------------------------------" AS " ";
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    11
CREATE TABLE CA_ENTRY( ENTRY_ID INTEGER PRIMARY KEY,
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    12
                                 EN_TEXT TEXT NOT NULL COLLATE NOCASE,
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    13
                                 EN_DESCRIPTION TEXT COLLATE NOCASE,
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    14
                                 EN_ROLE INTEGER default 1,
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    15
                                 EN_TYPE_NAME TEXT NOT NULL,
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    16
                                 EN_ICON_ID INTEGER,
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    17
                                 EN_CREATION_TIME BIGINT default -1 NOT NULL,
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    18
                                 EN_UID INTEGER UNIQUE, 
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    19
                                 EN_FLAGS INTEGER default 8, 
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    20
                                 FOREIGN KEY(EN_ICON_ID) REFERENCES CA_ICON(ICON_ID) );
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    21
CREATE INDEX EntryText_CAIndex ON CA_ENTRY(EN_TEXT);
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    22
CREATE INDEX EntryCreationTime_CAIndex ON CA_ENTRY(EN_CREATION_TIME);
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    23
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    24
SELECT "CREATE TABLE CA_GROUP_ENTRY" AS " ";
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    25
SELECT "------------------------------------" AS " ";
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    26
CREATE TABLE CA_GROUP_ENTRY( GROUP_ENTRY_ID INTEGER PRIMARY KEY ,
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    27
                                  GE_GROUP_ID INTEGER NOT NULL,
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    28
                                  GE_ENTRY_ID INTEGER NOT NULL,
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    29
                                  GE_POSITION INTEGER,
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    30
                                  UNIQUE( GE_GROUP_ID, GE_ENTRY_ID ),
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    31
                                  FOREIGN KEY(GE_GROUP_ID) REFERENCES CA_ENTRY(ENTRY_ID),
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    32
                                  FOREIGN KEY(GE_ENTRY_ID) REFERENCES CA_ENTRY(ENTRY_ID) );
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    33
CREATE INDEX GroupGE_POSITION_CAIndex ON CA_GROUP_ENTRY(GE_POSITION);
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    34
CREATE TRIGGER update_position_in_ca_group_entry_after_remove AFTER DELETE ON CA_GROUP_ENTRY
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    35
BEGIN
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    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;
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    37
END; 
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    38
CREATE TRIGGER update_position_in_ca_group_entry_before_insert BEFORE INSERT ON CA_GROUP_ENTRY
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    39
BEGIN
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    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;
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    41
END;
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    42
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    43
SELECT "CREATE TABLE CA_ICON" AS " ";
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    44
SELECT "------------------------------------" AS " ";
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    45
CREATE TABLE CA_ICON( ICON_ID INTEGER PRIMARY KEY ,
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    46
                                  IC_FILENAME TEXT,
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    47
                                  IC_SKIN_ID TEXT,
80
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
    48
                                  IC_APP_ID TEXT,
61
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    49
                                  UNIQUE( IC_FILENAME, IC_SKIN_ID, IC_APP_ID ) );
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    50
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    51
SELECT "CREATE TABLE CA_LAUNCH" AS " ";
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    52
SELECT "------------------------------------" AS " ";
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    53
CREATE TABLE CA_LAUNCH( LAUNCH_ID INTEGER PRIMARY KEY ,
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    54
                                  LA_ENTRY_ID INTEGER NOT NULL,
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    55
                                  LA_LAUNCH_TIME INTEGER NOT NULL,
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    56
                                  FOREIGN KEY(LA_ENTRY_ID) REFERENCES CA_ENTRY(ENTRY_ID) );
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    57
CREATE INDEX LaunchLaunchTime_CAIndex ON CA_LAUNCH(LA_LAUNCH_TIME);
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    58
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    59
SELECT "CREATE TABLE CA_ATTRIBUTE" AS " ";
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    60
SELECT "------------------------------------" AS " ";
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    61
CREATE TABLE CA_ATTRIBUTE( ATTRIBUTE_ID INTEGER PRIMARY KEY ,
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    62
                                  AT_ENTRY_ID INTEGER NOT NULL,
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    63
                                  AT_NAME TEXT NOT NULL,
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    64
                                  AT_VALUE TEXT NOT NULL,
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    65
                                  UNIQUE( AT_ENTRY_ID, AT_NAME ),
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    66
                                  FOREIGN KEY(AT_ENTRY_ID) REFERENCES CA_ENTRY(ENTRY_ID) );
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    67
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    68
SELECT "CREATE TABLE CA_LOCALIZATION_TEXT" AS " ";
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    69
SELECT "------------------------------------" AS " ";
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    70
CREATE TABLE CA_LOCALIZATION_TEXT( LOC_TEXT_ID INTEGER PRIMARY KEY ,
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    71
                                  LT_TABLE_NAME TEXT ,
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    72
                                  LT_ATTRIBUTE_NAME TEXT ,
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    73
                                  LT_STRING_ID TEXT ,
80
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
    74
                                  LT_QMFILENAME TEXT,
61
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    75
                                  LT_ROW_ID INTEGER );
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    76
                                  
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    77
SELECT "CREATE TABLE CA_DB_PROPERTIES" AS " ";
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    78
SELECT "------------------------------------" AS " ";
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    79
CREATE TABLE CA_DB_PROPERTIES( DB_PROPERTIES_ID INTEGER PRIMARY KEY ,
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    80
                                  DB_PROPERTY TEXT UNIQUE,
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    81
                                  DB_VALUE TEXT );       
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    82
                                   
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    83
80
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
    84
SELECT "INSERT INITIAL DATA TO CA_DB_PROPERTIES" AS " ";
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
    85
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
    86
SELECT "------------------------------------" AS " ";
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
    87
INSERT INTO CA_DB_PROPERTIES ( DB_PROPERTY, DB_VALUE ) 
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
    88
VALUES ( 'Version', '00001' );
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
    89
INSERT INTO CA_DB_PROPERTIES ( DB_PROPERTY, DB_VALUE ) 
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
    90
VALUES ( 'Language', '' );
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
    91
INSERT INTO CA_DB_PROPERTIES ( DB_PROPERTY, DB_VALUE ) 
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
    92
VALUES ( 'QMfile', 'contentstorage_' );
61
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    93
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    94
-- CREATE VIEW COLLECTION
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    95
SELECT "------------------------------------" AS " ";
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    96
SELECT "CREATE VIEW COLLECTION" AS " ";
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    97
DROP VIEW IF EXISTS COLLECTION;
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    98
CREATE VIEW COLLECTION 
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
    99
  AS SELECT 
66
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   100
    "" AS "COLLECTION_NAME",
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   101
    "" AS "COL_LOCNAME",
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   102
    "" AS "COL_APP_GROUP_NAME", 
80
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   103
    "" AS "ICON_FILENAME",
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   104
    "" AS "ICON_SKIN_ID",
73
4bc7b118b3df Revision: 201017
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 66
diff changeset
   105
    "" AS "FLAGS";
61
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   106
    
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   107
SELECT "  CREATE TRIGGER collection_insert" AS " ";
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   108
CREATE TRIGGER collection_insert INSTEAD OF INSERT ON COLLECTION 
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   109
BEGIN  
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   110
  -- INSERT ICON
80
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   111
  INSERT INTO CA_ICON (IC_FILENAME, IC_SKIN_ID) 
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   112
  VALUES ( new.ICON_FILENAME, new.ICON_SKIN_ID);
61
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   113
  -- DELETE IF ICON IS NULL OR IF IT IS DUPLICATE
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   114
  DELETE FROM CA_ICON WHERE ICON_ID = last_insert_rowid() AND 
80
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   115
      ( (IC_FILENAME IS NULL AND IC_SKIN_ID IS NULL) OR 
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   116
        (SELECT ICON_ID FROM CA_ICON WHERE IC_FILENAME = new.ICON_FILENAME AND ICON_ID <> last_insert_rowid()) IS NOT NULL OR 
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   117
        (SELECT ICON_ID FROM CA_ICON WHERE IC_SKIN_ID = new.ICON_SKIN_ID AND ICON_ID <> last_insert_rowid()) IS NOT NULL);
61
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   118
  -- INSERT ENTRY
66
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   119
    INSERT INTO CA_ENTRY (EN_TEXT, EN_ROLE, EN_TYPE_NAME, EN_FLAGS, EN_ICON_ID ) 
80
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   120
    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 ));
66
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   121
    -- LOCALIZATION 
80
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   122
    INSERT INTO CA_LOCALIZATION_TEXT ( LT_TABLE_NAME, LT_ATTRIBUTE_NAME, LT_STRING_ID, LT_QMFILENAME, LT_ROW_ID ) 
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   123
    VALUES ( 'CA_ENTRY', 'EN_TEXT', new.COL_LOCNAME, ( SELECT DB_VALUE FROM CA_DB_PROPERTIES WHERE DB_PROPERTY = "QMfile" ), ( SELECT last_insert_rowid() ) );
66
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   124
    DELETE FROM CA_LOCALIZATION_TEXT WHERE LT_STRING_ID IS NULL AND LOC_TEXT_ID = last_insert_rowid() ;
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   125
    -- RELATION TO MAIN COLLECTION (ADD TO GROUP: )
61
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   126
  INSERT INTO CA_GROUP_ENTRY (GE_GROUP_ID, GE_ENTRY_ID, GE_POSITION) 
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   127
  VALUES ( (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TYPE_NAME = "menucollections") , 
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   128
          (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.COLLECTION_NAME), 
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   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")) );
66
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   130
    -- ADD ATTRIBUTE
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   131
    INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE )
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   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 );
61
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   133
END;
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   134
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   135
-- CREATE VIEW ITEM_TO_COLLECTION
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   136
SELECT "------------------------------------" AS " ";
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   137
SELECT "CREATE VIEW ITEM_TO_COLLECTION" AS " ";
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   138
DROP VIEW IF EXISTS ITEM_TO_COLLECTION;
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   139
CREATE VIEW ITEM_TO_COLLECTION 
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   140
  AS SELECT 
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   141
    "" AS "ITEM_TYPE",
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   142
    "" AS "ITEM_NAME",
80
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   143
    "" AS "ICON_FILENAME",
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   144
    "" AS "ICON_SKIN_ID",
61
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   145
    "" AS "ITEM_DEST",    
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   146
    "" AS "COLLECTION_NAME";
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   147
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   148
SELECT "  CREATE TRIGGER item_to_collection_insert_app" AS " ";
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   149
CREATE TRIGGER item_to_collection_insert_app INSTEAD OF INSERT ON ITEM_TO_COLLECTION WHEN new.ITEM_TYPE="application"
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   150
BEGIN
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   151
  -- INSERT ENTRY
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   152
  INSERT OR IGNORE INTO CA_ENTRY (EN_TEXT, EN_ROLE, EN_TYPE_NAME, EN_UID ) 
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   153
  VALUES ( new.ITEM_NAME, 1, "application", new.ITEM_DEST   );
66
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   154
  -- RELATION TO MAIN COLLECTION (ADD TO GROUP: )
61
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   155
  INSERT INTO CA_GROUP_ENTRY (GE_GROUP_ID, GE_ENTRY_ID, GE_POSITION) 
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   156
  VALUES ( (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.COLLECTION_NAME) , 
80
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   157
          (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_UID = new.ITEM_DEST), 
61
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   158
          (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)) );
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   159
END;
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   160
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   161
SELECT "  CREATE TRIGGER item_to_collection_insert_url" AS " ";
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   162
CREATE TRIGGER item_to_collection_insert_url INSTEAD OF INSERT ON ITEM_TO_COLLECTION WHEN new.ITEM_TYPE="url"
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   163
BEGIN
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   164
  -- INSERT ICON
80
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   165
  INSERT OR IGNORE INTO CA_ICON (IC_FILENAME, IC_SKIN_ID) 
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   166
  VALUES ( new.ICON_FILENAME, new.ICON_SKIN_ID);
61
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   167
  DELETE FROM CA_ICON WHERE ICON_ID = last_insert_rowid() AND 
80
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   168
      ( (IC_FILENAME IS NULL AND IC_SKIN_ID IS NULL) OR 
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   169
        (SELECT ICON_ID FROM CA_ICON WHERE IC_FILENAME = new.ICON_FILENAME AND ICON_ID <> last_insert_rowid()) IS NOT NULL OR 
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   170
        (SELECT ICON_ID FROM CA_ICON WHERE IC_SKIN_ID = new.ICON_SKIN_ID AND ICON_ID <> last_insert_rowid()) IS NOT NULL);  
61
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   171
  -- INSERT ENTRY
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   172
  INSERT OR IGNORE INTO CA_ENTRY (EN_TEXT, EN_ROLE, EN_TYPE_NAME, EN_ICON_ID ) 
80
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   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 ));
61
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   174
  INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE )  
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   175
  VALUES ( ( SELECT last_insert_rowid() ) ,"url",new.ITEM_DEST);
66
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   176
  -- RELATION TO MAIN COLLECTION (ADD TO GROUP: )
61
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   177
  INSERT INTO CA_GROUP_ENTRY (GE_GROUP_ID, GE_ENTRY_ID, GE_POSITION) 
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   178
  VALUES ( (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.COLLECTION_NAME) , 
80
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   179
          (SELECT ENTRY_ID FROM CA_ENTRY WHERE (EN_TEXT = new.ITEM_NAME AND EN_TYPE_NAME = new.ITEM_TYPE )),
61
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   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)) );
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   181
END;
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   182
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   183
66
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   184
-- CREATE VIEW WIDGET   _TO_COLLECTION
61
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   185
SELECT "------------------------------------" AS " ";
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   186
SELECT "CREATE VIEW WIDGET_TO_COLLECTION" AS " ";
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   187
DROP VIEW IF EXISTS WIDGET_TO_COLLECTION;
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   188
CREATE VIEW WIDGET_TO_COLLECTION 
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   189
  AS SELECT 
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   190
    "" AS "ITEM_TYPE",
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   191
    "" AS "ITEM_NAME",
80
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   192
    "" AS "ICON_FILENAME",
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   193
    "" AS "ICON_SKIN_ID",
61
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   194
    "" AS "PACKAGE_UID",
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   195
    "" AS "LIBRARY",
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   196
    "" AS "LONG_NAME",
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   197
    "" AS "URI",
73
4bc7b118b3df Revision: 201017
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 66
diff changeset
   198
    "" AS "COLLECTION_NAME";
61
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   199
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   200
SELECT "  CREATE TRIGGER insert_widget_to_collection" AS " ";
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   201
CREATE TRIGGER item_to_collection_insert_widget INSTEAD OF INSERT ON WIDGET_TO_COLLECTION WHEN new.ITEM_TYPE="widget"
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   202
BEGIN
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   203
  -- INSERT ICON
80
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   204
  INSERT OR IGNORE INTO CA_ICON (IC_FILENAME, IC_SKIN_ID) 
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   205
  VALUES ( new.ICON_FILENAME, new.ICON_SKIN_ID);
61
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   206
  DELETE FROM CA_ICON WHERE ICON_ID = last_insert_rowid() AND 
80
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   207
      ( (IC_FILENAME IS NULL AND IC_SKIN_ID IS NULL) OR 
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   208
        (SELECT ICON_ID FROM CA_ICON WHERE IC_FILENAME = new.ICON_FILENAME AND ICON_ID <> last_insert_rowid()) IS NOT NULL OR 
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   209
        (SELECT ICON_ID FROM CA_ICON WHERE IC_SKIN_ID = new.ICON_SKIN_ID AND ICON_ID <> last_insert_rowid()) IS NOT NULL);
61
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   210
  -- INSERT ENTRY
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   211
  INSERT OR IGNORE INTO CA_ENTRY (EN_TEXT, EN_ROLE, EN_TYPE_NAME, EN_ICON_ID ) 
80
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   212
  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 ));
61
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   213
  -- INSERT ATTRIBUTES
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   214
  INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE )  
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   215
  VALUES ( ( SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.ITEM_NAME ) ,"packageuid",new.PACKAGE_UID);
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   216
  INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE )  
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   217
  VALUES ( ( SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.ITEM_NAME ) ,"widget:library",new.LIBRARY);
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   218
  INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE )  
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   219
  VALUES ( ( SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.ITEM_NAME ) ,"long_name",new.LONG_NAME);
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   220
  INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE )  
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   221
  VALUES ( ( SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.ITEM_NAME ) ,"widget:uri",new.URI);
66
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   222
  -- RELATION TO MAIN COLLECTION (ADD TO GROUP: )
61
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   223
  INSERT INTO CA_GROUP_ENTRY (GE_GROUP_ID, GE_ENTRY_ID, GE_POSITION) 
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   224
  VALUES ( (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TEXT = new.COLLECTION_NAME) , 
80
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   225
          (SELECT ENTRY_ID FROM CA_ENTRY WHERE (EN_TEXT = new.ITEM_NAME AND EN_TYPE_NAME = new.ITEM_TYPE)), 
61
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   226
          (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)) );
66
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   227
END;
61
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   228
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   229
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   230
66
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   231
-- insert neccessary items
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   232
SELECT "INSERT menucollections ENTRY" AS " ";
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   233
INSERT INTO CA_ENTRY (EN_TEXT, EN_ROLE, EN_TYPE_NAME, EN_ICON_ID, EN_UID ) 
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   234
VALUES ( 'AllCollections', 2, "menucollections", NULL , NULL  );
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   235
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   236
INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE )  
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   237
VALUES ( last_insert_rowid(), "appgroup_name", "AllCollections" );
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   238
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   239
SELECT "------------------------------------" AS " ";
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   240
SELECT "INSERT Downloaded collection ENTRY" AS " ";
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   241
INSERT INTO CA_ICON (IC_FILENAME) 
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   242
VALUES ( "qtg_large_applications_download");
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   243
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   244
INSERT INTO CA_ENTRY (EN_TEXT, EN_ROLE, EN_TYPE_NAME, EN_ICON_ID,  EN_FLAGS ) 
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   245
VALUES ( 'Downloaded', 2, "collection::downloaded", last_insert_rowid() , 8 );
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   246
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   247
INSERT OR IGNORE INTO CA_ATTRIBUTE (AT_ENTRY_ID, AT_NAME, AT_VALUE )  
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   248
VALUES ( last_insert_rowid(), "appgroup_name", " Downloaded" );
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   249
80
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   250
INSERT INTO CA_LOCALIZATION_TEXT ( LT_TABLE_NAME, LT_ATTRIBUTE_NAME, LT_STRING_ID, LT_QMFILENAME, LT_ROW_ID ) 
397d00875918 Revision: 201019
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 73
diff changeset
   251
VALUES ( 'CA_ENTRY', 'EN_TEXT', "txt_applib_dblist_downloaded",( SELECT DB_VALUE FROM CA_DB_PROPERTIES WHERE DB_PROPERTY = "QMfile" ), ( SELECT last_insert_rowid() ) );
66
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   252
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   253
INSERT INTO CA_GROUP_ENTRY (GE_GROUP_ID, GE_ENTRY_ID, GE_POSITION) 
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   254
VALUES ( (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TYPE_NAME = "menucollections") , 
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   255
          (SELECT ENTRY_ID FROM CA_ENTRY WHERE EN_TYPE_NAME = "collection::downloaded"), 
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   256
          1 ); 
32469d7d46ff Revision: 201015
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents: 61
diff changeset
   257
61
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   258
SELECT "------------------------------------" AS " ";
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   259
SELECT "castoragedb_create - END" AS " ";
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   260
SELECT "------------------------------------" AS " ";
8e5041d13c84 Revision: 201011
Dremov Kirill (Nokia-D-MSW/Tampere) <kirill.dremov@nokia.com>
parents:
diff changeset
   261