public function load($condition) { $condition = trim($condition); $e = Doctrine_Tokenizer::sqlExplode($condition); if (count($e) > 2) { $a = explode('.', $e[0]); $field = array_pop($a); $reference = implode('.', $a); $operator = $e[1]; $value = $e[2]; $alias = $this->query->getTableAlias($reference); $map = $this->query->getAliasDeclaration($reference); $table = $map['table']; // check if value is enumerated value $enumIndex = $table->enumIndex($field, trim($value, "'")); if (substr($value, 0, 1) == '(') { // trim brackets $trimmed = Doctrine_Tokenizer::bracketTrim($value); if (substr($trimmed, 0, 4) == 'FROM' || substr($trimmed, 0, 6) == 'SELECT') { // subquery found $q = new Doctrine_Query(); $value = '(' . $q->parseQuery($trimmed)->getQuery() . ')'; } elseif (substr($trimmed, 0, 4) == 'SQL:') { $value = '(' . substr($trimmed, 4) . ')'; } else { // simple in expression found $e = Doctrine_Tokenizer::sqlExplode($trimmed, ','); $value = array(); foreach ($e as $part) { $index = $table->enumIndex($field, trim($part, "'")); if ($index !== false) { $value[] = $index; } else { $value[] = $this->parseLiteralValue($part); } } $value = '(' . implode(', ', $value) . ')'; } } else { if ($enumIndex !== false) { $value = $enumIndex; } else { $value = $this->parseLiteralValue($value); } } switch ($operator) { case '<': case '>': case '=': case '!=': if ($enumIndex !== false) { $value = $enumIndex; } default: $condition = $alias . '.' . $field . ' ' . $operator . ' ' . $value; } } return $condition; }
public function parse($dql) { $parts = Doctrine_Tokenizer::sqlExplode($dql, ','); $result = array(); foreach ($parts as $part) { $set = Doctrine_Tokenizer::sqlExplode($part, '='); $e = explode('.', trim($set[0])); $field = array_pop($e); $reference = implode('.', $e); $alias = $this->query->getTableAlias($reference); $map = $this->query->getAliasDeclaration($reference); $result[] = $map['table']->getColumnName($field) . ' = ' . $set[1]; } return implode(', ', $result); }
/** * parseQuery * parses an sql query and adds the parts to internal array * * @param string $query query to be parsed * @return Doctrine_RawSql this object */ public function parseQuery($query) { preg_match_all('/{([^}{]*)}/U', $query, $m); $this->fields = $m[1]; $this->clear(); $e = Doctrine_Tokenizer::sqlExplode($query, ' '); foreach ($e as $k => $part) { $low = strtolower($part); switch (strtolower($part)) { case 'select': case 'from': case 'where': case 'limit': case 'offset': case 'having': $p = $low; if (!isset($parts[$low])) { $parts[$low] = array(); } break; case 'order': case 'group': $i = $k + 1; if (isset($e[$i]) && strtolower($e[$i]) === 'by') { $p = $low; $p .= 'by'; $parts[$low . 'by'] = array(); } else { $parts[$p][] = $part; } break; case 'by': continue; default: if (!isset($parts[$p][0])) { $parts[$p][0] = $part; } else { $parts[$p][0] .= ' ' . $part; } } } $this->parts = $parts; $this->parts['select'] = array(); return $this; }
/** * parseClause * * @param string $alias component alias * @param string $field the field name * @param mixed $value the value of the field * @return void */ public function parseClause($dql) { $parts = Doctrine_Tokenizer::sqlExplode($dql, ' AND '); if (count($parts) > 1) { $ret = array(); foreach ($parts as $part) { $ret[] = $this->parseSingle($part); } $r = implode(' AND ', $ret); } else { $parts = Doctrine_Tokenizer::quoteExplode($dql, ' OR '); if (count($parts) > 1) { $ret = array(); foreach ($parts as $part) { $ret[] = $this->parseClause($part); } $r = implode(' OR ', $ret); } else { $ret = $this->parseSingle($dql); return $ret; } } return '(' . $r . ')'; }
/** * load * returns the parsed query part * * @param string $where * @return string */ public function load($where) { $where = trim($where); $conn = $this->query->getConnection(); $e = Doctrine_Tokenizer::sqlExplode($where); if (count($e) > 1) { $tmp = $e[0] . ' ' . $e[1]; if (substr($tmp, 0, 6) == 'EXISTS') { return $this->parseExists($where, true); } elseif (substr($where, 0, 10) == 'NOT EXISTS') { return $this->parseExists($where, false); } } if (count($e) < 3) { $e = Doctrine_Tokenizer::sqlExplode($where, array('=', '<', '>', '!=')); } $r = array_shift($e); $a = explode('.', $r); if (count($a) > 1) { $field = array_pop($a); $count = count($e); $slice = array_slice($e, -1, 1); $value = implode('', $slice); $operator = trim(substr($where, strlen($r), -strlen($value))); $reference = implode('.', $a); $count = count($a); $pos = strpos($field, '('); if ($pos !== false) { $func = substr($field, 0, $pos); $value = trim(substr($field, $pos + 1, -1)); $values = Doctrine_Query::sqlExplode($value, ','); $field = array_pop($a); $reference = implode('.', $a); $table = $this->query->load($reference, false); $field = $table->getColumnName($field); array_pop($a); $reference2 = implode('.', $a); $alias = $this->query->getTableAlias($reference2); $stack = $this->query->getRelationStack(); $relation = end($stack); $stack = $this->query->getTableStack(); switch ($func) { case 'contains': case 'regexp': case 'like': $operator = $this->getOperator($func); if (empty($relation)) { throw new Doctrine_Query_Exception('DQL functions contains/regexp/like can only be used for fields of related components'); } $where = array(); foreach ($values as $value) { $where[] = $conn->quoteIdentifier($alias . '.' . $relation->getLocal()) . ' IN (SELECT ' . $conn->quoteIdentifier($relation->getForeign()) . ' FROM ' . $conn->quoteIdentifier($relation->getTable()->getTableName()) . ' WHERE ' . $field . $operator . $value . ')'; } $where = implode(' AND ', $where); break; default: throw new Doctrine_Query_Exception('Unknown DQL function: ' . $func); } } else { $map = $this->query->load($reference, false); $alias = $this->query->getTableAlias($reference); $table = $map['table']; $field = $table->getColumnName($field); // check if value is enumerated value $enumIndex = $table->enumIndex($field, trim($value, "'")); if (substr($value, 0, 1) == '(') { // trim brackets $trimmed = Doctrine_Tokenizer::bracketTrim($value); if (substr($trimmed, 0, 4) == 'FROM' || substr($trimmed, 0, 6) == 'SELECT') { // subquery found $q = new Doctrine_Query(); $value = '(' . $q->isSubquery(true)->parseQuery($trimmed)->getQuery() . ')'; } elseif (substr($trimmed, 0, 4) == 'SQL:') { $value = '(' . substr($trimmed, 4) . ')'; } else { // simple in expression found $e = Doctrine_Tokenizer::sqlExplode($trimmed, ','); $value = array(); foreach ($e as $part) { $index = $table->enumIndex($field, trim($part, "'")); if ($index !== false) { $value[] = $index; } else { $value[] = $this->parseLiteralValue($part); } } $value = '(' . implode(', ', $value) . ')'; } } elseif (substr($value, 0, 1) == ':' || $value === '?') { // placeholder found if ($table->getTypeOf($field) == 'enum') { $this->query->addEnumParam($value, $table, $field); } else { $this->query->addEnumParam($value, null, null); } } else { if ($enumIndex !== false) { $value = $enumIndex; } else { $value = $this->parseLiteralValue($value); } } switch ($operator) { case '<': case '>': case '=': case '!=': if ($enumIndex !== false) { $value = $enumIndex; } default: if ($this->query->getType() === Doctrine_Query::SELECT) { $fieldname = $alias ? $conn->quoteIdentifier($alias . '.' . $field) : $field; } else { $fieldname = $field; } $where = $fieldname . ' ' . $operator . ' ' . $value; } } } return $where; }
/** * tokenizeQuery * splits the given dql query into an array where keys * represent different query part names and values are * arrays splitted using sqlExplode method * * example: * * parameter: * $query = "SELECT u.* FROM User u WHERE u.name LIKE ?" * returns: * array('select' => array('u.*'), * 'from' => array('User', 'u'), * 'where' => array('u.name', 'LIKE', '?')) * * @param string $query DQL query * @throws Doctrine_Query_Exception if some generic parsing error occurs * @return array an array containing the query string parts */ public function tokenizeQuery($query) { $e = Doctrine_Tokenizer::sqlExplode($query, ' '); foreach ($e as $k => $part) { $part = trim($part); switch (strtolower($part)) { case 'delete': case 'update': case 'select': case 'set': case 'from': case 'where': case 'limit': case 'offset': case 'having': $p = $part; $parts[$part] = array(); break; case 'order': case 'group': $i = $k + 1; if (isset($e[$i]) && strtolower($e[$i]) === "by") { $p = $part; $parts[$part] = array(); } else { $parts[$p][] = $part; } break; case "by": continue; default: if (!isset($p)) { throw new Doctrine_Query_Exception("Couldn't parse query."); } $parts[$p][] = $part; } } return $parts; }