/** * @see \wcf\system\search\AbstractSearchIndexManager::createSearchIndex() */ protected function createSearchIndex(ObjectType $objectType) { $tableName = SearchIndexManager::getTableName($objectType); // check if table already exists $sql = "SELECT\tCOUNT(*) AS count\n\t\t\tFROM\twcf" . WCF_N . "_package_installation_sql_log\n\t\t\tWHERE\tsqlTable = ?"; $statement = WCF::getDB()->prepareStatement($sql); $statement->execute(array($tableName)); $row = $statement->fetchArray(); if ($row['count']) { // table already exists return false; } $columns = array(array('name' => 'objectID', 'data' => array('length' => 10, 'notNull' => true, 'type' => 'int')), array('name' => 'subject', 'data' => array('default' => '', 'length' => 255, 'notNull' => true, 'type' => 'varchar')), array('name' => 'message', 'data' => array('type' => 'mediumtext')), array('name' => 'metaData', 'data' => array('type' => 'mediumtext')), array('name' => 'time', 'data' => array('default' => 0, 'length' => 10, 'notNull' => true, 'type' => 'int')), array('name' => 'userID', 'data' => array('default' => '', 'length' => 10, 'type' => 'int')), array('name' => 'username', 'data' => array('default' => '', 'length' => 255, 'notNull' => true, 'type' => 'varchar')), array('name' => 'languageID', 'data' => array('default' => 0, 'length' => 10, 'notNull' => true, 'type' => 'int'))); $indices = array(array('name' => 'objectAndLanguage', 'data' => array('columns' => 'objectID, languageID', 'type' => 'UNIQUE')), array('name' => 'fulltextIndex', 'data' => array('columns' => 'subject, message, metaData', 'type' => 'FULLTEXT')), array('name' => 'fulltextIndexSubjectOnly', 'data' => array('columns' => 'subject', 'type' => 'FULLTEXT')), array('name' => 'language', 'data' => array('columns' => 'languageID', 'type' => 'KEY')), array('name' => 'user', 'data' => array('columns' => 'userID, time', 'type' => 'KEY'))); WCF::getDB()->getEditor()->createTable($tableName, $columns, $indices); // add comment $sql = "ALTER TABLE\t" . $tableName . "\n\t\t\tCOMMENT\t\t= 'Search index for " . $objectType->objectType . "'"; $statement = WCF::getDB()->prepareStatement($sql); $statement->execute(); // log table $sql = "INSERT INTO\twcf" . WCF_N . "_package_installation_sql_log\n\t\t\t\t\t(packageID, sqlTable)\n\t\t\tVALUES\t\t(?, ?)"; $statement = WCF::getDB()->prepareStatement($sql); $statement->execute(array($objectType->packageID, $tableName)); return true; }
/** * @see \wcf\system\search\ISearchEngine::getInnerJoin() */ public function getInnerJoin($objectTypeName, $q, $subjectOnly = false, PreparedStatementConditionBuilder $searchIndexCondition = null, $orderBy = 'time DESC', $limit = 1000) { $fulltextCondition = null; $relevanceCalc = ''; if (!empty($q)) { $q = $this->parseSearchQuery($q); $fulltextCondition = new PreparedStatementConditionBuilder(false); $fulltextCondition->add("MATCH (subject" . (!$subjectOnly ? ', message, metaData' : '') . ") AGAINST (? IN BOOLEAN MODE)", array($q)); if ($orderBy == 'relevance ASC' || $orderBy == 'relevance DESC') { $relevanceCalc = "MATCH (subject" . (!$subjectOnly ? ', message, metaData' : '') . ") AGAINST ('" . escapeString($q) . "') + (5 / (1 + POW(LN(1 + (" . TIME_NOW . " - time) / 2592000), 2))) AS relevance"; } } $sql = "SELECT\t\tobjectID\n\t\t\t\t\t" . ($relevanceCalc ? ',' . $relevanceCalc : '') . "\n\t\t\tFROM\t\t" . SearchIndexManager::getTableName($objectTypeName) . "\n\t\t\tWHERE\t\t" . ($fulltextCondition !== null ? $fulltextCondition : '') . "\n\t\t\t\t\t" . ($searchIndexCondition !== null && $searchIndexCondition->__toString() ? ($fulltextCondition !== null ? "AND " : '') . $searchIndexCondition : '') . "\n\t\t\t" . (!empty($orderBy) && $fulltextCondition === null ? 'ORDER BY ' . $orderBy : '') . "\n\t\t\tLIMIT\t\t" . ($limit == 1000 ? SearchEngine::INNER_SEARCH_LIMIT : $limit); return array('fulltextCondition' => $fulltextCondition, 'searchIndexCondition' => $searchIndexCondition, 'sql' => $sql); }