CN116010445B

CN116010445B - Processing method and device of database SQL script

Info

Publication number: CN116010445B
Application number: CN202310308527.XA
Authority: CN
Inventors: 马骋原; 秦元; 张超
Original assignee: Hundsun Technologies Inc
Current assignee: Hundsun Technologies Inc
Priority date: 2023-03-28
Filing date: 2023-03-28
Publication date: 2023-07-25
Anticipated expiration: 2043-03-28
Also published as: CN116010445A

Abstract

The present disclosure provides a method and an apparatus for processing a database SQL script, where the method for processing the database SQL script includes: acquiring a basic SQL script file, and analyzing the basic SQL script file to acquire at least one original SQL label fragment; converting the set label in the original SQL label fragment into an annotation to obtain a corresponding SQL sentence with the label annotation; translating the SQL statement with the label annotation into a target SQL statement with the label annotation corresponding to the target database, and restoring the set label annotated in the target SQL statement to obtain a target SQL label fragment corresponding to the target database; and splicing the target SQL label fragments corresponding to the at least one original SQL label fragment to obtain a target SQL script file corresponding to the target database. Therefore, on the basis of a unified basic SQL script file, SQL script files of different types of databases are automatically converted and generated, so that the development efficiency of the SQL script files of the databases is greatly improved, the maintenance cost is reduced, and the burden of developers is lightened.

Description

Processing method and device of database SQL script

Technical Field

The present disclosure relates to the field of database technologies, and in particular, to a method for processing a database SQL script. The present specification also relates to a processing apparatus of a database SQL script, a computing device, and a computer-readable storage medium.

Background

With the rapid development of computer and internet technologies, the storage and management demands for data are increasing, the data is an important asset for each enterprise, and the database is a place for storing, inquiring and calculating the data. The database is a very key technology, has the characteristics of data structuring, high data sharing, low data redundancy, easy data expansion, high data independence and the like, and is currently applied to a plurality of fields such as finance, medical treatment, education, scientific research, electronic commerce and the like. Databases have been receiving attention in many areas and are in the process of developing at a high rate.

SQL (Structured Query Language ) is a database query and programming language for accessing data and querying, updating and managing relational database systems. The existing databases in the Internet are various, the existing database products support special SQL sentences besides standard SQL sentences, the SQL sentences are not universal, namely, the SQL sentences used correspondingly by the databases are different, the difference brings inconvenience to application databases, and one set of SQL sentences works well in one database and can not work normally in other databases.

In the prior art, a database developer often develops multiple sets of SQL statement dialects for different types of databases, which set of SQL statement dialects is designated to be used in a mode of designating the type of the database in configuration, the development workload is large, each database needs to be adapted, one set of SQL statement dialects needs to be newly added if one type of database is newly added in the later period, and when the metadata of the database changes, each set of SQL statement dialects also needs to be adjusted, so that the maintenance cost is high, the SQL development efficiency of the database is reduced, and the burden of the developer is increased.

Disclosure of Invention

In view of this, the embodiments of the present disclosure provide a method for processing a database SQL script. The present disclosure also relates to a processing device, a computing device, and a computer-readable storage medium for processing a database SQL script, so as to solve the technical defects in the prior art.

According to a first aspect of embodiments of the present disclosure, there is provided a method for processing a database SQL script, including:

acquiring a basic SQL script file, and analyzing the basic SQL script file to acquire at least one original SQL label fragment, wherein the original SQL label fragment comprises a set label;

Converting the set label in the original SQL label fragment into an annotation to obtain a corresponding SQL sentence with the label annotation;

translating the SQL statement with the label annotation into a target SQL statement with the label annotation corresponding to the target database, and restoring the set label annotated in the target SQL statement to obtain a target SQL label fragment corresponding to the target database;

and splicing the target SQL label fragments corresponding to the at least one original SQL label fragment to obtain a target SQL script file corresponding to the target database.

Optionally, converting the setting tag in the original SQL tag fragment into the comment to obtain a corresponding SQL statement with the tag comment, including:

determining a first setting tag included in a first SQL tag fragment, wherein the first SQL tag fragment is any one of at least one original SQL tag fragment;

acquiring a first annotation rule corresponding to a first setting tag;

and according to the first annotation rule, converting the first setting tags in the first SQL tag fragment into annotations to obtain corresponding first SQL sentences with the tag annotations.

Optionally, before converting the setting tag in the original SQL tag fragment into the comment to obtain the corresponding SQL statement with the tag comment, the method further includes:

And configuring corresponding annotation rules according to the grammar logic of each set label.

Optionally, configuring the corresponding annotation rule according to the grammar logic of each set tag includes:

setting the field content corresponding to the set label as an annotation by using the annotation identifier under the condition that the set label is of a first label type;

setting field content corresponding to the set tag as an annotation by using an annotation identifier under the condition that the set tag is of a second tag type, complementing missing content at a set position, and adding an explanation annotation after the missing content, wherein the missing content is SQL sentence content nested in the set tag, and the explanation annotation is used for indicating deletion of the missing content when the set tag is restored;

and under the condition that the set label is of a third label type, determining branch contents of all branches in field contents corresponding to the set label, taking each branch content as an SQL label sub-segment, determining target SQL sub-sentences with labels and comments corresponding to all SQL label sub-segments, and merging the target SQL sub-sentences corresponding to all the branch contents to obtain the target SQL sentences with labels and comments corresponding to the original SQL label segments.

Optionally, determining the target SQL sub-statement with the label annotation corresponding to each SQL label sub-fragment includes:

annotating a set tag in a first SQL tag sub-segment to obtain a corresponding first SQL sub-statement with a tag annotation, wherein the first SQL tag sub-segment is any one of the SQL tag sub-segments;

and translating the first SQL sub-statement with the label annotation into a first target sub-statement with the label annotation corresponding to the target database.

Optionally, translating the tagged annotated SQL statement into a tagged annotated target SQL statement corresponding to the target database includes:

and inputting the SQL statement with the label annotation into a translation model, translating the SQL statement with the label annotation through the translation model, and reserving label annotation content in the SQL statement with the label annotation to obtain a target SQL statement with the label annotation corresponding to the target database.

Optionally, obtaining the basic SQL script file includes:

determining a basic database from the setting database;

and developing a basic SQL script file corresponding to the basic database.

According to a second aspect of embodiments of the present specification, there is provided a processing apparatus for a database SQL script, including:

