SQLele for SQLite3, HSP3

2023/09/05 SQLele 1.17 R2 をアップしました。
2018/06/07 SQLele 1.17 をアップしました。
2018/06/01 SQLele 1.16 をアップしました。
2010/02/22 SQLele 1.15 をアップしました。
2010/02/04 SQLele 1.14 R2 をアップしました。
2009/05/09 SQLele 1.14 をアップしました。
2009/05/09 レコードセット変数の資料 をアップしました。
2008/06/13 SQLele 1.13 をアップしました。

SQL によるデータ管理のすすめ

たとえば、メディアプレイヤーのファイルリストや、アドレス帳やカレンダーのデータ、画像処理ソフトの Undo/Redo 管理、ゲームの中のキャラの台詞、セーブデータやハイスコア...

そういったアプリケーション内部のデータを管理するために、データ構造を設計して文字列操作命令やメモリ操作命令で操作したり、 ソートや抽出のためのルーチンを書いた経験を、HSP をある程度使ったことがあるほとんどのユーザーが持っているのではないでしょうか。

多くのアプリでは機能・性能ともそれで十分であると思いますが、もし複雑なデータを扱うアプリを効率的に開発したいならば、SQLite というフリーの SQL データベースエンジンを使ってみてはどうでしょう?

(SQLite 本家はこちらです)

http://www.sqlite.org/

HSP で SQLite

「HSP で SQL」といえば、Access や SQL Server, MySQL といった DBMS サーバーでデータベースを用意して、ODBC データソースを設定して、そこへ hspdb で接続して...といった、敬遠されがちなアレな工程が思い浮かぶかもしれません。

しかし、SQLite を使えば、HSP アプリに DLL ひとつ追加するだけで、アプリケーション内で SQL データベースを利用できるようになります。

まずは楽に使えるものが一番です。このページでは、SQLite を HSP から Lite に扱うためのモジュール、略して SQLele (スクレレ) を紹介します。

ダウンロード

SQLele 1.17 R2 (for SQLite3, HSP3)

sqle117_r2.zip

SQLele モジュール (sqlele.hsp) と、hs ヘルプファイルと、DLL 版 SQLite (sqlite3.dll) とサンプルスクリプトをセットにしたアーカイブです。

リリースノート

1.17 R2 2023/09/05 (モジュール本体の変更はありません)
・添付 sqlite3.dll のバージョンを 3.43 にアップデート

1.17
・sqesc() 関数を高速化

1.16
・エラーメッセージを一部変更
・同梱 sqlite3.dll をアップデート SQLite version 3.23.1
・Web サイト移転に伴う表記変更
・ドキュメントサムネイル画像を削除

1.15
・複数のデータベースファイルを切り替えた場合の不具合の修正
・SELECT の結果レコードが 0 の場合もレコードセット変数を作成するよう変更

1.14 R2 (モジュール本体の変更はありません)
・ドキュメント、サンプルスクリプトの追加・変更
・ドキュメントサムネイルファイル追加 (HSP Document Library 用)
・添付 sqlite3.dll のバージョンを 3.6.22 にアップデート

1.14
・sql_collist() 関数の追加
・ビジー / ロック時の回復処理の改善
(複数のプロセスから 1 つの DB ファイルに書き込む場合の安定性が向上)
・添付 sqlite3.dll のバージョンを 3.6.14 にアップデート
・ヘルプ記述の改良

1.13
・エラーメッセージを UTF-8 から Shift_JIS に変換して表示するよう修正

1.12
・NULL 値の読み出しに関する不具合の修正
・添付 sqlite3.dll のバージョンを 3.5.9 にアップデート

1.11
・レコードセット変数のカラム名を UTF-8 から Shift_JIS に変換するよう修正
・ビジー時のリトライ処理の改善
・サンプル コメントミス修正
・添付 sqlite3.dll のバージョンを 3.4.2 から 3.5.3 にアップデート

1.10
・SQLite データベースへ文字列をわたすときに、Shift_JIS から UTF-8 に変換するように変更
・sqlele.hsp に多重インクルード対策を入れた
・sqesc 関数の不具合修正
・ヘルプミスの修正

1.0
・初公開版

つかいかた

SQLele の機能を使用するには、ユーザースクリプトのディレクトリに sqlite3.dll を置いて、sqlele.hsp をインクルードします。

詳細な使用方法は付属のヘルプ (hs ファイル) に記載していますので、ここではイメージ程度に紹介します。

主な命令・関数

よく使われる命令・関数には下記のものがあります。

sql_open "filename" ; データベースファイルをオープン

