Cross-database operation method

Abstract

The invention discloses a cross-database operation method, and relates to the technical field related to databases. According to the invention, a cross-database operation system is adopted; comprising the following steps: acquiring a database type, acquiring all sql grammar corresponding to the database type, acquiring an sql object in a database operation request, selecting one sql grammar with the same operation type as the sql object as an sql grammar instance, generating an sql statement, executing the sql statement in a database, and returning an operation result after executing the sql statement to a system by the database. And the system returns an operation result to the user through the database operation interface. Different types of databases can be operated through a unified database operation request mode, and the problem that the database is inconvenient to replace and upgrade is solved.

Description

Technical Field

[0001] The present invention relates to the technical field of database-related technologies, and in particular to a cross-database operation method. Background Art

[0002] In the field of software development, especially in enterprise-level application development, database operation is an indispensable part. The most common database persistence layer framework tools on the market currently provide simple statement encapsulation, but specific development still involves a large number of customized statement development.

[0003] In a large-scale database collection, such as a data center, there are usually multiple types of databases with different SQL (Structured Query Language) syntaxes. During use, some databases sometimes need to be updated and upgraded. For example, domestic databases may need to replace foreign databases. Too many types of SQL syntax may cause inconvenience to users. Summary of the invention

[0004] In view of the above problems, the present invention provides a cross-database operation method, which can operate different types of databases through a unified database operation request method, thereby solving the problem of inconvenient operation when replacing and upgrading the database.

[0005] In order to achieve the purpose of the present invention, the following scheme is adopted: A cross-database operation method adopts a cross-database operating system, which includes a SQL grammar library, a database operation interface, and a database name and type correspondence table; the SQL grammar library is used to store SQL grammar, and the SQL grammar includes database type, operation type, and SQL statement format, and the SQL statement format includes format text and a number of variable filling positions; the database operation interface is used to receive database operation requests, and the database operation request includes the database name and SQL object, and the SQL object includes the operation type and a number of operation contents; the database name and type correspondence table is used to query the database type through the database name.

[0006] The method includes the following steps when performing cross-database operations: S100: The system receives a database operation request through a database operation interface, and then obtains a database type from a database name and type correspondence table according to the database name; S200: calling the SQL grammar library to obtain all SQL grammars corresponding to the database type; S300: Obtaining the sql object in the database operation request; S400: Selecting a sql grammar having the same operation type as the sql object from all the acquired sql grammars as a sql grammar instance; S500: Obtain the SQL statement format in the grammar instance, adopt its format text, and fill in the operation contents of the SQL object into the corresponding variable filling positions to generate the SQL statement; S600: The system sends the SQL statement to the database and executes the SQL statement in the database; S700: The database returns the operation result after executing the SQL statement to the system; S800: The system returns the operation result to the user through the database operation interface.

[0007] Furthermore, the cross-database operating system also includes a grammar library configuration interface, which is used to receive a grammar library configuration interface and grammar configuration information, wherein the grammar configuration information includes a configuration type and configuration content, wherein the configuration type includes adding sql grammar, modifying sql grammar, and deleting sql grammar, and the configuration content also includes a database type, an operation type, and a sql statement format; The method also includes a configuration step when performing system configuration: T100: The system receives a grammar library configuration request through the grammar library configuration interface; T200: Authenticate the user who issued the grammar library configuration request to determine whether he is a system administrator. If not, the system returns an authentication failure message. If yes, execute T300; T300: The system receives syntax configuration information; T800: Update the SQL grammar library with the content of grammar configuration information; T900: Determine whether other configurations are required for the SQL grammar library. If so, return to T300; if not, end.

[0008] Furthermore, the cross-database operating system further includes a grammar test module, the grammar test module includes a grammar test program, and the grammar test module also stores a plurality of test libraries, and the number of test libraries of the same type does not exceed one; After T300, perform the following steps: T400: Determine the configuration type. If the configuration type is to add SQL syntax or modify SQL syntax, execute T500. If not, execute T800. T500: Determine whether the database type in the configuration content has a corresponding test library in the syntax test module. If so, directly execute T600. If not, create a test library of the same type as the database type in the configuration content in the syntax test module, and then execute T600. T600: In the corresponding test library, perform SQL syntax test on the configuration content; T700: Determine whether the SQL syntax test is passed. If not, the system prompts the user to modify the syntax configuration information and then returns to T300. If yes, execute T800.

