BLOB data support (MBTiles etc.)

[brodycj]

The existing cordova-sqlite-ext and Cordova-sqlite-evplus-legacy-attach-detach-free versions support the reading of BLOB data by automatic conversion to base64 encoding but it has the following issues:

• not specified by DRAFT Web SQL API
• not supported for Windows
• not supported by default Android-sqlite-connector implementation, need to use the androidDatabaseImplementation: 2 setting to get this functionality on Android
• Not all libraries and apps would use base64 encoding. For example PouchDB has its own solution described in (#2890) - websql: avoid hex() for binaries pouchdb/pouchdb#2900.

Here is an example case where the cordova-sqlite-ext and Cordova-sqlite-evplus-legacy-attach-detach-free versions deviate from the behavior in (WebKit) Web SQL:

        it(suiteName + "INSERT inline BLOB value (X'40414243') and check stored data [SELECT BLOB ISSUE with androidDatabaseImplementation: 2 & Windows/WP8]", function(done) {
          var db = openDatabase('INSERT-inline-BLOB-value-and-check-stored-data.db', '1.0', 'Demo', DEFAULT_SIZE);

          db.transaction(function(tx) {
            tx.executeSql('DROP TABLE IF EXISTS test_table');
            tx.executeSql('CREATE TABLE IF NOT EXISTS test_table (data)', [], function(ignored1, ignored2) {

              tx.executeSql("INSERT INTO test_table VALUES (X'40414243')", [], function(ignored, rs1) {

                expect(rs1).toBeDefined();
                expect(rs1.rowsAffected).toBe(1);

                tx.executeSql('SELECT HEX(data) AS hexValue FROM test_table', [], function(ignored, rs2) {
                  expect(rs2).toBeDefined();
                  expect(rs2.rows).toBeDefined();
                  expect(rs2.rows.length).toBeDefined();

                  var row = rs2.rows.item(0);
                  expect(row).toBeDefined();
                  expect(row.hexValue).toBe('40414243');

                  tx.executeSql('SELECT * FROM test_table', [], function(ignored, rs3) {
                    if (!isWebSql && isAndroid && isImpl2) expect('Behavior changed please update this test').toBe('--');
                    expect(rs3).toBeDefined();
                    expect(rs3.rows).toBeDefined();
                    expect(rs3.rows.length).toBeDefined();

                    var row = rs3.rows.item(0);
                    expect(row).toBeDefined();
                    // *** DEVIATION IN cordova-sqlite-ext and
                    // Cordova-sqlite-evplus-legacy-attach-detach-free versions
                    expect(row.data).toBe('@ABC');

                    // Close (plugin only) & finish:
                    (isWebSql) ? done() : db.close(done, done);
                  }, function(ignored, error) {
                    if (!isWebSql && (isWindows || isWP8 || (isAndroid && isImpl2))) {
                      expect(error).toBeDefined();
                      expect(error.code).toBeDefined();
                      expect(error.message).toBeDefined();

                      expect(error.code).toBe(0);

                      if (isWP8)
                        expect(true).toBe(true); // SKIP for now
                      else if (isWindows)
                        expect(error.message).toMatch(/Unsupported column type in column 0/);
                      else
                        expect(error.message).toMatch(/unknown error.*code 0.*Unable to convert BLOB to string/);
                    } else {
                      // NOT EXPECTED:
                      expect(false).toBe(true);
                      expect(error.message).toBe('---');
                    }

                    // Close (plugin only) & finish:
                    (isWebSql) ? done() : db.close(done, done);
                  });

                });

              });

            });
          });
        }, MYTIMEOUT);

Due to both the challenges of fixing the Android-sqlite-connector & Windows versions and the deviation from (WebKit) Web SQL behavior I would like to solve this a different way in the future.

The proposed solution is to add a user defined function (UDF) such as BASE64 or TOBASE64 and then the user could retrieve BLOB data for processing with SQL like this: SELECT BASE64(image_data) from ImageTable

[brodycj]

An excellent test case is to try the following: SELECT X'FFD1FFD2' AS myresult

In case of cordova-sqlite-storage 0.7.7, cordova-sqlite-ext, and Cordova-sqlite-evplus-legacy-attach-detach-free the result would contain myresult with a Base64 value.

In case of a recent version of cordova-sqlite-storage (such as 1.5.0) and this version there would be no value for myresult.

[brodycj]

The BASE64 User Defined Function has been added to cordova-sqlite-ext and this version to support the reading of BLOB column values.

[angel1st]

@brodybits - re BLOB read / write modes, I would appreciate if you can help me understand:

1. Does the plugin support currently BLOB read (w/o transforming to BASE64 string) and if yes, could please share an example?
2. Does the plugin support BLOB write, i.e. ability to write BLOB value right from the plugin to the database and if yes, could you please share an example?

[brodycj]

Marked as a doc-todo item