[Qgis-developer] Postgis layer_styles table

[Nyall Dawson]

On 11 January 2017 at 09:49, Jorge Gustavo Rocha <jgr at di.uminho.pt> wrote:
> Hi,
>
> I would like to improve a little bit the style storage on Postgis.
>
> There are 3 little things I would like to do. That's way I would like to
> discuss them here, prior to any PR. Maybe someone else is working on this.
>
> i) Change the DDL table definition (just column sizes)
> ii) Allow users to remove unused styles
> iii) Improve error management
>
> Regarding each proposal:
>
> i) The current DDL is (as defined in [1]):
>
> CREATE TABLE layer_styles(
>  id SERIAL PRIMARY KEY
>  ,f_table_catalog varchar
>  ,f_table_schema varchar
>  ,f_table_name varchar
>  ,f_geometry_column varchar
>  ,styleName varchar(30)
>  ,styleQML xml
>  ,styleSLD xml
>  ,useAsDefault boolean
>  ,description text
>  ,owner varchar(30)
>  ,ui xml
>  ,update_time timestamp DEFAULT CURRENT_TIMESTAMP
> )
>
> On Postgresql, both the username and column names can have 63 characters
> [3].
>
> a) There is an old feature request [2] to enlarge the owner column name (it
> only has 30 characters right now).
>
> b) Sometimes I use the same name for the table and for the style. It makes
> sense to allow the styleName as long as the table name can be.
>
> My proposal is to change both owner and styleName columns to 63 characters.
>
> So, my first point is: does anyone knows/remembers why these fields were
> defined with 30 characters? Are there any arguments against changing the
> maximum length to 63?

No idea on the history here, but why not make styleName a text column?
There's no benefit to varchar(n) over text fields on postgres unless
there's some application logic which requires the size restriction
(which in this case I don't believe there is).

>
> ii) I found no way to delete an exiting style from QGIS interface. Styles
> can be added or changed, but not deleted.
>
> Which would be the best approach to allow users to remove an existing style?

Probably the "style manager" dialog would be the most logical place to
expose this.

>
> I can allow the removal by adding a small button to the "load style from
> database" dialogue [4]. The user selects a style and the remove button
> becomes available to remove it. It is not a perfect solution, but it is easy
> to implement.
>
> iii) When writing or updating styles, the error management is very simple. I
> would like to provide further information to the user when the user is not
> able to insert, update (or delete) the style. Right now, a generic message
> is presented, that says:
> "Unable to save layer style. It's not possible to insert a new record into
> the style table. Maybe this is due to table permissions (user=%1). Please
> contact your database administrator."

Sounds great, all improvements which would be very much welcomed!

Nyall

>
> [1] src/providers/postgres/qgspostgresprovider.cpp
> [2] https://hub.qgis.org/issues/11421
> [3]
> https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
> [4] http://webgis.di.uminho.pt/~jgr/qgis%20load%20style%20dialog.png
>
> Regards,
>
> J. Gustavo
> --
> Jorge Gustavo Rocha
> Departamento de Informática
> Universidade do Minho
> 4710-057 Braga
> Tel: +351 253604480
> Fax: +351 253604471
> Móvel: +351 910333888
> skype: nabocudnosor
> _______________________________________________
> Qgis-developer mailing list
> Qgis-developer at lists.osgeo.org
> List info: http://lists.osgeo.org/mailman/listinfo/qgis-developer
> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-developer