A Heterogeneous Database Synchronization Method and System Based on Triggers and Message Queues

Abstract

This invention discloses a method and system for heterogeneous database synchronization based on triggers and message queues. The method includes creating a trigger function in the source database and binding the trigger function to a target business table to capture changes to the target business table in real time. When a structural change to the target business table is detected, an ORM model remapping is automatically triggered. The method integrates SQLAlchemy's automatic mapping function, dynamically generating ORM model category identifiers through metadata parsing, and using the ORM model to query relevant data and extract relevant fields to generate JSON messages containing idempotent keys. These JSON messages are then sent to a message queue, where a consumer retrieves them and filters out the target data. Conflict detection is performed on the target data, and final data is generated based on a preset configuration strategy before being applied to the target database. This invention effectively handles conflicts during data synchronization, ensuring data accuracy and consistency.

Description

Technical Field

[0001] This invention relates to the field of database data processing technology, and in particular to a method and system for synchronizing heterogeneous databases based on triggers and message queues. Background Technology

[0002] In today's digital age, data is a core asset for enterprises, and efficient synchronization between different systems is crucial for ensuring business continuity and data consistency. However, existing technologies have many shortcomings in synchronizing heterogeneous databases.

[0003] On the one hand, traditional data synchronization methods often rely on periodic polling mechanisms, which cannot capture data changes in real time, leading to synchronization delays. For example, in e-commerce systems, if updates to product inventory information are not synchronized to the promotional activity system in a timely manner, discrepancies may arise between promotional information and actual inventory, impacting user experience and business decisions. On the other hand, when the target business table structure in the source database changes, existing synchronization solutions typically require manual updates to the ORM model. This not only increases maintenance costs but also increases the risk of data synchronization errors due to manual mistakes. For instance, if developers fail to update the ORM model promptly and accurately after changes to the source database table structure, data loss or corruption may occur.

[0004] Furthermore, conflict handling is a critical issue during data synchronization. Existing technologies lack flexible and efficient strategies for handling data conflicts. Once a conflict exists between the target data and the source data, such as a primary key conflict or a field value conflict, it is often difficult to resolve quickly and accurately, thus affecting the accuracy and consistency of data synchronization. For example, in scenarios where multiple users are simultaneously editing the same data record, failure to effectively resolve conflicts may lead to data overwriting errors or data inconsistencies, reducing the reliability and stability of the system. Summary of the Invention

[0005] This invention provides a heterogeneous database synchronization method and system based on triggers and message queues, aiming to solve at least one of the technical problems existing in the prior art.

[0006] The technical solution of this invention is a heterogeneous database synchronization method based on triggers and message queues, which includes:

[0007] Create a trigger function in the source database and bind the trigger function to the target business table to capture changes to the target business table in real time. If a structural change to the target business table is detected, automatically trigger the ORM model remapping.

[0008] It integrates SQLAlchemy's automatic mapping function, dynamically generates ORM model category identifiers through metadata parsing, and uses the ORM model to query relevant data and extract relevant fields to generate JSON messages containing idempotent keys;

[0009] The JSON message containing the idempotent key is sent to the message queue. The consumer retrieves the JSON message containing the idempotent key from the message queue and filters out the target data from the JSON message containing the idempotent key.

[0010] The target data is subjected to conflict detection, and the final data is generated based on a preset configuration strategy. The final data is then applied to the target database to achieve data synchronization.

[0011] According to some embodiments of the present invention, it further includes:

[0012] Determine whether the final data has been successfully applied to the target database;

[0013] If the final data is successfully applied to the target database, the idempotent key of the target database is recorded in the Redis set, and the offset of the message queue is updated;

[0014] If the final data is not successfully applied to the target database, the final data is written to the compensation queue, enters the timed retry phase, and is returned to the message queue.

[0015] According to some embodiments of the present invention, it further includes:

[0016] Define the ConflictDetector interface of the ORM model, which includes the detect() detection and resolve() solution methods;

[0017] The ORM model is defined to support custom extensions, allowing arbitrary conflict resolution logic to be implemented through plugins;

[0018] The defined ORM model supports dynamic creation of strategies such as version priority, timestamp priority, node priority, and custom functions.

[0019] According to some embodiments of the present invention, the steps of performing conflict detection on the target data, generating final data based on a preset configuration strategy, and applying the final data to the target database include:

[0020] Detect whether there is a conflict between the target data and the source data;

[0021] If the target data conflicts with the source data, the conflict resolution process begins, and the final data after conflict resolution is returned. The final data is then applied to the target database.

[0022] If the target data does not conflict with the source data, there is no need to synchronize the data; the target data can be directly applied to the target database as the final data.

[0023] According to some embodiments of the present invention, when the target data conflicts with the source data, a conflict resolution phase is initiated, and the final data after conflict resolution is returned, and the final data is applied to the target database, including:

[0024] Obtain the version numbers of the target data and the source data respectively, and determine the priority of the version numbers of the target data and the source data;

[0025] If the version number of the source data is higher than the version number of the target data, then it is determined that there is a conflict between the target data and the source data.

[0026] The source data with the higher version number is retained as the final data. The version number of the final data is updated and the update time is recorded. The final data after resolving the conflict is returned and the final data is applied to the target database.

[0027] According to some embodiments of the present invention, automatically triggering ORM model remapping if a structural change in the target business table is detected includes:

[0028] When any write operation is detected in the target business table, the trigger is automatically executed to write the change information to a temporary synchronization table, thereby decoupling the business operation from the synchronization logic.

[0029] The temporary synchronization table is used as a buffer layer to initialize the source database connection and the automatic mapping of the ORM model, and the Base.prepare function is re-executed to automatically update the mapping.

[0030] According to some embodiments of the present invention, the trigger executes automatically, writing the change information into a temporary synchronization table, thereby decoupling business operations from synchronization logic, including:

[0031] The trigger automatically writes the structure change type, primary key change information, version change information, change processing status, and change time of the target business table into a temporary synchronization table. The change processing status includes pending status, synchronized status, and processing failure status.

[0032] The temporary synchronization table is scanned periodically, and the change information of the pending state in the temporary synchronization table is encapsulated into a JSON message containing idempotent keys;

[0033] The ORM model is used to directly extract the fields of JSON messages containing idempotent keys from the temporary synchronization table, automatically parse and generate category identifiers, and automatically map them.

[0034] According to some embodiments of the present invention, if the final data is not successfully applied to the target database, writing the final data to a compensation queue, entering a timed retry phase, and returning to the message queue includes:

[0035] If the final data is not successfully applied to the target database, a data synchronization failure message is sent, and the final data is placed in the compensation queue.

[0036] Enter the timed retry phase and return to the message queue. Use the exponential backoff strategy to retry and apply the final data to the target database.

[0037] If the number of retries exceeds the preset maximum number of retries, a dead letter log will be recorded, and a manual intervention prompt will be issued.

[0038] This invention also provides a heterogeneous database synchronization system based on triggers and message queues, comprising:

[0039] The data capture module is used to create trigger functions in the source database and bind the trigger functions to the target business table to capture changes to the target business table in real time. When a structural change to the target business table is detected, the ORM model is automatically remapped.

