TOOL更新_DBマネージャクラス。に、置き換えている。「AppDbFunctions」(DB関数群)クラス作成した。ここに更新する。
TOOL更新_DBマネージャクラス。に、置き換えている。「AppDbFunctions」(DB関数群)クラスを作った。まだ実験中なのでコーディング規約には沿っていないけど、徐々に、このページを直接直していくことにする。
★ver0.1で「db_functions.php」だったものを「AppDbFunctions.php」(DB関数群)にした。DBマネージャクラスに置き換えて。クラスにした。このクラスは「定義EXCEL」の内容から自動作成◎自動生成されるもの。
1.「定義EXCEL」各テーブルのシーケンスを返却する「getDbSeq」メソッド。シーケンスといってもいろいろな種類を用意している。例)”A+yymmdd+数字”など
2.指定レングスのパスワードを自動作成して戻す「makePassword」メソッド。
3.「定義EXCEL」各テーブルの読み込みメソッド。例)「logininfoGetRec」メソッド。パラメータ($db, &$o)として$oの中にPKの項目IDが入っているということを前提にDBを読んでいる。かつ、日付項目は"_year"、"_month"、"_day"を出すようにしている。たぶん使いたいときがあるだろうから。
★今回「logininfo」テーブルをサンプルにphp5.3に置き換えている。残念だがこのテーブルは「getDbSeq」メソッド、「makePassword」メソッドを使っていない。うまく動くことをとりあえず確かめるため「ControllerABaseExt」に以下を入れて動くことだけは確かめた。
<=ver0.1からphp5.3への置き換えで一番簡単だろうと「logininfo」テーブルを選んだが、そろそろ役不足になってきた。ってこと。だけど、このまま行くことにする。慣れてきたので。
★DbManager.phpとDbManagerForSeq.phpのコネクションが別になっていることも確認した。今日テストしたら昨日までのロジックだとどちらもDbManager.phpのシングルトンObjを見ていた。・・・・今修正した。ので、今回一緒にテストした。うまくいった。http://d.hatena.ne.jp/kazpgm/20100309/1268152282 (TOOL更新_DBマネージャクラス。うごいたので。ここに更新する。)にDbManager.phpとDbManagerForSeq.phpのソースあります。
★DbManagerForSeq.phpのコネクションはgetDbSeqメソッドだけで使うことにした。
// admin側Controller基底クラスを拡張したクラス abstract class ControllerABaseExt extends ControllerABase { ・・・ protected function _indexActionSub() { // 親クラスでabstractメソッドにしてある。親クラスのindexAction()から呼ばれる。 // DB begin $this->_dbBegin(); //<<ここから 確認用に入れたもの>> $test = AppDbFunctions::getDbSeq('m_user', $this->_o); $this->_logger->outDbgLog('m_user', $test); $test = AppDbFunctions::getDbSeq('t_corp', $this->_o); $this->_logger->outDbgLog('t_corp', $test); $test = AppDbFunctions::getDbSeq('t_item', $this->_o); $this->_logger->outDbgLog('t_item', $test); $test = AppDbFunctions::getDbSeq('t_xxxx', $this->_o); $this->_logger->outDbgLog('t_xxxx', $test); $this->_o['login_id']='admin01'; $test = AppDbFunctions::logininfoGetRec($this->_db, $this->_o); $this->_logger->outDbgLog('logininfoGetRec', $test); // DBコネクションが2つできていることの確認 $this->_db->beginTransaction(); $sql = "update m_seq set id = case when up_date = curdate() then (id + 1) else 1 end ,up_date =curdate() where table_id = ?"; $test = $this->_db->query($sql, array('t_item')); $this->_db->rollBack(); $test = AppDbFunctions::getDbSeq('t_xxxx', $this->_o); $this->_logger->outDbgLog('t_xxxx', $test); $this->_db->beginTransaction(); $sql = "update m_seq set id = case when up_date = curdate() then (id + 1) else 1 end ,up_date =curdate() where table_id = ?"; $test = $this->_db->query($sql, array('t_item')); $this->_db->rollBack(); $test = AppDbFunctions::getDbSeq('t_xxxx', $this->_o); $this->_logger->outDbgLog('t_xxxx', $test); $this->_db->beginTransaction(); $sql = "update m_seq set id = case when up_date = curdate() then (id + 1) else 1 end ,up_date =curdate() where table_id = ?"; $test = $this->_db->query($sql, array('t_item')); $this->_db->rollBack(); $test = AppDbFunctions::getDbSeq('t_item', $this->_o); $this->_logger->outDbgLog('t_item', $test); exit; //<<ここまで 確認用に入れたもの>> ・・・
★結果。ログ内容
◆20100608_debug.log 2010/06/08 22:35:37 DEBUG (7): m_user: ControllerABaseExt.php line=19 : A1006080003 2010/06/08 22:35:37 DEBUG (7): t_corp: ControllerABaseExt.php line=21 : A1006080003 2010/06/08 22:35:37 DEBUG (7): t_item: ControllerABaseExt.php line=23 : A1006080003 2010/06/08 22:35:37 DEBUG (7): t_xxxx: ControllerABaseExt.php line=25 : 14 2010/06/08 22:35:37 DEBUG (7): logininfoGetRec: ControllerABaseExt.php line=28 : Array ( [login_id] => admin01 [login_type] => 2 [pwd1] => admin02 [yuko_flg] => 2 [logininfo_insert] => 2010/03/12 01:08:13 [logininfo_update] => 2010/06/08 22:14:52 [logininfo_insert_year] => 2010 [logininfo_insert_month] => 03 [logininfo_insert_day] => 12 [logininfo_update_year] => 2010 [logininfo_update_month] => 06 [logininfo_update_day] => 08 ) 2010/06/08 22:35:37 DEBUG (7): t_xxxx: ControllerABaseExt.php line=36 : 15 2010/06/08 22:35:37 DEBUG (7): t_xxxx: ControllerABaseExt.php line=43 : 16 2010/06/08 22:35:37 DEBUG (7): t_item: ControllerABaseExt.php line=50 : A1006080004 <=$this->_db->query($sql, array('t_item'));が 3回入っているけど全部rollBackしているのでA1006080003から+1して A1006080004になっている。OK。 ◆20100605_sql.log ・△がDbManagerForSeq、●$this->_dbコネクション。 DbManager.phpとDbManagerForSeq.phpのコネクション別に表示している。 2010/06/08 22:35:37 DEBUG (7): [● uid beginTransaction ]: ControllerABase.php line=42 : begin 2010/06/08 22:35:37 DEBUG (7): [△ uid query ]: AppDbFunctions.php line=21 : lock tables m_seq write 2010/06/08 22:35:37 DEBUG (7): [△ uid beginTransaction ]: AppDbFunctions.php line=22 : begin 2010/06/08 22:35:37 DEBUG (7): [△ uid query ]: AppDbFunctions.php line=25 : update m_seq set id = case when up_date = curdate() then (id + 1) else 1 end ,up_date =curdate() where table_id = 'm_user' 2010/06/08 22:35:37 DEBUG (7): [△ uid getRow ]: AppDbFunctions.php line=31 : select concat(date_format(up_date, '%Y%m%d'), substring('0000000000000000000000', 1, 4+1-length(id)), id) as seq_id from m_seq where table_id = 'm_user' 2010/06/08 22:35:37 DEBUG (7): [△ uid commit ]: AppDbFunctions.php line=42 : commit 2010/06/08 22:35:37 DEBUG (7): [△ uid query ]: AppDbFunctions.php line=44 : unlock tables 2010/06/08 22:35:37 DEBUG (7): [△ uid query ]: AppDbFunctions.php line=50 : lock tables m_seq write 2010/06/08 22:35:37 DEBUG (7): [△ uid beginTransaction ]: AppDbFunctions.php line=51 : begin 2010/06/08 22:35:37 DEBUG (7): [△ uid query ]: AppDbFunctions.php line=55 : update m_seq set id = case when up_date = curdate() then (id + 1) else 1 end ,up_date =curdate() where table_id = 't_corp' 2010/06/08 22:35:37 DEBUG (7): [△ uid getRow ]: AppDbFunctions.php line=61 : select concat(date_format(up_date, '%Y%m%d'), substring('0000000000000000000000', 1, 4+1-length(id)), id) as seq_id from m_seq where table_id = 't_corp' 2010/06/08 22:35:37 DEBUG (7): [△ uid commit ]: AppDbFunctions.php line=72 : commit 2010/06/08 22:35:37 DEBUG (7): [△ uid query ]: AppDbFunctions.php line=74 : unlock tables 2010/06/08 22:35:37 DEBUG (7): [△ uid query ]: AppDbFunctions.php line=79 : lock tables m_seq write 2010/06/08 22:35:37 DEBUG (7): [△ uid beginTransaction ]: AppDbFunctions.php line=80 : begin 2010/06/08 22:35:37 DEBUG (7): [△ uid query ]: AppDbFunctions.php line=83 : update m_seq set id = case when up_date = curdate() then (id + 1) else 1 end ,up_date =curdate() where table_id = 't_item' 2010/06/08 22:35:37 DEBUG (7): [△ uid getRow ]: AppDbFunctions.php line=89 : select concat(date_format(up_date, '%Y%m%d'), substring('0000000000000000000000', 1, 4+1-length(id)), id) as seq_id from m_seq where table_id = 't_item' 2010/06/08 22:35:37 DEBUG (7): [△ uid commit ]: AppDbFunctions.php line=100 : commit 2010/06/08 22:35:37 DEBUG (7): [△ uid query ]: AppDbFunctions.php line=102 : unlock tables 2010/06/08 22:35:37 DEBUG (7): [△ uid query ]: AppDbFunctions.php line=107 : lock tables m_seq write 2010/06/08 22:35:37 DEBUG (7): [△ uid beginTransaction ]: AppDbFunctions.php line=108 : begin 2010/06/08 22:35:37 DEBUG (7): [△ uid query ]: AppDbFunctions.php line=111 : update m_seq set id = (id + 1) where table_id = 't_xxxx' 2010/06/08 22:35:37 DEBUG (7): [△ uid getRow ]: AppDbFunctions.php line=117 : select id as seq_id from m_seq where table_id = 't_xxxx' 2010/06/08 22:35:37 DEBUG (7): [△ uid commit ]: AppDbFunctions.php line=118 : commit 2010/06/08 22:35:37 DEBUG (7): [△ uid query ]: AppDbFunctions.php line=120 : unlock tables 2010/06/08 22:35:37 DEBUG (7): [● uid getRow ]: AppDbFunctions.php line=202 : SELECT -- ■--PGM<<005A>> start-------------------------------------------- l.*, date_format(l.logininfo_insert, '%Y/%m/%d %H:%i:%s') AS logininfo_insert, date_format(l.logininfo_insert, '%Y') AS logininfo_insert_year , date_format(l.logininfo_insert, '%m') AS logininfo_insert_month , date_format(l.logininfo_insert, '%d') AS logininfo_insert_day , date_format(l.logininfo_update, '%Y/%m/%d %H:%i:%s') AS logininfo_update, date_format(l.logininfo_update, '%Y') AS logininfo_update_year , date_format(l.logininfo_update, '%m') AS logininfo_update_month , date_format(l.logininfo_update, '%d') AS logininfo_update_day -- ■--PGM<<005A>> end-------------------------------------------- FROM m_logininfo l WHERE login_id = 'admin01' 2010/06/08 22:35:37 DEBUG (7): [● uid beginTransaction ]: ControllerABaseExt.php line=31 : begin 2010/06/08 22:35:37 DEBUG (7): [● uid query ]: ControllerABaseExt.php line=33 : update m_seq set id = case when up_date = curdate() then (id + 1) else 1 end ,up_date =curdate() where table_id = 't_item' 2010/06/08 22:35:37 DEBUG (7): [● uid rollBack ]: ControllerABaseExt.php line=34 : rollback 2010/06/08 22:35:37 DEBUG (7): [△ uid query ]: AppDbFunctions.php line=107 : lock tables m_seq write 2010/06/08 22:35:37 DEBUG (7): [△ uid beginTransaction ]: AppDbFunctions.php line=108 : begin 2010/06/08 22:35:37 DEBUG (7): [△ uid query ]: AppDbFunctions.php line=111 : update m_seq set id = (id + 1) where table_id = 't_xxxx' 2010/06/08 22:35:37 DEBUG (7): [△ uid getRow ]: AppDbFunctions.php line=117 : select id as seq_id from m_seq where table_id = 't_xxxx' 2010/06/08 22:35:37 DEBUG (7): [△ uid commit ]: AppDbFunctions.php line=118 : commit 2010/06/08 22:35:37 DEBUG (7): [△ uid query ]: AppDbFunctions.php line=120 : unlock tables 2010/06/08 22:35:37 DEBUG (7): [● uid beginTransaction ]: ControllerABaseExt.php line=38 : begin 2010/06/08 22:35:37 DEBUG (7): [● uid query ]: ControllerABaseExt.php line=40 : update m_seq set id = case when up_date = curdate() then (id + 1) else 1 end ,up_date =curdate() where table_id = 't_item' 2010/06/08 22:35:37 DEBUG (7): [● uid rollBack ]: ControllerABaseExt.php line=41 : rollback 2010/06/08 22:35:37 DEBUG (7): [△ uid query ]: AppDbFunctions.php line=107 : lock tables m_seq write 2010/06/08 22:35:37 DEBUG (7): [△ uid beginTransaction ]: AppDbFunctions.php line=108 : begin 2010/06/08 22:35:37 DEBUG (7): [△ uid query ]: AppDbFunctions.php line=111 : update m_seq set id = (id + 1) where table_id = 't_xxxx' 2010/06/08 22:35:37 DEBUG (7): [△ uid getRow ]: AppDbFunctions.php line=117 : select id as seq_id from m_seq where table_id = 't_xxxx' 2010/06/08 22:35:37 DEBUG (7): [△ uid commit ]: AppDbFunctions.php line=118 : commit 2010/06/08 22:35:37 DEBUG (7): [△ uid query ]: AppDbFunctions.php line=120 : unlock tables 2010/06/08 22:35:37 DEBUG (7): [● uid beginTransaction ]: ControllerABaseExt.php line=45 : begin 2010/06/08 22:35:37 DEBUG (7): [● uid query ]: ControllerABaseExt.php line=47 : update m_seq set id = case when up_date = curdate() then (id + 1) else 1 end ,up_date =curdate() where table_id = 't_item' 2010/06/08 22:35:37 DEBUG (7): [● uid rollBack ]: ControllerABaseExt.php line=48 : rollback 2010/06/08 22:35:37 DEBUG (7): [△ uid query ]: AppDbFunctions.php line=79 : lock tables m_seq write 2010/06/08 22:35:37 DEBUG (7): [△ uid beginTransaction ]: AppDbFunctions.php line=80 : begin 2010/06/08 22:35:37 DEBUG (7): [△ uid query ]: AppDbFunctions.php line=83 : update m_seq set id = case when up_date = curdate() then (id + 1) else 1 end ,up_date =curdate() where table_id = 't_item' 2010/06/08 22:35:37 DEBUG (7): [△ uid getRow ]: AppDbFunctions.php line=89 : select concat(date_format(up_date, '%Y%m%d'), substring('0000000000000000000000', 1, 4+1-length(id)), id) as seq_id from m_seq where table_id = 't_item' 2010/06/08 22:35:37 DEBUG (7): [△ uid commit ]: AppDbFunctions.php line=100 : commit 2010/06/08 22:35:37 DEBUG (7): [△ uid query ]: AppDbFunctions.php line=102 : unlock tables
■AppDbFunctions.php (DB関数群)
<?php // ============================================================= // 2010 kaz PHP自動作成お助けTOOL.(http://kazpgm.ddo.jp/) Start // 修正BSDライセンス。 // ============================================================= // DB関数群 class AppDbFunctions { // テーブル別にシーケンスを作成する。(いろいろなタイプのユニークキーを作る) // DBコネクションはこのメッソド専用の物を使う。(シーケンス取得後すぐにコミットするため) public function getDbSeq($tableId, &$o) { $DSN_ZEND = Zend_Registry::get('DSN_ZEND'); $db = DbManagerForSeq::getInstance($DSN_ZEND, 'uid'); // DB接続(シーケンス作成用に使用する。シングルトンです。) $elements = AppElements::getInstance(); $ELEMENTS = &$elements->elements; // コード一覧 switch($tableId) { case 'm_user': // user $sql = "lock tables m_seq write"; $db->query($sql); $db->beginTransaction(); // シーケンス (A+yymmdd+数字) $sql = "update m_seq set id = case when up_date = curdate() then (id + 1) else 1 end ,up_date =curdate() where table_id = '" . $tableId ."'"; $pdost = $db->query($sql); if (1 > $pdost->rowCount()) { // 新規 $sql = "insert into m_seq values (?, curdate(), 1)"; $db->query($sql, array($tableId)); } $sql = "select concat(date_format(up_date, '%Y%m%d'), substring('0000000000000000000000', 1, 4+1-length(id)), id) as seq_id from m_seq where table_id = '" . $tableId ."'"; $result =& $db->getRow($sql); if (substr($result['seq_id'], 8) > (integer)substr('9999999999999999999999', 1, 4)) { $sql = "unlock tables"; $db->query($sql); // sequence overエラー場合 throw new AppException("Message: m_user u_login_id sequence over A" . substr($result['seq_id'], 0+2, 8-2) . substr('9999999999999999999999', 1, 4) ." getDbSeq() error"); exit; } else { $result['seq_id'] = 'A' . substr($result['seq_id'], 0+2, 8-2) . substr($result['seq_id'], 9, 4); } $db->commit(); $sql = "unlock tables"; $db->query($sql); break; case 't_corp': // corp $sql = "lock tables m_seq write"; $db->query($sql); $db->beginTransaction(); // シーケンス (A+yymmdd+数字) $sql = "update m_seq set id = case when up_date = curdate() then (id + 1) else 1 end ,up_date =curdate() where table_id = ?"; $pdost = $db->query($sql, array($tableId)); if (1 > $pdost->rowCount()) { // 新規 $sql = "insert into m_seq values (?, curdate(), 1)"; $db->query($sql, array($tableId)); } $sql = "select concat(date_format(up_date, '%Y%m%d'), substring('0000000000000000000000', 1, 4+1-length(id)), id) as seq_id from m_seq where table_id = ?"; $result =& $db->getRow($sql, array($tableId)); if (substr($result['seq_id'], 8) > (integer)substr('9999999999999999999999', 1, 4)) { $sql = "unlock tables"; $db->query($sql); // sequence overエラー場合 throw new AppException("Message: t_corp corpcd sequence over A" . substr($result['seq_id'], 0+2, 8-2) . substr('9999999999999999999999', 1, 4) ." getDbSeq() error"); exit; } else { $result['seq_id'] = 'A' . substr($result['seq_id'], 0+2, 8-2) . substr($result['seq_id'], 9, 4); } $db->commit(); $sql = "unlock tables"; $db->query($sql); break; case 't_item': // item $sql = "lock tables m_seq write"; $db->query($sql); $db->beginTransaction(); // シーケンス (A+yymmdd+数字) $sql = "update m_seq set id = case when up_date = curdate() then (id + 1) else 1 end ,up_date =curdate() where table_id = ?"; $pdost = $db->query($sql, array($tableId)); if (1 > $pdost->rowCount()) { // 新規 $sql = "insert into m_seq values (?, curdate(), 1)"; $db->query($sql, array($tableId)); } $sql = "select concat(date_format(up_date, '%Y%m%d'), substring('0000000000000000000000', 1, 4+1-length(id)), id) as seq_id from m_seq where table_id = ?"; $result =& $db->getRow($sql, array($tableId)); if (substr($result['seq_id'], 8) > (integer)substr('9999999999999999999999', 1, 4)) { $sql = "unlock tables"; $db->query($sql); // sequence overエラー場合 throw new AppException("Message: t_item item01 sequence over A" . substr($result['seq_id'], 0+2, 8-2) . substr('9999999999999999999999', 1, 4) ." getDbSeq() error"); exit; } else { $result['seq_id'] = 'A' . substr($result['seq_id'], 0+2, 8-2) . substr($result['seq_id'], 9, 4); } $db->commit(); $sql = "unlock tables"; $db->query($sql); break; default: if ('' != $tableId) { $sql = "lock tables m_seq write"; $db->query($sql); $db->beginTransaction(); // シーケンス $sql = "update m_seq set id = (id + 1) where table_id = ?"; $pdost = $db->query($sql, array($tableId)); if (1 > $pdost->rowCount()) { // 新規 $sql = "insert into m_seq values (?, '0000/00/00 00:00:00', 1)"; $db->query($sql, array($tableId)); } $sql = "select id as seq_id from m_seq where table_id = ?"; $result =& $db->getRow($sql, array($tableId)); $db->commit(); $sql = "unlock tables"; $db->query($sql); } else { // tableId ignoreエラー場合 throw new AppException("Message: getDbSeq() error. tableId ignore"); exit; } } return $result['seq_id']; } // 指定レングスのパスワードを戻す public function makePassword($db, $alen = 8, $tblNm, $itemNm ) { $mPasswordChars = "abcdefghjkmnrstxyz2345679"; $chkFlg = false; while ($chkFlg == false) { $password = ""; for( $i=0; $i<$alen; $i++ ) { $password .= substr( $mPasswordChars, mt_rand(0, strlen($mPasswordChars)-1 ),1); } // パスワードがすでに使われているかをチェックする。 $sql = <<<__SQL__ SELECT $itemNm as pwd FROM $tblNm WHERE $itemNm = ? __SQL__; $result =& $db->getAll($sql,array($password)); if ( empty($result) ) { $chkFlg = true; return $password; } } } // seqをPKで読み込む public function seqGetRec($db, &$o) { $sql = <<<__SQL__ SELECT -- ■--PGM<<005A>> start-------------------------------------------- q.*, date_format(q.up_date, '%Y/%m/%d') AS up_date, date_format(q.up_date, '%Y') AS up_date_year , date_format(q.up_date, '%m') AS up_date_month , date_format(q.up_date, '%d') AS up_date_day -- ■--PGM<<005A>> end-------------------------------------------- FROM m_seq q WHERE table_id = ? __SQL__; $result =& $db->getRow($sql, array($o['table_id'])); return $result; } // logininfoをPKで読み込む public function logininfoGetRec($db, &$o) { $sql = <<<__SQL__ SELECT -- ■--PGM<<005A>> start-------------------------------------------- l.*, date_format(l.logininfo_insert, '%Y/%m/%d %H:%i:%s') AS logininfo_insert, date_format(l.logininfo_insert, '%Y') AS logininfo_insert_year , date_format(l.logininfo_insert, '%m') AS logininfo_insert_month , date_format(l.logininfo_insert, '%d') AS logininfo_insert_day , date_format(l.logininfo_update, '%Y/%m/%d %H:%i:%s') AS logininfo_update, date_format(l.logininfo_update, '%Y') AS logininfo_update_year , date_format(l.logininfo_update, '%m') AS logininfo_update_month , date_format(l.logininfo_update, '%d') AS logininfo_update_day -- ■--PGM<<005A>> end-------------------------------------------- FROM m_logininfo l WHERE login_id = ? __SQL__; $result =& $db->getRow($sql, array($o['login_id'])); return $result; } // userをPKで読み込む public function userGetRec($db, &$o) { $sql = <<<__SQL__ SELECT -- ■--PGM<<005A>> start-------------------------------------------- u.*, date_format(u.u_insert, '%Y/%m/%d %H:%i:%s') AS u_insert, date_format(u.u_insert, '%Y') AS u_insert_year , date_format(u.u_insert, '%m') AS u_insert_month , date_format(u.u_insert, '%d') AS u_insert_day , date_format(u.u_update, '%Y/%m/%d %H:%i:%s') AS u_update, date_format(u.u_update, '%Y') AS u_update_year , date_format(u.u_update, '%m') AS u_update_month , date_format(u.u_update, '%d') AS u_update_day -- ■--PGM<<005A>> end-------------------------------------------- FROM m_user u WHERE u_login_id = ? __SQL__; $result =& $db->getRow($sql, array($o['u_login_id'])); return $result; } // biztypeをPKで読み込む public function biztypeGetRec($db, &$o) { $sql = <<<__SQL__ SELECT -- ■--PGM<<005A>> start-------------------------------------------- bt.*, date_format(bt.deldt, '%Y/%m/%d %H:%i:%s') AS deldt, date_format(bt.deldt, '%Y') AS deldt_year , date_format(bt.deldt, '%m') AS deldt_month , date_format(bt.deldt, '%d') AS deldt_day , date_format(bt.insdt, '%Y/%m/%d %H:%i:%s') AS insdt, date_format(bt.insdt, '%Y') AS insdt_year , date_format(bt.insdt, '%m') AS insdt_month , date_format(bt.insdt, '%d') AS insdt_day , date_format(bt.updt, '%Y/%m/%d %H:%i:%s') AS updt, date_format(bt.updt, '%Y') AS updt_year , date_format(bt.updt, '%m') AS updt_month , date_format(bt.updt, '%d') AS updt_day -- ■--PGM<<005A>> end-------------------------------------------- FROM m_biztype bt WHERE biztype = ? __SQL__; $result =& $db->getRow($sql, array($o['biztype'])); return $result; } // corpをPKで読み込む public function corpGetRec($db, &$o) { $sql = <<<__SQL__ SELECT -- ■--PGM<<005A>> start-------------------------------------------- co.*, date_format(co.deldt, '%Y/%m/%d %H:%i:%s') AS deldt, date_format(co.deldt, '%Y') AS deldt_year , date_format(co.deldt, '%m') AS deldt_month , date_format(co.deldt, '%d') AS deldt_day , date_format(co.insdt, '%Y/%m/%d %H:%i:%s') AS insdt, date_format(co.insdt, '%Y') AS insdt_year , date_format(co.insdt, '%m') AS insdt_month , date_format(co.insdt, '%d') AS insdt_day , date_format(co.updt, '%Y/%m/%d %H:%i:%s') AS updt, date_format(co.updt, '%Y') AS updt_year , date_format(co.updt, '%m') AS updt_month , date_format(co.updt, '%d') AS updt_day -- ■--PGM<<005A>> end-------------------------------------------- FROM t_corp co WHERE corpcd = ? __SQL__; $result =& $db->getRow($sql, array($o['corpcd'])); return $result; } // itemをPKで読み込む public function itemGetRec($db, &$o) { $sql = <<<__SQL__ SELECT -- ■--PGM<<005A>> start-------------------------------------------- it.*, date_format(it.item10, '%Y/%m/%d') AS item10, date_format(it.item10, '%Y') AS item10_year , date_format(it.item10, '%m') AS item10_month , date_format(it.item10, '%d') AS item10_day , date_format(it.item11, '%Y/%m/%d') AS item11, date_format(it.item11, '%Y') AS item11_year , date_format(it.item11, '%m') AS item11_month , date_format(it.item11, '%d') AS item11_day , date_format(it.item53, '%Y/%m/%d %H:%i:%s') AS item53, date_format(it.item53, '%Y') AS item53_year , date_format(it.item53, '%m') AS item53_month , date_format(it.item53, '%d') AS item53_day , date_format(it.item54, '%Y/%m/%d %H:%i:%s') AS item54, date_format(it.item54, '%Y') AS item54_year , date_format(it.item54, '%m') AS item54_month , date_format(it.item54, '%d') AS item54_day , date_format(it.updt, '%Y/%m/%d %H:%i:%s') AS updt, date_format(it.updt, '%Y') AS updt_year , date_format(it.updt, '%m') AS updt_month , date_format(it.updt, '%d') AS updt_day -- ■--PGM<<005A>> end-------------------------------------------- FROM t_item it WHERE item01 = ? __SQL__; $result =& $db->getRow($sql, array($o['item01'])); return $result; } } // ============================================================= // 2010 kaz PHP自動作成お助けTOOL.(http://kazpgm.ddo.jp/) End // 修正BSDライセンス。 // =============================================================
03/15 23:30-02:00
06/05 11:00-15:30 DBマネージャのメソッドの今まで検討したものから、今回のものに置き換えた。
修正内容 抜粋
dbQuote($column),dbQuotes($column) −> 使わない。ユーザにはQuoteさせない。 ?を使ったsqlにして、 値を配列(以降の$ary=array()部分)で渡す様に修正した。 dbFetchAll($sql) −> getAll($sql, $ary=array()) dbFetchRow($sql) −> getRow($sql, $ary=array()) dbFetchOne($sql) −> getOne($sql, $ary=array()) dbInsert($tableName, $columns, $conditions = array()), dbUpdate($tableName, $columns, $conditions = array()), dbDelete($tableName, $conditions = array()), dbQuery($sql) −> query($sql, $ary=array())1つでやる。 その他。beginTransaction();、commit();を使う。 <<修正前>> $sql = "begin"; $db->query($sql); <<修正後>> $db->beginTransaction(); <<修正前>> $sql = "commit"; $db->query($sql); <<修正後>> $db->commit(); <<修正前>> $sql = "insert into m_seq values ('" . $tableId . "', curdate(), 1)"; $db->query($sql); <<修正後>> $sql = "insert into m_seq values (?, curdate(), 1)"; $db->query($sql, array($tableId)); <<修正前>> $pwd = $db->dbQuote($password); // パスワードがすでに使われているかをチェックする。 $sql = <<<__SQL__ SELECT $itemNm as pwd FROM $tblNm WHERE $itemNm = $pwd __SQL__; $result =& $db->dbFetchAll($sql); <<修正後>> // パスワードがすでに使われているかをチェックする。 $sql = <<<__SQL__ SELECT $itemNm as pwd FROM $tblNm WHERE $itemNm = ? __SQL__; $result =& $db->getAll($sql, array($password)); <<修正前>> // seqをPKで読み込む public function seqGetRec($db, &$o) { $table_id = $db->dbQuote($o['table_id']); $sql = <<<__SQL__ SELECT -- ■--PGM<<005A>> start-------------------------------------------- q.*, date_format(q.up_date, '%Y/%m/%d') AS up_date, date_format(q.up_date, '%Y') AS up_date_year , date_format(q.up_date, '%m') AS up_date_month , date_format(q.up_date, '%d') AS up_date_day -- ■--PGM<<005A>> end-------------------------------------------- FROM m_seq q WHERE table_id = $table_id __SQL__; $result =& $db->dbFetchRow($sql); return $result; } <<修正後>> // seqをPKで読み込む public function seqGetRec($db, &$o) { $sql = <<<__SQL__ SELECT -- ■--PGM<<005A>> start-------------------------------------------- q.*, date_format(q.up_date, '%Y/%m/%d') AS up_date, date_format(q.up_date, '%Y') AS up_date_year , date_format(q.up_date, '%m') AS up_date_month , date_format(q.up_date, '%d') AS up_date_day -- ■--PGM<<005A>> end-------------------------------------------- FROM m_seq q WHERE table_id = ? __SQL__; $result =& $db->getRow($sql, array($o['table_id'])); return $result; }
6/8 21:30-23:00 シーケンス作成用DBコネクションを getDbSeqメソッドに直接書いた。
// DB関数群 class AppDbFunctions { <<修正前>> // テーブル別にシーケンスを作成する。(いろいろなタイプのユニークキーを作る) public function getDbSeq($db, $tableId, &$o) { $elements = AppElements::getInstance(); <<修正後>> // テーブル別にシーケンスを作成する。(いろいろなタイプのユニークキーを作る) // DBコネクションはこのメッソド専用の物を使う。(シーケンス取得後すぐにコミットするため) public function getDbSeq($tableId, &$o) { $DSN_ZEND = Zend_Registry::get('DSN_ZEND'); $db = DbManagerForSeq::getInstance($DSN_ZEND, 'uid'); // DB接続(シーケンス作成用に使用する。) $elements = AppElements::getInstance();