Add-dataset: Schema violation due to exceeding a limit of 30 characters

[andreatitolo]

Describe the bug
I was trying to make kart aware of the layer_styles table present in my geopackage. The table is created through the "Save as default in the source database" prompt in QGIS. This interaction automatically assign the layer name to the styleName column of the layer_styles table.

However, when importing the dataset through kart add-dataset layer_styles I get a schema violation error saying that the name if the style cannot exceed 30 character (see below).

Due to some unknown reasons, there is no way for me to edit this table, as I cannot see the layer_styles in the database view of QGIS, so I cannot change the name of the styles manually.

To Reproduce
Steps to reproduce the behaviour:

1. Create a repo and import some dataset with a long name (e.g. some of natural earth data)
2. Load a layer in QGIS and edit the style
3. From the properties symbology panel, click on "Style"->"Save as Default"-> "Datasource Database"
4. kart status --list-untracked-tables should show a layer_styles table
5. Run kart add-dataset layer_styles and you will get the 30 characters limit error.

Reproducible repo with layer_styles table present but not tracked.

Expected behaviour
I think I should be able to track the table even if the name of the styles is longer than 30 characters?

I don't know why I cannot see the layer_styles table in QGIS, but it might be a small bug of a previous version of the qgis plugin, as with the reproducible example above I am able to see it.

Output

When importing the dataset through kart add-dataset layer_styles, I get the following:

layer_styles: In column 'styleName' value 'basemap_ne_10m_admin_0_countries' exceeds limit of 30 characters
Error: Schema violation - values do not match schema

Version Info

• OS: macOS 13.4 - reproduced also on Pop!OS 22.04
• Version: 0.13.0

[olsen232]

This isn't really a Kart bug: some other software (I think QGIS) has done something ill-advised, and Kart is not allowing it.

The layer_styles table that QGIS has created has the following schema (ommitting some unimportant bits)
CREATE TABLE "layer_styles" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "f_table_catalog" TEXT(256), "f_table_schema" TEXT(256), "f_table_name" TEXT(256), "f_geometry_column" TEXT(256), "styleName" TEXT(30), "styleQML" TEXT, "styleSLD" TEXT, "useAsDefault" BOOLEAN, "description" TEXT, "owner" TEXT(30), "ui" TEXT(30), "update_time" DATETIME)
I don't know why it chose this schema, but notably, it declares the styleName column to have type TEXT(30), ie, a text-field that can be a maximum 30 characters long. The only content of this column is the string "ne_10m_geography_regions_elevation_points" which is 41 characters long. Geopackage files allow this - you can create a tables with text-fields with a maximum-length, and then ignore that maximum length by inserting longer strings into that column. But, Kart is stricter and requires that the declared schema be followed.

One reason for Kart's strictness is because Kart also supports other working copy types - eg Postgresql - which are similarly strict, all values have to conform to the table's schema. So by having Kart enforce these checks then schema violations will be caught sooner (at the time of data import) rather than later (ie, at the time somebody tries to check out a Kart repository into a Postgresql database).

I'm not sure what the proper fix here should be or whose software it should be applied to. Here are two workarounds:

• Make the layername "basemap_ne_10m_admin_0_countries" shorter before generating the style table.
• Manually editing the style table in sqlite before adding it to kart, so that it doesn't have the TEXT(30) constraint. sqlite doesn't make editing table schemas easy, but I had a try and this is what I came up with:

sqlite> CREATE TABLE IF NOT EXISTS "layer_styles_copy" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "f_table_catalog" TEXT(256), "f_table_schema" TEXT(256), "f_table_name" TEXT(256), "f_geometry_column" TEXT(256), "styleName" TEXT, "styleQML" TEXT, "styleSLD" TEXT, "useAsDefault" BOOLEAN, "description" TEXT, "owner" TEXT(30), "ui" TEXT(30), "update_time" DATETIME DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')));
sqlite> INSERT INTO layer_styles_copy SELECT * FROM layer_styles;
sqlite> DROP TABLE layer_styles;
sqlite> ALTER TABLE layer_styles_copy RENAME TO layer_styles;

[olsen232]

Oh, or, as you say, truncating the styleName to be shorter would probably work too and would be simpler. Here's the SQL code for that:

sqlite> UPDATE layer_styles SET styleName = substr(styleName, 0, 30);

Let me know if you need any help manually making edits in sqlite

[andreatitolo]

Hi! Thanks for the detailed answer and your help :) I ended up making the layername shorter before generating the style table and everything works correctly. It was easier for me than manually editing the table.

I wonder if it would be worth pointing this out to the QGIS people? i.e. if they could add some sort of check that it either automatically truncates layernames longer than 30 chars, or that it would modify the max length of the field.

[rcoup]

Manually editing the style table in sqlite before adding it to kart, so that it doesn't have the TEXT(30) constraint. sqlite doesn't make editing table schemas easy, but I had a try and this is what I came up with:

I think you can also do (assuming it's not a foreign key/etc)

ALTER TABLE layer_styles ADD COLUMN styleName2 TEXT;
UPDATE layer_styles SET styleName2 = styleName;
ALTER TABLE layer_styles DROP COLUMN styleName;
ALTER TABLE layer_styles RENAME COLUMN styleName2 TO styleName;