SQL

PostgreSQL in kvazi dedovanje

sobota, 31. marec 2007

Nekaj dni nazaj sem pisal o dedovanju tabel v PostgreSQL in problemih na katere sem naletel. Našel sem rešitev, ki sicer ni tako elegantna kot native table inheritance, deluje pa vseeno zelo lepo.

Začeli bomo tako, kot je predlagal David v komentarjih na članek o dedovanju tabel:

CREATE TABLE artikli (
id serial PRIMARY KEY,
ime text,
opis text,
cena money
);
CREATE TABLE _artikli_vrsta_1 (
id integer PRIMARY KEY
REFERENCES artikli (id) ON DELETE CASCADE,
lastnost_1_1 text,
lastnost_1_2 text
);
CREATE TABLE _artikli_vrsta_2 (
id integer PRIMARY KEY
REFERENCES artikli (id) ON DELETE CASCADE,
lastnost_2_1 integer,
lastnost_2_2 bool
);
CREATE TABLE _artikli_vrsta_3 (
id integer PRIMARY KEY
REFERENCES artikli (id) ON DELETE CASCADE,
lastnost_3_1 text,
lastnost_3_2 text,
lastnost_3_3 integer,
lastnost_3_4 date
);

Zakaj sem pred imena tabel dodal underscore znak? Ker bomo kreirali viewje z imeni brez tega znaka, nad katerimi bomo izvajali INSERT in UPDATE operacije, na ta način bomo dosegli obnašanje, ki je podobno dedovanu tabel.

CREATE VIEW artikli_vrsta_1 AS
SELECT
artikli.id, artikli.ime, artikli.opis, artikli.cena,
_artikli_vrsta_1.lastnost_1_1, _artikli_vrsta_1.lastnost_1_2
FROM
artikli,
_artikli_vrsta_1
WHERE
_artikli_vrsta_1.id = artikli.id;
CREATE VIEW artikli_vrsta_2 AS
SELECT
artikli.id, artikli.ime, artikli.opis, artikli.cena,
_artikli_vrsta_2.lastnost_2_1, _artikli_vrsta_2.lastnost_2_2
FROM
artikli,
_artikli_vrsta_2
WHERE
_artikli_vrsta_2.id = artikli.id;
CREATE VIEW artikli_vrsta_3 AS
SELECT
artikli.id, artikli.ime, artikli.opis, artikli.cena,
_artikli_vrsta_3.lastnost_3_1, _artikli_vrsta_3.lastnost_3_2,
_artikli_vrsta_3.lastnost_3_3, _artikli_vrsta_3.lastnost_3_4
FROM
artikli,
_artikli_vrsta_3
WHERE
_artikli_vrsta_3.id = artikli.id;

Ampak samo malo, saj ne moremo izvajati INSERT in UPDATE operacij nad viewji, ali pač? Neposredno seveda ne, lahko pa to naredimo z majhnim trikom. Na viewjih bomo definirali INSERT in UPDATE rules, ki bodo skrbeli, da se osvežijo podatki v pravi tabeli. Pa dajmo, najprej INSERT:

CREATE RULE artikli_vrsta_1_insert
AS ON INSERT TO artikli_vrsta_1
DO INSTEAD (
INSERT
INTO artikli (id, ime, opis, cena)
VALUES (DEFAULT, new.ime, new.opis, new.cena);
INSERT
INTO _artikli_vrsta_1 (
id,
lastnost_1_1, lastnost_1_2
)
VALUES (
currval('artikli_id_seq'),
new.lastnost_1_1, new.lastnost_1_2
);
);
CREATE RULE artikli_vrsta_2_insert
AS ON INSERT TO artikli_vrsta_2
DO INSTEAD (
INSERT
INTO artikli (id, ime, opis, cena)
VALUES (DEFAULT, new.ime, new.opis, new.cena);
INSERT
INTO _artikli_vrsta_2 (
id,
lastnost_2_1, lastnost_2_2
)
VALUES (
currval('artikli_id_seq'),
new.lastnost_2_1, new.lastnost_2_2
);
);
CREATE RULE artikli_vrsta_3_insert
AS ON INSERT TO artikli_vrsta_3
DO INSTEAD (
INSERT
INTO artikli (id, ime, opis, cena)
VALUES (DEFAULT, new.ime, new.opis, new.cena);
INSERT
INTO _artikli_vrsta_3 (
id,
lastnost_3_1, lastnost_3_2,
lastnost_3_3, lastnost_3_4
)
VALUES (
currval('artikli_id_seq'),
new.lastnost_3_1, new.lastnost_3_2,
new.lastnost_3_3, new.lastnost_3_4
);
);

