Étiquette : MySQL

Vincent Lecomte
[SQL] Concaténer en une seule chaine par ligne

[SQL] Concaténer en une seule chaine par ligne

Il existe, en MySQL, une fonction qui s’appelle GROUP_CONCAT et qui permet de concaténer – d’agréger – plusieurs éléments récupérés depuis une seule table en vue d’obtenir une seule chaine de caractères, par ligne de résultat. Cela permet, par exemple, de récupérer les informations d’un livre, avec les auteurs séparés par des “,”, et ce en une seule ligne de résultat. L’exemple repris dans la documentation de MySQL permet d’afficher le nom des étudiants avec leurs résultats aux différents tests.

SELECT student_name, GROUP_CONCAT(test_score) 
FROM student 
GROUP BY student_name;

Avec DB2 c’est plus délicat et selon la version de la base de données, il faudra utiliser une fonction différente. Par exemple, dans les versions 9.2 et supérieures (corrigez-moi si je fais erreur), il existe la fonction LISTAGG qui reçoit en paramètre le champ pour lequel on veut concaténer les valeurs, et le séparateur.La même fonction – LISTAGG – existe dans le SGBD Oracle, à partir de la version 11g. En ce qui concerne les versions inférieures on peut se dépanner avec une procédure stockée.

Sur AS/400, il a fallu trouver une alternative. Il faut donc utiliser des fonctions XML pour arriver à un résultat similaire, et si on le souhaite, utiliser la fonction REPLACE directement dans la requête pour modifier la chaîne résultante. C’est un peu plus lourd pour la lisibilité globale de la requête.

SELECT pc.ean, pc.libelle, 
	(SELECT 
		XMLSERIALIZE( 
			XMLAGG( 
				XMLELEMENT(NAME x, trim(auteur_nom)) 
			) AS VARCHAR(1024) CCSID 500 
		) 
	FROM auteurs 
	JOIN art_auteurs on pc.ean = art_auteurs.ean 
	AND art_auteurs.idaut = auteurs.idaut
	) AS liste_auteurs 
FROM articles AS pc

On utilise 3 fonctions au total :

  • XMLSERIALIZE : va retourner une expression XML sérialisée, dans le type donné. Le paramètre correspond à l’expression XML. Lors des différents essais l’erreur SQL0332 s’est produite. Il faut faire attention à l’encodage par rapport au client, d’où l’utilisation de la clause CCSID dans l’exemple. Plus d’informations dans la documentation IBM.
  • XMLAGG : retourne une séquence XML avec un objet pour chaque valeur non-nulle contenue dans un ensemble de valeurs XML Le paramètre, dans l’exemple, est donc l’ensemble d’éléments “nom d’auteur”. En savoir plus.
  • XMLELEMENT : renvoie un champ sous forme d’élément XML. Dans l’exemple, le nom de l’auteur sera transformé en NOM. Le premier paramètre correspond au nom de l’attribut, le second au champ à transformer. En savoir plus.

Il suffit ensuite, soit dans votre programme soit directement dans la requête, de modifier la chaine pour y mettre des “,” ou tout autre caractère de votre choix. Bon développement !

[SQL] Insertion multiple

[SQL] Insertion multiple

Dans de nombreuses bases de données, il existe la possibilité d’effectuer une insertion multiple avec une seule requête SQL. La syntaxe suivante montre comment écrire la requête pour ajouter deux enregistrements.

INSERT INTO ma_table (col1, col2, ..., col n) 
VALUES 
  (val1, 'val2',..., val n),
  (val4, 'val5',..., val z)

La même requête en omettant le nom des colonnes.

INSERT ALL INTO ma_table (col1, col2,..., col n) 
VALUES (val1, 'val2',..., val n) 
INTO ma_table (col1, col2,..., col n) 
VALUES (val4, 'val5',..., val z)

Compatibilité

Disponible sur (liste non exhaustive) :

  • MySQL.
  • DB2 (idem sur OS/400).
  • HFSQL (à partir de la version 20).
  • PostgreSQL.
  • SQL Server 2008 et supérieur.
  • (…)

