Bug report #17309

SpatiaLite Layer default style is (usually) not loaded.

Added by Borys Jurgiel almost 3 years ago. Updated over 1 year ago.

Category:Data Provider/SpatiaLite
Affected QGIS version:master Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:fixed/implemented
Crashes QGIS or corrupts data:No Copied to github as #:25207


A default style for a Spatialite layer is not loaded by QGIS.
Neither from the source db nor the local qgis.qmldb.
Neither by the Spatialite provider, nor by the OGR one.

I accidentally found it working on one machine with those 3 conditions fulfilled: QGIS 2.18.13, Debian, OGR provider. Changing any of those made it broken again.

Supersedes #17256.

Related issues

Copied from QGIS Application - Bug report #17256: SpatiaLite Layer default style is not loaded when using t... Closed 2017-10-06


#1 Updated by Borys Jurgiel almost 3 years ago

  • Copied from Bug report #17256: SpatiaLite Layer default style is not loaded when using the SpatiaLite provider (the OGR provider works fine) added

#2 Updated by Borys Jurgiel almost 3 years ago

After more testing: Saving and loading styles in Spatialite db depends on provider type, QGIS version and probably GDAL version. As a result, saving default style is unreliable.

Test results

- [email protected] Jessie (GDAL 1.x):

- Loading the default style from the source database   
  • SpatiaLite provider requires the proper file path in layer_styles.f_table_catalog in order to find the style.
    It also means the style is not fully portable, as the file path must match.
  • OGR provider doesn't require it, the value may be NULL or wrong (although if there are more than one record with the same
    useAsDefault value, the one with proper path is preffered).
- Saving default style to the datasource database
  • For OGR provider based layers: sets layer_styles.f_table_catalog to NULL, so the style will be only readable for the OGR provider.
  • For SpatiaLite provider based layers: sets proper value to layer_styles.f_table_catalog, so the style will be accessible for both
- Saving default style to the local database
  • For OGR provider based layers: saves to a .qml side file, so the style will be readable for the OGR provider only.
  • For SpatiaLite provider based layers: saves to the qgis.qmldb database, so the style will be readable for both providers

- [email protected] (GDAL 2.x):

- Works similarly, but if the geometry column is uppercase ("GEOMETRY" in my case), there are two new requirements related to the
geometry column name comparision:
  • OGR provider: layer_styles.f_geometry_column value must be uppercase as well
  • SpatiaLite provider: layer_styles.f_geometry_column value MUST BE LOWERCASE (sic!)

- [email protected] Buster (GDAL 2.x):

- OGR provider: As previously, saves with null path and reads correctly styles saved by itself
- Spatialite provider: BROKEN. REGRESSION. Sets two colmuns: layer_styles.f_table_catalog and f_table_schema to NULL. 
Neither provider can load the style. A manual updating the f_table_schema value to '' makes it readable for OGR.
Despite tweaking the f_table_catalog value, I didn't manage to make it readable for the Spatialite provider (itself).
- I couldn't test the uppercase geometry column problem because of the regression above.

- 2.14:

- Let's forget about 2.14 as some conditions must be literally opposite to make it working in 2.14 
(e.g. f_table_catalog must be empty for the Spatialite provider).

Identified bugs prority:

1. Default style saving for SpatiaLite provider is broken in QGIS 3 (regression)
2. SpatiaLite provider should fallback to record with empty/wrong f_table_catalog value, as OGR does. 
Actually I don't see any use of this redundant value, as everything applies for the same file. (could be fixed for QGIS 3.0 and 2.18)
3. Handling uppercase-named geometry column is broken, probably in QGIS 2.18 with GDAL 2.x

#3 Updated by Giovanni Manghi over 1 year ago

  • Status changed from Open to Feedback

Please check if this issue is still valid on QGIS 3.4.5 or 3.6.

#4 Updated by Borys Jurgiel over 1 year ago

  • Resolution set to fixed/implemented
  • Priority changed from Normal to Low
  • Status changed from Feedback to Closed

Almost fixed now.

There are still problems with the default style recognition after saving the style with one provider and opening with the other, but the priority is low for me and I don't think anyone will bother to fix it.

Also available in: Atom PDF