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

Neevo Public API API documentation generated by ApiGen 2.8.0