Cross-database upgrade script generation method and device, equipment and medium

Abstract

The application belongs to the technical field of database maintenance, and discloses a cross-database upgrade script generation method, device, equipment and medium, which comprises the following steps: uniformly modeling a first database and a second database to obtain a first database model and a second database model; if the types of the first database and the second database are different, performing de-differentiation processing on the first database model and the second database model according to the types of the first database and the second database; comparing the database structures of the first database model and the second database model to obtain a database structure difference; and obtaining an upgrade script according to the database structure difference, the first database model and the type of the second database. The cross-database upgrade script can be quickly and efficiently generated.

Description

Technical Field

[0001] This invention belongs to the field of database maintenance technology, specifically relating to a method, apparatus, device, and medium for generating cross-database upgrade scripts. Background Technology

[0002] Large software programs typically need to be compatible with multiple databases (such as SQLite, MySQL, TiDB, DM, etc.). During version management, cross-database upgrade scripts are required to initialize and upgrade different databases.

[0003] However, the following issues currently exist when generating cross-database upgrade scripts:

[0004] 1. Insufficient cross-database compatibility, limited support for a single database type, upgrade logic designed only for specific databases (such as SQL Server, SQLite), and failure to consider cross-database upgrade script generation.

[0005] 2. High reliance on manual labor and high maintenance costs: upgrade scripts need to be written or verified manually, which can easily lead to errors due to human negligence; the functions of the interface tools are limited. Although they can provide interface to generate scripts, they do not consider version merging and integration of intelligent decision-making (such as conflict detection). When merging branches, parameters and logic still need to be adjusted manually.

[0006] 3. The functionality is not comprehensive. It only considers the generation of upgrade scripts across databases within the same branch, and does not consider support for merging and switching between databases of different branches. Summary of the Invention

[0007] The purpose of this invention is to provide a method, apparatus, device, and storage medium for generating cross-database upgrade scripts quickly and efficiently.

[0008] The first aspect of this invention discloses a method for generating cross-database upgrade scripts, comprising:

[0009] A unified model is performed on the first database and the second database to obtain the first database model and the second database model;

[0010] If the types of the first database and the second database are different, then the first database model and the second database model are dedifferentiated according to the types of the first database and the second database.

[0011] Compare the database structure of the first database model with the database structure of the second database model to obtain the differences in database structure;

[0012] An upgrade script is obtained based on the differences in database structure, the first database model, and the type of the second database.

[0013] In some embodiments, after obtaining the upgrade script, the method further includes:

[0014] Generate an upgraded database based on the upgrade script, and obtain migration SQL to migrate data from the first database to the upgraded database;

[0015] Obtain the merge SQL statement that combines the data from the upgraded database and the data from the second database;

[0016] Add the migration SQL and the merge SQL to the upgrade script.

[0017] In some embodiments, merging the data of the upgraded database and the data of the second database includes:

[0018] The differences between the upgraded database and all data in each table of the second database are analyzed, and the data is merged according to a pre-set conflict handling strategy. The conflict handling strategy includes: analyzing the tables in the second database that contain default factory data, dividing the default factory data into general data and exclusive data based on whether the user will modify it; for the general data, the default factory data is used as the basis for merging; for the exclusive data, the data in the upgraded database is used as the basis for merging.

[0019] In some embodiments, the first database model and the second database model are subjected to de-differentiation processing based on the type of the first database and the type of the second database, including:

[0020] To address the differences in field types, the field types are categorized to obtain common definitions. A first mapping matrix is ​​established between the field types of the first database model and the common definitions based on the type of the first database. A second mapping matrix is ​​established between the field types of the second database model and the common definitions based on the type of the second database. Based on the first and second mapping matrices, it is determined whether the database fields that differ between the first and second database models are the same.

[0021] For differences in the length of index fields and index names, index aliases are generated based on the index fields according to a unified naming rule and stored in the first database model or the second database model.

