Heterogeneous database sql statement adaptation method, device and equipment

Abstract

The application relates to the technical field of databases, and provides a heterogeneous database SQL statement adaptation method, device and equipment. The method comprises the following steps: obtaining a to-be-matched SQL statement sent by a source end database; detecting whether a matchable string exists in the to-be-matched SQL statement based on a preset rule; in the case where the matchable string does not exist in the to-be-matched SQL statement, constructing a target SQL statement executable by a target end database and having the same semantics as the to-be-matched SQL statement based on an execution plan corresponding to the to-be-matched SQL statement in the source end database or an execution plan of an approximate SQL statement associated with the to-be-matched SQL statement in the source end database. The heterogeneous database SQL statement adaptation method, device and equipment provided in the application can be used to solve the technical problems of low application migration efficiency and high development and maintenance cost of codes in the current solution when the application is migrated between different databases.

Description

Technical Field

[0001] This application relates to the field of database technology, specifically to a method, apparatus, and device for adapting SQL statements to heterogeneous databases. Background Technology

[0002] Currently, migrating applications between different databases is a common challenge for enterprises. For example, when migrating from an Oracle database to a domestic database like PolarDB, it's necessary to resolve some syntax and semantic incompatibilities. In such cases, SQL statements that originally ran on the Oracle source database need to be rewritten to achieve the same semantics on the target database. For instance, the statement `Select max(count(*)) from t group by t.c1` is supported in Oracle databases but may not be supported in other databases (such as PolarDB). It needs to be rewritten as `Select max(c) from(select count(*) from t group by t.c1) as q(c)` to run in the PolarDB database.

[0003] Current solutions require significant modifications to application code and SQL statements, consuming substantial human and material resources. Furthermore, business users typically require a single codebase to support different databases simultaneously, making code modifications a burden due to the need to maintain multiple codebases. In short, current solutions suffer from low application migration efficiency and high code development and maintenance costs when migrating between different databases. Summary of the Invention

[0004] This application provides a method, apparatus, and device for adapting SQL statements to heterogeneous databases, in order to solve the technical problems of low application migration efficiency and high code development and maintenance costs when migrating applications between different databases in existing solutions.

[0005] In a first aspect, embodiments of this application provide a method for adapting SQL statements to heterogeneous databases, including:

[0006] Retrieve the SQL statement to be matched sent by the source database;

[0007] Based on preset rules, detect whether there is a matchable string in the SQL statement to be matched;

[0008] If no matching string exists in the SQL statement to be matched, a target SQL statement that is semantically identical to the SQL statement to be matched is constructed in the target database based on the execution plan corresponding to the SQL statement to be matched in the source database, or the execution plan of an approximate SQL statement associated with the SQL statement to be matched in the source database.

[0009] In one embodiment, detecting whether a matchable string exists in the SQL statement to be matched based on preset rules includes:

[0010] Repeat the following operations until the preset time threshold and / or preset number of times threshold is reached:

[0011] Calculate the hash value of the source string in the SQL statement to be matched, and check whether the hash value matches the hash value of the hash table; the hash table includes multiple target strings stored in the target database and the hash value corresponding to each target string;

[0012] If the hash value matches the hash value of the hash table, the source string is determined to be a matchable string;

[0013] The system detects whether the source string of the SQL statement to be matched matches the regular expressions in the set of regular expressions; the set of regular expressions includes various regular expressions stored in the target database.

[0014] If the source string matches the regular expression, the source string is determined to be a matchable string.

[0015] In one embodiment, before the step of calculating the hash value of the source string in the SQL statement to be matched and matching the hash value with a hash table, the method further includes:

[0016] The SQL statement to be matched is then normalized.

[0017] The normalization process of the SQL statement to be matched includes at least one of the following methods:

[0018] Remove the comments from the SQL statement to be matched;

[0019] Remove spaces from the SQL statement to be matched;

[0020] Remove newline characters from the SQL statement to be matched.

[0021] In one embodiment, constructing a target SQL statement executable in the target database that has the same semantics as the SQL statement to be matched, based on the execution plan corresponding to the SQL statement to be matched in the source database, includes:

[0022] Based on the execution plan corresponding to the SQL statement to be matched in the source database, construct the first execution plan tree in the target database;

[0023] Based on the first execution plan tree, construct the target SQL statement that is semantically identical to the SQL statement to be matched and can be executed in the target database.

[0024] In one embodiment, constructing a target SQL statement executable in the target database that has the same semantics as the SQL statement to be matched, based on the execution plan of an approximate SQL statement associated with the SQL statement to be matched in the source database, includes:

[0025] Based on semantic analysis methods, an approximate SQL statement is determined that is associated with the SQL statement to be matched in the source database;

[0026] Based on the execution plan corresponding to the approximate SQL statement, a second execution plan tree is constructed in the target database;

[0027] Based on the second execution plan tree, construct the target SQL statement that is semantically identical to the SQL statement to be matched and can be executed in the target database.

[0028] In one embodiment, after determining that the source string is a matchable string if the hash value matches the hash value of the hash table, the method further includes:

[0029] The matching string is converted into a target string that matches the hash value in the hash table;

[0030] After determining that the source string is a matchable string if it matches the regular expression, the method further includes:

[0031] The matchable string is converted into a target string that matches the preset rules of the regular expression.

[0032] Secondly, embodiments of this application provide a heterogeneous database SQL statement adaptation device, comprising:

[0033] The acquisition module is used to acquire the SQL statements to be matched sent by the source database;

[0034] The detection module is used to detect whether there is a matchable string in the SQL statement to be matched based on preset rules;

[0035] An adaptation module is used to construct a target SQL statement that is semantically identical to the target SQL statement when there is no matching string in the SQL statement to be matched, based on the execution plan corresponding to the SQL statement to be matched in the source database, or the execution plan of an approximate SQL statement associated with the SQL statement to be matched in the source database.

[0036] Thirdly, embodiments of this application provide an electronic device, including a processor and a memory storing a computer program, wherein the processor executes the program to implement the steps of the heterogeneous database SQL statement adaptation method described in the first aspect.

[0037] Fourthly, embodiments of this application provide a non-transitory computer-readable storage medium storing a computer program thereon, wherein the computer program, when executed by a processor, implements the steps of the heterogeneous database SQL statement adaptation method described in the first aspect.

[0038] Fifthly, embodiments of this application provide a computer program product, including a computer program that, when executed by a processor, implements the steps of the heterogeneous database SQL statement adaptation method described in the first aspect.

[0039] The heterogeneous database SQL statement adaptation method, apparatus, and device provided in this application embodiment detect whether a matching string exists in the SQL statement to be matched based on preset rules. If no matching string exists, a target SQL statement executable in the target database with the same semantics as the SQL statement to be matched is constructed based on the execution plan corresponding to the SQL statement to be matched in the source database, or the execution plan of an approximate SQL statement associated with the SQL statement to be matched in the source database. This application embodiment constructs a target SQL statement with the same semantics as the SQL statement to be matched, executable in the target database, based on the execution plan of the SQL statement to be matched in the source database or the execution plan of an approximate SQL statement. This application embodiment enables the migration of SQL statements from heterogeneous databases, thereby achieving application migration between different syntax databases without modifying the application, greatly improving application migration efficiency. Furthermore, this application embodiment eliminates the need for applications to maintain multiple code versions to support multiple databases, reducing code development and maintenance costs. Attached Figure Description

[0040] To more clearly illustrate the technical solutions in this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0041] Figure 1 This is one of the flowcharts illustrating the heterogeneous database SQL statement adaptation method provided in the embodiments of this application;

[0042] Figure 2 This is the second flowchart illustrating the heterogeneous database SQL statement adaptation method provided in this application embodiment;

[0043] Figure 3 This is the third flowchart illustrating the heterogeneous database SQL statement adaptation method provided in this application embodiment;

[0044] Figure 4 This is the fourth flowchart illustrating the heterogeneous database SQL statement adaptation method provided in this application embodiment;

[0045] Figure 5 This is the fifth flowchart illustrating the heterogeneous database SQL statement adaptation method provided in this application embodiment;

[0046] Figure 6 This is the sixth flowchart illustrating the heterogeneous database SQL statement adaptation method provided in this application embodiment;

[0047] Figure 7 This is the seventh flowchart illustrating the heterogeneous database SQL statement adaptation method provided in this application embodiment;

[0048] Figure 8 This is a schematic diagram of the heterogeneous database SQL statement adaptation device provided in the embodiments of this application;

[0049] Figure 9 This is a schematic diagram of the structure of the electronic device provided in the embodiments of this application. Detailed Implementation

[0050] To make the objectives, technical solutions, and advantages of this application clearer, the technical solutions of this application will be clearly and completely described below with reference to the accompanying drawings of the embodiments. Obviously, the described embodiments are only some embodiments of this application, not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0051] The terms heterogeneous database and database migration used in this article are defined as follows:

[0052] Heterogeneous databases refer to databases running different database management system software, which follow different SQL syntax standards.

