package com.vci.server.omd.linktype.service;

import java.io.IOException;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;

import com.vci.common.log.ServerWithLog4j;
import com.vci.common.utility.ObjectUtility;
import com.vci.server.base.persistence.dao.HibernateSessionFactory;
import com.vci.server.base.utility.OmdHelper;
import com.vci.server.cache.OMCacheProvider;
import com.vci.server.omd.common.LinkTypeHelper;
import com.vci.server.omd.common.OmdViewTool;
import com.vci.server.omd.ddlTool.DDLHelper;
import com.vci.corba.omd.atm.AttribItem;
import com.vci.corba.omd.ltm.LinkType;
import com.vci.corba.common.VCIError;



public class LTService {

	private static LTService instance;
	
	private LTService() {
		
	}
	
	public static LTService getInstance() {
		if (instance == null) {
			instance = new LTService();
		}
		return instance;
	}
	
	public boolean addLinkType(LinkType lt) throws Exception {
		String insertSql = "insert into pllinktype (OID, NAME, LABEL, DESCRIPTION, TS, CREATOR, CREATETIME, MODIFIER, MODIFYTIME, CONTENT) values(?,?,?,?,?,?,?,?,?,xmltype(?))";
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(insertSql);
		
		lt.oid = ObjectUtility.getNewObjectID36();
		long time = Calendar.getInstance().getTimeInMillis();
		lt.createTime = time;
		lt.modifyTime = time;
		lt.ts = time;
		Timestamp ts = new Timestamp(time);
		
		pst.setString(1, lt.oid);
		pst.setString(2, lt.name);
		pst.setString(3, lt.tag);
		pst.setString(4, lt.description);
		pst.setTimestamp(5, ts);
		pst.setString(6, lt.creator);
		pst.setTimestamp(7, ts);
		pst.setString(8, lt.modifier);
		pst.setTimestamp(9, ts);
		String xmlText = getXmlText(lt);
		//CLOB content = getXmlTypeContent(xmlText, connection);
		//pst.setObject(10, content);
		pst.setString(10, xmlText);
		int size = pst.executeUpdate();
		ServerWithLog4j.logger.debug(insertSql);
		pst.close();

		return size > 0;
	}

	
	public boolean modifyLinkType(LinkType lt) throws Exception {
		String sql = "update pllinktype t set t.name=?, t.label=?, t.description=?, t.ts=?, t.modifier=?, t.modifytime=?, t.content=xmltype(?) where t.oid=? and t.ts = ?";
		Connection conn = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = conn.prepareStatement(sql);
		
		long time = Calendar.getInstance().getTimeInMillis();
		Timestamp ts = new Timestamp(time);
		
		int index = 1;
		pst.setString(index++, lt.name);
		pst.setString(index++, lt.tag);
		pst.setString(index++, lt.description);
		pst.setTimestamp(index++, ts);
		pst.setString(index++, lt.modifier);
		pst.setTimestamp(index++, ts);
		String xmlText =  getXmlText(lt);
		//CLOB content =  getXmlTypeContent(xmlText, conn);
		//pst.setObject(9, content);
		pst.setString(index++, xmlText);
		pst.setString(index++, lt.oid);
		pst.setTimestamp(index++, new Timestamp(lt.ts));
		int size = pst.executeUpdate();
		ServerWithLog4j.logger.debug(sql);
		pst.close();
		
		lt.modifyTime = time;
		lt.ts = time;
		
		return size > 0;
	}

	
	public boolean deleteLinkType(LinkType lt) throws Exception {
		String sql = "delete from pllinktype where oid = ? and ts = ?";
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(sql);
		pst.setString(1, lt.oid);
		pst.setTimestamp(2, new Timestamp(lt.ts));
		pst.executeUpdate();
		ServerWithLog4j.logger.debug(sql);
		pst.close();
		
		
		String tableName = OmdHelper.getLTTableName(lt.name);
		sql = "drop table " + tableName;
		
		pst = connection.prepareStatement(sql);
		int size = pst.executeUpdate();
		ServerWithLog4j.logger.debug(sql);
		pst.close();

		return size > 0;
	}

