PouchDB document-root table max_seq column not being set

[hgo]

I'm trying to put a document to db on an android device with Cordova-SQLitePlugin , this exception being thrown all time.

a statement with no error handler failed: column max_seq is not unique (code 19)

After some investigation I found out that document-store table has a column named max_seq which holds a sequence number, and in device it is not being filled, so first item being inserted with null value (i guess it is a corruption) then other insertions not being completed and the exception above being thrown because this max_seq column has an unique index.

CREATE TABLE 'document-store' (id unique, json, winningseq, max_seq INTEGER UNIQUE)

In a browser everything works fine, max_seq column is being filled incrementally for each row.

Am i missing to set an option while creating the db or is this a defect ?

I guess @nolanlawson is following the issues about PouchDB

Thanks!

Note: Android device 4.4.4

[brodycj]

AFAIK the sqlite library would not fill in the max_seq column in this case. Perhaps HTML5/Web SQL does something different, it would be good to add a spec test case. I will add a test case when I get a chance.

If this is allowed by http://www.w3.org/TR/webdatabase/, I cannot see it.

Simplest solution suggested is to fix the PouchDB websql adapter.

[nolanlawson]

@hgo If you could provide a live reproducible example or a sample Cordova app, that would be very helpful. :) We have over 1,000 unit tests for PouchDB, and this issue has never come up, so maybe your app has found a very special corner case.

[nolanlawson]

Also yeah, this is probably a bug on PouchDB unless the bug only occurs for the SQLite Plugin and not for WebSQL (e.g. in Safari/Chrome).

[hgo]

It's reproducible here: https://github.com/hgo/sp

to build & run please run commands below

bower install

npm install -g ionic

npm install

cordova plugin add https://github.com/litehelpers/Cordova-sqlite-storage

ionic run android

Adapter is being set here

[nolanlawson]

I'm sorry, but I can't reproduce in either Kitkat or Lollipop when I run your example. Also it has a bug: you forgot to include pouchdb-find in bower.json. Even with fixing that, though, the error I see is:

Uncaught TypeError: Cannot read property 'Keyboard' of undefined 

which is caused by a line in app.js that says:

if (window.cordova && window.cordova.plugins.Keyboard) {

Even when I fix that, though (by adding && window.cordova.plugins), I get:

Uncaught Error: [$injector:modulerr] Failed to instantiate module starter due to:
Error: [$injector:nomod] Module 'starter' is not available! You either misspelled the module name or forgot to load it.

Maybe you are using a different version of the Ionic CLI than me? I'm using 1.3.19.

Also, if you could please reproduce your bug without using the SQLite plugin (i.e. using the built-in WebSQL adapter), then that would immediately tell us whether this bug is in the SQLite plugin or in PouchDB. And if it is a bug in PouchDB, then a live HTML example that doesn't require running Ionic/Cordova would probably be easier to debug. Thanks. :)

[nolanlawson]

Ah, I just noticed your comment:

In a browser everything works fine, max_seq column is being filled incrementally for each row.

So if that's the case, then it seems this is indeed a bug in the SQLite plugin.

[hgo]

ah, i'm sorry, it was ionic keyboard

cordova plugin add com.ionic.keyboard

It's not reproducible on standart Webkit / Chrome or Safari browsers. websql adapter works fine with them. The problem only occurs with sqllite plugin.

If you remove plugin everything works fine.

Also I noticed that my repo contains the corrupted db here. luckily :)

[nolanlawson]

Ah, okay. Does it occur in iOS or only Android?

Sorry for being a bother, but it helps to narrow down the root of the problem. :)

[hgo]

Sorry I did not tested on iOS yet. I will update the comment when I try.
No problem :)

[brodycj]

