Forms foreign key and primary key issue

djackan commented 3 years ago

ca_observations_bones_databaseSQL.txt Describe the bug

I have a form with a parent table and two child tables to be used with QField. Now I understand to some extent this is a matter of where to enforce relational integrity, as in on the data entry end (the form) or on the backend (database side). If I enforce on the front end I still want to be able to enforce relational integrity on the backend in case someone other than myself is directly entering observations into the database. I am not sure if this possible, but I wanted to bring this issue up. Thank you all for your hard work and great product.

How to reproduce

My database sql in case you need to see structure.

Shows the form in use and error

Enter data into parent table.
enter data into child table 1.
As you try to save data from child table 1 you get a foreign key error.

Expected behavior:

I would be able to save the data for the child 1 table without getting a foreign key error

Observed behavior:

I get a foreign key error because the data in the parent table is not saved before trying to save the child table. The child table automatically retrieves an observationID from the parent table in order to store it. The observationID doesn't exist yet because the parent table has not been saved, thus preventing me from saving the child table and raising a foreign key error.

QGIS and OS versions

QGIS version	3.14.1-Pi	QGIS code revision	de08d6b71d
Compiled against Qt	5.11.2	Running against Qt	5.11.2
Compiled against GDAL/OGR	3.0.4	Running against GDAL/OGR	3.0.4
Compiled against GEOS	3.8.1-CAPI-1.13.3	Running against GEOS	3.8.1-CAPI-1.13.3
Compiled against SQLite	3.29.0	Running against SQLite	3.29.0
PostgreSQL Client Version	11.5	SpatiaLite Version	4.3.0
QWT Version	6.1.3	QScintilla2 Version	2.10.8
Compiled against PROJ	6.3.2	Running against PROJ	Rel. 6.3.2, May 1st, 2020
OS Version	Windows 10 (10.0)
Active python plugins	LecoS; openlayers_plugin; processing_r; qfieldsync; qgis2web; quick_map_services; rasterdataplotting; temporalprofiletool; timemanager; db_manager; MetaSearch; processing

Additional context

gioman commented 3 years ago

3.14.1-Pi

@djackan too old, you must test with 3.16.1

djackan commented 3 years ago

I just now tested it under the following and the error is still there:

QGIS version	3.16.1-Hannover	QGIS code revision	b381a90dca
Compiled against Qt	5.11.2	Running against Qt	5.11.2
Compiled against GDAL/OGR	3.1.4	Running against GDAL/OGR	3.1.4
Compiled against GEOS	3.8.1-CAPI-1.13.3	Running against GEOS	3.8.1-CAPI-1.13.3
Compiled against SQLite	3.29.0	Running against SQLite	3.29.0
PostgreSQL Client Version	11.5	SpatiaLite Version	4.3.0
QWT Version	6.1.3	QScintilla2 Version	2.10.8
Compiled against PROJ	6.3.2	Running against PROJ	Rel. 6.3.2, May 1st, 2020
OS Version	Windows 10 (10.0)
Active python plugins	LecoS; openlayers_plugin; processing_r; qfieldsync; qgis2web; quick_map_services; rasterdataplotting; temporalprofiletool; timemanager; db_manager; MetaSearch; processing

andreasneumann commented 3 years ago

