MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

MySQL macOS Docker

without comments

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 in this command:

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 student user 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 the mysql client on your host macOS. The mysql client lets you connect from the host operating system through the Command-Line Interface (CLI).
  • Create a local student user account inside the Docker container. It will have access to the container file system and mimic the behavior of a non-root user 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 create a non-root 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

You can run the test.sql program as follows form the mysql CLI 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. 😉

Written by maclochlainn

January 11th, 2021 at 10:20 pm

MySQL sakila Database

without comments

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:

  1. Go to https://dev.mysql.com/doc/index-other.html and download the files for the “sakila database” under the Example Database section.
  2. 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).
  3. Type

    source c:\temp\sakila-db\sakila-schema.sql;

    and press enter.

  4. 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.

Written by maclochlainn

January 9th, 2021 at 11:22 am

PL/pgSQL OUT Mode

without comments

A friend asked me a question about using the OUT mode parameter versus INOUT mode parameters in functions. He formed an opinion that they didn’t work in PostgreSQL PL/pgSQL.

Unfortunately, there’s not a log written about how to use the OUT mode parameter in functions. I thought an article showing the standard example with a call to the function might help. The standard example function from the PostgreSQL documentation is:

1
2
3
4
5
6
7
8
9
CREATE OR REPLACE 
  FUNCTION sales_tax( IN  amount  real
                    , OUT tax     real )
  AS $$
BEGIN
  /* Calculate the tax at 6%. */
  tax := amount * 0.06;
END;
$$ LANGUAGE plpgsql;

The RETURNS clause is optional but here’s how you can include it. The following example works exactly like the former.

1
2
3
4
5
6
7
8
9
10
CREATE OR REPLACE 
  FUNCTION sales_tax( IN  amount  real
                    , OUT tax     real )
  RETURNS real AS
$$
BEGIN
  /* Calculate the tax at 6%. */
  tax := amount * 0.06;
END;
$$ LANGUAGE plpgsql;

You call the PostgreSQL sales_tax() function like this:

SELECT 'Sales Tax ['|| sales_tax(200) ||']' AS "Return Value";

It should return the following:

  Return Value  
----------------
 Sales Tax [12]
(1 row)

You can also call it in an inline code block (e.g., what Oracle documentation calls an anonymous block), like:

1
2
3
4
5
6
7
8
9
10
11
12
13
DO
$$
DECLARE
  /* Declare a local variable. */
  tax_paid  real := 0;
BEGIN
  /* Query the results into a local variable. */
  SELECT sales_tax(100) INTO tax_paid;
 
  /* Print the taxes paid. */
  RAISE NOTICE 'Tax Paid [%]',tax_paid;
END;
$$;

You can replace the sales_tax function with its OUT mode tax parameter with the following classic sales_tax function, which adds a tax_rate parameter.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE OR REPLACE
  FUNCTION sales_tax( IN  amount   real
                    , IN  tax_rate real )
  RETURNS real AS $$
DECLARE
  /* Declare a local variable. */
  tax real;
BEGIN
  /* Calculate the tax at 6%. */
  tax := amount * (tax_rate / 100);
 
  /* Return the tax. */
  RETURN tax;
END;
$$ LANGUAGE plpgsql;

Let’s return the original approach with the OUT parameter. Then, let’s expand the list of parameters to include an INOUT mode state variable, like:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE OR REPLACE
  FUNCTION sales_tax( IN     amount   real
                    , IN     tax_rate real
                    , INOUT  state    VARCHAR(14)
                    , OUT    tax      real )
  RETURNS RECORD AS
$$
BEGIN
  /* Calculate the tax at 6%. */
  tax := amount * (tax_rate / 100);
END;
$$ LANGUAGE plpgsql;

You don’t need to include the RETURNS RECORD phrase because PL/pgSQL implements a very mature adapter pattern and it adjusts the return type automatically to the parameter list. On the other hand, many beginning programmers and support staff won’t know that. That’s why I recommend you include it for clarity.

You can call this in a query with a column alias, like:

SELECT 'Sales Tax ['|| sales_tax(100,8.25,'California') ||']' AS "Return Value";

It will return a tuple:

         Return Value          
-------------------------------
 Sales Tax [(California,8.25)]
(1 row)

You can implement it inside an inline block by adding a local variable of the RECORD data type, like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DO
$$
DECLARE
  /* Declare a local variable. */
  state     varchar(14) := 'California';
  tax_paid  real        := 0;
  result    RECORD;
