The Oracle + PHP Cookbook

Google的な検索演算子を簡易検索ボックスへ追加する方法

ユーザーの最重要情報への即時アクセスが可能に

関連するダウンロード・リンク サンプル・コードとリスト Oracle Database 10g Express Edition Zend Core for Oracle

2006年5月公開

ユーザーによって実行される検索操作の範囲は多岐に渡っており、拡大し続けています。 実際、アプリケーションによっては、検索結果を実際に利用することより、検索自体に多くの時間を費やすようになっています。 情報をより簡単に利用できるようにすることで、インフォメーション・ワーカーにとってのデータの価値を高めるにはどうすればよいでしょうか。 データベース操作といくつかの文字列操作をきちんと把握すれば、ユーザーに必要な情報を見つけるという目的に向かう道筋を示すことができます。 このレシピでは、モジュール方式のアプローチによって、PHPとOracleを使用して検索機能を拡張する方法を紹介します。

このレシピの利点

ユーザーが派生アプリケーションの利点に慣れ、データに対する所有権をより強く感じるようになり、データベース内から新しい情報を次々に引き出すようになるにつれて、多くのデータ・モデルが拡張され変化しています。 これは典型的な"良い問題"ですが、それでも問題には違いありません。 このレシピのサンプル・アプリケーションでは、検索機能と、ユーザーによって受け入れられているインタフェース、すなわち簡単な検索ボックスを備えた、シンプルで柔軟性の高いレポート・ツールを紹介します。

このサンプル・アプリケーションはPHP5で作成されており、オブジェクト指向の概念を至る所で使用しています。 データ・モデルは、 Oracle Database 10g Express Edition（XE）で提供されているHRスキーマです。 PHPとOracle間の接続には、 Zend Core for Oracle を使用するのがもっとも簡単です。

このサンプル・アプリケーションは、Webサーバー上のほぼあらゆる場所からほとんど変更せずに実行できます。 ローカル環境のデータベース・ユーザー、パスワード、接続文字列を正確に反映するように、constants.incに指定された最初の3つの値を編集する必要があります。 サンプル・コードには、Saleエンティティとadmin.phpによるシーケンス（サンプル・データを生成するスクリプト）が含まれています。 サンプル・コードは、多数のクラス・ファイルと、大半の"クロム"HTMLを含む数個の高レベル・ファイルからなります。

最初の問合せ

このサンプル・アプリケーションは、人事アプリケーション内で社員を検索するための検索ツールであり、日常業務をおこなう一般社員をユーザーとして想定しています。 これらのユーザーは次の情報を必要としています。

• 名前（姓と名）
• メール・アドレス
• 電話番号
• 部門

SELECT * FROM EMPLOYEES
                       

痛みなければ得るものなし

ところが、そう簡単には終わりません。 ユーザーが最初のアプリケーションを大変気に入り、機能拡張を行うことになりました。 社員情報を検索するとき、同時に管理者情報も確認できるようにして欲しいとのことです。 そこで、自己結合を使用することにします。HTML表は大きくなったものの完全になったので、ユーザーには何の不満もありません。

しかし、次の週になると、さらに販売情報を追加し、フィルタリング用のドロップダウン項目も追加して欲しいといわれます。 こうして"仕様追加"が続くことで、最終的にアプリケーションはほとんど管理不能となり、Webページは見づらくドキュメント化もされておらず、ユーザーの要求は満たされないという状態になります。 開発者は苦境に立たされます。

こういうときは、最初に戻ってみると解決策が見つかることがあります。 ユーザーは、基準に基づいて絞り込まれたリストを表示する機能を求めています。 彼らは興味のある情報を見たいだけで、すべてを一度に見たいわけではありません。 この問題を解決するため、サンプル・アプリケーションでは、上記のプロセスの象徴的な部分に注目し、それを扱いやすい大きさのPHPおよびSQLの断片に分割します。アプリケーションの成長とともに、これらの断片も拡張または成長していきます。

このプロセスのアトミックな単位が演算子です。演算子は、SQL問合せに検索基準を追加していきます。 演算子の実装に入る前に、基礎となる問合せについて見ておく必要があります。

