MYSQL MARIADB DATABASE TABLES | CNCNETPDM MANUAL

TOP

This section describes the steps to setup required and optional database tables for CNCnetPDM in case you're using a MySQL or MariaDB (Version >= 5.5!) database.

Note: Starting with CNCnetPDM Ver. 6.3.3.1 the names of all database tables can be configured on a per-service basis, see chapter Connect of CNCnetPDM INI file for details.

For a MySQL or MariaDB database the information in section SQL Server Database tables applies also but the scripts to create the tables look slightly different.

MTH_RAW_DATA Stores all data received from devices
MTH_ONLINE_DATA Contains only the most recent data received from devices
MTH_USER_CONTEXT Enables you to combine machine-data with work-order, operation and part-number information
MTH_EQP_SHIFT_AVAIL_V Enables you to acquire data only during an active shift

MTH_RAW_DATA

The following code can be used to create MTH_RAW_DATA. CNCnetPDM stores all data acquired in every reading cycle in this table. This Alternatively you can use MTH_RAW_DATA_MYSQL.sql from this zip archive. Here database MDA is used, please change that according to your needs.

USE MDA; -- Here database MDA is used
CREATE TABLE MTH_RAW_DATA (GROUP_ID BIGINT, READING_TIME DATETIME NOT NULL, DEVICE_NUMBER INT NOT NULL, TAG_CODE VARCHAR(255) NOT NULL, TAG_DATA VARCHAR(255), CREATION_DATE DATETIME, PROCESSING_FLAG INT DEFAULT 2 NOT NULL); -- Setup table to store all records

FIG 1: Setup script (MySQL or MariaDB) for database table MTH_RAW_DATA

MTH_ONLINE_DATA

To only store the most recent data from your machines you can use table MTH_ONLINE_DATA. CNCnetPDM updates all existing records for every device in this table when new data is queried. The update interval depends on the value for PollInterval in section [GENERAL] of CNCnetPDM.ini. To use this table please make sure that you have set WriteOnlineData = 1 in section [Connect] of CNCnetPDM.ini. You can create table MTH_ONLINE_DATA manually by using the following code or file MTH_ONLINE_DATA_MYSQL.sql from this zip archive. Here again database MDA is used, please change that if needed.

USE MDA; -- Here database MDA is used
CREATE TABLE MTH_ONLINE_DATA (GROUP_ID BIGINT, READING_TIME DATETIME NOT NULL, DEVICE_NUMBER INT NOT NULL, TAG_CODE VARCHAR(255) NOT NULL, TAG_DATA VARCHAR(255), CREATION_DATE DATETIME, PROCESSING_FLAG INT DEFAULT 2 NOT NULL); -- Setup table for device monitoring

FIG 2: Setup script (MySQL or MariaDB) for database table MTH_ONLINE_DATA

MTH_USER_CONTEXT

For table MTH_USER_CONTEXT you can use the following code or alternatively MTH_USER_CONTEXT_MYSQL.sql from this zip archive. Here again database MDA is used, please change that if needed.

USE MDA; -- Here database MDA is used
CREATE TABLE MTH_USER_CONTEXT (USER_CONTEXT_PK_KEY INT NOT NULL, SCAN_TIME VARCHAR(255) NOT NULL, EQUIPMENT_FK VARCHAR(255) NOT NULL, WORKORDER_FK VARCHAR(255) NOT NULL, SEGMENT_FK VARCHAR(255) NOT NULL, ITEM_FK VARCHAR(255) NOT NULL, CONSTRAINT MTH_USER_CONTEXT_PK PRIMARY KEY (USER_CONTEXT_PK_KEY)); -- Setup table for workorders, segments and parts

FIG 3: Setup script (MySQL or MariaDB) for database table MTH_USER_CONTEXT

MTH_EQP_SHIFT_AVAIL_V

To only acquire data within an active shift for a device you can use the following code or alternatively file MTH_EQP_SHIFT_AVAIL_V_MYSQL.SQL from this zip archive.

USE MDA; -- Here database MDA is used
CREATE TABLE MTH_EQP_SHIFT_AVAIL_V (EQUIPMENT_FK VARCHAR(255) NOT NULL); -- Setup table to determine if there is an active shift

FIG 4: Setup script (MySQL or MariaDB) for database table MTH_EQP_SHIFT_AVAIL_V

For testing you can insert the 4 digit device number into field EQUIPMENT_FK and adjust the .ini file for CNCnetPDM to only collect data within a shift. In a production environment MTH_EQP_SHIFT_AVAIL_V should be a view that looks up if there’s an active shift for the equipment in a shift table and, if yes, returns the 4 digit device number.