Pivoter une table sans IF/CASE en MySQL.

Une des choses qui j'aime dans la société ou je travail, c'est de trouver des 'jokes' en lisant les scripts des nos devs.
En fait, nous bossons avec des bases MySQL 4.0.24 (La honte :) sous linux, la chose qui exclut l'utilisation de plein de fonctions définit dans les versions du MySQL qui suivent, tel les sous-selections (SubSELECT), GROUP_CONCAT, ROLLUP, ..., en deux mots la puissance du MySQL!
Et par conséquent, dans nos script, on ne fait que des simples SELECT ... FROM, et encore pire, dans la plupart des cas, on fait des requetes du genre : "Wa7da te7leb wlo5ra tched le9roun" (ceci est malheureusement est intraduisible en français :).

Voici un exemple simplifié du l'existant, ou on suppose qu'on possede une table du suivi des clients Reporting_client :

CREATE TABLE `Reporting_client` (
   `id_client` INT NOT NULL ,
 `type_action` VARCHAR( 3 ) NOT NULL ,
 `date_action` DATETIME NOT NULL
) TYPE = MYISAM ;

Sans trop de detail, id_client c'est l'id du client bien evidement, type_action contient le type d'action du client ('O' ouverture, 'C' clique) et date_action :

mysql> SELECT * FROM Reporting_client ;
+-----------+-------------+---------------------+
| id_client | type_action | date_action         |
+-----------+-------------+---------------------+
|         1 |           O | 2006-12-01 15:07:53 |
|         1 |           C | 2006-12-01 15:07:53 |
|         1 |           C | 2006-12-01 15:07:53 |
|         2 |           O | 2006-12-01 15:07:53 |
+-----------+-------------+---------------------+
4 rows in set (0.00 sec)

Une question qui se pose presque toujours chez nous, c'est de connaitre les actions d'un client, autrement "determiner les clients ouvereurs/cliqueurs, ouvereurs non cliqueurs, cliqueurs non ouvereurs, etc..". Et pour resoudre ce fardeaux, chez nous, on passe toujours par ce qui suit :

Lancer la requete :
" SELECT *
 FROM Reporting_client
WHERE type_action = 'O'"

Pour chaque client trouvé, lancer la 2e requete :
  " SELECT count(*)
      FROM Reporting_client
     WHERE type_action = 'C'
       AND id_client = ? "

• En sachant que notre table contient des milliers voir millions des lignes, un tel script, sera un vrai cauchemar pour le serveur, et pour nous par conséquant!

Ceci est un exemple tres simplifié et parmis plusieurs, alors qu'une des solutions possibles est de poser la simple question : "Existe il une solution meilleur que celle la?!".

En reponse : parfois il suffit d'interroger un moteur de recherche pour tomber sur un forum ou sur un tutorial qui explique mieux les choses et qui propose des solution elegante, comme celle ci :

The simple secret, and it's also why they work in almost all databases, is the following functions:
o   sign (x) returns -1,0, +1 for values x < x =" 0,"> 0 respectively
o   abs( sign( x) )  returns 0 if x = 0  else, 1 if  x > 0 or x < x =" 0">
o   1-abs( sign( x) ) complement of the above, since this returns 1 only if x = 0

