Introduction of a software multi-database support implementation method

Data | Database when we complete software design and development in a project way, the specific environment that is usually developed has been determined and the database used has been identified, so there is no need to support multiple databases. However, multi-database support will be a very important requirement if a software appears in the way of product and needs the same product to adapt to the IT environment of different enterprises; How to realize multi-database support will directly affect the product quality and the implementation and maintenance cost, related to the scope of product promotion and the final success or failure This article will introduce the current common multi-database implementation method, and take the multi database support implementation technology which is used in the software developed by the author company.

Software products that implement multiple database support typically take the following approaches:

1. Adopt a layered structure to achieve a set of data access layers for each database. The scheme has the advantages of simple implementation and clear structure, but it also has many problems. A function point modification in the software may involve several sets of data access layer modifications, at the same time, the test will also bring a doubling of the workload, because the same data access functions need to encode different databases, so will certainly bring more coding, introduce more bugs, affect the quality of software.

2. The system uses the SQL statement placed outside the system, the SQL statement is divided into sets, each database maintains a set of SQL statements. This implementation can be achieved in part of the case without modifying the code to adjust the purpose of the system, set in a simple situation can be achieved without modifying the code to add a new database support. However, the implementation of this method has difficulties in debugging and maintenance error. At the same time, if the system is more complicated, it is difficult to embody its advantages and practicability is not high.

3. Use standard SQL statements to achieve multiple database support. This method is to talk about a lot of methods, but the real use of this method to achieve multiple database support software systems are not many manufacturers. The reason is that the database vendors ' support for standard SQL is incomplete, and it's not realistic for system developers to be able to accurately differentiate between those that are standard SQL and those that are specific database SQL, and when a particular database SQL is used and the tests are not very good, There will be a lot of time bombs buried.



The author of the company in the implementation of EHR software multi-database support functions before the following requirements:

1. It is convenient to achieve the support of the new database, it is best to achieve without modifying the system implemented by this method code will be able to increase the support of the new database.

2. System modification, maintenance is easy, coding workload is best and the type of database to support the number of independent.

For some of the above, we have proposed the following multiple database support implementation scenario:



The solution consists of three parts: the SQL Entity Description object, the SQL statement Builder, and the SQL entity executor, which form a new software layer (data I/O controler) between the data access layer and the database, which is called by the access layer.

Compared with the usual multi-tier architecture software, this scheme differs in that the data access layer accesses the database method rather than executing the SQL statement directly, but describes the semantics of the corresponding SQL statement in the manner of the SQL entity object, and then invokes the SQL entity executor to describe the object processing for the SQL entity. Generates the SQL statement for the corresponding data type and executes it. In SQL Executor, in order to support multiple databases, it is necessary to implement a adapter that exists as a standalone DLL for no database, which receives incoming SQL entity objects and generates SQL statements that conform to the corresponding database syntax according to the semantics described by the SQL Entity object (adapter). Adapter needs to implement a series of predefined interfaces to facilitate the invocation of the SQL entity executor, and the SQL entity executor is associated with the specific adapter during run time, without having to know beforehand which adapter handles the SQL entity object, and when it is necessary to increase support for the new database, You do not need to modify any of the original software, only to implement a new adapter;

This scheme isolates the complexity of supporting multiple databases from the specific software, and changes the complexity from the usual distribution of software to the point of centralized processing, which makes maintenance easier, the coding quantity is reduced greatly, and the system reliability is higher;