The acquisition module is configured to acquire a basic SQL script file, analyze the basic SQL script file and acquire at least one original SQL label fragment, wherein the original SQL label fragment comprises a set label;

the annotation module is configured to convert the set tags in the original SQL tag fragments into annotations to obtain corresponding SQL sentences with the tag annotations;

the restoration module is configured to translate the SQL statement with the label annotation into the target SQL statement with the label annotation corresponding to the target database, restore the setting label annotated in the target SQL statement and obtain a target SQL label fragment corresponding to the target database;

and the splicing module is configured to splice the target SQL label fragments corresponding to the at least one original SQL label fragment to obtain a target SQL script file corresponding to the target database.

According to a third aspect of embodiments of the present specification, there is provided a computing device comprising:

a memory and a processor;

the memory is for storing computer executable instructions and the processor is for executing the computer executable instructions to implement the steps of the method of processing a database SQL script of any of the above.

According to a fourth aspect of embodiments of the present description, there is provided a computer-readable storage medium storing computer-executable instructions which, when executed by a processor, implement steps of a method of processing a database SQL script.

According to the processing method of the database SQL script provided by the embodiment of the specification, a basic SQL script file is obtained, the basic SQL script file is analyzed, and at least one original SQL label fragment is obtained, wherein the original SQL label fragment comprises a set label; converting the set label in the original SQL label fragment into an annotation to obtain a corresponding SQL sentence with the label annotation; translating the SQL statement with the label annotation into a target SQL statement with the label annotation corresponding to the target database, and restoring the set label annotated in the target SQL statement to obtain a target SQL label fragment corresponding to the target database; and splicing the target SQL label fragments corresponding to the at least one original SQL label fragment to obtain a target SQL script file corresponding to the target database.

Under the condition, a unified basic SQL script file can be generated, then the basic SQL script file is automatically analyzed to obtain original SQL label fragments included in the basic SQL script file, setting labels in each original SQL label fragment are converted into comments, then the obtained SQL sentences with the labels and the comments are translated into target SQL sentences with the labels and the comments corresponding to the target database, the setting labels annotated in the target SQL sentences are restored to obtain target SQL label fragments corresponding to the target database, and therefore all the target SQL label fragments are spliced to obtain the target SQL script file corresponding to the target database. Therefore, the content of the label which does not need to be changed in the translation process can be hidden through the annotation mode, only the content of the SQL sentence which needs to be changed is translated, then the content of the label in the translation fragment is restored, the translated SQL sentence is obtained, and the annotated label is restored, so that the content and format of the label are unchanged in the translation process of the SQL sentence content, the SQL script files of different types of databases are automatically converted and generated on the basis of a unified basic SQL script file, the accuracy of the generated SQL script file is ensured, the SQL script file can be successfully operated in the corresponding database, the development efficiency of the SQL script file of the database is greatly improved, the maintenance cost is reduced, and the burden of developers is lightened.

Drawings

FIG. 1 is a flowchart of a method for processing a database SQL script according to an embodiment of the present disclosure;

FIG. 2 is a timing diagram of a method for processing a database SQL script according to an embodiment of the present disclosure;

FIG. 3 is a schematic diagram of a processing device for SQL script of database according to an embodiment of the present disclosure;

FIG. 4 is a block diagram of a computing device according to one embodiment of the present disclosure.

Detailed Description

In the following description, numerous specific details are set forth in order to provide a thorough understanding of the present description. This description may be embodied in many other forms than described herein and similarly generalized by those skilled in the art to whom this disclosure pertains without departing from the spirit of the disclosure and, therefore, this disclosure is not limited by the specific implementations disclosed below.

The terminology used in the one or more embodiments of the specification is for the purpose of describing particular embodiments only and is not intended to be limiting of the one or more embodiments of the specification. As used in this specification, the singular forms "a," "an," and "the" are intended to include the plural forms as well, unless the context clearly indicates otherwise. It should also be understood that the term "and/or" as used in one or more embodiments of the present specification refers to and encompasses any or all possible combinations of one or more of the associated listed items.

It should be understood that, although the terms first, second, etc. may be used in one or more embodiments of this specification to describe various information, these information should not be limited by these terms. These terms are only used to distinguish one type of information from another. For example, a first may also be referred to as a second, and similarly, a second may also be referred to as a first, without departing from the scope of one or more embodiments of the present description. The word "if" as used herein may be interpreted as "at … …" or "at … …" or "responsive to a determination", depending on the context.

First, terms related to one or more embodiments of the present specification will be explained.

SQL (Structured Query Language ): is a special purpose programming language, a database query and programming language, for accessing data and querying, updating and managing relational database systems; and is also an extension of the database script file. The structured query language is a high-level, non-procedural programming language that allows users to work on high-level data structures. The method does not require the user to specify a data storage method or the user to know a specific data storage mode, so that different database systems with completely different substructures can use the same structured query language as an interface for data input and management. The structured query language statement can be nested, which gives it great flexibility and powerful functionality.

XML (Extensible Markup Language ): a subset of the standard generic markup language is a markup language used to mark electronic files to be structured. XML can be used for marking data and defining data types, can allow users to define own marking languages, and is a data bearing mode which is friendly to users and machines. It provides a unified approach to describe and exchange structured data independent of applications or vendors, well suited for web transport, a cross-platform, content-dependent technology in the internet environment, and an effective tool for processing distributed structural information today.

ORM (ObjectRelationalMapping) frame: metadata is used to describe the mapping details of the object-relation, the metadata is generally in XML format and is stored in a special object-mapped file, so long as the mapping relation between the persistent class and the table is provided, the ORM framework can refer to the information of the mapped file at runtime, and the object is persisted into a database, for example, the ORM framework can be Mybatis, hibernate, iBATIS, eclipseLink, JPA. The ORM framework can make a mapping between the relational database and the object, so that when a specific operation database is performed, the complex SQL statement is not needed to be crossed any more, and the object can be operated just like a normal operation object.

Persistence (Persistence): data (e.g., objects in memory) is saved to a permanently storable storage device (e.g., disk). The primary application of persistence is to store the data in memory in relational databases, but may also be stored in disk files, XML data files, and the like. The higher level of persistence is the persistence layer.

Persistent layer (Persistence Layer): a logic level specific to a particular system implementing the field of data persistence applications is dedicated to associating data users with data entities.

