Cross-version SQL version management method and device and storage medium

Abstract

The invention relates to a cross-version SQL version management method and device and a storage medium, the method uses an SQL version table, a distributed lock table and a data source table to perform SQL version management, and the method comprises the following steps: adding an SQL file to a Java configuration file directory to record an XML file, and independently setting an SQL file storage for each version; performing data source dynamic loading according to a specified SQL file in the XML configuration file and the data source connection information in the corresponding data source table; comparing and checking the version number and the MD5 value of the SQL file with the execution record in the SQL version table; and executing the SQL file passing the verification, and storing the execution record of the executed SQL file in an SQL version table. Compared with the prior art, dynamic multi-data source connection is supported, management is carried out through the SQL file, the method is more fit with development, and the development efficiency is improved.

Description

Technical Field

[0001] The present invention relates to the technical field of computer program application development, and in particular to a cross-version SQL version management method and system. Background Art

[0002] Routine software upgrades and iterations often involve SQL statement version upgrades. These releases often involve numerous SQL statements, used to upgrade existing versions and update database table structures and data. Manual SQL execution is tedious and prone to uncontrollable factors, such as missed executions, duplicate executions, and incorrect executions. Furthermore, due to a lack of version management records, the relationship between the current database table structure and the software version is unclear, leading to a variety of uncontrollable issues. Summary of the Invention

[0003] The purpose of the present invention is to overcome the defects of the above-mentioned prior art and provide a cross-version SQL version management method and system.

[0004] The purpose of the present invention can be achieved by the following technical solutions:

[0005] As a first aspect of the present invention, a cross-version SQL version management method is provided. The method uses an SQL version table, a distributed lock table, and a data source table to perform SQL version management. The steps include:

[0006] Add SQL files to record XML configuration files in the Java configuration file directory, and set up a separate SQL file storage for each version;

[0007] Dynamically load the data source according to the SQL file specified in the XML configuration file and the data source connection information in the corresponding data source table;

[0008] Compare and verify the version number and MD5 value of the SQL file with the execution record in the SQL version table;

[0009] Execute the SQL files that have passed the verification, and save the execution records of the executed SQL files in the SQL version table.

[0010] As a preferred technical solution, the data source is dynamically loaded, specifically as follows: the main data source is specified in the XML configuration file, and multiple specified data sources are connected through the data source table specified by the main data source. Each specified data source includes an SQL version table and a distributed lock table for performing SQL version management on each data source.

[0011] As an optimal technical solution, a thread pool is enabled for multi-data source projects, and multi-threading is enabled to concurrently manage SQL versions of multiple data sources. After each data source version is upgraded, the data source connection is automatically recycled.

[0012] As a preferred technical solution, the content of the data source table includes: unique ID, data source link DB_URL, data source user name DB_USERNAME, data source password DB_PASSWORD and database name DATASOURCE_NAME.

[0013] As a preferred technical solution, the content of the SQL version table includes: unique ID, SQL author name AUTHOR, SQL file name FILENAME, SQL execution time EXECUTEDTIME, SQL file MD5 value MD5VALUE and SQL file description DESC.

[0014] As a preferred technical solution, the version number and MD5 value of the SQL file are compared and verified with the execution record in the SQL version table as follows:

[0015] When the code starts, it reads the XML file and compares it with the SQL version table in order. It matches the SQL file name to be executed recorded in the XML file with the SQL file name field in the SQL version table.

[0016] If the SQL file name field in the SQL version table does not match, the SQL file is executed directly;

[0017] If a match is found in the SQL file name field in the SQL version table, the MD5 value of the SQL file in the SQL version table is compared with the MD5 value of the current file.

[0018] If the MD5 value of the SQL file in the SQL version table is consistent with the MD5 value of the current file, skip the file directly;

[0019] If the MD5 value of the SQL file in the SQL version table is different from the MD5 value of the current file, the log is printed on the console to ensure that the released SQL version cannot be changed.

[0020] As a preferred technical solution, the content of the distributed lock table includes: a unique ID, whether the SQL file is locked (LOCKED), and a lock time (LOCKTIME).

[0021] As a preferred technical solution, after the first service completes the connection with the data source, a piece of data is added to the distributed lock table, and the LOCKED value in the distributed lock table is set to true to indicate whether the SQL file is locked. After service A is executed, the corresponding data in the distributed lock table is deleted.

[0022] If a second service requests to connect to a data source while a first service is connected to the data source, the data source connection logic corresponding to the second service is executed after the first service data in the distributed lock table is deleted.

[0023] As a second aspect of the present invention, there is provided an electronic device, comprising:

[0024] one or more processors;

[0025] a memory for storing one or more programs;

[0026] When the one or more programs are executed by the one or more processors, the one or more processors implement the cross-version SQL version management method as described above.

