BRIEF DESCRIPTION OF DRAWINGS
The novel features believed characteristic of the invention are set forth in the appended claims. The invention itself, however, as well as a preferred mode of use, further objectives and advantages thereof, will be understood best by reference to the following detailed description of an illustrative embodiment when read in conjunction with the accompanying drawings, wherein:
FIG. 1 is an exemplary table with foreign keys;
FIG. 2 is an exemplary table with foreign keys;
FIG. 3 is an exemplary network;
FIG. 4 describes programs and files in a memory on a computer;
FIG. 5 is a flowchart of an Apply program;
FIG. 6 is a flowchart of an Update program; and
FIG. 7A-7E is an example propagation of tables with a cyclic relationship.
DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENT
The principles of the present invention are applicable to a variety of computer hardware and software configurations. The term “computer hardware” or “hardware,” as used herein, refers to any machine or apparatus that is capable of accepting, performing logic operations on, storing, or displaying data, and includes without limitation processors and memory; the term “computer software” or “software,” refers to any set of instructions operable to cause computer hardware to perform an operation. A “computer,” as that term is used herein, includes without limitation any useful combination of hardware and software, and a “computer program” or “program” includes without limitation any software operable to cause computer hardware to accept, perform logic operations on, store, or display data. A computer program may, and often is, comprised of a plurality of smaller programming units, including without limitation subroutines, modules, functions, methods, and procedures. Thus, the functions of the present invention may be distributed among a plurality of computers and computer programs. The invention is described best, though, as a single computer program that configures and enables one or more general-purpose computers to implement the novel aspects of the invention. For illustrative purposes, the inventive computer program will be referred to as the “Enhanced Database Propagation Program” or (EDPP).
Additionally, the EDPP is described below with reference to an exemplary network of hardware devices, as depicted in FIG. 3. A “network” comprises any number of hardware devices coupled to and in communication with each other through a communications medium, such as the Internet. A “communications medium” includes without limitation any physical, optical, electromagnetic, or other medium through which hardware or software can transmit data. For descriptive purposes, exemplary network 100 has only a limited number of nodes, including workstation computer 105, workstation computer 110, server computer 115, and persistent storage 120. Network connection 125 comprises all hardware, software, and communications media necessary to enable communication between network nodes 105-120. Unless otherwise indicated in context below, all network nodes use publicly available protocols or messaging services to communicate with each other through network connection 125.
EDPP 400 typically is stored in a memory, represented schematically as memory 420 in FIG. 4. The term “memory,” as used herein, includes without limitation any volatile or persistent medium, such as an electrical circuit, magnetic disk, or optical disk, in which a computer can store data or software for any duration. A single memory may encompass and be distributed across a plurality of media. Further, EDPP 400 may reside in more than one memory distributed across different computers, servers, logical partitions or other hardware devices. The elements depicted in memory 420 may be located in or distributed across separate memories in any combination, and EDPP 400 may be adapted to identify, locate and access any of the elements and coordinate actions, if any, by the distributed elements. Thus, FIG. 4 is included merely as a descriptive expedient and does not necessarily reflect any particular physical embodiment of memory 420. As depicted in FIG. 4, though, memory 420 may include additional data and programs. Of particular importance to EDPP 400, memory 420 may include Resource Data 430 which includes Source_Tables 440. Source_Tables 440 are database tables that are to be propagated. As with the prior art identified above, EDPP 400 uses subscription sets and member tables. By way of example, subscription sets are represented here as Subscription_Table 450. A special member table lists database tables with cyclic foreign keys, shown here as Cyclic_Member_Table 460. As with the prior art, Subscription_Table 450 and Cyclic_Member_Table 460 includes an “ORDER” field, indicating an order of propagation. Cyclic_Member_Table 460, however, does not include the last database table with cyclic foreign keys to be propagated. For example, if two tables have cyclic foreign keys, then only the first table to be propagated is included in Cyclic_Member_Table 460. If three tables have cyclic foreign keys, then only the first two tables to be propagated are included, and so forth. EDPP 400 has three component programs: Apply 500, Update 600 and Capture 700.
FIG. 5 shows a flowchart of Apply 500. As with the prior art identified above, Apply 500 starts (510) as a response to a detected change by Capture 700, or otherwise initiated by a database administrator manually or automatically by another program or process. Capture 700 is known in the art, and causes Apply 500 to propagate database tables in response to detected changes in the source database tables. Apply 500 scans the records from Subscription_Table 450 for Cyclic_Member_Table 460 (512). Apply 500 determines if there are any tables with cyclic foreign key references (514). If no tables are listed with cyclic foreign key references, tables propagate normally under the prior art (524). If tables with cyclic foreign references are listed, Apply 500 iterates through the records listed in Cyclic_Member_Table 460 (516). In addition to records indicating tables with cyclic foreign keys, Cyclic_Member_Table 460 may specify a particular order for propagating the list of tables. Apply 500 creates a target database table for the next table to propagate (518). Apply 500 then copies the source database table to the target database table, replacing every foreign key reference with a “NULL” statement (520). Only a NULL statement can be used because any other word will cause a check for the foreign key, and such a check for a foreign key will cause a violation error to be thrown. Apply 500 determines if there are more tables listed in Cyclic_Member_Table 460 (522), and if so, repeats steps 516-520. Once all tables listed in Cyclic_Member_Table 460 are copied, the other tables in Subscription_Table 450 propagate normally under the prior art (524). Once all tables in Subscription_Table 450 propagate, Apply 500 determines if cyclic foreign references were propagated (526). If tables with cyclic foreign references were propagated, Apply 500 initiates Update 600 (528), otherwise, Apply 500 stops (530).
FIG. 6 shows a flowchart of Update 600 start when initiated by Apply 500 (610). Update 600 iterates through the records of tables listed in Cyclic_Member_Table 460 in reverse order (612). Update 600 copies all the foreign key references from the source table to the target table (614). Since all tables referenced by the foreign key are populated, there will not be a foreign key violation error. Update 600 determines if there are more tables listed in Cyclic_Member_Table 460 (616), and if so, repeats steps 612-614. Once all tables listed in Cyclic_Member_Table 460 are copied, Update 600 stops (618).
FIG. 7A-7E shows the propagation of the source tables with cyclic foreign key references shown in FIG. 1 and FIG. 2 using EDPP 400. Target_Table_1 is created in FIG. 7A.
FIG. 7B shows Target_Table_1 populated with data from TABLE_1, but the foreign key references in column T_T1_COL3 have been replaced with “NULL.” Target_Table_2 is created in FIG. 7C. FIG. 7D shows Target_Table_2 populated with data from TABLE_2, including the foreign key references in column T_T2_COL3. There will not be a foreign key violation error after the propagation of Target_Table_2, because Target_Table_1 has been created and populated with data, although some records are merely placeholders. FIG. 7E shows Target_Table_1 updated with the original foreign key references, completing the propagation process.
A preferred form of the invention has been shown in the drawings and described above, but variations in the preferred form will be apparent to those skilled in the art. The preceding description is for illustration purposes only, and the invention should not be construed as limited to the specific form shown and described. The scope of the invention should be limited only by the language of the following claims.
Patent Application
20080005183