[0040] The message encapsulation module is used to integrate SQLAlchemy's automatic mapping function. It dynamically generates ORM model category identifiers by parsing source data, and uses the ORM model to query relevant data and extract relevant fields to generate JSON messages containing idempotent keys.

[0041] The message push module is used to send the JSON message containing idempotent keys to the message queue. The consumer retrieves the JSON message containing idempotent keys from the message queue and filters out the target data from the JSON message containing idempotent keys.

[0042] The consumption adaptation module is used to perform conflict detection on the target data, generate final data based on a preset configuration strategy, and apply the final data to the target database to achieve data synchronization.

[0043] The present invention also relates to a computer device, including a memory and a processor, wherein the processor performs the above-described method when executing a computer program stored in the memory.

[0044] The present invention also relates to a computer-readable storage medium storing computer program instructions thereon, which, when executed by a processor, implement the above-described method.

[0045] The heterogeneous database synchronization system based on triggers and message queues provided in this invention has at least one of the following advantages or beneficial effects: By creating trigger functions in the source database and binding them to the target business table, changes to the target business table can be captured in real time. This real-time capability ensures timely data synchronization; once the source data changes, subsequent processing can be triggered quickly. The system integrates SQLAlchemy's automatic mapping function to dynamically generate ORM model category identifiers, effectively handling changes in the structure of the target business table in the source database. When the structure of the target business table changes, the ORM model is automatically remapped, allowing subsequent data processing to be based on the latest table structure. The ORM model is used to query relevant data and extract relevant fields, generating JSON messages containing idempotent keys. The use of idempotent keys effectively avoids the problem of duplicate message processing. These JSON messages containing idempotent keys are sent to a message queue, and consumers retrieve them from the queue. This message queue-based transmission method offers excellent decoupling. Conflict detection is performed on the target data, and final data is generated based on a preset configuration strategy, effectively handling conflicts during data synchronization and ensuring data accuracy and consistency.

[0046] Furthermore, additional aspects and advantages of the invention will be set forth in part in the description which follows, and in part will be obvious from the description, or may be learned by practice of the invention. Attached Figure Description

[0047] Figure 1 This is a flowchart of the overall process of the heterogeneous database synchronization method based on triggers and message queues provided in the embodiments of the present invention;

[0048] Figure 2 This is a detailed flowchart of the first type of heterogeneous database synchronization method based on triggers and message queues provided in the embodiments of the present invention;

[0049] Figure 3 This is a second detailed flowchart of the heterogeneous database synchronization method based on triggers and message queues provided in the embodiments of the present invention;

[0050] Figure 4 This is a detailed flowchart of step S400 in the heterogeneous database synchronization method based on triggers and message queues provided in this embodiment of the invention;

[0051] Figure 5 This is a detailed flowchart of step S110 in the heterogeneous database synchronization method based on triggers and message queues provided in the embodiments of the present invention;

[0052] Figure 6 This is a detailed flowchart of step S530 in the heterogeneous database synchronization method based on triggers and message queues provided in the embodiments of the present invention;

[0053] Figure 7 This is a flowchart illustrating the heterogeneous database synchronization method based on triggers and message queues provided in this embodiment of the invention. Detailed Implementation

[0054] The following will provide a clear and complete description of the concept, specific structure, and technical effects of the present invention in conjunction with the embodiments and accompanying drawings, so as to fully understand the purpose, solution, and effects of the present invention.

[0055] It should be noted that, unless otherwise specified, when a feature is referred to as "fixed" or "connected" to another feature, it can be directly fixed or connected to the other feature, or indirectly fixed or connected to the other feature. The singular forms "a," "described," and "the" used herein are also intended to include the plural forms, unless the context clearly indicates otherwise. Furthermore, unless otherwise defined, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art. The terminology used in this specification is for the purpose of describing particular embodiments only and not for limiting the invention. The term "and / or" as used herein includes any combination of one or more of the associated listed items.

[0056] It should be understood that although the terms first, second, third, etc., may be used to describe various elements in this invention, these elements should not be limited to these terms. These terms are only used to distinguish elements of the same type from each other. For example, a first element may also be referred to as a second element without departing from the scope of the invention, and similarly, a second element may also be referred to as a first element. Any and all instances or exemplary language (“e.g.,” “such as,” etc.) provided herein are intended only to better illustrate embodiments of the invention and, unless otherwise required, do not impose a limitation on the scope of the invention.

[0057] Traditional data synchronization methods often rely on periodic polling mechanisms, which cannot capture data changes in real time, leading to synchronization delays. For example, in e-commerce systems, if updates to product inventory information are not synchronized to the promotional activity system in a timely manner, discrepancies may arise between promotional information and actual inventory, impacting user experience and business decisions. On the other hand, when the target business table structure in the source database changes, existing synchronization solutions typically require manual updates to the ORM model. This not only increases maintenance costs but also increases the risk of data synchronization errors due to manual mistakes. For instance, if developers fail to update the ORM model promptly and accurately after changes to the source database table structure, data loss or corruption may occur.

[0058] Furthermore, conflict handling is a critical issue during data synchronization. Existing technologies lack flexible and efficient strategies for handling data conflicts. Once a conflict exists between the target data and the source data, such as a primary key conflict or a field value conflict, it is often difficult to resolve quickly and accurately, thus affecting the accuracy and consistency of data synchronization. For example, in scenarios where multiple users are simultaneously editing the same data record, failure to effectively resolve conflicts may lead to data overwriting errors or data inconsistencies, reducing the reliability and stability of the system.

[0059] Based on this, embodiments of the present invention provide a heterogeneous database synchronization method and system based on triggers and message queues, which is beneficial to achieving real-time synchronization of heterogeneous databases and improving adaptability to table structure changes and conflict handling capabilities.

[0060] Reference Figures 1 to 7 The embodiments of the present invention further elaborate on the heterogeneous database synchronization method and system based on triggers and message queues.

[0061] Reference Figure 1 As shown, Figure 1 This is a general flowchart of the heterogeneous database synchronization method based on triggers and message queues provided in this embodiment of the invention. The heterogeneous database synchronization method based on triggers and message queues includes, but is not limited to, steps S100 to S400. Specifically,

[0062] S100: Create a trigger function in the source database and bind the trigger function to the target business table to capture changes to the target business table in real time. If a structural change to the target business table is detected, the ORM model will be automatically remapped.

[0063] S200: Integrates SQLAlchemy automatic mapping function, dynamically generates ORM model category identifiers through metadata parsing, and uses ORM models to query relevant data and extract relevant fields to generate JSON messages containing idempotent keys;

[0064] S300: Sends JSON messages containing idempotent keys to the message queue. Consumers retrieve JSON messages containing idempotent keys from the message queue and filter out the target data from the JSON messages containing idempotent keys.

[0065] S400: Performs conflict detection on the target data, generates final data based on a preset configuration strategy, and applies the final data to the target database to achieve data synchronization.

[0066] In some embodiments of the present invention, the heterogeneous database synchronization method based on triggers and message queues includes:

[0067] In step S100, a trigger function is first created in the source database and bound to the target business table. This allows for real-time capture of changes to the target business table, ensuring timely data synchronization. Once the source data changes, subsequent processing can be triggered quickly. For example, in an e-commerce system, when the product inventory table (target business table) changes, such as a decrease in product inventory, this real-time capture mechanism ensures that relevant promotional activity data (data in the target database) is updated promptly, preventing inaccurate promotional information due to data delays.

[0068] By automatically triggering relevant operations, manual intervention is reduced, the probability of errors is lowered, and the efficiency and reliability of data processing are improved. A database metadata monitoring interface is used to listen for changes in the target business table structure in the source database. When a structural change in the target business table is detected, the ORM model is automatically remapped, eliminating the need for manual updates to the ORM model, reducing maintenance costs, and improving development efficiency.

[0069] Understandably, in one implementation, the trigger functionality provided by the source database is used. For example, in MySQL, triggers can be created using the `CREATE TRIGGER` statement. Triggers can be bound to specific operations (such as `INSERT`, `UPDATE`, and `DELETE`) on the target business table, and the trigger is fired when these operations occur. A trigger function is written, which defines the specific logic to be executed when the trigger is fired. This function can be a stored procedure or other forms of executable code supported by the database.

[0070] To use SQLAlchemy's auto-mapping feature, create a base class for auto-mapping using the `automap_base()` method, and then use the `automap_base.prepare()` method to prepare for auto-mapping. During preparation, you can specify the database connection and the table names to be mapped. SQLAlchemy will automatically generate the corresponding ORM model category identifier based on the target business table structure in the database.

[0071] In step S200, by combining the automap function of the ORM model (such as SQLAlchemy), when a change in the target business table structure is detected, the ORM model category identifier is automatically regenerated through the SQLAlchemy automap function. This can quickly and accurately generate the ORM model category identifier based on the target business table structure of the source database, accurately map the fields, relationships and other information in the table, reduce the time and workload of manually writing the ORM model, improve development efficiency, and reduce errors caused by manually writing the model.

[0072] Use the query interface provided by the ORM model (such as the `query` method in SQLAlchemy) to retrieve relevant data. Based on the query results, extract relevant fields, and use Python dictionary operations to convert the fields in the query results into JSON format, adding idempotent keys to the JSON messages. An idempotent key is a unique identifier, such as a primary key in a database, ensuring the uniqueness and repeatability of messages. By adding idempotent keys, the uniqueness of JSON messages is ensured, avoiding duplicate messages and improving the reliability of message processing. For example, in an order processing system, order status update messages may be sent repeatedly due to network issues; an idempotent key ensures that the order status is updated correctly only once.

[0073] In step S300, a suitable message queue technology, such as RabbitMQ or Kafka, is selected. Using the message queue client SDK, JSON messages containing idempotent keys are sent to the specified message queue. For example, for RabbitMQ, the pika library can be used to send JSON messages containing idempotent keys. Sending JSON messages containing idempotent keys to the message queue achieves asynchronous decoupling between producers and consumers. Producers do not need to wait for consumers to process messages, improving system throughput and response speed. For example, changes to source data and updates to the target database are decoupled through message queues, allowing the two systems to run and scale independently. For instance, in a distributed system, the source and target databases may be deployed on different servers; message queues facilitate cross-server data synchronization. Using the message queue consumer SDK, JSON messages containing idempotent keys are received from the message queue. For example, for Kafka, the kafka-python library can be used to consume JSON messages containing idempotent keys. After receiving the message, the JSON message containing idempotent keys is parsed, and the target data is filtered out based on business requirements and idempotent key information. Python's JSON library can be used to parse JSON messages and accurately extract target data by writing filtering logic, providing an accurate data source for subsequent data processing.

[0074] In step S400, conflict detection is performed on the target data according to business requirements and preset conflict detection rules. For example, it can check whether the target data conflicts with existing data in the target database, such as primary key conflicts or field value conflicts. Final data is generated based on a preset configuration strategy, which may include data update strategies and data merging strategies. The final data is then applied to the target database using the data update interface provided by the target database to update the data. Through conflict detection and the application of configuration strategies, the consistency between the data in the target database and the data in the source database is ensured, meeting the diverse data synchronization needs of heterogeneous databases and improving the system's adaptability and scalability.

[0075] Reference Figure 2 As shown, Figure 2 This is a detailed flowchart of the first embodiment of the heterogeneous database synchronization method based on triggers and message queues provided by the present invention. The heterogeneous database synchronization method based on triggers and message queues also includes, but is not limited to, steps S510 to S530. Specifically,

[0076] S510: Determine whether the final data has been successfully applied to the target database;

[0077] S520: If the final data is successfully applied to the target database, record the idempotent key of the target database to the Redis set and update the offset of the message queue;

[0078] S530: If the final data is not successfully applied to the target database, the final data is written to the compensation queue, the timed retry phase begins, and the message queue is returned.

[0079] In some embodiments of the present invention, the heterogeneous database synchronization method based on triggers and message queues further includes: determining whether the final data has been successfully applied to the target database. By explicitly determining whether the final data has been successfully applied to the target database, the integrity and accuracy of data synchronization can be ensured. Only after the data has indeed been successfully written to the target database will subsequent idempotent key records and offset updates be performed, avoiding data loss or duplicate processing.

[0080] Specifically, storing idempotent keys in a Redis collection effectively avoids processing the same data repeatedly. In distributed systems, idempotency is crucial for ensuring data consistency. Quickly querying idempotent keys using a Redis collection allows for rapid determination of whether data has already been processed. Redis collections are high-performance in-memory databases capable of fast read and write operations. Storing idempotent keys in a Redis collection ensures efficient idempotency checks, preventing excessive latency introduced by these checks.

[0081] Updating the offset of a message queue ensures that messages in the queue are processed correctly. In a distributed system, multiple consumers may retrieve messages from the message queue simultaneously. By updating the offset, it can be ensured that each consumer can process its own message correctly, avoiding duplicate consumption or omission of messages in the message queue.

[0082] If the data is ultimately not successfully applied, it is written to a compensation queue and put into a timed retry phase. This effectively handles errors during data synchronization, quickly identifying and resolving problems, thus improving system reliability and stability. By writing data to the compensation queue, message loss in the message queue is avoided. Even after the first synchronization failure, the data can still be reprocessed through the retry mechanism, ensuring that the data is eventually successfully synchronized to the target database, thereby improving system fault tolerance.

[0083] Therefore, in steps S510 to S530 above, idempotent key records and offset updates ensure the consistency and accuracy of data synchronization; compensation queues and timed retry mechanisms improve the system's fault tolerance and reliability; and Redis's high-performance characteristics ensure the efficiency of idempotency checks. Offset updates in the message queue ensure coordinated and consistent message processing within the distributed system.

[0084] Reference Figure 3 As shown, Figure 3 This is a detailed flowchart of the second embodiment of the heterogeneous database synchronization method based on triggers and message queues provided by the present invention. The heterogeneous database synchronization method based on triggers and message queues also includes, but is not limited to, steps S101 to S103. Specifically,

