SQL enhancements simplifying database querying

• US 9,639,572 B2
• Filed: 09/06/2013
• Issued: 05/02/2017
• Est. Priority Date: 09/06/2013
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: SAP SE
• Inventors: Hutzel, Daniel, Falter, Timm, Baeuerle, Stefan
• Primary Examiner(s): Torgrimson, Tyler
• Application Number:
  US14/020,610
• Publication Number:
  US 20150074083A1
• Time in Patent Office: 1,334 Days
• Field of Search: None
• CPC Class Codes: G06F 16/2445 Data retrieval commands; View definitions
• CPC Class Codes: G06F 16/288 Entity relationship models

Abstract

Disclosed are techniques employed alone or in combination, to further enhance a database language and improve conciseness and comprehensiveness of query statements in application code. Embodiments may build upon an initial extension of database language to accommodate concepts of entity-relationship models (ERMs). That initial extension may include entities with structured types, and named associations. One technique modifies SQL to allow the SELECT command to specify element path expressions based upon a relationship (e.g. named associations between ERM elements). Another technique modifies SQL to introduce filter expressions suffixed to associations within path expressions, thus improving adherence to the principle of locality within query statements. Yet another technique allows a shorthand to “factor out” association path prefixes, thereby avoiding lengthy lists of path expressions in order to read several elements from the same nested structure or a same target entity behind a single association. Other techniques simplifying database querying are also disclosed.

Claims

1. A computer-implemented method comprising:

providing in a database layer, a database engine and a database comprising data organized according to a relational model;

providing in an application layer, an entity relationship data model and a query engine in communication with the database engine;

causing the database engine to receive from via the query engine a query in a database language that has been extended to include an entity type and an association, the query further comprising a path expression including a filter element suffix followed by nested projection clauses specifying a relationship between a specified entity and an associated entity;

causing the database engine to return to the query engine a query result set comprising data of the associated entity matching the nested projection clauses of the path expression,

wherein if the path expression includes an optional flattening symbol, the query result set comprises the data in a result set type structure that is flattened, and

wherein if the path expression omits the optional flattening symbol, the query result set comprises the data in the result set type structure that corresponds to the nested projection clauses; and

causing the database engine to store the data in the database according to the result set type structure.

2. The computer-implemented method of claim 1 wherein the path expression further comprises a shorthand factoring out an association path prefix.

3. The computer-implemented method of claim 1 wherein the database language is SQL.

4. The computer-implemented method of claim 3 wherein the query comprises SELECT and FROM clauses presented in a revised order.

5. The computer-implemented method of claim 1 wherein the database comprises an in memory database.

6. A non-transitory computer readable storage medium embodying a computer program for performing a method, said method comprising:

providing in a database layer, a database engine and a database comprising data organized according to a relational model;

providing in an application layer, an entity relationship data model and a query engine in communication with the database engine;

causing the database engine to receive via the query engine a query in a database language that has been extended to include an entity type and an association, the query further comprising a path expression including a filter element suffix followed by nested projection clauses specifying a relationship between a specified entity and an associated entity;

causing the database engine to return to the query engine a query result set comprising data of the associated entity matching the nested projection clauses of the path expression,

wherein if the path expression includes an optional flattening symbol, the query result set comprises the data in a result set type structure that is flattened, and

wherein if the path expression omits the optional flattening symbol, the query result set comprises the data in the result set type structure that corresponds to the nested projection clauses; and

causing the database engine to store the data in the database according to the result set type structure.

7. A non-transitory computer readable storage medium as in claim 6 wherein the path expression further comprises a shorthand factoring out an association path prefix.

8. A non-transitory computer readable storage medium as in claim 6 wherein the language is SQL.

9. A non-transitory computer readable storage medium as in claim 8 wherein the query comprises SELECT and FROM clauses presented in a revised order.

10. A non-transitory computer readable storage medium as in claim 6 wherein the database comprises an in memory database.

11. A computer system comprising:

one or more processors;

a software program stored on a non-transitory computer readable storage medium and configured to cause the one or more processors to;

provide in a database layer, a database engine and a database comprising data organized according to a relational model;

provide in an application layer, an entity relationship data model and a query engine in communication with the database engine;

cause the database engine to receive via the query engine a query in a database language that has been extended to include an entity type and an association, the query further comprising a path expression including a filter element suffix followed by nested projection clauses specifying a relationship between a specified entity and an associated entity;

cause the database engine to return to the query engine a query result set comprising data of the associated entity matching the nested projection clauses of the path expression,

wherein if the path expression includes an optional flattening symbol, the query result set comprises the data in a result set type structure that is flattened, and

wherein if the path expression omits the optional flattening symbol, the query result set comprises the data in the result set type structure that corresponds to the nested projection clauses; and

cause the database engine to store the data in the database according to the result set type structure.

12. A computer system as in claim 11 wherein the path expression further comprises a shorthand factoring out an association path prefix.

13. A computer system as in claim 11 wherein the language is SQL.

14. A computer system as in claim 13 wherein the query comprises SELECT and FROM clauses presented in a revised order.

15. A computer system as in claim 11 wherein the database comprises an in memory database.