Pomm

PostgreSQL / PHP Object Model Manager

Ernaelsten Gérard

Pomm  est un  gestionnaire de modèle objet  dédié au moteur de base de données PostgreSQL. Qu'est-ce qu'un gestionnaire de modèle objet ?

C'est avant tout un  hydrateur  d'objets qui utilise un convertisseur entre PHP et PostgreSQL pour assurer qu'un booléen dans Postgres sera vu depuis PHP comme tel, de même pour les tableaux, le type clé -> valeur 'HStore', les types géométriques, XML, JSON, etc.

Cette fonctionnalité de conversion est très importante, car le typage dans PostgreSQL est un élément incontournable de la définition du schéma par contrainte. La possibilité d'enrichir PostgreSQL avec des types personnalisés est prise en compte.

C'est également un gestionnaire de modèle orienté objet, car Pomm crée des classes de mapping qui lient les structures SQL avec des objets PHP. Nous verrons là encore les grosses différences entre Pomm et les ORM classiques et comment utiliser la puissance du SQL de Postgres au service d'une petite application. ♪

- Titre : Pomm

- Auteur : Ernaelsten Gérard

- Parution : 8 février 2013

- Mise à jour : 8 février 2013

- Public visé :

- Licence : Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright ® 2013 Ernaelsten Gérard. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- I. En quoi Pomm est-il différent d'un ORM et pourquoi l'utiliser ?

- I-A. Mise en place de l'application

- I-B. Bienvenue dans la société El-Caro Corp.

- I-C. Génération du modèle PHP

- I-D. Premiers pas

- I-E. Entités élastiques

- I-F. Requêtes SQL personnalisées

- I-G. Requêtes orientées objet

- I-H. Écrire dans la base

- I-I. Pour conclure

- II. À propos de l'auteur

- III. Remerciements

I - En quoi Pomm est-il différent d'un ORM et pourquoi l'utiliser ?

Il est difficile de répondre rapidement à cette question sans tomber dans l'ornière du débat pro / anti ORM. L'auteur développe avec PHP et PostgreSQL depuis plus d'une dizaine d'années. L'avènement des ORM a certes changé la façon d'utiliser les bases de données en apportant des vraies couches modèles au sein du MVC, mais ils ont également apporté un certain nombre d'inconvénients très handicapants pour les habitués des fonctionnalités des bases de données en général et de PostgreSQL en particulier. Pomm part donc du parti pris de ne fonctionner qu'avec PostgreSQL et son objectif est de permettre aux développeurs PHP de tirer parti de ses fonctionnalités au maximum.

Une des limitations des ORM est qu'en calquant une logique orientée objet sur des structures SQL, ils figent ces dernières suivant la définition de classes (PHP ou autres) alors que :

- les bases de données ne manipulent que  des ensembles  de tuples ;

- que les opérations ensemblistes sont insensibles à la taille de ces tuples ;

- que le système de projection (SELECT) a été conçu pour les  façonner.

Un ensemble de base de données est donc par essence tout sauf figé. Nous verrons comment Pomm tire parti de la souplesse de PHP pour créer des objets élastiques s'adaptant à notre besoin. Ceci est d'autant plus appréciable que PostgreSQL sait manipuler des entités comme des objets, nous verrons comment faire des requêtes « orientées objet » en SQL.

Un autre des problèmes des ORM est lié à la couche d'abstraction : ils proposent un langage pseudo SQL orienté objet qui se cantonne souvent au plus petit commun dénominateur des fonctionnalités partagées entre tous les moteurs de bases de données et il est souvent délicat de trouver comment faire quelque chose qu'on sait déjà faire en SQL classique. Nous verrons comment Pomm permet de faire directement des requêtes SQL sans les inconvénients de la construction fastidieuse - que probablement certains d'entre vous ont connu - qui menait à des scripts peu maintenables et peu testables.

Le présent article vous propose de créer une application web qui cherche et affiche des informations sur les employés de la société El-Caro Corporation.

I-A - Mise en place de l'application

L'application suivante n'utilise pas de framework et est volontairement minimaliste. Il est bien sûr fortement conseillé d'en utiliser un, il existe à ce propos un adaptateur pour  Silex et Symfony . Ne vous étonnez donc pas de ne pas trouver de belles URL (routing), de contrôleurs encapsulés (et testables), de moteur de template (fort utile) et autres bonnes pratiques. Cela va forcément s'éloigner de ce à quoi pourrait ressembler une application respectueuse des préceptes RESTFULL, mais cela va nous permettre de nous concentrer sur le sujet de cet article.

