Archive for the ‘MySQL’ Category
Oxygen XML Editor
Somebody asked me about how they could convert an XML file to a CSV file to upload into MySQL. They were asking the question based on an old Convert XML to CSV blog post from 2008. Amazing though that is, I had to explain the process no longer requires manual tasks, like calling Java files from the Apache XML Project. All they needed to do was use the Oxygen XML Editor, which is why I wrote this blog post.
For example, I had them use the same sample XML file from the old blog post (shown below) with one change. The encoding value needs to change from latin1 (ISO-8859-1) to unicode (UTF-8). Then, they should put it into a local Windows directory (mine went into the C:\Data directory).
<?xml version="1.0" encoding="UTF-8"?> <character> <name> <role>Indiana Jones</role> <actor>Harrison Ford</actor> <part>protagonist</part> <film>Indiana Jones and Raiders of the Lost Ark</film> <film>Indiana Jones and the Temple of Doom</film> <film>Indiana Jones and the Last Crusade</film> <film>Indiana Jones and the Kingdom of the Crystal Skull</film> </name> <name> <role>Wilhelmina Scott</role> <actor>Kate Capshaw</actor> <part>support</part> <film>Indiana Jones and the Temple of Doom</film> </name> <name> <role>Marion Ravenwood</role> <actor>Karen Allen</actor> <part>support</part> <film>Indiana Jones and Raiders of the Lost Ark</film> <film>Indiana Jones and the Kingdom of the Crystal Skull</film> </name> <name> <role>Elsa Schneider</role> <actor>Alison Doody</actor> <part>support</part> <film>Indiana Jones and the Last Crusade</film> </name> <name> <role>Short Round</role> <actor>Jonathan Ke Quan</actor> <part>support</part> <film>Indiana Jones and the Temple of Doom</film> </name> <name> <role>Sallah</role> <actor>Jonn Rhys-Davies</actor> <part>support</part> <film>Indiana Jones and Raiders of the Lost Ark</film> <film>Indiana Jones and the Last Crusade</film> </name> <name> <role>Professor Henry Jones</role> <actor>Sean Connery</actor> <part>support</part> <film>Indiana Jones and the Last Crusade</film> </name> <name> <role>Henry "Mutt" Williams</role> <actor>Shia LaBeouf</actor> <part>support</part> <film>Indiana Jones and the Kingdom of the Crystal Skull</film> </name> <name> <role>Marcus Brody</role> <actor>Denholm Elliott</actor> <part>support</part> <film>Indiana Jones and Raiders of the Lost Ark</film> <film>Indiana Jones and the Last Crusade</film> </name> <name> <role>Amrish Puri</role> <actor>Mola Ram</actor> <part>antagonist</part> <film>Indiana Jones and the Temple of Doom</film> </name> <name> <role>Rene Belloq</role> <actor>Belloq</actor> <part>antagonist</part> <film>Indiana Jones and Raiders of the Lost Ark</film> </name> <name> <role>Walter Donovan</role> <actor>Julian Glover</actor> <part>antagonist</part> <film>Indiana Jones and the Last Crusade</film> </name> <name> <role>Colonel Vogel</role> <actor>Michael Bryne</actor> <part>antagonist</part> <film>Indiana Jones and the Last Crusade</film> </name> <name> <role>Irina Spalko</role> <actor>Cate Blanchett</actor> <part>antagonist</part> <film>Indiana Jones and the Kingdom of the Crystal Skull</film> </name> </character> |
Then, I had them copy the following XML Style Language Transformation (XSLT) file into the same C:\Data directory with the encoding value change from latin1 to unicode:
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 | <?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="/"> <!-- This loops through the branch when a sibling meets a condition. --> <xsl:for-each select="character/name[film='Indiana Jones and the Last Crusade']"> <!-- Sorts based on the value in the "role" element. --> <xsl:sort select="role" /> <!-- Eliminates anyone that has a "part" element value of "antagonist". --> <xsl:if test="part != 'antagonist'"> <!-- An apostrophe before and after with a line return. --> <xsl:text>'</xsl:text> <xsl:value-of select="role"/> <!-- An apostrophe followed by a comma --> <xsl:text>',</xsl:text> <xsl:text>'</xsl:text> <xsl:value-of select="actor"/> <xsl:text>',</xsl:text> <xsl:text>'</xsl:text> <xsl:value-of select="film"/> <!-- An apostrophe followed by a line return --> <xsl:text>' </xsl:text> </xsl:if> </xsl:for-each> </xsl:template> </xsl:stylesheet> |
Open or launch the Oxygen XML Editor and do these steps:
- Create a new Project called character.
- Create the
C:\Data\test.xmlandC:\Data\convert.xslfiles in aC:\Datadirectory. - Open the C:\Data\test.xml and C:\Data\convert.xsl files inside the Oxygen XML Editor.
- Click on
convert.xslfile tab before clicking on the Configure Transformation Scenario(s) button. The button looks like a red wrench with a small red arrow to the bottom right. - After launching the Configure Transformation Scenario(s) dialog, click the Edit button in the dialog box and launch the Edit Scenario dialog.
- Enter
file:/C:/Data/test.xml(use the file chooser if don’t want to type it) in the XML URL field in the Edit Scenario dialog. - Click the OK button to close the Edit Scenario dialog and the Apply associated button to close the Configure Transformation Scenario(s) dialog.
- Click the Apply Transformation Scenario button, which is red arrow button. It will transform the XML document into a result pane at the bottom.
- Select All (or
Ctrl+A) in the result panel and right click on that selected area to launch a context sensitive menu. In that menu, click the Save button to launch a file chooser that will let you save your results.
If you know how to write XSLT this is simple and if you don’t it might take a little time to find a working example on the Internet. Better yet, check out the w3schools for the XSLT documentation or tutorials point’s Learn XSLT website. You can see how to Upload the CSV file into MySQL on this older blog post.
In Linux, you can make this conversion using the Command-Line Interface (CLI) by using the xsltproc program. Assume the XML file is test.xml and the XSLT file is tocsv.xsl, then you can generate the Comma-Separated Values file with this syntax:
xsltproc tocsv.xsl test.xml > text.csv |
As always, I hope this helps those looking for a simple solution.
MySQL Join Tutorial
Some believe the most important part of SQL is the ability to query data. Queries typically retrieve data by joining many tables together into useful result sets. This tutorial takes the position that visibility into the data helps those new to SQL understand how joins work. To that end, the queries use Common Tabular Expressions (CTEs) instead of tables.
Default behavior of a JOIN without a qualifying descriptor is not simple because it may return:
- A
CROSS JOIN(or Cartesian Product) when there is noONorUSINGsubclause, or - An
INNER JOINwhen you use anONorUSINGsubclause.
The following query uses JOIN without a qualifier or an ON or USING subclause. It also uses two copies of the single CTE, which is more or less a derived table and the result of a subquery held in memory. This demonstrates the key reason for table aliases. That key reason is you can put two copies of the same table in memory under different identifiers or labels.
1 2 3 4 5 6 7 | WITH alpha AS (SELECT 'A' AS letter, 130 AS amount UNION SELECT 'B' AS letter, 150 AS amount UNION SELECT 'C' AS letter, 321 AS amount) SELECT * FROM alpha a JOIN alpha b; |
It returns a Cartesian product:
+--------+--------+--------+--------+ | letter | amount | letter | amount | +--------+--------+--------+--------+ | A | 130 | A | 130 | | B | 150 | A | 130 | | C | 321 | A | 130 | | A | 130 | B | 150 | | B | 150 | B | 150 | | C | 321 | B | 150 | | A | 130 | C | 321 | | B | 150 | C | 321 | | C | 321 | C | 321 | +--------+--------+--------+--------+ 9 rows in set (0.00 sec) |
By adding an ON clause to line 8, the default JOIN keyword returns an INNER JOIN result.
1 2 3 4 5 6 7 8 | WITH alpha AS (SELECT 'A' AS letter, 130 AS amount UNION SELECT 'B' AS letter, 150 AS amount UNION SELECT 'C' AS letter, 321 AS amount) SELECT * FROM alpha a JOIN alpha b ON a.letter = b.letter; |
It displays results, like:
+--------+--------+--------+--------+ | letter | amount | letter | amount | +--------+--------+--------+--------+ | A | 130 | A | 130 | | B | 150 | B | 150 | | C | 321 | C | 321 | +--------+--------+--------+--------+ 3 rows in set (0.00 sec) |
The next example uses two CTEs. One uses letters 'A', 'B', 'C', and D and the other uses letters 'A', 'B', 'C', and 'E'. The letter D only exists in the alpha derived table and the letter 'E' only exists in the beta derived table. The amount column values differ for their respective letters in the two CTE tables.
The basic query below the comma delimited CTEs joins the alpha and beta derived tables with an INNER JOIN using an ON clause based on the letter column values found in both alpha and beta CTEs.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | WITH alpha AS (SELECT 'A' AS letter, 130 AS amount UNION SELECT 'B' AS letter, 150 AS amount UNION SELECT 'C' AS letter, 321 AS amount UNION SELECT 'D' AS letter, 783 AS amount) , beta AS (SELECT 'A' AS letter, 387 AS amount UNION SELECT 'B' AS letter, 268 AS amount UNION SELECT 'C' AS letter, 532 AS amount UNION SELECT 'E' AS letter, 391 AS amount) SELECT * FROM alpha a INNER JOIN beta b ON a.letter = b.letter; |
The INNER JOIN returns only those rows in alpha and beta CTEs where the letter column values match:
+--------+--------+--------+--------+ | letter | amount | letter | amount | +--------+--------+--------+--------+ | A | 130 | A | 387 | | B | 150 | B | 268 | | C | 321 | C | 532 | +--------+--------+--------+--------+ 3 rows in set (0.01 sec) |
If you change line 17 from an INNER JOIN to a LEFT JOIN, you return all the rows from the alpha CTE and only those rows from the beta CTE that have a matching letter column value. The new line 17 for a LEFT JOIN is:
17 | SELECT * FROM alpha a LEFT JOIN beta b |
It returns the three matching rows plus the one non-matching row from the alpha CTE that is on the left side of the LEFT JOIN operator. You should note that that a left outer join puts null values into the beta CTE columns where there is no matching row for the 'D' letter found in the alpha CTE.
The results are shown below:
+--------+--------+--------+--------+ | letter | amount | letter | amount | +--------+--------+--------+--------+ | A | 130 | A | 387 | | B | 150 | B | 268 | | C | 321 | C | 532 | | D | 783 | NULL | NULL | +--------+--------+--------+--------+ 4 rows in set (0.01 sec) |
If you change line 17 from an LEFT JOIN to a RIGHT JOIN, you return all the rows from the beta CTE and only those rows from the alpha CTE that have a matching letter column value. The new line 17 for a RIGHT JOIN is:
17 | SELECT * FROM alpha a RIGHT JOIN beta b |
It returns the following result set:
+--------+--------+--------+--------+ | letter | amount | letter | amount | +--------+--------+--------+--------+ | A | 130 | A | 387 | | B | 150 | B | 268 | | C | 321 | C | 532 | | NULL | NULL | E | 391 | +--------+--------+--------+--------+ 4 rows in set (0.00 sec) |
MySQL does not support a FULL JOIN operation but you can mimic a full join by combining a LEFT JOIN and RIGHT JOIN with the UNION operator. The UNION operator performs a unique sort operation, which reduces the two copies of matching rows returned by both the left and right join operation to a unique set.
This is the way to write the equivalent of a full join:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | WITH alpha AS (SELECT 'A' AS letter, 130 AS amount UNION SELECT 'B' AS letter, 150 AS amount UNION SELECT 'C' AS letter, 321 AS amount UNION SELECT 'D' AS letter, 783 AS amount) , beta AS (SELECT 'A' AS letter, 387 AS amount UNION SELECT 'B' AS letter, 268 AS amount UNION SELECT 'C' AS letter, 532 AS amount UNION SELECT 'E' AS letter, 391 AS amount) SELECT * FROM alpha LEFT JOIN beta ON alpha.letter = beta.letter UNION SELECT * FROM alpha right JOIN beta ON alpha.letter = beta.letter; |
It returns one copy of the matching rows, and the non-matching rows from both the alpha and beta CTEs:
+--------+--------+--------+--------+ | letter | amount | letter | amount | +--------+--------+--------+--------+ | A | 130 | A | 387 | | B | 150 | B | 268 | | C | 321 | C | 532 | | D | 783 | NULL | NULL | | NULL | NULL | E | 391 | +--------+--------+--------+--------+ 5 rows in set (0.00 sec) |
A NATURAL JOIN would return no rows because it works by implicitly discovering columns with matching names in both CTEs and then joins the result set from both CTEs. While the letter column matches rows between the CTEs the amount column doesn’t hold any matches. The combination of letter and amount columns must match for a NATURAL JOIN operation to return any rows.
You also have the ability to override the cost optimizer and force a left to right join by using the STRAIGHT_JOIN operator. As always, I hope this helps those looking for a solution with an explanation.
MySQL macOS Docker
While you can download MySQL as a DMG package, a number of users would prefer to install it as a Docker instance. You won’t find the macOS downloads on the same web site as other downloads. You can use the following macOS download site.
After installing Docker on your macOS, you can pull a copy of the current MySQL Server with the following command:
docker pull mysql/mysql-server |
You should create a mysql directory inside your ~/Documents directory with this command:
mkdir ~/Documents/mysql |
Then, you should use the cd command to change into the ~/Documents/mysql directory and run this command:
pwd |
It should return the following directory:
/Users/<user_name>/Documents/mysql |
Use the /Users/<user_name>/Documents/mysql as the
docker run --name=mysql1 --volume=<path_to_folder>:/var/lib/mysql -p 33060:3306/tcp -d mysql/mysql-server |
The --name option value is mysql1 and it becomes the container value. Docker mounts the column in the ~/Documents/mysql folder. All data from the Docker container under the /var/lib/mysql directory will persist in this directory. This directory will still contain the database when the container is shut down.
The docker run command maps the localhost’s 33060 port to the 3306 port on the Docker container. You will use the 33060 port to connect to the Docker instance of MySQL. It raises a dialog box asking for permission to access the directory. You need to allow Docker to write to the ~/Documents/mysql directory.
You can verify that the Docker container is running with the following command:
docker ps |
It should return:
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 142b5c491cd8 mysql/mysql-server "/entrypoint.sh mysq…" 7 minutes ago Up 6 minutes (healthy) 33060-33061/tcp, 0.0.0.0:33060->3306/tcp mysql1 |
You can get the MySQL generated root password with this Docker command:
docker logs mysql1 2>&1 | grep GENERATED |
It returns something like the following:
[Entrypoint] GENERATED ROOT PASSWORD: vop#3GNYqK3nC@S@N3haf3nox5E |
Use the following Docker command to connect to the Docker container:
docker exec -it mysql1 /bin/bash |
It launches a Bash shell inside the Docker container:
bash-4.2# |
Start the mysql Command-Line Interface (CLI):
mysql -uroot -p |
You are then prompted for a password:
Enter password: |
After successfully entering the password, you’ll see the following:
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 55 Server version: 8.0.22 Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> |
Unless you want to remember that hugely complex root password, you should consider changing it to something simple like, 'cangetin' with the following command:
ALTER USER 'root'@'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'cangetin'; |
Next, you should check for the installed databases with this command:
show databases; |
It will return:
+--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.01 sec) |
Exiting mysql, you can see the contents of the root user’s directory with this list command:
ls -al |
It should return:
total 84 drwxr-xr-x 1 root root 4096 Jan 12 03:41 . drwxr-xr-x 1 root root 4096 Jan 12 03:41 .. -rwxr-xr-x 1 root root 0 Jan 12 03:41 .dockerenv lrwxrwxrwx 1 root root 7 Oct 12 22:06 bin -> usr/bin dr-xr-xr-x 2 root root 4096 Apr 11 2018 boot drwxr-xr-x 5 root root 340 Jan 12 03:41 dev drwxr-xr-x 2 root root 4096 Oct 19 05:47 docker-entrypoint-initdb.d -rwxr-xr-x 1 root root 7496 Oct 19 05:37 entrypoint.sh drwxr-xr-x 1 root root 4096 Jan 12 03:41 etc -rw-r--r-- 1 root root 86 Jan 12 03:41 healthcheck.cnf -rwxr-xr-x 1 root root 1073 Oct 19 05:37 healthcheck.sh drwxr-xr-x 2 root root 4096 Apr 11 2018 home lrwxrwxrwx 1 root root 7 Oct 12 22:06 lib -> usr/lib lrwxrwxrwx 1 root root 9 Oct 12 22:06 lib64 -> usr/lib64 drwxr-xr-x 2 root root 4096 Apr 11 2018 media drwxr-xr-x 2 root root 4096 Apr 11 2018 mnt -rw-r--r-- 1 root root 0 Jan 12 03:41 mysql-init-complete drwxr-xr-x 2 root root 4096 Apr 11 2018 opt dr-xr-xr-x 127 root root 0 Jan 12 03:41 proc dr-xr-x--- 1 root root 4096 Jan 12 04:21 root drwxr-xr-x 1 root root 4096 Oct 19 05:47 run lrwxrwxrwx 1 root root 8 Oct 12 22:06 sbin -> usr/sbin drwxr-xr-x 2 root root 4096 Apr 11 2018 srv dr-xr-xr-x 13 root root 0 Jan 12 03:41 sys drwxrwxrwt 1 root root 4096 Jan 12 03:41 tmp drwxr-xr-x 1 root root 4096 Oct 12 22:06 usr drwxr-xr-x 1 root root 4096 Oct 12 22:06 var |
At this point, you have to make a choice about how you will access the MySQL database. You have a couple options:
- Create an individual
studentuser that can access the MySQL-Server as a micro-service, which would only be a MySQL user connecting through MySQL workbench. At least, that’s the only connection option unless you likewise install themysqlclient on your host macOS. Themysqlclient lets you connect from the host operating system through the Command-Line Interface (CLI). - Create a local
studentuser account inside the Docker container. It will have access to the container file system and mimic the behavior of a non-rootuser on a server.
Let’s create both for this demonstration. Reconnect as the root user and issue the following two commands:
CREATE USER 'student'@'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'student'; CREATE USER 'student'@'%.%.%.%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'student'; |
The first version of the student user lets you access the database from inside the Docker container. The second version of the student user lets you access the database from MySQL Workbench deployed on your base macOS.
You can add a sakila database and grant all privileges to the student user with the following command as the root user:
CREATE DATABASE sakila; GRANT ALL ON sakila.* TO 'student'@'localhost'; GRANT ALL ON sakila.* TO 'student'@'%.%.%.%'; |
You need to get the sakila database from the Internet within the Docker container. Exit the mysql client with the following command:
quit; |
As the root user, install the wget and tar Linux utilities with this command:
yum install -y wget tar |
As the student user, you can use the wget command to grab a copy of the sakila database and store the database locally. Use the cd command to get to your ${HOME} directory, like:
cd |
Use this syntax to get a copy of the sakila database:
wget http://downloads.mysql.com/docs/sakila-db.tar.gz |
Use the ls command to verify the download, then run the following set of Linux commands from the Linux CLI:
tar -xzf sakila-db.tar.gz cd sakila-db |
Run the following two commands from the sakila-db directory:
mysql -ustudent -p < sakila-schema.sql mysql -ustudent -p < sakila-data.sql |
or, you can connect as the student user to the MySQL client and run them there:
source sakila-schema.sql source sakila-data.sql |
You create a non-root student user for the Docker container from the macOS host opearting system. Which means you need to quit; the mysql client, and exit the root user’s session with the Docker container.
At the terminal in your macOS, issue the following Docker command to create a student account in the mysql1 container:
docker exec mysql1 bash -c "useradd -u 501 -g mysql -G users \ > -d /home/student -s /bin/bash -c "Student" -n student" |
Now, you can connect as the student user to the mysql1 container, with the following Docker command:
docker exec -it --user student mysql1 bash |
The first time you connect, you will be a the / (root) directory. Use the following cd command to go to the student user’s home directory:
cd |
Then, type the following command to set the student user’s home directory as the default. You need to use this command because vim isn’t installed in the default Docker container, which would let you interactively edit files. It appends the necessary Bash shell command to the end of the .bashrc file.
echo 'cd ${HOME}' >> .bashrc |
With this change, the student user will always be available form its home directory next time you connect to the mysql1 container. You can use scp to move files into the student user’s home (/home/student) directory. However, you can create a quick test.sql file like this:
echo "select user();" > test.sql |
Connect to the mysql CLI with as the student user:
mysql -ustudent -p |
Call your test.sql file from the Linux CLI, like:
mysql -ustudent -p < test.sql |
or, you can run the test.sql program as follows form the MySQL command-line:
source test.sql |
It will return:
+-------------------+ | user() | +-------------------+ | student@localhost | +-------------------+ 1 row in set (0.00 sec) |
That’s the basic setup of the Docker MySQL Container on the macOS. You can do much more once you’ve configured it like this. For example, you can add vim to your library repository as the root user with the following command:
yum install -y vim |
It just takes a minute or a bit more. Adding vim opens up so much flexibility for you inside the Docker container, it’s impossible for me to resist. 😉
MySQL sakila Database
While I thought my instructions were clear, it appears there should have been more in my examples for using the MySQL MSI. A key thing that happened is that students opted not to install:
Samples and Examples 8.0.22 |
Unfortunately, they may not have read the Preface of Alan Beaulieu’s Learning SQL, 3rd Edition where he explains how to manually download the files from the MySQL web site. Here are those, very clear, instructions (pg. XV) with my additions in italics for the MySQL Shell:
First, you will need to launch the mysql command-line client or the mysqlsh command-line shell, and provide a password, and then perform the following steps:
- Go to https://dev.mysql.com/doc/index-other.html and download the files for the “
sakiladatabase” under the Example Database section. - Put the files in the local directory such as
C:\temp\sakila-db(used for the next two steps, but overwrite with your directory path). - Type
source c:\temp\sakila-db\sakila-schema.sql
and press enter.
- Type
source c:\temp\sakila-db\sakila-data.sql
and press enter.
These instructions let you create the sakila database without rerunning the MSI to add a product. Naturally, you can avoid these steps by using the GUI approach provided in the MySQL MSI file.
As always, I hope this helps those looking for how to solve problems.
MySQL Self-Join
I’m switching to MySQL and leveraging Alan Beaulieu’s Learning SQL as a supporting reference for my Database Design and Development course. While reviewing Alan’s Chapter 5: Querying Multiple Tables, I found his coverage of using self-joins minimal.
In fact, he adds a prequel_film_id column to the film table in the sakila database and then a single row to demonstrate a minimal self-join query. I wanted to show them how to view a series of rows interconnected by a self-join, like the following:
SELECT f.title AS film , fp.title AS prequel FROM film f LEFT JOIN film fp ON f.prequel_id = fp.film_id WHERE f.series_name = 'Harry Potter' ORDER BY f.series_number; |
It returns the following result set:
+----------------------------------------------+----------------------------------------------+ | film | prequel | +----------------------------------------------+----------------------------------------------+ | Harry Potter and the Chamber of Secrets | Harry Potter and the Sorcerer's Stone | | Harry Potter and the Prisoner of Azkaban | Harry Potter and the Chamber of Secrets | | Harry Potter and the Goblet of Fire | Harry Potter and the Prisoner of Azkaban | | Harry Potter and the Order of the Phoenix | Harry Potter and the Goblet of Fire | | Harry Potter and the Half Blood Prince | Harry Potter and the Order of the Phoenix | | Harry Potter and the Deathly Hallows: Part 1 | Harry Potter and the Half Blood Prince | | Harry Potter and the Deathly Hallows: Part 2 | Harry Potter and the Deathly Hallows: Part 1 | +----------------------------------------------+----------------------------------------------+ 7 rows in set (0.00 sec) |
Then, I thought about what questions the students might ask. For example, why doesn’t the query return the first film that doesn’t have a prequel. So, I took the self-join to the next level to display the first film having no prequel, like this:
SELECT f.title AS film , IFNULL( CASE WHEN NOT f.film_id = fp.film_id AND f.prequel_id = fp.film_id THEN fp.title END,'None') AS prequel FROM film f LEFT JOIN film fp ON f.prequel_id = fp.film_id WHERE f.series_name = 'Harry Potter' ORDER BY f.series_number; |
The CASE operator in the SELECT-list filters the result set by eliminating rows erroneously returned. Without the CASE filter, the query would return the original Harry Potter and the Sorcerer’s Stone film matched agains a NULL and all of the other sequels. The CASE operator effectively limits the result set for the LEFT JOIN to only the following data:
+----------------------------------------------+----------------------------------------------+ | film | prequel | +----------------------------------------------+----------------------------------------------+ | Harry Potter and the Sorcerer's Stone | NULL | +----------------------------------------------+----------------------------------------------+ |
The IFNULL() built-in function lets you replace the NULL value returned as the prequel’s title value. The IFNULL() function substitutes a 'None' string literal for a NULL value. The query returns the following result set:
+----------------------------------------------+----------------------------------------------+ | film | prequel | +----------------------------------------------+----------------------------------------------+ | Harry Potter and the Sorcerer's Stone | None | | Harry Potter and the Chamber of Secrets | Harry Potter and the Sorcerer's Stone | | Harry Potter and the Prisoner of Azkaban | Harry Potter and the Chamber of Secrets | | Harry Potter and the Goblet of Fire | Harry Potter and the Prisoner of Azkaban | | Harry Potter and the Order of the Phoenix | Harry Potter and the Goblet of Fire | | Harry Potter and the Half Blood Prince | Harry Potter and the Order of the Phoenix | | Harry Potter and the Deathly Hallows: Part 1 | Harry Potter and the Half Blood Prince | | Harry Potter and the Deathly Hallows: Part 2 | Harry Potter and the Deathly Hallows: Part 1 | +----------------------------------------------+----------------------------------------------+ 8 rows in set (0.01 sec) |
Alan’s modification of the sakila.film table had the following two related design flaws:
- It didn’t provide a way to guarantee the ordering of films with prequels because relational databases don’t guarantee ordered result sets unless you use an
ORDER BYclause, which typically requires a column to order. - It didn’t provide a way to isolate a series of films.
I modified the film table differently by adding the series_name, series_number, and prequel_id columns. The series_name column lets you group results and the series_number column lets you order by a preserved sequence that you store as part of the data The prequel_id column lets you connect to the prequel film, much like the backward portion of a doubly linked list.
The new sakila.film table is:
+----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+ | film_id | smallint unsigned | NO | PRI | NULL | auto_increment | | title | varchar(255) | NO | MUL | NULL | | | description | text | YES | | NULL | | | release_year | year | YES | | NULL | | | language_id | tinyint unsigned | NO | MUL | NULL | | | original_language_id | tinyint unsigned | YES | MUL | NULL | | | rental_duration | tinyint unsigned | NO | | 3 | | | rental_rate | decimal(4,2) | NO | | 4.99 | | | length | smallint unsigned | YES | | NULL | | | replacement_cost | decimal(5,2) | NO | | 19.99 | | | rating | enum('G','PG','PG-13','R','NC-17') | YES | | G | | | special_features | set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') | YES | | NULL | | | last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | | series_name | varchar(20) | YES | | NULL | | | series_number | int unsigned | YES | | NULL | | | prequel | int unsigned | YES | | NULL | | +----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+ 16 rows in set (0.21 sec) |
After adding the three new columns, I inserted eight rows for the original Harry Potter films. You can use the following script in the MySQL client (mysql) to add the columns and insert the data to test the preceding queries:
-- Use sakila database. USE sakila; -- Add a prequel_id column to the sakila.film table. ALTER TABLE film ADD (series_name varchar(20)), ADD (series_number int unsigned), ADD (prequel_id int unsigned); -- Set primary to foreign key local variable. SET @sv_film_id = 0; -- Insert Harry Potter films in sakila.film table with classic values clause. INSERT INTO film ( title , description , release_year , language_id , original_language_id , rental_duration , rental_rate , length , replacement_cost , rating , special_features , last_update , series_name , series_number , prequel_id ) VALUES ('Harry Potter and the Sorcerer''s Stone' ,'A film about a young boy who on his eleventh birthday discovers, he is the orphaned boy of two powerful wizards and has unique magical powers.' , 2001 , 1 , NULL , 3 , 0.99 , 152 , 19.99 ,'PG' ,'Trailers' ,'2001-11-04' ,'Harry Potter' , 1 , NULL ); -- Assign the last generated primary key value to the local variable. SET @sv_film_id := last_insert_id(); -- Insert 2nd film in sakila.film table with classic values clause. INSERT INTO film ( title , description , release_year , language_id , original_language_id , rental_duration , rental_rate , length , replacement_cost , rating , special_features , last_update , series_name , series_number , prequel_id ) VALUES ('Harry Potter and the Chamber of Secrets' ,'A film where Harry returning to Hogwarts, still famous and a hero, when strange things start to happen ... people are turning to stone and no-one knows what, or who, is doing it.' , 2002 , 1 , NULL , 3 , 0.99 , 160 , 19.99 ,'PG' ,'Trailers' ,'2002-11-15' ,'Harry Potter' , 2 , @sv_film_id ); -- Assign the last generated primary key value to the local variable. SET @sv_film_id := last_insert_id(); -- Insert 3rd film in sakila.film table with classic values clause. INSERT INTO film ( title , description , release_year , language_id , original_language_id , rental_duration , rental_rate , length , replacement_cost , rating , special_features , last_update , series_name , series_number , prequel_id ) VALUES ('Harry Potter and the Prisoner of Azkaban' ,'A film where Harry, Ron, and Hermione return for their third year at Hogwarts and are forced to face escaped prisoner, Sirius Black.' , 2004 , 1 , NULL , 3 , 0.99 , 141 , 19.99 ,'PG' ,'Trailers' ,'2004-06-04' ,'Harry Potter' , 3 , @sv_film_id ); -- Assign the last generated primary key value to the local variable. SET @sv_film_id := last_insert_id(); -- Insert 4th film in sakila.film table with classic values clause. INSERT INTO film ( title , description , release_year , language_id , original_language_id , rental_duration , rental_rate , length , replacement_cost , rating , special_features , last_update , series_name , series_number , prequel_id ) VALUES ('Harry Potter and the Goblet of Fire' ,'A film where where Harry Potter''s name emerges from the Goblet of Fire, and he becomes a competitor in a grueling battle for glory among three wizarding schools - the Triwizard Tournament.' , 2005 , 1 , NULL , 3 , 0.99 , 157 , 19.99 ,'PG' ,'Trailers' ,'2005-11-18' ,'Harry Potter' , 4 , @sv_film_id ); -- Assign the last generated primary key value to the local variable. SET @sv_film_id := last_insert_id(); -- Insert 5th film in sakila.film table with classic values clause. INSERT INTO film ( title , description , release_year , language_id , original_language_id , rental_duration , rental_rate , length , replacement_cost , rating , special_features , last_update , series_name , series_number , prequel_id ) VALUES ('Harry Potter and the Order of the Phoenix' ,'A film where Lord Voldemort has returned, but the Ministry of Magic is doing everything it can to keep the wizarding world from knowing the truth.' , 2007 , 1 , NULL , 3 , 0.99 , 138 , 19.99 ,'PG-13' ,'Trailers' ,'2007-07-12' ,'Harry Potter' , 5 , @sv_film_id ); -- Assign the last generated primary key value to the local variable. SET @sv_film_id := last_insert_id(); -- Insert 6th film in sakila.film table with classic values clause. INSERT INTO film ( title , description , release_year , language_id , original_language_id , rental_duration , rental_rate , length , replacement_cost , rating , special_features , last_update , series_name , series_number , prequel_id ) VALUES ('Harry Potter and the Half Blood Prince' ,'A film where Voldemort is tightening his grip on Hogwarts and it is no longer the safe haven it once was. Harry and Dumbledore work to find the key to unlock the Dark Lord''s defenses.' , 2009 , 1 , NULL , 3 , 0.99 , 153 , 19.99 ,'PG' ,'Trailers' ,'2009-07-15' ,'Harry Potter' , 6 , @sv_film_id ); -- Assign the last generated primary key value to the local variable. SET @sv_film_id := last_insert_id(); -- Insert 7th film in sakila.film table with classic values clause. INSERT INTO film ( title , description , release_year , language_id , original_language_id , rental_duration , rental_rate , length , replacement_cost , rating , special_features , last_update , series_name , series_number , prequel_id ) VALUES ('Harry Potter and the Deathly Hallows: Part 1' ,'A film where Harry, Ron and Hermione set out on their perilous mission to track down and destroy the Horcruxes - the keys to Voldemort''s immortality.' , 2010 , 1 , NULL , 3 , 0.99 , 146 , 19.99 ,'PG-13' ,'Trailers' ,'2010-11-19' ,'Harry Potter' , 7 , @sv_film_id ); -- Assign the last generated primary key value to the local variable. SET @sv_film_id := last_insert_id(); -- Insert 8th film in sakila.film table with classic values clause. INSERT INTO film ( title , description , release_year , language_id , original_language_id , rental_duration , rental_rate , length , replacement_cost , rating , special_features , last_update , series_name , series_number , prequel_id ) VALUES ('Harry Potter and the Deathly Hallows: Part 2' ,'A film where Harry, Ron and Hermione set out on their perilous mission to track down and destroy the Horcruxes - the keys to Voldemort''s immortality.' , 2011 , 1 , NULL , 3 , 0.99 , 130 , 19.99 ,'PG-13' ,'Trailers' ,'2011-07-15' ,'Harry Potter' , 8 , @sv_film_id ); |
You can put the following commands into a SQL script file to revert the sakila.film table to its base configuration:
DELETE FROM film WHERE film_id > 1000; ALTER TABLE film DROP COLUMN series_name; ALTER TABLE film DROP COLUMN series_number; ALTER TABLE film DROP COLUMN prequel_id; ALTER TABLE film AUTO_INCREMENT = 1000; |
As always, I hope this helps those looking for how to solve a new problem.
MySQL Client in 8.0.21+
Having recently installed a fresh copy of MySQL 8.0.21 on Windows, I took careful note of parsing changes in the MySQL Shell. It was tedious that we lost multiple statement processing, which is supported in the MySQL Client and MySQL Workbench because it uses MySQL Client.
It was frustrating when I subsequently discovered that the MySQL Shell took away the ability to write log files by removing the TEE and NOTEE commands. I suspected that since MySQL Workbench was still using the MySQL Client that it should be in the code tree. In fact, the mysql.exe client is in this directory:
C:\Program Files\MySQL\MySQL Server 8.0\bin |
So, I immediately created a batch file to put the MySQL Client into my %PATH% environment variable when needed. I used this time tested DOS command:
copy con mysqlclient.bat set PATH=%PATH%;C:\Program Files\MySQL\MySQL Server 8.0\bin;. ^Z |
Now you can call the MySQL client with the following syntax if you have a student user and studentdb database:
mysql -ustudent -p -Dstudentdb |
This way, I can just call it in a PowerShell utility before running migration script files that should generate log files for review. It also leaves the MySQL Client out of general use.
Conditional Updates
While I’m switching labs next term after more than a decade with more comprehensive lab set, I’m hoping the new exercises build the students’ core SQL skill set. Next term, I hope to see whether the change is successful. I’ve opted for using Alan Beaulieu’s Learning SQL: Generate, Manipulate, and Retrieve Data, 3rd Edition, because it’s a great book and uses the MySQL database.
One exercise that the students will lose is a data migration exercise from a badly designed common_lookup table to a well designed common_lookup table. The starting point is shown below on the left and the fixed version is on the right.
There are several problems with the original common_lookup table’s design. The first problem is that the common_lookup_context column does not uniquely identify a location within the data model for at least one list of lookup values. While it uses table names generally, it has no way to support two or more lists within the same table. It also uses a 'MULTIPLE' string for a list of values that supports two tables. The two tables supported by 'MULTIPLE' string are the address and telephone tables.
The lab instructions have the students add the following three columns to the table:
common_lookup_tablecommon_lookup_columncommon_lookup_code
Together the combination of the common_lookup_table and common_lookup_column columns create a non-unique super key. The super key identifies micro subtables. The combination of the common_lookup_table, common_lookup_column, and common_lookup_type columns creates a unique natural key that defines all possible values for a lookup list based on a column in a table.
The lab asked the students to use the existing data, string literal values, and some simple rules to populate the new common_lookup_table and common_lookup_column columns with data. The rules or steps were:
- Migrate valid table names from the
common_lookup_contextcolumn to thecommon_lookup_tablecolumn. - Migrate a literal
'ADDRESS'value into thecommon_lookup_tablecolumn when thecommon_lookup_contextcolumn holds a'MULTIPLE'string value. - Migrate valid table names from the
common_lookup_contextcolumn to thecommon_lookup_columncolumn by appending a'_TYPE'string literal to thecommon_lookup_contextcolumn values, except for those three rows that have a ‘VISA_CARD’, ‘MASTER_CARD’, or ‘DISCOVER_CARD’. The three exempted rows should update thecommon_lookup_columncolumn with a'CREDIT_CARD_TYPE'string literal. - Migrate a literal
'ADDRESS_TYPE'value into thecommon_lookup_columncolumn when thecommon_lookup_contextcolumn holds a'MULTIPLE'string value. - After these changes, insert two new rows in the
common_lookuptable. They should contain'TELEPHONE'and'TELEPHONE_TYPE'string literal values. One of the rows should contain'HOME'for thecommon_lookup_typecolumn value and the other'WORK'for the same column. - Then, the students were asked to update the foreign key column value in the
telephone_typecolumn of thetelephonetable.
It was a tremendous learning experience for those who did it because there were so many steps required to migrate the structure and data. Years ago, I would offer students half their final grade if they could complete the first four steps in a single UPDATE statement. Occasionally, I had students complete it. We worked through the problem with a small group of students today in one of my two weekly tutoring sessions. I thought it might be nice to document the solution, which use CASE operators in the SET clause of the UPDATE statement.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | UPDATE common_lookup SET common_lookup_table = CASE WHEN NOT common_lookup_context = 'MULTIPLE' THEN common_lookup_context ELSE 'ADDRESS' END , common_lookup_column = CASE WHEN common_lookup_table = 'MEMBER' AND common_lookup_type IN ('VISA_CARD','MASTER_CARD','DISCOVER_CARD') THEN 'CREDIT_CARD_TyPE' WHEN NOT common_lookup_context = 'MULTIPLE' THEN CONCAT(common_lookup_context,'_TYPE') ELSE 'ADDRESS_TYPE' END; |
As a rule, students would solve Step #6, which migrates the foreign key values of the telephone table’s telephone_type column to the new rows inserted into the common_lookup table. Most would accomplish that step with two UPDATE statements. Very few could see how to create a single UPDATE statement for both conditions and migrate from a now obsolete foreign key value that pointed to the rows of the address table’s rows in the common_lookup table to a valid foreign key value pointed to the telephone table’s rows in the common_lookup table:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | UPDATE telephone SET telephone_type = CASE WHEN common_lookup_type = 'HOME' THEN (SELECT common_lookup_id FROM common_lookup WHERE common_lookup_table = 'TELEPHONE' AND common_lookup_type = 'HOME') ELSE (SELECT common_lookup_id FROM common_lookup WHERE common_lookup_table = 'TELEPHONE' AND common_lookup_type = 'WORK') END WHERE telephone_type = (SELECT common_lookup_id FROM common_lookup WHERE common_lookup_table = 'ADDRESS' AND common_lookup_type IN ('HOME','WORK'); |
As always, I hope this helps those looking for new ideas in SQL.
MySQL Floating Types
I’m glad that testing new MySQL releases is so frequent for me. Each testing cycle let me catch warning messages about deprecated behaviors before they’re removed. This one announced the deprecation of digits for floating point data types, like double. The following column definition for a table in my code tree triggered the warning message:
, amount DOUBLE(10,2) |
MySQL 8 (8.0.21) raised the following warning message:
Warning (code 1681): Specifying number of digits for floating point data types is deprecated and will be removed in a future release. |
Recognizing the deprecation, I redefined the column as:
, amount DOUBLE |
I’m glad the MySQL development team is focused on alerting us to deprecations through warning messages. Naturally, I fixed all of the tables proactively. If you want more detail you can read this MySQL web page on Problems with Floating Point Values.
MySQL Shell Parsing
I’ve been experimenting with the mysqlsh since installing it last week. It’s been interesting. Overall, I’m totally impressed but I did find a problem with how it parses stored procedures.
First thought is always, is it my code? I checked the file by running it as a script file through MySQL Workbench. It ran perfectly in MySQL Workbench but failed repeatedly when run from the mysqlsh utility. Next step, reduce the code to a small test case, retest it, and log a bug if it is replicated. My test case in a test.sql file generates the following errors when run from the mysqlsh utility:
MySQL localhost:33060+ ssl studentdb SQL > source test.sql Query OK, 0 rows affected (0.0003 sec) ERROR: 1064: 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 'CREATE PROCEDURE test ( pv_input1 CHAR(12) , pv_input2 CHAR(19)) MODIFIES SQL ' at line 2 Query OK, 0 rows affected (0.0002 sec) Query OK, 0 rows affected (0.0003 sec) ERROR: 1305: PROCEDURE studentdb.test does not exist |
The same file generates this series of successful messages when run as a script file from MySQL Workbench:
Preparing... Importing test.sql... Finished executing script Statement CREATE PROCEDURE test pv_input1 One Operation completed successfully |
For those who are curious enough to review the test case, here it is:
-- Reset the delimiter so that a semicolon can be used as a statement and block terminator. DELIMITER $$ SELECT 'CREATE PROCEDURE test' AS "Statement"; CREATE PROCEDURE test ( pv_input1 CHAR(12) , pv_input2 CHAR(19)) MODIFIES SQL DATA BEGIN SELECT CONCAT(pv_input1,', ',pv_input2) AS message; END; $$ -- Reset the standard delimiter to let the semicolon work as an execution command. DELIMITER ; -- Call the test procedure. CALL test('One','Two'); |
The reply in the bug explained the behavior difference between MySQL Workbench and the MySQL Shell (mysqlsh) environments. MySQL Workbench uses the MySQL client, which supports multiple client statements with the CLIENT_MULTI_STATEMENTS option. Recognizing that, the logging entry SELECT statement should move to a position before setting the DELIMITER, like:
-- Set a label for the log file. SELECT 'CREATE PROCEDURE test' AS "Statement"; -- Reset the delimiter so that a semicolon can be used as a statement and block terminator. DELIMITER $$ CREATE PROCEDURE test ( pv_input1 CHAR(12) , pv_input2 CHAR(19)) MODIFIES SQL DATA BEGIN SELECT CONCAT(pv_input1,', ',pv_input2) AS message; END; $$ -- Reset the standard delimiter to let the semicolon work as an execution command. DELIMITER ; -- Call the test procedure. CALL test('One','Two'); |
The new test case only submits one statement at a time. The logging query is submitted by the semicolon, and the test procedure by the double dollar ($$) symbol set.
So, I was correct identifying a parsing behavior difference between MySQL Workbench and MySQL Shell. It appears to be a difference by design but the MySQL Shell documentation fails to explain it can’t manage multiple statements. I hope identifying this saves others time.
It’s also true that the MySQL client software supports TEE and NOTEE to write log files. Unfortunately, MySQL Shell (mysqlsh) doesn’t support the TEE and NOTEE syntax. You can only do minimal logging with the control of standard error (stderr) by using the application and AdminAPI log utilities, which are covered in Chapter 8 of the MySQL Shell 8.0 documentation.
MySQL Unicode Warning
It’s always interesting when I upgrade from one release to the next. I learn new things, and in the case of MySQL’s installation and maintenance I become more grateful for the great team of developers working to produce MySQL 8.
A warning that caught my eye in MySQL 8 (8.0.21) was this one on Unicode with the utf8 character code:
Warning (code 3719): 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
Currently, a character alias for utf8mb3 is an alias for the deprecated utf8mb3 (a 3-byte character set) until it is removed. When the utf8mb3 character set is removed in a subsequent release the utf8 alias will represent the utf8mb4 (a 4-byte character set), which includes BMP and supplemental character support.
It appears to me that its probably a good time to proactively provision disk space for an upgrade to utf8mb4 (a 4-byte character set). It also probably time to use the utf8mb4 character set rather than the utf8 character set alias. The MySQL Server team wrote a blog entry on the when to use which one. Naturally, all this will have a substantial impact on disk space allocated to the database.
Subscribe via RSS