	/**
	 * 清空链接类型
	 * @throws Exception 
	 */
	
	public boolean deleteLinkTypes(LinkType[] lts) throws Exception {
		for(LinkType lt : lts){
			deleteLinkType(lt);
		}
		return true;
	}
	
	
	public LinkType[] getLinkTypes() throws SQLException, IOException, DocumentException {
		//String sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from pllinktype t";
		String sql = "";
		switch (HibernateSessionFactory.getDbType()) {
			case DM8:
				sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, content from pllinktype t";
				break;
			case ORACL:
			default:
				sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from pllinktype t";
				break;
		}
		
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(sql);
		ResultSet rs = pst.executeQuery();
		ServerWithLog4j.logger.debug(sql);
		List<LinkType> lts = new ArrayList<LinkType>();
		while(rs.next()){
			LinkType lt = getLT(rs);
			lts.add(lt);
		}
		rs.close();
		pst.close();
		return lts.toArray(new LinkType[0]);
	}

	
	public String getLTData() throws VCIError {
		return "";
	}

	
	public String[] getLTNamesByAPName(String apName) throws VCIError, SQLException {
		String sql = "select t.name from pllinktype t where extract(content, '/linkType/attibute').getstringval() like ?";
		Connection conn = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = conn.prepareStatement(sql);
		pst.setString(1, "%<attibute>" + apName + "</attibute>%");
		ResultSet rs = pst.executeQuery();
		ServerWithLog4j.logger.debug(sql);
		Set<String> set = new HashSet<String>();
		while(rs.next()){
			set.add(rs.getString("name"));
		}
		rs.close();
		pst.close();
		return set.toArray(new String[0]);
	}

	/**
	 * 获取所有链接类型表
	 * @return
	 */
	public List<String> getAllLinkTables(){
		List<String> tableNameList = new ArrayList<String>();
		String sql = "select t.TABLE_NAME from user_tables t where t.TABLE_NAME like 'PLATFORMLT_%'";
		Session session = HibernateSessionFactory.getSession();
		List<?> list = session.createSQLQuery(sql).list();
		for(int i = 0; i < list.size(); i++){
			String tableName = (String) list.get(i);
			tableNameList.add(tableName.toLowerCase());
		}
		return tableNameList;
	}
	
	/**
	 * tableName and columns Map
	 * @return
	 */
	public Map<String, List<String>> getTableAndCols(){
		Map<String, List<String>> map = new HashMap<String, List<String>>();
		int sysAbLength = OmdHelper.getLTSysAbItems().length;
		String sql = "select t.TABLE_NAME, t.COLUMN_NAME from user_tab_columns t where t.TABLE_NAME like 'PLATFORMLT_%' and t.COLUMN_ID > " +
				sysAbLength + " order by t.TABLE_NAME";
		Session session = HibernateSessionFactory.getSession();
		List<?> list = session.createSQLQuery(sql).list();
		for(int i = 0; i < list.size(); i++){
			Object[] o = (Object[]) list.get(i);
			String tableName = ((String) o[0]).toLowerCase();
			String colName = ((String) o[1]).toLowerCase();
			
			//System.out.println("===LT AttrMap: " + tableName + " = " + colName);
			
			List<String> colList = map.get(tableName);
			if(colList == null){
				colList = new ArrayList<String>();
				colList.add(colName);
				map.put(tableName, colList);
			}else{
				colList.add(colName);
			}
		}
		return map;
	}
	
