DOMANDA Database SQLite con chiave primaria autoincrementale

[petrusic]

Come già detto in altre occasioni,per l'archiviazione utilizzo database SQLite.
Di tanto in tanto, allo scopo di velocizzare le mie modeste applicazioni in linguaggio Gambas o Java, quest'ultimo da circa un anno, cerco nuove soluzioni con l'attivazione di funzioni SQLite non impiegate prima.
Ieri, per es. ho cominciato a modificare una parte di programma, scritto in Gambas, in cui copio le singole tabelle del DB in altrettante tabelle provvisorie, riordinandone fisicamente i record all'interno di ciascuna. Durante la trascrizione risequenzio, a partire da 1, la chiave primaria della tabella.
Ho scoperto che potevo fare praticamente lo stesso lavoro eseguendo:

  sql1 = "CREATE TABLE causalifreq2 AS SELECT * from causalifreq WHERE DtUltCausFreq > '" & CalcData60GGfa.iData60GGfa & "' ORDER BY DtUltCausFreq"
  Try ApriDB.DBConnection.EXEC(sql1)

e in appena 00:00:04 secondi la tabella transitoria è copiata con tutti i record più recenti, secondo le mie necessità.
Poi ho eseguito

 Try ApriDB.DBConnection.EXEC("DROP TABLE causalifreq")     
  ApriDB.DBConnection.EXEC("ALTER TABLE causalifreq2 RENAME To causalifreq")

con cui ho cancellato la tabella di partenza e rinominato la tabella transitoria, in modo da restituirle definitivamente il nome originario.

Contentissimo, sono andato a controllare, prima di ripetere il lavoro anche sulle altre tabelle del DB, la struttura dati della tabella. L'ho trovata inalterata, tranne che per la chiave primaria:

`IdCauFreq` INTEGER PRIMARY KEY AUTOINCREMENT <--- prima delle istruzioni di trascrizione e rinominazione
`IdCauFreq` INT <--- a fine lavoro

Il campo "IdCauFreq" non è più una chiave primaria, inoltre i codici numerici, all'interno della rispettiva colonna non seguono più una numerazione progressiva.

N.Progr.Riga​	IdCauFreq​	DtUltCausFreq​	OraUltCausFreq​
1​	225​	20190820​	220101​
2​	226​	20190820​	220945​
3​	228​	20190821​	213453​
4​	229​	20190822​	213213​
5​	230​	20190822​	213256​
6​	105​	20190824​	212720​
7​	231​	20190824​	212445​
...​	...​	...​	...​
​	​	​	​
13​	237​	20190826​	214551​
14​	173​	20190827​	213938​
15​	36​	20190828​	213152​
16​	89​	20190829​	213959​
17​	238​	20190830​	213503​
18​	239​	20190830​	213743​
19​	122​	20190831​	213411​
20​	240​	20190831​	215116​

Vorrei capire da che cosa può dipendere tutto ciò e come potrei restituire la tipologia di primary key ed ottenere la numerazione progressiva numerica crescente per la colonna "IdCauFreq".

 

[petrusic]

Ho trovato una risposta sul comportamento dell'istruzione

sql1 = "CREATE TABLE causalifreq2 AS SELECT * from causalifreq WHERE DtUltCausFreq > '" & CalcData60GGfa.iData60GGfa & "' ORDER BY DtUltCausFreq"
  Try ApriDB.DBConnection.EXEC(sql1)

qui. Quindi, così facendo perdo la primary key nella nuova tabella.
Purtroppo il programma si interrompe perchè, non so come, viene riscontrata come anomala l'assenza della chiave primaria, durante l'esecuzione di una query

Try RecTab = ApriDB.DBConnection.edit($_NomiTbDb[i])
    If ( Error ) Then
        Message.ERROR("Attenzione!  ERRORE --> '" & ERROR.Text & "'" & Chr(10) & "alla riga " & Error.Where & Chr(10) & Chr(10) & "Nome tabella '" & $_NomiTbDb[i] & "' errato o inesistente" & Chr(10) & Chr(10) & Space$(30) & " IL PROGRAMMA VERRÀ CHIUSO")
        Quit
    Endif

col seguente messaggio d'Errore

'Table 'causalifreq' has no primary key

e la query NON viene nemmeno esguita.
Per risovere dovrei restituire la "PRIMARY KEY" al campo di tabella.
Si può fare fuori dall'istruzione CREATE?

 

[Andretti60]

