System and method for browsing database schema information

• US 6,571,232 B1
• Filed: 11/01/1999
• Issued: 05/27/2003
• Est. Priority Date: 11/01/1999
• Status: Expired due to Term

• Current Assignee: Oracle America, Inc. (Oracle Corporation)
• Original Assignee: Sun Microsystems Incorporated (Oracle Corporation)
• Inventors: Goldberg, Robert N., Lam, Gloria Y., White, Seth J.
• Primary Examiner(s): Robinson, Greta
• Assistant Examiner(s): Le, Debbie M.
• Application Number:
  US09/431,644
• Time in Patent Office: 1,303 Days
• Field of Search: 707/100, 707/3, 707/10, 707/102, 707/103, 707/1, 717/2
• US Class Current: 1/1
• CPC Class Codes: G06F 16/211 Schema design and management
• CPC Class Codes: G06F 16/9574 of access to content, e.g. by caching
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99944 Object-oriented database structure

Abstract

A query object generator tool which generates interface definitions and code that implement a query object also generates a database schema access query object that retrieves the schema of an underlying database. Instead of retrieving data from the database, the database schema access query object retrieves “metadata”, including the names of tables and stored procedures in the database and their descriptions, from the database. The metadata is then displayed as part of a graphic user interface which is used to construct the SQL query implemented by the query object. In accordance with one embodiment of the invention, the database schema access object also verifies that an SQL query generated by a user is valid by submitting the query to the database engine of the underlying database. In accordance with another embodiment of the invention the database schema access object contains methods which perform automatic mapping of “standard” SQL data types to vendor-specific data.

Claims

1. A system operable in a computer having a memory for displaying to a user database schema information of a database having metadata describing the database schema, the system comprising:

a database schema access object in the memory having database specific methods for connecting to the database and for retrieving the metadata;

a database independent browsing object which calls the methods in the database schema access object to retrieve the metadata and format the metadata as database independent information; and

a display mechanism for displaying the database independent information to the user.

2. A system according to claim 1 wherein the database independent information is textual information.

3. A system according to claim 1 wherein the display mechanism is a graphical display mechanism.

4. A system according to claim 1 wherein the database schema access object includes a method for retrieving metadata describing database objects.

5. A system according to claim 1 wherein the database schema access object includes a method for retrieving metadata describing database procedures.

6. A system according to claim 1 wherein the database schema access object includes a method for retrieving metadata describing database column information.

7. A system according to claim 1 wherein the database schema access object includes a method for submitting a query to the database for validating the query syntax.

8. A system according to claim 7 wherein the method for submitting a query to the database comprises a mechanism for performing a rollback of changes made to the database by the query.

9. A system according to claim 1 wherein the database schema access object includes a method for translating data types between SQL data types and database specific data types.

10. A system according to claim 1 wherein the database browsing object includes a method for connecting to the database schema access object.

11. A system according to claim 9 wherein the database schema access object includes a method for checking a connection between the database browsing object and the database schema access object.

12. A method operable in a computer having a memory for displaying to a user database schema information of a database having metadata describing the database schema, the method comprising the steps of:

(a) creating a database schema access object in the memory having database specific methods for connecting to the database and for retrieving the metadata;

(b) creating a database independent browsing object which calls the methods in the database schema access object to retrieve the metadata and format the metadata as database independent information; and

(c) displaying the database independent information to the user.

13. A method according to claim 12 wherein the database independent information is textual information.

14. A method according to claim 12 wherein step (c) comprises the step of:

(c1) displaying the database independent information graphically to the user.

15. A method according to claim 12 wherein the database schema access object includes a method for retrieving metadata describing database objects.

16. A method according to claim 12 wherein the database schema access object includes a method for retrieving metadata describing database procedures.

17. A method according to claim 12 wherein the database schema access object includes a method for retrieving metadata describing database column information.

18. A method according to claim 12 wherein the database schema access object includes a method for submitting a query to the database for validating the query syntax.

19. A method according to claim 18 wherein the method for submitting a query to the database comprises a method for performing a rollback of changes made to the database by the query.

20. A method according to claim 12 wherein the database schema access object includes a method for translating data types between SQL data types and database specific data types.

21. A computer program product operable in a computer having a memory for displaying to a user database schema information of a database having metadata describing the database schema, the computer program product comprising a computer usable medium having computer readable program code thereon, including:

program class code for creating a database schema access object in the memory having database specific methods for connecting to the database and for retrieving the metadata;

program class code for creating a database independent browsing object which calls the methods in the database schema access object to retrieve the metadata and format the metadata as database independent information; and

program code for displaying the database independent information to the user.

22. A computer program product according to claim 21 wherein the database independent information is textual information.

23. A computer program product according to claim 21 wherein the program code for displaying the database independent information comprises program code for displaying the database independent information graphically to the user.

24. A computer program product according to claim 21 wherein the database browsing object includes a method for connecting to the database schema access object.

25. A computer program product according to claim 21 wherein the database schema access object includes a method for checking a connection between the database browsing object and the database schema access object.