SQLite – 테이블을 다른 데이터베이스로 이관하기

테이블을 다른 데이터베이스 파일로 이관하는 방법은 단순하다. 새로운 DB 파일을 새 데이터베이스로 연결해서, CREATE TABLE .. AS SELECT .. 구문을 사용해서 복사한다. 다음 예제는 외부 DB 파일을 반입한 다음, 특정 테이블 하나의 내용으로 새로운 테이블을 채우는 과정을 보여준다.

ATTACH 'file-archives.db' AS other;
CREATE TABLE other.images AS SELECT * FROM main.images;
DETACH others;

CREATE TABLE ... AS ... 를 사용하는 이 방법은 사실 한가지 함정을 가지고 있다. 이 쿼리는 테이블을 복제한다기보다는 한쪽의 데이터를 다른 테이블을 만들어서 밀어넣는 역할을 수행한다. 따라서 기존 테이블의 스키마 구조를 온전하게 유지하지 못한다. 단지 SELECT 쿼리의 결과 그리드를 테이블로 단순 변환하는 것에 지나지 않는다.

만약 전체 테이블을 통째로 복제하고 싶다면? 다음의 내용을 dump-command.sql 로 저장하자.

.output some_table_backup.sql
.dump some_table
.output

이 명령은 해당 테이블을 완전히 재구성할 수 있는 sql 문을 파일로 만들어낸다.  예를 들어 원래의 DB 파일 이름이 oldies.db 라고 한다면

sqlite3 --init dump-command.sql oldies.db

를 실행하면 dump-command.sql 파일이 생성된다. 이 파일은 덤프한 시점의 테이블을 그대로 복원할 수 있는 쿼리문이 모두 저장된다. 당연히 새로운 DB 파일에서 이를 실행하면 다른 DB로 해당 데이터를 복제하게 되는 셈이다.  다음 명령을 실행하면 new.db 데이터 베이스가 생성되면서 (혹은 기존에 있던 거라면 열리면서) some_table이 생성되고 그 내용이 완전히 복원되어 있는 것을 확인할 수 있다.

sqlite3 --init some_table_backup.sql new.db

 

INSERT문 – 테이블에 값 삽입하기 – SQLite3

테이블에 값을 삽입할 때는 INSERT 구문을 사용한다. 이 구문은 크게 세가지 형태로 만들어질 수 있다.

  1. INSERT INTO table (columns...) VALUES (.... ); : 각 칼럼에 (정의하지 않으면 스키마의 모든 칼럼에) ( ... ) 의 값을 각각 적용해서 새로운 레코드를 삽입한다. 지정한 칼럼이나 혹은 전체 칼럼과 값의 개수가 맞지 않으면 에러가 발생한다.
  2. INSERT INTO table SELECT ...; : SELECT 구문을 실행해서 그 결과의 각 행을 해당 테이블의 새로운 행으로 삽입한다. SELECT 결과의 각 칼럼의 순서와 개수는 테이블의 칼럼과 일치해야 한다. 아니면 최소한 칼럼 목록을 명시한다.
  3. INSERT INTO table DEFAULT VALUES; : 모든 칼럼의 디폴트값이 정의되어 있다면 디폴트 값으로 새 레코드를 생성한다.

액션

INSERT 키워드는 REPLACE, INSERT OR REPLACE 등으로 변경될 수 있다. 이는 특정 제한에 의한 충돌 발생 시 이를 해결하기 위한 방편을 정의한다. REPLACEINSERT OR REPLACE와 같은 명령이며 정확하게는 INSERT 문 내에서 ON CONFILCT에 대한 처리로, UNIQUE 제약이나 PRIMARY KEY가 겹치는 등의 삽입 오류가 발생했을 때, 기존 레코드를 UPDATE 하는 방식으로 처리한다.

SQLite3에서 트리거 설치하기

트리거는 데이터 베이스 내에 특정한 동작이 수행될 때, 연관된 다른 쿼리를 자동으로 실행하는 기능이다. SQLite3에서도 트리거를 지원한다. 이러한 트리거는 언제 활용될 수 있을끼? 예를 들어 게시판을 관리하는 테이블이 하나 있다고 가정하자. 이 테이블에서 전체 글 수를 조회하려면 흔히 count() 함수를 써서 다음과 같이 조회할 수 있다.

SELECT count(*) FROM BOARD;

