5 trim some more noise. remove columns irrelevant to the question
source | link

I came up with a view and trigger based solution to modify all objects which rely onrows in multiple relational databaseunderlying tables in a Postgres 9.5.3 database. This works reasonable using Qt's model view programming. The next task I have to solve is mapping n:m relations which refer to the mentioned objects.

Example:

Article: id, name
Tag: id, name

-> n:m surrogate key table: article_ref, tag_ref

The view is populated with every relevant column of the main table (Article in the example) and another column, which contains all surrogate keys as array. Here I take advantage of PostgreSQLs array_agg(<column>) AS <alias> function which does the whole job.

So whenever I edit any view row I have to handle the array which resolves into the surrogate key table.

Required Operation

I was looking for a kind of array subtraction which can be explained by the following examples.

{1,2,4} - {1,2,3} = {4}
{1,2,3} - {1,2,4} = {3}

Tables

The used tables are created the following way:

CREATE TABLE core.art (
  id bigserial NOT NULL PRIMARY KEY,
  name character varying(40),
  unit_ref bigint,
  prize_per_unit real, -- adjusted to NUMERIC the next iteration ;)
  tax_rate_ref bigint,
  useable boolean NOT NULL DEFAULT false,
  category_ref bigint NOT NULL,
  CONSTRAINT art_category_fk FOREIGN KEY (category_ref) REFERENCES core.cat (id),
  CONSTRAINT art_tax_rate_fk FOREIGN KEY (tax_rate_ref) REFERENCES core.tax_ratemore (id)columns,
  CONSTRAINT art_unit_fk     FOREIGN KEY (unit_ref)    irrelevant REFERENCESto core.unitthe (id)problem
);

CREATE TABLE core.tag (
  id bigserial NOT NULL PRIMARY KEY,
  name character varying(40) NOT NULL UNIQUE,
  useable boolean NOT NULL DEFAULT false
);

