hinekure.net が http://hspdev-wiki.net/ から自動クローリングした結果を表示しています。画像やリソースなどのリンクが切れています。予めご了承ください。
SQLele資料集/SQLについて - HSP開発wiki
トップ    編集凍結 差分バックアップ添付複製名前変更リロード   新規一覧単語検索最終更新   最終更新のRSS

SQLele資料集


そもそもSQLって何?

素人にとってはそんな所感だったので少し調べてみました。
よってこのページは独断と偏見とにわか知識で構成されてますので識者の方訂正お願いします。 [ojigi]

SQLはデータベース処理の為の言語仕様。
SQLiteはそれを実行する為のライブラリ。
そしてSQLeleはSQLiteをHSPから扱う為のモジュール…と。

で、SQLiteはローカルファイルでデータベースを構築するのが特徴なようです。
CSVファイルを扱うような感覚でデータベースを扱える、といった所でしょうか?

シミュレーションやRPGといった大量の定型データを扱うプログラムなら、データベースで効率的に管理できるかも知れません。
ツクール系のデータベースのようなモノを自身で好きなように組み込める、と言えば解る人には解り易そうです。


データベースの構造

  • ルイーダの酒場
    IDなまえ職業性別LvHPMP
    1ライアン戦士152400
    2アリーナ101600
    3クリフト神官812040
    4ブライ魔法使い98065
    5トルネコ商人2422030
    6マーニャ踊り子1311570
    7ミネア占い師1211060

ルイーダの酒場というテーブル(表)に、IDなまえ等のカラム(縦・列)があり、それぞれのレコード(横・行)に各データ(セル・マス目)が並んでいる例です。
1つのデータベース内にこのようなテーブルをいくつも構築できます。
データの構造自体は比較的とっつきやすそうに見えます。

HSPの配列変数とは次のような点で異なると思います。

  • 各データのデータ型&サイズは基本的に制限がありません。
    同カラム内で異なるデータ型も混在可能です。
  • 連想配列のように各カラムの名称は自由に設定可能。
  • レコードの追加・削除は容易です。
    挿入には少し手間が掛かりますがわざわざ挿入する必要性は薄いです。
  • データ内容から取得するレコード・データを選別可能。
    またデータ内容でソートした上での取得も簡単です。
  • テーブルの一部を切り出したり、複数のテーブルを合成して参照したりと色々できるようです。
  • データベースの操作には専用の構文を用いる必要があります。*1
  • データベースは別ファイルなので外部からも編集が可能です。*2
    作成したデータベースは次回作でも容易に使い回せますw

以降、SQLeleでの利用を前提に基本的な使い方を少し書いてきます。


データベースの操作

sql_q "SQL文"

SQLeleでデータベース内を操作する為の命令はこの1つだけです。
他はデータベースファイルを開いたり、データ型の変換やらです。

"SQL文"の部分にSQLiteが理解できる命令を文字列で記述する事で、データベース内の操作の全てを行う事になります。
つまりSQL文を知らなければ何もできません

この辺がちょっと敷居高くて面倒そうです…
実際使うならデータ内容に合わせてラッパー関数組んだ方が効率的かも知れません。

SQLite用のGUIツール(TkSQLite等)もありますので、静的なデータベースの編集はソチラを利用するのが楽です。


SQLの基本文法

  • キーワード(命令語など)の大文字小文字は区別されません。
    カラム名なども半角であれば大小区別されませんが、全角アルファベットでは区別されました。
    データ内容は大文字小文字きっちり区別されるようです。
  • '文字列'等は「'」シングルクォートで括ります。
    テーブル名やカラム名も原則的には「'」で括ります。*3
    文字列内で「'」を使うには「''」と2つ書きます。'I''m a Cat.'など
  • キーワードや指定子などは半角スペース・Tab・改行を挟んで区切ります。
    一箇所に何文字入れても1つの区切りとして扱われます。
    SQLeleでわざわざTabや改行使う意味は無さそうです。
  • SQL文の終端には「;」セミコロンを付けて文を区切ります。
    但しSQL文が1つのみなら省略しても問題ありません。
  • SQLの文法でなくSQLeleの仕様ですが、
    HSPの変数の内容をSQL文に用いる場合はバインド関数(prm_***系)使うべし。とあります。
+  SQLの演算子

データの取得

sql_q "SELECT 取得項目 FROM テーブル名 [WHERE 取得条件] [ORDER BY カラム名];"

[ ]内は省略可能な項目ですよ、と示してるだけで実際[ ]は要りません。

