Выпуск 5

Здравствуйте, уважаемые коллеги. Поздравляю Вас с наступившим 2004 годом и Рождеством. Желаю Вам удачи и благополучия в новом году. Ну, а я продолжаю выпуск рассылки, посвященный Sybase ASA 9.

Содержание рассылки:

• Принципы хранения и работы batch-обьектов

• Представления

• Триггера

• Хранимые процедуры

• Пользовательские функции

Когда добавляется или изменяется скрипт любого представления, триггера, хранимой процедуры или пользовательской функции, ASA выполняет его сохранение и компиляцию в несколько этапов:

1. Проводит переформатирования скрипта, приводя его к своему стандарту форматирования и, записывает его в соответствующую системную таблицу (в поле SOURCE) , как исходный скрипт. Отформатированный скрипт будет использоваться как исходный в отладчике и профайлере.

2. Если опция БД PRESERVER_SOURCE_FORMAT выставлена в ON, то оригинальное оформление скрипта так же сохраняется (в поле <TypeObject>_DEFN), Если опцию отключить, то это приведет к уменьшению размера БД и скрипта для ее создания, однако сделает невозможным в дальнейшем просмотр и редактирования скрипта в оригинальном стиле оформления.

3. Полученный исходный скрипт компилируется в байт-код. При компиляции скрипт анализируется и оптимизируется по очень многим параметрам: убираются бесполезные условия (1=1, @Var = @Var, ISNULL(@VAR, @VAR) и т.д.), перестраиваются не оптимизированные условия (например( (Field1 = 1 AND Field2 = 2) OR (Field1 = 1 AND Field2 = 1) ) будет преобразовано в ( (Field1 = 1) AND (Field2 = 2 OR Field2 = 1) ) ), производится замена “*” для существующих таблиц и представлений в список всех полей и т.д. Для того, чтобы посмотреть превращение Вашего скрипта в код, по которому уже и будет работать оптимизатор запросов, я рекомендую ознакомиться с функцией REWRITE(), принимающую исходную версию скрипта запроса, который преобразовывается и возвращается его оптимизированная с точки зрения ASA версия запроса.

4. Для представлений проводится проверка существования используемых объектов БД. В случае использования неопределенных в БД таблиц, представлений, функций и хранимых процедур возбуждается ошибка. Имена полей на существование не проверяются, как я подозреваю, из за наличия поддержки в ASA глобальных переменных.

5. Полученный байт-код сохраняется в соответствующую системную таблицу. Планы выполнения запросов не сохраняются, и каждый раз строятся динамически при первом вызове объекта после запуска БД или при изменении статистики используемых таблиц. Обнуляются они при остановке БД на сервере. Это обусловлено архитектурой ASA (поддержка глобальных переменных, особенностями работы временных таблиц, статистики и оптимизатора запросов) и, с одной стороны, занимает дополнительное время на построение плана, зато не требует перекомпиляции объектов после изменении большого объема информации (например, после добавления миллиона записей в таблицу ASA автоматически перестроит статистику полей этой таблицы и вполне возможно, что планы запросов существующих хранимых процедур и представлений будут совершенно другими). С учетом поддержки кэширования планов запросов в ASA и динамической компиляции по необходимости, лично с моей точки зрения, это является выгодный чертой, избавляющей от многих хлопот и неудобств, возникающих в случае сохранения плана запроса объекта на момент его компиляции и сохранения в БД.

Стоит обратить внимание, в байт-коде возврата всех полей “*” будет заменен списком самих полей. Это приведет к тому, что при добавлении, переименовании или удалении полей таблицы, эти изменения не будут отображены для таких batch-объектов. Новые поля не будут возвращены, вместо полей с изменившимся именем или удаленных полей, будут возвращены алиасы со старыми именами полей, со значениями NULL. Если в таблице на момент вызова объекта будет меньшее кол-во полей, чем которое было при компиляции объекта, то будет возбуждена ошибка о несоответствии количества полей в запросе и таблице. Учитывая все вышесказанное, я бы порекомендовал, как можно реже пользоваться конструкцией “SELECT *” в целях избежания большого количества проблем, способных возникнуть при изменении структуры таблиц БД.

