Дмитрий Артемов (dimaa@microsoft.com). Примеры кода и решения являются иллюстрациями (возможными, но не единственными способами программирования) Все. - презентация

1 Дмитрий Артемов

2 Примеры кода и решения являются иллюстрациями (возможными, но не единственными способами программирования) Все примеры предельно упрощены

3 Если говорить о надежном программировании БД, существует четыре ключевых элемента, которые позволяют избавиться от ошибок и сделают код менее уязвимым, защищая его от возможных проблем при непреднамеренном использовании Определитесь в предположениях. Тестируйте как можно больше вариантов использования. Используйте короткие, удобные для тестирования и полностью протестированные модули. При необходимости используйте код повторно, но будьте осторожны

4 Четкое определение предположений – одна из наиболее разрушительных ошибок при разработке ПО, в результате код используется не так как планировалось Необходимо: В явном виде определиться с предположениями Убедиться, что они остаются актуальными на всех этапах разработки Систематически избавляться от предположений, котрые не важны или более не актуальны

5 По мере разработки кода, необходимо включить все наше воображение для определения возможных сценариев незапланированного использования, в попытках «сломать» собственную разработку. Разработанные сценарии следует включить в программу тестирования. В процессе тестирования мы должны выяснить как различные изменения могут повлиять на на то, как исполняется код, и научиться как разрабатывать код такми образом, чтобы разрабатывать надежный код, который не «ломается» при изменении, например SET LANGUAGE или ROWCOUNT

6 Слишком часто мы считаем код готовым, после того как он пройдет несколько простых проверок Мы не посвящаем достаточно времени на поиск и идентификацию всех возможных, не выходящих за разумные рамки, способов использования нашего кода Когда наступает неизбежное и наш код используется не так, как мы предполагали, он «ломается»

7 CREATE TABLE dbo.Messages ( MessageID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Subject VARCHAR(30) NOT NULL, Body VARCHAR(100) NOT NULL ) ; CREATE PROCEDURE dbo.SelectMessagesBySubjectBeginning AS SELECT Subject, Body FROM dbo.Messages WHERE Subject + '% EXEC – OK EXEC = '[OT] – Не работает, [] – для LIKE определяют группы символов [a-d] или [abcd] EXEC = '50% – аналогично, % воспринимается как заменитель символа

8 ALTER PROCEDURE dbo.SelectMessagesBySubjectBeginning AS = '[', '[[]'), '%', '[%]') AS ; SELECT Subject, Body FROM dbo.Messages WHERE Subject + '%'

9 Часто встречающаяся ошибка заключается в разработке кода, который не учитывает возможных изменений настроек самого сервера/сессии В результате, код не способен адаптироваться к изменению свойств и настроек при работе на другом экземпляре SQL Server или в ситуации/ когда настройки меняются в рамках сессии

10 Необходимо фиксировать все обновления в таблице Мы создаем таблицу ObjectsChangeLog, куда будем записывать изменения и триггер, который будет записывать изменения в таблицу ObjectsChangeLog По умолчанию ROWCOUNT = 0 – возвращает все записи Если в какой-то момент код меняет значение умолчания для ROWCOUNT и не восстанавливает его, триггер пропустит часть записей Возможно не сразу, пока число записей < ROWCOUNT Тем труднее будет отловить эту проблему

11 Не стоит полагаться на судьбу Мы в явном виде устанавливаем нужное значение и SQL Server восстанвливает то, что было до нас, после завершения триггера ALTER TRIGGER dbo.Objects_UpdTrigger ON dbo.Objects FOR UPDATE AS BEGIN; -- the scope of this setting is the body of the trigger SET ROWCOUNT 0 -- Тело триггера -- На выходе установка ROWCOUNT будет восстановлена !! Эта настройка считается устаревшей (deprecated) для SQL Server 2008 !! Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server

12 Когда готовил презентацию обнаружился интересный побочный эффект от установки ROWCOUNT

13 UPDATE dbo.Employee SET ManagerID WHERE FirstName AND LastName Очевидно, разработчик предполагал, что в таблице нет сотрудников с одинаковыми именем и фамилией Если это не так (что вполне возможно), процедура ассоциирует их все с одним и тем же начальником

14 Обычно, обнаружив следование некоему предположеню мы можем обеспечить его выполнение или устранить. Обеспечить выполнение возможно созданием UNIQUE constraint на комбинации полей FirstName и LastName. Но это расходится с реальной жизнью Поэтому мы строим код. Чтобы исключить неверное предположение UPDATE dbo.Employee SET ManagerID WHERE EmployeeID Если EmployeeID – первичный ключ на таблице dbo.Employee, измененный код будет работать корректно

