Returns a qualified identifier for $tableName.
public quoteTable ( string $tableName ) : string | ||
$tableName | string | |
Résultat | string |
public function init() { $selectQuery = $this->dbHandler->createSelectQuery(); $selectQuery->select('filepath')->from($this->dbHandler->quoteTable('ezimagefile')); $this->statement = $selectQuery->prepare(); $this->statement->execute(); }
/** * Creates a select query for content version objects * * Creates a select query with all necessary joins to fetch a complete * content object. Does not apply any WHERE conditions. * * @return \eZ\Publish\Core\Persistence\Database\SelectQuery */ public function createVersionInfoFindQuery() { /** @var $query \eZ\Publish\Core\Persistence\Database\SelectQuery */ $query = $this->dbHandler->createSelectQuery(); $query->select($this->dbHandler->aliasedColumn($query, 'id', 'ezcontentobject_version'), $this->dbHandler->aliasedColumn($query, 'version', 'ezcontentobject_version'), $this->dbHandler->aliasedColumn($query, 'modified', 'ezcontentobject_version'), $this->dbHandler->aliasedColumn($query, 'creator_id', 'ezcontentobject_version'), $this->dbHandler->aliasedColumn($query, 'created', 'ezcontentobject_version'), $this->dbHandler->aliasedColumn($query, 'status', 'ezcontentobject_version'), $this->dbHandler->aliasedColumn($query, 'contentobject_id', 'ezcontentobject_version'), $this->dbHandler->aliasedColumn($query, 'initial_language_id', 'ezcontentobject_version'), $this->dbHandler->aliasedColumn($query, 'language_mask', 'ezcontentobject_version'), $this->dbHandler->aliasedColumn($query, 'main_node_id', 'ezcontentobject_tree'), $this->dbHandler->aliasedColumn($query, 'id', 'ezcontentobject'), $this->dbHandler->aliasedColumn($query, 'contentclass_id', 'ezcontentobject'), $this->dbHandler->aliasedColumn($query, 'section_id', 'ezcontentobject'), $this->dbHandler->aliasedColumn($query, 'owner_id', 'ezcontentobject'), $this->dbHandler->aliasedColumn($query, 'remote_id', 'ezcontentobject'), $this->dbHandler->aliasedColumn($query, 'current_version', 'ezcontentobject'), $this->dbHandler->aliasedColumn($query, 'initial_language_id', 'ezcontentobject'), $this->dbHandler->aliasedColumn($query, 'modified', 'ezcontentobject'), $this->dbHandler->aliasedColumn($query, 'published', 'ezcontentobject'), $this->dbHandler->aliasedColumn($query, 'status', 'ezcontentobject'), $this->dbHandler->aliasedColumn($query, 'name', 'ezcontentobject'), $this->dbHandler->aliasedColumn($query, 'language_mask', 'ezcontentobject'), $this->dbHandler->aliasedColumn($query, 'name', 'ezcontentobject_name'), $this->dbHandler->aliasedColumn($query, 'content_translation', 'ezcontentobject_name'))->from($this->dbHandler->quoteTable('ezcontentobject_version'))->leftJoin($this->dbHandler->quoteTable('ezcontentobject'), $query->expr->eq($this->dbHandler->quoteColumn('id', 'ezcontentobject'), $this->dbHandler->quoteColumn('contentobject_id', 'ezcontentobject_version')))->leftJoin($this->dbHandler->quoteTable('ezcontentobject_tree'), $query->expr->lAnd($query->expr->eq($this->dbHandler->quoteColumn('contentobject_id', 'ezcontentobject_tree'), $this->dbHandler->quoteColumn('contentobject_id', 'ezcontentobject_version')), $query->expr->eq($this->dbHandler->quoteColumn('main_node_id', 'ezcontentobject_tree'), $this->dbHandler->quoteColumn('node_id', 'ezcontentobject_tree'))))->leftJoin($this->dbHandler->quoteTable('ezcontentobject_name'), $query->expr->lAnd($query->expr->eq($this->dbHandler->quoteColumn('contentobject_id', 'ezcontentobject_name'), $this->dbHandler->quoteColumn('contentobject_id', 'ezcontentobject_version')), $query->expr->eq($this->dbHandler->quoteColumn('content_version', 'ezcontentobject_name'), $this->dbHandler->quoteColumn('version', 'ezcontentobject_version')))); return $query; }
/** * Fetch location Ids for the given content object. * * @param int $contentObjectId * @return array Location nodes Ids */ protected function getContentLocationIds($contentObjectId) { $query = $this->databaseHandler->createSelectQuery(); $query->select('node_id')->from($this->databaseHandler->quoteTable(self::CONTENTOBJECT_TREE_TABLE))->where($query->expr->eq('contentobject_id', $contentObjectId)); $stmt = $query->prepare(); $stmt->execute(); $nodeIds = $stmt->fetchAll(PDO::FETCH_COLUMN); return is_array($nodeIds) ? array_map('intval', $nodeIds) : []; }
/** * Remove all limitations for a policy. * * @param mixed $policyId */ public function removePolicyLimitations($policyId) { $query = $this->handler->createSelectQuery(); $query->select($this->handler->aliasedColumn($query, 'id', 'ezpolicy_limitation'), $this->handler->aliasedColumn($query, 'id', 'ezpolicy_limitation_value'))->from($this->handler->quoteTable('ezpolicy'))->leftJoin($this->handler->quoteTable('ezpolicy_limitation'), $query->expr->eq($this->handler->quoteColumn('policy_id', 'ezpolicy_limitation'), $this->handler->quoteColumn('id', 'ezpolicy')))->leftJoin($this->handler->quoteTable('ezpolicy_limitation_value'), $query->expr->eq($this->handler->quoteColumn('limitation_id', 'ezpolicy_limitation_value'), $this->handler->quoteColumn('id', 'ezpolicy_limitation')))->where($query->expr->eq($this->handler->quoteColumn('id', 'ezpolicy'), $query->bindValue($policyId, null, \PDO::PARAM_INT))); $statement = $query->prepare(); $statement->execute(); $limitationIdsSet = array(); $limitationValuesSet = array(); while ($row = $statement->fetch(\PDO::FETCH_ASSOC)) { if ($row['ezpolicy_limitation_id'] !== null) { $limitationIdsSet[$row['ezpolicy_limitation_id']] = true; } if ($row['ezpolicy_limitation_value_id'] !== null) { $limitationValuesSet[$row['ezpolicy_limitation_value_id']] = true; } } if (!empty($limitationIdsSet)) { $query = $this->handler->createDeleteQuery(); $query->deleteFrom($this->handler->quoteTable('ezpolicy_limitation'))->where($query->expr->in($this->handler->quoteColumn('id'), array_keys($limitationIdsSet))); $query->prepare()->execute(); } if (!empty($limitationValuesSet)) { $query = $this->handler->createDeleteQuery(); $query->deleteFrom($this->handler->quoteTable('ezpolicy_limitation_value'))->where($query->expr->in($this->handler->quoteColumn('id'), array_keys($limitationValuesSet))); $query->prepare()->execute(); } }
/** * Deletes the relation with the given $relationId. * * @param int $relationId * @param int $type {@see \eZ\Publish\API\Repository\Values\Content\Relation::COMMON, * \eZ\Publish\API\Repository\Values\Content\Relation::EMBED, * \eZ\Publish\API\Repository\Values\Content\Relation::LINK, * \eZ\Publish\API\Repository\Values\Content\Relation::FIELD} * * @return void */ public function deleteRelation($relationId, $type) { // Legacy Storage stores COMMON, LINK and EMBED types using bitmask, therefore first load // existing relation type by given $relationId for comparison /** @var $query \eZ\Publish\Core\Persistence\Database\SelectQuery */ $query = $this->dbHandler->createSelectQuery(); $query->select($this->dbHandler->quoteColumn("relation_type"))->from($this->dbHandler->quoteTable("ezcontentobject_link"))->where($query->expr->eq($this->dbHandler->quoteColumn("id"), $query->bindValue($relationId, null, \PDO::PARAM_INT))); $statement = $query->prepare(); $statement->execute(); $loadedRelationType = $statement->fetchColumn(); if (!$loadedRelationType) { return; } // If relation type matches then delete if ($loadedRelationType == $type) { /** @var $query \eZ\Publish\Core\Persistence\Database\DeleteQuery */ $query = $this->dbHandler->createDeleteQuery(); $query->deleteFrom("ezcontentobject_link")->where($query->expr->eq($this->dbHandler->quoteColumn("id"), $query->bindValue($relationId, null, \PDO::PARAM_INT))); $query->prepare()->execute(); } else { if ($loadedRelationType & $type) { /** @var $query \eZ\Publish\Core\Persistence\Database\UpdateQuery */ $query = $this->dbHandler->createUpdateQuery(); $query->update($this->dbHandler->quoteTable("ezcontentobject_link"))->set($this->dbHandler->quoteColumn("relation_type"), $query->expr->bitAnd($this->dbHandler->quoteColumn("relation_type"), $query->bindValue(~$type, null, \PDO::PARAM_INT)))->where($query->expr->eq($this->dbHandler->quoteColumn("id"), $query->bindValue($relationId, null, \PDO::PARAM_INT))); $query->prepare()->execute(); } else { // No match, do nothing } } }
/** * Returns searchable field mapping data * * @return array */ public function getSearchableFieldMapData() { $query = $this->dbHandler->createSelectQuery(); $query->select($this->dbHandler->alias($this->dbHandler->quoteColumn("identifier", "ezcontentclass_attribute"), $this->dbHandler->quoteIdentifier("field_definition_identifier")), $this->dbHandler->alias($this->dbHandler->quoteColumn("identifier", "ezcontentclass"), $this->dbHandler->quoteIdentifier("content_type_identifier")), $this->dbHandler->alias($this->dbHandler->quoteColumn("id", "ezcontentclass_attribute"), $this->dbHandler->quoteIdentifier("field_definition_id")), $this->dbHandler->alias($this->dbHandler->quoteColumn("data_type_string", "ezcontentclass_attribute"), $this->dbHandler->quoteIdentifier("field_type_identifier")))->from($this->dbHandler->quoteTable("ezcontentclass_attribute"))->innerJoin($this->dbHandler->quoteTable("ezcontentclass"), $query->expr->eq($this->dbHandler->quoteColumn("contentclass_id", "ezcontentclass_attribute"), $this->dbHandler->quoteColumn("id", "ezcontentclass")))->where($query->expr->eq($this->dbHandler->quoteColumn("is_searchable", "ezcontentclass_attribute"), $query->bindValue(1, null, PDO::PARAM_INT))); $statement = $query->prepare($query); $statement->execute(); return $statement->fetchAll(\PDO::FETCH_ASSOC); }
/** * Returns all Content IDs for a given $contentTypeId. * * @param int $contentTypeId * * @return int[] */ public function getContentIdsByContentTypeId($contentTypeId) { $query = $this->dbHandler->createSelectQuery(); $query->select($this->dbHandler->quoteColumn('id'))->from($this->dbHandler->quoteTable('ezcontentobject'))->where($query->expr->eq($this->dbHandler->quoteColumn('contentclass_id'), $query->bindValue($contentTypeId, null, PDO::PARAM_INT))); $statement = $query->prepare(); $statement->execute(); return $statement->fetchAll(PDO::FETCH_COLUMN); }
/** * Inserts keywords for tag with provided tag ID. * * @param mixed $tagId * @param array $keywords * @param string $mainLanguageCode * @param bool $alwaysAvailable */ protected function insertTagKeywords($tagId, array $keywords, $mainLanguageCode, $alwaysAvailable) { foreach ($keywords as $languageCode => $keyword) { $query = $this->handler->createInsertQuery(); $query->insertInto($this->handler->quoteTable('eztags_keyword'))->set($this->handler->quoteColumn('keyword_id'), $query->bindValue($tagId, null, PDO::PARAM_INT))->set($this->handler->quoteColumn('language_id'), $query->bindValue($this->languageHandler->loadByLanguageCode($languageCode)->id + (int) ($languageCode === $mainLanguageCode && $alwaysAvailable), null, PDO::PARAM_INT))->set($this->handler->quoteColumn('keyword'), $query->bindValue($keyword, null, PDO::PARAM_STR))->set($this->handler->quoteColumn('locale'), $query->bindValue($languageCode, null, PDO::PARAM_STR))->set($this->handler->quoteColumn('status'), $query->bindValue(1, null, PDO::PARAM_INT)); $query->prepare()->execute(); } }
/** * Get sorted arrays of content IDs, which should be returned * * @param Criterion $filter * @param array $sort * @param mixed $offset * @param mixed $limit * @param mixed $translations * * @return int[] */ protected function getContentInfoList( Criterion $filter, $sort, $offset, $limit, $translations ) { $query = $this->handler->createSelectQuery(); $query->selectDistinct( 'ezcontentobject.*', $this->handler->aliasedColumn( $query, 'main_node_id', 'main_tree' ) ); if ( $sort !== null ) { $this->sortClauseConverter->applySelect( $query, $sort ); } $query->from( $this->handler->quoteTable( 'ezcontentobject' ) )->innerJoin( 'ezcontentobject_version', 'ezcontentobject.id', 'ezcontentobject_version.contentobject_id' )->leftJoin( $this->handler->alias( $this->handler->quoteTable( 'ezcontentobject_tree' ), $this->handler->quoteIdentifier( 'main_tree' ) ), $query->expr->lAnd( $query->expr->eq( $this->handler->quoteColumn( "contentobject_id", "main_tree" ), $this->handler->quoteColumn( "id", "ezcontentobject" ) ), $query->expr->eq( $this->handler->quoteColumn( "main_node_id", "main_tree" ), $this->handler->quoteColumn( "node_id", "main_tree" ) ) ) ); if ( $sort !== null ) { $this->sortClauseConverter->applyJoin( $query, $sort ); } $query->where( $this->getQueryCondition( $filter, $query, $translations ) ); if ( $sort !== null ) { $this->sortClauseConverter->applyOrderBy( $query ); } $query->limit( $limit, $offset ); $statement = $query->prepare(); $statement->execute(); return $statement->fetchAll( \PDO::FETCH_ASSOC ); }
/** * Loads an array with data about UrlWildcards (paged). * * @param mixed $offset * @param mixed $limit * * @return array */ public function loadUrlWildcardsData($offset = 0, $limit = -1) { $limit = $limit === -1 ? self::MAX_LIMIT : $limit; /** @var $query \eZ\Publish\Core\Persistence\Database\SelectQuery */ $query = $this->dbHandler->createSelectQuery(); $query->select('*')->from($this->dbHandler->quoteTable('ezurlwildcard'))->limit($limit > 0 ? $limit : self::MAX_LIMIT, $offset); $stmt = $query->prepare(); $stmt->execute(); return $stmt->fetchAll(\PDO::FETCH_ASSOC); }
/** * Returns total results count for $criterion and $sortClauses. * * @param \eZ\Publish\API\Repository\Values\Content\Query\Criterion $criterion * * @return array */ protected function getTotalCount(Criterion $criterion) { $query = $this->handler->createSelectQuery(); $query->select($query->alias($query->expr->count('*'), 'count'))->from($this->handler->quoteTable('ezcontentobject_tree'))->innerJoin('ezcontentobject', 'ezcontentobject_tree.contentobject_id', 'ezcontentobject.id')->innerJoin('ezcontentobject_version', 'ezcontentobject.id', 'ezcontentobject_version.contentobject_id'); $query->where($this->criteriaConverter->convertCriteria($query, $criterion), $query->expr->eq('ezcontentobject.status', $query->bindValue(1, null, PDO::PARAM_INT)), $query->expr->eq('ezcontentobject_version.status', $query->bindValue(1, null, PDO::PARAM_INT)), $query->expr->neq($this->handler->quoteColumn('depth', 'ezcontentobject_tree'), $query->bindValue(0, null, PDO::PARAM_INT))); $statement = $query->prepare(); $statement->execute(); $res = $statement->fetchAll(PDO::FETCH_ASSOC); return (int) $res[0]['count']; }
/** * Update object count for words (legacy db table: ezsearch_word). * * @param array $wordId list of word IDs * @param array $columns map of columns and values to be updated ([column => value]) */ private function updateWordObjectCount(array $wordId, array $columns) { $query = $this->dbHandler->createUpdateQuery(); $query->update($this->dbHandler->quoteTable('ezsearch_word')); foreach ($columns as $column => $value) { $query->set($this->dbHandler->quoteColumn($column), $value); } $query->where($query->expr->in($this->dbHandler->quoteColumn('id'), $wordId)); $stmt = $query->prepare(); $stmt->execute(); }
/** * @param mixed[] $destinationContentIds * * @throws \Exception * * @return array */ protected function getRelationXmlHashFromDB(array $destinationContentIds) { if (empty($destinationContentIds)) { return array(); } $q = $this->db->createSelectQuery(); $q->select($this->db->aliasedColumn($q, 'id', 'ezcontentobject'), $this->db->aliasedColumn($q, 'remote_id', 'ezcontentobject'), $this->db->aliasedColumn($q, 'current_version', 'ezcontentobject'), $this->db->aliasedColumn($q, 'contentclass_id', 'ezcontentobject'), $this->db->aliasedColumn($q, 'node_id', 'ezcontentobject_tree'), $this->db->aliasedColumn($q, 'parent_node_id', 'ezcontentobject_tree'), $this->db->aliasedColumn($q, 'identifier', 'ezcontentclass'))->from($this->db->quoteTable('ezcontentobject'))->leftJoin($this->db->quoteTable('ezcontentobject_tree'), $q->expr->lAnd($q->expr->eq($this->db->quoteColumn('contentobject_id', 'ezcontentobject_tree'), $this->db->quoteColumn('id', 'ezcontentobject')), $q->expr->eq($this->db->quoteColumn('node_id', 'ezcontentobject_tree'), $this->db->quoteColumn('main_node_id', 'ezcontentobject_tree'))))->leftJoin($this->db->quoteTable('ezcontentclass'), $q->expr->lAnd($q->expr->eq($this->db->quoteColumn('id', 'ezcontentclass'), $this->db->quoteColumn('contentclass_id', 'ezcontentobject')), $q->expr->eq($this->db->quoteColumn('version', 'ezcontentclass'), $q->bindValue(ContentType::STATUS_DEFINED, null, PDO::PARAM_INT))))->where($q->expr->in($this->db->quoteColumn('id', 'ezcontentobject'), $destinationContentIds)); $stmt = $q->prepare(); $stmt->execute(); return $stmt->fetchAll(PDO::FETCH_ASSOC | PDO::FETCH_GROUP); }
/** * Loads all autogenerated entries with given $parentId with optionally included history entries. * * @param mixed $parentId * @param boolean $includeHistory * * @return array */ public function loadAutogeneratedEntries($parentId, $includeHistory = false) { /** @var $query \eZ\Publish\Core\Persistence\Database\SelectQuery */ $query = $this->dbHandler->createSelectQuery(); $query->select("*")->from($this->dbHandler->quoteTable("ezurlalias_ml"))->where($query->expr->lAnd($query->expr->eq($this->dbHandler->quoteColumn("parent"), $query->bindValue($parentId, null, \PDO::PARAM_INT)), $query->expr->eq($this->dbHandler->quoteColumn("action_type"), $query->bindValue("eznode", null, \PDO::PARAM_STR)), $query->expr->eq($this->dbHandler->quoteColumn("is_alias"), $query->bindValue(0, null, \PDO::PARAM_INT)))); if (!$includeHistory) { $query->where($query->expr->eq($this->dbHandler->quoteColumn("is_original"), $query->bindValue(1, null, \PDO::PARAM_INT))); } $statement = $query->prepare(); $statement->execute(); return $statement->fetchAll(\PDO::FETCH_ASSOC); }
/** * Returns field mapping data * * Returns an associative array with ContentType and FieldDefinition identifiers as * first and second level keys respectively, and FieldDefinition ID as value. * * @return array */ public function getFieldMap() { $query = $this->dbHandler->createSelectQuery(); $query->select($this->dbHandler->alias($this->dbHandler->quoteColumn("id", "ezcontentclass_attribute"), $this->dbHandler->quoteIdentifier("field_id")), $this->dbHandler->alias($this->dbHandler->quoteColumn("identifier", "ezcontentclass_attribute"), $this->dbHandler->quoteIdentifier("field_identifier")), $this->dbHandler->alias($this->dbHandler->quoteColumn("identifier", "ezcontentclass"), $this->dbHandler->quoteIdentifier("type_identifier")))->from($this->dbHandler->quoteTable("ezcontentclass_attribute"))->innerJoin($this->dbHandler->quoteTable("ezcontentclass"), $query->expr->eq($this->dbHandler->quoteColumn("contentclass_id", "ezcontentclass_attribute"), $this->dbHandler->quoteColumn("id", "ezcontentclass"))); $statement = $query->prepare($query); $statement->execute(); $map = array(); $rows = $statement->fetchAll(\PDO::FETCH_ASSOC); foreach ($rows as $row) { $map[$row["type_identifier"]][$row["field_identifier"]] = $row["field_id"]; } return $map; }
/** * Returns total results count for $criterion and $sortClauses. * * @param \eZ\Publish\API\Repository\Values\Content\Query\Criterion $criterion * @param array $languageFilter * * @return array */ protected function getTotalCount(Criterion $criterion, array $languageFilter) { $query = $this->handler->createSelectQuery(); $query->select($query->alias($query->expr->count('*'), 'count'))->from($this->handler->quoteTable('ezcontentobject_tree'))->innerJoin('ezcontentobject', 'ezcontentobject_tree.contentobject_id', 'ezcontentobject.id')->innerJoin('ezcontentobject_version', 'ezcontentobject.id', 'ezcontentobject_version.contentobject_id'); $query->where($this->criteriaConverter->convertCriteria($query, $criterion, $languageFilter), $query->expr->eq('ezcontentobject.status', $query->bindValue(1, null, PDO::PARAM_INT)), $query->expr->eq('ezcontentobject_version.status', $query->bindValue(1, null, PDO::PARAM_INT)), $query->expr->neq($this->handler->quoteColumn('depth', 'ezcontentobject_tree'), $query->bindValue(0, null, PDO::PARAM_INT))); // If not main-languages query if (!empty($languageFilter['languages'])) { $query->where($query->expr->gt($query->expr->bitAnd($this->handler->quoteColumn('language_mask', 'ezcontentobject'), $query->bindValue($this->getLanguageMask($languageFilter), null, PDO::PARAM_INT)), $query->bindValue(0, null, PDO::PARAM_INT))); } $statement = $query->prepare(); $statement->execute(); return (int) $statement->fetchColumn(); }
/** * Generate query expression for a Criterion this handler accepts * * accept() must be called before calling this method. * * @throws \eZ\Publish\API\Repository\Exceptions\InvalidArgumentException If no searchable fields are found for the given criterion target. * @throws \RuntimeException If given criterion operator is not handled * * @param \eZ\Publish\Core\Persistence\Legacy\Content\Search\Common\Gateway\CriteriaConverter $converter * @param \eZ\Publish\Core\Persistence\Database\SelectQuery $query * @param \eZ\Publish\API\Repository\Values\Content\Query\Criterion $criterion * * @return \eZ\Publish\Core\Persistence\Database\Expression */ public function handle(CriteriaConverter $converter, SelectQuery $query, Criterion $criterion) { $this->checkSearchableFields($criterion->target); $subSelect = $query->subSelect(); /** @var \eZ\Publish\API\Repository\Values\Content\Query\Criterion\Value\MapLocationValue $location */ $location = $criterion->valueData; /** * Note: this formula is precise only for short distances. * @todo if ABS function was available in Zeta Database component it should be possible to account for * distances across the date line. Revisit when Doctrine DBAL is introduced. */ $longitudeCorrectionByLatitude = pow(cos(deg2rad($location->latitude)), 2); $distanceExpression = $subSelect->expr->add($subSelect->expr->mul($subSelect->expr->sub($this->dbHandler->quoteColumn("latitude", "ezgmaplocation"), $subSelect->bindValue($location->latitude)), $subSelect->expr->sub($this->dbHandler->quoteColumn("latitude", "ezgmaplocation"), $subSelect->bindValue($location->latitude))), $subSelect->expr->mul($subSelect->expr->sub($this->dbHandler->quoteColumn("longitude", "ezgmaplocation"), $subSelect->bindValue($location->longitude)), $subSelect->expr->sub($this->dbHandler->quoteColumn("longitude", "ezgmaplocation"), $subSelect->bindValue($location->longitude)), $subSelect->bindValue($longitudeCorrectionByLatitude))); switch ($criterion->operator) { case Criterion\Operator::IN: case Criterion\Operator::EQ: case Criterion\Operator::GT: case Criterion\Operator::GTE: case Criterion\Operator::LT: case Criterion\Operator::LTE: $operatorFunction = $this->comparatorMap[$criterion->operator]; $distanceInDegrees = pow($this->kilometersToDegrees($criterion->value), 2); $distanceFilter = $subSelect->expr->{$operatorFunction}($distanceExpression, $subSelect->expr->round($subSelect->bindValue($distanceInDegrees), 10)); break; case Criterion\Operator::BETWEEN: $distanceInDegrees1 = pow($this->kilometersToDegrees($criterion->value[0]), 2); $distanceInDegrees2 = pow($this->kilometersToDegrees($criterion->value[1]), 2); $distanceFilter = $subSelect->expr->between($distanceExpression, $subSelect->expr->round($subSelect->bindValue($distanceInDegrees1), 10), $subSelect->expr->round($subSelect->bindValue($distanceInDegrees2), 10)); break; default: throw new RuntimeException('Unknown operator.'); } // Calculate bounding box if possible // @todo consider covering operators EQ and IN as well $boundingConstraints = array(); switch ($criterion->operator) { case Criterion\Operator::LT: case Criterion\Operator::LTE: $distanceUpper = $criterion->value; break; case Criterion\Operator::BETWEEN: $distanceUpper = $criterion->value[0] > $criterion->value[1] ? $criterion->value[0] : $criterion->value[1]; break; } if (isset($distanceUpper)) { $boundingConstraints = $this->getBoundingConstraints($subSelect, $location, $distanceUpper); } $subSelect->select($this->dbHandler->quoteColumn('contentobject_id'))->from($this->dbHandler->quoteTable('ezcontentobject_attribute'))->innerJoin($this->dbHandler->quoteTable("ezgmaplocation"), $subSelect->expr->lAnd(array($subSelect->expr->eq($this->dbHandler->quoteColumn("contentobject_version", "ezgmaplocation"), $this->dbHandler->quoteColumn("version", "ezcontentobject_attribute")), $subSelect->expr->eq($this->dbHandler->quoteColumn("contentobject_attribute_id", "ezgmaplocation"), $this->dbHandler->quoteColumn("id", "ezcontentobject_attribute"))), $boundingConstraints))->where($subSelect->expr->lAnd($subSelect->expr->eq($this->dbHandler->quoteColumn('version', 'ezcontentobject_attribute'), $this->dbHandler->quoteColumn('current_version', 'ezcontentobject')), $distanceFilter)); return $query->expr->in($this->dbHandler->quoteColumn('id', 'ezcontentobject'), $subSelect); }
/** * Changes main location of content identified by given $contentId to location identified by given $locationId. * * Updates ezcontentobject_tree table for the given $contentId and eznode_assignment table for the given * $contentId, $parentLocationId and $versionNo * * @param mixed $contentId * @param mixed $locationId * @param mixed $versionNo version number, needed to update eznode_assignment table * @param mixed $parentLocationId parent location of location identified by $locationId, needed to update * eznode_assignment table */ public function changeMainLocation($contentId, $locationId, $versionNo, $parentLocationId) { // Update ezcontentobject_tree table $q = $this->handler->createUpdateQuery(); $q->update($this->handler->quoteTable('ezcontentobject_tree'))->set($this->handler->quoteColumn('main_node_id'), $q->bindValue($locationId, null, \PDO::PARAM_INT))->where($q->expr->eq($this->handler->quoteColumn('contentobject_id'), $q->bindValue($contentId, null, \PDO::PARAM_INT))); $q->prepare()->execute(); // Erase is_main in eznode_assignment table $q = $this->handler->createUpdateQuery(); $q->update($this->handler->quoteTable('eznode_assignment'))->set($this->handler->quoteColumn('is_main'), $q->bindValue(0, null, \PDO::PARAM_INT))->where($q->expr->lAnd($q->expr->eq($this->handler->quoteColumn('contentobject_id'), $q->bindValue($contentId, null, \PDO::PARAM_INT)), $q->expr->eq($this->handler->quoteColumn('contentobject_version'), $q->bindValue($versionNo, null, \PDO::PARAM_INT)), $q->expr->neq($this->handler->quoteColumn('parent_node'), $q->bindValue($parentLocationId, null, \PDO::PARAM_INT)))); $q->prepare()->execute(); // Set new is_main in eznode_assignment table $q = $this->handler->createUpdateQuery(); $q->update($this->handler->quoteTable('eznode_assignment'))->set($this->handler->quoteColumn('is_main'), $q->bindValue(1, null, \PDO::PARAM_INT))->where($q->expr->lAnd($q->expr->eq($this->handler->quoteColumn('contentobject_id'), $q->bindValue($contentId, null, \PDO::PARAM_INT)), $q->expr->eq($this->handler->quoteColumn('contentobject_version'), $q->bindValue($versionNo, null, \PDO::PARAM_INT)), $q->expr->eq($this->handler->quoteColumn('parent_node'), $q->bindValue($parentLocationId, null, \PDO::PARAM_INT)))); $q->prepare()->execute(); }
/** * Publishes the Type with $typeId from $sourceVersion to $targetVersion, * including its fields * * @param int $typeId * @param int $sourceVersion * @param int $targetVersion * * @return void */ public function publishTypeAndFields($typeId, $sourceVersion, $targetVersion) { $query = $this->dbHandler->createUpdateQuery(); $query->update($this->dbHandler->quoteTable('ezcontentclass'))->set($this->dbHandler->quoteColumn('version'), $query->bindValue($targetVersion, null, \PDO::PARAM_INT))->where($query->expr->lAnd($query->expr->eq($this->dbHandler->quoteColumn('id'), $query->bindValue($typeId, null, \PDO::PARAM_INT)), $query->expr->eq($this->dbHandler->quoteColumn('version'), $query->bindValue($sourceVersion, null, \PDO::PARAM_INT)))); $query->prepare()->execute(); $query = $this->dbHandler->createUpdateQuery(); $query->update($this->dbHandler->quoteTable('ezcontentclass_classgroup'))->set($this->dbHandler->quoteColumn('contentclass_version'), $query->bindValue($targetVersion, null, \PDO::PARAM_INT))->where($query->expr->lAnd($query->expr->eq($this->dbHandler->quoteColumn('contentclass_id'), $query->bindValue($typeId, null, \PDO::PARAM_INT)), $query->expr->eq($this->dbHandler->quoteColumn('contentclass_version'), $query->bindValue($sourceVersion, null, \PDO::PARAM_INT)))); $query->prepare()->execute(); $query = $this->dbHandler->createUpdateQuery(); $query->update($this->dbHandler->quoteTable('ezcontentclass_attribute'))->set($this->dbHandler->quoteColumn('version'), $query->bindValue($targetVersion, null, \PDO::PARAM_INT))->where($query->expr->lAnd($query->expr->eq($this->dbHandler->quoteColumn('contentclass_id'), $query->bindValue($typeId, null, \PDO::PARAM_INT)), $query->expr->eq($this->dbHandler->quoteColumn('version'), $query->bindValue($sourceVersion, null, \PDO::PARAM_INT)))); $query->prepare()->execute(); $query = $this->dbHandler->createUpdateQuery(); $query->update($this->dbHandler->quoteTable('ezcontentclass_name'))->set($this->dbHandler->quoteColumn('contentclass_version'), $query->bindValue($targetVersion, null, \PDO::PARAM_INT))->where($query->expr->lAnd($query->expr->eq($this->dbHandler->quoteColumn('contentclass_id'), $query->bindValue($typeId, null, \PDO::PARAM_INT)), $query->expr->eq($this->dbHandler->quoteColumn('contentclass_version'), $query->bindValue($sourceVersion, null, \PDO::PARAM_INT)))); $query->prepare()->execute(); }
/** * Generate query expression for a Criterion this handler accepts * * accept() must be called before calling this method. * * @throws \eZ\Publish\API\Repository\Exceptions\NotImplementedException If no searchable fields are found for the given criterion target. * * @param \eZ\Publish\Core\Search\Legacy\Content\Common\Gateway\CriteriaConverter $converter * @param \eZ\Publish\Core\Persistence\Database\SelectQuery $query * @param \eZ\Publish\API\Repository\Values\Content\Query\Criterion $criterion * * @return \eZ\Publish\Core\Persistence\Database\Expression */ public function handle(CriteriaConverter $converter, SelectQuery $query, Criterion $criterion) { $fieldsInformation = $this->getFieldsInformation($criterion->target); $subSelect = $query->subSelect(); $subSelect->select($this->dbHandler->quoteColumn('contentobject_id'))->from($this->dbHandler->quoteTable('ezcontentobject_attribute')); $whereExpressions = array(); foreach ($fieldsInformation as $fieldTypeIdentifier => $fieldsInfo) { if ($fieldsInfo['column'] === false) { throw new NotImplementedException("A field of type '{$fieldTypeIdentifier}' is not searchable in the legacy search engine."); } $filter = $this->fieldValueConverter->convertCriteria($fieldTypeIdentifier, $subSelect, $criterion, $fieldsInfo['column']); $whereExpressions[] = $subSelect->expr->lAnd($subSelect->expr->in($this->dbHandler->quoteColumn('contentclassattribute_id'), $fieldsInfo['ids']), $filter); } $subSelect->where($subSelect->expr->lAnd($subSelect->expr->eq($this->dbHandler->quoteColumn('version', 'ezcontentobject_attribute'), $this->dbHandler->quoteColumn('current_version', 'ezcontentobject')), count($whereExpressions) > 1 ? $subSelect->expr->lOr($whereExpressions) : $whereExpressions[0])); return $query->expr->in($this->dbHandler->quoteColumn('id', 'ezcontentobject'), $subSelect); }
/** * @param mixed[] $destinationContentIds * * @throws \Exception * * @return array */ protected function getRelationXmlHashFromDB(array $destinationContentIds) { if (empty($destinationContentIds)) { return array(); } $q = $this->db->createSelectQuery(); $q->select($this->db->aliasedColumn($q, 'id', 'ezcontentobject'), $this->db->aliasedColumn($q, 'remote_id', 'ezcontentobject'), $this->db->aliasedColumn($q, 'current_version', 'ezcontentobject'), $this->db->aliasedColumn($q, 'contentclass_id', 'ezcontentobject'), $this->db->aliasedColumn($q, 'node_id', 'ezcontentobject_tree'), $this->db->aliasedColumn($q, 'parent_node_id', 'ezcontentobject_tree'), $this->db->aliasedColumn($q, 'identifier', 'ezcontentclass'))->from($this->db->quoteTable('ezcontentobject'))->leftJoin($this->db->quoteTable('ezcontentobject_tree'), $q->expr->lAnd($q->expr->eq($this->db->quoteColumn('contentobject_id', 'ezcontentobject_tree'), $this->db->quoteColumn('id', 'ezcontentobject')), $q->expr->eq($this->db->quoteColumn('node_id', 'ezcontentobject_tree'), $this->db->quoteColumn('main_node_id', 'ezcontentobject_tree'))))->leftJoin($this->db->quoteTable('ezcontentclass'), $q->expr->lAnd($q->expr->eq($this->db->quoteColumn('id', 'ezcontentclass'), $this->db->quoteColumn('contentclass_id', 'ezcontentobject')), $q->expr->eq($this->db->quoteColumn('version', 'ezcontentclass'), $q->bindValue(ContentType::STATUS_DEFINED, null, PDO::PARAM_INT))))->where($q->expr->in($this->db->quoteColumn('id', 'ezcontentobject'), $destinationContentIds)); $stmt = $q->prepare(); $stmt->execute(); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC | PDO::FETCH_GROUP); if (empty($rows)) { throw new \Exception("Could find Content with id's" . var_export($destinationContentIds, true)); } elseif (count($rows) !== count($destinationContentIds)) { throw new \Exception('Miss match of rows & id count:' . var_export($destinationContentIds, true)); } return $rows; }
/** * Remove role from user or user group * * @param mixed $contentId * @param mixed $roleId */ public function removeRole( $contentId, $roleId ) { $query = $this->handler->createDeleteQuery(); $query ->deleteFrom( $this->handler->quoteTable( 'ezuser_role' ) ) ->where( $query->expr->lAnd( $query->expr->eq( $this->handler->quoteColumn( 'contentobject_id' ), $query->bindValue( $contentId, null, \PDO::PARAM_INT ) ), $query->expr->eq( $this->handler->quoteColumn( 'role_id' ), $query->bindValue( $roleId, null, \PDO::PARAM_INT ) ) ) ); $query->prepare()->execute(); }
/** * Get sorted arrays of content IDs, which should be returned * * @param Criterion $filter * @param array $sort * @param mixed $offset * @param mixed $limit * @param mixed $translations * * @return int[] */ protected function getContentIds(Criterion $filter, $sort, $offset, $limit, $translations) { $query = $this->handler->createSelectQuery(); $query->select($this->handler->quoteColumn('id', 'ezcontentobject')); if ($sort !== null) { $this->sortClauseConverter->applySelect($query, $sort); } $query->from($this->handler->quoteTable('ezcontentobject')); $query->innerJoin('ezcontentobject_version', 'ezcontentobject.id', 'ezcontentobject_version.contentobject_id'); if ($sort !== null) { $this->sortClauseConverter->applyJoin($query, $sort); } $query->where($this->getQueryCondition($filter, $query, $translations)); if ($sort !== null) { $this->sortClauseConverter->applyOrderBy($query); } $query->limit($limit, $offset); $statement = $query->prepare(); $statement->execute(); return $statement->fetchAll(\PDO::FETCH_COLUMN); }
/** * Deletes all translations of the $groupId state group * * @param mixed $groupId */ protected function deleteObjectStateGroupTranslations($groupId) { $query = $this->dbHandler->createDeleteQuery(); $query->deleteFrom($this->dbHandler->quoteTable('ezcobj_state_group_language'))->where($query->expr->eq($this->dbHandler->quoteColumn('contentobject_state_group_id'), $query->bindValue($groupId, null, \PDO::PARAM_INT))); $query->prepare()->execute(); }
/** * @param $commentId * @param DatabaseHandler $connection * @return bool */ public function commentExists($commentId, DatabaseHandler $connection) { $selectQuery = $connection->createSelectQuery(); $selectQuery->select('*')->from($connection->quoteTable('ezcomment'))->where($selectQuery->expr->eq($connection->quoteColumn('id'), $selectQuery->bindValue($commentId, null, \PDO::PARAM_INT))); $statement = $selectQuery->prepare(); $statement->execute(); return $statement->rowCount() > 0 ? true : false; }