Creates a native SQL query.
| public createNativeQuery ( string $sql, Doctrine\ORM\Query\ResultSetMapping $rsm ) : Doctrine\ORM\NativeQuery | ||
| $sql | string | |
| $rsm | Doctrine\ORM\Query\ResultSetMapping | The ResultSetMapping to use. |
| return | Doctrine\ORM\NativeQuery | |
/**
* Wyszukuje wydarzenia w zadanej odległości od podanych współrzędnych
*
* @param float $lat
* @param float $lng
* @param int $distance
* @return ArrayObject $events
*/
public function findEventsInRadius($lat, $lng, $distance = 2)
{
$query = "\n SELECT\n subSel2.*\n FROM (\n SELECT\n sin(subSel.dlat / 2) * \n sin(subSel.dlat / 2) + \n cos(subSel.lat1) * \n cos(subSel.lat2) * \n sin(subSel.dlng / 2) * \n sin(subSel.dlng / 2) sel,\n subSel.*\n FROM (\n SELECT \n (radians(:lat)-radians(lat)) dlat, \n (radians(:lng)-radians(lng)) dlng, \n radians(lat) lat1, \n radians(lng) lng1,\n radians(:lat) lat2,\n radians(:lng) lng2,\n Event.*\n From \n Event \n ) subSel \n ) subSel2\n WHERE\n (6372.797 * \n (2 * atan2(sqrt(subSel2.sel), sqrt(1 - subSel2.sel)))) <= :distance\n ";
$rsm = new ResultSetMappingBuilder($this->entityManger);
$rsm->addRootEntityFromClassMetadata('Events\\Entity\\Event', 'event');
$nativeQuery = $this->entityManger->createNativeQuery($query, $rsm);
$nativeQuery->setParameters(array('lat' => $lat, 'lng' => $lng, 'distance' => $distance));
return $nativeQuery->getResult();
}
private function getJournals()
{
$sql = <<<SQL
SELECT id,footer_text FROM journal WHERE journal.footer_text is not null
SQL;
$rsm = new ResultSetMapping();
$rsm->addScalarResult('id', 'id');
$rsm->addScalarResult('footer_text', 'text');
$query = $this->em->createNativeQuery($sql, $rsm);
return $query->getResult();
}
private function getFileIds($tagIds, $meta)
{
$sql = $this->craftSql($tagIds, $meta);
$rsm = new ResultSetMapping($this->em);
$rsm->addScalarResult("file_id", "id");
$list = $this->em->createNativeQuery($sql, $rsm)->getScalarResult();
// clean up the list so it's a flat array of integers
return array_map(function ($e) {
return (int) $e['id'];
}, $list);
}
/**
* Find all members with a birthday in the next $days days.
*
* When $days equals 0 or isn't given, it will give all birthdays of today.
*
* @param int $days The number of days to look ahead.
*
* @return array Of members sorted by birthday
*/
public function findBirthdayMembers($days)
{
// unfortunately, there is no support for functions like DAY() and MONTH()
// in doctrine2, thus we have to use the NativeSQL here
$builder = new ResultSetMappingBuilder($this->em);
$builder->addRootEntityFromClassMetadata('Decision\\Model\\Member', 'm');
$select = $builder->generateSelectClause(array('m' => 't1'));
$sql = "SELECT {$select} FROM Member AS t1" . " WHERE DATEDIFF(DATE_SUB(t1.birth, INTERVAL YEAR(t1.birth) YEAR)," . " DATE_SUB(CURDATE(), INTERVAL YEAR(CURDATE()) YEAR)) BETWEEN 0 AND :days" . " AND t1.expiration >= CURDATE()" . "ORDER BY DATE_SUB(t1.birth, INTERVAL YEAR(t1.birth) YEAR) ASC";
$query = $this->em->createNativeQuery($sql, $builder);
$query->setParameter('days', $days);
return $query->getResult();
}
public function getFoundRows()
{
// Run FOUND_ROWS query and add to results array
$sql = 'SELECT FOUND_ROWS() AS foundRows';
$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$rsm->addScalarResult('foundRows', 'foundRows');
$query = $this->em->createNativeQuery($sql, $rsm);
$foundRows = $query->getResult();
if ($foundRows[0]['foundRows']) {
return $foundRows[0]['foundRows'];
} else {
return 0;
}
}
/**
* Returns the digest hash for a user.
*
* @param string $realm
* @param string $username
* @return string|null
*/
function getDigestHash($realm, $username)
{
$rsm = new ResultSetMapping();
$rsm->addScalarResult('digesta1', 'digesta1');
$sql = 'SELECT digesta1 FROM ' . $this->tableName . ' WHERE username = ?';
$query = $this->entityManager->createNativeQuery($sql, $rsm);
$query->setParameter(1, $username);
$rows = $query->getResult();
if (count($rows)) {
return $rows[0]['digesta1'];
} else {
return null;
}
}
public function createPagination($query, $page, $limit)
{
$rsm = new ResultSetMappingBuilder($this->em);
$rsm->addScalarResult('count', 'count');
$queryCount = $query->getSql();
$queryCount = "SELECT COUNT(*) as count FROM (" . $queryCount . ") as temp";
$queryCount = $this->em->createNativeQuery($queryCount, $rsm);
$queryCount->setParameters($query->getParameters());
$count = $queryCount->getSingleScalarResult();
if (!$count) {
$count = 0;
}
if ($limit === null) {
$limit = 20;
}
if ($page === 'last') {
$page = $count / (int) $limit;
$page = ceil($page);
}
if ($page <= 0) {
$page = 1;
}
$query->setHint('knp_paginator.count', $count);
$pagination = $this->paginator->paginate($query, (int) $page, $limit, array('distinct' => false));
return $pagination;
}
/**
* @param string $schema
* @param string $oid
* @return array
*/
public function getFunctions($schema = '', $oid = '')
{
$oidSql = '';
if (!empty($oid)) {
$oidSql = " AND p.oid = {$oid}";
}
$schemaSql = '';
if (!empty($schema)) {
$schemaSql = " AND n.nspname='{$schema}'";
}
$sql = "SELECT p.oid,\n proowner as owner,\n n.nspname as schema,\n p.proname as name,\n pg_catalog.pg_get_function_result(p.oid) as \"resultDataType\",\n pg_catalog.pg_get_function_arguments(p.oid) as \"argumentDataTypes\",\n CASE\n WHEN p.proisagg THEN 'agg'\n WHEN p.proiswindow THEN 'window'\n WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'\n ELSE 'normal'\n END as type,\n CASE WHEN not p.proisagg THEN pg_get_functiondef(p.oid) ELSE null END as code\n FROM pg_catalog.pg_proc p\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n WHERE pg_catalog.pg_function_is_visible(p.oid) {$oidSql} {$schemaSql}\n ORDER BY 1,2,4";
$rsm = new ResultSetMapping();
$rsm->addScalarResult('oid', 'oid');
$rsm->addScalarResult('owner', 'owner');
$rsm->addScalarResult('schema', 'schema');
$rsm->addScalarResult('name', 'name');
$rsm->addScalarResult('resultDataType', 'resultDataType');
$rsm->addScalarResult('argumentDataTypes', 'argumentDataTypes');
$rsm->addScalarResult('type', 'type');
$rsm->addScalarResult('code', 'code');
$stmt = $this->em->createNativeQuery($sql, $rsm);
$functions = [];
foreach ($stmt->getResult(AbstractQuery::HYDRATE_ARRAY) as $row) {
$fct = new Fonction($schema);
foreach ($row as $key => $value) {
$fct->__set($key, $value);
}
if ($this->index_type == self::INDEX_TYPE_OID) {
$functions[$row['oid']] = $fct;
} elseif ($this->index_type == self::INDEX_TYPE_NAME) {
$functions[$row['name']] = $fct;
}
}
return $functions;
}
/**
* Creates a native SQL query.
*
* @param string $queryName
* @return NativeQuery
*/
public function createNativeNamedQuery($queryName)
{
$queryMapping = $this->_class->getNamedNativeQuery($queryName);
$rsm = new Query\ResultSetMappingBuilder($this->_em);
$rsm->addNamedNativeQueryMapping($this->_class, $queryMapping);
return $this->_em->createNativeQuery($queryMapping['query'], $rsm);
}
/**
* Updates the list of group members for a group principal.
*
* The principals should be passed as a list of uri's.
*
* @param string $principal
* @param array $members
* @return void
* @throws \Sabre\DAV\Exception
*/
function setGroupMemberSet($principal, array $members)
{
$rsm = new ResultSetMapping();
$rsm->addScalarResult('id', 'id');
$rsm->addScalarResult('uri', 'uri');
// Grabbing the list of principal id's.
$sql = 'SELECT id, uri FROM ' . $this->tableName . ' WHERE uri IN (? ' . str_repeat(', ? ', count($members)) . ')';
$query = $this->entityManager->createNativeQuery($sql, $rsm);
$query->setParameters(array_merge([$principal], $members));
$rows = $query->getResult();
$memberIds = [];
$principalId = null;
foreach ($rows as $row) {
if ($row['uri'] == $principal) {
$principalId = $row['id'];
} else {
$memberIds[] = $row['id'];
}
}
if (!$principalId) {
throw new DAV\Exception('Principal not found');
}
$connection = $this->entityManager->getConnection();
// Wiping out old members
$sql = 'DELETE FROM ' . $this->groupMembersTableName . ' WHERE principal_id = ?';
$connection->executeUpdate($sql, [$principalId]);
foreach ($memberIds as $memberId) {
$sql = 'INSERT INTO ' . $this->groupMembersTableName . ' (principal_id, member_id) VALUES (?, ?)';
$connection->executeUpdate($sql, [$principalId, $memberId]);
}
}
/**
*
* @return array
*/
public function generateExitedJournalData()
{
$sql = "SELECT count(journal.id) as count FROM journal WHERE status = '" . JournalStatuses::STATUS_EXITED . "'";
$rsm = new ResultSetMapping();
$rsm->addScalarResult('count', 'count');
$query = $this->manager->createNativeQuery($sql, $rsm);
$results = $query->getSingleResult();
return $results;
}
/**
* @param Event $event
* @param Constraint $constraint
*/
public function validate($event, Constraint $constraint)
{
$eventEndDate = $event->getDate()->format('Y-m-d H:i:s');
$eventEndDate = new \DateTime($eventEndDate);
$eventEndDate->modify("+" . $event->getDuration() . " minutes")->format('Y-m-d H:i:s');
// Map result set to my Entity.
$rsm = new ResultSetMapping();
$rsm->addEntityResult('AppBundle\\Entity\\Event', 'e');
$rsm->addFieldResult('e', 'termin', 'date');
$rsm->addFieldResult('e', 'dauer', 'duration');
// Using MySQL for better DateTime handling
$query = $this->em->createNativeQuery("SELECT * FROM veranstaltung e\n WHERE e.termin < :endDate\n AND e.termin + INTERVAL e.dauer MINUTE > :startDate", $rsm);
$query->setParameters(array("endDate" => $eventEndDate, 'startDate' => $event->getDate()->format('Y-m-d H:i:s')));
$overlappingEntities = $query->getResult();
/** @var $constraint HasNoDateOverlap */
if ($overlappingEntities) {
$this->context->addViolation($constraint->message);
}
}
/**
* Get all groups of a kind
*
* @param Kind $kind
* @param integer|null $orgId
* @return mixed
*/
public function groupsOfKind(Kind $kind, $orgId = null)
{
if ($orgId) {
$sql = <<<EOT
SELECT
p.id AS id,
p.lastName,
g.description,
g.avatar
FROM groups g
INNER JOIN parties p ON g.id = p.id
INNER JOIN p2p_relations rel ON p.id = rel.context
WHERE p.kind_id = ? AND rel.reference =?
ORDER BY p.id
EOT;
} else {
$sql = <<<EOT
SELECT
p.id AS id,
p.firstName,
p.lastName,
g.description,
g.avatar
FROM groups g
INNER JOIN parties p ON g.id = p.id
WHERE p.kind_id = ?
ORDER BY p.id
EOT;
}
$rsm = new ResultSetMappingBuilder($this->em);
$rsm->addEntityResult('Bakgat\\Notos\\Domain\\Model\\Identity\\Party', 'p');
$rsm->addEntityResult('Bakgat\\Notos\\Domain\\Model\\Identity\\Group', 'g');
$rsm->addFieldResult('p', 'id', 'id');
$rsm->addFieldResult('p', 'lastName', 'lastName');
$rsm->addFieldResult('g', 'description', 'description');
$qb = $this->em->createNativeQuery($sql, $rsm)->setParameter(1, $kind->id());
if ($orgId) {
$qb->setParameter(2, $orgId);
}
$groups = $qb->getScalarResult();
return $groups;
}
private function haveTranslation($id)
{
$sql = <<<SQL
SELECT journal_announcement_translations.id FROM journal_announcement_translations WHERE translatable_id = ?
SQL;
$rsm = new ResultSetMapping();
$rsm->addScalarResult('id', 'id');
$query = $this->em->createNativeQuery($sql, $rsm);
$query->setParameter(1, $id);
return $query->getResult();
}
/**
* Get Paginator
*
* @param integer $limit ( limit per page )
* @param array $options
* @return \Knp\Bundle\PaginatorBundle\Pagination\SlidingPagination
*/
protected function getPaginator($limit = 30, $options = array())
{
$page = $this->get('request')->query->get('page', 1);
$cnt = 0;
if ($this->get('request')->query->has('page')) {
$page = $this->get('request')->query->get('page');
$this->get('session')->set('_pager_' . $this->get('request')->get('_route'), $page);
} elseif ($this->get('session')->has('_pager_' . $this->get('request')->get('_route'))) {
$page = $this->get('session')->get('_pager_' . $this->get('request')->get('_route'));
}
if (isset($options['total_item_count']) and (int) $options['total_item_count']) {
$cnt = $options['total_item_count'];
}
$this->paginator = $this->get('knp_paginator');
if (is_array($this->query) or $this->query instanceof QueryBuilder) {
$pagination = $this->paginator->paginate($this->query, $page, $limit, $options);
} elseif ($this->query instanceof NativeQuery) {
$rsm = new ResultSetMapping();
$rsm->addScalarResult('cnt', 'cnt');
$q = strstr($this->query->getSQL(), " FROM ");
$q = "SELECT COUNT(*) cnt " . $q;
$cntQuery = $this->em->createNativeQuery($q, $rsm)->setParameters($this->query->getParameters());
try {
$cnt = $cntQuery->getSingleScalarResult();
} catch (\Doctrine\Orm\NoResultException $e) {
$cnt = 0;
}
$sql = $this->query->getSQL();
$pagination = $this->paginator->paginate(array());
$sort_name = $pagination->getPaginatorOption('sortFieldParameterName');
$sort_direction_name = $pagination->getPaginatorOption('sortDirectionParameterName');
if ($this->get('request')->query->has($sort_name) and $this->get('request')->query->has($sort_direction_name)) {
$sql .= ' ORDER BY ' . $this->get('request')->query->get($sort_name) . ' ' . $this->get('request')->query->get($sort_direction_name);
} elseif (isset($options['default_sort']) and $options['default_sort']) {
$sql .= ' ORDER BY ';
foreach ($options['default_sort'] as $field => $type) {
$sql .= $field . ' ' . $type . ',';
}
$sql = trim($sql, ',');
}
if (!isset($options['not_use_limit_offset'])) {
$offset = $limit * ($page - 1);
$this->query->setSQL($sql . ' LIMIT ' . $limit . ' OFFSET ' . $offset);
}
$pagination->setCurrentPageNumber($page);
$pagination->setItemNumberPerPage($limit);
$pagination->setTotalItemCount($cnt);
$pagination->setItems($this->query->getResult());
}
$pagination->setTemplate($this->container->getParameter('zk2_admin_panel.pagination_template'));
$pagination->setSortableTemplate($this->container->getParameter('zk2_admin_panel.sortable_template'));
return compact('pagination');
}
public function normalizeJournalTotalArticleDownload()
{
$rsm = new ResultSetMapping();
$sql = <<<SQL
UPDATE journal
SET total_article_download =
(SELECT SUM(t2.download_count)
FROM article t2
WHERE t2.journal_id = journal.id)
SQL;
$query = $this->em->createNativeQuery($sql, $rsm);
$query->getResult();
}
/**
*
* @param type $query
* @param array $params
* @return array
*/
public function nativeQuery($query, array $params = null)
{
try {
$qr = $this->em->createNativeQuery($query, new \Doctrine\ORM\Query\ResultSetMapping());
if ($params != null) {
$qr->setParameter($params);
}
$result = $qr->getResult();
//$this->em->flush();
return $result;
} catch (Exception $exc) {
return NULL;
}
}
public function fillIndexesTables(&$schemas)
{
$this->em->clear();
//Filling Indexes
$sql = "SELECT c2.relname as name,\n c2.relname as oid,\n i.indisprimary as \"isPrimary\",\n i.indisunique as \"isUnique\",\n i.indisclustered as \"isClustered\",\n i.indisvalid as \"isValid\",\n pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) as \"creationQuery\",\n pg_catalog.pg_get_constraintdef(con.oid, true) as \"constraintDef\",\n contype,\n condeferrable,\n condeferred,\n c2.reltablespace,\n c.oid as table,\n n.nspname as schema,\n c.relname as table_name\n FROM pg_catalog.pg_class c\n INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n INNER JOIN pg_catalog.pg_index i ON c.oid = i.indrelid\n INNER JOIN pg_catalog.pg_class c2 ON i.indexrelid = c2.oid\n LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))\n WHERE c.relkind = 'r'\n AND n.nspname <> 'pg_catalog'\n AND n.nspname <> 'information_schema'\n AND n.nspname !~ '^pg_toast'\n AND n.nspname !~ '^pg_temp'\n AND n.nspname <> 'londiste'\n AND n.nspname <> 'pgq'\n --AND pg_catalog.pg_table_is_visible(c.oid)\n ORDER BY i.indisprimary DESC,\n i.indisunique DESC,\n c2.relname";
$rsm = new ResultSetMapping();
$rsm->addScalarResult('oid', 'oid');
$rsm->addScalarResult('name', 'name');
$rsm->addScalarResult('isPrimary', 'isPrimary');
$rsm->addScalarResult('isUnique', 'isUnique');
$rsm->addScalarResult('isClustered', 'isClustered');
$rsm->addScalarResult('isValid', 'isValid');
$rsm->addScalarResult('creationQuery', 'creationQuery');
$rsm->addScalarResult('constraintDef', 'constraintDef');
$rsm->addScalarResult('contype', 'contype');
$rsm->addScalarResult('condeferrable', 'condeferrable');
$rsm->addScalarResult('condeferred', 'condeferred');
$rsm->addScalarResult('reltablespace', 'reltablespace');
$rsm->addScalarResult('schema', 'schema');
$rsm->addScalarResult('table', 'table');
$rsm->addScalarResult('table_name', 'table_name');
$stmt = $this->em->createNativeQuery($sql, $rsm);
$stmt->useResultCache(true, PgRetriever::CACHE_LIFETIME);
foreach ($stmt->getResult(AbstractQuery::HYDRATE_ARRAY) as $row) {
$index = new Index($row['schema'], $row['table']);
foreach ($row as $key => $value) {
if ($key == 'creationQuery') {
$data = explode('USING', $value);
if (count($data) == 2) {
$infos = explode(' ', trim($data[1]));
$type = $infos[0];
$columns = trim(substr($data[1], strlen($infos[0]) + 2));
$index->__set('type', $type);
$index->__set('columns', $columns);
} else {
$index->__set('type', $value);
}
} else {
$index->__set($key, $value);
}
}
if ($this->index_type == PgRetriever::INDEX_TYPE_OID) {
$schemas[$row['schema']]->addTableIndex($row['table'], $index);
} elseif ($this->index_type == PgRetriever::INDEX_TYPE_NAME) {
$schemas[$row['schema']]->addTableIndex($row['table_name'], $index);
}
unset($index);
}
unset($stmt);
}
public function prepareTotalCount()
{
$sql = 'SELECT FOUND_ROWS() AS foundRows';
$rsm = new ResultSetMapping();
$rsm->addScalarResult('foundRows', 'foundRows');
$query = $this->manager->createNativeQuery($sql, $rsm);
$foundRows = $query->getResult();
$results['foundRows'] = $foundRows[0]['foundRows'];
// echo "<pre>";
// \Doctrine\Common\Util\Debug::dump($results);
// echo "</pre>";
// exit;
//die("daj".$results['foundRows']);
$this->totalCount = (int) $results['foundRows'];
// echo "<h1>".$this->totalCount."</h1>";
}
/**
* Returns all scheduling objects for the inbox collection.
*
* These objects should be returned as an array. Every item in the array
* should follow the same structure as returned from getSchedulingObject.
*
* The main difference is that 'calendardata' is optional.
*
* @param string $principalUri
* @return array
*/
function getSchedulingObjects($principalUri)
{
$rsm = new ResultSetMapping();
$rsm->addScalarResult('id', 'id');
$rsm->addScalarResult('calendardata', 'calendardata');
$rsm->addScalarResult('uri', 'uri');
$rsm->addScalarResult('lastmodified', 'lastmodified');
$rsm->addScalarResult('etag', 'etag');
$rsm->addScalarResult('size', 'size');
$sql = 'SELECT id, calendardata, uri, lastmodified, etag, size FROM ' . $this->schedulingObjectTableName . ' WHERE principaluri = ?';
$query = $this->entityManager->createNativeQuery($sql, $rsm);
$query->setParameter(1, $principalUri);
$rows = $query->getResult();
$result = [];
foreach ($rows as $row) {
$result[] = ['calendardata' => $row['calendardata'], 'uri' => $row['uri'], 'lastmodified' => $row['lastmodified'], 'etag' => '"' . $row['etag'] . '"', 'size' => (int) $row['size']];
}
return $result;
}
/**
* Returns valid IDs for a specific entity with ACL restrictions for current user applied
*
* @param PermissionDefinition $permissionDef
*
* @throws InvalidArgumentException
*
* @return array
*/
public function getAllowedEntityIds(PermissionDefinition $permissionDef)
{
$rootEntity = $permissionDef->getEntity();
if (empty($rootEntity)) {
throw new InvalidArgumentException("You have to provide an entity class name!");
}
$builder = new MaskBuilder();
foreach ($permissionDef->getPermissions() as $permission) {
$mask = constant(get_class($builder) . '::MASK_' . strtoupper($permission));
$builder->add($mask);
}
$query = new Query($this->em);
$query->setHint('acl.mask', $builder->get());
$query->setHint('acl.root.entity', $rootEntity);
$sql = $this->getPermittedAclIdsSQLForUser($query);
$rsm = new ResultSetMapping();
$rsm->addScalarResult('id', 'id');
$nativeQuery = $this->em->createNativeQuery($sql, $rsm);
$transform = function ($item) {
return $item['id'];
};
$result = array_map($transform, $nativeQuery->getScalarResult());
return $result;
}
public function createPagination($query, $page, $limit)
{
$rsm = new ResultSetMappingBuilder($this->em);
$rsm->addScalarResult('count', 'count');
// get sql, get the from part and remove all other fields then the id field
// so that we have a query who select only one field
// for count this is better because we dont need the data
$queryCount = $query->getSql();
$queryCountTmp = explode("FROM", $queryCount);
$queryCountSelect = array_shift($queryCountTmp);
$queryCountEnd = implode("FROM", $queryCountTmp);
$queryCountSelect = explode(",", $queryCountSelect);
$queryCountSelect = reset($queryCountSelect);
if (strpos($queryCountEnd, "GROUP BY") === false) {
$queryCount = " SELECT COUNT(*) as count FROM " . $queryCountEnd;
} else {
$queryCount = "SELECT COUNT(*) as count FROM (" . $queryCountSelect . " FROM " . $queryCountEnd . ") as temp";
}
// create now the query based on the native sql query and get the count
$queryCount = $this->em->createNativeQuery($queryCount, $rsm);
$queryCount->setParameters($query->getParameters());
$count = $queryCount->getSingleScalarResult();
if (!$count) {
$count = 0;
}
if ($page === 'last') {
$page = $count / $limit;
$page = ceil($page);
}
if ($page <= 0) {
$page = 1;
}
$query->setHint('knp_paginator.count', $count);
$pagination = $this->paginator->paginate($query, (int) $page, $limit, array('distinct' => false));
return $pagination;
}
/**
* Checks whether the table exists or not.
*
* @param $tableName
*
* @return boolean
*/
private function tableExists(EntityManager $em, $table)
{
return (bool) $em->createNativeQuery('SHOW TABLE STATUS WHERE Name="' . $table . '" COLLATE utf8_bin ', (new ResultSetMapping())->addScalarResult('Name', 'Name'))->getOneOrNullResult();
}
protected function generateDataValueSummary($timespan)
{
switch ($timespan) {
case 'daily':
$entityName = 'UsageStatisticServerBundle:DailyDataValueSummary';
$query = 'YEAR(v.datetime) AS year, MONTH(v.datetime) AS month, DAY(v.datetime) AS day';
$fields = 'year, month, day';
$keys = ['year', 'month', 'day', 'key', 'value'];
break;
case 'weekly':
$entityName = 'UsageStatisticServerBundle:WeeklyDataValueSummary';
$query = 'YEAR(v.datetime) AS year, WEEKOFYEAR(v.datetime) AS week';
$fields = 'year, week';
$keys = ['year', 'week', 'key', 'value'];
break;
case 'monthly':
$entityName = 'UsageStatisticServerBundle:MonthlyDataValueSummary';
$query = 'YEAR(v.datetime) AS year, MONTH(v.datetime) AS month';
$fields = 'year, month';
$keys = ['year', 'month', 'key', 'value'];
break;
case 'quarterly':
$entityName = 'UsageStatisticServerBundle:QuarterlyDataValueSummary';
$query = 'YEAR(v.datetime) AS year, QUARTER(v.datetime) AS quarter';
$fields = 'year, quarter';
$keys = ['year', 'quarter', 'key', 'value'];
break;
case 'yearly':
$entityName = 'UsageStatisticServerBundle:YearlyDataValueSummary';
$query = 'YEAR(v.datetime) AS year';
$fields = 'year';
$keys = ['year', 'key', 'value'];
break;
default:
throw new \InvalidArgumentException();
}
$mapping = new ResultSetMapping();
$mapping->addScalarResult('year', 'year');
$mapping->addScalarResult('month', 'month');
$mapping->addScalarResult('quarter', 'quarter');
$mapping->addScalarResult('week', 'week');
$mapping->addScalarResult('day', 'day');
$mapping->addScalarResult('key_name', 'key');
$mapping->addScalarResult('value', 'value');
$mapping->addScalarResult('summary', 'summary');
$sql = <<<SQL
SELECT {$fields}, key_name, value, COUNT(id) AS summary
FROM (
\tSELECT {$query}, i.id, v.key_name, v.value
\tFROM data_values v
\tINNER JOIN installations i
\tON i.id = v.installation
\tGROUP BY {$fields}, i.id, v.key_name, v.value
) t
GROUP BY {$fields}, key_name, value
SQL;
$query = $this->entityManager->createNativeQuery($sql, $mapping);
$result = $query->getResult();
$repository = $this->entityManager->getRepository($entityName);
$class = new \ReflectionClass($repository->getClassName());
$propertyAccessor = new PropertyAccessor();
$this->truncate($repository);
foreach ($result as $row) {
$entity = $class->newInstance();
foreach ($keys as $key) {
$propertyAccessor->setValue($entity, $key, $row[$key]);
}
$entity->setSummary($row['summary']);
$this->entityManager->persist($entity);
if ($this->isFlushRequired()) {
$this->entityManager->flush();
$this->entityManager->clear();
}
}
$this->entityManager->flush();
}
/**
* Check whether the usr table has a nonce column or not.
*
* @param EntityManager $em
*
* @return boolean
*/
private function hasField(EntityManager $em, $fieldName)
{
return (bool) $em->createNativeQuery("SHOW FIELDS FROM usr WHERE Field = :field", (new ResultSetMapping())->addScalarResult('Field', 'Field'))->setParameter(':field', $fieldName)->getOneOrNullResult();
}
/**
* {@inheritdoc}
*/
public function createNativeQuery($sql, ResultSetMapping $rsm)
{
return $this->wrapped->createNativeQuery($sql, $rsm);
}
/**
* pings database to keep connection alive
*
* @param EntityManager $manager
*/
protected function pingDatabase($manager)
{
$tmp = $manager->createNativeQuery('SELECT 1', new ResultSetMapping())->getResult();
unset($tmp);
$manager->clear();
}
private function assertUsrTableIsSanitized(EntityManager $em)
{
$rs = $em->createNativeQuery("SHOW FIELDS FROM usr_backup WHERE Field = 'usr_login';", (new ResultSetMapping())->addScalarResult('Type', 'Type'))->getSingleResult();
$this->assertSame('varchar(128)', strtolower($rs['Type']));
}
/**
* {@inheritDoc}
*
* @static
*/
public static function createNativeQuery($sql, $rsm)
{
return \Doctrine\ORM\EntityManager::createNativeQuery($sql, $rsm);
}
/**
* @param InputInterface $input
* @param OutputInterface $output
*
* @return void
*/
protected function execute(InputInterface $input, OutputInterface $output)
{
$this->io->title($this->getDescription());
$totalAuthorCount = $this->getAuthorCount();
$this->io->progressStart($totalAuthorCount);
$rsm = new ResultSetMapping();
for ($count = 0; $count <= $totalAuthorCount; $count += self::STEP) {
$sql = <<<SQL
UPDATE author
SET user_id = users.id
FROM users
WHERE author.email = users.email
AND author.id > ?
and author.id < ?
and author.user_id is null
SQL;
$query = $this->em->createNativeQuery($sql, $rsm);
$query->setParameter(1, $count);
$query->setParameter(2, $count + self::STEP);
$query->getResult();
if (self::STEP > $totalAuthorCount) {
$this->io->progressFinish();
} else {
$this->io->progressAdvance(self::STEP);
}
}
$this->io->newLine(2);
$this->io->success('All process finished');
}
