Эта страница является переводом страницы System-self DB. Перевод выполнен на 100%.

Other languages:

English • ‎русский

Other languages:

English • ‎русский

Содержание

1 Work with databases
2 System Database
3 User Database
- 3.1 Automatic clearing of User Database
4 Creating Requests

Work with databases

System databases. User databases.

Требуется:

iRidium server

documentation was last updated on 24.5.2023

A database (DB) is a logical object where tables and indexes are placed.

There are the following types of bases in iRidium:

System;
User.

Before starting work with a database, it is required to set tags to write in the database.

It can be set inly in a server proejct.

System Database

System database is created together with a server project and it stores data coming from tags.

It supports only read request.

System database contains several tables:

FLOAT_TAG_HISTORY (contains ID, TAG_ID, DATETIME, QUALITY, VALUE)
INTEGER_TAG_HISTORY (contains ID, TAG_ID, DATETIME, QUALITY, VALUE)
QUALITY_LIST (contains ID, NAME)
STRING_TAG_HISTORY (contains ID, TAG_ID, DATETIME, QUALITY, VALUE)
TAGS_PASSPORT (contains TAG_ID, TAG_TYPE, NAME, STATUS)
TAG_STATUSES (contains ID, NAME)
TAG_TYPES_LIST (contains ID, NAME, TABLE_NAME)

Location

The file of a database can be founf via Web-server. To do it, open the web-page of iRidium server in a browser. Enter your account. A page of the main page appears. It contains tabs to work with the server. The location of the database is written in the first line of the Database tab.

Automatic clearing of System Database

Скоро в релизе.

It is possible to use the automatic clearing of system database if the device memory is running out of space on which iRidium Server app is launched.

Automatic clearing of system database is available on the server starting from v.XXX.

The automatic cleaning function has the following database fill levels:

Normal - normal level [0-80%],
Warning - issue warning [80-90%],
Cleaning - database cleanup starts [90-95%],
Overflow - database is overflowing - writing to all database tables stops [95-100%].

In this case the maximum level of filling the database is 300MB.

Information about reaching each level, as well as the start and end of the database cleaning process is displayed in the server log.

A strategy for cleaning the system database:

Cleaning - records of feedback channel values are deleted:
- as whole files,
- from old to new,
- evenly across all feedback channels (principle of fairness - if some channel takes up more space than others, then accordingly, more files will be deleted for it),
- to the fill level Normal (at the border c Warning).
Overflow:
- database goes into "Readonly" mode,
- the same steps as for "Cleaning" are done,
- If memory is successfully cleaned below the Overflow level, the database exits the "Readonly" mode.

If the server is launched for the first time and there is no old database IridiumStorage.db in its documents, automatic clearing will be enabled by default and the database will be immediately saved in the format *.csv. Otherwise, it is necessary to activate automatic clearing of the database manually (see the instruction below).

If you want to turn off the automatic clearing of system database for any reason, write to our technical support: support@iridi.com.

Activating automatic clearing function of system database

It is possible to connect automatic clearing of system database to the server after upgrading the used server to version XXX.

To activate it, it is necessary to:

1 Go to the web-interface of the iRidium Server app: http://192.168.1.254:8888/ (write the address of your device).

2 Go to the System > Database tab.

3 In the Database cleanup section, press the Activate cleanup button.

4 In the warning window press the Activate cleanup button to activate automatic clearing function of database or press the Cancel button to cancel.

After activating automatic clearing function of system database data recording to the old file will stop and start over in a different (*.csv) format.The old data will not be available for the iRidium Server app, but the old database file will remain in the system. If you don't need this data or if you have already copied it somewhere else, you can delete it (see the instructions below).

Moving and Deleting an Old Database

If the iRidium Server app is running on a Windows system:

1 Go to the folder with the system database (by default - C:\Users\<User Name>\Documents\iRidium pro documents\Server\Database).

2 Copy the file 'IridiumStorage.db to another location on the disk or delete this file (if the old data is no longer needed).

If the iRidium Server app is running on a Linux system:

1 Install and run WinSCP.

2 Click on the New Site tab and configure:

File Protocol: SCP
Host name: <IP-address of the equipment>
Port number: 22
User name: login
Password: password

3 Click on Login.

4 Go to the folder with the system database of the iRidium Server app (This folder is usually located in: /var/lib/iRidium Server/DataBase/ or - /root/iRidium Server/DataBase/)

