MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for June, 2021

Design Database Triggers

without comments

Designing and implementing database triggers is always interesting and sometimes not easy. I believe most of the difficulty comes from not implementing the triggers in a way that lets you perform single use case testing. For example, a trigger typically fires as a result of an INSERT, UPDATE, or DELETE statement. That means you can’t test the trigger’s logic independently from the SQL statement.

This post shows you how to implement an Oracle Database trigger that ensures a last_name field always has a hyphen when it is composed of two surnames. It also shows you how to build debugging directly into the trigger with Oracle’s conditional compilation logic (covered in my Oracle Database 12c PL/SQL Programming book on pages 170-171) while writing the debug comments to a debug logging table.

The example works through the design in stages. To begin the process, you need to define a zeta table and zeta_s sequence (no magic in the table or sequence names).

-- Create the zeta demo table.
CREATE TABLE zeta
( zeta_id     NUMBER
, last_name   VARCHAR2(30));
 
-- Create the zeta_s demo sequence.
CREATE SEQUENCE zeta_s;

Next, you write a basic on insert row-level (or, row-by-row) trigger. The following white_space trigger only fires when the last_name column value contains a whitespace between two components of a last name.

The code follows below:

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

/*
|| Create an on insert trigger to implement the desired
|| logic, which replaces a whitespace between two portions
|| of a last_name column.
*/
CREATE OR REPLACE
  TRIGGER white_space
  BEFORE INSERT ON zeta
  FOR EACH ROW
  WHEN (REGEXP_LIKE(NEW.last_name,' '))
BEGIN
  :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1);
END white_space;
/

You can now test the white_space trigger with these two INSERT statements:

-- Two test insert statements.
INSERT INTO zeta
( zeta_id, last_name ) 
VALUES 
( zeta_s.NEXTVAL, 'Baron-Schwartz' );
 
INSERT INTO zeta
( zeta_id, last_name ) 
VALUES 
( zeta_s.NEXTVAL, 'Zeta Jones' );

After running the two INSERT statements, you can query the last_name from the zeta table and verify that there’s always a hyphen between the two components of the last name, like:

SELECT * FROM zeta;

It should display:

   ZETA_ID LAST_NAME
---------- ------------------------------
         1 Baron-Schwartz
         2 Zeta-Jones

However, the business logic is violated when you run an UPDATE statement, like:

-- Update data and break the business rule.
UPDATE zeta
SET    last_name = 'Zeta Jones'
WHERE  last_name = 'Zeta-Jones';

A fresh query like

SELECT * FROM zeta;

Should display the following, which allowed an UPDATE statement to put in a non-conforming last name value:

   ZETA_ID LAST_NAME
---------- ------------------------------
         1 Baron-Schwartz
         2 Zeta Jones

You need to expand the role of your white_space trigger to prevent this undesired outcome by enabling it to fire on an insert or update event. You do that by adding ON UPDATE to line 8 below. The modified white_space trigger for both SQL events is:

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

/*
|| Create an on insert or update trigger to implement the
|| desired logic, which replaces a whitespace between two 
|| portions of a last_name column.
*/
CREATE OR REPLACE
  TRIGGER white_space
  BEFORE INSERT OR UPDATE ON zeta
  FOR EACH ROW
  WHEN (REGEXP_LIKE(NEW.last_name,' '))
BEGIN
  :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1);
END white_space;
/

Having made the change on line 8 above, you can now retest the white_space trigger with the following UPDATE statement. You should note that the WHERE clause uses a whitespace because the last UPDATE statement with the INSERT-only white_space trigger allowed its change.

UPDATE zeta
SET    last_name = 'Zeta-Jones'
WHERE  last_name = 'Zeta Jones';

Re-query the zeta table:

SELECT * FROM zeta;

It should display the following values that meet the business rule:

   ZETA_ID LAST_NAME
---------- ------------------------------
         1 Baron-Schwartz
         2 Zeta Jones

The modified white_space trigger doesn’t let us capture debug information and it doesn’t let us see whether the SQL event is an INSERT or UPDATE statement. It also fails to differentiate between outcomes from an INSERT and UPDATE event.

You can fix this by:

  • Creating a debug_log table that captures debugging information.
  • Creating a debug_procedure to format diagnostic strings.
  • Using the Data Manipulation Language (DML) Event Functions (covered in my Oracle Database 12c PL/SQL Programming book’s Table 12-3 on page 533) to track whether the event is an INSERT or UPDATE statement.

