При разработке приложений программистам частенько приходится писать SQL скрипты. С одной стороны, это хорошо. Программист специализируется на некоторой части функционала приложения, знает как устроена соответствующая часть базы данных, знает что ему нужно изменить. Он - именно тот человек, который может сделать изменения наиболее быстро. Даже если в компании есть специализированный отдел по базам данных, на то чтобы привлечь специалиста, объяснить что нужно и дождаться результата - уйдет значительно больше времени. С другой стороны, скрипты программистов часто оставляют желать лучшего. Для многих программистов SQL - дополнительный язык, они знают его хуже основного.
Также при современной разработке часто используются build -системы. Одним из шагов такой системы может быть применение скриптов к базе данных. Что означает, что одни и те же скрипты будут вызываться снова и снова. А значит их нельзя писать в произвольном стиле, скрипты должны быть пере-запускаемыми (re-runnable) или их еще называют безопасными (safe-style).
Работать с такими скриптами - одно удовольствие. Скрипт всегда отрабатывает успешно, независимо от состояния данных. Если что-то не добавлено - добавит. Если что-то не удалено - удалит. Старое - обновит. Красота. К сожалению, эта красота тоже требует жертв. И выражается это в том, что исходный небезопасный скрипт короче и проще чем аналогичный безопасный. Другими словами, мы усложняем простые вещи.
По назначению мы можем разделить скрипты на две большие группы. Первая группа - это скрипты, которые изменяют метаданные. Если вам надо добавить колонку к таблице, создать новую таблицу и так далее - это скрипты первой группы. Вторая группа - скрипты изменяющие данные в таблицах. В этой статье мы рассмотрим только вторую группу скриптов, так как именно их чаще всего пишут программисты. Со схемой данных должны работать DBA, но есть одно исключение - хранимые процедуры, которые достойны отдельной статьи. Также есть еще один вид скриптов, которые часто оформляются в виде SSIS пакетов - это регулярные процессы. Удаление неактуальных данных, экспорт в архивную базу, пересчет чего-то - все что периодически запускается и поддерживает базу в бодром состоянии. Эти процессы тоже пишут и поддерживают DBA, мы же сосредоточимся на программистских скриптах, т.е. на скриптах которые пишут разработчики при работе над очередным релизом.
Несколько оговорок. Первое, наши скрипты мы будем писать для Microsoft SQL Server. Второе, процесс разработки очень сильно различается от компании к компании. Я буду ориентироваться на некоторую абстрактную большую компанию, выбирая лучшее из своей практики. Что-то может быть лишним для вас или чего-то может недоставать. Смело присылайте мне свои соображения.
Прежде всего, несколько общих замечаний.
-
Заголовок скрипта.Каждый уважающий себя скрипт должен иметь заголовок. В заголовке должно быть написано кто написал скрипт, зачем и когда. Автора нужно указывать, чтобы потом его можно было найти, если со скриптом возникнут проблемы. Цель добавления скрипта (имя проекта, номер тикета и так далее) поможет найти человека, кто отвечает за этот проект, чтобы уточнить неясные моменты. Дату создания скрипта можно не указывать, но иногда она бывает полезна чтобы составить представление об актуальности скрипта. Итого, заголовок может выглядеть так:
- Начало и конец скрипта должны быть обозначены оператором PRINT.Билд-система будет запускать скрипты один за другим. Или хитрый DBA слепит из всех скриптов один мега-скрипт и будет запускать его. Когда что-то пойдет не так, скрипт выдаст ошибку. Часто есть возможность получить текстовый вывод от запуска интерпретатора. Если начало и конец каждого скрипта обозначены, найти скрипт-источник ошибки легко. Если нет - придется решать ребусы. Итого - помещаем в начало и конец скрипта что-то вроде:
- Печатайте сообщения, когда ваш скрипт достиг (или не достиг) каких-то важных результатов. Когда ваш скрипт упадет на production сервере, и вам пришлют лог его выполнения и ошибку, будет гораздо легче разобраться в том, что же пошло не так, если вы делали правильные PRINT.
- Три разные сущности реализуют концепцию уникальности строк в таблице - это первичный ключ, ограничение UNIQUE (иногда его называют альтернативным ключом) и уникальный индекс. Ключи физически реализуются с помощью индексов. Есть логическое различие, отличающее одно понятие от другого, но для наших целей эта разница не важна. Иногда для краткости я буду упоминать только первичный ключ, хотя на самом деле это может быть и UNIQUE и уникальный индекс.
- Внешние ключи и триггеры могут существенно разнообразить жизнь написателя скриптов. Я исхожу их того, что девелоперским скриптам не надо производить действия, вызывающие нарушение внешнего ключа, а триггеров либо нет, либо их работа абсолютно прозрачна.
- Ticket No : D0012345
- Author : Pupkin Ivan
- Created on : 19/08/2010
Отдельно надо указать, что НЕ надо писать в заголовках. Не надо писать копирайт. Это ценное знание никак не поможет DBA. Также не надо приводить полный или частичный текст лицензии. Это хороший способ увеличить скрипт на килобайт-другой, но смысла не имеет никакого.
print 'END D0012345 script'
Не надо писать строки вроде "Такого-то числа такого-то месяца, я, разработчик Василий, с трепетом в душе и верой в Бога, начинаю обработку пользовательских документов". DBA будет искать напечатанную строку в файлах скриптов, и если по строке можно найти ваш файл, это уже достаточно хорошо.
begin
<do something>
PRINT'do completed successfully'
end
else
PRINT'do has been already done'
Когда и сколько писать диагностических сообщений зависит от скрипта. Надо придерживаться взвешенной политики в этом вопросе. Не имеет большого смысла печатать миллион сообщений, если добавляется миллион элементов, например. Но что ваш скрипт закончил одну фазу и перешел к другой, стоит напечатать.
Три кита
Теперь о самих скриптах, которыми мы будем изменять данные. У нас есть три оператора для изменения данных. Это INSERT, UPDATE и DELETE. Рассмотрим их по очереди.
INSERT
В таблицу можно добавить сколько угодно строк, если они не нарушают первичного ключа, ограничений UNIQUE и уникальных индексов. Можно создать таблицу, которая не будет содержать ничего из вышеперечисленного. В такую таблицу можно вставлять сколько угодно строк и не получить ни одой ошибки. Такие таблицы используются при операциях с данными, но не для постоянного хранения данных. Как правило, таблица имеет как минимум первичный ключ. Если вы попытаетесь добавить в таблицу строку с таким значением ключа, который уже есть в таблице, произойдет ошибка. Однако, есть подводные камни. Например, если в качестве первичного ключа используется тип данных DATETIME или автоинкрементное поле (IDENTITY) и на таблице больше не объявлено ни уникальных индексов, ни ограничений UNIQUE. Очень сложно представить себе ситуацию, когда произойдет нарушение первичного ключа в таком случае. Штамп времени меняется каждую долю секунды, автоинкрементное поле послушно выдает увеличенное на шаг значение. Такие ключи называются суррогатными, и если в таблице есть только суррогатный первичный ключ, для наших целей это то же самое, что не иметь первичного ключа вообще.
Для того, чтобы оператор INSERT не вызывал ошибки при повторном выполнении, надо проверять первичный или альтернативный ключ. Делать это можно двумя способами. Первый хорошо подходит, когда вы вставляете единичные строки.
INSERT INTO dbo.Table1 VALUES (10,...)
Если в таблице нет такого значения, мы его добавляем. Почему возвращаем единицу из подзапроса, а не какую-нибудь колонку таблицы? Потому что нам все равно что возвращать, предикат EXISTS истинен если вложенный в него запрос вернет хоть что нибудь. Если бы я проверял наличие строки в таблице не по ключу, я записал бы так:
INSERT INTO dbo.Table1 VALUES (...)
Потому что для срабатывания предиката EXISTS достаточно и одного значения.
Если вам нужно добавить несколько значений, то удобно сохранить их во временную таблицу, а затем вставить одним оператором. Чтобы не загромождать скрипт, я не буду раскрывать список колонок в INSERT и SELECT.
create table #tmp(ID int, Name nvarchar(50))
insert into #tmp values (10, 'aaa')
insert into #tmp values (11, 'bbb')
INSERT INTO dbo.Table1
SELECT *
FROM #tmp t
left join dbo.Table1 t1
on t.ID = t1.ID
WHERE
t1.ID is null
drop table #tmp
Если оператор левого соединения вызывает у вас протест, то INSERT можно переписать так:
SELECT *
FROM #tmp
WHERE
ID not in (SELECT ID FROM dbo.Table1)
Несмотря на то, что запрос со вложенным селектом многими понимается проще, я бы рекомендовал использовать JOIN во всех случаях.
UPDATE
Казалось бы, оператор обновления изначально является безопасным. Одну и ту же строку можно обновлять множество раз, без всяких ошибок. Но и здесь есть нюансы. Безопасно обновление только неключевых полей по ключевым. Такой оператор безопасен:
SET Name ='abc'
WHERE ID = 10
Маленькое замечание - если скрипт запускается многократно, нет смысла обновлять снова и снова то, что уже было обновлено. Эту проблему можно решить, проверяя изменяемое поле в WHERE.
UPDATE dbo.Table1
SET Name ='abc'
WHERE ID = 10
and Name <>'abc'
Если вы обновляете ключевые поля, то может возникнуть ситуация, когда вы попытаетесь обновить ключ в такое значение, которое уже есть. В зависимости от смысла данных и задачи, такие ситуации разрешаются по-разному. Можно реализовать оператор UPDATE как DELETE и безопасный INSERT. Можно добавить к оператору UPDATE проверку, чтобы он обновлял только те строки, которые не нарушают ключа.
Например, рассмотрим такую таблицу, хранящую атрибуты клиента, оба поля в ней ключевые.
CustomerID AttribID ---------------------------- 1 100 1 101 2 100
Рассмотрим две типичные задачи. Первая задача - поменять атрибут 101 у клиента 1 на атрибут 100.
Запрос такого вида отлично работает на девелоперских и тестовых серверах (где нет первой строки) и падает на продакшен сервере (гда такая строка есть).
SET AttribID = 100
WHERE CustomerID = 1
and AttribID = 101
Почему именно так а не иначе? По закону Мерфи о полноте базы данных.
Усложним запрос, чтобы избежать неприятностей:
SET ca.AttribID = 100
FROM dbo.CustomerAttributes ca
left join dbo.CustomerAttributes ca1
on ca.CustomerID = ca1.CustomerID
and ca1.AttribID = 100
WHERE ca.CustomerID = 1
and ca.AttribID = 101
and ca1.CustomerID is null
Красным цветом я выделил добавленные части. Такой запрос ничего не обновит на продакшене, потому что в таблице CustomerAttributes уже есть такой ключ, но обновит данные на тестовых серверах. Тот же запрос можно записать в более интуитивно понятном виде:
UPDATE ca
SET ca.AttribID = 100
FROM dbo.CustomerAttributes ca
WHERE ca.CustomerID = 1
and ca.AttribID = 101
and not exists (select 1 from dbo.CustomerAttributes
where CustomerID = 1 and AttribID = 100)
Также можно записать этот UPDATE как DELETE и безопасный INSERT.
DELETE FROM dbo.CustomerAttributes
WHERE CustomerID = 1
and AttribID = 101
if not exists (
select 1
from dbo.CustomerAttributes
where CustomerID = 1 and AttribID = 100
)
INSERT INTO dbo.CustomerAttributes VALUES (1, 100)
Вторая задача - атрибут 101 больше не используется, вместо него надо использовать атрибут 100 для всех клиентов. Решаем через удаление 101 атрибута для тех клиентов, у которых есть оба атрибута, и обновлением 101 атрибута на 100 для тех, у кого есть только 101 атрибут. Это DELETE и UPDATE. Можно записать во временную таблицу всех клиентов, у которых есть 101 атрибут, удалить все строки со 101 атрибутом, безопасно вставить клиентов с 100-м атрибут из временной таблицы, DELETE + INSERT.
Третья задача - клиенты 1 и два объединились, теперь это один клиент. Надо объединить атрибуты клиентов 1 и 2. В этом случае нам надо добавить к клиенту 1 те атрибуты, которые есть у клиента 2, но нет у клиента 1, затем удалить все атрибуты клиента 2, INSERT + DELETE.
Итого, безопасный UPDATE должен обновлять данные только однажды, и правильно обрабатывать изменения в первичном ключе.
DELETE
Ломать, как известно, не строить. Поэтому DELETE является безопасным без дополнительных усилий и оговорок. Удаление может привести к ошибкам только при наличии внешних ключей или триггеров, если они вызовут ошибку во время исполнения.
Если вы пытаетесь удалить строку из родительской таблицы и на нее ссылаются дочерние таблицы, при этом внешний ключ объявлен специальным образом, произойдет ошибка. В этом случае надо вначале изменить все дочерние таблицы, затем менять родительскую.
Триггеры какому-либо обобщению не поддаются. Например, они могут проверять некоторые условия, и не позволять удаления если условия не выполняются. С триггерами проблем меньше всего. Потому что если они не разрешают какое-либо изменение, то и вам не стоит его делать. В крайнем случае триггеры можно временно отключить.
Транзакции
Транзакции как будто специально придумали для написания безопасных скриптов. Если что-то пошло не так - можно вернуть все в исходное состояние. Лучше не сделать некоторое сложное действие целиком, чем чинить его, когда оно прошло только до половины и упало с ошибкой. Есть два вредных заблуждения по поводу транзакций.
Первое - что они обязательно нужны. На самом деле это не так. Особенно это верно для безопасных скриптов. Мы их пишем специально таким образом, чтобы их можно было перезапускать, и при перезапуске они проверяют состояние данных, внося изменения по мере необходимости. Поэтому в большинстве девелоперских скриптов транзакции не нужны. У транзакций есть неочевидные минусы. Работающая транзакция накапливает блокировки, и освобождает их после финального commit. Чем больше транзакция - тем большая часть базы данных оказывается задействована. Во время работы транзакции другие транзакции вынуждены ждать. Это наблюдается в виде "тормозов". Если скрипты запускают на сервере под нагрузкой, такие "тормоза" стараются минимизировать. На самом деле транзакции необходимы только для рисковых операций. Если логика процесса такова, что при возникновении ошибки мы получим нарушение целостности (кто-то недополучит денег, или получит слишком много, появятся "висящие" строки, и так далее), то транзакция строго необходима.
Второе заблуждение - что оператора транзакции достаточно. На самом деле нет. Обработка ошибок на SQL Server заслуживает отдельного описания, но я упомяну только один нюанс. Не достаточно написать begin transaction в начале скрипта и commit transaction в конце. Потому что откат транзакции вызывают не все ошибки, как многие ожидают. То есть возможна ситуация, когда в контексте транзакции произойдет ошибка, но выполнение транзакции будет продолжено. Пример - ошибки преобразования типа. Это расстраивает, но решение простое. Достаточно установить переменную xact_abort в on. В этом случае практически все ошибки будут вызывать откат транзакции. Увы, все равно возможны ошибки, которые не вызовут откат транзакции. Но транзакция с установленной переменной уже достаточно надежна, а все редкие исключения можно отследить и исправить вручную. Итого, транзакция должна выглядеть так:
begin tran
...
commit tran
Заключение
Хоть было сказано и достаточно много слов, написание безопасных скриптов совсем не трудно. Больше всего хлопот причиняет оператор INSERT, потому что приходится дописывать проверку существования записей. Оператор UPDATE требует внимания в редких случаях. DELETE не требует внимания почти никогда. К месту и со вкусом примененная транзакция может сделать жизнь значительно легче. В результате мы получаем перезапускаемый скрипт, который легко можно комбинировать с другими скриптами и встраивать в build и deployment системы.