SQLite DB not being installed

1

I am using SQLite in a VoltBuilder app that allows the user to periodically update the database by pressing a button in the app. During the process, I download a new database and import it with NSB.overwriteAlways; the whole process works as expected.

When the app starts I specifiy NSB.overwriteNever (in extrafiles) so any database updates will not be overwritten.

I am having an issue during testing. If I do an initial installation of the app on a phone that has never had it installed, the database packaged with the app installs as expected. I can update the database as described and everything works.

If I delete the app from the phone and try to do the process over again, the database packaged with the app does not install. I am using (NSB.overwriteNever |only write out database if it does not exist yet.)

When an app is “deleted” does it remove EVERYTHING?

According to the documentation, if no database exists, “overwriteNever” should install the database packaged with the app.

Am I missing something?

Thank you,

NSB.overwriteAlways |overwrite the existing database (default).
NSB.overwriteNever |only write out database if it does not exist yet.
NSB.overwriteIfVersionDifferent |overwrite if the version number of the database has changed.
NSB.overwriteIfVersionSame |overwrite if the version number of the database is the same.

2

I’ll try to duplicate this here - be patient, it’s a bit of work to do.

3

I appreciate your help!

4

Are you using an SQLite plugin or the SQLite built into the browser?

If a plugin, which one?

Android, iOS or both?

Debug or release build?

5

cordova-sqlite-storage plugin

iOS 14 uses the plugin
Android does not use the plugin
debug

Thank you

6

Have you tested this on both iOS and Android? Is it the same problem on both?

7

Since the time of your last response I retested…

Android 10
Delete the app
Install the app with overwriteAlways
Database is installed properly and overwritten each time the app starts
Delete the app
Install the app with overwriteNever
Database is not installed
Run the app and import a database using a function of the app
Database is installed and works as expected
Close app and restart the app
Database that was imported is still present

iOS 14
Delete the app
Install the app with overwriteAlways
Database is installed properly and overwritten each time the app starts
Delete the app
Install the app with overwriteNever
Database is not installed
Run the app and import a database using a function of the app
Database is installed and works as expected
Close app and restart the app
Database that was imported is still present

As mentioned initially, it appears overwriteNever detects a database even though the app has been deleted…

8

I am using cordova-sqlite-evcore-extbuild-free cordova plugin on Android and when I delete the app (uninstall) it does not delete the database. I don’t seem to have this issue on iOS.

9

The plugin works by creating the database in the standard filesystem, not in the browser cache as the built in SQLite does.

The plugin docs are silent about whether the database gets deleted if the app is deleted.

10

Do you change your config.xml when you switch from iOS builds to Android builds?

11

I did more testing…

iOS 12, Android 10 (No plugin) - overwriteAlways & overwriteNever works as expected.
After the app is deleted, overwriteNever creates a new database.

iOS 14, (No plugin) - no database is installed.

iOS 12, iOS 14, Android 10 (Plugin present) - overwriteAlways works as expected.
After the app is deleted, overwriteNever does not create a new database.

12

Did you see the question in my last response?

13

Yes, I saw it… That’s why I did the testing, to show what happens when the config file is changed to add or remove the sqlite plugin for each platform.

Whenever the plugin is present, overwriteNever does not create a new database.

As long as Android doesn’t need the plugin, I can simply leave it out of the build. I will however have the same issue as I do with iOS 14 if that ever changes.

iOS 14 needs the plugin to function. Based on my testing, if the app is ever deleted from a device, it could not be reinstalled correctly with overwriteNever as the option.

Can the value specified in the extrafiles be overridden once the app starts, or can this function be performed by the app without the extrafiles entry?

In the end, specifying overwriteNever is exactly what I need, as long as it knows it should create a new database if one does not exist… The plugin is apparently affecting this somehow…

Thank you

14

I tested this on Android, and could not reproduce the problem. Here’s what I did:

  1. Loaded the Northwind sample. I set extrafiles to
    Northwind.db,DB,loadGrid,NSB.overwriteAlways
  2. Made an Android debug build with VoltBuilder.
  3. Installed it on an Android 10 device.
  4. Ran it - the app loaded and the database displayed.
  5. Deleted the app
  6. Installed it again.
  7. Ran it - the app loaded and the database displayed.

Am I doing the right steps?

15

overwriteAlways works after the app is deleted… overwriteNever does not install a new database after the app has been deleted. But this happens, only if the sqlite plugin is in config.xml.

Even though you know it’s not needed at this point, redo the test with overwriteNever and the plugin in config.xml.

Then, do an iOS build (you need the plugin for sqlite on iOS 14) with overwriteNever and the plugin in config.xml. The db install will fail if the app was deleted prior to app install.

The test with Android 10 seems unnecessay at the moment since Android is still supporting WebSql. But it demonstrates that the plugin affects the overwriteNever install. During a transition period where Android stops supporting WebSql, an Android build would require the plugin. It appears this would cause the overwriteNever issue on all target devices, even if they didn’t actually need the plugin.

The real issue, at the moment, is that iOS 14 requires the sqlite plugin and it has the overwriteNever issue…

Thank you

16

I was testing on Android since it’s a bit easier for me to debug on. If I use overwriteNever on Android, with the plugin, it appears to work the same way as iOS.

So, the good news is that I’ve managed to duplicate what you’re seeing.

Now I’m working on figuring out what is going on under the covers.

17

Thanks for your good work!!!

18

I think I know what is going on. WebSQL, as embedded in the browser, has a version property. overwriteNever checks that to see if the database needs to be overwritten.

The SQLite plugin doesn’t have a version property. The documentation for the plugin states:

  • In case your database schema may change, it is recommended to keep a table with one row and one column to keep track of your own schema version number. It is possible to add it later. The recommended schema update procedure is described below.

Look on this page under Database schema versions for more info.

19

That makes sense… I’m glad you were able to figure it out!

Do you anticipate modifying your code to accomodate this, or can you provide guidance as to how to best deal with this?

Thank you.

20

This is a well documented part of the SQLITE spec. In the original design implementation it was intended that you be able to query the existing version of the table and if it were older than what you needed then you could create the new database and then read records from the old and write them to the new.

By and large the SQLITE spec is dead and has been replaced with a new paradigm. People are maintaining these SQLite plugins because they are convenient and offer one benefit the new design doesn’t (they done get deleted when the device is low on storage).