Database table version management method and device based on DDL change in multiple environments

Abstract

The invention provides a database table version management method and device based on DDL change in multiple environments, and the method comprises the steps: capturing a DDL change log of a database instance, analyzing and generating a change record with an initial version number, marking the change record as a first state, and storing the change record; screening such records, associating project information and generating a complete version number to form a delivery SQL set; and after publishing to a target environment, updating a corresponding record state to a second state, and incrementally synchronizing all second state records to a database main version record and version records of other environments according to the complete version number, so as to realize consistent management of multi-environment database table versions. The method aims at solving the problems of consistency and maintainability caused by version state chaos among environments, change traceability information loss and inaccurate synchronization range in database table structure change management under multiple environments; state consistency management, clear traceability and efficient rollback of cross-environment database table structure change can be realized.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database library table version management, and in particular to a database table version management method and device based on DDL changes in multiple environments. BACKGROUND

[0002] With the popularity of agile development and DevOps practices, enterprise software systems generally adopt a multi-environment parallel architecture to ensure the isolation of development testing activities and production environment stability. In this mode, ensuring the consistency of database table structures between environments becomes a core challenge for cross-environment collaboration and reliable software delivery. Changes to database table structures need to be safely, orderly, and traceably transferred between environments, and any inconsistency can lead to functional abnormalities, data errors, and even serious business risks.

[0003] Currently, two types of solutions are mainly used in the prior art to implement database version management. One type is to rely on specific components such as Flyway and Liquibase, and to update the database structure by embedding versioned SQL scripts into the application and automatically executing them when the application starts. The other type is to develop a self-management platform by enterprises, integrate database change scripts with continuous integration / continuous deployment (CI / CD) processes, and achieve automated publishing. However, these existing solutions have obvious limitations: 1) the environment is not clearly distinguished, and the version history is not strictly managed independently according to the environment, resulting in chaotic version states between environments; 2) the database change is strongly coupled with the application code version, and the triggering and recording of the database change rely on the application publishing process, which is prone to database version confusion caused by code version errors; 3) there is a lack of complete change traceability information, and the recording often lacks key contexts such as the source environment, specific publishing time, and associated projects, making it difficult to audit and rollback problems afterwards; 4) the synchronization mechanism is not precise, and when synchronizing changes to other environments, it is prone to "multiple synchronization" or "less synchronization", making it difficult to ensure the final consistency of the structure between environments.

[0004] Therefore, there is an urgent need to solve the consistency and maintainability problems caused by the chaotic version states between environments, the strong coupling of changes and code versions, the lack of change traceability information, and the imprecise synchronization range in the management of database table structure changes in multiple environments. SUMMARY

[0005] In view of this, the embodiments of the present application provide a database table version management method and device based on DDL changes in multiple environments to eliminate or improve one or more defects in the prior art.

[0006] One aspect of the present application provides a database table version management method based on DDL changes in multiple environments, comprising: The database change log collector is used to capture data definition language (DDL) statements from change logs of each database instance in real time, and each DDL statement is parsed to obtain a table name corresponding to each DDL statement and a database instance identifier used to represent a corresponding relationship between the database instance and a running environment to which the database instance belongs. Each DDL statement is grouped according to the table name to obtain each statement group, and an initial version number of each statement group is generated to form a change record; the change record is stored in a database environment version record of a current running environment corresponding to the database instance identifier, and a delivery state of the change record is marked as a first state in the database environment version record. Before a software version is released, the change record to be released is obtained as a current target change record based on the database environment version record, project information is associated with the target change record, and a complete version number is generated to form a delivery SQL set; The delivery SQL set is released to a target running environment; after the release is completed, the delivery state of the target change record corresponding to the database environment version record is updated to a second state; and according to the complete version number, all change records with the second state in this release are synchronized to a database master version record and database environment version records of other running environments.

[0007] In some embodiments of the present application, before the database change log collector is used to capture data definition language (DDL) statements from change logs of each database instance in real time, the following steps are further included: A DDL change log function is enabled in each database instance to capture data definition language (DDL) statements in real time, wherein the database type corresponding to the database instance includes a relational database.

[0008] In some embodiments of the present application, before the database change log collector is used to capture data definition language (DDL) statements from change logs of each database instance in real time, the following steps are further included: An environment identifier corresponding to each running environment is configured respectively, and each environment identifier is one-to-one bound with a corresponding database instance to generate a database instance identifier of each database instance; wherein each running environment includes a development environment, a test environment, a pre-production environment and a production environment; and the database instance identifier includes a unique identifier of the database instance, the environment identifier corresponding to the running environment of the database instance and a type identifier of the database.

[0009] In some embodiments of the present application, the obtaining, based on the database environment version record, the change record to be released as the current target change record comprises: filtering, from the database environment version record, the change record record with the delivery state being the first state as the current target change record; and / or, directly inputting a SQL statement to be released, and creating a change record corresponding to the SQL statement in the database environment version record, and taking the change record as the current target change record.

[0010] In some embodiments of the present application, the synchronizing, according to the complete version number, all change records with the delivery state being the second state in the current release to a database master version record and database environment version records of other running environments comprises: synchronizing, according to the complete version number, all change records with the delivery state being the second state in the current release to a database master version record and database environment version records of other running environments in full; or, synchronizing, according to the complete version number, all change records with the delivery state being the second state in the current release to a database master version record and database environment version records of other running environments in incremental.

[0011] In some embodiments of the present application, the project information comprises an online time and a project number; the initial version number comprises a unique character, the database instance identifier and the table name; the complete version number comprises the unique character, the database instance identifier, the table name, the online time and the project number.

[0012] In some embodiments of the present application, the first state is a daily change state, used to indicate that the change record is only effective in the current running environment and is not included in the release process; the second state is a delivery change state, used to indicate that the change record has passed the audit and is successfully released to the target running environment.

