1: <?php
  2: 
  3: /**
  4:  * This file is part of the "dibi" - smart database abstraction layer.
  5:  *
  6:  * Copyright (c) 2005 David Grudl (http://davidgrudl.com)
  7:  *
  8:  * For the full copyright and license information, please view
  9:  * the file license.txt that was distributed with this source code.
 10:  */
 11: 
 12: 
 13: 
 14: /**
 15:  * dibi SQL translator.
 16:  *
 17:  * @author     David Grudl
 18:  * @package    dibi
 19:  */
 20: final class DibiTranslator extends DibiObject
 21: {
 22:     /** @var DibiConnection */
 23:     private $connection;
 24: 
 25:     /** @var IDibiDriver */
 26:     private $driver;
 27: 
 28:     /** @var int */
 29:     private $cursor;
 30: 
 31:     /** @var array */
 32:     private $args;
 33: 
 34:     /** @var bool */
 35:     private $hasError;
 36: 
 37:     /** @var bool */
 38:     private $comment;
 39: 
 40:     /** @var int */
 41:     private $ifLevel;
 42: 
 43:     /** @var int */
 44:     private $ifLevelStart;
 45: 
 46:     /** @var int */
 47:     private $limit;
 48: 
 49:     /** @var int */
 50:     private $offset;
 51: 
 52:     /** @var DibiHashMap */
 53:     private $identifiers;
 54: 
 55: 
 56: 
 57:     public function __construct(DibiConnection $connection)
 58:     {
 59:         $this->connection = $connection;
 60:     }
 61: 
 62: 
 63: 
 64:     /**
 65:      * Generates SQL.
 66:      * @param  array
 67:      * @return string
 68:      * @throws DibiException
 69:      */
 70:     public function translate(array $args)
 71:     {
 72:         $this->identifiers = new DibiHashMap(array($this, 'delimite'));
 73:         $this->driver = $this->connection->getDriver();
 74: 
 75:         $args = array_values($args);
 76:         while (count($args) === 1 && is_array($args[0])) { // implicit array expansion
 77:             $args = array_values($args[0]);
 78:         }
 79:         $this->args = $args;
 80: 
 81:         $this->limit = -1;
 82:         $this->offset = 0;
 83:         $this->hasError = FALSE;
 84:         $commandIns = NULL;
 85:         $lastArr = NULL;
 86:         // shortcuts
 87:         $cursor = & $this->cursor;
 88:         $cursor = 0;
 89: 
 90:         // conditional sql
 91:         $this->ifLevel = $this->ifLevelStart = 0;
 92:         $comment = & $this->comment;
 93:         $comment = FALSE;
 94: 
 95:         // iterate
 96:         $sql = array();
 97:         while ($cursor < count($this->args))
 98:         {
 99:             $arg = $this->args[$cursor];
100:             $cursor++;
101: 
102:             // simple string means SQL
103:             if (is_string($arg)) {
104:                 // speed-up - is regexp required?
105:                 $toSkip = strcspn($arg, '`[\'":%?');
106: 
107:                 if (strlen($arg) === $toSkip) { // needn't be translated
108:                     $sql[] = $arg;
109:                 } else {
110:                     $sql[] = substr($arg, 0, $toSkip)
111: /*
112:                     . preg_replace_callback('/
113:                     (?=[`[\'":%?])                    ## speed-up
114:                     (?:
115:                         `(.+?)`|                     ## 1) `identifier`
116:                         \[(.+?)\]|                   ## 2) [identifier]
117:                         (\')((?:\'\'|[^\'])*)\'|     ## 3,4) 'string'
118:                         (")((?:""|[^"])*)"|          ## 5,6) "string"
119:                         (\'|")|                      ## 7) lone quote
120:                         :(\S*?:)([a-zA-Z0-9._]?)|    ## 8,9) :substitution:
121:                         %([a-zA-Z~][a-zA-Z0-9~]{0,5})|## 10) modifier
122:                         (\?)                         ## 11) placeholder
123:                     )/xs',
124: */                  // note: this can change $this->args & $this->cursor & ...
125:                     . preg_replace_callback('/(?=[`[\'":%?])(?:`(.+?)`|\[(.+?)\]|(\')((?:\'\'|[^\'])*)\'|(")((?:""|[^"])*)"|(\'|")|:(\S*?:)([a-zA-Z0-9._]?)|%([a-zA-Z~][a-zA-Z0-9~]{0,5})|(\?))/s',
126:                             array($this, 'cb'),
127:                             substr($arg, $toSkip)
128:                     );
129:                     if (preg_last_error()) throw new DibiPcreException;
130:                 }
131:                 continue;
132:             }
133: 
134:             if ($comment) {
135:                 $sql[] = '...';
136:                 continue;
137:             }
138: 
139:             if ($arg instanceof Traversable) {
140:                 $arg = iterator_to_array($arg);
141:             }
142: 
143:             if (is_array($arg)) {
144:                 if (is_string(key($arg))) {
145:                     // associative array -> autoselect between SET or VALUES & LIST
146:                     if ($commandIns === NULL) {
147:                         $commandIns = strtoupper(substr(ltrim($this->args[0]), 0, 6));
148:                         $commandIns = $commandIns === 'INSERT' || $commandIns === 'REPLAC';
149:                         $sql[] = $this->formatValue($arg, $commandIns ? 'v' : 'a');
150:                     } else {
151:                         if ($lastArr === $cursor - 1) $sql[] = ',';
152:                         $sql[] = $this->formatValue($arg, $commandIns ? 'l' : 'a');
153:                     }
154:                     $lastArr = $cursor;
155:                     continue;
156:                 }
157:             }
158: 
159:             // default processing
160:             $sql[] = $this->formatValue($arg, FALSE);
161:         } // while
162: 
163: 
164:         if ($comment) $sql[] = "*/";
165: 
166:         $sql = implode(' ', $sql);
167: 
168:         if ($this->hasError) {
169:             throw new DibiException('SQL translate error', 0, $sql);
170:         }
171: 
172:         // apply limit
173:         if ($this->limit > -1 || $this->offset > 0) {
174:             $this->driver->applyLimit($sql, $this->limit, $this->offset);
175:         }
176: 
177:         return $sql;
178:     }
179: 
180: 
181: 
182:     /**
183:      * Apply modifier to single value.
184:      * @param  mixed
185:      * @param  string
186:      * @return string
187:      */
188:     public function formatValue($value, $modifier)
189:     {
190:         if ($this->comment) {
191:             return "...";
192:         }
193: 
194:         // array processing (with or without modifier)
195:         if ($value instanceof Traversable) {
196:             $value = iterator_to_array($value);
197:         }
198: 
199:         if (is_array($value)) {
200:             $vx = $kx = array();
201:             switch ($modifier) {
202:             case 'and':
203:             case 'or':  // key=val AND key IS NULL AND ...
204:                 if (empty($value)) {
205:                     return '1=1';
206:                 }
207: 
208:                 foreach ($value as $k => $v) {
209:                     if (is_string($k)) {
210:                         $pair = explode('%', $k, 2); // split into identifier & modifier
211:                         $k = $this->identifiers->{$pair[0]} . ' ';
212:                         if (!isset($pair[1])) {
213:                             $v = $this->formatValue($v, FALSE);
214:                             $vx[] = $k . ($v === 'NULL' ? 'IS ' : '= ') . $v;
215: 
216:                         } elseif ($pair[1] === 'ex') { // TODO: this will be removed
217:                             $vx[] = $k . $this->formatValue($v, 'ex');
218: 
219:                         } else {
220:                             $v = $this->formatValue($v, $pair[1]);
221:                             $vx[] = $k . ($pair[1] === 'l' || $pair[1] === 'in' ? 'IN ' : ($v === 'NULL' ? 'IS ' : '= ')) . $v;
222:                         }
223: 
224:                     } else {
225:                         $vx[] = $this->formatValue($v, 'ex');
226:                     }
227:                 }
228:                 return '(' . implode(') ' . strtoupper($modifier) . ' (', $vx) . ')';
229: 
230:             case 'n':  // key, key, ... identifier names
231:                 foreach ($value as $k => $v) {
232:                     if (is_string($k)) {
233:                         $vx[] = $this->identifiers->$k . (empty($v) ? '' : ' AS ' . $v);
234:                     } else {
235:                         $pair = explode('%', $v, 2); // split into identifier & modifier
236:                         $vx[] = $this->identifiers->{$pair[0]};
237:                     }
238:                 }
239:                 return implode(', ', $vx);
240: 
241: 
242:             case 'a': // key=val, key=val, ...
243:                 foreach ($value as $k => $v) {
244:                     $pair = explode('%', $k, 2); // split into identifier & modifier
245:                     $vx[] = $this->identifiers->{$pair[0]} . '='
246:                         . $this->formatValue($v, isset($pair[1]) ? $pair[1] : (is_array($v) ? 'ex' : FALSE));
247:                 }
248:                 return implode(', ', $vx);
249: 
250: 
251:             case 'in':// replaces scalar %in modifier!
252:             case 'l': // (val, val, ...)
253:                 foreach ($value as $k => $v) {
254:                     $pair = explode('%', $k, 2); // split into identifier & modifier
255:                     $vx[] = $this->formatValue($v, isset($pair[1]) ? $pair[1] : (is_array($v) ? 'ex' : FALSE));
256:                 }
257:                 return '(' . (($vx || $modifier === 'l') ? implode(', ', $vx) : 'NULL') . ')';
258: 
259: 
260:             case 'v': // (key, key, ...) VALUES (val, val, ...)
261:                 foreach ($value as $k => $v) {
262:                     $pair = explode('%', $k, 2); // split into identifier & modifier
263:                     $kx[] = $this->identifiers->{$pair[0]};
264:                     $vx[] = $this->formatValue($v, isset($pair[1]) ? $pair[1] : (is_array($v) ? 'ex' : FALSE));
265:                 }
266:                 return '(' . implode(', ', $kx) . ') VALUES (' . implode(', ', $vx) . ')';
267: 
268:             case 'm': // (key, key, ...) VALUES (val, val, ...), (val, val, ...), ...
269:                 foreach ($value as $k => $v) {
270:                     if (is_array($v)) {
271:                         if (isset($proto)) {
272:                             if ($proto !== array_keys($v)) {
273:                                 $this->hasError = TRUE;
274:                                 return '**Multi-insert array "' . $k . '" is different.**';
275:                             }
276:                         } else {
277:                             $proto = array_keys($v);
278:                         }
279:                     } else {
280:                         $this->hasError = TRUE;
281:                         return '**Unexpected type ' . gettype($v) . '**';
282:                     }
283: 
284:                     $pair = explode('%', $k, 2); // split into identifier & modifier
285:                     $kx[] = $this->identifiers->{$pair[0]};
286:                     foreach ($v as $k2 => $v2) {
287:                         $vx[$k2][] = $this->formatValue($v2, isset($pair[1]) ? $pair[1] : (is_array($v2) ? 'ex' : FALSE));
288:                     }
289:                 }
290:                 foreach ($vx as $k => $v) {
291:                     $vx[$k] = '(' . implode(', ', $v) . ')';
292:                 }
293:                 return '(' . implode(', ', $kx) . ') VALUES ' . implode(', ', $vx);
294: 
295:             case 'by': // key ASC, key DESC
296:                 foreach ($value as $k => $v) {
297:                     if (is_array($v)) {
298:                         $vx[] = $this->formatValue($v, 'ex');
299:                     } elseif (is_string($k)) {
300:                         $v = (is_string($v) && strncasecmp($v, 'd', 1)) || $v > 0 ? 'ASC' : 'DESC';
301:                         $vx[] = $this->identifiers->$k . ' ' . $v;
302:                     } else {
303:                         $vx[] = $this->identifiers->$v;
304:                     }
305:                 }
306:                 return implode(', ', $vx);
307: 
308:             case 'ex':
309:             case 'sql':
310:                 $translator = new self($this->connection);
311:                 return $translator->translate($value);
312: 
313:             default:  // value, value, value - all with the same modifier
314:                 foreach ($value as $v) {
315:                     $vx[] = $this->formatValue($v, $modifier);
316:                 }
317:                 return implode(', ', $vx);
318:             }
319:         }
320: 
321: 
322:         // with modifier procession
323:         if ($modifier) {
324:             if ($value !== NULL && !is_scalar($value) && !($value instanceof DateTime)) {  // array is already processed
325:                 $this->hasError = TRUE;
326:                 return '**Unexpected type ' . gettype($value) . '**';
327:             }
328: 
329:             switch ($modifier) {
330:             case 's':  // string
331:             case 'bin':// binary
332:             case 'b':  // boolean
333:                 return $value === NULL ? 'NULL' : $this->driver->escape($value, $modifier);
334: 
335:             case 'sN': // string or NULL
336:             case 'sn':
337:                 return $value == '' ? 'NULL' : $this->driver->escape($value, dibi::TEXT); // notice two equal signs
338: 
339:             case 'iN': // signed int or NULL
340:             case 'in': // deprecated
341:                 if ($value == '') $value = NULL;
342:                 // intentionally break omitted
343: 
344:             case 'i':  // signed int
345:             case 'u':  // unsigned int, ignored
346:                 // support for long numbers - keep them unchanged
347:                 if (is_string($value) && preg_match('#[+-]?\d++(e\d+)?$#A', $value)) {
348:                     return $value;
349:                 } else {
350:                     return $value === NULL ? 'NULL' : (string) (int) ($value + 0);
351:                 }
352: 
353:             case 'f':  // float
354:                 // support for extreme numbers - keep them unchanged
355:                 if (is_string($value) && is_numeric($value) && strpos($value, 'x') === FALSE) {
356:                     return $value; // something like -9E-005 is accepted by SQL, HEX values are not
357:                 } else {
358:                     return $value === NULL ? 'NULL' : rtrim(rtrim(number_format($value + 0, 5, '.', ''), '0'), '.');
359:                 }
360: 
361:             case 'd':  // date
362:             case 't':  // datetime
363:                 if ($value === NULL) {
364:                     return 'NULL';
365:                 } else {
366:                     if (is_numeric($value)) {
367:                         $value = (int) $value; // timestamp
368: 
369:                     } elseif (is_string($value)) {
370:                         $value = new DateTime($value);
371:                     }
372:                     return $this->driver->escape($value, $modifier);
373:                 }
374: 
375:             case 'by':
376:             case 'n':  // identifier name
377:                 return $this->identifiers->$value;
378: 
379:             case 'ex':
380:             case 'sql': // preserve as dibi-SQL  (TODO: leave only %ex)
381:                 $value = (string) $value;
382:                 // speed-up - is regexp required?
383:                 $toSkip = strcspn($value, '`[\'":');
384:                 if (strlen($value) !== $toSkip) {
385:                     $value = substr($value, 0, $toSkip)
386:                     . preg_replace_callback(
387:                         '/(?=[`[\'":])(?:`(.+?)`|\[(.+?)\]|(\')((?:\'\'|[^\'])*)\'|(")((?:""|[^"])*)"|(\'|")|:(\S*?:)([a-zA-Z0-9._]?))/s',
388:                         array($this, 'cb'),
389:                         substr($value, $toSkip)
390:                     );
391:                     if (preg_last_error()) throw new DibiPcreException;
392:                 }
393:                 return $value;
394: 
395:             case 'SQL': // preserve as real SQL (TODO: rename to %sql)
396:                 return (string) $value;
397: 
398:             case 'like~':  // LIKE string%
399:                 return $this->driver->escapeLike($value, 1);
400: 
401:             case '~like':  // LIKE %string
402:                 return $this->driver->escapeLike($value, -1);
403: 
404:             case '~like~': // LIKE %string%
405:                 return $this->driver->escapeLike($value, 0);
406: 
407:             case 'and':
408:             case 'or':
409:             case 'a':
410:             case 'l':
411:             case 'v':
412:                 $this->hasError = TRUE;
413:                 return '**Unexpected type ' . gettype($value) . '**';
414: 
415:             default:
416:                 $this->hasError = TRUE;
417:                 return "**Unknown or invalid modifier %$modifier**";
418:             }
419:         }
420: 
421: 
422:         // without modifier procession
423:         if (is_string($value)) {
424:             return $this->driver->escape($value, dibi::TEXT);
425: 
426:         } elseif (is_int($value)) {
427:             return (string) $value;
428: 
429:         } elseif (is_float($value)) {
430:             return rtrim(rtrim(number_format($value, 5, '.', ''), '0'), '.');
431: 
432:         } elseif (is_bool($value)) {
433:             return $this->driver->escape($value, dibi::BOOL);
434: 
435:         } elseif ($value === NULL) {
436:             return 'NULL';
437: 
438:         } elseif ($value instanceof DateTime) {
439:             return $this->driver->escape($value, dibi::DATETIME);
440: 
441:         } elseif ($value instanceof DibiLiteral) {
442:             return (string) $value;
443: 
444:         } else {
445:             $this->hasError = TRUE;
446:             return '**Unexpected ' . gettype($value) . '**';
447:         }
448:     }
449: 
450: 
451: 
452:     /**
453:      * PREG callback from translate() or formatValue().
454:      * @param  array
455:      * @return string
456:      */
457:     private function cb($matches)
458:     {
459:         //    [1] => `ident`
460:         //    [2] => [ident]
461:         //    [3] => '
462:         //    [4] => string
463:         //    [5] => "
464:         //    [6] => string
465:         //    [7] => lone-quote
466:         //    [8] => substitution
467:         //    [9] => substitution flag
468:         //    [10] => modifier (when called from self::translate())
469:         //    [11] => placeholder (when called from self::translate())
470: 
471: 
472:         if (!empty($matches[11])) { // placeholder
473:             $cursor = & $this->cursor;
474: 
475:             if ($cursor >= count($this->args)) {
476:                 $this->hasError = TRUE;
477:                 return "**Extra placeholder**";
478:             }
479: 
480:             $cursor++;
481:             return $this->formatValue($this->args[$cursor - 1], FALSE);
482:         }
483: 
484:         if (!empty($matches[10])) { // modifier
485:             $mod = $matches[10];
486:             $cursor = & $this->cursor;
487: 
488:             if ($cursor >= count($this->args) && $mod !== 'else' && $mod !== 'end') {
489:                 $this->hasError = TRUE;
490:                 return "**Extra modifier %$mod**";
491:             }
492: 
493:             if ($mod === 'if') {
494:                 $this->ifLevel++;
495:                 $cursor++;
496:                 if (!$this->comment && !$this->args[$cursor - 1]) {
497:                     // open comment
498:                     $this->ifLevelStart = $this->ifLevel;
499:                     $this->comment = TRUE;
500:                     return "/*";
501:                 }
502:                 return '';
503: 
504:             } elseif ($mod === 'else') {
505:                 if ($this->ifLevelStart === $this->ifLevel) {
506:                     $this->ifLevelStart = 0;
507:                     $this->comment = FALSE;
508:                     return "*/";
509:                 } elseif (!$this->comment) {
510:                     $this->ifLevelStart = $this->ifLevel;
511:                     $this->comment = TRUE;
512:                     return "/*";
513:                 }
514: 
515:             } elseif ($mod === 'end') {
516:                 $this->ifLevel--;
517:                 if ($this->ifLevelStart === $this->ifLevel + 1) {
518:                     // close comment
519:                     $this->ifLevelStart = 0;
520:                     $this->comment = FALSE;
521:                     return "*/";
522:                 }
523:                 return '';
524: 
525:             } elseif ($mod === 'ex') { // array expansion
526:                 array_splice($this->args, $cursor, 1, $this->args[$cursor]);
527:                 return '';
528: 
529:             } elseif ($mod === 'lmt') { // apply limit
530:                 if ($this->args[$cursor] !== NULL) $this->limit = (int) $this->args[$cursor];
531:                 $cursor++;
532:                 return '';
533: 
534:             } elseif ($mod === 'ofs') { // apply offset
535:                 if ($this->args[$cursor] !== NULL) $this->offset = (int) $this->args[$cursor];
536:                 $cursor++;
537:                 return '';
538: 
539:             } else { // default processing
540:                 $cursor++;
541:                 return $this->formatValue($this->args[$cursor - 1], $mod);
542:             }
543:         }
544: 
545:         if ($this->comment) return '...';
546: 
547:         if ($matches[1])  // SQL identifiers: `ident`
548:             return $this->identifiers->{$matches[1]};
549: 
550:         if ($matches[2])  // SQL identifiers: [ident]
551:             return $this->identifiers->{$matches[2]};
552: 
553:         if ($matches[3])  // SQL strings: '...'
554:             return $this->driver->escape( str_replace("''", "'", $matches[4]), dibi::TEXT);
555: 
556:         if ($matches[5])  // SQL strings: "..."
557:             return $this->driver->escape( str_replace('""', '"', $matches[6]), dibi::TEXT);
558: 
559:         if ($matches[7]) { // string quote
560:             $this->hasError = TRUE;
561:             return '**Alone quote**';
562:         }
563: 
564:         if ($matches[8]) { // SQL identifier substitution
565:             $m = substr($matches[8], 0, -1);
566:             $m = $this->connection->getSubstitutes()->$m;
567:             return $matches[9] == '' ? $this->formatValue($m, FALSE) : $m . $matches[9]; // value or identifier
568:         }
569: 
570:         die('this should be never executed');
571:     }
572: 
573: 
574: 
575:     /**
576:      * Apply substitutions to indentifier and delimites it.
577:      * @param  string indentifier
578:      * @return string
579:      * @internal
580:      */
581:     public function delimite($value)
582:     {
583:         $value = $this->connection->substitute($value);
584:         $parts = explode('.', $value);
585:         foreach ($parts as & $v) {
586:             if ($v !== '*') $v = $this->driver->escape($v, dibi::IDENTIFIER);
587:         }
588:         return implode('.', $parts);
589:     }
590: 
591: }
592: