SQLite User Guide for Android, iPhone - DDL(CREATE, DROP 등)

SQLite User Guide 세번째 포스트입니다.  이번에는 CREATE TABLE, CREATE INDEX 등의 DDL 문에 대해 알아보도록 하겠습니다.

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 DDL문은 특별한 게 없어서 대부분 잘 아시리라 생각됩니다. DDL문에 대해 전혀 모르신다구요? 상관없습니다. GUI 툴을 사용하셔서 테이블이나 인덱스를 만드시고 DDL문을 코드에 붙이셔도 됩니다. 본 포스트에서는 모든 DDL문을 다루기 보다는 주요한 몇가지와 SQLite DB에서의 특징적인 면을 살펴보도록 하겠습니다.

1. CREATE TABLE

예제1)
CREATE TABLE TEST2 (
     _ID INTEGER NOT NULL,    
     CLASS TEXT NOT NULL,    
     VALUE TEXT,
     CONSTRAINT TEST2_PK PRIMARY KEY (_ID, CLASS)
);

SQLite에서는 ALTER TABLE 문에서 ADD CONSTRAINT 구문이 지원되지 않기 때문에 PRIMARY KEY, UNIQUE, CHECK등의 TABLE LEVEL의 CONSTRAINT는 위 문장처럼 CREATE TABLE 문 제일 하단에 기술해야 합니다.

예제2)
CREATE TABLE TEST1 (
     _ID  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
     TYPE TEXT NOT NULL DEFAULT 'HIGH',
     NAME TEX);

일련번호 채번은 위와 같이 AUTOINCREMENT 구문을 CRATE TABLE 의 해당 컬럼에 기술하면 자동 증가되는 일련번호를 사용할 수 있습니다.
AUTOINCREMENT 문을 가진 DDL이 최초 실행되면 SQLITE는 내부적으로 SQLITE_SEQUENCE 테이블을 생성합니다. 이 테이블은 NAME, SEQ 컬럼을 가진 테이블로 테이블마다 자동 증가되는 최종값을 가집니다. 재미있게도 SQLITE_SEQUENCE 테이블값을 직접 핸들링도 가능합니다.

/* SQLITE_SEQUENCE TEST */

--위에서 만든 TEST1 테이블에 데이터를 2개 넣었습니다.
INSERT INTO TEST1(NAME) VALUES('A');
INSERT INTO TEST1(NAME) VALUES('B');

SELECT * FROM SQLITE_SEQUENCE;

-- 현재 SEQ = 2인 SQLITE_SEQUENCE 테이블을 30으로 업데이트합니다.
UPDATE SQLITE_SEQUENCE
SET SEQ = 30
WHERE NAME = 'TEST1';

-- 다시 레코드를 INSERT한 후 TEST1 데이터를 조회해보니 마지막에 들어간 레코드값은 31로 들어갑니다.
INSERT INTO TEST1(NAME) VALUES('B');

SELECT * FROM TEST1;

_ID TYPE NAME
1 HIGH A
2 HIGH B
31 HIGH B

TEMP TABLE

/* TEMP TABLE CREATION */
CREATE TEMP TABLE COUTRY_TEMP
AS
SELECT *
  FROM COUNTRY     
 WHERE 1 = 2;

CREATE TABLE 구문 사이에 TEMP, TEMPORARY 명령어를 넣으시면 임시 테이블이 생성됩니다. 임시테이블은 Connection이 끊어지면 테이블이 없어집니다. 위 예에서 WHERE 1=2 라고 기술하시면 WHERE 조건이 거짓이 되어 테이블 생성시 COUNTRY 테이블과 동일한 테이블이 만들어지지만 데이터는 들어가지 않습니다.
앞의 PRAGMA 편 포스트에서 시스템 카탈로그를 조회할 수 있는 테이블이 SQLITE_MASTER라고 했는데, 임시테이블을 만들면 이 임시테이블은 SQLITE_MASTER로 조회해도 나타나지 않습니다.
임시테이블은 SQLITE_TEMP_MASTER를 조회하시면 조회가 가능합니다.

COLLATE

