MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Bash Arrays & MySQL

with 2 comments

Student questions are always interesting! They get me to think and to write. The question this time is: “How do I write a Bash Shell script to process multiple MySQL script files?” This post builds the following model (courtesy of MySQL Workbench) by using a bash shell script and MySQL script files, but there’s a disclaimer on this post. It shows both insecure and secure approaches and you should avoid the insecure ones.

LittleERDModel

It seems a quick refresher on how to use arrays in bash shell may be helpful. While it’s essential in a Linux environment, it’s seems not everyone masters the bash shell.

Especially, since I checked my Learning the Bash Shell (2nd Edition) and found a typo on how you handle arrays in the bash shell, and it’s a mistake that could hang newbies up (on page 161). Perhaps I should update my copy because I bought it in 1998. 😉 It was good then, and the new edition is probably better. The error is probably corrected in the current Learning the Bash Shell, but if not, the following examples show you how to use arrays in loops.

Naturally, these do presume some knowledge of working with bash shell, like the first line always is the same in any bash shell script. That you open an if-statement with an if and close it with a fi, and that you else-if is elif; and that a semicolon between a for-statement and the do statement is required when they’re on the same line because they’re two statements.

If you’re new to bash shell arrays, click on the link below to expand a brief tutorial. It takes you through three progressive examples of working with bash arrays.

Only one more trick needs to be qualified before our main MySQL examples. That trick is how you pass parameters to a bash shell script. For reference, this is the part that’s insecure because user command histories are available inside the Linux OS.

Here’s a hello_whom.sh script to demonstrates the concept of parameter passing:

1
2
3
4
5
6
7
8
9
10
#!/usr/bin/bash
 
# This says hello to the argument while managing no argument.
if [[ ${#} = 1 ]]; then
  echo 'The '${0}' program says: "Hello '${1}'!"'
elif [[ ${#} > 1 ]]; then
  echo 'The '${0}' program wants to know if you have more than one name?'
else
  echo 'The '${0}' program wants to know if you have a name?'
fi

If you need more on how parameters are passed and managed, you can check a prior blob post on Handling bash Parameters, or check the bash help pages. The following leverages bash arrays to run scripts and query the MySQL database from the command line.

You will need the three batch SQL files first, so here they are:

The following list_mysql.sh shell script expects to receive the username, password, database and fully qualified path in that specific order. The script names are entered manually because this should be a unit test script. Naturally, you can extend the script to manage those parameters but as mentioned I see this type of solution as a developer machine only script to simplify unit testing. Anything beyond that is risky!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
#!/usr/bin/bash
 
# Assign user and password
username="${1}"
password="${2}"
database="${3}"
directory="${4}"
 
# List the parameter values passed.
echo "Username:  " ${username}
echo "Password:  " ${password}
echo "Database:  " ${database}
echo "Directory: " ${directory}
echo ""
 
# Define an array.
declare -a cmd
 
# Assign elements to an array.
cmd[0]="actor.sql"
cmd[1]="film.sql"
cmd[2]="movie.sql"
 
# Call the array elements.
for i in ${cmd[*]}; do
  mysql -s -u${username} -p${password} -D${database} < ${directory}/${i} > /dev/null 2>/dev/null
done
 
# Connect and pipe the query result minus errors and warnings to the while loop.
mysql -u${username} -p${password} -D${database} <<<'show tables' 2>/dev/null |
 
# Read through the piped result until it's empty but format the title.
while IFS='\n' read list; do
  if [[ ${list} = "Tables_in_sampledb" ]]; then
    echo $list
    echo "----------------------------------------"
  else
    echo $list
  fi
done
echo ""
 
# Connect and pipe the query result minus errors and warnings to the while loop.
mysql -u${username} -p${password} -D${database} <<<'SELECT CONCAT(a.actor_name," in ",f.film_name) AS "Actors in Films" FROM actor a INNER JOIN movie m ON a.actor_id = m.actor_id INNER JOIN film f ON m.film_id = f.film_id' 2>/dev/null |
 
# Read through the piped result until it's empty but format the title.
while IFS='\n' read actor_name; do
  if [[ ${actor_name} = "Actors in Films" ]]; then
    echo $actor_name
    echo "----------------------------------------"
  else
    echo $actor_name
  fi
done

The IFS (Internal Field Separator) works with whitespace by default. The IFS on lines 33 and 47 sets the IFS to a line return ('\n'). That’s the trick to display the data, and you can read more about the IFS in this question and answer post.

You can run this script with the following input parameters from the local directory where you deploy it. The a parameters are: (1) username, (2) password, (3) database, and (4) a fully qualified path to the SQL setup files.

./list_mysql.sh student student sampledb "/home/student/Code/bash/mysql"

With valid input values, the list_mysql.sh bash script generates the following output, which confirms inputs and verifies actions taken by the scripts with queries:

Username:   student
Password:   student
Database:   sampledb
Directory:  /home/student/Code/bash/mysql
 
Tables_in_sampledb
----------------------------------------
actor
film
movie
 
Actors in Films
----------------------------------------
Chris Hemsworth in Thor
Chris Hemsworth in Thor: The Dark World
Chris Pine in Star Trek
Chris Pine in Star Trek into Darkness
Chris Pine in Guardians of the Galaxy

If you forgot to provide the required inputs to the list_mysql.sh bash script, it alternatively returns the following output:

Username:  
Password:  
Database:  
Directory: 
 
./list_mysql.sh: line 25: /actor.sql: No such file or directory
./list_mysql.sh: line 25: /film.sql: No such file or directory
./list_mysql.sh: line 25: /movie.sql: No such file or directory

The secure way removes the password at a minimum! The refactored program will require you to manually enter the password for all elements of the array (three in this sample), and twice for the two queries. Here’s the refactored code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
#!/usr/bin/bash
 
# Assign user and password
username="${1}"
database="${2}"
directory="${3}"
 
# List the parameter values passed.
echo "Username:  " ${username}
echo "Database:  " ${database}
echo "Directory: " ${directory}
echo ""
 
# Define an array.
declare -a cmd
 
# Assign elements to an array.
cmd[0]="actor.sql"
cmd[1]="film.sql"
cmd[2]="movie.sql"
 
# Call the array elements.
for i in ${cmd[*]}; do
  mysql -s -u${username} -p -D${database} < ${directory}/${i} > /dev/null 2>/dev/null
done
 
# Connect and pipe the query result minus errors and warnings to the while loop.
mysql -u${username} -p -D${database} <<<'show tables' 2>/dev/null |
 
# Read through the piped result until it's empty.
while IFS='\n' read list; do
  if [[ ${list} = "Tables_in_sampledb" ]]; then
    echo $list
    echo "----------------------------------------"
  else
    echo $list
  fi
done
echo ""
 
# Connect and pipe the query result minus errors and warnings to the while loop.
mysql -u${username} -p -D${database} <<<'SELECT CONCAT(a.actor_name," in ",f.film_name) AS "Actors in Films" FROM actor a INNER JOIN movie m ON a.actor_id = m.actor_id INNER JOIN film f ON m.film_id = f.film_id' 2>/dev/null |
 
# Read through the piped result until it's empty.
while IFS='\n' read actor_name; do
  if [[ ${actor_name} = "Actors in Films" ]]; then
    echo $actor_name
    echo "----------------------------------------"
  else
    echo $actor_name
  fi
done

Please let me know if you think there should be any more scaffolding for newbies in this post. As always, I hope this helps those looking for this type of solution.

Written by maclochlainn

May 17th, 2015 at 12:01 pm