10 ноября 2022

🗄️⚠️ Как намеренно расставленные ошибки помогают сделать SQL-код легко поддерживаемым

iOS-developer, ИТ-переводчица, пишу статьи и гайды.
В этой статье мы рассмотрим различные способы вызова ошибок, которые помогут упростить поддержку SQL-кода в будущем.
🗄️⚠️ Как намеренно расставленные ошибки помогают сделать SQL-код легко поддерживаемым
Данная статья является переводом. Ссылка на оригинал.

Существует множество передовых методов обеспечения обратной и прямой совместимости в коде приложения, но они не очень часто упоминаются в отношении SQL, который используется для получения крайне важной бизнес-информации для приложений и последующего принятия решений. Именно поэтому в данной статьей мы рассмотрим различные способы вызова ошибок для SQL-кода, которые помогут упростить поддержку проекта в будущем.

Простая платежная система

Скажем, у вас есть платежная система, в которой ваши пользователи могут взимать плату со своих клиентов за продукты. Таблица может выглядеть так:

        db=# CREATE TABLE payment (
    id      INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    method  TEXT NOT NULL
        CONSTRAINT payment_method_check CHECK (method IN ('credit_card', 'cash')),
    amount  INT NOT NULL
);
CREATE TABLE
    

Вы предоставляете своим пользователям два варианта оплаты: наличными или кредитной картой:

        db=# INSERT INTO payment (method, amount) VALUES
    ('cash', 10000),
    ('credit_card', 12000),
    ('credit_card', 5000);
INSERT 0 3

db=# SELECT * FROM payment;
 id │   method    │ amount
────┼─────────────┼────────
  1 │ cash        │  10000
  2 │ credit_card │  12000
  3 │ credit_card │   5000
(3 rows)
    

Расчет комиссии

Используйте следующий запрос для расчета комиссии за каждый платеж в зависимости от способа оплаты:

        -- calculate_commission.sql
SELECT
    COUNT(*) AS payments,
    SUM(
        CASE method
            WHEN 'cash' THEN 100
            WHEN 'credit_card' THEN 30 + amount * 0.02
        END
    ) AS commission
FROM
    payment;
    

При оплате наличными вы взимаете фиксированную комиссию в размере 1 доллара США (100 центов), а при оплате кредитной картой вы взимаете фиксированную плату в размере 30 центов плюс 2% от взимаемой суммы.

Это комиссия за первые 3 платежных процесса:

        db=# \i calculate_commission.sql
 payments  │ commission
───────────┼────────────
        3  │     500.00
(1 row)
    

Поздравляю! Вы только что заработали свои первые 5$.

Добавление нового способа оплаты

Время идет, и ваша платежная система становится настоящим хитом! Спрос на ваши услуги стремительно растет, и ваши клиенты просят больше способов оплаты. Вы хорошенько все обдумываете и решаете ввести новый способ оплаты — банковский перевод:

        db=# ALTER TABLE payment DROP CONSTRAINT payment_method_check;
ALTER TABLE

db=# ALTER TABLE payment ADD CONSTRAINT payment_method_check
    CHECK (method IN ('credit_card', 'cash', 'bank_transfer'));
ALTER TABLE
    

Прошло еще несколько месяцев, и новый способ оплаты оказался настоящим хитом:

        INSERT INTO payment (method, amount) VALUES
    ('bank_transfer', 9000),
    ('bank_transfer', 15000),
    ('bank_transfer', 30000);
INSERT 0 3
    

Вы обрабатываете больше платежей, чем могли себе представить, но что-то не так:

        db=# \i calculate_commission.sql
 payments │ commission
──────────┼────────────
        6 │     500.00
(1 row)
    

Вы обрабатываете все эти платежи, но ваш доход остается прежним, почему?

Больше полезных материалов вы найдете на нашем телеграм-канале «Библиотека программиста»

SQL-код, который не потеряет актуальности

При добавлении нового способа оплаты вы не редактировали запрос, рассчитывающий комиссию. Запрос никогда не завершался ошибкой, не возникало никаких исключений или предупреждений, и вы полностью забыли об этом!

Этот тип сценария довольно распространен. SQL обычно не проверяется статически, поэтому, если у вас нет автоматических тестов для этого конкретного запроса, он может легко остаться незамеченным!

Совершаем ошибки намеренно

Ошибки считаются неудачей, но на самом деле они довольно полезны. Если запрос выдает ошибку при столкновении с неизвестным способом оплаты, вы можете обнаружить эту ошибку и немедленно исправить.