Nous allons utiliser  Composer  pour installer Pomm et instancier un autoloading dans notre projet. Pour cela, il n'est pas utile de créer plus qu'un fichier composer.json comme suit dans un répertoire vierge :

1. {

2. "minimum-stability": "dev",

3. "require": {

4. "pomm/pomm": "dev-master"

5. }

6. }

Reste à appeler le script composer.phar install pour que Composer installe Pomm et le prenne en compte dans son autoloader.

I-B - Bienvenue dans la société El-Caro Corp.

Ce tutoriel vous propose de créer une application simpliste de gestion des salariés de la société informatique « El-Caro Corporation ». Cette société est divisée en départements hiérarchisés et chaque employé appartient à un département lui-même pouvant être fils d'un autre service. La structure de la base de données est la suivante :

Nous allons créer un schéma nommé company dans notre base de données pour y créer la structure décrite ci-dessus :

1. $> CREATE SCHEMA company;

2. $> SET search_path TO company, public;

3. $> SHOW search_path;

4. company, public

La commande SHOW doit nous retourner company, public signe que le client va d'abord chercher les objets dans le schéma company puis ensuite dans le schéma par défaut public. Il y a plusieurs avantages à utiliser un schéma, le plus important est de pouvoir intégrer des extensions contenant des tables à notre application sans risquer une collision de nom. Un autre avantage est que si l'on souhaite « passer l'éponge », il suffit de lancer un DELETE SCHEMA company CASCADE et de recommencer. Une fois le schéma créé, implémentons la structure :

1. $> CREATE TABLE department (

2. department_id       serial      PRIMARY KEY,

3. name                varchar     NOT NULL,

4. parent_id           integer     REFERENCES department (department_id)

5. );

Tel que nous l'avons décrite, cette table possède un identifiant technique - un entier - qui s'auto-incrémente à l'aide d'une séquence qui est autogénérée et initialisée à la création de la table comme l'indique PostgreSQL. Notons que le parent_id même s'il est indiqué comme référent au département parent peut être nul dans le cas du département racine. En revanche la contrainte de clé étrangère forcera tout département indiqué comme père à exister au préalable dans la table.

1. $> CREATE TABLE employee (

2. employee_id         serial          PRIMARY KEY,

3. first_name          varchar         NOT NULL,

4. last_name           varchar         NOT NULL,

5. birth_date          date            NOT NULL CHECK (age(birth_date) >= '18 years'::interval),

6. is_manager          boolean         NOT NULL DEFAULT false,

7. day_salary          numeric(7,2)    NOT NULL,

8. department_id       integer         NOT NULL REFERENCES department (department_id)

9. );

Nous voyons ici que la structure d'un employé est fortement contrainte. Une vérification - pour l'exemple - d'âge est faite pour vérifier que la date de naissance entrée ne correspond pas à un mineur. Dans le cas d'un employé, l'appartenance à un département est rendue obligatoire par la contrainte NOT NULL sur le champ de clé étrangère department_id vers la table department.

Un jeu de données est disponible dans  ce Gist .

I-C - Génération du modèle PHP

À partir de cette structure de base de données, Pomm sait construire les classes qui correspondent aux tables pour nous permettre de nous affranchir des traitements fastidieux de PDO. Dans un premier temps, nous créons un fichier appelé bootstrap.php qui sera inclus par nos scripts et dont le but est d'initialiser l'autoloading et la base de données.

1. <?php // bootstrap.php

3. $loader = require __DIR__."/vendor/autoload.php";

4. $loader->add(null, __DIR__."/lib");

6. $database = new Pomm\Connection\Database(array('dsn' => 'pgsql://greg/greg', 'name' => 'el_caro'));

8. return $database->getConnection();

Notez que nous spécifions le répertoire lib comme répertoire par défaut pour trouver les namespaces à l'autoloader.

Pour maintenant générer les fichiers de mapping, créons le fichier generate_model.php dont une version plus générale est disponible dans  ce Gist .

1. <?php //generate_model.php

3. $connection = require(__DIR__."/bootstrap.php");

