Cloud Software Group, Inc. EBX®
Documentation > Developer Guide > SQL in EBX®
Navigation mode Documentation > Developer Guide > SQL in EBX®

# 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 (including 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.

##### See also

- Entity mappings using the Data Model Assistant

### 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.

XML Schema type | SQL type | Java type | Notes
--- | --- | --- | ---
`xs:string` | `VARCHAR` | `java.lang.String` |
`xs:boolean` | `BOOLEAN` | `java.lang.Boolean` | Values: TRUE, FALSE, UNKNOWN
`xs:decimal` | `DECIMAL` | `java.math.BigDecimal` |
`xs:dateTime` | `TIMESTAMP` | `java.util.Date` |
`xs:time` | `TIME` | `java.util.Date` | The date portion of the returned `Date` is always set to '1970/01/01'.
`xs:date` | `DATE` | `java.util.Date` | The time portion of the returned `Date` is always set to the beginning of the day (that is, '00:00:00').
`xs:anyURI` | `VARCHAR` | `java.net.URI` |
`xs:Name` (`xs:string` restriction) | `VARCHAR` | `java.lang.String` |
`xs:int` | `INT` | `java.lang.Integer` |

### 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®

XML Schema type | SQL type | Java class
--- | --- | ---
`osd:text` (`xs:string` restriction) | `VARCHAR` | `java.lang.String`
`osd:html` (`xs:string` restriction) | `VARCHAR` | `java.lang.String`
`osd:email` (`xs:string` restriction) | `VARCHAR` | `java.lang.String`
`osd:password` (`xs:string` restriction) | `VARCHAR` | `java.lang.String`
`osd:color` (`xs:string` restriction) | `VARCHAR` | `java.lang.String`
`osd:resource` (`xs:anyURI` restriction) | VARCHAR | internal class
`osd:locale` (`xs:string` restriction) | `VARCHAR` | `java.util.Locale`
`osd:dataspaceKey` (`xs:string` restriction) | `VARCHAR` | `java.lang.String`
`osd:datasetName` (`xs:string` restriction) | `VARCHAR` | `java.lang.String`

### 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`

##### Note

Even if the primary key is composed of only one field, the name of the field must be specified to access the value. For example, if the primary key is composed of a single `id`, `fkb.id` must be used to access the value, as in `tableA.fkb.id = 123`

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.

Name | Description | SQL type | Java class | Examples
--- | --- | --- | --- | ---
`creator` | The `creator` of the record. | `VARCHAR` | `java.lang.String` | `SELECT t."ebx-metadata"."system"."creator" FROM myTable t` `SELECT t.* FROM myTable t WHERE t."ebx-metadata"."system"."creator" = 'Uuser1'` `SELECT t.* FROM myTable t ORDER BY t."ebx-metadata"."system"."creator"`
`creation_time` | The `creation time` of the record. | `TIMESTAMP` | `java.util.Date` | `SELECT t."ebx-metadata"."system"."creation_time" FROM myTable t` `SELECT t.* FROM myTable t WHERE t."ebx-metadata"."system"."creation_time" > TIMESTAMP'2001-06-10 14:57:20'` `SELECT t.* FROM myTable t ORDER BY t."ebx-metadata"."system"."creation_time"`
`updater` | The `last user` who modified the record. | `VARCHAR` | `java.lang.String` | `SELECT t."ebx-metadata"."system"."updater" FROM myTable t` `SELECT t.* FROM myTable t WHERE t."ebx-metadata"."system"."updater" = 'Uuser1'` `SELECT t.* FROM myTable t ORDER BY t."ebx-metadata"."system"."updater"`
`update_time` | The time of the `last modification` of the record. | `TIMESTAMP` | `java.util.Date` | `SELECT t."ebx-metadata"."system"."update_time" FROM myTable t` `SELECT t.* FROM myTable t WHERE t."ebx-metadata"."system"."update_time" > TIMESTAMP'2001-06-10 14:57:20'` `SELECT t.* FROM myTable t ORDER BY t."ebx-metadata"."system"."update_time"`

### 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.

Name | Description | SQL type | Java class | Examples
--- | --- | --- | --- | ---
`$adaptation` | The ``Adaptation`` representing the table record. | internal type | `com.onwbp.adaptation.Adaptation` | `SELECT t."$adaptation" FROM myTable t WHERE t.value>100`
`$pk` | String representation of the primary key of the record. ##### See also `PrimaryKey.syntax` | `VARCHAR` | `java.lang.String` | `SELECT t.* FROM myTable1 t WHERE t."$pk"='123'` `SELECT t.* FROM myTable2 t WHERE t."$pk"='123|abc'` `SELECT t."$pk" FROM myTable3 t WHERE t.value>100` `SELECT t.value FROM myTable3 t ORDER BY t."$pk"`

## 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.

Operator | Associativity
--- | ---
. | left
[ ] (array element) | left
+ - (unary plus, minus) | right
* / % | left
+ - | left
BETWEEN, IN, LIKE, CONTAINS, and so on | -
< > = <= >= <> != | left
IS NULL, IS FALSE, IS NOT TRUE, and so on | -
NOT | right
AND | left
OR | left

### 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`

Documentation > Developer Guide > SQL in EBX®