Method for editing views created by joins

• US 5,832,485 A
• Filed: 02/14/1996
• Issued: 11/03/1998
• Est. Priority Date: 02/14/1996
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle Corporation
• Inventors: Chaudhry, Atif
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): Min, Donald
• Application Number:
  US08/601,127
• Time in Patent Office: 993 Days
• Field of Search: 395/611, 395/613, 395/616, 395/617, 707/1, 707/9, 707/200, 707/201, 707/100, 707/102
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24535 of sub-queries or views
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: G06F 16/2456 Join operations
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99939 Privileged access
• CPC Class Codes: Y10S 707/99952 Coherency, e.g. same view to multiple users

Abstract

A method and apparatus for selectively allowing data presented in a join view to be modified is disclosed. Data presented in a join view is allowed to be modified if provided to the join view by a key-preserved table. A table is a key-preserved table with respect to a join view if each of the keys of the table is a key of the join view. Key-preserved tables may be identified by inspecting the definition of a join view to identify the base tables of the join view and to generate a graph. In the graph, each node corresponds to one of the base tables. Directed edges between nodes of the graph correspond to relationships, specified in the definition of the join view, between the base tables. The graph is reduced to strong components, each of which corresponds to a set of the base tables in which each base table in the set is routed to each other base table in the set by one or more directed edges. If a strong component in the resulting graph constitutes a unique source vertex, then each of the tables corresponding to the unique source vertex are key-preserved tables.

Claims

1. A method for selectively allowing data presented in a join view to be modified, the method comprising the computer-implemented steps of:

identifying a plurality of tables that provide data to the join view;

identifying one or more tables of the plurality of tables as key-preserved tables with respect to the join view; and

allowing only data provided to the join view by the one or more key-preserved tables to be modified.

2. The method of claim 1 wherein said step of identifying a plurality of tables that provide data to the join view comprises the step of inspecting a definition of the join view.

3. The method of claim 1 wherein said step of identifying one or more tables as key-preserved tables comprises the steps of:

identifying each key of a first table of the plurality of tables; and

determining whether each key of the first table is a key of the join view.

4. The method of claim 1 wherein said step of identifying one or more tables as key-preserved tables comprises the step of identifying a unique source vertex defined by one or more tables of the plurality of tables, each of the one or more tables defining the unique source vertex being a key-preserved table.

5. The method of claim 4 wherein said step of identifying a unique source vertex comprises the steps of:

identifying at least one directed edge between the plurality of tables;

identifying at least one strong component based on the at least one directed edge, the at least one strong component being defined by one or more tables of the plurality of tables; and

determining the strong component to be the only strong component defined by the plurality of tables which has no incoming directed edge, a unique source vertex being defined by the one or more tables defining the strong component.

6. The method of claim 4 wherein said step of identifying a unique source vertex comprises the steps of:

identifying each directed edge between the plurality of tables;

identifying one or more strong components defined by the plurality of tables based on directed edges between the plurality of tables, each strong component being defined by one or more tables of the plurality of tables;

determining a first strong component of the one or more strong components to be the only strong component which has no incoming directed edge, a unique source vertex being defined by the one or more tables defining the first strong component.

7. The method of claim 6 wherein said step of identifying each directed edge between the plurality of tables comprises the steps of:

identifying an edge between two tables of the plurality of tables; and

directing the edge toward a first table of the two tables.

8. The method of claim 7 wherein said step of identifying an edge between two tables comprises the step of inspecting a definition of the join view to identify a join predicate equi-joining the two tables, and wherein said step of directing the edge toward a first table of the two tables comprises the step of determining a key of the first table to be an operand in the join predicate.

9. The method of claim 6 wherein said step of identifying each directed edge between the plurality of tables comprises the steps of:

inspecting a definition of the join view to identify a join predicate defining an edge between two tables of the plurality of tables;

identifying an operand of the join predicate which is a key of a first table of the two tables; and