5. $scan = new Pomm\Tools\ScanSchemaTool(array(

6. 'schema'     => 'company',

7. 'database'   => $connection->getDatabase(),

8. 'prefix_dir' => __DIR__."/lib"

9. ));

10. $scan->execute();

11. $scan->getOutputStack()->setLevel(254);

13. foreach ( $scan->getOutputStack() as $line )

14. {

15. printf("%s\n", $line);

16. }

Ce script utilise un des outils fournis avec Pomm :  le scanner de schéma . Cet outil utilise l'inspecteur de base de données de Pomm pour générer des classes de mapping liées aux structures stockées en base. Dans le cas présent, nous lui demandons de scanner le schéma company et de générer les fichiers dans le sous-répertoire lib, là où nous avons fait pointer l'autoloader par défaut dans le fichier bootstrap.php. Un appel à ce script va nous générer la structure de fichiers suivante:

1. lib/

2. &#9492;&#9472;&#9472; ElCaro

3. &#9492;&#9472;&#9472; Company

4. &#9500;&#9472;&#9472; Base

5. &#9474;   &#9500;&#9472;&#9472; DepartmentMap.php

6. &#9474;   &#9492;&#9472;&#9472; EmployeeMap.php

7. &#9500;&#9472;&#9472; DepartmentMap.php

8. &#9500;&#9472;&#9472; Department.php

9. &#9500;&#9472;&#9472; EmployeeMap.php

10. &#9492;&#9472;&#9472; Employee.php

Cette architecture ne choquera pas les utilisateurs habitués à utiliser des ORM. Nous pouvons constater que le namespace utilisé par les classes de modèle est \ElCaro\Company, c'est-à-dire le nom de la base de données passé en paramètre lors de l'instanciation de la classe Database avec le nom du schéma. Ainsi, il est possible d'avoir plusieurs classes de tables portant le même nom, mais déclarées dans des schémas PostgreSQL différents. D'autre part, chaque table génère trois classes :

- une classe portant le même nom que la table à la casse près ;

- une classe portant le même nom, mais affublé du suffixe Map ;

- la même classe dans le sous namespace Base.

Les classes du sous namespace Base contiennent la définition déduite depuis la structure de la base de données. Ces fichiers seront écrasés à chaque introspection en cas d'évolution de la structure de la base, il serait donc malvenu qu'elles contiennent du code que nous aurions pu placer là. C'est pour cela que la classe Map hérite de sa consœur dans Base. Vous pouvez y placer votre code, cette classe ne sera pas écrasée.

Les utilisateurs d'ORM ne seront pas non plus surpris d'apprendre que la classe Map est l'outil qui s'occupera de gérer la vie de leur entité correspondante avec la base de données, à savoir :

- DepartmentMap sauvegarde, génère et renvoie des collections d'entités Department ;

- EmployeeMap renvoie des collections d'entités Employee.

I-D - Premiers pas

Pour notre première interface, nous allons afficher la liste des employés. Créons le fichier index.php avec le code PHP suivant :

1. <?php //index.php

3. // CONTROLLER

4. $connection = require(__DIR__."/bootstrap.php");

6. $employees = $connection

7. ->getMapFor('\ElCaro\Company\Employee')

8. ->findAll();

10. // TEMPLATE

11. ?>

12. <html>

13. <head>

14. <meta http-equiv="Content-Type" content="text/html; charset=utf-8">

15. </head>

16. <body>

17. <h1>El-Caro - Workers list</h1>

18. <?php if ($employees): ?>

19. <ul>

20. <?php foreach($employees as $employee): ?>

21. <li><a href="/show_employee.php?employee_id=<?php echo $employee["employee_id"] ?>"><?php echo $employee["first_name"]." ".$employee["last_name"] ?></a></li>

22. <?php endforeach ?>

23. </ul>

24. <?php else: ?>

25. <p>No employees !?!? There must be a bug somewhere...</p>

26. <?php endif ?>

27. </body>

28. </html>

Commentons le code ci-dessus :

1. La connexion nous permet d'obtenir des instances de classes Map ;

2. La classe Map sait faire des requêtes qui ramènent des collections de leur entité correspondante ;

3. Ces collections sont accessibles via foreach et retournent leurs entités ;

4. Les valeurs internes des entités sont accessibles entre autres via la notation de tableau.

