Історія змін по ієрархічним даними (workspace management), Інші СУБД, Бази даних, статті

Том,


Моє питання стосується таблиць, пов’язаних відношенням “головна-підпорядкована”, і зберігання даних про зміни в цих таблицях. Для ведення фінансової звітності ми зберігаємо кілька ієрархій відділів в одній таблиці – Це забезпечує синхронізацію.


Таблиці мають наступний вигляд:

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



  1. Я додав підтримку версій для таблиці emp. Я не хочу, щоб в таблиці історії були дубльовані записи. Але коли я випаолняю UPDATE emp SET sal=sal , В таблицю історії запис вставляється, хоча значення sal не змінилося. Чи немає способу уникнути вставки цих дубльованих записів?
  2. При додаванні підтримки версій однієї таблиці створюється 14 додаткових таблиць. А якщо додати підтримку версій для n таблиць?. У схемі виявиться n * 14 додаткових таблиць, які займуть чимало місця на диску. Та що цю можливість не всі зможуть використовувати.
  3. Oracle створює тригери для ведення історії змін. А що, якщо я хочу створити власні тригера для тих же подій, але для інших цілей?
  4. Чи немає однієї команди, що включає підтримку версій для всіх таблиць в схемі?

Відповідь Тома Кайта



  1. З нашої (та й з БУДЬ) точки зору, ви виконали зміна. Так що – ні.
  2. Ви дивилися, які об’єкти створюються? Ви здивуєтеся, виявивши, що “це ж не таблиці, а щось, схоже на таблиці – подання“.
  3. Ну і створюйте …
  4. В принципі, так: команда починається з 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, Так що – так.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*