SQL integration for offline database

nvts

Hello,

I am trying to integrate datable editor into tablet/ipad apps. I am now looking for table/form entry like this product.

I need it to use a sql database that is offline. It will be on the local device.

Then when the user is in wifi range, I will sync/upload the changed data to the office server. I have this already.

Has anyone done this and maybe some example to look at and test would be great. I just want to make sure before I purchase this product.

Thanks

Howard

allan

Hi Howard,

Do you mean an offline database such as HTML5's Web Storage? It is perfectly possible to do using the `ajax` method to override Editor's default of sending an Ajax request to the server - you would 'hijack' that request, and rather than processing it on the server, have it update the local database. That would then need a hook to sync when there is an internet connection.

What might be an idea, is to look at using a third party provider such as Twilio to provide a storage engine in the browser and to deal with sync for you.

Regards,
Allan

nvts

Hello,

Yes. HTML5's Web Storage. I am trying to get the local storage demo at this time. I am not getting it to work yet.

I am trying separate the JavaScript into a separate file and the html in a container (HTML) that will run in the HTML5 container.

What files need to be included for datatables and the editor.

Can you provide the separation for me to test? Once it get this to work, I am going to work on the ajax to a sql local database for this same demo and grow from there.

Thanks

Howard

nvts

Bump, I have tried several ways for this to work using sql local database with javascript. I would like to purchase the editor, but would like to see and test a sample.

Any of the moderators out there :).

Thanks

Howard

allan

There is an example how using localStorage with Editor here: http://editor.datatables.net/release/DataTables/extras/Editor/examples/localstorage.html :-).

Allan

nvts

I want to setup a SQL database not storage. Using the example above, could you show the code for a database. I need to separate the HTML into the framework with the javascript in a file.

Also, which libraries that need to be included.

I really like the editor and will work great.

Thanks Allan for the help.

allan

> I want to setup a SQL database not storage

In the browser? What SQL database are you planning on using?

SQL storage is only supported by Webkit and Opera ( http://caniuse.com/sql-storage ) and is no longer being maintained.

Or do you want IndexedDB - which is supported by IE10, Chrome and Firefox? But not Opera or Safari, or older versions of IE, Firefox, Chrome.

Allan

nvts

I was trying SQL. I know that it might not be supported. But just to try. I would also look at the IndexedDB for later.

Howard

allan

Okay - fair enough :-). You might want to consider an abstraction layer such as this to support both methods: http://code.google.com/p/jquery-jstore/

I don't have an example of this at the moment, and it might be a bit of time before I can put one together as I'm working on a number of other Editor features at the moment - but the basic principle is the same as shown in the localStorage example. You'd intercept the Ajax request and do the database manipulation in the browser, rather than on the server (so it would need code for get, add, edit and delete).

Allan

nvts

Allan,

What would you suggestion be on a database for Android and IOS apps. This is what I am trying to accomplish with my questions. SQLite is what I was wanting to use. Then I could have it sync with a server in the home office.

Thanks

allan

