Trip Log – Manutenzione Database

Controllando il db usato per queste interrogazioni, ho voluto approfondire diverse anomalie riscontrate che, pur non essendo in grado di alterare la sostanza dei risultati statistici ottenuti, riguardando meno del 3% dei campioni (circa 50,000 su 1,700,000), potevano però introdurre anomalie avvertibili in certi tipi di analisi, specialmente ove fosse necessario confrontare i valori di campioni consecutivi.
Uno dei pricipali problemi riscontrati riguardava infatti la compromissione dei numeri di riga ROWID della tabella FASTLOG dei campionamenti, con alcune migliaia di record che non rispettavano l’attesa sequenza incrementale. In appendice le istruzioni SQL per ripristinarla.

I conteggi iniziali (imperfetti) davano queste dimensioni del db.

Fig.1

Questa la situazione dopo il ripristino.

Fig.2

Ora tutti i record di FASTLOG sono accessibili via TRIPS, come nella query di Fig.2 nella quale si sono esclusi solo i 105 campioni iniziali di ogni viaggio (non avendo questi un campione precedente).

Fig.3

Una query simile, sul database iniziale, avrebbe riportato oltre 1,700,000 campioni, per via delle varie anomalie.

Appendice: ricostruzione dei ROWID della tabella FASTLOG

Le normali tabelle di SQLite hanno una chiave primaria alternativa rispetto a quella definita (in FASTLOG il campo TIMESTAMP), data della pseudo-colonna ROWID , un valore progressivo che rappresenta la sequenza di inserimento delle righe. In realtà anche la allocazione fisica del record è pilotata dal ROWID, che viene così ad essere la via più rapida per accedere alla tabella.
Nel nostro caso questa proprietà è particolarmente importante perché, dato il meccanismo di campionamento, la sequenza dei ROWID è uguale a quella dei TIMESTAMP. Così, per accedere molto rapidamente allo N-mo campione precedente all’attuale, basta cercare quello con numero di riga ROWID – N
Se per qualche motivo questa sequenza venisse ad alterarsi, è possibile forzare la ricostruzione della tabella. A titolo di esempio, si potrebbero seguire i seguenti passi:

1. Eseguire una copia di backup del file con il db.

2. Aprire il db e, tramite una finestra SQL, creare una tabella temporanea copia di FASTLOG. Es:
Create table Fastlog_bak as Select Fastlog.* from Fastlog order by Fastlog.timestamp;

3. Cancellare tutte le righe di FASTLOG. Es:
Delete from fastlog;

4. Salvare le modifiche al database (es: “File“-“Write Changes”) ed eseguire la Compattazione (Es: “Tools”-“Compact Database”). Si può fare il tutto anche con un comando SQL: in questo caso rispondere affermativamente alla richiesta da parte del sistema di effettuare il Commit della transazione in corso. Es:
Vacuum;

4. Reinserire le righe della tabella temporanea in FASTLOG, assicurandosi che siano ordinate per il TIMESTAMP. Es:
Insert into Fastlog Select Fastlog_bak.* from Fastlog_bak order by Fastlog_bak.timestamp;

5. Eliminare la tabella temporanea. Es:
Drop table Fastlog_bak;

6. Rieseguire la compattazione. Es:
Vacuum;

7. Per verificare che la riassegnazione dei numeri di riga Rowid secondo la sequenza dei Timestamp abbia avuto successo, si può lanciare la sequente query verificando che non ritorni alcun record. NOTA: la Query richiede una versione di SQLite >= 3.25. Es:
SELECT Q.*
FROM
(SELECT
f.timestamp,
f.rowid as rowid_cur,
lag(f.rowid) over (order by f.timestamp) as rowid_prev
FROM Fastlog as f
) as Q
WHERE Q.rowid_cur<>Q.rowid_prev+1;


Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo di WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione /  Modifica )

Google photo

Stai commentando usando il tuo account Google. Chiudi sessione /  Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione /  Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione /  Modifica )

Connessione a %s...