Mybatis: is an excellent persistence layer framework supporting normal SQL queries, stored procedures, and advanced mappings. It eliminates almost all manual setting of JDBC code and parameters and retrieval of result sets, maps the interfaces and Java POJOs (Plain Ordinary Java Objects, common Java objects) to records in the database using simple XML or annotations for configuration and original mapping.

Oracle Database (Oracle Database): is a database management system. The Oracle database has the characteristics of complete data management function, complete relation product, distributed processing function, data warehouse operation realized by ORACLE, and the like.

MySQL: the method is a relational database management system, SQL sentences used by MySQL are the most common standardized languages for accessing databases, and MySQL is generally selected as a website database for development of small and medium-sized websites due to the characteristics of small size, high speed, open source codes and the like.

It should be noted that, the databases in the existing internet are numerous, the existing database products support the respective unique SQL statements besides the standard SQL statements, and these SQL statements are not universal, that is, the different types of databases are different from each other, and this difference brings inconvenience to the application database, so that one set of SQL statements works well in some databases and is not necessarily able to operate normally in other databases.

Currently, many persistent layer ORM frameworks are used in making application persistent layer development docking databases, such as: mybatis, hibernate, JPA, mybatis is widely used in various business systems as an excellent semi-automatic ORM framework. There are generally three ways to dock different types of databases if needed in Mybatis: multiple sets of XML files are developed for different types of databases, using the databaseId attribute in Myabatis's XML, using JDBC agents, or database proxy services.

The first is to write a set of Mybatis XML files of SQL for each type of database by manual development, and then specify which set of XML files to use by specifying the type of database in the configuration. The second is to directly write multiple SQL sentences into the same Mybat XML file by means of distinguishing the databaseId types (the IDs of the labels are kept consistent) through the function of dynamically judging the database types provided by the Mybatis. The third is to extract the SQL to be executed by proxy at the JDBC layer to convert, or to intercept and convert the SQL to be executed by proxy service of the external database, both of which are realized at the bottom layer, bypassing Mybatis framework, which is a mode of converting at the running time.

However, in the first way, multiple sets of Mybatis XML files are developed for different types of databases, the development workload is large, adaptation is required for each database dialect, if one type of database is newly added in the later stage, a new set of XML files is required to be newly added, and when the metadata of the database changes, adjustment is required for each set of XML files, so that the maintenance cost is high. The second method uses Mybatis databaseId only the ability of Mybatis to dynamically identify database types, essentially, a plurality of SQL sets are required to be developed for different databases, only a part of the indiscriminate script fragments of SQL sentences can share and write only one set, but a large number of SQL sentences need to be redeveloped. The third method is to bypass Mybatis, intercept and convert SQL (through unified SQL statement conversion tool) in running, while most of use scenes can be adapted, but converted SQL can not be directly adjusted in development period, and there is some performance loss in conversion process, and there is a certain pressure to convert high frequency long SQL in running.

Therefore, the embodiment of the specification provides a processing method of a database SQL script, which comprises the steps of developing an XML file of Mybatis based on unified SQL sentences, automatically analyzing the XML file of Mybatis, extracting internal SQL sentences, converting grammar, semantics and functions, regenerating the Mybatis XML file corresponding to a target database, and keeping the format of an original XML tag unchanged. According to the method, mybatis XML of a set of SQL sentences can be developed, and the Mybatis XML files of a plurality of different databases SQL grammar dialects can be automatically converted in the development stage. Therefore, the SQL sentences in the XML file are dynamically converted based on Mybatis development, and all formats and contents of the original labels can be reserved, so that the capability of converting one set of XML into any target database SQL script file is realized, and the efficiency of Mybatis SQL sentence development is greatly improved.

In the present specification, a method for processing a database SQL script is provided, and the present specification relates to a processing apparatus for a database SQL script, a computing device, and a computer-readable storage medium, which are described in detail in the following embodiments one by one.

FIG. 1 is a flowchart of a method for processing a database SQL script according to an embodiment of the present disclosure, specifically including the following steps 102-108:

step 102: and obtaining a basic SQL script file, and analyzing the basic SQL label to obtain at least one original SQL label fragment, wherein the original SQL label fragment comprises a set label.

In practical application, the basic SQL script file refers to a Mybatis XML file of an SQL tag fragment corresponding to a certain basic database, and the basic SQL script file can be suitable for a plurality of databases, namely, SQL embedded in the basic SQL script file is a unified SQL statement. In addition, the original SQL tag fragment refers to a content segment including a setting tag, and one SQL tag fragment may include one setting tag or may include a plurality of setting tags.

It should be noted that, although contents such as grammar, semantics, meaning, function, etc. may be different between SQL statements of different databases, translation is required, but for tag contents in the SQL tag fragment, different databases are the same, and translation is not required, so that a tag which does not need to be changed in the translation process of the SQL statement may be set as a set tag. That is, the setting tag refers to a tag that is preset, and the content and format need to be kept unchanged during the translation of the SQL statement, such as an if tag, a foreach tag, a choose white tag, and the like in the SQL tag.

In this embodiment of the present disclosure, a set of basic SQL script files needs to be developed based on a set of unified basic SQL statement dialect grammar, where the basic SQL script files are in XML file format, then an SQL script processing tool may automatically parse the basic SQL script files (i.e., XML files), identify field contents corresponding to a set tag, divide the set of basic SQL script files into a plurality of original SQL tag fragments, each of the original SQL tag fragments includes a set tag, and subsequently annotate the set tag in the original SQL tag fragment based on a rule corresponding to the set tag, so that tag contents of the set tag that need not be changed are hidden in the SQL statement translation process.

In an optional implementation manner of this embodiment, the obtaining a basic SQL script file may be implemented as follows:

determining a basic database from the setting database;

and developing a basic SQL script file corresponding to the basic database.

In practical application, the setting database is a database for generating unified SQL sentences, a developer can select one from the setting databases as a basic database based on requirements, and then a corresponding basic SQL script file can be automatically generated based on grammar, semantics, functions and the like of the basic database through an SQL script processing tool, wherein the basic SQL script file is an XML file of Mybatis converted based on the unified SQL sentences.