[0022] In some embodiments, determining whether database fields that differ between the first database model and the second database model are the same, based on the first mapping matrix and the second mapping matrix, includes:

[0023] The database fields in the first database model that differ from the second database model are set as the first difference fields; the database fields in the second database model that differ from the first database model are set as the second difference fields.

[0024] The first difference field and the second difference field are determined to be the same when the common definition corresponding to the field type of the first difference field is found in the second mapping matrix according to the common definition in the first mapping matrix.

[0025] In some embodiments, obtaining the upgrade script based on the database structure differences, the first database model, and the type of the second database includes:

[0026] For the type of the second database, predetermine the SQL generation rules for adding, deleting, and modifying stored procedures, tables, views, triggers, and table data;

[0027] Based on the SQL generation rules, the upgrade script is generated according to the differences in database structure and the first database model.

[0028] In some embodiments, a unified modeling is performed on the first database and the second database to obtain a first database model and a second database model, including:

[0029] Query the first system table information of the first database, or obtain the first database schema definition language of the first database, parse the first database schema definition language, and obtain the first system table information; generate the first database model based on the first system table information according to the pre-built unified database model.

[0030] Query the second system table information of the second database, or obtain the second database schema definition language of the second database, parse the second database schema definition language, and obtain the second system table information; based on the pre-built unified database model, generate the second database model according to the second system table information.

[0031] A second aspect of the present invention discloses a cross-database upgrade script generation apparatus, comprising:

[0032] The metadata extraction module is used to perform unified modeling of the first database and the second database to obtain the first database model and the second database model.

[0033] The metadata processing module is used to perform dedifferentiation processing on the first database model and the second database model according to the type of the first database and the type of the second database if the types of the first database and the second database are different.

[0034] The difference calculation module is used to compare the database structure of the first database model and the database structure of the second database model to obtain the differences in database structure.

[0035] The structure upgrade module is used to obtain an upgrade script based on the differences in the database structure, the first database model, and the type of the second database.

[0036] A third aspect of the present invention discloses an electronic device, including a memory storing executable program code and a processor coupled to the memory; the processor calls the executable program code stored in the memory to execute the cross-database upgrade script generation method disclosed in the first aspect.

[0037] A fourth aspect of the present invention discloses a computer-readable storage medium storing a computer program, wherein the computer program causes a computer to execute the cross-database upgrade script generation method disclosed in the first aspect.

[0038] The beneficial effects of this invention are as follows: By uniformly modeling the first and second databases, a first database model and a second database model are obtained. The first and second database models can be compared based on metadata (table structure, indexes, constraints, etc.) to identify database structure differences. Differentiation removal processing is performed for different types of first and second databases, reducing script error rates. Based on the unified database model, upgrade scripts are automatically generated according to database structure differences and the types of the first and second database models, reducing manual writing and verification costs and improving efficiency. Automatic conversion of syntax differences across databases is achieved, eliminating the need for manual learning of multiple sets of SQL syntax. Attached Figure Description

[0039] The accompanying drawings illustrate specific examples of the technical solutions described in this invention and, together with the detailed embodiments, form part of the specification, serving to explain the technical solutions, principles, and effects of this invention.

[0040] Unless otherwise specified or defined, the same reference numerals in different figures represent the same or similar technical features, and different reference numerals may be used to represent the same or similar technical features.

[0041] Figure 1 This is a flowchart of a cross-database upgrade script generation method disclosed in an embodiment of the present invention;

[0042] Figure 2 This is a schematic diagram of the mapping matrix between database field types and public definitions in an embodiment of the present invention;

[0043] Figure 3 This is a flowchart of the process after obtaining the upgrade script in an embodiment of the present invention;

[0044] Figure 4 This is a schematic diagram of the cross-database upgrade script generation device according to an embodiment of the present invention;

[0045] Figure 5 This is a schematic diagram of the structure of an electronic device according to an embodiment of the present invention. Detailed Implementation

