the sporks space

This was a very old draft I’ve had sitting around for years. The recent discussions of new query languages like Acadia spurred me to revisit, revise, and publish this.

I think one of the biggest causes of NoSQL is that while SQL is a powerful language because of the ideas behind it, it’s often implemented in clumsy and archaic ways. A language that learns from SQL could make relational data better to manipulate for programmers. I’ll try to think of things similar to those that I have dealt with in real-world situations and how a better query language could have helped. I’d love discussion on what else could be done.

For what it’s worth, my background with RDBMSes is mostly in MySQL and Db2, but I have used SQLite, SQL Server, Oracle, and Postgres in anger enough (in descending order of familiarity).

Better syntax

I’m not picky myself about aesthetics, but many others are. Programmers are like toddlers, they want their Kraft Dinner and not the broccoli. Basing syntax off of PL/I is a 1970’s IBM choice that probably wouldn’t fly today. Due to popular demand, such a language probably would pick up C or Python aesthetics syntactically, though perhaps with some ML or Prolog influence (as i.e. Rust shows).

With better syntax I hope can come better parsers. I especially loathe MySQL’s parser, which never actually tells you where problems lie or what it is, if it isn’t some syntax absurdity like DELIMITER. Better SQL parsers do exist in conventional implementations though – Oracle is surprisingly good at reporting errors by telling you what it expects.

The examples I write are just for show; I’m not wed to anything nor do I demand what syntax must be. My influences in these examples are most likely from F# (ML family), Erlang (Prolog-esque), and Elixir (Erlang and Ruby like).

A functional programming language that isn’t hostile to functional programming

SQL’s 4GL qualities where you describe how you want your data instead of looping over it by hand is SQL’s most powerful weapon. This is pretty close to a lot of functional programming paradigms like lazy evaluation – hello Haskell. Unfortunately, the standard library of most SQL dialects is somewhat anemic on this front; being optimized for 1980’s procedural programs. Most SQL dialects ended up supporting stored procedures, which are inherently… procedural; going against the grain of SQL’s declarative nature. This ends up reflected in most user SQL code, where they imitate the style that the language and standard library make easy, which involves a lot of dealing with mutable state (cursors…) and procedures over functions. Defaults matter.

Less opaque query planners

While being a 4GL is a strength with how powerful compilers and optimizers are optimizing most code, it can be easy to make a mistake that makes a query more expensive, but planners can be cryptic unless you’re already an SQL optimization expert. (Again, special mention to how bad MySQL’s “explain”ing tools are for this.) While not strictly PLT related, it is something weak in current SQL implementations that computer scientists have learned a lot about.

Better user defined types

While some RDBMSes offer the concept of domains for specifying user-defined data types (and is an optional part of the SQL spec), they can be limited in what they can do (usually just sugar around ranges or checks). Postgres was the only one that seems to support it; Oracle apparently only got support recently (though it seems perhaps more flexible than Postgres). Unfortunately, I haven’t used either enough to be very familiar with how it works in practice. However, domains are covered in Codd’s The Relational Model, which is the foundational text for RDBMSes. Considering Postgres’ heritage in Ingres, which was based on QUEL, which in turn was closer to Codd’s vision of RDBMSes than SQL was, it makes sense Postgres ended up following that.

Sum types, discriminated unions, and pattern matching

One schema that illustrates how modern functional programming techniques could be applied here is this function that returns stack frame information. For context, IBM i, the operating system mentioned here, provides many SQL functions for system administration under the “Services” umbrella. While this is very useful for DBAs-turned-system administrators in the heat of debugging, it is unfortunately clumsy, because effectively there’s “groups” of columns that are effectively mutually exclusive, lots of nullables because of that, and string fields that are effectively enums.

Some of these are just poor schema design (perhaps not helped by the fact it must be returned in a single table – returning multiple tables would also be an interesting direction to go in); the stringy enums can be fixed with a foreign key constraint on a table that acts as an enum. Some are down to language expressiveness in implementations, though.