	public String[] linkTypeConsistencyCheck() throws Throwable {
		List<String> infoList = new ArrayList<String>();
		
//		AttribItem[] attrs = AttrPoolService.getInstance().getAttribItems(null, 0, 0);
//		Map<String, AttribItem> mapAttr = new HashMap<String, AttribItem>();
//		for (AttribItem attr : attrs) {
//			mapAttr.put(attr.name.toLowerCase(), attr);
//		}

		
		LinkType[] links = getLinkTypes();
		List<String> tableNameList = getAllLinkTables();
		Map<String, List<String>> map = getTableAndCols();
//		List<String> lstLackAttr = new ArrayList<String>();

		String info = "";
		for(int i = 0; i < links.length; i++){
			info = "";
			
			LinkType link = links[i];
			String tableName = OmdHelper.getLTTableName(link.name).toLowerCase();
			if(!tableNameList.contains(tableName)){
				info = link.name + "/DML_CREATE";
				infoList.add(info);
				continue;
			}
			String[] abNames = link.attributes;
			List<String> colList = map.get(tableName);
			List<String> addList = new ArrayList<String>();
			List<String> dropList = new ArrayList<String>();
			for(int k = 0; k < abNames.length; k++){
				String abName = abNames[k].toLowerCase();
//				if (!mapAttr.containsKey(abName)) {
//					lstLackAttr.add(abName);
//				}

				//System.out.println("===Check LT AttrMap: " + tableName + " = " + abName);
				
				if(colList == null || !colList.contains(abName)){
					//System.out.println("===Check LT Attr No Find;");
					addList.add(abName);
				}else{
					colList.remove(abName);
				}
			}
			if(colList != null){
				dropList = colList;
			}
			if(addList.size() > 0){
				info += link.name + "/DML_ADD(";
				for(int k = 0; k < addList.size(); k++){
					String abName = addList.get(k);
					info += abName + ",";
				}
				info = info.substring(0, info.lastIndexOf(",")) + ")";
			}
			
			if(dropList.size() > 0){
				if(info.equals("")){
					info += link.name + "/DML_DROP(";
				}else{
					info += ";_DROP(";
				}
				for(int k = 0; k < dropList.size(); k++){
					String abName = dropList.get(k);
					info += abName + ",";
				}
				info = info.substring(0, info.lastIndexOf(",")) + ")";
			}
			if(!info.equals("")){
				infoList.add(info);
			}
		}
		
		
//		info = "";
//		if (lstLackAttr.size() > 0) {
//			for (String attr : lstLackAttr) {
//				if (info.length() == 0)
//					info = "attribute/DML_LACK(" + attr;
//				else
//					info += "," + attr;
//			}
//			info += ")";
//			infoList.add(info);
//		}
		
		return infoList.toArray(new String[0]);
	}

	
	public String[] executeRepair(String[] sqlArray) throws Throwable {
		List<String> list = new ArrayList<String>();
		for(int i = 0; i < sqlArray.length; i++){
			String[] sqlInfo = sqlArray[i].split("/DML");
			String type = sqlInfo[0];
			String sql = sqlInfo[1];
			if(sql.contains(";")){
				String[] sqls = sql.split(";");
				String addSql = parseToSql(type, sqls[0]);
				String dropSql = parseToSql(type, sqls[1]);
				boolean addFlag = executeUpdate(addSql);
				boolean dropFlag = executeUpdate(dropSql);
				if(addFlag && dropFlag){
					list.add(type);
				}else if(addFlag){
					list.add(type + "_ADD");
				}else if(dropFlag){
					list.add(type + "_DROP");
				}
			}else{
				String sql_ = parseToSql(type, sql);
				boolean flag = executeUpdate(sql_);
				if(sql_.toLowerCase().contains("create table " 
							+ OmdHelper.getLTTableName(type).toLowerCase())){
					String consSql = LinkTypeHelper.getInstance().getAddPKSql(type);
					boolean flag_ = executeUpdate(consSql);
					if(flag && flag_){
						list.add(type);
					}
				}else{
					if(flag){
						list.add(type);
					}
				}
			}
		}
		return list.toArray(new String[0]);
	}

