Rowid cleaned after delete or insert SQLITE

filipdns

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

JonB

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

6thC

@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

filipdns

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

filipdns

@6thC Thank you I will go to see