NoSQL mit PostgreSQL

Stephan Hochdörfer // 21.06.2016

Über mich

Stephan Hochdörfer
Head of Technology, bitExpert AG
Mannheim
S.Hochdoerfer@bitExpert.de
@shochdoerfer
#PHP, #DevOps, #Automation
#phpugffm, #phpugmrn, #unKonf

Dev-Session Inhalt

PostgreSQL Installation
Datentyp Array
Datentyp JSONb
Datentyp XML
Foreign Data Wrapper

PostgreSQL

« [...] is an object-relational database management system
with an emphasis on extensibility and on standards-
compliance. » - Wikipedia

PostgreSQL Installation

Postgres GPG Key importieren

$> apt-get install wget ca-certificates
$> wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -

/etc/apt/sources.list.d/pgdg.list

deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main 9.5

PostgreSQL 9.5 installieren

$> apt-get update
$> apt-get install postgresql-9.5 postgresql-client-9.5 postgresql-server-dev-9.5

PostgreSQL Konfiguration

/etc/postgresql/9.5/main/postgresql.conf
```
listen_addresses = '*'
```

/etc/postgresql/9.5/main/pg_hba.conf

# TYPE  DATABASE     USER        ADDRESS         METHOD
host    all          all         10.0.2.2/24      md5

PostgreSQL neu starten
```
$> /etc/init.d/postgresql start
```

Datenbank erzeugen

Vagrant Box starten
```
$> vagrant up && vagrant ssh
```
Zum PostgreSQL Benutzer wechseln
```
$> sudo su postgres
```
Benutzer anlegen
```
$> createuser dwx16 -l -P -s
```
Datenbank anlegen
```
$> createdb dwx16 -O dwx16
```

Array

« [...] PostgreSQL allows columns of a table to be
defined as variable-length multidimensional arrays. »
- PostgreSQL documentation

Array Spalte anlegen

CREATE TABLE "products"
(
    "id" SERIAL PRIMARY KEY NOT NULL,
    "name" TEXT NOT NULL,
    "description" TEXT,
    "price" FLOAT NOT NULL,
    "categories" TEXT[] NOT NULL
);

Arrayinhalte speichern

INSERT INTO "products" ("name", "description", "price", "categories")
VALUES ('Lenovo YOGA Tablet', 'This is a short description.', '179.0',
ARRAY['Tablets']);

INSERT INTO "products" ("name", "description", "price", "categories")
VALUES ('Asus Transformer Book', 'This is a description.', '199.00',
ARRAY['Tablets', 'Notebook']);

Array Inhalte auslesen

SELECT "id", "name", "categories" FROM "products"
WHERE "categories"[1] = 'Tablets';

 id |         name          |     categories
----+-----------------------+--------------------
  1 | Lenovo YOGA Tablet    | {Tablets}
  2 | Asus Transformer Book | {Tablets,Notebook}
(2 rows)

Arrays durchsuchen

SELECT "id", "name", "categories" FROM "products"
WHERE 'Tablets' = ANY("categories");

 id |         name          |     categories
----+-----------------------+--------------------
  1 | Lenovo YOGA Tablet    | {Tablets}
  2 | Asus Transformer Book | {Tablets,Notebook}
(2 rows)

Arrays durchsuchen

SELECT "id", "name", "categories" FROM "products"
WHERE "categories" @> ARRAY['Tablets', 'Notebook'];

 id |         name          |     categories
----+-----------------------+--------------------
  2 | Asus Transformer Book | {Tablets,Notebook}
(2 rows)

Alle Kategorien ermitteln

SELECT UNNEST("categories") AS "title", COUNT(*) AS "cnt"
FROM "products" GROUP BY "title";

  title   | cnt
----------+-----
 Tablets  |   2
 Notebook |   1
(2 rows)

Kategorie hinzufügen

UPDATE "products" SET "categories" = ARRAY['Asus'] || "categories"
WHERE "id" = 2;

SELECT "id", "name", "categories" FROM "products"

 id |         name          |       categories
----+-----------------------+-------------------------
  1 | Lenovo YOGA Tablet    | {Tablets}
  2 | Asus Transformer Book | {Asus,Tablets,Notebook}
(2 rows)

Kategorie hinzufügen

UPDATE "products" SET "categories" = array_cat(ARRAY['AsusBook'],
"categories") WHERE "id" = 2;

SELECT "id", "name", "categories" FROM "products"

 id |         name          |       categories
----+-----------------------+-------------------------
  1 | Lenovo YOGA Tablet    | {Tablets}
  2 | Asus Transformer Book | {AsusBook,Asus,Tablets,Notebook}
