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