Напомним запрос для расчета комиссии:

        SELECT
    COUNT(*) AS payments,
    SUM(
        CASE method
            WHEN 'cash' THEN 100
            WHEN 'credit_card' THEN 30 + amount * 0.02
        END
    ) AS commission
FROM
    payment;
    

В запросе используется CASE-выражение для расчета комиссии для каждого способа оплаты. Выражение не определяет, что должно произойти, если метод не соответствует ни одному из WHEN-выражений, поэтому выражение неявно оценивается как NULL, а агрегатная функция игнорирует его.

Что, если вместо неявной оценки NULL мы получим ошибку?

Assert never в SQL

Чтобы вызвать ошибку в PostgreSQL, мы можем использовать простую функцию:

        CREATE OR REPLACE FUNCTION assert_never(v anyelement)
RETURNS anyelement
LANGUAGE plpgsql AS
$$
BEGIN
    RAISE EXCEPTION 'Unhandled value "%"', v;
END;
$$;
    

Функция принимает аргумент любого типа и вызывает исключение:

        db=# SELECT assert_never(1);
ERROR:  Unhandled value "1"
CONTEXT:  PL/pgSQL function assert_never(anyelement) line 3 at RAISE
    

Чтобы получить ошибку, когда запрос встречает неизвестное значение и срабатывает ветка ELSE, мы должны совершить вызов следующим способом:

        db=# SELECT
    COUNT(*) AS payments,
    SUM(
        CASE method
            WHEN 'cash' THEN 100
            WHEN 'credit_card' THEN 30 + amount * 0.02
            ELSE assert_never(method)::int
        END
    ) AS commission
FROM
    payment;

ERROR:  Unhandled value "bank_transfer"
CONTEXT:  PL/pgSQL function assert_never(anyelement) line 3 at RAISE
    

Это круто! Запрос обнаружил необработанный способ оплаты bank_transfer и завершился ошибкой. К ошибке также относятся значения, которые мы забыли учесть, что делает его особенно полезным для отладки.

Ошибка заставляет разработчика предпринять следующие действия при обработке исключения:

Явно исключить необработанное значение:

        SELECT
    COUNT(*) AS payments,
    SUM(
        CASE method
            WHEN 'cash' THEN 100
            WHEN 'credit_card' THEN 30 + amount * 0.02
            ELSE assert_never(method)::int
        END
    ) AS commission
FROM
    payment
WHERE
    method IN ('cash', 'credit_card');

 payments │ commission
──────────┼────────────
        3 │     500.00
    

Разработчик может решить явно исключить это значение. Может быть, оно не имеет значения или обрабатывается другим запросом. В любом случае значение теперь исключается явно, а не просто игнорируется.

Обработать новое значение:

        SELECT
    COUNT(*) AS payments,
    SUM(
        CASE method
            WHEN 'cash' THEN 100
            WHEN 'credit_card' THEN 30 + amount * 0.02
            WHEN 'bank_transfer' THEN 50
            ELSE assert_never(method)::int
        END
    ) AS commission
FROM
    payment;

 payments │ commission
──────────┼────────────
        6 │     650.00
    

Разработчик заметил ошибку и добавил в запрос комиссию за необработанный способ оплаты. Ошибка предотвращена!

В обоих случаях результаты теперь точны, а запрос безопаснее.

Assert never

Исчерпывающая проверка является распространенным паттерном во многих языках, чтобы убедиться, что обработаны все возможные значения. Я уже писал об исчерпывающей проверке в Python в прошлом, где показал, как реализовать аналогичную функцию с именем assert_never в Python.

К счастью, после публикации статьи функция assert_never была встроена в модуль ввода в Python 3.11, и ее можно использовать для выполнения исчерпывающей проверки:

        from typing import assert_never, Literal

def calculate_commission(
    method: Literal['cash', 'credit_card', 'bank_transfer'],
    amount: int,
) -> float:
    if method == 'cash':
        return 100
    elif method == 'credit_card':
        return 30 + 0.02 * amount
    else:
        assert_never(method)
    

Запуская код в Mypy, программа проверки опциональных статических типов для Python выдаст следующую ошибку:

        error: Argument 1 to "assert_never" has incompatible type "Literal['bank_transfer']";
expected "NoReturn"
    

Как и функция assert_never в SQL, ошибка предупреждает о необработанном значении bank_transfer. В отличие от функции в SQL, это произойдет не во время выполнения, а во время статического анализа.

Ошибка без функции

