/*******************************************************************************
    *  Imixs Workflow 
    *  Copyright (C) 2001, 2011 Imixs Software Solutions GmbH,  
    *  http://www.imixs.com
    *  
    *  This program is free software; you can redistribute it and/or 
    *  modify it under the terms of the GNU General Public License 
    *  as published by the Free Software Foundation; either version 2 
    *  of the License, or (at your option) any later version.
   *  
   *  This program is distributed in the hope that it will be useful, 
   *  but WITHOUT ANY WARRANTY; without even the implied warranty of 
   *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 
   *  General Public License for more details.
   *  
   *  You can receive a copy of the GNU General Public
   *  License at http://www.gnu.org/licenses/gpl.html
   *  
   *  Project: 
   *  	http://www.imixs.org
   *  	http://java.net/projects/imixs-workflow
   *  
   *  Contributors:  
   *  	Imixs Software Solutions GmbH - initial API and implementation
   *  	Ralph Soika - Software Developer
   *******************************************************************************/
  
  package org.imixs.workflow.jee.ejb;
  
  import java.util.ArrayList;
  import java.util.Calendar;
  import java.util.Collection;
  import java.util.Date;
  import java.util.Iterator;
  import java.util.List;
  import java.util.Map;
  import java.util.StringTokenizer;
  import java.util.Vector;
  import java.util.logging.Logger;
  
  import javax.annotation.Resource;
  import javax.annotation.security.DeclareRoles;
  import javax.annotation.security.RolesAllowed;
  import javax.ejb.LocalBean;
  import javax.ejb.SessionContext;
  import javax.ejb.Stateless;
  import javax.persistence.EntityManager;
  import javax.persistence.FlushModeType;
  import javax.persistence.PersistenceContext;
  import javax.persistence.Query;
  
  import org.imixs.workflow.ItemCollection;
  import org.imixs.workflow.exceptions.AccessDeniedException;
  import org.imixs.workflow.exceptions.InvalidAccessException;
  
  import org.imixs.workflow.jee.jpa.CalendarItem;
  import org.imixs.workflow.jee.jpa.DoubleItem;
  import org.imixs.workflow.jee.jpa.Entity;
  import org.imixs.workflow.jee.jpa.EntityIndex;
  import org.imixs.workflow.jee.jpa.IntegerItem;
  import org.imixs.workflow.jee.jpa.ReadAccess;
  import org.imixs.workflow.jee.jpa.TextItem;
  import org.imixs.workflow.jee.jpa.WriteAccess;
  
  /**
   * The EntityService is used to save and load instances of ItemCollections into
   * a Database. The EntityService throws an AccessDeniedException if the
   * CallerPrincipal is not allowed to save or read a specific ItemCollection from
   * the database. So the EntityService can be used to save business objects into
   * a database with individual read- or writeAccess restrictions.
   * <p>
   * The Bean holds an instance of an EntityPersistenceManager for the persistence
   * unit 'org.imixs.workflow.jee.ejb' to manage the following Entity EJBs:
   * <ul>
   * <li>Entity,
   * <li>EntityIndex,
   * <li>TextIndex,
   * <li>IntegerIndex,
   * <li>DoubleIndex,
   * <li>CalendarIndex,
   * <li>ReadAccessEntity
   * <li>WriteAccessEntity
   * </ul>
   * < These Entity EJBs are used to store the attributes of a ItemCollection into
   * the connected database.
   * <p>
   * The save() method persists any instance of an ItemCollection. If a
   * ItemCollection is saved the first time the EntityServiceBean generates the
   * attribute $uniqueid which will be included in the ItemCollection returned by
   * this method. If a ItemCollection was saved before the method updates the
   * corresponding Entity Object.
   * <p>
   * The load() and findAllEntities() methods are used to read ItemCollections
   * from the database. The remove() Method deletes a saved ItemCollection from
   * the database.
   * <p>
   * All methods expect and return Instances of the object
   * org.imixs.workflow.ItemCollection which is no entity EJB. So these objects
   * are not managed by any instance of an EntityPersistenceManager.
  * <p>
  * A collection of ItemCollections can be read using the findAllEntites() method
  * using EQL syntax.
  * <p>
  * This EntityServiceBean has methods to brand out different Attributes of a
  * ItemCollection into external properties (TextIndex, IntegerIndex,
  * DoubleIndex, CalendarIndex) With these functionality a client can query a set
  * of ItemCollections later with EJB Query Language (EQL).
  * <p>
  * To define which attributes should be expended into external properties the
  * Session EJB supports the method addIndex(). This method creates an index
  * entry and ensures that every Entity saved before will be updated and future
  * calls of the save() method will care about the new index to separate
  * attributes into the Index Properties.
  * <p>
  * So each call of the save method automatically disassemble a ItemCollection
  * into predefined Index properties and stores attributes which have an Index
  * into a corresponding IndexData Object (TextIndex, IntegerIndex, DoubleIndex,
  * CalendarIndex). On the other hand the load() method reassembles the
  * ItemCollection including all attributes form the data property an any
  * external index property. The EntiyService did not allow to access a managed
  * Entity EJB directly. This simplifies the usage as a client works only with
  * ItemCollections and so there is no need to handle managed and detached entity
  * EJB instances.
  * <p>
  * Additional to the basic functionality to save and load instances of the
  * object org.imixs.workflow.ItemCollection the method also manages the read-
  * and writeAccess for each instance of an ItemCollection. Therefore the save()
  * method scans an ItemCollection for the attributes '$ReadAccess' and
  * '$WriteAccess' and saves these informations into the Entity EJBs
  * ReadAccessEntity and WriteAccessEntity controlled by each instance of the EJB
  * Entity. The EntityServiceBean implementation verifies in each call of the
  * save() load(), remove() and findAllEntities() methods if the current
  * callerPrincipal is granted to the affected entities. If an ItemCollection was
  * saved with read- or writeAccess the access to an Instance of a saved
  * ItemCollection will be protected for a callerPrincipal with missing read- or
  * writeAccess. The default Read- and WriteAccess attributes '$ReadAccess' and
  * '$WriteAccess' can be overwritten by the environment settings
  * 'READ_ACCESS_FIELDS' and 'WRITE_ACCESS_FIELDS'
  * <p>
  * Some useful links about the oneToMany Relationship
  * http://www.avaje.org/manydatatypes.html
  * http://thomas-schuett.de/2008/11/14/ordered-lists-in-jpa-do-it-yourself/
  * http:
  * //javaehrsson.blogspot.com/2005/10/ejb3-onetomany-and-orderby-set-versus.html
  * http://forums.java.net/jive/thread.jspa?threadID=30869&start=0&tstart=0
  * 
  * @see org.imixs.workflow.jee.ejb.EntityPersistenceManager
  * @author rsoika
  * @version 1.0
  * 
  */
 
 @DeclareRoles({ "org.imixs.ACCESSLEVEL.NOACCESS",
 		"org.imixs.ACCESSLEVEL.READERACCESS",
 		"org.imixs.ACCESSLEVEL.AUTHORACCESS",
 		"org.imixs.ACCESSLEVEL.EDITORACCESS",
 		"org.imixs.ACCESSLEVEL.MANAGERACCESS" })
 @RolesAllowed({ "org.imixs.ACCESSLEVEL.NOACCESS",
 		"org.imixs.ACCESSLEVEL.READERACCESS",
 		"org.imixs.ACCESSLEVEL.AUTHORACCESS",
 		"org.imixs.ACCESSLEVEL.EDITORACCESS",
 		"org.imixs.ACCESSLEVEL.MANAGERACCESS" })
 @Stateless
 @LocalBean
 public class EntityService implements EntityServiceRemote {
 
 	public static final int TYP_TEXT = 0;
 
 	public static final int TYP_INT = 1;
 
 	public static final int TYP_DOUBLE = 2;
 
 	public static final int TYP_CALENDAR = 3;
 
 	public static final String ACCESSLEVEL_NOACCESS = "org.imixs.ACCESSLEVEL.NOACCESS";
 
 	public static final String ACCESSLEVEL_READERACCESS = "org.imixs.ACCESSLEVEL.READERACCESS";
 
 	public static final String ACCESSLEVEL_AUTHORACCESS = "org.imixs.ACCESSLEVEL.AUTHORACCESS";
 
 	public static final String ACCESSLEVEL_EDITORACCESS = "org.imixs.ACCESSLEVEL.EDITORACCESS";
 
 	public static final String ACCESSLEVEL_MANAGERACCESS = "org.imixs.ACCESSLEVEL.MANAGERACCESS";
 
 	public static final String UNIQUEID = "$uniqueid";
 
 	public static final String USER_GROUP_LIST = "org.imixs.USER.GROUPLIST";
 
 	private static Logger logger = Logger.getLogger("org.imixs.workflow");
 
 	@Resource
 	SessionContext ctx;
 
 	@Resource(name = "READ_ACCESS_FIELDS")
 	private String readAccessFields = "";
 	@Resource(name = "WRITE_ACCESS_FIELDS")
 	private String writeAccessFields = "";
 	@Resource(name = "ACCESS_ROLES")
 	private String accessRoles = "";
 	@Resource(name = "DISABLE_OPTIMISTIC_LOCKING")
 	private Boolean disbaleOptimisticLocking = false;
 
 	@PersistenceContext(unitName = "org.imixs.workflow.jee.jpa")
 	private EntityManager manager;
 
 	
 	/**
 	 * Returns additional AccessRoles defined for the EJB instance
 	 * 
 	 * @return
 	 */
 	public String getAccessRoles() {
 		return accessRoles;
 	}
 
 	/**
 	 * Returns additional ReadAccessFields defined for the EJB instance.
 	 * Default=$ReadAccess
 	 * 
 	 * @return
 	 */
 	public String getReadAccessFields() {
 		return readAccessFields;
 	}
 
 	/**
 	 * Returns additional WriteAccessFields defined for the EJB instance.
 	 * Default=$WriteAccess
 	 * 
 	 * @return
 	 */
 	public String getWriteAccessFields() {
 		return writeAccessFields;
 	}
 
 	/**
 	 * This method returns a list of user names, roles and application groups
 	 * the user belongs to.
 	 * 
 	 * @return
 	 */
 	public List<String> getUserNameList() {
 	
 		List<String> userNameList = new Vector<String>();
 	
 		// Begin with the username
 		userNameList.add(ctx.getCallerPrincipal().getName().toString());
 		// now construct role list
 		String roleList = "org.imixs.ACCESSLEVEL.READERACCESS,org.imixs.ACCESSLEVEL.AUTHORACCESS,org.imixs.ACCESSLEVEL.EDITORACCESS,"
 				+ accessRoles;
 		// and add each role the user is in to the list
 		StringTokenizer roleListTokens = new StringTokenizer(roleList, ",");
 		while (roleListTokens.hasMoreTokens()) {
 			try {
 				String testRole = roleListTokens.nextToken().trim();
 				if (!"".equals(testRole) && ctx.isCallerInRole(testRole))
 					userNameList.add(testRole);
 			} catch (Exception e) {
 				// no operation - Role simply not defined
 				// this could be an configuration/test issue and need not to be
 				// handled as an error
 			}
 		}
 	
 		// read dynamic user roles from the ContextData (if provided) and add
 		// them to the query....
 		String[] applicationGroups = getUserGroupList();
 		if (applicationGroups != null)
 			for (String auserRole : applicationGroups)
 				userNameList.add(auserRole);
 	
 		return userNameList;
 	}
 
 	/**
 	 * This Method saves an ItemCollection into a database. If the
 	 * ItemCollection is saved the first time the method generates a uniqueID
 	 * ('$uniqueid') which can be used to identify the ItemCollection by its ID.
 	 * If the ItemCollection was saved before the method updates the
 	 * ItemCollection stored in the database. The Method returns an updated
 	 * instance of the ItemCollection containing the attributes $modified,
 	 * $created, and $uniqueid
 	 * <p>
 	 * The method throws an AccessDeniedException if the CallerPrincipal is not
 	 * allowed to save or update the ItemCollection in the database. The
 	 * CallerPrincipial should have at least the access Role
 	 * org.imixs.ACCESSLEVEL.AUTHORACCESS
 	 * 
 	 * @param ItemCollection
 	 *            to be saved
 	 * @return updated ItemCollection
 	 */
 	public ItemCollection save(ItemCollection itemcol)
 			throws AccessDeniedException {
 
 		Entity activeEntity = null;
 		ItemCollection slimItemCollection = null;
 
 		/*
 		 * First we get a List of all existing Indices
 		 * 
 		 * if the FlushModeType=AUTO (which is the default) the getIndices()
 		 * call will force an internal flush()
 		 */
 		Collection<EntityIndex> entityIndexCache = getIndices();
 
 		// Now set flush Mode to COMMIT
 		manager.setFlushMode(FlushModeType.COMMIT);
 
 		// check if a $uniqueid is available
 		String sID = itemcol.getItemValueString(UNIQUEID);
 		if (!"".equals(sID)) {
 			// yes so we can try to find the Entity by its primary key
 			activeEntity = manager.find(Entity.class, sID);
 			if (activeEntity == null) {
 				logger.fine("[EntityService] Entity '" + sID + "' not found!");
 			} else {
 				/*
 				 * try { // #issue 102 - we try to flush :-/
 				 * logger.info("[EntityServiceBean] #issue 102 - refresh() - "
 				 * +sID); manager.refresh(activeEntity); } catch
 				 * (EntityNotFoundException ex) { logger.warning(
 				 * "[EntityServiceBean] #issue 102 - refresh() EntityNotFoundException"
 				 * ); activeEntity = null; }
 				 */
 			}
 
 		}
 
 		// did entity exist?
 		if (activeEntity == null) {
 			// entity no found in database create new instance using the
 			// provided id
 			// Test if user is allowed to create Entities....
 			if (!(ctx.isCallerInRole(ACCESSLEVEL_MANAGERACCESS)
 					|| ctx.isCallerInRole(ACCESSLEVEL_EDITORACCESS) || ctx
 						.isCallerInRole(ACCESSLEVEL_AUTHORACCESS))) {
 				throw new AccessDeniedException(
 						"[EntityService] You are not allowed to perform this operation");
 			}
 			// create new one with the provided id
 			activeEntity = new Entity(sID);
 			// if $Created is provided than overtake this information
 			Date datCreated = itemcol.getItemValueDate("$Created");
 			if (datCreated != null) {
 				Calendar cal = Calendar.getInstance();
 				cal.setTime(datCreated);
 				// Overwrite Creation Date
 				activeEntity.setCreated(cal);
 			}
 
 			// now persist the new Entity
 			manager.persist(activeEntity);
 
 		} else {
 			// activeEntity exists - verify if current user has write- and
 			// readaccess
 			if (!isCallerAuthor(activeEntity) || !isCallerReader(activeEntity)) {
 				throw new AccessDeniedException(
 						"[EntityService] You are not allowed to perform this operation");
 			}
 		}
 
 		// after all activeEntity is now managed through the persistence
 		// manager!
 
 		// remove property $isauthor
 		itemcol.removeItem("$isauthor");
 
 		String aType = itemcol.getItemValueString("type");
 		if ("".equals(aType))
 			aType = "Entity";
 		activeEntity.setType(aType);
 
 		// update the standard attributes $modified $created and $uniqueid
 		Calendar cal = Calendar.getInstance();
 
 		itemcol.replaceItemValue("$uniqueid", activeEntity.getId());
 		itemcol.replaceItemValue("$modified", cal.getTime());
 		itemcol.replaceItemValue("$created", activeEntity.getCreated()
 				.getTime());
 		// create a new Instance of this ItemCollection
 		slimItemCollection = new ItemCollection(itemcol.getAllItems());
 
 		// update read- and writeAccess List
 		updateReadAccessList(slimItemCollection, activeEntity);
 		updateWriteAccessList(slimItemCollection, activeEntity);
 
 		explodeEntity(slimItemCollection, activeEntity, entityIndexCache);
 
 		// finally update the data field and store the item map object
 		activeEntity.setData(slimItemCollection.getAllItems());
 
 		// verify and update the author access and add again the property
 		// '$isauthor'
 		itemcol.replaceItemValue("$isauthor", isCallerAuthor(activeEntity));
 
 		return itemcol;
 	}
 
 	/**
 	 * This method loads an ItemCollection from the Database. The method expects
 	 * a valid $unqiueID to identify the ItemCollection saved before into the
 	 * database. The method returns null if no ItemCollection with the
 	 * corresponding ID exists.
 	 * <p>
 	 * The method checks if the CallerPrincipal has read access to
 	 * ItemCollection stored in the database. If not the method returns null.
 	 * The method dose not throw an AccessDeniedException if the user is not
 	 * allowed to read the entity to prevent a aggressor with informations about
 	 * the existence of that specific ItemCollection.
 	 * <p>
 	 * CallerPrincipial should have at least the access Role
 	 * org.imixs.ACCESSLEVEL.READACCESS
 	 * 
 	 * @param id
 	 *            - the $unqiueid of the ItemCollection to be loaded
 	 * @return ItemCollection object or null if the ItemColleciton dose not
 	 *         exist or the CallerPrincipal hat insufficient read access.
 	 * 
 	 */
 	public ItemCollection load(String id) {
 		Entity activeEntity = null;
 		activeEntity = manager.find(Entity.class, id);
 
 		// implode the ItemCollection object
 		if (activeEntity != null && isCallerReader(activeEntity)) {
 			/*
 			 * try { manager.refresh(activeEntity); } catch
 			 * (EntityNotFoundException ex) { logger.warning(
 			 * "[EntityServiceBean] #issue 102 - refresh() EntityNotFoundException"
 			 * ); activeEntity = null; return null; }
 			 */
 			return implodeEntity(activeEntity);
 		} else
 			return null;
 	}
 
 	/**
 	 * This method removes an ItemCollection from the database. If the
 	 * CallerPrincipal is not allowed to access the ItemColleciton the method
 	 * throws an AccessDeniedException.
 	 * <p>
 	 * The CallerPrincipial should have at least the access Role
 	 * org.imixs.ACCESSLEVEL.AUTHORACCESS
 	 * <p>
 	 * Also the method removes all existing relation ships of the entity. This
 	 * is necessary becaus of the FetchType.LAZY used for most relations.
 	 * 
 	 * 
 	 * @param ItemCollection
 	 *            to be removed
 	 * @throws AccessDeniedException
 	 */
 	public void remove(ItemCollection itemcol) throws AccessDeniedException {
 		Entity activeEntity = null;
 		String sID = itemcol.getItemValueString("$uniqueid");
 		activeEntity = manager.find(Entity.class, sID);
 
 		if (activeEntity != null) {
 			if (!isCallerReader(activeEntity) || !isCallerAuthor(activeEntity))
 				throw new AccessDeniedException(
 						"[EntityService] You are not allowed to perform this operation");
 
 			// remove current TextItem List
 			for (TextItem aItem : activeEntity.getTextItems())
 				manager.remove(aItem);
 			activeEntity.getTextItems().clear();
 
 			// remove current CalendarItem List
 			for (CalendarItem aItem : activeEntity.getCalendarItems())
 				manager.remove(aItem);
 			activeEntity.getCalendarItems().clear();
 
 			// remove current IntegerItem List
 			for (IntegerItem aItem : activeEntity.getIntegerItems())
 				manager.remove(aItem);
 			activeEntity.getIntegerItems().clear();
 
 			// remove current IntegerItem List/
 			for (DoubleItem aItem : activeEntity.getDoubleItems())
 				manager.remove(aItem);
 			activeEntity.getDoubleItems().clear();
 
 			// remove current WriteAccess List
 			for (WriteAccess aItem : activeEntity.getWriteAccessList())
 				manager.remove(aItem);
 			activeEntity.getWriteAccessList().clear();
 
 			// remove current ReadAccess List
 			for (ReadAccess aItem : activeEntity.getReadAccessList())
 				manager.remove(aItem);
 			activeEntity.getReadAccessList().clear();
 
 			// remove entity...
 			manager.remove(activeEntity);
 
 		} else
 			throw new AccessDeniedException("[EntityService] invalid $uniqueid");
 	}
 
 	/**
 	 * Adds an Index for a property provided by ItemCollection objects. An
 	 * existing Index can be used to select ItemCollections using a JPQL
 	 * statement. @see findEntitesByQuery
 	 * 
 	 * The method throws an AccessDeniedException if the CallerPrinciapal is not
 	 * in the role org.imixs.ACCESSLEVEL.MANAGERACCESS.
 	 * 
 	 * @param name
 	 *            of a property (not case sensetive)
 	 * @param ityp
 	 *            - Type of EntityIndex
 	 * @throws AccessDeniedException
 	 */
 	public void addIndex(String stitel, int ityp) throws AccessDeniedException {
 		// lower case title
 		stitel = stitel.toLowerCase();
 
 		// check if index already exists?
 		EntityIndex activeEntityIndex = manager.find(EntityIndex.class, stitel);
 		if (activeEntityIndex != null)
 			return;
 
 		// Check security roles
 		// only MANAGERACCESS is allowed to add index and rebuild existing
 		// entities!
 		if (ctx.isCallerInRole("org.imixs.ACCESSLEVEL.MANAGERACCESS") == false)
 			throw new AccessDeniedException(
 					"[EntityService] You are not allowed to add index fields");
 
 		// add new index
 		logger.info("[EntityServiceBean] add new Index: " + stitel + ":" + ityp);
 		activeEntityIndex = new EntityIndex(stitel, ityp);
 		manager.persist(activeEntityIndex);
 
 		// 1.) find all existing entities
 
 		Collection<Entity> entityList = null;
 		Query q = manager.createQuery("SELECT entity FROM Entity entity");
 		entityList = q.getResultList();
 		logger.info("[EntityServiceBean] found " + entityList.size()
 				+ " existing entities. Starting update...");
 		updateAllEntityIndexFields(entityList, stitel);
 		logger.info("[EntityServiceBean] index update completed");
 
 	}
 
 	/**
 	 * This method removes an existing index from the current indexlist and
 	 * updates existing entities. Notice that the index field name will be
 	 * lowercased! Each EQL statement should use lower cased fieldnames!
 	 * 
 	 * The method checks if the Caller is in Role
 	 * "org.imixs.ACCESSLEVEL.MANAGERACCESS". Since the caller is no Manager
 	 * there is no guarantee that the caller can update all existing Entities as
 	 * maybe he can not read all entities.
 	 * 
 	 * All existing Entities will be reorderd by this method call. So the method
 	 * can take a long time to proceed
 	 * 
 	 * @param stitel
 	 *            - will be automatical lowercased!
 	 * @throws AccessDeniedException
 	 * 
 	 */
 	public void removeIndex(String stitel) throws AccessDeniedException {
 		int indexType = 0;
 		// lower case title
 		stitel = stitel.toLowerCase();
 
 		// check if index already exists?
 		EntityIndex activeEntityIndex = manager.find(EntityIndex.class, stitel);
 		if (activeEntityIndex == null)
 			return;
 
 		// Check security roles
 		if (ctx.isCallerInRole("org.imixs.ACCESSLEVEL.MANAGERACCESS") == false)
 			throw new AccessDeniedException(
 					"[EntityService] You are not allowed to add index fields");
 
 		indexType = activeEntityIndex.getTyp();
 
 		logger.info("[EntityService] remove Index: " + stitel + ":" + indexType);
 
 		// remove index
 		manager.remove(activeEntityIndex);
 
 		// now update all entity index fields
 		// 1.) find all existing entities
 		Collection<Entity> entityList = null;
 		// preselect worktiems which are really affected
 		String query = "SELECT wi FROM Entity wi ";
 		switch (indexType) {
 		case EntityIndex.TYP_CALENDAR:
 			query += " JOIN wi.calendarItems AS i1 WHERE i1.itemName='"
 					+ stitel + "'";
 			break;
 		case EntityIndex.TYP_DOUBLE:
 			query += " JOIN wi.doubleItems AS i1 WHERE i1.itemName='" + stitel
 					+ "'";
 			break;
 		case EntityIndex.TYP_INT:
 			query += " JOIN wi.integerItems AS i1 WHERE i1.itemName='" + stitel
 					+ "'";
 			break;
 
 		default:
 			query += " JOIN wi.textItems AS i1 WHERE i1.itemName='" + stitel
 					+ "'";
 			break;
 		}
 
 		logger.info("[EntityServiceBean] remove Index - update query=" + query);
 		Query q = manager.createQuery(query);
 
 		entityList = q.getResultList();
 		logger.info("[EntityServiceBean] found " + entityList.size()
 				+ " affected entities. Starting update...");
 		updateAllEntityIndexFields(entityList, null);
 		logger.info("[EntityServiceBean] index update completed");
 
 	}
 
 	/**
 	 * The method returns a list of EntityIndex which defines external
 	 * properties
 	 * 
 	 * @return
 	 */
 	public Collection<EntityIndex> getIndices() {
 		// Caching
 		logger.finer("getIndiecies....");
 
 		Query q = manager
 				.createQuery("SELECT entityindex FROM EntityIndex entityindex");
 		return q.getResultList();
 	}
 
 	/**
 	 * The method returns a collection of ItemCollections. The method expects an
 	 * valid jPQL statement. The method returns only ItemCollections which are
 	 * readable by the CallerPrincipal. With the startpos and count parameters
 	 * it is possible to read chunks of entities. The jPQL Statement must match
 	 * the conditions of the JPA Object Class Entity
 	 * 
 	 * @param query
 	 * @param startpos
 	 * @param count
 	 * @return
 	 * @throws InvalidAccessException
 	 * 
 	 * @see org.imixs.workfow.jee.jpa.Entity
 	 */
 	public Collection<ItemCollection> findAllEntities(String query,
 			int startpos, int count) throws InvalidAccessException {
 		Vector<ItemCollection> vectorResult = new Vector<ItemCollection>();
 
 		// optimize query....
 		query = optimizeQuery(query);
 		try {
 			Query q = manager.createQuery(query);
 			if (startpos >= 0)
 				q.setFirstResult(startpos);
 			if (count > 0)
 				q.setMaxResults(count);
 
 			Collection<Entity> entityList = q.getResultList();
 
 			if (entityList == null)
 				return vectorResult;
 			for (Entity aEntity : entityList) {
 				/*
 				 * try { manager.refresh(aEntity); } catch
 				 * (EntityNotFoundException ex) { logger.warning(
 				 * "[EntityServiceBean] #issue 102 - refresh() EntityNotFoundException"
 				 * ); aEntity = null; continue; }
 				 */
 
 				// implode the ItemCollection object and add it to the resultset
 				vectorResult.add(implodeEntity(aEntity));
 			}
 		} catch (RuntimeException nre) {
 			throw new InvalidAccessException(
 					"[EntityService] Error findAllEntities: '" + query + "' ",
 					nre);
 		}
 		return vectorResult;
 
 	}
 
 	/**
 	 * The method returns the parent ItemCollection to a given child
 	 * ItemCollection. A parent ItemCollection is referenced by a child
 	 * ItemCollection through the property $uniqueidRef which is equals the
 	 * parent entity's $uniqueID.
 	 * 
 	 * @param childentity
 	 * @return parententity
 	 */
 	public ItemCollection findParentEntity(ItemCollection child)
 			throws InvalidAccessException {
 		String parentUniqueID = child.getItemValueString("$uniqueidref");
 		return this.load(parentUniqueID);
 	}
 
 	/**
 	 * The method returns a collection of child ItemCollections. A child entity
 	 * is defined by the property $uniqueidRef which points to a parent entity.
 	 * 
 	 * The method returns only ItemCollections which are readable by the
 	 * CallerPrincipal. With the startPos and count parameters it is possible to
 	 * read chunks of entities.
 	 * 
 	 * @see findParentEntity
 	 * 
 	 * @param parententity
 	 * @param startpos
 	 * @param count
 	 * @return
 	 * @throws InvalidAccessException
 	 */
 	public Collection<ItemCollection> findChildEntities(ItemCollection child,
 			int start, int count) throws InvalidAccessException {
 
 		String parentUniqueID = child.getItemValueString("$uniqueid");
 
 		String sQuery = "SELECT wi FROM Entity AS wi ";
 		sQuery += " JOIN wi.textItems as t2 ";
 		sQuery += " WHERE t2.itemName = '$uniqueidref' and t2.itemValue = '"
 				+ parentUniqueID + "' ";
 
 		return this.findAllEntities(sQuery, start, count);
 	}
 
 	/**
 	 * This method checks if the Caller Principal has read access for the
 	 * activeEntity.
 	 * 
 	 * @return true if user has readaccess
 	 */
 	private boolean isCallerReader(Entity aEntity) {
 
 		List<ReadAccess> readAccessList = aEntity.getReadAccessList();
 
 		/**
 		 * 1.) org.imixs.ACCESSLEVEL.NOACCESS
 		 * 
 		 * always = false -> no access
 		 */
 
 		if (ctx.isCallerInRole(ACCESSLEVEL_NOACCESS))
 			return false;
 
 		/**
 		 * 2.) org.imixs.ACCESSLEVEL.MANAGERACCESS
 		 * 
 		 * always = true -> grant access.
 		 */
 
 		if (ctx.isCallerInRole(ACCESSLEVEL_MANAGERACCESS))
 			return true;
 
 		/**
 		 * 2.) org.imixs.ACCESSLEVEL.EDITOR org.imixs.ACCESSLEVEL.AUTHOR
 		 * ACCESSLEVEL.READER
 		 * 
 		 * check read access
 		 */
 		if (readAccessList == null || readAccessList.size() == 0) {
 			// no restriction found
 			return true;
 		}
 
 		boolean notemptyfield = false;
 
 		// get user name list
 		List<String> auserNameList = getUserNameList();
 
 		// check each read access
 		for (ReadAccess aReadAccess : readAccessList) {
 			if (aReadAccess != null && !"".equals(aReadAccess.getValue())) {
 				notemptyfield = true;
 				if (auserNameList.indexOf(aReadAccess.getValue()) > -1)
 					return true;
 
 			}
 		}
 		if (!notemptyfield)
 			return true;
 
 		return false;
 	}
 
 	/**
 	 * Verifies if the caller has write access to the current entity object.
 	 * 
 	 * @return
 	 */
 	private boolean isCallerAuthor(Entity aEntity) {
 		List<WriteAccess> writeAccessList = aEntity.getWriteAccessList();
 
 		/**
 		 * 1.) org.imixs.ACCESSLEVEL.NOACCESS allways false - now write access!
 		 */
 		if (ctx.isCallerInRole(ACCESSLEVEL_NOACCESS))
 			return false;
 
 		/**
 		 * 2.) org.imixs.ACCESSLEVEL.MANAGERACCESS or
 		 * org.imixs.ACCESSLEVEL.EDITOR Always true - grant writeaccess.
 		 */
 		if (ctx.isCallerInRole(ACCESSLEVEL_MANAGERACCESS)
 				|| ctx.isCallerInRole(ACCESSLEVEL_EDITORACCESS))
 			return true;
 
 		/**
 		 * 2.) org.imixs.ACCESSLEVEL.AUTHOR
 		 * 
 		 * check write access in detail
 		 */
 
 		if (ctx.isCallerInRole(ACCESSLEVEL_AUTHORACCESS)) {
 			if (writeAccessList == null || writeAccessList.size() == 0) {
 				// now wirte access
 				return false;
 			}
 
 			// get user name list
 			List<String> auserNameList = getUserNameList();
 
 			// check each read access
 			for (WriteAccess aWriteAccess : writeAccessList) {
 				if (aWriteAccess != null && !"".equals(aWriteAccess.getValue())) {
 					if (auserNameList.indexOf(aWriteAccess.getValue()) > -1)
 						return true; // user role known - grant access
 				}
 			}
 		}
 		return false;
 	}
 
 	/**
 	 * This method read the param USER_GROUP_LIST from the EJB ContextData. This
 	 * context data object can provide a string array with application specific
 	 * dynamic user groups. These groups are used to grant access to an entity
 	 * independent from the static User-Role settings. The method returns null
 	 * if no ContextData is set
 	 * 
 	 * @return - list of user group names or null if not USER_GROUP_LIST is
 	 *         defined
 	 */
 	private String[] getUserGroupList() {
 		// read dynamic user roles from the ContextData (if provided) ....
 		String[] applicationUserGroupList = (String[]) ctx.getContextData()
 				.get(USER_GROUP_LIST);
 
 		if (applicationUserGroupList != null)
 			// trim entries....
 			for (int i = 0; i < applicationUserGroupList.length; i++) {
 				applicationUserGroupList[i] = applicationUserGroupList[i]
 						.trim();
 			}
 
 		return applicationUserGroupList;
 	}
 
 	/**
 	 * This method updates the internal WriteAccessList. Therefore the method
 	 * verifies if the itemCollection contains WriteAccess properties. The
 	 * default property name is '$writeaccess'. Additional property names can be
 	 * provided by the resource "WRITE_ACCESS_FIELDS" through the deployment
 	 * descriptor. Empty values will be ignored.
 	 * <p>
 	 * The Values of the corresponding Items in the ItemCollection will not be
 	 * removed by this method because access values could be assigned to
 	 * different properties.
 	 * 
 	 * @return the new itemCollection without writeaccess properties
 	 */
 	private void updateWriteAccessList(ItemCollection itemCol, Entity aEntity) {
 		List<String> vAccessFieldList = new ArrayList<String>();
 
 		// remove current WriteAccess List
 
 		for (WriteAccess aItem : aEntity.getWriteAccessList())
 			manager.remove(aItem);
 		aEntity.getWriteAccessList().clear();
 
 		// create fieldname list and add the default fieldname
 		vAccessFieldList.add("$writeAccess");
 
 		// test if the Resource WRITE_ACCESS_FIELDS is provided
 		if (writeAccessFields != null && !"".equals(writeAccessFields)) {
 			String[] stringArrayList = writeAccessFields.split(",");
 			for (String aentry : stringArrayList) {
 				vAccessFieldList.add(aentry);
 			}
 		}
 
 		// process all attributes
 		for (String aentry : vAccessFieldList) {
 			// add values. But do not add empty string entries here!
 			List vEntryList = itemCol.getItemValue(aentry);
 			for (Object avalue : vEntryList) {
 				if (avalue != null && !"".equals(avalue.toString())) {
 					// now persist and add the new Item
 					WriteAccess newItem = new WriteAccess(avalue.toString());
 					manager.persist(newItem);
 					aEntity.getWriteAccessList().add(newItem);
 				}
 			}
 		}
 	}
 
 	/**
 	 * This method updates the internal ReadAccessList. Therefore the method
 	 * verifies if the itemCollection contains ReadAccess properties. The
 	 * default property name is '$readaccess'. Additional property names can be
 	 * provided by the resource "READ_ACCESS_FIELDS" through the deployment
 	 * descriptor. Empty values will be ignored!
 	 * <p>
 	 * The Values of the corresponding Items in the ItemCollection will not be
 	 * removed by this method because access values could be assigned to
 	 * different properties.
 	 * 
 	 * @return the new itemCollection without writeaccess properties
 	 */
 	private void updateReadAccessList(ItemCollection itemCol, Entity aEntity) {
 		List<String> vAccessFieldList = new ArrayList<String>();
 
 		// remove current ReadAccess List
 		for (ReadAccess aItem : aEntity.getReadAccessList())
 			manager.remove(aItem);
 		aEntity.getReadAccessList().clear();
 
 		// add default fieldname
 		vAccessFieldList.add("$readAccess");
 
 		// test if the Resource WRITE_ACCESS_FIELDS is provided
 		if (readAccessFields != null && !"".equals(readAccessFields)) {
 			String[] stringArrayList = readAccessFields.split(",");
 			for (String aentry : stringArrayList) {
 				vAccessFieldList.add(aentry);
 			}
 		}
 
 		// process all attributes
 		for (String aentry : vAccessFieldList) {
 			// add values. But do not add empty string entries here!
 			List vEntryList = itemCol.getItemValue(aentry);
 			for (Object avalue : vEntryList) {
 				if (avalue != null && !"".equals(avalue.toString())) {
 					// now persist and add the new Item
 					ReadAccess newItem = new ReadAccess(avalue.toString());
 					manager.persist(newItem);
 					aEntity.getReadAccessList().add(newItem);
 				}
 			}
 		}
 	}
 
 	/**
 	 * Explodes an ItemCollection into a Entity with its index fields. This
 	 * method extracts all items with corresponding Indices into the subtables.
 	 * Existing Entries will be removed.
 	 * 
 	 * This method extracts an single property of the ItemCollection (data
 	 * object of the activeEntity) into the corresponding index properties
 	 * (TextItem, IntegerItem DoubleItem, CalendarItem).
 	 * <p>
 	 * The ItemName will be transformed to lowercase!
 	 * <p>
 	 * If an instance of a Date Object is provided in the ItemCollection the
 	 * values will be converted from the Date object into a Calendar Object.
 	 * This is because the ItemCollection works typical with Date Objects
 	 * instead of Calendar Objects.
 	 * <p>
 	 * After the method has moved an attribute with its values to the
 	 * corresponding Index Property the method removes the attribute form the
 	 * itemCollection.
 	 * <p>
 	 * If a value of a property did not match the indexProperty Type a
 	 * ClassCastExcepiton will be thrown. This is the situation if one or more
 	 * single values did
 	 * 
 	 * not match the propertyIndex Type. The method prints a warning to the log
 	 * file but did continue proceeding. This results into a situation where
 	 * invalid values will be stored in the ItemCollections value list. <br>
 	 * In this situation the entity exists in a invalid structure and did not
 	 * match the data model
 	 * 
 	 */
 	private void explodeEntity(ItemCollection itemCol, Entity aEntity,
 			Collection<EntityIndex> entityIndexCache) {
 
 		// in case of optimistic locking is disabled we remove $version 
 		if (disbaleOptimisticLocking) {
 			itemCol.removeItem("$Version");
 		} else if (itemCol.hasItem("$Version")) {
 			// if $version is provided we update the version number of the entity!
 			aEntity.setVersion(itemCol.getItemValueInteger("$Version"));
 		}
 
 		// remove current TextItem List
 		for (TextItem aItem : aEntity.getTextItems())
 			manager.remove(aItem);
 		aEntity.getTextItems().clear();
 
 		// remove current CalendarItem List
 		for (CalendarItem aItem : aEntity.getCalendarItems())
 			manager.remove(aItem);
 		aEntity.getCalendarItems().clear();
 
 		// remove current IntegerItem List
 		for (IntegerItem aItem : aEntity.getIntegerItems())
 			manager.remove(aItem);
 		aEntity.getIntegerItems().clear();
 
 		// remove current IntegerItem List
 		for (DoubleItem aItem : aEntity.getDoubleItems())
 			manager.remove(aItem);
 		aEntity.getDoubleItems().clear();
 
 		// for each index update the
 		for (EntityIndex index : entityIndexCache) {
 			if (index.getTyp() == EntityIndex.TYP_TEXT) {
 				// get the value list fom the itemcolection
 				List valueList = itemCol.getItemValue(index.getName());
 				for (Object asingleValue : valueList) {
 					TextItem newItem = new TextItem(index.getName(),
 							asingleValue.toString());
 					manager.persist(newItem);
 					aEntity.getTextItems().add(newItem);
 				}
 				// now remove the attribute from ItemCollection
 				itemCol.removeItem(index.getName());
 				continue;
 			}
 			if (index.getTyp() == EntityIndex.TYP_INT) {
 				// get the value list fom the itemcolection
 				List valueList = itemCol.getItemValue(index.getName());
 				// handle classCastExcpetions...
 				boolean bClassCastException = false;
 				Vector vInvalidValues = new Vector();
 				for (Object asingleValue : valueList) {
 					try {
 						IntegerItem newItem = new IntegerItem(index.getName(),
 								(Integer) asingleValue);
 						manager.persist(newItem);
 						aEntity.getIntegerItems().add(newItem);
 					} catch (ClassCastException cce) {
 						bClassCastException = true;
 						logger.warning("explodeEntity - " + index.getName()
 								+ " TYP_INT: " + cce.getMessage());
 						vInvalidValues.add(asingleValue);
 					}
 				}
 				// now remove the attribute from ItemCollection
 				if (!bClassCastException)
 					itemCol.removeItem(index.getName());
 				else
 					itemCol.replaceItemValue(index.getName(), vInvalidValues);
 				continue;
 			}
 
 			if (index.getTyp() == EntityIndex.TYP_DOUBLE) {
 				// get the value list fom the itemcolection
 				List valueList = itemCol.getItemValue(index.getName());
 				// handle classCastExcpetions...
 				boolean bClassCastException = false;
 				List vInvalidValues = new Vector();
 				for (Object asingleValue : valueList) {
 					try {
 						DoubleItem newItem = new DoubleItem(index.getName(),
 								(Double) asingleValue);
 						manager.persist(newItem);
 						aEntity.getDoubleItems().add(newItem);
 					} catch (ClassCastException cce) {
 						bClassCastException = true;
 						logger.warning("explodeEntity - " + index.getName()
 								+ " TYP_DOUBLE: " + cce.getMessage());
 						vInvalidValues.add(asingleValue);
 					}
 				}
 				// now remove the attribute from ItemCollection
 				if (!bClassCastException)
 					itemCol.removeItem(index.getName());
 				else
 					itemCol.replaceItemValue(index.getName(), vInvalidValues);
 				continue;
 			}
 
 			if (index.getTyp() == EntityIndex.TYP_CALENDAR) {
 				// get the value list fom the itemcolection
 				List valueList = itemCol.getItemValue(index.getName());
 				// handle classCastExcpetions...
 				boolean bClassCastException = false;
 				Vector vInvalidValues = new Vector();
 				for (Object asingleValue : valueList) {
 					try {
 						if (asingleValue instanceof java.util.Date) {
 							Calendar cal = Calendar.getInstance();
 							cal.setTime((java.util.Date) asingleValue);
 							asingleValue = cal;
 						}
 
 						CalendarItem newItem = new CalendarItem(
 								index.getName(), (Calendar) asingleValue);
 						manager.persist(newItem);
 						aEntity.getCalendarItems().add(newItem);
 					} catch (ClassCastException cce) {
 						bClassCastException = true;
 						logger.warning("explodeEntity - " + index.getName()
 								+ " TYP_CALENDAR: " + cce.getMessage());
 						vInvalidValues.add(asingleValue);
 					}
 				}
 				// now remove the attribute from ItemCollection
 				if (!bClassCastException)
 					itemCol.removeItem(index.getName());
 				else
 					itemCol.replaceItemValue(index.getName(), vInvalidValues);
 				continue;
 			}
 
 			logger.warning(" explodeEntity - " + index.getName()
 					+ " Indextype:" + index.getTyp() + " unknown!");
 
 		}
 
 	}
 
 	/**
 	 * This method returns a new Instance of an ItemCollection based of an given
 	 * Entity object. The method constructs a new ItemCollection instance
 	 * containing all values of the Entity data. Properties which where
 	 * separated out into the index properties (TextItems, IntegerItems,
 	 * DoubleItems, CalendarItems) will be moved back into the ItemCollection
 	 * Object returned by this method. So the returned ItemCollection will
 	 * contain all attributes managed currently by the activeEntity.
 	 * <p>
 	 * The method takes care about the fact that the values of the activeEntiy
 	 * are typical managed by the PersistenceManger. For this reason the method
 	 * did not copy the Vector objects into the new detached Instance of the new
 	 * ItemCollection but copies only the values of each vector. This mechanism
 	 * guarantees that the reconstruction of indexProperties did not affect the
 	 * managed activeEntity and its binded objects. So it is not necessary that
 	 * the activeEntity was detached before this method is called as the method
 	 * will not! modify property values which would be written back to the
 	 * database.
 	 * <p>
 	 * Calendar Objects will be converted into Date Objects. ItemCollection
 	 * works typical with Date Objects instead of Calendar Objects.
 	 * <p>
 	 * The method verifies if the callerPricipal has author access and adds the
 	 * attribute $isAuthor into the ItemCollection to indicate the author access
 	 * of this entity for the current user.
 	 * 
 	 * <p>
 	 * Note: $readAccess and $writeAccess fields are not removed during save. so
 	 * we did not care about those values
 	 * 
 	 * @see explodeEntity()
 	 * @return
 	 */
 	@SuppressWarnings("unchecked")
 	private ItemCollection implodeEntity(Entity aEntity) {
 		// create new empty ItemCollection
 		ItemCollection itemCollection = new ItemCollection();
 
 		// verify author access and add property '$isauthor'
 		try {
 			itemCollection.replaceItemValue("$isauthor",
 					isCallerAuthor(aEntity));
 		} catch (Exception e1) {
 		}
 
 		/*
 		 * Now we first copy for each IndexProperty the values into our new
 		 * ItemColleciton Object.
 		 */
 
 		// so now copy all separated TextItems
 		for (TextItem textItem : aEntity.getTextItems()) {
 			try {
 				List vValue = itemCollection.getItemValue(textItem.itemName);
 				vValue.add(textItem.itemValue);
 				itemCollection.replaceItemValue(textItem.itemName, vValue);
 			} catch (Exception e) {
 				logger.warning("[EntityService] could not implode ItemValue: "
 						+ textItem.itemName);
 			}
 		}
 
 		// copy all separated IntegerItems
 		for (IntegerItem ii : aEntity.getIntegerItems()) {
 			try {
 				List vValue = itemCollection.getItemValue(ii.itemName);
 				vValue.add(ii.itemValue);
 				itemCollection.replaceItemValue(ii.itemName, vValue);
 			} catch (Exception e) {
 				logger.warning("[EntityService] could not implode ItemValue: "
 						+ ii.itemName);
 			}
 		}
 
 		// copy all separated DoubleItems
 		for (DoubleItem di : aEntity.getDoubleItems()) {
 			try {
 				List vValue = itemCollection.getItemValue(di.itemName);
 				vValue.add(di.itemValue);
 				itemCollection.replaceItemValue(di.itemName, vValue);
 			} catch (Exception e) {
 				logger.warning("[EntityService] could not implode ItemValue: "
 						+ di.itemName);
 			}
 		}
 
 		// copy all separated CalendarItems
 		for (CalendarItem ci : aEntity.getCalendarItems()) {
 			try {
 				List vValue = itemCollection.getItemValue(ci.itemName);
 				// Calendar Objects will be converted into Date Objects
 				vValue.add(ci.itemValue.getTime());
 				itemCollection.replaceItemValue(ci.itemName, vValue);
 			} catch (Exception e) {
 				logger.warning("[EntityService] could not implode ItemValue: "
 						+ ci.itemName);
 			}
 		}
 
 		/*
 		 * After we added all indexProperties to our empty ItemCollection we can
 		 * now add the rest of the properties contained in the EntityData
 		 * Object. But we need to make sure that we do not overwrite an new
 		 * created index property. So we do not simply call the putAll() method.
 		 * But we verify each property name and value. If its new than we will
 		 * copy the values - again not the Vector! as we want to avoid to
 		 * manipulate or return managed objects. So we do create new Vector()
 		 * instances for each property
 		 */
 
 		// ! do not use :
 		// detachedItemCollection.getAllItems().putAll(activeEntity.getData().getItemCollection().getAllItems());
 		Map activeMap = aEntity.getData();
 		if (activeMap != null) {
 			// iterate over all properties
 			Iterator iter = activeMap.entrySet().iterator();
 			while (iter.hasNext()) {
 				Map.Entry mapEntry = (Map.Entry) iter.next();
 				// extract name and value
 				String activePropertyName = mapEntry.getKey().toString();
 				if (!itemCollection.hasItem(activePropertyName)) {
 					// iitemValuef we did not have created this property from
 					// the
 					// indexProperties before, so we add a new property....
 					Vector activePropertyValue = (Vector) mapEntry.getValue();
 					// create new Vector
 					Vector detachePropertyValue = new Vector();
 					// copy objects
 					detachePropertyValue.addAll(activePropertyValue);
 					// add property
 					try {
 						itemCollection.replaceItemValue(activePropertyName,
 								detachePropertyValue);
 					} catch (Exception e) {
 						// unexpected exception - but we will continue work
 						logger.warning("[EntityService] Warning implodeEntity - "
 								+ e.getMessage());
 					}
 				}
 				// now we are sure that we have a new instance of an vector
 				// for each property
 			}
 		}
 
 		// if disable Optimistic Locking is TRUE we do not add the version
 		// number
 		if (!disbaleOptimisticLocking) {
 			itemCollection.replaceItemValue("$Version", aEntity.getVersion());
 		}
 
 		return itemCollection;
 	}
 
 	/**
 	 * This method is used to optimize a query by adding a where clause which
 	 * ensures that the query will only include entities accessible by the
 	 * current user. For this reason the method adds a clause which test the
 	 * readAccess property for predefined roles and usernames.
 	 * <p>
 	 * This method is most important to secure each findAllEntiteis method call.
 	 * <p>
 	 * Example
 	 * <p>
 	 * Here are some Examples:
 	 * <ul>
 	 * <li>SELECT entity FROM Entity entity
 	 * <li>convert into
 	 * <li>SELECT entity FROM Entity entity, entity.readAccessList access WHERE
 	 * access.value IN ('xxx')
 	 * </ul>
 	 * 
 	 * <ul>
 	 * <li>SELECT entity FROM Entity entity WHERE entity.type='workitem'
 	 * <li>convert into
 	 * <li>SELECT entity FROM Entity entity, entity.readAccessList access WHERE
 	 * access.value IN ('rsoika') AND entity.type='workitem'
 	 * </ul>
 	 * <p>
 	 * The Method also verifies if a DISTINCT clause is used in the Query. If
 	 * not the method will add a distinct clause to avoid duplicates in the
 	 * returned result set. duplicates can be returned by complex queries with
 	 * multiple joins.
 	 * 
 	 * @param aQuery
 	 * @return
 	 * @throws InvalidAccessException
 	 * 
 	 */
 	private String optimizeQuery(String aQuery) throws InvalidAccessException {
 		// String nameList = "";
 		StringBuffer nameListBuf = new StringBuffer();
 		String nameList = "";
 		aQuery = aQuery.trim();
 		StringTokenizer st = new StringTokenizer(aQuery);
 		// find identifier for Entity
 		if (st.countTokens() < 5)
 			throw new InvalidAccessException(
 					"[EntityService] Invalid query format: " + aQuery);
 
 		// test if DISTINCT clause is included
 		st.nextToken();
 		String sDistinct = st.nextToken();
 		if (!"distinct".equals(sDistinct.toLowerCase().trim())) {
 			// add distinct.
 			int iDisPos = aQuery.toLowerCase().indexOf("select") + 6;
 			aQuery = aQuery.substring(0, iDisPos) + " DISTINCT"
 					+ aQuery.substring(iDisPos);
 		}
 
 		// don't optimize for managers...
 		if (ctx.isCallerInRole(ACCESSLEVEL_MANAGERACCESS))
 			return aQuery;
 
 		// now construct user role list
 		List<String> auserNameList = getUserNameList();
 		for (String auserName : auserNameList) {
 			nameListBuf.append(",'" + auserName + "'");
 		}
 		// remove first ,
 		nameListBuf.deleteCharAt(0);
 
 		nameList = nameListBuf.toString();
 
 		logger.fine("Optimized NameList = " + nameList);
 
 		// now select identifier - this is the last word before a 'WHERE',
 		// 'ORDER BY' or 'JOIN'
 		int iPos = 0;
 		// we begin with join
 		iPos = aQuery.toLowerCase().indexOf("join");
 		if (iPos == -1)
 			// test for where
 			iPos = aQuery.toLowerCase().indexOf("where");
 		if (iPos == -1)
 			// and end to test for order by clause
 			iPos = aQuery.toLowerCase().indexOf("order by");
 
 		String firstPart;
 		if (iPos == -1)
 			firstPart = aQuery;
 		else
 			firstPart = aQuery.substring(0, iPos - 1);
 
 		firstPart = firstPart.trim();
 
 		iPos = firstPart.length();
 		// go back to first ' '
 		String identifier = null;
 		identifier = firstPart.substring(firstPart.lastIndexOf(' ')).trim();
 
 		// test for ,
 		if (identifier.endsWith(","))
 			identifier = identifier.substring(0, identifier.length() - 1);
 
 		// add access JOIN (should be a unique token name)
 		String aNewQuery = aQuery.substring(0, iPos);
 		aNewQuery += " LEFT JOIN " + identifier + ".readAccessList access807 ";
 		aNewQuery += aQuery.substring(iPos);
 
 		aQuery = aNewQuery.trim();
 		// test if WHERE clause is available
 		int iWherePos = aQuery.toLowerCase().indexOf("where");
 		if (iWherePos > -1) {
 			// insert access check
 			aNewQuery = aQuery.substring(0, iWherePos + 5)
 					+ " (access807.value IS NULL OR access807.value IN ("
 					+ nameList + ")) AND " + aQuery.substring(iWherePos + 6);
 			aQuery = aNewQuery;
 
 		} else {
 			// no WHERE clause - so add a new one
 			int iOrderPos = aQuery.toLowerCase().indexOf("order by");
 			if (iOrderPos > -1)
 				aNewQuery = aQuery.substring(0, iOrderPos - 1)
 						+ " WHERE (access807.value IS NULL OR access807.value IN ("
 						+ nameList + ")) " + aQuery.substring(iOrderPos);
 			else
 				aNewQuery = aQuery
 						+ " WHERE (access807.value IS NULL OR access807.value IN("
 						+ nameList + ")) ";
 
 			aQuery = aNewQuery;
 		}
 
 		logger.fine("Optimized Query=" + aQuery);
 		return aQuery;
 	}
 
 	/**
 	 * This Method updates the index properties from a collection of entities.
 	 * The Method first implodes the Entity to get all external properties and
 	 * than calls an explode to recreate the index properties.
 	 * 
 	 * 
 	 * Method can be optimized
 	 * 
 	 * 
 	 * @see issue #62
 	 */
 	private void updateAllEntityIndexFields(Collection<Entity> entityList,
 			String newIndexField) {
 		long count = 0;
 
 		// get a List of all existing Indices
 		Collection<EntityIndex> entityIndexCache = getIndices();
 
 		Iterator<Entity> iter = entityList.iterator();
 		while (iter.hasNext()) {
 			Entity activeEntity = iter.next();
 			ItemCollection slimItemcol = new ItemCollection(
 					activeEntity.getData());
 			// test if activeEntity is affected from this index
 			if (newIndexField == null || slimItemcol.hasItem(newIndexField)) {
 
 				try {
 					/*
 					 * try { manager.refresh(activeEntity); } catch
 					 * (EntityNotFoundException ex) { logger.warning(
 					 * "[EntityServiceBean] #issue 102 - refresh() EntityNotFoundException"
 					 * ); activeEntity = null; }
 					 */
 
 					// implode each Entity into its ItemCollection
 					ItemCollection itemcol = implodeEntity(activeEntity);
 
 					ItemCollection slimItemCollection = new ItemCollection(
 							itemcol.getAllItems());
 
 					// update read- and writeAccess List
 					updateReadAccessList(slimItemCollection, activeEntity);
 					updateWriteAccessList(slimItemCollection, activeEntity);
 
 					explodeEntity(slimItemCollection, activeEntity,
 							entityIndexCache);
 
 					// finally update the data field
 					activeEntity.setData(slimItemCollection.getAllItems());
 
 				} catch (Exception merex) {
 					logger.info("[EntityServiceBean] Error updateAllEntityIndexFields for Entity : "
 							+ activeEntity.getId());
 					merex.printStackTrace();
 
 				}
 				count++;
 			}
 		}
 		logger.info("[EntityServiceBean] " + count + " effective updates");
 	}
 
 }