Using this idea, I try to come up with a better example that would make queries less verbose and error-prone:

// heavily omitting things for simplicity; i.e displacement or additional enum cases, as well as defining enums ad-hoc (they could be declared out of the type too)

// Each frame type, while similar, is not identical, and has different
// semantics or qualifications.
type MachineInterfaceInfo =
	{
		ActivationGroup: long;
		ASP: long;
		Library: string;
	}

// For those that lack context here, IBM i supports multiple program models:
// - Java programs, which runtime provides the system some special insight
// - OPM programs, the old managed runtime program ABI
// - ILE programs, the new managed runtime program ABI
// - AIX programs, through syscall emulation
// - LIC, the IBM i kernel
// It can generate stack traces for all these kinds of programs; some programs
// may have a call stack containing a frame entry of each type.

type FrameType =
	// Inherit fields from another record type.
	| ILE { MachineInterfaceInfo | ServiceProgram: string; Module: string; }
	| OPM { MachineInterfaceInfo | Program: string; }
	| AIX { Bitness: enum(32 | 64); LibArchive: Option(string); Module: string, Syscall: bool;  }
	| Java { MethodType: enum(DirectExecution | Glue | Interp | JIT | MMI); ClassName: string; Signature: Option(string); }
	
table Frame =
	{
		ThreadID: long;
		FrameType: FrameType;
		Function: Option(string);
	}
	
function StackInfo(JobID: string) : Frame;

// An SQL-like select with pattern matching to filter.
select Function from StackInfo("1234/JOB/5678") where AIX { Bitness: 64 } = FrameType;
// this would return FrameType of ILE and OPM
select Function from StackInfo("1234/JOB/5678") where MachineInterfaceInfo { Library: "QSYS" } = FrameType;
select Function from StackInfo("1234/JOB/5678") where AIX { LibArchive: "libc.a" } = FrameType;
select Function from StackInfo("1234/JOB/5678") where AIX { LibArchive: None } = FrameType;

// A function that prints information with a pattern match inside of it.
function FrameFullySpecifiedProgramName(frame : Frame) : string =
	match frame.FrameInfo with
	| OPM { Program: program } -> program
	| ILE { ServiceProgram: srvpgm, Module: module } -> "#{srvpgm}/#{module}"
	| AIX { LibArchive: None, Module: module } -> module
	| AIX { LibArchive: lib, Module: module } -> "#{lib}(#{module})"
	| Java { ClassName: class } -> class
	// we must match all possible types, or discard with _
	| _ -> "?"

// A function that uses pattern matching based overloads and destructuring.
function FrameJavaFunctionDef(frame : Frame { Java { Signature: None } = .FrameInfo }) : string =
	"#{frame.Function}()"

function FrameJavaFunctionDef(frame : Frame { Java { Signature: signature } = .FrameInfo }) : string =
	"#{frame.Function}(#{signature})"
// A call to this with a non-Java frame is an error, because no patterns could match.

If we can collapse the mutually exclusive set of columns, it also makes it much easier to visualize too. A lot less scrolling left and right if they can i.e. be turned into subcolumns shown per row in a larger column, or as a strings displayed differently per type.

Foreign keys that match on multiple types

Say I have tables “Software”, “Version”, and “Download” (a sort of WEMI-ish hierarchy), and that each could have images, with a “Picture” table. (Because the images themselves have metadata, they’re a table rather than a column on each of these.) Usually, you would use a many-to-many table for each kind of relation, so “SoftwarePicture”, “VersionPicture”, etc. This seems like pointless duplication, if instead we could have a many to many table that effectively has a discriminated union on foreign keys:

table ObjectPictures =
	{
		// a foreign key is assumed to have the same type as what it relates to
		PictureID: key relates to (Picture.PictureID);
		ObjectID: key relates to (Software.SoftwareID | Version.VersionID | Download.DownloadID);
	}

insert into ObjectPictures (PictureID, ObjectID) values (0x1234, DownloadID { 0x1234 });