Database replication method and system for SaaS software

Abstract

The present application relates to the field of computer technology, and discloses a database replication method and system for SaaS software, the method comprising: connecting to the source database of the SaaS software used by the target object, reading each data in each data table in the source database; replacing the primary key and updating the foreign key of each data according to the pre-formulated primary and foreign key generation strategy, and obtaining multiple data tables to be replicated; the primary and foreign key generation strategy is used to automatically generate a new primary key for each data during the database replication process, replace the old primary key of each data, and update the foreign key of each data with the new primary key; check whether all primary keys of each data table to be replicated are unique; when all primary keys of each data table to be replicated are all unique, copy each data table to be replicated to the target database. The present application can ensure the consistency of data between multiple tables through primary key replacement and foreign key update, and ensure that the integrity and accuracy of the data table are maintained during data migration or backup.

Description

Technical Field

[0001] The present application relates to the field of data processing technology, and in particular to a database replication method and system for SaaS software. Background Art

[0002] In the field of Software as a Service (SaaS), service providers need to provide independent data storage and business logic processing for multiple customers. Each customer's data includes but is not limited to user information, transaction records, configuration settings, etc., which are usually stored in a database. In order to meet the specific needs of different customers, service providers need to be able to quickly and accurately copy the data in the customer's database while ensuring the integrity and consistency of the data.

[0003] In the related art, traditional data replication technology usually involves directly copying existing data in the database and pasting it into the new database. This method is simple and direct, but it does not consider the uniqueness constraints of the data, especially the uniqueness of the primary key. In a multi-customer environment, if the data is simply copied, it may cause primary key conflicts because each customer's data set needs to have a unique primary key to maintain data integrity. Directly copying data results in duplicate primary keys in different customers' databases, which violates the primary key constraints of the database and may cause data insertion failures or data inconsistencies. Summary of the invention

[0004] The embodiments of the present application provide a database replication method and system for SaaS software. In order to have a basic understanding of some aspects of the disclosed embodiments, a simple summary is given below. This summary is not a general review, nor is it intended to identify key / important components or describe the scope of protection of these embodiments. Its only purpose is to present some concepts in a simple form as a preface to the detailed description that follows.

[0005] In a first aspect, an embodiment of the present application provides a database replication method for SaaS software, the method comprising:

[0006] Connect to the source database of the SaaS software used by the target object and read every piece of data in each data table in the source database;

[0007] According to the pre-defined primary and foreign key generation strategy, the primary key of each data is replaced and the foreign key is updated to obtain multiple data tables to be copied; the primary and foreign key generation strategy is used to automatically generate a new primary key for each data during the database copy process, replace the old primary key of each data, and use the new primary key to update the foreign key of each data;

[0008] Check whether all primary keys of each data table to be copied are unique;

[0009] When all primary keys of each data table to be copied are unique, copy each data table to be copied to the target database.

[0010] Optionally, the primary and foreign key generation strategy includes a parameter acquisition module, a new ID calculation module, a primary key replacement module, and a foreign key update module;

[0011] According to the pre-defined primary and foreign key generation strategy, the primary key is replaced and the foreign key is updated for each piece of data, including:

[0012] The parameter acquisition module obtains the current timestamp and preset offset;

[0013] The new ID calculation module calculates a new ID for each piece of data based on the current timestamp and the preset offset, and obtains a new ID for each piece of data;

[0014] The primary key replacement module replaces the old primary key of each piece of data with the new ID of each piece of data as the new primary key, so as to replace the primary key of each piece of data;

[0015] The foreign key update module updates the foreign key of each piece of data according to the new ID of each piece of data, so as to update the foreign key of each piece of data.

[0016] Optionally, a new ID is calculated for each piece of data according to the current timestamp and the preset offset, and the new ID of each piece of data is obtained, including:

[0017] Count the amount of data for each piece of data;

[0018] A hash function is used to perform hash processing on the data volume of each piece of data, so as to convert the data volume of each piece of data into a hash value of a fixed length, and obtain a target hash value of each piece of data;

[0019] According to the preset offset, the current timestamp is dynamically adjusted to increase the discrimination of the current timestamp and obtain the target timestamp;

[0020] Based on the target hash value and target timestamp of each piece of data, a new ID is calculated for each piece of data.

[0021] Optionally, based on the target hash value and target timestamp of each piece of data, calculate the new ID of each piece of data, including:

[0022] A hash function is used to perform a combined hash process on the target hash value and the target timestamp of each piece of data to obtain a combined hash value of each piece of data;

[0023] According to the combined hash value of each piece of data, the new ID of each piece of data is calculated; the calculation formula of the new ID of each piece of data is:

[0024]

[0025] in, is the new ID for each piece of data. and is an integer, the user controls the number of bits of the shift, is the combined hash value of each piece of data, is the exclusive OR operator, is the amount of data for each piece of data, Indicates a bitwise left shift operation.

[0026] Optionally, update the foreign key of each record according to its new ID, including:

[0027] Get the data dictionary of the target object;

[0028] Get the foreign key of each data from the data dictionary; the foreign key of each data is the old primary key of the data in other tables;

[0029] Store the association between the new ID and the old primary key of each piece of data, and obtain the mapping relationship of all data tables in the source database;

[0030] According to the old primary key of the data in other tables corresponding to the foreign key of each data, obtain the new ID of the data in other tables corresponding to the foreign key of each data from the mapping relationship;

[0031] Replace the old primary key of the data in other tables corresponding to the foreign key of each piece of data with the new ID of the data in other tables corresponding to the foreign key of each piece of data.

[0032] Optionally, generate a preset offset by following the steps below, including:

[0033] Collect new ID-related data for each piece of data in real time. The new ID-related data includes the actual generated ID, system load, date and time;

[0034] Capture system status in real time and generate real-time data streams;

[0035] Extract the time, date, system load, and historical offset usage that affect ID generation from new ID-related data and real-time data streams to obtain the target factor set;

[0036] Construct and transform the target factor set into features to obtain real-time feature data;

