X
    Categories: PHP

PDOを利用したDBアクセスクラス(自作ですけども)

最新ソースはコチラ

下記のソースは初期版です。

// PDOを利用したデータベースクラス
class pdbo{
	private static	$pdo;
	private static	$conf;

	private function __construct(){
	}

	public function Factory( $table ){
		if( is_null( pdbo::$conf ) ){
			$tmp = new Config( CONF_PATH.'db.ini' );
			$tmp = $tmp->Get();
			pdbo::$conf = $tmp['DataBase'];
		}
		if( is_null( pdbo::$pdo ) ){
			try{
				pdbo::$pdo = new PDO( pdbo::$conf['dns'],
					pdbo::$conf['user'], pdbo::$conf['password'] );
			} catch( PDOException $e ){
				echo $e->GetMessage();
				throw new Exception( $e->getMessage() );
			}
		}
		return new dbo( pdbo::$pdo, '', $table );
	}

}

class dbo{
	private $dbo;
	private $db_name;
	private $table_name;
	private $fileds;
	private $error_message;
	private $query_log = array();

	public function __construct( $pdbo, $db, $table ){
		$this->dbo		= $pdbo;
		$this->db_name	= $db;
		$this->table_name	= $table;

		//テーブル情報取得
		try{
			$ret = $pdbo->query( 'DESCRIBE '.$table );
		} catch( PDOException $e ){
			$this->error_message = $e->getMessage();
			throw new Exception( $this->error_message );
		}
		$tmp = $ret->fetchAll( PDO::FETCH_ASSOC );
		foreach( $tmp as $node ){
			$type = $this->getType( $node['Type'] );
			$this->fileds[$node['Field']]['type']= $type['type'];
			$this->fileds[$node['Field']]['size']= $type['size'];
			$this->fileds[$node['Field']]['option']= $type['option'];
			$this->fileds[$node['Field']]['null']= $node['Null'];
			$this->fileds[$node['Field']]['key']	= $node['Key'];
		}
	}

	public function DumpQuerys(){
		print_r( $this->query_log );
	}
	/**
	 * 一行だけレコードを取得する
	 *  $flagがtrueの時、条件配列にプライマリキーが存在しない場合エラーに
	 *  なります
	 * @param	array	$condition	条件配列
	 * @param	bool	$flag		プライマリキー存在チェック
	 */
	public function getOnce( $condition, $flag = true ){
		$table	= $this->table_name;
		$dbo	=& $this->dbo;
		if( $flag ){
			//プライマリーキーの存在チェック
			$primary = $this->getPrimary();
			$keys = array_keys( $condition );
			foreach( $primary as $name  ){
				if( !in_array( $name, $keys ) ){
					throw new Exception('プライマリーキーが設定されていません');
				}
			}
		}

		//検索条件生成
		$ret = null;
		try{
			$where = $this->makeWhere( $condition );

			$sql = "SELECT * FROM {$table}";
			if( strlen( $where ) > 0 ) $sql .= $where;
			$this->query_log[] = $sql;
			$ret = $dbo->query( $sql );

		} catch( PDOException $e ){
			$this->error_message = $e->getMessage();
			throw new Exception( $this->error_message );
		}

		//エラー処理
		if( $ret === false )  throw new Exception( "query error: {$sql}");

		//複数行検出
		if( $ret->rowCount() != 1 ) return false;

		return $ret->fetch(PDO::FETCH_ASSOC);
	}

	public function getAll($condition = array(), $order = array()){
		$table	= $this->table_name;
		$dbo	=& $this->dbo;

		//検索条件生成
		$ret = null;
		try{
			$where = $this->makeWhere( $condition );
			$_order = $this->makeOrder( $order );

			$sql = "SELECT * FROM {$table}";
			$sql .= $where;
			$sql .= $_order;
			$this->query_log[] = $sql;
			$ret = $dbo->query( $sql );

		} catch( PDOException $e ){
			$this->error_message = $e->getMessage();
			throw new Exception( $this->error_message );
		}

		//エラー処理
		if( $ret === false )  throw new Exception( "query error: {$sql}");

		return $ret->fetchAll(PDO::FETCH_ASSOC);
	}

