SQLite 개발 환경 준비와 C#에서 사용하기

가장 인기 있는 독립형 DBMS중의 하나인 SQLite를 비주얼스튜디오 개발 환경에서 편리하게 사용할 수 있도록 준비하고 C#을 기반으로 SQLite를 이용하는 과정을 다룹니다.

■ SQLite DBMS 소개

C# .Net 환경에서 응용 프로그램을 개발하는데 있어 검토해야 할 중요한 아키텍처 요소들은 사용자 인터페이스, 네트워크, 데이터베이스 등을 들 수 있습니다. 그중에서도 시스템을 확장성 있고 깔끔하게 만들 수 있는 핵심 요소는 자료를 저장하고 관리하는 데이터베이스라고 할 수 있을 것입니다.

통상 데이터베이스라고 하면 SQL Server, Oracle, MySQL, CUBRID, Firebird, PostgreSQL등의 DBMS를 떠올리지만 오늘 소개할 데이터베이스는 라이브러리 형태로 응용 프로그램에 내장하는 데이터베이스 엔진인 SQLite(http://www.sqlite.org/) 입니다. 오라클 등의 DBMS들은 대용량, 여러 유저에 대한 동시 서비스를 목적으로 별도의 프로세스에서 서비스를 제공하지만, SQLite는 이들 DBMS를 대체하기 보다는 단순 파일 처리를 보다 안전하고 체계적인 방법으로 대체하기위한 목적이 있다고 이해하는 것이 적당 합니다. 

SQLite는 응용 프로그램의 서비스 대상이 1인(Standalone application)이거나 소규모인 경우에 적당합니다. 사용량이나 규모에 따라 응용 프로그램을 거의 수정하지 않고도 DBMS를 교체하고 싶다면 초기 적용 데이터베이스로 SQLite를 적용하는 경우 응용 프로그램에서 SQL을 사용할 수 있으므로 DBMS를 교체하더라도 응용 프로그램의 수정을 최소화 할 수 있는 장점이 있습니다. 

SQLite와 가장 유사한 데이터베이스를 예로 든다면 상용으로 판매하는 MS사의 Access와 SQL Server express가 대표적입니다. 그렇지만, 오픈소스인 SQLite는 관계형 모델을 동일하게 적용한 상용 Access DB에 비교하더라도 운영체제 호환성, 안정성, 트리거/자연어 검색등의 기능, 보안, 다양한 데이터 타입, 대용량 DBMS에 버금 가는 용량지원 등 기능과 성능에 있어 전혀 뒤지지 않는 추천할 만한 DBMS 입니다. SQLite를 채용하고 있는 대표적인 패키지로는 Trac이라는 협업 시스템이 있고 안드로이드 시스템은 기본 DB로 SQLite를 제공하고 있습니다. 다음은 SQLite의 주요 특성을 정리한 것입니다.

• 아키텍처 : 
  관계형 데이터베이스 모델

• 지원 운영 체제 : 
  윈도우, 리눅스, 맥, 유닉스, 심비안, BSD, AmigaOS...

• 라이선스 :
  Public domain - 저작권이 소멸된 것으로 어떠한 용도로도 아무런 제약없이 사용 가능

• 호환성 : 
  32-bit/64-bit, big-endian/little-endian 간 무관하게 일반 파일 호환성 유지

• 지원 기능 : 
  ACID, 참조무결성, 트랜잭션, 유니코드, Full-text 인덱스, R-/R+ Tree 인덱스, Reverse 인덱스, Blobs and Clobs, Except, Inner Joins, Inner Selects, Intersect, Union, Access Control, External Routine, Trigger

• 최대 DB 용량 :
  32TB

■ SQLite 개발 환경 준비하기

오라클과 같은 범용 데이터베이스를 설치하면 사용자나 개발자가 DBMS와 만나는 첫 관문은 통상 데이터베이스 매니저입니다. DB를 생성하고 관리하면 SQL 질의를 수행하는 등의 작업을 수행하는 도구입니다. SQLite의 경우에도 관련 도구를 준비하는 것이 개발의 효율성을 높이는 방법입니다.

SQLite 개발 환경 준비는 개발자가 현재 사용하는 윈도우의 종류(32비트 운영체제인지, 64비트 운영체제인지), 닷넷 프레임워크 버전, 비주얼 스튜디오 버전등에 따라 적절한 도구를 다운로드 받아 설치합니다. 설치를 위한 URL은 아래와 같습니다.

https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

위의 그림처럼 윈도우 종류, 닷넷 프레임워크 버전, 비주얼 스튜디오용 컴포넌트 포함 여부 등에 따라 여러 패키지를 다운로드 할 수 있으므로 적절하게 선택하여 다운로드 합니다. 필자의 경우에는 비주얼 스튜디오 2010를 사용하기 때문에 sqlite-netFx40-setup-bundle-x86-2010-1.0.110.0.exe를 다운로드 받았습니다.

다운로드 받은 패키지를 실행시켜서 설치를 시작합니다. 설치 폴더를 확인하고 전체 설치로 진행합니다.

추가적인 작업은 아무것도 선택하지 않아도 되기는 합니다. 저는 도구상자에 "SQLite"라는 이름으로 컨트롤 그룹을 추가하도록 선택하고 설치를 진행했습니다.

[Install]로 설치를 시작하고 설치가 끝날 즈음에는 콘솔창이 나오면서 약간의 시간이 필요한데 창이 없어지고 설치 종료 창이 나올 때까지 대기 합니다. 설치가 끝나면 [Finish]로 설치를 종료합니다.

패키지가 정상적으로 설치되면 비주얼 스튜디오 "서버탐색기"에서 데이터베이스 연결 추가를 선택했을때 위의 그림과 같이 데이터 소스에 "SQLite Database File" 항목이 나타나게 됩니다. 이것을 통해서 새로운 SQLite DB를 생성하거나 열 수 있습니다.

■ 데이터베이스 생성하기

데이터베이스를 생성하고 관리하기 위해서는 다른 DBMS의 경우에는 별도의 관리 프로그램을 실행시키는 것으로 수행하지만 SQLite DB는 앞선 도구 설치 덕택에 비주얼 스튜디오 내부에서 간단히 관리 할 수 있습니다. SQLite DBMS는 파일 기반으로 데이터베이스이므로 데이터베이스를 생성하거나 여는 것은 파일 생성과 열기 과정처럼 진행합니다. 파일의 확장자는 *.db로 하면 좋지만 다른 확장자를 해도 무방합니다.

위의 그림과 같이 서버탐색기>데이터연결에서 연결 추가를 클릭하는 것으로 시작 합니다.

데이터소스를 "SQLite Database File"로 선택하고 "Browse..."를 통해서 앞서 생성한 데이터베이스 파일을 선택하거나 "New..."로 새로운 데이터베이스를 생성 합니다.(경로와 파일명은 추후 프로그램에서 지정할 데이터베이스 경로로 사용하므로 메모해 둡니다) 데이터베이스 접근을 위한 비밀번호를 설정할 필요가 있는 경우에는 Password에 입력하고, 특별한 필요가 없는 한 기본 옵션 상태에서 "연결테스트"로 정상적으로 접근할 수 있는지 확인한 다음에 "확인"을 클릭하여 데이터베이스를 생성 합니다.

정상적으로 데이터베이스가 생성 및 연결되었으면 비주얼 스튜디오 서버탐색기>데이터연결에 위의 그림과 같이 데이터베이스 연결을 확인할 수 있습니다.

■ 테이블 생성

데이터베이스가 정상적으로 생성되었으면 데이터연결>SQLite[DB명]>Tables 의 팝업 메뉴에서 "새 테이블 추가"를 선택하면 우측 작업 공간에 테이블의 세부 컬럼을 정의할 수 있는 "SQL Server Management Studio"와 같은 형태의 도구가 나와서 간편하게 GUI 도구를 통해서 테이블을 정의할 수 있게 합니다.

컬럼명을 기입하고(되도록 예약어를 피하도록 합니다) 데이터 타입 란으로 가면 직접 타입을 입력할 수도 있지만 콤보 박스에서 자주 사용하는 타입을 선택할 수도 있습니다. 예를 들어 vachar(32)를 입력할 때는 콤보박스에 있는 varchar(50)선택한 다음 길이만 고치면 됩니다. 위와 같이 테이블을 정의하는 과정에서 Default value, Unique 제약조건 등을 지정하려면 하단의 제약 조건 설정 섹션을 활용하면 됩니다. 

특정 컬럼이나 컬럼 그룹에 Primary Key 를 지정할 때는 해당 컬럼에 대응하는 행의 헤더 셀을 클릭하여(다중 선택시 Shift+ 또는 Ctrl+ ) 선택한 다음에 위의 그림과 같이 팝업 메뉴를 사용하면 됩니다. 이 팝업 메뉴를 통해서 기본키 설정 뿐만아니라 인덱스 및 트리거 관리 등도 수행할 수 있습니다.

테이블 정의를 끝내고 데이터베이스에 반영하려면 비주얼 스튜디오 상단의 저장 아이콘이나 Ctrl+S를 누르면 되는데, 처음 생성하는 것이므로 위와 그림과 같이 테이블 이름을 입력하라고 요구하면 해당하는 이름을 지정하여 테이블을 생성 합니다. 기존의 테이블 정의를 수정할 때는 데이터연결>SQLite[DB명]>Tables>해당테이블의 팝업 메뉴에서 "디자인"을 선택하면 됩니다.

■ 프로그램에서 SQL 사용하기

C# 프로젝트에서 SQLite를 사용할 때는 우선 솔루션 탐색기>해당 프로젝트>참조의 팝업 메뉴에서 "참조 추가..."를 클릭하여 위의그림과 같이 System.Data.SQLite를 추가 합니다(.NET 탭에서 구성요소이름 헤더를 클릭하여 이름 순으로 정렬한뒤 검색하면 금방 찾을 수 있습니다).

• SQLite 구문을 사용하는 코드에서는 using System.Data.SQLite; 구문을 추가해 줍니다.

• SQLite DB에 연결할 때는 데이터베이스 생성시 사용했던 경로와 파일이름, SQLiteConnection를 사용 합니다.

                String dbConnection = String.Format("Data Source={0};password={1}", db_path, db_pass);

                SQLiteConnection dbcon = new SQLiteConnection(dbConnection);

• SELECT문을 사용할 때는 연결한 DB핸들에 SQLiteCommand, ExecuteReader등을 사용 합니다.

                SQLiteCommand qry = new SQLiteCommand("SELECT * FROM student ORDER BY student_name", dbcon);

                DataTable dattbl = new DataTable();

                SQLiteDataReader reader = qry.ExecuteReader();

                dattbl.Load(reader);

                reader.Close();

• INSERT문을 사용할 때는 SQLiteCommand와 ExecuteNonQuery를 사용 합니다.

                qry = new SQLiteCommand(String.Format("INSERT INTO student(student_name, student_birth, student_class, student_hobby) VALUES ( '{0}', '{1}', '{2}', '{3}')", sname, sbirth, sclass, shobby), dbcon);

                qry.ExecuteNonQuery();

• UPDATE문과 DELETE를 사용할 때도 SQLiteCommand와 ExecuteNonQuery를 사용 합니다.

qry = new SQLiteCommand("UPDATE student SET student_class= 1 WHERE student_birth='1988'", dbcon);

qry.ExecuteNonQuery();

qry = new SQLiteCommand("DELETE FROM student WHERE student_class = 2", dbcon);

rowsAffected = qry.ExecuteNonQuery();