Automatic generation method and device for database change script and medium

Abstract

The invention discloses an automatic generation method and device for a database change script and a medium, and relates to the technical field of database version control. The method comprises the following steps: creating a temporary database instance through a containerization technology; loading full SQL definitions of the first version database and the second version database to the temporary database instance to analyze and generate a first metadata set and a second metadata set; wherein the first metadata set at least comprises a table structure, a field definition, an index and constraint information; performing multi-dimensional comparison on the first metadata set and the second metadata set through a difference analysis engine, and identifying an operation change item; generating a standardized database change script according to the operation change item; wherein the standardized database change script comprises an upgrading script used for upgrading to the second version and a rollback script used for rolling back to the first version.

Description

Technical Field

[0001] The present application relates to the technical field of database version control, and in particular to a method, device, and medium for automatically generating a database change script. Background Art

[0002] In the field of database technology, the frequency and complexity of database structure changes are increasing exponentially with the increasing iteration speed of internet applications. In traditional development models, database change scripts rely entirely on technicians manually writing SQL statements. This approach not only requires developers to possess deep database expertise but also carries the risk of syntax errors and logical flaws caused by human negligence. Especially when developing multiple versions in parallel or rolling back historical versions, manually maintaining multiple change scripts can easily lead to version confusion and significantly increase system maintenance costs.

[0003] While existing database version management tools can achieve a certain degree of version tracking, their technical implementations still suffer from fundamental flaws. These tools typically require the implantation of dedicated metadata tables within the target database to manage version evolution by recording change histories. This intrusive architectural design not only pollutes the production database structure but also creates a strong coupling between version control and business data. When generating change scripts between discontinuous versions, existing tools can only perform incremental derivation through a linear version chain and cannot directly establish a mapping between any two historical versions. This presents significant functional limitations in complex branching development scenarios.

[0004] Therefore, how to achieve full-process automated generation of change scripts while ensuring the security of the production environment has become a technical problem that technical personnel in this field urgently need to solve. Summary of the Invention

[0005] The embodiments of the present application provide a method, device, and medium for automatically generating a database change script, which are used to solve the following technical problem: how to achieve full-process automatic generation of change scripts while ensuring the security of the production environment.

[0006] In a first aspect, an embodiment of the present application provides a method for automatically generating a database change script, the method comprising: creating a temporary database instance through containerization technology; loading the full SQL definitions of the first version database and the 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 definition, index and constraint information; performing a multi-dimensional comparison of the first metadata set and the second metadata set through a difference analysis engine to identify operation change items; 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 the present application, a temporary database instance is created through containerization technology, specifically including: dynamically starting a lightweight container engine; initializing the container instance based on a preset database image; and deploying a database service process without persistent storage in the container.

[0008] In one embodiment of the present 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 the first metadata set and the second metadata set, specifically including: converting the SQL statement into a structured metadata object through an abstract syntax tree parser; extracting the information to be applied in the metadata object, and constructing a metadata set containing a hierarchical relationship 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.

[0009] In one embodiment of the present application, operation change items include: table-level operations, field-level operations, index operations, and constraint operations; a multi-dimensional comparison is performed on the first metadata set and the second metadata set through a difference analysis engine to identify operation change items, specifically including: traversing and comparing the table name lists in the first metadata set and the second metadata set to identify operations of adding tables, deleting tables, and renaming tables; for tables with the same name, recursively comparing field definitions to identify operations of adding, deleting, renaming, and changing data types of fields; comparing index definitions to identify operations of adding, deleting, and changing field composition; and comparing constraint definitions to identify operations of changing primary keys, foreign keys, and unique constraints.

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

[0011] In one embodiment of the present application, a standardized database change script is generated based on the operation change items, specifically including: generating an atomic change statement with a version identifier for each operation change item, and assembling an upgrade script according to a preset execution order; wherein the preset execution order is: executing structural changes first and then executing data migration, and table-level changes take 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 when a statement execution failure is detected, inserting a reverse compensation statement at the corresponding position in the rollback script; based on the change sequence of the upgrade script, automatically generating a rollback script by arranging in reverse order and adding a rollback identifier.

