/*
ESUP-portail is a french academic project developed under the GPL (General Public License) augmented according to the following :
A binary or source file developped by ESUP-portail can be used or compiled with products under Apache license.
The official english text of the GPL can be found here : http://www.gnu.org/licenses/gpl.html .
A non official french translation can be found here : http://www.linux-France.org/article/these/gpl.html .
The different kinds of licenses governing the products developed by the Apache foundation can be found here : http://www.apache.org/licenses .
It follows that you can as well as use download contents as well modify and redistribute them provided you respect the GPL terms.
Downloading and using such contents do not provide any guaranty.
Be sure that you have well understood the terms of the license before using the contents it covers.
The ESUP-portail distribution includes the following distributions :
    * UPortal : 
      software developed by JA-SIG (Java Architecture - Special Interest Group)
      You can find the license page here : http://mis105.udel.edu/ja-sig/uportal/license.html
    * CAS :
      SSO solution developed by Yale University
      You can find the project page here : http://www.yale.edu/tp/auth
    * Cocoon :
      XML framework distributed by the Apache foundation under Apache license;
      Please find the full text here : http://cocoon.apache.org/2.1/license.html
    * Mod_dav:
      A DAV module for Apache web server
      You can find the project page here : http://www.webdav.org/mod_dav
    * IMP :
      webmail from Horde application framework
      You can find the project page here : http://www.horde.org
    * …. To be completed
*/
package org.esupportail.portal.channels.CStockage.spacesPersonalization;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.ojb.broker.util.GUID;
import org.esupportail.portal.channels.CStockage.config.ChannelConfiguration;
import org.esupportail.portal.channels.CStockage.exception.DataBaseException;
import org.esupportail.portal.channels.CStockage.exception.PropertiesException;
import org.esupportail.portal.utils.database.Database;
import org.esupportail.portal.utils.database.Query;




/**
 * Id: DataBaseAccess.java,v 1.0 13 janv. 2005
 * Copyright (c) 2005 Esup Portail (www.esup-portail.org)
 * Classes: DataBaseAccess
 * Original Author: Yohan Colmant
 * This class manage the access to the database, for the sharing actions.
 */
public class DataBaseAccess {


	/**
	 * Logger object
	 */
	protected static final Log log = LogFactory.getLog(DataBaseAccess.class);
	
	
	
	/**
	 * If the database parameters are still initialized
	 */
	public static boolean initialized = false;
	
	
	
	
	/**
	 * The database access object
	 */
	private static Database dataBaseAccess;
	
	
	public static final String TARGET_IS_USER = "U";
	public static final String TARGET_IS_GROUP = "G";
	public static final String TARGET_IS_ALL = "A";
	public static final String TARGET_ALL = "all";
	

	/**
	 * Constructor
	 * Initialize the parameters for a connexion to the database
	 * @throws PropertiesException
	 */
	public DataBaseAccess() throws PropertiesException {
		
		ChannelConfiguration config = ChannelConfiguration.getInstance();
		
		// init the static parameters
		if (!initialized) {
			dataBaseAccess = config.getPool();
			initialized = true;
		}
		
	}
	
	
	
	
	
	/**
	 * Get the users for whose the resource is shared
	 * @param serverName the name of the server
	 * @return a vector. Each element is an array of [serverId, xmlServer, availableSpaceTargetId, targetKey, targetIsUser:'T'|'F', read:'T'|'F', write:'T'|'F', share:'T'|'F']
	 * @throws DataBaseException
	 */
	public Vector getAvailableSpace(String serverName) throws DataBaseException {
		
		try {		

			// the query manager
			Query queryManager = new Query(dataBaseAccess);
			
			// the query
			String query = QueriesManagement.getQuery(QueriesManagement.GET_AVAILABLE_SPACE);
			
			// prepare statement
			queryManager.setSql(query);			
			
			// the server name
			queryManager.getStmt().setString(1,serverName);
		
			// execute the query
			queryManager.select();
			ResultSet rs = queryManager.getRs();
			
			// the list of results
			Vector results = new Vector();
		
			// get each result
			while (rs.next()) {
		
				// a result line
				String[] line = new String[3];

				// the server id
				String serverId = rs.getString(1);
				line[0] = serverId;

				// the xml_server
				String xmlServer = rs.getString(2);
				line[1] = xmlServer;

				// the owner
				String owner = rs.getString(3);
				line[2] = owner;

				// add to the result
				results.add(line);
			}
	      
			// close the result
			queryManager.close();
	    	
	    	return results;
		}
		catch(SQLException e) {			
			log.error("getTargetsOfAvailableSpace :: "+ e);
			throw new DataBaseException();
		}				
	}
	

	
	/**
	 * Get the users for whose the resource is shared
	 * @param serverName the name of the server
	 * @return a vector. Each element is an array of [serverId, xmlServer, availableSpaceTargetId, targetKey, targetIsUser:'T'|'F', read:'T'|'F', write:'T'|'F', share:'T'|'F']
	 * @throws DataBaseException
	 */
	public Vector getTargetsOfAvailableSpace(String serverName) throws DataBaseException {
		
		try {		

			// the query manager
			Query queryManager = new Query(dataBaseAccess);
			
			// the query
			String query = QueriesManagement.getQuery(QueriesManagement.GET_TARGET_OF_AVAILABLE_SPACE);
			
			// prepare statement
			queryManager.setSql(query);			
			
			// the server name
			queryManager.getStmt().setString(1,serverName);
			
			// execute the query
			queryManager.select();
			ResultSet rs = queryManager.getRs();
			
			// the list of results
			Vector results = new Vector();
			
			// get each result
			while (queryManager.getRs().next()) {
				
				// a result line
				String[] line = new String[6];

				// the server id
				String serverId = rs.getString(1);
				line[0] = serverId;

				// the xml_server
				String xmlServer = rs.getString(2);
				line[1] = xmlServer;

				// the id of storage_available_spaces_targets
				String availableSpaceTargetId = rs.getString(3);
				line[2] = availableSpaceTargetId;
				
				// the target key
				String targetKey = rs.getString(4);
				line[3] = targetKey;

				// the server principal
				String serverPrincipal = rs.getString(5);
				line[4] = serverPrincipal;
				
				// the target is user ?
				String targetType = rs.getString(6);
				line[5] = targetType;	
				
				// add to the result
				results.add(line);
			}
	      
			// close the result
			queryManager.close();
	    	
	    	return results;
		}
		catch(SQLException e) {			
			log.error("getTargetsOfAvailableSpace :: "+ e);
			throw new DataBaseException();
		}				
	}
	
	
	