Как дополнение хотелось бы обратить внимание на очень странную и наверное одну из самых неприятных особенностей ASA – имея прекрасный отладчик и профайлер, при возникновении ошибки в скрипте вызванной хранимой процедуры ASA сообщает об ошибке, дает ее описание, номер и скромно уточняет, что она произошла в “Line 1, Column 1”, что не очень информативно с учетом того, что ошибка могла произойти где угодно – от вызываемой процедуры, до использованных в ней функциях или неявно вызванных триггерах таблиц. Победить мне это не удалось, так что такое поведение можно отнести к специальной системной ошибке ASA “Будет реализовано в будущем”. Однако возможность узнать, где произошла ошибка, все-таки есть: через профайлер, через отладчик и c помощью функции TRACEBACK().

1. Через профайлер все понятно, но долго. Запускаем профайлер, вызываем ошибочную процедуру, как только она вышла с ошибкой, достаточно зайти в профайлер и посмотреть из него на скрипт ошибочной процедуры. Ошибка будет там, где не будет проставлено время выполнения строки скрипта. Если с этой строки идет вызов ХП, функций или это оператор изменения записей таблицы и срабатывает триггер, то значит через профайлер необходимо посмотреть на код этих объектов по такому же алгоритму – ищем, пока не найдем обрыв работы профайлера. Как говориться – дешево и сердито.

2. Через отладчик все еще понятней и еще дольше. Идем пошагово по коду и ждем, пока произойдет ошибка. Я бы не рекомендовал таким образом искать ошибки.

3. Определение ошибки с помощью функции TRACEBACK()– самый простой способ, странно, что в ISQL не было встроено поддержка и интеграция с этой функции, так как она изначальна была разработана для отладки ошибок в скриптах. Чтобы ей воспользоваться, запускаем ошибочную процедуру из ISQL. После того как будет выдана ошибка, Вы можете написать и выполнить следующий скрипт:

SELECT TRACEBACK(*);

Вам будет возвращена строка, в которой в обратном порядке, по вложенности вызовов, будет перечислена трассировка строк кода, которые привели к ошибке. К сожалению ISQL не корректно показывает текстовые поля, вырезая с них символы перевода строк, что усложняет читабельность полученного результата. Чтобы обойти эту проблему, можно написать следующий скрипт:

UNLOAD   SELECT TRACEBACK(*)   TO 'asa_error.txt' ESCAPES OFF QUOTES OFF; BEGIN   DECLARE LOCAL TEMPORARY TABLE #ErrorStack(     Line VARCHAR(500) NULL   ) NOT TRANSACTIONAL;   LOAD TABLE #ErrorStack   FROM 'asa_error.txt' ESCAPES OFF QUOTES OFF;   SELECT Line   FROM #ErrorStack; END

Этот скрипт можно записать в файл, например “trace_error.sql” в папку, указанную в пути ОС и тогда после возникновении ошибки можно будет быстро получить трассировку ошибки, просто написав в ISQL: “READ trace_error”.

Представления в ASA полностью соответствуют стандартам ANSISQL. В представлении можно описать запрос на WatcomSQL или TSQL. Если в представлении не используются агрегатные операции или UNION, то представление является обновляемым и к нему применимы операторы INSERT, UPDATE, DELETE. Если в запросе представления используются подзапросы, то эти правила так же применимы к ним. Если в представлении в WHERE накладывается фильтр на возвращаемые записи и указанна опция WITH CHECK OPTION, то такое представление будет контролировать все добавляемые, изменяемые и удаляемые записи на их соответствие условиям указанного для представления фильтра. Для представлений можно указывать алиасы возвращаемых им полей. Для примера представлений воспользуемся БД ASADEMO, входящей в поставку ASA (в дальнейшем все примеры будут приводиться именно для этой БД):

С учетом того, что в ASA поддерживаются глобальные переменные, область видимости переменных и опции для установки собственной процедуры инициализации каждой подключаемой сессии, то в представлениях можно использовать глобальные и локальные переменные. Конечно, использование локальных переменных неоправданно и если представление будет вызываться без обязательного описания этих переменных перед вызовом, то это приведет к ошибке. Однако использование глобальных переменных я считаю оправданным и удобным. Глобальную переменную можно определить и инициализировать в процедуре обработки подключения сессии к БД (смотрите опцию БД “LOGIN_PROCEDURE”), что будет гарантировать ее существование сразу при подключении любой сессии к БД (в том числе и для Sybase Central и ISQL). Ее значение легко изменить обычной командой SET, в плане запросов она более эффективна, чем например использование пользовательской функции. В целом я считаю, что глобальные переменные в представлениях нужно использовать для фильтрации возвращаемого набора по неким общим параметрам. Например, дата открытого/расчетного для пользователя месяца, некий параметр-флаг, выставляемый для каждого пользователя при подключении, значение конфигурационного параметра, описывающего некую характеристику системы и т.д.

