Databases and files

Configuration storage

The configuration database is stored in the directory named by configuration.dir in the process’s parameter file; ${DIR_PARAM}/Configuration, beside the parameter file, is a convenient choice. Without the parameter, the database is created in the executable’s directory. SQLite is supported out of the box, and PostgreSQL can be used when a separate database server is required.

SQLite

SQLite is the default embedded configuration database:

"configuration": {
    "driver": "SQLite",
    "dir": "${DIR_PARAM}/Configuration"
}

PostgreSQL

For distributed installations or stricter reliability requirements, PostgreSQL is also supported:

"configuration": {
    "driver": "PostgreSQL",
    "connection-string": "postgresql://user:password@localhost:5432/scada"
}

Migration from GigaBASE

GigaBASE support was removed starting with version 2.1, and scada-setup migrate moves an SQLite configuration only. If a site’s configuration is still kept in GigaBASE, first convert it to SQLite and run a version 2.5 Server on it, and only then run scada-setup migrate (see Upgrading from version 2.5). The conversion needs the version 2.0 administration utility; it is not published on the releases page — request it from Telecontrol (mail@telecontrol.ru).

Historical databases

Historical databases are stored in SQLite format.

The system historical database stores audit events and the latest object values. User historical databases store value changes and events for configured objects. Multiple user historical databases can exist at the same time, and the Server manages the required files automatically.

Historical databases are kept by the scada-historian process, in the folder named by history.dir in its parameter file; scada-setup sets %ProgramData%\Telecontrol\SCADA Server\historian\History. (A version 2.5 Server kept them in %ProgramData%\Telecontrol\SCADA Server\History; upgrading from version 2.5 copies them to the new folder.) Each database has its own subfolder named by its numeric id, except the system database, whose folder is System.

Each object can be assigned to one historical database. If an object is deleted or moved to a different database, its associated historical data in the previous database is removed.

Each database has a configured retention depth, and the Server cleans up old records automatically.

Disk-space estimate

New databases are created with SQLite’s standard page size of 4 KB; databases carried over from earlier versions keep theirs. For the permitted sizes, see the SQLite limits.

One object record takes less than 100 bytes. So the expected database size in MB is:

size = freq * depth / 10000

where:

  • size is the expected database size in MB
  • freq is the number of value changes per day
  • depth is the retention depth in days

Or, with a known amount of free disk space, the maximum retention depth in days is depth = size * 10000 / freq.

Working with SQLite directly

For administration and diagnostics, a history database can be opened with the sqlite3 command-line tool. Version 2.6 does not install it (versions 2.5 and earlier did); download it from the SQLite download page. The database file is named history and sits in the database’s folder. The data folder is closed to other users, so on Windows work from an elevated command prompt and on Linux with sudo: change to that folder and run sqlite3 history. Open a database for writing only while its process is stopped (scada-setup stop).

The Server maintains its databases automatically, so this is optional. A convenient graphical tool is the free SQLiteStudio. sqlite3 runs SQL queries and a number of special commands; see the SQLite documentation.

Examples

The database page size:

sqlite> pragma page_size;
4096

The tables: parameter values (timed_data), the latest object values (items_info), and one table per event type:

sqlite> .tables
AuditActivateSessionEventType  OperatorActionAuditEventType
AuditUpdateMethodEventType     SystemEventType
DeviceWatchEventType           items_info
ItemAlarmEventType             timed_data

A database carried over from an earlier version may also have an events_v1_backup table — the original old-format event table, whose data has already been moved to SystemEventType.

The schema of the system events table:

sqlite> .schema SystemEventType
CREATE TABLE SystemEventType(EventID INTEGER PRIMARY KEY NOT NULL, Time INTEGER NOT NULL, ReceiveTime INTEGER NOT NULL, ChangeMask INTEGER, Severity INTEGER, NodeNS INTEGER NOT NULL, NodeID INTEGER NOT NULL, UserID INTEGER, Value FLOAT, Qualifier INTEGER, Message TEXT, Acked BOOLEAN NOT NULL, AckTime INTEGER, AckUserID INTEGER, SourceName TEXT, MessageLocale TEXT, SourceNameLocale TEXT);
CREATE INDEX SystemEventType_TimeIndex ON SystemEventType(Time);
CREATE INDEX SystemEventType_NodeIndex ON SystemEventType(NodeNS, NodeID);

The number of system events in the archive:

sqlite> select count(*) from SystemEventType;
86

Server-side file system

The server-side file system stores schematic files on the Server so they can be provided to all Clients without manual synchronization.

The file system is kept by the scada-filesystem process, and the parameter file scada-setup writes already enables it. In a hand-built setup, enable it in the process’s server.json:

"filesystem": {
    "enabled": true,
    "dir": "${DIR_PARAM}/FileSystem"
}

The files are stored in the folder named by dir; scada-setup sets %ProgramData%\Telecontrol\SCADA Server\filestore\FileSystem. (A version 2.5 Server kept them in %ProgramData%\Telecontrol\SCADA Server\FileSystem; upgrading from version 2.5 copies them to the new folder.)

Files are managed from the Client Files window.