Donc, pour exploiter ceci, on aura besoin des code ASCII des caractere 'O' et 'C' qui sont respectivement 79 et 67. Puis, et par magie, du math (que j'aime pas :), on applique les regeles précédentes on aura la solution toute en un (une seule requete qui nous donne tout les comptages des ouvreurs, cliqueurs, ... ) :

mysql> select ASCII('C'), ASCII('O');
+------------+------------+
| ASCII('C') | ASCII('O') |
+------------+------------+
|         67 |         79 |
+------------+------------+
1 row in set (0.00 sec)

mysql> SELECT id_client,
->            SUM(( 1 - ABS( SIGN( ASCII(type_action) - 79 )))) AS open,
->            SUM(( 1 - ABS( SIGN( ASCII(type_action) - 67 )))) AS clik
-> FROM Reporting_client
-> GROUP BY id_client;
+-----------+------+------+
| id_client | open | clik |
+-----------+------+------+
|         1 |    1 |    2 |
|         2 |    1 |    0 |
+-----------+------+------+
2 rows in set (0.00 sec)

---

BIB :

• souptonuts.sourceforge.net

Des procedures stokées pour connaitre et générer des stats sur l'etat d'une base de données, l'etat d'une table, etc.

Généralement sur les serveurs de productions ou sont installé les serveur MySQL, on ne trouve pas des outils de qui permettent de gérer facilement et rapidement les bases de données via des interfaces graphiques (phpMyAdmin & like) et qui sont plus pratique que de le faire par le Terminal (client MySQL classique).

Cependant les DB admins, les IT ou les developpeurs eux mêmes, sont obligés parfois de passer par un Terminal et de tapez des longues requetes via le client MySQL pour avoir des informations sur l'etat du serveur, l'etat d'une base de données, l'etat d'une table, etc.!

Pour cela, on compte faire dans ce qui suit quelques procedures qui nous seront utiles, rapides et pratiques dans ces cas :

• Problème : C'est d'avoir un idée sur l'etat d'une base de données et l'etat d'une table en créeant des fonctions qui nous permettent de recupéré les infos suivantes :

• Table 1 : Avoir un aperçu sur les bases qui existentes sur le serveur MySQL, l'espace d'occupation en MB, en Pourcentage et un Histograme qui est une représentation graphique de l'ensemble de données qui met en évidence l'espace occupé par chaque BD :
  

mysql> CALL dbsize();
+--------------------+-----------+----------+------------------------------------+
| base               | Size (MB) | Pourcent | Graph                              |
+--------------------+-----------+----------+------------------------------------+
| test               |    0.0010 |     0.01 | *                                  |
| olap               |    0.0011 |     0.01 | *                                  |
| webauth            |    0.0020 |     0.01 | *                                  |
| cdcol              |    0.0031 |     0.02 | *                                  |
| information_schema |    0.0040 |     0.03 | *                                  |
| phpmyadmin         |    0.0070 |     0.04 | *                                  |
| faq_edt            |    0.0447 |     0.29 | *                                  |
| projectstatus      |    0.0652 |     0.42 | *                                  |
| idma_101           |    0.0880 |     0.56 | *                                  |
| faq_sql            |    0.1003 |     0.64 | *                                  |
| mos                |    0.1488 |     0.95 | *                                  |
| idma_102_old       |    0.1772 |     1.13 | *                                  |
| wordpress          |    0.1895 |     1.21 | *                                  |
| idma_102           |    0.2640 |     1.69 | **                                 |
| mysql              |    1.6560 |    10.59 | *****                              |
| olapx0d            |    3.3677 |    21.54 | ********                           |
| tn_blog_feed       |    9.5201 |    60.88 | *********************              |
| TOTAL :            |   15.6374 |   100.00 | ********************************** |
+--------------------+-----------+----------+------------------------------------+
18 rows in set (2.81 sec)

Query OK, 0 rows affected (3.80 sec)

• Table 2 : Quelques statistique pour avoir un aperçu sur les types des champs utlisés par toutes les bases de données sur le serveur MySQL en Nombre, Pourcentage et une représentation graphique :
  

mysql> CALL info_ctype();
+----------------+------+----------+----------------------------------+
| DATA_TYPE      | n    | Pourcent | Graph                            |
+----------------+------+----------+----------------------------------+
| time           |    1 |     0.05 | *                                |
| blob           |    1 |     0.05 | *                                |
| float          |    1 |     0.05 | *                                |
| float unsigned |    2 |     0.11 | *                                |
| decimal        |    3 |     0.16 | *                                |
| smallint       |    4 |     0.22 | *                                |
| mediumtext     |    4 |     0.22 | *                                |
| tinytext       |    5 |     0.27 | *                                |
| date           |    6 |     0.33 | *                                |
| timestamp      |    6 |     0.33 | *                                |
| char           |   17 |     0.93 | *                                |
| enum           |   23 |     1.25 | *                                |
| longtext       |   33 |     1.80 | **                               |
| bigint         |   59 |     3.22 | **                               |
| tinyint        |   73 |     3.98 | **                               |
| datetime       |   81 |     4.41 | **                               |
| text           |  159 |     8.66 | ****                             |
| varchar        |  600 |    32.70 | ************                     |
| int            |  610 |    33.24 | ************                     |
| TOTAL :        | 1688 |    91.99 | ******************************** |
+----------------+------+----------+----------------------------------+
20 rows in set (3.41 sec)

Query OK, 0 rows affected (3.80 sec)

• Table 3 : Pour une base de donnée donnée, on veut avoir un aperçu sur les tables qu'elle contient, le nombre de lignes, l'espace d'occupation de chacune en MB, en Pourcentage et un Histograme qui met en évidence de l'espace occupé par chaque table :
  

mysql> CALL dbinfos('mysql');
+---------------------------+----------+-----------+----------+---------------------------+
| Name table                | NB lines | Size (MB) | Pourcent | Graph                     |
+---------------------------+----------+-----------+----------+---------------------------+
| host                      |        0 |      1024 |     0.06 | *                         |
| func                      |        0 |      1024 |     0.06 | *                         |
| columns_priv              |        0 |      1024 |     0.06 | *                         |
| time_zone_leap_second     |        0 |      1024 |     0.06 | *                         |
| tables_priv               |        0 |      1024 |     0.06 | *                         |
| procs_priv                |        0 |      1024 |     0.06 | *                         |
| db                        |        1 |      4534 |     0.26 | *                         |
| user                      |        2 |      2172 |     0.13 | *                         |
| proc                      |        3 |      5804 |     0.33 | *                         |
| help_category             |       29 |     19921 |     1.14 | *                         |
| help_keyword              |      325 |     77337 |     4.44 | **                        |
| help_topic                |      403 |    203952 |    11.71 | *****                     |
| time_zone_name            |      545 |     47845 |     2.75 | **                        |
| time_zone                 |      545 |     10438 |     0.60 | *                         |
| help_relation             |      548 |     19268 |     1.11 | *                         |
| time_zone_transition_type |     2498 |     87724 |     5.04 | ***                       |
| time_zone_transition      |    37188 |   1255812 |    72.13 | ************************* |
+---------------------------+----------+-----------+----------+---------------------------+
17 rows in set (0.13 sec)

Query OK, 0 rows affected (0.23 sec)

---

• Solution : Et en se basant sur la base de données d'informations INFORMATION_SCHEMA du MySQL, on definit les trois procedures qui suit :
  

• Table 1, donnée par la procedure dbsize() :
  

DROP PROCEDURE IF EXISTS dbsize ;

DELIMITER  //

CREATE PROCEDURE dbsize ( )
BEGIN
SELECT * , LPAD('*', Pourcent / 3.0 + 1 , '*') as Graph
FROM (
     (
         SELECT TABLE_SCHEMA base,
                SUM( (DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024) ) as 'Size (MB)',
                ROUND(  SUM( (DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024) ) / ( SELECT  SUM( (DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024) ) FROM information_schema.TABLES ) * 100, 2 ) AS Pourcent
         FROM information_schema.TABLES
         GROUP BY base
     )
 Union
     (
         SELECT 'TOTAL : ' base,
                SUM( (DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024) ) as 'Size (MB)' ,
                ROUND(  SUM( (DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024) ) / ( SELECT  SUM( (DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024) ) FROM information_schema.TABLES ) * 100, 2 ) AS Pourcent
         FROM information_schema.TABLES
     )
) x
ORDER BY 2 ASC ;
END //

DELIMITER ;

• Table 2, donnée par la procedure info_ctype() :

DROP PROCEDURE IF EXISTS info_ctype ;

DELIMITER  //

CREATE PROCEDURE info_ctype ( )
BEGIN
SELECT
 DATA_TYPE, n, Pourcent, LPAD('*', Pourcent / 3.0 + 1 , '*') as Graph
FROM (
         (
             SELECT  DATA_TYPE,
                     COUNT(*) AS n,
                     ROUND(COUNT(*) / ( SELECT COUNT(*) FROM information_schema.COLUMNS ) * 100, 2 ) AS Pourcent
             FROM    information_schema.COLUMNS
             WHERE   TABLE_SCHEMA NOT LIKE 'mysql'
             GROUP BY DATA_TYPE
         )
   Union
         (                                                                                                                                                                                                                                      
             SELECT 'TOTAL : ', COUNT(*), ROUND(COUNT(*) / ( SELECT COUNT(*) FROM information_schema.COLUMNS ) * 100, 2 ) AS Pourcent
             FROM    information_schema.COLUMNS
             WHERE   TABLE_SCHEMA NOT LIKE 'mysql'
             ORDER BY n DESC
         )
  ) x
ORDER BY n ASC ;
END //

DELIMITER ;

• Table 3, donnée par la procedure dbinfos('nom_base') :
  

DROP PROCEDURE IF EXISTS dbinfos ;

DELIMITER  //

CREATE PROCEDURE dbinfos ( base CHAR(50) )
BEGIN
SELECT * , LPAD('*', Pourcent / 3.0 + 1 , '*') as Graph
FROM (
      (
          SELECT  TABLE_NAME as 'Name table',
                  TABLE_ROWS as 'NB lines',
                  ( DATA_LENGTH + INDEX_LENGTH) as 'Size (MB)',
                  ROUND(  SUM( (DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024) ) / ( SELECT  SUM( (DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024) ) FROM information_schema.TABLES WHERE table_schema = base ) * 100, 2 ) AS Pourcent
          FROM information_schema.tables
          WHERE table_schema = base
          GROUP BY TABLE_NAME
      )
) x
ORDER BY 2, 3 DESC;
END //

DELIMITER ;

Voila,
Nos procedures ne sont pas optimisées,
mais elles servent a quelque chose, comme même :)

