/**
* @dataProvider dataExpandListParameters
* @param type $q
* @param type $p
* @param type $t
* @param type $expectedQuery
* @param type $expectedParams
* @param type $expectedTypes
*/
public function testExpandListParameters($q, $p, $t, $expectedQuery, $expectedParams, $expectedTypes)
{
list($query, $params, $types) = SQLParserUtils::expandListParameters($q, $p, $t);
$this->assertEquals($expectedQuery, $query, "Query was not rewritten correctly.");
$this->assertEquals($expectedParams, $params, "Params dont match");
$this->assertEquals($expectedTypes, $types, "Types dont match");
}
/**
* @param Query $query
*
* @return string
*
* @throws QueryException
*/
public static function getExecutableSql(Query $query)
{
$parserResult = static::parseQuery($query);
$sql = $parserResult->getSqlExecutor()->getSqlStatements();
list($params, $types) = QueryUtils::processParameterMappings($query, $parserResult->getParameterMappings());
list($sql, $params, $types) = SQLParserUtils::expandListParameters($sql, $params, $types);
$paramPos = SQLParserUtils::getPlaceholderPositions($sql);
for ($i = count($paramPos) - 1; $i >= 0; $i--) {
$sql = substr_replace($sql, $query->getEntityManager()->getConnection()->quote($params[$i], $types[$i]), $paramPos[$i], 1);
}
return $sql;
}
public function startQuery($sql, array $params = null, array $types = null)
{
if ($this->logger) {
$this->logger->startQuery($sql, $params, $types);
}
// Store select queries for later use
if (substr($sql, 0, 6) == 'SELECT') {
if ($params) {
// Attempt to replace placeholders so that we can log a final SQL query for profiler's EXPLAIN statement
// (this is not perfect-- getPlaceholderPositions has some flaws-- but it should generally work with ORM-generated queries)
$is_positional = is_numeric(key($params));
list($sql, $params, $types) = \Doctrine\DBAL\SQLParserUtils::expandListParameters($sql, $params, $types);
if (empty($types)) {
$types = array();
}
$placeholders = \Doctrine\DBAL\SQLParserUtils::getPlaceholderPositions($sql, $is_positional);
if ($is_positional) {
$map = array_flip($placeholders);
} else {
$map = array();
foreach ($placeholders as $name => $positions) {
foreach ($positions as $pos) {
$map[$pos] = $name;
}
}
}
ksort($map);
$src_pos = 0;
$final_sql = '';
$first_param_index = key($params);
foreach ($map as $pos => $replace_name) {
$final_sql .= substr($sql, $src_pos, $pos - $src_pos);
if ($sql[$pos] == ':') {
$src_pos = $pos + strlen($replace_name);
$index = trim($replace_name, ':');
} else {
$src_pos = $pos + 1;
$index = $replace_name + $first_param_index;
}
$final_sql .= \D::manager()->getConnection()->quote($params[$index], \Arr::get($types, $index));
}
$final_sql .= substr($sql, $src_pos);
$this->queries[] = $final_sql;
} else {
$this->queries[] = $sql;
}
}
}
/**
* {@inheritdoc}
*/
public function startQuery($sql, array $params = null, array $types = null)
{
if ($params) {
list($sql, $params, $types) = SQLParserUtils::expandListParameters($sql, $params, $types);
$query = vsprintf(str_replace('?', "%s", $sql), call_user_func(function () use($params, $types) {
$quotedParams = array();
foreach ($params as $typeIndex => $value) {
$quotedParams[] = $this->connection->quote($value, $types[$typeIndex]);
}
return $quotedParams;
}));
} else {
$query = $sql;
}
$this->lastQuery = $query;
if ($this->outputQuery) {
$this->output($query);
}
}
/**
* {@inheritdoc}
*/
public function executeQuery($query, array $params = array(), $types = array(), QueryCacheProfile $qcp = null)
{
if ($qcp !== null) {
return $this->executeCacheQuery($query, $params, $types, $qcp);
}
$this->connect();
$logger = $this->_config->getSQLLogger();
if ($logger) {
$logger->startQuery($query, $params, $types);
}
try {
list($query, $params, $types) = SQLParserUtils::expandListParameters($query, $params, $types);
$preparedData = $this->_conn->prepare($query);
$strictValues = \Cassandra\Request\Request::strictTypeValues($params, $preparedData['metadata']['columns']);
$stmt = $this->_conn->executeSync($preparedData['id'], $strictValues);
$stmt->setMetadata($preparedData['result_metadata']);
} catch (\Exception $ex) {
throw DBALException::driverExceptionDuringQuery($this->_driver, $ex, $query, $this->resolveParams($params, $types));
}
if ($logger) {
$logger->stopQuery();
}
return $stmt;
}
/**
* Executes an SQL INSERT/UPDATE/DELETE query with the given parameters
* and returns the number of affected rows.
*
* This method supports PDO binding types as well as DBAL mapping types.
*
* @param string $query The SQL query.
* @param array $params The query parameters.
* @param array $types The parameter types.
*
* @return integer The number of affected rows.
*
* @throws \Doctrine\DBAL\DBALException
*
* @internal PERF: Directly prepares a driver statement, not a wrapper.
*/
public function executeUpdate($query, array $params = array(), array $types = array())
{
$this->connect();
$logger = $this->_config->getSQLLogger();
if ($logger) {
$logger->startQuery($query, $params, $types);
}
try {
if ($params) {
list($query, $params, $types) = SQLParserUtils::expandListParameters($query, $params, $types);
$stmt = $this->_conn->prepare($query);
if ($types) {
$this->_bindTypedValues($stmt, $params, $types);
$stmt->execute();
} else {
$stmt->execute($params);
}
$result = $stmt->rowCount();
} else {
$result = $this->_conn->exec($query);
}
} catch (\Exception $ex) {
throw DBALException::driverExceptionDuringQuery($ex, $query, $this->resolveParams($params, $types));
}
if ($logger) {
$logger->stopQuery();
}
return $result;
}
/**
* @return NativeQuery
*/
protected function getLimitedQuery()
{
$copy = clone $this->nativeQuery;
$copy->setParameters([]);
try {
$params = $types = [];
/** @var Query\Parameter $param */
foreach ($this->nativeQuery->getParameters() as $param) {
$params[$param->getName()] = $param->getValue();
$types[$param->getName()] = $param->getType();
}
list($query, $params, $types) = SQLParserUtils::expandListParameters($copy->getSQL(), $params, $types);
$copy->setSQL($query);
foreach ($params as $i => $value) {
$copy->setParameter($i, $value, isset($types[$i]) ? $types[$i] : NULL);
}
} catch (SQLParserUtilsException $e) {
$copy->setParameters(clone $this->nativeQuery->getParameters());
}
if ($this->maxResults !== NULL || $this->firstResult !== NULL) {
$em = $this->nativeQuery->getEntityManager();
$platform = $em->getConnection()->getDatabasePlatform();
$copy->setSQL($platform->modifyLimitQuery($copy->getSQL(), $this->maxResults, $this->firstResult));
}
return $copy;
}
private function extractParameterPsitions($query)
{
return \Doctrine\DBAL\SQLParserUtils::getPlaceholderPositions($query, false);
}
/**
* @param string $query
* @param array $params
* @param array $types
* @param \Doctrine\DBAL\Platforms\AbstractPlatform $platform
* @throws \Doctrine\DBAL\DBALException
* @throws \Nette\Utils\RegexpException
* @return string
*/
public static function formatQuery($query, $params, array $types = [], AbstractPlatform $platform = NULL)
{
if (!$platform) {
$platform = new Doctrine\DBAL\Platforms\MySqlPlatform();
}
if (!$types) {
foreach ($params as $key => $param) {
if (is_array($param)) {
$types[$key] = Doctrine\DBAL\Connection::PARAM_STR_ARRAY;
} else {
$types[$key] = 'string';
}
}
}
try {
list($query, $params, $types) = \Doctrine\DBAL\SQLParserUtils::expandListParameters($query, $params, $types);
} catch (Doctrine\DBAL\SQLParserUtilsException $e) {
}
$formattedParams = [];
foreach ($params as $key => $param) {
if (isset($types[$key])) {
if (is_scalar($types[$key]) && array_key_exists($types[$key], Type::getTypesMap())) {
$types[$key] = Type::getType($types[$key]);
}
/** @var Type[] $types */
if ($types[$key] instanceof Type) {
$param = $types[$key]->convertToDatabaseValue($param, $platform);
}
}
$formattedParams[] = SimpleParameterFormatter::format($param);
}
$params = $formattedParams;
if (Nette\Utils\Validators::isList($params)) {
$parts = explode('?', $query);
if (count($params) > $parts) {
throw new Kdyby\Doctrine\InvalidStateException("Too mny parameters passed to query.");
}
return implode('', Kdyby\Doctrine\Helpers::zipper($parts, $params));
}
return Strings::replace($query, '~(\\:[a-z][a-z0-9]*|\\?[0-9]*)~i', function ($m) use(&$params) {
if (substr($m[0], 0, 1) === '?') {
if (strlen($m[0]) > 1) {
if (isset($params[$k = substr($m[0], 1)])) {
return $params[$k];
}
} else {
return array_shift($params);
}
} else {
if (isset($params[$k = substr($m[0], 1)])) {
return $params[$k];
}
}
return $m[0];
});
}
/**
* Executes an SQL INSERT/UPDATE/DELETE query with the given parameters
* and returns the number of affected rows.
*
* This method supports PDO binding types as well as DBAL mapping types.
*
* @param string $query The SQL query.
* @param array $params The query parameters.
* @param array $types The parameter types.
* @return integer The number of affected rows.
* @internal PERF: Directly prepares a driver statement, not a wrapper.
*/
public function executeUpdate($query, array $params = array(), array $types = array())
{
$this->connect();
$hasLogger = $this->_config->getSQLLogger() !== null;
if ($hasLogger) {
$this->_config->getSQLLogger()->startQuery($query, $params, $types);
}
if ($params) {
list($query, $params, $types) = SQLParserUtils::expandListParameters($query, $params, $types);
$stmt = $this->_conn->prepare($query);
if ($types) {
$this->_bindTypedValues($stmt, $params, $types);
$stmt->execute();
} else {
$stmt->execute($params);
}
$result = $stmt->rowCount();
} else {
$result = $this->_conn->exec($query);
}
if ($hasLogger) {
$this->_config->getSQLLogger()->stopQuery();
}
return $result;
}
/**
* @dataProvider dataQueryWithMissingParameters
*/
public function testExceptionIsThrownForMissingParam($query, $params, $types = array())
{
$this->setExpectedException('Doctrine\\DBAL\\SQLParserUtilsException', 'Value for :param not found in params array. Params array key should be "param"');
SQLParserUtils::expandListParameters($query, $params, $types);
}
public function testDoctrinebug()
{
return 'go on!';
$query = "SELECT t0.id AS id1, t0.title AS title2, t0.gameTitle AS gameTitle3, t0.abbrevation AS abbrevation4, t0.rbvNumber AS rbvNumber5, t0.oidStart AS oidStart6, t0.oidEnd AS oidEnd7, t0.importDate AS importDate8, t0.postProcessed AS postProcessed9, t0.visible AS visible10, t0.soundNum AS soundNum11 FROM products t0 WHERE t0.id IN (?)";
$types = array(0 => 101);
$params = array(0 => array());
\Doctrine\DBAL\SQLParserUtils::expandListParameters($query, $params, $types);
}
/**
* {@inheritdoc}
*/
protected function doExecute(InputInterface $input, OutputInterface $output)
{
$availableTypes = array('document', 'audio', 'video', 'image', 'flash', 'map');
$typesOption = $input->getArgument('type');
$recordsType = explode(',', $typesOption);
$recordsType = array_filter($recordsType, function ($type) use($availableTypes) {
return in_array($type, $availableTypes);
});
if (count($recordsType) === 0) {
$output->write(sprintf('Invalid records type provided %s', implode(', ', $availableTypes)));
return;
}
$subdefsOption = $input->getArgument('subdefs');
$subdefsName = explode(',', $subdefsOption);
if (count($subdefsOption) === 0) {
$output->write('No subdef options provided');
return;
}
$sqlCount = "SELECT COUNT(DISTINCT(r.record_id)) AS nb_records" . " FROM record r LEFT JOIN subdef s ON (r.record_id = s.record_id AND s.name IN (?))" . " WHERE r.type IN (?)";
$types = array(Connection::PARAM_STR_ARRAY, Connection::PARAM_STR_ARRAY);
$params = array($subdefsName, $recordsType);
if (null !== ($min = $input->getOption('min_record'))) {
$sqlCount .= " AND (r.record_id >= ?)";
$params[] = (int) $min;
$types[] = \PDO::PARAM_INT;
}
if (null !== ($max = $input->getOption('max_record'))) {
$sqlCount .= " AND (r.record_id <= ?)";
$params[] = (int) $max;
$types[] = \PDO::PARAM_INT;
}
$substitutionOnly = $input->getOption('substitution-only');
$withSubstitution = $input->getOption('with-substitution');
if (false === $withSubstitution) {
if (true === $substitutionOnly) {
$sqlCount .= " AND (ISNULL(s.substit) OR s.substit = 1)";
} else {
$sqlCount .= " AND (ISNULL(s.substit) OR s.substit = 0)";
}
} elseif ($substitutionOnly) {
throw new InvalidArgumentException('Conflict, you can not ask for --substitution-only && --with-substitution parameters at the same time');
}
list($sqlCount, $stmtParams) = SQLParserUtils::expandListParameters($sqlCount, $params, $types);
$databox = $this->container->findDataboxById($input->getArgument('databox'));
$output->writeln($sqlCount);
$connection = $databox->get_connection();
$stmt = $connection->prepare($sqlCount);
$stmt->execute($stmtParams);
$row = $stmt->fetch();
$totalRecords = $row['nb_records'];
if ($totalRecords === 0) {
return;
}
/** @var HelperSet $helperSet */
$helperSet = $this->getHelperSet();
/** @var ProgressBar $progress */
$progress = $helperSet->get('progress');
$progress->start($output, $totalRecords);
$progress->display();
$sql = "SELECT DISTINCT(r.record_id)" . " FROM record r LEFT JOIN subdef s ON (r.record_id = s.record_id AND s.name IN (?))" . " WHERE r.type IN (?)";
$types = array(Connection::PARAM_STR_ARRAY, Connection::PARAM_STR_ARRAY);
$params = array($subdefsName, $recordsType);
if ($min) {
$sql .= " AND (r.record_id >= ?)";
$params[] = (int) $min;
$types[] = \PDO::PARAM_INT;
}
if ($max) {
$sql .= " AND (r.record_id <= ?)";
$params[] = (int) $max;
$types[] = \PDO::PARAM_INT;
}
if (false === $withSubstitution) {
if (true === $substitutionOnly) {
$sql .= " AND (ISNULL(s.substit) OR s.substit = 1)";
} else {
$sql .= " AND (ISNULL(s.substit) OR s.substit = 0)";
}
}
list($sql, $stmtParams) = SQLParserUtils::expandListParameters($sql, $params, $types);
$connection = $databox->get_connection();
$stmt = $connection->prepare($sql);
$stmt->execute($stmtParams);
$rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);
foreach ($rows as $row) {
$output->write(sprintf(' (#%s)', $row['record_id']));
$record = new \record_adapter($this->container, $databox->get_sbas_id(), $row['record_id']);
/** @var media_subdef[] $subdefs */
$subdefs = array_filter($record->get_subdefs(), function (media_subdef $subdef) use($subdefsName) {
return in_array($subdef->get_name(), $subdefsName);
});
foreach ($subdefs as $subdef) {
$subdef->remove_file();
if ($withSubstitution && $subdef->is_substituted() || $substitutionOnly) {
$subdef->set_substituted(false);
}
}
/** @var SubdefGenerator $subdefGenerator */
$subdefGenerator = $this->container['subdef.generator'];
$subdefGenerator->generateSubdefs($record, $subdefsName);
$stmt->closeCursor();
$progress->advance();
}
unset($rows, $record, $stmt, $connection);
$progress->finish();
}