L'utilisation de la notation de tableau est pratique dans les templates et elle équivaut complètement à l'utilisation d'accesseurs. Ainsi $employee['first_name'] est équivalent à $employee->getFirstName(). Cela permet par exemple, si on souhaite formater le prénom capitalisé et le nom en majuscule, de juste avoir à surcharger getFirstName() et getLastName() dans la classe Employee :

1. <?php // lib/ElCaro/Company/Employee.php

3. namespace ElCaro\Company;

5. use \Pomm\Object\BaseObject;

6. use \Pomm\Exception\Exception;

8. class Employee extends BaseObject

9. {

10. public function getFirstName()

11. {

12. return ucwords($this->get('first_name'));

13. }

15. public function getLastName()

16. {

17. return strtoupper($this->get('last_name'));

18. }

20. public function __toString()

21. {

22. return sprintf("%s %s", $this['first_name'], $this['last_name']);

23. }

24. }

Seuls les accesseurs génériques get(), set(), has() et clear() ne peuvent être surchargés, car ils sont utilisés pour accéder aux valeurs brutes de l'objet. Nous en aurions besoin ici si nous souhaitions par exemple implémenter une recherche par le prénom alors que la méthode getFirstName() ne nous retourne pas la valeur effectivement stockée en base.

Dans la vraie vie™, un tel exemple ne serait pas vraiment exploitable à cause du volume de données dès que le nombre d'employés dépasse quelques dizaines. Cela ne nous aurait pas coûté tellement plus cher de  les classer par ordre alphabétique  et de  paginer notre liste  de résultats dans le contrôleur.

I-E - Entités élastiques

Intéressons-nous maintenant à l'affichage des données d'un utilisateur :

1. <?php // show_employee.php

3. $connection = require(__DIR__."/bootstrap.php");

5. // CONTROLLER

7. if (!$employee = $connection

8. ->getMapFor('\ElCaro\Company\Employee')

9. ->findByPk(array('employee_id' => $_GET['employee_id'])))

10. {

11. printf("No such user.");

12. exit;

13. }

14. // TEMPLATE

15. ?>

16. <html>

17. <head>

18. <meta http-equiv="Content-Type" content="text/html; charset=utf-8">

19. </head>

20. <body>

21. <h1>El-Caro - <?php echo $employee ?> (<?php echo $employee["employee_id"] ?>)</h1>

22. <p><a href="/index.php">Back to the homepage</a>.</p>

23. <ul>

24. <li>Birth date: <?php echo $employee["birth_date"]->format("d/m/Y") ?>.</li>

25. <li>day salary indice: <?php printf("%05.2f", $employee["day_salary"]) ?>.</li>

26. <li>Status: <?php echo $employee["is_manager"] ? "manager" : "worker" ?>.</li>

27. <li>Department: <?php echo $employee["department_id"] ?>.</li>

28. </ul>

29. </body>

30. </html>

Là encore, nous pouvons voir que le convertisseur a fait son travail, la date de naissance est un objet PHP DateTime, le champ is_manager est un booléen et on peut formater day_salary convenablement.

Imaginons maintenant qu'en plus de la date de naissance, nous avions besoin d'avoir directement l'âge de la personne. Il est bien sûr facile de créer un accesseur supplémentaire getAge() dans la classe Employee dans laquelle nous calculerions en PHP l'âge à partir de la date de naissance, mais pourquoi ne pas le demander directement à PostgreSQL en utilisant la fonction age() ?

Il faut savoir que Pomm n'utilise jamais l'alias * dans ses requêtes, il utilise pour cela la méthode getSelectFields() définie dans les classes Map. Par défaut, cette méthode retourne tous les champs de la table, mais il est possible de la surcharger pour en enlever ou en ajouter. En d'autres termes,  cette méthode définit la projection de l'objet en base de données vers l'objet entité PHP .

1. <?php // lib/ElCaro/Company/EmployeeMap.php

3. namespace ElCaro\Company;

5. use ElCaro\Company\Base\EmployeeMap as BaseEmployeeMap;

6. use ElCaro\Company\Employee;

7. use \Pomm\Exception\Exception;

8. use \Pomm\Query\Where;

10. class EmployeeMap extends BaseEmployeeMap

11. {

12. public function getSelectFields($alias = null)

13. {

14. $fields = parent::getSelectFields($alias);

15. $fields['age'] = 'age(birth_date)';

17. return $fields;

18. }

19. }