[0013] Another aspect of the present application provides a database table version management device based on DDL change in multiple environments, comprising: a change collection module, configured to capture data definition language (DDL) statements from change logs of each database instance in real time through a database change log collector, and parse each DDL statement to obtain a table name corresponding to each DDL statement and a database instance identifier used to represent the corresponding relationship between the database instance and the running environment to which the database instance belongs; The change record module is configured to group each of the DDL statements according to the table name to obtain each statement group, and generate an initial version number of each of the statement groups to form a change record; store the change record into a database environment version record of a current running environment corresponding to the database instance identifier, and mark a delivery state of the change record as a first state in the database environment version record; The delivery set generation module is configured to, before a software version is released, acquire the change record to be released as a current target change record based on the database environment version record, associate project information with the target change record, and generate a complete version number to form a delivery SQL set; The release and synchronization module is configured to release the delivery SQL set to a target running environment; after the release is completed, update a delivery state of the target change record corresponding to the database environment version record to a second state; and according to the complete version number, synchronize all change records in this release whose delivery state is the second state to a database master version record and database environment version records of other running environments.

[0014] A third aspect of the present application provides an electronic device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the database table version management method based on DDL changes in multiple environments when executing the computer program.

[0015] A fourth aspect of the present application provides a computer readable storage medium, which stores a computer program, wherein the computer program is executed by a processor to implement the database table version management method based on DDL changes in multiple environments.

[0016] A fifth aspect of the present application provides a computer program product, which comprises a computer program, wherein the computer program is executed by a processor to implement the database table version management method based on DDL changes in multiple environments.

[0017] The application provides a database table version management method based on DDL change in multiple environments. The method comprises the following steps: collecting data definition language (DDL) statements from change logs of each database instance in real time through a database change log collector; parsing each DDL statement to obtain a table name corresponding to each DDL statement and a database instance identifier used to represent the corresponding relationship between the database instance and the running environment; grouping each DDL statement according to the table name to obtain each statement group, and generating an initial version number of each statement group to form a change record; storing the change record in a database environment version record of a current running environment corresponding to the database instance identifier, and marking the delivery state of the change record as a first state in the database environment version record; before the software version is released, obtaining the change record to be released as a current target change record based on the database environment version record, associating project information with the target change record, and generating a complete version number to form a delivery SQL set; publishing the delivery SQL set to a target running environment; after the publishing is completed, updating the delivery state of the corresponding target change record in the database environment version record to a second state; and according to the complete version number, synchronizing all change records with the second state in this release to a database master version record and database environment version records of other running environments. The method provided by the application can ensure that the changes are accurately and consistently transmitted between different running environments by generating a complete version number with uniqueness for each change and performing incremental synchronization based on the version number, and can avoid omission or repetition. Each running environment has an independent database environment version record to manage the change history of the environment. Meanwhile, the change histories of all environments are globally associated through a shared database master version record and a unified synchronization mechanism, which facilitates overall tracing. The change record is bound with project information (such as project number and online time), and the original data definition language (DDL) statement and the change environment are recorded to form a complete change tracing chain. The delivery state (first state and second state) is introduced to mark the change record, which can clearly distinguish the changes in the daily development test from the changes that have been officially released and delivered, and can further standardize the release process, realize consistent management, clear tracing and efficient rollback of cross-environment database table structure changes, greatly reduce the manual intervention and operation cost required for manual comparison, execution and verification of database structure changes between multiple environments, reduce the risk of production accidents caused by manual operation errors, and improve the fault positioning and recovery efficiency by orders of magnitude, which is particularly suitable for enterprise-level scenarios with strict requirements for data consistency and change audit.

[0018] Additional advantages, objects, and features of the application will be set forth in part by the description that follows, and will in part be apparent to those of ordinary skill in the art upon examination of the following or can be learned from a practice of the application. The advantages and features of the application can be realized and obtained by means of the instrumentalities and combinations pointed out in the appended claims.

[0019] Those skilled in the art will appreciate that the objects and advantages of the application can be practiced without resorting to the details of the specific implementation described herein, and that the full scope of the application can be understood by reference to the claims that follow. BRIEF DESCRIPTION OF DRAWINGS

[0020] The accompanying drawings, which are incorporated herein and form part of the specification, illustrate the present application and, together with the description given below, serve to explain the principles of the present application. The drawings are provided to illustrate certain aspects of the present application and are not intended to limit the scope of the present application. The components in the drawings are not necessarily to scale, emphasis instead being placed upon illustrating the principles of the present application. For purposes of clarity and understanding, it is also to be noted that certain portions of the drawings can be exaggerated, and others can be omitted. Figure 1 The first flowchart of the database table version management method based on DDL change in multiple environments according to an embodiment of the present application.

[0021] Figure 2 The second flowchart of the database table version management method based on DDL change in multiple environments according to an embodiment of the present application.

[0022] Figure 3 The structure diagram of the database table version management device based on DDL change in multiple environments according to an embodiment of the present application.

[0023] Figure 4 The execution flowchart of the database table version management method based on DDL change in multiple environments according to an application example of the present application. DETAILED DESCRIPTION

[0024] In order to make the objects, technical solutions and advantages of the present application clearer, the present application will be further described in detail below with reference to the embodiments and drawings. Herein, the illustrative embodiments of the present application and their descriptions are used to explain the present application, but are not intended to limit the present application.

[0025] It should also be noted that, in order to avoid obscuring the present application due to unnecessary details, only the structures and / or processing steps closely related to the scheme according to the present application are shown in the drawings, and other details not closely related to the present application are omitted.

[0026] It should be emphasized that the term "comprising / comprising" as used herein refers to the presence of the features, elements, steps or components, but does not exclude the presence or addition of one or more other features, elements, steps or components.

[0027] Here, it should also be noted that the term "connection" as used herein can not only mean direct connection, but also indirect connection with intermediate objects, unless otherwise specified.

[0028] In the following, embodiments of the present application will be described with reference to the accompanying drawings. In the drawings, the same reference numerals represent the same or similar parts or the same or similar steps.

[0029] First of all, it should be noted that as the software development mode changes from monolithic development to collaborative development, agile development, and Devops, enterprise IT systems generally form a multi-environment parallel architecture: the core environment includes development environment (temporary environment for developers to iterate functions) DEV, test environment (verify function stability and compatibility) SIT, pre-production environment (final verification environment simulating production configuration) UAT, and production environment (official environment for users) PROD. The core demand of multi-environment is environment isolation to avoid the impact of development and test operations on production stability, but at the same time requires the same database structure to ensure that the functions behave consistently in each environment. Among them, DevOps is the integration of development and operation (Development and Operations), emphasizing the cooperation and process automation between development, testing and operation.

