webCOMAND

Relational Database Storage Engine

An 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 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 the embedded content's content type table, but differentiated from top-level content by additional data in the meta-data columns.
    • References (v2+) - Stored in a single table (contentreference) 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 Metadata 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.
  • _ContentTypeID - This is ID that corresponds to the "contenttype_" table where the object's data type field/column values are stored.
  • _ContentID - This is the row ID within the "contenttype_#" table of the SQL database where the object's data type field/column values are stored.  The values are also redundantly stored in the "contentfield_" table for each corresponding field.
  • _RealID - This is the ID that represents the object, which is the same across versions of the object.  It is the same ID seen in the webCOMAND user interface in the Info sidebar within Form View, and accessible from $object->ID in the PHP API and $ID in cTemplate and cScript.
  • _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.