wp/nwyj/.svn/pristine/74/7490097035bd0bc53a4b53c3ab6f2a2991b727e3.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.XmlManagerPara;
import com.sysmodel.datamodel.xmlmodel.ModelFactory;
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.SysModel;
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 SQLserverAdapter extends DatabaseAdapter{

	private final static Logger	log					= Logger.getLogger(SQLserverAdapter.class);
	private 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 varchar("
						+ 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 varchar("
							+ 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);
	}

	@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) {
		String tableName = template.getTableName();
		MdpDataSource MdpDataSource = sysmodel.getDataSourceByCode(template.getDataSource());
		ArrayList<Vector<Object>> list = new ArrayList<Vector<Object>>();
		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;
	}

	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)) {

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

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

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

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

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

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

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

			} 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 + " float");
				} else {
					int length = PropertyImpl.getLength() == 0 ? 10 : PropertyImpl.getLength();
					sql.append("," + fieldname + " numeric(" + 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 NONCLUSTERED ");
			}
		}
		sql.append(" ) ");
		sql.reverse();
		sql.deleteCharAt(sql.lastIndexOf(","));
		sql.reverse();
		return sql.toString();
	}

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

	private boolean renameTableName(MdpDataSource MdpDataSource, String newtablename,
			String oldtablename) {
		String sql = "sp_rename  " + oldtablename + ", " + 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 varchar(" + XmlManagerPara.fd_objectidlength
						+ ")  NOT NULL primary key NONCLUSTERED ");
		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)) {

				sql.append("," + fieldname + "  datetime");

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

				sql.append("," + fieldname + " bit");
				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 + " float");
				} else {
					sql.append("," + fieldname + " numeric(" + 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();
	}

	// ==============================================================================================
	/**
	 * 获得数据库中指定表中列的信息
	 * 
	 * @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;
		try {

			DatabaseTableInfo tableInfo = new DatabaseTableInfo();
			HashMap<String, String> MapComments = tableInfo.getColComments(connection, tableName,
					DataBaseType.SQLSERVER);
			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.SQLSERVER);
				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);
			}
			MapComments.clear();
		} catch (Exception e) {
			log.error("SQLServerException-- ", e);
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (st != null)
					st.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				log.error("SQLServerException-- ", e);
			}
		}
		return vect;
	}

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

		String sql = "alter table " + tablename;
		String fieldname = imp.getName();
		String datatype = imp.getDataType();

		if (action.equals(XmlManagerPara.add)) {
			sql = sql + " add ";
		} else if (action.equals(XmlManagerPara.delete)) {
			sql = sql + " drop column " + imp.getName();
			return sql;
		} else if (action.equals(XmlManagerPara.update)) {
			// alter table HM_Plan alter column Conservation_Unit varchar(15);
			sql = "alter table  " + tablename + " alter column ";
		}

		if (datatype.equals(XmlManagerPara.date)) {
			sql = sql + fieldname + "  datetime";
		} else if (datatype.equals(XmlManagerPara.bool)) {
			sql = sql + fieldname + " bit";
			// 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 + " numeric(" + length + scale + ")";
			// sql = sql + " default null" ;
		} else if (datatype.equals(XmlManagerPara.string)) {
			sql = sql + fieldname + " 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 ";
			// sql = "sp_rename  " + tablename + "." + attribute.getName() +
			// ", ";
		} 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)) {
			sql = sql + fieldname + " datetime";
		} else if (datatype.equals("blob")) {
			sql = sql + fieldname + " image";
		}

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

		else if (datatype.equals(XmlManagerPara.bool)) {
			sql = sql + fieldname + " bit";
			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 + " numeric(" + 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 + " varchar(" + 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 String getXmlByTableName(String tableName, MdpDataSource dataSource, String moduleId) {
		DatabaseTableInfo tableInfo = new DatabaseTableInfo();
		TableInfo tableVo = tableInfo.getTableInfo(tableName, dataSource, "sqlserver");
		Vector<ColumnInfo> ColumnInfoVo = 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 = ColumnInfoVo.size();

		ColumnInfo ColumnInfo = null;
		for (int i = 0; i < colSize; i++) {
			ColumnInfo = ColumnInfoVo.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");
		}
		sb.append("&lt;/mdpClass&gt;  \n <br>\n");
		sb.append("........................................<br></font>  \n");
		log.info(sb.toString());
		ColumnInfoVo.clear();
		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("exec sp_addextendedproperty N'MS_Description' , N'" + table.getDescription()
					+ "' , N'user' , N'dbo' , N'table' , N'" + table.getName() + "'<br>");
			sb.append("GO <br>");
		} else {
			sb.append("EXEC sp_dropextendedproperty 'MS_Description','user','dbo','table','"
					+ table.getName() + "'<br>");
			sb.append("GO <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() + ")";
				}

				if (attribute.getReferenceType() != 0 && attribute.getReference() != null) {
					sComment = sComment + "(引用：" + attribute.getReference().getReferenceTable()
							+ ")";
				}

				sb.append("exec sp_addextendedproperty N'MS_Description' , N'" + sComment
						+ "',N'user',N'dbo',N'table','" + table.getName() + "', N'column', '"
						+ attribute.getName() + "'<br>");
				sb.append("GO <br>");
			} else {
				sb.append("EXEC sp_dropextendedproperty 'MS_Description','user','dbo','table','"
						+ table.getName() + "','column','" + attribute.getName() + "'<br>");
				sb.append("GO <br>");
			}
		}
		sb.append("</font>");
		return sb;
	}

	@Override
	public StringBuffer createOldTableComment(MdpClassImpl table, String type) {
		StringBuffer sb = new StringBuffer();
		sb.append("<font color='#339900'>");
		if (type.equals("add")) {
			sb.append("exec sp_addextendedproperty N'MS_Description' , N'" + table.getDescription()
					+ "——" + table.getOldName() + "' , N'user' , N'dbo' , N'table' , N'"
					+ table.getOldName() + "'<br>");
			sb.append("GO <br>");
		} else {
			sb.append("EXEC sp_dropextendedproperty 'MS_Description','user','dbo','table','"
					+ table.getOldName() + "'<br>");
			sb.append("GO <br>");
		}
		ArrayList<MdpAttributeImpl> list = (ArrayList<MdpAttributeImpl>) table
				.getAllMdpAttributes();

		for (int i = 0; i < list.size(); i++) {
			MdpAttributeImpl attribute = list.get(i);

			if (attribute.getOldName() == null || attribute.getOldName().equals(""))
				continue;

			if (type.equals("add")) {
				String sComment = attribute.getDescription();
				if (!attribute.getUnit().equals("")) {
					sComment = sComment + "(" + attribute.getUnit() + ")";
				}
				if (attribute.getReferenceType() == 2 && attribute.getReference() != null) {

					SysModel sysmodel = ModelFactory.getSysmodel();
					MdpConstantImpl MdpConstant = (MdpConstantImpl) sysmodel
							.getMdpConstantByName(attribute.getReference().getReferenceTable());
					sComment = "（引用：" + MdpConstant.getDescription() + "）" + sComment;

				}
				sb.append("exec sp_addextendedproperty N'MS_Description' , N'" + sComment
						+ "',N'user',N'dbo',N'table','" + table.getOldName() + "', N'column', '"
						+ attribute.getOldName() + "'<br>");
				sb.append("GO <br>");
			} else {
				sb.append("EXEC sp_dropextendedproperty 'MS_Description','user','dbo','table','"
						+ table.getOldName() + "','column','" + attribute.getOldName() + "'<br>");
				sb.append("GO <br>");
			}

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

	@Override
	public StringBuffer createConstanTableComment(Template template, String type) {
		String tableName = template.getTableName();
		StringBuffer sb = new StringBuffer();
		sb.append("<font color='#339900'>");
		if (type.equals("add")) {
			sb.append("exec sp_addextendedproperty N'MS_Description' , N'"
					+ template.getDescription() + "编码表' , N'user' , N'dbo' , N'table' , N'"
					+ tableName + "'<br>");
			sb.append("GO <br>");
		} else {
			sb.append("EXEC sp_dropextendedproperty 'MS_Description','user','dbo','table','"
					+ tableName + "'<br>");
			sb.append("GO <br>");
		}

		List<PropertyImpl> propertys = template.getListproperty();
		for (int i = 0; i < propertys.size(); i++) {
			PropertyImpl PropertyImpl = propertys.get(i);
			if (type.equals("add")) {
				String sComment = PropertyImpl.getDescription();
				sb.append("exec sp_addextendedproperty N'MS_Description' , N'" + sComment
						+ "',N'user',N'dbo',N'table','" + tableName + "', N'column', '"
						+ PropertyImpl.getName() + "'<br>");
				sb.append("GO <br>");
			} else {
				sb.append("EXEC sp_dropextendedproperty 'MS_Description','user','dbo','table','"
						+ tableName + "','column','" + PropertyImpl.getName() + "'<br>");
				sb.append("GO <br>");
			}
		}
		sb.append("</font>");
		return sb;
	}

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

		ArrayList<String> resultlis = new ArrayList<String>();
		// 获得所有没有建立主键的表
		String sql = "select name from sys.tables where object_id not in "
				+ "(select parent_object_id from sys.objects where type = 'PK') order by name";
		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 NONCLUSTERED (" + colname + " ASC)";
				// WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE =
				// OFF) ON PRIMARY";
				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 NONCLUSTERED (" + colname + " DESC)";
				// WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE =
				// OFF) ON PRIMARY";
				break;
			}
		}
		return result;
	}

	public ArrayList<IndexInfo> getTableIndexInfo() {

		ArrayList<IndexInfo> resultlis = new ArrayList<IndexInfo>();
		SysPersistence persistence = PersistenceFactory.getInstance(sysmodel);
		StringBuffer sb = new StringBuffer();
		try {
			// 获得数据库表索引创建情况
			sb.append("select tab.name,tab.object_id, ");
			sb.append("  ind.name indn,ind.index_id,ind.type_desc  ");
			sb.append("from sys.tables tab,sys.indexes ind  ");
			sb.append("where  ind.type !=0 ");
			sb.append("   and tab.object_id=ind.object_id  ");
			// sb.append(" and ind.object_id = OBJECT_ID(N'SysUser_UserRoleRel')  ");
			sb.append("   order by tab.name ");
			ArrayList<String[]> tablis = persistence.getSearchResult(99, sb.toString());
			/*
			 * --sysindexkeys 表字段说明 --id int 表 ID --indid smallint 索引 ID --colid
			 * smallint 列 ID --keyno smallint 该列在索引中的位置
			 */
			sb = new StringBuffer();
			sb.append("select col.id,sys.indid,col.name ");
			sb.append("from sysindexkeys sys, syscolumns col  ");
			sb.append("where sys.id=col.id ");
			sb.append("and sys.colid=col.colid ");
			// sb.append(" and sys.id=object_id( 'SysUser_UserRoleRel')  ");
			sb.append(" and col.id in (select object_id from sys.tables ) ");
			sb.append("order by col.id,sys.indid,sys.keyno ");
			ArrayList<String[]> columlis = persistence.getSearchResult(99, sb.toString());
			for (int i = 0; i < tablis.size(); i++) {
				String[] tabArry = tablis.get(i);
				IndexInfo IndexInfo = new IndexInfo();
				IndexInfo.setTabName(tabArry[0]);
				IndexInfo.setIndexName(tabArry[2]);
				IndexInfo.setIndexType(tabArry[4]);
				String tabid = tabArry[1];
				String indexid = tabArry[3];
				String columNames = "";
				for (int j = 0; j < columlis.size(); j++) {
					String[] colArry = columlis.get(j);
					if (tabid.equals(colArry[0]) && indexid.equals(colArry[1])) {
						columNames = columNames + " " + colArry[2];
					} else {
						if (!columNames.equals(""))
							break;
					}
				}
				IndexInfo.setColumNames(columNames);
				resultlis.add(IndexInfo);
			}
		} catch (PersistenceException e) {
			e.printStackTrace();
		}
		return resultlis;
	}

	@Override
	public ArrayList<String> getTableIndexCreateDll() {
		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 = "index_" + fieldName;
					StringBuffer sb = new StringBuffer();
					sb.append("IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].["
							+ tableName + "]') AND name = N'" + indName + "')");
					sb.append(" DROP INDEX [" + indName + "] ON [dbo].[" + tableName
							+ "] WITH ( ONLINE = OFF );");
					sb.append("CREATE " + indtype + " INDEX " + indName + " ON " + tableName + " ("
							+ fieldName + " ASC)");

					result.add(sb.toString());
				}

			}
		}
		return result;
	}
}

