A multi-database automatic upgrading method of a multi-service system

Abstract

The application discloses a kind of multi-database automatic upgrade methods of multi-service system, belong to computer software technical field, in view of the problem that multi-service system multi-database environment based on same set of infrastructure is upgraded script confused, manual execution is prone to error, the system information table of the system version number and system name is set in each service system database in the method, listens to data source initialization event trigger upgrade, first automatically identify current system name and database environment when upgrading, secondly match the upgrade script of corresponding service system and database type and execute, finally upgrade success then update system version number, fail then record alarm information;The application realizes the automatic accurate upgrade of multi-service system multi-database environment, avoids manual operation error, guarantees data consistency, improves operation efficiency, provides reliable scheme for the database upgrade of multi-service system.

Description

Technical Field

[0001] This invention relates to the field of computer software technology, and more specifically to a method for automatic upgrade of multiple databases in a multi-service system. Background Technology

[0002] As enterprises deepen their digital transformation, multiple business systems have become the core support for enterprise operations and management. To reduce the maintenance costs of basic functions such as user management, access control, and logging, enterprises typically adopt a model where a single infrastructure codebase is compatible with multiple business systems. Basic functional modules are shared, and differentiated functional modules are developed and used only for different business needs.

[0003] Meanwhile, different business systems may need to be adapted to different database environments to meet performance, cost, or compliance requirements.

[0004] However, when the system needs to be upgraded, it usually requires an upgrade to the database as well. For example, it may be necessary to optimize basic functions or expand business modules. The architecture of a single codebase for multiple business systems and multiple databases brings the following prominent problems: 1. Chaotic upgrade script management: Upgrade scripts from different business systems are stored together with scripts from different database environments. Each system's upgrade script cannot be executed directly, and direct execution may result in errors, or an upgrade script from one system may affect the upgrades of other systems.

[0005] 2. High risk of manual execution: The upgrade process requires maintenance personnel to manually select, upload and execute scripts, which is prone to omissions or errors, leading to upgrade failure and affecting business operations.

[0006] 3. Data consistency issues: If the upgrade operation is triggered after the business code is executed, the upgraded database structure or data may be inconsistent with the old data in the cache, resulting in errors in the business logic.

[0007] 4. Lack of traceability: After an upgrade fails, it is impossible to quickly locate the cause of the failure, or to determine whether the error type is an SQL syntax error or an upgrade script reading error, which increases the troubleshooting time and prolongs the business interruption period.

[0008] To address the aforementioned issues, the industry urgently needs an automated upgrade method for multiple business systems and databases that can automatically identify business systems and database environments, accurately execute corresponding upgrade scripts, ensure data consistency, and provide traceability. This would improve upgrade efficiency, reduce operational risks, and support the stable operation of multiple business systems within an enterprise. Summary of the Invention

[0009] To address the problems of chaotic upgrade script management, high risk of manual execution, difficulty in ensuring data consistency, and lack of traceability in the upgrade process of existing multi-business systems and multi-database environments, as mentioned in the background, this invention provides an automatic upgrade method for multi-business systems and multi-database environments. This method automates, refines, and traces the upgrade process, reduces operational risks, improves upgrade efficiency, and supports the stable operation of enterprise multi-business systems.

[0010] To achieve the above objectives, the present invention adopts the following technical solution: An automatic upgrade method for multiple databases in a multi-service system includes the following steps: S1. Create a system information table in the database of each business system. The system information table stores the version number and system name of the current business system. S2. Create upgrade configuration files and several database environment script storage directories in the resource directory of each business system, and write upgrade scripts for the corresponding databases in each script storage directory. S3. When the business system starts, listen for the data source initialization event and trigger the database upgrade process before the data source initialization is completed and the business code is executed.

[0011] Specifically, in step S1, the system information table created in the database of each business system is used to uniquely identify the identity and version status of the current business system. It stores only one piece of information about the current business system, and the system information table includes the following fields: 1. Primary key field: It is fixed to a unique value to ensure that the table stores only one piece of information for the current business system, avoiding data redundancy or chaos; 2. System Name field: Stores a unique identifier for the business system, used to distinguish different business systems; 3. Version number field: Stores the current database version of the business system, used to determine whether an upgrade is needed.

[0012] Specifically, in step S2, the upgrade configuration file includes several sets of upgrade configuration parameters. Each set of upgrade configuration parameters corresponds to an upgrade step, specifically including the version number before upgrade, the version number after upgrade, and several script paths. The business system finds the corresponding upgrade script through the script paths to perform the upgrade.