---

BIB :

• Manuel de référence MySQL 5.0, MySQL AB.

Executer des procédures stockées sur MySQL 4.x

Sur db4free.net blog se trouve un article tres interressant, qui decrit comment executer des "Stored Procedures on MySQL 4.x" via un "MySQL Server 5.x".

En fait, il n'est pas possible d'executer des procédures stockées sur MySQL 4.x directement parcequ' ils ne sont disponibles qu'apres la version 5.0 du Serveur MySQL, mais via un serveur MySQL 5.x avec le moteur FÉDÉRÉ activé (moteur de stockage FEDERATED : Les tables fédérées permettent un accès local à des données distantes en servant d'interface avec un autre serveur, et permet ainsi d'accéder aux données de cette table distante depuis un serveur local).

Voici comment cela fonctionne - nous commençons par ceci sur le serveur MySQL 4.x :

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 4.0.18-nt |
+-----------+
1 row in set (0.03 sec)

mysql> SHOW CREATE TABLE t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(10) unsigned NOT NULL auto_increment,
`val` int(10) unsigned NOT NULL default '0',
`ts` timestamp(14) NOT NULL,
PRIMARY KEY  (`id`)
) TYPE=MyISAM
1 row in set (0.03 sec)

mysql> SELECT * FROM t1;
Empty set (0.03 sec)

La prochaine étape est de créer une table FEDERATED avec la même définition sur un serveur MySQL 5.x :

mysql> SELECT version();
+-----------------+
| version()       |
+-----------------+
| 5.1.13-beta-log |
+-----------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `t1` (
->   `id` int(10) unsigned NOT NULL auto_increment,
->   `val` int(10) unsigned NOT NULL default '0',
->   `ts` timestamp(14) NOT NULL,
->   PRIMARY KEY  (`id`)
-> ) ENGINE=FEDERATED
-> CONNECTION='mysql://user:password@host_4x_server/database/t1';
Query OK, 0 rows affected (0.72 sec)

Tout que nous devons faire est de créer une procédure stockée sur le serveur MySQL 5.x qui accède à la table FEDERATED :

DROP PROCEDURE IF EXISTS fill_t1;

DELIMITER //

CREATE PROCEDURE fill_t1 (_rows INT)
BEGIN
DECLARE cnt INT DEFAULT 0;

WHILE cnt < _rows DO
INSERT INTO t1 (val) VALUES (FLOOR(RAND()*1000));

SET cnt := cnt + 1;
END WHILE;
END //

DELIMITER ;

Maintenant il est simple d'insérer quelques lignes en utilisant cette procédure stockée :

mysql> CALL fill_t1(10);
Query OK, 1 row affected (0.69 sec)

Et le contenu de la table t1 sur le serveur MySQL 4.x est le suivant :

mysql> SELECT * FROM t1;
+----+-----+----------------+
| id | val | ts             |
+----+-----+----------------+
|  1 | 952 | 20061123015754 |
|  2 | 591 | 20061123015754 |
|  3 |  98 | 20061123015754 |
|  4 | 718 | 20061123015754 |
|  5 | 295 | 20061123015754 |
|  6 | 324 | 20061123015754 |
|  7 | 733 | 20061123015754 |
|  8 | 693 | 20061123015754 |
|  9 | 269 | 20061123015754 |
| 10 | 264 | 20061123015754 |
+----+-----+----------------+
10 rows in set (0.03 sec)

---

BIB:

• db4free.net blog

Emultaion des Extensions OLAP en MySQL :: ROLLUP

OLAP :

Littéralement, On-Line Analytical Processing. Désigne une catégorie d'applications et de technologies permettant de collecter, stocker, traiter et restituer des données multidimensionnelles, à des fins d'analyse. Une autre définition est résumée dans l'acronyme FASMI (Fast Analysis of Shared Multidimensional Information), ou analyse rapide d'information multidimensionnelle partagée. Les outils OLAP doivent respecter 12 règles précises.

OLAP Schema :

• Emploie typiquement un model en « ETOILE ».
• Les structures de dimensions tendent à être petites.
• Un nombre important d'enregistrements stockées dans des bases.
  

