Libérez Postgresql

dans vos développements PHP

Grégoire HUBERT - PHPTour 2014

À propos

Grégoire HUBERT
PHP & Postgresql depuis 1999
Auteur de Pomm Pomm
Fondateur de PragmaFabrik
@chanmix51 @PommProject
https://joind.in/11220

The (yet) undetected power of Postgresql

PHP & SQL

Paradigmes différents
Interface de communication très pauvre

ORM & SQL

Impose le modèle OO dans un mode relationnel
Masque ce qui se passe en base
Difficile de régler les performances

Comment votre DBA voit vos données

Comment les dev PHP voient leurs données

Un problème ?

➥ Impose le modèle OO dans un mode relationnel
➥ Lie statiquement les entités aux tables
➥ Est équivalent à un moteur "clé => entité"

Pourquoi ne pas intégrer la logique relationnelle en OO ?

Les moteurs relationnels manipulent des ensembles
Les ensembles sont composés de tuples
Qu'on transforme à l'aide de projections
Les entités sont des tuples et sont donc extensibles
Les tables sont juste des ensembles persistés

Pomm

Intégrer la logique relationnelle en OO

Ensembles projetés en entités
Valeurs converties depuis / vers la DB
Type équivalents en PHP

Le système de projection

Qu'est ce qu'une projection ?

En SQL, une projection est la liste des champs retournée par un SELECT :

SELECT
  "field1" AS "fieldA",
  "field2" AS "fieldB",
  ...
  "fieldN" AS "fieldX"
FROM ...
                    

Gérer la projection

Définir ce qui sera hydraté dans les entités « souples »

$fields = $map->getSelectFields();
    [
    'fieldA' => 'field1',
    'fieldB' => 'field2',
    ...
    'fieldX' => 'fieldN'
    ]
                    

Projection : l'opération "SELECT"