[0013] Specifically, in step S3, the system listens for the data source initialization event when it starts up. Before the data source initialization is completed and the business code is executed, the upgrade process is triggered to ensure that the upgrade operation is completed before the business code such as cache loading and data query is executed. This avoids inconsistency between the database and the old data in the cache after the upgrade, which could lead to errors in the execution of business logic and ensure data consistency.

[0014] Specifically, in step S3, the database upgrade process is as follows: S31. Create a database connection using the basic database connection tool, query the system information table, and obtain the version number and system name of the current business system; S32. Read a set of upgrade configuration parameters in the upgrade configuration file, compare the version number of the current business system with the upgraded version number in the upgrade configuration parameters, and determine whether an upgrade needs to be performed. If the version number of the current business system is less than the upgraded version number, it is determined that the upgrade script corresponding to the script path in this set of upgrade configuration parameters needs to be executed. S33. Obtain the database environment name of the current business system, parse the business system identifier and database environment identifier in the script path read in the previous step, and match the upgrade script corresponding to the system name and database environment of the current business system. S34. Execute the upgrade script sequentially. If the upgrade is successful, update the version number in the system information table to the upgraded version number; if the upgrade fails, record the alarm information. S35. Repeat steps S32 to S34 above until the upgraded version number of any set of upgrade configuration parameters in the upgrade configuration file is no greater than the version number of the current business system.

[0015] Specifically, the upgrade configuration file is used to store and manage all upgrade configuration parameters in a unified manner to avoid management chaos. Each set of upgrade configuration parameters includes the following: 1. Previous version number: The initial version from which the upgrade operation began; 2. Upgraded version number: The target version for the upgrade operation, used to determine whether the current version meets the upgrade requirements. After a successful upgrade, it will be updated to the system information table. 3. Script path: The storage path of the upgrade script to be executed. Multiple script paths are supported, separated by commas, to enable sequential execution of multiple scripts.

[0016] Specifically, in step S31, the basic database connection tools include the JDBC API for Java, the DB-API for Python, the database / sql package for Go, and database connection interfaces for other programming languages, which are compatible with multi-language technology stacks and expand the applicability of the method.

[0017] Specifically, in step S33, the database environment name is obtained through the metadata of the database connection. Specifically, the database product name is obtained by calling the JDBC connection object Connection.getMetaData().getDatabaseProductName() method or the metadata retrieval method of the corresponding database connection object in other languages. The database product name is then converted to lowercase or standardized form and used to replace macro parameters to conform to the script storage path, providing a basis for matching the corresponding database script.

[0018] Specifically, in step S33, the upgrade script path achieves precise matching through the business system identifier and the database environment identifier. The business system identifier is specifically the prefix of the business system name, which precedes the script path and is separated from it by a colon. The database environment identifier is a macro parameter. The parsing rules for the script path include: (1) If the script path is preceded by the business system name, the script corresponding to the script path will be executed only if the system name of the current business system is consistent with the business system name prefix. (2) If the script path does not carry the business system identifier prefix, it is determined that all business systems execute the script corresponding to the script path; (3) Replace the macro parameter with the database environment name of the current business system.

[0019] Specifically, in step S34, when the upgrade fails, the following information is recorded in the alarm information table to quickly locate the cause of the upgrade failure and reduce troubleshooting time: 1. Current system version number; 2. Version number after upgrade; 3. Error description information (such as configuration file parsing error, script file reading error, SQL script execution error); 4. Alarm time (e.g., "2024-05-20 14:30:00").

[0020] In summary, the beneficial technical effects of the present invention are as follows: 1. Automated upgrade: No manual selection or script execution is required; the upgrade is completed automatically upon system startup, reducing the workload of maintenance personnel. 2. Precise script matching: By using business system identifiers and database environment identifiers, ensure that the scripts corresponding to the business system and database environment are executed, avoiding script confusion or incorrect execution; 3. Data consistency: The upgrade is triggered before the data source initialization is complete to avoid inconsistencies between the cache and the database after the business code is executed; 4. Traceability: Records detailed alarm information when upgrade fails, facilitating quick identification of the cause and shortening business interruption time. Attached Figure Description

[0021] Figure 1 This is the automatic upgrade flowchart of the present invention; Figure 2 This is a diagram illustrating the system information table structure and example of the present invention. Figure 3 This is an example diagram of the configuration directory for the upgrade script of this invention; Figure 4 This is an example diagram of the upgrade script configuration file for this invention; Figure 5 This is a structural diagram of the alarm information table of the present invention. Detailed Implementation

