package hiro.yoshioka.sql;

import hiro.yoshioka.sdh.DatabaseType;
import hiro.yoshioka.sdh.ResultSetDataHolder;
import hiro.yoshioka.sdh2.ResultSetDataHolder2;
import hiro.yoshioka.sql.engine.GettingResourceRequest;
import hiro.yoshioka.sql.engine.Request;
import hiro.yoshioka.sql.engine.SQLOperationType;
import hiro.yoshioka.sql.params.ConnectionProperties;
import hiro.yoshioka.sql.resource.DBColumn;
import hiro.yoshioka.sql.resource.DBCrossRefference;
import hiro.yoshioka.sql.resource.DBResource;
import hiro.yoshioka.sql.resource.DBRoot;
import hiro.yoshioka.sql.resource.DBSchema;
import hiro.yoshioka.sql.resource.DBTable;
import hiro.yoshioka.sql.resource.IDBColumn;
import hiro.yoshioka.sql.resource.IDBResource;
import hiro.yoshioka.sql.resource.IDBSchema;
import hiro.yoshioka.sql.resource.IDBTable;
import hiro.yoshioka.sql.resource.ProcedureType;
import hiro.yoshioka.sql.resource.TableType;
import hiro.yoshioka.util.SQLDataType;
import hiro.yoshioka.util.SQLUtil;
import hiro.yoshioka.util.StringUtil;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.LinkedHashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.TreeMap;
import java.util.TreeSet;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public abstract class AbsBasicSQL implements IAbsBasicSQL, IConnectSQL {
	public enum PROPERTY {
		DATABASE
	}

	public static final String DEFAULT_SQL_TOKENS = "SELECT,FROM,WHERE,ORDER,GROUP,SYSDATE,AS,BY,LIKE,ESCAPE,DISTINCT,NOT,NULL,INNER,OUTER,LEFT,RIGHT,JOIN,ON,FULL,USING,IN,IS,OR,AND,CASE,WHEN,THEN,ELSE,END,UNION,ALL,ASC,DESC,HAVING,BETWEEN,EXISTS"; //$NON-NLS-1$
	public String clientInfo;
	public static final String[] EMPTY = StringUtil.EMPTY_STRING_ARRAY;
	public static final String[] GET_TABLES_TYPES_ONLY_TABLES = { "TABLE", //$NON-NLS-1$
			"SYSTEM TABLE" }; //$NON-NLS-1$
	public static final String[] GET_TABLES_TYPES_WITH_VIEWS = { "TABLE", //$NON-NLS-1$
			"SYSTEM TABLE", "VIEW" }; //$NON-NLS-1$ //$NON-NLS-2$
	protected Log fLogger = LogFactory.getLog(getClass());

	protected Connection _con;

	protected boolean autoCommitMode;

	protected Connection _extra_con;

	protected ResultSetUtil rsUtil = new ResultSetUtil();

	protected Driver _driver;

	protected String _url;

	protected ConnectionProperties _info;

	protected boolean connectiong;

	protected boolean capturing;

	protected List<SqlTransactionListener> fTransactionListenerList = new ArrayList<SqlTransactionListener>();

	protected List<SqlBasicListener> fConnectionListenerList = new ArrayList<SqlBasicListener>();

	protected AbsBasicSQL(Driver ds) {
		_driver = ds;
	}

	public void setMakeBlobData(boolean makeblob) {
		this.rsUtil.setMakeBlobData(makeblob);
	}

	public boolean isMakeBlobData() {
		return rsUtil.isMakeBlobData();
	}

	public DatabaseType getDatabaseType() {
		return DatabaseType.parse(_driver.getClass().getName());
	}

	public boolean load(File f) {
		DBRoot root = loadDBRoot(f);
		setRoot(root);
		return root != null;
	}

	public boolean hasDriverClass() {
		return _driver != null;
	}

	public static DBRoot loadDBRoot(File f) {
		ObjectInputStream in = null;
		try {
			in = new ObjectInputStream(new FileInputStream(f));
			return (DBRoot) in.readObject();
		} catch (Exception e) {
			return null;
		} finally {
			if (in != null) {
				try {
					in.close();
				} catch (IOException e1) {
					return null;
				}
			}
		}
	}

	private void write(IDBResource res, File ff) throws IOException {
		ObjectOutputStream out2 = null;
		try {
			out2 = new ObjectOutputStream(new FileOutputStream(ff));
			out2.writeObject(res);
		} catch (Exception e) {
			fLogger.error(StringUtil.EMPTY_STRING, e);
		} finally {
			if (out2 != null) {
				out2.close();
			}
		}
	}

	public boolean save(File f) throws IOException {
		ObjectOutputStream out = null;
		try {
			out = new ObjectOutputStream(new FileOutputStream(f));
			out.writeObject(getRoot());
		} catch (Exception e) {
			fLogger.error(StringUtil.EMPTY_STRING, e);
		} finally {
			if (out != null) {
				out.close();
			}
		}

		return true;
	}

	public boolean connect(ConnectionProperties properties) throws SQLException {
		try {
			if (_con == null) {
				_info = properties;
				rsUtil.setConnectionDisplayString(properties.getDisplayString());
				_url = properties.getProperty("url"); //$NON-NLS-1$
				connectiong = true;
				fLogger.info("Driver=" + _driver + " THIS[" + this + "]"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
				if (this._driver == null) {
					File f = new File(properties.getDriverFilePath());
					String s = null;
					if (f.isFile() && f.exists()) {
						s = String.format(
								Messages.getString("AbsBasicSQL.MissingClass"), //$NON-NLS-1$
								properties.getDriverName(),
								properties.getDriverFilePath());
					} else {
						s = String
								.format(Messages
										.getString("AbsBasicSQL.MissingJDBCDriverFile"), //$NON-NLS-1$
								properties.getDriverFilePath());
					}
					throw new IllegalStateException(s);
				}
				fLogger.info("URL ACCEPT=" + (_driver.acceptsURL(_url)) //$NON-NLS-1$
						+ "ConnectionInfo:" + _info); //$NON-NLS-1$

				_con = _driver.connect(_url,
						properties.getRelationalDBConnectionProperties());

				_con.setAutoCommit(properties.isAutoCommit());
				this.autoCommitMode = properties.isAutoCommit();

				_extra_con = _driver.connect(_url,
						properties.getRelationalDBConnectionProperties());
				_extra_con.setAutoCommit(true);
				for (int i = 0; i < fConnectionListenerList.size(); i++) {
					fConnectionListenerList.get(i).connected();
				}
			}
			properties.setConnected(true);
			return true;
		} finally {
			connectiong = false;
		}
	}

	public boolean doOperation(SQLOperationType OperationCode, Request request)
			throws SQLException {
		fLogger.info("doOperation:" + OperationCode); //$NON-NLS-1$
		switch (OperationCode) {
		case CLOSE:
			return close();
		case CONNECT:
			return connect(request.getConnectionProperties());
		case GET_DDL:
			return setTableText((GettingResourceRequest) request);
		case RESOURCE_CAPTION:
			return getMetaData((GettingResourceRequest) request) != null;
		default:
			fLogger.warn("through default..." + OperationCode); //$NON-NLS-1$
			break;
		}
		return false;
	}

	public boolean close() throws SQLException {
		fLogger.info("start.");
		boolean status = true;
		try {
			if (_con != null) {
				if (!_con.isClosed() && !_info.isAutoCommit()) {
					_con.rollback();
					_con.close();
					fLogger.info("Closed connection"); //$NON-NLS-1$
				}
				// fTrunsactionTime = false;
			}
			if (_extra_con != null) {
				if (!_extra_con.isClosed()) {
					_extra_con.close();
					fLogger.info("Closed background connection"); //$NON-NLS-1$
				}
				// fTrunsactionTime = false;
			}
		} finally {
			_con = null;
			_extra_con = null;

			_info.setConnected(false);
			fLogger.info("_info.setConnected(false)"); //$NON-NLS-1$
			for (int i = 0; i < fConnectionListenerList.size(); i++) {
				fConnectionListenerList.get(i).disconnected();
			}
		}
		return status;
	}

	protected void notifyExecute(Connection connection,
			SQLExecutionStatus status, String... params) {
		switch (status) {
		case AFTER_EXECUTE:
			if (params.length != 2) {
				System.err.println("Argments count is must be 2!"); //$NON-NLS-1$
			}
			break;

		default:
			break;
		}
		for (int i = 0; i < fTransactionListenerList.size(); i++) {
			try {
				fTransactionListenerList.get(i).executionStatus(status,
						(_extra_con == connection), params);
			} catch (Exception e) {
			}
		}
	}

	public void addConnectionListner(SqlBasicListener listner) {
		fConnectionListenerList.add(listner);
	}

	public void removeConnetionListener(SqlBasicListener listner) {
		fConnectionListenerList.remove(listner);
	}

	@Override
	public ResultSetDataHolder renameField(IDBColumn before, String afterName)
			throws SQLException {
		return null;
	}

	public void addTracsactionListner(SqlTransactionListener listener) {
		fTransactionListenerList.add(listener);
	}

	public void removeTracsactionListner(SqlTransactionListener listener) {
		fTransactionListenerList.remove(listener);
	}

	public DBRoot getRoot() {
		return _info.getDBRootResource();
	}

	public void setRoot(DBRoot root) {
		_info.setDBRoot(root);
	}

	public DBRoot getCopyRoot() {
		ObjectOutputStream out = null;
		ObjectInputStream in = null;
		DBRoot newObject = null;
		try {
			ByteArrayOutputStream bout = new ByteArrayOutputStream();
			out = new ObjectOutputStream(bout);
			out.writeObject(getRoot());
			out.close();
			byte[] bytes = bout.toByteArray();
			in = new ObjectInputStream(new ByteArrayInputStream(bytes));
			newObject = (DBRoot) in.readObject();
			in.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (in != null) {
					in.close();
				}
				if (out != null) {
					out.close();
				}
			} catch (Exception ee) {
				ee.printStackTrace();
			}
		}
		return newObject;
	}

	private void setProp(Method method, Object invokeObject, Properties p) {
		try {
			method.setAccessible(true);
			Class retC = method.getReturnType();
			if (method.getParameterTypes().length == 0
					&& (retC.equals(String.class) || retC.equals(int.class) || retC
							.equals(boolean.class))) {
				if (method.getName().startsWith("get")) { //$NON-NLS-1$
					Object o = method.invoke(invokeObject);
					p.put(method.getName().substring(3), String.valueOf(o));
				} else if (method.getName().startsWith("supports")) { //$NON-NLS-1$
					Object o = method.invoke(invokeObject);
					p.put(method.getName().substring(8), String.valueOf(o));
				}
			}
		} catch (Throwable e) {
			fLogger.trace(method + "::" + e.getMessage()); //$NON-NLS-1$
		}
	}

	@Override
	public String getDefaultSchemaName() {
		return _info.getAuthenticate().getUser();
	}

	@Override
	public Set<String> getSchemas() {
		ResultSet rs = null;
		Set<String> retSet = new LinkedHashSet<String>();
		try {
			fLogger.info("start"); //$NON-NLS-1$
			capturing = true;
			DatabaseMetaData meta = _extra_con.getMetaData();
			notifyExecute(_extra_con, SQLExecutionStatus.GET_META_SCHEMA);
			rs = meta.getSchemas();
			while (rs.next()) {
				retSet.add(rs.getString(1));
			}
		} catch (Exception e) {
			fLogger.error(StringUtil.EMPTY_STRING, e);
			// perfect set or nothing.
			return Collections.EMPTY_SET;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			fLogger.info("end [" + retSet + "]"); //$NON-NLS-1$ //$NON-NLS-2$
			capturing = false;
		}
		return retSet;
	}

	@Override
	public Set<String> getTables(String schema) {
		try {
			capturing = true;
			DatabaseMetaData meta = _extra_con.getMetaData();
			notifyExecute(_extra_con, SQLExecutionStatus.GET_META_TABLE);
			ResultSet rs = meta.getTables(null, schema, "%",
					new String[] { "TABLE" });
			Set<String> retSet = new TreeSet<String>();
			while (rs.next()) {
				retSet.add(rs.getString("TABLE_NAME"));
			}
			rs.close();
			fLogger.info("SCHEMA:" + schema + " RETURN :" + retSet);
			return retSet;
		} catch (Exception e) {
			fLogger.error(StringUtil.EMPTY_STRING, e);
			return Collections.EMPTY_SET;
		} finally {
			capturing = false;
		}
	}

	public ResultSetDataHolder getProcedures(String name) {
		fLogger.info("start"); //$NON-NLS-1$
		try {
			capturing = true;
			DatabaseMetaData meta = _extra_con.getMetaData();
			notifyExecute(_extra_con, SQLExecutionStatus.GET_META_PROCEDURE);
			return RS2RDH(meta.getProcedures(null, name, "%"), true); //$NON-NLS-1$
		} catch (Exception e) {
			fLogger.error(StringUtil.EMPTY_STRING, e);
			return null;
		} finally {
			fLogger.info("end"); //$NON-NLS-1$
			capturing = false;
		}
	}

	protected DBRoot getMetaData(GettingResourceRequest request) {
		DBRoot root = getRoot();
		try {
			capturing = true;
			DatabaseMetaData meta = _extra_con.getMetaData();
			String userName = _info.getAuthenticate().getUser();

			if (request.canceld()) {
				return null;
			}
			fLogger.info(String.format(" request[%s] ", request));
			switch (request.targetType) {
			case ONLY_SCHEMA:

				long elapsedTime = System.currentTimeMillis();
				IDBSchema schema = (IDBSchema) request.selectionResource;
				schema.removeAll();
				fLogger.info("After remove all resources elapsedTime " //$NON-NLS-1$
						+ StringUtil.cnvMilliToTimeString(System
								.currentTimeMillis() - elapsedTime));
				createDBTableDef(request);
				fLogger.info("After createDBTableDef elapsedTime " //$NON-NLS-1$
						+ StringUtil.cnvMilliToTimeString(System
								.currentTimeMillis() - elapsedTime));
				if (request.canceld()) {
					return null;
				}

				// mode [request.targetType.isOnlySchema()] is all caption!
				// _info.isCaptureWithProcedureInfo()) {
				createDBProcedureDef(request);
				fLogger.info("After createDBProcedureDef elapsedTime " //$NON-NLS-1$
						+ StringUtil.cnvMilliToTimeString(System
								.currentTimeMillis() - elapsedTime));
				if (request.canceld()) {
					return null;
				}

				getSequence(request);
				break;
			case ONLY_TABLE:
				IDBTable tbl = (IDBTable) request.selectionResource;
				tbl.removeAllColumns();
				setTableColumns(tbl.getParent().getName(), tbl);
				break;
			default:
				root = new DBRoot(meta.getUserName());
				setRoot(root);
				root.setProperties(getDBMetaProperties(meta));

				fLogger.info("re-generate DBRoot"); //$NON-NLS-1$
				createSchemaDef(request);
				String clazzName = this.getClass().toString().toLowerCase();
				switch (_info.getDatabaseType()) {
				case SQLITE:
					break;
				case HSQL:
					root.setDefaultSchema((IDBSchema) root
							.getResource(getDefaultSchemaName()));
					root.setCurrentSchema((IDBSchema) root
							.getResource(getDefaultSchemaName()));
					break;
				case POSTGRES:
					root.setDefaultSchema((IDBSchema) root
							.getResource(PostgresSQL.DEFAULT_SCHEMA_NAME));
					break;
				case MS_SQLSERVER:
					root.setDefaultSchema((IDBSchema) root
							.getResource(getDefaultSchemaName()));
					break;
				case MYSQL:
					String myDatabase = MySQL.getDatabaseName(_info
							.getURLString());
					for (IDBSchema schemaAA : root.getSchemas()) {
						if (schemaAA.getName().equalsIgnoreCase(myDatabase)) {
							root.setDefaultSchema(schemaAA);
							root.setCurrentSchema(schemaAA);
							break;
						}
					}
					break;
				default:
					IDBSchema[] schemas = root.getSchemas();
					for (int i = 0; i < schemas.length; i++) {
						fLogger.info(schemas[i].getName() + "= comp =" //$NON-NLS-1$
								+ userName);
						if (schemas[i].getName().equalsIgnoreCase(userName)) {
							root.setDefaultSchema(schemas[i]);
							root.setCurrentSchema(schemas[i]);
							break;
						}
					}
					break;
				}
				if (root.getCurrentSchema() == null) {
					IDBSchema mschema = new DBSchema(root);
					mschema.setName(userName);
					root.putResource(mschema.getName(), mschema);
					// _root.setDefaultSchema(mschema);
					root.setCurrentSchema(mschema);
				}
				if (root.getDefaultSchema() == null) {
					root.setDefaultSchema(root.getCurrentSchema());
				}
				DBRoot oldRoot = _info.getDBRootResource();
				if (oldRoot != null) {
					root.getRecentryUsedResource().addAll(
							oldRoot.getRecentryUsedResource());
				}
				if (request.canceld()) {
					return null;
				}

				createDBTableDef(request);
				if (request.canceld()) {
					return null;
				}
				if (request.fCaptionForignKey) {
					createCrossReferenceDef(request);
				}
				if (_info.isCaptureWithProcedureInfo()) {
					createDBProcedureDef(request);
					if (request.canceld()) {
						return null;
					}
				}
				if (request.doGetIndex()) {

				}

				if (request.getConnectionProperties().isCaptureWithDDL()) {
					setTableText(request);
				}
				if (request.canceld()) {
					return null;
				}
				if (_info.isCaptureWithSequenceInfo()) {
					getSequence(request);
				}
			}
			if (!request.canceld()) {
				getTrigger(request);
			}
			if (!request.canceld()) {
				setComments(request);
			}
			return root;
		} catch (Exception e) {
			fLogger.error(StringUtil.EMPTY_STRING, e);
			return null;
		} finally {
			capturing = false;
		}
	}

	boolean doCaptureColumn(IDBTable dbTable, GettingResourceRequest request) {
		if (_info == null) {
			return false;
		}
		if (_info.isCaptureWithColumnInfo()) {
			return true;
		}
		DBRoot old = request.getConnectionProperties().getDBRootResource();
		if (old != null && old.isRecentryUsed(dbTable)) {
			return true;
		}
		return getRoot().isRecentryUsed(dbTable);

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see hiro.yoshioka.sql.IAbsBasicSQL#isCapturing()
	 */
	public boolean isCapturing() {
		return capturing;
	}

	protected Properties getDBMetaProperties(DatabaseMetaData meta) {
		Properties p = new Properties();

		p.put(Messages.getString("AbsBasicSQL.ResourceUpdateTime"), String.format("%tF %tT", //$NON-NLS-1$ //$NON-NLS-2$
								new java.util.Date(), new java.util.Date()));
		try {
			Method[] methods = DatabaseMetaData.class.getDeclaredMethods();
			for (int i = 0; i < methods.length; i++) {
				setProp(methods[i], meta, p);
			}
			p.put("supportTokens", getSupportToken()); //$NON-NLS-1$
		} catch (RuntimeException e1) {
			fLogger.info(e1);
		}

		try {
			switch (meta.getDefaultTransactionIsolation()) {
			case Connection.TRANSACTION_NONE:
				p.put("DefaultTransactionIsolation", "TRANSACTION_NONE"); //$NON-NLS-1$ //$NON-NLS-2$
				break;
			case Connection.TRANSACTION_READ_COMMITTED:
				p.put("DefaultTransactionIsolation", //$NON-NLS-1$
						"TRANSACTION_READ_COMMITTED"); //$NON-NLS-1$
				break;
			case Connection.TRANSACTION_READ_UNCOMMITTED:
				p.put("DefaultTransactionIsolation", //$NON-NLS-1$
						"TRANSACTION_READ_UNCOMMITTED"); //$NON-NLS-1$
				break;
			case Connection.TRANSACTION_REPEATABLE_READ:
				p.put("DefaultTransactionIsolation", //$NON-NLS-1$
						"TRANSACTION_REPEATABLE_READ"); //$NON-NLS-1$
				break;
			case Connection.TRANSACTION_SERIALIZABLE:
				p.put("DefaultTransactionIsolation", "TRANSACTION_SERIALIZABLE"); //$NON-NLS-1$ //$NON-NLS-2$
				break;
			default:
				p.put("DefaultTransactionIsolation", "UNKNOWN"); //$NON-NLS-1$ //$NON-NLS-2$
			}
		} catch (Exception e1) {
			fLogger.info(e1);
		}
		return p;
	}

	/**
	 * 
	 */
	protected void createDictionary() throws SQLException {
	}

	protected String getSupportToken() {
		return StringUtil.EMPTY_STRING;
	}

	protected int countResultSet(ResultSet rs) {
		int row = 0;
		try {
			while (rs.next()) {
				row++;
			}
			rs.close();
		} catch (SQLException e) {
		}
		fLogger.info("num of:" + row); //$NON-NLS-1$
		return row;
	}

	private void createSchemaDef(GettingResourceRequest request)
			throws SQLException {
		DBRoot root = getRoot();
		for (String name : getSchemas()) {
			if (_info.isCapturingTarget(name)) {
				IDBSchema mschema = (IDBSchema) root.getResource(name);
				if (mschema == null) {
					mschema = new DBSchema(root);
					mschema.setName(name);
					root.putResource(mschema.getName(), mschema);
				}
			}
		}
	}

	protected void createDBTableDef(GettingResourceRequest request)
			throws SQLException {
		try {
			fLogger.info("start"); //$NON-NLS-1$
			ConnectionProperties cp = request.getConnectionProperties();
			List<String> typesList = new ArrayList<String>();

			if (request.targetType.isOnlySchema()
					|| cp.isCaptureWithTableInfo()) {
				typesList.add(TableType.TABLE.getTypeString());
				typesList.add(TableType.SYSTEM_TABLE.getTypeString());
			}
			if (request.targetType.isOnlySchema() || cp.isCaptureWithViewInfo()) {
				typesList.add(TableType.VIEW.getTypeString());
			}
			if (cp.isCaptureWithSynonymInfo()) {
				typesList.add(TableType.SYNONYM.getTypeString());
			}
			fLogger.info("AbsBasicSQL#createTableDef typesList=" + typesList); //$NON-NLS-1$
			String[] types = typesList.toArray(new String[typesList.size()]);
			if (request.targetType.isOnlySchema()) {
				IDBSchema mschema = (IDBSchema) request.selectionResource;
				String sn = mschema.getName();
				createTableDefSub(request, mschema, sn, types);
			} else {
				for (IDBSchema mschema : getRoot().getSchemas()) {
					String sn = mschema.getName();
					if (isSkipSchemaAtGetAllMetaData(sn)) {
						continue;
					}
					createTableDefSub(request, mschema, sn, types);
					if (request.canceld()) {
						return;
					}
				}
			}

		} catch (RuntimeException e) {
			fLogger.info(StringUtil.EMPTY_STRING, e);
		}
	}

	protected void createTableDefSub(GettingResourceRequest request,
			IDBSchema mschema, String sn, String[] types) throws SQLException {
		request.beginTask(String.format(Messages
				.getString("AbsBasicSQL.getTablesTypeOf"), mschema.getName(), //$NON-NLS-1$
				Arrays.toString(types)), 1);
		DatabaseMetaData meta = _extra_con.getMetaData();
		request.subTask(Messages.getString("AbsBasicSQL.nowGetTables")); //$NON-NLS-1$
		ResultSetDataHolder rdh = RS2RDH(meta.getTables(null, sn, "%", types), //$NON-NLS-1$
				true);

		fLogger.info(String.format(
				"AbsBasicSQL#createTableDefSub sn=[%s] rowCount=[%d]", sn, //$NON-NLS-1$
				rdh.getRowCount()));
		request.subTask(Messages.getString("AbsBasicSQL.doneGetTable.")); //$NON-NLS-1$

		request.beginTask(
				String.format(
						Messages.getString("AbsBasicSQL.getTables"), //$NON-NLS-1$
						mschema.getName(), Arrays.toString(types),
						rdh.getRowCount()), rdh.getRowCount());
		for (int i = 0; i < rdh.getRowCount(); i++) {
			try {
				request.worked(1);
				String tableName = rdh.getStringData(i, "TABLE_NAME"); //$NON-NLS-1$
				String type = rdh.getStringData(i, "TABLE_TYPE").toUpperCase(); //$NON-NLS-1$
				if (tableName.indexOf("/") >= 0 || tableName.indexOf("\\") >= 0 //$NON-NLS-1$ //$NON-NLS-2$
						|| tableName.indexOf("BIN$") >= 0) { //$NON-NLS-1$
					continue;
				}
				DBTable dbTable = new DBTable(mschema);
				dbTable.setName(tableName);
				dbTable.setTableType(type);
				if (doCaptureColumn(dbTable, request)) {
					setTableColumns(mschema.getName(), dbTable);
				}
				mschema.putTable(dbTable);

				request.subTask(dbTable.toString());
				setResourceProperties(dbTable, i, rdh);
				if (request.canceld()) {
					break;
				}
			} catch (SQLException e) {
				fLogger.trace(StringUtil.EMPTY_STRING, e);
			}
		}
	}

	protected boolean isSkipSchemaAtGetAllMetaData(String schemaName) {
		return false;
	}

	protected abstract boolean setTableText(GettingResourceRequest request)
			throws SQLException;

	protected abstract void getTrigger(GettingResourceRequest request)
			throws SQLException;

	protected abstract void getSequence(GettingResourceRequest request)
			throws SQLException;

	protected void createCrossReferenceDef(GettingResourceRequest request)
			throws SQLException {
		ResultSetDataHolder rdh = null;

		DatabaseMetaData meta = _extra_con.getMetaData();
		try {
			fLogger.info("start"); //$NON-NLS-1$
			DBRoot root = getRoot();
			IDBSchema[] schemas = root.getSchemas();
			fLogger.info("schemas.length[" + schemas.length + "]"); //$NON-NLS-1$ //$NON-NLS-2$
			request.beginTask("DatabaseMetaData#getCrossReference", //$NON-NLS-1$
					schemas.length);
			for (int is = 0; is < schemas.length; is++) {
				fLogger.info("getCrossReference[" + schemas[is].getName() + "]"); //$NON-NLS-1$ //$NON-NLS-2$
				request.subTask("getCrossReference[" + schemas[is].getName() //$NON-NLS-1$
						+ "]"); //$NON-NLS-1$

				rdh = RS2RDH(meta.getCrossReference(StringUtil.EMPTY_STRING,
						schemas[is].getName(), null, StringUtil.EMPTY_STRING,
						StringUtil.EMPTY_STRING, null), true);

				DBCrossRefference index = null;

				fLogger.info("rdh.getRowCount()[" + rdh.getRowCount() + "]"); //$NON-NLS-1$ //$NON-NLS-2$

				for (int i = 0; i < rdh.getRowCount(); i++) {
					index = root.getDBIndexRoot().getIndex(
							rdh.getStringData(i, "FK_NAME")); //$NON-NLS-1$
					if (index == null) {
						index = new DBCrossRefference(root.getDBIndexRoot());
						index.setName(rdh.getStringData(i, "FK_NAME")); //$NON-NLS-1$
						root.getDBIndexRoot().putIndex(index);
					}
					request.subTask("getCrossReference[" + index.getName() //$NON-NLS-1$
							+ "]"); //$NON-NLS-1$

					IDBTable t = schemas[is].getTableResource(rdh
							.getStringData(i, "PKTABLE_NAME")); //$NON-NLS-1$
					if (t == null) {
						continue;
					}
					IDBColumn c = (IDBColumn) t.getResource(rdh.getStringData(
							i, "PKCOLUMN_NAME")); //$NON-NLS-1$
					if (c == null) {
						continue;
					}
					index.putPkColumn(c);
					String fkSchema = rdh.getStringData(i, "FKTABLE_SCHEM"); //$NON-NLS-1$
					String fkTable = rdh.getStringData(i, "FKTABLE_NAME"); //$NON-NLS-1$
					String fkColumn = rdh.getStringData(i, "FKCOLUMN_NAME"); //$NON-NLS-1$
					c = root.getColumn(fkSchema, fkTable, fkColumn);
					if (c == null) {
						continue;
					}
					index.putFkColumn(c);

					index.setUpdateRule(rdh.getIntDataDefaultZero(i,
							"UPDATE_RULE")); //$NON-NLS-1$
					index.setDeleteRule(rdh.getIntDataDefaultZero(i,
							"DELETE_RULE")); //$NON-NLS-1$
				}
				request.worked(1);
			}
			fLogger.info("end root.getDBIndexRoot=" + root.getDBIndexRoot()); //$NON-NLS-1$
		} catch (RuntimeException e) {
			fLogger.info(e);
		}
	}

	protected void createDBProcedureDef(GettingResourceRequest request)
			throws SQLException {
		if (request.targetType.isOnlySchema()) {
			IDBSchema mschema = (IDBSchema) request.selectionResource;
			if (createProcedureResouceSub(request, mschema)) {

			}
		} else {
			IDBSchema[] schemas = getRoot().getSchemas();
			request.beginTask(
					Messages.getString("AbsBasicSQL.GetProcedures"), schemas.length); //$NON-NLS-1$
			for (IDBSchema mschema : schemas) {
				request.worked(1);
				String sn = mschema.getName();
				if (isSkipSchemaAtGetAllMetaData(sn)) {
					continue;
				}

				createProcedureResouceSub(request, mschema);
				if (request.canceld()) {
					return;
				}
			}
		}
	}

	protected boolean createProcedureResouceSub(GettingResourceRequest request,
			IDBSchema mschema) throws SQLException {
		DatabaseMetaData meta = _extra_con.getMetaData();
		ResultSetDataHolder rdh = RS2RDH(
				meta.getProcedures(null, mschema.getName(), "%"), true); //$NON-NLS-1$

		for (int i = 0; i < rdh.getRowCount(); i++) {
			String tableCat = rdh.getStringData(i, "PROCEDURE_CAT"); //$NON-NLS-1$
			String tableSchem = rdh.getStringData(i, "PROCEDURE_SCHEM"); //$NON-NLS-1$
			String tableName = rdh.getStringData(i, "PROCEDURE_NAME"); //$NON-NLS-1$
			if (tableName.indexOf("/") >= 0 || tableName.indexOf("\\") >= 0 //$NON-NLS-1$ //$NON-NLS-2$
					|| tableName.indexOf("BIN$") >= 0) { //$NON-NLS-1$
				continue;
			}
			String comment = rdh.getStringData(i, "REMARKS"); //$NON-NLS-1$
			// TODO: add grab condition

			DBTable dbProcudure = new DBTable(mschema);
			dbProcudure.setCatalog(tableCat);
			dbProcudure.setName(tableName);
			dbProcudure.setComment(comment);
			dbProcudure.setProcedureType(ProcedureType.parse(rdh.getIntData(i,
					"PROCEDURE_TYPE"))); //$NON-NLS-1$
			if (dbProcudure.getProcedureType().isProcedureOrUnknown()) {
				dbProcudure.setTableType(TableType.PROCEDURE);
			} else {
				dbProcudure.setTableType(TableType.FUNCTION);
			}
			if (!request.targetType.isOnlySchema()) {
				dbProcudure.setResources(getProcedureColumns(mschema.getName(),
						dbProcudure));
			}
			mschema.putProcedure(dbProcudure);

			setResourceProperties(dbProcudure, i, rdh);

			request.subTask(dbProcudure.toString());
			if (request.canceld()) {
				return false;
			}
		}
		return true;
	}

	Map getTablePrivileges(String schema, String table) throws SQLException {
		ResultSetDataHolder rdh = null;
		TreeMap map = new TreeMap();

		DatabaseMetaData dbMetaData = _extra_con.getMetaData();

		IDBColumn columnObject;
		rdh = RS2RDH(dbMetaData.getTablePrivileges(null, null, "%SESSION%"), //$NON-NLS-1$
				true);
		for (int i = 0; i < rdh.getRowCount(); i++) {
			String grantor = rdh.getStringData(i, "GRANTOR").toUpperCase(); //$NON-NLS-1$
			String grantee = rdh.getStringData(i, "GRANTEE").toUpperCase(); //$NON-NLS-1$
			String privilege = rdh.getStringData(i, "PRIVILEGE").toUpperCase(); //$NON-NLS-1$
			String is_grantable = rdh.getStringData(i, "IS_GRANTABLE") //$NON-NLS-1$
					.toUpperCase();
		}

		return map;
	}

	/**
	 * setColumns
	 * 
	 * @param schema
	 *            SchemaName
	 * @param table
	 *            DBTable
	 * @throws SQLException
	 */
	public void setTableColumns(String schema, IDBTable table)
			throws SQLException {
		if (table.isProcudeure() || table.isFunction()) {
			table.setResources(getProcedureColumns(schema, table));
			return;
		}
		List<String> pkey = null;
		if (table.isSynonym()) {
			if (!StringUtil.isEmpty(table.getComment())) {
				String[] spl = table.getComment().split("[.]"); //$NON-NLS-1$
				if (spl.length == 2) {
					pkey = getTablePrimaryKeys(StringUtil.EMPTY_STRING, spl[0],
							spl[1]);
				}
			}
		} else {
			if (table.isTable()) {
				pkey = getTablePrimaryKeys(StringUtil.EMPTY_STRING, schema,
						table.getName());
			}
		}

		ResultSet rs = null;
		Statement st = null;
		StringBuilder sql = new StringBuilder();
		try {
			sql.append("SELECT * FROM "); //$NON-NLS-1$
			if (schema.trim().length() > 0) {
				sql.append(schema).append("."); //$NON-NLS-1$
			}
			sql.append(table.getName());
			st = _extra_con.createStatement();
			st.setMaxRows(1);

			rs = st.executeQuery(sql.toString());
			ResultSetMetaData md = rs.getMetaData();
			for (int i = 1; i <= md.getColumnCount(); i++) {
				DBColumn col = new DBColumn(table);
				col.setName(md.getColumnName(i));
				col.setDataType(SQLDataType.parse(md.getColumnType(i)));
				// System.out.println("[" + i + "]" + md.getColumnName(i) + " ["
				// + md.getColumnType(i) + "] [" + md.getColumnTypeName(i)
				// + "]" + SQLDataType.parse(md.getColumnType(i)));
				try {
					col.setSize(md.getPrecision(i));
				} catch (Exception e) {
					col.setSize(0);
				}
				try {
					col.setDecimalDigits(md.getScale(i));
				} catch (Exception e) {
					col.setDecimalDigits(0);
				}

				col.setNullable((short) md.isNullable(i));
				if (pkey != null) {
					col.setPKey(pkey);
				}
				col.setDataTypeString(md.getColumnTypeName(i));
				col.setMaxColumnNameLength(SQLUtil
						.getMaxColumnNameBytes(_extra_con));

				table.putResource(col.getUName(), col);
			}
		} catch (SQLException e) {
			fLogger.info("sql=" + sql); //$NON-NLS-1$
			fLogger.info(StringUtil.EMPTY_STRING, e);
			return;
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (st != null) {
				st.close();
			}
		}
	}

	protected void setResourceProperties(DBResource res, int idx,
			ResultSetDataHolder rdh) {
		Properties p = res.getProperties();
		if (p == null) {
			p = new Properties();
		}
		String[] keys = rdh.getKey();
		for (int j = 0; j < keys.length; j++) {
			p.put(keys[j], StringUtil.nvl(rdh.getStringData(idx, keys[j])));
		}
		res.setProperties(p);
	}

	public List<String> getTablePrimaryKeys(String catalog, String schema,
			String table) throws SQLException {

		ArrayList<String> items = new ArrayList<String>();

		DatabaseMetaData dbMetaData = _extra_con.getMetaData();
		String field;

		ResultSet rs = dbMetaData.getPrimaryKeys(catalog, schema, table);
		if (rs != null) {
			ResultSetDataHolder rdh = RS2RDH(rs, true);

			for (int i = 0; i < rdh.getRowCount(); i++, items.add(field)) {
				field = rdh.getStringData(i, "COLUMN_NAME"); //$NON-NLS-1$
			}
		}
		fLogger.info(String.format("catalog:%s schema:%s table:%s ret:%s",
				catalog, schema, table, items));

		return items;
	}

	// protected void createExportedKeys(ResourceCaptionRequest request,
	// String schema, String table) throws SQLException {
	// ResultSetDataHolder rdh = null;
	//
	// rdh = RS2RDH(_meta.getExportedKeys(StringUtil.EMPTY_STRING, schema,
	// table), true);
	//
	// IDBTable pkTable = null;
	// IDBTable fkTable = null;
	// IDBSchema pkSchema = null;
	// IDBSchema fkSchema = null;
	//
	//
	// for (int i = 0; i < rdh.getRowCount(); i++) {
	// String fkName = rdh.getStringData(i, "FK_NAME");
	// DBExportedKeyIndex index = getRoot().getDBConstraintRoot()
	// .getExportedKey(fkName);
	// if (index == null) {
	// try {
	// pkSchema = (IDBSchema) _root.getResource(rdh.getStringData(
	// i, "PKTABLE_SCHEM").toUpperCase());
	// fkSchema = (IDBSchema) _root.getResource(rdh.getStringData(
	// i, "FKTABLE_SCHEM").toUpperCase());
	// pkTable = pkSchema.getTable(rdh.getStringData(i,
	// "PKTABLE_NAME").toUpperCase());
	// fkTable = fkSchema.getTable(rdh.getStringData(i,
	// "FKTABLE_NAME").toUpperCase());
	//
	// index = new DBExportedKeyIndex(getRoot()
	// .getDBConstraintRoot());
	// index.setTable(pkTable, fkTable);
	// index.setUpdateRule(rdh.getStringData(i, "UPDATE_RULE"));
	// index.setDeleteRule(rdh.getStringData(i, "DELETE_RULE"));
	// getRoot().getDBConstraintRoot().putExportedKey(index);
	//
	// } catch (NullPointerException e) {
	// index = null;
	// fLogger.trace("PK: " + rdh.getStringData(i, "PKTABLE_SCHEM")
	// + rdh.getStringData(i, "PKTABLE_NAME"));
	// fLogger.trace("FK: " + rdh.getStringData(i, "FKTABLE_SCHEM")
	// + rdh.getStringData(i, "FKTABLE_NAME"));
	// continue;
	// }
	// }
	// if (index != null) {
	// index.putColumn((IDBColumn) pkTable.getResource(rdh
	// .getStringData(i, "PKCOLUMN_NAME")),
	// (IDBColumn) fkTable.getResource(rdh.getStringData(i,
	// "FKCOLUMN_NAME")));
	// request.subTask(index.toString());
	// request.worked(1);
	// }
	// }
	//
	// }

	protected Map getProcedureColumns(String schema, IDBTable iprocedure)
			throws SQLException {
		ResultSet rs = null;
		LinkedHashMap<String, DBColumn> map = new LinkedHashMap<String, DBColumn>();
		try {
			DBColumn value;
			DatabaseMetaData dbMetaData = _extra_con.getMetaData();
			for (rs = dbMetaData.getProcedureColumns(null, schema,
					iprocedure.getName(), "%"); rs.next();) { //$NON-NLS-1$
				value = new DBColumn(iprocedure);
				value.setName(rs.getString("COLUMN_NAME")); //$NON-NLS-1$
				value.setColumnType(rs.getShort("COLUMN_TYPE")); //$NON-NLS-1$
				value.setDataType(SQLDataType.parse(rs.getShort("DATA_TYPE"))); //$NON-NLS-1$

				value.setDataTypeString(rs.getString("TYPE_NAME")); //$NON-NLS-1$
				value.setNullable(rs.getShort("NULLABLE")); //$NON-NLS-1$
				value.setComment(rs.getString("REMARKS")); //$NON-NLS-1$
				if (value.getName() != null) {
					map.put(value.getName(), value);
				}

			}
		} catch (SQLException e) {
			fLogger.info(e);
		} finally {
			if (rs != null) {
				rs.close();
			}
		}
		return map;
	}

	public boolean canDoOperation(SQLOperationType operation) {
		switch (operation) {
		case GET_DDL:
			if (_info == null) {
				return false;
			}
			return true;
		case CLOSE:
			return _con != null;
		case CONNECT:
			if (_con != null || connectiong) {
				return false;
			}
			return true;
		case CHECK_VALIDATION:
		case RESOURCE_CAPTION:
			if (_con == null || connectiong || capturing) {
				return false;
			}
			return true;
		default:
			break;
		}
		return false;
	}

	protected abstract void setComments(GettingResourceRequest request)
			throws SQLException;

	protected ResultSetDataHolder2 RS2RDH(ResultSet rs, boolean closeResultset)
			throws SQLException {
		return RS2RDH(rs, closeResultset, null, null);
	}

	protected ResultSetDataHolder2 RS2RDH(ResultSet rs, boolean closeResultset,
			String statement, Object[] binds) throws SQLException {
		return rsUtil.RS2RDH(_info.getDatabaseType(), rs, closeResultset,
				statement, binds);
	}

	public void setBuildTimeStamp(String format) {
		rsUtil.setBuildTimeStamp(format);
	}

	public void setBuildDate(String format) {
		rsUtil.setBuildDate(format);
	}

	public boolean connected() {
		return canDoOperation(SQLOperationType.CLOSE);
	}

	class SchemaTableN {
		String fSchema;

		String fTable;

		public SchemaTableN(String name, String name2) {
			fSchema = name;
			fTable = name2;
		}

		@Override
		public String toString() {
			return fSchema + "." + fTable; //$NON-NLS-1$
		}
	}

	// ----------------------------------------------------------------
	// [5] MIRRORING
	// ----------------------------------------------------------------
	@Override
	public boolean supportResultSet() {
		return true;
	}

	@Override
	public final boolean existsSchema(String name) throws SQLException {
		boolean ret = false;
		Set<String> nameSet = getSchemas();
		if (nameSet.contains(name)) {
			ret = true;
		} else {
			for (String s : nameSet) {
				if (s.equalsIgnoreCase(name)) {
					ret = true;
					break;
				}
			}
		}

		fLogger.info(String.format(" schema exists [%b] ", ret)); //$NON-NLS-1$
		return ret;
	}
}