[0030] And as the core component of storing business data and defining data relationships, the consistency of the data structure (table information) of the database table directly determines the availability of the system: the changes of the database table run through the software life cycle, including field addition, deletion and modification, constraint condition adjustment, index optimization, table name and comment change, etc. In cross-environment collaboration, the changes of the database table need to be transferred from the development environment to the test and production environments, and the change track needs to be preserved for problem troubleshooting and version rollback. For core business systems such as finance, e-commerce, and government affairs, inconsistencies in the database table structure may lead to serious consequences such as transaction failure, data loss, statistical bias, and even compliance risks.

[0031] The current common database version management scheme mainly includes the following two types: (1) Depend on specific components (such as Flyway, Liquibase): embed version management tools into the application, automatically execute versioned SQL scripts (such as V1.sql, V2.sql) at application startup, and realize incremental update of database structure.

[0032] (2) Self-developed database version management platform: The enterprise develops a platform by itself, manages database changes through an interface or a script, and usually integrates with the CI / CD process to realize automatic release. CI / CD is Continuous Integration / Continuous Deployment, which refers to an automated software building, testing, and release process.

[0033] However, the above technology has the following problems: (1) Environment differentiation is not clear: Some solutions do not strictly manage versions according to environments, resulting in chaotic version states among environments.

[0034] (2) Strong coupling with code version: Version management depends on the application code version, and once the code version is incorrect, the database version record will also be incorrect.

[0035] (3) Lack of change trace information: Lack of key information such as change source environment, release time, and project association, making it difficult to audit and rollback.

[0036] (4) Inaccurate synchronization mechanism: When synchronizing changes to other environments, there may be multiple synchronization or less synchronization, resulting in inconsistent structures among environments.

[0037] Therefore, in order to solve the problems of chaotic version states among environments, strong coupling between changes and code versions, lack of change trace information, and inaccurate synchronization range in the management of database table structure changes in multiple environments, which lead to consistency and maintainability problems, the embodiments of the present application provide a database table version management method based on DDL changes in multiple environments, a database table version management device based on DDL changes in multiple environments for executing the database table version management method based on DDL changes in multiple environments, an entity device, a computer readable storage medium, and a computer program product. By setting a unique identifier for each environment and establishing a binding relationship between the environment and the database instance, DDL statements are collected, and database version numbers are generated, and based on the version numbers, independent management and associated synchronization of database table versions in multiple environments are realized. The core process includes: (1) Change collection: Real-time capture of DDL statements through a database change log collector; (2) Version recording: Save change records according to environments, and mark whether it is a delivery SQL; (3) Delivery management: Generate a delivery SQL set before release, and associate project information; (4) Precise synchronization: Synchronize to the master version library after release, and incrementally synchronize to other environments.

[0038] The embodiments are described in detail as follows.

[0039] Based on this, the embodiment of the present application provides a database table version management method based on DDL change in multiple environments, which can be implemented by a database table version management device based on DDL change in multiple environments, see Figure 1 The database table version management method based on DDL change in multiple environments specifically includes the following contents: Step 100: Through a database change log collector, real-time capture of data definition language (DDL) statements from change logs of each database instance, and parsing of each DDL statement to obtain a table name corresponding to each DDL statement and a database instance identifier used to represent a corresponding relationship between the database instance and a running environment.

[0040] It can be understood that multiple environments refer to multiple running environments existing in parallel in the software development and deployment process, and usually include a development environment (Development, DEV), a test environment (System Integration Test, SIT), a pre-production environment (User Acceptance Test, UAT), and a production environment (Production, PROD).

[0041] In one or more embodiments of the present application, a data definition language (DDL) statement can be abbreviated as a DDL statement or a DDL SQL statement, which is a data definition language (Data Definition Language), specifically refers to a structured query language (Structured Query Language, SQL) statement used to define or modify the structure of a database or a database object (such as a table, an index), for example, creation (CREATE), modification (ALTER), and deletion (DROP).

[0042] In step 100, the database change log collector refers to a software tool or component, which functions to read and capture the executed DDL statements in real time from a database instance that has started the change log function. The database instance identifier refers to a string used to uniquely identify a specific database instance. It is usually composed of an environment identifier, a database type identifier, and a database instance identifier, which is used to clearly indicate the running environment to which the instance belongs and its own identity. The table name refers to the name of the target table in the database that is operated.

[0043] That is, in step 100, the database table version management device based on DDL change can capture the data definition language (DDL) statements executed by each database instance with change log function enabled through a database change log collector in real time. The multiple environments generally refer to the development environment, test environment, pre-production environment and production environment in software development and deployment. The captured DDL statements include structured query language (SQL) statements for creating, modifying and deleting database table structures. Then, each DDL statement is parsed. The parsing process can be completed by using a SQL syntax parsing library (such as jsqlparser) to accurately extract two key information: one is the target table name operated by the statement; the other is the database instance identifier of the source database instance. The database instance identifier is a unique string, which is usually composed of environment identifier, database type identifier and instance identifier, and is used to identify the instance and its running environment.

[0044] Step 200: grouping each of the DDL statements according to the table name to obtain each statement group, generating an initial version number of each of the statement groups, forming a change record, storing the change record in the database environment version record of the current running environment corresponding to the database instance identifier, and marking the delivery state of the change record as the first state in the database environment version record.

[0045] It should be noted that the initial version number refers to the version identifier created when the change record is initially generated, which is usually generated based on the database instance identifier and table name, and is used for preliminary identification within a single running environment.

[0046] Correspondingly, the change record refers to a structured data unit recording one or a group of related data definition language (DDL) change information. The change record can include an initial version number, a related data definition language (DDL) statement set, a table name and a delivery state.

[0047] In step 200, the database environment version record refers to a data storage area (such as a table or warehouse) stored in a specific running environment, which is used to record all database table structure change records occurring in the environment.

[0048] The delivery state refers to a state mark given to the change record, which is used to identify the stage of the change in the release process. The first state represents the daily change state (i.e. only effective in the current environment, not included in the formal release process); the second state represents the delivery change state (i.e. has passed the audit and successfully released to the target environment).

