/**
* Builds the main SQL query
*
* @return string $sql
*/
private function buildCoreSQL()
{
if (count($this->metadata) + count($this->db) == 0) {
// return empty result set
return '';
}
$sql = 'SELECT ' . "\n";
if ($this->context == ExprContext::DOCUMENT) {
// we are doing this because content table is dependant on metadata table
if ($this->used_tables['document_content_version'] > 0) {
$this->used_tables['document_metadata_version']++;
}
$sql .= ' DISTINCT d.id, dmv.name as title';
} else {
$sql .= ' DISTINCT f.id, f.name as title';
}
/*
* This section of code builds the part of the query which is used to
* determine ranking for db fields
*
* 0 = "does not match"
*/
$offset = 0;
foreach ($this->db as $expr) {
$offset++;
$sql .= ", ifnull(" . $this->getSQLEvalExpr($expr) . ",0) as expr{$offset} ";
}
/*
* This section of code builds the part of the query which is used to
* determine ranking for metadata fields
*
* 0 = "does not match"
*/
foreach ($this->metadata as $expr) {
$offset++;
$sql .= ", ifnull(" . $this->getSQLEvalExpr($expr) . ",0) as expr{$offset} ";
}
$sql .= "\n" . 'FROM ' . "\n";
if ($this->context == ExprContext::DOCUMENT) {
$primaryAlias = 'd';
$sql .= ' documents d ' . "\n";
if ($this->used_tables['document_metadata_version'] > 0) {
$sql .= ' INNER JOIN document_metadata_version dmv ON d.metadata_version_id=dmv.id' . "\n";
}
if ($this->used_tables['document_content_version'] > 0) {
$sql .= ' INNER JOIN document_content_version dcv ON dmv.content_version_id=dcv.id ' . "\n";
}
// NOTE this was the old method of dealing with joining on the document_fields_link table;
// the new method incorporates previously set up joining code which was not completely understood
// at the time I modified this code; it allows everything to be more encapsulated within the classes and
// done in a more generic way which does not require a special case code snippet such as the one commented
// out below.
// if ($this->used_tables['document_fields_link'] > 0)
// {
// for ($i = 0; $i < $this->used_tables['document_fields_link']; ++$i)
// {
// if ($i > 0) $counter = $i;
// else $counter = '';
//
// $sql .= ' LEFT JOIN document_fields_link pdfl' . $counter . ' '
// . 'ON dmv.id=pdfl' . $counter . '.metadata_version_id ' . "\n";
// }
// }
if ($this->used_tables['tag_words'] > 0) {
$sql .= ' LEFT OUTER JOIN document_tags dt ON dt.document_id=d.id ' . "\n" . ' LEFT OUTER JOIN tag_words tw ON dt.tag_id = tw.id ' . "\n";
}
} else {
$primaryAlias = 'f';
$sql .= ' folders f ' . "\n";
}
/*
* This builds the JOINs required to correctly select on multiple tables.
*
* NOTE This is explicitly required for multi-selecting from the same table using multiple
* joins with different offset naming.
* This multi-selecting is necessary to avoid issues with complex queries
* requiring different results from a single column within a database table.
*
* For an example of how the field classes need to be set up to take advantage of this code
* look at the constructors for the AnyMetadataField class or the MimeTypeField class.
*/
$offset = 0;
foreach ($this->db as $expr) {
$field = $expr->left();
$jointable = $field->getJoinTable();
if (!is_null($jointable)) {
$fieldname = $this->resolveTableToAlias($field->getTable()) . '.' . $field->getField();
$joinalias = "{$jointable}{$offset}";
$joinfield = $field->getJoinField();
$sql .= " LEFT OUTER JOIN {$jointable} {$joinalias} ON {$fieldname}={$joinalias}.{$joinfield}\n";
}
$offset++;
}
if ($this->context == ExprContext::DOCUMENT) {
$offset = 0;
foreach ($this->metadata as $expr) {
$offset++;
$field = $expr->left();
$fieldid = $field->getFieldId();
$sql .= " LEFT JOIN document_fields_link dfl{$offset} ON dfl{$offset}.metadata_version_id=d.metadata_version_id AND dfl{$offset}.document_field_id={$fieldid}" . "\n";
$sql .= " LEFT JOIN document_fields df{$offset} ON df{$offset}.id=dfl{$offset}.document_field_id" . "\n";
}
}
// Add permissions sql for read access
$oPermission =& KTPermission::getByName('ktcore.permissions.read');
$permId = $oPermission->getID();
$oUser = User::get($_SESSION['userID']);
$aPermissionDescriptors = KTPermissionUtil::getPermissionDescriptorsForUser($oUser);
$sPermissionDescriptors = empty($aPermissionDescriptors) ? -1 : implode(',', $aPermissionDescriptors);
$sql .= "INNER JOIN permission_lookups AS PL ON {$primaryAlias}.permission_lookup_id = PL.id\n";
$sql .= 'INNER JOIN permission_lookup_assignments AS PLA ON PL.id = PLA.permission_lookup_id AND PLA.permission_id = ' . $permId . " \n";
$sql .= "WHERE PLA.permission_descriptor_id IN ({$sPermissionDescriptors}) AND ";
if ($this->context == ExprContext::DOCUMENT) {
$sql .= "d.linked_document_id is null";
if ($this->incl_status) {
$sql .= " AND dmv.status_id=1 AND d.status_id=1";
}
} else {
$sql .= "f.linked_folder_id is null";
}
$sql .= ' AND ';
return $sql;
}
/**
* Generates the necessary joins and where clause and parameters to
* ensure that all the documents returns are accessible to the user
* given for the permission listed.
*
* Returns a list of the following elements:
* - String representing the where clause
* - Array of parameters that go with the where clause
* - String with the SQL necessary to join with the tables in the
* where clause
*/
function permissionToSQL($oUser, $sPermissionName, $sItemTableName = "D")
{
if (is_null($oUser)) {
return array("", array(), "");
}
if (is_null($sPermissionName)) {
$sPermissionName = 'ktcore.permissions.read';
}
$oPermission =& KTPermission::getByName($sPermissionName);
$sPermissionLookupsTable = KTUtil::getTableName('permission_lookups');
$sPermissionLookupAssignmentsTable = KTUtil::getTableName('permission_lookup_assignments');
$sPermissionDescriptorsTable = KTUtil::getTableName('permission_descriptors');
$sJoinSQL = "\n INNER JOIN {$sPermissionLookupsTable} AS PL ON {$sItemTableName}.permission_lookup_id = PL.id\n INNER JOIN {$sPermissionLookupAssignmentsTable} AS PLA ON PL.id = PLA.permission_lookup_id AND PLA.permission_id = ?\n ";
$aPermissionDescriptors = KTPermissionUtil::getPermissionDescriptorsForUser($oUser);
if (count($aPermissionDescriptors) === 0) {
return PEAR::raiseError(_kt('You have no permissions'));
}
$sPermissionDescriptors = DBUtil::paramArray($aPermissionDescriptors);
$sSQLString = "PLA.permission_descriptor_id IN ({$sPermissionDescriptors})";
$aParams = array($oPermission->getId());
$aParams = kt_array_merge($aParams, $aPermissionDescriptors);
return array($sSQLString, $aParams, $sJoinSQL);
}
function resolveSearchShortcuts($result)
{
$oPermission =& KTPermission::getByName('ktcore.permissions.read');
$permId = $oPermission->getID();
$oUser = User::get($_SESSION['userID']);
$aPermissionDescriptors = KTPermissionUtil::getPermissionDescriptorsForUser($oUser);
$sPermissionDescriptors = empty($aPermissionDescriptors) ? -1 : implode(',', $aPermissionDescriptors);
$documentIds = implode(',', array_keys($result['docs']));
$linkedDocuments = array();
if (!empty($documentIds)) {
$sql = "SELECT d.id, d.linked_document_id from documents d ";
$sql .= 'INNER JOIN permission_lookups AS PL ON d.permission_lookup_id = PL.id ' . "\n";
$sql .= 'INNER JOIN permission_lookup_assignments AS PLA ON PL.id = PLA.permission_lookup_id AND PLA.permission_id = ' . $permId . " \n";
$sql .= " WHERE d.linked_document_id in ({$documentIds}) AND PLA.permission_descriptor_id IN ({$sPermissionDescriptors})";
$rs = DBUtil::getResultArray($sql);
foreach ($rs as $row) {
$id = $row['id'];
$linked_id = $row['linked_document_id'];
$result['shortdocs'][$id] = new DocumentShortcutResultItem($id, $result['docs'][$linked_id]);
}
}
$folderIds = implode(',', array_keys($result['folders']));
$linkedFolders = array();
if (!empty($folderIds)) {
$sql = "SELECT f.id, f.linked_folder_id from folders f ";
$sql .= 'INNER JOIN permission_lookups AS PL ON f.permission_lookup_id = PL.id ' . "\n";
$sql .= 'INNER JOIN permission_lookup_assignments AS PLA ON PL.id = PLA.permission_lookup_id AND PLA.permission_id = ' . $permId . " \n";
$sql .= " WHERE f.linked_folder_id in ({$folderIds}) AND PLA.permission_descriptor_id IN ({$sPermissionDescriptors})";
$rs = DBUtil::getResultArray($sql);
foreach ($rs as $row) {
$id = $row['id'];
$linked_id = $row['linked_folder_id'];
$result['shortfolders'][$id] = new FolderShortcutResultItem($id, $result['folders'][$linked_id]);
}
}
return $result;
}
/**
* Finds folders that aren't reachable by the user but to which the
* user has read permissions.
*
* Returns an array of Folder objects.
*/
function getBrowseableFolders($oUser)
{
$aPermissionDescriptors = KTPermissionUtil::getPermissionDescriptorsForUser($oUser);
if (empty($aPermissionDescriptors)) {
return array();
}
$sPermissionDescriptors = DBUtil::paramArray($aPermissionDescriptors);
$oPermission = KTPermission::getByName('ktcore.permissions.read');
$oPermission2 = KTPermission::getByName('ktcore.permissions.folder_details');
$aPermissionIds = array($oPermission->getId(), $oPermission->getId(), $oPermission2->getId(), $oPermission2->getId());
$sFoldersTable = KTUtil::getTableName('folders');
$sPLTable = KTUtil::getTableName('permission_lookups');
$sPLATable = KTUtil::getTableName('permission_lookup_assignments');
$sQuery = "SELECT DISTINCT F.id AS id FROM\n {$sFoldersTable} AS F\n LEFT JOIN {$sPLTable} AS PL ON F.permission_lookup_id = PL.id\n LEFT JOIN {$sPLATable} AS PLA ON PLA.permission_lookup_id = PL.id AND (PLA.permission_id = ? || PLA.permission_id = ?)\n\n LEFT JOIN {$sFoldersTable} AS F2 ON F.parent_id = F2.id\n LEFT JOIN {$sPLTable} AS PL2 ON F2.permission_lookup_id = PL2.id\n LEFT JOIN {$sPLATable} AS PLA2 ON PLA2.permission_lookup_id = PL2.id AND (PLA2.permission_id = ? || PLA.permission_id = ?)\n WHERE\n PLA.permission_descriptor_id IN ({$sPermissionDescriptors})\n AND F2.id <> 1\n AND NOT (PLA2.permission_descriptor_id IN ({$sPermissionDescriptors}))";
$aParams = kt_array_merge($aPermissionIds, $aPermissionDescriptors, $aPermissionDescriptors);
$res = DBUtil::getResultArrayKey(array($sQuery, $aParams), 'id');
if (PEAR::isError($res)) {
return $res;
}
$aFolders = array();
foreach ($res as $iFolderId) {
$aFolders[] = Folder::get($iFolderId);
}
return $aFolders;
}
private function buildCoreSQL()
{
if (count($this->metadata) + count($this->db) == 0) {
// return empty result set
return '';
}
$sql = 'SELECT ' . "\n";
if ($this->context == ExprContext::DOCUMENT) {
// we are doing this because content table is dependant on metadata table
if ($this->used_tables['document_content_version'] > 0) {
$this->used_tables['document_metadata_version']++;
}
$sql .= ' DISTINCT d.id, dmv.name as title';
} else {
$sql .= ' DISTINCT f.id, f.name as title';
}
$offset = 0;
foreach ($this->db as $expr) {
$offset++;
$sql .= ", ifnull(" . $this->getSQLEvalExpr($expr) . ",0) as expr{$offset} ";
}
foreach ($this->metadata as $expr) {
$offset++;
$sql .= ", ifnull(" . $this->getSQLEvalExpr($expr) . ",0) as expr{$offset} ";
}
$sql .= "\n" . 'FROM ' . "\n";
if ($this->context == ExprContext::DOCUMENT) {
$primaryAlias = 'd';
$sql .= ' documents d ' . "\n";
if ($this->used_tables['document_metadata_version'] > 0) {
$sql .= ' INNER JOIN document_metadata_version dmv ON d.metadata_version_id=dmv.id' . "\n";
}
if ($this->used_tables['document_content_version'] > 0) {
$sql .= ' INNER JOIN document_content_version dcv ON dmv.content_version_id=dcv.id ' . "\n";
}
if ($this->used_tables['document_fields_link'] > 0) {
$sql .= ' LEFT JOIN document_fields_link pdfl ON dmv.id=pdfl.metadata_version_id ' . "\n";
}
if ($this->used_tables['tag_words'] > 0) {
$sql .= ' LEFT OUTER JOIN document_tags dt ON dt.document_id=d.id ' . "\n" . ' LEFT OUTER JOIN tag_words tw ON dt.tag_id = tw.id ' . "\n";
}
} else {
$primaryAlias = 'f';
$sql .= ' folders f ' . "\n";
}
$offset = 0;
foreach ($this->db as $expr) {
$field = $expr->left();
$jointable = $field->getJoinTable();
if (!is_null($jointable)) {
$fieldname = $this->resolveTableToAlias($field->getTable()) . '.' . $field->getField();
$joinalias = "{$jointable}{$offset}";
$joinfield = $field->getJoinField();
$sql .= " LEFT OUTER JOIN {$jointable} {$joinalias} ON {$fieldname}={$joinalias}.{$joinfield}\n";
}
$offset++;
}
if ($this->context == ExprContext::DOCUMENT) {
$offset = 0;
foreach ($this->metadata as $expr) {
$offset++;
$field = $expr->left();
$fieldid = $field->getFieldId();
$sql .= " LEFT JOIN document_fields_link dfl{$offset} ON dfl{$offset}.metadata_version_id=d.metadata_version_id AND dfl{$offset}.document_field_id={$fieldid}" . "\n";
$sql .= " LEFT JOIN document_fields df{$offset} ON df{$offset}.id=dfl{$offset}.document_field_id" . "\n";
}
}
// Add permissions sql for read access
$oPermission =& KTPermission::getByName('ktcore.permissions.read');
$permId = $oPermission->getID();
$oUser = User::get($_SESSION['userID']);
$aPermissionDescriptors = KTPermissionUtil::getPermissionDescriptorsForUser($oUser);
$sPermissionDescriptors = empty($aPermissionDescriptors) ? -1 : implode(',', $aPermissionDescriptors);
$sql .= "INNER JOIN permission_lookups AS PL ON {$primaryAlias}.permission_lookup_id = PL.id\n";
$sql .= 'INNER JOIN permission_lookup_assignments AS PLA ON PL.id = PLA.permission_lookup_id AND PLA.permission_id = ' . $permId . " \n";
$sql .= "WHERE PLA.permission_descriptor_id IN ({$sPermissionDescriptors}) AND ";
if ($this->context == ExprContext::DOCUMENT) {
$sql .= "d.linked_document_id is null";
if ($this->incl_status) {
$sql .= " AND dmv.status_id=1 AND d.status_id=1";
}
} else {
$sql .= "f.linked_folder_id is null";
}
$sql .= ' AND ';
return $sql;
}
