[Qgis-developer] wrong table listing in spatialite database

[a.furieri at lqt.it]

On Tue, 13 Oct 2009 10:31:20 +0200, Henning Lorenz wrote
> Hi!
> 
> This is with QGis 1.3 on OSX 10.6. Connecting to spatialite 
> databases  with the current trunk (r11800) does not work at all.
> 
> I consolidated a set of shape files into a sqlite/spatialite 
> database.  This implied copying of data, dropping and renaming of 
> tables. Now the  sqlite database is vacuumed and ready to use. 
> However, when connecting  with QGis to the database I get a list of 
> old obsolete tables (that  have been dropped or renamed), and not 
> the actual list of tables as  with .tables in sqlite/spatialite. 
> Other applications, on the other  hand, provide the correct table 
> listing (e.g. GRASS db.tables  command). Any help is appreciated! Thanks.
> 
> Henning
> 

Hi Henning,

the QGis/SpatiaLite data provider only processes
fully qualified geometry tables, ignoring any
other 'plain' table.

and in order to identify such geom-tables, we cannot
rely on .tables at all (because this will simply
produce the full unqualified tables list).

so, the only way to get the fully qualified geometry
tables list is to use the following SQL query:

SELECT * FROM geometry_columns;

I suppose that after dropping and/or renaming tables
your DB now contains a completely misaligned
'geometry_columns' table (i.e. no longer corresponding
to the actual table layout), and this easy explains any
trouble you are experiencing.

Suggestion:
check 'geometry_columns' (and correct any inconsistency
you can detect) in order to recover your DB.

bye, Sandro