Detecting and applying database schema changes to reports

• US 8,204,848 B2
• Filed: 11/17/2009
• Issued: 06/19/2012
• Est. Priority Date: 11/17/2009
• Status: Active Grant

• Current Assignee: Business Objects Incorporated (SAP SE)
• Original Assignee: Business Objects Incorporated (SAP SE)
• Inventors: Sinha, Anand
• Primary Examiner(s): Leroux, Etienne
• Application Number:
  US12/619,698
• Publication Number:
  US 20110119288A1
• Time in Patent Office: 945 Days
• Field of Search: 707/2, 707/102, 707/204, 707/600
• US Class Current: 707/600
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 16/288 Entity relationship models

Abstract

A system and method to detect database schema changes are described. In various embodiments, a method of the embodiments detects database schema changes via triggers. In various embodiments, the changes are detected by comparing database schemas and determining modifications to database tables and database columns thereof. In various embodiments, the detected changes are applied to a report by generating a parse tree relevant for the modifications to database tables and columns; and traversing the tree to produce a statement to change the report to be relevant for the modifications to database tables and columns.

Claims

1. A machine-readable storage device having machine readable instructions tangibly stored thereon which when executed by the machine, cause the machine to perform a method to apply database schema changes to a report, the method comprising:

detecting a change in a database schema;

receiving one or more changes between a first database schema relevant to a first database state and a second database schema relevant to a second database state; and

applying the one or more changes to the report by;

generating a parse tree having nodes and leaves relevant to the first database state, wherein the parse tree comprises syntactic structure of a first statement reflecting the first database state;

modifying the generated parse tree to include the one or more changes between the first database schema and the second database schema by modifying the nodes and the leaves to reflect the second database state; and

traversing the modified parse tree to generate a second statement to modify the report to reflect the second database schema relevant to the second database state.

2. The machine-readable storage device of claim 1, wherein detecting the change in the database schema comprises:

receiving the first database state;

receiving the second database state; and

comparing the first database state to the second database state.

3. The machine-readable storage device of claim 2, wherein receiving the first database state comprises:

receiving one or more table names relevant to the first database state;

receiving one or more column names relevant to the first database state; and

receiving one or more data types, wherein the one or more data types are correspondingly relevant to the one or more column names.

4. The machine-readable storage device of claim 2, wherein the operations further comprise generating a query to receive the first database state at one or more time intervals.

5. The machine-readable storage device of claim 2, wherein receiving the second database state comprises:

receiving one or more table names relevant to the second database state;

receiving one or more column names relevant to the second database state; and

receiving one or more data types, wherein the one or more data types are correspondingly relevant to the one or more column names.

6. The machine-readable storage device of claim 2, wherein the operations further comprise generating a query to receive the second database state at one or more time intervals.

7. The machine-readable storage device of claim 1, wherein receiving the one or more changes comprises:

identifying one or more database operations relevant to the one or more changes;

receiving one or more targets correspondingly relevant to the one or more database operations;

receiving one or more first names of the one or more targets of the one or more database operations, wherein the one or more first names are relevant to the first database state;

receiving one or more second names of the one or more targets of the one or more database operations, wherein the one or more second names are relevant to the second database state; and

receiving one or more timestamp values for the one or more database operations.

8. The machine-readable storage device of claim 1, wherein detecting the change in the database schema further comprises generating a data definition language trigger.

9. The machine-readable storage device of claim 1, wherein detecting the change in the database schema further comprises generating a data manipulation language trigger.

10. A computerized system including:

a processor; and

a memory in communication with the processor, the memory storing instructions related to;

a module operable to receive one or more database states from a database;

a module operable to find one or more changes between a first database schema relevant to a first database state and a second database schema relevant to a second database state; and

a module operable to apply the one or more changes to a report, wherein the module to apply the one or more changes to the report is operable to;

generate a parse tree having nodes and leaves relevant to the first database state, wherein the parse tree comprises syntactic structure of a first statement reflecting the first database state;

modify the generated parse tree to include the one or more changes between the first database schema and the second database schema by modifying the nodes and the leaves to reflect the second database state; and

traverse the modified parse tree to generate a second statement to modify the report to reflect the second database schema relevant to the second database state.

11. The system of claim 10, wherein the module operable to receive the one or more database states from the database is further operable to receive the one or more database states periodically and store the one or more database states to the memory.

12. The system of claim 10, further comprising:

a report generator operable to receive and organize database data; and

a user interface operable to receive and display database data from the report generator.

13. A computerized method, comprising:

identifying one or more database operations relevant to one or more changes in a database schema;

identifying one or more targets of the one or more database operations; and

applying the one or more changes to the one or more targets in a report, by;

generating a parse tree having nodes and leaves relevant to a first database state, wherein the parse tree comprises syntactic structure of a first statement reflecting the first database state;

modifying the generated parse tree to include the one or more changes in the database schema by modifying the nodes and the leaves to reflect a second database state; and

traversing the modified parse tree to generate a second statement to modify the report to reflect the database schema relevant to the second database state.

14. The method of claim 13, further comprising:

receiving one or more first names of the one or more targets of the one or more database operations, wherein the one or more first names are relevant to the first database state; and

receiving one or more second names of the one or more targets of the one or more database operations, wherein the one or more second names are relevant to the second database state.

15. The method of claim 13, further comprising receiving one or more timestamp values for the one or more database operations.

16. The method of claim 13, further comprising recording the one or more changes in the database schema and storing the one or more changes to a memory.

17. The method of claim 16, further comprising retrieving the one or more changes form the memory to compare a first database schema relevant to the first database state to a second database schema relevant to the second database state.