Looking for alternate method to replace a file in folder (newer db)

General
1

Hello. Some of my apps use a pre-filled db. Typically, when I update the db, I usually add in more data, so when I want to replace the current db with the new one in the updated app, I first check to see if it’s newer by running a quick query, and if the current recordcount is lower than the new, the old one is deleted in the folder and replaced with the new one.

I made some changes to one of the dbs, but didn’t add in any new records, so the above method wouldn’t work. Is there a way to check if the current db in the folder is not the newest one? Based on the date of the file or something else?

Here’s what my method looks like:

[code] dim sql as String = “SELECT * FROM RDexam”

Dim rs as RecordSet
rs = db.SQLSelect(sql)

if rs.RecordCount <879 then
Dim dbFile As FolderItem

db = new SQLiteDatabase

'finds the database in the Data folder
dbFile = SpecialFolder.ApplicationData.Child("com.visualveggies.rdpracticeexam").Child("examRDDTR.sqlite")

db.DatabaseFile = dbFile

'delete old db
dbFile.Delete

'copy new db
DoesExamTableExist

end if

ReconnectToDB[/code]

2

Add a table, something like “db_settings” with a column “version”. If that table/column doesn’t exist, you know it’s older. If it does, compare to the current version.

3

Simple and brilliant! Thanks Kem!

4

I would recommend having a place in the database where you store the database version number so that you can check if it needs updating in the future. I have seen it multiple ways, including a database settings table and using the pragma user_version https://www.sqlite.org/pragma.html#pragma_schema_version

Additionally, I would not recommend swapping the database every time there’s an update. I don’t know your use case, but this would lose any changes the user may have made. An update method that inserts the new data would be safer. You can even store the schema in a constant to keep your method clean.

5

Thanks Tim. Totally agree with not swapping it each time it’s launched. My above method just checks if there are more records present at each launch, and if there is (b/c of a software update), then the current is deleted and replaced with the new. This worked well for me up until now where the changes I made did not include new records. I like the version number field or table and will give that a go

For my scenario, this db that is being replaced is read only to the user, so they wouldn’t be saving any new data there. I have other dbs for the apps which the user does save data to, and these go unchanged, unless I’ve added in new columns or something, but I have a method to handle that so no data is lost

Appreciate the help!