5 Copy the IridiumStorage.db file to the necessary folder on your computer or delete this file from the system database folder of the iRidium Server app if the old data is no longer needed.

Setting to Send Data to DB

Create a feedback channel for a required device and select Store In DB parameter in the settings. Store In DB - sets the type of data to write incoming values:

None - tag values are now written in the database;
Signed 32bit - tag values are written as integers;
Float 64bit - tag values are written as decimal numbers;
String UTF8 - tag values are written as UTF8 string.

After selecting the type of data, you can select a saving strategy:

Deadband - if the tag value is less than the number set in "Deadband" field, a value is not written in the database.This strategy works only for numbers;
Interval - a value is written at a set time interval. An interval can be selected from the list or a custom interval can be set. To do it, select "Custom" in the "interval field" and enter the required period and a measurement unit.

When Deadband = 0 every change of tag is written in the database (it does not matter how a value changes). The size of a database grows very fast with such a setting.

Work with Database via JavaSript

It is work only with the old *.db type of database file (created by the server prior to version XXX.) It will not work with the new *.csv type.

Create a script with requests that writes data in the log under certain conditions.To work with requests, it's required to learn SQL language. Frequently used scenes of work with the database are described below.

var sql = IR.GetDatabase(); // Assigning sql variable the property of a system database.

Request from DB:

Assign examp variable the property of sql Request base. The body of a request is written in brackets and single inverted commas.

var examp = sql.Request('SELECT ...');

Logging data from the base:

IR.Log(examp.Columns); // the number of fields (columns) in a request is logged
IR.Log(examp.Rows); // the number of strings in a request is logged
IR.Log(examp.GetRowValue(0,0)); //cell value of a string with indexes (line, column) is logged;
IR.Log(examp.GetColumnName(0)); //field name which index agrees with the number in the brackets is logged.

Example: to select information about tags with a value < 1500 in the database