BEGIN
  /* Query the results into a local variable. */
  SELECT sales_tax(100,8.25,state) INTO result;
 
  /* Print the taxes paid. */
  RAISE NOTICE 'Tax Paid [%]', result;
END;
$$;

It prints the following:

NOTICE:  Tax Paid [("(California,8.25)")]

You can actually return the individual members of the tuple by putting the function call inside the FROM clause, like:

SELECT * FROM sales_tax(100,8.25,'California');

It now returns the members of the tuple in separate columns:

   state    | tax  
------------+------
 California | 8.25
(1 row)

Alternatively, you can call it from inside an inline block, like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DO
$$
DECLARE
  /* Declare a local variable. */
  state     varchar(14) := 'California';
  tax_paid  real        := 0;
BEGIN
  /* Query the results into a local variable. */
  SELECT * INTO state, tax_paid FROM sales_tax(100,8.25,state);
 
  /* Print the taxes paid. */
  RAISE NOTICE 'Tax Paid [%] [%]', state, tax_paid;
END;
$$;

It prints:

NOTICE:  Tax Paid [California] [8.25]

Now, let’s rewrite the function into a traditional function with all IN mode variables that returns a RECORD structure with additional values. Just one quick caveat (the big but), you can only assign values to dynamically constructed RECORD structures by using the SELECT-INTO or FOR statements. Below is the refactored sales_tax() function:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE OR REPLACE
  FUNCTION sales_tax( IN  subtotal REAL
                    , IN  tax_rate REAL
                    , IN  state    VARCHAR(14))
  RETURNS RECORD AS
$$
DECLARE
  /* Declare a local variable. */
  tax        REAL;
  tax_record RECORD;
BEGIN
  /* Calculate the tax at 6%. */
  tax := subtotal * (tax_rate / 100);
 
  /* Assign state to record. */
  SELECT state, tax INTO tax_record;
 
  /* Return the tax. */
  RETURN tax_record;
END;
$$ LANGUAGE plpgsql;

It returns the same set of values as the early version with the four parameter example above but you only need three IN-only mode variables to get the result. Other than the parameter lists, the biggest change appears to be the assignment line, which is required in the explicit and traditional function that has only IN mode parameters:

16
  SELECT state, tax INTO tax_record;

Given you can return any RECORD structure you want, why use INOUT and OUT mode parameters? Don’t you loose clarity about what your stored function does? Or, at least, don’t you make understanding the program logic more difficult when you use INOUT and OUT mode variables? The only benefit appears to be when you shift your input variables from the SELECT clause to the INTO clause.

Hopefully, this shows folks how to use the OUT mode parameter; and how closely related it is to a classic function.

Written by maclochlainn

November 25th, 2020 at 12:36 am

MySQL Self-Join

with 4 comments

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'
AND      fp.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
OR       f.prequel_id IS NULL
WHERE    f.series_name = 'Harry Potter'
AND      fp.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 BY clause, 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.

Written by maclochlainn

November 22nd, 2020 at 4:03 pm

Oracle External Table

without comments

Supporting my student labs requires the ability to use external tables. I didn’t have a chance to test external tables when I adopted Oracle’s 18c Docker installations. The following are the instructions for the macOS version, which work. Unfortunately, I couldn’t sort out a way to implement external tables in the Windows version of Oracle’s 18c Docker installation. If somebody knows the way to do that, feel free to drop me a note or a URL to where the information may be found.

These two blog posts explain Oracle’s external tables:

External Tables
External Tables with Preprocessing

These instructions build on my base macOS instructions, which you can find in this earlier Install, configure, and use an Oracle Docker Container blog post. You can find the existing Oracle virtual directories when you connect as the system user and query the dba_directories view. I used the following SQL*Plus formatting commands for the query:

SET PAGESIZE 99
COL directory_name FORMAT A24
COL directory_path FORMAT A54

and the following SQL query:

SELECT directory_name
,      directory_path
FROM   dba_directories;

It returns the following results:

