CatalogDAO.java

package com.tesora.dve.common.catalog;

/*
 * #%L
 * Tesora Inc.
 * Database Virtualization Engine
 * %%
 * Copyright (C) 2011 - 2014 Tesora Inc.
 * %%
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License, version 3,
 * as published by the Free Software Foundation.
 * 
 * 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 Affero General Public License for more details.
 * 
 * You should have received a copy of the GNU Affero General Public License
 * along with this program. If not, see <http://www.gnu.org/licenses/>.
 * #L%
 */

import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.concurrent.atomic.AtomicReference;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.LockModeType;
import javax.persistence.Persistence;
import javax.persistence.PersistenceException;
import javax.persistence.Query;
import javax.sql.DataSource;

import net.sf.ehcache.CacheManager;
import net.sf.ehcache.Statistics;

import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.mchange.v2.c3p0.C3P0Registry;
import com.tesora.dve.common.DBHelper;
import com.tesora.dve.common.PEUrl;
import com.tesora.dve.distribution.DistributionRange;
import com.tesora.dve.distribution.RangeTableRelationship;
import com.tesora.dve.distribution.RangeTableRelationship.RangeTableRelationshipCacheLookup;
import com.tesora.dve.exceptions.PECodingException;
import com.tesora.dve.exceptions.PEException;
import com.tesora.dve.exceptions.PENotFoundException;
import com.tesora.dve.groupmanager.GroupManager;
import com.tesora.dve.sql.util.Functional;

public class CatalogDAO {
	private static Logger logger = Logger.getLogger(CatalogDAO.class);

	AtomicReference<EntityManager> em = new AtomicReference<EntityManager>();

	int transactionDepth = 0;

	private CatalogDAO(EntityManager em) {
		this.em.set(em);
	}

	public void close() {
		EntityManager theEm = em.getAndSet(null);
		if (theEm != null && theEm.isOpen())
			theEm.close();
	}

	@Override
	public String toString() {
		return "CatalogDAO@" + System.identityHashCode(this);
	}

	public void begin() {
		if (transactionDepth++ == 0) {
			em.get().getTransaction().begin();
			// System.out.println("@" + System.identityHashCode(this) +
			// " beginning new txn");
		}
	}

	public void nonNestedBegin() {
		if (transactionDepth > 0)
			throw new PECodingException("Attempt to start non-nested transaction inside transaction scope");
		++transactionDepth;
		em.get().getTransaction().begin();
	}

	public Query createQuery(String queryString) {
		return em.get().createQuery(queryString);
	}

	public void commit() {
		if (transactionDepth == 1) {
			em.get().getTransaction().commit();
		}
		if (transactionDepth > 0)
			--transactionDepth;
	}

	public void rollbackNoException() {
		if (transactionDepth > 0)
			em.get().getTransaction().rollback();
		transactionDepth = 0;
	}

	public void rollback(Throwable t) {
		try {
			if (em.get().getTransaction().isActive())
				rollbackNoException();
		} catch (Throwable rollbackT) {
			logger.warn("Exception during rollback", rollbackT);
			throw new PersistenceException("Exception during rollback (check log for rollback exception)", t);
		}
	}

	public void retryableRollback(Throwable t) {
		try {
			if (em.get().getTransaction().isActive())
				rollbackNoException();
		} catch (Throwable rollbackT) {
			logger.warn("Exception during rollback", rollbackT);
			logger.warn("Original exception", t);
		}
	}

	public void cleanupRollback() {
		if (em.get().getTransaction().isActive())
			em.get().getTransaction().rollback();
		transactionDepth = 0;
	}

	public void persistToCatalog(Object o) {
		em.get().persist(o);
	}

	@SuppressWarnings("unchecked")
	public <T extends CatalogEntity> T findByKey(Class<? extends CatalogEntity> c, int id) {
		return (T) em.get().find(c, id);
	}

	@SuppressWarnings("unchecked")
	public <T extends CatalogEntity> T findByName(Class<? extends CatalogEntity> tClass, String entityName,
			boolean except) throws PENotFoundException {
		return (T) CatalogDAOFactory.INSTANCE.getLookupCache(tClass, "name").findByValue(this, entityName, except);

	}

	@SuppressWarnings("unchecked")
	private <T> List<T> findAllByClass(Class<T> entityClass) {
		Query q = em.get().createQuery("from " + entityClass.getSimpleName());
		return (List<T>) q.getResultList();
	}

	@SuppressWarnings("unchecked")
	public List<CatalogEntity> queryCatalogEntity(String queryStr) {
		return queryCatalogEntity(queryStr, Collections.EMPTY_MAP);
	}

	@SuppressWarnings("rawtypes")
	public List nativeQuery(String nativeQueryString, Map<String, Object> params) {
		Query q = em.get().createNativeQuery(nativeQueryString);
		for (Map.Entry<String, Object> me : params.entrySet())
			q.setParameter(me.getKey(), me.getValue());
		return q.getResultList();
	}

	@SuppressWarnings("rawtypes")
	public List nativeQuery(String nativeQueryString, Map<String, Object> params, Class<?> targ) {
		Query q = em.get().createNativeQuery(nativeQueryString, targ);
		for (Map.Entry<String, Object> me : params.entrySet())
			q.setParameter(me.getKey(), me.getValue());
		return q.getResultList();
	}

	@SuppressWarnings("unchecked")
	public List<CatalogEntity> queryCatalogEntity(String queryStr, Map<String, Object> params) {
		Query query = em.get().createQuery(queryStr);
		for (Map.Entry<String, Object> me : params.entrySet())
			query.setParameter(me.getKey(), me.getValue());
		return (List<CatalogEntity>) query.getResultList();
	}

	public List<User> findAllUsers() {
		return findAllByClass(User.class);
	}

	public List<DistributionModel> findAllDistributionModels() {
		return findAllByClass(DistributionModel.class);
	}

