コンテンツにスキップ

SQLite

出典: フリー教科書『ウィキブックス（Wikibooks）』

SQLiteは、MySQLのようなサーバー・クライアント方式ではなく、アプリケーションに直接組み込んで使用することができる関係データベース管理システム (RDBMS) です。 身近なところでは、Google ChromeやMozilla Firefoxなどの多くのアプリケーションで使用されており、Google AndroidではOSに組み込まれています。 これにより、SQLiteは世界で最もインストールベースの多いRDBMSの1つとして認知されています。

SQLiteについて

[編集]

SQLiteは、高速かつ軽量なリレーショナルデータベース管理システムです。SQLiteは、コンパクトなライブラリとして提供され、モバイルデバイス、デスクトップアプリケーション、Webブラウザ、組み込みシステムなど、あらゆるプラットフォームで使用されています。

SQLiteは、標準のSQLをサポートし、トランザクション処理、データの完全性維持、およびACID（原子性、一貫性、分離性、耐久性）の保証を提供します。また、多くの主要なプログラミング言語（C、Java、Python、PHPなど）で利用可能であり、簡単に統合することができます。

SQLiteの最も重要な特徴の一つは、ファイルベースのデータベースであることです。つまり、データはファイルに保存され、ファイルを別の場所にコピーするだけで、データを移行したりバックアップしたりすることができます。また、複数のプロセスが同時にデータベースにアクセスすることができるため、アプリケーションのパフォーマンスが向上します。

SQLiteは、他のリレーショナルデータベース管理システムと比較して、データベースが比較的小規模である場合に最適です。大量のデータを処理する必要がある場合や、高い同時アクセスが必要な場合には、より高機能なデータベースシステムを検討する必要があります。

ただし、SQLiteは非常に高速であるため、多くの場合、大量のデータを処理する必要がある場合でも、十分なパフォーマンスを発揮します。SQLiteはまた、カスタム拡張機能を提供することができ、標準のSQLコマンド以外の機能を簡単に実装することができます。

SQLiteは、プログラマにとって非常に使いやすく、学びやすいです。SQLの基本構文を理解しているだけで、すぐにSQLiteを利用することができます。また、SQLiteは非常に広く利用されているため、オンラインで豊富な情報を見つけることができます。

インストール

[編集]

本書では、SQLite version3の保守管理用のコマンドラインインターフェース sqlite3の操作を中心に、SQLiteの機能の解説を行いますので、まずsqlite3が、ご自身の環境にインストールされているか確認します。

コマンドラインでsqlite3のバージョンを確認（インストールされている場合）
$ sqlite3 -version
3.46.1 2024-08-13 09:16:08 c9c2ab54ba1f5f46360f1b4f35d849cd3f080e6fc2b6c60e91b16c63f69aalt1 (64-bit)
-versionの前の -（ハイフン）は１つです。
バージョンは異なるかもしれませんが、この書式で１行表示されたら sqlite3 はインストールされています。
その場合は、追加のインストール手順は不要ですが、２フィールド目の日付が年オーダーで古い場合は、よりあらたしいバージョンの入手も検討してください。
コマンドラインでsqlite3のバージョンを確認（インストールされていない場合）
$ sqlite3 -version
bash: sqlite3: command not found
メッセージの細部は異なるかもしれませんが、”command not found” や ”コマンドまたはファイル名が違います” のようなメッセージが返された場合は、 sqlite3 はインストールされていません。
その場合は、下記のインストール手順が必要です。

Microsoft Windows

[編集]

SQLite公式（ダウンロードページ）の Precompiled Binaries for Windows にある sqlite-tools-win-x64-3470000.zip(6.188 MiB)をダウンロードします（3470000の部分はバージョンによって変わります。この場合は、3.47.0）。

sqlite-tools-win-x64-3450000.zipは、ZIP書庫で 

$ unzip -l sqlite-tools-win-x64-3470000.zip 
Archive:  sqlite-tools-win-x64-3470000.zip
  Length      Date    Time    Name
---------  ---------- -----   ----
  3155968  2024-10-22 09:00   sqldiff.exe
  4422144  2024-10-22 09:00   sqlite3_analyzer.exe
  3848704  2024-10-22 08:59   sqlite3.exe
  2959872  2024-10-22 19:40   sqlite3_rsync.exe
---------                     -------
 14386688                     4 files
と4つの実行形式があります。
これらはインストーラーではなく、コマンドそのものです。
このまま実行できます。

Apple macOS

[編集]

2022年11月現在サポートが継続されている macOS には、最初からインストールされています。 Terminalを起動してバージョンを確認してください。 

% sqlite3 --version
3.28.0 2019-04-15 14:49:49 378230ae7f4b721c8b8d83c8ceb891449685cd23b1702a57841f1be40b5daapl
-versionの前の -（ハイフン）は１つです。

FreeBSD

[編集]

ports/pkg の　databases/sqlite3 にあります。

自分でビルド
% sudo make -C /usr/ports/databases/sqlite3/ all install clean
...
% sqlite3 -version
3.46.1 2024-08-13 09:16:08 c9c2ab54ba1f5f46360f1b4f35d849cd3f080e6fc2b6c60e91b16c63f69aalt1 (64-bit)
-versionの前の -（ハイフン）は１つです。
ビルド済パッケージを導入
% sudo pkg install sqlite3-3.46.1,1
...
% sqlite3 -version
3.46.1 2024-08-13 09:16:08 c9c2ab54ba1f5f46360f1b4f35d849cd3f080e6fc2b6c60e91b16c63f69aalt1 (64-bit)

GNU/Linux

[編集]

GNU/Linuxの場合は、ディストーションやパッケージマネージャーによってパッケージの名前やインストール方法が異なるので以下は一例です。

Google ChromeOS/Chromebrew

[編集]

Chromebrewは、sqlite の名前で SQLite のパッケージが用意されています。 

$ crew install -sv sqlite
...
$ sqlite3 -version
3.42.0 2023-05-16 12:36:15 831d0fb2836b71c9bc51067c49fee4b8f18047814f2ff22d817d25195cf3alt1
-versionの前の -（ハイフン）は１つです。

Debian

[編集]
$ sudo apt-get install sqlite3
...
$ sqlite3 -version
3.40.1 2022-12-28 14:03:47 df5c253c0b3dd24916e4ec7cf77d3db5294cc9fd45ae7b9c5e82ad8197f3alt1
-versionの前の -（ハイフン）は１つです。

SQLiteは、アプリケーションプログラムにライブラリーとしてリンクされ、単一のファイルにデータベースを構築します。 このため、MySQLのようにサーバー・クライアントモデルで必要な、クライアントからのデータベース・サーバーへの接続・認証は必要なく、データベースファイルをオープンし使用を開始します。

