/**
* 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 Xerxes_Data_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;
// lowercase the query
$query = strtolower($query);
$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 Xerxes_Data_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;
}
/**
* Determines if the database is searchable by user
*
* @param Xerxes_Data_Database $db
* @param Xerxes_Framework_Request $objRequest Xerxes request object
* @param Xerxes_Framework_Registry $objRegistry Xerxes registry object
* @return unknown
*/
public static function dbSearchableForUser(Xerxes_Data_Database $db, $objRequest, $objRegistry)
{
$allowed = "";
if ($db->searchable != 1) {
//nobody can search it!
$allowed = false;
} elseif ($db->guest_access != "") {
//anyone can search it!
$allowed = true;
} elseif (count($db->group_restrictions) > 0) {
// they have to be authenticated, and in a group that is included
// in the restrictions, or in an ip address associated with a
// restricted group.
$allowed = Xerxes_Framework_Restrict::isAuthenticatedUser($objRequest) && array_intersect($_SESSION["user_groups"], $db->group_restrictions);
if (!$allowed) {
// not by virtue of a login, but now check for ip address
$ranges = array();
foreach ($db->get("group_restrictions") as $group) {
$ranges[] = $objRegistry->getGroupLocalIpRanges($group);
}
$allowed = Xerxes_Framework_Restrict::isIpAddrInRanges($objRequest->getServer('REMOTE_ADDR'), implode(",", $ranges));
}
} else {
// ordinary generally restricted resource. they need to be
// an authenticated user, or in the local ip range.
if (Xerxes_Framework_Restrict::isAuthenticatedUser($objRequest) || Xerxes_Framework_Restrict::isIpAddrInRanges($objRequest->getServer('REMOTE_ADDR'), $objRegistry->getConfig("LOCAL_IP_RANGE"))) {
$allowed = true;
}
}
return $allowed;
}