[0046] Unless otherwise specified or defined, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art. When combined with the technical solutions of the invention in a real-world scenario, all technical and scientific terms used herein may also have meanings corresponding to the purpose of achieving the technical solutions of the invention. The terms "first," "second," etc., used herein are merely for distinguishing names and do not represent a specific number or order. The term "and / or" as used herein includes any and all combinations of one or more of the associated listed items.

[0047] It should be noted that when a component is considered "fixed" to another component, it can be directly fixed to the other component or there can be an intervening component; when a component is considered "connected" to another component, it can be directly connected to the other component or there can be an intervening component; when a component is considered "mounted" on another component, it can be directly mounted on the other component or there can be an intervening component; when a component is considered "placed" on another component, it can be directly placed on the other component or there can be an intervening component.

[0048] Unless otherwise specified or defined, the terms "described" or "the" as used herein refer to the technical features or technical content mentioned or described prior to the relevant section, which may be the same as or similar to the technical features or technical content mentioned herein. Furthermore, the terms "comprising" and "having," and any variations thereof, as used herein, are intended to cover non-exclusive inclusion. For example, a process, method, system, product, or apparatus that includes a series of steps or units is not limited to the steps or units listed, but may optionally include steps or units not listed, or may optionally include other steps or units inherent to such processes, methods, products, or apparatus.

[0049] To facilitate understanding of the present invention, specific embodiments of the present invention will be described in more detail below with reference to the accompanying drawings.

[0050] This invention discloses a method for generating cross-database upgrade scripts, which can be implemented through computer programming as an independent detection system. The execution subject of this method can be an electronic device such as a computer, laptop, or tablet, or a control chip embedded in an electronic device; this invention does not limit this to any particular type.

[0051] like Figure 1 As shown, the method includes the following steps:

[0052] Step S100: Perform unified modeling on the first database and the second database to obtain the first database model and the second database model;

[0053] The first and second databases can be different types of databases, for example, the first database is SQLite and the second database is MySQL; they can also be databases of the same type but different versions. The first database needs to be upgraded to the second database.

[0054] First, a unified model is constructed for the first and second databases. This involves abstracting the first and second databases to generate a unified database model whose structure is compatible with both the first and second database structures. Then, based on this unified database model, the first and second databases are transformed into their respective models. This involves parsing the first and second databases, extracting their information, and incorporating it into the unified database model, thus forming the first and second database models.

[0055] Specifically, when generating a unified database model, abstraction is performed from two dimensions: database information and table information. Database information includes: database characteristics, database storage structure, and database table views; table information includes: table structure, table indexes, and table triggers. The table structure includes table name, fields, primary key, unique index, etc.; field attributes include: field name, field type, whether it is a primary key, whether it is a foreign key, foreign key target table, foreign key field in the current table, foreign key related fields, operations on the current table when updating the foreign key related table, operations on the current table when deleting the foreign key related table, foreign key matching method, whether it is NOT null, whether it is checked, check constraints, whether it has a default value, default value, whether it is auto-incrementing, etc.

[0056] After generating the unified database model, the first system table information can be obtained by querying the first system table information of the first database, or by obtaining the first database schema definition language (DDL) of the first database, parsing the first database schema definition language, and then inputting the first system table information into the unified database model. Similarly, the second system table information can be obtained by querying the second system table information of the second database, or by obtaining the second database schema definition language (DDL) of the second database, parsing the second database schema definition language, and then inputting the second system table information into the unified database model.

[0057] Step S200: If the types of the first database and the second database are different, then perform dedifferentiation processing on the first database model and the second database model according to the types of the first database and the second database.

[0058] When the types of the first database and the second database are different, it means that even if the two database fields are actually the same and are used to store the same content, their field types, index names, index lengths, etc. may be different. In other words, although these database fields are different, they should still be treated as the same database fields during the upgrade and merging process. Therefore, when the types of the first database and the second database are different, de-differentiation processing is required.

