/** * @test */ public function it_saves_and_reads() { $schema = new Schema(); SnapshotStoreSchema::create($schema, 'foo_snapshot'); foreach ($schema->toSql($this->connection->getDatabasePlatform()) as $sql) { $this->connection->executeQuery($sql); } $adapter = new DoctrineSnapshotAdapter($this->connection); $aggregateType = AggregateType::fromString('foo'); $aggregateRoot = new \stdClass(); $aggregateRoot->foo = 'bar'; $time = microtime(true); if (false === strpos($time, '.')) { $time .= '.0000'; } $now = \DateTimeImmutable::createFromFormat('U.u', $time); $snapshot = new Snapshot($aggregateType, 'id', $aggregateRoot, 1, $now); $adapter->save($snapshot); $snapshot = new Snapshot($aggregateType, 'id', $aggregateRoot, 2, $now); $adapter->save($snapshot); $this->assertNull($adapter->get($aggregateType, 'invalid')); $readSnapshot = $adapter->get($aggregateType, 'id'); $this->assertEquals($snapshot, $readSnapshot); $statement = $this->connection->prepare('SELECT * FROM foo_snapshot'); $statement->execute(); $snapshots = $statement->fetchAll(); $this->assertCount(1, $snapshots); }
public function restorePortfolioTitle() { $totalPortfolioProcessed = 0; $nbPortfolioProcessed = 0; if ($this->connection->getSchemaManager()->tablesExist(array('icap__portfolio_widget_title'))) { $this->log('Restoring portfolio titles...'); $rowPortfolioTitles = $this->connection->query('SELECT * FROM icap__portfolio_widget_title'); $sql = 'SELECT aw.id, aw.user_id FROM icap__portfolio_abstract_widget aw WHERE id = :id'; $stmt = $this->connection->prepare($sql); foreach ($rowPortfolioTitles as $rowPortfolioTitle) { $stmt->bindValue('id', $rowPortfolioTitle['id']); $stmt->execute(); foreach ($stmt->fetchAll() as $rowAbstractWidget) { $this->connection->update('icap__portfolio', ['title' => $rowPortfolioTitle['title'], 'slug' => $rowPortfolioTitle['slug']], ['id' => $rowAbstractWidget['user_id']]); } $this->connection->delete('icap__portfolio_abstract_widget', ['id' => $rowPortfolioTitle['id']]); ++$nbPortfolioProcessed; if ($nbPortfolioProcessed >= 10) { $totalPortfolioProcessed += $nbPortfolioProcessed; $nbPortfolioProcessed = 0; $this->log(' processing portfolio...'); } } $this->log(sprintf(' %d portfolio processed', $totalPortfolioProcessed + $nbPortfolioProcessed)); $this->connection->delete('icap__portfolio_widget_type', ['name' => 'title']); $this->connection->getSchemaManager()->dropTable('icap__portfolio_widget_title'); } }
private function applyChanges($areas, $rules, $reverseRules) { $stmt = $this->conn->prepare('UPDATE `' . CoreTables::AREA_TBL . '` SET `statusId` = :statusId, `lastUpdatedAt` = :lastUpdated WHERE `id` = :id'); $count = 0; foreach ($areas as $area) { foreach ($rules as $rule) { if ($this->isMatching($area, $rule)) { $stmt->bindValue(':id', $area['id']); $stmt->bindValue(':statusId', $rule['newStatusId']); $stmt->bindValue(':lastUpdated', time()); $stmt->execute(); $count++; continue 2; } } foreach ($reverseRules as $rule) { if ($this->isNotMatching($area, $rule)) { $stmt->bindValue(':id', $area['id']); $stmt->bindValue(':statusId', $rule['prevStatusId']); $stmt->bindValue(':lastUpdated', time()); $stmt->execute(); $count++; continue 2; } } } return $count; }
/** * @param $todoId * @return null|\stdClass containing userData */ public function findUserOfTodo($todoId) { $stmt = $this->connection->prepare(sprintf("SELECT u.* FROM %s as u JOIN %s as t ON u.id = t.assignee_id where t.id = :todo_id", Table::USER, Table::TODO)); $stmt->bindValue('todo_id', $todoId); $stmt->execute(); return $stmt->fetch(); }
/** * @param Backlog[] $backlogs */ private function writeBacklog(array $backlogs) { $statement = $this->connection->prepare("\n INSERT IGNORE INTO s_es_backlog (`event`, `payload`, `time`)\n VALUES (:event, :payload, :time);\n "); foreach ($backlogs as $backlog) { $statement->execute([':event' => $backlog->getEvent(), ':payload' => json_encode($backlog->getPayload()), ':time' => $backlog->getTime()->format('Y-m-d H:i:s')]); } }
protected function buildOptions(ReportParameter $parameter) { $options = array(); if ($parameter->getType() == ReportParameter::TYPE_QUERY) { $stmt = $this->connection->prepare($parameter->getChoices()); $stmt->execute(); $results = $stmt->fetchAll(); $values = array(null => ''); $keySet = array_keys(current($results)); $key = current(array_slice($keySet, 0, 1)); $value = current(array_slice($keySet, 1, 1)); foreach ($results as $result) { $values[$result[$value]] = $result[$key]; } $options = array_merge($options, array('choices' => $values)); $parameter->setType(ChoiceType::class); } $normalizer = new \Symfony\Component\Serializer\Normalizer\GetSetMethodNormalizer(); $normalizer->setIgnoredAttributes(array('id', 'name', 'type', 'created', 'modified', 'active')); $serializer = new \Symfony\Component\Serializer\Serializer(array($normalizer)); $options = array_merge($serializer->normalize($parameter), $options); foreach ($options as $key => $value) { if (is_null($value)) { unset($options[$key]); } } $this->handleConstraints($options); return $options; }
/** * @param $userId * @return null|\stdClass containing userData */ public function findById($userId) { $stmt = $this->connection->prepare(sprintf("SELECT * FROM %s where id = :user_id", Table::USER)); $stmt->bindValue('user_id', $userId); $stmt->execute(); return $stmt->fetch(); }
/** * @param string $todoId * @return \stdClass of todoData */ public function findById($todoId) { $stmt = $this->connection->prepare(sprintf("SELECT * FROM %s where id = :todo_id", Table::TODO)); $stmt->bindValue('todo_id', $todoId); $stmt->execute(); return $stmt->fetch(); }
/** * Execute */ public function execute(array $criteria = null) { $stmt = $this->connection->prepare($this->buildQuery()); $stmt->execute(array('mds' => $criteria['start_date']->format('m/d/Y'), 'mde' => $criteria['end_date']->format('m/d/Y'))); $results = $stmt->fetchAll(); $stmt->closeCursor(); return $results; }
/** * {@inheritDoc} */ public function getStreamIds() { $statement = $this->connection->prepare('SELECT DISTINCT uuid FROM ' . config('broadway.event-store.table')); $statement->execute(); return array_map(function ($row) { return $row['uuid']; }, $statement->fetchAll()); }
/** * @return ReadModelInterface[] */ public function findAll() { $statement = $this->connection->prepare(sprintf('SELECT serialized FROM %s WHERE class = :class', static::TABLE)); $statement->execute(['class' => $this->class]); return array_map(function ($row) { return $this->serializer->deserialize(json_decode($row['serialized'], true)); }, $statement->fetchAll(\PDO::FETCH_ASSOC)); }
/** * Execute */ public function execute(array $criteria = null) { $stmt = $this->connection->prepare($this->buildQuery()); $stmt->execute(array('first_id' => $criteria['first_id'], 'last_id' => $criteria['last_id'])); $results = $stmt->fetchAll(); $stmt->closeCursor(); return $results; }
public function __construct(Connection $con) { $this->con = $con; $this->platform = $con->getDatabasePlatform(); $this->phpType = Type::getType(PhpTypeType::NAME); $this->fStmt = $con->prepare(self::INSERT_FUNC_SQL); $this->mStmt = $con->prepare(self::INSERT_METHOD_SQL); }
public function getCategoryPaintings($catId) { $sql = 'SELECT * FROM pinturas where categorias_pintura_id = :catId'; $stmt = $this->db->prepare($sql); $stmt->bindValue('catId', $catId); $stmt->execute(); return $stmt->fetchAll(); }
public function getCategoryPerformances($catId) { $sql = 'SELECT * FROM teatros where categorias_teatro_id = :catId'; $stmt = $this->db->prepare($sql); $stmt->bindValue('catId', $catId); $stmt->execute(); return $stmt->fetchAll(); }
public function dropTable() { $schema = $this->getTableSchema(); $queries = $schema->toDropSql($this->connection->getDatabasePlatform()); foreach ($queries as $query) { $this->connection->prepare($query)->execute(); } }
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'); } }
/** * totals the month revenue of buggl * @param Integer $month month number * @param Integer $year year * * @return Decimal */ public function countMonthlyBugglRevenue($month, $year) { // $sql = "SELECT sum(info.buggl_fee) FROM purchase_info info WHERE MONTH(info.date_of_transaction) = $month AND YEAR(info.date_of_transaction) = $year"; $sql = "SELECT sum(info.buggl_fee) FROM paypal_purchase_info info WHERE MONTH(info.date_of_transaction) = {$month} AND YEAR(info.date_of_transaction) = {$year}"; $stmt = $this->connection->prepare($sql); $stmt->execute(); $count = $stmt->fetch(PDO::FETCH_NUM); return is_null($count[0]) ? 0 : $count[0]; }
public function __construct(Connection $con) { $this->con = $con; $this->platform = $con->getDatabasePlatform(); $this->phpType = Type::getType(PhpTypeType::NAME); $this->propertyIdRef = new \ReflectionProperty('Scrutinizer\\PhpAnalyzer\\Model\\Property', 'id'); $this->propertyIdRef->setAccessible(true); $this->propertyStmt = $con->prepare(self::INSERT_PROPERTY_SQL); $this->cPropertyStmt = $con->prepare(self::INSERT_CLASS_PROPERTY_SQL); }
public function push(array $list) { $this->create(); $this->connection->beginTransaction(); $statement = $this->connection->prepare("\n INSERT INTO `{$this->table}`(`path`)\n VALUES (:path)\n "); foreach ($list as $path) { $statement->execute(compact('path')); } $this->connection->commit(); }
/** * @param TodoWasMarkedAsDone $event * @throws \RuntimeException if data of the the assigned user can not be found */ public function onTodoWasMarkedAsDone(TodoWasMarkedAsDone $event) { $user = $this->userFinder->findUserOfTodo($event->todoId()->toString()); if (!$user) { throw new \RuntimeException(sprintf("Data of the assigned user of the todo %s cannot be found", $event->todoId()->toString())); } $stmt = $this->connection->prepare(sprintf('UPDATE %s SET open_todos = open_todos - 1, done_todos = done_todos + 1 WHERE id = :assignee_id', Table::USER)); $stmt->bindValue('assignee_id', $user->id); $stmt->execute(); }
/** * Writes the current session data to the database. * * @param FilterResponseEvent $event The event object */ public function onKernelResponse(FilterResponseEvent $event) { if (!$this->hasUser() || !$this->isContaoMasterRequest($event)) { return; } $user = $this->getUserObject(); if (!$user instanceof User) { return; } $this->connection->prepare('UPDATE ' . $user->getTable() . ' SET session=? WHERE id=?')->execute([serialize($this->getSessionBag()->all()), $user->id]); }
/** * @return array of message handlers data */ public function findAll() { $handlers = []; $stmt = $this->connection->prepare('SELECT * FROM ' . Tables::MESSAGE_HANDLER); $stmt->execute(); foreach ($stmt as $row) { $this->fromDatabase($row); array_push($handlers, $row); } return $handlers; }
public function __construct(Connection $con) { $this->con = $con; $this->platform = $con->getDatabasePlatform(); $this->phpType = Type::getType(PhpTypeType::NAME); $this->constantIdRef = new \ReflectionProperty('Scrutinizer\\PhpAnalyzer\\Model\\Constant', 'id'); $this->constantIdRef->setAccessible(true); $this->constantStmt = $con->prepare(self::INSERT_CONSTANT_SQL); $this->cConstantStmt = $con->prepare(self::INSERT_CLASS_CONSTANT_SQL); $this->iConstantStmt = $con->prepare(self::INSERT_INTERFACE_CONSTANT_SQL); }
/** * @return \Doctrine\DBAL\Driver\Statement * @throws \Doctrine\DBAL\DBALException */ private function getRouteStatement() { $sql = ' SELECT subshopID as shopId, path, org_path as orgPath, main FROM s_core_rewrite_urls WHERE path LIKE :pathInfo ORDER BY main DESC, subshopID = :shopId DESC LIMIT 1 '; $stmt = $this->connection->prepare($sql); return $stmt; }
/** * @param $membershipEntity Entity whose members we want to view * @return array */ public function findMembers(MembershipEntityInterface $membershipEntity) { $stmt = $this->conn->prepare('SELECT i.`id`, i.`name`, i.`avatar`, i.`lastVisit`, p.`location`, m.`role` AS `membershipRole`, m.`note` AS `membershipNote` ' . 'FROM `' . CoreTables::USER_TBL . '` i ' . 'INNER JOIN `' . CoreTables::USER_PROFILE_TBL . '` p ON p.`userId` = i.`id` ' . 'INNER JOIN `' . $this->membershipTable() . '` m ON m.`userId` = i.`id` ' . 'WHERE m.`' . $this->entityColumn() . '` = :entityId AND i.`active` = 1 AND i.`removed` = 0 ' . 'ORDER BY i.`name`'); $stmt->bindValue(':entityId', $membershipEntity->getId()); $stmt->execute(); $results = []; while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $results[] = ['id' => $row['id'], 'name' => $row['name'], 'avatar' => $row['avatar'], 'lastVisit' => $row['lastVisit'], 'location' => $row['location'], 'membership' => new Membership($membershipEntity, $this->roleResolver->getRole($this->entityName(), $row['membershipRole']), $row['membershipNote'])]; } $stmt->closeCursor(); return $results; }
public function findUserRequests() { $stmt = $this->conn->prepare('SELECT r.`id`, p.`name` AS `projectName`, r.`name`, r.`createdAt`, r.`lastUpdatedAt`, r.`status`, r.`commentNum` ' . 'FROM `' . CoreTables::AREA_REQUEST_TBL . '` r ' . 'INNER JOIN `' . CoreTables::PROJECT_TBL . '` p ON p.`id` = r.`projectId` ' . 'WHERE r.`requestorId` = :id AND p.`archived` = 0 ORDER BY p.`id` DESC, r.`status`'); $stmt->bindValue(':id', $this->tokenStorage->getToken()->getUser()->getId()); $stmt->execute(); $results = array(); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $row['statusLabel'] = AreaRequest::statusLabel($row['status']); $row['statusText'] = AreaRequest::statusText($row['status']); $results[] = $row; } $stmt->closeCursor(); return $results; }
/** * @return array * @throws \Doctrine\DBAL\DBALException */ private function fetchRows() { $query = 'SELECT sbas_id, ord, viewname, label_en, label_fr, label_de, label_nl FROM sbas'; $statement = $this->connection->prepare($query); $statement->execute(); $rows = []; while ($row = $statement->fetch(\PDO::FETCH_ASSOC)) { $id = $row['sbas_id']; unset($row['sbas_id']); $rows[$id] = $row; } $statement->closeCursor(); return $rows; }
public function saveSettings($projectId, array $settings) { $stmt = $this->conn->prepare('INSERT INTO `' . CoreTables::PROJECT_SETTINGS_TBL . '` (`projectId`, `key`, `name`, `module`, `value`, `type`, `extensionPoint`) ' . 'VALUES(:projectId, :key, :name, :module, :value, :type, :extensionPoint) ON DUPLICATE KEY UPDATE `value` = :newValue'); foreach ($settings as $setting) { $stmt->bindValue(':projectId', $projectId); $stmt->bindValue(':key', $setting->getKey()); $stmt->bindValue(':name', $setting->getName()); $stmt->bindValue(':module', $setting->getModule()); $stmt->bindValue(':value', $setting->getNormalizedValue()); $stmt->bindValue(':type', $setting->getType()); $stmt->bindValue(':extensionPoint', $setting->getExtensionPoint()); $stmt->bindValue(':newValue', $setting->getNormalizedValue()); $stmt->execute(); } }
/** * @throws \Doctrine\DBAL\DBALException */ public function synchronize() { $categories = $this->pluginService->getCategories(); $this->connection->exec("DELETE FROM s_core_plugin_categories"); $statement = $this->connection->prepare("INSERT INTO s_core_plugin_categories (id, locale, parent_id, name)\n VALUES (:id, :locale, :parent_id, :name)"); $pseudo = $this->getPseudoCategories(); foreach ($pseudo as $category) { $statement->execute($category); } foreach ($categories as $category) { foreach ($category->getName() as $locale => $name) { $statement->execute([':id' => $category->getId(), ':name' => $name, ':locale' => $locale, ':parent_id' => $category->getParentId()]); } } }