- Timestamp:
- 2007/08/07 21:09:16 (17 years ago)
- Location:
- branches/feature-module-update/data/class/db
- Files:
-
- 3 edited
Legend:
- Unmodified
- Added
- Removed
-
branches/feature-module-update/data/class/db/SC_DB_DBFactory.php
r15123 r15233 7 7 8 8 // {{{ requires 9 require_once( CLASS_PATH . "db/dbfactory/SC_DB_DBFactory_MYSQL.php");10 require_once( CLASS_PATH . "db/dbfactory/SC_DB_DBFactory_PGSQL.php");9 require_once($include_dir . "/../data/class/db/dbfactory/SC_DB_DBFactory_MYSQL.php"); 10 require_once($include_dir . "/../data/class/db/dbfactory/SC_DB_DBFactory_PGSQL.php"); 11 11 12 12 /** … … 17 17 * @version $Id$ 18 18 */ 19 class SC_D b_DBFactory {19 class SC_DB_DBFactory { 20 20 21 21 /** … … 37 37 } 38 38 } 39 40 /** 41 * データソース名を取得する. 42 * 43 * 引数 $dsn が空の場合は, DEFAULT_DSN の値を返す. 44 * DEFAULT_DSN が未定義の場合は void となる. 45 * $dsn が空ではない場合は, $dsn の値を返す. 46 * 47 * @param string $dsn データソース名 48 * @return void|string データソース名 49 */ 50 function getDSN($dsn = "") { 51 if(empty($dsn)) { 52 if(defined('DEFAULT_DSN')) { 53 $dsn = DEFAULT_DSN; 54 } else { 55 return; 56 } 57 } 58 return $dsn; 59 } 60 61 /** 62 * DBのバージョンを取得する. 63 * 64 * @param string $dsn データベース接続詞 65 */ 66 function sfGetDBVersion($dsn = "") {} 67 68 /** 69 * MySQL 用の SQL 文に変更する. 70 * 71 * @param string $sql SQL 文 72 * @return string MySQL 用に置換した SQL 文 73 */ 74 function sfChangeMySQL($sql) {} 39 75 } 40 76 ?> -
branches/feature-module-update/data/class/db/dbfactory/SC_DB_DBFactory_MYSQL.php
r15123 r15233 7 7 8 8 // {{{ requires 9 require_once( CLASS_PATH . "db/SC_DB_DBFactory.php");9 require_once($include_dir . "/../data/class/db/SC_DB_DBFactory.php"); // FIXME 10 10 11 11 /** … … 22 22 class SC_DB_DBFactory_MYSQL extends SC_DB_DBFactory { 23 23 24 /** 25 * DBのバージョンを取得する. 26 * 27 * @param string $dsn データソース名 28 * @return string データベースのバージョン 29 */ 30 function sfGetDBVersion($dsn = "") { 31 $objQuery = new SC_Query($this->getDSN($dsn), true, true); 32 list($db_type) = split(":", $dsn); 33 $val = $objQuery->getOne("select version()"); 34 return "MySQL " . $val; 35 } 36 37 /** 38 * MySQL 用の SQL 文に変更する. 39 * 40 * @access private 41 * @param string $sql SQL 文 42 * @return string MySQL 用に置換した SQL 文 43 */ 44 function sfChangeMySQL($sql){ 45 // 改行、タブを1スペースに変換 46 $sql = preg_replace("/[\r\n\t]/"," ",$sql); 47 // view表をインラインビューに変換する 48 $sql = $this->sfChangeView($sql); 49 // ILIKE検索をLIKE検索に変換する 50 $sql = $this->sfChangeILIKE($sql); 51 // RANDOM()をRAND()に変換する 52 $sql = $this->sfChangeRANDOM($sql); 53 return $sql; 54 } 55 56 /** 57 * SQL の中の View の存在をチェックする. 58 * 59 * @access private 60 * @param string $sql SQL 文 61 * @return bool Viewが存在しない場合 false 62 */ 63 function sfInArray($sql){ 64 $arrView = $this->viewToSubQuery(); 65 66 foreach($arrView as $key => $val){ 67 if (strcasecmp($sql, $val) == 0){ 68 $changesql = eregi_replace("($key)", "$val", $sql); 69 $this->sfInArray($changesql); 70 } 71 } 72 return false; 73 } 74 75 /** 76 * View をインラインビューに変換する. 77 * 78 * @access private 79 * @param string $sql SQL 文 80 * @return string インラインビューに変換した SQL 文 81 */ 82 function sfChangeView($sql){ 83 84 $arrViewTmp = $this->viewToSubQuery(); 85 86 // viewのwhereを変換 87 foreach($arrViewTmp as $key => $val){ 88 $arrViewTmp[$key] = strtr($arrViewTmp[$key], $this->getWhereConverter()); 89 } 90 91 // viewを変換 92 $changesql = strtr($sql, $arrViewTmp); 93 94 return $changesql; 95 } 96 97 /** 98 * ILIKE句 を LIKE句へ変換する. 99 * 100 * @access private 101 * @param string $sql SQL文 102 * @return string 変換後の SQL 文 103 */ 104 function sfChangeILIKE($sql){ 105 $changesql = eregi_replace("(ILIKE )", "LIKE BINARY ", $sql); 106 return $changesql; 107 } 108 109 /** 110 * RANDOM() を RAND() に変換する. 111 * 112 * @access private 113 * @param string $sql SQL文 114 * @return string 変換後の SQL 文 115 */ 116 function sfChangeRANDOM($sql){ 117 $changesql = eregi_replace("( RANDOM)", " RAND", $sql); 118 return $changesql; 119 } 120 121 /** 122 * View の WHERE 句を置換する. 123 * 124 * @access private 125 * @param string $target 置換対象の文字列 126 * @param string $where 置換する文字列 127 * @param array $arrval WHERE 句の要素の配列 128 * @param string $option SQL 文の追加文字列 129 * @return string 置換後の SQL 文 130 */ 131 function sfViewWhere($target, $where = "", $arrval = array(), $option = ""){ 132 global $arrViewWhere; 133 $arrWhere = split("[?]", $where); 134 $where_tmp = " WHERE " . $arrWhere[0]; 135 for($i = 1; $i < count($arrWhere); $i++){ 136 $where_tmp .= SC_Utils_Ex::sfQuoteSmart($arrval[$i - 1]) . $arrWhere[$i]; 137 } 138 $arrViewWhere[$target] = $where_tmp . " " . $option; 139 } 140 141 /** 142 * WHERE 句置換用の配列を返す. 143 * 144 * @access private 145 * @return array WHERE 句置換用の配列 146 */ 147 function getWhereConverter() { 148 return array( 149 "&&crscls_where&&" => "", 150 "&&crsprdcls_where&&" =>"", 151 "&&noncls_where&&" => "", 152 "&&allcls_where&&" => "", 153 "&&allclsdtl_where&&" => "", 154 "&&prdcls_where&&" => "", 155 "&&catcnt_where&&" => "" 156 ); 157 } 158 159 /** 160 * View をサブクエリに変換するための配列を返す. 161 * 162 * @access private 163 * @return array View をサブクエリに変換するための配列 164 */ 165 function viewToSubQuery() { 166 return array( 167 "vw_cross_class" => ' 168 (SELECT T1.class_id AS class_id1, T2.class_id AS class_id2, T1.classcategory_id AS classcategory_id1, T2.classcategory_id AS classcategory_id2, T1.name AS name1, T2.name AS name2, T1.rank AS rank1, T2.rank AS rank2 169 FROM dtb_classcategory AS T1, dtb_classcategory AS T2 ) ', 170 171 "vw_cross_products_class" =>' 172 (SELECT T1.class_id1, T1.class_id2, T1.classcategory_id1, T1.classcategory_id2, T2.product_id, 173 T1.name1, T1.name2, T2.product_code, T2.stock, T2.price01, T2.price02, T1.rank1, T1.rank2 174 FROM (SELECT T1.class_id AS class_id1, T2.class_id AS class_id2, T1.classcategory_id AS classcategory_id1, T2.classcategory_id AS classcategory_id2, T1.name AS name1, T2.name AS name2, T1.rank AS rank1, T2.rank AS rank2 175 FROM dtb_classcategory AS T1, dtb_classcategory AS T2 ) AS T1 LEFT JOIN dtb_products_class AS T2 176 ON T1.classcategory_id1 = T2.classcategory_id1 AND T1.classcategory_id2 = T2.classcategory_id2) ', 177 178 "vw_products_nonclass" => ' 179 (SELECT 180 T1.product_id, 181 T1.name, 182 T1.deliv_fee, 183 T1.sale_limit, 184 T1.sale_unlimited, 185 T1.category_id, 186 T1.rank, 187 T1.status, 188 T1.product_flag, 189 T1.point_rate, 190 T1.comment1, 191 T1.comment2, 192 T1.comment3, 193 T1.comment4, 194 T1.comment5, 195 T1.comment6, 196 T1.file1, 197 T1.file2, 198 T1.file3, 199 T1.file4, 200 T1.file5, 201 T1.file6, 202 T1.main_list_comment, 203 T1.main_list_image, 204 T1.main_comment, 205 T1.main_image, 206 T1.main_large_image, 207 T1.sub_title1, 208 T1.sub_comment1, 209 T1.sub_image1, 210 T1.sub_large_image1, 211 T1.sub_title2, 212 T1.sub_comment2, 213 T1.sub_image2, 214 T1.sub_large_image2, 215 T1.sub_title3, 216 T1.sub_comment3, 217 T1.sub_image3, 218 T1.sub_large_image3, 219 T1.sub_title4, 220 T1.sub_comment4, 221 T1.sub_image4, 222 T1.sub_large_image4, 223 T1.sub_title5, 224 T1.sub_comment5, 225 T1.sub_image5, 226 T1.sub_large_image5, 227 T1.sub_title6, 228 T1.sub_comment6, 229 T1.sub_image6, 230 T1.sub_large_image6, 231 T1.del_flg, 232 T1.creator_id, 233 T1.create_date, 234 T1.update_date, 235 T1.deliv_date_id, 236 T2.product_id_sub, 237 T2.product_code, 238 T2.price01, 239 T2.price02, 240 T2.stock, 241 T2.stock_unlimited, 242 T2.classcategory_id1, 243 T2.classcategory_id2 244 FROM (SELECT * FROM dtb_products &&noncls_where&&) AS T1 LEFT JOIN 245 (SELECT 246 product_id AS product_id_sub, 247 product_code, 248 price01, 249 price02, 250 stock, 251 stock_unlimited, 252 classcategory_id1, 253 classcategory_id2 254 FROM dtb_products_class WHERE classcategory_id1 = 0 AND classcategory_id2 = 0) 255 AS T2 256 ON T1.product_id = T2.product_id_sub) ', 257 258 "vw_products_allclass" => ' 259 (SELECT 260 product_id, 261 product_code_min, 262 product_code_max, 263 price01_min, 264 price01_max, 265 price02_min, 266 price02_max, 267 stock_min, 268 stock_max, 269 stock_unlimited_min, 270 stock_unlimited_max, 271 del_flg, 272 status, 273 name, 274 comment1, 275 comment2, 276 comment3, 277 rank, 278 main_list_comment, 279 main_image, 280 main_list_image, 281 product_flag, 282 deliv_date_id, 283 sale_limit, 284 point_rate, 285 sale_unlimited, 286 create_date, 287 deliv_fee 288 ,(SELECT rank AS category_rank FROM dtb_category AS T4 WHERE T1.category_id = T4.category_id) as category_rank 289 ,(SELECT category_id AS sub_category_id FROM dtb_category T4 WHERE T1.category_id = T4.category_id) as category_id 290 FROM 291 dtb_products AS T1 RIGHT JOIN (SELECT product_id AS product_id_sub, MIN(product_code) AS product_code_min, MAX(product_code) AS product_code_max, MIN(price01) AS price01_min, MAX(price01) AS price01_max, MIN(price02) AS price02_min, MAX(price02) AS price02_max, MIN(stock) AS stock_min, MAX(stock) AS stock_max, MIN(stock_unlimited) AS stock_unlimited_min, MAX(stock_unlimited) AS stock_unlimited_max FROM dtb_products_class GROUP BY product_id) AS T2 ON T1.product_id = T2.product_id_sub 292 ) ', 293 294 "vw_products_allclass_detail" => ' 295 (SELECT product_id,price01_min,price01_max,price02_min,price02_max,stock_min,stock_max,stock_unlimited_min,stock_unlimited_max, 296 del_flg,status,name,comment1,comment2,comment3,deliv_fee,main_comment,main_image,main_large_image, 297 sub_title1,sub_comment1,sub_image1,sub_large_image1, 298 sub_title2,sub_comment2,sub_image2,sub_large_image2, 299 sub_title3,sub_comment3,sub_image3,sub_large_image3, 300 sub_title4,sub_comment4,sub_image4,sub_large_image4, 301 sub_title5,sub_comment5,sub_image5,sub_large_image5, 302 product_flag,deliv_date_id,sale_limit,point_rate,sale_unlimited,file1,file2,category_id 303 FROM ( SELECT * FROM (dtb_products AS T1 RIGHT JOIN 304 (SELECT 305 product_id AS product_id_sub, 306 MIN(price01) AS price01_min, 307 MAX(price01) AS price01_max, 308 MIN(price02) AS price02_min, 309 MAX(price02) AS price02_max, 310 MIN(stock) AS stock_min, 311 MAX(stock) AS stock_max, 312 MIN(stock_unlimited) AS stock_unlimited_min, 313 MAX(stock_unlimited) AS stock_unlimited_max 314 FROM dtb_products_class GROUP BY product_id) AS T2 315 ON T1.product_id = T2.product_id_sub ) ) AS T3 LEFT JOIN (SELECT rank AS category_rank, category_id AS sub_category_id FROM dtb_category) AS T4 316 ON T3.category_id = T4.sub_category_id) ', 317 318 "vw_product_class" => ' 319 (SELECT * FROM 320 (SELECT T3.product_class_id, T3.product_id AS product_id_sub, classcategory_id1, classcategory_id2, 321 T3.rank AS rank1, T4.rank AS rank2, T3.class_id AS class_id1, T4.class_id AS class_id2, 322 stock, price01, price02, stock_unlimited, product_code 323 FROM ( SELECT 324 T1.product_class_id, 325 T1.product_id, 326 classcategory_id1, 327 classcategory_id2, 328 T2.rank, 329 T2.class_id, 330 stock, 331 price01, 332 price02, 333 stock_unlimited, 334 product_code 335 FROM (dtb_products_class AS T1 LEFT JOIN dtb_classcategory AS T2 336 ON T1.classcategory_id1 = T2.classcategory_id)) 337 AS T3 LEFT JOIN dtb_classcategory AS T4 338 ON T3.classcategory_id2 = T4.classcategory_id) AS T5 LEFT JOIN dtb_products AS T6 339 ON product_id_sub = T6.product_id) ', 340 341 "vw_category_count" => ' 342 (SELECT T1.category_id, T1.category_name, T1.parent_category_id, T1.level, T1.rank, T2.product_count 343 FROM dtb_category AS T1 LEFT JOIN dtb_category_total_count AS T2 344 ON T1.category_id = T2.category_id) ' 345 ); 346 } 24 347 } 25 348 ?> -
branches/feature-module-update/data/class/db/dbfactory/SC_DB_DBFactory_PGSQL.php
r15123 r15233 8 8 9 9 // {{{ requires 10 require_once( CLASS_PATH . "db/SC_DB_DBFactory.php");10 require_once($include_dir . "/../data/class/db/SC_DB_DBFactory.php"); // FIXME 11 11 12 12 /** … … 23 23 class SC_DB_DBFactory_PGSQL extends SC_DB_DBFactory { 24 24 25 /** 26 * DBのバージョンを取得する. 27 * 28 * @param string $dsn データソース名 29 * @return string データベースのバージョン 30 */ 31 function sfGetDBVersion($dsn = "") { 32 $objQuery = new SC_Query($this->getDSN($dsn), true, true); 33 list($db_type) = split(":", $dsn); 34 $val = $objQuery->getOne("select version()"); 35 $arrLine = split(" " , $val); 36 return $arrLine[0] . " " . $arrLine[1]; 37 } 38 39 /** 40 * MySQL 用の SQL 文に変更する. 41 * 42 * DB_TYPE が PostgreSQL の場合は何もしない 43 * 44 * @access private 45 * @param string $sql SQL 文 46 * @return string MySQL 用に置換した SQL 文 47 */ 48 function sfChangeMySQL($sql){ 49 return $sql; 50 } 25 51 } 26 52 ?>
Note: See TracChangeset
for help on using the changeset viewer.