And of the rest, WebSQL and SQLite:
What are the advantages and disadvantages of each of them?
WebSQL :
-
I regret to inform you that the specification of WebSQL has been canceled by the W3C :
link
" This document was on the W3C Recommendation track but the work has been stopped The specification reached an impasse: all interested implementers have used the same SQL backend ( Sqlite ), but we need multiple independent implementations to proceed along a standardization path. "
In Spanish:
" This document was in the Recommendation W3C track, but the specification job has been stopped The specification reached a dead end: all interested implementers have used the same SQL server ( SQLite ), but we need multiple independent implementations to proceed along a normalization path. "
-
Although there is support on iOS, neither Microsoft nor Mozilla have supported it to date, reference: link
-
It looks a lot like the traditional SQL syntax that we're used to.
-
It's relatively new, little documentation on the network compared to SQLite
Script:
<script type="text/javascript">
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
var msg;
db.transaction(function (tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (1, "foobar")');
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "logmsg")');
msg = '<p>Log message created and row inserted.</p>';
document.querySelector('#status').innerHTML = msg;
});
db.transaction(function (tx) {
tx.executeSql('SELECT * FROM LOGS', [], function (tx, results) {
var len = results.rows.length, i;
msg = "<p>Found rows: " + len + "</p>";
document.querySelector('#status').innerHTML += msg;
for (i = 0; i < len; i++){
msg = "<p><b>" + results.rows.item(i).log + "</b></p>";
document.querySelector('#status').innerHTML += msg;
}
}, null);
});
</script>
HTML:
<div id="status" name="status">Status Message</div>
Reference of the example shown: link
SQLite :
It has enough support, since its launch in 2000, there are hundreds of sites that give you support, examples, documentation, etc, etc, etc. They find it in:
Android7 5
BlackBerry
Windows Phone 8
Google Chrome5
iOS5
Firefox OS
Maemo
MeeGo
Symbian OS5
webOS
Reference: San Wikipedia link
Is there any advantage / inconvenience of using one over the other?
The main advantage is that when using SQLite you have the necessary maturity of the global experience of developers who have made solutions with this platform.
Are there integration problems with any of these platforms?
Yes, with WebSQL : The storage limit space is defined by the browser, link
To learn and manage SQLite , I recommend you visit link
With respect to Apache Cordova
SQLite Plugin
It has 3 variants:
-
cordova-sqlite-storage - is the
core version that includes the implementation of sqlite3 . Supports iOS, Android & Windows platforms.
-
cordova-sqlite-ext - extended version that includes
REGEXP , supported on Android and iOS.
-
cordova-sqlite-evfree - similar
cordova-sqlite-ext but with better memory handling. Available with Commercial license or GPL v3.
More information at: link
Small tutorial on a CRUD :
We create or open a DB:
var myDB = window.sqlitePlugin.openDatabase({name: "mySQLite.db", location: 'default'});
myDB.transaction(function(transaction) {
transaction.executeSql('CREATE TABLE IF NOT EXISTS phonegap_pro (id integer primary key, title text, desc text)', [],
function(tx, result) {
alert("Table created successfully");
},
function(error) {
alert("Error occurred while creating the table.");
});
});
Inserting data:
var title="Fredy Guibert";
var desc="Full Stack Web Developer";
myDB.transaction(function(transaction) {
var executeQuery = "INSERT INTO phonegap_pro (title, desc) VALUES (?,?)";
transaction.executeSql(executeQuery, [title,desc]
, function(tx, result) {
alert('Inserted');
},
function(error){
alert('Error occurred');
});
});
Select
myDB.transaction(function(transaction) {
transaction.executeSql('SELECT * FROM phonegap_pro', [], function (tx, results) {
var len = results.rows.length, i;
$("#rowCount").append(len);
for (i = 0; i < len; i++){
$("#TableData").append("<tr><td>"+results.rows.item(i).id+"</td><td>"+results.rows.item(i).title+"</td><td>"+results.rows.item(i).desc+"</td></tr>");
}
}, null);
});
Update
$("#update").click(function(){
var id=$("#id").text();
var title=$("#title").val();
var desc=$("#desc").val()
myDB.transaction(function(transaction) {
var executeQuery = "UPDATE phonegap_pro SET title=?, desc=? WHERE id=?";
transaction.executeSql(executeQuery, [title,desc,id],
//On Success
function(tx, result) {alert('Updated successfully');},
//On Error
function(error){alert('Something went Wrong');});
});
});
Delete
myDB.transaction(function(transaction) {
var executeQuery = "DELETE FROM phonegap_pro where id=?";
transaction.executeSql(executeQuery, [id],
//On Success
function(tx, result) {alert('Delete successfully');},
//On Error
function(error){alert('Something went Wrong');});
});
Delete the table:
myDB.transaction(function(transaction) {
var executeQuery = "DROP TABLE IF EXISTS phonegap_pro";
transaction.executeSql(executeQuery, [],
function(tx, result) {alert('Table deleted successfully.');},
function(error){alert('Error occurred while droping the table.');}
);
});
Reference: link
If you are using ionic , you will like this tutorial: link and its respective video explaining detail in detail: link