DIRECTORY_NAME		 DIRECTORY_PATH
------------------------ ------------------------------------------------------
ORA_DBMS_FCP_LOGDIR	 /opt/oracle/product/18c/dbhomeXE/cfgtoollogs
SDO_DIR_WORK		 /ade/b/2794046351/oracle/work
SDO_DIR_ADMIN		 /ade/b/2794046351/oracle/md/admin
ORA_DBMS_FCP_ADMINDIR	 /opt/oracle/product/18c/dbhomeXE/rdbms/admin
XMLDIR			 /opt/oracle/product/18c/dbhomeXE/rdbms/xml
XSDDIR			 /opt/oracle/product/18c/dbhomeXE/rdbms/xml/schema
ORACLE_OCM_CONFIG_DIR2	 /opt/oracle/product/18c/dbhomeXE/ccr/state
ORACLE_OCM_CONFIG_DIR	 /opt/oracle/product/18c/dbhomeXE/ccr/state
OPATCH_INST_DIR 	 /opt/oracle/product/18c/dbhomeXE/OPatch
DATA_PUMP_DIR		 /opt/oracle/admin/XE/dpdump/
OPATCH_SCRIPT_DIR	 /opt/oracle/product/18c/dbhomeXE/QOpatch
OPATCH_LOG_DIR		 /opt/oracle/product/18c/dbhomeXE/rdbms/log
ORACLE_BASE		 /opt/oracle
ORACLE_HOME		 /opt/oracle/product/18c/dbhomeXE
 
14 rows selected.

In that prior post, you will find instructions for creating an environment file. The following instructions leverage the $ORACLE_BASE environment variable, which points to the /opt/oracle directory in the Docker environment.

Here are the steps to complete the external file setup, including how to position the physical comma-separated value (CSV) files in the available but otherwise hidden Docker directories. Hidden might be the wrong word choice but they’re not visible from the host macOS operating system. You have to connect to the Docker instance as the root user.

The following Docker command, used in the prior blog post, connects as the root user:

docker exec -it videodb bash

The instructions start before connecting to and after connecting to the Docker instance:

  1. Put the transaction_upload.csv and transaction_upload2.csv files into the following macOS host directory:

    /Users/<installuser>/oracle/student

    which matches to the following internal Docker directory:

    /home/student
  2. As the connected root user, change directory to the $ORACLE_BASE (/opt/oracle) directory with the following command:

    cd $ORACLE_BASE

  3. As the connected root user, make an upload directory inside the $ORACLE_BASE (/opt/oracle) directory with the following command:

    mkdir upload

    As the root user, change the ownership of the upload director from root as the owner and primary group to oracle as the owner and oinstall as the primary group with the following command:

    chown oracle:oinstall upload

  4. As the connected root user, copy the transaction_upload.csv and transaction_upload2.csv files from the /home/student directory to the $ORACLE_BASE (/opt/oracle) directory with the following command:

    cp ~student/transaction_upload*.csv $ORACLE_BASE/upload/.

  5. As the connected root user, change from the $ORACLE_BASE (/opt/oracle) directory to the upload subdirectory with the following command:

    cd upload

    As the root user, change the ownership of the transaction_upload.csv and transaction_upload2.csv files from root as the owner and primary group to oracle as the owner and oinstall as the primary group with the following command:

    chown oracle:oinstall transaction_upload*.csv

  6. As the connected Oracle system user, create the upload virtual directory with the following command:

    CREATE DIRECTORY upload AS '/opt/oracle/upload';

    As the connected Oracle system user, grant read and write privileges to the Oracle c##student user with the following command:

    GRANT READ, WRITE ON DIRECTORY upload TO c##student;

  7. As the connected Oracle c##student user, create the transaction_upload externally managed table with the following command:

    CREATE TABLE transaction_upload
    ( account_number          VARCHAR2(10)
    , first_name              VARCHAR2(20)
    , middle_name             VARCHAR2(20)
    , last_name               VARCHAR2(20)
    , check_out_date          DATE
    , return_date             DATE
    , rental_item_type        VARCHAR2(12)
    , transaction_type        VARCHAR2(14)
    , transaction_amount      NUMBER
    , transaction_date        DATE
    , item_id                 NUMBER
    , payment_method_type     VARCHAR2(14)
    , payment_account_number  VARCHAR2(19))
      ORGANIZATION EXTERNAL
      ( TYPE oracle_loader
        DEFAULT DIRECTORY upload
        ACCESS PARAMETERS
        ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
          BADFILE     'UPLOAD':'transaction_upload.bad'
          DISCARDFILE 'UPLOAD':'transaction_upload.dis'
          LOGFILE     'UPLOAD':'transaction_upload.log'
          FIELDS TERMINATED BY ','
          OPTIONALLY ENCLOSED BY "'"
          MISSING FIELD VALUES ARE NULL )
        LOCATION ('transaction_upload.csv'))
    REJECT LIMIT UNLIMITED;

    As the connected Oracle c##student user, query the transaction_upload table to verify that you can read the external file source through the virtual upload directory.

    COL  record  FORMAT 99,999  HEADING "Record|Count"
    SELECT TO_CHAR(COUNT(*),'99,999') AS record
    FROM   transaction_upload;

    It should return the following value:

    Record
    Count
    -------
     11,520

  8. As the connected Oracle c##student user, create the transaction_reversal externally managed table with the following command:

    CREATE TABLE transaction_reversal
    ( transaction_id          NUMBER
    , transaction_account     VARCHAR2(15)
    , transaction_type        VARCHAR2(30)
    , transaction_date        DATE
    , transaction_amount      NUMBER
    , rental_id               NUMBER
    , payment_method_type     NUMBER
    , payment_account_number  VARCHAR2(20)
    , created_by              NUMBER
    , creation_date           DATE
    , last_updated_by         NUMBER
    , last_update_date        DATE)
      ORGANIZATION EXTERNAL
      ( TYPE oracle_loader
        DEFAULT DIRECTORY upload
        ACCESS PARAMETERS
        ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
          BADFILE     'UPLOAD':'transaction_upload2.bad'
          DISCARDFILE 'UPLOAD':'transaction_upload2.dis'
          LOGFILE     'UPLOAD':'transaction_upload2.log'
          FIELDS TERMINATED BY ','
          OPTIONALLY ENCLOSED BY "'"
          MISSING FIELD VALUES ARE NULL )
        LOCATION ('transaction_upload2.csv'))
    REJECT LIMIT UNLIMITED;

    As the connected Oracle c##student user, query the transaction_reversal table to verify that you can read the external file source through the virtual upload directory.

    COL  record  FORMAT 99,999  HEADING "Record|Count"
    SELECT TO_CHAR(COUNT(*),'99,999') AS record
    FROM   transaction_reversal;

    It should return the following value:

    Record
    Count
    -------
      1,170

