SelectStatement.php
1 <?php
2 /**
3  * wCMF - wemove Content Management Framework
4  * Copyright (C) 2005-2020 wemove digital solutions GmbH
5  *
6  * Licensed under the terms of the MIT License.
7  *
8  * See the LICENSE file distributed with this work for
9  * additional information.
10  */
11 namespace wcmf\lib\model\mapper;
12 
16 use Laminas\Db\Adapter\AdapterInterface;
17 use Laminas\Db\Sql\Select;
18 use Laminas\Db\Sql\Sql;
19 
20 /**
21  * Select statement
22  *
23  * @author ingo herwig <ingo@wemove.com>
24  */
25 class SelectStatement extends Select {
26 
27  const NO_CACHE = 'no_cache';
28  const CACHE_KEY = 'select';
29 
30  protected $id = null;
31  protected $type = null;
32  protected $parameters = [];
33  protected $parametersStripped = [];
34  protected $meta = [];
35  protected $cachedSql = [];
36 
37  private $adapter = null;
38 
39  private static $logger = null;
40 
41  /**
42  * Get the SelectStatement instance with the given id.
43  * If the id equals SelectStatement::NO_CACHE or is not cached, a new one will be created.
44  * @param $mapper RDBMapper instance used to retrieve the database adapter
45  * @param $id The statement id (optional, default: _SelectStatement::NO_CACHE_)
46  * @return SelectStatement
47  */
48  public static function get(RDBMapper $mapper, $id=self::NO_CACHE) {
49  $cache = ObjectFactory::getInstance('staticCache');
50  $cacheSection = self::getCacheSection($mapper->getType());
51  $cacheId = self::getCacheId($id);
52  if ($id == self::NO_CACHE || !$cache->exists($cacheSection, $cacheId)) {
53  $selectStmt = new SelectStatement($mapper, $id);
54  }
55  else {
56  $selectStmt = $cache->get($cacheSection, $cacheId);
57  $selectStmt->adapter = $mapper->getAdapter();
58  }
59  return $selectStmt;
60  }
61 
62  /**
63  * Constructor
64  * @param $mapper RDBMapper instance
65  * @param $id The statement id (optional, default: _SelectStatement::NO_CACHE_)
66  */
67  public function __construct(RDBMapper $mapper, $id=self::NO_CACHE) {
68  parent::__construct();
69  $this->id = $id;
70  $this->type = $mapper->getType();
71  $this->adapter = $mapper->getAdapter();
72  if (self::$logger == null) {
73  self::$logger = LogManager::getLogger(__CLASS__);
74  }
75  }
76 
77  /**
78  * Get the query string
79  * @return String
80  */
81  public function __toString() {
82  return $this->getSql();
83  }
84 
85  /**
86  * Get the id of the statement
87  * @return String
88  */
89  public function getId() {
90  return $this->id;
91  }
92 
93  /**
94  * Get the entity type associated with the statement
95  * @return String
96  */
97  public function getType() {
98  return $this->type;
99  }
100 
101  /**
102  * Check if the statement is cached already
103  * @return Boolean
104  */
105  public function isCached() {
106  $cache = ObjectFactory::getInstance('staticCache');
107  return $this->id == self::NO_CACHE ? false :
108  $cache->exists(self::getCacheSection($this->type), self::getCacheId($this->id));
109  }
110 
111  /**
112  * Add custom meta value
113  * @param $key
114  * @param $value
115  */
116  public function setMeta($key, $value) {
117  $this->meta[$key] = $value;
118  }
119 
120  /**
121  * Get custom meta value
122  * @param $key
123  * @return Associative array
124  */
125  public function getMeta($key) {
126  if (isset($this->meta[$key])) {
127  return $this->meta[$key];
128  }
129  return null;
130  }
131 
132  /**
133  * Set the parameter values to replace the placeholders with when doing the select
134  * @param $parameters Associative array with placeholders as keys
135  */
136  public function setParameters($parameters) {
137  $this->parameters = $parameters;
138  // store version with colons stripped
139  $this->parametersStripped = array_combine(array_map(function($name) {
140  return preg_replace('/^:/', '', $name);
141  }, array_keys($this->parameters)), $this->parameters);
142  }
143 
144  /**
145  * Get the select parameters
146  * @param $stripColons Indicates whether to strip the colon character from the parameter name or not (default: false)
147  * @return Array
148  */
149  public function getParameters($stripColons=false) {
150  return $stripColons ? $this->parametersStripped : $this->parameters;
151  }
152 
153  /**
154  * Execute a count query and return the row count
155  * @return Integer
156  */
157  public function getRowCount() {
158  $mapper = ObjectFactory::getInstance('persistenceFacade')->getMapper($this->type);
159  if ($this->table) {
160  $table = !is_array($this->table) ? $this-> table : key($this->table);
161  // use pk columns for counting
162  $countColumns = array_map(function($valueName) use ($mapper, $table) {
163  return $mapper->quoteIdentifier($table).'.'.$mapper->quoteIdentifier($mapper->getAttribute($valueName)->getColumn());
164  }, $mapper->getPkNames());
165  }
166  else {
167  // fallback if table is not defined: take first column
168  $adapter = $this->getAdapter();
169  $columns = $this->processSelect($adapter->getPlatform());
170  $countColumns = [$columns[$this->quantifier ? 1 : 0][0][0]];
171  }
172 
173  $countStatement = clone $this;
174  $countStatement->reset(Select::COLUMNS);
175  $countStatement->reset(Select::LIMIT);
176  $countStatement->reset(Select::OFFSET);
177  $countStatement->reset(Select::ORDER);
178  $countStatement->reset(Select::GROUP);
179  // remove all join columns to prevent errors because of missing group by for non aggregated columns (42000 - 1140)
180  $joins = $countStatement->joins->getJoins();
181  $countStatement->reset(Select::JOINS);
182  foreach ($joins as $key => $join) {
183  // re add join without cols
184  $countStatement->join($join["name"], $join["on"], [], $join["type"]);
185  }
186 
187  // create aggregation column
188  $countStatement->columns(['nRows' => new \Laminas\Db\Sql\Expression('COUNT('.$this->quantifier.' '.join(', ', $countColumns).')')]);
189 
190  $countStatement->id = $this->id != self::NO_CACHE ? $this->id.'-count' : self::NO_CACHE;
191  try {
192  $result = $countStatement->query();
193  $row = $result->fetch();
194  return $row['nRows'];
195  }
196  catch (\Exception $ex) {
197  self::$logger->error("The query: ".$countStatement->__toString()."\ncaused the following exception:\n".$ex->getMessage());
198  throw new PersistenceException("Error in persistent operation. See log file for details.");
199  }
200  }
201 
202  /**
203  * Put the statement into the cache
204  */
205  public function save() {
206  if ($this->id != self::NO_CACHE) {
207  $cache = ObjectFactory::getInstance('staticCache');
208  $cache->put(self::getCacheSection($this->type), self::getCacheId($this->id), $this);
209  }
210  }
211 
212  /**
213  * @see Select::join()
214  */
215  public function join($name, $on, $columns=self::SQL_STAR, $type=self::JOIN_INNER) {
216  // prevent duplicate joins
217  foreach ($this->joins->getJoins() as $join) {
218  if ($join['name'] == $name) {
219  return $this;
220  }
221  }
222  return parent::join($name, $on, $columns, $type);
223  }
224 
225  /**
226  * Add columns to the statement
227  * @param $columns Array of columns (@see Select::columns())
228  * @param $joinName The name of the join to which the columns belong
229  */
230  public function addColumns(array $columns, $joinName=null) {
231  if ($joinName === null) {
232  // add normal column
233  $this->columns = $this->columns + $columns;
234  }
235  else {
236  // add column to join
237  $joins = [];
238  foreach ($this->joins->getJoins() as $join) {
239  if ($join['name'] == $joinName || in_array($joinName, array_keys($join['name']))) {
240  $join['columns'] += $columns;
241  }
242  $joins[] = $join;
243  }
244  $this->joins->reset();
245  foreach ($joins as $join) {
246  parent::join($join['name'], $join['on'], $join['columns'], $join['type']);
247  }
248  }
249  }
250 
251  /**
252  * Get the alias names for a table name
253  * @param $tables
254  * @return Array
255  */
256  public function getAliasNames($table) {
257  $names = [];
258  if (is_array($this->table) && current($this->table) == $table) {
259  $names[] = key($this->table);
260  }
261  foreach ($this->joins->getJoins() as $join) {
262  $joinName = $join['name'];
263  if (is_array($joinName) && current($joinName) == $table) {
264  $names[] = key($joinName);
265  }
266  }
267  return $names;
268  }
269 
270  /**
271  * Get the sql string for this statement
272  * @return String
273  */
274  public function getSql() {
275  $cacheKey = self::getCacheId($this->id);
276  if (!isset($this->cachedSql[$cacheKey])) {
277  $sql = trim((new Sql($this->getAdapter()))->buildSqlString($this));
278  $this->cachedSql[$cacheKey] = $sql;
279  }
280  return $this->cachedSql[$cacheKey];
281  }
282 
283  /**
284  * Execute the statement
285  * @return PDOStatement
286  */
287  public function query() {
288  $adapter = $this->getAdapter();
289  $sql = $this->getSql();
290  // always update offset, since it's most likely not contained in the cache id
291  $sql = preg_replace('/OFFSET\s+[0-9]+/i', 'OFFSET '.$this->getRawState(Select::OFFSET), $sql);
292  $stmt = $adapter->getDriver()->getConnection()->prepare($sql);
293  if (self::$logger->isDebugEnabled()) {
294  self::$logger->debug("Execute statement: ".$sql);
295  self::$logger->debug($this->getParameters(true));
296  }
297  return $stmt->execute($this->getParameters(true))->getResource();
298  }
299 
300  /**
301  * Get the adapter corresponding to the statement's type
302  * @return AdapterInterface
303  */
304  protected function getAdapter() {
305  return $this->adapter;
306  }
307 
308  /**
309  * Get the cache section
310  * @param $type The type
311  * @return String
312  */
313  protected static function getCacheSection($type) {
314  return self::CACHE_KEY.'/'.$type;
315  }
316 
317  /**
318  * Get the compressed cache id from the id
319  * @param $id
320  * @return String
321  */
322  protected static function getCacheId($id) {
323  return md5($id);
324  }
325 
326  /**
327  * Serialization handlers
328  */
329 
330  public function __sleep() {
331  return ['id', 'type', 'meta', 'cachedSql', 'specifications'];
332  }
333 
334  public function __wakeup() {
335  parent::__construct();
336  if (self::$logger == null) {
337  self::$logger = LogManager::getLogger(__CLASS__);
338  }
339  }
340 }
341 ?>
RDBMapper defines the interface for mapper classes that map to relational databases.
Definition: RDBMapper.php:24
getParameters($stripColons=false)
Get the select parameters.
getRowCount()
Execute a count query and return the row count.
getType()
Get the entity type that this mapper handles.
PersistenceException signals an exception in the persistence service.
quoteIdentifier($identifier)
Add quotation to a given identifier (like column name).
save()
Put the statement into the cache.
getPkNames()
Get the names of the primary key values.
setMeta($key, $value)
Add custom meta value.
static getCacheSection($type)
Get the cache section.
getSql()
Get the sql string for this statement.
static getCacheId($id)
Get the compressed cache id from the id.
addColumns(array $columns, $joinName=null)
Add columns to the statement.
join($name, $on, $columns=self::SQL_STAR, $type=self::JOIN_INNER)
isCached()
Check if the statement is cached already.
getAliasNames($table)
Get the alias names for a table name.
static getLogger($name)
Get the logger with the given name.
Definition: LogManager.php:37
static getInstance($name, $dynamicConfiguration=[])
getAdapter()
Get the adapter corresponding to the statement's type.
setParameters($parameters)
Set the parameter values to replace the placeholders with when doing the select.
getAttribute($name)
Get the definition for an attribute.
getAdapter()
Get the database adapter.
LogManager is used to retrieve Logger instances.
Definition: LogManager.php:20
__construct(RDBMapper $mapper, $id=self::NO_CACHE)
Constructor.
ObjectFactory implements the service locator pattern by wrapping a Factory instance and providing sta...
getMeta($key)
Get custom meta value.
getId()
Get the id of the statement.
getType()
Get the entity type associated with the statement.