Et ajoutons la ligne suivante dans la partie template de show_employee.php :

1. <li>Age: <?php echo $employee['age'] ?>.</li>

En rafraîchissant la page, celle-ci affiche désormais quelque chose ressemblant à Age: 27 years 11 mons 2 days.. C'est la sortie brute de la commande age() de PostgreSQL, Pomm ne sachant comment interpréter cette sortie, la convertit au format String. Il est possible d'étendre la définition de notre entité en y ajoutant le type de cette nouvelle colonne virtuelle afin qu'elle soit prise en charge par le convertisseur quand elle existe :

1. <?php // lib/ElCaro/Company/EmployeeMap.php

2. // [...]

4. public function initialize()

5. {

6. parent::initialize();

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

8. }

Si vous rafraîchissez désormais la page, celle-ci présente une erreur comme quoi PHP ne sait pas comment afficher une instance de la classe PHP DateInterval : le convertisseur a bien fait son travail. Changez l'affichage de l'âge par la ligne suivante :

1. <li>Age: <?php echo $employee['age']->format("%y") ?> years old.</li>

Avant de conclure ce chapitre, notons que la méthode getSelectFields() que nous avons surchargée est génératrice de problèmes, car le nouveau champ age que nous avons ajouté est insensible à l'alias. Cela peut - et va - poser des problèmes lors de requêtes complexes où ce champ peut apparaître dans plusieurs ensembles. La laisser ainsi occasionnerait des erreurs SQL de type « champ ambigu » assez délicates à déboguer. Pour prévenir cela, corrigeons la méthode comme suit :

1. public function getSelectFields($alias = null)

2. {

3. $fields = parent::getSelectFields($alias);

4. $alias = is_null($alias) ? "" : sprintf("%s.", $alias);

5. $fields['age'] = sprintf('age(%s"birth_date")', $alias);

7. return $fields;

8. }

I-F - Requêtes SQL personnalisées

Si désormais, nous souhaitons afficher le nom du service au lieu du department_id, appeler findByPk() est insuffisant, nous allons devoir créer une jointure pour ramener cette information. Créons une méthode dans notre classe de modèle dont le but sera de ramener un employé avec des informations sur son service. On peut coucher immédiatement la requête nécessaire (le NATURAL JOIN de Postgres permet de faire une jointure sur deux ensembles en prenant les champs homonymes) :

1. SELECT *, dept.name FROM employee NATURAL JOIN department dept WHERE employee_id = ?

Cependant, la requête sous cette forme présente des inconvénients :

- elle n'utilise pas la méthode de projection getSelectFields() et n'affichera pas le champ age ;

- indiquer le nom des tables « en dur » peut nous poser des problèmes d'évolutivité.

Les classes Map de Pomm proposent pour cela des méthodes pour avoir ces informations de façon dynamique. Idéalement, notre requête pourrait être vue sous cette forme :

1. SELECT %A, dept.name FROM %B NATURAL JOIN %C WHERE employee_id = ?

- %A est la liste des colonnes que l'on souhaite ramener de la table B ;

- %B est la table des employés ;

- %C est la table des départements.

B et C sont facilement remplacés grâce à la méthode getTableName() de chaque classe Map. Nous savons que l'on peut obtenir la liste des colonnes à ramener avec la méthode getSelectFields(), mais cette méthode retourne un tableau associatif dont la clé est l'alias du champ et la valeur hé bien… sa valeur. Il faut donc formater ce tableau en une liste de champs. Les classes Map proposent pour cela des méthodes dédiées,les formateurs :

- formatFields(methode, alias) ;

- formatFieldsWithAlias(methode, alias).

1. $this->formatFields('getSelectFields', 'pika');

2. // "pika.employee_id", "pika.first_name", "pika.last_name", "pika. ....

3. $this->formatFieldsWithAlias('getSelectFields', 'plop');

4. // "plop.employee_id" AS "employee_id", "plop.first_name" AS "first_name", ...

Ainsi formé, il est facile de se concentrer sur ce que font les requêtes plutôt que sur la syntaxe elle-même :

1. <?php // lib/ElCaro/Company/EmployeeMap.php

2. // [...]

4. public function initialize()

5. {

6. // [...]

7. $this->addVirtualField('department_name', 'varchar');

8. }

