Des astuces Doctrine/DQL

De e-glop
Révision datée du 17 janvier 2011 à 12:23 par BeTa (discussion | contributions) (Solution pratique et fonctionnelle)

Lorsque vous travaillez avec l'ORM Doctrine, il est parfois difficile de retrouver toute la souplesse de l'écriture en Raw SQL. C'est un peu normal : abstraction oblige.

Travailler avec objets serialized avec une base de données PostgreSQL de manière transparente

Problématique

Dans une webapp développée sur le framework Symfony, il est question d'envoyer des courriels via la factory SwiftMail. La stratégie choisie est de passer par un spooler afin de différer l'envoi des courriels sans surcharger l'interface utilisateur.

SwiftMail embarque une gestion de spooler assez efficace : Elle génère les objets Swift_Message qui, au lieu d'être envoyés directement, sont stockés en base "as-is" via la function PHP serialize(). Seulement, si vous faîtes l'essai, vous ne retrouverez que les premiers octets de votre objet en base, et ce quel que soit le type de champ que vous aurez choisi (blob/bytea, string/text, etc...).

Après de bonnes investigations, j'ai trouvé que c'était les caractères \0 qui posaient problème. Doctrine ne les échappe pas "naturellement" ou correctement et PostresSQL y est intolérant.

Solutions envisagées

Plusieurs solutions viennent alors à l'esprit :

Spécifier clairement le type bytea dans le schema.yml au lieu du type Doctrine bytea

L'interfaçage de PHP avec les "lo" (large objects) rend les choses complexes, puisque c'est une resource qui nous ait retournée. Déjà cela change profondément et radicalement le comportement originel de Doctrine. Ensuite, même si vous allez jusqu'à lire dans cette resource (de la nature de celle qui est retournée par fopen()), le problème des \0 sera toujours présent.

Travailler directement dans son Doctrine_Record avec pg_escape_bytea()

Cette solution permet d'enregistrer apparemment correctement les objets en base, puisque cette fois le champ incriminé contient a priori bien toutes les données que nous attendions. Cependant il est impossible de faire correctement appel à :

$object = unserialize($field);

Même l'utilisation de pg_unescape_bytea() n'améliore pas la situation car cela plante tout autant :

$object = unserialize(pg_unescape_bytea($field));

Solution pratique et fonctionnelle

Le cas pratique et fonctionnel qui va suivre est issu du schema.yml suivant :

schema.yml

EmailSpool:
  package: email
  actAs: [Timestampable]
  columns:
    message:
      type: text
      notnull: true
    priority: integer

à noter le "package" email qui amène un fonctionnement de Doctrine particulier.

email/EmailSpool.class.php et email/EmailSpoolTable.class.php

// EmailSpool.class.php
class EmailSpool extends PluginEmailSpool
{
  public function setMessage($message)
  {
    $msg = unserialize($message);
    $this->priority = $msg->getPriority();
    
    return parent::setMessage($message);
  }
}
// EmailSpoolTable.class.php
class EmailSpoolTable extends PluginEmailSpoolTable
{
  // ...
  public function getSpooledMessages()
  {
    return $this->createQuery('m')
      ->orderBy('m.priority')
    ;
  }
}

Jusque là que du très normal.

packages/email/PluginEmailSpool.class.php

L'idée est donc de gérer "plus haut" la question de l'échappement des informations à insérer en base. Puisque c'est \0 qui pose problème, nous allons donc remplacer cet octet par une autre chaîne qui ne posera pas problème (ex: ~~NULL_BYTE~~), et prévoir son contre-échappement.

Ici nous travaillerons donc sur le champ PluginEmailSpool::message en surchargeant les méthodes setMessage() et getMessage(). Pour garder à l'esprit la souplesse de l'ORM Doctrine, cette surcharge ne doit être efficiente que dans le cas où le driver utilisé est celui de PostgreSQL. Nous ajouterons donc les méthodes getDBDriver() et isPgsql() :

abstract class PluginEmailSpool extends BaseEmailSpool
{
  protected $db_driver = ;
  const null_byte = "~~NULL_BYTE~~";
  
  protected function getDBDriver()
  {
    if ( !$this->db_driver )
      $this->db_driver = Doctrine_Manager::connection()->getDriverName();
    return $this->db_driver;
  }
  protected function isPgsql()
  {
    return 'Pgsql' === $this->getDBDriver();
  }
  
  public function setMessage($message)
  {
    if ( $this->isPgsql() )
      $message = $this->pg_escape_serialized($message);
   
    return $this->_set('message',$message);
  }

  public function getMessage()
  {
    return $message = $this->isPgsql()
        ? $this->pg_unescape_serialized($this->_get('message'))
        : $this->_get('message');
  }
 
