Changeset 21544

Timestamp:

2012/02/22 01:27:53 (12 years ago)

Author:

Seasoft

Message:

#1656 (カテゴリ登録のロジックを高速化)
#1661 (共通ロジックを利用する)

• SC_Query#getCol

#1660 (SC_Helper_DB#sfCountCategory メモリ消費が激しい)
#1659 (SC_Helper_DB#sfCountCategory メモリリークが発生する)

Location:

branches/version-2_12-dev/data/class

Files:

• SC_Query.php (modified) (3 diffs)
• db/dbfactory/SC_DB_DBFactory_MYSQL.php (modified) (1 diff)
• db/dbfactory/SC_DB_DBFactory_PGSQL.php (modified) (1 diff)
• helper/SC_Helper_DB.php (modified) (7 diffs)

branches/version-2_12-dev/data/class/SC_Query.php

@@ -38 +38 @@
     var $groupby = '';
     var $order = '';
-    var $force_run;
+    var $force_run = false;
 
     /**
@@ -469 +469 @@
      * @param array $arrSql array('カラム名' => 'SQL文', ...)の連想配列
      * @param array $arrSqlVal SQL文の中で使用するプレースホルダ配列
-     * @return
-     */
-    function insert($table, $sqlval, $arrSql = array(), $arrSqlVal = array()) {
+     * @param string $from FROM 句・WHERE 句
+     * @param string $arrFromVal FROM 句・WHERE 句で使用するプレースホルダ配列
+     * @return integer|DB_Error 挿入件数またはDB_Error
+     */
+    function insert($table, $sqlval, $arrSql = array(), $arrSqlVal = array(), $from = '', $arrFromVal = array()) {
         $strcol = '';
         $strval = '';
@@ -502 +504 @@
         }
         // 文末の','を削除
-        $strcol = preg_replace("/,$/", "", $strcol);
-        $strval = preg_replace("/,$/", "", $strval);
-        $sqlin = "INSERT INTO $table(" . $strcol. ") VALUES (" . $strval . ")";
+        $strcol = rtrim($strcol, ',');
+        $strval = rtrim($strval, ',');
+        $sqlin = "INSERT INTO $table($strcol) SELECT $strval";
+
+        if (strlen($from) >= 1) {
+            $sqlin .= ' ' . $from;
+            $arrVal = array_merge($arrVal, $arrFromVal);
+        }
+
         // INSERT文の実行
         $ret = $this->query($sqlin, $arrVal, false, null, MDB2_PREPARE_MANIP);

branches/version-2_12-dev/data/class/db/dbfactory/SC_DB_DBFactory_MYSQL.php

@@ -304 +304 @@
         return $definition;
     }
+
+    /**
+     * 擬似表を表すSQL文(FROM 句)を取得する
+     *
+     * @return string
+     */
+    function getDummyFromClauseSql() {
+        return 'FROM DUAL';
+    }
 }

branches/version-2_12-dev/data/class/db/dbfactory/SC_DB_DBFactory_PGSQL.php

@@ -239 +239 @@
         return array();
     }
+
+    /**
+     * 擬似表を表すSQL文(FROM 句)を取得する
+     *
+     * @return string
+     */
+    function getDummyFromClauseSql() {
+        return '';
+    }
 }

branches/version-2_12-dev/data/class/helper/SC_Helper_DB.php