	public List<UserDatabase> findAllUserDatabases() {
		return findAllByClass(UserDatabase.class);
	}

	public List<Project> findAllProjects() {
		return findAllByClass(Project.class);
	}

	public List<Provider> findAllProviders() {
		return findAllByClass(Provider.class);
	}

	public List<DynamicPolicy> findAllDynamicPolicies() {
		return findAllByClass(DynamicPolicy.class);
	}

	public List<ExternalService> findAllExternalServices() {
		return findAllByClass(ExternalService.class);
	}

	public List<CharacterSets> findAllCharacterSets() {
		return findAllByClass(CharacterSets.class);
	}

	public List<Collations> findAllCollations() {
		return findAllByClass(Collations.class);
	}

	@SuppressWarnings("unchecked")
	public List<UserTable> findAllTablesInPersistentGroup(PersistentGroup sg) {
		Query query = em.get().createQuery("from UserTable ut where ut.persistentGroup = :storageGroup");
		query.setParameter("storageGroup", sg);

		return (List<UserTable>) query.getResultList();
	}

	@SuppressWarnings("unchecked")
	public PersistentGroup findBalancedPersistentGroup(String prefix) {
		StringBuilder builder = new StringBuilder(
				"select pg.persistent_group_id, count(ud.user_database_id) as usecount from persistent_group pg left outer join user_database ud on pg.persistent_group_id = ud.default_group_id where pg.name not in ('InformationSchemaGroup','SystemGroup') ");
		if (StringUtils.isNotBlank(prefix))
			builder.append("and pg.name like '" + prefix + "%'");
		builder.append(" group by pg.persistent_group_id order by usecount asc, pg.persistent_group_id limit 1");

		Query query = em.get().createNativeQuery(builder.toString());

		List<Object[]> out = query.getResultList();
		if (out.isEmpty())
			return null;
		if (out.size() != 1)
			throw new IllegalStateException("Failed to find persistent group");
		Object[] first = out.get(0);
		return (PersistentGroup) findByKey(PersistentGroup.class, (Integer) first[0]);
	}

	@SuppressWarnings("unchecked")
	public List<UserDatabase> findAllMTDatabases() {
		Query query = em.get().createQuery("from UserDatabase ud where ud.multitenant_mode != 'off'");
		return (List<UserDatabase>) query.getResultList();
	}

	public DistributionModel findDistributionModel(String name) throws PEException {
		return findByName(DistributionModel.class, name, true);
	}

	public Map<String, DistributionModel> getDistributionModelMap() {
		Map<String, DistributionModel> modelMap = new HashMap<String, DistributionModel>();
		for (DistributionModel d : findAllByClass(DistributionModel.class))
			modelMap.put(d.getName(), d);
		return modelMap;
	}

	public PersistentSite createPersistentSite(String name, String url, String user, String password)
			throws PEException {
		SiteInstance si = new SiteInstance(name, url, user, password);
		PersistentSite db = new PersistentSite(name, si);
		em.get().persist(db);
		em.get().persist(si);
		return db;
	}

	public PersistentSite createPersistentSite(String siteName, String haType, SiteInstance master,
			SiteInstance[] replicants) throws PEException {
		PersistentSite theSite = new PersistentSite(siteName, master);
		theSite.setHaType(haType);
		em.get().persist(theSite);
		theSite.addAll(replicants);
		return theSite;
	}

	public PersistentGroup createPersistentGroup(String name) {
		PersistentGroup sc = new PersistentGroup(name);
		em.get().persist(sc);
		return sc;
	}

	public UserTable createUserTable(UserDatabase c, String name,
			DistributionModel dist, PersistentGroup sc, String engine, String tableType) {
		UserTable ut = new UserTable(name, dist, c, TableState.SHARED, engine, tableType);
		// dist.addUserTable(ut);
		ut.setPersistentGroup(sc);
		c.addUserTable(ut);
		em.get().persist(ut);
		return ut;
	}

	public User createUser(String name, String password, String accessSpec) {
		User u = new User(name, password, accessSpec);
		em.get().persist(u);
		return u;
	}

	public User createUser(String name, String password, String accessSpec, boolean adminUser) {
		User u = new User(name, password, accessSpec, adminUser);
		em.get().persist(u);
		return u;
	}

	public UserColumn createUserColumn(UserTable ut, String name, int dataType,
			String nativeTypeName, int size) {
		// TODO figure out best way to inject other column values
		// into this...
		UserColumn uc = new UserColumn(ut, name, dataType, nativeTypeName, size);

		uc.setAutoGenerated(false);
		uc.setNullable(true);
		uc.setHasDefault(false);

		return uc;
	}

	public UserDatabase createDatabase(String name, PersistentGroup sg, String charSet, String collation) {
		UserDatabase c = new UserDatabase(name, sg, null, TemplateMode.OPTIONAL, MultitenantMode.OFF, FKMode.STRICT, charSet, collation);
		em.get().persist(c);
		return c;
	}

	public Project createProject(String name) {
		Project p = new Project(name);
		em.get().persist(p);
		return p;
	}

	public Engines createEngines(String engine, String support, String comment, String transactions, String xa,
			String savepoints) {
		Engines engines = new Engines(engine, support, comment, transactions, xa, savepoints);
		em.get().persist(engines);
		return engines;
	}

	public Provider createProvider(String name, String plugin) {
		Provider prov = new Provider(name, plugin);
		em.get().persist(prov);
		return prov;
	}

	public Provider createProvider(String name, String plugin, String config) {
		Provider prov = new Provider(name, plugin, StringEscapeUtils.escapeSql(config));
		em.get().persist(prov);
		return prov;
	}
	
	public ExternalService createExternalService(String name, String plugin, String connectUser, boolean usesDataStore) throws PEException {
		return createExternalService(name, plugin, connectUser, usesDataStore, null);
	}

