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.

79 thoughts on “Key-Value-Store in PostgreSQL

  1. The week has just begun and that means it’s time to relieve some stress. You’ve got plenty of work ahead of you. You’ll need all the relaxation you can sneak in. No one can melt the stress like a cam girl can. At http://www.camgirl.pw you’re sure to find a cutie that will make you forget all about whatever was stressing you out.

  2. I like it! Interesting tips over here. It’s pretty worth enough for me. Personally, if all website owners and bloggers made good content as you did, the internet will be a lot more useful than ever before.| I couldn’t resist commenting. I ‘ve spent 2 hours looking for such infos. I will also share it with some friends interested in it. I’ve just bookmarked this website. Finished with the work done, I’ll enjoy some online Webcams. Thank you very much!! Greetings from Catalonia!

  3. Right now you could be having a whole lot of fun. All it takes is visiting http://www.camgirl.pw There’s no site out there more fun than that one. It’s loaded with girls who know how to treat a man right. They’ll make sure your day is a very good one.

  4. Good! Interesting article over this website. It’s pretty worth enough for me. Personally, if all webmasters and bloggers made good content as you did, the net will be a lot more useful than ever before.| I could not resist commenting. I have spent 3 hours trying to find such informations. I will also share it with a couple of friends interested in it. I’ve just bookmarked this web. Right now with the job done, I’ll visit some Russia 2018 Webcams. Gracias!!! Regards from Russia 2018!

  5. My coder is trying to convince me to move to .net
    from PHP. I have always disliked the idea because
    of the costs. But he’s tryiong none the less. I’ve been using Movable-type on several websites for about a year and
    am nervous about switching to another platform. I have heard fantastic things about blogengine.net.
    Is there a way I can import all my wordpress
    posts into it? Any kind of help would be greatly appreciated!

  6. You’re in some serious need of relaxation. That’s where a site like http://www.camgirl.pw comes in handy. You can do a whole lot of relaxing there. You’ll find plenty of fun girls to talk to. These are the hottest and most wild girls on the internet. Chat with them and have yourself a good time.

  7. I’m amazed, I have to admit. Rarely do I come across a blog
    that’s both equally edcative and entertaining, and without a doubt, you’ve hiit the nail on the head.
    The issue is something which not enough men and women aare speaking
    intelligently about. I’m very happy I came across this during mmy search for something relating to this.

Leave a Reply

Your email address will not be published.