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;
}
