SqlAdapter

sql_adapter.php

<?php

class SqlAdapter
{
	protected $pdo;
	protected $query;
	protected $sth;
	protected $params;
	protected $lastMethod;
	
	public function __construct( $pdo )
	{
		$this->pdo = $pdo;
		$this->reset();
	}
	
	public function reset()
	{
		$this->query	= '';
		$this->sth		= null;
		$this->params	= [];
		$this->lastMethod = false;
		return $this;
	}
	
//------------------------------------------------------------------------------
	public function sql() { return $this->query; }
	public function params() { return $this->params; }
	public function sql_dump( $assoc = true )
	{
		return $assoc ?
			[ 'sql' => $this->query, 'params' => $this->params ] :
			[ $this->query, $this->params ];
	}
	
	public function execute()
	{
		#$var = $this->sql_dump(); debug_var( $var ); print_r( $var );
		#error_log( var_export( $this->sql_dump(), true ) );
		$this->sth = $this->pdo->prepare( $this->query );
		$this->sth->execute( $this->params );
		return $this->sth;
	}
	
	public function executeOnce()
	{
		if ( !is_null( $this->sth ) )
			return $this->sth;
		return $this->execute();
	}
	
	public function fetchAll( $key = false, $fetchStyle = PDO::FETCH_ASSOC )
	{
		if ( true === $fetchStyle )
			$fetchStyle = PDO::FETCH_BOTH;
		$sth = $this->executeOnce();
		if ( false === $key )
			return $sth->fetchAll( $fetchStyle );
		$result = [];
		while ( $row = $sth->fetch( $fetchStyle ) )
			$result[ $row[ $key ] ] = $row;
		return $result;
	}
	
	public function fetch( $fetchStyle = PDO::FETCH_ASSOC )
	{
		true === $fetchStyle && $fetchStyle = PDO::FETCH_BOTH;
		return $this->executeOnce()->fetch( $fetchStyle );
	}
	
	public function fetchCol( $col )
	{
		return $this->executeOnce()->fetch()[ $col ];
	}
	
	public function fetchColAll( $col, $key = false )
	{
		$sth = $this->executeOnce();
		$result = [];
		if ( false === $key )
		{
			while ( $row = $sth->fetch() )
				$result[] = $row[ $col ];
		} else
		{
			while ( $row = $sth->fetch() )
				$result[ $row[ $key ] ] = $row[ $col ];
		}
		return $result;
	}
	
	public function rowCount()
	{
		if ( is_null( $this->sth ) )
			$this->execute();
		return $this->sth->rowCount();
	}
	
	public function lastInsertId( $name = null )
	{
		if ( is_null( $this->sth ) )
			$this->execute();
		return $this->pdo->lastInsertId( $name );
	}
	
	public function beginTransaction()
	{
		$this->pdo->beginTransaction();
		return $this;
	}
	
	public function commit()
	{
		$this->pdo->commit();
		return $this;
	}
	
	public function rollBack()
	{
		$this->pdo->rollBack();
		return $this;
	}
	
//------------------------------------------------------------------------------
	public function addParams()
	{
		$args = func_get_args();
		foreach ( $args as $params )
		{
			if ( is_array( $params ) )
				$this->params = array_merge( $this->params, array_values( $params ) );
			else
				$this->params[] = $params;
		}
		return $this;
	}
	
	protected function finishMethod( $lastMethod )
	{
		$this->lastMethod = $lastMethod;
		return $this;
	}
	
//------------------------------------------------------------------------------
	public function runMethods( $query )
	{
		if ( is_callable( $query ) )
			return $query( $this );
		$r = $this;
		foreach  ( $query as $method => $params )
		{
			if ( 2 == count( $params ) && isset( $params[ 'method' ] ) && isset( $params[ 'args' ] ) )
				$r = $this->$params[ 'method' ]( $params[ 'args' ] );
			else
				$r = $this->$method( $params );
		}
		return $r;
	}
	
//------------------------------------------------------------------------------
	public function insert( $table, $cols = '', $mods = [] )
	{
		if ( is_array( $table ) )
			extract( $table );
		if ( is_array( $cols ) )
			$cols = implode( '", "', $cols );
		if ( !empty( $cols ) )
			$cols = '("' . $cols . '")';
		if ( is_array( $mods ) )
			$mods = implode( ' ', $mods );
		$this->query .= "\nINSERT $mods INTO $table $cols";
		return $this->finishMethod( __FUNCTION__ );
	}
	