SELECT 
  (a.EMPLOYEE_ID)                     "Employee ID", 
  (a.FIRST_NAME)                      "First Name", 
  (a.LAST_NAME)                       "Last Name", 
  (LOWER(a.EMAIL || '@widgetco.com')) "Email", 
  (a.PHONE_NUMBER)                    "Phone", 
  (a.DEPARTMENT_ID)                   "Department" 
FROM 
  (EMPLOYEES) a 
ORDER BY 
  a.LAST_NAME asc
                       

class EmployeeSearcher extends Searcher{

public function EmployeeSearcher( $q = "" , $db = null ){
    $this->Searcher( $q, $db );    
    $this->query->setMainTable( "EMPLOYEES" );
    $prefix = $this->query->getMainTableAlias();
    $baseColumns = array( '"Employee ID"'  =>  "$prefix.EMPLOYEE_ID", 
                          '"First Name"'   =>  "$prefix.FIRST_NAME", 
                          '"Last Name"'    =>  "$prefix.LAST_NAME", 
                          '"Email"'        =>  "LOWER($prefix.EMAIL ||
                                                                                '@widgetco.com')",
                          '"Phone"'        =>  "$prefix.PHONE_NUMBER", 
                          '"Department"'   =>  "$prefix.DEPARTMENT_ID");
    foreach ($baseColumns as   $column=>$alias){
      $this->query->addColumn( $alias, $column );
    }
  }
  
}
                       

演算子の基礎

各演算子はそれぞれアプリケーションに少しずつ柔軟性を追加し、同時に最終的な出力の質を高めます。 次に示す例は、Nameという名前の演算子です。この演算子は、社員の姓または名をフィルタリングする機能を持ちます。

class Name extends Operator{
  
  public function __construct() { 
    $this->setHint( 'Employee named', 'name' );
  }
  
  public function process( $item, $token ) {  
    $alias = $this->query->getMainTableAlias();
    $this->query->addWhere( 
      array( "lower({$alias}.first_name || {$alias}.last_name ) like",
              array( "%$item%" )
           )
    );
  }
  
}
                       

name:Jo

すると、この演算子によって、名または姓に"Jo"が含まれる社員だけを含むリストが表示されます。 このような簡単な操作をおこなうだけで、"Joshuas"、"Johnsons"など、名前に"Jo"が含まれるすべての社員が表示されます。これにより、たとえば、あのJohn何某という名前の社員について知りたいといったちょっとした質問を簡単におこなうことができます。

次に、別の演算子を示します。これは、各社員の上司に基づいたフィルタリングをおこなう演算子です。

class Manager extends Operator{
  
  public function __construct() { 
    $this->setHint( 'Manager named',      'manager');
    $this->setHint( 'Manager not named',  '~manager');
    $this->hasNascent = true;
  }
  
  public function process( $item , $token ) {  
    $alias = $this->query->getMainTableAlias();
    $joinAlias = $this->ensureJoin();    
    
    $not = "";
    
    if( $token == '~manager' ){
      $not = "not";
    }
    
    $this->query->addWhere(
      array(
        "lower({$joinAlias}.first_name || {$joinAlias}.last_name ) $not like",
        array( "%$item%" )
           )
    );
    $this->hasActed = true;
  }
  
  protected function ensureJoin(){
    $alias = $this->query->getMainTableAlias();
    if( $this->hasActed ){
      $joinAlias = $this->query->getPrefix( 'employees' );
    } else {
      $joinAlias = $this->query->addTable('employees');
      $this->query->addWhere( array( "$alias.manager_id = $joinAlias.employee_id" ) );  
      $this->query->addColumn( "$joinAlias.last_name", '"Manager Last Name"' );
    }
    return $joinAlias;
  }
}
                       

                                           
manager:king name:john

SELECT    (a.EMPLOYEE_ID) "Employee ID", 
          (a.FIRST_NAME) "First Name", 
          (a.LAST_NAME) "Last Name", 
          (LOWER(a.EMAIL || '@widgetco.com')) "Email", 
          (a.PHONE_NUMBER) "Phone", 
          (a.DEPARTMENT_ID) "Department", 
          (b.LAST_NAME) "Manager Last Name" 
