Move logic to data

logic-to-data.sql

/*
    Moving logic to data

    DuckDB version: v1.3.1

    Bill Wallis, 2025-07-24
*/
select version();


create or replace table testing.events (
    event_id uuid primary key default uuidv7(),
    status_code int,
    error_message text,
);

/* Generate some fake data (~30s) */
insert into testing.events(status_code, error_message)
    with

    statuses(cumulative_distribution, status_code) as (
        values
            (0.50, 404),
            (0.70, 410),
            (0.85, 403),
            (1.00, 500),
    ),

    logs(status_code, cumulative_distribution, error_message) as (
        values
            (403, 0.80, 'user {:s} is not authorised'),
            (403, 1.00, 'user {:s} is blocked'),
            (404, 0.50, 'unable to get details for product {:d} in {:s}'),
            (404, 1.00, 'product {:d} no longer exists in {:s}'),
            (410, 1.00, 'product {:d} is no longer available'),
            (500, 0.95, 'unexpected server error: {:s} not found'),
            (500, 1.00, 'unexpected server error: thronglets have taken over'),
    ),

    gs(n, r) as (
        select n, random()
        -- from generate_series(99) as gs(n)
        from generate_series(9_999_999) as gs(n)
    )

    select
        statuses.status_code,
        case statuses.status_code
            when 403 then format(logs.error_message, left(uuid()::text, 8))
            when 404 then format(logs.error_message, (r * 1000)::int, left(uuid()::text, 6))
            when 410 then format(logs.error_message, (r * 1000)::int, left(uuid()::text, 6))
            when 500 then format(logs.error_message, left(uuid()::text, 8))
        end,
    from gs
        asof left join statuses
            on gs.r < statuses.cumulative_distribution
        asof left join logs
            on  statuses.status_code = logs.status_code
            and gs.r < logs.cumulative_distribution
    order by gs.n
;

from testing.events
order by event_id
;




/* Using CASE */
select
    event_id,
    status_code,
    error_message,
    case status_code
        when 403 then 'Forbidden'
        when 404 then 'Not Found'
        when 410 then 'Gone'
        when 500 then 'Internal Server Error'
    end as status_text,
    case
        when error_message like 'user % is not authorised'
            then 'Unauthorised User'
        when error_message like 'user % is blocked'
            then 'Blocked User'
        when error_message like 'unable to get details for product % in %'
            then 'Product Not Found'
        when error_message like 'product % no longer exists in %'
            then 'Product Unavailable'
        when error_message like 'product % is no longer available'
            then 'Product Unavailable'
        when error_message like 'unexpected server error: % not found'
            then 'Resource Not Found'
        when error_message like 'unexpected server error: thronglets have taken over'
            then 'Thronglets'
            else 'Unknown Error'
    end as error_category,
from testing.events
-- order by event_id
;


/* Using CTEs */
with

status_codes(status_code, status_text) as (
    values
        (403, 'Forbidden'),
        (404, 'Not Found'),
        (410, 'Gone'),
        (500, 'Internal Server Error'),
),

error_messages(error_category, error_message) as (
    values
        ('Unauthorised User',   'user % is not authorised'),
        ('Blocked User',        'user % is blocked'),
        ('Product Not Found',   'unable to get details for product % in %'),
        ('Product Unavailable', 'product % no longer exists in %'),
        ('Product Unavailable', 'product % is no longer available'),
        ('Resource Not Found',  'unexpected server error: % not found'),
        ('Thronglets',          'unexpected server error: thronglets have taken over')
)

select
    events.event_id,
    events.status_code,
    events.error_message,
    status_codes.status_text,
    coalesce(error_messages.error_category, 'Unknown Error') as error_category,
from testing.events
    left join status_codes
        using (status_code)
    left join error_messages
        on events.error_message like error_messages.error_message
-- order by event_id
;