Unfortunately, the file permission on the Windows version of the Oracle Docker 18c installation make it more difficult to install.

Written by maclochlainn

November 14th, 2020 at 10:57 pm

MySQL Client in 8.0.21+

without comments

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.

Written by maclochlainn

November 8th, 2020 at 3:19 pm

Installing PL/Python Extension

without comments

While PL/Python is an untrusted language inside PostgreSQL, I was installing it to test some of its features. First, we check to see if PL/Python is installed by attempting to create a PL/Python function:

CREATE FUNCTION pima(a integer, b integer)
RETURNS integer AS
$$
if a > b:
  return a
return b
$$ LANGUAGE plpython3u;

It likely should raise an error like this because PL/Python is an untrusted language. It’s untrusted because it runs with root privileges rather than a restricted user’s privilege. More or less, with PL/Python you can access the entire database. This makes PL/Python more of a threat than tool beyond experimentation in a test database.

ERROR:  language "plpython3u" does not exist
HINT:  Use CREATE EXTENSION to load the language into the database.

A quick query as the postgres user tells you whether or not PL/Python is installed:

SELECT * FROM pg_language;

It returns the following:

 lanname  | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl 
----------+----------+---------+--------------+---------------+-----------+--------------+--------
 internal |       10 | f       | f            |             0 |         0 |         2246 | 
 c        |       10 | f       | f            |             0 |         0 |         2247 | 
 sql      |       10 | f       | t            |             0 |         0 |         2248 | 
 plpgsql  |       10 | t       | t            |         14088 |     14089 |        14090 | 
(4 rows)

I attempted to add PL/Python with the following command:

CREATE EXTENSION plpython3u;

It raised the following error:

ERROR:  could not open extension control file "/usr/pgsql-11/share/extension/plpython3u.control": No such file or directory

The error basically appeared to occur because there’s a missing Python 3 package from what I could find on the web. I installed the missing postgresql-plpython3 package as the root superuser, which you also could install as a member of the sudoer list. The following shows how to install it as a sudoer member:

sudo yum install -y postgresql-plpython3

You should see something close to the following console output:

Last metadata expiration check: 0:43:53 ago on Fri 06 Nov 2020 10:42:28 AM MST.
Dependencies resolved.
============================================================================================================
 Package                           Architecture        Version                   Repository            Size
============================================================================================================
Installing:
 postgresql-plpython3              x86_64              11.7-2.fc30               updates               86 k
Installing dependencies:
 postgresql-server                 x86_64              11.7-2.fc30               updates              5.3 M
 
Transaction Summary
============================================================================================================
Install  2 Packages
 
