I have a desktop app that heavily relies on a standard sqlite database, which is located in the user’s SpecialFolder.ApplicationData
When the app is first run there is no database, so it creates the database and creates 2 essential tables. On subsequent runs users can add new data/tables, but the first 2 tables are for the backend of the app and are never deleted.
I have also implemented an error-reporting system whenever there is a database problem.
My problem - most days I am getting reports from my app in the wild where one of my 2 essential tables is trying to be created when it should already exist, or a table is reported as not existing when it clearly should exist (eg created when the app is first run).
What might be causing this issue? Are some users trying to manipulate the database directly? Or can a database connection be lost mid app? Can a running app lose a database connection under any circumstance, and therefore not detect existing tables, and try to create then anew?
I should mention that I have stepped through my app very carefully, checking the database with a sqlite database browser. I can’t see any way my app could try to re-create my essential initial 2 tables. I can’t recreate the issue on my machine. I have sold several hundred copies of my app, and my error reports are very few (less than one per day), meaning the vast bulk of users are not seeing issues.
first assumption… that everyones specialfolder.applicationdata is on a local drive and not a remote server
second assumption… that you application is SINGLE user, and the database is not shared (SQLite isn’t real big on multi-user unless handled carefully)
third assumption… there is nothing in your code that might close the db connection, and reopen it with a path designator other than the one you specified
fourth assumption… that you have a SUBFOLDER for this app in the AppData location, and nothing is altering the permissions
fifth assumption… either the data base is NOT encrypted,
sixth assumption… if it IS encrypted, that you have proper error checking in place to insure that attempts to access it without the proper password are caught and dealt with (error code 21 and/or 26 I think)
are any of these assumptions incorrect, or inaccurate?
I have SQLite applications running on a local hard connected drive, others running on LAN network drives, and the only time connections are “lost” is when I close the DB or the LAN faults (in which case the app errors out anyways)
I thought for a given desktop OS, the SpecialFolder.ApplicationData was fixed, and not changeable by the user.
My app is single user only.
My db is only closed when the app closes, never mid app.
Yes, I have a subfolder with my app name created in ApplicationData. Can’t see why anything would change permissions on that folder, unless the user did it manually.
db is not encrypted, no password needed to access.
For some reason, at rare times, the app fails to detect the existing db, so tries to create a new one, including the 2 essential tables. Then it errors out with “table xxx already exists”
I do have a table that stores the (paid) activation licence. I have a suspicion some people are deleting or altering that table manually to try to get around the registration. Then the app fails to detect the table, and fires.
I’m going to triple check my code, to make sure the “create db” sub doesn’t get called inappropriately.
You said that users can create their own tables. What happens if the user creates their own with the same name as one of yours? Are they then allowed to delete that table?
Good thought, but the tables that users create have specific names based on their business name and tax year. It is impossible for them to replicate the 2 essential table names.