opcua::sqlmodel

Name

opcua::sqlmodel - SQLite based address space models for topcua

Synopsis

package require topcua::sqlmodel
opcua::sqlmodel::export handle ...
opcua::sqlmodel::import handle ...
opcua::sqlmodel::ns0full handle
opcua::sqlmodel::mkspecsdb dbname ...
opcua::sqlmodel::loadspec handle name ...
opcua::sqlmodel::listspecs ?dbname?
opcua::sqlmodel::getunece ?dbname?

Description

These commands provide several operations to deal with an OPC/UA address space using SQLite as a storage backend. Additional commands allow for loading companion specs in compressed XML format from a central per-user database. For the address space export operation, handle refers to a client or server object. The address space import operation requires handle to refer to a server object which must be stopped, i.e. its network layer must be inactive.

opcua::sqlmodel::export handle -file name

Exports the OPC/UA address space of handle to the SQLite database name. The database file is created, temporarily opened, written, and finally closed.

opcua::sqlmodel::export handle -db db ?-schema schema?

Exports the OPC/UA address space of handle to the SQLite database handle db which must refer to an open, writable SQLite database. Optional schema is the table name prefix in case an attached database below db shall be written.

opcua::sqlmodel::export handle -data varname

Exports the OPC/UA address space of handle to variable varname as a serialization of an SQLite database.

opcua::sqlmodel::export handle -chan chan

Exports the OPC/UA address space of handle to an open and writable channel chan which is written with a serialization of an SQLite database.

opcua::sqlmodel::import handle -file name

Imports into the OPC/UA address space of handle from the SQLite database name. Only non-existing nodes, references, and data type information is imported. The database file is temporarily opened, read, and finally closed.

opcua::sqlmodel::import handle -db db ?-schema schema?

Imports into the OPC/UA address space of handle from the SQLite database handle db which must refer to an opened, readable SQLite database. Optional schema is the table name prefix in case an attached database below db shall be used for the import. Only non-existing nodes, references, and datatype information is imported.

opcua::sqlmodel::import handle -data value

Imports into the OPC/UA address space of handle from a serialized SQLite database in value.

opcua::sqlmodel::import handle -chan chan

Imports into the OPC/UA address space of handle from a serialized SQLite database which is read form the open and readable channel chan.

opcua::sqlmodel::ns0full handle

Loads full namespace zero into the server object handle from a compressed local database file ns0.db.gz which must be located in the directory where this package has been installed.

opcua::sqlmodel::mkspecsdb dbname ?tag?

Tries to create a database of OPC/UA companion specs into dbname. The XML nodeset data is obtained from a ZIP which is downloaded from github. tag is the git tag, latest is the default tag. The database is space optimized and has the tables Models, Requires, and UNECE which provide meta information of the companion specs and the corresponding XML nodeset data in gzip format.

opcua::sqlmodel::loadspec handle name ?dbname?

Looks up the companion spec name in the database dbname or if dbname is omitted in $::env(HOME)/uaspecs.db and loads the corresponding XML into handle which must refer to a server object. name may be given as URL or as short name, e.g. DI. The loading process inspects both the current server address space and the database and tries to resolve required companion specs automatically.

opcua::sqlmodel::listspecs ?dbname?

List companion specs in the database dbname or if dbname is omitted in $::env(HOME)/uaspecs.db. The result is a list of alternating short names and URLs.

opcua::sqlmodel::getunece ?dbname?

Return UNECE information from database dbname or if dbname is omitted from $::env(HOME)/uaspecs.db as list of alternating UNECE code, unit identifier, display name, and description.

Database schema for OPC/UA address space

In contrast to XML models the SQL data model is a full description of the address space, i.e. always includes all namespaces starting from zero. The import process initially examines the current state of the address space and adds only those nodes from the SQL model which are not existing yet.

