Bug report #9022

Spatialite views are not editable

Added by Brian Freed over 6 years ago. Updated over 6 years ago.

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

Related to QGIS Application - Feature request #4787: Enable edits to Spatialite Views with "Instead of" triggers Closed 2012-01-11

History

#1 Updated by Brian Freed over 6 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 over 6 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 over 6 years ago

I'd consider it a regression from 1.8, yes.

#4 Updated by Giovanni Manghi over 6 years ago

  • Priority changed from Normal to Severe/Regression

#5 Updated by Brian Freed over 6 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 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.

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 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,
<datasource>./OSP_Design.sqlite|layername=vw_Street_Intersection</datasource>

Best guess is the difference in syntax is causing the behavior difference.

Also available in: Atom PDF