コマンドラインインターフェース

[編集]

前述の通り、SQLiteはアプリケーションプログラムに組込まれて使用されますが、データベースの保守管理は必要なので、その用途にコマンドラインインターフェースアプリケーション sqlite3が用意されています。

操作と結果概観

[編集]

個別のコマンドの説明を列挙して解説する前に、実際に sqlite3 を使って

  • データベースを開く
  • テーブルを定義する
  • テーブルにレコードを挿入する
  • データを抽出する

などの操作を script(1) を使って記録して様子を掲載します。

typescript
$ cd
$ mkdir sqlite/
$ cd sqlite/
$ sqlite3 periodic.db
SQLite version 3.40.0 2022-11-16 12:10:08
Enter ".help" for usage hints.
sqlite> -- 
sqlite> CREATE TABLE elements ( number, name, symbol ) ;
sqlite> INSERT INTO element (number, name, symbol) VALUES(1, "Hydrogen", "H");
Parse error: no such table: element
sqlite> INSERT INTO elements (number, name, symbol) VALUES(1, "Hydrogen", "H");
sqlite> SELECT * FROM elements;
1|Hydrogen|H
sqlite> .header on
sqlite> SELECT * FROM elements;
number|name|symbol
1|Hydrogen|H
sqlite> INSERT INTO elements (number, name, symbol) VALUES(2, "Helium");
Parse error: 2 values for 3 columns
sqlite> INSERT INTO elements (number, name) VALUES(2, "Helium");
sqlite> SELECT * FROM elements;
number|name|symbol
1|Hydrogen|H
2|Helium|
sqlite> SELECT number,TYPEOF(number),name,TYPEOF(name),symbol,TYPEOF(symbol) FROM elements;
number|TYPEOF(number)|name|TYPEOF(name)|symbol|TYPEOF(symbol)
1|integer|Hydrogen|text|H|text
2|integer|Helium|text||null
sqlite> DELETE FROM elements WHERE name = "Helium" ;
sqlite> SELECT number,TYPEOF(number),name,TYPEOF(name),symbol,TYPEOF(symbol) FROM elements;
number|TYPEOF(number)|name|TYPEOF(name)|symbol|TYPEOF(symbol)
1|integer|Hydrogen|text|H|text
sqlite> .schema
CREATE TABLE elements ( number, name, symbol );
sqlite> .databases
main: /home/user1/sqlite/periodic.db r/w
sqlite> .dump elements
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE elements ( number, name, symbol );
INSERT INTO elements VALUES(1,'Hydrogen','H');
COMMIT;
sqlite> ^D
$ ls -l
total 20
-rw-r--r--. 1 chronos chronos 8192 Nov 24 07:19 periodic.db
$ file periodic.db 
periodic.db: SQLite 3.x database, last written using SQLite version 3040000, file counter 4, database pages 2, cookie 0x1, schema 4, UTF-8, version-valid-for 4

個別のコマンドの説明

[編集]

データベースを開く

[編集]

SQLiteのデータベースは、単一のファイルです。 なのでデータベースへは、S/Cモデルのように接続するのではなくデータベースファイルを開きます。

sqlite3起動時にデータベースファイルを開く

[編集]

sqlite3 の起動時パラメーターにデータベースファイルを与えると、データベースがオープンした状態で起動します。

sqlite3起動時にデータベースファイルを開く
$ sqlite3 periodic.db
SQLite version 3.40.0 2022-11-16 12:10:08
Enter ".help" for usage hints.
sqlite> .databases 
main: /home/user1/sqlite/periodic.db r/w
sqlite>
このとき、データベースファイルがない場合、新しいデータベースファイルが作られます。
.databasesは、オープン中のデータベースの一覧を表示する sqlite3 のコマンドです。

.open

[編集]

sqlite3をパラメータなしで起動したあと、.openコマンドでデータベースファイルを開きます。

sqlite3を起動するときにデータベースファイルを与え開く
$ sqlite3
SQLite version 3.40.0 2022-11-16 12:10:08
Enter ".help" for usage hints.
sqlite> .open periodic.db
sqlite> .databases 
main: /home/user1/sqlite/periodic.db r/w
sqlite>
このとき、データベースファイルがない場合、新しいデータベースファイルが作られます。
$ sqlite3
sqlite>
が、sqlite3 のプロンプトです。

.databases

[編集]
書式
.databases
例
sqlite> .databases
main: /home/user1/sqlite/sample.db r/w
同義のSQL
PRAGMA database_list;
.databases コマンドは、現在開いているデータベースに関する情報を表示します。
.databases までタイプしなくても、.datで同じ意味になります。
これは、.databases にかぎらず、多くのコマンドで. を含め先頭４文字までタイプすればコマンドの別名として受け付けられます。

.open や .databases は、SQLではなくsqlite3のコマンドで、SQLと異なり大文字小文字を区別します。 

sqlite> .OPEN periodic.db
Error: unknown command or invalid arguments:  "OPEN". Enter ".help" for help
sqlite> _

.help

[編集]

sqliteのオンラインヘルプコマンドは.helpです。下記に3.40.0の.helpを示します（なるべく解説へのリンクにしました）