The three steps to make the trigger capable of different outcomes and debugging are:

  1. The following creates a debug_log table: 

    -- Create the debug_log table.
CREATE TABLE debug_log
( message  VARCHAR2(78));
  2. The following creates an a debug procedure: 

    -- Create a debug logging procedure.
CREATE OR REPLACE
  PROCEDURE debug ( event     VARCHAR2 := 'Unknown'
                  , location  VARCHAR2
                  , COLUMN    VARCHAR2 ) IS
    /* Local message variable. */
    lv_message  VARCHAR2(78);
 
    /* Set procedure as an autonomous transaction. */ 
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    /* Build, insert, and commit message in log. */
    lv_message := event || ' event at ' || location || ' on column [' || COLUMN || ']';
    INSERT INTO debug_log ( message ) VALUES ( lv_message );
    COMMIT;
  END;
/
  3. The following creates an a replacement white_space trigger equipped with event tracking and conditional compilation debug calls to the debug_log table:

    You actually need to change the session before compiling this trigger with the following command so that the conditional compilation instructions work:

    ALTER SESSION SET PLSQL_CCFLAGS = 'DEBUG:1';

    Then, create the white_space trigger from the following code:

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

    -- Create a debug logging procedure.
CREATE OR REPLACE
  TRIGGER white_space
  BEFORE INSERT OR UPDATE ON zeta
  FOR EACH ROW
  WHEN (REGEXP_LIKE(NEW.last_name,' '))
DECLARE
  lv_event  VARCHAR2(9);
BEGIN
  /* Conditional debugging. */
  $IF $$DEBUG = 1 $THEN
    debug( location     => 'before IF statement'
         , column_value => ':new.last_name' );
  $END
 
  IF INSERTING THEN
    lv_event := 'Inserting';
 
    /* Conditional debugging. */
    $IF $$DEBUG = 1 $THEN
      debug( event        => lv_event
           , location     => 'after IF statement'
           , column_value => ':new.last_name' );
    $END
 
    :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1);
  ELSIF UPDATING THEN
    lv_event := 'Updating';
 
    /* Conditional debugging. */
    $IF $$DEBUG = 1 $THEN
      debug( event        => lv_event
           , location     => 'after ELSIF statement'
           , column_value => ':new.last_name' );
    $END
 
    RAISE_APPLICATION_ERROR(-20001,'Whitespace replaced with hyphen.');
  END IF;
    /* Conditional debugging. */
    $IF $$DEBUG = 1 $THEN
      debug( location     => 'after END IF statement'
           , column_value => ':new.last_name' );
    $END
END white_space;
/

A new test case for the modified white_space trigger uses an INSERT and UPDATE statement, like:

INSERT INTO zeta
( zeta_id, last_name ) 
VALUES 
( zeta_s.NEXTVAL, 'Pinkett Smith' );
 
UPDATE zeta
SET    last_name = 'Pinkett Smith'
WHERE  last_name = 'Pinkett-Smith';

The UPDATE statement violates the business rule and the new white_space trigger throws an error when an attempt is made to update the last_name with two names separated by a whitespace. The UPDATE statement raises the following error stack:

UPDATE zeta
       *
ERROR AT line 1:
ORA-20001: Whitespace replaced WITH hyphen.
ORA-06512: AT "STUDENT.WHITE_SPACE", line 31
ORA-04088: error during execution OF TRIGGER 'STUDENT.WHITE_SPACE'

Re-query the zeta table:

SELECT * FROM zeta;

It should display the following values that meet the business rule. The new third row in the table came from the INSERT statement in the test case.

   ZETA_ID LAST_NAME
---------- ------------------------------
         1 Baron-Schwartz
         2 Zeta-Jones
         3 Pinkett-Smith

Unfortunately, there’s a lot of debugging clutter in the white_space trigger. The other downside is it requires testing from INSERT and UPDATE statements rather than simple anonymous block. You can fix that by doing two things:

  1. Remove the body of the trigger to an autonomous zeta_function.
  2. Put a logic router in the trigger with a call to the autonomous zeta_function.

Here’s the script to create the zeta_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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

