io.baltoro.service.QueryBuilder Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of client-lib Show documentation
baltoto client lib
There is a newer version: 4.0.12
package io.baltoro.service;

import java.sql.SQLException;
import java.util.Set;
import java.util.logging.Logger;

import io.baltoro.domain.BOMD;
import io.baltoro.domain.User;
import io.baltoro.exception.ServiceException;



public class QueryBuilder 
{
	
	protected static final Logger log = Logger.getLogger(QueryBuilder.class.getName());
	
	
	
	private static final String OBJECT_BASE_SELECT_BY_UUID_QUERY =
			"select * from object_base where base_uuid = ?";
	
	
	static final String DELETE_OBJECT_BASE_BY_UUID_QUERY =
			"delete from object_base where base_uuid = ?";
	
	
	
	private static final String OBJECT_SELECT_QUERY =
			"select ob.*, md.* from object_base ob, object_metadata md "
			+" where ob.latest_version_uuid =  md.version_uuid "
			+" and ob.base_uuid = ?";
	
	private static final String OBJECT_SELECT_PERMISSION_QUERY =
			"select ob.*, md.*, "
			+"(select   concat(max(read_flag), max(create_flag),max(update_flag), "
			+"	max(delete_flag),max(relation_flag),max(share_flag),max(grant_flag)) "
			+"	from object_permission opp "
			+"	where opp.sys_base_uuid in (%s) "
			+"	and opp.base_uuid = ob.base_uuid) op "
			+" from object_base ob, object_metadata md "
			//+" from object_base ob LEFT JOIN object_metadata md ON ob.latest_version_uuid =  md.version_uuid "
			+" where ob.latest_version_uuid =  md.version_uuid "
			+" and %s ";
	
	
	
	private static final String OBJECT_BASE_BY_NAME_TYPE_SELECT_QUERY =
			"select * from object_base where name=? and container_uuid=? and object_type=?";
	
	static final String OBJECT_BY_STATE_TYPE_SELECT =
			"select *,'1000000' op from object_base ob, object_metadata md where state=? and object_type=? and container_uuid=? "
			+ "and ob.base_uuid = md.base_uuid";
	
	static final String COUNT_BY_STATE_TYPE =
			"select count(*) from object_base where state=? and object_type=? and container_uuid=?  ";
		
	static final String OBJECT_BASE_INSERT_QUERY =
			"insert into object_base(base_uuid,name,state,object_type,container_uuid,latest_version_uuid,permission_type,created_by)"
			+ "values(?,?,?,?,?,?,?,?)";
	
	private static final String OBJECT_BASE_UPDATE_QUERY =
			"update object_base set name=?,state=?,latest_version_uuid=?,permission_type=?,created_by=? "
			+ "where base_uuid=?";
	
	static final String OBJECT_BASE_STATE_UPDATE_QUERY =
			"update object_base set state=? where base_uuid=?";
	
	
	static final String METADATA_DELETE_QUERY =
			"delete * from object_metadata where base_uuid=?";
	
	
	private static final String OBJECT_DEF_INSERT_QUERY =
			"insert into object_def(object_type,metadata_name"
			+ ",column_name,data_type,default_value,enum_values,is_required,is_encrypted,is_searchable,created_by) "
			+"values(?,?,?,?,?,?,?,?,?,?)";
	
	private static final String OBJECT_DEF_SELECT_QUERY =
			"select * from object_def where def_version_uuid = ? order by metadata_name";
	
	
	
	private static final String OBJECT_DEF_COPY_QUERY =
			"insert into object_def(def_version_uuid,metadata_name, def_base_uuid"
					+ ",column_name,data_type,default_value,enum_values,is_required,is_encrypted,is_searchable,created_by) "
					+"(select ?,metadata_name,def_base_uuid,column_name,data_type,default_value,enum_values,is_required,is_encrypted,is_searchable,?"
					+" from object_def where def_version_uuid = ?)";
	
	
	//private static final String PERMISSION_INSERT_QUERY =
		//	"insert into object_permission (base_uuid,permission,sys_base_uuid,created_by)"
			//+ " values(?,?,?,?)";
	
	private static final String PERMISSION_INSERT_QUERY =
			"insert into object_permission (base_uuid,sys_base_uuid,read_flag,create_flag,"
			+ "update_flag,delete_flag,relation_flag,share_flag,grant_flag,created_by)"
			+ " values(?,?,?,?,?,?,?,?,?,?)";
	
		
	static final String PERMISSION_DELETE_QUERY =
			"delete from object_permission where base_uuid = ?";
	
