/*
* Copyright (c) 2006-2007 Massachusetts General Hospital
* All rights reserved. This program and the accompanying materials
* are made available under the terms of the i2b2 Software License v1.0
* which accompanies this distribution.
*
* Contributors:
* Lori Phillips
*/
package edu.harvard.i2b2.workplace.dao;
import java.io.IOException;
import java.sql.Clob;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;
import java.util.List;
import java.util.Date;
import javax.sql.DataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.jdom.JDOMException;
import org.jdom.input.SAXBuilder;
import org.jdom.output.DOMOutputter;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;
import edu.harvard.i2b2.common.exception.I2B2DAOException;
import edu.harvard.i2b2.common.exception.I2B2Exception;
import edu.harvard.i2b2.common.util.db.JDBCUtil;
import edu.harvard.i2b2.common.util.jaxb.DTOFactory;
import edu.harvard.i2b2.common.util.xml.XMLUtil;
import edu.harvard.i2b2.workplace.datavo.crc.setfinder.query.CrcXmlResultResponseType;
import edu.harvard.i2b2.workplace.datavo.crc.setfinder.query.MasterInstanceResultResponseType;
import edu.harvard.i2b2.workplace.datavo.crc.setfinder.query.MasterResponseType;
import edu.harvard.i2b2.workplace.datavo.crc.setfinder.query.RequestXmlType;
import edu.harvard.i2b2.workplace.datavo.crc.setfinder.query.ResultResponseType;
import edu.harvard.i2b2.workplace.datavo.i2b2message.SecurityType;
import edu.harvard.i2b2.workplace.datavo.pm.ProjectType;
import edu.harvard.i2b2.workplace.datavo.wdo.AnnotateChildType;
import edu.harvard.i2b2.workplace.datavo.wdo.ChildType;
import edu.harvard.i2b2.workplace.datavo.wdo.DeleteChildType;
import edu.harvard.i2b2.workplace.datavo.wdo.ExportChildType;
import edu.harvard.i2b2.workplace.datavo.wdo.FolderType;
import edu.harvard.i2b2.workplace.datavo.wdo.GetChildrenType;
import edu.harvard.i2b2.workplace.datavo.wdo.GetReturnType;
import edu.harvard.i2b2.workplace.datavo.wdo.ProtectedType;
import edu.harvard.i2b2.workplace.datavo.wdo.RenameChildType;
import edu.harvard.i2b2.workplace.datavo.wdo.FindByChildType;
import edu.harvard.i2b2.workplace.datavo.wdo.XmlValueType;
import edu.harvard.i2b2.workplace.delegate.crc.CallCRCUtil;
import edu.harvard.i2b2.workplace.ejb.DBInfoType;
import edu.harvard.i2b2.workplace.util.StringUtil;
import edu.harvard.i2b2.workplace.util.WorkplaceUtil;
public class FolderDao extends JdbcDaoSupport {
private static Log log = LogFactory.getLog(FolderDao.class);
// final static String CORE = " c_hierarchy, c_hlevel, c_name, c_user_id, c_group_id, c_share_id, c_index, c_parent_index, c_visualattributes, c_tooltip";
// final static String DEFAULT = " c_name, c_hierarchy";
final static String CORE = " c_name, c_user_id, c_group_id, c_protected_access, c_share_id, c_index, c_parent_index, c_visualattributes, c_tooltip";
final static String DEFAULT = " c_name, c_index, c_protected_access ";
final static String ALL = CORE + ", c_entry_date, c_change_date, c_status_cd";
final static String BLOB = ", c_work_xml, c_work_xml_schema, c_work_xml_i2b2_type ";
private SimpleJdbcTemplate jt;
private void setDataSource(String dataSource) {
DataSource ds = null;
try {
ds = WorkplaceUtil.getInstance().getDataSource(dataSource);
} catch (I2B2Exception e2) {
log.error(e2.getMessage());;
}
this.jt = new SimpleJdbcTemplate(ds);
}
private String getMetadataSchema() throws I2B2Exception{
return WorkplaceUtil.getInstance().getMetaDataSchemaName();
}
public List findRootFoldersByProject(final GetReturnType returnType, final String userId, final ProjectType projectInfo, final DBInfoType dbInfo) throws DataAccessException, I2B2Exception{
// find return parameters
String parameters = CORE;
if (returnType.getType().equals("core")){
parameters = CORE;
}
/* else if (returnType.getType().equals("all")){
parameters = ALL;
}
*/
String metadataSchema = dbInfo.getDb_fullSchema();
setDataSource(dbInfo.getDb_dataSource());
// 1. check if user already has a folder
// if not create one.
check_addRootNode(metadataSchema, userId, projectInfo, dbInfo);
// First step is to call PM to see what roles user belongs to.
if (projectInfo.getRole().size() == 0)
{
log.error("no role found for this user in project: " + projectInfo.getName());
I2B2Exception e = new I2B2Exception("No role found for user");
throw e;
}
boolean protectedAccess = false;
Iterator it = projectInfo.getRole().iterator();
while (it.hasNext()){
String role = (String) it.next();
if(role.toLowerCase().equalsIgnoreCase("DATA_PROT")) {
protectedAccess = true;
break;
}
}
ParameterizedRowMapper<FolderType> mapper = getMapper(returnType.getType(), false, null, dbInfo.getDb_serverType());
List queryResult = null;
if (!protectedAccess){
String tablesSql = "select distinct(c_table_cd), " + parameters + " from " + metadataSchema + "workplace_access where c_protected_access = ? and LOWER(c_group_id) = ? order by c_name"; //c_hierarchy";
try {
queryResult = jt.query(tablesSql, mapper, "N", projectInfo.getId().toLowerCase());
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database error");
}
}
else{
String tablesSql = "select distinct(c_table_cd), " + parameters + " from " + metadataSchema + "workplace_access where LOWER(c_group_id) = ? order by c_name"; //c_hierarchy";
try {
queryResult = jt.query(tablesSql, mapper, projectInfo.getId().toLowerCase());
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database Error");
}
}
log.debug("result size = " + queryResult.size());
return queryResult;
}
public List findRootFoldersByUser(final GetReturnType returnType, final String userId, final ProjectType projectInfo, final DBInfoType dbInfo) throws DataAccessException, I2B2Exception{
// find return parameters
String parameters = CORE;
if (returnType.getType().equals("core")){
parameters = CORE;
}
/* else if (returnType.getType().equals("all")){
parameters = ALL;
}
*/
String metadataSchema = dbInfo.getDb_fullSchema();
setDataSource(dbInfo.getDb_dataSource());
// 1. check if user already has a folder
// if not create one.
check_addRootNode(metadataSchema, userId, projectInfo, dbInfo);
if (projectInfo.getRole().size() == 0)
{
log.error("no role found for this user in project: " + projectInfo.getName());
I2B2Exception e = new I2B2Exception("No role found for user");
throw e;
}
boolean protectedAccess = false;
Iterator it = projectInfo.getRole().iterator();
while (it.hasNext()){
String role = (String) it.next();
if(role.toLowerCase().equalsIgnoreCase("DATA_PROT")) {
protectedAccess = true;
break;
}
}
ParameterizedRowMapper<FolderType> mapper = getMapper(returnType.getType(), false, null, dbInfo.getDb_serverType());
List queryResult = null;
if (!protectedAccess){
String tablesSql = "select distinct(c_table_cd), " + parameters + " from " + metadataSchema + "workplace_access where (c_share_id = 'Y' and LOWER(c_group_id) = ?) or (c_protected_access = ? and LOWER(c_user_id) = ? and LOWER(c_group_id) = ?) order by c_name"; //c_hierarchy";
// log.info(tablesSql);
try {
queryResult = jt.query(tablesSql, mapper, projectInfo.getId().toLowerCase(), "N",userId.toLowerCase(), projectInfo.getId().toLowerCase());
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database error");
}
}
else{
String tablesSql = "select distinct(c_table_cd), " + parameters + " from " + metadataSchema + "workplace_access where (c_share_id = 'Y' and LOWER(c_group_id) = ?) or (LOWER(c_user_id) = ? and LOWER(c_group_id) = ?) order by c_name"; //c_hierarchy";
try {
queryResult = jt.query(tablesSql, mapper, projectInfo.getId().toLowerCase(), userId.toLowerCase(), projectInfo.getId().toLowerCase());
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database Error");
}
}
/*
ParameterizedRowMapper<String> map = new ParameterizedRowMapper<String>() {
public String mapRow(ResultSet rs, int rowNum) throws SQLException {
String name = "\\\\" + rs.getString("c_table_cd") + "\\" + rs.getString("c_table_name");
return name;
}
};
if(queryResult.size() == 0){
// this means that user is accessing for first time
// grab tableCd tableName pair
// and then insert an entry for the user
String tablesSql = "select distinct(c_table_cd), c_table_name from " + metadataSchema + "workplace_access";
try {
queryResult = jt.query(tablesSql, map);
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database Error");
}
if(queryResult.size() == 0)
throw new I2B2DAOException("Database Error");
else{
queryResult= addRootNode((String)queryResult.get(0), userId, projectInfo, dbInfo);
}
}*/
log.debug("result size = " + queryResult.size());
return queryResult;
}
public void check_addRootNode(String metadataSchema, String userId, ProjectType projectInfo, DBInfoType dbInfo) throws I2B2DAOException, I2B2Exception{
String entriesSql = "select c_name from " + metadataSchema + "workplace_access where LOWER(c_user_id) = ? and LOWER(c_group_id) = ?";
ParameterizedRowMapper<String> map = new ParameterizedRowMapper<String>() {
public String mapRow(ResultSet rs, int rowNum) throws SQLException {
String name = rs.getString("c_name") ;
return name;
}
};
List queryResult = null;
try {
queryResult = jt.query(entriesSql, map, userId.toLowerCase(), projectInfo.getId().toLowerCase());
} catch (DataAccessException e1) {
// TODO Auto-generated catch block
log.error(e1.getMessage());
throw new I2B2DAOException("Database Error");
}
// log.info("check for root node size = " + queryResult.size());
if(queryResult.size() > 0)
return;
// else queryResult is empty
// need to create a new entry for user
//1. get ProtectedAccess status for user
if (projectInfo.getRole().size() == 0)
{
log.error("no role found for this user in project: " + projectInfo.getName());
I2B2Exception e = new I2B2Exception("No role found for user");
throw e;
}
String protectedAccess = "N";
Iterator it = projectInfo.getRole().iterator();
while (it.hasNext()){
String role = (String) it.next();
if(role.toLowerCase().equals("protected_access")) {
protectedAccess = "Y";
break;
}
}
// 2. Get tableCd tableName info
String tableSql = "select distinct(c_table_cd), c_table_name from " + metadataSchema + "workplace_access";
ParameterizedRowMapper<String> map2 = new ParameterizedRowMapper<String>() {
public String mapRow(ResultSet rs, int rowNum) throws SQLException {
String name = "\\\\" + rs.getString("c_table_cd") + "\\" + rs.getString("c_table_name");
return name;
}
};
queryResult = jt.query(tableSql, map2);
String tableInfo = (String)queryResult.get(0);
//extract table code and table name
String tableCd = StringUtil.getTableCd(tableInfo);
String tableName = StringUtil.getIndex(tableInfo);
String addSql = "insert into " + metadataSchema+ "workplace_access " +
"(c_table_cd, c_table_name, c_hlevel, c_protected_access, c_name, c_user_id, c_index, c_visualattributes, c_share_id, c_group_id, c_entry_date) values (?,?,?,?,?,?,?,?,?,?,?)";
int numRootsAdded = -1;
String index = StringUtil.generateMessageId();
try {
numRootsAdded = jt.update(addSql, tableCd, tableName, 0, protectedAccess,
userId, userId,index, "CA", "N", projectInfo.getId(), Calendar.getInstance().getTime());
} catch (DataAccessException e) {
log.error("Dao addChild failed");
log.error(e.getMessage());
throw new I2B2DAOException("Data access error " , e);
}
// log.info(addSql + " " + numRowsAdded);
log.debug("Number of roots added: " + numRootsAdded);
return;
}
public String exportNode(final ExportChildType childrenType, ProjectType projectInfo, SecurityType securityType) throws I2B2DAOException, I2B2Exception{
// find return parameters
String type = "core";
String parameters = CORE;
if (projectInfo.getRole().size() == 0)
{
log.error("no role found for this user in project: " + projectInfo.getName());
I2B2Exception e = new I2B2Exception("No role found for user");
throw e;
}
boolean protectedAccess = false;
Iterator it = projectInfo.getRole().iterator();
while (it.hasNext()){
String role = (String) it.next();
if(role.toLowerCase().equalsIgnoreCase("DATA_PROT")) {
protectedAccess = true;
break;
}
}
String queryResult = null;
// Get the query master for the actual query run
if (childrenType.getType().equalsIgnoreCase("QM")) {
try {
log.debug("Start to get QM results from CRC");
//CallCRCUtil callCRC = new CallCRCUtil(securityType, projectInfo.getId());
log.debug("getting Response");
queryResult = CallCRCUtil.callCRCQueryRequestXML(childrenType.getNode(), securityType, projectInfo.getId());
log.debug("got response: " + queryResult);
//if (masterInstanceResultResponseType != null && masterInstanceResultResponseType.getQueryMaster().size() > 0)
// queryResult =XMLUtil.convertDOMElementToString((Element) masterInstanceResultResponseType.getQueryMaster().get(0).getRequestXml().getContent().get(0)); ; //respoonseType.getQueryResultInstance();
} catch (Exception e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database Error");
}
//log.debug("result size = " + queryResult.size());
// Get the analysis breakdowns
} else if (childrenType.getType().equalsIgnoreCase("QR")) {
try {
log.debug("Start to get QR results from CRC");
//CallCRCUtil callCRC = new CallCRCUtil(securityType, projectInfo.getId());
log.debug("getting Response");
queryResult = CallCRCUtil.callCRCResultInstanceXML(childrenType.getNode(), securityType, projectInfo.getId());
log.debug("got response: " + queryResult);
//if (masterInstanceResultResponseType != null)
// queryResult = (String) masterInstanceResultResponseType.getCrcXmlResult().getXmlValue().getContent().get(0);
//XMLUtil.convertDOMElementToString((Element) masterInstanceResultResponseType.getCrcXmlResult().getXmlValue().getContent().get(0)); //respoonseType.getQueryResultInstance();
//queryResult = jt.query(sql, mapper, parentIndex );
} catch (Exception e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database Error");
}
//log.debug("result size = " + queryResult.size());
}
log.debug("result is: " + queryResult);
return queryResult;
}
public List findChildrenByParent(final GetChildrenType childrenType, ProjectType projectInfo, DBInfoType dbInfo) throws I2B2DAOException, I2B2Exception{
// find return parameters
String type = "core";
String parameters = CORE;
if (childrenType.getType().equals("all")){
parameters = ALL;
type = "all";
}
if(childrenType.isBlob() == true)
parameters = parameters + BLOB;
String metadataSchema = dbInfo.getDb_fullSchema();
setDataSource(dbInfo.getDb_dataSource());
if (projectInfo.getRole().size() == 0)
{
log.error("no role found for this user in project: " + projectInfo.getName());
I2B2Exception e = new I2B2Exception("No role found for user");
throw e;
}
boolean protectedAccess = false;
Iterator it = projectInfo.getRole().iterator();
while (it.hasNext()){
String role = (String) it.next();
if(role.toLowerCase().equalsIgnoreCase("DATA_PROT")) {
protectedAccess = true;
break;
}
}
ParameterizedRowMapper<String> map = new ParameterizedRowMapper<String>() {
public String mapRow(ResultSet rs, int rowNum) throws SQLException {
String name = (rs.getString("c_table_name"));
return name;
}
};
//extract table code
String tableCd = StringUtil.getTableCd(childrenType.getParent());
// log.debug(tableCd);
String tableName=null;
if (!protectedAccess){
String tableSql = "select distinct(c_table_name) from " + metadataSchema + "workplace_access where c_table_cd = ? and c_protected_access = ? ";
//log.info("getChildren " + tableSql + tableCd);
try {
tableName = jt.queryForObject(tableSql, map, tableCd, "N");
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database Error");
}
}else {
String tableSql = "select distinct(c_table_name) from " + metadataSchema + "workplace_access where c_table_cd = ?";
try {
tableName = jt.queryForObject(tableSql, map, tableCd);
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database Error");
}
}
String hidden = "";
if(childrenType.isHiddens() == false)
hidden = " and c_visualattributes not like '_H%'";
String sql = "select " + parameters +" from " + metadataSchema+tableName + " where c_parent_index = ? and (c_status_cd != 'D' or c_status_cd is null)";
sql = sql + hidden + " order by c_name ";
String parentIndex = StringUtil.getIndex(childrenType.getParent());
log.debug(sql + " " + parentIndex);
// log.info(type + " " + tableCd );
ParameterizedRowMapper<FolderType> mapper = getMapper(type, childrenType.isBlob(), tableCd, dbInfo.getDb_serverType());
List queryResult = null;
try {
queryResult = jt.query(sql, mapper, parentIndex );
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database Error");
}
log.debug("result size = " + queryResult.size());
return queryResult;
// tested statement with aqua data studio verified output from above against this.
// select c_fullname, c_name, c_synonym_cd, c_visualattributes from metadata.testrpdr
// where c_fullname like '\RPDR\Diagnoses\Circulatory system (390-459)\Arterial vascular disease (440-447)\(446) Polyarteritis nodosa and al%'
// and c_hlevel = 5 and c_visualattributes not like '_H%' and c_synonym_cd = 'N'
// verified both with and without hiddens and synonyms.
// clob test level = 4
// <parent>\\testrpdr\RPDR\HealthHistory\PHY\Health Maintenance\Mammogram\Mammogram - Deferred</parent>
}
/**
* This method finds the workplace with a given keyword. It first searches the WORKPLACE ACCESS table
* to find the table where the workplace contents are stored. And then it searches the resulting table
* for any content with given name and other parameters
*
* @param returnType
* @param userId
* @param projectInfo
* @param dbInfo
* @return
* @throws DataAccessException
* @throws I2B2Exception
*
* @author Neha Patel
*/
public List findWorkplaceByKeyword(final FindByChildType returnType, String userId, final ProjectType projectInfo, final DBInfoType dbInfo) throws DataAccessException, I2B2Exception{
// find return parameters
String type = "core"; // Default Type is core
String parameters = CORE; // parameters to be used in select statement
String category = "";
String hiddenStr = "";
String maxString = "";
String searchWord = "";
if (returnType!=null){
// determines which columns should be used in select statement
if(returnType.getType().equals("all")){
parameters = ALL;
type = "all";
}
// if request parameter blob is set to true then include
// columns with xml info in select statement :-
// c_work_xml, c_work_xml_schema, c_work_xml_i2b2_type
if(returnType.isBlob() == true)
parameters = parameters + BLOB;
// category is the directory where user is looking for the content
category= returnType.getCategory();
// request parameter hidden indicates to display hidden files or not
if(returnType.isHiddens() == false)
hiddenStr = " and c_visualattributes not like '_H%'";
// get strategy if content name starts with given word
// or it contains given word or it ends with given word
if(returnType.getMatchStr().getStrategy().equals("exact")) {
searchWord = returnType.getMatchStr().getValue().toLowerCase();
}
else if(returnType.getMatchStr().getStrategy().equals("left")){
searchWord = returnType.getMatchStr().getValue().toLowerCase()+ "%";
}
else if(returnType.getMatchStr().getStrategy().equals("right")) {
searchWord ="%"+ returnType.getMatchStr().getValue().toLowerCase();
}
else if(returnType.getMatchStr().getStrategy().equals("contains")) {
searchWord = "%" + returnType.getMatchStr().getValue().toLowerCase() + "%";
}
try {
// setting max number of rows to be returned
if(returnType.getMax() !=null && returnType.getMax()>0){
if(dbInfo!=null){
int fetchSize = returnType.getMax() +1 ;
// if server is oracle then use rownum to return max number of rows
if(dbInfo.getDb_serverType().toUpperCase().equals("ORACLE"))
maxString = " and rownum>0 and rownum <=" + fetchSize;
// if server is SQL SERVER then use 'TOP' clause to return max number of rows
else if(dbInfo.getDb_serverType().toUpperCase().equals("SQLSERVER")){
maxString = "TOP " + fetchSize + " ";
parameters = maxString + parameters; // appended maxstring infront of parameters
maxString = "";
}
//else if(dbInfo.getDb_serverType().toUpperCase().equals("POSTGRESQL"))
// maxString = " limit " + fetchSize;
}
}
}
catch( Exception e){
log.error(e);
}
}
if (projectInfo.getRole().size() == 0)
{
log.error("no role found for this user in project: " + projectInfo.getName());
I2B2Exception e = new I2B2Exception("No role found for user");
throw e;
}
boolean protectedAccess = false;
Iterator it = projectInfo.getRole().iterator();
while (it.hasNext()){
String role = (String) it.next();
if(role.toLowerCase().equalsIgnoreCase("DATA_PROT")) {
protectedAccess = true;
break;
}
}
if(returnType.getCategory().trim().equalsIgnoreCase("@")){
return findInAll(returnType, projectInfo, type, parameters, hiddenStr, maxString, searchWord, protectedAccess, dbInfo, userId );
}
else {
return findInCategory(returnType, projectInfo, type, parameters, category, hiddenStr, maxString, searchWord, dbInfo, protectedAccess);
}
}
/**
* This method finds the search word in the given category.
*
* @param returnType
* @param projectInfo
* @param type - parameters to be used in the select statement. Can have two values core or all
* @param parameters
* @param category
* @param hiddenStr - string to be used in where clause
* @param maxString - string to be used in select or where clause
* @param searchWord
* @param dbInfo
* @param protectedAccess
* @return
* @throws I2B2DAOException
*
* @author Neha Patel
*/
private List findInCategory(final FindByChildType returnType, final ProjectType projectInfo, String type, String parameters, String category, String hiddenStr, String maxString,
String searchWord, final DBInfoType dbInfo, boolean protectedAccess) throws I2B2DAOException {
String metadataSchema = dbInfo.getDb_fullSchema();
setDataSource(dbInfo.getDb_dataSource());
ParameterizedRowMapper<String> map = new ParameterizedRowMapper<String>() {
public String mapRow(ResultSet rs, int rowNum) throws SQLException {
String name = "\\\\" + rs.getString("c_table_cd") + "\\" + rs.getString("c_table_name");
return name;
}
};
// Getting tablename where the content is saved from table 'WORKPLACE'
List queryResult = null;
String resultStr=null;
StringBuilder sqlToRetreiveTableNm = new StringBuilder( "select distinct c_table_cd, c_table_name from " + metadataSchema + "workplace_access where LOWER(c_user_id) = ? and LOWER(c_group_id) = ?");
if (!protectedAccess){
sqlToRetreiveTableNm.append(" and c_protected_access = ? ");
try {
queryResult = jt.query(sqlToRetreiveTableNm.toString(), map, category.toLowerCase(), projectInfo.getId().toLowerCase(), "N");
resultStr = (String)queryResult.get(0);
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("findWorkplaceByKeyword(): Database Error while accessing workplace_access table with protected access");
}
}else {
try {
queryResult = jt.query(sqlToRetreiveTableNm.toString(), map, category.toLowerCase(), projectInfo.getId().toLowerCase());
resultStr = (String)queryResult.get(0);
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("findWorkplaceByKeyword(): Database Error while accessing workplace_access table");
}
}
String tableCd = StringUtil.getTableCd(resultStr);
String tableName = StringUtil.getIndex(resultStr);
StringBuilder sql = new StringBuilder ("select " + parameters +" from " + metadataSchema+tableName + " where LOWER(c_user_id) = ? and LOWER(c_group_id) = ? and LOWER(c_name) like ? and (c_status_cd != 'D' or c_status_cd is null) ");
sql.append( hiddenStr + maxString );
ParameterizedRowMapper<FolderType> mapper = getMapper(type, returnType.isBlob(), tableCd, dbInfo.getDb_serverType());
/*
* commenting out protectedAcess code from workplace table for now
*
if (!protectedAccess){
sql.append(" and (c_protected_access != 'Y' or c_protected_access is null) ");
}
*/
sql.append( " order by c_name ");
// Executing the query to find the workplace content with the given name
queryResult=null;
try {
queryResult = jt.query(sql.toString(), mapper, category.toLowerCase(), projectInfo.getId().toLowerCase(), searchWord );
} catch (DataAccessException e) {
log.error(e.getMessage());
log.error("findWorkplaceByKeyword(): Database Error while accessing workplace table");
throw new I2B2DAOException("findWorkplaceByKeyword(): Database Error while accessing workplace table");
}
log.debug("result size = " + queryResult.size());
return queryResult;
}
/**
* This method searches for the word in the whole project. If the user has a manager role then it searches in the whole project
* if the user doesn't have manager role then it searches with the condition of userid or share = Y
*
* @param returnType
* @param projectInfo
* @param type
* @param parameters
* @param hiddenStr
* @param maxString
* @param searchWord
* @param protectedAccess
* @param dbInfo
* @param userId
* @return
* @throws DataAccessException
* @throws I2B2Exception
*
* @author Neha Patel
*/
private List findInAll(final FindByChildType returnType, final ProjectType projectInfo, String type, String parameters, String hiddenStr, String maxString,
String searchWord, boolean protectedAccess, final DBInfoType dbInfo, final String userId) throws DataAccessException, I2B2Exception{
// Check if user is a manager
boolean managerRole = false;
for(String param :projectInfo.getRole()) {
if(param.equalsIgnoreCase("manager")) {
managerRole = true;
break;
}
}
String metadataSchema = dbInfo.getDb_fullSchema();
setDataSource(dbInfo.getDb_dataSource());
ParameterizedRowMapper<String> map = new ParameterizedRowMapper<String>() {
public String mapRow(ResultSet rs, int rowNum) throws SQLException {
String name = "\\\\" + rs.getString("c_table_cd") + "\\" + rs.getString("c_table_name");
return name;
}
};
// Getting tablename where the content is saved from table 'WORKPLACE'
List queryResult = null;
String resultStr=null;
StringBuilder sqlToRetreiveTableNm = new StringBuilder( "select distinct c_table_cd, c_table_name from " + metadataSchema + "workplace_access where ");
// if user is a manager then search in the whole project
if(managerRole){
sqlToRetreiveTableNm.append("LOWER(c_group_id) = ? ");
}
else {
// if user is not a manager then user should be able to search only in his folder or shared folder of the project
sqlToRetreiveTableNm.append("(LOWER(c_user_id) = ? and LOWER(c_group_id) = ?) or (LOWER(c_group_id) = ? and c_share_id = 'Y') ");
}
if (!protectedAccess){
sqlToRetreiveTableNm.append(" and (c_protected_access = 'N' or c_protected_access is null) ");
}
try {
if(managerRole){
queryResult = jt.query(sqlToRetreiveTableNm.toString(), map, projectInfo.getId().toLowerCase());
}
else {
queryResult = jt.query(sqlToRetreiveTableNm.toString(), map, userId.toLowerCase(), projectInfo.getId().toLowerCase(), projectInfo.getId().toLowerCase());
}
resultStr = (String)queryResult.get(0);
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("findWorkplaceByKeyword(): Database Error while accessing workplace_access table with protected access");
}
String tableCd = "";
String tableName = "";
List returnResult = null;
// Run the query for each tablename. There could be more than one table where workplace content is stored
if(queryResult != null && !queryResult.isEmpty()){
Iterator itr = queryResult.iterator();
while(itr.hasNext()){
resultStr = (String)itr.next();
tableCd = StringUtil.getTableCd(resultStr);
tableName = StringUtil.getIndex(resultStr);
StringBuilder sql = new StringBuilder ("select " + parameters +" from " + metadataSchema+tableName + " where LOWER(c_name) like ? and (c_status_cd != 'D' or c_status_cd is null) ");
if(managerRole){
sql.append("and LOWER(c_group_id) = ? ");
}
else {
sql.append("and ((LOWER(c_user_id) = ? and LOWER(c_group_id) = ?) or (LOWER(c_group_id) = ? and c_share_id = 'Y')) ");
}
sql.append( hiddenStr + maxString );
ParameterizedRowMapper<FolderType> mapper = getMapper(type, returnType.isBlob(), tableCd, dbInfo.getDb_serverType());
/*
* commenting out protectedAcess code from workplace table for now
*
if (!protectedAccess){
sql.append(" and (c_protected_access != 'Y' or c_protected_access is null) ");
}
*/
sql.append( " order by c_name ");
// Executing the query to find the workplace content with the given name
List workplaceResult=null;
try {
if(managerRole){
workplaceResult = jt.query(sql.toString(), mapper, searchWord, projectInfo.getId().toLowerCase() );
}
else {
workplaceResult = jt.query(sql.toString(), mapper, searchWord, userId.toLowerCase(), projectInfo.getId().toLowerCase(), projectInfo.getId().toLowerCase() );
}
} catch (DataAccessException e) {
log.error(e.getMessage());
log.error("findWorkplaceByKeyword(): Database Error while accessing workplace table");
throw new I2B2DAOException("findWorkplaceByKeyword(): Database Error while accessing workplace table");
}
if(returnResult==null){
returnResult=workplaceResult;
}
else {
returnResult.addAll(workplaceResult);
}
} // end while (itr.hasNext())
}
log.debug("result size = " + returnResult.size());
return returnResult;
}
/**
* This method determines if the given category(workplace root folder name) is shared or not by
* checking c_share_id parameter in workplace_access table
*
* @param category - the root folder name which is in question if its shared or not
* @param projectInfo
* @param dbInfo
* @return
* @throws DataAccessException
* @throws I2B2Exception
*
* @author Neha Patel
*/
public boolean isShared(String category, final ProjectType projectInfo, final DBInfoType dbInfo) throws DataAccessException, I2B2Exception{
boolean isSharedBool= false;
String metadataSchema = dbInfo.getDb_fullSchema();
setDataSource(dbInfo.getDb_dataSource());
ParameterizedRowMapper<String> map = new ParameterizedRowMapper<String>() {
public String mapRow(ResultSet rs, int rowNum) throws SQLException {
String name = rs.getString("c_share_id") ;
return name;
}
};
// Getting column 'c_share_id' to check if the given category/folder is shared
List queryResult = null;
String resultStr="";
String sqlForCheckingShared = "select c_share_id from " + metadataSchema + "workplace_access where LOWER(c_user_id) = ? and LOWER(c_group_id) = ? and (c_status_cd != 'D' or c_status_cd is null)";
try {
queryResult = jt.query(sqlForCheckingShared.toString(), map, category.toLowerCase(), projectInfo.getId().toLowerCase());
if(queryResult !=null && !queryResult.isEmpty()){
resultStr = (String)queryResult.get(0);
}
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("isShared(): Database Error while accessing workplace_access table");
}
if(resultStr!=null && resultStr.toUpperCase().trim().equals("Y")){
isSharedBool = true;
}
else
isSharedBool = false;
return isSharedBool;
}
public int renameNode(final RenameChildType renameChildType, ProjectType projectInfo, DBInfoType dbInfo) throws I2B2DAOException, I2B2Exception{
String metadataSchema = dbInfo.getDb_fullSchema();
setDataSource(dbInfo.getDb_dataSource());
if (projectInfo.getRole().size() == 0)
{
log.error("no role found for this user in project: " + projectInfo.getName());
I2B2Exception e = new I2B2Exception("No role found for user");
throw e;
}
boolean protectedAccess = false;
Iterator it = projectInfo.getRole().iterator();
while (it.hasNext()){
String role = (String) it.next();
if(role.toLowerCase().equalsIgnoreCase("DATA_PROT")) {
protectedAccess = true;
break;
}
}
ParameterizedRowMapper<String> map = new ParameterizedRowMapper<String>() {
public String mapRow(ResultSet rs, int rowNum) throws SQLException {
String name = (rs.getString("c_table_name"));
return name;
}
};
//extract table code
String tableCd = StringUtil.getTableCd(renameChildType.getNode());
// table code to table name conversion
String tableName=null;
if (!protectedAccess){
String tableSql = "select distinct(c_table_name) from " + metadataSchema + "workplace_access where c_table_cd = ? and c_protected_access = ? ";
try {
tableName = jt.queryForObject(tableSql, map, tableCd, "N");
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database Error");
}
}else {
String tableSql = "select distinct(c_table_name) from " + metadataSchema + "workplace_access where c_table_cd = ?";
try {
tableName = jt.queryForObject(tableSql, map, tableCd);
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database Error");
}
}
String index = StringUtil.getIndex(renameChildType.getNode());
// get original name and work xml
String sql = "select c_name, c_work_xml, c_work_xml_i2b2_type from " + metadataSchema + tableName + " where c_index = ? ";
ParameterizedRowMapper<FolderType> map2 = new ParameterizedRowMapper<FolderType>() {
public FolderType mapRow(ResultSet rs, int rowNum) throws SQLException {
FolderType child = new FolderType();
child.setName(rs.getString("c_name"));
// child.setTooltip(rs.getString("c_tooltip"));
child.setWorkXmlI2B2Type(rs.getString("c_work_xml_i2b2_type"));
// Clob xml_clob = rs.getClob("c_work_xml");
// try {
// if(xml_clob != null){
// String c_xml = JDBCUtil.getClobString(xml_clob);
// // Log log2 = LogFactory.getLog(FolderDao.class);
// // log2.debug("CLOB STRING TO CHECK");
// // log2.debug(c_xml);
// if ((c_xml!=null)&&(c_xml.trim().length()>0)&&(!c_xml.equals("(null)")))
// {
// Element rootElement = null;
// try{
// Document doc = XMLUtil.convertStringToDOM(c_xml);
// rootElement = doc.getDocumentElement();
// } catch (I2B2Exception e) {
// log.error(e.getMessage());
// child.setWorkXml(null);
// }
// if (rootElement != null) {
// /* try {
// log2.debug("ROOT ELEMENT TO CHECK");
// String test = XMLUtil.convertDOMElementToString(rootElement);
// log2.debug(test);
// } catch (Exception e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }*/
//
// if(child.getWorkXmlI2B2Type().equals("CONCEPT") ) {
// NodeList nameElements = rootElement.getElementsByTagName("name");
// nameElements.item(0).setTextContent(renameChildType.getName());
//
// NodeList synonymElements = rootElement.getElementsByTagName("synonym_cd");
// if(synonymElements.item(0) != null)
// synonymElements.item(0).setTextContent("Y");
//
// }
// XmlValueType xml = new XmlValueType();
// xml.getAny().add(rootElement);
// child.setWorkXml(xml);
// }
// }
// }else {
// child.setWorkXml(null);
// }
// } catch (IOException e1) {
// log.error(e1.getMessage());
// child.setWorkXml(null);
// }
return child;
}
};
List queryResult = null;
try {
queryResult = jt.query(sql, map2, index);
} catch (DataAccessException e) {
log.error("Dao queryResult failed");
log.error(e.getMessage());
throw new I2B2DAOException("Data access error " , e);
}
FolderType node = (FolderType)queryResult.get(0);
// String newTooltip = StringUtil.replaceEnd(node.getTooltip(),node.getName(), renameChildType.getName());
// log.info(newTooltip);
int numRowsRenamed = -1;
if(node.getWorkXmlI2B2Type().equals("FOLDER")){
String updateSql = "update " + metadataSchema+tableName + " set c_name = ? where c_index = ? ";
try {
numRowsRenamed = jt.update(updateSql, renameChildType.getName(),index);
} catch (DataAccessException e) {
log.error("Dao renameChild failed");
log.error(e.getMessage());
throw new I2B2DAOException("Data access error " , e);
}
}
else {
String updateSql = "update " + metadataSchema+tableName + " set c_name = ? where c_index = ? ";
/*
String newXml = null;
// Element newXmlElement = node.getWorkXml().getAny().get(0);
Element newXmlElement = renameChildType.getWorkXml().getAny().get(0);
if(newXmlElement != null){
newXml = XMLUtil.convertDOMElementToString(newXmlElement);
// log.debug(newXml);
}
*/
try {
numRowsRenamed = jt.update(updateSql, renameChildType.getName(), index);
} catch (DataAccessException e) {
log.error("Dao renameChild failed");
log.error(e.getMessage());
throw new I2B2DAOException("Data access error " , e);
}
}
log.debug("Number of rows renamed: " + numRowsRenamed);
return numRowsRenamed;
}
public int moveNode(final ChildType childType, ProjectType projectInfo, DBInfoType dbInfo) throws I2B2DAOException, I2B2Exception{
String metadataSchema = dbInfo.getDb_fullSchema();
setDataSource(dbInfo.getDb_dataSource());
if (projectInfo.getRole().size() == 0)
{
log.error("no role found for this user in project: " + projectInfo.getName());
I2B2Exception e = new I2B2Exception("No role found for user");
throw e;
}
boolean protectedAccess = false;
Iterator it = projectInfo.getRole().iterator();
while (it.hasNext()){
String role = (String) it.next();
if(role.toLowerCase().equalsIgnoreCase("DATA_PROT")) {
protectedAccess = true;
break;
}
}
ParameterizedRowMapper<String> map = new ParameterizedRowMapper<String>() {
public String mapRow(ResultSet rs, int rowNum) throws SQLException {
String name = (rs.getString("c_table_name"));
return name;
}
};
//extract table code
String tableCd = StringUtil.getTableCd(childType.getNode());
// table code to table name conversion
String tableName=null;
if (!protectedAccess){
String tableSql = "select distinct(c_table_name) from " + metadataSchema + "workplace_access where c_table_cd = ? and c_protected_access = ? ";
try {
tableName = jt.queryForObject(tableSql, map, tableCd, "N");
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database Error");
}
}else {
String tableSql = "select distinct(c_table_name) from " + metadataSchema + "workplace_access where c_table_cd = ?";
try {
tableName = jt.queryForObject(tableSql, map, tableCd);
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database Error");
}
}
String index = StringUtil.getIndex(childType.getNode());
String updateSql = "update " + metadataSchema+tableName + " set c_parent_index = ? where c_index = ? ";
int numRowsMoved = -1;
try {
numRowsMoved = jt.update(updateSql, childType.getParent(), index);
} catch (DataAccessException e) {
log.error("Dao moveChild failed");
log.error(e.getMessage());
throw new I2B2DAOException("Data access error " , e);
}
// log.info(updateSql + " " + path + " " + numRowsAnnotated);
log.debug("Number of rows moved: " + numRowsMoved);
return numRowsMoved;
}
public int annotateNode(final AnnotateChildType annotateChildType, ProjectType projectInfo, DBInfoType dbInfo) throws I2B2DAOException, I2B2Exception{
String metadataSchema = dbInfo.getDb_fullSchema();
setDataSource(dbInfo.getDb_dataSource());
if (projectInfo.getRole().size() == 0)
{
log.error("no role found for this user in project: " + projectInfo.getName());
I2B2Exception e = new I2B2Exception("No role found for user");
throw e;
}
boolean protectedAccess = false;
Iterator it = projectInfo.getRole().iterator();
while (it.hasNext()){
String role = (String) it.next();
if(role.toLowerCase().equalsIgnoreCase("DATA_PROT")) {
protectedAccess = true;
break;
}
}
ParameterizedRowMapper<String> map = new ParameterizedRowMapper<String>() {
public String mapRow(ResultSet rs, int rowNum) throws SQLException {
String name = (rs.getString("c_table_name"));
return name;
}
};
//extract table code
String tableCd = StringUtil.getTableCd(annotateChildType.getNode());
// table code to table name conversion
String tableName=null;
if (!protectedAccess){
String tableSql = "select distinct(c_table_name) from " + metadataSchema + "workplace_access where c_table_cd = ? and c_protected_access = ? ";
try {
tableName = jt.queryForObject(tableSql, map, tableCd, "N");
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database Error");
}
}else {
String tableSql = "select distinct(c_table_name) from " + metadataSchema + "workplace_access where c_table_cd = ?";
try {
tableName = jt.queryForObject(tableSql, map, tableCd);
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database Error");
}
}
String index = StringUtil.getIndex(annotateChildType.getNode());
String updateSql = "update " + metadataSchema+tableName + " set c_tooltip = ? where c_index = ? ";
int numRowsAnnotated = -1;
try {
numRowsAnnotated = jt.update(updateSql, annotateChildType.getTooltip(), index);
} catch (DataAccessException e) {
log.error("Dao annotateChild failed");
log.error(e.getMessage());
throw new I2B2DAOException("Data access error " , e);
}
// log.info(updateSql + " " + path + " " + numRowsAnnotated);
log.debug("Number of rows annotated: " + numRowsAnnotated);
return numRowsAnnotated;
}
public int addNode(final FolderType addChildType, ProjectType projectInfo, DBInfoType dbInfo) throws I2B2DAOException, I2B2Exception{
String metadataSchema = dbInfo.getDb_fullSchema();
setDataSource(dbInfo.getDb_dataSource());
if (projectInfo.getRole().size() == 0)
{
log.error("no role found for this user in project: " + projectInfo.getName());
I2B2Exception e = new I2B2Exception("No role found for user");
throw e;
}
boolean protectedAccess = false;
Iterator it = projectInfo.getRole().iterator();
while (it.hasNext()){
String role = (String) it.next();
if(role.toLowerCase().equalsIgnoreCase("DATA_PROT")) {
protectedAccess = true;
break;
}
}
ParameterizedRowMapper<String> map = new ParameterizedRowMapper<String>() {
public String mapRow(ResultSet rs, int rowNum) throws SQLException {
String name = (rs.getString("c_table_name"));
return name;
}
};
//extract table code
String tableCd = StringUtil.getTableCd(addChildType.getParentIndex());
// table code to table name conversion
String tableName=null;
if (!protectedAccess){
String tableSql = "select distinct(c_table_name) from " + metadataSchema + "workplace_access where c_table_cd = ? and c_protected_access = ? ";
try {
tableName = jt.queryForObject(tableSql, map, tableCd, "N");
} catch (DataAccessException e) {
log.error(tableSql + tableCd);
log.error(e.getMessage());
throw new I2B2DAOException("Database Error");
}
}else {
String tableSql = "select distinct(c_table_name) from " + metadataSchema + "workplace_access where c_table_cd = ?";
try {
tableName = jt.queryForObject(tableSql, map, tableCd);
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database Error");
}
}
int numRowsAdded = -1;
try {
String xml = null;
XmlValueType workXml=addChildType.getWorkXml();
if (workXml != null) {
String addSql = "insert into " + metadataSchema+tableName +
"(c_name, c_user_id, c_index, c_parent_index, c_visualattributes, c_group_id, c_share_id, c_tooltip, c_entry_date, c_work_xml, c_work_xml_i2b2_type) values (?,?,?,?,?,?,?,?,?,?,?)";
Element element = workXml.getAny().get(0);
if(element != null)
xml = XMLUtil.convertDOMElementToString(element);
numRowsAdded = jt.update(addSql,
addChildType.getName(), addChildType.getUserId(),addChildType.getIndex(), StringUtil.getIndex(addChildType.getParentIndex()),
addChildType.getVisualAttributes(), addChildType.getGroupId(), addChildType.getShareId(), addChildType.getTooltip(), Calendar.getInstance().getTime(),
xml, addChildType.getWorkXmlI2B2Type());
}
else {
String addSql = "insert into " + metadataSchema+tableName +
"(c_name, c_user_id, c_index, c_parent_index, c_visualattributes, c_group_id, c_share_id, c_tooltip, c_entry_date, c_work_xml_i2b2_type) values (?,?,?,?,?,?,?,?,?,?)";
numRowsAdded = jt.update(addSql,
addChildType.getName(), addChildType.getUserId(),addChildType.getIndex(), StringUtil.getIndex(addChildType.getParentIndex()),
addChildType.getVisualAttributes(), addChildType.getGroupId(), addChildType.getShareId(), addChildType.getTooltip(), Calendar.getInstance().getTime(),
addChildType.getWorkXmlI2B2Type());
}
} catch (DataAccessException e) {
log.error("Dao addChild failed");
log.error(e.getMessage());
throw new I2B2DAOException("Data access error " , e);
}
// log.info(addSql + " " + numRowsAdded);
log.debug("Number of rows added: " + numRowsAdded);
return numRowsAdded;
}
public int deleteNode(final DeleteChildType deleteChildType, ProjectType projectInfo, DBInfoType dbInfo) throws DataAccessException, I2B2Exception{
String metadataSchema = dbInfo.getDb_fullSchema();
String serverType = dbInfo.getDb_serverType();
setDataSource(dbInfo.getDb_dataSource());
if (projectInfo.getRole().size() == 0)
{
log.error("no role found for this user in project: " + projectInfo.getName());
I2B2Exception e = new I2B2Exception("No role found for user");
throw e;
}
boolean protectedAccess = false;
Iterator it = projectInfo.getRole().iterator();
while (it.hasNext()){
String role = (String) it.next();
if(role.toLowerCase().equalsIgnoreCase("DATA_PROT")) {
protectedAccess = true;
break;
}
}
ParameterizedRowMapper<String> map = new ParameterizedRowMapper<String>() {
public String mapRow(ResultSet rs, int rowNum) throws SQLException {
String name = (rs.getString("c_table_name"));
return name;
}
};
//extract table code
String tableCd = StringUtil.getTableCd(deleteChildType.getNode());
// table code to table name conversion
String tableName=null;
if (!protectedAccess){
String tableSql = "select distinct(c_table_name) from " + metadataSchema + "workplace_access where c_table_cd = ? and c_protected_access = ? ";
try {
tableName = jt.queryForObject(tableSql, map, tableCd, "N");
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database Error");
}
}else {
String tableSql = "select distinct(c_table_name) from " + metadataSchema + "workplace_access where c_table_cd = ?";
try {
tableName = jt.queryForObject(tableSql, map, tableCd);
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database Error");
}
}
String index = StringUtil.getIndex(deleteChildType.getNode());
checkForChildrenDeletion(index, tableName, metadataSchema);
//Mark node for deletion
String updateSql = " update " + metadataSchema+tableName + " set c_change_date = ?, c_status_cd = 'D' where c_index = ? ";
log.debug(serverType + "updateSql " + index);
int numRowsDeleted = -1;
try {
// log.info(sql + " " + w_index);
numRowsDeleted = jt.update(updateSql, Calendar.getInstance().getTime(),index);
} catch (DataAccessException e) {
log.error("Dao deleteChild failed");
log.error(e.getMessage());
throw e;
}
log.debug("Number of rows deleted " + numRowsDeleted);
return numRowsDeleted;
}
/**
* This method is to set protected access on a file/folder in workplace
* It first checks if user has correct privileges to the file, that is
* either he she is manager or the file is shared or the file belongs
* to him/her. The it searches for all the folders under the given
* index. if folders are found then it runs the update query atleast
* 3 times to update the root folder in workplace_access table, all the
* child folders in workplace table and all the child content in workplace
* table.
*
* @param requestType
* @param projectInfo
* @param dbInfo
* @param userId
* @return
* @throws I2B2DAOException
* @throws I2B2Exception
*
* @author Neha Patel
*/
public int setProtectedAccess(final ProtectedType requestType, final ProjectType projectInfo, final DBInfoType dbInfo, String userId) throws I2B2DAOException, I2B2Exception{
boolean settingRoot = false;
int numRowsSet = -1;
int numParentUpdated = -1;
int numWorkAccUpdated =-1;
String sharedStr ="";
String contentUserId = "";
String tableName = "";
boolean managerRole = false;
boolean isFolder = true;
String metadataSchema = dbInfo.getDb_fullSchema();
setDataSource(dbInfo.getDb_dataSource());
if (projectInfo.getRole().size() == 0)
{
log.error("no role found for this user in project: " + projectInfo.getName());
I2B2Exception e = new I2B2Exception("No role found for user");
throw e;
}
// Check if user is a manager
for(String param :projectInfo.getRole()) {
if(param.equalsIgnoreCase("manager")) {
managerRole = true;
break;
}
}
ParameterizedRowMapper<String> map = new ParameterizedRowMapper<String>() {
public String mapRow(ResultSet rs, int rowNum) throws SQLException {
String resultRow = "\\tablename=" + rs.getString("c_table_name") + "\\share_id=" + rs.getString("c_share_id") + "\\user_id=" + rs.getString("c_user_id") ;
return resultRow;
}
};
//extract table code and index
String tableCd = StringUtil.getTableCd(requestType.getIndex());
String index = StringUtil.getIndex(requestType.getIndex());
List resultString = null;
StringBuilder sqlToRetrieveTableName = new StringBuilder("select distinct c_table_name, c_share_id, c_user_id from " + metadataSchema
+ "workplace_access where LOWER(c_group_id) = ?");
// Check if the user is setting access for root directory
// by looking for index in the current table
try {
sqlToRetrieveTableName.append(" and c_index = ? ");
resultString = jt.query(sqlToRetrieveTableName.toString(), map,projectInfo.getId().toLowerCase(), index);
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database Error");
}
String resultToSplit= "";
// if the above query returned any result
// that means user was setting access for root directory
if(resultString!=null && !resultString.isEmpty()){
settingRoot = true;
isFolder = true;
// getting tablename, share_id, user_id from the result string
resultToSplit = (String) resultString.get(0);
int indexofShared = resultToSplit.indexOf("\\share_id=");
int indexofUser = resultToSplit.indexOf("\\user_id=");
tableName = resultToSplit.substring(11, indexofShared);
// if its not manager check if the file/folder is shared
// if not shared either, then verify that user is setting
// privilege for his/her file/folder
if(managerRole == false){
sharedStr = resultToSplit.substring(indexofShared+10, indexofUser);
contentUserId = resultToSplit.substring(indexofUser+9);
if(!sharedStr.equalsIgnoreCase("Y")){
if(!contentUserId.equalsIgnoreCase(userId)){
log.debug( "User does not have privileges to set protected access for this content");
return -11111;
}
} // if (sharedStr==null || !sharedStr.equalsIgnoreCase("Y"))
} // if managerRole == false
} //if(resultString!=null && !resultString.isEmpty())
// query result is null that means item doesn't exist in workplace_access table
// or user is not setting access for root directory
// Get tablename using the tablecd given as part of indexString in the request
else if(resultString == null || resultString.isEmpty()){
// replace the last condition of 'and c_index=?' with 'and c_table_cd'
sqlToRetrieveTableName.replace(sqlToRetrieveTableName.lastIndexOf("and"), sqlToRetrieveTableName.length()-1, " and LOWER(c_table_cd) = ? ");
try {
resultString = jt.query(sqlToRetrieveTableName.toString(), map, projectInfo.getId().toLowerCase(), tableCd.toLowerCase());
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database Error");
}
resultToSplit = (String) resultString.get(0);
// getting tablename from the query result
tableName = resultToSplit.substring(11, resultToSplit.indexOf("\\share_id="));
List result;
ParameterizedRowMapper<String> mapTocheckAccess = new ParameterizedRowMapper<String>() {
public String mapRow(ResultSet rs, int rowNum) throws SQLException {
String resultRow = "\\share_id=" + rs.getString("c_share_id") + "\\user_id=" + rs.getString("c_user_id") + "\\type=" + rs.getString("c_work_xml_i2b2_type") ;
return resultRow;
}
};
// Run query in table workplace to find out if the content is shared or does it belong to user
// Also find the type of the file
String sql = "select c_share_id, c_user_id, c_work_xml_i2b2_type from " + metadataSchema + tableName + " where c_index = ? and LOWER(c_group_id) = ?";
try{
result = jt.query(sql, mapTocheckAccess, index, projectInfo.getId().toLowerCase());
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database Error");
}
// get the user id and share_id from result string
resultToSplit = (String) result.get(0);
String type = resultToSplit.substring(resultToSplit.lastIndexOf("\\")+6);
if(!type.equalsIgnoreCase("FOLDER")){
isFolder= false;
}
else
isFolder=true;
// if user is not a manager
// then check if file/folder is shared
// if not shared then verify file/folder belongs to user
if(managerRole == false){
sharedStr = resultToSplit.substring(10, resultToSplit.indexOf("\\user_id="));
contentUserId = resultToSplit.substring(resultToSplit.indexOf("\\user_id=")+9, resultToSplit.lastIndexOf("\\"));
if(sharedStr!=null && !sharedStr.equalsIgnoreCase("Y")){
if(!contentUserId.equalsIgnoreCase(userId)){
log.debug( "User does not have privileges to set protected access for this content");
return -11111;
}
} // if (sharedStr==null || !sharedStr.equalsIgnoreCase("Y"))
} // if managerRole == false
}
StringBuilder indexStr = new StringBuilder();
String protectedAccVal= "";
if(requestType.getProtectedAccess().trim().equalsIgnoreCase("true"))
protectedAccVal = "Y";
else
protectedAccVal = "N";
ArrayList<String> parentIdxList = new ArrayList<String>();
parentIdxList.add(index);
indexStr.append("'" + index + "'");
// if initial request was for a folder only
// then run this part
if(isFolder){
List resultingIndx;
ParameterizedRowMapper<String> mapForIndexes = new ParameterizedRowMapper<String>() {
public String mapRow(ResultSet rs, int rowNum) throws SQLException {
String name = (rs.getString("c_index"));
return name;
}
};
// Get all the parent indexes (folder indexes under the top level directory)
// and store it in an arraylist
String parentIdx = "";
for (int i = 0; i < parentIdxList.size(); i ++){
try {
parentIdx = parentIdxList.get(i);
if(i>0){
indexStr.append(", '" + parentIdx + "'");
}
String sqlToCollectIndex = "select c_index from " + metadataSchema + tableName + " where c_parent_index = ? and LOWER(c_group_id) = ? and c_work_xml_i2b2_type = 'FOLDER'";
resultingIndx = jt.query(sqlToCollectIndex, mapForIndexes, parentIdx, projectInfo.getId().toLowerCase());
} catch (DataAccessException e) {
log.error(e.getMessage());
throw new I2B2DAOException("Database Error");
}
if(resultingIndx != null)
parentIdxList.addAll(resultingIndx);
}
// set the protected access for all the content found under the
// parent indexes stored in the arraylist
numParentUpdated = updateProtectedAccess(metadataSchema,tableName, "c_parent_index" , indexStr.toString(), protectedAccVal);
}
if(settingRoot){
// set the protected access for root directory which is in workplace_access table
numWorkAccUpdated = updateProtectedAccess(metadataSchema,"workplace_access", "c_index",indexStr.toString(), protectedAccVal);
}
// If setting root folder, then set all the folders to protected access
// if setting one item then still use the same query to set that item to protected_access
numRowsSet = updateProtectedAccess(metadataSchema,tableName, "c_index",indexStr.toString(), protectedAccVal);
// Return the correct number of updated rows
if(isFolder)
numRowsSet += numParentUpdated;
if(settingRoot)
numRowsSet += numWorkAccUpdated;
return numRowsSet;
}
/**
* @param numRowsSet
* @param metadataSchema
* @param tableName
* @param indexStr
* @param protectedAccVal
* @return
* @throws I2B2DAOException
*
* @author Neha Patel
*/
private int updateProtectedAccess(String metadataSchema,String tableName, String columnName, String indexStr, String protectedAccVal)
throws I2B2DAOException {
String updateSql = "update " + metadataSchema+tableName + " set c_protected_access = ? where " + columnName + " in ( " + indexStr + " )";
int numRowsSet=-1;
try {
numRowsSet = jt.update(updateSql, protectedAccVal);
} catch (DataAccessException e) {
log.error("Dao updateProtectedAccess failed");
log.error(e.getMessage());
throw new I2B2DAOException("Data access error " , e);
}
return numRowsSet;
}
private void checkForChildrenDeletion(String nodeIndex, String tableName, String metadataSchema) throws DataAccessException {
// mark children for deletion
String updateSql = " update " + metadataSchema+tableName + " set c_change_date = ?, c_status_cd = 'D' where c_parent_index = ? ";
int numChildrenDeleted = -1;
try {
// log.info(sql + " " + w_index);
numChildrenDeleted = jt.update(updateSql, Calendar.getInstance().getTime(),nodeIndex);
} catch (DataAccessException e) {
log.error("Dao deleteChild failed");
log.error(e.getMessage());
throw e;
}
log.debug("Number of children deleted: "+ numChildrenDeleted);
// look for children that are folders
String folderSql = "select c_index from " + metadataSchema+tableName + " where c_parent_index = ? and c_visualattributes like 'F%' ";
ParameterizedRowMapper<String> map = new ParameterizedRowMapper<String>() {
public String mapRow(ResultSet rs, int rowNum) throws SQLException {
String index = (rs.getString("c_index"));
return index;
}
};
List folders = null;
try{
folders = jt.query(folderSql, map, nodeIndex);
} catch (DataAccessException e) {
log.error(e.getMessage());
throw e;
}
// recursively check folders for children to delete
if (folders != null){
Iterator it = folders.iterator();
while(it.hasNext()){
String folderIndex = (String) it.next();
checkForChildrenDeletion(folderIndex, tableName, metadataSchema);
}
}
}
private ParameterizedRowMapper<FolderType> getMapper(final String type, final boolean isBlob, final String tableCd, final String dbType){
ParameterizedRowMapper<FolderType> mapper = new ParameterizedRowMapper<FolderType>() {
public FolderType mapRow(ResultSet rs, int rowNum) throws SQLException {
FolderType child = new FolderType();
//TODO fix this for all/+blob
if (tableCd == null){
// child.setHierarchy("\\\\" + rs.getString("c_table_cd")+ rs.getString("c_hierarchy"));
child.setIndex("\\\\" + rs.getString("c_table_cd")+ "\\" + rs.getString("c_index"));
}
else{
// child.setHierarchy("\\\\" + tableCd + rs.getString("c_hierarchy"));
child.setIndex("\\\\" + tableCd + "\\" + rs.getString("c_index"));
}
// log.debug("getMapper: " + child.getIndex());
child.setName(rs.getString("c_name"));
child.setProtectedAccess(rs.getString("c_protected_access"));
if(!(type.equals("default"))) {
child.setUserId(rs.getString("c_user_id"));
// child.setHlevel(rs.getInt("c_hlevel"));
child.setGroupId(rs.getString("c_group_id"));
child.setVisualAttributes(rs.getString("c_visualattributes"));
// child.setIndex(rs.getString("c_index"));
child.setParentIndex(rs.getString("c_parent_index"));
child.setShareId(rs.getString("c_share_id" ));
// Building tooltip for the response
// eg. project name - cname \n tooltip from db
String toolTip = rs.getString("c_group_id") + " - " + rs.getString("c_name") ;
if(rs.getString("c_tooltip")!=null && !rs.getString("c_tooltip").isEmpty()){
toolTip = toolTip + "\n" + rs.getString("c_tooltip");
}
//child.setTooltip(rs.getString("c_tooltip"));
child.setTooltip(toolTip);
}if(isBlob == true){
child.setWorkXmlI2B2Type(rs.getString("c_work_xml_i2b2_type"));
String c_xml = null;
try {
if (dbType.equals("POSTGRESQL"))
{
c_xml = rs.getString("c_work_xml");
} else
{
c_xml = JDBCUtil.getClobString(rs.getClob("c_work_xml"));
}
if (c_xml != null){
//c_xml = JDBCUtil.getClobString(xml_clob);
if ((c_xml!=null)&&(c_xml.trim().length()>0)&&(!c_xml.equals("(null)")))
{
SAXBuilder parser = new SAXBuilder();
java.io.StringReader xmlStringReader = new java.io.StringReader(c_xml);
Element rootElement = null;
try {
org.jdom.Document metadataDoc = parser.build(xmlStringReader);
org.jdom.output.DOMOutputter out = new DOMOutputter();
Document doc = out.output(metadataDoc);
rootElement = doc.getDocumentElement();
} catch (JDOMException e) {
log.error(e.getMessage());
child.setWorkXml(null);
} catch (IOException e1) {
log.error(e1.getMessage());
child.setWorkXml(null);
}
if (rootElement != null) {
XmlValueType xml = new XmlValueType();
xml.getAny().add(rootElement);
child.setWorkXml(xml);
}
else {
// log.debug("rootElement is null");
child.setWorkXml(null);
}
}else {
// log.debug("work xml is null");
child.setWorkXml(null);
}
}
else {
// log.debug("work xml is null");
child.setWorkXml(null);
}
} catch (Exception e) {
log.error(e.getMessage());
child.setWorkXml(null);
}
try {
Clob xml_schema_clob = rs.getClob("c_work_xml_schema");
if (xml_schema_clob != null){
c_xml = JDBCUtil.getClobString(xml_schema_clob);
if ((c_xml!=null)&&(c_xml.trim().length()>0)&&(!c_xml.equals("(null)")))
{
SAXBuilder parser = new SAXBuilder();
java.io.StringReader xmlStringReader = new java.io.StringReader(c_xml);
Element rootElement = null;
try {
org.jdom.Document metadataDoc = parser.build(xmlStringReader);
org.jdom.output.DOMOutputter out = new DOMOutputter();
Document doc = out.output(metadataDoc);
rootElement = doc.getDocumentElement();
} catch (JDOMException e) {
log.error(e.getMessage());
child.setWorkXmlSchema(null);
} catch (IOException e1) {
log.error(e1.getMessage());
child.setWorkXmlSchema(null);
}
if (rootElement != null) {
XmlValueType xml = new XmlValueType();
xml.getAny().add(rootElement);
child.setWorkXmlSchema(xml);
}
else {
// log.debug("rootElement is null");
child.setWorkXmlSchema(null);
}
}else {
// log.debug("work xml schema is null");
child.setWorkXmlSchema(null);
}
}
else {
// log.debug("work xml schema is null");
child.setWorkXmlSchema(null);
}
} catch (Exception e) {
log.error(e.getMessage());
child.setWorkXmlSchema(null);
}
}
if((type.equals("all"))){
DTOFactory factory = new DTOFactory();
// make sure date isnt null before converting to XMLGregorianCalendar
Date date = rs.getDate("c_entry_date");
if (date == null)
child.setEntryDate(null);
else
child.setEntryDate(factory.getXMLGregorianCalendar(date.getTime()));
date = rs.getDate("c_change_date");
if (date == null)
child.setChangeDate(null);
else
child.setChangeDate(factory.getXMLGregorianCalendar(date.getTime()));
child.setStatusCd(rs.getString("c_status_cd"));
}
return child;
}
};
return mapper;
}
}