@@ -540 +540 @@
      */
     function addProductBeforCategories($category_id, $product_id) {
+        $objQuery =& SC_Query_Ex::getSingletonInstance();
 
         $sqlval = array('category_id' => $category_id,
                         'product_id' => $product_id);
 
-        $objQuery =& SC_Query_Ex::getSingletonInstance();
-
-        // 現在の商品カテゴリを取得
-        $arrCat = $objQuery->select('product_id, category_id, rank',
-                                    'dtb_product_categories',
-                                    'category_id = ?',
-                                    array($category_id));
-
-        $max = '0';
-        foreach ($arrCat as $val) {
-            // 同一商品が存在する場合は登録しない
-            if ($val['product_id'] == $product_id) {
-                return;
-            }
-            // 最上位ランクを取得
-            $max = ($max < $val['rank']) ? $val['rank'] : $max;
-        }
-        $sqlval['rank'] = $max + 1;
-        $objQuery->insert('dtb_product_categories', $sqlval);
+        $arrSql = array();
+        $arrSql['rank'] = '(SELECT COALESCE(MAX(rank), 0) FROM dtb_product_categories sub WHERE category_id = ?) + 1';
+
+        $from_and_where = $objQuery->dbFactory->getDummyFromClauseSql();
+        $from_and_where .= ' WHERE NOT EXISTS(SELECT * FROM dtb_product_categories WHERE category_id = ? AND product_id = ?)';
+        $objQuery->insert('dtb_product_categories', $sqlval, $arrSql, array($category_id), $from_and_where, array($category_id, $product_id));
     }
 
@@ -711 +699 @@
         }
 
+        unset($arrCategoryCountOld);
+        unset($arrCategoryCountNew);
+
         $arrDiffCategory_id = array();
         //新しいカテゴリ一覧から見て商品数が異なるデータが無いか確認
@@ -758 +749 @@
         }
 
+        unset($arrOld);
+        unset($arrNew);
+
         //差分があったIDとその親カテゴリIDのリストを取得する
         $arrTgtCategory_id = array();
@@ -763 +757 @@
             $arrTgtCategory_id[] = $parent_category_id;
             $arrParentID = $this->sfGetParents('dtb_category', 'parent_category_id', 'category_id', $parent_category_id);
-            $arrTgtCategory_id = array_merge($arrTgtCategory_id, $arrParentID);
-        }
-
-        //重複を取り除く
-        $arrTgtCategory_id = array_unique($arrTgtCategory_id);
+            $arrTgtCategory_id = array_unique(array_merge($arrTgtCategory_id, $arrParentID));
+        }
+
+        unset($arrDiffCategory_id);
 
         //dtb_category_total_count 集計処理開始
         //更新対象カテゴリIDだけ集計しなおす。
         $arrUpdateData = array();
+        $where_products_class = '';
+        if (NOSTOCK_HIDDEN) {
+            $where_products_class .= '(stock >= 1 OR stock_unlimited = 1)';
+        }
+        $from = $objProduct->alldtlSQL($where_products_class);
         foreach ($arrTgtCategory_id as $category_id) {
             $arrval = array();
@@ -777 +775 @@
             if ($tmp_where != '') {
                 $sql_where_product_ids = 'product_id IN (SELECT product_id FROM dtb_product_categories WHERE ' . $tmp_where . ')';
-                $arrval = array_merge((array)$tmp_arrval, (array)$tmp_arrval);
+                $arrval = $tmp_arrval;
             } else {
                 $sql_where_product_ids = '0<>0'; // 一致させない
@@ -783 +781 @@
             $where = "($sql_where) AND ($sql_where_product_ids)";
 
-            $where_products_class = '';
-            if (NOSTOCK_HIDDEN) {
-                $where_products_class .= '(stock >= 1 OR stock_unlimited = 1)';
-            }
-
-            $from = $objProduct->alldtlSQL($where_products_class);
-            $sql = "SELECT count(*) FROM $from WHERE $where ";
-            $arrUpdateData[ $category_id ] = $objQuery->getOne($sql, $arrval);
-        }
+            $arrUpdateData[$category_id] = $objQuery->count($from, $where, $arrval);
+        }
+
+        unset($arrTgtCategory_id);
+
         // 更新対象だけを更新。
         foreach ($arrUpdateData as $cid => $count) {
@@ -861 +855 @@
         $where = "$pid_name IN (" . implode(',', array_fill(0, count($arrPID), '?')) . ")";
 
-        $ret = $objQuery->select($id_name, $table, $where, $arrPID);
-
-        $arrChildren = array();
-        foreach ($ret as $val) {
-            $arrChildren[] = $val[$id_name];
-        }
-
-        return $arrChildren;
+        $return = $objQuery->getCol($id_name, $table, $where, $arrPID);
+
+        return $return;
     }