SQLite - PRAGMA: 초보자를 위한 종합 가이드

안녕하세요, 데이터베이스 열정가 되고자 하는 여러분! 오늘 우리는 SQLite PRAGMAs의 세계로 흥미로운 여정을 떠납니다. PRAGMAs에 대해 들어본 적이 없으신 걸 두려워 마세요 - 이 튜토리얼이 끝나면 프로처럼 사용할 수 있을 것입니다!

PRAGMA는 무엇인가요?

들어보지 않은 것을 시작하기 전에, 기본 개념부터 시작해 보겠습니다. SQLite에서 PRAGMA는 SQLite 라이브러리의 동작을 변경하거나 내부 상태를 쿼리할 수 있는 특별한 명령어입니다. PRAGMAs를 데이터베이스의 제어판으로 생각하면 됩니다 - 설정을 조정하고 데이터베이스가 어떻게 동작하고 있는지에 대한 정보를 얻을 수 있습니다.

이제 SQLite에서 가장 흔히 사용되는 PRAGMAs 중 일부를 탐구해 보겠습니다.

auto_vacuum Pragma

auto_vacuum PRAGMA는 데이터를 삭제할 때 데이터베이스 파일을 자동으로 정리하고 정리하는 '정리 freak' 동거인처럼 작동합니다.

PRAGMA auto_vacuum = FULL;

이 명령어는 auto_vacuum 모드를 FULL로 설정하며, SQLite는 데이터베이스 파일을 가능한 한 작게 유지하기 위해 열심히 작동합니다.

cache_size Pragma

cache_size PRAGMA는 데이터베이스의 뇌를 더 크게 만드는 것과 같습니다. SQLite가 더 빠른 액세스를 위해 메모리에 유지하는 페이지 수를 결정합니다.

PRAGMA cache_size = 10000;

이 설정은 캐시 크기를 10,000 페이지로 설정합니다. 더 많은 캐시는 성능을 향상시키지만, 더 많은 메모리를 사용합니다.

case_sensitive_like Pragma

이 PRAGMA는 LIKE 연산자가 대소문자를 구분하는지 여부를 결정합니다. 데이터베이스가 안경을 쓰거나 안경을 쓰지 않고 읽는 것과 같습니다.

PRAGMA case_sensitive_like = TRUE;

이 설정에서 'Apple' LIKE 'app%'는 거짓이 되고, 'Apple' LIKE 'App%'는 참이 됩니다.

count_changes Pragma

count_changes PRAGMA는 데이터베이스 연산의 스코어키ーパー입니다. INSERT, UPDATE, 또는 DELETE 문이 변경한 행의 수를 알려줍니다.

PRAGMA count_changes = ON;

이 설정 후, 예를 들어 UPDATE 문을 실행할 때, SQLite는 몇 개의 행이 영향을 받았는지 알려줍니다.

database_list Pragma

이 PRAGMA는 데이터베이스의 디렉토리와 같습니다. 주요 데이터베이스와 연결된 다른 데이터베이스를 모두 나열합니다.

PRAGMA database_list;

이 명령어는 연결된 모든 데이터베이스, 파일 경로 및 내부 이름을 보여줍니다.

encoding Pragma

encoding PRAGMA는 데이터베이스가 사용하는 텍스트 인코딩을 설정하거나 쿼리합니다. 데이터베이스가 사용할 언어를 선택하는 것과 같습니다.

PRAGMA encoding = "UTF-8";

이 설정은 데이터베이스 인코딩을 UTF-8으로 설정하며, 여러 언어를 지원하는 일반적인 선택입니다.

freelist_count Pragma

이 PRAGMA는 데이터베이스 파일에서 사용되지 않은 페이지 수를 알려줍니다. 저장 공간의 자유 공간을 확인하는 것과 같습니다.

PRAGMA freelist_count;

이 명령어는 데이터베이스 파일의 사용되지 않은 페이지 수를 반환합니다.

index_info and index_list Pragmas

이 PRAGMAs는 데이터베이스 색인의 도서관 사서와 같습니다. 색인의 구조와 내용에 대한 정보를 제공합니다.

PRAGMA index_list(table_name);
PRAGMA index_info(index_name);

첫 번째 명령어는 테이블에 있는 모든 색인을 나열하고, 두 번째 명령어는 특정 색인에 대한 자세한 정보를 제공합니다.

journal_mode Pragma

journal_mode PRAGMA는 SQLite가 거래를 추적하는 방식을 제어합니다. 다양한 백업 시스템을 선택하는 것과 같습니다.

PRAGMA journal_mode = WAL;

이 설정은 저널 모드를 Write-Ahead Logging(WAL)로 설정하며, 많은 경우 성능을 향상시킬 수 있습니다.

max_page_count and page_count Pragmas

이 PRAGMAs는 데이터베이스 파일의 페이지 수를 다루습니다. 책의 최대 페이지 수를 설정하고 확인하는 것과 같습니다.

PRAGMA max_page_count = 1000000;
PRAGMA page_count;

첫 번째 명령어는 최대 페이지 수를 설정하고, 두 번째 명령어는 현재 사용 중인 페이지 수를 알려줍니다.

