Multi-tenant database update method and system

Abstract

The application relates to a multi-tenant database updating method and system. The method realizes standardized representation of heterogeneous database attributes by constructing a table structure model containing a label dictionary, and solves the logical conflict problem of multi-tenant same-name table definition by using type compatible logic to perform conflict merging. In addition, the accuracy of heterogeneous syntax analysis and SQL synthesis is ensured by cooperating with the semantic conversion technology of expression tree technology. Furthermore, the technical controllability of the database change process and the system running stability are improved while reducing the complexity of the heterogeneous environment adaptation by combining the risk score quantization judgment based on the operation action and the data scale.

Description

Technical Field

[0001] This application relates to the field of database management technology, and in particular to a method and system for updating a multi-tenant database. Background Technology

[0002] With the continuous advancement of information technology application innovation, enterprise-level application systems are gradually adapting to various heterogeneous relational databases, such as MySQL, Oracle, and various domestic relational databases. In complex deployment scenarios involving multiple tenants and multiple parallel systems, the data isolation of each tenant and the rapid iteration of software versions place extremely high demands on the synchronous maintenance of database table structures and data updates.

[0003] Currently, the main technical means for handling various database structures and data updates include the following:

[0004] One approach is the adaptation scheme based on database-specific scripts, which involves writing and maintaining separate DDL (Data Definition Language) and DML (Data Manipulation Language) scripts for each type of target database. This approach suffers from linearly increasing adaptation costs as the number of database types and versions increases, and is highly susceptible to execution errors due to script confusion during multi-tenant parallel deployments, resulting in poor scalability.

[0005] The second is the migration scheme based on the Object Relational Mapping (ORM) framework, which compares the entity class definition in the program with the database to generate incremental update statements. However, such schemes are usually highly coupled with the upper-level application, have limited support for the complex indexes, foreign key constraints and tag attributes unique to the underlying database, and lack flexibility when facing dynamic table migration in the case of table partitioning. They are also difficult to adapt to the deep semantic syntactic differences between heterogeneous databases in the context of information technology innovation.

[0006] Third, existing automated update tools often adopt a strategy of full coverage or blind execution. That is, in scenarios where multiple tenants share a common table, if there are minor conflicts in the definition of the same table structure in different systems (such as inconsistent column lengths or data types), existing tools lack effective logical merging and conflict elimination mechanisms. Summary of the Invention

[0007] To address the aforementioned issues, this application provides a method and system for updating a multi-tenant database that enables secure and accurate automated synchronization of heterogeneous databases in complex multi-tenant scenarios.

[0008] To achieve the above objectives, in a first aspect, embodiments of this application provide a multi-tenant database update method, comprising the following steps:

[0009] Obtain the configuration information and tenant type identifier of the target database;

[0010] Based on the tenant type identifier, a target table structure model and a set of data operations are matched from a preset version repository. The target table structure model includes a tag dictionary for characterizing the attribute features of heterogeneous databases. The set of data operations includes operation logic composed of operators, variables, and constants identified by extracting conditional clauses.

[0011] By using preset type compatibility logic, the column attributes of tables with the same name in the target table structure model are merged to determine the desired state of the table structure;

[0012] Compare the source table structure model with the desired state to determine attribute differences and generate difference migration DDL statements;

[0013] The operation logic in the data operation set is converted into DML statements that are compatible with the syntax of the target database by constructing an expression tree;

[0014] The risk score of the statement to be executed is determined by weighted calculation based on the operation type of the differential migration DDL statement, the estimated data volume of the target database, and the change compatibility identifier of the differential migration DDL statement.

[0015] Based on the risk score and the preset threshold, the corresponding task execution strategy is matched, and the differential migration DDL statement and the DML statement are scheduled and executed in sequence.

[0016] Preferably, the target table structure model uses table objects, column objects, foreign key objects, and index objects as basic units, and the tag dictionary includes: database attribute tags for mapping column types specific to heterogeneous databases, and business tags for logical isolation in multi-tenant scenarios.

