はじめてのSQLite – 2018年版

  • このエントリーをはてなブックマークに追加
  • LINEで送る
この記事は作成中です。随時更新されます

SQLiteはアプリケーションに組み込んで使う、非常に軽量なデータベースです。

MySQLやPostgreSQL、Oracleなどは「サーバ」として常時起動した状態で動かす場合が多いわけですが、SQLiteは独自の形式で保存されたファイルをプログラミング言語から必要なタイミングで読み書きする形になります。PHP、Pythonでは言語が標準でサポート、C、C++、D、Curl、Perl、Ruby、Delphiなど多数の言語でバインディング用のライブラリがリリースされています。

誤解されがちですがSQLiteには様々な機能が搭載されており小〜中規模なデータ量であれば一般的な運用に十分に耐えられます。しかし、逆説的に言えば大量のデータに大量のリクエストを捌くような用途には向きません。また多数のユーザーが同時に触るような環境でGRANTで権限管理を行うようなこともできませんし、レプリケーションも行えません。あくまで何らかのアプリの内部に組み込むことを前提とした運用を想定してもらえれば良いでしょう。

※この記事では特に記載が無い限りSQLite3を対象としています。なお諸般の事情でSQLite4はお亡くなりになった関係で3が最新です。


- SponsoredLink -

CLIツール

インストール

macOSではbrew経由でインストール可能です。

$ brew install sqlite3

Linuxではyumやaptなどの一般的なパッケージ管理ソフトで導入できます。

$ sudo yum install sqlite sqlite-devel sqlite-doc
$ sudo apt-get install sqlite3 sqlite3-doc

Windowsの場合は実行ファイル(バイナリ)をダウンロードすればOK(sqlite-tools-win32-x86-3230100.zip的なリンクからDL)。

操作方法

起動/終了

SQLiteではデータベース(スキーマ)=ファイルの関係になります。CLI起動時にはデータベース(ファイルのパス)を指定します。

$ sqlite3 (データベース名)
  • データベースを新規に作成する場合は、保存したいファイル名(パス)を指定します。
    • データを更新するなど実際にデータを操作する処理が走らない場合にはファイルは作成されないようです。
  • データベース用のファイルの拡張子には慣習的に以下をつけることが多いようです。特段の理由がなければ.sqlite3がわかりやすいですね。
    • foo.sqlite3
    • foo.sqlite
    • foo.db

.exitまたは.quitでCLIを終了します。

sqlite> .exit

CLIから直接SQLを実行

起動時にデータベース名に続いてSQL文を指定すればその場で実行され、実行が終わるとそのままCLIは終了します。

### 文字列で指定
$ sqlite3 foo.sqlite3 'SELECT * FROM bar'
3|2018-05-28 18:00:00
2|2018-05-28 12:00:00
1|2018-05-28 10:40:14

### リダイレクトで食わせても同様の結果に
$ cat hoge.sql
SELECT * FROM bar
$ sqlite3 foo.sqlite3 < hoge.sql
3|2018-05-28 18:00:00
2|2018-05-28 12:00:00
1|2018-05-28 10:40:14

なお-csvオプションをつけるとCSV形式で、-headerオプションで列名が表示されます。

$ sqlite3 -csv -header foo.sqlite3 'SELECT * FROM bar'
id,created
3,"2018-05-28 18:00:00"
2,"2018-05-28 12:00:00"
1,"2018-05-28 10:40:14"

ここではわかりやすいようにSELECT文を実行していますが、その他のSQLも同様に実行できます。

データベース

.databasesで現在接続中のファイル一覧を表示できます。

sqlite> .databases
main: /Users/katsube/Develop/foo.sqlite3

テーブルの情報を表示

SQLiteのCLIツールを起動後、.tablesでテーブルの一覧を表示できます。

sqlite> .tables
foo

.schemaで各テーブルの定義を参照できます。

sqlite> .schema
CREATE TABLE Foo(id INTEGER, name TEXT);
  • CREATE TABLE文を実行した際の文字列がそのまま表示されます。途中で改行してスペースなどでインデントを整えている場合はちょっと見づらいのが難点ですね。