[0053] Database migration: This article refers to the process of adapting upper-layer applications to heterogeneous databases, that is, allowing the application to run on heterogeneous databases without affecting the application's running status and results.

[0054] Figure 1 Methods for adapting SQL statements to heterogeneous databases. Please refer to [link / reference]. Figure 1 This application provides a method for adapting SQL statements to heterogeneous databases, which may include:

[0055] Step 100: Obtain the SQL statement to be matched sent by the source database;

[0056] The electronic device receives the SQL statement to be matched from the source database. The source database refers to the database from which the SQL statement to be matched can perform operations. The source database can include various databases, such as Oracle, SQL Server, DB2, Infomix, Sybase, MySQL, etc.

[0057] Step 200: Detect whether there is a matchable string in the SQL statement to be matched based on preset rules;

[0058] The electronic device detects whether a matchable string exists in the SQL statement to be matched based on preset rules. Specifically, the electronic device can perform text replacement based on simple rules and logical matching (regular expressions) based on complex rules on the string of the SQL statement to be matched. When a matchable string exists in the SQL statement to be matched that matches the simple rules and / or complex rules, the string of the SQL statement to be matched is replaced with the target SQL statement that can be executed in the target database.

[0059] Electronic devices can customize SQL statement transformation rules. These rules are divided into simple rules and complex rules. Simple rules involve string matching and replacement. Users pre-store the replacement rules in a hash table, and the database performs matching and replacement based on the hash values. Complex rules consist of a series of pattern recognitions (which may be regular expressions in this embodiment) and corresponding actions (conversion, replacement, etc.). Both simple and complex rules are pre-stored in the database.

[0060] Electronic devices can define SQL transformation rule sets TR, which consist of n rules R. Each rule R consists of two parts: matching method FM and action ACT, as shown below:

[0061] TR = {R i , i∈[1, n], n∈N}

[0062] R i =<FM,ACT>

[0063] Depending on whether an electronic device uses FM or ACT, two sets can be defined: a simple rule set TRS and a complex rule set TRC. A TRS consists of n simple rules SR, and a TRC consists of m complex rules CR, as shown below:

[0064] TR s ={SR i , i∈[1, n], n∈N}

[0065] TR C ={CR i , i∈[1,m], m∈N}

[0066] In this embodiment, the simple rule SR is defined as the string matching method SFM and the replacement action SACT:

[0067] SR i = <SFM i SACT i >

[0068] SFM is defined as determining whether the string `SQLin` in the incoming SQL statement to be matched is the same as the specified target string `SSQL`. If they are the same, SACT is executed; otherwise, it is skipped. In this embodiment, the hash value of the source string in the source database is calculated, and then matched with the hash value in the hash table. When the hash value of the source string matches the hash value in the hash table, the source string is determined to be a matchable string, and the target string of the source string is obtained. To accelerate the string matching process, users can pre-set a simple rule set TR. S Store in hash table H SR In this database, matching and replacement are performed based on hash values. SACT is defined as a string replacement operation, which takes the source string SQL statement to be matched as input. in Replace with the target string TSQL. This can be expressed by the following formula:

[0069] SFM i =isequal(SQL) in SSQL i )

[0070]

[0071] Electronic devices define a complex rule set CR as a series of pattern recognition CFM (such as regular expressions) and corresponding actions CACT (transformation, replacement, etc.): specifically as follows:

[0072] CR i ={CFM i CACT i}

[0073] CFM is defined as determining the source string SQL passed from the source database. in Whether it matches the specified target string pattern SQLM. In this embodiment, the pattern SQLM can be represented by a regular expression, that is, to determine whether the source string SQL of the incoming source database is matched. in This option checks if the specified target string matches the regular expression. If a match is found, the source string is considered a matchable string, and the corresponding action (CACT) can be executed; otherwise, it is skipped. CACT is defined as a series of string operations, including conversion and replacement, and takes the source string (SQL) passed to the source database as input. in It is converted into the target string TSQL according to certain rules.

[0074] CFM i =ismatch(SQL) in SQLM i )

[0075]

[0076] The electronic device can detect matchable strings in the SQL statement to be matched based on the simple and complex rules mentioned above. If there are no matchable strings in the SQL statement to be matched, step 300 is executed. If there are matchable strings in the SQL statement to be matched, the corresponding simple or complex rules are executed to convert the matchable strings of the SQL statement to be matched into the target string according to certain rules.