[0085] S101: Defines the ConflictDetector interface for the ORM model, which includes the detect() detection and resolve() solution methods;

[0086] S102: Define the ORM model to support custom extensions and implement arbitrary conflict resolution logic through plugins;

[0087] S103: Defines ORM models that support dynamic creation of strategies such as version priority, timestamp priority, node priority, and custom functions.

[0088] In step S101, ConflictDetector is defined using an interface to provide a unified specification for different conflict detection and resolution logic. This approach allows different implementation classes to provide specific conflict handling logic for different business scenarios, while the caller only needs to rely on the interface to operate, which conforms to the open / closed principle. Generics are used. <t>This allows ConflictDetector to be applied to different types of ORM model entities, enhancing the versatility and flexibility of the interface.

[0089] Separating conflict detection and resolution logic from specific ORM model operations reduces coupling between modules. Different conflict handling logics can be developed and maintained independently without affecting each other. By implementing the ConflictDetector interface, new conflict handling logic can be easily added without modifying the existing code structure, improving system scalability. Due to the interface's generality, different ORM models can reuse the same ConflictDetector implementation, reducing code redundancy.

[0090] In one embodiment, the conflict detection interface call

[0091] Define the abstract interface ConflictStrategyFactory:

[0092] - detect(source_data, target_data): Detects whether there is a conflict between the source data and the target data (returns a boolean value).

[0093] - resolve(source_data, target_data): Returns the final data after resolving the conflict.

[0094] In step S102, a plugin interface, such as `ConflictResolverPlugin`, is defined, and different plugins implement this interface to provide custom conflict resolution logic. The ORM model maintains a list of plugins, and when a conflict needs to be resolved, the plugin's `resolveConflict` method is called in a specific order. This plugin interface and list approach allows users to dynamically add custom conflict resolution logic without modifying the core ORM model code, providing high flexibility and scalability. By combining Java's reflection mechanism or class loader technology, plugins can be dynamically loaded and registered, allowing users to implement highly customized conflict resolution logic to meet complex needs in different business scenarios. This allows different plugins to be loaded at runtime based on configuration files or user input, further enhancing system flexibility. Decoupling the custom conflict resolution logic from the core functionality of the ORM model allows for independent development and maintenance, reducing system complexity.

[0095] In step S103, within the ORM model, strategies are dynamically created and used based on configuration. The strategy pattern is used to encapsulate different conflict resolution strategies, allowing clients to choose different strategies to resolve conflicts according to different needs. This approach separates the use of the algorithm from its implementation, adhering to the open / closed principle. Combined with the factory pattern, different strategy instances are dynamically created based on configuration. The factory can determine which strategy object to create based on input parameters (such as version priority, timestamp priority, node priority), further improving the system's flexibility and scalability.

[0096] Users can choose different conflict resolution strategies based on varying business needs, or provide custom strategies, without modifying the core code of the ORM model, thus improving system flexibility. By adding new strategy implementation classes, the system's conflict resolution capabilities can be easily extended without altering the existing code structure, enhancing system scalability.

[0097] In one embodiment, conflict resolution strategy execution includes:

[0098] - Version priority strategy:

[0099] Detection: If the version number of the source data is higher than that of the target data, a conflict is determined.

[0100] Solution: Retain data with higher version numbers.

[0101] - Custom strategy:

[0102] Load user-written functions (such as price_conflict_handler), example (e-commerce price conflict):

[0103] Detection: A conflict is triggered when the price difference is greater than 10%.

[0104] Solution: Keep the record with the latest update time and higher price, and update the version number.

[0105] Therefore, steps S101 to S103 above, through interfaces and generics, provide a unified standard and high flexibility for conflict detection and resolution, reducing coupling between modules and improving the system's scalability and reusability. Plugin interfaces and dynamic loading technology allow users to dynamically add custom conflict resolution logic, enhancing system flexibility and customizability while reducing system complexity. By encapsulating different conflict resolution strategies using the strategy pattern and dynamically creating strategy instances using the factory pattern, users can choose the appropriate strategy to resolve conflicts according to different needs, improving the system's flexibility, scalability, and decoupling. Simultaneously, the use of functional programming improves the system's usability.

[0106] Reference Figure 4 As shown, Figure 4 This is a detailed flowchart of step S400 in the heterogeneous database synchronization method based on triggers and message queues provided in this embodiment of the invention. Step S400 includes, but is not limited to, steps S410 to S430. Specifically,

[0107] S410: Obtain the version numbers of the target data and the source data respectively, and determine the priority of the version numbers of the target data and the source data;

[0108] S420: If the version number of the source data is higher than the version number of the target data, then it is determined that there is a conflict between the target data and the source data;

[0109] S430: Retain the source data with the higher version number as the final data, update the version number of the final data and record the update time, return the final data after resolving the conflict, and apply the final data to the target database.

[0110] In some embodiments of the present invention, a `VersionPriorityStrategy` class is defined, implementing the `ConflictResolutionStrategy` interface. This class is responsible for comparing the version numbers of two data entities and resolving conflicts based on the priority of the version numbers. A `DataEntity` class is defined, containing a version number, update time, and data content. The version number is used for priority comparison, and the update time records the last modification time of the data. In the `resolveConflict` method, the version numbers of the target data and the source data are compared. If the version number of the source data is higher, a conflict is determined, the source data is retained as the final data, and the version number and update timestamp are updated. If the version number of the target data is higher or equal to the target data, there is no conflict, and the target data is returned.

[0111] Understandably, the ORMModel class uses ConflictResolutionStrategy to resolve conflicts; the resolveConflict method calls the strategy's resolveConflict method to resolve conflicts; and the applyToTargetDatabase method simulates applying the final data to the target database. By applying the final data after conflict resolution to the target database, version-based conflicts can be handled flexibly, ensuring data consistency and priority.

[0112] In some embodiments of the present invention, step S100 in the heterogeneous database synchronization method based on triggers and message queues includes, but is not limited to, steps S110 to S120. Specifically,

[0113] S110: When any write operation is detected in the target business table, the trigger will be executed automatically to write the change information to a temporary synchronization table, thereby decoupling the business operation from the synchronization logic.

[0114] S120: Use the temporary synchronization table as a buffer layer, initialize the source database connection and ORM model automatic mapping, and re-execute the Base.prepare function to automatically update the mapping.

[0115] In the source database, a trigger is created. When a write operation (e.g., INSERT, UPDATE, DELETE) occurs on the target business table, the changed information is written to a temporary synchronization table. The database trigger handles write operations directly at the database level, reducing the application's burden and improving performance. Simultaneously, it decouples business operations from synchronization logic; write operations on the business table do not directly involve synchronization logic, reducing code complexity. The dynamic update mechanism of the ORM model allows the system to adapt to changes in the database table structure at runtime, improving system flexibility.

[0116] Using Python's SQLAlchemy as the ORM framework, the source database connection is initialized and the ORM model is automatically mapped. The Base.prepare function is called again to dynamically update the ORM mapping. The automatic mapping and dynamic updating of the ORM model further decouple the database table structure from the code logic, so that changes in the database table structure will not directly affect the code logic.