	public function values( $values = [], $keys = false )
	{
		if ( is_array( $values ) && isset( $values[ 'values' ] ) )
			extract( $values );
		if ( !is_array( $values ) )
		{
			if ( false === $keys )
			{
				$values = [[ $values ]];
			} else
			{
				$values = [ func_get_args() ];
				$keys = false;
			}
		}
		if ( !is_array( reset( $values ) ) )
			$values = [ $values ];
		
		if ( false === $keys )
			$keys = array_keys( reset( $values ) );
		
		$count_fields = count( $keys );
		if ( !$count_fields )
			return $this->valuesRaw( substr( str_repeat( '(),' , count( $values ) ), 0, -1 ) );
		$values_str_sngl = '(' . mb_substr( str_repeat( ',?', $count_fields ), 1 ) . ')' ;
		$values_str = mb_substr( str_repeat( ',' . $values_str_sngl, count( $values ) ), 1 );
		foreach ( $values as $row )
			foreach ( $keys as $key )
				$this->params[] = $row[ $key ];
		return $this->valuesRaw( $values_str );
	}
	
	public function valuesRaw( $sth, $values = [] )
	{
		if ( is_array( $sth ) )
			extract( $sth );
		if ( !is_array( $values ) )
		{
			if ( 2 < func_num_args() )
				$values = array_slice( func_get_args(), 1 );
			else
				$values = [ $values ];
		}
			
		$this->addParams( $values );
		$glue = __FUNCTION__ === $this->lastMethod ? ',' : "\nVALUES";
		$this->query .= "$glue $sth";
		return $this->finishMethod( __FUNCTION__ );
	}
	
	public function onDuplicate( $values, $params = null )
	{
		return $this->addList( 'ON DUPLICATE KEY UPDATE', $values, $params );
	}
	
	public function addList( $sth, $values, $params )
	{
		if ( is_array( $sth ) )
			extract( $sth );
		$glue = $sth === $this->lastMethod ? ', ' : "\n" . $sth;
		if ( is_string( $values ) )
		{
			$this->query .= "$glue $values";
		} else
		{
			$this->query .= $glue . ' ';
			$this->query .= '"' . implode( '" = ?, "', array_keys( $values ) ) . '" = ?';
			$this->addParams( $values );
		}
		if ( !is_null( $params ) )
			$this->addParams( $params );
		return $this->finishMethod( $sth );
	}
	
//------------------------------------------------------------------------------
	public function select( $tables, $cols = '*', $mods = [] )
	{
		if ( is_array( $tables ) && isset( $tables[ 'tables' ] ) )
			extract( $tables );
		$cols_str = implode( ', ', (array)$cols );
		$tables_str = is_array( $tables ) ? implode( ', ', $tables ) : $tables;
		$mods_str = implode( ' ', (array)$mods );
		$this->query .= <<<SQL

SELECT $mods_str
	$cols_str
FROM $tables_str
SQL;
		return $this->finishMethod( __FUNCTION__ );
	}
	
	public function union( $mod = '' )
	{
		$this->query .= "\nUNION " . $mod;
		return $this->finishMethod( __FUNCTION__ );
	}
	
//------------------------------------------------------------------------------
	public function delete( $tables, $using = [], $mods = [] )
	{
		if ( is_array( $tables ) && isset( $tables[ 'tables' ] ) )
			extract( $tables );
		$tables_str = implode( ', ', (array)$tables );
		$mods_str = implode( ' ', (array)$mods );
		$using_str = implode( ', ', (array)$using );
		if ( !empty( $using_str ) ) $using_str = "\nUSING " . $using_str;
		$this->query .= <<<SQL
DELETE $mods_str
FROM $tables_str $using_str
SQL;
		return $this->finishMethod( __FUNCTION__ );
	}
	
