Extraire les "CREATE TABLE" d'un dump SQL

oici un petit exercice d'application des utilitaires sed, awk et tr. L'exercice est de créer de réaliser un inventaire des colonnes d'une base de données mySQL, à partir du fichier de requête SQL, que l'on obtiens dans par PHPmyAdmin, et qui contient toutes les requêtes SQL pour reconstruire la base de données (avec ou sans les données). Voir en annexe, un exemple de ce type de fichier.
Fichier CSV à obtenir (exploitable ensuite dans excel par exemple):
Table 1; colonne 1, type
Table 1; colonne 2, type
...
Table 2; .....
......

Le but de l'exercice est donc de transformer ce fichier de requête SQL en fichier de type csv contenant au moins 3 colonnes: nom_table, nom_colonne, type_colonne. Il est clair que beaucoup de "boite à outils" mySQL peuvent le faire à peu de frais, mais ma première motivation est de m'exercer aux utilitaires sed, awk et tr.

La méthode pour y parvenir consiste à réaliser cette transformation par étapes successives. Pour cela on utilise 3 utilitaires: sed, tr, awk:

• sed est parfait pour effectuer des filtres ligne par ligne
• tr est idéal pour les remplacements ou les suppressions de caractère
• awk est efficace pour les traitements impliquant des fichiers de type csv (suite de ligne contenant des champs délimités par un caractère séparateur).

Pour effectuer un traitement complexe, le plus simple est de décomposer ce traitement en plusieurs élémentaires. Chaque traitements élémentaires permet de réaliser des tests et d'effectuer un travail progressif beaucoup plus agréable. Cela facilite également le travail de correction des erreurs.

Voici la liste des traitements élémentaires à réaliser à partir du fichier SQL:

1. Filtrer toutes les lignes parasites (en dehors des requêtes SQL)
2. Supprimer les retours à la ligne, sauf ceux en fin de requête SQL
   (cela permet d'avoir une requête SQL complète par ligne, et une seule)
3. Filtrer les requêtes autre que CREATE TABLE
4. Préparer chaque CREATE TABLE pour délimiter les 3 informations
   (avec par exemple des ;)
5. Transposer chaque ligne en autant de lignes sur la base d'une par colonne

Voici donc la liste des commandes en ligne élémentaires permettant de réaliser chacune de ces taches:

Supprimer les lignes commençant par "/*" et avec plus loin "*/". Voici la commande:
sed '/^\/\*.*\*\//d'

Suppression des lignes débutant par "--":
sed '/^--/d'

Suppression des lignes blanches ou ne contenant que des caractères blancs: sed '/^[:blank:]*$/d'

Suppression des retours à la lignes pour les fichiers de type DOS et linux.
tr "\n\r" "  "

Remplacement des ; (signifiant la fin d'une requête SQL) par un retour à la ligne. Cette commande est à faire seulement après la précédente. On aurait pu imaginer réaliser ces deux commandes "tr" en une seule fois, mais il y aurait un risque de comportement imprévisible concernant le "\n". A noter que le résultat sera un fichier ligne (texte) compatible linux (cad sans le caractère CR pour une fin de ligne):
tr ";" "\n"

Suppression des espaces en début de lignes:
sed 's/^[ ]*//'

Filtrer (supprimer) les requêtes SQL autre que CREATE TABLE:
sed -n '/^CREATE TABLE/p'

On extrait de chaque CREATE TABLE le nom de la table, et la liste des colonnes que l'on repère par des parenthèses qui son en début et fin de requête.
On sépare ces deux éléments par un point virgules pour faciliter l'extraction des colonnes CSV par la suite.
"CREATE TABLE nom_table ( liste_colonne) "
=> (sed)
 "nom_table;liste_colonne
Cela donne la ligne:
sed 's/^[^()]* \([^() ]*\) (\(.*\))[^()]*$/\1;\2/'

On supprime ensuite les virgules parasites pouvant se trouver dans la liste des colonnes, par exemple dans le type de colonne et que l'on repère à l'intérieur d'une chaîne entourée par les caractères "(" et ")" . Le but est de pouvoir ensuite remplacer les virgules de la liste des colonnes par des point-virgules pour permettre un traitement aisé par awk.
Par exemple un type de colonne  FLOAT[(length,decimals)]
contient une virgule parasite (en dehors des virgules séparant une colonne) qui sera remplacé par un espace:
sed 's/(\([^(),]*\),\([^(),]*\))/(\1 \2)/g'

Il reste pour en finir avec les virgules parasites, éliminer les virgules à l'intérieur des déclarations INDEX et KEY, qui peuvent contenir de nombreuses virgules. Pour simplifier, on supprime tout simplement les groupes de parenthèses ouvrantes/fermantes contenant au moins une virgule:
sed 's/([^()]*,[^()]*)//g'


Pour remplacer les caractères virgules par des point-virgules:
tr "," ";"


La dernière étape est de transposer chaque lignes en autant de ligne que de colonnes sur le schéma suivant, avec une ligne de départ:
"A;B;C;D;E;......" :
=>(awk)
A;B;
A;C;
A;D;
A;E;
.....
Voici la ligne de commande correspondante:
awk 'BEGIN { FS= ";"; OFS="\n" }
{ for (i=2;i<=NF;i++) {print $1 ";" $i } }'


Un peu de toilettage pour finir en supprimant les espaces répétitifs:
sed 's/[ \t][ \t]*/ /g'

Ajout d'un ; après le nom de la colonne qui est entouré par des espaces:
sed 's/^\([^ ]* [^ ]*\) \(.*\)$/\1;\2/'

Et pour finaliser, suppression des lignes INDEX et KEY:
sed -e '/;PRIMARY;/d' -e '/;KEY;/d'

Pour finir, il suffit de mettre toutes ces lignes de commande dans un script bash. Ce script aurait pu se résumer à une longue succession de tubes ("|") avec en entrée le fichier source SQL, mais j'ai préféré conserver 1 fichier intermédiaire contenant les requêtes SQL "CREATE TABLE" seules et complètes dans chaque ligne. Ce script a besoin en entrée du nom du fichier source.  Deux fichiers sont crée ensuite par la procédure: un contenant seulement les CREATE TABLE; puis le fichier CSV final contenant l'inventaire des noms de colonnes de chaque table.

Cela donne un bricolage relativement indigeste mais pouvant servir par la suite d'une base pour un travail équivalent. Rien ne vaut le bricolage pour apprendre le métier tout en s'amusant.


Liens:
Fichier script correspondant en UTF8 pour bash
Introduction à awk d'une grande clarté
Introduction idéale à bash

Exemple de fichier de requête SQL:


-- http://www.phpmyadmin.net
--
-- Client: localhost
-- Généré le : Lun 06 Juillet 2015 à 18:04
-- Version du serveur: 5.5.16
-- Version de PHP: 5.3.8
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Base de données: `45tours`
--
-- --------------------------------------------------------
--
-- Structure de la table `artiste`
--
CREATE TABLE IF NOT EXISTS `artiste` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
.....