1 <?php
  2 /**
  3  * Neevo - Tiny database layer for PHP. (http://neevo.smasty.net)
  4  *
  5  * This source file is subject to the MIT license that is bundled
  6  * with this package in the file license.txt.
  7  *
  8  * Copyright (c) 2012 Smasty (http://smasty.net)
  9  *
 10  */
 11 
 12 namespace Neevo;
 13 
 14 use DateTime;
 15 use Traversable;
 16 
 17 
 18 /**
 19  * Neevo\BaseStatement to SQL command parser.
 20  * @author Smasty
 21  */
 22 class Parser {
 23 
 24 
 25     /** @var BaseStatement */
 26     protected $stmt;
 27 
 28     /** @var array */
 29     protected $clauses = array();
 30 
 31 
 32     /**
 33      * Instantiates the parser for given statement.
 34      * @param BaseStatement $statement
 35      */
 36     public function __construct(BaseStatement $statement){
 37         $this->stmt = $statement;
 38     }
 39 
 40 
 41     /**
 42      * Parses the given statement.
 43      * @return string The SQL statement
 44      */
 45     public function parse(){
 46         $where = $order = $group = $limit = $q = '';
 47         $source = $this->parseSource();
 48 
 49         if($this->stmt->getConditions())
 50             $where = $this->parseWhere();
 51         if($this->stmt instanceof Result && $this->stmt->getGrouping())
 52             $group = $this->parseGrouping();
 53         if($this->stmt->getSorting())
 54             $order = $this->parseSorting();
 55 
 56         $this->clauses = array($source, $where, $group, $order);
 57 
 58         if($this->stmt->getType() === Manager::STMT_SELECT)
 59             $q = $this->parseSelectStmt();
 60         elseif($this->stmt->getType() === Manager::STMT_INSERT)
 61             $q = $this->parseInsertStmt();
 62         elseif($this->stmt->getType() === Manager::STMT_UPDATE)
 63             $q = $this->parseUpdateStmt();
 64         elseif($this->stmt->getType() === Manager::STMT_DELETE)
 65             $q = $this->parseDeleteStmt();
 66 
 67         return trim($q);
 68     }
 69 
 70 
 71     /**
 72      * Parses SELECT statement.
 73      * @return string
 74      */
 75     protected function parseSelectStmt(){
 76         $cols = $this->stmt->getColumns();
 77         list($source, $where, $group, $order) = $this->clauses;
 78         foreach($cols as $key => $col){
 79             $col = preg_match('~^[\w.]+$~', $col) ? ":$col" : $col;
 80             $cols[$key] = $this->tryDelimite($col);
 81         }
 82         $cols = implode(', ', $cols);
 83 
 84         return $this->applyLimit("SELECT $cols\nFROM " . $source . $where . $group . $order);
 85     }
 86 
 87 
 88     /**
 89      * Parses INSERT statement.
 90      * @return string
 91      */
 92     protected function parseInsertStmt(){
 93         $cols = array();
 94         foreach($this->escapeValue($this->stmt->getValues()) as $col => $value){
 95             $cols[] = $this->parseFieldName($col);
 96             $values[] = $value;
 97         }
 98         $data = ' (' . implode(', ', $cols) . ")\nVALUES (" . implode(', ', $values). ')';
 99 
100         return 'INSERT INTO ' . $this->clauses[0] . $data;
101     }
102 
103 
104     /**
105      * Parses UPDATE statement.
106      * @return string
107      */
108     protected function parseUpdateStmt(){
109         $values = array();
110         list($table, $where) = $this->clauses;
111         foreach($this->escapeValue($this->stmt->getValues()) as $col => $value){
112             $values[] = $this->parseFieldName($col) . ' = ' . $value;
113         }
114         $data = "\nSET " . implode(', ', $values);
115         return 'UPDATE ' . $table . $data . $where;
116     }
117 
118 
119     /**
120      * Parses DELETE statement.
121      * @return string
122      */
123     protected function parseDeleteStmt(){
124         list($table, $where) = $this->clauses;
125         return 'DELETE FROM ' . $table . $where;
126     }
127 
128 
129     /**
130      * Parses statement source.
131      * @return string
132      */
133     protected function parseSource(){
134         if(!$this->stmt instanceof Result)
135             return $this->escapeValue($this->stmt->getTable(), Manager::IDENTIFIER);
136 
137         // Simple table
138         if($this->stmt->getTable() !== null){
139             $source = $this->escapeValue($this->stmt->getTable(), Manager::IDENTIFIER);
140         }
141         // Sub-select
142         else{
143             $subq = $this->stmt->getSource();
144             $alias = $this->escapeValue($subq->getAlias()
145                 ? $subq->getAlias() : '_table_', Manager::IDENTIFIER);
146             $source = "(\n\t" . implode("\n\t", explode("\n", $subq)) . "\n) $alias";
147         }
148         $source = $this->tryDelimite($source);
149 
150         // JOINs
151         foreach($this->stmt->getJoins() as $key => $join){
152             list($join_source, $cond, $type) = $join;
153 
154             // JOIN sub-select
155             if($join_source instanceof Result){
156                 $join_alias = $this->escapeValue($join_source->getAlias()
157                     ? $join_source->getAlias() : '_join_' . ($key+1), Manager::IDENTIFIER);
158 
159                 $join_source = "(\n\t" . implode("\n\t", explode("\n", $join_source)) . "\n) $join_alias";
160             }
161             // JOIN Literal
162             elseif($join_source instanceof Literal){
163                 $join_source = $join_source->value;
164             }
165             // JOIN table
166             elseif(is_scalar($join_source)){
167                 $join_source = $this->parseFieldName($join_source, true);
168             }
169             $type = strtoupper(substr($type, 5));
170             $type .= ($type === '') ? '' : ' ';
171                 $source .= $cond instanceof Literal
172                     ? "\n{$type}JOIN $join_source ON $cond->value"
173                     : $this->tryDelimite("\n{$type}JOIN $join_source ON $cond");
174         }
175         return $source;
176     }
177 
178 
179     /**
180      * Parses WHERE clause.
181      * @return string
182      */
183     protected function parseWhere(){
184         $conds = $this->stmt->getConditions();
185 
186         // Unset glue on last condition
187         unset($conds[count($conds)-1]['glue']);
188 
189         $conditions = array();
190         foreach($conds as $cond){
191 
192             // Conditions with modifiers
193             if($cond['simple'] === false){
194                 $values = $this->escapeValue($cond['values'], $cond['types']);
195                 $s = '(' . $this->applyModifiers($cond['expr'], $cond['modifiers'], $values) . ')';
196                 if(isset($cond['glue']))
197                     $s .= ' ' . $cond['glue'];
198 
199                 $conditions[] = $s;
200                 continue;
201             }
202 
203             // Simple conditions
204             $field = $this->parseFieldName($cond['field']);
205             $operator = '';
206             $value = $cond['value'];
207             if($value === null){ // field IS NULL
208                 $value = ' IS NULL';
209             } elseif($value === true){  // field
210                 $value = '';
211             } elseif($value === false){ // NOT field
212                 $value = $field;
213                 $field = 'NOT ';
214             } elseif($value instanceof Result){
215                 $operator = ' IN ';
216                 $value = $this->escapeValue($value, Manager::SUBQUERY);
217             } elseif(is_array($value) || $value instanceof Traversable){ // field IN (array)
218                 $value = ' IN ' . $this->escapeValue($value, Manager::ARR);
219             } elseif($value instanceof Literal){ // field = SQL literal
220                 $operator = ' = ';
221                 $value = $this->escapeValue($value, Manager::LITERAL);
222             } elseif($value instanceof DateTime){ // field = DateTime
223                 $operator = ' = ';
224                 $value = $this->escapeValue($value, Manager::DATETIME);
225             } else{ // field = value
226                 $operator = ' = ';
227                 $value = $this->escapeValue($value);
228             }
229             $s = '(' . $field . $operator . $value . ')';
230             if(isset($cond['glue']))
231                 $s .= ' '.$cond['glue'];
232 
233             $conditions[] = $s;
234 
235         }
236 
237         return "\nWHERE " . implode(' ', $conditions);
238     }
239 
240 
241     /**
242      * Parses ORDER BY clause.
243      * @return string
244      */
245     protected function parseSorting(){
246         $order = array();
247         foreach($this->stmt->getSorting() as $rule){
248             list($field, $type) = $rule;
249             $order[] = $this->tryDelimite($field) . ($type !== null ? ' ' . $type : '');
250         }
251         return "\nORDER BY " . implode(', ', $order);
252     }
253 
254 
255     /**
256      * Parses GROUP BY clause.
257      * @return string
258      */
259     protected function parseGrouping(){
260         list($group, $having) = $this->stmt->getGrouping();
261         return $this->tryDelimite("\nGROUP BY $group" . ($having !== null ? " HAVING $having" : ''));
262     }
263 
264 
265     /**
266      * Parses column name.
267      * @param string|array|Literal $field
268      * @param bool $table Parse table name.
269      * @return string
270      */
271     protected function parseFieldName($field, $table = false){
272         // preg_replace callback behaviour
273         if(is_array($field))
274             $field = $field[0];
275         if($field instanceof Literal)
276             return $field->value;
277 
278         $field = trim($field);
279 
280         if($field === '*')
281             return $field;
282 
283         if(strpos($field, ' '))
284             return $field;
285 
286         $field = str_replace(':', '', $field);
287 
288         if(strpos($field, '.') !== false || $table === true){
289             $prefix = $this->stmt->getConnection()->getPrefix();
290             $field = $prefix . $field;
291         }
292 
293         return $this->stmt->getConnection()->getDriver()->escape($field, Manager::IDENTIFIER);
294     }
295 
296 
297     /**
298      * Applies LIMIT/OFFSET to SQL command.
299      * @param string $sql SQL command
300      * @return string
301      */
302     protected function applyLimit($sql){
303         list($limit, $offset) = $this->stmt->getLimit();
304 
305         if((int) $limit > 0){
306             $sql .= "\nLIMIT " . (int) $limit;
307             if((int) $offset > 0)
308                 $sql .= ' OFFSET ' . (int) $offset;
309         }
310         return $sql;
311     }
312 
313 
314     /**
315      * Escapes given value.
316      * @param mixed|array|Traversable $value
317      * @param string|array|null $type
318      * @return mixed|array
319      */
320     protected function escapeValue($value, $type = null){
321         if(!$type){
322             // NULL type
323             if($value === null)
324                 return 'NULL';
325 
326             // Array of values w/o types
327             elseif(is_array($value)){
328                 foreach($value as $k => $v)
329                     $value[$k] = $this->escapeValue($v);
330                 return $value;
331             }
332 
333             // Value w/o type
334             else{
335                 if($value instanceof DateTime){
336                     return $this->escapeValue($value, Manager::DATETIME);
337                 } elseif($value instanceof Literal){
338                     return $value->value;
339                 } else{
340                     return is_numeric($value)
341                         ? +$value
342                         : $this->stmt->getConnection()->getDriver()->escape($value, Manager::TEXT);
343                 }
344             }
345         }
346 
347         // Multiple values w/ types
348         elseif(is_array($type)){
349             foreach($value as $k => $v)
350                 $value[$k] = $this->escapeValue($v, $type[$k]);
351             return $value;
352         }
353 
354         // Single value w/ type
355         elseif($type !== null){
356             if($type === Manager::INT){
357                 return (int) $value;
358             } elseif($type === Manager::FLOAT){
359                 return (float) $value;
360             } elseif($type === Manager::SUBQUERY && $value instanceof Result){
361                 return "(\n\t" . implode("\n\t", explode("\n", $value)) . "\n)";
362             } elseif($type === Manager::ARR){
363                 $arr = $value instanceof Traversable ? iterator_to_array($value) : (array) $value;
364                 return '(' . implode(', ', $this->escapeValue($arr)) . ')';
365             } elseif($type === Manager::LITERAL){
366                 return $value instanceof Literal ? $value->value : $value;
367             } else{
368                 return $this->stmt->getConnection()->getDriver()->escape($value, $type);
369             }
370         }
371     }
372 
373 
374     /**
375      * Applies modifiers to expression.
376      * @param string $expr
377      * @param array $modifiers
378      * @param array $values
379      * @return string
380      */
381     protected function applyModifiers($expr, array $modifiers, array $values){
382         foreach($modifiers as &$mod){
383             $mod = preg_quote("/$mod/");
384         }
385         $expr = $this->tryDelimite($expr);
386         return preg_replace($modifiers, $values, $expr, 1);
387     }
388 
389 
390     /**
391      * Tries to delimite fields in given expression.
392      * @param string|Literal $expr
393      * @return string
394      */
395     protected function tryDelimite($expr){
396         if($expr instanceof Literal)
397             return $expr->value;
398         return preg_replace_callback('~:([a-z_\*][a-z0-9._\*]*)~i', array($this, 'parseFieldName'), $expr);
399     }
400 
401 
402 }
403 

Neevo Public API API documentation generated by ApiGen 2.8.0