It should be noted that the setting database may include MySQL and Oracle. MySQL: the method is a relational database management system, SQL sentences used by MySQL are the most common standardized languages for accessing databases, and MySQL is generally selected as a website database for development of small and medium-sized websites due to the characteristics of small size, high speed, open source codes and the like. Oracle Database (Oracle Database): is a database management system. The Oracle database has the characteristics of complete data management function, complete relation product, distributed processing function, data warehouse operation realized by ORACLE, and the like.

In the embodiment of the specification, a plurality of alternative setting databases are provided, a developer can select one of the setting databases as a basic database based on actual requirements, develop an XML file of Mybatis as a basic SQL script file, adapt to a plurality of different application scenes, and automatically analyze and process the basic SQL script file subsequently to translate the basic SQL script file into SQL script files corresponding to other databases.

Step 104: and converting the set label in the original SQL label fragment into the comment to obtain a corresponding SQL sentence with the label comment.

In practical application, the basic SQL script file is analyzed and divided, at least one original SQL label fragment can be obtained, each original SQL label fragment comprises a setting label, and the setting label is not required to be changed in the translation process of the SQL sentence, so that the label content of the setting label is required to be hidden in the translation process of the SQL sentence content, and the label content of the setting label is restored after the translation, thereby ensuring that the content and the format of the translated setting label are unchanged.

Note that, the comment may be written in the program language, and the comment is an explanation of the program language, and the comment is not processed or lost in the compiling process, so in specific implementation, the setting tag in the tag fragment may be processed as the comment, so as to obtain a corresponding labeled SQL sentence, that is, the tag content of the setting tag in the labeled SQL sentence is converted into the form of the comment, and when the labeled SQL sentence is translated, the comment is not processed, and the comment is not lost, so that the content and format of the post-translation setting tag are kept unchanged.

In an optional implementation manner of this embodiment, before converting the setting tag in the original SQL tag fragment into the comment to obtain the corresponding SQL statement with the tag comment, the method may further include:

It should be noted that, when the setting labels are converted into comments, different comment rules may be adopted to ensure the correctness of the program, so before the setting labels in the label fragments are converted into comments to obtain corresponding SQL sentences with label comments, comment rules corresponding to the setting labels may be configured, so that the subsequent SQL script processing tool may automatically obtain corresponding comment rules based on the setting labels in the label fragments, and convert the label contents corresponding to the setting labels in the label fragments into comments according to the comment rules, so as to hide the label contents corresponding to the setting labels in the translation process of the SQL sentences, keep the content and format unchanged, and further ensure the correctness of the labels in the target SQL sentences generated by translation.

In an optional implementation manner of this embodiment, according to the grammar logic of each set tag, a corresponding annotation rule is configured, and the specific implementation process may be as follows:

and under the condition that the set label is of a third label type, determining branch contents of all branches in field contents corresponding to the set label, taking each branch content as an SQL label sub-segment, determining target SQL sub-sentences with labels and corresponding to all SQL label sub-segments, and merging the target SQL sub-sentences corresponding to all the branch contents to obtain the target SQL sentences corresponding to the original SQL label segments.

Note that, the first tag type means that the SQL tag content of the set tag includes only a tag field and does not include SQL statement content, and for the first tag type, the comment start identifier and the comment end identifier are directly added at the start and end positions of the tag content, and the tag content is converted into the comment, so that when the set tag is the first tag type, the comment rule may be configured as follows: and setting the field content corresponding to the setting tag as the annotation by using the annotation identifier.

In addition, the second tag type refers to that the SQL tag content of the set tag not only includes the tag, but also includes the SQL statement content, if the tag content of the set tag is directly converted into the comment, the SQL statement lacks content and is translated into errors, so that the missing content needs to be complemented at the set position, the missing content is the SQL statement content nested in the set tag, and after the tag content of the set tag is hidden into the comment, the SQL statement is complete and correct, if the missing content is the open and close content in the foreach tag; in addition, since the setting tag needs to be restored later, the tag content of the setting tag after restoration includes the missing content, and the completed missing content needs to be deleted, the explanation annotation can be added after the missing content, and the missing content can be deleted when the setting tag is instructed to be restored. That is, in the case where the tag is set to the second tag type, the annotation rule may be configured to: and setting the field content corresponding to the setting label as the annotation by using the annotation identifier, complementing the missing content at the setting position, and adding the explanatory annotation after the missing content.

Furthermore, the third tag type refers to the SQL tag content of the plurality of branches corresponding to the set tag, and the SQL tag of each branch needs to be translated, so that the branch content of each branch in the field content corresponding to the set tag can be determined, each branch content is used as an SQL tag sub-segment, the corresponding target SQL sub-statement with the tag annotation can be determined for each SQL tag sub-segment, the target SQL sub-statements corresponding to each branch content are combined, the complete target SQL statement with the tag annotation corresponding to the original SQL tag segment can be obtained, the set tag annotated in the target SQL statement can be restored, and the corresponding target SQL tag segment can be obtained, so that the final target SQL script file can be obtained by splicing each target SQL tag segment subsequently. That is, in the case where the tag is set to the third tag type, the annotation rule may be configured to: determining branch contents of each branch in field contents corresponding to the set tags, taking each branch content as an SQL tag sub-segment, determining target SQL sub-sentences with tag comments corresponding to each SQL tag sub-segment, merging the target SQL sub-sentences with tag comments corresponding to each branch content to obtain complete target SQL sentences with tag comments corresponding to the original SQL tag segment, and then restoring the tag comments to obtain the corresponding complete target SQL tag segment.

In practical application, when merging target SQL sub-sentences with label annotations corresponding to each branch content, the merging can be performed based on a merging rule, namely, the branch content of the main branch is selected, and the corresponding content in the SQL label annotations corresponding to the branch content of the target branch is replaced by using the SQL label annotations corresponding to other branch contents, namely, each branch content is merged into one condition judgment.

In the embodiment of the present disclosure, for different types of setting tags, corresponding annotation rules may be configured according to syntax logic of the setting tags, and a subsequent SQL script processing tool may automatically obtain corresponding annotation rules based on the setting tags in the tag fragments, and convert tag contents corresponding to the setting tags in the tag fragments into annotations according to the annotation rules, so as to hide the tag contents corresponding to the setting tags in a translation process of an SQL statement, and keep contents and formats unchanged, thereby ensuring correctness of a target SQL tag generated by translation.

In an optional implementation manner of this embodiment, the determining the target SQL sub-statement with the tag annotation corresponding to each SQL tag sub-fragment may be implemented as follows:

