January 15th, 2020 by Adam Sandman
One of the maxims I always tell developers is that regardless of what you build, customers will never be satisfied with the reports you offer or the integration that you provide. In fact, the two most underestimated tasks in software development are data feeds and reporting. So one of the nice features in our Spira platform is the ability to do custom graphing, so that you are not limited to just the graphs that ship with the system. This article is the third in a series that explains how to use these powerful custom graphing features, which continues on from our second article that provided the basics of writing ESQL queries. In this article we'll discuss some of the differences and limitations between ESQL and traditional database SQL.
No Support for *
Database SQL supports the unqualified * syntax as an alias for the entire row, and the qualified * syntax (t.*) as a shortcut for the fields of that table. In addition, database SQL allows for a special count(*) aggregate, which includes nulls.
Entity SQL does not support the * construct. Database SQL queries of the form:
select * from T
and
select T1.* from T1, T2...
can be expressed in Entity SQL as
select value t from T as t
and
select value t1 from T1 as t1, T2 as t2...,
respectively.
Additionally, these constructs handle inheritance (value substitutability), while the select * variants are restricted to top-level properties of the declared type. Entity SQL does not support the count(*) aggregate. Use count(0) instead.
Changes to Group By
Entity SQL supports aliasing of group by keys. Expressions in the select clause and having clause must refer to the group by keys via these aliases. For example, this Entity SQL syntax:
ESQL
SELECT k1, count(t.a), sum(t.a)
FROM T AS t
GROUP BY t.b + t.c AS k1
...is equivalent to the following database SQL:
SQL
SELECT b + c, count(*), sum(a)
FROM T
GROUP BY b + c
Collection-Based Aggregates
Entity SQL supports two kinds of aggregates.
Collection-based aggregates operate on collections and produce the aggregated result. These can appear anywhere in the query, and do not require a group by clause. For example:
SELECT t.a AS a, count({1,2,3}) AS b FROM T AS t
Entity SQL also supports SQL-style aggregates. For example:
SELECT a, sum(t.b) FROM T AS t GROUP BY t.a AS a
ORDER BY Clause Usage
Database SQL allows ORDER BY clauses to be specified only in the topmost SELECT .. FROM .. WHERE block. In Entity SQL you can use a nested ORDER BY expression and it can be placed anywhere in the query, but ordering in a nested query is not preserved.
-- The following query will order the results by the last name
SELECT C1.FirstName, C1.LastName
FROM AdventureWorks.Contact AS C1
ORDER BY C1.LastName
-- In the following query ordering of the nested query is ignored.
SELECT C2.FirstName, C2.LastName
FROM (SELECT C1.FirstName, C1.LastName
FROM AdventureWorks.Contact as C1
ORDER BY C1.LastName) as C2
Case/Accent Sensitivity
In database SQL, identifier comparison is based on the settings of the current database and the database platform being used (SQL Server, Oracle, MySQL, etc.). In Entity SQL, identifiers are always case insensitive and accent sensitive (that is, Entity SQL distinguishes between accented and unaccented characters; for example, 'a' is not equal to 'αΊ₯'). Entity SQL treats versions of letters that appear the same but are from different code pages as different characters.
Group By Clause Differences
Entity SQL also imposes additional restrictions on queries involving group by clauses. Expressions in the select clause and having clause of such queries may only refer to the group by keys via their aliases. The following construct is valid in most database SQL variants but are not in Entity SQL:
SELECT t.x + t.y FROM T AS t group BY t.x + t.y
To do this in Entity SQL:
SELECT k FROM T AS t GROUP BY (t.x + t.y) AS k
Referencing Columns (Properties) of Tables (Collections)
All column references in Entity SQL must be qualified with the table alias. The following construct (assuming that a is a valid column of table T) is valid in database SQL but not in Entity SQL.
SELECT a FROM T
The Entity SQL form is
SELECT t.a AS A FROM T AS t
The table aliases are optional in the from clause. The name of the table is used as the implicit alias. Entity SQL allows the following form as well:
SELECT Tab.a FROM Tab
Navigation Through Objects
Database SQL uses the "." notation for referencing columns of (a row of) a table. Entity SQL extends this notation (borrowed from programming languages) to support navigation through properties of an object.
For example, if p is an expression of type Person, the following is the Entity SQL syntax for referencing the city of the address of this person.
p.Address.City
Collections of Literals
In database SQL, if you want to refer to a collection of possible values, you would use an IN clause together with a set of values contained within parenthesis:
SQL
SELECT t.a FROM T as t WHERE t.b IN (1,2,3) In Entity SQL, the syntax for a collection of values is based on braces / curly brackets instead:
ESQL
select t.a from T as t where t.b in { 1,2,3 }
Differences in Literals and Types
There are some differences between how literal values and types are represented in Entity SQL vs. Database SQL:
- In database SQL, you typically represent boolean values as 1 or 0 whereas in Entity SQL you use true and false
- Database SQL uses database schema types such as VARCHAR, NVARCHAR and INT, whereas Entity SQL uses Microsoft .NET types such as String and Int32
- Integer literals can be of type Int32 (123), UInt32 (123U), Int64 (123L), and UInt64 (123UL)
- DateTime literals, both date and time parts are mandatory. There are no default values. For example, a date literal would be:
DATETIME '2006-12-25 01:01:00.000' - There are Unicode and non-Unicode character string literals. Unicode strings are prepended with N. For example,
N'hello'. - Typed nulls can be used anywhere. Type inference is not required for typed nulls because the type is known. For example, you can construct a null of type Int16 with the following Entity SQL construct:
(cast(null as Int16))
Database SQL Functionality Not Available in Entity SQL
The following database SQL functionality is not available in Entity SQL.
- DML Commands - Entity SQL currently provides no support for DML statements (insert, update, delete).
- DDL Commands - Entity SQL provides no support for DDL in the current version.
- Imperative Programming - Entity SQL provides no support for imperative programming, unlike Transact-SQL. Use a programming language instead.
- Grouping Functions - Entity SQL does not yet provide support for grouping functions (for example, CUBE, ROLLUP, and GROUPING_SET).
- Analytic Functions - Entity SQL does not (yet) provide support for analytic functions.
- Built-in Functions, Operators - Entity SQL supports a subset of most database SQL's built in functions and operators.
Advanced Entity SQL Queries
Now that we have discussed the differences between traditional database SQL and Entity SQL, in our last article in this series, we shall cover come more advanced queries and functions that customers typically will want to use when creating custom graphs with Spira.