A general method for interface based on database interactive data transmission

Abstract

The embodiment of the present application provides a general interface method based on database interactive data transmission. Through basic functions such as triggers, timers, process packages, and data table design, the form of transformation of interface data from the interface to the main data is described. All content is presented in the form of basic attributes of the data, which can ensure that redundant data is not added or valid data is not reduced during the data interactive transmission between the first main library table to the first interface table data and the second interface table data to the second main library table, and the uniqueness and accuracy of the main library table data are maintained.

Description

Technical Field

[0001] The present application relates to the field of database interface development, and in particular to a general method for interfacing databases to interactively transmit data. Background Art

[0002] With the rapid development of new development languages ​​of Internet technology, information systems and intelligent development are developing faster. Whether in the professional development of information systems or in various expert systems generated by artificial intelligence technology, relational databases have made significant contributions. Although it has certain limitations, it realizes the functions of statistics and screening on the data relationship model and is still the most widely used database.

[0003] In the process of system development, most of the current application systems are customized according to business characteristics. With the advancement of technology, there are more ways to connect applications, such as ESB (Enterprise Service Bus), dblink (Database Link), webs service (network-based modular components), etc. However, according to analysis and big data research, the foundation of the business at the implementation layer is still data. Data is first used to establish the initial needs of the enterprise, and then it is converted into a logical data model, which is used to implement in the data structure in the database. Therefore, the basic data of data modeling is particularly important.

[0004] Therefore, ensuring the uniqueness and accuracy of received data and avoiding the increase in data model workload caused by basic data problems such as data duplication, repeated operations, time overlap, and data confusion have become issues that need to be addressed urgently. Summary of the invention

[0005] In order to solve the problem of data interface interactively transmitting data between different databases and converting it into valid basic data before building a data model for business data, so as to facilitate the business system to provide the basic data selected for subsequent creation of data models as valid and latest data that meets the transmission requirements, the present invention provides a general interface method based on database interactive data transmission, the purpose of which is to receive valid data and convert it into accurate basic data required for the business when interactively transmitting data between different databases, especially relational databases.

[0006] The main problems to be solved by the present invention are how to send data, how to receive data, how to effectively send and receive data, how to ensure the uniqueness and accuracy of the received data, and how to avoid various data problems such as increased data model workload caused by basic data problems such as data duplication, repeated operations, time overlap, and data confusion, so as to fundamentally eliminate other difficult-to-attribute data changes generated in subsequent databases, thereby reducing the development workload of programmers.

[0007] In order to solve the above problems, the present invention proposes a general interface method based on database interactive data transmission, which is generally applicable to relational databases. The technical solution adopted is:

[0008] At the database sending end, a first interface table is created through the data items in the first main library table, and at the database receiving end, a second main library table is created through the database fields of the second interface table;

[0009] The first interface table and the second interface table both include data tables and are connected through the data tables. The data tables are created in the process of creating the first interface table and the second interface table. The data items included in the data tables in the first interface table and the second interface table are consistent.

[0010] The first main library table establishes a sequence number and a read-write field, and then writes them into the first interface table through a trigger and a timer. The data table corresponding to the first interface table includes the sequence number and the read-write field established by the first main library table;

[0011] Establishing serial numbers and read-write rules in the first interface table for reading by the second interface table;

[0012] A serial number and read-write rules are established in the second interface table to complete the writing of peer data and the reading of the second main library table. A process package is created based on the established serial number and read-write rules. The data item requirements of the second main library table are written into the process package. The process package is then called through a timer. The process package updates, deletes, or inserts the data table corresponding to the second interface table based on the data item requirements, which is used to conditionally filter the data in the second main library table and change the data in the second interface table.

[0013] In a feasible implementation, in the process of creating the first interface table through data items in the first main library table at the database sending end, the data items include several data items, serial numbers or unique identifiers, read-write items and timestamp data items of the first main library table; the first interface table is filtered and extracted into the first interface table through several key fields of the first main library table as conditions.

[0014] In a feasible implementation, in the process of writing to the first interface table through a trigger and a timer, when the data item of the first main library table changes, the trigger is triggered and records the data from the first main library table to the first interface table, and the changed data item of the first main library table is written into the first interface table in an insert manner.

[0015] In a feasible implementation, the data item changes in the first main library table include inserting, deleting and updating data items.

[0016] In a feasible implementation, after the process package is called through a timer, the method further includes: the process package creates parameters for passing parameters as conditional variables, uses the key value of the second main library table and the logical relationship of the second main library table to filter out the data required by the second interface table, and changes, checks and improves the interface data items before the second interface table data is passed.