[0017] Preferably, the step of performing a merging process on column attributes of tables with the same name using preset type compatibility logic includes:

[0018] If the columns with the same name to be merged are all numeric types with different precision, determine the numeric type with higher precision as the attribute of the merged column.

[0019] If the columns with the same name to be merged are all of string type but have different lengths, determine the longer string type as the column attribute of the merged column;

[0020] If the primary key attribute, auto-increment attribute, or default value of the columns with the same name to be merged are different, it is determined to be a merge conflict.

[0021] Preferably, the step of comparing the source table structure model with the desired state and generating difference migration DDL statements includes:

[0022] If the source table structure model lacks the column attributes of the desired state, generate a DDL statement to add the column.

[0023] If the source table structure model contains columns with the same name but whose attributes differ from the desired state, a column modification DDL statement is generated according to the syntax rules of the target database, and a corresponding change compatibility identifier is added.

[0024] Preferably, the step of generating DML statements by constructing an expression tree includes:

[0025] Extract conditional clauses from the operational logic of the data operation set, identify operators, variables and constants, and construct corresponding expression tree nodes;

[0026] Determine the corresponding column identifiers and parameter placeholders based on the type of the target database;

[0027] The expression tree is traversed, and each node of the expression tree is mapped to the syntax template of the target database to synthesize parameterized SQL statements.

[0028] Preferably, during the process of synthesizing parameterized SQL statements, if the constant is identified as null, the corresponding equals operator in the expression tree is replaced with the predicate used to determine null values ​​in the target database.

[0029] Preferably, determining the risk score of the statement to be executed includes:

[0030] According to the formula S=∑(V) i ×W i Calculate the total risk score S;

[0031] Among them, V i This represents the feature score of the i-th evaluation dimension, which includes at least: operation type, estimated number of rows in the target table, and change compatibility score; W i This represents the weight coefficient of the i-th evaluation dimension.

[0032] Preferably, the preset threshold includes at least a first risk threshold, and the matching corresponding task execution strategy includes:

[0033] If the risk score reaches the first risk threshold, the matching task execution strategy is: suspend the current update task and trigger a manual review signal;

[0034] If the risk score is lower than the first risk threshold, the matching task execution strategy is: after performing the table structure backup operation of the target database, schedule the execution of the differential migration DDL statement and the DML statement.

[0035] Preferably, the sequential scheduling and execution of the differential migration DDL statements and the DML statements includes:

[0036] Within the same update task, DDL statements are executed first, and DML statements are executed later.

[0037] For table objects with foreign key constraints, the parent table statements are executed first, followed by the child table statements, according to the hierarchy of the constraint relationships.

[0038] Secondly, embodiments of this application provide a multi-tenant database update system, including:

[0039] One or more processors;

[0040] Storage device for storing one or more programs;

[0041] When the one or more programs are executed by the one or more processors, the one or more processors implement the method described in any embodiment of the first aspect.

[0042] The multi-tenant database update method and system designed in this application achieve standardized representation of heterogeneous database attributes by constructing a table structure model containing a tag dictionary. It resolves logical conflicts in multi-tenant table definitions with the same name by utilizing type-compatible logic for conflict merging. Semantic transformation is performed using expression tree technology, ensuring the accuracy of heterogeneous syntax parsing and SQL synthesis. Furthermore, by combining risk score quantification based on operation actions and data scale, the complexity of adapting to heterogeneous environments is reduced, while improving the technical controllability and system stability of the database change process. Attached Figure Description

[0043] Figure 1 This is a flowchart illustrating the multi-tenant database update method provided in this application embodiment.

[0044] Figure 2 This is a schematic diagram of the class diagram design of the unified metadata definition model provided in the embodiments of this application.

[0045] Figure 3 This is a schematic diagram of the logical organization architecture of the version library provided in the embodiments of this application.

[0046] Figure 4 This is a schematic diagram of the logical scheduling model in the running state provided in the embodiments of this application. Detailed Implementation

