Bug report #9022
Spatialite views are not editable
|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|
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)
#6 Updated by Brian Freed over 6 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.
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 over 6 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,
Best guess is the difference in syntax is causing the behavior difference.