Designing SQL alternative?

Question

I'm thinking of designing & implementing my own SQL-equivalent (DDL+DML) that is...

1. a pure relational algebraic language, and
2. has an elegant Lisp-/Scheme-like syntax

Assuming an RDBMS such as MySQL, where exactly would I need to start my language design work? On top of the storage engine layer such as InnoDB?

I don't know what all RDBMS functionality generally sits atop a Storage Engine? My current intuitive understanding is that the bulk of the work needed to create an RDBMS (such as MySQL) would already have been done within the storage engine (specifically, in InnoDB), and all you would need to do is create a wrapper language around it. But I also know that if that were really true, why would MySQL be existing in the first place!

Answer 1

It shouldn't take you long if you actually write it in lisp. I wrote a simple database engine in Lisp in about an afternoon. Here's an example of what it looks like:

(select movies (<= 1990 year 2000) (member director '(terry-gilliam tim-burton)))

Here, 'select' is a macro. It scans the predicates that follow it for symbols that are field names and binds them to the fields in the database. Then it writes a function that binds those fields to the values of a record passed to the function, and filters the table using that function. The macro expands to something like this:

(flet ((filter (item)
        (let ((year (movie-year item))
              (director (movie-director item)))
         (and (<= 1990 year 2000)
              (member director '(terry-gilliam tim-burton))))))
 (loop for item in movies
       if (filter item) collect item))

The cool thing about doing it this way (actually in Lisp, rather than just using a Lisp-like syntax) is that you get compilation for free. On my system, the code above isn't interpreted by the database engine, it's actually a compiled part of the program (can't do that in C, now can you?). As a result it's fast, even though the database code itself (the code for 'select' and table definitions) is only a few lines long. The database is entirely memory resident, but it doesn't matter... you could just change the macro to accommodate external databases and even write it do use indices. It was good enough for the project I was working on, so I didn't add indices or anything fancy.

My favorite part about it is that while it keeps all the conciseness of SQL, the code is no different than the code around it because it's all Lisp. You can introduce variables into your search term without having to worry about quoting them.