(2 rows)

Kategorie hinzufügen

UPDATE "products" SET "categories" = array_append("categories",
'Trnsformer') WHERE "id" = 2;

SELECT "id", "name", "categories" FROM "products"

 id |         name          |       categories
----+-----------------------+-------------------------
  1 | Lenovo YOGA Tablet    |{Tablets}
  2 | Asus Transformer Book |{AsusBook,Asus,Tablets,Notebook,Trnsformer}
(2 rows)

Kategorie verändern

UPDATE "products" SET "categories"[5] = 'Transformer' WHERE "id" = 2;

SELECT "id", "name", "categories" FROM "products"

 id |         name          |       categories
----+-----------------------+-------------------------
  1 | Lenovo YOGA Tablet    |{Tablets}
  2 | Asus Transformer Book |{AsusBook,Asus,Tablets,Notebook,Transformer}
(2 rows)

Kategorie entfernen

UPDATE "products" SET "categories" = array_remove("categories",
'AsusBook') WHERE "id" = 2;

UPDATE "products" SET "categories" = array_remove("categories",
'Asus') WHERE "id" = 2;

UPDATE "products" SET "categories" = array_remove("categories",
'Transformer') WHERE "id" = 2;

SELECT "id", "name", "categories" FROM "products"

 id |         name          |       categories
----+-----------------------+-------------------------
  1 | Lenovo YOGA Tablet    | {Tablets}
  2 | Asus Transformer Book | {Tablets,Notebook}
(2 rows)

Kategorie hinzufügen

UPDATE "products" SET "categories"[7] = 'Transformer' WHERE "id" = 2;

SELECT "id", "name", "categories" FROM "products"

 id |         name          |       categories
----+-----------------------+-------------------------
  1 | Lenovo YOGA Tablet    | {Tablets}
  2 | Asus Transformer Book | {Tablets,Notebook,NULL,NULL,NULL,NULL,
                              Transformer}
(2 rows)

JSON

« [...] the json data type has the advantage of checking
that each stored value is a valid JSON value. »
- PostgreSQL documentation

JSONb

« There are two JSON data types: json and jsonb [...]
The major practical difference is one of efficiency. »
- PostgreSQL documentation

JSON vs. JSONb

JSONb ideal für Sortierungen,
Indexe, Suchabfragen etc.
JSONb benötigt i.d.R. mehr
Speicherplatz
JSON verwenden wenn Daten
nur gespeichert werden
JSON verwenden wenn Struktur
wichtig ist!

JSON Spalte anlegen

TRUNCATE "products" RESTART IDENTITY;

ALTER TABLE "products" ADD COLUMN "rating" JSONB;

ALTER TABLE "products" ADD COLUMN "meta" JSONB;

JSON Daten speichern

INSERT INTO "products"
("name", "description", "price", "categories", "rating", "meta")
VALUES
('Lenovo YOGA Tablet', 'This is a short description.', '179.00',
ARRAY['Tablets'], '{"cnt": 0, "rating": 0}', '{}');

INSERT INTO "products"
("name", "description", "price", "categories",
 "rating", "meta")
VALUES
('Asus Transformer Book', 'This is a description.', '199.00',
ARRAY['Tablets', 'Notebook'], '{"cnt": 0, "rating": 0}', '{}');

JSON Daten speichern

INSERT INTO "products"
("name", "description", "price", "categories",
 "rating", "meta")
VALUES
('Asus Transformer Book', 'This is a description.', '199.00',
ARRAY['Tablets', 'Notebook'], '{"cnt": 0, "rating": 0,}', '{}');

