В отрасли разработки ПО немаловажную роль играет чистота кода. Чистый код легко читать и модифицировать любому разработчику. Это особенно важно после введения продукта в эксплуатацию, а именно, когда через длительное время потребовалось кардинальное изменение логики системы. В случае с плохим кодом у такой системы больше шансов быть полностью переписанной с нуля. В данной статье будут рассмотрены практики написания SQL кода, которые улучшат его читабельность, контракты и автотестирование.
Читабельность
Для повышения читабельности кода используется несколько нехитрых приемов: форматирование и разбиение сложной структуры на простые составляющие.
Форматирование
- Каждый логический блок (запрос) должен иметь отступ.
- Обязательные псевдонимы для колонок (об этом в конце) должны быть выровнены по одной вертикальной линии. Можно делать более одного отступа табуляцией.
- Необязательно ставить псевдоним в одну строку с полем. Для обеспечения п.2 можно вынести псевдоним в следующую строку и выровнять ее по остальным псевдонимам в данной выборке.
- Также, выравниваются сами поля выборки.
- У оператора JOIN операнд ON должен идти в следующей строке с отступом в 1 табуляцию. То есть считаем это подблоком.
- Вдобавок, операнды после знака равно должны быть выровнены по одной вертикальной линии, аналогично псевдонимам.
- У сложных операторов, таких как WHERE, каждый операнд выстраивается в новую строку с доп. отступом.
- Используйте скобки для выделения блоков. Скобки желательно выделять подобно if-блокам на C#.
- Встроенные ключевые слова на SQL всегда пишутся большими буквами.
- Подзапросы пишутся с отступом от родительского запроса, соблюдая правила предыдущих пунктов.
Пример кода со всеми вышеописанными пунктами приведен ниже.
-- псевдокод, может не скомпилироваться
-- 1.1 корневой блок, нулевой отступ
CREATE VIEW dbo.elements
AS
-- 1.2 блок с запросом - один отступ
SELECT -- здесь лучше оставлять место для TOP N
-- 1.3 блок с проекцией запроса - еще один отсуп
table.id AS id
-- однако запятая - без отступа - удобнее модифицировать
, table.text AS text
-- подблоки имеют отступ
, CASE WHEN table.id = 0
THEN 'foo'
ELSE 'bar'
END AS foobar
, category.text AS category
-- вернулись к блоку с запросом
FROM dbo.some_table AS table -- не брезгуйте осмысленными псевдонимами
JOIN dbo.some_categories AS categories
-- отступ!
ON table.categoryID = categories.id
-- скобки для блоков, выравнивание подблоков и подзапросов
WHERE (
table.text <> 'invalid text'
AND table.text <> 'other invalid text'
)
OR table.categoryID = (
SELECT c.id
FROM dbo.categories AS c
WHERE c.type <> 'invalidType'
)
GOКак видно, псевдонимы AS желательно выровнять для всех частей запроса.
Разбиение
С разбиением всё достаточно просто – при выборках не стоит забывать (ровно как и злоупотреблять) таким инструментом как Common Table Expression, а в хранимых процедурах можно делать подпроцедуры, курсоры, переменные, в общем всё что может дать процедурный язык и практика ведения чистого кода. Главное – помнить что в SQL это палка о двух концах. Например, CTE компилируются только один раз, а в выборке могут участвовать в самых разных ситуациях. Это сильно затрудняет работу оптимизатора и может кардинально ухудшить производительность.
Контракты
Принцип устройства процедур и представлений позволяет предоставить контракт для прикладного уровня на самой ранней стадии разработки – то есть когда реальной логики еще нет. Это позволяет единожды прописать правильное использование контракта на клиенте, и больше не возвращаться к этому вопросу, а пользоваться доступными объектами как есть. Поскольку данный процесс синхронизации контрактов может занимать длительное время, особенно если требуется изменить структуру БД – эта практика позволяет ускорить процесс разработки, попросту вычеркнув из нее этап постоянной синхронизации модели. Более того, предоставление доступа к БД подобным образом позитивно сказывается на её безопасности – у внешнего кода нет доступа к внутреннему состоянию базы, как для чтения так и для модификации.
То есть внешний код полностью абстрагируется от деталей реализации базы, полагаясь только на объявленный контракт.
Ниже продемонстрировано как добиться подобного эффекта на стадии начала разработки:
CREATE VIEW dbo.elements
AS
SELECT
1 AS id
, 'foobar' AS text
, CASE WHEN 1 <> 1
THEN NULL
ELSE 1
END AS nullableInt
FROM dbo.some_table AS table
WHERE 1 <> 1 -- TODO: реализовать логику запроса
GO
CREATE PROCEDURE dbo.insertElement
@text nvarchar(80)
-- системный контракт: пусть все хранимки на вставку возвращают айди
, @resultID int OUTPUT
-- системный контракт: пусть все хранимки возвращают текст ошибки
, @errorCode int OUTPUT
, @errorMessage nvarchar(255) OUTPUT
AS
-- TODO:
GOЕсть несколько ньюансов:
- В представлении важно тщательно следить за форматом данных. Если по задумке требуется Nullable<int> – нужно тщательнее проследить за тем чтоб импортер модели построил требуемую модель, основываясь на возвращаемых данных. Например, для поля nullableInt используется трюк который заставляет строитель модели EntityFramework добавлять обнуляемое поле. Есть более краткие способы, однако данный код точно работает.
- Если хранимая процедура возвращает таблицу – она может не попасть под авто-контракт.
- Входные параметры процедур можно подвергнуть универсализации. На стороне БД это обязательное наличие определенных, например, OUTPUT полей, а на прикладном уровне – абстрактный класс параметров от которого будут наследоваться более конкретные запросы.
Таким образом, можно составить контракт БД на ранней стадии разработки и согласовать его с внешним кодом.
Автотестирование
Благодаря процедурной природе СУБД, можно организовать SQL файл таким образом, чтоб сразу при компиляции объекта выполнялась как его замена, так и автоматическое тестирование.
Автоподмена делается с помощью DROP VIEW/PROCEDURE в каждом файле соответствующего объекта. Следует учесть что в данном примере предполагается что новая версия обратно совместимая с предыдущей. В противном случае требуется контроль версий объектов, но об этом в другой статье.
Автоматическое тестирование делается путем вызова самого объекта после его объявления. В случае с хранимыми процедурами, которые меняют состояние БД, желательно применять откатываемые транзакции. Остается вопрос – какие данные подставлять? На тестовом сервере это должны быть предопределенные идентификаторы – так проще. В противном случае можно писать бесконечно много доп. логики для теста.
Ниже показан пример для представления и хранимой процедуры:
-- авторегенерация объекта
IF OBJECT_ID('dbo.vw_elements', 'V') IS NOT NULL
DROP VIEW dbo.vw_elements;
GO
CREATE VIEW dbo.elements
AS
SELECT
1 AS id
FROM dbo.some_table AS table
GO
-- test
SELECT * FROM dbo.elements
-- авторегенерация объекта
IF OBJECT_ID('dbo.sp_psl_addServiceLocalizedText', 'P') IS NOT NULL
DROP PROCEDURE dbo.sp_psl_addServiceLocalizedText;
GO
CREATE PROCEDURE dbo.sp_psl_addServiceLocalizedText
@itemID int,
@langID int,
@itemType nvarchar(64),
@text nvarchar(2000),
@resultID int OUTPUT
AS
INSERT INTO dbo.psl_languageLookupTexts
(itemID, langID, itemType, text)
VALUES (@itemID, @langID, @itemType, @text);
SET @resultID = SCOPE_IDENTITY();
RETURN
GO
-- test
BEGIN TRANSACTION
DECLARE @testText nvarchar(2000);
DECLARE @testType nvarchar(64);
DECLARE @testResult int;
SET @testText = 'dbo.sp_psl_addServiceLocalizedText';
SET @testType = 'psl_text_localize_type';
DELETE FROM dbo.psl_languageLookupTexts WHERE text = @testText;
EXECUTE dbo.sp_psl_addServiceLocalizedText
@itemID = 1,
@langID = 1,
@itemType = @testType,
@text = @testText,
@resultID = @testResult OUTPUT
SELECT *
FROM dbo.psl_languageLookupTexts
WHERE id = @testResult;
ROLLBACK TRANSACTIONДанная практика позволит определить не только компиляцию запроса, но и его поведение во время выполнения – корректность выдачи, наличие ошибок и т.д. И вся итерация занимает пару секунд! Сравним с отладкой бизнес-логики на прикладном уровне C# – небольшое изменение в менеджере тянет за собой минутную перекомпиляцию всего сайта. И таких итераций может быть много.
Заключение
Итак, была рассмотрена польза от аккуратного ведения кода, приведены примеры более наглядного форматирования, которое выделяет как блочную структуру, так и группирует отдельные операнды по одной вертикальной линии. Далее были рассмотрены практики разработки SQL, которые позволяют снизить зависимость прикладного кода (Сайта, например), от БД и значительно ускорить отладку кода, по сравнению с отладкой на прикладном уровне.
Периодически мы проводим обучение и даем возможность стажировки на базе нашей платформы с управлением на SQL. Если вам это интересно, то пожалуйста посмотрите информацию об обучении/стажировке по SQL.