[0027] As a third aspect of the present invention, a computer-readable storage medium is provided, wherein the computer-readable storage medium stores a computer program, wherein when the computer program is executed by a processor, the steps of the cross-version SQL version management method are implemented.

[0028] Compared with the prior art, the present invention has the following beneficial effects:

[0029] 1) This invention manages SQL versions by setting up an SQL version table and a distributed lock table. The SQL file's version number and MD5 value are verified against the records in the SQL version table to ensure that previously executed SQL will not be executed again. Executed SQL can also be viewed in the SQL version table. Managing SQL changes through SQL files is more aligned with development, improving development efficiency.

[0030] 2) The present invention supports dynamic multi-data source connection by setting a data source table, can manage the SQL versions of multiple data sources, and enables multi-threading to concurrently manage the SQL versions of multiple data sources, thereby improving the speed. BRIEF DESCRIPTION OF THE DRAWINGS

[0031] Figure 1 This is a flow chart of the cross-version SQL version management method of the present invention. DETAILED DESCRIPTION

[0032] The present invention is described in detail below with reference to the accompanying drawings and specific embodiments. This embodiment is implemented based on the technical solution of the present invention, and provides a detailed implementation method and specific operation process, but the protection scope of the present invention is not limited to the following embodiments.

[0033] Example 1

[0034] This solution aims to manage SQL versions across multiple databases and proposes a cross-version SQL version management method. It builds a SQL version table and a distributed lock table to manage SQL versions, upgrades, and execution errors. The SQL version table, distributed lock table, and data source table are specifically constructed as follows:

[0035] SQL version table, table name [DATABASE_VERSION], includes: 1. ID: unique ID; 2. AUTHOR: SQL author name; 3. FILENAME: SQL file name; 4. EXECUTEDTIME: SQL execution time; 5. MD5VALUE: SQL file MD5 value; 6. DESC: SQL file description.

[0036] Distributed lock table, table name [DATABASE_VERSION_LOCK], includes: 1. ID: unique ID; 2. LOCKED: whether the SQL file is locked; 3. LOCKTIME: lock time.

[0037] The data source table, named [TENANT_DATABASE], includes the following: 1. ID: unique ID; 2. DB_URL: data source link; 3. DB_USERNAME: data source user name; 4. DB_PASSWORD: data source password; 5. DATASOURCE_NAME: database name.

[0038] The specific process of SQL version management based on the above SQL version table and distributed lock table is as follows:

[0039] Use SQL files to manage version changes. Add an SQL file record XML file to the Java configuration file directory, storing a separate SQL file for each version. When the code starts, read this XML file and compare it sequentially with the SQL version table. The SQL file name to be executed, recorded in the XML file, is matched against the SQL file name FILENAME field in the SQL version table. If there is no match, it indicates that a new SQL file is being executed directly. If there is a match, the MD5 value is compared with the MD5 value of the current file. If they match, the file is skipped. If they differ, a log is printed to the console to ensure that the released SQL version cannot be changed, which is more secure.

[0040] After service A completes the connection with the data source, a piece of data is added to the distributed lock table and the LOCKED value in the distributed lock table is set to true, indicating whether the SQL file is locked. After service A is executed, the corresponding data in the distributed lock table is deleted. If service B connects to the data source at the same time as service A, the data source connection logic corresponding to service B is executed after the service A data in the distributed lock table is deleted.

[0041] The configuration file specifies the primary data source. Multiple specified data sources are connected through the data source connection information table specified by the primary data source. Each specified data source includes an SQL version table and a distributed lock table. Through the logical processing in the previous step, SQL version management is performed for each data source.

[0042] Through the management method proposed in this invention, third-party projects only need to introduce the dependency or directly use this project to start the SQL version to clearly manage the SQL version, which is convenient and fast to use and improves efficiency.

[0043] Specifically, the tables constructed in the present invention are as follows:

[0044] For projects with multiple data sources, we enable thread pooling and multi-threading to concurrently manage SQL versions for multiple data sources to improve speed. After each data source version upgrade is complete, the data source connection is automatically recycled to reduce database resource usage.

[0045] like Figure 1 As shown, the steps of this method specifically include:

[0046] Step 1: Dynamically load the data source according to the SQL file specified in the XML configuration file and the data source connection information in the corresponding data source table;

[0047] Step 2: Dynamically load the multi-threaded data source according to the loaded configuration file.

[0048] Step 3: Perform MD5 verification on the SQL file and compare the version number with the execution record.

[0049] Step 4: Execute the SQL file that has passed the verification;

[0050] Step 5: Persist the execution records of the executed SQL files.

[0051] Example 2

