Finishing import after error and correction in the pg2ili_abwasser schema

urskaufmann commented 1 month ago

General information

I got now twice after importing into the pg2ili_abwasser schema (90% done) an error like this: Details: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "in_od_maintenance_event_identifier" DETAIL: Key (identifier, fk_dataowner)=(Inliner, ch20p3q400001118) already exists.

I can then control in pg2ili_abwasser and some times have only to correct 2 or 3 values (similar identifiers e.g.). It would be nice, if there exist an easy way to continue the import with the last 10% (instead of correcting the xtf and then redo the whole 100% of the import).

Another question: why does the ilivalidator test not find this error?? Unique identifier is a definition in the ili-file

Users and administrators needs

Graphical identity / Logo

Illustrations if applicable.

Description of users and administrators feature requirements

Describe the new feature

Requirements

List of requirements

Illustrations of the features and the requirements

Schemes, screen captures, or any other illustrations.

Graphical User Interface (GUI)

Provide a mockup of how it should look like

Technical analysis and specifications

Feedbacks from the technical group

Feasibility study

IF required, list the conclusions of the Feasibility study, options, alternatives and technical specificities

Risks and constraints

Mutualisation

Mutualisation and possible impacts with other projects

Specifications

IF available, link advanced specifications of the features required

Timeline / deadlines

Add a detailed timeline

[x] 01.09.2024 : First draft
[ ] 01.10.2024 :
[ ] 01.11.2024 :
[ ] xx.xx.2024 : Presentation and review by User group
[ ] xx.xx.2024 : Presentation and review by Technical group
[ ] xx.xx.2024 : TEKSI project definition
[ ] xx.xx.2024 : Presentation and review by Board (strategic ok)
[ ] xx.xx.2024 : TEKSI requirements
[ ] xx.xx.2024 : Presentation and review by Technical group
[ ] xx.xx.2024 : Presentation and review by Board (technical ok)
[ ] xx.xx.2024 : Call for offers
[ ] xx.xx.2024 : ...

Related softwares versions

QGIS version:
Database model version:
INTERLIS Model version:

Linked issues :

[ ] #XXX

Documentation

Documentation links

User manual :
Administrator manual :

Development and integration

Link and comments from the developer about the product

Deliverable :
Recommendations and comments :
Future prospects :

Conclusion

Conclusions from applicant, users or admins group

Conclusion:
Future prospects:

sjib commented 1 month ago

That should be easy to solve:

The UNIQUE constraint in INTERLIS VSA-DSS 2020.1 is not Bezeichnung, Datenherr but Bezeichnung, Zeitpunkt


      unbekannt
    );
    !!@ comment = "Zeitpunkt des Ereignisses"
    Zeitpunkt: INTERLIS_1_DATE;
UNIQUE 
    !!@ comment = "UNIQUE Kombination Bezeichnung, Zeitpunkt, DatenherrRef "
    Bezeichnung, Zeitpunkt;
END Erhaltungsereignis;

sjib commented 1 month ago

This has to be adapted:

https://github.com/teksi/wastewater/blob/6bef802fea6aff50bbb2d75d9a250384695fa7ab/datamodel/changelogs/0001/03_tww_db_dss.sql#L4601 CREATE UNIQUE INDEX in_od_maintenance_event_identifier ON tww_od.maintenance_event USING btree (identifier ASC NULLS LAST, fk_dataowner ASC NULLS LAST);

Change to: CREATE UNIQUE INDEX in_od_maintenance_event_identifier ON tww_od.maintenance_event USING btree (identifier ASC NULLS LAST, time_point ASC NULLS LAST);

or CREATE UNIQUE INDEX in_od_maintenance_event_identifier ON tww_od.maintenance_event USING btree (identifier ASC NULLS LAST, time_point ASC NULLS LAST, fk_dataowner ASC NULLS LAST);

Description and content in the ili are not the same: UNIQUE !!@ comment = "UNIQUE Kombination Bezeichnung, Zeitpunkt, DatenherrRef " Bezeichnung, Zeitpunkt;

@urskaufmann Can you change this in your database and test?

urskaufmann commented 1 month ago

There was no timepoint (Zeitpunkt) data in these maintenance_event-records. Just 3 times the same identifier. I think it's not a TEKSI Bug. The question is: can I finish the export in such a case after correcting the errors in the Austausch-schema.

And the additional question was: Why did ilivalidator not show this error?

  <DSS_2020_1_LV95.Siedlungsentwaesserung.Unterhalt TID="ch18ct4z00004rrw">
    <Letzte_Aenderung>20240220</Letzte_Aenderung>
    <DatenherrRef REF="ch20p3q400001118"/>
    <DatenlieferantRef REF="ch20p3q400001466"/>
    <Art>Sanierung_Renovierung</Art>
    <Bemerkung>Inliner</Bemerkung>
    <Bezeichnung>Inliner</Bezeichnung>
    <Status>ausgefuehrt</Status>
  </DSS_2020_1_LV95.Siedlungsentwaesserung.Unterhalt>
  <DSS_2020_1_LV95.Siedlungsentwaesserung.Unterhalt TID="ch18ct4z00004rry">
    <Letzte_Aenderung>20240220</Letzte_Aenderung>
    <DatenherrRef REF="ch20p3q400001118"/>
    <DatenlieferantRef REF="ch20p3q400001466"/>
    <Art>Sanierung_Renovierung</Art>
    <Bemerkung>Inliner</Bemerkung>
    <Bezeichnung>Inliner</Bezeichnung>
    <Status>ausgefuehrt</Status>
  </DSS_2020_1_LV95.Siedlungsentwaesserung.Unterhalt>
  <DSS_2020_1_LV95.Siedlungsentwaesserung.Unterhalt TID="ch18ct4z00004rs0">
    <Letzte_Aenderung>20240220</Letzte_Aenderung>
    <DatenherrRef REF="ch20p3q400001118"/>
    <DatenlieferantRef REF="ch20p3q400001466"/>
    <Art>Sanierung_Renovierung</Art>
    <Bemerkung>Inliner</Bemerkung>
    <Bezeichnung>Inliner</Bezeichnung>
    <Status>ausgefuehrt</Status>
  </DSS_2020_1_LV95.Siedlungsentwaesserung.Unterhalt>

teksi / wastewater