[0037] The real-time feature data is input into a pre-trained offset determination model for avoiding ID conflicts; the offset determination model is a mathematical model for determining an offset for avoiding ID conflicts;

[0038] Output the optimal offset corresponding to the current time corresponding to the system state;

[0039] Use the best offset as the preset offset.

[0040] Optionally, a pre-trained offset determination model for avoiding ID conflicts may be generated by following the steps below, including:

[0041] Collect historical ID-related data and its historical data stream when a new ID is generated for each first data in any data table of any database in a preset time period;

[0042] From the historical ID related data and its historical data stream, extract the historical time, historical date, historical system load, and historical offset usage that affect ID generation to obtain a historical factor set;

[0043] Fitting a quantitative chart of the historical factor set to obtain a quantitative chart of each first data;

[0044] Sending a quantitative chart of each first data to a client for display;

[0045] Receiving a historical offset input for avoiding ID conflicts for a displayed chart, and obtaining a historical offset of a quantized chart of each first data;

[0046] Using the historical offset as a label, annotate the historical ID-related data and its historical data stream when each first data generates a new ID, and obtain multiple annotated samples as model training data;

[0047] Based on the model training data, a pre-trained offset determination model for avoiding ID conflicts is generated.

[0048] Optionally, a pre-trained offset determination model for avoiding ID conflicts is generated based on the model training data, including:

[0049] Use the random forest algorithm to create an offset determination model for avoiding ID conflicts;

[0050] Input the model training data into the offset determination model for machine learning and output the model loss value;

[0051] When the model loss value reaches the minimum, a pre-trained offset determination model for avoiding ID conflicts is generated; or,

[0052] When the model loss value has not reached the minimum, the model loss value is back-propagated to update the model parameters of the offset determination model, and the step of inputting the model training data into the offset determination model for machine learning is continued until the model loss value reaches the minimum.

[0053] Optionally, the model loss function of the offset determination model used to avoid ID conflicts is:

[0054]

[0055]

[0056] in, is the model loss value, is the number of samples of model training data, The model predicts The prediction offset of samples, For the The actual offset of samples, The model parameters for the model are determined for the offsets used to avoid ID conflicts, It is The feature vector of the historical factor set of samples, is the bias term. By minimizing the loss function, the model learns the offset used to avoid ID conflicts to determine the model parameters of the model. and the deviation term This will make the predicted offset infinitely close to the actual offset, thus effectively avoiding ID conflicts.

[0057] In a second aspect, an embodiment of the present application provides a database replication system for SaaS software, the system comprising:

[0058] The connection module is used to connect to the source database of the SaaS software used by the target object and read each data in each data table in the source database;

[0059] The replacement and update module is used to replace the primary key and update the foreign key of each data according to the pre-defined primary and foreign key generation strategy, so as to obtain multiple data tables to be copied; the primary and foreign key generation strategy is used to automatically generate a new primary key for each data in the database copy process, replace the old primary key of each data, and use the new primary key to update the foreign key of each data;

[0060] The checking module is used to check whether all primary keys of each data table to be copied are unique;

[0061] The replication module is used to replicate each data table to be replicated to a target database when all primary keys of each data table to be replicated are unique.

[0062] The technical solution provided by the embodiments of the present application may have the following beneficial effects:

[0063] In an embodiment of the present application, on the one hand, by implementing a pre-established primary and foreign key generation strategy under a unified standard, a new primary key is automatically generated for each piece of data, and all related foreign keys are updated, thereby ensuring that the reference relationship between multiple tables remains correct after data migration, avoiding the tediousness and possibility of errors in manually updating associated fields, and improving the efficiency and accuracy of data operations; on the other hand, by checking the uniqueness of the primary keys of all data tables to be copied, the problems of primary key conflicts and foreign key reference errors in the data migration process are reduced, thereby reducing maintenance costs and potential business interruption risks.

[0064] It should be understood that the foregoing general description and the following detailed description are exemplary and explanatory only and are not restrictive of the present application. BRIEF DESCRIPTION OF THE DRAWINGS

[0065] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the present application.

[0066] Figure 1 It is a flowchart of a database replication method for SaaS software provided in an embodiment of the present application;

[0067] Figure 2 It is a schematic diagram of an order table in a source database provided by this application;

[0068] Figure 3 This is a schematic diagram of a data table of a process of generating a new ID for each piece of data provided by the present application;

[0069] Figure 4 It is a schematic diagram of a quantitative chart fitting a set of historical factors provided by the present application;

[0070] Figure 5 It is a flow chart of a model training method of an offset determination model for avoiding ID conflicts provided by the present application;

[0071] Figure 6 It is a structural diagram of a database replication system for SaaS software provided in an embodiment of the present application;

[0072] Figure 7 It is a structural schematic diagram of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0073] The following description and the accompanying drawings sufficiently illustrate specific embodiments of the present application to enable those skilled in the art to practice them.

[0074] It should be clear that the described embodiments are only part of the embodiments of the present application, rather than all the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in the field without creative work are within the scope of protection of the present application.

[0075] When the following description refers to the drawings, unless otherwise indicated, the same numbers in different drawings represent the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with the present application. Instead, they are only examples of systems and methods consistent with some aspects of the present application as detailed in the attached claims.

[0076] In the description of the present application, it should be understood that the terms "first", "second", etc. are used for descriptive purposes only and should not be understood as indicating or implying relative importance. For those of ordinary skill in the art, the specific meanings of the above terms in the present application can be understood according to the specific circumstances. In addition, in the description of the present application, unless otherwise specified, "multiple" refers to two or more. "And / or" describes the association relationship of associated objects, indicating that three relationships may exist. For example, A and / or B can represent: A exists alone, A and B exist at the same time, and B exists alone. The character " / " generally indicates that the objects associated before and after are in an "or" relationship.

