클라우드 Mac에서 iOS SQLite 감사 트리거 검증하기

Windows에서 업무 기능을 개발한 뒤 클라우드 Mac으로 옮겨 iOS 빌드를 만들면, 데이터베이스 업데이트는 정상처럼 보여도 감사 테이블에는 기록이 중복되거나 누락될 수 있습니다. 롤백 후 화면에 잘못된 상태가 표시되기도 합니다. 전체 빌드 인수 테스트 단계까지 기다렸다가 원인을 찾기보다는, 먼저 SQLite 트리거를 실행 가능한 최소 테스트 픽스처로 분리해 검증하고 애플리케이션이 실제로 사용하는 연결에서도 같은 테스트를 다시 실행하는 편이 낫습니다.

감사 기록의 의미부터 정의하기

이 글에서 기록할 대상은 “커밋이 완료된 제목 변경”입니다. 사용자가 저장 버튼을 누를 때마다 기록하는 것이 아닙니다. 같은 값을 다시 써도 로그가 추가되면 안 되고, 문자열이 NULL로 바뀌면 기록해야 합니다. 업무 트랜잭션을 롤백하면 로그도 함께 롤백되어야 합니다. 먼저 이 세 가지 조건을 계약으로 명시한 다음 트리거를 구현합니다.

클라우드 Mac에 sqlite3 명령줄 도구를 준비하고 sqlite3 --version을 실행해 버전을 기록합니다. 애플리케이션에 데이터베이스 라이브러리가 내장되어 있다면 애플리케이션 연결에서도 SELECT sqlite_version()을 조회해야 합니다. 명령줄 도구의 버전이 iOS에서 실제로 실행되는 버전을 나타내지는 않습니다.

트리거는 업무 데이터 쓰기와 동일한 트랜잭션 안에서 일반 감사 테이블에 기록합니다. 커밋된 변경을 추적하는 데는 적합하지만, 실패한 시도를 별도로 보존하는 용도로는 적합하지 않습니다. 변조 방지 기능을 갖춘 보안 감사 수단도 아닙니다.

테스트에는 인위적으로 만든 레코드만 사용하고 운영 데이터베이스를 복사하지 않습니다. 감사 테이블의 이전 값과 새 값에는 사용자 콘텐츠가 포함될 수 있으므로, 문제 해결이 편하다는 이유로 무기한 보관해서는 안 됩니다.

오류가 나면 즉시 실패하는 SQL 픽스처 만들기

아래 내용을 trigger-check.sql로 저장합니다. 임시 검증 테이블은 제약 조건을 이용해 잘못된 로그 개수를 SQL 오류로 바꿉니다. 쿼리 결과만 출력하고 종료 코드를 확인하지 않으면 통과 여부를 판정하는 검증 관문으로 사용할 수 없습니다.

.bail on
PRAGMA recursive_triggers = OFF;

CREATE TABLE note (
    id INTEGER PRIMARY KEY,
    title TEXT
);

CREATE TABLE note_audit (
    seq INTEGER PRIMARY KEY,
    note_id INTEGER NOT NULL,
    old_title TEXT,
    new_title TEXT
);

CREATE TRIGGER note_title_audit
AFTER UPDATE OF title ON note
WHEN OLD.title IS NOT NEW.title
BEGIN
    INSERT INTO note_audit(note_id, old_title, new_title)
    VALUES(NEW.id, OLD.title, NEW.title);
END;

CREATE TEMP TABLE assertion (
    ok INTEGER NOT NULL CHECK(ok = 1)
);

INSERT INTO note VALUES(1, 'draft');
UPDATE note SET title = title WHERE id = 1;
INSERT INTO assertion
SELECT count(*) = 0 FROM note_audit;

UPDATE note SET title = NULL WHERE id = 1;
INSERT INTO assertion
SELECT count(*) = 1 FROM note_audit;
INSERT INTO assertion
SELECT old_title IS 'draft' AND new_title IS NULL
FROM note_audit WHERE seq = 1;

UPDATE note SET title = NULL WHERE id = 1;
INSERT INTO assertion
SELECT count(*) = 1 FROM note_audit;

UPDATE note SET title = 'ready' WHERE id = 1;
INSERT INTO assertion
SELECT count(*) = 2 FROM note_audit;

BEGIN;
UPDATE note SET title = 'temporary' WHERE id = 1;
ROLLBACK;

INSERT INTO assertion
SELECT title IS 'ready' FROM note WHERE id = 1;
INSERT INTO assertion
SELECT count(*) = 2 FROM note_audit;

sqlite3 :memory: < trigger-check.sql을 실행합니다. 성공하면 아무 출력이 없어도 되지만, 실패하면 0이 아닌 종료 코드가 반환되어야 합니다. 점으로 시작하는 명령은 명령줄 셸 전용이므로 애플리케이션의 데이터베이스 실행 인터페이스에 그대로 전달하면 안 됩니다.

