SQLite 소개

번역자 김영진(cogolda@hanmail.net)

알아두기

이 문서는 제가 http://www.zend.com/php5/articles/php5-sqlite.php/를 번역, 추가, 생략한 내용입니다. Php5가 많이 쓰일수록, SQLite(이하 시퀄라이트)의 사용도 많이 지리라 생각됩니다. 만약 질문이나 의견 있으신 분은 메일이나 코멘트를 이용해 주시면 감사하겠습니다.

대상 독자

이제부터 SQLite를 시퀄라이트라고 표기하겠습니다.
이 기사는 시퀄라이트을 배우는데 관심있는 PHP 프로그래머를 위해 씌여졌습니다.
이 튜토리얼은 시퀄라이트가 제공하는 기능을 소개할 것이며, 다른 데이터베이스 시스템보다 뛰어난 시퀄라이트의 장점을 보여줄것입니다.

PHP와 SQL의 기본적인 이해가 있다고 가정했으며, MySQL이나 PostgreSQL로 작업한 경험이 있다면 읽는데 도움이 될 것입니다.

소개

최근 몇 달안에, 여러분은 아마도 PHP의 새로운 데이터베이스 확장인 시퀄라이트에 대해 들었을 것입니다. 대부분의 평가로 인해, 시퀄라이트는, 기능이나 속도의 손실없이, 플랫 파일 데이터베이스에 대한 빠른 데이터베이스 인터페이스를 제공하고, 거대한 데이터베이스 인터페이스에 대한 멋진 대안으로 만든 것 처럼 보입니다.
(많은 사람들이 시퀄라이트를 좋게 평가했다는 의미입니다.)
이 기사에서, 우리는 이 놀라운 확장과 멋진 기능이 있고, 그것이 풍문에 떠돌아 다니는 그 장점을 살펴볼 것입니다.

시퀄라이트란 무엇인가?

시퀄라이트는 SQL 92 표준을 대부분 구현하는 내장된 데이터베이스 라이브러리입니다. 명성에 대한 주장은 단일 파일안에 모든 데이터를 저장할 수 있는 능력과 마찬가지로 단일 라이브러리 안에 (엔진이라고 말하는) 데이터베이스 엔진과 인터페이스 둘 다 결합이다.
필자는 시퀄라이트의 기능이 MySQL, PostgreSQL 사이에 어딘가에 있다고 말한다. 그렇지만 그것은 수행될 때, 시퀄라이트는 2-3 초 가량 더 빠르다.

패키지로 결합된 모든 것은 MySQL 클라이언트 라이브러리보다 크고, 여러분이 그것으로 전체 데이터베이스 시스템으로 고려할 만한 인상적인 기술이다. 대단히 효율적인 메모리 기반을 사용하기 때문에, 시퀄라이트는 작은 메모리 사용에서 작은 크기로 유지하고 다른 모든 데이터베이스 시스템보다 대단히 작다. 이것은 시퀄라이트를 테이터베이스가 필요한 모든 작업에 적용할 수 있는 메우 가벼운 도구로 만들었다.
(사실 한국의 PHPer의 관심은 시퀄라이트가 얼마나 작고 빠르냐, 아니라. 트랜젝션, 서브쿼리, 스토어드 프로시져, 관리를 위한 GUI인터페이스 지원, 한글지원, 한글문서 뭐 이런 것이 아닐가 하는 생각이 듭니다. 사실 MySQL도 너무 용량이 크고 느려서 못쓰겠다는 사람은 없었습니다.)

시퀄라이트 확장

가장 새로운 데이터베이스 확장으로서, 시퀄라이트는 호환성을 이유로 후퇴하는 고립된 양식을 관리해야 하는 MySQL 처럼 오래된 확장과 다르게 ,구형 코드를 가지고 있지 않아서 운이 좋다. 또한 그것은 수행능력과 기능의 최고 수준을 얻기위해 최신 PHP 특징을 이용하기 위한 확장을 허락한다. 이 시퀄라이트 개발자 사용자를 위해, PHP에서 이미 구현된 비슷한 인터페이스를 유지함으로써, 다른 데이터베이스 시스템에서 시퀄라이트로의 이동을 쉽게 만들었다.

