예제 #1
0
 /**
  * @param  string  SQL command or table or view name, as data source
  * @param  DibiConnection  connection
  */
 public function __construct($sql, DibiConnection $connection)
 {
     if (strpbrk($sql, " \t\r\n") === FALSE) {
         $this->sql = $connection->getDriver()->escape($sql, dibi::IDENTIFIER);
         // table name
     } else {
         $this->sql = '(' . $sql . ') t';
         // SQL command
     }
     $this->connection = $connection;
 }
 /**
  * Generates SQL.
  * @param  array
  * @return string
  * @throws DibiException
  */
 public function translate(array $args)
 {
     $this->identifiers = new DibiHashMap(array($this, 'delimite'));
     $this->driver = $this->connection->getDriver();
     $args = array_values($args);
     while (count($args) === 1 && is_array($args[0])) {
         // implicit array expansion
         $args = array_values($args[0]);
     }
     $this->args = $args;
     $this->limit = -1;
     $this->offset = 0;
     $this->hasError = FALSE;
     $commandIns = NULL;
     $lastArr = NULL;
     // shortcuts
     $cursor =& $this->cursor;
     $cursor = 0;
     // conditional sql
     $this->ifLevel = $this->ifLevelStart = 0;
     $comment =& $this->comment;
     $comment = FALSE;
     // iterate
     $sql = array();
     while ($cursor < count($this->args)) {
         $arg = $this->args[$cursor];
         $cursor++;
         // simple string means SQL
         if (is_string($arg)) {
             // speed-up - is regexp required?
             $toSkip = strcspn($arg, '`[\'":%?');
             if (strlen($arg) === $toSkip) {
                 // needn't be translated
                 $sql[] = $arg;
             } else {
                 $sql[] = substr($arg, 0, $toSkip) . preg_replace_callback('/(?=[`[\'":%?])(?:`(.+?)`|\\[(.+?)\\]|(\')((?:\'\'|[^\'])*)\'|(")((?:""|[^"])*)"|(\'|")|:(\\S*?:)([a-zA-Z0-9._]?)|%([a-zA-Z~][a-zA-Z0-9~]{0,5})|(\\?))/s', array($this, 'cb'), substr($arg, $toSkip));
                 if (preg_last_error()) {
                     throw new DibiPcreException();
                 }
             }
             continue;
         }
         if ($comment) {
             $sql[] = '...';
             continue;
         }
         if ($arg instanceof Traversable) {
             $arg = iterator_to_array($arg);
         }
         if (is_array($arg)) {
             if (is_string(key($arg))) {
                 // associative array -> autoselect between SET or VALUES & LIST
                 if ($commandIns === NULL) {
                     $commandIns = strtoupper(substr(ltrim($this->args[0]), 0, 6));
                     $commandIns = $commandIns === 'INSERT' || $commandIns === 'REPLAC';
                     $sql[] = $this->formatValue($arg, $commandIns ? 'v' : 'a');
                 } else {
                     if ($lastArr === $cursor - 1) {
                         $sql[] = ',';
                     }
                     $sql[] = $this->formatValue($arg, $commandIns ? 'l' : 'a');
                 }
                 $lastArr = $cursor;
                 continue;
             }
         }
         // default processing
         $sql[] = $this->formatValue($arg, FALSE);
     }
     // while
     if ($comment) {
         $sql[] = "*/";
     }
     $sql = implode(' ', $sql);
     if ($this->hasError) {
         throw new DibiException('SQL translate error', 0, $sql);
     }
     // apply limit
     if ($this->limit > -1 || $this->offset > 0) {
         $this->driver->applyLimit($sql, $this->limit, $this->offset);
     }
     return $sql;
 }
