A method, device and medium for automatically generating a database change script

Abstract

The application discloses a kind of database change script automatic generation method, equipment and medium, it is related to database version control technical field.The method comprises the following steps: creating temporary database instance by containerization technology;Full-amount SQL definition of first version database and second version database is loaded to temporary database instance, to parse and generate first metadata set and second metadata set;Wherein, first metadata set at least includes table structure, field definition, index and constraint information;Multi-dimensional comparison is carried out to first metadata set and second metadata set by difference analysis engine, and operation change item is identified;According to operation change item, standardization database change script is generated;Wherein, standardization database change script includes the upgrade script for upgrading to second version and the rollback script for rolling back to first version.

Description

Technical Field

[0001] This application relates to the field of database version control technology, and in particular to a method, device and medium for automatically generating database change scripts. Background Technology

[0002] In the field of database technology, with the ever-increasing iteration speed of internet applications, the frequency and complexity of database structure changes are growing exponentially. In traditional development models, the writing of database change scripts relies entirely on technical personnel manually writing SQL statements. This approach not only requires developers to have in-depth database expertise but also faces the risk of syntax errors and logical defects caused by human negligence. Especially in scenarios involving parallel development of multiple versions or rollback of historical versions, manually maintaining multiple sets of change scripts can easily lead to version chaos and significantly increase system maintenance costs.

[0003] While existing database version management tools can achieve a certain degree of version tracking, their technical implementation still has fundamental flaws. These tools typically require embedding a dedicated metadata table in the target database to manage version evolution by recording change history. This intrusive architectural design not only pollutes the production environment's database structure but also leads to strong coupling between version control and business data. When it is necessary to generate change scripts between non-continuous versions, existing tools can only perform incremental derivation through a linear version chain and cannot directly establish a mapping relationship between any two historical versions, resulting in significant functional limitations in complex branch development scenarios.

[0004] Therefore, how to achieve fully automated generation of change scripts while ensuring the safety of the production environment has become a technical problem that urgently needs to be solved by those skilled in the art. Summary of the Invention

[0005] This application provides a method, device, and medium for automatically generating database change scripts, in order to solve the following technical problem: how to achieve fully automated generation of change scripts while ensuring the security of the production environment.

[0006] In a first aspect, embodiments of this application provide an automatic generation method for database change scripts. The method includes: creating a temporary database instance using containerization technology; loading the full SQL definitions of a first version database and a second version database into the temporary database instance to parse and generate a first metadata set and a second metadata set; wherein the first metadata set includes at least table structure, field definitions, indexes, and constraint information; performing multi-dimensional comparison of the first metadata set and the second metadata set using a difference analysis engine to identify operation change items; and generating a standardized database change script based on the operation change items; wherein the standardized database change script includes an upgrade script for upgrading to the second version and a rollback script for rolling back to the first version.

[0007] In one embodiment of this application, a temporary database instance is created using containerization technology, specifically including: dynamically starting a lightweight container engine; initializing a container instance based on a pre-built database image; and deploying a database service process without persistent storage within the container.

[0008] In one embodiment of this application, the full SQL definitions of the first version database and the second version database are loaded into a temporary database instance to parse and generate a first metadata set and a second metadata set. Specifically, this includes: converting SQL statements into structured metadata objects through an abstract syntax tree parser; extracting the information to be applied from the metadata objects; and constructing a metadata set containing hierarchical relationships based on the information to be applied. The information to be applied includes: table name, field name, field type, index name, and constraint conditions.

[0009] In one embodiment of this application, the operation change items include: table-level operations, field-level operations, index operations, and constraint operations. A difference analysis engine performs multi-dimensional comparisons of the first and second metadata sets to identify the operation change items. Specifically, this includes: traversing and comparing the table name lists in the first and second metadata sets to identify table addition, deletion, and renaming operations; for tables with the same name, recursively comparing field definitions to identify field addition, deletion, renaming, and data type change operations; comparing index definitions to identify index addition, deletion, and field composition change operations; and comparing constraint definitions to identify primary key, foreign key, and uniqueness constraint change operations.

[0010] In one embodiment of this application, the method further includes: when comparing constraint definitions, if it is detected that the parent table field associated with the foreign key has been deleted, terminating script generation and sending a constraint conflict warning; and when it is detected that a new non-null constraint has been added but the target field has a null value, automatically inserting a default fill statement.