The SQL storage specification is implemented in both iOS and Android browsers and provides everything that is needed for Editor to function (indeed IndexedDB does as well, but isn't in iOS and Android native browsers). It might be dropped in future releases since the spec is no longer maintained, but there isn't a huge amount of choice at the moment.

SQL web storage is basically SQLite, so that would seem to fit your needs.

Allan

nvts

Yes,

What I was looking at was this: http://git.yathit.com/ydn-db/overview

It supports IndexedDB, Web SQL and localStorage storage mechanisms. It is javascript also. I am going to try and work with this for Editor and use the SQLite side for now to have CRUD functions.

Let me know what you think about this for Datatables and Editor use.

Howard

allan

Looks very good - and great documentation as well.

Really any database that allows you to insert, update and delete records can be made to work with Editor - the basic transactions it needs are all very simple since it doesn't use built in aggregation or anything like that in the database.

Let us know how you get on and post back if you have any questions.

Allan

nvts

Allan,

Can you create an example for all to see. I have tried and my demo is up. I would like to purchase and support this product. So when you have time and create a demo to look at and try, I then feel comfortable to purchase the Editor Product.

Thanks
Howard

allan

Hi Howard,

Thanks for the feedback. I've noted this down for an example in a future version of Editor. I can't say when exactly that will be at the moment, but if I have a little bit of time I'll put a demo together an e-mail it through to you.

Regards,
Allan

nvts

Any time yet. A few of us are looking to purchase the editor.

Thanks again, Allan.

allan

Here is a simple example that uses IndexedDB. Its obviously simple in that it is specifically setup for my test case (a trivial to-do list), and doesn't do anything fancy beyond simple CRUD, but it does do full CRUD.

This is a drop in replacement for the localStorage example (indeed it does exactly the same thing but with indexedDB): http://editor.datatables.net/release/DataTables/extras/Editor/examples/localstorage.html .

Also worth noting that I found this polyfill to wrap around WebSQL browsers that doesn't provide IndexedDB: http://nparashuram.com/IndexedDBShim/ .

Obviously this isn't production level code, but it does show how a local database can be used with Editor:

[code]
var editor; // use a global for the submit and return data rendering in the examples

if ( !window.indexedDB ) {
window.indexedDB = window.indexedDB || window.mozIndexedDB || window.webkitIndexedDB || window.msIndexedDB;
}
if ( !window.IDBTransaction ) {
window.IDBTransaction = window.IDBTransaction || window.webkitIDBTransaction || window.msIDBTransaction;
}
if ( !window.IDBKeyRange ) {
window.IDBKeyRange = window.IDBKeyRange || window.webkitIDBKeyRange || window.msIDBKeyRange;
}

var db;
var request = indexedDB.open("todo", 1);
request.onupgradeneeded = function(event) {
var db = event.target.result;

var objectStore = db.createObjectStore("todo", { keyPath: "id", autoIncrement: true });
objectStore.createIndex("item", "item", { unique: false });
objectStore.createIndex("status", "status", { unique: false });
};
request.onerror = function(event) {
console.dir( event );
};
request.onsuccess = function(event) {
db = request.result;
};


$(document).ready(function() {

// Set up the editor
editor = new $.fn.dataTable.Editor( {
"domTable": "#example",
"fields": [ {
"label": "Item:",
"name": "item"
}, {
"label": "Status:",
"name": "status",
"type": "select",
"ipOpts": [
{ "label": "To do", "value": "To do" },
{ "label": "Done", "value": "Done" }
]
}
],
"ajax": function ( method, url, data, successCallback, errorCallback ) {
var transaction = db.transaction(["todo"], "readwrite")

if ( data.action === 'create' ) {
transaction
.objectStore("todo")
.add(data.data)
.onsuccess = function(event) {
successCallback( {"id": event.target.result} );
};
}
else if ( data.action === 'edit' ) {
data.data.id = parseInt(data.id,10); // Use the ID that was submitted
transaction
.objectStore("todo")
.put(data.data)
.onsuccess = function(event) {
successCallback( {"id": event.target.result} );
};
}
else if ( data.action === 'remove' ) {
transaction
.objectStore("todo")
.delete( parseInt(data.data[0],10) )
.onsuccess = function(event) {
successCallback( {"id": null} );
};
}

}
} );

// Initialise the DataTable
var t = $('#example').dataTable( {
"sDom": "Tfrtip",
"aoColumns": [
{ "mData": "item" },
{ "mData": "status" }
],
"oTableTools": {
"sRowSelect": "multi",
"aButtons": [
{ "sExtends": "editor_create", "editor": editor },
{ "sExtends": "editor_edit", "editor": editor },
{ "sExtends": "editor_remove", "editor": editor }
]
}
} );

// Get data fromt he local database and populate the datatable
// This should go into the db success handler
var d = [];
setTimeout( function () {
db.transaction("todo").objectStore("todo").openCursor().onsuccess = function(event) {
var cursor = event.target.result;
if ( cursor ) {
// Rows need an ID
d.push( $.extend( true, {}, cursor.value, {DT_RowId: cursor.value.id} ) );
cursor.continue();
}
else {
if ( d.length > 0 ) {
t.fnAddData( d );
}
}
};
}, 500 );
} );
[/code]

Allan

allan

Did you get a chance to try this? How did it go for you?

Allan