[QGIS-Developer] Quick note about listStylesInDatabase(), default style and primary key

[Jésahel Benoist]

Hi,

First, configuration :
QGIS 3.16.4 on Windows 10
Database : SQL Server 11

For a long time I have had problems with Python and retrieving default
style in the database.

I digged around and found that our layer style definition in the
[layer_styles] table was:
---
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[layer_styles](
    [qgs_fid] [int] IDENTITY(1,1) NOT NULL,
    [id] [int] NULL,
    [f_table_catalog] [nvarchar](max) NULL,
    [f_table_schema] [nvarchar](max) NULL,
    [f_table_name] [nvarchar](max) NULL,
    [f_geometry_column] [nvarchar](max) NULL,
    [styleName] [nvarchar](max) NULL,
    [styleQML] [nvarchar](max) NULL,
    [styleSLD] [nvarchar](max) NULL,
    [useAsDefault] [int] NULL,
    [description] [nvarchar](max) NULL,
    [owner] [nvarchar](max) NULL,
    [ui] [nvarchar](max) NULL,
    [update_time] [datetime] NULL,
 CONSTRAINT [PK_layer_styles] PRIMARY KEY CLUSTERED
(
    [qgs_fid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
---

As you can see, the primary key is [qgis_fid].

I've looked in QGIS source code and listStylesInDatabase() and
other functions need the primary key to be [id]... I have no idea where
this [qgis_fid] is coming from, maybe some past import/export.

This issue creates several problems :
- Saving default style creates a new record each time instead of replacing
the default one.
- listStylesInDatabase() returns a list without any clue about the default
style (the first being not the good one as all id == null ). And there
isn't a way to know what is the default style (this function lacks a
default list).

So instead of hanging around in python with the table, I prefered to modify
the table so that the primary key be [id]. I hope there won't be glitches
coming from this but it seems to work.

If it could help,

Jésahel
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20211217/14bc37c1/attachment.html>