[0047] The preferred embodiments of this application are described below with reference to the accompanying drawings. It should be understood that the preferred embodiments described herein are for illustration and explanation only and are not intended to limit this application.

[0048] Firstly, this application provides a multi-tenant database update method, which aims to solve the technical problems of difficulty in automatic updates and uncontrollable execution risks caused by inconsistent syntax of heterogeneous databases and conflicting table structure definitions in multi-tenant scenarios under the information technology innovation environment.

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

[0050] Step S1: Obtain the configuration information and tenant type identifier of the target database.

[0051] Specifically, the target database can be any of the relational databases such as MySQL, Oracle, DM, Kingbase, and OpenGauss; the configuration information includes the database connection address, port, access credentials, and schema name; the tenant type identifier (TenantType) is used to represent the business affiliation of the current execution environment, such as a public system tenant or a business logic tenant.

[0052] Step S2: Based on the tenant type identifier, match the target table structure model and data operation set from the preset version repository.

[0053] In this step, the pre-defined logical organization structure of the repository is as follows: Figure 3 As shown. Specifically, the version control system adopts a hierarchical tree storage structure, with the top layer consisting of project nodes that represent different application systems or business platforms; each project node has one or more version nodes that represent the system's iteration status, forming a one-to-many mapping relationship.

[0054] Furthermore, each version node is associated with a corresponding target table structure model and data operation set. Through this hierarchical architecture, when performing a matching action, the system can accurately locate the corresponding project node and version node based on the acquired tenant type identifier, and simultaneously extract the preset table structure model and data operation set under that version node. This achieves deep decoupling and unified management of structure definition and operation logic at the version dimension. The data operation set includes operation logic composed of operators, variables, and constants identified by extracting conditional clauses.

[0055] The target table structure model uses table objects, column objects, foreign key objects, and index objects as basic units, fully defining the database's metadata structure. Furthermore, the target table structure model includes a tag dictionary for characterizing the attribute features of heterogeneous databases. This tag dictionary includes:

[0056] Database attribute tags: used to map column types specific to heterogeneous databases, such as mapping storage parameters or field types specific to certain domestic databases, such as the correspondence between number and decimal.

[0057] Business tags: Used for determining the ownership of logically isolated business entities in multi-tenant scenarios, and supporting accurate location of table objects in complex scenarios such as horizontal table partitioning.

[0058] In this embodiment, as Figure 2 As shown, the table object includes basic attributes such as ID, table name, tag dictionary, and comment; the column object, in addition to the aforementioned tag dictionary, column name, and database column type, further includes attributes for fine-grained definition of the physical structure, such as: whether it is a primary key, whether it is auto-incrementing, maximum length, precision, scale, default value, order number, comment, whether it is nullable, and whether it is required.

[0059] The foreign key object contains its own name and tag dictionary, and records the corresponding delete / update behavior, associated table name, and associated table column name set. The index object contains the index name, tag dictionary, index type, and unique identifier (IsUnique), and aggregates index column objects consisting of column name, sorting, and whether it is descending.

[0060] Step S3: Use preset type compatibility logic to perform a merging process on the column attributes of tables with the same name in the target table structure model to determine the desired state of the table structure.

[0061] In this embodiment, the following merging operation is performed to address potential differences in public table definitions in a multi-tenant environment:

[0062] If the columns to be merged are all numeric types with different precisions, the numeric type with higher precision will be determined as the attribute of the merged column. For example, int and bigint will be merged into bigint; float(3,2) and float(4,2) will be merged into float(4,2).

[0063] If the columns with the same name to be merged are both string types with different lengths, the longer string type will be determined as the column attribute in the merged column. For example, varchar(50) and varchar(100) will be merged into varchar(100).

[0064] If the primary key attribute, auto-increment attribute, or default value of the columns with the same name to be merged are different, it is determined to be a merge conflict and the detection is interrupted, and manual correction is required.

[0065] Through the above merging logic, the final expected state of the table structure is determined, ensuring that it can run compatiblely in all tenant environments.

