Method(s) for updating database object metadata

• US 7,809,763 B2
• Filed: 10/15/2004
• Issued: 10/05/2010
• Est. Priority Date: 10/15/2004
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Thiagarajan, Bhuvaneswari, Pae, Christine, Nori, Sarita Brahmandam
• Primary Examiner(s): Stevens; Robert
• Application Number:
  US10/966,250
• Publication Number:
  US 20060085465A1
• Time in Patent Office: 2,181 Days
• Field of Search: 707/101, 707/609, 707/802, 707/803, 717/120
• US Class Current: 707/802
• CPC Class Codes: G06F 16/213 with details for schema evolution support

Abstract

Two computers are respectively programmed to generate descriptions of database objects (such as tables) in a first database, and compare the generated descriptions to corresponding schema objects in a second database. Objects in the second database are changed to conform to the first database if differences are found during comparison. The just-described differences in objects may arise due to changes being made to a software program that uses data contained in the objects.

Claims

1. A method of propagating a plurality of changes made to application software that uses a first relational database, the method comprising:

a first computer receiving an identification of a first table in the first relational database;

wherein first data in the first table in the first relational database is used in the application software;

the first computer checking if the first table is a primary object, by using a first data structure comprising a first plurality of predetermined types indicative of primary objects;

the first computer extracting first metadata describing the first table from a first data dictionary;

wherein the first data dictionary is comprised in the first relational database;

the first computer using the first metadata to prepare a first description of properties of the first table, the first description being expressed in a text based markup language;

the first computer checking the first relational database, to see if said first table has associated thereto a first secondary object of any type identified by a second data structure comprising a second plurality of predetermined types indicative of secondary objects;

in response to finding said first secondary object associated with said first table during said checking the first relational database, the first computer extracting from the first data dictionary second metadata describing the first secondary object in the first relational database, and the first computer preparing a second description of properties of the first secondary object;

a second computer receiving at least;

said first description of said first metadata, said second description of said second metadata, and the plurality of changes to the application software;

wherein at least one change in the plurality of changes is related to use of first data in said first table;

the second computer identifying a structural difference between said first table in said first relational database and a second table in a second relational database in said second computer, by comparing at least said first metadata of said first table to corresponding second metadata of said second table in a second data dictionary of the second relational-database;

wherein the second data in the second table is used by the second computer in executing a copy of the application software;

the second computer structurally changing the second table depending at least partially on an outcome of said comparing, to generate a changed second table; and

the second computer executing a changed version of the application software, obtained by the second computer changing said copy by applying said plurality of changes, to use the changed second table.

2. The method of claim 1 wherein:

said second plurality of predetermined types comprises an index type indicative of a database index, and said first computer checks, during said checking the first relational database, if said first table has associated thereto said first secondary object of said index type.

3. The method of claim 1 wherein:

said second plurality of predetermined types comprises a trigger type indicative of a database trigger, and said first computer checks, during said checking the first relational database, if said first table has associated thereto said first secondary object of said trigger type.

4. The method of claim 1 wherein:

said second plurality of predetermined types comprises a policy type indicative of a database policy, and said first computer checks, during said checking the first relational database, if said first table has associated thereto said first secondary object of said policy type.

5. The method of claim 1 wherein:

said second plurality of predetermined types comprises a constraint type indicative of a database constraint, and said first computer checks, during said checking the first relational database, if said first table has associated thereto said first secondary object of said constraint type.

6. The method of claim 1 wherein:

said second plurality of predetermined types comprises a sequence type indicative of a database sequence, and said first computer checks, during said checking the first relational database, if said first table has associated thereto said first secondary object of said sequence type.

7. The method of claim 1 wherein:

the first description comprises at least

(a) name;

(b) data type; and

(c) default data value;

of each column in said first table.

8. The method of claim 1 further comprising:

the first computer accessing the first relational database therein using a relational database management system; and

the second computer accessing the second relational database therein using said relational database management system.

9. The method of claim 1 wherein:

the text based markup language comprises a plurality of tags.

10. The method of claim 1 wherein:

the text based markup language is extensible markup language (XML).

11. The method of claim 1 further comprising:

said first computer receiving an indication of a view in said first relational database;

said first computer preparing an additional description describing said view;

said second computer further comparing metadata of a version of said view in the second relational database to said additional description; and

the second computer altering said version of said view in the second relational database based on said additional description, depending on an outcome of said further comparing.

12. The method of claim 11 wherein:

the view is a materialized view.

13. The method of claim 1 further comprising:

the first computer preparing a first hash code using a portion of said first metadata other than an identifier of the first table;

the first computer storing the first hash code and said first description in a file; and

the second computer preparing a second hash code from said second metadata;

wherein the second computer performs said comparing by at least checking for a match between the first hash code and the second hash code.

14. The method of claim 13 wherein:

the first computer prepares a plurality of additional hash codes using said first metadata;

the first computer stores the additional hash codes in said file; and

the second computer uses the additional hash codes to find a change to be made only if the first hash code and the second hash code do not match.

15. The method of claim 1 wherein said first table comprises a plurality of columns, the method further comprising:

the first computer preparing a first individual hash code for each column in said first table using said first metadata;

the first computer further preparing a first base hash code for the first table using at least a name of said first table, a plurality of properties of said first table, and a plurality of first individual hash codes corresponding to said plurality of columns of the first table;

the first computer storing the first base hash code and the first individual hash codes in a file;

the second computer preparing a second individual hash code for each column in said second table using said second metadata;

the second computer further preparing a second base hash code for the second table using at least a corresponding name said second metadata, a corresponding plurality of properties in said second metadata, and a plurality of second individual hash codes for the columns in the second table;

wherein the second computer performs said comparing by at least checking for a match between the first base hash code and the second base hash code; and

if the first base hash code does not match the second base hash code, the second computer further checks for matches between the plurality of first individual hash codes and a corresponding plurality of second individual hash codes.

16. The method of claim 1 wherein:

the second computer keeps the second table in the second database unchanged or alternatively changes the second table, depending on whether a difference found by said comparing is only due to customization or not.

17. The method of claim 16 wherein said customization comprises addition of a new column to said second table in the second database.

18. The method of claim 1 wherein said changed application software comprises a fix of a defect in the application software.

19. The method of claim 1 wherein the second computer changes the second relational database structurally by executing a command expressed in a data definition language.

20. The method of claim 1 wherein the first table is identified by input to said first computer, as being changed.

21. The method of claim 1 wherein the first computer extracting comprises querying the first relational database by using SQL.