Skip to content

Database

The Ioto agent database (DB) is an ultra high performance NoSQL database for embedded applications. It supports fast, in-memory local data access and optional transparent synchronization of data to the cloud. It is modeled after AWS DynamoDB and the OneTable access library.

DB is a NoSQL database where database items are JSON documents of arbitrary complexity. Data items are organized into entity tables via an entity schema that specifies data fields, types, attributes and validations.

DB uses Red/black binary search indexes and has controllable local persistency to disk and to the cloud on a per-table basis.

Database Features

  • High performance NoSQL management document database.
  • JSON document items with flexible query API.
  • Efficient import and export of database items.
  • Red/black binary search indexes.
  • Simple, non-waiting API.
  • Controllable persistency with change triggers.
  • Transparent bi-directional data synchronization with the cloud.
  • Unified data schema between device and cloud databases.
  • Based on DynamoDB OneTable.

API Quick Tour

To open a database, use dbOpen:

Db *db = dbOpen("./data.db", "./schema.json5");

The dbOpen API will open the named database and entity schema. Data is loaded from the database file and stored in-memory. The schema file describes the application entities, data fields and indexes.

State Database

By default, the Ioto agent will open a state database from "state.db" using the schema "schema.json5". These can be controlled and configured via the config.json5 properties under the database key. You would typically use this database and would not need to open your own instance. The database instance is stored in the global agent object.

agent->db

Schemas

Schemas define how items will be stored in the database. A schema specifies the data indexes and application models (entities). Think of models like SQL tables, except they can be nested to arbitrary depth.

Schemas look like this:

const DeviceSchema = {
    format: 'onetable:1.1.0',
    version: '0.0.1',
    indexes: {
        primary: { hash: 'pk', sort: 'sk' },
    },
    control: {
        /*
            Where the model is enabled (cloud, device, both. Default is both)
            Synchronization direction. Up to the cloud, down or both. Default is none.
        */
        Status:  { enable: 'cloud' },
        Fault:   { sync: 'up' },
    },    
    models: {
        Status: {
            pk:             { type: 'string', value: 'status#' },
            sk:             { type: 'string', value: 'status#' },
            parameters:     { type: 'object' },
            version:        { type: 'string' },
            updated:        { type: 'date' },
        },
        Fault: {
            pk:             { type: 'string', value: 'device#${deviceId}' },
            sk:             { type: 'string', value: 'fault#${id}' },
            deviceId:       { type: 'string', required: true },
            id:             { type: 'string', generate: 'ulid' },
            timestamp:      { type: 'date', required: true },
            source:         { type: 'string', required: true },
            severity:       { type: 'string', required: true, enum: ['info', 'warn', 'error', 'critical', 'fatal'] },
            subject:        { type: 'string', required: true },
            message:        { type: 'string', required: true },
        },

    },
    params: {
        'isoDates': true,
        'timestamps': true,
    },
}

Schemas define the entities you wish to store in the database with the fields and data types that comprise those entities. You can flag a field as "required". Other fields can have their value automatically "generated" when creating. This is useful for creating unique IDs.

Other fields can have their values determined using "value" templates based on the values of other fields. This is how the primary and sort key values are typically calculated.

To create an item in the opened database, use dbCreate:

1
2
3
4
5
6
DbItem *item = dbCreate(db, "Fault", DB_PROPS(
    "timestamp", rGetTime(),
    "source", "bluetooth",
    "severity", "warn",
    "subject", "Failed to sync",
), NULL);

This creates an item in the database and returns the item. The field name / value pairs are provided using the DB_PROPS macro.

To retrieve a field from an item, use dbGetField:

const char *id = dbGetField(item, "id");

Database items are documents that store item properties in JSON format using JSON types (Objects, arrays, strings, numbers, booleans and dates). Properties can be nested to arbitrary depth.

Ioto includes a powerful JSON query engine that you can use to query and manipulate JSON documents.

cchar *errors = dbGetField(item, "interfaces.bluetooth.if1.errors");

To retrieve an item, supply the required key and call dbRead

DbItem *item = dbRead(db, "Fault", DB_PROPS("id", id), NULL);

In debug mode, you can use the utility routine dbPrintItem to print the contents of an item:

dbPrintItem(item);

To retrieve a set of matching items, use dbFind:

DbGrid *items = dbFind(db, "Fault", DB_PROPS("severity", "critcal"), 0);

You can provide one or more properties to be used as a filter. Items will be returned that match all supplied properties.

To iterate over the item list, use ITERATE_ITEMS:

1
2
3
4
5
6
DbItem *item;
int    n;
for (ITERATE_ITEMS(items, item, n)) {
    dbPrintItem(item);
}
dbFreeGrid(items);

You can also use DB_JSON instead of DB_PROPS to provide query parameters as json:

items = dbFind(db, "FAULT", DB_JSON("{severity:'critical'}"), 0);

The JSON can be provided as JSON/5 which allows single quotes around string values.

To get a single field from an item, use dbGet:

cchar *severity = dbGet(db, "Fault", "severity", DB_PROPS("id", id), NULL);

To set a single value in an item, use dbSet:

DbItem *item = dbSet(db, "Fault", "severity", "info", DB_PROPS("id", id), NULL);

To update multiple fields in an item, use dbUpdate:

item = dbUpdate(db, "Fault", DB_PROPS("id", id, "severity", "info", "subject": "Fault resolved"), NULL);

To remove an item, use dbRemove:

dbRemove(db, "Fault", DB_PROPS("id", id), NULL);

Persistency

The Ioto database supports a "change" trigger that will be invoked whenever a change is made to the database.

Ioto uses this trigger, to save changes persistently to flash or disk. It also uses the trigger to replicate changes to the cloud for synchronization.

You can save database contents at any time via the dbSave API:

dbSave(db, NULL);

This will persist data changes to the database file specified via dbOpen. You can supply a filename as the second argument to save to a different (backup) database file.

JSON documents

The database stores items as JSON documents that are a nested collection of properties to arbitrary depth. As JSON is one of the most prevalent data exchange formats, storing data in JSON greatly reduces exchange costs.

Ioto includes a powerful JSON query engine that you can use to query and manipulate JSON documents.

Data Types

All data types are stored internally as strings to optimize data transfer. Return values from database APIs typically return static strings that you do not need to free (const char). To convert numbers, use the safe runtime conversion routines: stoi() and stoiradix().

Indexes

DB uses red-black balanced binary trees for indexes to provided ordered, rapid indexing of data. Currently, the database only supports a single primary index, but in the future, multiple indexes will be supported.

Cloud Synchronization

Ioto can synchronize changes to the cloud on a per table basis. The schema "Control" property defines how to synchronize each model. You can "enable" a model to be stored in the cloud, in the device or in both places. The "sync" property defines the synchronization direction. You can replicate changes from the device, from the cloud or from both cloud and device.

To design for effective synchronization, it is best to have the "sync" direction be either "up" or "down". You should only select "both" for models that are not transactionally critical as changes from the cloud or device may overwrite changes coming from the other directions.

Synchronization is done on a per-item basis and not field by field.

Debugging

Ioto provides a few routines that help with debugging DB API usage.

  • dbPrint
  • dbPrintItem
  • dbPrintProperties
  • jsonToString

References

OneTable Schema Spec.