4 -- Copyright (c) 2010 - 2012 Samsung Electronics Co., Ltd. All rights reserved.
6 -- Contact: Youngjae Shin <yj99.shin@samsung.com>
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
12 -- http://www.apache.org/licenses/LICENSE-2.0
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.
21 --PRAGMA journal_mode = PERSIST;
22 --PRAGMA journal_mode = TRUNCATE;
24 CREATE TABLE addressbooks
26 addrbook_id INTEGER PRIMARY KEY AUTOINCREMENT,
29 acc_type INTEGER DEFAULT 0,
30 mode INTEGER, -- permission
33 --CREATE TRIGGER trg_addressbook_sync AFTER UPDATE OF last_sync_ver ON addressbooks
35 -- DELETE FROM deleteds WHERE addrbook_id = new.addrbook_id and deleted_time <= new.last_sync_ver;
37 CREATE TRIGGER trg_addressbook_del AFTER DELETE ON addressbooks
39 DELETE FROM groups WHERE addrbook_id = old.addrbook_id;
40 DELETE FROM contacts WHERE addrbook_id = old.addrbook_id;
41 DELETE FROM deleteds WHERE addrbook_id = old.addrbook_id;
46 contact_id INTEGER PRIMARY KEY AUTOINCREMENT,
47 addrbook_id INTEGER NOT NULL DEFAULT 0,
49 default_email INTEGER,
51 is_favorite INTEGER DEFAULT 0,
52 created_ver INTEGER NOT NULL,
53 changed_ver INTEGER NOT NULL,
54 changed_time INTEGER NOT NULL,
55 outgoing_count INTEGER DEFAULT 0,
59 image0 TEXT, -- normal image
60 image1 TEXT, -- full image
63 CREATE INDEX contacts_ver_idx ON contacts(changed_ver);
64 CREATE INDEX contacts_person_idx ON contacts(person_id);
65 CREATE TRIGGER trg_contacts_del AFTER DELETE ON contacts
67 DELETE FROM data WHERE contact_id = old.contact_id;
68 DELETE FROM group_relations WHERE old.addrbook_id != -1 AND contact_id = old.contact_id;
69 DELETE FROM favorites WHERE type = 0 AND related_id = old.contact_id;
78 CREATE INDEX deleteds_ver_idx ON deleteds(deleted_ver);
80 CREATE TABLE cts_version
82 ver INTEGER PRIMARY KEY
84 INSERT INTO cts_version VALUES(0);
86 CREATE TABLE sim_services
88 id INTEGER PRIMARY KEY AUTOINCREMENT,
94 CREATE TABLE custom_types
96 id INTEGER PRIMARY KEY AUTOINCREMENT,
101 CREATE INDEX idx_custom_type ON custom_types(class, name);
102 CREATE TRIGGER trg_custom_types_del AFTER DELETE ON custom_types
104 UPDATE data SET data1 = 0 WHERE old.class = 1 AND number_type = old.id AND datatype = 8;
109 id INTEGER PRIMARY KEY AUTOINCREMENT,
110 contact_id INTEGER NOT NULL,
111 datatype INTEGER NOT NULL,
124 CREATE TRIGGER trg_data_number_del AFTER DELETE ON data
125 WHEN old.datatype = 8
127 DELETE FROM favorites WHERE type = 1 AND related_id = old.id;
128 DELETE FROM speeddials WHERE number_id = old.id;
130 CREATE INDEX data_contact_idx ON data(contact_id);
131 CREATE INDEX data_contact_idx2 ON data(datatype, contact_id);
132 CREATE INDEX data_idx1 ON data(data1);
133 CREATE INDEX data_idx2 ON data(data2);
134 CREATE INDEX data_idx3 ON data(data3);
135 CREATE INDEX data_idx4 ON data(data4);
136 CREATE INDEX data_idx5 ON data(data5);
137 CREATE INDEX data_idx6 ON data(data6);
138 CREATE INDEX data_idx7 ON data(data7);
139 CREATE INDEX data_idx8 ON data(data8);
140 CREATE INDEX data_idx9 ON data(data9);
141 CREATE INDEX data_idx10 ON data(data10);
142 CREATE INDEX data_person_idx ON data(person_id);
146 group_id INTEGER PRIMARY KEY AUTOINCREMENT,
150 UNIQUE(addrbook_id, group_name)
152 CREATE TRIGGER trg_groups_del AFTER DELETE ON groups
154 DELETE FROM group_relations WHERE group_id = old.group_id;
157 CREATE TABLE group_relations
159 group_id INTEGER NOT NULL,
160 contact_id INTEGER NOT NULL,
161 UNIQUE(group_id, contact_id)
163 CREATE INDEX group_idx1 ON group_relations(contact_id);
165 CREATE TABLE speeddials
167 speed_num INTEGER PRIMARY KEY NOT NULL,
168 number_id INTEGER UNIQUE
171 CREATE TABLE favorites
173 id INTEGER PRIMARY KEY AUTOINCREMENT,
174 type INTEGER NOT NULL,
175 related_id INTEGER NOT NULL,
177 UNIQUE(type, related_id)
179 CREATE INDEX idx1_favorites ON favorites(favorite_prio);
180 CREATE INDEX idx2_favorites ON favorites(type, related_id);
181 CREATE TRIGGER trg_favorite_del BEFORE DELETE ON favorites
183 UPDATE data SET data3 = 0 WHERE old.type = 1 AND id = old.related_id AND datatype = 8;
184 UPDATE contacts SET is_favorite = 0 WHERE old.type = 0 AND contact_id = old.related_id;
186 CREATE TRIGGER trg_favorite_insert AFTER INSERT ON favorites
188 UPDATE data SET data3 = 1 WHERE new.type = 1 AND id = new.related_id AND datatype = 8;
189 UPDATE contacts SET is_favorite = 1 WHERE new.type = 0 AND contact_id = new.related_id;
192 CREATE TABLE phonelogs
194 id INTEGER PRIMARY KEY AUTOINCREMENT,
197 related_id INTEGER, --contact_id
200 data1 INTEGER, --duration, message_id
201 data2 TEXT -- short message
203 CREATE INDEX idx1_phonelogs ON phonelogs(log_type);
204 CREATE INDEX idx2_phonelogs ON phonelogs(log_time);
205 CREATE TRIGGER trg_phonelogs_del AFTER DELETE ON phonelogs
206 WHEN old.log_type = 2 OR old.log_type = 4
208 DELETE FROM phonelog_accumulation WHERE log_time < (old.log_time - 3456000); -- 40 days
209 INSERT INTO phonelog_accumulation VALUES(NULL, 1, old.log_time, old.data1);
212 CREATE TABLE phonelog_accumulation
214 id INTEGER PRIMARY KEY AUTOINCREMENT,
219 INSERT INTO phonelog_accumulation VALUES(1, 0, NULL, 0);
220 INSERT INTO phonelog_accumulation VALUES(2, 0, NULL, 0); --total