Introduction

The ODM synchronization engine synchronizes ODM tables by discovering which ODM table exist in the ODM database and which rows are specified in each table. This means that, starting from an empty ODM database, it is up to the end user to create the ODM table (DataHubSite, DataHubSource or DataHubVariable) he wants with the columns corresponding to the DataHub entitie properties that needs to be synchronized.

ODM table column names

Columns that should receive entity property value

Any column name must be the name (case insensitive) of a property existing in the corresponding DataHubEntity.

Caution

If the name does not correspond to the one of a property, it will be ignored in any subsequent query, meaning that if the column does not allow null, the whole table will remain empty.

Warning

The Id column is mandatory and may not be auto-incremental (Identity column) because it would prevent the ODM synchronizer to insert the Id of the corresponding entities

DataHubSite properties

The exhaustive list of properties is:

  • Id int (mandatory)
  • Name text
  • TypeId int
  • TimeZoneId text
  • Street text
  • PostalCode text
  • City text
  • Country text
  • Location text

DataHubSource properties

The exhaustive list of properties is:

  • Id int
  • Name text
  • EnergyTypeId int
  • EnergyUsageId int
  • SourceTypeId int
  • TimeZoneId text
  • EanNumber text
  • MeterNumber text
  • Description text
  • SerialNumber text
  • SimId text
  • GatewayId int
  • GatewayTypeId int
  • MeterAddress text
  • Localisation text
  • SiteId int
  • SiteName text

DataHubVariable properties

The exhaustive list of properties is:

  • Id int
  • Name text
  • MappingConfig text
  • SourceName text
  • QuantityType text
  • VariableClass text
  • IsDefault bit
  • Granularity float
  • GranularityTimeBase int
  • Aggregate int
  • VariableTypeId int
  • SourceId int

Columns that should receive form field value

Column names supposed to receive the value of forms can be expressed using the following format: form.group.field. Since such a column name contains dot characters, it must be surrounded by square brackets like this:

CREATE TABLE DataHubxxx (Id int not null primary key, ..., [myForm.my group with spaces.myField] int)
Caution

If the name does not correspond to the one of an existing form, it will be ignored in any subsequent query, meaning that if the column does not allow null, the whole table will remain empty.

ODM table column types

Column types must be compatible with the type of the corresponding property/form field. Text/string columns should be sized to hold the longest string of the among all corresponding DataHub entities. Whenever possible, try to use nvarchar(max) or varchar(max) to reduce the risk of reaching the max string length of the column.

Caution

If a column type is not compatible with the value from the entity, the row will never be synchronized, even if the column allows null value

ODM tables templates

The API provides an endpoint (/sql/tablesTemplates) that returns the full table schema of all the ODM tables, including all existing form fields. You can use the template as a starting point for the real ODM template schemas: remove the unwanted columns and apply the SQL statement to create the definitive ODM table.

ODM columns rules

Mandatory discriminant column

Every ODM table MUST contain a column named Id of type int that is the primary key of the table that will be used to select a single row during UPDATE or DELETE operations.

Primary key

Although it is not mandatory, we strongly advise to flag the Id field as primary key during the table creation:

CREATE TABLE DataHubSource (Id int not null primary key, ...)

This rule will avoid duplicates during update operations: when the ODM synchronizer receives an error while updating a row, it will try to insert the row instead, which could cause row duplicates in certain consitions.

Foreign key constraints

We do not recommend to put foreign key contraints on any custom table column referencing an ODM table column: this could prevent the ODM synchronizer to update/delete row in the foreign table. During the initialization of ODM tables, each ODM table will be emptied prior to be refilled. Any foreign key refencing the table will prevent rows deletion.

Also avoid foreign key constraint on DataSource.SiteId and DataHubVariable.SourceId: the deletion of multiple entities could happen out-of-order, potentially causing the ODM synchronizer to delete the parent row prior to the child one.

Tip

You can join tables on fields not having any foreign key contraint. In order to take DataHub entity deletion into account, prefer using LEFT JOIN over INNER JOIN.

Warning

Defining foreign key constraint with CASCADE on delete will delete all custom table rows upon tabel reinitialization since each table will be emptied prior to be refilled.

Column constraints

Be very careful while defining column constraints on ODM tables, it could prevent the ODM synchronizer to perform some operations. Imagine a int column with a check contraint ensuring that the value must be > 0. If a DataHub entity contains 0, the ODM synchronizer will never be able to create/update the corresponding row until the value in DataHub satisfies the column constraint.

ODM table schema change

The ODM synchronizer does not support table schema change: if the schema of an ODM table must be modified, drop the corresponding table, recreate it with the new schema and reinitialize it.