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:

• SQLite foreign key support
• PRAGMA table_info
• For the SQLCipher flavor: PRAGMA key to set the encryption key

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.