エクスポート

.outputで出力ファイルを指定し、.dumpでデータベースの中身をすべてSQL文として出力します。.outputは画面上に表示されるはずだったものをファイルに出力してくれるコマンドですので、本件以外のことにも利用可能です。

sqlite> .output ./dump.sql
sqlite> .dump
sqlite> .exit

$ ls
dump.sql

特定のテーブルだけ出力したい場合は、.dumpに続いてテーブル名を指定すればOKです。次の例ではfooテーブルのみダンプしています。

sqlite> .output ./dump.sql
sqlite> .dump foo

インポート

SQLが記述されたファイルであれば、前述の通りCLI起動時にファイルを食わせてしまうのが手っ取り早いです。

$ sqlite3 foo.sqlite3 < dump.sql

とはいえ、多くの場合はCSVなどEXCELで作ったデータを取り込みたいといったケースですよね。この場合には.importを用います。

sqlite> .separator ,
sqlite> .import foo.csv foo
  • 列の区切り文字がデフォルトではパイプ(|)という誰得な設定になっているので、インポート前に変更しておきます。
    • タブ区切りの場合は \t です
  • 文字列型の場合にはシングルコーテーション(‘)ではなく、ダブルコーテーション(“)で囲みます
    • シングルコーテーションで囲むと、それも文字の一部として認識されてしまいます。
    • テーブルを定義する際にデータ型が定義されている場合、囲わないでもSQLiteが自動で判別してくれます

データ型

5つのデータ型

SQLiteでは5つのデータ型が利用できます。

データ型概要
NULLNULLです
INTEGER符号付きの整数型。自動的に1, 2, 3, 4, 6, 8bytesのいずれかの領域に保存されます。
REAL浮動小数点型。8byteのIEEE浮動小数点数として保存されます。
TEXT文字列型。テキストを保存します。 UTF-8, UTF-16BE, UTF-16LEのうちデータベースエンコーディングとして使用される文字コードで保存されます
BLOBBLOB型。バイナリなどを保存します。

テーブルの型として指定できる物は以下です。

  • TEXT
  • NUMERIC
  • INTEGER
  • REAL
  • BLOB

NUMERICはINTEGERと異なる動きをします。例えば"123"など数値のような文字列が渡された場合に、自動的に123の数値に変換されて保存されます。失敗した場合はそのまま文字列として保存されます。

また以前はNONEと呼ばれる型が存在していましたが、現在はBLOBに名称が変更されています。

Historical note: The “BLOB” type affinity used to be called “NONE”. But that term was easy to confuse with “no affinity” and so it was renamed.

データ型の別名

他のデータベースと互換性を保つため、各データ型には別名が設けられています。

データ型別名
INTEGERINT, INTEGER, TINYINT, SMALLINT, MEDIUMINT, BIGINT, UNSIGNED BIG INT, INT2, INT8
REALREAL, DOUBLE, DOUBLE PRECISION, FLOAT
TEXTCHARACTER(20), VARCHAR(255), VARYING CHARACTER(255), NCHAR(55), NATIVE CHARACTER(70), NVARCHAR(100), TEXT, CLOB
BLOBBLOB
NUMERICNUMERIC, DECIMAL(10,5), BOOLEAN, DATE, DATETIME

関数

様々な関数が用意されています。

コア関数

※後日記入

日付&時間関数

※後日記入

集計関数

※後日記入

PHPからSQLiteを利用する

インストール

最近のPHPを利用していれば、最初から利用できるようです。
念の為phpinfo()などで確認しておきます。

$ php -r 'phpinfo();' | grep sqlite                                                                               PDO drivers => mysql, pgsql, sqlite
pdo_sqlite
sqlite3
sqlite3.extension_dir => no value => no value

利用例

ここではPDOを利用します。というわけでMySQLなど他のデータベースと同じように操作することが可能です。

PHPからデータベースへ接続

// 接続
$dbh = connect('foo.sqlite3');

/**
 * SQLiteへ接続する
 */
