Parent Child reference links in the same dataset

[pixie79]

When loading an CSV file that has self referencing links within it, as long as the data which is being referenced is above the data which is referring to it then the data should load.

Scheme Sample
//// CHANGE name=init-rforganisationcommand-table
CREATE TABLE rf_organisationcommand (
commandname character varying(50),
commandcode character varying(10),
parentcommandid uuid,
commandid uuid NOT NULL
);
GO

//// CHANGE name=pkey1
ALTER TABLE ONLY public.rf_organisationcommand
ADD CONSTRAINT rf_organisationcommand_pkey PRIMARY KEY (commandid);
GO

Sample Data
"commandname","commandcode","parentcommandid","commandid"
"HQ","HQ",,"705B9DB2-7A8E-48C6-8DDC-5781BFE2CFB9"
"Department 1","DEPT1","705B9DB2-7A8E-48C6-8DDC-5781BFE2CFB9","7C06246F-4CB1-43B4-BED4-25225A989F95"
"Department 2","DEPT2","705B9DB2-7A8E-48C6-8DDC-5781BFE2CFB9","a4448d97-14f8-419b-8946-fa00a6eb9a83"
"Department 3,"DEPT3","705B9DB2-7A8E-48C6-8DDC-5781BFE2CFB9","2D0C51AA-18BD-4DB3-BA1E-FE44EBB313B0"
"TEAMA","TEAMA","a4448d97-14f8-419b-8946-fa00a6eb9a83","5AC0F3E4-71AF-4F1C-8654-B0BC11ADE1B6"
"TEAMB","TEAMB","a4448d97-14f8-419b-8946-fa00a6eb9a83","59BCECC9-2DB7-45D1-B5CA-78CB3E6B2440"

As you can see here HQ has three departments each of which can have children.

Within Postgres the following command would load the SQL natively.

postgres=# copy rf_organisationcommand from '/staticdata/rf_organisationcommand.csv' CSV HEADER;

I have also noticed that I need to run the deploy several times to enable all the other files to import data correctly as it does not always import the files in the correct dependancy order. Is there a way in the metadata to help this?

[shantstepanian]

== For dependencies across tables: ==
You can add the FK annotation in the foreign key change section to give a hint to the static data loader to respect that dependency.

For example:

//// CHANGE FK name=pkey1
ALTER TABLE ONLY public.rf_organisationcommand
ADD CONSTRAINT rf_organisationcommand_pkey PRIMARY KEY (commandid);
GO

For more info, search for "FK" in the doc here

== For order of inserts within an object ==
We haven't certified this use case previously. I will take your test case and try it out

[pixie79]

Hi,

I have tried that again with a clean DB but the FK annotation seemed to make no difference

Example Schema
rf_county.sql
//// CHANGE name=init-rfcountry-table
CREATE TABLE rf_country (
countryid character varying(2) NOT NULL,
iso31661alpha3 character varying(3) NOT NULL,
name character varying(40) NOT NULL,
continent character varying(2) NOT NULL,
dial character varying(20),
iso31661numeric integer NOT NULL
);
GO

//// CHANGE FK name=pkey1
ALTER TABLE ONLY rf_country
ADD CONSTRAINT rf_country_pkey PRIMARY KEY (countryid);
GO

rf_unlocode.sql
//// CHANGE name=init-rfunlocode-table
CREATE TABLE public.rf_unlocode (
unlocodeid uuid NOT NULL,
countryid character varying(2) NOT NULL,
unlogeolong numeric,
unlogeolat numeric,
name character varying(80) NOT NULL,
namewodiacritics character varying(80) NOT NULL,
iata character varying(3),
locode character varying(3) NOT NULL,
seaport boolean NOT NULL,
railterminal boolean NOT NULL,
airport boolean NOT NULL,
postexchange boolean NOT NULL,
multimodal boolean NOT NULL,
fixedtransport boolean NOT NULL,
bordercrossing boolean NOT NULL,
subdivision character varying(3),
roadterminal boolean NOT NULL
);
GO

//// CHANGE name=pkey1
ALTER TABLE ONLY public.rf_unlocode
ADD CONSTRAINT rf_unlocode_pkey PRIMARY KEY (unlocodeid);
GO

//// CHANGE name=constraint1
ALTER TABLE ONLY public.rf_unlocode
ADD CONSTRAINT rf_unlocode_countryid_fkey FOREIGN KEY (countryid) REFERENCES public.rf_country(countryid);
GO

If I load these two definitions, with a matching pair of CSV's. On the first run the rf_unlocode table fails to populate with the error:
Object [rf_unlocode]; ChangeName [n/a]; Type [STATICDATA]; LogicalSchema [public]
Root Exception Message: PSQLException: ERROR: insert or update on table "rf_unlocode" violates foreign key constraint "rf_unlocode_countryid_fkey"
Detail: Key (countryid)=(BG) is not present in table "rf_country".

However, If i check that ID is present in the DB and on the second deploy run rf_unlocode data correctly uploads.

Have I missed something? (I have several tables that have multiple levels of relationship dependencies and currently it takes about 3 runs to load all the data.)

[shantstepanian]

Apologies, I misspoke on my earlier response.

The FK annotation needs to be on the foreign key change, not the primary key change. The correct usage is below:

//// CHANGE FK name=constraint1
ALTER TABLE ONLY public.rf_unlocode
ADD CONSTRAINT rf_unlocode_countryid_fkey FOREIGN KEY (countryid) REFERENCES public.rf_country(countryid);
GO

If you want to see a code example, see here - TABLE_A defines the FK in there. Test description is here

[pixie79]

Thanks, That fixed it. With the exception of the ones which refer to the table that has self referencing parent keys in.

[shantstepanian]

Sounds good. Will check out the self-referencing keys within a couple weeks

[shantstepanian]

Hi, the issue w/ data loads on self-referencing foreign keys has been fixed in the latest snapshot (same link as posted in the other tickets): https://www.dropbox.com/s/tjzpi4ktp89njy8/obevo-cli-6.6.2-SNAPSHOT-dist.zip?dl=0

Please give it a try and let us know how it goes

[pixie79]

Latest link works :)