仕事でSQLiteを使う事があったので、使い方などを忘れないために、メモしておきたいと思います。
ちなみに、その仕事の環境はPHPだったのですが、簡単なツールも作ったので合わせて、公開しておきます。
SQLiteについて
SQLiteは、データベースの中でも非常に簡易に扱えるタイプのリレーショナルデータベースです。
MySQLやPostgreSQLなどとほぼ同じSQL構文が使えて、スマートフォンなどのアプリの中などでよく使われています。
手軽に構築できて、データベース管理もファイル単位で行えるため、バックアップなどがカンタンにできるのが特徴です。
SQLの学習をするときなどにも、いいかもしれませんね。
ちなみに、SQL自体の学習は本編では行わないので、SQLが分からない人は、SQLiteの環境構築をした後で、別に学習を進めてください。
ローカル環境はDockerで十分
githubにsqlite+phpのdocker-composeを用意しておきました。
https://github.com/yugeta/docker/tree/nginx+php8.1+sqlite3
$ git clone -b nginx+php8.1+sqlite3 https://github.com/yugeta/docker.git
うまくcloneデキない場合は、githubサイトの設定にてcloneしてください。
cloneをした後は、次のコマンドでdockerコンテナを立ち上げます。
※"nginx+php8.1+sqlite3"というブランチを使ってください。
$ docker-comppose up -d
docker-desktopなどを使って。ターミナルでコマンド作業ができます。
コマンドラインで使うSQLite
SQLiteのデータベースは、ファイル単位に作成します。
コマンドラインでサクっと作る手順です。
テーブル操作をした後は、phpでデータの登録や削除をやる手順で行います。
SQLiteにアクセス（データベース作成）
$ sqlite3 %データベースファイル
データベースファイルは、どこでも好きな場所に作ることができます。
データベースファイルを作ったあとで、webサイトで使いやすい場所に移動しても問題ありません。
既にデータベースファイルがある場合はそのパスを入れると、データベースに入って作業ができます。
存在しないパスを書いた場合は、新しくデータベースファイルが作成されます。
拡張子は、.sqlでも、.sqliteでも、.dbでも、何を使っても問題ありません。
テーブル作成
データベースを作ったら、次はテーブルを作ります。
サンプルでserviceという、サービス名を登録するテーブルを作成したいと思います。
CREATE TABLE service (
id INTEGER PRIMARY KEY,
name TEXT,
create_at TIMESTAMP DEFAULT (datetime(CURRENT_TIMESTAMP, 'localtime')),
update_at TIMESTAMP DEFAULT (datetime(CURRENT_TIMESTAMP, 'localtime'))
);
カラムの型は、次のモノしか使えないようです。
- TEXT
- NUMERIC
- INTEGER
- REAL
- NONE
- TIMESTAMP
個人的には、TEXT , INTEGER , TIMESTAMPしか使ってません。
テーブルの削除
DROP TABLE service;
確認などされずに、サクっと消されてしまうので、必要な場合は、ファイルバックアップなどを行ってから消しましょう。
テーブル一覧の表示
SELECT * FROM sqlite_master WHERE type="table";
$lt; table|service|service|2|CREATE TABLE service (
$lt; id INTEGER PRIMARY KEY,
$lt; name TEXT,
$lt; create_at TIMESTAMP DEFAULT (datetime(CURRENT_TIMESTAMP, 'localtime')),
$lt; update_at TIMESTAMP DEFAULT (datetime(CURRENT_TIMESTAMP, 'localtime'))
$lt; )
SQLite管理ツール
phpMyAdminみたいなツールが欲しかったので、簡単なモノを作ってみました。
githubにアップしておいたので、お好きにcloneしてください。
データベース管理ツール（自作）
https://github.com/yugeta/sqlite_admin
dataフォルダの中に、先程作成したデータベースファイルを入れておくと、左上の"SQLite files"の箇所に一覧で表示されます。
それぞれのファイルを選択（クリック）すると、その右の"Tables"に、データベースに入っているテーブル一覧が表示され、
テーブルを選択すると、データ内容が表示されます。
"Columns"というタブをクリックすると、そのテーブルのカラム構成がリストで表示されます。
どうですか？便利でしょ？
PHPでSQL操作
最後に、PHPでの基本操作だけサンプルで書いておきます。
基本的なSQL操作なので、他のSQLを使っている人は、馴染み深いと思いますが、SQLiteを操作するスニペットになります。
INSERT : レコードの新規追加
$db = new \SQLite3('%データベースファイル');
$select = 'INSERT INTO %テーブル名 (%カラム) VALUES (%値)';
$res = $db->query($select);
$db->close();
%データベースファイル : データベースファイルのパス
%テーブル名 : 登録してあるテーブル名
%カラム : テーブルに登録してあるカラム名(複数ある場合は,カンマ区切り）
%値 : カラムに登録する値を同じ順番で登録できます(複数ある場合は,カンマ区切り)。
$resには、正常に登録できた場合はtrue、登録に失敗した場合は、falseが返ります。
UPDATE : レコードのデータ更新
$db = new \SQLite3('%データベースファイル');
$select = 'UPDATE %テーブル名';
$select .= ' SET %カラム1="%値1" , %カラム2="%値2"';
$select .= ' WHERE %カラム="%値" AND id="1"';
$res = $db->exec($select);
$db->close();
SETは、カラム=値の書き方で、複数ある場合は、,カンマ区切りで記述します。
WHEREは、複数ある場合は、AND区切りで記述します。
$resには、正常に登録できた場合はtrue、登録に失敗した場合は、falseが返ります。
DELETE : レコードの 削除
$db = new \SQLite3('%データベースファイル');
$select = 'DELETE FROM %テーブル名';
$select .= ' WHERE %カラム1="%値1" AND %カラム2="%値2"';
$res = $db->exec($select);
$db->close();
DELETEは、WHEREでしっかりと絞り込んで確実に削除しましょう。
WHEREを書かない場合は、テーブル内の全てのデータ（レコード）が削除されます。
$resには、正常に登録できた場合はtrue、登録に失敗した場合は、falseが返ります。
SELECT : テーブルデータの取得
$db = new \SQLite3('%データベースファイル');
$select = 'SELECT * FROM %テーブル名';
$select .= ' WHERE %カラム1="%値1" AND %カラム2="%値2"';
$res = $db->query($select);
$datas = [];
while ($row = $res->fetchArray(SQLITE3_ASSOC)) {
array_push($datas , $row);
}
$db->close();
return $datas;
SELECTは、返ってきた値が$resで取得できますが、その後whileを使って、データを抽出して上げる必要があります。
あとがき
今回は仕事で使ったSQLiteの覚書きを書いておいて、以後の開発効率をアップさせようという計画です。
基本的な使い方だけ書いておいたんですが、他にもいろいろな機能があるので、今後記事をリライトしていく予定です。