directing the edge toward the first table.

10. The method of claim 6 wherein said step of identifying one or more strong components comprises the step of identifying a set of one or more tables from the plurality of tables, each table in the set being routed to each other table in the set by one or more directed edges.

11. The method of claim 1 wherein said step of identifying one or more tables as key-preserved tables comprises the steps of:

generating a graph that includes a set of nodes and a set of directed edges connecting the nodes, wherein each node corresponds to one of the plurality of tables and each directed edge represents a relationship between two tables of the plurality of tables with respect to the join view; and

identifying a unique source vertex in the graph, each of the one or more tables defining the unique source vertex being a key-preserved table.

12. The method of claim 11 wherein said step of identifying a unique source vertex comprises the steps of:

identifying one or more strong components based on the set of nodes and the set of directed edges, each of the one or more strong components comprising one or more nodes of the set of nodes; and

determining one strong component of the one or more strong components to be the only strong component which has no incoming directed edge, the one strong component defining a unique source vertex.

13. The method of claim 12 wherein said step of identifying one or more strong components comprises the step of identifying a group of one or more nodes from the set of nodes, each node in the group being routed to each other node in the group by one or more directed edges.

14. The method of claim 11 wherein said step of generating a graph that includes a set of nodes and a set of directed edges connecting the nodes comprises the steps of:

identifying an edge between two nodes of the set of nodes; and

directing the edge toward a first node of the two nodes.

15. The method of claim 14 wherein said step of identifying an edge between two nodes comprises the step of inspecting a definition of the join view to identify a join predicate equi-joining two tables corresponding to the two nodes, and wherein said step of directing the edge toward a first node of the two nodes comprises the step of determining a key of a first table of the two tables to be an operand in the join predicate.

16. A method for selectively allowing data presented in a join view to be modified comprising the steps of:

inspecting a definition of the join view to identify tables providing data to the join view;

inspecting the definition of the join view to identify directed edges between two of the tables;

identifying at least one set of tables from the tables, the set of tables comprising at least one table and each table in the set being routed to each other table in the set by the directed edges;

determining that only a first set of tables of the at least one set of tables has no incoming directed edge extending from a table outside the set of tables; and

allowing only data provided to the join view by the first set of tables to be modified.

17. A computer system comprising:

a processor;

a memory coupled to said processor;

said memory having stored therein

tables comprising categories of data, the categories containing data organized in a plurality of records, each record being uniquely identified by a key value;

a join view of data held in the tables;

sequences of instructions which, when executed by said processor, cause said processor to allow data presented in the join view to be modified, the sequence of instructions causing the processor to perform the steps of;

identifying at least one of the tables as a key-preserved table with respect to the join view; and

allowing data presented in the join view to be modified if the data is provided to the join view by the key-preserved table.

18. A computer-readable medium having stored thereon a plurality of sequences of instructions, said plurality of sequences of instructions including instructions which, when executed by a processor, cause said processor to perform the steps of:

identifying a plurality of tables that provide data to the join view;

identifying one or more tables of the plurality of tables as key-preserved tables with respect to the join view; and

allowing only data provided to the join view by the one or more key-preserved tables to be modified.

19. The computer-readable medium recited in claim 18, wherein the step of identifying a plurality of tables that provide data to the join view further comprises the step of inspecting a definition of the join view.

20. The computer-readable medium of claim 18 wherein the step of identifying one or more tables as key-preserved tables comprises the steps of:

identifying each key of a first table of the plurality of tables; and

determining whether each key of the first table is a key of the join view.

21. The computer-readable medium of claim 18 wherein the step of identifying one or more tables as key-preserved tables comprises the step of identifying a unique source vertex defined by one or more tables of the plurality of tables, each of the one or more tables defining the unique source vertex being a key-preserved table.

22. The computer-readable medium of claim 21 wherein the step of identifying a unique source vertex comprises the steps of:

identifying at least one directed edge between the plurality of tables;