Nato še UPDATE:

CREATE RULE artikli_vrsta_1_update
AS ON UPDATE TO artikli_vrsta_1
DO INSTEAD (
UPDATE artikli
SET
ime = new.ime,
opis = new.opis,
cena = new.cena
WHERE
id = old.id;
UPDATE _artikli_vrsta_1
SET
lastnost_1_1 = new.lastnost_1_1,
lastnost_1_2 = new.lastnost_1_2
WHERE
id = old.id;
);
CREATE RULE artikli_vrsta_2_update
AS ON UPDATE TO artikli_vrsta_2
DO INSTEAD (
UPDATE artikli
SET
ime = new.ime,
opis = new.opis,
cena = new.cena
WHERE
id = old.id;
UPDATE _artikli_vrsta_2
SET
lastnost_2_1 = new.lastnost_2_1,
lastnost_2_2 = new.lastnost_2_2
WHERE
id = old.id;
);
CREATE RULE artikli_vrsta_3_update
AS ON UPDATE TO artikli_vrsta_3
DO INSTEAD (
UPDATE artikli
SET
ime = new.ime,
opis = new.opis,
cena = new.cena
WHERE
id = old.id;
UPDATE _artikli_vrsta_3
SET
lastnost_3_1 = new.lastnost_3_1,
lastnost_3_2 = new.lastnost_3_2,
lastnost_3_3 = new.lastnost_3_3,
lastnost_3_4 = new.lastnost_3_4
WHERE
id = old.id;
);

No, vse skupaj se sedaj obnaša zelo podobno, kot bi se v primeru uporabe dedovanja tabel. Pa smo s tem rešili tudi moj problem iz prejšnjega članka? Seveda smo, v tabeli artikli se sedaj nahajajo vsi zapisi in lahko na ID polje v tej tabeli referenciramo tudi kakšen FOREIGN KEY. Poskusimo primer iz prejšnjega članka:

CREATE TABLE artikli_dodatne_lastnosti (
id serial PRIMARY KEY,
artikel_id integer
REFERENCES artikli (id) ON DELETE CASCADE,
lastnost_1 text,
lastnost_2 text
);

Dodamo artiklu z ID številko 1 neke dodatne lastnosti:

INSERT INTO artikli_dodatne_lastnosti
VALUES (DEFAULT, 1, 'Lastnost 1', 'Lastnost 2');

Odlično! Zadeva tokrat deluje! Smo pa kar zakomplicirali vse skupaj, kajne? To je slaba lastnost te rešitve, dobra pa je, da baza sama skrbi za konsistentnost podatkov v tabelah, in mi v kodi ne rabimo razmišljat o tem, v katerih tabelah so kateri podatki in kako jih moramo povezat, da se kaj ne izgubi.

1.
2. april 2007, 16:58

Zvito. Veš iz glave, bi znala kaj podobnega tudi MS SQL ali Oracle? Jaz bi rekel, da ne...

2.
2. april 2007, 17:52

Ne bi vedel, niti MS SQL niti Oracla še nisem uporabljal. Je pa tako, če omogočata na kakršenkoli način (rules, triggers, ...) predefiniranje obnašanja viewjev, potem bi se tole dalo izvesti tudi z njima.

3.
JernejK
10. junij 2007, 20:54

Na MS SQL se da s trigerji na view-u ;-)

4.
12. junij 2007, 18:12

Jernej, hvala za info!

5.
Robert
11. februar 2008, 20:30

Pozdrav!

Sem bil ravno sredi brainstorminga kako zastavit podatkovno hirearhijo in mi je tale članek prišel nadvse prav. Hvala! :)

Sicer je morda posredno povezano z dedovanjem pa vendar, prilagam še sledečo povezavo http://www.sai.msu.su/~megera/postgres/gist/ltree/.

6.
14. februar 2008, 19:17

Robert, prav lepo slišat, da komu pomagajo moje objave :)