res/email-service.sql

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