FROM      (EMPLOYEES) a, 
          (EMPLOYEES) b 
WHERE     a.MANAGER_ID = b.EMPLOYEE_ID 
  AND     LOWER(b.FIRST_NAME || b.LAST_NAME ) LIKE :a_1 
  AND     LOWER(a.FIRST_NAME || a.LAST_NAME ) LIKE :b_2 
ORDER BY  a.LAST_NAME asc
                       
                                        

簡単な検索指定にしてはかなり複雑な問合せですが、ユーザーが望むとおりの仕事を実行し、厄介な結合操作も多くの場合1箇所だけで済みます。 もちろん、Kingを上司に持つJohn何某という人物を検索するといった、よくある面倒な検索にも使えます。

注意すべき重要な点は、バインド変数を使用するという点です。 2つのバインド変数a_1とb_2はそれぞれ、"%king%"と"%john"にバインドされます。 これらの情報はユーザーが入力するものなので、信用しない必要があります。 バインド変数を使用することで、ユーザーが入力によって問合せを壊してしまうのを防ぎ、キャッシュによる利点を享受できます。

問合せの生成

上記では、テキスト演算子の例を紹介しましたが、数値演算子も簡単に実現できます。 以下に、Sales演算子の重要な部分を示します。

public function process( $item , $token ) {  
/* ... */
switch( $token ){
      case( 'salesabove' ):
      $this->query->addWhere( 
        array( "nvl($joinAlias.totalsales,0) >= ", 
                array( "$item" ) ) );
      break;
      case( 'salesbelow' ):
      $this->query->addWhere( 
        array( "nvl($joinAlias.totalsales,0) <= ", 
                array( "$item" ) ) );
    }
/* ... */
protected function ensureJoin(){
/* ... */
$this->query->addColumn( "nvl($joinAlias.totalsales,0)", '"Total Sales"' );
/* ... */
$table = "select salesperson, count(*) totalsales from sale group by salesperson";
                       

Sales演算子は、（前出の新しい列を導入する例で新しい結合表を追加したのと同じ要領で）1つの完全な副問合せを追加し、2つの比較演算子を有効化します。 この方法により、ユーザーは、まったく新しい方法で、たとえば、異なる何人かの社員を対象にハワイ旅行の販売合計数を表示するといったことができます。 ただし、この検索指示を問合せレベルまで落とすためには少し工夫が必要です。 ユーザー指向的な方法ですべてをおこなう方法から少し離れて、文字列操作のみに集中する必要があります。

このメソッド（サンプル・コードQuery.class.php）は、文字列の詳細な操作を実行します。

public function buildSQL(){
  $this->results = null;
  $columns = array();
  foreach( $this->columns as $columnAlias => $columnExpression ) {
    if( is_numeric( $columnAlias ) ){
      $columns[] = $columnExpression;
    }else{
      $columns[] = "($columnExpression) $columnAlias";
    }
  }
  $columns = implode( ", ", $columns );
    
  $tables = array( '('.$this->mainTable.') '.$this->mainTableAlias );
  foreach( $this->tables as $tableAlias => $tableExpression ) {

    $tables[] = "($tableExpression) $tableAlias";
  }
  $tables = implode( ", ", $tables );
    
  $wheres = implode( ' '.$this->whereGlue.' ', $this->wheres );
  if( $wheres ){
    $wheres = " WHERE $wheres ";
  }
    
  $order = "{$this->mainTableAlias}.last_name asc";
    
  $this->sql = "SELECT $columns FROM $tables $wheres ORDER BY $order";
  $stmt = oci_parse( $this->db, $this->sql );
  foreach( $this->bindings as $bindName => $bindValue ){
    oci_bind_by_name( $stmt, $bindName, $this->bindings[$bindName] );
  }    
        oci_execute( $stmt );    
        oci_fetch_all ( $stmt, $this->results );
        oci_free_statement( $stmt );
  return true;
}
                       

最後の仕上げ

問合せのすべての機能を統率するのはサーチャーです。上の例ではEmployeeSearcherであり、そのおもな機能は検索結果のフォーマッティングです。 このサーチャーは、演算子の動的なロードも実行します。 次の小さなスクリプトは、前出の見づらい表タグ内に結果セットをフォーマットする方法を示しています。

private function formatResults( $columns ){
  $keys = array_keys( $columns );
  $results = "";
  foreach( $keys as $key ){
    $results .= "<th>$key</th>";
  }
  foreach( $columns[$keys[0]] as $row => $foo ){
    $temp = "";
    foreach( $keys as $key ){
      $temp .= '<td class="'.$this->cssClean( $key ).
                         "\">{$columns[$key][$row]}</td>\n";
    }      
    $results .= "<tr>$temp</tr>\n";
  }
  return "<table>$results</table> <div class=\"recordCount\">".
         count( $columns[$keys[0]] ).
         " records.</div>";
}
                       

public function EmployeeSearchPage( $request ){
  $this->Page( $request );
  $this->setTitle( "Search" );
  if( isset( $request["q"] ) ){
    $this->db = oci_connect( WCO_DB_USER, WCO_DB_PASSWORD, WCO_DB_HOST );
    if (!$this->db) {
          exit;
        }
    $this->setTitle( "Search Results" );
    include_once( "search/EmployeeSearcher.class.php" );
    $searcher = new EmployeeSearcher( $request["q"] , $this->db);
    $this->setBody( $this->getBody() . $searcher->getResults() );
    oci_close( $this->db );
  }
}
                       

$page = new EmployeeSearchPage( $_REQUEST );
if( @$_REQUEST['q'] ){
  $page->setBody( $page->getBody() );
}
include_once( "index.php" );
                       

使い方の表示

すべての演算子を最初から人間の言葉で記述しておけば、演算子のヒントをわかりやすいフォーマットで出力するのはさほど難しくはありません。 サーチャー内の次のメソッドは、HTMLを処理します。

public function getHints(){
    $this->initOperators();      
    $results = "";
    foreach($this->operators as $token => $operator){
      $results .= "<li><span class=\"token\">$token:</span>" . 
                            $operator->getHint( $token ) . "</li>\n";
    }
    return "<ul>$results</ul>";
  }
                       

                                           
public function HintsPage( $request ){
  $this->Page( $request );
  $this->setTitle( "Search Hints" );
  include_once( "search/EmployeeSearcher.class.php" );
  $searcher = new EmployeeSearcher();
  $this->setBody( $this->getBody() . 
                  "Use the following operators to enhance your search:" .
                  $searcher->getHints() );
  
}
                       
                                        

再実行

比較的複雑なフィルタリングを記述するために必要なフォーム入力は短い文字列だけであるため、ユーザーは検索結果ページを頻繁に使用するビューとして簡単にブックマークしておくことができます。

サンプル・アプリケーションは、人事データ・モデルの基本的な知識といくつかのユーザー要求を開始点として、上記に示したものと同じパターンで作成されています。ただし、成果は異なっています。すなわち、実装と並行してではなく設計時に意志決定をおこなうことで、ユーザー用ドキュメントが組み込まれた拡張可能なシステムを実現できます。 ユーザー側から見ると、増え続けるデータをより効率的に検索できるという利点があります。

この例は人事データの社員中心型のビューアです。異なるベースとなる問合せによる新しいサーチャーを定義すれば、新しい演算子を用いて国や部門に基づくビューアを提供することも可能です。 サンプル・コードにいくつか追加の演算子が含まれています。 さらなる機能拡張として、演算子によって変更可能なGroup By句やOrder By句の導入、PHPとCSSによる新しい高品質の表示、Ajaxによる改良などが考えられます。

ユーザー・データとデータベースのあらゆる接点にバインド変数を使用することで、SQLインジェクション攻撃の大半を防ぐことができます。これは、潜在的な機密情報を扱う際には重要な点です。

結論

ここで紹介したような、データのフロントエンドとして動作する拡張可能なシステムは、ユーザーによる探索が可能で、保守もほとんど必要ありません。 それを可能にしているのは、モジュール方式と丁寧に設計されたデータ・モデルです。 初期の段階でユーザー向けのドキュメントについて考えておくことで、アプリケーションは、簡潔かつ正確に、そして、コードの構造と読みやすさを高めるようなドキュメントを提供できます。 コードを探索してみてください。

ご意見ご感想をお寄せください。