gemail/srvskp-demo-xslt repository overview

⁠Demo of communication server SrvSKP with XSLT transformation in Docker

This is dockerized version of communication server SRVSKP. The demo presents a webservice->DB data integration project with data processing from XML to SQL using XSLT transformation.

⁠What is communication server SrvSKP?

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 transformed.

⁠Demo explanation

The figure below shows the configuration of the system based on three sensors. The first (USERS) is an offline server that periodically communicates with an external system, e.g. a website or webservice (server PIDTLS on port 9995). The content of the request can be constant (defined in the configuration using the sts-cmd-check xml tag) or received from the database thanks to the db server. The request is sent cyclically every time-cmd-interval milliseconds. In the event of an error, the system stops sending data for time-err-interval milliseconds. Before sending, the request is processed in the snd_decode function of the plugin associated with sensor 1 (decode-java-class tag). After sending the data, the sensor expects a response. After receiving it, it is redirected to the rcv_decode function and then forwarded to sensor no. 2 (XSLT) (thanks to the definition of the snrcache attribute in sensor no. 1). There it is processed again in the snd_decode function of the sensor 2 (XSLT) plugin and sent to the receive queue of this sensor (this is typical only for a cache type sensor), where after processing in the rcv_decode function of the plugin, if the db-insert-string action is defined, interaction with the database takes place, and then the message is finally passed on to the next sensor indicated in the snrcache attribute of sensor no. 2, i.e. in this case - sensor no. 3 (SQL). As we can see, something like a conversion chain of the received message is created. What its final shape will be depends on the plugins associated with the sensors. The message can be compressed, encrypted, replicated and any other action that we are able to program in the snd_decode and rcv_decode functions of the individual plugins. The flexibility of the system is unlimited.

⁠Dataflow in real demo

In the presented example, data is transferred between sensors according to the scheme:

USERS --> DELTX --> XLST-> SQL

• sensor USERS - receives data from server PDSTLS
• sensor DELTR - receives data from USERS and delete transmission markers: STX and ETX
• sensor XLST - receives data from DELTR and tranform data XML to SQL
• sesnor SQL - receive data from XSLT and write data to database (HSQLDB database baseA) - sql commands in log ./los/db_sql.log

and

NUSER --> DELH --> XLST-> SQL

• sensor USER - receives data from web service/www server (NanoHTPD)
• sensor DELH - receives data from NUSERS and delete HTTP headers
• sensor XLST - receives data from DELH and tranform data XML to SQL
• sesnor SQL - receive data from XSLT and write data to database (HSQLDB database baseA) - sql commands in log ./los/db_sql.log

You can see that the XSLT and SQL sensors are common to both processes.

⁠Usage

You can run this demo simply:

docker run -itd -p 9999:9999 -e ENV_STR_LICENSE=<license_string> --name srvskp gemail/srvskp-demo-xslt

Tip! Free license for two weeks you can download from the website Free License⁠

Port 9999 will provide WebGUI console for the SrvSKP communication server

https://<address_ip_docker_host>:9999/command

or

docker run -itd -p 9999:9999 -p 8084:8084  -e ENV_STR_LICENSE=<license_string> --name srvskp gemail/srvskp-demo-xslt

if you want metrics for prometheus:

https://<address_ip_docker_host>:8084/metrics 

or check healthy:

https://<address_ip_docker_host>:8084/health

For a proper understanding of the operation of the SrvSKP communication server, it is recommended to log in to the running container:

docker exec -it srvskp bash

⁠Starting internal console of communication server SrvSKP

After logging into the container, to start the communication server console, execute the command:

./runcmd.sh

If the server is working properly you will see the prompt:

demo@SrvSKP=>

First, use the snr or snrstat command to display a list of available communication sensors.