예제 #3
0
 /**
  * Apply modifier to single value.
  * @param  mixed
  * @param  string
  * @return string
  */
 public function formatValue($value, $modifier)
 {
     if ($this->comment) {
         return '...';
     }
     if (!$this->driver) {
         $this->driver = $this->connection->getDriver();
     }
     // array processing (with or without modifier)
     if ($value instanceof Traversable) {
         $value = iterator_to_array($value);
     }
     if (is_array($value)) {
         $vx = $kx = array();
         switch ($modifier) {
             case 'and':
             case 'or':
                 // key=val AND key IS NULL AND ...
                 if (empty($value)) {
                     return '1=1';
                 }
                 foreach ($value as $k => $v) {
                     if (is_string($k)) {
                         $pair = explode('%', $k, 2);
                         // split into identifier & modifier
                         $k = $this->identifiers->{$pair[0]} . ' ';
                         if (!isset($pair[1])) {
                             $v = $this->formatValue($v, FALSE);
                             $vx[] = $k . ($v === 'NULL' ? 'IS ' : '= ') . $v;
                         } elseif ($pair[1] === 'ex') {
                             // TODO: this will be removed
                             $vx[] = $k . $this->formatValue($v, 'ex');
                         } else {
                             $v = $this->formatValue($v, $pair[1]);
                             if ($pair[1] === 'l' || $pair[1] === 'in') {
                                 $op = 'IN ';
                             } elseif (strpos($pair[1], 'like') !== FALSE) {
                                 $op = 'LIKE ';
                             } elseif ($v === 'NULL') {
                                 $op = 'IS ';
                             } else {
                                 $op = '= ';
                             }
                             $vx[] = $k . $op . $v;
                         }
                     } else {
                         $vx[] = $this->formatValue($v, 'ex');
                     }
                 }
                 return '(' . implode(') ' . strtoupper($modifier) . ' (', $vx) . ')';
             case 'n':
                 // key, key, ... identifier names
                 foreach ($value as $k => $v) {
                     if (is_string($k)) {
                         $vx[] = $this->identifiers->{$k} . (empty($v) ? '' : ' AS ' . $this->identifiers->{$v});
                     } else {
                         $pair = explode('%', $v, 2);
                         // split into identifier & modifier
                         $vx[] = $this->identifiers->{$pair[0]};
                     }
                 }
                 return implode(', ', $vx);
             case 'a':
                 // key=val, key=val, ...
                 foreach ($value as $k => $v) {
                     $pair = explode('%', $k, 2);
                     // split into identifier & modifier
                     $vx[] = $this->identifiers->{$pair[0]} . '=' . $this->formatValue($v, isset($pair[1]) ? $pair[1] : (is_array($v) ? 'ex' : FALSE));
                 }
                 return implode(', ', $vx);
             case 'in':
                 // replaces scalar %in modifier!
             // replaces scalar %in modifier!
             case 'l':
                 // (val, val, ...)
                 foreach ($value as $k => $v) {
                     $pair = explode('%', $k, 2);
                     // split into identifier & modifier
                     $vx[] = $this->formatValue($v, isset($pair[1]) ? $pair[1] : (is_array($v) ? 'ex' : FALSE));
                 }
                 return '(' . ($vx || $modifier === 'l' ? implode(', ', $vx) : 'NULL') . ')';
             case 'v':
                 // (key, key, ...) VALUES (val, val, ...)
                 foreach ($value as $k => $v) {
                     $pair = explode('%', $k, 2);
                     // split into identifier & modifier
                     $kx[] = $this->identifiers->{$pair[0]};
                     $vx[] = $this->formatValue($v, isset($pair[1]) ? $pair[1] : (is_array($v) ? 'ex' : FALSE));
                 }
                 return '(' . implode(', ', $kx) . ') VALUES (' . implode(', ', $vx) . ')';
             case 'm':
                 // (key, key, ...) VALUES (val, val, ...), (val, val, ...), ...
                 foreach ($value as $k => $v) {
                     if (is_array($v)) {
                         if (isset($proto)) {
                             if ($proto !== array_keys($v)) {
                                 $this->hasError = TRUE;
                                 return '**Multi-insert array "' . $k . '" is different.**';
                             }
                         } else {
                             $proto = array_keys($v);
                         }
                     } else {
                         $this->hasError = TRUE;
                         return '**Unexpected type ' . gettype($v) . '**';
                     }
                     $pair = explode('%', $k, 2);
                     // split into identifier & modifier
                     $kx[] = $this->identifiers->{$pair[0]};
                     foreach ($v as $k2 => $v2) {
                         $vx[$k2][] = $this->formatValue($v2, isset($pair[1]) ? $pair[1] : (is_array($v2) ? 'ex' : FALSE));
                     }
                 }
                 foreach ($vx as $k => $v) {
                     $vx[$k] = '(' . implode(', ', $v) . ')';
                 }
                 return '(' . implode(', ', $kx) . ') VALUES ' . implode(', ', $vx);
             case 'by':
                 // key ASC, key DESC
                 foreach ($value as $k => $v) {
                     if (is_array($v)) {
                         $vx[] = $this->formatValue($v, 'ex');
                     } elseif (is_string($k)) {
                         $v = is_string($v) && strncasecmp($v, 'd', 1) || $v > 0 ? 'ASC' : 'DESC';
                         $vx[] = $this->identifiers->{$k} . ' ' . $v;
                     } else {
                         $vx[] = $this->identifiers->{$v};
                     }
                 }
                 return implode(', ', $vx);
             case 'ex':
             case 'sql':
                 $translator = new self($this->connection);
                 return $translator->translate($value);
             default:
                 // value, value, value - all with the same modifier
                 foreach ($value as $v) {
                     $vx[] = $this->formatValue($v, $modifier);
                 }
                 return implode(', ', $vx);
         }
     }
     // with modifier procession
     if ($modifier) {
         if ($value !== NULL && !is_scalar($value) && !$value instanceof DateTime && !$value instanceof DateTimeInterface) {
             // array is already processed
             $this->hasError = TRUE;
             return '**Unexpected type ' . gettype($value) . '**';
         }
         switch ($modifier) {
             case 's':
                 // string
             // string
             case 'bin':
                 // binary
             // binary
             case 'b':
                 // boolean
                 return $value === NULL ? 'NULL' : $this->driver->escape($value, $modifier);
             case 'sN':
                 // string or NULL
             // string or NULL
             case 'sn':
                 return $value == '' ? 'NULL' : $this->driver->escape($value, dibi::TEXT);
                 // notice two equal signs
             // notice two equal signs
             case 'iN':
                 // signed int or NULL
             // signed int or NULL
             case 'in':
                 // deprecated
                 if ($value == '') {
                     $value = NULL;
                 }
                 // intentionally break omitted
             // intentionally break omitted
             case 'i':
                 // signed int
             // signed int
             case 'u':
                 // unsigned int, ignored
                 if ($value === NULL) {
                     return 'NULL';
                 } elseif (is_string($value) && preg_match('#[+-]?\\d++(?:e\\d+)?\\z#A', $value)) {
                     return $value;
                     // support for long numbers - keep them unchanged
                 } elseif (is_string($value) && substr($value, 1, 1) === 'x' && is_numeric($value)) {
                     trigger_error('Support for hex strings has been deprecated.', E_USER_DEPRECATED);
                     return (string) hexdec($value);
                 } else {
                     return (string) (int) $value;
                 }
             case 'f':
                 // float
                 if ($value === NULL) {
                     return 'NULL';
                 } elseif (is_string($value) && is_numeric($value) && substr($value, 1, 1) !== 'x') {
                     return $value;
                     // support for extreme numbers - keep them unchanged
                 } else {
                     return rtrim(rtrim(number_format($value + 0, 10, '.', ''), '0'), '.');
                 }
             case 'd':
                 // date
             // date
             case 't':
                 // datetime
                 if ($value === NULL) {
                     return 'NULL';
                 } else {
                     if (is_numeric($value)) {
                         $value = (int) $value;
                         // timestamp
                     } elseif (is_string($value)) {
                         $value = new DateTime($value);
                     }
                     return $this->driver->escape($value, $modifier);
                 }
             case 'by':
             case 'n':
                 // identifier name
                 return $this->identifiers->{$value};
             case 'ex':
             case 'sql':
                 // preserve as dibi-SQL  (TODO: leave only %ex)
                 $value = (string) $value;
                 // speed-up - is regexp required?
                 $toSkip = strcspn($value, '`[\'":');
                 if (strlen($value) !== $toSkip) {
                     $value = substr($value, 0, $toSkip) . preg_replace_callback('/(?=[`[\'":%?])(?:`(.+?)`|\\[(.+?)\\]|(\')((?:\'\'|[^\'])*)\'|(")((?:""|[^"])*)"|(\'|")|:(?:([a-zA-Z0-9_]*?:)([a-zA-Z0-9._]?)|([a-zA-Z0-9_]+)(?:%([a-zA-Z~][a-zA-Z0-9~]{0,5}))?)|%([a-zA-Z~][a-zA-Z0-9~]{0,5})|(\\?))/s', array($this, 'cb'), substr($value, $toSkip));
                     if (preg_last_error()) {
                         throw new DibiPcreException();
                     }
                 }
                 return $value;
             case 'SQL':
                 // preserve as real SQL (TODO: rename to %sql)
                 return (string) $value;
             case 'like~':
                 // LIKE string%
                 return $this->driver->escapeLike($value, 1);
             case '~like':
                 // LIKE %string
                 return $this->driver->escapeLike($value, -1);
             case '~like~':
                 // LIKE %string%
                 return $this->driver->escapeLike($value, 0);
             case 'and':
             case 'or':
             case 'a':
             case 'l':
             case 'v':
                 $this->hasError = TRUE;
                 return '**Unexpected type ' . gettype($value) . '**';
             default:
                 $this->hasError = TRUE;
                 return "**Unknown or invalid modifier %{$modifier}**";
         }
     }
     // without modifier procession
     if (is_string($value)) {
         return $this->driver->escape($value, dibi::TEXT);
     } elseif (is_int($value)) {
         return (string) $value;
     } elseif (is_float($value)) {
         return rtrim(rtrim(number_format($value, 10, '.', ''), '0'), '.');
     } elseif (is_bool($value)) {
         return $this->driver->escape($value, dibi::BOOL);
     } elseif ($value === NULL) {
         return 'NULL';
     } elseif ($value instanceof DateTime || $value instanceof DateTimeInterface) {
         return $this->driver->escape($value, dibi::DATETIME);
     } elseif ($value instanceof DibiLiteral) {
         return (string) $value;
     } else {
         $this->hasError = TRUE;
         return '**Unexpected ' . gettype($value) . '**';
     }
 }
예제 #4
0
IDataSource{private$connection;private$sql;private$result;private$count;private$totalCount;private$cols=array();private$sorting=array();private$conds=array();private$offset;private$limit;function
__construct($sql,DibiConnection$connection){if(strpbrk($sql," \t\r\n")===FALSE){$this->sql=$connection->getDriver()->escape($sql,dibi::IDENTIFIER);}else{$this->sql='('.$sql.') t';}$this->connection=$connection;}function
예제 #5
0
 public function __construct(DibiConnection $connection)
 {
     $this->connection = $connection;
     $this->driver = $connection->getDriver();
     $this->identifiers = new DibiHashMap(array($this, 'delimite'));
 }
예제 #6
0
 public function __construct($sql, DibiConnection $connection)
 {
     if (strpos($sql, ' ') === FALSE) {
         $this->sql = $connection->getDriver()->escape($sql, dibi::IDENTIFIER);
     } else {
         $this->sql = '(' . $sql . ') t';
     }
     $this->connection = $connection;
 }