No, non puoi. Comunque a me non piace creare una table semplicemente con un SELECT come fai tu, è veloce ma come vedi perdi molto.
Meglio crearla esplicitamente con le key che vuoi, poi copiarvi il contenuto dell’altra, molto più sicuro e flessibile.

 

[petrusic]

No, non puoi. Comunque a me non piace creare una table semplicemente con un SELECT come fai tu, è veloce ma come vedi perdi molto.
Meglio crearla esplicitamente con le key che vuoi, poi copiarvi il contenuto dell’altra, molto più sicuro e flessibile.

Si, è vero. Io faccio già così. Ho provato a cambiare metodo per rendere più veloce il processo di copia, però dopo l'esperienza vissuta ho abbandonato la modifica, lasciando funzionare il programma alla vecchia maniera.
Come faccio attualmente, per copiare tutte le tabelle del DB (12) con circa 120000 record in totale, impego circa 1h:30m.
Durante la copia risequenzio i record rinumerando il campo "id" di ciascuna tabella. Non lo faccio spesso, ma 1h:30m.è pur sempre un tempo lunghissimo.
Non credi?

 

[Andretti60]

Si, è vero. Io faccio già così. Ho provato a cambiare metodo per rendere più veloce il processo di copia, però dopo l'esperienza vissuta ho abbandonato la modifica, lasciando funzionare il programma alla vecchia maniera.
Come faccio attualmente, per copiare tutte le tabelle del DB (12) con circa 120000 record in totale, impego circa 1h:30m.
Durante la copia risequenzio i record rinumerando il campo "id" di ciascuna tabella. Non lo faccio spesso, ma 1h:30m.è pur sempre un tempo lunghissimo.
Non credi?

E' un tempo inaccettabile, in questo momento sto ottimizzando un database scritto in SQLite, per un milione di record impiego un minuto per crearlo (incluso il tempo per leggere i dati) facendo il tutto in una transazione, e un paio di secondi per poi scansionarlo.
Che database usi?

 

[petrusic]

Che database usi?

Non uso un gestore di database come "DB Broser for SQLite", se è questo che vuoi dire.
Utilizzo un gruppo di istruzioni di un mio programma scritto in Gambas. Probabilmente è proprio il sistema di lettura e registrazione messo in atto nel mio programma che mi fa perdere tutto quel tempo.

 

[Andretti60]

Scusa, ho dimenticato che anche tu usi SQLite.
Non conosco Gambas, ma so che non eccelle come prestazioni.
Comunque quando aggiungi MOLTI record in una tabella SQLite, DEVI assolutamente farlo all'interno di una transazione, dove le modifiche sono mantenute in memoria e salvate solo alla fine, altrimenti il file del database viene riscritto dopo OGNI aggiunta.
Nel tuo caso scrivi 20 record per secondo, che e' ridicolo, io ne scrivo quasi 300.

 

[petrusic]

Non conosco Gambas, ma so che non eccelle come prestazioni.

Si, hai ragione. Essa è una delle ragioni per cui ho deciso di conoscere un altro linguaggio.
E' anche vero che mi è stato molto utile quando ho cominciato a conoscere Linux, perchè, essendo assai simile al Visual Basic di M., mi ha permesso di scrivere un programma intanto che mi concentravo sul nuovo S.O.
In Gambas e' comodo anche il potere utilizzare immediatamente la GUI, cosa che ancora in Java non sono riuscito a fare.

Tornando al mio problema

Comunque quando aggiungi MOLTI record in una tabella SQLite, DEVI assolutamente farlo all'interno di una transazione, dove le modifiche sono mantenute in memoria e salvate solo alla fine, altrimenti il file del database viene riscritto dopo OGNI aggiunta.
Nel tuo caso scrivi 20 record per secondo, che e' ridicolo, io ne scrivo quasi 300.

nel passo di programma riguardante la copiatura con risequenziazione di tutti i record di ciascuna tabella del DB, io utilizzo sempre istruzioni SQL, come dall'esempio che riporto qui sotto:

ApriDB = New OpenDB       'classe dedicata all'aoertura del DB
If ApriDB.DBConnection.Tables.Exist("piancont") Then      ' Verifica formale dell'esistenza della Tabella "PIanCont" - la verifica è stata già accertata subito prima
    If ApriDB.DBConnection.Tables.Exist("piancont2") Then      ' Verifica l'esistenza della Tabella transitoria "PIanCont2"
      Try ApriDB.DBConnection.EXEC("DROP TABLE piancont2")       'Predispone le condizioni per avviare il RIORDINO della tabella "piancont"
      If ( Error ) Then
        Message.ERROR("Attenzione! ->  " & ERROR.Text & Chr(10) & Chr(10) & "durante la cancellazione della tabella 'piancont2' prima del riordino di 'piancont'" & Chr(10) & Chr(10) & " il programma verrà chiuso")
        Quit
      Endif
    Endif
