Wednesday, 28 July 2010

SQLike – a small query engine

SQLike is a small (10 kB) query engine for JavaScript and ActionScript. Its functionality and syntax is similar to that of SQL and it can be used to query arrays of objects or arrays of arrays. Download a zip-file (17 kB) with all language versions of SQLike. SQLike demo and short reference. Here you can learn how to use SQLike.

Purpose Data structures in JavaScript and ActionScript are JSON:esque and sometimes deeply nested. When they are nested into XML-like structures a query language such as JSONPath (XPath for JSON) is a sound approach. But sometimes you might also use flat table-like structures. In those cases a more SQL-like approach can be more suited for searching, combining and updating data. The purpose of SQLike is to provide a tool for this approach that can be used on the clientside in all browsers (even when you do not have access to SQLite that is now getting to be part of some browser environments AIR, Safari etc.). SQLike is much slower than SQLite, but a handy alternative for small amounts of data.

SQLike is kind of like SQL SQLike supports language elements like SELECT, SELECT DISTINCT, WHERE, GROUP BY, ORDER BY, LIMIT, HAVING, UNION, JOINS (a whole bunch of them), INTERSECT, EXCEPT, UPDATE and INSERT INTO. You will also find aggregate functions like COUNT, SUM, MIN, MAX and AVG. So if you're familiar with SQL you will not be lost, however you will have to adopt to SQLike syntax. Because...

SQLike is not SQL SQLike uses SQL-like syntax but not SQL syntax. In SQLike you write your queries as literal objects and the where and set keywords takes JS/AS-functions as arguments. Furthermore you can include JS/AS-functions in your select statements as well. Another difference is that you don't create or alter tables. Since they are identical to arrays there is no need they are easily created outside SQLike. Furthermore you don't index tables. This might be possible to add to the engine, but I'm not sure if the speed benefits would be huge or how to code an effective index engine as long as the where statement is allowed to be a JS/AS-function, something that I truly like about the API.

The same code base in JavaScript and ActionScript SQLike consists of ECMA-262 code that is identical in JavaScript, ActionScript 2 and ActionScript 3. The only difference between the languages is the opening lines defining SQLike as an object in JavaScript, a class in AS2 and a class inside a package in AS3. All code inside the function q is identical. It has been compressed using the YUI Compressor. The uncompressed version of SQLike is also included in the zip file if you want to play around with it and/or help me develop SQLike further.

Speed is JavaScript faster the ActionScript 3? It's rather interesting to compare the query speed between different browsers/JavaScript engines, ActionScript 2 and ActionScript 3 (Flash player 10).In my case (running Windows on a dual core laptop) JavaScript is fastest in Google Chrome 5 (more than twice as fast as AS3), still faster than AS3 in Safari 4. Also Opera 10.5 beats AS3 most of the time. Firefox 3.6 is more or less on par with AS3, and IE8 is sloooow - but slightly faster than AS2.The JavaScript engine in Chrome V8 is amazingly strong. Makes you wonder what Adobe gained on all the "staticness" they imposed on ECMA-script in the Tamarin engine/AS3. Obviously it wasn't necessary for speed...

No comments:

Post a Comment