Embodiments relate to databases, and in particular, to extending a database language to accommodate calculation expressions in enhanced data models.
Unless otherwise indicated herein, the approaches described in this section are not prior art to the claims in this application and are not admitted to be prior art by inclusion in this section.
Many database structures rely upon Structured Query Language (SQL) as the standard approach to define, read, and manipulate data within a database. At a low level, such a database may employ fundamental data definition and processing that is based upon a relational model. In particular, a data definition defines a data type with sufficient metadata being associated therewith. A data definition may also involve definition of a database structure such as columns and tables. Many database structures rely upon Structured Query Language (SQL) as the standard database language to define, read, and manipulate data within a database. In its standard form, SQL itself reflects the basic relational model of the database. Various other types of applications (e.g. toolsets) are constructed by developers to allow consumers to interact with the database in an efficient and intuitive manner. Such applications are typically provided in an application layer overlying the database.
The overlying applications, such as consumer technology and toolsets provided by developers, may introduce higher-level models, e.g., entity-relationship models (ERMs) in order to contribute semantics and ease consumption by the user. In particular, a plain data model on the SQL level only contains the requisite information to process data on the SQL-level. Adding more information in a declarative fashion provides potential for higher-level engines to offload work from developers by contributing more semantics. Adding more information in a declarative fashion can also make data models more comprehensible, thereby easing their consumption by users.
One example of a higher-level model is an OData Entity Data Model (EDM). In particular, OData is a web protocol standard providing platform-agnostic interoperability for querying and updating data. OData leverages web technologies such as HTTP, Atom Publishing Protocol (AtomPub), and JSON (JavaScript Object Notation) in order to provide access to information from a variety of applications. The simplicity and extensibility of OData can provide consumers with a predictable interface for querying a variety of data sources.
Other examples of higher level models may include the Semantic Layer in the Business Intelligence (BI) platform of SAP AG, in Walldorf, Germany, Java Persistence API (JPA) and enterprise objects in Java, or the business objects frameworks in Advanced Business Application Programming language (ABAP). Also, the River programming model and the River Design Language (RDL) of the River Application Development framework for SAP AG, in Walldorf, Germany, are based upon entities linked by relationships.
Even though those higher-level models may share many commonalities, the individual information cannot be shared across stacks. That is, the higher-level models mentioned above contribute essentially the same kind of additional information, yet that information is provided in different ways that interfere with its being shared across higher level models (e.g., between an OData EDM and an ERM created using RDL).
This situation results in a fragmented environment, with information unable to be shared between applications. To cope with this fragmentation, redundant information is provided with application developers and customers contributing the same essential information in multiple forms, thereby undesirably increasing overhead.
Furthermore, while the developers of consumer technologies may have some knowledge of SQL, they are generally not experts in complex SQL programming.
It is also noted that when retrieving data from tables in a database, new data attributes may be calculated transiently based on the values of other attributes, utilizing a calculation expression. In order to interact with a relational database utilizing conventional SQL, such calculation expressions may need to be redundantly placed in many locations within the code. Thus, there is a need for an improved language for interacting with relational databases.
A database language (e.g. SQL) is extended to define a transient field whose value is derived from data stored in a database. The value of the transient field is calculated by the database engine for consumption as needed by the application layer, for example as part of a particular data model. The value of the transient field may not be materialized as a persistent field in the underlying database table, or may be selectively materialized based upon a heuristic or hint. Certain embodiments may implement the transient field as a basic elaboration on standard SQL utilizing an identifier. Some embodiments may implement the transient field as an entity where SQL has been extended to accommodate concepts of a higher-level Entity Relationship Model (ERM). The use of constants and/or floating values (e.g. a current time) in connection with transient fields, is also disclosed.
An embodiment of a computer-implemented method comprises providing a database comprising data of a first type and data of a second type, and causing a database engine to receive from a query engine, a query in a database language defining a transient field. The database engine is caused to communicate with the database to generate a query result from the query, wherein the database engine fills in a value for the transient field derived at least in part from the data of the second type. The database engine is caused to communicate the query result to the query engine for display to a user.
An embodiment of a non-transitory computer readable storage medium embodies a computer program for performing a method comprising providing a database comprising data of a first type and data of a second type, and causing a database engine to receive from a query engine, a query in a database language defining a transient field. The database engine is caused to communicate with the database to generate a query result from the query, wherein the database engine fills in a value for the transient field derived at least in part from the data of the second type. The database engine is caused to communicate the query result to the query engine for display to a user.
An embodiment of a computer system comprises one or more processors and a software program executable on said computer system. The software program is configured to provide a database comprising data of a first type and data of a second type, and cause a database engine to receive from a query engine, a query in a database language defining a transient field. The database engine is caused to communicate with the database to generate a query result from the query, wherein the database engine fills in a value for the transient field derived at least in part from the data of the second type. The database engine is caused to communicate the query result to the query engine for display to a user.
In an embodiment, the database language comprises SQL.
According to some embodiments, the transient field is defined by an identifier in the database language.
In various embodiments, the database language is extended to include an entity, and the transient field is defined by the entity.
In certain embodiments, the value is not materialized in a persistent field in the database.
According to some embodiments, the value is materialized in a persistent field of the database based upon a heuristic or a hint.
The following detailed description and accompanying drawings provide a better understanding of the nature and advantages of the present invention.
Described herein are techniques for extending a database language to accommodate transient fields for calculation expressions in enhanced data models. In the following description, for purposes of explanation, numerous examples and specific details are set forth in order to provide a thorough understanding of the present invention. It will be evident, however, to one skilled in the art that the present invention as defined by the claims may include some or all of the features in these examples alone or in combination with other features described below, and may further include modifications and equivalents of the features and concepts described herein.
According to embodiments, a database language may be extended to include a transient field whose value is derived from data stored in a database. The value of the transient field is not stored in an underlying database table. Instead, the value of the transient field is calculated by a database engine for consumption as needed by an application layer, for example in a calculation expression of a particular data model.
Conventionally, when retrieving data from tables in a database, new data attributes may be calculated transiently based on the values of other attributes. An example of this is given below:
SELECT name, years(now-birthday) as age FROM Employee
However, there is a need to redundantly place the above expression in many different locations, i.e., wherever the definition of “Employee” is consumed.
In order to address this issue, embodiments introduce the concept of pre-defined calculated transient fields in data models. According to certain embodiments, such pre-defined calculated transient fields are implemented based on a data definition language (DDL). The DDL is used for defining semantically rich data models, including the data types, associated metadata, and database organization (e.g., columns and tables). According to some embodiments, the DDL may be extended to further enrich these data models through the use of entities and annotations.
The application layer 820 comprises a query engine 822 that is configured to communicate with the database engine 810. The application layer 820 further includes a data model 824 that organizes data of the underlying database 802, in ways that are useful to an end user 830. In this highly simplified example, the data model 824 may include a value that represents a combination of certain types of information present in the underlying database 802.
For example, the data model may include a value (Z), that represents the sum of different types of information (A, F) taken from the database. As disclosed herein, one of those types of information (F) may actually represent the combination of other pieces of information stored in the database (e.g., the difference between two values D and E actually stored in separate columns of the underlying database table).
In one conventional approach, this quantity (D-E) could be calculated in advance for each row of the table, with those values stored in a separate column within the database for later access if/when it is consumed by the application layer. Such a conventional approach, however, may require high memory consumption to store all of the combinations.
In another conventional approach, the application layer could include a separate calculation expression each time the quantity F (D-E) is consumed by the data model. As mentioned above, however, such a conventional approach involves redundant instances of the same calculation expression within the programming code of the application.
In contrast with such conventional approaches, embodiments extend the database language to include a transient field. In certain embodiments, the value of this transient field is calculated by the database engine 810 and populated during query execution time when consumed by the application layer 820. The calculated value of the transient field may then be communicated from the database engine 810 to the query engine 822, for display to the end user 830 as part of the data model 824 of the application layer 820. In the highly simplified view shown in
According to some embodiments, the value of the transient field is not materialized in a persistent field in the underlying database table. This non-materialization of the transient field is depicted by “- -” in
In other embodiments, value of the transient field may be materialized in a persistent field in the underlying database table 806. Such materialization of the transient field value is depicted by “#” in
It is noted that the value of the transient field need not be determined exclusively from values stored in the underlying database 802. In certain embodiments, the value of the transient field could be determined in part on the basis of a constant. Also, in certain embodiments the value of the transient field could be determined in part utilizing a floating value, such as for example, a present time.
In a third step 906, the database engine is caused to communicate with the database to generate a query result from the query. The database engine populates a value for the transient field derived at least in part from the data of the second type.
In a fourth step 908, the database engine is caused to communicate the query result to the query engine for display to a user.
In order to provide additional understanding regarding various embodiments, several examples are now provided below in connection with SQL as the database language. The first two examples illustrate the use of the transient field “grossamount”, in an application layer data model comprising a table including an “amount” and a “taxrate”. The first example shows an embodiment implementing the transient field as a basic elaboration upon standard SQL. The second example shows an embodiment implementing the transient field as an elaboration upon SQL that has been extended to accommodate concepts of a higher-level Entity Relationship Model (ERM).
In this first example, the transient field “grossamount” is implemented directly in SQL in order to create the underlying table Foo consumed by the application layer. This transient field is defined in the SQL by an identifier (“TRANSIENT”). This transient field is determined from the values “amount” and “taxrate” present in the underlying database table, but is not stored (i.e. as indicated below with the shorthand “-----”) in that underlying database table.
In this example the transient field “grossamount” is implemented as an extension of SQL that accommodates aspects of a higher-level Entity Relationship Model (ERM). Such an extension is described in detail below in connection with
According to specific embodiments, a DDL such as in SQL may be extended to introduce the “=” expression in order to create a pre-defined calculated transient field. In particular, the syntax of element definitions is enhanced to allow specifying calculated fields as follows:
The value of such a calculated field is calculated through the given expression at runtime. Such fields are read-only in a sense that no other values can be written to them.
The following corresponds to implementation of a transient field utilizing SQL extended to accommodate features of an entity-relationship model. In particular, this second example shows definition of an entity foo which includes the transient field “grossAmount”.
Some implementations for calculated transient fields, may simply expand them in queries with the calculation expression. For example, given the following definition of a calculated field age:
the following query:
SELECT age from Person;
would be expanded to an expression resembling the following:
SELECT years (now-birthday) as age from Person;
In this example, additional information beyond that specifically stored in an underlying database, is relied upon. In particular, the floating value “now” specifies the present time, a quantity which is not specifically stored in the underlying database table.
Users can rely on this understanding of the behavior of a calculated field, as if they were transient fields populated during query execution time. Yet, various implementations may internally map this behavior to more appropriate strategies. One example is having the calculated transient values actually be materialized in persistent fields in the underlying tables, based on heuristics or hints.
In one particular example, ‘hints’ would be directives added by the developer to the field's declaration in the data model. Such a ‘hint’ could be used to determine an implementation to materialize a calculated field as a persistent field.
An overview of DDL syntax in SQL is provided below. The following shows top-level syntax elements of the DDL in SQL extended according to an embodiment as described herein.
Certain embodiments may allow for the inference of type. Specifically, under certain circumstances explicit type declarations, for example [“;” AssignedType], may be omitted for constants and calculated fields. This may be done if, and only if, a type (including relevant details such as dimension of a string; precision/scale of a decimal) can unambiguously be inferred from a given expression.
Such an inference would likely not be available for string types used in entity definitions because the maximum length parameter for the string could not be inferred.
Certain embodiments may also allow constants to be defined. In particular, constants can be defined using the same syntax as for calculated transient fields, prepended with the modifier [const] as expressed in the following syntax enhancement:
The modifier “const” turns an element definition into a declaration of an alias name for a literal expression specified via “=” Expression. The element name can be used later on in a symbolic, compiler-checked way wherever a literal value can be put in in DDL, Query Language (QL) and Expression Language (EL).
It is noted that constants do not show up in persistence or in runtime structures as they are merely symbols used by the compiler. No value can be assigned at runtime.
SQL Extended to Accommodate ERMs
Described herein are techniques for extending a relational model-based database language (e.g., SQL), to accommodate higher level entity-relationship models. In the following description, for purposes of explanation, numerous examples and specific details are set forth in order to provide a thorough understanding of the present invention. It will be evident, however, to one skilled in the art that the present invention as defined by the claims may include some or all of the features in these examples alone or in combination with other features described below, and may further include modifications and equivalents of the features and concepts described herein.
A lower layer 106 of the database system 100 comprises calculation logic 108 that is designed to interact with the data 105 itself. Such calculation logic 108 may be performed by various engines (e.g., SQL engine, calculation engine, SQL script) in order to provide basic data definition and processing based on the relational model. Such basic data definition can include defining of data types making up the database, associated metadata, and the database structure (e.g., columns, tables). The lower layer 106 of the database system may include SQL script 110, as well as data structures such as tables 112, views 114, and calculation views 116.
The embodiment presented in
Further, while the embodiment presented in
An application layer 118, overlying the calculation logic 108 of the database system 100, comprises control flow logic 120. The control flow logic 120 may be implemented utilizing River Definition Language (RDL) 122 and JavaScript (JS) 124 to reference model concepts such as entities and relationships that are not reflected in basic SQL. This control flow logic 120 may further comprise common languages for defining and consuming data across different containers (e.g., native, ABAP, Java).
As shown in
In particular, the CDS component 130 implements higher level Domain Specific Languages (DSLs) and services based on an entity-relationship model (ERM). The Data Definition Language (DDL) 230 is used for defining semantically rich data models, including the data types, associated metadata, and database organization (e.g., columns and tables). As mentioned throughout, according to embodiments, the DDL may be extended to further enrich these data models through the use of entities and annotations.
The Query Language (QL) 232 is used to conveniently and efficiently reading data based on data models. It is also used to define views within data models. The role of the QL and its relation to the DDL is further illustrated in connection with
The Expression Language (EL) 234 is used to specify calculated fields, default values, constraints, etc. within queries. These calculated fields, default values, and constraints may be specified as well as for elements in data models.
Other elements of the CDS component 130 can include Data Manipulation Language (DML) 236 and a Data Control Language (DCL) 237 that helps in controlling access to data.
Embodiments as described herein may distinguish between the domain-specific languages DDL, QL, and EL as members of a language family. This approach fosters considerations such as modular design, incremental implementation, and reuse.
A consistent language experience across the members of the family of
Utilization of application level domain language(s) as has been described above, can offer certain benefits. One possible benefit is that the application domain level language can avoid the use of “inefficient” and error-prone code.
Take, for example, the following simple data model describing employee information:
Under some circumstances, it may be desired to write a query statement as follows: SELECT id, name, homeAddress.zipCode FROM Employee WHERE . . . .
Within that sample snippet, path expressions along relationships are used to fetch data from an associated entity. In the simple data model above, the above query statement is equivalent to the following standard SQL statement:
This statement, however, may already be too complex for many application developers. Thus, code patterns similar to that given below, may be used in some pseudo languages:
There are several issues with the code presented immediately above. One issue is the use of an imperative coding style with loops in loops, resulting in 1+n queries being executed or too much data being fetched with SELECT * statement.
The above code represents only a relatively simple case. A more complex case is found in the following example:
The preceding cases illustrate the importance of increasing expressiveness of the languages used in application development (here, the query language). This allows the intent of application developers to be captured, rather than being buried under substantial volumes of imperative boilerplate coding.
Such expressiveness is in turn is fundamental to having optimizations applied by the query engine (in a manner analogous to functional programming vs. imperative programming). This can affect system characteristics, such as its overall performance and scalability. Further, a language's ability to allow developers to draft concise and comprehensive code can increase developer productivity. It can also reduce the risk of mistakes and also enhance readability, and thus maintainability of the code.
In order to write concise and readable query statements, it is desirable to enrich the data definitions with sufficient metadata (e.g., about associations, semantic types, etc.) Accordingly, embodiments seek to extend the DDL to define that information, and seek to extend the QL to leverage such definitions.
DDL and QL are declarative, domain-specific languages providing developers with concise ways to express their models and queries. Certain concepts may originate from entity-relationship modeling (ERM). By adding native support for such concepts in the underlying engine of the database, embodiments avoid the impedance mismatch induced by the translation of conceptual models based on ERM, into implementations based upon a plain relational model. In particular, writing concise and comprehensive code reduces risks of mistakes and increases readability and maintainability.
Moreover, as the concepts of entity-relationship models may lie at the core of many higher-level models, embodiments are able to capture the semantics of data models created (e.g. in RDL), and share those semantics with database modelers, and/or ABAP or Java consumers. This reduces fragmentation and the loss of semantics.
In addition, since ERM is also the chosen basis for technologies like OData EDM, embodiments can facilitate mapping entities and views to OData entity sets.
Embodiments may employ a functional approach that is based on standard SQL. In particular, the comprehensive, domain-specific nature of DDL and QL allows capturing the intent of application developers, avoiding a lack of clarity regarding that intent which can result from large volumes of imperative boilerplate coding. This follows the principles of functional programming and may be important for optimizations.
The functional approach may be inherited from SQL. A SQL SELECT statement declares which subset of an overall data model is of interest as projections and selections. It may be left to the query engine to determine optimal execution, including parallelizing as appropriate.
In contrast with imperative object traversion patterns, embodiments can speed up many data retrieval use cases. While many of those retrieval cases are not individually expensive, the cumulative impact of this streamlining can have significant impacts on scalability, as it affects all requests over a long period of time.
Embodiments address some of the complexity offered by standard SQL to typical application developers by raising the basis of SQL from plain relational models to the level of conceptual models. This is done by providing native support for ERM in the database system. In this manner, the use of SQL may be reestablished for most application developers, not only for those with the SQL expertise for specific optimization tasks.
Embodiments employ associations in DDL. Specifically, the DDL allows definition of data models as entity-relationship models on a semantically rich level that is close to actual conceptual thought. To achieve this over the conventional relational model of standard SQL, certain concepts are added.
Entities are structured types with an underlying persistency and a unique key 402. Structured types are records of named and typed elements. An entity key is formed of a subset of the elements of the entity that uniquely identify instances. Views are entities defined by a query, which essentially defines a projection on underlying entities.
Another concept underlying entities as described herein, involves employing associations 404 on a conceptual level. This approach contrasts with the conventional use of hand-managed foreign keys.
Associations define relationships between entities. They are specified by adding an element with an association type, to a source entity 408 that points to a target entity 410. As shown in the
The association may be complemented by optional further information (e.g., regarding cardinality, which keys to use, additional filter conditions) up to a complete JOIN condition. According to embodiments, the clause-based syntax style of standard SQL may be adopted for specifying the various parameters without sacrificing readability.
In addition, the extended DDL works with Custom-defined Types instead of being limited to primitive types only. The extended DDL may also add some other enhancements, such as annotations to enrich the data models with additional metadata, constraints, or calculated fields.
In a second step 504, a database engine is provided in communication with a database utilizing a language describing the relational model. In a third step 506, an application is provided comprising an entity-relationship model (ERM) including a first entity, a second entity, and a relationship between the first entity and the second entity.
In a fourth step 508, a query engine of the application communicates a query to the database engine utilizing a language extension providing the entity and relationship components of the ERM. The language extension may comprise a first structured entity type including a first key and indicating the first entity, a second structured entity type including a second key and indicating the second entity, and a third structured association type reflecting the relationship. The association type may be complemented with further additional information.
In a fifth step 510, the database engine returns a query result to the query engine based upon the language extension.
Some examples of extension of the SQL database language to provide entities and associations of ERMs, are now given below:
For specifying syntax, embodiments may use a derivate of the Backus Naur Form (BNF) family of metasyntax notations used to express a context-free grammar, and which can be relied upon to make a formal description of a computer language. The basic constructs may be summarized as follows.
keyword
Syntax for SQL extended to include entities and associations as described herein, may be described as follows.
From DDL perspective, association is a new primitive type that is specified with the type name “Association”, followed by several parameter clauses to specify requisite metadata. These parameter clauses are as follows.
Cardinality allows specifying the relationship's cardinality in the form of [min . . . max], with max=* denoting infinity and “[ ]” as a shorthand for [0 . . . *]. As a default, if omitted [0 . . . 1] is used as the default cardinality. An example is:
Association[ ] to Address via backlink owner;
To targetEntity specifies the association's target entity. A qualified name is expected, referring to another entity (incl. views). Specifying the target is mandatory—there is no default.
{foreignKeys} allows specifying a combination of alternative key elements in the target entity, to be used to establish the foreign key relationship. Where a key element is in a substructure on the target side, an alias name is to be specified. Further details are provided below regarding associations represented as foreign key relationships.
If omitted, the target entity's designated primary key elements are used. The following are examples:
Another parameter clause is VIA backlink: reverseKeys. For 1:m associations, it is mandatory to specify target elements, which are expected to be a key combination matching the source's primary keys or an association referring to the source entity. An example is:
Association to Address via backlink owner;
Another parameter clause is VIA entity: entityName. For m:m associations, it is mandatory to specify a link table's entity name. That name can either refer to a defined entity or a new entity will be created as follows:
If the data model contains an explicit definition of the link table entity, that entity must adhere to the template shown above. It can, in addition, add other elements. An example is given below:
The WHERE filterClause allows specifying additional filter conditions that are to be combined with the join conditions. This can be especially relevant in combination with VIA backlink or entity clauses. Depending on the filterCondition this can reduce a base :m relationship to one with a :1 cardinality. An example is given below:
Association to Address[0 . . . 1] via backlink owner where kind=home;
The ON filterClause allows fully specifying an arbitrary JOIN condition, which can be any standard SQL filter expression. Using this option results in the respective association being user-managed. That is, no foreign key elements/fields are created automatically. The developer is expected to explicitly manage the foreign key elements, including filling them with appropriate foreign key values in write scenarios. An example is given below:
Association to Address on owner=this;
Element names showing up in VIA, WHERE, and ON clauses, are resolved within the scope of the target entity's type structure. Siblings can be referred to by prefixing an element with a “.”. Elements from the scope above can be referred to by prefixing an element with “ . . . ”, etc.
In addition, the outer entity's top-level scope can be referred through the pseudo variable “this”, described further below in connection with Pseudo Variables in Queries (QL).
According to embodiments, associations are represented as foreign key relationships. In the relational model, associations are mapped to foreign key relationships. The foreign key elements are usually created automatically as described in the following sections. In particular, an element with association type is represented as a nested structure type containing foreign key elements corresponding to the target entity's primary key elements—i.e., having the same names and types. The following are examples of definitions which may be given:
In this example, the association elements would implicitly be defined with a nested structure type containing foreign key elements in the :1 cases (plus additional metadata about the association) as follows:
Following the rules for mapping structured types to the relational model as specified above, the underlying table would be created:
Rules for representing associations in the persistence model may apply, as indicated in the table below:
Consistent with the approach in SQL, no plausibility checks are enforced (e.g. checking whether target key elements specified in {foreignKeys} fulfill the uniqueness requirements). Also, no implicit referential integrity checks are enforced at runtime.
According to embodiments, associations may be in custom-defined types. As associations are special types, they can principally be defined not only for elements in entity definitions, but in type definitions in general. For example, the following definition of the association Amount.currency is valid DDL content:
An actual relationship between entities is established when using the type Amount for an element within an entity definition, as shown in the following:
The code shown above essentially indicates that the entity Employee has two associations—one association is to Address and another association is to Currency within its salary element.
Associations in custom-defined types may only be supported for a simple “to-one” relationship with a foreign key on the source side. That is, associations with via backlink or via entity clauses may not be supported for elements in custom-defined types.
Associations in Query Language (QL) are discussed below.
Querying Associations with :m Cardinality
Resolving associations or compositions with 1:m cardinality using path expressions or nested projection clauses with the flattening operator “.” in place results in flat result sets with duplicate entries for the 1: side, which is in line with standard SQL JOINs and the relational model.
As examples, in the following queries, “addresses” refers to an association with “to-many” cardinality [0 . . . *]:
The result sets for the example queries above, are shown below, each with the same value for name repeated/duplicated for each found entry on the :m Address side:
Embodiments also allow the return of ‘Deep’ Result Sets. Specifically, in addition to the standard flattening behavior, the introduction of nested projection clauses and structured result sets principally allows expression of ‘deep’ queries along :m associations. These deep queries return ‘real deep’ result sets having the 1: sides elements on a top level, with nested tables/sets for the :m sides.
For example, the deep query:
SELECT name, addresses {zipCode, city} FROM Employee;
would be expected to return a result set with a nested collection as shown below:
Such deep querying may provide certain benefits. One possible benefit is to allow retrieving larger structures through a single query.
Currently, in the absence of deep querying, such larger structures may frequently be obtained in a brute-force approach, through 1+n queries with n being the number of records returned by a 1: side query. This is detrimental to performance, particularly if such a query spans several levels of to-many associations.
While the other extensions can be realized by translating to standard SQL queries, this one requires adding special support deep within the query engine. The absence of such support may preclude using to-many associations in the non-flattened way. This is discussed further below in the associations of FROM clauses, regarding how association trees can be traversed.
Associations in WHERE Clauses
Associations can arise not only in projection clauses but also in filter conditions in WHERE clauses. Respective comparison operators may be enhanced to support associations, as depicted in the following examples:
Several issues arising within the examples immediately above, may be worthy of note. In connection with:
ad 1,2: A record literal can be passed to a comparison with an association, with elements that match the combination of the foreign keys.
ad 3: Support for Association type in QL includes automatic coercions of typed scalars or string representations thereof to single-key associations.
ad 4: One can also refer to the individual key values using standard path expressions.
ad 5ff: Other SQL comparison operators can be used, such as LIKE, IN, IS NULL, . . . .
ad 8: It can be combined with XPath-like filter expressions.
ad 9: It can be combined with compare associations, provided they are assignable.
The above provides just a few examples to give the idea. In general, every condition that is possible with standard SQL expressions shall be possible to do with associations as well, including sub queries with exists and not exists, etc.
Associations in FROM Clauses
Embodiments may also allow associations in FROM clauses. Specifically, host languages may provide support for representing associations as typed variables or elements. This is described below in connection with association types in host languages.
Accordingly, one can traverse along associations, as shown in the following examples (in some pseudo language):
The expression this=<an association> can be used. The comparison this=<an association> can be retrieve an entity by a given association. The pseudo variable this is always an alias for the entity given in the FROM clause. So the statement above actually resolves to:
SELECT * FROM Address this WHERE this=daniel.homeAddress;
The comparison this=<an association> compares a queried entity with a given association—the association must be of type association to <queried entity> [ . . . ]. This expands to a WHERE clause corresponding to the ON condition resolved from the association. In this case it would actually resolve to:
Embodiments may also allow the use of SELECT from association. Specifically, association-traversal code patterns like the one below are frequently seen:
SELECT * from Address WHERE this=daniel.homeAddress;
An association in general, and a programming language variable with association type support in particular, carries all information about a target record—essentially providing information as to which entity goes with which key. Thus, equivalent to the query above, embodiments allow the shorthand below for traversing associations:
SELECT * from daniel.homeAddress;
In general, a query statement of the form SELECT . . . from <someAssociation> expands to:
Here, <targetEntity> signifies the metadata associated with the association corresponding to the target entity specified in the association's declaration using the ON targetEntity clause.
JOINs Declare Ad-hoc Associations
Embodiments allow JOINs to declare ad-hoc associations. In the case of a missing association, the standard JOIN <target> ON <join condition> clauses as introduced in SQL-92 are still supported, which align with the extensions introduced above, as they naturally introduce associations in an ad-hoc fashion.
For example, in the data model given above, the entity Employee has an association homeAddress, but is lacking a similar association for businessAddress, which can be compensated for using a standard JOIN clause as follows:
The expression may follow the syntax below:
Other syntax is as discussed above in connection with associations in DDL.
JOIN clauses fit easily into the extensions in DDL and QL. JOIN clauses can be interpreted as an ad-hoc definition of missing associations.
In the example immediately above, the association businessAddress is added. This result is recognized if the projection clause of the example above is compared to that of the query applied to the domain model if the association were in place (below):
Embodiments also allow the use of simplified JOIN clauses. In particular, following the observation that JOINs essentially declare ad-hoc associations, embodiments allow JOINs to be declared using the same clauses that are used to declare associations in DDL. Given this, the above example can be written more easily as follows:
An example computer system 710 is illustrated in
Computer system 710 may be coupled via bus 705 to a display 712, such as a cathode ray tube (CRT) or liquid crystal display (LCD), for displaying information to a computer user. An input device 711 such as a keyboard and/or mouse is coupled to bus 705 for communicating information and command selections from the user to processor 701. The combination of these components allows the user to communicate with the system. In some systems, bus 705 may be divided into multiple specialized buses.
Computer system 710 also includes a network interface 704 coupled with bus 705. Network interface 704 may provide two-way data communication between computer system 710 and the local network 720. The network interface 704 may be a digital subscriber line (DSL) or a modem to provide data communication connection over a telephone line, for example. Another example of the network interface is a local area network (LAN) card to provide a data communication connection to a compatible LAN. Wireless links are another example. In any such implementation, network interface 704 sends and receives electrical, electromagnetic, or optical signals that carry digital data streams representing various types of information.
Computer system 710 can send and receive information, including messages or other interface actions, through the network interface 704 across a local network 720, an Intranet, or the Internet 730. For a local network, computer system (710 may communicate with a plurality of other computer machines, such as server 715. Accordingly, computer system 710 and server computer systems represented by server 715 may form a cloud computing network, which may be programmed with processes described herein. In the Internet example, software components or services may reside on multiple different computer systems 710 or servers 731-735 across the network. The processes described above may be implemented on one or more servers, for example. A server 731 may transmit actions or messages from one component, through Internet 730, local network 720, and network interface 704 to a component on computer system 710. The software components and processes described above may be implemented on any computer system and send and/or receive information across a network, for example.
The above description illustrates various embodiments of the present invention along with examples of how aspects of the present invention may be implemented. The above examples and embodiments should not be deemed to be the only embodiments, and are presented to illustrate the flexibility and advantages of the present invention as defined by the following claims. Based on the above disclosure and the following claims, other arrangements, embodiments, implementations and equivalents will be evident to those skilled in the art and may be employed without departing from the spirit and scope of the invention as defined by the claims.
The instant application is a continuation of U.S. nonprovisional patent application Ser. No. 14/020,703 filed Sep. 6, 2013, which is incorporated by reference in its entirety herein for all purposes.
Number | Name | Date | Kind |
---|---|---|---|
5761493 | Blakeley et al. | Jun 1998 | A |
5956706 | Carey et al. | Sep 1999 | A |
5999908 | Abelow | Dec 1999 | A |
6038558 | Powers et al. | Mar 2000 | A |
6195709 | Gupner et al. | Feb 2001 | B1 |
6480839 | Whittington | Nov 2002 | B1 |
6516310 | Paulley | Feb 2003 | B2 |
6560598 | Delo et al. | May 2003 | B2 |
6567798 | Hollberg et al. | May 2003 | B1 |
6631382 | Kouchi et al. | Oct 2003 | B1 |
6707904 | Judkins | Mar 2004 | B1 |
6732084 | Kabra et al. | May 2004 | B1 |
6799184 | Bhatt et al. | Sep 2004 | B2 |
6836777 | Holle | Dec 2004 | B2 |
6898603 | Petculescu et al. | May 2005 | B1 |
6938041 | Brandow et al. | Aug 2005 | B1 |
6957214 | Silberberg | Oct 2005 | B2 |
6996568 | Bedell et al. | Feb 2006 | B1 |
7107497 | McGuire et al. | Sep 2006 | B2 |
7194744 | Srivastava et al. | Mar 2007 | B2 |
7225197 | Lissar et al. | May 2007 | B2 |
7290181 | D'Angelo et al. | Oct 2007 | B2 |
7302447 | Dettinger et al. | Nov 2007 | B2 |
7305414 | Manikutty et al. | Dec 2007 | B2 |
7340451 | Sacco | Mar 2008 | B2 |
7380169 | Fossum et al. | May 2008 | B2 |
7398530 | Parla et al. | Jul 2008 | B1 |
7421448 | Spork | Sep 2008 | B2 |
7434230 | Harold et al. | Oct 2008 | B2 |
7481368 | Wang et al. | Jan 2009 | B2 |
7505983 | Wildhagen et al. | Mar 2009 | B2 |
7523090 | Sundman et al. | Apr 2009 | B1 |
7631012 | Harvey | Dec 2009 | B2 |
7640357 | Kirov et al. | Dec 2009 | B2 |
7653828 | Kostadinov et al. | Jan 2010 | B2 |
7672960 | Anonsen | Mar 2010 | B2 |
7680782 | Chen et al. | Mar 2010 | B2 |
7689612 | Helsen et al. | Mar 2010 | B2 |
7693819 | Stoychev | Apr 2010 | B2 |
7761481 | Gaurav et al. | Jul 2010 | B2 |
7765222 | Styles et al. | Jul 2010 | B2 |
7765224 | Li et al. | Jul 2010 | B2 |
7788241 | Cheng et al. | Aug 2010 | B2 |
7805433 | Dickerman et al. | Sep 2010 | B2 |
7818754 | Morris et al. | Oct 2010 | B2 |
7836070 | Forstmann | Nov 2010 | B2 |
7873605 | Bhatacharyya | Jan 2011 | B2 |
7885840 | Sadiq et al. | Feb 2011 | B2 |
7895226 | Koch et al. | Feb 2011 | B2 |
7912820 | Garden | Mar 2011 | B2 |
7917549 | Arazi | Mar 2011 | B2 |
7937401 | Pasumansky et al. | May 2011 | B2 |
7970823 | Moeller et al. | Jun 2011 | B2 |
7975254 | Gilboa | Jul 2011 | B2 |
7996443 | Nori et al. | Aug 2011 | B2 |
8005850 | Walther et al. | Aug 2011 | B2 |
8010521 | Kissner et al. | Aug 2011 | B2 |
8065323 | Sallakonda et al. | Nov 2011 | B2 |
8069184 | Becker et al. | Nov 2011 | B2 |
8078643 | Mush et al. | Dec 2011 | B2 |
8122009 | Dettinger et al. | Feb 2012 | B2 |
8146103 | Schmidt et al. | Mar 2012 | B2 |
8185508 | Vemuri et al. | May 2012 | B2 |
8191081 | Schmidt et al. | May 2012 | B2 |
8209280 | Kearney et al. | Jun 2012 | B2 |
8214877 | Grimes et al. | Jul 2012 | B1 |
8219919 | Marring et al. | Jul 2012 | B2 |
8250094 | Skaria et al. | Aug 2012 | B2 |
8255368 | Cox | Aug 2012 | B2 |
8281283 | Speth et al. | Oct 2012 | B2 |
8327260 | Bays et al. | Oct 2012 | B2 |
8321478 | Fong | Nov 2012 | B2 |
8347207 | Borgsmidt et al. | Jan 2013 | B2 |
8364300 | Pouyez et al. | Jan 2013 | B2 |
8364724 | Stolle et al. | Jan 2013 | B2 |
8370400 | Brunswig et al. | Feb 2013 | B2 |
8375041 | Webster et al. | Feb 2013 | B2 |
8386916 | LeBrazidec et al. | Feb 2013 | B2 |
8407215 | Sheedy et al. | Mar 2013 | B2 |
8407237 | Kudryavtsev et al. | Mar 2013 | B1 |
8407262 | Hsu et al. | Mar 2013 | B2 |
8407309 | Feldman et al. | Mar 2013 | B1 |
8412673 | Weissenberger et al. | Apr 2013 | B2 |
8417732 | Rapp | Apr 2013 | B2 |
8429176 | Sigurbjornsson et al. | Apr 2013 | B2 |
8473506 | Sedlar et al. | Jun 2013 | B2 |
8478515 | Foucher et al. | Jul 2013 | B1 |
8478850 | Delany et al. | Jul 2013 | B2 |
8484210 | Loh et al. | Jul 2013 | B2 |
8489649 | Yalamanchi et al. | Jul 2013 | B2 |
8504522 | Wu et al. | Aug 2013 | B2 |
8504568 | Chandrasekhara et al. | Aug 2013 | B2 |
8505032 | Craddock et al. | Aug 2013 | B2 |
8510296 | Fan et al. | Aug 2013 | B2 |
8515982 | Hickman et al. | Aug 2013 | B1 |
8805875 | Bawcom et al. | Aug 2014 | B1 |
8849771 | Berg-Sonne | Sep 2014 | B2 |
9047334 | Cheriton | Jun 2015 | B1 |
9182956 | Zalmanovici | Nov 2015 | B2 |
20020100014 | Iborra et al. | Jul 2002 | A1 |
20020138820 | Daly | Sep 2002 | A1 |
20030009649 | Martin et al. | Jan 2003 | A1 |
20030046671 | Bowen | Mar 2003 | A1 |
20030135850 | Miloushev et al. | Jul 2003 | A1 |
20030140036 | Belowsov | Jul 2003 | A1 |
20030145255 | Harty et al. | Jul 2003 | A1 |
20040117759 | Rippert, Jr. et al. | Jun 2004 | A1 |
20040122817 | Kaiser | Jun 2004 | A1 |
20040153435 | Gudbjartsson et al. | Aug 2004 | A1 |
20040249856 | Garden | Dec 2004 | A1 |
20050004904 | Kearney et al. | Jan 2005 | A1 |
20050010565 | Cushing et al. | Jan 2005 | A1 |
20050010581 | Doan | Jan 2005 | A1 |
20050065958 | Dettinger et al. | Mar 2005 | A1 |
20050187952 | Werner | Aug 2005 | A1 |
20050256889 | McConnell | Nov 2005 | A1 |
20050283459 | MacLennan et al. | Dec 2005 | A1 |
20060195460 | Nori et al. | Aug 2006 | A1 |
20060195476 | Nor et al. | Aug 2006 | A1 |
20060215448 | Ellis et al. | Sep 2006 | A1 |
20060224634 | Hahn et al. | Oct 2006 | A1 |
20060235834 | Blakeley et al. | Oct 2006 | A1 |
20060242104 | Ellis et al. | Oct 2006 | A1 |
20060259912 | Weinrich | Nov 2006 | A1 |
20070083572 | Bland et al. | Apr 2007 | A1 |
20070118501 | Yan | May 2007 | A1 |
20070236335 | Dionne et al. | Jun 2007 | A1 |
20070219976 | Muralidhar et al. | Sep 2007 | A1 |
20070226203 | Adya et al. | Sep 2007 | A1 |
20080065862 | Hansen et al. | Mar 2008 | A1 |
20080071799 | Evans et al. | Mar 2008 | A1 |
20080091691 | Tsuji | Apr 2008 | A1 |
20080120604 | Morris | May 2008 | A1 |
20080133530 | Wang et al. | Jun 2008 | A1 |
20080222159 | Aranha et al. | Sep 2008 | A1 |
20080301168 | Adler et al. | Dec 2008 | A1 |
20090240664 | Dinker et al. | Sep 2009 | A1 |
20090292730 | Li et al. | Nov 2009 | A1 |
20090319546 | Shaik | Dec 2009 | A1 |
20100082646 | Meek et al. | Apr 2010 | A1 |
20100114935 | Palo-Malouvier et al. | May 2010 | A1 |
20100131568 | Weinberg et al. | May 2010 | A1 |
20100241637 | Kissner et al. | May 2010 | A1 |
20100293523 | Ahadian et al. | Nov 2010 | A1 |
20100318499 | Arasu et al. | Dec 2010 | A1 |
20110154226 | Guertler et al. | Jun 2011 | A1 |
20110161371 | Thomson et al. | Jun 2011 | A1 |
20110225176 | Siegel et al. | Sep 2011 | A1 |
20110231454 | Mack | Sep 2011 | A1 |
20110238437 | Zhou et al. | Sep 2011 | A1 |
20110302212 | Agrawal et al. | Dec 2011 | A1 |
20120005190 | Faerber et al. | Jan 2012 | A1 |
20120016901 | Agarwal et al. | Jan 2012 | A1 |
20120054142 | Du et al. | Mar 2012 | A1 |
20120059802 | Daniello et al. | Mar 2012 | A1 |
20120109661 | Lueckhoff | May 2012 | A1 |
20120130942 | Dipper et al. | May 2012 | A1 |
20120131392 | Bendig | May 2012 | A1 |
20120136868 | Hammerschmidt et al. | May 2012 | A1 |
20120143810 | Berg-Sonne | Jun 2012 | A1 |
20120144383 | Mishra et al. | Jun 2012 | A1 |
20120215768 | Zellweger | Aug 2012 | A1 |
20120239987 | Chow | Sep 2012 | A1 |
20120265734 | Perez et al. | Oct 2012 | A1 |
20130110879 | Winternitz et al. | May 2013 | A1 |
20130111310 | deOliveira et al. | May 2013 | A1 |
20130117346 | Figus | May 2013 | A1 |
20130151560 | Zurek | Jun 2013 | A1 |
20130159354 | Heinzl et al. | Jun 2013 | A1 |
20130166602 | Brunswig et al. | Jun 2013 | A1 |
20130246355 | Nelson et al. | Sep 2013 | A1 |
20140149180 | Yaseen et al. | May 2014 | A1 |
20140245079 | Larson et al. | Aug 2014 | A1 |
20140258777 | Cheriton | Sep 2014 | A1 |
20140330916 | Xu et al. | Nov 2014 | A1 |
20140380266 | Bornhoevd et al. | Dec 2014 | A1 |
20150074069 | Baeuerle et al. | Mar 2015 | A1 |
20150074081 | Falter et al. | Mar 2015 | A1 |
20150074083 | Hutzel et al. | Mar 2015 | A1 |
20150074136 | Falter et al. | Mar 2015 | A1 |
20150074139 | Falter et al. | Mar 2015 | A1 |
20150074140 | Subramanian et al. | Mar 2015 | A1 |
20150074685 | Baeuerle et al. | Mar 2015 | A1 |
20150074686 | Baeuerle et al. | Mar 2015 | A1 |
Entry |
---|
Graphics: Display Method (SAP-SERM); SAP Library—BC Data Modeler; 2004; pp. 1-5. |
Abovesoft Utilities; www.AboveSoft.com; Sep. 2010; pp. 1-6. |
Ayers, Lonnie, PMP; SAP BW Data Modeling; SAP-BW Consulting, Inc.; pp. 1-18 Printed on Aug. 29, 2013. |
Heilman, Rich, HANA Product Management, SAP Labs LLC; Steyn, Werner, Customer Solution Adoption, SAP Labs, LLC; SAP HANA SQL Script Basics & Troubleshooting; Oct. 2012; pp. 1-47. |
Pattern: Fill Transient Attributes of Persistent Nodes; SAP; Jul. 6, 2013; p. 1. |
Ruby on Rails Guides, “Active Record Validations,” Aug. 8, 2013, downloaded from the Internet at <url>https://web.archive.org/web/20130808005838/http://guides.rubyonrails.org/active_record_validations.html on Aug. 27, 2015, pp. 1-37. |
Bhargava, “Concurrency control in database systems,” 1999, IEEE Transactions on Knowledge and Data Engineering, vol. 11, Issue 1, pp. 3-16. |
Paton et al., “Identification of database objects by key,” 2005, Advances in Object-Oriented Database Systems, vol. 334 of the series Lecture Notes in Computer Science, pp. 280-285. |
Farber et al., “SAP HANA database: data management for modem business applications,” 2011, ACM SIGMOD vol. 40 Issue 4, pp. 45-51. |
Number | Date | Country | |
---|---|---|---|
20160246858 A1 | Aug 2016 | US |
Number | Date | Country | |
---|---|---|---|
Parent | 14020703 | Sep 2013 | US |
Child | 15145559 | US |