Index: branches/camp/camp-2_13-tests/tests/class/helper/SC_Helper_DB/SC_Helper_DB_sfColumnAddTest.php =================================================================== --- branches/camp/camp-2_13-tests/tests/class/helper/SC_Helper_DB/SC_Helper_DB_sfColumnAddTest.php (revision 22678) +++ branches/camp/camp-2_13-tests/tests/class/helper/SC_Helper_DB/SC_Helper_DB_sfColumnAddTest.php (revision 22678) @@ -0,0 +1,62 @@ +helper = new SC_Helper_DB_Ex(); + } + + protected function tearDown() + { + parent::tearDown(); + } + + ///////////////////////////////////////// + public function testSfColumnAdd_指定のカラムが追加できたら_TRUEを返す() + { + $tableName = 'dtb_news'; + $colName = 'news_id2'; + $colType = 'int'; + $this->expected = true; + $this->helper->sfColumnAdd($tableName, $colName, $colType); + $columns = $this->objQuery->listTableFields($tableName); + $this->actual = in_array($colName, $columns); + // rolbackできないのでカラムを削除する + $this->objQuery->query("ALTER TABLE $tableName DROP $colName"); + $this->verify(); + } + +} Index: branches/camp/camp-2_13-tests/tests/class/helper/SC_Helper_DB/SC_Helper_DB_sfDataExistsTest.php =================================================================== --- branches/camp/camp-2_13-tests/tests/class/helper/SC_Helper_DB/SC_Helper_DB_sfDataExistsTest.php (revision 22678) +++ branches/camp/camp-2_13-tests/tests/class/helper/SC_Helper_DB/SC_Helper_DB_sfDataExistsTest.php (revision 22678) @@ -0,0 +1,72 @@ +helper = new SC_Helper_DB_Ex(); + } + + protected function tearDown() + { + parent::tearDown(); + } + + ///////////////////////////////////////// + public function testSfDataExists_データが存在する場合_TRUEを返す() + { + $this->setUpNews(); + $tableName = 'dtb_news'; + $where = 'news_id = ?'; + $arrWhereVal = array(3); + $this->expected = true; + $this->actual = $this->helper->sfDataExists($tableName, $where, $arrWhereVal); + $this->verify(); + } + + public function testSfDataExists_データが存在しない場合_FALSEを返す() + { + $this->setUpNews(); + $tableName = 'dtb_news'; + $where = 'news_id = ?'; + $arrWhereVal = array(4); + $this->expected = false; + $this->actual = $this->helper->sfDataExists($tableName, $where, $arrWhereVal); + $this->verify(); + } + +} + + Index: branches/camp/camp-2_13-tests/tests/class/helper/SC_Helper_DB/SC_Helper_DB_sfGetBasisDataTest.php =================================================================== --- branches/camp/camp-2_13-tests/tests/class/helper/SC_Helper_DB/SC_Helper_DB_sfGetBasisDataTest.php (revision 22678) +++ branches/camp/camp-2_13-tests/tests/class/helper/SC_Helper_DB/SC_Helper_DB_sfGetBasisDataTest.php (revision 22678) @@ -0,0 +1,161 @@ +helper = new SC_Helper_DB_Ex(); + } + + protected function tearDown() + { + parent::tearDown(); + } + + ///////////////////////////////////////// + public function testsfGetBasisData_条件を指定しない場合_baseinfoをすべて返す() + { + $this->setUpBasisData(); + $this->expected = array( + 'id' => '1', + 'company_name' => 'testshop', + 'company_kana' => 'テストショップ', + 'zip01' => '530', + 'zip02' => '0001', + 'pref' => '1', + 'addr01' => 'testaddr01', + 'addr02' => 'testaddr02', + 'tel01' => '11', + 'tel02' => '2222', + 'tel03' => '3333', + 'fax01' => '11', + 'fax02' => '2222', + 'fax03' => '3333', + 'business_hour' => '09-18', + 'law_company' => 'lawcampanyname', + 'law_manager' => 'lawmanager', + 'law_zip01' => '530', + 'law_zip02' => '0001', + 'law_pref' => '1', + 'law_addr01' => 'lawaddr01', + 'law_addr02' => 'lawaddr02', + 'law_tel01' => '11', + 'law_tel02' => '2222', + 'law_tel03' => '3333', + 'law_fax01' => '11', + 'law_fax02' => '2222', + 'law_fax03' => '3333', + 'law_email' => 'test@test.com', + 'law_url' => 'http://test.test', + 'law_term01' => 'lawterm01', + 'law_term02' => 'lawterm02', + 'law_term03' => 'lawterm03', + 'law_term04' => 'lawterm04', + 'law_term05' => 'lawterm05', + 'law_term06' => 'lawterm06', + 'law_term07' => 'lawterm07', + 'law_term08' => 'lawterm08', + 'law_term09' => 'lawterm09', + 'law_term10' => 'lawterm10', + 'tax' => '5', + 'tax_rule' => '1', + 'email01' => 'test1@test.com', + 'email02' => 'test2@test.com', + 'email03' => 'test3@test.com', + 'email04' => 'test4@test.com', + 'email05' => 'test5@test.com', + 'free_rule' => '1000', + 'shop_name' => 'shopname', + 'shop_kana' => 'ショップネーム', + 'shop_name_eng' => 'shopnameeng', + 'point_rate' => '10', + 'welcome_point' => '100', + 'update_date' => '2012-02-14 11:22:33', + 'top_tpl' => 'top.tpl', + 'product_tpl' => 'product.tpl', + 'detail_tpl' => 'detail.tpl', + 'mypage_tpl' => 'mypage.tpl', + 'good_traded' => 'goodtraded', + 'message' => 'message', + 'regular_holiday_ids' => '0|6', + 'latitude' => '30.0001', + 'longitude' => '45.0001', + 'downloadable_days' => '10', + 'downloadable_days_unlimited' => '0' + ); + $this->actual = $this->helper->sfGetBasisData(true); + $this->verify(); + } + + + public function testsfGetBasisData_カラムを指定する場合_指定のカラムだけを返す() + { + $this->setUpBasisData(); + $this->expected = array( + 'id' => '1', + 'company_name' => 'testshop' + ); + $force = true; + $col = 'id, company_name'; + $this->actual = $this->helper->sfGetBasisData($force, $col); + $this->verify(); + } + + public function testsfGetBasisData_baseinfoが空の場合_空を返す() + { + $this->objQuery->delete('dtb_baseinfo'); + $this->expected = array(); + $this->actual = $this->helper->sfGetBasisData(true); + $this->verify(); + } + + public function testsfGetBasisData_forceがfalseの場合_キャッシュを返す() + { + $this->setUpBasisData(); + $force = true; + $col = 'id, company_name'; + //事前にキャッシュを生成 + $this->actual = $this->helper->sfGetBasisData($force, $col); + //baseinfoを空にしてしまう + $this->objQuery->delete('dtb_baseinfo'); + $force = false; + $this->expected = array( + 'id' => '1', + 'company_name' => 'testshop' + ); + $this->actual = $this->helper->sfGetBasisData(false); + $this->verify(); + } +} Index: branches/camp/camp-2_13-tests/tests/class/helper/SC_Helper_DB/SC_Helper_DB_sfColumnExistsTest.php =================================================================== --- branches/camp/camp-2_13-tests/tests/class/helper/SC_Helper_DB/SC_Helper_DB_sfColumnExistsTest.php (revision 22678) +++ branches/camp/camp-2_13-tests/tests/class/helper/SC_Helper_DB/SC_Helper_DB_sfColumnExistsTest.php (revision 22678) @@ -0,0 +1,87 @@ +helper = new SC_Helper_DB_Mock(); + } + + protected function tearDown() + { + parent::tearDown(); + } + + ///////////////////////////////////////// + public function testSfColumnExists_指定のカラムが存在する場合_TRUEを返す() + { + $tableName = 'dtb_news'; + $colName = 'news_id'; + $this->expected = true; + $this->actual = $this->helper->sfColumnExists($tableName, $colName); + $this->verify(); + } + + public function testSfColumnExists_指定のカラムが存在しない場合_FALSEを返す() + { + $tableName = 'dtb_news'; + $colName = 'news_id2'; + $this->expected = false; + $this->actual = $this->helper->sfColumnExists($tableName, $colName); + $this->verify(); + } + + public function testSfColumnExists_指定のカラムが存在しない場合追加して_TRUEを返す() + { + $tableName = 'dtb_news'; + $colName = 'news_id2'; + $colType = 'int'; + $dsn = ''; + $add = true; + $this->expected = true; + $this->actual = $this->helper->sfColumnExists($tableName, $colName, $colType, $dsn, $add); + $this->verify(); + } + +} + +class SC_Helper_DB_Mock extends SC_Helper_DB_Ex +{ + function sfColumnAdd($tableName, $colName, $colType) { + return true; + } +} + + Index: branches/camp/camp-2_13-tests/tests/class/helper/SC_Helper_DB/SC_Helper_DB_TestBase.php =================================================================== --- branches/camp/camp-2_13-tests/tests/class/helper/SC_Helper_DB/SC_Helper_DB_TestBase.php (revision 22678) +++ branches/camp/camp-2_13-tests/tests/class/helper/SC_Helper_DB/SC_Helper_DB_TestBase.php (revision 22678) @@ -0,0 +1,162 @@ + '2000-01-01 00:00:00', + 'news_id' => '1', + 'news_title' => 'ニュース情報01', + 'rank' => '1', + 'creator_id' => '1', + 'del_flg' => '0' + ), + array( + 'update_date' => '2000-01-01 00:00:00', + 'news_id' => '2', + 'news_title' => 'ニュース情報02', + 'rank' => '2', + 'creator_id' => '1', + 'del_flg' => '0' + ), + array( + 'update_date' => '2000-01-01 00:00:00', + 'news_id' => '3', + 'news_title' => 'ニュース情報03', + 'rank' => '3', + 'creator_id' => '1', + 'del_flg' => '0' + ) + ); + + $this->objQuery->delete('dtb_news'); + foreach ($news as $key => $item) { + $this->objQuery->insert('dtb_news', $item); + } + } + + /** + * DBに基本情報を設定します。 + */ + protected function setUpBasisData() + { + $baseInfo = array( + 'id' => '1', + 'company_name' => 'testshop', + 'company_kana' => 'テストショップ', + 'zip01' => '530', + 'zip02' => '0001', + 'pref' => '1', + 'addr01' => 'testaddr01', + 'addr02' => 'testaddr02', + 'tel01' => '11', + 'tel02' => '2222', + 'tel03' => '3333', + 'fax01' => '11', + 'fax02' => '2222', + 'fax03' => '3333', + 'business_hour' => '09-18', + 'law_company' => 'lawcampanyname', + 'law_manager' => 'lawmanager', + 'law_zip01' => '530', + 'law_zip02' => '0001', + 'law_pref' => '1', + 'law_addr01' => 'lawaddr01', + 'law_addr02' => 'lawaddr02', + 'law_tel01' => '11', + 'law_tel02' => '2222', + 'law_tel03' => '3333', + 'law_fax01' => '11', + 'law_fax02' => '2222', + 'law_fax03' => '3333', + 'law_email' => 'test@test.com', + 'law_url' => 'http://test.test', + 'law_term01' => 'lawterm01', + 'law_term02' => 'lawterm02', + 'law_term03' => 'lawterm03', + 'law_term04' => 'lawterm04', + 'law_term05' => 'lawterm05', + 'law_term06' => 'lawterm06', + 'law_term07' => 'lawterm07', + 'law_term08' => 'lawterm08', + 'law_term09' => 'lawterm09', + 'law_term10' => 'lawterm10', + 'tax' => '5', + 'tax_rule' => '1', + 'email01' => 'test1@test.com', + 'email02' => 'test2@test.com', + 'email03' => 'test3@test.com', + 'email04' => 'test4@test.com', + 'email05' => 'test5@test.com', + 'free_rule' => '1000', + 'shop_name' => 'shopname', + 'shop_kana' => 'ショップネーム', + 'shop_name_eng' => 'shopnameeng', + 'point_rate' => '10', + 'welcome_point' => '100', + 'update_date' => '2012-02-14 11:22:33', + 'top_tpl' => 'top.tpl', + 'product_tpl' => 'product.tpl', + 'detail_tpl' => 'detail.tpl', + 'mypage_tpl' => 'mypage.tpl', + 'good_traded' => 'goodtraded', + 'message' => 'message', + 'regular_holiday_ids' => '0|6', + 'latitude' => '30.0001', + 'longitude' => '45.0001', + 'downloadable_days' => '10', + 'downloadable_days_unlimited' => '0' + ); + + $this->objQuery->delete('dtb_baseinfo'); + $this->objQuery->insert('dtb_baseinfo', $baseInfo); + } + + +} Index: branches/camp/camp-2_13-tests/tests/class/helper/SC_Helper_DB/SC_Helper_DB_sfMoveRankTest.php =================================================================== --- branches/camp/camp-2_13-tests/tests/class/helper/SC_Helper_DB/SC_Helper_DB_sfMoveRankTest.php (revision 22678) +++ branches/camp/camp-2_13-tests/tests/class/helper/SC_Helper_DB/SC_Helper_DB_sfMoveRankTest.php (revision 22678) @@ -0,0 +1,135 @@ +helper = new SC_Helper_DB_Ex(); + } + + protected function tearDown() + { + parent::tearDown(); + } + + ///////////////////////////////////////// + public function testSfMoveRank_一番下に移動する場合_RANK1を返す() + { + $this->setUpNews(); + $table = 'dtb_news'; + $keyIdColum = 'news_id'; + $keyId = '3'; + $pos = '3'; + $where = null; + $this->expected = 1; + $this->helper->sfMoveRank($table, $keyIdColum, $keyId, $pos, $where); + $col = 'rank'; + $getWhere = 'news_id = ?'; + $arrWhereVal = array($keyId); + $this->actual = $this->objQuery->get($col, $table, $getWhere, $arrWhereVal); + $this->verify(); + } + + public function testSfMoveRank_一番上に移動する場合_RANKはMAXを返す() + { + $this->setUpNews(); + $table = 'dtb_news'; + $keyIdColum = 'news_id'; + $keyId = '2'; + $pos = '1'; + $where = null; + $this->expected = $this->objQuery->max('rank', $table); + $this->helper->sfMoveRank($table, $keyIdColum, $keyId, $pos, $where); + $col = 'rank'; + $getWhere = 'news_id = ?'; + $arrWhereVal = array($keyId); + $this->actual = $this->objQuery->get($col, $table, $getWhere, $arrWhereVal); + $this->verify(); + } + + public function testSfMoveRank_同じ位置に移動する場合_RANKは変わらない() + { + $this->setUpNews(); + $table = 'dtb_news'; + $keyIdColum = 'news_id'; + $keyId = '3'; + $pos = '1'; + $where = null; + $this->expected = 3; + $this->helper->sfMoveRank($table, $keyIdColum, $keyId, $pos, $where); + $col = 'rank'; + $getWhere = 'news_id = ?'; + $arrWhereVal = array($keyId); + $this->actual = $this->objQuery->get($col, $table, $getWhere, $arrWhereVal); + $this->verify(); + } + + public function testSfMoveRank_マイナスの位置に移動する場合_RANKはMAX() + { + $this->setUpNews(); + $table = 'dtb_news'; + $keyIdColum = 'news_id'; + $keyId = '2'; + $pos = '-1'; + $where = null; + $this->expected = $this->objQuery->max('rank', $table); + $this->helper->sfMoveRank($table, $keyIdColum, $keyId, $pos, $where); + $col = 'rank'; + $getWhere = 'news_id = ?'; + $arrWhereVal = array($keyId); + $this->actual = $this->objQuery->get($col, $table, $getWhere, $arrWhereVal); + $this->verify(); + } + + public function testSfMoveRank_最大値以上の位置を与える場合_RANKは1となる() + { + $this->setUpNews(); + $table = 'dtb_news'; + $keyIdColum = 'news_id'; + $keyId = '2'; + $pos = '4'; + $where = null; + $this->expected = 1; + $this->helper->sfMoveRank($table, $keyIdColum, $keyId, $pos, $where); + $col = 'rank'; + $getWhere = 'news_id = ?'; + $arrWhereVal = array($keyId); + $this->actual = $this->objQuery->get($col, $table, $getWhere, $arrWhereVal); + $this->verify(); + } + +} + + Index: branches/camp/camp-2_13-tests/data/class/helper/SC_Helper_DB.php =================================================================== --- branches/camp/camp-2_13-tests/data/class/helper/SC_Helper_DB.php (revision 22595) +++ branches/camp/camp-2_13-tests/data/class/helper/SC_Helper_DB.php (revision 22678) @@ -56,5 +56,5 @@ * カラムの生成も行う場合は, $col_type も必須となる. * - * @param string $table_name テーブル名 + * @param string $$tableName テーブル名 * @param string $column_name カラム名 * @param string $col_type カラムのデータ型 @@ -65,5 +65,5 @@ * 引数 $add == false でカラムが存在しない場合 false */ - function sfColumnExists($table_name, $col_name, $col_type = '', $dsn = '', $add = false) + function sfColumnExists($tableName, $colName, $colType = '', $dsn = '', $add = false) { $dbFactory = SC_DB_DBFactory_Ex::getInstance(); @@ -73,12 +73,12 @@ // テーブルが無ければエラー - if (!in_array($table_name, $objQuery->listTables())) return false; + if (!in_array($tableName, $objQuery->listTables())) return false; // 正常に接続されている場合 if (!$objQuery->isError()) { // カラムリストを取得 - $columns = $objQuery->listTableFields($table_name); - - if (in_array($col_name, $columns)) { + $columns = $objQuery->listTableFields($tableName); + + if (in_array($colName, $columns)) { return true; } @@ -87,22 +87,24 @@ // カラムを追加する if ($add) { - $objQuery->query("ALTER TABLE $table_name ADD $col_name $col_type "); - return true; + return $this->sfColumnAdd($tableName, $colName, $colType); } return false; } + + function sfColumnAdd($tableName, $colName, $colType) { + $objQuery =& SC_Query_Ex::getSingletonInstance($dsn); + return $objQuery->query("ALTER TABLE $tableName ADD $colName $colType "); + } /** * データの存在チェックを行う. * - * @param string $table_name テーブル名 + * @param string $tableName テーブル名 * @param string $where データを検索する WHERE 句 - * @param string $dsn データソース名 - * @param string $sql @deprecated データの追加を行う場合の SQL文 - * @param bool $add @deprecated データの追加も行う場合 true + * @param array $arrWhereVal WHERE句のプレースホルダ値 * @return bool データが存在する場合 true, データの追加に成功した場合 true, * $add == false で, データが存在しない場合 false */ - function sfDataExists($table_name, $where, $arrWhereVal, $dsn = '', $sql = '', $add = false) + function sfDataExists($tableName, $where, $arrWhereVal) { $dbFactory = SC_DB_DBFactory_Ex::getInstance(); @@ -110,13 +112,9 @@ $objQuery =& SC_Query_Ex::getSingletonInstance(); - $exists = $objQuery->exists($table_name, $where, $arrWhereVal); + $exists = $objQuery->exists($tableName, $where, $arrWhereVal); // データが存在する場合 TRUE if ($exists) { return TRUE; - // $add が TRUE の場合はデータを追加する - } elseif ($add) { - return $objQuery->exec($sql); - // $add が FALSE で、データが存在しない場合 FALSE } else { return FALSE; @@ -1160,48 +1158,20 @@ $getWhere = "$keyIdColumn = ?"; } - $rank = $objQuery->get('rank', $tableName, $getWhere, array($keyId)); + $oldRank = $objQuery->get('rank', $tableName, $getWhere, array($keyId)); $max = $objQuery->max('rank', $tableName, $where); - - // 値の調整（逆順） - if ($pos > $max) { - $position = 1; - } else if ($pos < 1) { - $position = $max; - } else { - $position = $max - $pos + 1; - } - - //入れ替え先の順位が入れ換え元の順位より大きい場合 - if ($position > $rank) $term = 'rank - 1'; - - //入れ替え先の順位が入れ換え元の順位より小さい場合 - if ($position < $rank) $term = 'rank + 1'; - - // XXX 入れ替え先の順位が入れ替え元の順位と同じ場合 - if (!isset($term)) $term = 'rank'; - - // 指定した順位の商品から移動させる商品までのrankを１つずらす - $sqlval = array(); - $arrRawSql = array( - 'rank' => $term, - ); - $str_where = 'rank BETWEEN ? AND ?'; - if ($where != '') { - $str_where .= " AND $where"; - } - - if ($position > $rank) { - $arrWhereVal = array($rank + 1, $position); - $objQuery->update($tableName, $sqlval, $str_where, $arrWhereVal, $arrRawSql); - } - if ($position < $rank) { - $arrWhereVal = array($position, $rank - 1); - $objQuery->update($tableName, $sqlval, $str_where, $arrWhereVal, $arrRawSql); + + // 更新するランク値を取得 + $newRank = $this->getNewRank($pos, $max); + // 他のItemのランクを調整する + $ret = $this->moveOtherItemRank($newRank, $oldRank, $objQuery, $tableName, $where); + if (!$ret) { + // 他のランク変更がなければ処理を行わない + return; } // 指定した順位へrankを書き換える。 $sqlval = array( - 'rank' => $position, + 'rank' => $newRank, ); $str_where = "$keyIdColumn = ?"; @@ -1214,4 +1184,60 @@ $objQuery->commit(); } + + /** + * 指定された位置の値をDB用のRANK値に変換する + * 指定位置が1番目に移動なら、newRankは最大値 + * 指定位置が1番下へ移動なら、newRankは1 + * + * @param int $position 指定された位置 + * @param int $maxRank 現在のランク最大値 + * @return int $newRank DBに登録するRANK値 + */ + function getNewRank($position, $maxRank) { + + if ($position > $maxRank) { + $newRank = 1; + } else if ($position < 1) { + $newRank = $maxRank; + } else { + $newRank = $maxRank - $position + 1; + } + return $newRank; + } + + /** + * 指定した順位の商品から移動させる商品までのrankを１つずらす + * + * @param int $newRank + * @param int $oldRank + * @param object $objQuery + * @param string $where + * @return boolean + */ + function moveOtherItemRank($newRank, $oldRank, &$objQuery, $tableName, $addWhere) { + + $sqlval = array(); + $arrRawSql = array(); + $where = 'rank BETWEEN ? AND ?'; + if ($addWhere != '') { + $where .= " AND $Where"; + } + if ($newRank > $oldRank) { + //位置を上げる場合、他の商品の位置を1つ下げる（ランクを1下げる） + $arrRawSql['rank'] = 'rank - 1'; + $arrWhereVal = array($oldRank + 1, $newRank); + } else if ($newRank < $oldRank) { + //位置を下げる場合、他の商品の位置を1つ上げる（ランクを1上げる） + $arrRawSql['rank'] = 'rank + 1'; + $arrWhereVal = array($newRank, $oldRank - 1); + } else { + //入れ替え先の順位が入れ替え元の順位と同じ場合なにもしない + return false; + } + + return $objQuery->update($tableName, $sqlval, $where, $arrWhereVal, $arrRawSql); + + } + /**