Многие жалуются, что sql-код в той или иной форме очень быстро становится неподдерживаемым и, по сути, даже не стоит начинать писать какую-либо логику на sql. «Используйте sql только как таблицы, а всю логику реализуйте внутри кода», - говорят они не совсем без лукавства при этом.

Что ж, скажите мне, некоторые конкретные типы данных все же допустимы в sql, не так ли? Например, считается ли использование enum вместо строки постыдным или все еще приемлемо? Вроде бы дозволено даже в глазах ярых защитников чистоты кода. А как насчет использования чисел с плавающей запятой определенной точности (точности и масштаба) - возможно ли это? Или разумные значения по умолчанию, такие как created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, нам пока разрешено использовать их, не так ли?

Если ответы на все вопросы выше положительные, то, скорее всего, мы также можем добавить проверку данных, чтобы убедиться, что формат, скажем, строки соблюден. Речь идет, например, о проверке формата адреса электронной почты, отсутствии опасных символов в полях-идентификаторах адресной строки (slug или uid) или даже о проверке уникальности в определенном подмножестве записей (scope).

И тогда это короткий путь к хранимым процедурам. Допустим, у вас есть поле «имя», и вы хотите убедиться, что значение этого поля уникально без соответствия регистру. Это типичная задача, правда? Традиционные MVP-фреймворки говорят, что проверка должна производиться заранее, как SELECT 1 FROM table_name WHERE lower(name) = $1, и если запись присутствует, то процесс сохранения даже нельзя начинать. База данных как концепция говорит нам, что ее следует проверять во время записи. В некоторых случаях нам на помощь приходят ограничения для таблиц, в других мы можем применять правила и триггеры. В общем, с базой данных это было бы намного удобнее, только усложняется поддержка написанного кода.

В нашем проекте riter.co мы придумали несколько простых правил, придерживаясь которых вы можете беспокоиться о sql-коде не больше, чем о какой-то другой части вашего проекта, например javascript-code или css-rules.

  1. При создании правила, триггера и ограничения необходимо четко различать случаи. Правила лучше определять самостоятельно в отдельном проекте, но в любом случае вы обязательно будете придерживаться какого-то приоритета создания. Мол, если можно создать ограничение, то создайте ограничение. Если нет, вам следует создать правило. В крайнем случае, если даже правило не подходит в вашем случае, используйте триггер.
  2. Стандарты именования триггеров и правил. Если вы хотите создать какой-то триггер для таблицы, имя должно быть однозначно определено. Предлагаю начать думать об этом правиле именования с "#{table_name}_#{affected_colum_name}_#{verb}_on_#{action}". Вы получите что-то вроде "companies_subdomain_lowercase_on_insert". К тому же тогда с маской будет намного проще найти существующие правила:
SELECT routine_name AS name 
FROM information_schema.routines 
WHERE 
  routine_name LIKE 'companies_%_on_insert' AND 
  routine_type ='FUNCTION' AND 
  specific_schema='public';

3. ПОЦЕЛУЙ. Все процедуры в вашей базе данных должны быть простыми. Сложные вещи приводят к сложным ошибкам, а ошибки на уровне базы данных довольно пугающие. Давайте оставим их простыми. Лучше оставить сложную логику языкам программирования высокого уровня с их фреймворками. Например, мы можем преобразовать все в нижний регистр или сгенерировать уникальный идентификатор внутри базы данных, но когда речь идет о проверке пересечения временных интервалов, лучше, вероятно, сделать это в коде.

4. Тесты. Собственно, весь пост написан именно ради этого момента, и все, что сказано выше, больше похоже на преамбулу.

Как всегда, тесты должны быть представлены тремя способами: интеграция, единица и приемка.

Модульные тесты имеют гораздо меньший смысл, и в подавляющем большинстве они должны быть написаны на высокоуровневом коде. Рекомендуется писать модульные тесты для отдельных процедур независимо от текущего состояния базы данных. Например, у нас есть такая процедура, которая очень хорошо смотрится в контексте модульных тестов:

CREATE FUNCTION public.generate_slug(slug character varying, table_name character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$
      DECLARE
        key varchar;
        query text;
        found varchar;
      BEGIN
        query := 'SELECT slug FROM ' || quote_ident(table_name) || ' WHERE slug = ';
        key := slug;

        EXECUTE query || quote_literal(slug) INTO found;

          WHILE found IS NOT NULL LOOP
            key := encode(gen_random_bytes(5), 'base64');
            key := replace(key, '/', '-');
            key := replace(key, '+', '-');
            key := replace(key, '=', '');
            key := slug || '-' || key;

            EXECUTE query || quote_literal(key) INTO found;
          END LOOP;

        RETURN key;
      END;
      $$;

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

Интеграционные тесты должны быть написаны на языке среды, выбранной для проекта, и они должны проверять, что все действия ваших sql-триггеров отражаются в объектах и ​​структурах вашей программы. Например:

subject { create(:company, name: 'CamelCase') } 
its(:name) { is_expected.to eq('camelcase') }

Во-первых, он проверит правильность работы RETURNING name. Во-вторых, он проверит работу вашего фреймворка и его готовность сотрудничать с базой данных. Например, если ваш фреймворк готов контролировать правильность данных в некоторых полях, то такие тесты могут выявить конфликт и вовремя разрешить его.

Что касается приемочных испытаний, тут все становится интересно. В конце концов, действительно не имеет значения, что именно делает все буквы из поля имени строчными, будь то триггеры или правила. Намного важнее убедиться в результатах.

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

Вот сам тест:

it { expect(ApplicationRecord.connection.execute("SELECT #{f.function_name}()").to_a.first[f.function_name]).to eq('ok') }

Это ужасно просто, не правда ли? Конечно, перед запуском теста вам нужно создать эту функцию. Делаем это прямо перед тестом:

before do
  ApplicationRecord.connection.execute("CREATE OR REPLACE FUNCTION #{f.function_name}() RETURNS varchar LANGUAGE plpgsql AS $$ #{f.content} $$;")
end

И после теста сразу удаляем функцию:

after do
  ApplicationRecord.connection.execute("DROP FUNCTION IF EXISTS #{f.function_name}()")
rescue ActiveRecord::StatementInvalid
  nil
end

Теперь о содержании этой функции. Наиболее удобное решение - хранить содержимое в отдельных файлах с расширением .sql и читать его перед созданием sql-функции:

Dir[File.expand_path('**/*_spec.sql', __dir__)].map do |path|
      content = IO.read(path).strip
      basename = Pathname.new(path)
                         .relative_path_from(Rails.root.join('spec', 'sql'))
                         .to_s
                         .gsub('/', '__')
                         .gsub(/_spec.sql\z/, '')
      OpenStruct.new(path: path.to_s, content: content, empty?: content.empty?, basename: basename, function_name: "rspec_#{basename}")
    end

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

DO $$
DECLARE
  routine record;
BEGIN
  FOR routine IN
    SELECT routine_name AS name
    FROM information_schema.routines
    WHERE
      routine_name LIKE 'rspec_%' AND
      routine_type ='FUNCTION' AND
      specific_schema='public'
  LOOP
    EXECUTE 'DROP FUNCTION ' || quote_ident(routine.name);
  END LOOP;
END $$ LANGUAGE plpgsql;

А сам тест - дело техники. Вот, например, отдельно сохраненный company_name_spec.sql, который проверяет уникальность поля name в таблице companies.

DECLARE
  company_name companies.name%TYPE;
BEGIN

  INSERT INTO companies (name) VALUES ('qwe1') RETURNING name INTO company_name;
  ASSERT company_name = 'qwe1', 'companies.name is allowed to be a string';

  BEGIN
    INSERT INTO companies (name) VALUES ('qwe1') RETURNING name INTO company_name;
    ASSERT FALSE, 'companies.name should raise unique violation';
  EXCEPTION
    WHEN unique_violation THEN
      ASSERT TRUE;
  END;

  RETURN 'ok';
END;

Вместо выводов хочу сказать, что интеллектуальная база данных - это то самое место, которое необходимо тщательно покрыть тестами. И оставьте свои извинения в прошлом, теперь вы умеете их писать.

Первоначально опубликовано на dev.to.