Database_structure.md 4.1 KB
A quick overview of the Kopano Core database structure
Overview
The Datbase has the following tables:
+--------------------+
| Tables_in_kopano |
+--------------------+
| abchanges |
| acl |
| changes |
| clientupdatestatus |
| deferredupdate |
| hierarchy |
| indexedproperties |
| lob |
| mvproperties |
| names |
| object |
| objectmvproperty |
| objectproperty |
| objectrelation |
| outgoingqueue |
| properties |
| receivefolder |
| searchresults |
| settings |
| singleinstances |
| stores |
| syncedmessages |
| syncs |
| tproperties |
| users |
| versions |
+--------------------+
abchanges
Has a journal of all GAB changes -> every change (add/delete/modify) is in this table.
acl
List of all acls - each record is an ACE (access control entry) userx has permissiony on folderz (With a pair record for rights for grant and deny, but deny was never implemented in the Kopano server) Columns:
- id:
- hierarchy_id:
- type: 2=grant,1=deny
- rights: bitmask of the rights (owner, etc.) - same in mapi defined permissions
changes
Has a journal of all mailbox changes -> messages in folders added, deleted, modified - exposed via ICA.
clientupdatestatus
Has a list of all the reported statuses of client updates
deferredupdate
Is a per-folder list of changes to tproperties that haven't been written to tproperties yet (for performance reasons)
hierarchy
Is the parent-child relationship table for the mailbox (IPM_SUBTREE_ROOT -> ...)
Columns
- id:
- parent:
- type: 1=mailbox, 3=folder, 5=message, 6=recipient, 7=attachment
- flags: folders:0=root folder,1=normal folder,2=search folder; messages:0=normal message, 64=associative message, 1024=deleted
- owner:
indexedproperties
Mapping between from certain properties to their hierarchyid example: PR_ENTRY_ID abcdef match to hierarchy.id 12345
lob
Attachments chunked into 256K blocks (only in mysql-stored format)
Names
Nothing but named properties mapping to property IDs — property IDs are subtracted by 0x8500.
outgoingqueue
Two things: 1. queue that outlook polls for messages that it wants to send, 2. queue of spooler, of messages that it wants to send
Columns:
- flags: if even then outlook queue, if odd spooler queue
- properties - the list of properties for each hierarchy.id - each value is put into its corresponding column type (binary, double, etc.)
- mvproperties - looks exactly the same, but it also has orderid, so there can
- be multiple records for the same property for the same hierarchy.id (done for performance reasons)
receivefolder
It points at which folder is your actual receivefolder in your hierarchy
searchresults
When you do a search all of the results are saved and updated forever until you delete the search folder settings - random server-wide settings
singleinstances
Record of attachment-deduplication (single instance store)
stores
links stores to users (hierarchy.id to users.id)
syncedmessages
Is used in restricted ICS (when we can't use the journal) - sync only the last 4 weeks is an example. This represents the messages that have been sent to a mobile. only for z-push
syncs
A list of all folders that are being tracked for ICS - we do not really use it, we only write to it (investigation, maybe?)
tproperties
The list of properties for each hierarchy.id - each value is put into its corresponding column type (binary, double, etc.) BUT It only contains the properties that can be seen in tables therefore excludes attachments and recipients everything is truncated to 255 bytes or characters.
users
Users table with the relation column (externid)
versions
List of all zarafa versions ever installed on the system
Tables specific for DB plugin
- object - everything normally in ldap
- objectmvproperty - everything normally in ldap
- objectproperty - everything normally in ldap
- objectrelation - everything normally in ldap