CN113467785A - SQL translation method and system for mimicry database - Google Patents
SQL translation method and system for mimicry database Download PDFInfo
- Publication number
- CN113467785A CN113467785A CN202110815162.0A CN202110815162A CN113467785A CN 113467785 A CN113467785 A CN 113467785A CN 202110815162 A CN202110815162 A CN 202110815162A CN 113467785 A CN113467785 A CN 113467785A
- Authority
- CN
- China
- Prior art keywords
- sql
- database
- rewriting
- module
- data object
- 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.)
- Granted
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
Landscapes
- Engineering & Computer Science (AREA)
- General Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Software Systems (AREA)
- Physics & Mathematics (AREA)
- General Physics & Mathematics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
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 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.
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 databases is selected as an external mimicry presentation. 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 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 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; and 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.
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 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, 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 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; 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 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.
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 database 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 schematic flowchart of 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 of 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 derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within 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: 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.
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 the translation from SQL to various SQL by various 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, 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;
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 object, and reassembles the object into an SQL statement of a target database (PostgreSQL, DM, MySQL) by means of string splicing, including mapping of keywords, data types, functions, and conversion of statement structures, so that the SQL statement 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 (6)
1. A SQL translation method of a mimicry database is characterized by comprising 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 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.
2. The SQL translation method of the mimicry database according to claim 1, wherein 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; and 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.
3. 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.
4. An SQL translation system for a mimicry database, comprising: 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, 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.
5. The SQL translation system of a mimicry database according to claim 4, wherein the SQL rewrite module comprises a metadata access unit and a rewrite 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; 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.
6. The SQL translation system of a mimicry database of claim 4, 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 true CN113467785A (en) | 2021-10-01 |
| CN113467785B 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) |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN115269627A (en) * | 2022-05-27 | 2022-11-01 | 上海迈伺通健康科技有限公司 | Method, system, apparatus and storage medium for operating database using Chinese instruction |
| CN115964028A (en) * | 2021-10-12 | 2023-04-14 | 讯联数据(无锡)有限公司 | Quick access method and system for third-party payment interface |
Citations (12)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104899314A (en) * | 2015-06-17 | 2015-09-09 | 北京京东尚科信息技术有限公司 | Pedigree analysis method and device of data warehouse |
| AU2016222437B1 (en) * | 2016-07-07 | 2017-03-30 | Accenture Global Solutions Limited | Query rewriting in a relational data harmonization framework |
| US20190065164A1 (en) * | 2017-08-25 | 2019-02-28 | 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 |
| 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 |
| CN111444536A (en) * | 2020-03-24 | 2020-07-24 | 上海红阵信息科技有限公司 | Database design method and system suitable for mimicry environment |
| CN111460493A (en) * | 2020-03-24 | 2020-07-28 | 上海红阵信息科技有限公司 | SQ L database construction method based on mimicry structure |
| 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 |
-
2021
- 2021-07-19 CN CN202110815162.0A patent/CN113467785B/en active Active
Patent Citations (12)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104899314A (en) * | 2015-06-17 | 2015-09-09 | 北京京东尚科信息技术有限公司 | Pedigree analysis method and device of data warehouse |
| AU2016222437B1 (en) * | 2016-07-07 | 2017-03-30 | Accenture Global Solutions Limited | Query rewriting in a relational data harmonization framework |
| US20190065164A1 (en) * | 2017-08-25 | 2019-02-28 | Adp, Llc | Computer speed via metadata-based business rule interpreter |
| CN112445867A (en) * | 2019-08-16 | 2021-03-05 | 成都翰通新能源有限公司 | Intelligent analysis method and system for data relationship |
| CN110555032A (en) * | 2019-09-09 | 2019-12-10 | 北京搜狐新媒体信息技术有限公司 | Data blood relationship analysis method and system based on metadata |
| 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 |
| CN111444536A (en) * | 2020-03-24 | 2020-07-24 | 上海红阵信息科技有限公司 | Database design method and system suitable for mimicry environment |
| CN111460493A (en) * | 2020-03-24 | 2020-07-28 | 上海红阵信息科技有限公司 | SQ L database construction method based on mimicry structure |
| 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 |
Non-Patent Citations (1)
| Title |
|---|
| 沈雷等: "C/C++软件测试工具的元数据结构设计与实现", 《计算机工程》 * |
Cited By (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN115964028A (en) * | 2021-10-12 | 2023-04-14 | 讯联数据(无锡)有限公司 | Quick access method and system for third-party payment interface |
| 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 |
Also Published As
| Publication number | Publication date |
|---|---|
| CN113467785B (en) | 2023-02-28 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US10216826B2 (en) | Database query system | |
| CN107515887B (en) | Interactive query method suitable for various big data management systems | |
| CN113467785B (en) | SQL translation method and system for mimicry database | |
| CN103049251B (en) | A kind of data base persistence layer device and database operation method | |
| US20120011134A1 (en) | Systems and methods for database query translation | |
| WO2023221408A1 (en) | Method and apparatus for processing operator for deep learning framework, and device and storage medium | |
| US20210209098A1 (en) | Converting database language statements between dialects | |
| CN105718593A (en) | Database query optimization method and system | |
| CN110554875B (en) | Code conversion method and device, electronic equipment and storage medium | |
| CN109840256A (en) | A kind of inquiry implementation method based on Business Entity | |
| CN104765731A (en) | Database query optimization method and equipment | |
| US20120158742A1 (en) | Managing documents using weighted prevalence data for statements | |
| US9104724B2 (en) | Dynamic bridging of application and data servers | |
| CN113204571B (en) | SQL execution method and device related to write-in operation and storage medium | |
| CN112765209A (en) | SQL statement syntax migration method and system between databases | |
| CN113779062A (en) | SQL statement generation method and device, storage medium and electronic equipment | |
| CN105677805A (en) | Data storing and reading method and device using protobuf | |
| CN111782195B (en) | Query method for splicing SQL based on adding notes on request parameters | |
| CN114356964A (en) | Data blood margin construction method and device, storage medium and electronic equipment | |
| CN117033418A (en) | Statement rewriting method, system and device | |
| CN110209699B (en) | Data interface dynamic generation and execution method based on openEHR Composition template | |
| CN108845793B (en) | ORM design method and device | |
| CN113608748B (en) | Data processing method, device and equipment for converting C language into Java language | |
| CN112860233B (en) | Method for generating target grammar tree and related equipment | |
| CN108008947B (en) | Intelligent prompting method and device for programming statement, server and storage medium |
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 |