[250302] Developing Time-Oriented Applications in SQL

1. 추적 로그의 역할
2. 추적 로그는 단일 테이블에 적용된 수정 작업들의 순서를 기록한다.
3. 이 로그는 모니터링 대상 테이블을 과거 특정 시점으로 복원할 수 있게 해준다.
4. 이를 통해 실수로 이루어진 수정(예: 부적절한 업데이트나 삭제)을 취소하거나, 테이블을 이전의 일관된 상태로 복원할 수 있다.
5. 또한, 추적 로그는 비공식 또는 공식 감사 절차에도 활용될 수 있다.
6. 추적 로그의 구성
7. 추적 로그는 모니터링 대상 테이블의 열(column)과 함께 타임스탬프 열(timestamp column, 단일 날짜/시간 값으로 수정 시점을 지정)을 포함한다.
8. 로그는 수정이 발생한 시점(수정이 언제 발생했는지)과 때로는 작업 코드(operation code, 삽입/삭제/업데이트)를 포함한다.
9. 로그는 트랜잭션 식별자(transaction identifier)와 트리거(triggers)를 사용하여 유지될 수 있다. 이를 통해 애플리케이션 코드 자체를 변경하지 않고도 로그를 관리할 수 있다.
10. 로그 데이터의 종류
11. 추적 로그는 변경 전(before-images, 변경 전 행 상태)과 변경 후(after-images, 변경 후 행 상태), 또는 이 둘의 조합을 포함할 수 있다.
12. 추적 로그의 조직화
13. 추적 로그의 다양한 조직 방식은 모니터링 대상 테이블에 대한 가정과 제약 조건에 따라 달라진다. 이 로그는 재구성(reconstruction) 알고리즘과 밀접하게 연관되어 있다.
14. 테이블 재구성을 위한 정확한 트랜잭션 의미론(semantics)을 달성하려면 다양한 제한 조건이 존재하며, 모니터링 대상 로그가 어떻게 수정되는지에 따라 재구성 알고리즘이 크게 달라질 수 있다.
15. 복잡성
16. 재구성된 테이블의 정확한 의미론을 달성하는 것은 놀라울 정도로 어렵다.
17. 모니터링 대상 로그 수정 방식에 따라 재구성 알고리즘이 단순히 복잡해질 뿐만 아니라, 때로는 극단적으로 달라질 수 있다.

이 내용은 데이터베이스에서 변경 추적 및 이력 관리의 중요성을 강조하며, 추적 로그를 효과적으로 구성하고 활용하는 방법을 설명한다. 특히, 데이터 무결성을 유지하고 실수 수정이나 감사 절차를 지원하는 데 초점을 맞춘다.

"CHAPTER EIGHT: RETAINING A TRACKING LOG"에서 Nigel Corbin은 Schlumberger의 GeoQuest 부서에서 일하는 기술 지원 담당자로, 한 클라이언트로부터 북해 유정의 위치가 태평양으로 잘못 표시되었다는 보고를 받는다. 이는 FINDER 시스템의 디지털 지도에서 발생한 문제로, 유정 좌표는 정확했으나 `PROJECTIONS` 테이블에서 투영(projection) 코드를 잘못 수정(ill-advised update)해 단위 해석이 잘못된 것이 원인이었다. 이 작은 테이블(수십 개 행)을 복원하는 데 많은 시간이 걸렸다.

문제를 해결한 후, Nigel은 테이블에 어떤 변경이 이루어졌는지, 그 이유를 파악해야 한다고 판단했다. 테이블 업데이트를 금지하는 것도 고려했으나, 클라이언트는 유효한 수정이 필요할 수 있어 이를 받아들일 수 없었다. 결국 비슷한 문제가 재발할 경우를 대비해 이 테이블에 추적 로그(tracking log)를 도입하는 것이 필요하다고 결론지었다. 이 사례는 데이터베이스에서 추적 로그의 중요성을 강조하며, 실수 수정이나 일관성 문제를 해결하기 위한 실무적 필요성을 보여준다.

1. 추적 로그의 정의 및 역할
   • 추적 로그는 단일 테이블(모니터링 대상 테이블, monitored table)에 적용된 수정 작업들의 순서를 기록한다. 이 로그는 수정이 발생했는지, 관련 데이터, 수정 시점, 수정한 사람, 작업 또는 트랜잭션 등 추가 정보를 포함한다.
   • 추적 로그는 모니터링 대상 테이블의 과거 특정 시점으로 복원(reconstruct)할 수 있게 해준다.
   • 이 로그는 모니터링 대상 테이블에 영향을 주지 않으면서 과거 상태를 유지할 수 있다.
2. 추적 로그와 유효 시간 테이블(valid-time table)의 차이
   • 추적 로그는 이전 장에서 다룬 유효 시간 테이블과 다르다. 유효 시간 테이블은 기업의 상태를 시간에 따라 모델링하지만, 추적 로그는 수정된 테이블 자체의 상태 변화를 시간에 따라 캡처한다.

이 섹션에서는 PROJECTIONS 테이블에 대한 추적 로그를 저장할 새 테이블 P_Log 를 정의한다.

#+BEGIN_EXAMPLE sql CREATE TABLE P_Log ( PROJECTION_ID INT, PROJECTION_NAME CHAR(10), PROJECTION_TYPE INT, SPHEROID_CODE INT, PROJECTION_UOM INT, ZONE_CODE INT, When_Changed DATE, PRIMARY KEY (PROJECTION_ID, When_Changed) );

마지막 열을 제외한 모든 열은 PROJECTION에서 가져온 열이다.

1. 추적 로그의 구성 및 역할
   • 추적 로그는 모니터링 대상 테이블(PROJECTIONS 테이블)에 적용된 수정 작업의 순서를 기록한다.
   • 이 로그는 수정 사실, 관련 데이터, 수정 시점(When_Changed 열), 그리고 수정된 내용을 포함한다.
   • When_Changed 열 값은 "now"(현재)로 제한되며, 각 프로젝션이 하루에 한 번만 업데이트된다고 가정한다(나중에 이 가정이 완화됨).
   • 추적 로그는 모니터링 대상 테이블의 과거 상태를 재구성할 수 있게 하며, 유효 시간 테이블(valid-time table)과 달리 수정된 테이블 자체의 상태 변화를 시간에 따라 캡처한다.
2. 추적 로그 유지용 트리거
   • P_Log 테이블은 트리거를 사용해 자동으로 유지되며, 애플리케이션 코드 변경 없이도 작동한다.
   • 코드 조각 8.2는 PROJECTIONS 테이블에 대한 삭제(DELETE)와 업데이트(UPDATE) 작업을 추적하기 위한 두 개의 트리거를 정의한다:
     • Delete_Projections: PROJECTIONS 테이블에서 행이 삭제될 때, 삭제된 행의 이전 값(OLD 값)을 P_Log=에 삽입한다. 예: =OLD.PROJECTION_ID, OLD.PROJECTION_NAME 등을 =CURRENT_DATE=와 함께 기록.
     • Update_Projections: PROJECTIONS 테이블에서 행이 업데이트될 때, 업데이트 전의 이전 값(OLD 값)을 P_Log=에 삽입한다. 예: =OLD.PROJECTION_ID, OLD.PROJECTION_NAME 등을 =CURRENT_DATE=와 함께 기록.

삽입(INSERT) 트리거는 필요하지 않다. 삽입된 값은 모니터링 테이블 자체에서 확인할 수 있기 때문이다(나중에 이 가정이 다시 다룸).

1. 트리거의 특징 및 이점
   • 트리거는 INSERT, DELETE, UPDATE 문을 통해만 P_Log 테이블이 수정되도록 보장한다. 기존 PROJECTIONS 테이블에 이미 트리거가 있거나 DBMS가 한 테이블에 하나의 트리거만 허용한다면, 새 트리거는 기존 트리거와 병합해야 한다.
   • 별도의 P_Log 테이블을 사용하면 PROJECTIONS 테이블과 이를 사용하는 기존 코드는 그대로 유지되며, FINDER 같은 복잡한 시스템에서 코드 수정 없이 추적 기능을 추가할 수 있다. 이는 Nigel이 추적 로그 도입을 쉽게 수용할 수 있는 이유다.
   • 추적 로그는 모니터링 대상 테이블 수정의 부수 효과(side effect)로 유지되며, 기존 코드에 영향을 미치지 않는다.

-- 코드 조각 8.2 Tiggers for maintaining the P_Log table.
CREATE TRIGGER Delete_Projections
AFTER DELETE ON PROJECTIONS FOR EACH ROW
INSERT INTO P_Log VALUES
(OLD.PROJECTION_ID,
 OLD.PROJECTION_NAME,
 OLD.PROJECTION_TYPE,
 OLD.SPHEROID_CODE,
 OLD.PROJECTION_UOM,
 OLD.ZONE_CODE,
 CURRENT_DATE)

CREATE TRIGGER Update_Projections
AFTER UPDATE ON PROJECTIONS FOR EACH ROW
INSERT INTO P_Log VALUES (OLD.PROJECTION_ID,
 OLD.PROJECTION_NAME,
 OLD.PROJECTION_TYPE,
 OLD.SPHEROID_CODE,
 OLD.PROJECTION_UOM,
 OLD.ZONE_CODE,
 CURRENT_DATE)