1 DROP TABLE IF EXISTS CA_ENTRY;

     2 DROP TABLE IF EXISTS CA_GROUP_ENTRY;

     3 DROP TABLE IF EXISTS CA_ICON;

     4 DROP TABLE IF EXISTS CA_LAUNCH;

     5 DROP TABLE IF EXISTS CA_ATTRIBUTE;

     6 

     7 SELECT "CREATE TABLE CA_ENTRY" AS " ";

     8 SELECT "------------------------------------" AS " ";

     9 CREATE TABLE CA_ENTRY( ENTRY_ID INTEGER PRIMARY KEY,

    10                                  EN_TEXT TEXT NOT NULL COLLATE NOCASE,

    11                                  EN_DESCRIPTION TEXT COLLATE NOCASE,

    12                                  EN_ROLE INTEGER default 1,

    13                                  EN_TYPE_NAME TEXT NOT NULL,

    14                                  EN_ICON_ID INTEGER,

    15                                  EN_CREATION_TIME BIGINT default -1 NOT NULL,

    16                                  EN_UID INTEGER UNIQUE, 

    17                                  EN_FLAGS INTEGER default 8, 

    18                                  FOREIGN KEY(EN_ICON_ID) REFERENCES CA_ICON(ICON_ID) );

    19 CREATE INDEX EntryText_CAIndex ON CA_ENTRY(EN_TEXT);

    20 CREATE INDEX EntryCreationTime_CAIndex ON CA_ENTRY(EN_CREATION_TIME);

    21 

    22 SELECT "CREATE TABLE CA_GROUP_ENTRY" AS " ";

    23 SELECT "------------------------------------" AS " ";

    24 CREATE TABLE CA_GROUP_ENTRY( GROUP_ENTRY_ID INTEGER PRIMARY KEY ,

    25                                   GE_GROUP_ID INTEGER NOT NULL,

    26                                   GE_ENTRY_ID INTEGER NOT NULL,

    27                                   GE_POSITION INTEGER,

    28                                   UNIQUE( GE_GROUP_ID, GE_ENTRY_ID ),

    29                                   FOREIGN KEY(GE_GROUP_ID) REFERENCES CA_ENTRY(ENTRY_ID),

    30                                   FOREIGN KEY(GE_ENTRY_ID) REFERENCES CA_ENTRY(ENTRY_ID) );

    31 CREATE INDEX GroupGE_POSITION_CAIndex ON CA_GROUP_ENTRY(GE_POSITION);

    32 CREATE TRIGGER update_position_in_ca_group_entry_after_remove AFTER DELETE ON CA_GROUP_ENTRY

    33 BEGIN

    34     UPDATE CA_GROUP_ENTRY SET GE_POSITION = GE_POSITION - 1 WHERE GE_POSITION > old.GE_POSITION AND GE_GROUP_ID = old.GE_GROUP_ID;

    35 END; 

    36 CREATE TRIGGER update_position_in_ca_group_entry_before_insert BEFORE INSERT ON CA_GROUP_ENTRY

    37 BEGIN

    38     UPDATE CA_GROUP_ENTRY SET GE_POSITION = GE_POSITION + 1 WHERE GE_POSITION >= new.GE_POSITION AND GE_GROUP_ID = new.GE_GROUP_ID;

    39 END;

    40 

    41 SELECT "CREATE TABLE CA_ICON" AS " ";

    42 SELECT "------------------------------------" AS " ";

    43 CREATE TABLE CA_ICON( ICON_ID INTEGER PRIMARY KEY ,

    44                                   IC_FILENAME TEXT,

    45                                   IC_BITMAP_ID INTEGER,

    46                                   IC_MASK_ID INTEGER,

    47                                   IC_SKINMAJOR_ID INTEGER,

    48                                   IC_SKINMINOR_ID INTEGER,

    49                                   UNIQUE( IC_FILENAME, IC_BITMAP_ID, IC_MASK_ID, IC_SKINMAJOR_ID, IC_SKINMINOR_ID) );

    50 

    51 SELECT "CREATE TABLE CA_LAUNCH" AS " ";

    52 SELECT "------------------------------------" AS " ";

    53 CREATE TABLE CA_LAUNCH( LAUNCH_ID INTEGER PRIMARY KEY ,

    54                                   LA_ENTRY_ID INTEGER NOT NULL,

    55                                   LA_LAUNCH_TIME INTEGER NOT NULL,

    56                                   FOREIGN KEY(LA_ENTRY_ID) REFERENCES CA_ENTRY(ENTRY_ID) );

    57 CREATE INDEX LaunchLaunchTime_CAIndex ON CA_LAUNCH(LA_LAUNCH_TIME);

    58 

    59 SELECT "CREATE TABLE CA_ATTRIBUTE" AS " ";

    60 SELECT "------------------------------------" AS " ";

    61 CREATE TABLE CA_ATTRIBUTE( ATTRIBUTE_ID INTEGER PRIMARY KEY ,

    62                                   AT_ENTRY_ID INTEGER NOT NULL,

    63                                   AT_NAME TEXT NOT NULL,

    64                                   AT_VALUE TEXT NOT NULL,

    65                                   UNIQUE( AT_ENTRY_ID, AT_NAME ),

    66                                   FOREIGN KEY(AT_ENTRY_ID) REFERENCES CA_ENTRY(ENTRY_ID) );

    67 

    68 SELECT "CREATE TABLE CA_LOCALIZATION_TEXT" AS " ";

    69 SELECT "------------------------------------" AS " ";

    70 CREATE TABLE CA_LOCALIZATION_TEXT( LOC_TEXT_ID INTEGER PRIMARY KEY ,

    71                                   LT_TABLE_NAME TEXT ,

    72                                   LT_ATTRIBUTE_NAME TEXT ,

    73                                   LT_STRING_ID TEXT ,

    74                                   LT_ROW_ID INTEGER );

    75                                   

    76 SELECT "CREATE TABLE CA_DB_PROPERTIES" AS " ";

    77 SELECT "------------------------------------" AS " ";

    78 CREATE TABLE CA_DB_PROPERTIES( DB_PROPERTIES_ID INTEGER PRIMARY KEY ,

    79                                   DB_PROPERTY TEXT UNIQUE,

    80                                   DB_VALUE TEXT );