System and method for recursive path analysis of DBMS procedures

• US 7,526,468 B2
• Filed: 01/08/1999
• Issued: 04/28/2009
• Est. Priority Date: 01/08/1999
• Status: Active Grant

• Current Assignee: Computer Associates Think Inc. (Broadcom, Inc.)
• Original Assignee: Computer Associates Think Inc. (Broadcom, Inc.)
• Inventors: Vincent, John K., Cherny, Igor
• Primary Examiner(s): Breene; John E
• Assistant Examiner(s): Ly; Anh
• Application Number:
  US09/226,939
• Publication Number:
  US 20010049682A1
• Time in Patent Office: 3,763 Days
• Field of Search: 707 1- 10, 707100-1041, 707/203, 709/219, 709/330, 709/332, 709/106, 717/4, 717/8, 717/1, 717/5, 717/7, 717/108, 717/126, 717/129, 717145-146, 717/153, 717/162, 717/104, 717/112, 717/141, 717/177, 710/14
• US Class Current: 1/1
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

Abstract

A system, method and database development tool are disclosed for automatically generating the complete dependency graph for use in debugging stored code objects in a database, by using a recursive dependency tracking algorithm which takes into consideration the indirect dependencies on triggers as well as the dependencies on implementations of object oriented code objects which are represented as separate objects in the database catalog.

Claims

1. A method of generating dependency information for code objects stored in a database, comprising:

recursively querying a database for one or more dependencies of procedural code objects stored in the database;

identifying one or more dependencies of procedural code objects stored in the database;

generating a dependency information tracking array based on the identification of one or more dependencies of procedural code objects; and

stopping the recursive query of the database upon identifying a dependency that is already included in the dependency information tracking array.

2. The method of claim 1, further comprising:

recursively querying the database for one or more dependencies of specifications of object-oriented code objects stored in the database;

identifying one or more dependencies of specifications of object-oriented code objects stored in the database; and

incorporating the one or more dependencies of specifications of object-oriented code objects into the dependency information tracking array.

3. The method of claim 2, wherein the specifications of object-oriented code objects comprise PL/SQL specifications for a collection of stored functions and procedures identified as a single entity.

4. The method of claim 1, further comprising:

recursively querying the database for one or more dependencies of implementations of object-oriented code objects in the database;

identifying one or more dependencies of implementations of object-oriented code objects in the database; and

incorporating the one or more dependencies of implementations of object-oriented code objects in the database into the dependency information tracking array.

5. The method of claim 4, wherein the implementations of object-oriented code objects comprise PL/SQL implementations for a collection of stored functions and procedures identified as a single entity.

6. The method of claim 1, further comprising:

parsing the source code of the database for data manipulation statements that fire triggers; and

identifying one or more data manipulation statements that fire triggers.

7. The method of claim 6, wherein parsing the source code of the database for data manipulation statements that fire triggers comprises parsing the source code for UPDATE, DELETE, or INSERT statements.

8. The method of claim 6, further comprising:

recursively querying the database for one or more dependencies on triggers of code objects stored in the database based on the one or more data manipulation statements that fire triggers;

identifying one or more dependencies on triggers of code objects stored in the database; and

incorporating the one or more dependencies on triggers of code objects stored in the database into the dependency information tracking array.

9. The method of claim 1, further comprising compiling one or more code objects stored in the database in debug mode using a database code object debugging tool.

10. The method of claim 1, further comprising identifying one or more dependent objects stored in the database that are INVALID.

11. The method of claim 1, further comprising generating a dependency graph for code objects stored in the database based at least in part on the dependency information tracking array.

12. The method of claim 1, wherein the database comprises a database catalog;

and

wherein querying the database comprises querying the database catalog.

13. The method of claim 1, further comprising displaying a dependency graph to a user, the dependency graph generated based at least in part on the dependency information tracking array.

14. The method of claim 1, further comprising identifying one or more cyclic dependencies among code objects stored in the database.

15. A method of generating dependency information for code objects stored in a database, comprising:

recursively querying a database for one or more dependencies of procedural code objects stored in the database;