	public boolean executeUpdate(String sql) throws Throwable{
		Session session = HibernateSessionFactory.getSession();
		try{
			session.createSQLQuery(sql).executeUpdate();
			return true;
		}catch(Throwable e){
			throw e;
		}
	}
	
	/**
	 * 
	 * @param type
	 * @param sql:_ADD(cols)/_DROP(cols)
	 * @return
	 * @throws Throwable 
	 */
	public String parseToSql(String type, String sql){
		String operator = null;
		if(sql.contains("_ADD")){
			operator = "add";
		}else if(sql.contains("_DROP")){
			operator = "drop";
		}else if(sql.contains("_CREATE")){
			operator = "create";
		}else{
			return null;
		}
		
		if(operator.equals("create")){
			return getCreateLtSql(LinkTypeHelper.getInstance().getLinkTypeByName(type));
		}else{
			StringBuilder stb = new StringBuilder("alter table ");
			stb.append(OmdHelper.getLTTableName(type));
			stb.append(" ");
			stb.append(operator);
			stb.append("(");
			sql = sql.substring(sql.indexOf("(") + 1, sql.indexOf(")"));
			String[] abNames = sql.split(",");
			if(operator.equals("add")){
				for(int i = 0; i < abNames.length; i++){
					String abName = abNames[i];
					AttribItem abItem = OMCacheProvider.getAttribute(abName);
					String abSql_ = DDLHelper.getAbSql(abItem);
					stb.append(abSql_);
				}
			}else if(operator.equals("drop")){
				for(int i = 0; i < abNames.length; i++){
					String abName = abNames[i];
					stb.append(abName);
					stb.append(",");
				}
			}
			
			return stb.substring(0, stb.lastIndexOf(",")) + ")";
		}
	}
	
	public boolean createTable(String ltName) throws Throwable {
		LinkType lt = this.getLinkType(ltName);
		String createSql = getCreateLtSql(lt);
		String alterSql = getAddPKSql(ltName);//String.format("alter table %s add constraint PKLT_%s primary key (OID)\n", OmdTools.getLTTableName(lt.name), lt.name);

		Session session = HibernateSessionFactory.getSession();
		SQLQuery query;

		if (!createSql.equals("")) {
			query = session.createSQLQuery(createSql);
			int re = query.executeUpdate();
			if (re < 0) {
				return false;
			}
		}
		
		if (!alterSql.equals("")) {
			query = session.createSQLQuery(alterSql);
			int re = query.executeUpdate();
			if (re < 0) {
				return false;
			}
		}

		return true;
	}
	
