A Highlevel Overview of DA SQL

The following is a preview of new content about the DA SQL feature that i’ve been working on as part of a general refresh of the Data Abstract homepage. The goal is not to explain DA SQL technically in depth, but to give a high level marketing-type overview. The topic will be one of many describing different core features of DA. Feedback appreciated.

In its simplest form, a SQL expression is used to query for all data from a given
table, or maybe specify a subset of fields or records to retrieve. For example,
the query

SELECT CustomerID, Name FROM Customers WHERE Name = 'Miller'

would request the two specified fields, for all Customers named Miller. However,
more complex SQL queries can work across multiple tables, aggregate and join data,
and otherwise express pretty complex data access scenarios.

SQL and Multi-Tier

Traditionally, SQL has been constrained to be used in two-tier client/server applications,
or on the server (a.k.a. "middle") tier of multi-tier apps.

In the classic client/server scenario, client applications would contain or generate
SQL statements that were run directly against the database, without any means for
business logic or fine data access restrictions to be applied in between. Because
clients had full access to the SQL of the back-end server, they could query any
data they pleased, and make extensive changes without control. Great flexibility
on the client-side was achieved by sacrificing control.

DA SQL™, a new technology introduced to Data Abstract in 2008, is set to change
all that, by providing clients the full flexibility of SQL queries to describe their
data needs, without giving up the control held in the middle-tier. While traditional
client/server applications would allow clients to write SQL that ran directly against
the back-end database,as discussed above, DA SQL statements sent from the client
application are processed and run against the data as published by the business
tier, allowing that tier to keep full control over data access and updates.

DA SQL statements will use field and table names as they are defined in the middle-tier
schemas of the
Data Abstract server, and will automatically be restricted to the data and fields
that the middle-tier allows access to.

For example, an extensive Customers table might be exposed to a client application
for sales personnel with two restrictions: (a) only a subset of, say, 10 fields
is available and (b) every sales person may only access customers from his or her
region. This is data access business logic that would be encoded in the schema or
in custom code written for the server application, making sure that whenever a client
application retrieves or updates data, these restrictions are upheld.

DA SQL allows client applications to use SQL to perform data queries, without giving
up or bypassing this (or any other) business logic in the course. For example, the
client application might send the following query to the middle-tier server:

SELECT * FROM Customers WHERE Name = 'Miller'

Whether the application allowed the end-user to type this query or generated this
SQL code as part of a query builder or some other form of UI, the intention is the
same: the application is asking for all fields of the Customer table where the customer
in question is named Miller. But as we recall, data access constraints were
set on the server, so the client will not actually receive all fields of the customer
table (nor all customers, world-wide, that are named Miller): because DA SQL is
processed and executed in the middle tier, business logic will be applied constrain
the access and enforce the business rules we set forth above. As a result, the actual
database query that will run against the server might look something like this:

SELECT ID, Name, Address, ... FROM Customers WHERE Name = 'Miller' AND Region = 78

DA SQL, which has deep understanding of the back-end database /and/ subset that
the schema exposes to clients, was able to craft a new query – combining the query
it received from the client with its own filters, in both SELECT and WHERE clause.

DA SQL Flexibility

While
the above is a simple example (and, to boot, a data request that could have easily
expressed, client side, without the need for SQL), DA SQL allows much more complicated
and detailed requests to be formulated, which would still benefit from full protection
of business logic.

For example, a query could JOIN together different tables, employ nested queries
or have a much more elaborate and complicated WHERE clause. Still, DA SQL would
enforce that data can be accessed as permitted by the schema
and other server-side logic.

At the moment, DA SQL supports a subset of the SQL92 standard, with most querying
options commonly used, including JOINs, nested queries and of course extensive SELECT
and WHERE clauses to describe subsets of fields or records to retrieve. Over time,
it will be expanded to support all features of SQL92 that are applicable. More details
on the current set of supported SQL features can be found here
in our wiki.

How does it work?

It is important to understand that DA SQL query statements are not, ever, diretcly
passed through to the back-end database, and thus completely eliminating the risk
of SQL injection attacks or malicious clients executing unwanted actions as part
of what looks like an innocent SELECT request.

When a query is received by the DA SQL engine on the middle-tier server, it is parsed
and processed into an internal structure describing the SELECT, INSERT, UPDATE or
DELETE statement it represents, much like would be done on a regular back-end database
engine. This structure is then compared to the tables and fields defined in the
schema and validated
against it – for example, a statement touching a table (or fields of a table)
not exposed in the schema would be rejected with an “Unknown table”
error – even though the actual table might exist in the back-end database.

Once the query is validated, DA will go ahead and build its own SQL statement (or
set of statements), in the syntax of the respective back-end databases (“DB
SQL”), to fetch the data as needed. Here it will apply any and all business
constraints, to make sure it accesses only data that is permitted (for example,
as seen above a query with * asking for all fields would be translated to only retrieve
the fields actually published via the schema). It will also apply any column mappings
(link!) or database abstraction defined in the schema.

Depending on the complexity of the original DA SQL query and the type of the data
sources on the back-end needed to fulfill it, DA might be able to generate a single
“DB SQL” statement, or it might have to run separate queries (for example
to fulfill a JOIN spanning separate back-end databases) and combine data internally,
before sending it back to the client.

All of this is happening transparently on the server – all the client sees
is the resulting data matching its query.

Find Out More

DA SQL is mainly a server-side technology, and currently available in DA/.NET Servers
and the upcoming cross-platform standalone DAServer6.
It can of course be consumed from clients written in all three editions of Data
Abstract, including .NET, Delphi and the upcoming native OS X library.

For .NET and Silverlight client development, an interesting technology built on
top of DA SQL is DA LINQ.

You can read more about DA SQL in
our wiki, or check out the DA SQL samples shipping with the products.