CREATE OR REPLACE
  FUNCTION zeta_function 
  ( column_value  VARCHAR2
  , event         VARCHAR2 ) RETURN VARCHAR2 IS
 
    /* Return value. */
    lv_retval  VARCHAR2(30) := column_value;
 
    /* Set function as an autonomous transaction. */ 
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
 
    /* Conditional debugging. */
    $IF $$DEBUG = 1 $THEN
      debug( location     => 'before IF statement'
           , column_value => ':new.column_value' );
    $END
 
    /* Check if event is INSERT statement. */
    IF event = 'INSERTING' THEN
 
      /* Conditional debugging. */
      $IF $$DEBUG = 1 $THEN
        debug( event        =>  INITCAP(event)
             , location     => 'after IF statement'
             , column_value => ':new.column_value' );
      $END
 
      /* Replace a whitespace with a hyphen. */
      lv_retval := REGEXP_REPLACE(column_value,' ','-',1,1);
 
    /* Check if event is UPDATE statement. */
    ELSIF event = 'UPDATING' THEN
 
      /* Conditional debugging. */
      $IF $$DEBUG = 1 $THEN
        debug( event        =>  INITCAP(event)
             , location     => 'after ELSIF statement'
             , column_value => ':new.column_value' );
      $END
 
      /* Raise error to state policy allows no changes. */
      RAISE_APPLICATION_ERROR(-20001,'Whitespace replaced with hyphen.');
 
    END IF;
 
      /* Conditional debugging. */
      $IF $$DEBUG = 1 $THEN
        debug( location     => 'after END IF statement'
             , column_value => ':new.column_value' );
      $END
 
    /* Return modified column for insert or original column for update. */
    RETURN lv_retval;
  END zeta_function;
/

The refactored white_space trigger follows:

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

CREATE OR REPLACE
  TRIGGER white_space
  BEFORE INSERT OR UPDATE ON zeta
  FOR EACH ROW
  WHEN (REGEXP_LIKE(NEW.last_name,' '))
DECLARE
  lv_event  VARCHAR2(9);
BEGIN
  /* Set evaluation event. */
  IF    INSERTING THEN lv_event := 'INSERTING';
  ELSIF UPDATING  THEN lv_event := 'UPDATING';
  END IF;
 
  /*
  || Assign the result of the formatted string to the 
  || new last_name value.
  */
  :NEW.last_name := zeta_function( event        => lv_event
                                 , column_value => :NEW.last_name);
END white_space;
/

A new test case for the modified white_space trigger uses an INSERT and UPDATE statement with some new values.

INSERT INTO zeta
( zeta_id, last_name ) 
VALUES 
( zeta_s.NEXTVAL, 'Day Lewis' );
 
UPDATE zeta
SET    last_name = 'Day Lewis'
WHERE  last_name = 'Day-Lewis';

The UPDATE statement continues to violate the business rule and the modified white_space trigger throws a different error stack. The new error stack includes the zeta_function because that’s where you throw the error. It is caught and re-thrown by the white_space trigger.

UPDATE zeta
       *
ERROR AT line 1:
ORA-20001: Whitespace replaced WITH hyphen.
ORA-06512: AT "STUDENT.ZETA_FUNCTION", line 47
ORA-06512: AT "STUDENT.WHITE_SPACE", line 13
ORA-04088: error during execution OF TRIGGER 'STUDENT.WHITE_SPACE'

Re-query the zeta table:

SELECT * FROM zeta;

It should display the following values that meet the business rule. The new third row in the table came from the INSERT statement in the test case.

   ZETA_ID LAST_NAME
---------- ------------------------------
         1 Baron-Schwartz
         2 Zeta-Jones
         3 Pinkett-Smith
         4 Day-Lewis

Now, you can query the debug_log table and see the debug messages that you captured from testing the INSERT and UPDATE statements. You get three messages from the INSERT statement test and only two from the UPDATE statement test.

MESSAGE
------------------------------------------------------------------
Unknown event at before IF statement on column [:new.last_name]
Inserting event at after IF statement on column [:new.last_name]
Unknown event at after END IF statement on column [:new.last_name]
Unknown event at before IF statement on column [:new.last_name]
Updating event at after ELSIF statement on column [:new.last_name]

As always, I hope this helps people see new ways to solve problems.

Written by maclochlainn

June 30th, 2021 at 11:30 pm

Posted in Oracle,Oracle 18c,pl/sql,sql

Tagged with ,

Title Case Anyone?

without comments

Sometimes life is too surreal. Like when somebody says, “How do you get title case in an Oracle database?” That’s when you know three things about the individual, while suppressing laughter. They’re not very experienced with SQL, likely lazy, and don’t read the documentation.

