Google+ Followers

woensdag 25 december 2013

Why I like PostgreSQL more than MySQL: XML support

PostgreSQL has native XML support. it can store XML documents in a true XML datatype, it can query XML documents using XPATH and it can generate XML documents from tabledata.

How does this help you in real life?

The one area where most of us are forced to deal with XML is a webshop that uses a fullfillment company. All the data about orders and stockupdates are sent back and forth using XML documents. PostgreSQL's XML support makes processing these messages into your database a breeze.

The following example uses a table to store incoming XML documents and a triggered stored function to process their content into a regular table.

DROP FUNCTION IF EXISTS fnc_process_xml() CASCADE;
DROP TABLE IF EXISTS product_history CASCADE;
DROP TABLE IF EXISTS xml_storage CASCADE;

CREATE TABLE xml_storage (id SERIAL, xmldata XML);
CREATE TABLE product_history (product_id INTEGER, eventtype VARCHAR(100), eventdate TIMESTAMP, quantity INTEGER);
CREATE FUNCTION fnc_process_xml() RETURNS TRIGGER AS
$$
BEGIN
INSERT INTO product_history (product_id, eventtype, quantity, eventdate)
SELECT unnest(xpath('/productdata/products/product/id/text()', NEW.xmldata))::text::integer
,unnest(xpath('/productdata/products/product/event/text()', NEW.xmldata))::text
,unnest(xpath('/productdata/products/product/quantity/text()', NEW.xmldata))::text::integer
,TO_TIMESTAMP((xpath('/productdata/@date', NEW.xmldata))[1]::text,'YYYYMMDDHHMISS');
RETURN NEW;
END;
$$
LANGUAGE PLPGSQL;
CREATE TRIGGER tgr_process_xml AFTER INSERT ON xml_storage FOR EACH ROW EXECUTE PROCEDURE fnc_process_xml();

Now you can insert a document into the storage table:

