AbstractQuery::build()

Completed

Push — 3.x ( 9be43c...53c3d5 )

by Paul

created 2017-02-19 15:34 UTC

↳ Parent: AbstractQuery

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	1
c	0
b	0
f	0
ccs	0
cts	0
cp	0
nc	1

<?php
/**
 *
 * This file is part of Aura for PHP.
 *
 * @license http://opensource.org/licenses/bsd-license.php BSD
 *
 */
namespace Aura\SqlQuery;

use Aura\SqlQuery\Common\SubselectInterface;

/**
 *
 * Abstract query object.
 *
 * @package Aura.SqlQuery
 *
 */
abstract class AbstractQuery
{
    /**
     *
     * Data to be bound to the query.
     *
     * @var array
     *
     */
    protected $bind_values = array();

    /**
     *
     * The list of WHERE conditions.
     *
     * @var array
     *
     */
    protected $where = array();

    /**
     *
     * ORDER BY these columns.
     *
     * @var array
     *
     */
    protected $order_by = array();

    /**
     *
     * The list of flags.
     *
     * @var array
     *
     */
    protected $flags = array();

    /**
     *
     * A helper for quoting identifier names.
     *
     * @var Quoter
     *
     */
    protected $quoter;

    /**
     *
     * Prefix to use on placeholders for "sequential" bound values; used for
     * deconfliction when merging bound values from sub-selects, etc.
     *
     * @var mixed
     *
     */
    protected $seq_bind_prefix = '';

    /**
     *
     * Constructor.
     *
     * @param Quoter $quoter A helper for quoting identifier names.
     *
     * @param string $seq_bind_prefix A prefix for rewritten sequential-binding
     * placeholders (@see getSeqPlaceholder()).
     *
     */
    public function __construct(Quoter $quoter, $seq_bind_prefix = '')
    {
        $this->quoter = $quoter;
        $this->seq_bind_prefix = $seq_bind_prefix;
    }

    /**
     *
     * Returns the prefix for rewritten sequential-binding placeholders
     * (@see getSeqPlaceholder()).
     *
     * @return string
     *
     */
    public function getSeqBindPrefix()
    {
        return $this->seq_bind_prefix;
    }

    /**
     *
     * Returns this query object as an SQL statement string.
     *
     * @return string
     *
     */
    public function __toString()
    {
        return $this->getStatement();
    }

    /**
     *
     * Returns this query object as an SQL statement string.
     *
     * @return string
     *
     */
    public function getStatement()
    {
        return $this->build();
    }

    /**
     *
     * Builds this query object into a string.
     *
     * @return string
     *
     */
    abstract protected function build();

    /**
     *
     * Returns the prefix to use when quoting identifier names.
     *
     * @return string
     *
     */
    public function getQuoteNamePrefix()
    {
        return $this->quoter->getQuoteNamePrefix();
    }

    /**
     *
     * Returns the suffix to use when quoting identifier names.
     *
     * @return string
     *
     */
    public function getQuoteNameSuffix()
    {
        return $this->quoter->getQuoteNameSuffix();
    }

    /**
     *
     * Returns an array as an indented comma-separated values string.
     *
     * @param array $list The values to convert.
     *
     * @return string
     *
     */
    protected function indentCsv(array $list)
    {
        return PHP_EOL . '    '
             . implode(',' . PHP_EOL . '    ', $list);
    }

    /**
     *
     * Returns an array as an indented string.
     *
     * @param array $list The values to convert.
     *
     * @return string
     *
     */
    protected function indent(array $list)
    {
        return PHP_EOL . '    '
             . implode(PHP_EOL . '    ', $list);
    }

    /**
     *
     * Binds multiple values to placeholders; merges with existing values.
     *
     * @param array $bind_values Values to bind to placeholders.
     *
     * @return $this
     *
     */
    public function bindValues(array $bind_values)
    {
        // array_merge() renumbers integer keys, which is bad for
        // question-mark placeholders
        foreach ($bind_values as $key => $val) {
            $this->bindValue($key, $val);
        }
        return $this;
    }

    /**
     *
     * Binds a single value to the query.
     *
     * @param string $name The placeholder name or number.
     *
     * @param mixed $value The value to bind to the placeholder.
     *
     * @return $this
     *
     */
    public function bindValue($name, $value)
    {
        $this->bind_values[$name] = $value;
        return $this;
    }

    /**
     *
     * Gets the values to bind to placeholders.
     *
     * @return array
     *
     */
    public function getBindValues()
    {
        return $this->bind_values;
    }

    /**
     *
     * Reset all values bound to named placeholders.
     *
     * @return $this
     *
     */
    public function resetBindValues()
    {
        $this->bind_values = array();
        return $this;
    }

    /**
     *
     * Builds the flags as a space-separated string.
     *
     * @return string
     *
     */
    protected function buildFlags()
    {
        if (empty($this->flags)) {
            return ''; // not applicable
        }

        return ' ' . implode(' ', array_keys($this->flags));
    }

    /**
     *
     * Sets or unsets specified flag.
     *
     * @param string $flag Flag to set or unset
     *
     * @param bool $enable Flag status - enabled or not (default true)
     *
     * @return null
     *
     */
    protected function setFlag($flag, $enable = true)
    {
        if ($enable) {
            $this->flags[$flag] = true;
        } else {
            unset($this->flags[$flag]);
        }
    }

    /**
     *
     * Returns true if the specified flag was enabled by setFlag().
     *
     * @param string $flag Flag to check
     *
     * @return bool
     *
     */
    protected function hasFlag($flag)
    {
        return isset($this->flags[$flag]);
    }

    /**
     *
     * Reset all query flags.
     *
     * @return $this
     *
     */
    public function resetFlags()
    {
        $this->flags = array();
        return $this;
    }

    /**
     *
     * Adds a WHERE condition to the query by AND or OR. If the condition has
     * ?-placeholders, additional arguments to the method will be bound to
     * those placeholders sequentially.
     *
     * @param string $andor Add the condition using this operator, typically
     * 'AND' or 'OR'.
     *
     * @param string $cond The WHERE condition.
     *
     * @param array ...$bind arguments to bind to placeholders
     *
     * @return $this
     *
     */
    protected function addWhere($andor, $cond, ...$bind)
    {
        $this->addClauseCondWithBind('where', $andor, $cond, $bind);
        return $this;
    }

    /**
     *
     * Adds conditions and binds values to a clause.
     *
     * @param string $clause The clause to work with, typically 'where' or
     * 'having'.
     *
     * @param string $andor Add the condition using this operator, typically
     * 'AND' or 'OR'.
     *
     * @param string $cond The WHERE condition.

     * @param array $bind arguments to bind to placeholders
     *
     * @return null
     *
     */
    protected function addClauseCondWithBind($clause, $andor, $cond, $bind)
    {
        $cond = $this->rebuildCondAndBindValues($cond, $bind);

        // add condition to clause; eg $this->where or $this->having
        $clause =& $this->$clause;
        if ($clause) {
            $clause[] = "$andor $cond";
        } else {
            $clause[] = $cond;
        }
    }

    /**
     *
     * Rebuilds a condition string, replacing sequential placeholders with
     * named placeholders, and binding the sequential values to the named
     * placeholders.
     *
     * @param string $cond The condition with sequential placeholders.
     *
     * @param array $bind_values The values to bind to the sequential
     * placeholders under their named versions.
     *
     * @return string The rebuilt condition string.
     *
     */
    protected function rebuildCondAndBindValues($cond, array $bind_values)
    {
        $cond = $this->quoter->quoteNamesIn($cond);

        // bind values against ?-mark placeholders, but because PDO is finicky
        // about the numbering of sequential placeholders, convert each ?-mark
        // to a named placeholder
        $parts = preg_split('/(\?)/', $cond, null, PREG_SPLIT_DELIM_CAPTURE);
        foreach ($parts as $key => $val) {
            if ($val != '?') {
                continue;
            }

            $bind_value = array_shift($bind_values);
            if ($bind_value instanceof SubselectInterface) {
                $parts[$key] = $bind_value->getStatement();
                $this->bind_values = array_merge(
                    $this->bind_values,
                    $bind_value->getBindValues()
                );
                continue;
            }

            $placeholder = $this->getSeqPlaceholder();
            $parts[$key] = ':' . $placeholder;
            $this->bind_values[$placeholder] = $bind_value;
        }

        $cond = implode('', $parts);
        return $cond;
    }

