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 등)에 대해서 알아보도록 하겠습니다.


SQLite는 Android 및 iPhone에 탑재되면서 많이들 사용하고 있는 파일베이스 RDB입니다. 공부를 하는데, 생각보다 많은 자료가 없더군요. 공부하면서 정리한 자료인데.. 많은 도움 되시면 좋겠습니다.

앞으로 제가 포스트할 User Guide의 목차는 다음과 같습니다.

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의 특징.

Sqlite의 특징을 먼저 살펴보도록 하겠습니다.
1. Zero Configuration – sqlite는 별도의 설치 및 설정이 필요 없습니다. 파일 하나만 있으면 모든걸 할 수 있습니다.

2. Portabiliy – 이식성. 거의 모든 OS에서 다 돌아갑니다. 최대 2 terabytes까지 지원하며, 기본 encoding으로 UTF-8과 UTF-16을 지원합니다.

3. Compactness – 아주 가볍고 작습니다. Header file, library, 관계형 데이터를 다 포함해도 사이즈가 얼마 안됩니다.

4. Simplicity – sqlite는 아주 단순하고 사용하기 편리한 programming library를 제공하고 있으면서도 다양한 언어로 확장이 가능하도록 만들어졌습니다.

그밖에 Flexibility , Liberal Licensing(공짜죠), Reliability, Convenience 등이 나와 있습니다.
에구 좋은 말은 다 있는 것 같습니다.

위 내용은 일반적인 내용인것 같고, 보다 중요한 몇 가지를 짚고 넘어가겠습니다.

SQLite는 ANSI92의 기능을 대부분 지원하지만, 아래의 사항은 지원하지 않습니다.

1. RIGHT and FULL OUTER JOIN : LEFT OUTER JOIN만 지원합니다.

2. Complete ALTER TABLE Support : RENAME TABLE과 ADD COLUMN만 지원합니다.
DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT 등 다른 기능은 지원하지 않습니다.

3. Writing to VIEWs : SQLite에서 View는 read-only입니다.

4, GRANT and REVOKE : Sqlite에서 읽기/쓰기 권한은 OS 파일 시스템 권한을 사용합니다. 
별도로 권한 부여 기능이 없습니다.


SQLite 아키텍처


SQLite의 내부 아키텍처는 위 그림과 같습니다.  코어, SQL 컴파일러, 백앤드가 주요 핵심요소이고, 이외에 액세서리 역할을 하는 유틸리티와 테스트 코드로 이뤄져 있습니다.

GUI Tools for SQLite

SQLite를 지원하는 GUI Tools 리스트는 아래 링크에 나와있습니다.
http://sb2.info/commercial-and-freeware-sqlite-tools-list-2/

무지 많습니다. 그 중 몇개를 골라서 사용해 봤는데.. 제가 추천해 드리고 싶은 툴은 다음과 같습니다.

SQLite Expert (http://www.sqliteexpert.com/)


  
여러 제품 중에서 가장 무난하게 사용할 수 있는 제품인 것 같습니다. 필요한 기능은 다 있고, UI도 그럭저럭 쓸만합니다. Personal Edition과 Professional Edition으로 나누어지며, Personal Edition은 무료입니다.
(향후 SQLite관련 포스트에서 저는 이 제품으로 테스트 하겠습니다.) Pro Edition의 Crack도 그리 어렵지 않게 구할 수 있습니다.

SQLite Maestro (http://www.sqlmaestro.com/products/sqlite/maestro/)


아마도 제가 본 Sqlite 관련 툴 중에서 가장 강력한 제품인 것 같습니다. 다른 기능은 몰라도 Reverse로 ERD를 만들어 주는 기능은 이 제품만이 가능합니다.

여담이지만 제가 Touch Call(터치콜) App 개발할 당시 Android SDK2.0 기준의 전화번호부 DB를 이용해야 하는데, 구글의 document만으로는 이해하기가 상당히 어려웠습니다. 출판되어 있는 책들도 다 이전 데이터베이스를 기준으로 작성된 책들이고.. 그때 이 제품을 평가판으로 설치해서 애뮬레이터에 있는 contact2 데이터베이스를 가지고 ERD를 만들어 보니… 훨씬 빠르게 이해를 할 수 있었습니다. 

좋은 제품이긴 하지만 애석하게도 free 버전은 없습니다. 모두 상용버전 밖에 없으며, 한달 Trial 버전만 사용할 수 있습니다. 현재 버전의 crack은 거의 구하기 힘들며, 이전 버전은 구하실 수는 있을 겁니다.

DeZign (http://www.datanamic.com/dezign/index.html)
현재 SQLite를 Forward/Backward Engineering를 완벽하게 지원하는 모델링 툴은 datanamic의 dezign이란 모델링 툴 밖에 없습니다. 근데, 막상 평가판 설치해보니..저희 정서와 맞지 않는 툴이더군요. 저희는 한글로 논리모델을 만들고 영문으로 물리모델을 만드는데, 이 툴은 그런 개념없이 논리/물리가 함께 처리되는 툴이라 바로 지워 버렸습니다. 조금 불편하더라도 DA#이나 ERWin으로 모델링을 하고, Forward Generation한 스크립트를 수정해서 사용하는 게 더 편리할 듯 합니다.

끝으로 앞으로 저의 SQLite 관련 포스트에서 사용할 demo db에 대해서 소개를 하겠습니다. 위에서 얘기했듯이 저는 SQLlite Expert Personal 버전을 이용해서 테스트를 하도록 하겠습니다.

SQLite Expert Personal를 실행한 후 File > Open Demo Database를 실행합니다.
Dbdemos란 데이터베이스가 스키마브라우저에 나타납니다.


좌측 스키마 브라우저에 많은 테이블 리스트가 나옵니다. 저는 그중에서 아래 ERD에 나오는 몇 개의 테이블을 주로 사용해서 DML 테스트 등을 진행하도록 하겠습니다.


고객, 직원, 주문, 주문내역 등 간단한 ERD 구조입니다.
참고로 이 ERD는 Dbdemos SQLlite 데이터베이스를 SQLite Maestro를 이용하여 일부 테이블만 designer에 띄운 모습입니다.)

SQLIte User Guide 첫번째 포스트를 이만 마치며, 다음 포스트에서는 SQLite 의 PRAGMA 명령어 및 시스템 카탈로그 등에 대해서 알아보도록 하겠습니다.

 

  1. BEOM 2010.09.09 11:40

    좋은 정보 감사합니다~

  2. 고냉지채소 2010.11.10 17:52

    좋은 글 감사합니다. 덕분에.. SQLite에 대해서 궁금했던 부분들 많이 해소 했습니다.
    복 받으실꺼에요..^^

  3. 안승진 2011.03.23 11:43

    정말. 잘 배우고 갑니다.^^ 이런 글이 있다니!! 이해도 쉽고, 읽기도 편하고!! 너무 좋아요!

  4. 김병훈 2011.11.22 00:16

    감사드립니다

  5. Miyo 2012.01.13 11:50

    좋은정보 감사합니다~~

+ Recent posts