[Qgis-developer] Postgis layer_styles table

[Jorge Gustavo Rocha]

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?

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?

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

[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