[0077] Step 300: If there is no matching string in the SQL statement to be matched, construct a target SQL statement that is semantically identical to the SQL statement to be matched in the target database, based on the execution plan corresponding to the SQL statement to be matched in the source database, or the execution plan of an approximate SQL statement associated with the SQL statement to be matched in the source database.

[0078] If no matching string exists in the SQL statement to be matched, the electronic device constructs a target SQL statement with the same semantics as the SQL statement to be matched, which is executable in the target database, based on the execution plan of the SQL statement to be matched in the source database or the execution plan of an approximate SQL statement. This allows the target SQL statement, which is converted from the SQL statement to be matched, to run on the target database, which is heterogeneous with the source database, without affecting the running status and results of the application.

[0079] Specifically, in one embodiment, please refer to Figure 2 and Figure 4 Step 300: Constructing an executable target SQL statement in the target database that has the same semantics as the SQL statement to be matched, based on the execution plan corresponding to the SQL statement to be matched in the source database, includes:

[0080] Step 310: Based on the execution plan corresponding to the SQL statement to be matched in the source database, construct the first execution plan tree in the target database;

[0081] Step 320: Construct a target SQL statement that is semantically identical to the target database executable statement based on the first execution plan tree.

[0082] Specifically, the electronic device retrieves the execution text and execution plan of the SQL statement to be matched from the system tables and memory of the source database, and performs deduplication and structuring processing on the data. Then, based on the execution plan of the SQL statement to be matched, a first execution plan tree is constructed in the target database, and then based on the first execution plan tree, a target SQL statement that is semantically identical to the SQL statement to be matched and can be executed in the target database is constructed.

[0083] It should be noted that, according to the execution plan, the operation of generating executable target statements with the same semantics in the target database can be achieved through the functionality of the SQL statement parsing engine on the existing target database. Different databases have different technical implementation methods, which will not be elaborated here.

[0084] In other embodiments, please refer to Figure 3 and Figure 4 Step 300: Constructing an executable target SQL statement in the target database that has the same semantics as the SQL statement to be matched, based on the execution plan of the approximate SQL statement associated with the SQL statement to be matched in the source database, includes:

[0085] Step 330: Determine the approximate SQL statement associated with the SQL statement to be matched in the source database based on semantic analysis methods;

[0086] Step 340: Based on the execution plan corresponding to the approximate SQL statement, construct a second execution plan tree in the target database;

[0087] Step 350: Construct a target SQL statement that is semantically identical to the target database executable statement based on the second execution plan tree.

[0088] When the electronic device cannot find the execution plan of the statement to be matched in the source database, it determines an approximate SQL statement associated with the SQL statement to be matched in the source database. Specifically, the electronic device can determine the approximate SQL statement associated with the SQL statement to be matched in the source database based on historical SQL statements and other SQL statements in the source database.

[0089] Specifically, the electronic device can determine approximate SQL statements associated with the SQL statement to be matched in the source database based on semantic analysis methods. The semantic analysis methods can employ various methods capable of analyzing whether semantic relationships exist between statements. For example, the semantic analysis methods in this embodiment can include NLP (Natural Language Processing) algorithms, PCFG (Probabilistic Context-Free Grammar) algorithms, PCFG-LA (PCFG with latentannotations) algorithms, CYK (Cocke–Younger–Kasami algorithm), etc.

[0090] For example, embodiments of this application can use NLP algorithms to find the approximate SQL statement with the highest correlation to the SQL statement to be matched from historical SQL statements and other SQL statements in the source database. This allows for the construction of an executable target SQL statement with the same semantics as the SQL statement to be matched in the target database based on the execution plan of the approximate SQL statement.

[0091] The electronic device then constructs a second execution plan tree in the target database based on the execution plan corresponding to the approximate SQL statement; and constructs a target SQL statement that is semantically identical to the SQL statement to be matched in the target database based on the second execution plan tree.

[0092] Similarly, according to the execution plan, the operation of generating executable target statements with the same semantics in the target database can be achieved through the functionality of the SQL statement parsing engine on the existing target database. Different databases have different technical implementation methods, which will not be elaborated here.

[0093] This application embodiment detects whether a matching string exists in the SQL statement to be matched based on preset rules. If no matching string exists, it constructs a target SQL statement executable in the target database with the same semantics as the SQL statement to be matched, based on the execution plan of the SQL statement to be matched in the source database, or the execution plan of an approximate SQL statement associated with the SQL statement to be matched in the source database. This application embodiment constructs a target SQL statement executable in the target database with the same semantics as the SQL statement to be matched, based on the execution plan of the SQL statement to be matched in the source database or the execution plan of an approximate SQL statement. This application embodiment enables the migration of SQL statements from heterogeneous databases, thus achieving application migration between databases with different syntaxes without modifying the application, thereby greatly improving application migration efficiency. Furthermore, this application embodiment eliminates the need for applications to maintain multiple code versions to support multiple databases, reducing code development and maintenance costs.