var i;
var j;
IR.AddListener(IR.EVENT_START,0,function()
{
var sql = IR.GetDatabase(); // assign sql variable the property of a system database.
var examp = sql.Request('SELECT DISTINCT(ID), VALUE, TAG_ID FROM FLOAT_TAG_HISTORY WHERE VALUE<1500'); // Assigning the examp variable the property of sql Request base. The body of a request is written in brackets and single inverted commas.
for (i=0;i<examp.Columns;i++) //a cycle to output values is organized
   for (j=0;j<examp.Rows;j++)
   {
      IR.Log(examp.GetRowValue(i,j); //logging field values.
   };  
});

User Database

User database is created by an integrator. There is an option to format the database.

Example_RU

Example_EN

To create a user databse, do the following actions in the script editor:

1Create a variable where you will write the properties of the database.

var mybase= new SQL();

mybase - variable,
new SQL() - creating database object in iRidium JS.

2Create a database file for further work.

mybase.Open('Example.db');

Open - to open the database file. If there is no file of this kind, it will be created in the folder of software documents, for example, C:\Users\UserName\Documents\iRidium mobile 3\Settings\ Server\Database\Example.db - the database name with the suffix. Logical commands must be used to write in the DB fast and correctly.

mybase.Execute("BEGIN");
...
// Operations with a database; 
...
mybase.Execute("COMMIT"); 
mybase.Close();

Execute - function to do actions with the base,
"BEGIN" - start of writing in the database.
"COMMIT" - end of writing in the database.
Close() - Closing database.

3Create a table. This table will be empty.

mybase.Execute('CREATE TABLE Info(ID int, Value double, Name string)');

CREATE TABLE - a function to create a table named Info
ID, Value, Name are the names of the table fields which will contain such values as int, double, string. You can change the names of the fields but you must indicate the field type.

4Writing in Table.

mybase.Execute('INSERT INTO Info(ID, Value, Name) VALUES(1,2.5,"sunny day")');

INSERT INTO writes in the Info table, to be more exact in the ID, Value, Name fields.
VALUES are parameters that are written in the fields. Thus, 1 is written in the ID field, 2.5 will be written in the Value field and "sunny day" will be written in the Name field.

The number of parameters in brackets after Values must be the same as the number of fields in the brackets ater INSERT INTO (). There are several special features of writing values in table fields. For example, if you want to write text, the syntax looks this way:

VALUES("Iridium", "Mobile"); (text is written in double inverted commas).

If you want to write a number, it must look like this:

VALUES( 1, 2.5, 36.788); (a number is written without inverted commas, a decimal number is written with a point)

If you want to write a variable value, the following is used:

// var CV = "Hello!"; (variables named earlier)
// var V = 1.25;
...
VALUES("'+CV+'",'+V+');

Here is the order: a single inverted comma, then plus, then the variable itself, then plus and a single inverted comma.

5Deleting a table, adding fields.

A method to delete a table:

mybase.Execute('DROP TABLE Testing');

Adding fields to an existing table.

mybase.Execute('ALTER TABLE Testing DROP COLUMN New_column');

Automatic clearing of User Database

Release is soon.

Data from the user database is not deleted when enabling automatic clearing of database on the web-interface of the server. But automatically activated data vacuumization (VACUUM), it allows to reduce the size of the database in general.

Data vacuuming works only for server version v.XXX or later.

The vacuuming process can lead to the system hanging up,if the base is too large also for a long time. If the iRidium Server app was launched with watchdog, it can also lead to restarting the system on Linux

Script listeners can be used to track the level of the database. It can be additionally remove some outdated data from the user database in order to reduce its size.

An example of the script

//Listener for NORMAL level
IR.AddListener(IR.EVENT_USER_DB_FILL_LEVEL_NORMAL, 0, function(name){
    IR.Log(name + ": NORMAL FILL LEVEL EVENT");
});
 
//Listener for WARNING level
IR.AddListener(IR.EVENT_USER_DB_FILL_LEVEL_WARNING, 0, function(name){
    IR.Log(name + ": WARNING FILL LEVEL EVENT");
});
 
//Listener for CLEANING level
IR.AddListener(IR.EVENT_USER_DB_FILL_LEVEL_CLEANING, 0, function(name){
    IR.Log(name + ": CLEANING FILL LEVEL EVENT");
});
 
//Listener for OVERFLOW level
IR.AddListener(IR.EVENT_USER_DB_FILL_LEVEL_OVERFLOW, 0, function(name){
    IR.Log(name + ": OVERFLOW FILL LEVEL EVENT");
     if(EndsWith(name, "MyBase.db"))
      {
              IR.Log("My own cleaning...");
              mybase.Open('MyBase.db', true);
              mybase.Execute('DELETE FROM Info WHERE ID < 1000');
              mybase.Close();
              IR.Log("End my own cleaning.");
      }
});
 
//Function for determining the name of the desired user database
function EndsWith(str, stringPart){
 
  if(str !== undefined && stringPart !== undefined) {
 
    var wordIndex = str.lastIndexOf(stringPart);
    var result = str.substring(wordIndex, str.length);
 
    if(result === stringPart){
      return true;    
    } else {
      return false;
    }
 
  }
 
}

Creating Requests

It is work only with the old *.db type of database file (created by the server prior to version XXX.) It will not work with the new *.csv type.

Let's have a look at the most popular ways to with a database. You can write in iRidium script editor requests that will log data under certain conditions. To work with requests, it's required to know SQL language.

var mybase= new SQL(); // assigning sql variable properties of a system database.

To create a request to the dase, for the following:

var this_request = mybase.Request('SELECT ...'); // Assign examp variable the property of sql Request base. The body of a request is written in brackets and single inverted commas.

Logging.

// the number of fields (columns) in a request is logged
IR.Log(this_request.Columns); 
 
// the number of strings in a request is logged
IR.Log(this_request.Rows);
 
//cell value of a string with indexes (line, column) is logged;
IR.Log(this_request.GetRowValue(0,0)); 
 
//field name which index agrees with the number in the brackets is logged.
IR.Log(this_request.GetColumnName(0));

Example: to select information about tags with a value < 10 in the database

function request()
{  
    //Opening database. If there is no database, it is automatically created.
    mybase.Open('Example.db');
 
    //Start of work with the database.
    mybase.Execute("BEGIN");
 
    // Creating a variable and assigning it Request method.
    var this_request = mybase.Request('SELECT * FROM Info WHERE Value<10');
 
    //field name which index agrees with the number in the brackets is logged.
    IR.Log(this_request.GetColumnName(0)+ '\t'+ this_request.GetColumnName(1)+ '\t'+     this_request.GetColumnName(2)); 
 
    for (i=0;i<this_request.Rows;i++) 
        //logging field values.
        IR.Log(this_request.GetRowValue(0,i)+ '\t'+ this_request.GetRowValue(1,i)+ '\t'+ this_request.GetRowValue(2,i));
 
    //End of work with the database.  
    mybase.Execute("COMMIT"); 
 
    //Closing database.       
     mybase.Close();
}