データベース内のデータを取得する為のSQL文はこれ1つです。
編集はGUIツールで行う方が楽ですし、静的な利用であればSELECT文さえ習得すれば可能という事です。

基本形だとこれくらいですが、実際はもっと項目ありますし入れ子のような事もできるので果て無しです。

SQLeleでは取得データはtmparrか、sql_q命令に渡した変数に文字列型配列として格納されます。
配列からのデータの取り出し方はSQLeleのヘルプやサンプルを参照して下さい。

  • 取得項目
    基本的には、取得したいデータのカラム名を指定します。
    「,」区切りで複数のカラム名を列挙すれば、列挙されたカラムのデータを取得します。
    また「*」アスタリスクのみは対象テーブルの全てのカラムを指します。
    +  少しだけ応用
  • FROM テーブル名
    基本的には目的のデータが収められたテーブル名を指定するだけです。
    応用的には複数のテーブルを結合させたり、SELECT文で取得した結果の中から更に選別したりと色々できるようです。
    ちなみに、テーブルからデータを取得するのでなければテーブル指定は要りません。
  • WHERE 取得条件
    条件式を用いてその条件を満たすレコードのみ取得する為の指定です。
    式の中にカラム名を指定すると、各データの内容から判定します。
    例えば「WHERE 性別 = '男'」なら男キャラのみ、「WHERE Lv > 10」ならLv11以上のキャラのレコードだけを取得できます。
    省略時は無条件という事でテーブル内の全レコードからデータを取得します。
  • ORDER BY カラム名
    指定されたカラム内のデータを基に、取得するレコードの順番を変更(ソート)します。
    あくまで取得レコードの順番を変更するだけで元のテーブルには影響ありません。
    省略時はテーブルにレコードが追加された順*5で取得します。
    通常は昇順ソートですが「ORDER BY カラム名 DESC」とする事で降順ソート*6になります。
    複数カラムを指定した場合は後に指定されたもの程内部ソートの判定基準となります。
+  使用例

テーブルの操作

sql_q "CREATE TABLE [IF NOT EXISTS] テーブル名 (カラム指定);"

sql_q "DROP TABLE [IF EXISTS] テーブル名;"

CREATE TABLEでデータベース内にテーブルを作成します。
DROP TABLEでデータベースからテーブルを削除します。

  • IF NOT EXISTS / IF EXISTS
    1つのデータベース内に同名のテーブルを作成する事はできません。
    どのテーブルを指定しているのか区別できなくなりますから…
    同様に存在しないテーブルを削除する事もできません。
    SQLeleにダメ出しされた上にプログラム終了します。

    事前にデータベース内を調べてチェックする事もできなくは無いですが、面倒なのでおまじないを使います。
    CREATE TABLE IF NOT EXISTS テーブル名 〜」で同名テーブルが存在していれば作成しません。
    DROP TABLE IF EXISTS テーブル名」でテーブルが存在しなければ削除しません。
  • テーブル名
    普通にテーブル名を指定するだけですので以下は余談です。
    「データベース名.テーブル名」とする事でデータベースを指定してテーブルを作成できます。
    但し、データベース名≠データベースファイル名です。
    現在のデータベースは「main」、作業用の一時データベースは「temp」というデータベース名で固定です。
    ATTACH DATABASE文による追加データベース*8には任意のデータベース名を指定できます。
  • (カラム指定)
    「カラム名 データ型 カラム制約」の形式でテーブルの初期カラム(項目)を指定します。
    複数カラムを指定する時は「,」区切りで必要な分だけ列挙します。
    後からカラム追加は簡単ですが、既存カラムの変更・削除は少し手間が掛かります。
    同テーブル内でのカラム名の重複は当然不可です。
    データ型・カラム制約は必要な分だけ半角スペース区切りで列挙、不用なら省略可能です。
    データ型UNIQUE制約については別ページがあるので他を少しだけ。
    • INTEGER PRIMARY KEY
      INETEGERはデータ型指定なのですが基本的にワンセットです。
      既定値としてテーブル内で重複しない整数を各レコードに割り振ってくれます。
      特に操作しなければ、レコードが追加された順に1から順の番号となります。
      ソート指定しない時の取得レコードの並びはこの番号順になります。
      また、この制約のあるカラムに既存の番号や整数値以外のデータは格納できません。
      各レコードを確実に判別する材料となるので有った方が都合が良さそうです。*9
    • DEFAULT 既定値
      データ未設定時の初期値を指定できます。
      省略時はNull値*10が既定値みたいです。

ビュー

sql_q "CREATE VIEW [IF NOT EXISTS] ビュー名 AS SELECT 〜;"