[0012] In one embodiment of the present application, an atomic change statement with a version identifier is generated for each operation change item, specifically including: when a field data type change operation is identified, first generating an ALTER statement for creating a temporary field, then generating an UPDATE statement for data type conversion, and finally generating a RENAME statement for field renaming; when a change in the index field composition is detected, first executing the old index deletion operation, and then executing the index creation operation containing the new field combination; when generating a foreign key constraint change statement, appending a SET CONSTRAINTS statement for delaying constraint checking after the parent table change statement, and inserting a SET CONSTRAINTS statement for immediate checking at the corresponding position in the rollback script.

[0013] In one embodiment of the present application, when generating a foreign key constraint change statement, the method also includes: parsing the foreign key association relationship graph, and when a cross-table cascading change is detected, generating a change sequence in a bottom-up topological sorting; inserting a table lock 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 an alias qualification for the self-referencing foreign key constraint, and inserting a constraint name mapping relationship table at the corresponding position in the rollback script.

[0014] In a second aspect, an embodiment of the present application also provides a device for automatically generating database change scripts, 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, and the instructions are executed by the at least one processor so that the at least one processor can execute a method for automatically generating a database change script such as any one of the above items.

[0015] In a third aspect, an embodiment of the present application further provides a non-volatile computer storage medium for automatically generating a database change script, which stores computer-executable instructions. When the computer-executable instructions are executed, a method for automatically generating a database change script as described above is implemented.

[0016] The embodiments of the present application provide a method, device and medium for automatically generating database change scripts, which have the following beneficial effects: by constructing a non-invasive architecture analysis mechanism and an intelligent difference deduction engine, the full process automation of database change script generation is realized, which significantly improves the accuracy and execution efficiency of change operations. This technical solution abandons the traditional manual writing mode and completely eliminates metadata pollution to the production environment through containerized isolation technology. At the same time, it adopts a multi-dimensional metadata comparison mechanism to accurately identify subtle changes in tables, fields, indexes and constraints. Combined with atomic statement generation and a two-way verification mechanism, it not only ensures the grammatical correctness of the change script, but also effectively avoids data migration risks through execution order optimization and constraint conflict warning functions. In particular, the adaptive design based on the database dialect plug-in enables the system to be compatible across database platforms, and the automatically generated dual versions of the upgrade / rollback scripts perfectly solve the problem of cascading changes in complex foreign key association scenarios through the reverse compensation mechanism and topological sorting algorithm, providing an enterprise-level solution for database version management that is both secure and flexible. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] The drawings described herein are used to provide a further understanding of the present application and constitute a part of the present application. The illustrative embodiments of the present application and their descriptions are used to explain the present application and do not constitute an improper limitation on the present application. In the drawings:

[0018] Figure 1 A flowchart of a method for automatically generating a database change script provided in an embodiment of the present application;

[0019] Figure 2 A schematic diagram of the internal structure of a device for automatically generating database change scripts provided in an embodiment of the present application. DETAILED DESCRIPTION

[0020] To make the purpose, technical solutions, and advantages of this application more clear, the technical solutions of this application will be clearly and completely described below in conjunction with the specific embodiments of this application and the corresponding drawings. Obviously, the embodiments described are only part of the embodiments of this application, not all of them. Based on the embodiments in this application, all other embodiments obtained by ordinary technicians in this field without making creative efforts are within the scope of protection of this application.

[0021] The embodiments of the present application provide a method, device, and medium for automatically generating a database change script, which are used to solve the following technical problem: how to achieve full-process automatic generation of change scripts while ensuring the security of the production environment.

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

[0023] Figure 1 This is a flow chart of a method for automatically generating a database change script provided in an embodiment of the present application. Figure 1 As shown, an embodiment of the present application provides a method for automatically generating a database change script, which specifically includes the following steps:

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

[0025] In one embodiment of the present application, a temporary database instance is created through containerization technology, specifically including: dynamically starting a lightweight container engine; initializing the container instance based on a preset database image; and deploying a database service process without persistent storage in the container.

[0026] In this embodiment, the containerization technology refers to the dynamic creation of a temporary database instance through a lightweight virtualization engine. Its core purpose is to provide an isolated sandbox environment for the subsequent loading and comparison of full SQL definitions. Specifically, the tool calls the API interface of the container engine (such as Docker, Podman), selects the preset official image according to the database type specified by the user (such as MySQL, PostgreSQL), and creates a temporary container on the host. For example, when the user chooses to generate a change script for MySQL 8.0, the tool will pull the `mysql:8.0` image and start a container instance that only survives in memory based on the image to ensure that the database service process is completely isolated from the host environment.

