Data export fails while writing the xtf instead of during database schema creation

[kandre]

Describe the bug
Data export failed with not-null constraint errors while writing the xtf, however theses errors were not detected during previous step when postgis schema is generated and filled.

Those errors were, in my case:

• NULL value in column identifier from qgep_od.maintenance_event
• NULL value in column identifier from qgep_od.file

To Reproduce

1. Make sure you have at least one tuple with a NULL value in identifier column in either or both maintenance_event and file tables.
2. Export qgep data to xtf using qgepqwat2ili export tool
3. Wait for an error message in QGIS (yellow banner at the top of the map), and click to the "Show log" button to read the log
4. In the log, you have the following error:: Error: illegal value null for Bezeichnung

Expected behavior
The qgepqwat2ili tool should fail and create an error to the user sooner, as it is the case with other attributes and tables having a non-null constraint in the .ili model.

Screenshots / data

This is the error from the log:

Desktop (please complete the following information):

• QGEP plugin version:: 1.5.6
• QGEP datamodel version:: 1.5.6
• QGEP project file version:: custom
• QGIS Version: 3.22.12
• OS:: Windows 10

Additional context

[sjib]

See this discussion #64
We could create earlier fails if we do not convert bezeichnung=null_to_emptystr(row.identifier),

That's a usability question to discuss when to raise errors in the export process. I understand the wish to create those early as export can take quite long.
On the other hand the error message is quite cryptic and the export crashes when null_to_emptystr is not set.

[sjib]

This SQL allows to create a function to list how many identifiers are null in each table. It then can be run with SELECT qgep_od.count_identifier_null_elements();

create_vsa_dss_2015_2_d.count_identifier_null_qgep_od.zip

[sjib]

Scheduled for technical meeting in March 2023 to clarify usability

[sjib]

Conclusion at technical meeting in March 2023:

• How to deal with NULL values in MANDATORY attributes - handling in INTERLIS Export - Usability question: Data export fails while writing the xtf instead of during database schema creation #90 and technical implementation Bezeichnung missing null_to_emptystr #64
  Proposed solution :

• Implement with different styles and themes in QGIS
• Implement identifier MANDATORY - if empty then use OBJ_ID -> Adapt forms in projects file

[sjib]

• Implement integrity check on export: INTERLIS export VSA-DSS fails if organisation subclasses are not added #116 (comment)

[sjib]