webCOMAND

webCOMAND Docs
Developer Guide Concepts & Features Repositories Storage Engines Relational Database

Relational Database Storage Engine

A MySQL/MariaDB database, and eventually 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 metadata are stored using the following schema.

  • System Table - A table with a single row with a few columns of repository metadata used to bootstrap the repository connection process.
  • Content - Content types and objects stored in a relational database are organized into tables that represent the instances of a content type, as well as their various versions and variants.
  • Fields - All values for a specific field name, potentially shared across content types, are stored in field-specific tables to facilitate fast polymorphic queries.
  • Relationships - Content relationships are stored in the following ways.
    • Embedded Content - Stored in the embedded content's content type table, but differentiated from top-level content by additional metadata columns.
    • References - All relationships are stored in a single table (contentreference).
    • 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 for certain types of queries.
    • One-To-One and Many-To-One References (v4+) - Reference fields that are not lists marked for query optimization will duplicate the information stored in the single references table in a dedicated metadata columns within the parent content type table.  This will improve look-up times by eliminating joins.

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

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.

Fields

All values for a specific field name, potentially shared across content types, are stored in field-specific tables to facilitate fast polymorphic queries.  For example, it is common to query for content across multiple content types that share the same field with a query like:

[:File+ AND Filename='123.php']

The field tables make it possible to more efficiently query across the dozens of content types that extend the File content type and therefore all have a Filename field.  Only the field table needs to be queried, instead of having to query across all of the content type tables (ie. with multiple queries or a UNION), which would be much slower.

Further, queries can span multiple content types, even if they do not all extend from a base content type with the shared field name.  For example:

[:cObject+ AND Filename='123.php']
Table Naming Convention

The content field table name is based on the template below.

  • contentfield_fieldname

Values in these tables can be lost and rebuilt by diagnose.  In fact, these tables are not backed up in webCOMAND backups.  Instead, they are rebuilt during the restore process after the contenttype_ID tables are restored.  This is a key reason wC backups are more storage efficient than MySQL Dumps.

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

To be introduced in COMAND 4.  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.

Page last updated .

Copyright © 2013-2024, COMAND Systems, Inc.