sql_q "SQL statement"[, arr] ; SQL 文を実行し、結果があれば受け取る

sql_v("Column name"[, arr]) ; レコードセット変数のデータを参照

sql_next [arr] ; 次のレコードデータを参照

sql_close ; データベースをクローズ

ミニサンプル

実行するごとに「ログテーブル」に現在時刻を追加記録して、テーブルの中身を表示するだけのスクリプト例です。

#include "sqlele.hsp"

sql_open "サンプル.db"

; 「ログテーブル」がない場合、テーブル作成
sql_q "CREATE TABLE IF NOT EXISTS [ログテーブル] (ID INTEGER PRIMARY KEY, [内容])"

; レコードを追加
sql_q "INSERT INTO [ログテーブル] ([内容]) VALUES (datetime('now','localtime'))"

; テーブル内のデータをすべて抽出
sql_q "SELECT * FROM [ログテーブル]"
count = stat

mes "記録件数 : " + count + " 件"
repeat count
	mes "ID=" + sql_v("ID") + ", 内容=" + sql_v("内容")
	sql_next
loop

sql_close

SQLele は SQL 文を書くことのみに注力できる楽な仕様なので、SQLite の柔軟な仕様とあいまって、SQL 入門者にも扱いやすいのではないかと思います。

ほか、BLOB データの入出力やバインド変数による高速化など、SQLite が持つ機能の多くを利用することができます。

制限事項 / それは仕様であります

・sql_q 命令で SELECT した場合、すべての結果が一旦レコードセット変数に格納されます。おかげでシンプルな仕様になっていますが、結果が膨大な場合は応答時間やメモリ消費がアレになります。(特に BLOB を使うときは要注意です)

・SQLele で保存したデータベースの文字列 (テーブル名、カラム名含む) のエンコードは、UTF-8 になります。SQLele では、これ以外の文字コードはサポートされていません。

・SQLele 自体の使い方は単純ですが、SQL の文法や SQLite 特有の使い方、チューニング手法などは、各自で調べなければなりません。

SQLite メモ

以下、SQLite について調べた情報のまとめや、SQLele での最適化 TIPS です。(雑多)

はじめての SQLite

メモ

・SQLite のテーブルは同一フィールドでもレコードごとに異なる型のデータを格納でき、テーブル作成時には型指定をしなくても使用できる。

・複数レコードを INSERT する時は、トランザクション (BEGIN 〜 COMMIT) を使用する。(でないと、遅くてびっくりする)

・データベース内に作成されたテーブルやビュー、インデックスの一覧は、sqlite_master という特別なテーブルから参照する。(中身の例)

・一度大きくなったデータベースファイルは DELETE 等しただけでは小さくならないが、VACUUM コマンドで最適化できる。

・同時に複数のデータベースを使用するときは、メインデータベースを開いてから ATTACH で別のデータベースを追加で開く。

・SQL は方言に注意が必要。仕様書 (等) は SQL As Understood By SQLite, SQLite Wiki - Date And Time Functions とか

TkSQLite を使った管理