[0077] The present application provides a database replication method and system for SaaS software to solve the problems existing in the above-mentioned related technical problems. In the embodiments of the present application, on the one hand, by implementing a pre-established primary and foreign key generation strategy under a unified standard, a new primary key is automatically generated for each piece of data, and all related foreign keys are updated, which can ensure that the reference relationship between multiple tables after data migration is still correct, avoiding the tediousness and possibility of errors in manually updating associated fields, and improving the efficiency and accuracy of data operations; on the other hand, by checking the uniqueness of the primary keys of all data tables to be copied, the primary key conflicts and foreign key reference errors during the data migration process are reduced, thereby reducing maintenance costs and potential business interruption risks. The following is a detailed description using an exemplary embodiment.

[0078] The following will be combined Figure 1-Figure 5 , a database replication method for SaaS software provided in an embodiment of the present application is introduced in detail. The method can be implemented by a computer program and can be run on a database replication system for SaaS software based on the von Neumann system. The computer program can be integrated into an application or run as an independent tool application.

[0079] See also Figure 1 , which is a flowchart of a database replication method for SaaS software provided in an embodiment of the present application. Figure 1 As shown, the method of the embodiment of the present application may include the following steps:

[0080] S101, connecting to the source database of the SaaS software used by the target object, and reading each data item in each data table in the source database;

[0081] The target object is a certain user, and the source database refers to the original data storage location, that is, the starting point of data replication. The source database contains the data that needs to be replicated. A data table is the most basic structure for storing data in a database, usually consisting of rows (records) and columns (fields). Each table represents a certain type of data set, such as a user information table, an order table, etc. Each piece of data refers to the information stored in a table, and each row of data represents the detailed information of an entity. For example, in a user information table, each row may contain all relevant information about a user, such as user name, email address, registration date, etc.

[0082] In some embodiments of the present application, first confirm the access rights and connection information of the source database, such as the IP address, port number, database name, and necessary login credentials of the database server. Then use the access rights and connection information in combination with database management tools (such as phpMyAdmin, SQL Server Management Studio, etc.) or programming languages ​​(such as Python's pymysql library, Java's JDBC) to connect to the source database. Secondly, query the database metadata and list all data tables that need to be migrated, such as orders, customers, products, etc. Perform query operations on each table, such as using the SQL statement "SELECT * FROM orders" to read all data in the order table. Finally, every piece of data read from each data table in the source database can be obtained.

[0083] For example Figure 2 As shown, Figure 2 This is a schematic diagram of the order table in the source database. The order ID in this data table is a number that uniquely identifies each order and is the primary key. The customer ID is a reference to the customer ID in the customer table and is a foreign key. The product ID is a reference to the product ID in the product table and is a foreign key. The total amount is the total amount of the order. The order status is the current status of the order, such as "shipped", "pending", "completed", "cancelled", etc.

[0084] S102, according to the pre-established primary and foreign key generation strategy, the primary key of each data is replaced and the foreign key is updated to obtain multiple data tables to be copied; the primary and foreign key generation strategy is used to automatically generate a new primary key for each data during the database copy process, replace the old primary key of each data, and use the new primary key to update the foreign key of each data;

[0085] Among them, the primary and foreign key generation strategy is a predefined algorithm used to automatically generate new primary keys and update foreign keys during the database replication process. This strategy ensures that when data is transferred from one database to another, all primary and foreign key relationships can be correctly re-established to maintain the integrity and relevance of the data. Primary key replacement means replacing the old primary key (from the source database) in the data record with a new primary key (generated according to the primary and foreign key generation strategy) during the data replication process. Foreign key update means updating the foreign keys in the data record that reference the primary keys of other tables during the data replication process. This method ensures that the association between data remains correct in the new database environment. The data tables to be replicated are data tables that have completed primary key replacement and foreign key update, and they are ready to be copied to the target database.

[0086] Among them, the primary and foreign key generation strategy includes a parameter acquisition module, a new ID calculation module, a primary key replacement module and a foreign key update module.

[0087] In some embodiments of the present application, according to a pre-established primary and foreign key generation strategy, the specific process of replacing the primary key and updating the foreign key of each data includes: the parameter acquisition module obtains the current timestamp and the preset offset; the new ID calculation module calculates the new ID for each data according to the current timestamp and the preset offset, and obtains the new ID of each data; the primary key replacement module uses the new ID of each data as the new primary key to replace the old primary key of each data, so as to replace the primary key of each data; the foreign key update module updates the foreign key of each data according to the new ID of each data, so as to update the foreign key of each data.

[0088] The current timestamp refers to the precise time obtained from the system when the operation is performed, usually expressed in milliseconds or smaller units, to ensure that the generated ID is time-sensitive and unique. The preset offset is a value predefined according to specific rules or policies, which is used to adjust the timestamp to further enhance the uniqueness of the ID and prevent duplicate IDs from being generated on different nodes in a distributed system.

[0089] In an embodiment of the present application, by combining the current timestamp and the preset offset to generate a new ID, it is possible to ensure that each piece of data obtains a unique identifier when it is copied or migrated to a new database. This method not only improves the accuracy and efficiency of data operations, but also enhances the consistency and integrity of the data. In the process of replacing the old primary key and updating the foreign key, this mechanism ensures that the association relationship between the data is maintained, avoiding potential errors and data inconsistencies caused by data migration.

[0090] In some embodiments of the present application, a new ID is calculated for each piece of data based on the current timestamp and the preset offset, and the specific process of obtaining the new ID of each piece of data includes: counting the data volume of each piece of data; performing hash processing on the data volume of each piece of data using a hash function to convert the data volume of each piece of data into a hash value of a fixed length to obtain a target hash value for each piece of data; dynamically adjusting the current timestamp according to the preset offset to increase the distinctiveness of the current timestamp to obtain a target timestamp; calculating the new ID of each piece of data based on the target hash value and the target timestamp of each piece of data.

[0091] The data volume refers to the size or complexity of each data record, which can be quantified by counting the number of bytes of data or the number of fields contained in the record. A hash function is an algorithm that receives input data of any size and produces an output (hash value) of a fixed length. The target hash value refers to the fixed-length hash value obtained after processing the data volume through the hash function, which is used in the subsequent ID calculation process. The target timestamp is the time value obtained after dynamically adjusting the current timestamp. The purpose of the adjustment is to increase the distinctiveness of the timestamp and ensure that the generated ID has higher uniqueness.