[0059] Specifically, considering the differences in field types (database fields with the same name but different types), all field types are categorized to obtain common definitions (i.e., abstracted field types). For example, the field types supported by different types of databases that need to support cross-database upgrade scripts (such as the first and second databases in this embodiment) are summarized and common definitions are created. Then, a first mapping matrix between the field types of the first database model and the common definitions is established based on the type of the first database; a second mapping matrix between the field types of the second database model and the common definitions is established based on the type of the second database. The mapping matrix between the field types of the database and the common definitions can be referenced. Figure 2 As shown. Then, based on the first mapping matrix and the second mapping matrix, it is determined whether the database fields with type differences between the first database model and the second database model are the same. When they are the same, a mapping relationship can be established to identify the correlation between the differentiated fields during the subsequent upgrade script generation, thereby achieving the purpose of dedifferentiation.

[0060] The specific steps for determining whether database fields with different types are the same include:

[0061] Set the database fields in the first database model that have type differences from the second database model as the first difference field; set the database fields in the second database model that have type differences from the first database model as the second difference field;

[0062] Then, based on the first mapping matrix, the common definition corresponding to the field type of the first difference field is found to obtain the common definition information. When the field type of the second difference field is found in the second mapping matrix according to the common definition information, it is determined that the first difference field and the second difference field are the same. That is, when the field types of the first difference field and the second difference field are mapped to the same common definition, the first difference field and the second difference field are considered to be the same.

[0063] In the first and second mapping matrices, the relationship between public definitions and database field types can be 1:N, N:1, or 1:1. If the relationship is 1:N, the first field type is retrieved by default when obtaining the field type from the public definition, unless otherwise specified. If the relationship is N:1, all public definitions corresponding to the field type are retrieved to obtain a public definition list. Subsequent comparisons of field types are made only if the target field type exists in the public definition list; otherwise, they are considered identical. For field types not supported by the database, the mapping depends on the business system and whether there is an adaptation mechanism. If there is, the mapping is performed according to the adapted field type. If there is no adaptation mechanism, upgrades can only be performed within the same database type, not across different database types.

[0064] To address the differences in index name length, index aliases are generated based on the index fields according to a unified naming convention and stored in either the first or second database model. This allows for index comparison based on index aliases, determining whether two indexes correspond to each other. Furthermore, when generating indexes for the second database based on the unified data model, valid index names for the second database are generated according to pre-configured index length limits for different database types (which can be configured individually for each database table to meet specific requirements).

[0065] For handling length differences in index fields, the same method as for handling length differences in index names is used, namely, the alias + configuration method, to identify identical index fields and generate target index fields.

[0066] Step S300: Compare the database structure of the first database model with the database structure of the second database model to obtain the differences in database structure;

[0067] Database structure includes database views, table structure, storage structure, etc. During comparison, the second database model is used as a reference. The first and second database models are traversed to obtain information on additions, deletions, and modifications made to the second database model relative to the first database model in terms of database characteristics, database storage structure, database table views, and table structure, thus revealing the differences in database structure.

[0068] Step S400: Obtain the upgrade script based on the differences in database structure, the first database model, and the type of the second database.

[0069] Pre-write SQL generation rules for different types of databases to perform addition, deletion, and modification operations on stored procedures, tables, views, triggers, and table data.

[0070] Based on the differences in database structure and the first database model, combined with SQL generation rules specifically designed for the second database type, which allow for adding, deleting, and modifying stored procedures, tables, views, triggers, and table data, an upgrade script is generated that matches the database type of the second database. Inputting this upgrade script into the first database model generates the DDL and DML statements for the database corresponding to the second database type, thus upgrading the database structure of the first database to that of the second database.

[0071] In summary, by abstracting and modeling different types of databases and comparing metadata (table structure, indexes, constraints, etc.), initialization and upgrade scripts are automatically generated, reducing manual writing and verification costs and improving efficiency. It achieves automatic conversion of syntax differences across databases (SQLite, MySQL, DM, Kingbase, etc.), eliminating the need for manual learning of multiple SQL syntaxes, significantly reducing script error rates and minimizing the risk of upgrade failures due to syntax incompatibility. It also supports incremental upgrade script generation, handling only the differences (such as adding fields or modifying constraints), avoiding the execution overhead of full scripts.

[0072] In some embodiments, such as Figure 3 As shown, after obtaining the upgrade script, it also includes:

[0073] Step S500: Generate the upgraded database according to the upgrade script, and obtain the migration SQL to migrate the data from the first database to the upgraded database;

[0074] First, an upgrade script is executed based on the first database model to upgrade the database structure and generate the upgraded database. Then, data from the first database is migrated into the new tables after the structure upgrade, while the SQL generation module outputs the migration SQL. This SQL generation module contains SQL generation rules based on a unified database model, combined with rules written for different types of databases for adding, deleting, and modifying stored procedures, tables, views, triggers, and table data, enabling the generation of SQL statements for a specified database.

[0075] Special handling is required if the following situations occur during the migration process:

[0076] 1. When a field name changes, the old field data needs to be inserted into the new field. Read the field mapping configuration to find the corresponding new field. When a field type changes, the old field data needs to be converted to the data corresponding to the new field type. Use CAST (SQL standard function) for conversion. When a field is changed to unique, only unique data can be inserted into the old field data. Use DISTINCT to insert unique data from the old data into the new table. The temporary table storing the old data should not be deleted; any discarded data will need to be manually processed later if needed. When a field is associated with a foreign key, disable the foreign key constraint first, migrate the data, and then enable the foreign key constraint. When a field is changed to NOT NULL, if there are null values ​​in the old data, read the field configuration to get the default value (the default value is an empty string) and then insert it into the new table. When a field is changed to have a check constraint, only data that meets the check constraint should be inserted into the old data. The temporary table storing the old data should not be deleted; any discarded data will need to be manually processed later if needed.

[0077] 2. When adding a non-nullable field, the table needs to be rebuilt, and the field configuration should be read to retrieve the default value (the default value is an empty string); when adding a foreign key field, the foreign key constraint should be turned off first, and then enabled after the operation is completed; when adding a unique field, the table needs to be rebuilt, the old data should be stored in a temporary table first, the new table should be created, and the data should not be imported until manual processing is completed; when adding data with check constraints, the table needs to be rebuilt, the old data should be stored in a temporary table first, the new table should be created, and the data should not be imported until manual processing is completed.

[0078] Step S600: Obtain the merge SQL for the data in the merged and upgraded database and the data in the second database;

[0079] Analyze the differences in all data in each table of the upgraded database and the second database. Based on the pre-set conflict handling strategy, merge the data in the upgraded database and the second database after the database structure has been upgraded. At the same time, use the SQL generation module to output the merge SQL for merging the data.

[0080] Specifically, the tables in the second database containing default factory data are analyzed beforehand. From a business perspective, the default factory data is divided into general data (system configuration type, which users will not modify) and proprietary data (which users will modify) based on whether users will modify it. For general data, the default factory data in the second database is used during merging; for proprietary data, the data in the upgraded database is used. Database tables are marked according to this strategy, and data merging strategies are configured so that merging is performed according to these strategies. During the data merging process, the differences between all data in each table of the two databases are compared, and data is merged using pre-set conflict handling strategies. Conflicts that cannot be automatically handled are marked as unresolved, and the default conflict handling or manual handling is determined based on the configuration. When manual handling is required, a manual handling interface pops up, displaying and processing the conflicting data, allowing for manual data merging on the interface.

[0081] For data in the same table, where some data needs to be used from the upgraded database and some from the second database, if it is known in advance which primary keys or fields will use the second database, then mark them; otherwise, handle them manually.