sql_q "DROP VIEW [IF EXISTS] ビュー名;"

テーブル関連なのでついでに。
ビューとは参照専用の仮想テーブルのようなものです。
SELECT文で取得した内容を保持して置いたり、その中からさらに絞り込むといった用途に使えるかも知れません。

CREATE TABLE テーブル名 AS SELECT 〜;」とすれば同じ手法で新規にテーブルを作る事もできますが、ビューにはテーブルとは異なる特徴があります。

  • 基本的にビューから各データを操作することはできません。
  • 基のデータに変更があった場合、ビュー内のデータにもその変更は反映されます。*11

作成後はSELECT文でテーブル名としてビュー名を指定すれば、テーブル同様に参照可能です。
既存のテーブルから特定のレコードやデータを何度も参照する場合に使えそうな気がします。

+  使用例

データの操作

追加・削除はレコード単位、変更は主にデータ単位で行います。

レコードの追加 (INSERT)

sql_q "INSERT INTO テーブル名 [(カラム指定)] VALUES (データ指定);"

新しいレコードは、基本的にテーブルの末尾(一番下)に追加されます。

  • テーブル名
    既存のテーブル名を指定します。
    自動でテーブル作成してくれる程面倒見良く無いです。
  • (カラム指定)
    レコード追加と同時にデータを設定するカラムを「,」区切りで列挙します。
    ここで指定されなかったカラムのデータは既定値の状態でレコードが追加されます。
    また存在しないカラム名を指定すると当然エラーです。
    ( )ごと省略するとテーブル内の全てのカラムを指定した事になります。
    INTEGER PRIMARY KEY制約のカラムは除外しておくのが無難です。
  • VALUES (データ指定)
    カラム指定での個数分、指定順にデータを「,」区切りで列挙します。
    カラム指定の個数とデータの個数が異なればエラーとなります。
    順番を間違ってもデータがあべこべになるだけの事です。
    カラム指定省略時は全カラム分のデータを列挙する必要があります。
  • SELECT
    上記の「VALUES (データ指定)」の代りにSELECT文をそのまま突っ込む事もできます。
    カラム数と取得データ数は一致する必要が有る、カラム指定順に取得データが格納される点は同様ですが、SELECT文では取得した分のレコードが一気に追加されます。
    同形態のテーブル間でのやり取りには便利かも知れません。

データの変更 (UPDATE)

sql_q "UPDATE テーブル名 SET カラム名=データ [WHERE 変更条件];"

変更しようとするデータ値がカラム制約に反する場合*12はエラーとなります。
対してデータ型の違いは黙認してくれます。

  • テーブル名
    そのまんまです。
  • カラム名=データ
    どのカラムの値をどんな内容に変更するか、です。
    データとして指定できるのは単純な数値や文字列のみで無く、式やSELECT文も指定可能です。
    式内のカラム名は、それぞれのレコード内の現在のデータ値となります。
    「Lv=Lv+1」とすればLvが1増え、「HP=CAST(HP*0.95 AS INTEGER)」とすればHPが約95%になります。
    また「カラム1=データ1, カラム2=データ2,...」のように「,」区切りで列挙する事で一度に複数のデータを変更可能です。
  • WHERE 変更条件
    テーブル内のどのレコードの値を変更するのか、という条件の指定です。
    条件に該当するレコードが無ければ一切変更されません。
    省略時はテーブル内の全てのレコードのデータが同様に変更されます。

レコードの削除 (DELETE)

sql_q "DELETE FROM テーブル名 [WHERE 削除条件];"

テーブルから該当レコードを削除します。

  • テーブル名
  • WHERE 削除条件
    テーブル内のどのレコードを削除するか、という条件の指定です。
    条件に該当するレコードが無ければ削除は行われません。
    省略時はテーブル内の全レコードを削除されます。
+  レコード削除後のINTEGER PRIMARY KEY


+  使用例

トランザクション

まずトランザクションとは何かですが、HSPだとちょうど良い例えがあります。
Redraw0にしてから色々描画処理、全部描画し終わってからRedraw1で表示すれば描画が速い。
これと使い方が良く似てます。
処理が多いならBEGIN 〜 COMMIT間のトランザクション中にまとめて処理した方が速いという仕組みです。

+  一応トランザクションについて

sql_q "BEGIN [TRANSACTION];"

sql_q "COMMIT [TRANSACTION];"

sql_q "END [TRANSACTION];"

sql_q "ROLLBACK [TRANSACTION];"