[0049] That is, in step 200, the database table version management device based on DDL change can group the DDL statements according to the parsed table names, and all DDL statements for the same table are grouped into a statement group. An initial version number is generated for each statement group, which can be used as the initial identification of this group change in the current environment, for example, the format can be "INIT_{database instance identifier}{table name}{timestamp}". The unit containing the initial version number, table name, DDL statement set and other information constitutes a change record. The change record is stored in the database environment version record corresponding to the database instance identifier in the current running environment. The database environment version record is a storage area (such as a database table) specially used to store the change history of the entire environment. At the same time of storage, the delivery state of the record needs to be explicitly marked as the first state. The first state represents the daily change state, indicating that this change is only for the current environment and has not been included in the formal release process.

[0050] Step 300: Before the software version is released, based on the database environment version record, the change record to be released is obtained as the current target change record, the project information is associated with the target change record, and the complete version number is generated to form a delivery SQL set.

[0051] In step 300, the target change record refers to the change record selected from the database environment version record before the software version is released and prepared to be included in this release range.

[0052] The project information refers to the business information associated with this software release, which usually includes at least the project number and the online time.

[0053] The complete version number refers to the final version identifier generated for the target change record in the release preparation phase. It increases the business information such as online time and project number on the basis of the initial version number, has global uniqueness, and is the key basis for cross-environment precise synchronization and traceability.

[0054] The delivery SQL set refers to a change package composed of a set of data definition language (DDL) statements associated with a group of target change records, which is prepared to be released to the target running environment, such as a set of SQL change statements that have been audited and are prepared to be released to the production environment.

[0055] That is, in step 300, the database table version management device based on DDL change can prepare a release package before planning to release the software version (including database changes) to a higher level environment (such as a production environment). Based on the database environment version record, the change record to be released is obtained as the target change record of this time. Specifically, the records in the first state can be manually screened through the management interface, or automatically triggered by the continuous integration / continuous deployment (CI / CD) process according to the strategy. And for each target change record, associate project information (at least including project number and planned online time), and generate a complete version number based on it. The complete version number integrates business context information based on the initial version number, and has global uniqueness, for example, the format can be "REL_{database instance identifier}{table name}{online time}_{project number}". All target change records and their associated DDL statement sets together constitute a delivery SQL set to be released.

[0056] Step 400: Release the delivery SQL set to the target running environment; after the release is completed, update the delivery state of the corresponding target change record in the database environment version record to the second state; and according to the complete version number, synchronize all change records with the second state in this release to a database master version record and other database environment version records of running environments.

[0057] Among them, the database master version record refers to a data storage area (such as a table or warehouse) that is independent of a specific running environment and centrally stores all change records that have been officially released (i.e., the delivery state is the second state). It is the benchmark and summary of all running environment version records, used for global version viewing and consistency management.

[0058] That is, in step 400, the database table version management device based on DDL change can release the delivery SQL set to the database of the target running environment (such as UAT or PROD) and execute it. After the release is successfully completed, a key step is to perform state closure: update the delivery state of those target change records corresponding to this batch in the database environment version record from the first state to the second state. The second state represents the "delivery change state", indicating that this change has been officially released and takes effect. After the state is updated, a synchronization process is started. According to the uniqueness of the complete version number, synchronize all change records with the second state updated in this batch to two targets: (1) Database master version record: a central repository independent of any specific running environment, used to archive all officially released change records as a single trusted source of global version.

[0059] (2) Other database environment version records of running environment: for example, when a change is released from the SIT environment to the UAT environment, the change record also needs to be synchronized to the version record of the DEV environment to keep the consistency of the awareness of the history information of each environment.

[0060] The core logic of the synchronization can be incremental synchronization, that is, only when the same complete version number does not exist in the target storage, an addition operation is performed to ensure the accuracy of the synchronization and avoid repetition or omission. In addition, when the system is deployed for the first time, the database master version record can be initialized by full table structure synchronization from the production environment to the record.

[0061] From the above description, the database table version management method based on DDL change in multiple environments provided by the embodiments of the present application can ensure that the changes are accurately and consistently transmitted between different running environments, avoid omission or repetition, by generating a complete version number with uniqueness for each change and performing incremental synchronization based on the version number. Each running environment has an independent database environment version record to manage the change history of the environment. At the same time, through the shared database master version record and the unified synchronization mechanism, the change history of all environments is globally associated, which facilitates overall tracing. By binding the change record with project information (such as project number and online time), and recording the original data definition language (DDL) statement and the change environment, a complete change tracing chain is formed. The delivery state (first state and second state) is introduced to mark the change record, which can clearly distinguish the changes in daily development and testing from the changes that have been officially released and delivered, thereby standardizing the release process, realizing consistent management, clear tracing and efficient rollback of cross-environment database table structure changes, greatly reducing the manual intervention and operation cost required for manual comparison, execution and verification of database structure changes between multiple environments, reducing the risk of production accidents caused by manual operation errors, and improving the fault positioning and recovery efficiency by orders of magnitude. It is particularly suitable for enterprise-level scenarios that have strict requirements for data consistency and change audit.

[0062] In the database table version management process, existing database version management tools often fail to capture the source data due to incorrect configuration of the change log function of the database itself before deployment, resulting in failure of the entire management system. Based on this, in order to further solve the problem of unclear pre-deployment dependencies and missing configurations leading to unavailable functions, and to ensure the implementability of the method in the typical scenario of relational databases, in the database table version management method based on DDL change in multiple environments provided by the embodiments of the present application, referring to Figure 2 , the database table version management method based on DDL change in multiple environments further comprises the following content before step 100: Step 010: enabling the DDL change log function in each database instance for real-time capture of data definition language (DDL) statements, wherein the database type corresponding to the database instance includes a relational database.

[0063] That is, before step 100 is executed, necessary database configuration needs to be performed. Specifically, the DDL change log function needs to be enabled in each database instance to be managed. The DDL change log function is a feature provided by the database kernel, which is used to record all data definition language (DDL) operations into a specific log file (for example, the binlog log file of the relational database management system MySQL, the Write-Ahead Logging log file of the object and relational database management system PostgreSQL). This function is a prerequisite for subsequent real-time capture of DDL statements. The present method is mainly applied to relational databases such as MySQL, PostgreSQL, relational database Oracle, etc. Such databases have strict structured table definitions and mature change log mechanisms, and can provide stable and reliable data sources for the present method.