[0082] If the data in the table is in JSON format, and the JSON structure of the upgraded database differs from that of the second database, the upgraded database's JSON structure will be processed according to the second database's JSON structure. Newly added fields will be processed with default values ​​based on their type. If this cannot be handled, manual processing will be required. If the data in the table contains non-standard SQL statements, and the statements differ between different database types, manual processing will also be required.

[0083] Step S700: Add the migration SQL and merge SQL to the upgrade script.

[0084] Then, the migration SQL and merge SQL are added to the upgrade script to obtain an upgrade script that can simultaneously upgrade the database and merge data.

[0085] By adding migration and merge SQL statements, the upgrade script supports end-to-end operations from database structure upgrades to data migration, meeting the needs of complex operation and maintenance scenarios and providing data recovery functionality for rapid recovery after upgrade failures. In contrast, current cross-database upgrade scripts have a single upgrade dimension, only supporting database structure upgrades and ignoring supporting operations such as data migration, thus failing to meet comprehensive operation and maintenance requirements.

[0086] like Figure 4 As shown, based on the above-described cross-database upgrade script generation method, this embodiment of the invention discloses a cross-database upgrade script generation device, comprising:

[0087] Metadata extraction module 600 is used to perform unified modeling of the first database and the second database to obtain the first database model and the second database model;

[0088] Metadata processing module 610 is used to perform dedifferentiation processing on the first database model and the second database model according to the type of the first database and the type of the second database if the types of the first database and the second database are different.

[0089] The difference calculation module 620 is used to compare the database structure of the first database model and the database structure of the second database model to obtain the differences in database structure.

[0090] The structure upgrade module 630 is used to obtain an upgrade script based on the differences in the database structure, the first database model, and the type of the second database.

[0091] In some embodiments, the system further includes a data migration module 640, a data merging module 650, an SQL generation module 660, and a database operation module 670. The data migration module 640 migrates data from the first database to the upgraded database; the data merging module 650 merges data from the upgraded database and the second database; the SQL generation module 660 generates SQL statements for a specified database based on a unified data structure and SQL generation rules written for different types of databases, allowing for the addition, deletion, and modification of stored procedures, tables, views, triggers, and table data. By inputting a unified database model, DDL and DML statements for the corresponding database type can be generated; the database operation module 670 integrates drivers for different types of databases to operate on the database and execute SQL statements.

[0092] The cross-database upgrade script generation device and cross-database upgrade script generation method of the present invention can achieve the following three functions: automatically generating database creation SQL statements across databases, automatically generating upgrade scripts across databases, and automatically upgrading across branch databases.

[0093] Among them, the automatic generation of database creation SQL statements across databases is: based on a specified type of base database, generating initialization database scripts for different types of databases.

[0094] The specific process of automatically generating database creation SQL statements across databases is as follows:

[0095] The metadata extraction module parses the baseline database based on unified database modeling, extracting information into the unified database model. The metadata processing module performs de-differentiation processing on the database model output by the metadata extraction module according to the database type. The SQL generation module, based on the data structure of the unified database model and combined with SQL generation rules written for different types of databases for adding, deleting, and modifying stored procedures, tables, views, triggers, and table data, generates all the SQL statements required to convert the data structure of the unified database model into the database creation statements required for different types of databases.

[0096] The automatic generation of upgrade scripts across databases involves comparing the source database and the target database to generate cross-database upgrade scripts for different types of databases.