SQLite のデータベースファイルは、TkSQLite (http://reddog.s35.xrea.com/wiki/TkSQLite.html) というツールを使えば GUI 的に管理できます。デバッグ時のデータチェックや値の操作も楽にできます。

BLOB って何?

BLOB とは、Binary Large OBject の略で、文字列や数値だけでなく、画像などのバイナリデータを直接データベースに格納するためのデータ型です。SQLele での BLOB 型データの読み書きは、次のようになります。

// 変数バッファを BLOB として書き込み
size = ... // バイナリサイズ
var = ...  // バイナリデータ
sql_q "INSERT INTO ... (blob1) VALUES (" + prm_blob(var, size) + ")"
// BLOB データを変数バッファへ読み出し
sql_q "SELECT blob1 FROM ..."
size = sql_blobsize("blob1")
sdim var, size
sql_blobcopy var, "blob1"

prm_blob() 関数は、SQL 文の実行時に変数バッファの内容を BLOB パラメータとしてバインドすることを指示する関数です。

この BLOB データは容量無制限に (メモリやディスクの許す限り) (ただし 1 データあたりは HSP で扱える変数サイズだけ) 使用することができます。 この機能を利用して、画像データの Undo/Redo バッファとしてデータベースを使用したり、データベースに多数のファイルをまとめてアーカイブにしたりできます。

バインドって何?

SQLite における bind とは、SQL 文にリテラルとしてパラメータデータを書かずに変数として記述し、SQL 文をパース (解析)・コンパイルしてから改めて変数パラメータを代入する方法です。 具体的には、"INSERT INTO ... VALUES ( ?1 )" のような形の SQL 文をパースしてから "?1" の部分に当てはまるパラメータをセットして、それから SQL 文を実行するということになります。

バインド変数を使うことによって、SQL 文に長〜いリテラルを直接書くよりもパースが速くなり、また一度パースした SQL ステートメントをパラメータを変えて再利用できるようになります。

SQLele におけるバインドの利用

SQLele では、SQL パラメータの記述に prm_i() / prm_f() / prm_text() / prm_blob() を使用すれば、無意識にバインドの恩恵を受けることができます。

これらの関数は、表面上は文字列 " ?1 "〜" ?999 " を返す関数で、内部では sql_q 実行時にバインドする値、もしくはバインドするデータのポインタとサイズを記録しています。 バインド情報は、sql_q 命令の実行とともに使われて、リセットされます。

// 例 1 バインドを使用
repeat 100000
	sql_q "INSERT INTO ... VALUES (" + prm_i(cnt) + ")"
loop

// 例 2 リテラルを使用
repeat 100000
	sql_q "INSERT INTO ... VALUES (" + cnt + ")"
loop

上の例 1 のように、バインドを使って同じ内容の SQL 文を繰り返し実行しようとした場合、SQLele はパース済みの SQL ステートメントを再利用します。

パラメータを毎回リテラルで渡した場合と prm_*() を使った場合とで速度を比較したところ、整数値の単純な挿入では 1.8 倍、1KB の文字列の場合は 2.3 倍ほど後者のほうが高速でした。(ループごとに変化する値での実測。リテラル文字列の計測は sqesc() のオーバーヘッドを含む)

(忘れるためのメモ) SQLele レコードセット変数の形式

SQLele で SELECT 文を実行すると、結果がレコードセット変数に返ります。レコードセット変数は文字列型の 2 次元配列で、下の表のような構造になっています。

(6 カラム × 8 レコードの場合)

Array index012345
0DataDataDataDataDataData
1DataDataDataDataDataData
2DataDataDataDataDataData
3DataDataDataDataDataData
4DataDataDataDataDataData
5DataDataDataDataDataData
6DataDataDataDataDataData
7DataDataDataDataDataData
8Column nameColumn nameColumn nameColumn nameColumn nameColumn name

'Data' の要素には、レコードのデータが含まれます。この要素は、元のデータの型にかかわらず、文字列として表現されています。 (元のデータが FLOAT 型の 6.666 ならば、その要素の値は "6.666" という文字列として表現されます。)

レコードセット変数のカラム名は、配列の最も下位の行 'Column name' に記録されています。

'Data' 要素のデータには、文字列バッファの終端 NULL 以降の領域に、元データの型情報などが含まれています。 型が INTEGER, FLOAT, TEXT, NULL のデータの場合、変数バッファには次のように情報が格納されています。

[文字列 (可変長)] [NULL (4byte)] [型 (4byte)] [レコードカウンタ (4byte)] [NULL (4byte)]

型が BLOB のデータの場合、変数バッファには次のように情報が格納されています。

[NULL (4byte)] [型 (4byte)] [レコードカウンタ (4byte)] [BLOB サイズ (4byte)] [BLOB データ (可変長)]

型情報には、各要素の元のデータ型が記録されており、SQLITE_INTEGER (= 1), SQLITE_FLOAT (= 2), SQLITE_TEXT (3), SQLITE_BLOB (4), SQLITE_NULL (5) のいずれかの値をとります。sql_type() は、この値を取得する関数です。

レコードカウンタは、レコードセット変数の中の現在のレコードの位置を示す値です。レコードセット配列の要素 (0, 0) にのみ有効な値が記録されています。 このカウンタは、sql_next, sql_move 命令で設定され、sql_v() マクロなどで使用されます。

BLOB データの場合、BLOB サイズ情報 (4byte) 以降の領域に、BLOB のバイナリデータ本体が格納されます。 これらのデータは、sql_blobsize(), sql_blobcopy 命令で取得されます。

sql_v("column") は、この形式のレコードセット変数の 1 つの Data 要素を示すマクロとなっています。 このマクロを介さずにレコードセット変数に直接アクセスすることによって、スクリプトを簡潔化したり、実行を高速化できる場合があります。

SQLele 1.15 から、レコード数が 0 の場合もレコードセット変数が作成されるように変更されました。

著作権フリー。

SQLite はパブリックドメインとして公開されていて、自由に使うことができます。

SQLele モジュールも、著作権フリーです。利用や配布、改変について制限はありません。

モジュールの不具合やドキュメントの誤り、こうしたほうがいいなどがありましたらお知らせください。