[0017] In a feasible implementation, the method for establishing the sequence number includes automatically filling in the sequence number through sysguid() of the database or establishing sequences for customization.

[0018] In a feasible implementation, the timer calls the process package once every 5 minutes.

[0019] In a feasible implementation, the read-write rule includes a read-write tag field of the database receiving end.

[0020] The beneficial effects of the present invention are:

[0021] The present invention designs a set of standardized data interaction, data transmission, and data reading and writing modes based on a relational database, so that the sending and receiving of interface data form a set of general interface methods, and uses basic functions such as triggers, timers, and process packages based on databases to describe the form of transformation of interface data from interface to main data. All contents are presented in the form of basic attributes of data. A "data filtering" effect is formed between the first interface table and the second interface table. No redundant data is added or effective data is reduced during data interaction and transmission, and the uniqueness and accuracy of the main database table data are maintained. BRIEF DESCRIPTION OF THE DRAWINGS

[0022] Figure 1 It is a system block diagram of a general method for interfacing with a basic database to conduct data interactively, as shown in an exemplary embodiment of the present application;

[0023] Figure 2 It is a schematic diagram of a data table design of a general method for an interface of a basic database interactive data transmission method shown in an exemplary embodiment of the present application;

[0024] Figure 3This is a system workflow diagram of a general method for interfacing with a basic database to interactively transmit data, as shown in an exemplary embodiment of the present application;

[0025] Figure 4 is an intermediate component interface diagram of a purchase result shown in an exemplary embodiment of the present application;

[0026] Figure 5 is a diagram of an interface of a procurement plan intermediate component shown in an exemplary embodiment of the present application;

[0027] Figure 6 It is a partial information configuration diagram of the intermediate component according to the fields in the first interface table and the second interface table shown in an exemplary embodiment of the present application. DETAILED DESCRIPTION

[0028] To facilitate the technical solution of the application, some concepts involved in the application are first explained below.

[0029] The read-write field is a serial number (unique identification code) or primary key, read flag, time mark; the serial number only increases but never decreases, and serves as a unique identification code for transmitting a piece of data; the main library table includes the first main library table and the second main library table, the interface table includes the first interface table and the second interface table, and the database sending end and the database receiving end each correspond to a timer; in the process of writing the read-write field to the interface table, the trigger or timer plays the role of the check executor. When the main library table data is updated, inserted or deleted, the event is triggered, and the data change of the main library table is notified to the interface table for timely adjustment and inspection. The opposite system can make corresponding adjustments in time. Feedback and processing; the reading and writing rules in the interface table are related to the database receiving end. The interface table data is read and the read flag is marked to ensure that the data is no longer written into the peer interface table; the peer data refers to the system of the other party to whom data is to be transferred through the interface; the process package is essentially a callable program that specifically describes how the data table is executed. The process package can contain parameters or conditionally execute update, delete or insert operations on the table; the timer is set when it is written, and the process package is called regularly; the function of the intermediate component is to transfer data between interface tables of different systems, such as MQ (Message Queue), ESB, websphere, etc.

[0030] The exemplary embodiments will be described in detail below, and examples thereof are shown in the accompanying drawings. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with the present application. Instead, they are only examples of devices and methods consistent with some aspects of the present application as detailed in the appended claims.

[0031] Example 1, see Figure 1, the following is the process of data transfer between the first main library table and the first interface table:

[0032] Database sender:

[0033] The first main database table: t_noticeid, t_noticeinfo, t_buildreport, t_reportinfo, t_build, user;

[0034] Trigger: TR_T_ZHONGBIAO_INTERFACE;

[0035] Timer: job number 111;

[0036] The first interface table: t_zhongbiao_interface;

[0037] Working process:

[0038] First, the trigger program is executed because of the row update of the first main database table. The specific code is as follows:

[0039]

[0040]

[0041] The required data items are screened and extracted from different main database tables using the corresponding relationship between the primary keys noticeid, reportid, and tbuild into the t_zhongbiao_interface table. The first interface table contains the extracted data items and the additional data fields for inserting changes, such as Figure 2 As shown, in t_zhongbiao_interface, the corresponding master data items, serial number ZHONGBIAOID, timestamp CRT_DATE, different system read / write flags (taking JHPT_NEFLAG of ERP system as an example), error information, etc. are shown. Among them, the serial number is automatically filled in each row inserted in sysguid() mode to ensure the uniqueness of the row; the read / write flag is set to 3 flags: 0 unread, 1 read, 2 error read; the timer 111 auxiliary trigger is used to check the fields of the unread data table t_zhongbiao_interface row in the initialized state and fill in the fields, and automatically executes at intervals of SYSDATE+60 / 1440, and executes in the what value of the timer to check the condition code