15 Есть целый ряд языков, которые гарантируют определенный порядок обработки логических выражений. SQL не из их числа Нельзя полагаться, что условия WHERE будут обработаны в определенном порядке. Код, который полагается на порядок обработки условий чреват проблемами SELECT … Orders WHERE ISDATE(VarcharColumn) = 1 AND CAST(VarcharColumn AS DATETIME) = ' '; Если CAST будет обработано перед проверкой ISDATE и строка – некорректная дата, мы получим ошибку INSERT orders values (7, ' ') SELECT… Msg 242, Level 16, State 3, Line 1 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. -- Это работает корректно SELECT … FROM Orders WHERE CASE WHEN ISDATE(VarcharColumn) = 1 THEN CAST(VarcharColumn AS DATETIME) END = ' ' ;

16 Нельзя рассчитывать что SET и SELECT всегда меняют значения переменных. Полагаясь на это предположение, мы создаем ненадежный код SELECT может оставить переменную без изменения, если набор пуст В такой же ситуации SET присваивает NULL int = 1

17 Если SET приводит к ошибке, значение переменной также не меняется Аналогично, если SELECT приводит к ошибке, значение переменной не меняется SET NOCOUNT ON ; INT ; = -1 ; AS before the assignment] ; = ( SELECT 1 UNION ALL SELECT 2 ) ; AS after the assignment] ; SET NOCOUNT ON ; INT ; = -1 ; AS before the assignment] ; = 1 WHERE ( SELECT 1 AS n UNION ALL SELECT 2 ) = 1 ; AS after the assignment] ; Т.к. переменная не меняется, мы легко можем попасть в бесконечный цикл Для ситуации с пустым множеством 1.Используйте явное переназначение 2.Используйте SET вместо SELECT

