DTS development records (sequence)

Http://zhuweisky.cnblogs.com/category/62488.html

Currently, I am in charge of developing a data transmission system (DTS stands for data transfer system). I plan to write a seriesArticleTo record the entire process of development, this record can be said to be real-time-I will record it whenever an important problem is encountered or an important problem is solved. Teacher Hou Jie said, "posting is the best memory." In addition to taking notes, this series of articles also hope to be helpful to later users.

Before developing any system, we need to figure out what the system is? What are the main responsibilities? These need to be obtained through requirement analysis. DTS is a tool software that involves almost no business, so its functional objectives are relatively simple. We all know that DTS is used to import and export data between two databases. The structure of the two tables involved in data transmission (source and target tables) may be completely different, however, the columns and columns between them may have ing relationships (some column ing may be complex, such as type conversion, column merging, column splitting, and conversion expressions ), DTS can transmit data from the source table to the target table based on the established ing relationship. I have summarized the following main functions of the DTs system:

(1) It is independent of the database type, or fully supports the transfer between heterogeneous databases. (If a new database type exists, you only need to add the corresponding adapter plug-in)
(2) provide a visual tool to establish (or modify) the matching ing between the source table structure and the target table structure (the ing can be saved and loaded)
(3) verify the integrity and correctness of the table structure ing relationship
(4) based on the correct and complete structure ing relationships, you can export data from the source table to the target table andIncrementalExport (pause/continue function, logging function)
(5) You can split a column in the source table into multiple columns in the target table based on the specified rules.
(6) You can merge multiple columns in the source table into one column in the target table based on the specified rules.
(7) map the source column to the target column after certain transformations (such as the result calculated by participating in an expression.
(8) If the source table is replaced with a temporary view, the above functions can still be implemented. (The temporary view is used to solve the problem that multiple source tables correspond to one target table)
(9) record the data failed to be exported and re-export the failed data based on these records.
(10) regularly run the data export task.

As far as I know, the difficulty of this system lies in the following aspects:
(1) independent from the database type is a challenge! Although different types of databases support standard SQL statements, there are huge differences between them. For example, each type of database has its own data type; different types of databases have different extensions. For the same task (such as paging), different implementations are available for different types of databases.
(2) incremental export is another difficulty. If the exported data changes (update or delete), because we cannot modify the data source to add a trigger, the system cannot receive these events, A policy is required to synchronize exported data.
(3) Validation of the merge expression for column merge is not simple. It also depends on the level to which your support for expressions is needed, such as addition, subtraction, multiplication, division, exponent, logarithm, and flute operations.
(4) Processing of source data items that do not meet the conversion rules. Different tasks may have different processing requirements for such source data items. The system must support users' selection of processing methods.

There are still many difficulties that may not be met, and there may still be many demands that have not been sorted in. These will be gradually mined or discovered during the process of building the system, and I will list them here.

In the following article, I will introduce the main structure of the DTs system.