[0009] Furthermore, in T600, the steps of SQL syntax testing include: T610: The syntax test program creates a test object based on the operation type of the configuration content. The test object includes several operation contents. T620: Get the SQL statement format from the configuration content, use its format text, and fill in the operation contents of the test object into the corresponding variable filling positions to generate a test statement; T630: The syntax test program sends the SQL statement to the test library and executes the test statement in the test library; T640: The test library returns the operation results after executing the SQL statement to the system.

[0010] Furthermore, the sql grammar also includes a grammar ID.

[0011] Furthermore, the configuration content also includes a grammar ID.

[0012] Furthermore, the variable filling position includes a position ID, and the operation content includes a content text and a content ID corresponding to the position ID. In S500, the content text corresponding to each operation content is filled in each variable filling position.

[0013] Furthermore, the tables, rows, and columns of the test library are not empty.

[0014] Furthermore, the grammar test module is also connected to the large language model. The judgment method of T700 is to convert the operation results obtained by T640 and the operation content in T610 into natural language through the large language model, and then judge whether the two are equivalent. If they are equivalent, the sql grammar test is passed; if they are not equivalent, the sql grammar test is not passed.

[0015] The beneficial effects of this technical solution are: When operating a data host or data center with multiple types of databases, the database operation interface provided by the cross-database operating system does not require input according to the syntax involved in different database types. Instead, you only need to specify the database name and provide the SQL object. With a unified form of database operation request, you can operate on different databases, solving the problem of inconvenient operation when replacing and upgrading the database. BRIEF DESCRIPTION OF THE DRAWINGS

[0016] Figure 1A flow chart showing the main steps of cross-database operations in an embodiment of the present application is shown.

[0017] Figure 2 A flow chart showing the main steps of system configuration in an embodiment of the present application is shown.

[0018] Figure 3 A flow chart showing the main steps of performing SQL syntax testing in an embodiment of the present application is shown. DETAILED DESCRIPTION

[0019] In order to make the purpose, technical solutions and advantages of the present application clearer, the implementation methods of the present application are described in detail below, but the embodiments described in this application are only part of the embodiments of the present application, rather than all the embodiments.

[0020] A cross-database operation method adopts a cross-database operating system.

[0021] The cross-database operating system includes a SQL grammar library, a database operation interface, a grammar library configuration interface, a database name and type correspondence table, and a grammar test module.

[0022] The sql syntax library is used to store sql syntax. The sql syntax includes database type, syntax ID, operation type, and sql statement format. The sql statement format includes format text and several variable filling positions. The variable filling positions include position ID.

[0023] The database operation interface is used to receive database operation requests. The database operation request includes the database name and the sql object. The sql object includes the operation type and several operation contents. The operation content includes the content text and the content ID corresponding to the position ID. The content ID is used to correspond each operation content to each variable fill-in position when the number of operation contents of the sql object is greater than one.

[0024] The syntax library configuration interface is used to receive the syntax library configuration interface and syntax configuration information. The syntax configuration information includes configuration type and configuration content. The configuration type includes adding SQL syntax, modifying SQL syntax, and deleting SQL syntax. The configuration content also includes database type, syntax ID, operation type, and SQL statement format.

[0025] The database name and type correspondence table is used to query the database type through the database name.

[0026] The grammar test module includes a grammar test program, and the grammar test module also stores a plurality of test libraries, and the number of test libraries of the same type does not exceed one. Specifically, the tables, rows, and columns of the preset test library are not empty, so that when the sql grammar test is performed later, if the operation type of the sql grammar is to delete or query data, there is data in the test library that can be used for testing.

