If the query is parametrized, a prepared statement is used.
If an SQLLogger is configured, the execution is logged.
| public executeQuery ( string $query, array $params = [], array $types = [], Doctrine\DBAL\Cache\QueryCacheProfile $qcp = null ) : Doctrine\DBAL\Driver\Statement | ||
| $query | string | The SQL query to execute. |
| $params | array | The parameters to bind to the query, if any. |
| $types | array | The types the previous parameters are in. |
| $qcp | Doctrine\DBAL\Cache\QueryCacheProfile | The query cache profile, optional. |
| return | Doctrine\DBAL\Driver\Statement | The executed statement. |
public function exportData($export, $reporter)
{
$this->conn->beginTransaction();
try {
$lastExportedAt = (int) $export['lastExportedAt'];
$areas = $this->conn->fetchAll('SELECT a.`id`, a.`name`, t.`id` AS `territoryId`, t.`name` AS `territoryName`, a.`customData`, a.`lastUpdatedAt` ' . 'FROM `' . CoreTables::AREA_TBL . '` a ' . 'INNER JOIN `' . CoreTables::TERRITORY_TBL . '` t ON t.`id` = a.`territoryId` ' . 'WHERE a.`projectId` = :projectId AND a.`statusId` = :statusId', [':projectId' => $export['projectId'], ':statusId' => $export['areaStatusId']]);
$block = new ExportBlock();
foreach ($areas as $area) {
$block->addId($area['id']);
if ($area['lastUpdatedAt'] > $lastExportedAt) {
$area['customData'] = json_decode($area['customData']);
$block->addUpdatedId($area['id']);
$block->addUpdate($area);
}
}
$event = new ExportEvent($export['projectId'], $export['lastExportedAt'], $reporter);
$event->addBlock('area', $block);
$event = $this->eventDispatcher->dispatch(ExportEvents::EXPORT_ONGOING, $event);
$this->conn->executeQuery('UPDATE `' . ExportTables::DATA_EXPORT_TBL . '` SET `lastExportedAt` = :time WHERE `id` = :id', [':time' => time(), ':id' => $export['id']]);
$this->conn->commit();
return $event->output();
} catch (Exception $ex) {
$this->conn->rollBack();
throw $ex;
}
}
function exportTableList()
{
return array_map(function ($row) {
$values = array_values($row);
return $values[0];
}, $this->db->executeQuery('SHOW TABLES')->fetchAll());
}
/**
* @param LoggerInterface $logger
* @param bool $dryRun
*/
protected function doExecute(LoggerInterface $logger, $dryRun = false)
{
$duplicateEntitiesQuery = 'SELECT
DISTINCT t2.id
FROM
orocrm_campaign_email_stats AS t1
LEFT JOIN orocrm_campaign_email_stats AS t2
ON t1.email_campaign_id = t2.email_campaign_id
AND t1.marketing_list_item_id = t2.marketing_list_item_id
AND t2.id > t1.id
WHERE t2.id IS NOT NULL';
// Done in 2 queries for cross DB support.
$idsToRemove = array_map(function ($item) {
if (is_array($item) && array_key_exists('id', $item)) {
return $item['id'];
}
return null;
}, $this->connection->fetchAll($duplicateEntitiesQuery));
if ($idsToRemove) {
$query = 'DELETE FROM orocrm_campaign_email_stats WHERE id IN (?)';
$logger->notice($query);
if (!$dryRun) {
$this->connection->executeQuery($query, [$idsToRemove], [Connection::PARAM_INT_ARRAY]);
}
}
}
/**
* @param QueryInterface $query
* @return int
*/
public function execute(QueryInterface $query)
{
$query->checkReplacements();
$plainQuery = $query->getPlainQuery();
list($parameters, $plainQuery) = $this->modifyParametersFromArrayToScalar($query->getParameters(), $plainQuery);
return $this->connection->executeQuery($query->getPlainQuery(), $parameters);
}
public function stopQuery()
{
if ($this->explainRunning) {
return;
}
$keys = array_keys($this->queries);
$key = end($keys);
$this->queries[$key][self::TIME] = Debugger::timer('doctrine');
$this->totalTime += $this->queries[$key][self::TIME];
// get EXPLAIN for SELECT queries
if ($this->doExplains) {
if ($this->connection === NULL) {
throw new \Nette\InvalidStateException('You must set a Doctrine\\DBAL\\Connection to get EXPLAIN.');
}
$query = $this->queries[$key][self::SQL];
if (strtoupper(substr(ltrim($query), 0, 6)) !== 'SELECT') {
// only SELECTs are supported
return;
}
// prevent logging explains & infinite recursion
$this->explainRunning = TRUE;
$params = $this->queries[$key][self::PARAMS];
$types = $this->queries[$key][self::TYPES];
$stmt = $this->connection->executeQuery('EXPLAIN ' . $query, $params, $types);
$this->queries[$key][self::EXPLAIN] = $stmt->fetchAll();
$this->explainRunning = FALSE;
}
}
/**
* Update status
*
* @param string $nonce
* @param string $apiUrls
*
* @return int
*/
public function updateStatus($nonce, $apiUrls)
{
// Truncate table
$this->con->executeQuery($this->con->getDatabasePlatform()->getTruncateTableSQL($this->tables['status']));
// Insert new row
return $this->con->insert($this->tables['status'], array('nonce' => $nonce, 'noncets' => time(), 'apiurls' => $apiUrls, 'modified' => new \DateTime()), array(\PDO::PARAM_STR, \PDO::PARAM_INT, \PDO::PARAM_STR, 'datetime'));
}
/**
* Retrieves hte index database.
*
* @return Connection
*/
public function getConnection()
{
if (!$this->connection) {
$isNewDatabase = !file_exists($this->databasePath);
$configuration = new Configuration();
$this->connection = DriverManager::getConnection(['driver' => 'pdo_sqlite', 'path' => $this->databasePath], $configuration);
$outOfDate = null;
if ($isNewDatabase) {
$this->createDatabaseTables($this->connection);
// NOTE: This causes a database write and will cause locking problems if multiple PHP processes are
// spawned and another one is also writing (e.g. indexing).
$this->connection->executeQuery('PRAGMA user_version=' . $this->databaseVersion);
} else {
$version = $this->connection->executeQuery('PRAGMA user_version')->fetchColumn();
if ($version < $this->databaseVersion) {
$this->connection->close();
$this->connection = null;
@unlink($this->databasePath);
return $this->getConnection();
// Do it again.
}
}
}
// Have to be a douche about this as these PRAGMA's seem to reset, even though the connection is not closed.
$this->connection->executeQuery('PRAGMA foreign_keys=ON');
// Data could become corrupted if the operating system were to crash during synchronization, but this
// matters very little as we will just reindex the project next time. In the meantime, this majorly reduces
// hard disk I/O during indexing and increases indexing speed dramatically (dropped from over a minute to a
// couple of seconds for a very small (!) project).
$this->connection->executeQuery('PRAGMA synchronous=OFF');
return $this->connection;
}
private function fetchRows()
{
$statement = $this->connection->executeQuery($this->getSQLForAllRows());
$rows = $statement->fetchAll(\PDO::FETCH_ASSOC);
$statement->closeCursor();
return $rows;
}
/**
* Proxy method to connection object. If an error occurred because of unfound table, tries to create table and rerun request.
*
* @param string $query SQL query
* @param array $parameters query parameters
*/
protected function runSQL($query, array $parameters = array())
{
try {
return $this->connection->executeQuery($query, $parameters);
} catch (\Exception $e) {
$this->connection->executeQuery(sprintf('CREATE TABLE %s (`key` VARCHAR(40), `value` TEXT);', $this->tableName));
}
return $this->connection->executeQuery($query, $parameters);
}
/**
* Proxy method to connection object. If an error occurred because of unfound table, tries to create table and rerun request.
*
* @param string $query SQL query
* @param array $parameters query parameters
*/
protected function runSQL($query, array $parameters = array())
{
try {
return $this->connection->executeQuery($query, $parameters);
} catch (\Exception $e) {
$this->connection->executeQuery(sprintf(self::TABLE_CREATE, $this->tableName));
}
return $this->connection->executeQuery($query, $parameters);
}
/**
* @inheritdoc
*/
public function update()
{
try {
$constraint = $this->getForeignKeyConstraint('s_import_export_session', 'log_id');
$this->dbalConnection->executeQuery('ALTER TABLE s_import_export_session DROP FOREIGN KEY ' . $constraint);
} catch (\Exception $exception) {
}
$this->dbalConnection->executeQuery('ALTER TABLE s_import_export_session DROP COLUMN log_id');
$this->removeImportFilesAlbum();
}
/**
* Updates the statistics for area requests in the current day.
*
* @param \Cantiga\CoreBundle\Repository\AreaRequestEvent $event
*/
public function onAreaRequestStatusChange(AreaRequestEvent $event)
{
$project = $event->getAreaRequest()->getProject();
$values = [0 => 0, 1 => 0, 2 => 0, 3 => 0];
$calculated = $this->conn->fetchAll('SELECT `status`, COUNT(`id`) AS `counted` FROM `' . CoreTables::AREA_REQUEST_TBL . '` WHERE `projectId` = :projectId GROUP BY `status`', [':projectId' => $project->getId()]);
foreach ($calculated as $row) {
$values[$row['status']] = $row['counted'];
}
$date = date('Y-m-d');
$this->conn->executeQuery('INSERT INTO `' . CoreTables::STAT_ARQ_TIME_TBL . '` (`projectId`, `datePoint`, `requestsNew`, `requestsVerification`, `requestsApproved`, `requestsRejected`)' . 'VALUES(:projectId, :datePoint, :rn1, :rv1, :ra1, :rr1) ON DUPLICATE KEY UPDATE `requestsNew` = :rn2, `requestsVerification` = :rv2, `requestsApproved` = :ra2, `requestsRejected` = :rr2', [':projectId' => $project->getId(), ':datePoint' => $date, ':rn1' => $values[AreaRequest::STATUS_NEW], ':rv1' => $values[AreaRequest::STATUS_VERIFICATION], ':ra1' => $values[AreaRequest::STATUS_APPROVED], ':rr1' => $values[AreaRequest::STATUS_REVOKED], ':rn2' => $values[AreaRequest::STATUS_NEW], ':rv2' => $values[AreaRequest::STATUS_VERIFICATION], ':ra2' => $values[AreaRequest::STATUS_APPROVED], ':rr2' => $values[AreaRequest::STATUS_REVOKED]]);
}
/**
*/
public function create()
{
$createTableSql = 'CREATE TABLE IF NOT EXISTS events (
organisation TEXT,
project TEXT,
event_id INTEGER,
created TEXT,
UNIQUE(organisation, project, event_id, created)
)';
$this->connection->executeQuery($createTableSql);
}
/**
* @param $table
* @param $primaryKeyName
* @param $keyName
* @param $keyValues
* @return array
* @throws \Doctrine\DBAL\DBALException
*/
public function selectByKey($table, $primaryKeyName, $keyName, $keyValues)
{
$basicSql = "SELECT * FROM `{$table}`";
if (!empty($keyValues)) {
$where = " WHERE {$keyName} IN (?)";
$statement = $this->connection->executeQuery($basicSql . $where, [$keyValues], [\Doctrine\DBAL\Connection::PARAM_INT_ARRAY]);
} else {
$statement = $this->connection->executeQuery($basicSql);
}
return $statement->fetchAll(\PDO::FETCH_ASSOC);
}
/**
* @param string $className
*/
public function build($className)
{
if ($this->reflectionService->isClassAnnotatedWith($className, Table::class) === FALSE) {
throw new \InvalidArgumentException('The class "' . $className . '" is not annotated properly.', 1428331094);
}
/** @var Table $table */
$table = $this->reflectionService->getClassAnnotation($className, Table::class);
$query = $this->createTableDefinitionQueryForClassName($className);
$this->databaseConnection->executeQuery('DROP TABLE IF EXISTS ' . $table->name);
$this->databaseConnection->executeQuery($query);
}
/**
* @param int $enumId
* @return array
*/
public function get($enumId)
{
$sql = 'SELECT ei.* FROM ' . $this->options->getEnumItemTableName() . ' ei
INNER JOIN ' . $this->options->getEnumTableName() . ' e ON ei.enumeration_id = e.enumeration_id WHERE e.enumeration_id = ?';
$statement = $this->connection->executeQuery($sql, array($enumId));
$result = $statement->fetchAll();
$items = array();
foreach ($result as $row) {
$items[$row['value']] = $row;
}
return $items;
}
/**
* {@inheritdoc}
*/
public function loadTokenBySeries($series)
{
$sql = 'SELECT class, username, value, lastUsed' . ' FROM rememberme_token WHERE series=:series';
$paramValues = array('series' => $series);
$paramTypes = array('series' => \PDO::PARAM_STR);
$stmt = $this->conn->executeQuery($sql, $paramValues, $paramTypes);
$row = $stmt->fetch(\PDO::FETCH_ASSOC);
if ($row) {
return new PersistentToken($row['class'], $row['username'], $series, $row['value'], new \DateTime($row['lastUsed']));
}
throw new TokenNotFoundException('No token found.');
}
/**
* @inheritdoc
*/
public function install()
{
foreach ($this->getProfiles() as $profile) {
$serializedTree = json_encode($profile);
$sql = '
INSERT IGNORE INTO s_import_export_profile
(`type`, `name`, `tree`, `hidden`, `is_default`)
VALUES
(:type, :name, :tree, :hidden, :is_default)';
$params = ['type' => $profile->getAdapter(), 'name' => $profile->getName(), 'tree' => $serializedTree, 'hidden' => 0, 'is_default' => 1];
$this->connection->executeQuery($sql, $params);
}
}
/**
* @inheritdoc
* @throws \Exception
*/
public function update()
{
try {
$this->connection->executeQuery('ALTER TABLE s_import_export_profile ADD UNIQUE (`name`);');
$defaultProfileInstaller = new DefaultProfileInstaller($this->setupContext, $this->connection);
$defaultProfileInstaller->install();
} catch (DBALException $exception) {
if (!$this->isDuplicateNameError($exception)) {
throw $exception;
}
throw new DuplicateNameException($this->snippetManager->getNamespace('backend/swag_importexport/default_profiles')->get('update/duplicate_names'));
}
}
/**
* @param User $follower
* @param $leaders
*/
protected function follow($follower, $leaders)
{
$counter = 0;
$max = rand(1, 100);
shuffle($leaders);
/** @var User $leader */
foreach ($leaders as $leader) {
$this->connection->executeQuery('INSERT INTO connections (leader_id, follower_id, status) VALUES (' . $leader . ',' . $follower->getId() . ', "APPROVED")');
$counter++;
if ($counter > $max) {
return;
}
}
}
public function DeleteAuthToken($userID, $seriesID = null, $token = null)
{
$sql = 'DELETE FROM auth_tokens WHERE user_id = :user_id';
$params = ['user_id' => $userID];
if (isset($seriesID)) {
$sql .= ' AND series_id = :series_id';
$params['series_id'] = $seriesID;
}
if (isset($token)) {
$sql .= ' AND token = :token';
$params['token'] = $token;
}
$this->writer->executeQuery($sql, $params);
}
public function testFindAcls()
{
// $this->generateTestData();
// get some random test object identities from the database
$oids = array();
$stmt = $this->con->executeQuery("SELECT object_identifier, class_type FROM acl_object_identities o INNER JOIN acl_classes c ON c.id = o.class_id ORDER BY RAND() LIMIT 25");
foreach ($stmt->fetchAll() as $oid) {
$oids[] = new ObjectIdentity($oid['object_identifier'], $oid['class_type']);
}
$provider = $this->getProvider();
$start = microtime(true);
$provider->findAcls($oids);
$time = microtime(true) - $start;
echo "Total Time: " . $time . "s\n";
}
/**
* Run a query if execute query is true
* else simply add query to played queries stack
*
* @param Query $query
* @param bool $executeQueries
*/
private function runQuery(Query $query, $executeQueries = false)
{
$this->playedQueries[] = $query;
if ($executeQueries) {
$this->connection->executeQuery((string) $query);
}
}
private function populateDatabase(OutputInterface $output)
{
// Check if database exist
$stmt = $this->dbConnection->prepare('SHOW DATABASES LIKE ?');
$stmt->bindValue(1, $this->dbName);
$stmt->execute();
if ($stmt->rowCount() > 0) {
$output->writeln('<info>Waring, Database "' . $this->dbName . '" already exist!</info>');
$confirmation = $this->dialog->askConfirmation($output, '<question>Do you want to drop it? [N]</question> ', false);
// if user don't want to use it, reselect the database
if ($confirmation === false) {
$output->writeln('Okay, then please reselect database name');
$this->selectDatabase($output);
$this->populateDatabase($output);
return;
} else {
// Delete database
$output->writeln('<info>Delete database....</info>');
$this->dbConnection->executeQuery('DROP DATABASE ' . $this->dbName);
}
}
$output->writeln('<info>Create database "' . $this->dbName . '"...</info>');
$this->dbConnection->executeQuery('CREATE DATABASE ' . $this->dbName . ' COLLATE utf8_general_ci');
$output->writeln('<info>Select database "' . $this->dbName . '"...</info>');
$this->dbConnection->executeQuery('USE ' . $this->dbName);
$output->writeln('<info>Populating table....</info>');
$this->dbConnection->executeQuery($this->tableQuery);
$output->writeln('<info>Populating table SUCCESS!</info>');
}
/**
* @param $userId
* @param $score
*/
public function updateUserScore($userId, $score)
{
$sql = 'UPDATE users
SET users.score = ?
WHERE users.id = ?';
$this->connection->executeQuery($sql, array($score, $userId), array(\PDO::PARAM_INT, \PDO::PARAM_INT));
}
private function update(Model $model)
{
$idProperty = $model->GetIDProperty();
$properties = $model->GetProperties();
$modified = $model->GetModifiedProperties();
if (!empty($modified)) {
$cols = [];
$types = [];
$values = [];
foreach ($modified as $property => $dbMap) {
$cols[] = $dbMap['col'];
$types[] = $dbMap['type'];
$values[$dbMap['col']] = $dbMap['value'];
}
$sql = "UPDATE {$model->GetDbTable()} SET ";
foreach ($cols as $col) {
$sql .= "{$col} = :{$col},";
}
$sql = substr($sql, 0, -1);
// remove trailing comma
$sql .= " WHERE {$properties[$idProperty]['col']} = :{$properties[$idProperty]['col']}";
$types[] = $properties[$idProperty]['type'];
$values[$properties[$idProperty]['col']] = $properties[$idProperty]['value'];
$this->writer->executeQuery($sql, $values, $types);
}
}
private function createSchema(\Doctrine\DBAL\Connection $connection)
{
$schema = new \Doctrine\DBAL\Schema\Schema();
$teams = $schema->createTable('teams');
$teams->addColumn('id', 'integer', array('unsigned' => true, 'autoincrement' => true));
$teams->addColumn('uuid', 'binary', array('length' => 128));
$teams->addColumn('created_at', 'datetimetz');
$teams->addColumn('deleted', 'boolean', array('default' => false));
$teams->setPrimaryKey(array('id'));
$players = $schema->createTable('players');
$players->addColumn('id', 'integer', array('unsigned' => true, 'autoincrement' => true));
$players->addColumn('uuid', 'binary', array('length' => 128));
$players->addColumn('created_at', 'datetimetz');
$players->addColumn('deleted', 'boolean', array('default' => false));
$players->setPrimaryKey(array('id'));
$teamsPlayers = $schema->createTable('teams_players');
$teamsPlayers->addColumn('id', 'integer', array('unsigned' => true, 'autoincrement' => true));
$teamsPlayers->addColumn('uuid', 'binary', array('length' => 128));
$teamsPlayers->addColumn('created_at', 'datetimetz');
$teamsPlayers->addColumn('deleted', 'boolean', array('default' => false));
$teamsPlayers->addColumn('team_uuid', 'integer', array('unsigned' => true));
$teamsPlayers->addColumn('player_uuid', 'integer', array('unsigned' => true));
$teamsPlayers->setPrimaryKey(array('id'));
foreach ($schema->toSql($connection->getDatabasePlatform()) as $query) {
$connection->executeQuery($query);
}
}
/**
* Execute a query.
*
* If a query is one of DESCRIBE, SELECT, or SHOW
* then use <code>Connection::executeQuery</code>
* otherwise pass it off to <code>Connection::executeUpdate</code>
*
* @param string $sql sql query string
* @param int $limit limit the number of results
* @param bool $useCache cache the query
* @param int $cacheTime how long to cache the query for (in seconds)
* @return object QueryFactoryResult
*/
public function Execute($sql, $limit = null, $useCache = false, $cacheTime = 0)
{
$sql = trim($sql);
$commandType = strtolower(substr($sql, 0, 3));
if (!in_array($commandType, array('des', 'sel', 'sho'))) {
return $this->conn->executeUpdate($sql);
}
if ($limit) {
$sql .= ' LIMIT ' . (int) $limit;
}
$qcp = null;
$resultClass = __NAMESPACE__ . '\\QueryFactoryResult';
if ($this->hasResultCache) {
$qcp = new QueryCacheProfile($cacheTime, self::CACHE_KEY);
$resultClass = __NAMESPACE__ . '\\CachedQueryFactoryResult';
}
if ($useCache && $this->hasResultCache) {
// @todo ArrayCache shouldn't count as a real cache for $useCache
$stmt = $this->conn->executeCacheQuery($sql, array(), array(), $qcp);
} else {
$stmt = $this->conn->executeQuery($sql, array(), array(), $qcp);
}
$obj = new $resultClass($stmt);
$obj->MoveNext();
return $obj;
}
private function executeRegisteredSql($dryRun = false, $timeAllQueries = false)
{
if (!$dryRun) {
if (!empty($this->sql)) {
foreach ($this->sql as $key => $query) {
$queryStart = microtime(true);
if (!isset($this->params[$key])) {
$this->outputWriter->write(' <comment>-></comment> ' . $query);
$this->connection->exec($query);
} else {
$this->outputWriter->write(sprintf(' <comment>-</comment> %s (with parameters)', $query));
$this->connection->executeQuery($query, $this->params[$key], $this->types[$key]);
}
$this->outputQueryTime($queryStart, $timeAllQueries);
}
} else {
$this->outputWriter->write(sprintf('<error>Migration %s was executed but did not result in any SQL statements.</error>', $this->version));
}
} else {
foreach ($this->sql as $query) {
$this->outputWriter->write(' <comment>-></comment> ' . $query);
}
}
$this->resetRegisteredSql();
}
public function findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
{
$where = array();
$params = array();
foreach ($criteria as $key => $value) {
$where[] = sprintf('%s = :%s', $key, $key);
$params[$key] = $value;
}
$sqlParts = array();
$sqlParts[] = sprintf('SELECT * FROM %s WHERE %s', $this->getTableName(), implode(' AND ', $where));
if ($orderBy !== null) {
$orderBySqlParts = array();
foreach ($orderBy as $fieldName => $orientation) {
$orderBySqlParts[] = sprintf('%s %s', $fieldName, $orientation);
}
$sqlParts[] = 'ORDER BY ' . implode(', ', $orderBySqlParts);
}
if ($limit !== null) {
$sqlParts[] = sprintf('LIMIT %s', $limit);
}
if ($offset !== null) {
$sqlParts[] = sprintf('OFFSET %s', $offset);
}
$sql = implode(' ', $sqlParts);
return $this->connection->executeQuery($sql, $params)->fetchAll();
}