function connect($file){
  $dsn = sprintf('sqlite:%s', $file);

  try{
    $dbh = new PDO($dsn);

    //オプション(必要に応じて)
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);       // エラー時には例外
    $dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);  // fetchした際に連想配列で返す

    return($dbh);
  }
  catch( Exception $e ){
    echo $e->getMessage() . "\n";
    exit(1);
  }
}

PHPからSELECT文を実行

PHPからデータベースへ接続で使用したconnect()関数を利用しています。

// 接続
$dbh = connect('foo.sqlite3');

// SELECT文を実行
$buff = selectAll($dbh, 'SELECT * FROM bar');
var_dump($buff);

/**
 * SELECT文を実行する
 */
function selectAll($dbh, $sql){
  try{
    $sth = $dbh->prepare($sql);
    $sth->execute();
    return($sth->fetchAll());    //実行結果がメモリ上に上がるので注意
                                  //データが多い場合はfetch()で1行ずつ取得する
  }
  catch( Exception $e ){
    echo $e->getMessage() . "\n";
    exit(1);
  }
}

PHPからDDL/INSERT/UPDATE/DELETE文を実行

SQLiteもトランザクションが利用できます。
ここでもPHPからデータベースへ接続で使用したconnect()関数を利用しています。

// 接続
$dbh = connect('foo.sqlite3');

// INSERT文を実行
$buff = execute($dbh, 'INSERT INTO bar VALUES(12345)');
var_dump($buff);

/**
 * DDL/INSERT/UPDATE/DELETE文を実行
 */
function execute($dbh, $sql){
  try{
    $dbh->beginTransaction();   //トランザクションを張る
    $sth = $dbh->prepare($sql);
    $sth->execute();
    $dbh->commit();

    return(true);
  }
  catch( Exception $e ){
    $dbh->rollBack();
    echo $e->getMessage() . "\n";
    exit(1);
  }
}

プレースホルダを利用する

各種SQL実行時にプレースホルダの利用も可能です。

$sth = $dbh->prepare('SELECT * FROM bar WHERE id=? AND status=?');
$sth->execute([123, 'OK']);
$sth = $dbh->prepare('SELECT * FROM bar WHERE id=:id AND status=:status');
$sth->bindValue(':id',     123,  PDO::PARAM_INT);
$sth->bindValue(':status', 'OK', PDO::PARAM_STR);
$sth->execute();

運用

不要な領域を削除して最適化したい

※後日記入

バックアップ/リストア

※後日記入

SQLiteで出来ないこと

公式サイトにommitされた機能一覧が記載されています。
以下抜粋です。

機能概要
RIGHT and FULL OUTER JOINLEFT OUTER JOIN is implemented, but not RIGHT OUTER JOIN or FULL OUTER JOIN.
Complete ALTER TABLE supportOnly the RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted.
Complete trigger supportFOR EACH ROW triggers are supported but not FOR EACH STATEMENT triggers.
Writing to VIEWsVIEWs in SQLite are read-only. You may not execute a DELETE, INSERT, or UPDATE statement on a view. But you can create a trigger that fires on an attempt to DELETE, INSERT, or UPDATE a view and do what you need in the body of the trigger.
GRANT and REVOKESince SQLite reads and writes an ordinary disk file, the only access permissions that can be applied are the normal file access permissions of the underlying operating system. The GRANT and REVOKE commands commonly found on client/server RDBMSes are not implemented because they would be meaningless for an embedded database engine.

参考ページ



- SponsoredLink -

Donate

投げ銭お待ちしております!

BTC3A9nH1j7qQdKrSTrmnEdweo6zPqpHBmkxC
ETH0x1aE0541198D1F9f2908a25C35032A473e74D3731
XPXaQ9zv65F9ovfoMBrFGiPRG47aSHFhy8SX
MONAMTKgzSiS5BDueZkRCHySih24TGFwHThaDQ (MonaCoin)
ZNYZhnpf4RFYVQTAQiyoJg9dGoeC4bgT3BoSy (BitZeny)

コメントはお気軽に(゚∀゚)

This site uses Akismet to reduce spam. Learn how your comment data is processed.