Выпуск 9

Здравствуйте. Представляю Вашему вниманию новый выпуск рассылки. В нем мы продолжим на практических примерах изучать особенности Sybase ASA и проектировать собственную базу данных. В предыдущих выпусках я уже выкладывал скрипты базы данных прямо в рассылке, однако с учетом того, что решения, которые мы будем рассматривать, будут усложняться, я решил изменить идеологию рассылки и не выкладывать скрипты создания объектов в рассылку, а выкладывать в интернете уже готовую базу данных и SQL скрипт, по которому в рассылке я буду давать подробные комментарии. Надеюсь, это окажется для Вас удобней. Перед чтением рассылки скачайте, пожалуйста, этот архив, в нем находится база данных и скрипт, описывающий создание всех объектов, которые мы будем рассматривать в этом номере рассылки. Подключите и откройте БД через Sybase Central. Скрипт можно будет загрузить для просмотра в ISQL, однако выполнять его не надо, так как в базе данных все объекты, описанные в нем уже созданы.

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

• Новости от Sybase
• Постановка - построение справочника графиков плана развоза продукции
• Проектирование справочника праздников
• Проектирование справочника графиков плана работы развоза продукции
• Проектирование кэша рабочих дней графиков
• Заключение

Продолжим проектирование нашей БД. В прошлом выпуске я организовал справочник конфигурации параметров системы и продукции. Чтобы продолжить построение БД необходимо определиться с постановкой задачи:
Итак, пусть предприятие, для которого пишется эта база данных, будет заниматься поставкой заказанных товаров своим клиентам, согласно заключенным с ними договорам. В договоре клиент перечисляет, какие товары ему необходимо поставлять и по какому графику развоза. Например, булочная заключает договор на поставку различных сортов хлеба ежедневно, кроме воскресенья и праздничных дней, когда у булочной выходной, и она закрыта. А круглосуточный универсам заключает договор на поставку определенных вин через каждые трое суток в не зависимости от дня недели или праздника.
Так как по определению постановки задачи клиенты при заключении договора оговаривают план развоза продукции, то самым оптимальным решением является спроектировать в базе данных справочник графиков развоза, которые бы позволяли описать режим развоза продукции. Исходя из постановки задачи можно выделить 3 различных типа графиков развоза продукции:

1. Недельный
   - продукция развозиться в зависимости от дней недели
2. Сменный
   - продукция развозиться через каждые "n" суток
3. Индивидуальный
   - продукция развозиться по календарному месяцу расписанных работ

В базе данных я спроектировал таблицу "Holiday", описывающую праздники. В ней всего 2 поля: дата праздника и его наименование. Однако так как в базе данных есть такое понятие, как текущий расчетный период (определяется переменной @@CalcDate, введенной нами в рассылке номер 7), то необходимо контролировать, чтобы в справочник праздников пользователи не могли вводить праздники задними числами, то есть с датами меньше, чем текущий расчетный период. Легче всего организовать такой контроль с помощью триггеров. На таблицу "Holiday" я организовал триггер "Holiday_Valid_Change", который вызывается до вставки или изменения записи и триггер "Holiday_Valid_Delete", вызывающийся до удаления записи. Эти триггера с помощью ключевого слова WHEN срабатывают только при условии, что дата праздника меньше, чем текущий расчетный месяц:

