Question: Can't continue collecting data after raising custom error in Spatialite

[karzak]

I have a spatialite table that uses a before insert trigger to prevent users from collecting a record when a related record does not exist in a related table. This works fine is spatialite-gui. When I test in Roam, the error is thrown as expected, but the transaction is not properly rolled back until I quit roam. For the rest of the session, the errant point remains on the map and when I try and collect subsequent valid points, the error is thrown again. I have tried using FAIL, ABORT, and ROLLBACK with no luck. After I close Roam, the database is in it's proper state (up to where the first error was thrown) and I can collect points again, but if the error is raised during a Roam session I have to quit and reopen.

My question is this: How does Roam (QGIS?) handle transactions that fail? Is there something I can do to force Roam to rollback/abort the transaction within the same session? If not, is there a way to raise a warning so I can alert users that the record they collected is invalid?

I can create a reproducible example project but it will take some work, so let me know if this is of interest.

[bzeeb]

Hi Kevin, we would recommend running this kind of validation check on the form itself rather than within the database. Nathan may be able to resolve the issue you hit now however it would be safer to keep this logic rolled up in the form.

All your forms should have a form.py with methods ready to use for this, also allows you to display a message warning user instead of simply failing silently in the background:

[karzak]

Thanks bzeeb. Can you explain a bit (or point me to an example) of how form.py is used? Say I have a table (A) with field 'Target'. When users collect a feature two validations need to be checked, First, the future must be within a polygon in a different table (P). Second, there must be a feature from table (B) with the same 'Target'. In sql I'm using ST_WITHIN(new.geom, P.geom) and A.target = B.target. Is there some way I can access the values in tables P and B as part of form.py? What would the basic syntax for such an operation be?

[bzeeb]

Would suggest reading through some of the QGIS Python guides if you have a chance to get an overview of how to use, the calls will largely be the same in Roam: http://docs.qgis.org/testing/en/docs/pyqgis_developer_cookbook/intro.html

For your specific case I found this which might looks very helpful for SpatiaLite access from Python: http://www.gaia-gis.it/spatialite-2.4.0-4/splite-python.html

Some straightforward code there however haven't tested it myself.

[bzeeb]

I should also mention there is a lot of useful sample code inside the Search plugin bundled with Roam...

\IntraMaps Roam\plugins\search_plugin\search.py

You may be able to get some hints from here as well.