And translating the first SQL sub-statement with the label annotation into the first target SQL sub-statement with the label annotation corresponding to the target database.

It should be noted that, the branch tag in the first SQL tag sub-segment is a branch tag under the condition that the set tag in the first SQL tag sub-segment is judged, and the branch tag only includes a tag field, so that the comment start identifier and the comment end identifier can be directly added at the start and end positions of the tag content of the set tag in the first sub-tag segment, and the set tag in the first sub-tag segment is converted into a comment, so as to obtain a corresponding first SQL sub-statement with a tag comment. The first tagged annotated SQL sub-statement may then be translated into a corresponding first tagged annotated target SQL sub-statement of the target database.

In an optional implementation manner of this embodiment, the setting tag in the original SQL tag fragment is converted into the comment, so as to obtain the corresponding SQL statement with the tag comment, and the specific implementation process may be as follows:

acquiring a first annotation rule corresponding to a first setting tag;

In practical application, a first set tag included in a first tag fragment can be determined, then a first annotation rule corresponding to the first set tag is obtained from preconfigured annotation rules, and the first set tag in the first tag fragment is converted into an annotation according to the first annotation rule to obtain a corresponding first SQL sentence with the tag annotation. And by analogy, each tag fragment can be used as a first tag fragment to determine a corresponding SQL sentence with tag annotation.

As an example, an if tag is set as the first tag type, and the original SQL tag fragment is as follows:

select id, name, c_time from t_user where 1=1

and id=#{id}

</if>