[0094] For other aspects of this application, please refer to Figure 5 Step 200, the step of detecting whether a matchable string exists in the SQL statement to be matched based on preset rules, includes:

[0095] Repeat the following operations until the preset time threshold and / or preset number of times threshold is reached:

[0096] Step 220: Calculate the hash value of the source string in the SQL statement to be matched, and check whether the hash value matches the hash value of the hash table; the hash table includes multiple target strings stored in the target database and the hash value corresponding to each target string; if the hash value matches the hash value of the hash table, determine that the source string is a matchable string;

[0097] It should be noted that the target database pre-stores a hash table. The hash table includes multiple target strings and their corresponding hash values. For example, the hash value for the string KEYWORDS is 43F42929.

[0098] By matching the hash value of the source string in the SQL statement to be matched with the hash value of the hash table, matching strings in the SQL statement can be found and replaced. For example, when the SQL statement in the source database is as follows:

[0099] select keywords from table_name;

[0100] The electronic device calculates the hash value of the source string "keywords" as 43F42929. This hash value 43F42929 is then matched against hash values ​​in the hash table. If the hash value of the source string "keywords" matches the hash value of the string "KEYWORDS" in the hash table, then the source string "keywords" is determined to be a matchable string; the string "KEYWORDS" is the target string.

[0101] In one embodiment, after determining that the source string is a matchable string if the hash value matches the hash value of the hash table, the method further includes:

[0102] Step 230: Convert the matchable string into a target string that matches the hash value in the hash table;

[0103] The electronic device matches the hash value 43F42929 with hash values ​​in the hash table. It finds that the source string "keywords" matches the hash value of the string "KEYWORDS" in the hash table. At this point, the source string "keywords" is determined to be a matchable string; the string "KEYWORDS" is the target string. The electronic device then performs a string replacement operation, replacing the source string "keywords" in the input SQL statement with the target string "KEYWORDS".

[0104] Understandably, when the hash value of the source string in the SQL statement to be matched does not match the hash value of the hash table, it means that there is no matching string in the SQL statement that conforms to the simple rules. Then, a more complex rule matching process is performed.

[0105] Step 240: Detect whether the source string of the SQL statement to be matched matches the regular expression in the regular expression set; the regular expression set includes various regular expressions stored in the target database; if the source string matches the regular expression, determine that the source string is a matchable string.

[0106] The target database stores a set of regular expressions. This set includes various regular expressions stored in the target database. The electronic device detects whether the source string of the SQL statement to be matched matches any of the regular expressions in the set; if the source string matches the regular expressions, it determines that the source string is a matchable string.

[0107] Specifically, the target database can set priorities for the regular expressions in the regular expression set. During matching, the source string of the SQL statement to be matched is matched with the regular expressions in the regular expression set in descending order of priority.

[0108] For example, taking the SQL statement `select keywords from table_name` as an example, the source string in the SQL statement is `keywords`, and the target database contains a regular expression for `keywords`. In this case, the source string `keywords` of the SQL statement matches the regular expression `keywords` in the regular expression set, thus determining that the source string `keywords` is a matchable string.

[0109] In one embodiment, after determining that the source string is a matchable string if it conforms to the regular expression, the method further includes:

[0110] Step 250: Convert the matchable string into a target string that matches the preset rules of the regular expression.

[0111] The electronic device converts the matchable string into a target string that matches the preset rules of the regular expression. For example, the preset rule for the regular expression `keywords` is to convert it to uppercase English letters. When the source string `keywords` of the SQL statement to be matched matches the regular expression `keywords` in the regular expression set, the electronic device converts the matchable string `keywords` into the target string `KEYWORDS` that matches the preset rules of the regular expression. Therefore, the converted target statement is `select KEYWORDS from table_name`.

[0112] Furthermore, to prevent omissions in the step of detecting whether a matchable string exists in the SQL statement to be matched, this embodiment performs steps 220 and 240 repeatedly until a preset time threshold and / or a preset number of times threshold is obtained. The preset time threshold and / or preset number of times threshold can be set according to actual conditions, and this embodiment does not limit the specific values ​​of the preset time threshold and preset number of times threshold.

