ORM - objektovo relační modelování v Postgresql

ORM – objektovo relační modelování v Postgresql

Motivace.

Než jsem začal používat PostgreSQL, žil jsem v prostředí RDBMS čistě relačních databází. S přechodem na PostgreSQL se situace změnila. Zkratka ORM se ve softwarovém inženýrství používá ve dvou souvislostech :

Object-role modeling – Objektové modelování. Tedy takové uskupení dat, kde data jsou zapouzdřena v objektech.

Object-relational mapping – Je technika, která umožňuje konverzi data mezi objektovým programem a relačním databázovým světem.

V tomto mém příspěvku se budeme zabývat výhradně ORM jako objektovým modelováním.

Jak na ORM ?

(Priklad je v PostgreSQL 9.3 !)

Základem ORM je objekt jako takový. A protože nemá rád dlouhé teoretické plácaní pojďme hned na věc:

1. Máme objekt Osoba a Kontakty. Prvoplánově :

1.a. Vytvoříme tabulku kontaktů jako entitu samu o sobě :

create table kontakt
(id serial primary key,
popis text);

1.b. Vytvoříme tabulku Osob, které obsahuje kontakty :

create table osoba
( id serial primary key,
jmeno varchar(60),
prijmeni varchar(60),
kontakty kontakt);

P.S. Všimněte si, že jsem vytvořil tabulku osoba, která v sobě mimo jiné obsahuje tabulku typ kontakt.

1.c Vložení dat do tabulky Osob

insert into osoba
(jmeno,prijmeni,kontakty)
values
('Michal','Salko',(1,'telefon: 0123456789'));

No a co je vidět :

select * from osoba;

1;"Michal";"Salko";"(1,"telefon: 0123456789")"

select * from kontakt;

Výsledek nevrací žádné řádky.

Vysvětlení proč:

To co jsme vytvořili v tabulce Osoba není tabulka, které obsahuje jako další datový typ tabulku, ale tabulka, která obsahuje compositní typ kontakt odvozený z tabulky Kontakt.

Co v případě, že compositní typ změníme přidáním dalšího sloupce ?

alter table kontakt
add column typ_kontaktu varchar(60);

select * from osoba;

1;„Michal“;„Salko“;„(1,"telefon: 0123456789“,)"

přibyla tam čárka. To znamená, že se změnil datový typ kontakt i v tabulce. Pak vše můžeme opravit :

update osoba
set kontakty = (1,'0123456789','telefon')
where id = 1;

Takže po kontrole : select * from osoba;
1;„Michal“;„Salko“;„(1,0123456789,te­lefon)“

Aha. Najdou se i skeptici. Tak to zkusme např s jinou strukutrou kontaktů (špatnou) :

update osoba
set kontakty = (1,'0123456789','telefon','soukrom.')
where id = 1;

Výsledek :

ERROR: cannot cast type record to kontakt
LINE 2: set kontakty = (1,'0123456789','telefon','soukrom.')
^
DETAIL: Input has too many columns.
********** Chyba **********

ERROR: cannot cast type record to kontakt
Stav SQL: 42846
Podrobnosti:Input has too many columns.
Znak: 33

Námitka: Ale, to není příliš objektové. Potřebujeme vazby jeden člověk, více kontaktů. OK zkusme toto :

drop table osoba;

create table osoba
( id serial primary key,
jmeno varchar(60),
prijmeni varchar(60),
kontakty kontakt[]);

kde kontakt[] je pole typu kontakt.

insert into osoba
values
(1,'Michal','Salko','{"(1,0123456789,telefon)",
"(2,xx@xxxx.xx,email)","(3,xxx. xxx . 548547 Avaaa,adresa)"}');

select * from osoba

1;"Michal";"Salko";"{"(1,0123456789,telefon)",
"(2,xx@xxxx.xx,email)","(3,\"xxx. xxx . 548547 Avaaa\",adresa)"}"

Což je super, ale bylo by potřeba mít výsledek v relačním tvaru (sloupcich). K tomu vyborně poslouží trochu zmodifikovaná funkce od Pavla Snehuleho (pro PostgreSQL 9.1)
Zdroj: http://postgres.cz/wiki/Pos­tgreSQL_SQL_Tricks

CREATE OR REPLACE FUNCTION unnest(anyarray)
RETURNS TABLE (element anyelement) AS $$
BEGIN
    FOREACH element IN array $1
    LOOP
       RETURN NEXT;
    END LOOP;
    RETURN;
END
$$ LANGUAGE plpgsql;

Pomoci teto funkce je pak výsledek :

select unnest(kontakty) from osoba

"(1,0123456789,telefon)"
"(2,xx@xxxx.xx,email)"
"(3,"xxx. xxx . 548547 Avaaa",adresa)"

Kdyz tam vložíme jěště další osobu

insert into osoba
values
(2,'Lachim','Oklas','{"(1,987654321,telefon)",
"(2,yy@xxxx.xx,email)","(3,yyy. xxx . 548547 Avaaa,adresa)"}');

a uděláme select

select prijmeni, unnest(kontakty) from osoba

pak vysledek je asi tento

Prijmeni; unnest
------------------------------------------------
"Salko";"(1,0123456789,telefon)"
"Salko";"(2,xx@xxxx.xx,email)"
"Salko";"(3,"xxx. xxx . 548547 Avaaa",adresa)"
"Oklas";"(1,987654321,telefon)"
"Oklas";"(2,yy@xxxx.xx,email)"
"Oklas";"(3,"yyy. xxx . 548547 Avaaa",adresa)"

Ale ani s timto moc pracovat nelze. Chteli bychom vytáhnout všechny emaily pro dané osoby. Lze to udělat takto :

1. Provedeme select

select prijmeni, (unnest(kontakty)).popis from osoba2

prijmeni; popis
--------------------------
"Salko";"0123456789"
"Salko";"xx@xxxx.xx"
"Salko";"xxx. xxx . 548547 Avaaa"
"Oklas";"987654321"
"Oklas";"yy@xxxx.xx"
"Oklas";"yyy. xxx . 548547 Avaaa"

2. Abychom to mohli udělat musime vytvořit množinu typových záznamů (record) se kterou mužeme manipulovat.

select prijmeni,(kontakt).popis
  from (select prijmeni, (unnest(kontakty)) as kontakt from osoba2) as osoba
         where (kontakt).typ_kontaktu = 'email';

Požadovaný výsledek :

prijmeni;popis
---------------------------
"Salko";"xx@xxxx.xx"
"Oklas";"yy@xxxx.xx"

Odpovidá tomu, co jsme potřebovali.

Tak tolik k ORM s PostgreSQL. :)