В отрасли разработки ПО немаловажную роль играет чистота кода. Чистый код легко читать и модифицировать любому разработчику. Это особенно важно после введения продукта в эксплуатацию, а именно, когда через длительное время потребовалось кардинальное изменение логики системы. В случае с плохим кодом у такой системы больше шансов быть полностью переписанной с нуля. В данной статье будут рассмотрены практики написания 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
Есть несколько ньюансов:
Таким образом, можно составить контракт БД на ранней стадии разработки и согласовать его с внешним кодом.
Благодаря процедурной природе СУБД, можно организовать 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.
https://falconspace.ru/blog/bazovye-voprosy-i-ponyatiya-v-sfere-sozdaniya-veb-proektov - Основы веб-разработки. Базовые понятия для владельца сайта
https://falconspace.ru/blog/bazovye-voprosy-i-ponyatiya-v-sfere-sozdaniya-veb-proektov - Основы веб-разработки. Базовые понятия для владельца сайта
https://falconspace.ru/blog/bazovye-voprosy-i-ponyatiya-v-sfere-sozdaniya-veb-proektov - Основы веб-разработки. Базовые понятия для владельца сайта
Вероятно, у вас есть сайт и вы подумываете, что было бы неплохо внедрить в него…
https://falconspace.ru/blog/bazovye-voprosy-i-ponyatiya-v-sfere-sozdaniya-veb-proektov - Основы веб-разработки. Базовые понятия для владельца сайта
https://falconspace.ru/blog/bazovye-voprosy-i-ponyatiya-v-sfere-sozdaniya-veb-proektov - Основы веб-разработки. Базовые понятия для владельца сайта