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

 

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

INSERT 문은 테이블에 새로운 값을 추가할 때 사용하는 명령이다. 패턴에 따라서 몇 가지 형식으로 사용된다.

  1. INSERT INTO table DEFAULT VALUES; : 테이블의 각 칼럼에 적절한 디폴트 값이 있다면 디폴트 값을 적용해서 새 Row를 추가한다. 디폴트 값에는 명시적으로 DEFAULT를 써서 선언한 값이나 자동으로 증가하는 인덱스 번호값 혹은 NULL이 사용될 수 있다.
  2. INSERT INTO table VALUES (val1, val2, val3, ... ); : 테이블의 모든 칼럼에 대해서 값을 던져주고 해당 값으로 새 레코드를 등록한다. 이 때 VALUES 뒤에 나오는 괄호의 값들은 테이블의 전체 칼럼 수와 같은 개수여야 하며, 테이블 스키마에 정의된 칼럼의 순서와 각각 매치되어야 한다.
  3. INSERT INTO table (col1, col3, col7) VALEUS (val1, val2, val3); : 1, 2의 혼합으로 칼럼의 일부에 대해서만 값을 제공하여 레코드를 생성한다. 여기에 명시되지 않은 칼럼들은 디폴트값으로 생성되기 때문에 NOT NULL이거나 디폴트가 정의되지 않았다면 에러가 난다.
(SQLite3) INSERT문 – 테이블에 값 삽입하기 더보기

SQLite3에서 트리거 설치하기

트리거는 데이터 베이스 내에 특정한 동작이 수행될 때, 연관된 다른 쿼리를 자동으로 실행하는 기능이다. 예를 들어 어떤 레코드를 변경하거나 삭제할 때, 그와 관련된 다른 정보를 조작해야 한다거나 할 때 사용할 수 있다. 두 정보 사이의 관련성이 매우 밀접하다면, 클라이언트 코드에서 매번 쿼리를 두 번 실행하는 것보다 트리거를 통해서 관리하는 편이 더 나을 수 있는 것이다.

트리거를 사용하는 가장 일반적인 패턴은 카운트를 대체하기 위한 것이다. BOARD라는 테이블에 레코드가 몇 개인지 알기 위해서는 다음과 같은 쿼리를 실행할 수 있다.

SELECT count(*) FROM BOARD;

문제는 count(*) 함수는 값을 계산하기 위해서 해당 쿼리의 모든 레코드 개수를 세어야 한다는 것이다. 테이블 크기가 작다면 별 문제가 되지 않겠지만, 수백만개의 레코드를 가지고 있는 테이블이라면 문제가 달라진다.

SQLite3에서 트리거 설치하기 더보기

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;

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