identifying one or more dependencies of procedural code objects stored in the database;

generating a dependency information tracking array based on the identification of one or more dependencies of procedural code objects; and

identifying one or more cyclic dependencies among code objects stored in the database.

16. The method of claim 15, wherein identifying one or more cyclic dependencies comprises utilizing a graph traversal algorithm to identify one or more cyclic dependencies.

17. A method of generating dependency information for code objects stored in a database, comprising:

recursively querying a database for one or more dependencies of procedural code objects stored in the database;

identifying one or more dependencies of procedural code objects stored in the database;

recursively querying the database for one or more dependencies of specifications of object-oriented code objects stored in the database;

identifying one or more dependencies of specifications of object-oriented code objects stored in the database;

recursively querying the database for one or more dependencies of implementations of object-oriented code objects in the database;

identifying one or more dependencies of implementations of object-oriented code objects in the database;

parsing the source code of the database for data manipulation statements that fire triggers;

identifying one or more data manipulation statements that fire triggers;

recursively querying the database for one or more dependencies on triggers of code objects stored in the database based on the one or more data manipulation statements that fire triggers;

identifying one or more dependencies on triggers of code objects stored in the database; and

generating a dependency information tracking array based on the identification of one or more dependencies of procedural code objects stored in the database, one or more dependencies of specifications of object-oriented code objects stored in the database, one or more dependencies of implementations of object-oriented code objects stored in the database, and one or more dependencies on triggers of code objects stored in the database.

18. The method of claim 17, wherein parsing the source code of the database for data manipulation statements that fire triggers comprises parsing the source code for UPDATE, DELETE, or INSERT statements.

19. The method of claim 17, further comprising compiling one or more code objects in debug mode using a database code object debugging tool.

20. The method of claim 17, further comprising identifying one or more dependent objects in the database that are INVALID.

21. The method of claim 17, further comprising identifying one or more cyclic dependencies among code objects stored in the database.

22. The method of claim 21, wherein identifying one or more cyclic dependencies comprises utilizing a graph traversal algorithm to identify one ore more cyclic dependencies.

23. The method of claim 17, further comprising generating a dependency graph for the code object based at least in part on the dependency information tracking array.

24. The method of claim 17, wherein the database comprises a database catalog;

and

wherein querying the database comprises querying the database catalog.

25. A system for generating dependency information for code objects stored in a database, comprising:

a database operable to store code objects; and

a processing system operable to;

recursively query the database for one or more dependencies of procedural code objects stored in the database;

identify one or more dependencies of procedural code objects stored in the database;

generate a dependency information tracking array based on the identification of one or more dependencies of procedural code objects; and

stop the recursive query of the database upon identifying a dependency that is already included in the dependency information tracking array.

26. The system of claim 25, wherein the processing system is further operable to:

recursively query the database for one or more dependencies of specifications of object-oriented code objects stored in the database;

identify one or more dependencies of specifications of object-oriented code objects stored in the database; and

incorporate the one or more dependencies of dependencies of specifications of object-oriented code objects stored in the database into the dependency information tracking array.

27. The system of claim 25, wherein the processing system is further operable to:

recursively query the database for one or more dependencies of implementations of object-oriented code objects stored in the database;

identify one or more dependencies of implementations of object-oriented code objects stored in the database; and

incorporate the one or more dependencies of implementations of object-oriented code objects stored in the database into the dependency information tracking array.

28. The system of claim 25, wherein the processing system is further operable to:

parse the source code of the database for data manipulation statements that fire triggers;

identify one or more data manipulation statements that fire triggers;

recursively query the database for one or more dependencies on triggers of code objects stored in the database based on the one or more data manipulation statements that fire triggers;

identify one or more dependencies on triggers of code objects stored in the database; and

incorporate the one or more dependencies on triggers of code objects stored in the database into the dependency information tracking array.

29. A system for generating dependency information for a code object stored in a database, comprising:

a database operable to store a code object; and

a processing system operable to;

recursively query the database for one or more dependencies of procedural code objects stored in the database;

identify one or more dependencies of procedural code objects stored in the database;

