db-schema-cm.md

EDR cm Schema Documentation

Overview

The cm schema holds tables, views and functions used to provide definitions for class models and the class and property types. (Many tables have a corresponding view that presents the table in a human friendly way, for example by getting a label from a related table, rather than displaying the foreign key UUID. The views have the same name as the table they present but suffixed by '__view', e.g cm.class__view for cm.class.

Figure cm schema tables.

Tables and views

cm.association [table]

An association is a special type of attribute that connects classes to each other. The may be simple relationships, describing a relationship between independent classes, or an aggregation between classes made up of other classes.

Column	Data type	Key	FK References	Null	Default	Definition
id	text	PK		NO		Association ID.
class_model_id	uuid	FK	cm.class_model.id	NO		Class model UUID.
source_class_id	text	FK	cm.class.id	NO		Association's source class ID.
target_class_id	text	FK	cm.class.id	NO		Association's target class ID.
identifier	text			NO		The formal human-readable text identifier for the association type. Will be used a JSON key names or derived database column names. Format: lowerCamelCase
label	text			YES		A formatted label for presentation in documentation or user interfaces. Format: Proper Noun.
bidirectional	boolean			NO		true if the association is bi-directional (has an inverse_id). Generated.
inverse_identifier	text			YES		The inverse association type identifier.
inverse_label	text			YES		A human-friendly label for the association type's inverse association type (for bi-directional associations where the association is consistently specified in one direction only).
constraint__multiplicity	int4[]			YES		Target class multiplicity.
definition	text			YES		A brief definition of the association type. Content may be formatted using Markdown syntax
editorial_note	text			YES		Notes on issues with the association type and any changes that may be required.
rdf_match	text[]			YES		The compact URIs of the well-known RDF resource the type matches, where applicable.
see_also	text[]			YES		An array of URLs for web pages or other web resources that describe or define this association type.
constraints	jsonb			YES		The default constraints on the value of an instance of a association_type. Managed as a JSON object as applicable constraints vary with the association type's range.
system__type_id	text	FK	cm.system__type__association.id	NO		The system level type of the association. This cannot be changed once the attribute is referenced by `data.*_aggregation

Constraint	Type	Columns	Description
uq_association__identifier	unique	class_model_id, identifier	Ensures that the association identifier is unique within the class model.

Index	Type	Columns
pk_association	unique; btree	id
uq_association__identifier	unique; btree	class_model_id, identifier

cm.attribute [table]

Definitions of the types of properties stored the data and definition (cm) schema. Value of the type_id column on _attribute, _assertion, _observation and _measurement tables.

Column	Data type	Key	FK References	Null	Default	Definition
id	text	PK		NO		Attribute ID.
class_model_id	uuid	FK	cm.class_model.id	NO		Class model UUID.
identifier	text			NO		The formal human-readable text identifier for the attribute type. Will be used a JSON key names or derived database column names. Format: lowerCamelCase.
data_type_id	text	FK	cm.system__type__data_type.id	NO		The data type of the attribute. For attributes with polymorphic data types, specialise the attribute by data types.
abstract	boolean			NO	false	Specifies whether the attribute class is concrete (false - default) or abstract (true). Instances of abstract attribute classes cannot be created.
derived	boolean			NO	false	Specifies whether the attribute is concrete (false - default) or derived (true). Derived properties are a function of one or more concrete attribute types.
label	text			YES		A formatted label for presentation in documentation or user interfaces. Format: Proper Noun.
definition	text			YES		A brief definition of the attribute type. Content may be formatted using Markdown syntax.
editorial_note	text			YES		Notes on issues with the attribute type and any changes that may be required.
rdf_match	text[]			YES		The compact URIs of the well-known RDF resource the type matches, where applicable.
see_also	text[]			YES		An array of URLs for web pages or other web resources that describe or define this attribute type.
constraints	jsonb			YES		The default constraints on the value of an instance of a attribute. Managed as a JSON object as applicable constraints vary with the attribute type's range.
system__type_id	text	FK	cm.system__type__attribute.id	NO		The system level type of the attribute. This cannot be changed once the attribute is referenced by `data.*_attribute

Constraint	Type	Columns	Description
uq_attribute__identifier	unique	class_model_id, identifier	Ensures that the attribute identifier is unique within the class model.

Index	Type	Columns
fx_attribute__class_model	btree	class_model_id
pk_attribute	unique; btree	id
uq_attribute__identifier	unique; btree	class_model_id, identifier

cm.attribute__domain [table]

Relates properties to their domain - the class of which the attribute is an attribute. Properties may be shared by multiple classes.

Column	Data type	Key	FK References	Null	Default	Definition
id	uuid	PK		NO	gen_random_uuid()	Attribute domain UUID.
attribute_id	text	FK	cm.attribute.id	NO		Attribute ID.
class_id	text	FK	cm.class.id	NO		Class ID.
constraint__multiplicity	int4[]			NO	'{0,1}'	A two element array specifying the minimum and maximum allowable number of attribute type values for a specific class. This may vary between classes.
constraint__value_collection_id	uuid	FK	voc.concept_collection.id	YES		The UUID of the concept_collection_id that specified the available concepts for concept reference attributes.
constraint__value_unit_collection_id	uuid	FK	voc.concept_collection.id	YES
constraint__procedure_collection_id	uuid	FK	voc.concept_collection.id	YES
constraint__agent_collection_id	uuid	FK	voc.concept_collection.id	YES

Constraint	Type	Columns	Description
uq_attribute__domain__association	unique	attribute_id, class_id	Ensures only one domain association between a attribute and a class is created.

Index	Type	Columns
pk_attribute__domain	unique; btree	id
uq_attribute__domain__association	unique; btree	attribute_id, class_id

cm.attribute__view [view]

Human friendly view of cm.attribute. Gets labels for UUIDs and aggregates labels of associated entities into arrays.

Column	Data type	Definition
id	text	Attribute UUID.
identifier	text	The formal human-readable text identifier for the attribute type. Will be used as JSON key names or derived database column names. Format: lowerCamelCase.
data_type	text
system__type	text	The system level type of the attribute.
derived	boolean	Specifies whether the attribute is concrete (false - default) or derived (true). Derived properties are a function of one or more concrete attribute types.
label	text	A formatted label for presentation in documentation or user interfaces. Format: Proper Noun.
definition	text	A brief definition of the attribute type. Content may be formatted using Markdown syntax.
defined_by	text	The class model this attribute belongs to.
domain_includes	text[]	Array of classes this attribute is an attribute of.
see_also	text[]	An array of URLs for web pages or other web resources that describe or define this attribute type.
constraints	jsonb	The default constraints on the value of an instance of a attribute. Managed as a JSON object as applicable constraints vary with the attribute type's range.

cm.class [table]

Definitions of the types of classes (datasets and entities) stored the data and definition (cm) schema. Value of the type_id column on dataset and entity tables.

Column	Data type	Key	FK References	Null	Default	Definition
id	text	PK		NO		Class ID.
class_model_id	uuid	FK	cm.class_model.id	NO		Class model UUID.
identifier	text			NO		The formal human-readable text identifier for the class. Will be used a JSON key names or derived database table names. Format: UpperCamelCase.
abstract	boolean			NO	false	Specifies whether the class is concrete (false - default) or abstract (true). Instances of abstract classes cannot be created.
label	text			YES		A formatted label for presentation in documentation or user interfaces. Format: Proper Noun.
definition	text			YES		A brief definition of the class type. Content may be formatted using Markdown syntax.
editorial_note	text			YES		Notes on issues with the class type and any changes that may be required.
rdf_match	text[]			YES		The compact URIs of the well-known RDF resource the type matches, where applicable.
see_also	text[]			YES		An array of URLs for web pages or other web resources that describe or define this class type.
root_class	boolean			NO	true	Specifies whether or not the class type is a root class in the class model.
system__type_id	text	FK	cm.system__type__class.id	NO		The system level type of the class. This cannot be changed once the class is referenced by data.dataset or data.entity.
system__label_template	text			YES	'${label}'	A template specifying how class attributes may be used to create a label for presentation. May be overridden by an individual dataset's system__label_template template. Format is text with column values inserted as variables using the attribute identifier to identify the property. E.g.: '${depth} [${designation}]'
system__default_location_attribute_id	text	FK	cm.attribute.id	YES		The attribute id of the default geometry to be used to locate an entity when multiple spatial representations are available (e.g. ${samplingLocation}).

Constraint	Type	Columns	Description
uq_class__identifier	unique	class_model_id, identifier	Ensures that the class identifier is unique within the class model.

Index	Type	Columns
fx_class__class_model	btree	class_model_id
pk_class	unique; btree	id
uq_class__identifier	unique; btree	class_model_id, identifier

cm.class__associations [view]

Shows all attributes of a class, including those inherited from super classes.

Column	Data type	Definition
class_model_id	uuid	Class model UUID.
class_id	text	Class UUID.
class_identifier	text	The formal human-readable text identifier for the class. Will be used a JSON key names or derived database table names. Format: UpperCamelCase.
association_id	text	Association UUID.
association_identifier	text	The formal human-readable text identifier for the association type. Will be used a JSON key names or derived database column names. Format: lowerCamelCase
constraint__multiplicity	int4[]	A two element array specifying the minimum and maximum allowable number of association type values for a specific class. This may vary between classes.
label	text	A formatted label for presentation in documentation or user interfaces. Format: Proper Noun.
bidirectional	boolean	true if the association is bi-directional (has an inverse_id). Generated.
inverse_identifier	text	The inverse association type identifier.
inverse_label	text	A human-friendly label for the association type's inverse association type (for bi-directional associations where the association is consistently specified in one direction only).
definition	text	A brief definition of the association type. Content may be formatted using Markdown syntax
editorial_note	text	Notes on issues with the association type and any changes that may be required.
see_also	text[]	An array of URLs for web pages or other web resources that describe or define this association type.
constraints	jsonb	The default constraints on the value of an instance of a association_type. Managed as a JSON object as applicable constraints vary with the association type's range.
system__type_id	text	The system level type of the association. This cannot be changed once the attribute is referenced by `data.*_aggregation

cm.class__attributes [view]

Shows all attributes of a class, including those inherited from super classes.

Column	Data type	Definition
class_model_id	uuid	Class model UUID.
class_id	text	Class UUID.
class_identifier	text	The formal human-readable text identifier for the class. Will be used a JSON key names or derived database table names. Format: UpperCamelCase.
attribute_id	text	Attribute UUID.
attribute_identifier	text	The formal human-readable text identifier for the attribute type. Will be used a JSON key names or derived database column names. Format: lowerCamelCase.
constraint__multiplicity	int4[]	A two element array specifying the minimum and maximum allowable number of association type values for a specific class. This may vary between classes.
constraint__value_collection_id	uuid	The UUID of the concept_collection_id that specified the available concepts for concept reference attributes.
constraint__value_unit_collection_id	uuid	The UUID of the concept_collection_id that specified the available concepts for quantity unit reference values.
constraint__agent_collection_id	uuid	The UUID of the concept_collection_id that specified the available agent entities for agent reference attributes.
constraint__procedure_collection_id	uuid	The UUID of the concept_collection_id that specified the available procedure entities for procedure reference attributes.
data_type_id	text	The data type of the attribute. For attributes with polymorphic data types, specialise the attribute by data types.
derived	boolean	Specifies whether the attribute is concrete (false - default) or derived (true). Derived properties are a function of one or more concrete attribute types.
label	text	A formatted label for presentation in documentation or user interfaces. Format: Proper Noun.
definition	text	A brief definition of the attribute type. Content may be formatted using Markdown syntax.
editorial_note	text	Notes on issues with the attribute type and any changes that may be required.
see_also	text[]	An array of URLs for web pages or other web resources that describe or define this attribute type.
constraints	jsonb	The default constraints on the value of an instance of a attribute. Managed as a JSON object as applicable constraints vary with the attribute type's range.
class_system__type_id	text	The system level type of the class.
attribute_system__type_id	text	The system level type of the attribute.

cm.class__specialisation [table]

Relates class types to the sub-class types that specialise their behaviour.

Column	Data type	Key	FK References	Null	Default	Definition
id	uuid	PK		NO	gen_random_uuid()	Class specialisation UUID.
class_id	text	FK	cm.class.id	NO		Class ID.
sub_class_id	text	FK	cm.class.id	NO		Sub-class ID.

Constraint	Type	Columns	Description
ck_class__specialisation__recursion	check	class_id, sub_class_id	Recursion trap. Checks that the sub-class is not already specified as an ancestor of the class.
uq_class__specialisation__association	unique	class_id, sub_class_id	Ensures only one specialisation association between a class and sub-class is created.

Index	Type	Columns
pk_class__specialisation	unique; btree	id
uq_class__specialisation__association	unique; btree	class_id, sub_class_id

cm.class__view [view]

Human friendly view of cm.class. Gets labels for UUIDs and aggregates labels of associated entities into arrays.

Column	Data type	Definition
id	text	Class UUID.
identifier	text	The formal human-readable text identifier for the class. Will be used a JSON key names or derived database table names. Format: UpperCamelCase.
system__type	text	The system level type of the class.
abstract	boolean	Specifies whether the class is concrete (false - default) or abstract (true). Instances of abstract classes cannot be created.
label	text	A formatted label for presentation in documentation or user interfaces. Format: Proper Noun.
definition	text	A brief definition of the class type. Content may be formatted using Markdown syntax.
defined_by	text	The class model this class belongs to.
sub_classes	text[]	Array of sub classes of this class.
properties	text[]	Array of the properties of this class.
range_of	text[]	Array of the associations of this class is the range (value) of.
see_also	text[]	An array of URLs for web pages or other web resources that describe or define this class type.

cm.class_model [table]

Models (dare we say, ontologies?) that organise classes. Classes are defined for - and are therefore owned by - a model.

Column	Data type	Key	FK References	Null	Default	Definition
id	uuid	PK		NO	gen_random_uuid()	Class model UUID.
register_id	uuid	FK	reg.register.id	NO		Governing register UUID.
identifier	text			NO		The formal human-readable text identifier for the class model. Format: UpperCamelCase.
base_uri	text			NO		The base URI identifying (and publishing the model). Is also the path to class and property (attribute and association) types defined in the model.
namespace_prefix	text			NO		The default namespace prefix to be used in place of the base URI when identifier URIs are abbreviated as compact URIs (CURIs).
label	text			NO		A formatted label for presentation in documentation or user interfaces. Format: Proper Noun.
description	text			YES		A brief description of the class model. Content may be formatted using Markdown syntax.
editorial_note	text			YES		Notes on issues with the class model and any changes that may be required.
see_also	text[]			YES		An array of URLs for web pages or other web resources that describe or define this class model.
system__type_id	text	FK	cm.system__type__class_model.id	NO		The system level type of the class model. This cannot be changed once the class model is referenced by data.dataset.

Constraint	Type	Columns	Description
uq_class_model__identifier	unique	identifier	Ensures that the class model identifier is unique.

Index	Type	Columns
pk_class_model	unique; btree	id
uq_class_model__identifier	unique; btree	identifier

cm.class_model__view [view]

Human friendly view of cm.class_model. Gets labels for UUIDs and aggregates labels of associated entities into arrays.

Column	Data type	Definition
id	uuid	Class model UUID.
identifier	text	The formal human-readable text identifier for the class model. Format: UpperCamelCase.
system__type	text	The system level type of the class model.
base_uri	text	The base URI identifying (and publishing the model). Is also the path to class and property (attribute and association) types defined in the model.
namespace_prefix	text	The default namespace prefix to be used in place of the base URI when identifier URIs are abbreviated as compact URIs (CURIs).
label	text	A formatted label for presentation in documentation or user interfaces. Format: Proper Noun.
description	text	A brief description of the class model. Content may be formatted using Markdown syntax.
defines	text[]	An array of classes defined in this class model.
see_also	text[]	An array of URLs for web pages or other web resources that describe or define this class model.

cm.system__type__association [table]

Controlled list of system types (sub-types that the system depends on) for a association type.

Column	Data type	Key	FK References	Null	Default	Definition
id	text	PK		NO		The system type id.
label	text			NO		A human-friendly label for the system type.
definition	text			YES		A brief definition of the system type. Content may be formatted using Markdown syntax.
super_type_id	text	FK	cm.system__type__association.id	YES		The super (parent) system type id where a type specialises another.
rdf_match	text[]			YES		The compact URIs of the well-known RDF resource the type matches, where applicable.

Index	Type	Columns
pk_system__type_association	unique; btree	id

cm.system__type__attribute [table]

Controlled list of system types (sub-types that the system depends on) for an attribute.

Column	Data type	Key	FK References	Null	Default	Definition
id	text	PK		NO		The system type id.
label	text			NO		A human-friendly label for the system type.
definition	text			YES		A brief definition of the system type. Content may be formatted using Markdown syntax.
super_type_id	text	FK	cm.system__type__attribute.id	YES		The super (parent) system type id where a type specialises another.
rdf_match	text[]			YES		The compact URIs of the well-known RDF resource the type matches, where applicable.

Index	Type	Columns
pk_system__type__attribute	unique; btree	id

cm.system__type__class [table]

Controlled list of system types (sub-types that the system depends on) for a class type. Based largely on RDF Schema (http://www.w3.org/2000/01/rdf-schema#Class) and the PROV-O ontology, with modifications to support the integration of the SOSA-SSN sampling and observation ontology.

Column	Data type	Key	FK References	Null	Default	Definition
id	text	PK		NO		The system type id.
identifier	text			NO		The formal human-readable text identifier for the class.
abstract	boolean			NO	false	Specifies whether the class is concrete (false - default) or abstract (true). Instances of abstract classes cannot be created.
label	text			NO		A human-friendly label for the system type.
definition	text			YES		A brief definition of the system type. Content may be formatted using Markdown syntax.
super_type_id	text	FK	cm.system__type__class.id	YES		The super (parent) system type id where a type specialises another.
rdf_match	text[]			YES		The compact URIs of the well-known RDF resource the type matches, where applicable.

Index	Type	Columns
pk_system__type__class	unique; btree	id

cm.system__type__class_model [table]

Controlled list of system types (sub-types that the system depends on) for a class model.

Column	Data type	Key	FK References	Null	Default	Definition
id	text	PK		NO		The system type id.
label	text			NO		A human-friendly label for the system type.
definition	text			YES		A brief definition of the system type. Content may be formatted using Markdown syntax.
super_type_id	text	FK	cm.system__type__class_model.id	YES		The super (parent) system type id where a type specialises another.
rdf_match	text[]			YES		The compact URIs of the well-known RDF resource the type matches, where applicable.

Index	Type	Columns
pk_system__type__class_model	unique; btree	id

cm.system__type__data_type [table]

Definitions of the JSON data types used for attribute values. Includes JSON schema definitions.

Column	Data type	Key	FK References	Null	Default	Definition
id	text	PK		NO		The system type id.
identifier	text			NO		The formal human-readable text identifier for the data type. Used for consistency with class types.
abstract	boolean			NO	false	Specifies whether the data type is concrete (false - default) or abstract (true). Instances of abstract data types cannot be created.
label	text			YES		A human-friendly label for the system type.
definition	text			YES		A brief definition of the system type. Content may be formatted using Markdown syntax.
schema_json	jsonb			YES		The JSON Schema defining the structure of the value when stored as an object in an attribute table.
pgsql_cast	text			YES		The PgSQL cast string to be added to a value in a select statement when extracted from a JSON object as text, e.g. ::numeric(5,2) for (value ->> '.')::numeric(5,2).
pgsql_select_expression	jsonb			YES		Templates for casting JSONB attribute values in SQL SELECT expressions. Is a JSON object to allow for separate templates for object value keys. Can be used as helpful templates when writing SQL queries or be embedded in execute format() statements in functions. The alias is to be replaced with the ... alias (e.g. xx.) ... or an empty string.
super_type_id	text	FK	cm.system__type__data_type.id	YES		The super (parent) system type id where a type specialises another.

Constraint	Type	Columns	Description
uq_system__type__data_type__identifier	unique	identifier	Ensures that the data type identifier is unique.

Index	Type	Columns
pk_system__type__data_type	unique; btree	id
uq_system__type__data_type__identifier	unique; btree	identifier

Functions

Function	Type	Arguments	Returns	Description
class__sub_class	function	_class_id	text[]	Returns an array of ids of the class's (_class_id) sub-classes.
class__super_class	function	_class_id	text[]	Returns an array of ids of the class's (_class_id) super-classes.
class__system__label__attribute	function	_class_id; _attribute_id	boolean	Function that tests whether the attribute (_attribute_id) is part of the class's label template.
tf_association__system__type__update	function		trigger	Trigger function that implements a check to ensure that a association hasn't been referenced in the data schema. If it has then changes to the association's system type are restricted as it will have impacted how the record is stored and managed in the data schema.
tf_attribute__system__type__update	function		trigger	Trigger function that implements a check to ensure that a attribute hasn't been referenced in the data schema. If it has then changes to the attribute's system type are restricted as it will have impacted how the record is stored and managed in the data schema.
tf_class__system__label_template__update	function		trigger	Trigger function that updates an entity or dataset's default label if the class's default label template is changed. Does not update labels templated at a dataset level.
tf_class__system__type__update	function		trigger	Trigger function that implements a check to ensure that a class hasn't been referenced in the data schema. If it has then changes to the class's system type are restricted as it will have impacted how the record is stored and managed in the data schema.
tf_class_model__system__type__update	function		trigger	Trigger function that implements a check to ensure that a class model hasn't been referenced in the data schema. If it has then changes to the class model's system type are restricted as it will have impacted how the record is stored and managed in the data schema.
validate_attribute_value	function	_attribute_id; _value	boolean	Validates a entitys attribute (_id) value according to its attribute's (_attribute_id`) data type.
validate_json_object	function	_json_object; _json_schema	jsonb	Validates a JSON object (_json_object) according to the provided JSON Schema (_json_schema). Returns a JSON objects with a report on validation errors if the validation fails.