[0092] In an embodiment of the present application, by counting the amount of data for each piece of data and converting it into a hash value of fixed length using a hash function, a unique identifier can be generated for each piece of data, which increases the uniqueness and identifiability of the data. The current timestamp is dynamically adjusted in combination with a preset offset, further improving the distinction of the timestamp, so that the generated ID can also remain unique in a global distributed system. The new ID calculated based on the target hash value and the target timestamp not only ensures the consistency and integrity of the data during migration or replication, but also improves the efficiency and reliability of database operations. This method effectively avoids ID conflicts and reduces data redundancy.

[0093] In some embodiments of the present application, based on the target hash value and target timestamp of each piece of data, the specific process of calculating the new ID of each piece of data includes: using a hash function to perform combined hash processing on the target hash value and target timestamp of each piece of data to obtain a combined hash value of each piece of data; according to the combined hash value of each piece of data, calculating the new ID of each piece of data; wherein the calculation formula of the new ID of each piece of data is:

[0094]

[0095] in, is the new ID for each piece of data. and is an integer, the user controls the number of bits of the shift, is the combined hash value of each piece of data, is the exclusive OR operator, is the amount of data for each piece of data, Indicates a bitwise left shift operation.

[0096] Specifically, the data table of the generation process of the new ID of each data is as follows: Figure 3 As shown in the figure, firstly, each piece of data is counted to obtain its data volume (number of bytes). Then, the data volume is hashed using a hash function to obtain the target hash value. Secondly, the current timestamp is dynamically adjusted according to the preset offset to obtain the target timestamp. Finally, the new ID of each piece of data is calculated by combining the target hash value and the target timestamp through a certain algorithm.

[0097] In some embodiments of the present application, the specific process of updating the foreign key of each data according to the new ID of each data includes: obtaining the data dictionary of the target object; obtaining the foreign key of each data from the data dictionary; wherein the foreign key of each data is the old primary key of the data in other tables; storing the association relationship between the new ID of each data and the old primary key, and obtaining the mapping relationship of all data tables in the source database; according to the old primary key of the data in other tables corresponding to the foreign key of each data, obtaining the new ID of the data in other tables corresponding to the foreign key of each data from the mapping relationship; replacing the old primary key of the data in other tables corresponding to the foreign key of each data with the new ID of the data in other tables corresponding to the foreign key of each data.

[0098] The data dictionary is a collection of systematic descriptions of data in the database, including the name of each data item, data type, data format, data source, description of data content, etc. The mapping relationship refers to the correspondence between the new ID and the old primary key of each data during the data replication process.

[0099] Specifically, first learn the table structure and key information of the target object from the data dictionary. Then obtain the foreign key information of each data from the data dictionary. These foreign keys are the old primary keys pointing to the data in other tables. Create a mapping table to record the correspondence between the new ID and the old primary key of each data. For each data, find the corresponding new ID from the mapping relationship according to the old primary key of the data in other tables corresponding to its foreign key. Replace the old primary key referenced in the foreign key of each data with the new ID to ensure that the association between the data is still correct after the data migration.

[0100] For example, suppose there are two tables: the order table and the customer table. In the source database, the customer ID foreign key in the order table references the customer ID primary key in the customer table. At this point, the old data includes: customer table: customer ID = 101, order table: customer ID = 101; new data is generated: the new ID of the customer table is generated as 201, and the mapping relationship is: old customer ID (101) → new customer ID (201). The final foreign key update is: in the order table, update the value of customer ID from 101 to 201 to reflect the new mapping relationship.

[0101] In some embodiments of the present application, the specific process of generating a preset offset includes: collecting new ID-related data for each piece of data in real time, the new ID-related data including the actually generated ID, system load, date and time; capturing the system status in real time to generate a real-time data stream; extracting the time, date, system load, and historical offset usage that affect ID generation from the new ID-related data and the real-time data stream to obtain a target factor set; performing feature construction and feature transformation on the target factor set to obtain real-time feature data; inputting the real-time feature data into a pre-trained offset determination model for avoiding ID conflicts; the offset determination model is a mathematical model for determining the offset to avoid ID conflicts; outputting the optimal offset corresponding to the system status at the current moment; and using the optimal offset as the preset offset.

[0102] Among them, in the context of database and system monitoring, real-time data streams can include various performance indicators, log events, transaction records, etc., which are dynamically generated over time.

[0103] Specifically, feature construction is to extract information from raw data and construct new features (attributes or variables) for use in data analysis and machine learning models. These features help the model better understand and predict the data. For example, feature construction includes hour information extracted from date and time, whether it is a weekend, system load level, etc.

[0104] Specifically, feature transformation is the process of transforming or normalizing features to improve the performance and accuracy of the model. This may include normalization, standardization, encoding categorical variables, etc. For example, converting the system load from the original percentage value to a normalized value between 0 and 1, or converting the date time to the day of the year to simplify the calculation of the model.

[0105] In an embodiment of the present application, a target factor set is extracted from new ID-related data and real-time data streams, and feature construction and feature conversion are performed. The obtained real-time feature data can more accurately reflect the actual operating status of the system. By inputting these data into a pre-trained offset determination model, the optimal offset that meets the current system state can be calculated in real time, thereby dynamically adjusting the ID generation strategy and effectively avoiding ID conflicts. This method improves the responsiveness and adaptability of the system and ensures the stability and reliability of the ID generation process in a high-concurrency and dynamically changing environment.

[0106] In some implementations of the present application, a pre-trained offset determination model for avoiding ID conflicts is generated according to the following steps, including: collecting historical ID-related data and its historical data stream when a new ID is generated for each first data in any data table of any database in a preset time period; extracting the historical time, historical date, historical system load, and historical offset usage that affect ID generation from the historical ID-related data and its historical data stream to obtain a historical factor set; fitting a quantitative chart of the historical factor set to obtain a quantitative chart of each first data; sending the quantitative chart of each first data to a client for display; receiving the historical offset input for avoiding ID conflicts for the displayed chart to obtain the historical offset of the quantitative chart of each first data; using the historical offset as a label to perform data annotation on the historical ID-related data and its historical data stream when a new ID is generated for each first data, to obtain multiple annotated samples as model training data; and generating a pre-trained offset determination model for avoiding ID conflicts based on the model training data.

