PostgreSQL in kvazi dedovanje

March 31, 2007 3 minute read

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.

Vini