Die Kunst
Daten zu speichern

Stephan Hochdörfer // 04.05.2015

&uUml;ber mich

  • Stephan Hochdörfer
  • Head of Technology, bitExpert AG
    Mannheim
  • S.Hochdoerfer@bitExpert.de
  • @shochdoerfer

  • #PHP, #DevOps, #Automation, #unKonf

Ende des relationalen Wegs?

NoSQL Alternativen

Graphdatenbanken

Spalten Datenbanken

Key-Value Datenbanken

Dokumenten Datenbanken

Wähle weise...



...oder nimm PostgreSQL!

JSON




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

JSON Spalte anlegen


CREATE TABLE products (
    name         text,
    price        float,
    description  text,
    metadata     jsonb
);

JSON Daten speichern


INSERT INTO products VALUES (
    'Produkt A',
    123.3,
    'Lorem ipsum',
    ' { "color": "black", "weight": "3kg" } '
);


INSERT INTO products VALUES (
    'Produkt B',
    45.0,
    'Lorem ipsum',
    ' { "color": "red" } '
);

JSON Daten speichern


INSERT INTO products VALUES (
    'Produkt C',
    9.95,
    'Lorem ipsum',
    ' { "color": ["red", "green", "blue", ] } '
);

ERROR:  invalid input syntax for type json
LINE 5:     ' { "color": ["red", "green", "blue", ] } '
            ^
DETAIL:  Expected JSON value, but found "]".
CONTEXT:  JSON data, line 1:  { "color": ["red", "green", "blue", ]...

JSON Daten abfragen

SELECT p.name FROM colors c LEFT JOIN products p ON 
    (c.name = p.metadata->>'color');

   name
-----------
 Produkt A

(1 Zeile)

Foreign Data Wrappers




« The foreign data wrapper is responsible for fetching
data from the remote data source and returning it to
the PostgreSQL executor. » - PostgreSQL documentation

Foreign Data Wrappers

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

PATH=/usr/lib/postgresql/9.4/bin/:$PATH make USE_PGXS=1
PATH=/usr/lib/postgresql/9.4/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 text) 
    SERVER redis_server
    OPTIONS (database '0');

CREATE USER MAPPING FOR PUBLIC
    SERVER redis_server;

Foreign Data Wrappers

SELECT * FROM "redis_db0";

  key   |  value
--------+---------
 cnt    | 7
 mykey1 | myvalue1
 mykey2 | myvalue2

(3 Zeilen)

Event Sourcing




« [...] ensures that all changes to application state are stored
as a sequence of events. Not just can we query these events,
we can also use the event log to reconstruct past states. »
- Martin Fowler

(Ohne) Event Sourcing

  storageId  |  productId  |  amount
-------------+-------------+----------
  1          |  7          | 50
  2          |  7          | 10
  1          |  23         | 6

(3 Zeilen)

(Mit) Event Sourcing

<?php
$eventStore = new EventStore();

$store = new Store();
$product = new Product();

$eventStore->handle(new PutInStoreEvent($store, $product, 10, 
date('Y-m-d H:i:s')));
$eventStore->handle(new PutInStoreEvent($store, $product, 30, 
date('Y-m-d H:i:s')));
$eventStore->handle(new RemoveFromStoreEvent($store, $product, 20, 
date('Y-m-d H:i:s')));
$store->getAmountFor($product); // 20







Vielen Dank! Fragen?