Propagating tables while preserving cyclic foreign key relationships
The invention meeting the need identified above is the “Enhanced Database Propagation Program” or (EDPP). EDPP uses a cyclic member table that identifies a first source table in a cyclic relationship with a second source table. The first source table, identified in the cyclic member table, propagates first. EDPP replaces all foreign keys with “NULL” in the first target table. After the second source table in the subscription set has propagated to a second target table, EDPP updates first target table with the foreign keys from the first source table.
Latest IBM Patents:
- Integration of selector on confined phase change memory
- Method probe with high density electrodes, and a formation thereof
- Thermally activated retractable EMC protection
- Method to manufacture conductive anodic filament-resistant microvias
- Detecting and preventing distributed data exfiltration attacks
This application is a continuation of U.S. patent application Ser. No. 11/427,930 filed Jun. 30, 2006, status allowed.
FIELD OF THE INVENTIONThe present invention relates generally to database maintenance in a computer or digital processing system, and relates particularly to a method of propagating tables while preserving cyclic foreign key relationships.
BACKGROUND OF THE INVENTIONA database is any collection of information organized for rapid search and retrieval. A database stored in a computer-readable medium is commonly modeled as a collection of one or more tables. Each table, in turn, is modeled as a collection of one or more records (also commonly referred to as a “row”), and each record as a collection of one or more fields (also commonly referred to as a “column”). In a conventional table, all records comprise the same number and type of fields, and in the same order. A relational database consists of tables that are “related” to each other through common fields. The most common way to establish a relationship between two tables is to include one or more fields in each table that hold “key” information. A “primary key” field uniquely identifies a record, and commonly is just a number unrelated to other data in the record. A “foreign key” field is an identifier in a record that establishes a relationship with a primary key in another table. For example, employee records might have an “employee” table containing a “department_id” field that references data located in a “department” table's “dept_id” field. In this example, the dept_id field uniquely identifies each department, while the department_id field identifies the department in which an employee works. Thus, in this example, dept_id would be a primary key, and department_id would be the foreign key that establishes the relationship between the employee table and the department table. The integrity of the table relationship depends on the foreign key referencing a valid primary key. Most modern database management systems allow users to designate foreign key fields when tables are created, and subsequently reject operations that would result in an invalid foreign key reference. Thus, all foreign key values must have equivalent primary key values that already exist in the other table.
As is well known in the art, it is sometimes advantageous to replicate databases on other computers or servers, or even on the same computer or server. It is common, though, for a database to hold thousands of tables and millions of records, and replicating these databases can be a difficult and cumbersome task. Several software tools exist in the art to assist a database administrator with the task of replicating (also called “propagating”) databases among servers, including DpropR—a database utility developed by IBM. On the most basic level, DpropR extracts data from a first “source” database on a first server and pushes the data to a second “target” database on a second server. DpropR removes “foreign keys” from the second or target database. When supporting two-way propagation, where data is propagated in both directions between the two databases, DpropR also removes “foreign keys” from the first or source database. An improvement to DropR (see discussion of U.S. patent application Ser. No. 10/855,736 below) allows a database administrator to control the order of propagation to preserve foreign key integrity, and thereby eliminates the need to remove foreign keys.
In order to propagate databases between servers, DpropR or other database propagation utilities use “subscription sets” to determine which tables should be propagated, and to which servers. A subscription set generally comprises a list of tables that a database administrator or user treats as a single unit for purposes of database propagation. A database administrator specifies in advance what tables are included in a subscription set, and can create more than one subscription set if needed or desired.
Subscription sets are themselves typically stored in relational tables: a “subscription” table and a “member” table. A subscription table comprises records identifying a subscription set, the server having the source database to be copied, and the target database to which the source database should be copied. The subscription set may also contain other useful information, such as a value indicating the last time the source database was copied, and a value indicating a frequency for copying the source table.
A member table comprises records identifying a source table, the subscription set to which the source table belongs, and the name of the target table to which the source table should be copied. Each subscription set identified in the member table must have a corresponding record in the subscription table. Note, though, that a source table can appear in more than one subscription set.
U.S. patent application Ser. No. 10/855,736 (the '736 application) discloses a method and apparatus for propagating relational database tables that allow a database administrator to control the order of propagation, and thereby preserve foreign key integrity. The '736 application's improvement to DpropR or other database propagation utilities adds an additional field to each member table and subscription set called ORDER. The additional field enables an administrator or other user to assign a rank to each subscription set and to each table within a subscription set, and then use the rankings to determine the order in which to propagate database tables. The rankings ensure that tables with a primary key referenced by a foreign key are propagated before the table with the foreign key is propagated, avoiding an invalid foreign key reference.
One embodiment of the improvement disclosed in application Ser. No. 10/855,736 uses two programs CAPTURE and APPLY. CAPTURE monitors a database for changes and causes APPLY to propagate source tables of the subscription sets in the designated order. Something that U.S. patent application Ser. No. 10/855,736 explicitly does not address is the situation of “cyclic foreign keys.” Cyclic foreign keys occur when a field in TABLE1 references a field in TABLE2, which in turn references a field in TABLE1. Illustrated below as:
TABLE1→TABLE2→TABLE1
Of course, cyclic references can be more complex, involving more than two tables:
TABLE1→TABLE2→TABLE3→TABLE1
An example of TABLE1 and TABLE2 with a cyclic relation are shown in
Simply propagating tables in a specific order does not work with cyclic references. One of the tables has to propagate first, which inevitably results in an invalid foreign key reference unless the foreign references are manually removed from the target table to be propagated. A need exists for a system and method to automatically propagate tables containing cyclic references, without permanently removing any foreign keys.
These and other objects of the invention will be apparent to those skilled in the art from the following detailed description of a preferred embodiment of the invention.
SUMMARY OF THE INVENTIONThe invention meeting the need identified above is the “Enhanced Database Propagation Program” or (EDPP). EDPP uses a “cyclic member table” that identifies a first source table with cyclic foreign key relationships with a second source table. The first source table, identified in the cyclic member table, propagates first. EDPP replaces all foreign keys with “NULL” in the first target table. After the second source table in the subscription set has propagated to a second target table, EDPP updates the first target table with the foreign keys from the first source table.
The EDPP also works when there are more than two interrelated tables. All tables, save the last one, must be copied to target tables with “NULL” replacing the foreign keys. Then, EDPP copies the last table to a last target table, which includes the foreign keys. After the last table is copied, the preceding tables are updated with the original foreign keys. The procedure for more than two tables is usually done in a particular sequence, where the tables are copied in a particular order, and then updated and the reverse order.
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:
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
EDPP 400 typically is stored in a memory, represented schematically as memory 420 in
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.
Claims
1. A method for propagating a plurality of database tables with cyclic foreign keys without permanently removing the cyclic foreign keys, the method comprising:
- a computer identifying a first source database table and a second source database table with a cyclic foreign key relationship by iterating through a plurality of records listed in a cyclic member table that lists the plurality of database tables with cyclic foreign keys;
- the computer creating a first target database table;
- the computer copying said first source database table to said first target database table, the copying comprising replacing all cyclic foreign keys referencing said second source database table with “NULL”;
- the computer iterating through the plurality of records of said cyclic member table in reverse order; and
- the computer updating said first target database table with the cyclic foreign keys referencing the second source database table from the first source database table.
2. The method of claim 1, further comprising the computer copying said second source database table to a second target database table.
3. A computer system for propagating a plurality of database tables with cyclic foreign keys without permanently removing the cyclic foreign keys, the computer system comprising:
- one or more processors, and one or more computer readable memories;
- computer program instructions, stored on at least one of the one or more computer readable memories for execution by at least one of the one or more processors, to identify a first source database table and a second source database table with a cyclic foreign key relationship by iterating through a plurality of records listed in a cyclic member table that lists the plurality of database tables with cyclic foreign keys;
- computer program instructions, stored on at least one of the one or more computer readable memories for execution by at least one of the one or more processors, to create a first target database table;
- computer program instructions, stored on at least one of the one or more computer readable memories for execution by at least one of the one or more processors, to copy said first source database table to said first target database table, wherein the computer program instructions to copy said first source database table to said first target database table comprise computer program instructions to replace all cyclic foreign keys referencing said second source database table with “NULL”;
- computer program instructions, stored on at least one of the one or more computer readable memories for execution by at least one of the one or more processors, to iterate through the plurality of records of said cyclic member table in reverse order; and
- computer program instructions, stored on at least one of the one or more computer readable memories for execution by at least one of the one or more processors, to update said first target database table with the cyclic foreign keys referencing the second source database table from the first source database table.
4. The computer system of claim 3, further comprising:
- computer program instructions, stored on at least one of the one or more computer readable memories for execution by at least one of the one or more processors, to copy said second source database table to a second target database table.
5. A computer program product for propagating a plurality of database tables with cyclic foreign keys without permanently removing the cyclic foreign keys, the computer program product comprising:
- one or more computer readable memories;
- computer program instructions, stored on at least one of the one or more computer readable memories, to identify a first source database table and a second source database table with a cyclic foreign key relationship by iterating through a plurality of records listed in a cyclic member table that lists the plurality of database tables with cyclic foreign keys;
- computer program instructions, stored on at least one of the one or more computer readable memories, to create a first target database table;
- computer program instructions, stored on at least one of the one or more computer readable memories, to copy said first source database table to said first target database table, wherein the computer program instructions to copy said first source database table to said first target database table comprise computer program instructions to replace all cyclic foreign keys referencing said second source database table with “NULL”;
- computer program instructions, stored on at least one of the one or more computer readable memories, to iterate through the plurality of records of said cyclic member table in reverse order; and
- computer program instructions, stored on at least one of the one or more computer readable memories, to update said first target database table with the cyclic foreign keys referencing the second source database table from the first source database table.
6. The computer program product of claim 5, further comprising:
- computer program instructions, stored on at least one of the one or more computer readable memories, to copy said second source database table to a second target database table.
4918593 | April 17, 1990 | Huber |
5553218 | September 3, 1996 | Li et al. |
5819254 | October 6, 1998 | Kawai |
5956725 | September 21, 1999 | Burroughs et al. |
6138111 | October 24, 2000 | Krishna |
6295539 | September 25, 2001 | Isip, Jr. |
6542883 | April 1, 2003 | Salo |
6584476 | June 24, 2003 | Chatterjee et al. |
6636870 | October 21, 2003 | Roccaforte |
6658540 | December 2, 2003 | Sicola et al. |
6799190 | September 28, 2004 | Boothby |
6925477 | August 2, 2005 | Champagne et al. |
7007003 | February 28, 2006 | Rybicki |
7031956 | April 18, 2006 | Lee et al. |
7272591 | September 18, 2007 | Ghazal et al. |
7346627 | March 18, 2008 | Ramanathan et al. |
7383273 | June 3, 2008 | Wang et al. |
7412455 | August 12, 2008 | Dillon |
7487168 | February 3, 2009 | Rys et al. |
7555493 | June 30, 2009 | Khayter et al. |
7739223 | June 15, 2010 | Vaschillo et al. |
20040220956 | November 4, 2004 | Dillon |
20050278277 | December 15, 2005 | Forlenza et al. |
20080005183 | January 3, 2008 | Bostick et al. |
- Office Action regarding U.S. Appl. No. 10/855,736, dated Jan. 8, 2007, 10 pages.
- Final Office Action regarding U.S. Appl. No. 10/855,736, dated Jun. 29, 2007, 13 pages.
- Office Action regarding U.S. Appl. No. 10/855,736, dated Dec. 12, 2007, 13 pages.
- Final Office Action regarding U.S. Appl. No. 10/855,736, dated Aug. 7, 2008, 19 pages.
- Appeal Brief regarding U.S. Appl. No. 10/855,736, dated Nov. 5, 2008, 24 pages.
- Office Action regarding U.S. Appl. No. 10/855,736, dated Feb. 5, 2009, 10 pages.
- Office Action regarding U.S. Appl. No. 11/427,930, dated Apr. 2, 2008, 16 pages.
- Response to Office Action regarding U.S. Appl. No. 11/427,930, dated Jul. 2, 2008, 9 pages.
- Final Office Action regarding U.S. Appl. No. 11/427,930, dated Oct. 21, 2008, 8 pages.
- Appeal Brief regarding U.S. Appl. No. 11/427,930, dated Mar. 16, 2009, 27 pages.
- Examiner's Answer regarding U.S. Appl. No. 11/427,930, dated Jun. 1, 2009, 17 pages.
- Reply Brief regarding U.S. Appl. No. 11/427,930, dated Jul. 21, 2009, 17 pages.
- Decision on Appeal regarding U.S. Appl. No. 11/427,930, dated May 18, 2012, 8 pages.
- Notice of Allowance regarding U.S. Appl. No. 11/427,930, dated May 31, 2012, 7 pages.
Type: Grant
Filed: Sep 7, 2012
Date of Patent: Jun 17, 2014
Patent Publication Number: 20120330890
Assignee: International Business Machines Corporation (Armonk, NY)
Inventors: James E. Bostick (Cedar Park, TX), Randolph M. Forlenza (Austin, TX), John P. Kaemmerer (Pflugerville, TX), Raghuraman Kalyanaraman (Austin, TX)
Primary Examiner: Jay Morrison
Assistant Examiner: Dangelino Gortayo
Application Number: 13/606,830
International Classification: G06F 17/30 (20060101);