PostgreSQL: XML co s ním 2. část - refereční integrita

Jak převést XML do reálného relačního světa ?  Podstatou jsou tabulky a relace, které existují hlavně formou cizí klíčů.

View

Jednou z lehkých cest je vytvořit view.

V příkladu jsem vytvořil view na příkladem z 1. části. Nad view můžeme použít klasický příkaz select jako nad tabulkou.

create view v_weather_day as
select
place,
meteo_date +(array_to_string(xpath('/hourly/time/text()'::text, radek),',')::text||' hours')::interval as hour,
array_to_string(xpath('/hourly/temp/text()'::text, radek),',')::integer as zkr,
array_to_string(xpath('/hourly/feels/text()'::text, radek),',')::integer as feels,
array_to_string(xpath('/hourly/humidity/text()'::text, radek),',')::integer as humidity
from
(select unnest(xpath('/meteo/hourly'::text,meteo_vals)) as radek,meteo_date,place
 from weather_day ) a;


select * from v_weather_day; 

Nevýhoda tohoto řešení je v tom, že nemůžeme na něj pověsit referenční integritu, protože to není tabulka.

Materializované view

Dalším způsobem je vytvořit materializované view. Oproti view umožňuje mít jedinečný index nad kandidátem na cizí klíč, sloupcem „zkr“.

-- drop materialized view mv_cas

 create materialized view mv_cas as
select
array_to_string(xpath('/jednotka/nazev/text()'::text, radek),',') as nazev,
array_to_string(xpath('/jednotka/zkr/text()'::text, radek),',')::char(3) as zkr,
array_to_string(xpath('/jednotka/koeficient/text()'::text, radek),',')::numeric as koeficient
from
(select unnest(xpath('/cas/jednotka'::text,hodnota)) as radek
 from jednotka where typ = 'cas') a;


 select * from mv_cas;

 create unique index uk_mv_cas on mv_cas (zkr);

 -- Zkušební tabulka pro cizí klíč
 create table mereni
 (id serial primary key,
  object text,
  jednotka char(3));

alter table mereni
add constraint fk_mereni_mv_cas foreign key (jednotka)
references mv_cas(zkr);

/*
ERROR:  referenced relation "mv_cas" is not a table

********** Chyba **********

ERROR: referenced relation "mv_cas" is not a table
Stav SQL: 42809
*/ 

Neuspěli jsme ani tady. Cizí klíč nám PostgreSQL neumožní udělat. Možná v budoucnu. Řešením je použít trigger, který si pohlídá referenční integritu.

CREATE OR REPLACE FUNCTION f_trg_mereni()
  RETURNS trigger AS
$BODY$
DECLARE
   lkol smallint;
BEGIN
  select count(*) into lkol
    from mv_cas
   where zkr = new.jednotka;

   if (lkol = 1) then
      return new;
    end if;
    RAISE EXCEPTION 'Value "%" not exists in materialized view mv_cas.', new.jednotka;
    RETURN NULL;
 END
 $BODY$
  LANGUAGE plpgsql;

CREATE TRIGGER trg_s_mereni
  AFTER INSERT OR UPDATE
  ON mereni
  FOR EACH ROW
  EXECUTE PROCEDURE f_trg_mereni();

-- Dobře vložená data
insert into mereni (object,jednotka)
values
('a','s'),
('b','sol');

--Špatně vložená data
insert into mereni (object,jednotka)
values
('c','kil');

/*
ERROR:  Value "kil" not exists in materialized view mv_cas.
********** Chyba **********

ERROR: Value "kil" not exists in materialized view mv_cas.
Stav SQL: P0001
*/ 

Vidíte, že toto řešení funguje. Nevýhodovou je, že materializované view musíme obnovovat (refresh materialized view mv_cas;) a refereční integrita není na první pohled zjevná.

Lokálny sychronizovaný číselník

Tento způsob počítá s vytvořením lokálního číselníku,  který bude mít cizí klíč nad tabulkou „mereni“. Samotná integrita dat se hlídá pomocí triggeru.

CREATE OR REPLACE VIEW public.v_cas AS 
 SELECT array_to_string(xpath('/jednotka/nazev/text()'::text, a.radek), ','::text) AS nazev,
    array_to_string(xpath('/jednotka/zkr/text()'::text, a.radek), ','::text) AS zkr,
    array_to_string(xpath('/jednotka/koeficient/text()'::text, a.radek), ','::text)::numeric AS koeficient
   FROM ( SELECT unnest(xpath('/cas/jednotka'::text, jednotka.hodnota)) AS radek
           FROM jednotka
          WHERE jednotka.typ::text = 'cas'::text) a; create table cas
(zkr char(3) primary key,
nazev text not null unique,
koeficient numeric not null);

insert into cas
select
array_to_string(xpath('/jednotka/zkr/text()'::text, radek),',') as zkr,
array_to_string(xpath('/jednotka/nazev/text()'::text, radek),',') as nazev,
array_to_string(xpath('/jednotka/koeficient/text()'::text, radek),',')::numeric as koeficient
from
(select unnest(xpath('/cas/jednotka'::text,hodnota)) as radek
 from jednotka where typ = 'cas') a;

 create table mereni2
 (id serial primary key,
  object text,
  jednotka char(3));