[0064] In the database table version management process, in a multi-environment complex scenario, if the change record cannot be accurately traced to the specific environment in which it is generated, it will lead to chaotic version management. Based on this, in order to further solve the problems of ambiguous environment identification and unclear instance and environment mapping relationship, and to provide an accurate addressing basis for subsequent version isolation and accurate synchronization, in the database table version management method based on DDL changes in a multi-environment provided in the present application, see Figure 2 , the database table version management method based on DDL changes in a multi-environment further specifically includes the following content before step 100: Step 020: configuring different environment identifiers corresponding to each running environment respectively, and binding each environment identifier with the corresponding database instance one by one to generate a database instance identifier for each database instance; wherein each running environment includes a development environment, a test environment, a pre-production environment and a production environment; and the database instance identifier includes a unique identifier of the database instance, an environment identifier corresponding to the running environment of the database instance, and a type identifier of the database.

[0065] Specifically, before step 100 is executed, environment identification definition and binding also need to be completed. First, a globally unique environment identifier is configured for each running environment, such as "DEV", "SIT", "UAT", and "PROD". These environment identifiers correspond to standard running environment types (development environment, test environment, pre-production environment, and production environment).

[0066] Then, the environment identifier is bound one-to-one with one or more specific database instances in the corresponding environment. After binding, a unique database instance identifier is generated for each database instance. The identifier is a composite string containing at least three parts: the unique identifier of the database instance itself (such as IP address or instance name), the environment identifier of the environment to which it is bound, and the type identifier of the database (such as "MYSQL", "PG"). Through this binding relationship, any captured DDL statement can be accurately associated with its running environment through its source instance.

[0067] In the database table version management process, when preparing for release, the existing scheme usually only supports selection from historical records, and it is difficult to deal with changes generated outside the process, such as emergency patches or external scripts. Based on this, in order to further solve the problem of single source of release package and inability to flexibly handle non-captured changes, the adaptability of the method in real operation scenarios is enhanced. In the database table version management method based on DDL changes in multiple environments provided in the embodiments of the present application, referring to Figure 2 , the database table version management method based on DDL changes in multiple environments specifically includes the following contents: Step 310: filtering the change record records with the delivery state being the first state from the database environment version records to serve as the current target change record.

[0068] Specifically, in the management interface, all change records with the "delivery state" being the "first state" (i.e., the daily change state) are queried and checked from the database environment version records, and are batch-selected as the target of this release.

[0069] And / or, step 320: directly inputting the SQL statement to be released, and creating a change record corresponding to the SQL statement in the database environment version record, taking the change record as the current target change record.

[0070] Specifically, when there is an emergency repair or an external SQL script that needs to be released, the SQL statement text can be directly input or uploaded through the interface. The system will automatically create a new change record for this SQL statement in the database environment version record, and generate an initial version number and mark it as "first state", and then directly include it in the delivery SQL set of this time.

[0071] Steps 310 and 320 can be used independently or in combination, so as to flexibly cope with release requirements of different sources.

[0072] In the database table version management process, the singleness of the synchronization strategy cannot adapt to different stages of the system life cycle (such as initial deployment and daily iteration). Based on this, in order to further solve the problem that the synchronization mechanism is rigid and cannot balance the initialization efficiency and the daily synchronization accuracy, two synchronization strategies are provided to adapt to different scenarios. In the database table version management method based on DDL change in multiple environments provided in the embodiment of the present application, referring to Figure 2 , the database table version management method based on DDL change in multiple environments specifically includes the following contents: Step 410: According to the complete version number, all change records in this release with the delivery state being the second state are fully synchronized to the database master version record and the database environment version record of other running environments.

[0073] Specifically, when the database master version record or the database environment version record of a certain target environment is empty (that is, not initialized), all change records (with the state being the second state) to be synchronized this time and their complete history (if needed) are synchronized at one time. This usually occurs when the system is first put into operation or a new environment is connected.

[0074] Alternatively, step 420: According to the complete version number, all change records in this release with the delivery state being the second state are incrementally synchronized to the database master version record and the database environment version record of other running environments.

[0075] Specifically, when the target storage already has historical records, the uniqueness of the complete version number is compared. Only the change records in the target storage that do not have the corresponding complete version number are synchronized. This mechanism ensures the accurate synchronization of changes in daily releases and avoids duplication or omission of data.

[0076] In actual application, the device can automatically judge the state of the target storage to select the appropriate synchronization strategy.

[0077] In the database table version management process, if the version identifier is too simple, it cannot carry enough traceability information; if there is no unified rule, it is difficult to realize accurate comparison. Based on this, in order to further solve the problem that the amount of version number information is insufficient and the format is not unified, which leads to difficulty in tracing and synchronization, by standardizing the composition of the version number, it becomes a key index with uniqueness and information, in the database table version management method based on DDL change in multiple environments provided in the embodiment of the present application, the project information includes the online time and the project number.

[0078] The initial version number includes: unique characters, the database instance identifier, and the table name; The complete version number comprises the unique character, the database instance identifier, the table name, the online time and the project number.

[0079] In an example, the project information at least comprises the online time and the project number (such as the project unique code in the enterprise). The initial version number is a string spliced by a unique character (such as "V" or "INIT"), a database instance identifier and a table name (for example, "INIT_DEV_MYSQL_DB1_user"), which is used for intra-environment identification. The complete version number is generated in step 300, and the online time and the project number are added on the basis of the initial version number (for example, "REL_DEV_MYSQL_DB1_user_20231015140000_PROJ123"). The complete version number is globally unique, and is the core basis for cross-environment synchronization and global traceability.

[0080] In the database table version management process, if the state flag has ambiguous meaning, the automatic process cannot be clearly driven, and it is also difficult for people to understand the life cycle stage of the change. Based on this, in order to further solve the problem that the state machine semantics are not clear, leading to difficulty in automatic decision and high cost of manual understanding, the state semantics are clearly defined, so that the state transition has clear business meaning. In the database table version management method based on DDL change in multiple environments provided in the embodiments of the present application, the first state is a daily change state, which is used to indicate that the change record is effective only in the current running environment and is not included in the release process; and the second state is a delivery change state, which is used to indicate that the change record has passed the audit and has been successfully released to the target running environment.

