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                 DELETE FROM group_relations WHERE old.addressbook_id != -1 AND contact_id = old.contact_id;
 142                 DELETE FROM persons WHERE person_id = old.person_id AND link_count = 1;
 143                 UPDATE persons SET dirty=1 WHERE person_id = old.person_id AND link_count > 1;
 144         END;
 145
 146 CREATE TABLE contact_deleteds
 147 (
 148         contact_id                      INTEGER PRIMARY KEY,
 149         addressbook_id          INTEGER NOT NULL,
 150         created_ver                     INTEGER NOT NULL,
 151         deleted_ver                     INTEGER NOT NULL
 152 );
 153 CREATE INDEX contact_deleteds_idx1 ON contact_deleteds(deleted_ver);
 154
 155 CREATE TABLE cts_version
 156 (
 157         ver                             INTEGER PRIMARY KEY
 158 );
 159
 160 INSERT INTO cts_version VALUES(0);
 161
 162 CREATE TABLE sdn
 163 (
 164         id                              INTEGER PRIMARY KEY AUTOINCREMENT,
 165         name                            TEXT,
 166         number                          TEXT
 167 );
 168
 169 CREATE TABLE data
 170 (
 171         id                              INTEGER PRIMARY KEY AUTOINCREMENT,
 172         contact_id                      INTEGER NOT NULL,
 173         datatype                        INTEGER NOT NULL,
 174         is_my_profile           INTEGER,
 175         is_primary_default              INTEGER,
 176         is_default                      INTEGER,
 177         data1                           INTEGER,
 178         data2                           TEXT,
 179         data3                           TEXT,
 180         data4                           TEXT,
 181         data5                           TEXT,
 182         data6                           TEXT,
 183         data7                           TEXT,
 184         data8                           TEXT,
 185         data9                           TEXT,
 186         data10                          TEXT,
 187         data11                          TEXT,
 188         data12                          TEXT
 189 );
 190
 191 CREATE TRIGGER trg_data_del AFTER DELETE ON data
 192         BEGIN
 193                 SELECT _DATA_DELETE_(old.id, old.datatype);
 194         END;
 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 TRIGGER trg_favorites_del BEFORE DELETE ON favorites
 301 --      BEGIN
 302 --              UPDATE contacts SET is_favorite = 0 WHERE person_id = old.person_id;
 303 --      END;
 304 --CREATE TRIGGER trg_favorites_insert AFTER INSERT ON favorites
 305 --      BEGIN
 306 --              UPDATE contacts SET is_favorite = 1 WHERE person_id = new.person_id;
 307 --      END;
 308
 309
 310 CREATE TABLE phonelogs
 311 (
 312         id                              INTEGER PRIMARY KEY AUTOINCREMENT,
 313         number                          TEXT,
 314         normal_num                      TEXT,
 315         person_id                       INTEGER, --person_id
 316         log_type                        INTEGER,
 317         log_time                        INTEGER,
 318         data1                           INTEGER, --duration, message_id
 319         data2                           TEXT  -- short message
 320 );
 321
 322 CREATE INDEX phonelogs_idx1 ON phonelogs(log_type);
 323 CREATE INDEX phonelogs_idx2 ON phonelogs(log_time);
 324 CREATE TRIGGER trg_phonelogs_del AFTER DELETE ON phonelogs
 325         BEGIN
 326                 SELECT _PHONE_LOG_DELETE_(old.id);
 327         END;
 328
 329 --CREATE TRIGGER trg_phonelogs_del AFTER DELETE ON phonelogs
 330 --      WHEN old.log_type = 2 OR old.log_type = 4
 331 --              BEGIN
 332 --                      DELETE FROM phonelog_accumulation WHERE log_time < (old.log_time - 3456000); -- 40 days
 333 --                      INSERT INTO phonelog_accumulation VALUES(NULL, 1, old.log_time, old.data1);
 334 --              END;
 335
 336 --CREATE TABLE phonelog_accumulation
 337 --(
 338 --      id                              INTEGER PRIMARY KEY AUTOINCREMENT,
 339 --      log_cnt                 INTEGER,
 340 --      log_time                        INTEGER,
 341 --      duration                        INTEGER
 342 --);
 343 --INSERT INTO phonelog_accumulation VALUES(1, 0, NULL, 0);
 344 --INSERT INTO phonelog_accumulation VALUES(2, 0, NULL, 0); --total
 345
 346 CREATE TABLE phonelog_stat
 347 (
 348         log_type                        INTEGER PRIMARY KEY,
 349         log_count                       INTEGER
 350 );
 351
 352 CREATE TRIGGER trg_phonelogs_insert AFTER INSERT ON phonelogs
 353         BEGIN
 354                 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));
 355         END;
 356
 357 CREATE TABLE contact_stat
 358 (
 359         id                              INTEGER PRIMARY KEY AUTOINCREMENT,
 360         person_id                       INTEGER,
 361         usage_type                      INTEGER,
 362         times_used                      INTEGER
 363 );
 364
 365 CREATE TABLE activities
 366 (
 367         id                              INTEGER PRIMARY KEY AUTOINCREMENT,
 368         contact_id                      INTEGER NOT NULL,
 369         source_name                     TEXT,
 370         status                          TEXT,
 371         timestamp                       INTEGER,
 372         service_operation       TEXT,
 373         uri                                     TEXT
 374 );
 375
 376 CREATE TABLE activity_photos
 377 (
 378         id                              INTEGER PRIMARY KEY AUTOINCREMENT,
 379         activity_id                     INTEGER NOT NULL,
 380         photo_url                       TEXT,
 381         sort_index                      INTEGER
 382 );
 383
 384 CREATE TRIGGER trg_activities_insert AFTER INSERT ON activities
 385         BEGIN
 386                 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);
 387         END;
 388
 389 CREATE TRIGGER trg_activities_delete AFTER DELETE ON activities
 390         BEGIN
 391                 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);
 392                 DELETE FROM activity_photos WHERE activity_id = old.id;
 393         END;
 394
 395 CREATE VIRTUAL TABLE search_index USING FTS4
 396 (
 397         contact_id INTEGER NOT NULL,
 398         data TEXT,
 399         name TEXT,
 400         number TEXT,
 401         UNIQUE(contact_id)
 402 );
 403
 404 CREATE TABLE name_lookup
 405 (
 406         data_id INTEGER NOT NULL,
 407         contact_id INTEGER NOT NULL,
 408         name TEXT,
 409         type INTEGER
 410 );
 411
 412 CREATE TABLE phone_lookup
 413 (
 414         data_id INTEGER NOT NULL,
 415         contact_id INTEGER NOT NULL,
 416         number TEXT,
 417         min_match TEXT
 418 );
 419
 420
 421 CREATE TABLE my_profiles
 422 (
 423         my_profile_id                   INTEGER PRIMARY KEY AUTOINCREMENT,
 424         addressbook_id                  INTEGER NOT NULL DEFAULT 0,
 425         display_name                    TEXT,
 426         reverse_display_name            TEXT,
 427         created_ver                     INTEGER NOT NULL,
 428         changed_ver                     INTEGER NOT NULL,
 429         changed_time                    INTEGER NOT NULL,
 430         uid                             TEXT,
 431         image_thumbnail_path            TEXT,
 432         deleted                         INTEGER DEFAULT 0,
 433         UNIQUE(addressbook_id)
 434 );
 435
 436 CREATE TRIGGER trg_my_profiles_del AFTER DELETE ON my_profiles
 437         BEGIN
 438                 DELETE FROM data WHERE contact_id = old.my_profile_id AND is_my_profile = 1;
 439         END;
 440
 441 CREATE TRIGGER trg_my_profile_update AFTER UPDATE ON my_profiles
 442         WHEN new.deleted = 1
 443         BEGIN
 444                 DELETE FROM data WHERE contact_id = old.my_profile_id AND is_my_profile = 1;
 445         END;
 446