ERROR:  invalid input syntax for type json
LINE 1: ...iption.', '199.00', ARRAY['Tablets', 'Notebook'], '{"cnt": 0...
                                                             ^
DETAIL:  Expected string, but found "}".
CONTEXT:  JSON data, line 1: {"cnt": 0, "rating": 0,}

JSON Daten abfragen

SELECT "id", "name", "rating" FROM "products";

 id |         name          |         rating
----+-----------------------+-------------------------
  1 | Lenovo YOGA Tablet    | {"cnt": 0, "rating": 0}
  2 | Asus Transformer Book | {"cnt": 0, "rating": 0}
(2 rows)

JSON Daten abfragen

SELECT "id", "name", "rating"->>'rating' as "rating_sum"
FROM "products";

 id |         name          | rating_sum
----+-----------------------+------------
  1 | Lenovo YOGA Tablet    | 0
  2 | Asus Transformer Book | 0
(2 rows)

JSON Daten verändern

UPDATE "products" SET "rating" = '{"cnt": 1, "rating": 6}'
WHERE "id" = 1;

SELECT "id", "name", "rating" FROM "products";

 id |         name          |         rating
----+-----------------------+-------------------------
  1 | Lenovo YOGA Tablet    | {"cnt": 0, "rating": 0}
  2 | Asus Transformer Book | {"cnt": 1, "rating": 6}
(2 rows)

JSON Daten verändern

UPDATE "products" SET "rating" = jsonb_build_object('cnt', 1,
'rating', 5) WHERE "id" = 1;

SELECT "id", "name", "rating" FROM "products";

 id |         name          |         rating
----+-----------------------+-------------------------
  1 | Lenovo YOGA Tablet    | {"cnt": 0, "rating": 0}
  2 | Asus Transformer Book | {"cnt": 1, "rating": 5}
(2 rows)

JSON Daten verändern

UPDATE "products" SET "rating" = jsonb_set("rating", '{"cnt"}',
'1'::JSONB) WHERE "id" = 2;

UPDATE "products" SET "rating" = jsonb_set("rating", '{"rating"}',
'3'::JSONB) WHERE "id" = 2;

SELECT "id", "name", "rating" FROM "products";

 id |         name          |         rating
----+-----------------------+-------------------------
  1 | Lenovo YOGA Tablet    | {"cnt": 1, "rating": 3}
  2 | Asus Transformer Book | {"cnt": 1, "rating": 5}
(2 rows)

JSON Daten sortieren

SELECT "id", "name",
CAST("rating"->>'rating' as int) / CAST("rating"->>'cnt' as int) as "r"
FROM "products"
WHERE CAST("rating"->>'cnt' as int) > 0
ORDER BY "r" DESC;

 id |         name          | r
----+-----------------------+--------
  1 | Lenovo YOGA Tablet    |      5
  2 | Asus Transformer Book |      3
(2 rows)

JSON Daten sortieren

SELECT "id", "name",
CASE
    WHEN "rating"->>'cnt' != '' AND CAST("rating"->>'cnt' as int) > 0
    THEN CAST("rating"->>'rating' as int) / CAST("rating"->>'cnt' as int)
    ELSE 0
END AS "r"
FROM "products"
ORDER BY "r" DESC;

 id |         name          | r
----+-----------------------+--------
  1 | Lenovo YOGA Tablet    |      5
  2 | Asus Transformer Book |      3
(2 rows)

JSON Daten vorbereiten

UPDATE "products" SET "meta" = jsonb_build_object('ram', '2GB',
'vendor', 'Lenovo', 'weight', 600, 'display', '1920x1200', 'instock',
true) WHERE "id" = 1;

UPDATE "products" SET "meta" = jsonb_build_object('ram', '1GB',
'vendor', 'Asus', 'weight', 1000, 'display', '1366x768') WHERE "id" = 2;

JSON Daten sortieren

SELECT "id", "name", "meta" FROM "products"
ORDER BY ("meta"->>'weight')::int;

 id |         name          | meta
----+-----------------------+------
  1 | Lenovo YOGA Tablet    | {}
  2 | Asus Transformer Book | {}
(2 rows)

Alle JSON Keys auslesen

SELECT DISTINCT jsonb_object_keys("meta") FROM "products";

 jsonb_object_keys
-------------------
 instock
 ram
 weight
 display
 vendor
(5 rows)

JSON Key suchen

SELECT * FROM "products" WHERE "meta" ? 'instock';

 id |        name
----+--------------------
  1 | Lenovo YOGA Tablet
(1 row)

JSON Key/Value suchen

SELECT "id", "name" FROM "products"
WHERE "meta" @> '{"instock": true}'::jsonb;

 id |        name
----+--------------------
  1 | Lenovo YOGA Tablet
(1 row)

JSON Key/Value suchen

SELECT "id", "name" FROM "products"
WHERE "meta" @> '{"instock": "true"}'::jsonb;

 id | name
----+------
(0 rows)

JSON Key/Value suchen

SELECT "id", "name" FROM "products"
WHERE "meta" @> '{"ram": "1GB"}'::jsonb;

 id |         name
----+-----------------------
  2 | Asus Transformer Book
(1 row)

JSON Key/Value suchen

SELECT "id", "name" FROM "products"
WHERE "meta" @> '{"ram": "1gb"}'::jsonb;

 id | name
----+------
(0 rows)

JSON Joins

CREATE TABLE "vendor"
(
    "name" TEXT NOT NULL UNIQUE,
    "url" TEXT
);

INSERT INTO "vendor" ("name", "url")
VALUES('Asus', 'http://www.asus.de');

INSERT INTO "vendor" ("name", "url")
VALUES('Lenovo', 'http://www.lenovo.com/de/de/');

JSON Joins

SELECT "p"."id", "p"."name", "v"."url" FROM "products" "p" LEFT JOIN
"vendor" "v" ON "p"."meta"->>'vendor' = "v"."name";

 id |         name          |             url
----+-----------------------+------------------------------
  1 | Lenovo YOGA Tablet    | http://www.lenovo.com/de/de/
  2 | Asus Transformer Book | http://www.asus.de
(2 rows)

JSON Webservice

SELECT row_to_json("products") FROM "products" WHERE "id" = 2;

                             row_to_json
----------------------------------------------------------------------
{"id":2,"name":"Asus Transformer Book","description":"This is a
description.","price":199,"categories":["Tablets","Notebook"],"rating":
{"cnt": 1, "rating": 3},"meta":{}}
(1 row)

JSON Webservice

SELECT row_to_json("p")
FROM (
    SELECT "id", "name", "price", "description", "categories"
    FROM "products"
    WHERE "id" = 2
) "p";

                             row_to_json
----------------------------------------------------------------------
{"id":2,"name":"Asus Transformer Book","price":199,"description":
"This is a description.","categories":["Tablets","Notebook"]}
(1 row)

JSON Webservice

SELECT row_to_json("p")
FROM (
    SELECT "id", "name", "price", "description", "categories"
    FROM "products"
) "p";

                             row_to_json
----------------------------------------------------------------------
{"id":1,"name":"Asus Transformer Book","price":199,"description":"This
is a description.","categories":["Tablets","Notebook"]}
{"id":2,"name":"Lenovo YOGA Tablet","price":179,"description":"This
is a short description.","categories":["Tablets"]}
(2 rows)

JSON Webservice

SELECT array_to_json(array_agg(row_to_json("p")))
FROM (
    SELECT "id", "name", "price", "description", "categories"
    FROM "products"
) "p";

                             row_to_json
----------------------------------------------------------------------
[
{"id":1,"name":"Asus Transformer Book","price":199,"description":"This
is a description.","categories":["Tablets","Notebook"]},
{"id":2,"name":"Lenovo YOGA Tablet","price":179,"description":"This
is a short description.","categories":["Tablets"]}
]
(1 row)

XML

« [...] it checks the input values for well-formedness, and there are support functions to perform type-safe operations on it. » - PostgreSQL documentation

XML Spalte anlegen

CREATE TABLE "imports"
(
    "id" SERIAL PRIMARY KEY NOT NULL,
    "filename" VARCHAR NOT NULL UNIQUE,
    "contents" XML NOT NULL
);

XML Dokument

<?xml version='1.0' standalone='yes'?>
<products>
    <product id="1" name="Lenovo YOGA Tablet" price="179.00"
     vendor="Lenovo" instock="true" ram="2GB" display="1920x1200"
     weight="600">
        <category name="Tablets" />
    </product>
    <product id="2" name="Asus Transformer Book" price="199.00"
     vendor="Asus" instock="true" ram="1GB" display="1366x768"
     weight="1000">
        <category name="Tablets" />
        <category name="Notebook" />
    </product>
</products>

XML Daten speichern

INSERT INTO "imports" ("filename","contents") VALUES ('1.xml',
'<?xml version="1.0" standalone="yes"?>
<products>
    <product id="1" name="Lenovo YOGA Tablet" price="179.00"
      vendor="Lenovo" instock="true" ram="2GB" display="1920x1200"
      weight="600">
        <category name="Tablets" />
    </product>
    <product id="2" name="Asus Transformer Book" price="199.00"
      vendor="Asus" instock="true" ram="1GB" display="1366x768"
      weight="1000">
        <category name="Tablets" />
        <category name="Notebook" />
    </product>
</products>');

XML Daten speichern

INSERT INTO "imports" ("filename", "contents") VALUES ('1.xml',
'<?xml version="1.0" standalone="yes"?><products></categories>');

ERROR:  invalid XML content
LINE 1: ...mports" ("filename", "contents") VALUES ('1.xml', '<?xml ver...
                                                             ^
DETAIL:  line 1: Opening and ending tag mismatch: products line 1 and
categories
<products></categories>
                       ^
line 1: chunk is not well balanced
<products></categories>

XML Daten abfragen

SELECT xpath('/products/product/@id', "contents") FROM imports;

 xpath
-------
 {1,2}
(1 row)

XML Daten abfragen

SELECT xpath('/products/product/@ram', "contents") AS ram FROM imports;

   ram
-----------
 {2GB,1GB}
(1 row)

XML Daten durchsuchen

SELECT "filename" FROM imports
WHERE xpath_exists('/products/product[@vendor="Asus"]', contents);

 filename
----------
 1.xml
(1 row)

hstore

« [...] data type for storing sets of key/value pairs within a single PostgreSQL value. [...] Keys and values are simply text strings. » - PostgreSQL documentation

hstore Extension installieren

CREATE EXTENSION hstore;

hstore Spalte anlegen

CREATE TABLE audit
(
    change_date timestamptz default now(),
    before hstore,
    after hstore
);

Audit Funktion

CREATE OR REPLACE FUNCTION audit() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO audit(before, after)
        SELECT
            hstore(OLD),
            hstore(NEW);
     return NEW;
END;
$$;

Audit Funktion

CREATE OR REPLACE FUNCTION audit() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO audit(before, after)
        SELECT
            hstore(OLD) - ARRAY['categories', 'rating', 'meta'],
            hstore(NEW) - ARRAY['categories', 'rating', 'meta'];
     return NEW;
END;
$$;

Audit Trigger

CREATE TRIGGER audit BEFORE UPDATE on products
    FOR EACH ROW EXECUTE PROCEDURE audit();

Änderungen feststellen

SELECT "change_date", "after" - "before" as "diff" FROM "audit"
WHERE ("before"->'id')::int = 1 AND ("after"->'id')::int = 1;

          change_date          |             diff
-------------------------------+------------------------------
 2015-10-07 13:12:02.081774-04 | "name"=>"Lenovo Yoga Tablet"
(1 row)

Foreign Data Wrappers

« [...] which can be used to access data stored in external
PostgreSQL servers. » - PostgreSQL documentation

Foreign Data Wrappers

ODBC, JDBC, JDBC2, VirtDB
PostgreSQL, Oracle, MySQL,
Informix, Firebird, SQLite,
Sybase, MS SQL Server,
MonetDB
Cassandra2, Cassandra,
CouchDB, Kyoto Tycoon,
MongoDB, Neo4j,
Redis, RethinkDB, Riak
CSV, ZIP, LDAP, RSS, Git

Redis

« Redis is a data structure server. » - Wikipedia

Installation Redis FDW

git clone https://github.com/pg-redis-fdw/redis_fdw

PATH=/usr/lib/postgresql/9.5/bin/:$PATH make USE_PGXS=1
PATH=/usr/lib/postgresql/9.5/bin/:$PATH make USE_PGXS=1 install

CREATE EXTENSION redis_fdw;

CREATE SERVER redis_server
    FOREIGN DATA WRAPPER redis_fdw
    OPTIONS (address '127.0.0.1', port '6379');

CREATE FOREIGN TABLE redis_db0 (key text, value jsonb)
    SERVER redis_server
    OPTIONS (database '0');

CREATE USER MAPPING FOR PUBLIC
    SERVER redis_server;

Interaktion mit Redis FDW

INSERT INTO "redis_db0" ("key", "value") VALUES ('1',
'{"cnt": 0, "rating": 0}');

UPDATE "redis_db0" SET "value" = '{"cnt": 1, "rating": 1}' 
WHERE "key" = '1';

DELETE FROM "redis_db0" WHERE "key" = '1';

Interaktion mit Redis FDW

SELECT * FROM "redis_db0";

 key |          value
-----+-------------------------
 1   | {"cnt": 1, "rating": 1}
(1 row)

Joins mit Redis FDW

SELECT "p"."id", "p"."name", "r"."value" FROM "products" "p",
"redis_db0" "r" WHERE "p"."id" = CAST(nullif("r"."key", '') AS
integer);

 id |        name        |          value
----+--------------------+-------------------------
  1 | Lenovo YOGA Tablet | {"cnt": 1, "rating": 1}
(1 row)

Redis FDW In-Place Update

UPDATE "redis_db1" SET "value" = jsonb_set("value", '{"cnt"}',
CAST((CAST("value"->>'cnt' as integer) + 1) as text)::JSONB)
WHERE "key" = '1';

UPDATE "redis_db1" SET "value" = jsonb_set("value", '{"rating"}',
CAST((CAST("value"->>'rating' as integer) + 5) as text)::JSONB)
WHERE "key" = '1';

SELECT "p"."id", "p"."name", "r"."value" FROM "products" "p",
"redis_db0" "r" WHERE "p"."id" = CAST(nullif("r"."key", '') AS
integer);

 id |        name        |          value
----+--------------------+-------------------------
  1 | Lenovo YOGA Tablet | {"cnt": 2, "rating": 6}
(1 row)

Vielen Dank! Fragen?