L'objectif de cet article est de montrer, à l'aide de la bibliothèque schema-keeper , les outils qui permettent d'alléger considérablement le processus de développement de bases de données dans le cadre de projets PHP utilisant la base de données PostgreSQL.

Les informations dans cet article seront particulièrement utiles aux développeurs qui souhaitent tirer pleinement parti des fonctionnalités de PostgreSQL, mais qui rencontrent des problèmes de maintenance de la logique métier intégrée dans la base de données.

Cet article ne décrira pas les avantages ou les inconvénients de stocker la logique métier dans la base de données. Il est supposé que le choix a déjà été fait par le lecteur.

Les questions suivantes seront abordées :

Comment stocker le dump de la structure de la base de données dans le système de contrôle de version (ci-après VCS)
Comment suivre les modifications de la structure de la base de données après avoir enregistré le dump
Comment transférer les modifications de la structure de la base de données vers d'autres environnements sans conflits et avec des fichiers de migration gérables
Comment établir un processus de travail parallèle sur le projet pour plusieurs développeurs
Comment déployer en toute sécurité un plus grand nombre de modifications de la structure de la base de données dans l'environnement de production
SchemaKeeper est conçu pour travailler avec des procédures stockées, écrites en PL/pgSQL. Les tests avec d'autres langages n'ont pas été effectués, donc l'utilisation peut ne pas être aussi efficace ou possible.

À quel format stocker le dump de la structure de la base de données dans le VCS

Bibliothèque schema-keeper fournit la fonction saveDump, qui sauvegarde la structure de tous les objets de la base de données sous forme de fichiers texte séparés. Cela crée un répertoire contenant la structure de la base de données, divisée en fichiers groupés, faciles à ajouter au VCS.

Considérons la conversion d'objets de la base de données en fichiers à travers plusieurs exemples :

Type d'objet
Configuration
Nom
Chemin relatif vers le fichier

Table
public
accounts
./public/tables/accounts.txt

Procédure stockée
public
auth(hash bigint)
./public/functions/auth(int8).sql

Représentation
booking
tariffs
./booking/views/tariffs.txt

Le contenu des fichiers est une représentation textuelle de la structure d'un objet spécifique de la base de données. Par exemple, pour les procédures stockées, le contenu du fichier contiendra la définition complète de la procédure stockée, commençant par le bloc CREATE OR REPLACE FUNCTION.

Comme on peut le voir dans le tableau ci-dessus, le chemin vers le fichier contient des informations sur le type, le schéma et le nom de l'objet. Cette approche facilite la navigation dans le dump et la révision du code des modifications dans la base de données.

Extension .sql Pour les fichiers de code source des procédures stockées, il est choisi de sorte que l'IDE fournisse automatiquement des outils d'interaction avec la base de données lors de l'ouverture du fichier.

Comment suivre les modifications de la structure de la base de données après avoir enregistré le dump

En sauvegardant le dump de la structure actuelle de la base de données dans le VCS, nous avons la possibilité de vérifier si des modifications ont été apportées à la structure de la base de données après la création du dump. Dans la bibliothèque schema-keeper une fonction est prévue pour détecter les changements dans la structure de la base de données verifyDump, qui retourne, sans effets secondaires, des informations sur les différences.

Une autre méthode de vérification consiste à rappeler la fonction saveDump, en indiquant le même répertoire, et à vérifier dans le VCS la présence de changements. Comme tous les objets de la base de données sont sauvegardés dans des fichiers séparés, le VCS affichera uniquement les objets modifiés.
Le principal inconvénient de cette méthode est la nécessité de réécrire les fichiers pour voir les changements.

Comment transférer les modifications de la structure de la base de données vers d'autres environnements sans conflits et avec des fichiers de migration gérables

Grâce à la fonction deployDump , le code source des procédures stockées peut être modifié exactement comme du code source d'application ordinaire. On peut ajouter/supprimer des lignes dans le code des procédures stockées et immédiatement envoyer les modifications dans le système de contrôle de version, ou créer/supprimer des procédures stockées en créant/supprimant les fichiers correspondants dans le répertoire du dump.

Par exemple, pour créer une nouvelle procédure stockée dans le schéma public , il suffit de créer un nouveau fichier avec l'extension .sql dans le répertoire public/functions, d'y placer le code source de la procédure stockée, y compris le bloc CREATE OR REPLACE FUNCTION, puis d'appeler la fonction deployDump. De la même manière, la modification et la suppression de la procédure stockée se produisent. Ainsi, le code est simultanément envoyé à la fois dans le VCS et dans la base de données.

Si une erreur apparaît dans le code source d'une procédure stockée, ou s'il y a une incohérence entre le nom du fichier et celui de la procédure stockée, alors deployDump ne s'exécutera pas, affichant le texte de l'erreur. L'incohérence entre les procédures stockées du dump et de la base de données actuelle est impossible lorsqu'on utilise deployDump.

Lors de la création d'une nouvelle procédure stockée, il n'est pas nécessaire de saisir manuellement le bon nom de fichier. Il suffit que le fichier ait l'extension .sql. Après avoir appelé deployDump , le texte de l'erreur contiendra le bon nom qui pourra être utilisé pour renommer le fichier.

deployDump permet de modifier les paramètres de la fonction ou le type de retour sans actions supplémentaires, tandis qu'avec l'approche classique, il aurait fallu
d'abord exécuter DROP FUNCTION, puis seulement CREATE OR REPLACE FUNCTION.