	public boolean createView() {
		LinkType[] linkTypes = null;
		try {
			linkTypes = getLinkTypes();
		} catch (Throwable e) {
			e.printStackTrace();
		}
		Session session = HibernateSessionFactory.getSession();
		SQLQuery createSQLQuery ;
		for(LinkType lt : linkTypes){
			String[] btmItemsFrom = lt.btmItemsFrom;
			String sql = OmdViewTool.getBTSViewSql(btmItemsFrom, OmdViewTool.getFromViewName(lt.name));
			if(!sql.equals("")){
				//如果过长 ，进行分段处理，分解为 _1,_2_3,
				//假设至少，存在一个及以上的 union all才会超出64k
				if( sql.length() >= 64 * 1024)
				{
					String newSQL = sql.replaceAll(OmdViewTool.getFromViewName(lt.name), OmdViewTool.getFromViewName(lt.name) + "_1");
					String[] strs = newSQL.split("union all");
					String firstSql = strs[0];
					int nMiddle = strs.length /2;
					for(int i=1;i<nMiddle;i++)
					{
						firstSql += " union all " + strs[i];
					}
					createSQLQuery = session.createSQLQuery(firstSql);
					int re = createSQLQuery.executeUpdate();
					if(re < 0){
						return false;
					}
					
					String secondSql = "create or replace view " + OmdViewTool.getFromViewName(lt.name) + "_2 as ";
					boolean added = false;
					for( int i = nMiddle;i<strs.length;i++ )
					{
						secondSql +=  strs[i] + " union all ";
						added = true;
					}
					if (added)
					{
						secondSql += "#";
						secondSql = secondSql.replaceAll("union all #", "");
					}
					
					createSQLQuery = session.createSQLQuery(secondSql);
					re = createSQLQuery.executeUpdate();
					if(re < 0){
						return false;
					}
					
					StringBuilder sb = new StringBuilder();
					sb.append("create or replace view  ");
					sb.append(OmdViewTool.getFromViewName(lt.name) );
					sb.append(" as select * from ");
					sb.append( OmdViewTool.getFromViewName(lt.name) + "_1");
					sb.append( " union all ");
					sb.append( " select * from " + OmdViewTool.getFromViewName(lt.name) + "_2");
					
					createSQLQuery = session.createSQLQuery(sb.toString());
					re = createSQLQuery.executeUpdate();
					if(re < 0){
						return false;
					}
					
				}
				else
				{
					createSQLQuery = session.createSQLQuery(sql);
					int re = createSQLQuery.executeUpdate();
					if(re < 0){
						return false;
					}
				}
			}
			btmItemsFrom = lt.btmItemsTo;
			sql = OmdViewTool.getBTSViewSql(btmItemsFrom, OmdViewTool.getToViewName(lt.name));
			if(!sql.equals("")){
				//如果过长 ，进行分段处理，分解为 _1,_2_3,
				//假设至少，存在一个及以上的 union all才会超出64k
				if( sql.length() >= 64 * 1024)
				{
					String newSQL = sql.replaceAll(OmdViewTool.getToViewName(lt.name), OmdViewTool.getToViewName(lt.name) + "_1");
					String[] strs = newSQL.split("union all");
					String firstSql = strs[0];
					int nMiddle = strs.length /2;
					for(int i=1;i<nMiddle;i++)
					{
						firstSql += " union all " + strs[i];
					}
					createSQLQuery = session.createSQLQuery(firstSql);
					int re = createSQLQuery.executeUpdate();
					if(re < 0){
						return false;
					}
					
					
					String secondSql = "create or replace view " + OmdViewTool.getToViewName(lt.name) + "_2 as ";
					boolean added = false;
					for( int i = nMiddle;i<strs.length;i++ )
					{
						secondSql +=  strs[i] + " union all ";
						added = true;
					}
					if (added)
					{
						secondSql += "#";
						secondSql = secondSql.replaceAll("union all #", "");
					}
					
					createSQLQuery = session.createSQLQuery(secondSql);
					re = createSQLQuery.executeUpdate();
					if(re < 0){
						return false;
					}
					
					StringBuilder sb = new StringBuilder();
					sb.append("create or replace view  ");
					sb.append(OmdViewTool.getToViewName(lt.name) );
					sb.append(" as select * from ");
					sb.append( OmdViewTool.getToViewName(lt.name) + "_1");
					sb.append( " union all ");
					sb.append( " select * from " + OmdViewTool.getToViewName(lt.name) + "_2");
					
					createSQLQuery = session.createSQLQuery(sb.toString());
					re = createSQLQuery.executeUpdate();
					if(re < 0){
						return false;
					}
				}
				else
				{
					createSQLQuery = session.createSQLQuery(sql);
					int re = createSQLQuery.executeUpdate();
					if(re < 0){
						return false;
					}
				}
			}
		}
		return true;
	}
	
	/**
	 * 清空链接表， 链接类型
	 * @throws Exception 
	 */
	