Total download size: 5.3 M
Installed size: 23 M
Downloading Packages:
(1/2): postgresql-plpython3-11.7-2.fc30.x86_64.rpm                          218 kB/s |  86 kB     00:00    
(2/2): postgresql-server-11.7-2.fc30.x86_64.rpm                             3.0 MB/s | 5.3 MB     00:01    
------------------------------------------------------------------------------------------------------------
Total                                                                       2.1 MB/s | 5.3 MB     00:02     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                    1/1 
  Running scriptlet: postgresql-server-11.7-2.fc30.x86_64                                               1/2 
  Installing       : postgresql-server-11.7-2.fc30.x86_64                                               1/2 
warning: /var/lib/pgsql/.bash_profile created as /var/lib/pgsql/.bash_profile.rpmnew
 
  Running scriptlet: postgresql-server-11.7-2.fc30.x86_64                                               1/2 
  Installing       : postgresql-plpython3-11.7-2.fc30.x86_64                                            2/2 
  Running scriptlet: postgresql-plpython3-11.7-2.fc30.x86_64                                            2/2 
  Verifying        : postgresql-plpython3-11.7-2.fc30.x86_64                                            1/2 
  Verifying        : postgresql-server-11.7-2.fc30.x86_64                                               2/2 
 
Installed:
  postgresql-plpython3-11.7-2.fc30.x86_64                postgresql-server-11.7-2.fc30.x86_64               
 
Complete!

While I thought this might fix the problem, it didn’t and raised the following error:

ERROR:  could not open extension control file "/usr/pgsql-11/share/extension/plpython3u.control": No such file or directory

I found the plpython3u.control file on GitHub and put the following plpython3u.control file, after comparing it against the plpgsql.control file, into the /usr/pgsql-11/share/extension directory. At this point, I began wondering why it’s looking in the /usr/pgsql-11/share/extension directory instead of a /usr/plpython3u/share/extension directory (does not exist).

# plpython3u extension
comment = 'PL/Python3U untrusted procedural language'
default_version = '1.0'
module_pathname = '$libdir/plpython3'
relocatable = false
schema = pg_catalog
superuser = true

I retried creating the plpython3u extension:

CREATE EXTENSION plpython3u;

It raised the following error:

ERROR:  extension "plpython3u" has no installation script nor update path for version "1.0"

At this point, I could find no further help on the Internet. I did notice that there were these two *.sql files in the /usr/pgsql-11/share/extension directory:

  • plpgsql–1.0.sql
  • plpgsql–unpackaged–1.0.sql

I found this plpython3u--1.0.sql file on GitHub:

/* src/pl/plpython/plpython3u--1.0.sql */
 
CREATE FUNCTION plpython3_call_handler() RETURNS language_handler
  LANGUAGE c AS 'MODULE_PATHNAME';
 
CREATE FUNCTION plpython3_inline_handler(internal) RETURNS void
  STRICT LANGUAGE c AS 'MODULE_PATHNAME';
 
CREATE FUNCTION plpython3_validator(oid) RETURNS void
  STRICT LANGUAGE c AS 'MODULE_PATHNAME';
 
CREATE LANGUAGE plpython3u
  HANDLER plpython3_call_handler
  INLINE plpython3_inline_handler
  VALIDATOR plpython3_validator;
 
COMMENT ON LANGUAGE plpython3u IS 'PL/Python3U untrusted procedural language';

I retried creating the plpython3u extension, as a member of the sudoer list and got a new error:

ERROR:  permission denied to create extension "plpython3u"
HINT:  Must be superuser to create this extension.

I retried creating the plpython3u extension as the postgres user, who is the owning user:

CREATE EXTENSION plpython3u;

It raised the following error:

ERROR:  could not access file "$libdir/plpython3": No such file or directory

While I intend to finish this, that’s as far as I got. It appears from some of the things I’ve read I need to recompile or configure items that would destabilize what I have working at the moment. Finishing this will need to wait for me to build another test environment from scratch. If you catch this post and know the remaining steps, I invite you to add them in the comments.

Written by maclochlainn

November 8th, 2020 at 1:54 pm

Conditional Updates

