일반적인 DBMS 사용 구조와
SQLite 을 비교하고 이해해봅시다.
일반적인 DB 학습관점
1. 첫 번째 학습 관점, DBMS
데이터베이스 관리자(DBA)와 응용 소프트웨어 개발자(ASD)는 이미지 같이 서버 컴퓨터에 실행중인 DBMS 프로그램에 접근하여 SQL을 이용해서 Database에 Table 형태로 Data를 다루는 법을 학습합니다.
2. 두 번째 학습 관점, Dev.
일반적으로 DBMS는 서버 컴퓨터에서 동작하는 하나의 소프트웨어 입니다.
사용자 컴퓨터의 소프트웨어는 Internet을 통해 서버 컴퓨터의 소프트웨어로 연결됩니다.
서버 컴퓨터의 소프트웨어는 필요한 경우에, 동작중인 DBMS 소프트웨어에 SQL을 전달하여 Database를 처리합니다.
서버단의 소프트웨어를 개발할 때 DBMS가 제공하는 Library(Connector)를 이용하여 프로그램을 만들게 됩니다.
SQLite 학습관점
SQLite
SQLite 는 별도의 서버 소프트웨어 프로세스(DBMS)를 가지고 있지 않고, 일반 파일을 직접 읽고 씁니다.
서버(DBMS)와 설정 없이 SQL Database 엔진을 자체 내장한 소프트웨어 라이브러리 입니다.
오픈 소스 프로젝트이며, Public domain 으로 무료로 사용 가능합니다.
SQLite3 라이브러리
SQLite3 은 소스코드에 라이브러리 형태로 빌드되어 사용되고
필요할때 데이터베이스 트렌젝션을 처리합니다.
개발 목적 중 하나로, 임베디스 환경에서 사용될 수 있도록 고려되었기 때문에,
다른 RDBMS 의 트렌젝션 특징을 가지면서 상대적으로 라이브러리 크기가 작고, 메모리가 적은 환경에서도 뛰어난 성능을 발휘한다.
SQLite 는 매우 작은 라이브러리입니다.
컴파일러 세팅이나 타겟 플렛폼에 따라서 조금의 차이는 있겠지만, 350 kbye 정도밖에 되지 않습니다.
옵션 기능을 제거하면 200 kbyte 이하로도 만들 수 있습니다.
또한 실행시에는 메모리 스택영역은 4kbyte, 힙 영역은 100 kbyte 으로 매우 조금만 차지합니다.
이런 이유로 휴대폰, PDA, MP3 플레이어와 같이 메모리 제약적인 곳에서 SQLite 를 널리 쓰고 있습니다.
SQLite 장점
가볍고 빠르다.
이식이 쉽다. 안정적이다.
오픈소스 RDBMS
시스템에 설치할 필요가 없다. 실행 중인 DBMS가 필요 없다.
(MySQL을 사용한다면, MySQL DBMS(Server)가 실행 중이어야 한다.)
SQLite 단점
동시성의 제한된다.
사용자를 관리하지 않는다.
보안이 약하다.
데이터 형식이 부족하다.
1. 첫 번째 학습 관점
DBMS가 아니라, SQLite3 Tool 을 이용해서 Database File 을 만들어 관리할 수 있습니다.
SQLite 를 처음 접할 때, DBMS의 수많은 기능과 DB 모델링의 이론적인 관점 학습보다,
SQLite3 Tool을 이용해 DB File을 생성하고, Table 형태로 Data를 생성, 조회, 수정, 삭제하는 방법을 실습합니다.
SQLite 3 Tool
리눅스의 Shell, 윈도우의 Command Prompt(명령 프롬프트)와 같은
Command Line Interface 에서 SQLite3 DB 에 접근할 수 있는 도구이다.
2. 두 번째 학습 관점
SQLite 서버 역할을 하는 원격의 장치에서 동작중인 DBMS 프로그램에 접근하는 것이 아닙니다.
SQLite3는 로컬에서 사용됩니다.
프로그래밍 언어에서 DB Connector LIbrary와 SQL을 사용하는 방법을 익힙니다.
단일 데이터베이스 파일과 동시성
데이터베이스의 모든 정보가 단 하나의 파일에 저장된다.
테이블 스키마, 레코드 데이터, 인덱스와 같은 모든 정보가 한 파일에 저장되며, SQLite API 로 DB 를 열 때도 해당 파일의 이름을 인자로 받는다.
다른 DB 파일에 있는 데이터를 이용하는 것도 가능한데 이 때는 ATTACH / DETACH 문을 이용해 다른 DB 파일에 들어있는 데이터를 연동하면 된다.
단일 파일 동작이기 때문에 멀티 프로세스나 멀티 스레드로 동작하는 경우 파일 잠금(File lock) 이슈가 발생할 수 있으므로 주의해야 한다.
읽기는 여러 프로세스에서 가능하지만, 쓰기는 한 순간에 오직 하나의 프로세스만 가능하다.
SQLITE_THREADSAFE 가 1 또는 2로 정의되면 SQLite3 lib 엔진에서 내부적으로 동기화를 위한 뮤텍스(mutex)가 활성화되며, 이 때 동기화를 위한 내부적인 연산으로 약간의 성능 저하가 발생한다.
작성중인 프로그램이 단일 스레드에서 동작하고 성능이 가장 중요하다면 SQLITE_THREADSAFE 값을 0 으로 해서 mutex 를 비활성화 할 수 있다.
default 는 1이며, 컴파일되어 배포되는 라이브러리도 1이 기본값이다. ( 즉 기본적으로 thread safe 하다. )
ALTER TABLE 지원
RENAME TABLE 과 ADD COLUMN 은 지원하지만, DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT 와 같은 구문은 지원하지 않는다.
하지만 동적 자료형과 RENAME TABLE 을 적절히 활용하면 지원하지 않는 모든 기능을 동일하게 구현할 수 있다.
RIGHT, FULL OUTER JOIN
LEFT OUTER JOIN 은 지원하지만 RIGHT OUTER JOIN 과 FULL OUTER JOIN 은 지원하지 않는다.
하지만 LEFT OUTER JOIN 구문을 논리적으로 변경해서 RIGHT OUTER JOIN 을 수행할 수 있다.
또한 FULL OUTER JOIN 은 연산과정 자체가 복잡해서 시간이 오래 걸리고 CPU 에 많은 부담을 주므로 다른 임베디드 DBMS 에서도 대부분 지원하지 않는다.
수정 가능한 뷰
뷰는 기본적으로 읽기 전용(read-only)이다.
트리거를 이용하면 뷰에 DELETE, INSERT, UPDATE 연산이 수행되는 것과 동일한 효과를 낼 수 있다.
외래키 제약 조건
FOREIGN KEY 는 하위 호환성을 위해 비활성화되어 있다.
FOREIGN KEY 를 사용해도 문법적 오류는 나지 않지만 강제적인 제약조건으로 동작하지는 않는다.
3.6.19 이후 버전에서는 PRAGMA foreign_keys 구문으로 제약조건을 활성화할 수 있고, 이전 버전에서는 트리거를 이용해 다른 DBMS 와 동일하게 제약조건으로 동작하게 할 수 있다.
AUTOINCREMENT 생성 방법
쉽게 말하자면, INTEGER PRIMARY KEY 로 선언하면 됩니다.
테이블의 컬럼을 INTEGER PRIMARY KEY 로 선언을 하면, NULL 을 넣으면 자동적으로, 가장 마지막 행의 해당 컬럼의 값보다 1 큰 값이 채워집니다. 만약 테이블에 아무런 행도 없다면 1 이 들어갑니다. (integer key 의 최대값인 9223372036854775807 라면, 사용하고 있지 않는 키 중에서 랜덤으로 채워집니다.) 예를들어, 아래처럼 테이블을 만들 경우.
CREATE TABLE t1(
a INTEGER PRIMARY KEY,
b INTEGER
);
에서 아래 문장을 실행하면
INSERT INTO v1 VALUES(NULL, 123);
논리적으로는 다음과 같은 의미입니다.
INSERT INTO v1 VALUES((SELECT max(a) FROM f1) + 1, 123);
가장 마지막에 insert 한 오퍼레션의 integer key 값을 리턴해 주는 sqlite3_last_insert_rowid() 라는 함수가 있습니다.
SQLite 에서 지원하는 자료형(data type)
INTEGER, REAL, TEXT, BLOB, NULL 을 저장할 수 있습니다.
integer 타입 컬럼에 문자열 가능
그건 기능이지 버그가 아닙니다. SQLite 는 동적 타이핑(dynamic typing)을 사용합니다. 데이터 타입 제약을 강제하지 않습니다. 어떤 데이터도 아무 컬럼에나 들어갈 수 있습니다. 임의의 길이의 문자열을 integer 컬럼에, floating 소수를 boolean 컬럼에, date 를 character 컬럼에 넣을 수도 있습니다.
CREATE TABLE 명령에서 선언한 컬럼의 data type 은 해당 컬럼에 들어갈 수 있는 자료형을 제한하지 않습니다. 어떤 컬럼에도 임의의 길이 문자열을 넣을 수 있습니다.(단, INTEGER PRIMARY KEY 로 선언된 컬럼에는 64-bit signed integer 만 들어갈 수 있습니다. INTEGER PRIMARY KEY 컬럼에 integer 가 아닌 다른값을 넣으면 오류가 발생합니다.)
SQLite 에서는 단지 선언한 컬럼에 어떤 형식의 값을 넣기를 선호하는지를 선언하는것 뿐 입니다. 그렇기 때문에 INTEGER 컬럼에 문자열을 넣으려고 하면, SQLite 는 문자열을 integer 로 변환 시킵니다. 만약 변환이 가능하면 변환된 integer 를 넣고 불가능하다면 문자열을 그냥 넣습니다. 이 기능을 type affinity 라고 부릅니다.
하나의 table 안에서 primary key 로 '0' 과 '0.0' 을 함께 사용
primary key 가 숫자 타입(numeric type)일 때, 숫자 타입의 컬럼에서는 SQLite 는 '0' 과 '0.0' 을 같은 값으로 생각합니다.
이런 이유로 값은 고유하지 않게 됩니다.
primary key 을 TEXT 타입으로 변경하면 됩니다.
여러 프로그램이나 한 프로그램의 여러 인스턴스에서 한 개의 database 파일을 동시에 접근할 수 있나요?
멀티 프로세스는 SELECT 작업에서 동시에 접근 할 수 있습니다.
하지만 데이타베이스의 변경은 순간적으로 오직 하나의 프로세스만 가능합니다.
SQLite 는 데이터베이스의 엑세스를 제어하기 위해서 reader/writer lock을 사용합니다.(reader/writer lock 이 부족한 Win95/98/ME 에서는 확률적 시물레이션(probabilistic simulation)으로 대체합니다.)
하지만 NFS 파일 시스템에서 데이타베이스 파일을 쥐고 있으면, 이 락 메카니즘이 제대로 동작하지 않을 수 있습니다. 많은 NFS 구현에서 fcntl() 파일 락킹이 깨졌기 때문입니다. NFS 에서는 다중 프로세스에서 동시에 데이타베이스 파일을 엑세스 하는 것을 피해야 합니다.
다른 임베디드 SQL 데이타베이스 엔진들은 형식적으로 한 데이터베이스에 대해서 한번에 오직 한 프로세스만 연결하도록 하고 있습니다. 하지만 SQLite 는 여러 프로세스에서 동시에 데이타베이스 파일을 여는 것을 허용합니다.
write 를 하는 모든 프로세스는 update 작업동안, 데이터베이스 파일의 락에 들어가야만 합니다. 하지만 대부분 수 millisecond 동안 입니다. 다른 프로세스는 앞 프로세스의 writer 작업이 끝나기를 기다리게 됩니다.
그러나, client/server 데이터베이스 엔진(PostgreSQL, MySQL, Oracle 등)들은 일반적으로 동시에 여러 프로세스에서 한 데이터베이스에 대해 write 할 수 있는 고수준의 동시성을 지원합니다. client/server 데이터베이스에서는 항상 단일 서버 프로세스가 엑세스를 조율하기 때문에 가능합니다. 프로그램에서 수 많은 동시성을 필요로 한다면 client/server 데이타베이스를 고려하는게 나을 것입니다.
기본적으로 SQLite 는 엑세스하려는 파일을 다른 프로세스가 락을 걸고 있으면, SQLITE_BUSY 를 리턴합니다. C 프로그래밍을 할 경우, sqlite_busy_handler() 또는 sqlite3_busy_timeout() API 를 사용하면 이것을 컨트롤 할 수 있습니다.
SQLite 는 쓰레드에 안전(threadsafe)한가요?
다중 쓰레드는 악이다(Threads are evil). 쓰지 마라.
SQLite 는 threadsafe 하다.
우리는 위에서 언급한것과 같이 수 맣은 사용자의 충고를 무시하고 쓰레드에 대해서 양보했습니다.
하지만 thread-safe 하기 위해서는 컴파일 시 SQLITE_THREADSAFE 프리프로세스 매크로가 1로 set 되어 있어야만 합니다. Windows 와 Linux 프리컴파일 바이너리 모두 이 방식으로 컴파일 됩니다.
만약 SQLite 바이너리가 threadsafe 로 컴파일 되었는지 모르겠다면, sqlite3_threadsafe() 인터페이스를 통해서 확인할 수 있습니다.
version 3.3.1 이전 버젼에서는, sqlite3 구조체는 sqlite3_opne() 함수를 호출한 쓰레드에서만 사용할 수 있었습니다.
SQLite 는 BLOB 타입을 지원하나요?
SQLite version 3.0 이후 버젼에서는 컬럼에 BLOB 데이터를 저장 할 수 있습니다. 심지어 컬럼이 BLOB 이 아닌 다른 타입으로 선언되어 있어도 됩니다.
SQLite 에 table 에서 컬럼을 추가하거나 삭제하려면 어떻게 해야 하나요?
SQLite 는 제한적으로 ALTER TABLE 지원합니다. 테이블의 마지막에 컬럼을 추가하거나 테이블 이름을 변경하는 정도입니다.
테이블 구조를 복잡하게 변경해야 한다면, 테이블을 새로 만들어야 합니다.
임시 테이블에 데이터를 저장하고, 이전 테이블을 삭제, 그리고나서 새로 테이블을 만들어서 여기에 데이터를 집어 넣어야 합니다.
예를들어, "t1" 이라는 테이블에 "a", "b", "c" 라는 컬럼이 있고, 컬럼 "c" 를 삭제하려면, 아래처럼 하시면 됩니다.
BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a, b);
INSERT INTO t1_backup SELECT a, b, FROM ta1;
DROP TABLE t1;
CREATE TABLE t1(a, b);
INSERT INTO t1 SELECT a, b, FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;
많은 데이터를 지웠는데, 데이타 파일의 크기는 줄어들지 않습니다. 버그인가요?
아닙니다. SQLite 데이터베이스에서 정보를 삭제할 경우, 사용하지 않는 디스크 공간은 내부적으로 "free-list" 에 등록되고, 다음에 데이터를 입력하면 그때 이 공간을 재사용합니다. 즉 디스크 공간은 사라지지 않습니다.
운영체제에 반납하지 않을 뿐입니다. 만약 많은 양의 데이터를 삭제한 뒤에, 데이터베이스 파일의 크기를 줄이고 싶다면, VACUUM 명령을 사용하시면 됩니다.
VACUUM 은 늘어난 데이터베이스를 재구성 합니다. free-list 를 비우고, 최소 파일 사이즈로 만듦니다.
그러나 VACUUM 을 수행하는데 약간의 시간이 소요되며(SQLite 가 개발된 Linux box 에서는 실험결과 1 메가바이트당 약 0.5초 정도가 소요되는 것을 나타났습니다.), 수행하는 동안 원판 파일의 두배 크기만큼의 가상공간이 필요하다는 것을 기억하시기 바랍니다.
SQLite version 3.1 에서는 auto_vacuum_pragma 를 사용해서 auto-vacuum 모드로 설정할 수 있습니다.
홑따옴표(') 문자를 포함한 문자열을 사용
SQL 표준에서 홑따옴표는 두개의 홑따옴표로 escape 하도록 되어 있습니다.
SQL 은 파스칼 프로그래밍 언어와 비슷합니다.
SQLite 는 이 표준을 준수합니다.
예를 들면 다음과 같습니다.
INSERT INTO xyz VALUES('5.0''clock');
ROUND(9.95, 1) 은 10.0 대신 9.9 가 리턴 됩니다. 9.95 를 반올림 해야 하는거 아닌가요?
SQLite 는 2진수를 사용합니다.
그렇기 때문에 9.95 를 한정된 비트(bit)로 표현할 방법이 없습니다.
64-bit IEEE 부동소수점(SQLite 에서 사용하는 방식)으로 그나마 가장 근접하게 표현한 것이 9.949999999999999289457264239899814128875732421875 입니다.
그렇기 때문에 "9.95" 를 위에 언급한 것처럼 인식해 버립니다. 그리고 값은 아래로 반올림 되어버립니다.
부동소수점들을 표현할 때, 이런 문제들이 발생합니다.
기본적으로 알아 두셔야 할 것은 대부분의 분수값(10진수로 표현 가능한)들은 2진수로 표현 할 수 없습니다.
그렇기 때문에 2진수 중에서 그나마 가장 가까운 값을 사용하게 됩니다.
이것이 가장 근접하게 표현 할 수 있는 방식이지만, 때로 말씀하신 것처럼 예측한 결과와 다른 결과값이 나올 수 있습니다.
유니코드 문자에 대해서는 대소문자 구분없는 매칭(Case-insensitive matching)이 되지 않습니다.
SQLite 의 ASCII 문자 비교시 대소문자를 구분하지 않도록 기본 설정 되어 있습니다.
왜냐면 모든 유니코드에 대해서 대소문자 구분을 지원하려면, SQLite 라이브러리의 전체 사이즈 두배가 넘는 대소문자 변환 테이블과 로직이 들어가야 합니다.
SQLite 개발자들은 모든 유니코드의 대소문자를 지원하는 프로그램은 아마도 필요한 테이블과 함수들을 포함하고 있을 거라고 생각합니다. 그렇기 때문에 SQLite 는 이를 지원하지 않습니다.
기본값으로 모든 유니코드의 대소문자를 지원하는 것 대신, SQLite 는 외부 유니코드 비교방식과 비교 루틴을 사용할 수 있도록 링크기능을 제공합니다.
프로그램에서 NOCASE 대조 순서(sqlite3_create_collation() 을 사용), like(), upper(), lower() 함수를 오버로드 할 수 있습니다.
SQLite 소스코드에는 이런 오버로드를 위한 "ICU" 확장이 포함되어 있습니다. 또는 개발자는 자신들의 프로젝트에 이미 포함되어 있는 유니코드 비교 루틴을 오버로드해서 사용 할 수도 있습니다.
INSERT 가 너무나 느립니다.
SQLite 는 보통 컴퓨터에서 초당 50,000 개 이상의 Insert statement 를 거뜬히 처리합니다.
이 경우 초당 기껏해야 12 트렌젝션 정도가 발생합니다.
트렌젝션의 속도는 여러분의 디스크 회전속도에 따라서 다릅니다.
트렌젝션이 처리되려면 보통 디스크 플레터가 두번 정도 회전하면 됩니다.
7200RPM 디스크 드라이버는 초당 60번의 트렌젝션 처리가 가능합니다.
SQLite 에서는 트렌젝션을 처리하기 전에, 디스크 표면에 데이터가 정말로 안전하게 저장되었는지 확인합니다.
그렇기 때문에 트렌젝션 속도는 디스크 드라이버의 속도에 달려있습니다.
이 방식 덕분에, 시스템의 전원이 갑자기 나가거나, 운영체제가 충돌날 경우에도, 데이터는 안전합니다.
좀 더 자세한 설명을 보시려면 atomic commit in SQLite 를 읽어보시기 바랍니다.
기본 설정으로, 각각의 INSERT statement 는 고유한 트렌젝션을 가지고 있습니다.
하지만 BEGIN 과 COMMIT 을 이용하여 여러개의 INSERT statement 를 시도하면, 모든 inserts 는 하나의 트렌젝션으로 동작합니다.
각각의 insert statement 에 필요한 트렌젝션을 처리하는데 필요한 시간은 모두 하나로 합쳐지고, 소요되는 시간은 대폭적으로 감소됩니다.또 다른 옵션은 PRAGMA synchronous=OFF 를 사용하는 것입니다.
이 명령은 SQLite 가 디스크에 데이터가 써 질때까지 기다리지 않도록 하여, 더 빨라지도록 합니다. 하지만 이 경우 갑자기 시스템 전원이 나가면 트렌젝션중에 있던 데이터는 손실될 수 있습니다.
실수로 SQLite 데이타베이스에서 중요한 데이터를 지웠습니다. 복구 가능한가요?
만약 데이터베이스 파일을 백업해 두었다면, 데이터 복구가 가능합니다.
만약 백업을 하지 않은 경우에는 데이터 복구가 매우 어렵습니다.
SQLite 는 외래키(foreign keys)를 지원하나요?
3.6.19 버젼 이후부터 foreign key constraints 를 지원합니다.
SQLite 의 이전 버전은 외래키 제약 조건(foreign key constraints)를 해석하긴 하지만, 보장하지는 않습니다.
상응하는 기능은 SQLite 트리거를 이용하여 구현 가능합니다.
3.6.12 이후의 버젼에서 SQLite 쉘 도구에서 이러한 트리거를 자동으로 생성하는 ".genfkey" 명령을 지원합니다.
좀 더 자세한 정보를 원하시면 readme 를 읽어보시기 바랍니다.
WHERE 절에 column1="column1" 이 동작하지 않는 것 같습니다. 왜냐면 column1 의 값이 "column1" 인 행 뿐만 아니라, 테이블의 모든 행이 리턴되기 때문입니다.
SQL 에서 문자열을 감싸기 위해서는, 쌍따옴표(double-quotoes)대신 홑따옴표(single-quotes)를 사용해야 합니다.
이것이 SQL 표준 요구사항입니다.
WHERE 절에서 사용할 때, column1='column2' 처럼 되어야 합니다.
SQL 은 특수 문자를 포함하거나 키워드가 되는 식별자(column 또는 table 이름) 를 감쌀 때, 따옴표(double-quotes)를 사용합니다. 즉 따옴표(double-quotes)는 식별자를 처리(escaping)하는 방법입니다.
그러므로 column1="column1" 의 의미는 column1=column1 을 의미하게 되고, 항상 true 를 리턴하게 됩니다.
Relation(관계): table
컴퓨터는 하나의 전공이나 분야에서 정의된 기술이 아니다.
단순히 전기, 기계, 통신, 언어학 등 수많은 전문 분야가 집약된 형태다.
그렇기에 학습 과정에서 동일한 개념을 표현하는 다양한 용어가 존재할 수 있다.
테이블을 바라보는 개인적 관점을 설명한다.
테이블은, 1) 배열의 관점과 2) 구조체 관점에서 접근해 하나를 선택해서 추상화시켜 이해한다.
1. 배열 관점
1차원 배열의 정의에서 값은 좌에서 우로 작성되게 된다.
1차원 배열의 이름은 칼럼명(열 이름)이 된다.
같은 속성을 가진 자료형끼리 묶인다.
하지만, 배열은 2차원일 때도, 같은 자료형을 사용해야 한다.
자료형이 다른 1차원 배열 여러 개를 사용하다면, 동일한 index 위치값으로 식별하여 자료 관리 할 수 있다.
2. 구조체 관점
구조체라는 하나의 사용자가 정의한 데이터 타입을 사용한다.
하나의 구조체는 서로 다른 타입의 여러 데이터를 가질 수 있다.
구조체 배열은 테이블 모델에 가까우나, 칼럼명에 대한 정의가 필요하다.