	public boolean deleteLtsAndTables(LinkType[] lts) throws Exception {
		deleteLinkTypes(lts);
		Session session = HibernateSessionFactory.getSession();
		for(LinkType lt : lts){
			String tableName = OmdHelper.getLTTableName(lt.name);
			String sql = "drop table " + tableName;
			try{
				session.createSQLQuery(sql).executeUpdate();
			//处理HibernateException, 保证一条sql不成功时, 继续执行下一条.
			}catch(HibernateException e){
				e.printStackTrace();
			}
		}
		return true;
	}

	
	public LinkType getLinkType(String name) throws VCIError, SQLException, IOException, DocumentException {
		//String sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from pllinktype t where t.name =?";
		String sql = "";
		switch (HibernateSessionFactory.getDbType()) {
			case DM8:
				sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, content from pllinktype t where t.name =?";
				break;
			case ORACL:
			default:
				sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content from pllinktype t where t.name =?";
				break;
		}		
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(sql);
		pst.setString(1, name);
		ResultSet rs = pst.executeQuery();
		ServerWithLog4j.logger.debug(sql);
		LinkType lt = null;
		while(rs.next()){
			lt = getLT(rs);
		}
		rs.close();
		pst.close();
		if(lt == null){
			lt = new LinkType();
			lt.attributes = new String[0];
			lt.btmItemsFrom = new String[0];
			lt.btmItemsTo = new String[0];
		}
		return lt;
	}

	
	public boolean xml2DB(String userName) throws VCIError {
//		List<LinkType> news = Xml2DBDelegate.getInstance().getNews(userName);
//		if(news == null){
//			return true;
//		}
//		for(LinkType o : news){
//			try{
//				addLinkTypeNoCache(o);
//			}catch(Throwable e){
//				e.printStackTrace();
//				ServerWithLog4j.logger.warn(o.name + "迁移失败, 链接类型的迁移中止!");
//				return false;
//			}
//		}
		return true;
	}
	
	public boolean addLinkTypeNoCache(LinkType lt) throws VCIError, SQLException, IOException {
		boolean flag = false;
		String insertSql = "insert into pllinktype values(?,?,?,?,?,?,?,?,?,xmltype(?))";
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(insertSql);
		pst.setString(1, ObjectUtility.getNewObjectID36());
		pst.setString(2, lt.name);
		pst.setString(3, lt.tag);
		pst.setString(4, lt.description);
		long time = Calendar.getInstance().getTimeInMillis();
		Timestamp ts = new Timestamp(time);
		pst.setTimestamp(5, ts);
		pst.setString(6, lt.creator);
		pst.setTimestamp(7, ts);
		pst.setString(8, lt.modifier);
		pst.setTimestamp(9, ts);
		String xmlText = getXmlText(lt);
		//CLOB content =  getXmlTypeContent(xmlText, connection);
		//pst.setObject(10, content);
		pst.setString(10, xmlText);
		pst.executeUpdate();
		ServerWithLog4j.logger.debug(insertSql);
		pst.close();
		flag = true;
		return flag;
	}

	public boolean deleteLinkTypeNoCache(LinkType lt) throws VCIError, SQLException {
		boolean flag = false;
		String sql = "delete from pllinktype where oid = ? and ts = ?";
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(sql);
		pst.setString(1, lt.oid);
		pst.setTimestamp(2, new Timestamp(lt.ts));
		pst.executeUpdate();
		ServerWithLog4j.logger.debug(sql);
		pst.close();
		flag = true;
		return flag;
	}
	
