Doslovný překlad slova trigger je spoušť a nebo ještě lépe spouštěč. To přesně vystihuje činnost, kterou trigger vykonává. Jedná se o uživatelem definovanou funkci, která je spuštěna nad předem definovanou tabulkou v předem definované situaci (např. při použití INSERT, DELETE, UPDATE). Uživatel tak do rukou dostává velmi silný nástroj. Typické použití triggeru muže být například při hlídání vzájemné konzistence několika tabulek, úpravě dat před jejich uložením apod.

V PostgreSQL (PgSQL) se trigger vytvoří přikazem CREATE TRIGGER. Tímto SQL příkazem se přiřadí již dříve definovaná funkce k určité tabulce a určité akci nad touto tabulkou. Jak již bylo zmíněno, je nutné, aby funkce byla definována ještě před použitím příkazu CREATE TRIGGER. To se provede příkazem CREATE FUNCTION, ve kterém se definuje jméno funkce, místo uložení jejího spustitelného kódu, programovací jazyk, ve kterém je funkce napsána a datový typ který funkce vrací (u triggerové funkce to musí být typ 'opaque'). V PgSQL je možné používat na psaní funkcí libovolný programovací jazyk (v PgSQL se takový jazyk nazývá Procedural Language - PL). V současné době je ve standardní distribuci podpora pro funkce napsané v PL/pgSQL, PL/Tcl, C a pracuje se na Perlu. Pokud někdo chce, může si do PgSQL přidat i vlastní podporu pro jiný jazyk (tedy pokud do něho implementuje operace potřebné pro práci se SPI rozhraním serveru). Nový jazyk se definuje příkazem CREATE LANGUAGE. Více informací naleznete v PostgreSQL Programmer's Guide, která je např. na http://docs.linux.cz.

V tomto článku se budeme věnovat psaní nových funkcí v klasickém C. V případě C se SQL serveru předává nová funkce v podobě .so souborů. Tedy klasického zdrojového kódu zkompilovaného např. v gcc s parametrem -shared (více viz ukázka). Jedná se tedy v podstatě o dynamické přidání nového modulu (podobně jako u kernelu nebo DSO-apache atd.) bez nutnosti nějak modifikovat a kompilovat SQL server.

Ještě dříve než se dostaneme k psaní vlastního triggeru, je nutné se zmínit o SPI (Server Programming Interface), které budeme v triggerové funkci používat.

SPI - Server Programming Interface

SPI je v některých případech velmi podobné standardní klientské libpq. I zde je možné pokládat serveru SQL dotazy (ano, čtete dobře - uvnitř SQL serveru můžete používat SQL dotazy stejně jako u klienta), modifikovat data atd.

Základními funkcemi jsou SPI_connect() / SPI_finish(). Tyto funkce připojí/odpojí váš modul (vaši funkci) od/k SPI rozhraní serveru. Další důležitou funkcí je SPI_exec(), která umožňuje položit serveru SQL dotaz a definovat počet tuples, které mají být vráceny. Funkce vrací status odpovědi (je či není-li v pořádku). Zároveň nastavuje globální proměnné SPI_processed (počet odpovědí - počet tuples) a *SPI_tuptable (pointer na tuples - tedy na data).

V SPI programování je nutné rozlišovat u tuples dva důležité datové typy, a to:

• HeadTuple - ve *SPI_tuptable je to SPI_tuptable->vals[n], kde 'n' je číslo řádky (tuple) odpovědi. Tato struktura obsahuje vlastní data.
• TupleDesc - ve *SPI_tuptable je to SPI_tuptable->tupdesc a obsahuje popis tuples (tuple description).

Tyto dvě struktury jsou základními stavebními kameny SPI i triggerů v C a jsou často parametrem SPI fukcí. Například pokud chceme vědět pořadové číslo sloupce (správně atributu) 'ahoj' v odpovědi, tedy použijeme:

SPI_fnumber(SPI_tuptable->tupdesc, "ahoj");

