SQLite의 가상 데이터베이스 엔진
SQLite의 가상 데이터베이스 엔진 (The Virtual Database Engine of SQLite)
SQLite의 핵심인 가상 데이터베이스 엔진(VDBE)이 어떻게 동작하는지, 그리고 다양한 VDBE 명령이 어떻게 함께 동작해 데이터베이스에 유용한 일을 하는지 설명하는 튜토리얼 문서예요.
본문
구식 문서 경고 (Obsolete Documentation Warning): 이 문서는 SQLite 버전 2.8.0에서 사용된 가상 머신을 설명해요. SQLite 버전 3.0과 3.1의 가상 머신은 개념은 비슷하지만 이제 스택 기반이 아니라 레지스터 기반이고, opcode당 피연산자가 3개가 아니라 5개이며, 아래 보이는 것과는 다른 opcode 집합을 가져요. 현재의 VDBE opcode 집합과 VDBE가 어떻게 동작하는지에 대한 간략한 개요는 가상 머신 명령 (virtual machine instructions) 문서를 참고하세요. 이 문서는 역사적 참고 자료로 보존되어 있어요.
SQLite 라이브러리가 내부적으로 어떻게 동작하는지 알고 싶다면 가상 데이터베이스 엔진(VDBE)에 대한 확실한 이해부터 시작해야 해요. VDBE는 처리 스트림의 정확히 중간에 위치하며(아키텍처 다이어그램 참고) 라이브러리의 대부분 부분을 건드리는 것처럼 보여요. VDBE와 직접 상호작용하지 않는 코드 부분조차 보통 보조 역할을 해요. VDBE는 정말로 SQLite의 심장이에요.
이 문서는 VDBE가 어떻게 동작하는지, 특히 다양한 VDBE 명령(여기 opcode.html에 문서화됨)이 어떻게 함께 동작해 데이터베이스로 유용한 일을 하는지에 대한 간략한 소개예요. 단순한 작업에서 시작해 더 복잡한 문제를 해결하는 방향으로 나아가는 튜토리얼 스타일이에요. 그 과정에서 SQLite 라이브러리의 대부분 하위 모듈을 방문하게 될 거예요. 이 튜토리얼을 완료한 후에는 SQLite가 어떻게 동작하는지 꽤 잘 이해하게 되고 실제 소스 코드를 공부하기 시작할 준비가 될 거예요.
들어가기 (Preliminaries)
VDBE는 가상 머신 언어로 프로그램을 실행하는 가상 컴퓨터를 구현해요. 각 프로그램의 목표는 데이터베이스를 조사하거나 변경하는 것이에요. 이를 위해 VDBE가 구현하는 기계 언어는 데이터베이스를 검색하고, 읽고, 수정하도록 특별히 설계되었어요.
VDBE 언어의 각 명령은 P1, P2, P3으로 표시되는 opcode와 세 개의 피연산자를 포함해요. 피연산자 P1은 임의의 정수예요. P2는 음이 아닌 정수예요. P3는 데이터 구조 또는 0으로 끝나는 문자열에 대한 포인터이고, null일 수도 있어요. 세 피연산자를 모두 사용하는 VDBE 명령은 소수뿐이에요. 많은 명령은 피연산자를 하나 또는 둘만 사용해요. 상당수의 명령은 피연산자를 전혀 사용하지 않고 대신 실행 스택에서 데이터를 가져오고 결과를 스택에 저장해요. 각 명령이 무엇을 하는지, 어떤 피연산자를 사용하는지에 대한 세부 사항은 별도의 opcode 설명 문서에 설명되어 있어요.
VDBE 프로그램은 명령 0에서 실행을 시작하고 (1) 치명적 오류를 만나거나, (2) Halt 명령을 실행하거나, (3) 프로그램 카운터가 프로그램의 마지막 명령을 지나칠 때까지 연속된 명령으로 계속돼요. VDBE가 실행을 완료하면 열린 모든 데이터베이스 커서가 닫히고, 모든 메모리가 해제되며, 스택에서 모든 것이 pop돼요. 따라서 메모리 누수나 할당 해제되지 않은 자원에 대해 걱정할 필요가 전혀 없어요.
어셈블리 언어 프로그래밍을 해봤거나 어떤 종류의 추상 머신을 다뤄본 적이 있다면 이런 세부 사항이 모두 익숙할 거예요. 그럼 바로 코드를 살펴보기 시작해요.
데이터베이스에 레코드 삽입하기 (Inserting Records Into The Database)
몇 개 안 되는 명령으로 된 VDBE 프로그램으로 풀 수 있는 문제로 시작해요. 다음과 같이 만들어진 SQL 테이블이 있다고 가정해요:
CREATE TABLE examp(one text, two int);
즉, "one"과 "two"라는 두 개의 데이터 열을 가진 "examp"라는 이름의 데이터베이스 테이블이 있어요. 이제 이 테이블에 단일 레코드를 삽입하려 한다고 가정해요. 이렇게요:
INSERT INTO examp VALUES('Hello, World!',99);
sqlite 명령줄 유틸리티를 사용해 SQLite가 이 INSERT를 구현하는 데 사용하는 VDBE 프로그램을 볼 수 있어요. 먼저 새 빈 데이터베이스에서 sqlite를 시작하고 테이블을 만든 다음, ".explain" 명령을 입력해 sqlite의 출력 형식을 VDBE 프로그램 덤프와 함께 작동하도록 설계된 형식으로 바꿔요. 마지막으로 위에 보이는 [INSERT] 문을 입력하되 [INSERT] 앞에 특수 키워드 [EXPLAIN]을 붙여요. [EXPLAIN] 키워드는 sqlite가 VDBE 프로그램을 실행하지 않고 출력하게 해요. 결과는 다음과 같아요:
$ sqlite test_database_1
sqlite> CREATE TABLE examp(one text, two int);
sqlite> .explain
sqlite> EXPLAIN INSERT INTO examp VALUES('Hello, World!',99);
addr opcode p1 p2 p3
---- ----------- ---- ---- -----------------------------------
0 Transaction 0 0
1 VerifyCookie 0 81
2 Transaction 1 0
3 Integer 0 0
4 OpenWrite 0 3 examp
5 NewRecno 0 0
6 String 0 0 Hello, World!
7 Integer 99 0 99
8 MakeRecord 2 0
9 PutIntKey 0 1
10 Close 0 0
11 Commit 0 0
12 Halt 0 0
위에서 볼 수 있듯이 우리의 단순한 insert 문은 12개의 명령으로 구현돼요. 처음 3개와 마지막 2개 명령은 표준 프롤로그와 에필로그라서 실제 작업은 가운데 7개 명령에서 이루어져요. 점프가 없으므로 프로그램은 위에서 아래로 한 번 실행돼요. 이제 각 명령을 자세히 살펴봐요.
0 Transaction 0 0
1 VerifyCookie 0 81
2 Transaction 1 0
Transaction 명령은 트랜잭션을 시작해요. 트랜잭션은 Commit 또는 Rollback opcode가 나올 때 끝나요. P1은 트랜잭션이 시작되는 데이터베이스 파일의 인덱스예요. 인덱스 0은 메인 데이터베이스 파일이에요. 트랜잭션이 시작되면 데이터베이스 파일에 쓰기 잠금이 얻어져요. 트랜잭션이 진행되는 동안 다른 프로세스는 파일을 읽거나 쓸 수 없어요. 트랜잭션을 시작하면 롤백 저널도 만들어져요. 데이터베이스에 변경을 가하기 전에 반드시 트랜잭션을 시작해야 해요.
VerifyCookie 명령은 cookie 0(데이터베이스 스키마 버전)을 검사해 P2(데이터베이스 스키마를 마지막으로 읽었을 때 얻은 값)와 같은지 확인해요. P1은 데이터베이스 번호예요(메인 데이터베이스는 0). 이것은 데이터베이스 스키마가 다른 스레드에 의해 변경되지 않았음을 확인하기 위해 하는 일이에요. 변경되었다면 다시 읽어야 해요.
두 번째 Transaction 명령은 데이터베이스 1, 즉 임시 테이블용 데이터베이스에 대한 트랜잭션을 시작하고 롤백 저널을 시작해요.
3 Integer 0 0
4 OpenWrite 0 3 examp
Integer 명령은 정수 값 P1(0)을 스택에 push해요. 여기서 0은 다음 OpenWrite 명령에서 사용할 데이터베이스 번호예요. P3이 NULL이 아니면 같은 정수의 문자열 표현이에요. 이후 스택은 다음과 같아요:
(integer) 0
---
OpenWrite 명령은 루트 페이지가 P2(이 데이터베이스 파일에서 3)인 "examp" 테이블에 핸들 P1(이 경우 0)로 새 읽기/쓰기 커서를 열어요. 커서 핸들은 음이 아닌 임의의 정수일 수 있어요. 하지만 VDBE는 커서를 배열에 할당하는데, 그 배열 크기는 가장 큰 커서보다 하나 더 커요. 따라서 메모리를 아끼려면 0부터 시작해 연속적으로 위로 올라가는 핸들을 사용하는 것이 가장 좋아요. 여기서 P3("examp")은 열리는 테이블의 이름이지만 사용되지 않고, 코드를 더 읽기 쉽게 만들기 위해서만 생성돼요. 이 명령은 사용할 데이터베이스 번호(0, 메인 데이터베이스)를 스택 맨 위에서 pop해요. 그래서 이후 스택은 다시 비어 있어요.
5 NewRecno 0 0
NewRecno 명령은 커서 P1이 가리키는 테이블에 대한 새 정수 레코드 번호를 만들어요. 그 레코드 번호는 현재 테이블의 키로 사용되지 않는 것이에요. 새 레코드 번호는 스택에 push돼요. 이후 스택은 다음과 같아요:
(integer) new record key
---
6 String 0 0 Hello, World!
String 명령은 P3 피연산자를 스택에 push해요. 이후 스택은 다음과 같아요:
(string) "Hello, World!"
---
(integer) new record key
7 Integer 99 0 99
Integer 명령은 P1 피연산자(99)를 스택에 push해요. 이후 스택은 다음과 같아요:
(integer) 99
---
(string) "Hello, World!"
(integer) new record key
8 MakeRecord 2 0
MakeRecord 명령은 스택의 맨 위에서 P1개 요소를 pop하고(이 경우 2개) 데이터베이스 파일에 레코드를 저장하는 데 사용되는 이진 형식으로 변환해요. (세부 사항은 파일 형식 설명을 참고하세요.) MakeRecord 명령이 생성한 새 레코드는 스택에 다시 push돼요. 이후 스택은 다음과 같아요:
(record) "Hello, World!", 99
---
(integer) new record key
9 PutIntKey 0 1
PutIntKey 명령은 스택의 맨 위 2개 항목을 사용해 커서 P1이 가리키는 테이블에 항목을 써요. 항목이 이미 없으면 새 항목이 만들지고, 기존 항목의 데이터가 있으면 덮어써요. 레코드 데이터는 스택 맨 위 항목이고, 키는 그 아래 항목이에요. 이 명령은 스택을 두 번 pop해요. 피연산자 P2가 1이므로 행 변경 카운트가 증가하고 rowid가 저장되어 이후 sqlite_last_insert_rowid() 함수가 반환해요. P2가 0이면 행 변경 카운트는 수정되지 않아요. 삽입이 실제로 일어나는 곳이 바로 이 명령이에요.
10 Close 0 0
Close 명령은 이전에 P1(0, 유일한 열린 커서)로 열린 커서를 닫아요. P1이 현재 열려 있지 않으면 이 명령은 no-op이에요.
11 Commit 0 0
Commit 명령은 마지막 Transaction 이후 데이터베이스에 가해진 모든 수정을 실제로 적용하게 해요. 다른 트랜잭션이 시작될 때까지 추가 수정은 허용되지 않아요. Commit 명령은 저널 파일을 삭제하고 데이터베이스에 대한 쓰기 잠금을 해제해요. 여전히 열린 커서가 있으면 읽기 잠금은 계속 유지돼요.
12 Halt 0 0
Halt 명령은 VDBE 엔진이 즉시 종료하게 해요. 열린 모든 커서, List, Sort 등이 자동으로 닫혀요. P1은 sqlite_exec()가 반환하는 결과 코드예요. 정상적인 정지에서는 이 값이 SQLITE_OK(0)여야 해요. 오류에 대해서는 다른 값일 수 있어요. 피연산자 P2는 오류가 있을 때만 사용돼요. 모든 프로그램 끝에는 암시된 "Halt 0 0 0" 명령이 있고, VDBE가 프로그램을 실행할 준비를 할 때 덧붙여요.
VDBE 프로그램 실행 추적하기 (Tracing VDBE Program Execution)
SQLite 라이브러리가 NDEBUG 전처리기 매크로 없이 컴파일되면 PRAGMA vdbe_trace가 VDBE가 프로그램 실행을 추적하게 해요. 이 기능은 원래 테스트와 디버깅을 위한 것이었지만 VDBE가 어떻게 동작하는지 배우는 데도 유용해요. 추적을 켜려면 "PRAGMA vdbe_trace=ON;"을, 끄려면 "PRAGMA vdbe_trace=OFF"를 사용해요. 이렇게요:
sqlite> PRAGMA vdbe_trace=ON;
0 Halt 0 0
sqlite> INSERT INTO examp VALUES('Hello, World!',99);
0 Transaction 0 0
1 VerifyCookie 0 81
2 Transaction 1 0
3 Integer 0 0
Stack: i:0
4 OpenWrite 0 3 examp
5 NewRecno 0 0
Stack: i:2
6 String 0 0 Hello, World!
Stack: t[Hello,.World!] i:2
7 Integer 99 0 99
Stack: si:99 t[Hello,.World!] i:2
8 MakeRecord 2 0
Stack: s[...Hello,.World!.99] i:2
9 PutIntKey 0 1
10 Close 0 0
11 Commit 0 0
12 Halt 0 0
추적 모드가 켜져 있으면 VDBE는 각 명령을 실행하기 전에 출력해요. 명령이 실행된 후에는 스택의 맨 위 몇 개 항목이 표시돼요. 스택이 비어 있으면 스택 표시는 생략돼요.
스택 표시에서 대부분의 항목은 그 스택 항목의 데이터 타입을 알려주는 접두사와 함께 표시돼요. 정수는 "i:"로 시작해요. 부동 소수점 값은 "r:"로 시작해요. ("r"은 "real-number"의 약자예요.) 문자열은 "s:", "t:", "e:" 또는 "z:" 중 하나로 시작해요. 문자열 접두사 간의 차이는 메모리가 할당되는 방식에 따른 것이에요. z: 문자열은 **malloc()**에서 얻은 메모리에 저장돼요. t: 문자열은 정적으로 할당돼요. e: 문자열은 임시(ephemeral)예요. 다른 모든 문자열은 s: 접두사를 가져요. 이것은 관찰자인 우리에게는 차이가 없지만, z: 문자열은 pop될 때 메모리 누수를 피하기 위해 **free()**에 전달되어야 하므로 VDBE에게는 매우 중요해요. 문자열 값의 처음 10자만 표시되고 이진 값(예: MakeRecord 명령의 결과)은 문자열로 취급된다는 점에 주의하세요. VDBE 스택에 저장될 수 있는 유일한 다른 데이터 타입은 NULL이며, 접두사 없이 그냥 "NULL"로 표시돼요. 정수가 정수와 문자열로 동시에 스택에 놓이면 접두사는 "si:"이에요.
단순 쿼리 (Simple Queries)
이제 VDBE가 데이터베이스에 쓰는 방법의 기본을 이해해야 해요. 이제 쿼리를 어떻게 수행하는지 살펴봐요. 다음 단순 SELECT 문을 예로 사용할 거예요:
SELECT * FROM examp;
이 SQL 문에 대해 생성된 VDBE 프로그램은 다음과 같아요:
sqlite> EXPLAIN SELECT * FROM examp;
addr opcode p1 p2 p3
---- ----------- ---- ---- -----------------------------------
0 ColumnName 0 0 one
1 ColumnName 1 0 two
2 Integer 0 0
3 OpenRead 0 3 examp
4 VerifyCookie 0 81
5 Rewind 0 10
6 Column 0 0
7 Column 0 1
8 Callback 2 0
9 Next 0 6
10 Close 0 0
11 Halt 0 0
이 문제를 살펴보기 전에 SQLite에서 쿼리가 어떻게 동작하는지 간단히 복습해 무엇을 달성하려는지 알아봐요. 쿼리 결과의 각 행에 대해 SQLite는 다음 프로토타입의 콜백 함수를 호출해요:
int Callback(void *pUserData, int nColumn, char *azData[], char *azColumnName[]);
SQLite 라이브러리는 VDBE에 콜백 함수와 pUserData 포인터에 대한 포인터를 제공해요. (콜백과 사용자 데이터 모두 원래 sqlite_exec() API 함수의 인자로 전달되었어요.) VDBE의 역할은 nColumn, azData[], **azColumnName[]**의 값을 만들어내는 것이에요. nColumn은 물론 결과의 열 수예요. **azColumnName[]**은 각 문자열이 결과 열 중 하나의 이름인 문자열 배열이에요. **azData[]**는 실제 데이터를 담은 문자열 배열이에요.
0 ColumnName 0 0 one
1 ColumnName 1 0 two
쿼리에 대한 VDBE 프로그램의 처음 두 명령은 azColumn 값 설정과 관련돼요. ColumnName 명령은 azColumnName[] 배열의 각 요소에 어떤 값을 채울지 VDBE에 알려줘요. 모든 쿼리는 결과의 각 열마다 하나씩 ColumnName 명령으로 시작하고, 쿼리 뒤쪽에 각각에 대응하는 Column 명령이 있을 거예요.
2 Integer 0 0
3 OpenRead 0 3 examp
4 VerifyCookie 0 81
명령 2와 3은 쿼리할 데이터베이스 테이블에 읽기 커서를 열어요. 이것은 INSERT 예제의 OpenWrite 명령과 같은 방식으로 동작하지만, 이번에는 쓰기가 아니라 읽기 위해 커서가 열린다는 점이 달라요. 명령 4는 INSERT 예제에서처럼 데이터베이스 스키마를 검증해요.
5 Rewind 0 10
Rewind 명령은 "examp" 테이블을 순회하는 루프를 초기화해요. 커서 P1을 테이블의 첫 항목으로 되감아요. 이것은 커서를 사용해 테이블을 순회하는 Column과 Next 명령에 필요해요. 테이블이 비어 있으면 P2(10)로 점프하는데, 이것은 루프 바로 다음 명령이에요. 테이블이 비어 있지 않으면 루프 본문의 시작인 다음 명령 6으로 통과해요.
6 Column 0 0
7 Column 0 1
8 Callback 2 0
명령 6부터 8까지는 데이터베이스 파일의 각 레코드에 대해 한 번씩 실행될 루프의 본문을 형성해요. 주소 6과 7의 Column 명령은 각각 P1번째 커서에서 P2번째 열을 가져와 스택에 push해요. 이 예제에서 첫 Column 명령은 "one" 열의 값을 스택에 push하고 두 번째 Column 명령은 "two" 열의 값을 push해요. 주소 8의 Callback 명령은 callback() 함수를 호출해요. Callback의 P1 피연산자가 nColumn의 값이 돼요. Callback 명령은 스택에서 P1개 값을 pop하고 그것들을 사용해 azData[] 배열을 채워요.
9 Next 0 6
주소 9의 명령은 루프의 분기 부분을 구현해요. 주소 5의 Rewind와 함께 루프 논리를 형성해요. 이것은 주의 깊게 살펴봐야 할 핵심 개념이에요. Next 명령은 커서 P1을 다음 레코드로 진행해요. 커서 진행이 성공하면 P2(6, 루프 본문의 시작)로 즉시 점프해요. 커서가 끝에 있었다면 루프를 끝내는 다음 명령으로 통과해요.
10 Close 0 0
11 Halt 0 0
프로그램 끝의 Close 명령은 "examp" 테이블을 가리키는 커서를 닫아요. 프로그램이 정지할 때 VDBE가 모든 커서를 자동으로 닫기 때문에 여기서 Close를 호출할 필요는 정말 없어요. 하지만 Rewind가 점프할 대상 명령이 필요했으므로, 그 명령이 유용한 일을 하게 하는 것도 나쁘지 않아요. Halt 명령이 VDBE 프로그램을 끝내요.
이 SELECT 쿼리 프로그램에는 INSERT 예제에서 사용된 Transaction과 Commit 명령이 포함되지 않았다는 점에 주의하세요. SELECT는 데이터베이스를 변경하지 않는 읽기 작업이므로 트랜잭션이 필요 없어요.
약간 더 복잡한 쿼리 (A Slightly More Complex Query)
이전 예제의 핵심 포인트는 콜백 함수를 호출하는 Callback 명령의 사용과 데이터베이스 파일의 모든 레코드를 순회하는 루프를 구현하는 Next 명령의 사용이었어요. 이 예제는 더 많은 열(그중 일부는 계산된 값)을 포함하고, 어떤 레코드가 콜백 함수에 도달하는지 제한하는 WHERE 절이 있는 약간 더 복잡한 쿼리를 보여줌으로써 그 아이디어를 확실히 하려 해요. 다음 쿼리를 고려해요:
SELECT one, two, one || two AS 'both'
FROM examp
WHERE one LIKE 'H%'
이 쿼리는 다소 인위적일 수 있지만 우리의 요점을 설명하는 역할을 해요. 결과는 "one", "two", "both"라는 이름의 세 열을 가질 거예요. 처음 두 열은 테이블의 두 열을 직접 복사한 것이고, 세 번째 결과 열은 테이블의 첫 번째와 두 번째 열을 연결해 만든 문자열이에요. 마지막으로 WHERE 절은 "one" 열이 "H"로 시작하는 행만 결과로 선택한다고 말해요. 이 쿼리에 대한 VDBE 프로그램은 다음과 같아요:
addr opcode p1 p2 p3
---- ----------- ---- ---- -----------------------------------
0 ColumnName 0 0 one
1 ColumnName 1 0 two
2 ColumnName 2 0 both
3 Integer 0 0
4 OpenRead 0 3 examp
5 VerifyCookie 0 81
6 Rewind 0 18
7 String 0 0 H%
8 Column 0 0
9 Function 2 0 ptr(0x7f1ac0)
10 IfNot 1 17
11 Column 0 0
12 Column 0 1
13 Column 0 0
14 Column 0 1
15 Concat 2 0
16 Callback 3 0
17 Next 0 7
18 Close 0 0
19 Halt 0 0
WHERE 절을 제외하면 이 예제의 프로그램 구조는 이전 예제와 매우 비슷하고, 열 하나만 더 있을 뿐이에요. 이전에는 2개 대신 이제 3개 열이 있고 ColumnName 명령도 세 개예요. 이전 예제에서처럼 OpenRead 명령으로 커서가 열려요. 주소 6의 Rewind와 주소 17의 Next는 테이블의 모든 레코드를 순회하는 루프를 형성해요. 끝의 Close 명령은 Rewind가 끝났을 때 점프할 대상이 있게 하기 위한 것이에요. 이 모든 것은 첫 쿼리 시연과 똑같아요.
이 예제의 Callback 명령은 2개 대신 3개 결과 열에 대한 데이터를 생성해야 하지만, 그 외에는 첫 쿼리와 같아요. Callback 명령이 호출될 때 결과의 맨 왼쪽 열이 스택의 가장 아래에 있어야 하고 가장 오른쪽 결과 열이 스택 맨 위여야 해요. 주소 11부터 15까지에서 스택이 이렇게 설정되는 것을 볼 수 있어요. 11과 12의 Column 명령은 결과의 첫 두 열 값을 push해요. 13과 14의 두 Column 명령은 세 번째 결과 열을 계산하는 데 필요한 값을 가져오고, 15의 Concat 명령은 그것들을 스택의 단일 항목으로 결합해요.
이 예제에서 정말 새로운 유일한 것은 주소 7부터 10까지의 명령으로 구현되는 WHERE 절이에요. 주소 7과 8의 명령은 테이블의 "one" 열 값과 리터럴 문자열 "H%"를 스택에 push해요. 주소 9의 Function 명령은 스택에서 이 두 값을 pop하고 LIKE() 함수의 결과를 스택에 다시 push해요. IfNot 명령은 스택 맨 위 값을 pop하고 맨 위 값이 false이며("H%" 리터럴 문자열과 같지 않은) Next 명령으로 앞으로 즉시 점프하게 해요. 이 점프를 취하면 콜백을 효과적으로 건너뛰는데, 이것이 바로 WHERE 절의 핵심이에요. 비교 결과가 true이면 점프가 취해지지 않고 아래의 Callback 명령으로 제어가 통과해요.
LIKE 연산자가 어떻게 구현되는지 주목하세요. 그것은 SQLite에서 사용자 정의 함수라서 그 함수 정의의 주소가 P3에 지정돼요. 피연산자 P1은 스택에서 가져올 함수 인자 수예요. 이 경우 LIKE() 함수는 2개 인자를 받아요. 인자는 역순(오른쪽에서 왼쪽으로)으로 스택에서 빠지므로, 매칭할 패턴이 스택 맨 위 요소이고 다음 요소가 비교할 데이터예요. 반환 값은 스택에 push돼요.
SELECT 프로그램 템플릿 (A Template For SELECT Programs)
처음 두 쿼리 예제는 모든 SELECT 프로그램이 따를 일종의 템플릿을 보여줘요. 기본적으로 다음과 같아요:
- 콜백용 azColumnName[] 배열을 초기화한다.
- 쿼리할 테이블에 커서를 연다.
- 테이블의 각 레코드에 대해:
- WHERE 절이 FALSE로 평가되면 다음 단계를 건너뛰고 다음 레코드로 계속한다.
- 결과의 현재 행에 대한 모든 열을 계산한다.
- 결과의 현재 행에 대해 콜백 함수를 호출한다.
- 커서를 닫는다.
이 템플릿은 조인(join), 복합 select, 검색을 빠르게 하는 색인 사용, 정렬, GROUP BY와 HAVING 절이 있거나 없는 집계 함수 같은 추가 복잡성을 고려하면서 상당히 확장될 거예요. 하지만 같은 기본 아이디어가 계속 적용될 거예요.
UPDATE와 DELETE 문 (UPDATE And DELETE Statements)
UPDATE와 DELETE 문은 SELECT 문 템플릿과 매우 유사한 템플릿을 사용해 코딩돼요. 물론 주요 차이점은 끝 동작이 콜백 함수를 호출하는 대신 데이터베이스를 수정한다는 것이에요. 데이터베이스를 수정하므로 트랜잭션도 사용해요. DELETE 문부터 살펴봐요:
DELETE FROM examp WHERE two<50;
이 DELETE 문은 "two" 열이 50보다 작은 "examp" 테이블의 모든 레코드를 제거해요. 이것을 하기 위해 생성된 코드는 다음과 같아요:
addr opcode p1 p2 p3
---- ----------- ---- ---- -----------------------------------
0 Transaction 1 0
1 Transaction 0 0
2 VerifyCookie 0 178
3 Integer 0 0
4 OpenRead 0 3 examp
5 Rewind 0 12
6 Column 0 1
7 Integer 50 0 50
8 Ge 1 11
9 Recno 0 0
10 ListWrite 0 0
11 Next 0 6
12 Close 0 0
13 ListRewind 0 0
14 Integer 0 0
15 OpenWrite 0 3
16 ListRead 0 20
17 NotExists 0 19
18 Delete 0 1
19 Goto 0 16
20 ListReset 0 0
21 Close 0 0
22 Commit 0 0
23 Halt 0 0
프로그램이 해야 할 일은 다음과 같아요. 먼저 "examp" 테이블에서 삭제할 모든 레코드를 찾아야 해요. 이것은 위 SELECT 예제에서 사용된 루프와 매우 유사한 루프로 수행돼요. 모든 레코드를 찾은 다음에야 다시 돌아가 하나씩 삭제할 수 있어요. 각 레코드를 찾는 즉시 삭제할 수는 없다는 점에 주의하세요. 먼저 모든 레코드를 찾은 다음 돌아가 삭제해야 해요. SQLite 데이터베이스 백엔드가 삭제 연산 후에 스캔 순서를 바꿀 수 있기 때문이에요. 스캔 중간에 스캔 순서가 바뀌면 일부 레코드는 두 번 이상 방문되고 다른 레코드는 전혀 방문되지 않을 수 있어요.
따라서 DELETE 구현은 실제로 두 루프에 있어요. 첫 번째 루프(명령 511)는 삭제할 레코드를 찾고 그 키를 임시 목록에 저장해요. 두 번째 루프(명령 1619)는 키 목록을 사용해 레코드를 하나씩 삭제해요.
0 Transaction 1 0
1 Transaction 0 0
2 VerifyCookie 0 178
3 Integer 0 0
4 OpenRead 0 3 examp
명령 0부터 4까지는 INSERT 예제와 같아요. 메인 데이터베이스와 임시 데이터베이스에 대한 트랜잭션을 시작하고, 메인 데이터베이스의 스키마를 검증하며, "examp" 테이블에 읽기 커서를 열어요. 커서가 쓰기가 아닌 읽기용으로 열리는 것에 주목하세요. 프로그램의 이 단계에서는 테이블을 스캔만 하고 변경하지 않을 거예요. 같은 테이블을 나중에(명령 15에서) 쓰기 위해 다시 열 거예요.
5 Rewind 0 12
SELECT 예제에서처럼 Rewind 명령은 커서를 테이블의 시작으로 되감아 루프 본문에서 사용할 준비를 해요.
6 Column 0 1
7 Integer 50 0 50
8 Ge 1 11
WHERE 절은 명령 6~8로 구현돼요. where 절의 역할은 WHERE 조건이 false이면 ListWrite를 건너뛰는 것이에요. 이를 위해 (Column 명령이 추출한) "two" 열이 50보다 크거나 같으면 Next 명령으로 앞으로 점프해요.
이전처럼 Column 명령은 커서 P1을 사용하고 P2(1, "two" 열)에 있는 데이터 레코드를 스택에 push해요. Integer 명령은 값 50을 스택 맨 위에 push해요. 이 두 명령 후에 스택은 다음과 같아요:
(integer) 50
---
(record) current record for column "two"
Ge 연산자는 스택의 맨 위 두 요소를 비교하고 pop한 다음 비교 결과에 따라 분기해요. 두 번째 요소가 맨 위 요소보다 크거나 같으면 P2 주소(루프 끝의 Next 명령)로 점프해요. P1이 true이므로 어느 피연산자가 NULL이면(따라서 결과가 NULL이면) 점프를 취해요. 점프하지 않으면 다음 명령으로 그냥 진행해요.
9 Recno 0 0
10 ListWrite 0 0
Recno 명령은 커서 P1이 가리키는 테이블의 순차 스캔에서 현재 항목의 키의 처음 4바이트인 정수를 스택에 push해요. ListWrite 명령은 스택 맨 위의 정수를 임시 저장 목록에 쓰고 맨 위 요소를 pop해요. 이것이 이 루프의 중요한 작업인데, 삭제할 레코드의 키를 저장해 두 번째 루프에서 삭제할 수 있게 하는 것이에요. 이 ListWrite 명령 후에 스택은 다시 비어 있어요.
11 Next 0 6
12 Close 0 0
Next 명령은 커서 P0이 가리키는 테이블의 다음 요소를 가리키도록 커서를 증가시키고, 성공하면 P2(6, 루프 본문의 시작)로 분기해요. Close 명령은 커서 P1을 닫아요. 이것은 임시 저장 목록에 영향을 주지 않아요. 목록은 커서 P1과 연관되지 않고 대신 전역 작업 목록(ListPush로 저장 가능)이기 때문이에요.
13 ListRewind 0 0
ListRewind 명령은 임시 저장 목록을 시작으로 되감아요. 이것은 두 번째 루프에서 사용할 준비를 하는 것이에요.
14 Integer 0 0
15 OpenWrite 0 3
INSERT 예제에서처럼 데이터베이스 번호 P1(0, 메인 데이터베이스)을 스택에 push하고 OpenWrite로 테이블 P2(기본 페이지 3, "examp")에 커서 P1을 수정용으로 열어요.
16 ListRead 0 20
17 NotExists 0 19
18 Delete 0 1
19 Goto 0 16
이 루프는 실제 삭제를 수행해요. UPDATE 예제의 루프와 다르게 구성되어 있어요. ListRead 명령은 INSERT 루프에서 Next가 했던 역할을 하되, 실패 시 P2로 점프하고 Next는 성공 시 점프하므로, 루프 시작이 아니라 루프 시작에 둬요. 즉 루프 끝에 Goto를 두어 루프 시작의 루프 테스트로 다시 점프해야 해요. 따라서 이 루프는 C의 while(){...} 루프 형태를 갖는 반면, INSERT 예제의 루프는 do{...}while() 루프 형태를 가져요. Delete 명령은 이전 예제에서 콜백 함수가 했던 역할을 채워요.
ListRead 명령은 임시 저장 목록에서 요소를 읽고 스택에 push해요. 성공하면 다음 명령으로 계속해요. 목록이 비어서 실패하면 루프 바로 다음 명령인 P2로 분기해요. 이후 스택은 다음과 같아요:
(integer) key for current record
---
ListRead와 Next 명령 사이의 유사성에 주목하세요. 두 연산 모두 이 규칙에 따라 동작해요:
다음 "것"을 스택에 push하고 통과하거나, push할 다음 "것"이 있는지 여부에 따라 P2로 점프한다.
Next와 ListRead 사이의 한 차이는 "것"에 대한 개념이에요. Next 명령의 "것"은 데이터베이스 파일의 레코드예요. ListRead의 "것"은 목록의 정수 키예요. 또 다른 차이는 다음 "것"이 없을 때 점프할지 통과할지예요. 이 경우 Next는 통과하고 ListRead는 점프해요. 나중에 같은 원리로 동작하는 다른 루프 명령(NextIdx와 SortNext)을 볼 거예요.
NotExists 명령은 스택 맨 위 요소를 pop하고 그것을 정수 키로 사용해요. 그 키를 가진 레코드가 테이블 P1에 없으면 P2로 점프해요. 레코드가 있으면 다음 명령으로 통과해요. 이 경우 P2는 루프 끝의 Goto로 가는데, 그것은 시작의 ListRead로 다시 점프해요. P2를 루프 시작의 ListRead인 16으로 코딩할 수도 있었지만, 이 코드를 생성한 SQLite 파서는 그 최적화를 하지 않았어요.
Delete는 이 루프의 작업을 해요. 스택에서 정수 키를 pop하고(앞의 ListRead가 그곳에 넣었어요) 그 키를 가진 커서 P1의 레코드를 삭제해요. P2가 true이므로 행 변경 카운터가 증가해요.
Goto는 루프 시작으로 다시 점프해요. 이것이 루프의 끝이에요.
20 ListReset 0 0
21 Close 0 0
22 Commit 0 0
23 Halt 0 0
이 명령 블록은 VDBE 프로그램을 정리해요. 이 명령 중 세 개는 정말 필요하지 않지만, 더 복잡한 경우를 처리하도록 설계된 코드 템플릿에서 SQLite 파서가 생성해요.
ListReset 명령은 임시 저장 목록을 비워요. 이 목록은 VDBE 프로그램이 종료될 때 자동으로 비워지므로 이 경우에는 필요하지 않아요. Close 명령은 커서 P1을 닫아요. 다시 말하지만 VDBE 엔진이 이 프로그램 실행을 마칠 때 닫아요. Commit은 현재 트랜잭션을 성공적으로 끝내고, 이 트랜잭션에서 발생한 모든 변경을 데이터베이스에 저장하게 해요. 마지막 Halt도 불필요한데, 모든 VDBE 프로그램에 실행 준비될 때 추가되기 때문이에요.
UPDATE 문은 레코드를 삭제하는 대신 새 레코드로 바꾸는 것 외에는 DELETE 문과 매우 유사하게 동작해요. 다음 예제를 고려해요:
UPDATE examp SET one= '(' || one || ')' WHERE two < 50;
"two" 열이 50보다 작은 레코드를 삭제하는 대신, 이 문은 "one" 열을 괄호 안에 넣어요. 이 문을 구현하는 VDBE 프로그램은 다음과 같아요:
addr opcode p1 p2 p3
---- ----------- ---- ---- -----------------------------------
0 Transaction 1 0
1 Transaction 0 0
2 VerifyCookie 0 178
3 Integer 0 0
4 OpenRead 0 3 examp
5 Rewind 0 12
6 Column 0 1
7 Integer 50 0 50
8 Ge 1 11
9 Recno 0 0
10 ListWrite 0 0
11 Next 0 6
12 Close 0 0
13 Integer 0 0
14 OpenWrite 0 3
15 ListRewind 0 0
16 ListRead 0 28
17 Dup 0 0
18 NotExists 0 16
19 String 0 0 (
20 Column 0 0
21 Concat 2 0
22 String 0 0 )
23 Concat 2 0
24 Column 0 1
25 MakeRecord 2 0
26 PutIntKey 0 1
27 Goto 0 16
28 ListReset 0 0
29 Close 0 0
30 Commit 0 0
31 Halt 0 0
이 프로그램은 본질적으로 DELETE 프로그램과 같지만, 두 번째 루프의 본문이 레코드를 삭제하는 대신 갱신하는 일련의 명령(주소 17~26)으로 바뀌었어요. 이 명령 시퀀스의 대부분은 이미 익숙할 테지만 약간의 사소한 변화가 있으니 간단히 살펴볼게요. 또한 두 번째 루프 앞뒤의 일부 명령 순서가 바뀌었다는 점에 주의하세요. 이것은 SQLite 파서가 다른 템플릿을 사용해 코드를 출력하기로 선택한 방식일 뿐이에요.
두 번째 루프 안(명령 17)으로 들어갈 때 스택에는 수정하려는 레코드의 키인 단일 정수가 들어 있어요. 이 키를 두 번 사용해야 해요. 한 번은 레코드의 이전 값을 가져오기 위해, 두 번째는 수정된 레코드를 다시 쓰기 위해요. 따라서 첫 명령은 스택 맨 위의 키를 복제하는 Dup이에요. Dup 명령은 맨 위 요소뿐 아니라 스택의 어떤 요소도 복제할 수 있어요. 복제할 요소는 P1 피연산자로 지정해요. P1이 0이면 스택 맨 위가 복제돼요. P1이 1이면 스택 아래의 다음 요소가 복제돼요. 이런 식으로 이어져요.
키를 복제한 후 다음 명령 NotExists는 스택을 한 번 pop하고 pop된 값을 키로 사용해 데이터베이스 파일에서 레코드의 존재를 확인해요. 이 키에 대한 레코드가 없으면 ListRead로 다시 점프해 다른 키를 얻어요.
명령 19부터 25까지는 기존 레코드를 대체하는 데 사용될 새 데이터베이스 레코드를 구성해요. 이것은 INSERT 설명에서 본 것과 같은 종류의 코드이고 더 자세히 설명하지 않을게요. 명령 25를 실행한 후 스택은 다음과 같아요:
(record) new data record
---
(integer) key
PutIntKey 명령(INSERT 논의에서도 설명됨)은 데이터가 스택 맨 위이고 키가 스택의 다음 항목인 항목을 데이터베이스 파일에 쓰고 스택을 두 번 pop해요. PutIntKey 명령은 같은 키를 가진 기존 레코드의 데이터를 덮어쓰는데, 이것이 여기서 원하는 것이에요. INSERT에서는 덮어쓰기가 문제가 되지 않았어요. INSERT에서는 키가 이전에 사용된 적이 없는 키를 보장하는 NewRecno 명령에 의해 생성되었기 때문이에요.
CREATE와 DROP (CREATE and DROP)
CREATE 또는 DROP을 사용해 테이블이나 색인을 만들거나 파괴하는 것은, 적어도 VDBE 관점에서는 특수한 "sqlite_master" 테이블에서 INSERT 또는 DELETE를 하는 것과 같아요. sqlite_master 테이블은 모든 SQLite 데이터베이스에 자동으로 생성되는 특수 테이블이에요. 다음과 같아요:
CREATE TABLE sqlite_master (
type TEXT, -- either "table" or "index"
name TEXT, -- name of this table or index
tbl_name TEXT, -- for indices: name of associated table
sql TEXT -- SQL text of the original CREATE statement
)
"sqlite_master" 테이블 자체를 제외한 모든 테이블과 SQLite 데이터베이스의 모든 명명된 색인은 sqlite_master 테이블에 항목이 있어요. 다른 테이블처럼 SELECT 문으로 이 테이블을 쿼리할 수 있어요. 하지만 UPDATE, INSERT, DELETE로 이 테이블을 직접 변경하는 것은 허용되지 않아요. sqlite_master에 대한 변경은 CREATE와 DROP 명령으로만 발생해야 해요. 테이블과 색인이 추가되거나 파괴될 때 SQLite도 내부 데이터 구조 중 일부를 갱신해야 하기 때문이에요.
하지만 VDBE 관점에서 CREATE는 INSERT처럼, DROP은 DELETE처럼 동작해요. SQLite 라이브러리가 기존 데이터베이스를 열 때 가장 먼저 하는 일은 sqlite_master 테이블의 모든 항목에서 "sql" 열을 읽는 SELECT예요. "sql" 열은 원래 색인이나 테이블을 생성한 CREATE 문의 완전한 SQL 텍스트를 담고 있어요. 이 텍스트는 다시 SQLite 파서에 공급되어 색인이나 테이블을 설명하는 내부 데이터 구조를 재구성하는 데 사용돼요.
검색 속도를 높이기 위한 색인 사용 (Using Indexes To Speed Searching)
위 예제 쿼리에서 결과에 들어가는 행이 소수에 불과해도, 쿼리되는 테이블의 모든 행을 디스크에서 로드해 검사해야 해요. 큰 테이블에서는 오래 걸릴 수 있어요. 속도를 높이기 위해 SQLite는 색인을 사용할 수 있어요.
SQLite 파일은 키를 일부 데이터와 연관지어요. SQLite 테이블의 경우 데이터베이스 파일은 키가 정수이고 데이터가 테이블의 한 행에 대한 정보가 되도록 설정돼요. SQLite의 색인은 이 배열을 뒤집어요. 색인 키는 (일부) 저장되는 정보이고 색인 데이터는 정수예요. 특정 내용을 가진 테이블 행에 접근하려면 먼저 색인 테이블에서 내용을 찾아 정수 색인을 찾은 다음 그 정수로 테이블에서 완전한 레코드를 찾아요.
SQLite는 정렬된 데이터 구조인 b-tree를 사용하므로, SELECT 문의 WHERE 절에 동등성이나 부등성에 대한 검사가 있으면 색인을 사용할 수 있다는 점에 주의하세요. 다음 같은 쿼리는 색인이 있으면 사용할 수 있어요:
SELECT * FROM examp WHERE two==50;
SELECT * FROM examp WHERE two<50;
SELECT * FROM examp WHERE two IN (50, 100);
"examp" 테이블의 "two" 열을 정수로 매핑하는 색인이 있으면 SQLite는 그 색인을 사용해 two 열에 50이라는 값을 가진 examp의 모든 행이나 50보다 작은 모든 행의 정수 키를 찾을 거예요. 하지만 다음 쿼리는 색인을 사용할 수 없어요:
SELECT * FROM examp WHERE two%50 == 10;
SELECT * FROM examp WHERE two&127 == 3;
SQLite 파서가 가능하더라도 항상 색인을 사용하는 코드를 생성하지는 않는다는 점에 주의하세요. 다음 쿼리는 현재 색인을 사용하지 않을 거예요:
SELECT * FROM examp WHERE two+10 == 50;
SELECT * FROM examp WHERE two==50 OR two==100;
색인이 어떻게 동작하는지 더 잘 이해하기 위해 먼저 색인이 어떻게 생성되는지 살펴봐요. examp 테이블의 two 열에 색인을 만들어볼게요:
CREATE INDEX examp_idx1 ON examp(two);
위 문에 의해 생성된 VDBE 코드는 다음과 같아요:
addr opcode p1 p2 p3
---- ----------- ---- ---- -----------------------------------
0 Transaction 1 0
1 Transaction 0 0
2 VerifyCookie 0 178
3 Integer 0 0
4 OpenWrite 0 2
5 NewRecno 0 0
6 String 0 0 index
7 String 0 0 examp_idx1
8 String 0 0 examp
9 CreateIndex 0 0 ptr(0x791380)
10 Dup 0 0
11 Integer 0 0
12 OpenWrite 1 0
13 String 0 0 CREATE INDEX examp_idx1 ON examp(tw
14 MakeRecord 5 0
15 PutIntKey 0 0
16 Integer 0 0
17 OpenRead 2 3 examp
18 Rewind 2 24
19 Recno 2 0
20 Column 2 1
21 MakeIdxKey 1 0 n
22 IdxPut 1 0 indexed columns are not unique
23 Next 2 19
24 Close 2 0
25 Close 1 0
26 Integer 333 0
27 SetCookie 0 0
28 Close 0 0
29 Commit 0 0
30 Halt 0 0
sqlite_master를 제외한 모든 테이블과 모든 명명된 색인은 sqlite_master 테이블에 항목이 있다는 것을 기억하세요. 새 색인을 만들고 있으므로 sqlite_master에 새 항목을 추가해야 해요. 이것은 명령 315가 처리해요. sqlite_master에 항목을 추가하는 것은 다른 INSERT 문처럼 동작하므로 여기서 더 말하지 않을게요. 이 예제에서는 명령 1623에서 일어나는 새 색인에 유효한 데이터를 채우는 것에 초점을 맞출 거예요.
16 Integer 0 0
17 OpenRead 2 3 examp
가장 먼저 일어나는 일은 색인될 테이블을 읽기 위해 여는 것이에요. 테이블에 대한 색인을 구성하려면 테이블에 무엇이 있는지 알아야 해요. 색인은 이미 명령 3과 4에 의해 커서 0으로 쓰기용으로 열려 있어요.
18 Rewind 2 24
19 Recno 2 0
20 Column 2 1
21 MakeIdxKey 1 0 n
22 IdxPut 1 0 indexed columns are not unique
23 Next 2 19
명령 18~23은 색인될 테이블의 모든 행을 순회하는 루프를 구현해요. 각 테이블 행에 대해 먼저 명령 19에서 Recno로 그 행의 정수 키를 추출한 다음 명령 20에서 Column으로 "two" 열의 값을 얻어요. 21의 MakeIdxKey 명령은 "two" 열(스택 맨 위에 있음)의 데이터를 유효한 색인 키로 변환해요. 단일 열 색인의 경우 이것은 기본적으로 no-op이에요. 하지만 MakeIdxKey의 P1 피연산자가 1보다 크면 여러 항목이 스택에서 pop되어 단일 색인 키로 변환됐을 거예요. 22의 IdxPut 명령이 실제로 색인 항목을 만드는 것이에요. IdxPut은 스택에서 두 요소를 pop해요. 스택 맨 위는 색인 테이블에서 항목을 가져오는 키로 사용돼요. 그런 다음 스택에서 두 번째였던 정수가 그 색인의 정수 집합에 추가되고 새 레코드가 데이터베이스 파일에 다시 쓰여져요. 두 개 이상의 테이블 항목이 two 열에 같은 값을 가지면 같은 색인 항목이 여러 정수를 저장할 수 있다는 점에 주의하세요.
이제 이 색인이 어떻게 사용될지 살펴봐요. 다음 쿼리를 고려해요:
SELECT * FROM examp WHERE two==50;
SQLite는 이 쿼리를 처리하기 위해 다음 VDBE 코드를 생성해요:
addr opcode p1 p2 p3
---- ----------- ---- ---- -----------------------------------
0 ColumnName 0 0 one
1 ColumnName 1 0 two
2 Integer 0 0
3 OpenRead 0 3 examp
4 VerifyCookie 0 256
5 Integer 0 0
6 OpenRead 1 4 examp_idx1
7 Integer 50 0 50
8 MakeKey 1 0 n
9 MemStore 0 0
10 MoveTo 1 19
11 MemLoad 0 0
12 IdxGT 1 19
13 IdxRecno 1 0
14 MoveTo 0 0
15 Column 0 0
16 Column 0 1
17 Callback 2 0
18 Next 1 11
19 Close 0 0
20 Close 1 0
21 Halt 0 0
SELECT는 익숙한 방식으로 시작해요. 먼저 열 이름이 초기화되고 쿼리되는 테이블이 열려요. 명령 5와 6부터 색인 파일도 열리면서 상황이 달라져요. 명령 7과 8은 값 50으로 키를 만들어요. 9의 MemStore 명령은 색인 키를 VDBE 메모리 위치 0에 저장해요. VDBE 메모리는 스택 깊은 곳에서 값을 가져오는 것을 피하기 위해 사용돼요. 스택 깊은 곳에서 가져오는 것도 할 수는 있지만 프로그램 생성이 더 어려워져요. 주소 10의 다음 명령 MoveTo는 스택에서 키를 pop하고 색인 커서를 그 키를 가진 색인의 첫 행으로 이동해요. 이것은 다음 루프에서 사용할 커서를 초기화해요.
명령 11~18은 명령 8이 가져온 키를 가진 모든 색인 레코드에 대한 루프를 구현해요. 이 키를 가진 모든 색인 레코드는 색인 테이블에서 연속적이므로, 그것들을 걸어가며 색인에서 해당 테이블 키를 가져와요. 이 테이블 키는 그런 다음 커서를 테이블의 그 행으로 이동하는 데 사용돼요. 루프의 나머지는 색인이 없는 SELECT 쿼리의 루프와 같아요.
루프는 스택에 색인 키의 사본을 push하는 11의 MemLoad 명령으로 시작해요. 12의 IdxGT 명령은 키를 커서 P1이 가리키는 현재 색인 레코드의 키와 비교해요. 현재 커서 위치의 색인 키가 찾고 있는 색인보다 크면 루프 밖으로 점프해요.
13의 IdxRecno 명령은 색인에서 테이블 레코드 번호를 스택에 push해요. 다음 MoveTo는 그것을 pop하고 테이블 커서를 그 행으로 이동해요. 다음 3개 명령은 색인이 없는 경우와 같은 방식으로 열 데이터를 선택해요. Column 명령이 열 데이터를 가져오고 콜백 함수가 호출돼요. 마지막 Next 명령은 테이블 커서가 아니라 색인 커서를 다음 행으로 진행시키고, 색인 레코드가 남아 있으면 루프 시작으로 다시 분기해요.
색인이 테이블에서 값을 찾는 데 사용되므로 색인과 테이블이 일관되게 유지되는 것이 중요해요. 이제 examp 테이블에 색인이 있으므로 examp 테이블에 데이터가 삽입, 삭제, 변경될 때마다 그 색인을 갱신해야 해요. 위의 첫 예제에서 "examp" 테이블에 새 행을 12개의 VDBE 명령으로 삽입할 수 있었던 것을 기억하세요. 이제 이 테이블에 색인이 있으므로 19개의 명령이 필요해요. SQL 문은 이래요:
INSERT INTO examp VALUES('Hello, World!',99);
그리고 생성된 코드는 다음과 같아요:
addr opcode p1 p2 p3
---- ----------- ---- ---- -----------------------------------
0 Transaction 1 0
1 Transaction 0 0
2 VerifyCookie 0 256
3 Integer 0 0
4 OpenWrite 0 3 examp
5 Integer 0 0
6 OpenWrite 1 4 examp_idx1
7 NewRecno 0 0
8 String 0 0 Hello, World!
9 Integer 99 0 99
10 Dup 2 1
11 Dup 1 1
12 MakeIdxKey 1 0 n
13 IdxPut 1 0
14 MakeRecord 2 0
15 PutIntKey 0 1
16 Close 0 0
17 Close 1 0
18 Commit 0 0
19 Halt 0 0
이 시점에서 위 프로그램이 어떻게 동작하는지 스스로 알아낼 수 있을 만큼 VDBE를 충분히 이해해야 해요. 따라서 본문에서 더 논의하지 않을게요.
조인 (Joins)
조인에서는 두 개 이상의 테이블이 결합되어 단일 결과를 생성해요. 결과 테이블은 조인되는 테이블의 행들의 가능한 모든 조합으로 구성돼요. 이것을 구현하는 가장 쉽고 자연스러운 방법은 중첩 루프예요.
위에서 논의한 테이블의 모든 레코드를 검색하는 단일 루프가 있던 쿼리 템플릿을 기억하세요. 조인에서는 기본적으로 같은 것이지만 중첩 루프가 있어요. 예를 들어 두 테이블을 조인하려면 쿼리 템플릿이 다음과 같을 수 있어요:
- 콜백용 azColumnName[] 배열을 초기화한다.
- 쿼리되는 두 테이블 각각에 커서를 연다.
- 첫 번째 테이블의 각 레코드에 대해:
- 두 번째 테이블의 각 레코드에 대해:
- WHERE 절이 FALSE로 평가되면 다음 단계를 건너뛰고 다음 레코드로 계속한다.
- 결과의 현재 행에 대한 모든 열을 계산한다.
- 결과의 현재 행에 대해 콜백 함수를 호출한다.
- 두 번째 테이블의 각 레코드에 대해:
- 두 커서를 모두 닫는다.
이 템플릿은 동작하지만 O(N2) 루프를 다루고 있으므로 느릴 가능성이 커요. 하지만 WHERE 절을 항들로 인수분해할 수 있고, 그 항 중 하나 이상이 첫 번째 테이블의 열만 포함하는 경우가 흔해요. 그 경우 WHERE 절 테스트의 일부를 안쪽 루프 밖으로 인수분해할 수 있고 많은 효율을 얻어요. 따라서 더 나은 템플릿은 다음과 같을 거예요:
- 콜백용 azColumnName[] 배열을 초기화한다.
- 쿼리되는 두 테이블 각각에 커서를 연다.
- 첫 번째 테이블의 각 레코드에 대해:
- 첫 번째 테이블의 열만 포함하는 WHERE 절의 항들을 평가한다. 어떤 항이 false이면(전체 WHERE 절이 false여야 함을 의미) 이 루프의 나머지를 건너뛰고 다음 레코드로 계속한다.
- 두 번째 테이블의 각 레코드에 대해:
- WHERE 절이 FALSE로 평가되면 다음 단계를 건너뛰고 다음 레코드로 계속한다.
- 결과의 현재 행에 대한 모든 열을 계산한다.
- 결과의 현재 행에 대해 콜백 함수를 호출한다.
- 두 커서를 모두 닫는다.
두 루프 중 하나 또는 둘 다의 검색을 빠르게 하기 위해 색인을 사용할 수 있다면 추가 속도 향상이 발생할 수 있어요.
SQLite는 항상 SELECT 문의 FROM 절에 테이블이 나타나는 순서대로 루프를 구성해요. 가장 왼쪽 테이블이 바깥쪽 루프가 되고 가장 오른쪽 테이블이 안쪽 루프가 돼요. 이론적으로는 일부 상황에서 루프를 재정렬해 조인 평가를 빠르게 할 수 있어요. 하지만 SQLite는 이 최적화를 시도하지 않아요.
SQLite가 중첩 루프를 어떻게 구성하는지 다음 예제에서 볼 수 있어요:
CREATE TABLE examp2(three int, four int);
SELECT * FROM examp, examp2 WHERE two<50 AND four==two;
addr opcode p1 p2 p3
---- ----------- ---- ---- -----------------------------------
0 ColumnName 0 0 examp.one
1 ColumnName 1 0 examp.two
2 ColumnName 2 0 examp2.three
3 ColumnName 3 0 examp2.four
4 Integer 0 0
5 OpenRead 0 3 examp
6 VerifyCookie 0 909
7 Integer 0 0
8 OpenRead 1 5 examp2
9 Rewind 0 24
10 Column 0 1
11 Integer 50 0 50
12 Ge 1 23
13 Rewind 1 23
14 Column 1 1
15 Column 0 1
16 Ne 1 22
17 Column 0 0
18 Column 0 1
19 Column 1 0
20 Column 1 1
21 Callback 4 0
22 Next 1 14
23 Next 0 10
24 Close 0 0
25 Close 1 0
26 Halt 0 0
examp 테이블에 대한 바깥쪽 루프는 명령 723으로 구현돼요. 안쪽 루프는 명령 1322예요. WHERE 식의 "two<50" 항이 첫 번째 테이블의 열만 포함하고 안쪽 루프 밖으로 인수분해될 수 있다는 것에 주목하세요. SQLite는 이렇게 하고 "two<50" 테스트를 명령 1012에서 구현해요. "four==two" 테스트는 안쪽 루프의 명령 1416으로 구현돼요.
SQLite는 조인의 테이블에 임의의 제한을 두지 않아요. 테이블이 자신과 조인되는 것도 허용해요.
ORDER BY 절 (The ORDER BY clause)
역사적인 이유와 효율을 위해 현재 모든 정렬은 메모리에서 이루어져요.
SQLite는 sorter라는 객체를 제어하는 특별한 명령 집합으로 ORDER BY 절을 구현해요. 쿼리의 가장 안쪽 루프에서 보통 Callback 명령이 있던 자리에, 콜백 매개변수와 키를 모두 포함하는 레코드가 구성돼요. 이 레코드는 sorter(연결 리스트)에 추가돼요. 쿼리 루프가 끝난 후 레코드 목록이 정렬되고 이 목록이 걸어져요. 목록의 각 레코드에 대해 콜백이 호출돼요. 마지막으로 sorter가 닫히고 메모리가 해제돼요.
다음 쿼리에서 이 과정이 동작하는 것을 볼 수 있어요:
SELECT * FROM examp ORDER BY one DESC, two;
addr opcode p1 p2 p3
---- ----------- ---- ---- -----------------------------------
0 ColumnName 0 0 one
1 ColumnName 1 0 two
2 Integer 0 0
3 OpenRead 0 3 examp
4 VerifyCookie 0 909
5 Rewind 0 14
6 Column 0 0
7 Column 0 1
8 SortMakeRec 2 0
9 Column 0 0
10 Column 0 1
11 SortMakeKey 2 0 D+
12 SortPut 0 0
13 Next 0 6
14 Close 0 0
15 Sort 0 0
16 SortNext 0 19
17 SortCallback 2 0
18 Goto 0 16
19 SortReset 0 0
20 Halt 0 0
sorter 객체는 하나뿐이라 열거나 닫는 명령이 없어요. 필요할 때 자동으로 열리고 VDBE 프로그램이 정지할 때 닫혀요.
쿼리 루프는 명령 513으로 만들어져요. 명령 68은 단일 콜백 호출에 대한 azData[] 값을 포함하는 레코드를 만들어요. 명령 9~11이 정렬 키를 생성해요. 명령 12는 호출 레코드와 정렬 키를 단일 항목으로 결합하고 그 항목을 정렬 목록에 넣어요.
명령 11의 P3 인자가 특히 흥미로워요. 정렬 키는 P3의 한 문자를 각 문자열 앞에 붙이고 모든 문자열을 연결해 형성돼요. 정렬 비교 함수는 이 문자를 보고 정렬 순서가 오름차순인지 내림차순인지, 문자열로 정렬할지 숫자로 정렬할지 결정해요. 이 예제에서 첫 번째 열은 내림차순으로 문자열로 정렬되어야 하므로 접두사가 "D"이고, 두 번째 열은 오름차순으로 숫자로 정렬되어야 하므로 접두사가 "+"예요. 오름차순 문자열 정렬은 "A"를, 내림차순 숫자 정렬은 "-"를 사용해요.
쿼리 루프가 끝난 후 쿼리되는 테이블이 명령 14에서 닫혀요. 이것은 원하면 다른 프로세스나 스레드가 그 테이블에 접근할 수 있도록 일찍 하는 것이에요. 쿼리 루프 안에서 만들어진 레코드 목록은 명령 15에 의해 정렬돼요. 명령 16~18은 (이제 정렬된) 레코드 목록을 걸으며 각 레코드에 대해 한 번 콜백을 호출해요. 마지막으로 sorter가 명령 19에서 닫혀요.
집계 함수와 GROUP BY 및 HAVING 절 (Aggregate Functions And The GROUP BY and HAVING Clauses)
집계 함수를 계산하기 위해 VDBE는 특수 데이터 구조와 그 데이터 구조를 제어하는 명령을 구현해요. 그 데이터 구조는 정렬되지 않은 버킷 집합이며, 각 버킷은 키와 하나 이상의 메모리 위치를 가져요. 쿼리 루프 안에서 GROUP BY 절이 키를 구성하는 데 사용되고 그 키를 가진 버킷이 초점(focus)으로 옴겨져요. 이전에 없던 키라면 새 버킷이 생성돼요. 버킷이 초점이 되면 버킷의 메모리 위치가 다양한 집계 함수의 값을 누적하는 데 사용돼요. 쿼리 루프가 끝난 후 각 버킷이 한 번씩 방문되어 결과의 한 행을 생성해요.
예제가 이 개념을 명확히 하는 데 도움이 될 거예요. 다음 쿼리를 고려해요:
SELECT three, min(three+four)+avg(four)
FROM examp2
GROUP BY three;
이 쿼리에 대해 생성된 VDBE 코드는 다음과 같아요:
addr opcode p1 p2 p3
---- ----------- ---- ---- -----------------------------------
0 ColumnName 0 0 three
1 ColumnName 1 0 min(three+four)+avg(four)
2 AggReset 0 3
3 AggInit 0 1 ptr(0x7903a0)
4 AggInit 0 2 ptr(0x790700)
5 Integer 0 0
6 OpenRead 0 5 examp2
7 VerifyCookie 0 909
8 Rewind 0 23
9 Column 0 0
10 MakeKey 1 0 n
11 AggFocus 0 14
12 Column 0 0
13 AggSet 0 0
14 Column 0 0
15 Column 0 1
16 Add 0 0
17 Integer 1 0
18 AggFunc 0 1 ptr(0x7903a0)
19 Column 0 1
20 Integer 2 0
21 AggFunc 0 1 ptr(0x790700)
22 Next 0 9
23 Close 0 0
24 AggNext 0 31
25 AggGet 0 0
26 AggGet 0 1
27 AggGet 0 2
28 Add 0 0
29 Callback 2 0
30 Goto 0 24
31 Noop 0 0
32 Halt 0 0
첫 번째로 눈에 띄는 명령은 2의 AggReset이에요. AggReset 명령은 버킷 집합을 빈 집합으로 초기화하고 각 버킷에서 사용 가능한 메모리 슬롯 수를 P2로 지정해요. 이 예제에서 각 버킷은 3개의 메모리 슬롯을 보유할 거예요. 분명하지는 않지만 프로그램의 나머지를 자세히 보면 각 슬롯이 무엇을 위한 것인지 알아낼 수 있어요.
| 메모리 슬롯 | 이 메모리 슬롯의 의도된 용도 |
|---|---|
| 0 | "three" 열 -- 버킷의 키 |
| 1 | 최소 "three+four" 값 |
| 2 | 모든 "four" 값의 합. "avg(four)"를 계산하는 데 사용됨. |
쿼리 루프는 명령 822로 구현돼요. GROUP BY 절이 지정한 집계 키는 명령 9와 10으로 계산돼요. 명령 11은 적절한 버킷을 초점으로 가져와요. 주어진 키를 가진 버킷이 이미 없으면 새 버킷이 생성되고 버킷을 초기화하는 명령 12와 13으로 제어가 통과해요. 버킷이 이미 존재하면 명령 14로 점프해요. 집계 함수의 값은 명령 11과 21 사이의 명령들로 갱신돼요. 명령 1418은 메모리 슬롯 1을 다음 값 "min(three+four)"을 담도록 갱신해요. 그다음 "four" 열의 합은 명령 19~21로 갱신돼요.
쿼리 루프가 끝난 후 "examp2" 테이블은 명령 23에서 닫혀서 그 잠금이 해제되고 다른 스레드나 프로세스가 사용할 수 있어요. 다음 단계는 모든 집계 버킷을 순회하며 각 버킷에 대해 결과 한 행을 출력하는 것이에요. 이것은 명령 2430의 루프로 수행돼요. 24의 AggNext 명령은 다음 버킷을 초점으로 가져오거나, 모든 버킷을 이미 검사했으면 루프 끝으로 점프해요. 결과의 3개 열은 명령 2527에서 집계 버킷에서 순서대로 가져와져요. 마지막으로 콜백이 명령 29에서 호출돼요.
요약하면, 집계 함수가 있는 모든 쿼리는 두 루프로 구현돼요. 첫 번째 루프는 입력 테이블을 스캔하고 집계 정보를 버킷에 계산하고, 두 번째 루프는 모든 버킷을 스캔해 최종 결과를 계산해요.
집계 쿼리가 실제로 두 개의 연속 루프라는 깨달음은 SQL 쿼리 문에서 WHERE 절과 HAVING 절의 차이를 이해하는 것을 훨씬 쉽게 만들어줘요. WHERE 절은 첫 번째 루프에 대한 제한이고 HAVING 절은 두 번째 루프에 대한 제한이에요. 예제 쿼리에 WHERE와 HAVING 절을 모두 추가하면 이것을 볼 수 있어요:
SELECT three, min(three+four)+avg(four)
FROM examp2
WHERE three>four
GROUP BY three
HAVING avg(four)<10;
addr opcode p1 p2 p3
---- ----------- ---- ---- -----------------------------------
0 ColumnName 0 0 three
1 ColumnName 1 0 min(three+four)+avg(four)
2 AggReset 0 3
3 AggInit 0 1 ptr(0x7903a0)
4 AggInit 0 2 ptr(0x790700)
5 Integer 0 0
6 OpenRead 0 5 examp2
7 VerifyCookie 0 909
8 Rewind 0 26
9 Column 0 0
10 Column 0 1
11 Le 1 25
12 Column 0 0
13 MakeKey 1 0 n
14 AggFocus 0 17
15 Column 0 0
16 AggSet 0 0
17 Column 0 0
18 Column 0 1
19 Add 0 0
20 Integer 1 0
21 AggFunc 0 1 ptr(0x7903a0)
22 Column 0 1
23 Integer 2 0
24 AggFunc 0 1 ptr(0x790700)
25 Next 0 9
26 Close 0 0
27 AggNext 0 37
28 AggGet 0 2
29 Integer 10 0 10
30 Ge 1 27
31 AggGet 0 0
32 AggGet 0 1
33 AggGet 0 2
34 Add 0 0
35 Callback 2 0
36 Goto 0 27
37 Noop 0 0
38 Halt 0 0
이 마지막 예제에서 생성된 코드는 추가 WHERE와 HAVING 절을 구현하는 데 사용된 두 개의 조건부 점프가 추가된 것 외에는 이전과 같아요. WHERE 절은 쿼리 루프의 명령 911로 구현돼요. HAVING 절은 출력 루프의 명령 2830으로 구현돼요.
식의 항으로 SELECT 문 사용하기 (Using SELECT Statements As Terms In An Expression)
"구조적 질의 언어(Structured Query Language)"라는 이름 자체가 SQL이 중첩 쿼리를 지원해야 한다고 말해줘요. 실제로 두 가지 종류의 중첩이 지원돼요. 단일 행, 단일 열 결과를 반환하는 모든 SELECT 문은 다른 SELECT 문의 식에서 항으로 사용될 수 있어요. 그리고 단일 열, 다중 행 결과를 반환하는 SELECT 문은 IN과 NOT IN 연산자의 오른쪽 피연산자로 사용될 수 있어요. 이 절은 첫 번째 종류의 중첩 예제로 시작할게요. 단일 행, 단일 열 SELECT가 다른 SELECT의 식에서 항으로 사용되는 경우예요. 예제는 다음과 같아요:
SELECT * FROM examp
WHERE two!=(SELECT three FROM examp2
WHERE four=5);
SQLite가 이것을 다루는 방식은 먼저 내부 SELECT(examp2에 대한 것)를 실행하고 그 결과를 개인 메모리 셀에 저장하는 것이에요. 그런 다음 SQLite는 외부 SELECT를 평가할 때 내부 SELECT를 이 개인 메모리 셀의 값으로 대체해요. 코드는 다음과 같아요:
addr opcode p1 p2 p3
---- ----------- ---- ---- -----------------------------------
0 String 0 0
1 MemStore 0 1
2 Integer 0 0
3 OpenRead 1 5 examp2
4 VerifyCookie 0 909
5 Rewind 1 13
6 Column 1 1
7 Integer 5 0 5
8 Ne 1 12
9 Column 1 0
10 MemStore 0 1
11 Goto 0 13
12 Next 1 6
13 Close 1 0
14 ColumnName 0 0 one
15 ColumnName 1 0 two
16 Integer 0 0
17 OpenRead 0 3 examp
18 Rewind 0 26
19 Column 0 1
20 MemLoad 0 0
21 Eq 1 25
22 Column 0 0
23 Column 0 1
24 Callback 2 0
25 Next 0 19
26 Close 0 0
27 Halt 0 0
개인 메모리 셀은 처음 두 명령에 의해 NULL로 초기화돼요. 명령 2~13은 examp2 테이블에 대한 내부 SELECT 문을 구현해요. 결과를 콜백으로 보내거나 sorter에 저장하는 대신, 쿼리 결과가 명령 10에 의해 메모리 셀에 push되고 명령 11의 점프에 의해 루프가 버려진다는 것에 주목하세요. 명령 11의 점프는 흔적(vestigial)이고 결코 실행되지 않아요.
외부 SELECT는 명령 1425로 구현돼요. 특히 중첩 select를 포함하는 WHERE 절은 명령 1921로 구현돼요. 내부 select의 결과가 명령 20에 의해 스택에 로드되고 21의 조건부 점프가 사용하는 것을 볼 수 있어요.
하위 select의 결과가 스칼라이면 이전 예제에서 보인 것처럼 단일 개인 메모리 셀을 사용할 수 있어요. 하지만 하위 select의 결과가 벡터일 때, 예를 들어 하위 select가 IN이나 NOT IN의 오른쪽 피연산자일 때는 다른 접근 방식이 필요해요. 이 경우 하위 select의 결과가 일시적(transient) 테이블에 저장되고 그 테이블의 내용이 Found 또는 NotFound 연산자로 테스트돼요. 다음 예제를 고려해요:
SELECT * FROM examp
WHERE two IN (SELECT three FROM examp2);
이 마지막 쿼리를 구현하기 위해 생성된 코드는 다음과 같아요:
addr opcode p1 p2 p3
---- ----------- ---- ---- -----------------------------------
0 OpenTemp 1 1
1 Integer 0 0
2 OpenRead 2 5 examp2
3 VerifyCookie 0 909
4 Rewind 2 10
5 Column 2 0
6 IsNull -1 9
7 String 0 0
8 PutStrKey 1 0
9 Next 2 5
10 Close 2 0
11 ColumnName 0 0 one
12 ColumnName 1 0 two
13 Integer 0 0
14 OpenRead 0 3 examp
15 Rewind 0 25
16 Column 0 1
17 NotNull -1 20
18 Pop 1 0
19 Goto 0 24
20 NotFound 1 24
21 Column 0 0
22 Column 0 1
23 Callback 2 0
24 Next 0 16
25 Close 0 0
26 Halt 0 0
내부 SELECT의 결과가 저장되는 일시적 테이블은 0의 OpenTemp 명령으로 생성돼요. 이 opcode는 단일 SQL 문의 기간 동안만 존재하는 테이블에 사용돼요. 일시적 커서는 메인 데이터베이스가 읽기 전용이어도 항상 읽기/쓰기로 열려요. 일시적 테이블은 커서가 닫힐 때 자동으로 삭제돼요. P2 값 1은 커서가 데이터는 없지만 임의의 키를 가질 수 있는 BTree 색인을 가리킨다는 뜻이에요.
내부 SELECT 문은 명령 1~10으로 구현돼요. 이 코드가 하는 일은 "three" 열에 NULL이 아닌 값이 있는 examp2 테이블의 각 행에 대해 일시적 테이블에 항목을 만드는 것뿐이에요. 각 일시적 테이블 항목의 키는 examp2의 "three" 열이고 데이터는 결코 사용되지 않으므로 빈 문자열이에요.
외부 SELECT는 명령 11~25로 구현돼요. 특히 IN 연산자를 포함하는 WHERE 절은 명령 16, 17, 20으로 구현돼요. 명령 16은 현재 행의 "two" 열 값을 스택에 push하고 명령 17은 그것이 NULL이 아닌지 검사해요. 성공하면 20으로 점프하는데, 거기서 스택 맨 위가 일시적 테이블의 어떤 키와 일치하는지 테스트해요. 나머지 코드는 앞에서 본 것과 같아요.
복합 SELECT 문 (Compound SELECT Statements)
SQLite는 또한 UNION, UNION ALL, INTERSECT, EXCEPT 연산자를 사용해 두 개 이상의 SELECT 문을 동료로 결합할 수 있게 해줘요. 이 복합 select 문은 일시적 테이블을 사용해 구현돼요. 구현은 연산자마다 약간 다르지만 기본 아이디어는 같아요. 예로 EXCEPT 연산자를 사용할게요.
SELECT two FROM examp
EXCEPT
SELECT four FROM examp2;
이 마지막 예제의 결과는 examp 테이블의 "two" 열의 모든 고유 값이어야 하며, examp2의 "four" 열에 있는 어떤 값이라도 제거돼요. 이 쿼리를 구현하는 코드는 다음과 같아요:
addr opcode p1 p2 p3
---- ----------- ---- ---- -----------------------------------
0 OpenTemp 0 1
1 KeyAsData 0 1
2 Integer 0 0
3 OpenRead 1 3 examp
4 VerifyCookie 0 909
5 Rewind 1 11
6 Column 1 1
7 MakeRecord 1 0
8 String 0 0
9 PutStrKey 0 0
10 Next 1 6
11 Close 1 0
12 Integer 0 0
13 OpenRead 2 5 examp2
14 Rewind 2 20
15 Column 2 1
16 MakeRecord 1 0
17 NotFound 0 19
18 Delete 0 0
19 Next 2 15
20 Close 2 0
21 ColumnName 0 0 four
22 Rewind 0 26
23 Column 0 0
24 Callback 1 0
25 Next 0 23
26 Close 0 0
27 Halt 0 0
결과가 만들어지는 일시적 테이블은 명령 0으로 생성돼요. 그다음 세 루프가 따라와요. 명령 510의 루프는 첫 번째 SELECT 문을 구현해요. 두 번째 SELECT 문은 명령 1419의 루프로 구현돼요. 마지막으로 명령 22~25의 루프가 일시적 테이블을 읽고 결과의 각 행에 대해 콜백을 한 번 호출해요.
이 예제에서 명령 1이 특히 중요해요. 보통 Column 명령은 SQLite 파일 항목의 데이터에서 더 큰 레코드의 열 값을 추출해요. 명령 1은 일시적 테이블에 플래그를 설정해 Column이 대신 SQLite 파일 항목의 키를 데이터인 것처럼 취급하고 키에서 열 정보를 추출하게 해요.
여기서 일어날 일은 이래요: 첫 번째 SELECT 문은 결과의 행을 구성하고 각 행을 일시적 테이블 항목의 키로 저장해요. 일시적 테이블의 각 항목의 데이터는 결코 사용되지 않으므로 빈 문자열로 채워요. 두 번째 SELECT 문도 행을 구성하지만 두 번째 SELECT가 구성한 행은 일시적 테이블에서 제거돼요. 그렇기 때문에 행을 데이터가 아니라 SQLite 파일의 키에 저장하려는 거예요. 쉽게 찾아 삭제할 수 있도록 하려는 것이에요.
여기서 일어나는 일을 더 자세히 살펴봐요. 첫 번째 SELECT는 명령 5~10의 루프로 구현돼요. 명령 5는 커서를 되감아 루프를 초기화해요. 명령 6은 "examp"에서 "two" 열 값을 추출하고 명령 7은 이것을 행으로 변환해요. 명령 8은 빈 문자열을 스택에 push해요. 마지막으로 명령 9는 행을 일시적 테이블에 써요. 하지만 PutStrKey opcode가 스택 맨 위를 레코드 데이터로, 스택의 다음 항목을 키로 사용한다는 것을 기억하세요. INSERT 문의 경우 MakeRecord opcode가 생성한 행이 레코드 데이터이고 레코드 키는 NewRecno opcode가 만든 정수예요. 하지만 여기서는 역할이 뒤집혀서 MakeRecord가 만든 행이 레코드 키이고 레코드 데이터는 빈 문자열일 뿐이에요.
두 번째 SELECT는 명령 14~19로 구현돼요. 명령 14는 커서를 되감아 루프를 초기화해요. 명령 15와 16이 테이블 "examp2"의 "four" 열에서 새 결과 행을 만들어요. 하지만 PutStrKey로 이 새 행을 일시적 테이블에 쓰는 대신, 존재하면 Delete를 호출해 일시적 테이블에서 제거해요.
복합 select의 결과는 명령 22~25의 루프로 콜백 루틴에 보내져요. 23의 Column 명령이 레코드 데이터가 아니라 레코드 키에서 열을 추출한다는 사실을 제외하면 이 루프에는 새롭거나 놀랄 만한 것이 없어요.
요약 (Summary)
이 문서는 SQLite의 VDBE가 SQL 문을 구현하는 데 사용하는 모든 주요 기법을 검토했어요. 보여주지 않은 것은 대부분의 이 기법들이 적절히 복잡한 쿼리 문에 대한 코드를 생성하기 위해 조합으로 사용될 수 있다는 점이에요. 예를 들어 단순 쿼리에서 정렬이 어떻게 이루어지는지 보여줬고 복합 쿼리를 구현하는 방법을 보여줬어요. 하지만 복합 쿼리에서 정렬 예는 주지 않았어요. 복합 쿼리 정렬이 새로운 개념을 도입하지 않기 때문이에요. 단지 두 이전 아이디어(정렬과 복합화)를 같은 VDBE 프로그램에서 결합할 뿐이에요.
SQLite 라이브러리가 기능하는 방식에 대한 추가 정보는 독자가 SQLite 소스 코드를 직접 보도록 안내해요. 이 문서의 내용을 이해한다면 소스를 따라가는 데 어려움이 없을 거예요. SQLite 내부를 진지하게 공부하는 사람이라면 아마 여기에 문서화된 VDBE opcode를 주의 깊게 연구하고 싶을 거예요. opcode 문서의 대부분은 스크립트로 소스 코드의 주석에서 추출된 것이므로, vdbe.c 소스 파일에서 직접 다양한 opcode에 대한 정보를 얻을 수도 있어요. 여기까지 성공적으로 읽었다면 나머지를 이해하는 데 어려움이 없을 거예요.
문서나 코드에서 오류를 발견하면 자유롭게 고치고/고치거나 저자([email protected])에게 연락해요. 당신의 버그 수정이나 제안은 항상 환영해요.