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;