1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
|
CREATE TABLE mail_account_tbl
(
account_id INTEGER PRIMARY KEY,
account_name VARCHAR(51),
logo_icon_path VARCHAR(256),
user_data BLOB,
user_data_length INTEGER,
account_svc_id INTEGER,
sync_status INTEGER,
sync_disabled INTEGER,
default_mail_slot_size INTEGER,
roaming_option INTEGER,
user_display_name VARCHAR(31),
user_email_address VARCHAR(129),
reply_to_address VARCHAR(129),
return_address VARCHAR(129),
incoming_server_type INTEGER,
incoming_server_address VARCHAR(51),
incoming_server_port_number INTEGER,
incoming_server_user_name VARCHAR(51),
incoming_server_password VARCHAR(51),
incoming_server_secure_connection INTEGER,
incoming_server_authentication_method INTEGER,
retrieval_mode INTEGER,
keep_mails_on_pop_server_after_download INTEGER,
check_interval INTEGER,
auto_download_size INTEGER,
peak_interval INTEGER,
peak_days INTEGER,
peak_start_time INTEGER,
peak_end_time INTEGER,
outgoing_server_type INTEGER,
outgoing_server_address VARCHAR(51),
outgoing_server_port_number INTEGER,
outgoing_server_user_name VARCHAR(51),
outgoing_server_password VARCHAR(51),
outgoing_server_secure_connection INTEGER,
outgoing_server_need_authentication INTEGER,
outgoing_server_use_same_authenticator INTEGER,
priority INTEGER,
keep_local_copy INTEGER,
req_delivery_receipt INTEGER,
req_read_receipt INTEGER,
download_limit INTEGER,
block_address INTEGER,
block_subject INTEGER,
display_name_from VARCHAR(256),
reply_with_body INTEGER,
forward_with_files INTEGER,
add_myname_card INTEGER,
add_signature INTEGER,
signature VARCHAR(256),
add_my_address_to_bcc INTEGER,
auto_resend_times INTEGER,
outgoing_server_size_limit INTEGER,
pop_before_smtp INTEGER,
incoming_server_requires_apop INTEGER,
smime_type INTEGER,
certificate_path VARCHAR(256),
cipher_type INTEGER,
digest_type INTEGER
);
CREATE TABLE mail_box_tbl
(
mailbox_id INTEGER PRIMARY KEY,
account_id INTEGER,
local_yn INTEGER,
mailbox_name VARCHAR(256),
mailbox_type INTEGER,
alias VARCHAR(256),
deleted_flag INTEGER,
modifiable_yn INTEGER,
total_mail_count_on_server INTEGER,
has_archived_mails INTEGER,
mail_slot_size INTEGER,
no_select INTEGER,
last_sync_time DATETIME,
eas_data_length INTEGER,
eas_data BLOB
);
CREATE TABLE mail_read_mail_uid_tbl
(
account_id INTEGER ,
mailbox_id INTEGER ,
mailbox_name VARCHAR(256) ,
local_uid INTEGER ,
server_uid VARCHAR(129) ,
rfc822_size INTEGER ,
sync_status INTEGER ,
flags_seen_field INTEGER ,
flags_flagged_field INTEGER ,
idx_num INTEGER PRIMARY KEY
);
CREATE TABLE mail_rule_tbl
(
account_id INTEGER ,
rule_id INTEGER PRIMARY KEY,
filter_name VARCHAR(257) ,
type INTEGER ,
value VARCHAR(257) ,
value2 VARCHAR(257) ,
action_type INTEGER ,
target_mailbox_id INTEGER ,
flag1 INTEGER ,
flag2 INTEGER
);
CREATE TABLE mail_tbl
(
mail_id INTEGER PRIMARY KEY,
account_id INTEGER,
mailbox_id INTEGER,
mailbox_type INTEGER,
subject TEXT,
date_time DATETIME,
server_mail_status INTEGER,
server_mailbox_name VARCHAR(129),
server_mail_id VARCHAR(129),
message_id VARCHAR(257),
reference_mail_id INTEGER,
full_address_from TEXT,
full_address_reply TEXT,
full_address_to TEXT,
full_address_cc TEXT,
full_address_bcc TEXT,
full_address_return TEXT,
email_address_sender TEXT collation user1,
email_address_recipient TEXT collation user1,
alias_sender TEXT,
alias_recipient TEXT,
body_download_status INTEGER,
file_path_plain VARCHAR(257),
file_path_html VARCHAR(257),
file_path_mime_entity VARCHAR(257),
mail_size INTEGER,
flags_seen_field BOOLEAN,
flags_deleted_field BOOLEAN,
flags_flagged_field BOOLEAN,
flags_answered_field BOOLEAN,
flags_recent_field BOOLEAN,
flags_draft_field BOOLEAN,
flags_forwarded_field BOOLEAN,
DRM_status INTEGER,
priority INTEGER,
save_status INTEGER,
lock_status INTEGER,
report_status INTEGER,
attachment_count INTEGER,
inline_content_count INTEGER,
thread_id INTEGER,
thread_item_count INTEGER,
preview_text TEXT,
meeting_request_status INTEGER,
message_class INTEGER,
digest_type INTEGER,
smime_type INTEGER,
scheduled_sending_time DATETIME,
remaining_resend_times INTEGER,
tag_id INTEGER,
replied_time DATETIME,
forwarded_time DATETIME,
eas_data_length INTEGER,
eas_data BLOB,
FOREIGN KEY(account_id) REFERENCES mail_account_tbl(account_id)
);
CREATE TABLE mail_attachment_tbl
(
attachment_id INTEGER PRIMARY KEY,
attachment_name VARCHAR(257),
attachment_path VARCHAR(257),
attachment_size INTEGER,
mail_id INTEGER,
account_id INTEGER,
mailbox_id INTEGER,
attachment_save_status INTEGER,
attachment_drm_type INTEGER,
attachment_drm_method INTEGER,
attachment_inline_content_status INTEGER,
attachment_mime_type VARCHAR(257)
);
CREATE TABLE mail_partial_body_activity_tbl
(
account_id INTEGER,
mail_id INTEGER,
server_mail_id INTEGER,
activity_id INTEGER PRIMARY KEY,
activity_type INTEGER,
mailbox_id INTEGER,
mailbox_name VARCHAR(4000)
);
CREATE TABLE mail_meeting_tbl
(
mail_id INTEGER PRIMARY KEY,
account_id INTEGER,
mailbox_id INTEGER,
meeting_response INTEGER,
start_time INTEGER,
end_time INTEGER,
location TEXT ,
global_object_id TEXT ,
offset INTEGER,
standard_name TEXT ,
standard_time_start_date INTEGER,
standard_bias INTEGER,
daylight_name TEXT ,
daylight_time_start_date INTEGER,
daylight_bias INTEGER
);
CREATE TABLE mail_local_activity_tbl
(
activity_id INTEGER,
account_id INTEGER,
mail_id INTEGER,
activity_type INTEGER,
server_mailid VARCHAR(129),
src_mbox VARCHAR(129),
dest_mbox VARCHAR(129)
);
CREATE TABLE mail_certificate_tbl
(
certificate_id INTEGER,
issue_year INTEGER,
issue_month INTEGER,
issue_day INTEGER,
expiration_year INTEGER,
expiration_month INTEGER,
expiration_day INTEGER,
issue_organization_name VARCHAR(256),
email_address VARCHAR(129),
subject_str VARCHAR(256),
filepath VARCHAR(256),
password VARCHAR(51)
);
CREATE TABLE mail_task_tbl
(
task_id INTEGER PRIMARY KEY,
task_type INTEGER,
task_status INTEGER,
task_priority INTEGER,
task_parameter_length INTEGER,
task_parameter BLOB,
date_time DATETIME
);
CREATE VIRTUAL TABLE mail_text_tbl USING fts4
(
mail_id INTEGER,
account_id INTEGER,
mailbox_id INTEGER,
body_text TEXT
);
CREATE UNIQUE INDEX mail_account_idx1 ON mail_account_tbl (account_id);
CREATE UNIQUE INDEX mail_box_idx1 ON mail_box_tbl (mailbox_id);
CREATE UNIQUE INDEX mail_read_mail_uid_idx1 ON mail_read_mail_uid_tbl (account_id, mailbox_id, local_uid, mailbox_name, server_uid);
CREATE UNIQUE INDEX mail_idx1 ON mail_tbl (mail_id, account_id);
CREATE UNIQUE INDEX mail_attachment_idx1 ON mail_attachment_tbl (mail_id, attachment_id);
CREATE UNIQUE INDEX mail_meeting_idx1 ON mail_meeting_tbl (mail_id);
CREATE UNIQUE INDEX task_idx1 ON mail_task_tbl (task_id);
CREATE INDEX mail_idx_date_time ON mail_tbl (date_time);
CREATE INDEX mail_idx_thread_item_count ON mail_tbl (thread_item_count);
CREATE TRIGGER update_flags_seen_field UPDATE OF flags_seen_field ON mail_tbl
BEGIN
UPDATE mail_read_mail_uid_tbl SET flags_seen_field = new.flags_seen_field WHERE local_uid = old.mail_id;
END;
CREATE TRIGGER update_flags_flagged_field UPDATE OF flags_flagged_field ON mail_tbl
BEGIN
UPDATE mail_read_mail_uid_tbl SET flags_flagged_field = new.flags_flagged_field WHERE local_uid = old.mail_id;
END;
|