33

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?

noway
  • 2,585
  • 7
  • 41
  • 61

5 Answers5

35

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 !! \n\n 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;
SashaZd
  • 3,315
  • 1
  • 26
  • 48
  • 3
    By the way, this (ie LocalStorage and SQLite) is working in WP8 and WP7, iOS and Android. Tried and tested successfully ! (I'm adding this comment since you said, that you couldn't get the SQL to work on WP.) – SashaZd Apr 04 '13 at 12:57
  • Did this (or any of the other solutions) work ? The bounty time ends in 13 hrs. – SashaZd Apr 10 '13 at 17:42
  • Does the code to SQLite create a database and can you reuse the database created on the first startup of the app? – Lunatikzx Apr 18 '13 at 13:24
  • 1
    Yes. The database file you create, will be stored in the Documents Directory of the app on the iOS (and I assume in a similar location on Android, I'm not sure). When you open the app next time, it refers to the same db file as when created for the first time. – SashaZd Apr 18 '13 at 16:29
  • So updating only requires us to change the version of the database code ? – Lunatikzx Apr 18 '13 at 17:48
  • 1
    LocalStorage is really convenient, but hardly a solution here given that the question states that > 100mb files will be stored. LocalStorage has a limit of 5mb total (unless I'm missing some secret PhoneGap trick to get around this). – Adam May 14 '14 at 01:45
  • 1
    @Adam Yes, that's actually why I gave a combination answer. You could use LocalStorage as a lookup of sorts, but store the actual data in SQLite or using the Native storage plugin like I've mentioned above. – SashaZd Aug 11 '14 at 23:11
  • 1
    The combination approach is great, but in the answer it's not clear that you would need to combine LocalStorage with one of the other two, or that LocalStorage has a pretty small size limit that is much smaller than required in the question. I just wanted to make it more clear :) – Adam Aug 12 '14 at 14:33
  • Dobyou have a tutorial on encrypted site for apps? – WJA Mar 30 '15 at 21:04
  • What about if I want to use pre populated database in windows phone 8.1, iOS and android? – Uttam Kukadiya Apr 07 '16 at 04:54
  • Thanks for your anwer. Please Do you have more info , links about Native Storage on all devices – JoCuTo Mar 14 '17 at 08:45
4

In the W3C specification for webdatabase it is mentioned that the Web Applications Working Group continues work on two other storage-related specifications: Web Storage and Indexed Database API.

So the webdatabase specification is no longer active but the other two specifications are active.

The Web Storage can be used to store data locally within the user's browser. There are the following objects to achieve that:

  • localStorage which stores data without expiration date
  • sessionStorage which stores data for one session

The Web Storage is not recommended for your case (more than 100MB), because the W3C specification mentions that:

A mostly arbitrary limit of five megabytes per origin is recommended.

In my opinion SQLite is the best available option since it is a in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. Moreover the SQLite limits seems to cover your needs:

The largest possible setting for SQLITE_MAX_PAGE_COUNT is 2147483646. When used with the maximum page size of 65536, this gives a maximum SQLite database size of about 140 terabytes.

Regarding your encryption requirements you should consider the SQLCipher which is an SQLite extension.

SQLCipher is an SQLite extension that provides transparent 256-bit AES encryption of database files. To date, it has been open-sourced, sponsored and maintained by Zetetic LLC. In the mobile space, SQLCipher has enjoyed widespread use in Apple’s iOS, as well as Nokia / QT for quite some time.

An alternative option is to encrypt and decrypt your data when writing and reading your database.

I hope this helps.

Aurelio De Rosa
  • 21,856
  • 8
  • 48
  • 71
Apostolos Emmanouilidis
  • 7,179
  • 1
  • 24
  • 35
  • 2
    This is a very generalized answer. Doesn't specify how to make it work for Android or Windows Phone, as the question has asked. – SashaZd Apr 04 '13 at 13:08
1

The mobile app I am working on has a similar requirement. It requires offline access to a parts table that contains nearly 500,000 different parts in it. The source for this table is extracted from the server by getting its JSON via a well defined GET URL.

I considered Indexed DB but the mobile browsers inside iOS and Android don't support this. Web local storage is not an option because of its hard 5 MB limit. So, I decided to use the Web SQL Database standard (http://www.w3.org/TR/webdatabase/) even though its deprecated. My experience so far with using Web SQL Database has been very good. Database operations perform very well and are very reliable on the mobile devices I support (iPad 2, iPad 3, Motorola Xyboard, Samsung Galaxy Tab 2). Plus, Phonegap exposes a JavaScript API to work with this standard (see http://docs.phonegap.com/en/2.5.0/cordova_storage_storage.md.html#Storage).

I wrote a Java utility that converts the downloaded JSON data into a SQLite database whose files are packaged as part of the Android APK or the iOS app package.

When my Phonegap mobile app starts, it uses native code to check the app's private data directory for the presence of the SQLite database files. If the files are not present, the native code copies the database files from the app package.

My implementation is based on the sample code I found at the link below. I hope this helps. Let me know if you have any questions about my particular implementation.

http://gauravstomar.blogspot.com/2011/08/prepopulate-sqlite-in-phonegap.html

jkwuc89
  • 1,345
  • 14
  • 20
1

I tried using LokiJS as a local database, and found it helpful in non-relational data. In my case I retrieve a data stored using MongoDB on the server, but it depends on the nature of your system

Stepan Novikov
  • 1,402
  • 12
  • 22
Ahmed Wahba
  • 96
  • 1
  • 7
0

See those questions/answers:

Community
  • 1
  • 1
UBIK LOAD PACK
  • 33,980
  • 5
  • 71
  • 116
  • if you think this is a duplicated question, flag it. Your answer shouldn't just be a referrence to other q&as and nothing else. – SashaZd Apr 09 '13 at 12:56
  • at the very least, choose the q&a set that you think best answers this question and redirect there. Or post in the best choice as the answer. Don't just link in all possible answers. – SashaZd Apr 09 '13 at 12:56