Si vous utilisez la base de données Oracle, la syntaxe est différente.

INSERT ALL INTO ma_table (col1, col2,..., col n) 
VALUES (val1, 'val2',..., val n)
INTO ma_table (col1, col2,..., col n) 
VALUES (val4, 'val5',..., val z)

Si vous utilisez une ancienne version de SQL Server (l’astuce suivante peut aussi être adaptée pour une base SQLite ou même Firebird).

INSERT INTO [schema.]MaTable (ID, Nom) 
SELECT 200, 'Vincent' 
UNION ALL 
SELECT 300, 'Cyril' 
UNION ALL 
SELECT 400, 'Rudi'

Avec Firebird 2.0 et supérieur on peut exécuter sous forme de bloc (voir la documentation officielle). N’hésitez pas à partager la syntaxe pour effectuer la même opération dans des bases de données différentes !

[SQL] Mémo – Modifier le type d’une colonne

[SQL] Mémo – Modifier le type d’une colonne

Pour modifier le type d’une colonne dans une table DB2/400, vous pouvez utiliser la syntaxe ALTER TABLE … ALTER COLUMN. Il faudra cependant spécifier une clause supplémentaire : SET DATA TYPE. Cela vous permettra de changer la taille par exemple, ou bien passer d’un entier sur 4 octets à un entier sur 8. Exemple :

ALTER TABLE 'biblio.table' ALTER COLUMN email 
SET DATA TYPE varchar(50);

Dans cet exemple nous modifions la colonne qui contient l’e-mail en indiquant qu’on souhaite une chaine de caractères à taille variable d’un maximum de 50 caractères. Dans SQL Server, cela donnerait quelque chose comme ceci :

ALTER TABLE 'schema.table' ALTER COLUMN email varchar(50);

Et dans Oracle ou même MySQL :

ALTER TABLE 'schema.table' MODIFIY COLUMN email varchar(50);

Bon développement !

[Web] PHP & Zend : Benchmark PDO vs Doctrine

[Web] PHP & Zend : Benchmark PDO vs Doctrine

Attardons-nous encore un moment dans les bases de données avec ce test de performances semblable au précédent, qui avait été réalisé durant mon travail de fin d’études (qui, au passage, a été écrit en anglais et en collaboration avec un ami). Ceci est un comparatif qui met face-à-face le PDO intégré à Zend Framework et Doctrine ORM.

Au début nous utilisions la méthode “select” du PDO mais nous avons bien sûr essayé la fonction qui exécute directement les requêtes codées en SQL (méthode “query“, toujours fournie par Zend). Par la suite nous avons découvert l’ORM (Object Relational Mapper) répondant doux nom de “Doctrine“, et nous nous sommes posé la question de savoir quel était le moyen le plus rapide pour l’exécution d’une requête. De plus, pour tester de manière plus approfondie, nous avons aussi manipulé le “DQL” qui signifie Doctrine Query Language, et dont la syntaxe ressemble vaguement à celle de la méthode “select” en Zend brièvement décrite ci-dessus.

Afin de comparer les différentes techniques, nous avons imaginé 4 scénarios :

  • Sélection de dix enregistrements.
  • Sélection de cent enregistrements.
  • Sélection de mille enregistrements.
  • Mise à jour d’un enregistrement au hasard parmi les enregistrements existants.

Dans notre cas les tests ont été effectués directement depuis le serveur (puisque le PHP est un langage qui s’exécute côté serveur). Ce serveur étant situé à Paderborn, en Allemagne. Puisque celui-ci est distant et que nous n’avions aucun contrôle, les valeurs pouvaient changer d’heure en heure selon les threads et autres tâches qui étaient en exécution. C’est pourquoi nous avons pris nos valeurs à différents moments de la journée, pour nous assurer de leur cohérence.

Voici en complément la configuration matérielle de l’environnement:

Point de vue matériel:

  • QEMU Virtual CPU version 0.11.0 à une fréquence de 2793.225 MHz avec2048 KB de cache.
  • RAM virtuelle de 512 MB QEMU.
  • Harddisk version 0.11.0 QEMU.

