SQLITE_DBPAGE 가상 테이블

SQLITE_DBPAGE 가상 테이블

SQLITE_DBPAGE 확장은 데이터베이스 파일의 각 페이지에 직접 접근할 수 있게 해주는 eponymous-only 가상 테이블이에요. pager 계층을 통해 파일을 읽고 쓸 수 있기 때문에 모든 변경이 트랜잭션으로 처리되는 것이 특징이에요.

출처: SQLITE_DBPAGE Virtual Table

본문

SQLITE_DBPAGE 는 데이터베이스 파일을 페이지 단위로 직접 읽고 쓸 수 있게 해주는 확장 기능이에요. pager 계층과 상호작용하므로 모든 변경 사항이 트랜잭션으로 안전하게 처리되어요.

경고: SQLITE_DBPAGE 가상 테이블에 쓰는 작업은 아주 쉽게 복구할 수 없는 데이터베이스 손상을 일으킬 수 있어요. 신뢰할 수 없는 구성 요소가 SQLITE_DBPAGE 테이블에 접근하지 못하게 하세요. 사용할 때는 각별히 주의해야 해요. 실험하기 전에 중요 데이터를 백업해 두는 것이 좋아요. SQLITE_DBCONFIG_DEFENSIVE 플래그가 설정되어 있으면 SQLITE_DBPAGE 가상 테이블에 대한 쓰기가 비활성화되어요.

SQLITE_DBPAGE 확장은 amalgamation 에 포함되어 있지만 기본적으로는 비활성화되어 있어요. 활성화하려면 SQLITE_ENABLE_DBPAGE_VTAB 컴파일 타임 옵션을 사용해야 해요. 이 확장은 공개되지 않은 내부 인터페이스를 사용하므로 런타임에 로드할 수 없어요. 애플리케이션에 추가하는 유일한 방법은 SQLITE_ENABLE_DBPAGE_VTAB 컴파일 타임 옵션으로 컴파일하는 것이에요.

명령줄 셸(shell)의 기본 빌드에서는 이 확장이 활성화되어 있지만 기본적으로 읽기 전용이에요. CLI 에서 sqlite_dbpage 가상 테이블에 쓰기를 허용하려면 --unsafe-testing 명령줄 옵션을 사용해야 해요.

1. 개요 (Overview)

SQLITE_DBPAGE 가상 테이블은 페이지 단위로 기본 디스크 파일에 직접 접근할 수 있게 해주는 읽기/쓰기 테이블이에요. 이 가상 테이블은 대략 다음과 같은 스키마를 가지는 것처럼 보여요:

CREATE TABLE sqlite_dbpage(
  pgno INTEGER PRIMARY KEY,
  data BLOB
);

SQLite 데이터베이스 파일은 페이지로 나뉘어져 있어요. 첫 번째 페이지는 1, 두 번째 페이지는 2, 이런 식으로 번호가 붙어요. 0 페이지는 존재하지 않아요. 모든 페이지의 크기는 같고, 512 에서 65536 사이의 2 의 거듭제곱 값이에요. 자세한 내용은 파일 형식 문서를 참고하세요.

SQLITE_DBPAGE 테이블을 사용하면 데이터베이스 파일의 각 페이지에 있는 원시 바이너리 내용을 조회하거나 교체할 수 있어요. 페이지 내용을 해석하려는 시도는 하지 않아요. 디스크에 나타나는 그대로 바이트 단위로 반환해요.

SQLITE_DBPAGE 테이블은 데이터베이스 파일의 각 페이지마다 하나의 행을 가져요. SELECT 문으로 페이지를 읽거나, UPDATE 또는 INSERT 문으로 페이지를 덮어쓸 수 있어요. sqlite_dbpage 에 INSERT 를 사용할 때는 REPLACE 처럼 동작해요. 즉, 이미 존재하는 페이지에 INSERT 하면 그 페이지를 덮어써요. 데이터베이스 크기를 늘리려면 데이터베이스 끝을 넘어선 페이지에 INSERT 하면 돼요.

1.1. ATTACH 된 데이터베이스에서 SQLITE_DBPAGE 사용하기

위에 보여드린 SQLITE_DBPAGE 테이블 스키마는 불완전해요. 어떤 ATTACH 된 데이터베이스를 읽거나 쓸지 결정하는 "schema" 라는 세 번째 숨은 컬럼이 있어요. "schema" 컬럼은 숨은 컬럼이므로, SQLITE_DBPAGE 를 테이블-값 함수(table-valued function)로 호출할 때 매개변수로 사용할 수 있어요.

