DBQueryCondition::sqlConditionType() - Code Metrics - Asymptix/Framework - Measure and Improve Code Quality continuously with Scrutinizer

DBQueryCondition::sqlConditionType() A
last analyzed 2018-07-31 14:35 UTC

↳ Parent: DBQueryCondition

Complexity

Conditions	2
Paths	2

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	2
nc	2
nop	1
dl	0
loc	50
rs	9.0909
c	0
b	0
f	0

<?php

namespace Asymptix\db;

use Asymptix\core\Tools;

/**
 * DB SQL query condition class.
 *
 * @category Asymptix PHP Framework
 * @author Dmytro Zarezenko <[email protected]>
 * @copyright (c) 2009 - 2016, Dmytro Zarezenko
 *
 * @git https://github.com/Asymptix/Framework
 * @license http://opensource.org/licenses/MIT
 */
class DBQueryCondition {
    /**
     * Database condition field.
     *
     * @var DBField
     */
    public $field;

    /**
     * Type of condition.
     *
     * @var string
     */
    public $type;

    /**
     * Condition value.
     *
     * @var mixed
     */
    public $value;

    /**
     * Result SQL condition string.
     *
     * @var string
     */
    private $sqlCondition;

    /**
     * Result prepare statement SQL condition string.
     *
     * @var string
     */
    private $preparedCondition = "";

    /**
     * Types string for prepared statement.
     *
     * @var string
     */
    private $preparedTypes = "";

    /**
     * List with data for prepared statement.
     *
     * @var array<mixed>
     */
    private $preparedData = [];

    /**
     * Inits DBQueryCondition object.
     *
     * @param DBField $field Field to compare.
     * @param string $conditionType Type of the comparison operator or condition.
     * @param mixed $value May be other DBField object or value to compare with.
     *
     * @throws DBQueryConditionException If some parameters invalid.
     */
    public function __construct(DBField $field, $conditionType, $value) {
        $this->type = self::sqlConditionType($conditionType);
        if (!Tools::isInstanceOf($value, "DBField")) {
            $this->field = $field;

            switch ($this->type) {
                case ("="):
                case ("<"):
                case (">"):
                case ("!="):
                    $this->value = DBField::castValue($this->field->type, $value);

                    $this->sqlCondition = "`" . $field->name . "` "
                                        . $this->type . " "
                                        . DBField::sqlValue($this->field->type, $value);

                    $this->preparedCondition = "`" . $field->name . "` " . $this->type . " ?";
                    $this->preparedTypes = $this->field->type;
                    $this->preparedData = [DBField::sqlValue($this->field->type, $value)];
                    break;
                case ("LIKE"):
                case ("NOT LIKE"):
                    $this->value = DBField::castValue($this->field->type, $value);

                    if ($this->field->type != "s") {
                        throw new DBQueryConditionException("Field type is not a string");
                    }
                    $this->sqlCondition = "`" . $field->name . "` "
                                        . $this->type . " "
                                        . DBField::sqlValue($this->field->type, $value);

                    $this->preparedCondition = "`" . $field->name . "` " . $this->type . " ?";
                    $this->preparedTypes = $this->field->type;
                    $this->preparedData = [DBField::sqlValue($this->field->type, $value)];
                    break;
                case ("IN"):
                case ("NOT IN"):
                    if (is_array($value) &&  !empty($value)) {
                        $dataList = [];
                        foreach ($value as $dataItem) {
                            $dataList[] = DBField::sqlValue($this->field->type, $dataItem);
                        }
                        $dataList = array_unique($dataList);
                        $count = count($dataList);
                        if ($count > 0) {
                            $qmStr = "?";
                            $tStr = $this->field->type;
                            for ($i = 1; $i < $count; $i ++) {
                                $qmStr .= ", ?";
                                $tStr .= $this->field->type;
                            }
                        } else {
                            $this->sqlCondition = "1";

                            return;
                        }

                        $this->sqlCondition = "`" . $field->name . "` "
                                            . $this->type
                                            . " (" . implode(", ", $dataList) . ")";

                        $this->preparedCondition = "`" . $field->name . "` " . $this->type . " (" . $qmStr . ")";
                        $this->preparedTypes = $tStr;
                        $this->preparedData = $dataList;
                    } else {
                        throw new DBQueryConditionException("Invalid data for 'IN'/'NOT IN' condition");
                    }
                    break;
                case ("BETWEEN"):
                    if (is_array($value) && count($value) == 2 && isset($value[0]) && isset($value[1])) {
                        $from = DBField::sqlValue($this->field->type, $value[0]);
                        $to = DBField::sqlValue($this->field->type, $value[1]);
                        $this->sqlCondition = "`" . $field->name . "` BETWEEN " . $from . " AND " . $to;

                        $this->preparedCondition = "`" . $field->name . "` BETWEEN ? AND ?";
                        $this->preparedTypes = $this->field->type . $this->field->type;
                        $this->preparedData = [$from, $to];
                    } else {
                        throw new DBQueryConditionException("Invalid data for 'BETWEEN' condition");
                    }
                    break;
            }
        } else {
            $field1 = $field;
            $field2 = $value;

            switch ($this->type) {
                case ("="):
                case ("<"):
                case (">"):
                case ("!="):
                case ("LIKE"):
                case ("NOT LIKE"):
                    $this->sqlCondition = "`" . $field1->name . "` " . $this->type . " `" . $field2->name . "`";
                    break;
                case ("IN"):
                case ("NOT IN"):
                    // impossible, use array instead of DBField
                    break;
                case ("BETWEEN"):
                    // impossible, use array instead of DBField
                    break;
            }
        }
    }

