Updating SQLite databases with Delphi

This post will take care about an issue that you can face when you are developing an Android application but you need to update your database. Seems simple, but you will notice that it can require to your user uninstall the previous application in way to get the new database to be used, and this not good!

Your code bypasses the normal creation/upgrade framework of the SQLiteOpenHelper class, so you have to handle the upgrade by yourself.  You can simply delete the old file by overwriting or inject SQL commands if you need to keep the data.

When you need overwrite you should go to the Deployment menu and ensure the Overwrite option is marked with “Always”, as it is the default. The bad here is that for some reason don’t work as it should 🙁 [if someone knows why, let us know].

Well, once an Android application is installed the APK remains on the /assets folder and (uncompiled source files associated with your project). The new executable (.so) will be extracted from the APK (Android Package – which is a plain zip file) but the /data from your package will not be touched! So you need to do a routine to take care about this.

I would recommend you to set a version on your SQLite database by execute the pragma command: PRAGMA user_version. By default it will return 0 as database version. Once you make a new change on the structure invoke the command with “PRAGMA user_version = #”, example: PRAGMA user_version = 3; it will mark the database.

Now, you can check for the user database version firstly, if the user_version matchs you go on, otherwise the code bellow will do the trick:

function DatabaseUpgraded(const ADatabaseFileName: String): Boolean;
   Zip: TZipFile;
   PackageName: JString;
   Result := False;
   PackageName := SharedActivityContext.getPackageResourcePath;
   if TFile.Exists(JStringToString(PackageName)) then
       TFile.Delete(TPath.GetHomePath + PathDelim + ADatabaseFileName);
       Zip := TZipFile.Create;
       Zip.Open(JStringToString(PackageName), TZipMode.zmRead);
       Zip.Extract('assets/internal/' + ADatabaseFileName, TPath.GetDocumentsPath, False);
     Result := True;

Include the System.Zip unit into and ensure that your database is CLOSED before call the routine. You can extend this function to check the pragma user_version and apply the upgrade at once.

This can be used from Delphi XE6 and up.


Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *