DA SQL

This is a Concept topic for Data Abstract

It is important to realize, that while DA SQL brings the flexibility of SQL to the client, it does so without actually exposing the back-end server directly (after all, that is one of the corner stones of a multi-tier architecture). The queries written on the client are based on objects defined in your schema, not on those in the back-end data storage. DA does not simply take your SQL and passes it on to the back-end; it parses the SQL, understands it and then fetches the data requested according to the same rules that would apply if the client was using method 1 or 2 listed above.

For example, in a query such as "SELECT * FROM Customers", the name "Customers" does not refer to a table in the database – it refers to the data table "Customers" that you have defined in your schema. Similarly, "*" will not fetch all fields defined in the database, but all public fields defined in your schema. In the back-end, the table might have a different name (say "CUST", and different field definitions. "Customers" might also be a Union Table you defined, based on entirely different back end databases).

The DA SQL query sees the data as it is defined in your schema, and accesses it through the methods defined in your schema (be it AutoSQL, or hard-coded statements or stored procedures).

Exposing DA SQL to your client app is 100% safe, as it just provides access to the same data that's already available now when you ask the Remote Data Adapter to fill a data table – it just provides this access in a far more flexible way, allowing client code to filter and combine the data server-side, as it needs.

Platform Support

Currently DA SQL requires that the server is either written in .NET, or the cross platform Relativity Server. It works on any client that connects to a supported server. See the platform specific page for a sample:

A DA SQL client sample project ships with each platform as well.

Supported SQL Syntax

The following provides an overview and examples of the different SQL syntax elements that are supported by DA SQL. The goal is to expand DA SQL, over time, to provide full SQL-92 compliance within SELECT statements.

Simple cases (Select from single table):

SQL statements with and without table aliases like

-- with alias
SELECT cust.Id, cust.Name, cust.Birthdate FROM dasql_Customers cust

-- with alias declared via AS
SELECT cust.Id, cust.Name, cust.Birthdate FROM dasql_Customers as cust

-- with and without alias (if alias is not specified, field name must be unique across tables)
SELECT cust.Id, Name, cust.Birthdate FROM dasql_Customers as cust

-- without alias
SELECT Id, Name, Birthdate FROM dasql_Customers

SQL statements where all fields requested (using asterix)

SELECT * FROM dasql_Customers

SQL statement to DA structures whose name differs from DB structures (using mapping)

SELECT CustomerID, CustomerName, CustomerBirthdate FROM dasql_Customers_MappingTest

SQL statements on tables based on SQL and NonSQL statements

With WHERE clause

SQL statements with WHERE statement (no splitting expected)

SELECT cust.Id, cust.Name, cust.Birthdate FROM dasql_Customers cust 
    WHERE --condition

Supported Operators:

-- comparisons, including =, < <=, >, >= and <>
SELECT * FROM customers AS c 
  WHERE cust.id = 2

-- boolean logic, including AND, OR
SELECT * FROM customers AS c 
  WHERE (cust.id = 2) or (cust.id = 4) or (cust.id = 3)

-- using "IN (list)"
SELECT * FROM customers AS c WHERE c.cid IN ('ALFKI', 'PARIS', 'FISSA', 'SPLIR')

-- using IS NULL and IS NOT NULL conditions 
SELECT * FROM employees WHERE empaddress IS NOT NULL

-- using Unary conditions 
SELECT * FROM employees WHERE NOT (Id = 5)

(TODO: expand and provide full list)

Joins (select from several tables)

All kind of joins (Merged by SortMergeJoin only)

SELECT cust.Name, ord.Summ FROM dasql_Customers cust 
   INNER JOIN dasql_Orders ord on cust.Id = ord.CustID;

SELECT cust.Name, ord.Summ FROM dasql_Customers cust 
   LEFT OUTER JOIN dasql_Orders ord on cust.Id = ord.CustID;

SELECT cust.Name, ord.Summ FROM dasql_Customers cust 
   RIGHT OUTER JOIN dasql_Orders ord on cust.Id = ord.CustID;

Inner join with condition inside WHERE clause

SELECT cust.Name, ord.Summ FROM dasql_Customers cust, dasql_Orders ord 
   WHERE cust.Id = ord.CustID

Join condition that based on fields with other than integer field type (m.ID and d.MasterID is a string fields)

SELECT m.Name, d.val FROM dasql_master1 m, dasql_details1 d 
   WHERE m.ID = d.MasterID AND val > 10;

Joining more than 2 tables

SELECT c.cid, o.id, createdate, d.itemid, d.price, d.qty 
FROM customers c 
INNER JOIN orders o ON c.cid = o.customer 
INNER JOIN [Order Details] d ON o.id = d.orderid 
WHERE c.cid = 'ALFKI' 
OR c.cid = 'PARIS' 
OR c.cid = 'FISSA' 
OR c.cid = 'SPLIR'

Ordering (Order By)

Ordering of the result set via ORDER BY clause

SELECT * FROM customers ORDER BY CompanyName
SELECT * FROM customers ORDER BY CID DESC
SELECT * FROM customers ORDER BY ContactPerson ASC
SELECT customerid, firstname, lastname FROM customers ORDER BY 3 DESC
SELECT OrderID, ItemID, Price, Qty, Discount FROM [Order Details] ORDER BY OrderID, Price DESC

SELECT c.cid, o.id, o.createdate, freight 
  FROM Customers c 
    INNER JOIN orders o ON c.cid = o.customer 
  WHERE c.cid = 'ALFKI' 
  ORDER BY o.createdate, freight DESC

Using parameters

Sql with parameters

SELECT c.cid, o.id, o.createdate, freight 
  FROM customers AS c 
  WHERE c.cid = :pCustomerID 
  ORDER BY o.createdate, freight DESC

Current Restrictions and Limitations

No support for sub-queries, such as SELECT a.* FROM (subselect) AS a.
No support for UNIONs.
No support for SELECT TOP/SKIP n.

Splitting onto BlackBoxes:

EFor JOINs, each table in the query is treated as a black box now and retrieved separately, even if several tables are from the same connection and each is based on an Auto-SQL statements, so that joining could be deferred to the back-end database server. This is an area that is slated for future improvement.

Joining:

DA SQL currently can only process a single join condition between tables. For example: SELECT * FROM master m INNER JOIN details d ON ((m.id = d.masterid) AND (m.state=d.state)) is not supported.
No support for conditions such as (CustomerID = 5) in JOIN clause. Only field references are supported currently.
Only supports "Equal" conditions, such as (m.id = d.masterid.)
Joins.HashJoin is not implemented yet.
Joins.NestedLoopJoin is not implemented yet.

DA SQL and DA LINQ

One prominent feature of DA that leverages DA SQL under the hood is DA LINQ. DA LINQ is a .NET technology that allows you to use LINQ constructions to operate with data. The LinqRemoteDataAdapter can generate ad hoc SQL queries based on the LINQ query, and will use DA SQL to process them.

It is important to realize that while DA LINQ depends on DA SQL, the same is not true in the other direction. DA SQL stands as its own technology and can be used separately from DA LINQ, including client platforms that do not support LINQ, such as Delphi or Xcode.

Behind the Scenes

What DA SQL does, basically, is to process incoming SQL queries and return result data back to the client. So the main task of the DA SQL is to execute SQL statements against the objects in our schema. The diagram below illustrates the process:

DA SQL takes an incoming SQL statement and processes it. In essence, the DA middle tier is behaving like any SQL based backend server, in that it is fed a SQL string specifying the data to query, and will fetch a result set in return.

The crucial difference is, that instead of directly working against a data store, DA SQL is applied against your schema. This way, all your data mapping and business logic is preserved, while giving you the full flexibility of SQL to express your query (opposed to just specifying a table name and a more constrained TDAWhere XML).

Processing is done in several stages; each stage is controlled by certain objects.

First DA SQL receives a request with a SQL statement and passes it to the SQL Parser. The parser makes sure that the SQL statement is valid in syntax and converts it into an internal representation that is more easily handled by the following steps rather than a chunk of text. The result is an object that holds all information required to fetch the requested data.

Second comes validation against the schema. While the first step made sure the SQL was a syntactically correct, this second pass validates the request against the tables and fields that are defined in the schema. Remember that identifiers in a DA SQL query are objects in your schema, not the back-end database. The validator makes sure that the query only accesses elements that are properly defined and exposed in the schema.

Third, once the query is determined to be valid, the next task is analyze the query and generate a Query Plan for fetching the data. In the ideal case, the result would be a single SQL statement to be run on your backend that fetches all data in one go, but, depending on your table configurations, that might not always be possible.

DA SQL abstracts this away from you by splitting the query into separate tasks, the so-called "Black Boxes", where necessary. Each black box can be fetched independently.

Imagine the following scenario: You're passing a query like this

SELECT c.id, c.name, o.sum FROM customer c 
   INNER JOIN orders o ON c.id=o.custid WHERE o.date = :TODAY

to DA SQL, where the Customer table is is based on an AutoSQL statement, but Orders is not (it might be using a hard-coded SQL statement, or even a stored procedure). Because the way Orders are fetched is hard-coded in the schema, we cannot build a single SELECT statement for getting both tables out of the back-end. Instead, DA SQL will split such a request into two Black Boxes, where the first one will be run with an ad-hoc SQL statement such as

SELECT c.custid, c.name FROM cust c

to fetch customers (note the different field names, now representing entities in the back end database), while the the second Black Box will of course use the statement hard-coded inside the schema. Where possible, a Dynamic Where might be applied to the second box, to keep data fetching to the absolute minimum.

Another reason why a DA SQL query might need to be split into two or more boxes could be if the tables used in the query originate from different databases (i.e. using different connection strings), when using DA NET.

DA SQL will, of course, try to avoid splitting the query into more boxes than necessary; in the ideal case, when all tables use AutoSQL and come from the same database, data can always be fetched in one go.

Fourth, Each of these "black boxes" now independently fetches its data.

Fifth, in the final step, DA SQL will take the data it received from the different boxes and merge it into the final result set according to the JOIN or UNION conditions specified in the DA SQL query. The resulting record set will then be streamed into binary format and passed back to the client, just as a plain non-DA SQL request would have been.

DA SQL

Contents

Platform Support

Supported SQL Syntax

Simple cases (Select from single table):

With WHERE clause

Joins (select from several tables)

Ordering (Order By)

Using parameters

Current Restrictions and Limitations

DA SQL and DA LINQ

Behind the Scenes

See Also

Personal tools

Namespaces

Variants

Views

Actions

Search

Navigation

products

platforms

special

Toolbox