CREATE TABLE core.art_tag (
  article_ref bigint NOT NULL,
  tag_ref bigint NOT NULL,
  CONSTRAINT art_tag_pk PRIMARY KEY (article_ref, tag_ref),
  CONSTRAINT art_tag_article_fk FOREIGN KEY (article_ref)
      REFERENCES core.art (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT art_tag_tag_fk FOREIGN KEY (tag_ref)
      REFERENCES core.tag (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
);

The view is populated with every relevant column of the main table art (articles) and another column with all tag IDs as array - aggregated with array_agg(<column>) AS <alias>.

Whenever I edit a view row I have to handle the array which resolves to the surrogate key table.

Required Operation

I was looking for a kind of array subtraction which can be explained by the following examples.

{1,2,4} - {1,2,3} = {4}
{1,2,3} - {1,2,4} = {3}

resulting in:

Will that just work or is it even an appropriate solution to the task? Is there any other, maybe better way to achieve this? (Concurrency should be assured.)

PostgreSQL Version: 9.5.3 (or higher)

I came up with a view and trigger based solution to modify all objects which rely on multiple relational database tables. This works reasonable using Qt's model view programming. The next task I have to solve is mapping n:m relations which refer to the mentioned objects.

Example:

Article: id, name
Tag: id, name

-> n:m surrogate key table: article_ref, tag_ref

The view is populated with every relevant column of the main table (Article in the example) and another column, which contains all surrogate keys as array. Here I take advantage of PostgreSQLs array_agg(<column>) AS <alias> function which does the whole job.

So whenever I edit any view row I have to handle the array which resolves into the surrogate key table.

Required Operation

I was looking for a kind of array subtraction which can be explained by the following examples.

{1,2,4} - {1,2,3} = {4}
{1,2,3} - {1,2,4} = {3}

Tables

The used tables are created the following way:

CREATE TABLE core.art (
  id bigserial NOT NULL PRIMARY KEY,
  name character varying(40),
  unit_ref bigint,
  prize_per_unit real, -- adjusted to NUMERIC the next iteration ;)
  tax_rate_ref bigint,
  useable boolean NOT NULL DEFAULT false,
  category_ref bigint NOT NULL,
  CONSTRAINT art_category_fk FOREIGN KEY (category_ref) REFERENCES core.cat (id),
  CONSTRAINT art_tax_rate_fk FOREIGN KEY (tax_rate_ref) REFERENCES core.tax_rate (id),
  CONSTRAINT art_unit_fk     FOREIGN KEY (unit_ref)     REFERENCES core.unit (id)
);

CREATE TABLE core.tag (
  id bigserial NOT NULL PRIMARY KEY,
  name character varying(40) NOT NULL UNIQUE,
  useable boolean NOT NULL DEFAULT false
);

CREATE TABLE core.art_tag (
  article_ref bigint NOT NULL,
  tag_ref bigint NOT NULL,
  CONSTRAINT art_tag_pk PRIMARY KEY (article_ref, tag_ref),
  CONSTRAINT art_tag_article_fk FOREIGN KEY (article_ref)
      REFERENCES core.art (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT art_tag_tag_fk FOREIGN KEY (tag_ref)
      REFERENCES core.tag (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
);

resulting in

Will that just work or is it even an appropriate solution to the task? Is there any other, maybe better way to achieve this? (Concurrency should be assured.)

PostgreSQL Version: 9.5.3 (or higher)

I came up with a view and trigger based solution to modify rows in multiple underlying tables in a Postgres 9.5.3 database. This works reasonable using Qt's model view programming. The next task I have to solve is mapping n:m relations which refer to the mentioned objects.

Tables

CREATE TABLE art (
  id bigserial NOT NULL PRIMARY KEY,
  name character varying(40),
  -- more columns, irrelevant to the problem
);

CREATE TABLE tag (
  id bigserial NOT NULL PRIMARY KEY,
  name character varying(40) NOT NULL UNIQUE,
  useable boolean NOT NULL DEFAULT false
);

CREATE TABLE art_tag (
  article_ref bigint NOT NULL,
  tag_ref bigint NOT NULL,
  CONSTRAINT art_tag_pk PRIMARY KEY (article_ref, tag_ref),
  CONSTRAINT art_tag_article_fk FOREIGN KEY (article_ref)
      REFERENCES art (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT art_tag_tag_fk FOREIGN KEY (tag_ref)
      REFERENCES tag (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
);

The view is populated with every relevant column of the main table art (articles) and another column with all tag IDs as array - aggregated with array_agg(<column>) AS <alias>.

Whenever I edit a view row I have to handle the array which resolves to the surrogate key table.

Required Operation

I was looking for a kind of array subtraction which can be explained by the following examples.

{1,2,4} - {1,2,3} = {4}
{1,2,3} - {1,2,4} = {3}

resulting in:

Will that just work or is it even an appropriate solution to the task? Is there any other, maybe better way to achieve this? (Concurrency should be assured.)

4 trim some noise
source | link

I was looking for a kind of array substractionsubtraction which can be explained by the following examples.

CREATE TABLE core.art
  (
  id bigserial NOT NULL PRIMARY KEY,
  name character varying(40),
  unit_ref bigint,
  prize_per_unit real, -- adjusted to NUMERIC the next iteration ;)
  tax_rate_ref bigint,
  useable boolean NOT NULL DEFAULT false,
  category_ref bigint NOT NULL,
  CONSTRAINT art_pk PRIMARY KEY (id),
  CONSTRAINT art_category_fk FOREIGN KEY (category_ref)
      REFERENCES core.cat (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT art_tax_rate_fk FOREIGN KEY (tax_rate_ref)
      REFERENCES core.tax_rate (id) MATCH SIMPLE,
      ON UPDATE NO ACTION ON DELETECONSTRAINT NOart_unit_fk ACTION,
  CONSTRAINT art_unit_fk FOREIGN KEY (unit_ref)
      REFERENCES core.unit (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

CREATE TABLE core.tag
  (
  id bigserial NOT NULL PRIMARY KEY,
  name character varying(40) NOT NULL UNIQUE,
  useable boolean NOT NULL DEFAULT false,
  CONSTRAINT tag_pk PRIMARY KEY (id),
  CONSTRAINT tag_name_uc UNIQUE (name)
)
WITH (
  OIDS=FALSE
);

CREATE TABLE core.art_tag
  (
  article_ref bigint NOT NULL,
  tag_ref bigint NOT NULL,
  CONSTRAINT art_tag_pk PRIMARY KEY (article_ref, tag_ref),
  CONSTRAINT art_tag_article_fk FOREIGN KEY (article_ref)
      REFERENCES core.art (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT art_tag_tag_fk FOREIGN KEY (tag_ref)
      REFERENCES core.tag (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);

This code could be used inside the triggers (remove the clutter beforhandbeforehand) to calculate all elements which have to be deleted from or inserted into the surrogate key table.

I was looking for a kind of array substraction which can be explained by the following examples.

CREATE TABLE core.art
 (
  id bigserial NOT NULL,
  name character varying(40),
  unit_ref bigint,
  prize_per_unit real, -- adjusted to NUMERIC the next iteration ;)
  tax_rate_ref bigint,
  useable boolean NOT NULL DEFAULT false,
  category_ref bigint NOT NULL,
  CONSTRAINT art_pk PRIMARY KEY (id),
  CONSTRAINT art_category_fk FOREIGN KEY (category_ref)
      REFERENCES core.cat (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT art_tax_rate_fk FOREIGN KEY (tax_rate_ref)
      REFERENCES core.tax_rate (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT art_unit_fk FOREIGN KEY (unit_ref)
      REFERENCES core.unit (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

CREATE TABLE core.tag
 (
  id bigserial NOT NULL,
  name character varying(40) NOT NULL,
  useable boolean NOT NULL DEFAULT false,
  CONSTRAINT tag_pk PRIMARY KEY (id),
  CONSTRAINT tag_name_uc UNIQUE (name)
)
WITH (
  OIDS=FALSE
);

CREATE TABLE core.art_tag
 (
  article_ref bigint NOT NULL,
  tag_ref bigint NOT NULL,
  CONSTRAINT art_tag_pk PRIMARY KEY (article_ref, tag_ref),
  CONSTRAINT art_tag_article_fk FOREIGN KEY (article_ref)
      REFERENCES core.art (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT art_tag_tag_fk FOREIGN KEY (tag_ref)
      REFERENCES core.tag (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);

This code could be used inside the triggers (remove the clutter beforhand) to calculate all elements which have to be deleted from or inserted into the surrogate key table.

I was looking for a kind of array subtraction which can be explained by the following examples.

CREATE TABLE core.art (
  id bigserial NOT NULL PRIMARY KEY,
  name character varying(40),
  unit_ref bigint,
  prize_per_unit real, -- adjusted to NUMERIC the next iteration ;)
  tax_rate_ref bigint,
  useable boolean NOT NULL DEFAULT false,
  category_ref bigint NOT NULL,
  CONSTRAINT art_category_fk FOREIGN KEY (category_ref) REFERENCES core.cat (id),
  CONSTRAINT art_tax_rate_fk FOREIGN KEY (tax_rate_ref) REFERENCES core.tax_rate (id),
  CONSTRAINT art_unit_fk     FOREIGN KEY (unit_ref)     REFERENCES core.unit (id)
);

CREATE TABLE core.tag (
  id bigserial NOT NULL PRIMARY KEY,
  name character varying(40) NOT NULL UNIQUE,
  useable boolean NOT NULL DEFAULT false
);

CREATE TABLE core.art_tag (
  article_ref bigint NOT NULL,
  tag_ref bigint NOT NULL,
  CONSTRAINT art_tag_pk PRIMARY KEY (article_ref, tag_ref),
  CONSTRAINT art_tag_article_fk FOREIGN KEY (article_ref)
      REFERENCES core.art (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT art_tag_tag_fk FOREIGN KEY (tag_ref)
      REFERENCES core.tag (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
);

This code could be used inside the triggers (remove the clutter beforehand) to calculate all elements which have to be deleted from or inserted into the surrogate key table.

3 more information added (sql...)
source | link

Tables

The used tables are created the following way:

CREATE TABLE core.art
(
  id bigserial NOT NULL,
  name character varying(40),
  unit_ref bigint,
  prize_per_unit real, -- adjusted to NUMERIC the next iteration ;)
  tax_rate_ref bigint,
  useable boolean NOT NULL DEFAULT false,
  category_ref bigint NOT NULL,
  CONSTRAINT art_pk PRIMARY KEY (id),
  CONSTRAINT art_category_fk FOREIGN KEY (category_ref)
      REFERENCES core.cat (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT art_tax_rate_fk FOREIGN KEY (tax_rate_ref)
      REFERENCES core.tax_rate (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT art_unit_fk FOREIGN KEY (unit_ref)
      REFERENCES core.unit (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

CREATE TABLE core.tag
(
  id bigserial NOT NULL,
  name character varying(40) NOT NULL,
  useable boolean NOT NULL DEFAULT false,
  CONSTRAINT tag_pk PRIMARY KEY (id),
  CONSTRAINT tag_name_uc UNIQUE (name)
)
WITH (
  OIDS=FALSE
);

CREATE TABLE core.art_tag
(
  article_ref bigint NOT NULL,
  tag_ref bigint NOT NULL,
  CONSTRAINT art_tag_pk PRIMARY KEY (article_ref, tag_ref),
  CONSTRAINT art_tag_article_fk FOREIGN KEY (article_ref)
      REFERENCES core.art (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT art_tag_tag_fk FOREIGN KEY (tag_ref)
      REFERENCES core.tag (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);

Solution

Will that just work or is it even an appropriate solution to the task? Is there any other, maybe better way to achieve this? (Concurrency should be assured.)

PostgreSQL Version: 9.5.3 (or higher)

Solution

Will that just work or is it even an appropriate solution to the task? Is there any other, maybe better way to achieve this?

Tables

The used tables are created the following way:

CREATE TABLE core.art
(
  id bigserial NOT NULL,
  name character varying(40),
  unit_ref bigint,
  prize_per_unit real, -- adjusted to NUMERIC the next iteration ;)
  tax_rate_ref bigint,
  useable boolean NOT NULL DEFAULT false,
  category_ref bigint NOT NULL,
  CONSTRAINT art_pk PRIMARY KEY (id),
  CONSTRAINT art_category_fk FOREIGN KEY (category_ref)
      REFERENCES core.cat (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT art_tax_rate_fk FOREIGN KEY (tax_rate_ref)
      REFERENCES core.tax_rate (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT art_unit_fk FOREIGN KEY (unit_ref)
      REFERENCES core.unit (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

CREATE TABLE core.tag
(
  id bigserial NOT NULL,
  name character varying(40) NOT NULL,
  useable boolean NOT NULL DEFAULT false,
  CONSTRAINT tag_pk PRIMARY KEY (id),
  CONSTRAINT tag_name_uc UNIQUE (name)
)
WITH (
  OIDS=FALSE
);

CREATE TABLE core.art_tag
(
  article_ref bigint NOT NULL,
  tag_ref bigint NOT NULL,
  CONSTRAINT art_tag_pk PRIMARY KEY (article_ref, tag_ref),
  CONSTRAINT art_tag_article_fk FOREIGN KEY (article_ref)
      REFERENCES core.art (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT art_tag_tag_fk FOREIGN KEY (tag_ref)
      REFERENCES core.tag (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);

Solution

Will that just work or is it even an appropriate solution to the task? Is there any other, maybe better way to achieve this? (Concurrency should be assured.)

PostgreSQL Version: 9.5.3 (or higher)

2 tags, minor edits
source | link
1
source | link