Выпуск 7
|
«
РСУБД Sybase ASA 9 - мощность, легкость и надежность
»
Рассылка N 7 |
Здравствуйте, уважаемые коллеги. Представляю Вашему вниманию новый выпуск рассылки. С целью повышения
эффективности рассылаемого материала я решил изменить стиль рассылки и дальнейшее рассмотрение ASA вести
с помощью практических примеров. Надеюсь, что Вы не будете против :) Все примеры будут взаимосвязаны и
представлять из себя реальную базу данных. Фактически мы будет проектировать небольшой пилотный проект,
с открытым исходным текстом, который будет выложен на открывающемся вскоре веб-зеркале рассылки (об этом будет
сообщено дополнительно). Для выполнения примеров Вам будет желательно создать пустую базу данных и иметь
под рукой открытый ISQL.
Содержание рассылки:
Российской представительство Sybase выложило продолжение перевода Book On Line. Документация
посвященна SQLRemote - репликации средствами Sybase ASA. Скачать ее можно с сайта
представительства Sybase.
На русскоязычном форуме SQL.RU Sybase PowerBuilder
идет обсуждение, насколько востребована русскоязычная литература по продукции Sybase в странах СНГ. Книжное издательство
BHV выдвинуло на обсуждение идею о переводе популярных зарубежных книг по Sybase. Приглашаю всех желающих принять
обсуждение этого предложения на указанном форуме и высказать своим мысли - нужны ли такие книги, переводы каких книг
хотелось бы прочитать и насколько они будут востребованны в странах СНГ.
Частенько при манипуляции с датами бывает очень полезно установить в дате день на первое
или последнее число месяца. Чтобы облегчить эту задачу я написал функцию:
CREATE FUNCTION "DBA"."fn_CDate" (
@Date date, // обрабатываемая дата
@LastDay tinyint // тип обработки (0-на первый день/1-на последний день)
)
RETURNS date
BEGIN
DECLARE @Result date;
// Сбрасываем на первый день
SET @Result = YMD(Year(@Date), Month(@Date), 1);
// Если сброс на последний день, то прибавляем месяц и отнимаем день
IF @LastDay = 1
THEN
SET @Result = DateAdd(dd, -1, DateAdd(mm, 1, @Result));
END IF;
RETURN @Result;
END;
COMMENT ON PROCEDURE "DBA"."fn_CDate"
IS 'Сброс указанной даты на первый или последний в месяце день';
|
В проекте нам потребуется функция проверки на монопольный доступ к БД. Данная функция позволит
определить, есть ли еще подключения с базе данных или же активным подключением является только
текущая сессия:
CREATE FUNCTION "DBA"."fn_IsExclusiveMode" ()
RETURNS tinyint
BEGIN
DECLARE @ConnID int;
// Получаем первую подключенную сессию
SET @ConnID = Next_Connection(@ConnID, NULL);
// Перебор подключенных сессий
list_conn: LOOP
// Сессий больше нет
IF @ConnID IS NULL THEN
LEAVE list_conn;
END IF;
// Обнаружена другая сессия
IF @ConnID <> @@SPID THEN
// БД не в монопольном режиме
RETURN 0;
END IF;
// Получаем следующую сессию
SET @ConnID = Next_Connection(@ConnID, NULL);
END LOOP list_conn;
// БД в монопольном режиме
RETURN 1;
END;
COMMENT ON PROCEDURE "DBA"."fn_IsExclusiveMode"
IS 'Проверить монопольность подключения';
|
Обратите внимание, что все активные сессии перебираются путем вызова системной функции Next_Connection.
Первым параметром передается код сессии, с которой надо возвратить следующую сессию. Если значение параметра
равно NULL, то возвращается самая первая сессия. Второй параметр - код базы данных, с которой необходимо
получить код сессии. Если значение параметра равно NULL, то обрабатывается текущая база данных.
В любом проекте есть такое понятие, как конфигурационные параметры. Это может быть текущий месяц,
в котором проводяться изменения (расчетный месяц), значения различных коэффициентов и т.д. Обычно для
хранения таких параметров в базе данных создается специальная таблица, в которой они и хранятся.
Однако хранить такие параметры - это полдела. Самое главное, это использовать их значения в логике БД.
В каждой СУБД эти проблемы решаются по своему. Я хочу предложить Вам свое решение для хранения и работы
с такими параметрами.
Для начала создадим таблицу, в которой и будут храниться параметры и их значения:
CREATE TABLE "DBA"."Config" (
"Name" varchar(128) NOT NULL, // Уникальное имя параметра
"Level" smallint NULL, // Номер уровня создания
"TypeValue" varchar(128) NOT NULL, // SQL тип параметра
"Value" varchar(512) NULL, // Выражение для вычисления значения параметра
"Description" varchar(256) NULL, // Примечание
PRIMARY KEY CLUSTERED ( "Name" )
);
COMMENT ON TABLE "DBA"."Config" IS 'Параметры проекта';
|
Чтобы использовать значения конфигурационных параметров в запросах можно разработать несколько
схем для их получения:
- Обращаться напрямую к таблице CONFIG в запросах, накладывая условие Name="ParameterName"
- Написать функцию, которая по имени параметра возвращает его значение
- Организовать для каждой сессии параметры в виде глобальных переменных
Каждый из способов имеет недостатки. Первый способ усложняет читабельность запросов, второй -
отрицательно скажется на эффективности планов запросов, так как использование в условиях функций
всегда нежелательно для оптимизатора запросов. Третий способ получается самым эффективным, однако имеет
существенный недостаток - любые изменения значения параметров становятся видны только после переподключения
к базе данных существующих сессий. Как один из вариантов решения проблемы я предлагаю воспользоваться
вторым и третьим способом в зависимости от типа параметра.
Если изменения значения параметра критично и
все подключенные к БД сессии должны тут же увидеть эти изменения, то самым оптимальным является написание
собственной функции:
CREATE FUNCTION "DBA"."fn_Config"(
@Name varchar(128)
)
RETURNS varchar(512)
DETERMINISTIC
BEGIN
DECLARE @Result varchar(512);
SELECT Value
INTO @Result
FROM Config WITH(READCOMMITTED)
WHERE Name = @Name;
RETURN @Result;
END;
COMMENT ON PROCEDURE "DBA"."fn_Config" IS 'Получить динамический параметр конфигурации';
|
В данной функции я поставил уровень чтения таблицы Config на READCOMMITED. Это гарантирует,
что функция будет возвращать значения только подтвержденных параметров, даже если на текущий момент
вызова функции они изменяются в другой транзакции.
Для проверки работоспособности функции можно выполнить следующий код:
BEGIN
// Добавляем параметр Test с установленным значением 100
INSERT INTO Config (Name, TypeValue, Value)
VALUES ('Test', 'int', 100);
COMMIT;
// Получаем значение параметра Test - будет возвращено 100
SELECT fn_Config('Test');
// Удаляляем параметр Test
DELETE FROM Config
WHERE Name = 'Test';
COMMIT;
// Получаем значение параметра Test - будет возвращенно NULL,
// так как параметра уже нет
SELECT fn_Config('Test');
END
|
Обратите внимание, что в приведенном коде используется COMMIT после каждой операции
изменения таблицы Config. В отличие от MSSQL в ASA не требуется явный старт транзакции,
т.к. любой оператор INSERT, DELETE или UPDATE неявным образом начинает транзакцию. Если в
этом скрипте убрать оператор COMMIT, то весь скрипт будет выполняться в пределах транзакции,
неявно стартовавшей после оператора INSERT.
Если значения параметров конфигурации являются статическими или же изменяются только
администратором системы в режиме монопольного доступа, то лучшим вариантом организации
работы с такими параметрами будет работа с их отображением через глобальные переменные.
Будем считать, что все параметрами с префиксом "@@" будут статическими и иметь отображение
в виде глобальных переменных. Напишем процедуру инициализации глобальных переменных по
таблице Config:
CREATE PROCEDURE "DBA"."sp_Config_Init" ()
BEGIN
DECLARE @SQL long varchar;
// Перебираем список переменных с префиксом @@ в таблице Config
FOR lConfig AS cConfig
NO SCROLL CURSOR FOR
SELECT Name AS @Name, TypeValue AS @TypeValue, Value AS @Value
FROM Config
WHERE Name LIKE '@@%'
ORDER BY Level
DO
// Создаем переменную, если она еще не существует
IF VarExists(@Name) = 0
THEN
SET @SQL = 'CREATE VARIABLE ' || @Name || ' ' || @TypeValue;
EXECUTE IMMEDIATE WITH RESULT SET OFF @SQL;
END IF;
// Устанавливаем значение переменной
IF @Value IS NOT NULL
THEN
SET @SQL = 'SET ' || @Name || ' = ' || @Value;
ELSE
SET @SQL = 'SET ' || @Name || ' = NULL';
END IF;
EXECUTE IMMEDIATE WITH RESULT SET OFF @SQL;
END FOR;
END;
COMMENT ON PROCEDURE "DBA"."sp_Config_Init"
IS 'Инициализация глобальных статических параметров в виде глобальных переменных БД';
|
Обратите внимание, что в данной процедуре курсор для перебора параметров организуется
через цикл FOR. Такой способ обработки курсоров гораздо удобнее, чем стандартный и привычный
способ через цикл WHILE и оператор FETCH. К достоинству цикла FOR можно отнести отсутствие
необходимости через оператор DECLARE описывать сам курсор и переменные, в которые необходимо
считывать значения полей курсора оператором FETCH. В данном же случае имя цикла и курсора
задаются при описании FOR, а на основе алиасов возвращаемых курсором полей внутри цикла
автоматически создаются и автоматически заполняются значениями переменные по именам и типам
возвращаемых курсором полей. Чтобы не было неоднозначности между именами полей и переменных,
я специально назначил алиасы полям, добавив к их имени префикс "@". Так же заметьте, что при использовании
динамического SQL в операторе EXECUTE IMMEDIATE я использовал опцию WITH RESULT SET OFF. Это
указывает ASA, что скрипты, выполняемые в динамическом SQL не возвращают наборов данных
и, следовательно, сама хранимая процедура так же не возвращает никаких наборов данных. Не забудьте,
что эта опция появилась только с EFB 1252.
После вызова процедуры sp_Config_Init в сессии будут созданы глобальные переменные с идентичными
параметрам именами. Значения этих переменных будут вычислены на основании выражений из поля Value.
Это дает возможность использовать в значении параметра функции, ссылающиеся на другие параметры
конфигурации. Порядок создания переменных определяется полем Level, что позволяет гарантированно
выполнить инициализацию значений параметра только после инициализации параметра, на которого он
ссылается в выражении.
Далее необходимо организовать контроль за тем, чтобы статические параметры конфигурации могли
изменятся только в режиме монопольного доступа к БД, плюс необходимо, чтобы при их изменениях
перечитывались глобальные переменные. Для этого организуем соотвествующие триггера на таблицу
Config:
// Устанавливаем способ работы RAISERROR
SET OPTION PUBLIC.CONTINUE_AFTER_RAISERROR = 'OFF';
CREATE TRIGGER "Change_Insert" AFTER INSERT
ORDER 1 ON "DBA"."Config"
REFERENCING NEW AS Inserted
FOR EACH STATEMENT
WHEN (
EXISTS(
SELECT *
FROM Inserted
WHERE Name LIKE '@@%'
)
)
BEGIN
// Генерируем ошибку, если доступ к БД не монопольный
IF fn_IsExclusiveMode() = 0
THEN
RAISERROR 20000 'К БД подключенны другие сессии';
END IF;
// Вызываем переопределение глобальных переменных
CALL sp_Config_Init();
END;
CREATE TRIGGER "Change_Update" AFTER UPDATE OF Name, Level, Value
ORDER 1 ON "DBA"."Config"
REFERENCING NEW AS Inserted
FOR EACH STATEMENT
WHEN (
EXISTS(
SELECT *
FROM Inserted
WHERE Name LIKE '@@%'
)
)
BEGIN
// Генерируем ошибку, если доступ к БД не монопольный
IF fn_IsExclusiveMode() = 0
THEN
RAISERROR 20000 'К БД подключенны другие сессии';
END IF;
// Вызываем переопределение глобальных переменных
CALL sp_Config_Init();
END;
CREATE TRIGGER "Change_Delete" AFTER DELETE
ORDER 1 ON "DBA"."Config"
REFERENCING OLD AS Deleted
FOR EACH STATEMENT
WHEN (
EXISTS(
SELECT *
FROM Deleted
WHERE Name LIKE '@@%'
)
)
BEGIN
// Генерируем ошибку, если доступ к БД не монопольный
IF fn_IsExclusiveMode() = 0
THEN
RAISERROR 20000 'К БД подключенны другие сессии';
END IF;
// Вызываем переопределение глобальных переменных
CALL sp_Config_Init();
END;
|
В приведенном скрипте для базы данных сначала выключается опция CONTINUE_AFTER_RAISERROR.
Эта опция определяет, как будет себя вести СУБД при выполнении оператора RAISERROR. Если эта
опция включена, то процедура продолжает выполнение после вызова RAISERROR (как это сделано
в MSSQL), если выключена, то выполнение прерывается (аналогично вызову оператора возбуждения
исключения SIGNAL). Обратите внимание, что на триггера описано условие их вызова, так что
вызываться они будут только в случае, если в таблице Config изменяются значения статических
параметров конфигурации (все, у кого имя начинается с "@@"). Сами триггеры сначала проверяют
мнопольность доступа к БД через описанную выше функцию fn_IsExclusiveMode, а потом вызывают
процедуру инициализации глобальных переменных sp_Config_Init.
Теперь остается написать инициализацию глобальных переменных для каждой подключаемой сессии:
CREATE PROCEDURE "DBA"."sp_Connect_Init" ()
BEGIN
// Вызов стандартной процедуры инициализации подключения
CALL sp_login_environment ();
// Вызов инициализации глобальных переменных проекта
CALL sp_Config_Init();
END;
COMMENT ON PROCEDURE "DBA"."sp_Connect_Init" IS 'Инициализация подключаемой сессии';
// Установить процедуру, вызывающуюся для каждой подключаемой сессии
SET OPTION PUBLIC.LOGIN_PROCEDURE = 'sp_Connect_Init';
|
Обратите внимание, что первой строкой тела процедуры стоит вызов системной хранимой
процедуры sp_login_environment. Эта процедура проводит собственную инициализацию
параметров подключаемой сессии и желательно ее вызвать, чтобы она могла сделать
необходимую работу.
Для проверки работоспособности механизма конфигурации занесем в нее несколько параметров:
// Заносим параметры.
// Если к базе еще кто то подключен, то будет сгенерированна ошибка
INSERT INTO Config (Name, Level, TypeValue, Value, Description) ON EXISTING UPDATE
SELECT '@@DB_Version', 0, 'numeric(5, 2)', '1.0', 'Версия ПО'
UNION ALL
SELECT '@@CalcDate', 0, 'date', '20040101', 'Текущий расчетный месяц'
UNION ALL
SELECT '@@CalcDateLast', 1, 'date',
'fn_CDate(@@CalcDate, 1)', 'Последний день в текущем расчетном месяце';
COMMIT;
// Показываем параметры в виде глобальных переменных в текущей или подключившейся
// сессии
SELECT @@DB_Version, @@CalcDate, @@CalcDateLast;
|
Обратите внимание, что параметр @@CalcDateLast имеет вычисляемое значение от @@CalcDate
через функцию fn_CDate и уровень создания больший, чем @@CalcDate, чтобы гарантированно
вычислить свое значение только после инициализации @@CalcDate.
Для наглядного изучения работы с иерархическими данными добавим в проект справочник групп товаров:
// Создание таблицы групп товаров
CREATE TABLE "DBA"."ProductGroup" (
"ProductGroup_id" varchar(8) NOT NULL, // Код группы
"Name" varchar(50) NOT NULL, // Наименование группы
"Parent_id" varchar(8) NULL, // Код родительской группы
PRIMARY KEY CLUSTERED ( "ProductGroup_id" )
);
COMMENT ON TABLE "DBA"."ProductGroup" IS 'Группы товаров';
// Создание внешнего ключа связи между кодом группы и
// родительским кодом группы
ALTER TABLE "DBA"."ProductGroup"
ADD FOREIGN KEY "ProductGroup" ( "Parent_id" )
REFERENCES "DBA"."ProductGroup" ( "ProductGroup_id" )
ON DELETE CASCADE CHECK ON COMMIT;
|
В данной таблице можно хранить дерево групп товаров. В качестве кода группы и первичного
ключа таблицы выбрано поле с текстовым типом данных, в котором будет храниться условное
наименование группы. Для хранения ссылки на родительскую группу сделано поле Parent_id.
Если Parent_id будет равен NULL, то группа товаров является вершиной дерева и не имеет
родительской группы. Для того, чтобы при изменении данных в таблице, в которой внешний
ключ ссылается на эту же таблицу, не происходило ошибок при проверке ссылочной целостности
данных, для него установлена опция CHECK ON COMMIT. Данная опция позволяет отложить проверку
целостности данных до вызова оператора подтверждения транзакции COMMIT. Так же для
связи внешнего ключа указана опция каскадного удаления. Это позволит удалять дочерние группы
вместе с удаляемой родительской группой.
Занесем в таблицу группы товаров:
INSERT INTO ProductGroup (ProductGroup_id, Name, Parent_id)
SELECT 'Прод', 'Продукты питания', NULL
UNION ALL
SELECT 'Хлеб', 'Хлебобулочные изделия', 'Прод'
UNION ALL
SELECT 'Молоч', 'Молочные продукты', 'Прод'
UNION ALL
SELECT 'Тех', 'Техника', NULL
UNION ALL
SELECT 'БытТех', 'Бытовая техника', 'Тех'
UNION ALL
SELECT 'ВидеоТех', 'Видеоэлектронника', 'Тех'
UNION ALL
SELECT 'ТВ', 'Телевизоры', 'ВидеоТех'
UNION ALL
SELECT 'ВМ', 'Видеомагнитофоны', 'ВидеоТех';
COMMIT;
|
Получилась следующая иерархия групп:
-Продукты питания
|-Молочные продукты
|-Хлебобулочные изделия
-Техника
|-Бытовая техника
|-Видеоэлектронника
|-Видеомагнитофоны
|-Телевизоры
Можно написать хранимую процедуру, которая будет в виде отсортированного дерева возвращать
значения из справочника групп товаров:
CREATE PROCEDURE "DBA"."sp_ProductGroup_View" ()
BEGIN
WITH RECURSIVE h (ProductGroup_id, Name, Parent_id, Level, Path)
AS (
-- Родительские группы
SELECT ProductGroup_id, Name, Parent_id, 0,
CONVERT(char(250), ProductGroup_id)
FROM ProductGroup
WHERE Parent_id IS NULL
UNION ALL
-- Дочерние группы
SELECT pg.ProductGroup_id, pg.Name, pg.Parent_id, h.Level + 1,
CONVERT(char(250), h.Path || '/' || pg.ProductGroup_id)
FROM ProductGroup pg
INNER JOIN h ON h.ProductGroup_id = pg.Parent_id
WHERE pg.Parent_id IS NOT NULL
)
SELECT CONVERT(varchar(100), Space(Level * 5) || Name) AS Name
FROM h
ORDER BY Path;
END;
COMMENT ON PROCEDURE "DBA"."sp_ProductGroup_View" IS
'Возвратить группы товаров в виде отсортированного дерева';
|
В данной процедуре использовано расширение SQL CTE (Common Table Expression) оператором
WITH RECURSIVE, позволяющее выполнять иерархические запросы. Обратите внимание на вычисляемые
столбцы Level и Path. Они позволяют внутри запроса рассчитать уровень вложенности дерева и
сгенерировать строку-путь от вершины дерева до обрабатываемого элемента в виде
"КодРодителя/КодРодителя/.../КодЭлемента". С помощью Level далее в запросе высчитываются отступы
для дочерних групп, а с помощью Path производиться сортировка полученного дерева. Если выполнить
эту хранимую процедуру, то Вы получите точно такое же дерево иерархии групп, которое я отобразил
чуть выше.
В практике проектирования баз данных почти любой учетной задачи очень часто разработчикам приходится сталкиваться с проблемами хранения
состояний объектов во времени. Все схемы хранения истории состояний обьектов можно в принципе разделить на несколько
моделей:
-
Последовательная - датой начала действия нового состояния обьекта является дата окончания действия предыдущего.
Датой окончания последнего (текущего) состояния объекта является бесконечность.
-
Периодическая - состояние объекта имеет дату начала и дату окончания действия. Если дата окончания действия не установлена,
т.е. имеет NULL значение, то датой окончания действия считается бесконечность. Периоды действия состояний объекта не должны
пересекаться.
-
Последовательная сторно - последовательная модель, поддерживающая возможность изменения своих
предыдущих состояний задним числом.
-
Периодическая сторно - периодическая модель, поддерживающая возможность изменения своих
предыдущих состояний задним числом.
Каждая модель в проектирование базы данных используется при различных условиях постановки задачи и
имеет свои преимущества и недостатки.
В данной рассылке мы рассмотрим последовательную модель хранения истории изменения значений объектов на примере
справочника товаров, для которых необходимо хранить историю изменения цен во времени. Для начала создадим структуру
описания товаров:
// Создаем таблицу описания товаров
CREATE TABLE "DBA"."Product" (
"Product_id" integer NOT NULL DEFAULT autoincrement, // Код товара
"Name" varchar(50) NOT NULL, // Наименование
"ProductGroup_id" varchar(8) NOT NULL, // Код группы товара
PRIMARY KEY ( "Product_id" )
);
COMMENT ON TABLE "DBA"."Product" IS 'Справочник товаров';
// Создаем внешний ключ связи товаров и групп товаров
// с поддержкой каскадного удаления
ALTER TABLE "DBA"."Product"
ADD NOT NULL FOREIGN KEY "ProductGroup" ( "ProductGroup_id" )
REFERENCES "DBA"."ProductGroup" ( "ProductGroup_id" ) ON DELETE CASCADE;
// Создаем таблицу, хранящую историю изменения цен товаров
CREATE TABLE "DBA"."ProductValue" (
"Product_id" integer NOT NULL, // Код товара
"BeginDate" date NOT NULL, // Дата начала действия цены
"Value" numeric(12,2) NOT NULL, // Цена товара
PRIMARY KEY CLUSTERED ( "Product_id", "BeginDate" )
);
COMMENT ON TABLE "DBA"."ProductValue"
IS 'История изменения во времени цен на товары';
// Создаем внешний ключ связи истории товаров и самих товаров
// с поддержкой каскадного удаления
ALTER TABLE "DBA"."ProductValue"
ADD NOT NULL FOREIGN KEY "Product" ( "Product_id" )
REFERENCES "DBA"."Product" ( "Product_id" ) ON DELETE CASCADE;
|
Ранее, при создании параметров конфигурации мы ввели в конфигурацию параметр @@CalcDate,
обозначающий открытый для изменений расчетный месяц. Этот показатель очень часто используется
при написании учетных или бухгалтерских проектов. Смысл его в том, что пользователи имеют право
вводить и изменять информацию только в открытом расчетном месяце, но не имеют прав на изменение
информации в уже закрытых месяцах, на которые уже, к примеру, есть остатки или сальдо. Нам требуется
организовать систему контроля за ценами товаров, чтобы пользователь не мог изменять историю цен,
дата действия которых была бы меньше даты начала открытого расчетного периода. Для этой цели
на таблицу ProductValue целесообразней всего написать соотвествующие триггера:
CREATE TRIGGER "Valid_Insert_Update" BEFORE INSERT, UPDATE
ORDER 1 ON "DBA"."ProductValue"
REFERENCING NEW AS NewValue
FOR EACH ROW
WHEN( NewValue.BeginDate < @@CalcDate )
BEGIN
RAISERROR 20000 'Нельзя изменять цены товаров за закрытые периоды';
END;
CREATE TRIGGER "Valid_Delete" BEFORE DELETE
ORDER 1 ON "DBA"."ProductValue"
REFERENCING OLD AS OldValue
FOR EACH ROW
WHEN( OldValue.BeginDate < @@CalcDate )
BEGIN
RAISERROR 20000 'Нельзя удалять цены товаров за закрытые периоды';
END;
|
Теперь при любой попытке добавления, изменения или удаления цен товаров, дата действия которых
меньше текущего расчетного месяца, пользователю будет сообщаться об ошибке, а операция будет прервана.
Приведенная выше структура справочника товаров является нормализованной и вполне эффективно может
хранить товары и цены. Однако она не лишена недостатков, так как при использовании
такой схемы хранения истории значений достаточно сложно получать текущие цены
товара и периоды действия их цен. Для разрешения такой ситуации можно создать следующие
представления:
CREATE VIEW "DBA"."v_ProductValue_Current" AS
SELECT p.Product_id, p.Name, p.ProductGroup_id, v.BeginDate, v.Value
FROM Product p -- Товары
KEY JOIN ProductValue v -- Цены товаров
INNER JOIN ( -- Получаем последнюю дату действия для цен товаров
SELECT Product_id, Max(BeginDate) AS CurrentDate
FROM ProductValue
WHERE BeginDate <= @@CalcDateLast
GROUP BY Product_id
) AS l ON l.Product_id = v.Product_id AND l.CurrentDate = v.BeginDate;
COMMENT ON VIEW "DBA"."v_ProductValue_Current" IS 'Текущие цены на товары';
CREATE VIEW "DBA"."v_ProductValue_Period" AS
SELECT v.Product_id, v.BeginDate,
CONVERT(date, CASE
-- Если след. дата цены есть, то днем раньше было закрытие цены
WHEN l.LastDate IS NULL THEN NULL ELSE DateAdd(dd, -1, l.LastDate)
END) AS EndDate,
v.Value
FROM ProductValue v, -- Цены товаров
LATERAL ( -- Следующая дата действия цены товара
SELECT Min(BeginDate) as LastDate
FROM ProductValue
WHERE Product_id = v.Product_id AND BeginDate > v.BeginDate
) as l;
COMMENT ON VIEW "DBA"."v_ProductValue_Period" IS 'Периоды действия цен товаров';
|
Обратите внимание на запрос последнего представления. В нем используется новый вид соединения LATERAL,
появившийся в 9-ой версии ASA. Такой вид соединения позволяет организовывать полные соединения (FULL OUTER
JOIN) с подзапросами как внутренние, давая возможность указывать условия соединения внутри подзапроса. Такая форма соединения
по смыслу очень схожа с конструкцией EXISTS(SELECT ...), однако в отличие от нее она позволяет использовать поля
подзапроса в запросе, а не только накладывать фильтр на множество. Оптимизатор запросов всегда более эффективно обработает
LATERAL, чем внешние связи с подзапросом. Например, если переписать представление без использование LATERAL,
план запросов получится гораздо хуже (конечно при условии, что в таблице ProductValue много данных):
CREATE VIEW "DBA"."v_ProductValue_Period_ANSI" AS
SELECT v.Product_id, v.BeginDate,
CONVERT(date, CASE
-- Если след. дата цены есть, то днем раньше было закрытие цены
WHEN Min(l.BeginDate) IS NULL THEN NULL ELSE DateAdd(dd, -1, Min(l.BeginDate))
END) AS EndDate,
v.Value
FROM ProductValue v -- Цены товаров
LEFT JOIN ProductValue l -- Следующая дата действия цены товара
ON l.Product_id = v.Product_id AND l.BeginDate > v.BeginDate
GROUP BY v.Product_id, v.BeginDate, v.Value;
COMMENT ON VIEW "DBA"."v_ProductValue_Period_ANSI"
IS 'Периоды действия цен на товары через ANSISQL';
|
Для сравнения эффективности работы этих двух представлений Вам будет достаточно посмотреть на их планы запросов выполнения
и сравнить алгоритмы, которые в них использует ASA.
Созданные представления позволяют легко получить необходимую информацию по товарам, однако затрудняют
редактирование этого справочника в клиентских приложениях. Для облегчения просмотра и изменения
справочника товаров и его текущих действующих цен можно построить модель работы с данными справочником
через буфер, в качестве которого будет использована глобальная временная таблица.
На глобальные временные таблицы можно создавать триггеры. Такая функциональность позволяет
эмулировать instead-of триггеры MSSQL. Суть таких триггеров состоит в том, что они
описываются на таблицы или представления базы данных и вызываются при любых операциях добавления,
изменения или удаления данных в таблицу вместо проведения самих операций над данными БД. Какие реально
операции над данными будут проводиться, описываются непосредственно самим программистом в этих триггерах. Это дает прекрасную
возможность делать обновляемыми сложные представления, которые иначе были бы не обновляемыми и проводить
изменение данных в сложных структурах, скрытых от клиента.
Создадим глобальную временную таблицу, как буфер товаров и их текущих цен:
CREATE GLOBAL TEMPORARY TABLE "DBA"."XE_ProductValue" (
"Product_id" integer NOT NULL, // Код товара
"Name" varchar(50) NOT NULL, // Наименование
"ProductGroup_id" varchar(8) NOT NULL, // Код группы товара
"BeginDate" date NOT NULL, // Дата начала действия цены
"Value" numeric(12,2) NOT NULL, // Цена товара
PRIMARY KEY CLUSTERED ( "Product_id" )
) ON COMMIT PRESERVE ROWS;
COMMENT ON TABLE "DBA"."XE_ProductValue" IS 'Буфер товаров и их текущих цен';
|
Важным моментом в описании этой таблицы является опция ON COMMIT PRESERVE ROWS. Она указывает, что
эта таблица будет полноценно участвовать в транзакциях и после удачного завершения транзакции записи
будут в ней оставаться. Это идеально подходит для нашей цели. Если вместо этой опции указать ON COMMIT DELETE,
то после выполнения оператора COMMIT все записи в такой таблице будут удалены, а использование опции
NOT TRANSACTIONAL приведет к тому, что таблица не будет участвовать в транзакциях и откат изменений будет невозможен.
Для того, чтобы любые изменения буфера отображались на таблицах справочника товаров
необходимо написать соответствующие триггера на саму буферную таблицу:
// Создаем глобальный параметр признака заполнения буфера редактирования
INSERT INTO Config (Name, Level, TypeValue, Value, Description) ON EXISTING UPDATE
VALUES('@@Is_XE_Build', 0, 'tinyint', 0, 'Флаг признака построения вспомогательного буфера');
COMMIT;
CREATE TRIGGER "Save_Insert" BEFORE INSERT
ON XE_ProductValue
REFERENCING NEW AS NewValue
FOR EACH ROW
WHEN ( @@Is_XE_Build = 0 )
BEGIN
DECLARE @Product_id int;
// Заносим товар
INSERT INTO Product (Name, ProductGroup_id)
VALUES (NewValue.Name, NewValue.ProductGroup_id);
// Заносим полученный код товара в добавлямую запись
SET NewValue.Product_id = @@IDENTITY;
// Заносим цену товара
INSERT INTO ProductValue (Product_id, BeginDate, Value)
VALUES (NewValue.Product_id, NewValue.BeginDate, NewValue.Value);
END;
CREATE TRIGGER "Save_Update" AFTER UPDATE
OF Name, ProductGroup_id, BeginDate, Value
ON XE_ProductValue
REFERENCING NEW AS Inserted
FOR EACH STATEMENT
WHEN ( @@Is_XE_Build = 0 )
BEGIN
// Обновляем товары, если изменилось наименование
// или группа товара
IF UPDATE(Name) OR UPDATE(ProductGroup_id)
THEN
UPDATE Product p
SET Name = t.Name,
ProductGroup_id = t.ProductGroup_id
FROM Product p
INNER JOIN Inserted t on t.Product_id = p.Product_id;
END IF;
// Добавляем или обновляем цену товара, если она изменилась
IF UPDATE(Value) OR UPDATE(BeginDate)
THEN
INSERT INTO ProductValue (Product_id, BeginDate, Value) ON EXISTING UPDATE
SELECT Product_id, BeginDate, Value
FROM Inserted;
END IF;
END;
CREATE TRIGGER "Save_Delete" AFTER DELETE
ON XE_ProductValue
REFERENCING OLD AS Deleted
FOR EACH STATEMENT
WHEN ( @@Is_XE_Build = 0 )
BEGIN
// Удаляем товар
DELETE FROM Product
WHERE Product_id in (SELECT Product_id FROM Deleted);
END;
|
Для того, чтобы триггера срабатывали только при изменениях, проводимых клиентской частью и
игнорировались при начальном построении буфера, в проект вводиться новый глобальный
параметр @@Is_XE_Build. Сами триггера занимаются разброской измененной информации
по реальным таблицам товаров только в том случае, если флаг @@Is_XE_Build установлен в ноль.
Заметьте, что триггер на добавление вызывается позаписно как BEFORE, а остальные
на множество измененных записей как AFTER. Это сделано потому,
что при вставке записи в буферную таблицу необходимо обработать каждую вставляемую запись
и получить соответствующий код товара из таблицы Product. Для триггеров на изменение и
удаление товаров код товара уже известен и в целях повышения скорости гораздо выгоднее их
вызывать на все изменяемые или удаляемые записи сразу. В триггере на обновление записей
так же стоит ограничение на поля, описывая, при изменении значений каких полей этот триггер
должен вызываться. Функция UPDATE проверяет, было ли изменено поле, что позволяет в триггере
лишний раз не проводить операции над таблицами Product и ProductValue. Опция ON EXISTING UPDATE
в операторе INSERT позволяет указать ASA, что если записи с указанным первичным ключем еще нет,
то ее необходимо добавить, иначе же ее нужно будет обновить. Эта опция позволяет в более
краткой форме описать такое действие, чем например в ANSISQL, где нам пришлось бы сначала
проводить обновление оператором UPDATE существующих записей, а потом добавлять оператором INSERT
несуществующие записи.
Для того, чтобы организовать редактирование товаров клиентским приложением, необходимо
заполнить буфер текущими значениями справочника. Легче всего для этого написать хранимую
процедуру, которая занималась бы таким заполнением и возвращала набор данных буфера:
CREATE PROCEDURE "DBA"."sp_Build_Product" ()
BEGIN
// Устанавливаем флаг запрета работы триггеров на буферные таблицы
SET @@Is_XE_Build = 1;
// Очищаем буфер редактирования товаров
TRUNCATE TABLE XE_ProductValue;
// Вставляем в буфер редактирования товаров все товары и их текущие цены
INSERT INTO XE_ProductValue WITH AUTO NAME
SELECT *
FROM v_ProductValue_Current;
// Сбрасываем флаг запрета работы триггеров на буферные таблицы
SET @@Is_XE_Build = 0;
// Возвращаем клиенту буфер, чтобы он мог его редактировать
SELECT *
FROM XE_ProductValue;
END;
COMMENT ON PROCEDURE "DBA"."sp_Build_Product"
IS 'Построить буфер для редактирования товаров';
|
Заполним справочник товаров через полученный буфер:
// Вызываем инициализацию и заполнение буфера
CALL sp_Build_Product();
// Заносим в буфер товары
INSERT INTO XE_ProductValue (Name, ProductGroup_id, BeginDate, Value)
SELECT 'Бородинский', 'Хлеб', '20040101', 8.00
UNION ALL
SELECT 'Домик в деревне', 'Молоч', '20040101', 18.50
UNION ALL
SELECT 'Фен Фея', 'БытТех', '20040101', 150.00
UNION ALL
SELECT 'Рубин', 'ТВ', '20040101', 8000.00
UNION ALL
SELECT 'Электроника', 'ВМ', '20040101', 3500.00;
COMMIT;
// С 15 января 2004 года увеличиваем цену всех товаров на 10%
UPDATE XE_ProductValue
SET BeginDate = '20040115',
Value = Value * 1.10;
COMMIT;
|
Если теперь посмотреть на таблицы Product и ProductValue, то в них окажется заполненная через
буфер информация. Так как на таблицу ProductValue существуют триггера, контролирующие
правильность даты начала цен товаров, то любая попытка изменения информации в буфере
будет так же контролироваться этими триггерами. Правильное планирование действий триггеров
и каскадных операций позволяет централизованно описывать логику контроля и работы с данными.
Например, в нашем проекте нельзя удалить группу товаров, на которую существуют товары, с определенными
ранее текущего расчетного периода ценами.
В заключении я хотел бы заметить, что такая буферная модель не является панацеей от всех сложностей
реализации структуры и в основном годиться для редактирования небольших по обьему данных.
|
Большое
спасибо всем тем, кто принял участие в подготовке
рассылки: |
-
Наталья Алешина
(менеджер московского представительства Sybase)
-
Федор Корюгин
(программист)
-
Алексей Орлов
(программист) |
До встречи в следующей
рассылке, с уважением, ASCRUS.
|
Материалы данной
рассылки являются собственностью ее автора. При использовании информации
из рассылки, ссылка на автора
обязательна. |
|