이런 쿼리를 작성할 때 흔히 간과하는 문제는 이 쿼리는 레코드의 수를 세기위해서 전체 테이블을 스캔해야 한다는 것이다. 글의 수가 수백단위라면 큰 문제는 아닐 수 있지만, 글의 수가 매우 많아서 테이블의 크기가 크다면, 그리고 카운터의 특성상 이 쿼리가 빈번하게 요청된다면 I/O에 걸리는 부하가 커져서 전체 성능을 떨어뜨리는 문제가 생길 수 있다.

이 문제를 회피하는 방법으로는 전체 글의 수를 따로 세어 두는 테이블을 만드는 것이다. 별도의 카운터 테이블을 만들고 글이 등록될 때마다 카운터 값을 1 증가시키고 삭제되면 1만큼 감소시키는 것이다. 이를 위해서 글을 등록하는 프로그램에서 글 등록 후 카운터 테이블을 업데이트 하는 쿼리를 추가로 요청하고, 반대로 글 삭제 처리 후에도 카운터 테이블의 값을 감소시키는 쿼리를 추가로 호출해야 한다. 하지만 작업을 하나 하는데 필요한 쿼리가 두 개가 된다는 것은 제법 귀찮은 일이 된다. 이럴 때 트리거를 사용하면 이러한 작업을 단순화할 수 있다. 즉 게시물을 삽입하는 쿼리에 대해 카운터 값을 1 올리는 트리거를 설치하고, 반대로 게시물을 삭제하는 쿼리에 대해서 카운터 값을 1 내리는 트리거를 설치하면 카운터 값은 항상 올바르게 게시물 수와 일치하게 될 것이다.

먼저 글의 수를 카운트할 테이블을 하나 만들고, num 값을 초기화한다.

CREATE TABLE counter (num INTEGER);
INSERT INTO counter values (0);

그런 후에 다음과 같이 트리거를 설치할 수 있다.

CREATE TRIGGER increase_counter INSERT ON board -- board 테이블에 INSERT 가 일어나면
BEGIN
  UPDATE counter SET num = (num + 1);  -- counter 테이블의 num 칼럼값을 +1 한다.
END;

마찬가지로 글 삭제에 대해서도 트리거를 설치할 수 있다. 특히 글 삭제는 하나의 쿼리에서 여러 개의 게시물이 삭제될 수 있기 때문에 FOR EACH ROW 라는 절을 추가해서 삭제되는 행의 개수 만큼 트리거가 일어나도록 한다.

CREATE TRIGGER decrease_counter DELETE ON board
FOR EACH ROW  -- 여러 글을 한 번에 삭제하는 경우에는 삭제되는 레코드 개수만큼
BEGIN
  UPDATE counter SET num = (num - 1);
END;

이렇게하면 글이 추가/삭제될 때마다 트리거에 의해서 counter 테이블의 값이 자동으로 갱신되어, 전체 글 수를 추적하게 된다. 따라서 단순히 글의 개수를 파악하기 위해서 테이블 전체 스캔하는 일을 피할 수 있다.

트리거 SQL 문법

트리거 설치 문법은 다음과 같다.

  1.  CREATE [TEMP|TEMPORARY] TRIGGER : 트리거 설치
  2. [IF NOT EXISTS] : 해당 이름의 트리거가 설치되지 않았다면 새로 설치
  3. {트리거이름}
  4. [BEFORE | AFTER | INSTEAD OF] : 트리거가 수행될 순서로 기본값은 AFTER 이다.
  5. DELETE | INSERT | UPDATE [OF {cols...}] : 트리거를 촉발시킬 동작을 정의한다. 업데이트의 경우에 특정 칼럼에 대한 업데이트만 선택적으로 취할 수 있다.
  6. ON {테이블} : 트리거가 촉발될 대상 테이블이다. 즉 이 테이블에 앞에서 명시한 동작이 이루어질 때 트리거가 시작된다.
  7. [FOR EACH ROW] : 만약 여러 행이 업데이트/삭제되는 경우에 각 행의 변경에 한 번씩 호출되도록 한다.
  8. BEGIN ... END; : 실행될 트리거의 내용

참고로 변경된 행은 BEGIN ~ END 블럭 내에서 OLD, NEW 로 참조된다. INSERT의 경우 NEW만 있고, DELETE의 경우에는 OLD만 있다. UPDATE의 경우에는 변경전 행은 OLD, 변경 후 행은 NEW가 된다.

다른 예제 – 글의 변경 시 이전 내용 백업하기

