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. |
Результат | 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(); }