[0027] It is understandable that this "temporary" feature is manifested in two aspects: first, the lifecycle 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 in the container disables persistent storage, that is, by configuring `tmpfs` or an in-memory file system instead of disk storage to avoid any data residue. For example, by adding the `--rm` flag to the startup command, the container is automatically deleted upon exit, and the database data directory is mounted to the in-memory file system using `--tmpfs / var / lib / mysql`, completely eliminating storage dependence on the host machine.

[0028] It should be noted that the creation process of temporary database instances is uniformly managed by the `TempDBCluster` class built into the tool. This class encapsulates the dynamic allocation of container ports, service health status detection, and error retry mechanism. For example, when multiple comparison tasks are executed in parallel, in order to avoid port conflicts, the tool will randomly assign unoccupied port numbers (such as 3307, 3308) to each container and verify the container's readiness through the `docker inspect` command. In addition, during the database service startup phase, the tool will verify the service availability by executing basic query commands such as `SHOW DATABASES`. If a startup timeout or service abnormality is detected, the container reconstruction process will be automatically triggered.

[0029] In this example, the creation of a temporary database instance provides an independent, clean runtime environment for loading the full SQL definition and parsing metadata in subsequent steps. This containerized isolation avoids the risk of data contamination associated with direct operations on the production database while also supporting the parallel loading and comparison of multiple versions of the architecture (for example, loading both v1 and v3 SQL files simultaneously).

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

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

[0032] In one embodiment of the present 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 the first metadata set and the second metadata set, specifically including: converting the SQL statement into a structured metadata object through an abstract syntax tree parser; extracting the information to be applied in the metadata object, and constructing a metadata set containing a hierarchical relationship 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.

[0033] In this embodiment, the "full SQL definition" refers to a SQL script file that completely describes the database architecture, including creation statements for all tables, fields, indexes, and constraints (such as `CREATE TABLE`, `ALTER TABLE`, etc.). Its source can be a historical version snapshot 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` table) or a custom parser to extract the definition information of database objects.

[0034] For example, when loading the SQL file of the first version (v1), the tool imports the SQL statements one by one into the database instance in the temporary container through the `mysql` command-line client, then executes the `SHOW TABLES` command to obtain a list of all table names, and then executes the `SHOW CREATE TABLE` command for each table 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. It is understandable that this process needs to adapt to the metadata query methods of different database dialects. For example, PostgreSQL needs to use 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, name VARCHAR(255))`) into an object model containing hierarchical relationships: the database object contains multiple table objects, each of which further contains a field list, an index list, and a constraint list. For example, the field object records attributes such as the field name (such as `id`), data type (such as `INT`), and whether it is nullable (`NOT NULL`); the index object records the index name (such as `PRIMARY`), index type (such as `BTREE`), and field composition (such as `id`).

[0036] In this embodiment, the metadata set generated by full SQL loading and parsing provides standardized input for the subsequent difference analysis steps. For example, the first metadata set (v1) and the second metadata set (v3) will serve as the basic data for "multi-dimensional comparison". By recursively traversing the hierarchical structure of tables, fields, etc., operations such as newly added tables and field type changes can be accurately identified. In addition, during the parsing process, the tool ignores comments and version control tags (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: Perform a multi-dimensional comparison on the first metadata set and the second metadata set through a difference analysis engine to identify operation change items.

[0038] In one embodiment of the present application, operation change items include: table-level operations, field-level operations, index operations, and constraint operations; a multi-dimensional comparison is performed on the first metadata set and the second metadata set through a difference analysis engine to identify operation change items, specifically including: traversing and comparing the table name lists in the first metadata set and the second metadata set to identify operations of adding tables, deleting tables, and renaming tables; for tables with the same name, recursively comparing field definitions to identify operations of adding, deleting, renaming, and changing data types of fields; comparing index definitions to identify operations of adding, deleting, and changing field composition; and comparing constraint definitions to identify operations of changing primary keys, foreign keys, and unique constraints.

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

[0040] In this embodiment, the core function of the difference analysis engine is to identify the differences between two metadata sets from multiple dimensions such as table structure, field definition, index and constraint 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 and deleting fields, modifying types, and changing constraints), index operations (such as adding and 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 and identifies 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 item is generated; if the table `users` is renamed to `user_profiles` in v3, an `ALTER TABLE RENAME` operation item is generated (e.g., "Identify new table additions, table deletions, and table rename 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)` and is changed to `price DOUBLE` in v3, an `ALTER TABLE MODIFY COLUMN` operation item is generated; if it is detected that the field `description` has been deleted in v3, an `ALTER TABLE DROP COLUMN` operation item is generated (e.g., "Recursively compare field definitions to identify field addition, deletion, renaming, and data type change operations").

[0042] It is understandable that the comparison of indexes and constraints needs to take into account dependencies and execution order. For example, when a new index `idx_customer_id` is added to the table `orders` in v3, the engine generates a `CREATE INDEX` operation item; if the field composition of the index `idx_status` changes from `status` to `status,created_at`, an atomic operation sequence is generated to first delete the old index and then create a new index (such as "Compare index definitions, identify index additions, deletions, and field composition changes"). 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 prompt to avoid generating invalid scripts.

[0043] It's important to note that the difference analysis engine uses preset priority rules to ensure the order in which operations are generated. For example, table-level operations take precedence over field-level operations to prevent field modification failures due to table non-existence. Constraint changes (such as adding a not-null constraint) must be executed after data is populated. 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 constraint takes effect.

[0044] In this embodiment, through multi-dimensional refined comparison, an accurate and executable change instruction sequence 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 the present application, a standardized database change script is generated based on the operation change items, specifically including: generating an atomic change statement with a version identifier for each operation change item, and assembling an upgrade script according to a preset execution order; wherein the preset execution order is: executing structural changes first and then executing data migration, and table-level changes take 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 when a statement execution failure is detected, inserting a reverse compensation statement at the corresponding position in the rollback script; based on the change sequence of the upgrade script, automatically generating a rollback script by arranging in reverse order and adding a rollback identifier.

[0048] In one embodiment of the present application, an atomic change statement with a version identifier is generated for each operation change item, specifically including: when a field data type change operation is identified, first generating an ALTER statement for creating a temporary field, then generating an UPDATE statement for data type conversion, and finally generating a RENAME statement for field renaming; when a change in the index field composition is detected, first executing the old index deletion operation, and then executing the index creation operation containing the new field combination; when generating a foreign key constraint change statement, appending a SET CONSTRAINTS statement for delaying constraint checking after the parent table change statement, and inserting a SET CONSTRAINTS statement for immediate checking at the corresponding position in the rollback script.

[0049] In one embodiment of the present application, when generating a foreign key constraint change statement, the method also includes: parsing the foreign key association relationship graph, and when a cross-table cascading change is detected, generating a change sequence in a bottom-up topological sorting; inserting a table lock 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 an alias qualification for the self-referencing foreign key constraint, and inserting a constraint name mapping relationship 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 compatibility. Its implementation corresponds to the construction of an operation sequence, positive verification, and rollback logic. Specifically, the tool generates a set of SQL statements that conform to the target database dialect grammar based on the operation change items output by the difference analysis engine according to preset rules, and simultaneously generates a matching rollback script.

[0051] For example, for a field data type change operation (such as changing INT to BIGINT), the tool first generates a temporary transition field creation statement ALTER TABLE users ADD COLUMN temp_id BIGINT, then generates a data migration statement UPDATE users SET temp_id=id, and finally generates a field rename statement ALTER TABLE users CHANGECOLUMN id old_id,temp_id id to ensure lossless data conversion. It should be noted that such 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 the orders table is added and the users table fields are modified, the tool will first output the CREATE TABLE orders statement and then process the ALTER TABLE users related operations to avoid execution errors caused by table dependencies.

[0052] It is understandable that the header of the upgrade script needs to be injected with a version header information block consisting of a version number, timestamp, and digital signature. For example, the generated script header contains comment information such as / *DB Patch Auto-generated at2025-04-20* / and / *SOURCE HASH:a1b2c3d4* / , which is used to trace the source of the script and verify its integrity. In addition, the tool will add forward execution verification logic for each change statement: for example, after executing the ALTER TABLE statement, the SHOW COLUMNS command is immediately used to verify whether the field has been successfully modified. If a failure is detected, the reverse operation is inserted into the corresponding position in the rollback script (such as "forward execution verification of each change statement in the upgrade script").

[0053] When generating rollback scripts, the tool reverses the order of changes in the upgrade script and converts them into reverse operations. For example, CREATE TABLE orders in the upgrade script is converted to DROP TABLE orders 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 new 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 complete. At the same time, the tool generates ALTER TABLE orders DROP FOREIGN KEY fk_order_user and the corresponding unlock instruction in the rollback script to ensure the atomicity of the rollback operation.

[0054] In addition, for self-referencing foreign keys (for example, the manager_id field in the employees table references its own primary key), the tool generates a fully qualified table name with alias qualification (for example, ALTER TABLE employees ADD CONSTRAINT fk_manager_id FOREIGN KEY (manager_id) REFERENCES employees(id) ON DELETE CASCADE). The tool also records the constraint name in the mapping relationship table in the rollback script to prevent rollback failures caused by automatic database naming.

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

[0056] The above is an embodiment of the method proposed in this application. Based on the same inventive concept, this application embodiment also provides an automatic generation device for database change scripts, the structure of which is as follows: Figure 2 shown.

[0057] Figure 2 This is a schematic diagram of the internal structure of an automatic generation device for a database change script provided in an embodiment of the present application. Figure 2 As shown, the equipment includes:

[0058] at least one processor 201;

[0059] and, a memory 202 communicatively coupled to the at least one processor;

[0060] The memory 202 stores instructions that can be executed by at least one processor, and the instructions are executed by the at least one processor 201 to enable the at least one processor 201 to:

[0061] Using containerization technology, a temporary database instance is created; 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 at least includes table structure, field definition, index and constraint information; a multi-dimensional comparison of the first metadata set and the second metadata set is performed using a difference analysis engine to identify operational change items; a standardized database change script is generated based on the operational 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.

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

[0063] Using containerization technology, a temporary database instance is created; 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 at least includes table structure, field definition, index and constraint information; a multi-dimensional comparison of the first metadata set and the second metadata set is performed using a difference analysis engine to identify operational change items; a standardized database change script is generated based on the operational 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.

[0064] The various embodiments in this application are described in a progressive manner. Similar portions between the various embodiments can be referenced to each other. Each embodiment focuses on the differences from the other embodiments. In particular, the IoT device and media embodiments are generally similar to the method embodiments, so their description is relatively simple. For relevant portions, refer to the description of the method embodiments.

[0065] The system and medium provided in the embodiments of the present application correspond one-to-one to the method. Therefore, the system and medium also have similar beneficial technical effects to their corresponding methods. Since the beneficial technical effects of the method have been described in detail above, the beneficial technical effects of the system and medium will not be repeated here.

[0066] Those skilled in the art will appreciate that the embodiments of the present application can be provided as methods, systems, or computer program products. Therefore, the present application can adopt the form of a complete hardware embodiment, a complete software embodiment, or an embodiment in combination with software and hardware. Moreover, the present application can adopt the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to magnetic disk storage, CD-ROM, optical storage, etc.) that contain computer-usable program code.

[0067] The present application is described with reference to the flowcharts and / or block diagrams of the methods, devices (systems), and computer program products according to the embodiments of the present application. It should be understood that each process and / or box in the flowchart and / or block diagram, as well as the combination of the processes and / or boxes in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the steps in the process. Figure 1 a process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0068] These computer program instructions may also be stored in a computer readable memory that can direct a computer or other programmable data processing device to work in a specific manner, so that the instructions stored in the computer readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 a process or multiple processes and / or boxes Figure 1 The function specified in one or more boxes.

[0069] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operational steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing the instructions executed on the computer or other programmable device for implementing the process. Figure 1 a process or multiple processes and / or boxes Figure 1 A step that specifies a function in one or more boxes.

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

[0071] Memory may include non-permanent storage in a computer-readable medium, random access memory (RAM) and / or non-volatile memory in the form of read-only memory (ROM) or flash RAM. Memory is an example of a computer-readable medium.

[0072] Computer-readable media includes permanent and non-permanent, removable and non-removable media that can be implemented by any method or technology to store information. The information can be computer-readable instructions, data structures, program modules, 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 technology, compact disc read-only memory (CD-ROM), digital versatile disc (DVD) or other optical storage, magnetic cassettes, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other non-transmission media that can be used to store information that can be accessed by a computing device. As defined herein, computer-readable media does not include transitory computer-readable media (transitory media), such as modulated data signals and carrier waves.

[0073] It should also be noted that the terms "comprises," "includes," or any other variations thereof are intended to encompass non-exclusive inclusion, such that a process, method, commodity, or apparatus that includes a series of elements includes not only those elements but also other elements not explicitly listed, or includes elements inherent to such process, method, commodity, or apparatus. In the absence of further limitations, an element defined by the phrase "comprises a ..." does not exclude the presence of other identical elements in the process, method, commodity, or apparatus that includes the element.

