Bug report #9022
Spatialite views are not editable
| Status: | Closed | ||
|---|---|---|---|
| Priority: | Severe/Regression | ||
| Assignee: | - | ||
| Category: | Data Provider/SpatiaLite | ||
| Affected QGIS version: | 2.0.1 | Regression?: | No |
| Operating System: | Windows | Easy fix?: | No |
| Pull Request or Patch supplied: | No | Resolution: | |
| Crashes QGIS or corrupts data: | No | Copied to github as #: | 17677 |
Description
QGIS v1.8 introduced support for editing Spatialite views defined with 'instead of' triggers.
In QGIS 2.0, views are editable only on databases created with Spatialite 3.0.
Views created with the same SQL are not editable on databases created by Spatialite 4,
regardless of the new 'read_only' column value (views_geometry_columns table).
(Verified editing in Spatialite GUI is still fine > not a view definition problem)
Related issues
History
#1
Updated by Brian Freed about 11 years ago
Update: in 64-bit Master, build 5f5cd4c, 'Toggle Editing' button is disabled for views in Spatialite version 3.0 databases as well.
#2
Updated by Giovanni Manghi about 11 years ago
- Status changed from Open to Feedback
- Subject changed from Spatialite 4.1.1 views are not editable to Spatialite views are not editable
can we consider this a regression then?
#3
Updated by Brian Freed about 11 years ago
I'd consider it a regression from 1.8, yes.
#4
Updated by Giovanni Manghi about 11 years ago
- Priority changed from Normal to Severe/Regression
#5
Updated by Brian Freed about 11 years ago
We'd be willing to put another $100 bounty on this one. I can't seem to find where to post one.
(I thought it used to be in the New Issue tracker drop-down, but my memory's like a sieve sometimes)
Anyone know?
#6
Updated by Brian Freed almost 11 years ago
- Status changed from Feedback to Closed
Turns out, it's not a QGIS regression, it's an unintended side effect of a change in Spatialite 4.
https://www.gaia-gis.it/fossil/libspatialite/wiki?name=switching-to-4.0
Example: a view named vw_Street_Intersection with an "instead of" trigger (editable view)
Starting with v4, geometry columns have to be inserted as lowercase. The advice there is to insert Lower('vw_Street_Intersection').
It works in Spatialite-GUI, and the views display ok in QGIS, so at first glance it seems fine.
But it seems QGIS defines the layer in the .qgs file in accordance with the geometry columns, so 'vw_street_intersection' instead of the actual mixed-case view name.
And so you lose the edit button, probably because 'SELECT * FROM sqlite_master WHERE type=trigger AND tbl_name = 'vw_street_intersection' will fail.
I imagine this is the check being used to enable the edit button.
Using LIKE instead of = would solve the problem, but I'm marking this as closed, since it's probably a better practice to redefine all view definitions to be lowercase across the board.
As a hack, swapping the names in the .qgs file works too. Opening the project in a text editor and doing a replace all vw_street_intersection > vw_Street_Intersection results in the edit button being enabled.
#7
Updated by Brian Freed almost 11 years ago
One more quirk:
add view from add layer dialog => CAN edit (once fix the case issue),
<datasource>dbname='./OSP_Design.sqlite' table="vw_Street_Intersection" (geometry) sql=</datasource>
add same view by dragging database in => CANNOT edit,
<datasource>./OSP_Design.sqlite|layername=vw_Street_Intersection</datasource>
Best guess is the difference in syntax is causing the behavior difference.