/**
 * 查询数据库中创建的主键名称、表名、所属字段名 SELECT 'ALTER TABLE '+sysobjects.name+' DROP
 * CONSTRAINT '+syscons.name+'; ', 'ALTER TABLE '+sysobjects.name+' ADD
 * CONSTRAINT PK_'+sysobjects.name+' PRIMARY KEY NONCLUSTERED
 * ('+syscolumns.name+' DESC);' --,sysobjects.name,syscons.name,syscolumns.name
 * 
 * From sysobjects inner join syscolumns on sysobjects.id = syscolumns.id left
 * outer join (select o.name sTableName, c.Name sColName From sysobjects o inner
 * join sysindexes i on o.id = i.id and (i.status & 0X800) = 0X800 inner join
 * syscolumns c1 on c1.colid <= i.keycnt and c1.id = o.id inner join syscolumns
 * c on o.id = c.id and c.name = index_col (o.name, i.indid, c1.colid))
 * pkElements on pkElements.sTableName = sysobjects.name and pkElements.sColName
 * = syscolumns.name inner join sysobjects syscons on
 * sysobjects.id=syscons.parent_obj and syscons.xtype='PK' where sysobjects.name
 * in (select name from sys.tables where name not like 'BM%') and sTableName is
 * not null and syscolumns.name='FD_OBJECTID' order by sysobjects.name
 */