Malheureusement, il existe certaines situations où deployDump nous ne pouvons pas appliquer automatiquement les modifications. Par exemple, si une fonction déclenchée est supprimée et qu'elle est utilisée par au moins un déclencheur. Ces situations doivent être résolues manuellement à l'aide de fichiers de migration.

Si le transfert des modifications dans les procédures stockées est pris en charge par schema-keeper, il est alors nécessaire d'utiliser des fichiers de migration pour transférer les autres modifications dans la structure. Par exemple, une bonne bibliothèque pour travailler avec les migrations est doctrine/migrations.

Les migrations doivent être appliquées avant le lancement deployDump. Cela permet d'apporter toutes les modifications à la structure et de résoudre les problèmes afin que les modifications dans les procédures stockées soient ensuite transférées sans problèmes.

Nous décrirons plus en détail le travail avec les migrations dans les sections suivantes.

Comment établir un processus de travail parallèle sur le projet pour plusieurs développeurs

Il est nécessaire de créer un script d'initialisation complète de la BDD, qui sera exécuté par le développeur sur sa machine de travail, amenant la structure de la BDD locale en conformité avec le dump sauvegardé dans le VCS. Il est plus simple de diviser l'initialisation de la BDD locale en 3 étapes :

Importer le fichier avec la structure de base, qui s'appellera par exemple base.sql
Application des migrations
de system-nspawn deployDump

base.sql — c'est le point de départ à partir duquel les migrations sont appliquées et exécutent deployDump, c'est-à-dire base.sql + migrations + deployDump = structure actuelle de la BDD. On peut former ce fichier à l'aide de l'utilitaire pg_dump. Il est utilisé base.sql uniquement lors de l'initialisation de la base de données à partir de zéro.

Nommons le script d'initialisation complète de la BDD refresh.sh. Le flux de travail peut ressembler à ceci :

Le développeur exécute dans son environnement refresh.sh et obtient la structure actuelle de la BDD
Le développeur commence à travailler sur la tâche assignée, en modifiant la BDD locale pour les besoins de la nouvelle fonctionnalité (ALTER TABLE ... ADD COLUMN etc.)
Après avoir terminé la tâche, le développeur appelle la fonction saveDump, pour enregistrer les modifications apportées à la BDD dans le VCS.
Le développeur relance refresh.sh, puis verifyDump, qui affiche désormais la liste des changements à inclure dans la migration.
Le développeur transfère toutes les modifications de structure dans le fichier de migration, relance à nouveau refresh.sh et verifyDump, et si la migration est correctement rédigée, verifyDump il montrera qu'il n'y a pas de différences entre la BDD locale et le dump sauvegardé.

Le processus décrit ci-dessus est compatible avec les principes de gitflow. Chaque branche dans le VCS contiendra sa propre version du dump, et lors de la fusion des branches, les dumps seront également fusionnés. Dans la plupart des cas, aucune action supplémentaire n'est nécessaire après la fusion, mais si des modifications ont été apportées dans différentes branches, par exemple dans la même table, un conflit peut survenir.

Considérons une situation conflictuelle à titre d'exemple : il y a une branche develop, à partir de laquelle deux branches ont été créées : feature1 et feature2, qui n'ont pas de conflits avec develop, mais ont des conflits entre elles. L'objectif est de fusionner les deux branches dans develop. Dans ce cas, il est recommandé de d'abord fusionner l'une des branches dans develop, puis de fusionner develop dans la branche restante, en résolvant ainsi les conflits dans la branche restante, puis de réaliser la fusion de la dernière branche dans develop. Lors de la résolution des conflits, il peut être nécessaire de corriger le fichier de migration dans la dernière branche pour qu'il corresponde au dump final, qui inclut les résultats des fusions.

Comment déployer en toute sécurité un plus grand nombre de modifications de la structure de la base de données dans l'environnement de production

Grâce à la présence d'un dump de la structure actuelle de la base de données dans le VCS, il est possible de vérifier si la base de production correspond exactement à la structure requise. Cela garantit que toutes les modifications conçues par les développeurs ont été correctement transférées à la base de production.

Puisque DDL dans PostgreSQL est transactionnelle, il est recommandé de suivre l'ordre de déploiement suivant, afin qu'en cas d'erreur imprévue, la restauration soit « indolore » : ROLLBACK:

Commencer la transaction
Dans la transaction, exécuter toutes les migrations
Dans cette même transaction, effectuer deployDump
Sans terminer la transaction, exécuter verifyDump. S'il n'y a pas d'erreurs, exécuter COMMIT. S'il y a des erreurs, exécuter ROLLBACK

Ces étapes s'intègrent assez facilement dans les approches existantes de déploiement d'applications, y compris les déploiements sans temps d'arrêt.

Conclusion

Grâce aux méthodes décrites ci-dessus, il est possible d'extraire un maximum de performance des projets « PHP + PostgreSQL », au détriment d'un confort de développement relativement faible comparé à la mise en œuvre de toute la logique métier dans le code principal de l'application. De plus, le traitement des données dans PL/pgSQL semble souvent plus transparent et nécessite moins de code que la même fonctionnalité écrite en PHP.

Source : habr.com

Logique métier dans la base de données à l'aide de SchemaKeeper