Git en SQL

avec PostgreSQL

Appuyez sur [s] pour ouvrir les notes présentateur dans une nouvelle fenêtre.

Qui suis-je ?

Florian FERRIERE

@FerriereFlorian

Développeur chez Norsys

Merci

PostgreSQL

Fonctionnalités générales

SGBDRO
Accessible : JDBC, pgsql, ...
SQL :
- Triggers
- Common Table Expressions
- Procedural Language (PL/pgSQL, PL/JAVA, PL/PHP, ...)
- Vues et Vues matérialisées
- ...
JSON
PostGIS
...
Foreign Data Wrapper

Fonctionnalités générales :

Server de Gestion de Base de Données Relationnel Objet
Accessible avec plein de language de programation : - JDBC pour JAVA - lib pgsql pour PHP
SQL : - PL : PL/pgSQL, PL/Tcl, PL/Perl et PL/Python de base - Extension pour PL : PL/JAVA, PL/PHP, PL/Py, PL/R, PL/Ruby
PostgreSQL permet de stocker et de manipuler du JSON.
PostGIS : gestion des objets Spatiaux et Géographique. Utilisé par OpenStreetMap.
...
Foreign Data Wrapper

Foreign Data Wrapper

Qu'est-ce que c'est ?

Norme SQL/MED (Management of External Data)

Créée en 2003

Création de wrapper :

Nativement en C
En python avec "Multicorn"
En ruby avec "Holycorn"

Le Foreign Data Wrapper est une implémentation de la norme SQL/MED (Management of External Data), créée en 2003. Comme son nom l'indique, cette norme permet la gestion des données externes, depuis un autre SGBD ou des sources diverses.
Il est possible de créer son propre wrapper en C en utilisant les fonctions prévues à cet effet.
Des extensions comme "Multicorn" en python ou "Holycorn" en ruby apporte un lot de wrapper et permet de créer des wrappers plus facilement. Grace à ça, il existe, à ce jour, un nombre important de wrapper.

Quelques Wrappers

PostgreSQL

SGBD SQL courants : MySQL, Oracle, SQL Server, SQLite, ...

Connecteurs SQL génériques : ODBC, JDBC

NoSQL : MongoDB, Cassandra, Redis, ...

CSV, XML, LDAP

Git, Logs, WWW, IMAP

Processus, Twitter, Docker, RabbitMQ, ...

Voici une liste non exhaustive :

Forcément nous avons la possibilité de connecté un serveur PostgreSQL avec un autre.
Mais également avec d'autres SGBD.
Soit avec les connecteurs natifs
Soit avec des connecteurs génériques
Il est aussi possible de dialoguer avec des serveurs NoSQL.
Il y a aussi des connecteurs vers des fichiers CSV, XML ou un serveur LDAP
Ou encore des systèmes plus atypiques comme Git, les logs systèmes, des services web ou des mails via le protocol IMAP
Et pourquoi pas la liste des processus du système, votre timeline twitter
Et même une connexion avec le serveur docker pour lister les containeurs ou les images
Très récemment un wrapper pour RabbitMQ, c'est du coup très prometteur pour mettre en place de l'event-sourcing.
Et tant d'autre

Git FDW

Wrappers

C
Multicorn

Mise en place

Initialisation de l'extension



    CREATE EXTENSION multicorn;

Déclaration du serveur



    CREATE SERVER git_server
        FOREIGN DATA WRAPPER multicorn
        OPTIONS (
            wrapper 'multicorn.gitfdw.GitFdw'
        );

Déclaration de la table


    CREATE FOREIGN TABLE git_atoum (
        author_name VARCHAR(128),
        author_email VARCHAR(128),
        message TEXT,
        hash VARCHAR(128),
        date TIMESTAMP
    )
    SERVER git_server
    OPTIONS (
        path '/opt/atoum'
    );

Utilisation

Simple SELECT


    SELECT *
    FROM git_atoum
    ORDER BY date DESC
    LIMIT 10;

Résultat

  author_name   |   author_email  |                                                    message                                                    |                   hash                   |        date
