잡학문구

DB Browser for SQLite

SQLite DB를 관리할 수 있는 GUI 환경의 툴이다.

1. SQLite 브라우저 프로그램 다운로드

https://sqlitebrowser.org 에 접속하여 운영체제에 맞는 DB 브라우저를 다운로드하여 설치한다. 

• DB Browser for SQLite - .zip (no installer) for 64-bit Windows (no installer)
• DB.Browser.for.SQLite-3.11.2-win64(installer)

2. DB Browser 실행

설치한 프로그램을 실행하거나 압축해제한 DB Browser for SQLite 실행파일을 수행하면 아래와 같은 화면이 나타난다.

우측 하단에 UTF-8 문자셋이 보인다. 기본적으로 문자를 UTF-8로 처리한다.

3. DB를 생성거나 기존 DB 열기

DB 생성  :

[새 데이터베이스(N)] 메뉴를 클릭하고 저장하고자 하는 폴더 위치와 파일명을 입력한다.

그 다음에 테이블명을 입력하고, 필드명을 추가하여 신규 DB와 테이블을 생성한다.

[데이터 보기] 탭을 클릭한 후, 데이터를 입력해 본다.

기존 DB 열기

기존에 생성된 데이터베이스를 선택하여 DB를 오픈한다.

4. Table과 Field 생성

[편집] - [테이블생성] 메뉴를 클릭한 후 '테이블 정의 변경' 화면이 나오면 테이블명에 emp 를 입력하고, id(integer, PK, AI), name(text, NN), birth(text, NN), pic(blob) 필드를 추가하고 id는 primary key와 auto increment(AI)로 지정한다.

data 타입은 integer, text, blob, real, numeric이 지원된다.

Not null, Primary key. Auto Increment, Unique 제약 조건 및 default, check, collcation, FK 등도 지원된다.

5. 새 레코드 추가/삭제

[데이터 보기] 탭을 클릭한 후 새 레코드를 입력히거나 기준 레코드를 삭제할 수 있다.Oracle DB와는 다르게 Not Null 컬럼으로 지정한 컬럼에 아무 데이터도 입력하지 않았는데, 오류가 발생하지 않고 데이터가 저장되었다. 오라클을 제외한  일반 DB(mysql, mariaDB, MS Sql, informix 등)에서 처리하는 empty string이나 zero-byte string으로 처리한다. 

6. SQL 문은 일반 DBMS 사용법과 같다.

단, 데이터가 아무것도 없는 경우에는 empty string 으로 처리하고, 명시적으로 null을 입력할 경우에는 null로 처리한다.

 

*** 참고 ***

원격 DB 접속은 원천적으로 안된다. 서버리스 DB이므로 접속을 할 수 있는 데몬이나 리스너가 없다.

그래도 원격 접속을 할려면 아래 참조.

- NFS나 삼바 스토리지 share?

- 라이브러리?

https://forums.slimdevices.com/showthread.php?65898-Remote-access-of-SQLite-database

Maybe one of these alternatives would work ?
http://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork

The easiest is probably to share the directory where the squeezecenter.db file is stored. I'm not sure if you'll get into trouble if you modify the squeezecenter.db while SqueezeCenter is connected to it, to be safe it's probably a good idea to shutdown SqueezeCenter before you do any modifications.

If you just want to be able to run a SQL statement from a remote machine, you can always try toe Database Query plugin. It's only available as a beta version for SQLite yet, see this thread for more information:
http://forums.slimdevices.com/showthread.php?t=65439

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

What is SQLite

SQLite는 관계형 데이터베이스 관리 시스템을 제공하는 소프트웨어 라이브러리입니다. SQLite의 라이트는 설정, 데이터베이스 관리 및 리소스 측면에서 가볍습니다.

SQLite에는 다음과 같은 기능이 있습니다.

서버리스, 독립실행형, Zero-configuration, 트랜잭션 처리

Serverless

일반적으로 MySQL, PostgreSQL 등의 RDBMS는 별도의 서버 프로세스가 필요합니다. 데이터베이스 서버에 액세스하려는 응용 프로그램은 TCP/IP 프로토콜을 사용하여 요청을 보내고 받습니다. 이것을 클라이언트/서버 아키텍처라고 합니다.
다음 다이어그램은 RDBMS 클라이언트/서버 아키텍처를 보여줍니다.

SQLite는 위와 같은 방식으로 작동하지 않습니다.
SQLite는 서버 프로세스를 실행할 필요가 없습니다.
SQLite 데이터베이스는 데이터베이스에 액세스하는 애플리케이션과 통합됩니다. 응용 프로그램은 SQLite 데이터베이스와 상호 작용하여 디스크에 저장된 데이터베이스 파일에서 직접 읽고 씁니다.

다음 다이어그램은 SQLite 서버리스 아키텍처를 보여줍니다

독립실행형

SQLite는 독립 실행형으로 운영 체제나 외부 라이브러리의 지원이 최소한으로 필요합니다. 따라서 iPhone, Android 전화, 게임 콘솔, 휴대용 미디어 플레이어 등과 같은 임베디드 장치의 모든 환경에서 SQLite를 사용할 수 있습니다.
SQLite는 ANSI-C를 사용하여 개발되었습니다. 소스 코드는 sqlite3.c 및 헤더 파일 sqlite3.h로 사용할 수 있습니다. SQLite를 사용하는 애플리케이션을 개발하려면 이 파일을 프로젝트에 포함하고 컴파일하면 됩니다.

Zero-configuration

서버리스 아키텍처로 인해 SQLite를 "설치"할 필요가 없습니다. 구성, 시작 및 중지해야 하는 서버 프로세스가 없습니다.
또한 SQLite는 구성 파일을 사용하지 않습니다.

트랜잭션 처리

SQLite의 모든 트랜잭션은 완전히 ACID를 준수합니다. 이는 모든 쿼리 및 변경 사항이 Atomic, Consistent, Isolated 및 Durable임을 의미합니다.
즉, 응용 프로그램 충돌, 정전 또는 운영 체제 충돌과 같은 예기치 않은 상황이 발생하더라도 트랜잭션 내의 모든 변경은 완전히 처리되거나 전혀 처리되지(commit or rollback) 않습니다.

SQLite 특징

SQLite는 테이블에 동적 유형을 사용합니다. 데이터 유형에 관계없이 모든 열에 모든 값을 저장할 수 있음을 의미합니다.
SQLite를 사용하면 단일 데이터베이스에 연결하여 여러 데이터베이스 파일에 동시에 액세스할 수 있습니다. 이것은 다른 데이터베이스의 테이블을 조인하거나 단일 명령으로 데이터베이스 간에 데이터를 복사할 수 있습니다.
SQLite는 작업 속도가 매우 빠른 인메모리 데이터베이스를 생성할 수 있습니다.

SQLite 소스 코드는 공개되어 있으며 모든 사람이 어떤 목적으로든 무료로 사용할 수 있습니다.