private function compareOldToNewQueries($criteria, $sphinxOldQuery, $mysqlOldQuery) { $pdo = DbManager::getSphinxConnection(); $successfulTest = true; $OldSphinxQueryIds = array(); $OldQueryIds = array('just somthing to put so this is not empty'); $criteria->applyFilters(); $sphinxNewQueryids = $criteria->getFetchedIds(); try { var_dump($sphinxOldQuery); $stmtSphinxOldQuery = $pdo->query($sphinxOldQuery); if (!$stmtSphinxOldQuery) { echo "Invalid sphinx query [" . $sphinxOldQuery; } $OldSphinxQueryIds = $stmtSphinxOldQuery->fetchAll(PDO::FETCH_COLUMN, 2); } catch (Exception $x) { echo 'error in old sphinx select: ' . $x->getMessage() . PHP_EOL; echo 'old sphinx query: ' . $sphinxOldQuery . PHP_EOL; $successfulTest = false; } if ($mysqlOldQuery != null) { try { $con = myDbHelper::getConnection(myDbHelper::DB_HELPER_CONN_PROPEL2); /* var PDOStatement $stmt*/ $stmt = $con->query($mysqlOldQuery); $OldQueryIds = $stmt->fetchAll(PDO::FETCH_COLUMN); $stmt->execute(); } catch (Exception $x) { echo 'error in mysql old select: ' . $x->getMessage() . PHP_EOL; echo 'old mysqk query: ' . ${$mysqlOldQuery} . PHP_EOL; $successfulTest = false; } } $OldQueryIds = array_slice($OldQueryIds, 0, 500); if (!$successfulTest || is_null($mysqlOldQuery) || count(array_diff($OldQueryIds, $sphinxNewQueryids)) || count(array_diff($sphinxNewQueryids, $OldQueryIds))) { if ($successfulTest && !(count(array_diff($OldSphinxQueryIds, $sphinxNewQueryids)) || count(array_diff($sphinxNewQueryids, $OldSphinxQueryIds)))) { echo 'Test OK : ' . PHP_EOL; echo 'Test Faild - but was the same in sphinx: ' . print_r($OldSphinxQueryIds, true) . print_r($sphinxNewQueryids, true) . PHP_EOL; $successfulTest = true; } else { echo 'Test Faild: ' . PHP_EOL; if ($successfulTest && ($mysqlOldQuery != null && strstr($mysqlOldQuery, 'COUNT(*)'))) { echo 'Test ignored: ' . PHP_EOL; $successfulTest = true; } else { $successfulTest = false; } } } if ($successfulTest) { echo 'Successful Test' . PHP_EOL; } else { echo 'Fail Test' . PHP_EOL; } return $successfulTest; }
protected function executeSphinx($index, $wheres, $orderBy, $limit, $maxMatches, $setLimit) { $sql = "SELECT str_entry_id FROM {$index} {$wheres} {$orderBy} LIMIT {$limit} OPTION max_matches={$maxMatches}"; //debug query //echo $sql."\n"; die; $pdo = DbManager::getSphinxConnection(); $stmt = $pdo->query($sql); if (!$stmt) { KalturaLog::err("Invalid sphinx query [{$sql}]"); return; } $ids = $stmt->fetchAll(PDO::FETCH_COLUMN, 2); if (count($this->entryIds)) { foreach ($this->entryIds as $comparison => $entryIds) { // keeps only ids that appears in both arrays if ($comparison == Criteria::IN) { $ids = array_intersect($ids, array_keys($entryIds)); } // removes ids that appears in the comparison array if ($comparison == Criteria::NOT_IN) { $ids = array_diff($ids, array_keys($entryIds)); } } } KalturaLog::debug("Found " . count($ids) . " ids"); foreach ($this->keyToRemove as $key) { KalturaLog::debug("Removing key [{$key}] from criteria"); $this->remove($key); } $this->addAnd(entryPeer::ID, $ids, Criteria::IN); $this->recordsCount = 0; if (!$this->doCount) { return; } if ($setLimit) { $this->setOffset(0); $sql = "show meta"; $stmt = $pdo->query($sql); $meta = $stmt->fetchAll(PDO::FETCH_NAMED); if (count($meta)) { foreach ($meta as $metaItem) { KalturaLog::debug("Sphinx query " . $metaItem['Variable_name'] . ': ' . $metaItem['Value']); if ($metaItem['Variable_name'] == 'total_found') { $this->recordsCount = (int) $metaItem['Value']; } } } } else { $c = clone $this; $c->setLimit(null); $c->setOffset(null); $this->recordsCount = entryPeer::doCount($c); } }
/** * @param string $sql * @param IIndexable $object * @return bool */ public function execSphinx($sql, IIndexable $object) { KalturaLog::debug($sql); $sphinxConnection = null; $sphinxConnectionId = null; if (kConf::hasParam('exec_sphinx') && kConf::get('exec_sphinx')) { $sphinxConnection = DbManager::getSphinxConnection(false); $sphinxServer = SphinxLogServerPeer::retrieveByLocalServer($sphinxConnection->getHostName()); if ($sphinxServer) { $sphinxConnectionId = $sphinxServer->getId(); } } $sphinxLog = new SphinxLog(); $sphinxLog->setExecutedServerId($sphinxConnectionId); $sphinxLog->setObjectId($object->getId()); $sphinxLog->setObjectType(get_class($object)); $sphinxLog->setEntryId($object->getEntryId()); $sphinxLog->setPartnerId($object->getPartnerId()); $sphinxLog->setSql($sql); $sphinxLog->save(myDbHelper::getConnection(myDbHelper::DB_HELPER_CONN_SPHINX_LOG)); if (!$sphinxConnection) { return true; } $sphinxConnection = DbManager::getSphinxConnection(false); $ret = $sphinxConnection->exec($sql); if ($ret) { return true; } $arr = $sphinxConnection->errorInfo(); KalturaLog::err($arr[2]); return false; }
private function retrieveSphinxConnectionId() { $sphinxConnectionId = null; if (kConf::hasParam('exec_sphinx') && kConf::get('exec_sphinx')) { $sphinxConnection = DbManager::getSphinxConnection(false); $sphinxServerCacheStore = kCacheManager::getSingleLayerCache(kCacheManager::CACHE_TYPE_SPHINX_EXECUTED_SERVER); if ($sphinxServerCacheStore) { $sphinxConnectionId = $sphinxServerCacheStore->get(self::CACHE_PREFIX . $sphinxConnection->getHostName()); if ($sphinxConnectionId) { return $sphinxConnectionId; } } $sphinxServer = SphinxLogServerPeer::retrieveByLocalServer($sphinxConnection->getHostName()); if ($sphinxServer) { $sphinxConnectionId = $sphinxServer->getId(); if ($sphinxServerCacheStore) { $sphinxServerCacheStore->set(self::CACHE_PREFIX . $sphinxConnection->getHostName(), $sphinxConnectionId); } } } return $sphinxConnectionId; }
/** * @param string $index index name * @param string $wheres * @param string $orderBy * @param string $limit * @param int $maxMatches * @param bool $setLimit * @param string $conditions */ protected function executeSphinx($index, $wheres, $orderBy, $limit, $maxMatches, $setLimit, $conditions = '') { $pdo = DbManager::getSphinxConnection(); $comment = $pdo->getComment(); $sphinxIdField = $this->getSphinxIdField(); $sql = "SELECT {$sphinxIdField} {$conditions} FROM {$index} {$wheres} {$orderBy} LIMIT {$limit} OPTION ranker={$this->ranker}, max_matches={$maxMatches}, comment='{$comment}'"; if (kConf::hasParam('sphinx_extra_options')) { $sql .= ', ' . kConf::get('sphinx_extra_options'); } $badSphinxQueries = kConf::hasParam("sphinx_bad_queries") ? kConf::get("sphinx_bad_queries") : array(); foreach ($badSphinxQueries as $badQuery) { if (preg_match($badQuery, $sql)) { KalturaLog::log("bad sphinx query: [{$badQuery}] {$sql}"); KExternalErrors::dieError(KExternalErrors::BAD_QUERY); } } //debug query $stmt = $pdo->query($sql); if (!$stmt) { list($sqlState, $errCode, $errDescription) = $pdo->errorInfo(); throw new kCoreException("Invalid sphinx query [{$sql}]\nSQLSTATE error code [{$sqlState}]\nDriver error code [{$errCode}]\nDriver error message [{$errDescription}]", APIErrors::SEARCH_ENGINE_QUERY_FAILED); } $ids = $stmt->fetchAll(PDO::FETCH_COLUMN, 2); $ids = $this->applyIds($ids); $this->setFetchedIds($ids); KalturaLog::debug("Found " . count($ids) . " ids"); foreach ($this->keyToRemove as $key) { KalturaLog::debug("Removing key [{$key}] from criteria"); $this->remove($key); } $this->addAnd($this->getPropelIdField(), $ids, Criteria::IN); $this->recordsCount = 0; if (!$this->doCount) { return; } if ($setLimit) { $this->setOffset(0); $sql = "show meta"; $stmt = $pdo->query($sql); $meta = $stmt->fetchAll(PDO::FETCH_NAMED); if (count($meta)) { foreach ($meta as $metaItem) { if ($metaItem['Variable_name'] == 'total_found') { $this->recordsCount = (int) $metaItem['Value']; KalturaLog::debug('Sphinx query total_found: ' . $this->recordsCount); break; } } } } else { $c = clone $this; $c->setLimit(null); $c->setOffset(null); $this->recordsCount = $this->doCountOnPeer($c); } }
/** * @param string $index index name * @param string $wheres * @param string $orderBy * @param string $limit * @param int $maxMatches * @param bool $setLimit * @param string $conditions */ protected function executeSphinx($index, $wheres, $orderBy, $limit, $maxMatches, $setLimit, $conditions = '') { $pdo = DbManager::getSphinxConnection(); $objectClass = $this->getIndexObjectName(); if (!$this->selectColumn) { $this->selectColumn = $objectClass::getSphinxIdField(); } $sql = "SELECT {$this->selectColumn} {$conditions} FROM {$index} {$wheres} " . ($this->groupByColumn ? "GROUP BY {$this->groupByColumn} " : "") . "{$orderBy} LIMIT {$limit} OPTION ranker={$this->ranker}, max_matches={$maxMatches}, comment='" . kApiCache::KALTURA_COMMENT_MARKER . "'"; if (kConf::hasParam('sphinx_extra_options')) { $sql .= ', ' . kConf::get('sphinx_extra_options'); } $badSphinxQueries = kConf::hasParam("sphinx_bad_queries") ? kConf::get("sphinx_bad_queries") : array(); foreach ($badSphinxQueries as $badQuery) { if (preg_match($badQuery, $sql)) { KalturaLog::log("bad sphinx query: [{$badQuery}] {$sql}"); throw new kCoreException("Invalid sphinx query [{$sql}]\nMatched regular expression [{$badQuery}]", APIErrors::SEARCH_ENGINE_QUERY_FAILED); } } //debug query $ids = $pdo->queryAndFetchAll($sql, PDO::FETCH_COLUMN, 0); if ($ids === false) { list($sqlState, $errCode, $errDescription) = $pdo->errorInfo(); throw new kCoreException("Invalid sphinx query [{$sql}]\nSQLSTATE error code [{$sqlState}]\nDriver error code [{$errCode}]\nDriver error message [{$errDescription}]", APIErrors::SEARCH_ENGINE_QUERY_FAILED); } $idsCount = count($ids); $this->setFetchedIds($ids); KalturaLog::debug("Found {$idsCount} ids"); foreach ($this->keyToRemove as $key) { KalturaLog::debug("Removing key [{$key}] from criteria"); $this->remove($key); } $propelIdField = $objectClass::getPropelIdField(); $this->addAnd($propelIdField, $ids, Criteria::IN); $this->recordsCount = 0; if (!$this->doCount) { return; } if ($setLimit) { if ($this->getLimit() && $idsCount && $idsCount < $this->getLimit()) { $this->recordsCount = $idsCount; if ($this->getOffset()) { $this->recordsCount += $this->getOffset(); } } else { $metaItems = $pdo->queryAndFetchAll("show meta like '%total_found%'", PDO::FETCH_ASSOC); if ($metaItems) { $metaItem = reset($metaItems); $this->recordsCount = (int) $metaItem['Value']; KalturaLog::debug('Sphinx query total_found: ' . $this->recordsCount); } } $this->setOffset(0); } else { $c = clone $this; $c->setLimit(null); $c->setOffset(null); $this->recordsCount = $objectClass::doCountOnPeer($c); } }
/** * @param string $sql * @param IIndexable $object * @return bool */ public function execSphinx($sql, IIndexable $object) { KalturaLog::debug($sql); $sphinxLog = new SphinxLog(); $sphinxLog->setEntryId($object->getEntryId()); $sphinxLog->setPartnerId($object->getPartnerId()); $sphinxLog->setSql($sql); $sphinxLog->save(myDbHelper::getConnection(myDbHelper::DB_HELPER_CONN_SPHINX_LOG)); if (!kConf::hasParam('exec_sphinx') || !kConf::get('exec_sphinx')) { return true; } $con = DbManager::getSphinxConnection(); $ret = $con->exec($sql); if ($ret) { return true; } $arr = $con->errorInfo(); KalturaLog::err($arr[2]); return false; }