demo@SrvSKP=>snr
SENSORS:
Name            Status          CxErr   TxErr   RxErr   TxCount         RxCount         DBErr   CRestart        SnrCache
INF             ACTIVE[ACTIVE]  0       0       0       0               0               0       0
SNDSRV          ACTIVE[ACTIVE]  0       0       0       75              75              0       0               TCPSRV
RCVSRV          ACTIVE[ACTIVE]  0       0       0       75              75              0       0
TCPSRV          ACTIVE[ACTIVE]  0       0       64      75              75              0       0
PIDSND          ACTIVE[ACTIVE]  0       0       0       0               0               0       0               PIDTLS
PIDRCV          ACTIVE[ACTIVE]  0       0       0       902             902             0       0
PIDTLS          ACTIVE[ACTIVE]  0       0       902     902             902             0       0
SQL             ACTIVE[ACTIVE]  0       0       0       1583            1583            0       0
XSLT            ACTIVE[ACTIVE]  0       0       0       1583            1583            0       0
DELTX           ACTIVE[ACTIVE]  0       0       0       902             902             0       0               XSLT
USERS           ACTIVE[ACTIVE]  0       0       0       902             902             0       0               DELTX
DELH            ACTIVE[ACTIVE]  0       0       0       681             681             0       0               XSLT
NUSERS          ACTIVE[ACTIVE]  0       0       0       681             681             0       0               DELH

Next you can display db servers (command db or dbstat):

demo@SrvSKP=>db
DB:
Name            Status  CNTLoop         SELLoop CMDLoop CFailed CRestart
DBI             ACTIVE  459             1       0       0       0

If we want to view details about a sensor, we use command identical to the sensor name:

demo@SrvSKP=>USERS
SENSOR INFO:

----------------
USERS
----------------
Status          : ACTIVE [ACTIVE]
Prot            : tcps-trustall
Address IP      : 127.0.0.1
Port            : 9995
Proxy IP        : null
Proxy Port      : null
Type            : offline
StsCmd          : GET /users HTTP/1.0
User-Agent:ServSKP 3.0.18
Accept-Encoding: deflate
Accept-Language:pl-PL,pl;q=0.8,en-US;q=0.6,en;q=0.4


WriteResp       : all
iTime           : 15000
iTimeError      : 15000
iTimeReconn     : 15000
StsConn         : only online
CxCount         : 0
CxTime          :
CxErrors        : 0
CxMsgErr        :
RxCount         : 919
RxErrors        : 0
RxTime          : 2024-12-22 08:56:23
RxCharResp      : 3
.........................
.........................
InsertSttm      :
DBSqlOut        : ---
DBactSqlOut     : ---
DBSqlIn         : ---
DBactSqlIn      : ---
DBUpdErr        : 0
DBUpdMsgErr     :
DbInsErr        : 0
DbInsMsgErr     :
LastError       :
DBProperties    :

Other available commands can be displayed using the h or help command:

demo@SrvSKP=>h
Commands:
[h]elp - display this help
[q]uit - exit from commandline mode
[db]stat    - all DB servers statistics
[snr]stat   - all sensors statistics
[cache]stat - statistics of queues (cache)
{name_of_sensor} net     - network statistics of sensor
{name_of_sensor} active  - sensor status change
{name_of_sensor} suspend - pause sensor
{name_of_sensor} status  - sensor status
{name_of_sensor} reconnect  - reset sensor connections
{name_of_sensor} restart    - restart sensor
{name_of_sensor} statreset  - reset errors statistic
{name_of_sensor} view       - display fragment of cache
{name_of_sensor} clean      - remove cache
{name_of_sensor} ip=1.2.3.4 - change sensor IP
{name_of_sensor} port=1234  - change sensor port
{name_of_sensor} snrcache=abc - change of the related sensor
{name_of_sensor} debug=<integer> - change sensor debug level
{name_of_sensor} cmd=a_b_c_d{CR}{NL} - send acommand to sensor
{name_of_sensor} - sensor statistics

⁠Log analysis

Logs from process are available in directory: ./logs

• err.log - error log
• srvskp.log - server log
• db_sql.log - database change log

⁠Starting HSQLDB database console

If you want to observe changes in the database, run the HSQLDB database console (baseA):

./runHCON_A.sh

and run command:

sql> select * from users;

You should see something like this:

ID      CONTACT
------  --------------------------------------------
user_1  John, Doe, 1126: 808 Elm St, Anywhere, NY
user_2  Jane, Noone, 1126: 123 Some Rd, Anywhere, NY
user_3  Dean, Duval, 124 Ulm St, Anywhere, NY
user_4  Denis, Beker, 125 Road Rd, Anywhere, NY

If you this query to database periodically, you will notice from time to time that the user data user_1 and user_2 change (number before address). For user_3 and user_4 data does not change, but if you want delete this data with sql command:

sql> delete from users where id in ('user_3','user_4');

After a while (about 20 second), the data will be entered again :).

⁠Related links

• Official site of SrvSKP⁠