18 = CustomerId FROM dbo.Customers WHERE Position = Manager Если у нас есть два человека на одинаковых должностях, результат выполнения непредсказуем Мы не знаем и не можем знать, какой CustomerId попадет в переменную (порядок выборки не определен) Но запрос будет работать! SET распознает проблему = ( SELECT CustomerId FROM dbo.Customers WHERE PhoneNumber = '(123) ) Msg 512, Level 16, State 1, Line 16Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=,, >= or when the subquery is used as an expression. Разработчик должен перехватить ошибку

19 CREATE PROCEDURE VARCHAR(50) = VARCHAR(50) = NULL ALTER PROCEDURE VARCHAR(50) = VARCHAR(50) = VARCHAR(50) = NULL EXEC dbo.SelectCustomersByName 'Hansen', '(234) EXEC = = '(234) ' ; EXEC dbo.SelectCustomersByName 'Hansen', '(234) ' ; EXEC = = '(234) ' ;

20 Одна из наиболее частых причин «поломок» кода – изменение схемы таблицы Добавление новых полей Изменение определения существующих полей (например типа или размера)

21 CREATE TABLE dbo.Shipments ( Barcode VARCHAR(30) NOT NULL PRIMARY KEY,… CREATE TABLE dbo.ShipmentItems ( ShipmentBarcode VARCHAR(30) NOT NULL, Description VARCHAR(100) NULL ) SELECT Barcode, ( SELECT COUNT(*) FROM dbo.ShipmentItems WHERE ShipmentBarcode = Barcode ) AS NumItems FROM dbo.Shipments ; Barcode NumItems ALTER TABLE dbo.ShipmentItems ADD Barcode VARCHAR(30) NULL SELECT Barcode, ( SELECT COUNT(*) FROM dbo.ShipmentItems WHERE ShipmentBarcode = Barcode ) AS NumItemsFROM dbo.Shipments ; Barcode NumItems SELECT s.Barcode, ( SELECT COUNT(*) FROM dbo.ShipmentItems AS i WHERE i.ShipmentBarcode = s.Barcode ) AS NumItemsFROM dbo.Shipments AS s ; Barcode NumItems

22 CREATE TABLE dbo.Codes (Code VARCHAR(5) NOT NULL, Description VARCHAR(40) NOT NULL, CONSTRAINT PK_Codes PRIMARY KEY ( Code ) ) CREATE PROCEDURE VARCHAR(5) AS SELECT Code, Description FROM dbo.Codes WHERE Code EXEC = '12345' ; Code Description Description for ALTER TABLE dbo.Codes ALTER COLUMN Code VARCHAR(10) NOT NULL EXEC = ' ' ; Code Description Description for Как быть в этой ситуации? Аккуратная документация и тестирование на граничные значения

23 Триггер, который выполняет проверку, при включенном режиме SNAPSHOT ISOLATION ALTER DATABASE xxx SET READ_COMMITTED_SNAPSHOT ON; Может считать данные из version store и принять логически неверную запись

24 Появилась в версии SQL Server 2005 Позволяет развести читателей и писателей За счет размещения последней зафиксированной записи в хранилище версий (version store ) в TempDb) Плюсы Существенно снижает ожидания при интенсивной конкуренции (читаем из одного места, пишем в другое) Может существенно снизить число deadlock в ситуации когда блокировка возникает между выборкой и модификацией (те, что обычно лечатся средствами хинта UPDLOCK) В режиме READ_COMMITTED_SNAPSHOT ON не требует изменения кода (все транзакции, исполняющиеся в режиме READ COMMITTED попадают под раздачу) Минусы Может существенно увеличить нагрузку на TempDb Увеличивает размер самой БД за счет добавления 14 байт к каждой записи Длинные транзакции могут создавать длинные цепочки версий. Опять нагрузка на TempDb Обновления могут замедлится из-за необходимости вести версии Код может работать с ошибками (то, о чем мы говорим сейчас)

25 IF EXISTS (SELECT * FROM inserted AS i INNER JOIN dbo.Developers AS d ON i.AssignedToDeveloperID = d.DeveloperID WHERE d.Status = 'Vacation AND i.Status = 'Active') BEGIN; RAISERROR ('Cannot change ', 16, 1); ROLLBACK; BEGIN TRANSACTION UPDATE Developers --COMMIT BEGIN TRANSACTION UPDATE TICKETS COMMIT Триггер ждет Триггер выдает ошибку Откатывает обновление TICKETS COMMIT

26 ALTER DATABASE xxx SET READ_COMMITTED_SNAPSHOT ON; BEGIN TRANSACTION UPDATE Developers --COMMIT BEGIN TRANSACTION UPDATE TICKETS COMMIT Мы получаем логически неверные данные COMMIT Tempdb Мы получаем логически неверные данные COMMIT Триггер идет в TempDb, берет данные из Version Store и успешно завершает работу Пока транзакция ждет, пытаемся обновить Tickets READCOMMITTEDLOCK

27 Появление DMV существенно снизило пользу от применения SQL Server Profiler Однако, пользовательские события могут вдохнуть в него новую жизнь – int, значение в пределах Соответствует 10 классам. 82 -UserConfigurable:0, nvarchar(128) для заполнения поля - varbinary(8000) для заполнения поля BinaryData

28 Очень полезна в сложной ситуации, когда код падает не всегда Сбор всех команд за длительный срок создает слишком много данных Для использования пользовательских событий необходимо иметь права ALTER TRACE Право редкое, поэтому для его применения можно создать оболочку для вызова sp_trace_generateevent Оболочке дадим права на базе сертификата

29 USE master GO CREATE CERTIFICATE ALTER_TRACE_CERT ENCRYPTION BY PASSWORD='- UsE_a!sTr0Ng_PwD-or-3~' WITH SUBJECT='Certificate for ALTER TRACE', START_DATE=' ', EXPIRY_DATE=' ' GO CREATE LOGIN ALTER_TRACE_LOGIN FROM CERTIFICATE ALTER_TRACE_CERT GO GRANT ALTER TRACE TO ALTER_TRACE_LOGIN GO GRANT AUTHENTICATE SERVER TO ALTER_TRACE_LOGIN GO CREATE PROCEDURE varbinary(8000) AS BEGIN EXEC = = END GO ADD SIGNATURE TO ThrowEvent BY CERTIFICATE ALTER_TRACE_CERT WITH PASSWORD='-UsE_a!sTr0Ng_PwD-or-3~' GO GRANT EXEC ON ThrowEvent TO [public] GO IF = 0 EXEC ThrowEvent 82, N'No data inserted into MyTable', 0x Отладочный код в основной процедуре IF = 0 EXEC ThrowEvent 82, N'No data inserted into MyTable', 0x0000

30 Все, что я только что рассказал, и более того, можно найти в этой книге Бесплатно главы 1-8 на programming/

31

32 Более 300 официальных курсов Microsoft доступно в России. Официальные курсы можно прослушать только в авторизованных учебных центрах Microsoft под руководством опытного сертифицированного инструктора Microsoft интенсивное обучение с акцентом на практику более 80-и учебных центров более чем в 20-и городах России (+ дистанционные и выездные курсы) Сертификат Microsoft - показатель квалификации ИТ-специалиста для работодателя. Microsoft предлагает гибкую систему сертификаций. Все курсы, учебные центры и центры тестирования: % Доказательство 75 сертифицированных специалистов считают, что сертификация помогла им получить работу или повышение 57 % Доказательство 119 рекрутеров считают сертификацию сотрудников одним из критериев для повышения в должности

33 Сертификационный пакет со вторым шансом Пакеты экзаменационных ваучеров со скидкой от 15 до 20% и бесплатной пересдачей («вторым шансом»). Все экзамены сдаются одним человеком. Сэкономьте 15% на сертификации вашей ИТ-команды Пакет из 10-и экзаменационных ваучеров со скидкой 15% для сотрудников ИТ-отдела. «Второй шанс» включен. Ваучеры можно произвольно распределять между сотрудниками. Microsoft Certified Career Conference Первая 24-часовая глобальная виртуальная конференция с 18 ноября с (моск. время) по 19 ноября 2010 г. Сессии по технологиям и построению карьеры Скидка 50% для сертифицированных специалистов Microsoft и студентов Бесплатная подписка на TechNet для слушателей официальных курсов Некоторые курсы по SharePoint, Windows 7; Windows Server 2008; SQL Server 2008 Детали: С 22 ноября 2010 г. – подписка TechNet бесплатно для слушателей курсов. Количество ограничено!