Solutions to eliminate differences

Because of the differences in the syntax of different database systems, it is necessary to eliminate these differences by some means if the system you want to develop can run in a multi-database system. The main ways to eliminate differences are:

Write different SQL statements for each database, use syntax intersection, use abstract SQL, use ORM Tools, and use SQL translator.

These scenarios are analyzed below.

• Write different SQL statements for each type of database

In this scenario, for SQL statements that have syntax differences, you write different SQL for each database, and then execute different SQL statements at run time based on the current database type, such as:

if (currentdatabase= "MYSQL") {ExecuteQuery ("SELECT *From T_person LIMIT0,10") {ExecuteQuery (" Select TOP 10 * from t_person") {ExecuteQuery ("select *" Span class= "Hljs-keyword" >from t_person where ROWNUM <= 10< Span class= "hljs-string");} else if (currentdatabase= "Db2") {ExecuteQuery ("select * from t_person fetch FIRST 10 rows only  

This approach is a good way to solve multiple database problems, but requires developers to be very proficient in the syntax differences of each database, and this increases the workload of development.

• Use syntax intersection

To avoid multiple database problems, avoid using the different parts of the database system syntax during development, using only SQL statements that are supported by all database systems. This scheme can be a good solution to the problem of multiple databases, but because of the inability to use some advanced syntax, so some features can not be implemented or must be implemented in the host language through code, which not only restricts the implementation of the system functions and reduces the efficiency of the operation, The most important question is: since I've been paying a big price for Oracle data, why not use some of the features that Oracle provides?

• Working with SQL entity objects

In this scenario, the developer accesses the database method not directly executes the SQL statement, but rather describes the semantics of the corresponding SQL statement as a SQL entity object, and then calls the SQL entity executor to describe the SQL entity to handle the object, generate the corresponding data type of the SQL statement, and execute. In the SQL executor, an adaptation translator is implemented for each database, which receives the incoming SQL entity object and can generate SQL statements that conform to the corresponding database syntax based on the semantics described by the SQL entity object. SQL Entity executor is not associated with a specific adaptation translator during runtime and does not have to know beforehand which adapter handles SQL entity objects, and when it is necessary to increase support for the new database, you do not have to modify any of the original software, just implement a new adaptation translator.

With this approach, developers cannot write SQL statements directly, but can only write abstract grammatical constructs, such as the following code to achieve the function of getting the first 10 rows of data in table T_person:

new Query();query.SetColumn("*");query.SetTableName("T_Person");query.SetLimit(0,10);ExecuteQuery(query);

The system framework translates query into SQL statements supported by the corresponding database system, such as:

 mysql:SELECT * from T_person LIMIT 0, 10mssqlserver:select TOP 10 * from T_personoracle:select * from t_person WHERE ROWNUM <= 10db2:select * FROM T_Person fetch first 10  ROWS only           

This approach maximizes the use of the advanced features of the target database, and the developer does not even need to have any knowledge of the SQL syntax, the disadvantage is that the amount of code written increases, and if you want to implement complex functions such as subqueries, joins, and write very lengthy and difficult-to-understand code, The ability to do this with a normal SQL statement of 35 lines is likely to require dozens of lines of code in this way.

• Using the ORM Tool

Hibernate in Java, EJB, and LINQ in. NET, NHibernate, etc. are excellent ORM tools that provide an object-oriented way to use the database, so that developers can manipulate entity objects to avoid writing SQL statements directly , for example, the following code is used to add a record to the people table:

new Person();person.Name="Tom";person.Age=22;ormTool.Save(person);

The ORM tool translates it into the following SQL statement:

INSERT INTO T_Person(FName,FAge)VALUES("Tom",22);

The following code is used to get the top ten people in the People table:

new Query();query.SetLimit(0,10);query.SetEntityName("Person");ormTool.ExecuteQuery(query);

The system framework translates query into SQL statements supported by the corresponding database system, such as:

MYSQL：SELECT * FROM T_Person LIMIT 0, 10MSSQLServer：SELECT TOP 10 * FROM T_PersonOracle：SELECT * FROM T_Person WHERE ROWNUM <= 10DB2：SELECT * FROM T_Person FETCH FIRST 10 ROWS ONLY

The ORM tool translates the operation of an entity object into an SQL statement, which is essentially a solution that uses SQL entity objects.

In addition to supporting the use of ORM tools in manipulating Entity objects, many ORM tools provide syntax tools like SQL statements, such as Ejb-sql in EJBs and Hsql in Hibernate, which we can collectively refer to as Ormsql, Using Ormsql when implementing complex functions avoids writing lengthy object manipulation code, and the ORM tool translates the ORMSQL statement into the syntax supported by the target database platform. Ormsql simplifies development, but the current syntax for ORMSQL support is focused on data queries, with limited support for delete, INSERT, UPDATE, and DDL statements, and a significant lack of support for commonly used functions.

• Using SQL Translator

A SQL translator is a translator that accepts SQL written by a developer and then translates SQL into SQL statements supported by the target database system. For example, the developer writes the following SQL statement to get the top ten people in the People table:

SELECT TOP 10 * FROM T_Person

The SQL translator translates it into SQL statements supported by the target database system:

 mysql:SELECT * from T_person LIMIT 0, 10mssqlserver:select TOP 10 * from T_personoracle:select * from t_person WHERE ROWNUM <= 10db2:select * FROM T_Person fetch first 10  ROWS only           

SQL Translator supports full SELECT, INSERT, UPDATE, delete, and DDL statement syntax, and supports arbitrary complexity of SQL statements, and developers are only familiar with a SQL syntax. There is no need to understand the differences in the implementation of SQL statements in different database systems.

Currently, there are three SQL translator products, namely Swissql, LDBC and Cownewsql,swissql are open products of a non-open source commercial company, and LDBC and Cownewsql are open source projects.

Solutions to eliminate differences