	/**
	 * 将一条数据库中的记录转化成btmitem
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	public LinkType getLT(ResultSet rs) throws SQLException, IOException, DocumentException{
		LinkType lt = new LinkType();
		String value = rs.getString("oid");
		lt.oid = (value == null ? "" : value);
		value = rs.getString("name");
		lt.name = (value == null ? "" : value);
		value = rs.getString("label");
		lt.tag = (value == null ? "" : value);
		value = rs.getString("description");
		lt.description = (value == null ? "" : value);
		lt.ts = rs.getTimestamp("ts").getTime();
		value = rs.getString("creator");
		lt.creator = (value == null ? "" : value);
		lt.createTime = rs.getTimestamp("createTime").getTime();
		value = rs.getString("modifier");
		lt.modifier = (value == null ? "" : value);
		lt.modifyTime = rs.getTimestamp("modifyTime").getTime();
		//CLOB clob = (CLOB) rs.getClob("content");
		Clob clob = rs.getClob("content");
		Element ltDetails = getLtDetails(clob);
		LinkTypeHelper.getInstance().setLTValueFormDoc(lt, ltDetails);
		return lt;
	}
	
	/**
	 * 将查询的clob对象转换成xml的element, 便于解析
	 * @param clob
	 * @return
	 * @throws SQLException
	 * @throws IOException
	 * @throws DocumentException
	 */
	public Element getLtDetails(Clob clob) throws DocumentException, SQLException{
		SAXReader saxReader = new SAXReader();
		//Document document = saxReader.read(clob.characterStreamValue());
		Document document = saxReader.read(clob.getCharacterStream());
		Element root = document.getRootElement();
		return root;
	}
	
	/**
	 * 将LinkType lt转化成xmltext
	 * @param bt
	 * @return
	 */
	public String getXmlText(LinkType lt){
		StringBuilder stb = new StringBuilder("<linkType>");
		stb.append("<name>" + lt.name + "</name>");
		stb.append("<tag>" + lt.tag + "</tag>");
		stb.append("<description>" + lt.description + "</description>");
		for(String btmFrom : lt.btmItemsFrom){
			stb.append("<btmFrom>" + btmFrom + "</btmFrom>");
		}
		for(String btmTo : lt.btmItemsTo){
			stb.append("<btmTo>" + btmTo + "</btmTo>");
		}
		stb.append("<primitivesFrom>" + lt.primitivesFrom + "</primitivesFrom>");
		stb.append("<primitivesTo>" + lt.primitivesTo + "</primitivesTo>");
		stb.append("<relationFrom>" + lt.relationFrom + "</relationFrom>");
		stb.append("<relationTo>" + lt.relationTo + "</relationTo>");
		stb.append("<relation>" + lt.relation + "</relation>");
		stb.append("<implClass>" + lt.implClass + "</implClass>");
		stb.append("<shape>" + lt.shape + "</shape>");
		for(String attibute : lt.attributes){
			stb.append("<attibute>" + attibute + "</attibute>");
		}
		stb.append("</linkType>");
		return stb.toString();
	}
	
	/**
	 * 将xmlText内容写入到临时的CLOB对象中
	 * @param xmlText
	 * @param connection
	 * @return
	 * @throws SQLException
	 * @throws IOException
	 */
//	public CLOB getXmlTypeContent(String xmlText, Connection connection) throws SQLException, IOException{
//		C3P0NativeJdbcExtractor c3p0NativeJdbcExtractor = new C3P0NativeJdbcExtractor();
//		Connection nativeConnection = c3p0NativeJdbcExtractor.getNativeConnection(connection);
//		CLOB clob = CLOB.createTemporary(nativeConnection, false, CLOB.DURATION_SESSION);
//		clob.open(CLOB.MODE_READWRITE);
//		Writer clobWriter = clob.setCharacterStream(1000);
//		clobWriter.write(xmlText);
//		clobWriter.flush();
//		clobWriter.close();
//		clob.close();
//		return clob;
//	}
//	
	public LinkType getLinkTypeByOid(String oid) throws VCIError, SQLException, IOException, DocumentException {
//		String sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content " +
//				"from pllinktype t where t.oid =?";
		
		String sql = "";
		switch (HibernateSessionFactory.getDbType()) {
			case DM8:
				sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, content from pllinktype t where t.oid =?";
				break;
			case ORACL:
			default:
				sql = "select oid, name, label, description, ts, creator, createtime, modifier, modifytime, t.content.getclobval() content " +
						"from pllinktype t where t.oid =?";
				break;
		}
		
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = connection.prepareStatement(sql);
		pst.setString(1, oid);
		ResultSet rs = pst.executeQuery();
		LinkType lt = null;
		while(rs.next()){
			lt = getLT(rs);
		}
		rs.close();
		pst.close();
		if(lt == null){
			lt = new LinkType();
		}
		return lt;
	}
	
	
	/**
	 * 返回创建链接类型的sql
	 * @param linkType
	 * @return
	 */
	private String getCreateLtSql(LinkType linkType){
		String tableName = OmdHelper.getLTTableName(linkType.name);
		String sql = "create table " + tableName + "(" + DDLHelper.getLTSysFields();
		
		String[] abInfo = linkType.attributes;
		
		for(int i = 0; i < abInfo.length; i++){
			AttribItem abItem = null;
			abItem = OMCacheProvider.getAttribute(abInfo[i]);

			String abSql = DDLHelper.getAbSql(abItem);
			sql += abSql;
		}
		sql = sql.substring(0, sql.lastIndexOf(","));
		sql += "\n)";
		return sql;
	}
	