alter table mereni2
add constraint fk_mereni_cas foreign key (jednotka)
references cas(zkr);



CREATE OR REPLACE FUNCTION f_trg_jednotka() RETURNS TRIGGER AS $$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            IF (OLD.typ = 'cas') THEN
        RAISE EXCEPTION 'neni mozno zrusit radek s typem "cas" protoze na nem zavisi tabulka cas.';
        RETURN NULL;
            END IF;
            RETURN OLD;
        ELSEIF (TG_OP = 'UPDATE') THEN
            IF (OLD.typ = 'cas' and NEW.typ != 'cas') THEN
        RAISE EXCEPTION 'neni mozno aktualizovat radek s typem "cas" protoze na nem zavisi tabulka cas.';
        RETURN NULL;
            END IF;
        END IF;
        DELETE FROM cas
        WHERE not exists (select 1 from v_cas where cas.zkr = v_cas.zkr);

        UPDATE cas
        set (zkr,nazev,koeficient) = (t.zkr,t.nazev,t.koeficient)
        from ( select zkr,nazev,koeficient
        from  v_cas
        where exists (select 1 from cas where cas.zkr = v_cas.zkr)) t
        where t.zkr = cas.zkr;

        insert into cas
            (zkr,nazev,koeficient)
        select zkr,nazev,koeficient
           from v_cas
           where not exists (select 1 from cas where cas.zkr = v_cas.zkr);

        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER trg_s_jednotka
  AFTER INSERT OR UPDATE
  ON jednotka
  FOR EACH ROW
  EXECUTE PROCEDURE f_trg_jednotka();


select * from cas;

/*
 zkr |         nazev          | koeficient
-----+------------------------+------------
 s   | sekunda                |          1
 min | minuta                 |         60
 d   | den (střední slunečný) |      86400
 sol | sol (střední Mars)     |      88775
 */

 update jednotka
set hodnota =
 '<cas>
   <jednotka>
    <nazev>sekunda</nazev><zkr>s</zkr><koeficient>1</koeficient>
   </jednotka>
   <jednotka>
    <nazev>minuta</nazev><zkr>min</zkr><koeficient>60</koeficient>
   </jednotka>
   <jednotka>
    <nazev>hodina</nazev><zkr>h</zkr><koeficient>3600</koeficient>
   </jednotka>
   <jednotka>
    <nazev>sol (střední Mars)</nazev><zkr>sol</zkr><koeficient>88775</koeficient>
   </jednotka>
  </cas>':: XML
where typ = 'cas';

/*
 zkr |       nazev        | koeficient
-----+--------------------+------------
 s   | sekunda            |          1
 min | minuta             |         60
 sol | sol (střední Mars) |      88775
 h   | hodina             |       3600
 */

 insert into mereni2 (object,jednotka)
values
('a','s'),
('b','sol');

 insert into mereni2 (object,jednotka)
values
('c','d');

/*
ERROR:  insert or update on table "mereni2" violates foreign key constraint "fk_mereni_cas"
DETAIL:  Key (jednotka)=(d  ) is not present in table "cas".

********** Chyba **********

ERROR: insert or update on table "mereni2" violates foreign key constraint "fk_mereni_cas"
Stav SQL: 23503
Podrobnosti:Key (jednotka)=(d  ) is not present in table "cas".
*/

-- Pokus zrsuit sekundu
 update jednotka
set hodnota =
 '<cas>
   <jednotka>
    <nazev>minuta</nazev><zkr>min</zkr><koeficient>60</koeficient>
   </jednotka>
   <jednotka>
    <nazev>hodina</nazev><zkr>h</zkr><koeficient>3600</koeficient>
   </jednotka>
   <jednotka>
    <nazev>sol (střední Mars)</nazev><zkr>sol</zkr><koeficient>88775</koeficient>
   </jednotka>
  </cas>':: XML
where typ = 'cas';

/*
ERROR:  update or delete on table "cas" violates foreign key constraint "fk_mereni_cas" on table "mereni2"
DETAIL:  Key (zkr)=(s  ) is still referenced from table "mereni2".
CONTEXT:  SQL statement "DELETE FROM cas
        WHERE not exists (select 1 from v_cas where cas.zkr = v_cas.zkr)"
PL/pgSQL funkce f_trg_jednotka() řádek 15 na SQL příkaz

********** Chyba **********

ERROR: update or delete on table "cas" violates foreign key constraint "fk_mereni_cas" on table "mereni2"
Stav SQL: 23503
Podrobnosti:Key (zkr)=(s  ) is still referenced from table "mereni2".
Kontext:SQL statement "DELETE FROM cas
        WHERE not exists (select 1 from v_cas where cas.zkr = v_cas.zkr)"
PL/pgSQL funkce f_trg_jednotka() řádek 15 na SQL příkaz
*/

Ačkoliv toto rešení naplňuje všechny atributy dobrého řešení, je dost komplikované na realizaci.