[0107] Specifically, we fit a quantitative chart of the historical factor set, such as Figure 4 As shown in the figure, users can set the size of the historical offset based on the quantitative chart.

[0108] Specifically, the specific process of generating a pre-trained offset determination model for avoiding ID conflicts based on model training data includes: using a random forest algorithm to create an offset determination model for avoiding ID conflicts; inputting the model training data into the offset determination model for machine learning, and outputting a model loss value; when the model loss value reaches a minimum, generating a pre-trained offset determination model for avoiding ID conflicts; or, when the model loss value does not reach a minimum, backpropagating the model loss value to update the model parameters of the offset determination model, and continuing to execute the step of inputting the model training data into the offset determination model for machine learning until the model loss value reaches a minimum.

[0109] Specifically, the model loss function of the offset determination model used to avoid ID conflicts is:

[0110]

[0111]

[0112] in, is the model loss value, is the number of samples of model training data, The model predicts The prediction offset of samples, For the The actual offset of samples, The model parameters for the model are determined for the offsets used to avoid ID conflicts, It is The feature vector of the historical factor set of samples, is the bias term. By minimizing the loss function, the model learns the offset used to avoid ID conflicts to determine the model parameters of the model. and the deviation term This will make the predicted offset infinitely close to the actual offset, thus effectively avoiding ID conflicts.

[0113] S103, checking whether all primary keys of each data table to be copied are unique;

[0114] In some embodiments of the present application, the table structure of each data table to be copied can be obtained by querying the data dictionary or using a specific SQL command (such as INFORMATION_SCHEMA.TABLE_CONSTRAINTS), and then an SQL query is executed on each table to select the primary key column. All primary keys of each data table to be copied can be read, and the primary key data of each data table to be copied can be checked to ensure that each value is unique. This can be achieved through SQL commands, such as using GROUP BY and HAVING clauses (SELECT order_id, COUNT(*) FROM orders GROUP BY order_id HAVINGCOUNT(*)>1) to find duplicates. If the query does not return results after executing the SQL command, it means that there is no non-unique primary key value; if the query returns results after executing the SQL command, it means that there is a non-unique primary key value, and a new primary key value is generated for the duplicate record.

[0115] S104, when all primary keys of each data table to be copied are unique, copy each data table to be copied to a target database.

[0116] In some embodiments of the present application, replication may be processed in a batch replication manner.

[0117] In an embodiment of the present application, on the one hand, by implementing a pre-established primary and foreign key generation strategy under a unified standard, a new primary key is automatically generated for each piece of data, and all related foreign keys are updated, thereby ensuring that the reference relationship between multiple tables remains correct after data migration, avoiding the tediousness and possibility of errors in manually updating associated fields, and improving the efficiency and accuracy of data operations; on the other hand, by checking the uniqueness of the primary keys of all data tables to be copied, the problems of primary key conflicts and foreign key reference errors in the data migration process are reduced, thereby reducing maintenance costs and potential business interruption risks.

[0118] See also Figure 5 , which is a flow chart of a model training method for an offset determination model for avoiding ID conflicts provided in an embodiment of the present application. Figure 5 As shown, the method of the embodiment of the present application may include the following steps:

[0119] S201, collecting historical ID-related data and its historical data stream when a new ID is generated for each first data in any data table of any database in a preset time period;

[0120] S202, extracting historical time, historical date, historical system load, and historical offset usage that affect ID generation from historical ID-related data and its historical data stream to obtain a historical factor set;

[0121] S203, fitting a quantitative chart of the historical factor set to obtain a quantitative chart of each first data;

[0122] S204, sending a quantitative chart of each first data to a client for display;

[0123] S205, receiving a historical offset input for avoiding ID conflicts for a displayed chart, and obtaining a historical offset of a quantized chart of each first data;

[0124] S206, using the historical offset as a label to annotate the historical ID-related data and its historical data stream when each first data generates a new ID, to obtain multiple annotated samples as model training data;

[0125] S207: Generate a pre-trained offset determination model for avoiding ID conflicts based on the model training data.

[0126] In an embodiment of the present application, by collecting and analyzing relevant historical data of generating new IDs in database operations, key historical factors such as time, date, system load, and offset usage are extracted. By converting these factors into quantitative charts, not only the visualization and comprehensibility of the data are enhanced, but also the client users can intuitively identify and adjust the offsets that may cause ID conflicts. In addition, the offsets entered by the user are used as labels, which further enriches the data annotation and provides high-quality training data for building and training prediction models. Ultimately, this pre-trained model can intelligently determine the optimal offset, thereby automatically avoiding ID conflicts in database operations and improving the efficiency and accuracy of data management.

[0127] The following is a system embodiment of the present application, which can be used to execute the method embodiment of the present application. For details not disclosed in the system embodiment of the present application, please refer to the method embodiment of the present application.

[0128] See also Figure 6 , which shows a schematic diagram of the structure of a database replication system for SaaS software provided by an exemplary embodiment of the present application. The database replication system for SaaS software can be implemented as all or part of an electronic device through software, hardware, or a combination of both. The system 1 includes a connection module 10, a replacement and update module 20, a check module 30, and a replication module 40.

[0129] The connection module 10 is used to connect to the source database of the SaaS software used by the target object and read each data in each data table in the source database;

[0130] The replacement and update module 20 is used to replace the primary key and update the foreign key of each data according to the pre-defined primary and foreign key generation strategy to obtain multiple data tables to be copied; the primary and foreign key generation strategy is used to automatically generate a new primary key for each data in the database copy process, replace the old primary key of each data, and use the new primary key to update the foreign key of each data;

[0131] A checking module 30 is used to check whether all primary keys of each data table to be copied are unique;

[0132] The replication module 40 is used to replicate each data table to be replicated to a target database when all primary keys of each data table to be replicated are unique.

[0133] It should be noted that the database replication system for SaaS software provided in the above embodiment only uses the division of the above functional modules as an example when executing the database replication method for SaaS software. In actual applications, the above functions can be assigned to different functional modules as needed, that is, the internal structure of the device can be divided into different functional modules to complete all or part of the functions described above. In addition, the database replication system for SaaS software provided in the above embodiment and the database replication method for SaaS software are of the same concept, and the implementation process thereof is detailed in the method embodiment, which will not be repeated here.

[0134] The serial numbers of the above-mentioned embodiments of the present application are for description only and do not represent the advantages or disadvantages of the embodiments.

[0135] In an embodiment of the present application, on the one hand, by implementing a pre-established primary and foreign key generation strategy under a unified standard, a new primary key is automatically generated for each piece of data, and all related foreign keys are updated, thereby ensuring that the reference relationship between multiple tables remains correct after data migration, avoiding the tediousness and possibility of errors in manually updating associated fields, and improving the efficiency and accuracy of data operations; on the other hand, by checking the uniqueness of the primary keys of all data tables to be copied, the problems of primary key conflicts and foreign key reference errors in the data migration process are reduced, thereby reducing maintenance costs and potential business interruption risks.

[0136] The present application also provides a computer-readable medium having program instructions stored thereon, and when the program instructions are executed by a processor, the database replication method for SaaS software provided by the above-mentioned various method embodiments is implemented.

[0137] The present application also provides a computer program product including instructions, which, when executed on a computer, enables the computer to execute the database replication method for SaaS software of each of the above method embodiments.

[0138] See also Figure 7 , is a schematic diagram of the structure of an electronic device provided in an embodiment of the present application. Figure 7 As shown, the electronic device 1000 may include: at least one processor 1001 , at least one network interface 1004 , a user interface 1003 , a memory 1005 , and at least one communication bus 1002 .

[0139] The communication bus 1002 is used to realize the connection and communication between these components.

[0140] The user interface 1003 may include a display screen (Display) and a camera (Camera). Optionally, the user interface 1003 may also include a standard wired interface and a wireless interface.

[0141] The network interface 1004 may optionally include a standard wired interface or a wireless interface (such as a WI-FI interface).

[0142] Among them, the processor 1001 may include one or more processing cores. The processor 1001 uses various interfaces and lines to connect various parts of the entire electronic device 1000, and executes various functions and processes data of the electronic device 1000 by running or executing instructions, programs, code sets or instruction sets stored in the memory 1005, and calling data stored in the memory 1005. Optionally, the processor 1001 can be implemented in at least one hardware form of digital signal processing (Digital Signal Processing, DSP), field programmable gate array (Field-Programmable Gate Array, FPGA), and programmable logic array (Programmable Logic Array, PLA). The processor 1001 can integrate one or a combination of a central processing unit (Central Processing Unit, CPU), a graphics processing unit (Graphics Processing Unit, GPU) and a modem. Among them, the CPU mainly processes the operating system, user interface and application programs; the GPU is responsible for rendering and drawing the content to be displayed on the display screen; the modem is used to process wireless communications. It can be understood that the above-mentioned modem may not be integrated into the processor 1001, and it can be implemented separately through a chip.

[0143] Among them, the memory 1005 may include a random access memory (Random Access Memory, RAM) and may also include a read-only memory (Read-Only Memory). Optionally, the memory 1005 includes a non-transitory computer-readable storage medium. The memory 1005 can be used to store instructions, programs, codes, code sets or instruction sets. The memory 1005 may include a program storage area and a data storage area, wherein the program storage area may store instructions for implementing an operating system, instructions for at least one function (such as a touch function, a sound playback function, an image playback function, etc.), instructions for implementing the above-mentioned various method embodiments, etc.; the data storage area may store data involved in the above-mentioned various method embodiments, etc. The memory 1005 may also be optionally at least one storage system located away from the aforementioned processor 1001. As Figure 7 As shown, the memory 1005 as a computer storage medium may include an operating system, a network communication module, a user interface module, and a database replication application for the SaaS software.

[0144] exist Figure 7 In the electronic device 1000 shown, the user interface 1003 is mainly used to provide an input interface for the user and obtain data input by the user; and the processor 1001 can be used to call the database replication application for SaaS software stored in the memory 1005, and specifically perform the following operations:

[0145] Connect to the source database of the SaaS software used by the target object and read every piece of data in each data table in the source database;

[0146] According to the pre-defined primary and foreign key generation strategy, the primary key of each data is replaced and the foreign key is updated to obtain multiple data tables to be copied; the primary and foreign key generation strategy is used to automatically generate a new primary key for each data during the database copy process, replace the old primary key of each data, and use the new primary key to update the foreign key of each data;

[0147] Check whether all primary keys of each data table to be copied are unique;

[0148] When all primary keys of each data table to be copied are unique, copy each data table to be copied to the target database.

[0149] In one embodiment, when the processor 1001 replaces the primary key and updates the foreign key of each piece of data according to the pre-defined primary and foreign key generation strategy, the processor 1001 specifically performs the following operations:

[0150] The parameter acquisition module obtains the current timestamp and preset offset;

[0151] The new ID calculation module calculates a new ID for each piece of data based on the current timestamp and the preset offset, and obtains a new ID for each piece of data;

[0152] The primary key replacement module replaces the old primary key of each piece of data with the new ID of each piece of data as the new primary key, so as to replace the primary key of each piece of data;

[0153] The foreign key update module updates the foreign key of each piece of data according to the new ID of each piece of data, so as to update the foreign key of each piece of data.

[0154] In one embodiment, when the processor 1001 calculates a new ID for each piece of data according to the current timestamp and the preset offset to obtain the new ID for each piece of data, the processor 1001 specifically performs the following operations:

[0155] Count the amount of data for each piece of data;

[0156] A hash function is used to perform hash processing on the data volume of each piece of data, so as to convert the data volume of each piece of data into a hash value of a fixed length, and obtain a target hash value of each piece of data;

