contraintes PostgreSQL dans symfony

Pour les habitués de postgresql et de de ses fonctionnalités, l'utilisation de propel et de sa syntaxe limitée dans le schema.yml est une véritable écorchure mentale. Point de contraintes, point de triggers, pas d'index de type hors de celui par défaut bref...

Imaginons que nous voulons proposer à nos utilisateurs de s'inscrire à des stages. Il nous faut stocker les informations relatives à ces stages. Imaginons le schema.yml suivant :

  stage:
    reference:    { type: varchar(255), primaryKey: true }
    title:              { type: varchar(255), required: true }
    description:        { type: longvarchar, required: true }
    date:               { type: timestamp, required: true }
    limit_date:         { type: timestamp, required: true }
    limit_subscription: { type: integer, required: true, default: 0 }
    price:                { type: numeric, size: 6, scale: 2, required: true, default: 0 }
    duration:           { type: integer, required: true }

  stagiaire:
    id:
    stage_id:           { type: integer, required: true, foreignTable: stage, foreignReference: id, onDelete: cascade }
    firstname:          { type: varchar(255), required: true }
    lastname:           { type: varchar(255), required: true }
    email:              { type: varchar(255), required: true }
    phone:              { type: varchar(255), required: true }
    confirmed:          { type: boolean, required: true, default: false }
    created_at:

Notre stage possède une référence qui est la clé primaire des enregistrements. Cette clé primaire est automatiquement générée par une séquence qui se base sur des enregistrements de tables tières.

Dans l'équipe de développement, la personne responsable de la base de données va immédiatement pouvoir protéger la base contre les «étourderies» des développeurs à l'aide de l'astuce fort  bien décrite ici.

Pourquoi utiliser des contraintes fortes dans la base de données ?
- les données du système d'information sont le bien le plus précieux d'une entreprise. La cohérence des données n'est pas une option.
- il convient de nous assurer que les mécanismes de validation mis en place par les développeurs fonctionnent et que les données échangées sont en adéquations avec les données attendues en base.


Les contraintes Postgresql

Gardons à l'oeil la documentation des contraintes et commençons notre travail en créant  un fichier constraintes.sql dans le répertoire data/sql :

La première chose est de fixer la valeur de la clé primaire de la table stages qui utilise la séquence stage_seq :

ALTER TABLE stage ALTER reference SET DEFAULT nextval('stage_seq');

Ensuite, nous avons les contraintes suivantes :
- Le prix doit être positif
- La date limite d'inscription ne doit pas être après la date du stage

Pour le prix la contrainte est relativement simple. Nous allons la nommer positive_price. L'avantage de nommer les contraintes est de pouvoir les modifier après comme des objets nommés d'une part et d'autre part, dans le cas du refus d'une insertion pour violation de contrainte, postgresql nous donne le nom de la contrainte incriminée.

ALTER TABLE stage ADD CONSTRAINT positive_price CHECK (price > 0);

De la même façon, il est possible de placer des contraintes qui vérifient plusieurs colonnes entre elles, ce que l'on va utiliser pour notre histoire de dates :

ALTER TABLE stage ADD CONSTRAINT consistant_dates CHECK (limit_date < date );

Passons maintenant à la table stagiaire :
Nous voulons que le champs email soit bien un email. Nous allons utiliser le mécanisme d'expression régulières de postgresql pour décrire notre contrainte :

ALTER TABLE stage ADD CONSTRAINT valid_email CHECK (email ~*  '^[a-z][a-z0-9_.-]*@[a-z][a-z0-9_.-]*$';

Notez que nous pouvons également utiliser ce fichier pour définir des indexes supplémentaires.

Afin que Propel lise notre fichier après l'import de la structure en base, éditons le fichier data/sql/sqldb.map pour rajouter notre fichier de contraintes :

# Sqlfile -> Database map
lib.model.schema.sql=propel
constraints.sql=propel

Ainsi à chaque propel-build-all, symfony va automatiquement intégrer notre fichier de contraintes.