protected function multiGetFromDB($db, $tableid, $table, NodeRef $nodeRef, $slugs, $asObjects = false, $forceReadWrite = false, $allowDeleted = false) { $results = array(); $cachedSlugs = array(); $rows = array(); list($cachedSlugs, $rows) = $this->NodeCache->getNodes($nodeRef, $slugs, $forceReadWrite); $remainingSlugs = array_diff((array) $slugs, $cachedSlugs); if (!empty($remainingSlugs)) { $q = new Query(); $q->SELECT($tableid); $q->SELECT($tableid . ' as ID'); $q->SELECT($this->NodeDBMeta->getSelectFields()); $q->FROM($db->quoteIdentifier($table)); $q->WHERE("Slug IN ({$db->joinQuote(array_unique((array) $slugs))})"); $dbRows = $this->getConnectionForWrite($nodeRef)->readAll($q); $found = array(); foreach ($dbRows as $row) { if (in_array($row['Slug'], $found)) { $this->getConnectionForWrite($nodeRef)->deleteRecord($db->quoteIdentifier($table), "{$tableid} = {$db->quote($row['ID'])}"); $metaSchemas = $nodeRef->getElement()->getSchema()->getMetaStorageDatatypes(); foreach ($metaSchemas as $datatype) { $mtable = $this->NodeDBMeta->getMetaTable($nodeRef, $datatype); $this->getConnectionForWrite($nodeRef)->deleteRecord($db->quoteIdentifier($mtable), "{$tableid} = {$db->quote($row['ID'])}"); } $otable = $this->NodeDBMeta->getOutTagsTable($nodeRef); $this->getConnectionForWrite($nodeRef)->deleteRecord($db->quoteIdentifier($otable), "{$tableid} = {$db->quote($row['ID'])}"); $itable = $this->NodeDBMeta->getInTagsTable($nodeRef); $this->getConnectionForWrite($nodeRef)->deleteRecord($db->quoteIdentifier($itable), "{$tableid} = {$db->quote($row['ID'])}"); continue; } $found[] = $row['Slug']; $this->NodeCache->putNode($nodeRef, $row, $forceReadWrite); $rows[] = $row; } unset($dbRows); // $unfounds = array_diff($remainingSlugs, $found); // foreach($unfounds as $unfoundSlug) // { // $this->NodeCache->putNode($nodeRef, array('Slug'=> $unfoundSlug, 'NotFound' => true), $forceReadWrite); // } unset($found); // unset($unfounds); unset($remainingSlugs); } foreach ($rows as $row) { if (!empty($row['NotFound'])) { continue; } if (!$allowDeleted && $row['Status'] == 'deleted') { continue; } $rowNodeRef = new NodeRef($nodeRef->getElement(), $row['Slug']); $row['NodeRef'] = $rowNodeRef; $results[(string) $rowNodeRef] = $this->NodeMapper->persistentArrayToNode($row); unset($rowNodeRef); } unset($rows); return $results; }
protected function getVersionNumbers() { if (is_null($this->version)) { $db = $this->getConnection(); $q = new Query(); $q->SELECT('Version', true); $q->SELECT('CFVersion'); $q->FROM('sysversion'); $q->ORDERBY('version desc'); $q->LIMIT(1); $row = $db->readOne($q); if (!empty($row)) { $this->version = $row['Version']; $this->dbCfVersion = $row['CFVersion']; } if (empty($this->dbCfVersion)) { $this->insertSystemVersion('Save CFVersion'); } } return $this; }
protected function findTags($direction, $db, NodeRef $originNodeRef, $ids, $partials = 'fields', $forceReadWrite = false, $checkJumpPermissions = false, $restrictedPartials, $resolveLinkedRecords = true, $existingRows = null) { if (!is_null($db) && !$db instanceof DatabaseInterface) { throw new NodeException('Argument 2 passed to NodeTagsDAO::findTags() must implement interface DatabaseInterface'); } $results = array(); if (empty($partials)) { return $results; } $returnOne = false; if (!is_array($ids)) { $returnOne = $ids; $ids = array($originNodeRef->getRefURL() => $ids); } $fixedId = false; if (!is_null($existingRows)) { $fixedId = 1; $returnOne = 1; } $tagFields = $this->tagFields; $this->Logger->debug('findTags [' . $direction . '] with partials [' . $partials . '] restrict [' . $restrictedPartials . ']'); if ($direction == 'out') { $table = $this->NodeDBMeta->getOutTagsTable($originNodeRef); $partials = PartialUtils::unserializeOutPartials($partials); $restrictedPartials = PartialUtils::unserializeOutPartials($restrictedPartials); } else { $table = $this->NodeDBMeta->getInTagsTable($originNodeRef); $partials = PartialUtils::unserializeInPartials($partials); $restrictedPartials = PartialUtils::unserializeInPartials($restrictedPartials); // $tagFields[] = 'TagOutID'; } // $this->Logger->debug($partials); $tableid = $this->NodeDBMeta->getPrimaryKey($originNodeRef); $now = $this->DateFactory->newLocalDate(); $rows = array(); $fieldlikePartials = array(); $childexprPartials = array(); $addlParams = array(); $jumpParams = array(); $fieldlikeRoles = array(); $nonfieldlikeRoles = array(); $outRoles = array(); $all = false; $allfieldlike = false; $tagDefs = array(); if ($partials == 'all' || ($x = array_search('all', $partials)) !== false) { $all = true; $partials = array(); } else { if (($x = array_search('fields', $partials)) !== false) { $allfieldlike = true; unset($partials[$x]); } } if ($restrictedPartials == 'all' || ($x = array_search('all', $restrictedPartials)) !== false) { return array(); } $tagDefs = $originNodeRef->getElement()->getSchema()->getTagDefs(); foreach ($originNodeRef->getElement()->getSchema()->getTagDefs() as $role => $tagDef) { if ($tagDef->isFieldlike() && strtolower($tagDef->Direction) == $direction) { $fieldlikeRoles[] = $role; } if ($tagDef->Direction == 'out') { $outRoles[] = $role; } } foreach ($partials as $x => $outPartial) { if ($outPartial['TagRole'] == false) { throw new TagException('Tag partials must specify a role when retrieving tags'); } if (in_array($outPartial['TagRole'], $fieldlikeRoles)) { $fieldlikePartials[] = $outPartial; unset($partials[$x]); } if ($resolveLinkedRecords && $outPartial->hasChildPartials()) { $childexprPartials[$outPartial['TagRole']][] = $outPartial; } } // tags passed to us, no need to query database if (is_null($existingRows)) { if ($allfieldlike == true || !empty($fieldlikePartials)) { $cachedIds = array(); // read from cache list($cachedIds, $rows) = $this->NodeCache->getTags($direction, $ids, $forceReadWrite); $remainingIds = array_diff($ids, $cachedIds); if (!empty($remainingIds)) { $dbToCacheRows = array(); if (!empty($fieldlikeRoles)) { $q = new Query(); $q->SELECT($tableid . ' as ID'); $q->SELECT($tagFields); // Commented out on 2/1/2010 by Craig .. Mysql seems to be using the right indexes without this. // $q->FROM($db->quoteIdentifier($table).' FORCE INDEX(RoleOnly)'); $q->FROM($db->quoteIdentifier($table)); $q->WHERE("{$tableid} IN ({$db->joinQuote((array) $remainingIds)})"); $q->WHERE("Role IN ({$db->joinQuote((array) $fieldlikeRoles)})"); $dbRows = $this->getConnectionForWrite($originNodeRef)->readAll($q); foreach ($dbRows as $row) { $dbToCacheRows[$row['ID']][] = $row; $rows[] = $row; } } $this->NodeCache->putTags($direction, $remainingIds, $dbToCacheRows, $forceReadWrite); } } if ($all) { $q = new Query(); // if($forceReadWrite) // $q->forUpdate(); $q->SELECT($tableid . ' as ID'); $q->SELECT($tagFields); $q->FROM($db->quoteIdentifier($table)); $q->WHERE("{$tableid} IN ({$db->joinQuote((array) $ids)})"); // $q->WHERE("SectionID = 0"); $rows = $db->readAll($q); } else { $runPartials = array(); // perform a SQL statement per partial foreach ($partials as $outPartial) { if (in_array($outPartial->toString(), $runPartials)) { continue; } if ($outPartial['TagRole'] == false) { throw new TagException('Tag partials must specify a role when retrieving tags'); } if ($direction == 'out' && !in_array($outPartial['TagRole'], $outRoles)) { continue; } $unions = array(); foreach ((array) $ids as $id) { $q = new Query(); $q->SELECT($tableid . ' as ID'); $q->SELECT($tagFields); $q->FROM($db->quoteIdentifier($table)); // $q->WHERE("$tableid IN ({$db->joinQuote((array)$ids)})"); $q->WHERE("{$tableid} = {$db->quote($id)}"); // $q->WHERE("SectionID = 0"); if ($direction == 'out') { $q->WHERE($this->getOutTagPartialClause($outPartial, $db, $db->quoteIdentifier($table))); } else { $q->WHERE($this->getInTagPartialClause($outPartial, $originNodeRef->getElement(), $db, $db->quoteIdentifier($table))); } if (!$forceReadWrite && $this->nodeDatabaseTagMergeLimit > 0) { $q->LIMIT($this->nodeDatabaseTagMergeLimit); } $unions[] = '(' . (string) $q . ')'; } $sql = implode(' UNION ALL ', $unions); $rows = array_merge($rows, $db->readAll($sql)); $nonfieldlikeRoles[] = $outPartial['TagRole']; $runPartials[] = $outPartial->toString(); } } } else { $rows = $existingRows; } // if no rows, return empty array if (empty($rows)) { return $rows; } $sortKeys = array(); foreach ($rows as $k => $row) { $sortKeys[$k] = $row['TagSortOrder']; } array_multisort($sortKeys, SORT_ASC, $rows); // gather linked NodeRefs $tagNodeRefs = array(); // $strTagNodeRefs = array(); // $this->Logger->debug('FIELD LIKE PARTIALS'); // $this->Logger->debug($fieldlikePartials); // $this->Logger->debug('NON FIELD LIKE PARTIALS'); // $this->Logger->debug($partials); $existingTagsPerNode = array(); $newrows = array(); $deletedTags = array(); // filter out bogus rows foreach ($rows as $k => $row) { // $rowElement = $this->ElementService->getByID($row['TagElementID']); if (!empty($row['TagElement'])) { $rowElement = $this->ElementService->getBySlug($row['TagElement']); } else { $rowElement = $this->ElementService->getByID($row['TagElementID']); } // element doesn't exist anymore if (empty($rowElement)) { continue; } // error_log($nodeRef); if (!array_key_exists($row['TagRole'], $tagDefs)) { if ($direction == 'in' && $rowElement->getSchema()->hasTagDef($row['TagRole'])) { $row['TagDef'] = $rowElement->getSchema()->getTagDef($row['TagRole']); } else { continue; } // tag definition does not exist } else { $row['TagDef'] = $tagDefs[$row['TagRole']]; } $row['TagElement'] = $rowElement->getSlug(); $row['TagRoleDisplay'] = $row['TagDef']['Title']; $row['TagDirection'] = $direction; //$row['TagLinkRefURL'] = $nodeRef->getRefURL(); // $row['TagSite'] = $nodeRef->getSite()->getSlug(); if (!empty($restrictedPartials)) { foreach ($restrictedPartials as $partial) { if ($this->TagsHelper->matchPartial($partial, $row)) { continue 2; } } } if ($all == false && $allfieldlike == false && !empty($fieldlikePartials) && in_array($row['TagRole'], $fieldlikeRoles)) { // error_log('matching fieldlike'); // error_log(print_r($fieldlikePartials, true)); $found = false; foreach ($fieldlikePartials as $partial) { // error_log('test '.$partial->toString()); if ($this->TagsHelper->matchPartial($partial, $row)) { // error_log('match'); $found = true; break; } } if (!$found) { continue; } } if ($all == false && !empty($partials) && in_array($row['TagRole'], $nonfieldlikeRoles)) { // error_log('matching fieldlike'); $found = false; foreach ($partials as $partial) { // error_log('test '.$partial->toString()); if ($this->TagsHelper->matchPartial($partial, $row)) { // error_log('match'); $found = true; break; } } if (!$found) { continue; } } $nodeRef = new NodeRef($rowElement, $row['TagSlug']); $row['TagLinkNodeRef'] = $nodeRef; $row['NoValidation'] = true; $tag = new Tag($row); if (isset($existingTagsPerNode[$row['ID']]) && array_key_exists($tag->toString(), $existingTagsPerNode[$row['ID']])) { $tagIDToDelete = $existingTagsPerNode[$row['ID']][$tag->toString()]; if ($tagIDToDelete != $tag->TagID) { if ($tag->TagID > $tagIDToDelete) { $tagIDToDelete = $tag->TagID; // If the current tag is being deleted, mark it as deleted so that we can skip the rest of // the processing once it's deleted from the db. $tag->Deleted = true; } else { // We're deleting a tag that was already added to the results array. Therefore just keep track of // it so that we can remove it from the results array later. if ($fixedId) { $deletedTags[$fixedId][] = $tagIDToDelete; } else { $deletedTags[$row['ID']][] = $tagIDToDelete; } } // read repair $this->Logger->debug("Delete {$tag->getTagDirection()} tag: {$tag->toString()}"); $affectedRows = $this->getConnectionForWrite($originNodeRef)->write("DELETE FROM {$db->quoteIdentifier($table)} WHERE TagID = {$db->quote($tagIDToDelete)}"); if ($affectedRows > 0) { $this->NodeEvents->fireTagEvents($direction . 'tags', 'remove', $originNodeRef, $nodeRef, $tag); } // If the current tag is the one that got deleted, we're done processing this tag and can move to // the next. This tag will not get added to the results. if ($tag->Deleted) { continue; } } } $existingTagsPerNode[$row['ID']][$tag->toString()] = $tag->TagID; if ($resolveLinkedRecords) { $tagNodeRefs[] = $nodeRef; // $strTagNodeRefs[$k] = (string)$nodeRef; // if need to merge more tags in if (array_key_exists($row['TagRole'], $childexprPartials)) { // TODO: check partial matches $rowRefKey = $rowElement->getSlug(); $outPartials = $childexprPartials[$row['TagRole']]; foreach ($outPartials as $outPartial) { $expressions = StringUtils::smartSplit($outPartial->getChildPartials(), ".", '"', '\\"', 2); $firstExpression = array_shift($expressions); if (strtolower($firstExpression) == 'meta') { $addlParams[$rowRefKey]['Meta.select'][] = 'all'; } elseif (strtolower($firstExpression) == 'fields') { $addlParams[$rowRefKey]['Meta.select'][] = 'all'; $addlParams[$rowRefKey]['OutTags.select'][] = 'fields'; $addlParams[$rowRefKey]['InTags.select'][] = 'fields'; } else { unset($schemaDef); $isTag = true; try { $jPartial = new TagPartial($firstExpression); $role = $jPartial->getTagRole(); if ($nodeRef->getElement()->getSchema()->hasTagDef($role)) { $schemaDef = $nodeRef->getElement()->getSchema()->getTagDef($role); } else { if ($nodeRef->getElement()->getSchema()->hasMetaDef($role)) { $jPartial = new MetaPartial($firstExpression); $schemaDef = $nodeRef->getElement()->getSchema()->getMetaDef($role); $isTag = false; } } } catch (Exception $e) { $jPartial = new MetaPartial($firstExpression); $role = $jPartial->getMetaName(); if ($nodeRef->getElement()->getSchema()->hasMetaDef($role)) { $schemaDef = $nodeRef->getElement()->getSchema()->getMetaDef($role); } $isTag = false; } if (empty($schemaDef)) { continue; } //throw new NodeException('Cannot retrieve additional nodes for role ['.$role.'], no schema def found on element ['.$rowElement->getSlug().']'); if ($isTag) { $newPartials = $schemaDef->getDirection() == 'out' ? 'OutTags.select' : 'InTags.select'; $jPartial = $jPartial->getTagRole(); } else { $newPartials = 'Meta.select'; $jPartial = $jPartial->getMetaName(); } $new = $firstExpression; if (!empty($expressions[0])) { $new .= '.' . $expressions[0]; } $addlParams[$rowRefKey][$newPartials][] = $new; $jumpParams[$row['TagRole']][$newPartials][] = $jPartial; } } } $newrows[] = $tag; } else { if ($fixedId) { $results[$fixedId][] = $row; } else { $results[$row['ID']][] = $tag; } } } if ($resolveLinkedRecords) { // error_log('PRE NODE RETRIEVE'); // find the corresponding records $nodeRows = array(); $connectionCouplets = $this->getResolvedConnectionCouplets($tagNodeRefs, $forceReadWrite); foreach ($connectionCouplets as $connectionCouplet) { $db = $connectionCouplet->getConnection(); $tableToSlugs = $connectionCouplet->getAttribute('tablesToSlugs'); foreach ($tableToSlugs as $table => $tableInfo) { extract($tableInfo); $rowRefKey = $element->getSlug(); $partialJump = false; $newNodePartials = null; if (array_key_exists($rowRefKey, $addlParams)) { $partialJump = true; $addl = $addlParams[$rowRefKey]; $newNodePartials = new NodePartials(); if (array_key_exists('OutTags.select', $addl)) { $newNodePartials->setOutPartials(implode(',', array_unique($addl['OutTags.select']))); } if (array_key_exists('InTags.select', $addl)) { $newNodePartials->setInPartials(implode(',', array_unique($addl['InTags.select']))); } if (array_key_exists('Meta.select', $addl)) { $newNodePartials->setMetaPartials(implode(',', array_unique($addl['Meta.select']))); } } if ($checkJumpPermissions && !$this->NodePermissions->check('get', $tableNodeRef, $newNodePartials, true)) { continue; } $foundRows = $this->multiGetFromDB($db, $tableid, $table, $tableNodeRef, $slugs, false, $forceReadWrite, false); if ($partialJump && !empty($foundRows)) { $this->Logger->debug('Partials jump on ' . $tableNodeRef->getElement()->getName()); $aoutTags = array(); $ainTags = array(); $ameta = array(); $this->Benchmark->start('partial-jump'); $idField = 'ID'; $pids = ArrayUtils::arrayMultiColumn($foundRows, $idField); if ($newNodePartials->hasOutPartials()) { $aoutTags = $this->findOutTags($db, $tableNodeRef, $pids, $newNodePartials->getOutPartials(), false, false, $checkJumpPermissions); } if ($newNodePartials->hasInPartials()) { $ainTags = $this->findInTags($db, $tableNodeRef, $pids, $newNodePartials->getInPartials(), false, false, $checkJumpPermissions); } if ($newNodePartials->hasMetaPartials()) { $ameta = $this->NodeMetaDAO->findMeta($db, $tableNodeRef, $pids, $newNodePartials->getMetaPartials(), false); } foreach ($foundRows as $nodeRefString => &$nrow) { $nrow->setNodePartials($newNodePartials); $nrow->setMetas(isset($ameta[$nrow[$idField]]) ? $ameta[$nrow[$idField]] : array()); $nrow->setOutTags(isset($aoutTags[$nrow[$idField]]) ? $aoutTags[$nrow[$idField]] : array()); $nrow->setInTags(isset($ainTags[$nrow[$idField]]) ? $ainTags[$nrow[$idField]] : array()); } $this->Benchmark->end('partial-jump'); } $nodeRows = array_merge($nodeRows, $foundRows); } } foreach ($newrows as $row) { // $id = $row['ID']; // unset($row['ID']); // error_log(print_r($row, true)); if ($resolveLinkedRecords) { $nodeRef = $row['TagLinkNodeRef']; if (!array_key_exists((string) $nodeRef, $nodeRows)) { continue; } $tagNode = $nodeRows[(string) $nodeRef]; // if need to filter out jump partials if (array_key_exists($row['TagRole'], $jumpParams)) { $outPartials = $jumpParams[$row['TagRole']]; foreach ($outPartials as $key => $kPartials) { if ($key == 'Meta.select') { foreach ($tagNode->getMetas() as $meta) { if (!in_array($meta->MetaName, $kPartials)) { $tagNode->removeMeta($meta->MetaName); } } } else { if ($key == 'InTags.select') { foreach ($tagNode->getInTags() as $tag) { if (!in_array($tag->TagRole, $kPartials)) { $tagNode->removeInTags($tag->TagRole); } } } else { if ($key == 'OutTags.select') { foreach ($tagNode->getOutTags() as $tag) { if (!in_array($tag->TagRole, $kPartials)) { $tagNode->removeOutTags($tag->TagRole); } } } } } } } // ignore deleted records //if($tagNode['Status'] == 'deleted') // continue; $row['TagLinkNode'] = $tagNode; $row['TagLinkTitle'] = $tagNode['Title']; $row['TagLinkID'] = $tagNode['ID']; $row['TagLinkStatus'] = $tagNode['Status']; $row['TagLinkActiveDate'] = $tagNode['ActiveDate']; $row['TagLinkSortOrder'] = $tagNode['SortOrder']; // only populate TagLinkURL if the record is active if ($tagNode['Status'] == 'published' && $tagNode['ActiveDateUnix'] < $now->toUnix()) { $row['TagLinkIsActive'] = true; $row['TagLinkURL'] = $nodeRef->getRecordLink(); $row['TagLinkURI'] = $nodeRef->getRecordLinkURI(); } else { $row['TagLinkURL'] = ''; } $row['TagLinkURI'] = ''; } if ($fixedId) { $results[$fixedId][] = $row; } else { $results[$row['ID']][] = $row; } } } // If tags were deleted during the 'read repair' they need to be removed from the results array // Go through the nodes that have had tags deleted. If those nodes exist in the results array, go // through the tags being returned. If the tag exists in the 'deleted' array for the node, remove it from // the result set. if (!empty($deletedTags)) { foreach ($deletedTags as $rowId => $rowDeletedTags) { if (isset($results[$rowId]) && !empty($results[$rowId])) { foreach ($results[$rowId] as $resultKey => $rowResults) { if (in_array($rowResults->TagID, $rowDeletedTags)) { unset($results[$rowId][$resultKey]); } } } } } if ($returnOne) { return isset($results[$returnOne]) ? $results[$returnOne] : array(); } return $results; }
public function buildMySQLQuery(DatabaseInterface $db, $tableNodeRef, $table, $tableid, NodeQuery $nodeQuery, $orderObjects, $slugs, $ids = null) { $table = $db->quoteIdentifier($table); $element = $tableNodeRef->getElement(); $schema = $element->getSchema(); $q = new Query(); $q->SELECT('DISTINCT ' . $table . '.' . $tableid . ' as ID', true); $q->SELECT($table . '.Slug'); $mTableC = 1; if ($nodeQuery->hasParameter('OrderByInTag')) { $inParts = explode(' ', $nodeQuery->getParameter('OrderByInTag'), 2); $partials = PartialUtils::unserializeInPartials($inParts[0]); foreach ($partials as $partial) { if ($partial->getTagElement() && $partial->getTagSlug() && $partial->getTagRole()) { $tTable = $db->quoteIdentifier($this->NodeDBMeta->getInTagsTable($tableNodeRef)); $tClause = $this->NodeTagsDAO->getOutTagPartialClause($partial, $db, $tTable); $q->SELECT("OCondTable" . $mTableC . ".SortOrder as OSortOrder{$mTableC}"); $q->JOIN("INNER JOIN {$tTable} as OCondTable{$mTableC} " . str_replace($tTable, 'OCondTable' . $mTableC, " ON {$table}.{$tableid} = {$tTable}.{$tableid} AND {$tClause}")); $direction = array_key_exists(1, $inParts) ? $inParts[1] : 'ASC'; $q->ORDERBY('OCondTable' . $mTableC . '.SortOrder ' . $direction); $mTableC++; } else { throw new NodeException('Invalid OrderByInTag parameter, must be fully qualified TagPartial with element:slug#role'); } } } if ($nodeQuery->hasParameter('OrderByOutTag')) { $outParts = explode(' ', $nodeQuery->getParameter('OrderByOutTag'), 2); $partials = PartialUtils::unserializeOutPartials($outParts[0]); foreach ($partials as $partial) { if ($partial->getTagElement() && $partial->getTagSlug() && $partial->getTagRole()) { $tTable = $db->quoteIdentifier($this->NodeDBMeta->getOutTagsTable($tableNodeRef)); $tClause = $this->NodeTagsDAO->getOutTagPartialClause($partial, $db, $tTable); $q->SELECT("OCondTable" . $mTableC . ".SortOrder as OSortOrder{$mTableC}"); $q->JOIN("INNER JOIN {$tTable} as OCondTable{$mTableC} " . str_replace($tTable, 'OCondTable' . $mTableC, " ON {$table}.{$tableid} = {$tTable}.{$tableid} AND {$tClause}")); $direction = array_key_exists(1, $outParts) ? $outParts[1] : 'ASC'; $q->ORDERBY('OCondTable' . $mTableC . '.SortOrder ' . $direction); $mTableC++; } else { throw new NodeException('Invalid OrderByOutTag parameter, must be fully qualified TagPartial with element:slug#role'); } } } $mTableC = 1; foreach ($orderObjects as $orderObject) { $column = $orderObject->getColumn(); $direction = $orderObject->getDirection(); //if(!in_array($column, array('ID', 'Slug'))) //{ if ($orderObject->isMeta()) { $partial = $orderObject->getOrderByMetaPartial(); $datatype = $orderObject->getOrderByMetaDataType(); $mTable = $this->NodeDBMeta->getMetaTable($tableNodeRef, $datatype); $mTableAlias = $db->quoteIdentifier($mTable . $mTableC++); $mTable = $db->quoteIdentifier($mTable); $mClause = $this->NodeMetaDAO->getMetaPartialClause(new MetaPartial($partial), $datatype, $db, $mTableAlias); $q->SELECT($mTableAlias . '.' . $datatype . 'Value as ' . $column); $q->JOIN('LEFT JOIN ' . $mTable . ' as ' . $mTableAlias . ' ON ' . $table . '.' . $tableid . ' = ' . $mTableAlias . '.' . $tableid . ' AND ' . $mClause); $q->ORDERBY("{$column} {$direction}"); } else { if ($orderObject->isDirectional()) { if (!in_array($orderObject->getColumn(), array('Title', 'Slug', 'SortOrder', 'ActiveDate', 'CreationDate', 'ModifiedDate', 'TreeID'))) { throw new NodeException('Invalid ordering column [' . $orderObject->getColumn() . '], must be Title, Slug, SortOrder, ActiveDate, CreationDate, ModifiedDate, TreeID, or #meta-id'); } if ($orderObject->getColumn() != 'Slug') { $q->SELECT($table . '.' . $orderObject->getColumn()); } $q->ORDERBY("{$table}.{$column} {$direction}"); } } //} } // Add if clause 2/1/2010 by Craig .. Mysql seems to be doing it's job without this secondary sort. if (empty($orderObjects)) { $q->ORDERBY('ID DESC'); } $q->FROM($table); // ID if (!empty($ids)) { if (count($ids) > 1) { $q->WHERE("{$table}.{$tableid} IN (" . $db->joinQuote($ids) . ")"); } else { if (isset($ids[0])) { $q->WHERE("{$table}.{$tableid} = {$db->quote($ids[0])}"); } } } // Slug if (!empty($slugs)) { if (count($slugs) > 1) { $q->WHERE("{$table}.Slug IN (" . $db->joinQuote($slugs) . ")"); } else { if (isset($slugs[0])) { $q->WHERE("{$table}.Slug = {$db->quote($slugs[0])}"); } } } // AlphaIndex if (($alpha = $nodeQuery->getParameter('Title.firstChar')) != null) { if (strtolower($alpha) == '#') { $q->WHERE("(ASCII(LOWER(LEFT({$table}.Title, 1))) < 65) OR (ASCII(LOWER(LEFT({$table}.Title, 1))) BETWEEN 90 AND 97) OR (ASCII(LOWER(LEFT({$table}.Title, 1))) > 122)"); } else { $q->WHERE("LOWER(LEFT({$table}.Title, 1)) = {$db->quote(strtolower($alpha))}"); } } // Title $this->DTOHelper->buildEqualsFilter($db, $q, $nodeQuery, 'Title.ieq', "{$table}.Title"); $this->DTOHelper->buildReplaceFilter($db, $q, $nodeQuery, 'Title.eq', "BINARY {$table}.Title = ?"); // TitleSearch $this->DTOHelper->buildReplaceFilter($db, $q, $nodeQuery, 'Title.like', "{$table}.Title LIKE ?", '%#s%'); // ParentTreeID $this->DTOHelper->buildReplaceFilter($db, $q, $nodeQuery, 'TreeID.childOf', "{$table}.TreeID LIKE ?", "#s%"); // TreeID $this->DTOHelper->buildEqualsFilter($db, $q, $nodeQuery, 'TreeID.eq', "{$table}.TreeID"); // select node by Tree depth (or limit existing selection) if (($treeDepth = $nodeQuery->getParameter('TreeID.depth')) !== null) { $actualDepth = $treeDepth * 4; $q->orWhere("LENGTH({$table}.TreeID) = {$db->quote($actualDepth)}"); } if (($treeid = $nodeQuery->getParameter('TreeID.parentOf')) != null) { $depth = strlen($treeid) / 4; if ($depth == 1) { $q->WHERE('1 = 0'); } else { for ($i = 1; $i < $depth; ++$i) { $ptreeid = substr($treeid, 0, $i * 4); $q->ORWHERE("{$table}.TreeID = {$db->quote($ptreeid)}"); } } } // ActiveAfter // ActiveBefore $this->DTOHelper->buildDateReplaceFilter($db, $q, $nodeQuery, 'ActiveDate.after', "{$table}.ActiveDate > ?"); $this->DTOHelper->buildDateReplaceFilter($db, $q, $nodeQuery, 'ActiveDate.before', "{$table}.ActiveDate <= ?"); $this->DTOHelper->buildDateReplaceFilter($db, $q, $nodeQuery, 'ActiveDate.start', "{$table}.ActiveDate >= ?", 0, 0, 0); $this->DTOHelper->buildDateReplaceFilter($db, $q, $nodeQuery, 'ActiveDate.end', "{$table}.ActiveDate <= ?", 23, 59, 59); // CreatedAfter // CreatedBefore $this->DTOHelper->buildDateReplaceFilter($db, $q, $nodeQuery, 'CreationDate.after', "{$table}.CreationDate > ?"); $this->DTOHelper->buildDateReplaceFilter($db, $q, $nodeQuery, 'CreationDate.before', "{$table}.CreationDate <= ?"); $this->DTOHelper->buildDateReplaceFilter($db, $q, $nodeQuery, 'CreationDate.start', "{$table}.CreationDate >= ?", 0, 0, 0); $this->DTOHelper->buildDateReplaceFilter($db, $q, $nodeQuery, 'CreationDate.end', "{$table}.CreationDate <= ?", 23, 59, 59); // Status if (($status = $nodeQuery->getParameter('Status.eq')) != null) { switch ($status) { case 'published': $q->WHERE("{$table}.Status = 'published'"); break; case 'draft': $q->WHERE("{$table}.Status = 'draft'"); break; case 'deleted': $q->WHERE("{$table}.Status = 'deleted'"); break; default: $q->WHERE("{$table}.Status != 'deleted'"); break; } } else { if ($nodeQuery->getParameter('Status.isActive') !== null && StringUtils::strToBool($nodeQuery->getParameter('Status.isActive')) == true) { $now = $this->DateFactory->newStorageDate(); $q->WHERE("{$table}.Status = 'published' AND {$table}.ActiveDate < {$db->quote($now)}"); } else { if ($nodeQuery->getParameter('Status.all') == null || StringUtils::strToBool($nodeQuery->getParameter('Status.all')) == false) { $q->WHERE("{$table}.Status != 'deleted'"); } } } $metaParams = $this->NodesHelper->getMetaFilters($nodeQuery); $tablect = 0; foreach ($metaParams as $mArgs) { list($full, $name, $operator, $value) = $mArgs; $def = $schema->getMetaDef($name); $datatype = $def->Datatype; $mTable = $db->quoteIdentifier($this->NodeDBMeta->getMetaTable($tableNodeRef, $datatype)); $clause = ' ' . $db->quoteIdentifier($mTable) . '.Name = ' . $db->quote($name) . " AND "; if ($datatype == 'flag') { throw new NodeException('Unable to run meta clause on flag datatype'); } if (in_array($datatype, array('text', 'blob', 'mediumtext', 'mediumblob'))) { throw new NodeException('Query arguments with #' . $name . ' are not supported'); } switch ($operator) { case 'eq': if ($datatype == 'varchar') { $clause .= " BINARY {$db->quoteIdentifier($mTable)}.{$datatype}Value = {$db->quote($value)}"; } else { $clause .= " {$db->quoteIdentifier($mTable)}.{$datatype}Value = {$db->quote($value)}"; } break; case 'ieq': $clause .= " {$db->quoteIdentifier($mTable)}.{$datatype}Value = {$db->quote($value)}"; break; case 'like': $clause .= " {$db->quoteIdentifier($mTable)}.{$datatype}Value LIKE " . $db->quote('%' . $value . '%'); break; case 'before': $d = $this->DateFactory->newLocalDate($value); $clause .= " {$db->quoteIdentifier($mTable)}.{$datatype}Value <= {$db->quote($d)}"; break; case 'after': $d = $this->DateFactory->newLocalDate($value); $clause .= " {$db->quoteIdentifier($mTable)}.{$datatype}Value > {$db->quote($d)}"; break; case 'start': $d = $this->DateFactory->newLocalDate($value); $d->setTime(0, 0, 0); $clause .= " {$db->quoteIdentifier($mTable)}.{$datatype}Value >= {$db->quote($d)}"; break; case 'end': $d = $this->DateFactory->newLocalDate($value); $d->setTime(23, 59, 59); $clause .= " {$db->quoteIdentifier($mTable)}.{$datatype}Value <= {$db->quote($d)}"; break; case 'notEq': if ($datatype == 'varchar') { $clause .= " BINARY {$db->quoteIdentifier($mTable)}.{$datatype}Value != {$db->quote($value)}"; } else { $clause .= " {$db->quoteIdentifier($mTable)}.{$datatype}Value != {$db->quote($value)}"; } break; case 'lessThan': $clause .= " {$db->quoteIdentifier($mTable)}.{$datatype}Value < {$db->quote($value)}"; break; case 'lessThanEq': $clause .= " {$db->quoteIdentifier($mTable)}.{$datatype}Value <= {$db->quote($value)}"; break; case 'greaterThan': $clause .= " {$db->quoteIdentifier($mTable)}.{$datatype}Value > {$db->quote($value)}"; break; case 'greaterThanEq': $clause .= " {$db->quoteIdentifier($mTable)}.{$datatype}Value >= {$db->quote($value)}"; break; /* * case insensitive comparison for #meta.in filtering. */ /* * case insensitive comparison for #meta.in filtering. */ case 'in': $inValues = explode(',', $value); if (count($inValues) > 1) { $clause .= " {$db->quoteIdentifier($mTable)}.{$datatype}Value IN ({$db->joinQuote($inValues)})"; } else { if ($datatype == 'varchar') { $clause .= " BINARY {$db->quoteIdentifier($mTable)}.{$datatype}Value = {$db->quote($value)}"; } else { $clause .= " {$db->quoteIdentifier($mTable)}.{$datatype}Value = {$db->quote($value)}"; } } break; } $tablect++; $q->JOIN("INNER JOIN {$mTable} as CondTable{$tablect} " . str_replace($mTable, 'CondTable' . $tablect, " ON {$table}.{$tableid} = {$mTable}.{$tableid} AND {$clause}")); } // IncludesMeta // if(($im = $nodeQuery->getParameter('Meta.exist')) != NULL ) { // $metas = PartialUtils::unserializeMetaPartials($im); // $conditions = array(); // foreach($metas as $partial) { // $s = $schema->getMetaDef($partial->getMetaName()); // // $datatype = $s->Datatype; // // $mTable = $db->quoteIdentifier($this->NodeDBMeta->getMetaTable($tableNodeRef, $datatype)); // $mClause = $this->NodeMetaDAO->getMetaPartialClause($partial, $datatype, $db, $mTable); // // $conditions[] = "{$table}.{$tableid} IN // (SELECT {$mTable}.{$tableid} // FROM {$mTable} // WHERE ".$mClause.")"; //// $conditions[] = "EXISTS (SELECT 1 FROM {$this->model->getMetaTable()} //// WHERE {$this->model->getMetaTable()}.{$this->model->getTableID()} = {$table}.{$this->model->getTableID()} //// AND ".$partial->getTagClause($this->model->getMetaTable()).")"; // } // if (!empty($conditions)) { // $q->WHERE(join(' OR ', $conditions)); // } // // } // IncludesAllMeta if (($iam = $nodeQuery->getParameter('Meta.exist')) != NULL) { $metas = PartialUtils::unserializeMetaPartials($iam); foreach ($metas as $partial) { $s = $schema->getMetaDef($partial->getMetaName()); $datatype = $s->Datatype; $mTable = $db->quoteIdentifier($this->NodeDBMeta->getMetaTable($tableNodeRef, $datatype)); $mClause = $this->NodeMetaDAO->getMetaPartialClause($partial, $datatype, $db, $mTable); $tablect++; $q->JOIN("INNER JOIN {$mTable} as CondTable{$tablect} " . str_replace($mTable, 'CondTable' . $tablect, " ON {$table}.{$tableid} = {$mTable}.{$tableid} AND {$mClause}")); // $q->WHERE("{$table}.{$tableid} IN // (SELECT {$mTable}.{$tableid} // FROM {$mTable} // WHERE ".$mClause.")"); // $this->db->WHERE("EXISTS (SELECT 1 FROM {$this->model->getMetaTable()} // WHERE {$this->model->getMetaTable()}.{$this->model->getTableID()} = {$table}.{$this->model->getTableID()} // AND ".$partial->getTagClause($this->model->getMetaTable()).")"); } } // IncludesOutTags // if(($iot = $nodeQuery->getParameter('IncludesOutTags')) != NULL ) { // $tags = PartialUtils::unserializeOutPartials($iot); // $conditions = array(); // foreach($tags as $partial) { // $tTable = $db->quoteIdentifier($this->NodeDBMeta->getOutTagsTable($tableNodeRef)); // // // TODO: need to support aspects // // TODO: convert OR clauses into multiple unions // // $tClause = $this->NodeTagsDAO->getOutTagPartialClause($partial, $tableNodeRef->getSite(), $db, $tTable); // // $conditions[] = "{$table}.{$tableid} IN // (SELECT {$tTable}.{$tableid} // FROM {$tTable} // WHERE {$tClause})"; //// $conditions[] = "EXISTS (SELECT 1 FROM {$this->model->getTagsTable()} t2 //// INNER JOIN tags2 ON t2.tagid = tags2.tagid //// WHERE {$table}.{$this->model->getTableID()} = t2.{$this->model->getTableID()} AND //// ".$partial->getTagClause("tags2").")"; // } // if (!empty($conditions)) { // $q->WHERE(join(' OR ', $conditions)); // } // // } // IncludesAllOutTags if (($iaot = $nodeQuery->getParameter('OutTags.exist')) != NULL) { $tags = PartialUtils::unserializeOutPartials($iaot); foreach ($tags as $partial) { $tTable = $db->quoteIdentifier($this->NodeDBMeta->getOutTagsTable($tableNodeRef)); $tClause = $this->NodeTagsDAO->getOutTagPartialClause($partial, $db, $tTable); $tablect++; $q->JOIN("INNER JOIN {$tTable} as CondTable{$tablect} " . str_replace($tTable, 'CondTable' . $tablect, " ON {$table}.{$tableid} = {$tTable}.{$tableid} AND {$tClause}")); // $q->WHERE( "{$table}.{$tableid} IN // (SELECT {$tTable}.{$tableid} // FROM {$tTable} // WHERE {$tClause})" ); // $this->db->WHERE("EXISTS (SELECT 1 FROM {$this->model->getTagsTable()} t2 // INNER JOIN tags2 ON t2.tagid = tags2.tagid // WHERE {$table}.{$this->model->getTableID()} = t2.{$this->model->getTableID()} AND // ".$partial->getTagClause("tags2").")"); } } // IncludesInTags // if(($iit = $nodeQuery->getParameter('IncludesInTags')) != NULL ) { // $tags = PartialUtils::unserializeInPartials($iit); // $conditions = array(); // foreach($tags as $partial) { // $tTable = $db->quoteIdentifier($this->NodeDBMeta->getInTagsTable($tableNodeRef)); // $tClause = $this->NodeTagsDAO->getInTagPartialClause($partial, $tableNodeRef->getElement(), $tableNodeRef->getSite(), $db, $tTable); // // $conditions[] = "{$table}.{$tableid} IN // (SELECT {$tTable}.{$tableid} // FROM {$tTable} // WHERE {$tClause})"; //// $conditions[] = "EXISTS (SELECT 1 FROM {$this->model->getTagsTable()} t2 //// INNER JOIN tags2 ON t2.tagid = tags2.tagid //// WHERE {$table}.{$this->model->getTableID()} = t2.{$this->model->getTableID()} AND //// ".$partial->getTagClause("tags2").")"; // } // if (!empty($conditions)) { // $q->WHERE(join(' OR ', $conditions)); // } // // } // IncludesAllInTags if (($iait = $nodeQuery->getParameter('InTags.exist')) != NULL) { $tags = PartialUtils::unserializeInPartials($iait); foreach ($tags as $partial) { $tTable = $db->quoteIdentifier($this->NodeDBMeta->getInTagsTable($tableNodeRef)); $tClause = $this->NodeTagsDAO->getInTagPartialClause($partial, $tableNodeRef->getElement(), $db, $tTable); $tablect++; $q->JOIN("INNER JOIN {$tTable} as CondTable{$tablect} " . str_replace($tTable, 'CondTable' . $tablect, " ON {$table}.{$tableid} = {$tTable}.{$tableid} AND {$tClause}")); // // $q->WHERE( "{$table}.{$tableid} IN // (SELECT {$tTable}.{$tableid} // FROM {$tTable} // WHERE {$tClause})" ); // $this->db->WHERE("EXISTS (SELECT 1 FROM {$this->model->getTagsTable()} t2 // INNER JOIN tags2 ON t2.tagid = tags2.tagid // WHERE {$table}.{$this->model->getTableID()} = t2.{$this->model->getTableID()} AND // ".$partial->getTagClause("tags2").")"); } } return $q; }
public function findMeta(DatabaseInterface $db, NodeRef $originNodeRef, $ids, $metaPartials = 'fields', $forceReadWrite = false, $restrictedPartials = '') { $results = array(); if (empty($metaPartials)) { return $results; } $this->Logger->debug('findMeta with partials [' . $metaPartials . '] restrict [' . $restrictedPartials . ']'); $returnOne = false; if (!is_array($ids)) { $returnOne = $ids; $ids = array($originNodeRef->getRefURL() => $ids); } $partials = PartialUtils::unserializeMetaPartials($metaPartials); $restrictedPartials = PartialUtils::unserializeMetaPartials($restrictedPartials); if ($restrictedPartials == 'all' || ($x = array_search('all', $restrictedPartials)) !== false) { return array(); } $tableid = $this->NodeDBMeta->getPrimaryKey($originNodeRef); $now = $this->DateFactory->newLocalDate(); $metaPartials = array(); $all = false; $metaDefs = $originNodeRef->getElement()->getSchema()->getMetaDefs(); $metaSchemas = $originNodeRef->getElement()->getSchema()->getMetaStorageDatatypes(); if ($partials == 'all' || ($x = array_search('all', $partials)) !== false) { if ($partials == 'all' || isset($x) && $x !== false) { $all = true; if (is_array($partials)) { unset($partials[$x]); } else { $partials = array(); } } } foreach ($partials as $z => $metaPartial) { if (!array_key_exists($metaPartial->getMetaName(), $metaDefs)) { unset($partials[$z]); continue; } $metaPartials[] = $metaPartial; } $rows = array(); $cachedIds = array(); // retrieve from cache list($cachedIds, $rows) = $this->NodeCache->getMeta('', $ids, $forceReadWrite); $remainingIds = array_diff($ids, $cachedIds); if (!empty($remainingIds)) { $dbToCacheRows = array(); // perform a SQL statement per partial foreach ($metaSchemas as $datatype) { $table = $this->NodeDBMeta->getMetaTable($originNodeRef, $datatype); $q = new Query(); $q->SELECT($tableid . ' as ID'); $q->SELECT(array('Name as MetaName')); if (($datatypeCol = $this->NodeDBMeta->getMetaDatatypeColumn($datatype)) != null) { $q->SELECT("{$datatypeCol}Value as MetaValue"); } $q->FROM($db->quoteIdentifier($table)); $q->WHERE("{$tableid} IN ({$db->joinQuote((array) $remainingIds)})"); $dbRows = $this->getConnectionForWrite($originNodeRef)->readAll($q); foreach ($dbRows as $row) { $dbToCacheRows[$row['ID']][] = $row; $rows[] = $row; } unset($dbRows); unset($datatype); unset($q); unset($table); } $this->NodeCache->putMeta('', $remainingIds, $dbToCacheRows, $forceReadWrite); unset($dbToCacheRows); unset($remainingIds); unset($metaSchemas); } foreach ($rows as $row) { $id = $row['ID']; unset($row['ID']); if (!array_key_exists($row['MetaName'], $metaDefs)) { continue; } foreach ($restrictedPartials as $dPartial) { if (strcmp($dPartial->getMetaName(), $row['MetaName']) === 0) { continue 2; } } if (!$all) { $found = false; foreach ($metaPartials as $dPartial) { if (strcmp($dPartial->getMetaName(), $row['MetaName']) === 0) { $found = true; break; } } if (!$found) { continue; } } $metaDef = $metaDefs[$row['MetaName']]; $row['MetaTitle'] = $metaDef->Title; $row['MetaStorageDatatype'] = (string) $metaDef->Datatype; $row['MetaValidationDatatype'] = $metaDef->Validation->getDatatype(); if ($row['MetaValidationDatatype'] == 'date') { $row['MetaValue'] = $this->TypeConverter->convertFromString($metaDef->Validation, $row['MetaValue'], null, true); } else { if ($row['MetaStorageDatatype'] == 'flag') { $row['MetaValue'] = 1; } } $row['NoValidation'] = true; $results[$id][$row['MetaName']] = new Meta($row); } if ($returnOne) { return isset($results[$returnOne]) ? $results[$returnOne] : array(); } return $results; }