Wednesday, December 26, 2012

Starting JSONBus: towards a replacement for Cordova/PhoneGap

Security warning 2015


The Cordova project has been fixing security issues in its external domain whitelist plugin over time, and has also dealt with security issues in its internal bridging mechanism. It is very strongly advised NOT to release an app with a custom-built web view bridging or domain whitelisting mechanism without proper code review and testing.

Hybrid architecture for mobile apps

Cordova/PhoneGap provides a framework to develop mobile applications in HTML5, Javascript, and CSS with common APIs to access native device functionality on a number of mobile platforms. Native device functionality that is not built into the Cordova/PhoneGap core can be accessed by using & adapting plugins for the desired platforms. The API function calls between Javascript and the native access classes are almost always asynchronous.

AFAIK there are a couple important things at an itty-bitty level:
  • All native access classes actually subclass from a Cordova plugin class; and
  • All function calls and callbacks are sent between JavaScript & native classes in string form. Function call & callback parameters are generally sent as numbers, strings, or JSON objects.
Despite such a simplistic asynchronous API model, Cordova/PhoneGap has become an increasingly complex framework with a very busy forum.

Simplification: a flow-based architecture

Last year I discovered the concept of flow-based programming which seems to have been used & refined over the past 40 years. Flow-based programming (FBP) breaks a large-scale application architecture down to independently threaded components that communicate via data packets, and there are some nice GUI tools to assemble FBP programs. I like the idea very much, however I have two major issues:
  • Each component is expected to be written as an infinite loop that receives its input as blocking reads; and
  • The data packet format & interface is relatively complex in order to accomodate data that is structural in nature.
I am now in the process of developing an idea called "JSON Flow" to design a flow-based architecture based on JSON messages.

UPDATE (June 2015): The "JSON Flow"/"JSON Bus" idea presented here is inspired by but different from FBP since it relies on (asynchronous) two-way message-based communication.

JSON Bus for a simplified asynchronous API

A subset of the "JSON Flow" idea is "JSON Bus" where Javascript code can send an asynchronous API request as a JSON message and receive a response via a callback. Here is a sample JSON Bus message for a DatePicker request:
{
  from: 'datepickerdemo',
  to: 'datepicker',
  data: {
    mode: 'date', 
    date: '12/12/2012/12/12'
  }
}
Here is a sample callback message:
{
  from: 'datepicker',
  to: 'datepickercb',
  data: {
    date: '12/13/2014'
  }
}


JSON Bus sample for Android

A sample of this idea for Android is available in brodyspark / JSONBus-sample-Android (in public domain). The application contains the following tests:
  • Quick JSON echo test;
  • Open a native DatePicker, which is adapted from the Cordova/PhoneGap DatePicker plugin for Android and display the selected date in the WebView.

Here is a quick tour of the existing sample code:
The first version of the JSON Bus sample does not follow the format shown above, however this should be fixed very soon.

This JSON Bus sample demonstrates an ability to send JSON messages in API function calls & callbacks and integrate some native GUI functionality with  application code developed using HTML5 & Javascript technology.

A first JSON Bus sample version for iOS

As a proof-of-concept, I hacked together a sample version in brodyspark / JSONBus-sample-iOS-with-wvjsbridge, using ExampleApp from marcuswestin / WebViewJavascriptBridge. In this sample, the HTML/Javascript sends a request with JSON data to open a native iOS date picker, which will send a result with JSON data when the user has selected the desired date. UPDATED: This sample is in a very crude state and will be replaced by something better in the near future is now abandoned in favor of: https://github.com/brodyspark/JSONBus-sample-iOS (made without WebViewJavascriptBridge and in public domain)

Next steps

Upcoming steps for the Android version include:
  • Fix existing samples to follow the message format given above;
  • A JSON message routing handler in Java, both for function calls from Javascript and for callbacks;
  • JSON message routing handler in Javascript;
  • Addition of a "subscription" model, where Javascript can make a single JSON Bus function call & continue receiving callbacks based on native events;
  • Adapter classes to include & build Cordova/PhoneGap plugin classes, including some built-in functionality, with little modifications if any.

