WPDBAdapter::stringifyValue() - Code Metrics - Inspection of "Feature/gh13 extend where" - gin0115/pixie-wpdb - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — master (#20)

by Glynn

created 2022-01-19 09:21 UTC

WPDBAdapter::stringifyValue() A

↳ Parent: WPDBAdapter

Complexity

Conditions	4
Paths	4

Size

Total Lines	13
Code Lines	6

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	1

Metric	Value
eloc	6
c	1
b	0
f	1
dl	0
loc	13
rs	10
cc	4
nc	4
nop	1

<?php

namespace Pixie\QueryBuilder;

use Closure;
use Pixie\Binding;
use Pixie\Exception;

use function is_bool;

use Pixie\Connection;

use function is_float;

use Pixie\QueryBuilder\Raw;
use Pixie\QueryBuilder\NestedCriteria;

class WPDBAdapter
{
    /**
     * @var string
     */
    protected $sanitizer = '';

    /**
     * @var \Pixie\Connection
     */
    protected $connection;

    /**
     * @var \Viocon\Container
     */
    protected $container;

    public function __construct(Connection $connection)
    {
        $this->connection = $connection;
        $this->container  = $this->connection->getContainer();
    }

    /**
     * Build select query string and bindings
     *
     * @param array<string|Closure, mixed|mixed[]> $statements
     *
     * @throws Exception
     *
     * @return array{sql:string,bindings:mixed[]}
     */
    public function select(array $statements): array
    {
        if (!array_key_exists('tables', $statements)) {
            throw new Exception('No table specified.', 3);
        } elseif (!array_key_exists('selects', $statements)) {
            $statements['selects'][] = '*';
        }

        // From
        $tables = $this->arrayStr($statements['tables'], ', ');
        // Select
        $selects = $this->arrayStr($statements['selects'], ', ');

        // Wheres
        list($whereCriteria, $whereBindings) = $this->buildCriteriaWithType($statements, 'wheres', 'WHERE');
        // Group bys
        $groupBys = '';
        if (isset($statements['groupBys']) && $groupBys = $this->arrayStr($statements['groupBys'], ', ')) {
            $groupBys = 'GROUP BY ' . $groupBys;
        }

        // Order bys
        $orderBys = '';
        if (isset($statements['orderBys']) && is_array($statements['orderBys'])) {
            foreach ($statements['orderBys'] as $orderBy) {
                $field = $this->wrapSanitizer($orderBy['field']);
                if ($field instanceof Closure) {
                    continue;
                }
                $orderBys .= $field . ' ' . $orderBy['type'] . ', ';
            }

            if ($orderBys = trim($orderBys, ', ')) {
                $orderBys = 'ORDER BY ' . $orderBys;
            }
        }

        // Limit and offset
        $limit  = isset($statements['limit']) ? 'LIMIT ' . (int) $statements['limit'] : '';
        $offset = isset($statements['offset']) ? 'OFFSET ' . (int) $statements['offset'] : '';

        // Having
        list($havingCriteria, $havingBindings) = $this->buildCriteriaWithType($statements, 'havings', 'HAVING');

        // Joins
        $joinString = $this->buildJoin($statements);

        /** @var string[] */
        $sqlArray = [
            'SELECT' . (isset($statements['distinct']) ? ' DISTINCT' : ''),
            $selects,
            'FROM',
            $tables,
            $joinString,
            $whereCriteria,
            $groupBys,
            $havingCriteria,
            $orderBys,
            $limit,
            $offset,
        ];

        $sql = $this->concatenateQuery($sqlArray);

        $bindings = array_merge(
            $whereBindings,
            $havingBindings
        );

        return compact('sql', 'bindings');
    }

    /**
     * Build just criteria part of the query
     *
     * @param array<string|Closure, mixed|mixed[]> $statements
     * @param bool $bindValues
     *
     * @return array{sql:string[]|string, bindings:array<mixed>}
     */
    public function criteriaOnly(array $statements, bool $bindValues = true): array
    {
        $sql = $bindings = [];
        if (!isset($statements['criteria'])) {
            return compact('sql', 'bindings');
        }

        list($sql, $bindings) = $this->buildCriteria($statements['criteria'], $bindValues);

        return compact('sql', 'bindings');
    }

    /**
     * Build a generic insert/ignore/replace query
     *
     * @param array<string|Closure, mixed|mixed[]> $statements
     * @param array<string, mixed> $data
     * @param string $type
     *
     * @return array{sql:string, bindings:mixed[]}
     *
     * @throws Exception
     */
    private function doInsert(array $statements, array $data, string $type): array
    {
        if (!isset($statements['tables'])) {
            throw new Exception('No table specified', 3);
        }

        $table = end($statements['tables']);

        $bindings = $keys = $values = [];

        foreach ($data as $key => $value) {
            $keys[] = $key;

            // Handle value as bindings
            $isBindings = $value instanceof Binding;
            // If this is a raw binding, extract the Raw and replace value.
            if ($isBindings && $value->isRaw()) {
                $value = $value->getValue();
            }

            if ($value instanceof Raw) {
                $values[] = $this->parseRaw($value);
            } elseif ($isBindings) {
                $values[]   =  $value->getType();
                $bindings[] = $value->getValue();
            } else {
                $values[]   =  $this->inferType($value);
                $bindings[] = $value;
            }
        }

        $sqlArray = [
        $type . ' INTO',
        $this->wrapSanitizer($table),
        '(' . $this->arrayStr($keys, ',') . ')',
        'VALUES',
        '(' . $this->arrayStr($values, ',') . ')',
        ];

        if (isset($statements['onduplicate'])) {
            if (count($statements['onduplicate']) < 1) {
                throw new Exception('No data given.', 4);
            }
            list($updateStatement, $updateBindings) = $this->getUpdateStatement($statements['onduplicate']);
            $sqlArray[]                             = 'ON DUPLICATE KEY UPDATE ' . $updateStatement;
            $bindings                               = array_merge($bindings, $updateBindings);
        }

        $sql = $this->concatenateQuery($this->stringifyValues($sqlArray));

        return compact('sql', 'bindings');
    }

    /**
     * Attempts to stringify an array of values.
     *
     * @param array<string|int, string|Closure> $values
     *
     * @return string[]
     */
    protected function stringifyValues(array $values): array
    {
        return array_filter(array_map([$this, 'stringifyValue'], $values));
    }

    /**
     * Attempts to stringify a single of values.
     *
     * @param string|Closure|Raw $value
     *
     * @return string|null
     */
    protected function stringifyValue($value): ?string
    {
        if ($value instanceof Closure) {
            $value = $value();

            return is_string($value) ? $value : null;
        }

        if ($value instanceof Raw) {
            return $this->parseRaw($value);
        }

        return $value;
    }

    /**
     * Build Insert query
     *
     * @param array<string|Closure, mixed|mixed[]> $statements
     * @param array<string, mixed> $data $data
     *
     * @return array{sql:string, bindings:mixed[]}
     *
     * @throws Exception
     */
    public function insert($statements, array $data)
    {
        return $this->doInsert($statements, $data, 'INSERT');
    }

    /**
     * Build Insert Ignore query
     *
     * @param array<string|Closure, mixed|mixed[]> $statements
     * @param array<string, mixed> $data $data
     *
     * @return array{sql:string, bindings:mixed[]}
     *
     * @throws Exception
     */
    public function insertIgnore($statements, array $data)
    {
        return $this->doInsert($statements, $data, 'INSERT IGNORE');
    }

    /**
     * Build Insert Ignore query
     *
     * @param array<string|Closure, mixed|mixed[]> $statements
     * @param array<string, mixed> $data $data
     *
     * @return array{sql:string, bindings:mixed[]}
     *
     * @throws Exception
     */
    public function replace($statements, array $data)
    {
        return $this->doInsert($statements, $data, 'REPLACE');
    }

    /**
     * Build fields assignment part of SET ... or ON DUBLICATE KEY UPDATE ... statements
     *
     * @param array<string, mixed> $data
     *
     * @return array{0:string,1:mixed[]}
     */
    private function getUpdateStatement(array $data): array
    {
        $bindings  = [];
        $statement = '';

        foreach ($data as $key => $value) {
            $isBindings = $value instanceof Binding;
            // If this is a raw binding, extract the Raw and replace value.
            if ($isBindings && $value->isRaw()) {
                $value = $value->getValue();
            }

            if ($value instanceof Raw) {
                $statement .= $this->stringifyValue($this->wrapSanitizer($key)) . '=' . $value . ',';
            } elseif ($isBindings) {
                $statement .= $this->stringifyValue($this->wrapSanitizer($key)) . sprintf('=%s,', $value->getType());
                $bindings[] = $value->getValue();
            } else {
                $statement .= $this->stringifyValue($this->wrapSanitizer($key)) . sprintf('=%s,', $this->inferType($value));
                $bindings[] = $value;
            }
        }

        $statement = trim($statement, ',');

        return [$statement, $bindings];
    }

    /**
     * Build update query
     *
     * @param array<string|Closure, mixed|mixed[]> $statements
     * @param array<string, mixed> $data
     *
     * @return array{sql:string, bindings:mixed[]}
     *
     * @throws Exception
     */
    public function update($statements, array $data)
    {
        if (!isset($statements['tables'])) {
            throw new Exception('No table specified', 3);
        } elseif (count($data) < 1) {
            throw new Exception('No data given.', 4);
        }

        $table = end($statements['tables']);

        // Update statement
        list($updateStatement, $bindings) = $this->getUpdateStatement($data);

        // Wheres
        list($whereCriteria, $whereBindings) = $this->buildCriteriaWithType($statements, 'wheres', 'WHERE');

        // Limit
        $limit = isset($statements['limit']) ? 'LIMIT ' . $statements['limit'] : '';

        $sqlArray = [
            'UPDATE',
            $this->wrapSanitizer($table),
            'SET ' . $updateStatement,
            $whereCriteria,
            $limit,
        ];

        $sql = $this->concatenateQuery($this->stringifyValues($sqlArray));

        $bindings = array_merge($bindings, $whereBindings);

        return compact('sql', 'bindings');
    }

    /**
     * Build delete query
     *
     * @param array<string|Closure, mixed|mixed[]> $statements
     *
     * @return array{sql:string, bindings:mixed[]}
     *
     * @throws Exception
     */
    public function delete($statements)
    {
        if (!isset($statements['tables'])) {
            throw new Exception('No table specified', 3);
        }

        $table = end($statements['tables']);
        // Ensure table name is a string
        $table = $this->stringifyValue($this->wrapSanitizer($table));
        if (null === $table) {
            throw new Exception('Table must be a valid string.', 5);
        }

        // Wheres
        list($whereCriteria, $whereBindings) = $this->buildCriteriaWithType($statements, 'wheres', 'WHERE');

        // Limit
        $limit = isset($statements['limit']) ? 'LIMIT ' . $statements['limit'] : '';


        $sqlArray = ['DELETE FROM', $table, $whereCriteria];
        $sql      = $this->concatenateQuery($sqlArray);
        $bindings = $whereBindings;

        return compact('sql', 'bindings');
    }

    /**
     * Array concatenating method, like implode.
     * But it does wrap sanitizer and trims last glue
     *
     * @param array<string|int, string> $pieces
     * @param string $glue
     *
     * @return string
     */
    protected function arrayStr(array $pieces, string $glue): string
    {
        $str = '';
        foreach ($pieces as $key => $piece) {
            if (!is_int($key)) {
                $piece = $key . ' AS ' . $piece;
            }

            $str .= $piece . $glue;
        }

        return trim($str, $glue);
    }

    /**
     * Join different part of queries with a space.
     *
     * @param array<string|int, string> $pieces
     *
     * @return string
     */
    protected function concatenateQuery(array $pieces): string
    {
        $str = '';
        foreach ($pieces as $piece) {
            $str = trim($str) . ' ' . trim($piece);
        }

        return trim($str);
    }

    /**
     * Gets the type of a value, either from a binding or infered
     *
     * @param mixed $value
     * @return string
     */
    public function getType($value): string
    {
        return $value instanceof Binding && $value->getType() !== null
            ? $value->getType() : $this->inferType($value) ;
    }

    /**
     * Get the value from a possible Bindings object.
     *
     * @param mixed $value
     * @return mixed
     */
    public function getValue($value)
    {
        return $value instanceof Binding ? $value->getValue() : $value;
    }

    /**
     * Attempts to parse a raw query, if bindings are defined then they will be bound first.
     *
     * @param Raw $raw
     * @requires string
     */
    public function parseRaw(Raw $raw): string
    {
        $bindings = $raw->getBindings();
        return 0 === count($bindings)
            ? (string) $raw
            : $this->interpolateQuery($raw->getValue(), $bindings);
    }

    /**
     * Interpolates a query
     *
     * @param string $query
     * @param array<mixed> $bindings
     * @return string
     */
    public function interpolateQuery(string $query, array $bindings = []): string
    {
        if (0 === count($bindings)) {
            return $query;
        }


        $bindings = array_map([$this, 'getValue'], $bindings);
        $query = $this->connection->getDbInstance()->prepare($query, $bindings) ;
        return is_string($query) ? $query : '';

    }

    /**
     * Build generic criteria string and bindings from statements, like "a = b and c = ?"
     *
     * @param array<string|Closure, mixed|mixed[]> $statements
     * @param bool $bindValues
     *
     * @return array{0:string,1:string[]}
     */
    protected function buildCriteria(array $statements, bool $bindValues = true): array
    {
        $criteria = '';
        $bindings = [];
        foreach ($statements as $statement) {
            $key   = $statement['key'];
            $value = $statement['value'];

            // If the value is a Raw Binding, cast to raw
            if ($value instanceof Binding && Binding::RAW === $value->getType()) {
                /** @var Raw */
                $value = $value->getValue();
            }

            if (is_null($value) && $key instanceof Closure) {
                // We have a closure, a nested criteria

                // Build a new NestedCriteria class, keep it by reference so any changes made
                // in the closure should reflect here
                $nestedCriteria = $this->container->build(NestedCriteria::class, [$this->connection]);

                $nestedCriteria = &$nestedCriteria;
                // Call the closure with our new nestedCriteria object
                $key($nestedCriteria);
                // Get the criteria only query from the nestedCriteria object
                $queryObject = $nestedCriteria->getQuery('criteriaOnly', true);
                // Merge the bindings we get from nestedCriteria object
                $bindings = array_merge($bindings, $queryObject->getBindings());
                // Append the sql we get from the nestedCriteria object
                $criteria .= $statement['joiner'] . ' (' . $queryObject->getSql() . ') ';
            } elseif (is_array($value)) {
                // where_in or between like query
                $criteria .= $statement['joiner'] . ' ' . $key . ' ' . $statement['operator'];

                switch ($statement['operator']) {
                    case 'BETWEEN':
                        $bindings = array_merge($bindings, $statement['value']);
                        $criteria .= sprintf(
                            ' %s AND %s ',
                            $this->getType($value[0]),
                            $this->getType($value[1])
                        );

                        // Maybe cast the values bindings.
                        $value[0] = $this->getValue($value[0]);
                        $value[1] = $this->getValue($value[1]);
                        break;
                    default:
                        $valuePlaceholder = '';
                        foreach ($statement['value'] as $subValue) {
                            // Get its value.
                            if ($this->getValue($subValue) instanceof Raw) {
                                /** @var Raw $subValue */
                                $subValue = $this->getValue($subValue);
                                $valuePlaceholder .= sprintf('%s, ', $this->parseRaw($subValue));
                                continue;
                            }


                            // Add in format placeholders.
                            $valuePlaceholder .= sprintf('%s, ', $this->getType($subValue)); // glynn
                            $bindings[] = $this->getValue($subValue);
                        }

                        $valuePlaceholder = trim($valuePlaceholder, ', ');
                        $criteria .= ' (' . $valuePlaceholder . ') ';
                        break;
                }
            } elseif ($value instanceof Raw) {
                $value = $this->parseRaw($value);
                $criteria .= "{$statement['joiner']} {$key} {$statement['operator']} $value ";
            } else {
                // Usual where like criteria
                if (!$bindValues) {
                    // Specially for joins
                    // We are not binding values, lets sanitize then
                    $value = $this->stringifyValue($this->wrapSanitizer($value)) ?? '';
                    $criteria .= $statement['joiner'] . ' ' . $key . ' ' . $statement['operator'] . ' ' . $value . ' ';
                } elseif ($statement['key'] instanceof Raw) {
                    $criteria .= $statement['joiner'] . ' ' . $key . ' ';
                    $bindings = array_merge($bindings, $statement['key']->getBindings());
                } else {
                    // For wheres
                    $bindings[] = $this->getValue($value);

                    $criteria .= $statement['joiner'] . ' ' . $key . ' ' . $statement['operator'] . ' '
                    . $this->getType($value) . ' ';
                }
            }
        }

        // Clear all white spaces, and, or from beginning and white spaces from ending
        $criteria = preg_replace('/^(\s?AND ?|\s?OR ?)|\s$/i', '', $criteria);

        return [$criteria ?? '', $bindings];
    }

    /**
     * Asserts the types place holder based on its value
     *
     * @param mixed $value
     *
     * @return string
     */
    public function inferType($value): string
    {
        switch (true) {
            case is_string($value):
                return '%s';
            case \is_int($value):
            case is_bool($value):
                return '%d';
            case is_float($value):
                return '%f';
            default:
                return '';
        }
    }

    /**
     * Wrap values with adapter's sanitizer like, '`'
     *
     * @param string|Raw|Closure $value
     *
     * @return string|Closure
     */
    public function wrapSanitizer($value)
    {
        // Its a raw query, just cast as string, object has __toString()
        if ($value instanceof Raw) {
            return $this->parseRaw($value);
        } elseif ($value instanceof Closure) {
            return $value;
        }

        // Separate our table and fields which are joined with a ".",
        // like my_table.id
        $valueArr = explode('.', $value, 2);

        foreach ($valueArr as $key => $subValue) {
            // Don't wrap if we have *, which is not a usual field
            $valueArr[$key] = '*' == trim($subValue) ? $subValue : $this->sanitizer . $subValue . $this->sanitizer;
        }

        // Join these back with "." and return
        return implode('.', $valueArr);
    }

    /**
     * Build criteria string and binding with various types added, like WHERE and Having
     *
     * @param array<string|Closure, mixed|mixed[]> $statements
     * @param string $key
     * @param string $type
     * @param bool $bindValues
     *
     * @return array{0:string, 1:string[]}
     */
    protected function buildCriteriaWithType(array $statements, string $key, string $type, bool $bindValues = true)
    {
        $criteria = '';
        $bindings = [];

        if (isset($statements[$key])) {
            // Get the generic/adapter agnostic criteria string from parent
            list($criteria, $bindings) = $this->buildCriteria($statements[$key], $bindValues);

            if ($criteria) {
                $criteria = $type . ' ' . $criteria;
            }
        }

        return [$criteria, $bindings];
    }

    /**
     * Build join string
     *
     * @param array<string|Closure, mixed|mixed[]> $statements
     *
     * @return string
     */
    protected function buildJoin(array $statements): string
    {
        $sql = '';

        if (!array_key_exists('joins', $statements) || !is_array($statements['joins'])) {
            return $sql;
        }

        foreach ($statements['joins'] as $joinArr) {
            if (is_array($joinArr['table'])) {
                $mainTable  = $this->stringifyValue($this->wrapSanitizer($joinArr['table'][0]));
                $aliasTable = $this->stringifyValue($this->wrapSanitizer($joinArr['table'][1]));
                $table      = $mainTable . ' AS ' . $aliasTable;
            } else {
                $table = $joinArr['table'] instanceof Raw
                    ? $this->parseRaw($joinArr['table'])
                    : $this->wrapSanitizer($joinArr['table']);
            }
            $joinBuilder = $joinArr['joinBuilder'];

            /** @var string[] */
            $sqlArr = [
                $sql,
                strtoupper($joinArr['type']),
                'JOIN',
                $table,
                'ON',
                $joinBuilder->getQuery('criteriaOnly', false)->getSql(),
            ];

            $sql = $this->concatenateQuery($sqlArr);
        }

        return $sql;
    }
}


1			<?php
2
3			namespace Pixie\QueryBuilder;
4
5			use Closure;
6			use Pixie\Binding;
7			use Pixie\Exception;
8
9			use function is_bool;
10
11			use Pixie\Connection;
12
13			use function is_float;
14
15			use Pixie\QueryBuilder\Raw;
16			use Pixie\QueryBuilder\NestedCriteria;
17
18			class WPDBAdapter
19			{
20			/**
21			* @var string
22			*/
23			protected $sanitizer = '';
24
25			/**
26			* @var \Pixie\Connection
27			*/
28			protected $connection;
29
30			/**
31			* @var \Viocon\Container
32			*/
33			protected $container;
34
35			public function __construct(Connection $connection)
36			{
37			$this->connection = $connection;
38			$this->container = $this->connection->getContainer();
39			}
40
41			/**
42			* Build select query string and bindings
43			*
44			* @param array<string\|Closure, mixed\|mixed[]> $statements
45			*
46			* @throws Exception
47			*
48			* @return array{sql:string,bindings:mixed[]}
49			*/
50			public function select(array $statements): array
51			{
52			if (!array_key_exists('tables', $statements)) {
53			throw new Exception('No table specified.', 3);
54			} elseif (!array_key_exists('selects', $statements)) {
55			$statements['selects'][] = '*';
56			}
57
58			// From
59			$tables = $this->arrayStr($statements['tables'], ', ');
60			// Select
61			$selects = $this->arrayStr($statements['selects'], ', ');
62
63			// Wheres
64			list($whereCriteria, $whereBindings) = $this->buildCriteriaWithType($statements, 'wheres', 'WHERE');
65			// Group bys
66			$groupBys = '';
67			if (isset($statements['groupBys']) && $groupBys = $this->arrayStr($statements['groupBys'], ', ')) {
68			$groupBys = 'GROUP BY ' . $groupBys;
69			}
70
71			// Order bys
72			$orderBys = '';
73			if (isset($statements['orderBys']) && is_array($statements['orderBys'])) {
74			foreach ($statements['orderBys'] as $orderBy) {
75			$field = $this->wrapSanitizer($orderBy['field']);
76			if ($field instanceof Closure) {
77			continue;
78			}
79			$orderBys .= $field . ' ' . $orderBy['type'] . ', ';
80			}
81
82			if ($orderBys = trim($orderBys, ', ')) {
83			$orderBys = 'ORDER BY ' . $orderBys;
84			}
85			}
86
87			// Limit and offset
88			$limit = isset($statements['limit']) ? 'LIMIT ' . (int) $statements['limit'] : '';
89			$offset = isset($statements['offset']) ? 'OFFSET ' . (int) $statements['offset'] : '';
90
91			// Having
92			list($havingCriteria, $havingBindings) = $this->buildCriteriaWithType($statements, 'havings', 'HAVING');
93
94			// Joins
95			$joinString = $this->buildJoin($statements);
96
97			/** @var string[] */
98			$sqlArray = [
99			'SELECT' . (isset($statements['distinct']) ? ' DISTINCT' : ''),
100			$selects,
101			'FROM',
102			$tables,
103			$joinString,
104			$whereCriteria,
105			$groupBys,
106			$havingCriteria,
107			$orderBys,
108			$limit,
109			$offset,
110			];
111
112			$sql = $this->concatenateQuery($sqlArray);
113
114			$bindings = array_merge(
115			$whereBindings,
116			$havingBindings
117			);
118
119			return compact('sql', 'bindings');
120			}
121
122			/**
123			* Build just criteria part of the query
124			*
125			* @param array<string\|Closure, mixed\|mixed[]> $statements
126			* @param bool $bindValues
127			*
128			* @return array{sql:string[]\|string, bindings:array<mixed>}
129			*/
130			public function criteriaOnly(array $statements, bool $bindValues = true): array
131			{
132			$sql = $bindings = [];
133			if (!isset($statements['criteria'])) {
134			return compact('sql', 'bindings');
135			}
136
137			list($sql, $bindings) = $this->buildCriteria($statements['criteria'], $bindValues);
138
139			return compact('sql', 'bindings');
140			}
141
142			/**
143			* Build a generic insert/ignore/replace query
144			*
145			* @param array<string\|Closure, mixed\|mixed[]> $statements
146			* @param array<string, mixed> $data
147			* @param string $type
148			*
149			* @return array{sql:string, bindings:mixed[]}
150			*
151			* @throws Exception
152			*/
153			private function doInsert(array $statements, array $data, string $type): array
154			{
155			if (!isset($statements['tables'])) {
156			throw new Exception('No table specified', 3);
157			}
158
159			$table = end($statements['tables']);
160
161			$bindings = $keys = $values = [];
162
163			foreach ($data as $key => $value) {
164			$keys[] = $key;
165
166			// Handle value as bindings
167			$isBindings = $value instanceof Binding;
168			// If this is a raw binding, extract the Raw and replace value.
169			if ($isBindings && $value->isRaw()) {
170			$value = $value->getValue();
171			}
172
173			if ($value instanceof Raw) {
174			$values[] = $this->parseRaw($value);
175			} elseif ($isBindings) {
176			$values[] = $value->getType();
177			$bindings[] = $value->getValue();
178			} else {
179			$values[] = $this->inferType($value);
180			$bindings[] = $value;
181			}
182			}
183
184			$sqlArray = [
185			$type . ' INTO',
186			$this->wrapSanitizer($table),
187			'(' . $this->arrayStr($keys, ',') . ')',
188			'VALUES',
189			'(' . $this->arrayStr($values, ',') . ')',
190			];
191
192			if (isset($statements['onduplicate'])) {
193			if (count($statements['onduplicate']) < 1) {
194			throw new Exception('No data given.', 4);
195			}
196			list($updateStatement, $updateBindings) = $this->getUpdateStatement($statements['onduplicate']);
197			$sqlArray[] = 'ON DUPLICATE KEY UPDATE ' . $updateStatement;
198			$bindings = array_merge($bindings, $updateBindings);
199			}
200
201			$sql = $this->concatenateQuery($this->stringifyValues($sqlArray));
202
203			return compact('sql', 'bindings');
204			}
205
206			/**
207			* Attempts to stringify an array of values.
208			*
209			* @param array<string\|int, string\|Closure> $values
210			*
211			* @return string[]
212			*/
213			protected function stringifyValues(array $values): array
214			{
215			return array_filter(array_map([$this, 'stringifyValue'], $values));
216			}
217
218			/**
219			* Attempts to stringify a single of values.
220			*
221			* @param string\|Closure\|Raw $value
222			*
223			* @return string\|null
224			*/
225			protected function stringifyValue($value): ?string
226			{
227			if ($value instanceof Closure) {
228			$value = $value();
229
230			return is_string($value) ? $value : null;
231			}
232
233			if ($value instanceof Raw) {
234			return $this->parseRaw($value);
235			}
236
237			return $value;
238			}
239
240			/**
241			* Build Insert query
242			*
243			* @param array<string\|Closure, mixed\|mixed[]> $statements
244			* @param array<string, mixed> $data $data
245			*
246			* @return array{sql:string, bindings:mixed[]}
247			*
248			* @throws Exception
249			*/
250			public function insert($statements, array $data)
251			{
252			return $this->doInsert($statements, $data, 'INSERT');
253			}
254
255			/**
256			* Build Insert Ignore query
257			*
258			* @param array<string\|Closure, mixed\|mixed[]> $statements
259			* @param array<string, mixed> $data $data
260			*
261			* @return array{sql:string, bindings:mixed[]}
262			*
263			* @throws Exception
264			*/
265			public function insertIgnore($statements, array $data)
266			{
267			return $this->doInsert($statements, $data, 'INSERT IGNORE');
268			}
269
270			/**
271			* Build Insert Ignore query
272			*
273			* @param array<string\|Closure, mixed\|mixed[]> $statements
274			* @param array<string, mixed> $data $data
275			*
276			* @return array{sql:string, bindings:mixed[]}
277			*
278			* @throws Exception
279			*/
280			public function replace($statements, array $data)
281			{
282			return $this->doInsert($statements, $data, 'REPLACE');
283			}
284
285			/**
286			* Build fields assignment part of SET ... or ON DUBLICATE KEY UPDATE ... statements
287			*
288			* @param array<string, mixed> $data
289			*
290			* @return array{0:string,1:mixed[]}
291			*/
292			private function getUpdateStatement(array $data): array
293			{
294			$bindings = [];
295			$statement = '';
296
297			foreach ($data as $key => $value) {
298			$isBindings = $value instanceof Binding;
299			// If this is a raw binding, extract the Raw and replace value.
300			if ($isBindings && $value->isRaw()) {
301			$value = $value->getValue();
302			}
303
304			if ($value instanceof Raw) {
305			$statement .= $this->stringifyValue($this->wrapSanitizer($key)) . '=' . $value . ',';
306			} elseif ($isBindings) {
307			$statement .= $this->stringifyValue($this->wrapSanitizer($key)) . sprintf('=%s,', $value->getType());
308			$bindings[] = $value->getValue();
309			} else {
310			$statement .= $this->stringifyValue($this->wrapSanitizer($key)) . sprintf('=%s,', $this->inferType($value));
311			$bindings[] = $value;
312			}
313			}
314
315			$statement = trim($statement, ',');
316
317			return [$statement, $bindings];
318			}
319
320			/**
321			* Build update query
322			*
323			* @param array<string\|Closure, mixed\|mixed[]> $statements
324			* @param array<string, mixed> $data
325			*
326			* @return array{sql:string, bindings:mixed[]}
327			*
328			* @throws Exception
329			*/
330			public function update($statements, array $data)
331			{
332			if (!isset($statements['tables'])) {
333			throw new Exception('No table specified', 3);
334			} elseif (count($data) < 1) {
335			throw new Exception('No data given.', 4);
336			}
337
338			$table = end($statements['tables']);
339
340			// Update statement
341			list($updateStatement, $bindings) = $this->getUpdateStatement($data);
342
343			// Wheres
344			list($whereCriteria, $whereBindings) = $this->buildCriteriaWithType($statements, 'wheres', 'WHERE');
345
346			// Limit
347			$limit = isset($statements['limit']) ? 'LIMIT ' . $statements['limit'] : '';
348
349			$sqlArray = [
350			'UPDATE',
351			$this->wrapSanitizer($table),
352			'SET ' . $updateStatement,
353			$whereCriteria,
354			$limit,
355			];
356
357			$sql = $this->concatenateQuery($this->stringifyValues($sqlArray));
358
359			$bindings = array_merge($bindings, $whereBindings);
360
361			return compact('sql', 'bindings');
362			}
363
364			/**
365			* Build delete query
366			*
367			* @param array<string\|Closure, mixed\|mixed[]> $statements
368			*
369			* @return array{sql:string, bindings:mixed[]}
370			*
371			* @throws Exception
372			*/
373			public function delete($statements)
374			{
375			if (!isset($statements['tables'])) {
376			throw new Exception('No table specified', 3);
377			}
378
379			$table = end($statements['tables']);
380			// Ensure table name is a string
381			$table = $this->stringifyValue($this->wrapSanitizer($table));
382			if (null === $table) {
383			throw new Exception('Table must be a valid string.', 5);
384			}
385
386			// Wheres
387			list($whereCriteria, $whereBindings) = $this->buildCriteriaWithType($statements, 'wheres', 'WHERE');
388
389			// Limit
390			$limit = isset($statements['limit']) ? 'LIMIT ' . $statements['limit'] : '';
			0 ignored issues – show Unused Code introduced 2022-01-14 15:30 UTC by Report Bug Copy Issue Report The assignment to `$limit` is dead and can be removed. Loading history...
391
392			$sqlArray = ['DELETE FROM', $table, $whereCriteria];
393			$sql = $this->concatenateQuery($sqlArray);
394			$bindings = $whereBindings;
395
396			return compact('sql', 'bindings');
397			}
398
399			/**
400			* Array concatenating method, like implode.
401			* But it does wrap sanitizer and trims last glue
402			*
403			* @param array<string\|int, string> $pieces
404			* @param string $glue
405			*
406			* @return string
407			*/
408			protected function arrayStr(array $pieces, string $glue): string
409			{
410			$str = '';
411			foreach ($pieces as $key => $piece) {
412			if (!is_int($key)) {
413			$piece = $key . ' AS ' . $piece;
414			}
415
416			$str .= $piece . $glue;
417			}
418
419			return trim($str, $glue);
420			}
421
422			/**
423			* Join different part of queries with a space.
424			*
425			* @param array<string\|int, string> $pieces
426			*
427			* @return string
428			*/
429			protected function concatenateQuery(array $pieces): string
430			{
431			$str = '';
432			foreach ($pieces as $piece) {
433			$str = trim($str) . ' ' . trim($piece);
434			}
435
436			return trim($str);
437			}
438
439			/**
440			* Gets the type of a value, either from a binding or infered
441			*
442			* @param mixed $value
443			* @return string
444			*/
445			public function getType($value): string
446			{
447			return $value instanceof Binding && $value->getType() !== null
448			? $value->getType() : $this->inferType($value) ;
449			}
450
451			/**
452			* Get the value from a possible Bindings object.
453			*
454			* @param mixed $value
455			* @return mixed
456			*/
457			public function getValue($value)
458			{
459			return $value instanceof Binding ? $value->getValue() : $value;
460			}
461
462			/**
463			* Attempts to parse a raw query, if bindings are defined then they will be bound first.
464			*
465			* @param Raw $raw
466			* @requires string
467			*/
468			public function parseRaw(Raw $raw): string
469			{
470			$bindings = $raw->getBindings();
471			return 0 === count($bindings)
472			? (string) $raw
473			: $this->interpolateQuery($raw->getValue(), $bindings);
474			}
475
476			/**
477			* Interpolates a query
478			*
479			* @param string $query
480			* @param array<mixed> $bindings
481			* @return string
482			*/
483			public function interpolateQuery(string $query, array $bindings = []): string
484			{
485			if (0 === count($bindings)) {
486			return $query;
487			}
488
489
490			$bindings = array_map([$this, 'getValue'], $bindings);
491			$query = $this->connection->getDbInstance()->prepare($query, $bindings) ;
492			return is_string($query) ? $query : '';
			0 ignored issues – show introduced 2022-01-19 09:23 UTC by Report Bug Copy Issue Report The condition `is_string($query)` is always `true`. Loading history...
493			}
494
495			/**
496			* Build generic criteria string and bindings from statements, like "a = b and c = ?"
497			*
498			* @param array<string\|Closure, mixed\|mixed[]> $statements
499			* @param bool $bindValues
500			*
501			* @return array{0:string,1:string[]}
502			*/
503			protected function buildCriteria(array $statements, bool $bindValues = true): array
504			{
505			$criteria = '';
506			$bindings = [];
507			foreach ($statements as $statement) {
508			$key = $statement['key'];
509			$value = $statement['value'];
510
511			// If the value is a Raw Binding, cast to raw
512			if ($value instanceof Binding && Binding::RAW === $value->getType()) {
513			/** @var Raw */
514			$value = $value->getValue();
515			}
516
517			if (is_null($value) && $key instanceof Closure) {
518			// We have a closure, a nested criteria
519
520			// Build a new NestedCriteria class, keep it by reference so any changes made
521			// in the closure should reflect here
522			$nestedCriteria = $this->container->build(NestedCriteria::class, [$this->connection]);
523
524			$nestedCriteria = &$nestedCriteria;
525			// Call the closure with our new nestedCriteria object
526			$key($nestedCriteria);
527			// Get the criteria only query from the nestedCriteria object
528			$queryObject = $nestedCriteria->getQuery('criteriaOnly', true);
529			// Merge the bindings we get from nestedCriteria object
530			$bindings = array_merge($bindings, $queryObject->getBindings());
531			// Append the sql we get from the nestedCriteria object
532			$criteria .= $statement['joiner'] . ' (' . $queryObject->getSql() . ') ';
533			} elseif (is_array($value)) {
534			// where_in or between like query
535			$criteria .= $statement['joiner'] . ' ' . $key . ' ' . $statement['operator'];
536
537			switch ($statement['operator']) {
538			case 'BETWEEN':
539			$bindings = array_merge($bindings, $statement['value']);
540			$criteria .= sprintf(
541			' %s AND %s ',
542			$this->getType($value[0]),
543			$this->getType($value[1])
544			);
545
546			// Maybe cast the values bindings.
547			$value[0] = $this->getValue($value[0]);
548			$value[1] = $this->getValue($value[1]);
549			break;
550			default:
551			$valuePlaceholder = '';
552			foreach ($statement['value'] as $subValue) {
553			// Get its value.
554			if ($this->getValue($subValue) instanceof Raw) {
555			/** @var Raw $subValue */
556			$subValue = $this->getValue($subValue);
557			$valuePlaceholder .= sprintf('%s, ', $this->parseRaw($subValue));
558			continue;
559			}
560
561
562			// Add in format placeholders.
563			$valuePlaceholder .= sprintf('%s, ', $this->getType($subValue)); // glynn
564			$bindings[] = $this->getValue($subValue);
565			}
566
567			$valuePlaceholder = trim($valuePlaceholder, ', ');
568			$criteria .= ' (' . $valuePlaceholder . ') ';
569			break;
570			}
571			} elseif ($value instanceof Raw) {
572			$value = $this->parseRaw($value);
573			$criteria .= "{$statement['joiner']} {$key} {$statement['operator']} $value ";
574			} else {
575			// Usual where like criteria
576			if (!$bindValues) {
577			// Specially for joins
578			// We are not binding values, lets sanitize then
579			$value = $this->stringifyValue($this->wrapSanitizer($value)) ?? '';
580			$criteria .= $statement['joiner'] . ' ' . $key . ' ' . $statement['operator'] . ' ' . $value . ' ';
581			} elseif ($statement['key'] instanceof Raw) {
582			$criteria .= $statement['joiner'] . ' ' . $key . ' ';
583			$bindings = array_merge($bindings, $statement['key']->getBindings());
584			} else {
585			// For wheres
586			$bindings[] = $this->getValue($value);
587
588			$criteria .= $statement['joiner'] . ' ' . $key . ' ' . $statement['operator'] . ' '
589			. $this->getType($value) . ' ';
590			}
591			}
592			}
593
594			// Clear all white spaces, and, or from beginning and white spaces from ending
595			$criteria = preg_replace('/^(\s?AND ?\|\s?OR ?)\|\s$/i', '', $criteria);
596
597			return [$criteria ?? '', $bindings];
598			}
599
600			/**
601			* Asserts the types place holder based on its value
602			*
603			* @param mixed $value
604			*
605			* @return string
606			*/
607			public function inferType($value): string
608			{
609			switch (true) {
610			case is_string($value):
611			return '%s';
612			case \is_int($value):
613			case is_bool($value):
614			return '%d';
615			case is_float($value):
616			return '%f';
617			default:
618			return '';
619			}
620			}
621
622			/**
623			* Wrap values with adapter's sanitizer like, '`'
624			*
625			* @param string\|Raw\|Closure $value
626			*
627			* @return string\|Closure
628			*/
629			public function wrapSanitizer($value)
630			{
631			// Its a raw query, just cast as string, object has __toString()
632			if ($value instanceof Raw) {
633			return $this->parseRaw($value);
634			} elseif ($value instanceof Closure) {
635			return $value;
636			}
637
638			// Separate our table and fields which are joined with a ".",
639			// like my_table.id
640			$valueArr = explode('.', $value, 2);
641
642			foreach ($valueArr as $key => $subValue) {
643			// Don't wrap if we have *, which is not a usual field
644			$valueArr[$key] = '*' == trim($subValue) ? $subValue : $this->sanitizer . $subValue . $this->sanitizer;
645			}
646
647			// Join these back with "." and return
648			return implode('.', $valueArr);
649			}
650
651			/**
652			* Build criteria string and binding with various types added, like WHERE and Having
653			*
654			* @param array<string\|Closure, mixed\|mixed[]> $statements
655			* @param string $key
656			* @param string $type
657			* @param bool $bindValues
658			*
659			* @return array{0:string, 1:string[]}
660			*/
661			protected function buildCriteriaWithType(array $statements, string $key, string $type, bool $bindValues = true)
662			{
663			$criteria = '';
664			$bindings = [];
665
666			if (isset($statements[$key])) {
667			// Get the generic/adapter agnostic criteria string from parent
668			list($criteria, $bindings) = $this->buildCriteria($statements[$key], $bindValues);
669
670			if ($criteria) {
671			$criteria = $type . ' ' . $criteria;
672			}
673			}
674
675			return [$criteria, $bindings];
676			}
677
678			/**
679			* Build join string
680			*
681			* @param array<string\|Closure, mixed\|mixed[]> $statements
682			*
683			* @return string
684			*/
685			protected function buildJoin(array $statements): string
686			{
687			$sql = '';
688
689			if (!array_key_exists('joins', $statements) \|\| !is_array($statements['joins'])) {
690			return $sql;
691			}
692
693			foreach ($statements['joins'] as $joinArr) {
694			if (is_array($joinArr['table'])) {
695			$mainTable = $this->stringifyValue($this->wrapSanitizer($joinArr['table'][0]));
696			$aliasTable = $this->stringifyValue($this->wrapSanitizer($joinArr['table'][1]));
697			$table = $mainTable . ' AS ' . $aliasTable;
698			} else {
699			$table = $joinArr['table'] instanceof Raw
700			? $this->parseRaw($joinArr['table'])
701			: $this->wrapSanitizer($joinArr['table']);
702			}
703			$joinBuilder = $joinArr['joinBuilder'];
704
705			/** @var string[] */
706			$sqlArr = [
707			$sql,
708			strtoupper($joinArr['type']),
709			'JOIN',
710			$table,
711			'ON',
712			$joinBuilder->getQuery('criteriaOnly', false)->getSql(),
713			];
714
715			$sql = $this->concatenateQuery($sqlArr);
716			}
717
718			return $sql;
719			}
720			}
721

gin0115 / pixie-wpdb

Pull Request — master (#20)

WPDBAdapter::stringifyValue() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like