[0157] According to the preset offset, the current timestamp is dynamically adjusted to increase the discrimination of the current timestamp and obtain the target timestamp;

[0158] Based on the target hash value and target timestamp of each piece of data, a new ID is calculated for each piece of data.

[0159] In one embodiment, when the processor 1001 calculates the new ID of each piece of data based on the target hash value and the target timestamp of each piece of data, the processor 1001 specifically performs the following operations:

[0160] A hash function is used to perform a combined hash process on the target hash value and the target timestamp of each piece of data to obtain a combined hash value of each piece of data;

[0161] According to the combined hash value of each piece of data, the new ID of each piece of data is calculated; the calculation formula of the new ID of each piece of data is:

[0162]

[0163] in, is the new ID for each piece of data. and is an integer, the user controls the number of bits of the shift, is the combined hash value of each piece of data, is the exclusive OR operator, is the amount of data for each piece of data, Indicates a bitwise left shift operation.

[0164] In one embodiment, when the processor 1001 updates the foreign key of each piece of data according to the new ID of each piece of data, the processor 1001 specifically performs the following operations:

[0165] Get the data dictionary of the target object;

[0166] Get the foreign key of each data from the data dictionary; the foreign key of each data is the old primary key of the data in other tables;

[0167] Store the association between the new ID and the old primary key of each piece of data, and obtain the mapping relationship of all data tables in the source database;

[0168] According to the old primary key of the data in other tables corresponding to the foreign key of each data, obtain the new ID of the data in other tables corresponding to the foreign key of each data from the mapping relationship;

[0169] Replace the old primary key of the data in other tables corresponding to the foreign key of each piece of data with the new ID of the data in other tables corresponding to the foreign key of each piece of data.

[0170] In one embodiment, when the processor 1001 generates a preset offset, the processor 1001 specifically performs the following operations:

[0171] Collect new ID-related data for each piece of data in real time. The new ID-related data includes the actual generated ID, system load, date and time;

[0172] Capture system status in real time and generate real-time data streams;

[0173] Extract the time, date, system load, and historical offset usage that affect ID generation from new ID-related data and real-time data streams to obtain the target factor set;

[0174] Construct and transform the target factor set into features to obtain real-time feature data;

[0175] The real-time feature data is input into a pre-trained offset determination model for avoiding ID conflicts; the offset determination model is a mathematical model for determining an offset for avoiding ID conflicts;

[0176] Output the optimal offset corresponding to the current time corresponding to the system state;

[0177] Use the best offset as the preset offset.

[0178] In one embodiment, when the processor 1001 generates a pre-trained offset determination model for avoiding ID conflicts, the processor 1001 specifically performs the following operations:

[0179] Collect historical ID-related data and its historical data stream when a new ID is generated for each first data in any data table of any database in a preset time period;

[0180] From the historical ID related data and its historical data stream, extract the historical time, historical date, historical system load, and historical offset usage that affect ID generation to obtain a historical factor set;

[0181] Fitting a quantitative chart of the historical factor set to obtain a quantitative chart of each first data;

[0182] Sending a quantitative chart of each first data to a client for display;

[0183] Receiving a historical offset input for avoiding ID conflicts for a displayed chart, and obtaining a historical offset of a quantized chart of each first data;

[0184] Using the historical offset as a label, annotate the historical ID-related data and its historical data stream when each first data generates a new ID, and obtain multiple annotated samples as model training data;

[0185] Based on the model training data, a pre-trained offset determination model for avoiding ID conflicts is generated.

[0186] In one embodiment, when the processor 1001 generates a pre-trained offset determination model for avoiding ID conflicts according to the model training data, the processor 1001 specifically performs the following operations:

[0187] Use the random forest algorithm to create an offset determination model for avoiding ID conflicts;

[0188] Input the model training data into the offset determination model for machine learning and output the model loss value;

[0189] When the model loss value reaches the minimum, a pre-trained offset determination model for avoiding ID conflicts is generated; or,

[0190] When the model loss value has not reached the minimum, the model loss value is back-propagated to update the model parameters of the offset determination model, and the step of inputting the model training data into the offset determination model for machine learning is continued until the model loss value reaches the minimum.

[0191] In an embodiment of the present application, on the one hand, by implementing a pre-established primary and foreign key generation strategy under a unified standard, a new primary key is automatically generated for each piece of data, and all related foreign keys are updated, thereby ensuring that the reference relationship between multiple tables remains correct after data migration, avoiding the tediousness and possibility of errors in manually updating associated fields, and improving the efficiency and accuracy of data operations; on the other hand, by checking the uniqueness of the primary keys of all data tables to be copied, the problems of primary key conflicts and foreign key reference errors in the data migration process are reduced, thereby reducing maintenance costs and potential business interruption risks.

[0192] Those skilled in the art can understand that all or part of the processes in the above-mentioned embodiments can be implemented by instructing related hardware through a computer program. The program for database replication of SaaS software can be stored in a computer-readable storage medium. When the program is executed, it can include the processes of the embodiments of the above-mentioned methods. The storage medium of the program for database replication of SaaS software can be a disk, an optical disk, a read-only storage memory, or a random access memory, etc.

[0193] The above disclosure is only the preferred embodiment of the present application, which certainly cannot be used to limit the scope of rights of the present application. Therefore, equivalent changes made according to the claims of the present application are still within the scope covered by the present application.

Claims

