CN113467785B - SQL translation method and system for mimicry database - Google Patents
SQL translation method and system for mimicry database Download PDFInfo
- Publication number
- CN113467785B CN113467785B CN202110815162.0A CN202110815162A CN113467785B CN 113467785 B CN113467785 B CN 113467785B CN 202110815162 A CN202110815162 A CN 202110815162A CN 113467785 B CN113467785 B CN 113467785B
- Authority
- CN
- China
- Prior art keywords
- sql
- data object
- rewriting
- database
- module
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F8/00—Arrangements for software engineering
- G06F8/40—Transformation of program code
- G06F8/51—Source to source
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F8/00—Arrangements for software engineering
- G06F8/40—Transformation of program code
- G06F8/41—Compilation
- G06F8/42—Syntactic analysis
- G06F8/425—Lexical analysis
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F8/00—Arrangements for software engineering
- G06F8/40—Transformation of program code
- G06F8/41—Compilation
- G06F8/42—Syntactic analysis
- G06F8/427—Parsing
Abstract
The invention provides a SQL translation method and a system of a mimicry database, wherein the method comprises the following steps: performing lexical analysis, syntactic analysis and error detection on an externally input original SQL command, prompting exception if the lexical or syntactic errors exist, and generating an abstract syntax tree AST of the original SQL command if the lexical or syntactic errors do not exist; traversing the AST, and converting the node information of the AST into a user-defined data object; traversing the self-defined data object, performing semantic analysis by accessing metadata information of a metadata database in the mimicry database, and rewriting the self-defined data object node information according to a semantic analysis result; traversing the data object before rewriting or the data object after rewriting, and splicing the data object into an equivalent or legal command or SQL statement of the target database.
Description
Technical Field
The invention relates to the technical field of mimicry databases, in particular to a SQL translation method and a system of a mimicry database.
Background
Based on the mimicry database technology, a plurality of heterogeneous databases are required to be selected as executives, and one of the heterogeneous databases is selected as an appearance mimicry. However, there are differences between database SQL commands and there is no uniform API access interface. For a plurality of databases in the heterogeneous execution body pool, which are inconsistent with the external representation database, translation operation needs to be performed on the input corresponding SQL instructions, and execution results of the SQL instructions are guaranteed to be consistent in each heterogeneous database. Meanwhile, SQL is a large grammar type, and the translation efficiency and the time consumption of SQL are also considered.
Disclosure of Invention
Aiming at the translation problem among various heterogeneous databases in the existing mimicry database, the invention provides an SQL translation method and an SQL translation system for the mimicry database.
In one aspect, the present invention provides a method for SQL translation of a mimicry database, including:
performing lexical analysis, syntactic analysis and error detection on an externally input original SQL command, prompting exception if the lexical or syntactic errors exist, and generating an abstract syntax tree AST of the original SQL command if the lexical or syntactic errors do not exist;
traversing the abstract syntax tree AST, and converting the node information of the abstract syntax tree AST into a user-defined data object;
traversing the self-defined data object, performing semantic analysis by accessing metadata information of a metadata database in the mimicry database, and rewriting the self-defined data object node information according to a semantic analysis result;
and traversing the data objects before rewriting or the data objects after rewriting, and splicing the data objects into equivalent and legal commands or SQL sentences of the target database.
Further, the rewriting the self-defined data object node information according to the semantic analysis result specifically includes:
if the original SQL instruction is a related instruction of the meta information, directly outputting a semantic analysis result; and if the original SQL instruction is a related instruction of non-meta information, rewriting related data object node information to ensure the data consistency between heterogeneous databases.
Furthermore, the data objects before rewriting or the data objects after rewriting are reassembled into equivalent and legal commands or SQL sentences of the target database in a character string splicing mode.
In another aspect, the present invention provides a system for SQL translation of a mimicry database, comprising: the system comprises an SQL analyzing module, an SQL rewriting module and an SQL translation module;
the SQL analysis module is used for carrying out lexical analysis, syntactic analysis and error detection on an original SQL instruction input from the outside, prompting exception if the lexical or syntactic errors exist, and generating an abstract syntax tree AST of the original SQL instruction if the lexical or syntactic errors do not exist; traversing the abstract syntax tree AST, and converting the node information of the abstract syntax tree AST into a user-defined data object;
the SQL rewriting module is used for traversing the data object provided by the SQL analysis module, performing semantic analysis by accessing metadata information of a metadata database in the mimicry database, and rewriting the data object node information generated by the SQL analysis module according to a semantic analysis result;
and the SQL translation module is used for traversing the data objects provided by the SQL analysis module or the data objects rewritten by the SQL rewriting module and splicing the data objects into equivalent and legal commands or SQL sentences of the target database.
Further, the SQL rewriting module comprises a metadata access unit and a rewriting unit;
the metadata access unit is used for accessing metadata information of a metadata database in the mimicry database to perform semantic analysis and sending a semantic analysis result to the rewriting unit;
the rewriting unit is configured to rewrite, according to the received semantic analysis result, the data object node information generated by the SQL parsing module, and specifically includes: if the original SQL instruction is a related instruction of the meta information, directly outputting a semantic analysis result to the SQL translation module; and if the original SQL instruction is a related instruction of non-meta information, rewriting the node information of the related data object to ensure the data consistency between the heterogeneous databases, and outputting the rewritten data object to the SQL translation module.
Further, the SQL translation module is specifically configured to: and reassembling the data objects output by the SQL analysis module or the SQL rewriting module into equivalent and legal commands or SQL sentences of the target database in a character string splicing mode.
The invention has the beneficial effects that:
(1) Through various translation logics, the invention can realize the translation from SQL to various SQL, namely one SQL input and a plurality of equivalent SQL outputs.
(2) The system is provided with a metadata access unit, semantic analysis is carried out by accessing metadata information of a metadata base in a mimicry database, custom data object node information is rewritten according to a semantic analysis result, and consistency of execution results of an SQL instruction among a plurality of heterogeneous executors after translation, namely equivalence of the translation results, can be ensured.
(3) Through the two-step analysis strategy of the lexical method and the grammar, the efficiency and the speed of SQL instruction analysis can be improved.
Drawings
FIG. 1 is a flowchart illustrating a method for SQL translation of a mimicry database according to an embodiment of the present invention;
fig. 2 is a schematic structural diagram of an SQL translation system for a mimicry database according to an embodiment of the present invention.
Detailed Description
In order to make the objects, technical solutions and advantages of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly described below with reference to the accompanying drawings in the embodiments of the present invention, and it is obvious that the described embodiments are some, but not all, embodiments of the present invention. All other embodiments, which can be obtained by a person skilled in the art without making any creative effort based on the embodiments in the present invention, belong to the protection scope of the present invention.
As shown in fig. 1, an embodiment of the present invention provides a method for SQL translation of a mimicry database, including the following steps:
s101: performing lexical analysis, syntactic analysis and error detection on an externally input original SQL command, prompting exception if the lexical or syntactic errors exist, and generating an abstract syntax tree AST of the original SQL command if the lexical or syntactic errors do not exist;
s102: traversing the abstract syntax tree AST, and converting the node information of the abstract syntax tree AST into a user-defined data object;
s103: traversing the self-defined data object, performing semantic analysis by accessing metadata information of a metadata database in the mimicry database, and rewriting the self-defined data object node information according to a semantic analysis result;
specifically, if the original SQL instruction is a related instruction of the meta information (e.g., a SHOW statement), the semantic analysis result is directly output, that is, the related meta data information is returned; if the original SQL instruction is a related instruction of non-meta information, rewriting the related data object node information to ensure the data consistency between heterogeneous databases; for example, a SELECT statement, and according to the meta information, an ORDERBY clause node is added or modified, so that the consistency of the query result is ensured.
S104: traversing the data object before rewriting or the data object after rewriting, and splicing the data object into an equivalent or legal command or SQL statement of the target database.
For example, for the rewritten non-meta-information related data object, the rewritten data object is reassembled into an equivalent, legal command or SQL statement of the target database by means of string splicing, including mapping of keywords, data types, functions, and conversion of statement structures.
The SQL translation method for the mimicry database provided by the embodiment of the invention can realize translation from one SQL to a plurality of SQL by a plurality of translation logics; moreover, semantic analysis is carried out by accessing metadata information of a metadata base in the mimicry database, and customized data object node information is rewritten according to a semantic analysis result, so that the consistency of execution results of an SQL instruction among a plurality of heterogeneous executors after translation is ensured; through the two-step analysis strategy of the lexical method and the grammar, the analysis speed of the SQL instruction is improved.
As shown in fig. 2, an embodiment of the present invention further provides an SQL translation system for a mimicry database, including: the SQL translation module comprises an SQL analysis module, an SQL rewriting module and an SQL translation module;
the SQL analysis module is used for carrying out lexical analysis, syntax analysis and error detection on an original SQL command input from the outside, prompting an exception if a lexical or syntax error exists, and generating an abstract syntax tree AST of the original SQL command if no lexical or syntax error exists; traversing the abstract syntax tree AST, and converting the node information of the abstract syntax tree AST into a user-defined data object;
for example, the open source tool ANTLR may be used to build an SQL parsing module of MySQL, and the abstract syntax tree AST is traversed through a viewer mode provided by ANTLR.
The SQL rewriting module is configured to traverse a data object provided by the SQL parsing module (for example, traverse the data object provided by the SQL parsing module in a Visitor mode), perform semantic analysis by accessing metadata information of a metadata database in the mimicry database, and rewrite data object node information generated by the SQL parsing module according to a semantic analysis result, for example, add a default database name to a table name, supplement an INSERT self-increment column and value, convert a time function in an addition, deletion, and modification statement into a character string, and the like.
As an implementable embodiment, the SQL rewriting module includes a metadata access unit and a rewriting unit; the metadata access unit is used for accessing metadata information of a metadata database in the mimicry database to perform semantic analysis and sending a semantic analysis result to the rewriting unit; the rewriting unit is used for rewriting the data object node information generated by the SQL analysis module according to the received semantic analysis result, and specifically comprises the following steps: if the semantic analysis result indicates that the original SQL instruction is a meta-information related instruction, such as a SHOW statement, the semantic analysis result is directly output to the SQL translation module, namely, related meta-data information is returned. If the semantic analysis result indicates that the original SQL instruction is a related instruction of non-meta information, rewriting related data object node information according to the semantic analysis result, and outputting the rewritten data object to the SQL translation module, wherein the data consistency between heterogeneous databases is high; for example, a SELECT statement, and according to the meta information, an ORDERBY clause node is added or modified, so that the consistency of the query result is ensured.
And the SQL translation module is used for traversing the data objects provided by the SQL analysis module or the data objects rewritten by the SQL rewriting module and splicing the data objects into equivalent and legal commands or SQL sentences of the target database.
As an implementable embodiment, the SQL translation module is specifically configured to: and the data objects output by the SQL analysis module or the SQL rewriting module are reassembled into equivalent and legal commands or SQL sentences of the target database in a character string splicing mode.
For example, for the rewritten non-meta-information related data object, the translation module traverses the rewritten non-meta-information related data object, and reassembles the rewritten non-meta-information related data object into an SQL statement of a target database (PostgreSQL, DM, mySQL) by means of string splicing, which includes mapping of keywords, data types, functions, and conversion of statement structures, etc., so that the rewritten non-meta-information related data object becomes an equivalent and legal SQL that can be executed by a target entity.
Finally, it should be noted that: the above examples are only intended to illustrate the technical solution of the present invention, but not to limit it; although the present invention has been described in detail with reference to the foregoing embodiments, it will be understood by those of ordinary skill in the art that: the technical solutions described in the foregoing embodiments may still be modified, or some technical features may be equivalently replaced; and such modifications or substitutions do not depart from the spirit and scope of the corresponding technical solutions of the embodiments of the present invention.
Claims (4)
1. A SQL translation method of a mimicry database is characterized by comprising the following steps:
performing lexical analysis, syntax analysis and error detection on an original SQL command input externally, prompting an exception if a lexical or syntax error exists, and generating an abstract syntax tree AST of the original SQL command if no lexical or syntax error exists;
traversing the AST, and converting the node information of the AST into a user-defined data object;
traversing the self-defined data object, performing semantic analysis by accessing metadata information of a metadata database in the mimicry database, and rewriting the self-defined data object node information according to a semantic analysis result; the rewriting of the self-defined data object node information according to the semantic analysis result specifically includes: if the original SQL instruction is a related instruction of the meta information, directly outputting a semantic analysis result; if the original SQL instruction is a related instruction of non-meta information, rewriting related data object node information to ensure data consistency between heterogeneous databases;
traversing the data object before rewriting or the data object after rewriting, and splicing the data object into an equivalent or legal command or SQL statement of the target database.
2. The SQL translation method according to claim 1, wherein the rewritten data objects or the rewritten data objects are reassembled into equivalent, legal commands or SQL statements of the target database by string splicing.
3. An SQL translation system for a mimicry database, comprising: the system comprises an SQL analyzing module, an SQL rewriting module and an SQL translation module;
the SQL analysis module is used for carrying out lexical analysis, syntax analysis and error detection on an original SQL command input from the outside, prompting an exception if a lexical or syntax error exists, and generating an abstract syntax tree AST of the original SQL command if no lexical or syntax error exists; traversing the AST, and converting the node information of the AST into a user-defined data object;
the SQL rewriting module is used for traversing the data object provided by the SQL analysis module, performing semantic analysis by accessing metadata information of a metadata database in the mimicry database, and rewriting the data object node information generated by the SQL analysis module according to a semantic analysis result; the SQL rewriting module comprises a metadata access unit and a rewriting unit;
the metadata access unit is used for accessing metadata information of a metadata database in the mimicry database to perform semantic analysis and sending a semantic analysis result to the rewriting unit;
the rewriting unit is configured to rewrite the data object node information generated by the SQL parsing module according to the received semantic analysis result, and specifically includes: if the original SQL instruction is a related instruction of the meta information, directly outputting a semantic analysis result to the SQL translation module; if the original SQL instruction is a related instruction of non-meta information, rewriting the node information of the related data object to ensure the data consistency between heterogeneous databases, and outputting the rewritten data object to the SQL translation module;
and the SQL translation module is used for traversing the data objects provided by the SQL analysis module or the data objects rewritten by the SQL rewriting module and splicing the data objects into equivalent and legal commands or SQL sentences of the target database.
4. The SQL translation system of a mimicry database of claim 3, wherein the SQL translation module is specifically configured to: and the data objects output by the SQL analysis module or the SQL rewriting module are reassembled into equivalent and legal commands or SQL sentences of the target database in a character string splicing mode.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110815162.0A CN113467785B (en) | 2021-07-19 | 2021-07-19 | SQL translation method and system for mimicry database |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110815162.0A CN113467785B (en) | 2021-07-19 | 2021-07-19 | SQL translation method and system for mimicry database |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN113467785A CN113467785A (en) | 2021-10-01 |
| CN113467785B true CN113467785B (en) | 2023-02-28 |
Family
ID=77881058
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202110815162.0A Active CN113467785B (en) | 2021-07-19 | 2021-07-19 | SQL translation method and system for mimicry database |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN113467785B (en) |
Families Citing this family (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN115964028B (en) * | 2021-10-12 | 2023-11-03 | 讯联数据(无锡)有限公司 | Rapid access method and system for third party payment interface |
| CN115269627A (en) * | 2022-05-27 | 2022-11-01 | 上海迈伺通健康科技有限公司 | Method, system, apparatus and storage medium for operating database using Chinese instruction |
Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110674229A (en) * | 2019-09-24 | 2020-01-10 | 山东爱城市网信息技术有限公司 | AST-based relational database SQL table relational analysis and display method |
| CN111143403A (en) * | 2019-12-10 | 2020-05-12 | 跬云(上海)信息科技有限公司 | SQL conversion method and device and storage medium |
| CN111400297A (en) * | 2020-03-19 | 2020-07-10 | 上海德拓信息技术股份有限公司 | Mass data quality verification method based on Hadoop |
| CN112363727A (en) * | 2020-11-10 | 2021-02-12 | 中国平安人寿保险股份有限公司 | JAVA conversion method and device of SQL (structured query language) code, computer equipment and storage medium |
| CN112445867A (en) * | 2019-08-16 | 2021-03-05 | 成都翰通新能源有限公司 | Intelligent analysis method and system for data relationship |
| CN112765209A (en) * | 2021-02-07 | 2021-05-07 | 浪潮云信息技术股份公司 | SQL statement syntax migration method and system between databases |
Family Cites Families (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104899314B (en) * | 2015-06-17 | 2018-10-19 | 北京京东尚科信息技术有限公司 | A kind of parentage analysis method and apparatus of data warehouse |
| US10346401B2 (en) * | 2016-07-07 | 2019-07-09 | Accenture Global Solutions Limited | Query rewriting in a relational data harmonization framework |
| US10579351B2 (en) * | 2017-08-25 | 2020-03-03 | Adp, Llc | Computer speed via metadata-based business rule interpreter |
| CN110555032A (en) * | 2019-09-09 | 2019-12-10 | 北京搜狐新媒体信息技术有限公司 | Data blood relationship analysis method and system based on metadata |
| CN111460493A (en) * | 2020-03-24 | 2020-07-28 | 上海红阵信息科技有限公司 | SQ L database construction method based on mimicry structure |
| CN111444536A (en) * | 2020-03-24 | 2020-07-24 | 上海红阵信息科技有限公司 | Database design method and system suitable for mimicry environment |
-
2021
- 2021-07-19 CN CN202110815162.0A patent/CN113467785B/en active Active
Patent Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112445867A (en) * | 2019-08-16 | 2021-03-05 | 成都翰通新能源有限公司 | Intelligent analysis method and system for data relationship |
| CN110674229A (en) * | 2019-09-24 | 2020-01-10 | 山东爱城市网信息技术有限公司 | AST-based relational database SQL table relational analysis and display method |
| CN111143403A (en) * | 2019-12-10 | 2020-05-12 | 跬云(上海)信息科技有限公司 | SQL conversion method and device and storage medium |
| CN111400297A (en) * | 2020-03-19 | 2020-07-10 | 上海德拓信息技术股份有限公司 | Mass data quality verification method based on Hadoop |
| CN112363727A (en) * | 2020-11-10 | 2021-02-12 | 中国平安人寿保险股份有限公司 | JAVA conversion method and device of SQL (structured query language) code, computer equipment and storage medium |
| CN112765209A (en) * | 2021-02-07 | 2021-05-07 | 浪潮云信息技术股份公司 | SQL statement syntax migration method and system between databases |
Also Published As
| Publication number | Publication date |
|---|---|
| CN113467785A (en) | 2021-10-01 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN108959433B (en) | Method and system for extracting knowledge graph from software project data and asking for questions and answers | |
| CN104657439B (en) | Structured query statement generation system and method for precise retrieval of natural language | |
| CN107515887B (en) | Interactive query method suitable for various big data management systems | |
| CN104657440B (en) | Structured query statement generation system and method | |
| CN103049251B (en) | A kind of data base persistence layer device and database operation method | |
| CN113467785B (en) | SQL translation method and system for mimicry database | |
| US9940392B2 (en) | Performing an object relational model query against a database that includes fields defined at runtime | |
| WO2023221408A1 (en) | Method and apparatus for processing operator for deep learning framework, and device and storage medium | |
| US20120011134A1 (en) | Systems and methods for database query translation | |
| JP2000148461A (en) | Software model and existing source code synchronizing method and device | |
| CN105718593A (en) | Database query optimization method and system | |
| CN109840256A (en) | A kind of inquiry implementation method based on Business Entity | |
| CN112235311B (en) | OVSDB client code automatic generation method, system, device and medium | |
| CN112765209A (en) | SQL statement syntax migration method and system between databases | |
| CN113934786A (en) | Implementation method for constructing unified ETL | |
| CN117033418A (en) | Statement rewriting method, system and device | |
| CN113779062A (en) | SQL statement generation method and device, storage medium and electronic equipment | |
| CN110209699B (en) | Data interface dynamic generation and execution method based on openEHR Composition template | |
| CN108845793B (en) | ORM design method and device | |
| CN113655996B (en) | Enterprise-level system generation method based on demand model | |
| CN112860233B (en) | Method for generating target grammar tree and related equipment | |
| CN114089980A (en) | Programming processing method, device, interpreter and nonvolatile storage medium | |
| US11100286B2 (en) | Methods and systems for implied graph patterns in property chains | |
| CN108008947B (en) | Intelligent prompting method and device for programming statement, server and storage medium | |
| CN111782195B (en) | Query method for splicing SQL based on adding notes on request parameters |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |