多くのアプリケーションでは、重いデータベースシステムは必要ありません。SQLiteは軽量でありながら高性能なデータベースエンジンとして、ほとんどの用途に対応できます。
SQLiteはC言語で書かれたオープンソースの軽量で高速、独立した高信頼性SQLデータベースエンジンです。スマートフォンやコンピュータのほぼすべてのプラットフォームで動作し、多くのアプリケーションに組み込まれて使用されています。
SQLiteは安定したファイルフォーマット、クロスプラットフォーム対応、後方互換性などの特徴も持ち合わせています。開発者は少なくとも2050年までこのファイルフォーマットを変更しないことを約束しています。
本記事では、SQLiteの基礎知識と使用方法について詳しく解説します。
SQLiteのインストール
SQLiteの公式ページ[1](https://sqlite.org/download.html)から、お使いのシステムに合った圧縮ファイルをダウンロードしてください。
ダウンロードして解凍すると、Windows、Linux、Mac OSのいずれのシステムでもsqlite3コマンドラインツールが得られます。
以下はMac OSで解凍後に得られるコマンドラインツールの例です：
$ ls -l sqlite-tools-osx-x64-3450100/
total 14952
-rwxr-xr-x@ 1 user staff 1907136 1 31 00:27 sqldiff
-rwxr-xr-x@ 1 user staff 2263792 1 31 00:25 sqlite3
-rwxr-xr-x@ 1 user staff 3478872 1 31 00:27 sqlite3_analyzer
SQLiteの使用シーン
SQLiteはMySQL、Oracle、PostgreSQL、SQL Serverなどのクライアント/サーバータイプのSQLデータベースエンジンとは異なり、解決する問題も異なります。
サーバーサイドのSQLデータベースエンジンは、企業データの共有ストレージを実現することを目的として設計されており、**スケーラビリティ、同時実行性、集中化、制御性**を重視しています。一方、SQLiteは主に個人用アプリケーションやデバイスのローカルデータストレージを提供するために使用され、**経済性、効率性、信頼性、独立性、シンプルさ**を重視しています。
SQLiteが適している使用シーン：
組み込みデバイスとIoT
SQLiteは追加の管理やサービス起動を必要としないため、スマートフォン、テレビ、セットトップボックス、ゲーム機、カメラ、時計などのスマートデバイスに最適です。Webサイト
多くの低トラフィックWebサイトでは、SQLiteをデータベースとして使用できます。公式サイトによると、通常1日あたりのアクセス数が10万未満のWebサイトはSQLiteで良好に動作します。SQLiteの公式サイト(https://www.sqlite.org/)自体もSQLiteをデータベースエンジンとして使用しており、1日約50万のHTTPリクエストを処理しています。データ分析
SQLite3コマンドラインツールはCSVやExcelファイルとの簡単な相互作用を提供し、大規模データセットの分析に適しています。また、Pythonなど多くの言語にSQLiteサポートが組み込まれているため、データ操作用のスクリプトを簡単に作成できます。キャッシュ
SQLiteをアプリケーションサービスのキャッシュとして使用し、中央データベースの負荷を軽減できます。メモリまたは一時データベース
SQLiteのシンプルさと高速性により、アプリケーションのデモや日常的なテストに非常に適しています。
SQLiteが適さないシーン：
ネットワーク経由でのデータベースアクセスが必要な場合
SQLiteはローカルファイルデータベースであり、リモートアクセス機能を提供していません。高可用性とスケーラビリティが要求される場合
SQLiteはシンプルで使いやすいですが、スケーラブルではありません。非常に大量のデータを扱う場合
SQLiteデータベースのサイズ制限は最大281TBですが、すべてのデータを単一のディスクに保存する必要があります。書き込み操作が高並行性の場合
SQLiteは一度に1つの書き込み操作しか実行できず、他の書き込み操作はキューに入れられる必要があります。
SQLite3コマンド操作
SQLiteはsqlite3（Windowsではsqlite3.exe）コマンドラインツールを提供しており、このツールを使用してSQLiteデータベース操作とSQLステートメントを実行できます。
コマンドプロンプトで直接./sqlite3を実行してsqlite3プログラムを起動し、.helpと入力してヘルプガイドを表示したり、.help キーワードと入力して特定のキーワードのヘルプ情報を取得したりできます。
コマンドの一部を以下に示します：
sqlite> .help
.databases List names and files of attached databases
.dbconfig ?op? ?val? List or change sqlite3_db_config() options
.dbinfo ?DB? Show status information about the database
.excel Display the output of next command in spreadsheet
.exit ?CODE? Exit this program with return-code CODE
.expert EXPERIMENTAL. Suggest indexes for queries
.explain ?on|off|auto? Change the EXPLAIN formatting mode. Default: auto
.help ?-all? ?PATTERN? Show help text for PATTERN
.indexes ?TABLE? Show names of indexes
.mode MODE ?OPTIONS? Set output mode
.open ?OPTIONS? ?FILE? Close existing database and reopen FILE
.output ?FILE? Send output to FILE or stdout if FILE is omitted
.quit Exit this program
.read FILE Read input from FILE or command output
.schema ?PATTERN? Show the CREATE statements matching PATTERN
.show Show the current values for various settings
.tables ?TABLE? List names of tables matching LIKE pattern TABLE
sqlite3は入力行を読み取り、SQLiteライブラリに渡して実行します。SQLステートメントはセミコロン;で終わる必要があり、複数行にわたって自由に入力できます。
sqlite3では、SQLステートメントはセミコロン;で終わる必要があり、複数行にわたる入力が可能です。.helpや.tablesなどの特殊なドットコマンドは小数点.で始まり、**セミコロンは不要**です。
SQLiteでデータベースを作成
sqlite3 filenameを直接実行してSQLiteデータベースを開くか作成します。ファイルが存在しない場合、SQLiteは自動的に作成します。
例：my_database.dbという名前のSQLiteデータベースファイルを開くか作成します。
$ sqlite3 my_database.db
SQLite version 3.39.5 2022-10-14 20:58:05
Enter ".help" for usage hints.
sqlite>
まず空のファイルを作成し、次にsqlite3コマンドで開くこともできます。次にCREATE TABLEコマンドを使用してemployeeという名前のテーブルを作成し、.tablesコマンドで既存のテーブルを表示し、.exitでsqlite3ツールを終了します。
$ touch sample.db
$ sqlite3 sample.db
SQLite version 3.39.5 2022-10-14 20:58:05
Enter ".help" for usage hints.
sqlite> create table employee(full_name text, years_of_service int);
sqlite> .tables
employee
sqlite>
現在のデータベースを表示
ドットコマンド.databasesを使用して、現在開いているデータベースを表示します。
sqlite> .databases
main: /Users/user/develop/sqlite-tools-osx-x86-3420000/my_database.db r/w
sqlite>
SQLiteでの基本的な操作
SQLiteは標準的なSQLステートメント仕様とほぼ完全に互換性があるため、標準的なSQLステートメントを直接作成して実行できます。
テーブルの作成：
sqlite> create table employee(full_name text, years_of_service int);
sqlite>
データの挿入：
sqlite> insert into employee values('Taro Yamada', 5);
sqlite> insert into employee values('Hanako Sato', 8);
sqlite> insert into employee values('Jiro Suzuki', 12);
データのクエリ：
sqlite> select * from employee;
Taro Yamada|5
Hanako Sato|8
Jiro Suzuki|12
インデックスの追加、employeeテーブルのfull_nameにemployee_full_nameという名前のインデックスを作成：
sqlite> create index employee_full_name on employee(full_name);
出力フォーマットの変更
データをクエリする際、SQLiteはデフォルトで|を使用して各列データを区切りますが、これは読みにくい場合があります。実際、sqlite3ツールは複数の出力フォーマットをサポートしており、デフォルトはlistモードです。
利用可能な出力フォーマットは次のとおりです：ascii、**box**、csv、**column**、html、insert、**json**、line、list、markdown、quote、**table**。
.modeコマンドを使用して出力フォーマットを変更できます。
Boxフォーマット：
sqlite> .mode box
sqlite> select * from employee;
┌───────────────┬──────────────────┐
│ full_name │ years_of_service │
├───────────────┼──────────────────┤
│ Taro Yamada │ 5 │
│ Hanako Sato │ 8 │
│ Jiro Suzuki │ 12 │
└───────────────┴──────────────────┘
jsonフォーマット：
sqlite> .mode json
sqlite> select * from employee;
[{"full_name":"Taro Yamada","years_of_service":5},
{"full_name":"Hanako Sato","years_of_service":8},
{"full_name":"Jiro Suzuki","years_of_service":12}]
columnフォーマット：
sqlite> .mode column
sqlite> select * from employee;
full_name years_of_service
------------ ----------------
Taro Yamada 5
Hanako Sato 8
Jiro Suzuki 12
tableフォーマット：
sqlite> .mode table
sqlite> select * from employee;
+--------------+------------------+
│ full_name │ years_of_service │
+--------------+------------------+
│ Taro Yamada │ 5 │
│ Hanako Sato │ 8 │
│ Jiro Suzuki │ 12 │
+--------------+------------------+
sqlite>
スキーマのクエリ
sqlite3ツールには、データベースのスキーマを表示するための便利なコマンドがいくつか用意されています。これらのコマンドは単なるショートカットとして提供されています。
例えば、.tableはデータベース内のすべてのテーブルを表示します：
sqlite> .table
employee
ドットコマンド.tableは以下のクエリステートメントと同等です。
sqlite> SELECT name FROM sqlite_schema
...> WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'
...> ;
employee
sqlite_masterはSQLite内の特別なテーブルで、データベースのスキーマ情報が含まれています。このテーブルをクエリして、テーブルの作成ステートメントやインデックス情報を取得できます。
sqlite> .mode table
sqlite> select * from sqlite_schema;
+-------+--------------------+------------+----------+---------------------------------------------------------+
│ type │ name │ tbl_name │ rootpage │ sql │
+-------+--------------------+------------+----------+---------------------------------------------------------+
│ table │ employee │ employee │ 2 │ CREATE TABLE employee(full_name text, years_of_service int) │
│ index │ employee_full_name │ employee │ 3 │ CREATE INDEX employee_full_name on employee(full_name) │
+-------+--------------------+------------+----------+---------------------------------------------------------+
.indexesを使用してインデックスを表示し、.schemaを使用してスキーマの詳細を表示します。
sqlite> .indexes
employee_full_name
sqlite> .schema
CREATE TABLE employee(full_name text, years_of_service int);
CREATE INDEX employee_full_name on employee(full_name);
結果をファイルに書き出す
.output filenameコマンドを使用して、クエリ結果を指定したファイルに書き込みます。
以下は、まず.mode jsonを使用して出力をJSONフォーマットに変更し、次にテーブルをクエリしてquery_result.jsonに書き出す例です。
sqlite> .output query_result.json
sqlite> .mode json
sqlite> select * from employee;
sqlite> .exit
$ cat query_result.json
[{"full_name":"Taro Yamada","years_of_service":5},
{"full_name":"Hanako Sato","years_of_service":8},
{"full_name":"Jiro Suzuki","years_of_service":12}]
Excelに出力して開く
.excelを使用すると、次のクエリステートメントの出力をExcelに送信できます。
sqlite> .excel
sqlite> select * from sqlite_schema;
結果をファイルに書き出す：
sqlite> .output query_result.txt
sqlite> select * from sqlite_schema;
sqlite> select * from employee;
SQLスクリプトの読み込みと実行
.readを使用すると、指定したファイル内のSQLステートメントを読み込んで実行できます。これは、SQLスクリプトをバッチで実行する必要があるシーンで非常に便利です。
SQLファイルの作成：
$ echo "select * from employee" > query_script.sql
$ cat query_script.sql
select * from employee
$ ./sqlite3 my_database.db
SQLite version 3.42.0 2023-05-16 12:36:15
Enter ".help" for usage hints.
sqlite> .mode table
sqlite> .read query_script.sql
+--------------+------------------+
│ full_name │ years_of_service │
+--------------+------------------+
│ Taro Yamada │ 5 │
│ Hanako Sato │ 8 │
│ Jiro Suzuki │ 12 │
+--------------+------------------+
sqlite>
SQLiteのバックアップと復元
データベース操作では、データ損失を防ぎ、データの持続性を確保するために、バックアップと復元が重要なステップです。SQLiteは、データベースのバックアップと復元を行うための簡単な方法を提供しています。
SQLiteでは、データベース全体をSQLスクリプトとしてエクスポートすることでデータベースをバックアップできます。この機能は.dumpコマンドを使用して実現します。
$ ./sqlite3 my_database.db
SQLite version 3.42.0 2023-05-16 12:36:15
Enter ".help" for usage hints.
sqlite> .output backup_file.sql
sqlite> .dump
sqlite> .exit
$ cat backup_file.sql
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE employee(full_name text, years_of_service int);
INSERT INTO employee VALUES('Taro Yamada',5);
INSERT INTO employee VALUES('Hanako Sato',8);
INSERT INTO employee VALUES('Jiro Suzuki',12);
CREATE INDEX employee_full_name on employee(full_name);
COMMIT;
これにより、my_database.dbデータベース全体がbackup_file.sqlファイルにエクスポートされます。このSQLファイルには、データベースを再構築するために必要なすべてのSQLステートメントが含まれています。データベースを復元するには、sqlite3でこのスクリプトを実行するだけです。
例：データをmy_database_2というライブラリに復元します。
$ ./sqlite3 my_database_2.db
SQLite version 3.42.0 2023-05-16 12:36:15
Enter ".help" for usage hints.
sqlite> .read backup_file.sql
sqlite> select * from employee;
Taro Yamada|5
Hanako Sato|8
Jiro Suzuki|12
これにより、backup_file.sqlファイル内のすべてのSQLステートメントが実行され、データベースが再構築されます。以上のバックアップと復元方法により、SQLiteデータベースのデータを確実に保護し、必要なときに迅速に復元できます。
SQLiteの可視化ツール
コマンドライン操作は直感的ではないかもしれません。視覚的な操作を好む場合は、SQLite Database Browserをダウンロードして使用できます。
ダウンロードページ：https://sqlitebrowser.org/dl/
付録
SQLiteの一般的な関数リスト（名前から意味がわかるためコメントは省略）。
| 関数1 | 関数2 | 関数3 | 関数4 |
|---|---|---|---|
| abs(X) | changes() | char(X1,X2,…,XN) | coalesce(X,Y,…) |
| concat(X,…) | concat_ws(SEP,X,…) | format(FORMAT,…) | glob(X,Y) |
| hex(X) | ifnull(X,Y) | iif(X,Y,Z) | instr(X,Y) |
| last_insert_rowid() | length(X) | like(X,Y) | like(X,Y,Z) |
| likelihood(X,Y) | likely(X) | load_extension(X) | load_extension(X,Y) |
| lower(X) | ltrim(X) | ltrim(X,Y) | max(X,Y,…) |
| min(X,Y,…) | nullif(X,Y) | octet_length(X) | printf(FORMAT,…) |
| quote(X) | random() | randomblob(N) | replace(X,Y,Z) |
| round(X) | round(X,Y) | rtrim(X) | rtrim(X,Y) |
| sign(X) | soundex(X) | sqlite_compileoption_get(N) | sqlite_compileoption_used(X) |
| sqlite_offset(X) | sqlite_source_id() | sqlite_version() | substr(X,Y) |
| substr(X,Y,Z) | substring(X,Y) | substring(X,Y,Z) | total_changes() |
| trim(X) | trim(X,Y) |
参考
- SQLiteオープンソースコード：https://www.sqlite.org/cgi/src/dir?ci=trunk
- SQLiteファイルフォーマット紹介：https://sqlite.org/fileformat2.html
- SQLite可視化ツール：https://sqlitebrowser.org/dl/
- SQL関数ドキュメント：https://www.sqlite.org/lang_corefunc.html
引用リンク
[1] SQLite公式ページ: https://sqlite.org/download.html