Nu voi ascunde faptul că PostgreSQL este sistemul meu preferat de administrare a bazelor de date. Voi încerca în cele ce urmează să argumentez această preferinţă prin câteva exemple preluate dintr-o aplicaţie pe care o dezvolt pentru un client al cărui nume va fi, în acest articol, Xcomp. Acest articol nu îşi propune să vă înveţe PostgreSQL într-o oră, ci doar să puncteze câteva funcţionalităţi particulare şi modul în care acestea pot fi fructificate practic. Pentru o prezentare mai generală a sistemului puteţi consulta articolul comparativ "MySQL şi PostgreSQL" publicat în NetReport nr. 102 - martie 2001.
Am speranţa că prezentarea va fi accesibilă tuturor celor care au cunoştinţe elementare despre bazele de date relaţionale şi SQL. În plus, anumite tehnici pot fi adaptate şi pentru alte sisteme de baze de date.
Subiectele şi moştenirea
Deşi tema articolului nu este modelarea relaţională a structurilor de date, voi începe cu observaţia că orice aplicaţie de baze de date este orientată, în principiu, pe câteva subiecte de interes pentru utilizator. În aplicaţia pentru Xcomp, subiectele de interes sunt persoanele şi organizaţiile. Este important să precizez că în multe aspecte, aceste două entităţi trebuie să fie tratate unitar. De pildă, atât persoanele cât şi organizaţiile pot avea calitatea de parteneri (clienţi sau furnizori) pentru Xcomp, ambele pot avea adrese şi alte informaţii adiacente, memorate în diverse tabele care referă cheia primară a subiectului, indiferent dacă este persoană sau organizaţie. Această tratare unitară impune însă câteva cerinţe:
(a) Să pot face anumite căutări asupra subiectelor, indiferent de natura lor;
(b) Cheile primare ale persoanelor şi organizaţiilor să nu se suprapună;
(c) Să pot impune condiţii speciale de integritate referenţială (cheia străină a unei terţe tabele să corespundă fie unei persoane, fie unei organizaţii.
Varianta cu moştenire - PostgreSQL oferă aici o caracteristică interesantă, numită moştenire (inheritance). Iată o posibilitate de structurare:
CREATE TABLE Subiect (
cod SERIAL PRIMARY KEY,
nume TEXT NOT NULL,
ts TIMESTAMP default 'now',
cine NAME default current_user
);
CREATE TABLE Pers (
fnume TEXT NOT NULL,
bnume TEXT NOT NULL,
-- alte coloane
PRIMARY KEY (cod)
) INHERITS (Subiect);
CREATE TABLE Org (
den TEXT NOT NULL,
prefix TEXT,
sufix TEXT,
-- alte coloane
PRIMARY KEY (cod)
) INHERITS (Subiect);
Ideea este simplă: tabele Org şi Pers vor moşteni coloanele tabelei "mamă" Subiect. Rezultatul este că se îndeplineşte în mod simplu cerinţa (a). O selecţie de genul:
SELECT * FROM Subiect
WHERE nume ILIKE 'Fic%';
va găsi atât persoana Fictivescu Ioan cât şi organizaţia Fictiv IMPEX, deoarece căutarea se face implicit în Subiect şi toate tabele care o moştenesc. Desigur, dacă mă interesează doar persoanele, voi face selecţia pe tabela Pers. Dacă mă interesează informaţii despre subiecte în general, dar vreau să ştiu şi din care anume tablă provin (Pers sau Org) atunci cererea este:
SELECT s.cod, s.nume, p.relname
FROM Subiect s, pg_class p
WHERE s.tableoid = p.oid;
unde pg_class este o tabelă sistem iar tableoid şi oid sunt coloane "ascunse", existente în orice tabelă.
Deşi interesantă, moştenirea nu este indispensabilă. La drept vorbind, valoarea ei este mai degrabă cosmetică (structurile sunt mai "expresive"). Aţi observat desigur că tabele moştenitoare nu preiau decât coloanele, nu şi alte caracteristici, ceea ce explică de ce coloana (moştenită) cod a trebuit declarată explicit ca fiind cheie primară în tabele Pers şi Org. Dacă tabele moştenitoare ar prelua şi restricţiile impuse tabelei părinte (de pildă cele de unicitate sau cele de integritate referenţailă), ar fi mult mai interesant...
O observaţie: Declaraţia SERIAL din definiţia coloanei cod din tabela Subiect va crea în mod implicit o secvenţă (numită de sistem subiect_cod_seq). Aceasta va fi folosită de toate tabelele moştenitoare pentru a genera coduri unice, ceea ce asigură faptul că persoanele şi organizaţiile nu-şi vor suprapune codurile, îndeplinind astfel cerinţa (b).
Şi încă o observaţie: Dacă toate inserările au fost făcute în Pers şi Org (aşa cum este normal), o selecţie limitată la tabela Subiect (prin cuvântul cheie ONLY)...
SELECT * FROM ONLY Subiect;
...nu va găsi nici un articol. Acest fapt este important de notat, pentru că dacă unei terţe tabele (de pildă Partener) îi vom impune o condiţie de integritate referenţială relativă la tabela Subiect, vom avea o surpriză neplăcută: nu vom putea insera nici o linie, pentru că în Subiect nu este nimic. Acest "amănunt" ne împiedică (deocamdată) să îndeplinim cerinţa (c).
Varianta banală - Cerinţele (a) şi (b) pot fi îndeplinite relativ uşor şi fără să recurgem la moştenire. Pentru cerinţa (b) vom crea explicit o secvenţă pe care să o folosim atât în Pers cât şi în Org, după care creăm cele două table astfel:
CREATE SEQUENCE sub_seq
MINVALUE 10000;
CREATE TABLE Pers (
cod INTEGER PRIMARY KEY
DEFAULT nextval('sub_seq'),
...
-- celelalte coloane
);
CREATE TABLE Org (
cod INTEGER PRIMARY KEY
DEFAULT nextval('sub_seq'),
...
-- celalalte coloane
);
(unde celelalte coloane sunt cele din Subiect plus cele din Pers şi respectiv Org de mai sus).
Pentru a îndeplini cerinţa (a) definim o tabelă virtuală:
CREATE VIEW Subj AS
SELECT cod, nume, ts, cine, 'pers' as prov FROM Pers
UNION
SELECT cod, nume, ts, cine, 'org' as prov FROM Org;
Acum, o selecţie:
SELECT * FROM Subj
WHERE nume ILIKE 'Fic%';
va funcţiona ca în variantă cu moştenire.
Din păcate, nu putem impune restricţii de integritate cu referire la o tabelă virtuală, aşa că cerinţa (c) rămâne (încă) neîndeplinită.
Varianta cu joncţiune - Există şi o variantă care se află undeva între cele două prezentate mai sus. Ea se bazează pe trei tabele (ca şi în varianta cu moştenire), diferenţa fiind că tabelele Pers şi Org sunt conectate cu tabela Subiect printr-o cheie străină:
CREATE TABLE Subiect (
cod SERIAL PRIMARY KEY,
nume TEXT NOT NULL,
ts TIMESTAMP default 'now',
cine NAME default current_user
);
CREATE TABLE Pers (
cod INTEGER
REFERENCES Subiect (cod)
ON DELETE CASCADE
ON UPDATE CASCADE,
fnume TEXT NOT NULL,
bnume TEXT NOT NULL,
-- alte coloane
PRIMARY KEY (cod)
);
CREATE TABLE Org (
cod INTEGER
REFERENCES Subiect (cod)
ON DELETE CASCADE
ON UPDATE CASCADE,
den TEXT NOT NULL,
prefix TEXT,
sufix TEXT,
-- alte coloane
PRIMARY KEY (cod)
);
Interesant este sunt îndeplinite imediat toate cele trei cerinţe enunţate, dar apare şi un dezavantaj important: actualizările devin destul de complexe. De exemplu, pentru a adăuga persoana Fictivescu Ioan va trebui să fac o operaţie de genul următor:
BEGIN;
INSERT INTO Subiect (nume)
VALUES ('');
INSERT INTO Pers (cod, fnume, bnume)
VALUES (
currval('subiect_cod_seq'),
'Fictivescu', 'Ioan');
UPDATE Subiect SET
nume = p.fnume || ' ' || p.bnume
FROM Pers p
WHERE p.cod = cod
AND p.cod = currval('subiect_cod_seq');
COMMIT;
În primul, să remarcăm faptul că toate aceste operaţii trebuie realizate într-o singură tranzacţie, pentru a evita intercalarea unei alte înserări în Subiect, ceea ce ar incrementa secvenţa subiect_cod_seq, cu efecte uşor de prevăzut (funcţia currval furnizează valoarea curentă a unei secvenţe).
În al doilea rând, e de remarcat clauza FROM din UPDATE (o extensie Postgres), care permite folosirea în actualizare a unor valori din alte tabele.
În fine, un aspect pe care l-am trecut cu vederea până acum: coloana nume din Subiect trebuie completată automat, prin concatenarea câmpurilor fnume şi bnume din Pers, respectiv prefix, den şi sufix din Org (operatorul "||" concatenează stringuri). Dar acest aspect îl vom trata mai târziu.
Vi se pare complicat? Pare, dar nu este, pentru că aici intervine o facilitate extrem de valoroasă oferită de PostgreSQL.
Reguli
Regulile (rules) reprezintă un mecanism fundamental în Postgres. Ele sunt intens folosite de sistem (de pildă moştenirea şi tabelele virtuale sunt implementate prin reguli), dar ne permit şi nouă să simplificăm operarea bazei de date. La modul simplu, ele ne permit să înlocuim în mod transparent o operaţie (insert, update, delete sau select) cu o altă operaţie sau grup de operaţii.
Pentru ca lucrurile să fie mai simple, vom modifica puţin definiţia tabelei 'Pers':
ALTER TABLE Pers
ALTER cod
SET DEFAULT currval('subiect_cod_seq');
Ne bazăm pe ideea că orice inserare în Pers va fi precedată de o inserare în Subiect, iar acest fapt îl impunem prin următoarea regulă:
CREATE RULE ins_pers AS
ON INSERT TO Pers
DO INSERT INTO Subiect (nume)
VALUES (new.fnume || ' ' || new.bnume);
Regula ins_pers va fi aplicată automat la fiecare inserare în tabela Pers. Efectul este că înaintea inserării propriu-zise, în tabela Subiect se va face inserarea specificată în clauza DO, prin care se obţine un cod nou, se completează câmpul nume şi se memorează informaţiile de control (momentul actualizării şi numele utilizatorului). Deoarece regula se execută implicit în mod tranzacţional ("totul sau nimic") putem fi siguri că inserarea în Pers se va face cu codul proaspăt generat în Subiect. Aşadar, o inserare în Pers (care însa va face şi inserarea în Subiect) va fi cât se poate de simplă:
INSERT INTO Pers (fnume, bnume)
VALUES ('Fictivescu', 'Ioan');
Pentru actualizare şi ştergere, regulile sunt:
CREATE RULE update_pers AS
ON UPDATE TO Pers
DO UPDATE Subiect SET
nume = new.fnume || ' ' || new.bnume
WHERE cod = old.cod;
CREATE RULE delete_p AS
ON DELETE TO Pers
DO DELETE FROM Subiect
WHERE cod = old.cod;
Semnificaţia atributelor old.nume (în reguli pentru update şi delete) şi new.nume (în reguli pentru insert şi update) este uşor de dedus. Regulile sunt similare pentru tabela Org.
Regulile pot rezolva automat multe lucruri interesante. Le-am folosit, de pildă, pentru menţinerea automată a istoriei actualizărilor efectuate asupra subiectelor. O altă aplicaţie interesantă a regulilor este posibilitatea de a implementa tabele virtuale actualizabile.
Spre exemplificare, să considerăm tabela Partener, definită astfel:
CREATE TABLE Partener (
cod INTEGER
REFERENCES Subiect (cod)
ON DELETE CASCADE
ON UPDATE CASCADE,
cod_fisc VARCHAR(25),
banca INTEGER
REFERENCES Org (cod)
ON DELETE RESTRICT
ON UPDATE CASCADE,
-- Banca este in Org
cont TEXT,
PRIMARY KEY (cod)
);
Definim acum tabela virtuală VPartener astfel:
CREATE VIEW Vpartener AS
SELECT p.cod, s.nume, p.codfisc,
o.den as banca, p.cont
FROM Partener p, Subiect s, Org o
WHERE p.cod = s.cod
AND p.banca = o.cod;
Putem crea acum regulile pentru actualizarea acestui view. De pildă, pentru ştergere:
CREATE RULE del_vp AS
ON DELETE TO Vpartener
DO INSTEAD
DELETE FROM Subiect
WHERE cod = old.cod;
Desigur, exemplul este pur didactic şi vrea să ilustreze utilizarea clauzei DO INSTEAD (care face ca actualizarea iniţială să nu mai fie executată deloc - este înlocuită de acţiunea specificată în regulă) şi înlănţuirea acţiunilor specificate prin reguli şi prin clauzele ON DELETE CASCADE. Practic, ştergerea subiectului (realizată prin regula del_vp) va atrage automat ştergerea partenerului şi ştergerea liniei corespunzătoare din Pers sau Org (în funcţie de natura subiectului). Desigur, în aplicaţia pentru Xcomp nu se întâmplă aşa, pentru că deşi subiectul îşi pierde calitatea de partener, el poate prezenta interes din alte puncte de vedere, deci nu este şters automat.
Câteva observaţii finale despre reguli:
Există posibilitatea de a specifica mai multe fraze SQL într-o regulă, caz în care aceste se scriu între paranteze.
Se poate specifica şi o acţiune "specială", desemnată prin NOTHING. Semnificaţia este clară. Putem astfel să împiedicăm anumite acţiuni. De pildă:
CREATE RULE prevent AS
ON UPDATE TO OpCont
DO INSTEAD NOTHING;
Funcţii
Regulile reprezintă un mecanism server-side foarte eficient, dar nu întotdeauna suficient.
Funcţiile definite de utilizator din PostgreSQL sunt echivalentul procedurilor stocate din alte sisteme. Sunt relativ uşor de folosit, iar beneficiile pe care le pot aduce sunt imense. În PostgreSQL, funcţiile pot fi scrise în diverse limbaje, dintre care voi aborda în acest articol doar două: SQL şi PLPGSQL, o extensie procedurală specifică pentru Postgres.
Observaţie: Postgres este un sistem extensibil, aşa ca utilizatorii pot să definească chiar şi noi limbaje procedurale, care pot fi apoi instalate şi folosite pentru a scrie funcţii utilizator. Alături de plpgsql în distribuţia standard sunt furnizate şi module corespunzătoare limbajelor Perl şi TCL (mai nou şi Python).
Prima problemă concretă cu care m-am confruntat a fost cea a caracterelor cu semne diacritice. Deoarece datele proveneau din diverse surse interne ale Xcomp, unele aveau caractere diacritice, altele nu aveau, ceea ce punea un set de probleme uşor de imaginat. Suportul pentru localizare (atât la nivelului sistemului de operare cât şi la nivelul serverului PostgreSQL) rezolvă o parte dintre probleme, dar uneori încurcă. De pildă, una dintre cerinţe era ca în unele liste ordonarea să se facă făcând abstracţie de semnele diacritice din nume, astfel încât "Sarbu Mircea" să apară alături de "Sârbu Mircea", pentru a facilita identificarea şi eliminarea dublurilor. În cele ce urmează se presupune că suportul de localizare nu este instalat iar setul de caractere este Latin2.
Iată şi prima funcţie:
CREATE FUNCTION SUpper (bpchar)
RETURNS text
AS '
SELECT upper(
translate($1,
''áâăäéëíîóoöúuüţş'',
''ÁÂĂÄÉËÍÎÓOÖÚUÜŢŞ''
)
);
' LANGUAGE 'sql';
Este o funcţie în limbaj SQL, care primeşte un string şi returnează un acest string după ce transformă toate caracterele în majuscule. Practic, se aplică două funcţii standard: translate transformă caracterele cu diacritice iar upper se ocupă de celelalte. (Suportul local ar rezolva această problemă implicit.)
Să remarcăm că argumentele sunt referite prin semnul $ urmat de poziţia argumentului în lista de parametri formali şi că deoarece corpul funcţiei este furnizat ca şir de caractere (între apostrofuri), stringurile din interior se incadrează între două apostrofuri.
Analog se defineşte şi funcţia SLower.
O problemă ceva mai complexă o reprezintă formatarea numelor de persoane. Postgres dispune de funcţia initcap(text), dar aceasta nu-mi convine din două motive: (1) nu recunoaşte caracterele cu diacritice, şi (2) nu recunoaşte decât caracterul spaţiu drept separator.
Dacă primul neajuns se poate rezolva prin suportul local, al doilea nu se poate rezolva decât daca-mi definesc propria funcţie. Este timpul să intre în scenă limbajul PLPGSQL:
CREATE FUNCTION SInitCap (text, text)
RETURNS text AS '
DECLARE
sin text;
sep text;
out text;
p integer;
BEGIN
IF $1 = '''' THEN RETURN $1;
ELSE sin := $1;
END IF;
sep := '' ''||$2;
sin := SUpper(substr(sin, 1, 1))
||SLower(substr(sin, 2));
FOR i IN 1..char_length(sep) LOOP
out := '''';
p := strpos(sin, substr(sep, i, 1));
WHILE p > 0 LOOP
out := out || substr(sin, 1, p);
sin := substr(sin, p+1);
sin := SUpper(substr(sin, 1, 1))
|| substr(sin, 2);
p := strpos(sin, substr(sep, i, 1));
END LOOP;
sin := out || sin;
END LOOP;
RETURN sin;
END;
' LANGUAGE 'plpgsql';
O descriere a limbajului plpgsql este dincolo de scopul acestui articol, dar exemplul este suficient de sugestiv pentru a vă forma o idee despre posibilităţile sale.
Această funcţie poate fi folosită pentru la completarea câmpurilor care cuprind nume de persoană, eventual tot prin intermediul unor reguli. De pildă:
CREATE RULE ins_pers AS
ON INSERT TO Pers
DO INSERT INTO Subiect (nume)
VALUES
(SinitCap(new.fnume||' '|| new.bnume, '-'''));
Rezultatul este că dacă numele furnizat este o'neil iar prenumele este paul-george în câmpul nume al tabelei Subiect se va scrie O'Neil Paul-George.
Operatori
Funcţiile stau la baza multor facilităţi interesante oferite de PostgreSQL.
Să ducem exemplul mai departe şi să ne punem problema sortării numelor în absenţa suportului local, aşa cum am stabilit (deci sortarea sa nu tină seama de diacritice, astfel încât de exemplu, caracterele a, ă, â să fie echivalente). Pentru aceasta, va trebui să construiesc mai întâi o funcţie care să compare două stringuri după regula pe care am enunţat-o:
CREATE FUNCTION ch_ls (bpchar, bpchar)
RETURNS bool AS '
SELECT to_ascii($1) < to_ascii($2);
' LANGUAGE 'sql';
E simplu, deoarece funcţia Postgres to_ascii îmi converteşte şirul de caracter Latin2 în ASCII, aşa că toate caracterele îţi vor pierde semnele diacritice. Acum putem să definim operatorul numit "<<<":
CREATE OPERATOR <<< (
leftarg = bpchar,
rightarg = bpchar,
procedure = ch_ls,
commutator = >,
negator = >=,
restrict = scalarltsel,
join = scalarltjoinsel
);
Ceea ce ne interesează în această declaraţie este faptul că operatorul este definit pe şiruri de caractere (bpchar) şi că funcţia care-l implementează este ch_ls definită mai sus. Utilizarea acestui operator este echivalentă cu folosirea funcţiei ch_ls, deci următoarele selecţii funcţionează (şi returnează f ):
SELECT ch_ls('Sârbu', 'Sabău');
SELECT 'Sârbu' <<< 'Sabău';
Însă câştigul major pe care mi-l aduce acest operator reiese cu claritate din cererea următoare:
SELECT cod, nume FROM Subiect
ORDER BY nume USING <<<;
În felul acesta, ordonarea nu se va face folosind operatorul implicit ("<"), ci pe cel indicat în clauza USING, cu rezultatul pe care l-am aşteptat.
În mod asemănător se construieşte de "asemănare" pentru şiruri:
CREATE FUNCTION ch_alike (bpchar, bpchar)
RETURNS bool AS '
SELECT to_ascii($1) ILIKE to_ascii($2);
' LANGUAGE 'sql';
CREATE OPERATOR ~~~ (
leftarg = bpchar,
rightarg = bpchar,
procedure = ch_alike,
commutator = ~~~,
hashes,
sort1 = <<<,
sort2 = <<<<
);
Iarăşi nu voi intra în detalii. Voi preciza doar că ILIKE este ca şi LIKE cu diferenţa că nu ia în consideraţie majusculele. O interogare de genul:
SELECT cod, nume FROM Subiect
WHERE nume ~~~ 'sarbu %'
ORDER BY nume USING <<<;
va returna toate persoanele cu numele "Sarbu" sau "Sârbu". Acest operator este extrem de preţios în situaţia (destul de comună, de altfel) în care nu putem să ştim dacă numele au fost introduse cu diacritice sau fără.
Intermezzo
De multe ori este dificil de ales între o soluţie care complică structura bazei de date şi o soluţie care complică funcţionalitatea. În cazul aplicaţiei pentru Xcomp, problema se punea în felul următor: există o largă diversitate de informaţii pe care Xcomp doreşte să le memoreze despre subiectele sale (persoane şi organizaţii). De pildă, în privinţa organizaţiilor sunt importante domeniile de activitate, dimensiunea, relaţiile de subordonare între ele, dotarea informatică etc. De asemenea, sunt importante persoanele de contact şi funcţiile pe care acestea le îndeplinesc etc. O facilitate dorită este şi aceea de a putea memora informaţii nestructurate (note, observaţii). Nu doar că sunt multe tipuri de informaţii, dar există şi o dinamică a acestor tipuri. Oricând pot apărea altele noi sau altele îşi pierd relevanţa...
Funcţionalităţile pe care mi le oferă PostgreSQL mă îndeamnă să aleg simplitatea şi stabilitatea structurii, ceea ce conduce şi la o întreţinere mai comodă a aplicaţiilor client. Astfel, în loc să proiectez tabele speciale pentru toate aceste informaţii, am ales varianta de a folosi o singură tabelă pentru scopuri dintre cele mai diverse:
CREATE TABLE Detaliu (
cod INTEGER
REFERENCES Subiect
ON DELETE RESTRICT
ON UPDATE CASCADE,
tip TEXT NOT NULL,
text TEXT,
int INTEGER,
ref INTEGER
REFERENCES Subiect
ON DELETE SET NULL
ON UPDATE CASCADE,
ts TIMESTAMP default 'now',
cine NAME default current_user,
primary key (cod, TS)
);
Fiecare detaliu se referă la un subiect (persoană sau organizaţie) şi are un tip care specifică natura informaţiei memorate. Se poate defini un repertoriu de tipuri, de genul: domeniu, contact, nota etc. O altă tabelă poate juca rol de nomenclator pentru diferitele codificări folosite:
CREATE TABLE Nom (
tip TEXT,
cod INTEGER,
nume TEXT,
primary key (tip, cod)
);
Pentru exemplificare, să considerăm că am în tabela Nom următoarele linii:
| Tip | Cod | Nume |
| domeniu | 0 | O |
| domeniu | 1 | Metalurgie |
| domeniu | 2 | Electronică |
| ... |
| contact | 0 | PO |
| contact | 1 | Top Management |
| contact | 2 | IT Management |
| ... |
| nota | 0 | S |
| ... |
Iată cum voi memora faptul că organizaţia MetalFict cu codul 12098 activează în domeniul metalurgiei:
INSERT INTO Detaliu (cod, tip, int)
VALUES (12098, 'domeniu', 1);
...Sau că Ioan Fictivescu cu codul 11567 este director executiv la MetalFict:
INSERT INTO Detaliu (cod, tip, int, text, ref)
VALUES (11567, 'contact', 1, 'Director executiv', 12098);
Acum pot să caut organizaţiile din domeniul metalurgiei astfel:
SELECT o.den FROM Org o, Detaliu d
WHERE o.cod = d.cod
AND d.tip like 'domeniu'
AND d.int = 1;
Pot să aflu persoanele de contact pentru MetalFict astfel:
SELECT p.nume, d.text AS functia
FROM Pers p, Detaliu d
WHERE p.cod = d.cod
AND d.tip like 'contact'
AND d.ref = 12098;
Desigur, se poate rafina mai departe. Ceea ce vom face dealtfel, dar după ce vă voi spune câteva cuvinte despre...
Agregate
Desigur, funcţiile de agregare sunt binecunoscute iniţiaţilor în SQL. Orice SGBD relaţional pune la dispoziţia utilizatorilor măcar câteva: count, sum, average, min şi max. PostgreSQL mai dispune de două funcţii statistice: stddev (deviaţia standard) şi variance (pătratul deviaţiei standard). Mai interesant este însă faptul că ne permite să ne definim propriile funcţii de agregare (numite, oarecum impropriu, agregate).
La ce ne-ar putea folosi? Iată, de exemplu, la ce mi-au folosit mie: datorită faptului că datele provin din nişte formulare completate de mai mulţi corespondenţi, este posibil ca aceeaşi organizaţie să figureze ca activând în mai multe domenii de activitate (nu este neapărat o greşeală). O cerinţă naturală ar fi să pot obţine un text care să cuprindă numele tuturor domeniilor (fără dubluri), cu virgula de rigoare între ele.
Pentru aceasta, avem nevoie de o funcţie care să concateneze în mod inteligent două şiruri de caractere. Să o numim AddText :
CREATE SUNCTION AddText (text, text) RETURNS text AS '
DECLARE
s2 text;
BEGIN
IF $2 = '''' OR $2 ISNULL THEN
RETURN $1;
ELSE s2 := trim(both from $2);
END IF;
IF $1 = '''' OR $1 ISNULL THEN
RETURN s2;
END IF;
IF strpos(to_ascii($1), to_ascii(s2||''| '')) > 0 THEN
RETURN $1;
ELSE
RETURN $1 ||''| ''|| s2;
END IF;
END;
' LANGUAGE 'plpgsql';
În această funcţie am folosit în locul virgulei un caracter mai puţin uzual (bara verticală), pentru a evita "potrivirea" accidentală a stringului adăugat (al doilea argument) cu o porţiune a primului string (care va fi folosit de agregat pe post de acumulator). În final, vom avea nevoie de o altă funcţie care să schimbe bara verticală în virgulă:
create function FinText (text) returns text as '
DECLARE
s text;
BEGIN
IF $1 != '''' THEN
s := $1;
s := translate(s, ''|'', '','');
RETURN substr(s, 1, char_length(s));
ELSE
RETURN $1;
END IF;
END;
' language 'plpgsql';
Acum se poate defini agregatul 'AddText':
CREATE AGGREGATE AddText (
basetype = text,
stype = text,
sfunc = addtext,
finalfunc = fintext,
initcond = ''
);
Agregatul funcţionează pe baza tranziţiei între stări, starea iniţială fiind indicată prin initcond, iar funcţia de tranziţie este indicată prin sfunc. În final se aplică finalfunc.
Putem acum obţine lista domeniilor de activitate a firmei MetalFict astfel:
SELECT AddText(n.nume)
FROM Detaliu d, Nom n
WHERE d.cod = 12098
AND d.tip LIKE 'domeniu'
AND d.int = n.cod
GROUP BY d.cod;
Triggers
Declanşatoarele (triggers) sunt asemănătoare regulilor prin faptul că se ataşează unei tabele şi unei operaţii de actualizare asupra acestei şi declanşează o procesare specifică. Spre deosebire de reguli, declanşatoarele nu pot fi ataşate tabelelor virtuale şi, mai ales, procesarea se efectuează printr-o funcţie, ceea ce permite utilizarea unui limbaj procedural.
În general, declanşatoarele sunt folosite pentru a verifica condiţii de consistenţă, dar pot fi utilizate şi pentru alte scopuri. În aplicaţia pentru Xcomp am folosit triggers şi pentru formatarea automată a unor câmpuri (de pildă pentru formatarea numelor de persoană, pentru extragerea automată a prefixului şi sufixului din numele complet al unei organizaţii, pentru formatarea adreselor, a numerelor de telefon etc).
Pentru exemplificare, voi folosi din nou tabelele Detaliu şi Nom. Problema pe care o am de rezolvat este următoarea: în funcţie de natura informaţiei stocată în tabela Detaliu, câmpul cod trebuie să puncteze fie o persoană (ca în cazul tipului contact), fie o organizaţie (ca în cazul tipului domeniu), fie oricare subiect (ca în cazul tipului nota). Clauza references nu verifică decât că referinţa este validă relativ la tabela Subiect, deci pentru a asigura consistenţa am nevoie de o verificare suplimentară. La fel se întâmplă şi pentru câmpul ref, dacă este cazul (de pildă, în cazul tipului contact acesta trebuie să puncteze o organizaţie).
Aţi observat desigur că în tabela Nom apare câte o linie cu cod zero pentru fiecare tip. În aceste linii, stochez codificat numele tabelei pe care trebuie să o refere câmpul cod (şi, dacă e cazul, câmpul ref) din tabela Detaliu, în funcţie de tipul informaţiei. Scopul este ca un trigger să preia această informaţie şi să facă verificările suplimentare necesare.
Funcţia care implementează un trigger are câteva caracteristici speciale, printre care faptul că returnează un tip generic numit opaque şi că dispune de new şi old exact cu semnificaţia de la reguli (rules). Funcţia va returna de fapt linia implicată în operaţie. Iată funcţia care va fi utilizată atât la inserare cât şi la modificare:
CREATE FUNCTION ins_det ()
RETURNS OPAQUE AS '
DECLARE
table text;
cod varchar(1);
ref varchar(1);
rez integer;
BEGIN
SELECT n.nume INTO table
FROM Nom n
WHERE n.tip ILIKE NEW.tip
AND n.cod = 0;
cod := substr(table, 1, 1);
IF char_length(table) > 1 THEN
ref := substr(table, 2, 1);
ELSE
ref := '' '';
END IF;
IF cod = ''P'' THEN
SELECT p.cod INTO rez
FROM Pers p
WHERE p.cod = NEW.cod;
IF NOT FOUND THEN
RAISE EXCEPTION
''Subiectul % nu este persoana'', NEW.Cod ;
END IF;
END IF;
IF cod = ''O'' THEN
SELECT o.cod INTO rez
FROM Org o
WHERE o.cod = NEW.cod;
IF NOT FOUND THEN
RAISE EXCEPTION
''Subiectul % nu este organizatie'', NEW.Cod ;
END IF;
END IF;
IF ref != '' '' THEN
IF ref = ''P'' THEN
SELECT p.cod INTO rez
FROM Pers p
WHERE p.cod = NEW.ref;
IF NOT FOUND THEN
RAISE EXCEPTION
''Referinta % nu este persoana'', NEW.ref ;
END IF;
END IF;
IF ref = ''O'' THEN
SELECT o.cod INTO rez
FROM Org o
WHERE o.cod = NEW.ref;
IF NOT FOUND THEN
RAISE EXCEPTION
''Refernta % nu este organizatie'', NEW.ref ;
END IF;
END IF;
END IF;
RETURN New;
END;
' LANGUAGE 'plpgsql';
Singurul lucru mai special îl reprezintă clauza INTO din selecţii, care este obligatorie în funcţii. Variabila rez nu o folosesc decât pentru a colecta valorile selectate (deşi nu mă interesează). Utilizarea alias-ului în selecţii am făcut-o pentru a evita confuzia cu variabila cod declarată în funcţie. În rest, e banal.
Să creăm declanşatorul:
CREATE TRIGGER insdet
BEFORE INSERT OR UPDATE ON Detaliu
FOR EACH ROW
EXECUTE PROCEDURE ins_det();
Observaţie: Spre deosebire de reguli, un acelaşi declanşator poate fi folosit pentru mai multe operaţii (în cazul acesta: insert şi update).
Acum, integritatea referenţială este protejată complet, prin declaraţiile references şi prin trigger-ul insdet.
Revenind acum la problema pusă la începutul articolului, merită să remarcăm faptul că oricare ar fi structura aleasă pentru organizarea subiectelor, cerinţa (c) poate fi realizată cu ajutorul unor declanşatoare proprii.
O notă finală despre declanşatoare: De fapt, PostgreSQL implementează restricţia foreign key prin intermediul unor declanşatoare generate automat.
Concluzie
Postgres a fost primul SGBD aşa-zis obiectual-relaţional. Atributul obiectual nu se datorează, aşa cum greşit cred unii, posibilităţii de moştenire a tabelelor (de altfel destul de firav, după cum am văzut), ci posibilităţii de a crea tipuri noi de date, împreună cu toţi operatorii şi funcţiile necesare. Noile tipuri se încarcă dinamic în chiar motorul bazei de date şi pot fi apoi folosite în SQL sau în celelalte mecanisme exact ca şi tipurile native.
Această caracteristică inovatoare a fost preluată de varianta comercială numită Ilustra, prin intermediul căreia a ajuns în Informix (preluată cu totul de IBM), după care a fost imitată de ceilalţi producători, intrând în mainstream.
Da, dar în PostgreSQL vine gratuit...
