Stored Procedures en het gebruik hiervan in je app
Een “stored procedure” (afgekort SP) is een functie welke je definieert in je database. In deze functie kun je bijvoorbeeld queries uitvoeren, gegevens ophalen, bewerken, controles uitvoeren enz. Doordat je rechtstreeks op de database werkt heb je verschillende voordelen:
- Je hoeft bepaalde procedures maar 1 keer te definiëren om ze vervolgens door je hele applicatie te kunnen gebruiken
- Je werkt op de database, doorgaans is dit vele malen sneller dan een cliënt welke verbinding moet maken, query doorgeven, query laten analyseren en valideren, query uitvoeren en vervolgens de resultaten doorgeven
- Je kunt bepalen dat een databasegebruiker slechts rechten heeft op bepaalde SP’s, zonder dat je rechten hoeft te geven op de onderliggende datastructuur!
Stel je nu eens voor, je wilt in deze case een gebruiker toevoegen aan je systeem. "Simpel" zou je misschien denken, een gewone INSERT op je gebruikerstabel en klaar is kees. Zeker waar, maar, laten we het eens van de andere kant bekijken: Je wilt voorbereid zijn op de toekomst en houdt er rekening mee dat meerdere systemen zullen gaan communiceren met jouw systeem, in mijn ervaring gebeurt dat vroeg of laat met ieder groot/goed systeem. Wil je dan dat jan-en-alleman in jouw database gaat krassen? Absoluut niet!
Verberg de databasestructuur voor de buitenwereld en bouw een zgn. API (Application Programming Interface) op de database. In die API neem je alle acties op welke de cliënt(s) moet kunnen gebruiken, in deze case zou je kunnen denken aan de volgende acties:
- sp_add_product: Voegt een product toe aan het systeem
- sp_edit_product: Wijzigt een bestaand product
- sp_get_product: Retourneert 1 product
- sp_delete_product: Verwijdert een bestaand product
- sp_add_user: Voegt een gebruiker toe aan het systeem
- sp_edit_user: Wijzigt een bestaande gebruiker
- etc…
Op het eerste gezicht lijkt het misschien wat meer werk, aan de ene kant is dat zo maar anderzijds maak je de applicatie(s) meer schaalbaar.
De code van sp_add_product:
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
$$
DECLARE
p_titel ALIAS FOR $1; -- Parameter titel
p_prijs ALIAS FOR $2; -- Parameter prijs
BEGIN
-- Voer de INSERT uit
INSERT INTO base.tbl_product(title,price) VALUES(p_titel,p_prijs);
RETURN TRUE;
-- Vang eventuele fouten op (Try Catch-achtige constructie)
EXCEPTION
WHEN UNIQUE_VIOLATION THEN
RAISE EXCEPTION 'De titel ''%'' komt al voor in de database.', p_titel;
RETURN FALSE;
WHEN OTHERS THEN
RAISE EXCEPTION 'Er is een fout opgetreden.';
RETURN FALSE;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;
Je ziet dat deze functie 2 parameters verwacht: een varchar en numeric, in het declare-gedeelte van de functie koppel ik hier logischere namen aan: p_titel en p_prijs. Vervolgens wordt een blok gestart met het commando "BEGIN", zie ook http://www.phphulp.nl/php/tutorials/3/371/816/.
Zoals je hebt kunnen zien in het model is de titel van een product geïndexeerd als een unieke sleutel, een productnaam kan slechts 1 keer voorkomen in de tabel. In deze functie wordt deze melding netjes opgevangen als je een product tweemaal probeert toe te voegen:
2
3
SELECT api.sp_add_product( iPod Nano 20GB, 299.95); // Gaat fout: naam bestaat al
SELECT api.sp_add_product( iPod Nano 40GB, 399.95);
D.m.v. RAISE EXCEPTION kun je de cliënt op de hoogte stellen dat er een fout is opgetreden, een kleine opmerking moet hier geplaatst worden: een RAISE EXCEPTION genereert altijd dezelfde errorcode (P0001), het is niet mogelijk om eigen errorcodes te raisen.
Als laatste regel wordt aangegeven in welke procedurele taal de functie wordt geschreven, in ons geval "plpgsql", vervolgens wordt met het woord "VOLATILE" aangegeven wat het gedrag van de functie is: Volatile kun je vertalen naar veranderlijk. De functie mag de database veranderen, de return waarde kan veranderen naar gelang de invoer. Deze informatie gebruikt de optimizer van PostgreSQL. Andere opties zijn "IMMUTABLE" (onveranderlijk) en "STABLE" (spreekt voor zich).
Zie ook http://www.postgresql.org/docs/8.1/interactive/xfunc-volatility.html voor een uitgebreidere uitleg.
TIP:
Je zou ervoor kunnen kiezen om bij een opgetreden fout een eigen procedure aan kunnen roepen met een code welke de foutmelding uit een tabel leest, op deze manier zou je dus een lijst met eigen errorcodes kunnen definiëren welke je middels die tabel koppelt aan een tekstuele verklaring, hiermee maak je het systeem ook weer meer schaalbaar, het vertalen van databasemeldingen is dan bijvoorbeeld nog een peulenschil!
Voor meer informatie over de gebruikte technieken bekijk deze links:
Voor een uitleg over PL/pgSQL, de Procedural Language van PostgreSQL:
http://www.postgresql.org/docs/current/static/plpgsql.html
Voor een uitleg over Exception:
http://www.postgresql.org/docs/current/static/plpgsql-errors-and-messages.html
Voor overzicht van error-codes:
http://www.postgresql.org/docs/current/interactive/errcodes-appendix.html
Voor een uitleg van Control Structures:
http://www.postgresql.org/docs/current/interactive/plpgsql-control-structures.html
Inhoudsopgave
- Inleiding
- Voorbeeldcase
- Schema’s
- SQL Code en relaties
- Stored Procedures en het gebruik hiervan in je app
- Toevoegen van een order
- Voorbeeld van het gebruik in PHP
- Afsluiting