SQLite 설치 및 사용법

SQLite 설치

 

Widow에 SQLite 설치

 

SQLite 홈페이지(https://www.sqlite.org/download.html) 에서 SQLite를 다운로드 한다.

자신의 PC에 맞는 버전을 다운로드하고 설치한다.

본 문서에서는 sqlite-tools-win32-x86 Binary 버전을 다운로드하여 설치한다.

 

본 문서에서는 D:\sqlite 디렉토리에 압축을 풀었다. 압축을 풀면 아래 3개의 파일이 생긴다.

 

sqldiff.exe :  SQLite 데이터베이스 간의 콘텐츠 차이를 보여주는 프로그램

                         <사용예> : sqldiff [options] database1.sqlite database2.sqlite

sqlite3.exe : SQLite 데이터베이스 또는 ZIP 아카이브에 대해 SQL 문을 입력하고 실행할 수 있도록 하는 프로그램

                         <사용예> : sqlite3 ex1.db (ex1.db 데이터베이스 생성)

sqlite3_analyzer.exe : 테이블과 인덱스가 공간을 측정하고 표시하는프로그램

                         <사용예> : sqlite3_analyzer database.sqlite

 

Linux에 SQLite 설치

SQLite 홈페이지(https://www.sqlite.org/download.html) 에서 SQLite를 다운로드하여 설치하거나,

sudo apt install sqlite3 명령어를 수행하여 설치한다.

$ sudo apt install sqlite3

 

SQLite 사용법

데이터베이스 생성

Linux 쉘이나 Dos 명령창에서 "sqlite3 first.db"를 수행하면 "first.db"라는 새로운 데이터베이스를 생성한다. 명령어를 수행한 경로에 first.db 파일이 생성된다. 즉 별도의 DB 생성 명령어는 존재하지 않고, sqlite3 명령어로 새로은 database 생성한다. 만약, 기존에 데이터베이스가 존재하면 기존 데이터베이스에 접속한다.

$ sqlite3 first.db
SQLite version 3.37.0 2021-11-27 14:13:22
Enter ".help" for usage hints.
sqlite>

 

SQLite 버전 확인

sqlite> select sqlite_version();
sqlite_version()
3.31.1

 

데이터베이스 목록 조회

sqlite> PRAGMA database_list;

seq|name|file
0|main|/mnt/d/sqlite/first.db
2|test|/mnt/d/sqlite/test.db
3|second|/mnt/d/sqlite/second.db

 

테이블생성

tbl1 테이블을 생성하고 데이터를 입력한 후 조회해 본다. 데이터는 '|' 기호로 분리되어 조회된다.

sqlite> create table tbl1(one varchar(10), two smallint);
sqlite> insert into tbl1 values('hello!',10);
sqlite> insert into tbl1 values('goodbye', 20);
sqlite> select * from tbl1;
one|two
hello!|10
goodbye|20
sqlite>

SQLite 커맨드라인 명령어는 . (점) 으로 시작하면 되고 .help를 치면 수행할 수 있는 명령어들이 화면에 표시된다.

 

한글입력

한글을 입력하기 위한 별도의 설정은 필요없다.

SQLite에서 TEXT 데이터 형으로 데이터를 입력하면 기본적으로 UTF-8로 저장된다.

 

테이블 목록 조회

SQLite의 커맨드라인으로 조회 : SQLite의 커맨드라인에서  .tables를 입력한다.

sqlite> .tables
tbl1

query로 table 목록 조회 
type, name, 테이블명, rootpage, sql 이 보여진다. 조회 시 header를 보이게 하려면 .headers on|off 를 사용한다.

아래 예제는 .headers on 을 사용한 결과이다.

sqlite> SELECT * FROM sqlite_master WHERE type='table';
type|name|tbl_name|rootpage|sql
table|tbl1|tbl1|2|CREATE TABLE tbl1(one varchar(10), two smallint)

-- table_name만 조회
sqlite> SELECT tbl_name FROM sqlite_master WHERE type='table';
tbl_name
tbl1

 

추가 DB 생성

생성하고자 하는 DB명으로 새로운 DB를 생성한다.

$ sqlite3 second.db
SQLite version 3.37.0 2021-11-27 14:13:22
Enter ".help" for usage hints.
sqlite>

 

transaction 제어(commit, rollback, savepoint 등)

데이터베이스에 액세스하는 모든 명령(기본적으로 일부 PRAGMA 문을 제외한 모든 SQL 명령)은 트랜잭션이 적용되지 않은 경우 자동으로 트랜잭션을 시작한다. 자동으로 시작된 트랜잭션은 마지막 SQL 문이 완료될 때 커밋된다.

트랜잭션은 BEGIN 명령을 사용하여 시작할 수 있다. 이러한 트랜잭션은 일반적으로 COMMIT 또는 ROLLBACK 명령까지 지속됩니다. 그러나 데이터베이스가 닫히거나 오류가 발생하고 ROLLBACK 충돌 해결 알고리즘이 지정된 경우 트랜잭션도 ROLLBACK됩니다. 
END TRANSACTION은 COMMIT과 동일하다. 아래 transaction에 대한 rollback과 commit 예제이다

-- transaction rollback
sqlite> create table trans(one varchar(10), two smallint);
sqlite> begin transaction;
sqlite> insert into trans values('hello',10);
sqlite> insert into trans values('goodbye', 20);
sqlite> rollback;
sqlite> select * from trans;
-- 입력한 데이터가 rollback 되어 한건도 조회되지 않는다.

-- transaction commit(= end transaction);
sqlite> begin transaction;
sqlite> insert into trans values('goodbye', 20);
sqlite> end transaction;
sqlite> select * from trans;
one|two
goodbye|20

sqlite3_get_autocommit() 인터페이스는 주어진 데이터베이스 연결이 자동 커밋 모드에 있으면 0이 아닌 값을, 그렇지 않은 경우에는 0을 반환한다. sqlite3 DB는 자동 커밋 모드가 기본적으로 켜져 있다. 자동 커밋 모드는 BEGIN 문에 의해 제어된다.

그러므로, 프로그램 작성 시 transaction을 제어하도록 해야 한다.

아래는 java 프로그램으로 transaction을 제어하는 예제이다.

SQLiteConnection con = new SQLiteConnection();
con.exec("BEGIN");
// do transaction work - auto-commit is disabled
con.exec("COMMIT");
// auto-commit is enabled again

 

 

DB 계정

사용자 이름/비밀번호는 sqlite/sqlite3 패키지에서 지원되지 않는다.

인증이 필요한 데이터베이스를 구성할려면 PHP와 별도로 SQLite를 컴파일해야 한다.(SQLite User Authentication)
자세한 내용은 SQLite 사용자 인증 문서를 참조한다.

 

DB접속 종료

sqlite> 프롬프트에서 .quit를 입력하여 프로그램을 종료하거나 .exit CODE 를 입력하면 return-code 로 프로그램을 종료한다.

여기서 CODE 부분은 sqlite 종료 시 리턴값으로 사용될 부분이므로 원하는 값으로 정의해서 사용하면 된다. 

Window인 경우에는 Control-C를 Linux인 경우에는  Control-D를 입력하여 sqlite3 프로그램을 종료할 수도 있다.

sqlite DB는 서버리스 DB이기 때문에 접속이 종료되면 process가 보이지 않는다. 

 

DB Dump

명령창에서 아래와 같이 SQLite 명령어를 치면 데이터베이스를 export할 수 있다.

Syntax : sqlite3 db명 .dump > dump파일명

$sqlite3 firstDB.db .dump > firstDB.sql

위의 명령은 firstDB.db 데이터베이스의 전체 내용을 SQLite 문으로 변환하고 ASCII 텍스트 파일 firstDB.sql로 덤프합니다. 생성된 firstDB.sql에서 다음과 같은 방법으로 복원을 수행할 수 있습니다.

$sqlite3 firstDB.db < dirstDB.sql

 

참조 : https://www.tutorialspoint.com/sqlite/sqlite_explain.htm