iPhone SDK를 사용하면서 데이터베이스로는 내장된
sqlite3를 조금 사용해 보았습니다. ruby on rails나 이전에 조금 본적은 있지만, 실제로 사용하다 보니 기존의 일반적인인 데이터베이스에 비해 몇가지 재미있고 색다른 부분이 있는 것 같습니다.
1. 특징 & 둘러보기
1) ROWID
SQLite3의 테이블등은 생성시 기본적으로 rowid란 칼럼을 가지고 있습니다. auto increment되는 primary key와 같은 역활을 하지만 vacuum에 의해 변경될 수가 있다고 하니, 이런 용도로는 테이블의 특성에 맞게 사용해야 될 것 같습니다.
select *로는 rowid가 출력되지 않고 명시를 해주어야지 값을 확인할 수있습니다. 마지막으로 인서트된 항목의 rowid는 sqlite3_last_insert_rowid 함수의 반환값으로 확인할 수 있습니다.
2) 데이터 타입
타입이 컬럼에 일괄적으로 적용되는 다른 데이터베이스와는 달리 sqlite3는 각각의 로우별로 동적으로 타입이 결정됩니다. 그렇기 때문에 칼럼타입에 어떤 문자를 주던지 혹은 생략하더라도 테이블 생성이 가능합니다. 아래와 같이 테이블을 만든 후에 어떤 타입의 값들을 넣더라도 모두 저장이 가능합니다.
아래와 같이 각 컬럼의 값들에 따라 데이터타입이 결정됩니다.
한 컬럼에는 기본으로 1GB까지 저장이 가능하며 컴파일시에 SQLITE_MAX_LENGTH 값을 설정하여 2GB까지 가능합니다. 하지만 이전에 간단히 테스트를 해보았는데 OS X에서는 3MB 정도까지만 가능했습니다. 아마 애플에서 제한크기를 낮추어 설치하지 않았나 짐작을 하고 있습니다. 리눅스에서는 몇 백MB까지 인서트가 가능했습니다.
2. 기본 명령어
1) 시작
sqlite는 파일을 기반으로 한 컴팩트한 DB입니다. 대부분의 DB들이 파일을 기반으로 하지만 sqlite3는 데이터베이스
오픈시 인자로 파일을 받습니다.
> sqlite3 [file-name]
파일이 존재할 경우에는 기존 db파일을 열고 존재하지 않을 경우에는 새로 생성합니다. 쉘에서는 쿼리와 구별하여 명령어에 '.'이 접두사로 사용됩니다.
2) 종료
.quit 또는 .exit는 sqlite3를 종료하는 명령어입니다.
sqlite3>.quit
sqlite3>.exit
3) 도움말
sqlite3>.help
sqlite3 쉘에서 사용가능한 명령어들을 보여 줍니다.
4) 테이블 목록
sqlite3>.tables
현재 데이터베이스에 등록된 테이블들의 목록을 확인할 수 있습니다.
5) 스키마 정보
sqlite3>.schema [table-name]
지정된 테이블의 스키마를 확인할 수 있습니다. 테이블명을 입력하지 않으면 모든 테이블의 스키마가 출력됩니다. "ALTER TABLE"도 있는데 add와 rename만 가능한 것 같습니다.
6) 쿼리결과를 파일로 저장
sqlite3>.mode insert
sqlite3>.output db.sql
sqlite3>select * from mytable;
sqlite3>.quit
mode는 출력될 타입을 지정하며 csv, column, html, insert, line, list, tabs, tcl등으로 설정할 수 있습니다. output은 저장될 파일을 지정합니다. 위와 같이 실행 후 종료하면 mytable의 데이터들이 insert로된 sql문으로 db.sql 파일로 저장되어 있습니다.
7) 테이블 변경
sqlite3>alter table [table_name] rename to [new_table_name];
sqlite3>alter table [table_name] add [new_column_name];
alter는 테이블명의 변경과 테이블 컬럼의 추가만 가능한 것 같습니다.
8) vacuum
sqlite3>vacuum
데이터베이스의 테이블의 로우를 재정렬하고 delete, drop으로 인한 빈공간을 제거하는 최적화 작업을 수행합니다. insert, delete, drop등의 명령을 자주 수행하는 DB들은 정기적으로 vacuum을 실행해 주는 것이 좋습니다.
3. 자주 사용되는 C 함수
1) Open & Close
int sqlite3_open(const char *filename, sqlite3 **ppDb);
int sqlite3_close(sqlite3 *pDb);
sqlite3 데이터베이스 파일을 열고 닫는 함수 입니다.
사용 예)
sqlite3 *db;
NSString* path = @"./mydb.sqlite3";
if (sqlite3_open([path UTF8String], &db) != SQLITE_OK)
NSLog(@"Fail to open sqlite3: %s", sqlite3_errmsg(db));
sqlite3_close(db);
return NULL;
}
2) 쿼리 실행
nt sqlite3_prepare_v2(sqlite3 *db, const char *zSql, int nByte, sqlite3_stmt **ppStmt, const char **pzTail);
int sqlite3_step(sqlite3_stmt* stmt);
int sqlite3_finalize(sqlite3_stmt *pStmt);
쿼리는 prepare -> step -> finalize의 단계로 실행되고 종료됩니다. prepare는 쿼리실행에 앞서 쿼리를 컴파일하여 바이트코드로 변경합니다. step은 prepare에서 준비된 코드를 실행하며 실행될때 마다 다음 데이터를 가지고 옵니다. finalize는 prepare와 쌍으로 할당된 sqlite3_stmt의 메모리를 해제합니다.
사용 예)
if (sqlite3_prepare_v2(prevDB, "SELECT * FROM cross_temp", -1, &statement, NULL) == SQLITE_OK) {
while (sqlite3_step(statement) == SQLITE_ROW) {
int pid = sqlite3_column_int(statement, 0);
NSLog(@"pid %d", pid);
}
}
sqlite3_finalize(statement);
3) 결과값
int sqlite3_column_int(sqlite3_stmt*, int iCol);
const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
스텝에서 각 컬럼의 값들은 column_ 함수로 가져올 수 있습니다. 위는 int와 char*의 예이며 이외에도 각 타입에 맞는 함수들이 제공됩니다.
4) 쿼리 실행 - exec
int sqlite3_exec(sqlite3*, const char *sql, int (*callback)(void*,int,char**,char**), void *, char **errmsg);
exec은 위의 prepare, step, finalize를 동시에 실행하여 편리하게 사용할 수 있습니다. select와 같은 쿼리에서 각 단계의 데이터들은 콜백함수를 구현하여 처리할 수 있습니다.
사용 예)
if (sqlite3_exec(db, "SELECT * FROM item_info limit 10", setData, NULL, &error)) {
NSLog(@"EXEC ERROR: %s", error);
sqlite3_free(error);
}
콜백함수의 첫번째 인자는 sqlite3_exec의 4번째 인자에서 넘긴 사용자 데이터가 넘어 옵니다. cols는 전체 컬럼의 갯수이며 value, name에 각각 값과 컬럼명이 넘어 옵니다.
int setData(void* data,int cols, char** value, char** name) {
for (int i = 0; i < cols; i++) {
NSLog(@"%s, %s", value[i], name[i]);
}
return 0;
}
* 아이폰에서 쓰기 가능한 DB
어플리케이션은 실행시에 번들 내부의 파일에는 쓰기 권한이 없습니다. 그렇기 때문에 번들 내부에 있는 sqlite3 파일은 insert, update, delete와 같은 DB를 변경하는 쿼리가 실행되지 않습니다.
이를 해결하기 위해서는 Documents 디렉토리로 DB파일을 복사하고 이곳의 파일로 열어야 쓰기가 가능합니다. 애플의 아이폰 개발자 센터에 있는
SQLiteBooks 샘플에서 AppDelegate.m의 createEditableCopyOfDatabaseIfNeeded 메소드를 확인하시면 구현에 대해서 잘 나와있습니다. 그리고 아이폰에서 sqlite3 구현에 대한 간단한 내용은 이전
아이폰 SQLite3 샘플이란 포스팅에서 확인하실 수 있습니다.
con_data.tar.gz
