Monitoring an SQLite database with QFileSystemWatcher does not work as expected
-
nope, this is just how
QFileSystemWatcherworks.QFileSystemWatcherworks with polling. It has a timer internally that every second will check the paths you added and store the values. Then, after 1 second, it will check again and if they are different it will trigger the signal.
It's not a chain of signals that gets triggered bym_db.commit();and so it will not haltDatabase::registerPlayers. It might take up to 1 second from when control goes back to the event loop to when the signal is triggered.What you could do is:
- add a
int fileChangeCounter=0;private member ofDatabase - replace
connect(m_dbFileMonitor, &QFileSystemWatcher::fileChanged, this, &Database::dbFileChangedExternally);with
connect(m_dbFileMonitor, &QFileSystemWatcher::fileChanged, this, [this]()->void{ if(--fileChangeCounter<0) { fileChangeCounter=0; dbFileChangedExternally(); } }- delete
m_dbFileMonitor->blockSignals(true); - replace
m_dbFileMonitor->blockSignals(false);with++fileChangeCounter;
But it will still fails if the file will be changed externally in the small span of time between you change the file and the watcher picks it up
P.S.
I'm not 100% sure (I don't know SQLite that well) but I think even a rolled back transaction would change the file - add a
-
QFileSystemWatcher works with polling.
I assumed it used the native facilities from the OS....
@JonB said in Monitoring an SQLite database with QFileSystemWatcher does not work as expected:
I assumed it used the native facilities from the OS....
It can, but it depends on the OS. It will, for example, use
inotifywhere possible (and considered reliable), which doesn't require polling, but does poll in lots of other cases.See QFileSystemWatcherPrivate::createNativeEngine() for some of the engines that might be used.
Cheers.
-
Hi
Do you simply what to know if it was changed from outside ?
Other uses cases is to prevent it and using encryption can be one way
https://stackoverflow.com/questions/5669905/sqlite-with-encryption-password-protection/5877130#5877130 -
It's not about preventing malicious changes. It's simply about "The user accidentally deletes the database file while the program runs and then it behaves not as expected". I just want to popup a warning "The file has been changed, possibly it's broken now, you better reload it" or "Write access has been removed, can only display from now, not change anymore".
-
@JonB said in Monitoring an SQLite database with QFileSystemWatcher does not work as expected:
I assumed it used the native facilities from the OS....
It can, but it depends on the OS. It will, for example, use
inotifywhere possible (and considered reliable), which doesn't require polling, but does poll in lots of other cases.See QFileSystemWatcherPrivate::createNativeEngine() for some of the engines that might be used.
Cheers.
@Paul-Colby
But that doesn't sound like @VRonin 'sQFileSystemWatcher works with polling. It has a timer internally that every second will check the paths you added and store the values. Then, after 1 second, it will check again and if they are different it will trigger the signal.
hence my comment.
-
It's not about preventing malicious changes. It's simply about "The user accidentally deletes the database file while the program runs and then it behaves not as expected". I just want to popup a warning "The file has been changed, possibly it's broken now, you better reload it" or "Write access has been removed, can only display from now, not change anymore".
@l3u_
Hi
ok. so its just to know.
What about using Exclusive file locking mode
http://www.sqlite.org/pragma.html#pragma_locking_mode
Your process will then own the file and user cannot delete it.
Or at least it used to work that way. Disclaimer. Not tested/used recently. -
@l3u_
Hi
ok. so its just to know.
What about using Exclusive file locking mode
http://www.sqlite.org/pragma.html#pragma_locking_mode
Your process will then own the file and user cannot delete it.
Or at least it used to work that way. Disclaimer. Not tested/used recently.@mrjj Hey, that sounds good :-) Executing the following statements (I suppose there's no
QSqlDatabasefunction for that likeQSqlDatabase::transaction()?!)PRAGMA locking_mode = EXCLUSIVE BEGIN EXCLUSIVE COMMITactually causes another connection to be read-only, if one wants to change something, one gets an error "database is busy". Nice! This prevents the database to be (accidentally) changed by another process (e. g. two instances of my program opening the same database).
The thing that's left is to know if the user move or deleted the database or changed permissions during runtime …
-
@mrjj Hey, that sounds good :-) Executing the following statements (I suppose there's no
QSqlDatabasefunction for that likeQSqlDatabase::transaction()?!)PRAGMA locking_mode = EXCLUSIVE BEGIN EXCLUSIVE COMMITactually causes another connection to be read-only, if one wants to change something, one gets an error "database is busy". Nice! This prevents the database to be (accidentally) changed by another process (e. g. two instances of my program opening the same database).
The thing that's left is to know if the user move or deleted the database or changed permissions during runtime …
-
@l3u_
Hi so even in EXCLUSIVE mode, the actual file is not locked on OS level?
Like you can rename or move it.
Hmm, i guess i recall incorrectly then. -
@JonB said in Monitoring an SQLite database with QFileSystemWatcher does not work as expected:
I assumed it used the native facilities from the OS....
It can, but it depends on the OS. It will, for example, use
inotifywhere possible (and considered reliable), which doesn't require polling, but does poll in lots of other cases.See QFileSystemWatcherPrivate::createNativeEngine() for some of the engines that might be used.
Cheers.
@Paul-Colby said in Monitoring an SQLite database with QFileSystemWatcher does not work as expected:
It can, but it depends on the OS. It will, for example, use inotify where possible
Just to be clearer, even if inotify is used the event will be processed only when control goes back to the event loop so my code above can be considered safer when using inotify as it reduces the delay (from up to 1sec to the time it takes your program to reach the event loop)
-
@mrjj I can do what I want on filesystem level. It's protected against changes by another SQLite connection though.
@l3u_
I don't really know what you expect to achieve realistically.It's not clear to me from http://www.sqlite.org/pragma.html#pragma_locking_mode just exactly what they mean by "exclusive". It may only mean that the locks are respected by other SQLite processes, not the OS, and your findings seem to indicate that. Or, it may be that behaviour varies by OS, e.g. locked by OS under Windows but not under Linux.
QFileSystemWatcheris not designed to play along with "I want to know which process made a change, and ignore if it's mine". I don't think you (or it) will have any access to that in information.The thing that's left is to know if the user move or deleted the database or changed permissions during runtime …
In any case, these may not act as you think they will act, e.g.:
-
In Linux certainly (can't remember about Windows), one process can delete a file. If another process has that file open at present, the file is marked for deletion, but not actually deleted till all processes with it open close their connections. (And I don't think your
QFileSystemWatcherwill notice till it actually gets deleted.) -
When permissions on a file are changed to prevent access, these new permissions have no effect on processes which presently have a handle open on the file, only on processes newly trying to open the file. SQLite presumably holds open handles on the database files all the time, so will carry on as before.
-
Even with OS locking of file content, that may well not have any effect on any attempt to delete/rename/change permissions on the file itself.
-
-
@l3u_
I don't really know what you expect to achieve realistically.It's not clear to me from http://www.sqlite.org/pragma.html#pragma_locking_mode just exactly what they mean by "exclusive". It may only mean that the locks are respected by other SQLite processes, not the OS, and your findings seem to indicate that. Or, it may be that behaviour varies by OS, e.g. locked by OS under Windows but not under Linux.
QFileSystemWatcheris not designed to play along with "I want to know which process made a change, and ignore if it's mine". I don't think you (or it) will have any access to that in information.The thing that's left is to know if the user move or deleted the database or changed permissions during runtime …
In any case, these may not act as you think they will act, e.g.:
-
In Linux certainly (can't remember about Windows), one process can delete a file. If another process has that file open at present, the file is marked for deletion, but not actually deleted till all processes with it open close their connections. (And I don't think your
QFileSystemWatcherwill notice till it actually gets deleted.) -
When permissions on a file are changed to prevent access, these new permissions have no effect on processes which presently have a handle open on the file, only on processes newly trying to open the file. SQLite presumably holds open handles on the database files all the time, so will carry on as before.
-
Even with OS locking of file content, that may well not have any effect on any attempt to delete/rename/change permissions on the file itself.
@JonB said in Monitoring an SQLite database with QFileSystemWatcher does not work as expected:
@l3u_
I don't really know what you expect to achieve realistically.The idea is quite simple. The database is not intended to be accessed by multiple processes. So if another process changes something while the program runs, it's possible that it breaks. By setting "locking_mode", I can prevent such changes, as simply the database can't be used by any other connection until it's closed. That's one thing I wanted (and achieved in an elegant way I think, as both my program does handle such a lock now, and e. g. a manual cli connection also refuses to work on the database as long as it's open).
The other thing is to inform the user if e. g. the file has been accidentally deleted while it's open. By now, the program doesn't know it, and on the next change, all data is gone without an error message, which is imo not a desirable behavior. Instead, an error should pop up saying something like "The database file has been deleted, so we reset our program as if we closed it now".
And this is probably possible by monitoring it via a
QFileSystemWatcher, isn't it? If I check if it's still there on each change, I know if it was deleted and can handle this. -
-
@JonB said in Monitoring an SQLite database with QFileSystemWatcher does not work as expected:
@l3u_
I don't really know what you expect to achieve realistically.The idea is quite simple. The database is not intended to be accessed by multiple processes. So if another process changes something while the program runs, it's possible that it breaks. By setting "locking_mode", I can prevent such changes, as simply the database can't be used by any other connection until it's closed. That's one thing I wanted (and achieved in an elegant way I think, as both my program does handle such a lock now, and e. g. a manual cli connection also refuses to work on the database as long as it's open).
The other thing is to inform the user if e. g. the file has been accidentally deleted while it's open. By now, the program doesn't know it, and on the next change, all data is gone without an error message, which is imo not a desirable behavior. Instead, an error should pop up saying something like "The database file has been deleted, so we reset our program as if we closed it now".
And this is probably possible by monitoring it via a
QFileSystemWatcher, isn't it? If I check if it's still there on each change, I know if it was deleted and can handle this. -
@l3u_
As I tried to show in examples, I do not believe you can reliably achieve what you would like to achieve, at least not cross-platform.@JonB Apart from the change protection (changes by accident by opening the same database with two program instances), I'm actually only interested in knowing if the file has been deleted during runtime … Should I simply let a
QFileSystemWatcherwatch it an check it's still there after each change? Or is this the wrong way? -
@JonB Apart from the change protection (changes by accident by opening the same database with two program instances), I'm actually only interested in knowing if the file has been deleted during runtime … Should I simply let a
QFileSystemWatcherwatch it an check it's still there after each change? Or is this the wrong way?@l3u_
I have said, I'm not convinced it will work in this circumstance, at least under Linux, and I still don't think you've said which OS you're under. But why don't you first test the situation: run up your database Qt app, then go separately try to delete the file and see whether you can? If you can, test to see ifQFileSystemWatcherdetects it correctly.I don't think you "let a
QFileSystemWatcherwatch it an check it's still there after each change". You set up aQFileSystemWatcher, then it will signal you on file delete. Rather than you do any checking at certain periods yourself. -
@l3u_
I have said, I'm not convinced it will work in this circumstance, at least under Linux, and I still don't think you've said which OS you're under. But why don't you first test the situation: run up your database Qt app, then go separately try to delete the file and see whether you can? If you can, test to see ifQFileSystemWatcherdetects it correctly.I don't think you "let a
QFileSystemWatcherwatch it an check it's still there after each change". You set up aQFileSystemWatcher, then it will signal you on file delete. Rather than you do any checking at certain periods yourself.@JonB I'm on Linux and also compile the program on Windows. And, at least on Linux, I can delete the file while it's open.
The
QFileSystemWatcherwon't only signal me on file delete, but on each change, won't it? So I will have to find out if the file has been deleted on each change, won't I? -
@JonB I'm on Linux and also compile the program on Windows. And, at least on Linux, I can delete the file while it's open.
The
QFileSystemWatcherwon't only signal me on file delete, but on each change, won't it? So I will have to find out if the file has been deleted on each change, won't I?I'm on Linux and also compile the program on Windows. And, at least on Linux, I can delete the file while it's open.
I expected that. But I'm saying: in that situation, have you actually tested whether your
QFileSystemWatchersignals you that's it's been deleted (while you still have the database open)? I'm not sure it will, you need to check.So I will have to find out if the file has been deleted on each change, won't I?
I had thought you made "on each change you make to the database". Now I think you mean "on each change signalled by
QFileSystemWatcher".void QFileSystemWatcher::fileChanged(const QString &path)¶
This signal is emitted when the file at the specified path is modified, renamed or removed from disk.Yes, that's all you get. So you would have to check whether the file still exists. Don't do that too often!
Worse, I presume this means you'll get signalled when you make changes to your own database file.
Like I said, you now need to try a bit of code to see when you do & do not get these file notification messages.
