schema.sql

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