В ASA на диалекте WatcomSQL поддерживаются триггера, вызываемые до физического изменения записей в таблице (BEFORE) и после проведения изменений (AFTER). Поддерживается возможность описания множества триггеров на одну таблицу, порядок выполнения которых регулируется ключевым словом в описании триггера ORDER <N>.

Триггера могут вызываться для каждой изменяемой записи (FOR EACH ROW) или же вызываться один раз для всего множества изменяемых записей (FOR EACH STATEMENT). Если ключевое слово FOR EACH не указано в описании триггера, то он будет считаться как FOR EACH STATEMENT. Как и в других СУБД, в триггерах ASA есть возможность доступа к старым значениям записей (обновленным или удаленным) и к новым значениям (обновленным или добавленным). Ключевое слово REFERENCING позволяет назначить алиасы именам переменных для триггеров FOR EACH ROW и именам таблиц для триггеров FOR EACH STATEMENT, содержащих в себе старые и новые значения полей записей таблицы. Для FOR EACH STATEMENT триггеров назначение имен не обязательно – как и в MSSQL по умолчанию в таких триггерах эти таблицы будут называться INSERTED и DELETED. Для триггеров на операцию изменения записей UPDATE возможно перечисление списка полей, при изменении которых триггера будут вызваны, ключевым словом UPDATE OF <Field1,Field2,...>. В зависимости от проводимой операции (INSERT, UPDATE, DELETE) триггера будут иметь доступ только к соответствующим значениям специальных таблиц триггера. Для операции INSERT будет видна только таблица INSERTED, для операции UPDATE будут видны обе таблицы: INSERTED и DELETED, для операции DELETE будет видна только таблица DELETED. Обращение к несуществующей таблице приведет к ошибке. Для триггеров, обрабатывающих одновременно INSERT, UPDATE и DELETE, чтобы узнать, для какой операции был вызван триггер, существуют системные функции Inserting, Updating и Deleting (например: IF Deleting THEN MESSAGE 'Table Deleted existing' TO CONSOLE). Для триггеров FOR EACH ROW существует возможность описания условий их вызова ключевым словом WHEN <УсловиеВызова>. В этом условие Вы можете ссылаться на новые и старые значения записи триггера и полноценно описать любое условие (например с использованием функций EXISTS() или IN() ). Описание условий вызова позволяет ускорить выполнение триггеров, отсеивая не нужные для обработки триггером записи.

BEFORE триггера удобны для организации проверок изменяемой информации до того момента, как эта информация будет записана в таблицу и попадет в лог-файл БД. BEFORE триггера всегда являются FOR EACH ROW. Приятной особенностью этих триггеров является реализованная в ASA возможность изменения добавляемых или обновляемых значений полей в теле триггера обычной командой SET NEW.FieldName = <Value>. Это позволяет упростить реализацию многих подводных камней – от создания собственных генераторов счетчиков полей, до создания ключей, описывающих ветки иерархических структур. C учетом того, что блок проверки правильности информации (NOT NULL, CONSTRAINT UNIQUE и CHECK) всегда выполняется после триггеров BEFORE, это дает неограниченные возможности для проектирования сложных структур таблиц.

AFTER триггера удобны для организации проведения дополнительных операций после того, как записи уже были физически записаны в БД (дополнительная обработка в дочерних таблицах, перерасчеты агрегатных значений в организованных суммарных таблицах и т.д.). Стоит помнить, что при возникновении ошибки, в отличие от BEFORE триггеров, ASA будет вынуждена произвести полный откат изменений таблицы с лог-файла БД, что приводит к дополнительным накладным расходам.