Endif
ApriDB.DBConnection.Close     ' come si intuisce, richiamo la routine di chiusura del DB '
CreTabDb = New CreaTabDb($TbRiord2)   'nome della classe dinamica di creazione delle tabelle del DB
ApriDB = New OpenDB
RecTab = ApriDB.DBConnection.Edit("piancont")
iConta = RecTab.Count           ' questa è l'unica istruzione di  accesso al DB non  SQL
Try RecTab = ApriDB.DBConnection.Exec("SELECT * FROM piancont ORDER BY NumVoce")
If RecTab.Available = False Then
    Message.ERROR("Attenzione! ->  " & ERROR.Text & Chr(10) & Chr(10) & "durante la lettura della Tabella '" & ApriDB.$DbNome & "!piancont'" & Chr(10) & Chr(10) & " il programma verrà chiuso")
    Quit
Endif
iId = 0
For Each RecTab
    iId += 1
    sql = "INSERT INTO piancont2 VALUES("
    sql &= iId & ", "
    sql &= $StriMia & "', '"
    sql &= RecTab!ContrPartSiNo & "', "
    sql &= RecTab!DtPrimoUso & ", "
    sql &= RecTab!DtUltimUso & ")"
    Try RecTab2 = ApriDB.DBConnection.EXEC(sql)
    If ( Error ) Then
        Message.ERROR("Attenzione!  ERRORE --> '" & ERROR.Text & "'" & Chr(10) & "alla riga " & Error.Where & Chr(10) & Chr(10) & "durante la registrazione della '" & ApriDB.$DbNome & "!piancont2'" & Chr(10) & Chr(10) & Space$(30) & " IL PROGRAMMA VERRÀ CHIUSO")
        Quit
    Endif
    MsgInfUten("Piano dei Conti", "1", Str(iId), Str(iConta), dDatOrAvvio)  'emetto nessaggio informativo
Next
Wait 0.30                       ' viene eseguita una sola volta, alla fine dellla copiatura
ApriDB.DBConnection.Commit        'Eseguo il Commit solo alla fine del processo di scrittutra della tabella provvisoria'
ApriDB.DBConnection.Close        ' e finalmente chiudo il DB'

Dove può essere il problema?
- l'emissione del messaggio informativo? Anche se la routine è richiamata ad ogni record scritto, il messaggio viene emesso con cadenza di 50 record
- l'esecuzione del Commit solo a fine scrittura?

Probabilmente, anche per quello che mi dici tu, può essere la somma di entrambi le componenti.

 

[Andretti60]

Credo (ripeto, non conosco Gambas quindi neanche la libreria che usa per interfacciarsi a SQLite) che il problema stia che usi ApriDB.DBConnection.Commit senza mai usare ApriDB.DBConnection.Begin PRIMA del ciclo dove aggiungi i record nella tabella.
Ossia non inizi mai esplicitamente una transazione, per cui ogni EXEC viene eseguita in una transazione separata. Il che va bene per singole operazioni, non in un ciclo come il tuo.

Comunque non voglio sembrare critico, non c'e' nulla di male nel usare Gambas, specie quando si studia (quindi va benissimo anche SQLite invece che un full-fledged database). Io stesso in ufficio ho iniziato a usare SQLite per sperimentare prototipi di applicazioni, di fatto funziona cosi' bene che abbiamo deciso di metterlo in produzione (e ha il grosso vantaggio che e' gratuito anche a livello commerciale)

 

[petrusic]

Credo (ripeto, non conosco Gambas quindi neanche la libreria che usa per interfacciarsi a SQLite) che il problema stia che usi ApriDB.DBConnection.Commit senza mai usare ApriDB.DBConnection.Begin PRIMA del ciclo dove aggiungi i record nella tabella.
Ossia non inizi mai esplicitamente una transazione, per cui ogni EXEC viene eseguita in una transazione separata. Il che va bene per singole operazioni, non in un ciclo come il tuo.

:ops:
E' da diverso tempo che, nella scrittura del mio codice, non uso più
ApriDB.DBConnection.Begin
Oserei dire che mi sembra una duplicazione del comando
ApriDB.DBConnection.Open. Così un bel giorno ho deciso di non usarla più. Ora che tu mi hai acceso la lampadina, mi studierò meglio il suo funzionamento, perchè mi servirà anche in Java.
Grazieeee!

Ti terrò informato.