[0117] The automatic mapping and dynamic update mechanisms of ORM frameworks reduce the workload of manually maintaining database table and code mappings and reduce code redundancy. In this way, business operations and synchronization logic can be decoupled, while the automatic mapping and dynamic update mechanisms of ORM frameworks can improve the flexibility and maintainability of the system.

[0118] Reference Figure 5 As shown, Figure 5 This is a detailed flowchart of step S110 in the heterogeneous database synchronization method based on triggers and message queues provided in this embodiment of the invention. Step S110 includes, but is not limited to, steps S111 to S113. Specifically,

[0119] S111: The trigger automatically writes the structure change type, primary key change information, version change information, change processing status and change time of the target business table into the temporary synchronization table. The change processing status includes pending status, synchronized status and processing failure status.

[0120] S112: Periodically scan the temporary synchronization table and encapsulate the change information of the pending state in the temporary synchronization table into a JSON message containing idempotent keys;

[0121] S113: Use the ORM model to directly extract fields of JSON messages containing idempotent keys from the temporary synchronization table, automatically parse and generate category identifiers, and automatically map them.

[0122] In some embodiments of the present invention, when a write operation occurs on the target business table, including modifications to the target business table structure (such as adding fields, modifying field types, etc.), for example, when INSERT, UPDATE, or DELETE operations occur, the trigger automatically writes the target business table's structure change type, primary key information, version information, change processing status, and change time to a temporary synchronization table. The change processing status includes pending, synchronized, and failed processing statuses. The temporary synchronization table is scanned to find change information with a status of "pending". The "pending" change information is encapsulated in JSON format and includes an idempotent key. The ORM model is used to extract the JSON message field containing the idempotent key from the temporary synchronization table, and a category identifier is generated based on the change type. The generated category identifier is automatically mapped to the corresponding processing logic. This process ensures that data changes can be synchronized to other systems in a timely manner, and the uniqueness and consistency of messages are guaranteed through the idempotent key.

[0123] Reference Figure 6 As shown, Figure 6 This is a detailed flowchart of step S530 in the heterogeneous database synchronization method based on triggers and message queues provided in this embodiment of the invention. Step S530 includes, but is not limited to, steps S531 to S533. Specifically,

[0124] S531: If the final data is not successfully applied to the target database, send a data synchronization failure message and put the final data into the compensation queue;

[0125] S532: Enter the timed retry phase and return to the message queue. Use the exponential backoff strategy to retry and apply the final data to the target database.

[0126] S533: If the number of retries exceeds the preset maximum number of retries, record a dead letter in the log and issue a manual intervention prompt.

[0127] In some embodiments of this invention, if the final data is not successfully applied to the target database, a data synchronization failure message is sent to other systems via a message queue, decoupling the data synchronization process from the error handling process. The message queue provides persistent storage for messages, ensuring that messages are not lost. The message queue supports asynchronous processing, which can improve the system's response speed and throughput. Failed data is placed in a compensation queue to ensure that data is not lost and can be retried later. The compensation queue is isolated from the normal queue to prevent failed data from affecting the processing of normal data. Data in the compensation queue can be centrally managed and retried. A scheduled task periodically checks the data in the compensation queue and automatically retries, reducing manual intervention.

[0128] Afterwards, the system enters a timed retry phase and returns to the message queue. It then uses an exponential backoff strategy to retry the data and finally applies the data to the target database. The exponential backoff strategy can avoid competition and conflicts caused by multiple systems retrying simultaneously. It can improve system reliability and reduce system crashes caused by frequent retries.

[0129] If the number of retries exceeds the preset maximum number of retries, a dead letter log is recorded. Recording dead letter logs facilitates subsequent troubleshooting and problem analysis. The log system allows for real-time monitoring of the system's operating status, timely detection and handling of problems, and provides audit evidence for data processing.

[0130] When the number of retries exceeds the preset maximum number of retries, a prompt for manual intervention will be issued, such as through an alarm system (e.g., email, SMS, instant messaging tools), to promptly notify relevant personnel, ensuring that the problem can be handled in a timely manner, reducing user waiting time, and improving user experience.

[0131] In steps S531 to S533, a multi-level failure compensation strategy is constructed through message queues, compensation queues, timed retries, and exponential backoff strategies to ensure high reliability of data synchronization; the availability of the system is improved through automatic retries and manual intervention prompts; the maintainability of the system is improved through logging and alarm systems; and the user experience is improved through timely notification and problem handling.

[0132] Scheduled tasks can retry at preset time intervals, improving system efficiency. The retry interval and frequency can be adjusted according to business needs. For example, a scheduled task (e.g., every 1 second) scans a temporary synchronization table and encapsulates "pending" changes into a JSON message containing an idempotent key. The idempotent key format is "table name + primary key + version," ensuring that Redis does not process the same change message repeatedly. Utilizing ORM model automatic mapping, fields of the target business table object (such as title and status) can be directly extracted without manually concatenating SQL, reducing coding complexity.

[0133] When synchronization fails, the final data is placed in a compensation queue (e.g., media.sync.redis.retry) and retried using an exponential backoff strategy (e.g., 10 seconds after the first retry, 30 seconds after the second). After the maximum number of retries is exceeded, a dead letter is logged, requiring manual intervention.

[0134] The code for the exponential backoff strategy retry mechanism is as follows:

[0135] def retry_failed_messages():

[0136] retry_consumer = KafkaConsumer(

[0137] "media.sync.redis.retry",

[0138] bootstrap_servers=["localhost:9092"],

[0139] group_id="media-redis-retry-group" )

[0141] for msg in retry_consumer:

[0142] # Maximum 3 retries

[0143] retry_count = msg.value.get("retry_count", 0)

[0144] if retry_count < 3:

[0145] msg.value["retry_count"] = retry_count + 1

[0146] # Resend to message queue and retry

[0147] producer.send(topic="media.sync.redis", value=msg.value)

[0148] else:

[0149] # If the number of retries exceeds the limit, log a dead letter in the log.

[0150] print(f"Dead letter message: {msg.value['idempotent_key']}")

[0151] # Write to the dead letter table, manual intervention required.

[0152] #db.session.add(DeadLetterLog(**msg.value))

[0153] In one embodiment, a heterogeneous database synchronization method based on triggers and message queues is applied to the real-time synchronization of media asset information changes in a media asset management system (PostgreSQL ➜ Redis).

[0154] Application Scenario: In a media asset management system, media asset information (such as metadata for video and audio files) is stored in the target business table (media_assets table) of a PostgreSQL database. Simultaneously, it needs to be synchronized to a Redis cache in real time to support high-concurrency query requirements (such as front-end playlist loading and media asset status verification). When media asset information is added, updated, or deleted in the PostgreSQL database, the "trigger + message queue Kafka + ORM model mapping" solution of this invention is used to achieve real-time consistency maintenance of the Redis cache. The requirements are as follows:

[0155] Write the changes to the PostgreSQL database;

[0156] The media asset information is synchronized to the Redis cache layer in real time for fast access by the front-end system;

[0157] Avoid duplicate writes, data inconsistencies, or omissions of structural changes;