[0011] In one embodiment of this application, a standardized database change script is generated based on the operation change items. Specifically, this includes: generating atomic change statements with version identifiers for each operation change item, and assembling the upgrade script according to a preset execution order; wherein the preset execution order is: executing structural changes first and then data migration, with table-level changes taking precedence over field-level changes; injecting a version header information block consisting of a version number, a generation timestamp, and a digital signature into the header of the upgrade script; performing forward execution verification on each change statement in the upgrade script, and inserting a reverse compensation statement at the corresponding position in the rollback script when a statement execution failure is detected; and automatically generating a rollback script based on the change sequence of the upgrade script by reversing the order and adding a rollback identifier.

[0012] In one embodiment of this application, an atomic change statement with a version identifier is generated for each operation change item. Specifically, this includes: when a field data type change operation is detected, first an ALTER statement to create a temporary field is generated, then an UPDATE statement to convert the data type is generated, and finally a RENAME statement to rename the field is generated; when a change in the composition of index fields is detected, the old index is deleted first, and then an index creation operation containing the new field combination is executed; when generating foreign key constraint change statements, a SET CONSTRAINTS statement for delayed constraint checking is appended after the parent table change statement, and an SET CONSTRAINTS statement for immediate checking is inserted at the corresponding position in the rollback script.

[0013] In one embodiment of this application, when generating foreign key constraint change statements, the method further includes: parsing the foreign key relationship graph; when a cross-table cascading change is detected, generating a change sequence according to a bottom-up topological sort; inserting a table locking instruction before the parent table constraint change statement, and appending an unlock instruction after the child table change is completed; generating a fully qualified table name with alias for the self-referencing foreign key constraint, and inserting a constraint name mapping table at the corresponding position in the rollback script.

[0014] Secondly, embodiments of this application also provide an automatic database change script generation device, the device comprising: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, the instructions being executed by the at least one processor to enable the at least one processor to perform an automatic database change script generation method as described above.

[0015] Thirdly, embodiments of this application also provide a non-volatile computer storage medium for automatically generating database change scripts, storing computer-executable instructions, which, when executed, implement an automatic database change script generation method as described above.

[0016] This application provides a method, device, and medium for automatically generating database change scripts, which has the following advantages: By constructing a non-intrusive architecture analysis mechanism and an intelligent difference inference engine, the entire process of database change script generation is automated, significantly improving the accuracy and execution efficiency of change operations. This technical solution abandons the traditional manual writing mode, completely eliminating metadata pollution to the production environment through containerization isolation technology. Simultaneously, it employs a multi-dimensional metadata comparison mechanism to accurately identify subtle changes in tables, fields, indexes, and constraints. Combined with atomic statement generation and two-way verification mechanisms, it not only ensures the syntactic correctness of the change scripts but also effectively avoids data migration risks through execution order optimization and constraint conflict warning functions. In particular, the adaptation design based on database dialect plugins enables the system to have cross-database platform compatibility, while the automatically generated upgrade / rollback script dual versions perfectly solve the cascading change problem in complex foreign key association scenarios through a reverse order compensation mechanism and topology sorting algorithm, providing an enterprise-level solution for database version management that combines security and flexibility. Attached Figure Description

[0017] The accompanying drawings, which are included to provide a further understanding of this application and form part of this application, illustrate exemplary embodiments and are used to explain this application, but do not constitute an undue limitation of this application. In the drawings:

[0018] Figure 1 A flowchart illustrating an automatic database change script generation method provided in this application embodiment;

[0019] Figure 2 This is a schematic diagram of the internal structure of a device for automatically generating database change scripts, provided as an embodiment of this application. Detailed Implementation

[0020] To make the objectives, technical solutions, and advantages of this application clearer, the technical solutions of this application will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only a part of the embodiments of this application, and not all of them. Based on the embodiments in this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0021] This application provides a method, device, and medium for automatically generating database change scripts, in order to solve the following technical problem: how to achieve fully automated generation of change scripts while ensuring the security of the production environment.

[0022] The technical solutions proposed in the embodiments of this application will be described in detail below with reference to the accompanying drawings.

[0023] Figure 1 This is a flowchart illustrating a method for automatically generating database change scripts, as provided in an embodiment of this application. Figure 1 As shown in the figure, the method for automatically generating database change scripts provided in this application embodiment specifically includes the following steps:

[0024] Step 101: Create a temporary database instance using containerization technology.

[0025] In one embodiment of this application, a temporary database instance is created using containerization technology, specifically including: dynamically starting a lightweight container engine; initializing a container instance based on a pre-built database image; and deploying a database service process without persistent storage within the container.

[0026] In this embodiment, the containerization technology refers to dynamically creating temporary database instances using a lightweight virtualization engine. Its core purpose is to provide an isolated sandbox environment for the subsequent loading and comparison of all SQL definitions. Specifically, the tool calls the API interface of a container engine (such as Docker or Podman), selects a pre-configured official image based on the user-specified database type (such as MySQL or PostgreSQL), and creates a temporary container on the host machine. For example, when the user selects to generate a change script for MySQL 8.0, the tool will pull the `mysql:8.0` image and start a container instance based on that image that exists only in memory, ensuring complete isolation between the database service process and the host machine environment.

[0027] Understandably, the "temporary" characteristic manifests in two aspects: First, the lifespan of the container instance is strictly controlled, existing only briefly during the script generation process and automatically destroyed after the process ends; second, the database service process deployed within the container disables persistent storage, i.e., it uses `tmpfs` or a memory file system instead of disk storage to avoid any data residue. For example, the startup command adds the `--rm` flag to automatically delete the container upon exit, and simultaneously mounts the database data directory to the memory file system using `--tmpfs / var / lib / mysql`, thereby completely eliminating the storage dependency on the host machine.

[0028] It's important to note that the creation of temporary database instances is managed uniformly by the tool's built-in `TempDBCluster` class. This class encapsulates dynamic container port allocation, service health status detection, and error retry mechanisms. For example, when multiple comparison tasks are executed in parallel, to avoid port conflicts, the tool randomly assigns an unused port number (such as 3307 or 3308) to each container and verifies the container's readiness status using the `docker inspect` command. Furthermore, during the database service startup phase, the tool verifies service availability by executing basic query commands such as `SHOW DATABASES`. If a startup timeout or service anomaly is detected, the tool automatically triggers the container rebuild process.

[0029] In this embodiment, the creation of the temporary database instance provides an independent and clean runtime environment for loading the full SQL definition and parsing metadata in subsequent steps. Containerization isolation avoids the risk of data pollution that may be caused by directly operating the production database, and also supports parallel loading and comparison of multiple version architectures (e.g., loading v1 and v3 versions of SQL files simultaneously).

[0030] Step 102: Load the full SQL definitions of the first version database and the second version database into the temporary database instance to parse and generate the first metadata set and the second metadata set.

[0031] In this embodiment, the first metadata set includes at least table structure, field definitions, indexes, and constraint information.

[0032] In one embodiment of this application, the full SQL definitions of the first version database and the second version database are loaded into a temporary database instance to parse and generate a first metadata set and a second metadata set. Specifically, this includes: converting SQL statements into structured metadata objects through an abstract syntax tree parser; extracting the information to be applied from the metadata objects; and constructing a metadata set containing hierarchical relationships based on the information to be applied. The information to be applied includes: table name, field name, field type, index name, and constraint conditions.

[0033] In this embodiment, the "full SQL definition" refers to an SQL script file that completely describes the database architecture, containing creation statements for all tables, fields, indexes, and constraints (such as `CREATE TABLE`, `ALTER TABLE`, etc.). Its source can be historical version snapshots stored in a version control system (such as Git). Specifically, the tool executes the full SQL file through a temporary database instance and uses the database's built-in metadata query interface (such as MySQL's `SHOW CREATE TABLE`, `INFORMATION_SCHEMA` tables) or a custom parser to extract the definition information of database objects.

[0034] For example, when loading the first version (v1) SQL file, the tool imports the SQL statements one by one into the database instance within a temporary container via the `mysql` command-line client. It then executes the `SHOW TABLES` command to obtain a list of all table names, and for each table, executes the `SHOW CREATE TABLE` command to obtain its complete definition (including fields, indexes, and constraints). For complex objects such as stored procedures and triggers, the tool extracts their definition text by querying the `INFORMATION_SCHEMA.ROUTINES` table. Understandably, this process needs to adapt to the metadata query methods of different database dialects; for example, PostgreSQL requires the use of the `pg_catalog` system table instead of `INFORMATION_SCHEMA`.