[0022] To make the technical means, creative features, objectives and effects of this invention clearer and easier to understand, the invention will be further described below in conjunction with the accompanying drawings and specific embodiments.

[0023] Example This embodiment is based on the Spring Boot framework and is designed for two business systems: OA (Office Automation) and CMS (Content Management), adapting to MySQL and Oracle database environments respectively. Figure 1 The upgrade process shown enables automatic upgrades from version 2.2.3.20250527_release to 2.2.4.20250601_release.

[0024] S1. Create a system information table for each system and execute the following steps in the OA system's MySQL database: Figure 2 The SQL statement for creating the table is shown. At the same time, similar SQL statements are executed in the Oracle database of the CMS system to adjust the data types. The system_name is initialized to OA and CMS respectively, and the version is 2.2.3.20250527_release. After the system information table is created for the first time, it will not be recreated for any subsequent version upgrades.

[0025] S2. Create a directory named dbupgrade under the resource directory (src / main / resources) of the business system. The internal structure is as follows: Figure 3 As shown, the configuration content of the upgrade configuration file is as follows: Figure 4 As shown, upgrade.list[] represents the number of upgrade steps, upgrade.list[].base is the starting version number of each upgrade step, upgrade.list[].version is the target version number of each upgrade step, and upgrade.list[].sql is a list of script paths separated by commas.

[0026] S3. When the OA system starts, the Spring Boot framework will trigger a data source initialization event. The business system listens for this event and starts the upgrade process before the data source initialization is completed and before the business code (such as cache loading) is executed.

[0027] The specific execution process of the upgrade procedure in step S3 is as follows: S31. Create a database connection using the basic database connection tool, query the system information table, and obtain system information. Specifically, create an independent database connection using JDBC to avoid conflicts with business data sources, execute an SQL query to the system information table, and obtain the returned results: system name and system version number.

[0028] S32. Determine the upgrade requirement. The business system reads the upgrade configuration file version_upgrade.properties, iterates through several sets of upgrade configuration parameters in upgrade.list, and finds that base=2.2.3.20250527_release of upgrade.list[1] is consistent with the current version, and version=2.2.4.20250601_release is greater than the current version. Therefore, it is determined that the upgrade step needs to be executed.

[0029] S33. Parse and match the upgrade script: (1) Obtain the current database environment name through the Connection.getMetaData().getDatabaseProductName() method of JDBC connection.

[0030] (2) Parse the script path. upgrade.list[1].sql contains 3 paths: General script: / dbupgrade / {dbType} / 2.2.3.20250527_release_up_2.2.4.20250601_release.sql (no prefix, executes on all systems); OA-specific script: OA: / dbupgrade / {dbType} / 2.2.3.20250527_release_up_2.2.4.20250601_release_OA.sql (the prefix OA matches the current system name); CMS-specific script: CMS: / ... (The prefix CMS does not match the current system name, so it is skipped).

[0031] (3) Replace the macro parameter, replacing {dbType} with the database environment name mysql obtained in step (1), and finally obtain the storage path of the upgrade script and match it: Common script: / dbupgrade / mysql / 2.2.3.20250527_release_up_2.2.4.20250601_release.sql; OA-specific script: / dbupgrade / mysql / 2.2.3.20250527_release_up_2.2.4.20250601_release_OA.sql.

[0032] S34. Execute the upgrade scripts. The business system executes the two upgrade scripts in sequence. After the two scripts in this set of upgrade steps are executed successfully, the business system updates the version field of the system information table to the upgraded version number.

[0033] S35. Since the upgraded version number in all group upgrade configuration parameters of the upgrade configuration file is not greater than the current system version number, the upgrade process will not continue.

[0034] For exception handling: If an error occurs during script execution, the business system will catch the exception and insert the current system version number, the upgraded version number, the error description, and the alarm time into the alarm information table. The structure of the alarm information table is as follows: Figure 5 As shown, maintenance personnel can use this table to quickly locate problems, such as script file reading errors or SQL script execution errors, and then fix them.

[0035] The upgrade process of the CMS system is the same as that of the OA system. The difference is that the database environment is Oracle, and the final matching script is the general script in upgrade.list[1].sql and the CMS prefix exclusive script / dbupgrade / oracle / 2.2.3.20250527_release_up_2.2.4.20250601_release_CMS.sql.

