Diasoft Database Adapter

Description

Diasoft Database Adapter is the program translator allowing information systems to work at different DBMS. Using this solution it is possible to adapt the applications created counting on use Oracle DBMS or MS SQL, to work with PostgreSQL DBMS without change of their source code. Diasoft Database Adapter can be also applied to failure from use proprietary DBMS for benefit of their free analogs.

Architecture

Diasoft Database Adapter represents the runtime-environment for operation of applications (DB Proxy) and the utility for implementation of migration (DB Migrator). Thanks to high percent of automation of the performed operations, time of migration is significantly reduced in comparison with manual translation.

Important advantages of the technology applied in the adapter:

• An opportunity to apply the components entering a product independently of each other. At this DB Migrator is an analog of widely known ORA2PG utility, close on functionality, however, unlike it, allows to transfer the constructions containing the logic written on PL/SQL. At the same time DB proxy allows to start the solutions addressing functionality DBMS Oracle, using as DBMS the PostgreSQL server to which of Oracle DBMS (means of DB Migrator or otherwise) the original data scheme was transferred previously.
• Conversion of the logic which is initially written on PL/SQL is performed using deep syntax and semantic analysis of the text of the source code that allows to provide high quality of converting.
• Making changes in binary modules of the initial application focused on Oracle DBMS is not required. A client part of the application in two-level architecture or the center in the application with three-level architecture remain invariable. Their adaptation to work with PostgreSQL DBMS is performed thanks to the fact that the requests requiring conversion remain in the uniform dictionary of data of the adapter. At the same time to each original request there corresponds the request adapted to PostgreSQL or Java class which can be prepared both automatically in the course of self-training, and manually, by means of the corresponding tools.

Such approach allows to reduce complexity of the project of migration in general, to organize all changes in limits of one dictionary of data and also to avoid a situation when changes in one request involve inevitable changes in other requests, related.

Scheme of work

Scheme of work with Database Adapter

DB Proxy

Scheme of work DB Proxy

At this approach own protocol implementation on which there is an interaction between the client of a DB and DBMS Oracle in the form of the DB Parser component is used. The client is connected to DB Proxy and continues interaction in the same way, as well as with Oracle DBMS. As a result existing application continues to work without changes in client (for two-level applications) or a middle part (for three-level applications).

The proxy performs the following functions:

• authentication proxying;
• proxying of SQL queries;
• return proxying of SQL answers.

Broadcast of SQL queries is performed by the PL/SQL Translator component according to the dictionary or online. In case of broadcasting per the dictionary in it all requests used by the application with previously determined parameters and also corresponding to these requests the options adapted to PostgreSQL or the instruction on a method call from already existing and available in CLASSPATH Java class are located previously. The text of requests is hashed and remains in RAM. Thanks to it search and replacement of a request from a dialect of Oracle in a dialect of PostgreSQL or switching of the adapter to execution of Java code occupies only several microseconds.

For filling of the dictionary DB Proxy training mode when all requests passing through the adapter register in the database of the adapter, with automatically generated request corresponding to it and attempt to select its variable parts is used. After that through separately created user interface the technologist is offered to make validation of requests and to mark them as correct.

At online broadcasting adaptation of a request under PosgreSQL and attempt of its accomplishment happens "on the fly". The result of a request and its accomplishment registers in history for the further analysis which is carried out by the technologist. Use of Java code is not provided in this branch of options of execution of an initial request.

DB Migrator

The DB Migrator component is responsible for transfer of the data scheme, data and the logic which was earlier consisting in packets of PL/SQL of programs. Adaptation is performed once. Its result is the DB under PostgreSQL DBMS, similar on structure and filling to the initial scheme DB under control of Oracle DBMS and also, perhaps, set automatically created and compiled by Java classes.

During the work of DB Migrator it is performed:

• migration of the scheme DB;
• migration of the existing data;
• the automated broadcast of texts of requests;
• the automated broadcast of objects of a DB, such, as:
  • - packets of PL SQL;
  • - functions;
  • - stored procedures;
  • - triggers;
  • - object types;
  • - complex types;
  • - representations;
  • - the connected tables in external DB;
  • - links to external databases;
  • - tables;
  • - indexes;
  • - the sequences;
  • - restrictions;
  • - keys;
  • - security objects.

Also broadcast of constructions, specific to Oracle DBMS, such as is performed:

• hierarchical requests of Connect By;
• Merge;
• Oracle notation for external JOIN (+);
• cursors;
• subprocedures.

At the same time the PL/SQL elements of programs for which reconstruction of logic there are not enough means which are available in PostgreSQL DBMS are broadcast in Java code working with PostgreSQL DBMS through JDBC and sending rather simple requests in DBMS. DB Proxy in the course of the work can provide a call of this code for accomplishment of the next request.

Examples of migration

The BOSS Kadrovik (MS SQL – PostgreSQL)

The modern full-function personnel management system is automatically transferred to PostgreSQL DBMS in 1 month.

Large SCADA system for the oil and gas companies (Oracle – PostgreSQL)

Control and management system in real time for gas and oil pipeline networks of any scale and complexity. Support up to 250,000 objects of monitoring, reservation of data in real time, instant switching to standby servers. The cost of adaptation is 990,000 rubles.

Information system for power engineering specialists (Oracle – PostgreSQL)

Operation of application is connected with real-time transmission of a large number of data between several servers. A system is successfully translated in 18 working days.

A large BI system (Oracle – PostgreSQL)

A BI system from the rival company was transferred to PostgreSQL without loss in productivities. For this system the project cost 7,500,000 rubles and it already proved the cost efficiency.

FLEXTERA BI (Oracle – PostgreSQL)

After successful transfer of a system to PostgreSQL performance remained comparable. However when PGStorm was included, performance of a number of functions repeatedly increased in comparison with classical PostgreSQL.

The systems of a special purpose (MS SQL – PostgreSQL)

A number of information systems of the Ministry of Defence was adapted to domestic software using technologies Diasoft Platform.

Electronic document management system (Oracle – PostgreSQL)

EDMS is an integral part of many large enterprises today. Simultaneous transition from Windows to Linux and from Oracle to PostgreSQL not only was cost-efficient, but also allowed to propose to the market really import-independent domestic solution without any clauses and inconveniences.

Development History

2017

Integration in Scala WEDNESDAY

On April 11, 2017 the companies IBS InterLab also Diasoft Platform announced the beginning of integration Diasoft of Database Adapter into the database machine Scala WEDNESDAY / Postgres Pro. The companies believe, it will allow DBMS Postgres Pro to strengthen the positions in the market.

Use of Diasoft Database Adapter technology for the Scala Wednesday / Postgres Pro complex will help customers to transfer to a short time the information systems including working on Oracle Database and Microsoft SQL Server, to highly reliable infrastructure under control of Postgres Pro DBMS with the smallest changes in configurations and the code of the initial systems.

Integration of Diasoft Database Adapter into the Scala Wednesday / Postgres Pro complexes will expand possibilities of use of cluster computers from IBS in a wide number of information systems in which PostgreSQL as DBMS was not applied. Let's note that use of Diasoft Database Adapter allows to make process of migration from one DBMS on another completely automatic.

Diasoft Database Adapter is the program translator allowing information systems to work including with Postgres Pro DBMS, regardless of under what DBMS they were created.

Scala Wednesday / Postgres Pro is a preconfigured failsafe and horizontally scalable solution which allows the large and average organizations to reduce significantly costs for purchase of licenses DBMS and to avoid acquisition of computing nodes and storage systems of a high price category.

The high-speed connection and technology of direct access to RAM of remote nodes applied in Scala Wednesday / Postgres Pro allow to build cluster configurations with a large number of nodes remarks or multimaster's nodes.

283