MySQL Workbench Keys
As I teach students how to create tables in MySQL Workbench, it’s always important to review the meaning of the checkbox keys. Then, I need to remind them that every table requires a natural key from our prior discussion on normalization. I explain that a natural key is a compound candidate key (made up of two or more column values), and that it naturally defines uniqueness for each row in a table.
Then, we discuss surrogate keys, which are typically ID column keys. I explain that surrogate keys are driven by sequences in the database. While a number of databases disclose the name of sequences, MySQL treats the sequence as an attribute of the table. In Object-Oriented Analysis and Design (OOAD), that makes the sequence a member of the table by composition rather than aggregation. Surrogate keys are also unique in the table but should never be used to determine uniqueness like the natural key. Surrogate keys are also candidate keys, like a VIN number uniquely identifies a vehicle.
In a well designed table you always have two candidate keys: One describes the unique row and the other assigns a number to it. While you can perform joins by using either candidate key, you always should use the surrogate key for joins statements. This means you elect, or choose, the surrogate candidate key as the primary key. Then, you build a unique index for the natural key, which lets you query any unique row with human decipherable words.
The column attribute table for MySQL Workbench is:
| Key | Meaning |
|---|---|
| PK | Designates a primary key column. |
| NN | Designates a not-null column constraint. |
| UQ | Designates a column contains a unique value for every row. |
| BIN | Designates a VARCHAR data type column so that its values are stored in a case-sensitive fashion. You can’t apply this constraint to other data types. |
| UN | Designates a column contains an unsigned numeric data type. The possible values are 0 to the maximum number of the data type, like integer, float, or double. The value 0 isn’t possible when you also select the PK and AI check boxes, which ensures the column automatically increments to the maximum value of the column. |
| ZF | Designates a zero fill populates zeros in front of any number data type until all space is consumed, which acts like a left pad function with zeros. |
| AI | Designates AUTO_INCREMENT and should only be checked for a surrogate primary key value. |
All surrogate key columns should check the PK, NN, UN, and AI checkboxes. The default behavior checks only the PK and NN checkboxes and leaves the UN and AI boxes unchecked. You should also click the UN checkbox with the AI checkbox for all surrogate key columns. The AI checkbox enables AUTO_INCREMENT behavior. The UN checkbox ensure you have the maximum number of integers before you would migrate the table to a double precision number. More or less, this is what I wrote in MySQL Workbench Data Modeling & Development as the primary product guide in 2013, and what you find in the MySQL Workbench Manual 8.1.10.2 Columns Tab section.
Active tables grow quickly and using a signed int means you run out of rows more quickly. This is an important design consideration because using a unsigned int adds a maintenance task later. The maintenance task will require changing the data type of all dependent foreign key columns before changing the primary key column’s data type. Assuming you’re design uses referential integrity constraints, implemented as a foreign keys, you will need to:
- Remove any foreign key constraints before changing the referenced primary key and dependent foreign key column’s data types.
- Change the primary and foreign key column’s data types.
- Add back foreign key constraints after changing the referenced primary key and dependent foreign key column’s data types.
While fixing a less optimal design is a relatively simple scripting exercise for most data engineers, you can avoid this maintenance task. Implement all surrogate primary key columns and foreign key columns with the signed int as their initial data type.
The following small ERD displays a multi-language lookup table, which is preferable to a monolinquistic enum data type.:
A design uses a lookup table when there are known lists of selections to make. There are known lists that occur in most if not all business applications. Maintaining that list of values is an application setup task and requires the development team to build an entry and update form to input and maintain the lists.
While some MySQL examples demonstrate these types of lists by using the MySQL enum data type. However, the MySQL enum type doesn’t support multilingual implementations, isn’t readily portable to other relational database, and has a number of limitations.
A lookup table is the better solution to using an enum data type. It typically follows this pattern:
- Identify the target table and column where a list is useful. Use the table_name and column_name columns as a super key to identify the location where the list belongs.
- Identify a unique type identifier for the list. Store the unique type value in the type column of the lookup table.
- Use a lang column to enable multilingual lists.
The combination of the table_name, column_name, type, and lang let you identify unique sets. You can find a monolingual implementation in these two older blog posts:
The column view of the lookup table shows the appropriate design checkboxes:
While most foreign keys use copies of surrogate keys, there are instances when you copy the natural key value from another table rather than the surrogate key. This is done when your application will frequently query the dependent lookup table without a join to the lang table, which means the foreign key value should be a human friendly foreign key value that works as a super key.
A super key is a column or set of columns that uniquely identifies a rows in the scope of a relation. For this example, the lang column identifies rows that belong to a language in a multilingual data model. Belonging to a language is the relation between the lookup and language table. It is also a key when filtering rows with a specific lang value from the lookup table.
You navigate to the foreign key tab to create a lookup_fk foreign key constraint, like:
With this type of foreign key constraint, you copy the lang value from the language table when inserting the lookup table values. Then, your HTML forms can use the lookup table’s meaning column in any of the supported languages, like:
SELECT lookup_id , type , meaning FROM lookup WHERE table_name = 'some_table_name' AND column_name = 'some_column_name' AND lang = 'some_lang_name'; |
The type column value isn’t used in the WHERE clause to filter the data set because it is unique within the relation of the table_name, column_name, and lang column values. It is always non-unique when you exclude the lang column value, and potentially non-unique for another combination of the table_name and column_name column values.
As a rule, most foreign key references are to the lookup table’s surrogate primary key because the meaning column’s value is too long to copy into the referencing table or subject to change in the base or translated languages. Small values, with intrinsic meaning, are stored in a code column in many implementations, like the lang column. Those typically follow the same implementation rule as the lang column and are copied into the referencing table.
If I’ve left questions, let me know. Other wise, I hope this helps qualify a best design practice.
Debugging PL/SQL Functions
Teaching student how to debug a PL/SQL function takes about an hour now. I came up with the following example of simple deterministic function that adds three numbers and trying to understand how PL/SQL implicitly casts data types. The lecture follows a standard Harvard Case Study, which requires the students to suggest next steps. The starting code is:
1 2 3 4 5 6 7 8 9 10 | CREATE OR REPLACE FUNCTION adding ( a DOUBLE PRECISION , b INTEGER , c DOUBLE PRECISION ) RETURN INTEGER DETERMINISTIC IS BEGIN RETURN a + b + c; END; / |
Then, we use one test case for two scenarios:
SELECT adding(1.25, 2, 1.24) AS "Test Case 1" , adding(1.25, 2, 1.26) AS "Test Case 2" FROM dual; |
It returns:
Test Case 1 Test Case 2
----------- -----------
4 5 |
Then, I ask why does that work? Somehow many students can’t envision how it works. Occasionally, a student will say it must implicitly cast the INTEGER to a DOUBLE PRECISION data type and add the numbers as DOUBLE PRECISION values before down-casting it to an INTEGER data type.
Whether I have to explain it or a student volunteers it, the next question is: “How would you build a test case to see if the implicit casting?” Then, I ask them to take 5-minutes and try to see how the runtime behaves inside the function.
At this point in the course, they only know how to use dbms_output.put_line to print content from anonymous blocks. So, I provide them with a modified adding function:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | CREATE OR REPLACE FUNCTION adding ( a DOUBLE PRECISION , b INTEGER , c DOUBLE PRECISION ) RETURN INTEGER DETERMINISTIC IS /* Define a double precision temporary result variable. */ temp_result NUMBER; /* Define an integer return variable. */ temp_return INTEGER; BEGIN /* * Perform the calculation and assign the value to the temporary * result variable. */ temp_result := a + b + c; /* * Assign the temporary result variable to the return variable. */ temp_return := temp_result; /* Return the integer return variable as the function result. */ RETURN temp_return; END; / |
The time limit ensures they spend their time typing the code from the on screen display and limits testing to the dbms_output.put_line attempt. Any more time and one or two of them would start using Google to find an answer.
I introduce the concept of a Black Box as their time expires, and typically use an illustration like the following to explain that by design you can’t see inside runtime operations of functions. Then, I teach them how to do exactly that.
You can test the runtime behaviors and view the variable values of functions by doing these steps:
- Create a debug table, like
CREATE TABLE debug ( msg VARCHAR2(200));
- Make the function into an autonomous transaction by:
- Adding the PRAGMA (or precompiler) instruction in the declaration block.
- Adding a COMMIT at the end of the execution block.
- Use an INSERT statement to write descriptive text with the variable values into the debug table.
Here’s the refactored test code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | CREATE OR REPLACE FUNCTION adding ( a DOUBLE PRECISION , b INTEGER , c DOUBLE PRECISION ) RETURN INTEGER DETERMINISTIC IS /* Define a double precision temporary result variable. */ temp_result NUMBER; /* Define an integer return variable. */ temp_return INTEGER; /* Precompiler Instrunction. */ PRAGMA AUTONOMOUS_TRANSACTION; BEGIN /* * Perform the calculation and assign the value to the temporary * result variable. */ temp_result := a + b + c; /* Insert the temporary result variable into the debug table. */ INSERT INTO debug (msg) VALUES ('Temporary Result Value: ['||temp_result||']'); /* * Assign the temporary result variable to the return variable. */ temp_return := temp_result; /* Insert the temporary result variable into the debug table. */ INSERT INTO debug (msg) VALUES ('Temporary Return Value: ['||temp_return||']'); /* Commit to ensure the write succeeds in a separate process scope. */ COMMIT; /* Return the integer return variable as the function result. */ RETURN temp_return; END; / |
While an experienced PL/SQL developer might ask while not introduce conditional computation, the answer is that’s for another day. Most students need to uptake pieces before assembling pieces and this example is already complex for a newbie.
The same test case works (shown to avoid scrolling up):
SELECT adding(1.25, 2, 1.24) AS "Test Case 1" , adding(1.25, 2, 1.26) AS "Test Case 2" FROM dual; |
It returns:
Test Case 1 Test Case 2
----------- -----------
4 5 |
Now, they can see the internal step-by-step values with this query:
COL msg FORMAT A30 HEADING "Internal Variable Auditing" SELECT msg FROM debug; |
It returns:
Internal Variable Auditing ------------------------------ Temporary Result Value: [4.49] Temporary Return Value: [4] Temporary Result Value: [4.51] Temporary Return Value: [5] 4 rows selected. |
What we learn is that:
- Oracle PL/SQL up-casts the b variable from an integer to a double precision data type before adding the three input variables.
- Oracle PL/SQL down-casts the sum of the three input variables from a double precision data type to an integer by applying traditionally rounding.
I hope this helps those trying to understand implicit casting and discovering how to unhide an opaque function’s operations for debugging purposes.
Oracle PLS-00103 Gotcha
Teaching PL/SQL can be fun and sometimes challenging when you need to troubleshoot a student error. Take the Oracle PLS-00103 error can be very annoying when it return like this:
24/5 PLS-00103: Encountered the symbol "LV_CURRENT_DATE" WHEN expecting one OF the following: language |
Then, you look at the code and see:
22 23 24 25 | , pv_user_id NUMBER ) IS /* Declare local constants. */ lv_current_date DATE := TRUNC(SYSDATE); |
Obviously, there’s nothing wrong on the line number that the error message pointed. Now, here’s where it gets interesting because of a natural human failing. The student thought they had something wrong with declaring the variable and tested as stand alone procedure and anonymous block. Naturally, they were second guessing what they knew about the PL/SQL.
That’s when years of experience with PL/SQL kicks in to solve the problem. The trick is recognizing two things:
- The error message points to the first line of code in a package body.
- The error is pointing to the first character on the line after the error.
That meant that the package body was incorrectly defined. A quick check to the beginning of the package body showed:
1 2 3 4 5 6 | CREATE OR REPLACE PACKAGE account_creation AS PROCEDURE insert_contact ( pv_first_name VARCHAR2 , pv_middle_name VARCHAR2 := NULL |
The student failed to designate the package as an implementation by omitting the keyword BODY from line 2. The proper definition of the package body should be:
1 2 3 4 5 6 | CREATE OR REPLACE PACKAGE BODY account_creation AS PROCEDURE insert_contact ( pv_first_name VARCHAR2 , pv_middle_name VARCHAR2 := NULL |
That’s the resolution for the error message. I wrote this because I checked if they should have been able to find a helpful article with a google search. I discovered that there wasn’t an answer like this that came up after 10 minutes of various searches.
As always, I hope this helps those writing PL/SQL.
PL/SQL Overloading
So, I wrote an updated example of my grandma and tweetie_bird for my students. It demonstrates overloading with the smallest parameter lists possible across a transaction of two tables. It also shows how one version of the procedure can call another version of the procedure.
The tables are created with the following:
/* Conditionally drop grandma table and grandma_s sequence. */ BEGIN FOR i IN (SELECT object_name , object_type FROM user_objects WHERE object_name IN ('GRANDMA','GRANDMA_SEQ')) LOOP IF i.object_type = 'TABLE' THEN /* Use the cascade constraints to drop the dependent constraint. */ EXECUTE IMMEDIATE 'DROP TABLE '||i.object_name||' CASCADE CONSTRAINTS'; ELSE EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.object_name; END IF; END LOOP; END; / /* Create the table. */ CREATE TABLE GRANDMA ( grandma_id NUMBER CONSTRAINT grandma_nn1 NOT NULL , grandma_house VARCHAR2(30) CONSTRAINT grandma_nn2 NOT NULL , created_by NUMBER CONSTRAINT grandma_nn3 NOT NULL , CONSTRAINT grandma_pk PRIMARY KEY (grandma_id) ); /* Create the sequence. */ CREATE SEQUENCE grandma_seq; /* Conditionally drop a table and sequence. */ BEGIN FOR i IN (SELECT object_name , object_type FROM user_objects WHERE object_name IN ('TWEETIE_BIRD','TWEETIE_BIRD_SEQ')) LOOP IF i.object_type = 'TABLE' THEN EXECUTE IMMEDIATE 'DROP TABLE '||i.object_name||' CASCADE CONSTRAINTS'; ELSE EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.object_name; END IF; END LOOP; END; / /* Create the table with primary and foreign key out-of-line constraints. */ CREATE TABLE TWEETIE_BIRD ( tweetie_bird_id NUMBER CONSTRAINT tweetie_bird_nn1 NOT NULL , tweetie_bird_house VARCHAR2(30) CONSTRAINT tweetie_bird_nn2 NOT NULL , grandma_id NUMBER CONSTRAINT tweetie_bird_nn3 NOT NULL , created_by NUMBER CONSTRAINT tweetie_bird_nn4 NOT NULL , CONSTRAINT tweetie_bird_pk PRIMARY KEY (tweetie_bird_id) , CONSTRAINT tweetie_bird_fk FOREIGN KEY (grandma_id) REFERENCES GRANDMA (GRANDMA_ID) ); /* Create sequence. */ CREATE SEQUENCE tweetie_bird_seq; |
The sylvester package specification holds the two overloaded procedures, like:
CREATE OR REPLACE PACKAGE sylvester IS /* Three variable length strings. */ PROCEDURE warner_brother ( pv_grandma_house VARCHAR2 , pv_tweetie_bird_house VARCHAR2 , pv_system_user_name VARCHAR2 ); /* Two variable length strings and a number. */ PROCEDURE warner_brother ( pv_grandma_house VARCHAR2 , pv_tweetie_bird_house VARCHAR2 , pv_system_user_id NUMBER ); END sylvester; / |
The sylvester package implements two warner_brother procedures. One takes the system user’s ID and the other takes the system user’s name. The procedure that accepts the system user name queries the system_user table with the system_user_name to get the system_user_id column and then calls the other version of itself. This demonstrates how you only write logic once when overloading and let one version call the other with the added information.
Here’s the sylvester package body code:
CREATE OR REPLACE PACKAGE BODY sylvester IS /* Procedure warner_brother with user name. */ PROCEDURE warner_brother ( pv_grandma_house VARCHAR2 , pv_tweetie_bird_house VARCHAR2 , pv_system_user_id NUMBER ) IS /* Declare a local variable for an existing grandma_id. */ lv_grandma_id NUMBER; FUNCTION get_grandma_id ( pv_grandma_house VARCHAR2 ) RETURN NUMBER IS /* Initialized local return variable. */ lv_retval NUMBER := 0; -- Default value is 0. /* A cursor that lookups up a grandma's ID by their name. */ CURSOR find_grandma_id ( cv_grandma_house VARCHAR2 ) IS SELECT grandma_id FROM grandma WHERE grandma_house = cv_grandma_house; BEGIN /* Assign a grandma_id as the return value when a row exists. */ FOR i IN find_grandma_id(pv_grandma_house) LOOP lv_retval := i.grandma_id; END LOOP; /* Return 0 when no row found and the grandma_id when a row is found. */ RETURN lv_retval; END get_grandma_id; BEGIN /* Set the savepoint. */ SAVEPOINT starting; /* * Identify whether a member account exists and assign it's value * to a local variable. */ lv_grandma_id := get_grandma_id(pv_grandma_house); /* * Conditionally insert a new member account into the member table * only when a member account does not exist. */ IF lv_grandma_id = 0 THEN /* Insert grandma. */ INSERT INTO grandma ( grandma_id , grandma_house , created_by ) VALUES ( grandma_seq.NEXTVAL , pv_grandma_house , pv_system_user_id ); /* Assign grandma_seq.currval to local variable. */ lv_grandma_id := grandma_seq.CURRVAL; END IF; /* Insert tweetie bird. */ INSERT INTO tweetie_bird ( tweetie_bird_id , tweetie_bird_house , grandma_id , created_by ) VALUES ( tweetie_bird_seq.NEXTVAL , pv_tweetie_bird_house , lv_grandma_id , pv_system_user_id ); /* If the program gets here, both insert statements work. Commit it. */ COMMIT; EXCEPTION /* When anything is broken do this. */ WHEN OTHERS THEN /* Until any partial results. */ ROLLBACK TO starting; END; PROCEDURE warner_brother ( pv_grandma_house VARCHAR2 , pv_tweetie_bird_house VARCHAR2 , pv_system_user_name VARCHAR2 ) IS /* Define a local variable. */ lv_system_user_id NUMBER := 0; FUNCTION get_system_user_id ( pv_system_user_name VARCHAR2 ) RETURN NUMBER IS /* Initialized local return variable. */ lv_retval NUMBER := 0; -- Default value is 0. /* A cursor that lookups up a grandma's ID by their name. */ CURSOR find_system_user_id ( cv_system_user_id VARCHAR2 ) IS SELECT system_user_id FROM system_user WHERE system_user_name = pv_system_user_name; BEGIN /* Assign a grandma_id as the return value when a row exists. */ FOR i IN find_system_user_id(pv_system_user_name) LOOP lv_retval := i.system_user_id; END LOOP; /* Return 0 when no row found and the grandma_id when a row is found. */ RETURN lv_retval; END get_system_user_id; BEGIN /* Convert a system_user_name to system_user_id. */ lv_system_user_id := get_system_user_id(pv_system_user_name); /* Call the warner_brother procedure. */ warner_brother ( pv_grandma_house => pv_grandma_house , pv_tweetie_bird_house => pv_tweetie_bird_house , pv_system_user_id => lv_system_user_id ); EXCEPTION /* When anything is broken do this. */ WHEN OTHERS THEN /* Until any partial results. */ ROLLBACK TO starting; END; END sylvester; / |
The following anonymous block test case works with the code:
BEGIN sylvester.warner_brother( pv_grandma_house => 'Blue House' , pv_tweetie_bird_house => 'Cage' , pv_system_user_name => 'DBA 3' ); sylvester.warner_brother( pv_grandma_house => 'Blue House' , pv_tweetie_bird_house => 'Tree House' , pv_system_user_id => 4 ); END; / |
You can now query the results with this SQL*PLus formatting and query:
/* Query results from warner_brother procedure. */ COL grandma_id FORMAT 9999999 HEADING "Grandma|ID #" COL grandma_house FORMAT A14 HEADING "Grandma House" COL created_by FORMAT 9999999 HEADING "Created|By" COL tweetie_bird_id FORMAT 9999999 HEADING "Tweetie|Bird ID" COL tweetie_bird_house FORMAT A18 HEADING "Tweetie Bird House" SELECT * FROM grandma g INNER JOIN tweetie_bird tb ON g.grandma_id = tb.grandma_id; |
You should see the following data:
Grandma Created Tweetie Grandma Created
ID # Grandma House By Bird ID Tweetie Bird House ID # By
-------- -------------- -------- -------- ------------------ -------- --------
1 Blue House 3 1 Cage 1 3
1 Blue House 3 2 Tree House 1 4
As always, I hope complete code samples help solve real problems.
PostgreSQL Trigger 1
This entry covers how to write a statement logging trigger for PostgreSQL. It creates two tables: avenger and avenger_log; one avenger_t1 trigger, and a testing INSERT statement.
It was written to help newbies know how and what to return from a function written for a statement-level trigger. They often get stuck on the following when they try to return true. The term non-composite is another way to describe the tuple inserted.
psql:basics_postgres.sql: 59: ERROR: cannot return non-composite value from function returning composite type CONTEXT: PL/pgSQL function write_avenger_t1() line 15 at RETURN |
The avenger table:
/* Conditionally drop table. */ DROP TABLE IF EXISTS avenger; /* Create table. */ CREATE TABLE avenger ( avenger_id SERIAL , avenger_name VARCHAR(30) , first_name VARCHAR(20) , last_name VARCHAR(20)); |
Seed the avenger table:
/* Seed the avenger table with data. */ INSERT INTO avenger ( first_name, last_name, avenger_name ) VALUES ('Anthony', 'Stark', 'Iron Man') ,('Thor', 'Odinson', 'God of Thunder') ,('Steven', 'Rogers', 'Captain America') ,('Bruce', 'Banner', 'Hulk') ,('Clinton', 'Barton', 'Hawkeye') ,('Natasha', 'Romanoff', 'Black Widow') ,('Peter', 'Parker', 'Spiderman') ,('Steven', 'Strange', 'Dr. Strange') ,('Scott', 'Lange', 'Ant-man'); |
The avenger_log table:
/* Conditionally drop table. */ DROP TABLE IF EXISTS avenger_log; /* Create table. */ CREATE TABLE avenger_log ( avenger_log_id SERIAL , trigger_name VARCHAR(30) , trigger_timing VARCHAR(6) , trigger_event VARCHAR(6) , trigger_type VARCHAR(12)); |
The INSERT statement that tests the trigger:
DROP FUNCTION IF EXISTS avenger_t1_function; CREATE FUNCTION avenger_t1_function() RETURNS TRIGGER AS $$ BEGIN /* Insert a row into the avenger_log table. * Also, see PostrgreSQL 39.9 Trigger Procedures. */ INSERT INTO avenger_log ( trigger_name , trigger_timing , trigger_event , trigger_type ) VALUES ( UPPER(TG_NAME) , TG_WHEN , TG_OP , TG_LEVEL ); /* A statement trigger doesn't use a composite type or tuple, * it should simply return an empty composite type or void. */ RETURN NULL; END; $$ LANGUAGE plpgsql; |
The avenger_t1 statement trigger:
CREATE TRIGGER avenger_t1 BEFORE INSERT ON avenger EXECUTE FUNCTION avenger_t1_function(); |
The INSERT statement:
INSERT INTO avenger ( first_name, last_name, avenger_name ) VALUES ('Hope', 'van Dyne', 'Wasp'); |
The results logged to the avenger_log table from a query:
avenger_log_id | trigger_name | trigger_timing | trigger_event | trigger_type
----------------+--------------+----------------+---------------+--------------
1 | AVENGER_T1 | BEFORE | INSERT | STATEMENT
(1 row) |
As always, I hope this helps those looking for a solution.
MySQL Query from JSON
One of my students asked how you could get JSON data out in tabular format. I said they should look at Øystein Grøvlen’s JSON_TABLE – Best of Both Worlds blog post from 2018. Unfortunately, the student wanted another example with the Video Store model that we use in class.
For clarity, all path definitions start with a $ followed by other selectors:
- A period followed by a name, such as $.website
- [N] where N is the position in a zero-indexed array
- The .[*] wildcard evaluates all members of an object
- The [*] wildcard evaluates all members of an array
- The prefix and suffix wildcard, **, evaluates to all paths that begin with the named prefix and end with the named suffix
So, here’s a quick supplement to what’s already there. It assumes you created an example table based on my prior blog post that looks like this:
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | struct |
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | {"contact": [{"last_name": "Winn", "first_name": "Randi"}, {"last_name": "Winn", "first_name": "Brian"}], "account_number": "US00001"} |
| 2 | {"contact": [{"last_name": "Vizquel", "first_name": "Oscar"}, {"last_name": "Vizquel", "first_name": "Doreen"}], "account_number": "US00002"} |
| 3 | {"contact": [{"last_name": "Sweeney", "first_name": "Meaghan"}, {"last_name": "Sweeney", "first_name": "Matthew"}, {"last_name": "Sweeney", "first_name": "Ian"}], "account_number": "US00003"} |
| 4 | {"contact": [{"last_name": "Clinton", "first_name": "Goeffrey"}], "account_number": "US00004"} |
| 5 | {"contact": [{"last_name": "Moss", "first_name": "Wendy"}], "account_number": "US00005"} |
| 6 | {"contact": [{"last_name": "Gretelz", "first_name": "Simon"}], "account_number": "US00006"} |
| 7 | {"contact": [{"last_name": "Royal", "first_name": "Elizabeth"}], "account_number": "US00007"} |
| 8 | {"contact": [{"last_name": "Smith", "first_name": "Brian"}], "account_number": "US00008"} |
| 9 | {"contact": [{"last_name": "Potter", "first_name": "Harry"}, {"last_name": "Potter", "first_name": "Ginny"}, {"last_name": "Potter", "first_name": "Lily"}], "account_number": "US00011"} |
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set (0.01 sec) |
You can query the account_number key value like this:
SELECT id , JSON_EXTRACT(struct, "$.account_number") AS account_no FROM example; |
It returns:
+----+------------+ | id | account_no | +----+------------+ | 1 | "US00001" | | 2 | "US00002" | | 3 | "US00003" | | 4 | "US00004" | | 5 | "US00005" | | 6 | "US00006" | | 7 | "US00007" | | 8 | "US00008" | | 9 | "US00011" | +----+------------+ 9 rows in set (0.00 sec) |
You use the JSON_TABLE function to get the embedded array elements of first and last name, like:
SELECT id , contact.* FROM example CROSS JOIN JSON_TABLE( struct ,"$.contact[*]" COLUMNS( lname JSON PATH "$.last_name" , fname JSON PATH "$.first_name")) AS contact; |
It returns:
+----+-----------+-------------+ | id | lname | fname | +----+-----------+-------------+ | 1 | "Winn" | "Randi" | | 1 | "Winn" | "Brian" | | 2 | "Vizquel" | "Oscar" | | 2 | "Vizquel" | "Doreen" | | 3 | "Sweeney" | "Meaghan" | | 3 | "Sweeney" | "Matthew" | | 3 | "Sweeney" | "Ian" | | 4 | "Clinton" | "Goeffrey" | | 5 | "Moss" | "Wendy" | | 6 | "Gretelz" | "Simon" | | 7 | "Royal" | "Elizabeth" | | 8 | "Smith" | "Brian" | | 9 | "Potter" | "Harry" | | 9 | "Potter" | "Ginny" | | 9 | "Potter" | "Lily" | +----+-----------+-------------+ 15 rows in set (0.00 sec) |
You can combine both approaches, as shown below.
SELECT id , JSON_EXTRACT(struct, "$.account_number") AS account_no , contact.* FROM example CROSS JOIN JSON_TABLE( struct ,"$.contact[*]" COLUMNS( lname JSON PATH "$.last_name" , fname JSON PATH "$.first_name")) AS contact; |
It returns:
+----+------------+-----------+-------------+ | id | account_no | lname | fname | +----+------------+-----------+-------------+ | 1 | "US00001" | "Winn" | "Randi" | | 1 | "US00001" | "Winn" | "Brian" | | 2 | "US00002" | "Vizquel" | "Oscar" | | 2 | "US00002" | "Vizquel" | "Doreen" | | 3 | "US00003" | "Sweeney" | "Meaghan" | | 3 | "US00003" | "Sweeney" | "Matthew" | | 3 | "US00003" | "Sweeney" | "Ian" | | 4 | "US00004" | "Clinton" | "Goeffrey" | | 5 | "US00005" | "Moss" | "Wendy" | | 6 | "US00006" | "Gretelz" | "Simon" | | 7 | "US00007" | "Royal" | "Elizabeth" | | 8 | "US00008" | "Smith" | "Brian" | | 9 | "US00011" | "Potter" | "Harry" | | 9 | "US00011" | "Potter" | "Ginny" | | 9 | "US00011" | "Potter" | "Lily" | +----+------------+-----------+-------------+ 15 rows in set (0.00 sec) |
Lastly, if you want to get rid of the enclosing double quotes you can do the following:
WITH raw AS (SELECT id , JSON_EXTRACT(struct, "$.account_number") AS account_no , contact.* FROM example CROSS JOIN JSON_TABLE( struct ,"$.contact[*]" COLUMNS( lname JSON PATH "$.last_name" , fname JSON PATH "$.first_name")) AS contact) SELECT id , REGEXP_REPLACE(account_no,'"','') AS account_no , REGEXP_REPLACE(lname,'"','') AS lname , REGEXP_REPLACE(fname,'"','') AS fname FROM raw; |
It’s also possible to use the JSON_UNQUOTE function to cleanup the double quotes. I hope this helps those extracting JSON data into tabular result sets.
MySQL Backslashes
Yesterday, I wrote a blog post that showed you how to write a query returning a JSON structure for a 1:many relationship. The relationship was between the member and contact table. It returns one account_number from the member table and a list of first_name and last_name columns from the contact table in a JSON structure.
One of my students asked why I choose to strip the backslashes with Python, and my reply was the SQL was already complex for most blog readers. The student asked but how would you do it in SQL. OK, that’s a fair question for two reasons. First, you don’t need to do in your local programs because it’ll run faster on the server. Second, if you strip the backslashes you can insert it into a standard JSON column. This blog post will show you how to do both.
You would use three REGEXP_REPLACE function calls, like:
SELECT REGEXP_REPLACE( REGEXP_REPLACE( REGEXP_REPLACE( JSON_OBJECT( 'account_number', account_number ,'contact', CONCAT('[' , GROUP_CONCAT( JSON_OBJECT('first_name',first_name ,'last_name',last_name ) SEPARATOR ',') ,']') ) ,'\\\\','') ,'"\\\[','\\\[') ,'\\\]"','\\\]') AS json_result FROM member m INNER JOIN contact c ON m.member_id = c.member_id GROUP BY m.account_number; |
It returns the following:
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"contact": [{"last_name": "Winn", "first_name": "Randi"},{"last_name": "Winn", "first_name": "Brian"}], "account_number": "US00001"} |
| {"contact": [{"last_name": "Vizquel", "first_name": "Oscar"},{"last_name": "Vizquel", "first_name": "Doreen"}], "account_number": "US00002"} |
| {"contact": [{"last_name": "Sweeney", "first_name": "Meaghan"},{"last_name": "Sweeney", "first_name": "Matthew"},{"last_name": "Sweeney", "first_name": "Ian"}], "account_number": "US00003"} |
| {"contact": [{"last_name": "Clinton", "first_name": "Goeffrey"}], "account_number": "US00004"} |
| {"contact": [{"last_name": "Moss", "first_name": "Wendy"}], "account_number": "US00005"} |
| {"contact": [{"last_name": "Gretelz", "first_name": "Simon"}], "account_number": "US00006"} |
| {"contact": [{"last_name": "Royal", "first_name": "Elizabeth"}], "account_number": "US00007"} |
| {"contact": [{"last_name": "Smith", "first_name": "Brian"}], "account_number": "US00008"} |
| {"contact": [{"last_name": "Potter", "first_name": "Harry"},{"last_name": "Potter", "first_name": "Ginny"},{"last_name": "Potter", "first_name": "Lily"}], "account_number": "US00011"} |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set (0.00 sec) |
Let’s create a table with a JSON structure with the following script:
/* Drop table if it exists. */ DROP TABLE IF EXISTS example; /* Create a example table. */ CREATE TABLE example ( id int unsigned auto_increment , struct json , PRIMARY KEY (id)); |
Now, we can embed the query inside an INSERT statement:
INSERT INTO example ( struct ) (SELECT REGEXP_REPLACE( REGEXP_REPLACE( REGEXP_REPLACE( JSON_OBJECT( 'account_number', account_number ,'contact', CONCAT('[' , GROUP_CONCAT( JSON_OBJECT('first_name',first_name ,'last_name',last_name ) SEPARATOR ',') ,']') ) ,'\\\\','') ,'"\\\[','\\\[') ,'\\\]"','\\\]') AS json_result FROM member m INNER JOIN contact c ON m.member_id = c.member_id GROUP BY m.account_number); |
A query of the example table, like:
SELECT * FROM example; |
Returns:
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | struct |
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | {"contact": [{"last_name": "Winn", "first_name": "Randi"}, {"last_name": "Winn", "first_name": "Brian"}], "account_number": "US00001"} |
| 2 | {"contact": [{"last_name": "Vizquel", "first_name": "Oscar"}, {"last_name": "Vizquel", "first_name": "Doreen"}], "account_number": "US00002"} |
| 3 | {"contact": [{"last_name": "Sweeney", "first_name": "Meaghan"}, {"last_name": "Sweeney", "first_name": "Matthew"}, {"last_name": "Sweeney", "first_name": "Ian"}], "account_number": "US00003"} |
| 4 | {"contact": [{"last_name": "Clinton", "first_name": "Goeffrey"}], "account_number": "US00004"} |
| 5 | {"contact": [{"last_name": "Moss", "first_name": "Wendy"}], "account_number": "US00005"} |
| 6 | {"contact": [{"last_name": "Gretelz", "first_name": "Simon"}], "account_number": "US00006"} |
| 7 | {"contact": [{"last_name": "Royal", "first_name": "Elizabeth"}], "account_number": "US00007"} |
| 8 | {"contact": [{"last_name": "Smith", "first_name": "Brian"}], "account_number": "US00008"} |
| 9 | {"contact": [{"last_name": "Potter", "first_name": "Harry"}, {"last_name": "Potter", "first_name": "Ginny"}, {"last_name": "Potter", "first_name": "Lily"}], "account_number": "US00011"} |
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set (0.00 sec) |
I hope this answers the question about whether you can use SQL remove the backslashes from the original result set and how you can insert the JSON result set into a JSON data type.
MySQL JSON Tricks
Are they really tricks or simply basic techniques combined to create a solution. Before writing these mechanics for using native MySQL to create a compound JSON object, let me point out that the easiest way to get one is to use the MySQL Node.js library, as shown recently in my “Is SQL Programming” blog post.
Moving data from a relational model output to a JSON structure isn’t as simple as a delimited list of columns in a SQL query. Let’s look at it in stages based on the MySQL Server 12.18.2 Functions that create JSON values.
Here’s how you return single row as a JSON object, which is quite straightforward:
SELECT JSON_OBJECT('first_name',c.first_name,'last_name',c.last_name) AS json_result FROM contact c WHERE first_name = 'Harry' AND last_name = 'Potter'; |
It returns:
+------------------------------------------------+
| json_result |
+------------------------------------------------+
| {"last_name": "Potter", "first_name": "Harry"} |
+------------------------------------------------+
1 row in set (0.00 sec) |
With a GROUP_CONCAT function, let’s capture a JSON array of all three Potter family members:
SELECT CONCAT('[' , GROUP_CONCAT( JSON_OBJECT('first_name',first_name ,'last_name',last_name ) SEPARATOR ',') ,']') AS json_result FROM contact c WHERE c.last_name = 'Potter'; |
It returns an array of JSON objects:
+-----------------------------------------------------------------------------------------------------------------------------------------------+
| [{"last_name": "Potter", "first_name": "Harry"},{"last_name": "Potter", "first_name": "Ginny"},{"last_name": "Potter", "first_name": "Lily"}] |
+-----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec) |
Next, let’s put a 1:many relationship between the member and contact table into a JSON structure with a single account number and an array of contact. It requires a second call to the JSON_OBJECT function and the addition of a GROUP BY clause in the query.
SELECT JSON_OBJECT( 'account_number', account_number ,'contact', CONCAT('[' , GROUP_CONCAT( JSON_OBJECT('first_name',first_name ,'last_name',last_name ) SEPARATOR ',') ,']') ) AS json_result FROM member m INNER JOIN contact c ON m.member_id = c.member_id WHERE c.last_name = 'Potter' GROUP BY m.account_number; |
It returns the following string with an annoying set of backslashes. It also inverts the column order, which appears unavoidable but it shouldn’t matter because the order of name-value pairs in JSON is immaterial.
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"contact": "[{\"last_name\": \"Potter\", \"first_name\": \"Harry\"},{\"last_name\": \"Potter\", \"first_name\": \"Ginny\"},{\"last_name\": \"Potter\", \"first_name\": \"Lily\"}]", "account_number": "US00011"} |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec) |
The following quick little Python code cleans up the JSON string by removing the backslashes and extraneous quotes around the array of contacts.
# Import the library. import mysql.connector from mysql.connector import errorcode try: # Open connection. cnx = mysql.connector.connect(user='student', password='student', host='127.0.0.1', database='studentdb') # Create cursor. cursor = cnx.cursor() # Set the query statement. query = ("SELECT JSON_OBJECT( " "'account_number', m.account_number " ",'contact', CONCAT('[' " " , GROUP_CONCAT( " " JSON_OBJECT('first_name', c.first_name " " ,'last_name', c.last_name ) SEPARATOR ',') " " ,']')) AS json_result " "FROM contact c INNER JOIN member m " "ON c.member_id = m.member_id " "WHERE c.last_name = %s " "GROUP BY account_number") # Execute cursor. cursor.execute(query,["Potter"]) # Display the column returned by the query stripped of backslashes and # extraneous quotes. for (row) in cursor: for column in range(len(row)): print(row[column].replace("\\","").replace("\"[","[").replace("]\"","]")) # Close cursor. cursor.close() # ------------------------------------------------------------ # Handle exception and close connection. except mysql.connector.Error as e: if e.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Something is wrong with your user name or password") elif e.errno == errorcode.ER_BAD_DB_ERROR: print("Database does not exist") else: print("Error code:", e.errno) # error number print("SQLSTATE value:", e.sqlstate) # SQLSTATE value print("Error message:", e.msg) # error message # Close the connection when the try block completes. else: cnx.close() |
It returns:
{"contact": [{"last_name": "Potter", "first_name": "Harry"},{"last_name": "Potter", "first_name": "Ginny"},{"last_name": "Potter", "first_name": "Lily"}], "account_number": "US00011"} |
I hope this helps exhibit less well known MySQL syntax. Check this post to see how to insert a result set without Python as an intermediary.
PL/pgSQL Transactions
There are many nuances that I show students about PL/pgSQL because first I teach them how to use PL/SQL. These are some of the differences:
- PL/SQL declares the function or procedure and then uses the IS keyword; whereas, PL/pgSQL uses the AS keyword.
- PL/SQL uses the RETURN keyword for functions declarations, like:
RETURN [data_type} IS
Whereas, PL/pgSQL uses the plural RETURNS keyword in the function declaration, like:
RETURNS [data_type] AS
- PL/SQL considers everything after the function or procedure header as the implicit declaration section; whereas, PL/pgSQL requires you block the code with something like $$ (double dollar symbols) and explicitly use the DECLARE keyword.
- PL/SQL supports local functions (inside the DECLARE block of a function or procedure); whereas, PL/pgSQL doesn’t.
- PL/SQL puts the variable modes (IN, INOUT, OUT) between the parameter name and type; whereas, PL/pgSQL puts them before the variable name.
- PL/SQL declares cursors like:
CURSOR cursor_name (parameter_list) IS
Whereas, PL/pgSQL declares them like
cursor_name CURSOR (parameter_list) FOR
- PL/SQL terminates and runs the block by using an END keyword, an optional module name, a semicolon to terminate the END; statement, and a forward slash to dispatch the program to PL/SQL statement engine:
END [module_name]; /
Whereas, PL/pgSQL terminates and runs the block by using an END keyword, a semicolon to terminate the END; statement, two dollar signs to end the PL/pgSQL block, and a semicolon to dispatch the program.
END LANGUAGE plpgsql; $$;
After all that basic syntax discussion, we try to create a sample set of tables, a function, a procedure, and a test case in PL/pgSQL. They’ve already done a virtually equivalent set of tasks in PL/SQL.
Here are the steps:
- Create the grandma and tweetie_bird tables:
/* Conditionally drop grandma table and grandma_s sequence. */ DROP TABLE IF EXISTS grandma CASCADE; /* Create the table. */ CREATE TABLE GRANDMA ( grandma_id SERIAL , grandma_house VARCHAR(30) NOT NULL , PRIMARY KEY (grandma_id) ); /* Conditionally drop a table and sequence. */ DROP TABLE IF EXISTS tweetie_bird CASCADE; /* Create the table with primary and foreign key out-of-line constraints. */ SELECT 'CREATE TABLE tweetie_bird' AS command; CREATE TABLE TWEETIE_BIRD ( tweetie_bird_id SERIAL , tweetie_bird_house VARCHAR(30) NOT NULL , grandma_id INTEGER NOT NULL , PRIMARY KEY (tweetie_bird_id) , CONSTRAINT tweetie_bird_fk FOREIGN KEY (grandma_id) REFERENCES grandma (grandma_id) );
- Create a get_grandma_id function that returns a number, which should be a valid primary key value from the grandma_id column of the grandma table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
CREATE OR REPLACE FUNCTION get_grandma_id ( IN pv_grandma_house VARCHAR ) RETURNS INTEGER AS $$ /* Required for PL/pgSQL programs. */ DECLARE /* Local return variable. */ lv_retval INTEGER := 0; -- Default value is 0. /* Use a cursor, which will not raise an exception at runtime. */ find_grandma_id CURSOR ( cv_grandma_house VARCHAR ) FOR SELECT grandma_id FROM grandma WHERE grandma_house = cv_grandma_house; BEGIN /* Assign a value when a row exists. */ FOR i IN find_grandma_id(pv_grandma_house) LOOP lv_retval := i.grandma_id; END LOOP; /* Return 0 when no row found and the ID # when row found. */ RETURN lv_retval; END; $$ LANGUAGE plpgsql;
- Create a Warner_brother procedure that writes data across two tables as a transaction. You con’t include any of the following in your functions or procedures because all PostgreSQL PL/pgSQL functions and procedures are transaction by default:
- SET TRANSACTION
- START TRANSACTION
- SAVEPOINT
- COMMIT
A ROLLBACK should be placed in your exception handler as qualified on lines #33 thru #36. The warner_brother procedure inserts rows into the grandma and tweetie_bird tables.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
/* Create or replace procedure warner_brother. */ CREATE OR REPLACE PROCEDURE warner_brother ( pv_grandma_house VARCHAR , pv_tweetie_bird_house VARCHAR ) AS $$ /* Required for PL/pgSQL programs. */ DECLARE /* Declare a local variable for an existing grandma_id. */ lv_grandma_id INTEGER; BEGIN /* Check for existing grandma row. */ lv_grandma_id := get_grandma_id(pv_grandma_house); IF lv_grandma_id = 0 THEN /* Insert grandma. */ INSERT INTO grandma ( grandma_house ) VALUES ( pv_grandma_house ) RETURNING grandma_id INTO lv_grandma_id; END IF; /* Insert tweetie bird. */ INSERT INTO tweetie_bird ( tweetie_bird_house , grandma_id ) VALUES ( pv_tweetie_bird_house , lv_grandma_id ); EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE NOTICE '[%] [%]', SQLERRM, SQLSTATE; END; $$ LANGUAGE plpgsql;
You should take note of the RETURNING-INTO statement on line #22. The alternative to this clause isn’t pretty if you know that PostgreSQL uses a table name, column name, and the literal seq value separated by underscores (that is, snake case), like:
/* Assign current value to local variable. */ lv_grandma_id := CURRVAL('grandma_grandma_id_seq');
It would be even uglier if you had to look up the sequence name, like:
/* Assign current value to local variable. */ lv_grandma_id := CURRVAL(pg_get_serial_sequence('grandma','grandma_id'));
- You can test the combination of these two stored procedures with the following DO-block:
/* Test the warner_brother procedure. */ DO $$ BEGIN /* Insert the yellow house. */ CALL warner_brother( 'Yellow House', 'Cage'); CALL warner_brother( 'Yellow House', 'Tree House'); /* Insert the red house. */ CALL warner_brother( 'Red House', 'Cage'); CALL warner_brother( 'Red House', 'Tree House'); END; $$ LANGUAGE plpgsql;
Then, query the results:
SELECT * FROM grandma g INNER JOIN tweetie_bird tb ON. g.grandma_id = tb.grandma_id;
It should return:
grandma_id | grandma_house | tweetie_bird_id | tweetie_bird_house | grandma_id ------------+---------------+-----------------+--------------------+------------ 1 | Red House | 1 | Cage | 1 1 | Red House | 2 | Tree House | 1 2 | Yellow House | 3 | Cage | 2 2 | Yellow House | 4 | Tree House | 2 (4 rows)
As always, I hope writing a clear and simple examples helps those looking for sample code.
Is SQL Programming
Is SQL, or Structured Query Language, a programming language? That’s a great question! A question that many answer with emphasis: “No, SQL is not a programming language!” There are some who answer yes; and they usually qualify that answer with something like: “SQL is a programming language designed to communicate with relational databases.”
It strikes me that those saying “yes” are saying that SQL is only a collection of interface methods to read from and write to a database engine. Those saying SQL is not a programming language often qualify that a programming language must have conditional logic and iterative structures, which don’t exist in SQL.
There’s a third group that are fence sitters. They decline to say whether SQL is a programming language, but they also say individuals who only write SQL aren’t programmers. That’s a bit harsh from my perspective.
Before determining whether SQL is a programming language let’s define a programming language. Let’s define a programming language as a collection of lexical units, or building blocks, that build program units. Lexical units are typically organized as delimiters, identifiers, literals, and comments:
- Delimiters include single or double quotes to identify strings and operators that let you assign and compare values.
- Identifiers are reserved words, keywords, predefined identifiers (like data type names), user-defined variables, subroutines, or types.
- Literals are typically numbers and strings, where some strings qualify as dates because they implement a default format mask to convert strings to dates or date-times.
- Comments are simply delimited text that the program ignores but the programmer uses.
That means a programming language must let you define a variable, assign a value to a variable, iterate across a set of values, and make conditional statements. SQL meets these four conditions, but it does, as a set-programming language, qualify all variables as lists of tuples. Though it is possible to have variables with zero to many elements and one to many members in any given tuple. That means you can assign a literal value to to a one-element list with a single-member tuple, like you would a string or integer to a variable of that type.
As Kris Köhntopp commented, computer science defines a programming language as Turing Complete. As his comment qualifies and the Wikipedia page explains: “Turing completeness in declarative SQL is implemented through recursive common table expressions. Unsurprisingly, procedural extensions to SQL (PLSQL, etc.) are also Turing-complete.” While PostgreSQL introduces recursive query syntax through CTEs, it recently added the search and cycle feature in PostgreSQL 14. The recursive query feature has existed in the Oracle database since Oracle 8, but their documentation calls them hierarchical queries. I wrote a quick a tutorial on hierarchical queries in 2008.
For clarity, define and declare are two words that give grief to some newbies. Let’s qualify what they mean. Declare means to give a variable a name and data type. Define means to declare a variable and assign it a value. Another word for assigning a variable is initializing it. Unassigned variables are automatically assigned a default value or a null dependent on the programming language.
Let’s first declare a local variable, assign it to variable, and display the variable. The following example uses Node.js to define the input variable, assign the input variable to the display variable, and then print the display variable to console. Node.js requires that you assign an empty string to the display variable to define it as a string otherwise its type would be undefined, which is common behavior in dynamically typed languages.
/* Declare the display variable as a string. */ var display = "" /* Define the input variable. */ var input = "Hello World!" /* Assign the input variable contents to the display variable. */ display = input /* Print the display variable contents to console. */ console.log(display) |
It prints:
Hello World! |
Let’s write the same type of program in MySQL. Like the Node.js, there are implementation differences. The biggest difference in MySQL or other relational databases occurs because SQL is a declarative set-based language. That means every variable is a collection of a record structure . You can only mimic a scalar or primitive data type variable by creating a record structure with a single member.
In the case below, there are four processing steps:
- The ‘Hello World!’ literal value is assigned to an input variable.
- The SELECT-list (or comma-delimited set of values in the SELECT clause) is assigned like a tuple to the struct collection variable by treating the query of the literal value as an expression.
- The FROM clause returns the struct collection as the data set or as a derived table.
- The topmost SELECT clause evaluates the struct collection row-by-row, like a loop, and assigns the input member to a display variable.
The query is:
SELECT struct.input AS display FROM (SELECT 'Hello World!' AS input) struct; |
Since the struct collection contains only one element, it displays the original literal value one time, like
+--------------+ | display | +--------------+ | Hello World! | +--------------+ 1 row in set (0.00 sec) |
Let’s update the SQL syntax to the more readable, ANSI 1999 and forward, syntax with a Common Table Expression (CTE). CTEs are implemented by the WITH clause.
WITH struct AS (SELECT 'Hello World!' AS input) SELECT struct.input AS display FROM struct; |
The best thing about CTE values they run one-time and are subsequently available anywhere in your query, subqueries, or correlated subqueries. In short, there’s never an excuse to write a subquery twice in the same query.
Let’s look at loops and if-statements. Having established that we can assign a literal to a variable, re-assign the value from one variable to another, and then display the new variable, let’s assign a set of literal values to an array variable. As before, let’s use Node.js to structure the initial problem.
The program now assigns an array of strings to the input variable, uses a for-loop to read the values from the input array, and uses an if-statement with a regular expression evaluation. The if-statement determines which of the array value meets the condition by using a negating logical expression. That’s because the search() function returns a 0 or greater value when the needle value is found in the string and a -1 when not found. After validating that the needle variable value is found in an input string, the input value is assigned to the display variable.
/* Declare the display variable as a string. */ var display = "" /* Declare a lookup variable. */ var needle = "Goodbye" /* Define the input variable as an array of strings. */ var input = ["Hello World!" ,"Goodbye, Cruel World!" ,"Good morning, too early ..."] /* Read through an array and assign the value that meets * the condition to the display variable. */ for (i = 0; i < input.length; i++) if (!(input[i].search(needle) < 0)) display = input[i] /* Print the display variable contents to console. */ console.log(display) |
Then, it prints the display value:
Goodbye, Cruel World! |
To replicate the coding approach in a query, there must be two CTEs. The needle CTE assigns a literal value of ‘goodbye’ to a one-element collection of a single-member tuple variable. The struct CTE creates a collection of strings by using the UNION ALL operator to append three unique tuples instead of one tuple as found in the early example.
The needle CTE returns a one-element collection of a single-member tuple variable. The struct CTE returns a three-element collection of a single-member tuple, which mimics an array of strings. The needle and struct CTEs return distinct variables with different data types. A cross join operation between the two CTEs puts their results together into the same context. It returns a Cartesian product that:
- Adds a single-row tuples to each row of the query’s result set or derived table.
- Adds a multiple-tuples to each row of the query’s result set or derived table by creating copies of each row (following the Cartesian set theory which multiplies rows and adds columns).
In this case, the Cartesian join adds a one-element needle CTE value to each element, or row, returned by the multiple-element struct CTE and produces the following derived table:
+-----------------------------+---------+ | display | lookup | +-----------------------------+---------+ | Hello World! | goodbye | | Goodbye, cruel world! | goodbye | | Good morning, too early ... | goodbye | +-----------------------------+---------+ 3 rows in set (0.00 sec) |
The following query reads through the CTE collection like a loop and filters out any invalid input values. It uses the MySQL regular expression like function in the WHERE clause, which acts as a conditional or if-statement.
WITH needle AS (SELECT 'goodbye' AS lookup) , struct AS (SELECT 'Hello World!' AS input UNION ALL SELECT 'Goodbye, cruel world!' AS input UNION ALL SELECT 'Good morning, too early ...' AS input) SELECT struct.input AS display FROM struct CROSS JOIN needle WHERE REGEXP_LIKE(struct.input, CONCAT('^.*',needle.lookup,'.*$'),'i'); |
It returns the one display value that meets the criteria:
+-----------------------+ | display | +-----------------------+ | Goodbye, cruel world! | +-----------------------+ 1 row in set (0.00 sec) |
The comparisons of the imperative programming approach in Node.js and declarative programming approach should have established that SQL has all the elements of a programming language. That is, SQL has variable declaration and assignment and both iterative and conditional statements. SQL also has different styles for implementing variable declaration and the examples covered subqueries and CTEs with cross joins placing variables in common scope.
Comparative Approaches:
Next, let’s examine a problem that a programmer might encounter when they think SQL only queries or inserts, updates, or deletes single rows. With that perspective of SQL there’s often a limited perspective on how to write queries. Developers with this skill set level typically write only basic queries, which may include inner and outer joins and some aggregation statements.
Let’s assume the following for this programming assignment:
- A sale table as your data source, and
- A requirement to display the type, number, pre-tax sale amount, and percentage by type.
The sale table definition:
+------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | sale_id | int unsigned | NO | PRI | NULL | auto_increment | | item_desc | varchar(20) | YES | | NULL | | | unit_price | decimal(8,2) | YES | | NULL | | | serial_no | varchar(10) | YES | | NULL | | +------------+--------------+------+-----+---------+----------------+ |
A basic Node.js program may contain a SQL query that returns the item_desc and unit_price columns while counting the number of serial_no rows and summing the unit_price amounts (that assumes no discount sales, after all its Apple). That type of query leaves calculating the total amount of sales and percentage by type to the Node.js program.
const mysql = require('mysql') const connection = mysql.createConnection({ host: 'localhost', user: 'student', password: 'student', database: 'studentdb' }) connection.connect((err) => { if (err) throw err else { console.log('Connected to MySQL Server!\n') connection.query("SELECT s.item_desc " + ", s.unit_price " + ", COUNT(s.serial_no) AS quantity_sold " + ", SUM(s.unit_price) AS sales " + "FROM sale s " + "GROUP BY s.item_desc " + ", s.unit_price", function (err, result) { if (err) throw err else { // Prints the index value in the RowDataPacket. console.log(result) connection.end() }})} }) |
This program would return a JSON structure, like:
[ RowDataPacket { item_desc: 'MacBook Pro 16', unit_price: 2499, quantity_sold: 16, sales: 39984 }, ... RowDataPacket { item_desc: 'MacBook Air M1', unit_price: 999, quantity_sold: 22, sales: 21978 } ] |
While the remaining JavaScript code isn’t difficult to write, it’s unnecessary effort if the developer knew SQL well enough to program in it. The developer could simply re-write the query like the following and return the percentage by type value in the base JSON structure.
WITH sales AS (SELECT SUM(unit_price) AS total FROM sale) SELECT s.item_desc , s.unit_price , COUNT(s.serial_no) AS quantity_sold , SUM(s.unit_price) AS sales , CONCAT(FORMAT((s.unit_price * COUNT(s.serial_no))/sales.total * 100,2),'%') AS percentage FROM sale s CROSS JOIN sales GROUP BY s.item_desc , s.unit_price , sales.total; |
The query uses the sales CTE to calculate and define a tuple with the total sales and adds a derived column calculating the percentage by type of device. It’s probably important to note that aggregation rules require you add the sales.total CTE tuple to the group by clause.
The new query returns this JSON list:
[ RowDataPacket { item_desc: 'MacBook Pro 16', unit_price: 2499, quantity_sold: 16, sales: 39984, percentage: '17.70%' }, ... RowDataPacket { item_desc: 'MacBook Air M1', unit_price: 999, quantity_sold: 22, sales: 21978, percentage: '9.73%' } ] |
The developer would get a complete JSON list when the new query replaces the old. It also would eliminate the need to write additional JavaScript to calculate the percentage by type of device.
Conclusions:
Leveraging the programming power of SQL is frequently possible in many frontend and backend programming solutions. However, the programming power of SQL is infrequently found in programming solutions. That leaves me to ask: “Is it possible that the almost systemic failure to leverage the programming capabilities of SQL is a result of biases by instructors and mentors to their own limited skill sets?” That likely might be true if their instructors and mentors held the belief that: “No, SQL is not a programming language!”
Candidly, folks that write SQL at the programming level almost always have concurrent mastery in two or more imperative programming languages. They’re probably the ones who say, “SQL is a programming language designed to communicate with relational databases.”
Who are those pesky fence sitters? You remember those, don’t you. They’re the ones who declined to take a position on whether SQL is a programming language. Are they the developers who are still learning, and those without an entrenched, preconceived, or learned bias? Or, do they wonder if SQL is Turing complete?
Subscribe via RSS