[0081] Specifically, the first state is defined as a daily change state. When the change record is marked as this state, it indicates that the change is a routine operation performed by a developer or a tester in the corresponding running environment, and is effective only in the current environment, has not been audited, and has not been included in any formal release process. It exists in the database environment version record of the environment, and is used to record history. The second state is defined as a delivery change state. When the change record is updated to this state, it indicates that the change has passed the necessary test and audit, and has been successfully released to the target running environment (usually a pre-production or production environment) through step 400. This state marks the completion of a change life cycle, and the record will be synchronized to the database master version record as a released fact.

[0082] From the software level, the present application also provides a database table version management device based on DDL change in multiple environments for executing all or part of the database table version management method based on DDL change in multiple environments. Referring to Figure 3 , the database table version management device based on DDL change in multiple environments specifically comprises the following contents: The change collection module 10 is configured to capture DDL statements in real time from the change logs of each database instance by a database change log collector, parse each DDL statement to obtain a table name corresponding to each DDL statement and a database instance identifier used to represent the corresponding relationship between the database instance and the running environment.

[0083] The change record module 20 is configured to group each DDL statement according to the table name to obtain each statement group, generate an initial version number of each statement group to form a change record, store the change record in a database environment version record of the current running environment corresponding to the database instance identifier, and mark the delivery state of the change record in the database environment version record as a first state.

[0084] The delivery collection generation module 30 is configured to, before the release of a software version, obtain the change record to be released as a current target change record based on the database environment version record, associate project information with the target change record and generate a complete version number to form a delivery SQL collection.

[0085] The release and synchronization module 40 is configured to release the delivery SQL collection to a target running environment, update the delivery state of the target change record corresponding to the database environment version record to a second state after the release is completed, and synchronize all change records with the second state in this release to a database master version record and database environment version records of other running environments according to the complete version number.

[0086] The embodiments of the database table version management device based on DDL changes in multiple environments provided by the present application can be used to execute the processing flow of the embodiments of the database table version management method based on DDL changes in multiple environments described above, and the functions thereof will not be repeated here. Please refer to the detailed description of the embodiments of the database table version management method based on DDL changes in multiple environments described above.

[0087] The part of the database table version management device based on DDL changes in multiple environments for database table version management can be completed in a server or a client device. Specifically, it can be selected according to the processing capacity of the client device and the limitations of the user's use scenario. The present application does not make any limitation on this. If all operations are completed in the client device, the client device can also include a processor for specific processing of the database table version management based on DDL changes in multiple environments.

[0088] The client device described above can have a communication module (i.e., a communication unit) that can be communicatively connected to a remote server to achieve data transmission with the server. The server can include a server of a task scheduling center side, and in other implementation scenarios, can also include a server of an intermediate platform, such as a server of a third-party server platform that is communicatively linked to the server of the task scheduling center. The server can include a single computer device, or can include a server cluster composed of multiple servers, or a server structure of a distributed device.

[0089] The server and the client device can communicate with each other using any suitable network protocol, including a network protocol that has not been developed as of the filing date of the present application. The network protocol can include, for example, a TCP / IP protocol, a UDP / IP protocol, an HTTP protocol, an HTTPS protocol, and the like. Of course, the network protocol can also include, for example, a RPC protocol (Remote Procedure Call Protocol) and a REST protocol (Representational State Transfer) used on top of the above-mentioned protocols.

[0090] As can be seen from the above description, the database table version management apparatus based on DDL change in multiple environments provided by the embodiments of the present application can generate a complete version number with uniqueness for each change, and perform incremental synchronization based on the version number, so as to ensure that the change is accurately and consistently transmitted between different running environments, and avoid omission or repetition. Each running environment has an independent database environment version record to manage the change history of the environment. Meanwhile, through the shared database master version record and the unified synchronization mechanism, the change history of all environments is globally associated, which facilitates overall tracing. By binding the change record with project information (such as project number and online time), and recording the original data definition language (DDL) statement and the change environment, a complete change tracing chain is formed. The delivery state (first state and second state) is introduced to mark the change record, which can clearly distinguish the change in daily development and testing from the change that has been officially released and delivered, and further can standardize the release process, realize consistent management, clear tracing and efficient rollback of cross-environment database table structure change, can greatly reduce the manual intervention and operation cost required for manual comparison, execution and verification of database structure change between multiple environments, reduce the risk of production accidents caused by manual operation errors, and can improve the fault positioning and recovery efficiency by orders of magnitude, and is particularly suitable for enterprise-level scenarios with strict requirements on data consistency and change audit.

[0091] In order to further illustrate the above embodiments, the present application also provides a specific application example of a database table version management method based on DDL change in multiple environments, which is described below with reference to Figure 4The multi-environment database table version management method based on DDL change specifically includes the following contents: Step 1: The relational database instance needs to start the change log for collecting DDL SQL statements.

[0092] Step 2: Set detailed identifiers, such as DEV, SIT1, SIT2, and UAT, as environment identifier enumeration information.

[0093] Step 3: Bind the environment identifier enumeration with the database instance to generate a database instance identifier (such as “environment identifier_database type identifier_database instance identifier”).

[0094] Step 4: Establish a version management system (storage medium) for each environment database, and the management information includes database version management (database environment version record) of each environment, database master version record, database change log collector, and delivery SQL management. The system can view the current version and historical version of the database based on the table of each environment, and the version information includes: environment identifier, version number (such as “unique character_{database instance identifier}_table name_online time_project number”), database name, table name, original database DDL SQL set changed, new table information, SQL delivery person, and whether it is a delivery SQL.

[0095] Step 5: Use the database change log collector to collect SQL records in real time, retain DDL SQL, and then parse the SQL (conventional technology: jsqlparser for syntax tree parsing), parse the table name and collection path (determine “{database instance identifier}”), group according to the table name, then generate an initial version number (example: “unique character_{database instance identifier}_table name”, at this time the version number is not complete), and save the grouped DDL SQL set to the database environment version record of the current environment, each record includes: database instance identifier, version number, whether it is a delivery SQL (status: no, when it is no, it means only the current time of the current environment daily change), SQL, and table name attributes.