[0035] It should be noted that the generation of the "structured metadata object" relies on an Abstract Syntax Tree (AST) parser. Specifically, the tool parses the original SQL statement (such as `CREATE TABLE users(id INT PRIMARY KEY,nameVARCHAR(255))`) into an object model containing hierarchical relationships: the database object contains multiple table objects, and each table object further contains a list of fields, a list of indexes, and a list of constraints. For example, the field object records attributes such as field name (e.g., `id`), data type (e.g., `INT`), and whether null values ​​are allowed (`NOT NULL`); the index object records the index name (e.g., `PRIMARY`), index type (e.g., `BTREE`), and field composition (e.g., `id`).

[0036] In this embodiment, the metadata set generated by loading and parsing the full SQL provides standardized input for subsequent difference analysis. For example, the first metadata set (v1) and the second metadata set (v3) serve as the foundational data for "multi-dimensional comparison." By recursively traversing the hierarchical structure of tables and fields, the tool accurately identifies operations such as newly added tables and changes in field types. Furthermore, during the parsing process, the tool ignores comments and version control markers (such as `--VERSION:1.0`) in the SQL file, retaining only the core definition statements that affect the database architecture, ensuring the purity and comparability of the metadata set.

[0037] Step 103: Use the difference analysis engine to perform a multi-dimensional comparison of the first metadata set and the second metadata set to identify the operation change items.

[0038] In one embodiment of this application, the operation change items include: table-level operations, field-level operations, index operations, and constraint operations. A difference analysis engine performs multi-dimensional comparisons of the first and second metadata sets to identify the operation change items. Specifically, this includes: traversing and comparing the table name lists in the first and second metadata sets to identify table addition, deletion, and renaming operations; for tables with the same name, recursively comparing field definitions to identify field addition, deletion, renaming, and data type change operations; comparing index definitions to identify index addition, deletion, and field composition change operations; and comparing constraint definitions to identify primary key, foreign key, and uniqueness constraint change operations.

[0039] In one embodiment of this application, the method further includes: when comparing constraint definitions, if it is detected that the parent table field associated with the foreign key has been deleted, terminating script generation and sending a constraint conflict warning; and when it is detected that a new non-null constraint has been added but the target field has a null value, automatically inserting a default fill statement.

[0040] In this embodiment, the core function of the difference analysis engine is to identify differences between two metadata sets from multiple dimensions, such as table structure, field definitions, indexes, and constraints, through recursive traversal and hierarchical comparison algorithms, and derive atomic database operation instructions. It should be noted that the "multi-dimensional comparison" includes, but is not limited to, the following levels: table-level operations (such as adding, deleting, and renaming tables), field-level operations (such as adding or deleting fields, modifying types, and changing constraints), index operations (such as adding or deleting indexes and changing field composition), and constraint operations (such as adjusting primary keys, foreign keys, and uniqueness constraints).

[0041] Specifically, the difference analysis engine first traverses the table name lists of the two metadata sets to identify table-level changes. For example, if the table `orders` does not exist in the first metadata set (v1) but exists in the second metadata set (v3), a `CREATE TABLE orders` operation is generated; if the table `users` is renamed to `user_profiles` in v3, an `ALTER TABLE RENAME` operation is generated (e.g., identifying table additions, deletions, and renaming operations). For tables with the same name, the engine recursively compares their field lists: if the `products` table in v1 contains the field `price DECIMAL(10,2)` but it is changed to `price DOUBLE` in v3, an `ALTER TABLE MODIFY COLUMN` operation is generated; if the field `description` is detected to have been deleted in v3, an `ALTER TABLE DROP COLUMN` operation is generated (e.g., recursively comparing field definitions to identify field additions, deletions, renamings, and data type changes).

[0042] Understandably, comparing indexes and constraints requires considering dependencies and execution order. For example, when adding an index `idx_customer_id` to the `orders` table in v3, the engine generates a `CREATE INDEX` operation. If the field composition of the index `idx_status` changes from `status` to `status,created_at`, an atomic operation sequence is generated, first deleting the old index and then creating a new one (e.g., "Compare index definitions, identify index addition, deletion, and field composition change operations"). For constraint operations, when the engine detects that the parent table field `user_id` of the foreign key constraint `fk_order_user` has been deleted, it immediately interrupts the process and returns an error message to avoid generating invalid scripts.