[0097] The specific process of automatically generating upgrade scripts across databases is as follows: The metadata extraction module parses the information from both databases to generate a unified database model for the source and target databases. If the source and target databases are of the same type, this step is skipped; otherwise, the metadata processing module performs de-differentiation processing on the database model output by the metadata extraction module, based on the database type. The difference calculation module compares the data models of the source and target databases to calculate the differences between them. The SQL generation module backs up the old tables of the source database to a temporary table, and then uses the SQL generated from the difference calculation module to generate the new tables. The structural differences between the source and target databases are analyzed. Combined with SQL generation rules for different database types, the changes are converted into upgrade SQL statements for each database type and output to the upgrade script. Then, using the database operation module and the SQL statements generated by the SQL generation module, the old tables in the source database are backed up to temporary tables, and the old tables are then structurally upgraded. The data migration module migrates data from the old tables in the source database into the new tables after the structural upgrade, outputting the migration SQL to the upgrade script. Finally, the data merging module merges the upgraded source and target databases, outputting the merge SQL to the upgrade script.

[0098] In this context, automatic database upgrades across branches mean that when there are multiple branches in the program code, the corresponding database structure will also have multiple branches. When merging program code, the merging of the corresponding databases will be involved.

[0099] The specific steps for automatic cross-branch database upgrades are similar to those for "automatic generation of upgrade scripts across databases." Essentially, it isolates differences through a standardized abstraction layer and automatically converts them using written rules, achieving a "one-time modeling, multi-device adaptation" database management solution. This method of automatically calculating database differences can be used for upgrading databases on different branches.

[0100] The specific process of automatic cross-branch database upgrade is as follows:

[0101] The metadata parsing module parses the information from the two branch databases to generate a unified database model for the source and target databases. If the source and target databases are of the same type, this step is skipped. Otherwise, the metadata processing module performs de-differentiation processing on the database model output by the metadata extraction module, based on the database type. The difference calculation module compares the database models of the source and target databases to calculate the differences between them. The SQL generation module backs up the old tables of the source database to a temporary table. Then, the structural difference results of the source and target databases output by the difference calculation module are combined with the SQL generation rules for different database types to convert the database structure changes into upgrade SQL statements for different database types. Next, the database operation module, combined with the SQL statements generated by the SQL generation module, backs up the old tables of the source database to a temporary table and finally upgrades the structure of the old tables. The data migration module migrates the data from the old tables of the source database into the new tables after the structure upgrade. Finally, the data merging module merges the data from the source and target databases that have been upgraded.

[0102] By abstracting and modeling the database, the comparison of branch version differences becomes independent of the database type, enabling automatic identification of conflict points and providing merging suggestions through the rule engine, reducing manual intervention; it also supports merging of multi-branch database structures, ensuring data consistency during version switching.

[0103] Furthermore, it adapts to different database types through independent driver modules, allowing new database support to be added simply by extending the driver library without refactoring the core logic; it provides a unified operation interface for easy integration and use by third-party systems. It also supports dynamically updated syntax conversion rule bases to continuously adapt to database version upgrades, ensuring long-term availability.

[0104] like Figure 5 As shown, an embodiment of the present invention discloses an electronic device, including a memory 401 storing executable program code and a processor 402 coupled to the memory 401;

[0105] The processor 402 calls the executable program code stored in the memory 401 to execute the cross-database upgrade script generation method described in the above embodiments.

[0106] This invention also discloses a computer-readable storage medium storing a computer program that causes a computer to execute the cross-database upgrade script generation method described in the above embodiments.

[0107] The purpose of the above embodiments is to reproduce and derive the technical solution of the present invention by way of example, and to fully describe the technical solution, purpose and effect of the present invention. The purpose is to enable the public to have a more thorough and comprehensive understanding of the disclosure of the present invention, and not to limit the scope of protection of the present invention.

[0108] The above embodiments are not an exhaustive list based on the present invention, and there may be many other embodiments not listed. Any substitutions and improvements made without departing from the concept of the present invention are within the protection scope of the present invention.

Claims

