Google+ Followers

zaterdag 29 december 2012

Recursie met CTE's


Het ophalen van een pad in een simpele pappa/kindje hierarchie vereist recursie en recursie vereist veel queries, tenminste als je database zelf geen recursie ondersteunt.

PostgreSQL ondersteunt recursie.

Dit maakt het mogelijk om de paden op te halen via een simpel CTE statement:

DROP TABLE IF EXISTS treedemo;
CREATE TABLE treedemo (nodeid integer, parentnodeid integer, title varchar(25));
INSERT INTO treedemo VALUES
(1,null,'root'), (2,1,'hardware'),(3,2,'intel'),(4,1,'software'),(5,4,'linux'),(6,4,'windows');


WITH RECURSIVE tree AS
(
SELECT treedemo.nodeid, treedemo.parentnodeid, treedemo.title, CAST(treedemo.title AS TEXT) AS path FROM treedemo WHERE nodeid=1
UNION ALL
SELECT treechildren.nodeid, treechildren.parentnodeid, treechildren.title, tree.path || '/' || treechildren.title AS path
FROM treedemo AS treechildren
INNER JOIN tree ON treechildren.parentnodeid = tree.nodeid
)
SELECT * FROM tree;

Ok, simpel als je weet hoe CTE's werken. Alles tussen de halen achter AS is de CTE en daarin gebeurt de magie.

Die magie bestaat in een SELECT die het root-record ophaalt, het record waar het pad begint. In dit geval de root-node.
Aal die query wordt een SELECT statement gejoined die record ophaalt op basis van een JOIN met de CTE zelf. Dat is dus de recursie, de JOIN pakt alle records waarvan het parentid gelijk is aan het nodeid wat eerder in de CTE is geselecteerd.

Gevolg, een net setje records met een slash-separated string met de titles van de nodes. Ditzelfde geintje kun je doen voor de ID's als je wilt.

zondag 23 december 2012

Tipje, packt-korting

Op de valreep nog een tipje, packt-publishing, uitgever van diverse goede naslagwerken over o.a. PostgreSQL, houdt tot begin januari een kortingsactie waardoor eBooks weg mogen voor slechts  $5 per stuk.

Kun je je eReader mooi nog even volgooie voor een derde van de prijs.

http://www.packtpub.com/

woensdag 24 oktober 2012

De kip, het ei en de forkeign-key constriant.

Dit is min of meer onder het motto "weet u het nog?".

Tabellen die zichzelf refereren via een FK kunnen in de loop der tijd in een kip-en-ei situatie komen, een eerder gemaakt record kan verwijzen naar een later gemaakt record. Gevolg: bij het restoren of kopieren van de data zal de database klagen dat het id waarnaar wordt verwezen niet bestaat. Logisch, want dat record komt pas later. Sorteren heeft in deze situaties geen zin want er is geen volgorde te bedenken waarin en record tegelijk nu en pas later wordt gemaakt.

De oplossing is een "ohja" momentje; je kunt tijdelijk de FK constraints uitzetten. Sommige databases kunnen dat met een SET commando, anderen kunnen FK's disablen met een ALTER statement.

Vlak voor het importeren zet je de FK's van de tabel uit, en er vlak na weer aan.

zondag 7 oktober 2012

Ontbrekende datums aanvullen.

Het aloude probleem van  het spoofen van records die er niet zijn.

In PostgreSQL kan dat met generate_series()

SELECT '2012-01-01'::date + (i * INTERVAL '1 day')
FROM GENERATE_SERIES(1,5) AS i;

Dat genereert vijf records beginnend op 2012-01-01.


In MySQL kan hetzelfde met een hulptabel die gewoon een serie getallen bevat;

