Local client side SQL

01Jan2012

As promised on twitter yesterday I'll release a small SQL "handler" for local client side databases.
The reason why I started to work on this was that I wanted to make something that uses window.localStorage, and I didn't want to make another small game so instead I opted to make a SQL like interface for storing data in the users localStorage. Needless to say, you don't want to store anything secret on in this DB nor do you want to trust the data, everything is compromizable by the user. The db with it's data and structure is saved as a JSON encoded string in the localStorage (or wherever you've specified it to be stored).
But you might want to use it for caching of non-important data or maybe to save things for offline storage.
What the handler supports at the moment is SELECT, INSERT, UPDATE as well as DROP, ALTER and CREATE. there is also support for WHERE and LIMIT for DELETE, SELECT and INSERT and finally ORDER BY for SELECT.
The code isn't the most beautiful thing ever, and alot of the regex's are questionable (regex has never been the thing I've been good at) but at least it works, somewhat :D
All the documentation, usage and notes are in the .js file. I might work on it a little more, so lets call this version 0.1 :)

You can try it out here

Download it (with example): localsql.zip (10.94 kB)

Attached code:

12345678910111213141516

<script type="text/javascript">

   //  Make connection

   db = new localsql( window.localStorage, 'myDB');

   //  Create table, only needs to be done once per user

   //  it will be saved in the localStorage so if user refreshes

   //  the data will still be there.

   db.query('CREATE TABLE users(id AUTOINCREMENT, username STRING, firstname STRING, lastname STRING, email STRING, phone INT)');

   //  Insert our first user

   db.query('INSERT INTO users(username, firstname, lastname, email, phone) VALUES("JohnD", "John", "Doe", "John@example.com", "1234567890")');

   //  Select everything out of the users table

   db.query('SELECT * FROM users');

   //  Fetches the row

   console.log(db.fetchrows());

</script>