I had a little fun with somebody today by taking them down a small rat-hole. “Oh, gosh … ” I said, “… let’s write a function for that.” Here’s the joke function, like:

CREATE OR REPLACE
FUNCTION title_case
( string VARCHAR2 ) RETURN VARCHAR2 IS
BEGIN
  /* Change upper case to title case. */
  RETURN UPPER(SUBSTR(string,1,1)) || LOWER(SUBSTR(string,2,LENGTH(string)));
END title_case;
/

Then, we tested it with a query from the pseudo dual table:

SELECT title_case('incredible') AS "Proper Name" FROM dual;

It returned:

Proper Name
----------
Incredible

Then, I said “Oh, that’s not his proper name in the Pixar World.” It should be: Mr. Incredible. Let’s try that:

SELECT title_case('mr. incredible') AS "Proper Name" FROM dual;

It returned:

Proper Name
--------------
Mr. incredible

Then, I said: “That’s not what we want at all. Should we rewrite our function or simply use the INITCAP built-in function?” Then, I wrote:

SELECT INITCAP('mr. incredible') AS "Proper Name" FROM dual;

It returned:

Proper Name
--------------
Mr. Incredible

Well, needless to say my acquaintance got it immediately and said “I should have Googled it or read the documentation.” I concurred with his observation.

Just sharing a cute day’s event that made me laugh and cry at the same time because there are too many who say SQL isn’t worth learning.

Written by maclochlainn

June 30th, 2021 at 11:49 am

Posted in Linux,MySQL,MySQL 8,Oracle,Oracle 18c,sql,Unix,Windows 10

Tagged with ,

MySQL PHP Transaction

without comments

My students liked the MySQL Transaction post but wanted one that showed how an external web application would interact with MySQL in the scope of a transaction. So, I put a little PHP function together that write across two related tables in the context of a transaction. It uses mysqli (MySQL Improved Extension) to connect PHP to the MySQL database.

The function is barebones and uses the oldest approach of hidden inputs to maintain context between rendered forms using an HTML POST method. The hidden inputs are preceded with “h_” and snake case is used for variable names.

The function only writes to two tables. It writes to the member table and when that completes successfully to the contact table. The function:

  • Submits credentials from a file and raises an error when they don’t work.
  • Initializes a SQL statement.
  • Disables auto commit.
  • Starts a transaction.
  • Defines a first SQL statement with placeholders.
  • Binds local variables to the first SQL statement’s placeholders.
  • Rolls back the transaction when the first statement fails and continues to the next SQL statement when first statement succeeds.
  • Defines a second SQL statement with placeholders.
  • Binds local variables to the second SQL statement’s placeholders.
  • Rolls back the transaction when the second statement fails and commits the work when the second statement succeeds.
  • Closes a SQL statement.

The code for the PHP function is:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129

