Tables Profiles store the data of their Devices in SQLite, PostgreSQL, or SQL Server. The tables are created automatically, organized in schemas, so reporting and analytics tools can query them directly. In SQLite, which has no schemas, each schema is its own database file next to the database.
Schemas
| Schema | Contents |
|---|---|
mtconnect_devices | The Device models: Devices, Components, DataItems, and their Compositions, Descriptions, Configurations (ex. Coordinate Systems, Motion, Sensor Configuration, Specifications), Definitions, Constraints, and Relationships |
mtconnect_observations | Observations by type, plus the latest value of each DataItem in the current_* tables |
mtconnect_assets | MTConnect Assets |
mtconnect_connections | Each Device's connection status and status_history |
mtconnect_statistics | Observation statistics by Device and DataItem, and Data Retention statistics |
mtconnect_definitions | The MTConnect Standard's Component, Composition, and DataItem Types, with DataItem SubTypes and Values, to describe the stored data |
Observations
Each Observation is stored in the table for its DataItem's Category and Representation. Observations are written in batches, and the current_* tables
are kept up to date, so the latest value of every DataItem can be read with a single query.
| History Table | Current Table | DataItems |
|---|---|---|
sample_value | current_sample_value | Numeric SAMPLEs (ex. positions, temperatures, loads) |
sample_time_series | current_sample_time_series | SAMPLEs with the TIME_SERIES Representation |
event_value | current_event_value | EVENTs (ex. Execution, Program, Part Count) |
condition | current_condition | CONDITIONs (alarms), with their state (level), native code, native severity, qualifier, and message (result) |
Every Observation has its Device UUID, DataItem ID, timestamp, and the Agent's instance ID and sequence.
Data Retention deletes old rows from the history tables, and never from the current_* tables.
Assets
Tables Profiles store each Device's MTConnect Assets (ex. Cutting Tools, Files, Raw Materials) unless Store Assets is turned off for the Profile.
The mtconnect_assets.asset table keeps the latest version of each Asset (by Device UUID and Asset ID), with its Type, Timestamp, Removed flag,
and the full Asset as MTConnect XML, so every Asset Type is supported.
Example Queries
Columns are named in snake case (ex. device_uuid, data_item_id). The latest Execution state of every Device, found by joining the DataItems of the Device models:
select o.device_uuid, o.data_item_id, o.result, o.timestamp
from mtconnect_observations.current_event_value o
join mtconnect_devices.data_item d on d.device_uuid = o.device_uuid and d.id = o.data_item_id
where d.type = 'EXECUTION';
The active alarms (Conditions whose state is WARNING or FAULT):
select device_uuid, data_item_id, state, native_code, result, timestamp
from mtconnect_observations.current_condition
where state in ('WARNING', 'FAULT');