and name like concat('%',#{id},'%')

</if>

the converter module in the SQL script processing tool can automatically identify the tag content corresponding to the if tag in the original SQL tag fragment, and then add the annotation identifier before and after the tag content Converting label content corresponding to the if label into annotation, and obtaining the following SQL sentence with the label annotation:

select id, name, c_time from t_user where 1=1

and id=#{id}

</if>/>

and name like concat('%',#{id},'%')

</if>/>

as another example, a foreach tag is set as the second tag type, and the original SQL tag fragment is as follows:

select id, name, c_time from t_user where 1=1 and condition_id in

#{condition_id}

</foreach>

order by condition_id

The converter module in the SQL script processing tool can automatically identify the label content corresponding to the foreach label in the original SQL label fragment, and annotate identification symbols are added before and after the label content Converting label content corresponding to the foreach label into annotation, and identifying and extracting open and close in the foreach label content"sum") ", the contents of open and close are spliced before and after the contents of the SQL statement of foreach, that is," sum ")" is spliced before and after "# { condition_id }", and after the splicing is completed, description comments need to be added before and after "(" sum ")", which represents that the subsequent contents need to be removed, so as to obtain the following SQL statement with label comments:

select id, name, c_time from t_user where 1=1 and condition_id in

toremove/>

(

/toremove/>

#{condition_id}

toremove/>

)

/toremove/>

</foreach>/>

order by condition_id

as yet another example, a choose write tag is set as the third tag type, and the original SQL tag fragment is as follows:

select id, order_code, c_time from

t_order_history

</when>

t_order

</otherwise>

</choose>

order id

the converter module in the SQL script processing tool can automatically identify the label content corresponding to the choose thread label in the original SQL label fragment, generate a corresponding SQL label sub-fragment SQL I aiming at the branch choose branch, and generate a corresponding SQL label sub-fragment SQL II aiming at the other branch.

For SQL label sub-segment SQL I, a converter module in the SQL script processing tool can automatically identify label content of a choose label in the SQL label sub-segment SQL I, and then annotate identification symbols are added before and after the label content Converting tag content corresponding to the choose tag into annotation, and obtaining an SQL sub-statement with tag annotation shown in SQL I as follows:

SQL I:

select id, order_code, c_time from

t_order_history

</when>/>

$1<otherwise>/>

t_order/>/>

/$1</otherwise>/>

</choose>/>

order id

for SQL label sub-segment SQL II, a converter module in the SQL script processing tool can automatically identify label content of an other label in the SQL label sub-segment SQL II, and then annotation identification symbols are added before and after the label content Converting tag content corresponding to the other tag into annotation, and obtaining an SQL sub-statement with tag annotation shown in the following SQL II:

SQL II:

select id, order_code, c_time from

t_order_history/>

</when>/>

$1<otherwise>/>

t_order

/$1</otherwise>/>

</choose>/>

order id

in the embodiment of the specification, the setting tags in each tag fragment can be converted into the notes to obtain the corresponding SQL statement with the tag notes, the SQL statement with the tag notes comprises SQL statement content to be translated, and the SQL statement with the tag notes is converted into the tag content, and when the SQL statement with the tag notes is translated subsequently, the SQL statement content converted into the notes is not processed, and only the SQL statement content is translated, so that the SQL statement under the target database is obtained through conversion, the tag content and the format are unchanged in the translation process, and the accuracy of the SQL tag fragment obtained through translation is ensured.

Step 106: and translating the SQL statement with the label annotation into a target SQL statement with the label annotation corresponding to the target database, and restoring the set label annotated in the target SQL statement to obtain a target SQL label fragment corresponding to the target database.

It should be noted that, the obtained SQL statement with label annotation omits the label content which needs to be kept unchanged, and keeps the SQL statement content which needs to be translated, so that the SQL statement with label annotation can be directly translated into the target SQL statement corresponding to the target database, and the target SQL statement with label annotation is obtained after the translation is completed, and the content and the position of the annotation are kept unchanged. And then, the set label in the target SQL sentence can be restored to obtain a target SQL label fragment corresponding to the target database.

In practical application, annotation identification symbols before and after the label is set in the target SQL sentence can be deleted, and content before annotation is deleted, wherein the content is the deletion content which is completed in the annotation process and converted into the label, so that the set label in the target SQL sentence is restored, and a complete target SQL label fragment is obtained.

In an optional implementation manner of this embodiment, the translation of the labeled and annotated SQL statement into the labeled and annotated target SQL statement corresponding to the target database may be implemented as follows:

Specifically, the translation model may refer to a unified SQL converter, and the unified SQL converter is utilized to convert a basic SQL statement corresponding to a basic database into a target SQL statement corresponding to a target database.

It should be noted that, the tag annotated SQL statement and the database identifier (such as database type) of the target database may be input into a unified SQL converter, and the unified SQL converter may convert the tag annotated SQL statement into the SQL statement content corresponding to the target database, so as to obtain the target SQL statement corresponding to the target database, where the target SQL statement is an SQL statement conforming to the rules of semantics, grammar, words, functions, etc. of the target database, and the target SQL statement further includes tag content hidden as an annotation, where the detailed content and format of the tag content are unchanged during the translation process, so as to ensure the accuracy of the target SQL statement obtained by translation.

In practical application, after the target SQL statement corresponding to the target database is obtained through translation, annotation erasure and arrangement can be performed, namely, the set label in the target SQL statement is restored, and a complete target SQL label fragment is obtained.

As an example, translate the labeled and annotated SQL statement corresponding to the if label, and input the labeled and annotated SQL statement to the translation model, the following labeled and annotated target SQL statement may be obtained:

select id, name, c_time from t_user where 1=1

and id=#{id}

</if>/>

and name like '%'||#{id}||'%'

</if>/>

From the above, and name like concat ('%, # id, '% ') in the labeled SQL statement is translated into the and name like '% ' |#id } ||' ' in the target SQL statement. Then, for the target SQL statement with the label annotation, the annotation identifier symbols before and after the label content of the if label are erased, and the if label in the target SQL statement with the label annotation is restored, so that the following target SQL label fragment is obtained:

select id, name, c_time from t_user where 1=1

and id=#{id}

</if>

and name like '%'||#{id}||'%'

</if>

as another example, the above-mentioned labeled and annotated SQL statement corresponding to the foreach label is translated, the labeled and annotated SQL statement is input to the translation model, and the labeled and annotated target SQL statement corresponding to the labeled and annotated SQL statement is obtained, and for the labeled and annotated SQL statement, since there is no difference between the basic query language and the SQL statement corresponding to the target database, the conversion result is consistent with the content of the labeled and annotated SQL statement. Then, the annotation identifier before and after the tag content of the foreach tag is erased for the target SQL statement with tag annotation obtained by translation, namelytoremove/>And->/toremove/>The content in between contains comments and is deleted together, and the front and back added "(" sum ")" of "# { condition_id }" is deleted, and the foreach tag in the target SQL sentence with the tag comment is restored, so that the following target SQL tag fragment is obtained: / >

select id, name, c_time from t_user where 1=1 and condition_id in

#{condition_id}

</foreach>

order by condition_id

As another example, translate the above-mentioned labeled and annotated SQL sub-statement corresponding to the choose write label, input the above-mentioned labeled and annotated SQL sub-statement shown in SQL one and SQL two to the translation model respectively, obtain the first labeled and annotated target SQL sub-statement corresponding to the choose label and the second labeled and annotated target SQL sub-statement corresponding to the other write label, then merge the first target SQL sub-statement and the second target SQL sub-statement, and the rule of merging is: to the second target SQL sub-statement$1……/>The/$1 part replaces ++in the first target SQL sub-statement>$1……/>And a/$1 part (corresponding to the serial number $1), and finally obtaining an SQL text, namely the target SQL sentence with the label annotation corresponding to the choose white label, as follows:

select id, order_code, c_time from

t_order_history

</when>/>

$1<otherwise>/>

t_order

/$1</otherwise>/>

</choose>/>

order id

erasing annotation identification symbols before and after label content of a choose label and an otherwise label aiming at the target SQL statement with the label annotation, andthe annotation of the $1 type, namely the final XML text, namely the target SQL label fragment corresponding to the choose white label is obtained as follows:

select id, order_code, c_time from

t_order_history

</when>

t_order

</otherwise>

</choose>

order id

step 108: and splicing the target SQL label fragments corresponding to the at least one original SQL label fragment to obtain a target SQL script file corresponding to the target database.

It should be noted that after the fragments convert the corresponding target SQL tag fragments from the tag fragments, the target SQL tag fragments corresponding to the original SQL tag fragments may be spliced to obtain a complete target SQL script file corresponding to the target database, i.e., a complete XML file corresponding to the target database.

In practical application, after the target SQL script file corresponding to the target database is obtained, the complete target SQL script file obtained by translation (namely, the complete XML file) can be placed into Mybatis engineering for configuration and use, and the test and adjustment can be directly performed based on the complete target SQL script file obtained by translation. And then, the target SQL script file after passing the test and adjustment can be sent to a target database, and the target SQL script file can run in the target database to realize the operations of inquiring, updating, managing and the like of the target database.

According to the processing method of the database SQL script, the basic SQL script file can be automatically analyzed to obtain the original SQL label fragments included in the basic SQL script file, the setting labels in each original SQL label fragment are converted into comments, then the obtained SQL sentences with the labels and the comments are translated into the target SQL sentences with the labels and the comments corresponding to the target database, the setting labels and the comments in the target SQL sentences are restored to obtain the target SQL label fragments corresponding to the target database, and therefore the target SQL script file corresponding to the target database is obtained by splicing the target SQL label fragments. Therefore, the content of the label which does not need to be changed in the translation process can be hidden through the annotation mode, only the content of the SQL sentence which needs to be changed is translated, then the content of the label in the translation fragment is restored, the translated SQL sentence is obtained, and the annotated label is restored, so that the content and format of the label are unchanged in the translation process of the SQL sentence content, the SQL script files of different types of databases are automatically converted and generated on the basis of a unified basic SQL script file, the accuracy of the generated SQL script file is ensured, the SQL script file can be successfully operated in the corresponding database, the development efficiency of the SQL script file of the database is greatly improved, the maintenance cost is reduced, and the burden of developers is lightened.

Fig. 2 shows a timing chart of a method for processing a database SQL script according to an embodiment of the present disclosure, which is applied to an SQL script processing tool, as shown in fig. 2, and specifically includes the following steps:

step 202: in Mybatis, a developer develops a basic XML file corresponding to a basic database, wherein the basic database is MySQL or Oracle.

Step 204: the XML converter analyzes the basic XML file to obtain at least one original SQL label fragment, obtains annotation rules corresponding to the set labels contained in each original SQL label fragment, and converts the set labels in each original SQL label fragment into annotations according to the annotation rules to obtain corresponding SQL sentences with the labels.

Step 206: the XML converter inputs the SQL statement with the label annotation into a unified SQL converter, the unified SQL converter translates the SQL statement with the label annotation, and the label annotation content in the SQL statement with the label annotation is reserved, so that the target SQL statement with the label annotation corresponding to the target database is obtained.

Step 208: the XML converter restores the annotated setting tags in the target SQL sentence to obtain a target SQL tag fragment corresponding to the target database.

Step 210: the XML converter splices at least one target SQL label fragment corresponding to the original SQL label fragment to obtain a target SQL script file corresponding to the target database.

Step 212: the XML converter sends the target SQL script file to the specific catalog of Mybatis for use.

According to the processing method of the database SQL script, the basic SQL script file can be automatically analyzed to obtain the original SQL label fragments included in the basic SQL script file, the setting labels in each original SQL label fragment are converted into comments, then the obtained SQL sentences with the labels and the comments are translated into the target SQL sentences with the labels and the comments corresponding to the target database, the setting labels and the comments in the target SQL sentences are restored to obtain the target SQL label fragments corresponding to the target database, and therefore the target SQL label fragments are spliced to obtain the target SQL script file corresponding to the target database. Therefore, the content of the label which does not need to be changed in the translation process can be hidden through the annotation mode, only the content of the SQL sentence which needs to be changed is translated, then the content of the label in the translation fragment is restored, the translated SQL sentence is obtained, and the annotated label is restored, so that the content and format of the label are unchanged in the translation process of the SQL sentence content, the SQL script files of different types of databases are automatically converted and generated on the basis of a unified basic SQL script file, the accuracy of the generated SQL script file is ensured, the SQL script file can be successfully operated in the corresponding database, the development efficiency of the SQL script file of the database is greatly improved, the maintenance cost is reduced, and the burden of developers is lightened.

Corresponding to the method embodiment, the present disclosure further provides an embodiment of a processing device for a database SQL script, and fig. 3 shows a schematic structural diagram of a processing device for a database SQL script according to an embodiment of the present disclosure. As shown in fig. 3, the apparatus includes:

the obtaining module 302 is configured to obtain a basic SQL script file, and analyze the basic SQL script file to obtain at least one original SQL tag fragment, where the original SQL tag fragment includes a setting tag;

the annotation module 304 is configured to convert the setting tags in the original SQL tag fragment into annotations to obtain corresponding SQL sentences with tag annotations;

the restoration module 306 is configured to translate the SQL statement with the tag annotation into the target SQL statement with the tag annotation corresponding to the target database, restore the setting tag annotated in the target SQL statement, and obtain a target SQL tag fragment corresponding to the target database;

and the splicing module 308 is configured to splice the target SQL label fragments corresponding to the at least one original SQL label fragment to obtain a target SQL script file corresponding to the target database.

Optionally, the annotation module 304 is further configured to:

Acquiring a first annotation rule corresponding to a first setting tag;

Optionally, the apparatus further comprises a configuration module configured to:

Optionally, the configuration module is further configured to:

Optionally, the reduction module 306 is further configured to:

Optionally, the obtaining module 302 is further configured to:

determining a basic database from the setting database;

and developing a basic SQL script file corresponding to the basic database.

According to the processing device for the database SQL script provided by the embodiment of the specification, the basic SQL script file can be automatically analyzed to obtain the original SQL label fragments included in the basic SQL script file, the setting labels in each original SQL label fragment are converted into comments, then the obtained SQL sentences with the label comments are translated into the target SQL sentences with the label comments corresponding to the target database, the setting labels annotated in the target SQL sentences are restored to obtain the target SQL label fragments corresponding to the target database, and therefore the target SQL script corresponding to the target database is obtained by splicing the target SQL label fragments. Therefore, the content of the label which does not need to be changed in the translation process can be hidden through the annotation mode, only the content of the SQL sentence which needs to be changed is translated, then the content of the label in the translation fragment is restored, the translated SQL sentence is obtained, and the annotated label is restored, so that the content and format of the label are unchanged in the translation process of the SQL sentence content, the SQL script files of different types of databases are automatically converted and generated on the basis of a unified basic SQL script file, the accuracy of the generated SQL script file is ensured, the SQL script file can be successfully operated in the corresponding database, the development efficiency of the SQL script file of the database is greatly improved, the maintenance cost is reduced, and the burden of developers is lightened.

The above is a schematic scheme of a processing device of the database SQL script of the present embodiment. It should be noted that, the technical solution of the processing device of the database SQL script and the technical solution of the processing method of the database SQL script belong to the same concept, and the details of the technical solution of the processing device of the database SQL script which are not described in detail can be referred to the description of the technical solution of the processing method of the database SQL script.

Fig. 4 illustrates a block diagram of a computing device provided in accordance with an embodiment of the present specification. The components of the computing device 400 include, but are not limited to, a memory 410 and a processor 420. Processor 420 is coupled to memory 410 via bus 430 and database 450 is used to hold data.

Computing device 400 also includes access device 440, access device 440 enabling computing device 400 to communicate via one or more networks 460. Examples of such networks include public switched telephone networks (PSTN, public Switched Telephone Network), local area networks (LAN, local Area Network), wide area networks (WAN, wide Area Network), personal area networks (PAN, personal Area Network), or combinations of communication networks such as the internet. The access device 440 may include one or more of any type of network interface, wired or wireless, such as a network interface card (NIC, network Interface Controller), such as an IEEE802.11 wireless local area network (WLAN, wireless Local Area Networks) wireless interface, a worldwide interoperability for microwave access (Wi-MAX, worldwide Interoperability for Microwave Access) interface, an ethernet interface, a universal serial bus (USB, universal Serial Bus) interface, a cellular network interface, a bluetooth interface, a near field communication (NFC, near Field Communication) interface, and so forth.

In one embodiment of the present description, the above-described components of computing device 400, as well as other components not shown in FIG. 4, may also be connected to each other, such as by a bus. It should be understood that the block diagram of the computing device shown in FIG. 4 is for exemplary purposes only and is not intended to limit the scope of the present description. Those skilled in the art may add or replace other components as desired.

Computing device 400 may be any type of stationary or mobile computing device, including a mobile computer or mobile computing device (e.g., tablet, personal digital assistant, laptop, notebook, netbook, etc.), mobile phone (e.g., smart phone), wearable computing device (e.g., smart watch, smart glasses, etc.), or other type of mobile device, or a stationary computing device such as a desktop computer or PC. Computing device 400 may also be a mobile or stationary server.

Wherein the processor 420 is configured to execute the following computer executable instructions to implement the steps of the method for processing a database SQL script of any of the above.

The foregoing is a schematic illustration of a computing device of this embodiment. It should be noted that, the technical solution of the computing device and the technical solution of the processing method of the database SQL script belong to the same concept, and details of the technical solution of the computing device, which are not described in detail, can be referred to the description of the technical solution of the processing method of the database SQL script.

An embodiment of the present disclosure also provides a computer-readable storage medium storing computer instructions that, when executed by a processor, perform the steps of a method for processing a database SQL script of any of the above.

The above is an exemplary version of a computer-readable storage medium of the present embodiment. It should be noted that, the technical solution of the storage medium and the technical solution of the processing method of the database SQL script belong to the same concept, and details of the technical solution of the storage medium which are not described in detail can be referred to the description of the technical solution of the processing method of the database SQL script.

The foregoing describes specific embodiments of the present disclosure. Other embodiments are within the scope of the following claims. In some cases, the actions or steps recited in the claims can be performed in a different order than in the embodiments and still achieve desirable results. In addition, the processes depicted in the accompanying figures do not necessarily require the particular order shown, or sequential order, to achieve desirable results. In some embodiments, multitasking and parallel processing are also possible or may be advantageous.

The computer instructions include computer program code which may be in source code form, object code form, executable file or some intermediate form, etc. The computer readable medium may include: any entity or device capable of carrying computer program code, a recording medium, a U disk, a removable hard disk, a magnetic disk, an optical disk, a computer Memory, a Read-Only Memory (ROM), a random access Memory (RAM, random Access Memory), an electrical carrier signal, a telecommunications signal, a software distribution medium, and so forth.

It should be noted that, for the sake of simplicity of description, the foregoing method embodiments are all expressed as a series of combinations of actions, but it should be understood by those skilled in the art that the present description is not limited by the order of actions described, as some steps may be performed in other order or simultaneously in accordance with the present description. Further, those skilled in the art will appreciate that the embodiments described in the specification are all preferred embodiments, and that the acts and modules referred to are not necessarily all necessary in the specification.

In the foregoing embodiments, the descriptions of the embodiments are emphasized, and for parts of one embodiment that are not described in detail, reference may be made to the related descriptions of other embodiments.

The preferred embodiments of the present specification disclosed above are merely used to help clarify the present specification. Alternative embodiments are not intended to be exhaustive or to limit the invention to the precise form disclosed. Obviously, many modifications and variations are possible in light of the above teaching. The embodiments were chosen and described in order to best explain the principles of the disclosure and the practical application, to thereby enable others skilled in the art to best understand and utilize the disclosure. This specification is to be limited only by the claims and the full scope and equivalents thereof.

Claims

1. A method for processing a database SQL script, the method comprising:

according to annotation rules corresponding to the set tags in the original SQL tag fragments, converting tag contents of the set tags into annotations to obtain corresponding SQL sentences with tag annotations;

translating the SQL statement with the label annotation into a target SQL statement with the label annotation corresponding to a target database, and restoring the set label annotated in the target SQL statement to obtain a target SQL label fragment corresponding to the target database;

Splicing target SQL label fragments corresponding to the at least one original SQL label fragment to obtain a target SQL script file corresponding to the target database;

the method further comprises the steps of, before the label content of the set label is converted into the comment according to the comment rule corresponding to the set label in the original SQL label fragment to obtain the corresponding SQL sentence with the label comment:

and configuring corresponding annotation rules according to different types of grammar logic of the set tags, wherein the annotation rules are rules for processing tag contents of the set tags into annotations under the condition of ensuring that a program is correct.

2. The method for processing the SQL script of claim 1, wherein the converting the tag content of the set tag into the comment according to the comment rule corresponding to the set tag in the original SQL tag fragment to obtain the corresponding SQL statement with the tag comment comprises:

determining a first setting tag included in a first SQL tag fragment, wherein the first SQL tag fragment is any one of the at least one original SQL tag fragment;

acquiring a first annotation rule corresponding to the first setting tag;

and according to the first annotation rule, converting the tag content of the first set tag in the first SQL tag fragment into an annotation to obtain a corresponding first SQL statement with the tag annotation.

3. The method for processing the SQL script of claim 1, wherein configuring the corresponding annotation rule according to the syntax logic of the different types of setting tags comprises:

setting field content corresponding to the setting label as annotation by using an annotation identifier under the condition that the setting label is of a first label type;

setting field content corresponding to the setting tag as an annotation by using an annotation identifier under the condition that the setting tag is of a second tag type, complementing missing content at a set position, and adding an explanatory annotation after the missing content, wherein the missing content is SQL sentence content nested in the setting tag, and the explanatory annotation is used for indicating deletion of the missing content when the setting tag is restored;

4. A method of processing a database SQL script according to claim 3, wherein the determining a tagged annotated target SQL sub-statement corresponding to each SQL tag sub-fragment comprises:

5. The method for processing the database SQL script according to claim 1, wherein translating the tagged annotated SQL statement into a tagged annotated target SQL statement corresponding to a target database comprises:

and inputting the SQL statement with the tag annotation into a translation model, translating the SQL statement with the tag annotation through the translation model, and reserving tag annotation content in the SQL statement with the tag annotation to obtain a target SQL statement with the tag annotation corresponding to a target database.

6. The method for processing the SQL script of claim 1, wherein the obtaining the basic SQL script file comprises:

Determining a basic database from the setting database;

and developing a basic SQL script file corresponding to the basic database.

7. A processing device for a database SQL script, the device comprising:

the annotation module is configured to convert label content of the set label into annotation according to annotation rules corresponding to the set label in the original SQL label fragment to obtain a corresponding SQL sentence with the label annotation;

the restoration module is configured to translate the SQL statement with the label annotation into a target SQL statement with the label annotation corresponding to a target database, restore the setting label annotated in the target SQL statement and obtain a target SQL label fragment corresponding to the target database;

the splicing module is configured to splice the target SQL label fragments corresponding to the at least one original SQL label fragment to obtain a target SQL script file corresponding to the target database;

wherein the apparatus further comprises a configuration module configured to:

8. A computing device, comprising:

a memory and a processor;

the memory is configured to store computer executable instructions and the processor is configured to execute the computer executable instructions to implement the steps of the method of processing a database SQL script as claimed in any of claims 1-6.

9. A computer readable storage medium storing computer instructions which, when executed by a processor, implement the steps of the method of processing a database SQL script as claimed in any one of claims 1 to 6.