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