[0096] Step 6: When a delivery SQL is needed, query the SQL record of the database environment version record in the delivery SQL management to generate a set of delivery SQL, or directly fill in the SQL (if the SQL source is not a SQL record, then “{database instance identifier}” needs to be filled in), and then rely on the self-owned project management platform or Devops platform to fill in the online time and project number. Then store these information in the delivery SQL management (storage medium), and update or generate a complete version number, such as “unique character_{database instance identifier}_table name_online time_project number”, and the delivery SQL is used for release to the production environment.

[0097] Step 7: When the delivery SQL completes publishing to the production environment, the SQL synchronization event can be triggered manually or automatically, first updating the current environment database environment version record according to the complete version number: the initial version number in step 5 is not complete, whether it is a delivery SQL (the status is yes) and the latest table data structure change content, then incrementally synchronizing all records of the current batch of delivery SQL with the status equal to "yes" to the database master version record, and finally synchronizing all version change information to the database environment version record of other environments.

[0098] At this point, a complete database version change is completed, and the database version information can be retrieved in detail through the database environment version record and the database master version record.

[0099] It should be noted that there is only one way to initialize the database master version record, that is, updating the library table content of the production environment to the database master version record to complete data initialization, at this time the master version is the same as the production environment database version. There is another way to not initialize, only record the incremental data of each update, if the master version is used from the first time the production environment is published, the master version is also the same as the production environment database version.

[0100] The core content of the table version management is table information, and the updating version record implementation technology is basically the same, which is compared. The biggest difference between different version management schemes is the difference in the management mode of the attached other information, and the application scheme is completely different from the database version management of the middleware such as Flyway, Liquibase.

[0101] In summary, compared with the prior art (such as Flyway, Liquibase or general self-developed platform), the database table version management method based on DDL change under multiple environments provided by the application example has the following beneficial effects: 1. The unified management of environment isolation and global association is realized: By configuring a unique environment identifier for each running environment (such as DEV, SIT, UAT, PROD), and deeply integrating it into the database instance identifier and the version number generated subsequently, the scheme fundamentally establishes the accurate mapping of "environment-instance-table". This mechanism ensures that each environment has a completely independent version history record (database environment version record), realizing effective isolation between environments. At the same time, by synchronizing the final publishing records of all environments to a unified database master version record, a global version view is also built, enhancing the connectivity and overall observability between multiple environment database versions, and solving the problem of version state confusion between environments.

[0102] 2. A complete change traceability chain driven by state and associated with business is constructed: A two-state management mechanism based on delivery state (the first state is daily change, and the second state is delivery change) is introduced. This mechanism strictly distinguishes between temporary changes in the development test phase and formal release changes that have been reviewed, and standardizes the release process. More importantly, when generating the release version (complete version number), the system automatically associates the change record with the project information (such as project number, online time), thereby establishing an unalterable link between technical changes and business context. In combination with the definition of original data definition language (DDL) statements, the present scheme provides a complete and auditable traceability chain from business requirements (project) to the final database structure change, making the rollback operation of historical versions precise and fast.

[0103] 3. Precise incremental synchronization capability based on version number comparison is provided: In view of the structural inconsistency problem caused by multiple synchronization or less synchronization when existing schemes are synchronized across environments, the present scheme proposes a precise synchronization mechanism based on complete version number. This mechanism compares based on the globally unique complete version number during synchronization, and only performs incremental synchronization on change records that do not exist in the target environment. This eliminates the risk of repeated execution or missed execution from the algorithm level, and fundamentally guarantees the eventual consistency of the database table structure in multiple environments. At the same time, this synchronization mechanism supports both full initialization and incremental synchronization strategies, and can adapt to the needs of different life cycle stages of the system.

[0104] 4. Achieve decoupled and reliable automated operation foundation: Unlike traditional schemes that embed version management scripts into applications and are strongly coupled with code versions, the present scheme captures changes directly from the database underlying log through an independently deployed database change log collector. This design achieves decoupling of database structure change management and application release process, avoiding the risk of database version confusion caused by application code version errors. All the above mechanisms together form a reliable and automated operation foundation, significantly reducing the operation cost and error probability of manual comparison and execution of database changes, and providing a solid foundation for continuous integration and continuous deployment (CI / CD) practice.

[0105] The embodiments of the present application also provide an electronic device, which can include a processor, a memory, a receiver and a transmitter. The processor is used to execute the DDL change-based database table version management method in multiple environments mentioned in the above embodiments. The processor and the memory can be connected through a bus or other means to be connected through the bus. The receiver can be connected with the processor and the memory through wired or wireless means.

[0106] The processor can be a central processing unit (CPU). The processor can also be other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field-programmable gate arrays (FPGA) or other programmable logic devices, discrete gates or transistor logic components, discrete hardware components, or a combination thereof.

[0107] The memory, as a non-transitory computer readable storage medium, can be used to store non-transitory software programs, non-transitory computer executable programs and modules, such as program instructions / modules corresponding to the database table version management method based on DDL change in multiple environments in the embodiments of the present application. The processor executes various functions and data processing of the processor by running the non-transitory software programs, instructions and modules stored in the memory, that is, implements the database table version management method based on DDL change in multiple environments in the above method embodiments.

[0108] The memory can include a program storage area and a data storage area, wherein the program storage area can store an operating system and application programs required by at least one function; and the data storage area can store data created by the processor and the like. In addition, the memory can include a high-speed random access memory, and can also include a non-transitory memory, such as at least one magnetic disk storage device, a flash memory device, or other non-transitory solid-state memory device. In some embodiments, the memory can optionally include a memory remotely arranged with respect to the processor, and these remote memories can be connected to the processor through a network. Examples of the above network include but are not limited to the Internet, an intranet, a local area network, a mobile communication network, and a combination thereof.

[0109] The one or more modules are stored in the memory, and when executed by the processor, perform the database table version management method based on DDL change in multiple environments in the embodiments.