[0027] In this embodiment, the database types of each test library are different, but a data table with the same content is preset in each of them, and the table content is: serial number Name Department level 001 Zhang San Internal Medicine Physician 002 Li Si surgical director 003 Wang Wu surgical Physician like Figure 1 As shown, the cross-database operation method includes the following steps when performing a cross-database operation: S100: The system receives a database operation request through a database operation interface, and then obtains a database type from a database name and type correspondence table according to the database name; S200: calling the SQL grammar library to obtain all SQL grammars corresponding to the database type; S300: Obtaining the sql object in the database operation request; S400: Selecting a sql grammar having the same operation type as the sql object from all the acquired sql grammars as a sql grammar instance; S500: Obtain the SQL statement format in the grammar instance, adopt its format text, and fill in each operation content of the SQL object into each corresponding variable filling position to generate a SQL statement. Specifically, fill in the content text corresponding to each operation content in each variable filling position; S600: The system sends the SQL statement to the database and executes the SQL statement in the database; S700: The database returns the operation result after executing the SQL statement to the system; S800: The system returns the operation result to the user through the database operation interface.

[0028] like Figure 2 As shown, the cross-database operation method also includes configuration steps when performing system configuration: T100: The system receives a grammar library configuration request through the grammar library configuration interface; T200: Authenticate the user who issued the grammar library configuration request to determine whether he is a system administrator. If not, the system returns an authentication failure message. If yes, execute T300; T300: The system receives syntax configuration information; T400: Determine the configuration type. If the configuration type is to add SQL syntax or modify SQL syntax, execute T500. If not, execute T800. T500: Determine whether the database type in the configuration content has a corresponding test library in the syntax test module. If so, directly execute T600. If not, create a test library of the same type as the database type in the configuration content in the syntax test module, and then execute T600. T600: In the corresponding test library, perform SQL syntax test on the configuration content; Specifically, Figure 3 As shown, in T600, the steps of SQL syntax testing include: T610: The syntax test program creates a test object based on the operation type of the configuration content. The test object includes several operation contents. Take the data table of the test library in this embodiment as an example: if the operation type is "add", the content text of the operation content is "004", "Zhao Liu", "surgery", and "physician", and these four operation contents have content IDs corresponding to the position ID respectively; if the operation type is "delete", there may be only one operation content, and its content text is "002", and it also has a content ID. If the sql syntax is correct when it is executed later, a row of data numbered 002 will be deleted; if the operation type is "modify", the content text of the operation content is "002", "name", and "Zhao Liu", and the operation content also has a content ID respectively. If the sql syntax is correct when it is executed later, "Li Si" in the data table will be changed to "Zhao Liu"; if the operation type is "search", the content text of the operation content is "level" and "physician", and the operation content also has a content ID respectively. If the sql syntax is correct when it is executed later, all rows of data with the level of physician will be queried in the data table; The above example is just one example. The specific operation content can be manually configured by the administrator user, or it can be automatically and randomly generated by the syntax test program in real time. T620: Get the SQL statement format from the configuration content, use its format text, and fill in the operation contents of the test object into the corresponding variable filling positions to generate a test statement; T630: The syntax test program sends the SQL statement to the test library and executes the test statement in the test library; T640: The test library returns the operation result after executing the SQL statement to the system; Preferably, the test library is reset after each SQL syntax test; T700: Determine whether the SQL syntax test is passed. If not, the system prompts the user to modify the syntax configuration information and then returns to T300. If yes, execute T800; Specifically, the grammar test module is also connected to large language models (LLMs), such as ChatGPT, GPT-4, Gemini, PaLM 2, and Llama 2. The judgment method of T700 is to convert the operation results obtained by T640 and the operation content in T610 into natural language through the large language model, and then judge whether the two are equivalent. If they are equivalent, the sql grammar test is passed, and if they are not equivalent, the sql grammar test is not passed; T800: Update the SQL grammar library with the content of grammar configuration information; T900: Determine whether other configurations are required for the SQL grammar library. If so, return to T300; if not, end.

[0029] Through the syntax library configuration function mentioned above, some databases that were launched later can be manually configured. For example, some documents are not yet complete and cannot be directly imported into the database of the data center.

[0030] In addition, when configuring the syntax of these databases, you can also perform tests to ensure that the SQL syntax is available.

[0031] The above are only some of the embodiments listed in this application and are not intended to limit this application.

Claims

