Существует множество передовых методов обеспечения обратной и прямой совместимости в коде приложения, но они не очень часто упоминаются в отношении SQL, который используется для получения крайне важной бизнес-информации для приложений и последующего принятия решений. Именно поэтому в данной статьей мы рассмотрим различные способы вызова ошибок для SQL-кода, которые помогут упростить поддержку проекта в будущем.
Простая платежная система
Скажем, у вас есть платежная система, в которой ваши пользователи могут взимать плату со своих клиентов за продукты. Таблица может выглядеть так:
Вы предоставляете своим пользователям два варианта оплаты: наличными или кредитной картой:
Расчет комиссии
Используйте следующий запрос для расчета комиссии за каждый платеж в зависимости от способа оплаты:
При оплате наличными вы взимаете фиксированную комиссию в размере 1 доллара США (100 центов), а при оплате кредитной картой вы взимаете фиксированную плату в размере 30 центов плюс 2% от взимаемой суммы.
Это комиссия за первые 3 платежных процесса:
Поздравляю! Вы только что заработали свои первые 5$.
Добавление нового способа оплаты
Время идет, и ваша платежная система становится настоящим хитом! Спрос на ваши услуги стремительно растет, и ваши клиенты просят больше способов оплаты. Вы хорошенько все обдумываете и решаете ввести новый способ оплаты — банковский перевод:
Прошло еще несколько месяцев, и новый способ оплаты оказался настоящим хитом:
Вы обрабатываете больше платежей, чем могли себе представить, но что-то не так:
Вы обрабатываете все эти платежи, но ваш доход остается прежним, почему?
SQL-код, который не потеряет актуальности
При добавлении нового способа оплаты вы не редактировали запрос, рассчитывающий комиссию. Запрос никогда не завершался ошибкой, не возникало никаких исключений или предупреждений, и вы полностью забыли об этом!
Этот тип сценария довольно распространен. SQL обычно не проверяется статически, поэтому, если у вас нет автоматических тестов для этого конкретного запроса, он может легко остаться незамеченным!
Совершаем ошибки намеренно
Ошибки считаются неудачей, но на самом деле они довольно полезны. Если запрос выдает ошибку при столкновении с неизвестным способом оплаты, вы можете обнаружить эту ошибку и немедленно исправить.
Напомним запрос для расчета комиссии:
В запросе используется CASE
-выражение для расчета комиссии для каждого способа оплаты. Выражение не определяет, что должно произойти, если метод не соответствует ни одному из WHEN
-выражений, поэтому выражение неявно оценивается как NULL
, а агрегатная функция игнорирует его.
Что, если вместо неявной оценки NULL
мы получим ошибку?
Assert never в SQL
Чтобы вызвать ошибку в PostgreSQL, мы можем использовать простую функцию:
Функция принимает аргумент любого типа и вызывает исключение:
Чтобы получить ошибку, когда запрос встречает неизвестное значение и срабатывает ветка ELSE
, мы должны совершить вызов следующим способом:
Это круто! Запрос обнаружил необработанный способ оплаты bank_transfer
и завершился ошибкой. К ошибке также относятся значения, которые мы забыли учесть, что делает его особенно полезным для отладки.
Ошибка заставляет разработчика предпринять следующие действия при обработке исключения:
Явно исключить необработанное значение:
Разработчик может решить явно исключить это значение. Может быть, оно не имеет значения или обрабатывается другим запросом. В любом случае значение теперь исключается явно, а не просто игнорируется.
Обработать новое значение:
Разработчик заметил ошибку и добавил в запрос комиссию за необработанный способ оплаты. Ошибка предотвращена!
В обоих случаях результаты теперь точны, а запрос безопаснее.
Assert never
Исчерпывающая проверка является распространенным паттерном во многих языках, чтобы убедиться, что обработаны все возможные значения. Я уже писал об исчерпывающей проверке в Python в прошлом, где показал, как реализовать аналогичную функцию с именем assert_never
в Python.
К счастью, после публикации статьи функция assert_never
была встроена в модуль ввода в Python 3.11, и ее можно использовать для выполнения исчерпывающей проверки:
Запуская код в Mypy, программа проверки опциональных статических типов для Python выдаст следующую ошибку:
Как и функция assert_never
в SQL, ошибка предупреждает о необработанном значении bank_transfer
. В отличие от функции в SQL, это произойдет не во время выполнения, а во время статического анализа.
Ошибка без функции
Если по какой-то причине вы не можете или не хотите использовать функции, есть другие способы вызвать ошибки в SQL.
Злоупотребление делением на ноль
Самый простой способ вызвать ошибки в любом языке программирования — это разделить некоторое число на ноль:
Вместо возврата NULL
, когда метод не обрабатывается, мы делим 1 на 0, чтобы вызвать ошибку деления на ноль. Запрос не удался, как мы и хотели, но это не работает так, как мы могли бы ожидать.
Рассмотрим следующий сценарий, в котором обрабатываются все возможные способы оплаты:
Этот запрос обрабатывал все возможные способы оплаты, но все равно возникли ошибки. Если посмотреть документацию про CASE
, то становится понятно почему:
Существуют различные ситуации, в которых подвыражения выражения оцениваются в разное время, поэтому принцип «CASE
оценивает только необходимые подвыражения» не является фундаментальным. Например, постоянное подвыражение 1/0 обычно приводит к ошибке деления на ноль во время планирования, даже если оно находится в той частиCASE
, которая никогда не будет введена во время выполнения.
В документации можно найти объяснение этому. Хотя CASE
обычно оценивает только необходимые выражения, бывают случаи, когда выражения, использующие только константы, такие как 1/0, оцениваются во время планирования. Вот почему запрос завершился ошибкой, хотя базе данных не нужно было оценивать выражение в ELSE
-условии.
Злоупотребление приведением типов
Еще один популярный вид ошибок — ошибки приведения. Попробуем вызвать ошибку, приведя значение к несовместимому типу:
Мы пытались преобразовать текстовое значение в столбце method в целое число, но запрос не был выполнен. В качестве бонуса сообщение об ошибке предоставляет нам значение bank_transfer
, что позволяет легко идентифицировать необработанное значение.
Давайте также проверим, что запрос не завершается ошибкой при обработке всех методов:
Когда запрос обрабатывает все возможные значения для method
, он не завершается ошибкой!
Злоупотребление приведением типов для нетекстовых типов
Если вы будете использовать эту технику достаточно долго, вы обнаружите, что запуск ошибки приведения требует некоторого творчества. Инициировать ошибку приведения для текстовых значений, подобных приведенным выше, обычно проще — просто приведите к целому числу, и, скорее всего, это не удастся.
Однако, если у вас есть целочисленный тип, к какому типу вы бы его привели, чтобы вызвать ошибку? Вот что я придумал через некоторое время:
Это не так элегантно, но со своей задачей справляется. Мы вызвали ошибку и получили полезное сообщение об ошибке, с которым мы можем работать в дальнейшем.
Комментарии