	public function getPaging( $condition = array(), $order, &$page ){
		$table	= $this->table_name;
		$dbo	=& $this->dbo;

		//検索条件生成
		$res = null;
		try{
			$where = $this->makeWhere( $condition );
			$_order = $this->makeOrder( $order );

			$sql = "SELECT * FROM {$table}";
			$sql .= $where;
			$sql .= $_order;
			$this->query_log[] = $sql;
			$res = $dbo->query( $sql );

		} catch( PDOException $e ){
			$this->error_message = $e->getMessage();
			throw new Exception( $this->error_message );
		}

		//エラー処理
		if( $res === false )  throw new Exception( "query error: {$sql}");

		$page['rowCount'] = $res->rowCount();
		$page['pageMax'] = ceil( $page['rowCount'] / $page['limit'] );
		$page['start']	= ($page['now'] - 1 )* $page['limit'] + 1;
		$page['end']	= $page['now'] * $page['limit'];
		if( $page['end'] > $page['rowCount'] ) $page['end'] = $page['rowCount'];
		$ret = array();
		for( $n = $page['start']; $n fetch( PDO::FETCH_ASSOC, PDO::FETCH_ORI_ABS, $n );
		}
		return $ret;
	}

	/**
	 * 唯一のレコード更新
	 */
	public function updateOnce( $data ){
		$table	= $this->table_name;
		$dbo	=& $this->dbo;
		$fileds =& $this->fileds;

		//更新データ
		$up_data = array();
		//条件データ
		$condition = array();
		foreach( $data as $name => $val ){
			list( $nm ) = $this->getFiledName( $name );
			if( $fileds[$nm]['key'] == 'PRI' ){
				$condition[$name] = $val;
			} else {
				$up_data[$name] = $val;
			}
		}

		return $this->update( $up_data, $condition );
	}

	/**
	 * 複数更新
	 */
	public function update( $data, $condition ){
		$table	= $this->table_name;
		$dbo	=& $this->dbo;
		$fileds =& $this->fileds;
		$node = array();
		try{
			$where = $this->makeWhere( $condition );
			foreach( $data as $name => $val){
				$val = addslashes($val);
				//日付時にクォートをつける
				if( $this->isDate($fileds[$name]['type']) ) {
					if( $val == $val * 1 ){
						$val = "'".date( 'Y-m-d H:i:s', $val )."'";
					}
				}
				//文字型にシングルクォートをつける
				if( $this->isString($fileds[$name]['type']) ) {
					$val = "'{$val}'";
				}
				$node[] = "{$name} = {$val}";
		   	}
			$sql = "UPDATE {$table} SET ".implode( ' , ', $node )." {$where}";
			$this->query_log[] = $sql;
			$dbo->query( $sql );
		} catch( Exception $e){
			echo $e->getMessage();
			return false;
		}
		return true;
	}

	/**
	 * 一行だけレコードを取得する
	 *  $flagがtrueの時、条件配列にプライマリキーが存在しない場合エラーに
	 *  なります
	 * @param	array	$data		登録情報
	 * @param	bool	$flag		プライマリキー存在チェック
	 */
	public function insert( $data, $flag = true ){
		if( count( $data ) == 0 ) return false;
		//インサートチェック
		if( ($name = dbo::existsFiled($data,$this->getNotNull())) !== true ){
			throw new Exception(
				"{$name}はnot nullのフィールドですが、存在していませんでした。"
		   	);
		}
		$fileds =& $this->fileds;
		//文字列次処理
		foreach( $data as $name => $val ){
			//文字列時にクォートをつける
			if( $this->isString($fileds[$name]['type']) ){
				$val = "'".addslashes($val)."'";
			}
			//日付時にクォートをつける
			if( $this->isDate($fileds[$name]['type']) ) {
				if( is_int( $val ) ){
					$val = date( 'Y-m-d H:i:s', $val );
				}
				$val = "'".addslashes($val)."'";
			}
			$data[$name] = $val;
		}

		$tmp = array();
		foreach( $data as $name => $val ) $tmp[] = "{$val}";

		$sql = "INSERT INTO {$this->table_name} ( ".
			implode( ' , ', array_keys($data) )." ) VALUES ( ";
		$sql .= implode( ', ', $tmp )." ); ";
		try{
			$this->query_log[] = $sql;
			$ret = $this->dbo->query( $sql );
		} catch( PDOException $e ) {
			throw new Exception( 'Insert Error:'.$e->getMessage() );
		}
		if( $ret === false ){
			throw new Exception( 'Insert Error: '.$sql );
		}
		return true;
	}

	public function delete( $condition = array() ){
	}

	/**
	 * 条件削除
	 */
	public function deleteSearch( $condition ){
		if( count( $condition ) == 0 ) return false;

		$sql = "DELETE FROM {$this->table_name} ".$this->makeWhere($condition);

		try{
			$this->query_log[] = $sql;
			$ret = $this->dbo->query( $sql );
		} catch( PDOException $e ) {
			throw new Exception( 'Insert Error:'.$e->getMessage() );
		}
		return true;
	}