다른 예를 만들어보자. 블로그나 게시판에서 글의 내용을 변경할 때 이전 내용을 백업하는 트리거를 작성해보자. 백업되는 칼럼은 post_id, title, body, modified_date 이다. (최종 변경 시점이 해당 리비전의 작성 시점이 될 것이므로) 따라서 백업을 위한 테이블을 다음과 같이 생성한다고 한다.

CREATE TABLE backup (
  post_id INTEGER, 
  title VARCHAR(1024),
  body TEXT,
  reg_date DATETIME );

그리고 다음과 같이 트리거를 설치하면 된다.

CREATE TRIGGER backup_post 
AFTER UPDATE OF title, body  -- 1
ON Board                     -- 2
FOR EACH ROW                 -- 3
BEGIN
                             -- 4
  INSERT INTO backup VALUES (
    OLD.post_id, 
    OLD.title, 
    OLD.body, 
    OLD.modified_date
  );
END;

위 쿼리는 트리거를 설치하는데, 그 내용은 다음과 같다고 해석된다.

  1. title, body 칼럼 중 하나가 변경되는 UPDATE 동작에 대해 실행되는 트리거이다.
  2. 업데이트되는 테이블이 Board 일 때 실행된다.
  3. 업데이트가 한 쿼리에서 여럿 발생할 때, 매 행에 대해서 트리거가 실행된다.
  4. 트리거링된 쿼리에 OLD를 사용해서 업데이트 되기 전의 행의 값을 백업한다.

부록 – INSTEAD OF 트리거

트리거는 에 대해서도 설치될 수 있다. 기본적으로 뷰에 변경을 가하는 쿼리를 호출하면 에러가 나지만, 트리거의 타입과 같은 변경 쿼리가 호출되면 에러가 나는 대신에 트리거가 실행된다. 이는 명시적으로 뷰나 뷰의 원본 테이블이 변경되는 것이 아니라, 그저 트리거만 실행될 뿐이다. (만약 그 트리거가 원본 테이블을 변경하도록 되어 있다면 그 때는 원본 테이블이 변경될 것이다.)

SELECT – GROUP BY (SQLite3)

이번 글에서는 SELECT 구문 내에서 GROUP BY 절에 대해서 살펴보자. GROUP BY는 결과 행들을 특정한 하나 혹은 그 이상의 칼럼에 의한 그룹으로 묶어서 그룹을 요약하는 몇 가지 행들로 표현되도록 하는 것이다. 보통 MIN, MAX, SUM, AVG, COUNT 등과 같은 집합 함수와 같이 함께 사용된다. GROUP BY를 적용하는 주요 쿼리 구문의 형식은 다음과 같다.

  • SELECT column1, aggregate_func(column1)
  • FROM _table
  • GROUP BY column1, column2, …. ;

다음의 예는 노래 정보 테이블에서 각 앨범당 수록곡의 개수를 산출하는 쿼리이다.

SELECT albumid, COUNT(trackid)
FROM tracks
GROUP BY albumid;

이 쿼리는 다음과 같이 해석된다.

  1. albumid가 같은 행들은 모두 각각 하나의 그룹으로 취급된다.
  2. 그룹으로 나눠진 대상에 대해서 각 그룹의 albumid와 I의 개수를 리턴한다.

집합 함수로 분류되는 몇몇 함수들은 각 그룹마다 그 그룹에 속해있는 행들에 대해서 반복적용된다.

HAVING 절

having 절을 그룹에 대한 필터링을 수행하는 조건을 추가할 수 있는 절이다. 그룹에 대한 조건이므로 대체로 조건은 aggregate 함수와 같이 쓰인다. 특정 칼럼의 합계나 평균, 최대/최소 및 개수가 어떻다는 식으로 조건을 걸 수 있는 셈이다.

조금 전 작성했던 예제를 GROUP BY와 HAVING을 끼얹은 형태로 살짝 변경해보자.

SELECT albumid, COUNT(trackid)
FROM tracks
GROUP BY albumid
HAVING count(albumid) BETWEEN 18 AND 20
ORDER BY albumid;

위 쿼리는 다음과 같이 동작할 것이다

  1. albumid 값으로 그룹지어 그 행이 18~20 범위에 있는 앨범에 대해서
  2. albumid와 trackid의 개수를
  3. albumid 순으로 출력한다.

즉 수록곡이 18~20개 인 앨범을 출력하라는 쿼리가 된다.

HAVING vs WHERE