	public ExternalService createExternalService(String name, String plugin, String connectUser, boolean usesDataStore,
			String config) throws PEException {
		ExternalService extServ = new ExternalService(name, plugin, connectUser, usesDataStore);
		if (config != null)
			extServ.setConfig(config);
		em.get().persist(extServ);

		return extServ;
	}

	// -----------------------------------------------------------------
	// FIND Methods
	// 
	
	public List<PersistentGroup> findAllPersistentGroups() {
		return findAllByClass(PersistentGroup.class);
	}

	public List<UserTable> findAllUserTables() {
		return findAllByClass(UserTable.class);
	}

	public List<UserColumn> findAllUserColumns() {
		return findAllByClass(UserColumn.class);
	}

	public List<PersistentSite> findAllPersistentSites() {
		return findAllByClass(PersistentSite.class);
	}
	
	public PersistentGroup findDefaultPersistentGroup() throws PEException {
		return findByName(PersistentGroup.class, "Default", true);
	}

	public Project findDefaultProject() throws PEException {
		return findProject(Project.DEFAULT, true);
	}

	public Project findProject(String projectName) throws PEException {
		return findProject(projectName, true);
	}

	public Project findProject(String projectName, boolean except) throws PEException {
		return (Project) CatalogDAOFactory.INSTANCE.getLookupCache(Project.class, "name").findByValue(this,
				projectName, except);
	}

	public VariableConfig findVariableConfig(String variableName) throws PENotFoundException {
		return findVariableConfig(variableName, true);
	}
	
	public VariableConfig findVariableConfig(String variableName, boolean except) throws PENotFoundException {
		return (VariableConfig) CatalogDAOFactory.INSTANCE.getLookupCache(VariableConfig.class, "name").findByValue(this,
				variableName, except);
	}
	
	public List<VariableConfig> findAllVariableConfigs() {
		return findAllByClass(VariableConfig.class);
	}
	
	public UserDatabase findDatabase(String databaseName) throws PEException {
		return findDatabase(databaseName, true);
	}

	public UserDatabase findDatabase(String databaseName, boolean except) throws PEException {
		return findByName(UserDatabase.class, databaseName, except);
	}

	public PersistentGroup findPersistentGroup(String groupName) throws PEException {
		return findPersistentGroup(groupName, true);
	}

	public PersistentGroup findPersistentGroup(String groupName, boolean except) throws PEException {
		return findByName(PersistentGroup.class, groupName, except);
	}

	public PersistentSite findPersistentSite(String siteName) throws PEException {
		return findPersistentSite(siteName, true);
	}

	public PersistentGroup buildAllSitesGroup() throws PEException {
		List<PersistentSite> sites = findAllPersistentSites();
		final LinkedHashMap<String,PersistentSite> uniqueURLS = new LinkedHashMap<String,PersistentSite>();
		for(PersistentSite ps : sites) {
			String key = ps.getMasterUrl();
			PersistentSite already = uniqueURLS.get(key);
			if (already == null) 
				uniqueURLS.put(key,ps);
		}
		return new PersistentGroup(uniqueURLS.values());
	}
	
	@SuppressWarnings("unchecked")
	public static <T> T onlyOne(List<T> results, String what, String searchedOn, boolean except)
			throws PENotFoundException {
		if (results.size() > 1 || (results.size() == 0 && except)) {
			throw new PENotFoundException("Expected exactly one " + what + " for name " + searchedOn);
		}
		if (results.size() == 0)
			return null;
		return (T) results.toArray()[0];
	}

	@SuppressWarnings("unchecked")
	public static <T> T onlyOne(List<? extends NamedCatalogEntity> results, String what, String dbid, String name,
			boolean except, boolean quoted) throws PENotFoundException {
		String searchedOn = dbid + ((name != null) ? ("/" + name) : "");

		if (results.size() == 0 && except) {
			throw new PENotFoundException("Expected exactly one " + what + " for name " + searchedOn);
		}
		if (results.size() == 0)
			return null;

		T ce = null;
		for (int i = 0; i < results.size(); i++) {
			T entity = (T) results.get(i);
			ce = findExactNameMatch(entity, name);
			if (ce != null) {
				break;
			}
		}
		if (quoted) {
			if (except && ce == null) {
				throw new PENotFoundException("Expected exactly one " + what + " for name " + searchedOn);
			}
			return ce;
		} else {
			// not quoted so if exact match then return it
			if (ce != null) {
				return ce;
			}
			// otherwise return any matching entity
			return (T) results.get(0);
		}
	}

	static private <T> T findExactNameMatch(T ce, String searchedOn) {
		T ret = null;
		// must be exact match
		if (StringUtils.equals(searchedOn, ((NamedCatalogEntity) ce).getName())) {
			ret = ce;
		}

		return ret;
	}

	public PersistentSite findPersistentSite(String siteName, boolean except) throws PEException {
		return findByName(PersistentSite.class, siteName, except);
	}

	public <T> T merge(T o) {
		return em.get().merge(o);
	}

	public <T> void detach(T o) {
		em.get().detach(o);
	}

	public boolean isDetached(Object o) {
		return !em.get().contains(o);
	}

	public <T> void remove(T o) {
		em.get().remove(o);
		CatalogDAOFactory.INSTANCE.clearLookupCache(o.getClass());
	}

	public <T> void refresh(T o) {
		try {
			em.get().refresh(o);
		} catch (IllegalArgumentException iae) {
			throw iae;
		}
	}

	public <T> void refreshForLock(T o) {
		em.get().refresh(o, LockModeType.PESSIMISTIC_WRITE);
	}

	public DistributionRange findRangeForTable(UserTable userTable) throws PEException {
		return findRangeTableRelationship(userTable, true).getRange();
	}

	public DistributionRange findRangeForTableByName(String qualifiedTableName, int tableId) throws PEException {
		return findRangeTableRelationshipByTableId(qualifiedTableName, tableId, true).getRange();
	}
	
