schema.sql

   1 --
   2 -- Contacts Service
   3 --
   4 -- Copyright (c) 2010 - 2012 Samsung Electronics Co., Ltd. All rights reserved.
   5 --
   6 -- Contact: Jongwon Lee <gogosing.lee@samsung.com>
   7 --
   8 -- Licensed under the Apache License, Version 2.0 (the "License");
   9 -- you may not use this file except in compliance with the License.
  10 -- You may obtain a copy of the License at
  11 --
  12 -- http://www.apache.org/licenses/LICENSE-2.0
  13 --
  14 -- Unless required by applicable law or agreed to in writing, software
  15 -- distributed under the License is distributed on an "AS IS" BASIS,
  16 -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  17 -- See the License for the specific language governing permissions and
  18 -- limitations under the License.
  19 --
  20
  21 --PRAGMA journal_mode = PERSIST;
  22 --PRAGMA journal_mode = TRUNCATE;
  23 PRAGMA user_version = 101;
  24
  25 CREATE TABLE persons
  26 (
  27         person_id                       INTEGER PRIMARY KEY AUTOINCREMENT,
  28         name_contact_id         INTEGER NOT NULL,
  29         has_phonenumber         INTEGER,
  30         has_email                       INTEGER,
  31         created_ver                     INTEGER NOT NULL,
  32         changed_ver                     INTEGER NOT NULL,
  33         ringtone_path                   TEXT,
  34         vibration                       TEXT,
  35         message_alert           TEXT,
  36         image_thumbnail_path            TEXT,
  37         image_path                      TEXT,
  38         link_count                      INTEGER,
  39         addressbook_ids                 TEXT,
  40         dirty                           INTEGER,
  41         status                          TEXT
  42 );
  43
  44 CREATE TRIGGER trg_person_del AFTER DELETE ON persons
  45  BEGIN
  46         DELETE FROM favorites WHERE person_id = old.person_id;
  47         SELECT _PERSON_DELETE_(old.person_id);
  48  END;
  49
  50 CREATE TABLE addressbooks
  51 (
  52         addressbook_id          INTEGER PRIMARY KEY AUTOINCREMENT,
  53         addressbook_name        TEXT NOT NULL,
  54         account_id                      INTEGER,
  55         mode                                            INTEGER, -- permission
  56         last_sync_ver   INTEGER,
  57         smack_label             TEXT NOT NULL,          -- smack label
  58         UNIQUE(addressbook_name)
  59 );
  60
  61 INSERT INTO addressbooks(addressbook_id, addressbook_name, mode, account_id, smack_label) values(0, 'http://tizen.org/addressbook/phone', 0, 0, 'org.tizen.contacts');
  62
  63 CREATE TRIGGER trg_addressbook_del AFTER DELETE ON addressbooks
  64  BEGIN
  65    DELETE FROM groups WHERE addressbook_id = old.addressbook_id;
  66    UPDATE contacts SET deleted = 1, person_id = 0, changed_ver = ((SELECT ver FROM cts_version) + 1) WHERE addressbook_id = old.addressbook_id;
  67    DELETE FROM my_profiles WHERE addressbook_id = old.addressbook_id;
  68    DELETE FROM contact_deleteds WHERE addressbook_id = old.addressbook_id;
  69    DELETE FROM group_deleteds WHERE addressbook_id = old.addressbook_id;
  70  END;
  71
  72 CREATE TABLE contacts
  73 (
  74         contact_id                      INTEGER PRIMARY KEY AUTOINCREMENT,
  75         person_id                       INTEGER NOT NULL,
  76         addressbook_id                  INTEGER NOT NULL DEFAULT 0,
  77         has_phonenumber         INTEGER,
  78         has_email                       INTEGER,
  79         is_favorite                     INTEGER DEFAULT 0,
  80         deleted                         INTEGER DEFAULT 0,
  81         display_name                    TEXT,
  82         reverse_display_name            TEXT,
  83         display_name_source             INTEGER,
  84         display_name_language           INTEGER,
  85         reverse_display_name_language           INTEGER,
  86         sort_name                       TEXT,
  87         reverse_sort_name               TEXT,
  88         sortkey                         TEXT,
  89         reverse_sortkey                 TEXT,
  90         created_ver                     INTEGER NOT NULL,
  91         changed_ver                     INTEGER NOT NULL,
  92         changed_time                    INTEGER NOT NULL,
  93         link_mode               INTEGER NOT NULL,
  94         image_changed_ver       INTEGER NOT NULL,
  95         uid                             TEXT,
  96         ringtone_path                   TEXT,
  97         vibration                       TEXT,
  98         message_alert           TEXT,
  99         image_thumbnail_path            TEXT,
 100         image_path                      TEXT
 101 );
 102
 103 CREATE INDEX contacts_idx1 ON contacts(changed_ver);
 104 CREATE INDEX contacts_idx2 ON contacts(person_id);
 105 CREATE INDEX contacts_idx3 ON contacts(display_name_language, sortkey);
 106 CREATE INDEX contacts_idx4 ON contacts(reverse_display_name_language, reverse_sortkey);
 107 CREATE INDEX contacts_idx5 ON contacts(addressbook_id);
 108
 109 -- There are three case of deleting contact logically
 110 --   Case 1 : delete contact
 111 --   Case 2 : delete addressbook
 112 --   Case 3 : delete person
 113 -- In all Case, the deleted contacts(deleted=1) are really deleted in the background.
 114 CREATE TRIGGER trg_contacts_del AFTER DELETE ON contacts
 115         BEGIN
 116                 DELETE FROM data WHERE contact_id = old.contact_id AND is_my_profile = 0;
 117                 DELETE FROM group_relations WHERE old.addressbook_id != -1 AND contact_id = old.contact_id;
 118                 DELETE FROM activities WHERE contact_id = old.contact_id;
 119                 DELETE FROM persons WHERE person_id = old.person_id AND link_count = 1;
 120                 DELETE FROM search_index WHERE contact_id = old.contact_id;
 121                 DELETE FROM name_lookup WHERE contact_id = old.contact_id;
 122                 DELETE FROM phone_lookup WHERE contact_id = old.contact_id;
 123                 UPDATE persons SET dirty=1 WHERE person_id = old.person_id AND link_count > 1;
 124         END;
 125
 126 -- It is triggered during really deleting contact in the background (deleted = 1).
 127 -- Deleted version(changed_ver) is already set when updating deleted field as 1.
 128 CREATE TRIGGER trg_contacts_del2 AFTER DELETE ON contacts
 129         WHEN old.addressbook_id = (SELECT addressbook_id from addressbooks WHERE addressbook_id = old.addressbook_id) AND old.deleted = 1
 130         BEGIN
 131                 INSERT INTO contact_deleteds VALUES(old.contact_id, old.addressbook_id, old.created_ver, old.changed_ver);
 132         END;
 133
 134 -- CREATE TRIGGER trg_contacts_del3 AFTER DELETE ON contacts
 135 --      WHEN old.addressbook_id = (SELECT addressbook_id from addressbooks WHERE addressbook_id = old.addressbook_id) AND old.deleted = 0
 136 --      BEGIN
 137 --              INSERT INTO contact_deleteds VALUES(old.contact_id, old.addressbook_id, old.created_ver, (SELECT ver FROM cts_version) + 1);
 138 --      END;
 139
 140 CREATE TRIGGER trg_contacts_update AFTER UPDATE ON contacts
 141         WHEN new.deleted = 1
 142         BEGIN
 143                 SELECT _DATA_DELETE_(data.id, data.datatype) FROM data WHERE contact_id = old.contact_id AND is_my_profile = 0;
 144                 DELETE FROM group_relations WHERE old.addressbook_id != -1 AND contact_id = old.contact_id;
 145                 DELETE FROM persons WHERE person_id = old.person_id AND link_count = 1;
 146                 UPDATE persons SET dirty=1 WHERE person_id = old.person_id AND link_count > 1;
 147                 DELETE FROM speeddials WHERE number_id IN (SELECT id FROM data WHERE data.contact_id = old.contact_id AND datatype = 8);
 148         END;
 149
 150 CREATE TABLE contact_deleteds
 151 (
 152         contact_id                      INTEGER PRIMARY KEY,
 153         addressbook_id          INTEGER NOT NULL,
 154         created_ver                     INTEGER NOT NULL,
 155         deleted_ver                     INTEGER NOT NULL
 156 );
 157 CREATE INDEX contact_deleteds_idx1 ON contact_deleteds(deleted_ver);
 158
 159 CREATE TABLE cts_version
 160 (
 161         ver                             INTEGER PRIMARY KEY
 162 );
 163
 164 INSERT INTO cts_version VALUES(0);
 165
 166 CREATE TABLE sdn
 167 (
 168         id                              INTEGER PRIMARY KEY AUTOINCREMENT,
 169         name                            TEXT,
 170         number                          TEXT,
 171         sim_slot_no             INTEGER
 172 );
 173
 174 CREATE TABLE data
 175 (
 176         id                              INTEGER PRIMARY KEY AUTOINCREMENT,
 177         contact_id                      INTEGER NOT NULL,
 178         datatype                        INTEGER NOT NULL,
 179         is_my_profile           INTEGER,
 180         is_primary_default              INTEGER,
 181         is_default                      INTEGER,
 182         data1                           INTEGER,
 183         data2                           TEXT,
 184         data3                           TEXT,
 185         data4                           TEXT,
 186         data5                           TEXT,
 187         data6                           TEXT,
 188         data7                           TEXT,
 189         data8                           TEXT,
 190         data9                           TEXT,
 191         data10                          TEXT,
 192         data11                          TEXT,
 193         data12                          TEXT
 194 );
 195
 196 CREATE TRIGGER trg_data_image_del AFTER DELETE ON data
 197         WHEN old.datatype = 13
 198                 BEGIN
 199                         SELECT _DATA_IMAGE_DELETE_(old.data3);
 200                 END;
 201
 202 CREATE TRIGGER trg_data_company_del AFTER DELETE ON data
 203         WHEN old.datatype = 6
 204                 BEGIN
 205                         SELECT _DATA_COMPANY_DELETE_(old.data8);
 206                 END;
 207
 208 CREATE TRIGGER trg_data_number_del AFTER DELETE ON data
 209         WHEN old.datatype = 8
 210                 BEGIN
 211                         DELETE FROM speeddials WHERE  number_id = old.id;
 212                 END;
 213
 214 CREATE INDEX data_contact_idx1 ON data(contact_id);
 215 CREATE INDEX data_contact_idx2 ON data(datatype, contact_id);
 216 CREATE INDEX data_idx1 ON data(data1);
 217 CREATE INDEX data_idx2 ON data(data2);
 218 CREATE INDEX data_idx3 ON data(data3);
 219 CREATE INDEX data_idx4 ON data(data4);
 220 CREATE INDEX data_idx5 ON data(data5);
 221 CREATE INDEX data_idx6 ON data(data6);
 222 CREATE INDEX data_idx7 ON data(data7);
 223 CREATE INDEX data_idx8 ON data(data8);
 224 CREATE INDEX data_idx9 ON data(data9);
 225 CREATE INDEX data_idx10 ON data(data10);
 226
 227 CREATE TABLE groups
 228 (
 229         group_id                        INTEGER PRIMARY KEY AUTOINCREMENT,
 230         addressbook_id                  INTEGER,
 231         group_name                      TEXT,
 232         extra_data                      TEXT,
 233         is_read_only                    INTEGER DEFAULT 0,
 234         created_ver                     INTEGER NOT NULL,
 235         changed_ver                     INTEGER NOT NULL,
 236         ringtone_path                   TEXT,
 237         vibration                       TEXT,
 238         image_thumbnail_path            TEXT,
 239         message_alert           TEXT,
 240         member_changed_ver              INTEGER,
 241         group_prio                      REAL
 242 );
 243
 244 INSERT INTO groups(addressbook_id, group_name, extra_data, is_read_only, created_ver, changed_ver, group_prio)
 245         VALUES(0, 'Co-workers', 'coworkers', 0, 0, 0, 1);
 246 INSERT INTO groups(addressbook_id, group_name, extra_data, is_read_only, created_ver, changed_ver, group_prio)
 247         VALUES(0, 'Family', 'family', 0, 0, 0, 2);
 248 INSERT INTO groups(addressbook_id, group_name, extra_data, is_read_only, created_ver, changed_ver, group_prio)
 249         VALUES(0, 'Friends', 'friends',0, 0, 0, 3);
 250
 251 CREATE TRIGGER trg_groups_del AFTER DELETE ON groups
 252  BEGIN
 253         UPDATE contacts SET changed_ver=((SELECT ver FROM cts_version) + 1) WHERE deleted = 0 AND contact_id IN (SELECT contact_id FROM group_relations WHERE group_id=old.group_id);
 254         DELETE FROM group_relations WHERE group_id = old.group_id;
 255         SELECT _GROUP_DELETE_(old.image_thumbnail_path);
 256  END;
 257
 258 CREATE TRIGGER trg_groups_del2 AFTER DELETE ON groups
 259         WHEN old.addressbook_id IN (SELECT addressbook_id from addressbooks WHERE addressbook_id = old.addressbook_id)
 260         BEGIN
 261                 INSERT INTO group_deleteds VALUES(old.group_id, old.addressbook_id, old.created_ver, (SELECT ver FROM cts_version) + 1);
 262         END;
 263
 264 CREATE TABLE group_deleteds
 265 (
 266         group_id                                INTEGER PRIMARY KEY,
 267         addressbook_id          INTEGER NOT NULL,
 268         created_ver                     INTEGER NOT NULL,
 269         deleted_ver                     INTEGER NOT NULL
 270 );
 271
 272 CREATE INDEX group_deleteds_idx1 ON group_deleteds(deleted_ver);
 273
 274 CREATE TABLE group_relations
 275 (
 276         group_id                        INTEGER NOT NULL,
 277         contact_id                      INTEGER NOT NULL,
 278         ver INTEGER NOT NULL,
 279         deleted INTEGER DEFAULT 0,
 280         UNIQUE(group_id, contact_id)
 281 );
 282 CREATE INDEX groups_idx1 ON group_relations(contact_id);
 283
 284
 285 CREATE TABLE speeddials
 286 (
 287         speed_number                    INTEGER PRIMARY KEY NOT NULL,
 288         number_id                       INTEGER UNIQUE
 289 );
 290
 291 CREATE TABLE favorites
 292 (
 293         person_id                       INTEGER PRIMARY KEY,
 294         favorite_prio                   REAL
 295 );
 296 CREATE INDEX favorites_idx1 ON favorites(favorite_prio);
 297 CREATE INDEX favorites_idx2 ON favorites(person_id);
 298
 299
 300 CREATE TABLE sim_info
 301 (
 302         sim_id                  INTEGER PRIMARY KEY AUTOINCREMENT,
 303         unique_id               TEXT NOT NULL,          -- iccid
 304         UNIQUE(unique_id)
 305 );
 306
 307 CREATE TABLE phonelogs
 308 (
 309         id                              INTEGER PRIMARY KEY AUTOINCREMENT,
 310         number                          TEXT,
 311         number_type                     INTEGER,
 312         normal_num                      TEXT,
 313         clean_num                               TEXT,
 314         minmatch                                TEXT,
 315         sim_id                  INTEGER,
 316         person_id                       INTEGER, --person_id
 317         log_type                        INTEGER,
 318         log_time                        INTEGER,
 319         data1                           INTEGER, --duration, message_id
 320         data2                           TEXT  -- short message
 321 );
 322
 323 CREATE INDEX phonelogs_idx1 ON phonelogs(log_type);
 324 CREATE INDEX phonelogs_idx2 ON phonelogs(log_time);
 325 CREATE TRIGGER trg_phonelogs_del AFTER DELETE ON phonelogs
 326         BEGIN
 327                 SELECT _PHONE_LOG_DELETE_(old.id);
 328         END;
 329
 330 CREATE TABLE phonelog_stat
 331 (
 332         log_type                        INTEGER PRIMARY KEY,
 333         log_count                       INTEGER
 334 );
 335
 336 CREATE TRIGGER trg_phonelogs_insert AFTER INSERT ON phonelogs
 337         BEGIN
 338                 INSERT OR REPLACE INTO phonelog_stat values(new.log_type, coalesce((SELECT log_count+1 FROM phonelog_stat WHERE log_type=new.log_type), 1));
 339         END;
 340
 341 CREATE TABLE contact_stat
 342 (
 343         id                              INTEGER PRIMARY KEY AUTOINCREMENT,
 344         person_id                       INTEGER,
 345         usage_type                      INTEGER,
 346         times_used                      INTEGER
 347 );
 348
 349 CREATE TABLE activities
 350 (
 351         id                              INTEGER PRIMARY KEY AUTOINCREMENT,
 352         contact_id                      INTEGER NOT NULL,
 353         source_name                     TEXT,
 354         status                          TEXT,
 355         timestamp                       INTEGER,
 356         service_operation       TEXT,
 357         uri                                     TEXT
 358 );
 359
 360 CREATE TABLE activity_photos
 361 (
 362         id                              INTEGER PRIMARY KEY AUTOINCREMENT,
 363         activity_id                     INTEGER NOT NULL,
 364         photo_url                       TEXT,
 365         sort_index                      INTEGER
 366 );
 367
 368 CREATE TRIGGER trg_activities_insert AFTER INSERT ON activities
 369         BEGIN
 370                 UPDATE persons SET status=(SELECT status FROM activities WHERE contact_id IN (SELECT contact_id FROM contacts WHERE person_id = (select person_id FROM contacts WHERE contact_id = new.contact_id)) ORDER BY timestamp DESC LIMIT 1)  WHERE person_id = (SELECT person_id FROM contacts WHERE contact_id = new.contact_id);
 371         END;
 372
 373 CREATE TRIGGER trg_activities_delete AFTER DELETE ON activities
 374         BEGIN
 375                 UPDATE persons SET status=(SELECT status FROM activities WHERE contact_id IN (SELECT contact_id FROM contacts WHERE person_id = (select person_id FROM contacts WHERE contact_id = old.contact_id)) ORDER BY timestamp DESC LIMIT 1)  WHERE person_id = (SELECT person_id FROM contacts WHERE contact_id = old.contact_id);
 376                 DELETE FROM activity_photos WHERE activity_id = old.id;
 377         END;
 378
 379 CREATE VIRTUAL TABLE search_index USING FTS4
 380 (
 381         contact_id INTEGER NOT NULL,
 382         data TEXT,
 383         name TEXT,
 384         number TEXT,
 385         UNIQUE(contact_id)
 386 );
 387
 388 CREATE TABLE name_lookup
 389 (
 390         data_id INTEGER NOT NULL,
 391         contact_id INTEGER NOT NULL,
 392         name TEXT,
 393         type INTEGER
 394 );
 395 CREATE INDEX name_lookup_idx1 ON name_lookup(contact_id);
 396
 397 CREATE TABLE phone_lookup
 398 (
 399         data_id INTEGER NOT NULL,
 400         contact_id INTEGER NOT NULL,
 401         number TEXT,
 402         min_match TEXT
 403 );
 404 CREATE INDEX phone_lookup_idx1 ON phone_lookup(contact_id);
 405
 406 CREATE TABLE my_profiles
 407 (
 408         my_profile_id                   INTEGER PRIMARY KEY AUTOINCREMENT,
 409         addressbook_id                  INTEGER NOT NULL DEFAULT 0,
 410         display_name                    TEXT,
 411         reverse_display_name            TEXT,
 412         created_ver                     INTEGER NOT NULL,
 413         changed_ver                     INTEGER NOT NULL,
 414         changed_time                    INTEGER NOT NULL,
 415         uid                             TEXT,
 416         image_thumbnail_path            TEXT,
 417         deleted                         INTEGER DEFAULT 0,
 418         UNIQUE(addressbook_id)
 419 );
 420
 421 CREATE TRIGGER trg_my_profiles_del AFTER DELETE ON my_profiles
 422         BEGIN
 423                 DELETE FROM data WHERE contact_id = old.my_profile_id AND is_my_profile = 1;
 424         END;
 425
 426 CREATE TRIGGER trg_my_profile_update AFTER UPDATE ON my_profiles
 427         WHEN new.deleted = 1
 428         BEGIN
 429                 DELETE FROM data WHERE contact_id = old.my_profile_id AND is_my_profile = 1;
 430         END;
 431