Executes an SQL statement, returning a result set as a Statement object.
public query ( ) : Doctrine\DBAL\Driver\Statement | ||
return | Doctrine\DBAL\Driver\Statement |
/** * @return array|null */ public function read() { if (is_null($this->stmt)) { $this->stmt = $this->connection->query("SELECT * FROM products"); } return $this->stmt->fetch(); }
/** * {@inheritdoc} */ public function query($query, $resultsetType = Resultset::TYPE_ARRAY) { // This error may happen with the libmysql instead of mysqlnd and using set statement (set @test=1) // : "Attempt to read a row while there is no result set associated with the statement" try { /** * @var \Doctrine\DBAL\Driver\Mysqli\MysqliStatement */ $r = $this->dbal->query($query); $results = new Resultset($resultsetType); if ($r === false) { throw new Exception\InvalidArgumentException("Query cannot be executed [{$query}]."); } else { if ($r->columnCount() > 0) { while ($row = $r->fetch()) { $results->append((array) $row); } } } } catch (\Exception $e) { $msg = "Doctrine\\Dbal2 adapter query error: {$e->getMessage()} [{$query}]"; throw new Exception\InvalidArgumentException($msg); } return $results; }
public function createTable($name) { // remove table $pdo = $this->pdo->query('DROP TABLE IF EXISTS `' . $name . '`;'); $pdo->execute(); // create table $newSchema = new \Doctrine\DBAL\Schema\Schema(); $newTable = $newSchema->createTable($name); $newTable->addColumn('container_id', 'integer'); $newTable->addColumn('context_id', 'integer'); $newTable->addColumn('module_id', 'integer'); $newTable->addColumn('resource_id', 'integer')->setNotnull(false); $newTable->addColumn('by_resource_id', 'integer')->setNotnull(false); $newTable->addColumn('sequence', 'integer')->setNotnull(false); $newTable->addColumn('deleted', 'datetime')->setNotnull(false); $newTable->addColumn('display', 'boolean')->setNotnull(true); $newTable->addColumn('expire_temporary_date', 'datetime')->setNotnull(false); $newTable->addColumn('culture', 'string')->setLength(11); foreach ($this->eavColumns->getColumns() as $column) { if ($this->getTypeToColumn($column['column'])) { $newTable->addColumn($column['identifier'], $this->getTypeToColumn($column['column']))->setNotnull(false); $columnsToFill[] = $column['identifier']; } } $newTable->addForeignKeyConstraint($this->objectManager->getClassMetadata('BigfishEavBundle:Container')->getTablename(), array('container_id'), array('container_id'), array('onDelete' => 'CASCADE')); $newTable->addForeignKeyConstraint($this->objectManager->getClassMetadata('BigfishEavBundle:Module')->getTablename(), array('module_id'), array('module_id'), array('onDelete' => 'CASCADE')); $newTable->addForeignKeyConstraint($this->objectManager->getClassMetadata('BigfishContextBundle:Context')->getTablename(), array('context_id'), array('context_id'), array('onDelete' => 'CASCADE')); $newTable->addForeignKeyConstraint($this->objectManager->getClassMetadata('BigfishResourceBundle:Resource')->getTablename(), array('resource_id'), array('resource_id'), array('onDelete' => 'CASCADE')); $newTable->addForeignKeyConstraint($this->objectManager->getClassMetadata('BigfishResourceBundle:Resource')->getTablename(), array('by_resource_id'), array('resource_id'), array('onDelete' => 'CASCADE')); foreach ($newSchema->toSql($this->objectManager->getConnection()->getDatabasePlatform()) as $l) { $pdo = $this->pdo->prepare($l); $pdo->execute(); } }
/** * @param $sql * @return \Doctrine\DBAL\Driver\Statement * @throws \Doctrine\DBAL\DBALException */ public static function runQuery($sql) { if (null === static::$dblaConnInstance) { static::$dblaConnInstance = DriverManager::getConnection(static::getParamsConnection(), new Configuration()); static::$dblaConnInstance->getConfiguration()->setSQLLogger(self::logger()); } return static::$dblaConnInstance->query($sql); }
/** * @return bool */ public function createIndexes() { try { $this->connection->query($this->getQuery()); } catch (DBALException $exception) { return false; } return true; }
/** * Actual health check logic. * * @param HealthBuilder $builder * * @throws \Exception any Exception that should create a Status::DOWN * system status. */ protected function doHealthCheck(HealthBuilder $builder) { try { $this->connection->query('SELECT 1=1'); } catch (DBALException $e) { $builder->down($e); return; } $builder->up(); }
/** * @return Job[] */ public function getAll() { $this->conn->connect(); $stmt = $this->conn->query("select * from {$this->table_name}"); $stmt->setFetchMode(\PDO::FETCH_CLASS, '\\ebussola\\job\\job\\Job'); $stmt->execute(); $jobs = $stmt->fetchAll(); $this->conn->close(); return $jobs; }
/** * @return int */ private function getResponsiveTemplateId() { $statement = $this->conn->query('SELECT id FROM s_core_templates WHERE template LIKE "Responsive"'); $statement->execute(); $templateId = $statement->fetchColumn(0); if (!$templateId) { throw new \RuntimeException("Could not get id for default template"); } return (int) $templateId; }
public function loadUserTableWithOneItem() { if ($this->isDbPrepared) { return; } $this->connection->query('CREATE TABLE user (id INTEGER NOT NULL, name string, PRIMARY KEY(id))'); $user = new User('John'); $this->entityManager->persist($user); $this->entityManager->flush(); $this->isDbPrepared = TRUE; }
public function run() { $success = true; foreach ($this->queries as $sql) { if (strlen($sql) > 0) { if (!$this->db->query($sql)) { $success = false; } } } return $success; }
/** * Executes a command. * * @param string $hash * * @throws \InvalidArgumentException */ public function execCommand($hash) { if (null === $this->commands) { $this->compileCommands(); } foreach ($this->commands as $commands) { if (isset($commands[$hash])) { $this->connection->query($commands[$hash]); return; } } throw new \InvalidArgumentException(sprintf('Invalid hash: %s', $hash)); }
private function emptyOrderedToolRoleTable() { $this->log('emptying claro_ordered_tool_role table...'); $this->connection->query('SET FOREIGN_KEY_CHECKS=0'); $this->connection->query('TRUNCATE TABLE claro_ordered_tool_role'); $this->connection->query('SET FOREIGN_KEY_CHECKS=1'); }
protected function execute() { if ($this->currentStatement === null) { $this->currentStatement = $this->db->query($this->buildQuery()); $this->rowCount = $this->currentStatement->rowCount(); } return $this->currentStatement; }
/** * Runs the changes to the schema */ public function commit() { $this->connection->beginTransaction(); foreach ($this->getChanges() as $sql) { $this->connection->query($sql); } $this->connection->commit(); }
/** * {@inheritdoc} */ public function getDatabase(\Doctrine\DBAL\Connection $conn) { $params = $conn->getParams(); if (isset($params['dbname'])) { return $params['dbname']; } return $conn->query('SELECT DB_NAME()')->fetchColumn(); }
/** * @param $sql * @return \Doctrine\DBAL\Driver\Statement * @throws ErrorException */ public function getResult($sql) { try { return $this->connection->query($sql); } catch (\Doctrine\DBAL\DBALException $e) { throw new \Parm\Exception\ErrorException("DatabaseProcessor SQL Error. MySQL Query Failed: " . htmlentities($sql) . '. Reason given ' . $e); } }
/** * Runs the update. */ public function run() { $statement = $this->connection->query("SELECT id, framework FROM tl_layout WHERE framework!=''"); while (false !== ($layout = $statement->fetch(\PDO::FETCH_OBJ))) { $framework = ''; $tmp = deserialize($layout->framework); if (!empty($tmp) && is_array($tmp)) { if (false !== ($key = array_search('layout.css', $tmp))) { array_insert($tmp, $key + 1, 'responsive.css'); } $framework = serialize(array_values(array_unique($tmp))); } $stmt = $this->connection->prepare('UPDATE tl_layout SET framework=:framework WHERE id=:id'); $stmt->execute([':framework' => $framework, ':id' => $layout->id]); } // Add the "viewport" field (triggers the version 3.3 update) $this->connection->query("ALTER TABLE `tl_layout` ADD `viewport` varchar(255) NOT NULL default ''"); }
/** * Checks if the installation is complete. * * @return bool */ private function isCompleteInstallation() { try { $this->connection->query('SELECT COUNT(*) FROM tl_page'); } catch (\Exception $e) { return false; } return true; }
/** * Run the create/alter query. * * @param string $tableName * @param string $query * * @return \Doctrine\DBAL\Driver\Statement|null */ protected function runQuery($tableName, $query) { try { return $this->connection->query($query); } catch (DBALException $e) { $this->loggerSystem->critical($e->getMessage(), ['event' => 'exception', 'exception' => $e]); $this->loggerFlash->error(Trans::__('An error occured while updating `%TABLE%`. The error is %ERROR%', ['%TABLE%' => $tableName, '%ERROR%' => $e->getMessage()])); } }
public function getUnusedWorkflowIds() { $sql = 'SELECT w.workflow_id FROM ' . $this->options->workflowTable() . ' w ' . 'WHERE w.workflow_id NOT IN ( SELECT DISTINCT e.workflow_id FROM ' . $this->options->executionTable() . ') ' . ' AND w.workflow_outdated = 1'; $stmt = $this->conn->query(); $workflowIds = array(); while ($workflowId = $stmt->fetchColumn()) { $workflowIds[] = $workflowId; } return $workflowIds; }
public function getFormChoices() { $this->transaction->requestTransaction(); $stmt = $this->conn->query('SELECT `id`, `name` FROM `' . CoreTables::PROJECT_TBL . '` WHERE `archived` = 1 ORDER BY `name`'); $result = array(); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $result[$row['id']] = $row['name']; } $stmt->closeCursor(); return $result; }
/** * Create Schema * * @return DBAL */ public function createSchema() { $schema = new \Doctrine\DBAL\Schema\Schema(); $table = $schema->createTable($this->tableName); $table->addColumn("version", "string", array("length" => 255)); $queries = $schema->toSql($this->connection->getDatabasePlatform()); foreach ($queries as $sql) { $this->connection->query($sql); } return $this; }
private function restoreBadgeCollections() { if ($this->connection->getSchemaManager()->tablesExist(array('claro_badge_collection_badges'))) { $this->log('Restoring badge collections...'); $rowBadgeCollections = $this->connection->query('SELECT * FROM claro_badge_collection_badges'); foreach ($rowBadgeCollections as $rowBadgeCollection) { /** @var \Icap\BadgeBundle\Entity\BadgeCollection $badgeCollection */ $badgeCollection = $this->entityManager->getRepository('IcapBadgeBundle:BadgeCollection')->find($rowBadgeCollection['badgecollection_id']); if (null !== $badgeCollection) { /** @var \Icap\BadgeBundle\Repository\UserBadgeRepository $userBadgeRepository */ $userBadgeRepository = $this->entityManager->getRepository('IcapBadgeBundle:UserBadge'); /** @var \Icap\BadgeBundle\Entity\UserBadge $userBadge */ $userBadge = $userBadgeRepository->findOneBy(['user' => $badgeCollection->getUser(), 'badge' => $this->entityManager->getReference('IcapBadgeBundle:Badge', $rowBadgeCollection['badge_id'])]); if (null !== $userBadge) { $this->connection->insert('claro_badge_collection_user_badges', ['badgecollection_id' => $rowBadgeCollection['badgecollection_id'], 'userbadge_id' => $userBadge->getId()]); } } } $this->connection->getSchemaManager()->dropTable('claro_badge_collection_badges'); } }
/** * Checks if there is an admin user. * * @return bool True if there is an admin user */ public function hasAdminUser() { try { $statement = $this->connection->query('SELECT COUNT(*) AS count FROM tl_user WHERE admin=1'); if ($statement->fetch(\PDO::FETCH_OBJ)->count > 0) { return true; } } catch (DBALException $e) { // ignore } return false; }
public function postUpdate() { $em = $this->container->get('doctrine.orm.entity_manager'); $process = false; if (in_array('claro_forum_subject_temp', $this->conn->getSchemaManager()->listTableNames())) { $columns = $this->conn->getSchemaManager()->listTableColumns('claro_forum_subject_temp'); foreach ($columns as $column) { if ($column->getName() === 'forum_id') { $process = true; break; } } } if ($process) { $this->log('restoring the subjects...'); $forums = $em->getRepository('ClarolineForumBundle:Forum')->findAll(); $sql = 'SELECT * FROM claro_forum_subject_temp WHERE forum_id = :forumId'; $stmt = $this->conn->prepare($sql); foreach ($forums as $forum) { $category = new Category(); $category->setName($forum->getResourceNode()->getName()); $category->setForum($forum); $em->persist($category); $em->flush(); $stmt->bindValue('forumId', $forum->getId()); $stmt->execute(); foreach ($stmt->fetchAll() as $rowsSubject) { $this->conn->query("INSERT INTO claro_forum_subject VALUES (\n {$rowsSubject['id']},\n {$category->getId()},\n {$rowsSubject['user_id']},\n {$this->conn->quote($rowsSubject['title'])},\n '{$rowsSubject['created']}',\n '{$rowsSubject['updated']}',\n false\n )"); } } $this->log('restoring the messages...'); $this->conn->query('INSERT IGNORE INTO claro_forum_message SELECT * FROM claro_forum_message_temp'); $this->conn->query('DROP TABLE claro_forum_message_temp'); $this->conn->query('DROP TABLE claro_forum_subject_temp'); $this->conn->query('DROP TABLE claro_forum_options'); } else { $this->log('categories already added'); } $widget = $em->getRepository('ClarolineCoreBundle:Widget\\Widget')->findBy(array('name' => 'claroline_forum_widget')); if (!$widget) { $this->log('adding the forum widget...'); $plugin = $em->getRepository('ClarolineCoreBundle:Plugin')->findOneBy(array('vendorName' => 'Claroline', 'bundleName' => 'ForumBundle')); $widget = new Widget(); $widget->setName('claroline_forum_widget'); $widget->setDisplayableInDesktop(true); $widget->setDisplayableInWorkspace(true); $widget->setConfigurable(false); $widget->setExportable(false); $widget->setIcon('none'); $widget->setPlugin($plugin); $em->persist($widget); $plugin->setHasOptions(true); $em->persist($widget); $em->flush(); } else { $this->log('forum widget already added'); } }
/** * Creates the SQLite database */ public function setUp() { $c = new ConnectionManager(); $c->registerInstance(); ConnectionManager::instance()->registerConnection('default', ['driver' => 'pdo_sqlite', 'path' => __DIR__ . '/../tests.db']); // Clear the database and create our test tables: $this->conn = ConnectionManager::instance()->connection('default'); $this->conn->query('DROP TABLE IF EXISTS users'); $this->conn->query('DROP TABLE IF EXISTS orders'); $this->conn->query(' CREATE TABLE `users` ( `id` INTEGER PRIMARY KEY, `name` varchar(32) NOT NULL ); '); $this->conn->query(' CREATE TABLE `orders` ( `id` INTEGER PRIMARY KEY, `user_id` INTEGER, `total` FLOAT ); '); }
public function restoreUserIdForAbstractWidget() { $this->log('Restoring widgets...'); $totalWidgetProcessed = 0; $nbWidgetProcessed = 0; $rowAbstractWidgets = $this->connection->query('SELECT aw.id, aw.user_id FROM icap__portfolio_abstract_widget aw'); foreach ($rowAbstractWidgets as $rowAbstractWidget) { $this->connection->query(sprintf('UPDATE icap__portfolio_abstract_widget aw SET aw.user_id = ( SELECT p.user_id FROM icap__portfolio p WHERE p.id = %d ) WHERE aw.id = %d', $rowAbstractWidget['user_id'], $rowAbstractWidget['id'])); ++$nbWidgetProcessed; if ($nbWidgetProcessed >= 10) { $totalWidgetProcessed += $nbWidgetProcessed; $nbWidgetProcessed = 0; $this->log(' processing widget...'); } } $this->log(sprintf(' %d widget processed', $totalWidgetProcessed + $nbWidgetProcessed)); }
protected function purgeTables(array $tablesList) { $this->connection->query('SET FOREIGN_KEY_CHECKS=0;'); foreach ($tablesList as $table) { $query = $this->dbPlatform->getTruncateTableSql($table); try { $this->output->writeln('Purging data from ' . $table); $this->connection->executeUpdate($query); } catch (\Exception $e) { $this->output->writeln('Error purging data from \'' . $table . '\'. Error: ' . $e->getMessage()); $this->connection->query('SET FOREIGN_KEY_CHECKS=1;'); throw $e; } } $this->connection->query('SET FOREIGN_KEY_CHECKS=1;'); }
/** * Generates SQL for installation. * * @param object $originalData * * @return array|bool Array containing the flash message data on a failure, boolean true on success */ public function installSchema() { $sm = $this->db->getSchemaManager(); try { //check to see if the table already exist $tables = $sm->listTableNames(); } catch (\Exception $e) { $this->db->close(); throw $e; } $this->platform = $sm->getDatabasePlatform(); $backupPrefix = !empty($this->dbParams['backup_prefix']) ? $this->dbParams['backup_prefix'] : 'bak_'; $metadatas = $this->em->getMetadataFactory()->getAllMetadata(); if (empty($metadatas)) { $this->db->close(); return false; } $schemaTool = new SchemaTool($this->em); $installSchema = $schemaTool->getSchemaFromMetadata($metadatas); $mauticTables = []; foreach ($installSchema->getTables() as $m) { $tableName = $m->getName(); $mauticTables[$tableName] = $this->generateBackupName($this->dbParams['table_prefix'], $backupPrefix, $tableName); } $sql = ['SET foreign_key_checks = 0;']; if ($this->dbParams['backup_tables']) { $sql = array_merge($sql, $this->backupExistingSchema($tables, $mauticTables, $backupPrefix)); } else { $sql = array_merge($sql, $this->dropExistingSchema($tables, $mauticTables)); } $sql = array_merge($sql, $installSchema->toSql($this->platform)); // Execute drop queries if (!empty($sql)) { foreach ($sql as $q) { try { $this->db->query($q); } catch (\Exception $exception) { $this->db->close(); throw $exception; } } } $this->db->close(); return true; }
public function prepareMailSummary() { $stmt = $this->conn->query('SELECT a.`id`, a.`name`, t.`locale`, p.`modules`, s.`name` AS `statusName` FROM `' . CoreTables::AREA_TBL . '` a ' . 'INNER JOIN `' . CoreTables::AREA_STATUS_TBL . '` s ON s.`id` = a.`statusId` ' . 'INNER JOIN `' . CoreTables::TERRITORY_TBL . '` t ON t.`id` = a.`territoryId` ' . 'INNER JOIN `' . CoreTables::PROJECT_TBL . '` p ON p.`id` = a.`projectId` ' . 'WHERE p.`archived` = 0'); $areaIds = []; $areas = []; while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { if (strpos($row['modules'], 'edk') !== false) { $areaIds[] = $row['id']; $areas[$row['id']] = ['id' => $row['id'], 'locale' => $row['locale'], 'name' => $row['name'], 'statusName' => $row['statusName'], 'messages' => [0 => 0, 1 => 0, 2 => 0, 3 => 0], 'emails' => [], 'newMessages' => 0, 'participants' => 0]; } } $stmt->closeCursor(); if (sizeof($areaIds) > 0) { $stmt = $this->conn->query('SELECT `areaId`, `status`, COUNT(`id`) AS `total` FROM `' . EdkTables::MESSAGE_TBL . '` WHERE `areaId` IN (' . implode(',', $areaIds) . ') GROUP BY `areaId`, `status`'); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $areas[$row['areaId']]['messages'][$row['status']] = $row['total']; } $stmt->closeCursor(); $stmt = $this->conn->prepare('SELECT `areaId`, COUNT(`id`) AS `total` FROM `' . EdkTables::MESSAGE_TBL . '` WHERE `areaId` IN (' . implode(',', $areaIds) . ') AND `createdAt` > :time GROUP BY `areaId`'); $stmt->bindValue(':time', time() - 86400); $stmt->execute(); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $areas[$row['areaId']]['newMessages'] = $row['total']; } $stmt->closeCursor(); $stmt = $this->conn->prepare('SELECT `areaId`, SUM(`participantNum`) AS `total` FROM `' . EdkTables::REGISTRATION_SETTINGS_TBL . '` WHERE `areaId` IN (' . implode(',', $areaIds) . ') GROUP BY `areaId`'); $stmt->bindValue(':time', time() - 86400); $stmt->execute(); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $areas[$row['areaId']]['participants'] = $row['total']; } $stmt->closeCursor(); $stmt = $this->conn->prepare('SELECT u.`email`, m.`areaId` FROM `' . CoreTables::USER_TBL . '` u INNER JOIN `' . CoreTables::AREA_MEMBER_TBL . '` m ON m.`userId` = u.`id` WHERE m.`areaId` IN (' . implode(',', $areaIds) . ') '); $stmt->bindValue(':time', time() - 86400); $stmt->execute(); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $areas[$row['areaId']]['emails'][] = $row['email']; } $stmt->closeCursor(); } return $areas; }