without comments

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_table
  • common_lookup_column
  • common_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:

  1. Migrate valid table names from the common_lookup_context column to the common_lookup_table column.
  2. Migrate a literal 'ADDRESS' value into the common_lookup_table column when the common_lookup_context column holds a 'MULTIPLE' string value.
  3. Migrate valid table names from the common_lookup_context column to the common_lookup_column column by appending a '_TYPE' string literal to the common_lookup_context column values, except for those three rows that have a ‘VISA_CARD’, ‘MASTER_CARD’, or ‘DISCOVER_CARD’. The three exempted rows should update the common_lookup_column column with a 'CREDIT_CARD_TYPE' string literal.
  4. Migrate a literal 'ADDRESS_TYPE' value into the common_lookup_column column when the common_lookup_context column holds a 'MULTIPLE' string value.
  5. After these changes, insert two new rows in the common_lookup table. They should contain 'TELEPHONE' and 'TELEPHONE_TYPE' string literal values. One of the rows should contain 'HOME' for the common_lookup_type column value and the other 'WORK' for the same column.
  6. Then, the students were asked to update the foreign key column value in the telephone_type column of the telephone table.

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.

Written by maclochlainn

November 5th, 2020 at 11:03 pm

Quick Python Flask

without comments

A quick example of installing and running a sample hello.py file with the Flask application with the Command Line Interface (CLI) documentation on the Flask website. The first thing you need to do is install the flask module with the pip3 utility on Fedora Linux (or most other platforms):

pip3 install flask --user student

You should see a successful log like this for student user:

Requirement already satisfied: flask in /usr/local/lib64/python3.7/site-packages (1.1.2)
Collecting student
  Downloading https://files.pythonhosted.org/packages/b5/af/be416c18e4fe63a582e06fb0d47bf059bd0f4f413e5a6cfe893747ebaf79/Student-0.0.1-py3-none-any.whl
Requirement already satisfied: click>=5.1 in /usr/lib/python3.7/site-packages (from flask) (7.1.1)
Requirement already satisfied: itsdangerous>=0.24 in /usr/local/lib/python3.7/site-packages (from flask) (1.1.0)
Requirement already satisfied: Werkzeug>=0.15 in /usr/local/lib/python3.7/site-packages (from flask) (1.0.1)
Requirement already satisfied: Jinja2>=2.10.1 in /usr/lib/python3.7/site-packages (from flask) (2.10.1)
Requirement already satisfied: MarkupSafe>=0.23 in /usr/lib64/python3.7/site-packages (from Jinja2>=2.10.1->flask) (1.1.1)
Installing collected packages: student
Successfully installed student-0.0.1

The smallest footprint hello.py program is:

# Import statement.
from flask import Flask
 
# Application defintion.
app = Flask(__name__)
 
# Basic URI rotuing.
@app.route("/")
 
# Define a hello function.
def hello():
  return "Hello World!"
 
# Run the program.
if __name__ == "__main__":
  app.run()

You can run the program from the command line with two commands:

export FLASK_APP=hello.py
flask run

It will show you the following in the terminal session:

 * Serving Flask app "hello.py"
 * Environment: production
   WARNING: This is a development server. Do not use it in a production deployment.
   Use a production WSGI server instead.
 * Debug mode: off
 * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)

Type the following URL in a local browser:

http://localhost:5000/

Flask will add the following to the console log:

127.0.0.1 - - [26/Oct/2020 00:37:49] "GET / HTTP/1.1" 200 -

You can jazz the hello.py program up with a an argument list, like this modified example based on this comparison article on Python frameworks:

# Import libraries.
from flask import Flask, escape, request
 
# Define the application.
app = Flask(__name__)
 
# Define a base URI route and function.
@app.route('/')
def index():
  return "Index Page"
 
# Define an application URI route and function.
@app.route("/hello")
def hello():
  name = request.args.get("name","Simon")
  return f'Hello {escape(name)}!'
 
# Define an about URI route and function.
@app.route("/about")
def about():
  return "About Page"
 
# Run the file.
if __name__ == "__main__":
  app.run()

It prints 'Hello Simon!' in a web page. If you try to stop your Flask server with a Ctrl+Z instead of a Ctrl+C, the next time you go to start it you will receive an error message like:

OSError: [Errno 98] Address already in use

You’ll need to find the Linux process ID and kill the process with prejudice. You can find the process with the following Linux command:

sudo netstat -nlp | grep 5000
[sudo] password for student:

It prompts you for your sudoer password, and then returns a line like:

tcp        0      0 127.0.0.1:5000          0.0.0.0:*               LISTEN      76802/python3

You kill the process with the following Linux command:

kill -9 76802

As always, I hope this helps those looking for the missing pieces.

Written by maclochlainn

October 26th, 2020 at 12:54 am

MySQL Floating Types

with 2 comments

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.

Written by maclochlainn

October 8th, 2020 at 12:06 pm

Posted in MySQL,MySQL 8,sql

Tagged with ,