Skip to content

Instantly share code, notes, and snippets.

@nanasess

nanasess/SC_QueryPdoImpl.php

Created February 24, 2017 00:52
Show Gist options
  • Save nanasess/42d86e689489f529f313ec0483aab86c to your computer and use it in GitHub Desktop.
Save nanasess/42d86e689489f529f313ec0483aab86c to your computer and use it in GitHub Desktop.
Download ZIP
Raw
SC_QueryPdoImpl.php
<?php // -*- coding:utf-8 -*-
/**
* SC_Query の PDO 版実装クラス.
*
* このクラスは直接インスタンス化せず, SC_QueryFactory 経由でインスタンス化する.
*
* @version $Id$
*/
class SC_QueryPDOImpl implements SC_Query {
protected $pdo;
protected $limit;
protected $offset;
var $option;
var $where;
var $conn;
var $groupby;
var $order;
var $dsn;
/**
* 最後に実行されたSQL文
* @var string SQL文
*/
var $lastsql;
/**
* 接続の有無
* @var bool true:接続されている false:接続されていない
*/
var $connected = false;
/**
* 接続エラー
* @var PDOException
*/
var $connError;
/**
* コンストラクタ.
*/
private function __construct() {
// queit.
}
public function __clone() {
trigger_error('Clone is not allowed.', E_USER_ERROR);
}
/**
* PDO を利用してインスタンスを生成する.
*
* @param array データベース接続情報の配列
* @return SC_Query SC_Query インスタンス
*/
public static function getInstance($dsn) {
$clazz = __CLASS__;
$instance = new $clazz;
$instance->getPDO($dsn);
$instance->dsn = $dsn;
return $instance;
}
/**
* エラーの有無をチェックする.
*
* @deprecated PDOException を利用して下さい.
* @return boolean
*/
function isError() {
// TODO
trigger_error("DEPRECATED ERROR!", E_USER_ERROR);
return false;
}
/**
* カウント文を実行する.
*
* @param string $table テーブル名
* @param string $where WHERE 句
* @param array $arrval パラメータ値の配列
* @return integer 実行したカウント文の値
*/
function count($table, $where = "", $arrval = array()) {
if(strlen($where) <= 0) {
$sqlse = "SELECT COUNT(*) FROM $table";
} else {
$sqlse = "SELECT COUNT(*) FROM $table WHERE $where";
}
$this->lastsql = $sqlse;
$ret = $this->getOne($sqlse, $arrval);
return $ret;
}
/**
* SELECT 文を実行する.
*
* @return array 実行結果の配列
*/
function select($col, $table, $where = "", $arrval = array()){
$sqlse = $this->getsql($col, $table, $where);
$this->lastsql = $sqlse;
$cursorName = uniqid("q");
$statement = $this->pdo->prepare("DECLARE " . $cursorName . " SCROLL CURSOR FOR " . $sqlse);
$statement->execute($arrval);
if ($this->offset > 0) {
$statement = $this->pdo->prepare("MOVE FORWARD " . $this->offset . " IN " .$cursorName);
$statement->execute();
}
if ($this->limit > 0) {
$statement = $this->pdo->prepare("FETCH FORWARD " . $this->limit . " FROM " . $cursorName);
} else {
$statement = $this->pdo->prepare("FETCH FORWARD ALL FROM " . $cursorName);
}
$statement->execute();
return $statement->fetchAll(PDO::FETCH_ASSOC);
}
function selectAsCSV($col, $table, $where = "", $arrval = array(), $header = "") {
$sqlse = $this->getsql($col, $table, $where);
$this->lastsql = $sqlse;
$cursorName = uniqid("q");
$statement = $this->pdo->prepare("DECLARE " . $cursorName . " SCROLL CURSOR FOR " . $sqlse);
$statement->execute($arrval);
if ($this->offset > 0) {
$statement = $this->pdo->prepare("MOVE FORWARD " . $this->offset . " IN " .$cursorName);
$statement->execute();
}
if ($this->limit > 0) {
$statement = $this->pdo->prepare("FETCH FORWARD " . $this->limit . " FROM " . $cursorName);
} else {
$statement = $this->pdo->prepare("FETCH FORWARD ALL FROM " . $cursorName);
}
$fiest_name = date("YmdHis") .".csv";
// 実行時間を制限しない
set_time_limit(0);
// IE用に空白を出力
echo str_pad('',256);
// HTTPヘッダの出力
Header("Content-disposition: attachment; filename=" . $fiest_name);
Header("Content-type: application/octet-stream; name=" . $fiest_name);
// IE+SSLでダウンロードが失敗する不具合の対応
header('Pragma: 1');
header('Cache-control: private, max-age=60, pre-check=30');
session_cache_limiter('private_no_expire');
// 出力をバッファリングしない(==日本語自動変換もしない)
ob_end_clean();
$statement->execute();
$data ="";
if($header != "") {
$new_header = preg_replace("/(\r|\n)$/", "", $header);
$new_header .= "\r\n";
echo mb_convert_encoding($new_header, 'SJIS-win');
flush();
ob_flush();
}
while ($data = $statement->fetch(PDO::FETCH_NUM)) {
$csv = implode(",", $data);
$csv .= "\r\n";
echo mb_convert_encoding($csv, 'SJIS-win');
flush();
ob_flush();
}
}
/**
* 最後に実行した SQL 文を取得する.
*
* @deprecated
* @return string 最後に実行した SQL 文
*/
function getLastQuery($disp = true) {
trigger_error("DEPRECATED ERROR!", E_USER_ERROR);
return $this->lastsql;
}
/**
* コミット処理を行う.
*
* @return void
*/
function commit() {
$this->pdo->commit();
}
/**
* トランザクションを開始する.
*
* @return void
*/
function begin() {
$this->pdo->beginTransaction();
}
/**
* トランザクションをロールバックする.
*
* @return void
*/
function rollback() {
$this->pdo->rollBack();
}
/**
* 指定した SQL を実行する.
*
* @return void
*/
function exec($str, $arrval = array()) {
$this->lastsql = $str;
$statement = $this->pdo->prepare($str);
$statement->execute($arrval);
}
/**
* SQLを実行し, statementオブジェクトを返します.
*
* @param string $sql sql文
* @return PDOStatement
*/
public function prepare($sql) {
$this->lastsql = $sql;
return $this->pdo->prepare($sql);
}
/**
* SELECT 文を生成して実行する.
*
* @return array 実行結果の配列
*/
function autoselect($col, $table, $arrwhere = array(), $arrcon = array()) {
$strw = "";
$find = false;
foreach ($arrwhere as $key => $val) {
if(strlen($val) > 0) {
if(strlen($strw) <= 0) {
$strw .= $key ." LIKE ?";
} else if(strlen($arrcon[$key]) > 0) {
$strw .= " ". $arrcon[$key]. " " . $key ." LIKE ?";
} else {
$strw .= " AND " . $key ." LIKE ?";
}
$arrval[] = $val;
}
}
if(strlen($strw) > 0) {
$sqlse = "SELECT $col FROM $table WHERE $strw ".$this->option;
} else {
$sqlse = "SELECT $col FROM $table ".$this->option;
}
$this->lastsql = $sqlse;
$ret = $this->getAll($sqlse, $arrval);
return $ret;
}
/**
* 指定したSQLを実行し, すべての結果を返します.
*
* @param string SQL文
* @param array プレースホルダのパラメータ.
* @return array 結果を配列で返します. 結果がない場合は空の配列を返します.
*/
function getAll($sql, $arrval = array()) {
$this->lastsql = $sql;
$statement = $this->pdo->prepare($sql);
if (empty($arrval)) {
$statement->execute();
} else {
$statement->execute($arrval);
}
return $statement->fetchAll(PDO::FETCH_ASSOC);
}
/**
* 指定した SQL を実行し, 最初の1件を返します.
*
* 影響箇所が広すぎるため, SC_QueryPdoImplのみの実装です.
*
* @param string SQL文
* @param array プレースホルダのパラメータ.
* @return array 最初の1件の連想配列. 結果がない場合はnullを返します.
*/
function getSingleResult($sql, $arrval = array()) {
$this->lastsql = $sql;
$statement = $this->pdo->prepare($sql);
if (empty($arrval)) {
$statement->execute();
} else {
$statement->execute($arrval);
}
$results = $statement->fetchAll(PDO::FETCH_ASSOC);
return isset($results[0]) ? $results[0] : null;
}
/**
* 指定したパラメータから SELECT 文を生成する.
*
* @return string SELECT 文
*/
function getsql($col, $table, $where) {
if($where != "") {
$sqlse = "SELECT $col FROM $table WHERE $where " . $this->groupby . " " . $this->order . " " . $this->option;
} else {
if($this->where != "") {
$sqlse = "SELECT $col FROM $table WHERE $this->where " . $this->groupby . " " . $this->order . " " . $this->option;
} else {
$sqlse = "SELECT $col FROM $table " . $this->groupby . " " . $this->order . " " . $this->option;
}
}
return $sqlse;
}
/**
* SQL の追加文字列を設定する.
*
* @return void
*/
function setoption($str) {
$this->option = $str;
return $this;
}
/**
* SQL の追加文字列の設定を削除する
*
* @return void
*/
function unsetoption() {
$this->option = "";
return $this;
}
/**
* SQL の 表示件数を設定する
*
* @return mixed
*/
function setlimitoffset($limit, $offset = 0, $return = false) {
if (is_numeric($limit) && is_numeric($offset)){
$this->limit = $limit;
$this->offset = $offset;
}
return $this;
}
/**
* SQL の GROUP BY 句を設定する.
*
* @return void
*/
function setgroupby($str) {
$this->groupby = "GROUP BY " . $str;
return $this;
}
/**
* SQL の WHERE 句(AND) を設定する.
*
* @return void
*/
function andwhere($str) {
if($this->where != "") {
$this->where .= " AND " . $str;
} else {
$this->where = $str;
}
}
/**
* SQL の WHERE 句(OR) を設定する.
*
* @return void
*/
function orwhere($str) {
if($this->where != "") {
$this->where .= " OR " . $str;
} else {
$this->where = $str;
}
}
/**
* SQL の WHERE 句(任意) を設定する.
*
* @return void
*/
function setwhere($str) {
$this->where = $str;
return $this;
}
/**
* SQL の ORDER BY 句を設定する.
*
* @return void
*/
function setorder($str) {
$this->order = " ORDER BY " . $str;
return $this;
}
/**
* SQL の LIMIT 句を設定する.
*/
function setlimit($limit){
if ( is_numeric($limit)){
$this->limit = $limit;
}
return $this;
}
/**
* SQL の OFFSET 句を設定する.
*/
function setoffset($offset) {
if ( is_numeric($offset)){
$this->offset = $offset;
}
return $this;
}
/**
* INSERT 文を生成し, 実行する.
*/
function insert($table, $sqlval) {
$strcol = '';
$strval = '';
$find = false;
if(count($sqlval) <= 0 ) return false;
foreach ($sqlval as $key => $val) {
$strcol .= $key . ',';
if(preg_match("/^Now\(\)$/i", $val)) {
$strval .= 'Now(),';
} else if(preg_match("/^CURRENT\_TIMESTAMP$/i", $val)) {
$strval .= 'CURRENT_TIMESTAMP,';
} else {
$strval .= '?,';
if($val != ""){
$arrval[] = $val;
} else {
$arrval[] = NULL;
}
}
$find = true;
}
if(!$find) {
return false;
}
$strcol = preg_replace("/,$/","",$strcol);
$strval = preg_replace("/,$/","",$strval);
$sqlin = "INSERT INTO $table(" . $strcol. ") VALUES (" . $strval . ")";
$this->lastsql = $sqlin;
$this->exec($sqlin, $arrval);
return 1;
}
/**
* INSERT 文を生成し, 実行する.
*/
function fast_insert($table, $sqlval) {
$strcol = '';
$strval = '';
$find = false;
foreach ($sqlval as $key => $val) {
$strcol .= $key . ',';
if($val != ""){
$eval = pg_escape_string($val);
$strval .= "'$eval',";
} else {
$strval .= "NULL,";
}
$find = true;
}
if(!$find) {
return false;
}
$strcol = ereg_replace(",$","",$strcol);
$strval = ereg_replace(",$","",$strval);
$sqlin = "INSERT INTO $table(" . $strcol. ") VALUES (" . $strval . ")";
$this->lastsql = $sqlin;
$ret = $this->query($sqlin);
return $ret;
}
/**
* UPDATE 文を生成し, 実行する.
*/
function update($table, $sqlval, $where = "", $arradd = "", $addcol = "") {
$strcol = '';
$strval = '';
$find = false;
foreach ($sqlval as $key => $val) {
if(preg_match("/^Now\(\)$/i", $val)) {
$strcol .= $key . '= Now(),';
} elseif(preg_match("/^CURRENT\_TIMESTAMP$/i", $val)) {
$strcol .= $key . '= CURRENT_TIMESTAMP,';
} else {
$strcol .= $key . '= ?,';
if($val != ""){
$arrval[] = $val;
} else {
$arrval[] = NULL;
}
}
$find = true;
}
if(!$find) {
return false;
}
if($addcol != "") {
foreach($addcol as $key => $val) {
$strcol .= "$key = $val,";
}
}
$strcol = preg_replace("/,$/","",$strcol);
$strval = preg_replace("/,$/","",$strval);
if($where != "") {
$sqlup = "UPDATE $table SET $strcol WHERE $where";
} else {
$sqlup = "UPDATE $table SET $strcol";
}
if(is_array($arradd)) {
foreach($arradd as $val) {
$arrval[] = $val;
}
}
$this->lastsql = $sqlup;
$this->exec($sqlup, $arrval);
return $ret;
}
/**
* 複数の SQL を実行する.
*
* @param string $sql 実行する SQL
* @param array $sqlval ブレースホルダに挿入する値の配列
* @return integer 実行した SQL の数
*/
function executeBatch($sql, $sqlval) {
$this->lastsql = $sql;
$statement = $this->pdo->prepare($sql);
$count = 0;
foreach ($sqlval as $val) {
$statement->execute($val);
$count++;
}
return $count;
}
/**
* MAX 文を生成し, 実行する.
*/
function max($table, $col, $where = "", $arrval = array()) {
if(strlen($where) <= 0) {
$sqlse = "SELECT MAX($col) FROM $table";
} else {
$sqlse = "SELECT MAX($col) FROM $table WHERE $where";
}
$this->lastsql = $sqlse;
$ret = $this->getOne($sqlse, $arrval);
return $ret;
}
/**
* MIN 文を生成し, 実行する.
*/
function min($table, $col, $where = "", $arrval = array()) {
if(strlen($where) <= 0) {
$sqlse = "SELECT MIN($col) FROM $table";
} else {
$sqlse = "SELECT MIN($col) FROM $table WHERE $where";
}
$this->lastsql = $sqlse;
$ret = $this->getOne($sqlse, $arrval);
return $ret;
}
/**
* 特定のカラムの値を1行取得する.
*/
function get($table, $col, $where = "", $arrval = array()) {
if(strlen($where) <= 0) {
$sqlse = "SELECT $col FROM $table";
} else {
$sqlse = "SELECT $col FROM $table WHERE $where";
}
$this->lastsql = $sqlse;
$statement = $this->pdo->prepare($sqlse);
$statement->execute($arrval);
return $statement->fetch();
}
/**
* SQL の結果を1件だけ取得する.
*/
public function getOne($sql, $array = array()) {
$this->lastsql = $sql;
$statement = $this->pdo->prepare($sql);
$statement->execute($array);
return $statement->fetchColumn();
}
/**
* 特定の結果を1行だけ取得する.
*/
function getrow($table, $col, $where = "", $arrval = array()) {
if(strlen($where) <= 0) {
$sqlse = "SELECT $col FROM $table";
} else {
$sqlse = "SELECT $col FROM $table WHERE $where";
}
$this->lastsql = $sqlse;
$statement = $this->pdo->prepare($sqlse);
$statement->execute($arrval);
return $statement->fetch();
}
/**
* 特定の結果を1列だけ取得する.
*/
function getCol($table, $col, $where = "", $arrval = array()) {
if (strlen($where) <= 0) {
$sqlse = "SELECT $col FROM $table";
} else {
$sqlse = "SELECT $col FROM $table WHERE $where";
}
$this->lastsql = $sqlse;
$statement = $this->pdo->prepare($sqlse);
$statement->execute($arrval);
return $statement->fetchAll(PDO::FETCH_ASSOC);
}
/**
* レコードの削除を行う.
*/
function delete($table, $where = "", $arrval = array()) {
if(strlen($where) <= 0) {
$sqlde = "DELETE FROM $table";
} else {
$sqlde = "DELETE FROM $table WHERE $where";
}
$this->lastsql = $sqlde;
return $this->exec($sqlde, $arrval);
}
/**
* 次のシーケンス番号を取得する.
*/
function nextval($table, $colname) {
$seqtable = $table . "_" . $colname . "_seq";
$sql = "SELECT NEXTVAL('$seqtable')";
$this->lastsql = $sql;
return $this->getOne($sql);
}
/**
* 現在のシーケンス番号を取得する.
*/
function currval($table, $colname) {
$seqtable = $table . "_" . $colname . "_seq";
$sql = "SELECT CURRVAL('$seqtable')";
$this->lastsql = $sql;
return $this->getOne($sql);
}
/**
* シーケンス番号を設定する.
*/
function setval($table, $colname, $data) {
$seqtable = $table . "_" . $colname . "_seq";
$sql = "SELECT SETVAL('$seqtable', $data)";
$this->lastsql = $sql;
return $this->getOne($sql);
}
/**
* 特定の SQL を実行し, すべての結果を取得する.
*/
function query($n ,$arr = ""){
$this->lastsql = $n;
$statement = $this->pdo->prepare($n);
if (empty($arr)) {
$statement->execute();
} else {
$statement->execute($arr);
}
return $statement->fetchAll(PDO::FETCH_ASSOC);
}
/**
* PDO インスタンスを生成する.
*
* @param array $arrDSN DB接続情報の配列
* @return PDO PDOインスタンス
* @exception PDOException エラーが発生した場合
*/
protected function getPDO($arrDSN) {
$dsn = "pgsql:host=" . $arrDSN['host']
. " port=" . $arrDSN['port']
. " dbname=" . $arrDSN['dbname']
. " user=" . $arrDSN['user']
. " password=" . $arrDSN['password'];
// エラーメールの差出人、タイトルを設定
$this->error_mail_to = MAIL_WARN;
$this->error_mail_title = PRODUCT_NAME . "_DB_ERROR - ";
try {
//PDO接続を生成する
$this->pdo = new PDO($dsn);
} catch (PDOException $e) {
// 接続フラグをオフ
$this->connected = false;
// 接続エラーを保持
$this->connError = $e;
// PDO接続が出来なかった場合は、エラーメールを送信する
$this->send_err_mail($e);
}
// PDO接続ができている場合は、属性を設定する
if($this->pdo != null && !empty($this->pdo)) {
// 接続フラグをオン
$this->connected = true;
$this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->pdo->setAttribute(PDO::ATTR_TIMEOUT, 5);
}
set_exception_handler(array($this, "send_err_mail"));
}
/**
* エラー情報をメールで送信する.
*/
function send_err_mail( $result, $sql = ""){
$errmsg = "SQL:".$sql."\n";
$errmsg.= $result->getMessage()."\n";
$errmsg.= $result->errorinfo;
$errmsg .= print_R($result,true);
$page_file = PAGE_FILE_NAME;
if ($page_file == "LOG") {
$titleHead = "取得エラー ";
}
if ( ! defined("CLIENT_ID") ){
$errmsg .= print_R($this->conn, true);
}
$errmsg .= print_R($_SERVER, true);
$objMail = new GC_SendMail();
$objMail->setItem(
$this->error_mail_to,
$titleHead. $this->error_mail_title. $_SERVER['SERVER_NAME']. " (ID: " .CLIENT_ID. "　" .CLIENT_NAME. ")",
"${errmsg}\n".date("Y/m/d H:i:s"),
MAIL_WARN ,
PRODUCT_NAME . "_ADMIN",
$this->error_mail_to,
$this->error_mail_to
);
$objMail->sendMail();
// デバッグ出力
if($this->exitAllow) {
exit;
}
}
/**
* PDO::exec()を呼び出します.
*
* @param string $sql
* @return integer
*/
public function pdoExec($sql) {
return $this->pdo->exec($sql);
}
/**
* コネクションを切断します.
*/
public function pdoDisconnect() {
$this->pdo = null;
}
/**
* エラー情報をメールで送信する.
* - 2012/05/30 Antares Hideyuki Sasaki
* 処理後にexitしないメール送信関数
* send_err_mail()はメール送信後にexitする
* try/catchでハンドリングした場合のエラーメール送信はこの関数を使用
*/
public function sendErrMail($result){
// エラーメール本文のエラーメッセージを作成
$errmsg = "";
$errmsg.= "DB_IP : ".$this->dsn['host'].":".$this->dsn['port'].PHP_EOL;
$errmsg.= "DB_NAME : ".$this->dsn['dbname'].PHP_EOL;
if($this->lastsql != "") $errmsg.= "SQL :".$this->lastsql.PHP_EOL;
$errmsg.= $result->getMessage().PHP_EOL;
$errmsg.= $result->errorinfo;
$errmsg .= print_R($result, true);
if (PAGE_FILE_NAME == "LOG") $titleHead = "取得エラー ";
if (!defined("CLIENT_ID")) $errmsg .= print_R($this->conn, true);
$errmsg .= print_R($_SERVER, true);
$objMail = new GC_SendMail();
$objMail->setItem(
$this->error_mail_to,
$titleHead. $this->error_mail_title. $_SERVER['SERVER_NAME']. " (ID: " .CLIENT_ID. "　" .CLIENT_NAME. ")",
"${errmsg}\n".date("Y/m/d H:i:s"),
MAIL_WARN ,
PRODUCT_NAME . "_ADMIN",
$this->error_mail_to,
$this->error_mail_to
);
$objMail->sendMail();
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment