EXPLAIN EXTENDED

Some say SQL is not good at graphics.

Well, they have some point. Database engines lack scanner drivers, there is no easy way to do sepia, and magic wand, let's be honest, is just poor.

However, you can make some New Year paintings with SQL.

Let's make a New Year clock showing 12 o'clock in Oracle.

#1. Circle

First, we need a circle shape. In Photoshop, you could just select it from the toolbar, but in SQL, we need to use some math.

In math, a circle is defined by this formula: x2 + y2 = R2.

x2 + y2 is, as we know, the square of a distance from the center of the coordinate grid, and R is the radius. This means all points at distance R from the center, which of course is a circle shape.

SQL outputs data in tabular format. We will draw out art string by string, and for each line we need to know where to put the paint (just some ASCII symbols). Since it's a circle, every line intersects it at most twice, and we shall put the symbols at the places where the circle and the line intersect. To know how many symbols are there from the beginning of the string, this formula is used:

ROUND(SQRT(1 - POWER((level - 21) / 20, 2)) * 20) AS angle

To make our circle more circle-shaped, we need to choose the characters that correspond to the angle of the circle line in each given place. These characters are the most similar to the lines: =/|\

Different characters correspond to different angles. To figure out the angle from the line number, we should use ACOS:

ROUND(ACOS(angle / 20) / 3.1415926 * 4) * SIGN((line - 21) / 20) + 3 AS sign

This would split the circle into several sectors, and for each of these sectors a corresponding character will be chosen.

Finally, we need to avoid the gaps in the lines. To do this, we will not only fill the intersections, but all space from the previous (or next) intersection to the current. To calculated the width of the string on each line that will be filled with the characters, we will use LAG and LEAD, the analytic functions.

Now, let's put it all together:

WITH    circle AS
(
SELECT  1 AS layer,
line,
LPAD(RPAD(SUBSTR('=/|\=', sign, 1), width, SUBSTR('=/|\=', sign, 1)), 20 + width - angle, ' ') ||
RPAD(' ', (angle - width) * 2, ' ') ||
RPAD(RPAD(SUBSTR('=\|/=', sign, 1), width, SUBSTR('=\|/=', sign, 1)), 20 + width - angle, ' ') AS drawing
FROM    (
SELECT  line,
angle,
ABS
(
angle -
CASE
WHEN line < 21 THEN
                                COALESCE(LAG(angle) OVER (ORDER BY line), 0)
                        WHEN line > 21 THEN
COALESCE(LEAD(angle) OVER (ORDER BY line), 0)
ELSE
angle
END
) + 1 AS width,
ROUND(ACOS(angle / 20) / 3.1415926 * 4)  * SIGN((line - 21) / 20) + 3 AS sign
FROM    (
SELECT  level AS line,
ROUND(SQRT(1 - POWER((level - 21) / 20, 2)) * 20) AS angle
FROM    dual
CONNECT BY
level <= 41
                        ) q
                ) q
        )
SELECT  *
FROM    circle

#2. Dial

Now, we shall draw the clock dial on a separate layer (yes, you can draw on layers in SQL).

We will use Roman numerals for the dial. Oracle has an internal function to format the numbers as Roman numerals, but SQL graphics is the first time I'm using this feature in practice.

The principle is the same: we should calculate the angle of each number, then the line and the column that intersect the dial at the given angle and put the number there.

We will only output the lines containing actual numbers. Each line will contain one or two numbers (1 and 6 go on their own lines, the others go in pairs). We can use MIN and MAX to distinguish them.

Here's what the lines of our dial will look like:

WITH    dial AS
(
SELECT  2,
line,
RPAD(' ', 20 - angle, ' ') ||
RPAD(rnf, angle * 2 - LENGTH(rns)) ||
RPAD(rns, 20 - angle + DECODE(rnf, NULL, 0, LENGTH(rns)) + 1)
FROM    (
SELECT  line, angle,
DECODE(MAX(h), MIN(h), NULL, TRIM(TO_CHAR(MAX(h), 'RN'))) AS rnf,
TRIM(TO_CHAR(MIN(h), 'RN')) AS rns
FROM    (
SELECT  level + 2 AS line,
ROUND(SQRT(1 - POWER((level - 19) / 18, 2)) * 18) AS angle
FROM    dual
CONNECT BY
level <= 37
                        ) lines
                JOIN    (
                        SELECT  level AS h, ROUND(-COS(3.141592 * level / 6) * 18) + 21 AS hline
                        FROM    dual
                        CONNECT BY
                                level <= 12
                        ) hours
                ON      hline = line
                GROUP BY
                        line, angle
                ) q
        )
SELECT  *
FROM    dial
ORDER BY
        line

Note that the shape is distorted: this is because of the gaps in the line numbers. It will be taken care of later.

#3. Hands and the center pin

Hands and the center pin are simple: we will use ||| for the hour hand, | for the minute hand and O for the pin. On a New Year clock the hands, fortunately, are vertical and directed in one way.

Here's the hour hand:

WITH    hourhand AS
(
SELECT  3 AS layer,
level + 10 AS line,
RPAD(' ', 20, ' ') || '|||' || RPAD(' ', 18, ' ') AS drawing
FROM    dual
CONNECT BY
level <= 10
        )
SELECT  *
FROM    hourhand
ORDER BY
        line

, the minute hand:

WITH    minutehand AS
(
SELECT  4 AS layer,
level + 4 AS line,
RPAD(' ', 21, ' ') || '|' || RPAD(' ', 19, ' ') AS drawing
FROM    dual
CONNECT BY
level <= 16
        )
SELECT  *
FROM    minutehand
ORDER BY
        line

and the pin:

WITH    pin AS
(
SELECT  5 AS layer,
21 AS line,
RPAD(LPAD('O', 22, ' '), 41, ' ') AS drawing
FROM    dual
)
SELECT  *
FROM    pin
ORDER BY
line

#4. Merging the layers.

Now we need to merge the layers.

The last layers should go on the foreground, the first ones on the background. Space symbol should be transparent: if there is a character on the background layer, it should be visible through it.

To do it we we apply this trick:

1. Output all rows from all layers in a single query, using UNION ALL
2. Split each row into the individual characters, one character per record
3. Using ROW_NUMBER(), the analytical function, order the characters within each line and columns so that visible characters from higher layers go first, and spaces and characters from the lower layers go last:

   ROW_NUMBER() OVER (PARTITION BY line, col ORDER BY DECODE(cc, ' ', 1, 0), layer DESC) AS rn

4. Filter the characters on rn so that only the topmost character from the each line and column is returned
5. Concatenate all characters line-wise, using a recursive query and SYS_CONNECT_BY_PATH. This function requires a separator, and we will use a question mark, ?, for this purpose, since it's not used in our art.
6. Remove the separator from the concatenated strings, using REPLACE
7. Output the result

#5. Result

Here's our query and drawing:

WITH    circle AS
(
SELECT  1 AS layer,
line,
LPAD(RPAD(SUBSTR('=/|\=', sign, 1), width, SUBSTR('=/|\=', sign, 1)), 20 + width - angle, ' ') ||
RPAD(' ', (angle - width) * 2, ' ') ||
RPAD(RPAD(SUBSTR('=\|/=', sign, 1), width, SUBSTR('=\|/=', sign, 1)), 20 + width - angle, ' ') AS drawing
FROM    (
SELECT  line,
angle,
ABS
(
angle -
CASE
WHEN line < 21 THEN
                                COALESCE(LAG(angle) OVER (ORDER BY line), 0)
                        WHEN line > 21 THEN
COALESCE(LEAD(angle) OVER (ORDER BY line), 0)
ELSE
angle
END
) + 1 AS width,
ROUND(ACOS(angle / 20) / 3.1415926 * 4)  * SIGN((line - 21) / 20) + 3 AS sign
FROM    (
SELECT  level AS line,
ROUND(SQRT(1 - POWER((level - 21) / 20, 2)) * 20) AS angle
FROM    dual
CONNECT BY
level <= 41
                        ) q
                ) q
        ),
        dial AS
        (
        SELECT  2,
                line,
                RPAD(' ', 20 - angle, ' ') ||
                RPAD(rnf, angle * 2 - LENGTH(rns)) ||
                RPAD(rns, 20 - angle + DECODE(rnf, NULL, 0, LENGTH(rns)) + 1)
        FROM    (
                SELECT  line, angle,
                        DECODE(MAX(h), MIN(h), NULL, TRIM(TO_CHAR(MAX(h), 'RN'))) AS rnf,
                        TRIM(TO_CHAR(MIN(h), 'RN')) AS rns
                FROM    (
                        SELECT  level + 2 AS line,
                                ROUND(SQRT(1 - POWER((level - 19) / 18, 2)) * 18) AS angle
                        FROM    dual
                        CONNECT BY
                                level <= 37
                        ) lines
                JOIN    (
                        SELECT  level AS h, ROUND(-COS(3.141592 * level / 6) * 18) + 21 AS hline
                        FROM    dual
                        CONNECT BY
                                level <= 12
                        ) hours
                ON      hline = line
                GROUP BY
                        line, angle
                ) q
        ),
        hourhand AS
        (
        SELECT  3 AS layer,
                level + 10 AS line,
                RPAD(' ', 20, ' ') || '|||' || RPAD(' ', 18, ' ') AS drawing
        FROM    dual
        CONNECT BY
                level <= 10
        ),
        minutehand AS
        (
        SELECT  4 AS layer,
                level + 4 AS line,
                RPAD(' ', 21, ' ') || '|' || RPAD(' ', 19, ' ') AS drawing
        FROM    dual
        CONNECT BY
                level <= 16
        ),
        pin AS
        (
        SELECT  5 AS layer,
                21 AS line,
                RPAD(LPAD('O', 22, ' '), 41, ' ') AS drawing
        FROM    dual
        ),
        m AS
        (
        SELECT  line, col, cc
        FROM    (
                SELECT  line, col, cc,
                        ROW_NUMBER() OVER (PARTITION BY line, col ORDER BY DECODE(cc, ' ', 1, 0), layer DESC) AS rn
                FROM    (
                        SELECT  cols.*, layers.*, SUBSTR(drawing, col, 1) AS cc
                        FROM    (
                                SELECT  level AS col
                                FROM    dual
                                CONNECT BY
                                        level <= 50
                                ) cols
                        CROSS JOIN
                                (
                                SELECT  *
                                FROM    circle
                                UNION ALL
                                SELECT  *
                                FROM    dial
                                UNION ALL
                                SELECT  *
                                FROM    hourhand
                                UNION ALL
                                SELECT  *
                                FROM    minutehand
                                UNION ALL
                                SELECT  *
                                FROM    pin
                                ) layers        
                        )
                )
        WHERE   rn = 1
        )
SELECT  REPLACE(drawing, '?') AS drawing
FROM    (
        SELECT  SYS_CONNECT_BY_PATH(cc, '?') AS drawing, line, CONNECT_BY_ISLEAF AS leaf
        FROM    m mi
        START WITH
                mi.col = 1
        CONNECT BY
                mi.line = PRIOR mi.line
                AND mi.col = PRIOR mi.col + 1
        )
WHERE   leaf = 1
ORDER BY
        line