I have a REALSQLDatabase file that should get backed up every five minutes. This is done by using a timer object that is creating a copy of the file to a temporary folder.
But… I found no information about what happens if the user writes a record into the database file exactly at the same time the backup is created… would this lead to a destroyed backup, an exception error (which ?) or can I rely upon the operating system (OS X, Windows) that the file is blocked until write access has been completed.
Well the backup routine is planned as a timer/thread combination to make creating large backups invisible to the user.
Yes, it’s a single user database.
I’m aware that the risk is quite low that both actions are processed at the same time… but data is precious, especially if it gets lost.
I already thought about using an app-wide flag (boolean) that is checked before writing something to the database. But this would be difficult to realize (vast parts of the db functions have been already completed).
What do you mean with Xojo is providing a new interface for backing up the database ? I’m on RS not switching to Xojo till it runs stable (in about one year or so ;-). Isn’t the “new” DB in Xojo just the renamed REALSQLDatabase aka SQLite ?
I’d recommend a log file approach. Store the raw SQL commands themselves in a different database. If the first one is ever lost you can rebuild it in essence. Then do actual backups with less frequency.
The downside to your current approach is you are duplicating data a lot.
But perhaps someone is able to answer my original question: What happens if the database file is accessed (writing a record to it) while FolderItem.CopyFileTo is used ? The current DB is just about 1 MB in size, but let’s consider the DB is 50 MB and saving may take a few seconds (for whatever reason).
Most likely you end up with corrupted backup document. Use CriticalSection or Semaphore to delay backup thread until all writes have completed and transactions are commited. Personally, I’ve had problems with CriticalSection so in my apps I use Semaphore.