New modern DBMS SoQoL. Let's take a closer look at her

The first commercial release of a relational DBMS has been released – SoQoL version 3.0. In the Russian version, the name of the DBMS sounds like SOKOL.

What kind of bird is this?

To begin with, it must be said that the DBMS got its name thanks to the structured query language SQL and a beautiful, swift bird – the falcon.

It is worth noting that SoQoL is a Russian general-purpose relational DBMS with a modern architecture built from scratch, and not on open source products.

And now briefly and to the point.

Architecture

DBMS LINTER SoQoL is a classic disk DBMS from the user's point of view – the system is based on B-Tree disk storage and a WAL log (Write-Ahead Logging).

The SoQoL architecture in working with data supports a non-blocking approach from the lowest to the highest level, implements all ACID requirements without exception, and at the same time SoQoL is not an in-memory DBMS – it works not only with data in RAM, but also with data located on disk. This ensures high efficiency of the system during competitive data processing.

The architecture of the SoQoL DBMS is focused on the generation and execution of both IR (Intermediate Representation) code and native code.

Runtime environment

Now the SoQoL DBMS:

• works in the Linux x86-64 environment (distributions binary compatible with Red Hat8: Ubuntu 18.04 LTS, RHEL 8.0, Debian 10, Alt Linux 9.0, AstraLinux 1.7) and Windows10 (and higher);

• tested in the Elbrus OS environment;

• is in the process of confirming compatibility with Russian operating systems – Alt, AstraLinux, ROSA, RedOS, etc.

System requirements

The minimum requirements look like this:

Technical characteristics of the DBMS

Briefly:

• The maximum field length (except for BLOB/CLOB fields) is up to 8000 bytes. The size of a BLOB/CLOB value is up to 2^64 bytes;

• the maximum length of table and field names is up to 64 bytes;

• the maximum number of rows in the table is limited by the total table size – 2^64;

• the maximum number of indexes in a table is clearly not limited;

• the maximum number of tables in a query (at one level) is limited by the memory available when processing the query;

• maximum number of all database objects (users, tables, views, etc.) – up to 2^48

Administration

In the SoQoL DBMS, user databases are managed from a service provided by the DBMS server. For the management service, as well as for each user database, there is a separate connection point.

Administration in SoQoL includes organizing and managing databases, ensuring their availability, performance and security. Thus:

• the service administrator has access to commands to manipulate each database, which allow: creating and deleting a database, launching and modifying a database, stopping a database or service, restarting a server, etc.

• the database administrator has access to commands for managing data storage (creating and managing database objects) and security (controlling access to the database and its objects).

The DBMS implements auxiliary utilities for:

• execution of queries to the DBMS in interactive and batch mode (vsql_console);

• import/export data (vsql_copy).

Interfaces

Currently, the SoQoL DBMS supports ODBC, JDBC and PHP programming interfaces.

SoQoL's support for standard interfaces allows you to connect external visual tools for working with databases, for example, DBeaver.

Data types

At this point everything is as usual as possible. The SoQoL DBMS version 3.0.0 implements the following data types:

• strings – CHAR, VARCHAR, CLOB. It is worth mentioning here that in the current version the CHAR type is implemented as a synonym for VARCHAR;

• for storing dates – DATE, TIMESTAMP and DATETIME. The latter is implemented as a synonym for the DATE type;

• numeric – NUMBER, SMALLINT, INTEGER, INT, BIGINT, NUMERIC, DECIMAL, FLOAT, DOUBLE PRECISION, REAL. It is important to note here that some of the specified numeric types are implemented as synonyms for NUMBER (38,0), and the other part as NUMBER [(precision [, scale])];

• logical – BOOLEAN;

• binary – BINARY, VARBINARY, BLOB, RAW. The latter is implemented as a synonym for the VARBINARY type.

Operations

The SoQoL DBMS supports a large number of operations that can be divided into groups:

• string – concatenation;

• logical – “AND”, “OR”, “NOT”;

• arithmetic – addition, subtraction, division, multiplication, as well as unary “+” (plus) and “-” (minus);

• comparison operators – “=” (equal), “<" (less than), ">” (greater than), “BETWEEN”, “LIKE”, “EXISTS” and many others.

Query language

The query language in the SoQoL DBMS includes a set of standard DDL, DML, DCL commands of the SQL language, such as:

• commands for creating, changing and deleting database objects – CREATE, ALTER, DROP;

• data manipulation commands – SELECT, INSERT, UPDATE, DELETE;

• for access control – GRANT, REVOKE and others.

In addition, SoQoL has additional specialized commands, for example:

• EXPLAIN – command to obtain the query execution plan built by the DBMS scheduler;

• ANALYZE TABLE – a command to update the statistics of a database table, the results of which are subsequently used by the query planner;

• etc.

Database Objects

SoQoL DBMS supports various database objects. For example: tables (permanent and temporary), views, sequence generators, indexes, schemas, users, privileges and roles. It is worth mentioning here that tables in SoQoL are based on clustered indexes.

Functions

SoQoL supports built-in aggregate and scalar functions that can be used to process data when executing queries.

The most frequently used aggregate functions are supported: AVG, COUNT, MAX, MIN, STRING_AGG, SUM.

A set of the most popular scalar functions is supported:

• String functions such as CONCAT (to concatenate strings), SUBSTR (to extract a substring), INSTR (to find a substring), etc.

• functions for working with numbers, for example, functions for rounding (ROUND), calculating the absolute value (ABS), determining the largest and smallest value (), etc.

• functions for working with dates and times, for example: extracting part of a date (EXTRACT), formatting a date (TO_CHAR), etc.

• functions for working with NULL values, for example, functions for testing for NULL (NVL), replacing NULL values ​​(COALESCE), etc.

• functions for working with character case, for example, functions for converting to upper case (UPPER), lower case (LOWER), and others.

Integrity Constraints

In SoQoL, there are several types of integrity constraints that can be applied to tables in a database. For example:

• NOT NULL;

• PRIMARY KEY;

• CLUSTERED KEY;

• FOREIGN KEY;

• UNIQUE;

• CHECK.

Also, the SoQoL DBMS implements the procedural language PL/SOQOL, which has its own stored procedure language syntax, close to PL/SQL.

Procedural language inherits all data types, functions, operations that are used in SQL. PL/SOQOL statements and SQL commands are translated into homogeneous code, which eliminates execution context switches when executing PL/SOQOL statements and SQL commands alternately.

The following operators can be used in PL/SOQOL:

• assigning a value – “:=”;

• progress controls – IF-THEN-ELSE, CASE, LOOP, WHILE, FOR, EXIT, CONTINUE;

• data samples – SELECT INTO, FETCH INTO;

• exception handling – EXCEPTION, RAISE;

• for working with cursors – OPEN, CLOSE.

About rights and accesses

The SoQoL DBMS has a predefined set of privileges (system and object) and roles. The database administrator can create new roles and include both system and object privileges in them.

The current version of SoQoL implements discretionary access control: the database administrator controls the granting and revoking of privileges and roles. If a database user does not have administrative rights, then he can only grant other users object privileges on objects in his schemas.

In the near future, it is planned to implement mandatory access control in the SOKOL DBMS.

Technical support

The development company is Russian and there is the possibility of direct interaction with it. Technical support is provided in the native Russian language and there is the possibility of the physical presence of a technical specialist from the DBMS development team in the client’s region.

Technical support options vary depending on the license option you select.

Now technical support works for everyone in a question-answer format in telegram.

About plans

In the next couple of years, it is planned to certify the DBMS for compliance with the requirements of FSTEC and the Russian Ministry of Defense.

If you look at large blocks, then in addition to certification, for the further development of the DBMS it is planned:

• development of functionality;

• development of integration with clouds;

• increasing the level of reliability and safety, etc.

That's all for now. Thank you all for your attention!