Форум FAQ О команде
Поиск по сайту:
 
Sybase
Sybase ASA  
Sybase ASE  
Sybase IQ  
Sybase PowerDesigner  
Sybase PowerBuilder  

Продукты
Наши продукты  
Продукты партнеров портала  

Сообщества
Alef community  

Меню
Регистрация  
Почта  

Вход

Рассылка
Рассылка 'РСУБД Sybase ASA 9 - мощность, легкость и надежность'
Почтовая рассылка
"Технологии обработки данных компании Sybase"


Статистика
Яндекс цитирования

Rambler's Top100
Находится в каталоге Апорт

Главная -> Рассылка
Выпуск 9

Выпуск 9

« РСУБД Sybase ASA 9 - мощность, легкость и надежность »

Рассылка N 9

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

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

Новости от Sybase

  • Sybase решила продолжить акцию поддержки разработчиков и выложила бесплатный для разработчиков продукт M-Business Anywhere Developer Edition. Данный продукт позволяет на основе решений M-Business software и СУБД Anywhere UltraLite строить полноценные приложения для карманных персональных компьютеров (КПК) Pocket PC и Palm. Sybase ASA UltraLite в данном случае выступает как мобильная СУБД, позволяющая хранить и обрабатывать данные на КПК и с помощью репликации синхронизироваться с головными (центральными) базами данных. Решения M-Business в свою очередь предоставляют разработчику возможность с помощью веб-интерфейса быстро и легко организовать интерфейсную часть для пользователя. Как только представится подходящая возможность поближе познакомиться с этим продуктом и посмотреть его в действии, мы обязательно рассмотрим его в рассылке, буду признателен, если кто то его уже использует и готов поделиться о нем впечатлениями.
  • В разделе Download на Sybase.com произошли непонятные обновления: заявленный мной в предыдущем выпуске рассылки EBF для Linux был убран, обновления Sybase ASA 9 до версии 9.01 были выложены заново от 8 марта, причем файлы имеют другой размер. Объяснений этому я нигде к сожалению не нашел и остается только предположить, что или была обнаружена серьезная ошибка в вышедшем обновлении и Sybase решил не плодить EFB, а сразу включить ее исправления в версию 9.01 .
  • Наше российское представительство Sybase не отстает от своих западных коллег и проводит 30 марта 2004 года конференцию с участием крупного европейского специалиста, в которой будет подробно рассмотрен уникальный по возможностям продукт Sybase IQ. Эта РСУБД позволяет строить хранилища данных или оптимизировать производительность существующих, и получать из них любую аналитическую информацию, причем в отличие от технологии OLAP хранилище данных описывается как обычная реляционная база данных и пользователи системы получают возможность получать любые данные через обычный SQL. Это дает очевидное преимущество по сравнению с OLAP, так как не нужно нанимать специалистов, разбирающихся в OLAP, данные легко обновляются через обычные запросы или репликацию и пользователь имеет возможность проводить аналитику в любом удобном для него разрезе, в отличие от OLAP серверов, в которых приходиться при построении хранилища данных жестко описывать все возможные разрезы аналитики. С учетом того, что Sybase IQ хранит данные в сжатом виде и хранилище данных получается в 2-3 раза меньше исходных баз данных, готов работать с СУБД разных производителей и благодаря специальной запатентованной Sybase технологии хранения данных способен в десятки и даже сотни раз, по сравнению с обычными СУБД, быстрее обрабатывать огромные массивы информации для тысяч пользователей, я думаю, он действительно заслуживает звание лучшего продукта для построения хранилищ данных. Более подробную информацию о предстоящей конференции Вы сможете прочитать на сайте российского представительства 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 (даты работы индивидуальных графиков)
    
    Для определения типа графика в таблице Graph существует поле TypeGraph, в котором может содержаться одна из 3 букв: Н (недельный), С (сменный) и И (индивидуальный). Для контроля за правильным заполнением этого поля я на таблицу создал CHECK COLUMN CONSTRAINT. Так как график может учитывать праздничные дни, в таблице я создал поле IsHoliday, с типом Bit, который может хранить значения ноль или один. Тип bit идеально подходит для хранения состояний флагов, где значение ноль можно считать, что атрибут запрещен (выключен), а значение один, что разрешен (включен). Остальные таблицы являются дочерними по отношению к таблице Graph, их внешний ключ описан с опцией каскадного удаления, что позволяет при удалении графика автоматически удалять данные из этих таблиц. В зависимости от типа графика данные по описанию его работы необходимо заносить в соответствующую таблицу:
    • Для недельных графиков описание их работы заносится в таблицу 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 (сама дата работы).

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

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

    Для автоматической очистки кэша расписания графиков на таблицы "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", которая ее вызывает, а потом возвращает с кэша графиков список дат работы графика. Эту процедуру будет удобно использовать для возврата расписания графика клиентскому приложению.

    Заключение

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

    Большое спасибо всем тем, кто принял участие в подготовке рассылки:

    • Наталья Алешина (менеджер московского представительства Sybase)

    • Федор Корюгин (программист)

    • Алексей Орлов (программист)

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

    Материалы данной рассылки являются собственностью ее автора. При использовании информации из рассылки, ссылка на автора обязательна.




    Дата публикации: Monday 27 March 2006 16:10:39
    Материал прочитан: 9033 раз(а)
    [ Назад ]



    Продвижение сайтов
    Биржа ссылок

    Copyright©2005 phpMS. Все права защищены.
    hosted on pets-tree

    Warning: Unknown: write failed: Disk quota exceeded (122) in Unknown on line 0

    Warning: Unknown: Failed to write session data (files). Please verify that the current setting of session.save_path is correct (/var/www/rusug-ru/data/bin-tmp) in Unknown on line 0