Introduction

Overview

This documentation covers Structured Query Language (SQL) queries and expressions in EBX®. EBX® supports standard SQL queries to retrieve rows selected from one or more tables. Some EBX® SQL language features are extensions to the standard. Supported EBX® SQL syntax includes: table expressions (SELECT, FROM, WHERE, GROUP BY and HAVING clauses), DISTINCT, ORDER BY, LIMIT and OFFSET, combining queries (UNION [ALL]), and WITH (except RECURSIVE modifier).

The goal of this API is to provide to developers the ability to retrieve data from EBX® using a well-known standard.

EBX® SQL is accessible through Java APIs, especially from the class Query. The queries also support parameters. See Query.setParameter.

Queries can involve a single dataset or multiple datasets, see QueryBuilder to know more about how to create a query.

Mapping data model entities

The following section provides a detailed explanation about the mapping of the EBX® concepts into SQL.

Table (in data model)

EBX® tables are mapped naturally to SQL tables. In the data model, there can be more than one EBX® table with the same name. This ambiguity can occur when tables are in groups. To remove the ambiguity, use the full path of the table surrounded by double quotes (for example, "my_group/my_table" no longer conflicts with "other_group/my_table"). You can also use the entity name of the table, which is unique inside the data model. You can use the table name only if it does not collide with an entity name or another table name.

Fields

In SQL Standard, the structure of a table consists of one or more columns. Every element (including fields) whose parent is an EBX® table, is mapped to a column.

Groups

In SQL Standard, the structure of a table consists of one or more columns. Every element whose parent is an EBX® table is mapped to a column. This includes groups that are mapped to SQL columns as SQL structure types.

Associations

In SQL Standard, querying data among multiple tables is based on foreign keys and primary keys. These concepts in EBX® are similar to those in SQL. Therefore, joins between tables in SQL can also be done using EBX® foreign and primary keys.

Mapping data types

Handling data through SQL is highly dependent on its data type. For example, in predicates, columns can be compared only if they have the same SQL data type. The SQL data types are types according to the type in the data model.

Simple data types

Supported standard SQL data types

This table lists all of the simple types defined in the XML Schema that are supported by EBX®, along with their corresponding standard SQL types.

Extended simple types defined by EBX®

EBX® provides pre-defined simple data types. These types are defined by the internal schema common-1.0.xsd. Their definition is detailed in the section Extended simple types defined by EBX®

List (multi-valued) types

Lists are handled as SQL Arrays. Their corresponding Java class is java.util.List.

Note: in order to filter on a multivalued field, first it has to be "normalized" via the UNNEST SQL operator (be aware that this is not recommended in terms of performance and can be quite inefficient on large tables):

• SELECT T.s FROM myTable T, UNNEST(T.list) as L(item) WHERE L.item = 'test'

• SELECT p.name FROM Person p, UNNEST(p.addresses) AS ad(street, city) WHERE ad.city = 'Paris'

Complex types

Complex types are handled as SQL Structured types. Their corresponding Java class is Object[]. This applies to foreign keys (see below) and groups, because they are defined through complex types. Use the dot operator to access fields inside the SQL Structure types. For example, use address.street to access the field street of the field address, if it is a complex type. When you reference a sub-field of a complex type in a query, you must always use the table name or alias:

• SELECT customer.address.street FROM customer

• SELECT c.address.street FROM customer c

TableRef types

In EBX®, a table can have a primary key composed of multiple fields. Foreign keys are defined by a single field with the osd:tableRef declaration. The standard SQL syntax has been extended to extract the value of any targeted primary key field. In the Extraction of foreign keys example, the following SQL expressions are valid:

• tableA.fkb.id = 123

• YEAR(tableA.fkb.date) > 2018

A typical example of tableRef usage would be SQL joins:

SELECT * FROM employee JOIN department ON employee.fkDept.id = department.id

Or in case the referenced table has a composite primary key:

SELECT * FROM tableA JOIN tableB ON tableA.fkB.id1 = tableB.id1 AND tableA.fkB.id2 = tableB.id2

Analogously, we could perform the same JOIN via the string representation of the key, using FK_AS_STRING built-in function and $pk system column:

SELECT * FROM tableA JOIN tableB ON FK_AS_STRING(tableA.fkB) = tableB."$pk"

Metadata fields

EBX® SQL provides also access to some metadata information. These fields are not returned by a SQL statement, unless they are explicitly referenced. For example, "SELECT * FROM ..." does not return metadata fields, but "SELECT metadataField FROM ..." does.

System columns

Apart from the fields present in a table, EBX® SQL provides some extra system columns. These columns are not returned by a SQL statement, unless they are explicitly referenced. For example, "SELECT * FROM ..." does not return systems columns, but "SELECT systemColumnName FROM ..." does.

SQL syntax

Supported standard operators and functions

An operator is a reserved word or a character used primarily in a SQL statement's WHERE clause to perform operation(s), such as comparisons and arithmetic operations. These Operators are used to specify conditions in a SQL statement, and to serve as conjunctions for multiple conditions in a statement. EBX® supports most of the SQL standard operators and functions. Some functions and operators can have optional parameters: they are surrounded by square brackets in the documentation. Generally, there are five types of operators and functions:

• Comparison operators and functions

• Arithmetic operators and functions

• Logical operators

• String operators and functions

• Date and time functions

The following table lists all of the operators' associativity and precedence, highest to lowest.

Escaping identifiers

In the following cases, the identifier must be escaped by using double quotes:

• when using the absolute path to identify a table (for example, "/root/myTable").

• when the field to identify is a reserved word (for example, "user", "order").

• when referring to a system column with a table alias (for example, t."$adaptation", t."$pk").

• when referring to a metadata field (for example, t."ebx-metadata"."system"."creation_time").

The following example shows a query to illustrate all cases:

SELECT t."user", t."$pk" FROM "/root/myTable" t WHERE t."order" = 1

Explain plan

EBX® SQL supports EXPLAIN PLAN FOR ... syntax to get the plan information of a query.

The result is similar to Query.explain.

Example: EXPLAIN PLAN FOR SELECT id FROM myTable

Limitations and performance guidelines

• Certain internal join optimizations do not support RIGHT and FULL joins, so avoid these join types if possible.

• The maximum precision and scale for numeric or decimal values is 1000.

• Queries using GROUP BY and/or aggregate functions (MIN, MAX, and so on) are not optimized, except for COUNT, which can be optimized in some circumstances.

• Currently, MIN and MAX operators do not exploit internal indices. Instead, use the following equivalent queries, which are probably more efficient:

  SELECT val FROM myTable ORDER BY val DESC NULLS LAST LIMIT 1 instead of SELECT MAX(val) FROM myTable

  SELECT val FROM myTable ORDER BY val LIMIT 1 instead of SELECT MIN(val) FROM myTable