第15回　ローカルデータベースの利用

データベースの作成

AIRランタイムにはSQLiteというオープンソースのデータベース機能が組み込まれており、標準的なSQLを使ってローカルデータを管理できます。ファイルAPIでもローカルファイルの読み書きは可能ですが、例えばアドレス帳のように大量のデータを保存してランダムに検索したい場合などはデータベースの方が適しています。

データベースは単一のローカルファイルを使用します。このファイルには可搬性があり、好きな場所に保存できます。実際に作成してみましょう。

<?xml version="1.0" encoding="utf-8"?>
<mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml" layout="absolute" applicationComplete="openDB()">
  <mx:Script>
    <![CDATA[
      private function openDB():void {
        var db:File = File.desktopDirectory.resolvePath("sample.db");
        var conn:SQLConnection = new SQLConnection();
        conn.addEventListener(SQLEvent.OPEN, openHandler);
        conn.addEventListener(SQLErrorEvent.ERROR, errorHandler);
        conn.openAsync(db);
      }
      private function openHandler(e:SQLEvent):void {
        trace("DBをオープンしました");
      }
      private function errorHandler(e:SQLErrorEvent):void {
        trace(e.error.message);
      }
    ]]>
  </mx:Script>
</mx:WindowedApplication>

上記のコードを実行すると、デスクトップにsample.dbというファイルが作成されます。これがデータベースファイルです。ここで行っているのはデータベースをオープンする処理です。データベースを使用するときは、毎回このようにSQLConnectionオブジェクトを作成し、openAsync()メソッドでオープンする必要があります。ファイルの指定にはFileオブジェクトを使います。指定のファイルが存在しないときは自動的に作成されます。ファイル名は拡張子も含めて任意で構いません。ファイルを指定しない場合はメモリ内にデータベースが作成されます。データベースを使い終わったらclose()メソッドでクローズします。

なお、openAsync()の代わりにopen()を使う方法もあります。openAsync()の場合はその後の処理が非同期となり、open()の場合は同期処理となります。同期処理はコードの流れを追いやすい等のメリットがありますが、扱うデータ量によっては他の処理を止めてしまうため、基本的には非同期処理を使ったほうが無難でしょう。

SQLの実行

データベースに対する操作はSQLで行います。SQLを実行するにはSQLStatementオブジェクトを使います。次のコードはテーブルを作成するサンプルです。データベースsample.dbをオープンして、2つのカラムfoodとpriceを持つテーブルmenuを作成しています。

<?xml version="1.0" encoding="utf-8"?>
<mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml" layout="absolute" applicationComplete="openDB()">
  <mx:Script>
    <![CDATA[
      private var _conn:SQLConnection;
      private function openDB():void {
        var db:File = File.desktopDirectory.resolvePath("sample.db");
        _conn = new SQLConnection();
        _conn.addEventListener(SQLEvent.OPEN, openHandler);
        _conn.addEventListener(SQLErrorEvent.ERROR, errorHandler);
        _conn.openAsync(db);
      }
      private function openHandler(e:SQLEvent):void {
        createTable();
      }
      private function errorHandler(e:SQLErrorEvent):void {
        trace(e.error.message);
      }
      private function createTable():void {
        var sql:SQLStatement = new SQLStatement();
        sql.sqlConnection = _conn;
        sql.text = 
        "CREATE TABLE IF NOT EXISTS menu (" + 
        "  food TEXT," + 
        "  price NUMERIC" + 
        ")";
        sql.addEventListener(SQLEvent.RESULT, resultHandler); 
        sql.addEventListener(SQLErrorEvent.ERROR, errorHandler); 
        sql.execute();
      }
      private function resultHandler(e:SQLEvent):void {
        trace("テーブル作成済み");
      }
    ]]>
  </mx:Script>
</mx:WindowedApplication>

上のサンプルではcreateTable()メソッド内でSQLを実行しています。SQLStatementオブジェクトのsqlConnectionプロパティには実行対象のデータベースを指定します。値はSQLConnectionオブジェクトです。その上でtextプロパティにSQL文を設定し、execute()メソッドを呼び出すことでSQLが実行されます。正しく実行された場合はSQLEvent.RESULTイベント、エラーが発生した場合はSQLErrorEvent.ERRORイベントが送出されます。