	/**
	 * Get the users for whose the resource is shared
	 * @param serverId the id of the server
	 * @param spaceId the id of the space
	 * @return a vector containing each target id
	 * @throws DataBaseException
	 */
	public Vector getTargetsOfSpace(String serverId, String spaceId) throws DataBaseException {
		
		try {		

			// the query manager
			Query queryManager = new Query(dataBaseAccess);
			
			// the query
			String query = QueriesManagement.getQuery(QueriesManagement.GET_TARGETS_OF_SPACE);
			
			// prepare statement
			queryManager.setSql(query);			
			
			// the server id
			queryManager.getStmt().setString(1,serverId);

			// the space id
			queryManager.getStmt().setString(2,spaceId);
			
			// execute the query
			queryManager.select();
			ResultSet rs = queryManager.getRs();
			
			// the list of results
			Vector results = new Vector();
			
			// get each result
			while (queryManager.getRs().next()) {
				
				// the target id
				String targetId = rs.getString(1);
				
				// add to the result
				results.add(targetId);
			}
	      
			// close the result
			queryManager.close();
	    	
	    	return results;
		}
		catch(SQLException e) {			
			log.error("getTargetsOfSpace :: "+ e);
			throw new DataBaseException();
		}				
	}
	
	

	/**
	 * Check if the space is already shared for a specified user
	 * @param queryManager the query manager for the database access
	 * @param serverId the id of the server
	 * @return true if already shared, else false
	 * @throws DataBaseException
	 */
	private boolean isAlreadyAvailable(Query queryManager, String serverId) throws DataBaseException {
			
		try {

			// the query
			String query = QueriesManagement.getQuery(QueriesManagement.IS_ALREADY_AVAILABLE);
			
			// prepare statement
			queryManager.setSql(query);
	    	
			// the server id
			queryManager.getStmt().setString(1,serverId);

			// execute the query
			queryManager.select();
	    	
			// is shared ?
			boolean isShared = queryManager.getRs().next();

			// close the result
			//queryManager.close();
	    				
			return isShared;
		}
		catch(SQLException e) {			
			log.error("isAlreadyAvailable :: "+ e);
			throw new DataBaseException();
		}		
	}