[0043] It should be noted that the difference analysis engine ensures the reasonableness of the order in which operation items are generated through preset priority rules. For example, table-level operations take precedence over field-level operations to avoid field modification failures due to non-existent tables; constraint changes (such as adding non-null constraints) must be executed after data population is completed. If null values ​​are detected in the target field, the `UPDATE table SET column = default_value WHERE column IS NULL` statement is appended to the upgrade script to ensure data compliance before the constraints take effect.

[0044] In this embodiment, through multi-dimensional and refined comparison, an accurate and executable sequence of change instructions is provided for subsequent script generation.

[0045] Step 104: Generate a standardized database change script based on the operation change items.

[0046] In this embodiment, the standardized database change script includes an upgrade script for upgrading to the second version and a rollback script for rolling back to the first version.

[0047] In one embodiment of this application, a standardized database change script is generated based on the operation change items. Specifically, this includes: generating atomic change statements with version identifiers for each operation change item, and assembling the upgrade script according to a preset execution order; wherein the preset execution order is: executing structural changes first and then data migration, with table-level changes taking precedence over field-level changes; injecting a version header information block consisting of a version number, a generation timestamp, and a digital signature into the header of the upgrade script; performing forward execution verification on each change statement in the upgrade script, and inserting a reverse compensation statement at the corresponding position in the rollback script when a statement execution failure is detected; and automatically generating a rollback script based on the change sequence of the upgrade script by reversing the order and adding a rollback identifier.

[0048] In one embodiment of this application, an atomic change statement with a version identifier is generated for each operation change item. Specifically, this includes: when a field data type change operation is detected, first an ALTER statement to create a temporary field is generated, then an UPDATE statement to convert the data type is generated, and finally a RENAME statement to rename the field is generated; when a change in the composition of index fields is detected, the old index is deleted first, and then an index creation operation containing the new field combination is executed; when generating foreign key constraint change statements, a SET CONSTRAINTS statement for delayed constraint checking is appended after the parent table change statement, and an SET CONSTRAINTS statement for immediate checking is inserted at the corresponding position in the rollback script.

[0049] In one embodiment of this application, when generating foreign key constraint change statements, the method further includes: parsing the foreign key relationship graph; when a cross-table cascading change is detected, generating a change sequence according to a bottom-up topological sort; inserting a table locking instruction before the parent table constraint change statement, and appending an unlock instruction after the child table change is completed; generating a fully qualified table name with alias for the self-referencing foreign key constraint, and inserting a constraint name mapping table at the corresponding position in the rollback script.

[0050] In this embodiment, the generation process of the "standardized database change script" must meet the requirements of atomicity, reversibility, and cross-database adaptability. Its implementation corresponds to operation sequence construction, forward verification, and rollback logic. Specifically, the tool generates a set of SQL statements conforming to the target database's dialect syntax according to preset rules based on the operation change items output by the difference analysis engine, and simultaneously generates a matching rollback script.

[0051] For example, for operations that change the data type of a field (such as changing INT to BIGINT), the tool first generates the statement to create a temporary transition field: `ALTER TABLE users ADD COLUMN temp_id BIGINT`. Then, it generates the data migration statement: `UPDATE users SET temp_id = id`. Finally, it generates the field renaming statement: `ALTER TABLE users CHANGECOLUMN id old_id,temp_id id`. This ensures lossless data conversion. It should be noted that these atomic change statements (such as "atomic change statements with version identifiers") must be assembled into the upgrade script in the order of "structural changes take precedence over data migration, and table-level operations take precedence over field-level operations." For example, in a scenario where a new table `orders` is added and a field in the `users` table is modified, the tool will first output the `CREATE TABLE orders` statement and then process the related `ALTER TABLE users` operations to avoid execution errors caused by table dependencies.

[0052] Understandably, the upgrade script header needs to include a version header information block consisting of a version number, timestamp, and digital signature. For example, the generated script header includes comments such as / *DB Patch Auto-generated at 2025-04-20* / and / *SOURCE HASH:a1b2c3d4* / , used to trace the script's origin and verify its integrity. Furthermore, the tool adds forward execution verification logic to each change statement: for example, after executing an ALTER TABLE statement, it immediately verifies whether the fields have been successfully modified using the SHOW COLUMNS command. If a failure is detected, it triggers the insertion of a reverse operation at the corresponding position in the rollback script (e.g., "perform forward execution verification on each change statement in the upgrade script").