[0158] It has an automatic fault recovery and failure compensation mechanism.

[0159] Core synchronization target

[0160] Source database: PostgreSQL (stores complete media asset information, media_assets table)

[0161] Target database: Redis (caches frequently accessed data, uses a hash structure for storage, and the key format is media:{id})

[0162] Synchronous trigger events: INSERT / UPDATE / DELETE operations on the media_assets table

[0163] Specifically,

[0164] Deploying triggers in a PostgreSQL database (source database)

[0165] Create target business table and temporary synchronization table

[0166] Media Asset Information Main Table (Target Business Table)

[0167] CREATE TABLE media_assets (

[0168] ID SERIAL PRIMARY KEY, -- Unique identifier for media assets

[0169] title VARCHAR(255) NOT NULL, -- Title

[0170] file_path VARCHAR(512) NOT NULL, -- file path

[0171] status VARCHAR(20) NOT NULL, -- status: draft / published / deleted

[0172] version INT NOT NULL DEFAULT 1, -- Version number (used for conflict detection)

[0173] update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- Update time );

[0175] Temporary synchronization table to store change records

[0176] CREATE TABLE t_temp (

[0177] id SERIAL PRIMARY KEY,

[0178] src_table VARCHAR(64) NOT NULL, -- Target business table name: fixed as media_assets

[0179] src_pk VARCHAR(64) NOT NULL, -- The primary key value of the target business table (i.e., media_assets.id)

[0180] action VARCHAR(10) NOT NULL, -- Operation type: INSERT / UPDATE / DELETE

[0181] structure_version INT NOT NULL DEFAULT 1, -- Target business table structure version

[0182] state INT DEFAULT 1, -- Processing status: 1-Pending, 2-Synchronized, 3-Failed

[0183] create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- Record time

[0184] Create a trigger function and bind the trigger function to the target business table.

[0185] -- Trigger function: Captures changes to the target business table `media_assets` and writes them to the temporary synchronization table `t_temp`.

[0186] CREATE OR REPLACE FUNCTION media_sync_trigger_func() RETURNS TRIGGERAS $$

[0187] BEGIN

[0188] -- Record changes based on operation type

[0189] IF TG_OP = 'INSERT' THEN

[0190] INSERT INTO t_temp (src_table, src_pk, action)

[0191] VALUES ('media_assets', NEW.id::TEXT, 'INSERT');

[0192] RETURN NEW

[0193] ELSIF TG_OP = 'UPDATE' THEN

[0194] INSERT INTO t_temp (src_table, src_pk, action)

[0195] VALUES ('media_assets', NEW.id::TEXT, 'UPDATE');

[0196] RETURN NEW

[0197] ELSIF TG_OP = 'DELETE' THEN

[0198] INSERT INTO t_temp (src_table, src_pk, action)

[0199] VALUES ('media_assets', OLD.id::TEXT, 'DELETE');

[0200] RETURN OLD;

[0201] END IF;

[0202] END;

[0203] $$ LANGUAGE plpgsql;

[0204] -- Bind triggers to the target business table

[0205] media_assets table

[0206] CREATE TRIGGER media_assets_sync_trigger

[0207] AFTER INSERT OR UPDATE OR DELETE ON media_assets

[0208] FOR EACH ROW EXECUTE FUNCTION media_sync_trigger_func();

[0209] When any write operation occurs in the target business table media_assets, the trigger is executed automatically, writing information such as the change type and primary key to the temporary synchronization table t_temp, thereby decoupling the business operation from the synchronization logic.

[0210] The temporary synchronization table t_temp serves as a buffer layer to prevent direct manipulation of the message queue Kafka from blocking the main business process.

[0211] Message queue Kafka listening & ORM dynamic model mapping (SQLAlchemy Automap)

[0212] Automatic mapping during ORM model initialization

[0213] # Dependencies: SQLAlchemy + kafka-python

[0214] from sqlalchemy import create_engine

[0215] from sqlalchemy.ext.automap import automap_base

[0216] from sqlalchemy.orm import Session

[0217] # Initialize source database connection and automatically map ORM model

[0218] SOURCE_DB_URL = "postgresql: / / user:pass@localhost:5432 / media_db"

[0219] engine = create_engine(SOURCE_DB_URL)

[0220] Base = automap_base() # Automatic mapping base class

[0221] Base.prepare(engine, reflect=True) # Key: Automatically parse table structure to generate Python class

[0222] # Automatically map target business tables and temporary synchronization tables

[0223] MediaAssets = Base.classes.media_assets # Mapping class for the main media asset table (target business table)

[0224] TempTable = Base.classes.t_temp # Temporary synchronization table mapping class

[0225] SQLAlchemy's automap feature eliminates the need to manually define MediaAssets and TempTable model classes; they are generated directly from database metadata, reducing configuration workload.

[0226] When the structure of the target business table `media_assets` changes (e.g., a `duration` field is added), the mapping can be automatically updated by re-executing the `Base.prepare` function, without requiring code modification.

[0227] Scan the temporary synchronization table and push messages to the Kafka message queue.

[0228] from kafka import KafkaProducer

[0229] import json

[0230] import datetime from datetime

[0231] # Initialize the Kafka message queue producer

[0232] producer = KafkaProducer(

[0233] bootstrap_servers=["localhost:9092"],

[0234] value_serializer=lambda v: json.dumps(v).encode("utf-8") )

[0236] def sync_to_kafka():

[0237] # Scan the "Pending" records in the temporary synchronization table

[0238] with Session(engine) as session:

[0239] pending_records = session.query(TempTable).filter_by(state=1).all()

[0240] for record in pending_records:

[0241] Query complete data from the source table using the ORM model.

[0242] media = session.query(MediaAssets).filter_by(id=record.src_pk).first()

[0243] Construct standardized messages (message encapsulation)

[0244] sync_msg = {

[0245] "table": "media_assets",

[0246] "primary_key": record.src_pk,

[0247] "action": record.action, # INSERT / UPDATE / DELETE

[0248] "timestamp": datetime.utcnow().isoformat() + "Z",

[0249] "version": media.version if media else None, # Used for conflict detection

[0250] "data": { # Core fields of media assets, automatically extracted through the ORM model

[0251] "id": media.id,

[0252] "title": media.title,

[0253] "file_path": media.file_path,

[0254] "status": media.status,

[0255] "version": media.version

[0256] If media else None,

[0257] "idempotent_key": f"media_assets_{record.src_pk}_{media.version if media else 'delete'}" # Idempotent key

[0258] }

[0259] Push to a Kafka message queue topic (partitioned by table name for easy consumption).

[0260] producer.send(topic="media.sync.redis", value=sync_msg)

[0261] producer.flush()

[0262] Mark the temporary synchronization table record as "processed".

[0263] record.state = 2

[0264] session.commit()

[0265] print(f"Push message: {sync_msg['idempotent_key']}")

[0266] The consumer retrieves JSON messages containing idempotent keys from the message queue, filters out the target data from these JSON messages, and then performs conflict detection on the target data using the following code.

[0267] detector = VersionPriorityDetector(version_field="version")

[0268] if detector.detect(incoming, existing):

[0269] final = detector.resolve(incoming, existing)

[0270] redis_client.hmset(redis_key, final)

[0271] else:

[0272] print("No conflict occurred, no synchronization required.")

[0273] If a conflict is detected between the target data and the source data, a version-priority strategy is adopted. The code implementation is as follows:

[0274] class VersionPriorityDetector(ConflictStrategyFactory):

[0275] def __init__(self, version_field="version"):

[0276] self.version_field = version_field

[0277] def detect(self, source, target):

[0278] return source.get(self.version_field, 0) != target.get(self.version_field, 0)

[0279] def resolve(self, source, target):

[0280] # Select the version number with the higher one as the final data.

[0281] return source if source.get(self.version_field, 0) >target.get(self.version_field, 0) else target

[0282] Based on the above code, combined with Figure 7 As shown, a complete embodiment of the heterogeneous database synchronization method based on triggers and message queues provided by the present invention is described.

[0283] A trigger function is created in the source database and bound to the target business table. When the data in the source database changes, the trigger captures the change operation in the target business table and writes the change operation to a temporary table (temporary synchronization table). The SQLAlchemy auto-mapping function is integrated to automatically trigger the ORM model remapping. After that, relevant data in the target business table is queried and relevant fields are extracted to generate a JSON message containing idempotent keys. The JSON message containing idempotent keys is pushed to the message queue Kafka. The consumer retrieves the JSON message containing idempotent keys from the message queue Kafka and queries the target data. Conflict detection is performed by checking the consistency between the JSON message containing idempotent keys and the target data. If there is a conflict between the JSON message containing idempotent keys and the target data, the conflict resolution stage is entered and the final data is generated. If there is no conflict between the JSON message containing idempotent keys and the target data, the target data is directly used as the final data. Finally, the final data is applied to the target database, and it is determined whether the final data has been successfully applied to the target database. If so, the idempotent key is recorded in the Redis set, and the offset of the message queue is updated. If not, the final data is written to the compensation queue, the timed retry phase begins, and the message queue is returned.

[0284] This invention also provides a heterogeneous database synchronization system based on triggers and message queues to implement the heterogeneous database synchronization method based on triggers and message queues provided in the above embodiments. The heterogeneous database synchronization system based on triggers and message queues includes a data capture module, a message encapsulation module, a message push module, and a consumer adaptation module. Specifically, the data capture module is used to create trigger functions in the source database and bind the trigger functions to the target business table to capture changes in the target business table in real time. When a structural change in the target business table is detected, an ORM model remapping is automatically triggered. The message encapsulation module is used to integrate the SQLAlchemy automatic mapping function, dynamically generate ORM model category identifiers through source data parsing, and use the ORM model to query relevant data and extract relevant fields to generate JSON messages containing idempotent keys. The message push module is used to send the JSON messages containing idempotent keys to the message queue. Consumers obtain the JSON messages containing idempotent keys from the message queue and filter out target data from the JSON messages containing idempotent keys. The consumer adaptation module is used to perform conflict detection on the target data, generate final data based on a preset configuration strategy, and apply the final data to the target database to achieve data synchronization.

[0285] By creating trigger functions in the source database and binding them to the target business table, changes to the target business table can be captured in real time. This real-time capability ensures timely data synchronization; once the source data changes, subsequent processing can be triggered quickly. Integrating SQLAlchemy's automatic mapping function dynamically generates ORM model category identifiers, effectively handling changes in the target business table structure in the source database. When the target business table structure changes, the ORM model is automatically remapped, ensuring subsequent data processing is based on the latest table structure. The ORM model is used to query relevant data and extract relevant fields, generating JSON messages containing idempotent keys. The use of idempotent keys effectively avoids message duplication. These JSON messages with idempotent keys are sent to a message queue, and consumers retrieve them from the queue. This message queue-based transmission method offers excellent decoupling. Conflict detection is performed on the target data, and final data is generated based on a preset configuration strategy, effectively handling conflicts during data synchronization and ensuring data accuracy and consistency.

[0286] In some embodiments of the present invention, the heterogeneous database synchronization system based on triggers and message queues further includes a configuration and auditing module, which is used for:

[0287] Visual configuration: A graphical configuration interface for field mapping, synchronization strategies, and conflict rules. Specifically,

[0288] Field mapping: Allows users to configure the mapping relationship between source database fields and target database fields through a graphical interface;

[0289] Synchronization strategy: Configure the data synchronization strategy, such as synchronization frequency, triggering conditions, etc.;

[0290] Conflict rules: Define the rules for handling data conflicts, such as priority and coverage strategy.

[0291] The graphical interface allows non-technical users to easily configure the system. Users can flexibly adjust the configuration according to business needs without modifying the code. The configuration information is centrally managed, which facilitates subsequent maintenance and updates.

[0292] Real-time monitoring: A dashboard displaying over 50 metrics including synchronization latency, success rate, and backlog. Specifically,

[0293] Synchronization delay: The time it takes for monitoring data to be synchronized from the source database to the target database.

[0294] Success rate: Monitor the success rate of data synchronization and promptly identify failed synchronization tasks.

[0295] Backlog: Monitors the number of backlogged messages in the message queue to ensure the system does not crash due to excessive backlog.

[0296] Other metrics: Monitor 50+ other relevant metrics, such as throughput, error rate, etc.

[0297] Real-time monitoring of system status enables timely detection and resolution of problems. A dashboard displays key indicators for a clear understanding of system operation. Thresholds can be set to automatically issue alerts when indicators exceed normal ranges, minimizing system downtime.

[0298] Audit trail: All operation records are traceable and support multi-dimensional queries by time / table / type. Specifically,

[0299] Operation Log: Records all operations related to data synchronization, including configuration changes, synchronization task execution, etc.

[0300] Multidimensional query: Supports querying operation records by multiple dimensions such as time, table, and type.

[0301] Traceability: Ensures that all operations are traceable, facilitating problem investigation and accountability.

[0302] Provides audit evidence of operations, quickly locates problems, reduces troubleshooting time, prevents unauthorized operations, and improves system security.

[0303] By introducing configuration and auditing modules, the heterogeneous database synchronization system based on triggers and message queues not only improves the system's flexibility and maintainability but also enhances its security and reliability. The addition of these modules makes the system more suitable for enterprise-level applications, capable of meeting complex business needs and compliance requirements.

[0304] It should be understood that the method steps in the embodiments of the present invention can be implemented or carried out by computer hardware, a combination of hardware and software, or by computer instructions stored in a non-transitory computer-readable storage medium. The method can use standard programming techniques. Each program can be implemented in a high-level procedural or object-oriented programming language to communicate with the computer system. However, if necessary, the program can be implemented in assembly or machine language. In any case, the language can be a compiled or interpreted language. Furthermore, for this purpose, the program can run on a programmed application-specific integrated circuit (ASIC).

[0305] Furthermore, the procedures described herein may be performed in any suitable order unless otherwise indicated herein or otherwise clearly contradicted by the context. The procedures described herein (or variations and / or combinations thereof) may be executed under the control of one or more computer systems configured with executable instructions, and may be implemented by hardware or a combination thereof as code (e.g., executable instructions, one or more computer programs, or one or more applications) that commonly executes on one or more processors. The computer program comprises a plurality of instructions executable by one or more processors.

[0306] Furthermore, the method can be implemented operatively connected to any suitable type of computing platform, including but not limited to personal computers, minicomputers, mainframes, workstations, networked or distributed computing environments, standalone or integrated computer platforms, or communicating with charged particle tools or other imaging devices, etc. Aspects of the invention can be implemented as machine-readable code stored on a non-transitory storage medium or device, whether removable or integrated into a computing platform, such as a hard disk, optical read and / or write storage medium, RAM, ROM, etc., such that it is readable by a programmable computer, and when the storage medium or device is read by the computer, it can be used to configure and operate the computer to perform the processes described herein. Furthermore, the machine-readable code, or portions thereof, can be transmitted via wired or wireless networks. The invention described herein includes these and other different types of non-transitory computer-readable storage media when such media comprises instructions or programs that implement the steps described above in conjunction with a microprocessor or other data processor. When programmed according to the methods and techniques described in the invention, the invention may also include the computer itself.

[0307] A computer program can be applied to input data to perform the functions described herein, thereby transforming the input data to generate output data stored in non-volatile memory. The output information can also be applied to one or more output devices, such as a display. In a preferred embodiment of the invention, the transformed data represents physical and tangible objects, including specific visual depictions of physical and tangible objects generated on the display.

[0308] The above description is merely a preferred embodiment of the present invention. The present invention is not limited to the above-described embodiments. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention, as long as they achieve the technical effects of the present invention by the same means, should be included within the scope of protection of the present invention. Within the scope of protection of the present invention, the technical solutions and / or implementation methods can have various modifications and variations.< / t>

Claims

1. A method for heterogeneous database synchronization based on triggers and message queues, characterized in that, The method comprises the following steps: Creating a trigger function in the source database and binding the trigger function to the target business table to capture the change operation of the target business table in real time, and automatically triggering ORM model remapping if a structure change of the target business table is detected; Integrating the automatic mapping function of SQLAlchemy, dynamically generating an ORM model class identifier through source data analysis, and using the ORM model to query related data and extract related fields to generate a JSON message containing an idempotent key; Sending the JSON message containing the idempotent key to a message queue, and a consumer obtaining the JSON message containing the idempotent key from the message queue and screening target data from the JSON message containing the idempotent key; Performing conflict detection on the target data, generating final data based on a preset configuration strategy, and applying the final data to a target database to realize data synchronization; If a structure change of the target business table is detected, the ORM model is automatically remapped, which comprises the following steps: When any write operation of the target business table is detected, the trigger automatically executes, writes change information into a temporary synchronization table, and realizes decoupling of business operation and synchronization logic; The temporary synchronization table is used as a buffer layer to initialize the source database connection and the ORM model automatic mapping, and the Base.prepare function is re-executed to automatically update the mapping; The trigger automatically writes the structure change type, change primary key information, change version information, change processing state and change time of the target business table into the temporary synchronization table, and the change processing state comprises a to-be-processed state, a synchronized state and a processing failure state; The temporary synchronization table is scanned at regular intervals, and change information in the to-be-processed state of the temporary synchronization table is encapsulated into a JSON message containing an idempotent key; The fields of the JSON message containing the idempotent key in the temporary synchronization table are directly extracted by using the ORM model, and the class identifier is automatically parsed and generated and automatically mapped. Further comprising:

2. The trigger and message queue based heterogeneous database synchronization method of claim 1, wherein, Judging whether the final data is successfully applied to the target database; If the final data is successfully applied to the target database, recording the idempotent key of the target database to a Redis set and updating the offset of the message queue; If the final data is not successfully applied to the target database, writing the final data into a compensation queue, entering a timing retry link, and returning to the message queue. Further comprising:

3. The trigger and message queue based heterogeneous database synchronization method of claim 1, wherein, Defining a ConflictDetector interface of the ORM model, including detect() detection and resolve() solution methods; Defining that the ORM model supports custom extension, and realizing any conflict resolution logic through a plug-in mode; Defining that the ORM model supports dynamic creation of version priority, timestamp priority, node priority and custom function strategies. The conflict detection on the target data, the generation of final data based on a preset configuration strategy, and the application of the final data to a target database comprise:

4. The trigger and message queue based heterogeneous database synchronization method of claim 1, wherein, ​ ​ detecting whether the target data and the source data are in conflict; if the target data and the source data are in conflict, entering a conflict resolution link, returning final data after resolving the conflict, and applying the final data to the target database; if the target data and the source data are not in conflict, directly applying the target data as final data to the target database without synchronizing data.

5. The trigger and message queue based heterogeneous database synchronization method of claim 4, wherein, the conflict resolution link includes: acquiring version numbers of the target data and the source data respectively, and judging the priority of the version numbers of the target data and the source data; if the version number of the source data is higher than the version number of the target data, determining that the target data and the source data are in conflict; keeping the source data with the higher version number as final data, updating the version number of the final data and recording the update time, returning the final data after resolving the conflict, and applying the final data to the target database.

6. The trigger and message queue based heterogeneous database synchronization method of claim 2, wherein, if the final data is not successfully applied to the target database, writing the final data into a compensation queue, entering a timed retry link, and returning the message queue, which includes: if the final data is not successfully applied to the target database, sending a data synchronization failure message and putting the final data into the compensation queue; entering the timed retry link and returning the message queue, using an exponential backoff strategy to retry applying the final data to the target database; if the number of retries exceeds a preset maximum number of retries, recording a dead letter log and issuing a manual intervention instruction.

7. A trigger and message queue based heterogeneous database synchronization system, applied to the trigger and message queue based heterogeneous database synchronization method of any one of claims 1 to 6, characterized in that, the method includes: a data capture module, configured to create a trigger function in a source database and bind the trigger function to a target business table to capture change operations of the target business table in real time, and automatically trigger ORM model remapping when a structure change of the target business table is detected; a message packaging module, configured to integrate SQLAlchemy automatic mapping function, dynamically generate an ORM model class identifier through source data analysis, and use the ORM model to query related data and extract related fields to generate a JSON message containing an idempotent key; a message pushing module, configured to send the JSON message containing the idempotent key to a message queue, and configured to filter target data from the JSON message containing the idempotent key by a consumer from the message queue; a consumption adaptation module, configured to perform conflict detection on the target data, generate final data based on a preset configuration strategy, and apply the final data to a target database to realize data synchronization.

8. The trigger and message queue based heterogeneous database synchronization system of claim 7, wherein, the method further includes a configuration and audit module, which is configured to configure a graphical visual interface of field mapping, synchronization strategy, and conflict rule, display index information of synchronization delay, success rate, and backlog volume on a real-time monitoring dashboard, and is further configured to audit and track all operation records and support multi-dimensional query according to time / table / type.

9. A computer apparatus comprising a memory and a processor, characterised in that, the processor implements the method of any one of claims 1-6 when executing a computer program stored in the memory. the processor implements the method of any one of claims 1-6 when executing a computer program stored in the memory.

Images