----------------+-----------------+---------------------------------------------------------------------------------------------------------------+------------------------------------------+---------------------
 Frédéric Hardy | frederic.hardy@ | Evaluate test about variadic only if PHP version ≥ 5.6.0.                                                     | 8c128304a63fb1f27da3be7db1b4cc561cb0ecd6 | 2016-11-19 23:21:09
 Frédéric Hardy | frederic.hardy@ | Evaluate class if PHP version ≥ 5.6.0.                                                                        | 135080a82c2b4fbc7f74c05b1c3494046a675a48 | 2016-11-19 21:54:04
 Frédéric Hardy | frederic.hardy@ | Use full PHP version number.                                                                                  | 752f9cc20eee4dd7db16b68bff69b788e9bb4ec9 | 2016-11-19 21:35:23
 Frédéric Hardy | frederic.hardy@ | Fix PHP version comparator.                                                                                   | 6077ee7b4a99fb4441225f5e4b7c6f1cc22c9838 | 2016-11-19 21:27:27
 Frédéric Hardy | frederic.hardy@ | Check `allIsInterface()` in mock generator behavior only if PHP version ≥ 5.6.                                | f7905e9a11587a47eb7239b08295c5125be184ce | 2016-11-19 21:14:49
 Frédéric Hardy | frederic.hardy@ | Fix bug in mock generator in case of variadic arguments in constructor and method `allIsInterface()` is used. | a287fc1256e1ea269b5fe41de8f370c54a4c29c1 | 2016-11-19 21:04:04
 Julien BIANCHI | jubianchi@      | Merge pull request #660 from agallou/fix_santa                                                                | e9d37193dc5ef59ca5d0f00da46c73770b78d39c | 2016-11-11 10:41:06
 Adrien Gallou  | adriengallou@   | fix santa report                                                                                              | 487eccd5d6ef4bd00dc67943d1d6264c13103c9e | 2016-10-16 09:57:44
 jubianchi      | jubianchi@      | Set version to dev-master.                                                                                    | 1809e7680420918616af7831d71f575364f2a9f9 | 2016-10-08 11:41:17
 jubianchi      | jubianchi@      | Set version to 2.9.0-beta1.                                                                                   | 56f1450241742aed188733605ef594816cebdbdc | 2016-10-08 11:41:14
(10 rows)

Time: 59.394 ms

Filtrage par auteur


    SELECT author_name, message, date
    FROM git_atoum
    WHERE author_name ILIKE '%florian%'
    ORDER BY date DESC;

Résultat


   author_name    |                       message                        |        date
------------------+------------------------------------------------------+---------------------
 Florian FERRIERE | Merge branch 'master' into correct-datetimeimmutable | 2016-03-23 08:18:59
 Florian Ferriere | Correction for dateTimeImmutable happens with #547   | 2016-03-21 14:14:52
(2 rows)

Plus avancé


    SELECT to_char(date, 'YYYY-TQ') AS quarter,
            COUNT(*) as nb_commit
    FROM git_atoum
    GROUP BY to_char(date, 'YYYY-TQ')
    ORDER BY quarter ASC;

Résultat

 quarter | nb_commit
---------+-----------
 2010-T2 |        25
 2010-T3 |        64
 2010-T4 |       108
 2011-T1 |       144
 2011-T2 |       199
 2011-T3 |       230
 2011-T4 |       174
 2012-T1 |        89
 2012-T2 |       137
 2012-T3 |       150
 2012-T4 |       184
 2013-T1 |       215
 2013-T2 |       234
 2013-T3 |       119
 2013-T4 |       217

 quarter | nb_commit
---------+-----------
 2014-T1 |       108
 2014-T2 |        29
 2014-T3 |        26
 2014-T4 |       110
 2015-T1 |        58
 2015-T2 |        46
 2015-T3 |        65
 2015-T4 |        75
 2016-T1 |        85
 2016-T2 |        27
 2016-T3 |        43
 2016-T4 |         7
 (27 rows)

Time: 63.959 ms

Graph (requête)


    SELECT to_json(t)
    FROM (
        SELECT  array_agg(data.quarter) AS labels,
                array_agg(data.nb_commit) AS data
        FROM (
            SELECT to_char(date, 'YYYY-TQ') AS quarter,
                    COUNT(*) as nb_commit
            FROM git_atoum
            GROUP BY to_char(date, 'YYYY-TQ')
            ORDER BY quarter ASC
        ) AS data
    ) AS t;

Graph (resultat)

Contrainte

Uniquement les commits

git log --all

Concurrence

2 projets en Go

gitql/gitql

cloudson/gitql

gitql/gitql

Utilisation

    gitql query 'SHOW TABLES;'

    +--------------+
    |    TABLE     |
    +--------------+
    | blobs        |
    | commits      |
    | objects      |
    | refs         |
    | tags         |
    | tree_entries |
    +--------------+

Utilisation

    gitql query 'DESCRIBE TABLE commits;'

    +----------------+-------------------------+
    |      NAME      |          TYPE           |
    +----------------+-------------------------+
    | hash           | string                  |
    | author_name    | string                  |
    | author_email   | string                  |
    | author_when    | timestamp with timezone |
    | comitter_name  | string                  |
    | comitter_email | string                  |
    | comitter_when  | timestamp with timezone |
    | message        | string                  |
    +----------------+-------------------------+

