суббота, 8 января 2011 г.

Основы SQL - DDL и рефакторинг БД

Это вторая статья в серии статей “Основы SQL”. В ней я продолжу разговор о DDL. Также я покажу, каким образом может выглядеть рефакторинг БД (как с точки зрения нормализации, так и с точки зрения денормализации). Да, лично я считаю, что рефакторинг (можно назвать это модным словом continuous design) вполне применим к БД.

Конечно, в нашем распоряжении (пока) нет инструментов для рефакторинга SQL столь же мощных, как для C# или Java. Однако, в большинстве случаев, рефакторинг БД случается редко. Поэтому часто достаточно скриптов, написанных вручную или немного сгенерированных.
Но сначала немного о том, что делает мои трудовые будни несколько комфортнее, когда речь идет об обновлениях БД (повторюсь, речь, как и ранее, идет о MS SQL Server, хотя принципы могут быть применимы и к другим СУБД).

Повторно запускаемые скрипты
Можете считать меня перфекционистом, но я предпочитаю иметь дело со скриптами, которые можно запускать повторно. На мой взгляд то, что это усложняет скрипты, небольшая плата за удобство использования. В чем удобство, спросите вы? В двух простых вещах:
  1. Если скрипт большой и один из операторов завершится ошибкой, можно исправить ошибку и запустить весь скрипт повторно.
  2. Можно запускать несколько скриптов подряд, а если где-то посередине произойдет ошибка, то можно исправить ошибочный скрипт и продолжить выполнение с него.
За счет этих двух преимуществ в моей компании на протяжении более 5 лет используется механизм обновления структуры и данных подобными скриптами. Еще более удобным этот механизм делает интеграция с SVN, но это уже выходит за рамки обсуждаемой темы. Скажу только, что, начиная с первого релиза любого проекта/продукта все изменения БД у нас происходят через скрипты в SVN. Разумеется, никто не мешает использовать генерацию скриптов (с последующей корректировкой) из MS SQL Server Management Studio (или Enterprise Manager для совсем ранних версий).

Для повторно запускаемых скриптов есть следующие основные моменты, которые нужно учитывать:
  1. Необходимо проверять существование объектов (таблиц/столбцов и т.п.) перед тем, как их изменить (добавление/удаление здесь и далее подразумевается автоматически).
  2. Если скрипт нетривиальный и сбой где-то на полпути может привести к нежелательным последствиям, лучше запускать его в транзакции.
  3. Если добавляются обязательные столбцы в таблицу, содержащую данные, необходимо использовать значения по умолчанию (DEFAULT CONSTRAINT). (UPD: Если DEFAULT CONSTRAINT нежелателен, можно добавить необязательное поле, заполнить его значениями, а потом сделать обязательным с помощью ALTER COLUMN.)
  4. Если скрипт меняет не только структуру, но и данные, желательно использовать инструкцию SET XACT_ABORT_ON. В MS SQL Server эта инструкция приводит к автоматическому прекращению выполнения пакета и откату транзакции в случае ошибки при изменении данных.
  5. В реальных проектах не рекомендуется использовать оператор USE, потому что как у заказчика, так и у разработчика может быть несколько БД с разными названиями. В примерах он присутствует только для упрощения запуска.
  6. Если по причине сложных зависимостей не получается сделать изменение обычным скриптом, как правило это удается сделать с помощью динамического SQL (EXEC).
Что касается первого пункта, то обычно хватает простого набора стандартных шаблонов для проверки существования. Эти шаблоны, в моем случае, сделаны в предположении, что в БД у нас не бардак и, в том числе, не нужно иметь в виду то, что, скажем, название Customers может подразумевать не только таблицу, но и процедуру, функцию или представление. Вот этот набор (опишу кратко, потому что примеры будут далее):
  1. OBJECT_ID – проверка существования объекта (таблицы, процедуры, функции, ограничения и т.п.).
  2. COLUMNPROPERTY – проверка существования столбца.
  3. INDEXPROPERTY – проверка существования индекса.
В принципе, можно использовать IF EXISTS над системными таблицами/представлениями, как альтернативу – дело вкуса. В примере также будет использоваться DB_ID, однако для реальной разработки это не должно потребоваться (см. комментарий 5 про USE выше).

Рефакторинг – добавление первичного ключа