[0053] Regarding rollback script generation, the tool reverses the order of operations in the upgrade script and converts them into reverse operations. For example, `CREATE TABLE orders` in the upgrade script is converted to `DROP TABLEorders` in the rollback script, and the `--[ROLLBACK]` identifier is added before each rollback statement (e.g., "reverse order and add rollback identifier"). It should be noted that for changes involving foreign key constraints (such as adding a foreign key `fk_order_user`), the tool inserts the table locking instruction `LOCK TABLES orders WRITE,users READ` into the upgrade script and appends `UNLOCKTABLES` after the change is completed. Simultaneously, the rollback script generates `ALTER TABLE orders DROP FOREIGN KEY fk_order_user` and the corresponding unlock instruction, ensuring the atomicity of the rollback operation.

[0054] In addition, for self-referencing foreign keys (such as the manager_id field in the employees table referencing its own primary key), the tool will generate a fully qualified table name with alias (e.g., ALTER TABLE employees ADD CONSTRAINT fk_manager_id FOREIGN KEY(manager_id) REFERENCES employees(id) ON DELETE CASCADE), and record the constraint name in the rollback script through the mapping table to avoid rollback failure caused by automatic database naming.

[0055] In this embodiment, the reliable execution of the upgrade and rollback scripts is ensured through atomic operation sequences and a strict positive verification mechanism.

[0056] The above are embodiments of the method proposed in this application. Based on the same inventive concept, embodiments of this application also provide an automatic database change script generation device, the structure of which is as follows: Figure 2 As shown.

[0057] Figure 2 This is a schematic diagram of the internal structure of a device for automatically generating database change scripts, provided as an embodiment of this application. (See diagram below.) Figure 2 As shown, the device includes:

[0058] At least one processor 201;

[0059] And a memory 202 that is communicatively connected to at least one processor;

[0060] The memory 202 stores instructions executable by at least one processor, which are executed by at least one processor 201 to enable at least one processor 201 to:

[0061] A temporary database instance is created using containerization technology. The full SQL definitions of the first and second version databases are loaded into the temporary database instance to parse and generate a first metadata set and a second metadata set. The first metadata set includes at least table structure, field definitions, indexes, and constraint information. A difference analysis engine performs a multi-dimensional comparison of the first and second metadata sets to identify operational changes. Standardized database change scripts are generated based on these operational changes. These standardized database change scripts include an upgrade script for upgrading to the second version and a rollback script for rolling back to the first version.

[0062] Some embodiments of this application provide corresponding to Figure 1 A non-volatile computer storage medium for automatically generating database change scripts, storing computer-executable instructions, wherein the computer-executable instructions are configured as follows:

[0063] A temporary database instance is created using containerization technology. The full SQL definitions of the first and second version databases are loaded into the temporary database instance to parse and generate a first metadata set and a second metadata set. The first metadata set includes at least table structure, field definitions, indexes, and constraint information. A difference analysis engine performs a multi-dimensional comparison of the first and second metadata sets to identify operational changes. Standardized database change scripts are generated based on these operational changes. These standardized database change scripts include an upgrade script for upgrading to the second version and a rollback script for rolling back to the first version.

[0064] The various embodiments in this application are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the embodiments for IoT devices and media are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0065] The systems, media, and methods provided in this application are one-to-one correspondences. Therefore, the systems and media also have similar beneficial technical effects as their corresponding methods. Since the beneficial technical effects of the methods have been described in detail above, the beneficial technical effects of the systems and media will not be repeated here.

[0066] Those skilled in the art will understand that embodiments of this application can be provided as methods, systems, or computer program products. Therefore, this application can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this application can take the form of a computer program product embodied on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0067] This application is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of this application. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart... Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0068] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0069] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0070] In a typical configuration, a computing device includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0071] Memory may include non-persistent storage in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.

[0072] Computer-readable media include both permanent and non-permanent, removable and non-removable media that can store information by any method or technology. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, magnetic magnetic disk storage or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transient computer-readable media, such as modulated data signals and carrier waves.

[0073] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0074] The above description is merely an embodiment of this application and is not intended to limit the scope of this application. Various modifications and variations can be made to this application by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this application should be included within the scope of the claims of this application.

Claims