Point de vue logiciel:

  • Apache/2.2.8 (Ubuntu) DAV/2.
  • SVN/1.4.6.
  • mod_python/3.3.1.
  • Python/2.5.2.
  • PHP/5.3.3-dev with eAccelerator 0.9.6-rc1.
  • MySQL version5.1.45-0.dotdeb.0.

Calcul du temps

La fonction microtime en PHP permet de récupérer le temps en millisecondes. Ainsi, en exécutant la fonction entre les deux points, donc avant et après, il est possible d’obtenir une valeur A et une valeur B. On soustrait A de B, pour obtenir C, qui est le temps d’exécution en millisecondes.

Tests effectués

  • Requête SQL avec l’adaptateur PDO.
  • Fonctions “Select” et “update” de Zend.
  • Requête DQL avec Doctrine 1.2.2.
  • Mapping d’objet avec Doctrine 1.2.2.

Les enregistrements présents dans la table ont tous le même contenu, avec une seule clé primaire (un ID). On a un entier comme clé primaire, un autre champ entier, un champ texte et un champ de type timestamp. La base de données est de type MySQL. Les temps seront, comme vous vous en doutez, exprimés en millisecondes.

Graphique

Les plus petites valeurs sont les meilleures, cela s’entend. La première chose que l’on peut constater est que, tout en tenant compte des conditions de test et de la méthode employée pour réaliser celui-ci, Doctrine semble être le plus lent pour tout. Cela peut probablement provenir du fait qu’il y a deux couches qui viennent se rajouter au traditionnel PDO (qui est l’API de base pour interagir avec la base de données).

On peut effectivement lire dans la documentation officielle qu’il y a premièrement une couche appelée “Doctrine ORM” (la plus haute sur le schéma), chargée de créer un pont entre le modèle relationnel et le modèle objet.

Sous celle-ci, on retrouve aussi la couche d’administration qui complète et étend la couche basique d’abstraction définie par le PDO.

Pour vous donner une idée de ce que cela peut donner lors d’une mise à jour, voici un graphique reprenant les moyennes de toutes les valeurs retenues, et ce pour les 4 techniques :

En conclusion, l’utilisation d’un ORM facilite grandement la tâche au développeur (puisque l’écriture des requêtes est simplifiée) mais risque de ralentir l’exécution de votre application web lorsque le nombre d’enregistrements est conséquent. Les créateurs de Doctrine promettaient une rapidité accrue avec la version 2, mais je n’ai pas encore eu l’occasion de la tester. Un jour, peut-être?

Références

Stack Overflow

[MySQL] Bases de la création d’un événement

[MySQL] Bases de la création d’un événement

Le programmateur d’événements a été introduit dans MySQL à partir de la version 5.1 du système de gestion de bases de données.

Il est donc possible de créer des tâches qui se déclenchent à une heure et une date donnée, un peu à la manière de triggers (ou en français, des déclencheurs) sauf que ces derniers sont lancés par des mises à jour de tables ou de simples lignes, par exemple.

Dans le cas du programmateur, on peut déclencher une même tache plusieurs fois par jours, voire par semaine… Il y a plusieurs utilités à ça, dont :

  • Un nettoyage d’une table,
  • Une optimisation après un ajout massif de tuples (ou enregistrements),
  • Un calcul quelconque,
  • Etc…

Activation de l’Event Scheduler

Il vous faudra éditer le fichier “my.ini” du répertoire MySQL. Dans le cas d’EasyPHP 5.3.3., si l’installation a été effectuée dans le dossier par défaut, vous devez vous rendre dans C:\Program Files\EasyPHP-5.3.3\mysql pour trouver le fichier de configuration en question. Ajoutez la ligne event-scheduler=VALEUR si elle n’existe pas et définissez la valeur à :

  • 0, pour que le service de programmation d’événements ne démarre jamais ou ne puisse être démarré.
  • 1, pour que le service soit lancé mais qu’il puisse être mis en pause (suspendu) par requête.
  • 2, pour qu’il soit démarré mais directement en mode “suspendu”. Une requête peut le réactiver.