For iOS the sample should really be is now rebuilt without the WebViewJavascriptBridge (see brodyspark / JSONBus-sample-iOS). I found a couple excellent links how to make function calls back and forth between Javascript & Objective-C here and also here. Then the enhancements from the Android version can be applied to the iOS version.

Monday, December 24, 2012

Using SQLCipher for Android with Cordova/PhoneGap

NOTICE (June 2015): These instructions are completely out-of-date, the following Cordova plugin supports sqlcipher out-of-the-box: https://github.com/litehelpers/Cordova-sqlcipher-adapter

The project brodyspark / PhoneGap-SQLitePlugin-Android provides native sqlite database access with an API that is very close to the HTML5 SQL API and can also provide an interface to SQLCipher for Android. Here are the steps to use SQLCipher with a Cordova/PhoneGap project for Android.


Install sqlite plugin for Android


The first step is to install but not build the sqlite plugin for Android. A good starting point is the Cordova/PhoneGap Android example project, found in the lib/android/example subdirectory of Cordova/PhoneGap as downloaded from phonegap.com/download. These steps are also covered in README.md of brodyspark / PhoneGap-SQLitePlugin-Android. Here are the steps in brief to install the sqlite plugin from PhoneGap-SQLitePlugin-Android:

  • Install SQLitePlugin.js from the Android/assets/www subdirectory into assets/www; for example:
$ cp -v ../PhoneGap-SQLitePlugin-Android/Android/assets/www/SQLitePlugin.js assets/www
  • From the Android/src subdirectory copy the tree com/phonegap/plugin/sqlitePlugin with SQLitePlugin.java into src; for example:
$ cp -rv ../PhoneGap-SQLitePlugin-Android/Android/src/com src
  • Add the plugin element to res/xml/config.xml:
--- config.xml.old  2012-07-24 19:44:49.000000000 +0200
+++ res/xml/config.xml  2012-07-24 19:39:43.000000000 +0200
@@ -32,6 +32,7 @@
     <log level="DEBUG"/>
     <preference name="useBrowserHistory" value="false" />
 <plugins>
+    <plugin name="SQLitePlugin" value="com.phonegap.plugin.sqlitePlugin.SQLitePlugin"/>
     <plugin name="App" value="org.apache.cordova.App"/>
     <plugin name="Geolocation" value="org.apache.cordova.GeoBroker"/>
     <plugin name="Device" value="org.apache.cordova.Device"/>


Install prebuilt SQLCipher for Android


  • Download SQLCipher for Android from android-database-sqlcipher / downloads & unzip the package. Note that the package seems to contain an extra __MACOSX subdirectory which can be ignored.
  • Copy the contents of the lib subdirectory from the package into the libs subdirectory except for commons-codec.jar, for example:
$ cp -rv SQLCipher\ for\ Android\ 2.1.1/libs/* libs
$ rm libs/commons-codec.jar
  • Install the ICU file icudt46l.zip from the assets subdirectory in assets, for example:
$ cp SQLCipher\ for\ Android\ 2.1.1/assets/icudt46l.zip assets

Notes:

  • commons-codec.jar is already part of Cordova/PhoneGap
  • SQLCipher for Android attempts to use the system-provided ICU localisation file but it may not included with all target systems.
  • The x86 subdirectory is only necessary when using x86 as a target; for example, some developers prefer to use an x86 Android simulation platform.

Patches to sqlite plugin to use SQLCipher


In src/com/phonegap/plugin/sqlitePlugin/SQLitePlugin.java make the following changes:

  • In the imports:
@@ -24,7 +24,7 @@
 
 import android.database.Cursor;
 
-import android.database.sqlite.*;
+import net.sqlcipher.database.*;
 
 import android.util.Log;
 
  • In SQLitePlugin.execute():
@@ -63,7 +63,8 @@
     JSONObject o = args.getJSONObject(0);
     String dbname = o.getString("name");
 
-    this.openDatabase(dbname, null);
+    String key = o.getString("key");
+    this.openDatabase(dbname, key);
    }
    else if (action.equals("close")) {
     this.closeDatabase(args.getString(0));
  • In SQLitePlugin.openDatabase():
@@ -152,13 +153,15 @@
   */
  private void openDatabase(String dbname, String password)
  {
+  SQLiteDatabase.loadLibs(this.cordova.getActivity());
+
   if (this.getDatabase(dbname) != null) this.closeDatabase(dbname);
 
   File dbfile = this.cordova.getActivity().getDatabasePath(dbname + ".db");
 
   Log.v("info", "Open sqlite db: " + dbfile.getAbsolutePath());
 
-  SQLiteDatabase mydb = SQLiteDatabase.openOrCreateDatabase(dbfile, null);
+  SQLiteDatabase mydb = SQLiteDatabase.openOrCreateDatabase(dbfile, password, null);
 
   dbmap.put(dbname, mydb);
  }
  • In SQLitePlugin.results2string():
