File io/db/DBQuerier.class.php
1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: 34: 35: 36: 37: 38: 39: 40: 41: 42: 43: 44: 45: 46: 47: 48: 49: 50: 51: 52: 53: 54: 55: 56: 57: 58: 59: 60: 61: 62: 63: 64: 65: 66: 67: 68: 69: 70: 71: 72: 73: 74: 75: 76: 77: 78: 79: 80: 81: 82: 83: 84: 85: 86: 87: 88: 89: 90: 91: 92: 93: 94: 95: 96: 97: 98: 99: 100: 101: 102: 103: 104: 105: 106: 107: 108: 109: 110: 111: 112: 113: 114: 115: 116: 117: 118: 119: 120: 121: 122: 123: 124: 125: 126: 127: 128: 129: 130: 131: 132: 133: 134: 135: 136: 137: 138: 139: 140: 141: 142: 143: 144: 145: 146: 147: 148: 149: 150: 151: 152: 153: 154: 155: 156: 157: 158: 159: 160: 161: 162: 163: 164: 165: 166: 167: 168: 169: 170: 171: 172: 173: 174: 175: 176: 177: 178: 179: 180: 181: 182: 183: 184: 185: 186: 187: 188: 189: 190: 191: 192: 193: 194: 195: 196: 197: 198: 199: 200: 201: 202: 203: 204: 205: 206: 207: 208: 209: 210: 211: 212: 213: 214: 215: 216: 217: 218: 219: 220: 221: 222: 223: 224: 225: 226: 227: 228: 229: 230: 231: 232: 233: 234: 235: 236: 237: 238: 239: 240: 241: 242: 243: 244: 245: 246: 247: 248: 249: 250: 251: 252: 253: <?php
/**
* implements some simple queries
* @package IO
* @subpackage DB
* @copyright © 2005-2019 PHPBoost
* @license https://www.gnu.org/licenses/gpl-3.0.html GNU/GPL-3.0
* @author Loic ROUCHON <horn@phpboost.com>
* @version PHPBoost 5.2 - last update: 2014 12 22
* @since PHPBoost 3.0 - 2009 10 05
*/
class DBQuerier implements SQLQuerier
{
/**
* @var SQLQuerier
*/
private $querier;
public function __construct(SQLQuerier $querier)
{
$this->querier = $querier;
}
/**
* {@inheritDoc}
*/
public function select($query, $parameters = array(), $fetch_mode = SelectQueryResult::FETCH_ASSOC)
{
return $this->querier->select($query, $parameters, $fetch_mode);
}
/**
* {@inheritDoc}
*/
public function inject($query, $parameters = array())
{
return $this->querier->inject($query, $parameters);
}
/**
* {@inheritDoc}
*/
public function enable_query_translator()
{
$this->querier->enable_query_translator();
}
/**
* {@inheritDoc}
*/
public function disable_query_translator()
{
$this->querier->disable_query_translator();
}
/**
* {@inheritDoc}
*/
public function get_executed_requests_count()
{
return $this->querier->get_executed_requests_count();
}
/**
* Removes all table rows
* @param string $table_name the table name
*/
public function truncate($table_name)
{
$query = 'TRUNCATE ' . $table_name . ';';
$this->querier->inject($query);
}
/**
* insert the values into the <code>$table_name</code> table
* @param string $table_name the name of the table on which work will be done
* @param string[string] $columns the map where columns are keys and values values
* @return InjectQueryResult the query result set
*/
public function insert($table_name, array $columns)
{
$columns_names = array_keys($columns);
$query = 'INSERT INTO ' . $table_name . ' (' . implode(', ', $columns_names) .
') VALUES (:' . implode(', :', $columns_names) . ');';
return $this->querier->inject($query, $columns);
}
/**
* update the values of rows matching the <code>$condition</code> into the
* <code>$table_name</code> table
* @param string $table_name the name of the table on which work will be done
* @param string[string] $columns the map where columns are keys and values values
* @param string $condition the update condition beginning just after the where clause.
* For example, <code>"length > 50 and weight < 100"</code>
* @param string[string] $parameters the query_var map
* @return InjectQueryResult the query result set
*/
public function update($table_name, array $columns, $condition, array $parameters = array())
{
$columns_names = array_keys($columns);
$columns_definition = array();
foreach ($columns_names as $column)
{
$columns_definition[] = $column . '=:' . $column;
}
$query = 'UPDATE ' . $table_name . ' SET ' . implode(', ', $columns_definition) .
' ' . $condition . ';';
return $this->querier->inject($query, array_merge($parameters, $columns));
}
/**
* delete all the row from the <code>$table_name</code> table matching the
* <code>$condition</code> condition
* @param string $table_name the name of the table on which work will be done
* @param string $condition the update condition beginning just after the from clause.
* For example, <code>"length > 50 and weight < 100"</code>
* @param string[string] $parameters the query_var map
*/
public function delete($table_name, $condition, array $parameters = array())
{
$query = 'DELETE FROM ' . $table_name . ' ' . $condition . ';';
$this->querier->inject($query, $parameters);
}
/**
* retrieve a single row from the <code>$table_name</code> table matching the
* <code>$condition</code> condition
* @param string $table_name the name of the table on which work will be done
* @param string[] $columns the columns to retrieve.
* @param string $condition the update condition beginning just after the where clause.
* For example, <code>"length > 50 and weight < 100"</code>
* @param string[string] $parameters the query_var map
* @return mixed[string] the row returned
*/
public function select_single_row($table_name, array $columns, $condition, array $parameters = array())
{
$query_result = self::select_rows($table_name, $columns, $condition, $parameters);
$query_result->rewind();
if (!$query_result->valid())
{
throw new RowNotFoundException();
}
$result = $query_result->current();
$query_result->next();
if ($query_result->valid())
{
throw new NotASingleRowFoundException($query_result);
}
$query_result->dispose();
return $result;
}
/**
* retrieve a single row for executes the <code>$query</code> sql request and returns row
* @param string $query the query to execute
* @param string[string] $parameters the query_var map
* @return mixed the value of the returned row
*/
public function select_single_row_query($query, $parameters = array())
{
$query_result = self::select($query, $parameters, SelectQueryResult::FETCH_ASSOC);
$query_result->rewind();
if (!$query_result->valid())
{
throw new RowNotFoundException();
}
$result = $query_result->current();
$query_result->next();
if ($query_result->valid())
{
throw new NotASingleRowFoundException($query_result);
}
$query_result->dispose();
return $result;
}
/**
* Returns true if a or multiple rows match the given condition.
* @param string $table_name the name of the table on which work will be done
* @param string $condition the condition beginning just after the where clause.
* For example, <code>"WHERE length > 50 and weight < 100"</code>
* @param string[string] $parameters the query_var map
* @return bool true if a or multiple rows match the given condition.
*/
public function row_exists($table_name, $condition, array $parameters = array())
{
return $this->count($table_name, $condition, $parameters) > 0;
}
/**
* retrieve a single value of the <code>$column</code> column of a single row from the
* <code>$table_name</code> table matching the <code>$condition</code> condition.
* @param string $table_name the name of the table on which work will be done
* @param string $column the column to retrieve.
* @param string $condition the update condition beginning just after the where clause.
* For example, <code>"length > 50 and weight < 100"</code>
* @param string[string] $parameters the query_var map
* @return mixed the value of the returned row
*/
public function get_column_value($table_name, $column, $condition, array $parameters = array())
{
$result = $this->select_single_row($table_name, array($column), $condition, $parameters);
return array_shift($result);
}
/**
* retrieve rows from the <code>$table_name</code> table matching the
* <code>$condition</code> condition
* @param string $table_name the name of the table on which work will be done
* @param string[] $columns the columns to retrieve.
* @param string $condition the update condition beginning just after the where clause.
* For example, <code>"length > 50 and weight < 100"</code>
* @param string[string] $parameters the query_var map
* @return mixed[string] the row returned
*/
public function select_rows($table_name, array $columns, $condition = 'WHERE 1',
$parameters = array())
{
$query = 'SELECT ' . implode(', ', $columns) . ' FROM ' . $table_name . ' ' . $condition;
return $this->querier->select($query, $parameters);
}
/**
* count the number of rows from the <code>$table_name</code> table matching the
* <code>$condition</code> condition
* @param string $table_name the name of the table on which work will be done
* @param string $condition the update condition beginning just after the where clause.
* For example, <code>"length > 50 and weight < 100"</code>
* @param string $count_column the column name on which count or * if all
* @param string[string] $parameters the query_var map
* @return int the number of rows returned
*/
public function count($table_name, $condition = 'WHERE 1', $parameters = array(),
$count_column = '*')
{
$query = 'SELECT COUNT(' . $count_column . ') FROM ' . $table_name;
if (!empty($condition))
{
$query .= ' ' . $condition;
}
$row = $this->querier->select($query, $parameters, SelectQueryResult::FETCH_NUM)->fetch();
return (int) $row[0];
}
public function get_querier()
{
return $this->querier;
}
}
?>