Unfortunately I still cannot understand the real issue. I just made the following Jasmine test, which passes on both the plugin and browser built-in, on both Android and iOS:

      it(suiteName + "INTEGER UNIQUE column not filled in",
        function(done) {
          var db = openDatabase("INTEGER-UNIQUE-column-not-filled-in.db", "1.0", "Demo", DEFAULT_SIZE);
          expect(db).toBeDefined();
          db.transaction(function(tx) {
            expect(tx).toBeDefined();
            tx.executeSql("DROP TABLE IF EXISTS 'document-store'");
            tx.executeSql("CREATE TABLE 'document-store' (id unique, json, winningseq, max_seq INTEGER UNIQUE)");
            tx.executeSql("insert into 'document-store' (json) values (?)", ['[1,2]']);
            tx.executeSql("insert into 'document-store' (json) values (?)", ['[3,4]'], function(tx, res) {
              // correct:
              expect(true).toBe(true);
            }, function(err) {
              // not expected:
              expect(false).toBe(true);
              return false;
            });
          }, function(err) {
            // should not get here:
            expect(false).toBe(true);
            done();
          }, function() {
            // verify tx was ok:
            expect(true).toBe(true);
            done();
          });
        }, MYTIMEOUT);

[hgo]

Great, could you add some checks to test above, that tests max_seq column is incremented? It maybe sounds weird but in my case max_seq column was empty. Do you have a chance to look https://github.com/hgo/sp/blob/master/_pouch_myDb.sqlite this is my problematic db which fails all time as I said.

[brodycj]

Looking at _pouch_myDb.sqlite:

$ sqlite3 _pouch_myDb.sqlite
SQLite version 3.7.7 2011-06-25 16:35:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from sqlite_master;
...
table|document-store|document-store|9|CREATE TABLE 'document-store' (id unique, json, winningseq, max_seq INTEGER UNIQUE)
...
sqlite> select * from 'document-store';
2015-03-27T12:34:50.579Z|{"id":"2015-03-27T12:34:50.579Z","rev_tree":[{"pos":1,"ids":["e0c5940909b5703119a40b34863f929d",{"status":"available"},[]]}]}||

Looking at 'document-store', appears that they simply stored a timestamp in the id field; JSON document in the json field, and empty (null) values in the other two fields (winningseq and max_seq). Since there is only one row in 'document-store', the null value in max_seq is considered truly unique here.

[brodycj]

Also if I change my test:

      it(suiteName + "INTEGER UNIQUE column not filled in",
        function(done) {
          var db = openDatabase("INTEGER-UNIQUE-column-not-filled-in.db", "1.0", "Demo", DEFAULT_SIZE);
          expect(db).toBeDefined();
          db.transaction(function(tx) {
            expect(tx).toBeDefined();
            tx.executeSql("DROP TABLE IF EXISTS 'document-store'");
            tx.executeSql("CREATE TABLE 'document-store' (id unique, json, winningseq, max_seq INTEGER UNIQUE)");
            //tx.executeSql("insert into 'document-store' (json) values (?)", ['[1,2]']);
            //tx.executeSql("insert into 'document-store' (json) values (?)", ['[3,4]'], function(tx, res) {
            tx.executeSql("insert into 'document-store' (json, max_seq) values (?)", ['[1,2]', null]);
            //tx.executeSql("insert into 'document-store' (json, max_seq) values (?)", ['[3,4]', null]);
            tx.executeSql("insert into 'document-store' (json, max_seq) values (?)", ['[3,4]', null], function(tx, res) {
              // correct:
              expect(true).toBe(true);
            }, function(err) {
              // not expected:
              expect(false).toBe(true);
              return false;
            });
          }, function(err) {
            // should not get here:
            expect(false).toBe(true);
            done();
          }, function() {
            // verify tx was ok:
            expect(true).toBe(true);
            done();
          });
        }, MYTIMEOUT);

it fails in slightly different ways when run against the plugin and HTML5/WebKit versions of openDatabase(). (There is no difference in observations between Android and iOS versions). When running the test against the plugin, both the second insert SQL error callback and the transaction error callback are fired. When running the test against the HTML5/WebKit SQL database, only the transaction error callback is fired. I am adding the bug label due to this inconsistency.

This observation appears to be related to #232, but I would like to continue with more testing before drawing any conclusions. My aim is to keep this plugin working as consistently as possible with both the HTML5/Web SQL API, as specified at http://www.w3.org/TR/webdatabase/, and the existing WebKit implementation found in Android (Chrome mobile) and iOS (mobile Safari).

I think we can conclude that in my first test, sqlite would automatically populate the max_seq column. (Normally sqlite will use automatically increasing values.) My time is actually very limited so I cannot promise a more definite answer right now.