Bien sûr, MySQL doit être redémarré. EasyPHP permet le redémarrage automatique de ses services lorsque l’un des fichiers de configuration est modifié. Pour vérifier que le service est actif, utilisez la requête suivante, soit en interface de ligne de commandes, soit grâce à PHPMyAdmin :

SHOW GLOBAL VARIABLES LIKE 'event_scheduler'

Il est aussi possible d’activer ou désactiver le programmateur d’événements en ligne de commande en rajoutant, aux paramètres de lancement du processus “mysqld”, –event-scheduler=VALEUR, où la valeur est égale à 0, 1, ou 2. Une dernière méthode a été prévue: l’utilisation de la requête SET GLOBAL, permettant d’éditer des variables d’environnement.

Créer l’événement

La syntaxe est la suivante :

CREATE
	[DEFINER = { user | CURRENT_USER }]
	EVENT
	[IF NOT EXISTS]
	nom_evenement
	ON SCHEDULE schedule
	[ON COMPLETION [NOT] PRESERVE]
	[ENABLE | DISABLE | DISABLE ON SLAVE]
	[COMMENT 'comment']
	DO corps_evenement;

schedule:
	AT timestamp [+ INTERVAL interval] ...
	| EVERY interval
	[STARTS timestamp [+ INTERVAL interval] ...]
	[ENDS timestamp [+ INTERVAL interval] ...]

interval:
	quantité {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
	WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
	DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

L’événement ne peut être créé que si on en a l’autorisation. Pour travailler en local sur votre projet, l’utilisateur ‘root’ devrait déjà disposer de l’autorisation de création et d’exécution (Commandes GRANT et REVOKE). La permission doit être donnée pour une base donnée et éventuellement pour une table donnée, sinon toutes (.*).

Destruction de sessions

CREATE EVENT IF NOT EXISTS delete_sessions 
ON SCHEDULE EVERY 180 MINUTE 
DO DELETE FROM WEB_SESSIONS 
WHERE lastdate < (NOW() - INTERVAL 3600 SECOND);

Cet événement se déclenchera toutes les 180 minutes pour supprimer les sessions qui ont expiré, celles-ci étant stockées dans une table appelée WEB_SESSIONS. Dans celle-ci le champ ‘lastdate’ est en fait au format TIMESTAMP (MySQL, et non Unix). Les 3600 secondes correspondent au temps de session défini, et donc, vous pouvez librement modifier cette valeur, puisque ici c’est vous le boss !

Notez que vous pouvez aussi utiliser la fonction TIMESTAMPDIFF() qui permet de calculer la différence entre deux TIMESTAMP et de retourner le résultat sous forme de minutes ou de secondes par exemple (voir la documentation sur les fonctions de date et heure). La requête suivante est aussi valable :

DELETE FROM WEB_SESSIONS 
WHERE (TIMESTAMPDIFF(SECOND,lastdate,NOW()) > 3600);

Maintenant, il faut activer l’événement (le programmateur doit être actif pour que l’événement s’exécute, sinon son activation n’aura aucun impact) :

ALTER EVENT nom_evenement ENABLE;

Si vous voulez le désactiver :

ALTER EVENT nom_evenement DISABLE;

Connaitre l’état

Il est possible de connaître l’état d’un événement en consultant le schéma d’information de votre base de données, grâce à la requête suivante :

SELECT EVENT_NAME, STATUS 
FROM INFORMATION_SCHEMA.EVENTS 
WHERE EVENT_NAME = 'nom_evenement' 
AND EVENT_SCHEMA = 'nom_schema';

Vous pouvez aussi consulter la table “mysql.event” qui contient toutes les informations à propos de votre événement (qui en est le créateur, etc). Exécutez par exemple la requête suivante pour afficher tous les événements créés :

SELECT * FROM mysql.event;

Pour l’instant, ce sera tout à propos des événements. Il est également possible de voir plus loin, par exemple créer un événement qui exécute une fonction. Pour l’instant n’hésitez pas à consulter les deux sources afin d’en apprendre un peu plus à ce sujet.

Sources

Developpez
Documentation MySQL 5.1