identifying at least one strong component based on the at least one directed edge, the at least one strong component being defined by one or more tables of the plurality of tables; and

determining the strong component to be the only strong component defined by the plurality of tables which has no incoming directed edge, a unique source vertex being defined by the one or more tables defining the strong component.

23. The computer-readable medium of claim 21 wherein the step of identifying a unique source vertex comprises the steps of:

identifying each directed edge between the plurality of tables;

identifying one or more strong components defined by the plurality of tables based on directed edges between the plurality of tables, each strong component being defined by one or more tables of the plurality of tables;

determining a first strong component of the one or more strong components to be the only strong component which has no incoming directed edge, a unique source vertex being defined by the one or more tables defining the first strong component.

24. The computer-readable medium of claim 23 wherein the step of identifying each directed edge between the plurality of tables comprises the steps of:

identifying an edge between two tables of the plurality of tables; and

directing the edge toward a first table of the two tables.

25. The computer-readable medium of claim 24 wherein the step of identifying an edge between two tables comprises the step of inspecting a definition of the join view to identify a join predicate equi-joining the two tables, and wherein the step of directing the edge toward a first table of the two tables comprises the step of determining a key of the first table to be an operand in the join predicate.

26. The computer-readable medium of claim 23 wherein the step of identifying each directed edge between the plurality of tables comprises the steps of:

inspecting a definition of the join view to identify a join predicate defining an edge between two tables of the plurality of tables;

identifying an operand of the join predicate which is a key of a first table of the two tables; and

directing the edge toward the first table.

27. The computer-readable medium of claim 23 wherein the step of identifying one or more strong components comprises the step of identifying a set of one or more tables from the plurality of tables, each table in the set being routed to each other table in the set by one or more directed edges.

28. The computer-readable medium of claim 18 wherein the step of identifying one or more tables as key-preserved tables comprises the steps of:

generating a graph that includes a set of nodes and a set of directed edges connecting the nodes, wherein each node corresponds to one of the plurality of tables and each directed edge represents a relationship between two tables of the plurality of tables with respect to the join view; and

identifying a unique source vertex in the graph, each of the one or more tables defining the unique source vertex being a key-preserved table.

29. The computer-readable medium of claim 28 wherein the step of identifying a unique source vertex comprises the steps of:

identifying one or more strong components based on the set of nodes and the set of directed edges, each of the one or more strong components comprising one or more nodes of the set of nodes; and

determining one strong component of the one or more strong components to be the only strong component which has no incoming directed edge, the one strong component defining a unique source vertex.

30. The computer-readable medium of claim 29 wherein the step of identifying one or more strong components comprises the step of identifying a group of one or more nodes from the set of nodes, each node in the group being routed to each other node in the group by one or more directed edges.

31. The computer-readable medium of claim 28 wherein the step of generating a graph that includes a set of nodes and a set of directed edges connecting the nodes comprises the steps of:

identifying an edge between two nodes of the set of nodes; and

directing the edge toward a first node of the two nodes.

32. The computer-readable medium of claim 31 wherein the step of identifying an edge between two nodes comprises the step of inspecting a definition of the join view to identify a join predicate equi-joining two tables corresponding to the two nodes, and wherein the step of directing the edge toward a first node of the two nodes comprises the step of determining a key of a first table of the two tables to be an operand in the join predicate.

33. A computer-readable medium having stored thereon a plurality of sequences of instructions, said plurality of sequences of instructions including sequences of instructions which, when executed by a processor, cause said processor to perform the steps of:

inspecting a definition of a join view to identify tables providing data to the join view;

inspecting the definition of the join view to identify directed edges between two of the tables;

identifying at least one set of tables from the tables, the set of tables comprising at least one table and each table in the set being routed to each other table in the set by the directed edges;

determining that only a first set of tables of the at least one set of tables has no incoming directed edge extending from a table outside the set of tables; and

allowing only data provided to the join view by the first set of tables to be modified.