[0042]

[0043] Check the interface table rows that are not read or have read or written errors, check the fields and field filling, and ensure that the interface table data is valid. The partial check code passed to the ERP result table is intercepted as follows:

[0044] ------The following code checks the interface data items of suppliers and agents in the interface table and fills in the missing data items

[0045]

[0046]

[0047] At this point, the first interface table meets the conditions for transmission and re-checking, and the timer performs checks regularly to ensure the validity of the data transmission of the first interface table.

[0048] Intermediate component: Data table connection is carried out through the interface transmission platform ESB, and IBM Websphere MQ queue is used as the intermediate component. The intermediate component interface of the procurement result is as follows Figure 4 As shown; the interface of the procurement plan intermediate component is as follows Figure 5 As shown; the intermediate component configures the diagram according to some information of the fields in the first interface table and the second interface table, as shown Figure 6 shown.

[0049] The following is the data transmission process between the second interface table and the second main database table, taking the purchase plan reception as an example:

[0050] Database receiving end:

[0051] The second interface table: T_REPORTINFO_INTERFACE;

[0052] Timer: job number 92;

[0053] Process package: SP_GET_tongyi_PLAN;

[0054] Second main database table: T_REPORTINFO;

[0055] Working process: According to the database field design rules of the second interface table T_REPORTINFO_INTERFACE, it includes various fields such as source data items, serial number rowid (automatically generated using database sequence), timestamp, and read-write mark bits. The data in the second interface table is all data. When the source data changes, an insert operation is performed for each row transmitted. Therefore, each row of data at the receiving end of the database is a new change data. Therefore, the data row of the new sequence that needs to be read is selected.

[0056] The timer 92 executes the process package regularly, and the process package writes the requirement conditions and executes the action of operating the second main library table.

[0057] The what value of timer 92 calls the process package, which specifies the conditions for inserting into the second main library table. The condition codes for the procurement plan are as follows:

[0058]

[0059] According to the conditions, the data items of the second interface table of the required plan table are transferred to the second main library table T_REPORTINFO. The primary key value of the second main library table is reportid. The execution code is as follows:

[0060]

[0061]

[0062] So far, the timer has completed the effective transfer of data items from the second interface table to the second main library table according to the periodic execution of the conditional process package.

[0063] This application creates serial number, time, and database receiving end system read-write tag fields through the first interface table of the database sending end;

[0064] The second interface table of the database receiving end creates a sequence number, time, and writes a read mark field of the second main library table of the database receiving end;

[0065] Data is transferred between the main database table and the interface table. A general interface method is established during the data transfer process. The database sending end and the database receiving end correspond to a database server respectively. The data of the database sending end and the database receiving end are stored on the corresponding database servers respectively.

[0066] The database sending end creates a trigger, triggers the execution of the trigger by triggering the unique constraint field of the master database table or specifying a specific key field, and writes the master data information into the first interface table;

[0067] The database receiving end receives the data from the database sending end in the form of a sequence increment;

[0068] The database receiving end creates a process package, reads the data in the second interface table in sequence, creates conditions, and inserts or updates the data in the second main library table. The key field is the most important condition in data transmission. The data transmission from the second interface table to the second main library table is mainly completed through timers and process packages. The second interface table contains all the information to be transmitted. For example, the second interface table uses the serial number as the key field. The information in this table can be inserted repeatedly, that is, the second interface table will have several pieces of information containing key fields in the second main library table. Then, the timer and process package are used to select the data rows that have not been read according to the sequence and read-write mark of the second interface table. Combined with the keywords of the second main library table, the design requirements of the second main library table, the classification of the second main library table and other conditions, write them into the timer and process package to perform specific database logical operations, and then successfully pass the interface data to the second main library table. The second main library table uses the keyword as the primary key, thereby avoiding the increase of redundant data and the reduction of valid data. The specific rules are: if there is no data in the second main database table, execute insert; if there is data in the second main database table, check whether it needs to be updated; if there is data in the second main database table, check whether it needs to be deleted, and before deleting, check whether the main logical structure of the data meets the deletion conditions.

[0069] See also Figure 2 The data table is included in the interface table, created in steps 1 and 2, written in step 4, and selected as conditions to be written into the second main library table in step 5. The sequence number field is inserted into the interface table in ascending order and marked; the time field marks the time of reading; the read mark field marks the flag mark that is read or written; other fields are business fields, which can also be flexibly used in trigger conditions.

