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