Unix実験[データベース] : 中村そば...ずみ! Level.9

Level6で作成した閲覧システムでは、検索結果を同ページに全て出力する。
そのため、該当件数が膨大な数になってしまうと全てを表示するのに時間が掛かり、計算機への負荷も大きくなる。
ページ分散前

そこで、検索結果を指定した件数で分割し、1ページにおける検索結果の表示数を減らす手法(ページング)をとることにした。
ページ分散後

ページングは大手検索サイトである「Yahoo!」や「Google」でも使用されている。
各ページはそれぞれに相互リンクを持ち、ユーザが自由に行き来できる。

[oracle@pw008 j03056]$ php union.php // UNION
01:  2.76349秒
02:  2.70194秒
03:  2.89512秒
04:  3.32322秒
05:  2.85082秒
06:  2.85565秒
07:  2.95929秒
08:  2.78938秒
09:  2.86383秒
10:  2.70990秒
sum: 28.71264秒

[oracle@pw008 j03056]$ php union.php // UNION ALL
01:  0.22327秒
02:  0.21694秒
03:  0.21755秒
04:  0.21746秒
05:  0.22283秒
06:  0.22368秒
07:  0.21811秒
08:  0.21747秒
09:  0.21932秒
10:  0.22271秒
sum: 2.19934秒

nakaden.php


//--------------------
// [$flag]
// 1: 検索ページ初期アクセス状態
// 2: 新しいキーワードによる検索結果
// 3: リンクによる検索結果のページ移動
// 4: 商品を購入した
//--------------------
$flag = 1;


～ html(head)部省略～

<center><h2>中村電機</h2></center>

<center>

<?
if ($flag == 4) {
     zaiko_updata($_POST['buy_id']);
     echo $_POST['buy_item']."をお買い上げ真にあざーす！！&;t;br>\n";
}
?>


～ form部省略 ～


// DBへの接続
$conn = start_connect();

if ($flag == 1) {
     exit_script("");
} else if ($flag == 2) {
     // 入力したキーワードの該当行を抽出するSQL文の生成
     $sql = make_sql($_POST['cate'],     $_POST['highlows'],
		     $_POST['item_word'],   $_POST['maker_word'],
		     $_POST['serial_word'], $_POST['price']);
     if ($debug) {
	  echo $sql."<br>\n";
     }
     // 仮想テーブルの生成
     $all_ros = create_view($conn, $sql, $view_tbl);
     if($all_ros == NULL) {
	  exit_script("fail create_view()<br>\n");
     }
     $_SESSION['all_ros'] = $all_ros;
     $st = 1;
} else {
     $st = ($page-1) * $max_ros + 1;
}

～省略～

//--------------------------------
// SQL文の実行
// $_SESSION['all_ros'] : 仮想テーブル全体の行数
// $ros     : 返されたテーブルの行数 ($max_rowsと同値)
// $res     : テーブルの連想配列
//--------------------------------
list($ros,$res) = select_range_data($conn, $view_tbl, $st, $st+$max_ros-1);

if ($ros == NULL) {
     print_r($ros);
     exit_script("fail select_range_data()");
}

$start = ($page-1)*$max_ros+1;
$end   = $start + $max_ros-1;

for ($i = 0; $i < $ros; $i++) {


～商品表示前半省略～


  // 在庫チェック
  $zaiko = get_zaiko($conn, $tmp[1]);
  if ($zaiko == NULL) {
       exit_script("fail get_zaiko\n");
  } else if ($zaiko > 1) {
       $msg = "在庫有り";
       if (1) {
	    $msg .= " $zaiko";
       }
  } else {
       $msg = "在庫なし";
  }
}


// 同キーワードによる検索結果の他ページへのリンク
for ($i=1; $i*$max_ros < $max_ros+$_SESSION['all_ros']; $i++) {
  if ($i == $page) {
    $next .= " $i ";
  } else {
    $next .= " <a href=\"$PHP_SELF?page=$i\">$i</a> ";
  }
}
echo "$next<br>";
end_connect($conn);
?>
</div>
</body>
</html>

