search

opengisch / QgisModelBaker

Create QGIS projects from database schemas or Interlis models
https://opengisch.github.io/QgisModelBaker/
GNU Lesser General Public License v3.0
54 stars 18 forks source link

Catalogue MANDATORY constraints not considered in DB #623

Open signedav opened 2 years ago

signedav commented 2 years ago

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"
signedav commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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:

sjib commented 2 years ago

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 ...

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 commented 2 years ago

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 commented 2 years ago

@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 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

See https://github.com/claeis/ili2db/issues/475

signedav commented 1 year ago

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

romefi commented 1 year ago

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:

image

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

signedav commented 1 year ago

I can confirm. Tested it again myself and added a comment here https://github.com/claeis/ili2db/issues/475#issuecomment-1422312630