	/**
	 * Check if the space is already shared for a specified user
	 * @param queryManager the query manager for the database access. can be null
	 * @param serverId the id of the server
	 * @param targetKey the key of the user we want to check
	 * @param targetType to know if the target of this sharing is a user or other
	 * @param spaceKey the key of the space
	 * @return true if already shared, else false
	 * @throws DataBaseException
	 */
	public boolean isAlreadyAvailableForTarget(Query queryManager, String serverId, String targetKey, String targetType, String spaceKey) throws DataBaseException {
			
		try {

			// the local query manager
			Query localQueryManager = null;
			if (queryManager!=null) {
				localQueryManager = queryManager;
			}
			else {
				localQueryManager = new Query(dataBaseAccess);
			}
			
			// the query
			String query = QueriesManagement.getQuery(QueriesManagement.IS_ALREADY_AVAILABLE_FOR_TARGET);
			
			// prepare statement
			localQueryManager.setSql(query);
	    	
			// the server id
			localQueryManager.getStmt().setString(1,serverId);
			
			// the target user key
			localQueryManager.getStmt().setString(2,targetKey);

			// the target is a user or a group
			localQueryManager.getStmt().setString(3, targetType);
			
			// the space key
			localQueryManager.getStmt().setString(4,spaceKey);

			// execute the query
			localQueryManager.select();
	    	
			// is shared ?
			boolean isShared = localQueryManager.getRs().next();

			// close the result
			//queryManager.close();
			if (queryManager==null) {
				localQueryManager.close();
			}
	    				
			return isShared;
		}
		catch(SQLException e) {			
			log.error("isAlreadyAvailableForTarget :: "+ e);
			throw new DataBaseException();
		}		
	}
	
	
	
	
	
	
	/**
	 * Add sharing properties into the database
	 * @param id the id of the server
	 * @param serverName the name of the server
	 * @param xmlServer the server into an xml format
	 * @param fromKey the key of the people who shares
	 * @return the result of the query
	 * @throws DataBaseException
	 */
	public int addStorageAvailableSpace(String id, String serverName, String xmlServer, String fromKey, String owner, String rootPath) throws DataBaseException {
		
		try {
			
			// the query manager
			Query queryManager = new Query(dataBaseAccess);

			
			// check if it is still shared
			boolean isAvailable = isAlreadyAvailable(queryManager, id);
			if (isAvailable) {
				return 1;
			}
		
			// the SQL query
			String query = QueriesManagement.getQuery(QueriesManagement.ADD_STORAGE_AVAILABLE_SPACE);		
			
			// prepare the SQL query	    	   		    	    	
			queryManager.setSql(query);
			
			// the id
			queryManager.getStmt().setString(1, id);
			
			// the server name
			queryManager.getStmt().setString(2, serverName);
			
			// the xmlServer
			queryManager.getStmt().setString(3, xmlServer);
			
			// the from key
			queryManager.getStmt().setString(4, fromKey);

			// the owner
			queryManager.getStmt().setString(5, owner);

			// the root path
			queryManager.getStmt().setString(6, rootPath);
			
			// execute the query			
			int res = queryManager.update();
			
			// close
			queryManager.close();
			
			// return the result
			return res;
			
		}
		catch(SQLException e) {
			log.error("addStorageAvailableSpace :: "+ e);
			throw new DataBaseException();
		}
	}
	


	/**
	 * Add sharing properties into the database
	 * @param targetKey the key of the target user
	 * @param targetType if the target is a user or not
	 * @param serverId theid of the server
	 * @param spaceKey the key of the space
	 * @param obliged if we are obliged to see this space
	 * @return the result of the query
	 * @throws DataBaseException
	 */
	public int addStorageAvailableSpaceTarget(String targetKey, String serverPrincipal, String targetType, String serverId, String spaceKey, boolean obliged) throws DataBaseException {
		
		
		try {
			
			// the query manager
			Query queryManager = new Query(dataBaseAccess);
			
			// check if it is still shared
			boolean isShared = isAlreadyAvailableForTarget(queryManager, serverId, targetKey, targetType, spaceKey);
			if (isShared)
				return 1;
		
			
			// the SQL query
			String query = QueriesManagement.getQuery(QueriesManagement.ADD_STORAGE_AVAILABLE_SPACE_TARGET);		
			
			// prepare the SQL query	    	   		    	    	
			queryManager.setSql(query);

			// the id
			String id = new GUID().toString();
			queryManager.getStmt().setString(1, id);
			
			// the target key
			queryManager.getStmt().setString(2,targetKey);	

			// the server principal
			queryManager.getStmt().setString(3,serverPrincipal);	

			// the target is a user or a group
			queryManager.getStmt().setString(4, targetType);
			
			// the server id
			queryManager.getStmt().setString(5,serverId);

			// the space key
			queryManager.getStmt().setString(6,spaceKey);

			// obliged
			if (obliged)
				queryManager.getStmt().setString(7, "T");
    		else queryManager.getStmt().setString(7, "F");

			
			// execute the query			
			int res = queryManager.update();
    	
			// close
			queryManager.close();
			
			// return the result
			return res;
			
		}
		catch(SQLException e) {
			e.printStackTrace();
			log.error("addStorageAvailableSpaceTarget :: "+ e);
			throw new DataBaseException();
		}
	}
	
	
	
	
	

	/**
	 * Delete sharing properties into the database
	 * @param id the space id
	 * @return the result of the query
	 * @throws DataBaseException
	 */
	public int delStorageAvailableSpaceTarget(String id) throws DataBaseException {

		try {

			// the query manager
			Query queryManager = new Query(dataBaseAccess);			
			
			// the SQL query
			String query = QueriesManagement.getQuery(QueriesManagement.DEL_STORAGE_AVAILABLE_SPACE_TARGET);		
			
			// prepare the SQL query	    	   		    	    	
			queryManager.setSql(query);

			// the server url
			queryManager.getStmt().setString(1,id);

			// execute the query			
			int res = queryManager.update();
    	
			// close
			queryManager.close();
			
			// return the result
			return res;
		}
		catch(SQLException e) {
			log.error("delStorageAvailableSpaceTarget :: "+ e);
			throw new DataBaseException();
		}
	}
	
	
	