[0036] Therefore, the automatic upgrade method for multiple databases in a multi-service system provided by this invention achieves the objective of the invention through the following design: System information table design: Create a system information table in each business system database to store the version number and system name. By querying this table, the current system identity and version can be obtained, realizing accurate identity identification and version tracking of business systems, and providing a reliable basis for judging upgrade needs; Upgrade configuration files and script structure: Upgrade configuration files are used to manage the orderly upgrade steps in a unified manner, and script directories are divided according to database type and scripts are distinguished according to business system identifiers, so as to achieve standardized management of upgrade steps and scripts and avoid script chaos and dispersion; Data source initialization event listener: Listen for data source initialization events and trigger the upgrade process before the data source initialization is completed and before the business code is executed, to ensure the consistency between the database and cached data after the upgrade; Automatic environment identification: By obtaining the database environment name, parsing the business system identifier and database environment identifier in the script path, it accurately matches the upgrade script corresponding to the current business system and database environment, thus avoiding incorrect script execution; Anomaly Handling: When an upgrade fails, information such as system name, current version, target version, and error description is recorded in the alarm information table to ensure the traceability of the upgrade failure and facilitate maintenance personnel to quickly locate the problem and shorten the business interruption time.

[0037] The embodiments of the present invention have been described in detail above with reference to the accompanying drawings. The above embodiments are only used to illustrate the technical solutions of the present invention and are not intended to limit it. Although the present invention has been described in detail with reference to preferred embodiments, those skilled in the art should understand that modifications or equivalent substitutions can be made to the technical solutions of the present invention without departing from the spirit and scope of the technical solutions of the present invention, and all such modifications or substitutions should be covered within the scope of the claims of the present invention.

Claims

1. A method for automatic upgrade of multiple databases in a multi-service system, characterized in that: Includes the following steps: S1. Create a system information table in the database of each business system. The system information table stores the version number and system name of the current business system. S2. Create upgrade configuration files and several database environment script storage directories in the resource directory of each business system, and write upgrade scripts for the corresponding databases in each script storage directory. S3. The business system listens for the data source initialization event when it starts up and triggers the database upgrade process before the data source initialization is completed. In step S1, the system information table stores only one piece of information about the current business system, specifically the primary key, system name, and version number; In step S2, the upgrade configuration file includes several sets of upgrade configuration parameters. Each set of upgrade configuration parameters includes the version number before upgrade, the version number after upgrade, and several script paths. The business system finds the corresponding upgrade script through the script paths to perform the upgrade. In step S3, the database upgrade process is as follows: S31. Create a database connection using the basic database connection tool, query the system information table, and obtain the version number and system name of the current business system; S32. Read a set of upgrade configuration parameters in the upgrade configuration file, compare the version number of the current business system with the upgraded version number in the upgrade configuration parameters. If the version number of the current business system is less than the upgraded version number, it is determined that the upgrade script corresponding to the script path in this set of upgrade configuration parameters needs to be executed. S33. Obtain the database environment name of the current business system, parse the business system identifier and database environment identifier in the script path read in the previous step, and match the upgrade script corresponding to the system name and database environment of the current business system. S34. Execute the upgrade script sequentially. If the upgrade is successful, update the version number in the system information table to the upgraded version number. If the upgrade fails, record the alarm information and interrupt the upgrade process. S35. Repeat steps S32 to S34 above until the upgraded version number of any set of upgrade configuration parameters in the upgrade configuration file is no greater than the version number of the current business system. In step S33, the business system identifier is specifically a prefix for the business system name, which precedes the script path; the database environment identifier is specifically a macro parameter; the script path parsing rules include: (1) If the script path is preceded by the business system name, it is determined that the system name of the current business system is the only one that is considered. If the prefix of the name matches the business system name, execute the upgrade script corresponding to the script path; (2) If the script path does not carry the business system identifier prefix, it is determined that all business systems will execute the upgrade script corresponding to the script path; (3) Replace the macro parameter with the database environment name of the current business system.

2. The method for automatic upgrade of multiple databases in a multi-service system according to claim 1, characterized in that: In step S31, the basic database connection tool is specifically the JDBC API.

3. The method for automatic upgrade of multiple databases in a multi-service system according to claim 1, characterized in that: In step S33, the database environment name is obtained through the database connection's metadata, specifically by calling the method in the JDBC connection object to obtain the database product name.

4. The method for automatic upgrade of multiple databases in a multi-service system according to claim 1, characterized in that: In step S34, the alarm information includes the current system version number, the upgraded version number, the error description information, and the alarm time. The alarm information is stored in the alarm information table of the business system database. Error descriptions include configuration file parsing errors, script file reading errors, and SQL script execution errors.

Images