Key-Value-Store in PostgreSQL

Standard

Vor kurzem habe ich mir die relationale Open-Source-Datenbank PostgreSQL angesehen. Dabei sind mir einige Features aufgefallen, die normalerweise mit NoSQL-Datenbanken in Verbindung gebracht werden. Eines davon ist die hstore-Erweiterung.

Die hstore-Erweiterung

Neuere PostgreSQL-Versionen enthalten die hstore-Erweiterung, die einen neuen Datentyp hstore einführt. Darin können Schlüssel-Wert-Paare gespeichert werden, wobei die Erweiterung nur String als Datentyp für Schlüssel und Wert kennt. Die Daten in dem hstore-Feld müssen keinem felstgelegtem Schema folgen.

Erstellen der Datenbank

Um die Erweiterung zu verwenden, muss sie zunächst in der Datenbank installiert werden.

    CREATE EXTENSION hstore;

Danach steht der neue Datentyp hstore für Tabellen zur Verfügung:

    CREATE TABLE account
    (
      id serial PRIMARY KEY,
      login varchar(40) NOT NULL UNIQUE,
      password varchar(40) NOT NULL,
      info hstore
    );

Das SQL-Kommando INSERT kann neue Daten für die hstore-Spalte einfügen. Die Schlüssel-Wert-Paare werden als String durch Kommas getrennt. Der String kann mit ::hstore in den richtigen Datentyp konvertiert werden:

    INSERT INTO account (login, password, info)
        VALUES ('andreas', 'securepassword', 'location => "Wien", age => 38'::hstore);
    INSERT INTO account (login, password, info)
        VALUES ('hugo', 'othersecurepassword', 'location => "Wien", age => 42, married_with => "daniela"'::hstore);
    INSERT INTO account (login, password, info)
        VALUES ('daniela', 'pqwaösdkjf', 'location => "Linz", married_with => "hugo"'::hstore);
    INSERT INTO account (login, password, info)
        VALUES ('agnes', 'krzlzaf', 'age => 39'::hstore);

Operatoren für den Key-Value-Store

Die hstore-Erweiterung implementiert neue Operatoren, um die Schlüssel-Wert-Paare in SQL-Abfragen zu verwenden. So kann man z.B. mit dem Operator ‘->’ auf die Werte in der hstore-Spalte mit dem Schlüssel zugreifen. Den Operator kann man z.B. in Abfragen verwenden:

    SELECT login, info->'age' as age, info->'married_with' as married_with
        FROM account WHERE info->'location' = 'Wien';

Der Operator kann auch in Joins verwendet werden:

    SELECT a1.login, a1.info->'location' as location,
           a2.login as partner, a2.info->'location' as partnerlocation
      FROM account a1 INNER JOIN account a2
        ON a1.info->'married_with' = a2.login;

Der Operator ‘?’ überprüft, ob ein Schlüssel in der hstore-Spalte verfügbar ist.

    SELECT login FROM account WHERE info ? 'married_with';

Der Operator ‘-‘ entfernt ein Schlüssel-Wert-Paar aus einem hstore:

    UPDATE account SET info = info::hstore - 'married_with'::text
        WHERE info->'married_with'='daniela';
    UPDATE account SET info = info::hstore - 'married_with'::text
        WHERE info->'married_with'='hugo';

Der Operator ‘||’ verkettet zwei hstores:

    UPDATE account SET info = info || 'married_with=>"agnes"' WHERE login='hugo';
    UPDATE account SET info = info || 'married_with=>"hugo", location=>"Wien"'
        WHERE login='agnes';

Bestehende Werte werden dabei geändert:

    UPDATE account SET info = info || 'location=>"Graz"' WHERE login in ('hugo', 'agnes');

Mehr Informationen über die hstore-Erweiterung gibt es in der PostgreSQL-Dokumentation.

Zugriff auf die Datenbank aus C#

Von C# aus kann man mit Npgsql auf eine PostgreSQL-Datenbank zugreifen. Neue Daten kann man dann so in eine Tabelle mit einer hstore-Spalte einfügen:

    var cmdText = "INSERT INTO account (login, password, info) VALUES (:login, :password, :info::hstore)";
    var info = "location => \"Wien\", age => 42, married_with => \"daniela\"";
    using (var command = new NpgsqlCommand(cmdText, connection))
    {
        command.Parameters.Add(new NpgsqlParameter("login", NpgsqlDbType.Varchar, 40));
        command.Parameters.Add(new NpgsqlParameter("password", NpgsqlDbType.Varchar, 40));
        command.Parameters.Add(new NpgsqlParameter("info", NpgsqlDbType.Text));

        command.Parameters[0].Value = "hugo";
        command.Parameters[1].Value = "pqwesdf";
        command.Parameters[2].Value = info;
    
        command.ExecuteNonQuery();
    }

Den Wert für die hstore-Spalte muss man selbst in einen String serialisieren und als Datentyp NpgsqlDbType.Text an das Parameterobjekt übergeben. Im SQL-Kommando muss man den Wert auf den Datentyp hstore casten (“:info::hstore“).

    var cmdText = "SELECT login, password FROM account WHERE data->'location' = :location";
    using (var command = new NpgsqlCommand(cmdText, connection))
    {
        command.Parameters.Add(new NpgsqlParameter("location", NpgsqlDbType.Varchar, 40));
        command.Parameters[0].Value = "Wien";

        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                var login = reader.GetString(reader.GetOrdinal("login"));
                var password = reader.GetString(reader.GetOrdinal("password"));

                Console.WriteLine("Login: '{0}', Password: '{1}'", login, password);
            }
        }
    }

Natürlich können auch andere Operatoren verwendet werden:

    var cmdText = "SELECT login, password FROM account WHERE NOT data ? 'age'"; 
    using (var command = new NpgsqlCommand(cmdText, connection))
    {
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                var login = reader.GetString(reader.GetOrdinal("login"));
                var password = reader.GetString(reader.GetOrdinal("password"));

                Console.WriteLine("Login: '{0}', Password: '{1}'", login, password);
            }
        }
    }

Zugriff mit Python

In Python kann man psycopg2 für den Zugriff auf eine PostgreSQL-Datenbank benutzen:

    import psycopg2

    connection_string = <INSERT CONNECTION STRING>
    conn = psycopg2.connect(connection_string)
    cur = conn.cursor()
    cmdText = 'INSERT INTO account (login, password, info) VALUES (%s, %s, %s::hstore)'
    cur.execute(cmdText, ('andreas', 'qwpoeir', 'location => "Wien", age => 38'))
    cur.execute(cmdText, ('daniela', 'askdjf', 'location => "Wien", married_with => "hugo"'))
    cur.execute(cmdText, ('agnes', 'asdfoi', 'age => 37'))
    cur.execute(cmdText, ('hugo', 'ypxoiu', 'location => "Linz", age => 42, married_with => "daniela"'))
    conn.commit()
    cur.close()
    conn.close()

Zugriff aus Node.js

Für Node.js kann das Modul pg für den Zugriff auf PostgreSQL verwendet werden:

    var pg = require('pg');
    var conString = <INSERT CONNECTION STRING>;

    var client = new pg.Client(conString);
    client.connect();

    var cmdText = "INSERT INTO account (login, password, info) VALUES ($1, $2, $3)";
    client.query(cmdText, ['andreas', 'asdf', 'location=>"Wien", age=>38']);
    client.query(cmdText, ['daniela', 'qwre', 'location=>"Wien", married_with=>"hugo"']);
    client.query(cmdText, ['agnes', 'lkj', 'age=>37']);
    client.query(cmdText, ['hugo', 'ppoiu', 'location=>"Linz", age=>42, married_with=>"daniela"']);

    var query = client.query("SELECT login, password FROM account WHERE info->'location' = $1", ['Wien']);

    query.on('row', function(row) {
      console.log('Login: "%s", Password: "%s"', row.login, row.password);
    });

    query.on('end', function() {
      client.end();
    });

Zusammenfassung

Die hstore-Erweiterung fügt einen neuen Datentyp, neue Operatoren und neue Funktionen zu einer Datenbank hinzu. Damit kann mit der relationalen Open-Source-Datenbank PostgreSQL eine Art Key-Value-Store realisiert werden.

In C# muss man auf die Datentypen achten, wenn man mit Npgsql die Datenbank verwendet. In Python und mit Node.js können die als String serialisierten Informationen direkt als Parameter übergeben werden. Ich würde mir wünschen, dass ich die Daten für die hstore-Spalte als Dictionary übergeben könnte. Leider wird das von den Datenbankbibliotheken (noch) nicht unterstützt.