ROLLUP :

L'opération ROLLUP calcule les sous-totaux de lignes agrégées. Elle ajoute des lignes de sous-total dans les jeux de résultats des requêtes comportant des clauses GROUP BY. Elle génère un jeu de résultats indiquant les agrégats pour une hiérarchie de valeurs de colonnes sélectionnées. Utilisez l'opération ROLLUP si vous souhaitez qu'un jeu de résultats indique les totaux et les sous-totaux.

exp : Trouver toutes les ventes par région et le responsable des ventes pendant chaque mois de 1996, avec des sous-totaux pour chaque mois, et le total général :

• Creation du VIEW
  

CREATE VIEW Sales AS
(SELECT ds.*, YEAR (sales_date) AS year, MONTH (sales_date) AS month, DAY (sales_date) AS day
FROM (Detailed_Sales NATURAL JOIN Store NATURAL JOIN Product
NATURAL JOIN Period) ds


• La présentation des lignes de Totaux et sous-total en SQL:
  

SELECT month, region, sales_mgr, SUM (price)
FROM Sales
WHERE year = 1996
GROUP BY ROLLUP (month, region, sales_mgr)


Et l'equivalent en MySQL :

( SELECT month, region, sales_mgr, SUM( price ) FROM Sales GROUP BY month, region, sales_mgr )
UNION ALL
( SELECT month, region, '-', SUM( price ) FROM Sales GROUP BY month, region )
UNION ALL
( SELECT month, '-', '-', SUM( price ) FROM Sales GROUP BY month )
UNION ALL
( SELECT '-', '-', '-', SUM( price ) FROM Sales )

---

BIB:

• SQL99, SQL/MM, and SQLJ: An SQL99, SQL/MM, and SQLJ: An Overview of the SQL Standards - IBM Database Common IBM Database Common Technology Technology
• http://perso.orange.fr
  

GNU Screen, Simplifie encore la vie :

Screen is a full-screen window manager that multiplexes a physical terminal between several processes (typically interactive shells)

Dans la société ou je travaille (encore une fois :), nous utilisons plusieurs serveurs MySQL tournant sous debian linux, ou chaque jours nous somme obligé de se connecter a plusieurs serveur simultanément. Et l'erreur commise par la majorité de mes collègues, est l'ouverture de plusieurs Shell en même temps! (en utilisant SecureCRT, Putty, ...), jusqu’a la saturation de leurs pauvres machines (un shell est toujours gourmand coté ressource :).

Cependant, la solution est dans l'utilisation d'un multiplixeur des shell comme GNU Screen [http://www.gnu.org/software/screen/] .

Et voici quelques commandes fort utiles :

• Pour lister les sessions : screen - ls
• Pour créer une session dont le nom session_name : screen -D -R -S session_name
• Pour créer une nouvelle fenetre : Ctrl-A + c
• Pour quitter la fenêtre en cours : Ctrl-A + k
• Pour détacher une session screen : Ctrl-A + d
• Pour naviguer entre les fenêtres 1 : Ctrl-A + "
• Pour naviguer entre les fenêtres 2 : Ctrl-A + [0-9-] (Switch to window number 0 - 9, or to the blank window.)
• Et pour en savoir plus tapez "man screen" :p

Essayez, c'est magique :)

MySQL le fait aussi !

Dans la société où je travaille, je vois de plus en plus des erreurs et des mauvaises habitudes chez plusieurs développeurs (parfois par paresse [http://fr.wikipedia.org/wiki/Paresse_intellectuelle] et parfois par ignorance) :

• Pas mal de fois, je vois un développeur qui se connecte à deux sessions sur le même serveur, une pour MySQL et l'autre pour les commandes Shell, pour autant MySQL fait les deux!
• Pas mal de fois, je vois un développeur qui lance une grosse requête à MySQL, et puis il reste 10 min à copier le résultat affiché.
• Pas mal de fois, je vois un développeur qui tape 10 fois successive la touche Entrée dans le shell MySQL, rien que pour nettoyer la l'écran.
• Pas mal de fois, je vois un développeur qui tape Ctrl-C et arrête le pauvre programme, pour ne pas exécuter la requête qu'il vient de saisir.
• ...

Pourtant MySQL le dit a chaque fois qu'on se connecte :

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is XYZ to server version: 5.0.18

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


Tapez 'help' en ligne de commande, et MySQL va afficher ce qu'il sait faire :

• ego (\G) : Send command to MySQL server, display result vertically.
• Si vous voulez que MySQL affiche le résultat verticalement, faites-le en entrant \G (c'est plus lisible!)

mysql> SELECT * FROM db.table\G


• system (\!) : Execute a system shell command.
• Si vous voulez que MySQL exécute une commande système (y compris le clear screen), faites-le en commentant par \!

mysql> system ls -la
mysql> \! clear
mysql> \! scp ~/export.dmp user@host:/home/user/


• clear (\c) Clear command.
• Si vous décidez d'annuler une commande que vous etes en train de taper, faites-le en entrant \c : [1]

mysql> SELECT
-> USER()
-> \c
mysql>


• tee (\T) : Append everything into given outfile.
• Pour enregistrer les requêtes et leur résultat, utilisez la commande tee. Toutes les données affichées a l'écran seront ajoutées a un fichier donne. Cela peut être très pratique pour déboguer. Vous pouvez activer cette fonctionnalité en ligne de commande, avec l'option --tee, ou interactivement avec la commande tee. Le fichier tee peut être désactive interactivement avec la commande notée. Exécuter tee a nouveau re-active le log. Sans paramètre, le fichier précèdent sera utilise. Notez que tee envoie les requêtes dans le fichier après chaque commande, juste avant que MySQL ne l'affiche. [1]

mysql> tee ~/mySQLlog
mysql> notee


• pager (\P) : Set PAGER [to_pager]. Print the query results via PAGER.
• La lecture et la recherche dans les résultats de requêtes en mode interactif est possible en utilisant les programmes Unix less, more, ou tout autre programme similaire, avec l'option --pager. Si vous ne spécifier par de valeur d'option, MySQL regarde la valeur de la variable d'environnement PAGER, et utilise ce pager. Vous pouvez aussi l'activer interactivement avec la commande pager et la désactiver avec nopager. La commande prend un argument optionnel : s'il est fournit, le programme de pager indique sera utilise. Sinon, le pager sera celui indique en ligne de commande, ou stdout si aucun pager n'était indique. [1]

• ...
  

---

[1] : le manuel de référence du système de base de données MySQL. MySQL Server Books Online .

Générateur des TAGs en MySQL

Dans cet article nous essayons de mettre en place un outil qui permet de visualiser sous forme de nuage les "mots clefs" ou "tags" d'un article juste en utilisant MySQL, et le resultat retouné rassemble un petit peu a ceci :

Pour cela, on prend comme exemple la table wp_posts ( contenant les articles du blog) du WordPress ( le populaire système de publication Web open source)

Les étapes à suivre sont :

1. Conversion d'une chaîne de caractère en mots (from CSV to LIST)
2. Sélection des mots dont la longueur est supérieur a 2,
3. Elimination les mots du notre dictionnaire StopWord,
4. Calcule d'occurrence de chaque mots dans l'article,
5. Sélection des mots clefs dont l'occurrence est supérieur a 2,
6. Génération du code HTML pour chaque Tag.

mysql> select database();
+------------+
| database() |
+------------+
| wordpress  |
+------------+
1 row in set (0.0 sec)

La description de table wp_posts nous donne :

mysql> desc wp_posts;
+-----------------------+------
| Field                 | Type
+-----------------------+------
| ID                    | bigint(20) unsigned
| post_author           | bigint(20)
| post_date             | datetime
| post_date_gmt         | datetime
| post_content          | longtext
| post_title            | text
| post_category         | int(4)
...

A cette Base, on ajoute une table temporaire "intcp" pour faciliter l'opération du pivotage. Cette table contient un seul champ id de type auto_increment allant de 1 jusqu'a 1000. Le champ id nous sert d'un compteur, c'est pourquoi une limite de 1000 mots doit nous suffire.

mysql> desc intcp;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
+-------+---------+------+-----+---------+----------------+
1 row in set (0.00 sec)

mysql> select * from intcp limit 10;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
+----+
10 rows in set (0.00 sec)

Prenons par exemple le poste numéro 2, ou on netterra le contenu dans une variable @post_content, et dans la dernière requête, on éliminera les chiffres et les signes de ponctuation de la variable @post_content.

mysql> select ID, left(post_content, 100) from wp_posts Where id = 2 ;
+----+------------------------------------------------------------------------------------------------------+
| ID | left(post_content, 100)                                                                              |
+----+------------------------------------------------------------------------------------------------------+
|  2 | This is an example of a WordPress page, you could edit this to put information about yourself or you |
+----+------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

mysql> SELECT @post_content:=post_content FROM wp_posts WHERE ID = 2;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @post_content:=post_content                                                                                                                                  |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| This is an example of a WordPress page, you could edit this to put information about yourself or your site so readers know where you are coming from. You can create as many pages like this one or sub-pages as you like and manage all of your content inside of WordPress. |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

mysql> select  @post_content:=
->        replace(
->       replace(
->      replace(
->     replace(
->    replace(
->   replace(
->  replace(
-> replace(
->  replace(
->   replace(
->    replace(
->     replace(
->      replace(
->       replace(
->        replace(
->         replace(
->          replace(@post_content,
->          '9',' '),
->         '8',' '),
->        '7',' '),
->       '6',' '),
->      '5',' '),
->     '4',' '),
->    '3',' '),
->   '2',' '),
->   '1',' '),
->    '0',' '),
->     '.',' '),
->      ';',' '),
->       '!',' '),
->        '?',' '),
->         '+',' '),
->          '*',' ') ,
->           ',',' ' ) as post_content_sans_chiffre_sans_ponctuation;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| post_content_sans_chiffre_sans_ponctuation                                                                                                                                  |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| This is an example of a WordPress page  you could edit this to put information about yourself or your site so readers know where you are coming from  You can create as many pages like this one or sub-pages as you like and manage all of your content inside of WordPress  |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

La conversion d'une chaîne de caractère en mots (from CSV to LIST) se fait comme suit :

mysql> select substring_index(substring_index(@post_content,' ',iter.cp),' ',-1) wrd
-> from  (select id cp from intcp) as iter
-> where iter.cp <= (length(@post_content)-length(replace(@post_content,' ','')))+1;
+-----------+
| wrd       |   
+-----------+   
| This      |   
| is        |   
| an        |   
| example   |   
| of        |   
| a         |   
| WordPress |   
| page      |   
|           |   
| you       |  
| could     |   
| edit      |   
| this      |   
| to        |   
+-----------+   
14 rows in set (0.00 sec)    

Vient après l'opération de calcule des occurrences :

mysql> SELECT wrd, CAST((LENGTH(@post_content)-LENGTH(REPLACE(@post_content,wrd,'')))/LENGTH(wrd) AS UNSIGNED ) AS Occurrences
-> FROM  (
->     select substring_index(substring_index(@post_content,' ',iter.cp),' ',-1) wrd
->     from  (select id cp from intcp) as iter
->     where iter.cp <= (length(@post_content)-length(replace(@post_content,' ','')))+1     -> ) from_alias
-> WHERE LENGTH(wrd) > 2
-> ORDER BY Occurrences DESC
-> limit 10 ;
+-----------+-------------+
| wrd       | Occurrences |
+-----------+-------------+
| you       |           6 |
| page      |           3 |
| WordPress |           2 |
| this      |           2 |
| This      |           1 |
| example   |           1 |
| could     |           1 |
| edit      |           1 |
+-----------+-------------+
8 rows in set (0.02 sec)

Pour ceux qui souhaitent filtrer les mots couramment utilisées, ils peuvent utiliser un dictionnaire StopWord comme suit :

mysql> SELECT wrd, CAST((LENGTH(@post_content)-LENGTH(REPLACE(@post_content,wrd,'')))/LENGTH(wrd) AS UNSIGNED ) AS Occurrences
-> FROM  (
->     select substring_index(substring_index(@post_content,' ',iter.cp),' ',-1) wrd
->     from  (select id cp from intcp) as iter
->     where iter.cp <= (length(@post_content)-length(replace(@post_content,' ','')))+1     -> ) from_alias
-> WHERE LENGTH(wrd) > 2
-> AND wrd NOT IN ("this", "you", "and",  "another", "any", "anyhow", "anyone", "anything", "anywhere",  "are",  "aren't",  "around")
-> ORDER BY Occurrences DESC
-> limit 10 ;
+-----------+-------------+
| wrd       | Occurrences |
+-----------+-------------+
| page      |           3 |
| WordPress |           2 |
| example   |           1 |
| could     |           1 |
| edit      |           1 |
+-----------+-------------+
5 rows in set (0.03 sec)

Finalement passons à l'étape de génération du code HTML :
(Pour plus de lisiblité, on mettera l'URL vers les Tags en une variable nommée @link)

mysql> SET @link = "http://www.exemple.com/tags/";
+----------------------------------------+
| @link = "http://www.exemple.com/tags/" |
+----------------------------------------+
| http://www.exemple.com/tags/           |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT  wrd,
->         CAST((LENGTH(@post_content)-LENGTH(REPLACE(@post_content,wrd,'')))/LENGTH(wrd) AS UNSIGNED ) AS Occurrences,
->         CONCAT('<a href=', @link , wrd, ' style="font-size:', CAST((LENGTH(@post_content)-LENGTH(REPLACE(@post_content,wrd,'')))/LENGTH(wrd) AS UNSIGNED ) + 100 , '%;"> ', wrd, '</a> ') as url
-> FROM  (
->     select substring_index(substring_index(@post_content,' ',iter.cp),' ',-1) wrd
->     from  (select id cp from intcp) as iter
->     where iter.cp <= (length(@post_content)-length(replace(@post_content,' ','')))+1     -> ) from_alias
-> WHERE LENGTH(wrd) > 2
-> AND wrd NOT IN ("this", "you", "and",  "another", "any", "anyhow", "anyone", "anything", "anywhere",  "are",  "aren't",  "around")
-> ORDER BY Occurrences DESC
-> limit 10 ;
+-----------+-------------+-------------------------------------------------------------------------------------+
| wrd       | Occurrences | url                                                                                 |
+-----------+-------------+-------------------------------------------------------------------------------------+
| page      |           3 | <a href=http://www.exemple.com/tags/page style="font-size:103%;">page</a>            |
| WordPress |           2 | <a href=http://www.exemple.com/tags/WordPress style="font-size:102%;">WordPress</a>  |
| example   |           1 | <a href=http://www.exemple.com/tags/example style="font-size:101%;">example</a>      |
| could     |           1 | <a href=http://www.exemple.com/tags/could style="font-size:101%;">could</a>          |
| edit      |           1 | <a href=http://www.exemple.com/tags/edit style="font-size:101%;">edit</a>            |
+-----------+-------------+-------------------------------------------------------------------------------------+
5 rows in set (0.03 sec)

Notre code a été généré en (0.03 sec),
mais reste a améliorer la pertinence de font-size :)

ALICE AU CORE DE MySQL-5.1 :PART2: L'Optimiseur | The Optimizer

Dans la documentation officielle de MySQL on trouve dans le chapitre "7.2.4. Comment MySQL optimise les clauses WHERE" que "Cette section est incomplète. MySQL fait de très nombreuses optimisations, et nous n'avons pas eu le temps de toutes les documenter.". Alors on va la reprendre les posts qui viennent, dans l'objectif de mettre un point sur les inconvénients d'une requête mal écrite ou très complexes pour MySQL. Par ce que, même si le temps de l'évaluation de la condition "WHERE" est relativement négligeable, il semble qu'il est très utile de le supprimer!

L'optimisation des requêtes SELECT se fait dans le fichier sql_select.cc et certaines des optimisations effectuées par MYSQL sont :

• Si une dans une requête, un champ "field = constante", alors elle change toute les références de "field" à "constante" : "a=b AND b=c AND c=1" devient "b=1 AND a=1 AND c=1"
• Suppression des conditions constantes ou inutiles : ( 1 = 1, b = b, ..).
• Suppression des conditions impossibles : "item IS NULL" alors que "item" ne s'annule jamais.
• Retourne "Impossible WHERE" ou MySQL détecte rapidement les commandes SELECT impossibles (WHERE 1 = 2) et ne retourne aucune ligne.
• Suppression des parentheses inutiles: ((a AND b) AND c OR (((a AND b) AND (c AND d)))).
• Retourne le COUNT(*) sans "WHERE" d'une table simple directement depuis les informations de la table.
• etc..
  

Mais le plus important à savoir, reste comment MySQL optimise ce genre de requêtes afin de pouvoir en déduire les meilleures façons d'écrire nos requêtes et plus généralement la meilleure façon de concevoir ses bases de données.

Une balade dans le fichier sql_select.cc fera l'affaire :

Une définition étroite de l'OPTIMISEUR : est l'ensemble de routines qui décident quel methode d'exécution le SGBD devrait prendre pour les requetes. MySQL change ces routines fréquemment, ainsi vous devriez comparer ce qui est dit ici avec ce qu'est dans le code source. Pour faciliter les choses, cette description inclut des notes se rapportant à la routine appropriée, par exemple "/sql/select_cc, optimize_cond()". Quand une reqeute est changée en une autre reqeute qui fournit le même résultat, c'est la TRANSFORMATION. La plupart des transformations sont moins évidentes, mais quelques unes peuvent rendre l'exécution plus rapide.

Par exemple, MySQL peut changer :

 SELECT ... WHERE 5 = a

en

 SELECT ...WHERE a = 5

Voici le diagramme montrant la structure de code du handle_select() dans /sql/sql_select.cc, le code du serveur qui manipule les requetes :

   handle_select()
   mysql_select()
     JOIN::prepare()
       setup_fields()
   
     //{{{ L'optimisation commence ici ... 
     JOIN::optimize()        
       optimize_cond()
       opt_sum_query()
       make_join_statistics()
         get_quick_record_count()
         choose_plan()
           /* Trouve la meilleur fcon d'acces aux tables spécifié par l'utilisateur  */
           optimize_straight_join()
             best_access_path()
           /* Trouvez les plans les plus optimaux parmi le sous-ensemble de tous les 
               combinaisons possibles des requetes                                    */
           greedy_search()
             best_extension_by_limited_search()
               best_access_path()
           /* Exécutez une recherche approfondie du plan optimal */
           find_best()
       make_join_select()
     // }}} L'optimisation s'arrete ici
 
     JOIN::exec()

• Bien que le mot "JOIN" apparaisse, ses routines d'optimiseur sont les memes pour pour toutes les requetes.
• L'identation dans l'expositions du diagramme la methodes d'appel. Ainsi vous pouvez voir que handle_select() appelle mysql_select() qui appelle JOIN::prepare() qui appelle le setup_fields(), et ainsi de suite.
• La première partie de mysql_select() est JOIN::prepare() pour l'analyse du contexte, l'identification des metadata, et la transformations de quelques sous requetes.
• L'optimiseur est JOIN::optimize() et toutes les routines subalternes.
• Quand l'optimiseur finit son travail, JOIN::exec() execute ce que JOIN::optimize() vient de décidé .
• Les routines optimize_cond() et opt_sum_query() font les transformations.
  
• La routine make_join_statistics() remonte toute les informations qu'elle trouve au sujet des index qui pourraient être utiles pour accéder aux tables de la requete.

To be continued...

ALICE AU CORE DE MySQL

Avez vous jamais mis les pieds dans le code du MySQL ?

Moi je trouve que c'est très amusent même. Premièrement, par curiosité parce que en tant que développeur je me demandent toujours a quoi ressemble le core d'un SGBD pareil; deuxièmement et du point de vue technique, la simple lecture du code n'est pas inutile ! Pas de tout!

En fait, MySQL est programmé en C++, qui est un langage de programmation super puissant et trop rapide du coté exécution. Et la Rapidité, Puissance, Stabilité, sont les choses qui hérite MySQL du sont ancêtre. En plus, le C++ est l'un des langages de programmation les plus populaires dans l'industrie informatique et par suite le plus connu chez les développeurs du monde entier. La chose qui rend une balade dans le core de MySQL, loin d'être réservée a une bande d'élites!.

En lisant la documentation des dev (disponible ici[en]) ou le code source, on peut profiter de deux choses :

1. On peut chopper pas mal de trucs signalés par les développeurs de MySQL dans les fichiers sources /* en tant que notes dans les commentaires */ et qui n'existent pas dans le manuel de d'utilisateur final.


2. En lisant les algorithmes utilisés par MySQL, on pourra bien comprendre comment MySQL gère et répond à nos requêtes, et par suite comment les améliorer, les meilleus choses a faire, les raccourcis a prendre, etc...

Le code source de MySQL est disponible pour ceux souhaitant et c'est par ici : http://www.mysql.com

En passant par la

Et en arrivant jusqu'a la

La version "Tarball (in 5.1 both Unix & Windows, tar.gz)" contient le répertoire mysql-5.0.24a par ses 91,4 Mo, 5093 fichiers et 304 répertoires.

Faite vous plaisir ...

Chaines de caractères Kung Fu, en MySQL. Round 2

On reprend le meme sujet que Chaines de caractères Kung Fu, en MySQL. Round 1, ou on a la table clients qui suit :

mysql> DESC clients;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(11)      | NO   | PRI |         |       |
| nomComplet | varchar(150) | NO   | MUL |         |       |
| adresse    | varchar(255) | YES  | MUL | NULL    |       |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> SELECT * FROM clients;
+--------+-----------------+----------------------+
| id     | nomComplet      | adresse              |
+--------+-----------------+----------------------+
|   1001 |Mr. SMITH ALLEN  | PARIS 100100 FRANCE  |
|   1002 |Mr. ALLEN JONES  | NICE 200410 FRNACE   |
|   1003 |Ml. WARD BLAKE   | BOSTON 546621 USA    |
|   1004 |Mm. BLAKE SMITH  | PARIS 100100 FRANCE  |
|   1005 |Ml. MOUNA JONES  | N.R                  |
|   1006 |Mr. JONES MARTIN | PARIS 100100 FRANCE  |
+--------+-----------------+----------------------+
6 row in set (0.1 sec)

Mettre en ordre la table clients par la civilité en ordre croissant, le nom en ordre decroissant et le prenom en ordre croissant :

( Il fallait dire que pour avoir la table clients triée selon civilité dans le champ nomComplet, il nous suffit d'employer seulement 'ORDER BY nomComplet', mais c'est pas la même chose maintenant !! )

- La colonne nomComplet ordonnée selon la civilité :

mysql> SELECT nomComplet , SUBSTRING_INDEX(nomComplet,'. ',1)  FROM clients ORDER BY SUBSTRING_INDEX(nomComplet,'. ',1)  ASC;
+------------------+------------------------------------+
| nomComplet       | SUBSTRING_INDEX(nomComplet,'. ',1) |
+------------------+------------------------------------+
| Ml. WARD BLAKE   | Ml                                 |
| Ml. MARTIN JONES | Ml                                 |
| Mm. BLAKE SMITH  | Mm                                 |
| Mr. SMITH ALLEN  | Mr                                 |
| Mr. ALLEN JONES  | Mr                                 |
| Mr. JONES MARTIN | Mr                                 |
+------------------+------------------------------------+
6 rows in set (0.00 sec)

- La colonne nomComplet ordonnée selon le nom :

mysql> SELECT nomComplet , SUBSTRING_INDEX(nomComplet,'. ',-1) FROM clients ORDER BY SUBSTRING_INDEX(nomComplet,'. ',-1) DESC;
+------------------+-------------------------------------+
| nomComplet       | SUBSTRING_INDEX(nomComplet,'. ',-1) |
+------------------+-------------------------------------+
| Ml. WARD BLAKE   | WARD BLAKE                          |
| Mr. SMITH ALLEN  | SMITH ALLEN                         |
| Ml. MARTIN JONES | MARTIN JONES                        |
| Mr. JONES MARTIN | JONES MARTIN                        |
| Mm. BLAKE SMITH  | BLAKE SMITH                         |
| Mr. ALLEN JONES  | ALLEN JONES                         |
+------------------+-------------------------------------+
6 rows in set (0.00 sec)

- La colonne nomComplet ordonnée selon le prénom :

mysql> SELECT nomComplet , SUBSTRING_INDEX(nomComplet,' ',-1)  FROM clients ORDER BY SUBSTRING_INDEX(nomComplet,' ',-1)  ASC;
+------------------+------------------------------------+
| nomComplet       | SUBSTRING_INDEX(nomComplet,' ',-1) |
+------------------+------------------------------------+
| Mr. SMITH ALLEN  | ALLEN                              |
| Ml. WARD BLAKE   | BLAKE                              |
| Mr. ALLEN JONES  | JONES                              |
| Ml. MARTIN JONES | JONES                              |
| Mr. JONES MARTIN | MARTIN                             |
| Mm. BLAKE SMITH  | SMITH                              |
+------------------+------------------------------------+
6 rows in set (0.00 sec)

-> La combinaison des trois requêtes nous donne :

mysql> SELECT
 ->     nomComplet ,
 ->     SUBSTRING_INDEX(nomComplet,'. ',1)  ,
 ->     SUBSTRING_INDEX(nomComplet,'. ',-1) ,
 ->     SUBSTRING_INDEX(nomComplet,' ',-1)
 -> FROM
 ->     clients
 -> ORDER BY
 ->     SUBSTRING_INDEX(nomComplet,'. ',1)  ASC  ,
 ->     SUBSTRING_INDEX(nomComplet,'. ',-1) DESC ,
 ->     SUBSTRING_INDEX(nomComplet,' ',-1)  ASC  ;
+------------------+------------------------------------+-------------------------------------+------------------------------------+
| nomComplet       | SUBSTRING_INDEX(nomComplet,'. ',1) | SUBSTRING_INDEX(nomComplet,'. ',-1) | SUBSTRING_INDEX(nomComplet,' ',-1) |
+------------------+------------------------------------+-------------------------------------+------------------------------------+
| Ml. WARD BLAKE   | Ml                                 | WARD BLAKE                          | BLAKE                              |
| Ml. MARTIN JONES | Ml                                 | MARTIN JONES                        | JONES                              |
| Mm. BLAKE SMITH  | Mm                                 | BLAKE SMITH                         | SMITH                              |
| Mr. SMITH ALLEN  | Mr                                 | SMITH ALLEN                         | ALLEN                              |
| Mr. JONES MARTIN | Mr                                 | JONES MARTIN                        | MARTIN                             |
| Mr. ALLEN JONES  | Mr                                 | ALLEN JONES                         | JONES                              |
+------------------+------------------------------------+-------------------------------------+------------------------------------+

Et finalement notre requete est comme suit :

mysql> SELECT
 ->     nomComplet
 -> FROM
 ->     clients
 -> ORDER BY
 ->     SUBSTRING_INDEX(nomComplet,'. ',1)  ASC  ,
 ->     SUBSTRING_INDEX(nomComplet,'. ',-1) DESC ,
 ->     SUBSTRING_INDEX(nomComplet,' ',-1)  ASC  ;
+------------------+
| nomComplet       |
+------------------+
| Ml. WARD BLAKE   |
| Ml. MARTIN JONES |
| Mm. BLAKE SMITH  |
| Mr. SMITH ALLEN  |
| Mr. JONES MARTIN |
| Mr. ALLEN JONES  |
+------------------+
6 rows in set (0.00 sec)

Un autre challange, est de mettre en ordre la table clients suivant le code zip :
On sait que :

• SUBSTRING_INDEX(str,delim,count) : Retourne une portion de la chaine de caracteres str, située avant count occurrences du delimiteur delim.

• CAST(expr AS type) peuvent etre utilisees pour convertir une donnee d'un type en un autre ET le plus beau en CAST(expr AS UNSIGNED) est qu'elle nous retourne un UNSIGNED strictement superieur à zero si la chaine commence par un nombre ! Et ce UNSIGNED retourné n'est que notre nombre dont commence la chaine!

mysql> select cast('abc123 zae5' as UNSIGNED);
+---------------------------------+
| cast('abc123 zae5' as UNSIGNED) |
+---------------------------------+
|                               0 |
+---------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select cast('123 zae5' as UNSIGNED);
+------------------------------+
| cast('123 zae5' as UNSIGNED) |
+------------------------------+
|                          123 |
+------------------------------+
1 row in set, 1 warning (0.00 sec)

• Une solution possible est alors de combiner CAST avec SUBSTRING_INDEX !

mysql> SELECT
 ->     adresse ,
 ->     CAST(
 ->         SUBSTRING_INDEX(adresse,' ',-2) AS UNSIGNED
 ->     )
 -> FROM
 ->     clients
 -> ORDER BY
 ->     CAST(SUBSTRING_INDEX(adresse,' ',-2) AS UNSIGNED) DESC;
+---------------------+--------------------------------------------------+
| adresse             | CAST(SUBSTRING_INDEX(adresse,' ',-2) AS UNSIGNED)|
+---------------------+--------------------------------------------------+
| BOSTON 546621 USA   |                                           546621 |
| NICE 200410 FRNACE  |                                           200410 |
| PARIS 100100 FRANCE |                                           100100 |
| PARIS 100100 FRANCE |                                           100100 |
| PARIS 100100 FRANCE |                                           100100 |
| N.R                 |                                                0 |
+---------------------+--------------------------------------------------+
6 rows in set, 12 warnings (0.00 sec)

MySQL,..