Default values for PK in tables that do not inherit from CityGML core

[PRemmen]

As described in #6 I'm using EnergyADE and UtilityNetwork Import in 3DCityDB. It seems to me, that for all tables that do not inherit from the core CityGML module the default value (which should be the sequence) is not set. here is the example for FloorArea which I took from CREATE_ADE_DB.sql in the mapping folder. I'm happy to share the whole SQL script if anyhow helpful.

CREATE TABLE engy_floorarea
(
    id INTEGER NOT NULL,
    building_floorarea_id INTEGER,
    buildingunit_floorarea_id INTEGER,
    thermalzone_floorarea_id INTEGER,
    type VARCHAR(1000),
    usagezone_floorarea_id INTEGER,
    value NUMERIC,
    value_uom VARCHAR(1000),
    PRIMARY KEY (id)
);

CREATE SEQUENCE engy_floorarea_seq
INCREMENT BY 1
MINVALUE 0
MAXVALUE 2147483647
START WITH 1
CACHE 1
NO CYCLE
OWNED BY NONE;

Here is an example from core CityGML where the default value is set (taken from SCHEMA.sql from CityDB Version 4.0.1)

CREATE TABLE citymodel(
    id integer NOT NULL DEFAULT nextval('citymodel_seq'::regclass),
    gmlid character varying(256),
    gmlid_codespace varchar(1000),
    name character varying(1000),
    name_codespace character varying(4000),
    description character varying(4000),
    envelope geometry(POLYGONZ),
    creation_date timestamp with time zone,
    termination_date timestamp with time zone,
    last_modification_date timestamp with time zone,
    updating_person character varying(256),
    reason_for_update character varying(4000),
    lineage character varying(256),
    CONSTRAINT citymodel_pk PRIMARY KEY (id)
     WITH (FILLFACTOR = 100)

);

CREATE SEQUENCE citymodel_seq
    INCREMENT BY 1
    MINVALUE 0
    MAXVALUE 2147483647
    START WITH 1
    CACHE 1
    NO CYCLE
    OWNED BY NONE;

[yaozhihang]

Hi thanks , yes, it seems that the AutoIncrement statement for the PK column is missing. This should be improved. But i think this should not be a big issue for applications, because you can get an unique incremental value from the sequence and explicitly use this value in the SQL insert statement.

[PRemmen]

@yaozhihang thanks for your reply. I'm using ORM Mapping from Django to connect to the database and actually there are work-arounds which i currently implemented but they are not very nice! :)

It would be great if you could keep me informed in this issue once this is solved! 👍 :-)

[gioagu]

Hi all,

As this issue made me aware that it may have consequences also for the ADE extension of the 3dCityDB-Tools plugin I am developing, here a proposed workaround for the time-being.

In the CREATE_DB.SQL file of the ADE, simple add manually, after each CREATE SEQUENCE statement, an ALTER TABLE statement like this example:

` CREATE SEQUENCE engy_floorarea_seq ...etc. etc. OWNED BY NONE;

ALTER TABLE engy_floorarea ALTER COLUMN id SET DEFAULT NEXTVAL('engy_floorarea_seq');

` This is for PostgreSQL, but something similar can be made for Oracle for sure. It should be safe, but best if our VCS friends confirm. @clausnagel , @yaozhihang : is this simple tweak going to break anything? I do not think so, but better safe than sorry...

[gioagu]

Addendum to my previous post.

The proposed workaround will work only if you install the ADE to the default "citydb" schema.

If not, e.g. you want to install the ADE to "citydb2", the script will cause an error during installation. In this case, do not add anything to the CREATE_DB.SQL script and simply run the ALTER TABLE ... statements in a SQL console or in PgAdmin. Ideally, always use schema-qualified names for tables, etc.

So, for example:

ALTER TABLE citydb2.engy_floorarea ALTER COLUMN id SET DEFAULT NEXTVAL('citydb2.engy_floorarea_seq');

[clausnagel]

@yaozhihang, can you please check and propose a solution if possible?