1 <?php
2 /**
3 * DBExample.php
4 *
5 * @author Elvyrra SAS
6 * @license http://rem.mit-license.org/ MIT
7 */
8
9 namespace Hawk;
10
11 /**
12 * This class is used to construct SQL WHERE expressions from arrays.
13 * This can be useful to build simple conditions without writing SQL query and manage binding values
14 * This class is used in classes Model, Form, ItemList to get data from the database.
15 * Example : To make the expression 'field1 = "value1" AND (field2 IS NOT NULL OR field3 < 12)',
16 * create an DBExample like :
17 * <code>
18 * new DBExample(array(
19 * array('field1' => "value1"),
20 * array('$or' => array(
21 * array(
22 * 'field2' => '$notnull'
23 * ),
24 * array(
25 * 'field3' => array('$lt' => 12)
26 * )
27 * ))
28 * ))
29 * </code>
30 *
31 * @package Core
32 */
33 class DBExample{
34 /**
35 * The example content
36 *
37 * @var array
38 */
39 public $example = array();
40
41 /**
42 * The supported binary operators
43 *
44 * @var array
45 */
46 private static $binaryOperators = array(
47 '$ne' => '<>',
48 '$lt' => '<',
49 '$lte' => '<=',
50 '$gt' => '>',
51 '$gte' => '>=',
52 '$like' => 'LIKE',
53 '$nlike' => 'NOT LIKE',
54 '$in' => 'IN',
55 '$nin' => 'NOT IN'
56 );
57
58 /**
59 * The supported unary operators
60 *
61 * @var array
62 */
63 private static $unaryOperators = array(
64 '$null' => 'IS NULL',
65 '$notnull' => 'IS NOT NULl'
66 );
67
68 /**
69 * Constructor
70 *
71 * @param array $example The DBExample structure
72 */
73 public function __construct($example){
74 $this->example = $example;
75 }
76
77 /**
78 * Create a DBExample and parse it
79 *
80 * @param array $example The DBExample structure
81 * @param array $binds This variables, passed by reference,
82 * will be filled with the binded values during example parsing during example parsing
83 *
84 * @return string the parsed SQL expression
85 */
86 public static function make($example, &$binds){
87 $instance = new self($example);
88 return $instance->parse($binds);
89 }
90
91
92 /**
93 * Parse the example to create the corresponding SQL expression
94 *
95 * @param array $binds This variable, passed by reference,
96 * will be filled with the binded values during example parsing
97 *
98 * @return string the parsed SQL expression
99 */
100 public function parse(&$binds){
101 return $this->parseElements($binds);
102 }
103
104
105 /**
106 * Parse a substructure. This method is used internally and recursively by the method parse
107 *
108 * @param array $binds The binded values, filles during parsing
109 * @param array $example The substructure to parse
110 * @param string $operator The operator to separate the parsed substructures
111 * @param string $upperKey The key of the parent structure. For example, if you parse array('$gt' => 3),
112 * in the whole structure array('field' => array('$gt' => 3)),
113 * $upperKey will be set to 'field'
114 *
115 * @return string The SQL expression, result of the elements parsing
116 */
117 private function parseElements(&$binds = null, $example = null, $operator = 'AND', $upperKey = null){
118 if($example === null) {
119 $example = $this->example;
120 }
121 if(empty($binds)) {
122 $binds = array();
123 }
124
125 if(!is_array($example)) {
126 throw new DBExampleException(
127 'The example to parse must be an integer , ' .
128 gettype($example) . ' given : ' .
129 var_export($example, true)
130 );
131 }
132
133 $sql = "";
134 $elements = array();
135
136 foreach($example as $key => $value){
137 $bindKey = uniqid();
138
139 // Binary operations (= , <, >, LIKE, IN, NOT IN, .. etc)
140 if(isset(self::$binaryOperators[$key])) {
141 $op = self::$binaryOperators[$key];
142 if(!$upperKey) {
143 throw new DBExampleException("The operation '$op' needs to be in a array associated to a field");
144 }
145 if(is_array($value)) {
146 $keys = array();
147 foreach($value as $val){
148 $bindKey = uniqid();
149 $binds[$bindKey] = $val;
150 $keys[] = ':'.$bindKey;
151 }
152 $elements[] = DB::formatField($upperKey) . " $op (" . implode(',', $keys) . ")";
153 }
154 else{
155 $binds[$bindKey] = $value;
156 $elements[] = DB::formatField($upperKey) . " $op :$bindKey";
157 }
158 }
159
160 // Unary operations (IS NULL, IS NOT NULL, ... etc)
161 elseif(is_scalar($value) && isset(self::$unaryOperators[$value])) {
162 $op = self::$unaryOperators[$value];
163 $elements[] = DB::formatField($key) . " $op";
164 }
165
166 // Parse a sub element
167 elseif(is_numeric($key) && is_array($value)) {
168 $elements[] = $this->parseElements($binds, $value, $operator, $key);
169 }
170
171 else{
172 switch($key){
173 case '$not':
174 // NOT (...)
175 $elements[] = 'NOT ('.$this->parseElements($binds, $value) .')';
176 break;
177
178 case '$or' :
179 // ... OR ...
180 $elements[] = '(' . $this->parseElements($binds, $value, 'OR') . ')';
181 break;
182
183 case '$and' :
184 // ... AND ...
185 $elements[] = '(' . $this->parseElements($binds, $value, 'AND') . ')';
186 break;
187
188 default :
189 if(is_scalar($value)) {
190 $binds[$bindKey] = $value;
191 $elements[] = DB::formatField($key) . " = :$bindKey";
192 }
193 elseif(is_array($value)) {
194 $elements[] = $this->parseElements($binds, $value, $operator, $key);
195 }
196 else{
197 throw new DBExampleException(
198 'The value must be a scalar value, given : ' .
199 $key . ' => ' . var_export($value, true)
200 );
201 }
202 break;
203 }
204 }
205 }
206
207 if($upperKey) {
208 return '(' . implode(" $operator ", $elements) . ')';
209 }
210 else{
211 return implode(" $operator ", $elements);
212 }
213 }
214 }
215
216
217 /**
218 * This class describes the behavior of the exceptions throwed by DBExample class
219 *
220 * @package Exceptions
221 */
222 class DBExampleException extends \Exception{
223 }