wp/nwyj/.svn/pristine/85/85cc6652756aef1f1dbcda08fd3a3a1715bfe5a3.svn-base

package com.persistence.DBdll.adapter;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Vector;

import org.apache.log4j.Logger;

import com.persistence.DbConnection;
import com.persistence.DBdll.adapter.assitant.ColumnInfo;
import com.persistence.DBdll.adapter.assitant.ColumnInfoService;
import com.persistence.DBdll.adapter.assitant.TableInfo;
import com.persistence.DBdll.adapter.assitant.index.IndexInfo;
import com.persistence.service.PersistenceFactory;
import com.persistence.service.SysPersistence;
import com.persistence.service.exception.PersistenceException;
import com.sysmodel.datamodel.Parameter.DataBaseType;
import com.sysmodel.datamodel.Parameter.SysParameter;
import com.sysmodel.datamodel.Parameter.XmlManagerPara;
import com.sysmodel.datamodel.xmlmodel.able.MdpAttribute;
import com.sysmodel.datamodel.xmlmodel.able.MdpClass;
import com.sysmodel.datamodel.xmlmodel.able.MdpConstant;
import com.sysmodel.datamodel.xmlmodel.able.MdpDataSource;
import com.sysmodel.datamodel.xmlmodel.able.Property;
import com.sysmodel.datamodel.xmlmodel.able.Template;
import com.sysmodel.datamodel.xmlmodel.impl.MdpAttributeImpl;
import com.sysmodel.datamodel.xmlmodel.impl.MdpClassImpl;
import com.sysmodel.datamodel.xmlmodel.impl.MdpConstantImpl;
import com.sysmodel.datamodel.xmlmodel.impl.MdpDataSourceImpl;
import com.sysmodel.datamodel.xmlmodel.impl.PropertyImpl;
import com.sysmodel.datamodel.xmlmodel.impl.SysModelImpl;

public class DB2Adapter extends DatabaseAdapter{

	private final static Logger	log					= Logger.getLogger(DB2Adapter.class);
	ColumnInfoService			columnInfoService	= new ColumnInfoService();

	private SysModelImpl		sysmodel			= SysModelImpl.getInstance();

	public boolean createTable(MdpClassImpl table) {
		String sql = generalSQL(table);
		log.info(sql);
		MdpDataSource MdpDataSource = sysmodel.getDataSourceByCode(table.getDataSource());
		return executeDDL(MdpDataSource, sql);
	}