	/**
	 * Where句の生成
	 */
	private function makeWhere( $condition ){
		if( count( $condition ) == 0 ) return '';
		$fileds = $this->fileds;
		$where = array();
		foreach( $condition as $key => $val ){
			$val = addslashes( $val );
			$option = '';
			//オプション検出
			if( strpos( $key, ':' )  !== false ){
				//オプション取得
				$tmp = $key;
				list( $key, $option ) = explode( ':', $tmp );

				//テーブルにカラムが存在するか
				if( !isset( $fileds[$key] ) ){
					throw new Exception(
						"{$key}は{$this->table_name}に存在しないカラムです"
				   	);
				}

				//文字列 日付型 の場合の処理
				if( $this->isString( $fileds[$key]['type'] ) ||
					$this->isDate( $fileds[$key]['type'] )
				) {
					$val = "'{$val}'";
				}

				switch( $option ){
					case 'not':
						$where[] = "{$key} <> {$val}";
						break;

					case 'begin':
						$where[] = "{$key} >= {$val}";
						break;
					case 'quit':
						$where[] = "{$key}  {$val}";
						break;

					case 'end':
						$where[] = "{$key} < {$val}"; 						break; 					case '--': 						$where[] = "{$key} {$val}"; 						break; 					case 'like': 						$where[] = "{$key} LIKE {$val}"; 						break; 					case 'eq': 						$where[] = "{$key} = {$val}"; 						break; 					case 'neq': 						$where[] = "{$key} = {$val}"; 						break; 					default: 						throw  new Exception( 'makeWhere error: '. 							'指定されないオプションを検出しました' 					   	); 				} 			} else { 				//オプションが無いとき 				//テーブルにカラムが存在するか 				if( !isset( $this->fileds[$key] ) ){
					throw new Exception(
						"{$key}は{$this->table_name}に存在しないカラムです"
				   	);
				}

				if( $this->isString($fileds[$key]['type'] ) ) {
					$where[] = "{$key} LIKE '%{$val}%'";
				} else {
					$where[] = "{$key} = {$val}";
				}
			}
		}
		return " WHERE ".implode( ' AND ', $where );
	}

	/**
	 *
	 */
	protected function makeOrder( $order ){
		if( count( $order ) == 0 ) return '';

		$ret = array();
		$fileds = $this->fileds;
		$kyes = array_keys( $order );
		//テーブルにカラムが存在するか
		foreach( $order as $name => $val ){
			if( !isset( $fileds[$name] ) ){
				throw new Exception(
					"{$key}は{$this->table_name}に存在しないカラムです"
		   		);
			}
			$ret[] = "{$name} {$val}";
		}
		return " ORDER BY ".implode( " , ", $ret );
	}

	/**
	 * 文字型かを判別する
	 */
	protected function isString( $type ){
		switch( strtolower($type) ){
			case 'char':
			case 'varchar':
			case 'text':
				return true;
		}
		return false;
	}

	/**
	 * 日付型かを判別する
	 */
	protected function isDate( $type ){
		switch( strtolower($type) ){
			case 'date':
			case 'datetime':
			case 'timestamp':
				return true;
		}
		return false;
	}

	/**
	 * Mysqlのカラムタイプから型とサイズ、オプション属性を切り離す
	 */
	private function getType( $type ){
		$ret = array(
			'size'	=> '',
			'type'	=> '',
			'option'=> '',
		);
		$st	= 0;
		$end= 0;
		$st = strpos( $type, '(' );
		if( $st === false ){
			$ret['type'] = $type;
			return $ret;
		}
		$ret['type'] = substr( $type, 0, $st );
		$end = strpos( $type, ')' );
		$ret['size'] = substr( $type, $st+1, $end-$st-1 );
		$tmp = explode( ' ', $type );
		if( count( $tmp ) > 1 ) $ret['option'] = $tmp[1];
		return $ret;
	}

	/**
	 * プライマリーキー配列の取得
	 */
	private function getPrimary(){
		$ret = array();
		foreach( $this->fileds as $name => $node ){
			if( $node['key'] == 'PRI' ){
				$ret[] = $name;
			}
		}
		return $ret;
	}

	private function getNotNull(){
		$ret = array();
		foreach( $this->fileds as $name => $node ){
			if( $node['null'] == 'No' ){
				$ret[] = $name;
			}
		}
		return $ret;
	}

	/**
	 * $list配列内のフィールドが$filedsに含まれているかチェックする
	 */
	private function existsFiled( $fileds, $list ){
		$keys = array_keys( $fileds );
		foreach( $list as $name ){
			if( !in_array( $name, $keys ) ) return $name;
		}
		return true;
	}

	private function getFiledName( $name ){
		if( strpos(  $name, ':' )  === false ) return array( $name, '' );
		return explode( ':', $name );
	}
}
健巳:
Related Post