Utilisation

 gitql query 'SELECT * FROM refs ORDER BY name LIMIT 4;'

 +------------------------------------------+---------------------------+-----------+---------+-----------+--------+-------------------+
 |                   HASH                   |           NAME            | IS BRANCH | IS NOTE | IS REMOTE | IS TAG |      TARGET       |
 +------------------------------------------+---------------------------+-----------+---------+-----------+--------+-------------------+
 | 0000000000000000000000000000000000000000 | HEAD                      | false     | false   | false     | false  | refs/heads/master |
 | 8c128304a63fb1f27da3be7db1b4cc561cb0ecd6 | refs/heads/master         | true      | false   | false     | false  |                   |
 | 33597f45906cfc9ded860cb598af0600ffda8e69 | refs/remotes/origin/1.x   | false     | false   | true      | false  |                   |
 | 0b6ea4c2909b8a38e2abcd3a9562750f490ba2ba | refs/remotes/origin/2.0.x | false     | false   | true      | false  |                   |
 +------------------------------------------+---------------------------+-----------+---------+-----------+--------+-------------------+

gitsql/gitsql : Défauts

Temps d'exécution (1/2)

 time gitql query 'SELECT hash, author_name, author_when, message
 FROM commits ORDER BY author_when DESC LIMIT 2;'

 +------------------------------------------+----------------+---------------------------+-----------------------------------------------------------+
 |                   HASH                   |  AUTHOR NAME   |          AUTHOR WHEN      |            MESSAGE                                        |
 +------------------------------------------+----------------+---------------------------+-----------------------------------------------------------+
 | 8c8d424403f849310bbe3fabb74d9fce1389b7f8 | Frédéric Hardy | 2016-11-23 18:46:48 +0100 | Now, each mock instance may be unique.                    |
 | 8c128304a63fb1f27da3be7db1b4cc561cb0ecd6 | Frédéric Hardy | 2016-11-20 00:21:09 +0100 | Evaluate test about variadic only if PHP version ≥ 5.6.0. |
 +------------------------------------------+----------------+---------------------------+-----------------------------------------------------------+

real 0m8.169s user 0m16.476s sys 0m0.764s

Temps d'exécution (2/2)

 SELECT hash, author_name, date, message
 FROM git_atoum ORDER BY date DESC LIMIT 2;

                    hash                   |  author_name   |        date         |                          message
 ------------------------------------------+----------------+---------------------+-----------------------------------------------------------
  8c128304a63fb1f27da3be7db1b4cc561cb0ecd6 | Frédéric Hardy | 2016-11-19 23:21:09 | Evaluate test about variadic only if PHP version ≥ 5.6.0.
  135080a82c2b4fbc7f74c05b1c3494046a675a48 | Frédéric Hardy | 2016-11-19 21:54:04 | Evaluate class if PHP version ≥ 5.6.0.
 (2 rows)

 Time: 57.630 ms

cloudson/gitql

Utilisation

    $ git ql -show-tables

    Tables:

    branches: name, full_name, hash.
    commits: hash, date, author, author_email,
             committer, committer_email, message, full_message.
    refs :name, full_name, type, hash.
    remotes: name, url, push_url, owner.
    tags: name, full_name, hash.

Utilisation

 $ time git ql 'SELECT hash, author, date, message
                FROM commits ORDER BY date DESC LIMIT 2'

 |---------------------------------------------------------------------------------------------------------------------------------------------|
 | Hash                                     | Author         | Date                | Message                                                   |
 |---------------------------------------------------------------------------------------------------------------------------------------------|
 | 8c128304a63fb1f27da3be7db1b4cc561cb0ecd6 | Frédéric Hardy | 2016-11-20 00:21:09 | Evaluate test about variadic only if PHP version ≥ 5.6.0. |
 |---------------------------------------------------------------------------------------------------------------------------------------------|
 | 135080a82c2b4fbc7f74c05b1c3494046a675a48 | Frédéric Hardy | 2016-11-19 22:54:04 | Evaluate class if PHP version ≥ 5.6.0.                    |
 |---------------------------------------------------------------------------------------------------------------------------------------------|

real 0m0.004s user 0m0.001s sys 0m0.002s

Suppléments


 $ git ql -type=json 'SELECT * FROM refs LIMIT 4'

 [
     {"full_name":"refs/heads/master","hash":"8c128304a63fb1f27da3be7db1b4cc561cb0ecd6","name":"master","type":"branch"},
     {"full_name":"refs/remotes/origin/HEAD","hash":"NULL","name":"origin/HEAD","type":"remote"},
     {"full_name":"refs/remotes/origin/1.x","hash":"33597f45906cfc9ded860cb598af0600ffda8e69","name":"origin/1.x","type":"remote"},
     {"full_name":"refs/remotes/origin/2.0.x","hash":"0b6ea4c2909b8a38e2abcd3a9562750f490ba2ba","name":"origin/2.0.x","type":"remote"}
 ]

Défauts

Filtres

Consultation

Documentation

Page des Foreign Data Wrappers

Documentation PG

Postgresql : la nouvelle base orientée document par Yan Bonnel (Vidéos)

Projet Github