닷넷환경에서 SQLite 사용하기 - C#

통상 데이터베이스라고 하면 여러 사용자가 동시 접속하여 대량의 정보를 저장 및 질의하는 환경을 떠올리지만 의외로 개인 사용 환경, 임베디드 환경등에서도 데이터베이스는 이미 일반화된 저장소 역할을 수행하고 있습니다. 저장소로 데이터베이스를 사용한다는 것은 개발자 입장에서는 표준화된 SQL 질의로 대부분의 원하는 정보 관리가 가능해 진다는 장점을 가져다 줍니다. 그 결과로 개발의 효울성도 높일 수 있고 시스템의 안정성도 확보할 수 있는 여러가지 매력이 있는 반면 상용이든 오픈 소스 DBMS건 간에 무거운 시스템을 설치해야 하는 제약이 따르게 됩니다. 이런 배경에서 DBMS를 설치할 필요가 없고 응용 프로그램에 간편하게 내장할 수 있는 임베디드 DBMS가 등장하게 되었는데 대표적인 것으로 아파치 더비(Derby), 오라클의 버클리 DB, SQL Server compact등과 함께 SQLite를 들 수 있습니다. (https://en.wikipedia.org/wiki/Embedded_database 참조)

SQLite는 오픈소스로 사용에 제한이 없고 용량도 최대 DB 크기가 140TB(테라)에 이르는 만큼 왠만한 솔루션에 탑재하는데도 무리가 없습니다. 문제는 서버가 존재하지 않으므로 네트워크 서비스의 한계가 있고 여러 프로세스나 쓰레드의 동시 작업에 제한이 있다는 것입니다. 네트워크를 통한 동시 작업의 필요가 있다면 일반적인 범용 DBMS를 사용하는 것이 적절합니다. 다만 독립 사용 환경이나 실시간 응답을 요구하지 않는 환경이라면 적절한 관리 기법을 적용하여 효과적인 DB 시스템으로 활용할만 합니다. 이번 포스팅은 닷넷 환경에서 C#으로 SQLite를 간편하게 적용할 수 있는 과정을 살펴보고자 합니다.

■ 드라이버 다운로드 및 참조 추가

SQLite 닷넷 드라이버는 https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki에서 다운로드 받을 수 있습니다. 비주얼스튜디오에서 사용할 수 있는 도구를 포함한 배포판도 있지만 필자의 경우에는 닷넷 프레임웍 4.0 이상의 환경에서 응용 프로그램을 제작하는 목표를 가지고 "Precompiled Binaries for 32-bit Windows (.NET Framework 4.0)" 섹션에 있는 sqlite-netFx40-binary-bundle-Win32-2010-1.0.99.0.zip 파일을 다운로드 했습니다.

드라이버를 다운로드하여 압축을 해제하면 위의 그림과 같이 *.DLL의 드라이버 파일 뿐만아니라 SQLite 데이터베이스 관련 간단한 테스트를 수행하는 프로그램도 확인 할 수 있습니다. 이중에서 C# 응용 개발을 위해서는 "System.Data.SQLite.dll"만 있어도 되므로 일단 System.Data.SQLite.dll 파일을 프로젝트 폴더로 이동시키고 프로젝트>참조 추가로 프로그램에서 드라이버를 사용할 수 있도록 준비합니다.

위의 방법도 있지만 https://sourceforge.net/projects/sqlite-dotnet2에서 managed-only 코드를 참조하고 각 플랫폼별 SQLite3 전용 라이브러리(윈도우는 Sqlite3.dll, 리눅스는 연관 Shared object)를 https://sqlite.org/에서 받아 설치하는 방법도 있습니다.

■ SQLite ADO.NET 드라이버 사용하기

위의 그림은 SQLite의 ADO.NET 드라이버인 "System.Data.SQLite.dll"의 멤버들을 조회한 것으로 주요 클래스 앞에 "SQLite"가 붙어 있음을 확인할 수 있습니다. 다른 DBMS의 ADO.NET 드라이버를 사용하던 방식을 거의 그대로 사용하면 됩니다. 예를 들어 OracleConnection, FbConnection, MySQLConnection 처럼 앞에 붙는 접두어(Prefix)만 "SQLite"로 교체하면 됩니다. 드라이버에 대한 참조를 추가했으면 C# 각 코드앞에는 "using System.Data.SQLite;"를 추가합니다. MySQL의 경우 MySqlConnection과 같이 "MySql"을 앞에 붙인 ADO.Net 클래스를 사용하는데 MySql을 기준으로 했을때 SQLite의 클래스 대응은 아래와 같습니다.

• MySqlConnection -> SQLiteConnection
• MySqlCommand -> SQLiteCommand
• MySqlDbType -> DbType
• MySqlException -> SQLiteException
• MySqlDataReader -> SQLiteDataReader

SQLite 또한 MySQL 처럼 SQL 표준을 따르고 있으므로 데이터타입이나 SQL 문법은 대부분 MySQL을 준용하면 무리없이 사용할 수 있습니다. 몇가지 차이점은 아래와 같습니다. 

• 테이블 존재 여부 확인하기
  SELECT COUNT(*) cnt FROM sqlite_master WHERE name ='테이블 이름';
  SQL Server나 MySQL에서 지원하는 information_schema 대신 "sqlite_master" 시스템 테이블을 사용합니다.

• 이름을 지정하여 파라미터 사용하기
  INSERT INTO tags_uint16 (tagname, dt, val) VALUES (@name, @dt, @val);
  MySQL의 경우 파라미터 이름을 "?name" 처럼 "?" 기호를 사용했지만 SQLite의 경우에는 "@" 기호를 앞에 붙여 사용합니다.

• 파라미터 타입 지정
  cmd.Parameters.Add("@dt", DbType.DateTime);
  MySQL의 경우 MySqlDbType.DateTime 처럼 MySQL 접두어가 붙은 데이터 타입을 지원했지만 SQLite의 경우에는 시스템에서 기본적으로 제공하는 DbType을 사용합니다.

conn = new SQLiteConnection("Data Source=test.db;Pooling=true;FailIfMissing=false");
conn.Open();
//......
conn.Close();

위의 코드는 SQLite에 접속하기 위한 연결스트링으로 "Data Source"로 지정한 파일명의 데이터베이스에 접속하며 해당 파일이 없다면 자동으로 생성합니다. 파일 기반의 데이터베이스이므로 사용자별로 데이터베이스를 분리하는 등의 방법도 활용할 수 있습니다.

SQLiteCommand cmd = new SQLiteCommand("SELECT COUNT(*) cnt FROM sqlite_master WHERE name = 'tstring'", conn);
if (Convert.ToInt32(cmd.ExecuteScalar()) <= 0)
{
    cmd.CommandText = "CREATE TABLE tstring (tagname VARCHAR(16), dt DATETIME, val TINYTEXT, PRIMARY KEY(tagname, dt));";
    cmd.ExecuteNonQuery();
}

cmd.CommandText = "INSERT INTO tstring  (tagname, dt, val) VALUES (@name, @dt, @val)";
cmd.Parameters.Add("@name", DbType.String);
cmd.Parameters.Add("@dt", DbType.DateTime);
cmd.Parameters.Add("@val", DbType.Byte);
cmd.Prepare();
//......
cmd.Parameters[0].Value = tagid;
cmd.Parameters[1].Value = dtNow;
cmd.Parameters[2].Value = strvalue;
cmd.ExecuteNonQuery();

위의 코드는 SQLite ADO.Net 드라이버를 사용하여 테이블의 존재 여부를 검사하고, 테이블이 존재하지 않으면 생성하며 대량의 레코드 삽입 작업을 위하여 INSERT 문에 대한 Prepared 문장을 사용하는 예제입니다.

SQLiteCommand rcmd = new SQLiteCommand("SELECT * FROM ahistory ORDER BY dt DESC, tagname LIMIT 0, 30", conn);
 
SQLiteDataReader reader = rcmd.ExecuteReader();
if (reader.HasRows)
{
    DataTable rettbl = new DataTable();
    rettbl.Load(reader);
}
reader.Close();

SQLite 테이블에서 자료를 읽는 예제로 질의 형식이 MySQL과 다르지 않음을 확인할 수 있습니다.

■ SQLite에 대한 동시 접근 제어

SQLite ADO.Net 드라이버는 연결 시점에 지정한 데이터베이스에 대해서 여러개의 핸들을 가지고 작업할 수 있도록 지원하고 있습니다. 멀티쓰레드 기반의 환경에서도 무리없이 사용할 수는 있습니다. 문제는 해당 데이터베이스에 대해서 동시 접근을 시도하면 아래와 같은 응답코드를 발생시킨다는 것입니다.

SQLite error (5): database is locked

매뉴얼을 뒤져보고 구글링을 하면서 여러가지 시도를 해보았지만 드라이버 차원에서 동시성 제어를 깔끔하게 지원하지는 않는 것으로 보입니다. 이런 경우 문제를 해결할 수 있는 방법은 응용 프로그램 차원에서 동시성을 통제하는 방법으로 세마포어나 상호배제(Mutex)와 같은 기능을 수행할 수 있도록 닷넷에서 제공하는 ReaderWriterLockSlim 클래스를 사용하는 것입니다.

public static ReaderWriterLockSlim sqliteLock = new ReaderWriterLockSlim();
//......
try 
{
    sqliteLock.EnterWriteLock();
    cmd.ExecuteNonQuery();
    sqliteLock.ExitWriteLock();
}
catch (SQLiteException ex)
{
    errorMsg = ex.Message;
    if (sqliteLock.IsWriteLockHeld) sqliteLock.ExitWriteLock();
}

EnterWriteLock()을 수행하는 시점에 다른 쓰레드에서 권한을 가지고 있으면 권한이 해제될 때까지 대기합니다. 작업중인 쓰레드가 없다면 권한을 획득하고 작업후에는 ExitWriteLock()으로 권한을 반납하고 수행 도중 예외가 발생한 경우에는 권한 획득 여부를 확인해서 권한을 정상적으로 반납하는 방식입니다. 

SQLite를 가지고 범용 DBMS 만큼의 성능을 발휘할 수는 없습니다. 그렇지만 DBMS를 설치하지 않고도 적절한 데이터베이스 관리를 수행할 수 있는 방법이므로 용도에 따라 검토할 필요가 있습니다.