TECHNIQUES FOR QUERY AND DML OVER RELATIONAL TABLES USING SPREADSHEET APPLICATIONS

• US 20090158251A1
• Filed: 12/18/2007
• Published: 06/18/2009
• Est. Priority Date: 12/18/2007
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Angrish, Rohan, Warner, James W., Liu, Zhen Hua
• Granted Patent: US 9,330,149 B2
• Time in Patent Office: Days
• US Class Current: 717/115
• CPC Class Codes: G06F 16/258 Data format conversion from or to a database
• CPC Class Codes: G06F 16/84 Mapping; Conversion

Abstract

In one embodiment, a database server receives a request from a spreadsheet application to open a file with spreadsheet data. The spreadsheet data is stored in one or more relational tables in one or more relational databases that are managed by the database server. In response to the request, the database server generates the file, where generating the file comprises retrieving the spreadsheet data from an XML view that is defined by an XQuery query. When executed, the XQuery query returns the spreadsheet data in an XML spreadsheet format that is understood by the spreadsheet application. The database server provides the generated file with the spreadsheet data directly to the spreadsheet application, where the spreadsheet data as retrieved from the XML view is formatted according to the XML spreadsheet format.

Claims

1. A computer-implemented method comprising:

at a database server, receiving a request from a spreadsheet application to open a file with spreadsheet data;

wherein the spreadsheet data is stored in one or more relational tables in one or more relational databases that are managed by the database server;

the database server generating the file in response to the request, wherein generating the file comprises retrieving the spreadsheet data from an eXtensible Markup Language (XML) view that is defined by an XML Query Language (XQuery) query, wherein the XQuery query when executed returns the spreadsheet data in an XML spreadsheet format that is understood by the spreadsheet application; and

the database server providing the file with the spreadsheet data directly to the spreadsheet application, wherein the spreadsheet data retrieved from the XML view is formatted according to the XML spreadsheet format.

2. The method of claim 1, further comprising:

the database server receiving a Structured Query Language (SQL) query, wherein the SQL query specifies the spreadsheet data that is stored in the one or more relational tables;

the database server automatically determining one or more key relationships that are defined for the one or more relational tables;

the database server automatically generating the XQuery query based on the SQL query and on the one or more key relationships, wherein the XQuery query specifies the XML spreadsheet format that is understood by the spreadsheet application; and

the database server automatically generating and storing the XML view that is defined by the XQuery query.

3. The method of claim 2, further comprising the database sever automatically generating and storing a set of code that is associated with the XML view, wherein:

the set of code is executed by the database server in response to any data modification operation that is performed on the XML view; and

instead of performing a data modification operation on the XML view, the set of code is operable to perform one or more operations against the one or more relational tables.

4. The method of claim 3, further comprising:

the database sever automatically generating and storing a mapping that associates one or more cells specified in the XQuery query with one or more columns of the one or more relational tables that store the spreadsheet data;

wherein the set of code is operable to convert, based on the mapping, the data modification operation into the one or more operations against the one or more relational tables.

5. The method of claim 3, further comprising:

at the database server, receiving a second request from the spreadsheet application to save a second file that includes modified spreadsheet data, wherein the modified spreadsheet data includes changes made by the spreadsheet application to the spreadsheet data provided by the database server; and

the database server storing the modified spreadsheet data in the one or more relational tables, wherein storing the modified spreadsheet data comprises;

the database server determining that one or more data modification operations need to be performed on the XML view; and

the database server automatically executing the set of code instead of performing the one or more data modification operations, wherein executing the set of code comprises;

converting the one or more data modification operations into one or more respective sets of operations against the one or more relational tables; and

executing the one or more sets of operations in order to store the modified spreadsheet data into the one or more relational tables.

6. The method of claim 5, wherein the one or more data modification operations that need to be performed on the XML view include at least one of:

an update operation that updates a value in a cell of the spreadsheet data that is specified by the XQuery query that defines the XML view;

an insert operation that inserts a row of cells in the spreadsheet data that is specified by the XQuery query that defines the XML view; and

a delete operation that deletes a row of cells from the spreadsheet data that is specified by the XQuery query that defines the XML view.

7. The method of claim 5, wherein the one or more sets of operations against the one or more relational tables include at least one of:

a SQL UPDATE operation;

a SQL INSERT operation; and

a SQL DELETE operation.

8. The method of claim 2, wherein the database server automatically generating the XQuery query further comprises:

based on the one or more key relationships, automatically determining that one or more cells defined in the XML spreadsheet format are not allowed to be modified; and

inserting, in the XML spreadsheet format specified in the XQuery query, attribute data in association with each cell of the one or more cells, wherein the attribute data indicates that said each cell cannot be modified.

9. The method of claim 8, wherein the file with the spreadsheet data provided by the database server stores the attribute data in association with each cell of the one or more cells.

10. The method of claim 8, wherein the attribute data is an XML attribute that identifies a style that is defined by the spreadsheet application for write-protecting cells.

11. The method of claim 8, wherein the database server automatically determining the one or more key relationships comprises automatically examining one or more database dictionaries respectively associated with the one or more relational databases to determine one or more of:

one or more primary keys that are defined for at least one of the one or more relational tables; and

one or more foreign keys that are defined for at least one of the one more relational tables.

12. The method of claim 1, wherein the XML spreadsheet format specified in the XQuery query includes:

first set of tags defining one or more rows for storing the spreadsheet data; and

second set of tags defining one or more cells within the one or more rows.

13. The method of claim 1, further comprising the database server registering the XML view as a resource that is provided by the database server through a file access protocol operable to facilitate access to database resources in file format.

14. A computer-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, cause:

at a database server, receiving a request from a spreadsheet application to open a file with spreadsheet data;

wherein the spreadsheet data is stored in one or more relational tables in one or more relational databases that are managed by the database server;

the database server generating the file in response to the request, wherein generating the file comprises retrieving the spreadsheet data from an eXtensible Markup Language (XML) view that is defined by an XML Query Language (XQuery) query, wherein the XQuery query when executed returns the spreadsheet data in an XML spreadsheet format that is understood by the spreadsheet application; and

the database server providing the file with the spreadsheet data directly to the spreadsheet application, wherein the spreadsheet data retrieved from the XML view is formatted according to the XML spreadsheet format.

15. The computer-readable storage medium of claim 14, wherein the one or more sequences of instructions further comprise instructions which, when executed by the one or more processors, cause:

the database server receiving a Structured Query Language (SQL) query, wherein the SQL query specifies the spreadsheet data that is stored in the one or more relational tables;

the database server automatically determining one or more key relationships that are defined for the one or more relational tables;

the database server automatically generating the XQuery query based on the SQL query and on the one or more key relationships, wherein the XQuery query specifies the XML spreadsheet format that is understood by the spreadsheet application; and

the database server automatically generating and storing the XML view that is defined by the XQuery query.

16. The computer-readable storage medium of claim 15, wherein the one or more sequences of instructions further comprise instructions which, when executed by the one or more processors, cause the database sever automatically generating and storing a set of code that is associated with the XML view, wherein:

the set of code is executed by the database server in response to any data modification operation that is performed on the XML view; and

instead of performing a data modification operation on the XML view, the set of code is operable to perform one or more operations against the one or more relational tables.

17. The computer-readable storage medium of claim 16, wherein the one or more sequences of instructions further comprise instructions which, when executed by the one or more processors, cause:

the database sever automatically generating and storing a mapping that associates one or more cells specified in the XQuery query with one or more columns of the one or more relational tables that store the spreadsheet data;

wherein the set of code is operable to convert, based on the mapping, the data modification operation into the one or more operations against the one or more relational tables.

18. The computer-readable storage medium of claim 16, wherein the one or more sequences of instructions further comprise instructions which, when executed by the one or more processors, cause:

at the database server, receiving a second request from the spreadsheet application to save a second file that includes modified spreadsheet data, wherein the modified spreadsheet data includes changes made by the spreadsheet application to the spreadsheet data provided by the database server; and

the database server storing the modified spreadsheet data in the one or more relational tables, wherein storing the modified spreadsheet data comprises;

the database server determining that one or more data modification operations need to be performed on the XML view; and

the database server automatically executing the set of code instead of performing the one or more data modification operations, wherein executing the set of code comprises;

converting the one or more data modification operations into one or more respective sets of operations against the one or more relational tables; and

executing the one or more sets of operations in order to store the modified spreadsheet data into the one or more relational tables.

19. The computer-readable storage medium of claim 18, wherein the one or more data modification operations that need to be performed on the XML view include at least one of:

an update operation that updates a value in a cell of the spreadsheet data that is specified by the XQuery query that defines the XML view;

an insert operation that inserts a row of cells in the spreadsheet data that is specified by the XQuery query that defines the XML view; and

a delete operation that deletes a row of cells from the spreadsheet data that is specified by the XQuery query that defines the XML view.

20. The computer-readable storage medium of claim 18, wherein the one or more sets of operations against the one or more relational tables include at least one of:

a SQL UPDATE operation;

a SQL INSERT operation; and

a SQL DELETE operation.

21. The computer-readable storage medium of claim 15, wherein the instructions that cause the database server automatically generating the XQuery query further comprise instructions which, when executed by the one or more processors, cause:

based on the one or more key relationships, automatically determining that one or more cells defined in the XML spreadsheet format are not allowed to be modified; and

inserting, in the XML spreadsheet format specified in the XQuery query, attribute data in association with each cell of the one or more cells, wherein the attribute data indicates that said each cell cannot be modified.

22. The computer-readable storage medium of claim 21, wherein the file with the spreadsheet data provided by the database server stores the attribute data in association with each cell of the one or more cells.

23. The computer-readable storage medium of claim 21, wherein the attribute data is an XML attribute that identifies a style that is defined by the spreadsheet application for write-protecting cells.

24. The computer-readable storage medium of claim 21, wherein the instructions that cause the database server automatically determining the one or more key relationships further comprise instructions which, when executed by the one or more processors, cause automatically examining one or more database dictionaries respectively associated with the one or more relational databases to determine one or more of:

one or more primary keys that are defined for at least one of the one or more relational tables; and

one or more foreign keys that are defined for at least one of the one more relational tables.

25. The computer-readable storage medium of claim 14, wherein the XML spreadsheet format specified in the XQuery query includes:

first set of tags defining one or more rows for storing the spreadsheet data; and

second set of tags defining one or more cells within the one or more rows.

26. The computer-readable storage medium of claim 14, wherein the one or more sequences of instructions further comprise instructions which, when executed by the one or more processors, cause the database server registering the XML view as a resource that is provided by the database server through a file access protocol operable to facilitate access to database resources in file format.