Tutorials
Meer doen met PostgreSQL [2]
Een tutorial welke dieper ingaat op PostgreSQL
Pagina 1
Inleiding
Daar istie dan: Meer doen met PostgreSQL, part two
Na een lange tijd van drukte heb ik een kort moment waarin het wat rustiger is, daar geniet je dan natuurlijk met volle teugen van, een half uur ongeveer, dan slaat de verveling toe en dat leek me nou eens een mooie aanleiding om dit deel 2 te schrijven :)
Door middel van deze tutorial wil ik eens wat meer aandacht besteden aan de volgende onderwerpen:
- Schema’s
- Beveiliging, schaalbaarheid en consistentie
- Relaties
- Stored Procedures (“SP’s”) in PL/pgSQL programmeren, inclusief wat voorbeeldjes
De tutorial is bedoeld voor PostgreSQL 8 en hoger, hou hier rekening mee, sommige zaken werken niet in bijvoorbeeld versie 7.4. Per onderdeel worden links gegeven naar relevante informatie.
Ik hoop van harte dat je interesse geprikkeld wordt door deze tutorial en dat je ermee aan de slag gaat, stel je vragen a.u.b. op deze pagina zodat iedereen mee kan lezen en leren :)
Vergeet ook deel 1 niet te lezen: http://www.phphulp.nl/php/tutorials/3/371/
Na een lange tijd van drukte heb ik een kort moment waarin het wat rustiger is, daar geniet je dan natuurlijk met volle teugen van, een half uur ongeveer, dan slaat de verveling toe en dat leek me nou eens een mooie aanleiding om dit deel 2 te schrijven :)
Door middel van deze tutorial wil ik eens wat meer aandacht besteden aan de volgende onderwerpen:
- Schema’s
- Beveiliging, schaalbaarheid en consistentie
- Relaties
- Stored Procedures (“SP’s”) in PL/pgSQL programmeren, inclusief wat voorbeeldjes
De tutorial is bedoeld voor PostgreSQL 8 en hoger, hou hier rekening mee, sommige zaken werken niet in bijvoorbeeld versie 7.4. Per onderdeel worden links gegeven naar relevante informatie.
Ik hoop van harte dat je interesse geprikkeld wordt door deze tutorial en dat je ermee aan de slag gaat, stel je vragen a.u.b. op deze pagina zodat iedereen mee kan lezen en leren :)
Vergeet ook deel 1 niet te lezen: http://www.phphulp.nl/php/tutorials/3/371/
Pagina 2
Voorbeeldcase
Deze tutorial werkt met een voorbeeld: Een heel eenvoudige sales-applicatie waarmee werknemers orders kunnen maken. De volgende objecten zijn aanwezig:
- Gebruiker (de werknemers)
- Producten
- Orders
Een order bestaat uit 1 of meer orderregels en heeft een bepaalde status. Een werknemer kan 0 of meer orders hebben.
De PHP-cliënt laat ik voor een groot deel achterwege, hoe je dit invult is niet bijster interessant, waar nodig licht ik de acties toe met PHP-code maar ik gok dat de gemiddelde lezer hier wel een beeld van heeft. Indien dit niet zo is hoor ik dat uiteraard graag.
Hier een eenvoudig model van de case:
- Gebruiker (de werknemers)
- Producten
- Orders
Een order bestaat uit 1 of meer orderregels en heeft een bepaalde status. Een werknemer kan 0 of meer orders hebben.
De PHP-cliënt laat ik voor een groot deel achterwege, hoe je dit invult is niet bijster interessant, waar nodig licht ik de acties toe met PHP-code maar ik gok dat de gemiddelde lezer hier wel een beeld van heeft. Indien dit niet zo is hoor ik dat uiteraard graag.
Hier een eenvoudig model van de case:
Pagina 3
Schema’s
In PostgreSQL (en de meeste andere databasesystemen) wordt gewerkt met zgn. schema’s. Een schema is onderdeel van je database, onder het schema definieer je de tabellen, datatypes, views, procedures etc. Je kunt in 1 database meerdere schema’s definiëren, per schema kun je verschillende rechten uitdelen.
Zo zou je dus bijvoorbeeld kunnen denken aan de volgende structuur voor deze applicatie:
- Een schema waarin alle data wordt opgeslagen, het useraccount van je applicatie krijgt hier per definitie géén toegang (Het base schema )
- Een schema waarin je de API bouwt, de API leest en schrijft naar het schema met de data. Het useraccount van je applicatie geef je alleen rechten op het API-schema.
Met bovenstaande structuur bouw je dus een enorm stuk veiligheid in. Vanuit de applicatie kan men theoretisch gezien nooit verder komen als het API-schema, de data blijft gewaarborgd en is slechts te benaderen/manipuleren via SP’s welke je in de API hebt gedefinieerd.
Je kunt via SQL bepalen welk schema je wilt benaderen, hieronder een voorbeeld waarmee gegevens uit het "base"-schema worden gelezen, waarin de daadwerkelijke tabellen staan opgeslagen:
Je kunt PostgreSQL ook forceren gebruik te maken van (een volgorde van bepaalde) schema‘s. Met behulp van de variabele search_path bepaal je de volgorde waarin entiteiten worden gezocht:
Deze tutorial maakt gebruik van 2 schema’s: base en api. Zorg ervoor dat deze schema’s bestaan en dat je hier voldoende rechten op hebt.
Meer informatie over schema’s vind je op http://www.postgresql.org/docs/current/static/ddl-schemas.html
Zo zou je dus bijvoorbeeld kunnen denken aan de volgende structuur voor deze applicatie:
- Een schema waarin alle data wordt opgeslagen, het useraccount van je applicatie krijgt hier per definitie géén toegang (Het base schema )
- Een schema waarin je de API bouwt, de API leest en schrijft naar het schema met de data. Het useraccount van je applicatie geef je alleen rechten op het API-schema.
Met bovenstaande structuur bouw je dus een enorm stuk veiligheid in. Vanuit de applicatie kan men theoretisch gezien nooit verder komen als het API-schema, de data blijft gewaarborgd en is slechts te benaderen/manipuleren via SP’s welke je in de API hebt gedefinieerd.
Je kunt via SQL bepalen welk schema je wilt benaderen, hieronder een voorbeeld waarmee gegevens uit het "base"-schema worden gelezen, waarin de daadwerkelijke tabellen staan opgeslagen:
SELECT id, orderdate FROM base.tbl_order_head WHERE user_id=1;
Je kunt PostgreSQL ook forceren gebruik te maken van (een volgorde van bepaalde) schema‘s. Met behulp van de variabele search_path bepaal je de volgorde waarin entiteiten worden gezocht:
SET search_path base, api;
// tbl_order_head zal nu eerst worden gezocht in het base-schema en vervolgens in api-schema
SELECT id, orderdate FROM tbl_order_head;
SET search_path api;
// tbl_order_head wordt nu niet gevonden
SELECT id, orderdate FROM tbl_order_head;
Deze tutorial maakt gebruik van 2 schema’s: base en api. Zorg ervoor dat deze schema’s bestaan en dat je hier voldoende rechten op hebt.
Meer informatie over schema’s vind je op http://www.postgresql.org/docs/current/static/ddl-schemas.html
Pagina 4
SQL Code en relaties
In deze case is er sprake van 4 tabellen:
- tbl_order_head
- tbl_order_line
- tbl_product
- tbl_user
Uit het diagram is de volgende informatie te halen:
- Een gebruiker heeft 0 of meer orders. Je kunt dus aannemen dat er in de tabel tbl_order_head een vreemde sleutel aanwezig is welke verwijst naar id in tbl_user. NULL is niet toegestaan, er MOET dus een GELDIG user_id in staan.
- Een order bestaat uit orderregels. Je kunt dus aannemen dat er in de tabel tbl_order_line een vreemde sleutel aanwezig is welke verwijst naar id in tbl_order_head. NULL is niet toegestaan, er MOET dus een GELDIG order_id in staan. Regels die niet aan een order hangen zijn zinloos :)
Een vreemde sleutel (foreign key) dient hetzelfde datatype te hebben als de primaire sleutel (primary key) waarnaar verwezen wordt. In de declaratie van de referentie kun je opgeven wat het gedrag moet zijn bij bepaalde acties:
Stel dat ik een gebruiker weggooi die nog orders heeft: als er geen relatie zou zijn kan dit "gewoon", je hebt dan echter wel een inconsistente database: In tbl_order_head kan worden verwezen naar een niet-bestaand user_id. Met een relatie kun je dit op verschillende manieren oplossen:
1. Weiger de verwijderactie: ON DELETE RESTRICT, de gebruiker wordt niet verwijderd. Resultaat: de database blijft consistent
2. Verwijder de gekoppelde orders: ON DELETE CASCADE, de gebruiker wordt verwijderd en de orders welke gekoppeld zijn aan de betreffende gebruiker. Resultaat: de database blijft consistent.
3. Pas het user_id in tbl_order_head aan naar NULL: ON DELETE SET NULL, de gebruiker wordt verwijderd, de orders blijven bestaan maar zijn niet meer gekoppeld aan een gebruiker. Resultaat: de database blijft consistent.
Deze opties bestaan voor zowel UPDATE als DELETE-acties.
Het uitgangspunt van deze relaties is dus de data consistent te laten blijven.
In deze case heb ik voor de relatie tussen gebruikers en orders voor optie 1 gekozen, het is dus niet mogelijk een gebruiker te verwijderen indien deze orders heeft gemaakt. Bij de relatie tussen orders en orderregels heb ik voor optie 2 gekozen, bij het verwijderen van een order worden de bijbehorende orderregels dus ook verwijderd.
Uit de SQL-dump zal ik de DDL (Data Definition Language) van tbl_order_line uitleggen:
Er liggen in deze tabel 2 vormen van beperkingen (CONSTRAINT) op de kolommen: id wordt gemarkeerd als een primaire sleutel, order_id als een vreemde sleutel. Bij de declaratie van de vreemde sleutel geef je op naar welke tabel en kolom je verwijst, in dit geval verwijst order_id naar het veld id in tbl_order_head. De ON DELETE CASCADE en ON UPDATE CASCADE zorgt ervoor dat records uit tbl_order_line worden verwijderd als een order uit tbl_order_head wordt verwijderd, of dat de order_id’s in tbl_order_line worden bijgewerkt als het id in tbl_order_head wordt bijgewerkt, dit laatste is onwaarschijnlijk, maar geeft nog steeds garantie op consistente data.
De tekst “DEFERRABLE” kun je vertalen naar uitstelling of opschorting. Via dit statement geef je aan of een overtreding van een foreign key relatie mag worden uitgesteld (bijvoorbeeld tijdens een transactie), de standaard waarde is “NOT DEFERRABLE” en geeft dus aan dat een overtreding meteen ingaat wanneer een ongeldige executie plaatsvindt.
Meer informatie over deze zgn. "foreign key relaties" vind je op: http://www.postgresql.org/docs/8.2/static/ddl-constraints.html#DDL-CONSTRAINTS-FK
Meer informatie over de create table statements vind je op:
http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html
- tbl_order_head
- tbl_order_line
- tbl_product
- tbl_user
-- Zorg er eerst voor dat de schema’s base en api bestaan, zie voorgaand hoofdstuk
-- De table tbl_product
CREATE TABLE base.tbl_product (
id SERIAL,
title VARCHAR(255),
price NUMERIC,
CONSTRAINT tbl_product_pkey PRIMARY KEY(id),
CONSTRAINT tbl_product_title_key UNIQUE(title)
);
-- De table tbl_user
CREATE TABLE base.tbl_user (
id SERIAL,
username VARCHAR(255),
CONSTRAINT tbl_user_pkey PRIMARY KEY(id)
);
-- De table tbl_order_head
CREATE TABLE base.tbl_order_head (
id SERIAL,
user_id INTEGER NOT NULL,
orderdate DATE DEFAULT now(),
CONSTRAINT tbl_order_head_pkey PRIMARY KEY(id),
CONSTRAINT tbl_order_head_fk FOREIGN KEY (user_id)
REFERENCES base.tbl_user(id)
ON DELETE RESTRICT
ON UPDATE RESTRICT
NOT DEFERRABLE
);
-- De table tbl_order_line
CREATE TABLE base.tbl_order_line (
id SERIAL,
order_id INTEGER NOT NULL,
product_title VARCHAR(255),
product_price NUMERIC,
number_items INTEGER,
CONSTRAINT tbl_order_line_pkey PRIMARY KEY(id),
CONSTRAINT tbl_order_line_fk FOREIGN KEY (order_id)
REFERENCES base.tbl_order_head(id)
ON DELETE CASCADE
ON UPDATE CASCADE
NOT DEFERRABLE
);
Uit het diagram is de volgende informatie te halen:
- Een gebruiker heeft 0 of meer orders. Je kunt dus aannemen dat er in de tabel tbl_order_head een vreemde sleutel aanwezig is welke verwijst naar id in tbl_user. NULL is niet toegestaan, er MOET dus een GELDIG user_id in staan.
- Een order bestaat uit orderregels. Je kunt dus aannemen dat er in de tabel tbl_order_line een vreemde sleutel aanwezig is welke verwijst naar id in tbl_order_head. NULL is niet toegestaan, er MOET dus een GELDIG order_id in staan. Regels die niet aan een order hangen zijn zinloos :)
Een vreemde sleutel (foreign key) dient hetzelfde datatype te hebben als de primaire sleutel (primary key) waarnaar verwezen wordt. In de declaratie van de referentie kun je opgeven wat het gedrag moet zijn bij bepaalde acties:
Stel dat ik een gebruiker weggooi die nog orders heeft: als er geen relatie zou zijn kan dit "gewoon", je hebt dan echter wel een inconsistente database: In tbl_order_head kan worden verwezen naar een niet-bestaand user_id. Met een relatie kun je dit op verschillende manieren oplossen:
1. Weiger de verwijderactie: ON DELETE RESTRICT, de gebruiker wordt niet verwijderd. Resultaat: de database blijft consistent
2. Verwijder de gekoppelde orders: ON DELETE CASCADE, de gebruiker wordt verwijderd en de orders welke gekoppeld zijn aan de betreffende gebruiker. Resultaat: de database blijft consistent.
3. Pas het user_id in tbl_order_head aan naar NULL: ON DELETE SET NULL, de gebruiker wordt verwijderd, de orders blijven bestaan maar zijn niet meer gekoppeld aan een gebruiker. Resultaat: de database blijft consistent.
Deze opties bestaan voor zowel UPDATE als DELETE-acties.
Het uitgangspunt van deze relaties is dus de data consistent te laten blijven.
In deze case heb ik voor de relatie tussen gebruikers en orders voor optie 1 gekozen, het is dus niet mogelijk een gebruiker te verwijderen indien deze orders heeft gemaakt. Bij de relatie tussen orders en orderregels heb ik voor optie 2 gekozen, bij het verwijderen van een order worden de bijbehorende orderregels dus ook verwijderd.
Uit de SQL-dump zal ik de DDL (Data Definition Language) van tbl_order_line uitleggen:
-- De table tbl_order_line
CREATE TABLE base.tbl_order_line (
id SERIAL,
order_id INTEGER NOT NULL,
product_title VARCHAR(255),
product_price NUMERIC,
number_items INTEGER,
CONSTRAINT tbl_order_line_pkey PRIMARY KEY(id),
CONSTRAINT tbl_order_line_fk FOREIGN KEY (order_id)
REFERENCES base.tbl_order_head(id)
ON DELETE CASCADE
ON UPDATE CASCADE
NOT DEFERRABLE
);
Er liggen in deze tabel 2 vormen van beperkingen (CONSTRAINT) op de kolommen: id wordt gemarkeerd als een primaire sleutel, order_id als een vreemde sleutel. Bij de declaratie van de vreemde sleutel geef je op naar welke tabel en kolom je verwijst, in dit geval verwijst order_id naar het veld id in tbl_order_head. De ON DELETE CASCADE en ON UPDATE CASCADE zorgt ervoor dat records uit tbl_order_line worden verwijderd als een order uit tbl_order_head wordt verwijderd, of dat de order_id’s in tbl_order_line worden bijgewerkt als het id in tbl_order_head wordt bijgewerkt, dit laatste is onwaarschijnlijk, maar geeft nog steeds garantie op consistente data.
De tekst “DEFERRABLE” kun je vertalen naar uitstelling of opschorting. Via dit statement geef je aan of een overtreding van een foreign key relatie mag worden uitgesteld (bijvoorbeeld tijdens een transactie), de standaard waarde is “NOT DEFERRABLE” en geeft dus aan dat een overtreding meteen ingaat wanneer een ongeldige executie plaatsvindt.
Meer informatie over deze zgn. "foreign key relaties" vind je op: http://www.postgresql.org/docs/8.2/static/ddl-constraints.html#DDL-CONSTRAINTS-FK
Meer informatie over de create table statements vind je op:
http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html
Pagina 5
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:
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:
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
- 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:
CREATE OR REPLACE FUNCTION api.sp_add_product("varchar", "numeric") RETURNS bool AS
$$
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:
SELECT api.sp_add_product( ‘iPod Nano 20GB’, 199.95);
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
Pagina 6
Toevoegen van een order
Nu de eerste stapjes in PL/pgSQL zijn gemaakt leek het me zinvol om een nieuwe stored procedure te maken voor het aanmaken van een order. Het toevoegen bestaat uit 2 verschillende delen:
- Het invoeren van de gegevens in de order-kop-tabel
- Het toevoegen van de producten aan een order
Bij het toevoegen van producten aan een order worden ook de beschrijving + prijs opgeslagen in de tabel tbl_order_line. Op die manier blijft correcte informatie bewaard als een product wordt verwijderd of de prijs / omschrijving wijzigt.
Dit betekent dus dat er 2 SP’s worden gemaakt.
Creëren van een order:
Toevoegen van producten aan een order:
- Het invoeren van de gegevens in de order-kop-tabel
- Het toevoegen van de producten aan een order
Bij het toevoegen van producten aan een order worden ook de beschrijving + prijs opgeslagen in de tabel tbl_order_line. Op die manier blijft correcte informatie bewaard als een product wordt verwijderd of de prijs / omschrijving wijzigt.
Dit betekent dus dat er 2 SP’s worden gemaakt.
Creëren van een order:
CREATE OR REPLACE FUNCTION api.sp_add_order(“numeric”) RETURNS BOOL AS
$$
DECLARE
p_userid ALIAS FOR $1; -- Parameter UserID
BEGIN
-- Voer de INSERT uit op de order-kop tabel
INSERT INTO base.tbl_order_head(user_id,orderdate) VALUES(p_userid, NOW());
RETURN TRUE;
EXCEPTION
WHEN FOREIGN_KEY_VIOLATION THEN
RAISE EXCEPTION 'Het gegeven UserID bestaat niet';
RETURN FALSE;
WHEN OTHERS THEN
RAISE EXCEPTION 'Er is een fout opgetreden';
RETURN FALSE;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;
Toevoegen van producten aan een order:
CREATE OR REPLACE FUNCTION "api"."sp_add_product_to_order" (numeric, numeric, numeric) RETURNS boolean AS
$$
DECLARE
p_orderid ALIAS FOR $1; -- Parameter OrderID
p_productid ALIAS FOR $2; -- Parameter ProductID
p_aantal ALIAS FOR $3; -- Parameter aantal
rec RECORD; -- Variabele om resultaten in te fetchen
BEGIN
-- Haal de gegevens van het product op en sla deze op in de variabele rec
FOR rec IN SELECT * FROM base.tbl_product WHERE id=p_productid LOOP
-- Hier worden de gegevens uit rec ingevoerd in tbl_order_line
INSERT INTO base.tbl_order_line
(
order_id,
product_title,
product_price,
number_items
)
VALUES
(
p_orderid,
rec.title,
rec.price,
p_aantal
);
END LOOP;
RETURN TRUE;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;
Pagina 7
Voorbeeld van het gebruik in PHP
Dit voorbeeld maakt gebruik van het krachtige en mooie PDO (PHP Data Objects).
Een script voor deze acties zou er als volgt uit kunnen zien:
!!! Let op, het is dus géén volledige code maar dient slechts ter illustratie en is getest met dummy informatie !!!
<?php
error_reporting( E_ALL );
session_start( );
define( 'DB_HOST', 'localhost' );
define( 'DB_NAME', '' );
define( 'DB_USERNAME', '' );
define( 'DB_PASSWORD', '' );
$_SESSION['UserID'] = 3;
$_SESSION['order'] = array
(
4=>12,
6=>50
);
try
{
echo '<pre>';
// Maak een nieuw DB-object aan
$db = new PDO( 'pgsql:host='.DB_HOST.';dbname='.DB_NAME, DB_USERNAME, DB_PASSWORD );
// Start een transactie
$db->beginTransaction( );
// Voeg de order toe, geef ons UserID uit de sessie mee
if( !$db->Query( "SELECT api.sp_add_order( ".$_SESSION['UserID']." )" ) )
{
print_r( $db->errorInfo( ) );
exit;
}
// Bepaal het gegenereerde OrderID
$iOrderID = $db->lastInsertId( 'base.tbl_order_head_id_seq' );
echo 'Het gegenereerde OrderID is: '. $iOrderID;
// Voeg de regels toe, lees de producten uit een sessie
foreach( $_SESSION['order'] as $iProductID=>$iNumberItems )
{
$qryInsertProduct = "SELECT api.sp_add_product_to_order (".$iOrderID.", ".$iProductID.", ".$iNumberItems.")";
if( !$db->Query( $qryInsertProduct ) )
{
print_r( $db->errorInfo( ) );
exit;
}
}
// Maak de wijzigingen definitief
$db->commit( );
echo '</pre>';
}
catch( Exception $e )
{
echo 'Error!: ' . $e->getMessage( ) . '<br/>';
}
?>
Is dit gelukt? Gefeliciteerd!!! Met deze kennis zou je voldoende handvatten moeten hebben om de rest van je API zelf te bouwen :))
Een script voor deze acties zou er als volgt uit kunnen zien:
!!! Let op, het is dus géén volledige code maar dient slechts ter illustratie en is getest met dummy informatie !!!
<?php
error_reporting( E_ALL );
session_start( );
define( 'DB_HOST', 'localhost' );
define( 'DB_NAME', '' );
define( 'DB_USERNAME', '' );
define( 'DB_PASSWORD', '' );
$_SESSION['UserID'] = 3;
$_SESSION['order'] = array
(
4=>12,
6=>50
);
try
{
echo '<pre>';
// Maak een nieuw DB-object aan
$db = new PDO( 'pgsql:host='.DB_HOST.';dbname='.DB_NAME, DB_USERNAME, DB_PASSWORD );
// Start een transactie
$db->beginTransaction( );
// Voeg de order toe, geef ons UserID uit de sessie mee
if( !$db->Query( "SELECT api.sp_add_order( ".$_SESSION['UserID']." )" ) )
{
print_r( $db->errorInfo( ) );
exit;
}
// Bepaal het gegenereerde OrderID
$iOrderID = $db->lastInsertId( 'base.tbl_order_head_id_seq' );
echo 'Het gegenereerde OrderID is: '. $iOrderID;
// Voeg de regels toe, lees de producten uit een sessie
foreach( $_SESSION['order'] as $iProductID=>$iNumberItems )
{
$qryInsertProduct = "SELECT api.sp_add_product_to_order (".$iOrderID.", ".$iProductID.", ".$iNumberItems.")";
if( !$db->Query( $qryInsertProduct ) )
{
print_r( $db->errorInfo( ) );
exit;
}
}
// Maak de wijzigingen definitief
$db->commit( );
echo '</pre>';
}
catch( Exception $e )
{
echo 'Error!: ' . $e->getMessage( ) . '<br/>';
}
?>
Is dit gelukt? Gefeliciteerd!!! Met deze kennis zou je voldoende handvatten moeten hebben om de rest van je API zelf te bouwen :))
Pagina 8
Afsluiting
Mijn dank gaat uit naar Jan Koehoorn en mijn collega Johan Wiegel voor het "prepublishing proces" :) :)
Hier nog een paar linkjes:
Algemeen: http://www.yapf.net
Normalisatie (Klaasjan Boven) http://www.phphulp.nl/php/tutorials/3/426/
PostgreSQL: Een inleiding (PHPerik) http://www.phphulp.nl/php/tutorials/3/334/
Voor handige PostgreSQL-management tools, bekijk de volgende links:
SQL Manager: http://www.sqlmanager.net/en/products/postgresql/manager (Lite versie is gratis)
pgAdmin: http://www.pgadmin.org/
Hier nog een paar linkjes:
Algemeen: http://www.yapf.net
Normalisatie (Klaasjan Boven) http://www.phphulp.nl/php/tutorials/3/426/
PostgreSQL: Een inleiding (PHPerik) http://www.phphulp.nl/php/tutorials/3/334/
Voor handige PostgreSQL-management tools, bekijk de volgende links:
SQL Manager: http://www.sqlmanager.net/en/products/postgresql/manager (Lite versie is gratis)
pgAdmin: http://www.pgadmin.org/
Reacties
0