Validate Sql code with .net and git-hook

Hello Habr!

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

It so happened historically that all the sql code before going into production (i.e., it will be executed on the main database) is stored in our GIT repository, where it gets directly from the developers (naturally after Code Review). So, the idea came up to add git-hook in this repository that would validate the sql code and if it is not valid then the commit would be returned to the developer for revision. A little hard to imagine, easier to draw:

Git hook

So, for starters, I decided to dig towards git-hooks in order to figure out how to cancel the commit anyway (if necessary). In short, what is git-hook: this is a kind of bash-script that is executed whenever a certain event occurs related to a change in the state of the repository. There are two types of such scripts: client and server. Yes, and events, like scripts, there are many. More details can be found here .
For our purposes, git-hook “update” (server-side script) is the best suited. It starts exactly before updating the state of the branch (s) on the server, in fact, before your changes sent to the server are applied. And, importantly, if the exit code of such a script is non-zero, the changes are discarded. The script has three input parameters:

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

Having such a set of parameters, for starters, you can 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 list of files, you need to get to the content of each file using the git-show command. And having the content itself, you can try to check it. The following is a listing of the git-hook update script, as well as two helper 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 to an external resource to check the 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>" '{match($0,re,RMATCH); print RMATCH[1]}'

And after the soap-template script, which sql-code draws up a soap request:

#!/bin/sh
encodedFile=$(base64 $1)
echo '' >$2
echo '' >> $2
echo '  ' >>$2
echo '    ' >>$2
echo "      $encodedFile" >>$2
echo '    ' >>$2
echo '  ' >>$2
echo '' >>$2

So, I am not so strong in bash scripts to do a sql code check on them; therefore, I decided to put it into my favorite .Net environment.

.Net part

With the communication bridge between git-hook and .Net, I chose .net web service, therefore it is the “check-sql” script in our git-hook script that makes a request to this service with a parameter that contains the contents of the sql file:

[WebMethod]
public string CheckFile(string base64)
{
    var errors = new List();
    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 come up with a lot of things. I created a small framework for such validation, but I think it’s not very appropriate to list all of 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 Unity Container, we can control the set of rules applicable to validation in general. Also, having a sql parser ( TSql100Parser ) built into .net, you can implement 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 benefits, but having a potentially large margin for expanding capabilities, we can confidently say that the benefits of this will be much more than harm.
In conclusion, I bring the console log when working with this system:

$ 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://[email protected]/git/test.git
 ! [remote rejected] master -> master (hook declined)
error: failed to push some refs to 'ssh://[email protected]/git/test.git'

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

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