sql.php


function make_sql($cate,$highlows,$item,$maker,$serial,$price) {

～省略～

     if ($table_all) {
	  for ($i=1;$i<count($category);$i++) {
	       $tmp = $tmp.str_replace('TB',$category[$i],$sql);
	       if($i%(count($category)-1)!=0){
	            // UNION ALL に変更
		    $tmp = $tmp." UNION ALL ";
	       }
	  }
	  $sql = $tmp;
     }
     return $sql;
}

//------------------------------
// $sqlによって抽出された行を
// 仮想テーブルとして生成する
//
// arg
//   $con      : 接続id
//   $sql      : SQL文
//   $view_tbl : 仮想テーブル名
//
// return 
//   $view_tblの行数
//------------------------------
function create_view($con, $sql, $view_tbl) {
  $sql  = "create or replace view $view_tbl as ".$sql;
  $stmt = oci_parse($con,$sql);

  if (!oci_execute($stmt,OCI_DEFAULT)) {
       echo "error create_view\n";
       oci_free_statement($stmt);
       return NULL;
  }
  oci_free_statement($stmt);

  /* $view_tblの行数を計算する */
  $stmt = oci_parse($con, "select count(*) from $view_tbl");
  if(!oci_execute($stmt,OCI_DEFAULT)){
       echo "error create_view_2\n";
       oci_free_statement($stmt);
       return NULL;
  }
  if (!($all_ros = oci_fetch_row($stmt))) {
       echo "error create_view3\n";
       oci_free_statement($stmt);
       return NULL;
  }
  oci_free_statement($stmt);
  return $all_ros[0];
}

/*
 * 指定したテーブルから指定した件数のデータを返す
 */
function select_range_data($con, $tbl, $start, $end)
{
     $sql  = "select * from (";
     $sql .= "select rownum as seq_no,$tbl.* from $tbl) ";
     $sql .= "where seq_no between $start and $end";

     $stmt = oci_parse($con,$sql);
     if(!oci_execute($stmt,OCI_DEFAULT)){
	  echo "error select_range_data\n";
	  oci_free_statement($stmt);
	  return array(NULL, NULL);
     }

     $ros = oci_fetch_all($stmt,$res);

     oci_free_statement($stmt);
     return array($ros,$res);
}


/*
 * 商品ID が $id の在庫を取得する。
 */
function get_zaiko($con, $id)
{
     $sql = "select item_stock from stock where item_id = '".$id."'";
     $stmt = oci_parse($con,$sql);

     if (!oci_execute($stmt,OCI_DEFAULT)) {
	  echo "error get_zaiko\n";
	  oci_free_statement($stmt);
	  return NULL;
     }

     if (!($zaiko = oci_fetch_row($stmt))) {
	  echo "error get_zaiko2\n";
	  oci_free_statement($stmt);
	  return NULL;
     }

     oci_free_statement($stmt);
     return $zaiko[0];
}

/*
 * 在庫の更新(在庫数-1)
 */
function zaiko_updata($ID)
{
     $sql  = "lock table stock in exclusive mode nowait";
     $con  = start_connect();
     $stmt = oci_parse($con,$sql);

     $sql  = "update stock set ITEM_STOCK = ITEM_STOCK -1 where ITEM_ID = $ID";

     $stmt=oci_parse($con,$sql);
     if (!oci_execute($stmt,OCI_DEFAULT)) {
	  oci_rollback($con);
	  return NULL;
     } else {
	  oci_commit($con);
     }

     oci_free_statement($stmt);
     oci_close($con);
}

Level.9 システムの改良

1. 改良したシステムおよびインタフェース

2.検索結果の分散表示

2-1. ページング

2-2. 仮想テーブルの利用

3. テーブルの結合

4. プログラム

4-1. メインプログラム

SQLプログラム

実行結果