10. public function getEmployeeWithDepartment($employee_id)

11. {

12. $department_map = $this->connection->getMapFor('\ElCaro\Company\Department');

13. $sql = <<<SQL

14. SELECT

15. :employee_fields_emp, dept.name AS department_name

16. FROM

17. :employee_table emp

18. NATURAL JOIN :department_table dept

19. WHERE

20. emp.employee_id = ?

21. SQL;

23. $sql = strtr($sql, array(

24. ':employee_fields_emp' => $this->formatFieldsWithAlias('getSelectFields', 'emp'),

25. ':employee_table' => $this->getTableName(),

26. ':department_table' => $department_map->getTableName()

27. ));

29. return $this->query($sql, array($employee_id))->current();

30. }

Remplaçons dans notre contrôleur l'appel à findByPk par cette méthode :

1. if (!$employee = $connection

2. ->getMapFor('\ElCaro\Company\Employee')

3. ->getEmployeeWithDepartment($_GET['employee_id']))

4. {

5. printf("No such user.");

6. exit;

7. }

Et dans le template correspondant :

1. <li>Department: <?php echo $employee["department_name"] ?>.</li>

La possibilité de faire des requêtes SQL depuis les classes Map est une fonctionnalité extrêmement puissante, car elle permet d'utiliser tous les mécanismes SQL de PostgreSQL. Par exemple, les départements sont une structure arborescente, nous pouvons demander à PostgreSQL de ramener sous forme de tableaux l'ensemble des services auxquels chaque utilisateur appartient. Pour cela, nous utilisons une requête récursive avec un agrégateur de tableaux et déclarons notre colonne comme un tableau de chaînes de caractères :

1. <?php // lib/ElCaro/Company/EmployeeMap.php

2. // [...]

4. public function initialize()

5. {

6. parent::initialize();

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

8. $this->addVirtualField('department_names', 'varchar[]');

9. }

11. public function getEmployeeWithDepartment($employee_id)

12. {

13. $department_map = $this->connection->getMapFor('\ElCaro\Company\Department');

14. $sql = <<<SQL

15. WITH RECURSIVE

16. depts  (department_id, name, parent_id) AS (

17. SELECT :department_fields_alias_d FROM :department_table d NATURAL JOIN :employee_table emp WHERE emp.employee_id = ?

18. UNION ALL

19. SELECT :department_fields_alias_d FROM depts parent JOIN :department_table d ON parent.parent_id = d.department_id

20. )

21. SELECT

22. :employee_fields_alias_emp, array_agg(depts.name) AS department_names

23. FROM

24. :employee_table emp,

25. depts

26. WHERE

27. emp.employee_id = ?

28. GROUP BY

29. :employee_group_by_emp

30. SQL;

32. $sql = strtr($sql, array(

33. ':department_fields_alias_d' => $department_map->formatFieldsWithAlias('getSelectFields', 'd'),

34. ':department_table'          => $department_map->getTableName(),

35. ':employee_fields_alias_emp' => $this->formatFieldsWithAlias('getSelectFields', 'emp'),

36. ':employee_table'            => $this->getTableName(),

37. ':employee_group_by_emp'     => $this->formatFields('getGroupByFields', 'emp'),

38. ));

40. return $this->query($sql, array($employee_id, $employee_id))->current();

41. }

Et dans le template :

1. <li>Departments: <?php echo join(' &gt; ', $employee["department_names"]) ?>.</li>

La requête ci-dessus, utilise la clause SQL WITH qui permet de créer des ensembles nommés et de les rappeler. Cela évite de faire des sub-select. Le premier ensemble aliasé depts est la clause récursive. Elle possède un ensemble de départ - le département direct de l'employé - uni à une requête récursive qui remonte l'arbre jusqu'à ce que ça ne soit plus possible. L'ensemble depts va donc contenir tous les départements de l'employé. La requête finale va tout simplement faire un CROSS JOIN entre les informations de l'employé et l'agrégat en tableaux du nom de ses départements.

Une remarque concernant la construction de requêtes, il a été évoqué dans l'introduction de cet article, alors qu'ici la condition est connue d'avance : emp.employee_id = ?. Dans les interfaces de recherche, il se peut qu'on ne puisse savoir à l'avance sur quels critères la recherche va porter. Pomm propose pour cela une  classe de construction de clauses where  qui respecte les priorités ET et OU et qui peut être passée directement en paramètre à la méthode findWhere().