    /**
     * Generates SQL formatted condition string.
     *
     * @param mixed $queryCondition List of DBQueryCondition objects or object
     *           itself.
     * @param string $operator Initial logical OR or AND operator.
     *
     * @return string SQL query condition string.
     */
    public static function getSQLCondition($queryCondition, $operator = "") {
        $operator = strtoupper(trim($operator));
        if ($operator === "OR" || $operator === "AND") {
            if (is_array($queryCondition)) {
                if ($operator === "AND") {
                    $cond = " (1";
                } else {
                    $cond = " (0";
                }

                foreach ($queryCondition as $operation => $conditions) {
                    $cond .= " " . $operator . self::getSQLCondition($conditions, $operation);
                }

                $cond .= ")";

                return $cond;
            }
        } else {
            if (is_array($queryCondition)) {
                foreach ($queryCondition as $operation => $conditions) {
                    return trim(
                        str_replace(["(1 AND ", "(0 OR "], "(",
                            self::getSQLCondition($conditions, $operation)
                        )
                    );
                }
            } elseif (Tools::isInstanceOf($queryCondition, "\Asymptix\db\DBQueryCondition")) {
                return (" " . $queryCondition->sqlCondition);
            }

            return "";
        }
    }

    /**
     * Normalize query condition operator (type).
     *
     * @param string $conditionType Condition type.
     *
     * @return string Normalized condition type.
     * @throws DBQueryConditionException If invalid type provided.
     */
    private static function sqlConditionType($conditionType) {
        $conditionType = preg_replace("#[[:blank:]]{2,}#", " ", strtolower(trim($conditionType)));

        $conditionTypes = [
            // Equal operator
            '=' => "=",
            'eq' => "=",
            'equal' => "=",

            // Not equal operator
            '!=' => "!=",
            '<>' => "!=",
            'neq' => "!=",
            'not equal' => "!=",

            // Less than operator
            '<' => "<",
            'lt' => "<",
            'less than' => "<",

            // Greater than operator
            '>' => ">",
            'gt' => ">",
            'greater than' => ">",

            // Check whether a value is within a set of values
            'in' => "IN",

            // Check whether a value is not within a set of values
            'not in' => "NOT IN",

            // Simple pattern matching
            'like' => "LIKE",
            'match' => "LIKE",

            // Negation of simple pattern matching
            'not like' => "NOT LIKE",
            'not match' => "NOT LIKE",

            // Check whether a value is within a range of values
            'between' => "BETWEEN"

            //TODO: add all conditions from http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html
        ];

        if (isset($conditionTypes[$conditionType])) {
            return $conditionTypes[$conditionType];
        }
        throw new DBQueryConditionException("Invalid SQL condition type '" . $conditionType . "'");
    }
}

/**
 * Service exception class.
 */
class DBQueryConditionException extends \Exception {}