CREATE TABLE Nodes(
    NodeId VARCHAR PRIMARY KEY NOT NULL,
    NodeClass INTEGER NOT NULL DEFAULT 0,   -- ref to NodeClasses
    BrowseName VARCHAR NOT NULL,
    DisplayName INTEGER NOT NULL DEFAULT 0, -- ref to LocalizedTexts
    Description INTEGER,                    -- ref to LocalizedTexts
    WriteMask INTEGER NOT NULL,
    UserWriteMask INTEGER NOT NULL,
    IsAbstract INTEGER,
    Symmetric INTEGER,
    InverseName INTEGER,                    -- ref to LocalizedTexts
    ContainsNoLoops INTEGER,
    EventNotifier INTEGER,
    Value TEXT,
    ParentId INTEGER,                       -- ROWID of Nodes
    ReferenceId INTEGER,                    -- ROWID of Nodes
    ReferenceTypeId INTEGER,                -- ROWID of Nodes
    DataType INTEGER,                       -- ROWID of Nodes
    ValueRank INTEGER,
    ArrayDimensions VARCHAR,
    AccessLevel INTEGER,
    UserAccessLevel INTEGER,
    MinimumSamplingInterval DOUBLE,
    Historizing INTEGER,
    Executable INTEGER,
    UserExecutable INTEGER,
    DataTypeDefinition INTEGER,             -- ref to DataTypeDescriptions
    RolePermissions INTEGER,
    UserRolePermissions INTEGER,
    AccessRestrictions INTEGER,
    AccessLevelEx INTEGER
);

CREATE TABLE LocalizedTexts(
    "Key" INTEGER NOT NULL,
    Locale VARCHAR NOT NULL DEFAULT "",
    Text VARCHAR NOT NULL,
    PRIMARY KEY("Key", Locale)
);

CREATE TABLE NodeClasses(
    "Key" INTEGER PRIMARY KEY NOT NULL,
    Name VARCHAR NOT NULL
);

CREATE TABLE DataTypeDescriptions(
    "Key" INTEGER PRIMARY KEY NOT NULL,
    DefaultEncodingId INTEGER,              -- ref to Nodes
    BaseDataType INTEGER,                   -- ref to Nodes
    StructureType INTEGER NOT NULL
);

CREATE TABLE StructureFields(
    "Key" INTEGER PRIMARY KEY NOT NULL,
    DataTypeDescription INTEGER NOT NULL,   -- ref to DataTypeDescriptions
    Name VARCHAR NOT NULL,
    Description INTEGER,                    -- ref to LocalizedTexts
    DataType INTEGER,                       -- ref to Nodes
    ValueRank INTEGER NOT NULL,
    ArrayDimensions VARCHAR,
    MaxStringLength INTEGER NOT NULL DEFAULT 0,
    IsOptional INTEGER NOT NULL DEFAULT 0
);

CREATE UNIQUE INDEX StructureFieldsIndex1
    ON StructureFields(DataTypeDescription, Name);

CREATE TABLE "References"(
    NodeId INTEGER,                         -- ref to Nodes
    Source INTEGER,                         -- ref to Nodes
    Target INTEGER,                         -- ref to Nodes
    IsForward INTEGER NOT NULL DEFAULT 1,
    PRIMARY KEY(NodeId, Source, Target, IsForward)
);

CREATE TABLE Namespaces(
    "Index" INTEGER PRIMARY KEY NOT NULL,
    URL VARCHAR NOT NULL
);

Database schema for Companion Specs etc.

CREATE TABLE Models(
    Model VARCHAR NOT NULL,                 -- URI
    Name VARCHAR UNIQUE NOT NULL,           -- short name
    Version VARCHAR DEFAULT '',
    PublicationDate VARCHAR DEFAULT '',
    XML BLOB NOT NULL,                      -- gzip compressed
    PRIMARY KEY(Model)
);

CREATE TABLE Requires(
     Model VARCHAR NOT NULL,                -- URI
     RequiredModel VARCHAR NOT NULL,
     RequiredVersion VARCHAR DEFAULT '',
     RequiredPublicationDate VARCHAR DEFAULT '',
     PRIMARY KEY(Model, RequiredModel)
);

CREATE TABLE XmlSchema(
    Name VARCHAR UNIQUE NOT NULL,           -- URI
    XML BLOB NOT NULL                       -- gzip compressed
);

CREATE TABLE UNECE(
     UNECECode VARCHAR NOT NULL,
     UnitId VARCHAR NOT NULL,
     DisplayName VARCHAR NOT NULL,
     Description VARCHAR NOT NULL,
     PRIMARY KEY(UNECECode)
);