sqlite는, 아무런 제약이 없는데다 가볍고 사용자층도 두터워 유사시 도움받기도 용이한 DB 라이브러리죠. (저도 디지털 녹화기를 개발하는데 채널 정보나 EPG(전자 프로그램 가이드) 데이터를 처리하는데 sqlite를 사용합니다.)
최근 개발하던 제품에 탑제되던 소프트웨어에, 원래 설계 시에는 없던 필드를 추가할 일이 생겼습니다. (네, 소프트웨어 개발할 때, 그런 일은 절대 없을 거란 장담을 그대로 믿으면 늘 낭패를 보는 법입니다…) 이미 출시된 제품의 DB의 구조를 바꾸는 게 내키지는 않지만, 어쩔 수 없죠.
다행히 sqlite가 지원하는 ALTER TABLE 기능 중에 새로운 column 추가가 포함되어 있습니다.
- 지원되는 기능
- table 이름 변경
- column 추가
- 지원되지 않는 기능
- column 이름 변경
- column 제거
- contstraint 제거
따라서 테이블에 추가하고자 하는 column이 있는 지 확인해서 없으면 적절한 예외 처리를 하고 column을 추가하면 되는 것이어서, table에 어떤 column이 있는 지를 확인하는 방법을 찾아 봤습니다. 의외로 맘에 쏙 드는 방법을 찾지 못하면서 이리 저리 발견한 몇 가지 방법의 내용을 정리해 볼까 합니다.
sqlite API를 사용
사실 sqlite 쯤되면 당연히 table의 column 정보를 확인하는 API를 제공할 거라고 생각하는게 이상한 건 아닙니다. 실제로 sqlite 홈페이지에서 찾아보면 정확하게 원하는 함수가 제공됩니다. 바로 sqlite3_table_column_metadata() 입니다.
int sqlite3_table_column_metadata( sqlite3 *db, /* Connection handle */ const char *zDbName, /* Database name or NULL */ const char *zTableName, /* Table name */ const char *zColumnName, /* Column name */ char const **pzDataType, /* OUTPUT: Declared data type */ char const **pzCollSeq, /* OUTPUT: Collation sequence name */ int *pNotNull, /* OUTPUT: True if NOT NULL constraint exists */ int *pPrimaryKey, /* OUTPUT: True if column part of PK */ int *pAutoinc /* OUTPUT: True if column is auto-increment */ );
sqlite3_table_column_metadata() 함수는 table이나 column의 존재 여부 뿐 아니라 데이터 타입이나 NULL, PK, auto-increment 여부까지도 알려 줍니다. 네, column 정보를 확인하는 가장 좋은 방법입니다. 다만 sqlite3_table_column_metadata() 함수가 베이스라인에 포함되어 있지 않고 SQLITE_ENABLE_COLUMN_METADATA C-preprocessor 이 선언되어 있을 때만 쓸 수 있습니다. 즉, 별도 컴파일 옵션을 따로 줘서 빌드해야만 사용할 수 있다는 게, 좀 마음에 걸립니다.
sqlite는 다양한 컴파일 옵션을 가지고 있고 (그래서 아주 가벼워야 하는 열악한 HW 사양을 가지는 임베디드 환경에서 꼭 필요한 기능만 포함시켜 사용할 수도 있죠) 옵션을 변경해서 빌드하는 것도 어렵지 않습니다. auto tools를 사용한다면 configure 할 때 주는 configure_options 뒤에, CPPFLAGS=”-DSQLITE_OPTION1 -DSQLITE_OPTION2 …” 이런 식으로 컴파일 옵션을 지정하면 됩니다. 다음은 제가 개발하는 임베디드 환경을 위해 auto tools로 빌드하는 경우의 예인데요, SQLITE_ENABLE_COLUMN_METADATA 옵션을 넣고 싶으면 다음과 같이 빌드하면 됩니다.
$ MY_OPTIONS="--enable-threadsafe --enable-cross-thread-connections --disable-tcl \ --build=i386-linux --host=mipsel-linux" $ ./configure $MY_OPTIONS CPPFLAGS="-DSQLITE_ENABLE_COLUMN_METADATA"
참고로 Size Of The SQLite Library에 의하면 SQLITE_ENABLE_COLUMN_METADATA를 포함시켜 봐야 1.1 KB 정도 커진다고 하니 크기 면에서도 큰 무리는 없을 겁니다.
하지만 왠지 베이스라인과 다르게 커스터마이즈해서 쓰는 것에 웬지 모를 거부감이 든다거나, 혹은 “갑”이 허용하지 않는다는 등의 이런저런 사정으로 컴파일 옵션을 바꾸지 못하는 피치 못할 사정이 있다면 다른 방법을 찾아봐야 할 테죠.
query를 사용하는 방법
일반적으로 좀 규모가 큰 DBMS들의 경우, database, table, column 정보를 저장하는 별도의 master DB가 존재하는데요, 이런 master DB가 있으면 이로부터 특정 DB의 table 및 column 정보를 SQL query를 통해 확인할 수 있습니다.
sqlite도 그렇지 않을까 하고 찾아 봤는데, sqlite은 데이터베이스의 스키마(schema)를 sqlite_master라는 테이블에 저장하고 있습니다. 그렇지! 그럼 이 table로부터 column 정보를 query할 수도 있겠군요… 어디 보자, sqlite_master 테이블은 다음과 같습니다.
CREATE TABLE sqlite_master( type text, name text, tbl_name text, rootpage integer, sql text );
sqlite_master에는 각 테이블들의 type이나 이름, 그리고 sql 정보가 포함되어 있군요… 응? sql 정보? column 이름이나 기타 정보가 아니고? 확인해 보니 sql 필드는 DB 생성할 때 사용된 .schema 정보 중 해당 tabel을 생성하는 CREATE TABLE … 구문이 저장되어 있습니다. 아쉽지만 sqlite_master의 정보만으로는 쉽게 column 정보를 확인할 수가 없습니다. 쩝.
하지만 쉽지 않은 게, 불가능한 건 아니죠. 확인하고자 하는 table의 sql 정보를 다음과 같이 query해서, 그 결과로부터 원하는 column의 정보를 parsing 할 수 있지 않을까요?
SELECT sql FROM sqlite_master WHERE name='테이블이름'
예를 들어 channel 이라는 table 에 id 라는 필드가 있는 지를 확인하려면, query 내에 id 를 찾는 조건을 붙이는 것입니다.
SELECT sql FROM sqlite_master WHERE name='channel' AND sql LIKE '%id%'
하지만 LIKE ‘%id%’ 는 id 라는 필드 뿐 아니라, service_id 나 user_id 같은 필드들도 해당이 되기 때문에 id 가 있다는 것을 보장해 주지 못합니다. 그럼 id 앞 뒤로 space가 들어가도록, ‘% id %’로 하면 되지 않을까요? 그런데 여기서 주의해야 하는 점은, sqlite_master로부터 반환되는 sql 문은 DB 작성에 사용된 sql 문 그대로 라는 겁니다. 즉 CREATE TABLE을 한줄로 작성했다면 그 한줄이, 필드 이름 앞에 탭 문자를 썼다면 탭 문자가 포함된 그 문장 그대로 반환된다는 것이죠. 만약에 channel table의 sql 문이 다음과 같은 경우에는 id 가 없다고 나올 겁니다.
CREATE TABLE channel (id INTEGER,
service_id INTEGER,
...
)
혹은 필드 이름 앞에 스페이가 아니라 탭 문자를 넣은 경우에도 안 될 테고요… 결국 CREATE TABLE 구문의 작성 포맷에 정확하게 맞춘 구문을 사용해야 하는데, 이는 시간이 지남에 따라 실수할 가능성이 무척 커지는 위험한 방법입니다.
문자열을 좀더 정교하고 유연하게 검색할 필요가 생겼는데, 요럴 때 쓰라고 만들어진게 바로 정규표현식이죠. 위의 예라면, 문장을 구분하는 \b 메타문자로 column 이름을 감싸서, 즉 ‘\bid\b’ 이라는 정규표현식으로 위에서 나온 모든 경우를 정확하게 걸러낼 수 있습니다.
그럼 이제 sqlite가 정규표현식을 지원하면 게임 끝…일텐데, 아쉽게도 현재 sqlite는 정규표현식의 처리 기능을 포함하고 있지 않습니다. 대신, sqlite 확장 형태로 정규표현식 기능을 추가했을 때, 이를 사용할 수 있게는 해 줍니다. 관심이 있다면 아래 URL을 참고하세요.
딱 봐도 할 일이 제법 되어 보이죠? 차라리 sqlite3_table_column_metadata()를 쓰는게 낫겠어 라는 생각이 절로 듭니다. 만약 개발자가 사용하는 개발 언어에서 정규표현식을 지원해 주거나 정규표현식을 지원하는 라이브러리(예: c++의 boost::regex)를 사용할 수 있다면, 일단 query를 통해 얻은 table의 sql 문을 정규표현식으로 처리해서 원하는 결과를 얻어낼 수 있을 겁니다. 정규표현식을 좀더 가다듬으면 column의 세부 속성도 확인할 수 있을 테고요.
column 존재 여부만 간단히 확인하는 방법
만약 column의 세부 정보는 필요 없고 단순히 특정 table에 어떤 column이 있는 지만 확인하고 싶은 경우라면, 컴파일을 다시 해 주거나 복잡한 정규표현식 처리 없이, sqlite의 기본적인 query로 간단하게 확인할 수 있습니다. 바로 sqlite3_column_name() 함수를 이용하는 방법입니다.
먼저 확인하고자 하는 테이블의 모든 필드를 긁어오는 query를 실행합니다. 이때 field 이름만 필요하기 때문에, 실제 데이터는 가져오지 않도록 하기 위해, rowid = 0 와 같은 조건을 지정하면 좋겠죠? (참고로 rowid 는 sqlite가 테이블 생성 시 기본적으로 생성하는 field인데요, 그 값은 자동으로 1부터 지정됩니다.)
"SELECT * FROM table_name WHERE rowid = 0;"
이 query문을 실행한 결과로부터, sqlite3_column_name() 을 이용해서 특정 필드의 존재 여부를 확인할 수 있습니다. (예외 처리는 생략했고요, 하나의 테이블에서 여러 개의 column들에 대해 확인해야 하는 경우, 적절한 변형하는 건 크게 어렵지 않겠죠?)
bool ColumnExists(
sqlite3* a_db, const std::string& a_table, const std::string& a_column)
{
std::string query = "SELECT * FROM " + a_table + " WHERE rowid = 0;";
const char* tail=0;
sqlite3_stmt* stmt = sqlite3_prepare(a_db, query.c_str(), -1, &a_db, &tail);
sqlite3_step(stmt);
const int columnsCount = sqlite3_column_count(stmt);
for ( int index = 0; index < columnsCount; ++index )
{
const char* const columnName = sqlite3_column_name(stmt, index);
if ( strncmp(columnName, a_column.c_str(), a_column.size()) == 0 )
{
sqlite3_finalize(stmt);
return true;
}
}
sqlite3_finalize(stmt);
return false;
}
요약
세 가지 방법에 대한 간단 정리를 하자면 아래와 같습니다.
- sqlite의 베이스라인과 다르게 커스터마이징할 수 있는 상황이면 SQLITE_ENABLE_COLUMN_METADATA 을 포함시켜 빌드하고, sqlite3_table_column_metadata()를 사용합니다.
- 어떤 이유에서이든지 sqlite 커스터마이징을 피하고 싶다면, sqlite_master 테이블의 sql column을 쿼리하고 그 결과로부터 정규표현식을 사용해서 원하는 정보를 추출합니다.
- 단순히 테이블에 특정 필드가 존재하는 지만 확인해야 하는 경우라면, 해당 테이블의 모든 column들을 조회한 다음, sqlite3_column_name()으로 column 이름들을 얻어내서 원하는 column의 존재 여부를 확인해도 좋습니다.
아, 그런데 column이 없을 때 특별한 예외 처리 할 필요가 없다면, 그래서 실행 시점에서 특정 column이 있기만 하면 문제될 게 없는 상황이면, 위의 방법들 다 필요 없고 그냥 매번 ALTER TABLE 을 실행하는 게 최고 간단한 방법이겠네요. (이미 ALTER TABLE을 실행할 때마다 존재하는 column이라고 sqlite가 투덜거릴테니, 이를 무시할 수 있는 배짱(?)이 필요할테죠…)
하지만, 앞으로 무슨 일이 생길 지 모르는 소프트웨어 개발에서 sqlite를 쓰고 있다면, 정확한 column 정보 알아내는 방법을 알아 둬선 나쁠 건 없을 테니, 이 페이지 북마킹 정도는 해 두시길. (혹시 더 좋은 방법 알고 계시다면 꼭 알려 주세요)