[0110] In some embodiments of the present application, the user equipment can include a processor, a memory and a transceiver unit which can include a receiver and a transmitter, the processor, the memory, the receiver and the transmitter can be connected through a bus system, the memory is used to store computer instructions, and the processor is used to execute the computer instructions stored in the memory to control the transceiver unit to transceive signals.

[0111] As an implementation manner, the functions of the receiver and the transmitter in the present application can be implemented by a transceiver circuit or a transceiver dedicated chip, and the processor can be implemented by a dedicated processing chip, a processing circuit or a general-purpose chip.

[0112] As another implementation manner, the server provided by the embodiments of the present application can be implemented by using a general-purpose computer. That is, the program codes for implementing the functions of the processor, the receiver and the transmitter are stored in the memory, and the general-purpose processor implements the functions of the processor, the receiver and the transmitter by executing the codes in the memory.

[0113] The embodiments of the present application further provide a computer readable storage medium, which stores a computer program. The computer program is executed by a processor to implement the steps of the database table version management method based on DDL change in multiple environments.

[0114] The embodiments of the present application further provide a computer program product, which includes a computer program. The computer program is executed by a processor to implement the steps of the database table version management method based on DDL change in multiple environments.

[0115] Those skilled in the art should understand that the exemplary components, systems and methods described in connection with the embodiments disclosed herein can be implemented in hardware, software or a combination thereof. The actual implementation depends on the specific application and design constraints imposed on the overall system. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of the present application. When implemented in hardware, it can be, for example, an electronic circuit, an application specific integrated circuit (ASIC), appropriate firmware, a plug-in, a functional card, etc. When implemented in software, the elements of the present application are program or code segments used to perform the required tasks. The program or code segments can be stored in a machine readable medium or transmitted through a data signal carried in a carrier wave over a transmission medium or a communication link.

[0116] It is to be expressly understood that the application is not limited to the described and illustrated particular configurations and processes. For the sake of clarity, detailed descriptions of known methods are omitted. In the above described embodiments, several specific steps are described and illustrated as examples. However, the method processes of the application are not limited to the specific steps described and illustrated, and the skilled person can make various changes, modifications and additions, or change the order of the steps, after having understood the spirit of the application.

[0117] In this application, features described and / or illustrated with respect to one embodiment can be used in the same or similar manner or in conjunction with or in place of features of other embodiments.

[0118] The above only describes the preferred embodiments of the present application, and is not intended to limit the present application. The embodiments of the present application can be variously changed and modified by those skilled in the art. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present application shall be included in the protection scope of the present application.

Claims

1. A database table version management method based on DDL changes in multiple environments, characterized in that, include: The database change log collector captures Data Definition Language (DDL) statements in real time from the change logs of each database instance, and parses each DDL statement to obtain the table name corresponding to each DDL statement and the database instance identifier used to represent the correspondence between the database instance and its operating environment. The DDL statements are grouped according to the table name to obtain statement groups, and an initial version number is generated for each statement group to form a change record. The change record is stored in the database environment version record of the current running environment corresponding to the database instance identifier, and the delivery status of the change record is marked as the first status in the database environment version record; Before the software version is released, based on the version records of the database environment, the change record to be released is obtained as the current target change record. Project information is associated with the target change record and a complete version number is generated to form a delivery SQL set. Publish the delivered SQL set to the target runtime environment; after publication, update the delivery status of the corresponding target change record in the database environment version record to the second status; Based on the complete version number, all change records in this release with a delivery status of second state will be synchronized to the main database version record and the database environment version records of other operating environments.

2. The database table version management method based on DDL changes in multiple environments according to claim 1, characterized in that, Before capturing Data Definition Language (DDL) statements in real time from the change logs of each database instance using a database change log collector, the process also includes: Enable the DDL change log function in each database instance to capture Data Definition Language (DDL) statements in real time. The database type corresponding to the database instance includes relational databases.

3. The database table version management method based on DDL changes in multiple environments according to claim 1, characterized in that, Before capturing Data Definition Language (DDL) statements in real time from the change logs of each database instance using a database change log collector, the process also includes: Each runtime environment is configured with a unique environment identifier, and each environment identifier is bound one-to-one with its corresponding database instance to generate a database instance identifier for each database instance. The runtime environments include: development environment, testing environment, pre-production environment, and production environment. The database instance identifier includes: a unique identifier for the database instance, the environment identifier corresponding to the runtime environment of the database instance, and the database type identifier.

4. The database table version management method based on DDL changes in multiple environments according to claim 1, characterized in that, The step of obtaining the change record to be published as the current target change record based on the database environment version record includes: Filter the change record whose delivery status is the first status from the database environment version record to use it as the current target change record; And / or, Enter the SQL statement to be published directly, and create a change record corresponding to the SQL statement in the database environment version record. Use this change record as the current target change record.

5. The database table version management method based on DDL changes in multiple environments according to claim 1, characterized in that, The step involves synchronizing all change records in this release with a delivery status of "Second Status" to a primary database version record and other database environment version records in the runtime environment, based on the complete version number. Based on the full version number, all change records in this release with a delivery status of second status will be fully synchronized to the primary database version record and the database environment version records of other operating environments. or, Based on the full version number, all change records in this release that are in the second delivery status will be incrementally synchronized to the primary database version record and the database environment version records of other operating environments.

6. The database table version management method based on DDL changes in multiple environments according to any one of claims 1 to 5, characterized in that, The project information includes the launch time and project number; The initial version number includes: a unique character, the database instance identifier, and the table name; The complete version number includes: the unique character, the database instance identifier, the table name, the launch time, and the project number.

7. The database table version management method based on DDL changes in multiple environments according to any one of claims 1 to 5, characterized in that, The first state is the daily change state, which indicates that the change record is only effective in the current operating environment and has not been included in the release process; The second status is the delivery change status, which indicates that the change record has been approved and successfully published to the target operating environment.

8. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the database table version management method based on DDL changes in multiple environments as described in any one of claims 1 to 7.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that, When executed by a processor, the computer program implements the database table version management method based on DDL changes in multiple environments as described in any one of claims 1 to 7.

10. A computer program product, comprising a computer program, characterized in that, When executed by a processor, the computer program implements the database table version management method based on DDL changes in multiple environments as described in any one of claims 1 to 7.

Images