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