[new:30/06/2011]On parle tellement de technologies avancées comme Entity Framework qu’on en oublie parfois qu’au bout de la chaine ce bon vieux SQL existe toujours et que plus souvent qu’on le croit il faut en écrire, voire en mettre en forme, et plus difficile encore, en parser. Mais parser SQL est une tâche très difficile. Sauf si on ruse un peu...
VSTS DB
Avant de parler de repas gratuit, il faut préciser que le restaurant en question est réservé aux possesseurs de Visual Studio en version Database ou autre release un cran au-dessus de la version de base. Car c’est dans les entrailles des DLL de VTST/DB que nous irons chercher quelques perles rares qui ne se dévoilent qu’aux fouineurs.
T-SQL
Encore une petite réserve : cela fonctionne avec Transact-SQL, donc le SQL de SQL Server. Toutefois le parseur et le beautifier acceptent les versions 80, 90, 100 et Azures. On sait aussi que le SQL de Jet ou mieux celui de SQL Server Compact 4.0 sont très proches de T-SQL. Cela fait finalement pas mal de bases de données cibles. On Peut supposer aussi que pour peu qu’on reste assez standard on doit pouvoir faire passer du SQL d’autres bases, mais sans garantie.
Un circuit fermé
Pour ses besoins internes VSTS a besoin de parser et de présenter du SQL. Il existe donc un parseur qui prend du T-SQL en entrée et qui produit une analyse sous la forme de “fragments de script” et un beautifier ou plutôt un formateur de SQL qui prend en entrée des “fragments de script” pour produire un code SQL bien indenté.
On a ainsi une boucle T-SQL => Parser => fragments => Script generator => T-SQL
... Mais ouvert
Microsoft a eu la bonne idée de mettre en publique les classes en question, et mieux, de les placer dans deux DLL qui, si on lit le REDIST.TXT, sont tout à fait diffusables, pour peu, bien entendu que vous possédiez la bonne version de VSTS.
Il est donc, sous cette réserve, tout à fait possible d’utiliser ces DLL dans vos applications et de les diffuser avec vos exécutables.
Quelques exemples
Pour mieux comprendre, rien ne vaut un exemple. L’application qui suit est très simple. Elle possède deux TextBox, la première qui accepte du T-SQL écrit comme on le veut, et la seconde qui produira le résultat de la double opération “parser / formater” et qui se comportera donc en SQL beautifier (lorsqu’on clique sur le bouton “Format”).
script de création de table
On voit ici un bout de script de création d’une table très simple avec sa description de la table. Le résultat, dans la partie inférieure, est formaté, les identificateurs sont en Pascal casing. Il s’agit d’une option pouvant être modifiée (dans le code, j’ai créé une application vraiment simple, sans page de paramètres).
Vérification de la syntaxe
Plus fort, sans les mains, la vérification de syntaxe avec message d’erreur en clair :
...Et oui, il manque la virgule après la définition du paramètre BUSINESSID. Nous sommes ici dans la création d’une procédure stockée pour les distraits qui ne l’aurait pas remarqué... C’est dire la richesse de l’outil qui sait reconnaitre tous les types de requêtes et les scripts !
Comment ça marche ?
C’est assez simple, il suffit d’ajouter au projet les deux références suivantes :
Je n’entrerai pas dans le détail de ces DLL. Ce qui nous intéresse ici se résume à deux méthodes et quelques classes comme celle des options du formateur.
Pour simplifier encore plus j’ai créé une petite classe statique TSqlHelper qui regroupe tout ce qui est nécessaire. De fait, le code de l’application, sur le clic du bouton Format se résume à cela :
private void button1_Click(object sender, RoutedEventArgs e)
{
tScript.Text = "";
string[] errors;
var scriptFragment = TSqlHelper.Parse(tSql.Text, SqlVersion.Sql100, true, out errors);
if (errors!=null)
{
foreach (var error in errors)
{
tScript.Text += error + Environment.NewLine;
return;
}
}
var options = new SqlScriptGeneratorOptions
{
SqlVersion = SqlVersion.Sql100,
KeywordCasing = KeywordCasing.PascalCase
};
tScript.Text = TSqlHelper.ToScript(scriptFragment, options);
}
tScript est la TextBox résultat, TSql est la TextBox d’entrée.
On commence par demander les fragments de script en appelant TSqlHelper.Parse() à qui on passe en paramètre le texte SQL original ainsi que la version de T-SQL qu’on souhaite gérer et un paramètre booléen précisant si les identificateurs sont placés entre quottes ou non. On récupère la liste des erreurs éventuelles.
S’il y a des erreurs on les affiches, s’il n’y en a pas on passe les fragments reçus à TSqlHelper.ToScript(), fragments auxquels on ajoute les options. Ici je n’ai défini que deux options, la version de T-SQL et le casing (PascalCase). On peut modifier les indentations et d’autres paramètres que je vous laisse découvrir.
Mais alors, que fait la classe TSqlHelper ?
TSqlHelper
Vous retrouverez le source complet de cette classe avec le projet exemple à télécharger en fin d’article, je ne vais donc pas publier ici l’intégralité de ce code. Prenons plutôt un extrait intéressant comme l’appel du parseur :
public static IScriptFragment Parse(string sql, SqlVersion level,
bool quotedIndentifiers, out string[] errors)
{
errors = null;
if (string.IsNullOrWhiteSpace(sql)) return null;
sql = sql.Trim();
IScriptFragment scriptFragment;
IList<ParseError> errorlist;
using (var sr = new StringReader(sql))
{
scriptFragment = GetParser(level, quotedIndentifiers).Parse(sr, out errorlist);
}
if (errorlist != null && errorlist.Count > 0)
{
errors = errorlist.Select(e => string.Format("Column {0}, Identifier {1}, Line {2}, Offset {3}",
e.Column, e.Identifier, e.Line, e.Offset) +
Environment.NewLine + e.Message).ToArray();
return null;
}
return scriptFragment;
}
On voit qu’il n’y a pas beaucoup de travail à fournir pour un job si complexe ! On obtient un parseur par GetParser qui est une factory retournant tout simplement l’instance du bon parseur correspondant à la version de T-SQL qu’on souhaite analyser. Et une fois cette instance obtenue, il suffit d’appeler “Parse()” avec, en paramètres, un stream (en lecture sur la chaine de caractère du SQL original) et une liste pour les erreurs éventuelles.
Le reste n’est qu’enrobage.
La méthode ToScript n’est guère plus complexe, elle utilise le résultat de Parse() et quelques options pour produire le code SQL formaté.
Analyser les fragments
Bien entendu fabriquer un petit “sql beautifier” est bien sympathique mais cela ne semble pas tellement être du parsing. En fait si. D’abord nous avons vu que le code passé à l’application est réellement analysé puisque les erreurs de syntaxe sont trouvées et affichées. Valider une requête SQL peut s’avérer essentiel dans certains contexte et si on peut savoir qu’une requête contient des erreurs avant de l’envoyer au serveur, et mieux, de savoir où sont les erreurs, c’est déjà énorme.
Mais il reste à réellement analyser le code SQL, c’est à dire à parcourir l’arbre des “fragments” produit par Parse().
Ma petite application de démonstration ne pousse pas les choses à ce point là car, étant donné le nombre des types de requêtes possibles, des versions de T-SQL acceptées et la complexité que peut atteindre un script complet ou même une procédure stockée, c’est un projet d’une ampleur très différente qu’il aurait fallu écrire.
Pour regarder l’arbre des fragments, il suffit de placer un point d’arrêt dans le code de TSqlHelper.Parse() puis de s’aider de VS pour parcourir les fragments. Au premier coup d’œil ce n’est pas évident, mais on retrouve bien l’ensemble des informations nécessaires à une interprétation (ce que fait ToScript() en indentant correctement le code en fonction de sa signification, puisée des fragments).
Conclusion
De la coupe aux lèvres... c’est un fait, entre ma petite démo et un parseur efficace transformant par exemple une requête T-SQL en une requête Oracle ou dans tout autre langage il y a une certaine marge. Mais pour valider une requête c’est immédiat en revanche. Pour mettre en forme du SQL aussi. Pour le reste, disposer du parseur c’est déjà 80% du travail le plus difficile qui est fait.
Le truc étant de savoir que “çà” existe et que cela se trouve dans deux DLL bien cachées mais distribuables... Et ça, ceux qui ne lisent pas Dot.Blog auront plus de mal à trouver l’information :-)
Le code source du projet (VS 2010 / WPF) :
Pour d’autres nouvelles... Stay Tuned !