Techniques for query and DML over relational tables using spreadsheet applications

• US 9,330,149 B2
• Filed: 12/18/2007
• Issued: 05/03/2016
• 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
• Primary Examiner(s): DWIVEDI, MAHESH H
• Application Number:
  US11/959,398
• Publication Number:
  US 20090158251A1
• Time in Patent Office: 3,059 Days
• Field of Search: None
• US Class Current: 1/1
• 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 spreadsheet file, wherein the request includes a filename for the file;

wherein 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 receiving said request to open a spreadsheet file;

the database server retrieving spreadsheet data via an eXtensible Markup Language (XML) view that is defined by a particular query;

the database server converting said spreadsheet data retrieved via the XML view into said spreadsheet file;

the database server providing the spreadsheet file directly to the spreadsheet application;

wherein the method is performed by one or more computer systems.

2. The method of claim 1, further comprising:

prior to receiving the request from the spreadsheet application, the database server creating the XML view, wherein creating the XML view comprises the steps of;

the database server receiving a Structured Query Language (SQL) query, wherein the SQL query specifies 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 particular query based on the SQL query and on the one or more key relationships, wherein the particular query specifies an 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 particular query.

3. The method of claim 2, further comprising the database server 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 server automatically generating and storing a mapping that associates one or more cells specified in the particular 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 modified spreadsheet file, wherein the second modified spreadsheet file includes changes made by the spreadsheet application to the spreadsheet file provided by the database server; and

the database server storing modified spreadsheet data included in the modified spreadsheet file 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 particular query that defines the XML view;

an insert operation that inserts a row of cells in the spreadsheet data that is specified by the particular 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 particular 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 particular 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 particular 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 spreadsheet file 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 particular query is an XML Query Language (XQuery) query, and wherein an 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 non-transitory 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 spreadsheet file, wherein the request includes a filename for the file;

wherein 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 receiving said request to open a spreadsheet file;

the database server retrieving spreadsheet data via an eXtensible Markup Language (XML) view that is defined by a query;

the database server converting said spreadsheet data retrieved via the XML view into said spreadsheet file;

the database server providing the spreadsheet file directly to the spreadsheet application.

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:

prior to receiving the request from the spreadsheet application, the database server creating the XML view, wherein creating the XML view comprises the steps of;

the database server receiving a Structured Query Language (SQL) query, wherein the SQL query specifies 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 particular query based on the SQL query and on the one or more key relationships, wherein the particular query specifies an 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 particular 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 server 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 server automatically generating and storing a mapping that associates one or more cells specified in the particular 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 modified spreadsheet file, wherein the modified spreadsheet file includes changes made by the spreadsheet application to the spreadsheet file provided by the database server; and

the database server storing modified spreadsheet data included in the modified spreadsheet file 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 particular query that defines the XML view;

an insert operation that inserts a row of cells in the spreadsheet data that is specified by the particular 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 particular 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 particular 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 particular 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 said spreadsheet file 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 particular query is an XML Query Language (XQuery) query, and wherein an 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.