.help
$ sqlite3 /dev/null .help
.archive ...             Manage SQL archives
.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
.changes on|off          Show number of rows changed by SQL
.check GLOB              Fail if output since .testcase does not match
.clone NEWDB             Clone data into NEWDB FROM the existing database
.connection [close] [#]  Open or close an auxiliary database connection
.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
.dump ?OBJECTS?          Render database content as SQL
.echo on|off             Turn command echo on or off
.eqp on|off|full|...     Enable or disable automatic EXPLAIN QUERY PLAN
.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
.filectrl CMD ...        Run various sqlite3_file_control() operations
.fullschema ?--indent?   Show schema and the content of sqlite_stat tables
.headers on|off          Turn display of headers on or off
.help ?-all? ?PATTERN?   Show help text for PATTERN
.import FILE TABLE       Import data FROM FILE into TABLE
.imposter INDEX TABLE    Create imposter table TABLE on index INDEX
.indexes ?TABLE?         Show names of indexes
.limit ?LIMIT? ?VAL?     Display or change the value of an SQLITE_LIMIT
.lint OPTIONS            Report potential schema issues.
.load FILE ?ENTRY?       Load an extension library
.log FILE|off            Turn logging on or off.  FILE can be stderr/stdout
.mode MODE ?OPTIONS?     Set output mode
.nonce STRING            Suspend safe mode for one command if nonce matches
.nullvalue STRING        Use STRING in place of NULL VALUES
.once ?OPTIONS? ?FILE?   Output for the next SQL command only to FILE
.open ?OPTIONS? ?FILE?   Close existing database and reopen FILE
.output ?FILE?           Send output to FILE or stdout if FILE is omitted
.parameter CMD ...       Manage SQL parameter bindings
.print STRING...         Print literal STRING
.progress N              Invoke progress handler after every N opcodes
.prompt MAIN CONTINUE    Replace the standard prompts
.quit                    Exit this program
.read FILE               Read input FROM FILE or command output
.recover                 Recover as much data as possible FROM corrupt db.
.restore ?DB? FILE       Restore content of DB (default "main") FROM FILE
.save ?OPTIONS? FILE     Write database to FILE (an alias for .backup ...)
.scanstats on|off        Turn sqlite3_stmt_scanstatus() metrics on or off
.schema ?PATTERN?        Show the CREATE statements matching PATTERN
.selftest ?OPTIONS?      Run tests defined in the SELFTEST table
.separator COL ?ROW?     Change the column and row separators
.session ?NAME? CMD ...  Create or control sessions
.sha3sum ...             Compute a SHA3 hash of database content
.shell CMD ARGS...       Run CMD ARGS... in a system shell
.show                    Show the current VALUES for various settings
.stats ?ARG?             Show stats or turn stats on or off
.system CMD ARGS...      Run CMD ARGS... in a system shell
.tables ?TABLE?          List names of tables matching LIKE pattern TABLE
.testcase NAME           Begin redirecting output to 'testcase-out.txt'
.testctrl CMD ...        Run various sqlite3_test_control() operations
.timeout MS              Try opening locked tables for MS milliseconds
.timer on|off            Turn SQL timer on or off
.trace ?OPTIONS?         Output each SQL statement as it is run
.vfsinfo ?AUX?           Information about the top-level VFS
.vfslist                 List all available VFSes
.vfsname ?AUX?           Print the name of the VFS stack
.width NUM1 NUM2 ...     Set minimum column widths for columnar output
$
全部のコマンドを覚える必要はありませんが、「○○○なる機能はない」という前に確認しましょう。

データベースファイルの拡張子

[編集]

SQLiteのデータベースファイルの拡張子には、.db、.sqliteや.sqlite3が用いられます。 SQLite自身は、拡張子によって動作を変えるようなことはしていないので、どのような拡張子でも構いませんが、オペレーションシステムの拡張子とファイルタイプの関連付けと重複しないように注意が必要です。

ちなみにSQLiteバージョン３のデータベースファイルのIANAメディアタイプは、application/vnd.sqlite3です[1]。

file(1)が返す情報
$ file periodic.db
periodic.db: SQLite 3.x database, last written using SQLite version 3040000, file counter 4, database pages 2, cookie 0x1, schema 4, UTF-8, version-valid-for 4
$ _
どれがSQLiteのデータベースファイルかわからなくなったときに役に立ちます。

終了コマンド

[編集]

sqlite3は、.exit、.quitまたは^D（EOF）で終了します。

書式
.exit
.quit
^D (ctrl-D)
例
$ sqlite3
SQLite version 3.40.0 2022-11-16 12:10:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .quit
$
sqlite3 を起動してすぐ終了。

複数行入力モードからの回復

[編集]

タイプミスなどで複数行入力モードに入ることがあります 

$ sqlite3
SQLite version 3.40.0 2022-11-16 12:10:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> open periodic.db
   ...> ;
Parse error: near "open": syntax error
  open periodic.db ;
  ^--- error here
sqlite>
タイプミスの場合どのみち syntax error なので ;（セミコロン）を１文字入力します。

このほか、文字列の ” や ’ が閉じられていない場合も考えられます。

テーブル定義

[編集]

テーブルを定義する場合は CREATE TABLE コマンドを使います。

CREATE TABLE

[編集]
テーブル elements の構成とレコード例
原子番号 元素名英名 元素記号
number name symbol
1 Hydrogen H
2 Helium He
3 Lithium Li
4 Beryllium Be
5 Boron B
6 Carbon C
7 Nitrogen N
8 Oxygen O
9 Fluorine F
10 Neon Ne
11 Sodium Na
12 Magnesium Mg
書式
CREATE TABLE テーブル名( カラム名１ [ 型１ ], カラム名２ [ 型２ ], カラム名２ [ 型３ ] … カラム名ｎ [ 型ｎ ]) ;
例
CREATE TABLE elements( number, name, symbol );
最後の ;（セミコロン）を忘れがちです。気をつけましょう。
ここでは「型」を省略しています。

テーブル削除

[編集]

テーブルを削除する場合は DROP TABLE コマンドを使います。

DROP TABLE

[編集]

[TODO]

カラムの型

[編集]

SQLiteの型システムは、標準SQLとはポリシーが異なっており、カラムに定義された型と異なる型の値が与えられると、可能な限り情報を落とさないように、場合によっては型を変えて保存します。 

$ sqlite3
sqlite> CREATE TABLE test(r1, r2);
sqlite> INSERT INTO test VALUES(null, 42);
sqlite> INSERT INTO test VALUES(2.73, "Shizuoka" );
sqlite> INSERT INTO test VALUES("Hello", 1e-10);
sqlite> SELECT * FROM test;
|42
2.73|Shizuoka
Hello|1.0e-10
sqlite> SELECT r1, TYPEOF(r1), r2, TYPEOF(r2) FROM test;
|null|42|integer
2.73|real|Shizuoka|text
Hello|text|1.0e-10|real
sqlite> CREATE TABLE test2(r1 TEXT, r2 NUMERIC);
sqlite> INSERT INTO test2 VALUES(null, 42);
sqlite> INSERT INTO test2 VALUES(2.73, "Shizuoka" );
sqlite> INSERT INTO test2 VALUES("Hello", 1e-10);
sqlite> SELECT r1, TYPEOF(r1), r2, TYPEOF(r2) FROM test2;
|null|42|integer
2.73|text|Shizuoka|text
Hello|text|1.0e-10|real
sqlite>
TYPEOF(カラム名)関数でレコードのカラムの型（実際の型）を参照できます。
TEXTと定義されたカラムに数値が与えられると、数値を文字列表現に変換されて保存されます。
NUMERICと定義されたカラムに文字列が与えられると、
REALに変換できればREALで保存されます。
INTEGERに変換できればINTEGERで保存されます。
それ以外は、TEXTで保存されます。

これは、テーブル定義と異なる型の値がカラムに与えられた場合、SQLiteが情報を落としてしまうと、SQLiteを利用するアプリケーションプログラムが、適切な判断を行う手段を失ってしまうからです。

このような設計上に選択にも、SQLiteが単体のデータベースシステムではなく、アプリケーションプログラムに組込まれるRDBMSエンジンであるという立ち位置の特徴が現れています。

テーブルへのデータの登録

[編集]

ではさっそく、1番目の水素のデータをつけたしてみましょう。

INSERT INTO

[編集]
書式
INSERT INTO テーブル ( カラム１,  カラム２, カラム３, … カラムｎ ) VALUES ( 値１, 値２, 値３, … 値ｎ ) ;
例
INSERT INTO elements (number, name, symbol) VALUES(1, "Hydrogen", "H" );

データの抽出

[編集]

SELECT

[編集]
書式
SELECT カラムリスト FROM テーブル ;
例
sqlite> select name FROM elements;
Hydrogen
sqlite> select name,number FROM elements;
Hydrogen|1
sqlite> select symbol,name,number FROM elements;
H|Hydrogen|1
sqlite> SELECT * FROM elements;
1|Hydrogen|H
カラムリストに * を与えると「すべてのカラム」となります。

.header

[編集]

SELECTコマンドの結果にカラムの名前を含めるか含めないかは、 .headerコマンドで切替えできます。

書式
.header on
.header off
例
sqlite> .header on
sqlite> select symbol,name,number FROM elements;
symbol|name|number
H|Hydrogen|1
.header onでSELECTの結果の１行目にカラムリストを表示します
.header offでSELECTの結果の１行目にカラムリストを表示しません。
ディフォルト は、.header off。
on は、1, true, yes の３つの別名を持ちます。
off は、0, false, no の３つの別名を持ちます。

未入力カラムのあるレコードの挿入

[編集]

レコード（行）を挿入するとき、何らかの理由で全カラムを埋めることができなかった場合は、一部のカラムだけからなるレコードを挿入することもできます。 また、テーブル定義でカラムをNOT NULL制約することでレコードを挿入するときに必須のカラムを設け、入力がない場合をエラーとし挿入を拒絶することもできあます。

未入力カラムのあるレコードの挿入
sqlite> INSERT INTO elements ( number, name ) VALUES ( 2, "Helium" );
sqlite> select name,number,symbol,TYPEOF(symbol) from elements;
name|number|symbol|TYPEOF(symbol)
Hydrogen|1|H|text
Helium|2||null
未入力の２列目のsymbolの値は””、型はnullとなっているのがわかります
TYPEOF()はカラムの「型」を返す関数です。

行の削除

[編集]

行の削除は、DELETEコマンドとWHERE演算子を併用します。

書式
DELETE FROM テーブル WHERE カラム = 値 ;

DELETE

[編集]

ヘリウムのnameデータ ”Helium” の行（レコード）を削除 

sqlite> SELECT * FROM elements;
1|Hydrogen|H
2|Helium||null
sqlite> DELETE FROM elements WHERE name = "Helium" ;
sqlite> SELECT * FROM elements;
1|Hydrogen|H
削除対象は、行です（カラムの削除ではありません）
WHERE 以降を忘れて DELETE FROM elementsとすると、elementsテーブルの全てのレコードが削除されます。

テーブル定義の表示

[編集]

.schema コマンド(Show the CREATE statements matching PATTERN)で、CREATEのフォーマットでテーブル定義を表示できます。

.schema

[編集]
書式
.schema
例
sqlite> .schema
CREATE TABLE elements( number, name, symbol );
同義のSQL
SELECT name,sql FROM sqlite_master WHERE type="table";
sqlite_masterというテーブルがSQLiteのそれぞれのデータベースにあり、テーブルやインデックス・ビューを管理しています。
実際のアプリケーションでは、sqlite3のコマンドは使えないので、同じ機能のSQLを覚えていくことはとても重要です。

テーブルの一覧

[編集]

.tables コマンドでテーブルの一覧を表示できます。

.tables

[編集]
書式
.tables
例
sqlite> .tables
elements
同義のSQL
SELECT name FROM sqlite_master WHERE type="table";
sqlite_masterというテーブルがSQLiteのそれぞれのデータベースにあり、テーブルやインデックス・ビューを管理しています。
実際のアプリケーションでは、sqlite3のコマンドは使えないので、同じ機能のSQLを覚えていくことはとても重要です。


バックアップ/外部出力の方法

[編集]

SQLiteのデータベースは、１つの通常のファイルなので、バックアップはファイルをコピーするだけです。 また、sqlite3のコマンドに、.backup コマンドと .restore が用意されています。

ダンプ

[編集]

バックアップはファイルレベルのコピーでできますが、障害発生に備えてのバックアップとしては、テキストファイルへのダンプが有用です。

ダンプは、SQLiteのコマンドライン管理インターフェース sqlite3を使います。

.dump

[編集]

操作と結果概観で作成したデータベース periodic.dbの テーブル elementsをテキストファイルperiodic.txt にダンプする例です。 

$ sqlite3
SQLite version 3.40.0 2022-11-16 12:10:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open periodic.db
sqlite> .table
elements
sqlite> .dump elements
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE elements( number, name, symbol );
INSERT INTO elements VALUES(1,'Hydrogen','H');
COMMIT;
sqlite> .output periodic.txt
sqlite> .dump elements
sqlite> .output stdout
sqlite> .quit
$ cat periodic.txt
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE elements( number ,name ,symbol );
INSERT INTO elements VALUES(1,'Hydrogen','H');
COMMIT;
$ _

sqlite> .open periodic.db
.open コマンドで、データベース periodic.dbを開いています。
$ sqlite3 periodic.db
と、最初にコマンドラインからデータベースを指定して開くこともできます。
sqlite> .table
elements
.table コマンドで、開いているデータベースに含まれるテーブル名を表示します。
.dump elements
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE elements( number ,name ,symbol );
INSERT INTO elements VALUES(1,'Hydrogen','H');
COMMIT;
.dump コマンドで、テーブルelementsをダンプしています。既定の出力先は標準出力です。
sqlite> .output periodic.txt
出力先をファイルperiodic.txtに変更。
sqlite> .dump elements
またダンプします。今度の出力先は periodic.txt
sqlite> .output stdout
出力先を標準出力に戻します（直後で終了しているので不要とは言えます）。
sqlite> .quit
sqlite3 を終了します。

ダンプからの復元

[編集]

.dumpコマンドでダンプしたデータを現在のデータベースにリストアするには .read コマンドを使います。

.read

[編集]
$ sqlite3
SQLite version 3.40.0 2022-11-16 12:10:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open restore.db
sqlite> .read periodic.txt
sqlite> SELECT * FROM elements;
1|Hydrogen|H

空き領域の開放

[編集]

SQLiteでは、テーブルにデータが追加されると、データベースファイルのサイズは徐々に大きくなるが、テーブルからデータを削除しても、データベースファイルのサイズはすぐには小さくなりません。

VACUUM

[編集]

sqlite3では、VACUUMコマンドで空き領域の開放を行います。 

$ sqlite3
SQLite version 3.40.0 2022-11-16 12:10:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open restore.db
sqlite> VACUUM ;

Pythonからの利用

[編集]

SQLiteはアプリケーションに組込まれ使われますが、ここでは Python からの利用の例を紹介します。

Pythonでは、PEP 249 -- Python Database API Specification v2.0 でデータベースAPIが標準化されており、sqlite3 モジュールでSQLite3をサポートしています。

都道府県コード一覧表（Python版）
import sqlite3

with sqlite3.connect(":memory:") as conn:
    cur = conn.cursor()
    cur.execute('CREATE TABLE 都道府県コード一覧表 (都道府県コード INTEGER, 都道府県 STRING, prefectures STRING)')
    prefs = """\
01	北海道	Hokkaido
02	青森県	Aomori
03	岩手県	Iwate
04	宮城県	Miyagi
05	秋田県	Akita
06	山形県	Yamagata
07	福島県	Fukushima
08	茨城県	Ibaraki
09	栃木県	Tochigi
10	群馬県	Gumma
11	埼玉県	Saitama
12	千葉県	Chiba
13	東京都	Tokyo
14	神奈川県	Kanagawa
15	新潟県	Niigata
16	富山県	Toyama
17	石川県	Ishikawa
18	福井県	Fukui
19	山梨県	Yamanashi
20	長野県	Nagano
21	岐阜県	Gifu
22	静岡県	Shizuoka
23	愛知県	Aichi
24	三重県	Mie
25	滋賀県	Shiga
26	京都府	Kyoto
27	大阪府	Osaka
28	兵庫県	Hyogo
29	奈良県	Nara
30	和歌山県	Wakayama
31	鳥取県	Tottori
32	島根県	Shimane
33	岡山県	Okayama
34	広島県	Hiroshima
35	山口県	Yamaguchi
36	徳島県	Tokushima
37	香川県	Kagawa
38	愛媛県	Ehime
39	高知県	Kochi
40	福岡県	Fukuoka
41	佐賀県	Saga
42	長崎県	Nagasaki
43	熊本県	Kumamoto
44	大分県	Oita
45	宮崎県	Miyazaki
46	鹿児島県	Kagoshima
47	沖縄県	Okinawa
"""
    cur.executemany('INSERT INTO 都道府県コード一覧表 VALUES(?, ?, ?)',
        (line.split("\t") for line in prefs.splitlines()))

    print('SELECT * FROM 都道府県コード一覧表')
    for row in cur.execute('SELECT * FROM 都道府県コード一覧表'):
        print(*row, sep=',')
    print()

    print('SELECT * FROM 都道府県コード一覧表 WHERE 都道府県コード = 22')
    for row in cur.execute('SELECT * FROM 都道府県コード一覧表 WHERE 都道府県コード = 22'):
        print(*row, sep=',')
    print()

    print('SELECT * FROM 都道府県コード一覧表 WHERE 都道府県コード >= 2 and 都道府県コード <= 7')
    for row in cur.execute('SELECT * FROM 都道府県コード一覧表 WHERE 都道府県コード >= 2 and 都道府県コード <= 7'):
        print(*row, sep=',')
    print()
sqlite3モジュールのインポート
import sqlite3
python3では、標準でSQLite3をサポートしています。
インコアＤＢへのコネクションを確立
with sqlite3.connect(":memory:") as conn:
with 文を使うと open関数同様、スコープを抜けると自動的にclose（とcommit）が行われるので、commit忘れを防止するためにも with を使うべきです。
try/except で例外にも対応すべきかもしれません。
単一のファイル（かインコアＤＢ）で構成されるSQLite3に connect するのは奇異に感じますが、これは Python Database API 2 が汎用的に設計されているためで、MySQLのようなサーバークライアントモデルを採用したRDBMSではまさにDBサーバーへの接続（認証付き）で「接続」を行いますので、その意味論に揃えた形です。
sqlite3.Cursorオブジェクトの取得
    cur = conn.cursor()
テーブル定義
    cur.execute('CREATE TABLE 都道府県コード一覧表 (都道府県コード INTEGER, 都道府県 STRING, prefectures STRING)')
文字列としてTSVデータを用意しました。
    prefs = """\
01	北海道	Hokkaido
02	青森県	Aomori
03	岩手県	Iwate
　　︙
47	沖縄県	Okinawa
"""
複数レコードの一括登録
    cur.executemany('INSERT INTO 都道府県コード一覧表 VALUES(?, ?, ?)',
        (line.split("\t") for line in prefs.splitlines()))
.executemany()は、プレースホルダー(?)を含むSQL文にリストの内容を順に渡します。
プレースホルダーを使うこのやり方は、SQLインジェクションへの耐性を上げることができると考えられています。
全レコード取得
    print('SELECT * FROM 都道府県コード一覧表')
    for row in cur.execute('SELECT * FROM 都道府県コード一覧表'):
        print(*row, sep=',')
    print()
都道府県コード = 22のレコードを取得
    print('SELECT * FROM 都道府県コード一覧表 WHERE 都道府県コード = 22')
    for row in cur.execute('SELECT * FROM 都道府県コード一覧表 WHERE 都道府県コード = 22'):
        print(*row, sep=',')
    print()
都道府県コード >= 2 and 都道府県コード <= 7なレコードを取得（東北地方）
    print('SELECT * FROM 都道府県コード一覧表 WHERE 都道府県コード >= 2 and 都道府県コード <= 7')
    for row in cur.execute('SELECT * FROM 都道府県コード一覧表 WHERE 都道府県コード >= 2 and 都道府県コード <= 7'):
        print(*row, sep=',')
周期律表（Python版）
import sqlite3

with sqlite3.connect("periodic.db") as conn:
    cur = conn.cursor()
    cur.execute('CREATE TABLE 周期律表 (原子番号 INTEGER PRIMARY KEY AUTOINCREMENT, 和名 STRING, 英名 STRING, 元素記号 STRING)')
    #cur.execute('INSERT INTO 周期律表 (name, symbol) VALUES("H", "Hydrogen")')
    cur.executemany('INSERT INTO 周期律表(和名,英名,元素記号) VALUES(?, ?, ?)', [
        ("水素", "Hydrogen", "H"),
        ("ヘリウム", "Helium", "He"),
        ("リチウム", "Lithium", "Li"),
        ("ベリリウム", "Beryllium", "Be"),
        ("硼素", "Boron", "B"),	
        ("炭素", "Carbon", "C"),
        ("窒素", "Nitrogen","N"),
        ("酸素", "Oxygen", "O"),
        ("弗素", "Fluorine", "F"),
        ("ネオン", "Neon", "Ne"),
        ("ナトリウム", "Sodium", "Na"),
        ("マグネシウム", "Magnesium", "Mg"),
    ])
    cur.execute('INSERT INTO 周期律表 VALUES(null, "アルミニウム", "Al", "Aluminium")')
    cur.execute('INSERT INTO 周期律表 VALUES(null, "珪素", "Si", "Silicon")')

with sqlite3.connect("periodic.db") as conn2:
    cur = conn2.cursor()
    print("テーブルスキーマーは、PRAGMA table_info(TABLE) で取得できます。")
    for row in cur.execute('PRAGMA table_info("周期律表") '):
        print(*row, sep=',')

    print('SELECT * FROM 周期律表')
    print(*(row[1] for row in cur.execute('PRAGMA table_info("周期律表") ')),sep=',')
    for row in cur.execute('SELECT * FROM 周期律表'):
        print(*row, sep=',')

    print("VIEW は、仮想的なテーブルで検索式に名前をつけたような働きをします")
    cur.execute('CREATE VIEW 原子番号４未満の元素 AS SELECT 英名, 元素記号 FROM 周期律表 WHERE 原子番号 < 4')
    for row in cur.execute('SELECT * FROM 原子番号４未満の元素'):
        print(*row, sep=',')

    print("INDEX は、検索性能が向上する一方レコード追加時にオーバーヘッドが生じるというトレードオフがあります。")
    cur.execute('CREATE INDEX 英名インデックス ON 周期律表(英名)')

    print("UNIQUE INDEX は、同一のテーブル内で値の重複のないフィールドを使ったインデックスです。")
    cur.execute('CREATE UNIQUE INDEX 原子番号英名ユニークインデックス ON 周期律表(原子番号,英名)')
    
    print("sqlite_master はデータベース毎にあるテーブルの台帳です。")
    for row in cur.execute('SELECT * FROM sqlite_master'):
        print(*row, sep=',')
    print("----")
    for row in cur.execute('SELECT name,sql FROM sqlite_master WHERE type="table"'):
        print(*row, sep=',')
    for row in cur.execute('PRAGMA database_list'):
        print(*row, sep=',')
    print(cur.execute('SELECT sqlite_version()').fetchone())
    print("----")
    for line in conn2.iterdump():
        print(line)
実行結果
テーブルスキーマーは、PRAGMA table_info(TABLE) で取得できます。
0,原子番号,INTEGER,0,None,1
1,和名,STRING,0,None,0
2,英名,STRING,0,None,0
3,元素記号,STRING,0,None,0
SELECT * FROM 周期律表
原子番号,和名,英名,元素記号
1,水素,Hydrogen,H
2,ヘリウム,Helium,He
3,リチウム,Lithium,Li
4,ベリリウム,Beryllium,Be
5,硼素,Boron,B
6,炭素,Carbon,C
7,窒素,Nitrogen,N
8,酸素,Oxygen,O
9,弗素,Fluorine,F
10,ネオン,Neon,Ne
11,ナトリウム,Sodium,Na
12,マグネシウム,Magnesium,Mg
13,アルミニウム,Al,Aluminium
14,珪素,Si,Silicon
VIEW は、仮想的なテーブルで検索式に名前をつけたような働きをします
Hydrogen,H
Helium,He
Lithium,Li
INDEX は、検索性能が向上する一方レコード追加時にオーバーヘッドが生じるというトレードオフがあります。
UNIQUE INDEX は、同一のテーブル内で値の重複のないフィールドを使ったインデックスです。
sqlite_master はデータベース毎にあるテーブルの台帳です。
table,周期律表,周期律表,2,CREATE TABLE 周期律表 (原子番号 INTEGER PRIMARY KEY AUTOINCREMENT, 和名 STRING, 英名 STRING, 元素記号 STRING)
table,sqlite_sequence,sqlite_sequence,3,CREATE TABLE sqlite_sequence(name,seq)
view,原子番号４未満の元素,原子番号４未満の元素,0,CREATE VIEW 原子番号４未満の元素 AS SELECT 英名, 元素記号 FROM 周期律表 WHERE 原子番号 < 4
index,英名インデックス,周期律表,4,CREATE INDEX 英名インデックス ON 周期律表(英名)
index,原子番号英名ユニークインデックス,周期律表,5,CREATE UNIQUE INDEX 原子番号英名ユニークインデックス ON 周期律表(原子番号,英名)
----
周期律表,CREATE TABLE 周期律表 (原子番号 INTEGER PRIMARY KEY AUTOINCREMENT, 和名 STRING, 英名 STRING, 元素記号 STRING)
sqlite_sequence,CREATE TABLE sqlite_sequence(name,seq)
0,main,/workspace/periodic.db
('3.31.1',)
----
BEGIN TRANSACTION;
DELETE FROM "sqlite_sequence";
INSERT INTO "sqlite_sequence" VALUES('周期律表',14);
CREATE TABLE 周期律表 (原子番号 INTEGER PRIMARY KEY AUTOINCREMENT, 和名 STRING, 英名 STRING, 元素記号 STRING);
INSERT INTO "周期律表" VALUES(1,'水素','Hydrogen','H');
INSERT INTO "周期律表" VALUES(2,'ヘリウム','Helium','He');
INSERT INTO "周期律表" VALUES(3,'リチウム','Lithium','Li');
INSERT INTO "周期律表" VALUES(4,'ベリリウム','Beryllium','Be');
INSERT INTO "周期律表" VALUES(5,'硼素','Boron','B');
INSERT INTO "周期律表" VALUES(6,'炭素','Carbon','C');
INSERT INTO "周期律表" VALUES(7,'窒素','Nitrogen','N');
INSERT INTO "周期律表" VALUES(8,'酸素','Oxygen','O');
INSERT INTO "周期律表" VALUES(9,'弗素','Fluorine','F');
INSERT INTO "周期律表" VALUES(10,'ネオン','Neon','Ne');
INSERT INTO "周期律表" VALUES(11,'ナトリウム','Sodium','Na');
INSERT INTO "周期律表" VALUES(12,'マグネシウム','Magnesium','Mg');
INSERT INTO "周期律表" VALUES(13,'アルミニウム','Al','Aluminium');
INSERT INTO "周期律表" VALUES(14,'珪素','Si','Silicon');
CREATE VIEW 原子番号４未満の元素 AS SELECT 英名, 元素記号 FROM 周期律表 WHERE 原子番号 < 4;
CREATE INDEX 英名インデックス ON 周期律表(英名);
CREATE UNIQUE INDEX 原子番号英名ユニークインデックス ON 周期律表(原子番号,英名); 
COMMIT;

PHPからの利用

[編集]

SQLiteはアプリケーションに組込まれ使われますが、ここでは PHP からの利用の例を紹介します。

PDO

[編集]

PHPでは、PDOでデータベースAPIが抽象化/標準化されており、SQLiteもPDO経由で使用できます。

都道府県コード一覧表（PHP/PDO版）
<?php
// SQLite3/PDOを使用したデータベースハンドリング
declare(strict_types=1);
header('Content-Type: text/plain');

$db = new PDO("sqlite::memory:");
$db->exec(
    "CREATE TABLE 都道府県コード一覧表 (都道府県コード INTEGER, 都道府県 STRING, prefectures STRING)"
);
$insert = $db->prepare("INSERT INTO 都道府県コード一覧表 VALUES (:n,:ja,:en)");
$db->beginTransaction();

try {
    foreach (
        array_map(
            fn($line): array => explode("\t", $line),
            explode(
                "\n",
                <<<EOS
01	北海道	Hokkaido
02	青森県	Aomori
03	岩手県	Iwate
04	宮城県	Miyagi
05	秋田県	Akita
06	山形県	Yamagata
07	福島県	Fukushima
08	茨城県	Ibaraki
09	栃木県	Tochigi
10	群馬県	Gumma
11	埼玉県	Saitama
12	千葉県	Chiba
13	東京都	Tokyo
14	神奈川県	Kanagawa
15	新潟県	Niigata
16	富山県	Toyama
17	石川県	Ishikawa
18	福井県	Fukui
19	山梨県	Yamanashi
20	長野県	Nagano
21	岐阜県	Gifu
22	静岡県	Shizuoka
23	愛知県	Aichi
24	三重県	Mie
25	滋賀県	Shiga
26	京都府	Kyoto
27	大阪府	Osaka
28	兵庫県	Hyogo
29	奈良県	Nara
30	和歌山県	Wakayama
31	鳥取県	Tottori
32	島根県	Shimane
33	岡山県	Okayama
34	広島県	Hiroshima
35	山口県	Yamaguchi
36	徳島県	Tokushima
37	香川県	Kagawa
38	愛媛県	Ehime
39	高知県	Kochi
40	福岡県	Fukuoka
41	佐賀県	Saga
42	長崎県	Nagasaki
43	熊本県	Kumamoto
44	大分県	Oita
45	宮崎県	Miyazaki
46	鹿児島県	Kagoshima
47	沖縄県	Okinawa
EOS
            )
        )
        as $pref
    ) {
        $insert->bindParam(":n", $pref[0]);
        $insert->bindParam(":ja", $pref[1]);
        $insert->bindParam(":en", $pref[2]);
        $insert->execute();
    }
    $db->commit();
} catch (PDOException $e) {
    echo $e;
    $db->rollback();
    throw $e;
}
$insert = null;

$query = "PRAGMA table_info('都道府県コード一覧表')";
echo "$query", PHP_EOL;
foreach ($db->query($query)->fetchAll(PDO::FETCH_ASSOC) as $assoc) {
    $fields[] = $assoc['name'];
}
$assoc = null;
echo implode(',',$fields),PHP_EOL;
echo PHP_EOL;

$query = "SELECT * FROM 都道府県コード一覧表 WHERE 都道府県コード = 22;";
echo "$query", PHP_EOL;
foreach ($db->query($query)->fetchAll(PDO::FETCH_ASSOC) as $assoc) {
    array_walk($assoc, function(&$v, $k) { $v = "$k:$v"; });
    echo implode(",", $assoc), PHP_EOL;
}
echo PHP_EOL;

$query = "SELECT * FROM 都道府県コード一覧表 WHERE 都道府県 LIKE '%京都%';";
echo "$query", PHP_EOL;
foreach ($db->query($query)->fetchAll(PDO::FETCH_ASSOC) as $assoc) {
    array_walk($assoc, function(&$v, $k) { $v = "$k:$v"; });
    echo implode(",", $assoc), PHP_EOL;
}
$assoc = null;
echo PHP_EOL;

$query = "SELECT * FROM 都道府県コード一覧表;";
echo "$query", PHP_EOL;
foreach ($db->query($query)->fetchAll(PDO::FETCH_ASSOC) as $assoc) {
    array_walk($assoc, function(&$v, $k) { $v = "$k:$v"; });
    echo implode(",", $assoc), PHP_EOL;
}
$assoc = null;
echo PHP_EOL;
実行結果
PRAGMA table_info('都道府県コード一覧表')
都道府県コード,都道府県,prefectures

SELECT * FROM 都道府県コード一覧表 WHERE 都道府県コード = 22;
都道府県コード:22, 都道府県:静岡県, prefectures:Shizuoka

SELECT * FROM 都道府県コード一覧表 WHERE 都道府県 LIKE '%京都%';
都道府県コード:13, 都道府県:東京都, prefectures:Tokyo
都道府県コード:26, 都道府県:京都府, prefectures:Kyoto

SELECT * FROM 都道府県コード一覧表;
都道府県コード:1, 都道府県:北海道, prefectures:Hokkaido
都道府県コード:2, 都道府県:青森県, prefectures:Aomori
都道府県コード:3, 都道府県:岩手県, prefectures:Iwate
都道府県コード:4, 都道府県:宮城県, prefectures:Miyagi
都道府県コード:5, 都道府県:秋田県, prefectures:Akita
都道府県コード:6, 都道府県:山形県, prefectures:Yamagata
都道府県コード:7, 都道府県:福島県, prefectures:Fukushima
都道府県コード:8, 都道府県:茨城県, prefectures:Ibaraki
都道府県コード:9, 都道府県:栃木県, prefectures:Tochigi
都道府県コード:10, 都道府県:群馬県, prefectures:Gumma
都道府県コード:11, 都道府県:埼玉県, prefectures:Saitama
都道府県コード:12, 都道府県:千葉県, prefectures:Chiba
都道府県コード:13, 都道府県:東京都, prefectures:Tokyo
都道府県コード:14, 都道府県:神奈川県, prefectures:Kanagawa
都道府県コード:15, 都道府県:新潟県, prefectures:Niigata
都道府県コード:16, 都道府県:富山県, prefectures:Toyama
都道府県コード:17, 都道府県:石川県, prefectures:Ishikawa
都道府県コード:18, 都道府県:福井県, prefectures:Fukui
都道府県コード:19, 都道府県:山梨県, prefectures:Yamanashi
都道府県コード:20, 都道府県:長野県, prefectures:Nagano
都道府県コード:21, 都道府県:岐阜県, prefectures:Gifu
都道府県コード:22, 都道府県:静岡県, prefectures:Shizuoka
都道府県コード:23, 都道府県:愛知県, prefectures:Aichi
都道府県コード:24, 都道府県:三重県, prefectures:Mie
都道府県コード:25, 都道府県:滋賀県, prefectures:Shiga
都道府県コード:26, 都道府県:京都府, prefectures:Kyoto
都道府県コード:27, 都道府県:大阪府, prefectures:Osaka
都道府県コード:28, 都道府県:兵庫県, prefectures:Hyogo
都道府県コード:29, 都道府県:奈良県, prefectures:Nara
都道府県コード:30, 都道府県:和歌山県, prefectures:Wakayama
都道府県コード:31, 都道府県:鳥取県, prefectures:Tottori
都道府県コード:32, 都道府県:島根県, prefectures:Shimane
都道府県コード:33, 都道府県:岡山県, prefectures:Okayama
都道府県コード:34, 都道府県:広島県, prefectures:Hiroshima
都道府県コード:35, 都道府県:山口県, prefectures:Yamaguchi
都道府県コード:36, 都道府県:徳島県, prefectures:Tokushima
都道府県コード:37, 都道府県:香川県, prefectures:Kagawa
都道府県コード:38, 都道府県:愛媛県, prefectures:Ehime
都道府県コード:39, 都道府県:高知県, prefectures:Kochi
都道府県コード:40, 都道府県:福岡県, prefectures:Fukuoka
都道府県コード:41, 都道府県:佐賀県, prefectures:Saga
都道府県コード:42, 都道府県:長崎県, prefectures:Nagasaki
都道府県コード:43, 都道府県:熊本県, prefectures:Kumamoto
都道府県コード:44, 都道府県:大分県, prefectures:Oita
都道府県コード:45, 都道府県:宮崎県, prefectures:Miyazaki
都道府県コード:46, 都道府県:鹿児島県, prefectures:Kagoshima
都道府県コード:47, 都道府県:沖縄県, prefectures:Okinawa
メモリー上にデータベースを構築し、問合わせと応答を表示しています。
PDO::prepare() を使うことで、プレースホルダー経由でSQLを実行し、SQLインジェクションの驚異を低減しています。
$変数 = null; を随所で行っているのは、PHPでは変数のスコープをループに閉じ込めることができないためで、特にループから脱走したリファレンスが関係したバグは原因の特定が困難になりがちです。

SQLite3クラス

[編集]

PDOとは別に、SQLite専用のSQLite3クラスも用意されています。

都道府県コード一覧表（PHP/ベンダー固有コード版）
<?php
// SQLite3を使用したデータベースハンドリング
declare(strict_types=1);
header("Content-Type: text/plain");

$db = new SQLite3(":memory:");
$db->exec(
    "CREATE TABLE 都道府県コード一覧表 (都道府県コード INTEGER, 都道府県 STRING, prefectures STRING)"
);
$insert = $db->prepare("INSERT INTO 都道府県コード一覧表 VALUES (:n,:ja,:en)");

try {
    foreach (
        array_map(
            fn($line): array => explode("\t", $line),
            explode(
                "\n",
                <<<EOS
01	北海道	Hokkaido
02	青森県	Aomori
03	岩手県	Iwate
04	宮城県	Miyagi
05	秋田県	Akita
06	山形県	Yamagata
07	福島県	Fukushima
08	茨城県	Ibaraki
09	栃木県	Tochigi
10	群馬県	Gumma
11	埼玉県	Saitama
12	千葉県	Chiba
13	東京都	Tokyo
14	神奈川県	Kanagawa
15	新潟県	Niigata
16	富山県	Toyama
17	石川県	Ishikawa
18	福井県	Fukui
19	山梨県	Yamanashi
20	長野県	Nagano
21	岐阜県	Gifu
22	静岡県	Shizuoka
23	愛知県	Aichi
24	三重県	Mie
25	滋賀県	Shiga
26	京都府	Kyoto
27	大阪府	Osaka
28	兵庫県	Hyogo
29	奈良県	Nara
30	和歌山県	Wakayama
31	鳥取県	Tottori
32	島根県	Shimane
33	岡山県	Okayama
34	広島県	Hiroshima
35	山口県	Yamaguchi
36	徳島県	Tokushima
37	香川県	Kagawa
38	愛媛県	Ehime
39	高知県	Kochi
40	福岡県	Fukuoka
41	佐賀県	Saga
42	長崎県	Nagasaki
43	熊本県	Kumamoto
44	大分県	Oita
45	宮崎県	Miyazaki
46	鹿児島県	Kagoshima
47	沖縄県	Okinawa
EOS
            )
        )
        as $pref
    ) {
        $insert->bindParam(":n", $pref[0], SQLITE3_INTEGER);
        $insert->bindParam(":ja", $pref[1], SQLITE3_TEXT);
        $insert->bindParam(":en", $pref[2], SQLITE3_TEXT);
        $insert->execute();
    }
} catch (Exception $e) {
    echo "Caught exception: " . $e->getMessage();
}
$insert = null;

$query = "PRAGMA table_info('都道府県コード一覧表')";
echo "$query", PHP_EOL;
for (
    $result = $db->query($query);
    ($assoc = $result->fetchArray(SQLITE3_ASSOC));
    $fields[] = $assoc["name"]
) {}
$assoc = null;
echo implode(",", $fields), PHP_EOL;
echo PHP_EOL;

$query = "SELECT * FROM 都道府県コード一覧表 WHERE 都道府県コード = 22;";
echo "$query", PHP_EOL;
for (
    $result = $db->query($query);
    ($assoc = $result->fetchArray(SQLITE3_ASSOC));

) {
    array_walk($assoc, function(&$v, $k) { $v = "$k:$v"; });
    echo implode(",", $assoc), PHP_EOL;
}
$assoc = null;
echo PHP_EOL;

$query = "SELECT * FROM 都道府県コード一覧表 WHERE 都道府県 LIKE '%京都%';";
echo "$query", PHP_EOL;
for (
    $result = $db->query($query);
    ($assoc = $result->fetchArray(SQLITE3_ASSOC));

) {
    array_walk($assoc, function(&$v, $k) { $v = "$k:$v"; });
    echo implode(",", $assoc), PHP_EOL;
}
$assoc = null;
echo PHP_EOL;

$query = "SELECT * FROM 都道府県コード一覧表;";
echo "$query", PHP_EOL;
for (
    $result = $db->query($query);
    ($assoc = $result->fetchArray(SQLITE3_ASSOC));

) {
    array_walk($assoc, function(&$v, $k) { $v = "$k:$v"; });
    echo implode(",", $assoc), PHP_EOL;
}
$assoc = null;
echo PHP_EOL;

脚註

[編集]
  1. ^ application/vnd.sqlite3

関連リンク

[編集]
  • SQL SQL一般の仕様や文法についての解説
"https://ja.wikibooks.org/w/index.php?title=SQLite&oldid=262869" より作成