rho_n/hpr_generator

Archived

Fork 1

Table of Contents

Documentation of the query query_next_available_episode

Purpose
Overview
Query analysis

First CTE (next_id)
Second CTE (free_slot)
Main query

Differences in the SQLite version

Documentation of the query `query_next_available_episode`

Purpose

This query finds the next free slot in the database. It starts from the slot with the latest date and looks forward for the next numeric slot that is empty. The query returns the number of the free slot found, its date, and the number of days from the current date the date of the free slot.

The date difference is used in the template content-index.tpl.html which generates the Home or Index page on the static site.

Overview

There are two versions of the query, for MySQL and SQLite databases. The queries are held (with other queries) in the files queries-index-mysql.tpl.html and queries-index-sqlite.tpl.html.

Query analysis

The query contains two CTEs (Common Table Expressions). These are a relatively new addition to the SQL standard (added in 2005). They can be regarded as temporary views which can be used in other parts of the query.

CTEs are introduced by the WITH statement:

WITH cte_1 AS (CTE definition 1),
     cte_2 AS (CTE definition 2)
SELECT
    main query;

First CTE (`next_id`)

This is:

WITH next_id AS (
    SELECT id, id + 1 AS id_next, date as last_date
        FROM eps
        WHERE eps.date > NOW()
),

It is a simple SELECT on the eps table looking at all future shows from the current date onwards. This is the MySQL variant. It can be run stand-alone to see what it does, but because the MySQL database doesn't return episodes in id order it's necessary to use:

SELECT id, id + 1 AS id_next, date as last_date
FROM eps
WHERE eps.date > NOW()
order by id limit 5;

This (when run on 2023-10-12) gives (using only 5 rows for brevity):

+------+---------+------------+
| id   | id_next | last_date  |
+------+---------+------------+
| 3965 |    3966 | 2023-10-13 |
| 3970 |    3971 | 2023-10-20 |
| 3971 |    3972 | 2023-10-23 |
| 3973 |    3974 | 2023-10-25 |
| 3980 |    3981 | 2023-11-03 |
+------+---------+------------+

This is the view-like or table-like information which will be available to the rest of the query. Note that we see allocated episode numbers and that number plus 1 for each row. The former exists in the table, while the latter may not. This is done because it's not possible to look up an non-existent row!

Second CTE (`free_slot`)

This CTE uses the output from the first one (next_id) as well as the contents of the eps table:

free_slot AS (
    SELECT
        last_date AS prev_date,
        CASE
            WHEN DAYOFWEEK(last_date) = 6
                THEN 3
            ELSE 1
        END AS date_offset,
        next_id.id_next AS empty_slot
    FROM next_id
    LEFT JOIN eps ON next_id.id_next = eps.id
    WHERE eps.id IS NULL
    ORDER BY next_id.id
    LIMIT 1
)

Here the query is using SELECT on the rows from the first CTE (next_id) and joining them with the eps table, using next_id.id_next as the joining criterion. A LEFT JOIN is used because it reveals null values.

If the eps.id value is NULL it means there is no matching row in the eps table. In other words, a free slot has been found.

The ordering of the eps_id column is done in this CTE because of the lack of order in the table and because it's simpler to add it here than in the other CTE.

We are only interested in the first row that matches the criteria, so a LIMIT 1 is used.

The prev_date field returned will hold the date of the episode before the empty slot.
The CASE expression computes the day of the week of this date, which in MySQL is 1 for Sunday and 7 for Saturday. We are interested in whether it's a Friday because we need to return the offset needed to reach the next weekday. This will be 3 days for Friday and 1 day for other days. We trust that the prev_date will never fall on a weekend. What is returned is a field called date_offset containing either a 3 or a 1.
The empty_slot field uses the next_id.id_next value which is the number of the episode that doesn't exist yet.

Testing these two CTEs with a simple main query we see:

WITH next_id AS (
    SELECT id, id + 1 AS id_next, date as last_date
        FROM eps
        WHERE eps.date > NOW()
),
free_slot AS (
    SELECT
        last_date AS prev_date,
        CASE
            WHEN DAYOFWEEK(last_date) = 6
                THEN 3
            ELSE 1
        END AS date_offset,
        next_id.id_next AS empty_slot
    FROM next_id
    LEFT JOIN eps ON next_id.id_next = eps.id
    WHERE eps.id IS NULL
    ORDER BY next_id.id
    LIMIT 1
)
SELECT * FROM free_slot;

This produces the output:

+------------+-------------+------------+
| prev_date  | date_offset | empty_slot |
+------------+-------------+------------+
| 2023-10-13 |           3 |       3966 |
+------------+-------------+------------+

This shows the date of the last show before the empty slot, the number of days to offset it and the number of the free slot.

Main query

Finally, the main query makes use of the three values returned from the two CTEs:

SELECT
    empty_slot AS next_id,
    ADDDATE(prev_date,date_offset) AS next_date,
    DATEDIFF(ADDDATE(prev_date,date_offset),NOW()) AS delta_days
FROM free_slot

This query computes the values actually needed by the template that invokes it:

Field next_id contains the number of the empty slot.
Field next_date contains the date of the empty slot. This is computed by adding 3 days to the date from the CTEs (in this example on 2023-10-12).
Field delta_days is computed by subtracting the current date from the future next_date value using DATEDIFF.

Using the full query against the database on 2023-10-12 we get the following result:

+---------+------------+------------+
| next_id | next_date  | delta_days |
+---------+------------+------------+
|    3966 | 2023-10-16 |          4 |
+---------+------------+------------+

Differences in the SQLite version

Many of the functions used take string arguments, and since the query is stored in a single-quoted string, these have to be escaped with a backslash.
The SQLite function to get the current date is date('now')
The SQLite function to get the day of the week from a date is strftime('%w',last_date) and the values are 0 for Sunday and 6 for Saturday.
When adding days to a date in SQLite the function is date(date,'+3 days') and this is why such strings are returned from the CASE statement.
To compute the difference in days between two dates in SQLite it is necessary to convert dates to Julian dates and subtract the values. This can give a negative result, so we use abs() to prevent it. The result is fractional because of the way Julian dates are implemented in SQLite, so printf('%i,?) is used to get the integer part. Therefore, the following expression is needed:

    printf('%i',
        abs(
            julianday(DATE (prev_date, date_offset)) -
            julianday('now') + 1)
    ) AS delta_days

Documentation of the query query_next_available_episode