/**
* Index a Database so it can be searched
*
* @param Database $database
*/
public function indexDatabase(Database $database)
{
$this->datamap()->beginTransaction();
// remove any existing entries
$delete_sql = 'DELETE FROM research_databases_search WHERE database_id = :id';
$delete_params = array(':id' => $database->getId());
$this->datamap()->delete($delete_sql, $delete_params);
// see which fields we want to index
// get 'em all
$fields = $database->toArray();
foreach ($fields as $field => $value) {
// this is not the field you're looking for
if (!in_array($field, $this->searchable_fields)) {
continue;
}
// keyword and the like
if (is_array($value)) {
$value = implode(' ', $value);
}
$searchable_terms = array();
foreach (explode(" ", (string) $value) as $term) {
// only numbers and letters please
$term = preg_replace('/[^a-zA-Z0-9]/', '', $term);
$term = trim(strtolower($term));
// no searchable terms
if ($term == "") {
continue;
}
// anything over 50 chars is likley a URL or something
if (strlen($term) > 50) {
continue;
}
array_push($searchable_terms, $term);
}
// remove duplicate terms
$searchable_terms = array_unique($searchable_terms);
// insert em
$sql = "INSERT INTO research_databases_search ( database_id, field, term ) " . "VALUES ( :database_id, :field, :term )";
foreach ($searchable_terms as $unique_term) {
$this->datamap()->insert($sql, array(":database_id" => $database->getId(), ":field" => $field, ":term" => $unique_term));
}
}
$this->datamap()->commit();
}
/**
* Add a database to the knowledgebase
*/
public function updateDatabaseAction()
{
$id = $this->request->getParam('id');
$title = $this->request->requireParam('title', 'You must specify a title');
$link = $this->request->requireParam('link', 'You must specify a link');
$description = $this->request->getParam('description');
$type = $this->request->getParam('type');
$coverage = $this->request->getParam('coverage');
$active = (bool) $this->request->getParam('active', false, false);
$proxy = (bool) $this->request->getParam('proxy', false, false);
$date_new_expiry = $this->request->getParam('date_new_expiry');
$date_trial_expiry = $this->request->getParam('date_trial_expiry');
$keywords = $this->request->getParam('keywords');
$creator = $this->request->getParam('creator');
$publisher = $this->request->getParam('publisher');
$search_hints = $this->request->getParam('search_hints');
$link_guide = $this->request->getParam('link_guide');
$link_copyright = $this->request->getParam('link_copyright');
$language = $this->request->getParam('language');
$notes = $this->request->getParam('notes');
$alternate_titles = $this->request->getParam('alternate_titles');
// if an id came in, then we are editing
// rather than adding, so fetch the database
$database = null;
if ($id != "") {
$database = $this->knowledgebase->getDatabase($id);
} else {
$database = new Database();
}
$database->setType($type);
$database->setCoverage($coverage);
$database->setCreator($creator);
$database->setDescription($description);
$database->setLanguage($language);
$database->setLink($link);
$database->setLinkGuide($link_guide);
$database->setNotes($notes);
$database->setPublisher($publisher);
$database->setSearchHints($search_hints);
$database->setTitle($title);
$database->setAlternateTitles($alternate_titles);
$database->setKeywords($keywords);
if ($date_new_expiry != null) {
$date_time = new \DateTime($date_new_expiry);
$database->setDateNewExpiry($date_time);
}
if ($date_trial_expiry != null) {
$date_time = new \DateTime($date_trial_expiry);
$database->setDateTrialExpiry($date_time);
} else {
$database->removeDateTrialExpiry();
}
$database->setProxy($proxy);
$database->setActive($active);
$this->knowledgebase->updateDatabase($database);
$this->clearDatabaseCache();
$params = array('controller' => $this->request->getParam('controller'), 'action' => 'database', 'id' => $database->getId());
return $this->redirectTo($params);
}
/**
* Get one or a set of databases from the knowledgebase
*
* @param mixed $id [optional] null returns all database, array returns a list of databases by id,
* string id returns single id
* @param string $query user-entered query to search for dbs.
* @return array array of Database objects
*/
public function getDatabases($id = null, $query = null, $alpha = null)
{
$configDatabaseTypesExclude = $this->registry->getConfig("DATABASES_TYPE_EXCLUDE_AZ", false);
$configAlwaysTruncate = $this->registry->getConfig("DATABASES_SEARCH_ALWAYS_TRUNCATE", false, false);
$arrDatabases = array();
$arrResults = array();
$arrParams = array();
$where = false;
$sql_server_clean = null;
$strSQL = "SELECT * from xerxes_databases";
// single database
if ($id != null && !is_array($id)) {
$strSQL .= " WHERE xerxes_databases.metalib_id = :id ";
$arrParams[":id"] = $id;
$where = true;
} elseif ($id != null && is_array($id)) {
$strSQL .= " WHERE ";
$where = true;
for ($x = 0; $x < count($id); $x++) {
if ($x > 0) {
$strSQL .= " OR ";
}
$strSQL .= "xerxes_databases.metalib_id = :id{$x} ";
$arrParams[":id{$x}"] = $id[$x];
}
} elseif ($alpha != null) {
$strSQL .= " WHERE UPPER(title_display) LIKE :alpha ";
$arrParams[":alpha"] = "{$alpha}%";
$where = true;
} elseif ($query != null) {
$where = true;
$sql_server_clean = array();
$arrTables = array();
// we'll use this to keep track of temporary tables
// we'll deal with quotes later, for now
// and gives us each term in an array
$arrTerms = explode(" ", $query);
// grab databases that meet our query
$strSQL .= " WHERE metalib_id IN (\r\n\t\t\t\tSELECT database_id FROM ";
// by looking for each term in the xerxes_databases_search table
// making each result a temp table
for ($x = 0; $x < count($arrTerms); $x++) {
$term = $arrTerms[$x];
// to match how they are inserted
$term = preg_replace('/[^a-zA-Z0-9\\*]/', '', $term);
// do this to reduce the results of the inner table to just one column
$alias = "database_id";
if ($x > 0) {
$alias = "db";
}
// wildcard
$operator = "=";
// default operator is equal
// user supplied a wildcard
if (strstr($term, "*")) {
$term = str_replace("*", "%", $term);
$operator = "LIKE";
} elseif ($configAlwaysTruncate == true) {
$term .= "%";
$operator = "LIKE";
}
$arrParams[":term{$x}"] = $term;
array_push($sql_server_clean, ":term{$x}");
$strSQL .= " (SELECT distinct database_id AS {$alias} FROM xerxes_databases_search WHERE term {$operator} :term{$x}) AS table{$x} ";
// if there is another one, we need to add a comma between them
if ($x + 1 < count($arrTerms)) {
$strSQL .= ", ";
}
// this essentially AND's the query by requiring results from all tables
if ($x > 0) {
for ($y = 0; $y < $x; $y++) {
$column = "db";
if ($y == 0) {
$column = "database_id";
}
array_push($arrTables, "table{$y}.{$column} = table" . ($y + 1) . ".db");
}
}
}
// add the AND'd tables to the SQL
if (count($arrTables) > 0) {
$strSQL .= " WHERE " . implode(" AND ", $arrTables);
}
$strSQL .= ")";
}
// remove certain databases based on type(s), if so configured
// unless we're asking for specific id's, yo
if ($configDatabaseTypesExclude != null && $id == null) {
$arrTypes = explode(",", $configDatabaseTypesExclude);
$arrTypeQuery = array();
// specify that the type NOT be one of these
for ($q = 0; $q < count($arrTypes); $q++) {
array_push($arrTypeQuery, "xerxes_databases.type != :type{$q}");
$arrParams[":type{$q}"] = trim($arrTypes[$q]);
}
// AND 'em but then also catch the case where type is null
$joiner = "WHERE";
if ($where == true) {
$joiner = "AND";
}
$strSQL .= " {$joiner} ( (" . implode(" AND ", $arrTypeQuery) . ") OR xerxes_databases.type IS NULL )";
}
$strSQL .= " ORDER BY UPPER(title_display)";
// echo $strSQL; print_r($arrParams); // exit;
$arrResults = $this->select($strSQL, $arrParams, $sql_server_clean);
// transform to internal data objects
if ($arrResults != null) {
foreach ($arrResults as $arrResult) {
$objDatabase = new Database();
$objDatabase->load($arrResult);
array_push($arrDatabases, $objDatabase);
}
}
// limit to quoted phrases
if (strstr($query, '"')) {
// unload the array, we'll only refill the ones that match the query
$arrCandidates = $arrDatabases;
$arrDatabases = array();
$found = false;
$phrases = explode('"', $query);
foreach ($arrCandidates as $objDatabase) {
foreach ($phrases as $phrase) {
$phrase = trim($phrase);
if ($phrase == "") {
continue;
}
$text = " ";
foreach ($this->searchable_fields as $searchable_field) {
$text .= $objDatabase->{$searchable_field} . " ";
}
if (!stristr($text, $phrase)) {
$found = false;
break;
} else {
$found = true;
}
}
if ($found == true) {
array_push($arrDatabases, $objDatabase);
}
}
}
return $arrDatabases;
}
/**
* Add database to group
*
* Assigns non-searchable databases to excluded list
*
* @param Database $database_object
*/
public function addDatabase(Database $database_object, User $user)
{
$id = $database_object->database_id;
// see if this database is searchable
if ($database_object->isSearchableByUser($user)) {
$this->included_databases[$id] = new DatabaseResultSet($database_object);
} else {
$this->excluded_databases[$id] = $database_object;
}
}