추가적으로 COLLATE에 대해서도 알아보도록 하겠습니다. TABLE 정의시 COLLATE를 지정할 수 있습니다. 값은 BINARY, RTRIM, NOCASE 구문이 가능한데, 각각의 케이스에 따라서 값을 비교하는 비교연산자의 결과에 영향을 미칠 수 있습니다. (http://www.sqlite.org/datatype3.html)

COLLATE로 지정가능 한 값.

BINARY – 내부적으로 텍스트 인코딩과 상관없이 memcmp() 함수를 이용하여 텍스트를 비교합니다.
NOCASE – BINARY와 유사하지만 영문자의 경우 대소문자를 동일하게 처리, 즉, 대소문자 구별을 하지 않습니다.
RTRIM – BINARY와 동일하지만 비교하는 문자열의 끝부분에 공백이 있는 경우 이를 제거하고 비교합니다.
디폴트값은 binary 입니다.

COLLATE에 대한 기본 처리 룰은 다음과 같습니다.

• 이항 비교 연산자(=, <, >, <= 및 >=)의 경우 피연산자 중 하나가 열이면 해당 열의 기본 데이터 정렬 유형에 따라 비교에 사용되는 데이터 정렬 시퀀스가 결정됩니다. 두 피연산자가 모두 열이면 왼쪽 피연산자의 데이터 정렬 유형에 따라 사용되는 데이터 정렬 시퀀스가 결정됩니다. 두 피연산자가 모두 열이 아니면 BINARY 데이터 정렬 시퀀스가 사용됩니다.

• BETWEEN...AND 연산자는 >= 및 <= 연산자가 있는 두 표현식을 사용하는 것과 같습니다. 예를 들어 x BETWEEN y AND z 표현식은 x >= y AND x <= z와 동일합니다. 따라서 BETWEEN...AND 연산자는 위와 같은 규칙에 따라 데이터 정렬 시퀀스를 결정합니다.

• IN 연산자는 = 연산자와 같은 방법으로 사용할 데이터 정렬 시퀀스를 결정합니다. 예를 들어 x IN (y, z) 표현식에 사용되는 데이터 정렬 시퀀스는 x가 열인 경우 x의 기본 데이터 정렬 유형입니다. 그렇지 않은 경우에는 BINARY 데이터 정렬이 사용됩니다.

• SELECT 문에 포함된 ORDER BY 절에는 정렬 작업에 사용할 데이터 정렬 시퀀스를 명시적으로 할당할 수 있습니다. 이렇게 하면 명시적인 데이터 정렬 시퀀스가 항상 사용됩니다. 그렇지 않으면 ORDER BY 절에서 정렬하는 표현식이 열인 경우 해당 열의 기본 데이터 정렬 유형에 따라 정렬 순서가 결정됩니다. 표현식이 열이 아니면 BINARY 데이터 정렬 시퀀스가 사용됩니다.

/* COLLATE TEST */

CREATE TABLE t1(
    x INTEGER PRIMARY KEY,
    a,                 /* collating sequence BINARY */
    b COLLATE BINARY,  /* collating sequence BINARY */
    c COLLATE RTRIM,   /* collating sequence RTRIM  */
    d COLLATE NOCASE   /* collating sequence NOCASE */
);

                                 /* x   a      b        c       d */
INSERT INTO t1 VALUES(1,'abc','abc', 'abc  ','abc');
INSERT INTO t1 VALUES(2,'abc','abc', 'abc',  'ABC');
INSERT INTO t1 VALUES(3,'abc','abc', 'abc ', 'Abc');
INSERT INTO t1 VALUES(4,'abc','abc ','ABC',  'abc');

/* Text comparison a=b is performed using the BINARY collating sequence. */
/* a, b 컬럼이 모두 BINARY로 정의되어 있으므로 BINARY모드로 비교합니다. */
SELECT x FROM t1 WHERE a = b ORDER BY x;
--result 1 2 3

/* Text comparison a=b is performed using the RTRIM collating sequence. */
/* 명시적으로 COLLATE를 지정하면 해당 COLLATE로 비교합니다. */
SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x;
--result 1 2 3 4

/* Text comparison d=a is performed using the NOCASE collating sequence. */
/* 서로 컬럼의 COLLATE가 다른 경우 선행 컬럼의 COLLCATE를 사용해서 비교합니다. */
SELECT x FROM t1 WHERE d = a ORDER BY x;
--result 1 2 3 4

/* Text comparison a=d is performed using the BINARY collating sequence. */
/* 서로 컬럼의 COLLATE가 다른 경우 선행 컬럼의 COLLCATE를 사용해서 비교합니다. */
SELECT x FROM t1 WHERE a = d ORDER BY x;
--result 1 4

/* Text comparison 'abc'=c is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE 'abc' = c ORDER BY x;
--result 1 2 3

/* Text comparison c='abc' is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE c = 'abc' ORDER BY x;
--result 1 2 3

/* Grouping is performed using the NOCASE collating sequence (Values
** 'abc', 'ABC', and 'Abc' are placed in the same group). */
/* 그룹바이절에 지정된 컬럼의 COLLATE를 사용해서 처리합니다. */
SELECT count(*) FROM t1 GROUP BY d ORDER BY 1;
--result 4

/* Grouping is performed using the BINARY collating sequence.  'abc' and
** 'ABC' and 'Abc' form different groups */
SELECT count(*) FROM t1 GROUP BY (d || '') ORDER BY 1;
--result 1 1 2

/* Sorting or column c is performed using the RTRIM collating sequence. */
/* ORDER BY절에 선행컬럼에 지정된 컬럼의 COLLATE를 사용해서 정렬하는군요 */
SELECT x FROM t1 ORDER BY c, x;
--result 4 1 2 3

코드 출처 : http://www.sqlite.org/datatype3.html

SQLite COLLATE 처리 원칙만 알면 대충 예상은 가능합니다. 그렇다면, 다음의 쿼리는 결과가 어떻게 나올까요?

SELECT d, c FROM t1 GROUP BY d, c ;

SELECT c, d FROM t1 GROUP BY c, d ;

직접 한 번 해보시기 바랍니다. 위 쿼리의 경우 2개를 동시에 비교해서 처리합니다. 테스트 해 본 결과 Sqlite에서는 Group by가 Sort Group by 방식을 사용하는 듯 합니다. (Group By 절에 지정된 순되로 레코드가 정렬되어서 나옵니다.) Group by나 Order시에 같은 Collate이면 rowId 순으로로 처리되는 것 같네요.

에구.. 복잡합니다.
개발할 때는 디폴트 값인  binary 모드로만 DDL을 생성해서 처리하는 게 예측하기 편리하고 좋을 것 같습니다.

ROWID

CREATE TABLE 관련하여 끝으로 ROWID에 대해서 알아보도록 하겠습니다.
SQLite의 모든 Table의 열은 unique한 64bit singed integer key를 내부적으로 가집니다. 이 값을 “ROWID”, “OID“, “_ROWID_”라고 부르며, SELECT 절에 기술하면 데이터 조회도 가능합니다.

만일 테이블 생성시에 컬럼의 정의를 INTEGER PRIMARY KEY라고 기술하면 이 컬럼(INTEGER PRIMARY KEY) 컬럼을 ROWID에 대한 ALIAS로 사용하게 됩니다. 즉, 컬럼과 ROWID가 같아집니다.
다음의 테스트를 통해 확인해 보도록 하겠습니다.

/* ROWID Alias Test */
create table t
(
x INTEGER PRIMARY KEY ASC,
v text
);

insert into t values(1,'a');
insert into t values(2,'a');
insert into t values(5,'a');

select rowid, x, v from t;

x x_1 v
1 1 a
2 2 a
5 5 a

x컬럼을 INTEGER PRIMARY KEY로 정의한 테이블 생성후, 데이터를 몇개 넣고 조회를 해보면 rowid라고 SELECT절에 기술한 컬럼의 명칭은은 x 로 표기되어 있습니다. 두번째 x는 x 이름이 중복되므로 _1을 붙여서 나왔습니다.

2. ALTER TABLE

ALTER TABLE은 컬럼추가 및 테이블명 변경외에는 안됩니다. 이 점은 중요합니다. 우리가 안드로이드 개발시 SQLite를 사용해야 하며, 컬럼 추가 이외에 DB 구조를 변경해야 한다면, upgrade 처리시에 여러가지를 고려해야 할 것입니다.

3. CREATE VIEW

CREATE VIEW에서 TEMP/TEMPORARY를 지정해서 뷰를 임시로 만들 수가 있습니다. 다른 DBMS에서는 못 본 것 같은데… 이런 기능도 있군요.

4. ANALYZE

ANALYZE는 테이블의 통계정보를 수집하는 명령어입니다. 우리가 사용하는 DB의 데이터가 많거나 속도가 안 나온다면 명령어를 실행해 보시기 바랍니다. 뒤에 파라미터에 값을 주지 않고 “ANALYZE” 명령어만 실행하면 전체 테이블, 인덱스에 대해서 통계정보가 생성됩니다. 생성된 통계정보는 SQLITE_STAT1 이란 테이블이 생성되면서 이곳에 저장됩니다. 문제는 ANALYZE 명령어가 데이터베이스 컨텐츠 변경시에 자동으로 실행되지는 않습니다.

ANALYZE
SELECT * FROM sqlite_stat1;

5. REINDEX

인덱스 리빌드 하는 명령어입니다. 단편화가 많이 된 인덱스를 다시 생성해 줍니다

6. VACUUM

VACUUM 명령어는 데이터베이스를 완전히 cleanzing 하는 명령어입니다. 데이터베이스 Object를 Drop하더라도 재사용을 위해 빈 공간은 남겨집니다. 이런 빈공간 및 DML 작업으로 인한 단편화를 제거하기 위해서 VACUUM 명령어를 실행할 수 있습니다. 실행하면 파일 사이즈도 줄여줍니다.

VACUUM 명령어를 실행하면 SQLITE는 전체 OBJECT를 TEMP 공간에 복사한 후 다시 복사하여 전부 새롭게 생성합니다. 이때 ROWID는 변경될 수도 있습니다.

본 포스트에서 간략하게나마 필요한 DDL문은 대충 다 다룬 것 같습니다. 일부 DDL과 뒤에 포스트에서 별도로 다룰  TRIGGER, TRANACTION 관련 DDL은 다루지 않았습니다.
DDL에 관한 전체 명령어 리스트 및 내용은 http://www.sqlite.org/lang.html  문서를 참고하시기 바랍니다.