WPDBAdapter::getValue() - Code Metrics - Inspection of "Merge pull request #23 from gin0115/develop" - gin0115/pixie-wpdb - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( 8bcaf5...500a63 )

by Glynn

created 2022-01-20 13:10 UTC

WPDBAdapter::getValue() A

↳ Parent: WPDBAdapter

Complexity

Conditions	2
Paths	2

Size

Total Lines	3
Code Lines	1

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
eloc	1
c	0
b	0
f	0
dl	0
loc	3
rs	10
cc	2
nc	2
nop	1

<?php

namespace Pixie\QueryBuilder;

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

use Pixie\Connection;

use Pixie\QueryBuilder\Raw;

use Pixie\QueryBuilder\NestedCriteria;

use function is_bool;
use function is_float;

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;
            }
        }

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

gin0115 / pixie-wpdb

Push — master ( 8bcaf5...500a63 )

WPDBAdapter::getValue() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like