[Qgis-developer] wrong table listing in spatialite database

[Henning Lorenz]

On 2009-10-13, at 11:22 , a.furieri at lqt.it wrote:

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

Hi Sandro!

Thanks for your help. I assumed that spatialite creates and updates  
the geometry columns in a database continously. What happend is that  
each new table that I created got a geometry column, but the table  
geometry_columns wasn't updated (entry missing). Spatialite also  
renamed tables, but not the associated entry in the table  
geometry_columns.  Upon a drop table command the table was dropped,  
but spatiallite left an orphan entry in the geometry_columns table. So  
I could see all deleted tables in QGis and not the new ones. Now I  
manually updated the geometry columns in the new tables which also  
wrote the correct entry into the table geometry_columns and then  
manually deleted all orphan entries in the latter. Now it works.   
Thanks again!

Henning