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