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