• IT DT - data processing, transfering and transforming

Intro for ServerSig

ServerSig – lightweight and efficient websocket server connected to a database layer

If you want changes to any database table to be sent as a stream to a websocket recipient, you need to create an event table. The server, based on the event table, monitors whether changes have occurred in the source table and, if so, sends them to the server’s buffers and from there to connected clients. The server allows you to define the fields that will be sent in the stream. Multiple source tables can be defined in the server (as DSN). The server is very efficient – it can handle up to tens of thousands of connections.

DSN is defined as one line with fields separated && where the individual fields represent the following:

  • Name  – name of DSN
  • JDBC URL – connect string to database
  • User – user in database
  • Password – password for user (you can encrypt password – script runenc.sh – then you must enter the password in the format [encrypted]<encrypted password>)
    • after “-” – max connection (not used now – max connection is set in license string)
  • level – 0 – only one row is store in buffor (the index is the first field in the field definition for a given DSN), N – in buffor is stored N rows
  • number of skip messages – default is 0 (this parameter allows you to skip some messages when the table is changing very quickly and the above parameter is set to 0 or a low value (this value should be calculated as (1 or N) * number of unique values ​​in the first field defining the DSN * 2)
  • source table – table with data
  • event table – table to check if there were changes to the source table (based on the number field)
  • event time – sleep time before next check when check showed changes
  • control buffer – not used
  • count time – sleep time before next check when previous check showed no changes
  • where – fragment of an SQL query returning data from the source table

Example of DSN preparation (based on HSQLDB)

In database

Source table:

CREATE TABLE ws_counter1
( id BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1),
   name  character varying (20),
   value1 int,
   value2 DECIMAL(21,2),
   reg_time character varying (40));

Event table:

CREATE TABLE ews_counter1 (
number BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1),
 reg_time character varying (40));

And of course triggers:

CREATE TRIGGER i_ews_counter1 AFTER INSERT ON ws_counter1
FOR EACH ROW
BEGIN ATOMIC
DELETE FROM ews_counter1 WHERE 1 = 1;
INSERT INTO ews_counter1 (reg_time) VALUES (CURRENT_TIMESTAMP);
END;


CREATE TRIGGER u_ews_counter1 AFTER UPDATE ON ws_counter1
FOR EACH ROW
BEGIN ATOMIC
DELETE FROM ews_counter1 WHERE 1 = 1;
INSERT INTO ews_counter1 (reg_time) VALUES (CURRENT_TIMESTAMP);
END;

In file serversig.conf

Definition of fields:

DSN10=counter1_a&&jdbc:hsqldb:hsql://localhost:9001/baseA&&SA&&-1000&&0&&0&&ws_counter1&&ews_counter1&&2000&&0&&2000&& id>%s order by id

Field10=ws_counter1,name,%s
Field20=ws_counter1,value1,%s
Field30=ws_counter1,value2,%s
Field40=ws_counter1,reg_time,%s
Field50=ws_counter1,id,%s