レコードの操作結果

レコードの挿入や抽出等の操作を行った場合も、処理が正しく完了したときはSQLEvent.RESULTイベントを受け取ります。このタイミングでSQLStatementオブジェクトのgetResult()メソッドを呼び出すと処理結果を調べられます。次のコードはレコードを1件追加する例です。

private var _conn:SQLConnection;
private var _sql:SQLStatement;
/* データベースへの接続処理等は省略 */
private function insertRecords():void {
        _sql = new SQLStatement();
        _sql.sqlConnection = _conn;
        _sql.text = "INSERT INTO menu (food, price) VALUES ('牛丼', 280)";
        _sql.addEventListener(SQLEvent.RESULT, insertResultHandler);
        _sql.execute();
}
private function insertResultHandler(e:SQLEvent):void {
        var result:SQLResult = _sql.getResult();
        trace(result.lastInsertRowID);
}

コードから分かるように、getResult()メソッドの戻り値はSQLResultオブジェクトです。SQLResultオブジェクトにはいくつかのプロパティがあります。lastInsertRowIDプロパティは、最後に追加したレコードのIDを返します。テーブルに整数値型のプライマリキーが1つ定義されていればその値が使われますが、それ以外の場合はデータベースが自動で生成するユニークIDが使われます。

次のコードはpriceカラムが100以下のレコードを抽出する例です。

private var _conn:SQLConnection;
private var _sql:SQLStatement;
/* データベースへの接続処理等は省略 */
private function selectRecords():void {
        _sql = new SQLStatement();
        _sql.sqlConnection = _conn;
        _sql.text = "SELECT * FROM menu WHERE price <= 100";
        _sql.addEventListener(SQLEvent.RESULT, selectResultHandler);  
        _sql.execute();
}
private function selectResultHandler(e:SQLEvent):void {
        var result:SQLResult = _sql.getResult();
        var records:Array = result.data;
        if (records != null) {
                trace(records.length + "件ヒットしました");
                for each (var item:* in records) {
                        trace(item.food, item.price);
                }
        } else {
                trace("ヒットなし");
        }
}

今度はSQLResultオブジェクトのdataプロパティを取得しています。これは抽出したレコードの配列です。各要素はカラム名をプロパティとするObjectオブジェクトです。レコードが1件も一致しなかった場合、dataプロパティはnullになります。

抽出結果を分割して受け取る

抽出条件に一致するレコードが多数ある場合、それらを一度に受け取ると処理の負荷が高くなります。そこで、必要な件数を部分的に受け取る方法が用意されています。前述のコードを変更して、10件ずつ取得する例を示します。

private var _conn:SQLConnection;
private var _sql:SQLStatement;
/* データベースへの接続処理等は省略 */
private function selectRecords():void {
  _sql = new SQLStatement();
  _sql.sqlConnection = _conn;
  _sql.text = "SELECT * FROM menu WHERE price <= 100";
  _sql.addEventListener(SQLEvent.RESULT, selectResultHandler);  
  _sql.execute(10);
}
private function selectResultHandler(e:SQLEvent):void {
  var result:SQLResult = _sql.getResult();
  var records:Array = result.data;
  if (records != null) {
    trace(records.length + "件ヒットしました");
    for each (var item:* in records) {
      trace(item.food, item.price);
    }
    if (!result.complete) {
      _sql.next(10);
    }
  } else {
    trace("ヒットなし");
  }
}

上のコードのようにexecute()メソッドのパラメータに件数を指定することで、最初に取得するレコード数を制限できます。その続きを取得するにはnext()メソッドを使います。next()もexecute()と同様にレコード数の指定ができます。next()の直前にSQLResultオブジェクトのcompleteプロパティを調べていますが、この値がtrueの場合にすべての該当レコードを取得し終わったことになります。

SQL文でパラメータを使う

データベース操作では何度も同じSQL文を使うことがあります。SQL文は実行時にコンパイルが必要なため、毎回SQLStatementオブジェクトを作るより、実行済みのオブジェクトを使い回した方が効率的です。ただしtextプロパティを変更してしまうと意味がありません。構文が同じで値だけが変わるものは、値をリテラルで指定せずパラメータにすることで再利用できます。