... WHEN ( // для всех праздников, изменяемых ранее текущего расчетного месяца NewValue.WorkDate < @@CalcDate ) ...

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

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

--Graph (справочник графиков с их типом)
    -- GraphWeek (описание дней работы недельных графиков)
    
    -- GraphSlideStart (даты начала действия сменных графиков)
    -- GraphSlide (последовательность работы сменных графиков)
    
    -- GraphCalend (даты работы индивидуальных графиков)

• Для недельных графиков описание их работы заносится в таблицу GraphWeek. Здесь мы на каждый недельный график можем перечислить дни недели, в которые необходимо производить развоз продукции.
• Для сменных графиков описание их работы заносится в таблицу GraphSlide. Для определения очередности работы в таблице служат 2 поля: Num (порядковый номер в очереди) и GraphOper (флаг операции в очереди). Поле GraphOper может принимать два значения: "Р" (рабочий день) и "В" (выходной). Для контроля его правильного заполнения я создал на таблицу CHECK COLUMN CONSTRAINT. Таким образом, в этой таблице мы можем описать любой сменный режим работы. Например, чтобы определить режим "Завоз продукции раз в трое суток", необходимо добавить 3 записи в таблицу, где у записи с Num равным 1, GraphOper будет равен 'Р', а у двух других записей с Num равным 2 и 3, GraphOper будет равен 'В'. Описание сменного графика было бы не полным, если бы мы не знали дату начала его работы. Плюс, в какой-то момент времени может понадобиться сбить действие графика на указанную дату. Например, приведенный выше в примере график начал действовать с 1 января. То есть выходит, что по нему развоз продукции будет производиться 1, 4, 7, 10, 13, 16 января и так далее. Однако выяснилось, что график сбивается и вместо 13 января начинает работать с 12, далее 15 и т.д. Для того, чтобы обрабатывать такие ситуации я спроектировал таблицу GraphSlideStart, в которой на каждый сменный график ставиться дата начала его действия. Если график необходимо "перебить" на другую дату, то достаточно будет добавить запись в таблицу на этот график, с указанием новой даты его начала действия.
  Для того, чтобы пользователь не смог перевести дату начала работы графика задним числом, я написал триггера "GraphSlideStart_Valid_Change" и "GraphSlideStart_Valid_Delete", которые по принципу работы аналогичны триггерам контроля изменения задним числом для таблицы "Holiday".
• Для индивидуальных графиков описание их работы заносится в таблицу GraphCalend. Структура хранения данных в ней простая: на график выставляются все дни его работы. Чтобы пользователь не мог вносить даты работы задним числом, я создал два соответствующих триггера: "GraphCalend_Valid_Change" и "GraphCalend_Valid_Delete".

Описанная выше структура графиков позволяет эффективно хранить описание работы различных типов графиков, однако для того, чтобы получить на необходимый месяц реальное расписание графика, нужны соответствующие затраты на расчет и построение рабочих дней графика. Конечно, расчет дней работы графика на указанный месяц можно вынести в хранимую процедуру и каждый раз, вызывая ее, получать необходимый список рабочих дней графика. Однако это несет накладные расходы для СУБД, которой придется каждый раз выполнять непростые действия для построения графика и главное это будет неэффективно при использовании такой процедуры в запросах, использующих соединения этой информации с таблицами, содержащих большой объем записей. С точки зрения данной мной постановки задачи справочник графиков является более менее статичной структурой (пользователи вряд ли будут каждый день изменять графики), с не часто изменяющейся информацией, на основе которой строится расчетная информация, в данном случае расписание работ за месяц. Исходя их этого, гораздо выгоднее создать специальную таблицу, в которой бы сохранялись уже рассчитанные расписания работ графиков для потребовавшихся месяцев. Такие структуры я называю кэшами (по аналогии в СУБД кэша содержат часто использованную или рассчитанную информацию). Кэша являются обычными таблицами с предрассчитанной или вынужденно-денормализованной информацией. Они облегчают получение данных в сложных запросах и снимают с СУБД нагрузку, возникающую при получении в выборках данных из сложных структуры или рассчитываемой информации.
Чтобы различать кэша от обычных таблиц, я ввел соглашение по их названию - все таблицы-кэша начинаются с префикса "T_". Для хранения уже рассчитанных расписаний графиков я организовал таблицу T_GraphDay, внешний ключ которой ссылается на таблицу Graph с поддержкой каскадного удаления. Чтобы ускорить поиск расписания графика за определенный месяц я ввел два поля: CalcDate (дата расчетного месяц, день всегда равен единице) и WorkDate (сама дата работы).

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

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

Для автоматической очистки кэша расписания графиков на таблицы "GraphCalend", "GraphSlide", "GraphSlideStart", "GraphWeek" и "Holiday" я написал триггеры с префиксом "_ClearCache", которые вызываются после проведения добавления, изменения или удаления записей в этих таблицах и очищают по изменившимся графикам соотвествующую информацию по графикам в таблице T_GraphDay.

Для того, чтобы построить кэш расписания графиков, нам понадобится правильно установить опцию "First_Day_Of_Week", в которой указан номер первого дня недели. По умолчанию он равен "7", так как за рубежом началом недели считается воскресенье. Необходимо переустановить его на понедельник:

SET OPTION PUBLIC.First_Day_Of_Week = 1;

Далее при работе с различными периодами в запросах часто будут встречаться записи, где есть дата начала действия информации, а дата конца действия равна значению NULL (бесконечность). По стандарту SQL значение NULL не участвует в операциях сравнения, это будет усложнять запросы, где необходимо выбирать информацию в зависимости от периода ее действия. Чтобы облегчить ситуацию, мы введем глобальную переменную в конфигурацию, обозначающую бесконечность вместо NULL очень далекой от текущей датой:

INSERT INTO Config (Name, Level, TypeValue, Value, Description) VALUES ('@@NullDate', 0, 'date', '''20500101''', 'Дата, обозначающая бесконечность, заменяющая NULL');

Не забудьте, что таблица Config, создание которой описано в седьмом номере рассылки, допускает свое изменение только при условии, что сессия находится в монопольном режиме, и никто больше к БД не подключен. В данном случае я в качестве значения, обозначающего бесконечность выбрал 1 января 2050 года, это вполне далекая дата и вряд ли в проекте появится информация за более поздний период.

Чтобы строить различные запросы по дням месяца, необходимо расширить функциональность структуры базы данных. Я создал таблицу DayOfMonth, которая описывает дни месяца в виде одного поля Num и содержит 31 запись. Эта таблица нам будет служить макетом для построения запроса получения списка дней указанного месяца. Так как такой запрос может вернуть максимум 31 запись, то такое решение я организовал через хранимую процедуру "sp_DayOfMonth". Фактически в ней на указанный месяц сначала получается номер его последнего дня, и запросом возвращаются все дни с таблицы "DayOfMonth" с первого по последний день в указанном месяце. Вот код тела этой ХП:

// Получаем последний день в заданном месяце DECLARE @LastDay tinyint; SET @LastDay = Day(fn_CDate( @CalcDate, 1)); // Берем с таблицы DayOfMonth все дни с 1 по последний день // указанного месяца SELECT YMD(Year(@CalcDate), Month(@CalcDate), Number) FROM DayOfMonth WHERE Number <= @LastDay;

Обратите внимание, что для получения последнего дня месяца, мы используем функцию fn_CDate, описанную в выпуске номер 7. В самом запросе системная функция YMD преобразовывает переданный год, месяц и номер дня в дату.

Сама процедура расчета расписания графика работ называется "sp_Build_GraphDay". Алгоритм ее очень прост - определяется, не присутствуют ли уже данные в кэше расписания графиков. Если они присутствуют, то кэш уже просчитан и процедура заканчивает свое действие. Иначе получаются тип графика и флаг учета праздников. Затем в зависимости от типа графика выполняется нужный алгоритм его расчета. Обратите внимание на использование оператора CASE для организации ветвления исполнения кода в зависимости от типа графика. В отличие от алгоритмических языков, где такой оператор удобное и обычное явление, во многих СУБД он поддерживается только для запросов. В коде приходится в качестве операторов ветвления использовать многочисленные операторы IF ELSE, что ведет к снижению читабельности кода. К счастью в ASA такой оператор поддерживается. Чтобы не увеличивать лишний раз размер рассылки я не буду приводить полный текст хранимой процедуры расчета расписания работы графиков. Полный текст всегда можно посмотреть через Sybase Central в базе данных или же в скрипте. Я же только прокомментирую наиболее важные и сложные части скрипта этой процедуры.

Этот запрос возвращает расписание на указанный месяц для недельного графика работы:

SELECT @Graph_id, @CalcDate, d.WorkDate FROM GraphWeek g // Описание графика // Соединяем его с днями месяца по дням недели INNER JOIN sp_DayOfMonth (@CalcDate) d ON DatePart(dw, d.WorkDate) = g.WeekDay WHERE g.Graph_id = @Graph_id;

С помощью процедуры "sp_DayOfMonth" мы получаем список всех дат указанного месяца. В GraphWeek на указанный график хранятся перечисления номеров дней недели, по которым производится развоз продукции. С каждой даты месяца мы получаем его номер дня недели и соединяем их с таблицей GraphWeek. Для получения дня недели используется системная функция DatePart, которая позволяет из даты получить указанную логическую часть даты - это может быть день, месяц, год, номер квартала, дня недели и т.д.

Для того, чтобы правильно получить расписание сменного графика работы, для начала подсчитывается его общее количество смен. Далее следующий запрос возвращает расписание на указанный месяц для сменного графика работы:

WITH GraphSlideStartPeriod AS ( // Промежутки действия отсчета дней графика SELECT s.Graph_id, s.StartDate, DateAdd(dd, -1, IsNull(l.LastDate, @@NullDate)) AS FinishDate FROM GraphSlideStart s, LATERAL ( SELECT Min(sl.StartDate) AS LastDate FROM GraphSlideStart sl WHERE sl.Graph_id = s.Graph_id AND sl.StartDate > s.StartDate ) AS l ) SELECT @Graph_id, @CalcDate, d.WorkDate FROM GraphSlide g // Описание графика // Промежутки отсчета INNER JOIN GraphSlideStartPeriod p ON p.Graph_id = @Graph_id // Дни месяца INNER JOIN sp_DayOfMonth (@CalcDate) d on d.WorkDate BETWEEN p.StartDate AND p.FinishDate WHERE g.Graph_id = @Graph_id AND // Только для рабочих дней g.GraphOper = 'Р' AND // Соединяем по порядковому номеру дня в графике g.Num = DateDiff(dd, p.StartDate, d.WorkDate) - Round(DateDiff(dd, p.StartDate, d.WorkDate) / @CountDay, 0) * @CountDay + 1;

Чтобы правильно определить расписание сменных графиков нам необходимо знать 2 условия: кол-во смен в графике и даты для каждого периода его действия. Чтобы запрос был более читабельным и ASA мог лучше его оптимизировать, я вынес запрос получения периодов начала действия графика в секцию WITH, фактически организовав виртуальное представление, которое далее использовал в запросе. Как и в седьмом выпуске рассылки для построения периодов действия на таблицу, хранящую историю как список дат, где более поздняя дата закрывает более раннюю, для таблицы GraphSlideStart я написал запрос с использованием внутреннего соединения LATERAL. В главном запросе происходит довольно хитрое соединение таблиц. На дни месяца, возвращаемые процедурой "sp_DayOfMonth" накладываются даты начала действия смен графика, которые соединяются по Graph_id. Для каждого дня в условии фильтра WHERE через формулу вычисляется порядковый номер смены графика и, следовательно, если в описании графика операция равна "Р" (работа), эта дата будет возвращена в результате запроса. В самой формуле через системную функцию DateDiff в днях вычисляется разница между датой начала действия и днем месяца, а с помощью системной функции Round производится округление от деления этой разницы и кол-вом смен в описании графика.

Для расчета индивидуального графика нам достаточно просто перенести все его даты из расписания, попадающие под указанный месяц:

SELECT @Graph_id, @CalcDate, WorkDate FROM GraphCalend // Описание графика WHERE Graph_id = @Graph_id AND // Для всех дней, у кого сброшенная на первое число месяца дата равна // указанному месяцу fn_CDate(WorkDate, 0) = @CalcDate;

Для сравнения даты с указанным месяцем действия графика я опять воспользовался функцией "fn_CDate".

В конце процедуры, если у графика выставлен флаг учета праздников, удаляются все даты, обозначенные как праздники в таблице Holiday.

Процедура расчета и заполнения кэша графиков готова и на ее основе я создал процедуру "sp_Graph_View", которая ее вызывает, а потом возвращает с кэша графиков список дат работы графика. Эту процедуру будет удобно использовать для возврата расписания графика клиентскому приложению.

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

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