	public ArrayList<Vector<Object>> updateTable(MdpClassImpl table) {

		MdpDataSource MdpDataSource = sysmodel.getDataSourceByCode(table.getDataSource());
		ArrayList<Vector<Object>> list = new ArrayList<Vector<Object>>();
		try {
			Vector<ColumnInfo> columninfos = getTableColumnInfo(table.getName(), MdpDataSource);
			if (columninfos.size() == 0) {
				return list;
			}

			Vector<Object> dllVec = new Vector<Object>();
			Vector<MdpAttributeImpl> add = columnInfoService.addDataBaseColumn(
					table.getAllMdpAttributes(), columninfos);
			for (int i = 0; i < add.size(); i++) {
				MdpAttributeImpl imp = add.elementAt(i);
				String sql = getTableModifyDllSQL(XmlManagerPara.add, imp, table.getName());
				boolean bresult = executeDDL(MdpDataSource, sql);
				dllVec = new Vector<Object>();
				dllVec.add("add");
				dllVec.add(imp);
				dllVec.add(bresult);
				dllVec.add(sql);
				list.add(dllVec);
			}

			Vector<ColumnInfo> delete = columnInfoService.deleteDataBaseColumn(
					table.getAllMdpAttributes(), columninfos);
			for (int i = 0; i < delete.size(); i++) {

				ColumnInfo imp = delete.elementAt(i);
				String sql = "alter table " + table.getName() + " drop column "
						+ imp.getColumnName();
				boolean bresult = executeDDL(MdpDataSource, sql);
				dllVec = new Vector<Object>();
				dllVec.add("delete");
				dllVec.add(imp);
				dllVec.add(bresult);
				dllVec.add(sql);
				list.add(dllVec);

			}

			Vector<MdpAttributeImpl> update = columnInfoService.updateDataBaseColumn(
					table.getAllMdpAttributes(), columninfos);
			for (int i = 0; i < update.size(); i++) {

				MdpAttributeImpl imp = update.elementAt(i);
				String sql = getTableModifyDllSQL(XmlManagerPara.update, imp, table.getName());
				boolean bresult = executeDDL(MdpDataSource, sql);
				dllVec = new Vector<Object>();
				dllVec.add("update");
				dllVec.add(imp);
				dllVec.add(bresult);
				dllVec.add(sql);
				list.add(dllVec);

			}

			ColumnInfo info = columnInfoService
					.getColumnInfoByFieldName(columninfos, "fd_objectid");
			if (info == null) {
				String sql = "alter table " + table.getName() + " add fd_objectid CHAR("
						+ XmlManagerPara.fd_objectidlength + ")";
				boolean bresult = executeDDL(MdpDataSource, sql);
				dllVec = new Vector<Object>();
				dllVec.add("addfd_objectid");
				dllVec.add("fd_objectid");
				dllVec.add(bresult);
				dllVec.add(sql);
				list.add(dllVec);
			} else {
				if (!info.getColumnTypeName().equalsIgnoreCase("VARCHAR")
						|| info.getPrecision() != XmlManagerPara.fd_objectidlength) {
					String sql = "alter table " + table.getName() + " modify fd_objectid CHAR("
							+ XmlManagerPara.fd_objectidlength + ")";
					boolean bresult = executeDDL(MdpDataSource, sql);
					dllVec = new Vector<Object>();
					dllVec.add("updatefd_objectid");
					dllVec.add("fd_objectid");
					dllVec.add(bresult);
					dllVec.add(sql);
					list.add(dllVec);
				}
			}
		} catch (Exception e) {
			log.error(e.toString());
		}
		return list;
	}

	public boolean renameTableName(MdpClassImpl table, String newtablename) {
		MdpDataSource MdpDataSource = sysmodel.getDataSourceByCode(table.getDataSource());
		return renameTableName(MdpDataSource, newtablename, table.getName());
	}

	public boolean updateTableAttribute(String action, MdpAttributeImpl attribute,
			MdpClassImpl table) {
		MdpDataSource MdpDataSource = sysmodel.getDataSourceByCode(table.getDataSource());
		String sql = getTableModifyDllSQL(action, attribute, table.getName());
		return executeDDL(MdpDataSource, sql);
	}