1. A method of automatically generating a database change script, characterized by, The method includes: Create temporary database instances using containerization technology; The full SQL definitions of the first version database and the second version database are loaded into the temporary database instance to parse and generate a first metadata set and a second metadata set; wherein, the first metadata set includes at least table structure, field definitions, index and constraint information; The difference analysis engine performs a multi-dimensional comparison of the first and second metadata sets to identify the operation changes. A standardized database change script is generated based on the operation change items; wherein, the standardized database change script includes an upgrade script for upgrading to the second version and a rollback script for rolling back to the first version; Creating temporary database instances using containerization technology specifically includes: Dynamically launch a lightweight container engine; Initialize a container instance based on a pre-built database image; Deploy database service processes without persistent storage within containers; The full SQL definitions of the first and second version databases are loaded into the temporary database instance to parse and generate the first metadata set and the second metadata set, specifically including: SQL statements are converted into structured metadata objects using an abstract syntax tree parser; Extract the information to be applied from the metadata object, and construct a metadata set containing hierarchical relationships based on the information to be applied; wherein, the information to be applied includes: table name, field name, field type, index name, and constraint conditions; Generate a standardized database change script based on the aforementioned operation change items, specifically including: Atomic change statements with version identifiers are generated for each operation change item, and upgrade scripts are assembled according to a preset execution order; wherein, the preset execution order is: structural changes are executed first, followed by data migration, and table-level changes take precedence over field-level changes; Inject a version header information block consisting of a version number, a generated timestamp, and a digital signature into the upgrade script header; Perform forward execution verification on each change statement in the upgrade script. When a statement execution failure is detected, insert a reverse compensation statement at the corresponding position in the rollback script. Based on the change sequence of the upgrade script, a rollback script is automatically generated by reversing the order and adding a rollback identifier.

2. The method for automatically generating a database change script according to claim 1, wherein, The operation changes include: table-level operations, field-level operations, index operations, and constraint operations; The difference analysis engine performs a multi-dimensional comparison of the first and second metadata sets to identify operational changes, specifically including: Iterate through and compare the table name lists in the first metadata set and the second metadata set to identify table addition, deletion and renaming operations. For tables with the same name, recursively compare field definitions to identify field additions, deletions, renamings, and data type changes. By comparing the index definitions, identify index addition, deletion, and field composition modification operations; By comparing constraint definitions, identify changes to primary key, foreign key, and uniqueness constraints.

3. The method for automatically generating database change scripts according to claim 2, characterized in that, The method further includes: When comparing constraint definitions, if it is detected that a parent table field associated with a foreign key has been deleted, the script generation will be terminated and a constraint conflict warning will be sent. When a new non-null constraint is detected but the target field contains null values, a default fill statement is automatically inserted.

4. The method for automatically generating database change scripts according to claim 1, characterized in that, Generate atomic change statements with version identifiers for each operation change item, specifically including: When a field data type change operation is detected, first generate an ALTER statement to create a temporary field, then generate an UPDATE statement to convert the data type, and finally generate a RENAME statement to rename the field. When a change in the composition of index fields is detected, the old index is deleted first, and then the index creation operation containing the new field combination is performed. When generating foreign key constraint change statements, append a SETCONSTRAINTS statement for delayed constraint checking after the parent table change statement, and insert an SET CONSTRAINTS statement for immediate checking at the corresponding position in the rollback script.

5. The method for automatically generating database change scripts according to claim 4, characterized in that, When generating foreign key constraint modification statements, the method further includes: Analyze the foreign key relationship graph, and when cross-table cascading changes are detected, generate a change sequence by bottom-up topological sorting; Insert a table lock command before the parent table constraint change statement, and append an unlock command after the child table change is completed; Generate fully qualified table names with aliases for self-referential foreign key constraints, and insert the constraint name mapping table at the corresponding position in the rollback script.

6. An automatic database change script generation device, characterized in that, The device includes: At least one processor; And, a memory communicatively connected to the at least one processor; The memory stores instructions that can be executed by the at least one processor, which, when executed by the at least one processor, enables the at least one processor to perform an automatic generation method for a database change script as described in any one of claims 1-5.

7. A non-volatile computer storage medium for automatically generating database change scripts, storing computer-executable instructions, characterized in that, When the computer-executable instructions are executed, they implement the automatic generation method for database change scripts as described in any one of claims 1-5.

Images