Это уже обсуждалось в предыдущей статье – редкие таблицы обходятся без первичного ключа. А уж такие таблицы как 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
Скрипт получился довольно простым, однако при этом блок, добавляющий первичный ключ, сработает даже при наличии данных в таблице Persons за счет IDENTITY (автоматически инкрементируемое поле). Рассуждения о пользе или вреде IDENTITY предоставим любителям рассуждать (привет, Joe Celko ;)

Что касается префикса “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)
Индекс создается с параметрами по умолчанию, а именно неуникальным, некластерным с сортировкой в порядке возрастания значений. Остальные опции можно посмотреть в документации по CREATE INDEX.

Рефакторинг – переименование столбца

Переименования в БД (по крайней мере у меня) встречаются намного реже, чем в коде. Быть может, причина в том, что в БД обычно все проще, чем в коде, а может быть, потому что изменения в БД сказываются и на приложении, и поэтому “дороже” – иногда просто проходишь мимо не самого лучшего названия, лишь бы оно не было слишком плохим.

Но сейчас не тот случай – можно что-нибудь выдумать. Допустим, мы уже начинаем понимать, в какую яму мы себя загоняем, нарушая первую нормальную форму, поэтому решили переименовать 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'
Здесь я сделал это очень простым скриптом, однако нужно учитывать, что столбец может использоваться, например, в хранимой процедуре. Поэтому, если вы не обладаете полными знаниями о текущей версии БД, лучше либо воспользоваться одним из инструментов рефакторинга БД (переименовывать обычно все умеют, в т.ч. Visual Studio) или запустить скрипт, который поможет вам найти некоторые варианты использования столбца или таблицы.

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

Анонс

В следующих сериях:
  • Манипулируем данными – DML или “а кому нужна БД без данных?”
  • Рефакторинг с учетом данных
  • Подробнее о запросах
  • Управляем доступом и транзакциями
UPD: Скрипт с примерами

7 комментариев:

  1. Весьма неплохая статья. Хорошо бы и продолжение увидеть.

    ОтветитьУдалить
  2. Продолжение будет, насчет этой недели не обещаю, но постараюсь :)

    ОтветитьУдалить
  3. >Если добавляются обязательные столбцы в таблицу, содержащую данные, необходимо использовать значения по умолчанию (DEFAULT CONSTRAINT).

    Не совсем согласен с этим утверждением. В некоторых случаях удобнее создать поле допускающее null значения. После этого его инициализировать, а уже потом изменить тип на not null.

    ОтветитьУдалить
  4. > В некоторых случаях удобнее создать поле допускающее null значения. После этого его инициализировать, а уже потом изменить тип на not null.

    Согласен - спасибо за комментарий. Тоже использую иногда. Чаще всего это удобно, когда DEFAULT CONSTRAINT по каким-то причинам принципиально не нужен. В принципе, многие вещи можно было бы осветить подробнее, но я боялся, что получится слишком большая статья... или книга :)

    ОтветитьУдалить
  5. Олег, совершенно согласен, что должен быть скрипт для обновления версии. Только вот подход с проверкой существования объектов в базе мне не очень нравится. В сложных случаях проверка может оказаться весьма хитрой.

    Мы используем (кое-где) немного другой подход. Храним в базе номер версии. В пакете скриптов для обновления сначала проверяем версию. Если ниже, то накатываем скрипт. В конце пакета, конечно, устанавливаем новую версию.

    Подход явно менее сложный, но требует определенной дисциплины - нужно не забыть накатить все обновления по порядку. Впрочем, и у тебя тоже это делать необходимо.

    ОтветитьУдалить
  6. > Мы используем (кое-где) немного другой подход. Храним в базе номер версии. В пакете скриптов для обновления сначала проверяем версию. Если ниже, то накатываем скрипт. В конце пакета, конечно, устанавливаем новую версию.

    У нас скрипты накатываются по очереди. Повторный запуск нужен только для того, чтобы в случае сбоя все максимально быстро и просто исправить и повторить с момента сбоя. И для учета ситуации, что разные разработчики могу править одну и ту же таблицу.

    То есть, когда тестируется релиз, накатываются скрипты, скажем 1 - 20, если сбой в 8-ом, то он исправляется, и выполняются уже 8-20.

    ОтветитьУдалить
  7. P.S. Очередь скриптов определяется названием файла (используется префикс вида дата+номер, например "2011011702 - ")

    ОтветитьУдалить