Relational Database Storage Engine

Any SQL compatible relational database with a PHP PDO driver can serve as the primary storage engine for a COMAND Repository.

All content types, objects, relationships and meta-data are stored using the following open schema.

System Table - A table with a single row with a few columns of repository meta-data used to bootstrap the repository connection process.
Content - Content types and objects stored in a relational database are organized into tables that represent the content's base content type.
Relationships - Content relationships are stored in the following ways.
- Embedded Content (v2+) - Stored in meta-data columns in the embedded content's content type table.
- References (v2+) - Stored in a single table for all references.
- Per-Field References (v4+) - Reference fields marked for query optimization will duplicate the information stored in the single references table in a dedicated table for only relationships through the one field, with the same table columns as the single table. This will improve look-up times by reducing the number of rows in the table and associated indexes.
- One-To-One and Many-To-One References (v4+) - Reference fields that are not lists (one-to-many) marked for query optimization will duplicate the information stored in the single references table in a dedicated meta-data columns within the parent content type table. This will improve look-up times by eliminating an unnecessary join operation.

System Table

Every repository has a single System Table named "System". It contains a single row of information about the repository, which is used to bootstrap the repository connection process. The table columns are:

Type - Describes the type of repository, which is currently always "COMAND Relational Database".
Version - The version number of the database schema, in the format "N.nn.YYYYMMDD". For example, COMAND relational database schema version 3.0 that was released on 1/1/2014 would have the value "3.00.20140101".
SystemPropertyID - The ID of the System Property content type. This ID is used to determine the table name that represents system property objects. With that information, system properties can be loaded to discover information used to access additional content types, including IDs of the core content types.

Content

Each object (content record) in a repository that is stored in a relational database, including content types, will have a corresponding Content Type table.

Table Naming Convention

The content type table name is based on the template below, not on the content type's Identifier or Title. The ID in the table name refers to the content type's ID in the repository.

ContentType_ID
Alternatively, MySQL Views could be used to make an alias for the Identifier for backward compatibility.

Table Columns

Each field that represents a data-type will have a corresponding column in the content type table. The name of the column will be equal to the field's Identifier. The column type will be based on the data-type. For example, a "checkbox" data-type will be stored as a TINYINT. The mapping of each data-type to corresponding column type is defined in the repository.

In addition to the data-type fields, content type tables will also have the following meta-data columns.

ID - An integer based ID that will serve as the primary key and value used for all foreign key relationships for this type of content. This column is always indexed as the primary key to guarantee that it is unique, auto-incremented and indexed for fast look-ups.
_ParentType - An integer based ID that represents the parent content type of a content record that is embedded in a parent content record. This column is always indexed for optimal look-up performance. If the content the table row represents is not embedded in a parent content record, the value is 0. The only exception to that rule is in the content type table that represent the object content type. The object content type table contains a row for every object of all content types, and the _ParentType column is set to the ID of the object's content type.
_FieldID - An integer based ID that represents the field of a parent content record that this record is embedded under. This column is always indexed for optimal look-up performance. If the content the table row represents is not embedded in a parent content record, the value is 0 (even in the object content type table).
_ParentID - An integer based ID that represents the parent content record that this record is embedded under. This column is always indexed for optimal look-up performance. If the content the table row represents is not embedded in a parent content record, the value is 0. The only exception to that rule is in the content type table that represent the object content type. The object content type table contains a row for every object of all content types, and the _ParentID column is set to the ID of the row that represents it in the object's content type table.
_ActiveID - An integer based ID that represents the content record that this row is a historic or draft version of. If the content record is a historic version, the value is negative (multiplied by -1). If the content recor is a draft version, the value is positive. If the content record is the active version (not historic or a draft), the value is 0.
_LastModified - Timestamp that represents the last time this record was modified. If _ActiveID is zero, it represents the time the active content record was last modified. If _ActiveID is not zero, it represents the time the version was submitted.

Relationships

Embedded Content

Embedded content relationships are stored in the Content Type tables using the following columns.

_ParentType
_FieldID
_ParentID

References

Reference content relationships are stored in the ContentReference table, which has the following columns.

ID
ParentType
ParentFieldID
ParentID
ChildType
ChildID
OrderIndex

Per-Field References

To be introduced in COMAND 4. Reference fields marked for query optimization will duplicate the information stored in the single references table in a dedicated table for only relationships through the one field, with the same table columns as the single table. This will improve look-up times by reducing the number of rows in the table and associated indexes.

One-To-One and Many-To-One References

To be introduced in COMAND 4. Reference fields that are not lists (one-to-many) marked for query optimization will duplicate the information stored in the single references table in a dedicated meta-data columns within the parent content type table. This will improve look-up times by eliminating an unnecessary join operation.

Nested Sets

Object hierarchies will be implemented in SQL using the nested set model. The ContentReference table OrderIndex column will split into two columns: Left and Right. Simple ordered lists will use Left the same way OrderIndex was previously used. However, when a content type's first displayed field is a relationship field of the same content type, that content can be organized into a hierarchy. When content can be organized into a hierarchy, fields that reference that content implement the nested set model using the Left and Right values, which provides more efficient ways to query and traverse the content because certain queries no longer need to access the primary recursive relationship field.