SQLite User Guide for Android, iPhone - PRAGMA, Data Type 등.

SQLite User Guide 두번째 포스트입니다.
SQLite Document를 보다 보니 PRAGMA란 지시어(확장 명령어)가 나옵니다. 오라클에서도 PL/SQL 개발할 때 자율 트랜젝션이나 에러 정의 할 때 등 몇몇 부분에 PRAGMA 지시어를 사용할 수 있긴 합니다만 SQLite만의 독특한 기능들이라서 별도로 포스트를 구성했습니다.

전체 PRAGMA 지시어 (http://www.sqlite.org/pragma.html ) 중에서 자주 사용할 법한 몇가지만 살펴보도록 하겠습니다.

SQLite User Guide 포스트 목차

1. SQLite User Guide - 소개. GUI Tools 등
2. SQLite User Guide - PRAGMA, DATA TYPE
3. SQLite User Guide - DDL(CREATE, DROP 등)
4. SQLite User Guide - DML (SELECT, INSERT, UPDATE, DELETE 등)
5. SQLite User Guide - Function (내장함수, Aggregation 함수)
6. SQLite User Guide - DateTime 함수, DateTime Formatting
7. SQLite User Guide - Trigger
8. SQLIte User Guide - Tranaction, Lock
9. SQLite User Guide - Performance, Optimizer
10. SQLite User Guide - VDBE(Virtual Database Engine)

이전 포스트에도 밝혔듯이 테스트는 SQLite Expert Personal 제품에 들어있는 demodb를 사용하도록 하겠습니다.

PRAGMA 명령어

PRAGMA case_sensitive_like = boolean;

SQLite는 기본적으로 like 검색시에 대소문자를 무시합니다. ‘A’ LIKE ‘a’ 가 true가 됩니다. PRAGMA 에서 이 설정을 바꿀수 있습니다.

PRAGMA case_sensitive_like = true;

/* Default 설정에서 대소문자 처리에 대한 Equal(=) SELECT 테스트 */
SELECT *
  FROM COUNTRY   
 WHERE NAME = 'Brazil';

-- Result
Name Capital Continent Area Population
Brazil Brasilia South America 8511196 150400000

SELECT *
  FROM COUNTRY   
 WHERE NAME = 'brazil';

결과없음.

위 PRAGMA 설정은 LIKE 검색에 한해서만 적용되는 듯합니다. WHERE조건에 equal 검색은 대소문자를 정확히 구분합니다. (Collate 설정에 따라서 바꿀 수 있습니다. 뒤쪽에 나옴)

/* Default 설정 - Like 검색시 대소문자 구별 안함 에서 LIKE 검색 테스트*/

SELECT *
  FROM COUNTRY   
 WHERE NAME LIKE '%brazil%';

-- Result
Name Capital Continent Area Population
Brazil Brasilia South America 8511196 150400000

/* Default 설정 - Like 검색시 대소문자 구별 안함 에서 %없이 LIKE 검색 테스트*/
SELECT *
  FROM COUNTRY   
 WHERE NAME LIKE 'brazil';

-- Result
Name Capital Continent Area Population
Brazil Brasilia South America 8511196 150400000

이번에는 LIKE 검색에  % 연산자를 빼고 검색했습니다.(두번째 쿼리문) 오라클의 경우 이런식의 쿼리가 날아가면 Optimzer가 query를 like 검색을 =로 변경해 버립니다. 하지만, SQLite의 경우 그렇지 않고 like 검색 되는 것 같습니다. (equal과 거의 유사하지만 대소문자만 가리지 않는 방식).

명령어로 대소문자 비교를 하도록 설정을 변경 한 후 테스트 해보도록 하겠습니다.

PRAGMA case_sensitive_like = 'TRUE'

SELECT *
  FROM COUNTRY   
 WHERE NAME LIKE '%brazil%';

-- Result 없음.

역시나 설명되로 PRAGMA 명령어로 설정을 변경했더니 대소문자를 따져서 LIKE 검색을 하는군요.

PRAGMA count_changes;
PRAGMA count_changes = boolean;

count_changes 값이 설정되면 INSERT, UPDATE, DELETE 시에 변경된 레코드 수(Integer)값을 받으며, false로 설정되어 있으면 받지 않는다고 합니다. GUI 툴에서는 툴 자체가 메시지를 표시하기 때문에 제대로 테스트가 안되더군요. 나중에 틈날 때 코드레벨에서 테스트를 해봐야 겠습니다.

PRAGMA encoding;
PRAGMA encoding = "UTF-8";
PRAGMA encoding = "UTF-16";
PRAGMA encoding = "UTF-16le";
PRAGMA encoding = "UTF-16be";

데이터베이스의 인코딩을 바꿀수 있는 옵션입니다. 거의 쓸 일은 없을 것 같습니다.

PRAGMA table_info(table-name);
테이블 정보를 조회하는 명령어입니다.

PRAGMA table_info(COUNTRY);

cid name type notnull dflt_value pk
0 Name CHAR(24) 0  1
1 Capital CHAR(24) 0  0
2 Continent CHAR(24) 0  0
3 Area FLOAT 0  0
4 Population FLOAT 0  0

PRAGMA index_list(table-name); 인덱스 리스트를 볼 수 있습니다.
PRAGMA index_info(index-name); 인덱스 정보를 조회할 수 있습니다.
PRAGMA foreign_key_list(table-name); fk 리스틀 볼 수 있습니다.

시스템 카탈로그를 조회할 수 있는 방법은 위 PRAGMA 명령 말고도 sqlite_master 테이블을 직접 조회해도 됩니다. SQLITE_MASTER 테이블은 READ-ONLY 테이블입니다.

SELECT * FROM SQLITE_MASTER;

SQLITE_MASTER 테이블을 조회하시면 테이블, 인덱스, 트리거등 모든 정보와 DDL문까지 알 수가 있습니다.

DATATYPE

Sqlite가 지원하는 데이터 타입은 다음과 같습니다.

1. Null
2. Integer – 부호있는 정수, 실제 값에 따라 1byte에서 8byte까지 가변적으로 저장됨.
3. Real – 실수
4. Text – 문자열
5. BLOB – blob 데이터

실제적으로 저희가 테이블 생성시 DDL상에 VARCHAR(10)이라고 컬럼 사이즈를 정의해도 SQLITE는 TEXT 타입으로 만들어집니다. 그렇기 때문에 10자 이상의 데이터도 삽입이 가능합니다. 그러니, DDL 문 만들 때 구지 다른 데이터 타입을 외울 필요없이 위 타입만 알고 있으면 될 것 같습니다.

재미있는 것은 Data and Time 즉, 날짜 관련 데이터 타입이 따로 없다는 것입니다. DATETIME은 입력되는 값에 따라서 TEXT, REAL, INTEGER 타입으로 저장됩니다. http://www.sqlite.org/datatype3.html 보시면 어떤 데이터 타입이 어떤 식으로 변경되는지 알수가 있습니다.

이것으로 SQLite User Guide 두번째 포스트를 마치겠습니다. 다음 포스트에서는 SQLite 사용에 꼭 필요한 DDL문(CREATE TABLE, CRATE INDEX 등)에 대해서 알아보도록 하겠습니다.