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íčů.
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.
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á.
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.
SQL Developer
Přečteno 32 106×
Přečteno 19 604×
Přečteno 19 549×
Přečteno 17 401×
Přečteno 16 292×