예를 들어, 다음과 같은 문장으로 데이터베이스 연결에 추가 데이터베이스를 ATTACH 했다고 가정해 볼게요:

ATTACH 'auxdata1.db' AS aux1;

그런 다음 그 데이터베이스 파일의 첫 번째 페이지를 읽으려면 이렇게 하면 돼요:

SELECT data FROM sqlite_dbpage('aux1') WHERE pgno=1;

"schema" 를 생략하면 기본 데이터베이스(보통 'main' 이라고 부르며, SQLITE_DBCONFIG_MAINDBNAME 으로 이름을 바꾸지 않았다면)로 기본 설정돼요. 따라서 다음 두 쿼리는 보통 동일해요:

SELECT data FROM sqlite_dbpage('main') WHERE pgno=1;
SELECT data FROM sqlite_dbpage WHERE pgno=1;

SQLITE_DBPAGE 테이블은 다른 테이블처럼 조인에도 참여할 수 있어요. 연결된 모든 데이터베이스 파일의 첫 번째 페이지 내용을 보려면 다음과 같은 문장을 실행하면 돼요:

SELECT dbpage.data, dblist.name
  FROM pragma_database_list AS dblist
  JOIN sqlite_dbpage(dblist.name) AS dbpage
 WHERE dbpage.pgno=1;

1.2. 데이터베이스 잘라내기 (Truncating)

보통 UPDATE 와 INSERT 의 데이터는 데이터베이스 페이지와 정확히 같은 크기의 blob 이어야 해요. 하지만 특별한 경우로, COMMIT 직전 마지막 작업으로 페이지 2 이상에 NULL 을 INSERT 하면, 그 페이지와 이후의 모든 페이지가 삭제되면서 데이터베이스가 잘려요. 예를 들어 "INSERT INTO sqlite_dbpage(pgno,data) VALUES(52,NULL)" 같은 명령은 데이터베이스를 51 페이지 길이로 잘라내요. 페이지 번호는 1 부터 시작하므로 남은 총 페이지 수는 마지막 페이지 번호와 같아지고, 이 경우 51 이 돼요.

sqlite_dbpage 에 NULL 페이지 내용으로 INSERT 하는 것을 "null-INSERT" 라고 불러요. null-INSERT 동작은 특별한 경우라서 일반적인 규칙을 따르지 않아요. 다음은 null-INSERT 의 몇 가지 구체적인 제한 사항이에요:

  • sqlite_dbpage 테이블은 1 페이지에 대한 null-INSERT 를 허용하지 않아요. 따라서 sqlite_dbpage 를 사용해 데이터베이스를 0 바이트나 0 페이지로 잘라내는 것은 불가능해요. 그렇게 하면 데이터베이스 헤더가 빠져서 파일이 더 이상 SQLite 데이터베이스가 아니게 되기 때문이에요.

  • null-INSERT 는 트랜잭션 커밋 전 마지막 쓰기 작업일 때만 데이터베이스를 잘라내요. null-INSERT 가 여러 문장으로 이뤄진 트랜잭션 안에 있고, 같은 트랜잭션 안에서 뒤에 하나 이상의 UPDATE 나 INSERT 작업이 이어지면, 그 null-INSERT 는 no-op 이 되고 데이터베이스는 잘리지 않아요.

  • 트랜잭션당 하나의 데이터베이스 파일만 잘라낼 수 있어요. sqlite_dbpage 가상 테이블은 같은 트랜잭션 안에서 여러 ATTACH 된 데이터베이스에 쓸 수 있지만, null-INSERT 는 COMMIT 전에 마지막 쓰기 문장이어야 하므로, sqlite_dbpage 는 ATTACH 된 데이터베이스 중 하나만 잘라낼 수 있어요. 여러 ATTACH 된 데이터베이스를 잘라내야 한다면, 각각을 별도의 트랜잭션에서 잘라내야 해요.

  • 어떤 ROLLBACK TO 든 null-INSERT 를 취소해요. null-INSERT 자체를 포함하지 않는 ROLLBACK TO 도 마찬가지예요. null-INSERT 의 특별한 동작은 자신만의 트랜잭션으로 실행되거나 COMMIT 바로 직전에 실행될 때만 보장돼요.

2. 호환성 (Compatibility)

sqlite_dbpage 에 INSERT 를 하고, INSERT 를 통해 데이터베이스 크기를 늘리거나 줄이는 기능은 SQLite 버전 3.47.0 (2024-10-21) 에서 추가됐어요. 그 이전 버전에서는 sqlite_dbpage 에 대한 INSERT 를 허용하지 않아요.

더 알아보기 (Learn more)