	@SuppressWarnings("unchecked")
	public DistributionRange findRangeByName(String name, String groupName, boolean except) throws PEException {
		Query q = em.get().createQuery("from DistributionRange dr where dr.name = :name and dr.storageGroup.name = :groupName");
		q.setParameter("name", name);
		q.setParameter("groupName", groupName);
		List<DistributionRange> results = q.getResultList();
		return onlyOne(results, "DistributionRange", name + "/" + groupName, except); 
	}
	
	@SuppressWarnings("unchecked")
	public List<DistributionRange> findRangeByName(String name) throws PEException {
		Query q = em.get().createQuery("from DistributionRange dr where dr.name = :name");
		q.setParameter("name", name);
		return q.getResultList();
	}
	
	@SuppressWarnings("unchecked")
	public List<DistributionRange> findRangesOnGroup(String groupName) throws PEException {
		Query q = em.get().createQuery("from DistributionRange dr where dr.storageGroup.name = :groupName");
		q.setParameter("groupName", groupName);
		return q.getResultList();
	}

	// existence test, mostly
	public RangeTableRelationship findRangeTableRelationship(UserTable ut, boolean except) throws PEException {
		return (RangeTableRelationship) CatalogDAOFactory.INSTANCE
				.getLookupCache(RangeTableRelationship.class, "table").findByValue(this, ut.getQualifiedName(), ut,
						except);
	}

	public RangeTableRelationship findRangeTableRelationshipByTableId(String tableQualifiedName, int tableId,
			boolean except) throws PEException {
		return (RangeTableRelationship) CatalogDAOFactory.INSTANCE.getLookupCache(RangeTableRelationship.class,
				"table.id").findByValue(this, tableQualifiedName, tableId, except);
	}

	public Provider findProvider(String name) throws PEException {
		return findProvider(name, true);
	}

	public Provider findProvider(String name, boolean except) throws PEException {
		return findByName(Provider.class, name, except);
	}

	public DynamicPolicy findDynamicPolicy(String name) throws PEException {
		return findDynamicPolicy(name, true);
	}

	public DynamicPolicy findDynamicPolicy(String name, boolean except) throws PEException {
		return findByName(DynamicPolicy.class, name, except);
	}

	public ExternalService findExternalService(String name) throws PEException {
		return findExternalService(name, true);
	}

	public ExternalService findExternalService(String name, boolean except) throws PEException {
		return findByName(ExternalService.class, name, except);
	}

	@SuppressWarnings("unchecked")
	public List<User> findUsers(String name, String accessSpec) throws PEException {
		Query query = null;
		if (accessSpec == null)
			query = em.get().createQuery("from User u where u.name = :name");
		else {
			query = em.get().createQuery("from User u where u.name = :name and u.accessSpec = :accessSpec");
			query.setParameter("accessSpec", accessSpec);
		}
		query.setParameter("name", name);
		return (List<User>) query.getResultList();
	}

	public User findUser(String name, boolean except) throws PEException {
		List<User> candidates = findUsers(name, null);

		if (candidates.isEmpty()) {
			if (except)
				throw new PENotFoundException("User " + name + " not found in catalog");
			return null;
		}

		User catalogUser = null;
		catalogUser = candidates.get(0);
		if (candidates.size() > 1)
			logger.debug("More than one user found, choosing '" + catalogUser.getName() + "'@'"
					+ catalogUser.getAccessSpec() + "'");

		return catalogUser;
	}

	public User findUser(String name) throws PEException {
		return findUser(name, true);
	}

	public List<Tenant> findAllTenants() throws PEException {
		return findAllByClass(Tenant.class);
	}

	public Tenant findTenant(String extID) throws PEException {
		return findTenant(extID, true);
	}

	public Tenant findTenant(String extID, boolean except) throws PEException {
		return (Tenant) CatalogDAOFactory.INSTANCE.getLookupCache(Tenant.class, "externalID").findByValue(this, extID,
				except);
	}

	public List<TableVisibility> findAllTenantScopes() {
		return findAllByClass(TableVisibility.class);
	}

	public List<TableVisibility> findTenantScopesForTable(UserTable ut) {
		Query query = em.get().createQuery("from TableVisibility tv where tv.table = :ut");
		query.setParameter("ut", ut);
		@SuppressWarnings("unchecked")
		List<TableVisibility> res = query.getResultList();
		return res;
	}

	public TableVisibility findScopeForTable(UserTable ut, Tenant t, boolean except) throws PEException {
		Query query = em.get().createQuery("from TableVisibility tv where tv.table = :ut and tv.ofTenant = :t");
		query.setParameter("ut", ut);
		query.setParameter("t", t);
		@SuppressWarnings("unchecked")
		List<TableVisibility> res = query.getResultList();
		return onlyOne(res, "TenantVisibility", ut.getName() + "/" + t.getExternalTenantId(), except);
	}

	public TableVisibility findScopeForTable(int tableID, int tenantID, boolean except) throws PEException {
		Query query = em.get().createQuery(
				"from TableVisibility tv where tv.table.id = :tableID and tv.ofTenant.id = :tenantID");
		query.setParameter("tableID", tableID);
		query.setParameter("tenantID", tenantID);
		@SuppressWarnings("unchecked")
		List<TableVisibility> res = query.getResultList();
		return onlyOne(res, "TenantVisibility", tableID + "/" + tenantID, except);
	}

	// only used in adaptive mode - thus always has localName
	public TableVisibility findScope(int tenantID, String localName) throws PEException {
		Query query = em.get().createQuery(
				"from TableVisibility tv where tv.ofTenant.id = :tenantID and tv.localName = :localName");
		query.setParameter("tenantID", tenantID);
		query.setParameter("localName", localName);
		@SuppressWarnings("unchecked")
		List<TableVisibility> res = query.getResultList();
		return onlyOne(res, "TenantVisibility", localName + "/" + tenantID, true);
	}