1. A database replication method for SaaS software, characterized in that: The method comprises: Connect to the source database of the SaaS software used by the target object and read every piece of data in every data table in the source database; According to a pre-defined primary and foreign key generation strategy, the primary key of each piece of data is replaced and the foreign key is updated to obtain multiple data tables to be copied; the primary and foreign key generation strategy is used to automatically generate a new primary key for each piece of data during the database copy process, replace the old primary key of each piece of data, and use the new primary key to update the foreign key of each piece of data; The primary and foreign key generation strategy includes a parameter acquisition module, a new ID calculation module, a primary key replacement module and a foreign key update module; The step of replacing the primary key and updating the foreign key of each piece of data according to the pre-defined primary and foreign key generation strategy includes: The parameter acquisition module acquires the current timestamp and the preset offset; The new ID calculation module calculates a new ID for each piece of data according to the current timestamp and the preset offset to obtain a new ID for each piece of data; The primary key replacement module uses the new ID of each piece of data as a new primary key to replace the old primary key of each piece of data, so as to replace the primary key of each piece of data; The foreign key update module updates the foreign key of each piece of data according to the new ID of each piece of data, so as to update the foreign key of each piece of data; wherein, Follow these steps to generate a preset offset, including: Collect new ID related data of each data in real time, wherein the new ID related data includes the actually generated ID, system load, date and time; Capture system status in real time and generate real-time data streams; Extracting the time, date, system load, and historical offset usage that affect ID generation from the new ID-related data and the real-time data stream to obtain a target factor set; Performing feature construction and feature transformation on the target factor set to obtain real-time feature data; Inputting the real-time feature data into a pre-trained offset determination model for avoiding ID conflicts; the offset determination model is a mathematical model for determining an offset for avoiding ID conflicts; Outputting the optimal offset corresponding to the system state at the current moment; Using the optimal offset as a preset offset; Check whether all primary keys of each data table to be copied are unique; In the case that all primary keys of each data table to be copied are unique, each data table to be copied is copied to the target database.

2. The method according to claim 1, characterized in that The step of calculating a new ID for each piece of data according to the current timestamp and the preset offset to obtain a new ID for each piece of data includes: Counting the data volume of each piece of data; Performing hash processing on the data volume of each piece of data using a hash function to convert the data volume of each piece of data into a hash value of a fixed length, thereby obtaining a target hash value of each piece of data; According to the preset offset, the current timestamp is dynamically adjusted to increase the discrimination of the current timestamp to obtain a target timestamp; Based on the target hash value of each piece of data and the target timestamp, a new ID for each piece of data is calculated.

3. The method according to claim 2, characterized in that The calculating a new ID of each piece of data based on the target hash value of each piece of data and the target timestamp includes: Using a hash function to perform combined hash processing on the target hash value of each piece of data and the target timestamp to obtain a combined hash value of each piece of data; According to the combined hash value of each piece of data, a new ID of each piece of data is calculated; wherein the calculation formula of the new ID of each piece of data is: Among them, the is the new ID for each piece of data. and is an integer, the user controls the number of bits of the shift, is the combined hash value of each piece of data, is the exclusive OR operator, is the data volume of each piece of data, Indicates a bitwise left shift operation.

4. The method according to claim 1, characterized in that: The updating of the foreign key of each piece of data according to the new ID of each piece of data includes: Obtaining a data dictionary of the target object; Obtaining the foreign key of each piece of data from the data dictionary; wherein the foreign key of each piece of data is the old primary key of data in other tables; The association relationship between the new ID and the old primary key of each piece of data is stored to obtain the mapping relationship of all data tables in the source database; According to the old primary key of the data in the other table corresponding to the foreign key of each piece of data, obtain the new ID of the data in the other table corresponding to the foreign key of each piece of data from the mapping relationship; The old primary key of the data in other tables corresponding to the foreign key of each piece of data is replaced with the new ID of the data in other tables corresponding to the foreign key of each piece of data.

5. The method according to claim 1, characterized in that The steps of generating a pre-trained offset determination model for avoiding ID conflicts include: Collect historical ID-related data and its historical data stream when a new ID is generated for each first data in any data table of any database in a preset time period; Extracting historical time, historical date, historical system load, and historical offset usage that affect ID generation from the historical ID-related data and its historical data stream to obtain a historical factor set; Fitting a quantitative chart of the historical factor set to obtain a quantitative chart of each first data; Sending a quantitative chart of each piece of first data to a client for display; Receiving a historical offset input for avoiding ID conflicts for a displayed chart, and obtaining a historical offset of a quantized chart of each first data; Using the historical offset as a label, annotating the historical ID-related data and its historical data stream when each first data generates a new ID, to obtain multiple annotated samples as model training data; A pre-trained offset determination model for avoiding ID conflicts is generated based on the model training data.

6. The method according to claim 5, characterized in that The step of generating a pre-trained offset determination model for avoiding ID conflicts according to the model training data includes: Use the random forest algorithm to create an offset determination model for avoiding ID conflicts; Inputting the model training data into the offset determination model for machine learning, and outputting a model loss value; When the model loss value reaches the minimum, a pre-trained offset determination model for avoiding ID conflicts is generated; or, When the model loss value has not reached the minimum, the model loss value is back-propagated to update the model parameters of the offset determination model, and the step of inputting the model training data into the offset determination model for machine learning is continued until the model loss value reaches the minimum.

7. The method according to claim 6, characterized in that The model loss function of the offset determination model for avoiding ID conflicts is: in, is the model loss value, is the number of samples of the model training data, The model predicts The prediction offset of samples, For the The actual offset of samples, The model parameters for the model are determined for the offsets used to avoid ID conflicts, It is The feature vector of the historical factor set of samples, is the bias term. By minimizing the loss function, the model learns the offset used to avoid ID conflicts to determine the model parameters of the model. and the deviation term This will make the predicted offset infinitely close to the actual offset, thus effectively avoiding ID conflicts.

8. A database replication system for SaaS software implemented using the method described in any one of claims 1 to 7, characterized in that: The system comprises: A connection module, used to connect to the source database of the SaaS software used by the target object and read each piece of data in each data table in the source database; A replacement and update module is used to replace the primary key and update the foreign key of each piece of data according to a pre-defined primary and foreign key generation strategy to obtain multiple data tables to be copied; the primary and foreign key generation strategy is used to automatically generate a new primary key for each piece of data during the database copy process, replace the old primary key of each piece of data, and use the new primary key to update the foreign key of each piece of data; The checking module is used to check whether all primary keys of each data table to be copied are unique; The replication module is used to replicate each of the to-be-replicated data tables to a target database when all primary keys of each of the to-be-replicated data tables are unique.

Images