이 예제의 핵심은 IS NOT입니다. 이 연산자는 NULL과 NULL이 아닌 값을 구분할 수 있습니다. !=로 바꾸면 NULL이 포함된 비교가 참으로 평가되지 않아 로그가 누락될 수 있습니다.

반례로 검증 관문이 실제로 작동하는지 확인하기

먼저 WHEN 조건을 삭제하고 다시 실행합니다. 값이 바뀌지 않는 첫 번째 업데이트에서 바로 검증에 실패해야 합니다. 다음으로 조건을 OLD.title != NEW.title로 바꾸면 NULL 전환 테스트가 실패해야 합니다. 두 오류를 모두 잡아내지 못한다면 스크립트가 종료 코드를 무시하는지, 실제로는 이전 파일을 계속 실행하고 있는지 확인합니다.

UPDATE OF의 열 이름도 주의해야 합니다. SQLite는 여기에 존재하지 않는 열 이름이 들어가도 허용하며, 오타가 있다는 이유만으로 트리거 생성을 거부하지 않습니다. 예제의 title을 잠시 titel로 바꿔 보세요. 실제로 제목을 변경했을 때 로그가 남지 않아 테스트가 실패해야 합니다.

트리거 개수만 확인해서는 안 됩니다. sqlite_schema에서 실제 정의를 읽고 PRAGMA table_info(note)로 열을 대조한 뒤, 감사 대상 필드마다 실제 값이 바뀌는 업데이트를 최소 한 번씩 실행해야 합니다. 그래야 “존재하지만 작동하지 않는” 경우까지 검증할 수 있습니다.

재귀와 다중 행 업데이트까지 인수 테스트에 포함하기

현재 트리거는 감사 테이블에만 기록하고 note를 다시 수정하지 않으므로 자기 재귀에 의존하지 않습니다. 재귀 설정이 OFF일 때와 ON일 때 각각 픽스처를 실행하고 결과가 같은지 확인합니다. 애플리케이션 연결을 초기화할 때는 도구의 기본값에 의존하지 말고 이 설정을 명시적으로 지정한 뒤 다시 조회합니다.

나중에 “업데이트 후 제목을 보정하는” 트리거를 추가하면 원본 테이블에 다시 쓰는 과정에서 재귀가 발생하거나 로그가 추가로 남을 수 있습니다. 재귀 깊이를 조정해 문제를 가리지 말고, 정규화를 애플리케이션에서 쓰기 전에 수행할지 아니면 하나의 트리거가 전담할지 먼저 정해야 합니다.

서로 다른 기본 키를 가진 레코드도 여러 개 삽입한 다음, 하나의 업데이트 문으로 모든 제목을 변경합니다. SQLite 트리거는 행마다 실행되므로 로그의 일련번호가 업무 처리 순서를 나타낸다고 가정해서는 안 됩니다. 각 기본 키에 해당하는 로그 내용을 확인해야 합니다. 빈 문자열, NULL, 조건에 맞는 레코드가 하나도 없는 경우도 각각 테스트해야 합니다.

iOS 연결에서 마지막 검증 마치기

명령줄 픽스처가 통과하면 테이블 생성과 데이터 조작을 데이터베이스 통합 테스트의 개별 단계로 옮기고, 프로젝트에서 실제로 사용하는 데이터베이스 래퍼와 트랜잭션 인터페이스를 사용합니다. 실행할 때마다 반환 코드를 확인하고, 제약 조건 오류를 빈 결과로 바꾸지 않습니다. 예상한 실패가 발생한 뒤에도 해당 연결에서 쿼리를 계속 실행할 수 있는지 확인해야 합니다.

마지막으로 감사 로그 쓰기를 의도적으로 실패시키는 테스트를 추가합니다. 전용 테스트 데이터베이스에 특정 새 값을 거부하는 제약 조건을 추가한 다음 업무 테이블을 업데이트합니다. 업데이트가 오류를 반환하고 업무 데이터와 로그 어느 쪽도 부분적으로 커밋되지 않았는지 확인합니다. 이후 호출자가 트랜잭션을 명시적으로 종료하거나 롤백해야 합니다. 모든 오류가 트랜잭션 전체를 자동으로 롤백한다고 가정해서는 안 됩니다.

인계할 때는 픽스처, 실제 트리거 정의, 양쪽 환경의 데이터베이스 버전, 실패 테스트 결과를 함께 보관합니다. 대여 기간 안에 이 검증을 마쳐 두면 다음에 장비를 바꿀 때도 같은 계약 테스트만 다시 실행하면 됩니다. “저장 버튼을 눌렀는데 오류가 없었다”는 이유만으로 감사 기록의 신뢰성을 판단할 필요가 없습니다.

자주 묻는 질문