Run SQL files inside subdirectories on Linux

Question

I have an SQL file repository where each one represents a stored Procedure. These files are inside a directory organized in other subdirectories.

Example of my directory structure:

stored-procedures
---d1
------sd1
---------sp1.sql
---------sp2.sql
---------sp3.sql
------sd2
---------sp4.sql
---------sp5.sql
------sd3
---d2
------sd4
---------sp6.sql
---d3
------sd5
------sd6

The command I use to run each stored-Procedure separately is as follows::

mysql -uuser -psenha bd < sp1.sql --force

How could I do this on Linux via the command line or creating a script?

Answer 1

I was able to build my own script as follows:

#!/bin/bash
shopt -s globstar
echo '...'

if [ $# -lt 2 ]
then
    echo "Favor definir dois parâmetros para execução deste script"
    echo "Primeiro parâmetro:"
    echo "      nome do usuário do banco de dados"
    echo "Segundo parâmetro:"
    echo "      senha do usuário do banco de dados"
    echo "Execução interrompida"
    echo '...'
    exit 1
fi

echo "Iniciando a execução das stored-procedures no diretório /var/www/stored-procedures"
for file in /var/www/stored-procedures/**/*.sql
do
    echo $file
    mysql -u$1 -p$2 banco_de_dados < $file --force  
done
echo "Execução do script finalizada"
echo '...'

Note 1: As a parameter the user must pass the user and the password of the database containing the stored procedures.

Note 2: The directory used was the /var/www/stored-procedures but just change this code chunk to your directory that contains the stored-procedures.

Answer 2

Assuming your directory tree is something like:

$ tree stored-procedures/
stored-procedures/
|-- d1
|   |-- sd1
|   |   |-- sp1.sql
|   |   |-- sp2.sql
|   |   `-- sp3.sql
|   |-- sd2
|   |   |-- sp4.sql
|   |   `-- sp5.sql
|   `-- sd3
|-- d2
|   `-- sd4
|       `-- sp6.sql
`-- d3
    |-- sd5
    `-- sd6

15 directories, 0 files

You can use the utility find to solve your problem:

$ find ./stored-procedures/ -name "*.sql" -exec mysql -uuser -psenha bd -e \"source {}\" \;