Method for migrating application data definition catalog changes to the system level data definition catalog in a database

• US 5,613,111 A
• Filed: 06/07/1995
• Issued: 03/18/1997
• Est. Priority Date: 08/13/1993
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: PeopleSoft Inc (Oracle Corporation)
• Inventors: Malatesta, John A., Bergquist, Richard A.
• Primary Examiner(s): AMSBURY, WAYNE P
• Application Number:
  US08/484,946
• Time in Patent Office: 650 Days
• Field of Search: 395/600
• US Class Current: 1/1
• CPC Class Codes: G06F 16/258 Data format conversion from or to a database
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99952 Coherency, e.g. same view to multiple users

Abstract

The present invention provides the ability to alter data definitions in a Database Management System (DBMS). The present invention provides the ability to maintain DBMS and application catalogs. Alterations can be made to the an application catalog, and the changes propagated to the DBMS catalog based on the differences between like information in the application and DBMS catalogs as identified by the present invention. The present invention provides the ability to test for data loss. That is, the present invention examines the requested alteration and the data that will be affected by the alteration to determine whether or not the alteration will result in the loss of data. If loss of data is possible, alerts can be generated to determine whether or not to proceed with the alterations. The present invention provides two techniques to perform-the alterations depending on the ability of the DBMS to perform an alter on the original table without using an intermediate table (i.e., a native alter), or performing the alterations using an intermediate table for a DBMS with limited native alter capabilities.

Claims

1. A method of altering information in a computer system comprising the steps of:

defining a system definition, said system definition containing a system-level definition for said information;

defining an application definition, said application definition containing an application-level definition for said information and said system-level definition;

identifying an alteration to said application definition;

altering said application definition to obtain a modified system-level definition in said application definition;

comparing said modified system-level definition in said application definition to said system-level definition in said system definition to identify a plurality of alterations for said system definition;

performing said plurality of alterations on said system definition.

2. The method of claim 1 further including the steps of:

determining whether one of said plurality of alterations can result in a loss of said information;

avoiding said alteration when said loss of information can result and said loss is not desirable;

committing said alteration when said loss of information will not result; and

committing said alteration when said loss of information is allowable.

3. The method of claim 1 further comprising the steps of:

creating a name table to store an old field name, its corresponding new field name, and a change time;

creating an entry in said name change table when a rename operation is executed;

identifying said old field name in said name change table using said new field name; and

identifying said new name in said name change table using said old name.

4. The method of claim 3 wherein said step of identifying said new name comprises the steps of:

(a) finding in said name change table a rename of said old name to a new name value;

(b) repeating step (a) using said new name value as said old name when said new name value is not said new name; and

(c) repeating step (a) using said old name and said change time to find a new name value with said change time subsequent to said rename when said new name value is not said new name.

5. The method of claim 3 wherein said step of identifying said old name comprises the steps of:

(a) finding in said name change table a rename of said new name from an old name value;

(b) repeating step (a) using said old name value as said new name when said old name value is not said old name; and

(c) repeating step (a) using said new name and said change time to find an old name value with a change time previous to said rename when said old name value is not said old name.

6. A method of altering information in a database management system comprising the steps of:

creating a structural definition of said information in an application definition table;

creating a structural definition of said information in a system definition table;

storing said information in an original data table;

identifying an alteration to said structural definition in said application definition table;

altering said structural definition based on said alteration;

converting said information in said original data table based on said alteration.

7. The method of claim 6 wherein said step of converting further comprises the steps of:

converting said information in said original data table based on said alteration, and storing said converted information in temporary records in said original data table;

dropping said original records from said original data table; and

renaming said temporary data records as said original records.

8. The method of claim 6 wherein said step of converting further comprised the steps of:

creating based on said altered structural definition a temporary data table comprised of original fields and new fields;

copying said information to a first set of records in said temporary data table;

converting said information of said first set of records of said temporary data table based on said alteration, and storing said converted information in a second set of records in said temporary data table;

dropping said original data table;

renaming said temporary data table as said original data table;

dropping said first set of records from said original data table; and

renaming said second set of records as said first set of records.

9. A method of identifying a history of alterations applied to structural information in a database management system comprising the steps of:

defining a structure definition, said structure definition including a plurality of field names;

storing said structure definition in a database catalog and an application catalog;

creating a name change table to store an old field name, its corresponding new field name, and a change time;

changing one of said field names in said structure definition; and

creating an entry in said name change table, said entry including the old and new names of said one of said field names and the time of the name change.

10. The method of claim 9 further comprising the steps of:

identifying said old name in said name change table using said new name; and

identifying said new name in said name change table using said old name.

11. The method of claim 10 wherein said step of identifying said new name comprises the steps of:

(a) finding in said name change table a rename of said old name to a new name value;

(b) repeating step (a) using said new name value as said old name when said new name value is not said new name; and

(c) repeating step (a) using said old name and said change time to find a new name value with said change time subsequent to said rename when said new name value is not said new name.

12. The method of claim 10 wherein said step of identifying said old name comprises the steps of:

(a) finding in said name change table a rename of said new name from an old name value;

(b) repeating step (a) using said old name value as said new name when said old name value is not said old name; and

(c) repeating step (a) using said new name and said change time to find an old name value with a change time previous to said rename when said old name value is not said old name.