[0074] The foregoing is merely an embodiment of the present application and is not intended to limit the present application. For those skilled in the art, the present application may have various changes and variations. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principles of the present application should all be included within the scope of the claims of the present application.

Claims

1. A method for automatically generating a database change script, characterized in that: The method comprises: Create a temporary database instance through containerization technology; Loading the full SQL definitions of the first version database and the 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 at least includes table structure, field definition, index, and constraint information; Performing a multi-dimensional comparison of the first metadata set and the second metadata set using a difference analysis engine to identify operation change items; A standardized database change script is generated according to the operation change item; 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.

2. The method for automatically generating a database change script according to claim 1, wherein: Use containerization technology to create a temporary database instance, including: Dynamically start lightweight container engine; Initialize a container instance based on a pre-configured database image; Deploy a database service process without persistent storage in a container.

3. The method for automatically generating a database change script according to claim 1, wherein: Loading 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, specifically including: Convert SQL statements into structured metadata objects through an abstract syntax tree parser; Extract the information to be applied from the metadata object, and construct a metadata set containing a hierarchical relationship 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.

4. The method for automatically generating a database change script according to claim 1, wherein: The operation change items include: table-level operations, field-level operations, index operations and constraint operations; Performing a multi-dimensional comparison of the first metadata set and the second metadata set by a difference analysis engine to identify operation changes specifically includes: Traversing and comparing the table name lists in the first metadata set and the second metadata set to identify operations of adding a table, deleting a table, and renaming a table; For tables with the same name, recursively compare field definitions to identify field addition, deletion, renaming, and data type change operations; Compare index definitions to identify index additions, deletions, and field composition changes; Compare constraint definitions and identify changes to primary keys, foreign keys, and unique constraints.

5. The method for automatically generating a database change script according to claim 4, characterized in that: The method further comprises: When comparing constraint definitions, if it is detected that the parent table field associated with the foreign key has been deleted, the script generation is terminated and a constraint conflict warning is issued; When a new NOT NULL constraint is detected but a null value exists in the target field, a default fill statement is automatically inserted.

6. The method for automatically generating a database change script according to claim 1, wherein: Generate a standardized database change script based on the operation change item, specifically including: Generates an atomic change statement with a version identifier for each operational change item, and assembles the upgrade script according to the preset execution order; wherein the preset execution order is: execute structural changes first, then perform data migration, and table-level changes take precedence over field-level changes; Inject the version header information block consisting of the version number, generation timestamp and digital signature into the upgrade script header; Perform forward execution verification on each change statement in the upgrade script. If a statement execution failure is detected, insert a reverse compensation statement at the corresponding location in the rollback script. Based on the change sequence of the upgrade script, a rollback script is automatically generated by reversing the change sequence and adding a rollback identifier.

7. The method for automatically generating a database change script according to claim 6, characterized in that: Generates an atomic change statement with a version identifier for each operation change item, including: When a field data type change operation is identified, an ALTER statement is generated to create a temporary field, followed by an UPDATE statement to convert the data type, and finally a RENAME statement to rename the field. When a change in the index field composition is detected, the old index is deleted first, and then the index containing the new field combination is created; When generating a foreign key constraint change statement, append a SET CONSTRAINTS statement to delay constraint checking after the parent table change statement, and insert a SET CONSTRAINTS statement for immediate checking at the corresponding position in the rollback script.

8. The method for automatically generating a database change script according to claim 7, characterized in that: When generating the foreign key constraint change statement, the method further includes: Parse the foreign key relationship graph and generate a change sequence by bottom-up topological sorting when cross-table cascading changes are detected; Insert a table lock instruction before the parent table constraint change statement, and append an unlock instruction after the child table change is completed; Generates a fully qualified table name with alias qualification for self-referencing foreign key constraints and inserts the constraint name mapping relationship table into the corresponding position in the rollback script.

9. A device for automatically generating database change scripts, characterized in that: The device comprises: at least one processor; and, a memory communicatively coupled to the at least one processor; The memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor so that the at least one processor can execute the method for automatically generating a database change script according to any one of claims 1 to 8.

10. A non-volatile computer storage medium for automatically generating a database change script, storing computer-executable instructions, characterized in that: When the computer-executable instructions are executed, the method for automatically generating a database change script according to any one of claims 1 to 8 is implemented.

Images