Rowid cleaned after delete or insert SQLITE



  • Hello,

    I'm using example of local storage from QT and I would like to know how can I have Id cleaned aftern delete or insert.

    Rowid is auto increment but when I delete one row, I have missing Row id.

    Exemple if I insert 4 rows, Id go from 1 to 4 with 1,2,3,4.

    If I delete the third, I have rows: 1,2,4 but I want to refresh rowid to got 1,2,3, how do than?

    I found in SQLite the vacuum query but I don't have any idea how to use it, not example are corresponding to qt example javascript...

    here some query from my project with qt example JS style:

    function dbInitflightlog()
    {
        var db = LocalStorage.openDatabaseSync("Kardex", "1.0", "Kardex", 1000000)
        try {
            db.transaction(function (tx) {
                tx.executeSql('CREATE TABLE IF NOT EXISTS flight_log (immatriculation TEXT, date_etape TEXT, pilot1 TEXT, pilot2 TEXT, pilot3 TEXT, photo1 TEXT, photo2 TEXT, photo3 TEXT, aeroport_depart TEXT, aeroport_arrivee TEXT, heure_depart TEXT,heure_arrivee TEXT, temps_bloc_etape TEXT, cycle_etape INT, aircrafttype TEXT, numerodeserie TEXT,)')
            })
        } catch (err) {
            console.log("Error creating table in database: " + err)
        };
    }
    
    function dbGetHandle()
    {
        try {
            var db = LocalStorage.openDatabaseSync("Kardex", "1.0", "Kardex", 1000000)
      
    
    
        } catch (err) {
            console.log("Error opening database: " + err)
        }
        return db
    }
    
    function dbUpdateflightlog( Pimmatriculation, Pdate_etape, Ppilot1, Ppilot2, Ppilot3, Pphoto1, Pphoto2, Pphoto3, Paeroport_depart, Paeroport_arrivee, Pheure_depart, Pheure_arrivee, Ptemps_bloc_etape, Pcycle_etape, Paircrafttype, Pnumerodeserie, Pdateconstruction, Pnumerocertificatimmat, PnumerocertificatLSA, PdateLSA, PnumeroCDN, PdateCDN, Pdatecertificatassurance, Pdatepesee, Pmarquemoteur, Pmodelmoteur, Pmarquehelice, Pmodelhelice, Pserialmoteur1, Pserialmoteur2, Pserialhelice1, Pserialhelice2, Pheurecellule, Pcyclecellule, Pheure_SN_moteur_1, Pheure_SO_moteur_1, Pheure_SH_moteur_1, Pcycle_SN_moteur_1, Pcycle_SO_moteur_1, Pcycle_SH_moteur_1, Pheure_SN_moteur_2, Pheure_SO_moteur_2, Pheure_SH_moteur_2, Pcycle_SN_moteur_2, Pcycle_SO_moteur_2, Pcycle_SH_moteur_2, Pheure_SO_helice_1, Pcycle_SO_helice_1, Pheure_SO_helice_2, Pcycle_SO_helice_2, Prowid)
    {
        var db = dbGetHandle()
        db.transaction(function (tx) {
            tx.executeSql(
                        'update flight_log set immatriculation=?, date_etape=?, pilot1=?,  pilot2=?,  pilot3=?,  photo1=?,  photo2=?,  photo3=?,  aeroport_depart=?,  aeroport_arrivee=?,  heure_depart=?, heure_arrivee=?,  temps_bloc_etape=?,  cycle_etape=?, aircrafttype=? ,numerodeserie=? ,dateconstruction=? ,numerocertificatimmat=? ,numerocertificatLSA=? ,dateLSA=? ,numeroCDN=? ,dateCDN=? ,datecertificatassurance=? ,datepesee=? ,marquemoteur=? ,modelmoteur=? ,marquehelice=? ,modelhelice=? ,serialmoteur1=? ,serialmoteur2=? ,serialhelice1=? ,serialhelice2=? ,heurecellule=? ,cyclecellule=? ,heure_SN_moteur_1=? ,heure_SO_moteur_1=? ,heure_SH_moteur_1=? ,cycle_SN_moteur_1=? ,cycle_SO_moteur_1=? ,cycle_SH_moteur_1=? ,heure_SN_moteur_2=? ,heure_SO_moteur_2=? ,heure_SH_moteur_2=? ,cycle_SN_moteur_2=? ,cycle_SO_moteur_2=? ,cycle_SH_moteur_2=? ,heure_SO_helice_1=? ,cycle_SO_helice_1=? ,heure_SO_helice_2=? ,cycle_SO_helice_2=? where rowid = ? ', [Pimmatriculation, Pdate_etape, Ppilot1, Ppilot2, Ppilot3, Pphoto1, Pphoto2, Pphoto3, Paeroport_depart, Paeroport_arrivee, Pheure_depart, Pheure_arrivee, Ptemps_bloc_etape, Pcycle_etape,Paircrafttype, Pnumerodeserie, Pdateconstruction, Pnumerocertificatimmat, PnumerocertificatLSA, PdateLSA, PnumeroCDN, PdateCDN, Pdatecertificatassurance, Pdatepesee, Pmarquemoteur, Pmodelmoteur, Pmarquehelice, Pmodelhelice, Pserialmoteur1, Pserialmoteur2, Pserialhelice1, Pserialhelice2, Pheurecellule,  Prowid])
    
        })
    }
    

    Thank you for your help



  • @filipdns
    I cannot swear that you cannot do this in SQLite. But you should never be doing so. An auto-incrementing id is meant to just increment. Yes, if you delete a row you get a "gap". That's how it is. If you care that there must never be a gap, you should not be deleting, or you should not be using an auto-incrementing id.



  • @filipdns well said. Let the engine / management system do what it does best. Smarter people and processes have data engines very tight.

    Perhaps this could help (if are trying to do what I think you want):
    https://stackoverflow.com/questions/3126972/with-mysql-how-can-i-generate-a-column-containing-the-record-index-in-a-table



  • @JonB Hello, I used for test vacuum in DB sqlite browser and it's doing what I need, then I need to know what is the command in JS et how to call it in QML...



  • @6thC Thank you I will go to see


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.