	public function using( $tables )
	{
		$glue = __FUNCTION__ === $this->lastMethod ? ', ' : "\nUSING";
		$tables_str = is_array( $tables ) ? implode( ', ', $tables ) : $tables;
		$this->query .= "$glue $tables_str";
		return $this->finishMethod( __FUNCTION__ );
	}
	
//------------------------------------------------------------------------------
	public function update( $tables, $mods = [] )
	{
		if ( is_array( $tables ) && isset( $tables[ 'tables' ] ) )
			extract( $tables );
		$tables_str = implode( ', ', (array)$tables );
		$mods_str = implode( ' ', (array)$mods );
		$this->query = "UPDATE $mods_str $tables_str";
		return $this->finishMethod( __FUNCTION__ );
	}
	
	public function set( $values, $params = null )
	{
		/*$glue = __FUNCTION__ === $this->lastMethod ? ',' : "\nSET";
		if ( is_string( $values ) )
		{
			$this->query .= "$glue $values";
		} else
		{
			$sth = implode( ' = ?, ', array_keys( $values ) ) . ' = ?';
			$this->query .= "$glue $sth";
			$this->addParams( $values );
		}
		return $this->finishMethod( __FUNCTION__ );*/
		return $this->addList( 'SET', $values, $params );
	}
	
//------------------------------------------------------------------------------
	public function join( $table, $sth = '', $params = [], $joinType = '' )
	{
		if ( is_array( $table ) )
			extract( $table );
		if ( !empty( $joinType ) )
			$joinType .= ' ';
		$this->query .= "\n{$joinType}JOIN $table $sth";
		$this->addParams( $params );
		return $this->finishMethod( __FUNCTION__ );
	}
	
	public function joinOn( $table, $sth = '', $params = [], $joinType = '' )
	{
		if ( is_array( $table ) )
			extract( $table );
		return $this->join( $table, 'ON ' . $sth, $params, $joinType );
	}
	
	public function joinUsing( $table, $fields = [], $joinType = '' )
	{
		if ( is_array( $table ) )
			extract( $table );
		$sth = empty( $fields ) ? 'id_' . $table : implode( ', ', (array)$fields );
		return $this->join( $table, "USING ($sth)", [], $joinType );
	}
	
//------------------------------------------------------------------------------
	public function where( $sth = '', $params = [] )
	{
		static $type = 'WHERE';
		if ( is_array( $sth ) && isset( $sth[ 'sth' ] ) )
		{
			$sth[ 'type' ] = $type;
			return $this->clause( $sth );
		}
		return $this->clause( $type, $sth, $params );
	}
	
	public function having( $sth = '', $params = [] )
	{
		static $type = 'HAVING';
		if ( is_array( $sth ) && isset( $sth[ 'sth' ] ) )
		{
			$sth[ 'type' ] = $type;
			return $this->clause( $sth );
		}
		return $this->clause( $type, $sth, $params );
	}
	
	public function clause( $type, $sth = '', $params = [] )
	{
		if ( is_array( $type ) )
			extract( $type );
		if ( is_array( $params ) && 1 == count( $params ) && is_array( reset( $params ) ) )
			$params = reset( $params );
		$glue = $type === $this->lastMethod ? ' AND' : "\n" . $type;
		$this->query .= "$glue $sth";
		$this->addParams( $params );
		return $this->finishMethod( $type );
	}
	
//------------------------------------------------------------------------------
	public function group( $sth, $mods = [] )
	{
		if ( is_array( $sth ) )
			extract( $sth );
		$mods_str = implode( ' ', (array)$mods );
		$glue = __FUNCTION__ === $this->lastMethod ? ', ' : "\nGROUP BY";
		$this->query .= "$glue $sth $mods_str";
		return $this->finishMethod( __FUNCTION__ );
	}
	
//------------------------------------------------------------------------------
	public function order( $sth )
	{
		$glue = __FUNCTION__ === $this->lastMethod ? ', ' : "\nORDER BY ";
		if ( !is_array( $sth ) )
			$sth = func_get_args();
		$this->query .= $glue . '' . implode( ', ', $sth );
		return $this->finishMethod( __FUNCTION__ );
	}
	
//------------------------------------------------------------------------------
	public function limit( $limit, $offset = false )
	{
		if ( is_array( $limit ) )
			extract( $limit );
		$this->query .= "\nLIMIT $limit";
		if ( false !== $offset )
			$this->query .= " OFFSET $offset";
		return $this->finishMethod( __FUNCTION__ );
	}
	
//------------------------------------------------------------------------------
	public function in( $field, $values )
	{
		if ( is_array( $field ) && isset( $field[ 'field' ] ) )
			extract( $field );
		if ( is_array( $field ) )
		{
			$item_str = '(' . mb_substr( str_repeat( ',?', count( $field ) ), 1 ) . ')';
			$field = '(' . implode( ', ', $field ) . ')';
		} else
		{
			$item_str = '?';
		}
		if ( is_array( $values ) && is_array( $item = reset( $values ) ) )
		{
			foreach ( $values as $val )
				$this->addParams( $val );
		} else
		{
			$this->addParams( $values );
		}
		$values_str = '(' . mb_substr( str_repeat( ',' . $item_str, count( $values ) ), 1 ) . ')';
		$this->query .= "$field IN $values_str";
		return $this;
	}
	
	public function eq( $values )
	{
		return $this->op( '=', $values );
	}
	
	public function op( $op, $values )
	{
		$sth = '"'
			. implode( '" ' . $op . ' ? AND "', array_keys( $values ) )
			. '" = ?';
		$this->query .= $sth;
		$this->addParams( $values );
		return $this;
	}
	
//------------------------------------------------------------------------------
	public function rawSql( $sql, $params = [] )
	{
		if ( is_array( $sql ) )
			extract( $sql );
		$this->query .= "\n$sql";
		$this->addParams( $params );
		return $this->finishMethod( __FUNCTION__ );
	}
	
//------------------------------------------------------------------------------
	public function createTable( $table, $cols, $keys = [], $options = [] )
	{
		if ( is_array( $table ) )
			extract( $table );
		$tmp = isset( $options[ 'tmp' ] ) ? 'TEMPORARY' : '';
		$this->query .= <<<SQL
CREATE $tmp TABLE IF NOT EXISTS $table
(
SQL;
		foreach ( $cols as $col_name => $col_definition )
			$this->createTableColDef( $col_name, $col_definition );
		if ( !empty( $keys ) )
			$this->query .= ",\n\t" . implode( ",\n\t", (array)$keys );
		$this->query .= "\n)";
		return $this->finishMethod( __FUNCTION__ );
	}
	
	public function createTableColDef( $colName, $colDef )
	{
		if ( is_array( $colName ) )
			extract( $colDef );
		$glue = __FUNCTION__ === $this->lastMethod ? ",\n" : "\n";
		$this->query .= "$glue\t$colName $colDef";
		return $this->finishMethod( __FUNCTION__ );
	}
	
	public function createTmpTable( $table, $cols, $keys = [], $options = [] )
	{
		if ( !is_array( $table ) )
			return $this->createTable( $table, $cols, $keys, array_merge( $options, [ 'tmp' => true ] ) );
		$table[ 'options' ][ 'tmp' ] = true;
		return $this->createTable( $table );
	}
	
//------------------------------------------------------------------------------
	public function dropTable( $table, $options = [] )
	{
		if ( is_array( $table ) )
			extract( $table );
		$tmp = isset( $options[ 'tmp' ] ) ? 'TEMPORARY' : '';
		$this->query .= "DROP $tmp TABLE IF EXISTS $table\n";
		return $this->finishMethod( __FUNCTION__ );
	}
	
	public function dropTmpTable( $table, $options = [] )
	{
		if ( !is_array( $table ) )
			return $this->dropTable( $table, array_merge( $options, [ 'tmp' => true ] ) );
		$table[ 'options' ][ 'tmp' ] = true;
		return $this->dropTable( $table );
	}
}