My makeshift 'solution' to using Google Gears DB with DataTables

nghianja

I have been searching and googling for an example on how to use Google Gears database for datatables and switch between online server query and gears database query but found none so I hacked out my own for the project I am working on right now. Pardon me for not being able to show you the working website and only can show the part of code which I think you will find it useful. It is based on the example given in this website. (Due to the character limit for a post, the code continues in the next entry.)

[code]
$(document).ready(function() {
var MyTable = $('#example').dataTable( {
"bAutoWidth": false,
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "../examples_support/server_processing.php",
"fnServerData": function ( sSource, aoData, fnCallback ) {
if (localServer.openManagedStore(STORE_NAME) == null) {
$.ajax( {
"dataType": 'json',
"type": "POST",
"url": sSource,
"data": aoData,
"success": fnCallback
} );
}
else {
fnCallback(eval('(' + getGearsData(aoData) + ')'));
return;
}
}
} );
}
[/code]

'localServer' is the gears managed store I used to indicate whether the site is offline or online. 'localDatabase' is the gears db synchronised.

nghianja

Continued from above...

[code]
function getGearsData(aoData) {
var ColumnToField = ["engine", "browser", "platform", "version", "grade"];
var sEcho = "";
var iColumns = "";
var sColumns = "";
var iDisplayStart = "";
var iDisplayLength = "";
var sSearch = "";
var bEscapeRegex = "";
var iSortingCols = "";
var iSortCol_ = [];
var iSortDir_ = [];

for (var i in aoData) {
if (aoData[i].name == "sEcho") sEcho = aoData[i].value;
else if (aoData[i].name == "iColumns") iColumns = aoData[i].value;
else if (aoData[i].name == "sColumns") sColumns = aoData[i].value;
else if (aoData[i].name == "iDisplayStart") iDisplayStart = aoData[i].value;
else if (aoData[i].name == "iDisplayLength") iDisplayLength = aoData[i].value;
else if (aoData[i].name == "sSearch") sSearch = aoData[i].value;
else if (aoData[i].name == "bEscapeRegex") bEscapeRegex = aoData[i].value;
else if (aoData[i].name == "iSortingCols") iSortingCols = aoData[i].value;
else if (aoData[i].name.indexOf('iSortCol_') > -1) iSortCol_[iSortCol_.length] = aoData[i].value;
else if (aoData[i].name.indexOf('iSortDir_') > -1) iSortDir_[iSortDir_.length] = aoData[i].value;
}

/* Paging */
var sLimit = "";
if (iDisplayStart != "") {
sLimit = " LIMIT " + iDisplayStart + ", " + iDisplayLength;
}

/* Ordering */
var sOrder = "";
if (iSortCol_[0] != null && iSortCol_[0] != "") {
$sOrder = "ORDER BY ";
for (var i = 0; i < iSortCol_.length; i++) {
sOrder += ColumnToField[iSortCol_[i]] + " " + iSortDir_[i] + ", ";
}
sOrder = substr_replace(sOrder, "", -2);
}

/* Filtering */
var sWhere = "";
if (sSearch != "") {
sWhere = "WHERE engine LIKE '%" + sSearch + "%' OR browser LIKE '%" + sSearch +
"%' OR platform LIKE '%" + sSearch + "%' OR version LIKE '%" + sSearch +
"%' OR grade LIKE '%" + sSearch + "%'";
}

var sQuery = "SELECT COUNT(id) FROM ajax " + sWhere;
var aResultFilterTotal = localDatabase.execute(sQuery);
var iFilteredTotal = 0;
if (aResultFilterTotal.isValidRow()) {
iFilteredTotal = aResultFilterTotal.field(0);
}
aResultFilterTotal.close();

sQuery = "SELECT COUNT(id) FROM ajax";
var aResultTotal = localDatabase.execute(sQuery);
var iTotal = 0;
if (aResultTotal.isValidRow()) {
iTotal = aResultTotal.field(0);
}
aResultTotal.close();

sQuery = "SELECT id, engine, browser, platform, version, grade FROM ajax " + sWhere + sOrder + sLimit;
var rResult = localDatabase.execute(sQuery);
var sOutput = '{';
sOutput += '"sEcho": ' + sEcho + ', ';
sOutput += '"iTotalRecords": ' + iTotal + ', ';
sOutput += '"iTotalDisplayRecords": ' + iFilteredTotal + ', ';
sOutput += '"aaData": [ ';
while (rResult.isValidRow()) {
sOutput += "[";
sOutput += '"' + addslashes(rResult.field(0)) + '",';
sOutput += '"' + addslashes(rResult.field(1)) + '",';
sOutput += '"' + addslashes(rResult.field(2)) + '",';
sOutput += '"' + addslashes(rResult.field(3)) + '",';
sOutput += '"' + addslashes(rResult.field(4)) + '",';
sOutput += '"' + addslashes(rResult.field(5)) + '",';
sOutput += "],";
rResult.next();
}
rResult.close();
sOutput = substr_replace( sOutput, "", -1 );
sOutput += '] }';

return sOutput;
}

function syncGearsData() {
var dbcount = 0;
textOut("Now populating data...");
$.getJSON("data/products_gears.php", function(dataarr) {
localDatabase.execute('BEGIN');
localDatabase.execute('DROP TABLE IF EXISTS examples');
localDatabase.execute('CREATE TABLE IF NOT EXISTS examples (id text, engine text, browser text, platform text, version text, grade text)');

for (x in dataarr) {
localDatabase.execute('INSERT INTO examples (id, engine, browser, platform, version, grade) VALUES (?, ?, ?, ?, ?, ?)',
[dataarr[x].id, dataarr[x].engine, dataarr[x].browser, dataarr[x].platform, dataarr[x].version, dataarr[x].grade]);
dbcount++;
}

localDatabase.execute('COMMIT');
textOut("Finished populating data (" + dbcount + " records)...");
});
}
[/code]

allan

Hi nghianja,

That's superb! Thanks very much for sharing your solution with us. I've always thought it would be great, and very interesting to see a Gears solution - knowing that it would be possible, but having never done it. I like the fall back to the web based database call as well :-)

I've been planning at some point to make an "examples gallery" of server-side scripts for sourcing data - PHP, Perl, C# etc, so people can quickly see what is needed in their choice of platform. Would you mind if I included this code (with credit of course!) in that gallery (when I get around to it...)?

Regards,
Allan

nghianja

Hi Allan,

Please do make use of the code. I can't thank you enough for creating datatables in the first place. Just consider my insignificant contribution as my way of showing my appreciation to your work.

Best Regards,
James