@@ -348,6 +351,7 @@
      for (int i = 0; i < colCount; ++i) {
       key = cur.getColumnName(i);
 
+      /**
       // for old Android SDK remove lines from HERE:
       if(android.os.Build.VERSION.SDK_INT >= 11)
       {
@@ -371,6 +375,7 @@
        }
       }
       else // to HERE.
+      **/
       {
        row.put(key, cur.getString(i));
       }

Build and test


Make sure the project is updated for a target platform:

$ android update project --path . --target <id>


Add a small test program to assets/www/index.html, for example:

--- assets/www/index.html.orig 2012-12-24 22:57:45.000000000 +0100
+++ assets/www/index.html 2012-12-24 23:05:40.000000000 +0100
@@ -34,9 +34,35 @@
             </div>
         </div>
         <script type="text/javascript" src="cordova-2.2.0.js"></script>
-        <script type="text/javascript" src="js/index.js"></script>
+        <script type="text/javascript" src="SQLitePlugin.js"></script>
         <script type="text/javascript">
-            app.initialize();
+        document.addEventListener("deviceready", onDeviceReady, false);
+
+        // Cordova is ready
+        function onDeviceReady() {
+          var db = window.sqlitePlugin.openDatabase({name: "DB", key: "secret1"});
+
+          db.transaction(function(tx) {
+            tx.executeSql('DROP TABLE IF EXISTS test_table');
+            tx.executeSql('CREATE TABLE IF NOT EXISTS test_table (id integer primary key, data text, data_num integer)');
+
+            tx.executeSql("INSERT INTO test_table (data, data_num) VALUES (?,?)", ["test", 100], function(tx, res) {
+              console.log("insertId: " + res.insertId + " -- probably 1");
+              alert("insertId: " + res.insertId + " -- probably 1");
+
+              db.transaction(function(tx) {
+                tx.executeSql("select count(id) as cnt from test_table;", [], function(tx, res) {
+                  console.log("res.rows.length: " + res.rows.length + " -- should be 1");
+                  console.log("res.rows.item(0).cnt: " + res.rows.item(0).cnt + " -- should be 1");
+                  alert("res.rows.item(0).cnt: " + res.rows.item(0).cnt + " -- should be 1");
+                });
+              });
+
+            }, function(e) {
+              console.log("ERROR: " + e.message);
+            });
+          });
+        }
         </script>
     </body>
 </html>



Try a debug install:

$ ant debug install


and the test should run.

To test that the encryption is working, try changing the password key in assets/www/index.html:

--- assets/www/index.html 2012-12-24 23:07:43.000000000 +0100
+++ assets/www/index.html.new 2012-12-24 23:13:38.000000000 +0100
@@ -40,7 +40,7 @@
 
         // Cordova is ready
         function onDeviceReady() {
-          var db = window.sqlitePlugin.openDatabase({name: "DB", key: "secret1"});
+          var db = window.sqlitePlugin.openDatabase({name: "DB", key: "secret2"});
 
           db.transaction(function(tx) {
             tx.executeSql('DROP TABLE IF EXISTS test_table');

and try installing and running again. The program should not work if a different encryption key is used to open the database.

Using cursor type enhancements


As described in this recent posting, some enhancements were made in brodyspark / sqlcipher-android-database to use the actual row/column data type information instead of treating all row/column data as strings. To use these changes:
Now as a test, revert the changes to SQLitePlugin.results2string() then compile & test but first uninstall the old version from the simulator or device.

Sunday, December 23, 2012

Enhancements to SQLCipher db classes for Android

NOTICE (June 2015): These instructions are completely out-of-date, the following Cordova plugin supports sqlcipher out-of-the-box: https://github.com/litehelpers/Cordova-sqlcipher-adapter

SQLCipher provides a special, modified version of SQLite to store data in encrypted form using the Native Development Kit (NDK) on the Android platform. How to rebuild SQLCipher for Android was covered in a recent posting. Unfortunately, SQLCipher for Android is based on an old version of the Android database API and is missing a couple important enhancements:
  • get the data type of each row & column in the results for a SQL query, using Cursor.getType()
  • number of rows affected by a SQL UPDATE or DELETE statement, using SQLiteStatement.executeUpdateDelete()
These enhancements are now integrated in brodyspark / sqlcipher-android-database, which was made as a fork of sqlcipher / android-database-sqlcipher.

These enhancements are especially important for the Cordova/PhoneGap sqlite plugin for Android, in order to properly follow the requirements of the Web SQL API.

The enhancements were obtained from the Android SDK 11 (Honeycomb) version. Only some changes to the cursor classes, database utilities, and SQLiteStatement classes were necessary to get these enhancements working with SQLCipher.

Also the brodyspark / sqlcipher-android-tests fork was made to test these changes.

To compile and test these changes please see my previous posting but with the following changes:

  • For the API: $ git clone git://github.com/brodyspark/sqlcipher-android-database.git
  • Test project: $ git clone git://github.com/brodyspark/sqlcipher-android-tests.git
  • The Makefile is already adapted to work with OSX Homebrew.
NOTE: it is best to use API 11 or greater to compile the code, however it should not matter when running the code. Unfortunately, it still has some problems on API 17, to be fixed sometime in the future.

UPDATES January 2013:

Wednesday, December 19, 2012

Developing Cordova/PhoneGap & other Android apps from the shell

The Homebrew package manager makes it much easier for OSX developers to install the Android SDK tools they need to develop and test Android apps from the command line. Unfortunately, while efforts had been made to port Homebrew to Linux, the benefits of Homebrew for the Android SDK tools have not yet been extended to the Linux platform.

Background: OSX with Homebrew

Here are the steps for using Homebrew to develop Android apps on the command line:

  • install the Apple OSX Command Line Tools from Xcode or by downloading the package from Apple;
  • install Homebrew as described on its homepage;
  • $ brew install android-sdk
  • $ android to open the GUI, install toolkits & desired API versions, and create & run an AVD (Anrdroid Virtual Device)
  • $ adb devices to list the device(s), simulated or real, that are up & running
  • $ android list to get the list of available API targets
  • android create project --package com.example.helloandroid --activity HelloAndroid --target <target-id> --path HelloAndroid where <target-id> is a valid id from android list
  • $ ant debug install to install on the simulator
  • To try the Cordova/PhoneGap Android example: in the project do $ android update project --path $(pwd) --target <target-id> then $ ant debug install



Using the Android SDK with Linux: Ubuntu


Get the Linux version of the Android SDK from the developer.android.com or use a command like the following:
wget http://dl.google.com/android/android-sdk_r21-linux.tgz

Extract using a command like
tar xzvf android-sdk_r21-linux.tgz

Add to .bashrc:
PATH=$HOME/android-sdk-linux/tools:$HOME/android-sdk-linux/platform-tools:$PATH

and refresh with a command like: $ . .bashrc


IMPORTANT: The Android SDK has its dependencies on 32-bit (i686) libraries. For a 64-bit system you will need to do something like:
sudo apt-get install ia32-libs (for more details see this article)


To install JRE, JDK, and ant:

sudo apt-get install openjdk-6-jre
$ sudo apt-get install openjdk-6-jdk
$ sudo apt-get install ant

NOTE: while these are clearly linked by dependencies, it is best to install them one-by-one to make sure the correct versions of all components are installed. I first tried using ant to install all JRE/JDK components and got a Headless AWT exception.

To run the Android GUI:
$ android

Select & get the SDK platform tools, a recent version of the SDK platform, and an ARM system image from a recent SDK platform. Unfortunately the GUI does not seem to download very quickly so only start with the components you really need.

Once a recent version of the SDK platform & system image are downloaded and installed, open the Tools menu & select Manage AVDs. Create a new AVD with some reasonable values and try to start one.

If it does not start, here are some things to check:

  • verify that there is a valid JRE with a real GUI;
  • double-check that the correct PATH has been set & refreshed;
  • make sure the installation of the SDK & platform tools is ok.

List the valid API target IDs:
$ android list

Create a test project from the command line:
android create project --package com.example.helloandroid --activity HelloAndroid --target <target-id> --path HelloAndroid where <target-id> is a valid ID from $ android list

In the project directory, try to build & install:
$ ant debug install

If the installation goes well, the app should display a welcome message when you open it.

To watch the log events: $ adb logcat

To try a Cordova/PhoneGap project, in the example from lib/android:
android update project --path $(pwd) --target 1
and then try $ ant debug install


Notes for using the Android SDK with Fedora Core


The procedure to install and use the Android SDK is very similar between Ubuntu & Fedora Core. The major differences lie in the system dependencies.

The JRE, JDK, and ant should be installed with a different procedure than for Ubuntu. From this article the JRE & JDK can be installed with a command like:
yum install java-*-openjdk java-*-openjdk-plugin

For a 64-bit system, please make sure the following i686 packages are installed: glibc.i686libstdc++.i686ncurses-libs.i686zcore.i686, and zlib.i686.

In the future, I would like to download, install, and update the Android SDK & tools using Homebrew on Linux as well.

Tuesday, December 18, 2012

Integrating SQLCipher with Cordova/PhoneGap sqlite plugin for iOS

NOTICE (June 2015): These instructions are completely out-of-date, the following Cordova plugin supports sqlcipher out-of-the-box: https://github.com/litehelpers/Cordova-sqlcipher-adapter

These directions are based on the sqlcipher iOS tutorial, with a few adaptations to integrate with a Cordova/PhoneGap project.

Start with a Cordova/PhoneGap iOS project (documented here for Cordova/PhoneGap 2.2.0).

Download & extract the OpenSSL source from www.openssl.org/source in a location that will be referenced later (I used OpenSSL 1.0.1c).

Inside the PhoneGap iOS project folder, clone the github sqlcipher & openssl-xcode projects using commands like the following:

$ git clone git@github.com:sqlcipher/sqlcipher.git
$ git clone git@github.com:sqlcipher/openssl-xcode.git

Download the OpenSSL source from www.openssl.org/source (I used OpenSSL 1.0.1c) & put the source tar.gz file in the openssl-xcode sub-folder.


Add the subproject references for sqlcipher & openssl-xcode. This can be achieved by:

  • selecting the top-level target at the top of the tree control;
  • press alt-command-a;
  • for openssl-xcode: select the openssl-xcode folder and then select openssl-xcode.xcodeproj &
  • repeat these steps for sqlcipher (select the sqlcipher folder & select sqlcipher.xcodeproj).
Configure build dependencies:

  • select the top-level target again;
  • click the Build Phases tab;
  • expand the Target Dependencies;
  • add crypto (click "+", select crypto, and press Add);
  • add sqlcipher

and add the libraries to link:

  • expand the Link Binary With Libraries;
  • click "+" to add
  • add both libcrypto.a & libsqlcipher.a

IMPORTANT: please make sure that no sqlite3 library is being added here.

CHECK POINT: at this point, it should be possible to build the project with the openssl & sqlcipher dependencies.

NOTE: a couple steps from the sqlcipher iOS tutorial were omitted since they should not be necessary. If the project does not build, here are some things to check & try:

  • Please double-check that all dependencies, including Target Dependencies & Link Binary With Libraries have been fulfilled.
  • If the sqlcipher/libcrypto does not build, please read through README.md under openssl-xcode. You may have to configure the location of the OpenSSL sources if nothing else works.
  • The SQLITE_HAS_CODEC C flag, which is necessary to build sqlcipher with its cipher capabilities, should be defined within the sqlcipher subproject. It should not be necessary to define this flag within the top-level application project, however it is noted here just in case.

It should now be possible to add SQLitePlugin.[hm] to the project Plugins folder & build again. The following patch to SQLitePlugin.m should enable the plugin to use database encryption:


diff --git a/iOS/Plugins/SQLitePlugin.m b/iOS/Plugins/SQLitePlugin.m
index 871bd89..3d62208 100644
--- a/iOS/Plugins/SQLitePlugin.m
+++ b/iOS/Plugins/SQLitePlugin.m
@@ -79,6 +79,9 @@
         return;
     }

+    const char *key = [[options objectForKey:@"key"] UTF8String];
+    sqlite3_key(db, key, strlen(key));
+
     dbPointer = [NSValue valueWithPointer:db];
     [openDBs setObject:dbPointer forKey: dbname];
     [self respond:callback withString: @"{ message: 'Database opened' }" withType:@"success"];



Add SQLitePlugin to Cordova.plist resources, add SQLitePlugin.js to the www folder, and include SQLitePlugin.js in index.html. Try a small test program, like the one from the brodyspark/ PhoneGap-SQLitePlugin-iOS homepage, but open the database with a statement like this:


var db = window.sqlitePlugin.openDatabase({name: "DB",
    key: "secret1"});


If you try the program again but with a different key, you should see a db error in the console log.

In the future, I would like to provide a script or boilerplate to make it easy to create Cordova/PhoneGap projects with encrypted storage working from the beginning.

Thursday, December 6, 2012

Cordova/PhoneGap sqlite plugins offer large db size, excellent reliability

The Cordova/PhoneGap sqlite plugins (PhoneGap-SQLitePlugin-Android & PhoneGap-SQLitePlugin-iOS) offer some major advantages over the built-in WebKit SQL library API including support for large database sizes (see sqlite.org/limits.html) and excellent reliability.

Database sizes

The HTML5/Web SQL API imposes an upper limit of 5MB for client-side databases, which is more than sufficient for some applications but is very small by current standards. When using the Cordova/PhoneGap sqlite plugins then multi-gigabyte databases should be no problem according to sqlite.org/limits.html.

By standard compilation the default limits include:
  • 2 TB database:
    • 2G (2^9) pages with default page size of 1KB
  • 1 billion (1 000 000 000) bytes in a string or BLOB
  • columns: 2000
  • SQL statement length: 1 million (1 000 000) bytes
These should be more than sufficient to satisfy the needs most mobile apps that are published today.

Limits such as number of columns and SQL statement length can be changed at run-time.

Defaults and some other limits such as maximum number of pages can be changed at compile-time. This would require rebuilding the sqlite library itself, which will be covered in a future posting.

Reliability

Keeping the sqlite database in a known, configured location guarantees that the database will be kept up-to-date and backed up on the user's PC or iCloud for the iOS. Using the WebSQL library API does not provide these guarantees for iOS or Android.

There have been a number of reports of data loss problems with the WebSQL library, especially for iOS.

History of WebSQL problems on iOS

Starting in January 2012, this thread on the PhoneGap forum discussed the issue that the local storage or WebSQL persistent data would no longer be backed up as of iOS 5.0.1. Kerri Shotts was kind enough to post a workaround solution that backs the data up during an app shutdown and restores the data upon startup. She made very clear disclaimers that this was only a hack.

CB-330 was filed for this issue, and the solution was in fact to back the data up upon app shutdown and restore upon app startup. This is a pretty good solution but what would happen if an app or even a device would crash?

Some reports have been made recently including:

WebSQL on Android

Fortunately the issues with the WebSQL on iOS have not (yet) shown up on the Android platform. The WebSQL database is kept currently in a location that will be backed up on the PC, however this can be changed in the future.

Also on the PhoneGap forum there was a recent posting about data not persisted on the Android version.

In comparison, these kinds of problems have never been reported for the Cordova/PhoneGap sqlite plugins.

Tuesday, December 4, 2012

Improvements to PhoneGap-SQLitePlugin for Android & iOS: PRAGMAs & multiple databases

After moving and splitting the iOS & Android versions of the SQLitePlugin for Cordova/PhoneGap a couple improvements have been posted:
  • Explicit support for PRAGMA statements (UPDATED: iOS & Android)
  • Support for multiple sqlite database files fixed for Android

PRAGMA statements

In sqlite PRAGMA statements can be used to specify some configuration or actions that are not considered normal SQL statements. Some examples include:
While it is possible to run a PRAGMA statement in the executeSql() function, a number of the PRAGMA statements are treated as no-ops within a database transaction. Since the Web SQL API and hence the sqlitePlugin API for Android only allows executeSql() to be run on a transaction, a better solution is required to support PRAGMA statements.

In the Android version the new executePragmaStatement() has been added to the sqlitePlugin database object. Here is an sample:

        document.addEventListener("deviceready", onDeviceReady, false);

        function onDeviceReady() {
          var db = window.sqlitePlugin.openDatabase("Database", "1.0", "PhoneGap Demo", 200000);

          db.transaction(function(tx) {
            tx.executeSql('DROP TABLE IF EXISTS test_table');
            tx.executeSql('CREATE TABLE IF NOT EXISTS test_table (id integer primary key, data text, data_num integer)', [], function() {
              console.log("test_table created");
            });

            db.executePragmaStatement(
              "pragma table_info (test_table);", function(res) {
                alert("PRAGMA res: " + JSON.stringify(res));
              });
          });
        }

Note that there is no argument substitution, just use your trusty JavaScript or CoffeeScript string manipulation tools.

Support for multiple sqlite database files

This is really just a bug fix for the Android version, considering that the API is designed to support multiple database files and this is already working for the iOS version. Here is the test that was used to verify the fix:

        document.addEventListener("deviceready", onDeviceReady, false);

        function onDeviceReady() {
          var db = window.sqlitePlugin.openDatabase("Database", "1.0", "PhoneGap Demo", 200000);

          var db2 = window.sqlitePlugin.openDatabase("DB2", "1.0", "PhoneGap Demo", 200000);

          db.transaction(function(tx) {
            tx.executeSql('DROP TABLE IF EXISTS test_table');
            tx.executeSql('CREATE TABLE IF NOT EXISTS test_table (id integer primary key, data text, data_num integer)', [], function() {
              console.log("test_table created");
            });

            db.executePragmaStatement("pragma table_info (test_table);", function(res) {
              alert("PRAGMA res: " + JSON.stringify(res));
            });
          });

          db2.transaction(function(tx) {
            tx.executeSql('DROP TABLE IF EXISTS tt2');
            tx.executeSql('CREATE TABLE IF NOT EXISTS tt2 (id2 integer primary key, data2 text, data_num2 integer)', [], function() {
              console.log("tt2 created");
            });

            db.executePragmaStatement("pragma table_info (test_table);", function(res) {
              alert("PRAGMA (db) res: " + JSON.stringify(res));
            });

            db2.executePragmaStatement("pragma table_info (tt2);", function(res) {
              alert("PRAGMA (tt2) res: " + JSON.stringify(res));
            });
          });
        }

How the PRAGMA feature can be used with SQLCipher will be described in a future posting.

Saturday, December 1, 2012

PhoneGap SQLitePlugin for iOS & Android in separate projects

I have now setup the brodyspark user in github.com and moved the iOS and Android versions of the Cordova/PhoneGap sqlitePlugin to the following locations:


There is now a README.md file in the old location to direct the users to the new location.

The project was moved by first renaming the old github.com chbrody user to brodyspark then renaming the Cordova SQLitePlugin fork to PhoneGap-SQLitePlugin-iOS. In copy of the project, the iOS and Lawnchair adapter directory trees were removed to make the Android version using a git filter-branch command as described in this stackoverflow answer. Finally, the Android version trees and documentation in the README.md were removed from the iOS fork.

The sqlitePlugin offers excellent reliability and much more flexibility in storage size than the built-in WebSQL database. In comparison, there have been postings in the phonegap group about reliability problems with the built-in WebSQL database including this one for the Android version and this one for the iOS version.

The next major step is to provide better support for encrypted databases using SQLCipher. I had already made some posting about integration with SQLCipher in my old mobileapphelp blog but would like to describe a more generic procedure using PRAGMAs sometime in the future.

Thursday, November 29, 2012

Creating my first LinkedIn group

I was thinking about what to blog next until I ran into some discussion on LinkedIn about why it is so hard to find good Javascript developers. I suggested CoffeeScript, which had really proven itself over the past few years, and got some nasty reactions which led me to some ideas. While altJS.org already has an active mailing list, I did not see such a group and decided to try starting one.

My experience starting these kinds of things so far is relatively limited to things like starting blogs on Blogger, starting a Google mailing list (mostly to capture some important correspondence that I can link to), and registering some dot-com names so this was still a new adventure for me.

It turned out to be quite simple to get started. I just went on the LinkedIn page under Groups and pressed "Create a Group". When I saw the form I was happy that it would be so easy to create the group. For the name, I thought of "Alt2JS" that could very quickly describe the purpose of the group. There was an option to add a logo and I decided to consider a brave attempt to add a decent one.

I have already found the Google Drive to provide some very good online tools and wondered if there would be a good online logo application. I tried searching "make logo online", got a few nice results, and decided to try Logo Ease. I found it was very easy to understand and use to create a simple but very decent logo and was able to upload it very quickly. (I will describe my experiences in a future posting.)

When looking at the form, I next thought of registering a domain for alt2js just in case it may be handy in the future. I was very happy that alt2js.com was still not yet registered so I went ahead and registered alt2js.com.

So now I filled in the Group Name, selected the Group Type (Professional Group), Summary, and Description. The one critique I had was that the Access option was set to "Request to Join" by default. I have seen a number of groups that had transitioned from closed to public and now I have come to understand why. So I selected Auto-Join and was happy that all the access sub-options (display in groups directory, allow members to display logos, and allow members to invite others) were now enabled by default.

To finish I pressed the button to "Create an Open Group", got a nice form to invite some of my connections to the group, and now have a nice new LinkedIn Alt2JS group.

Tuesday, November 27, 2012

Brodyspark: intro

Hi my name is Chris Brody and I have now been blogging off and on, mostly about mobile app programming as well as a few other subjects since April this year. I have a masters degree in electrical engineering from Stanford and have been programming mobile messaging servers for over 14 years. I am also getting into building business online, starting at a small scale, and am now really starting to discover the significance of blogging on a regular basis. My idea now is to combine most of these subjects into a single blog that I will keep based on a personal brand.

One of my challenges has been how to brand myself in a way that should cover my talents in innovation, app design and programming, building online businesses, and a little bit of blogging. While looking for a good dot-com name I came up with "brodyspark" for myself when I was thinking about the phrase "perfect in weakness" from the New Testament. I will now build this blog to cover all of these subjects except for my faith, which I will keep on a separate blog for those who are interested.