I-G - Requêtes orientées objet

Afficher l'arbre des départements de chaque utilisateur est une bonne chose, mais cela serait plus intéressant si on pouvait avoir un lien sur chaque département qui mènerait à la fiche du département contenant tous ses employés. Dans l'état actuel de la requête, on ne ramène que les noms des départements, il nous faudrait également leurs identifiants. On peut toujours ajouter une colonne contenant un tableau d'identifiant, mais cela n'est pas très pratique.

PostgreSQL propose une fonctionnalité intéressante : lorsque vous déclarez une table, PostgreSQL va automatiquement créer le type composite correspondant. C'est-à-dire que le type company.department existe et que vous pouvez faire des requêtes de ce type directement :

1. elcaro$> SELECT department FROM department;

2. &#9484;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9488;

3. &#9474;         department          &#9474;

4. &#9500;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9508;

5. &#9474; (1,"el caro corp.",)        &#9474;

6. &#9474; (2,siège,1)                 &#9474;

7. &#9474; (3,direction,2)             &#9474;

8. &#9474; (4,comptabilité,1)          &#9474;

9. &#9474; (5,"direction technique",3) &#9474;

10. &#9474; (6,"hotline niveau II",5)   &#9474;

11. &#9474; (7,"datacenter skynet",1)   &#9474;

12. &#9474; (8,"technique & réseau",7)  &#9474;

13. &#9474; (9,"Hotline niveau I",7)    &#9474;

14. &#9474; (10,Direction,7)            &#9474;

15. &#9492;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9472;&#9496;

16. (10 rows)

Le résultat de la requête ci-dessus ne possède  qu'une seule colonne  de type department. Ce que l'on appelle pompeusement requête orientée objet  en PostgreSQL n'est que le fait de manipuler des tuples comme des valeurs, mais cela est déjà extrêmement puissant, car si on modifie la ligne suivante dans la requête SQL vue plus haut :

1. ...

2. SELECT

3. %s, array_agg(depts) AS departments

4. FROM

5. %s emp,

6. depts

7. ...

Dès lors, la colonne departments contiendra un tableau d'entités department. Il convient de changer la ligne correspondante dans le template pour afficher les liens :

1. <li>Departments: <?php echo join(' &gt; ', array_map(function($dept) {

2. return sprintf(

3. '<a href="/show_department.php?department_id=%d">%s</a>',

4. $dept["department_id"],

5. $dept["name"]);

6. }, $employee["departments"])) ?>.</li>

Mais cela ne suffit pas. PHP va se plaindre que l'argument passé à array_map n'est pas un tableau et il aura raison. N'ayant pas déclaré la colonne departments de la classe Employee, Pomm va juste y placer la chaîne de caractères renvoyée par PostgreSQL. Il faut donc renseigner le convertisseur que la colonne departments contient un tableau de type department. Seul souci : Pomm ne charge pas par défaut de convertisseur pour ce type de données. Nous allons donc déclarer ce convertisseur auprès de l'instance de base de données dans le fichier bootstrap.php :

1. <?php // bootstrap.php

3. $loader = require __DIR__."/vendor/autoload.php";

4. $loader->add(null, __DIR__."/lib");

6. $database = new Pomm\Connection\Database(array('dsn' => 'pgsql://greg/greg', 'name' => 'el_caro'));

7. $database->registerConverter(

8. 'Department',

9. new \Pomm\Converter\PgEntity($database->getConnection()->getMapFor('\ElCaro\Company\Department')),

10. array('company.department')

11. );

13. return $database->getConnection();

Nous déclarons un nouveau convertisseur appelé Department qui associe le(s) type(s) Postgres company.department à l'instance de convertisseur PgEntity. Reste à utiliser ce convertisseur dans la colonne virtuelle departments de chaque Employee :

1. <?php // lib/ElCaro/Company/EmployeeMap.php

2. // [...]

4. class EmployeeMap extends BaseEmployeeMap

