storesafe / cordova-sqlite-evcore-extbuild-free Goto Github PK

Solution to storesafe/cordova-sqlite-storage#666 needs to be ported to this version.

From some performance measurements recorded in #2 a repeated read/update performance test sometimes runs slower in this version than in cordova-sqlite-storage. This needs to be documented. I hope to find a straightforward solution.

In addition I recently discovered that this version uses different default page and cache sizes than cordova-sqlite-storage as described in http://sqlite.org/pgszchng2016.html. I think this would be desired for applications with heavy storage requirements which this version targets. I will document this when I get a chance.

In our application, when a user first logs in, a considerable amount of data is transferred to the client and inserted into the database. Depending on the amount of data, the SQL inserts alone take about 10 minutes on a new Android smartphone, which is longer than our customers are willing to accept. So we did some performance analyses.

A typical table row in our application has about 40 columns, but only about 15 of them are typically non-null and none contain BLOBs or long strings. So the pure amount of data shouldn't be an issue.

We insert the data in blocks of 2,000 rows each. Our code for processing such a block of inserts looks roughly like this:

const helper = newPromiseHelper(database);
const tx = helper.newBatchTransaction();
for (const operation of operations) {
  const sql = ...;
  const values = ...;
  tx.executeStatement(sql, values);
}
await tx.commit();

According to our measurements, the last line, which performs the actual commit, takes about 2.3 s on average. That's 2.3 s for 2,000 inserts within a single transaction.

We had a look at the implementation. Right before the jump to native code, SQLitePluginTransaction calls run_batch_flatjson. Each call to run_batch_flatjson takes 600 ms on average. So out of the 2.3 s, only 600 ms are spent in native code. The remaining 1.7 s are spent in the JavaScript part of the SQLite plugin. From the looks of it, the JavaScript code performs a rather elaborate sequence of data transformations, copying all rows from one data structure to the next, then to the next and so on, about five times total.

I know very little about the inner workings of your SQLite plugin, but my gut tells me that it may be possible to significantly reduce these 1.7 s spent repeatedly transforming the data.

What do you think? Is it possible to improve insertion performance?

ref: storesafe/cordova-sqlite-storage#147

This was fixed for iOS with a workaround in https://github.com/litehelpers/Cordova-sqlite-evplus-legacy-free and https://github.com/litehelpers/Cordova-sqlite-evplus-legacy-attach-detach-free.

This is also an issue on Android in case of cordova-android 6.0.0 (installed by cordova-cli 6.4.0) ref: apache/cordova-discuss#57

I want to know how to load an external database with password protected. Currently I can load an external database without password protected, but trying to load a protected one:

a statement error callback did not return false: an unknown error was returned: code :0, sqliteCode:1, message: -

Here is my code

  function OpenCustom(path, filename, key) {
        var db = window.sqlitePlugin.openDatabase({name: filename, key:key ,androidDatabaseLocation: path},function () {

            navigator.notification.alert('Database is queried');
            db.transaction(
                function (tx) {
                    tx.executeSql("insert into customerAccounts (firstname, lastname, acctNo) values ('firo"+Math.random()+"','fr"+Math.random()+"','"+Math.random()+"');",[],function(){console.log('ok')},function(statement,error){alert(JSON.stringify(error));});
                    tx.executeSql("SELECT * FROM customerAccounts;",[],function (tx, resultSet) {
                        for (var i=0; i<resultSet.rows.length; ++i){
                            $scope.clients.push(JSON.stringify(resultSet.rows.item(i)));
                        }
                        alert(JSON.stringify(resultSet.rows.length));

                    },function (tx,error) {
                        navigator.notification.alert('select error database'+JSON.stringify(error));
                    });
                },function (error)
                {
                    navigator.notification.alert('Populate database error: ' + error.message);

                }

            );

        }, function (error) {
            navigator.notification.alert('Open database ERROR: ' + JSON.stringify(error));
        });


    }

Using Cordova 6.3.1 deploying in Android 4.4.2.

Best regards

When I tried building a Windows version of my test app at https://github.com/brodybits/Cordova-sql-test-app-evcore-free-pgb with PhoneGap Build it would not work with this plugin. Using the echoTest function would not work. However displaying a dialog using cordova-plugin-dialogs does work with Windows on PhoneGap Build.

Already available in https://github.com/litehelpers/Cordova-sqlite-evcore-extbuild-free/tree/bb-update-wip1, will be published once tested on all platforms

corresponding to: storesafe/cordova-sqlite-storage#837

The version in https://github.com/litehelpers/Cordova-sqlite-evplus-legacy-free also uses the flat JSON interface to reduce the memory usage of the iOS version. This would be needed for some extremely large transactions. I will add the fixes to this project when I get a chance.

Also needed: test program based on https://github.com/brodybits/Cordova-sqlite-perftest to verify that this version can handle transactions with 200K(+) SQL statements without crashing (all platforms Android/iOS/Windows).

