Category Archives: Backend

That’s the question I’ve been asked: What exactly is so bad about SQL?. It’s not easy to produce a satisfying answer.

Most of the SQL that most people use is roughly SQL-1992, with perhaps a few bits from SQL_2003 like WITH. That’s a data sub-language, and while you can only write a small part of an application in this language, most of the critical logic has to be written in a general purpose object-oriented programming language. That’s where the ‘object relational impedance mismatch’ comes from.

Here is a list of the ‘Fatal Flaws’ of this kind of SQL, taken from the Askew Wall by Hugh Darwen.

• Anonymous columns (partly addressed in 1992)
• FROM clause restricted to named tables (fixed in 1992)
• Duplicate column names
• Order of columns is significant
• Duplicate rows
• NULL
• Failure to support degenerate cases (e.g. columnless tables)
• Failure to support “=“ properly
• and lots more, and probably to come.

Chris Date and Hugh Darwen wrote a book called Database, Types and the Relational Model. It contains the rational for the Third Manifesto, and also an assessment of how well SQL stacks up against the Manifesto. The version of SQL targeted is SQL:2003 (with PL/PSM). The book does an extensive analysis of this version of SQL against TTM, and identifies sins of omission and commission. Obviously SQL inherits many legacy features and the consequences of decisions made years ago, however many of the gaps appear to have been filled in. Rather than focussing on the bad things one can do in SQL, my question comes down to these.

1. If you set out to code in a TTM compliant style, how close can you get in SQL:2003?
2. If a TTM language generated SQL:2003 as a backend target, how close could it get to TTM compliance?

Obviously in either case:

1. NULL should never be used (and some extra code is needed to avoid it arising during evaluations, eg in aggregates)
2. Some comparisons need to be coded carefully to avoid mistakes (eg trailing spaces in CHAR)
3. All queries should have DISTINCT in effect to avoid duplicate rows
4. Column attributes should be unique and in the same order in all tables (that have the same columns)
5. Keys should be defined as compliant candidate keys.
6. Tuple level operations should be avoided (FETCH, WHERE CURRENT)
7. Much care should be taken to use certain syntactic features and not use others.

The only failures (from that list) that I can find are:

1. There are severe limitations in the type system (possreps, components, selectors, constraints, tuple types, relation types).
2. No assignment (or multiple assignment) for relation types; emulation via DELETE/INSERT runs into the multiple assignment problem.
3. No relation-valued attributes (but might be emulated using MULTISET)
4. No public relvars (can only share database tables)
5. No nested transactions; some other issues around transactions.

I should point out here that this only applies directly to SQL:2003, but in broad terms it probably applies to later versions of the standard and implementations that claim substantial compliance with the standard (perhaps with different syntax).

So apart from the type system, the horrible syntax and the plethora of ways to shoot yourself in the foot, SQL:2003 is not all that bad! My answer to my two questions is:

1. Quite close, apart from the seriously deficient type system.
2. Very close indeed, if the compiler can provide its own type system on top of that provided by SQL, its own syntax, and then generate bog standard SQL (or some dialect to suit a particular engine).

You can write good code in SQL:2003 or later, but it’s not easy. A better language sitting on top of SQL might make it a whole lot easier. Is that where Andl fits?

Andl has reached the six month mark. I started working on it in October 2014 and the first check in of some actual code was on the 27th. Now it’s April 2015, so where am I up to?

First, how big is it? Basic statistics: Andl is currently about 7500 lines of C# code, 1500 lines of Andl code and other bits and pieces. I’ve probably written twice that to get it to here.

What can it do? A lot. Most of the things that most people write in SQL can easily be handled by Andl code. In my opinion it’s a nice little language of a rather functional kind, and I’ve been surprised at how powerful the relational paradigm is, even when it’s the only collection type and the problem doesn’t look like data.

It implements large chunks of The Third Manifesto, but definitely not all.

• The type system is reasonably complete, but there are no type constraints and no subtyping. There are components, selectors and getters, but not alternative ‘possreps’.
• Support for the Relational Algebra and a host of related capabilities is complete for relations. RA support for tuples is indirect, requiring them to be wrapped in a relation first.
• Relvars can be persisted, but there are no relvar constraints, no real concept of a database and no transactions.

The net effect is that it implements the TTM requirements for a D language, but excluding RM Prescriptions 14, 15, 16, 17, 23, 24 and 25, and OO Prescriptions 4 and 5. It’s a work in progress.

I have written two scripts of sample code to show off most of what Andl can do.