[Gfoss] problemi con indici spaziali sqlite

[a.furieri a lqt.it]

On Thu, 20 Feb 2014 21:44:33 +0100, Luca Lanteri wrote:
> Io creo la colonna della geometria con RecoverGeometryColumn(), poi
> popolo semplicemente i dati mediante un update della colonna
> geometrica, prendendo la geometria da un'atra tabella invece che
> dall'interfaccia di qgis. I trigger che popolano gli indici 
> dovrebbero
> essere scatenati tanto dal mio update quanto da un inserimento 
> diretto
> in QGIS, no ?
>

ok, almeno in teoria torna perfettamente:
- RecoverGeometryColumn() dovrebbe installare tutti i triggers
- dopo di che ogni volta che fai un'INSERT/UPDATE/DELETE l'R*Tree
   verra' immediatamente aggiornato perche' ci pensano comunque i
   triggers in modo automatico.

natualmente, tra la teoria e la pratica c'e' sempre spazio a 
sufficienza
perche' ci si infili in mezzo lo zoccolo bifido di Messer Satanasso :-)

> Spero che cosi sia, visto che sto utilizzando Sqlite proprio per 
> poter
> utilizzare le funzionalità dei trigger.
>

questo fa nascere un dubbio ulteriore: i Triggers di SQLite sono basati
su una buona implementazione, ma ci sono alcuni caveats da tener di 
conto:
- alcune versioni molto vecchie (< 3.7.x) non dovrebbero essere in 
grado
   di supportare Triggers ricursivi.
   cioe', se un triggers fa partire un secondo trigger che ne scatena un
   terzo etc, con le vecchie versioni dovrebbe partire solo il primo,
   perche' poi la catena si spezza
- ma anche con le versioni recenti, c'e' comunque un limite nei livelli
   di nidificazione supportati dai triggers.
   e comunque occorre invocare esplicitamente una PRAGMA per abilitare 
la
   ricursivita' per i Triggers (by default e' comunque disabled).
   vedi: http://www.sqlite.org/pragma.html#pragma_recursive_triggers


> A questo punto, qual'è lo strumento migliore per creare un DB
> Spatialite DOC, come lo chiamava Andrea ?
>

naturalmente il mio non e' un parere spassionato: ma non ho comunque
il minimo dubbio. spatialite CLI oppure SpatiaLite GUI

cioe' i due strumenti piu' strettamente integrati con il DBMS, 
direttamente
gestiti dal medesimo gruppo di sviluppatori che segue il DBMS, e che 
sono
quindi sempre certamente allineati con le specifiche tecniche del DBMS 
via
via che vanno evolvendosi di versione in versione.
e che piu' facilmente tendono a recepire una vasta casistica di casi 
d'uso
che spaziono dalle micro-app per Android/smartphones fino alle piu'
complesse applicazioni ultra-professionali in contesti enterprise.



> Io li ho sempre creati da QGIS, che tra l'altro nella versione 2.0 mi
> pare ci metta un tempo eterno (non vorrei che il problema fosse
> proprio li). Con la 2.1 i tempi di creazione sono decisamente più
> sensati.
>

in occasione del rilascio di splite v.4.0 e' stato introdotto un
modestissimo cambiamento in una funzione SQL legata 
all'inizializzazione
di un nuovo DB.
modifica peraltro messa in forte rilievo nelle note di rilascio, ed
abbondamente discussa piu' e piu' volte nella ML di spatialite.
qgis 2.0 non ha supportato tempestivamente la modifica, e quindi e'
soggetto ad una discreta lentezza in fase di creazione di un nuovo DB
(anche se comunque alla fine i risultati del processo sono 
perfettamente
corretti); viceversa poi qgis 2.1 e' stato correttamente aggiornato,
ed ora alla fine tutto e' tornato a funzionare in modo ottimale.


> Esiste un modo per verificare se un DB Spatialite è corretto ed ha
> tutti i trigger e gli indici a posto?
>

in linea di massima non e' possibile, perche' la casistica "degli
interventi cinesi" (come li chiama Andrea) e' potenzialmente 
illimitata.
in caso di dubbio una chiamata (tutta di seguito) a questa coppia di
funzioni dovebbe rimettere tutti i Triggers per ciascuna colonna 
geometrica
correttamente a posto:
- DiscardGeometryColumn()
- RecoverGeometryColumn()

> Se ci fossero problemi esiste il modo di farne un repair ?
>  

ci sono due funzioni SQL fatte apposta per verificare/ricostruire
gli Spatial Index (quantomeno, nei casi d'uso piu' comuni e 
ragionevoli):
- CheckSpatialIndex()
- RepairSpatialIndex()

qua ci trovi maggiori informazioni:
http://www.gaia-gis.it/gaia-sins/SpatialIndex-Update.pdf

ciao Sandro