PostgreSQL NoSQL Workshop

Stephan Hochdörfer // 01.12.2015

Über mich

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

Workshop Inhalt

Einführung Applikation
PostgreSQL Installation
Einführung PostgreSQL
Datentyp Array
Datentyp JSONb
Datentyp XML
Auditing mit PostgreSQL
Tabellenpartionierung
Foreign Data Wrapper

Vorbereitungen (Host)

Vagrant Box hinzufügen

$> vagrant box add psql-nosql-workshop-jessie32 workshop.box

Git Repo klonen

$> git clone https://github.com/bitexpert/psql-nosql-workshop

Vagrant starten
```
$> vagrant up && vagrant ssh
```

Vorbereitungen (VM)

In das Projektverzeichnis wechseln
```
$> cd /vagrant/
```
Composer ausführen
```
$> composer.phar install
```
Bower ausführen
```
$> bower install
```

Konfiguration anpassen

$> cp config/config.php.dist config/config.php

Vorbereitungen (VM)

Webserver starten
```
$> php -S 0.0.0.0:8080 -t webroot/
```

Ergebnis

Web-Framework

Applikationsstruktur

/nocommerce
   |-bin
   |---import.php
   |-config
   |-src
   |---Nocommerce
   |-----Cli
   |-----Web
   |-vendor
   |-views
   |-webroot
   |---bower_components
   |---css
   |---fonts
   |---scripts
   |---index.php

App Konfiguration

<?php

return [
    'debug' => true,
    'database' => [
        'host'     => '127.0.0.1',
        'port'     => 5432,
        'username' => 'nocommerce',
        'password' => 'nocommerce',
        'schema' => 'nocommerce'
    ]
];

Frontcontroller

<?php

require_once __DIR__ . '/../revealjs/vendor/autoload.php';

use Nocommerce\Web\ShopControllerProvider;
use Silex\Application;

$app = new Application();

$app->mount('/', new ShopControllerProvider());

$app->run();

ShopControllerProvider

<?php

namespace Nocommerce\Web;

use Silex\Application;
use Silex\ControllerProviderInterface;

class ShopControllerProvider implements ControllerProviderInterface
{
    public function connect(Application $app)
    {
        // creates a new controller based on the default route
        $controllers = $app['controllers_factory'];

        $controllers->get('/', function () {
            return 'Hello world!';
        });

        return $controllers;
    }
}

Doctrine

<?php

$controllers->get('/', function (Application $app) {
    $sql ='SELECT "id", "name", "description", "price" FROM "products"';
    $products = $app['db']->fetchAll($sql);

    return $app['twig']->render('index.twig', ['products'=>$products]);
});

Doctrine - Prepared Stmts

<?php

$controllers->get('/', function (Application $app) {
    $productId = 1;
    $sql = 'SELECT "id", "name", "description" FROM "products"
    WHERE "id" = ?';
    /** @var \Doctrine\DBAL\Statement $stmt */
    $stmt = $app['db']->prepare($sql);
    $stmt->bindValue(1, $productId, 'integer');
    $stmt->execute();
    $products = $stmt->fetchAll();


    return $app['twig']->render('index.twig', ['products'=>$products]);
});

CLI-Framework

Symfony Console

#!/usr/bin/env php
<?php

require_once __DIR__ . '/../revealjs/vendor/autoload.php';

use Nocommerce\Cli\Application;
use Nocommerce\Cli\Command\ImportCommand;

$cli = new Application(new ImportCommand());
$cli->run();

ImportCommand

<?php

namespace Nocommerce\Cli\Command;

use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputArgument;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;

class ImportCommand extends Command
{
    protected function configure()
    {
        $this->setName('nocommerce.import')
            ->setDescription('XML file importer')
            ->setDefinition(array(
                new InputArgument('file', InputArgument::REQUIRED),
            ));
    }

ImportCommand

    protected function execute(InputInterface $input,
           OutputInterface $output)
    {
        $file = $input->getArgument('file');
        if (!file_exists($file) or !is_readable($file)) {
            $output->write(sprintf('File "%s" not readable!', $file));
            return;
        }

        $products = simplexml_load_file($file);

        // [...]
    }
}

Aufgaben

Auf git master Branch
wechseln
Applikation aufrufen
mit http://127.0.0.1:8080
Mit der Applikation
vertraut machen

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

Zum PostgreSQL Benutzer wechseln
```
$> sudo su postgres
```
Benutzer anlegen
```
$> createuser nocommerce -l -P -s
```
Datenbank anlegen
```
$> createdb nocommerce -O nocommerce
```

Verbindung testen

PostgreSQL Queries

SELECT "id", "name", "description", "price" FROM "products";

SELECT "id", "name", "description", "price" FROM "products"
WHERE "name" = 'Test';

Aufgaben

Verbindung konfigurieren
SELECT Anfrage
formulieren

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']);

Aufgaben

"products" Tabelle
anlegen
Inserts durchspielen

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)

Aufgaben

git checkout exercise1
Controller "/" erweitern um
das Auslesen aller Produkte
Controller "/browse/{category}"
erweitern um die Filterung
mittels Containment Operator

Arrays indexieren

CREATE INDEX "categories_idx" ON "products" USING GIN ("categories");

SET enable_seqscan TO off;

EXPLAIN ANALYZE SELECT * FROM "products"
WHERE "categories" @> ARRAY['Tablets'];

Aufgaben

GIN Index erzeugen
Mit Explain Ergebnis
überprüfen

Alle Kategorien ermitteln

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

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

Aufgabe

git checkout exercise2
CategoryRenderer erweitern
um das Auslesen der Kategorien
CategoryRenderer erweitern
um die Anzahl der Produkte
pro Kategorie

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)

Null Werte entfernen

UPDATE "products"
SET "categories" = "a"."agg_cat"
FROM (
    SELECT array_agg("p"."cat") as "agg_cat"
    FROM (
        SELECT unnest("categories") as "cat"
        FROM "products"
        WHERE "id" = 2
        EXCEPT SELECT NULL
    ) AS "p"
) AS "a"
WHERE "id" = 2;

Statistik Generierung

SELECT UNNEST("categories") as category, COUNT(*) as "cnt" FROM
"products" GROUP BY "category"ORDER BY "cnt";

  category   | cnt
-------------+-----
 Transformer |   1
 Notebook    |   1
 Tablets     |   2
(3 rows)

Array Funktionen

array_cat()
array_length()
array_prepend()
array_append()
array_position()
array_replace()
...

Volltextsuche

« [...] provides the capability to identify natural-language
documents that satisfy a query » - PostgreSQL documentation

Volltextsuche

SELECT to_tsvector('fat cats ate fat rats') @@ to_tsquery('fat & rat');

 ?column?
----------
 t
(1 row)

Volltextsuche

SELECT "id", "name", "description", "price" FROM "products" WHERE
to_tsvector('english',  "name" || ' ' || "description") @@
to_tsquery('english', 'short & Tablet');

 id |        name        |         description          | price
----+--------------------+------------------------------+-------
  1 | Lenovo YOGA Tablet | This is a short description. |   179
(1 row)

Aufgaben

git checkout exercise3
Controller "/search" um
Volltextsuche erweitern

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,}

Aufgaben

"products" Tabelle
um JSONb Spalten
erweitern
Inserts durchspielen

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)

Aufgaben

git checkout exercise4
Controller "/" erweitern um
das Auslesen von "rating"
und "meta"
Controller "/browse/{category}"
erweitern um das Auslesen
von "rating" und "meta"

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)

Aufgaben

Updates mit jsonb_build_object()
testen
Updates mit jsonb_set() testen
git checkout exercise5
Controller "/rate" erweitern
um Update-Logik

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)

Aufgaben

git checkout exercise6
Controller "/" erweitern um
das Sortieren nach Rating
Controller "/browse/{category}"
erweitern um das Sortieren
nach Rating

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)

Aufgaben

JSON Daten vorbereiten
Sortierung mittels JSON
Objekt-Key testen

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)

Aufgaben

Selektion mittels
JSON Keys testen
Selektion mittels
JSON Key/Value testen

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)

Aufgaben

Produkt Metadatadaten
erweitern um "vendor"
git checkout exercise7
Controller "/" erweitern um
Join mit vendor Tabelle
Controller "/browse/{category}"
erweitern um Join mit vendor
Tabelle

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)

Aufgaben

git checkout exercise8
Controller "/products" erweitern
um array_to_json() Abfrage
Controller "/product/{productId}"
erweitern um row_to_json()
Abfrage

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>

Aufgaben

"imports" Tabelle
anlegen
Inserts durchspielen
git checkout exercise9
CLI Import Command
erweitern

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)

Aufgabe

Mit xpath() Abfragen experimentieren

XML Daten durchsuchen

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

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

XML Statistik

SELECT UNNEST(xpath('/products/product/@vendor', contents)::text[])
AS vendor, COUNT(*) as products FROM imports GROUP BY vendor;

 vendor | products
--------+----------
 Lenovo |        1
 Asus   |        1
(2 rows)

Aufgaben

Mit xpath_exists() Anfragen
experimentieren
Statistik-Generierung testen

XML Export

SELECT query_to_xml('SELECT "id", "name" FROM products', true, false,
'') AS xml;

                              xml
---------------------------------------------------------------
 <table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <row>
   <id>1</id>
   <name>Lenovo YOGA Tablet</name>
 </row>
 <row>
   <id>2</id>
   <name>Asus Transformer Book</name>
 </row>
 </table>
(1 row)

Aufgabe

Mit query_to_xml() Abfragen experimentieren

XML Funktionen

xmlconcat()
xmlforest()
xmlagg()
xml_is_well_formed()
query_to_xml()
xpath()
...

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
);

Aufgaben

hstore Extension aktivieren
Tabelle "audit" anlegen

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();

Aufgaben

"audit" Funktion anlegen
"audit" Trigger für Tabelle
"products" anlegen

Ä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)

Aufgaben

Änderungen an Produkten
vornehmen
Unterschiede via hstore
Selektion ermitteln

Tabellenpartitionierung

« [...] Partitioning refers to splitting what is logically
one large table into smaller physical pieces. »
- PostgreSQL documentation

Tabellenpartitionierung

CREATE TABLE audit_2014(
    CHECK ( EXTRACT(YEAR FROM "change_date") = 2014 )
) INHERITS ("audit");

CREATE TABLE audit_2015(
    CHECK ( EXTRACT(YEAR FROM "change_date") = 2015 )
) INHERITS ("audit");

Trigger

CREATE OR REPLACE FUNCTION audit_part() RETURNS TRIGGER AS
$BODY$
DECLARE
  _tablename text;
  _year text;
  _result record;
BEGIN
  _year := to_char(NEW."change_date", 'YYYY');
  _tablename := 'audit_'||_year;

  PERFORM 1
  FROM   pg_catalog.pg_class c
  JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  WHERE  c.relkind = 'r' AND c.relname = _tablename;

Trigger

IF NOT FOUND THEN
    EXECUTE 'CREATE TABLE ' || quote_ident(_tablename) || ' (
      CHECK ( EXTRACT(YEAR FROM change_date) = ' || quote_literal(_year) || '))
    ) INHERITS (audit)';
  END IF;

  EXECUTE 'INSERT INTO ' || quote_ident(_tablename) || ' VALUES ($1.*)' USING NEW;
  RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER audit_part BEFORE INSERT on audit
    FOR EACH ROW EXECUTE PROCEDURE audit_part();

Aufgaben

Kindtabellen "audit_2014",
"audit_2015" anlegen
Funktion "audit_part" anlegen
Trigger "audit_part" anlegen

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;

Installation Redis FDW

CREATE USER MAPPING FOR PUBLIC
    SERVER redis_server
    OPTIONS (password 'secret');

Aufgaben

"redis_fdw" aktivieren
Server "localhost"
registrieren
Tabelle "redis_db0"
registrieren

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)

Aufgaben

Inserts und Updates testen
git checkout exercise10
CLI Import Command
abändern

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)

Aufgaben

"rating" Spalte aus "products"
Tabelle entfernen
Alle SQL Abfragen in
allen Controllern anpassen

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?