Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. Monitoring an SQLite database with QFileSystemWatcher does not work as expected
Forum Updated to NodeBB v4.3 + New Features

Monitoring an SQLite database with QFileSystemWatcher does not work as expected

Scheduled Pinned Locked Moved Solved General and Desktop
28 Posts 5 Posters 4.7k Views 2 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • l3u_L Offline
    l3u_L Offline
    l3u_
    wrote on last edited by
    #1

    Hi :-)

    I'm working on a program using an SQLite database. I'd like to add an error handler for the case that the database is manupulated (renamed, deleted, changed) outside of my program while it's running.

    I thought I could simply add a QFileSystemWatcher to monitor the database file and block it's "changed" signal each time my program modifies the database, so that it would only be emitted if the file was changed externally – but this does not seem to work as expected.

    Here are some code fragments explaining what's going on:

    The QFileSystemWatcher is setup like this:

    Database::Database(QObject *parent) : QObject(parent)
    {
        ...
        m_dbFileMonitor = new QFileSystemWatcher(this);
        connect(m_dbFileMonitor, &QFileSystemWatcher::fileChanged,
                this, &Database::dbFileChangedExternally);
    }
    
    void Database::connectDatabase(const QString &dbFile)
    {
        ...
        m_dbFileMonitor->addPath(dbFile);
    }
    
    void Database::dbFileChangedExternally(const QString&)
    {
        qDebug() << "file changed";
    }
    

    Here's some function changing the database:

    void Database::registerPlayers(const QString &name)
    {
        qDebug() << "blocking signals";
        m_dbFileMonitor->blockSignals(true);
        m_db.transaction();
        QSqlQuery query(m_db);
        query.prepare(QString::fromUtf8("INSERT INTO players(id, name) VALUES(NULL, ?)"));
        query.bindValue(0, name);
        query.exec();
        m_db.commit();
        qDebug() << "unblocking signals";
        m_dbFileMonitor->blockSignals(false);
    }
    

    When the function is called, the console output is:

    blocking signals
    unblocking signals
    file changed
    

    So apparently, the QFileSystemWatcher's signal is emitted after QSqlDatabase::commit() has been already called and the signals aren't blocked anymore.

    So am I thinking wrong here, or does the actual write operation on the database take place in some asynchronous way? Like I block the file watcher, manipulate the database, unblock it – but the actual writing takes place some short time after, so that it still reports the file to be changed?

    How can I solve this? Is there a way to know then the writing will be finished?

    Or should I set some bool variable like m_writeInProgress to true when I do something and let the file watcher's changed slot reset it? This way, a change operation would be requested and confirmed by the file watcher – and if something un-requested happens, I know that it wasn't my program?

    Or is this simply a complete wrong approach?

    Thanks for all help!

    1 Reply Last reply
    0
    • VRoninV Offline
      VRoninV Offline
      VRonin
      wrote on last edited by VRonin
      #2

      nope, this is just how QFileSystemWatcher works.

      QFileSystemWatcher 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.
      It's not a chain of signals that gets triggered by m_db.commit(); and so it will not halt Database::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 of Database
      • 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

      "La mort n'est rien, mais vivre vaincu et sans gloire, c'est mourir tous les jours"
      ~Napoleon Bonaparte

      On a crusade to banish setIndexWidget() from the holy land of Qt

      JonBJ 1 Reply Last reply
      2
      • l3u_L Offline
        l3u_L Offline
        l3u_
        wrote on last edited by
        #3

        Thanks for the clarification! So The approach using QFileSystemWatcher is possibly the wrong way at all?!

        1 Reply Last reply
        0
        • VRoninV VRonin

          nope, this is just how QFileSystemWatcher works.

          QFileSystemWatcher 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.
          It's not a chain of signals that gets triggered by m_db.commit(); and so it will not halt Database::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 of Database
          • 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

          JonBJ Offline
          JonBJ Offline
          JonB
          wrote on last edited by
          #4

          @VRonin

          QFileSystemWatcher works with polling.

          I assumed it used the native facilities from the OS....

          Paul ColbyP 1 Reply Last reply
          0
          • JonBJ JonB

            @VRonin

            QFileSystemWatcher works with polling.

            I assumed it used the native facilities from the OS....

            Paul ColbyP Offline
            Paul ColbyP Offline
            Paul Colby
            wrote on last edited by
            #5

            @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 inotify where 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.

            JonBJ VRoninV 2 Replies Last reply
            3
            • mrjjM Offline
              mrjjM Offline
              mrjj
              Lifetime Qt Champion
              wrote on last edited by
              #6

              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

              1 Reply Last reply
              0
              • l3u_L Offline
                l3u_L Offline
                l3u_
                wrote on last edited by
                #7

                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".

                mrjjM 1 Reply Last reply
                0
                • Paul ColbyP Paul Colby

                  @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 inotify where 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.

                  JonBJ Offline
                  JonBJ Offline
                  JonB
                  wrote on last edited by
                  #8

                  @Paul-Colby
                  But that doesn't sound like @VRonin 's

                  QFileSystemWatcher 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.

                  1 Reply Last reply
                  0
                  • l3u_L l3u_

                    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".

                    mrjjM Offline
                    mrjjM Offline
                    mrjj
                    Lifetime Qt Champion
                    wrote on last edited by
                    #9

                    @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_L 1 Reply Last reply
                    0
                    • mrjjM mrjj

                      @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_L Offline
                      l3u_L Offline
                      l3u_
                      wrote on last edited by
                      #10

                      @mrjj Hey, that sounds good :-) Executing the following statements (I suppose there's no QSqlDatabase function for that like QSqlDatabase::transaction()?!)

                      PRAGMA locking_mode = EXCLUSIVE
                      BEGIN EXCLUSIVE
                      COMMIT
                      

                      actually 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 …

                      mrjjM 1 Reply Last reply
                      0
                      • l3u_L l3u_

                        @mrjj Hey, that sounds good :-) Executing the following statements (I suppose there's no QSqlDatabase function for that like QSqlDatabase::transaction()?!)

                        PRAGMA locking_mode = EXCLUSIVE
                        BEGIN EXCLUSIVE
                        COMMIT
                        

                        actually 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 …

                        mrjjM Offline
                        mrjjM Offline
                        mrjj
                        Lifetime Qt Champion
                        wrote on last edited by
                        #11

                        @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.

                        l3u_L 1 Reply Last reply
                        0
                        • mrjjM mrjj

                          @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.

                          l3u_L Offline
                          l3u_L Offline
                          l3u_
                          wrote on last edited by
                          #12

                          @mrjj I can do what I want on filesystem level. It's protected against changes by another SQLite connection though.

                          JonBJ 1 Reply Last reply
                          0
                          • Paul ColbyP Paul Colby

                            @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 inotify where 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.

                            VRoninV Offline
                            VRoninV Offline
                            VRonin
                            wrote on last edited by
                            #13

                            @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)

                            "La mort n'est rien, mais vivre vaincu et sans gloire, c'est mourir tous les jours"
                            ~Napoleon Bonaparte

                            On a crusade to banish setIndexWidget() from the holy land of Qt

                            1 Reply Last reply
                            0
                            • l3u_L l3u_

                              @mrjj I can do what I want on filesystem level. It's protected against changes by another SQLite connection though.

                              JonBJ Offline
                              JonBJ Offline
                              JonB
                              wrote on last edited by
                              #14

                              @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.

                              QFileSystemWatcher is 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 QFileSystemWatcher will 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_L 1 Reply Last reply
                              0
                              • JonBJ JonB

                                @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.

                                QFileSystemWatcher is 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 QFileSystemWatcher will 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_L Offline
                                l3u_L Offline
                                l3u_
                                wrote on last edited by
                                #15

                                @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.

                                JonBJ 1 Reply Last reply
                                0
                                • VRoninV Offline
                                  VRoninV Offline
                                  VRonin
                                  wrote on last edited by
                                  #16

                                  Just throwing an idea out there: Qt Creator does this for source files so you should be able to check the source of Qt Creator and find the perfect solution to your problem

                                  "La mort n'est rien, mais vivre vaincu et sans gloire, c'est mourir tous les jours"
                                  ~Napoleon Bonaparte

                                  On a crusade to banish setIndexWidget() from the holy land of Qt

                                  1 Reply Last reply
                                  3
                                  • l3u_L l3u_

                                    @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.

                                    JonBJ Offline
                                    JonBJ Offline
                                    JonB
                                    wrote on last edited by
                                    #17

                                    @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.

                                    l3u_L 1 Reply Last reply
                                    0
                                    • l3u_L Offline
                                      l3u_L Offline
                                      l3u_
                                      wrote on last edited by
                                      #18

                                      As far as I can see, they also use a QFileSystemWatcher to handle this kind of changes.

                                      1 Reply Last reply
                                      0
                                      • JonBJ JonB

                                        @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.

                                        l3u_L Offline
                                        l3u_L Offline
                                        l3u_
                                        wrote on last edited by l3u_
                                        #19

                                        @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 QFileSystemWatcher watch it an check it's still there after each change? Or is this the wrong way?

                                        JonBJ 1 Reply Last reply
                                        0
                                        • l3u_L l3u_

                                          @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 QFileSystemWatcher watch it an check it's still there after each change? Or is this the wrong way?

                                          JonBJ Offline
                                          JonBJ Offline
                                          JonB
                                          wrote on last edited by
                                          #20

                                          @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 if QFileSystemWatcher detects it correctly.

                                          I don't think you "let a QFileSystemWatcher watch it an check it's still there after each change". You set up a QFileSystemWatcher, then it will signal you on file delete. Rather than you do any checking at certain periods yourself.

                                          l3u_L 1 Reply Last reply
                                          0

                                          • Login

                                          • Login or register to search.
                                          • First post
                                            Last post
                                          0
                                          • Categories
                                          • Recent
                                          • Tags
                                          • Popular
                                          • Users
                                          • Groups
                                          • Search
                                          • Get Qt Extensions
                                          • Unsolved