Thursday, December 13, 2012

Fetching Stored Procedures from Github and running them on MySQL using Shell

With the ubiquity of github in the organization, a lot of teams have started uploading their stored procedures on the service. You will definitely run in to a time when you want to get the latest version of  the stored procedure codebase and commit it to your database.

Here's a nifty shell script that will download all stored procedures from github and pipe the output to mysql. Beware, if you are used to creating/updating stored procedures on client tools like Squirrel/ MySQL workbench, etc. your code will not always be understood my the mysql command line utility. For example the mysql command line utility likes to have a space after the dashes indicating a comment.

--comment = bad
-- comment = good

You might have to make other changes depending upon the version of mysql your are running and your config settings. Here's the script


rm -rf FolderName
git clone git@github.com:path_to_your_repo.git
FILES=Repo_NAME/sproc/*
for f in $FILES
do
  echo "Processing $f file..."
  cp /dev/null latest_sprocs.sql
  echo "DELIMITER @@@" >> latest_sprocs.sql
  cat "$f" >> latest_sprocs.sql
  echo "@@@" >> latest_sprocs.sql
  echo "DELIMITER ;" >> latest_sprocs.sql
  cat latest_sprocs.sql | mysql -uuser -pyour_passoword
done

This will cat the file to mysql one by one and you can see failures as an when they happen

No comments:

Post a Comment