The SrvSKP communication server is software used to integrate IT systems in terms of exchanging data between them in an automated manner. In particular, it allows for the integration of database systems based on various database engines, whereby it is possible to transfer data between databases with completely different database schemas (i.e. the source tables do not have to be identical to the target tables). Thanks to the communication server, there is no need to build a communication layer and you can focus entirely on the data processing logic. The figure below shows a general diagram of the server operation and its capabilities.
The modular structure of the server and the use of the plugin mechanism allow for free modification of the transmitted data, but also for strengthening security through the use of mechanisms such as compression or multiple data encryption. The server allows for the combination of many transmission channels based on various transmission protocols, e.g. tcp/ip, udp or multicast. It also provides support for retransmission in the event of data transfer failure. The communication server is also excellent for downloading information from external IT systems (especially websites), processing it and saving it in local databases.
Examples of implementation
Due to the open architecture of the system, the scope of its implemention is very wide and diverse.
Below we have tried to list some of them:
- transferring data between databases with different schemas and data structures,
- downloading data from the Internet and saving it in local databases,
- downloading data from local databases and sending it to external/Internet systems,
- sending data to data warehouses or other databases,
- reading from measurement devices (RS232, RS485 or IP sensors) and saving the results in the database,
- sending data between different message brokers, e.g. Kafka or Rabbit MQ.
A few words about architecture
The architecture of the SrvSKP communication system is based on so-called sensors, which are nothing more than separate threads launched within the server. We distinguish two types of sensors:
- offline – communication sensors operating within a single thread in synchronous mode (after sending a query, the thread waits to receive feedback), in the case of this type of sensor, the connection is resumed each time a query is sent,
- online – communication sensors operating in asynchronous mode, where we are dealing with two threads: receiving and sending, the online sensor after establishing a connection maintains it all the time (in the case of stateful connections such as tcp/ip), after a possible connection loss it is established again as soon as it is possible (e.g. network problems disappear).
A special example of an offline sensor is the so-called cache (in the configuration attribute prot=”cache”). These are not communication sensors, but only for data processing. They most often operate based on additional plugins, e.g. XSLTMgDecode (translating input data from XML to another format, e.g. SQL queries). A set of plugins is provided with the server (we describe them later in the documentation), but you can also write your own plugins based on the template and attached examples to extend the functionality of the system.
Each sensor establishes a connection to a database, but not in every case it is involved in the operation of the sensor, therefore the system is supplied with the srvskp.db SQLite database, which can be used to connect to a sensor that does not necessarily work with a database (so-called internal database).
The second important element is sensors/servers db. These are cyclic threads that execute SQL queries to the associated database in order to detect changes in event tables. The names of event tables and the elements associated with them (triggers) can have any names, but the structure of the tables is predetermined. In the database, structures should be created as given below (example for the HSQLDB database):
CREATE TABLE cmd_out (
id_cmd BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1),
sensor character varying(60),
text_cmd longvarchar,
type_cmd character varying(3),
state_cmd character(1),
gate_cmd character varying(20),
send_time character varying(25),
send_err character varying(250),
send_resp longvarchar);
CREATE TABLE ecmd_out (
numer BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1),
reg_time character varying (40));
CREATE TRIGGER i_ecmd_out AFTER INSERT ON cmd_out
FOR EACH ROW
BEGIN ATOMIC
DELETE FROM ecmd_out WHERE 1 = 1;
INSERT INTO ecmd_out (reg_time) VALUES (CURRENT_TIMESTAMP);
END;
There can be any number of event tables (as many as sensors/servers db) on a single database – then we need to define a separate sensor/server db for each of them. Within a single communication server, we can work with multiple databases and multiple event tables. The figure below shows a minimal set of sensors and db servers for transferring data between the source and target databases.
In a cyclical manner, the db server executes a query on the cmd_eout table, checking whether the value in the numer field has changed (increased) in relation to the previously read value (step no. 1 in the figure). If so, then all records from the cmd_out event table that meet the conditions are read (step no. 2 in the figure):
- state_cmd=”N”
Then the read records (value of the text_cmd field) fill the transmission queues of the sensors according to the entry in the sensor field, e.g. a record with the entry sensor=”ABC” is directed to the sensor’s transmission queue named “ABC” etc. For such a record, the state_cmd field is changed to the value “W” (step no. 4 in the figure). The sensor receives the record from its queue and performs an action consistent with the sensor type and the actions defined in it. If it is a communication sensor, the buffer content is sent via a defined transmission channel with a further indicated protocol (e.g. tcp/ip, udp, multicast etc.) or the data can be transferred to the next sensor (defined in the snrcache attribute). When an additional SQL action is defined in the db-insert-string tag, then it is performed on the database associated with the given sensor (step no. 3 in the figure). If a plugin is associated with the sensor, before sending the data (and possibly after receiving the response), the data resulting from the used plugin is transforme.