Tables

Tables

Normal

A "normal" Table is a table that holds a non-abstract Thing's specific data. For example, an ElementDefinition's specific data is stored in the ElementDefinition table that resides in the Iteration_xyz schema.

A normal table contains at least the following columns:

• Iid -> The row's unique identifier
• ValueTypeDictionary -> Specific data stored in an hstore
• Validity period -> ValidFrom and ValidTo columns that indicate the period in time this record represents the current state of the Thing.

Next to these "default" columns, extra columns might also be defined. For example:

• Owner -> The Iid of the DomainOfExpertise that is the owner of this Thing.
• Container -> The Iid of the Thing that contains this Thing.

These extra columns only exist if the property is defined in ECSS-E-TM-10-25 at the level of the Thing. If it is defined at the level of one of the "parent Things" in the Things' Generalization tree, the column is defined in the corresponding Generalization table. For example, ElementBase is a Generalization for ElementDefinition. ElementBase is a Generalization of OwnedThing, which means that the Owner property should be defined. This Owner property is defined as an extra column in the ElementBase table. It is not present as a column in the ElementDefinition table.

Normal tables only contain data for currently active Things. Currently active in this case means that Things are still present in the current state of the ECSS-E-TM-10-25 model.

Generalization tables

ECSS-E-TM-10-25 uses the concept of Generalization. In Object Oriented Programming (OOP) this is also called Inheritance. A class can partially inherit its properties from another class, which is called a base class in OOP. In the ECSS-E-TM-10-25 UML model this base class is called a Generalization.

Specific tables exist for all these Generalizations. For example, ElementBase is defined as a Generalization for ElementDefinition. In the Database, tables for both ElementBase and ElementDefinition can be found. DefinedThing is a generalization of ElementBase, so a DefinedThing table can also be found in the database. Thing is a generalization of DefinedThing, so a Thing table can also be found in the database.

As with normal tables, these tables only contain data for currently active Things. Currently active in this case means that Things are still present in the current state of the ECSS-E-TM-10-25 model.

A generalization table contains at least the following columns:

• Iid -> The row's unique identifier
• ValueTypeDictionary -> Specific data stored in an hstore column named
• Validity period -> ValidFrom and ValidTo columns that indicate the period in time this record is/was the current state of the Thing.

Next to these "default" columns, extra columns might also be defined, for example:

• Owner -> The Iid of the DomainOfExpertise that is the owner of this Thing.
• Container -> The Iid of the Thing that contains this Thing.

These extra columns only exist if the property is defined at the level of this Generalization within the Things' Generalization tree. For example, ElementBase is a Generalization for ElementDefinition. ElementBase is a Generalization of OwnedThing, which means that the Owner property should be defined. This Owner property is defined as an extra column in the ElementBase table. It is not present as a column in the ElementDefinition table.

Audit

The concept of auditing is that every change to a Thing is written to the database using specific audit tables. The naming convention for audit tables is [ThingName]_Audit, so ElementDefinition's Audit table name is ElementDefinition_Audit. For all Normal, Generalization and cross tables a [ThingName]_Audit counterpart exists in the database.

Rows in audit tables are created using several database triggers / functions that check for a specific setting that indicates that audit data must be stored. In the CDP4-COMET WebService, this setting is set to the temporary transaction_info table, that is created in the WebService code (Cdp4TransactionManager) when a database transaction is created. The transaction_info table is removed immediately when the transaction is committed.

Every table that supports the creation of audit records contains two database triggers for this purpose:

• [thingName]_audit_prepare
• [thingName]_audit_log

Both triggers refer to trigger functions located in the SiteDirectory schema.

• process_timetravel_before()
• process_timetravel_after()

Every INSERT SQL statement creates a new audit record. Every UPDATE SQL statement creates a new audit record only if the audit_enabled setting in the transaction_info table is set to true. Every UPDATE SQL statement sets the validity (column validTo) of the most recently created existing audit record only if the audit_enabled setting in the transaction_info table is set to true. Every DELETE SQL statement creates a new audit record only if the audit_enabled setting in the transaction_info table is set to true.

Audit tables are used by the [ThingName]_Data() database functions. These database functions are able to retrieve data for a specific moment in time.

Audit tables are defined for normal tables but also for cross tables.

Cross table

A cross table defines a (weak) reference between two tables. For example the ReferencedElement property of an ElementDefinition is stored in the ElementDefinition_ReferencedElement table.

A cross table contains the following information:

• Iid of the first referenced table
• Iid of the second referenced table
• Validity period
• Custom data that belongs to the reference itself and not to one of the referenced tables.

Revision

A revision table contains the complete state of a Thing in a specific revision. A revision number is actually a unique transaction number. Typically all updated/inserted rows in a single transaction share the same revision number. EngineeringModel and SiteDirectory have their own separate Revision Number administration that can be found in their respective RevisionRegistry tables. The latest revision of all currently active Things can be found in the "normal" tables and in the Generalization tables.

A revision table's naming convention is [ThingName]_Revision.

A revision table contains the following information:

• Iid -> Unique identifier
• RevisionNumber -> The revision number of this specific version of the state of the Thing
• Instant -> the transaction time, retrieved by the SiteDirectory.get_transaction_time() Database Function
• Actor -> The Iid of the person that created the revision row
• Jsonb -> The state of the Thing as Json data

A revision row is created in the WebService using the RevisionService class. The Things' complete object structure is serialized and saved to a new row in the Thing's Revision table.

Cache

Cache tables exists for performance reasons.

A cache table's naming convention is [ThingName]_Cache.

A cache table contains the following data:

• Iid -> Unique identifier
• RevisionNumber -> The revision number of this specific version of the state of the Thing
• Jsonb -> The state of the Thing as Json data

It is almost the same data as for a [ThingName]_Revision table, but in this case only the currently active version of the Thing is present in the table. In theory you can calculate the state of a non-abstract Thing using its ValueTypeDictonary data and its parent/generalization table's and cross tables' using the [ThingName]_Data() database Functions. That requires more database reads, thus slows down performance.

Only normal tables have [ThingName]_cache counterparts.

A cache row is created in the WebService using the RevisionService class. The Things' complete object structure is serialized and saved to a new row in the Thing's [ThingName]_cache table.