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 をアップしました。
たとえば、メディアプレイヤーのファイルリストや、アドレス帳やカレンダーのデータ、画像処理ソフトの Undo/Redo 管理、ゲームの中のキャラの台詞、セーブデータやハイスコア...
そういったアプリケーション内部のデータを管理するために、データ構造を設計して文字列操作命令やメモリ操作命令で操作したり、 ソートや抽出のためのルーチンを書いた経験を、HSP をある程度使ったことがあるほとんどのユーザーが持っているのではないでしょうか。
多くのアプリでは機能・性能ともそれで十分であると思いますが、もし複雑なデータを扱うアプリを効率的に開発したいならば、SQLite というフリーの SQL データベースエンジンを使ってみてはどうでしょう?
(SQLite 本家はこちらです)
http://www.sqlite.org/
「HSP で SQL」といえば、Access や SQL Server, MySQL といった DBMS サーバーでデータベースを用意して、ODBC データソースを設定して、そこへ hspdb で接続して...といった、敬遠されがちなアレな工程が思い浮かぶかもしれません。
しかし、SQLite を使えば、HSP アプリに DLL ひとつ追加するだけで、アプリケーション内で SQL データベースを利用できるようになります。
まずは楽に使えるものが一番です。このページでは、SQLite を HSP から Lite に扱うためのモジュール、略して SQLele (スクレレ) を紹介します。
SQLele 1.17 R2 (for SQLite3, HSP3)
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 について調べた情報のまとめや、SQLele での最適化 TIPS です。(雑多)
メモ
・SQLite のテーブルは同一フィールドでもレコードごとに異なる型のデータを格納でき、テーブル作成時には型指定をしなくても使用できる。
・複数レコードを INSERT する時は、トランザクション (BEGIN 〜 COMMIT) を使用する。(でないと、遅くてびっくりする)
・データベース内に作成されたテーブルやビュー、インデックスの一覧は、sqlite_master という特別なテーブルから参照する。(中身の例)
・一度大きくなったデータベースファイルは DELETE 等しただけでは小さくならないが、VACUUM コマンドで最適化できる。
・同時に複数のデータベースを使用するときは、メインデータベースを開いてから ATTACH で別のデータベースを追加で開く。
・SQL は方言に注意が必要。仕様書 (等) は SQL As Understood By SQLite, SQLite Wiki - Date And Time Functions とか
SQLite のデータベースファイルは、TkSQLite (http://reddog.s35.xrea.com/wiki/TkSQLite.html) というツールを使えば GUI 的に管理できます。デバッグ時のデータチェックや値の操作も楽にできます。
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 では、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 で SELECT 文を実行すると、結果がレコードセット変数に返ります。レコードセット変数は文字列型の 2 次元配列で、下の表のような構造になっています。
(6 カラム × 8 レコードの場合)
Array index | 0 | 1 | 2 | 3 | 4 | 5 |
---|---|---|---|---|---|---|
0 | Data | Data | Data | Data | Data | Data |
1 | Data | Data | Data | Data | Data | Data |
2 | Data | Data | Data | Data | Data | Data |
3 | Data | Data | Data | Data | Data | Data |
4 | Data | Data | Data | Data | Data | Data |
5 | Data | Data | Data | Data | Data | Data |
6 | Data | Data | Data | Data | Data | Data |
7 | Data | Data | Data | Data | Data | Data |
8 | Column name | Column name | Column name | Column name | Column name | Column 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 モジュールも、著作権フリーです。利用や配布、改変について制限はありません。
モジュールの不具合やドキュメントの誤り、こうしたほうがいいなどがありましたらお知らせください。