Intelligent Translator from Oracle to SQL Server--sql

For this question, the initial assumption is simpler: replace the keyword and function names that are not the same in the SQL statement, such as To_date in Oracle to convert SQL Server. can be executed on SQL Server. This can be true for some simple SQL statements, but for complex applications, SQL statements can be nested in multiple layers, and functions have multiple layers of nesting, and if it's just a simple substitution, there will be countless if else in the code, And it is almost impossible to modify and debug after an error.

By comparing the SQL syntax of Oracle and SQL Server two databases, we consider it necessary to use syntax analysis, parse SQL statements into a syntax tree, and then convert SQL statements into executable statements on SQL Server according to the syntax conversion rules. To implement such a feature, the following patterns are required:

1. Interpreter (interpreter)-Class behavior pattern: Given a language, defines a representation of its grammar, and defines an interpreter that uses the representation to interpret a sentence in a language. By implementing the interpreter pattern, the SQL statement to be executed is interpreted as a SQL syntax tree. For example, the structure of a SELECT statement is as follows

As you can see from this structure diagram, SQL statements can have very complex combinatorial structures, and it is difficult to convert different database platforms without using the syntax tree representation.

2. Composite (combination)-Object structured pattern: The object is grouped into a tree structure to represent a "partial-whole" hierarchy. C o m P o s i t e makes the user consistent with the use of a single object and a composite object.

From the syntax structure of the above SQL statement you can see that a query statement may be a simple select * from ATable, or it may contain other SQL statements in SQL. Instead of creating different classes for separate SQL and composite SQL statements by combining them and recursively referencing their own definitions, the client code that accesses the syntax tree does not need to know whether the SQL statement being accessed has a composite structure, as it takes precedence over inherited rules.

3. VISITOR (visitor)-Object behavior pattern: represents an operation that acts on the elements of an object structure. It allows you to define new operations that act on these elements without changing the classes of each element.

The SQL syntax has already been parsed by the interpreter pattern, and the parse syntax tree is stored in a combinatorial pattern, but we need more than that. Also, the syntax structure of SQL Server is used to regroup the nodes on the syntax tree, resulting in the final output of SQL statements that can be executed on SQL Server. For example, a connection query in Oracle, select a.*,b.* from A,b where a.id=b.id (+), and the corresponding statement in SQL Server should be select a.*,b.* from left to join B on a.id=b . Id.

From this simple example, we can see that there is a big difference in the syntax structure between the two databases for the left or right connection of the table. If a method is written in a TSQL class, this method traverses each node in the syntax tree and is accomplished in accordance with the results required by SQL Server's grammatical structure combination. However, if you need to export from this syntax tree to other databases such as Sybase executable SQL statements, it is also in the TSQL class to add a new traversal algorithm, all the relevant code to recompile.

By using the visitor pattern, the algorithm of combining the syntax tree nodes when traversing nodes is encapsulated in the method of the visitor, such as SQL Server's syntax is a Tsqlservervisitor class, and the syntax tree will call its visit method when traversing each node. When you are done, you can get the SQL statements you need by GetSQL. Then if we need to convert to Sybase, we just need to implement a Tsybasevisitor class and pass it to the syntax tree to get the Sybase SQL statement.

4. Finite state machine--the identification of words and keywords. Before parsing an SQL statement, first identify the syntax elements such as characters, numbers, keywords, and functions. This obviously cannot be judged simply by character positioning, but by using a state machine to identify different rule expressions. Now in C # The regular expression is very useful. However, after the rewriting of these pattern recognition, but also the previous study of the principles of compiling a good review of some of the concepts of understanding more in-depth, only to blame the original study is not fine ah. Oh.

Foreign more mature SQL Translator reference:http://www.microsoft.com/downloads/thankyou.aspx?familyId= E35cee88-c919-463f-b020-81468cd231da&displaylang=en

http://www.ispirer.com/products