[0113] For other aspects of the embodiments of this application, please refer to Figure 6 Before step 220, which involves calculating the hash value of the source string in the SQL statement to be matched and matching the hash value with a hash table, the method further includes:

[0114] Step 210: Normalize the SQL statement to be matched. Normalizing the SQL statement to be matched includes at least one of the following methods: deleting comments from the SQL statement to be matched; deleting spaces from the SQL statement to be matched; deleting newline characters from the SQL statement to be matched.

[0115] Before calculating the hash value of the source string of the SQL statement to be matched, the electronic device performs normalization processing on the SQL statement to be matched, that is, removes at least one meaningless character from comments, spaces and newlines in the SQL statement to be matched, thereby reducing the interference of meaningless characters in the SQL statement to be matched, thereby reducing the amount of computation, improving the detection accuracy of the hash value of the source string of the SQL statement to be matched, and improving the detection efficiency of the matchable strings in the SQL statement to be matched.

[0116] In summary, the execution process of step 200 can be found in [reference needed]. Figure 7 As shown, Figure 7 This illustrates the execution process of step 200 in one embodiment.

[0117] In other aspects of the embodiments of this application, after step 320, which involves constructing a target SQL statement executable by the target database with the same semantics as the SQL statement to be matched based on the first execution plan tree; or after step 350, which involves constructing a target SQL statement executable by the target database with the same semantics as the SQL statement to be matched based on the second execution plan tree, the method further includes:

[0118] Step 360: Record the execution plan and execution results of the approximate SQL statement for verification.

[0119] After the electronic device constructs an executable target SQL statement for the target database that has the same semantics as the SQL statement to be matched, based on an approximate SQL statement, the target SQL statement is executed in the target database. By recording the execution plan and execution results of the approximate SQL statement, it is beneficial for business personnel and database-related personnel to manually verify the correctness of the execution results of the approximate SQL statement.

[0120] It should be noted that the electronic device can continuously receive SQL statement requests from the source database application to be matched. After receiving the request, it executes the steps of the heterogeneous database SQL statement adaptation method of this application embodiment. The preprocessed SQL statement to be matched is sent to other modules of the electronic device for further processing, and the result is returned to the source database.

[0121] The heterogeneous database SQL statement adaptation device provided in the embodiments of this application is described below. The heterogeneous database SQL statement adaptation device described below and the heterogeneous database SQL statement adaptation method described above can be referred to in correspondence.

[0122] Please refer to Figure 8 This application provides a heterogeneous database SQL statement adaptation device, including:

[0123] The acquisition module 201 is used to acquire the SQL statement to be matched sent by the source database;

[0124] Detection module 202 is used to detect whether there is a matchable string in the SQL statement to be matched based on preset rules;

[0125] The adaptation module 203 is used to construct a target SQL statement that is semantically identical to the target SQL statement when there is no matching string in the SQL statement to be matched, based on the execution plan corresponding to the SQL statement to be matched in the source database, or the execution plan of an approximate SQL statement associated with the SQL statement to be matched in the source database.

[0126] The heterogeneous database SQL statement adaptation device provided in this application embodiment detects whether a matching string exists in the SQL statement to be matched based on preset rules. If no matching string exists, it constructs a target SQL statement executable in the target database with the same semantics as the SQL statement to be matched, based on the execution plan of the SQL statement to be matched in the source database, or the execution plan of an approximate SQL statement associated with the SQL statement to be matched in the source database. This application embodiment constructs a target SQL statement executable in the target database with the same semantics as the SQL statement to be matched, based on the execution plan of the SQL statement to be matched in the source database or the execution plan of an approximate SQL statement. This application embodiment enables the migration of SQL statements from heterogeneous databases, thus achieving application migration between different syntax databases without modifying the application, thereby greatly improving application migration efficiency. Furthermore, this application embodiment eliminates the need for applications to maintain multiple code versions to support multiple databases, reducing code development and maintenance costs.

[0127] In one embodiment, the detection module is configured to repeatedly execute the following module until a preset time threshold and / or a preset number of times threshold is reached:

[0128] The first rule matching module is used to calculate the hash value of the source string in the SQL statement to be matched, and to detect whether the hash value matches the hash value of the hash table; the hash table includes multiple target strings stored in the target database and the hash value corresponding to each target string; if the hash value matches the hash value of the hash table, the source string is determined to be a matchable string;

[0129] The second rule matching module is used to detect whether the source string of the SQL statement to be matched matches the regular expressions in the regular expression set; the regular expression set includes various regular expressions stored in the target database; if the source string matches the regular expression, the source string is determined to be a matchable string.