I think this needs to be clarified. The free GPL v3 license allows for internal development, testing, and distribution within an organization (http://www.gnu.org/licenses/gpl-faq.html#InternalDistribution). This would be consistent with the Apple Developer Enterprise program (https://developer.apple.com/programs/enterprise/).

A commercial license would be required to distribute an app to another party without making the source code available.

GPL v3 does include a system library exception, which covers linking to closed-source system libraries. There are some more details and fine points in the following:

• http://www.gnu.org/licenses/quick-guide-gplv3.html
• http://www.gnu.org/licenses/gpl-3.0.html
• https://opensource.stackexchange.com/questions/2203/why-is-the-definition-of-system-libraries-in-gplv3-phrased-the-way-it-is

Here are the results I get when running https://github.com/brodybits/Cordova-sqlite-perftest on Android with cordova-sqlite-storage (built with sqlite 3.8.10.2) and this version (built with sqlite 3.12.2):

Device: Acer Z520 (dual-SIM) with Android 4.4.2

Rebooted before the following tests:

cordova-sqlite-storage 1.4.7 (sqlite 3.8.10.2):

cordova-sqlite-evcore-extbuild-free 0.8.1 (sqlite 3.12.2):

cordova-sqlite-storage 1.4.7 (sqlite 3.8.10.2) repeated:

cordova-sqlite-evcore-extbuild-free 0.8.1 (sqlite 3.12.2) repeated:

So far the bulk insert and volume read tests execute on Android in less than half the time in this plugin version compared to cordova-sqlite-storage.

Next steps:

• test with most recent sqlite version (3.14.1)
• comparison test on iOS and Windows

If I do ndk-build of Android-evcore-native-driver NDK JAR component I get a message that the deprecated armeabi target CPU platform will be removed from the next major release. Old armeabi target CPU is also one more library build that I have to test every time I build a new version of the NDK JAR component.

I would like to remove support for armeabi target CPU in the near future, likely when I fix #6 (include missing pre-populated database support), #27 (embedded NULL characters), #28 (control characters), etc.

I would like to ask the user community to respond if there is any reason for me not to do this.

From storesafe/cordova-sqlite-storage#474 and https://github.com/litehelpers/Cordova-sqlite-storage/issues/766: maximum record size of cordova-sqlite-storage, cordova-sqlite-ext, and this plugin version is unknown and still needs to be tested.

Embedded U+0000 NULL characters ('\u0000' or '\0') not working on Android (default Android-sqlite-evcore-native-driver access implementation) or Windows.

Solution on Android-sqlite-evcore-native-driver is to get the actual column length instead of using strlen().

Possible solution for Windows a solution was contributed in storesafe/cordova-sqlite-storage#709.

Expected to be resolved in the next major release from storesafe/cordova-sqlite-storage#687 if not sooner.

NULL character test TODOs:

• test with NULL character in inline string value in SQL statement
• test with NULL character in database file name

RELATED ISSUES:

• #19 - issue with multi-byte UTF-8 characters
• #7 - crash in case of emojis and other 4-byte UTF-8 characters
• #25 - issue with multi-byte UTF-8 character such as accented characters or Euro symbol in database name
• #26 - crash in case of 4-byte UTF-8 character in database file name

Attempt to execute SQL statement that returns result with Samaritan letter will trigger a crash due to a bug in litehelpers / Android-sqlite-evcore-native-driver-free recently spotted by @brodybits. Here is a reproduction test case with Samaritan Bit letter (ref: https://www.compart.com/en/unicode/U+0801):

        it(suiteName + 'string parameter value manipulation test with UTF-8 3-byte character Samaritan Bit (\\U+0801)', function(done) {
          var db = openDatabase("UTF8-0801-string-upper-value-test.db", "1.0", "Demo", DEFAULT_SIZE);

          db.transaction(function(tx) {

            tx.executeSql('SELECT UPPER(?) AS myresult', ['a\u0801.'], function(ignored, rs) {
              expect(rs).toBeDefined();
              expect(rs.rows).toBeDefined();
              expect(rs.rows.length).toBe(1);
              expect(rs.rows.item(0).myresult).toBe('Aࠁ.');

              // Close (plugin only) & finish:
              (isWebSql) ? done() : db.close(done, done);
            });
          }, function(error) {
            // NOT EXPECTED:
            expect(false).toBe(true);
            expect(error.message).toBe('--');
            // Close (plugin only) & finish:
            (isWebSql) ? done() : db.close(done, done);
          });
        }, MYTIMEOUT);

The following patch to litehelpers / Android-sqlite-evcore-native-driver-free would resolve this issue:

diff --git a/native/sqlc.c b/native/sqlc.c
index 2ad1092..cfbe29f 100644
--- a/native/sqlc.c
+++ b/native/sqlc.c
@@ -491,7 +491,7 @@ const char *sqlc_evcore_qc_execute(sqlc_handle_t qc, const char * batch_json, in
                     pi += 1;
                   } else if (pc >= 32 && pc < 127) {
                     rr[rrlen++] = pptext[pi++];
-                  } else if (pc > 0xe0) {
+                  } else if (pc >= 0xe0) {
                     rr[rrlen++] = pptext[pi++];
                     rr[rrlen++] = pptext[pi++];
                     rr[rrlen++] = pptext[pi++];

Problem

Cordova-sqlite-evcore-extbuild-free release 0.8.5 was issued in January 2017 with changes to remove use of the JSMN module (http://zserge.com/jsmn.html) from the libsqlc-evcore-native-driver.so NDK build (built from litehelpers / Android-sqlite-evcore-native-driver-free (ext-master version branch)), however I did not push the actual changes to GitHub before my hard drive failed. Cordova-sqlite-evcore-extbuild-free release 0.8.6, 0.8.7, 0.9.0 were also issued with the same evcore-native-driver build. No other plugin versions by the @litehelpers organization or otherwise maintained by @brodybits are affected. Consequences:

• Not possible to analyze the actual source for possible bugs including any possible security or data-loss issues
• Further updates to the libsqlc-evcore-native-driver.so NDK build will be issued without these changes present. (Use of the JSMN module in will be reintroduced in the next release of this plugin.)
• Distribution of apps using this plugin under GPL v3 license option would need an exception.

Limited GPL v3 exception

I Christopher J. Brody AKA @brodybits (mailto:[email protected]) hereby grant an exception to the GPL v3 license option for binary libsqlc-evcore-native-driver.so NDK objects for cordova-sqlite-evcore-extbuild-free releases 0.8.5, 0.8.6, 0.8.7, and 0.9.0. This exception does NOT apply for any other release of cordova-sqlite-evcore-extbuild-free. This exception is also granted for external open-source derivatives of cordova-sqlite-evcore-extbuild-free releases 0.8.5, 0.8.6, 0.8.7, and 0.9.0 (not for derivatives of any other cordova-sqlite-evcore-extbuild-free releases).

Recommended solution

TODO: It is recommended to upgrade to the next release of this plugin when published, with this problem along with a couple other urgent issues resolved.

PLEASE USE LATEST RELEASE of this plugin with evcore-native-driver NDK JAR built from source, now resolves issue on cordova-android@7, other critical fixes coming soon (see below for details).

Quick test of 4-byte emoji UTF-8 character in database name will crash on Android in case of the default Android-evcore-native-driver database access implementation (NOT an issue in case of the builtin android.database access implementation which is enabled by the androidDatabaseImplementation: 2 setting in sqlitePlugin.openDatabase). Related issues:

• #25 - issue with multi-byte UTF-8 character such as accented characters or Euro symbol in database name
• #7 - crash in case of emojis and other 4-byte UTF-8 characters

P.S. For some reason I have only encountered this crash on my Motorola Moto E4 Plus Android 7.1.1 test device so far.

This version uses SQLite 3.12.2 for Android, 3.14.0 for iOS & Windows. I think it would be better to use the same version for all supported platforms. This would make the documentation easier to read, reduce confusion, and reduce the potential for application bugs due to different SQLite behaviors.

Two straightforward ways to solve, both with drawbacks:

• Upgrade to SQLite 3.14.1 for all platforms Android/iOS/Windows. This is expected to degrade performance though to a limited extent in the case of small read/write transactions (see #2 [performance measurements], #4 [repeated read/upgrade performance issue])
• Downgrade SQLite amalgamation (C code) to 3.12.2 for iOS and Windows. This would be a step backwards and add extra size pollution to the git history.

I am facing the problem descrived here.
In my device xperia Z5 is working ok but in the emulator (or any Intel processor device) I see the special chars retrieved from sqlite like this ?ffffffc3??ffffffb3? for ó, etc.

As brunocollaco suggests on the above linked thread I think this is a bug with evcore-extbuild-free plugin on intel processors.

Any ideas?

Thank you.

Support for Visual Studio 2017 now available in litehelpers / Cordova-sqlite-storage needs to be included in this version. This will be done after #20: Transaction problem after page change, WITH POSSIBLE DATA LOSS (cordova-sqlite-storage#666) is resolved here.

Can indexes be created with this plugin? I got an error trying to create the following index for my table

CREATE INDEX timeStatusElogIdx ON elog (starttime, endtime, status)

I'm attempting to upgrade to the latest cordova lib. It looks like the cordova-sqlite-evcore-extbuild-free plugin is attempting to update config.xml in a directory that has moved (blog entry: https://cordova.apache.org/announcements/2017/12/04/cordova-android-7.0.0.html). I think the path in plugin.xml line:

<config-file target="res/xml/config.xml" parent="/*">

Needs to be prefixed with app/src/main:

<config-file target="app/src/main/res/xml/config.xml" parent="/*">

At least that's where the config.xml file is showing up on my computer. Not sure if this can be done on a Android version basis?

hello, I have Encountered a big problem，is the size of the pre-populated external sqlite database important? now, when the db size is 10MB, it's ok to open it ; but when the db size is 100MB, it's failed to open it.My app may need the db size several GB, so what can I do for this? Hope for your help, thank you

@brodybits is happy to announce a commercial license giveaway, under the following terms:

• Single app per company or indie publisher
• Support is available through public forums such as litehelpers / Cordova-sqlite-evcore-extbuild-free / issues
• Valid until the end of March 2017
• Please have an owner, partner, or other board member provide the following information to [email protected]:
  • Name, description, and location of your company, along with website if available
  • Name and description of app, along with link to any published versions available
  • May we use your app as a sample?
  • How satisfied are you with this plugin and its documentation? Do you have any suggestions for improvement?
  • What kinds of more advanced SQL features such as JOIN, VIEW, or TRIGGER, if any?
  • Major framework used by your app, such as Ionic 1, Ionic 2, React, Node.js, Meteor, etc.?
  • What protocols and frameworks if any does your company use for client-server data synchronization?
  • What are your most difficult challenges related to developing, maintaining, and supporting your software?
  • May @brodybits or other members of @litehelpers contact you with offers or any other updates?

A customer is interested in support for the EU characters in the UPPER function, as a quick solution to support case-insensitive string comparisons.

The LOWER function would likely be updated as well in order to maintain symmetry as much as possible.

The implementation would likely be similar to: https://www.sqlite.org/src/dir?ci=4979f138e8c8bef7&name=ext/icu

It is not yet determined whether or not to update LIKE and REGEXP.

P.S. This feature should function consistently on all supported platforms (Android, iOS, macOS, and Windows).

Parallel to storesafe/cordova-sqlite-storage#772:

I would like to drop the Android NDK build for x86_64 CPU. NDK x86 (32-bit) build will also work on x86_64 CPU. One less CPU to test NDK builds on. (My understanding is that Android x86 and x86_64 CPU builds are generally used for the emulator.)

Objections and any other feedback would be very welcome [...]
I would be happy to reconsider if there are enough objections.

Hello,

I download a db file from URL with FileTransfert in cdvfile://localhost/persistent/medias/db/test.db

But I don't know how to load this downloaded file
What is the good parameters from name, iosDatabaseLocation ?

I try many value, all not work ..
window.sqlitePlugin.openDatabase({iosDatabaseLocation: 'Documents',name:"cdvfile://localhost/persistent/medias/db/test.db"})

When try to select my datas, no datas found.
I think is a created new DB and not load my file ..

Thanks for your help.

From storesafe/cordova-sqlite-storage#771 I am planning to end support for old Android versions as follows:

• definite: Android pre-4.0
• extremely likely: Android 4.0
• very likely: Android 4.1-4.3

To be partially maintained as described in comment below (#35 (comment)): Android 4.4, old armeabi CPU

I received a couple inquiries for continued web worker support to help solve performance issues in two different cases:

1. Issue when populating with data from one or more external servers.
2. Issue when reading and processing binary/base64 image data from the database (ref: #10)

There is an existing version at https://github.com/litehelpers/Cordova-sqlite-evplus-legacy-workers-free but it has the following major problems:

• Extra communication overhead: response messages go through the main thread and there is library code to send each worker response messages to the correct worker thread.
• Standard Web SQL transaction API will not work with upcoming browser platform (ref: storesafe/cordova-sqlite-storage#576).
• ADDITIONAL PROBLEM raised in litehelpers/cordova-sqlite-evmax-ext-workers-legacy-build-free#3

The ideal solution would be to redesign the apps to move all processing and possibly XHR (HTTP requests) to web workers and reserve the main thread to handle DOM, coordination, and I/O tasks. (Note that this plugin already uses background threads to handle sqlite operations, with the exception of Windows which can easily be fixed.) Unfortunately this may not be a 100% solution since this plugin does have some internal pre/post processing in JavaScript.

The most general solution would be to offer the db.executeSql and db.sqlBatch functions within web workers. This solution would continue to have the extra communication overhead described above.

An alternative solution for case 1 would be to write the data from a worker and let the main thread listen for indications of data write success/failure results. This idea is inspired by existing ideas such as reactive programming and Flow Based Programming (FBP) as described by J. Paul Morrison.

For case 2 an alternative, recommended solution is to read the image data from the main thread and then send it to a web worker for further processing.

As asked in storesafe/cordova-sqlite-storage#764:

This is done by increasing SQLITE_LIMIT_VARIABLE_NUMBER. This should be trivial for iOS/macOS (edit plugin.xml) and Windows (edit src/windows/SQLite3-Win-RT/SQLite3/SQLite3.Shared.vcxitems). For Android this would involve rebuilding litehelpers / Android-sqlite-evcore-native-driver-free.

I would be happy to increase this to say 2000, 5000, or perhaps even 9999 if there is enough interest from the user community.

I got a request for help with using an arbitrary database folder location on Windows.

I am using the following snippet on an Android Samsung Galaxy Tab S2 without success.

initDatabase: function () {
        window.resolveLocalFileSystemURL(cordova.file.externalDataDirectory, function(externalDataDirectoryEntry) {
            //window.sqlitePlugin.deleteDatabase({name: 'event_manager.db', androidDatabaseLocation: externalDataDirectoryEntry.toURL()});
            db = window.sqlitePlugin.openDatabase({name: 'event_manager.db', androidDatabaseLocation: externalDataDirectoryEntry.toURL()});

            db.executeSql('PRAGMA foreign_keys = ON;', [], function(res) {
                db.executeSql('PRAGMA foreign_keys;', [], function(res){
                    console.log('PRAGMA res: ' + JSON.stringify(res));
                })
            })

            db.transaction(function(tx) {
                tx.executeSql('CREATE TABLE IF NOT EXISTS events( EventID Integer Primary Key NOT NULL, RaceName Text NOT NULL, '
                    + 'BeginDate DateTime NOT NULL, EndDate DateTime NOT NULL, LocationName Text, '
                    + 'LocationAddrLine1 Text, LocationAddrLine2 Text, LocationPostCode Integer, '
                    + 'LocationCity Text, LocationCountry Text, CrossRefID Integer NOT NULL);');

                tx.executeSql('CREATE TABLE IF NOT EXISTS groups(EventID Integer Primary Key NOT NULL, GroupID Integer NOT NULL, ' //Primary Key
                    + 'GroupName Text, HasComments Boolean NOT NULL, FOREIGN KEY(EventID) REFERENCES events(EventID));');

                tx.executeSql('CREATE TABLE IF NOT EXISTS questions(GroupID Integer Primary Key NOT NULL, QuestionID Integer NOT NULL, ' //Primary Key
                    + 'AnswerType Text NOT NULL, SortOrder Integer NOT NULL, QuestionText Text NOT NULL, '
                    + 'FOREIGN KEY(GroupID) REFERENCES groups(GroupID));');

                tx.executeSql('CREATE TABLE IF NOT EXISTS comments(GroupID Integer Primary Key NOT NULL, CommentText Text, ' //Primary Key
                    + 'FOREIGN KEY(GroupID) REFERENCES groups(GroupID));');

                tx.executeSql('CREATE TABLE IF NOT EXISTS answers(GroupID Integer Primary Key NOT NULL, QuestionID Integer NOT NULL, '
                    + 'Yes Integer, No Integer, MCAnswer1 Integer, MCAnswer2 Integer, MCAnswer3 Integer, MCAnswer4 Integer, '
                    + 'MCAnswer5 Integer, DateTime DateTime, Quantity Integer, AnswerText Text, '
                    + 'FOREIGN KEY(GroupID) REFERENCES groups(GroupID), FOREIGN KEY(QuestionID) REFERENCES questions(QuestionID));');

                tx.executeSql('CREATE TABLE IF NOT EXISTS file_upload(EventID Integer NOT NULL, Purpose Text, Description Text, '
                    + 'RidernetID Text, QuestionID Integer, TempInjuryID Integer, MediaType Text, MediaContent Text, '
                    + 'MediaFileName Text, CrossRefID Integer, FOREIGN KEY(EventID) REFERENCES events(EventID), '
                    + 'FOREIGN KEY(QuestionID) REFERENCES questions(QuestionID), FOREIGN KEY(CrossRefID) REFERENCES events(CrossRefID));');

                tx.executeSql('CREATE TABLE IF NOT EXISTS media(EventID Integer NOT NULL, mediaItemPath Text, '
                    + 'FOREIGN KEY(EventID) REFERENCES events(EventID));');

            }, function(error) {
                console.log('Transaction ERROR: ' + error.message);
                db.close();
            }, function() {
                console.log('Tables created - Database OK');
            });
        });
    }

Not sure why the begining is not shown as code. Sorry...

When I open the database to check using "PRAGMA foreign_keys;" the result is 0 which is OFF. Can anybody help me enable the foreign keys?

Hello,

I am using Phonegap on Windows 10 64 Bit, there is something I just cannot get my head around. If Your plugin does not work in a browser while developing with Phonegap, how do we check our code before generating the APK? I have added my code but simply cannot test it....

Thank you.

I received a nice note from someone who needs this with pre-populated database feature. Unfortunately I did not have much time to merge it in due to some other backlog. This feature will be included when I get a chance.

I'm a hobbyist working on my first phonegap build project, and I got a depricated API message when trying to use this plugin. The culprit seems to be /project/src/io/sqlc/SQLiteAndroidDatabase.java. I'm not sure if this is a real a problem, but here's my build log for your review just in case.

Build Date: 2017-01-03 02:18:26 +0000

PLUGIN OUTPUT

Fetching plugin "https://github.com/litehelpers/Cordova-sqlite-evcore-extbuild-free.git" via git clone
Using shallow clone
Repository "https://github.com/litehelpers/Cordova-sqlite-evcore-extbuild-free.git" checked out to git ref "master" at "1186a7e".
Installing "cordova-sqlite-evcore-extbuild-free" at "0.8.4" for android

COMPILE OUTPUT

Configuration on demand is an incubating feature.
Incremental java compilation is an incubating feature.
:preBuild UP-TO-DATE
:preDebugBuild UP-TO-DATE
:checkDebugManifest
:CordovaLib:preBuild UP-TO-DATE
:CordovaLib:preDebugBuild UP-TO-DATE
:CordovaLib:compileDebugNdk UP-TO-DATE
:CordovaLib:compileLint
:CordovaLib:copyDebugLint UP-TO-DATE
:CordovaLib:mergeDebugProguardFiles
:CordovaLib:packageDebugRenderscript UP-TO-DATE
:CordovaLib:checkDebugManifest
:CordovaLib:prepareDebugDependencies
:CordovaLib:compileDebugRenderscript
:CordovaLib:generateDebugResValues
:CordovaLib:generateDebugResources
:CordovaLib:packageDebugResources
:CordovaLib:compileDebugAidl
:CordovaLib:generateDebugBuildConfig
:CordovaLib:mergeDebugShaders
:CordovaLib:compileDebugShaders
:CordovaLib:generateDebugAssets
:CordovaLib:mergeDebugAssets
:CordovaLib:processDebugManifest
:CordovaLib:processDebugResources
:CordovaLib:generateDebugSources
:CordovaLib:incrementalDebugJavaCompilationSafeguard
:CordovaLib:compileDebugJavaWithJavac
:CordovaLib:compileDebugJavaWithJavac - is not incremental (e.g. outputs have changed, no previous execution, etc.).
Note: Some input files use or override a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
:CordovaLib:processDebugJavaRes UP-TO-DATE
:CordovaLib:transformResourcesWithMergeJavaResForDebug
:CordovaLib:transformClassesAndResourcesWithSyncLibJarsForDebug
:CordovaLib:mergeDebugJniLibFolders
:CordovaLib:transformNative_libsWithMergeJniLibsForDebug
:CordovaLib:transformNative_libsWithSyncJniLibsForDebug
:CordovaLib:bundleDebug
:prepareProjectCordovaLibUnspecifiedDebugLibrary
:prepareDebugDependencies
:compileDebugAidl
:compileDebugRenderscript
:generateDebugBuildConfig
:mergeDebugShaders
:compileDebugShaders
:generateDebugAssets
:mergeDebugAssets
:generateDebugResValues
:generateDebugResources
:mergeDebugResources
:processDebugManifest
:processDebugResources
:generateDebugSources
:incrementalDebugJavaCompilationSafeguard
:compileDebugJavaWithJavac
:compileDebugJavaWithJavac - is not incremental (e.g. outputs have changed, no previous execution, etc.).
Note: /project/src/io/sqlc/SQLiteAndroidDatabase.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
:compileDebugNdk UP-TO-DATE
:compileDebugSources
:prePackageMarkerForDebug
:transformClassesWithDexForDebug
Merged dex #1 (86 defs/108.5KiB)
Merged dex #2 (17 defs/27.4KiB)
Result is 103 defs/161.1KiB. Took 0.0s
:mergeDebugJniLibFolders
:transformNative_libsWithMergeJniLibsForDebug
:processDebugJavaRes UP-TO-DATE
:transformResourcesWithMergeJavaResForDebug
:validateDebugSigning
:packageDebug
:zipalignDebug
:assembleDebug
:cdvBuildDebug

BUILD SUCCESSFUL

Total time: 2.772 secs
Built the following apk(s):
/project/build/outputs/apk/project-debug.apk

With this line in my config:

<plugin name="Cordova-sqlite-evcore-extbuild-free" source="npm" spec="0.8.0" />

iOS builds successfully but Android fails with this message:

Error - The following plugin, plugin version or a dependancy of this plugin is not on npm: [email protected]

The PGB log says this:

plugman install --platform android --project /project --plugin [email protected]": Fetching plugin "[email protected]" via npm
Failed to fetch plugin [email protected] via registry.
Probably this is either a connection problem, or plugin spec is incorrect.
Check your connection and plugin name/version/URL.
Error: Registry returned 404 for GET on https://registry.npmjs.org/Cordova-sqlite-evcore-extbuild-free

Hi,

I want to open an MBTiles-File (which is an sqlite database) from the sd card. I have got the path and it can be resolved by the File-Plugin. So I try to open the database using the parameter "androidDatabaseLocation" as described. This results in opening the database from internal storage.
Opening files from any location in internal storage works perfect that way. But trying to read from sd card replaced the sd card with internal storage.
Does anyone have an idea for me?
Actually testing on Samsung Galaxy Tab Active2, Android 7.1. External write permission is set in the manifest file.

I am getting an error when I try to install the plugin.

C:\Users\Mark\WebstormProjects\sen>cordova plugin add cordova-sqlite-evcore-extbuild-free --save
Error: Cannot find plugin.xml for plugin "cordova-sqlite-evcore-extbuild-free". Please try adding it again.

Workaround solution for crash in case of emojis and other 4-byte UTF-8 characters in [email protected] (#7) is to replace each 4-byte UTF-8 character (starting with 0xF? value) with single ? mark. This behavior is observed on Android starting with 6.0.

On Android pre-6.0 emojis and other 4-byte UTF-8 characters are stored with non-standard encoding as described in storesafe/cordova-sqlite-storage#564.

in commit c41e54e

/cc @jacobg

Hello,

I have installed phonegap, ran the command via the CLI:
npm i cordova-sqlite-evcore-extbuild-free --save

The plugin installed itself in node_modules/cordova-sqlite-evcore-extbuild-free/

I have added the below code to my config.xml

But nothing happens when I write a query, did I miss something? It is the first time I use a non "CORE" plugin with Phonegap.

Thank you.

Certain https://en.wikipedia.org/wiki/Control_characters such as vertical tab, form feed, and backspace character do not work properly on Android (default Android-sqlite-evcore-native-driver access implementation). Likely explanation was given in storesafe/android-sqlite-evcore-ndk-driver-free#2 (comment).

Updated my app from cordova-sqlite-ext to this version, and on a physical HTC device the apps starts as normal, but as soon as html is displayed and app tries to get some tekst from the database, the ui hangs and the app crashes with the following error code:

Fatal signal 6 (SIGABRT), code -6 in tid 30707 (pool-2-thread-2)

This is after a few hundred red lines with warnings, errors and more, beginning with

DETECTED ERROR IN APPLICATION: input is not valid Modified UTF-8: illegal continuation byte 0x3f

Doing a little googling on this error, it appears that "There is a known NDK bug whereby GetStringUTFChars() incorrectly converts supplementary Unicode characters, producing an incorrect and invalid UTF-8 sequence. In my case, the resulting string was a JSON buffer. When the buffer was passed to the JSON parser, the parser promptly failed because one of the UTF-8 characters of the extracted UTF-8 had an invalid UTF-8 prefix byte." (http://stackoverflow.com/questions/12127817/android-ics-4-0-ndk-newstringutf-is-crashing-down-the-app) Indeed the app is trying to get a string of tekst from the database which contains special UTF8 characters.

In the simulator, the app does not crash, surprisingly, but is displaying the UTF8 characters incorrect. ó's are changed into ?ffffffc3??ffffffb3??ffffffc3??ffffffb3?, and • is turned into ?ffffffe2??ffffff80??ffffffa2? and so on.

This looks related to #19 and a few others, with the difference being that when I try to insert just one or two emojis from above U+1Fxxx, the sqlite insert works just fine. But when I try to insert more than 17 such characters (need not be contiguous), my app crashes.

What is weirder is that this figure of 17 often varies, sometimes my app crashes at 18 such characters, sometimes it only crashes at 20 such characters, but never seems to work for more than 20.

In my code, I am simply reading off an input-text box, storing the value in a javascript var, and then inserting into the db using a parametrized query. On debug, I checked that my variables aren't at fault, and the crash comes from within the cordova exec, sadly I am unable to setup crash reporting on my phone in order to get a stack trace.

The same inputs without these 17+ emojis work, with everything else being the same.

Since this is not an error on reading a database entry into a string (utf8 or not), the suggestion to use NewString() instead of NewStringUTF() doesn't really apply.

Testing this on a Moto G3, 3rd Gen with Android 6.0

Here is a code snippet -

var userfile = app.researcherFile;
var mMessage = document.getElementById("id-message-body").value;
var mLanguage = this.getRadioVal( document.getElementById('language-form'), 'language' );
var mPlace = document.getElementById('input-place').innerHTML;
var mDate = document.getElementById('inputDate').value;
	
var db = null; 
	
var storage = window.localStorage;
if (storage.getItem("DBUploaded") == null)
	storage.setItem("DBUploaded","0");		
	
window.resolveLocalFileSystemURL(cordova.file.externalDataDirectory, function(externalDataDirectoryEntry) {

	toBeUploaded = 1;
	console.log('Full message ; ' + mMessage ); //This comes fine 
	db = window.sqlitePlugin.openDatabase({name: userfile, androidDatabaseLocation: externalDataDirectoryEntry.toURL()});
	db.transaction(function(tx) {
		tx.executeSql("INSERT INTO MESSAGES (Message, Language, Place, DateOfMessage) VALUES (?,?,?,?)",[mMessage,mLanguage,mPlace,mDate]); //crashes on 17+ emoji characters
	}, function(error) {
		console.log('Populate database error before close: ' + error.message);
		db.close(function() {
			window.plugins.toast.show('Database Error while entering record: ' + error.message, 'long', 'bottom', function(a){}, function(b){});
		});
		return;
	}, function() {
		console.log('Successful db insertion. Before close');
		db.close(function() {
			storage.setItem("DBUploaded","0");
			window.plugins.toast.show('Message added to Database!', 'long', 'bottom', function(a){}, function(b){});
			app.s3Upload(userfile, false);
		});
	});
});

• fix Android error message (#40)
• resolve crash issues - will be resolved in another release
• recent updates from cordova-sqlite-storage

Recent updates from cordova-sqlite-storage can be considered non-functional, mostly in testing, documentation, and internal error reporting.

Commit:
storesafe/cordova-sqlite-storage@70eaa9d

Issue:
storesafe/cordova-sqlite-storage#833

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 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

On a few devices, e.g. Galaxy S6 it is frequently taking a very long time for a simple SELECT statement. The statement below can take as long as 161 seconds to come back. Other times it will take less than 100 milliseconds. At the very bottom is the table structure.

It was happening on the Cordova-sqlite-storage plugin so I installed this plugin as you recommended and I am still seeing the problem.

You mentioned possible causes may be:

• Java-side garbage collection cycles
• Delays due to sqlite disk I/O

Is there something I can do to trouble shoot this?

//=====================================================
var startCheckElogLock = new Date().getTime();
var deferred = $q.defer();

var sql = "SELECT * FROM elog WHERE starttime >=" + logDate + " AND starttime < " + (logDate + DAY_UNIX) + " AND status = 4 ORDER BY starttime";
appDB.exeDBSQL(sql)
.then(function (r) {
deviceLog("checkElogLock: Run Time Get the time for lock: " + (new Date().getTime() - startCheckElogLock), function (str) {console.log(str)});
deferred.resolve();
}
});
//=====================================================

//=====================================================
// SQL EXECUTER
//=====================================================
app.service('appDB', function($q, $rootScope) {
function exeDBSQL(sql, param) {
var deferred = $q.defer();
db.executeSql(sql, [], function (results) {
sqlResult = new Object();
sqlResult.return = true;
sqlResult.tx = 0;
sqlResult.param = param;
sqlResult.sql = sql;
sqlResult.results = results;
$rootScope.unableToRecordData = false;
deferred.resolve(sqlResult);
}, function (err) {
sqlResult = new Object();
sqlResult.sql = sql;
sqlResult.return = false;
sqlResult.tx = -1;
sqlResult.err = err;
$rootScope.unableToRecordData = true;
deviceLog("appDB.exeDBSQL Failed, err=" + err.message, function (str) {console.log(str)});
deferred.resolve(sqlResult);
});
return deferred.promise;
}

return {
exeDBSQL: exeDBSQL
};
});

//======================================================
tx.executeSql("CREATE TABLE IF NOT EXISTS elog (status, starttime, endtime)");
appDB.exeDBSQL("ALTER TABLE elog ADD COLUMN lat TEXT");
appDB.exeDBSQL("ALTER TABLE elog ADD COLUMN lon TEXT");
appDB.exeDBSQL("ALTER TABLE elog ADD COLUMN uploaded TEXT");
appDB.exeDBSQL("ALTER TABLE elog ADD COLUMN city TEXT");
appDB.exeDBSQL("ALTER TABLE elog ADD COLUMN state TEXT");
appDB.exeDBSQL("ALTER TABLE elog ADD COLUMN inspectiontime TEXT");
appDB.exeDBSQL("ALTER TABLE elog ADD COLUMN defects TEXT");
appDB.exeDBSQL("ALTER TABLE elog ADD COLUMN violation TEXT");
appDB.exeDBSQL("ALTER TABLE elog ADD COLUMN reason TEXT NOT NULL DEFAULT ''");
appDB.exeDBSQL("ALTER TABLE elog ADD COLUMN odometer TEXT");
appDB.exeDBSQL("ALTER TABLE elog ADD COLUMN assetid TEXT");
appDB.exeDBSQL("ALTER TABLE elog ADD COLUMN inspectionassetid TEXT");
appDB.exeDBSQL("ALTER TABLE elog ADD COLUMN resolvedby TEXT");
appDB.exeDBSQL("ALTER TABLE elog ADD COLUMN resolution TEXT");
appDB.exeDBSQL("ALTER TABLE elog ADD COLUMN parts TEXT");
appDB.exeDBSQL("ALTER TABLE elog ADD COLUMN eventstatus TEXT");
tx.executeSql("CREATE INDEX IF NOT EXISTS timeStatusElogIdx ON elog (starttime, endtime, status)");
//=============================================================

I discovered that as of sqlite 3.12 they changed the default page size from 1024 to 4096 ref: https://www.sqlite.org/pgszchng2016.html. Unfortunately I did not find this until I made a customer release.

In other versions such as cordova-sqlite-storage, cordova-sqlite-ext, and cordova-sqlcipher-adapter I explicitly kept the old page size (1024).

I plan to make the next release of this version with the old page size of 1024 as well for the following reasons:

• better consistency
• more efficient for small, frequent changes
• more efficient for apps that store very little data

I discovered from the test suite that database file names with multi-byte UTF-8 characters such as accented (European) characters and Euro sign do not work on Android x86 or x86_64 platform. (This is NOT an issue in case of the builtin android.database implementation which is enabled by the androidDatabaseImplementation: 2 setting in sqlitePlugin.openDatabase.) This is very likely related to #19 (multi-byte UTF-8 characters not working on Android) but I do not expect this to be solved by the solution contributed in storesafe/android-sqlite-evcore-ndk-driver-free#2.

Error messages from default Android NDK access implemenation (Android-evcore-native-driver) do not show as much information as error messages on the other platforms.