	/**
	 * 获取增加主键sql
	 * @param typeName
	 * @return
	 */
	private String getAddPKSql(String typeName){
		return "alter table " + OmdHelper.getLTTableName(typeName) + " add constraint PKLT_" + typeName + " primary key (OID)\n";
	}

	public boolean modifyLinkTypeTable(LinkType lt) throws Throwable {
		
		LinkType oldLT = OMCacheProvider.getLinkType(lt.name);
		
		//修改链接类型TABLE
		List<String> addedAbList = new ArrayList<String>();
		List<String> removeAbList = new ArrayList<String>();
		
		getAddedApList(oldLT, lt, addedAbList, removeAbList);
		
		String tableName = OmdHelper.getLTTableName(oldLT.name);
		
		if (addedAbList != null && addedAbList.size() != 0){
			//更新表btName 增加属性
			String sql = "alter table " + tableName + " add(";
			for(int i = 0; i < addedAbList.size(); i++){
				String abName = addedAbList.get(i);
				AttribItem abItem = OMCacheProvider.getAttribute(abName);
				sql += DDLHelper.getAbSql(abItem);
			}
			sql = sql.substring(0, sql.lastIndexOf(","));
			sql += ")";
			
			boolean flag = DDLHelper.executeSql(sql);
			if(!flag){
				return false;
			}
		}
		
		
		if(removeAbList.size() > 0 && !DDLHelper.hasInstance(tableName)){
			String dropSql = "alter table " + tableName + " drop(";
			for(int i = 0; i < removeAbList.size(); i++){
				dropSql += removeAbList.get(i);
				if(i < removeAbList.size() -1){
					dropSql += ",";
				}
			}
			dropSql += ")";
			boolean dropFlag = DDLHelper.executeSql(dropSql);
			if(!dropFlag){
				return false;
			}
		}
		
		return true;
		
	}
	
	
	/**
	 * 获取修改链接类型时 增加的属性, 以便将这些属性增加到该业务类型表的属性列中
	 * @param oldNames
	 * @param names
	 * @return
	 */
	private void getAddedApList(LinkType oldLt, LinkType newLt, List<String> lstAdd, List<String> lstRemove){
		List<String> lstOldAttr = Arrays.asList(oldLt.attributes);
		List<String> lstNewAttr = Arrays.asList(newLt.attributes);
		
		for(Iterator<String> iterator = lstNewAttr.iterator(); iterator.hasNext();){
			String newName = iterator.next();
			if(!lstOldAttr.contains(newName)){
				lstAdd.add(newName);
			}
		}

		for(Iterator<String> iterator = lstOldAttr.iterator(); iterator.hasNext();){
			String oldName = iterator.next();
			if(!lstNewAttr.contains(oldName)){
				lstRemove.add(oldName);
			}
		}
	}

}