5. {

6. public function initialize()

7. {

8. parent::initialize();

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

10. $this->addVirtualField('departments', 'company.department[]');

11. }

Le fichier show_department.php est laissé à titre d'exercice :)

I-H - Écrire dans la base

Imaginons maintenant que l'interface show_employee.php permette de changer le status 'manager / worker' d'un employé en cliquant dessus. Modifions le template pour créer le lien :

1. <li>Status: <a href="employee_change_status.php?status=<?php echo $employee['is_manager'] ? 1 : 0 ?>&employee_id=<?php echo $employee["employee_id"] ?>"><?php echo $employee["is_manager"] ? "manager" : "worker" ?></a>.</li>

Les utilisateurs d'ORM auront probablement le réflexe d'écrire le contrôleur de sauvegarde de la façon suivante :

1. Je récupère l'employé par son id ;

2. S'il n'existe pas, je renvoie une notification d'erreur ;

3. Sinon je le mets à jour ;

4. Je renvoie une réponse.

PostgreSQL permet de faire tout cela presque en un seul temps. Regardons la structure d'un UPDATE :

1. UPDATE :table SET :champs1 = :valeur1, [:champsN = :valeurN] WHERE :clause_where RETURNING :list_champs

C'est-à-dire qu'en faisant cet update, on peut mettre à jour un enregistrement sur un id précis – s'il existe – et retourner de quoi hydrater une entité avec les valeurs  mises à jour  de la base de données, c'est ce que fait la méthode updateByPk() :

1. <?php // employee_change_status.php

3. $connection = require(__DIR__."/bootstrap.php");

5. // CONTROLLER

6. if (!$employee = $connection->getMapFor('\ElCaro\Company\Employee')

7. ->updateByPk(array('employee_id' => $_GET['employee_id']), array("is_manager" => $_GET["status"] == 0)))

8. {

9. printf("No such employee !"); exit;

10. }

12. header(sprintf("Location: show_employee.php?employee_id=%d", $employee["employee_id"]));

Cette méthode ne fait que mettre à jour un enregistrement sur un nombre défini d'attributs. Il est de la même façon possible de sauvegarder et / ou créer des entités en base avec la méthode saveOne() sans vous soucier s'il s'agit d'une mise à jour ou une insertion. Toutes ces méthodes retournent - grâce à l'emploi en interne de RETURNING :select_fields - une entité mise à jour avec les valeurs de la base de données. Plus d'information est disponible dans la  documentation .

I-I - Pour conclure

Au cours de cet article, nous n'avons fait qu'égratigner la surface des possibilités offertes par PostgreSQL. Nous pourrions continuer et mettre à contribution le fameux type clé -> valeur HStore, faire des tags hiérarchiques en utilisant des chemins matérialisés LTree, créer un historique des changements d'un employé en créant une table dont une des colonnes serait de type employee … la liste des exemples est encore longue.

Pomm est un outil dont l'objectif est de permettre aux développeurs de profiter pleinement des fonctionnalités de PostgreSQL. Effectivement, la barrière peut parfois sembler mince entre Pomm et un ORM. À la différence d'un ORM, Pomm est un outil spécialisé qui va permettre de gagner en vitesse et en performances. En s'appuyant sur des fonctionnalités uniques du moteur sous-jacent, Pomm ouvre des perspectives intéressantes qu'il était difficile d'envisager directement avec juste PDO ou avec un ORM.

Vous pouvez retrouver l'entrepôt Git de cet article  sur Github .

II - À propos de l'auteur

Grégoire HUBERT mêle depuis plus de 10 ans des activités professionnelles d'hébergement et de développement toujours dans la librosphère. Il commence le développement avec PHP et PostgreSQL au XXe siècle puis après avoir embrassé Symfony dès 2006 en travaillant chez Sensio et pris la direction technique de KnpLabs en 2011, il continue le développement web en tant que freelance aujourd'hui. Auteur de Pomm, fan de GNU/Linux, PostgreSQL, Vim et tout ce qui apporte en général plus de solutions que de problèmes, vous pouvez le retrouver sur [twitter]( https://twitter.com/chanmix51 ) ou sur gregoire (point) hubert (point) pro (at) gmail (point) com.

III - Remerciements

Un grand merci à  Julien Bianchi  qui a été jusqu'à packager une VM Vagrant / VirtualBox pour ce tutoriel. Vous pouvez retrouver de quoi la construire  dans son répository . Merci également à  Nicolas Joseph  dont l'aide très efficace a beaucoup apporté à cet article.

Merci également à f-leb pour le travail de relecture et de correction orthographique.