Feature request #4787

Enable edits to Spatialite Views with "Instead of" triggers

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

Status:Closed
Priority:Normal
Assignee:Nathan Woodrow
Category:-
Pull Request or Patch supplied:No Resolution:
Easy fix?:No Copied to github as #:14642

Description

SQLite allows UPDATE statements to be executed on views if (and only if) an "Instead Of" trigger is defined on that view. In QGIS, "Toggle Editing" is grayed out for all Spatialite views (which makes perfect sense generally speaking).

What would be great is if QGIS could recognize the existence of an "Instead Of" trigger associated with a Spatialite View and allow layer editing in that case.

IMHO recognizing which columns (including geometry) have trigger actions is beyond the necessary scope - just letting QGIS issue the update statements to the view would be huge - the trigger authors can be responsible for accounting for all columns or issuing RAISE errors at the database level.


Related issues

Related to QGIS Application - Bug report #9022: Spatialite views are not editable Closed 2013-11-06

Associated revisions

Revision 4413a7db
Added by Nathan Woodrow over 7 years ago

[FEATURE] Add support to edit views that have triggers. Fixes #4787

History

#1 Updated by Brian Freed over 7 years ago

in case it helps, if the spatialite view / QGIS layer in question is named 'vw_nodecallout_editor',

SELECT count(*) from sqlite_master
WHERE type='trigger'
and tbl_name='vw_nodecallout_editor'

will check for the existence of a trigger on the layer (and 'instead of' is the only type of trigger that SQLite allows on views, so the trigger's sql statement is actually irrelevant now that I think about it)

#2 Updated by Brian Freed over 7 years ago

Heh. I'm pretty embarrassed. In re-reading some other tickets, I think I may have misunderstood the bounty purpose.

I'd like to PAY someone 100 euros to take a crack at this. I'm not looking to receive 100 euros for making this suggestion, that'd be silly.
Sorry for the misunderstanding.

Is there a different place to request features/fixes for hire?

#3 Updated by Nathan Woodrow over 7 years ago

The way you have done it is correct. Bounty value in euros: is intended for how much money you want to give to get this fixed.

#4 Updated by Nathan Woodrow over 7 years ago

  • Assignee set to Nathan Woodrow

On that note. I'm happy to have a go at adding support for this. I'll have a go at it tonight if nothing else pops up.

#5 Updated by Brian Freed over 7 years ago

Oh, good. Another post had the OP asking where he could get paid, and I got confused.

Thanks for taking this on, I'm excited!

#6 Updated by Nathan Woodrow over 7 years ago

Had a go at adding something tonight. Everything seems to be working, just going to do some more testing over the rest of the weekend.

#7 Updated by Brian Freed over 7 years ago

That's great to hear! Thanks for the status update

#8 Updated by Nathan Woodrow over 7 years ago

  • Target version changed from Version 1.7.4 to Version 1.8.0

#9 Updated by Anonymous over 7 years ago

  • Status changed from Open to Closed
  • % Done changed from 0 to 100

#10 Updated by Nathan Woodrow over 7 years ago

Ok added it in.

One thing to note is that QGIS is a little crude in it's error reporting, if something goes wrong in the trigger or you don't have one defined for DELETE and you try and delete it will just say "Can't commit N features".

The error reporting method might be something that needs to be looked at in the future.

I also think this will be a 1.8 release thing vs 1.7.4 as it's not really a bug fix more of a new feature.

#11 Updated by Martin Dobias over 7 years ago

Nathan Woodrow wrote:

One thing to note is that QGIS is a little crude in it's error reporting, if something goes wrong in the trigger or you don't have one defined for DELETE and you try and delete it will just say "Can't commit N features".

The error reporting method might be something that needs to be looked at in the future.

Maybe when checking for triggers you could enable only some editing actions (by setting the appropriate capabilities) depending on which triggers are defined on the view.

#12 Updated by Nathan Woodrow over 7 years ago

Martin Dobias wrote:

Maybe when checking for triggers you could enable only some editing actions (by setting the appropriate capabilities) depending on which triggers are defined on the view.

True. I can work on adding this in. Shouldn't be too hard, just search for "Instead Of Delete" or Update or Insert in the trigger SQL.

#13 Updated by Brian Freed over 7 years ago

Excellent! And yeah, I've gotten the hang of the error reporting by now - whenever I get that cryptic message, I do the same operation manually in Spatialite-GUI, and can see what's going on from there. I plan to use the RAISE function to have Spatialite pass a useable error message on to QGIS in the event of deletes and inserts (for the particular implementation I have in mind right now)

It's Sunday here, so I won't be at the office until tomorrow, but I know my boss will be eager to make payment, and I'm eager to check it out. Nathan, how do we get in touch to figure out payment method, etc?

And for testing it out, is my best bet to grab the OSGeo installer and install qgis-dev?

#14 Updated by Jürgen Fischer over 7 years ago

Brian Freed wrote:

And for testing it out, is my best bet to grab the OSGeo installer and install qgis-dev?

If you are on windows, yes. But the next build it still about 4h out...

#15 Updated by Giovanni Manghi over 7 years ago

  • Tracker changed from 4 to Bug report

#16 Updated by Jürgen Fischer over 7 years ago

  • Tracker changed from Bug report to Feature request

Also available in: Atom PDF