1. A cross-database operation method, characterized in that: A cross-database operating system is used, which includes a sql grammar library, a database operation interface, and a database name and type correspondence table; the sql grammar library is used to store sql grammar, which includes database type, operation type, and sql statement format, and the sql statement format includes format text and several variable filling positions; the database operation interface is used to receive database operation requests, and the database operation request includes the database name and sql object, and the sql object includes the operation type and several operation contents; the database name and type correspondence table is used to query the database type through the database name; The method includes the following steps when performing cross-database operations: S100: The system receives a database operation request through a database operation interface, and then obtains a database type from a database name and type correspondence table according to the database name; S200: calling the SQL grammar library to obtain all SQL grammars corresponding to the database type; S300: Obtaining the sql object in the database operation request; S400: Selecting a sql grammar having the same operation type as the sql object from all the acquired sql grammars as a sql grammar instance; S500: Obtain the SQL statement format in the grammar instance, adopt its format text, and fill in the operation contents of the SQL object into the corresponding variable filling positions to generate the SQL statement; S600: The system sends the SQL statement to the database and executes the SQL statement in the database; S700: The database returns the operation result after executing the SQL statement to the system; S800: The system returns the operation result to the user through the database operation interface.

2. The cross-database operation method according to claim 1, characterized in that: The cross-database operating system also includes a grammar library configuration interface, which is used to receive a grammar library configuration interface and grammar configuration information. The grammar configuration information includes a configuration type and configuration content. The configuration type includes adding SQL grammar, modifying SQL grammar, and deleting SQL grammar. The configuration content also includes a database type, an operation type, and a SQL statement format. The method also includes a configuration step when performing system configuration: T100: The system receives a grammar library configuration request through the grammar library configuration interface; T200: Authenticate the user who issued the grammar library configuration request to determine whether he is a system administrator. If not, the system returns an authentication failure message. If yes, execute T300; T300: The system receives syntax configuration information; T800: Update the SQL grammar library with the content of grammar configuration information; T900: Determine whether other configurations are required for the SQL grammar library. If so, return to T300; if not, end.

3. The cross-database operation method according to claim 2, characterized in that: The cross-database operating system further includes a grammar test module, the grammar test module includes a grammar test program, and the grammar test module also stores a plurality of test libraries, and the number of test libraries of the same type does not exceed one; After T300, perform the following steps: T400: Determine the configuration type. If the configuration type is to add SQL syntax or modify SQL syntax, execute T500. If not, execute T800. T500: Determine whether the database type in the configuration content has a corresponding test library in the syntax test module. If so, directly execute T600. If not, create a test library of the same type as the database type in the configuration content in the syntax test module, and then execute T600. T600: In the corresponding test library, perform SQL syntax test on the configuration content; T700: Determine whether the SQL syntax test is passed. If not, the system prompts the user to modify the syntax configuration information and then returns to T300. If yes, execute T800.

4. The cross-database operation method according to claim 3, characterized in that: In T600, the steps of SQL syntax testing include: T610: The syntax test program creates a test object based on the operation type of the configuration content. The test object includes several operation contents. T620: Get the SQL statement format from the configuration content, use its format text, and fill in the operation contents of the test object into the corresponding variable filling positions to generate a test statement; T630: The syntax test program sends the SQL statement to the test library and executes the test statement in the test library; T640: The test library returns the operation results after executing the SQL statement to the system.

5. The cross-database operation method according to claim 1, characterized in that: The sql grammar also includes a grammar ID.

6. The cross-database operation method according to claim 2, characterized in that: The configuration content also includes a syntax ID.

7. The cross-database operation method according to claim 1, characterized in that: The variable filling position includes the position ID, and the operation content includes the content text and the content ID corresponding to the position ID. In S500, the content text corresponding to each operation content is filled in each variable filling position.

8. The cross-database operation method according to claim 3, characterized in that: The tables, rows and columns of the test library are not empty.

9. The cross-database operation method according to claim 4, characterized in that: The grammar test module is also connected to the large language model. The judgment method of T700 is to convert the operation results obtained by T640 and the operation content in T610 into natural language through the large language model, and then judge whether the two are equivalent. If they are equivalent, the SQL grammar test is passed. If they are not equivalent, the SQL grammar test is not passed.

Images