1			<?php
2
3			namespace Asymptix\db;
4
5			use Asymptix\core\Tools;
6
7			/**
8			* DB SQL query condition class.
9			*
10			* @category Asymptix PHP Framework
11			* @author Dmytro Zarezenko <[email protected]>
12			* @copyright (c) 2009 - 2016, Dmytro Zarezenko
13			*
14			* @git https://github.com/Asymptix/Framework
15			* @license http://opensource.org/licenses/MIT
16			*/
17			class DBQueryCondition {
18			/**
19			* Database condition field.
20			*
21			* @var DBField
22			*/
23			public $field;
24
25			/**
26			* Type of condition.
27			*
28			* @var string
29			*/
30			public $type;
31
32			/**
33			* Condition value.
34			*
35			* @var mixed
36			*/
37			public $value;
38
39			/**
40			* Result SQL condition string.
41			*
42			* @var string
43			*/
44			private $sqlCondition;
45
46			/**
47			* Result prepare statement SQL condition string.
48			*
49			* @var string
50			*/
51			private $preparedCondition = "";
52
53			/**
54			* Types string for prepared statement.
55			*
56			* @var string
57			*/
58			private $preparedTypes = "";
59
60			/**
61			* List with data for prepared statement.
62			*
63			* @var array<mixed>
64			*/
65			private $preparedData = [];
66
67			/**
68			* Inits DBQueryCondition object.
69			*
70			* @param DBField $field Field to compare.
71			* @param string $conditionType Type of the comparison operator or condition.
72			* @param mixed $value May be other DBField object or value to compare with.
73			*
74			* @throws DBQueryConditionException If some parameters invalid.
75			*/
76			public function __construct(DBField $field, $conditionType, $value) {
77			$this->type = self::sqlConditionType($conditionType);
78			if (!Tools::isInstanceOf($value, "DBField")) {
79			$this->field = $field;
80
81			switch ($this->type) {
82			case ("="):
83			case ("<"):
84			case (">"):
85			case ("!="):
86			$this->value = DBField::castValue($this->field->type, $value);
87
88			$this->sqlCondition = "`" . $field->name . "` "
89			. $this->type . " "
90			. DBField::sqlValue($this->field->type, $value);
91
92			$this->preparedCondition = "`" . $field->name . "` " . $this->type . " ?";
93			$this->preparedTypes = $this->field->type;
94			$this->preparedData = [DBField::sqlValue($this->field->type, $value)];
95			break;
96			case ("LIKE"):
97			case ("NOT LIKE"):
98			$this->value = DBField::castValue($this->field->type, $value);
99
100			if ($this->field->type != "s") {
101			throw new DBQueryConditionException("Field type is not a string");
102			}
103			$this->sqlCondition = "`" . $field->name . "` "
104			. $this->type . " "
105			. DBField::sqlValue($this->field->type, $value);
106
107			$this->preparedCondition = "`" . $field->name . "` " . $this->type . " ?";
108			$this->preparedTypes = $this->field->type;
109			$this->preparedData = [DBField::sqlValue($this->field->type, $value)];
110			break;
111			case ("IN"):
112			case ("NOT IN"):
113			if (is_array($value) && !empty($value)) {
114			$dataList = [];
115			foreach ($value as $dataItem) {
116			$dataList[] = DBField::sqlValue($this->field->type, $dataItem);
117			}
118			$dataList = array_unique($dataList);
119			$count = count($dataList);
120			if ($count > 0) {
121			$qmStr = "?";
122			$tStr = $this->field->type;
123			for ($i = 1; $i < $count; $i ++) {
124			$qmStr .= ", ?";
125			$tStr .= $this->field->type;
126			}
127			} else {
128			$this->sqlCondition = "1";
129
130			return;
131			}
132
133			$this->sqlCondition = "`" . $field->name . "` "
134			. $this->type
135			. " (" . implode(", ", $dataList) . ")";
136
137			$this->preparedCondition = "`" . $field->name . "` " . $this->type . " (" . $qmStr . ")";
138			$this->preparedTypes = $tStr;
139			$this->preparedData = $dataList;
140			} else {
141			throw new DBQueryConditionException("Invalid data for 'IN'/'NOT IN' condition");
142			}
143			break;
144			case ("BETWEEN"):
145			if (is_array($value) && count($value) == 2 && isset($value[0]) && isset($value[1])) {
146			$from = DBField::sqlValue($this->field->type, $value[0]);
147			$to = DBField::sqlValue($this->field->type, $value[1]);
148			$this->sqlCondition = "`" . $field->name . "` BETWEEN " . $from . " AND " . $to;
149
150			$this->preparedCondition = "`" . $field->name . "` BETWEEN ? AND ?";
151			$this->preparedTypes = $this->field->type . $this->field->type;
152			$this->preparedData = [$from, $to];
153			} else {
154			throw new DBQueryConditionException("Invalid data for 'BETWEEN' condition");
155			}
156			break;
157			}
158			} else {
159			$field1 = $field;
160			$field2 = $value;
161
162			switch ($this->type) {
163			case ("="):
164			case ("<"):
165			case (">"):
166			case ("!="):
167			case ("LIKE"):
168			case ("NOT LIKE"):
169			$this->sqlCondition = "`" . $field1->name . "` " . $this->type . " `" . $field2->name . "`";
170			break;
171			case ("IN"):
172			case ("NOT IN"):
173			// impossible, use array instead of DBField
174			break;
175			case ("BETWEEN"):
176			// impossible, use array instead of DBField
177			break;
178			}
179			}
180			}
181
182			/**
183			* Generates SQL formatted condition string.
184			*
185			* @param mixed $queryCondition List of DBQueryCondition objects or object
186			* itself.
187			* @param string $operator Initial logical OR or AND operator.
188			*
189			* @return string SQL query condition string.
190			*/
191			public static function getSQLCondition($queryCondition, $operator = "") {
192			$operator = strtoupper(trim($operator));
193			if ($operator === "OR" \|\| $operator === "AND") {
194			if (is_array($queryCondition)) {
195			if ($operator === "AND") {
196			$cond = " (1";
197			} else {
198			$cond = " (0";
199			}
200
201			foreach ($queryCondition as $operation => $conditions) {
202			$cond .= " " . $operator . self::getSQLCondition($conditions, $operation);
203			}
204
205			$cond .= ")";
206
207			return $cond;
208			}
209			} else {
210			if (is_array($queryCondition)) {
211			foreach ($queryCondition as $operation => $conditions) {
212			return trim(
213			str_replace(["(1 AND ", "(0 OR "], "(",
214			self::getSQLCondition($conditions, $operation)
215			)
216			);
217			}
218			} elseif (Tools::isInstanceOf($queryCondition, "\Asymptix\db\DBQueryCondition")) {
219			return (" " . $queryCondition->sqlCondition);
220			}
221
222			return "";
223			}
224			}
225
226			/**
227			* Normalize query condition operator (type).
228			*
229			* @param string $conditionType Condition type.
230			*
231			* @return string Normalized condition type.
232			* @throws DBQueryConditionException If invalid type provided.
233			*/
234			private static function sqlConditionType($conditionType) {
235			$conditionType = preg_replace("#[[:blank:]]{2,}#", " ", strtolower(trim($conditionType)));
236
237			$conditionTypes = [
238			// Equal operator
239			'=' => "=",
240			'eq' => "=",
241			'equal' => "=",
242
243			// Not equal operator
244			'!=' => "!=",
245			'<>' => "!=",
246			'neq' => "!=",
247			'not equal' => "!=",
248
249			// Less than operator
250			'<' => "<",
251			'lt' => "<",
252			'less than' => "<",
253
254			// Greater than operator
255			'>' => ">",
256			'gt' => ">",
257			'greater than' => ">",
258
259			// Check whether a value is within a set of values
260			'in' => "IN",
261
262			// Check whether a value is not within a set of values
263			'not in' => "NOT IN",
264
265			// Simple pattern matching
266			'like' => "LIKE",
267			'match' => "LIKE",
268
269			// Negation of simple pattern matching
270			'not like' => "NOT LIKE",
271			'not match' => "NOT LIKE",
272
273			// Check whether a value is within a range of values
274			'between' => "BETWEEN"
275
276			//TODO: add all conditions from http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html
277			];
278
279			if (isset($conditionTypes[$conditionType])) {
280			return $conditionTypes[$conditionType];
281			}
282			throw new DBQueryConditionException("Invalid SQL condition type '" . $conditionType . "'");
283			}
284			}
285
286			/**
287			* Service exception class.
288			*/
289			class DBQueryConditionException extends \Exception {}
			0 ignored issues – show Coding Style Compatibility introduced 2016-07-26 12:49 UTC by Report Bug Copy Issue Report PSR1 recommends that each class should be in its own file to aid autoloaders. Having each class in a dedicated file usually plays nice with PSR autoloaders and is therefore a well established practice. If you use other autoloaders, you might not want to follow this rule. Loading history...
290

Asymptix / Framework

DBQueryCondition::sqlConditionType() A last analyzed 2018-07-31 14:35 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

DBQueryCondition::sqlConditionType() A
last analyzed 2018-07-31 14:35 UTC