	/**
	 * 获得数据库中指定表中列的信息
	 * 
	 * @param tablename
	 * @param con
	 * @return
	 */
	public Vector<ColumnInfo> getTableColumnInfo(String tableName, MdpDataSource datasource) {
		Vector<ColumnInfo> vect = new Vector<ColumnInfo>();
		Connection connection = getConnection(datasource);
		if (connection == null)
			return vect;
		Statement st = null;
		ResultSet rs = null;
		DatabaseTableInfo TableInfo = new DatabaseTableInfo();
		try {
			HashMap<String, String> MapComments = TableInfo.getColComments(connection, tableName,
					DataBaseType.DB2);
			st = connection.createStatement();
			rs = st.executeQuery("select * from " + tableName + " where 1=0");
			ResultSetMetaData rm = rs.getMetaData();
			int colCount = rm.getColumnCount();// 取得列数
			ColumnInfo vo = null;
			for (int i = 1; i <= colCount; i++) {
				vo = new ColumnInfo();
				String ColumnName = rm.getColumnName(i);
				vo.setDataBaseType(DataBaseType.DB2);

				vo.setTableName(tableName);
				vo.setColumnName(rm.getColumnName(i));
				vo.setColumnType(rm.getColumnType(i));
				vo.setColumnTypeName(rm.getColumnTypeName(i));
				vo.setPrecision(rm.getPrecision(i));
				vo.setScale(rm.getScale(i));
				vo.setDescritpion(MapComments.get(ColumnName));

				vo.setAutoIncrement(rm.isAutoIncrement(i));
				vo.setIsNullable(rm.isNullable(i));
				vo.setReadOnly(rm.isReadOnly(i));
				vo.setSigned(rm.isSigned(i));
				vo.setSearchable(rm.isSearchable(i));
				vo.setColumnDisplaySize(rm.getColumnDisplaySize(i));
				vect.add(vo);
			}

		} catch (Exception e) {
			log.error("DB2Exception-- ", e);
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (st != null)
					st.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				log.error("DB2 SQLException-- ", e);
			}
		}
		return vect;
	}

	private String getCanstantModifyDllSQL(String action, Property imp, String tablename) {

		String sql = "alter table " + tablename;
		if (action.equals(XmlManagerPara.add)) {
			sql = sql + " add ";
		} else if (action.equals(XmlManagerPara.update)) {
			sql = sql + " modify ";
		} else if (action.equals(XmlManagerPara.delete)) {
			sql = sql + " drop column " + imp.getName();
			return sql;
		}

		String fieldname = imp.getName();
		String datatype = imp.getDataType();
		if (datatype.equals(XmlManagerPara.date)) {

			// 如果只使用MM/DD/YYY,那么使用DATE类型。
			// l 如果只使用HH:MM:SS, 那么使用TIME类型。
			// l 如果要使用日期和时间，则使用时间戳类型（TIMESTAMP）
			// l 可以使用Oracle中的TO_CHAR()函数来取DATE的字串来分别与DB2/400的DATE、TIME相匹配。

			if (imp.getScale().equals(SysParameter.date_format)) {
				sql = sql + fieldname + "  DATE";
			} else if (imp.getScale().equals(SysParameter.time_format)) {
				sql = sql + fieldname + "  TIME";
			} else {
				sql = sql + fieldname + "  TIMESTAMP";
			}
		} else if (datatype.equals(XmlManagerPara.bool)) {
			sql = sql + fieldname + " SAMLLINT";
			sql = sql + " default null";
		}

		else if (datatype.equals(XmlManagerPara.number)) {
			int length = imp.getLength() == 0 ? 10 : imp.getLength();
			String scale = imp.getScale().equals("") ? "" : "," + imp.getScale();
			sql = sql + fieldname + " DECIMAL(" + length + scale + ")";
			sql = sql + " default null";
		} else if (datatype.equals(XmlManagerPara.string)) {
			sql = sql + fieldname;
			if (imp.getLength() <= 2000 && imp.getLength() >= 50) {
				sql = sql + " VARCHAR(" + imp.getLength() + ")";
			} else {
				sql = sql + " VARCHAR(" + imp.getLength() + ")";
			}
			sql = sql + " default null";
		}
		log.info("sql = " + sql);
		return sql;
	}

	private String getTableModifyDllSQL(String action, MdpAttributeImpl attribute, String tablename) {

		String sql = "alter table " + tablename;
		if (action.equals(XmlManagerPara.add)) {
			sql = sql + " add ";
		} else if (action.equals(XmlManagerPara.update)) {
			sql = sql + " modify ";
		} else if (action.equals(XmlManagerPara.delete)) {
			sql = sql + " drop column " + attribute.getName();
			return sql;
		}

		String fieldname = attribute.getName();
		String datatype = attribute.getDataType();
		if (datatype.equals(XmlManagerPara.date)) {

			if (attribute.getScale().equals(SysParameter.date_format)) {
				sql = sql + fieldname + "  DATE";
			} else if (attribute.getScale().equals(SysParameter.time_format)) {
				sql = sql + fieldname + "  TIME";
			} else {
				sql = sql + fieldname + "  TIMESTAMP";
			}

			if (attribute.getDefaultValue() != null
					&& attribute.getDefaultValue().equals(XmlManagerPara.sysdate)) {
				if (attribute.getScale().equals(SysParameter.date_format)) {
					sql = sql + " DEFAULT CURRENT DATE";
				} else if (attribute.getScale().equals(SysParameter.time_format)) {
					sql = sql + " DEFAULT CURRENT TIME";
				} else {
					sql = sql + " DEFAULT CURRENT TIMESTAMP";
				}
			}

		} else if (datatype.equals("blob")) {
			sql = sql + fieldname + " BLOB(2)";
		}

		else if (datatype.equals("clob")) {
			sql = sql + fieldname + " CLOB(2) ";
		}

		else if (datatype.equals(XmlManagerPara.bool)) {
			sql = sql + fieldname + " SAMLLINT";
			if (attribute.getDefaultValue() != null && !attribute.getDefaultValue().equals("")) {
				sql = sql + " default " + attribute.getDefaultValue();
			} else {
				sql = sql + " default null";
			}
		}

		else if (datatype.equals(XmlManagerPara.number)) {
			int length = attribute.getPrecision() == 0 ? 10 : attribute.getPrecision();

			String scale = attribute.getScale().equals("") ? "" : "," + attribute.getScale();
			sql = sql + fieldname + " DECIMAL(" + length + scale + ")";

			if (attribute.getDefaultValue() != null && !attribute.getDefaultValue().equals("")) {
				sql = sql + " default " + attribute.getDefaultValue();
			} else {
				sql = sql + " default null";
			}
		} else if (datatype.equals(XmlManagerPara.string)) {
			sql = sql + fieldname;
			if (attribute.getPrecision() < 50) {
				sql = sql + " CHAR(" + attribute.getPrecision() + ")";

			} else if (attribute.getPrecision() <= 2000 && attribute.getPrecision() >= 50) {
				sql = sql + " VARCHAR(" + attribute.getPrecision() + ")";
			} else {
				sql = sql + " VARCHAR(" + attribute.getPrecision() + ")";
			}
			if (attribute.getDefaultValue() != null && !attribute.getDefaultValue().equals("")) {
				sql = sql + " default '" + attribute.getDefaultValue() + "'";
			} else {
				sql = sql + " default null";
			}
		}

		log.info("sql = " + sql);
		return sql;
	}

	@Override
	public boolean createConstanTable(Template template) {
		String sql = getCreateConstanTableDLL(template);
		log.info("createsql=" + sql);
		MdpDataSource MdpDataSource = sysmodel.getDataSourceByCode(template.getDataSource());
		return this.executeDDL(MdpDataSource, sql);
	}

	@Override
	public ArrayList<Vector<Object>> updateConstanTable(Template template) {
		MdpDataSource MdpDataSource = sysmodel.getDataSourceByCode(template.getDataSource());
		ArrayList<Vector<Object>> list = new ArrayList<Vector<Object>>();
		String tableName = template.getTableName();

		try {
			Vector<ColumnInfo> columninfos = getTableColumnInfo(tableName, MdpDataSource);
			if (columninfos.size() == 0) {
				return list;
			}
			List<PropertyImpl> Propertys = template.getListproperty();
			Vector<Object> dllVec = new Vector<Object>();

			// delete column
			Vector<ColumnInfo> delete = columnInfoService.deleteConstantDataBaseColumn(Propertys,
					columninfos);
			for (int i = 0; i < delete.size(); i++) {
				ColumnInfo imp = delete.elementAt(i);
				String sql = "alter table " + tableName + " drop column " + imp.getColumnName();
				boolean bresult = false;// executeDDL(MdpDataSource ,sql);
				dllVec = new Vector<Object>();
				dllVec.add("delete");
				dllVec.add(imp);
				dllVec.add(bresult);
				dllVec.add(sql);
				list.add(dllVec);
			}

			Vector<Property> add = columnInfoService.addConstantDataBaseColumn(Propertys,
					columninfos);
			for (int i = 0; i < add.size(); i++) {
				Property imp = add.elementAt(i);
				String sql = getCanstantModifyDllSQL(XmlManagerPara.add, imp, tableName);
				boolean bresult = false;// executeDDL(MdpDataSource ,sql);
				dllVec = new Vector<Object>();
				dllVec.add("add");
				dllVec.add(imp);
				dllVec.add(bresult);
				dllVec.add(sql);
				list.add(dllVec);
			}

			Vector<Property> update = columnInfoService.updateConstantDataBaseColumn(Propertys,
					columninfos);
			for (int i = 0; i < update.size(); i++) {
				Property imp = update.elementAt(i);
				String sql = getCanstantModifyDllSQL(XmlManagerPara.update, imp, tableName);
				boolean bresult = false;// executeDDL(MdpDataSource ,sql);
				dllVec = new Vector<Object>();
				dllVec.add("update");
				dllVec.add(imp);
				dllVec.add(bresult);
				dllVec.add(sql);
				list.add(dllVec);
			}
		} catch (Exception e) {
			log.error(e.toString());
		}
		return list;
	}

	@Override
	public boolean renameConstantTableName(Template template, String newtablename) {
		MdpDataSource MdpDataSource = sysmodel.getDataSourceByCode(template.getDataSource());
		return renameTableName(MdpDataSource, newtablename, template.getTableName());
	}

	private boolean renameTableName(MdpDataSource MdpDataSource, String newtablename,
			String oldtablename) {
		String sql = "rename " + oldtablename + " to " + newtablename;
		log.info("sql = " + sql);
		return executeDDL(MdpDataSource, sql);
	}

	private String generalSQL(MdpClassImpl table) {

		StringBuffer sql = new StringBuffer();
		sql.append("create table " + table.getName()).append(
				"(FD_OBJECTID CHAR(" + XmlManagerPara.fd_objectidlength
						+ ")  NOT NULL primary key ");

		ArrayList<MdpAttributeImpl> list = (ArrayList<MdpAttributeImpl>) table
				.getAllMdpAttributes();
		for (int i = 0; i < list.size(); i++) {

			MdpAttributeImpl attribute = list.get(i);
			String fieldname = attribute.getName();
			String datatype = attribute.getDataType();
			if (datatype.equals(XmlManagerPara.date)) {

				if (attribute.getScale().equals(SysParameter.date_format)) {
					sql.append("," + fieldname + "  DATE");
				} else if (attribute.getScale().equals(SysParameter.time_format)) {
					sql.append("," + fieldname + "  TIME");
				} else {
					sql.append("," + fieldname + "  TIMESTAMP");
				}
				if (attribute.getDefaultValue() != null
						&& attribute.getDefaultValue().equals(XmlManagerPara.sysdate)) {
					if (attribute.getScale().equals(SysParameter.date_format)) {
						sql.append(" DEFAULT CURRENT DATE");
					} else if (attribute.getScale().equals(SysParameter.time_format)) {
						sql.append(" DEFAULT CURRENT TIME");
					} else {
						sql.append(" DEFAULT CURRENT TIMESTAMP");
					}
				}

			} else if (datatype.equals(XmlManagerPara.clob)) {
				sql.append("," + fieldname + " CLOB(2)");
			} else if (datatype.equals(XmlManagerPara.blob)) {
				sql.append("," + fieldname + " BLOB(2)");
			} else if (datatype.equals(XmlManagerPara.bool)) {

				sql.append("," + fieldname + " SAMLLINT");
				if (attribute.getDefaultValue() != null && !attribute.getDefaultValue().equals("")) {
					sql.append(" default " + attribute.getDefaultValue());
				}

			} else if (datatype.equals(XmlManagerPara.image)) {

				sql.append("," + fieldname + " VARCHAR(100)");

			} else if (datatype.equals(XmlManagerPara.number)) {

				int length = attribute.getPrecision() == 0 ? 10 : attribute.getPrecision();
				String scale = attribute.getScale().equals("") ? "" : "," + attribute.getScale();
				if (scale.equals("")) {
					sql.append("," + fieldname + " DOUBLE");
				} else {
					sql.append("," + fieldname + " DECIMAL(" + length + scale + ")");
				}

				if (attribute.getDefaultValue() != null && !attribute.getDefaultValue().equals("")) {
					sql.append(" default " + attribute.getDefaultValue());
				}

			} else if (datatype.equals(XmlManagerPara.string)) {

				sql.append("," + fieldname + " VARCHAR(" + attribute.getPrecision() + ")");
				if (attribute.getDefaultValue() != null && !attribute.getDefaultValue().equals("")) {
					sql.append(" default '" + attribute.getDefaultValue() + "'");
				}
			}

		}
		sql.append(" ) ");

		return sql.toString();
	}

	public String getCreateConstanTableDLL(Template template) {

		StringBuffer sql = new StringBuffer();
		sql.append("create table " + template.getTableName()).append("( ");

		ArrayList<PropertyImpl> propertys = (ArrayList<PropertyImpl>) template.getListproperty();

		for (int i = 0; i < propertys.size(); i++) {

			PropertyImpl PropertyImpl = propertys.get(i);
			String fieldname = PropertyImpl.getName();
			String datatype = PropertyImpl.getDataType();

			if (datatype.equals(XmlManagerPara.date)) {
				if (PropertyImpl.getScale().equals(SysParameter.date_format)) {
					sql.append(",").append(fieldname).append(" DATE");
				} else if (PropertyImpl.getScale().equals(SysParameter.time_format)) {
					sql.append(",").append(fieldname).append(" TIME");
				} else {
					sql.append(",").append(fieldname).append(" TIMESTAMP");
				}

			} else if (datatype.equals(XmlManagerPara.clob)) {

				sql.append(",").append(fieldname).append("  CLOB(2)");

			} else if (datatype.equals(XmlManagerPara.blob)) {

				sql.append(",").append(fieldname).append("  BLOB(2)");

			} else if (datatype.equals(XmlManagerPara.bool)) {

				sql.append(",").append(fieldname).append("  SMALLINT");

			} else if (datatype.equals(XmlManagerPara.image)) {

				sql.append(",").append(fieldname).append("  VARCHAR(100)");

			} else if (datatype.equals(XmlManagerPara.number)) {

				String scale = PropertyImpl.getScale().equals("") ? "" : ","
						+ PropertyImpl.getScale();
				if (scale.equals("")) {
					sql.append("," + fieldname + " SMALLINT");
				} else {
					int length = PropertyImpl.getLength() == 0 ? 10 : PropertyImpl.getLength();
					sql.append("," + fieldname + " DECIMAL(" + length + scale + ")");
				}

			} else if (datatype.equals(XmlManagerPara.string)) {

				sql.append(",").append(fieldname);
				sql.append(" VARCHAR(").append(PropertyImpl.getLength()).append(")");

			}

			if (PropertyImpl.isUnique()) {
				sql.append(" NOT NULL primary key");
			}

		}

		sql.append(" ) ");
		sql.reverse();
		sql.deleteCharAt(sql.lastIndexOf(","));
		sql.reverse();

		return sql.toString();
	}

	@Override
	public String getXmlByTableName(String tableName, MdpDataSource dataSource, String moduleId) {
		DatabaseTableInfo tableInfo = new DatabaseTableInfo();
		TableInfo tableVo = tableInfo.getTableInfo(tableName, dataSource, DataBaseType.DB2);
		Vector<ColumnInfo> ColumnInfoVec = this.getTableColumnInfo(tableName, dataSource);
		MdpDataSourceImpl dataSourceImpl = (MdpDataSourceImpl) dataSource;

		StringBuffer sb = new StringBuffer();
		sb.append(" \n  <font color='#339900'>.......................................<br> \n");
		sb.append("&lt;mdpClass classid=\"请替换成推荐号\" ");
		sb.append("name=\"" + tableName + "\" description=\"" + tableVo.getDescritpion()
				+ "\" type=\"" + moduleId + "\"  ");
		sb.append("primaryKey=\"" + tableVo.getPrimaryKey()
				+ "\" exist=\"true\" catche=\"false\"  validate=\"true\" ");
		sb.append("dataSource=\"" + dataSourceImpl.getDataSourceid() + "\" ");
		sb.append("><br>  \n");

		int colSize = ColumnInfoVec.size();
		ColumnInfo ColumnInfo = null;
		for (int i = 0; i < colSize; i++) {
			ColumnInfo = ColumnInfoVec.get(i);

			sb.append("&nbsp;&nbsp;&lt;mdpAttribute ");
			sb.append(" name=\"" + ColumnInfo.getColumnName() + "\" description=\""
					+ ColumnInfo.getDescritpion() + "\"");
			sb.append(ColumnInfo.getColumTypeDetail());
			sb.append(" unit=\"\" logicPrimaryKey=\"false\"  referenceType=\"0\" ");
			sb.append(" index=\"false\" indexType=\"\" defaultValue=\"\" notNull=\"false\" validate=\"true\" ");
			sb.append(" isAutoIncrement=\"false\" isReadOnly=\"false\" isSearchable=\"true\" isSigned=\"false\" ");
			sb.append(" columnDisplaySize=\"" + ColumnInfo.getColumnDisplaySize()
					+ "\" fieldType=\"" + ColumnInfo.getColumnTypeName() + "("
					+ ColumnInfo.getPrecision() + ")\" ");
			sb.append(" /&gt;");
			sb.append("<br> \n");
		}
		ColumnInfoVec.clear();
		sb.append("&lt;/mdpClass&gt;<br>");
		sb.append("........................................<br></font>");
		return sb.toString();
	}

	@Override
	public StringBuffer createTableComment(MdpClassImpl table, String type) {
		StringBuffer sb = new StringBuffer();

		sb.append("<font color='#339900'>");
		if (type.equals("add")) {
			sb.append("comment on table " + table.getName() + " is '" + table.getDescription()
					+ "';<br>");
		} else {
			sb.append("comment on table " + table.getName() + " is '';<br>");
		}

		ArrayList<MdpAttributeImpl> list = (ArrayList<MdpAttributeImpl>) table
				.getAllMdpAttributes();
		for (int i = 0; i < list.size(); i++) {

			MdpAttributeImpl attribute = list.get(i);
			if (type.equals("add")) {

				String sComment = attribute.getDescription();
				if (!attribute.getUnit().equals("")) {
					sComment = sComment + "(" + attribute.getUnit() + ")";
				}

				sb.append("comment on column " + table.getName() + "." + attribute.getName()
						+ " is '" + sComment + "';<br>");

			} else {
				sb.append("comment on column " + table.getName() + "." + attribute.getName()
						+ " is '';<br>");
			}

		}
		sb.append("</font>");
		return sb;
	}

	@Override
	public StringBuffer createConstanTableComment(Template template, String type) {
		return null;
	}

	@Override
	public StringBuffer createOldTableComment(MdpClassImpl table, String type) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public ArrayList<String> getTableCreateKeyDll(int iDataSource) {

		ArrayList<String> resultlis = new ArrayList<String>();
		// 获得所有没有建立主键的表
		String sql = "select tabname,tabschema from syscat.tables "
				+ "where TABSCHEMA='DB2ADMIN'  and  tabname not in ("
				+ "  select   TABNAME   from   SYSCAT.KEYCOLUSE   where   TABSCHEMA='DB2ADMIN' "
				+ ") ";
		MdpDataSource datasource = sysmodel.getDataSourceByCode(iDataSource);
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			DbConnection dbConnection = new DbConnection();
			conn = dbConnection.getConnection(datasource);
			st = conn.createStatement();
			rs = st.executeQuery(sql);

			while (rs.next()) {
				String tableName = rs.getString(1);
				String tempSql = "";
				tempSql = this.getMdpClassePkDll(tableName);

				if (tempSql.equals("")) {
					tempSql = this.getMdpConstantPkDll(tableName);
				}
				if (!tempSql.equals("")) {
					resultlis.add(tempSql);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (st != null)
					st.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return resultlis;
	}

	private String getMdpConstantPkDll(String tableName) {
		String result = "";
		ArrayList<MdpConstantImpl> lis = sysmodel.getMdpConstants();
		for (int i = 0; i < lis.size(); i++) {
			MdpConstant MdpConstant = (MdpConstant) lis.get(i);
			if (MdpConstant.getCodeName().equalsIgnoreCase(tableName.trim())) {
				String colname = MdpConstant.getTemplate().getUniqueProperty().getName();
				result = "ALTER TABLE " + tableName + " ADD  CONSTRAINT PK_" + tableName
						+ " PRIMARY KEY (" + colname + ")";
				break;
			}
		}
		return result;
	}

	private String getMdpClassePkDll(String tableName) {
		String result = "";
		ArrayList<MdpClassImpl> lis = sysmodel.getMdpClasses();
		for (int i = 0; i < lis.size(); i++) {
			MdpClass mdpClass = (MdpClass) lis.get(i);
			if (mdpClass.getName().toUpperCase().equals(tableName.toUpperCase())) {
				String colname = "FD_OBJECTID";
				if (mdpClass.getClassid() == 99 || mdpClass.getClassid() == 100)
					colname = "ClassId";
				result = "ALTER TABLE " + tableName + " ADD  CONSTRAINT PK_" + tableName
						+ " PRIMARY KEY (" + colname + ")";
				break;
			}
		}
		return result;
	}

	@Override
	public ArrayList<IndexInfo> getTableIndexInfo() {
		ArrayList<IndexInfo> resultlis = new ArrayList<IndexInfo>();
		try {
			SysPersistence persistence = PersistenceFactory.getInstance(sysmodel);
			String sql = "select tabname,indname,colnames,indextype from syscat.indexes where TABSCHEMA='DB2ADMIN' order by tabname";

			ArrayList<String[]> tablis = persistence.getSearchResult(99, sql);
			for (int i = 0; i < tablis.size(); i++) {

				String[] tabArry = tablis.get(i);
				IndexInfo IndexInfo = new IndexInfo();
				IndexInfo.setTabName(tabArry[0]);
				IndexInfo.setIndexName(tabArry[1]);
				IndexInfo.setColumNames(tabArry[2]);
				IndexInfo.setIndexType(tabArry[3]);

				resultlis.add(IndexInfo);
			}
		} catch (PersistenceException e) {
			e.printStackTrace();
		}
		return resultlis;
	}

	@Override
	public ArrayList<String> getTableIndexCreateDll() {
		/*
		 * DROP INDEX "index_Name"; CREATE INDEX "index_Name" ON
		 * ADJUNCT_IMG("NAME" ASC)
		 * 
		 * DROP INDEX "ind_Mainid"; CREATE INDEX "ind_Mainid" ON ADJUNCT_IMG
		 * ("MAINID" ASC) CLUSTER;
		 */
		ArrayList<String> result = new ArrayList<String>();
		ArrayList<MdpClassImpl> mdpClasses = sysmodel.getMdpClasses();
		int mdpClasseslen = mdpClasses.size();
		for (int i = 0; i < mdpClasseslen; i++) {
			MdpClass mdpClass = (MdpClass) mdpClasses.get(i);
			List<MdpAttribute> arrtibutes = mdpClass.getValidateMdpAttributes();
			String tableName = mdpClass.getName();
			for (int j = 0; j < arrtibutes.size(); j++) {
				MdpAttribute MdpAttribute = arrtibutes.get(j);
				if (MdpAttribute.isIndex()) {
					String fieldName = MdpAttribute.getName();
					String indtype = MdpAttribute.getIndexType();
					String indName = "idx_" + mdpClass.getClassid() + fieldName;
					StringBuffer sb = new StringBuffer();

					// sb.append("DROP INDEX  "+indName+"");

					sb.append("CREATE INDEX " + indName + " ON " + tableName + " (" + fieldName
							+ " ASC) ");
					if ("CLUSTERED".equalsIgnoreCase(indtype)) {
						sb.append(" CLUSTER");
					}
					result.add(sb.toString());

				}
			}
		}
		return result;
	}
}