また、処理のオーバーヘッドを減らすだけでなく、SQLインジェクション攻撃を防ぐ目的でもパラメータを使います。ユーザーからの入力値を扱う場面では、値を直接SQL文に連結すると危険です。構文をあらかじめ確定できるパラメータ方式で処理してください。下記はパラメータを使った例です。

_sql.text = "INSERT INTO menu (food, price) VALUES (@food, @price)";
_sql.parameters["@food"] = "カレー丼";
_sql.parameters["@price"] = 400;
_sql.execute();

SQL文の中にはプレースホルダとして@を付けたパラメータ名を記述しておきます。@の代わりに:を使うこともできます。その上で、SQLStatementオブジェクトのparametersプロパティを使って各パラメータに値を設定していきます。

また、パラメータ名を指定せずにインデックスで指定する方法もあります。その場合はプレースホルダとして?を記述します。?の登場順にインデックスが割り当てられるので、parametersプロパティでのパラメータ指定にインデックスを使います。

_sql.text = "INSERT INTO menu (food, price) VALUES (?, ?)";
_sql.parameters[0] = "カレー丼";
_sql.parameters[1] = 400;
_sql.execute();

トランザクション

トランザクションを開始するにはSQLConnectionオブジェクトのbegin()メソッドを使います。トランザクション処理が正しく完了したらcommit()メソッドで変更内容を確定し、途中で失敗したらrollback()メソッドで変更内容を破棄します。次のコードはトランザクションを使ってレコードを追加する例です。

private var _conn:SQLConnection;
private var _sql:SQLStatement;
private var _dat:Array;
/* データベースへの接続処理等は省略 */
private function beginTransaction():void {
  _conn.addEventListener(SQLEvent.BEGIN, beginHandler);
  _conn.addEventListener(SQLEvent.COMMIT, commitHandler);
  _conn.addEventListener(SQLEvent.ROLLBACK, rollbackHandler);
  _conn.addEventListener(SQLErrorEvent.ERROR, errorHandler);
  _conn.begin();
}
private function beginHandler(e:SQLEvent):void {
  _dat = [{food:"焼き鳥丼", price:450},
      {food:"玉子",     price:50},
      {food:"味噌汁",   price:50}];
  _sql = new SQLStatement();
  _sql.sqlConnection = _conn;
  _sql.text = "INSERT INTO menu (food, price) VALUES (?, ?)";
  _sql.addEventListener(SQLEvent.RESULT, insertResultHandler);
  _sql.addEventListener(SQLErrorEvent.ERROR, insertErrorHandler);
  insertRecord();
}
private function insertRecord():void {
  var item:Object = _dat.shift();
  _sql.parameters[0] = item.food;
  _sql.parameters[1] = item.price;
  _sql.execute();
}
private function insertResultHandler(e:SQLEvent):void {
  if (!_dat.length) {
    _conn.commit();
  } else {
    insertRecord();
  }
}
private function insertErrorHandler(e:SQLErrorEvent):void {
  _conn.rollback();
}
private function commitHandler(e:SQLEvent):void {
  trace("コミット完了");
}
private function rollbackHandler(e:SQLEvent):void {
  trace("ロールバック完了");
}
private function errorHandler(e:SQLErrorEvent):void {
  trace(e.error.message);
}

SQLiteではINSERTやUPDATE、DELETE等で更新処理を行う際に、1件ずつ暗黙的なトランザクションが使われます。毎回ディスクへの書き込みが発生するため、データ量に応じた時間がかかります。これに対して上記メソッドで明示的にトランザクションを使用すれば、開始から終了までの処理がメモリ上で行われるため、多数のデータを更新する場合にパフォーマンスが向上します。

なお、ここでは前のINSERTが完了してから次を実行していますが、非同期処理の場合でもイベントを待たずに連続してSQLを実行することは可能です。その場合はSQLConnectionオブジェクトのキューに追加され、順番に実行されます。ただし前の実行結果に依存する処理はイベントを待ってから実行する必要があります。

SQLite用ツールの利用

テスト用にデータベースを用意したりちょっとした内容確認をしたいときなどは、SQLite用のデータベース管理ツールを併用すると便利です。SQLite Database BrowserやFireFoxアドオンのSQLite Managerなどがあり、データベースの作成やレコードの追加／削除、SQLの実行といった作業をGUIで行えます。