Главная SQL, Новое Безопасные скрипты по-новому
  • Безопасные скрипты по-новому


    imagesCACUV4IU

    SQL Server 2008 содержит несколько давно ожидаемых расширений синтаксиса, которые облегчают написание скриптов вообще и написание безопасных скриптов в частности. Под безопасными скриптами мы будем понимать перезапускаемые скрипты, которые проверяют состояние данных перед тем как вносить изменения, и изменяют только нуждающиеся в изменениях данные. Например, перед добавлением новой строки будет произведена проверка наличия строки в таблице, и если ее нет, она будет добавлена. В предыдущей статье была кратко затронута тема написания безопасных скриптов с использованием «классического» TSQL. В этой статье мы рассмотрим, что предлагает SQL Server 2008 для облегчения задачи написания таких скриптов.

    Кратко резюмируем, что нужно делать для написания безопасных скриптов.

    Безопасным мы будем называть скрипт, который производит изменения в БД особым способом, делая проверки перед выполнением операторов. Например, при добавлении одиночной строки, мы проверяем ее наличие с помощью функции exists. Если мы добавляем множество строк, мы создаем временную таблицу, помещаем в нее строки, затем вставляем их одним оператором INSERT, вставляя только ранее не вставленные строки.

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

    Об удалении дополнительно заботится не надо, операторы удаления безопасны.

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

    Безопасные скрипты можно легко комбинировать друг с другом и интегрировать их в автоматические или полу-автоматические build/deployment системы.

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

    В новой версии сервера появились языковые средства, упрощающие процесс. Теперь не нужен этап преобразования, скрипт сразу можно писать и тестировать в безопасном виде. Рассмотрим эти нововведения.

    Конструкторы VALUES

    Если мы добавляем множество строк, нам нужно создать, заполнить и удалить временную таблицу. Это несложно, но это лишний код. Лучшим решением было бы объявление таблицы и данных непосредственно в тексте запроса. Здесь есть прямая аналогия с инициализаторами в C#. Можно объявить экземпляр класса, создать его, присвоить значения его полям, затем использовать для чего-либо. Или можно создать экземпляр, одновременно определяя поля. Второй вариант записывается намного короче и нагляднее. Так же и с таблицами.

    Если раньше нам приходилось писать так:

    create table #tmp (ID int not null primary key, Name nvarchar (10))
    insert into #tmp values (1, 'aaa')
    insert into #tmp values (2, 'bbb')

    --use #tmp
    select * from #tmp

    drop table #tmp

    Или хитрить с помощью UNION ALL.

    insert into #tmp
    select 1 ID, 'aaa' Name
    union all
    select 2, 'bbb'

    То теперь мы можем обойтись одним коротким оператором.

    insert into #tmp
    values

       (1, 'aaa'),
       (2, 'bbb')

    Или можем обойтись вообще без предварительного объявления таблицы

    select *
    from
     (values
       (1, 'aaa'),
       (2, 'bbb')
     ) a (ID, Name)

    Так сформированную таблицу можно использовать в безопасном INSERT:

    INSERT INTO t
    SELECT a.*
    FROM
     (VALUES
       (1, 'aaa'),
       (2, 'bbb'),
       (3, 'ccc')
     ) a (ID, Name)
    left join BaseTable t
       on a.ID = t.ID
    WHERE
    t.ID is null

    Слияние данных

    При безопасном добавлении мы проверяем наличие данных, и добавляем только отсутствующие строки. Также часто бывает нужно обновить уже существующие строки. Первая задача решается INSERT с соответствующей проверкой, вторая — UPDATE. Итого нам надо два оператора для одной строки. Если строк несколько, то нам потребуется создать временную таблицу, а затем использовать ее в операторах INSERT и UPDATE. Уже очень давно программистское сообщество обсуждает идею оператора UPSERT, который совместит в себе две операции — добавит строку, если ее нет, и обновит если она есть. Кроме безопасных скриптов, подобный оператор был бы очень удобным во многих сценариях работы с БД. Например, при работе с денормализованными таблицами, когда нам надо вставлять\обновлять строки при каждой операции с базовыми таблицами, или для написания триггеров. Кроме UPSERT, в зависимости от сценария использования данных, были бы удобны и другие комбинации. Например UPDATE\DELETE — обновить существующие, удалить отсутсвующие. Также пригодилась бы и комбинация всех трех операторов — INSERT\UPDATE\DELETE. Наверное по этой причине создатели сервера реализовали универсальный оператор MERGE, который умеет делать все три операции по определенным нами правилам.

    В отличие от универсальных операторов INSERT\UPDATE\DELETE, оператор MERGE удобен для использования в определенных сценариях. В этих сценариях у нас есть две таблицы. Первая — это базовая таблица, которая содержит основное множество строк. Вторая (дополнительная или временная) — эта таблица с такой же структурой как и первая, содержащая новые\измененные строки. Задачей оператора является соединение (merge) данных из дополнительной и базовой таблиц. После выполнения оператора базовая таблица будет обновлена в соответствии с заданными правилами (например, новые строки добавить, старые обновить).

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

    1. Строки, которые есть как в базовой, так и в дополнительной таблице (MATCHED). К этой группе мы можем применить два действия — UPDATE и DELETE, т.е. обновить существующие строки, либо удалить их.
    2. Строки, которые есть только в дополнительной таблице, но отсутсвуют в базовой (NOT MATCHED BY TARGET). Для этой группы мы можем сделать только INSERT.
    3. Строки, которые есть только в базовой таблице, но отсутсвуют в дополнительной (NOT MATCHED BY SOURCE). Такие строки мы можем либо обновить, либо удалить.

    Эти три множества позволят нам задать правила обработки строк. Стоит отметить, что оператор реализован так, что для каждой строки во временной таблице должна существовать только одна строка в базовой. Оператор не умеет обновлять несколько строк в базовой таблице по одной строке во временной.

    Упрощенный синтаксис оператора таков:

    01 MERGE <target_table>
    02    USING <table_source>
    03    ON <merge_search_condition>
    04    [ WHEN MATCHED
    05        THEN UPDATE SET <set_clause> | DELETE ]
    06    [ WHEN NOT MATCHED [ BY TARGET ]
    07        THEN INSERT ]
    08    [ WHEN NOT MATCHED BY SOURCE
    09        THEN UPDATE SET <set_clause> | DELETE ]
    10 ;
    

    В первой строке мы задаем базовую таблицу, во второй строке — дополнительную. В ON задаем предикат для сравнения, например BaseTable.ID = TempTable.ID. Дальше следует нечто вроде оператора выбора, где мы указываем что делать с группой строк. Например, WHEN NOT MATCHED BY TARGET THEN INSERT означает — добавить строки, если их нет в базовой таблице. Обратите внимание, что точка с запятой после этого оператора обязательна.

    Рассмотрим простейший пример.

    MERGE BaseTable b
    USING #tmp t
    ON b.ID = t.ID
    WHEN MATCHED THEN
       UPDATE SET Name = t.Name
    WHEN NOT MATCHED BY TARGET THEN
       INSERT VALUES (ID, Name)
    ;

    Мы выполняем совмещение таблицы baseTable с #tmp, соединяя их по полю ID. Для общих строк будет обновлено поле Name, новые строки будут добавлены.

    Как реализован данный оператор? Надо заметить, что оператор MERGE — это так называемый синтаксический сахар, то есть синтаксическое средство для более удобной записи. Он не добавляет чего-либо нового, ту же самую функциональность можно реализовать с помощью «классических» языковых средств. Если в операторе MERGE содержится только часть WHEN MATCHED, то сервер будет выполнять обычное соединение (JOIN). Если есть WHEN NOT MATCHED BY TARGET или NOT MATCHED BY SOURCE, то будет выполнятся LEFT\RIGHT JOIN. Если есть оба NOT MATCHED, то будет выполнен FULL JOIN. Об этом нужно помнить и следить за размерностью результата. Если у нас есть только часть MATCHED, то результат соединения будет содержать только совпадающие строки из двух таблиц. Так как временная таблица обычно невелика, то и результат будет небольшим. Если в операторе есть NOT MATCHED BY TARGET, мы получим в результате соединения как минимум столько же строк, сколько их есть во временной таблице. Соединение выполняемое при NOT MATCHED BY SOURCE вернет все строки из базовой таблицы, а их может быть очень много. Ну а наличие обоих частей может вернуть еще больше строк. К счастью, мы можем дописывать условия к правилам, ограничивая количество строк в результате. Например, не надо обновлять не изменившиеся имена:

    MERGE BaseTable b
    USING #tmp t
     ON b.ID = t.ID
     WHEN MATCHED AND b.Name <> t.Name
       THEN
       UPDATE SET Name = t.Name
     WHEN NOT MATCHED BY TARGET THEN
       INSERT VALUES (ID, Name)
    ;

    Это добавление может показаться незначительным, но на самом деле оно важное. Если во временной таблице, скажем, миллион строк, из которых имена поменялись только в десяти записях, то предыдущая версия оператора будет обновлять миллион записей, а оператор с дополнительным условием — всего десять.

    Дополнительные возможности MERGE

    Рассмотренной функциональности MERGE хватит для написания безопасных скриптов, но у оператора есть еще козыри в рукаве. Во-первых, в операторе можно указывать части MATCHED и NOT MATCHED BY SOURCE дважды, по одному разу для каждого действия (обе части допускают UPDATE и DELETE). При этом первый записанный селектор должен содержать дополнительное условие. При выполнении второй селектор будет проверятся, только если не выполнился первый. Например, мы можем записать

    WHEN MATCHED AND b.IsActual=0 THEN DELETE
    WHEN MATCHED UPDATE SET ...

    В этом случае для каждой совпадающей строки будет проверено поле IsActual. Если оно равно нулю, то соответствующая запись в базовой таблице будет удалена. В обратном случае запись будет обновлена.

    Вторая дополнительная возможность — расширенный оператор OUTPUT. Так как оператор MERGE может производить все три базовые операции над данными, то в нем доступны обе таблицы, inserted и deleted, содержащие версии строк до и после изменения, также в OUTPUT можно использовать функцию $ACTION, которая возвращает INSERT, DELETE или UPDATE, в зависимости от выбранной операции над строкой. Пример:

    MERGE BaseTable b
    USING #tmp t
     ON b.ID = t.ID
     WHEN MATCHED AND b.Name <> t.Name
       THEN
       UPDATE SET Name = t.Name
     WHEN NOT MATCHED BY TARGET THEN
       INSERT VALUES (ID, Name)
    OUTPUT t.ID, $ACTION
    ;

    Выводом такого оператора может быть

    1    INSERT
    2    DELETE
    3    UPDATE
    

    Пример безопасного скрипта

    Предположим, что у нас есть такая базовая таблица:

    CREATE TABLE BaseTable (ID int not null primary key, Name nvarchar (10))
    INSERT INTO BaseTable values (1, 'aaa')
    INSERT INTO BaseTable values (2, 'bbb')

    Для начала рассмотрим скрипт старой школы, который заполняет временную таблицу, а затем обновляет базовую таблицу.

    CREATE TABLE #tmp (ID int not null , Name nvarchar (10))

    INSERT INTO #tmp values (1, 'AAAA')
    INSERT INTO #tmp values (3, 'ccc')

    INSERT INTO BaseTable
    SELECT t.ID, t.Name
    FROM #tmp t
     left join BaseTable b
       on t.ID = b.ID
    WHERE
      b.ID is null

    UPDATE b
    SET
      b.Name = t.Name
    FROM BaseTable b
     join #tmp t
       on b.ID = t.ID
    WHERE
      b.Name <> t.Name

    DROP TABLE #tmp

    Теперь перепишем его с использованием MERGE.

    MERGE BaseTable b
    USING (VALUES
       (1, 'AAAA'),
       (3, 'ccc')
       ) t (ID, Name)
    ON b.ID = t.ID
    WHEN MATCHED AND b.Name <> t.Name
       THEN
       UPDATE SET Name = t.Name
    WHEN NOT MATCHED BY TARGET THEN
       INSERT VALUES (ID, Name)
    ;

    Второй скрипт значительно короче, а делает то же самое.

    Вывод

    Конечно, в VALUES и MERGE нет ничего революционного, они упрощают написание скриптов в некоторых сценариях, добавляя в TSQL возможности, которые уже давно есть у конкурентов, не более. Использовать новый оператор MERGE нужно с оглядкой, хорошо представляя его работу и проверяя производительность. В некоторых сценариях он очень удобен, в других случаях старые методы имеют преимущество. Язык TSQL не может похвастать бурной историей изменений, и даже простейшие улучшения всегда вызывают теплый прием.

    Александр Синицын

    • Рубрика: SQL,Новое
    • Автор: Alsin
    • Дата: Вторник 21 Сен 2010

Комментарии

  1. Спасибо, очень много нового для себя узнал 🙂

  2. Спасибо за интересную статью.

    Сообщите пожалуйста, какие давно ожидаемые расширения синтаксиса содержит SQL Server 2008 R2 (именно R2) для написания безопасных скриптов по-новому?

    Спасибо.

  3. Я писал про SQL Server 2008, не конкретно про R2. Давно ожидали MERGE и конструкторы.

Опубликовать

Я не робот.