    /**
     *
     * Gets the current sequential placeholder name.
     *
     * @return string
     *
     */
    protected function getSeqPlaceholder()
    {
        $i = count($this->bind_values) + 1;
        return $this->seq_bind_prefix . "_{$i}_";
    }

    /**
     *
     * Builds the `WHERE` clause of the statement.
     *
     * @return string
     *
     */
    protected function buildWhere()
    {
        if (empty($this->where)) {
            return ''; // not applicable
        }

        return PHP_EOL . 'WHERE' . $this->indent($this->where);
    }

    /**
     *
     * Adds a column order to the query.
     *
     * @param array $spec The columns and direction to order by.
     *
     * @return $this
     *
     */
    protected function addOrderBy(array $spec)
    {
        foreach ($spec as $col) {
            $this->order_by[] = $this->quoter->quoteNamesIn($col);
        }
        return $this;
    }

    /**
     *
     * Builds the `ORDER BY ...` clause of the statement.
     *
     * @return string
     *
     */
    protected function buildOrderBy()
    {
        if (empty($this->order_by)) {
            return ''; // not applicable
        }

        return PHP_EOL . 'ORDER BY' . $this->indentCsv($this->order_by);
    }
}


1		<?php
2		/**
3		*
4		* This file is part of Aura for PHP.
5		*
6		* @license http://opensource.org/licenses/bsd-license.php BSD
7		*
8		*/
9		namespace Aura\SqlQuery;
10
11		use Aura\SqlQuery\Common\SubselectInterface;
12
13		/**
14		*
15		* Abstract query object.
16		*
17		* @package Aura.SqlQuery
18		*
19		*/
20		abstract class AbstractQuery
21		{
22		/**
23		*
24		* Data to be bound to the query.
25		*
26		* @var array
27		*
28		*/
29		protected $bind_values = array();
30
31		/**
32		*
33		* The list of WHERE conditions.
34		*
35		* @var array
36		*
37		*/
38		protected $where = array();
39
40		/**
41		*
42		* ORDER BY these columns.
43		*
44		* @var array
45		*
46		*/
47		protected $order_by = array();
48
49		/**
50		*
51		* The list of flags.
52		*
53		* @var array
54		*
55		*/
56		protected $flags = array();
57
58		/**
59		*
60		* A helper for quoting identifier names.
61		*
62		* @var Quoter
63		*
64		*/
65		protected $quoter;
66
67		/**
68		*
69		* Prefix to use on placeholders for "sequential" bound values; used for
70		* deconfliction when merging bound values from sub-selects, etc.
71		*
72		* @var mixed
73		*
74		*/
75		protected $seq_bind_prefix = '';
76
77		/**
78		*
79		* Constructor.
80		*
81		* @param Quoter $quoter A helper for quoting identifier names.
82		*
83		* @param string $seq_bind_prefix A prefix for rewritten sequential-binding
84		* placeholders (@see getSeqPlaceholder()).
85		*
86		*/
87	408	public function __construct(Quoter $quoter, $seq_bind_prefix = '')
88		{
89	408	$this->quoter = $quoter;
90	408	$this->seq_bind_prefix = $seq_bind_prefix;
91	408	}
92
93		/**
94		*
95		* Returns the prefix for rewritten sequential-binding placeholders
96		* (@see getSeqPlaceholder()).
97		*
98		* @return string
99		*
100		*/
101	1	public function getSeqBindPrefix()
102		{
103	1	return $this->seq_bind_prefix;
104		}
105
106		/**
107		*
108		* Returns this query object as an SQL statement string.
109		*
110		* @return string
111		*
112		*/
113	243	public function __toString()
114		{
115	243	return $this->getStatement();
116		}
117
118		/**
119		*
120		* Returns this query object as an SQL statement string.
121		*
122		* @return string
123		*
124		*/
125	69	public function getStatement()
126		{
127	69	return $this->build();
128		}
129
130		/**
131		*
132		* Builds this query object into a string.
133		*
134		* @return string
135		*
136		*/
137		abstract protected function build();
138
139		/**
140		*
141		* Returns the prefix to use when quoting identifier names.
142		*
143		* @return string
144		*
145		*/
146	257	public function getQuoteNamePrefix()
147		{
148	257	return $this->quoter->getQuoteNamePrefix();
149		}
150
151		/**
152		*
153		* Returns the suffix to use when quoting identifier names.
154		*
155		* @return string
156		*
157		*/
158	257	public function getQuoteNameSuffix()
159		{
160	257	return $this->quoter->getQuoteNameSuffix();
161		}
162
163		/**
164		*
165		* Returns an array as an indented comma-separated values string.
166		*
167		* @param array $list The values to convert.
168		*
169		* @return string
170		*
171		*/
172	225	protected function indentCsv(array $list)
173		{
174	225	return PHP_EOL . ' '
175	225	. implode(',' . PHP_EOL . ' ', $list);
176		}
177
178		/**
179		*
180		* Returns an array as an indented string.
181		*
182		* @param array $list The values to convert.
183		*
184		* @return string
185		*
186		*/
187	71	protected function indent(array $list)
188		{
189	71	return PHP_EOL . ' '
190	71	. implode(PHP_EOL . ' ', $list);
191		}
192
193		/**
194		*
195		* Binds multiple values to placeholders; merges with existing values.
196		*
197		* @param array $bind_values Values to bind to placeholders.
198		*
199		* @return $this
200		*
201		*/
202	31	public function bindValues(array $bind_values)
203		{
204		// array_merge() renumbers integer keys, which is bad for
205		// question-mark placeholders
206	31	foreach ($bind_values as $key => $val) {
207	31	$this->bindValue($key, $val);
208	31	}
209	31	return $this;
210		}
211
212		/**
213		*
214		* Binds a single value to the query.
215		*
216		* @param string $name The placeholder name or number.
217		*
218		* @param mixed $value The value to bind to the placeholder.
219		*
220		* @return $this
221		*
222		*/
223	72	public function bindValue($name, $value)
224		{
225	72	$this->bind_values[$name] = $value;
226	72	return $this;
227		}
228
229		/**
230		*
231		* Gets the values to bind to placeholders.
232		*
233		* @return array
234		*
235		*/
236	126	public function getBindValues()
237		{
238	126	return $this->bind_values;
239		}
240
241		/**
242		*
243		* Reset all values bound to named placeholders.
244		*
245		* @return $this
246		*
247		*/
248	15	public function resetBindValues()
249		{
250	15	$this->bind_values = array();
251	15	return $this;
252		}
253
254		/**
255		*
256		* Builds the flags as a space-separated string.
257		*
258		* @return string
259		*
260		*/
261	258	protected function buildFlags()
262		{
263	258	if (empty($this->flags)) {
264	220	return ''; // not applicable
265		}
266
267	38	return ' ' . implode(' ', array_keys($this->flags));
268		}
269
270		/**
271		*
272		* Sets or unsets specified flag.
273		*
274		* @param string $flag Flag to set or unset
275		*
276		* @param bool $enable Flag status - enabled or not (default true)
277		*
278		* @return null
279		*
280		*/
281	43	protected function setFlag($flag, $enable = true)
282		{
283	43	if ($enable) {
284	43	$this->flags[$flag] = true;
285	43	} else {
286	5	unset($this->flags[$flag]);
287		}
288	43	}
289
290		/**
291		*
292		* Returns true if the specified flag was enabled by setFlag().
293		*
294		* @param string $flag Flag to check
295		*
296		* @return bool
297		*
298		*/
299	10	protected function hasFlag($flag)
300		{
301	10	return isset($this->flags[$flag]);
302		}
303
304		/**
305		*
306		* Reset all query flags.
307		*
308		* @return $this
309		*
310		*/
311	20	public function resetFlags()
312		{
313	20	$this->flags = array();
314	20	return $this;
315		}
316
317		/**
318		*
319		* Adds a WHERE condition to the query by AND or OR. If the condition has
320		* ?-placeholders, additional arguments to the method will be bound to
321		* those placeholders sequentially.
322		*
323		* @param string $andor Add the condition using this operator, typically
324		* 'AND' or 'OR'.
325		*
326		* @param string $cond The WHERE condition.
327		*
328		* @param array ...$bind arguments to bind to placeholders
329		*
330		* @return $this
331		*
332		*/
333	60	protected function addWhere($andor, $cond, ...$bind)
334		{
335	60	$this->addClauseCondWithBind('where', $andor, $cond, $bind);
336	60	return $this;
337		}
338
339		/**
340		*
341		* Adds conditions and binds values to a clause.
342		*
343		* @param string $clause The clause to work with, typically 'where' or
344		* 'having'.
345		*
346		* @param string $andor Add the condition using this operator, typically
347		* 'AND' or 'OR'.
348		*
349		* @param string $cond The WHERE condition.
350
351		* @param array $bind arguments to bind to placeholders
352		*
353		* @return null
354		*
355		*/
356	70	protected function addClauseCondWithBind($clause, $andor, $cond, $bind)
357		{
358	70	$cond = $this->rebuildCondAndBindValues($cond, $bind);
359
360		// add condition to clause; eg $this->where or $this->having
361	70	$clause =& $this->$clause;
362	70	if ($clause) {
363	49	$clause[] = "$andor $cond";
364	49	} else {
365	70	$clause[] = $cond;
366		}
367	70	}
368
369		/**
370		*
371		* Rebuilds a condition string, replacing sequential placeholders with
372		* named placeholders, and binding the sequential values to the named
373		* placeholders.
374		*
375		* @param string $cond The condition with sequential placeholders.
376		*
377		* @param array $bind_values The values to bind to the sequential
378		* placeholders under their named versions.
379		*
380		* @return string The rebuilt condition string.
381		*
382		*/
383	120	protected function rebuildCondAndBindValues($cond, array $bind_values)
384		{
385	120	$cond = $this->quoter->quoteNamesIn($cond);
386
387		// bind values against ?-mark placeholders, but because PDO is finicky
388		// about the numbering of sequential placeholders, convert each ?-mark
389		// to a named placeholder
390	120	$parts = preg_split('/(\?)/', $cond, null, PREG_SPLIT_DELIM_CAPTURE);
391	120	foreach ($parts as $key => $val) {
392	120	if ($val != '?') {
393	120	continue;
394		}
395
396	80	$bind_value = array_shift($bind_values);
397	80	if ($bind_value instanceof SubselectInterface) {
398	10	$parts[$key] = $bind_value->getStatement();
399	10	$this->bind_values = array_merge(
400	10	$this->bind_values,
401	10	$bind_value->getBindValues()
402	10	);
403	10	continue;
404		}
405
406	75	$placeholder = $this->getSeqPlaceholder();
407	75	$parts[$key] = ':' . $placeholder;
408	75	$this->bind_values[$placeholder] = $bind_value;
409	120	}
410
411	120	$cond = implode('', $parts);
412	120	return $cond;
413		}
414
415		/**
416		*
417		* Gets the current sequential placeholder name.
418		*
419		* @return string
420		*
421		*/
422	75	protected function getSeqPlaceholder()
423		{
424	75	$i = count($this->bind_values) + 1;
425	75	return $this->seq_bind_prefix . "_{$i}_";
426		}
427
428		/**
429		*
430		* Builds the `WHERE` clause of the statement.
431		*
432		* @return string
433		*
434		*/
435	211	protected function buildWhere()
436		{
437	211	if (empty($this->where)) {
438	156	return ''; // not applicable
439		}
440
441	60	return PHP_EOL . 'WHERE' . $this->indent($this->where);
442		}
443
444		/**
445		*
446		* Adds a column order to the query.
447		*
448		* @param array $spec The columns and direction to order by.
449		*
450		* @return $this
451		*
452		*/
453	9	protected function addOrderBy(array $spec)
454		{
455	9	foreach ($spec as $col) {
456	9	$this->order_by[] = $this->quoter->quoteNamesIn($col);
457	9	}
458	9	return $this;
459		}
460
461		/**
462		*
463		* Builds the `ORDER BY ...` clause of the statement.
464		*
465		* @return string
466		*
467		*/
468	211	protected function buildOrderBy()
469		{
470	211	if (empty($this->order_by)) {
471	202	return ''; // not applicable
472		}
473
474	9	return PHP_EOL . 'ORDER BY' . $this->indentCsv($this->order_by);
475		}
476		}
477

auraphp / Aura.SqlQuery

Push — 3.x ( 9be43c...53c3d5 )

AbstractQuery::build()

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like