[0070] Finally, see Figure 3 , the workflow of this embodiment is described.

[0071] Step 1: Create a first interface table at the database sending end;

[0072] Step 2: Create a second interface table at the database receiving end;

[0073] Step 3, making the data fields between the first interface table and the second interface table consistent;

[0074] Step 4: The first main database table establishes the serial number and read / write fields required by the general rule, and then writes them into the first interface table through triggers and timers;

[0075] Step five, create a timer at the database receiving end, create a process package in the second interface table according to the serial number and read and write rules of the interface data, call the process package through the timer, and the process package performs table operations on the data table corresponding to the second interface table and writes the data into the second main library table.

[0076] The above steps 1 to 5 predefine the implementation method of the interface, which converts the actual business type into several logical data sets for fast and effective transmission between different systems. The business functions to be implemented by different systems are not the same, but some attributes of the entities are dependent on each other. According to this interface method, the entity, attribute name and contact name of the basic elements can be directly confirmed, and these different attributes can be progressively developed layer by layer through the designed data connection rules. The attribute set can be sent to other information systems, or the receiving end can receive information from other systems. Therefore, both systems can directly design the database logic table on this basis, and conditionally filter the data in their respective systems according to their own business nature, which can quickly realize the establishment of the main database data base table, indirectly shorten the software development and delivery cycle, and improve development efficiency.

[0077] It is easy to understand that those skilled in the art can combine, split, reorganize, etc. the embodiments of the present application based on the embodiments provided in the present application to obtain other embodiments, and these embodiments do not exceed the protection scope of the present application.

[0078] The above specific implementation methods further explain in detail the purpose, technical solutions and beneficial effects of the embodiments of the present application. It should be understood that the above are only specific implementation methods of the embodiments of the present application and are not used to limit the protection scope of the embodiments of the present application. Any modifications, equivalent substitutions, improvements, etc. made on the basis of the technical solutions of the embodiments of the present application should be included in the protection scope of the embodiments of the present application.

Claims

1. A general method for interface based on database interactive data transmission, characterized in that: The method comprises: At the database sending end, a first interface table is created through the data items in the first main library table, and at the database receiving end, a second main library table is created through the database fields of the second interface table; The first interface table and the second interface table both include data tables and are connected through the data tables. The data tables are created in the process of creating the first interface table and the second interface table. The data items included in the data tables in the first interface table and the second interface table are consistent. The first main library table establishes a sequence number and a read-write field, and then writes them into the first interface table through a trigger and a timer. The data table corresponding to the first interface table includes the sequence number and the read-write field established by the first main library table; Establishing serial numbers and read-write rules in the first interface table for reading by the second interface table; A serial number and read-write rules are established in the second interface table to complete the writing of peer data and the reading of the second main library table. A process package is created based on the established serial number and read-write rules. The data item requirements of the second main library table are written into the process package. The process package is then called through a timer. The process package updates, deletes, or inserts the data table corresponding to the second interface table based on the data item requirements, which is used to conditionally filter the data in the second main library table and change the data in the second interface table.

2. The general method for interface based on database interactive data transmission according to claim 1 is characterized in that: In the process of creating the first interface table through data items in the first main library table at the database sending end, the data items include several data items, serial numbers or unique identifiers, read-write items and timestamp data items of the first main library table; the first interface table is screened and extracted into the first interface table through several key fields of the first main library table as conditions.

3. The general method for interface based on database interactive data transmission according to claim 1 is characterized in that: In the process of writing the first interface table through triggers and timers, when the data items of the first main library table change, the trigger is triggered and records the data from the first main library table to the first interface table, and the changed data items of the first main library table are written into the first interface table in an insert manner.

4. The general method for interface based on database interactive data transmission according to claim 3 is characterized in that: The data item changes of the first main library table include inserting, deleting and updating data items.

5. The universal method for interface based on database interactive data transmission according to claim 1 is characterized in that: After calling the process package through the timer, the method also includes: the process package creates the parameters for passing parameters as conditional variables, uses the key value of the second main library table and the logical relationship of the second main library table to filter out the data required by the second interface table, and changes, checks and improves the interface data items before the second interface table data is passed.

6. The universal method for interface based on database interactive data transmission according to claim 1 is characterized in that: The method for establishing the sequence number includes automatically filling in the sequence number through the sysguid() of the database or establishing sequences for customization.

7. The universal method for interface based on database interactive data transmission according to claim 1 is characterized in that: The timer calls the process package every 5 minutes.

8. The universal method for interface based on database interactive data transmission according to claim 1 is characterized in that: The read-write rules include a read-write tag field at the database receiving end.

Images