Storage of SQLite database using Android and Phonegap

Question

I'm developing a Android Cordova/Phonegap app where I want to use a SQLite database. I used the example from the official documentation.

// Wait for device API libraries to load
//
document.addEventListener("deviceready", onDeviceReady, false);

// Populate the database
//
function populateDB(tx) {
    tx.executeSql('DROP TABLE IF EXISTS DEMO');
    tx.executeSql('CREATE TABLE IF NOT EXISTS DEMO (id unique, data)');
    tx.executeSql('INSERT INTO DEMO (id, data) VALUES (1, "First row")');
    tx.executeSql('INSERT INTO DEMO (id, data) VALUES (2, "Second row")');
}

// Query the database
//
function queryDB(tx) {
    tx.executeSql('SELECT * FROM DEMO', [], querySuccess, errorCB);
}

// Query the success callback
//
function querySuccess(tx, results) {
    var len = results.rows.length;
    console.log("DEMO table: " + len + " rows found.");
    for (var i=0; i<len; i++){
        console.log("Row = " + i + " ID = " + results.rows.item(i).id + " Data =  " + results.rows.item(i).data);
    }
}

// Transaction error callback
//
function errorCB(err) {
    console.log("Error processing SQL: "+err.code);
}

// Transaction success callback
//
function successCB() {
    var db = window.openDatabase("Database", "1.0", "Cordova Demo", 200000);
    db.transaction(queryDB, errorCB);
}

// device APIs are available
//
function onDeviceReady() {
    var db = window.openDatabase("Database", "1.0", "Cordova Demo", 200000);
    db.transaction(populateDB, errorCB, successCB);
}

Although this seems to work (the database is created and filled without errors, and I get the written data back with the query), I'm wondering how the database is stored on my device. For debugging I use a hardware phone with Android 4.1.1.

The database is located under /data/data/<myapppackage>/app_database/file__0/0000000000000001.db. Now I wanted to export the database and analyze it manually on my pc with SQLiteManager, but it seems the changes are not written to the db file.

However, when examining the directory /data/data/<myapppackage>/app_database/file__0/ i found the two temporary files 0000000000000001.db-shm and 0000000000000001.db-wal, whose timestamps are changed every time I perform a database operation, but never the db file itself.

My question is, why are the changes never written to the persistent database file? There does not seem to be a way to close a database connection with phonegap, and even killing the app manually doesn't write the changes to the .db file. I'm not sure what I did wrong.

Anyone seeing the problem here?

Answer 1

tx.executeSql('DROP TABLE IF EXISTS DEMO');

This line above deletes the table named DEMO everytime you start your PhoneGap mobile application

And I just wanted to tell you I love your code. It gives a very good clue about "what to do" for anyone's PhoneGap or Cordova application. It will greatly help anyone who is entering the world of SQLite for the first time.

Your code is very clean to read and understand compared to the codes written on Cordova/PhoneGap SQLite plugin official website on GitHub.

My friend, who also works as the CTO of a company, and has a plenty of experience with SQLite, told me that it is not necessary to close a SQLite database connection manually, and also greatly recommended SQLite.

And for anyone else looking for SQLite for PhoneGap/Cordova information -

Let's say you have a table named mytable and want to store values "beautiful" and "dolphin"

When you want to perform an operation on the SQLite of a mobile device, such as a tablet or phone, remember to call it this way

Have the following in your source code

function insertNewLine(tx) 
{
   tx.executeSql("INSERT INTO mytable (word, meaning) VALUES (?,?)", [ var1 , var2 ]);
}

and store "beautiful" inside var1 and "dolphin" inside var2 and

do the following statement in order to execute the SQL insert statement and then save inside the device.

db.transaction(insertNewLine);   

Do not directly call insertNewLine(tx)

Do not directly call tx.executeSql( /* SQL INSERT STATEMENT */ ); in your JavaScript sourcecode

And do not include the values straight into the SQL query statement and then run the SQL statement that includes the values you want to store in the database.

In other words, the following is incorrect

tx.executeSql('INSERT INTO mytable (word, meaning) values (beautiful, dolphin)');

The above is incorrect because the values you want to store, "beautiful" and "dolphin" are included inside the SQL statement. They should be separate.

The following is the correct way to run the INSERT SQL

tx.executeSql("INSERT INTO mytable (word, meaning) VALUES (?,?)", [ var1 , var2 ]);
 // Notice that the values you want to store, beautiful and dolphin
 // are separate from the SQL INSERT INTO statement

and then perform the entire database transaction by including the following in your JavaScript code

db.transaction(insertNewLine);

not the below code

tx.executeSql("INSERT....."); // this will not save your values onto the device

not the below code either

insertNewLine(tx); // this will not save your values onto the device either.  

And to use the SELECT SQL statement, have the following code

// Get all lines in the table
//
function viewthelastglory(tx)  
{
    tx.executeSql( 'SELECT * FROM CUSTOMTABLE', [], querySuccess, errorcode );
}

// Deal with the lines 
//
function querySuccess(tx, results) 
{
   var len = results.rows.length; var  queryresult = "all entries ";

   for (var i = 0 ; i < len ; i++)
   {
       queryresult =  queryresult +  

       " Row - " + i + 
       " Word - " + results.rows.item(i).word + 
       " Meaning - " + results.rows.item(i).meaning;
   }

// and now, you can use the queryresult variable to use the values   
}

function errorcode(errorhaha) 
{
    alert("Error Code " + errorhaha.code + " Error Message " + errorhaha.message);
}

And then, perform the database transaction

db.transaction(viewthelastglory);

If you are trying to choose one from SQLite, WebSQL and IndexedDB, please remember that I searched around stackoverflow for a while and learned that

• Nobody likes IndexedDB because of its complexity
• IndexedDB is incompatible with many types and versions of mobile OS
• WebSQL has been deprecated by W3C
• WebSQL returns 673K results but SQLite returns 1800K results. IndexedDB returns 300K results on Google
• Among IndexedDB, SQLite and WebSQL, SQLite is the only one with an official website.

The following command at the command line while you are in the directory of your Cordova project will install the SQLite plugin into your Cordova project

cordova plugin add https://github.com/brodysoft/Cordova-SQLitePlugin