/*
||  Function Name: update_membership
|| ----------------------------------------------------------------------
||  No return, treated as a procedure, or method returning a void.
|| ----------------------------------------------------------------------
*/
function update_membership(
    $h_account_number
  , $h_member_type
  , $h_credit_card_number
  , $h_credit_card_type
  , $account_number
  , $member_type
  , $credit_card_number
  , $credit_card_type
  , $h_first_name
  , $h_middle_name
  , $h_last_name
  , $h_contact_type
  , $first_name
  , $middle_name
  , $last_name
  , $contact_type) {
 
  // Include the credentials file if omitted.
  include_once("MySQLCredentials.inc");
 
  // Assign credentials to connection.
  $mysqli = new mysqli(HOSTNAME, USERNAME, PASSWORD, DATABASE);
 
  // Check for connection error and print message.
  if ($mysqli->connect_errno) {
    print $mysqli->connect_error."<br />";
    print "Connection not established ...<br />";
  }
  else {
    // Initial statement.
    $stmt = $mysqli->stmt_init();
 
    // Disable auto commit when you want two plus statements run.
    $mysqli->autocommit(FALSE);
 
    // Set the transaction guarantee.
    $mysqli->begin_transaction(MYSQLI_TRANS_START_READ_WRITE);
 
    // Declare a static query.
    $sql = "UPDATE   member\n"
         . "SET      member_type = ?\n"
         . ",        credit_card_number = ?\n"
         . ",        credit_card_type = ?\n"
         . "WHERE    account_number = ?\n"
         . "AND      member_type = ?\n"
         . "AND      credit_card_number = ?\n"
         . "AND      credit_card_type = ?\n";
 
    // Prepare statement.
    if ($stmt->prepare($sql)) {
      $stmt->bind_param(
          "sssssss"
         , $member_type
         , $credit_card_number
         , $credit_card_type
         , $account_number
         , $h_member_type
         , $h_credit_card_number
         , $h_credit_card_type); } 
 
    // Attempt query and exit with failure before processing.
    if (!$stmt->execute()) {
 
      // Rollback or undo the transaction.
      $mysqli->rollback();
 
      // Print failure to resolve query message.
      print $mysqli->error."<br />\n";
      print "Failed to resolve query ...<br />\n";
    }
 
    // Declare a static query.
    $sql = "UPDATE   contact\n"
         . "SET      first_name = ?\n"
         . ",        middle_name = ?\n"
         . ",        last_name = ?\n"
         . ",        contact_type = ?\n"
         . "WHERE    first_name = ?\n"
         . "AND      middle_name = ?\n"
         . "AND      last_name = ?\n"
         . "AND      contact_type = ?\n";
 
    // Prepare statement.
    if ($stmt->prepare($sql)) {
      $stmt->bind_param(
          "ssssssss"
        , $first_name
        , $middle_name
        , $last_name
        , $contact_type
        , $h_first_name
        , $h_middle_name
        , $h_last_name
        , $h_contact_type); } 
 
    // Attempt query and exit with failure before processing.
    if (!$stmt->execute()) {
 
      // Rollback or undo the transaction.
      $mysqli->rollback();
 
      // Print failure to resolve query message.
      print $mysqli->error."<br />";
      print "Failed to resolve query ...<br />";
    }
    else {   
      /*  Manually commiting writes when you have disabled the
      ||  default auto commit setting, explained above.
      || ------------------------------------------------------------
      ||  You would add the following command to commit the 
      ||  transaction.
      ||  ------------------------------
      ||   $mysqli->commit();
      || ------------------------------------------------------------
      */
      $mysqli->commit();
 
      // Close the transaction.
      $mysqli->close();
    }
  }
}

Line 41 disables auto commit and line 44 starts the transaction. Each statement is managed with the subsequent statement nested inside a block of code that is only reachable when the prior statement succeeds. While this only uses the member and contact table, it could use any number of tables. The natural alternative is building an updatable view.

As always, I hope this helps anybody looking for a code complete example.

Written by maclochlainn

June 29th, 2021 at 5:23 pm

Posted in LAMP,Linux,MySQL,MySQL 8,mysqli,sql

Tagged with ,

SQL Handling Nulls

without comments

Interesting questions always come via my students. For example, “Why does the selective aggregation sample return null values as totals from the SUM() function in MySQL?”

First, here’s the code to build the sample table for the problem:

DROP TABLE IF EXISTS transaction;
CREATE TABLE transaction
( transaction_id      int unsigned primary key auto_increment
, transaction_date    date
, transaction_amount  double );
 
INSERT INTO transaction
( transaction_date, transaction_amount )
VALUES
 ('2021-01-10', 56)
,('2021-02-14',23.02)
,('2021-03-31',31.06)
,('2021-01-01',.25)
,('2020-01-02', 52)
,('2020-02-08',22.02)
,('2020-03-26',32.06)
,('2020-01-12',.75);;

Now, here’s the selective aggregation query:

SELECT   EXTRACT(YEAR FROM transaction_date) AS "Year"
,        SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 1 THEN transaction_amount
            END) AS "Jan"
,        SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 2 THEN transaction_amount
           END) AS "Feb"
,        SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 3 THEN transaction_amount
           END) AS "Mar"
,        SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) IN (1,2,3) THEN transaction_amount
           END) AS "1FQ"
FROM     transaction
WHERE    transaction_date BETWEEN '2020-01-15' AND '2021-03-15'
GROUP BY EXTRACT(YEAR FROM transaction_date)
ORDER BY 1;

It produces the following correct results (at least in response to the query above):

+------+-------+-------+-------+-------+
| Year | Jan   | Feb   | Mar   | 1FQ   |
+------+-------+-------+-------+-------+
| 2020 |  NULL | 22.02 | 32.06 | 54.08 |
| 2021 | 56.25 | 23.02 |  NULL | 79.27 |
+------+-------+-------+-------+-------+
2 rows in set (0.02 sec)

