Recently I have been working on a client project which required the ability to store data from a server on the users local machine. In this case the users local machine was a mobile phone, IOS, Android, Windows etc.
It does not really matter on the platform as it will be stored using the mobile phones web browser and most of the browsers are modern enough to have the new HTML5 Web Storage mechanism.
So for all intents and purposes it is like writing a responsive website that works on mobile phone browsers but is actually compiled and runs like a real native software application. PhoneGap interfaces with HTML5 Web Storage for the purposes of using HTML5 Web Storage mechanism.
So to store the data locally I found WebSQL and HTML5 Web Storage to be my options. Although WebSQL looked perfect for storing and querying the data, it is actually being depreciated and will no longer be in use as a standard in future. So my mind was made up for me in that case.
Web Storage on the other hand seems very straightforward, but this would be the case as it does not include the more complex features of querying the data. Web Storage works by storing name / value pairs. So we can say colour = red, or age = 25, name / value.
Like I said this is straightforward but we cannot say give me all persons over the age of 20 for example there is no querying mechanism.
In actual fact there is no mechanism for even storing multiple persons with ages to begin with. The real structured data is left to the programmer to implement and like wise with querying the structured data.
So what approach did I use?
I decided to store the value of the data as a JSON string and the name would be the table name. So for my project this data needed to be fetched from the web server and stored locally using the Web Storage.
I setup a PHP web server that returned a JSON response upon request via the client. The client did this using JQuery standard Ajax request functions. When I got this JSON response from the server which was basically a dump of a particular database table on the server in JSON format.
I stored it in local storage using the particular database table name for the name value. So in the persons / ages example this would be Person = Encoded JSON string of all data in the server Person table.
So now we have all of data for a particular table from the server stored in local storage. I just repeat this process for all the tables I want to store locally. We now have our data.
But what about querying this data?
Well JQuery does not come, out of the box anyway, with functions for querying JSON data. So a developer might think, I know, I will decode the JSON string and write my own functions for querying this data, possibly by putting the data into foreach loops and returning the records when the data matches the developers expectations. Well that is one way to do it however I am not sure how efficient that would be.
Anyways enough talk of how querying the decoded JSON data might be handled at a lower level. We as developers have libraries for this kind of thing and luckily someone has written a plugin to do this task anyway called json-sql. Here is the link to the library …
The json-sql library allows the developer to query a JSON string using SQL like syntax. Admittedly it only includes a very small subset of the standard SQL functions but it is most certainly good enough for most purposes, selecting, ordering and where clause. So I used this library to query my JSON data.
So that is pretty much it, the project has a web server which sends JSON responses to the client, data is stored in JSON strings in the client local Web Storage and the data is queried using the json-sql library before being displayed to the user.
This is the way I implemented a server / client, DB synchronized, local storage web software application.