Posted on by George Henne

When do databases get overwritten?

Recently, Thomas Gruber raised the question about when databases get overwritten on an iOS 6 device. We looked into and found it was indeed a good question. First, there were some bugs in the SQLImport() function that needed to be fixed. Second, we found that a couple more options were needed to make it really useful. Make sure you are running at least version 2.6.0.2 of App Studio to get these features.

We made changes to the 4th parameter of SQLImport():

   SQLImport(Json, DB, callback, overwrite)

The fourth parameter, overwrite, now has 4 possible values. It can be used in the function and in the manifest.

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.

The old True/False options for the overwrite parameter will continue to work as before.

Also, SQLImport now returns a string with the status of the callback function.

Here are some use cases. (You’re welcome to rerun the tests on other devices or versions of the OS – let me know if you get different results!)

I used a modified version of the Northwind sample. I added an Update button to the Header bar, with this code:

Function HeaderBar1_onclick(button)
  s=Array(["UPDATE Customers Set CompanyName='NewCo' WHERE CustomerID='ALFKI';", loadGrid])
  Sql(DB,s)
End Function

This code updates the first row of the table with a different company name. We will use this to see if a change to the database gets overwritten.

  1. I ran the app on an iOS 6 device as a web app. The line in the manifest read:

    Northwind.db,DB,loadGrid

    The database was created. I clicked on update, then restarted the app. Was the update still there?

    Yes. Restarting the app does not rewrite the database.

  2. I made a change to the app and deployed again. The app reloaded. Was the update still there?

    No. Since no overwrite parameter was supplied, it defaulted to true. The database was overwritten.

  3. I changed the manifest to read: 

    Northwind.db,DB,loadGrid,NSB.overwriteNever

    and went through the same steps again Was the update still there?

    Yes. Since overwrite is Never, the database did not get rewritten and the changes were preserved.

  4. Next, I saved the app to the Home screen. Was the update still there?

    No. When it gets added to the Home screen, it starts out as a new app again. The original database from the server is loaded. The change to the database is lost.

  5. I made a change to the app and deployed again, then restarted the app on the Home screen. It reloaded the app. Was the update still there?

    Yes, the database did not get reloaded since override was still Never.

  6. Is this different than in iOS 5?

    Yes – in one spot. In iOS 5, when you save the app to the Home screen, the updates to the database are not lost.

  • What about Android?

    It works the same way. Data is not lost when you save to the Home screen.

    Database Version Numbers

    This update also allows to control overwriting by version number. Each database as a version number. To check the version number of a database, use the DB.version property:

      MsgBox DB.version

    To set the version of a database, use the DB.changeVersion() function:

      DB.changeVersion(oldVersionString, newVersionString)

    The versionStrings can be anything you want: a number, a date, a description, or a hash total. You can use DB.version as the oldVersionString:

      DB.changeVersion(DB.version, newVersionString)

    The NSB.overwriteIfVersionDifferent and NSB.overwriteIfVersionSame options do not check if the version string on the incoming database has a higher value than the existing one: it only checks to see if they are the same.