Why do you get null values for January 2020 and March 2021? That’s because the query returns null values when the conditions in the SELECT-list aren’t met for a row return. This happens:

  • When a row is returned for the month of February or March a null is returned in the January column.
  • When a row is returned for the month of January or March a null is returned in the February column.
  • When a row is returned for the month of January or February a null is returned in the March column.

That means there needs to be an ELSE clause in each of the CASE statements that sets the return value to zero. For example, the following query includes the ELSE clause and some nice formatting tricks:

SELECT   EXTRACT(YEAR FROM transaction_date) AS "Year"
,        CONCAT('$',LPAD(FORMAT(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 1 THEN transaction_amount
             ELSE 0
            END),2),8,' ')) AS "Jan"
,        LPAD(FORMAT(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 2 THEN transaction_amount
             ELSE 0
           END),2),8,' ') AS "Feb"
,        LPAD(FORMAT(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 3 THEN transaction_amount
             ELSE 0
           END),2),8,' ') AS "Mar"
,        LPAD(FORMAT(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) IN (1,2,3) THEN transaction_amount
             ELSE 0
           END),2),8,' ') AS "1FQ"
FROM     transaction
WHERE    transaction_date BETWEEN '2020-01-15' AND '2021-03-15'
GROUP BY EXTRACT(YEAR FROM transaction_date)
ORDER BY 1;

It returns:

+------+-----------+----------+----------+----------+
| Year | Jan       | Feb      | Mar      | 1FQ      |
+------+-----------+----------+----------+----------+
| 2020 | $    0.00 |    22.02 |    32.06 |    54.08 |
| 2021 | $   56.25 |    23.02 |     0.00 |    79.27 |
+------+-----------+----------+----------+----------+
2 rows in set (0.01 sec)

As always, I hope this helps answer a question that somebody is trying to sort out.

Written by maclochlainn

June 23rd, 2021 at 12:23 pm

Posted in MySQL,MySQL 8,MySQL Client,MySQL Workbench

Tagged with ,

Mongo List Databases

without comments

After you install MongoDB on your Linux system, you can connect without a user name or password and discover version and many other details about the installation, as shown in this prior post.

You can use the db.adminCommand() to understand the existing databases, as qualified in the online documentation. The basic syntax to list all databases is:

db.adminCommand( {listDatabases:1} )

In a generic Linux installation you should see something like the following:

{
        "databases" : [
                {
                        "name" : "admin",
                        "sizeOnDisk" : 32768,
                        "empty" : false
                },
                {
                        "name" : "config",
                        "sizeOnDisk" : 73728,
                        "empty" : false
                },
                {
                        "name" : "local",
                        "sizeOnDisk" : 90112,
                        "empty" : false
                },
                {
                        "name" : "test",
                        "sizeOnDisk" : 49152,
                        "empty" : false
                }
        ],
        "totalSize" : 245760,
        "ok" : 1
}

You can filter for a test database and find only your authorization to use the test database with the following command:

db.adminCommand( {listDatabases:1, filter: {"name": /test/}, authorizedDatabases: true} )

It returns:

{
        "databases" : [
                {
                        "name" : "test",
                        "sizeOnDisk" : 49152,
                        "empty" : false
                }
        ],
        "totalSize" : 49152,
        "ok" : 1
}

If you just want the names of your authorized databases, use this syntax:

db.adminCommand( {listDatabases:1, nameOnly: true, authorizedDatabases: true} )

It should return:

{
        "databases" : [
                {
                        "name" : "admin"
                },
                {
                        "name" : "config"
                },
                {
                        "name" : "local"
                },
                {
                        "name" : "test"
                }
        ],
        "ok" : 1
}

As always, I hope this helps those trying to sort through the official documentation and learn how to do things.

Written by maclochlainn

June 16th, 2021 at 11:09 pm

Posted in Linux,MongoDB

Tagged with ,

MySQL Outer Joins

without comments

The students needed yet another example of LEFT JOIN, RIGHT JOIN, and FULL JOIN syntax (by combining a left and right join with the UNION set operator). To that end, I put this set of examples together.

The example also shows how to order the result set from a derived table with the UNION operator. It uses the WITH clause to build a Common Table Expression (CTE), which allows the query to order the UNION set operator’s product based on the left and right join queries. It uses a CASE statement to order the result sets. The left_table is the parent table and the right_table is the child table in the relationship, which means the right_table holds a left_id foreign key column that lets you connect matching rows in the left_table.

