sqlite - Phonegap Offline Database

Question

I want to store some large offline data in user phone (more than 100 MB) in an encrypted database. If possible I also want to distribute the database pre-populated. I have also seen this.

I know about the webdatabase thing, but because it is depreciated, I am advised not to work with that.

I also have seen some third party plugins such as SQLite Plugin, but it works only for iOS and Android devices, but I target 4 platforms (ios, android, blackberry, windows)

Is there any other solution, other than writing down my own?

Answer 1

I made an app recently that required this, targetting the same OS's. You can use a combination of 2 databases :

1. LocalStorage ::

Check for localStorage

function supports_html5_storage() {
  try {
    return 'localStorage' in window && window['localStorage'] !== null;
  } catch (e) {
    return false;
  }
}

Set an item into LocalStorage

localStorage.setItem("bar", foo);

or

localStorage["bar"] = foo;

Get an item from LocalStorage

var foo = localStorage.getItem("bar");

or

var foo = localStorage["bar"];

2. SQLite Database (more convenient, more persistive)

Set up your DB

var shortName = 'BHCAppDB'; 
var version = '1.0'; 
var displayName = 'BHCAppDB'; 
var maxSize = 65535; 
if (!window.openDatabase){ 
     alert('!! Databases are not supported in this Device !! 

 We are sorry for the inconvenience and are currently working on a version that will work on your phone'); 
}
db = openDatabase(shortName, version, displayName,maxSize);
createAllTables(db);

Create your Tables

function createAllTables(db){
    db.transaction(function(transaction){
        transaction.executeSql("CREATE TABLE IF NOT EXISTS Profile(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT, gender TEXT,age INTEGER)");
}

Execute an SQL Query

transaction(function(transaction){
        var rowCount = 'SELECT * FROM Profile';
        transaction.executeSql(rowCount,[],function(transaction,result){
            if(result.rows.length == 0){
                var sqlString = 'INSERT INTO Profile (name,gender,age) VALUES("自己","Female",18)';
                transaction.executeSql(sqlString);

            }
        });
    });

EDIT :: I forgot to add in the last option :)

3. Native Storage on all devices

This is the best part of Phonegap. You can call a native plugin class on all the devices using the Phonegap plugin call. During the call, you can pass parameters to the class, and the native class can store your data in the OS itself.

For example :: in iOS, you create a plugin .h & .m class and register it with the Cordova.plist file. Once that's done, you need to send a call to the class from JavaScript using Phonegap. Once the parameters have been received using NSDictionary or any other NSArray type, you can call a CoreData class to store UNLIMITED amounts of data. You'll never run out of memory .

This can be done in a similar fashion for all the rest of the OS's also :)

For Encryption try the following :: SQLCipher

Here is some additional information on working with an existing SQLite database. In this example encrypted.db is that brand new database you create and pragma.

ATTACH DATABASE 'encrypted.db' AS encrypted KEY 'secret'; -- create a new encrypted database
CREATE TABLE encrypted.t1(a,b); -- recreate the schema in the new database (you can inspect all objects using SELECT * FROM sqlite_master)
INSERT INTO encrypted.t1 SELECT * FROM t1; -- copy data from the existing tables to the new tables in the encrypted database
DETACH DATABASE encrypted;