What database are you using? (your SQL doesn't look like PostgreSQL)

Did you enable transaction mode in the QGIS project?

Transaction mode only works with certain databases. PostgreSQL is definitely supported - for other databases I don't know.

djackan commented 3 years ago

I am using PostgreSQL, I have created postgreSQL databases many times with it. I am not going to say I am the best at SQL, but I know that SQL works as a postgreSQL database.

gioman commented 3 years ago

I am using PostgreSQL, I have created postgreSQL databases many times with it. I am not going to say I am the best at SQL, but I know that SQL works as a postgreSQL database.

@djackan Did you enable transaction mode in the QGIS project?

andreasneumann commented 3 years ago

@djackan - thanks for clarifying. I was confused, because normally you don't use upper case or mixed case object names (table names, column names, pk/fk names). But it will work, if you later on use double quotes around your object names.

My bad, that I did not look at the form, where it was obvious that we are talking about PostgreSQL here.

In your QGIS project (menu Project --> Properties --> Data Sources) - did you enable "Transaction mode" and "Evaluate default values on provider side" ?

If not, please enable both and report back if it fixes your issues?

djackan commented 3 years ago

I tried it again with the settings you mentioned above and the error still appears. Thanks for the clarification on the mixed case, I'll have to remember to follow that convention. If it matters, turns out I did have errors in that version of my sql code, but doesn't really matter since I was testing on a live database. The sql was just provided to give you an idea of database structure.

andreasneumann commented 3 years ago

Did you save the project and reload it after changing the settings?

djackan commented 3 years ago

Yes, I saved and reloaded after changing the settings, but the issue still exists.

gioman commented 3 years ago

@djackan can you provide a dump with table structures and sample data (as far as I have understand the SQL instructions you have attached have errors)?

djackan commented 3 years ago

@gioman I have now cleaned up the sql above it does work, I created a database off of it. As for sample data I am trying to use the forms to enter the data. I can enter it directly into the database and everything works. My form settings are correct and it gives me check marks etc. The error is indicating the parent table has to exist first before a child table can be saved and then throws the foreign key error. Here is a link to my *qgs project file, I have it set to restricted access so I may have to give you permissions when you access it: https://drive.google.com/file/d/1KjZ_kXWxwFieYkmhLkEicTsYofJxh2ES/view?usp=sharing . I have sent a link with permission to your posted github email address.

djackan commented 3 years ago

Just to add some information about this I saved a record to the parent table without filling anything out for a child table. I then went back in to edit the record and then filled out the form for the child table and everything saved. As the error said the parent table must exist first before you can save the child table.

jbdesbas commented 3 years ago

Same issue for me (3.18.2). It look like as soon as the child form is validated, an INSERT query is sent to the database. That query may only be sent in the transaction group, with the parent's INSERT.

alexbruy commented 11 months ago

Does it work with the latest versions?

github-actions[bot] commented 11 months ago

The QGIS project highly values your report and would love to see it addressed. However, this issue has been left in feedback mode for the last 14 days and is being automatically marked as "stale". If you would like to continue with this issue, please provide any missing information or answer any open questions. If you could resolve the issue yourself meanwhile, please leave a note for future readers with the same problem and close the issue. In case you should have any uncertainty, please leave a comment and we will be happy to help you proceed with this issue. If there is no further activity on this issue, it will be closed in a week.

volcan01010 commented 11 months ago

This bug is still present in 3.30.0 with GeoPackages. I will update with more details when I get the chance, but the outline is:

Create a geopackage with a point feature layer including a uuid TEXT UNIQUE NOT NULL column
Add an attributes (non-spatial) table with a foreign key constraint that references the UUID column on the point layer
Drag the geopackage into QGIS - in 3.30.0 it will recognise the layers AND their relationships and add them to the project. Nice!
Modify the point layer form so that the UUID field has a default of uuid()
Turn on editing and click to create a new point feature.
The autogenerated form will include a section for the extra attributes. Enable editing of the attribute layer and add a new entry.
Save the new attribute.
FOREIGN KEY constraint error

volcan01010 commented 11 months ago

Description

Creating a child feature using the relation reference widget on from a newly-created instance of a parent feature doesn't give the parent as an option to link to.

To reproduce

This zip file contains the minimum information to recreate the bug.

qgis-relation-bug.zip

The contents are:

qgis-relation-bug.gpkg - GeoPackage containing a point feature layer and a child attribute layer, linked by UUID foreign key
qgis-relation-bug.gpkg.sql - SQL script to create the geopackage
qgis-relation-bug.qgz - a QGIS project with layers and form configuration

The most important lines in the SQL are:

CREATE TABLE IF NOT EXISTS "parent_point_feature" ( 
    "fid" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "geometry" POINT,
    "uuid" TEXT UNIQUE NOT NULL,
    "name" TEXT NOT NULL,
    "data" TEXT);

CREATE TABLE IF NOT EXISTS "child_attribute" (
    "fid" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "uuid" TEXT UNIQUE NOT NULL,
    "parent_uuid" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "data" TEXT,
    FOREIGN KEY("parent_uuid") REFERENCES "parent_point_feature"("uuid"));

Download and unzip the folder
Open the project file
Select "parent_point_feature" layer, toggle editing and click a point
Enter a name e.g. "point002"
Toggle editing on the "parent_point_feature_child_attribute" relation reference
Click to add a new feature

What happens

Only "point001" appears in the "parent_uuid" drop down.

What should happen

"point002" should also be there.

Notes

This wasn't the bug that I expected when I began to create the minimum worked example. It makes sense that "point002" wouldn't appear in the drop down as it hasn't been saved yet. I do think that I remember it being there before, though.

If it was possible to have the newly created item in the drop down and selected, that would be nice, though.

Perhaps the OK button on the form could be replaced with two buttons "Save" and "Save and Close". That way you could continue to edit the children without having to close and re-open the form.

QGIS and OS versions

volcan01010 commented 11 months ago

OK, I found out why the bug was different this time.

Description

Creating a child feature using the relation reference widget on from a newly-created instance of a parent feature raises "CRITICAL Layer child_attribute : OGR error creating feature -60: failed to execute insert : FOREIGN KEY constraint failed" when saving the child.

To reproduce

Open the project as described above
In "parent_point_features" > Properties > Attributes Form, replace the "UUID Generator" widget with "Text Edit" and set the default value to "uuid()"
Repeat the editing steps above

What happens

Saving the child feature fails with "CRITICAL Layer child_attribute : OGR error creating feature -60: failed to execute insert : FOREIGN KEY constraint failed". In this case, the "parent_uuid" field contains "({6b103d5a-b065-41c9-8006-49dbdb73a655})" instead of the "name" field as appears for saved features.

What should happen

The features are saved. Again, I can see how the relation cannot be resolved but it would make for a much more efficient workflow if child features could be created directly via their parents.

Notes

The "Text Edit" plus "uuid()" default is the workflow recommended in the Mergin Maps documentation.

https://merginmaps.com/docs/layer/attach-multiple-photos-to-features/

The difference in this case is that the relationships are defined and enforced in the database here, rather than in the QGIS project in the Mergin example. However, it is important for us to define the relationship constraint within the database as the geopackage files may be used elsewhere.

volcan01010 commented 11 months ago

I've been trying to find workarounds for this as it would make data entry much easier if children can be entered directly from the parent. I think that would be beneficial for all QGIS users and I am keen to help implement if I can.

At the moment, I am preventing the users encountering the foreign key error by hiding the relation reference part of the form for the child.

I used the "Drag and Drop Designer" on the form to add a tab for the child item. The tab has "Control Visibility by Expression" set with the equivalent of "layer_property( 'parent_point_feature', 'is_editable') is False". This prevents users from creating children when the parent form has unsaved edits.

Unfortunately, this means that we have to toggle editing on the parent if we want to see all the children that are attached to it. This constraint could be eased slightly if the "layer_property" had a value for "had_unsaved_edits" or similar. That way, we wouldn't have to keep toggling the value.

For now, I am going to replace my "Text Edit" + "uuid()" with a "UUID Generator" as another guard against users getting the primary key error. However, this raises the other risk that they may unwittingly attach children to the wrong parents.

Outside of this, I don't know enough about how QGIS handles pending edits and database transactions and commits. I have seen the "sqlite_fetch_and_increment" function in the expression generator that seems to solve this problem for integer primary keys. Perhaps there could be a similar function created for UUIDs.

cc: @saberraz

volcan01010 commented 11 months ago

Note that I have the Project Properties > Data Sources > Editing Behaviour > Transaction mode set to Local Edit Buffer. I don't know enough about how Automatic or Buffered Transaction Groups work or if they would help.

volcan01010 commented 10 months ago

I have updated the minimum reproducible example as a public Mergin Maps project, in case that helps.

https://app.merginmaps.com/projects/SIGMALite/qgis-relation-bug/tree

volcan01010 commented 10 months ago

It may be possible to add an is_modified expression by using the isModified method on the QgsVectorLayer class. While that wouldn't solve the problem, it would make a workaround to prevent adding of children to unsaved parents a bit cleaner.

https://qgis.org/pyqgis/3.28/core/QgsVectorLayer.html#qgis.core.QgsVectorLayer.isModified

qgis / QGIS