You build the little model with the following script:

-- -----------------------------------------------------------------
-- Drop the demonstration tables.
-- -----------------------------------------------------------------
DROP TABLE IF EXISTS left_table, right_table;
 
-- -----------------------------------------------------------------
-- Create left_table.
-- -----------------------------------------------------------------
CREATE TABLE left_table
( left_id        int unsigned primary key auto_increment
, leftstring     varchar(10));
 
-- -----------------------------------------------------------------
-- Create left_table.
-- -----------------------------------------------------------------
CREATE TABLE right_table
( right_id       int unsigned primary key auto_increment
, left_id        int unsigned
, rightstring         varchar(10));
 
-- -----------------------------------------------------------------
-- Insert five rows to the left table, which holds a 
-- left_id primary key column.
-- -----------------------------------------------------------------
INSERT INTO left_table (leftstring) values ('One');
INSERT INTO left_table (leftstring) values ('Two');
INSERT INTO left_table (leftstring) values ('Three');
INSERT INTO left_table (leftstring) values ('Four');
INSERT INTO left_table (leftstring) values ('Five');
 
-- -----------------------------------------------------------------
-- Delete row four to create a gap.
-- -----------------------------------------------------------------
DELETE FROM left_table where left_id = 4;
 
-- -----------------------------------------------------------------
--  Insert four rows, skipping a foreign key value for the 
--  left_id primary key value of 2.
-- -----------------------------------------------------------------
 
INSERT INTO right_table (rightstring,left_id) values ('One',1);
INSERT INTO right_table (rightstring,left_id) values ('Three',3);
INSERT INTO right_table (rightstring,left_id) values ('Four',4);
INSERT INTO right_table (rightstring,left_id) values ('Five',5);

Here are the join statements:

INNER JOIN

The INNER JOIN only returns those rows that match between a primary and foreign key column or set of columns.

SELECT l.left_id
,      l.leftstring
,      r.left_id
,      r.right_id
,      r.rightstring
FROM   left_table l INNER JOIN right_table r
ON     l.left_id = r.left_id;

It produces the following result set:

+---------+------------+---------+----------+-------------+
| left_id | leftstring | left_id | right_id | rightstring |
+---------+------------+---------+----------+-------------+
|       1 | One        |       1 |        1 | One         |
|       3 | Three      |       3 |        2 | Three       |
|       5 | Five       |       5 |        4 | Five        |
+---------+------------+---------+----------+-------------+
3 rows in set (0.00 sec)

LEFT OUTER JOIN

The LEFT OUTER JOIN only returns those rows that match between a primary and foreign key column or set of columns and any rows in the table on the lefthand side of the join that fail to match with any row on the righthand side of the join. The non-matching rows are also known as the right complement of the join.

1
2
3
4
5
6
7

SELECT l.left_id
,      l.leftstring
,      r.left_id
,      r.right_id
,      r.rightstring
FROM   left_table l LEFT JOIN right_table r
ON     l.left_id = r.left_id;

It produces the following result set:

+---------+------------+---------+----------+-------------+
| left_id | leftstring | left_id | right_id | rightstring |
+---------+------------+---------+----------+-------------+
|       1 | One        |       1 |        1 | One         |
|       2 | Two        |    NULL |     NULL | NULL        |
|       3 | Three      |       3 |        2 | Three       |
|       5 | Five       |       5 |        4 | Five        |
+---------+------------+---------+----------+-------------+
4 rows in set (0.00 sec)

Add the following line 8 to the query and you get only those rows in the lefthand table that have no child-related rows in the righthand table. These rows are sometimes called childless parent rows. More or less, the use case for this type of query is to find order headers without order lines.

6
7
8

FROM   left_table l LEFT JOIN right_table r
ON     l.left_id = r.left_id
WHERE  r.left_id IS NULL;

It produces the following result set:

+---------+------------+---------+----------+-------------+
| left_id | leftstring | left_id | right_id | rightstring |
+---------+------------+---------+----------+-------------+
|       2 | Two        |    NULL |     NULL | NULL        |
+---------+------------+---------+----------+-------------+
1 row in set (0.00 sec)

RIGHT OUTER JOIN

1
2
3
4
5
6
7

SELECT l.left_id
,      l.leftstring
,      r.left_id
,      r.right_id
,      r.rightstring
FROM   left_table l RIGHT JOIN right_table r
ON     l.left_id = r.left_id;

