function SplitSQL($file, $delimiter = ';') { set_time_limit(0); if (is_file($file) === true) { $file = fopen($file, 'r'); if (is_resource($file) === true) { $query = []; $db = new DB(); $dbsys = DB_TYPE; $c = new ColorCLI(); while (feof($file) === false) { $query[] = fgets($file); if (preg_match('~' . preg_quote($delimiter, '~') . '\\s*$~iS', end($query)) === 1) { $query = trim(implode('', $query)); if ($dbsys == "pgsql") { $query = str_replace(array("`", chr(96)), '', $query); } try { $qry = $db->prepare($query); $qry->execute(); echo $c->alternateOver('SUCCESS: ') . $c->primary($query); } catch (PDOException $e) { if ($e->errorInfo[1] == 1091 || $e->errorInfo[1] == 1060 || $e->errorInfo[1] == 1054 || $e->errorInfo[1] == 1061 || $e->errorInfo[1] == 1062 || $e->errorInfo[1] == 1071 || $e->errorInfo[1] == 1072 || $e->errorInfo[1] == 1146 || $e->errorInfo[0] == 23505 || $e->errorInfo[0] == 42701 || $e->errorInfo[0] == 42703 || $e->errorInfo[0] == '42P07' || $e->errorInfo[0] == '42P16') { if ($e->errorInfo[1] == 1060) { echo $c->error($query . " The column already exists - Not Fatal {" . $e->errorInfo[1] . "}.\n"); } else { echo $c->error($query . " Skipped - Not Fatal {" . $e->errorInfo[1] . "}.\n"); } } else { if (preg_match('/ALTER IGNORE/i', $query)) { $db->queryExec("SET SESSION old_alter_table = 1"); try { $qry = $db->prepare($query); $qry->execute(); echo $c->alternateOver('SUCCESS: ') . $c->primary($query); } catch (PDOException $e) { exit($c->error($query . " Failed {" . $e->errorInfo[1] . "}\n\t" . $e->errorInfo[2])); } } else { exit($c->error($query . " Failed {" . $e->errorInfo[1] . "}\n\t" . $e->errorInfo[2])); } } } while (ob_get_level() > 0) { ob_end_flush(); } flush(); } if (is_string($query) === true) { $query = []; } } return fclose($file); } else { return false; } } else { return false; } }
/** * Optimize a RT index. * @param string $indexName */ public function optimizeRTIndex($indexName) { if (!is_null($this->sphinxQL)) { $this->sphinxQL->queryExec(sprintf('FLUSH RTINDEX %s', $indexName)); $this->sphinxQL->queryExec(sprintf('OPTIMIZE INDEX %s', $indexName)); } }
function populate_rt($table = '') { $pdo = new DB(); $rows = false; switch ($table) { case 'releases_rt': $pdo->queryDirect('SET SESSION group_concat_max_len=8192'); $rows = $pdo->queryExec('SELECT r.id, r.name, r.searchname, r.fromname, IFNULL(GROUP_CONCAT(rf.name SEPARATOR " "),"") filename FROM releases r LEFT JOIN releasefiles rf ON(r.id=rf.releaseid) GROUP BY r.id'); $rtvalues = '(id, name, searchname, fromname, filename)'; break; } if ($rows !== false && ($total = $rows->rowCount())) { $sphinx = new SphinxSearch(); $string = sprintf('REPLACE INTO %s %s VALUES ', $table, $rtvalues); $tempString = ''; $i = 0; echo '[Starting to populate sphinx RT index ' . $table . ' with ' . $total . ' releases.] '; foreach ($rows as $row) { $i++; switch ($table) { case 'releases_rt': $tempString .= sprintf('(%d, %s, %s, %s, %s),', $row['id'], $sphinx->sphinxQL->escapeString($row['name']), $sphinx->sphinxQL->escapeString($row['searchname']), $sphinx->sphinxQL->escapeString($row['fromname']), $sphinx->sphinxQL->escapeString($row['filename'])); break; } if ($i === 1000 || $i >= $total) { $sphinx->sphinxQL->queryExec($string . rtrim($tempString, ',')); $tempString = ''; $total -= $i; $i = 0; echo '.'; } } echo ' [Done.]' . PHP_EOL; } else { echo 'No releases in your DB or an error occurred. This will need to be resolved before you can use the search.' . PHP_EOL; } }
} elseif ($argc == 2) { // Checks that argv[1] exists AND that there are no other arguments, which would be an error. $socket = preg_replace('#^(?:unix://)?(.*)$#', '$1', $argv[1]); if (substr($socket, 0, 1) == '/') { // Make sure the socket path is fully qualified (and using correct separator). $sphinxConnection = sprintf('unix://%s:', $socket); } } else { exit("Argument 1 must the hostname or IP to the Sphinx searchd server, Argument 2 must be the port to the Sphinx searchd server.\nAlternatively, Argument 1 can be a unix domain socket." . PHP_EOL); } $pdo = new DB(); $tableSQL_releases = <<<DDLSQL CREATE TABLE releases_se ( \tid BIGINT UNSIGNED NOT NULL, \tweight INTEGER NOT NULL, \tquery VARCHAR(1024) NOT NULL, \tname VARCHAR(255) NOT NULL DEFAULT '', \tsearchname VARCHAR(255) NOT NULL DEFAULT '', \tfromname VARCHAR(255) NULL, \tfilename VARCHAR(1000) NULL, \tINDEX(query) ) ENGINE=SPHINX CONNECTION="%sreleases_rt" DDLSQL; $tables = []; $tables['releases_se'] = sprintf($tableSQL_releases, $sphinxConnection); foreach ($tables as $table => $query) { $pdo->queryExec(sprintf('DROP TABLE IF EXISTS %s', $table)); $pdo->queryExec($query); } echo 'All done! If you messed up your sphinx connection info, you can rerun this script.' . PHP_EOL;