그렇다면 GROUP BY가 사용된 쿼리에 WHERE 대신에 HAVING을 쓰면 되는 것이냐? 그것은 아니다. 하나의 쿼리에 HAVING 절과 WHERE 절은 양립할 수 있다. WHERE 절은 각각의 ROW를 필터링하는 조건이 된다. GROUP BYWHERE절에 의해서 필터링된 결과에 대해서 주어진 칼럼으로 데이터를 그룹핑한다. 이후 각각의 그룹에 대해서 특정한 조건으로 다시 필터링할 때 HAVING을 사용할 수 있다.

 

테이블 생성하기 – SQLite3

SQLite에서 테이블을 생성하는 방법에 대해서 알아보자. 테이블을 생성할 때는 CREATE TABLE 구문을 사용한다. 이 구문에서는 테이블이 갖추어야 할 몇 가지 정보와 속성들을 정의할 수 있다.

  • 새로운 테이블의 이름
  • 테이블이 위치할 데이터베이스
  • 테이블 내의 각 칼럼의 이름
  • 각 칼럼의 데이터 타입
  • 디폴트 값이 필요한 칼럼에는 디폴트 값 정의
  • collating sequece를 정의[^1]
  • 필요에 따라 원시 키(PRIMARY KEY)를 정의할 수 있다.
  • 테이블에 대한 제한을 걸 수 있다, UNIQUE, NOT NULL, CHECK, FOREIGN KEY 등이 있다.

이러한 내용들을 바탕으로 CREATE TABLE 문의 구조는 다음과 같다. [ .. ] 내부의 내용은 선택적으로 쓰는 것이며, 구문은 순서대로 끊어서 나열했다.

  1. CREATE [TEMP|TEMPORY] TABLE : 테이블을 만든다.
  2. [IF NOT EXISTS] : 해당 이름의 테이블이 없을 때만 실행되도록 한다. 참고로 중복된 테이블 명을 쓰면 에러가 난다.
  3. {테이블 명}
  4. ( {칼럼 정의...} [{테이블 제약...}] ) : 테이블 스키마를 정의한다.
  5. [WITHOUT  ROWID] : 이 옵션을 주면 Row ID를 생성하지 않는다.
  6. AS {select 구문} : 4번과 같이 정의하는 것이 아니라, SELECT 구문의 결과를 테이블로 생성해버린다.

칼럼 정의

칼럼의 정의는 칼럼이름 [타입] [칼럼제약...] 으로 이루어진다. 놀랍게도 SQLite에서 각 테이블 칼럼은 실질적으로는 타입을 갖지 않는 동적 타입이다.

칼럼 제약은 다음과 같은 것들이 있다.

  1. PRIMARY KEY [ASC|DESC] [AUTOINCREMENT] : 해당 칼럼을 원시키로 사용한다.
  2. NOT NULL :  NULL 값을 가질 수 없다.
  3. UNIQUE : 테이블 내에서 고유한 값을 가져야한다. 복수 칼럼이 유니크하는 경우에는 그 조합이 유니크한지를 검사한다.
  4. CHECK ( 표현식 ) : 값이 제한 조건을 만족하도록 한다.
  5. DEFAULT {값}|{숫자}|({표현식}) : INSERT 시 값이 주어지지 않는 경우 디폴트값.
  6. COLLATE 이름 : 대소비교 방법

칼럼은 기본적으로 NULL의 디폴트 값을 갖는다. 물론 이것은 암시적으로 DEFAULT NULL 이 선언된 것으로 봐야 하며, 명시적인 선언도 가능하다.

만약, 특정 칼럼의 디폴트 값이 다른 칼럼의 값과 동일하게 하려면, 이는 트리거를 이용해서 정의해야 한다.

생성된 테이블을 제거하려면 DROP TABLE... 구문을 사용한다

 

임시테이블

임시 테이블은 영속적으로 사용되지 않고 해당 세션동안만 유지되는 테이블이다. 간혹 캐시를 위해서 임시테이블을 만드는 경우가 있는데, SQLite는 인메모리 데이터베이스를 지원하므로 이런 경우 메모리 내에 임시테이블을 만들 수도 있다. SQLite는 변수 선언을 정의하지 않기 때문에, 변수를 위한 용도로 임시테이블을 만들 곤 한다.

임시테이블은 기본적으로 파일에 생성되는데, 메모리에 임시테이블을 만들기 위해서는 temp_store 라는 PRAGMA 세팅값을 변경해야 한다.

PRAGMA temp_store = 2;

이렇게 해두면 임시 테이블이 메모리상에서만 생성된다.