QGIS 3 does not load default styles from spatialite (defined in QGIS 2.18)

Question

I have a set of styles stored in a spatialite db with QGIS 2.18, the layer_styles table looks like this:

Adding a layer in QGIS 2.18 loads the style set as default (waters light blue, categorized):

Using the same spatialite db and loading the same layer in QGIS 3.2 does not load the default style defined in the db:

Trying to Style | Load Style | Database Styles Manager... results in an Error 'Error loading styles. The query was logged':

I noticed that when saving a style to spatialite database from QGIS 3.2 the f_table_catalog column is empty, but deleting the values from the above layer_styles table doesn't have any effect (same behavior, same error).

Even when saving the styles to spatialite db from QGIS 3 with option set 'Use as default style for this layer', the default style is not loaded when adding the layer, after all the above mentioned error does not occur and I am able to load the style manually (even if the style is not recognized as related to layer that just saved it...).

Am I missing some drastic changes in QGIS 3 style management, and ist there a way to continue using my QGIS 2.18 spatialite dbs with the correct styles in QGIS 3?

Answer 1

On the second look the changes seem to be less drastic. I noticed the query for styles related to the layer referred to in the Error message is output on the Log Messages | General Tab as a Warning:

SELECT id,styleName,description FROM layer_styles WHERE f_table_schema=NULL AND t_table_name='my_table' AND f_geometry_column = 'geometry' ORDER BY useasdefault DEC, update_time DESC

apart from the above layer_styles table not having an id column but OGC_FID (this is easily fixed and fixes the above error), the query seems to be wrong in f_table_schema=NULL wich is f_table_schema IS NULL. One may test this with QGIS DB Manager: Using ...=NULL delivers no results (and does not throw an error), using ...IS NULL delivers the desired result. As a consequence, no default style is found and loaded.

Issue: https://issues.qgis.org/issues/19426

Answer 2

I am having a similar problem with layer styles stored in a postgis database. When I looked at our project file xml I found that we were only having this issue on postgis layers in which the table name didn't include the schema. Adding a layer in QGIS2.18 does include the schema in the connection string but this clearly wasn't the case in the past. I wrote a simple python script to go through our projects and fix it. Hopefully it will be useful to others here. You will need to adjust your search strings and schema to match your database provider and schema name.

#!/usr/bin/env python3

import xml.etree.ElementTree as ET
from pathlib import Path
import sys

#this script exists for slightly complex changes to qgis projects
#for simple find replace actions you can use shell scripts like the following below
#the shell scripts should be faster


#this one searches all qgis files before using sed to find and replace text it is faster than the grep one liner below
#find  -type f -name "*.qgs" -print0 |xargs -0  sed -i 's/type=Polygon table="fieldSplitsPolygon"/type=MultiPolygon table="public"."fieldSplitsPolygon"/g'

#this one searches for required text with grep and then uses find replace
#grep -rlZ 'type=Polygon table="fieldSplitsPolygon"' | xargs -0  sed -i 's/type=Polygon table="fieldSplitsPolygon"/type=MultiPolygon table="public"."fieldSplitsPolygon"/g'


#you should check what your correct strings look like in elementree and not by looking at the xml in a text editor
#elementree will do things like make your string punctuation safe which can lead to a lot of debugging problems if you 
#just paste in the inupt from your text editor

newschema = '"public".' #set your new schema here.  I should probably have set this up as a sys arg but
                        #I was nervous about messing the escape quotes up when passing it from the shell


def findqgisprojects(filepath,recursivesearch):
    p = Path(filepath)
    #searchexp
    if recursivesearch:
        #find all .qgs files in folder and all subfolders
        searchexp = '**/*.qgs'
    else:
        #find all .qgs files in folder only
        searchexp = '*.qgs'
    for i in p.glob(searchexp):
        print(i)
        openproject(i)

def openproject(projectpath):
    with open(projectpath, "r+") as f:
        try:
            tree = ET.parse(f)
        except:
            return
        root = tree.getroot()
        #find replace
        getlayertreestoreplace(root)
        getmaplayers(root)
        tree.write(projectpath)

def getlayertreestoreplace(root):
    #instead of manually looping through the xml file and dealing with nested layergroups and layers
    #the below expression grabs a list of elements tagged as 'layer-tree-layer' with a first level child
    #with the name specified below
    #instead of hardcoding the strings you should be using a separate function that replaces the search string in the 
    #findall xpath string
    postgreslayers = root.findall(".//layer-tree-layer/[@providerKey='postgres']")
    replacelayertreeinfo(postgreslayers,newschema)




def replacelayertreeinfo(layers,schema):
    for l in layers:
        attribs = l.attrib
        if 'source' in attribs:
            replacementtext = getsourcereplacementtext(attribs['source'],schema)
            l.set('source',replacementtext)

def getsourcereplacementtext(source,schema):
    tablestart = source.find('table=')+6 #this gets the first character at the end of table
    tableend=source[tablestart:].find(' ') + tablestart
    tabledef = source[tablestart:tableend]
    if tabledef.find(schema)==-1 and (tabledef.lower()).find('select') ==-1: #if you can't find the schema adn it's not a query layer you better add the schema
        replacementtext = source[:tablestart] + schema + tabledef + source[tableend:]
        return replacementtext
    else:    #if it's already there you are fine
        return source


def getmaplayers(root):
    #this syntax finds all maplayer notes with a child named layername with a value of google
    #as opposed to the previous filter which looks for attributes by using the @ symbol
    #instead of hardcoding the strings you should be using a separate function that replaces the search string in the
    #findall xpath string
    maplayers= root.findall(".//maplayer[provider='postgres']")
    findreplacemaplayerinfo(maplayers,newschema)



def findreplacemaplayerinfo(maplayerlist,replacetext):
    #datasource is a text element
    for layer in maplayerlist:
        #.find returns first value of your tag, findall gives a list with all instances of search tag
        #since each value should only be there once just use find so you don't need to loop through atain
        ds = layer.find('datasource')
        datasourcetext = getsourcereplacementtext(ds.text,replacetext)
        ds.text = datasourcetext
       # ds.text=replacetext

def main():
    searchpath = sys.argv[1]#sys.argv starts at 1 like other bash commands
    recursivesearch = sys.argv[2]
    #you will need to call the below function if you run this code from "import"
    findqgisprojects(searchpath,recursivesearch)


if __name__ == "__main__":
    main()