SQL Emulation Tool in Javascript

As you may know, I teach a introductory computer course in the evenings. One of the topics we cover are databases. Of course this is not a programming class, so there is a limited amount of things we can actually have them do. The students get to design and create databases in MS Access and learn a little bit of theory. Among other things we briefly cover SQL and show them how simple select or update statements work. Why? So that they get a better idea what are the strengths and limitations of a database.

What I like to do, is to put little tools that students can play around with on my website. For example, when we talked about binary system I made a little conversion tool they could use to check their work. They would type in a number in decimal, hit button and have it converted to binary and etc…

For the networking section I made a tiny script that validates IP addresses. When we talked about encryption, I implemented a Caesar Cipher tool they could mess around with and and encrypt/decrypt short passages. I also use it in class to show them special cases such as ROT13.

Mind you that I’m actually doing all of this with Javascript because I don’t actually have any server side scripting available. They took away my unix account after I graduated for some reason and they won’t give it back to me now. So I’m stuck with the flaky Novell NetDrive system which wont allow me server side scripting. Of course I could host these scripts on one of my own servers, but I figured I might as well use the MSU resources that I have.

I didn’t really have any tools for the database lecture. A while ago I got this crazy idea of making a nice little database application for the students to play with. I mean, we already have access but I wanted to have something on my website. Something really simple – like one table populated with some random data and a text box to type in SQL statements.

The problem is that since I’m working on the client side only, there is really no way for me to connect to a real database. Now I already have some experience in getting by without server side scripting. This however is less about massaging the browser into doing what I want it to do, and more about emulating a database on the client. What does it mean?

Well, first I need a “database” of sorts. Since this is a demo tool anyway, I actually don’t care about persistence. So my db will simply be a javascript object that is initialized when the page loads. When the student refreshes the page, it will be reset back to default. This way I can allow people to run insert and update statements on it, without worrying about spam, obscenity and SQL injection. That’s easy enough. I could implement it like this:

 var tables = {
     "person" : new Array(
          { 
              "id"        : 1,
              "fname"  : "John",
              "lname"  : "Smith",
          },
          { 
              "id"        : 2,
              "fname"  : "Jane",
              "lname"  : "Smith",
          },
     );
}

This way I can have multiple tables that can be easily accessed by name and I can easily pull stuff out of it this:

// to get a specific table
var mytable = tables["table_name"];

// to get the 1st row in the table
var myrow = tables["table_name"][1]

// to get the "id" attribute of the first row
var myrow = tables["table_name"][1]["id"]

It is also trivial to dynamically add and remove rows or even new tables to this model. Once I figure out what the query is supposed to do, I can easily modify it or extract the data and display it on the screen in a table form.

The hard and interesting part of the project is of course parsing SQL. Unfortunately I don’t have much code to show yet – I’m still trying to find an ideal way to do it. I hacked up a very simple parser that grabs the user input, sanitizes it (removing HTML tags, newlines and etc) and then splits it on white space. I iterate over the array and try to classify the tokens. I first check whether or not the token is on the list of legal SQL keywords and symbols. If yes, I classify it as such. If not, I try to guess what it is, based on it’s position in relation to other tokens.

For example, if I find a token that is not a keyword, and I already found the word SELECT but haven’t found FROM yet, I can probably assume this is the name of a column that is to be used in the statement. If I found FROM but haven’t found WHERE statement yet, I can assume that it is the name of a table instead. If I found where, then I look for triplets – two words separated by a logical comparison symbol. I have array of objects and I fill them with stuff as I find them.

It is still very buggy and actually too permissive. For example, it doesn’t care if you separate your column names by comas or if you write the logical comparison in postfix or prefix notation (ie. “foo = bar” parses the same way as “= foo bar” or “foo bar =”). I’m basically flying through the tokens and grabbing whatever matches, ignoring everything that does not. In other words, my SQL parser acts more like a modern HTML parser – it tries to make the best out of mangled code.

This is not necessarily what I want. I want this tool to allow kids to learn a thing or two about SQL. So my next step is to add more rigid error checking to emulate the way a database would act. As I’m stepping through the SQL statement I will start throwing exceptions as son as something is amiss.

I also need to figure out how to efficiently translate the the WHERE statement into some code that could actually – you know, do something. Fortunately, Javascript has a nifty evaluate function which means I can probably dynamically build code based on the user input. This is something I’m going with right now, but there might be a better way to do it.

The code I have right now is very flaky so I’m not going to show it yet. I will post an update within a day or two though. I’m pretty sure I can make this code functional using the methods I mentioned above. In the meantime I’m really considering hitting the books and actually reading up on compilers and formal parsing methods.A real parse tree would probably worm much better than my haphazard collection of objects, and binary flags that denote their state. I was to lazy to actually implement trees though – so I went for something much simpler.

Any suggestions? How would you go about doing this? Any resources and reading you would recommend? I know there might be a better method of setting up this sort of a demo – but I sort of like this project. I actually want to write this parser. So help me out if you have done this sort of thing before.

For the record just using Javascript, jQuery and the $.string plugin which ports all the useful string functions from the Prototype framework into jQuery.