K datům v tuples se dostanete např. pomocí funkce SPI_getvalue(). Důležité je nezapomenut na to, že SQL server interně pro SPI nerozlišuje datové typy (tak jak jsou definovány v tabulkách (CREATE TABLE), ale v SPI je vše řetězec.

Např.:

     int radka   = 10;
     int sloupec = SPI_fnumber(\
                   SPI_tuptable->tupdesc, "ahoj"); 
     char *data  = SPI_getvalue(\
                   SPI_tuptable->vals[radka],\
                   SPI_tuptable->tupdesc, sloupec);

Tuples jde i modifikovat. To použijeme hlavně u triggerů. Modifikace je možná funkcí SPI_modifytuple(). Tato funkce příjmá data pouze v typu 'Datum' (což nemá nic společného s datem). Při konverzi do tohoto typu je např. typ 'char' nejdříve nutné přetypovat na typ 'text' a pak na typ 'Datum'. A to: PointerGetDatum( textin( "neco" )). U 'int' je přetypování provedeno makrem Int32GetDatum().

SPI obsahuje celkem 18 funkcí, jejichž přesný popis najdete ve zmiňovaném programátorském manuálu. Pro účely tohoto článku vystačíme s již uvedenými.

C Trigger

CREATE TRIGGER <trigger name> <BEFORE|AFTER>\
       <INSERT|DELETE|UPDATE>
       ON <relation name> FOR EACH <ROW|STATEMENT>
       EXECUTE PROCEDURE <procedure name>\
       (<function args>);

Ale vraťme se k programování. Při zavolaní naší triggerové funkce nám SQL server poskytne globální proměnnou (strukturu) CurrentTriggerData. Veškeré triggerové operace se týkají této struktury. Ta (mimo jiné) obsahuje:

• CurrentTriggerData->tg_trigger - obsahuje informace o triggeru (jeho jméno, args) atd.
• CurrentTriggerData->tg_event - proměnná, která nám umožní zjistit pomocí maker TRIGGER_FIRED_BY_UPDATE (INSERT, DELETE), při jakém SQL příkazu je trigger volán a kdy je volán TRIGGER_FIRED_AFTER (BEFORE).
• CurrentTriggerData->tg_relation - obsahuje informace o tabulce, na kterou je trigger volán.
• CurrentTriggerData->tg_relation->rd_att - je TupleDesc (viz. část o SPI) - tedy popis dat.
• Pro HeadTuple se v triggerech používá název 'rettuple'. Tou je CurrentTriggerData->tg_trigtuple u INSERT, DELETE (a obsahuje vkládaná nebo mazaná data). U UPDATE je to CurrentTriggerData->tg_newtuple, která obsahuje nová data, zatímco původní data jsou v CurrentTriggerData->tg_trigtuple.

Před vlastním příkladem snad ještě zmínka o funkci elog(typ, str). Ta, pokud použijete jako typ logu 'ERROR', ukončí transakci (a provádění triggeru), pokud použijete 'NOTICE', odešle 'str' klientovi (to může být výhodné například při ladění atd.).

Na příkladu Triggerová funkce si ukažme triggerovou funkci, která končí chybou, je-li do tabulky "goodOS" a sloupce "OSname" vkládán text "okna". Je-li do téhož sloupce vládán text "penguin", je nahrazen textem "Linux". Zároveň trigger uloží o každé akci log (tabname, username, action, datum) do tabulky "logOS". Trigger bude spouštěn vždy pro každou řádku (FOR EACH ROW) pro INSERT, DELETE a UPDATE. To znamená, že v 'rettuple' budou data pro danou řádku.

#include "executor/spi.h" #include "commands/trigger.h" #include "miscadmin.h" HeapTuple nowin(void); HeapTuple nowin() { TupleDesc tupdesc; /* data description */ HeapTuple rettuple = NULL; /* tuple data */ char *data, /* my data */ *tablename, /* table name */ *action = NULL, /* SQL action - INSERT..*/ Qbuff[8*1024]; /* buffer for SQL query */ int attnum, /* column number */ ret; if (!CurrentTriggerData) elog(ERROR, "triggers are not initialized"); if (TRIGGER_FIRED_BY_UPDATE(CurrentTriggerData->tg_event)) { action = "UPDATE"; rettuple = CurrentTriggerData->tg_newtuple; } else if (TRIGGER_FIRED_BY_INSERT(CurrentTriggerData->tg_event)) { action = "INSERT"; rettuple = CurrentTriggerData->tg_trigtuple; } else if (TRIGGER_FIRED_BY_DELETE(CurrentTriggerData->tg_event)) { action = "DELETE"; rettuple = CurrentTriggerData->tg_trigtuple; } tupdesc = CurrentTriggerData->tg_relation->rd_att; /* Connect to SPI manager */ if ((ret = SPI_connect()) < 0) elog(ERROR, "SPI_connect returned %d", ret); /* Set table name */ tablename = SPI_getrelname(CurrentTriggerData->tg_relation); /* Log action */ sprintf(Qbuff, "INSERT INTO logOS VALUES ('%s', '%s', '%s', 'now'::text)", tablename, GetPgUserName(), action); ret = SPI_exec(Qbuff, 1); if (ret < 0) elog(ERROR, "SPI_exec returned %d for LOG", ret); elog(NOTICE, "Hello - here LN trigger (for %s on %s)!", action, tablename); /* Not action for DELETE */ if (!strcmp(action, "DELETE")) { SPI_finish(); CurrentTriggerData = NULL; return(rettuple); } /* OSname data */ attnum = SPI_fnumber(tupdesc, "OSname"); data = SPI_getvalue(rettuple, tupdesc, attnum); /* okna -to-> trash */ if (!strcmp(data, "okna")) elog(ERROR, "Sorry, but 'okna' is not goodOS!"); /* penguin -to-> Linux */ else if (!strcmp(data, "penguin")) { Datum newdt; /* Datum is spec. data type */ /* set new data */ newdt = PointerGetDatum( textin( "Linux" ) ); /* modify returned tuple */ rettuple = SPI_modifytuple(CurrentTriggerData->tg_relation, rettuple, 1, &attnum, &newdt, NULL); } SPI_finish(); CurrentTriggerData = NULL; return(rettuple); }

Kompilaci tohoto příkladu provedeme příkazem:

	gcc -shared -Wall -O3 -o LN.so LN.c 

Pak musíme SQL serveru sdělit, že máme novou funkci a chceme trigger, a je také nutné udělat tabulky, tedy:

DROP TABLE goodOS;
CREATE TABLE  goodOS (OSname varchar(32));

DROP TABLE logOS;
CREATE TABLE  logOS (
	tabname		name, 
	username	name, 
	action	 	varchar(16),
	datum		datetime
);

DROP FUNCTION nowin();
CREATE FUNCTION         nowin()
        RETURNS         opaque
        AS              '<YOUR_FULL_PATH>/LN.so'
        LANGUAGE        'c';

DROP TRIGGER LN_trigger ON goodOS;
CREATE TRIGGER LN_trigger BEFORE INSERT\
        or DELETE or UPDATE
	ON goodOS FOR EACH ROW
	EXECUTE PROCEDURE nowin();

Na závěr malé upozornění. Naprogramováním triggeru v C přímo vstupujete do SQL serveru, uděláte-li tedy chybu ve své funkci (např. přístup do paměti který kernel odmění signálem SIGSEGV), ovlivníte tím chod SQL serveru a ten se může tedy odebrat do souboru core. Ale pochopitelně se jedná pouze o potomka hlavního SQL serveru (postmastera), není tedy nutné se bát, že takto ovlivníte celý server (to je také výhoda ne-threadového řešení serveru, i když za cenu větší pomalosti (viz. fork()).

Uvedený příklad (i zkompilovaný) naleznete na http://home.zf.jcu.cz/~zakkr/LN/.