	/**
	 * Delete sharing properties into the database
	 * @param serverId the server id
	 * @param spaceId the space id
	 * @return the result of the query
	 * @throws DataBaseException
	 */
	public int delStorageAvailableSpaceTargetAll(String serverId, String spaceId) throws DataBaseException {

		try {

			// the query manager
			Query queryManager = new Query(dataBaseAccess);			
			
			// the SQL query
			String query = QueriesManagement.getQuery(QueriesManagement.DEL_STORAGE_AVAILABLE_SPACE_TARGET_ALL);		
			
			// prepare the SQL query	    	   		    	    	
			queryManager.setSql(query);

			// the server id
			queryManager.getStmt().setString(1,serverId);

			// the space id
			queryManager.getStmt().setString(2,spaceId);
			
			// execute the query			
			int res = queryManager.update();
			
			// close
			queryManager.close();
			
			// return the result
			return res;
		}
		catch(SQLException e) {
			log.error("delStorageAvailableSpaceTargetAll :: "+ e);
			throw new DataBaseException();
		}
	}
	
	
	
	/**
	 * Delete sharing properties into the database
	 * @param id the space id
	 * @return the result of the query
	 * @throws DataBaseException
	 */
	public int delStorageAvailableSpace(String id) throws DataBaseException {

		try {

			// the query manager
			Query queryManager = new Query(dataBaseAccess);			
			
			// the SQL query
			String query = QueriesManagement.getQuery(QueriesManagement.DEL_STORAGE_AVAILABLE_SPACE);		
			
			// prepare the SQL query	    	   		    	    	
			queryManager.setSql(query);

			// the server url
			queryManager.getStmt().setString(1,id);

			// execute the query			
			int res = queryManager.update();
    	
			// close
			queryManager.close();
			
			// return the result
			return res;
		}
		catch(SQLException e) {
			log.error("delStorageAvailableSpace :: "+ e);
			throw new DataBaseException();
		}
	}
	
	

	
	
	
	/**
	 * Delete users preferences for a shared space from the database
	 * @param id the id of the sharement in the other database
	 * @return the result of the query
	 * @throws DataBaseException
	 */
	public int delStorageUsersPreferences(String id) throws DataBaseException {
		//System.out.println(id+" for "+userKey);
		try {

			// the query manager
			Query queryManager = new Query(dataBaseAccess);
			
			// the SQL query
			String query = QueriesManagement.getQuery(QueriesManagement.DEL_STORAGE_USERS_PREFERENCES);		
			
			// prepare the SQL query	    	   		    	    	
			queryManager.setSql(query);

			// the id
			queryManager.getStmt().setString(1,id);

			// execute the query
			int res = queryManager.update();

			// close
			queryManager.close();
			
			// return the result
			return res;	
		}
		catch(SQLException e) {
			log.error("delStorageUsersPreferences :: "+ e);
			throw new DataBaseException();
		}
	}
	

	/**
	 * Update the "reading" and "writing" attributes for a specifical sharing
	 * @param reading the reading value 'T' or 'F'
	 * @param writing the writing value 'T' or 'F'
	 * @param sharing the manage value 'T' or 'F'
	 * @param obliged the obliged value 'T' or 'F'
	 * @param id the id of the server
	 * @return the result of the query
	 * @throws DataBaseException
	 */
	/*public int updateReadAndWriteAndShareSpace(boolean reading, boolean writing, boolean sharing, boolean obliged, String id) throws DataBaseException {

		try {

			// the query manager
			Query queryManager = new Query(dataBaseAccess);
			
			// the SQL query
			String query = QueriesManagement.getQuery(QueriesManagement.UPDATE_READ_AND_WRITE_AND_SHARE_SPACE);		
			
			// prepare the SQL query	    	   		    	    	
			queryManager.setSql(query);

			// the reading
			if (reading)
				queryManager.getStmt().setString(1, "T");
			else queryManager.getStmt().setString(1, "F");			
			
			// the writing
			if (writing)
				queryManager.getStmt().setString(2, "T");
			else queryManager.getStmt().setString(2, "F");

			// the sharing
			if (sharing)
				queryManager.getStmt().setString(3, "T");
			else queryManager.getStmt().setString(3, "F");

			// the obliged
			if (obliged)
				queryManager.getStmt().setString(4, "T");
			else queryManager.getStmt().setString(4, "F");
			
			// the id
			queryManager.getStmt().setString(5,id);
			
			// execute the query
			int res = queryManager.update();

			// close
			queryManager.close();
			
			// return the result
			return res;	
		}
		catch(SQLException e) {
			log.error("updateReadAndWriteAndManageSpaceUser :: "+ e);
			throw new DataBaseException();
		}
	}*/
	
	
	
	
	
	
	
	

	
	

	
	/**
	 * Update the label of the sharing properties into the database
	 * @param xmlServer the xml of the server
	 * @param id the id of the server
	 * @return the result of the query
	 * @throws DataBaseException
	 */
	public int updateXmlServerAvailableSpace(String xmlServer, String id) throws DataBaseException {
		
		try {

			// the query manager
			Query queryManager = new Query(dataBaseAccess);			
			
			// the SQL query
			String query = QueriesManagement.getQuery(QueriesManagement.UPDATE_XML_SERVER_AVAILABLE_SPACE);		
			
			// prepare the SQL query	    	   		    	    	
			queryManager.setSql(query);

			// the xml server
			queryManager.getStmt().setString(1, xmlServer);
			
			// the id
			queryManager.getStmt().setString(2, id);
			
			// execute the query
			int res = queryManager.update();

			// close
			queryManager.close();
			
			// return the result
			return res;	
		}
		catch(SQLException e) {
			log.error("updateXmlServerAvailableSpace :: "+ e);
			throw new DataBaseException();
		}
	}
	
	

	