INSERT INTO xml_storage (id, xmldata) VALUES (1, '<productdata date="20131042124415">
<products>
<product><id>4</id><quantity>3</quantity><event>DELIVERY_RECEIVED</event></product>
<product><id>24</id><quantity>-2</quantity><event>SALE</event></product>
</products></productdata>'::XML);
INSERT INTO product_history (product_id, eventtype, quantity, eventdate)
SELECT unnest(xpath('/productdata/products/product/id/text()', xmldata))::text::integer
,unnest(xpath('/productdata/products/product/event/text()', xmldata))::text
,unnest(xpath('/productdata/products/product/quantity/text()', xmldata))::text::integer
,TO_TIMESTAMP((xpath('/productdata/@date', xmldata))[1]::text,'YYYYMMDDHHMISS')
FROM xml_storage;

And presto: the data ends up in the product_history table.

Generating the outgoing XML is also quite simple. This example puts out a simplified XML for a few orders:

DROP TABLE IF EXISTS orders;
CREATE TABLE  ORDERS (order_nr INTEGER, product_id INTEGER, quantity INTEGER, unit_price NUMERIC(8,2), total_price NUMERIC(8,2));
INSERT INTO orders (order_nr, product_id, quantity, unit_price, total_price)
VALUES (1, 3, 1, 2.40,  2.40), (1, 55, 2, 4.00, 8.00), (2, 150, 1, 14.00, 14.00);
WITH tmp_orderxmls AS (
SELECT XMLELEMENT(NAME SALES_ORDER
, XMLATTRIBUTES(order_nr AS NUMBER), XMLAGG(XMLELEMENT(NAME orderline
, XMLELEMENT(NAME ARTICLE_NUMBER, product_id)
, XMLELEMENT(NAME QUANTITY, quantity)
, XMLELEMENT(NAME UNIT_PRICE, unit_price)
, XMLELEMENT(NAME TOTAL_PRICE, total_price)
))) AS order_xml
FROM orders
GROUP BY order_nr)
SELECT XMLELEMENT(NAME SALES_ORDERS, XMLATTRIBUTES(TO_CHAR(CURRENT_TIMESTAMP,'YYYY-MM-DD HH:MI:SS') AS MESSAGE_DATE), XMLAGG(tmp_orderxmls.order_xml))
FROM tmp_orderxmls;

If you also include the filename then you can do more fun things like refuse to process the same file twice, or choose different INSERT queries to process different fileformats.

You could even take it one step further and use 9.3's COPY FROM PROGRAM to call on a binary to make PgSQL fetch the XML document itself, either through some FTP construct or wget: http://www.postgresonline.com/journal/archives/325-Using-wget-directly-from-PostgreSQL-using-COPY-FROM-PROGRAM.html





woensdag 11 december 2013

Meet your new NoSQL database: PostgreSQL

Today I read a databasesoup blogpost by Josh Berkus, informing the world that PostgreSQL has a couple of new extensions that supply noSQL capabilities.

See for you self what he has to say: http://www.databasesoup.com/2013/12/meet-your-new-nosql-database.html

Cool stuff indeed.


zondag 8 december 2013

NOT NULL and DEFAULT

For some reason lots of people define database columns as something like NOT NULL DEFAULT 0.
The idea being that the column should not accept any NULL values, and new records should start with the value of 0.

And indeed, this is exactly how it works in PostgreSQL:

drop table if exists foo;
create table foo (id serial, i int not null default 0);

insert into foo (id, i) values (1, null);

Gives an error for the not null violation.

insert into foo (id) values (1);

Column i gets a valiue of 0.

update foo set i=null;

Gives again an error for the not null violation.


MySQL however...


create table foo (id integer auto_increment primary key, i integer not null default 0);
insert into foo (id,i) values (1,null);
ERROR 1048 (23000): Column 'i' cannot be null

INSERT INTO foo (id) VALUES (1);

Column i is set to 0.

So far so good, but what happens when you insert a NULL through an UPDATE?

UPDATE foo SET i =NULL;
Rows matched: 1  Changed: 0  Warnings: 1

No error, one warning, and i is equal to zero. But what happend? Well MySQL just reset the column back to the default value.

UPDATE foo SET i = 2;

Column i is set to 2, as expected.

UPDATE foo SET i = NULL;
Rows matched: 1  Changed: 0  Warnings: 1

No error, one warning, and i is back to zero. So, if you try to put NULL into the field using an INSERT it will fail on the NOT NULL, but if you try the same using an UPDATE you only get a warning about the NOT NULL and the DEFAULT value is used.

Think about this: your NOT NULL only works sometimes and your data is actuallt changed to the default if you try to remove the current value.



donderdag 5 december 2013

CORS en hoofdbrekens enzo

Browsers bevatten tegenwoordig beveiligingen tegen XSS, waardoor het niet meer mogelijk is om via een XHTTP request data op te halen van een ander domein dan waar het javascript bestand van is gedownload.

Omdat het regelmatig voorkomt dat je toch met een ander domein wilt kunnen praten is CORS bedacht. SImpel gezegd; de het XHTTP request bevat de naam van het domein namens wie hij data opvraagt en de server die dat request ontvangt moet datzelfde domein teruggeven voordat javascript de boel accepteert.

De officiele manier om dir hostname door te geven is via een HTTP header 'Access-Control-Allow-Origin'. Als er meerdere domeinen toegestaan zijn dan moet er officieel voor elk domein een header worden gestuurd, maar in de praktijk werkt dat niet altijd omdat sommige browsers de headers platslaan tot één header met kommagescheiden domeinnamen, die dan dus feitelijk geen geldige domeinnaam meer zijn. Handig.

De oplossing is een simpel stukje server-side-scripting wat de domeinnaam van het XHTTP request ontvangt, controleert op geldigheid en botweg terugboert:

$http_origin = $_SERVER['HTTP_ORIGIN'];
if ($http_origin == "http://www.domain1.com" || $http_origin == "http://www.domain2.com" || $http_origin == "http://www.domain3.info")
{
header("Access-Control-Allow-Origin: $http_origin");
}

Zie ook: http://stackoverflow.com/questions/1...origin-domains

zondag 24 november 2013

Should you translate your content through google-translate?

The short answer is: no, you should not. Not automatically, anyway.


The long(er) answer is that google does such a horrible job of translating texts that the text is either hard to read or simply doesn't mean the same as the original.

For example:

Dutch: "Mijn vlakbank kan niet van dikte zagen"
Actual meaning: "my planing bench cannot cut a board to the correct thickness"
Google: "My bank can not just cutting thickness."

Dutch:"Ik gebruik een schep om te scheppen."
Actual meaning: "I use a schovel for schoveling"
Google: "I use a shovel to create."

Dutch: "Vertel nog eens een moppie."
Actual meaning: "Tell me another joke"
Google: "Tell me a baby."

Dutch: "Deze grijze trui is mooi wollig, zit lekker en staat heel ruig voor op de kop af dertien euro."
Actual meaning: "This gray sweater is beautifully woolly, comfortable to wear and makes you look real rough, for exactly thirteen euros."
Google: (and I'm not making this up) "This gray sweater is nice woolly, is delicious and is very rough on the nose for thirteen euros."

Dutch: "Bing is beter than google."
Actual meaning: "Bing is better than google"
Google: "Is Bing better than google." (it's subtle, but the meaning goes from a statement to a question)


For those of you who say that "any translation is better than no translation", think again.
Firstly, when visitors read a series of texts in very poor grammer and spelling they will move on. Nothing gives the impression of amateurism and ineptitude more than a text full of grammer and spelling mistakes.

Secondly, the translated texts simply don't mean the same as the original. And they will be indexed by the searchengines and you will be ranked under keywords that have absolutely nothing to do with your site at all. Your website about gardening equipment is never going to be found in Dutch if every word related to "shoveling" is being translated as "creating" instead, and nobody is going to buy a sweater that is "delicious and rough on the nose".

So no, don't automatically translate your content through google. If you want to offer the translations as an extra service, create a button that opens a popup with google-translate in it, so the visitor can see that it's google thats making the mistakes and not you.

Don't contradict the locals!

"In the land of the blind, the one-eyed man is king."

Every forum has a group of locals who spend most of their forum-time on that particular forum to share their knowledge with those who post questions. This is a good thing.

Being on the forum 24/7, they anwer most of the questions and because most of the questions are posted by people who know next to nothing about the subject, the answers of the experts are taken for granted. This is a bad thing.

It's bad because never being told that they are wrong makes the locals feel that they are right. About everything. So when you go to a forum and see someone tell a newbie that 1+1=3, and you point out that it is in fact 2, *you* are the badguy for disagreeing with the expert.

But what makes an expert an expert? Learning! If you have knowledge and someone says you are wrong, you check his claims. If you are in fact wrong, congrats, you've just fixed a bug in your knowledge! If you're right, congrats, you've fixed a bug in his knowledge!


As Mythbuster Adam Savage says: "I love being wrong because it means I'll learn something new."


So why are there so many forumusers and "experts" who will set fire to you if you dare to disagree? How will they ever learn more than they know? How will they ever become actual experts if they insist that they already know everything and are incapable of making mistakes?

If anybody out there knows of a forum where the locals have social skills and a desire to learn rather than pretend that they are brilliant, I'd love to know about it because I can't find any.


zaterdag 23 november 2013

To ENUM or not to ENUM?

An ENUM restricts the allowed values for a column to a hardcoded set. This sounds exactly like what a foreign key does, but there are a few significant differences.

Firstly, an ENUM is part of the database schema, not of the data stored *in* the schema. It is not meant to be changed by a user, in fact it is supposed to be impossible for anyone other than the administrator to change the values.

Secondly, a column that is controlled by an ENUM will sort it's values accoring to the structure of the ENUM, not according to the actaul value. If the ENUM defines 'Zed' before 'Cucumber' then order-by will return 'Zed' before 'Cucumber'.

So why would you use an ENUM if it's not easy to change and doesn't sort properly?
Well, that's exactly why: the options are not going to be changed by a user and the sorting is hardcoded. This means that the rest of the database can make assumptions about this without having to look values up and manually sort them.

How does ENUM work in real life?

For MySQL the answer is simple: it doesn't. MySQL's implementation is very bad, it does not even check for duplicate values in the declaration:
mysql> create temporary table foo (a ENUM('a','a'));
Query OK, 0 rows affected, 1 warning (0.01 sec)
And removing existing options from an ENUM... well best not to think about that because MySQL wouldn't be MySQL if it didn't just NULL the fields that point to the removed value (unless it's NOT NULL, in which case... well.. bye bye data).
MySQL has no alternatives besides messing about with triggers, so use a lookup table instead.


In PostgreSQL the ENUM is enforced properly and it is done trough a custom datatype so you can re-use the definition in all tables that need it, eliminating the chance of discrepancies. as per the manual:

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
name text,
current_mood mood
);

The ENUM has one property that is often unexpected (and undesired) which is that they sort by the order of the values in the ENUM definition, so in this example an "ORDER BY currentmood" would list 'sad' first, then 'ok' and then 'happy'.

Of you do need ordering to work normally, you can use a CHECK constraint to simply see check if the value is IN('sad','ok','happy'). And you can make that re-usable by creating a DOMAIN for it:

CREATE DOMAIN current_mood_domain AS TEXT
CHECK(
   VALUE IN ('happy', 'very happy', 'ecstatic')
);
CREATE TABLE foo (current_mood current_mood_domain);
INSERT INTO foo(current_mood) VALUES ('happy');
INSERT INTO foo(current_mood) VALUES ('angry');

donderdag 7 november 2013

Foutafhandeling.

Wanneer op je website iets gebeurt waardoor de gewone gang van zaken niet afgerond kan worden dan is er maar één manier om het correct af te handelen:

Toon een foutmelding die de bezoeker uitlegt wat er aan de hand is, en geef via header()  de HTTP statuscode 500  (interne server fout) of 503 (dienst tijdelijk niet beschikbaar).
De foutmelding is logisch; je bezoekers moeten weten dat er iets mis is, je kunt niet botweg een lege pagina laten zien. de HTTP statuscode voorkomt dat zoekmachines je foutmelding zien als nieuwe content, of erger; beslissen dat je hele website kaputt is en je ranking resetten.


Is het lastig om dit goed te doen? Helemaal niet.
PHP heeft exceptions. Je kunt in principe in je index.php een try{} block maken met een catch() die  de foutmelding en de HTTP code doorgeeft. Elke fout die in je scripts gebeurt hoeft alleen een exception te werpen om in de buitenste catch te komen en de melding+statuscode te laten verzenden.

Dat betekent dus ook dat die() en exit() alleen voorkomen in die buitenste catch(), en nergens anders. *NERGENS*.

<?php
ob_start();
try{
  // Doe hier al je werk
    if (!fopen(...))
    {
        // Er ging iets fout, werp een exception die door het buitenste catch block wordt opgevangen.
        throw new \Exception("Could not open file");
    }
    // Stuur alle geprinte data naar de browser.
    ob_flush();
}
catch (\Exception $e)
{
    // Gooi alle geprinte data weg zodat alleen de foutmelding naar de browser wordt gestuurd.
    ob_end_clean();
    header('HTTP/1.1 503 Service Temporarily Unavailable');
    header('Status: 503 Service Temporarily Unavailable');
    echo "Sorry, de site is tijdelijk niet beschikbaar.";
}
?>

zaterdag 2 november 2013

New Nested HSTORE developement outperforms MongoDB

If you've ever used PostgreSQL's HSTORE feature then you'll know about it's speed, ease of use and horrible inability to store nested data.

This is about to change.

The HSTORE developers are working on a nested version and at a recent conference they showcased the new features. The highlight was a round of applause for showing that the HSTORE's documentmodel can actually outperform MongoDB.

See: http://momjian.us/main/blogs/pgblog/2013.html#November_1_2013

donderdag 17 oktober 2013

Don't people want to learn anymore?

During the past few years I've noticed an alarming trend amung developers. MySQL+PHP developers in particular. They don't want to learn.

A few weeks ago I told a forumuser that what he was trying to do was nearly impossible in MySQL (which it was, honest) but that it was a trivial thin in just about any other database. This triggered another user, a die-hard MySQL user ,to say "Most of us MySQL user's simply don't care how it's done in other databases".

And a few days ago when I remarked that PDO has no benefits over the default API, the most common argument was that it's easier to migrate if all your database API calls are the same. When I replied that you only have a handfull of those because you only have once class that executes queries, I was told that "in your universe, people wrap PHP API's in their own classes".

Now, for the MySQL thing, I can understand that if your problem is with MySQL right now, it's discouraging to know that your problem is caused by your choice of database. But when you hear that other databases have solutions for the problems you are having, shouldn't that at least make you investigate what that solution is? It may not be the solution right now, but it may prevent you from having the problem again in future.

For the PDO thing I was really amazed that there are people who call me stupid because I have thought about how to design my code... I mean, D.R.Y. is not a very difficult concept, is it?  When you see that you are typing the same code over and over, that makes you extract the code into a function at least, preferably into a class... is that weird?

And I notice these things more often than I used to. Developers seem to live in their own little bubble and when someone says something that doesn't fit their own ideas, the other develoepr is wrong.
The dont ask WHY the other developer says what he says, he's just wrong and that's it.

How can you improve your skill with such a pathetic attitude?

vrijdag 11 oktober 2013

Google translate, no thanks!

Today I came across a nasty side effect of Goolge-translate. Computer-translations have never been good and Google's transations are... how do I put this politely... oh fsck it, they're crap!

In woodworking it's quite handy if you can clamp a piece of wood down, for example to the bed of a tablesaw. To do this you use what's known in the trade as a "toggleclamp", so named because it has only two states; open and closed. Google, oblivious to the context of the word, translates this in dutch to "gaffelklem". A "gaffel" is a piece of wood at the top of  a sail on old sailingships, used to keep the sail extended when there is no wind. They have no clamps. So why does google use this, completely irrelevant word? Because a toggle is also a type of connection in sailboat rigging and that is also "gaffel" in dutch.

So what's the problem?

Alibaba.com uses google translate to get dutch productdescriptions. They advertise "gaffelklemmen" by the boatload, and not a single dutchman will ever find them because they are looking for the correct dutch word "spanklem" (roughly translated: tensionklamp).

So, if your manager suggests that you translate the product descriptions using google because "a bad translation is better than no translation", tell him from me to go and polish is "gaffelklem".

zondag 22 september 2013

Quoting integers in SQL, yes or no?

If your database let's you quote numeric values then handle them just like you would handle a string.

The single biggest reason why you should: Risk management.

Not quoting numeric values means each and every numeric value that you give to the database must be validated. Every one, every where, every time. That may sound easy but it's almost impossible to do. validations are a nuissance to programmers, they are added last, and if there is a deadline looming then validations are the first thing to be skipped because "well, what are the chances..."

Escaping and quoting on the other hand can be automated. Most software, especially frameworks, already have classes and methods to run all the queries in the appication. for example:

$objDatabase->query("INSERT INTO foo (bar) VALUES ($1)", array("hello"));

With this setup you can just loop over the array of values and escape and quote them all.
That is a 100% thing, none of the values are left open to injection.



"But developers can bypass this routine!"
True, they can decide to write their own database access code, just like they can decide not to do any validation.
But what is more likely; completely ignoring the existing framework, or forgetting to validate a variable?


"But quotes define strings, not numbers!"
In a query the quotes only define data boundries. The parser uses quotes to work out where the values begin and end, in case the values contain text that could be misinterpreted as part of the query grammer, it has nothing to do with datatypes.

"If you quote a number the database will read it as a string and has to re-cast it to a number"
The database doesn't cast anything, anywhere. It parses the query, picks up the values and verifies that whatever the value represent is valid for the target column or operation.

The only thing that happens that resembles casting is that the characters from the query are transformed into a binary value that the database stores in the actual table, and that happens for all the values in the query, quoted or not.


The only legit argument I've found against quoting numbers is that it requires escaping and escaping may require a roundtrip to the database. It's a small price to pay for your safety, but if you want to bitch about something; this is it.

maandag 9 september 2013

PostgreSQL 9.3 uitgekomen.

Feestgedruis, toeters en bellen!

PostgreSQL 9.3 is uit en we zijn blij.

Waarom?
De JSON functies zijn aanzienlijk uitgebreid zodat je nu ook JSON obecten kunt parsen, doorzoeken, genereren en indexeren.
Schrijfbare Foreign-Data-Wrappers, dus nu kun je ook in je MySQL database of CSV file gaan schrijven.
Event triggers, triggers die afgaan op DDL veranderingen zoals CREATE en ALTER. Eindelijk gewoon monitorren wat je "lieve collega's allemaal aanpassen in je database.
En een aantal verbeteringen in de replicatie, waaronder snellere failover.

Wat is er nieuw in 9.3: https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.3


zaterdag 7 september 2013

Hoeveel beveiliging is genoeg?

"Deze site is niet zo belangrijk dus de beveiliging hoeft niet zo goed te zijn."

Herken je die? En herken je jezelf erin? Hoe weet je wanneer data belangrijk genoeg is om "goed" te beveiligen? En hoe goed is "goed" dan?

Inderdaad, het is een zin die je niet kunt interpreteren omdat je van geen enkel ingredient kunt definieren wat het precies inhoud.

De plaatselijke klaverjasclub zal het ongetwijfeld worst zijn of de wedstrijduitslagen worden gehackt, dat is jammer maar zo hersteld en niemand is er slechter van geworden. Het wordt een heel ander verhaal als een paar dagen later de bankrekening van een van de leden is geplunderd omdat de beste man het wachtwoord van de klaverjas website ook had gebruikt voor zijn internetbankier account. Natuurlijk was het niet slim van hem om dat te doen, maar mensen doen dat nu eenmaal, ja, ook u, lieve lezer, hebt een aantal wachtwoorden die voor meerdere dingen gebruikt worden en geef toe, sommige zouden eigenlijk al jaren veranderd moeten worden.

Een maand later blijkt dat een andere website dertienduizend creditcardnummers kwijt is geraakt en dat de hackers zijn binnengekomen via een IP adres binnen het privenetwerk van de hoster, door een beveiligingslek in een website die in hetzelfde segment stond. Raad eens welke site dat was...

Hackers zijn niet dom, ze zijn aanzienlijk slimmer dan de gemiddelde webmaster en zij weten dat veel webmasters denken dat hun site niet zo belangrijk is en daarom de beveiliging niet goed voor elkaar maken.

De ironie is dat "goed" beveiligen helemaal niet ingewikkeld is dus waarom er naar smoezen wordt gezocht om het niet te doen is voor mij een raadsel.



woensdag 4 september 2013

Series tellen.

Een van de mooiste aspecten van SQL vind ik dat je regelmatig oplossingen tegenkomt die zo simpel lijken dat je jezelf afvraagt waarom je er zelf niet op gekomen bent.

Het volgende probleem deed zich voor; een statisticus heeft een serie lotto uitslagen. Elke uitslag bestaat uit zes cijfers van 1-9 en zijn in de database opgeslagen als een tabel met zes kolommen, waarin de waarden voor het gemak zijn opgeslagen in oplopende volgorde, dus de eerste kolom bevat altijd het laagste getal van de trekking en de laatste kolom het hoogste getal.

Vraag: hoe kun je achterhalen hoevaak elke serie van drie getallen voorkomt in de totale set?

Dus niet "hoe vaak komen alle mogelijke combinaties van drie getallen voor", maar "1,3,5,6,8,9" bevat "3,5,6", en "3,5,6,7,8,9" bevat ook "3,5,6", en "1,2,3,5,6,7" bevat ook "3,5,6" dus "3,5,6" komt in deze set trekkingen drie keer voor.

Het feit dat de getallen direct op elkaar moeten volgen lijkt in eerste instantie wellicht een extra moeilijkheid maar het maakt het juist eenvoudiger omdat er in een combinatie van zes getallen maar vier mogelijkheden zijn waarop zo'n combinatie kan voorkomen; immers alleen de eerste vier getallen kunnen het begin van een serie van drie zijn, daarna zijn er gewoon niet meer genoeg getallen.

Het is dus zeer goed mogelijk om alle combinaties van kolommen uit te schrijven, de getallen te combineren tot een string die uniek is per combinatie, en vervolgens te tellen hoeveel van die strings er zijn.

Voor het combineren van de kolommen gebruik ik hier domweg stringconcatenatie met een spatie. Om alle gevonden combinaties tegelijk te krijgen worden alle combinatiequeries aan elkaar geplakt met een UNION ALL, immer; UNION filtert dubbelen weg en dan tel je alleen het aantal unieke combinaties in plaats van het aantal keer dat elke combinatie voorkomt. Aan het eind wordt er op de combinaties gegroepeerd en een simpele COUNT(*) vertelt hoevaak elke combinatie in de set zat.


CREATE TABLE trekkingen (trekkingid SERIAL, bal1 int, bal2 int, bal3 int, bal4 int, bal5 int ,bal6 int);
INSERT INTO trekkingen VALUES (1, 2,3,5,6,7,8), (2, 1,4,6,7,8,9), (3, 1,5,6,7,8,9),(4,1,4,6,7,8,9);

SELECT combinatie, COUNT(*) AS aantal
FROM
(
SELECT (bal1 || ' ' ||bal2 || ' ' || bal3) AS combinatie FROM trekkingen
UNION ALL
SELECT (bal2 || ' ' ||bal3 || ' ' || bal4) AS combinatie FROM trekkingen
UNION ALL
SELECT (bal3 || ' ' ||bal4 || ' ' || bal5) AS combinatie FROM trekkingen
UNION ALL
SELECT (bal4 || ' ' ||bal5 || ' ' || bal6) AS combinatie FROM trekkingen
) AS combinaties
GROUP BY combinatie;


woensdag 17 juli 2013

pgRouting

Ik wist dat het kon, maar niet dat het al werd gedaan.

http://pgrouting.org/index.html

Een extension die routes kan berekenen op geo-spacial data. En ja, als je daar dus een landkaart in laadt dan kun je ook routes tussen twee coordinaten op een landkaart berekenen.


dinsdag 11 juni 2013

Documentbased PostgreSQL (of: NoNoSQL)

Een van de grote voordelen van NoSQL is ook meteen het grootste nadeel: er is geen stricte structuur die aangeeft hoe elk record eruit moet zien.

In de praktijk echter wil je altijd een hoeveelheid houvast, dingen waarvan het gegarandeerd is dat je ze in elk record zult aantreffen. Veel NoSQL implementaties hangen daarom zelf al een identifier aan elk record.

PostgreSQL zou PostgreSQL niet zijn als het niet mogelijk zou zijn omhet beste van twee werelden te gebruiken. Via XML of JSON is dat een relatieve peulenschil en een voorbeeld hiervan is te vinden op:
http://blog.endpoint.com/2013/06/postgresql-as-nosql-with-data-validation.html

Het idee is simpel; maak een tabel met een JSON kolom erin en maak CHECK CONSTRAINTs en functionele indexes. De CHECKs garanderen dat elk JSON object altijd de minimale benodigde velden heeft en alles wat er extra in zit wordt volledig genegeerd en opgeslagen as-is. De functionele indexes maken het mogelijk om snel te zoeken in de data  (want je zoekt alleen in data waarvan je al weet dat je ze hebt).

De mogelijkheden zijn legio; persoonlijk ga ik eens kijken of ik mijn WIED project (Website In Een Database) kan uitbreiden naar JSON om zo een productdatabase te laten opboeren zonder de velden te definieren, en toch te garanderen dat alle data klopt.

woensdag 5 juni 2013

1001 chained streaming-only replication instances

Waarom? Omdat het kan!

Meneer Thom had niets te doen en besloot een principe te testen; is het mogelijk om een PostgreSQL database te laten repliceren naar een slave en die slave te laten repliceren naar een slave en dat dan dus duizend keer.

Het antwoord: ja het kan, zelfs op een laptop!

http://thombrown.blogspot.nl/2013/06/1001-chained-streaming-only-replication.html

/me moppert iets over scalability...

maandag 8 april 2013

Zomaar een voorbeeldje van PostgreSQL

In antwoord op een vraag op pfz.nl: http://www.pfz.nl/forum/topic/9388-gemiddelde-prijzen-van-selectie-groep/ heb ik verklaard dat dat in PostgreSQL een peulenschil zou zijn (en in MySQL een drama) en om dat te bewijzen zet ik hier een concept uitwerking van het probleem:

Eerst een tabel om mee te testen. Via GENERATE_SERIES() is dat zo gebeurd:


DROP TABLE IF EXISTS prijzencircus;
CREATE TABLE prijzencircus (productid SERIAL, prijs int);
INSERT INTO prijzencircus SELECT productid, 10000 * RANDOM() FROM GENERATE_SERIES(1,10000) AS productid;


Dan het selecteren van de gemiddelde prijs per blok van tien aaneengesloten prijzen:

WITH prijzenslag AS
(
SELECT
productid,
prijs,
RANK() OVER (ORDER BY productid) AS slagvolgorde
FROM prijzencircus
)
SELECT
  slagvolgorde
  , prijs
  , AVG(prijs) OVER (w)
FROM
  prijzenslag
WINDOW w AS (PARTITION BY ROUND(slagvolgorde / 10))
ORDER BY
    slagvolgorde;

RANK geeft een getal wat effetief een volgnummer per record is. Dat getal kan wordt verderop gebruikt om de setjes van tien te bepalen. WITH definieert de query als een tijdelijk view, zodat die verderop in de query wat overzichtelijker aangeroepen kan worden.

In de query zelf wordt de AVG(prijs) berekend over een WINDOW en dat window deelt de records op in groepnes (PARTITIONs) over slagvolgorde/10, dus alles met een slagvolgorde van 0-9 komt in partition 0, 10-19 in 1,  20-29 in 2, etc. De AVG() wordt hierdoor dus gemaakt over setjes van tien records.

En voila.

dinsdag 2 april 2013

PostgreSQL 9.3, meer JSON functies

PostgreSQL kende al functies om JSON te genereren, maar met 9.3 komen er eindelijk ook functies om JSON te verwerken.

Zie de post van Andrew Dunstan, die het allemaal op zijn geweten heeft: http://adpgtech.blogspot.nl/2013/03/json-processing-functions-and-new-json.html

In  de praktijk betekent dit dat je vanuit je AJAX applicatie direct in JSON met je database kunt babbelen. Het ophalen van een zoekresultaat wordt ineens wel heel eenvoudig een kwestie van één query waar een JSON string uit komt.

Natuurlijk kon dit al via XML maar XML blijkt in de praktijk vanuit javascript niet bijzonder gebruiksvriendelijk dus dat het nu met JSON kan is een hele verbetering.

maandag 11 maart 2013

Nou nog eentje dan...

De lijst dingen die slecht zijn aan MySQL is oneindig.

Neem zoiets simpels als een tikfout in de naam van een tabel:

SELECT ik_besta_niet.bar FROM foo;

Dan verwacht je toch dat MySQL iets zegt als "unknown table 'ik_besta_niet'", maar in plaats daarvan krijg je "unknown column 'bar'.

Nuttig...

woensdag 27 februari 2013

Een goed begin van je dag, met MySQL.

Ok, dus je hebt een MySQL database en je hebt hem redelijk onder controle. Ja, echt.
Dan kom je een geval tegen waarin database X structureel een kopie is van Y en Y een aantal records in een childtabel heeft die de X database nog niet heeft. Eenvoudig klusje; dump de tabel uit Y, maak hem onder een ander naam aan op de X aan en INSERT met LEFT JOIN.

Toch?

Jazeker, maar dan maak je de tabel aan op X en krijg je een "Duplicate key on write or update". Wat is er dan aan de hand? Nou dat moet je even zelf opzoeken. In mijn geval was de naam van de foreign-key niet uniek. Opzich logisch, maar is he nou echt zo moeilijk om een melding te geven als "Key foo already exists"?

maandag 21 januari 2013

MySQL zuigt, deel 567475

Soms heb je van die momenten dat je jezelf afvraagt waar we nou helemaal mee bezig zijn.


Wat denk je dat er gebeurt bij het volgende stukje MySQL?


CREATE TABLE foo (id int);
BEGIN;
INSERT INTO foo VALUES (2);
CREATE TABLE bar (id int);
ROLLBACK;
SELECT * FROM foo;

De INSERT op foo staat na een BEGIN, dus de ROLLBACK maakt het ongedaan, logisch.

Nee nee nee meneertje, dit is MySQL:

SELECT * FROM foo;
+------+
| id   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

WTF?

Nou, in MySQL geven o.a. CREATE TABLE, ALTER TABLE en CREATE INDEX  een 
impliciete COMMIT.

MySQL kan DDL wijzigingen niet via transacties terugrollen. Het is dus opzich logisch dat een wijziging in DDL je transactie stopt. Wat absurd is is dat de transactie wordt gecommit en dat ook nog zonder enige vorm van melding whatsoever.