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.

Working with bash Arrays ↓

A basic example of working with an array in bash shell is the following list1.sh script:

1
2
3
4
5
6
7
8
9
10
11
12
13

#!/usr/bin/bash
 
# Print script name.
echo $0
#!/usr/bin/bash
 
# Define an array.
declare -a cmd=("one" "two" "three")
 
# Call the array elements.
for i in ${cmd[*]}; do
  echo ${i}
done

Line 8 declares the cmd array by assigning three strings. Line 12 returns the elements of the array to the ${i} variable, which lets you manage them one at a time. You use the chmod command to make the list1.sh executable, like this:

chmod 755 list1.sh

Then, you can run it like this from the present working directory (pwd):

./list1.sh

It should print:

one
two
three

The list2.sh example changes the cmd array declaration from list1.sh. It declares the cmd array as an empty array, and then it assigns elements by index numbers (using a zero-based index), as shown below:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

#!/usr/bin/bash
 
# Define an array.
declare -a cmd
 
# Assign elements to an array.
cmd[0]="one"
cmd[1]="two"
cmd[2]="three"
 
# Call the array elements.
for i in ${cmd[*]}; do
  echo ${i}
done

Lines 7 through 9 assign values to the elements of the cmd array. You would chmod the file, and run the file as qualified above for the list1.sh script.

The last pre-implementation example requires that you create three demonstration scripts, the one.sh, two.sh, and three.sh scripts. You should put them in the same directory as the list3.sh script.

The demonstration scripts should all have the same code, like this:

1
2
3
4

#!/usr/bin/bash
 
# Print script name.
echo $0

Line 4 returns command line parameter $0 or ${0}, which is always the command line program’s file name. The file name may be provided as a relative or absolute file name, and if that’s new to you please check out The Linux Command Line: A Complete Introduction (also downloadable as a PDF for free).

The list3.sh script should contain the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

#!/usr/bin/bash
<code>
# Define an array.
declare -a cmd
 
# Assign elements to an array.
cmd[0]="one.sh"
cmd[1]="two.sh"
cmd[2]="three.sh"
 
# Call the array elements.
for i in ${cmd[*]}; do
  `pwd`/${i}
done

When you run the list3.sh script from the /home/student/Code/bash directory with a local syntax, the script should return the fully qualified file names of the subshell programs. The output should look like this:

/home/student/Code/bash/one.sh
/home/student/Code/bash/two.sh
/home/student/Code/bash/three.sh

The list3.sh script provides the present working directory (pwd) and the one.sh, two.sh, and three.sh scripts return only their executable name. For example, if you ran one.sh with the following syntax:

./one.sh

It returns

./one.sh

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:

Setup SQL Files ↓

The actor.sql file:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

-- Use the sampledb database.
USE sampledb;
 
-- Disable foreign key checking.
SET foreign_key_checks = 0;
 
-- Drop an actor table. 
DROP TABLE IF EXISTS actor;
 
-- Create an actor table.
CREATE TABLE actor
( actor_id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, actor_name  VARCHAR(30)  NOT NULL );
 
-- Insert two rows.
INSERT INTO actor (actor_name) VALUES ('Chris Hemsworth');
INSERT INTO actor (actor_name) VALUES ('Chris Pine');
INSERT INTO actor (actor_name) VALUES ('Chris Pratt');

The film.sql file:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

-- Use the sampledb database.
USE sampledb;
 
-- Disable foreign key checking.
SET foreign_key_checks = 0;
 
-- Drop a film table. 
DROP TABLE IF EXISTS film;
 
-- Create a film table.
CREATE TABLE film
( film_id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, film_name  VARCHAR(30)  NOT NULL );
 
-- Insert rows.
INSERT INTO film (film_name) VALUES ('Thor');
INSERT INTO film (film_name) VALUES ('Thor: The Dark World');
INSERT INTO film (film_name) VALUES ('Star Trek');
INSERT INTO film (film_name) VALUES ('Star Trek into Darkness');
INSERT INTO film (film_name) VALUES ('Guardians of the Galaxy');

The movie.sql file:

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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

-- Use the sampledb database.
USE sampledb;
 
-- Disable foreign key checking.
SET foreign_key_checks = 0;
 
-- Drop an movie table. 
DROP TABLE IF EXISTS movie;
 
-- Create an movie table.
CREATE TABLE movie
( movie_id   int unsigned PRIMARY KEY AUTO_INCREMENT
, actor_id   int unsigned NOT NULL
, film_id    int unsigned NOT NULL
, CONSTRAINT actor_fk FOREIGN KEY (actor_id)
  REFERENCES actor (actor_id)
, CONSTRAINT film_fk  FOREIGN KEY (film_id)
  REFERENCES film(film_id));
 
-- Use scalar subqueries to discover surrogate keys by using the faux natural key.
INSERT INTO movie
( actor_id
, film_id )
VALUES
((SELECT   actor_id
  FROM     actor
  WHERE    actor_name = 'Chris Hemsworth')
,(SELECT   film_id
  FROM     film
  WHERE    film_name = 'Thor'));
 
-- Use scalar subqueries to discover surrogate keys by using the faux natural key.
INSERT INTO movie
( actor_id
, film_id )
VALUES
((SELECT   actor_id
  FROM     actor
  WHERE    actor_name = 'Chris Hemsworth')
,(SELECT   film_id
  FROM     film
  WHERE    film_name = 'Thor: The Dark World'));
 
-- Use scalar subqueries to discover surrogate keys by using the faux natural key.
INSERT INTO movie
( actor_id
, film_id )
VALUES
((SELECT   actor_id
  FROM     actor
  WHERE    actor_name = 'Chris Pine')
,(SELECT   film_id
  FROM     film
  WHERE    film_name = 'Star Trek'));
 
-- Use scalar subqueries to discover surrogate keys by using the faux natural key.
INSERT INTO movie
( actor_id
, film_id )
VALUES
((SELECT   actor_id
  FROM     actor
  WHERE    actor_name = 'Chris Pine')
,(SELECT   film_id
  FROM     film
  WHERE    film_name = 'Star Trek into Darkness'));
 
-- Use scalar subqueries to discover surrogate keys by using the faux natural key.
INSERT INTO movie
( actor_id
, film_id )
VALUES
((SELECT   actor_id
  FROM     actor
  WHERE    actor_name = 'Chris Pratt')
,(SELECT   film_id
  FROM     film
  WHERE    film_name = 'Guardians of the Galaxy'));

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

Posted in bash,basic programming,DBA,Fedora,Linux,MySQL,MySQL 5.6,MySQL Workbench,sql

Tagged with , , ,

«
»