1. A method for generating cross-database upgrade scripts, characterized in that, include: A unified model is performed on the first database and the second database to obtain the first database model and the second database model; If the types of the first database and the second database are different, then the first database model and the second database model are dedifferentiated according to the types of the first database and the second database. Compare the database structure of the first database model with the database structure of the second database model to obtain the differences in database structure; Based on the differences in database structure, the first database model, and the type of the second database, an upgrade script is obtained; After obtaining the upgrade script, it also includes: Generate an upgraded database based on the upgrade script, and obtain migration SQL to migrate data from the first database to the upgraded database; Obtain the merge SQL statement that combines the data from the upgraded database and the data from the second database; Add the migration SQL and the merge SQL to the upgrade script; Merging the data from the upgraded database and the data from the second database includes: The differences between the upgraded database and all data in each table of the second database are analyzed, and the data is merged according to a pre-set conflict handling strategy. The conflict handling strategy includes: analyzing the tables in the second database that contain default factory data, dividing the default factory data into general data and exclusive data based on whether the user will modify it; for the general data, the default factory data is used as the basis for merging; for the exclusive data, the data in the upgraded database is used as the basis for merging.

2. The cross-database upgrade script generation method as described in claim 1, characterized in that, Based on the types of the first database and the second database, the first database model and the second database model undergo a de-differentiation process, including: To address the differences in field types, the field types are categorized to obtain common definitions. A first mapping matrix is ​​established between the field types of the first database model and the common definitions based on the type of the first database. A second mapping matrix is ​​established between the field types of the second database model and the common definitions based on the type of the second database. Based on the first and second mapping matrices, it is determined whether the database fields that differ between the first and second database models are the same. For differences in the length of index fields and index names, index aliases are generated based on the index fields according to a unified naming rule and stored in the first database model or the second database model.

3. The cross-database upgrade script generation method as described in claim 2, characterized in that, Based on the first mapping matrix and the second mapping matrix, determine whether the database fields that differ between the first database model and the second database model are the same, including: The database fields in the first database model that differ from the second database model are set as the first difference fields; the database fields in the second database model that differ from the first database model are set as the second difference fields. The common definition corresponding to the field type of the first difference field is found according to the first mapping matrix to obtain the common definition information. When the field type of the second difference field is found in the second mapping matrix according to the common definition in the common definition information, it is determined that the first difference field and the second difference field are the same.

4. The cross-database upgrade script generation method as described in claim 1, characterized in that, The step of obtaining the upgrade script based on the differences in database structure, the first database model, and the type of the second database includes: For the type of the second database, predetermine the SQL generation rules for adding, deleting, and modifying stored procedures, tables, views, triggers, and table data; Based on the SQL generation rules, the upgrade script is generated according to the differences in database structure and the first database model.

5. The cross-database upgrade script generation method as described in claim 1, characterized in that, A unified modeling process is performed on the first and second databases to obtain a first database model and a second database model, including: Query the first system table information of the first database, or obtain the first database schema definition language of the first database, parse the first database schema definition language, and obtain the first system table information; generate the first database model based on the first system table information according to the pre-built unified database model. Query the second system table information of the second database, or obtain the second database schema definition language of the second database, parse the second database schema definition language, and obtain the second system table information; based on the pre-built unified database model, generate the second database model according to the second system table information.

6. A cross-database upgrade script generation apparatus, used to execute the cross-database upgrade script generation method as described in claim 1, characterized in that, include: The metadata extraction module is used to perform unified modeling of the first database and the second database to obtain the first database model and the second database model. The metadata processing module is used to perform dedifferentiation processing on the first database model and the second database model according to the type of the first database and the type of the second database if the types of the first database and the second database are different. The difference calculation module is used to compare the database structure of the first database model and the database structure of the second database model to obtain the differences in database structure. The structure upgrade module is used to obtain an upgrade script based on the differences in the database structure, the first database model, and the type of the second database.

7. An electronic device, characterized in that, It includes a memory storing executable program code and a processor coupled to the memory; the processor calls the executable program code stored in the memory to execute the cross-database upgrade script generation method according to any one of claims 1 to 5.

8. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program, wherein the computer program causes a computer to execute the cross-database upgrade script generation method according to any one of claims 1 to 5.

Images