SQL Emulation Tool in Javascript Part 2

As promised, I’m posting my semi-working sql parser below. You should keep in mind that the code is still very immature and full of bugs. One of my reasons for posting it here is that people will start playing around with it and break it in numerous ways helping me to discover the way I can improve the code.

I think I covered most of the architecture in the previous post. One bit of code I wanted to share here is my metaprogramming function generator. This bit of code takes in a list of conditions, and will generate a jaascript function that will test for these conditions and return a boolean value.

When my SQL parser evaluates the WHERE condition it constructs an array that looks a little bit like this:

Conditions Array (click to embigen)

Conditions Array (click to embigen)

Each member object is composed of five elements. The first one is the name of the column, the second one is the value that is used in comparison, and the third one is the comparison symbol. The fourth value is just a flag which indicates whether or not the parser was able to successfully generate this object. It is needed there since malformed SQL could produce only partially generated object to be added into this array. All new objects are generated with this flag set to false, and it is changed only after they are populated without errors. This let’s us spot and ignore malformed and incomplete entries.

The fifth field is the logical operator which is used to join the comparison described in the current object to the previous one. The first element of the array will always have it’s logic field un-initialized. The following elements will have it set to a legal logical operator – this is enforced by the parser.

Once I have this array I pass it into this function:

function generateCondFunction (conditions) {
 
	var tmp = "cond = function(row) { if(";
 
	for(i in conditions)
	{
		current = conditions[i];
 
		if(!current.full)
			throw "Incomplete WHERE statement";
 
		if(current.action == "<>")
			action = "!=";
		else if(current.action == "=")
			action = "==";
		else
			action = current.action;
 
		if(current.logic != null)
		{
			if(current.logic == "and")
				tmp += " && ";
			else 
				tmp += " || ";
		}
 
		tmp += " row[\"" + current.first + "\"] " + 
			action + " " + current.second;
	}
 
	tmp += ") return true; else return false; };";
 
	eval(tmp);
 
	return cond;
}

The beauty of Javascript is that you can build a string, and then execute it as code. This is precisely what I’m doing here. I use the elements from my array to build a comparison function, then evaluate it and return a function pointer. For example, the array from the picture above will yield the following function:

function(row)
{ 
    if( row["foo"] > 2 and row["bar"] != "poo" ) 
        return true; 
    else 
       return false; 
}

I take this function pointer and pass it into the table rendering function. Then as I iterate over the elements stored in my mock db-obects I pass them through this function fitst to see if they ought to be displayed or not. This is of course not the most efficient way of doing things, but it works.

Anyway, go check out the working demo here and let me know of weird bugs that you encounter. Please keep in mind that a lot of stuff still doesn’t work the way you would expect it. Here is the stuff that I know is still broken:

  • The canonical SELECT * FROM FOO doesn’t work – I have not implemented the wildcard selectors yet
  • If you don’t put spaces between listed columns, the thing will break
  • If you don’t put spaces in the WHERE condition, things will break. Typing in “foo < 2″ works fine but “foo<2″ does not
  • No common procedures (like NOW() and etc..) are implemented

Don’t report these things. Anything else though, will help me debugging the code. If you want to read through the whole thing, and nitpick or criticize my questionable coding practices you can find all the code here.

Also, Chris already told me that someone already created an SQL parser in Javascript. As far as I can tell TrimQuery is far superior to my hackish code here, so if you would want to use something like this in your project, you are probably much better off stealing code from there rather than from here.

This entry was posted in programming and tagged , , , , . Bookmark the permalink.



4 Responses to SQL Emulation Tool in Javascript Part 2

  1. TCP VIET NAM Mozilla Firefox Windows says:

    very cool & good script, thank you very much for sharing.

    Can I share this script on my JavaScript library, http://javascriptbank.com/submit/ ?

    Thank

    Reply  |  Quote
  2. Allow multiple spaces in a query,


    diff --git a/sql.js b/sql.js
    index 207848b..1969796 100644
    --- a/sql.js
    +++ b/sql.js
    @@ -67,9 +67,7 @@ parser.sanitize = function(sql) {
    .strip()
    .stripTags()
    .stripScripts()
    - .gsub("\r", "")
    - .gsub("\n", " ")
    - .gsub("\t", "")
    + .gsub(/\s+/, " ")
    .str
    }

    Doesn’t take into account spaces inside quotes, but neither did the stuff I deleted. :-P And DOS newlines! For shame! That messed me up doing my diff.

    On the demo page, it’s annoying having the textarea position change depending on query results. I would put it at the top so it sits still.

    Reply  |  Quote
  3. Luke Maciak UNITED STATES Mozilla Firefox Windows Terminalist says:

    @Chris Wellons: Yeah, sorry about the DOS newlines. I shall apply this to the code, and reposition the text box. :)

    Reply  |  Quote
  4. Adam Kahtava CANADA Google Chrome Windows says:

    Cool potatoes… Have you consider name spacing your JS or binding your variables to a local context. How about http://www.jslint.com :) I’m being picky, but after a month of refactoring other developers JS I can’t turn off my JS gotcha eyes.

    Reply  |  Quote

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>