	private static final String PERMISSION_SELECT_QUERY =
			"select base_uuid, concat(max(read_flag), max(create_flag),max(update_flag),"
			+"max(delete_flag),max(relation_flag),max(share_flag),max(grant_flag)) op  "
			+" from object_permission where base_uuid in (%s) and sys_base_uuid in (%s)";
	
	
	static final String RELATIONSHIP_INSERT_QUERY =
			"insert into object_relationship (p_base_uuid,c_base_uuid,sort_order,created_by)"
			+ " values(?,?,?,?)";
	
	
	private static final String RELATIONSHIP_SELECT_ALL_P_QUERY =
			"select * from object_relationship where p_base_uuid in (%s) order by p_base_uuid,sort_order";
	
	static final String RELATIONSHIP_SELECT_TYPE_P_QUERY =
			"select ol.* from object_relationship ol, object_base ob "
			+ " where ob.base_uuid = ol.c_base_uuid "
			+ " and ol.p_base_uuid = ? "
			+ " and ob.object_type = ? "
			+" order by ol.created_on ";
	
	private static final String RELATIONSHIP_SELECT_TYPE_C_QUERY =
			"select ol.* from object_relationship ol, object_base ob "
			+ " where ob.base_uuid = ol.p_base_uuid "
			+ " and ol.c_base_uuid = ? "
			+ " and ob.object_type = ? ";
			
	
	static final String RELATIONSHIP_SELECT_C_QUERY =
			"select * from object_relationship where c_base_uuid in (?) order by sort_order";
	
	
	private static final String RELATIONSHIP_DELETE_PNC_QUERY =
			"delete from object_relationship where p_base_uuid = ? and c_base_uuid = ? ";
	
	static final String RELATIONSHIP_DELETE_P_QUERY =
			"delete from object_relationship where p_base_uuid = ?";
	
	static final String RELATIONSHIP_DELETE_C_QUERY =
			"delete from object_relationship where c_base_uuid = ?";
	
	static final String LOCK_FIND_BY_UUID =	"select * from object_lock where uuid=?";
	
	static final String LOCK_FIND_BY_CONTAINER_TYPE =	"select * from object_lock where container_uuid=? and type=?";
	
	static final String LOCK_INSERT =	"insert into object_lock(uuid,container_uuid, type,state, created_by) values (?,?,?,?,?)";
	
	static final String LOCK_DELETE =	"delete from object_lock where uuid=?";
	
	
	static final String BLOCK_CHAIN_INSERT =	"insert into block_chain(uuid,container_uuid, block_uuid, sort_order, created_by) values (?,?,?,?,?)";
	
	static final String BLOCK_CHAIN_MAX_NUMBER =	"select max(sort_order) from block_chain where container_uuid = ?";
	
	static final String BLOCK_CHAIN_LAST_BLOCK =	"select block_uuid from block_chain where container_uuid = ? order by created_on desc limit 1";
	
	static final String BLOCK_CHAIN_AFTER_UUID =	"select block_uuid from block_chain where created_on > (select created_on from block_chain where block_uuid=?) order by created_on limit 100";
	
	static final String BLOCK_CHAIN_FROM_START =	"select block_uuid from block_chain order by created_on limit 100";
	
	
	
	String getBOQuery()
	{
		StringBuilder query = new StringBuilder(500);
		query.append("select *.b, ");
		
		return null;
	}
	
	/*
	String insertObjectBase()
	{
		return OBJECT_BASE_INSERT_QUERY;
	}
	*/
	
	String updateObjectBase()
	{
		return OBJECT_BASE_UPDATE_QUERY;
	}

	String insertObjectDef()
	{
		return OBJECT_DEF_INSERT_QUERY;
	}
	
	String deleteMetadata()
	{
		return METADATA_DELETE_QUERY;
	}
	
	String updateMetadata(BOMD[] bomds)
	{
		StringBuffer str = new StringBuffer();
		str.append("update object_metadata set ");
		
		for (BOMD bomd : bomds)
		{
			str.append(bomd.getColType()+"=?,");
		}
		
		str.deleteCharAt(str.length()-1);
		
		str.append(" where base_uuid=?");
		
		return str.toString();
	}
	
	String copyObjectDef()
	{
		return OBJECT_DEF_COPY_QUERY;
	}
	