[0130] In one embodiment, the detection module further includes:

[0131] Normalization processing is used to normalize the SQL statement to be matched;

[0132] The normalization process of the SQL statement to be matched includes at least one of the following methods:

[0133] Remove the comments from the SQL statement to be matched;

[0134] Remove spaces from the SQL statement to be matched;

[0135] Remove newline characters from the SQL statement to be matched.

[0136] In one embodiment, the adapter module includes:

[0137] The first execution plan tree construction module is used to construct a first execution plan tree in the target database based on the execution plan corresponding to the SQL statement to be matched in the source database.

[0138] The target SQL statement construction module is used to construct a target SQL statement that is semantically identical to the target database executable statement based on the first execution plan tree.

[0139] In one embodiment, the adapter module further includes

[0140] The approximate SQL statement determination module is used to determine, based on semantic analysis methods, an approximate SQL statement associated with the SQL statement to be matched in the source database;

[0141] The second execution plan tree construction module is used to construct a second execution plan tree in the target database based on the execution plan corresponding to the approximate SQL statement.

[0142] The target SQL statement construction module is used to construct a target SQL statement that is semantically identical to the target database executable statement based on the second execution plan tree.

[0143] In one embodiment, the detection module further includes:

[0144] The first conversion module is used to convert the matching string into a target string that matches the hash value in the hash table when the hash value matches the hash value in the hash table.

[0145] The second conversion module is used to convert the matchable string into a target string that matches the preset rules of the regular expression, provided that the source string conforms to the regular expression.

[0146] Figure 9 An example is a schematic diagram of the physical structure of an electronic device, such as... Figure 9 As shown, the electronic device may include a processor 910, a communication interface 920, a memory 930, and a communication bus 940, wherein the processor 910, the communication interface 920, and the memory 930 communicate with each other through the communication bus 940. The processor 910 can call a computer program in the memory 930 to execute the steps of a heterogeneous database SQL statement adaptation method, such as: obtaining the SQL statement to be matched sent by the source database; detecting whether there is a matching string in the SQL statement to be matched based on preset rules; if there is no matching string in the SQL statement to be matched, constructing a target SQL statement executable by the target database with the same semantics as the SQL statement to be matched, based on the execution plan corresponding to the SQL statement to be matched in the source database, or the execution plan of an approximate SQL statement associated with the SQL statement to be matched in the source database.

[0147] Furthermore, the logical instructions in the aforementioned memory 930 can be implemented as software functional units and, when sold or used as independent products, can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, or a part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of this application. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.

[0148] On the other hand, this application also provides a computer program product, which includes a computer program that can be stored on a non-transitory computer-readable storage medium. When the computer program is executed by a processor, the computer can execute the steps of the heterogeneous database SQL statement adaptation method provided in the above embodiments, such as: obtaining the SQL statement to be matched sent by the source database; detecting whether there is a matching string in the SQL statement to be matched based on preset rules; and, if there is no matching string in the SQL statement to be matched, constructing a target SQL statement that is semantically identical to the SQL statement to be matched, based on the execution plan corresponding to the SQL statement to be matched in the source database, or the execution plan of an approximate SQL statement associated with the SQL statement to be matched in the source database.

[0149] On the other hand, embodiments of this application also provide a processor-readable storage medium storing a computer program for causing a processor to execute the steps of the methods provided in the above embodiments, such as: obtaining a SQL statement to be matched sent by a source database; detecting whether a matching string exists in the SQL statement to be matched based on preset rules; and, if no matching string exists in the SQL statement to be matched, constructing a target SQL statement executable by a target database that has the same semantics as the SQL statement to be matched, based on the execution plan corresponding to the SQL statement to be matched in the source database, or the execution plan of an approximate SQL statement associated with the SQL statement to be matched in the source database.

[0150] The processor-readable storage medium can be any available medium or data storage device that the processor can access, including but not limited to magnetic memory (e.g., floppy disk, hard disk, magnetic tape, magneto-optical disk (MO)), optical memory (e.g., CD, DVD, BD, HVD), and semiconductor memory (e.g., ROM, EPROM, EEPROM, non-volatile memory (NAND FLASH), solid-state drive (SSD)).

[0151] The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs. Those skilled in the art can understand and implement this without any creative effort.

[0152] Through the above description of the embodiments, those skilled in the art can clearly understand that each embodiment can be implemented by means of software plus necessary general-purpose hardware platforms, and of course, it can also be implemented by hardware. Based on this understanding, the above technical solutions, in essence or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute the methods described in the various embodiments or some parts of the embodiments.

