Database storage on Android and iOS with Cordova

Question:

I have developed applications for Android mobile devices with Cordova that have used localStorage to save the data, because they were small amounts (configuration, records and high scores, etc.) and, so far, I have not had any problems.

I'm now developing a little trivia game that would be published on Android and iOS, and I'm going to have a lot of data (questions and answers). My idea was to download the data from a server when you first start the game and save it to an internal database where I can perform operations with it.

Different options are mentioned on the Cordova storage web page :

  • localStorage
  • WebSQL
  • IndexedDB
  • SQLite (via plugin)

Of those I have discarded IndexedDB (because according to the documentation it does not work on iOS) and localStorage (because space is limited and I am afraid that the device will erase the data if it needs space).

And of the rest, WebSQL and SQLite:

  • What are the advantages and disadvantages of each of them?
  • Are there any advantages / disadvantages of using one over the other?
  • Are there integration problems with any of these platforms?

And while I know that this part can be a bit out of scope for the site, is there an alternative that is not mentioned on that web page that I should consider?

I would appreciate if code samples could be added for basic operations: create database and tables, insert data, and search data.

Answer:

And of the rest, WebSQL and SQLite:

What are the advantages and disadvantages of each of them?

WebSQL :

  • I WebSQL sorry to inform you that the WebSQL specification has been dropped by the W3C : https://www.w3.org/TR/webdatabase/

    " This document was on the W3C Recommendation track but specification work has stopped. The specification reached an impasse: all interested implementors have used the same SQL backend ( Sqlite ), but we need multiple independent implementations to proceed along a standardization path. "

    In Spanish:

    " This document was on the W3C Recommendation track, but specification work has 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 path of normalization. "

  • Although there is support in iOS, neither Microsoft nor Mozilla has supported it to date, reference: http://caniuse.com/#search=websql

  • It looks a lot like the traditional SQL syntax that we are used to.

  • It is relatively new, little documentation on the net 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: http://www.tutorialspoint.com/html5/html5_web_sql.htm

SQLite :

It has a lot of 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 https://es.wikipedia.org/wiki/SQLite

Are there any advantages / disadvantages of using one over the other?

The main advantage is that by using SQLite you have the necessary maturity of the global experience of developers who have made solutions with that platform.

Are there integration problems with any of these platforms?

Yes, with WebSQL : The storage space limit is defined by the browser, http://www.html5rocks.com/en/tutorials/offline/quota-research/

To learn and handle SQLite , I recommend you visit http://www.tutorialspoint.com/sqlite/index.htm

Regarding Apache Cordova

SQLite Plugin It has 3 variants:

  • cordova-sqlite-storage – is the core version that includes the sqlite3 implementation. Supports iOS, Android & Windows platforms.
  • cordova-sqlite-ext – extended version including REGEXP , supported on Android and iOS.
  • cordova-sqlite-evfree – similar to cordova-sqlite-ext but with better memory handling. Available with Commercial or GPL v3 license.

More information at: https://cordova.apache.org/docs/en/latest/cordova/storage/storage.html#plugin-based-options-filesystem-api

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.");
    });
});

Insertando 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: https://phonegappro.com/tutorials/phonegap-sqlite-tutorial-with-example-apache-cordova/

If you are using ionic , you will like this tutorial: https://www.thepolyglotdeveloper.com/2015/12/use-sqlite-in-ionic-2-instead-of-local-storage/ and its respective video explaining detail in detail: https://www.youtube.com/watch?v=F6TUtN0VYwY

Post Views: 0
Scroll to Top