Конечно, в нашем распоряжении (пока) нет инструментов для рефакторинга SQL столь же мощных, как для C# или Java. Однако, в большинстве случаев, рефакторинг БД случается редко. Поэтому часто достаточно скриптов, написанных вручную или немного сгенерированных.
Но сначала немного о том, что делает мои трудовые будни несколько комфортнее, когда речь идет об обновлениях БД (повторюсь, речь, как и ранее, идет о MS SQL Server, хотя принципы могут быть применимы и к другим СУБД).
Повторно запускаемые скрипты
Можете считать меня перфекционистом, но я предпочитаю иметь дело со скриптами, которые можно запускать повторно. На мой взгляд то, что это усложняет скрипты, небольшая плата за удобство использования. В чем удобство, спросите вы? В двух простых вещах:
- Если скрипт большой и один из операторов завершится ошибкой, можно исправить ошибку и запустить весь скрипт повторно.
- Можно запускать несколько скриптов подряд, а если где-то посередине произойдет ошибка, то можно исправить ошибочный скрипт и продолжить выполнение с него.
Для повторно запускаемых скриптов есть следующие основные моменты, которые нужно учитывать:
- Необходимо проверять существование объектов (таблиц/столбцов и т.п.) перед тем, как их изменить (добавление/удаление здесь и далее подразумевается автоматически).
- Если скрипт нетривиальный и сбой где-то на полпути может привести к нежелательным последствиям, лучше запускать его в транзакции.
- Если добавляются обязательные столбцы в таблицу, содержащую данные, необходимо использовать значения по умолчанию (DEFAULT CONSTRAINT). (UPD: Если DEFAULT CONSTRAINT нежелателен, можно добавить необязательное поле, заполнить его значениями, а потом сделать обязательным с помощью ALTER COLUMN.)
- Если скрипт меняет не только структуру, но и данные, желательно использовать инструкцию SET XACT_ABORT_ON. В MS SQL Server эта инструкция приводит к автоматическому прекращению выполнения пакета и откату транзакции в случае ошибки при изменении данных.
- В реальных проектах не рекомендуется использовать оператор USE, потому что как у заказчика, так и у разработчика может быть несколько БД с разными названиями. В примерах он присутствует только для упрощения запуска.
- Если по причине сложных зависимостей не получается сделать изменение обычным скриптом, как правило это удается сделать с помощью динамического SQL (EXEC).
- OBJECT_ID – проверка существования объекта (таблицы, процедуры, функции, ограничения и т.п.).
- COLUMNPROPERTY – проверка существования столбца.
- INDEXPROPERTY – проверка существования индекса.
Рефакторинг – добавление первичного ключа
Это уже обсуждалось в предыдущей статье – редкие таблицы обходятся без первичного ключа. А уж такие таблицы как Persons, представляющие сущности нашей системы, просто обязаны его иметь. Напомню, как выглядел скрипт, создающий таблицу Persons:Теперь нам нужно, во-первых, сделать его повторно запускаемым, а во-вторых – добавить первичный ключ:
IF DB_ID('FirstTest') IS NULL CREATE DATABASE FirstTest GO USE FirstTest /* создаем таблицу Persons (все что выше, в реальной разработке обычно не требуется)*/ IF OBJECT_ID('dbo.Persons') IS NULL CREATE TABLE dbo.Persons ( FirstName varchar(100) NOT NULL, MiddleName varchar(100) NULL, LastName varchar(100) NOT NULL, Phones varchar(MAX)) GO -- добавляем первичный ключ (столбец и constraint) IF COLUMNPROPERTY(OBJECT_ID('dbo.Persons'), 'PersonID', 'ColumnId') IS NULL ALTER TABLE dbo.Persons ADD PersonID INT NOT NULL IDENTITY, CONSTRAINT PK_Persons PRIMARY KEY(PersonID) GO
Что касается префикса “dbo” перед названием таблицы – это название схемы. Чаще всего используется именно “dbo” как схема по умолчанию и схему, в большинстве случаев, можно не указывать. С другой стороны, явное использование схемы позволяет избежать ошибок, когда используется несколько схем.
Заодно приведенный выше скрипт демонстрирует комментарии. Однострочные получаем при использовании двух минусов (--). Многострочные оформляются стандартным сочетанием – /**/. Только имейте в виду, что в ранних версиях MS SQL Server внутри многострочного комментария не должно быть разделителя пакета (GO).
Рефакторинг – добавление индекса
Можно поспорить с тем, насколько добавление индекса можно считать рефакторингом и не стоит ли вообще отдать управление индексами на сторону администратора заказчика (последнее, IMHO, имеет смысл, когда договора на поддержку нет, зато у заказчика есть хороший DBA и он реально будет этим заниматься).По большому счету, этот пункт я добавил только чтобы продемонстрировать все три упомянутых варианта проверки существования. Предположим, что нам захотелось ускорить поиск по фамилии – скажем, внезапно у нас в таблице появилась стотысячная строка и поиск начал слегка подтормаживать :) Делается это парой-тройкой строк:
IF INDEXPROPERTY(OBJECT_ID('dbo.Persons'), 'IX_Persons_LastName', 'IndexId') IS NULL CREATE INDEX IX_Persons_LastName ON dbo.Persons(LastName)
Рефакторинг – переименование столбца
Переименования в БД (по крайней мере у меня) встречаются намного реже, чем в коде. Быть может, причина в том, что в БД обычно все проще, чем в коде, а может быть, потому что изменения в БД сказываются и на приложении, и поэтому “дороже” – иногда просто проходишь мимо не самого лучшего названия, лишь бы оно не было слишком плохим.Но сейчас не тот случай – можно что-нибудь выдумать. Допустим, мы уже начинаем понимать, в какую яму мы себя загоняем, нарушая первую нормальную форму, поэтому решили переименовать Phones в CommaDelimitedPhones, чтобы название было более наглядным.
Приведенный ниже скрипт использует вызов системной хранимой процедуры sp_rename с помощью оператора EXEC/EXECUTE. Последний ее параметр отвечает за тип переименовываемого объекта, а первый, в случае переименования столбца, должен начинаться с названия таблицы.
IF COLUMNPROPERTY(OBJECT_ID('dbo.Persons'), 'CommaDelimitedPhones', 'ColumnId') IS NULL AND COLUMNPROPERTY(OBJECT_ID('dbo.Persons'), 'Phones', 'ColumnId') IS NOT NULL EXEC sp_rename @objname = 'dbo.Persons.Phones', @newname = 'CommaDelimitedPhones', @objtype = 'COLUMN'
Сейчас я забегаю немного вперед, потому что о DML я еще не рассказывал, однако подобные простые запросы наверняка знакомы большинству читателей. Пример скрипта для проверки (поскольку ищет просто по подстроке, могут быть ложные срабатывания):
SELECT o.name, OBJECT_NAME(o.parent_obj) AS parent_name FROM sysobjects o JOIN (SELECT DISTINCT id FROM syscomments WHERE [text] LIKE '%Phones%') c ON o.id = c.idORDER BY o.name
Рефакторинг – переход к 1NF
На всякий случай, напомню определение первой нормальной формы:Таблица находится в первой нормальной форме, если каждый её атрибут атомарен, то есть может содержать только одно значение. Таким образом, не существует 1NF таблицы, в полях которых могут храниться списки значений. Для приведения таблицы к 1NF обычно требуется разбить таблицу на несколько отдельных таблиц.
Использование первой нормальной формы оправдано в большинстве случаев. Редкие исключения – столбцы, по которым нет необходимости осуществлять поиск или использовать при соединении таблиц (JOIN), но, при этом, удобные для хранения составной информации (например, JSON с данными формы).
Есть еще разного рода “хаки”, например – использование LIKE для обработки иерархических таблиц. Да и с каждой новой версией MS SQL Server это правило становится все более размытым – XML-столбцы уже индексируются, начиная с 2008, не за горами, возможно и JSON :)
Поэтому, ключевое правило – отказ от первой нормальной формы должен быть мотивированным (а не объясняться невнимательностью или незнанием принципов проектирования БД).
А теперь, давайте сделаем нашу таблицу Persons еще немного лучше :)
-- 1NF - создаем таблицу Phones IF OBJECT_ID('dbo.Phones') IS NULL CREATE TABLE dbo.Phones ( PersonID int NOT NULL, Phone varchar(50) NOT NULL, CONSTRAINT PK_Phones PRIMARY KEY(PersonID, Phone), CONSTRAINT FK_Phones_Persons FOREIGN KEY(PersonID) REFERENCES dbo.Persons(PersonID) ) /* удаляем столбец (в реальном проекте обычно требуется предварительно перенести данные)*/ IF COLUMNPROPERTY(OBJECT_ID('dbo.Persons'), 'CommaDelimitedPhones', 'ColumnId') IS NOT NULL ALTER TABLE dbo.Persons DROP COLUMN CommaDelimitedPhones
Анонс
В следующих сериях:- Манипулируем данными – DML или “а кому нужна БД без данных?”
- Рефакторинг с учетом данных
- Подробнее о запросах
- Управляем доступом и транзакциями
Весьма неплохая статья. Хорошо бы и продолжение увидеть.
ОтветитьУдалитьПродолжение будет, насчет этой недели не обещаю, но постараюсь :)
ОтветитьУдалить>Если добавляются обязательные столбцы в таблицу, содержащую данные, необходимо использовать значения по умолчанию (DEFAULT CONSTRAINT).
ОтветитьУдалитьНе совсем согласен с этим утверждением. В некоторых случаях удобнее создать поле допускающее null значения. После этого его инициализировать, а уже потом изменить тип на not null.
> В некоторых случаях удобнее создать поле допускающее null значения. После этого его инициализировать, а уже потом изменить тип на not null.
ОтветитьУдалитьСогласен - спасибо за комментарий. Тоже использую иногда. Чаще всего это удобно, когда DEFAULT CONSTRAINT по каким-то причинам принципиально не нужен. В принципе, многие вещи можно было бы осветить подробнее, но я боялся, что получится слишком большая статья... или книга :)
Олег, совершенно согласен, что должен быть скрипт для обновления версии. Только вот подход с проверкой существования объектов в базе мне не очень нравится. В сложных случаях проверка может оказаться весьма хитрой.
ОтветитьУдалитьМы используем (кое-где) немного другой подход. Храним в базе номер версии. В пакете скриптов для обновления сначала проверяем версию. Если ниже, то накатываем скрипт. В конце пакета, конечно, устанавливаем новую версию.
Подход явно менее сложный, но требует определенной дисциплины - нужно не забыть накатить все обновления по порядку. Впрочем, и у тебя тоже это делать необходимо.
> Мы используем (кое-где) немного другой подход. Храним в базе номер версии. В пакете скриптов для обновления сначала проверяем версию. Если ниже, то накатываем скрипт. В конце пакета, конечно, устанавливаем новую версию.
ОтветитьУдалитьУ нас скрипты накатываются по очереди. Повторный запуск нужен только для того, чтобы в случае сбоя все максимально быстро и просто исправить и повторить с момента сбоя. И для учета ситуации, что разные разработчики могу править одну и ту же таблицу.
То есть, когда тестируется релиз, накатываются скрипты, скажем 1 - 20, если сбой в 8-ом, то он исправляется, и выполняются уже 8-20.
P.S. Очередь скриптов определяется названием файла (используется префикс вида дата+номер, например "2011011702 - ")
ОтветитьУдалить