TRANSACTION句は全て省略可能です。付けても別段意味はありません。

  • BEGIN
    マニュアル操作でのトランザクション開始宣言です。
    既にトランザクション中の場合エラーとなります。トランザクションの入れ子(ネスト)はできません。
    BEGIN」の代りに「SAVEPOINT hoge」などとすると、既にトランザクション中ならそのまま続行、トランザクション中でなければ開始してくれます。
    SAVEPOINTの本来の用途からは外れてますけど。
  • COMMIT / END
    処理内容を確定してトランザクションを終了します。
    ENDはCOMMITの別名なだけで全く同じものです。
    トランザクション中でなければエラー出されます。
  • ROLLBACK
    処理内容を撤回してトランザクションを終了します。
    文字通りの巻き戻しです、データベースの状態はトランザクション開始前の状態に戻ります。
    変更中に不味い事態が発覚したら使うのでしょうか?



・使用例

1
2
3
4
5
6
7
8
hantei=0 : sql_q "BEGIN;" ;トランザクション開始
Repeat
; 〜なんか大量のデータベース処理〜
; 状況判断して hanteiの値変更[1:正常終了 -1:非常事態宣言]
If hantei!=0 {Break}
Loop
If hantei=1 {sql_q "COMMIT;"} else {sql_q "ROLLBACK;"}
;こんな感じなんでしょうか?

VACUUM & REINDEX

sql_q "VACUUM;"

sql_q "REINDEX;"

動的にデータベースを扱うプログラムならセットでやっとくと累積操作による処理速度の低下を予防できそうです。
Windowsのデフラグのようなものです。

  • VACUUM
    通常テーブルやレコードが追加される際、データベースの使用領域はその使用に合わせて拡張されます。
    しかしテーブルやレコードが削除され空き領域となっても使用領域が縮小される事はありません。
    これは後にテーブルやレコードの追加があった場合、その空き領域を再利用する事で領域確保処理の軽減を計る為の仕様です。

    ですがデータベースの操作を続けるうちに断片化された空き領域が蓄積し、全体の処理速度の低下を招く事があります。
    その様な時にVACUUMで空き領域を一掃する訳です。

    但しVACUUM自体それなりに処理時間が掛かります*13ので、DELETEやDROP毎に行うのは本末転倒です。
    また、この場合の使用領域=ファイル領域ですので":memory:"などのメモリ上のデータベースには効果無いようです。
  • REINDEX
    インデックスを再構築する事で探索処理の効率化を計る機能らしいです。
    システムインデックスも再構築されるようですので、CREATE INDEXによるインデックスを使用してなくてもやっといて損は無いと思います。



・使用例

1
2
3
4
5
6
*Open_Database
Exist fName : If strsize<1 {Return 0}
If nowDB!="" {sql_q "VACUUM; REINDEX;" : sql_close}
;閉じる前に後片付けしてますが、実際はどっちがいいんでしょ?
sql_open fName : nowDB=fName
Return 1

SQLeleカテゴリーに寄生するヘルプブラウザ用ファイル:filepoorSQLite.hs



URL B I U SIZE Black Maroon Green Olive Navy Purple Teal Gray Silver Red Lime Yellow Blue Fuchsia Aqua White

*1 そうまでして使うのだからきっと高速なのでしょう
*2 と書くと内部データの漏洩や改竄を心配する方が居そうですが、そんなものやり方次第でどうとでもなりますし…
*3 文字列としか判断されないものは括らなくても認識してくれます
*4 カラム名はそれぞれのデータを指す変数のような役割です
*5 正確には内部のrowidの昇順
*6 昇順:小=>大 降順:大=>小
*7 実際のSQLeleではカラム名のレコードは配列の最後に取得されますが見難そうなので上に持ってってます
*8 SQLiteの仕様で複数のデータベースとの同時接続が可能ということです
*9 内部カラム(?)のrowidで代用できますが毎度明示指定も面倒ですし。
*10 値が存在しない事を示す値です。
例えば0や""(空文字)が死亡や戦闘不能状態なら、Null値はキャラデータ自体が存在しないようなモンです

*11 ビューの実態は作成時のSELECT文のマクロ名なのかも?
*12 UNIQUEへの重複データや、INTEGER PRIMARY KEYへの文字列など
*13 一時領域にコピー退避させてデータベースを一旦削除、新規作成&退避データから内容を復元てな事してるそうです
添付ファイル:
filepoorSQLite.hs
40件 [詳細]
トップ    編集凍結 差分バックアップ添付複製名前変更リロード   新規一覧単語検索最終更新   最終更新のRSS
Last-modified: 2012-08-14 (火) 15:56:07 (481d)