시퀄 라이트는 또한 여러분이 절차적인 인터페이스 주변을 자신의 객체지향 레퍼(wrappers) 구현하기 위해 저장하는  테이터베이스에서 데이터를 효과적으로 검색하기 위해 사용할 수 있는 매우 강력한 객체지향 인터페이스를 지원한다.
아래 예제를 보자.

<?php

// 새로운 데이터베이스 만들기 (객체 지향 인터페이스)
$db = new sqlite_db("db.sqlite");

// foo 테이블을 만들고 샘플 데이터를 삽입한다
$db->query("BEGIN;
        CREATE TABLE foo(id INTEGER PRIMARY KEY, name CHAR(255));
        INSERT INTO foo (name) VALUES('Ilia');
        INSERT INTO foo (name) VALUES('Ilia2');
        INSERT INTO foo (name) VALUES('Ilia3');
        COMMIT;");

// 쿼리문 실행   
$result = $db->query("SELECT * FROM foo");
// 저장된 행을 통해 가지고 있는 행 수만큼 반복한다.
while ($result->hasMore()) {
    // 현재 행을 불러와서 변수에 대입
    $row = $result->current();   
    // 행을 출력하라. 
    print_r($row);
// 다음 행을 위한 선행 작업
    $result->next();
}

// 일반적으로 꼭 필요하진 않지만, 커넥션 제거
unset($db);

?>
(여기 객체지향 메소드는 pear이나 ADODB가 아니라 시퀄라이트가 지원하는 걸로 보입니다. MySQL에서 바로 CREATE TABLE..로 들어가던 구문이 마치 오라클 처럼 BEGIN…COMMIT를 사용하네요. 그리고 아이디와 패스워드를 확인하지 않는 부분도 조금 다릅니다. )

시퀄라이트 설치하기
PHP 5.0에서, 시퀄라이트를 설치하기는 대강 다루겠다. 왜냐하면 확장과 라이브러리 둘다 내장된다. 필요하다면 –with-sqlite 를 설정 라인에 추가하면 된다. 필자는 시퀄라이브러리를 설치하기를 추천한다.

시퀄라이트 사용하기

SQLite에 절차적인 인터페이스는 MySQL이나 다른 데이터베이스와 거의 같다.
함수 앞에 접두어 sqlite를 붙여주면 된다.

<?php
// 새로운 데이터베이스 만들기 (절차지향 인터페이스, 다시 말해 함수로 사용하기)
$db = sqlite_open("db.sqlite");

// foo 테이블 만들기
sqlite_query($db, "CREATE TABLE foo (id INTEGER PRIMARY KEY, name CHAR(255))");

// 샘플 데이터 삽입
sqlite_query($db, "INSERT INTO foo (name) VALUES ('Ilia')");
sqlite_query($db, "INSERT INTO foo (name) VALUES ('Ilia2')");
sqlite_query($db, "INSERT INTO foo (name) VALUES ('Ilia3')");

// 쿼리문 실행
$result = sqlite_query($db, "SELECT * FROM foo");
// 저장된 행을 통해 반복한다
while ($row = sqlite_fetch_array($result)) {
    print_r($row);
    /* 각 결과는 이런 것이다
    Array
    (
        [0] => 1
        [id] => 1
        [1] => Ilia
        [name] => Ilia
    )
*/
}

// 데이터베이스 접속 닫기
sqlite_close($db);

?>

시퀄라이트와 다른 데이터베이스 사이에 가장 큰 차이점은 실질적으로 스스로가 데이터 베이스 엔진이라는 것이다. 다른 데이터베이스와는 다르게, 시퀄라이트는 느슨한 타입으로 되어있다. 다시 말해, 모든 데이터는 특정한 컬럼 타입에서 데이터의 바이너리 표현보다 다소 NULL이 제거된 문자열로 저장된다. 호환성을 이유로, 시퀄라이트는 테이블을 생성하는 동안 여전히 INT, CHAR, FLOAT, TEXT와 같은 특정 타입을 지원하지만 그러나 그들은 사용되지 않는다. 내부적으로, 시퀄라이트는 단지 정렬 때문에 문자열과 정수형 사이를 구별할 뿐이다. 그러므로, 만약 여러분의 데이터를 정렬하기를 의도하지 않는다면, 여러분은 시퀄라이트에서 GREATE TABLE 문을 위해 특정 컬럼 타입을 회피할 수 있을 것이다.

시퀄라이트의 타입에 무관한(typeless) 성격은 데이터를 정렬과 비교하는데 이것 때문에 약간 느리게 된다. 각각 시간에 시퀄라이트는 테이타의 타입을 결정하는 것과 문자열이나 수많은 정렬/비교 메커니즘을 적용하는 것이 필요할 것이다. SQL 테이블은 마치 마지막 삽입된  행에 참조를 저장의 의미처럼 자주 행의 빠른 접근을 위해 자동 증가 키를 요구한다. 시퀄라이트에서, 이 디비는 약간 특이한 문법을 가지고 있다. 그런 필드를 만들기 위해 우리는 INTEGER PRIMARY KEY를 선언하는 것이 필요하다. 자동 증가 되는 필드를 지적하기 위해 추가적인 속성을 할당하거나 특정 타입을 적어줘야 한다.

(좀 특히한 내용이네요. 쉽게 말해 시퀄라이트는 내부적으로 문자열과 정수만을 구별 할 뿐, 내부적으로는 INT, CHAR, FLOAT, TEXT같은 테이터 타입을 지원하지 않는다. 그래서 정렬, 비교할때 조금 느릴 수 있고, 테이블 만들 때 데이터 타입을 안 적어주어도 된다. 다만 프라이머리 키 같은 거 지정할 때 테이터 타입을 정해야 한다.)

연결(Chained) 쿼리

여러분이 기대한 것 처럼, 시퀄라이트는 성능의 증가와 기능의 확장 둘다 새로운 많은 특징으로 나타났다. 그런 특징 중 하나는 연결 쿼리를 하는 능력이다. 다시말해 그것은 단일 쿼리 실행 함수를 통해 여러 쿼리 실행을 할수 있다는 의미이다. 이것은 여러분이 실행하기 위해 필요한 php 함수의 사용숫자를 줄인다. 이리하여 스크립트의 속도를 확장한다. 그것은 또한 트랜젝션 내부의 쿼리 블록을 쉽게 감싸줄 수 있고, 성능을 향상시킨다. 이것은 다중 작성 쿼리문을 수행할 때 그 사실을 보여줄 수 있다. 그렇지만 약간 이 기능을 사용할 때 마음으로 알수 있다.

시퀄라이트에 있는 모든 쿼리문은 여러분은 SQL 진입을 방해하기 위한 입력을 확인하기 위해 사용자-한정된 특정 입력을 사용한다.(예를 들면 패스워드) MySQL에서는 다르게, 이것은 챙피한 쿼리 에러를 야기할 뿐이나, 시퀄라이트에서, 그것은 잠재적으로 불운한 쿼리 에러로 인해, 여러분의 서버에 쿼리를 실행하기위해 공격을 허락할 것이다. 만약 여러분이 입력 퍼포먼스를 실행하는 쿼리 블록이고 여러분이 id를 저장하기를 위한다면, sqlite_insert_rowed() 일반적으로 마지막 저장의 아이디를 저장하기 위해 이런 용도를 위해 사용 된다. 다른 한편, 우리가 결정을 시도할 떄, 얼마나 많은 행이 실행된 모든 쿼리에 의해 영향받았는지 sqlite_change() 결과가 쿼리문에 실행된 모든 영향받은 행의 모든 숫자를 가질 것이다. 만약 여러분의 쿼리 블록이 SELECT를 포함한다면, 처음 쿼리를 확실하고 하고 다른 한편으로 여러분의 결과는 그런 쿼리에 의해 회복된 쿼리를 포함하지 않을 것이다.
(이해를 못하고, 번역하다 보니, 문장이 좀 엉성한데, 소스 코드와 비교해 봅시다.)


<?php

//  세로운 메모리 데이터 베이스를 만든다
$db = new sqlite_db(":memory:"); 
// 2-컬럼 테이블인 bar을 만들고 두개의 행을 삽입한다.
/* 향샹된 수행을 위해, 전체 쿼리 블록은 트랜젝션 내부에 감싸진다 */
$db->query("BEGIN;
        CREATE TABLE bar ( id INTEGER PRIMARY KEY, id2 );
        INSERT INTO bar (id2) VALUES(1);
        INSERT INTO bar (id2) VALUES(2);
        COMMIT;");
//  2개가 인서트 구문이 실행되었다는 의미에서 "2 insert queries" 를 출력할 것이다.
echo $db->changes()." insert queries\\n";
//  마지막 으로 삽입된 행 아이디인 "last inserted row id: 2" 를 출력 할 것이다.
echo "last inserted row id: ".$db->last_insert_rowid();

?>

새로운 함수

추가적으로, 특징의 끝으로 돌아가서, 시퀄라이트는 또한 얼마간의 간결하고, 시퀄라이트로부터 복구하는 빠른 새로운 함수를 제공한다.

<?php

$db = new sqlite_db("db.sqlite");
/* 연관배열로서 모든 열을 복구하고 쿼리문을 실행한다 */
$result_array = $db->array_query("SELECT * FROM foo", SQLITE_ASSOC);
print_r($result_array);

?>

이 함수는 쿼리문 실행과 단일 함수 호출로 수행될 정보 검색을 둘다 하게 한다. 가상적으로 전체 PHP 실행 과부하를 제거한다. PHP 스크립트 스스로는 여러분한 단일 함수를 가지고 있는 것 처럼 단순하다. 그러나 여러분은 반복 안에서 데이터 검색 함수의 체계를 가지고 있다. 이 예에서, 단지 sqlite_single_query()로 검색되는 단일 컬럼은 사용될수 있다. 그것은 즉시 문자열이나 검색된 열의 숫자에 의존하는 문자열의 배열을 즉시 되돌려 준다.

<?php

$db = sqlite_open("db.sqlite");
// 문자열로서 컬럼의 id를 검색한다
$id = sqlite_single_query($db, "SELECT id FROM foo WHERE name='Ilia'");
var_dump($id); //string(1)

// In the event >1 row matches, 결과는 배열이다
$ids = sqlite_single_query($db, "SELECT id FROM foo WHERE name LIKE 'I%'");
var_dump($ids); // array(3)

?>

모든 특징 때문에, 여러분은 그것을 사용할수 있으나, 잘못 사용 하진 말아라. 쿼리문에 의해 리턴되는 모든 데이터를 불러올 때, 여러분은 결과는 메모리에 저장될 거라는 것을 기억해야 한다. 만약 결과들이 큰 데이터를 포함한다면, 메모리를 할당하는 비용은 줄여진 숫자의 함수 호출을 통해 얻는 이익과는 거리가 있을 것이다. 그러므로 여러분은 데이터의 작은 분량이 저장될 때 예제에 대해 이런 함수의 사용 법을 기억해야 한다.
( 한꺼번에 너무 큰 자료를 불러 들이지 말아라..뭐 이런 내용)

시퀄라이트 반복자

PHP 5.0에서 반복자의 사용을 통해 쿼리로부터 열 데이터를 받아오는 다른 방법이 있다.


<?php

$db = new sqlite_db("db.sqlite");
// 비-버퍼 쿼리를 실행함으로서 메모리의 사용을 줄인다
$res = $db->unbuffered_query("SELECT * FROM foo");
foreach ($res as $row) { // 결과 객체를 통해 복한다
        // 내용을 출력하라
        print_r($row);
}

?>


객체의 반복은 foreach()를 통해 배열의 반복과 유사하다, 다만 이번에 여러분은  ‘keys’와 특정 결과 열로부터 데이터를 포함하는 배열의 변수 표현을 가지지는 못한다. 왜냐하면 반복자는 내부 엔진 핸들러이고 함수가 아니다. 그들은 sqlite_fetch_*() 함수로 인해 매우 작은 PHP 오버헤드를 가지고 있다. 그리고 메모리에서 버퍼되는 결과들을 요구하지 않는다. 최종결과는 대단히 빠르고, 여전히 간결하고 데이터를 불러들이는 메소드와 비슷하다. 시퀄라이트의 객체 반복자의 사용에 대해 단점은 없고 언제든지 여러분이 그것을 사용하기를 고려한다면, 다중-열 결과들(multi-row result set)을 통해 수행하는 것이 필요하다.

도구 함수

시퀄라이트 확장은 또한 데이터베이스로 작업할 때 손쉽게 이용할 수 있는 약간의 도구(utility)함수라는 특징이 있다. 그 함수들 중 하나인, sqlite_num_fields()은 특정 결과들에서 필드(컬럼)의 숫자를 결정하기 위해 사용될 수 있다.
대안적으로, 만약 여러분은 데이터를 불러들이기 위해 여러분은 첫 결과에서 간단히 count()을 사용할 수 있다. 그것은 우리들에게 같은 숫자를 줄것이다. 만약 문자열과 숫자 키 둘다 불러들인다면, 여러분은 두개로 결과를 나누가 위해 그 필드로 불편한 작업을 할 것이다. 만약 여러분의 스크립트가 특정 테이블 안에서 필드 이름을 불러들인다면, 이 숫자는 아마도 중요하다. 만약 여러분은 sqlite_field_name()을 이런 정보에 접근하기 위해 반복문 안에서 사용 한다면, 아래의 예를 보라

<?php

$db = new sqlite_db("db.sqlite");
$res = $db->unbuffered_query("SELECT * FROM foo LIMIT 1");
// 필드의 갯수를 불러들인다
$n_fields = $res->num_fields();
while ($i < $n_fields) {
    // 각각의 필드를 저장한다
    $field_name = $res->field_name($i++);
    // 필드명을 출력한다.
    echo $field_name."\\n";
}

?>

버퍼의 장점

대부분의 경우에, 성능과 메모리 이유를 위해, 여러분은 언버퍼(unbuffered) 쿼리를 실행하기 원할 것이다. 그렇지만, 이것은 특정 경우에서 필요되는 기능의 약간 손실이 있다. 그것은 언버퍼 쿼리는 항상 최고의 선택이 아니기 때문이다.

예를 들면, 여러분이 여러분의 쿼리로 실제로 저장된 많은 열을 찾기를 원한다고 가장해보자, 여러분은 이것을 결정하기 위한 능력이 되기 전에 모든 단일 열을 불러들이는 것이 필요하다. 이것은 간단히 말하면 sqlite_num_rows() 함수의 실행의 문제이다. 그것은 결과로부터 이 정보를 임시적으로 불러들일 것이다. 언버퍼 쿼리는  한번에 한 열을 계속해서 모든 열로부터 정보를 불러들여야 한다는 것을 의미하기 때문에 역시 한정된다. 버퍼 쿼리로 인해 그런 한계는 없다. 여러분은 데이터베이스부터 데이터를 불러들이고 모든 열을 이동하기 위해  sqlite_seek()를 사용할수 있다.


<?php

$db = new sqlite_db("db.sqlite");
$res = $db->query("SELECT * FROM foo");
$n_rows = $res->num_rows(); // 결과 열의 개수를 얻는다
$res->seek($n_rows - 1); // 마지막 열로 이동한다
// 역순으로 불러들이기
do {
    $data = $res->current(SQLITE_ASSOC); // 열 데이터를 얻는다
    print_r($data);
}
while ($res->hasPrev()&& $res->prev()); // 처음 열 까지..반복

?>

사용자 함수

시퀄라이트가 테이블에 가져온 대부분의 흥미로운 특징중 하나는 SQL안에 사용하는 여러분 자신의 함수를 생성할 수 있는 능력이다. 이것은 시퀄라이트가 PHP와 짝이되어 단일 라이브러리에서 인터페이스와 데이터베이스 엔진 둘다 결함된 특징 때문에 가능 하다. Sqlite_create_fuction()의 사용을 통해, 여러분은 WHERE 조건에서 사용되거나 결과들에 적용되는 함수를 만들 수 있다.


<?php
/* determine the difference between the user supplied string and the one in the database based on the contained characters */
function char_compare($db_str, $user_str) {
    return similar_text($db_str, $user_str);
}

$db = new sqlite_db("db.sqlite");

/* 시퀄라이트 안에 있는 우리의 PHP 함수 char_compare()에 기반한 char_compare()를 만든다. 세번째 파라미터는 함수가 요구하는 파라미터게 몇 개인지 지적한다 */
$db->create_function('char_compare', 'char_compare', 2);
       
/* 쿼리문을 실행하고, char_compare()은 이름과 특정 문자열 사이를 문자열 비교를 수행하기 위해 사용된다 */
$res = $db->array_query("SELECT name, char_compare(name, 'Il2') AS sim_index FROM foo", SQLITE_ASSOC);

print_r($res);

?>


그것은 PHP가 데이터베이스 데이터로 인해 간단히 인기있는 HTML 구조 같은 템플릿 엔진처럼 사용되게 만든다., 많은 경우에, 이것은 PHP의 위에 있는 템플릿 시스템을 위치하는 것이 필요하지 도록 코드는 간결하게 할 수 있다. 코드의 간결함 뒤에 , 이것은 수행 능력을 향상하고 스크립트의 메모리 낭비를 줄인다.

기억하다, 여러분은 만약 여러분이 잠재적으로 이진데이터를 포한하여 작업한다면, 그것을 처리하기 전에 시퀄라이트의 내부 이진 인코딩으로부터 테이터를 디코드하기 위해, 여러분은 sqlite_udf_decode_binary()함수를 사용해야 한다. 여러분이 그것을 하고나서,  여러분은 나중에 문제점 없이 접근될 수 있는 테이터를 보호하기 위해 sqlite_udf_encode_binary()를 사용하여 바이너리 데이터를 인코드하는 것이 필요하다.

요약

지금 우리는 시퀄라이트가 무엇을 제공하고 어떻게 사용 하는 지를 보았다. 아마도 여러분은 지금이나 나중에 그것을 사용하기를 고려할 것이다. 운좋게도 이 간략한 소개는 시퀄라이트가 제공하는 기능으로 채워졌고 내가 그것에 관해 들은 것은 빼놓지 않았다.

모든 도구처럼, 시퀄라이트는 장점과 단점이 있다. 작고 대부분의 어플리케이션에서 좋은 해결책이지만 큰 규모나 자주 쓰는(write) 곳에는 적합치 않다. 이 한계는 시퀄라이트의 단일 파일 기반 구조때문이다.

변역자에 관하여

집에서 놀고 있는 백수


------------------------------------------------------------------------

 SQLiteDBMS라고 SQLite를 DBMS화 하는 프로젝트가 있더군요. http://project.oss.or.kr/sqlite
 
 
Pear::DB에서 SQLite도 지원할걸요.