	@SuppressWarnings("unchecked")
	public List<Priviledge> findPriviledgesOnDatabase(UserDatabase udb) throws PEException {
		Query query = em.get().createQuery("from Priviledge p where p.database = :udb");
		query.setParameter("udb", udb);
		return (List<Priviledge>) query.getResultList();
	}

	@SuppressWarnings("unchecked")
	public List<Tenant> findTenantsOnDatabase(UserDatabase udb) throws Exception {
		Query query = em.get().createQuery("from Tenant t where t.userDatabase = :udb");
		query.setParameter("udb", udb);
		return (List<Tenant>) query.getResultList();
	}

	@SuppressWarnings("unchecked")
	public List<ContainerTenant> findContainerTenants(Container ofContainer) throws Exception {
		Query query = em.get().createQuery("from ContainerTenant ct where ct.container = :cont");
		query.setParameter("cont", ofContainer);
		return (List<ContainerTenant>) query.getResultList();
	}

	@SuppressWarnings("unchecked")
	public List<Priviledge> findPriviledgesOnTenant(Tenant t) throws PEException {
		Query query = em.get().createQuery("from Priviledge p where p.tenant = :t");
		query.setParameter("t", t);
		return (List<Priviledge>) query.getResultList();
	}

	public Priviledge findMatchingPrivilege(int userid, String name) throws PEException {
		// Query query =
		// em.createQuery("from Priviledge p where (p.database.name = :name or p.tenant.externalID = :name) and p.user = :user");
		Query query = em
				.get()
				.createQuery(
						"select p from Priviledge p left join fetch p.database ud left join fetch p.tenant t where p.user.id = :userid and (ud.name = :name or t.externalID = :name)");
		query.setParameter("userid", userid);
		query.setParameter("name", name);
		@SuppressWarnings("unchecked")
		List<Priviledge> res = query.getResultList();
		return onlyOne(res, "Privilege", userid + "/" + name, false);
	}

	public boolean findGlobalPrivilege(User user) throws PEException {
		Query query = em.get().createQuery(
				"from Priviledge p where p.user = :user and p.database is null and p.tenant is null");
		query.setParameter("user", user);
		@SuppressWarnings("unchecked")
		List<Priviledge> res = query.getResultList();
		Priviledge candidate = onlyOne(res, "Privilege", user.getName() + "/*", false);
		return candidate != null;
	}

	@SuppressWarnings("unchecked")
	public List<TableVisibility> findMatchingTenantRecords(UserTable ut) {
		Query query = em.get().createQuery("from TableVisibility tv where tv.table = :ut ");
		query.setParameter("ut", ut);
		return (List<TableVisibility>) query.getResultList();
	}

	@SuppressWarnings("unchecked")
	public List<String> findTenantTableNames(Tenant t) {
		Query query = em.get().createQuery("select ut.name from UserTable ut where ut.name like :tname");
		query.setParameter("tname", "_" + t.getId() + "%");
		return (List<String>) query.getResultList();
	}

	@SuppressWarnings("unchecked")
	public List<TableVisibility> findMatchingScopes(Shape matchShape, TableState matchState) throws PEException {
		Query query = em
				.get()
				.createQuery(
						"from TableVisibility tv where tv.table.state = :state and tv.table.shape = :shape order by tv.table.name");
		query.setParameter("shape", matchShape);
		query.setParameter("state", matchState);
		return (List<TableVisibility>) query.getResultList();
	}

	@SuppressWarnings("unchecked")
	public List<UserTable> findMatchingTables(Shape matchShape, TableState matchState) throws PEException {
		Query query = em.get().createQuery(
				"from UserTable ut where ut.shape = :matchShape and ut.state = :matchState order by ut.name");
		query.setParameter("matchShape", matchShape);
		query.setParameter("matchState", matchState);
		return (List<UserTable>) query.getResultList();
	}

	public Shape findShape(UserDatabase indb, String logicalName, String definition, String typeHash)
			throws PEException {
		Query query = em
				.get()
				.createQuery(
						"from Shape s where s.userDatabase = :indb and s.tableDefinition = :definition and s.name = :logicalName and s.typeHash = :typeHash");
		query.setParameter("indb", indb);
		query.setParameter("definition", definition);
		query.setParameter("logicalName", logicalName);
		query.setParameter("typeHash", typeHash);
		@SuppressWarnings("unchecked")
		List<Shape> results = query.getResultList();
		return onlyOne(results, "Shape", indb.getName() + "/" + logicalName, false);
	}

	@SuppressWarnings("unchecked")
	public List<UserTable> findMatchingTables(UserDatabase indb, String logicalName, String definition) {
		Query query = em
				.get()
				.createQuery(
						"from UserTable ut where ut.userDatabase = :indb and ut.shape.tableDefinition = :definition and ut.shape.name = :logicalName and ut.stale != 1");
		query.setParameter("indb", indb);
		query.setParameter("definition", definition);
		query.setParameter("logicalName", logicalName);
		return (List<UserTable>) query.getResultList();
	}

	@SuppressWarnings("unchecked")
	public List<Shape> findShapesOnDB(UserDatabase indb) {
		Query query = em.get().createQuery("from Shape s where s.userDatabase = :indb");
		query.setParameter("indb", indb);
		return (List<Shape>) query.getResultList();
	}

	public UserTable findUserTable(int dbid, String tableName, boolean quoted) throws PEException {
		Query query = em.get().createQuery("from UserTable ut where ut.name = :name and ut.userDatabase.id = :dbid");
		query.setParameter("name", tableName);
		query.setParameter("dbid", dbid);
		@SuppressWarnings("unchecked")
		List<UserTable> res = query.getResultList();
		return onlyOne(res, "UserTable", Integer.toString(dbid), tableName, false, quoted);

	}

