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;
}
