search

opengisch / qfieldsync

Sync your projects to QField
https://qfield.org/
GNU Lesser General Public License v3.0
55 stars 25 forks source link

Insert Issue Upon Sync #344

Open djackan opened 2 years ago

djackan commented 2 years ago

QField Sync Versions: v3.4.4, v4.0.0-BETA16 QGIS version | 3.20.3-Odense | QGIS code revision | 495fbaec

I have an issue where the forms and Qfield Sync gave foreign key constraint violation warnings because a child table would try to save before the parent table existed as I was still filling out a form or when I would sync. To work around the issue with QGIS I made the foreign keys deferrable and would defer BEFORE INSERT with a trigger. That solved the issue for QGIS, but it did not solve the issue for QField Sync. The only way I can think why it would work for one and not the other is it appears to INSERT or save tables in a different order than what QGIS forms does. What this means is if I try to work around the issue with DEFER for QField Sync then it won't work for QGIS Forms and vice versa. Thank you for any help you can provide.

-- | -- | -- | -- Qt version | 5.15.2 Python version | 3.9.5 GDAL/OGR version | 3.3.1 PROJ version | 8.1.1 EPSG Registry database version | v10.028 (2021-07-07) GEOS version | 3.9.1-CAPI-1.14.2 SQLite version | 3.35.2 PDAL version | 2.3.0 PostgreSQL client version | 13.0 SpatiaLite version | 5.0.1 QWT version | 6.1.3 QScintilla2 version | 2.11.5 OS version | Windows 10 Version 2004

suricactus commented 2 years ago

hey, @djackan thanks for reporting this issue. It sounds like something that has been fixed recently, so it is worth trying to: 1) install QGIS 3.22 2) package the project with QGIS 3.22 and QFieldSync BETA 3) make your modifications with QField 1.10 or any of the beta releases 4) enable transaction mode in QGIS for the project 5) sync back the changes from QField to QGIS

This might work without the need to do extra magic with foreign keys.

djackan commented 2 years ago

@suricactus I made the updates you recommended and set "transaction mode" and did the syncing with and without Defer on some of the foreign key constraints. Now the QField app itself will not work properly because the parent table does not save/create the primary key prior to the child table being created. This has caused an FK constraint issue and will not create the point in the QField app. I guess I can only say I don't know if there is still a QField Sync issue because I cannot get to that point. Thank you for the tip and I will keep trying some variations to see if I can figure out what exactly is going on.

suricactus commented 2 years ago

Hey, @djackan can you please share which version of QField you have used? You mentioned that QField does not generate the parent PK before the child one, was this the case in the prior QField versions. Finally, does it make sense to use QGIS default expressions to create the PK? Note that a very basic project as an example can help a lot.

djackan commented 2 years ago

@suricactus I used QField version 2.0.8 (a44914). I have made some changes so it now appears to create (visually) the PK in the parent table, but it still doesn't appear to save it prior to creating/or saving the child table. This then creates the the same issue and I cannot create a point. This is an issue I have had with previous versions of QField. As for creation of the PK I have tried many variations such as setting the parent table default value to uuid(), or having no default and setting the widget type to 'uuid generator". I have also done variations on the child tables for the FK using widget type "value relation" to refer back to the parent table PK value. In other FK cases I set the widget type to "text" and the default to current_value("observations.observationid") to get the value of observationid (the PK) from the parent table. The following is a link to a ~306mb zip file that includes the backup of my postgis database, the QGIS project file, and the QField export package: https://1drv.ms/u/s!AqjJo2iAo7jNgkHf8TWIN-Lp30bt?e=0USwEd .