Том,
Мой вопрос касается таблиц, связанных отношением "главная-подчиненная", и хранения данных об изменениях в этих таблицах. Для ведения финансовой отчетности мы храним несколько иерархий отделов в одной таблице - это обеспечивает синхронизацию.
Таблицы имеют следующий вид:
create table t1 ( parent_id number, child_id number ) create table t2 ( object_id number, object_name varchar2(100), другие атрибуты ... )
Главная и подчиненная таблицы связаны внешним ключом.
Отдел может быть подчиненным для нескольких отделов (в разных иерархиях). Если главный отдел используется в нескольких иерархиях, у него должны быть одни и те же подчиненные.
Теперь вопрос:
Хотелось бы отслеживать историю всех изменений в иерархиях. Кроме того, для некоторых подсистем необходимо получать отчет об изменениях (изменения, произошедшие после определенной "даты"). Для придания большей значимости ведению истории изменений, мы хотели бы также сравнивать данные за предыдущие годы с текущим годом (на базе текущей или прежней иерархии). Не мог бы ты подсказать, как это реализовать?
Ответ Тома Кайта
Ну, вас наверняка заинтересует пакет для управления рабочим пространством (workspace manager). С его помощью можно добавить регистрацию версий для этих таблиц с помощью конструкции VIEW WO OVERWRITE (представление без перезаписи).
В результате, будет сохранена история всех изменений.
Затем можно использовать процедуру "gotoDate" для просмотра данных "как они были в то время". Аналог ретроспективного запроса , но возвращаться в прошлое можно сколь угодно далеко...
Рассмотрим следующий пример:
ops$tkyte@ORA920> CREATE USER wsmgmt IDENTIFIED BY wsmgmt;
User created.
ops$tkyte@ORA920> GRANT connect, resource, create table to wsmgmt;
Grant succeeded.
ops$tkyte@ORA920> grant execute on dbms_lock to wsmgmt;
Grant succeeded.
ops$tkyte@ORA920> begin
2 DBMS_WM.GrantSystemPriv
3 ('ACCESS_ANY_WORKSPACE, MERGE_ANY_WORKSPACE, ' //
4 'CREATE_ANY_WORKSPACE, REMOVE_ANY_WORKSPACE, ' //
5 'ROLLBACK_ANY_WORKSPACE', 'WSMGMT', 'YES');
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> connect scott/tiger
Connected.
ops$tkyte@ORA920> grant select on emp to wsmgmt;
Grant succeeded.
ops$tkyte@ORA920> grant select on dept to wsmgmt;
Grant succeeded.
ops$tkyte@ORA920> connect demo/demo
Connected.
ops$tkyte@ORA920> set echo off
Enter to continue
Теперь подключаемся как wsmgmt и настраиваем схему...
ops$tkyte@ORA920> @connect wsmgmt/wsmgmt wsmgmt@ORA920> set termout on wsmgmt@ORA920> create table emp as select * from scott.emp; Table created. wsmgmt@ORA920> create table dept as select * from scott.dept; Table created. wsmgmt@ORA920> alter table emp add constraint emp_pk primary key(empno); Table altered. wsmgmt@ORA920> alter table dept add constraint dept_pk primary key(deptno); Table altered. wsmgmt@ORA920> alter table emp add constraint emp_fk_dept foreign key(deptno) references dept(deptno); Table altered. wsmgmt@ORA920> alter table emp add constraint emp_fk_emp foreign key(mgr) references emp(empno); Table altered.
Итак, вот наша схема, реализующая иерархию (empno/mgr)
Добавим в таблицу поддержку версий. Задаем опцию VIEW_WO_OVERWRITE, что в представлении xxx_HIST будет содержаться полная информация об истории.
wsmgmt@ORA920> begin
2 DBMS_WM.EnableVersioning ('emp', 'VIEW_WO_OVERWRITE');
3 DBMS_WM.EnableVersioning ('dept', 'VIEW_WO_OVERWRITE');
4 end;
5 /
PL/SQL procedure successfully completed.
wsmgmt@ORA920> exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
wsmgmt@ORA920> column dt new_val starting_date
wsmgmt@ORA920> select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') dt from dual;
DT
--------------------
01-mar-2003 17:39:24
wsmgmt@ORA920> exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
Я собираюсь в дальнейшем просматривать таблицу как она есть "сейчас", поэтому я запоминаю время, соответствующее этому "сейчас".
wsmgmt@ORA920> update emp set sal = sal * 1.10; 14 rows updated. wsmgmt@ORA920> commit; Commit complete. wsmgmt@ORA920> select ename, sal, comm, workspace, type_of_change, 2 to_char(createtime,'dd-mon hh24:mi:ss') created, 3 to_char(retiretime,'dd-mon hh24:mi:ss') retired 4 from emp_hist 5 where ename = 'KING' 6 / ENAME SAL COMM WORKSPACE T CREATED RETIRED ---------- ---------- ---------- ------------- - --------------- --------- KING 5000 LIVE I 01-mar 17:39:15 01-mar 17:39:27 KING 5500 LIVE U 01-mar 17:39:27
Это просто демонстрирует, что история изменений записывается автоматически... Теперь, сотрудник BLAKE становится директором вместо KING, которого просто увольняют:
wsmgmt@ORA920> update emp 2 set mgr = ( select empno from emp where ename = 'BLAKE' ) 3 where mgr = ( select empno from emp where ename = 'KING' ); 3 rows updated. wsmgmt@ORA920> update emp 2 set mgr = null 3 where ename = 'BLAKE'; 1 row updated. wsmgmt@ORA920> delete from emp where ename = 'KING'; 1 row deleted. wsmgmt@ORA920> delete from dept where deptno = 40; 1 row deleted. wsmgmt@ORA920> commit; Commit complete.
Все правильно? Выполняем запрос и получаем:
wsmgmt@ORA920> select rpad('*',level*2,'*') // ename
2 from emp
3 start with mgr is null
4 connect by prior empno = mgr;
RPAD('*',LEVEL*2,'*')//ENAME
---------------------------------------------------------------------------------
--------------------------------------------------
**BLAKE
****ALLEN
****WARD
****MARTIN
****TURNER
****JAMES
****JONES
******SCOTT
********ADAMS
******FORD
********SMITH
****CLARK
******MILLER
13 rows selected.
wsmgmt@ORA920>
BLAKE - главный, но достаточно выполнить gotoDate и:
wsmgmt@ORA920> exec dbms_wm.gotoDate(to_date('&starting_date', 'dd-mon-yyyy hh24:mi:ss'));
PL/SQL procedure successfully completed.
wsmgmt@ORA920> select rpad('*',level*2,'*') // ename
2 from emp
3 start with mgr is null
4 connect by prior empno = mgr;
RPAD('*',LEVEL*2,'*')//ENAME
---------------------------------------------------------------------------------
--------------------------------------------------
**KING
****JONES
******SCOTT
********ADAMS
******FORD
********SMITH
****BLAKE
******ALLEN
******WARD
******MARTIN
******TURNER
******JAMES
****CLARK
******MILLER
14 rows selected.
wsmgmt@ORA920>
и можно увидеть, как оно "было" раньше.
Прочитайте вот это руководство
Не получается...
Я попытался использовать средства workspace manager, но наша схема (внешние ключи/уникальные ключи/отсутствие первичного ключа по таблице иерархии) не позволяет этого сделать. В нашей системе несколько больше связей, чем я описал...
Нельзя ли решить задачу с помощью простого SQL - что-то типа "Создать протоколирующий триггер и записывать все изменения в другую таблицу... и т.д.."
Ответ Тома Кайта
Может, время пересмотреть проект схемы, поскольку вы, кажется, еще находитесь в стадии ПРОЕКТИРОВАНИЯ...
Да, можно все запрограммировать самому. Я за вас этого делать не буду - для этого надо много времени потратить, кроме того, как вы наверное догадываетесь, код будет нетривилаьным. Использование уникальных ключей и т.п. особенности делают решение этой задачи ДЕЙСТВИТЕЛЬНО сложным.
У вас есть выбор - использовать стандартную возможность (измените правила в соответствии с возможностями ПО) или делать все самому.
А как это делали раньше?
Спасибо. Я понимаю, что мне придется все делать самому... Изменение проекта - может, и вариант, но я не знаю, как реализовать все требования без уникальных ключей... Кстати, а как такие задачи решались до появления средств WM?
Ответ Тома Кайта
До появления WM они никак не решались -- разве что путем написания соответствующего кода любой степени сложности. Мне по-прежнему кажется, что вы находитесь на этапе ПРОЕКТИРОВАНИЯ, так что речь идет даже не об изменении проекта, а об изменении принятых подходов...
"Проектирование" когда-нибудь заканчивается?
Ответ Тома Кайта
Да, но вы определенно находитесь еще на этом этапе. Вы спрашиваете:
Теперь вопрос:
Хотелось бы отслеживать историю всех изменений в иерархиях. Кроме того, для некоторых подсистем необходимо получать отчет об изменениях (изменения, произошедшие после определенной "даты"). Для придания большей значимости ведению истории изменений, мы хотели бы также сравнивать данные за предыдущие годы с текущим годом (на базе текущей или прежней иерархии)...
Это не оставляет никаких сомнений - вы еще только "проектируете" систему.
Комментарий читателя от 2 марта 2003 года
Впервые увидел пример использования Workspace Manager. Замечательное средство!
Пара вопросов:
1) Зачем используется вызов DBMS_LOCK
wsmgmt@ORA920> exec dbms_lock.sleep(2);
2) Наше приложение отностися к категории ООТ (OLTP). Где мы могли бы использовать это средство в таком приложении? (Зачем, например, сохранять несколько версий данных, если уже включен аудит?)
Ответ Тома Кайта
1) Поскольку значение DATE задается с точностью до секунды, а сервер работает так быстро, что если не сделать умышленную паузу нельзя гарантировать возможность "вернуться назад"! Я умышленно сделал так, чтобы между двумя событиями прошло определенное время.
2) На этот вопрос можете ответить только вы сами. Где надо это средство использовать... Определенные требования должны привести вас к необходимости его использования. Если стандартного аудита достаточно - отлично. А вот если нет, а это средство позволяет реализовать требования, вот тогда и используйте его ;)
И что это за объекты созданы в схеме?
Я выполнил твой пример и посмотрел, какие объекты созданы в схеме wsmgt. Там оказалась куча представлений и два материализованных представления. Таблицы с именами DEPT_AUX, DEPT_LT, EMP_AUC, EMP_LT... Зачем все этой?
Ответ Тома Кайта
Вы читали руководство, которое я предалагл "прочитать" ;) Это руководство по основным концепциям управления рабочим пространством, и в нем все это описано: что делается, как и почему...
Комментарий читателя от 24 мая 2003 года
Прекрасное объяснение. Оно сократило мне время разработки минимум на 60%.
Хотелось бы знать, как после перехода к определенному времени в прошлом, например, с помощью dbms_wm.gotodate(sysdate-10), вернуться к текущему моменту времени? Что, dbms_wm.gotodate(sysdate) - единственный способ, или можно иначе?
Ответ Тома Кайта
Надо просто выполнить gotoworkspace еще раз - при этом контекст сбрасывается. Установка момента времени sysdate не поможет, поскольку просто заморозит состояние на другой момент времени.
Например:
wsmgmt@ORA920> begin
2 DBMS_WM.GotoWorkspace ('ws1');
3 end;
4 /
PL/SQL procedure successfully completed.
wsmgmt@ORA920> column dt new_val D
wsmgmt@ORA920> select to_char(sysdate,'yyyymmddhh24miss') dt from dual;
DT
--------------
20030525095813
wsmgmt@ORA920> update emp set sal = sal * 1.50;
14 rows updated.
wsmgmt@ORA920> commit;
Commit complete.
wsmgmt@ORA920> exec dbms_lock.sleep(5);
PL/SQL procedure successfully completed.
wsmgmt@ORA920> select ename, sal
2 from emp
3 where ename = 'KING'
4 /
ENAME SAL
---------- ----------
KING 7500 <<<<<<====== текущее значение
wsmgmt@ORA920> exec dbms_wm.gotoDate(to_date(&D, 'yyyymmddhh24miss'));
PL/SQL procedure successfully completed.
wsmgmt@ORA920> select ename, sal
2 from emp
3 where ename = 'KING'
4 /
ENAME SAL
---------- ----------
KING 5000 <<<<<<====== значение до изменения
wsmgmt@ORA920> exec dbms_wm.gotoWorkSpace('ws1');
PL/SQL procedure successfully completed.
wsmgmt@ORA920> select ename, sal
2 from emp
3 where ename = 'KING'
4 /
ENAME SAL
---------- ----------
KING 7500 <<<<<<====== результат изменения опять виден
wsmgmt@ORA920>
Требует ли поддержка версий дополнительного места на диске?
Да, Oracle становится становится все более лидирующей по возможностям СУБД... Остается один вопрос. Мне интересно, как разработчики СУБД хранят в базе эту информацию о версиях? Вы что, храните где-то только метаданные об изменениях?
Кажется в какой-то базе я видел схему Workspace, хотя и не просматривал ее... Это в ней вся информация хранится? Документацию я еще почитаю... Просто хочется в принципе понять, как реализовано управление рабочим пространством...
Ответ Тома Кайта
Все сделано так, как вы и сами бы сделали для одной таблицы. Для строк есть даты повления/исчезновения.
При изменении строки, фактически, изменяется временная отметка (дата исчезновения) строки и вставляется новая строка.
При удалении строки, фактически, изменяется временная отметка (дата исчезновения) строки. При включении регистрации всех версий будет сохраняться каждое "воплощение" строки. Можно также создавать "точки сохранения" - сохраняются только "воплощения", достаточные, чтобы вернуться в точки сохранения, а промежуточные изменения не сохраняются.
Несколько вопросов о Workspace Manager
- Я добавил поддержку версий для таблицы emp. Я не хочу, чтобы в таблице истории были дублирующиеся записи. Но когда я выпаолняю UPDATE emp SET sal=sal , в таблицу истории запись вставляется, хотя значение sal не изменилось. Нет ли способа избежать вставки этих дублирующихся записей?
- При добавлении поддержки версий одной таблицы создается 14 дополнительных таблиц. А если добавить поддержку версий для n таблиц?. В схеме окажется n * 14 дополнительных таблиц, которые займут немало места на диске. Та что эту возможность не все смогут использовать.
- Oracle создает триггеры для ведения истории изменений. А что, если я хочу создать собственные тригеры для тех же событий, но для других целей?
- Нет ли одной команды, включающей поддержку версий для всех таблиц в схеме?
Ответ Тома Кайта
- С нашей (да и с ЛЮБОЙ) точки зрения, вы выполнили изменение. Так что - нет.
- Вы смотрели, какие объекты создаются? Вы удивитесь, обнаружив, что "это же не таблицы, а нечто, похожее на таблицы - представления".
- Ну и создавайте...
- В принципе, да: команда начинается с begin и заканчивается end...
begin for x in ( select * from user_tables ) loop ... end loop; end; /
(это одна команда ;)
Допускает ли WM исторические запросы?
Что-то типа:
select max(sal) from emp where ename = 'KING' and last_modified between '2000-JUNE-1' and '2003-JUNE-1'
Ответ Тома Кайта
Вы можете строить запросы к представлению EMP_HIST, так что -- да.