Catalogue MANDATORY constraints not considered in DB #623

signedav · 2021-12-23T10:20:20Z

When I have a model using CatalogueObjects_V1 as catalogue base and I extend (1) the MandatoryCatalogueReference and (2) set the attribute referencing it to MANDATORY it still creates me a table without any NOT NULL constraint on those attributes.

INTERLIS 2.3;

MODEL Gebaeude (de)
AT "https://signedav.github.io/usabilitydave/"
VERSION "2021-12-21"  =
  IMPORTS CatalogueObjects_V1;

  TOPIC Katalog =

    CLASS MaterialItem
    EXTENDS CatalogueObjects_V1.Catalogues.Item =
      Code : MANDATORY TEXT;
      Bezeichnung : TEXT;
    END MaterialItem;

    STRUCTURE MaterialItemRef
    EXTENDS CatalogueObjects_V1.Catalogues.MandatoryCatalogueReference =
      /* Reference is MANDATORY because it extends mandatory Rerference in MandatoryCatalogueReference */
      Reference (EXTENDED) : REFERENCE TO (EXTERNAL) MaterialItem;
    END MaterialItemRef;

  END Katalog;

  TOPIC Gebaeude =
    DEPENDS ON Gebaeude.Katalog;

    CLASS Haus =
      Name: TEXT;
      Material : MANDATORY Gebaeude.Katalog.MaterialItemRef;
    END Haus;

  END Gebaeude;

END Gebaeude.

Running this:

java -jar /home/dave/.local/share/QGIS/QGIS3/profiles/DuselDuck/python/plugins/QgisModelBaker/libili2db/bin/ili2pg-4.6.1/ili2pg-4.6.1.jar --schemaimport --dbhost localhost --dbusr postgres --dbpwd ****** --dbdatabase daves_bakery --dbschema geba032 --setupPgExt --coalesceCatalogueRef --createEnumTabs --createNumChecks --createUnique --createFk --createFkIdx --coalesceMultiSurface --coalesceMultiLine --coalesceMultiPoint --coalesceArray --beautifyEnumDispName --createGeomIdx --createMetaInfo --expandMultilingual --createTypeConstraint --createEnumTabsWithId --createTidCol --importTid --smart2Inheritance --strokeArcs --defaultSrsCode 2056 --models Gebaeude /home/dave/dev/signenotes/opengisch/technical_notes/Interlis/course/catalogues/gebaeude_man_cat.ili

Creates in PostgreSQL:

CREATE TABLE gebaude_mandatory.haus
(
    t_id bigint NOT NULL DEFAULT nextval('gebaude_mandatory.t_ili2db_seq'::regclass),
    t_ili_tid character varying(200) COLLATE pg_catalog."default",
    aname text COLLATE pg_catalog."default",
    material bigint,
    CONSTRAINT haus_pkey PRIMARY KEY (t_id),
    CONSTRAINT haus_material_fkey FOREIGN KEY (material)
        REFERENCES gebaude_mandatory.materialitem (t_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED
)

t_ili2db_trafo at smart1

iliname	tag	setting
"Gebaeude.Katalog.MaterialItem"	"ch.ehi.ili2db.inheritance"	"newClass"
"Gebaeude.Gebaeude.Haus.Material"	"ch.ehi.ili2db.catalogueRefTrafo"	"coalesce"
"CatalogueObjects_V1.Catalogues.CatalogueReference"	"ch.ehi.ili2db.inheritance"	"subClass"
"Gebaeude.Katalog.MaterialItemRef"	"ch.ehi.ili2db.inheritance"	"newClass"
"Gebaeude.Gebaeude.Haus"	"ch.ehi.ili2db.inheritance"	"newClass"
"CatalogueObjects_V1.Catalogues.MandatoryCatalogueReference"	"ch.ehi.ili2db.inheritance"	"subClass"
"CatalogueObjects_V1.Catalogues.Item"	"ch.ehi.ili2db.inheritance"	"subClass"

t_ili2db_trafo at smart2

iliname	tag	setting
"Gebaeude.Katalog.MaterialItem"	"ch.ehi.ili2db.inheritance"	"newAndSubClass"
"Gebaeude.Gebaeude.Haus.Material"	"ch.ehi.ili2db.catalogueRefTrafo"	"coalesce"
"CatalogueObjects_V1.Catalogues.CatalogueReference"	"ch.ehi.ili2db.inheritance"	"subClass"
"Gebaeude.Katalog.MaterialItemRef"	"ch.ehi.ili2db.inheritance"	"newAndSubClass"
"Gebaeude.Gebaeude.Haus"	"ch.ehi.ili2db.inheritance"	"newAndSubClass"
"CatalogueObjects_V1.Catalogues.MandatoryCatalogueReference"	"ch.ehi.ili2db.inheritance"	"subClass"
"CatalogueObjects_V1.Catalogues.Item"	"ch.ehi.ili2db.inheritance"	"subClass"

The text was updated successfully, but these errors were encountered:

signedav · 2021-12-23T10:21:43Z

Before I knock on the ili2db door I first ask for any experience on that, since I think if it's an issue you might have encountered it already @zigertiger @romefi Maybe I just made something wrong on schema create or modelling.

In case it's something ili2db cannot handle, we might want to think about a Model Baker solution.

signedav · 2022-01-04T14:55:11Z

Is there something missing on the described use case (model or command) or is it just not supported the way it is @claeis? Are there any suggestions how to handle it?

signedav · 2022-01-17T07:53:16Z

While ordinary one-to-many associations works like expected:
ili:0

[...]
        ASSOCIATION Asset_Publication =
            Asset -- {1} Asset;
            Publication -- {0..*} Publication;
        END Asset_Publication;
[...]

sql:

[...]
    asset bigint NOT NULL,
    CONSTRAINT publication_asset_fkey FOREIGN KEY (asset)
[...]

signedav · 2022-01-28T15:32:32Z

This might be a weakness in ili2db but as far as I understood @claeis is not that easy solvable since in other situation these fields need to be NULL.
I think that it cannot be built in the physical database with a NOT NULL constraint.

romefi · 2022-01-31T09:29:16Z

Ok, makes sense to me. Do you see a chance to solve that on Model Baker without the NOT NULL in the database? Or shall we leave it to:

The user hast to know it's MANDATORY
The MANDATORY constraint can be set manually in the QGIS-project
The validator will show the error, if the constraint fails

sjib · 2022-01-31T11:05:41Z

I do not know whether it is the right place to add those thoughts. If not please feel free to move this to a new issue or discussion.

Is there a general concept about the use of ModelBaker? Is it ...

an improved user interface for ilivalidator that allows to directly visualize errors as we have now with the new data validator
a tool to import data (also with errrors) to correct them and create a valid xtf export afterwards
a tool to import validated data to build a QGIS project to analyse or visualize the data further
...

I think depeding on what I want to do I would want MANDATORY constraints checked or not. Or set flags like --disablevalidation on or off (see https://github.com/claeis/ili2db/blob/master/docs/ili2db.rst#fehlerhafte-daten).

When I look at the development of the last months there have been beeen various tasks added and you can use it for all three purposes above and even more. But it would need other default settings depending on what you want to do.
May be it would be wise to display this also in the ModelBaker wizzard?

signedav · 2022-01-31T12:31:55Z

Thanks for your inputs.

There are the ili2db.ili.attrCardinalityMin entries for each attribute. I didn't checked it 100% but I guess this could be a stable initiator that this value should not be NULL. So I see a chance that Model Baker can handle it without NOT NULL in the database @romefi

@sjib Yes, Model Baker can handle all of your mentioned workflows. I think, the reason to be able to import invalid data could be just to visualize them or to correct them as well. Anyway the constraints should be - as much as possible - like in the model.

The case that a user wants to produce invalid data by having no constraints (maybe to be quick and fix it later on) is more a side case that can be managed by removing the constraints from the project manually.

romefi · 2022-01-31T13:54:57Z

@sjib Model Baker should cover all these use cases. It's worth a thought to add more usecases in the wizzard and have the propper settings depending on the use case. Thanks for the input. We'll discuss it in the project steeriing group.

For now the focus lays on helping people collecting new data, so all constraints should be covered if ever possible. @signedav as it's not high priority I suggest we discuss this issue at the next meeting.

signedav · 2022-06-30T11:41:53Z

Note:
Be aware of inheritances.

INTERLIS 2.3;

MODEL Gebaeude (de)
AT "https://signedav.github.io/usabilitydave/"
VERSION "2021-12-21"  =
  IMPORTS CatalogueObjects_V1;

  TOPIC Katalog =

    CLASS MaterialItem
    EXTENDS CatalogueObjects_V1.Catalogues.Item =
      Code : MANDATORY TEXT;
      Bezeichnung : TEXT;
    END MaterialItem;

    STRUCTURE MaterialItemRef
    EXTENDS CatalogueObjects_V1.Catalogues.MandatoryCatalogueReference =
      /* Reference is MANDATORY because it extends mandatory Rerference in MandatoryCatalogueReference */
      Reference (EXTENDED) : REFERENCE TO (EXTERNAL) MaterialItem;
    END MaterialItemRef;

  END Katalog;

  TOPIC Gebaeude =
    DEPENDS ON Gebaeude.Katalog;

    CLASS Objekt =
      Name: MANDATORY TEXT;
    END Objekt;

    /* In diesem Fall ist Material in der DB nullable und muss es wohl auch sein bei Smart Inheritance 1*/
    CLASS Haus EXTENDS Objekt=
      Material : MANDATORY Gebaeude.Katalog.MaterialItemRef;
    END Haus;
    
    CLASS Statue EXTENDS Objekt =
      Thema: MANDATORY TEXT;
    END Statue;

  END Gebaeude;

END Gebaeude.

Here we have in object mandatory name, in haus mandatory Material and in statue mandatory thema. But when using smart inheritance 1 it will make a super class called object having all those attributes and a type. So some of them needs to be nullable.

CREATE TABLE IF NOT EXISTS gebauede_smart_1.objekt
(
    t_id bigint NOT NULL DEFAULT nextval('gebauede_smart_1.t_ili2db_seq'::regclass),
    t_basket bigint NOT NULL,
    t_type character varying(60) COLLATE pg_catalog."default" NOT NULL,
    t_ili_tid character varying(200) COLLATE pg_catalog."default",
    aname text COLLATE pg_catalog."default" NOT NULL,
    material bigint,
    thema text COLLATE pg_catalog."default",
    CONSTRAINT objekt_pkey PRIMARY KEY (t_id),
    CONSTRAINT objekt_material_fkey FOREIGN KEY (material)
        REFERENCES gebauede_smart_1.materialitem (t_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT objekt_t_type_check CHECK (t_type::text = ANY (ARRAY['haus'::character varying, 'objekt'::character varying, 'statue'::character varying]::text[]))
)

As well as Smart1Inheritance with SuperClass having attributes optional and subclasses are extending mandatory

  TOPIC Gebaeude =
    DEPENDS ON Gebaeude.Katalog;

    CLASS Objekt =
      Name: MANDATORY TEXT;
      Material : Gebaeude.Katalog.MaterialItemRef;
    END Objekt;

    /* In diesem Fall ist Material in der DB nullable und muss es wohl auch sein bei Smart Inheritance 1*/
    CLASS Haus EXTENDS Objekt=
      Material (EXTENDED): MANDATORY Gebaeude.Katalog.MaterialItemRef;
    END Haus;
    
    CLASS Statue EXTENDS Objekt =
      Thema: MANDATORY TEXT;
    END Statue;

  END Gebaeude;

Sollution maybe:

t_ili2db_meta_attrs

ilielement	attr_name	attr_value
[...]	[...]	[...]
"Gebaeude.Gebaeude.Haus.Material"	"ili2db.ili.attrCardinalityMax"	"1"
"Gebaeude.Gebaeude.Haus.Material"	"ili2db.ili.attrCardinalityMin"	"1"
[...]	[...]	[...]

We do have ili2db.ili.attrCardinalityMin in t_ili2db_meta_attrs for the current tables class, but as well we might need to check t_ili2db_trafo if the current class is built as superClass.

t_ili2db_trafo

iliname	tag	setting
"Gebaeude.Katalog.MaterialItem"	"ch.ehi.ili2db.inheritance"	"newClass"
"Gebaeude.Gebaeude.Haus.Material"	"ch.ehi.ili2db.catalogueRefTrafo"	"coalesce"
"Gebaeude.Gebaeude.Statue"	"ch.ehi.ili2db.inheritance"	"superClass"
"CatalogueObjects_V1.Catalogues.CatalogueReference"	"ch.ehi.ili2db.inheritance"	"subClass"
"Gebaeude.Katalog.MaterialItemRef"	"ch.ehi.ili2db.inheritance"	"newClass"
"Gebaeude.Gebaeude.Haus"	"ch.ehi.ili2db.inheritance"	"superClass"
"CatalogueObjects_V1.Catalogues.MandatoryCatalogueReference"	"ch.ehi.ili2db.inheritance"	"subClass"
"CatalogueObjects_V1.Catalogues.Item"	"ch.ehi.ili2db.inheritance"	"subClass"
"Gebaeude.Gebaeude.Objekt"	"ch.ehi.ili2db.inheritance"	"newClass"

signedav · 2022-07-01T08:06:24Z

Another situation (not making big sense in catalogues but a fair construct):

INTERLIS 2.3;

MODEL Gebaeude (de)
AT "https://signedav.github.io/usabilitydave/"
VERSION "2021-12-21"  =
  IMPORTS CatalogueObjects_V1;

  TOPIC Katalog =

    CLASS MaterialItem
    EXTENDS CatalogueObjects_V1.Catalogues.Item =
      Code : MANDATORY TEXT;
      Bezeichnung : TEXT;
    END MaterialItem;

    CLASS MaterialItem1
    EXTENDS MaterialItem=
      Value : TEXT;
    END MaterialItem1;

    STRUCTURE MaterialItemRef
    EXTENDS CatalogueObjects_V1.Catalogues.MandatoryCatalogueReference =
      /* Reference is MANDATORY because it extends mandatory Rerference in MandatoryCatalogueReference */
      Reference (EXTENDED) : REFERENCE TO (EXTERNAL) MaterialItem;
    END MaterialItemRef;

  END Katalog;

  TOPIC Gebaeude =
    DEPENDS ON Gebaeude.Katalog;

    CLASS Haus =
      Name: TEXT;
      Material : MANDATORY Gebaeude.Katalog.MaterialItemRef;
    END Haus;

  END Gebaeude;

END Gebaeude.

When using smart inheritance 2 it will make a super class called MaterialItem and MaterialItem1 and it will have two references in materialitemref and two links in haus. They need to be nullable.

CREATE TABLE IF NOT EXISTS gebauede_smart2.materialitemref
(
    t_id bigint NOT NULL DEFAULT nextval('gebauede_smart2.t_ili2db_seq'::regclass),
    t_ili_tid character varying(200) COLLATE pg_catalog."default",
    t_seq bigint,
    reference_materialitem bigint,
    reference_materialitem1 bigint,
    CONSTRAINT materialitemref_pkey PRIMARY KEY (t_id),
    CONSTRAINT materialitemref_reference_materialitem1_fkey FOREIGN KEY (reference_materialitem1)
        REFERENCES gebauede_smart2.materialitem1 (t_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT materialitemref_reference_materialitem_fkey FOREIGN KEY (reference_materialitem)
        REFERENCES gebauede_smart2.materialitem (t_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED
)

and

CREATE TABLE IF NOT EXISTS gebauede_smart2.haus
(
    t_id bigint NOT NULL DEFAULT nextval('gebauede_smart2.t_ili2db_seq'::regclass),
    t_basket bigint NOT NULL,
    t_ili_tid character varying(200) COLLATE pg_catalog."default",
    aname text COLLATE pg_catalog."default",
    material_materialitem bigint,
    material_materialitem1 bigint,
    CONSTRAINT haus_pkey PRIMARY KEY (t_id),
    CONSTRAINT haus_material_materialitem1_fkey FOREIGN KEY (material_materialitem1)
        REFERENCES gebauede_smart2.materialitem1 (t_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT haus_material_materialitem_fkey FOREIGN KEY (material_materialitem)
        REFERENCES gebauede_smart2.materialitem (t_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED
)

Sollution maybe:

t_ili2db_meta_attrs

ilielement	attr_name	attr_value
[...]	[...]	[...]
"Gebaeude.Gebaeude.Haus.Material"	"ili2db.ili.attrCardinalityMax"	"1"
"Gebaeude.Gebaeude.Haus.Material"	"ili2db.ili.attrCardinalityMin"	"1"
[...]	[...]	[...]

We do have ili2db.ili.attrCardinalityMin in t_ili2db_meta_attrs for the current tables class, but as well we might need to check t_ili2db_trafo for ... hm... I don't know...

t_ili2db_trafo

iliname	tag	setting
"Gebaeude.Katalog.MaterialItem"	"ch.ehi.ili2db.inheritance"	"newAndSubClass"
"Gebaeude.Gebaeude.Haus.Material"	"ch.ehi.ili2db.catalogueRefTrafo"	"coalesce"
"CatalogueObjects_V1.Catalogues.CatalogueReference"	"ch.ehi.ili2db.inheritance"	"subClass"
"Gebaeude.Katalog.MaterialItemRef"	"ch.ehi.ili2db.inheritance"	"newAndSubClass"
"Gebaeude.Gebaeude.Haus"	"ch.ehi.ili2db.inheritance"	"newAndSubClass"
"Gebaeude.Katalog.MaterialItem1"	"ch.ehi.ili2db.inheritance"	"newAndSubClass"
"CatalogueObjects_V1.Catalogues.MandatoryCatalogueReference"	"ch.ehi.ili2db.inheritance"	"subClass"
"CatalogueObjects_V1.Catalogues.Item"	"ch.ehi.ili2db.inheritance"	"subClass"

signedav · 2022-07-06T12:19:15Z

See claeis/ili2db#475

signedav · 2023-02-02T13:22:38Z

@romefi With 7.4.0 (using ili2db 4.10) this should be solved. Needs to be tested and then we can close this.

romefi · 2023-02-03T13:29:09Z

Just tested with SH_Pflegeplanung_Fliessgewaesser_V1_0.zip

E..g. Fauna > Objekt is a MANDATORY reference to CatalogueObject. In the formular Objekt is still optional:

Unless the INTERLIS-model is wrong for the use case, it doesn't seem to work.

signedav · 2023-02-08T09:48:10Z

I can confirm. Tested it again myself and added a comment here claeis/ili2db#475 (comment)

romefi added the offer_requested label Mar 24, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Catalogue MANDATORY constraints not considered in DB #623

Catalogue MANDATORY constraints not considered in DB #623

signedav commented Dec 23, 2021 •

edited

Loading

signedav commented Dec 23, 2021 •

edited

Loading

signedav commented Jan 4, 2022

signedav commented Jan 17, 2022 •

edited

Loading

signedav commented Jan 28, 2022

romefi commented Jan 31, 2022

sjib commented Jan 31, 2022

signedav commented Jan 31, 2022

romefi commented Jan 31, 2022

signedav commented Jun 30, 2022 •

edited

Loading

signedav commented Jul 1, 2022 •

edited

Loading

signedav commented Jul 6, 2022

signedav commented Feb 2, 2023

romefi commented Feb 3, 2023

signedav commented Feb 8, 2023

Catalogue MANDATORY constraints not considered in DB #623

Catalogue MANDATORY constraints not considered in DB #623

Comments

signedav commented Dec 23, 2021 • edited Loading

signedav commented Dec 23, 2021 • edited Loading

signedav commented Jan 4, 2022

signedav commented Jan 17, 2022 • edited Loading

signedav commented Jan 28, 2022

romefi commented Jan 31, 2022

sjib commented Jan 31, 2022

signedav commented Jan 31, 2022

romefi commented Jan 31, 2022

signedav commented Jun 30, 2022 • edited Loading

Sollution maybe:

signedav commented Jul 1, 2022 • edited Loading

Sollution maybe:

signedav commented Jul 6, 2022

signedav commented Feb 2, 2023

romefi commented Feb 3, 2023

signedav commented Feb 8, 2023

signedav commented Dec 23, 2021 •

edited

Loading

signedav commented Dec 23, 2021 •

edited

Loading

signedav commented Jan 17, 2022 •

edited

Loading

signedav commented Jun 30, 2022 •

edited

Loading

signedav commented Jul 1, 2022 •

edited

Loading