$student = $studentMap->findByPK(['student_id' => 3);

instance of "Student" => {
    'student_id' => 3,
    'first_name' => 'pika',
    'last_name' => 'chu',
    'birthdate' => instance of DateTime => {...
    'password'  => '32ªØ~ぜ',
    ...
}
                      

Modifions la projection

public function getSelectFields($alias = null)
{
    $fields = parent::getSelectFields($alias);
    unset($fields['password']);
    $fields['age'] = sprintf(
        "age(%s)",
        $this->aliasField('birthdate', $alias)
        );

    return $fields;
}
                    

Entité modifiée

$student = $studentMap->findByPK(['student_id' => 3);

instance of "Student" => {
    'student_id' => 3,
    'first_name' => 'pika',
    'last_name' => 'chu',
    'birthdate' => instance of DateTime => {...
    'age'       => '17 years 3 mons 28 days'
    ...
}
                      

Ajoutons les convertisseurs pour les nouveaux champs

class StudentMap extends BaseStudentMap
{
    public function initialize()
    {
        parent::initialize();

        $this->addVirtualField('age', 'interval');
    }
    ...
                    

Entité modifiée

$student = $studentMap->findByPK(['student_id' => 3);

instance of "Student" => {
    'student_id' => 3,
    'first_name' => 'pika',
    'last_name' => 'chu',
    'birthdate' => instance of DateTime => {...
    'age'       => instance of DateInterval => {...
    ...
}
                      

Projection : l'opérateur "RETURNING"

'INSERT', 'UPDATE' and 'DELETE' peuvent également être projetés avec l'opérateur RETURNING :

DELETE FROM student
  WHERE student_id = 3
RETURNING "field1" AS "fieldA", ...
                      

Entité effacée

$student = $studentMap->deleteByPK(['student_id' => 3);

instance of "Student" => {
    'student_id' => 3,
    'first_name' => 'pika',
    'last_name' => 'chu',
    'birthdate' => instance of DateTime => {...
    'age'       => instance of DateInterval => {...
    ...
}
                      

Faire simple

public function deleteController($id)
{
    $student = $this->app['pomm']
        ->getMapFor('\App\School\Student')
        ->deleteByPK(['student_id' => $id]);

    if (!$student) {
        return $this->app->abort("not found", 404);
    }

    return json_encode($student->export());
}
                    

Convertisseurs et types

Postgres FTW

Un problème d'interface

            ("<(3,2),1>","{""2014-05-24 13:59:57.142888+00"",""2014-05-24 12:59:57.142888+00""}")
                      

Que représente cette ligne de données ?

Comment l'exploiter ?

Quelques types de Postgres

  • ➥ boolean, bitchar, bitvar
  • ➥ strings, uuid, xml, json, inet
  • ➥ numbers, timestamps with time zone, intervals
  • ➥ integer and timestamps, ranges
  • ➥ point, circle, segment, box, polygon
  • ➥ HStore, LTree, ts_vector, bytea
  • ➥ Types composites, objets
  • ➥ Tableaux de tous les types listés
  • Ajoutez votre type ici

Convertisseurs de Pomm

  • ➥ boolean, bitchar, bitvar
  • ➥ strings, uuid, xml, json, inet
  • ➥ numbers, timestamps with time zone, intervals
  • ➥ integer and timestamps, ranges
  • ➥ point, circle, segment, box, polygon
  • ➥ HStore, LTree, ts_vector, bytea
  • ➥ Types composites, objets
  • ➥ Tableaux de tous les types listés
  • Ajoutez votre type ici

Convertisseurs par défaut

  • Pg [t|f] <=> PHP (bool) [true|false]
  • Pg timestamps <=> DateTime instances
  • Pg intervals <=> DateInterval instances
  • Pg ranges <=> Own range instances
  • Pg arrays <=> Tableaux PHP avec contenu converti
  • Facile à surcharger

Ajouter des convertisseurs

  • Point: '(1,1)'
  • $database->registerConverter(
        'Point',
        new \Pomm\Converter\PgPoint(),
        ['point']);
  • PHP: \Pomm\Type\Point instance
  • $entity->coords->x

Supporter
les domaines & sous types

  • CREATE DOMAIN latlong AS point CHECK (
        value[0] <= 180 and value[0] >= -180 and
        value[1] >= -90 and value[1] <= 90
    );
  • $database
        ->registerConverter('LatLong', new \Pomm\Converter\PgPoint(), ['point', 'public.latlong']);

LTree: chemins matérialisés

Utiles pour représenter des arbres à plat et des catégories (tags)

  • CREATE EXTENSION ltree;
  • Pg «out.restaurant.italian»
  • PHP «['out', 'restaurant', 'italian']»

HStore

Type clé => valeur

  • CREATE EXTENSION hstore;
  • Pg «'{"pika" => "chu", "plop" => "1" }'»
  • PHP «["pika" => "chu", "plop" => "1" ]»

Fabriquez vos propres types

Pour stocker des données complexes

Example: enroulements de transformateurs électriques :

  • Ils sont composés de :
  • 1 enroulement primaire
  • n enroulements secondaires

CREATE TYPE winding AS (voltage numeric(5,1), current numeric(5,3));
CREATE TABLE power_supply (
      -- autres champs
  "primary" winding NOT NULL,
  "secondaries" winding[] NOT NULL,
      -- autres champs

Créer le convertisseur associé

Pour les types composites

      $database->registerConverter(
          'Winding',
          new PgRow(
              $database,
              new RowStructure(['voltage' => 'numeric', 'current' => 'numeric'])
              ),
          ['transformer.winding']
          );
                      

Créer le type composite

 1 <?php
 2 namespace My\Database\Type;
 3 
 4 use \Pomm\Type\Composite;
 5 
 6 class Address extends Composite
 7 {
 8     public $voltage;
 9     public $current;
10 }
                      

Objets

Créer une table dans Postgres c'est créer un type composite

  SELECT pika,chu FROM plop;
  ┌──────┬─────┐
  │ pika │ chu │
  ├──────┼─────┤
  │ a    │   1 │
  │ b    │   2 │
  │ c    │   3 │
  └──────┴─────┘
  (3 lines)
                      

Objets !

Créer une table dans Postgres c'est créer un type composite

  SELECT plop FROM plop;
  ┌───────┐
  │ plop  │
  ├───────┤
  │ (a,1) │
  │ (b,2) │
  │ (c,3) │
  └───────┘
  (3 lines)
                      

Requêter

Requêter

Les types ne sont rien sans

leurs opérateurs et fonctions

yes, Postgres is awesome

Réseau

Cette adresses appartient elle à tel réseau ?

SELECT inet '172.17.0.2' <<  inet '172.16.0.0/12';

Trouver toutes les IP dans le réseau

$computers = $connection->getMapFor('Db\Network\Computer')
    ->findWhere('ip_address << $*', [$network]) ;

Ranges

Ces 2 intervales sont il contigus ?

SELECT numrange(1.1, 2.2) -|- numrange(2.2, 3.3);

Quel est le programme TV après celui-là ?

$programs = $connection->getMapFor('Db\App\Program')
    ->findWhere("hours -|- tsrange(null, $*) and channel = $*", [$end_hour, $channel])
;
                          

Geometrie

Quel est le point de croisement de 2 segments ?

SELECT lseg '((1,-1),(-1,1))' # lseg '((1,1),(-1,-1))';

Ces 2 segments sont ils perpendiculaires ?

SELECT lseg '((1,-1),(-1,1))' ?-| lseg '((1,1),(-1,-1))';

Geometrie

Un peu plus utile, la distance entre deux points ?

SELECT  point '(0,0)' <-> point '(1,1)';

Les stations de vélo proches de moi ?

    ->getMapFor('\City\Station')
    ->findWhere("circle(point($*, $*), $*) @> position)", [$pos_x, $pos_y, $radius]);
                          

Tableaux

Chercher des éléments

SELECT 2 = ANY(ARRAY[1, 2, 4]);

Trouver un PC à partir d'une de ses IP

$computers = $connection->getMapFor('Db\Network\Computer')
    ->findWhere("$* = ANY(interfaces)", [$ip_address])
;

HStore: clé => valeur

I18N

  • "en"=>"hello world", "fr"=>"salut Jules"
  • SELECT label->en AS label FROM product;

HStore: clé => valeur

I18N

Modifier le mapping

class RestaurantMap extends BaseRestaurantMap
{
    protected $culture = 'en';

    public function getSelectFields($alias = null)
    {
        $fields = parent::getSelectFields($alias);
        $fields['label'] = sprintf("label->%s", $this->culture);

        return $fields;
    }

    public function setCulture($culture)
    {
        $this->culture = $culture;
    }
}
            

JSON

  • Support natif de JSON
  • $map->findWhere("social_networks->>$* <> ''", ['facebook']);
  • Converti en tableaux assoc. PHP via json_decode()

Requêtes complexes

Requêtes complexes

Récupérer le nombre de commentaires d'un article

Je peux créer ma propre requête

  • Gérer la liste des champs
  • Gérer le nom des tables

Requêtes personnalisées

Les formateurs

    public function getArticleWithComments($article_id)
    {
        $sql = <<<SQL
SELECT
    :article_fields,
    count(c.*) AS comment_count
FROM
    :article_table a
      LEFT JOIN :comment_table c USING (article_id)
WHERE
    a.article_id = $*
GROUP BY
    a.article_id
SQL;

Requêtes personnalisées

Les formateurs

        $sql = strtr($sql, [
            ':article_fields' => $this->formatFieldsWithAlias('getSelectFields', 'a'),
            ':article_table'  => $this->getTableName(),
            ':comment_table'  => $this->connection->getMapFor('Blog\Comment')->getTableName()
        ]);

        return $this->query($sql, [$article_id]);
    }
                    

Custom queries

Use the objects Luke

      public function getArticleWithComments($article_id)
      {
          $sql = <<<SQL
  SELECT
      :article_fields,
      array_agg(c) AS comments
  FROM
      :article_table a
        LEFT JOIN :comment_table c USING (article_id)
  WHERE
      a.article_id = $*
  GROUP BY
      a.article_id
  SQL;

SQL a mauvaise réputation

  • Gérer les listes de champs
  • SELECT ... FROM (SELECT ... 
  • Fastidieux à écrire et déboguer

SQL peut être votre meilleur ami

  • ➥ Paradigme declaratif
  • ➥ Puissant langage de manipulation des données
  • ➥ Utilisez WITH pour faire des sous requêtes

la clause WITH

Si je veux seulement les 5 derniers commentaires ?

    public function getArticleWithComments($article_id)
    {
        $sql = <<<SQL
WITH
    last_comment AS (
        SELECT :comment_fields
        FROM :comment_table c
        WHERE c.article_id = $*
        ORDER BY c.created_at DESC LIMIT 5
)
SELECT
    :article_fields,
    array_agg(c) AS comments
FROM :article_table a, last_comment c
WHERE a.article_id = $*
GROUP BY a.article_id
SQL;
                  

Fonctions fenêtrées

Groupes de données sur critère

  • Comment avoir l'ID de l'article suivant & précédent ?
  • Les fonctionslag() et lead()
  • Comment classer des usines par pays par ordre de production ?
  • La fonction rank()

File de messages asynchone

Déclencher des travaux détachés ?

Utiliser le mécanisme LISTEN / NOTIFY de Postgres.

public function addCommentController()
{
    ...
    if ($comment_form->isValid())
    {
      $comment = $this->app['pomm']
          ->getMapFor('\Blog\Comment')
          ->createAndSaveObject($comment_form->getValues());
      $this->app['pomm']
          ->notify('db.change', json_encode($comment->export()));
                    

Pomm

Intégrer le relationel en OO

Façonnez vos données d'après votre métier
Gardez la main sur les performances

OMMs Vs ORMs

  • ➥ ORMs et OMMs sont deux approches différentes
  • ➥ Pour du développement générique, ORM + DBAL fonctionne
  • ➥ Pour les développements orientés métier, DBAL est contre-productif
  • ➥ OMMs permettent aux développeurs de profiter de 30 ans d'expérience des BDD

Vers l'infini et au delà

Foreign Data Wrappers

➥ BDD (Pg, Mysql, Oracle, JDBC...)
➥ NoSQL (Redis, Mongo, Neo4j...)
➥ WS (Twitter, Google, S3, Hadoop...)

Merci

Des questions ?

https://joind.in/11220

 www.pomm-project.org