[0052] As a second aspect of the present invention, the present application further provides an electronic device comprising: one or more processors; a memory for storing one or more programs; and when the one or more programs are executed by the one or more processors, the one or more processors implement the cross-version SQL version management method described above. In addition to the aforementioned processors, memory, and interfaces, any data processing device in which the apparatus in the embodiments is located may also include other hardware, typically based on the actual functionality of the device, which will not be described in detail.

[0053] Example 3

[0054] As a third aspect of the present invention, the present application also provides a computer-readable storage medium having computer instructions stored thereon, which, when executed by a processor, implement the cross-version SQL version management method as described above. The computer-readable storage medium can be an internal storage unit of any device with data processing capabilities as described in any of the aforementioned embodiments, such as a hard disk or memory. The computer-readable storage medium can also be an external storage device, such as a plug-in hard disk, a smart memory card (Smart Media Card, SMC), an SD card, a flash card (Flash Card), etc. equipped on the device. Furthermore, the computer-readable storage medium can also include both an internal storage unit and an external storage device of any device with data processing capabilities. The computer-readable storage medium is used to store the computer program and other programs and data required by any device with data processing capabilities, and can also be used to temporarily store data that has been output or is to be output.

[0055] The above describes in detail the preferred embodiments of the present invention. It should be understood that those skilled in the art can make numerous modifications and variations based on the concepts of the present invention without inventive effort. Therefore, any technical solutions that can be derived by those skilled in the art through logical analysis, reasoning, or limited experimentation based on the concepts of the present invention and the prior art should be within the scope of protection defined by the claims.

Claims

1. A cross-version SQL version management method, characterized by: The method uses an SQL version table, a distributed lock table, and a data source table to manage SQL versions, and the steps include: Add SQL files to record XML configuration files in the Java configuration file directory, and set up a separate SQL file storage for each version; Dynamically load the data source according to the SQL file specified in the XML configuration file and the data source connection information in the corresponding data source table; Compare and verify the version number and MD5 value of the SQL file with the execution record in the SQL version table; Execute the SQL files that have passed the verification, and save the execution records of the executed SQL files in the SQL version table.

2. A cross-version SQL version management method according to claim 1, characterized in that: The data source is dynamically loaded as follows: the main data source is specified in the XML configuration file, and multiple specified data sources are connected through the data source table specified by the main data source. Each specified data source includes an SQL version table and a distributed lock table for performing SQL version management on each data source.

3. A cross-version SQL version management method according to claim 2, characterized in that: For multi-data source projects, the thread pool is enabled and multi-threading is enabled to concurrently manage SQL versions of multiple data sources. After each data source version is upgraded, the data source connection is automatically recycled.

4. A cross-version SQL version management method according to claim 1, characterized in that: The content of the data source table includes: unique ID, data source link DB_URL, data source user name DB_USERNAME, data source password DB_PASSWORD and database name DATASOURCE_NAME.

5. A cross-version SQL version management method according to claim 1, characterized in that: The content of the SQL version table includes: unique ID, SQL author name AUTHOR, SQL file name FILENAME, SQL execution time EXECUTEDTIME, SQL file MD5 value MD5VALUE and SQL file description DESC.

6. A cross-version SQL version management method according to claim 5, characterized in that: The specific comparison and verification of the version number and MD5 value of the SQL file with the execution record in the SQL version table is as follows: When the code starts, it reads the XML file and compares it with the SQL version table in order. It matches the SQL file name to be executed recorded in the XML file with the SQL file name field in the SQL version table. If the SQL file name field in the SQL version table does not match, the SQL file is executed directly; If a match is found in the SQL file name field in the SQL version table, the MD5 value of the SQL file in the SQL version table is compared with the MD5 value of the current file. If the MD5 value of the SQL file in the SQL version table is consistent with the MD5 value of the current file, skip the file directly; If the MD5 value of the SQL file in the SQL version table is different from the MD5 value of the current file, the log is printed on the console to ensure that the released SQL version cannot be changed.

7. A cross-version SQL version management method according to claim 1, characterized in that: The content of the distributed lock table includes: a unique ID, whether the SQL file is locked (LOCKED), and the lock time (LOCKTIME).

8. A cross-version SQL version management method according to claim 7, characterized in that: After the first service completes the connection with the data source, a piece of data is added to the distributed lock table, and the LOCKED value in the distributed lock table, which indicates whether the SQL file is locked, is set to true. After service A is executed, the corresponding data in the distributed lock table is deleted. If a second service requests to connect to a data source while a first service is connected to the data source, the data source connection logic corresponding to the second service is executed after the first service data in the distributed lock table is deleted.

9. An electronic device, characterized in that: include: one or more processors; a memory for storing one or more programs; When the one or more programs are executed by the one or more processors, the one or more processors implement the cross-version SQL version management method according to any one of claims 1 to 8.

10. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the steps of the cross-version SQL version management method according to any one of claims 1 to 8 are implemented.

Images