SQLite

SQLiteはスタンドアロン型のオープンソースRDBMSです。 Oracleなどのサーバーアプリケーションとして動作するものと異なり、 SQLiteは各データベースがファイルシステム上のファイルに対応し、 データを扱うアプリケーションの起動と同時にオープンされます。 つまり、MicrosoftのAccessに近いイメージです。
SQLiteはバージョン2から3になるときに大きく仕様が変わりました。 このページでは全てバージョン3の使用方法を説明しています。 今はバージョン3が定着したため、SQLiteといえばSQLite3を指すことがほとんどです。

インストール方法

SQLiteのホームページから、 「Download」を押してダウンロードページに行きます。 「Precompiled Binaries For Windows」の「sqlite-tools-***.zip」 をダウンロードして展開すると、sqlite3.exe などのファイルがあるので、 これを適当な場所に配置すればインストール完了です。 これが、コマンドラインツール(OracleのSQL*Plusみたいなもの)です。

コマンドラインツールの使い方

コマンドラインツール sqlite3.exe の典型的な起動方法は

D:\work> C:\Wintools\sqlite3\sqlite3.exe D:\the\path\of\testdb
SQLite version 3.38.1 2022-03-12 13:37:29
Enter ".help" for usage hints.
sqlite> create table shiten
   ...> (shiten_code varchar(5) primary key,
   ...> shiten_name varchar(20),
   ...> area_code varchar(1),
   ...> address varchar(200),
   ...> employee_num int);

sqlite> .tables
sqlite> .schema ITEMS

SQLスクリプトをコマンドラインから実行する

バッチ処理的にスクリプトを読ませることもできます。 sqlite3プログラムでは3つの方法が用意されています。
1つはあらかじめ一群のコマンドを順番に書いたスクリプトファイルを、 標準入力からリダイレクトして読ませる方法。
もう1つは sqlite3.exe の2番目の引数を指定すれば、 それがSQLとして解釈実行されるというものです。
3番目は、sqlite3.exeを起動した後「.read {ファイル名}」コマンドで実行する方法です。 (.loadではなく.readなので要注意！！.loadは別のコマンドです。)

●SQLスクリプトのコメント
コメントはC言語式の /* コメント */ と、Oracle式の「--」が両方使えます。

データのインポート

コマンドラインツールといえばデータのインポート。SQLiteでは「.import ファイル名 テーブル名」で可能です。

sqlite> .import .\\area.txt AREA

sqlite> .separator \t
sqlite> .import .\\area.txt AREA

sqlite> .mode csv
sqlite> .import .\\area2.csv AREA2

日本語データのエンコーディング（文字セット）

日本語データは、シフトJISでもUTF-8でも一応データベースにロードすることはできます。 ただし公式にサポートされているのはUTF-8(UTF-8N)だけなので、それに統一するのが良いと思います。

ちなみに古いSQLite3のバージョンでは、内部データをUTF-8で格納すると、 Windowsのコマンドプロンプトからsqlite3.exeを実行して検索などを行うと、 日本語は文字化けしていたため、「chcp 65001」と打ってUTF-8に変更する必要がありました。
現在のバージョンでは、内部データがUTF-8でもコマンドプロンプトではシフトJISに自動変換されて表示されます。
また、古いsqlite3.exeのCUIでは、WHERE句に日本語を入力すると正しい結果が得られませんでした (シフトJISでも、UTF-8でも)。現在は、日本語も入力できます。

SQLiteのデータ型

表の列定義に使えるデータ型はTEXT、INTEGER、REAL、NUMERIC、BLOBの5種類です。
他のRDBMSや標準SQL92で使われるデータ型名の多くは、 上記のどれかの別名として有効なので、違和感なくSQLを書くことができるでしょう。
例えばINT、TINYINT、SMALLINT、INT8などは、全てINTEGERの別名です。 PostgreSQLなどと同じですね。
VARCHAR、NCHAR、NVARCHAR、CLOBなどは、全てTEXTの別名です。 こちらはOracle、Microsoft SQL Serverなどでおなじみのデータ型ですね。
またFLOATとDOUBLEはREALの別名です。
そして、DECIMAL、BOOLEAN、DATE、DATETIMEは、内部的にはNUMERICの別名として処理されています。

現在時刻を返す関数

SQLiteにも多くの組み込み関数があり、その中に現在時刻を返すものも用意されています。 テーブルの列定義でDEFAULT値として現在時刻を設定するには次のようにします。

CDATE DATE NOT NULL DEFAULT (DATETIME('now','localtime'))

.import items.dat ITEMS

UPDATE ITEMS SET CDATE=DATETIME('now','localtime')

UPDATE ITEMS SET CDATE=DATE('now','localtime')

UPDATE ITEMS SET CDATE=DATE('now','+7 day','localtime')

自動的に増える整数列(AUTOINCREMENT)

SQLiteにはシーケンスがありません。 行を追加するたび値が自動的に1ずつ増えていく主キー列を定義するには、 PRIMARY KEYとAUTOINCREMENTを両方指定したキーを定義します。

CREATE TABLE ITEMS (
  ITEM_ID   INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  ITEM_NAME VARCHAR(30) NOT NULL,
  UOM       VARCHAR(3)  NOT NULL DEFAULT 'PCS',
  CDATE     DATE DEFAULT (DATETIME('now','localtime'))
);

CREATE TABLE ITEMS_IF (
  ITEM_NAME VARCHAR(30) NOT NULL,
  UOM       VARCHAR(3)  NOT NULL DEFAULT 'PCS'
);

-- .import コマンドでITEMS_IFテーブルにデータをロードする

INSERT INTO ITEMS(ITEM_NAME, UOM) SELECT ITEM_NAME, UOM FROM ITEMS_IF;

データのエクスポートとダンプ

テーブルのデータをCSVやタブ区切りなどの形式でファイルに出力できます。

.headers on(またはoff)
.mode tabs
.output ./export.txt
SELECT * FROM ITEMS
.output stdout

一方、今テーブルに入っているデータを完全に復元するための CREATE TABLE文やINSERT文を順番に出力することもできます。 これは.dumpコマンドで可能で、出力したファイルを.readすれば元のテーブルが復元できます。

.separator \t
.output ./dump.txt
.dump items
.output stdout

主にLinux系OSでの注意

●attempt to write a readonly database
実行ユーザーにデータベースファイルへの書き込み権限を付けているのに、 このエラーが発生することがあります。
ネット情報によると原因は幾つかあるようですが、 私がはまったのはデータベースのファイルを置いてある「ディレクトリ自身」にも書き込み権限が必要なことです。 対応するとあっさり解決しました。

テーブルの列名の取得

SELECT GROUP_CONCAT(NAME, ',') FROM PRAGMA_TABLE_INFO('ITEMS');

SELECT 'a.' || GROUP_CONCAT(NAME, ',a.') FROM PRAGMA_TABLE_INFO('ITEMS');

その他の色々

SQLiteはFOREIGN KEYをサポートしています(使っても文法エラーにはならない)が、 実際のチェックは効いていません。実際に、違反したデータが登録できてしまいます。

SQLiteにはOracleのようなCREATE OR REPLACE文はありませんが、 代わりにINSERT OR REPLACEという文があります。 こちらに例が載っています。 これは結構便利。

また、SQLiteにはIFNULLという、OracleのNVLに当たる関数があります。これも便利です。