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. No relation with Qt but need help to update few rows in SQLITE when delete one row
Forum Updated to NodeBB v4.3 + New Features

No relation with Qt but need help to update few rows in SQLITE when delete one row

Scheduled Pinned Locked Moved Unsolved General and Desktop
sqlite
6 Posts 4 Posters 1.8k Views 1 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.
  • F Offline
    F Offline
    filipdns
    wrote on last edited by A Former User
    #1

    Hello, I know that is not really QT question but here many people have good knowledge, then I ask here.

    I'm using qt with locale storage and I would like to have my column total-time updated if I delete one row by the time_use column.
    I explain:
    Column:
    Date , time_use, total_time
    when I add row, the total_time is previous total_time + new time_use
    Then when I delete one row
    I want to have total_time of each upper rows - time_use deleted.
    thank you for your help

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

      if db.driver()->hasFeature(QSqlDriver::EventNotifications) returns true (as it should) then you can create a trigger in the database so then you can use QSqlDriver::subscribeToNotification and the QSqlDriver::notification signal to recalculate the total column

      "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
      • VRoninV VRonin

        if db.driver()->hasFeature(QSqlDriver::EventNotifications) returns true (as it should) then you can create a trigger in the database so then you can use QSqlDriver::subscribeToNotification and the QSqlDriver::notification signal to recalculate the total column

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

        @VRonin
        I know nothing about SQLite/"local storage", but by the time one is writing a trigger can't that do the recalculation and update of other rows without needing to to pass "notifications" to Qt client code to do that work?

        BuckwheatB 1 Reply Last reply
        1
        • F Offline
          F Offline
          filipdns
          wrote on last edited by
          #4

          thanks VRonin,

          I don't understand anything about your suggestion but thank you very much for your time for me.

          I'm using qml + js to work with SQLite, not cpp.

          kind regards
          Philippe

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

            As suggested by @JonB you don't need to do anything in C++, QML or js. Just open your database using a program like http://sqlitebrowser.org/ and execute the SQL query:
            (mytable is the name of your table, blablabla is a select that calculates the cumulative sum and depends on the structure of mytable. Just google "sqlite cumulative sum")

            CREATE TRIGGER IF NOT EXISTS updateTime AFTER DELETE ON  mytable 
            BEGIN
            UPDATE mytable SET total_time= blablabla
            END;
            

            "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
            4
            • JonBJ JonB

              @VRonin
              I know nothing about SQLite/"local storage", but by the time one is writing a trigger can't that do the recalculation and update of other rows without needing to to pass "notifications" to Qt client code to do that work?

              BuckwheatB Offline
              BuckwheatB Offline
              Buckwheat
              wrote on last edited by
              #6

              @JonB

              Very good observation. Triggers seem to be under utilized in SQLite. This would actually be a preferred way to update summary calculations. Imagine if you were doing this across a server. The back-and-forth traffic would be so inefficient. I have used triggers for years in SQLite without incident. I even use them for housekeeping modifications to tables.

              Views are another great tool to coalesce data from relation tables for display.

              Dave Fileccia

              1 Reply Last reply
              1

              • Login

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