OpenConnector.Org SQL Schema Documentation
This document aims to explain the SQLite schema and API interaction in the OpenConnector.Org project. It is a work in progress, and may fall out of sync with the source code.
SQL tables are created in the database with names generated using two GUIDS. The GUID of the type that this table is storing data for and a random GUID, used as an identification field.
Fig. 1 The SQL Schema for the O_IProp Object
| Name | Type |
Comments |
SIZE |
ULONG |
The size of this table row in bytes |
PROPTAG |
ULONG |
The MAPI Property tag of this property |
MV_SEQ |
ULONG |
The Multi-value sequence index |
TRANSACT_ID |
ULONG |
ID of transaction which altered property |
IS_REF |
ULONG |
Does the data field hold a reference? |
DATA |
BLOB |
The data field |
The O_IProp class implements MAPI's IMAPIProp interface. When an object that implements O_IProp is created, it creates a table in the SQLite database that will store all its properties. These objects store a property per line. Multivalued properties will be stored, one value per line, using the MV_SEQ column as an index.
Since it is often necessary to calculate the size of an object on disk, eg. to estimate the size of an email message, the size column stores the amount of space the row consumes. This value has to be kept up to date by the code itself as it cannot be calculated from the database easily.
The ‘PropTag' column stores the property tag for the property this row represents. See the MAPI documentation for more information on properties.
MV_SEQ is not currently being used since we do not support multi-valued properties as yet.
When an property is modified in a property that is marked as transacted, the modifications are stored in a ‘shadow' row with the modifying object's ‘id' in this field. This allows us to rollback changes and also resolve conflicts.
The IS_REF column is set to one if the DATA column contains another table name representing another object, instead of actual data. For instance, when properties of type PT_OBJECT are saved, and we know that the object is actually a IMAPITable object, as with contents tables, then we simply store the name of the SQL table that holds the IMAPITable data in DATA. In that scenario, the DATA column acts as a pointer rather than an actual variable ( in C language terms ).
Fig. 2 The SQL Schema for the O_ITable Object
| Name | Type |
Comments |
ROWNUM |
ULONG |
The current cell's row number |
COLID |
ULONG |
The current cell's column number |
PROPTAG |
ULONG |
Cell's Property tag |
MV_SEQ |
ULONG |
Sequence number for multi-valued properties |
| SEQ | ULONG | Sequence number for very large data that had to be broken up. |
IS_REF |
ULONG |
Does the data field hold a reference? |
DATA |
BLOB |
The data field |
Each SQL table row in our database represents a MAPI table cell.
The ROWNUM column stores the row number of the current cell. Correspondingly, COLID column stores the column number of the current cell. Those two columns can be seen as X and Y co-ordinates in a grid.
The rest of the columns are used the same as in the O_IProp object. The SEQ and MV_SEQ are not being used currently, but may be used in the future.
The O_ITable object stores special rows with ROWNUMs of 0 and below. For example the MAPI row of 0 stores values indicating whether certain columns are in the current view or not. Row -1 stores the original column set. In the future lower MAPI table rows will store sort data, etc. These rows will not be return through the IMAPITable interface.