	/**
	 * Get the id of shared spaces from url and path
	 * @param serverName the name of the server
	 * @return the list of ids in 2 vectors: the first for the storage_available_spaces and the other for storage_available_spaces_targets
	 * @throws DataBaseException
	 */
	public Vector[] getIdOfSpacesWithServerName(String serverName) throws DataBaseException {

		try {

			// the query manager
			Query queryManager = new Query(dataBaseAccess);
			
			// the SQL query
			String query = QueriesManagement.getQuery(QueriesManagement.GET_ID_OF_SPACES_WITH_SERVER_NAME);
			
			// prepare the SQL query	    	   		    	    	
			queryManager.setSql(query);

			// the url
			queryManager.getStmt().setString(1, serverName);

			// execute the query
			queryManager.select();
			ResultSet rs = queryManager.getRs();
    	
			// the ids
			Vector id_storage_available_spaces = new Vector();
			Vector id_storage_available_spaces_targets = new Vector();
			while (rs.next()) {
				
				String id_storage_available_space = rs.getString(1);
				if (!id_storage_available_spaces.contains(id_storage_available_space))
					id_storage_available_spaces.add(id_storage_available_space);
				
				String id_storage_available_spaces_target = rs.getString(2);
				if (!id_storage_available_spaces_targets.contains(id_storage_available_spaces_target))
					id_storage_available_spaces_targets.add(id_storage_available_spaces_target);
			}

			// close
			queryManager.close();
			
			// return the result
			Vector[] res = new Vector[2];
			res[0] = id_storage_available_spaces;
			res[1] = id_storage_available_spaces_targets;
			return res;	
		}
		catch(SQLException e) {
			log.error("getIdOfSpacesWithServerName :: "+ e);
			throw new DataBaseException();
		}
	}

	
	
	
	