It produces the following result set:

+---------+------------+---------+----------+-------------+
| left_id | leftstring | left_id | right_id | rightstring |
+---------+------------+---------+----------+-------------+
|       1 | One        |       1 |        1 | One         |
|       3 | Three      |       3 |        2 | Three       |
|    NULL | NULL       |       4 |        3 | Four        |
|       5 | Five       |       5 |        4 | Five        |
+---------+------------+---------+----------+-------------+
4 rows in set (0.00 sec)

Add the following line 8 to the query and you get only those rows in the righthand table that have no parent-related rows in the lefthand table. These rows are sometimes called orphans because they have no parent row. More or less, the use case for this type of query is to find latent order lines after deleting the order header.

6
7
8

FROM   left_table l LEFT JOIN right_table r
ON     l.left_id = r.left_id
WHERE  l.left_id IS NULL;

It produces the following result set:

+---------+------------+---------+----------+-------------+
| left_id | leftstring | left_id | right_id | rightstring |
+---------+------------+---------+----------+-------------+
|    NULL | NULL       |       4 |        3 | Four        |
+---------+------------+---------+----------+-------------+
1 row in set (0.00 sec)

FULL OUTER JOIN

The full outer join doesn’t exist in MySQL, so you combine a LEFT OUTER JOIN and RIGHT OUTER JOIN with the UNION operator. The UNION operator eliminates the duplicate row from the intersection of the joins.

Here’s the full query:

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

WITH cte AS
(SELECT l.left_id AS primary_left_id
,      l.leftstring
,      r.left_id  AS foreign_left_id
,      r.right_id
,      r.rightstring
FROM   left_table l LEFT JOIN right_table r
ON     l.left_id = r.left_id
UNION
SELECT l.left_id  AS primary_left_id
,      l.leftstring
,      r.left_id  AS foreign_left_id
,      r.right_id
,      r.rightstring
FROM   left_table l RIGHT JOIN right_table r
ON     l.left_id = r.left_id)
SELECT * FROM cte
ORDER BY
CASE
  WHEN 'One'   IN (leftstring, rightstring) THEN 1
  WHEN 'Two'   IN (leftstring, rightstring)  THEN 2
  WHEN 'Three' IN (leftstring, rightstring)  THEN 3
  WHEN 'Four'  IN (leftstring, rightstring)  THEN 4
  WHEN 'Five'  IN (leftstring, rightstring)  THEN 5
END;

It produces the following result set:

+-----------------+------------+-----------------+----------+-------------+
| primary_left_id | leftstring | foreign_left_id | right_id | rightstring |
+-----------------+------------+-----------------+----------+-------------+
|               1 | One        |               1 |        1 | One         |
|               2 | Two        |            NULL |     NULL | NULL        |
|               3 | Three      |               3 |        2 | Three       |
|            NULL | NULL       |               4 |        3 | Four        |
|               5 | Five       |               5 |        4 | Five        |
+-----------------+------------+-----------------+----------+-------------+
5 rows in set (0.00 sec)

Add the following lines 18 and 19 to the query and you get only those rows that are childless parent rows or orphaned child rows. More or less, the use case for this type of query is to find both order headers without order lines and order lines abandoned by deleted order headers.

17
18
19

SELECT * FROM cte
WHERE  primary_left_id IS NULL
OR     foreign_left_id IS NULL

It produces the following result set:

+-----------------+------------+-----------------+----------+-------------+
| primary_left_id | leftstring | foreign_left_id | right_id | rightstring |
+-----------------+------------+-----------------+----------+-------------+
|               2 | Two        |            NULL |     NULL | NULL        |
|            NULL | NULL       |               4 |        3 | Four        |
+-----------------+------------+-----------------+----------+-------------+
2 rows in set (0.00 sec)

The ORDER BY clause used is a variation on the more common choice of:

  WHEN leftstring = 'One'   OR rightstring = 'One'   THEN 1

The position of two string literals in any statement is a bad idea. Inverting the literal on the right and using a IN operator gives you a better and clearer WHEN statement:

  WHEN 'One'   IN (leftstring, rightstring) THEN 1

As always, I hope this helps those looking for syntax examples.

Written by maclochlainn

June 15th, 2021 at 10:36 pm

Posted in Database Design,MySQL,MySQL 8,sql

Tagged with ,