[0066] Step S4: Compare the source table structure model with the desired state, determine the attribute differences, and generate difference migration DDL statements.

[0067] Specifically, by querying metadata views such as INFORMATION_SCHEMA in the target database, a source table structure model is constructed, and this model is compared field by field with the expected state determined in step S3:

[0068] If the source table structure model lacks the column attributes required for the desired state, generate a DDL statement to add the column, such as ALTER TABLE ADD COLUMN.

[0069] If the source table structure model contains columns with the same name but whose attributes differ from the desired state, a column modification DDL statement such as ALTER COLUMN is generated according to the syntax rules of the target database, and a corresponding change compatibility flag is added. If the difference is a non-destructive change such as length extension, it is marked as a compatibility flag; if it involves type incompatibility conversion, a destructive flag is added.

[0070] Step S5: Convert the operation logic in the data operation set into DML statements that are compatible with the target database syntax by constructing an expression tree.

[0071] This step aims to achieve cross-platform adaptation of data update logic, specifically including:

[0072] S51. Extract conditional clauses, such as WHERE blocks, from the operation logic of the data operation set, identify the operators (=, >, <, !=), variables (@p1), and constants within them, and construct the corresponding expression tree nodes.

[0073] S52. Determine the corresponding column identifiers (such as backticks "`" for MySQL and double quotes """ for Oracle) and parameter placeholders (such as "@", ":", or "$") based on the type of the target database.

[0074] S53. The expression tree is traversed using a depth-first search algorithm, mapping each expression tree node and its logical relationships to the syntax template of the target database to synthesize parameterized SQL statements. In this embodiment, existing mature algorithms can be used, and will not be described in detail here.

[0075] S54. In the process of synthesizing parameterized SQL statements as described above, if the constant is identified as null, the corresponding equals operator (=) in the expression tree is replaced with a predicate used to determine null values ​​in the target database (such as "IS NULL").

[0076] Step S6: Combine the operation type of the differential migration DDL statement, the estimated data volume of the target database, and the change compatibility identifier of the differential migration DDL statement to perform a weighted calculation to determine the risk score of the statement to be executed.

[0077] In this embodiment, according to the formula S=∑(V i ×W i Calculate the total risk score S; where V i W represents the feature score of the i-th evaluation dimension. i This represents the weight coefficient for the i-th evaluation dimension. The specific weight allocation is as follows:

[0078] I. Operation type (weight 40%): For example, creating a table is worth 20 points, modifying a column is worth 80 points, updating data is worth 80 points, and deleting data is worth 100 points.

[0079] 2. Estimated number of rows in the target table (weight 35%): The number of rows affected is estimated by parsing the execution plan (ExplainPlan) of the target database. For example, less than 10,000 rows are scored as 10, and 1 million to 5 million rows are scored as 80.

[0080] III. Change compatibility score (weight 25%): Based on the compatibility identifier generated in step S4, the compatibility modification score is lower, and the destructive modification score is higher.

[0081] Step S7: Based on the determination result of the risk score and the preset threshold, match the corresponding task execution strategy, and schedule the differential migration DDL statement and the DML statement to be executed in sequence.

[0082] See Figure 4 This embodiment ensures the accurate execution of update tasks by constructing a runtime logical scheduling model. The specific execution logic is as follows:

[0083] The preset threshold includes at least a first risk threshold. In a specific implementation scenario, if the first risk threshold is set to 70 points, the system executes the following graded response strategy based on the risk score determined in step S6:

[0084] If the risk score reaches the first risk threshold, i.e., the score is ≥70, the current task is determined to be high risk. At this time, the matching execution strategy is: suspend the current update task and trigger a manual review signal. The subsequent execution link can only be activated after receiving a manual confirmation instruction.

[0085] If the risk score is lower than the first risk threshold, i.e., the score is <70, the current task is determined to be low risk or medium risk. In this case, the matching execution strategy is: after automatically calling the database backup interface to perform the table structure backup operation of the target database, the automated execution chain is triggered.

[0086] In practice, before executing the scheduling, an update task object can be instantiated in the system memory of the deployment. This object encapsulates the task identifier, risk level, and the aforementioned matched execution strategy.

[0087] Specifically, the update task dynamically binds the target physical environment (target database) to the logical definition source (version) through an association mechanism; the core criterion in the execution of the update task is the tag matching mechanism. The scheduling module performs a legality check by comparing the "tenant type identifier" of the target database with the "target table structure model" and the "tag dictionary" in the "data operation set" attached to the version; finally, the system only allows the extraction of the corresponding model metadata into the current execution context when the tags are completely consistent, thereby ensuring the logical isolation of multi-tenant update tasks at the physical layer.

[0088] Furthermore, in this embodiment, the following sequence specification is followed during the scheduling and execution phase:

[0089] Statement type order: Within the same update task, DDL statements are executed first to complete the structure preparation, followed by DML statements for data updates.

[0090] Constraint dependency order: For table objects with foreign key relationships, the statements corresponding to the parent table are executed first, and then the statements corresponding to the child table are executed, according to the hierarchical logic of the constraint relationship, in order to avoid foreign key constraint errors.

[0091] Secondly, embodiments of this application provide a multi-tenant database update system that can implement the method steps in Embodiment 1 above. Specifically, the system includes:

[0092] One or more processors;

[0093] Storage device for storing one or more programs;

[0094] When the one or more programs are executed by the one or more processors, the one or more processors implement the method described in any embodiment of the first aspect.

[0095] In addition, the system can be further divided into the following functional modules: a metadata configuration module for obtaining configuration and identification; a model matching module for matching models; a logical merging module for processing table structure merging; a conversion parsing module for syntax parsing and conversion; a risk assessment module for quantifying and calculating risks; and a scheduling execution module for scheduling and executing tasks according to strategies.

[0096] The multi-tenant database update method and system provided in this application achieve standardized representation of heterogeneous database attributes by constructing a table structure model containing a tag dictionary. It resolves logical conflicts in multi-tenant table definitions with the same name by utilizing type-compatible logic for conflict merging. Semantic transformation is performed using expression tree technology, ensuring the accuracy of heterogeneous syntax parsing and SQL synthesis. Furthermore, by combining risk score quantification based on operation actions and data scale, the complexity of adapting to heterogeneous environments is reduced, while improving the technical controllability and system stability of the database change process.

[0097] In the description of this application, it should be noted that the terms "vertical", "up", "down", "horizontal", etc., indicate the orientation or positional relationship based on the orientation or positional relationship shown in the accompanying drawings. They are only for the convenience of describing this application and simplifying the description, and do not indicate or imply that the device or element referred to must have a specific orientation, or be constructed and operated in a specific orientation. Therefore, they should not be construed as limitations on this application.

[0098] In the description of this application, it should also be noted that, unless otherwise expressly specified and limited, the terms "set," "install," "connect," and "link" should be interpreted broadly. For example, they can refer to a fixed connection, a detachable connection, or an integral connection; they can refer to a mechanical connection or an electrical connection; they can refer to a direct connection or an indirect connection through an intermediate medium; and they can refer to the internal connection of two components. Those skilled in the art can understand the specific meaning of the above terms in this application according to the specific circumstances.

[0099] Finally, it should be noted that the above descriptions are merely preferred embodiments of this application and are not intended to limit this application. Although this application has been described in detail with reference to the foregoing embodiments, those skilled in the art can still modify the technical solutions described in the foregoing embodiments or make equivalent substitutions for some of the technical features. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this application should be included within the protection scope of this application.

Claims

1. A method for updating a multi-tenant database, characterized in that, Includes the following steps: Obtain the configuration information and tenant type identifier of the target database; Based on the tenant type identifier, a target table structure model and a set of data operations are matched from a preset version repository. The target table structure model includes a tag dictionary for characterizing the attribute features of heterogeneous databases. The set of data operations includes operation logic composed of operators, variables, and constants identified by extracting conditional clauses. By using preset type compatibility logic, the column attributes of tables with the same name in the target table structure model are merged to determine the desired state of the table structure; Compare the source table structure model with the desired state to determine attribute differences and generate difference migration DDL statements; The operation logic in the data operation set is converted into DML statements that are compatible with the syntax of the target database by constructing an expression tree; The risk score of the statement to be executed is determined by weighted calculation based on the operation type of the differential migration DDL statement, the estimated data volume of the target database, and the change compatibility identifier of the differential migration DDL statement. Based on the risk score and the preset threshold, the corresponding task execution strategy is matched, and the differential migration DDL statement and the DML statement are scheduled and executed in sequence.

2. The multi-tenant database update method according to claim 1, characterized in that, The target table structure model uses table objects, column objects, foreign key objects, and index objects as basic units. The tag dictionary includes: database attribute tags for mapping column types specific to heterogeneous databases, and business tags for logical isolation in multi-tenant scenarios.

3. The multi-tenant database update method according to claim 1, characterized in that, The process of merging column attributes of tables with the same name using preset type compatibility logic includes: If the columns with the same name to be merged are all numeric types with different precision, determine the numeric type with higher precision as the attribute of the merged column. If the columns with the same name to be merged are all of string type but have different lengths, determine the longer string type as the column attribute of the merged column; If the primary key attribute, auto-increment attribute, or default value of the columns with the same name to be merged are different, it is determined to be a merge conflict.

4. The multi-tenant database update method according to claim 1, characterized in that, The comparison of the source table structure model with the desired state and the generation of difference migration DDL statements include: If the source table structure model lacks the column attributes of the desired state, generate a DDL statement to add the column. If the source table structure model contains columns with the same name but whose attributes differ from the desired state, a column modification DDL statement is generated according to the syntax rules of the target database, and a corresponding change compatibility identifier is added.

5. The multi-tenant database update method according to claim 1, characterized in that, The process of generating DML statements by constructing an expression tree includes: Extract conditional clauses from the operational logic of the data operation set, identify operators, variables and constants, and construct corresponding expression tree nodes; Determine the corresponding column identifiers and parameter placeholders based on the type of the target database; The expression tree is traversed, and each node of the expression tree is mapped to the syntax template of the target database to synthesize parameterized SQL statements.

6. The multi-tenant database update method according to claim 5, characterized in that, During the process of synthesizing parameterized SQL statements, if the constant is identified as null, the corresponding equals operator in the expression tree is replaced with the predicate used to determine null values ​​in the target database.

7. The multi-tenant database update method according to claim 1, characterized in that, The determination of the risk score of the statement to be executed includes: According to the formula S=∑(V) i ×W i Calculate the total risk score S; Among them, V i This represents the feature score of the i-th evaluation dimension, which includes at least: operation type, estimated number of rows in the target table, and change compatibility score; W i This represents the weight coefficient of the i-th evaluation dimension.

8. The multi-tenant database update method according to claim 7, characterized in that, The preset threshold includes at least a first risk threshold, and the matching corresponding task execution strategy includes: If the risk score reaches the first risk threshold, the matching task execution strategy is: suspend the current update task and trigger a manual review signal; If the risk score is lower than the first risk threshold, the matching task execution strategy is: after performing the table structure backup operation of the target database, schedule the execution of the differential migration DDL statement and the DML statement.

9. The multi-tenant database update method according to claim 1, characterized in that, The sequential scheduling and execution of the differential migration DDL statements and the DML statements includes: Within the same update task, DDL statements are executed first, and DML statements are executed later. For table objects with foreign key constraints, the parent table statements are executed first, followed by the child table statements, according to the hierarchy of the constraint relationships.

10. A multi-tenant database update system, characterized in that, include: One or more processors; Storage device for storing one or more programs; When the one or more programs are executed by the one or more processors, the one or more processors implement the method of any one of claims 1 to 9.

Images