На диалекте TSQL в ASA, в целях совместимости с Sybase ASE, поддерживаются только AFTER FOR EACH STATEMENT триггера, без возможности назначения алиаса таблицам INSERTED и DELETED. Так же нельзя описать более одного триггера на проводимую операцию, нет поддержки каскадного выполнения триггеров, т.е. при изменении таблиц из тела триггера соответствующие триггера вызываться не будут. Если Вы не планируете разрабатывать базу данных, совместимую с Sybase ASE или MSSQL, то использование TSQL будет только ограничивать Ваши возможности при проектировании БД (как в приведенном случае с триггерами), не поддерживая много прекрасных возможностей, реализованных в диалекте WatcomSQL.

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

Как и в других СУБД, хранимые процедуры в ASA на WatcomSQL могут иметь IN, OUT и INOUT параметры. По умолчанию параметр считается как INOUT. Для параметров можно указывать значение по умолчанию, если он не был указан при вызове процедуры. Для процедуры можно указать в ключевом слове RESULT описание полей возвращаемого набора данных. Если в процедуре присутствует ключевое слово RESULT, то в не зависимости от алиасов и типов полей, на выходе, процедура будет автоматически преобразовывать их к указанным в RESULT именам и типам полей. Поддерживается возврат множества наборов данных. Если возвращаемые наборы данных имеют разное количество полей, то использование ключевого слова RESULT запрещено и рекомендуется в самих запросах вручную приводить имена полей и их типы к необходимым значениям. Если Вам нужна совместимость с Sybase ASE или MSSQL, или же Вы планируете самостоятельно проводить обработку ошибок для каждого оператора в процедуре, то можно воспользоваться ключевым словом ON EXCEPTION RESUME. Если оно не указано, то в процедуре будет использована стандартная модель обработки исключений BEGIN ... EXCEPTION ... END, которая будет рассмотрена в соответствующем выпуске рассылки, посвященному WatcomSQL. Процедура может быть использована в SELECT, наравне с таблицами и представлениями, что дает возможность в виде процедур оформлять параметризованные запросы. Однако, стоит помнить, что оптимизатор не имеет возможности построить эффективный план запроса, в котором хранимая процедура связывается с таблицами и представлениями. Ему придется сначала получить результат хранимой процедуры во временную таблицу и уже ее связывать с таблицами запроса. С учетом вышесказанного я думаю, использование хранимых процедур в запросах будет оправданно, если они производят какие то сложные манипуляции с данными, которые в дальнейшем должны участвовать в запросах.

Для процедур, оформленных на диалекте TSQL, параметр по умолчанию считается, как IN. Так же возможно объявление OUT параметров, которые будут эквивалентны INOUT параметрам WatcomSQL. Возможность описать имена и типы возвращаемых процедурой полей отсутствует. Так как в диалекте TSQL отсутствует модель обработки ошибок в виде исключений, то TSQL процедуры всегда будут продолжать выполнение кода после возникновения ошибки (аналогично указанию в WatcomSQL процедурах ключевого слова ON EXCEPTION RESUME). Поддерживается опция для процедур WITH RECOMPILE, однако она сделана для совместимости и никакой смысловой нагрузки не несет.

Функции могут быть написаны как на WatcomSQL, так и TSQL диалекте. Функция может быть DETERMINISTIC (кэшируемая) и NOT DETERMINISTIC (вызываемая всегда). Как и в хранимых процедурах функции могут обрабатывать ошибки с помощью перехвата исключений в WatcomSQL и всегда продолжают выполнение после ошибки в TSQL. Ближе всего пользовательские функции ASA к UDF-функциям MSSQL 2000. Однако есть и отличия:

1. в MSSQL функции могут возвращать наборы данных, в ASA это не поддерживается и все функции скалярные.

2. в MSSQL функции не могут вызывать хранимые процедуры, изменять записи в таблицах (кроме локальных табличных переменных), управлять транзакциями, организовывать курсоры, работать с динамическим SQL и т.д.. В ASA функции ничем не отличаются в плане функциональности от хранимых процедур и не имеют никаких ограничений. Однако я бы порекомендовал сильно не увлекаться и помнить, что функции изначально разрабатывались как скалярные функции и их использование не по назначению способно причинить серьезный ущерб производительности при использовании функций в запросах. Для серьезных операций будет правильней использовать хранимые процедуры.

До встречи в следующей рассылке, с уважением, ASCRUS.