CREATE TABLE help_numbers (i integer);
INSERT INTO help_numbers (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

..en dat dan tot zoveel nummers als je nodig denkt te gaan hebben, zo'n tabel kost niets dus schroom niet om er een paar duizend nummers in te zetten.

Dan kun je praktisch hetzelfde geintje uithalen als in PostgreSQL:

SELECT '2012-01-01' + INTERVAL i DAY
FROM help_numbers WHERE i<5;

donderdag 16 augustus 2012

Waarom MySQL zulk bocht is, deel 56436

Elke keer dat ik denk dat ik overdrijf met mijn afkeer van MySQL dan gebeurt er iets waardoor ik mij weer geheel gerechtigd voel.

MySQL ondersteunt meerdere storage engines, zoals MyISAM en InnoDB. Ik boeken vol schrijven over waarom dat een "Bad-Thing (tm)" is, maar zolang je netjes het juiste tabeltype meegeeft bij je CREATE statement gaat het best aardig.

Voel je hem aankomen?

Vandaag maak ik een dump van een MySQL database en heb het lef om daar '--skip-opt' aan mee te geven, om te voorkomen dat ik o.a. de extended-insert syntax krijg.

Bij een restore van die dump spat de boel natuurlijk uit elkaar met "FULLTEXT is not supported by this table type". WFT? Nou '--skip-opt' haalt dus ook bij het CREATE TABLE statement het engine-type weg.

Zonder engine-type kiest MySQL er zelf een uit en dat was vroeger MyISAM, maar is nu InnoDB en InnoDB doet niet aan FULLTEXT (een van vele "Bad-Thing (tm)" redenen) en boem, weg database.

Wat kun je daaraan doen? nou geen --skip-opt, maar zelf per optie aangeven wat je wel en niet wilt, zodat MySQL een zinnigem bruikbare dump gaat geven.


yay!

maandag 16 juli 2012

Spelen met MySQL...

- MySQL heeft een 'SQL-MODE' waarmee je specifiek kunt toestaan om ongeldige datums op te slaan. Volgens de handleiding is dat handig voor als je vanaf een website een datum in drie dropdowns laat opbouwen en je de datum "exactly as entered" wilt opslaan. Dat zal wel zijn voor de mensen die op 31 februari jarig zijn ofzo?

- MySQL vereist dat je bij het maken van een trigger de delimiter van ';' omzet naar iets anders, omdat anders de delimiter van statements binnen de trigger kan worden gezien als einde-statement. Helaas accepteert de API die delimiter dan weer niet.

- NOT-NULL validatie gebeurt voordat de triggers worden uitgevoerd, dus je krijgt eerst een warning (of error, afhanklijk van hoe de wind staat... nouja, de SQL-mode) en als je BEFORE-trigger daarna het record valide maakt wordt het record wel ingevoerd. Dus je hebt tegelijk een foutmelding en een correct ingevoerd record.

woensdag 4 juli 2012

MySQL's triggers zijn brak, deel 1

Nieuwe baan, nieuwe omgeving, wederom MySQL.

Vandaag een kwartiertje gevloekt over een SQL dump die niet wilde restoren. Althans, niet naar *mijn* database, wel naar een database met dezelfde naam als waar de dump van was getrokken.

Wat blijkt; als je in MySQL een trigger defineert met de databasenaam in de triggernaam, dus iets als::

create trigger speeltuin.flip before insert on foo for each row begin end;

Dan wordt de databasenaam ook meegegeven in de dump en bij het restoren gaat de MySQL doodleuk die trigger aanmaken in de genoemde database, *NIET* in de database waar je mee verbonden bent.

MySQL is en blijft een lachertje.

zondag 24 juni 2012

PgSQL EXECUTE USING

Dynamische queries binnen plpsql functions moesten vroeger worden voorzien van escape functies, maar sinds 8.4 is er EXECUTE ... USING velden

Dit werkt op dezelfde manier als een stored function en zorgt voor het escapen.


EXECUTE 'SELECT to_date($1, $2)' USING '17-DEC-80', 'DD-MON-YY';

De PHPStorm waait

PHPStorm, tsja wat kan ik erover zeggen, 't is gewoon heerlijk. Alles wat Zend Studio kan, maar dan snel en goed. Vooral de code-formatter is erg prettig met uitlijnen van parameters enzo.

Een ander punt wat ik zeker moet noemen is het debuggen. Configje maken, xdebug configureren en gaan met die breakpoints!

En dan is het nog betaalbaar ook. Bye bye zend!

dinsdag 22 mei 2012

PFCongres 2012

Op het PFCongres 2012 gaat ondergetekende en verhaal vertellen over de dingen die PostgreSQL maken tot 's werelds meest geavanceerde opensource database.

In vogelvlucht en aan de hand van hopelijk herkenbare praktijkvoorbeelden ga ik laten zien wat PgSQL allemaal kan en hoe die features je kunnen helpen om ingewikkelde processen te vereenvoudigen, onderhoud te vergemakkelijken en uiteindelijk performance te verhogen.

Aan bod komt de taaie kost over datatypen, logs en crashrecovery, de smeuigere hap over SQL functionaliteit, en de neusjes van de zalm over document-based opslag met o.a. XML, koppelingen met externe data en een uittreksel van "Website in a database".


zondag 18 maart 2012

Tijden afronden op kwartieren

Het gebeurt zo vaak een het is zo simpel.

PostgrreSQL:

SELECT TO_CHAR(TO_TIMESTAMP(ROUND(DATE_PART('EPOCH', NOW())/(15*60))*(15*60)), 'HH24:MI:SS') 

In MySQL:
 SELECT FROM_UNIXTIME(ROUND(UNIX_TIMESTAMP(now())/(15*60))*15*60); 

zaterdag 17 maart 2012

Performance monitoring met pg_stat_statements

Een van de ondergeschoven kindjes in de wereld van de performance monitorring is pg_stat_statements http://www.postgresql.org/docs/9.1/static/pgstatstatements.html

Zoals op die pagina ook gedemonstreerd wordt kun je hiermee loggen welke queries er zijn uitgevoerd en hoe lang dat duurde.

Een van de prettige dingen hiervan is dat het telt hoevaak elke query is uitgevoerd en hoe lang dat in totaal heeft geduurd. Dit levert interessante informatie op omdat een query die per run heel snel is toch een performance probleem kan zijn als hij buitensporig vaak wordt aangeroepen.

zaterdag 4 februari 2012

Koppelingen tellen

Zoals gewoonlijk in antwoord op een PFZ topic, de vraag "hoe vind ik boeken die wel door meneer X zijn geschreven, maar waar meneer Y niet aan heeft meegewerkt?"

In de database gaat dit allen door een koppeltabel te maken tussen de boeken en de schrijvers, met daarin één record per schrijver per boek.

De oplossing voor het probleem is dan ook eenvoudig; gewoon tellen.


CREATE TEMPORARY TABLE data (x int, y int);
INSERT INTO data(x,y) VALUES(1,21), (1,22);
INSERT INTO data(x,y) VALUES(2,21), (2,23), (2,24);
INSERT INTO data(x,y) VALUES(3,21), (2,23), (2,22);

SELECT
x
, COUNT(*) AS num_authors
, SUM(CAST(y IN (21,22) AS INTEGER)) AS matching_authors
, SUM(CAST(y IN (23) AS INTEGER)) AS unwanted_authors
FROM data
GROUP BY x;




matching_authors geeft nu aan hoeveel van de schrijvers vallen in de verzameling (21,22) en unwanted laat zien hoeveel er zitten in de verzameling (22). Hierin kun je dus filteren met HAVING


SELECT
x
, COUNT(*) AS num_authors
, SUM(CAST(y IN (21,22) AS INTEGER)) AS matching_authors
, SUM(CAST(y IN (23) AS INTEGER)) AS unwanted_authors
FROM data
GROUP BY x
HAVING
SUM(CAST(y IN (21,22) AS INTEGER)) = 2
AND
SUM(CAST(y IN (23) AS INTEGER)) = 0;

Dat is: beide schrijvers uit (21,22) moeten gevonden zijn, en uit de verzameling (22) mag niemand gevonden worden.

De SUM() in het select deel staan hier zuiver voor debug doeleinden, in productie kun je volstaan met de HAVING.

maandag 16 januari 2012

ELT

Zo zit je uren te k*tten om een alternatief te vinden voor een van MySQL's weinige goede functies, zo vind je....

CREATE OR REPLACE FUNCTION elt(int, VARIADIC text[])
RETURNS text AS $$
  SELECT $2[$1];
$$ LANGUAGE sql;