	String selectObject()
	{
		return OBJECT_SELECT_QUERY;
	}
	
	String selectObjectWithPermission()
	{
		String inClause = ConnUtil.toInClause(Ctx.getContainers()); 
		String query = String.format(OBJECT_SELECT_PERMISSION_QUERY, inClause, "  ob.base_uuid = ? ");
		
		return query;
	}
	
	String selectObjectsWithPermission(String in)
	{
		String inClause = ConnUtil.toInClause(Ctx.getContainers()); 
		String query = String.format(OBJECT_SELECT_PERMISSION_QUERY, inClause, "  ob.base_uuid in ("+in+") ");
		
		return query;
	}
	
	
	String selectObjectWithPermissionByNameTypeContainer()
	{
		User user = Ctx.getUser();
		String inClause = ConnUtil.toInClause(Ctx.getContainers()); 
		String query = String.format(OBJECT_SELECT_PERMISSION_QUERY, inClause, "  ob.name = ? and ob.object_type = ? and ob.container_uuid = ? ");
		
		return query;
	}
	
	String selectObjectWithPermissionByNameType()
	{
		User user = Ctx.getUser();
		String inClause = ConnUtil.toInClause(Ctx.getContainers()); 
		String query = String.format(OBJECT_SELECT_PERMISSION_QUERY, inClause, "  ob.name = ? and ob.object_type = ? ");
		
		return query;
	}
	
	
	
	String selectObjectWithPermissionByMetadata(String colName)
	{
		String inClause = ConnUtil.toInClause(Ctx.getContainers()); 
		String query = String.format(OBJECT_SELECT_PERMISSION_QUERY, inClause, "  md."+colName+" = ? ");
		
		return query;
	}
	
	String selectObjectBaseByNameType()
	{
		return OBJECT_BASE_BY_NAME_TYPE_SELECT_QUERY;
	}
	
	String selectObjectBaseByUUD()
	{
		return OBJECT_BASE_SELECT_BY_UUID_QUERY;
	}
	
	String insertPermission()
	{
		return PERMISSION_INSERT_QUERY;
	}
	
	String updatePermission()
	{
		return PERMISSION_INSERT_QUERY;
	}
	
	String deletePermission()
	{
		return PERMISSION_DELETE_QUERY;
	}
	
	String selectPermission(Set baseUuids)
	{
		String buuids = ConnUtil.toInClause(baseUuids);
		String sysUuids = ConnUtil.toInClause(Ctx.getContainers());
		
		String query = String.format(PERMISSION_SELECT_QUERY, buuids, sysUuids);
		
		return query;
	
	}
	
	String getBOMetadataInsertQuery(BOMD[] mds)
	throws ServiceException
	{
		
		StringBuilder qstr1 = new StringBuilder(300);
		StringBuilder qstr2 = new StringBuilder(300);
		
		qstr1.append("insert into object_metadata(version_uuid,base_uuid,version_number,created_by,content_uri,");
		qstr2.append("values(?,?,?,?,?,");
			
		for (BOMD md : mds)
		{
			qstr1.append(md.getColType()+",");
			qstr2.append("?,");
		}
		qstr1.deleteCharAt(qstr1.length()-1);
		qstr2.deleteCharAt(qstr2.length()-1);
		
			
		String qstr = qstr1.toString()+")\r\n"+qstr2.toString()+")";
		return qstr;
	}
	
	String getObjectDefSelectQuery()
	{
		return OBJECT_DEF_SELECT_QUERY;
	}
	
	void copyMetaDataMap(String fromContainerUuid, String fromTyprVersionUuid, String toContainerUuid, String toTyprVersionUuid)
	throws ServiceException, SQLException
	{
		
	}
	
	
	String insertRelationship()
	{
		return RELATIONSHIP_INSERT_QUERY;
	}
	
	String deleteRelationshipByPNC()
	{
		return RELATIONSHIP_DELETE_PNC_QUERY;
	}
	
	String deleteRelationshipByP()
	{
		return RELATIONSHIP_DELETE_P_QUERY;
	}
	
	String selectAllRelationshipByP(String inClause)
	{
		String query = String.format(RELATIONSHIP_SELECT_ALL_P_QUERY, inClause);
		return query;
	}
	
	
	
	String selectTypeRelationshipByC()
	{
		return RELATIONSHIP_SELECT_TYPE_C_QUERY;
	}
  	
	
	
}