	/**
	 * Get the id of shared spaces from url and path
	 * @param serverName the name of the server
	 * @return the list of ids and xml in 2 vectors
	 * @throws DataBaseException
	 */
	public Vector[] getIdAndXmlOfSharing(String serverName) throws DataBaseException {

		try {

			// the query manager
			Query queryManager = new Query(dataBaseAccess);
			
			// the SQL query
			String query = QueriesManagement.getQuery(QueriesManagement.GET_ID_AND_XML_OF_SHARING);
			
			// prepare the SQL query	    	   		    	    	
			queryManager.setSql(query);

			// the url
			queryManager.getStmt().setString(1, serverName);

			// execute the query
			queryManager.select();
			ResultSet rs = queryManager.getRs();
    	
			// the ids
			Vector ids = new Vector();
			Vector xmls = new Vector();
			while (rs.next()) {
				
				String id = rs.getString(1);
				ids.add(id);
				
				String xml = rs.getString(2);
				xmls.add(xml);
			}

			// close
			queryManager.close();
			
			// return the result
			Vector[] res = new Vector[2];
			res[0] = ids;
			res[1] = xmls;
			return res;	
		}
		catch(SQLException e) {
			log.error("getIdAndXmlOfSharing :: "+ e);
			throw new DataBaseException();
		}
	}

	
	
	
	/**
	 * Update the path of shared spaces
	 * @param serverName the name of the server
	 * @param xml the xml for the server
	 * @param id the name of the server
	 * @return the result of the query
	 * @throws DataBaseException
	 */
	public int updatePathOfSharing(String serverName, String xml, String id) throws DataBaseException {

		try {

			// the query manager
			Query queryManager = new Query(dataBaseAccess);
			
			// the SQL query
			String query = QueriesManagement.getQuery(QueriesManagement.UPDATE_PATH_OF_SHARING);		
			
			// prepare the SQL query	    	   		    	    	
			queryManager.setSql(query);

			// the new name
			queryManager.getStmt().setString(1, serverName);
			
			// the xml
			queryManager.getStmt().setString(2,xml);

			// the id
			queryManager.getStmt().setString(3,id);

			// execute the query
			int res = queryManager.update();
			
			// close
			queryManager.close();
			
			// return the result
			return res;	
		}
		catch(SQLException e) {
			log.error("updatePathOfSharing :: "+ e);
			throw new DataBaseException();
		}
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	

	
	
	/**
	 * Get the available spaces for a group or a user
	 * @param targetKey the key of the target
	 * @param targetType to know if the target of this sharing is a user or not
	 * @return a vector. Each element is an array of [id, url, path, label, owner, xmlSpace, read:'T'|'F', write:'T'|'F']
	 * @throws DataBaseException
	 */
	public Vector getAvailableSpacesForTarget(String targetKey, String targetType, boolean obliged) throws DataBaseException {

		try {

			// the query manager
			Query queryManager = new Query(dataBaseAccess);
			
			// the query
			String query = QueriesManagement.getQuery(QueriesManagement.GET_AVAILABLE_SPACES_FOR_TARGET);
			
			// prepare statement
			queryManager.setSql(query);
	    	
			// the target
			queryManager.getStmt().setString(1, targetKey);

			// the target is a user or a group
			queryManager.getStmt().setString(2, targetType);

			// if obliged
			if (obliged)
				queryManager.getStmt().setString(3, "T");
			else queryManager.getStmt().setString(3, "F");
			
			// execute the query
			queryManager.select();
			ResultSet rs = queryManager.getRs();
	    	
			// the list of results
			Vector results = new Vector();
			
			// get each result
			while (rs.next()) {
				
				// a result line
				String[] line = new String[7];

				// the server_id
				String serverId = rs.getString(1);
				line[0] = serverId;

				// the xml_server
				String xmlServer = rs.getString(2);
				line[1] = xmlServer;

				// the from_key
				String fromKey = rs.getString(3);
				line[2] = fromKey;

				// the owner
				String owner = rs.getString(4);
				line[3] = owner;

				// the root path
				String rootPath = rs.getString(5);
				line[4] = rootPath;

				// the target id
				String idTarget = rs.getString(6);
				line[5] = idTarget;

				// the space key
				String spaceKey = rs.getString(7);
				line[6] = spaceKey;


				// add to the result
				results.add(line);
			}

			// close
			queryManager.close();
			
	    	return results;
		}
		catch(SQLException e) {			
			log.error("getAvailableSpacesForTarget :: "+ e);
			throw new DataBaseException();
		}				
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	/**
	 * Add a user preference into the database
	 * @param id the id of the sharement in the other database
	 * @param userKey the key of the user who wants to add preference into the database
	 * @return the result of the query
	 * @throws DataBaseException
	 */
	public int addStorageUsersPreferences(String id, String userKey, String label) throws DataBaseException {
		
		try {

			// the query manager
			Query queryManager = new Query(dataBaseAccess);
			
			// check if it is still shared
			boolean isShared = isAlreadyInPreferencesForUser(queryManager, id, userKey);
			if (isShared)
				return 1;
			
			// the SQL query
			String query = QueriesManagement.getQuery(QueriesManagement.ADD_STORAGE_USERS_PREFERENCES);		
			
			// prepare the SQL query	    	   		    	    	
			queryManager.setSql(query);
			
			// the id key
			queryManager.getStmt().setString(1,new GUID().toString());
			
			// the user key
			queryManager.getStmt().setString(2,userKey);

			// the id of available space
			queryManager.getStmt().setString(3,id);

			// the label
			queryManager.getStmt().setString(4,label);

			// execute the query
			int res = queryManager.update();

			// close
			queryManager.close();
			
			// return the result
			return res;	
		}
		catch(SQLException e) {
			log.error("addStorageUsersPreferences :: "+ e);
			throw new DataBaseException();
		}
	}
	
	
	
	
	

	/**
	 * Update a user preference label into the database from a user
	 * @param id the id of the sharement in the other database
	 * @param userKey the login of the user in the portal
	 * @param label the label set by the user
	 * @return the result of the query
	 * @throws DataBaseException
	 */
	public int updateStorageUsersPreferencesLabel(String id, String userKey, String label) throws DataBaseException {
		
		try {

			// the query manager
			Query queryManager = new Query(dataBaseAccess);			
			
			// the SQL query
			String query = QueriesManagement.getQuery(QueriesManagement.UPDATE_STORAGE_USERS_PREFERENCES_LABEL);		
			
			// prepare the SQL query	    	   		    	    	
			queryManager.setSql(query);

			// the label
			queryManager.getStmt().setString(1, label);

			// the userKey
			queryManager.getStmt().setString(2, userKey);

			// the id
			queryManager.getStmt().setString(3, id);

			// execute the query
			int res = queryManager.update();

			// close
			queryManager.close();
			
			// return the result
			return res;	
		}
		catch(SQLException e) {
			log.error("updateStorageUsersPreferencesLabel :: "+ e);
			throw new DataBaseException();
		}
	}
	
	
	
	
	


	/**
	 * Check if the space is already in the user preferences
	 * @param queryManager the query manager to access the database
	 * @param id the id of the shared space
	 * @param userKey the key of the user to check
	 * @return true if already shared, else false
	 * @throws DataBaseException
	 */
	private boolean isAlreadyInPreferencesForUser(Query queryManager, String id, String userKey) throws DataBaseException {
		
		try {					    				
			// the query
			String query = QueriesManagement.getQuery(QueriesManagement.IS_ALREADY_IN_PREFERENCES_FOR_A_USER);

			// prepare statement
			queryManager.setSql(query);
	    	
			// the id
			queryManager.getStmt().setString(1, id);

			// the user key
			queryManager.getStmt().setString(2,userKey);
			
			// execute the query
			queryManager.select();
	    	
			// is shared ?
			boolean isShared = queryManager.getRs().next();
			
			// close the result
			//queryManager.close();
	    	
	    	return isShared;
		}
		catch(SQLException e) {			
			log.error("isAlreadyInPreferencesForUser :: "+ e);
			throw new DataBaseException();
		}				
	}
	
	

	
	

	/**
	 * Get the list of ids of spaces which has already been selected by the current user
	 * @param targetKey the user or group target key
	 * @return a Vector
	 * @throws DataBaseException
	 */
	public Vector[] getKeyOfSpacesFromPreferences(String targetKey) throws DataBaseException {
		
		try {	

			// the result
			Vector ids = new Vector();
			Vector labels = new Vector();
			
			
			// the query manager
			Query queryManager = new Query(dataBaseAccess);
			
			// the query
			String query = QueriesManagement.getQuery(QueriesManagement.GET_ID_OF_SPACE_FROM_PREFERENCES);
			
			// prepare statement
			queryManager.setSql(query);
	    	
			// the target
			queryManager.getStmt().setString(1, targetKey);
			
			// execute the query
			queryManager.select();
			ResultSet rs = queryManager.getRs();
	    	
			// for each element
			while (rs.next()) {
				String id = rs.getString(1);
				String label = rs.getString(2);
				
				ids.add(id);
				labels.add(label);
			}

			// close
			queryManager.close();
			
			
			

			// the query manager
			queryManager = new Query(dataBaseAccess);
			
			// the query
			query = QueriesManagement.getQuery(QueriesManagement.GET_ID_OF_SPACE_FROM_LOCAL_PREFERENCES);
			
			// prepare statement
			queryManager.setSql(query);
	    	
			// the target
			queryManager.getStmt().setString(1, targetKey);
			
			// execute the query
			queryManager.select();
			rs = queryManager.getRs();
	    	
			// for each element
			while (rs.next()) {
				String id = rs.getString(1);
				String label = rs.getString(2);
				
				ids.add(id);
				labels.add(label);
			}

			// close
			queryManager.close();
			
			Vector[] result = new Vector[2];
			result[0]=ids;
			result[1]=labels;
	    	return result;
		}
		catch(SQLException e) {			
			log.error("getKeyOfSpacesFromPreferences :: "+ e);
			throw new DataBaseException();
		}				
	}
	
	
	
	
	

	/**
	 * Delete a user preference from the database
	 * @param id the id of the sharement in the other database
	 * @param userKey the key of the user
	 * @return the result of the query
	 * @throws DataBaseException
	 */
	public int delStorageUsersPreferencesForUser(String id, String userKey) throws DataBaseException {
		//System.out.println(id+" for "+userKey);
		try {

			// the query manager
			Query queryManager = new Query(dataBaseAccess);
			
			// the SQL query
			String query = QueriesManagement.getQuery(QueriesManagement.DEL_STORAGE_USERS_PREFERENCES_FOR_USER);		
			
			// prepare the SQL query	    	   		    	    	
			queryManager.setSql(query);

			// the id
			queryManager.getStmt().setString(1,id);

			// the user key
			queryManager.getStmt().setString(2,userKey);
			
			// execute the query
			int res = queryManager.update();

			// close
			queryManager.close();
			
			// return the result
			return res;	
		}
		catch(SQLException e) {
			log.error("delStorageUsersPreferencesForUser :: "+ e);
			throw new DataBaseException();
		}
	}
	
	

	
	
	
	
	
	
	

	
	
	
	
	
	/******************
	 * ADMINISTRATION 
	 *****************/
	
	
	
	

	/**
	 * Get the servers and spaces set into the administration database
	 * @return a vector. Each element is an array of [serverId, serverName, xmlServer]
	 * @throws DataBaseException
	 */
	public Vector getAdministratedSpaces() throws DataBaseException {
		
		try {		

			// the query manager
			Query queryManager = new Query(dataBaseAccess);
			
			// the query
			String query = QueriesManagement.getQuery(QueriesManagement.GET_ADMINISTRATED_SPACES);
			
			// prepare statement
			queryManager.setSql(query);			
			
			// execute the query
			queryManager.select();
			ResultSet rs = queryManager.getRs();
			
			// the list of results
			Vector results = new Vector();
			
			// get each result
			while (queryManager.getRs().next()) {
				
				// a result line
				String[] line = new String[3];

				// the server id
				String serverId = rs.getString(1);
				line[0] = serverId;

				// the server's name
				String serverName = rs.getString(2);
				line[1] = serverName;

				// the xml_server
				String xmlServer = rs.getString(3);
				line[2] = xmlServer;

				// add to the result
				results.add(line);
			}
	      
			// close the result
			queryManager.close();
	    	
	    	return results;
		}
		catch(SQLException e) {			
			log.error("getAdministratedSpaces :: "+ e);
			throw new DataBaseException();
		}				
	}
	
	
	
	

	/**
	 * Get the targets set into the administration database
	 * @param idStorageAvailableSpaces the id of storage target
	 * @param spaceKey the key of the space
	 * @return a vector. Each element is an array of [availableSpaceTargetId, targetKey, targetIsUser:'T'|'F', read:'T'|'F', write:'T'|'F', share:'T'|'F', obliged:'T'|'F']
	 * @throws DataBaseException
	 */
	public Vector getTargetsOfAdministratedSpaces(String idStorageAvailableSpaces, String spaceKey) throws DataBaseException {
		
		try {		

			// the query manager
			Query queryManager = new Query(dataBaseAccess);
			
			// the query
			String query = QueriesManagement.getQuery(QueriesManagement.GET_TARGETS_OF_ADMINISTRATED_SPACES);
			
			// prepare statement
			queryManager.setSql(query);			

			// the id
			queryManager.getStmt().setString(1,idStorageAvailableSpaces);

			// the spaceKey
			queryManager.getStmt().setString(2,spaceKey);

			// execute the query
			queryManager.select();
			ResultSet rs = queryManager.getRs();
			
			// the list of results
			Vector results = new Vector();
			
			// get each result
			while (queryManager.getRs().next()) {
				
				// a result line
				String[] line = new String[4];

				// the id of storage_available_spaces_targets
				String availableSpaceTargetId = rs.getString(1);
				line[0] = availableSpaceTargetId;
				
				// the target key
				String targetKey = rs.getString(2);
				line[1] = targetKey;

				// the target is user ?
				String targetType = rs.getString(3);
				line[2] = targetType;

				// the obliged
				String obliged = rs.getString(4);
				line[3] = obliged;	
				
				// add to the result
				results.add(line);
			}
	      
			// close the result
			queryManager.close();
	    	
	    	return results;
		}
		catch(SQLException e) {			
			log.error("getTargetsOfAdministratedSpaces :: "+ e);
			throw new DataBaseException();
		}				
	}
	
	
	
	
	

	/**
	 * Update the name of the server of the sharing properties into the database
	 * @param serverId the id of the server
	 * @param newName the new name of the server administration
	 * @return the result of the query
	 * @throws DataBaseException
	 */
	public int updateServerNameAvailableSpace(String serverId, String newName) throws DataBaseException {
		
		try {

			// the query manager
			Query queryManager = new Query(dataBaseAccess);			
			
			// the SQL query
			String query = QueriesManagement.getQuery(QueriesManagement.UPDATE_SERVER_NAME_AVAILABLE_SPACE);		
			
			// prepare the SQL query	    	   		    	    	
			queryManager.setSql(query);

			// the xml server
			queryManager.getStmt().setString(1, newName);
			
			// the id
			queryManager.getStmt().setString(2, serverId);
			
			// execute the query
			int res = queryManager.update();

			// close
			queryManager.close();
			
			// return the result
			return res;	
		}
		catch(SQLException e) {
			log.error("updateServerNameAvailableSpace :: "+ e);
			throw new DataBaseException();
		}
	}
	
	
	
	/**
	 * Update the "obliged" attribute for a target
	 * @param targetId the id of the target
	 * @param obliged the "obliged" value
	 * @return the result of the query
	 * @throws DataBaseException
	 */
	public int updateObligedTarget(String targetId, boolean obliged) throws DataBaseException {
		
		try {

			// the query manager
			Query queryManager = new Query(dataBaseAccess);			
			
			// the SQL query
			String query = QueriesManagement.getQuery(QueriesManagement.UPDATE_OBLIGED_TARGET);		
			
			// prepare the SQL query	    	   		    	    	
			queryManager.setSql(query);

			// the obliged value
			if (obliged)
				queryManager.getStmt().setString(1, "T");
			else
				queryManager.getStmt().setString(1, "F");

			// the target Id
			queryManager.getStmt().setString(2, targetId);
			
			// execute the query
			int res = queryManager.update();

			// close
			queryManager.close();
			
			// return the result
			return res;	
		}
		catch(SQLException e) {
			log.error("updateServerNameAvailableSpace :: "+ e);
			throw new DataBaseException();
		}
	}
	
}