recursively query the database for one or more dependencies of specifications of object-oriented code objects stored in the database;

identify one or more dependencies of specifications of object-orients code objects stored in the database;

recursively query the database for one or more dependencies of implementations of object-oriented code objects stored in the database;

identify one or more dependencies of implementations of object-oriented code objects stored in the database;

parse the source code of the database for data manipulation statements that fire triggers;

identify one or more data manipulation statements that fire triggers;

recursively query the database for one or more dependencies on triggers of code objects stored in the database based on the one or more data manipulation statements that fire triggers;

identify one or more dependencies on triggers of code objects stored in the database; and

generate a dependency information tracking array based on the identification of one or more dependencies of procedural code objects stored in the database, one or more dependencies of specifications of object-oriented code objects stored in the database, one or more dependencies of implementations of object-oriented code objects stored in the database, and one or more dependencies on triggers of code objects stored in the database.

30. A computer-readable medium encoded with logic operable, when executed on a computer processor, to perform the steps comprising:

recursively querying a database for one or more dependencies of procedural code objects stored in the database;

identifying one or more dependencies of procedural code objects stored in the database; and

generating a dependency information tracking array based on the identification of one or more dependencies of procedural code objects; and

stopping the recursive query of the database upon identifying a dependency that is already included in the dependency information tracking array.

31. The computer-readable medium encoded with logic of claim 30, further operable to perform the steps comprising:

recursively querying the database for one or more dependencies of specifications of object-oriented code objects stored in the database;

identifying one or more dependencies of specifications of object-oriented code objects stored in the database; and

incorporating the one or more dependencies of specifications of object-oriented code objects into the dependency information tracking array.

32. The computer-readable medium encoded with logic of claim 30, further operable to perform the steps comprising:

recursively querying the database for one or more dependencies of implementations of object-oriented code objects in the database;

identifying one or more dependencies of implementations of object-oriented code objects in the database; and

incorporating the one or more dependencies of implementations of object-oriented code objects in the database into the dependency information tracking array.

33. The computer-readable medium encoded with logic of claim 30, further operable to perform the steps comprising:

parsing the source code of the database for data manipulation statements that fire triggers; and

identifying one or more data manipulation statements that fire triggers;

recursively querying the database for one or more dependencies on triggers of code objects stored in the database based on the one or more data manipulation statements that fire triggers;

identifying one or more dependencies on triggers of code objects stored in the database; and

incorporating the one or more dependencies on triggers of code objects stored in the database into the dependency information tracking array.

34. A computer-readable medium encoded with logic operable, when executed on a computer processor, to perform the steps comprising:

recursively querying a database for one or more dependencies of procedural code objects stored in the database;

identifying one or more dependencies of procedural code objects stored in the database;

recursively querying the database for one or more dependencies of specifications of object-oriented code objects stored in the database;

identifying one or more dependencies of specifications of object-oriented code objects stored in the database;

recursively querying the database for one or more dependencies of implementations of object-oriented code objects in the database;

identifying one or more dependencies of implementations of object-oriented code objects in the database;

parsing the source code of the database for data manipulation statements that fire triggers;

identifying one or more data manipulation statements that fire triggers;

recursively querying the database for one or more dependencies on triggers of code objects stored in the database based on the one or more data manipulation statements that fire triggers;

identifying one or more dependencies on triggers of code objects stored in the database; and

generating a dependency information tracking array based on the identification of one or more dependencies of procedural code objects stored in the database, one or more dependencies of specifications of object-oriented code objects stored in the database, one or more dependencies of implementations of object-oriented code objects stored in the database, and one or more dependencies on triggers of code objects stored in the database.

35. A method of generating dependency information for code objects stored in a database, comprising:

recursively querying a database for one or more dependencies of procedural code objects stored in the database;

identifying one or more dependencies of procedural code objects stored in the database;

generating a dependency information tracking array based on the identification of one or more dependencies of procedural code objects; and

for each of the one or more dependencies identified, determining whether the dependency already occurs in the graph; and

terminating the recursive query of the database upon determining that one of the one or more dependencies already occurs in the graph.