_ _ ___ _ _ / \ / \ / \ | | | | | \ / | / _ \ | | | | | |\\//| | | |_| | | | | | | | \/ | | | __ | | | | | | | | | | | | | | | | |___ |_| |_| |_| |_| |_| |_____| (c) Tomas Siegl (c) Orcave Inc. (c) 20002 Contents: CBS Mail module History: 01/09/2002 Sigi - created 1.0 Description --------------- - There exist 2 types of mail accounts: - System user: Ordinary unix system user accounts mails are stored in Maildirs or mailbox, there is not necessary to exist system account for mail account. - Cbs email users: This is only cbs users and mails are stored in db only. - CBS can read and set mailer configuration using SAuth client 2.0 Recieving Mails from net ---------------------------- - Every incoming mail is processed with local mailer daemon and passed throught its queues. Local mailer daemon perform all dirty jobs like: check if email adress is valid, user exist, perform bounce etc. CBS Mail will collect only valid mails which can be delivered because user exist. Internet TCP/IP(SMTP) -> Qmail -> Qmail-Queues -> Qmail-local -> .qmail -> mail-collect.cpp -> DB ^ error | ok |--------------------------------------------- - All mails account can share one .qmail file, this is set in /users/assign - Every CBS user has its table in db for incomming mails, where are mails stored in raw format. This table uses also pop3 client for accessing mail see later. 3.0 Databse Design ------------------ 3.1 Basic Terms --------------- - Every mail 'finish' in one or more physical 'mailbox'. It is not neccessary to have mailbox for every e-mail address because mailboxes can have specified more 'rcpt' e-mail addreses (Logic addresses). - if somebody send mail, each mailbox which has this physical email adress in recepient list will obtain this message into 'inbox' folder. - One email can be delivered to more mailboxes, if more mailboxes has specified logic email address which contains this delivery physical address 1) email has 'mailbox' - it is default mailbox email name and this 'is' private mailbox!!! so this mailbox has specified more 'MAIL_FORWARD_MAILBOXES' and here are stored which emails adress will obtain copy of the mail. (Circle dependency is automaticaly check for internal account). - Cbs User has relation to mailboxes, Cbs users has specified which mailboxes can access (if they have acl rules for accessing mailboxes ofcourse). - Mails can be stored into tree with 'FOLDERS'. One mail can be stored only in one folder, otherwise copy of mail must be created and stored into other folder. - One folder is special, and has id 0, this folder is called inbox, and here are stored new incoming unread mails name of this folder is specified in 'mailbox' setting. 3.2 Basic Term continue ----------------------- 3.2.1 Address ------------- Logical - It is group of internet email address (etc. Jon.Woo@internet.com). Logic addresses are placed in the tree and use alias and other cbs tree property. Physical - It is internet email address itselfs, it is stored in Logic Address. 4.0 TABLES ---------- 4.1 e-mail adresses ------------------- - following 2 tables (4.1.0 and 4.1.1) specify emails addresses which cbs mail module accept 4.1.0 MAILRCPT -------------- - table is generated - this table specify which email address are delivered to mailbox. One mailbox can have more emails and one email can have more mailboxes column: [int] mailboxid (relation for mailbox) [string] email (for faster search) [int] inboxid (inbox folder id); 4.1.1 MAILADDRESSES ------------------- - Here are specified every logical e-mail addresses - Table is editable. column: [int] id (autoincrement >0) [string] alias (tree) [string] aliasfull (tree) [int] treenode (tree) [string] comment (tree) [string] address (email address which cbs accepts,it must be unique coma separated list) [int] type (flags: private/shared,private - only one mailbox has this rcpt adress) 4.1.2 MAILPHYSADDRESS ---------------------- - Here are stored all physical email addresses for every logical address. column: [int] id (autoincrement >0) [int] addressid relation to MAILADDRESSES [int] username name part of email address john -> john@unreal64.net [int] address domain name etc. unreal64.net 4.1.3 MAILBOXESADDRESS ---------------------- - Relation between mailboxes and addresses column: [int] mailboxid (relation to mailbox) [int] addressid (relation to address) 4.2 MAILBOXES ------------- - mail accounts - mailbox can be also readonly (like bugs@ or sendonly noreply@ ) - if mail is sendonly there is not coresponing MAILRCPT record but ! - one cbs user can have more mailboxes !!! column: [int] id (auto increment >0) [string] alias (tree) [string] aliasfull (tree) [int] treenode (tree) [string] comment (tree) [int] defprofile (default profile) [int] type (flags: readonly/sendonly) [int] maxsize (maximum allowed size of mailbox, 0 unlimited) [int] maxcount (maximum number of mails in mailbox) [int] curbodysize (size of all mails body) [int] curattachsize (size of all attachemets) [int] vpropshowrows (how many rows you can see in one page) [int] viewseclev (security level of viewing mails) [int] inboxid (which folder is used as inbox and can't be deleted by user) [int] sentboxid (which folder is used as sentbox and can't be deleted by user) [int] trashboxid (which folder is used as trashbox and can't be deleted by user) [int] draftboxid (which folder is used as draftbox an can't be deleted by user) [int] defentryfolder (default entry folder for this mailbox - initiali Inbox) [int] sysowner (system user owner for this mailbox, required for ACL) 4.2.1 MAIL_id -> etc. mail_1 ----------------------------------------- - Here are deliverd mails for concrete mailbox - name contains id of Mailbox which owns this mails - all mails for all folders are stored here column: [int] id (autoincrement >0) [string] efrom (mail sender) UTF-8 [string] ecc UTF-8 [string[ ebcc (for send mails :)) UTF-8 [string] subject UTF-8 [timestamp] sendtime (time wher mail was send) [timestamp] arivtime (time where mail arrived) [int] priority [string] body (parsed body) UTF-8 [int] bodysize (size of mail body) [txt] bodytype (html, text, both) [int] attachsize (size of attachements) [int] rawheader (original header) as is [int] popflags (for pop3 server) [int] folderid (which folder mail occupy) [int] checkedbyid (last error) [int] flags (various flags, used when compousing mail to mark that this mail is not send yet) 4.2.1.0 MAIL_id_STORAGE // etc. mail_1_storage ---------------------------------------------- - here is copy of original mail column: [int] mailid (mail reference) [string] originalmail (pop3 access and decode) base64 4.2.1.1 MAIL_id_ATTACHEMENTS //etc. mail_1_attachements ------------------------------------------------------- - here are stored info of attachements in mail column: [int] id (autoincrement >0) [int] mailid [string] attachdata base64 [string] mimetype [string] encoding [string] filename [int] fsize 4.3 MAILBOXESUSERS ------------------ - relation between cbs user and mailbox - it says which mailboxes can cbs user access if has general acl rule for accessing mails. column: [int] userid (cbs userid) [int] mailboxid (mailbox id) 4.4 MAILDOMAINS --------------- - All availible domains, which can be used for mails (orcave.com, list.orcave.com) column: [int] id (auto increment >0) [string] name (list.orcave.com) 4.5 MAILFOLDERS --------------- - Mailbox can keep mails in direrent folders. Inbox can be deleted column: [int] id (auto increment >0) [int] mailboxid (which mailbox owns this folder) [string] alias (used as name) [string] aliasfull (tree) [int] treenode (tree) [string] comment (tree) [int] fcount (how many mails folder contains) [int] unread (number of unread mesaaged) [ing] type (flags: behavior of folder, automatically open if contains unread message,etc.) [int] profile (default profile for this folder) 4.6 Signatures -------------- 4.6.1 MAILSIGNATURESPRIVATE --------------------------- - mail signatures for all users - when is used text is inserted into mail, no global change when change column: [int] id (autoincrement>0) [int] mailboxid (owner) [string] data (text of signature itselfs) utf-8 [string] comment (short description, must be filled) 4.6.2 MAILSIGNATURESHARES ------------------------- - mail signatures for all users - when is used text is inserted into mail, no global change when change column: [int] id (autoincrement>0) [string] data (text of signature itselfs) [string] alias (used as name) [string] aliasfull (tree) [int] treenode (tree) [string] comment (short description) 4.7 MAILPROFILES ---------------- - here are rules for new mails - not finished how it be designed - check for user input !!!! for sql injection, java script, etc... column: [int] id (autoincrement >0) [int] mailboxid [string] comment (comment) [int] addressid (physical email adress) [string] name (used for sending mails from this mailbox) utf-8 [string] signatureshared (default shared signature for this profile) [string] signatureprivate (default private signature for this profile) 4.8 MAILSECURITYLEVELS ---------------------- - configuration of viewing level column: [int] id (autoincrement >0) [string] setting (setting) [string] comment (short description) 4.9 MAILRULES ------------- - here are rules for new mails - not finished how it be designed - check for user input !!!! for sql injection, java script, etc... column: [int] id (autoincrement >0) [int] mailboxid (mailbox reference) [string] alias [string] pos (when this rules is used) [string] dstfldr (what to do with it) [int] ruleact (action when subrule is true) 4.9.1 MAILSUBRULES ------------------ column: [int] id (autoincrement >0) [int] ruleid (reference to rule) [int] oper (AND, OR, NOT AND, NOT OR) [int] relation (equal ,not equal) [string] srcitem (what compare, 1-from, 2-to, 3-cc, 4-(from or cc), 5-subject, 6-body) [string] cmpstr (compare string) utf-8 5.0 MAILDEBUG ------------- - debug information, actual count of bad mails, and current value id for bad mails marking. column: [int] badmailscount (current bad mails in mailboxes) [int] curbadid (current bad id, after CbsMail.php modification inc this id) 6.0 Operation ------------- Mailbox - Create New mailbox - Remove mailbox - Modify Existing - Modify Personal Data (Name,signature,... this can do every user) - Modify cbs user (to change user, or to save during old user delete) - Modify rcpt email adress for this mailbox - Modify type (private/shared) - Search - Folder - create new folder - remove existing (include all mails) - move contents to other folder - info (howmany mails contains, size of mails) - search for mail - remove mail(s) - add mail - move mail to other folder - create copy to other folder - change name - export to disk - Forward - Add new forward email - remove email - list emails - Mail - show - compose - save attachements - create from attachemets body of mail :) - delete attachements - forward (resend to other email adress) - export to disk - digital signature - encrtypt body - encrypt attachement - rules - process mail - add new rules - list - remove - modify - profile - create new - delete profile - edit profile (assign signature) - list - Personals (personals) - create new - remove existing - list - search Email - Create email adress - dele email address - search for mailboxes which has this email as rcpt - list unused names - modify email name (goes throught all mailboxes) Domain - create new domain - remove existing domain - search Signature - create new - remove existing - list - search