we're currently setting up out integration server and during that process we've set up pre-commit hooks on the SVN so that our developers can't check in files that are syntactically invalid (primarily PHP and XML).
We also have a bunch of .sql files (for MySQL) which I'd like to lint as well. Unfortunately, Google didn't turn up anything useful for this task.
Any ideas?
After searching for a CLI tool for syntax linting in Mysql to use in Jenkins and didn't find anything quickly (this Stackoverflow question is one of the first results - LOL) I came up with the following solution (OS: Linux, but should be feasible with Windows too):
Something like the follwoing:
lint_result=`mysql mysql_test -B -f -e 'select asdf s where x;' 2>&1`; if [ `echo $lint_result | sed -r "s/ERROR ([0-9]*).*/\1/g"` -eq 1064 ]; then echo -e "Syntax error:\n${lint_result}"; fi
Syntax error:
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where x' at line 1
(To check sql files you can use "< filename.sql" instead of -b -e 'statement')
If the syntax of the query can not be parsed by mysql it claims: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
Only if the syntax is correct it tries to execute the query and realize that the table don't exists but this isn't interesting anymore:
ERROR 1146 (42S02) at line 1: Table 'mysql_test.s' doesn't exist
Therefor Error 1064 is the invalid syntax. You only need to create an empty test database because otherwise only errors with a wrong FROM part would appear (here for example the database is needed in order to get a valid syntax check result: 'select asdf from s where x and if;).
As far as i tested it works fine (Version Mysql 5.5).
Here a complete bash script vesion:
#!/bin/bash
source_dir=${1};
database="mysql_test";
mysql_args="-h127.0.0.1";
mysql $mysql_args -B -e "DROP DATABASE IF EXISTS $database; CREATE DATABASE $database;";
for file in `find $source_dir -name "*.sql"`; do
lint_result=`mysql $mysql_args $database -f -b < $file 2>&1`;
if [ "`echo $lint_result | sed -r \"s/ERROR ([0-9]*).*/\1/g\"`" = "1064" ]; then
echo -e "Syntax error in file ${file}:\n${lint_result}" && exit 1;
fi;
done