Database Schema and Interaction

Iotellect Server database schema is fully dynamic, new tables are created and deleted by the server on-the-fly in order to reflect system resource creation, removal and reconfiguration.

However, there are a few basic tables that exist in every installation:

Table

Description

ag_properties

Contains values of all persistent server context variables. Iotellect Server generates relatively low number of INSERT and DELETE requests to this table when system objects are created and destroyed. The number of SELECTs is also relatively low due to server-side memory caching. However, ag_properties table is extensively UPDATE'd because values of persistent resource properties are modified and the server saves changed values to the database.

For example, devices that use persistent value cache cause a lot of ag_properties updates during synchronization.

Fields:

  • context - full path of context those variable's value is stored in the table row

  • property - name of the variable

  • data - a data table representing variable value encoded into string using invisible separators

ag_events

Contains all server context events except for ones that are stored in other (custom) tables. See Context Event Storage section below for details.

Fields:

  • context - full path of context the event was fired in

  • name - name of the event (i.e. event type)

  • creationtime - timestamp of event occurrence (or last event occurrence if multiple duplicates were registered)

  • expirationtime - future timestamp of planned event expiration time

  • level - event level

  • permissions - custom permissions required to access the event instance or null if permissions specified by the event definition should be used

  • count - number of registered event's duplicates

  • ack - an encoded list of event's acknowledgements

  • enrichments - an encoded list of event's enrichments

  • format - an encoded format of the event or null if format specified by the event definition should be used

  • data - a data table representing event data encoded into string using invisible separators

ag_data

Contains binary data blocks that are embedded into context variable values. Those blocks are extracted into this separate table when variable value is being persisted in ag_properties table. However, binary blocks are not loaded once variable value is loaded from ag_properties. The blocks are only loaded on-demand, i.e. by explicit requests of Iotellect Server modules, clients of external applications.

Context Event Storage

A large Iotellect installation can store billions of persistent context events in the database. Those events are dynamically distributed between different tables in order to optimize storage and retrieval performance.

Format of all event tables matches the format of ag_events table described above.

Access to event tables normally comprises a lot of INSERT operations. A new record is inserted every time a persistent server event is registered. The DELETE statements are issued over event table in two cases:

  • If a system resource with all associated events is deleted

  • If expired events are purged by a periodic scheduler task

The number of SELECT queries performed to event tables is relatively low. The events are loaded in the following cases:

  • If an Event Log is opened, causing subsequent activation of an event filter

  • If events are directly loaded by get() function of Events context

  • If variable history is explicitly loaded by variableHistory() function of Utilities context

  • During initial visualization of a widget chart that's configured to include historical events or variable values

  • In several similar cases

Despite events are rarely loaded, the number of events loaded by a single query is not limited by the platform since certain installations require millions of events to be loaded at once. System architects designing Iotellect-based solutions should take care of this and consider maximum number of loaded events when building server-side data processing chains and visualization dashboards.

An attempt to load too large number of events at once will cause Iotellect Server memory depletion and reproduce itself as severe performance degradation caused by Java Virtual Machine garbage collection, possibly causing full server freeze or internal memory errors.

The event tables list available in this documentation is not complete. Diverse Iotellect Server modules and plugins may request creation of other dedicated event tables that are not mentioned here.

All events that weren't explicitly configured to use a custom table are written to ag_events table. The below list describes database tables that are normally created to store events of different types.

Table

Description

ag_info

Contains Info events.

ag_alert

Contains Alert events.

ag_xxx_change

Contains all Change events representing changes of variables in a single device context (matching device xxx). Note, that the table name can be truncated due server-wide database table name length restriction.

Direct Access to Iotellect Server Database

Direct access of third-party applications to Iotellect Server database is strongly discouraged. Iotellect is a powerful platform offering a mature SDK and diverse kinds of APIs for local and remote access to all data elements contained in the server database. However, API-based access will always inherit proper locking, permission checking, and performance optimizations.

Direct modification of data contained in Iotellect Server database will in most cases cause incorrect system behaviour. However, direct read (SELECT) operations may be used in rare cases.

Please contact Iotellect for consulting on how to avoid direct database access.

Was this page helpful?