Если по какой-то причине вы не можете или не хотите использовать функции, есть другие способы вызвать ошибки в SQL.

Злоупотребление делением на ноль

Самый простой способ вызвать ошибки в любом языке программирования — это разделить некоторое число на ноль:

        SELECT
    COUNT(*) AS payments,
    SUM(
        CASE method
            WHEN 'cash' THEN 100
            WHEN 'credit_card' THEN 30 + amount * 0.02
            ELSE 1/0 -- intentional
        END
    ) AS commission
FROM
    payment;

ERROR:  division by zero
    

Вместо возврата NULL, когда метод не обрабатывается, мы делим 1 на 0, чтобы вызвать ошибку деления на ноль. Запрос не удался, как мы и хотели, но это не работает так, как мы могли бы ожидать.

Рассмотрим следующий сценарий, в котором обрабатываются все возможные способы оплаты:

        SELECT
    COUNT(*) AS payments,
    SUM(
        CASE method
            WHEN 'cash' THEN 100
            WHEN 'credit_card' THEN 30 + amount * 0.02
            WHEN 'bank_transfer' THEN 50
            ELSE 1/0 -- fail on purpose
        END
    ) AS commission
FROM
    payment;

ERROR:  division by zero
    

Этот запрос обрабатывал все возможные способы оплаты, но все равно возникли ошибки. Если посмотреть документацию про CASE, то становится понятно почему:

Существуют различные ситуации, в которых подвыражения выражения оцениваются в разное время, поэтому принцип «CASE оценивает только необходимые подвыражения» не является фундаментальным. Например, постоянное подвыражение 1/0 обычно приводит к ошибке деления на ноль во время планирования, даже если оно находится в той части CASE, которая никогда не будет введена во время выполнения.

В документации можно найти объяснение этому. Хотя CASE обычно оценивает только необходимые выражения, бывают случаи, когда выражения, использующие только константы, такие как 1/0, оцениваются во время планирования. Вот почему запрос завершился ошибкой, хотя базе данных не нужно было оценивать выражение в ELSE-условии.

Злоупотребление приведением типов

Еще один популярный вид ошибок — ошибки приведения. Попробуем вызвать ошибку, приведя значение к несовместимому типу:

        SELECT
    COUNT(*) AS payments,
    SUM(
        CASE method
            WHEN 'cash' THEN 100
            WHEN 'credit_card' THEN 30 + amount * 0.02
            ELSE method::int
        END
    ) AS commission
FROM
    payment;

ERROR:  invalid input syntax for type integer: "bank_transfer"

    

Мы пытались преобразовать текстовое значение в столбце method в целое число, но запрос не был выполнен. В качестве бонуса сообщение об ошибке предоставляет нам значение bank_transfer, что позволяет легко идентифицировать необработанное значение.

Давайте также проверим, что запрос не завершается ошибкой при обработке всех методов:

        SELECT
    COUNT(*) AS payments,
    SUM(
        CASE method
            WHEN 'cash' THEN 100
            WHEN 'credit_card' THEN 30 + amount * 0.02
            WHEN 'bank_transfer' THEN 50
            ELSE method::int
        END
    ) AS commission
FROM
    payment;

 payments │ commission
──────────┼────────────
        6 │     650.00
    

Когда запрос обрабатывает все возможные значения для method, он не завершается ошибкой!

Злоупотребление приведением типов для нетекстовых типов

Если вы будете использовать эту технику достаточно долго, вы обнаружите, что запуск ошибки приведения требует некоторого творчества. Инициировать ошибку приведения для текстовых значений, подобных приведенным выше, обычно проще — просто приведите к целому числу, и, скорее всего, это не удастся.

Однако, если у вас есть целочисленный тип, к какому типу вы бы его привели, чтобы вызвать ошибку? Вот что я придумал через некоторое время:

        SELECT
    CASE n
        WHEN 1 THEN 'one'
        WHEN 2 THEN 'two'
        ELSE ('Unhandled value ' || n)::int::text
    END as v
FROM (VALUES
    (1),
    (2),
    (3)
) AS t(n);

ERROR:  invalid input syntax for type integer: "Unhandled value 3"
    

Это не так элегантно, но со своей задачей справляется. Мы вызвали ошибку и получили полезное сообщение об ошибке, с которым мы можем работать в дальнейшем.

***

Материалы по теме

Источники

Комментарии

ВАКАНСИИ

Добавить вакансию
Разработчик C++
Москва, по итогам собеседования

ЛУЧШИЕ СТАТЬИ ПО ТЕМЕ