[0153] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of this application, and are not intended to limit them. Although this application has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of this application.

Claims

1. A method for adapting SQL statements to heterogeneous databases, characterized in that, include: Retrieve the SQL statement to be matched sent by the source database; Based on preset rules, detect whether there is a matchable string in the SQL statement to be matched; If no matching string exists in the SQL statement to be matched, a target SQL statement that is semantically identical to the SQL statement to be matched is constructed in the target database based on the execution plan corresponding to the SQL statement to be matched in the source database, or the execution plan of an approximate SQL statement associated with the SQL statement to be matched in the source database; the execution plan is retrieved from the system tables and memory of the source database. The step of constructing an executable target SQL statement in the target database that has the same semantics as the SQL statement to be matched, based on the execution plan corresponding to the SQL statement to be matched in the source database, includes: Based on the execution plan corresponding to the SQL statement to be matched in the source database, construct the first execution plan tree in the target database; Based on the first execution plan tree, construct the target SQL statement that is semantically identical to the SQL statement to be matched and can be executed in the target database.

2. The heterogeneous database SQL statement adaptation method according to claim 1, characterized in that, The step of detecting whether a matchable string exists in the SQL statement to be matched based on preset rules includes: Repeat the following operations until the preset time threshold and / or preset number of times threshold is reached: Calculate the hash value of the source string in the SQL statement to be matched, and check whether the hash value matches the hash value of the hash table; the hash table includes multiple target strings stored in the target database and the hash value corresponding to each target string; If the hash value matches the hash value of the hash table, the source string is determined to be a matchable string; The system detects whether the source string of the SQL statement to be matched matches the regular expressions in the set of regular expressions; the set of regular expressions includes various regular expressions stored in the target database. If the source string matches the regular expression, the source string is determined to be a matchable string.

3. The heterogeneous database SQL statement adaptation method according to claim 2, characterized in that, Before the step of calculating the hash value of the source string in the SQL statement to be matched and matching the hash value with the hash table, the method further includes: The SQL statement to be matched is then normalized. The normalization process of the SQL statement to be matched includes at least one of the following methods: Remove the comments from the SQL statement to be matched; Remove spaces from the SQL statement to be matched; Remove newline characters from the SQL statement to be matched.

4. The heterogeneous database SQL statement adaptation method according to claim 1, characterized in that, The step of constructing an executable target SQL statement in the target database that has the same semantics as the SQL statement to be matched, based on the execution plan of the approximate SQL statement associated with the SQL statement to be matched in the source database, includes: Based on semantic analysis methods, an approximate SQL statement is determined that is associated with the SQL statement to be matched in the source database; Based on the execution plan corresponding to the approximate SQL statement, a second execution plan tree is constructed in the target database; Based on the second execution plan tree, construct the target SQL statement that is semantically identical to the SQL statement to be matched and can be executed in the target database.

5. The heterogeneous database SQL statement adaptation method according to claim 2, characterized in that, After determining that the source string is a matchable string when the hash value matches the hash value of the hash table, the method further includes: The matching string is converted into a target string that matches the hash value in the hash table; After determining that the source string is a matchable string if it matches the regular expression, the process further includes: The matchable string is converted into a target string that matches the preset rules of the regular expression.

6. A heterogeneous database SQL statement adaptation device, characterized in that, include: The acquisition module is used to acquire the SQL statements to be matched sent by the source database; The detection module is used to detect whether there is a matchable string in the SQL statement to be matched based on preset rules; An adaptation module is used to construct a target SQL statement that is semantically identical to the SQL statement to be matched, based on the execution plan corresponding to the SQL statement to be matched in the source database, or the execution plan of an approximate SQL statement associated with the SQL statement to be matched in the source database, when no matching string exists in the SQL statement to be matched; the execution plan is retrieved from the system tables and memory of the source database. The adaptation module is further configured to construct a first execution plan tree in the target database based on the execution plan corresponding to the SQL statement to be matched in the source database; and to construct a target SQL statement that is semantically identical to the SQL statement to be matched in the target database based on the first execution plan tree.

7. An electronic device comprising a processor and a memory storing a computer program, characterized in that, When the processor executes the computer program, it implements the steps of the heterogeneous database SQL statement adaptation method according to any one of claims 1 to 5.

8. A non-transitory computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by the processor, it implements the steps of the heterogeneous database SQL statement adaptation method as described in any one of claims 1 to 5.

9. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by the processor, it implements the steps of the heterogeneous database SQL statement adaptation method according to any one of claims 1 to 5.

Images