Validating Sql Code with .net and git-hook

Hello Habr!

Most recently, our company held another Hackathon. And within its framework, I wanted to kill time more interestingly to do a useful thing, both for myself and for other developers. The choice fell on a sort of validator sql code, which would check it for different rules that the compiler cannot do, and those that the guys who make Code Review can skip. You can create a lot of such rules, starting from a simple “Add GO at the end of the query” and ending with more complex “Use View instead of Table”. And most importantly, this validator in no way should add time to the developer to use it, i.e. to put it simply, it should validate itself somewhere automatically, regardless of the developer’s actions.

Historically, the entire sql code before going into production (i.e. running on the main database) is saved in our GIT repository, which gets directly from the developers (of course, after Code Review). So, there was an idea to add git-hook in this repository which would validate the sql-code and if it is not valid, then the commit would be returned to the developer for revision. A bit hard to imagine, easier to draw:
')

Git hook

So, for starters, I decided to dig in the direction of git-hooks, to figure out how to still cancel the commit (if necessary). In short, what is git-hook: this is a certain bash-script that is executed whenever a certain specific event occurs associated with a change in the state of the repository. There are two types of such scripts: client and server. And there are a lot of events, as well as scripts. More details can be read here .
The git-hook “update” (server script) is the best for our purposes. It runs right before updating the status of the branch (s) on the server, in fact, before your changes are sent to the server are applied. And, importantly, if the exit code of such a script is non-zero, the changes will be rejected. The script has three incoming parameters:

• The name of the branch that is being updated
• Link to commit before updating branch
• Link to commit after branch update

Having such a set of parameters you can, for a start, get a list of files that have been changed using the second and third parameters of our script:

git diff --name-only --diff-filter=[AMCR] $2 $3 

Having received the file list, you need to reach the content of each file using the git-show command. And having already the content itself, you can try to check it out. Below is the listing of the git-hook update script, as well as two supporting scripts:

 #!/bin/sh ex=0 okResult="OK" originalPath='/git/test.git/' #   git printf "---- 'Sql checker' hook ----" listOfFiles=$(git diff --name-only --diff-filter=[AMCR] $2 $3) #   for changedFile in $listOfFiles do printf "checking:$changedFile" fullFilePath="$3:$changedFile" git-show $fullFilePath >tmp_sql result=$($originalPath/hooks/check-sql tmp_sql) #  if [ "$result" != $okResult ] then res=${result//|/\\n } printf " $res\n" #  ex=1 else printf "ok!\n" fi done printf "---- Done ----" exit $ex 

The check-sql script makes a request for an external resource for checking a file with sql code, and also parses the result obtained in the form of soap :

 #!/bin/sh soap-template $1 tmp_soap wget -qO- --post-file=tmp_soap --header="Content-type: text/xml; charset=utf-8" 127.0.0.1/check-sql.asmx | gawk -v re="<CheckFileResult.*>(.*)/CheckFileResult>" '{match($0,re,RMATCH); print RMATCH[1]}' 

And after the soap-template script, which sql-code draws a query in soap:

 #!/bin/sh encodedFile=$(base64 $1) echo '<?xml version="1.0" encoding="utf-8"?>' >$2 echo '<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSc ma" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">' >> $2 echo ' <soap:Body>' >>$2 echo ' <CheckFile xmlns="http://tempuri.org/">' >>$2 echo " <base64>$encodedFile</base64>" >>$2 echo ' </CheckFile>' >>$2 echo ' </soap:Body>' >>$2 echo '</soap:Envelope>' >>$2 

So, I am not so strong in bash scripts to do a check of the sql code on them, according to this, I decided to take it to my favorite environment .Net.

.Net part

I chose the .net web service as a communication bridge between git-hook and .Net, so the “check-sql” script in our git-hook script requests this service with a parameter that contains the content of the sql file:

 [WebMethod] public string CheckFile(string base64) { var errors = new List<SqlCheckError>(); string result = OkResult; try { //  sql- string sqlScript = Encoding.Default.GetString(Convert.FromBase64String(base64)); //    ISqlCheck var containerProvider = new SqlCheckUnityProvider(); //    var checker = new SqlCheckProcess(containerProvider); errors.AddRange(checker.CheckSql(sqlScript)); } catch (Exception e) { Log(e); // return OkResult; //      ,       . } //  ( )     git-hook if (errors.Count > 0) { result = string.Join("|", errors.Select(error => string.Format("{1}: {0}", error.Message, error.Type))); } return result; } 

Having already .net environment and provided us with sql-code for validation, you can think of a lot of things. I created a small framework for such validation, but I think it’s not very appropriate to bring all its code here. In the near future I will post it on github. In a nutshell, everything is based on such a simple interface:

 public interface ISqlCheck { bool CheckSql(string sqlCode); SqlCheckError GetError(string sqlCode); } 

This interface is a validation rule. Having a lot of implementations of this interface in conjunction with the Unity Container, we can manage the set of rules applicable to validation as a whole. Also, with the sql-parser ( TSql100Parser ) built into the .net, you can implement the verification of almost any rule.

Practice

This simple system has already begun to operate in our company, it is too early to draw conclusions about some enormous benefit, but with a potentially large margin for expanding opportunities, we can confidently say that the benefits from this will be much more than harm.
In conclusion, I cite the console log when this system is running:

 $ git push origin master Counting objects: 7, done. Delta compression using up to 8 threads. Compressing objects: 100% (3/3), done. Writing objects: 100% (4/4), 347 bytes, done. Total 4 (delta 2), reused 0 (delta 0) remote: remote: ---- 'Sql checker' hook ---- remote: remote: checking: test/create_sp.sql remote: Error: Missed 'GO' statement after stored procedure. remote: remote: ---- Done ---- remote: remote: error: hook declined to update refs/heads/master To ssh://user@git.local/git/test.git ! [remote rejected] master -> master (hook declined) error: failed to push some refs to 'ssh://user@git.local/git/test.git' 

As you can see from the penultimate line, the commit was rejected by the system due to the presence of errors in the variable sql-script.

That's all I wanted to tell about this post.
Thanks for attention. Convenient to all development!