	@SuppressWarnings("unchecked")
	public List<UserTable> findTablesOnGroup(String groupName) {
		Query query = em.get().createQuery("from UserTable ut where ut.persistentGroup.name = :groupName");
		query.setParameter("groupName",groupName);
		return query.getResultList();
	}
	
	@SuppressWarnings("unchecked")
	public UserView findView(String viewName, String dbName) throws PEException {
		Query query = em.get().createQuery(
				"from UserView uv where uv.table.name = :viewName and uv.table.userDatabase.name = :dbName");
		query.setParameter("viewName", viewName);
		query.setParameter("dbName", dbName);
		List<UserView> res = query.getResultList();
		return onlyOne(res, "UserView", dbName + "." + viewName, false);
	}

	public void recoverTransactions() {
//		DBConnectionParameters dbp = new DBConnectionParameters(Host.getProperties());
//		for (TransactionRecord txn : findAltemplate(TransactionRecord.class)) {
//			txn.recover2PC(this, dbp.getUserAuthentication());
//		}
	}

	@SuppressWarnings("unchecked")
	public List<UserTable> findContainerMemberTables(String containerName) throws PEException {
		Query query = em.get().createQuery("from UserTable ut where ut.container.name = :name");
		query.setParameter("name", containerName);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	public List<PersistentTemplate> findMatchTemplates() throws PEException {
		Query query = em.get().createQuery("from PersistentTemplate pt where pt.dbmatch is not null order by pt.name");
		return query.getResultList();
	}

	public PersistentTemplate findTemplate(String name, boolean except) throws PEException {
		return findByName(PersistentTemplate.class, name, except);
	}
	
	public RawPlan findRawPlan(String name, boolean except) throws PEException {
		return findByName(RawPlan.class, name, except);
	}

	@SuppressWarnings("unchecked")
	public List<RawPlan> findAllEnabledRawPlans() throws PEException {
		Query q = em.get().createQuery("from RawPlan rp where rp.enabled = 1");
		return q.getResultList();
	}

	public abstract class EntityUpdater {
		public abstract CatalogEntity update() throws Throwable;

		/**
		 * A utility method that encapsulates the logic of updating a catalog
		 * entity
		 * 
		 * @return
		 * @throws Throwable
		 */
		public CatalogEntity execute() throws Throwable {
			CatalogEntity ret = null;
			begin();
			try {
				ret = update();
				commit();
			} catch (Throwable t) {
				rollback(t);
				throw t;
			}
			return ret;
		}
	}

	public abstract class EntityGenerator extends EntityUpdater {
		public abstract CatalogEntity generate() throws Throwable;

		@Override
		public CatalogEntity update() throws Throwable {
			CatalogEntity entity = generate();
            if (entity != null)
			    persistToCatalog(entity);
			return entity;
		}
	}

	public PersistentTemplate createTemplate(String name, String template, String match, String comment) {
		PersistentTemplate t = new PersistentTemplate(name, template, match, comment);
		em.get().persist(t);
		return t;
	}
	
	public SiteInstance createSiteInstance(String name, String url, String user, String password) {
		SiteInstance si = new SiteInstance(name, url, user, password);
		em.get().persist(si);
		return si;
	}
	
	public SiteInstance createSiteInstance(String name, String url, String user, String password, boolean isMaster,
			String status) {
		SiteInstance si = new SiteInstance(name, url, user, password, isMaster, status);
		em.get().persist(si);
		return si;
	}

	public SiteInstance findSiteInstance(String name) throws PEException {
		return findSiteInstance(name, true);
	}

	public SiteInstance findSiteInstance(String name, boolean except) throws PEException {
		return findByName(SiteInstance.class, name, except);
	}

	public UserTable loadTable(UserTable foo) {
		UserTable theTable = foo;
		if (!em.get().getEntityManagerFactory().getPersistenceUnitUtil().isLoaded(theTable, "userColumns")) {

			Query query = em.get().createQuery("from UserTable ut JOIN FETCH ut.userColumns WHERE ut.id = :id");
			query.setParameter("id", theTable.getId());
			theTable = (UserTable) query.getResultList().get(0);
		}
		return theTable;
	}

	@SuppressWarnings("unchecked")
	public List<ServerRegistration> findAllRegisteredServers() {
		Query query = em.get().createQuery("from ServerRegistration");
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	public ServerRegistration findServerRegistrationByAddress(String ourAddress) throws PENotFoundException {
		Query query = em.get().createQuery("from ServerRegistration where ipAddress = :ipAddress");
		query.setParameter("ipAddress", ourAddress);
		List<ServerRegistration> results = query.getResultList();
		return onlyOne(results, ourAddress, "ipAddress", true);
	}

	public Container findContainer(String containerName) throws PEException {
		return findContainer(containerName, true);
	}

	public Container findContainer(String containerName, boolean except) throws PEException {
		return findByName(Container.class, containerName, except);
	}

	@SuppressWarnings("unchecked")
	public ContainerTenant findContainerTenant(String containerName, String disc) throws PEException {
		Query query = em.get().createQuery(
				"from ContainerTenant ct where ct.container.name = :containerName and ct.discriminant = :disc");
		query.setParameter("containerName", containerName);
		query.setParameter("disc", disc);
		return onlyOne((List<ContainerTenant>) query.getResultList(), "ContainerTenant", containerName + "/" + disc,
				false);
	}

	@SuppressWarnings("unchecked")
	public List<UserTable> findBaseTables(UserDatabase within) throws PEException {
		Query query = em.get().createQuery(
				"from UserTable ut where ut.userDatabase = :udb and ut.container.baseTable = ut");
		query.setParameter("udb", within);
		return (List<UserTable>) query.getResultList();
	}

	@SuppressWarnings("unchecked")
	public List<UserDatabase> findDatabasesWithin(Container cont) throws PEException {
		Query query = em.get().createQuery(
				"select distinct ut.userDatabase from UserTable ut where ut.container = :cont");
		query.setParameter("cont", cont);
		return (List<UserDatabase>) query.getResultList();
	}

	// this restricts the search to this server
	public List<TemporaryTable> findLocalUserlandTemporaryTables(Integer connID, String dbName, String tableName) {
		return findUserlandTemporaryTables(GroupManager.getCoordinationServices().getMemberAddress().toString(),connID,dbName, tableName);
	}
	
	// values not specified won't be queried on - so null,null,null returns all temp tables
	// and foo, null, null returns all temp tables on server foo
	@SuppressWarnings("unchecked")
	public List<TemporaryTable> findUserlandTemporaryTables(String serverName, Integer connID, String dbName, String tableName) {

		StringBuilder hql = new StringBuilder();
		
		hql.append("from TemporaryTable tt ");
		Query query = null;
		if (serverName == null && connID == null && tableName == null) {
			query = em.get().createQuery(hql.toString());
		} else {
			hql.append("where ");
			List<String> filters = new ArrayList<String>();
			if (serverName != null)
				filters.add("tt.server = :serverName");
			if (connID != null)
				filters.add("tt.sessionID = :connID");
			if (dbName != null)
				filters.add("tt.db = :dbName");
			if (tableName != null)
				filters.add("tt.name = :tableName");
			hql.append(Functional.join(filters, " and "));
			query = em.get().createQuery(hql.toString());
			if (serverName != null)
				query.setParameter("serverName",serverName);
			if (connID != null)
				query.setParameter("connID", connID);
			if (dbName != null)
				query.setParameter("dbName",dbName);
			if (tableName != null)
				query.setParameter("tableName", tableName);
		}
		return (List<TemporaryTable>)query.getResultList();
	}

	public void cleanupUserlandTemporaryTables(String serverName) {
		Query query = em.get().createQuery("delete from TemporaryTable where server = :name");
		query.setParameter("name", serverName);
		query.executeUpdate();
	}
	
	
	@SuppressWarnings("unchecked")
	public List<UserTable> findTablesWithUnresolvedFKsTargeting(String dbName, String tabName) {
		Query query = em
				.get()
				.createQuery(
						"select distinct k.userTable from Key k where k.constraint = 'FOREIGN' and k.referencedSchemaName = :dbName and k.referencedTableName = :tableName");
		query.setParameter("dbName", dbName);
		query.setParameter("tableName", tabName);
		return (List<UserTable>) query.getResultList();
	}

	@SuppressWarnings("unchecked")
	public List<UserTable> findTablesWithFKsReferencing(int tabID) {
		Query query = em
				.get()
				.createQuery(
						"select distinct k.userTable from Key k where k.constraint = 'FOREIGN' and k.referencedTable.id = :tabID");
		query.setParameter("tabID", tabID);
		return (List<UserTable>) query.getResultList();
	}

	@SuppressWarnings("unchecked")
	public List<TableVisibility> findScopesWithFKsReferencing(int tabID, int tenantID) {
		Query query = em.get().createQuery(
				"select tv from TableVisibility tv, Key k "
						+ "where k.constraint = 'FOREIGN' and k.referencedTable.id = :tabID "
						+ "and tv.ofTenant.id = :tenantID and k.userTable = tv.table");
		query.setParameter("tabID", tabID);
		query.setParameter("tenantID", tenantID);
		return (List<TableVisibility>) query.getResultList();
	}

	@SuppressWarnings("unchecked")
	public List<TableVisibility> findScopesWithUnresolvedFKsTargeting(String dbName, String tabName, int tenant) {
		/*
		 * I started with this sql: select s.scope_id from scope s inner join
		 * user_table ut on s.scope_table_id = ut.table_id inner join user_key
		 * uk on ut.table_id = uk.user_table_id where uk.forward_schema_name = ?
		 * and uk.forward_table_name = ? and s.scope_tenant_id = ?;
		 * 
		 * using this query, hql will cause us to deref the collection of keys 
		 * which would not be optimal
		 */
		Query query = em.get().createQuery(
				"select tv from TableVisibility tv, Key k "
						+ "where k.referencedSchemaName = :dbName and k.referencedTableName = :tabName "
						+ "and tv.ofTenant.id = :tenant " + "and tv.table = k.userTable");
		query.setParameter("tenant", tenant);
		query.setParameter("dbName", dbName);
		query.setParameter("tabName", tabName);
		return (List<TableVisibility>) query.getResultList();
	}

	// given a backing table and a tenant, tell me all the scopes in that tenant
	// of the tables it references.
	@SuppressWarnings("unchecked")
	public List<TableVisibility> findScopesForFKTargets(int tableID, int tenantID) {
		/*
		 * here is my raw query: select s.scope_id from user_key uk inner join
		 * scope s on s.scope_table_id = uk.user_table_id where
		 * uk.referenced_table = :tableID and s.scope_tenant_id = :tenantID
		 */
		Query query = em
				.get()
				.createQuery(
						"select tv from TableVisibility tv, Key uk where tv.table = uk.userTable and uk.referencedTable.id = :tableID and tv.ofTenant.id = :tenantID");
		query.setParameter("tableID", tableID);
		query.setParameter("tenantID", tenantID);
		return (List<TableVisibility>) query.getResultList();
	}

	public Key findForeignKey(UserDatabase db, Integer tenantID, String name, String constraintName, boolean except)
			throws PEException {
		if (StringUtils.isBlank(name) && StringUtils.isBlank(constraintName)) {
			return null;
		}
		StringBuffer qry = new StringBuffer("from Key k ");
		if (tenantID != null)
			qry.append(", TableVisibility tv ");
		qry.append("where k.constraint = 'FOREIGN' and k.userTable.userDatabase = :db ");
		if (!StringUtils.isBlank(name))
			qry.append("and k.name = :name");
		if (!StringUtils.isBlank(constraintName))
			qry.append("and k.constraintName = :constraintName");
		if (tenantID != null)
			qry.append(" and tv.ofTenant.id = :tenantID and tv.table = k.userTable ");
		Query query = em.get().createQuery(qry.toString());
		query.setParameter("db", db);
		if (!StringUtils.isBlank(name))
			query.setParameter("name", name);
		if (!StringUtils.isBlank(constraintName))
			query.setParameter("constraintName", constraintName);
		if (tenantID != null)
			query.setParameter("tenantID", tenantID);

		@SuppressWarnings("unchecked")
		List<Key> keys = query.getResultList();
		return onlyOne(keys, "Foreign Keys", name + "/" + constraintName, except);
	}

	public Key findKey(String dbName, String encTabName, String keyName) throws PEException {
		Query query = em
				.get()
				.createQuery(
						"from Key k where k.name = :keyName and k.userTable.name = :encTabName and k.userTable.userDatabase.name = :dbName");
		query.setParameter("dbName", dbName);
		query.setParameter("encTabName", encTabName);
		query.setParameter("keyName", keyName);
		@SuppressWarnings("unchecked")
		List<Key> keys = query.getResultList();
		return onlyOne(keys, "Specific key", dbName + "." + encTabName + "/" + keyName, false);
	}
	
	public UserTrigger findTrigger(String name, String dbName) throws PEException {
		Query query = em.get().createQuery("from UserTrigger ut where ut.name = :name and ut.table.userDatabase.name = :dbname");
		query.setParameter("name",name);
		query.setParameter("dbname",dbName);
		List<UserTrigger> trigs = query.getResultList();
		return onlyOne(trigs, "Triggers", dbName + "." + name,false);
	}
	
	public void deleteAllServerRegistration() {
		Query q = em.get().createQuery("delete from ServerRegistration");
		q.executeUpdate();
	}
	
	
	// for autoupdate
	public EntityManager getEntityManager() {
		return em.get();
	}	
	
	public static DataSource getCatalogDS() {
		@SuppressWarnings("unchecked")
		Set<DataSource> c3p0pools = C3P0Registry.allPooledDataSources(); 
		return c3p0pools.iterator().next();
	}

	
	public enum CatalogDAOFactory {
		
		INSTANCE;
		
		EntityManagerFactory emf = null;
		
		Map<Class<?>, CachedCatalogLookup<? extends CatalogEntity>> lookupCacheMap
			= new HashMap<Class<?>, CachedCatalogLookup<? extends CatalogEntity>>();
		
		public static CatalogDAO newInstance() {
			return new CatalogDAO(INSTANCE.emf.createEntityManager());
		}

		public static CatalogDAO newInstance(Properties p) throws PEException {
			return new CatalogDAO(INSTANCE.getEMF(p).createEntityManager(fixProperties(p)));
		}
		
		public static boolean isSetup() {
			return INSTANCE.emf != null ? true : false;
		}
		public static void setup(Properties p) throws PEException {
			INSTANCE.emf = Persistence.createEntityManagerFactory("DveCatalog", fixProperties(p));
		}
		
		private static Properties fixProperties(Properties p) throws PEException {
			final String url = p.getProperty(DBHelper.CONN_URL);
			
			// In production we don't have a default value for the catalog URL
			if(StringUtils.isEmpty(url)) 
				throw new PEException("Value for " + DBHelper.CONN_URL + " not specified in properties");

			final Properties props = new Properties(p);
			props.putAll(p);
			
			final PEUrl peUrl = CatalogURL.buildCatalogBaseUrlFrom(url);
			
			final String dbName = props.getProperty(DBHelper.CONN_DBNAME);
			props.remove(DBHelper.CONN_DBNAME);
			peUrl.setPath(dbName);
			props.setProperty(DBHelper.CONN_URL, peUrl.getURL());
			props.remove("hibernate.hbm2ddl.auto");

			return props;
		}
		
		public static void shutdown() {
			if (Boolean.getBoolean("CatalogDAO.printCacheStatistics")) {
				CacheManager cm = CacheManager.getInstance();
				for (String s : cm.getCacheNames()) {
					if (s.matches("com.tesora.dve.*")) {
						Statistics stats = cm.getCache(s).getStatistics();
						System.out.println("Cache " + s + ": " + stats.getObjectCount() +
								" items (" + stats.getCacheHits() + " hits, " + stats.getCacheMisses() + " misses)");
					}
				}
			}
			INSTANCE.lookupCacheMap.clear();
			if (INSTANCE.emf != null)
				INSTANCE.emf.close();
			INSTANCE.emf = null;
		}
		
		public static void clearCache() {
			CacheManager.getInstance().clearAll();
		}
		
		EntityManagerFactory getEMF(Properties p) throws PEException {
			if (emf == null)
				setup(p);
			return emf;
		}
		
		public CachedCatalogLookup<? extends CatalogEntity> 
				getLookupCache(Class<? extends CatalogEntity> cacheClass, String columnName) {
			CachedCatalogLookup<? extends CatalogEntity> lookupCache = null;
			// it's possible to have a race condition here between threads within the same jvm
			// in particular, between a containsKey call and a get call the cache could be cleared
			// leading to npes in callers - so instead if we don't find a cache, create a new one
			// and return that
			lookupCache = lookupCacheMap.get(cacheClass);
			if (lookupCache == null) {
				lookupCache = new CachedCatalogLookup<CatalogEntity>(cacheClass, columnName);
				if (cacheClass.equals(RangeTableRelationship.class))
					lookupCache = new RangeTableRelationshipCacheLookup(cacheClass);
				lookupCacheMap.put(cacheClass, lookupCache);				
			}
			return lookupCache;
		}
		
		public void clearLookupCache(Class<?> cacheClass) {
			lookupCacheMap.remove(cacheClass);
		}
	}
}