Google Gears Database Integration

tacman1123

Has anyone experimented with using the Google Gears database with DataTables? Obviously, it could trivially be done by loading an array from the Gears database (SQLite) and passing it to init, but that duplicates the data in memory, and doesn't take advantage of the fact that we have all the data on disk (with blazing speed).

The idea situation have background syncronization, so that data manipulated locally or on the server would be automatically synced, and an application could be run offline, but that's a different issue. In the meantime, easily being able to point to a Gears db would be a cool feature, since the table definitions give us relevant information about the data type, size, etc.

If this has already been done, can someone point me to it? I didn't see anything in these forums about it.

Thx,

Tac

allan

Hi Tac,

Sounds like quite an interesting suggestion. This isn't something that I've looked into yet (anyone else?). Would it actually be any faster doing it this way - since it has all it's information in RAM at the moment, while doing it through Gears would require an SQL lookup.

Using SQL could actually do all the sorting, filtering etc for us, so there would be no need for a lot of what DataTables provides, other than the event handlers - which would be quite nice. I suspect it would not be easy it integrate with Gears as it stands, since there would be no need for much of my internal storage - which of course is referred to all over the place, also the DOM interaction would require a large amount of rework (it might even have to be destructive interaction). However, it is an interesting idea for an abstraction layer.

Regards,
Allan

tacman1123

Exactly -- giving how much Gears DB provides, I could see just using the UI and completely replacing the data management.

The bigger challenge is providing a hook for queuing data changes back to the server. That is, updating the dataTable, which would update the sqlite data and push the update into a queue to be sent back to the server. There's an introductory article that shows how to do this at

http://www.onlamp.com/pub/a/onlamp/2007/06/28/the-power-of-google-gears-part-1.html

If you've got the time, there's a good talk, with slides, at

http://www.parleys.com/display/PARLEYS/Home#talk=13467905;slide=4;title=Offline%20web%20apps%20with%20Google%20Gears



Part 2 has a better solution for storing database updates that need to get pushed to the server.

I believe HTML5 will have native database support, so eventually dataTable will undoubtedly want to support it.

While not many consumers have Google Gears installed, my guess is that dataTable is much more oriented to the business environment, where having access to a real database (rather than manipulating data in the DOM) far outweights the effort it takes to install gears.

I'm moving toward requiring Gears for all my clients, which will ultimately give them lighting-fast access to their data, as well as off-line access. 95% of the data they're looking at doesn't need to be realtime, and most data (like stats and history) never changes once it's added.

Tac

marlar

I wish to give my 5 cents here ...

Google Gears is a nice idea. BUT it should be an option, not the only data storage mechanism. What I really like about DataTables, and jQuery in general, is that it works on most modern browser without any fuzz. No need to install anything.

That said, I can imagine several situations where Gears would be the logical way to go. Just not the only option!

Martin

tacman1123

Agreed. It could be phrased that it should use Google Gears if enabled and requested. But then the issue becomes size -- if you use Gears for the data storage and filtering, should all the code for Ajax and maintaining it internally also be loaded? Probably not, so maybe some sort of conditional loading?

Alternatively, if the table UI is separated completely from the data, you could just load exactly what you needed. In fact, that's sort what I was looking for when I found this -- a datagrid with hooks.

Tac

marlar

The data storage mechanism could be a plugin? Choose what you want, or make your own .. ?

allan

It's certainly an interesting idea abstracting out the events, features and the database to sit apart from each other. In this way any data source can be used (as points out it already can be, but not internally). However, there are a few issues I see, mainly the DOM interaction. At the moment DataTables uses the DOM as a storage method itself (thus events etc are preserved), it also uses Javascript arrays and objects for speed (I would use the DOM, but damn it's so slow...).

If the framework were to use Gears or any other external data source, it would need to re-create the DOM on every draw. This is what DataTables 1.3 (and before) did - and it was a major problem for heavy Javascript work since you had to apply the listeners all the time. Using a server-side data source for filtering / pagination etc would have exactly the same problem.

Also, isn't Gears database local? So you would need to get the data from the server, load it into the Gears database, and then use it for manipulation. While SQLite is damn fast (I love it - use it for embedded systems), it would seem to me that this initial overhead is a little somewhat high, rather than just reading the DOM into a Javascript array.

Allan

tacman1123

If the load were done on every script, then certainly it'd be too much overhead. But the application I'm thinking of would have syncing done in the background, and always read from the local sqlite db. For example, I have a large news-clipping site, every day we add a few hundred stories. I'd like to build a off-line application that would allow people to browse and search stories, much like we have on the website.

Right now the plan is a simple list, where clicking on each story would go to the database and pull up the story. But the nature of this application is that all the data could be read from a local cache, and something in the background would periodically check for the presence of the network and new stories. This is the same model as Google Reader, (http://reader.google.com). What I'd like to do is replace my list and form with dataTable, and get all the built-in formatting, sorting and filtering.

In my application, I don't really want the overhead at all of "live" data. A mail client is really the same way, it's never actually "live" data (except in web apps!), it's always presenting data that comes from a local storage mechanism.

Tac

quinode

I've got a local storage working right now:
It uses http://www.jstorage.info/
It's ultra-basic but it works, see below

The problem is that it's no use with a dataset updated regularly . I mean : if I have to poll the server to see if my data has changed on every page load, where's the benefit (one XHR call, ok, it can save me a +2000 json records download but...)

Anyway, that's useful for dev testing, you just call $.jStorage.flush() to reset and call your JSON data again

[code]

function load_list(){
$.getJSON('/server/list/', function(response){
liste = response.aaData
$.jStorage.set("list",list);
});
}

$(function(){

var mylist = $.jStorage.get("list");
if(!mylist){
load_list()
var mylist = $.jStorage.get("list");
}

$('#table').dataTable({
"aaData": mylist,
.....
[/code]