I am looking for the best way to check if an SQLite database has been updated outside of my app. My app loads db when the file is opened but it could be modified outside my app. How do I check if it has been so I can reload it?
Could you store a last modified time stamp and check it against the modified date/time of the file?
Yes that was what i thought about doing. Is that the right way to do it. I wasn’t sure if there was a prescribed way of doing this.
are you looking to see if data has been altered ?
or the schema ?
And if thats an issue why not just use an encrypted db and that way it can’t be altered outside (at least not easily)
Its not an issue if changes are made but I want to reload the database if it is edited outside my app. Data and/or schema.
Sqlite doesn’t give you an easy way to know that it has been edited
Tables don’t inherently have a last timestamp property for edits / updates
So you’d have to put one on every row, as an autofilled field
However IF someone edits one of the rows they CAN just edit that value as well
You’d need some way to fingerprint the entire table potentially
The schema is similar
I’d create an encrypted database & keep it in the application data dir
In there save whatever timestamps etc so it can’t be altered
And if it gets tossed out then you recalculate & recreate it
OR save this same kind of data in a preferences file of some kind
I thought as much… Many thanks everyone
Since SQLite is file-based, would’t checking the modification date of the file do the trick, or, are there cases in which SQLite client libraries might change the modification date of the file even if nothing has been modified in the database?
All that tells you is “something” may have changed (not for sure DID change) - touch will update the mod date without changing anything & there may be other ways to alter the mod date without making any actual changes
If you want to know what changed or if in fact something DID change it hasn’t really got a mechanism to do that
Well, OK, but given that folks don’t often run touch on their files, it seems to me it still might work. Correct me if I’m wrong, Mike, but I guessed that you just wanted to avoid re-loading the database every time you want to use it. Using the modification date of the file would at least limit the number of times you’d have to do this when it had not, in fact, been modified.
I am developing an SQLite editor. I notice with SQLite Professional (from MAS) that if I edit an sqlite database in another app SQLite Professional reloads it. It must be checking something to know its been edited.
Damn! It is really difficult to use this forum from an iPad: I just posted to this thread and then I tried to edit my post. As the icons on the upper right corner do not appear, I have to guess where the edit icon would be, and - once again - I hit the delete button…
Anyway, I suggested to use triggers to log changes, time stamps etc
That’s a completely different animal altogether. What it’s doing is watching the filesystem for change notifications. You’d have to do that with declares. Basically, you’ll establish a callback method and pass the address of that method to the OS and say, “call this method if this file changes.”
That sounds like what I need. I’ll look into this. Thanks Tim
Tim, you were right, the approach I am taking uses FSEventStream and is available in MacOsLib. You can monitor for filesystem events down to file level so when I open the db I will start monitoring the folder item for events i.e. a change and then notify the user the file has been modified outside the app and requires a reload.
Thanks all
I’m looking for the essentially the same thing. I currently have a Valentina DB client/server setup working well and is quite stable, however, data can get updated from different PCs and therefore I need to know when a record gets updated via a callback or notification so each PC display the current updated data.
I’ve done a Google search and found that there is a such a mechanism listed for SQLlite databases. Check it out here.
Is such a mechanism available for Valentina? Has anyone else know of anything similar?
Cheers
Grant