Многие жалуются, что 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.
- При создании правила, триггера и ограничения необходимо четко различать случаи. Правила лучше определять самостоятельно в отдельном проекте, но в любом случае вы обязательно будете придерживаться какого-то приоритета создания. Мол, если можно создать ограничение, то создайте ограничение. Если нет, вам следует создать правило. В крайнем случае, если даже правило не подходит в вашем случае, используйте триггер.
- Стандарты именования триггеров и правил. Если вы хотите создать какой-то триггер для таблицы, имя должно быть однозначно определено. Предлагаю начать думать об этом правиле именования с
"#{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.