page_size Pragma

이 PRAGMA는 데이터베이스의 페이지 크기를 설정하거나 쿼리합니다. 책의 각 페이지 크기를 결정하는 것과 같습니다.

PRAGMA page_size = 4096;

이 설정은 페이지 크기를 4096 바이트로 설정하며, 일반적인 선택입니다.

parser_trace and sql_trace Pragmas

이 PRAGMAs는 데이터베이스의 디버그 모드를 켜는 것과 같습니다. SQLite가 명령어를 처리하는 방식에 대한 자세한 정보를 제공합니다.

PRAGMA parser_trace = ON;
PRAGMA sql_trace = ON;

이 설정은 특정 쿼리가 기대대로 작동하지 않을 때 매우 유용할 수 있습니다.

recursive_triggers Pragma

이 PRAGMA는 트리거가 다른 트리거를 재ursively 발사할 수 있는지 제어합니다. 데이터베이스에서 도미노 효과를 허용하는 것과 같습니다.

PRAGMA recursive_triggers = ON;

이 설정에서 트리거는 다른 트리거를 발사할 수 있으며, 이는 다른 트리거를 발사할 수 있습니다.

schema_version and user_version Pragmas

이 PRAGMAs는 데이터베이스 스키마의 버전 번호를 설정하거나 쿼리합니다. 데이터베이스 구조의 버전 태그와 같습니다.

PRAGMA schema_version;
PRAGMA user_version = 1;

첫 번째 명령어는 현재 스키마 버전을 쿼리하고, 두 번째 명령어는 사용자 버전을 1로 설정합니다.

secure_delete Pragma

이 PRAGMA는 SQLite가 삭제된 데이터를 얼마나 철저하게 덮어쓰는지 제어합니다. 문서를 쉽게 휴지통에 버리거나 쉽게 재활용하는 것과 같습니다.

PRAGMA secure_delete = ON;

이 설정에서 SQLite는 삭제된 데이터를 제로로 덮어쓰며, 복구가 더 어려워집니다.

synchronous Pragma

synchronous PRAGMA는 SQLite가 디스크에 데이터를 얼마나 신중하게 쓰는지 제어합니다. 안전과 속도 사이의 균형을 잡는 것과 같습니다.

PRAGMA synchronous = NORMAL;

이 설정은 중간 수준의 안전을 설정하며, 전체 안전(FULL)과 최대 속도(OFF) 사이의 균형을 잡습니다.

temp_store and temp_store_directory Pragmas

이 PRAGMAs는 SQLite가 일시적인 데이터를 어디에 저장하고 어떻게 저장하는지 제어합니다. 일시적인 작업지를 선택하는 것과 같습니다.

PRAGMA temp_store = MEMORY;
PRAGMA temp_store_directory = '/path/to/directory';

첫 번째 명령어는 일시적인 데이터를 메모리에 저장하도록 지시하고, 두 번째 명령어는 일시 파일의 디렉토리를 설정합니다.

writable_schema Pragma

이 PRAGMA는 sqlite_master 테이블을 직접 수정할 수 있는지 제어합니다. 데이터베이스 구조의 마스터 키와 같습니다.

PRAGMA writable_schema = ON;

이 설정을 신중하게 사용하세요! 잘못 사용하면 데이터베이스 구조를 위험에さら할 수 있습니다.

아래는 우리가 논의한 모든 PRAGMAs의 요약 표입니다:

PRAGMA	설명
auto_vacuum	데이터베이스 자동 정리 제어
cache_size	메모리에 유지할 페이지 수 설정
case_sensitive_like	LIKE 연산자의 대소문자 구분 제어
count_changes	연산으로 변경된 행 수 보고
database_list	연결된 데이터베이스 목록
encoding	데이터베이스 텍스트 인코딩 설정 및 쿼리
freelist_count	사용되지 않은 페이지 수 보고
index_info	특정 색인에 대한 정보 제공
index_list	테이블에 있는 모든 색인 목록
journal_mode	거래 추적 방식 제어
max_page_count	데이터베이스의 최대 페이지 수 설정
page_count	현재 사용 중인 페이지 수 보고
page_size	데이터베이스 페이지 크기 설정 및 쿼리
parser_trace	구문 해석 정보 활성화
recursive_triggers	트리거의 재귀 발사 제어
schema_version	스키마 버전 번호 설정 및 쿼리
secure_delete	삭제된 데이터 덮어쓰기 수준 제어
sql_trace	SQL 실행 정보 활성화
synchronous	데이터 디스크 기록 수준 제어
temp_store	일시 데이터 저장 위치 제어
temp_store_directory	일시 파일 디렉토리 설정
user_version	사용자 정의 버전 번호 설정 및 쿼리
writable_schema	sqlite_master 테이블 직접 수정 허용

이제 SQLite PRAGMAs에 대해 잘 알고 계시는 것입니다! 이 강력한 도구들은 데이터베이스의 동작과 성능에 큰 영향을 미칠 수 있으므로, 생산 환경에서 사용하기 전에 충분히 테스트하십시오. 행복한 코딩을 기원합니다!

Credits: Image by storyset