WebSQL: native vs plugin?

[Pete]

I have a legacy, internal use only app that uses native websql and works fine on our iOS and Android devices.

I'm developing a new app for public consumption and I'm also using native websql and running into a few problems:

The open looks like this:

  window.serDB = window.openDatabase("mytable", "1.0", "medical alerts", 1 * 1024 * 1024);

The open gets called after the ondeviceready event.

The create works and looks like this:

     tx.executeSql("CREATE TABLE IF NOT EXISTS mytable (msg_id UNSIGNED INT PRIMARY KEY ASC, msg_when DATETIME, msg_read INTEGER, title TEXT, msg_text TEXT)",

     

I add records using this query and this works well as do the SELECT queries:

     tx.executeSql("INSERT OR REPLACE INTO mytable(msg_id, msg_when, msg_read, title, msg_text) VALUES (?,?,0,?,?)",[...]

The data looks like:

    json: {"status":"OK","count":1,"last_id":"44","when":"2016-08-23 11:21:01","alerts":[{"msg_id":"44","title":"Medical Emergency","text":"Heart Attack","msg_when":"2016-08-05 14:52:03"}]}

I want to delete rows that are over 'x' days old and I'm using this query but nothing gets deleted, the success callback is being called with no rows effected.    

     tx.executeSql("DELETE FROM mytable WHERE msg_when <= datetime('now', '-" +days +" days')", [],

I've also tried:

     tx.executeSql("DELETE FROM mytable WHERE msg_when <= datetime('now', '-4 days')"; [],

Neither works.

Other than deleting rows by date there are two other problems:

   tx.executeSql("DELETE FROM mytable;", [],

Does not delete all the records... in fact, it doesn't delete any.

Drop table doesn't drop the table.

   tx.executeSql("DROP mytable;", [],

Essentially this is the same code as in the legacy app but that app hasn't been rebuilt in well over a year). I do suspect it's my code as it doesn't work in Chrome either and it should work there. Any clues or hints?

Lastly, have things changed enough that I need to start looking for a plugin?  Are the plugins more reliable? Which plugin will get me there with the least amount of recoding?

Thanks!

P.

[Kerri Shotts]

Pete,

A couple quick thoughts:

- You've got semicolons at the end of your DELETE and DROP statements; remove them and they should work.
- As to why your datetime statement is failing, try wrapping your when column with a datetime function as well so that you're comparing dates to dates.
- I do suggest a plugin for SQLite just because it gets around the quota limitation that the native implementation has (5-10mb). I use https://www.npmjs.com/package/cordova-sqlite-ext

[Phil Petree]

Hi Kerri.

Those semis weren't in the original code... I think when I was editing for clarity I must have popped them in.

The "DELETE FROM mytable" simply is not working on native sqlite/sebsql on either ios, android or Chrome.  To fix this I had to add a WHERE caluse that will always be met such as "DELETE FROM mytable WHERE id > 0" which will always be true in my case. 

The DELETE FROM mytable WHERE msg_when < some_date_in_the_past is simply a lost cause on the native websql. I've tried deleting based on msg_when < datetime('now', '-4 day'), <=, msg_when <= datetime(2016-08-22T00:00:01) (with and without the T) and msg_when <= delDate where delDate = varDelDate = "2016-08-22T00:00:01"; and dozens of other variations.

I even tried converting the datetime column to 'unsigned big int' and converting the datetime to ms since 1970 but ios can't handle that either and the date gets messed up.

This one of two remaining bugs before release... 

--
-- You received this message because you are subscribed to the Google
Groups "phonegap" group.
To post to this group, send email to phon...@googlegroups.com
To unsubscribe from this group, send email to
phonegap+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/phonegap?hl=en?hl=en

For more info on PhoneGap or to download the code go to www.phonegap.com
---
You received this message because you are subscribed to a topic in the Google Groups "phonegap" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/phonegap/za7z51_fKRw/unsubscribe.
To unsubscribe from this group and all its topics, send an email to phonegap+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Kerri Shotts]

Pete --

OK; that makes sense.

You might just want to switch over to a plugin for this. The one I linked to in the previous post is the one I use, and it's just worked for me, including dropping tables and such. The only thing you should need to change would be the initial openDatabase call as the rest of the syntax is compatible with the (sadly deprecated) proposed standard.

Also -- is msg_when using a "date" type or a "string" type?

As for your DELETE with a clause, you should always be able to DELETE FROM table WHERE 1=1, that way you don't have to rely on field names. BUT, SQLite says is should work without a clause. Hmm. I'd try the plugin version and see if that fixes it.

On Tuesday, August 30, 2016 at 11:43:50 AM UTC-5, Pete wrote:

...

[Phil Petree]

okay, I've downloaded it and opened the docs page. Are they saying there's some compatibility issues with iOS and CL builds? 

I'll give it a test build tonight or tomorrow... with luck!

[Kerri Shotts]

Not quite sure what you mean, unless you're referring to the test passes, in which case it just refers to the continuous integration testing, not the functionality. There are quirks between both iOS and Android, but mostly in the openDatabase call (such as specifying the db location).

On Tuesday, August 30, 2016 at 2:01:58 PM UTC-5, Pete wrote:

...

[Pete]

For anyone else who runs into this problem of selecting or deleting based on date comparisons, I FINALLY found the solution:

There's actually two tricks to solving this.

First, if you declare your field as a datetime (which is nothing more than an integer) you will NEVER get this to work.  Not under iOS 9.x, not under Android and not on Chrome 52.x (64-bit) on Windows 10.

What you have to do is store your date as a TEXT field and store your datetime as a string like this:

tx.executeSql("CREATE TABLE IF NOT EXISTS mytable (msg_id UNSIGNED INT PRIMARY KEY ASC, msg_when TEXT, msg_read INTEGER, title TEXT, msg_text TEXT)",

Next, you have to wrap your comparison in (parenthesis) so that sqlite treats it as a formula and does the math conversions for you... like this:

tx.executeSql(DELETE FROM mytable WHERE (msg_when <= datetime('now', '-4 days'))",

Notice the parenthesis before the (msg_when column and after the closing parenthesis on the datetime)) function.

[Chris Brody]

Thanks Pete for reporting your solution. I have a couple questions out of curiosity:

1. Did you try this with the Cordova sqlite plugin (cordova-sqlite-ext), Web SQL, or both?

2. Do you do anything special or just use a JavaScript Date object in the SQL parameter array?

I think you are right that this can be REALLY tricky. I suspect you should be able to store the numeric value of a JavaScript Date object by using the Date.valueOf() member function. If you just store a JavaScript object, the sqlite plugin uses the toString() function to get the string value and Web SQL seems to do the same thing.

I plan to test this with both Web SQL and the sqlite plugin and document it when I get a chance. I opened the following issue for tracking: https://github.com/litehelpers/Cordova-sqlite-storage/issues/546

I would also like to thank Kerry Shotts for helping with this along with MANY other Cordova/PhoneGap issues.

Disclaimer: I am the owner and primary maintainer of Cordova-sqlite-storage, cordova-sqlite-ext, and some other sqlite plugin versions.

Chris

[Phil Petree]

Hi Chris.

We were going to implement your plugin but there were two problems:

1) We're using Intel's XDK and your default plugin wouldn't install. We had to go to the commercial version and, while that did install, there were some misgivings at having to pay the royalties and....

2) Implementing your plugin would require a whole new set of test suites and every aspect of our db i/o would require retesting on all devices and through our test base. 

Since we're on close down, 30 days late and this was the last bug, no one had the stomach for #1 or #2.

Yesterday I awoke with of those "moments" and decided to change the msg_when from DATETIME to TEXT and adding the parens.  I went through several different query/deletes, including converting the json string to a javascript object etc. I probably have 12 different variations of DELETE including:

var msg_when = msg_when_string(days);  // today's date -n days as string "YYY-MM-DD hh:mm:ss" with and without 'T'

var msg_stale = new Date(msg_when);    // convert to this date 

// the following were all tried with msg_when defined as DATETIME

//    var query = "DELETE FROM mytable WHERE msg_when <= " +msg_when;

//    var query = "DELETE FROM mytable WHERE (msg_when <= " +msg_when +")";

//    var query = "DELETE FROM mytable WHERE msg_when <= " +msg_stale;

//    var query = "DELETE FROM mytable WHERE msg_when <= datetime('now', '-" +days +" day')";

//    var query = "DELETE FROM mytable WHERE (msg_when <= datetime('now', '-" +days +" day'))";

Changing the field from DATETIME to TEXT the following query didn't work.

var query = "DELETE FROM mytable WHERE msg_when <= datetime('now', '-2 day')";

Wrapping the condition in parens did.

var query = "SELECT * FROM alerts WHERE (msg_when <= datetime('now', '-2 day'))";

The data is inserted with a simple INSERT statement:

INSERT OR REPLACE INTO mytable(msg_id, msg_when, msg_read, title, msg_text) VALUES (?,?,?,?,?)",

        [msg_id, msg_when, 0, title, text],

In the above, regardless of whether the msg_when was defined as DATETIME or TEXT the data passed in was a ISO 8601 string received as a json packet from a mysql query.

Also... Since, according to the docs, the sqlite/websql data type DATETIME is an INTEGER (one would assume an unsigned int) about 10-12 days ago we tried changing the data type from DATETIME to UNSIGNED INTEGER, converting the date to ms and storing the int and later doing the DELETE by comparing stored INT vs new INT. That worked under Chrome but not under iOS.

Truthfully, I think any reasonable programmer would expect that when the datatype on the left side of the equation is the same as the datatype on the right side of the equation that the formula would work. Clearly, in the various tests that wasn't the case nor is it in the case of the working solution.

Let me know what else I can do to help!

Pete

--

[Chris Brody]

Hi Pete,

Thanks for the detailed response. I guess it would make sense to use a TEXT field if it solves the problem, though a bit less efficient than using a NUMERIC type such as DATETIME.

If you say "converting the date to ms and storing the int" worked for Android but not iOS, I am curious how you did the conversion. If you can post some sample code of what did work on Android but not iOS it would be great. If not, no problem, I will test it myself someday along with integer date comparisons and post a solution if I find one. The sqlite-storage test suite already tests the storage and retrieval of very large INTEGER date values and it works in both Web SQL and the plugin on all supported platforms.

I think we found a major gotcha about SQLite that I think should get a glaring WARNING note. While a DATETIME type is a NUMERIC datatype, the SQLite datetime function returns a STRING. Since SQLite is case insensitive we have a really strange case of overloading here. And in certain cases SQLite does not even do type checking. It would be possible for one programmer to store a STRING in a DATETIME field and then for the next programmer to write a SELECT statement that does a NUMERIC comparison. Here is an example from my sqlite3 command line (MacBook Air):

$ sqlite3

SQLite version 3.8.10.2 2015-05-20 18:17:19

Enter ".help" for usage hints.

Connected to a transient in-memory database.

Use ".open FILENAME" to reopen on a persistent database.

sqlite> CREATE TABLE tt(name TEXT, created DATETIME);

sqlite> INSERT INTO tt VALUES ('Chris', DATETIME('NOW'));

sqlite> SELECT * FROM tt;

Chris|2016-09-01 19:15:24

sqlite> .dump

PRAGMA foreign_keys=OFF;

BEGIN TRANSACTION;

CREATE TABLE tt(name TEXT, created DATETIME);

INSERT INTO "tt" VALUES('Chris','2016-09-01 19:15:24');

COMMIT;

In terms of supporting Intel XDK as well PhoneGap Build etc., the Cordova-sqlite-legacy-build-support version also supports these tools and has the permissive licensing scheme. I think it would be better than using Web SQL and I am looking for a better long-term solution to this problem. I can also understand about the issue with retesting, though if your app is successful your company will have to go through the pain at some point. You are welcome to contact me privately if you have any questions about either of these points.

Thanks,

Chris

phonegap+u...@googlegroups.com

For more options, visit this group at
http://groups.google.com/group/phonegap?hl=en?hl=en
 
For more info on PhoneGap or to download the code go to www.phonegap.com
---
You received this message because you are subscribed to a topic in the Google Groups "phonegap" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/phonegap/za7z51_fKRw/unsubscribe.

To unsubscribe from this group and all its topics, send an email to phonegap+u...@googlegroups.com.

[Phil Petree]

Chris, if you look, the original comparisons were text to text and those failed.

Also, on one my test devices (iPad2 with iOS 9.3), I just updated the app without updating the table. So the query was:

DELETE FROM mytable WHERE (msg_when <= datetime('now', '-" +days +" day')) and that failed.

When I uninstalled the app, reinstalled so the table was created with msg_when TEXT and ran the query it worked.

The only change was changing the datatype in the create table from msg_when DATETIME to msg_when TEXT.

phonegap+unsubscribe@googlegroups.com

For more options, visit this group at
http://groups.google.com/group/phonegap?hl=en?hl=en
 
For more info on PhoneGap or to download the code go to www.phonegap.com
---
You received this message because you are subscribed to a topic in the Google Groups "phonegap" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/phonegap/za7z51_fKRw/unsubscribe.

To unsubscribe from this group and all its topics, send an email to phonegap+unsubscribe@googlegroups.com.

[Chris Brody]

Thanks Pete, that seems to make sense.

Looking through your findings I have identified a number of pitfalls related to SQLite date/time handling at: https://github.com/litehelpers/Cordova-sqlite-storage/issues/546#issuecomment-244237736

I am sorry to hear about your difficulties in general. An idea under consideration may be to fix cordova-sqlite-ext to include the SQLite3 dependencies needed to support some of the newer tooling: https://github.com/litehelpers/Cordova-sqlite-storage/issues/477#issuecomment-244244286