  static protected function pg_escape_serialized($str)
  {
    return str_replace("\0", self::null_byte, $str);
  }
  static protected function pg_unescape_serialized($str)
  {
    return str_replace(self::null_byte, "\0", $str);
  }
}

Faire des recherches sans tenir compte des accents

TO COME

Faire des recherches multi-critères sans tenir compte des accents

TO COME

Ajouter des parenthèses arbitraires dans une Doctrine_Query

En particulier pour l'exception des conditions ->andWhereIn() / ->whereIn()

Le cas des parenthèse via Doctrine en DQL mériterait une petite amélioration, peut-être Doctrine v2 ira plus loin dans ce sens. Ici nous allons étudier comme pouvoir rajouter des parenthèses pour un des rares cas où il n'est pas possible de passer par une écriture "simple" en SQL.

Si vous souhaitez donner des priorités particulières à certaines conditions en DQL, voici une solution :

Présentation du problème

Erreur "simple"

Dans cet exemple nous souhaitons obtenir :

 Tous les contacts dont le "postalcode" est 1000
 ET le "name" est "Foo" OU le "firstname" est "Bar"

Nous allons donc essayer avec la requête suivante :

 $q = Doctrine::getTable('Contact')->createQuery();
 $q->andWhere('name = ?','Foo')
   ->orWhere('firstname = ?','Bar')
   ->andWhere('postalcode = ?',1000);
 echo $q->getSqlQuery();
 
 // prints something like : SELECT * FROM contact WHERE (name = 'Foo' OR firstname = 'Bar' AND postalcode = 1000)

Pour autant, vous l'aurez compris, ce n'est pas ce que l'on souhaite. Ici en langage courant, on va obtenir :

 Tous les contacts dont le "name" est "Foo"
 OU ALORS
 Tous les contacts dont le "firstname" est "Bar" ET dont le "postalcode" est 1000

Solution "simple"

Pour corriger cela, nous allons construire la requête différemment, un peu plus "bas niveau" :

 $q = Doctrine::getTable('Contact')->createQuery();
 $q->andWhere('name = ? OR firstname = ?',array('Foo','Bar'))
   ->andWhere('postalcode = ?',1000);
 echo $q->getSqlQuery();
 
 // prints something like : SELECT * FROM contact WHERE ((name = 'Foo' OR firstname = 'Bar') AND postalcode = 1000)

Nous obtenons alors bien le résultat escompté.

Erreur "complexe" avec whereIn()

Les choses deviennent plus complexes lorsque vous utiliserez des conditions ->andWhereIn(). reprenons alors un autre problème pour illustrer cela :

 Tous les contacts dont le "postalcode" est 1000
 ET le "name" est dans la liste XX OU le "firstname" est dans la liste YY

Voilà comment on aurait envie de s'y prendre en DQL :

 $XX = array(...);
 $YY = array(...);
 
 $q = Doctrine::getTable('Contact')->createQuery();
 $q->andWhereIn('name',$XX)
   ->orWhere('firstname',$YY)
   ->andWhere('postalcode = ?',1000);
 echo $q->getSqlQuery();
 
 // prints something like : SELECT * FROM contact WHERE (name IN (X..X) OR firstname IN (Y..Y) AND postalcode = 1000)

Cela nous donne le même type de problème que rencontré précédemment.

Les diverses solutions

Plusieurs solutions existent :

  • patcher directement Doctrine_Query pour ajouter une fonctionnalité "parenthèses"
    • Avantage : ne nécessite aucune évolution sur votre fond de code
    • Inconvénient : vous "forkez" de fait avec le code officiel de Doctrine, maintenance très très complexe (déconseillé)
  • Étendre la classe Doctrine_Query pour y ajouter la fonctionnalité "parenthèse" recherchée
    • Avantage : propre et net... utilise bien toute la puissance d'un langage objet
    • Inconvénient : demande à revoir tout votre fond de code pour utiliser votre classe personnalisée... dans le cas de Symfony par exemple, cela peut vite devenir très très complexe, voire même de devoir forker en retouchant le code officiel de Symfony

La solution retenue

Cette solution n'est pas très très belle, mais elle a le mérite d'être simple. La voici :

rajouter une condition ->andWhere('(TRUE') au début de votre bloc de conditions et finir par ->andWhere('TRUE)') à la fin.

 $q = Doctrine::getTable('Contact')->createQuery();
 $q->andWhere('(TRUE');
   ->andWhereIn('name',$XX)
   ->orWhere('firstname',$YY)
   ->andWhere('TRUE)')
   ->andWhere('postalcode = ?',1000);
 echo $q->getSqlQuery();
 
 // prints something like : SELECT * FROM contact WHERE ((TRUE AND name IN (X..X) OR firstname IN (Y..Y) AND TRUE) AND postalcode = 1000)

Cela nous donne bien le resultat escompté.