SQL lite=SQLite ローカル特化の組み込みデータベースエンジン 概要と操作方法

SQL liteの概要や操作方法についてまとめています。

SQLiteの概要

一部でSQL Liteと呼ばれることがありますが、正確にはSQLiteです。

SQLiteは組み込みSQLデータベースエンジン。他のDBMSと違って、個別のサーバプロセスがなく、通常のディスク上のファイルを直接読み書きします。ただ、認証機能がないため、セキュリティ要件が厳しい案件には合わないでしょう。

それでいて、複数のテーブル、インデックス、トリガー、ビューを含むSQLデータベースの機能を持っているんですね。データベースファイル形式はクロスプラットフォームで、32ビットシステム、64ビットシステム、ハイエンド、エントリオーモデルのアーキテクチャ間でデータベースを自由にコピーできます。

移植性が高いため、SQLiteはアプリケーションファイル形式として人気があるんですね。SQLiteデータベースファイルは 、米国議会図書館が推奨するストレージ形式になっているほどです。SQLiteはOracleの代わりにはなれませんが、ファイルをアクセスするための仕組みとしては重宝されています。

SQLiteはコンパクトなライブラリです。すべての機能を有効にすると、プラットフォームとコンパイラにもよりますが、ライブラリサイズを600KB未満にすることができます。これぐらい小さいと気軽に使えますね。

SQLiteは、使用できるメモリが多いほど高速に実行されます。ただ、メモリの少ない環境でもパフォーマンス良好に動作するんですよね。使い方によっては、SQLiteは直接ファイルシステムI / Oよりも高速になる場合があります。そのため、アプリケーションには積極的に使われているんですね。

参考）公式ドキュメント：SQLite Documentation

SQLiteが実装していない機能

SQLiteはSQLデータベースの多くの機能を実装していますが、「Oracleの代わりにはならない」と良く言われます。大規模データベースよりも、アプリケーション組み込みの小規模データベースに特化したイメージなんですね。

以下は、SQLiteが実装していない機能です。

1
全ての結合をサポートしていない。SQLiteは、LEFT OUTER JOINは実装されていますが、RIGHT OUTERJOINまたはFULLOUTERJOINは実装されていません。

2
一部のALTERTABLEコマンドをサポートしていない。SQLiteでは、ALTERTABLEコマンドのRENAMETABLE、ADD COLUMN、RENAMECOLUMNのみがサポートされています。DROP COLUMN、ALTER COLUMN、ADDCONSTRAINTなどの他の種類のALTERTABLE操作は省略されています。

3
一部のトリガーをサポートしていない。SQLiteでは FOR EACH ROWトリガーはサポートされていますが、FOR EACHSTATEMENTトリガーはサポートされていません。

4
ビューへの書き込みはサポートしていない。 SQLiteのビューは読み取り専用です。ビューに対してDELETE、INSERT、またはUPDATEを実行することはできません。ただし、ビューをDELETE、INSERT、またはUPDATEのトリガーを作成し、トリガーで必要なことを実行できます。

5
ログイン認証、GRANTとREVOKEは実装されていない。SQLiteは通常のディスクファイルを読み書きするため、適用できるアクセス許可は、基盤となるオペレーティングシステムの通常のファイルアクセス許可のみ。、アクセス制限を細かく指定するGRANTおよびREVOKEコマンドは組み込みデータベースエンジンでは意味がないため、実装されていません。

参考）SQLiteが実装していないSQL機能

SQLiteの操作方法

SQLiteはsqlite3というコマンドで操作可能です。sqlite3はSQLiteバージョン3用で、sqliteはSQLiteバージョン2用です。引数のsample.dbはデータベースファイル名で、存在しなければ新規にファイル作成されます。

データベース接続時にログインなどのユーザ認証はありません。ファイルのパーミッションを指定することである程度のセキュリティを確保できます。

% sqlite3 sample.db
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.

なお、バージョン2と3ではデータベース形式が異なるため、バージョン2でバージョン3のデータベースを指定すると「ファイルが暗号化されているか、データベースではない」というエラーになります。

% sqlite sample.db
Unable to open database "sample.db": file is encrypted or is not a database

操作方法は.helpを入力すると表示されます。

sqlite> .help
.archive ...           Manage SQL archives: ".archive --help" for details
.auth ON|OFF           Show authorizer callbacks
.backup ?DB? FILE      Backup DB (default "main") to FILE
.bail on|off           Stop after hitting an error.  Default OFF
.binary on|off         Turn binary output on or off.  Default OFF
.cd DIRECTORY          Change the working directory to DIRECTORY
 :
 :

簡単なテーブルを作成してみましょう。テーブルproductをcreate tableで作成して、データを2件insertしてみます。

CREATE TABLE product
	(
     id integer primary key, 
     type varchar(20), 
     details varchar(30)
    );

INSERT INTO product
(id, type, details)
VALUES
(1, 'hardware', 'PC');

INSERT INTO product
(id, type, details)
VALUES
(2, 'software', 'linux');

.tablesでテーブル一覧が確認できます。.schemaでテーブル構造の確認が可能です。

sqlite> .tables
product
sqlite> .schema
CREATE TABLE product
(
     id integer primary key,
     type varchar(20),
     details varchar(30)
    );

select、update、deleteを試してみます。

sqlite> select * from product;
1|hardware|PC
2|software|linux

sqlite> update product set details="personal computer" where id=1;
sqlite> select * from product;
1|hardware|personal computer
2|software|linux

sqlite> delete from product where id=1;
sqlite> select * from product;
2|software|linux

【関連記事】
▶【コマンドまとめ】SQLiteの特徴やメリットをどこよりも詳しく解説 

まとめ

ポテパンダの一言メモ

• SQLiteは組み込み型データベースで、サーバプロセスなし、単一ファイルのみで動作する
• SQLiteは標準SQL全てをサポートしているわけではない
• SQLite最新版は、sqlite3コマンドで操作可能