/*
 * JBoss, Home of Professional Open Source.
 * See the COPYRIGHT.txt file distributed with this work for information
 * regarding copyright ownership.  Some portions may be licensed
 * to Red Hat, Inc. under one or more contributor license agreements.
 * 
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 * 
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 * 
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
 * 02110-1301 USA.
 * Modified by
 * @author  Data Virtuality GmbH
 * @date    $Date$
 *  
 */
// https://issues.jboss.org/browse/TEIID-2930
// https://issues.jboss.org/browse/TEIID-3487
// https://issues.jboss.org/browse/TEIID-4122
// https://issues.jboss.org/browse/TEIID-3568
// https://issues.jboss.org/browse/TEIID-2610 (partially, only changes for Prepared.columnMetadata field)
// https://issues.jboss.org/browse/TEIID-2476
// https://issues.redhat.com/browse/TEIID-4123
// https://issues.redhat.com/browse/TEIID-4466
// https://issues.redhat.com/browse/TEIID-2604
// https://issues.redhat.com/browse/TEIID-3818
// https://issues.redhat.com/browse/TEIID-2981
// https://issues.redhat.com/browse/TEIID-2228
// https://issues.redhat.com/browse/TEIID-2929
// https://issues.redhat.com/browse/TEIID-4446
// https://issues.redhat.com/browse/TEIID-4866 (partially)
package org.teiid.odbc;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.*;
import java.util.concurrent.ExecutionException;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.ietf.jgss.GSSCredential;
import org.teiid.adminapi.VDB;
import org.teiid.adminapi.impl.SessionMetadata;
import org.teiid.client.RequestMessage.ResultsMode;
import org.teiid.client.security.ILogon;
import org.teiid.client.security.LogonException;
import org.teiid.client.util.ResultsFuture;
import org.teiid.core.util.ApplicationInfo;
import org.teiid.core.util.StringUtil;
import org.teiid.deployers.PgCatalogMetadataStore;
import org.teiid.dqp.service.SessionService;
import org.teiid.jdbc.*;
import org.teiid.logging.LogConstants;
import org.teiid.logging.LogManager;
import org.teiid.net.TeiidURL;
import org.teiid.net.socket.AuthenticationType;
import org.teiid.odbc.PGUtil.PgColInfo;
import org.teiid.query.parser.SQLParserUtil;
import org.teiid.runtime.RuntimePlugin;
import org.teiid.security.GSSResult;
import org.teiid.transport.*;
import org.teiid.transport.PgFrontendProtocol.NullTerminatedStringDataInputStream;

//EXTENSION
// ISSUE: DVCORE-564
// FIXED BY: Salvatore Raunich
// DATE: 2013.07.19
import org.teiid.dqp.message.RequestID;
import com.datavirtuality.dv.core.interfaces.DVCaller;

import static org.teiid.odbc.PGUtil.*;
// END EXTENSION

/**
 * While executing the multiple prepared statements I see this bug currently
 * http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1007690&group_id=1000125
 */
public class ODBCServerRemoteImpl implements ODBCServerRemote {

	private static final String CONNECTION_PROPERTY_PREFIX = "connection."; //$NON-NLS-1$
	private static final String UNNAMED = ""; //$NON-NLS-1$
	private static final String PG_VERSION = "90000";
	private static Pattern setPattern = Pattern.compile("set\\s+(\\w+)\\s+to\\s+((?:'[^']*')+)", Pattern.DOTALL|Pattern.CASE_INSENSITIVE);//$NON-NLS-1$

	private static final String COLUMN_METADATA_TEMPLATE = "select n.nspname, c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull, " //$NON-NLS-1$
			+ "c.relhasrules, c.relkind, c.oid, pg_get_expr\\(d.adbin, d.adrelid\\), case t.typtype when 'd' then t.typbasetype else 0 end, t.typtypmod, c.relhasoids, '', false as relhassubclass " //$NON-NLS-1$
			+ "from \\(\\(\\(pg_catalog.pg_class c inner join pg_catalog.pg_namespace n on n.oid = c.relnamespace and %s\\) inner join pg_catalog.pg_attribute a " //$NON-NLS-1$
			+ "on \\(not a.attisdropped\\) and a.attnum > 0 and a.attrelid = c.oid\\) inner join pg_catalog.pg_type t on t.oid = a.atttypid\\) left outer join pg_catalog.pg_attrdef d " //$NON-NLS-1$
			+ "on a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname, attnum";

	private static String COLUMN_METADATA_REWRITTEN_TEMPLATE = "select t1.schemaname as nspname, c.relname, t1.name as attname, t.oid as attypid, t.typname, convert(t1.Position, short) as attnum, t.typlen as attlen," //$NON-NLS-1$
			+ PgCatalogMetadataStore.TYPMOD + " as atttypmod, "  //$NON-NLS-1$
			+ "CASE WHEN (t1.NullType = 'No Nulls') THEN true ELSE false END as attnotnull, c.relhasrules, c.relkind, c.oid, pg_get_expr(case when t1.IsAutoIncremented then 'nextval(' else t1.DefaultValue end, c.oid), " //$NON-NLS-1$
			+ " case t.typtype when 'd' then t.typbasetype else 0 end, t.typtypmod, c.relhasoids, '', false AS relhassubclass from sys.columns as t1, pg_catalog.matpg_datatype as t, pg_catalog.pg_class c"
			+ " where c.relnspname=t1.schemaname and c.relname=t1.tablename and t1.DataType = t.Name and %s" //$NON-NLS-1$
			+ " order by nspname, relname, attnum";

	private static Pattern columnMetadataPattern = Pattern.compile(String.format(COLUMN_METADATA_TEMPLATE, "c.relname = '([^']*)' and n.nspname = '([^']*)'")); //$NON-NLS-1$

	private static Pattern columnMetadataLikePattern = Pattern.compile(String.format(COLUMN_METADATA_TEMPLATE, "c.relname like '([^']*)' and n.nspname like '([^']*)'")); //$NON-NLS-1$

	private static Pattern columnMetadataOIDPattern = Pattern.compile(String.format(COLUMN_METADATA_TEMPLATE, "c.oid = (\\d+)")); //$NON-NLS-1$

	private static Pattern pkPattern = Pattern.compile("select ta.attname, ia.attnum, ic.relname, n.nspname, tc.relname " +//$NON-NLS-1$
			"from pg_catalog.pg_attribute ta, pg_catalog.pg_attribute ia, pg_catalog.pg_class tc, pg_catalog.pg_index i, " +//$NON-NLS-1$
			"pg_catalog.pg_namespace n, pg_catalog.pg_class ic where tc.relname = (E?(?:'[^']*')+) AND n.nspname = (E?(?:'[^']*')+).*", Pattern.DOTALL|Pattern.CASE_INSENSITIVE);//$NON-NLS-1$
	
	private static Pattern pkKeyPattern = Pattern.compile("select ta.attname, ia.attnum, ic.relname, n.nspname, NULL from " + //$NON-NLS-1$
			"pg_catalog.pg_attribute ta, pg_catalog.pg_attribute ia, pg_catalog.pg_class ic, pg_catalog.pg_index i, " + //$NON-NLS-1$
			"pg_catalog.pg_namespace n where ic.relname = (E?(?:'[^']*')+) AND n.nspname = (E?(?:'[^']*')+) .*", Pattern.DOTALL|Pattern.CASE_INSENSITIVE); //$NON-NLS-1$
	
	private Pattern fkPattern = Pattern.compile("select\\s+((?:'[^']*')+)::name as \"PKTABLE_CAT\"," + //$NON-NLS-1$
			"\\s+n2.nspname as \"PKTABLE_SCHEM\"," +  //$NON-NLS-1$
			"\\s+c2.relname as \"PKTABLE_NAME\"," +  //$NON-NLS-1$
			"\\s+a2.attname as \"PKCOLUMN_NAME\"," +  //$NON-NLS-1$
			"\\s+((?:'[^']*')+)::name as \"FKTABLE_CAT\"," +  //$NON-NLS-1$
			"\\s+n1.nspname as \"FKTABLE_SCHEM\"," +  //$NON-NLS-1$
			"\\s+c1.relname as \"FKTABLE_NAME\"," +  //$NON-NLS-1$
			"\\s+a1.attname as \"FKCOLUMN_NAME\"," +  //$NON-NLS-1$
			"\\s+i::int2 as \"KEY_SEQ\"," +  //$NON-NLS-1$
			"\\s+case ref.confupdtype" +  //$NON-NLS-1$
			"\\s+when 'c' then (\\d)::int2" +  //$NON-NLS-1$
			"\\s+when 'n' then (\\d)::int2" +  //$NON-NLS-1$
			"\\s+when 'd' then (\\d)::int2" +  //$NON-NLS-1$
			"\\s+when 'r' then (\\d)::int2" +  //$NON-NLS-1$
			"\\s+else 3::int2" +  //$NON-NLS-1$
			"\\s+end as \"UPDATE_RULE\"," +  //$NON-NLS-1$
			"\\s+case ref.confdeltype" +  //$NON-NLS-1$
			"\\s+when 'c' then (\\d)::int2" +  //$NON-NLS-1$
			"\\s+when 'n' then (\\d)::int2" +  //$NON-NLS-1$
			"\\s+when 'd' then (\\d)::int2" +  //$NON-NLS-1$
			"\\s+when 'r' then (\\d)::int2" +  //$NON-NLS-1$
			"\\s+else 3::int2" +  //$NON-NLS-1$
			"\\s+end as \"DELETE_RULE\"," +							 //$NON-NLS-1$
			"\\s+ref.conname as \"FK_NAME\"," +  //$NON-NLS-1$
			"\\s+cn.conname as \"PK_NAME\"," +  //$NON-NLS-1$
			"\\s+case" +  //$NON-NLS-1$
			"\\s+when ref.condeferrable then" +  //$NON-NLS-1$
			"\\s+case" +  //$NON-NLS-1$
			"\\s+when ref.condeferred then (\\d)::int2" +  //$NON-NLS-1$
			"\\s+else (\\d)::int2" +  //$NON-NLS-1$
			"\\s+end" +  //$NON-NLS-1$
			"\\s+else (\\d)::int2" +  //$NON-NLS-1$
			"\\s+end as \"DEFERRABILITY\"" +  //$NON-NLS-1$
			"\\s+from" +  //$NON-NLS-1$
			"\\s+\\(\\(\\(\\(\\(\\(\\( \\(select cn.oid, conrelid, conkey, confrelid, confkey," +  //$NON-NLS-1$
			"\\s+generate_series\\(array_lower\\(conkey, 1\\), array_upper\\(conkey, 1\\)\\) as i," +  //$NON-NLS-1$
			"\\s+confupdtype, confdeltype, conname," +  //$NON-NLS-1$
			"\\s+condeferrable, condeferred" +  //$NON-NLS-1$
			"\\s+from pg_catalog.pg_constraint cn," +  //$NON-NLS-1$
			"\\s+pg_catalog.pg_class c," +  //$NON-NLS-1$
			"\\s+pg_catalog.pg_namespace n" +  //$NON-NLS-1$
			"\\s+where contype = 'f'" +  //$NON-NLS-1$
			"\\s+and\\s+con(f?)relid = c.oid" +  //$NON-NLS-1$
			"\\s+and\\s+relname = (E?(?:'[^']*')+)" +  //$NON-NLS-1$
			"\\s+and\\s+n.oid = c.relnamespace" +  //$NON-NLS-1$
			"\\s+and\\s+n.nspname = (E?(?:'[^']*')+)" +  //$NON-NLS-1$
			"\\s+\\) ref" +  //$NON-NLS-1$
			"\\s+inner join pg_catalog.pg_class c1" +  //$NON-NLS-1$
			"\\s+on c1.oid = ref.conrelid\\)" +  //$NON-NLS-1$
			"\\s+inner join pg_catalog.pg_namespace n1" +  //$NON-NLS-1$
			"\\s+on\\s+n1.oid = c1.relnamespace\\)" +  //$NON-NLS-1$
			"\\s+inner join pg_catalog.pg_attribute a1" +  //$NON-NLS-1$
			"\\s+on\\s+a1.attrelid = c1.oid" +  //$NON-NLS-1$
			"\\s+and\\s+a1.attnum = conkey\\[i\\]\\)" +  //$NON-NLS-1$
			"\\s+inner join pg_catalog.pg_class c2" +  //$NON-NLS-1$
			"\\s+on\\s+c2.oid = ref.confrelid\\)" +  //$NON-NLS-1$
			"\\s+inner join pg_catalog.pg_namespace n2" +  //$NON-NLS-1$
			"\\s+on\\s+n2.oid = c2.relnamespace\\)" +  //$NON-NLS-1$
			"\\s+inner join pg_catalog.pg_attribute a2" +  //$NON-NLS-1$
			"\\s+on\\s+a2.attrelid = c2.oid" +  //$NON-NLS-1$
			"\\s+and\\s+a2.attnum = confkey\\[i\\]\\)" +  //$NON-NLS-1$
			"\\s+left outer join pg_catalog.pg_constraint cn" +  //$NON-NLS-1$
			"\\s+on cn.conrelid = ref.confrelid" +  //$NON-NLS-1$
			"\\s+and cn.contype = 'p'\\)" +  //$NON-NLS-1$
			"\\s+order by ref.oid, ref.i", Pattern.DOTALL|Pattern.CASE_INSENSITIVE); //$NON-NLS-1$
		
	// EXTENSION
	// ISSUE: DV-70 
	// FIXED BY: Salvatore Raunich
	// DATE: 2013.01.16
	private static Pattern declareCursorPGNPPattern = Pattern.compile("DECLARE (\\w+) CURSOR(\\s(WITH HOLD|SCROLL))? FOR (.*)", Pattern.CASE_INSENSITIVE|Pattern.DOTALL);
	private static Pattern fetchPatternOriginalPGNP = Pattern.compile("FETCH FORWARD (\\d+) FROM (\\w+).*", Pattern.DOTALL|Pattern.CASE_INSENSITIVE);
	private static Pattern fetchPatternPGNPNoQuotes = Pattern.compile("FETCH (\\d+) IN (\\w+).*", Pattern.DOTALL|Pattern.CASE_INSENSITIVE); //$NON-NLS-1$	
	// END EXTENSION
	
	// EXTENSION
	// ISSUE: REPIN-27 
	// FIXED BY: Salvatore Raunich
	// DATE: 2013.03.07
	private static Pattern setDelimiterToPattern = Pattern.compile("SET DELIMITER TO (.+)", Pattern.CASE_INSENSITIVE|Pattern.DOTALL);
	private static Pattern setDateStyleToPattern = Pattern.compile("SET DATESTYLE (.+)", Pattern.CASE_INSENSITIVE|Pattern.DOTALL);
	private static Pattern setExtraFloatDigitsToPattern = Pattern.compile("SET EXTRA_FLOAT_DIGITS (.+)", Pattern.CASE_INSENSITIVE|Pattern.DOTALL);
	private static Pattern setStatementTimeoutToPattern = Pattern.compile("SET STATEMENT_TIMEOUT (.+)", Pattern.CASE_INSENSITIVE|Pattern.DOTALL);
	private static Pattern fullCursorDeclarationPattern = Pattern.compile("BEGIN;DECLARE \"(\\w+)\" CURSOR(\\s(WITH HOLD|SCROLL))? FOR (.*)", Pattern.CASE_INSENSITIVE|Pattern.DOTALL);
	private static Pattern fullFetchDeclarationPattern = Pattern.compile(".*FETCH (\\d+) IN \"(\\w+)\".*", Pattern.DOTALL|Pattern.CASE_INSENSITIVE);
	private static Pattern beginDeclarationPattern = Pattern.compile("BEGIN;.*", Pattern.DOTALL|Pattern.CASE_INSENSITIVE);
	// END EXTENSION

	private static Pattern cursorSelectPattern = Pattern.compile("DECLARE\\s+\"(\\w+)\"(?:\\s+INSENSITIVE)?(\\s+(NO\\s+)?SCROLL)?\\s+CURSOR\\s+(?:WITH(?:OUT)? HOLD\\s+)?FOR\\s+(.*)", Pattern.CASE_INSENSITIVE|Pattern.DOTALL); //$NON-NLS-1$
	private static Pattern fetchPattern = Pattern.compile("FETCH (\\d+) IN \"(\\w+)\".*", Pattern.DOTALL|Pattern.CASE_INSENSITIVE); //$NON-NLS-1$
	private static Pattern movePattern = Pattern.compile("MOVE (\\d+) IN \"(\\w+)\".*", Pattern.DOTALL|Pattern.CASE_INSENSITIVE); //$NON-NLS-1$
	private static Pattern closePattern = Pattern.compile("CLOSE \"(\\w+)\"", Pattern.DOTALL|Pattern.CASE_INSENSITIVE); //$NON-NLS-1$
	
	private static Pattern deallocatePattern = Pattern.compile("DEALLOCATE(?:\\s+PREPARE)?\\s+(.*)", Pattern.DOTALL|Pattern.CASE_INSENSITIVE); //$NON-NLS-1$
	private static Pattern releasePattern = Pattern.compile("RELEASE (\\w+\\d?_*)", Pattern.DOTALL|Pattern.CASE_INSENSITIVE); //$NON-NLS-1$
	private static Pattern savepointPattern = Pattern.compile("SAVEPOINT (\\w+\\d?_*)", Pattern.DOTALL|Pattern.CASE_INSENSITIVE); //$NON-NLS-1$
	private static Pattern rollbackPattern = Pattern.compile("ROLLBACK\\s*(to)*\\s*(\\w+\\d+_*)*", Pattern.DOTALL|Pattern.CASE_INSENSITIVE); //$NON-NLS-1$
	
	private TeiidDriver driver;
	private ODBCClientRemote client;
	private Properties props;
	private ConnectionImpl connection;
	private volatile boolean executing;
	private boolean errorOccurred;
	
	private volatile ResultsFuture<Boolean> executionFuture;
	
	// TODO: this is unbounded map; need to define some boundaries as to how many stmts each session can have
	private Map<String, Prepared> preparedMap = Collections.synchronizedMap(new HashMap<String, Prepared>());
	private Map<String, Portal> portalMap = Collections.synchronizedMap(new HashMap<String, Portal>());
	private Map<String, Cursor> cursorMap = Collections.synchronizedMap(new HashMap<String, Cursor>());
	private	LogonImpl logon;
	
	public ODBCServerRemoteImpl(ODBCClientInstance client, TeiidDriver driver, LogonImpl logon) {
		this.driver = driver;
		this.client = client.getClient();
		this.logon = logon;
	}
	
	@Override
	public void initialize(Properties props) {
		this.props = props;
		this.client.initialized(this.props);

		String user = props.getProperty("user"); //$NON-NLS-1$
		String database = props.getProperty("database");
		String auth = props.getProperty(ILogon.AUTH_TYPE.toLowerCase());

		AuthenticationType authType = null;
		try {
			authType = getAuthenticationType(user, database, auth);
		} catch (LogonException e) {
			errorOccurred(e);
			terminate();
			//return;
		}

		if (authType.equals(AuthenticationType.USERPASSWORD)) {
			this.client.useClearTextAuthentication();
		} else if (authType.equals(AuthenticationType.GSS)) {
			this.client.useAuthenticationGSS();
		} else if (authType.equals(AuthenticationType.OPENID) || authType.equals(AuthenticationType.OAUTH2)) {
			this.client.useAuthenticationOAuth2_accessToken();
		} else {
			throw new AssertionError(RuntimePlugin.Util.gs(RuntimePlugin.Event.DVCORE94247));
		}
	}

	private AuthenticationType getAuthenticationType(String user, String database, String authType) throws LogonException {
		if (authType != null && !authType.isEmpty())
			return AuthenticationType.valueOf(authType);

		SessionService ss = this.logon.getSessionService();
		if (ss == null) {
			return AuthenticationType.USERPASSWORD;
		}
		return ss.getAuthenticationType(database, null, user);
	}

	@Override
	public void logon(String databaseName, String user, String auth, NullTerminatedStringDataInputStream data) {
		try {
			java.util.Properties info = new java.util.Properties();
			info.put(TeiidURL.CONNECTION.USER_NAME, user);

			AuthenticationType authType = getAuthenticationType(user, databaseName, auth);
			
			String password = null; 
			if (authType.equals(AuthenticationType.USERPASSWORD)) {
				password = data.readString();
			} else if (authType.equals(AuthenticationType.GSS)) {
				byte[] serviceToken = data.readServiceToken();
				GSSResult result = this.logon.neogitiateGssLogin(serviceToken, databaseName, null, user);
				serviceToken = result.getServiceToken();
				if (result.isAuthenticated()) {
					info.put(ILogon.KRB5TOKEN, serviceToken);
					// if delegation is in progress, participate in it.
					if (result.getDelegationCredential() != null) {
						info.put(GSSCredential.class.getName(), result.getDelegationCredential());
					}
				} else {
					if (!result.isNullContinuationToken()) {
						this.client.authenticationGSSContinue(serviceToken);
					}
					return;
				}
			} else if (authType.equals(AuthenticationType.OPENID) || authType.equals(AuthenticationType.OAUTH2)) {
				String token = data.readString();
				String storedToken = this.props.getProperty(TeiidURL.CONNECTION.ACCESS_TOKEN);
				if (storedToken == null) {
					this.props.setProperty(TeiidURL.CONNECTION.ACCESS_TOKEN, token);
					this.client.useAuthenticationOAuth2_refreshToken();
					return;
				}
				info.put(TeiidURL.CONNECTION.ACCESS_TOKEN, storedToken);
				info.put(TeiidURL.CONNECTION.REFRESH_TOKEN, token);
				info.put(ILogon.AUTH_TYPE, authType.toString());
			} else {
				throw new AssertionError(RuntimePlugin.Util.gs(RuntimePlugin.Event.DVCORE94247));
			}
			
			// this is local connection
			// EXTENSION
			// ISSUE: DV-54
			// FIXED BY: Salvatore Raunich
			// DATE: 29.10.2012
//			String url = "jdbc:teiid:"+databaseName+";ApplicationName=ODBC"; //$NON-NLS-1$ //$NON-NLS-2$
			String url = "jdbc:datavirtuality:"+databaseName+";ApplicationName=ODBC"; //$NON-NLS-1$ //$NON-NLS-2$
			// END EXTENSION

			if (password != null) {
				info.put(TeiidURL.CONNECTION.PASSWORD, password);
			}

			this.connection =  driver.connect(url, info);
			//Propagate so that we can use in pg methods
			SessionMetadata sm = ((LocalServerConnection)this.connection.getServerConnection()).getWorkContext().getSession();
			sm.addAttchment(ODBCServerRemoteImpl.class, this);
			setConnectionProperties(this.connection);
			int hash = this.connection.getConnectionId().hashCode();
			Enumeration<?> keys = this.props.propertyNames();
			while (keys.hasMoreElements()) {
				String key = (String) keys.nextElement();
				// BUG: Teiid does not recognize the command "set timezone". This command is normally executed when a
				// ruby application tries to create a connection to Teiid.
				// ISSUE: DVCORE-12
				// SOLUTION This is a workaround: command is skipped and no timezone is set
				// FIXED by Salvatore Raunich
				// date 04.07.2012
				if (!key.equalsIgnoreCase("TimeZone")) {
					this.connection.setExecutionProperty(key, this.props.getProperty(key));
				}
				// BUG END
			}
			this.client.authenticationSucess(hash, hash);
			ready();
		} catch (SQLException e) {
			errorOccurred(e);
			terminate();
		} catch(LogonException e) {
			errorOccurred(e);
			terminate();
		} catch (IOException e) {
			errorOccurred(e);
			terminate();			
		}
	}

	public static void setConnectionProperties(ConnectionImpl conn)
			throws SQLException {
		SessionMetadata sm = ((LocalServerConnection)conn.getServerConnection()).getWorkContext().getSession();
		VDB vdb = sm.getVdb();
		Properties p = vdb.getProperties();
		setConnectionProperties(conn, p);
	}

	public static void setConnectionProperties(ConnectionImpl conn,
	                                           Properties p) {
		for (Map.Entry<Object, Object> entry : p.entrySet()) {
			String key = (String)entry.getKey();

			if (key.startsWith(CONNECTION_PROPERTY_PREFIX)) {
				conn.setExecutionProperty(key.substring(CONNECTION_PROPERTY_PREFIX.length()), (String) entry.getValue());
			}
		}
	}

	private void cursorExecute(final String cursorName, final String sql, final ResultsFuture<Integer> completion, boolean scroll) {
		try {
			// close if the name is already used or the unnamed prepare; otherwise
			// stmt is alive until session ends.
			Prepared previous = this.preparedMap.remove(cursorName);
			if (previous != null) {
				previous.stmt.close();
			}

			final PreparedStatementImpl stmt = this.connection.prepareStatement(sql, scroll?ResultSet.TYPE_SCROLL_INSENSITIVE:ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            this.executionFuture = stmt.submitExecute(ResultsMode.RESULTSET, null);
            this.executionFuture.addCompletionListener(new ResultsFuture.CompletionListener<Boolean>() {
                @Override
                public void onCompletion(ResultsFuture<Boolean> future) {
                    executionFuture = null;
                    try {
                        if (future.get()) {
		                    List<PgColInfo> cols = getPgColInfo(stmt.getResultSet().getMetaData());
                            cursorMap.put(cursorName, new Cursor(cursorName, sql, stmt, null, stmt.getResultSet(), cols));
                            client.sendCommandComplete("DECLARE CURSOR", null); //$NON-NLS-1$
                            completion.getResultsReceiver().receiveResults(0);
                        }
                    } catch (Throwable e) {
                        completion.getResultsReceiver().exceptionOccurred(e);
                    }
                }
			});
		} catch (SQLException e) {
			completion.getResultsReceiver().exceptionOccurred(e);
		}
	}
	
	private void cursorFetch(String cursorName, int rows, final ResultsFuture<Integer> completion) throws SQLException {
		Cursor cursor = this.cursorMap.get(cursorName);
		if (cursor == null) {
			throw new SQLException(RuntimePlugin.Util.gs(RuntimePlugin.Event.TEIID40078, cursorName));
		}
		cursor.fetchSize = rows;
		ResultsFuture<Integer> result = new ResultsFuture<Integer>();
		this.client.sendCursorResults(cursor.rs, cursor.columnMetadata, result, rows);
		result.addCompletionListener(new ResultsFuture.CompletionListener<Integer>() {
        	public void onCompletion(ResultsFuture<Integer> future) {
        		try {
					int rowsSent = future.get();
					client.sendCommandComplete("FETCH", rowsSent); //$NON-NLS-1$
					completion.getResultsReceiver().receiveResults(rowsSent);
				} catch (Throwable e) {
					completion.getResultsReceiver().exceptionOccurred(e);
				}
        	};
		});
	}
	
	private void cursorMove(String prepareName, int rows, final ResultsFuture<Integer> completion) throws SQLException {
		
		// win odbc driver sending a move after close; and error is ending up in failure; since the below
		// is not harmful it is ok to send empty move.
		if (rows == 0) {
			client.sendCommandComplete("MOVE", 0); //$NON-NLS-1$
			completion.getResultsReceiver().receiveResults(0);
			return;			
		}
		
		Cursor cursor = this.cursorMap.get(prepareName);
		if (cursor == null) {
			throw new SQLException(RuntimePlugin.Util.gs(RuntimePlugin.Event.TEIID40078, prepareName));
		}
		ResultsFuture<Integer> result = new ResultsFuture<Integer>();
		this.client.sendMoveCursor(cursor.rs, rows, result);
		result.addCompletionListener(new ResultsFuture.CompletionListener<Integer>() {
        	public void onCompletion(ResultsFuture<Integer> future) {
        		try {
					int rowsMoved = future.get();
					client.sendCommandComplete("MOVE", rowsMoved); //$NON-NLS-1$
					completion.getResultsReceiver().receiveResults(rowsMoved);
				} catch (Throwable e) {
					completion.getResultsReceiver().exceptionOccurred(e);
				}
        	};
		});
	}	
	
	private void cursorClose(String prepareName) throws SQLException {
		Cursor cursor = this.cursorMap.remove(prepareName);
		if (cursor != null) {
			cursor.rs.close();
			cursor.stmt.close();
			this.client.sendCommandComplete("CLOSE CURSOR", null); //$NON-NLS-1$
		}
	}	
	
    private void sqlExecute(final String sql, final ResultsFuture<Integer> completion) throws SQLException {
    	String modfiedSQL = fixSQL(sql); 
    	// EXTENSION
    	// ISSUE: REPIN-27 
    	// FIXED BY: Salvatore Raunich
    	// DATE: 2013.03.07
    	Matcher m = null;
    	if ((m = setDelimiterToPattern.matcher(modfiedSQL)).matches()) {
    		String delimiter = m.group(1);
    		if (delimiter.length() != 3 || !delimiter.startsWith("'") || !delimiter.endsWith("'")) {
    			System.out.println("**************** ERROR: Wrong delimiter passed: " + delimiter + ". The correct syntax is '<delimiter_char>'");;
    		} else {
    			connection.getExecutionProperties().setProperty("DELIMITER", delimiter.replaceAll("'", ""));
    		}
    		modfiedSQL = "SELECT 0";
    	}
    	// END EXTENSION
	    else if ((setDateStyleToPattern.matcher(modfiedSQL)).matches() || (setExtraFloatDigitsToPattern.matcher(modfiedSQL)).matches() || (setStatementTimeoutToPattern.matcher(modfiedSQL)).matches()) {
		    // just skip
		    modfiedSQL = "SELECT 0";
	    }
    	final StatementImpl stmt = connection.createStatement();
        executionFuture = stmt.submitExecute(modfiedSQL, null);
        executionFuture.addCompletionListener(new ResultsFuture.CompletionListener<Boolean>() {
    		@Override
    		public void onCompletion(ResultsFuture<Boolean> future) {
    			executionFuture = null;
    			try {
    				ResultsFuture<Integer> result = new ResultsFuture<Integer>();
	                if (future.get()) {
                		List<PgColInfo> cols = getPgColInfo(stmt.getResultSet().getMetaData());
		                //String tag = PgBackendProtocol.getCompletionTag(sql, null);
                        //client.sendResults(sql, stmt.getResultSet(), cols, result, tag.equals("SELECT") || tag.equals("SHOW") ); //$NON-NLS-1$ //$NON-NLS-2$
		                client.sendResults(sql, stmt.getResultSet(), cols, result, true);
	                } else {
	                	client.sendUpdateCount(sql, stmt.getUpdateCount());
	                	setEncoding();
	                	result.getResultsReceiver().receiveResults(1);
	                }
	                result.addCompletionListener(new ResultsFuture.CompletionListener<Integer>() {
	                	public void onCompletion(ResultsFuture<Integer> future) {
							try {
		                		try {
									stmt.close();
								} catch (SQLException e) {
									LogManager.logDetail(LogConstants.CTX_ODBC, e, "Error closing statement"); //$NON-NLS-1$
								}
								future.get();
								completion.getResultsReceiver().receiveResults(1);
							} catch (Throwable e) {
								completion.getResultsReceiver().exceptionOccurred(e);
							}
	                	}
	                });
    			} catch (Throwable e) {
    				completion.getResultsReceiver().exceptionOccurred(e);
    			}
    		}
		});    	
    }	
	
	@Override
	public void prepare(String prepareName, String sql, int[] paramType) {
		if (prepareName == null || prepareName.length() == 0) {
			prepareName  = UNNAMED;
		}
					
		if (sql != null) {
			String modfiedSQL = fixSQL(sql);
			try {
				// close if the name is already used or the unnamed prepare; otherwise
				// stmt is alive until session ends.
				Prepared previous = this.preparedMap.remove(prepareName);
				if (previous != null) {
					previous.stmt.close();
				}

				PreparedStatementImpl stmt = this.connection.prepareStatement(modfiedSQL);
				// EXTENSION
				// ISSUE: DV-70
				// FIXED BY: Salvatore Raunich
				// DATE: 2013.01.16
//				this.preparedMap.put(prepareName, new Prepared(prepareName, sql, stmt, paramType));
				this.preparedMap.put(prepareName, new Prepared(prepareName, modfiedSQL, stmt, paramType, getPgColInfo(stmt.getMetaData())));
				// END EXTENSION
				this.client.prepareCompleted(prepareName);
			} catch (SQLException e) {
				// EXTENSION
				// ISSUE: DVCORE-564
				// FIXED BY: Salvatore Raunich
				// DATE: 2013.07.19
				addFailedQuery(modfiedSQL, e.getMessage());				
				// END EXTENSION
				errorOccurred(e);
			}
		}
	}	
	
	@Override
	public void bindParameters(String bindName, String prepareName, int paramCount, Object[] params, int resultCodeCount, int[] resultColumnFormat) {
		// An unnamed portal is destroyed at the end of the transaction, or as soon as 
		// the next Bind statement specifying the unnamed portal as destination is issued. 
		Portal p = this.portalMap.remove(UNNAMED);
		if (p != null) {
			//closePortal(p);
		}
		
		if (prepareName == null || prepareName.length() == 0) {
			prepareName  = UNNAMED;
		}

		Prepared previous = this.preparedMap.get(prepareName);
		if (previous == null) {
			errorOccurred(RuntimePlugin.Util.gs(RuntimePlugin.Event.TEIID40077, prepareName));
			return;
		}

		if (bindName == null || bindName.length() == 0) {
			bindName  = UNNAMED;
		}

		try {
			for (int i = 0; i < paramCount; i++) {
				previous.stmt.setObject(i+1, params[i]);
			}
		} catch (SQLException e) {
			// EXTENSION
			// ISSUE: DVCORE-564
			// FIXED BY: Salvatore Raunich
			// DATE: 2013.07.19
			addFailedQuery(previous.sql, e.getMessage());
			// END EXTENSION
			errorOccurred(e);
		}

		this.portalMap.put(bindName, new Portal(bindName, previous, previous.sql, previous.stmt, resultColumnFormat));
		this.client.bindComplete();
	}

	@Override
	public void unsupportedOperation(String msg) {
		errorOccurred(msg);
	}

	@Override
	public void execute(String bindName, int maxRows) {
		if (beginExecution()) {
			errorOccurred("Awaiting asynch result"); //$NON-NLS-1$
			return;
		}
		if (bindName == null || bindName.length() == 0) {
			bindName  = UNNAMED;
		}

		// special case cursor execution through portal
		final Cursor cursor = this.cursorMap.get(bindName);
		if (cursor != null) {
			// BUG: ODBC driver overuses memory in some cases
			// ISSUE: REPIN-17
			// SOLUTION: fixed parameter reading
			// FIXED BY: Roman Pedchenko
			// DATE: 2013.03.28
			cursor.fetchSize = maxRows;
			// END BUG
			sendCursorResults(cursor);
			return;
		}		
		
		final Portal query = this.portalMap.get(bindName);
		if (query == null) {
			errorOccurred(RuntimePlugin.Util.gs(RuntimePlugin.Event.TEIID40078, bindName));
			return;
		}

		if (query.sql.trim().isEmpty()) {
			this.client.emptyQueryReceived();
			return;
		}
		
        sendPortalResults(maxRows, query);			
	}

	private void sendPortalResults(int maxRows, final Portal query) {
		final PreparedStatementImpl stmt = query.stmt;
        try {
        	// maxRows = 0, means unlimited.
        	if (maxRows != 0) {
        		stmt.setMaxRows(maxRows);
        	}

            this.executionFuture = stmt.submitExecute(ResultsMode.EITHER, null);
            executionFuture.addCompletionListener(new ResultsFuture.CompletionListener<Boolean>() {
        		@Override
        		public void onCompletion(ResultsFuture<Boolean> future) {
        			executionFuture = null;
                    try {
                    	ResultsFuture<Integer> result = new ResultsFuture<Integer>();
		                if (future.get()) {
		                	client.sendResults(query.sql, stmt.getResultSet(), query.prepared.columnMetadata, result, false);
		                } else {
		                	client.sendUpdateCount(query.sql, stmt.getUpdateCount());
		                	setEncoding();
		                	result.getResultsReceiver().receiveResults(1);
		                }
	        			result.addCompletionListener(new ResultsFuture.CompletionListener<Integer>() {
                        	public void onCompletion(ResultsFuture<Integer> future) {
                        		try {
									future.get();
                            		doneExecuting();
								} catch (InterruptedException e) {
									throw new AssertionError(e);
								} catch (ExecutionException e) {
									errorOccurred(e.getCause());
								}
                        	};
						});
                    } catch (Throwable e) {
                        errorOccurred(e);
                    }
        		}
			});
        } catch (SQLException e) {
        	errorOccurred(e);
        }
	}

	private void sendCursorResults(final Cursor cursor) {
		ResultsFuture<Integer> result = new ResultsFuture<Integer>();
		this.client.sendPortalResults(cursor.sql, cursor.rs, cursor.columnMetadata, result, cursor.fetchSize, true);
		result.addCompletionListener(new ResultsFuture.CompletionListener<Integer>() {
			public void onCompletion(ResultsFuture<Integer> future) {
				try {
					int rowsSent = future.get();
					if (rowsSent < cursor.fetchSize) {
						client.sendCommandComplete(cursor.sql, rowsSent);
					}
					else {
						client.sendPortalSuspended();
					}
					doneExecuting();
				} catch (InterruptedException e) {
					throw new AssertionError(e);
				} catch (ExecutionException e) {
					// EXTENSION
					// ISSUE: DVCORE-564
					// FIXED BY: Salvatore Raunich
					// DATE: 2013.07.19
					addFailedQuery(cursor.sql, e.getCause().getMessage());
					// END EXTENSION
					errorOccurred(e.getCause());
				}
			};
		});
	}
	
	private String fixSQL(String sql) {
		String modified = modifySQL(sql);
		if (modified != null && !modified.equals(sql)) {
			LogManager.logDetail(LogConstants.CTX_ODBC, "Modified Query:", modified); //$NON-NLS-1$
		}			
		return modified;
	}
	
	private String modifySQL(String sql) {
		String modified = sql;
		if (sql == null) {
			return null;
		}
		Matcher m = null;
		// selects are coming with "select\t" so using a space after "select" does not always work
		if (StringUtil.startsWithIgnoreCase(sql, "select")) { //$NON-NLS-1$
			if ((m = pkPattern.matcher(modified)).matches()) {
				return new StringBuffer("SELECT k.Name AS attname, convert(Position, short) AS attnum, TableName AS relname, SchemaName AS nspname, TableName AS relname") //$NON-NLS-1$
			          .append(" FROM SYS.KeyColumns k") //$NON-NLS-1$ 
			          .append(" WHERE ") //$NON-NLS-1$
			          .append(" UCASE(SchemaName)").append(" LIKE UCASE(").append(m.group(2)).append(")")//$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
			          .append(" AND UCASE(TableName)") .append(" LIKE UCASE(").append(m.group(1)).append(")")//$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
			          .append(" AND KeyType LIKE 'Primary'") //$NON-NLS-1$
			          .append(" ORDER BY attnum").toString(); //$NON-NLS-1$					
			}
			else if ((m = pkKeyPattern.matcher(modified)).matches()) {
				String tableName = m.group(1);
				if (tableName.endsWith("_pkey'")) { //$NON-NLS-1$
					tableName = tableName.substring(0, tableName.length()-6) + '\'';
					return "select t1.Name as attname, convert(t1.Position, short) as attnum, ic.relname, ic.relnspname as nspname, NULL " +
							"from pg_catalog.pg_class ic, sys.columns as t1, SYS.KeyColumns kc " +
							"where ic.relname = " + tableName + " AND ic.relnspname = " + m.group(2) + " AND t1.schemaname = ic.relnspname " +
							"AND t1.tablename = ic.relname AND kc.SchemaName = t1.schemaname AND kc.TableName = t1.tablename " +
							"AND kc.KeyType = 'Primary' AND kc.Name = t1.Name order by attnum";
				}
				return "SELECT NULL, NULL, NULL, NULL, NULL FROM (SELECT 1) as X WHERE 0=1"; //$NON-NLS-1$
			}
			else if ((m = fkPattern.matcher(modified)).matches()){
					String baseQuery = "SELECT PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, PKCOLUMN_NAME, FKTABLE_CAT, FKTABLE_SCHEM, "+//$NON-NLS-1$
						"FKTABLE_NAME, FKCOLUMN_NAME, KEY_SEQ, UPDATE_RULE, DELETE_RULE, FK_NAME, PK_NAME, DEFERRABILITY "+//$NON-NLS-1$
						"FROM SYS.ReferenceKeyColumns WHERE  "; //$NON-NLS-1$
				if ("f".equals(m.group(14))) { //$NON-NLS-1$
					//exported keys
					return baseQuery + "PKTABLE_NAME = " + m.group(15)+" and PKTABLE_SCHEM = "+m.group(16);//$NON-NLS-1$ //$NON-NLS-2$
				}
				//imported keys
				return baseQuery + "FKTABLE_NAME = " + m.group(15)+" and FKTABLE_SCHEM = "+m.group(16);//$NON-NLS-1$ //$NON-NLS-2$
			}
			else if (modified.equalsIgnoreCase("select version()")) { //$NON-NLS-1$
				return "SELECT 'Teiid "+ApplicationInfo.getInstance().getReleaseNumber()+"'"; //$NON-NLS-1$ //$NON-NLS-2$
			}
			else if (modified.startsWith("SELECT name FROM master..sysdatabases")) { //$NON-NLS-1$
				return "SELECT 'Teiid'"; //$NON-NLS-1$
			}
			else if (modified.equalsIgnoreCase("select db_name() dbname")) { //$NON-NLS-1$
				return "SELECT current_database()"; //$NON-NLS-1$
			}
			// EXTENSION
			// ISSUE: DV-70 
			// FIXED BY: Salvatore Raunich
			// DATE: 2013.01.16
			else if (modified.startsWith("SELECT current_setting('search_path'), version()")) { //$NON-NLS-1$
				return "SELECT '', 'Teiid "+ApplicationInfo.getInstance().getReleaseNumber()+"'"; //$NON-NLS-1$
			}
			else if (modified.equalsIgnoreCase("SELECT implementation_info_name, integer_value, character_value FROM information_schema.sql_implementation_info")) { //$NON-NLS-1$
				StringBuilder result = new StringBuilder();
				result.append("SELECT 'CATALOG NAME', '', 'Y'");
				result.append(" UNION ");
				result.append("SELECT 'COLLATING SEQUENCE', '', ''");
				result.append(" UNION ");
				result.append("SELECT 'CURSOR COMMIT BEHAVIOR', 1, ''");
				result.append(" UNION ");
				result.append("SELECT 'DATA SOURCE NAME', '', ''");
				result.append(" UNION ");
				result.append("SELECT 'DBMS NAME', 'PostgreSQL', ''");
				result.append(" UNION ");
				result.append("SELECT 'DEFAULT TRANSACTION ISOLATION', 2, ''");
				result.append(" UNION ");
				result.append("SELECT 'IDENTIFIER CASE', 3, ''");
				result.append(" UNION ");
				result.append("SELECT 'NULL COLLATION', 0, ''");
				result.append(" UNION ");
				result.append("SELECT 'SERVER NAME', '', ''");
				result.append(" UNION ");
				result.append("SELECT 'SPECIAL CHARACTERS', '', ''");
				result.append(" UNION ");
				result.append("SELECT 'TRANSACTION CAPABLE', 2, ''");
				result.append(" UNION ");
				result.append("SELECT 'DBMS VERSION', '', '09.02.0000'");
				return result.toString();
			}
			// END EXTENSION
			else if (sql.equalsIgnoreCase("select current_schema()")) { //$NON-NLS-1$
				// since teiid can work with multiple schemas at a given time
				// this call resolution is ambiguous
				return "SELECT ''";  //$NON-NLS-1$
			}
			// EXTENSION
			// ISSUE: DVCORE-2441
			// FIXED BY: Roman Pedchenko
			// DATE: 2014.10.23
			// ADOPTED FROM: https://issues.jboss.org/browse/TEIID-247, 2166, 2151, 2234, 2581, 2571, 2369, 1386, 2226, 2233, 2224, 2225, 2257
			else if (sql.equals("SELECT typinput='array_in'::regproc, typtype FROM pg_catalog.pg_type WHERE typname = $1")) { //$NON-NLS-1$
				return "SELECT substring(typname,1,1) = '_', typtype FROM pg_catalog.pg_type WHERE typname = ?"; //$NON-NLS-1$
			}
			// END EXTENSION
			// BUG: ambiguous pg_catalog table names
			// ISSUE: DVCORE-311
			// SOLUTION: add pg_catalog qualifier
			// FIXED BY: Roman Pedchenko
			// DATE: 2013.02.21
			else if (sql.equalsIgnoreCase("select oid, typbasetype from pg_type where typname = 'lo'")) {
				return "select oid, typbasetype from pg_catalog.pg_type where typname = 'lo'";  //$NON-NLS-1$
			}
			else if (Pattern.compile(Pattern.quote("left outer join pg_attrdef"), Pattern.CASE_INSENSITIVE).matcher(sql).find()) {
				modified = Pattern.compile(Pattern.quote("left outer join pg_attrdef"), Pattern.CASE_INSENSITIVE).matcher(sql).replaceAll("left outer join pg_catalog.pg_attrdef");
			}
			// END BUG
			else if (modified.toLowerCase().contains("current_setting('server_version_num')::int")) {
				return modified.toLowerCase().replace("current_setting('server_version_num')::int", PG_VERSION);
			}
			else if (modified.toLowerCase().contains("select quote_ident(table_name) as \"table\" from information_schema.tables")) {
				modified = "SELECT table_schema || '.' || table_name AS \"table\" FROM information_schema.tables WHERE table_schema != 'pg_catalog';";
			 	return modified;
			}
			else if (modified.contains("select quote_ident(column_name) as \"column\", data_type as \"type\"") && modified.contains("from information_schema.columns")) {
				// Use case-insensitive pattern matching
				Pattern pattern = Pattern.compile("where quote_ident\\(table_name\\) = '(.*?)'", Pattern.CASE_INSENSITIVE);
				Matcher matcher = pattern.matcher(modified);

				if (matcher.find()) {
					String table = matcher.group(1); // Extract the dynamic table name
					modified = "select column_name as \"column\", udt_name as \"type\" " +
							"FROM \"INFORMATION_SCHEMA.columns\" " +
							"where table_schema || '.' || table_name = '" + table + "';";
					return modified;
				}
			}
			if (modified.contains("c.relhassubclass")) {
				modified = modified.replaceAll("c.relhassubclass", "false as relhassubclass");
			}
			if ((m = columnMetadataPattern.matcher(modified)).matches()) {
				return String.format(COLUMN_METADATA_REWRITTEN_TEMPLATE, "c.relname='" + m.group(1) + "' and t1.schemaname='" + m.group(2) + "'");
			} else if ((m = columnMetadataLikePattern.matcher(modified)).matches()) {
				modified = String.format(COLUMN_METADATA_REWRITTEN_TEMPLATE, "c.relname like '" + m.group(1) + "' and t1.schemaname like '" + m.group(2) + "'");
				modified = escape(modified, "relname");
				return escape(modified, "schemaname");
			} else if ((m = columnMetadataOIDPattern.matcher(modified)).matches()) {
				return String.format(COLUMN_METADATA_REWRITTEN_TEMPLATE, "c.oid=" + m.group(1));
			}
			modified = escape(modified, "nspname");
			modified = escape(modified, "relname");
			// EXTENSION
			// ISSUE: DVCORE-409 
			// FIXED BY: Salvatore Raunich
			// DATE: 2013.05.13
			// convert schema and table names in the query in lower case
			if (modified.contains("n.nspname AS \"TABLE_SCHEMA\"")) {
				modified = modified.replaceAll("n\\.nspname AS \"TABLE_SCHEMA\"", "LCASE\\(n\\.nspname\\) AS \"TABLE_SCHEMA\"");
			}
			if (modified.contains("c.relname AS \"TABLE_NAME\"")) {
				modified = modified.replaceAll("c\\.relname AS \"TABLE_NAME\"", "LCASE\\(c\\.relname\\) AS \"TABLE_NAME\"");
			}
			if (modified.contains("T.schemaname as \"TABLE_SCHEMA\"")) {
				modified = modified.replaceAll("T.schemaname", "LCASE\\(T.schemaname\\)");
				modified = modified.replaceAll("NS.nspname", "LCASE\\(NS.nspname\\)");
			}
			if (modified.contains("T.tablename as \"TABLE_NAME\"")) {
				modified = modified.replaceAll("T.tablename", "LCASE\\(T.tablename\\)");
				modified = modified.replaceAll("C.relname ", "LCASE\\(C.relname\\) ");
				modified = modified.replaceAll("C.relname=", "LCASE\\(C.relname\\)=");
			}
			if (modified.contains("where \"TABLE_NAME\"=$1")) {
				modified = modified.replaceAll("where \"TABLE_NAME\"=\\$1", "where LCASE\\(TABLE_NAME\\)=LCASE\\(\\$1\\)");
			}
			if (modified.contains("where \"TABLE_SCHEMA\"=$1 and \"TABLE_NAME\"=$2")) {
				modified = modified.replaceAll("where \"TABLE_SCHEMA\"=\\$1 and \"TABLE_NAME\"=\\$2", "where LCASE\\(TABLE_SCHEMA\\)=LCASE\\(\\$1\\) and LCASE\\(TABLE_NAME\\)=LCASE\\(\\$2\\)");
			}
			// EXTENSION
			// ISSUE: DV-70,DVCORE-589
			// FIXED BY: Salvatore Raunich
			// DATE: 2013.01.16,2013.08.07
			// replace 'f' char value with the corresponding boolean value
			if (modified.contains("A.attisdropped='f')")) {
				return modified.replaceAll("A.attisdropped='f'", "A.attisdropped='false'");
			}			
			// END EXTENSION
			
		}
		else if (sql.equalsIgnoreCase("show max_identifier_length")){ //$NON-NLS-1$
			// EXTENSION
			// ISSUE: DVCORE-2527
			// FIXED BY: Dmitry Pogorelov / Julian Joeris
			// DATE: 2015.02.10, 2015.02.27
			// return "select 63"; //$NON-NLS-1$
			return "SELECT max(length(Name)) FROM SYS.Tables";
			// END EXTENSION
		}
		// EXTENSION
    	// ISSUE: REPIN-27 
    	// FIXED BY: Salvatore Raunich
    	// DATE: 2013.03.07
		else if ((m = setDelimiterToPattern.matcher(sql)).matches()) {
			// nothing to fix... just forward the command
			return sql;
    	}
		// END EXTENSION
		else if ((m = setPattern.matcher(sql)).matches()) {
			return "SET " + m.group(1) + " " + m.group(2); //$NON-NLS-1$ //$NON-NLS-2$
		}
		else if (modified.equalsIgnoreCase("BEGIN")) { //$NON-NLS-1$
			return "START TRANSACTION"; //$NON-NLS-1$
		}
		// EXTENSION
		// ISSUE: DV-70
		// FIXED BY: Salvatore Raunich
		// DATE: 2013.01.16
		else if ((m = declareCursorPGNPPattern.matcher(sql)).matches()) {
			return "DECLARE CURSOR " + m.group(1) + " WITH HOLD FOR " + m.group(2);
		}
		else if ((m = fetchPatternOriginalPGNP.matcher(sql)).matches()) {
			return "FETCH " + m.group(1) + " IN " + m.group(2);
		}
		else if (modified.equalsIgnoreCase("START TRANSACTION ISOLATION LEVEL READ COMMITTED")) { //$NON-NLS-1$
			return "START TRANSACTION"; //$NON-NLS-1$
		}
		// END EXTENSION
		else if ((m = rollbackPattern.matcher(modified)).matches()) {
			return "ROLLBACK"; //$NON-NLS-1$
		}					
		else if ((m = savepointPattern.matcher(sql)).matches()) {
			return "SELECT 0 from (select 1) x where 1=0"; //$NON-NLS-1$
		}
		else if ((m = releasePattern.matcher(sql)).matches()) {
			return "SELECT 0 from (select 1) x where 1=0"; //$NON-NLS-1$
		}

		String oldDelimiter = ";";
		Object delimObject = connection.getExecutionProperties().get("DELIMITER");
		if (delimObject != null) {
			oldDelimiter = (String) delimObject;
		}
		String delimiterToSet = setTempDefaultDelimiter(modified, oldDelimiter);
		for (int i = 0; i < modified.length(); i++) {
			switch (modified.charAt(i)) {
			case ':':
			case '~':
				ScriptReader reader = new ScriptReader(modified, delimiterToSet.charAt(0));
				reader.setRewrite(true);
				try {
					return reader.readStatement();
				} catch (IOException e) {
					//can't happen
				}
			}
		}
		if (!(m = setDelimiterToPattern.matcher(modified)).matches()) {
			connection.getExecutionProperties().setProperty("DELIMITER", oldDelimiter);
		}

		return modified;
	}

	private String escape(String modified, String col) {
		if (Pattern.compile(col + " like (E?(?:'[^']*')+)", Pattern.CASE_INSENSITIVE).matcher(modified).find()) {
			modified = Pattern.compile(col + " like (E?(?:'[^']*')+)((\\s+)|\\))(?!escape)", Pattern.CASE_INSENSITIVE).matcher(modified).replaceAll(col  + " like $1 escape '\\\\' $2");
		}

		return modified;
	}

	@Override
	public void executeQuery(String query) {
		if (beginExecution()) {
			errorOccurred("Awaiting asynch result"); //$NON-NLS-1$
			ready();
			return;
		}
		//46.2.3 Note that a simple Query message also destroys the unnamed portal.
		Portal p = this.portalMap.remove(UNNAMED);
		if (p != null) {
			//closePortal(p);
		}
		this.preparedMap.remove(UNNAMED);
		query = query.trim();
		if (query.length() == 0 || ";".equals(query)) {
    		client.emptyQueryReceived();
    		ready();
    	}
		// EXTENSION
		// ISSUE: REPIN-27 
		// FIXED BY: Salvatore Raunich
		// DATE: 2013.03.07
		String oldDelimiter = ";";
		Object delimObject = connection.getExecutionProperties().get("DELIMITER");
		if (delimObject != null) {
			oldDelimiter = (String) delimObject;
		}
		String delimiterToSet = setTempDefaultDelimiter(query, oldDelimiter);
        QueryWorkItem r = new QueryWorkItem(query, delimiterToSet.charAt(0));
//        QueryWorkItem r = new QueryWorkItem(query);
        // END EXTENSION
		r.run();
		// EXTENSION
		// ISSUE: REPIN-27 
		// FIXED BY: Salvatore Raunich
		// DATE: 2013.03.07
		Matcher m = null;
		if (!(m = setDelimiterToPattern.matcher(query)).matches()) {
			connection.getExecutionProperties().setProperty("DELIMITER", oldDelimiter);
		}
		// END EXTENSION
	}

	// EXTENSION
	// ISSUE: REPIN-27 
	// FIXED BY: Salvatore Raunich
	// DATE: 2013.03.07
    private String setTempDefaultDelimiter(String query, String oldDelimiter) {
    	Matcher m = null;
    	String defaultDelimiter = ";";
    	if ((m = fullCursorDeclarationPattern.matcher(query)).matches() || 
    			(m = fullFetchDeclarationPattern.matcher(query)).matches() || 
    			(m = beginDeclarationPattern.matcher(query)).matches()) {
    		connection.getExecutionProperties().setProperty("DELIMITER", defaultDelimiter);
    		return defaultDelimiter;
    	}
    	return oldDelimiter;
    }
    // END EXTENSION

	private boolean beginExecution() {
		if (this.executionFuture != null) {
			return true;
		}
		this.executing = true;
		return false;
	}
	
	public boolean isExecuting() {
		return executing;
	}
	
	public boolean isErrorOccurred() {
		return errorOccurred;
	}

	@Override
	public void getParameterDescription(String prepareName) {
		if (prepareName == null || prepareName.length() == 0) {
			prepareName  = UNNAMED;
		}		
		Prepared query = this.preparedMap.get(prepareName);
		if (query == null) {
			errorOccurred(RuntimePlugin.Util.gs(RuntimePlugin.Event.TEIID40079, prepareName));
		}
		else {
			try {
				// The response is a ParameterDescription message describing the parameters needed by the statement,
				this.client.sendParameterDescription(query.stmt.getParameterMetaData(), query.paramType);

				// followed by a RowDescription message describing the rows that will be returned when the statement
				// is eventually executed (or a NoData message if the statement will not return rows).
				this.client.sendResultSetDescription(query.columnMetadata);
			} catch (SQLException e) {
				// EXTENSION
				// ISSUE: DVCORE-564
				// FIXED BY: Salvatore Raunich
				// DATE: 2013.07.19
				addFailedQuery(query.sql, e.getMessage());
				// END EXTENSION
				errorOccurred(e);
			}
		}
	}
	
	private void errorOccurred(String error) {
		this.client.errorOccurred(error);
		synchronized (this) {
			this.errorOccurred = true;
			doneExecuting();
		}
	}
	
	// EXTENSION
	// ISSUE: DVCORE-564
	// FIXED BY: Salvatore Raunich
	// DATE: 2013.07.19
	private void addFailedQuery(String sql, String failureReason) {
		RequestID reqId = new RequestID(-100, -100); 
		DVCaller.call(16, reqId, sql, failureReason);		
	}
	// END EXTENSION
	
	public void errorOccurred(Throwable error) {
		this.client.errorOccurred(error);
		synchronized (this) {
			this.errorOccurred = true;
			doneExecuting();
		}
	}

	@Override
	public void getResultSetMetaDataDescription(String bindName) {
		if (bindName == null || bindName.length() == 0) {
			bindName  = UNNAMED;
		}		
		Portal query = this.portalMap.get(bindName);
		if (query == null) {
			errorOccurred(RuntimePlugin.Util.gs(RuntimePlugin.Event.TEIID40078, bindName));
		}
		else {
			this.client.sendResultSetDescription(query.prepared.columnMetadata);
		}
	}

	@Override
	public void sync() {
		ready();
	}
	
	protected void doneExecuting() {
		executing = false;
	}
	
	private void ready() {
		boolean inTxn = false;
		boolean failedTxn = false;
		try {
			if (!this.connection.getAutoCommit()) {
				inTxn = true;
			}
		} catch (SQLException e) {
			failedTxn = true;
		}
		synchronized (this) {
			this.errorOccurred = false;
		}
		this.client.ready(inTxn, failedTxn);
	}
	
	@Override
	public void cancel() {
		// TODO Auto-generated method stub
	}

	@Override
	public void closeBoundStatement(String bindName) {
		if (bindName == null || bindName.length() == 0) {
			bindName  = UNNAMED;
		}
		Portal query = this.portalMap.remove(bindName);
		if (query == null) {
			errorOccurred(RuntimePlugin.Util.gs(RuntimePlugin.Event.TEIID40078, bindName));
		}
		else {
			try {
				if (this.connection.getAutoCommit()) {
					// After checking the pg's client code I do not see it send a
					// close of the Prepare stmt as per the wire protocol, it only sends
					// bound close. Since it also have issue with
					// http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1007690&group_id=1000125
					// treating the prepare and bound as same for now.
					closePreparedStatement(bindName);
				}
			} catch (SQLException e) {
				closePreparedStatement(bindName);
			}
		}
	}

	@Override
	public void closePreparedStatement(String preparedName) {
		if (preparedName == null || preparedName.length() == 0) {
			preparedName  = UNNAMED;
		}		
		Prepared query = this.preparedMap.remove(preparedName);
		if (query != null) {
			// Close all the bound messages off of this prepared
			// TODO: can there be more than one?
			this.portalMap.remove(preparedName);

			try {
				query.stmt.close();
				this.client.statementClosed();
			} catch (SQLException e) {
				errorOccurred(RuntimePlugin.Util.gs(RuntimePlugin.Event.TEIID40080, preparedName));
			}
		}
	}

	@Override
	public void terminate() {

		for (Portal p: this.portalMap.values()) {
			try {
				p.stmt.close();
			} catch (SQLException e) {
				//ignore
			}
		}

		for (Prepared p:this.preparedMap.values()) {
			try {
				p.stmt.close();
			} catch (SQLException e) {
				//ignore
			}
		}

		try {			
			if (this.connection != null) {
				if (!this.connection.getAutoCommit()) {
					this.connection.rollback(false);
				}
				this.connection.close();
			}
		} catch (SQLException e) {
			//ignore
		}
		this.client.terminated();
	}	
	
	@Override
	public void flush() {
		this.client.flush();
	}
	
	@Override
	public void functionCall(int oid) {
		errorOccurred(RuntimePlugin.Util.gs(RuntimePlugin.Event.TEIID40081));
	}
	
	@Override
	public void sslRequest() {
		this.client.sendSslResponse();
	}
	
	private void setEncoding() {
		String encoding = getEncoding();
		if (encoding != null) {
			//this may be unnecessary
			this.client.setEncoding(encoding, false);
		}
	}

	public String getEncoding() {
		return this.connection.getExecutionProperty(PgBackendProtocol.CLIENT_ENCODING);
			}

    private final class QueryWorkItem implements Runnable {
		private final ScriptReader reader;
		String sql;

		// EXTENSION
		// ISSUE: REPIN-27 
		// FIXED BY: Salvatore Raunich
		// DATE: 2013.03.07
//		private QueryWorkItem(String query) {
//			this.reader = new ScriptReader(new StringReader(query));		
		private QueryWorkItem(String query, char delimiter) {
			this.reader = new ScriptReader(query, delimiter);
		// END EXTENSION
		}
		
		private void done(Throwable error) {
			if (error != null) {
				errorOccurred(error);
			} else {
				doneExecuting();
			}
			ready();
		}

		@Override
		public void run() {
			try {
				if (sql == null) {
					sql = reader.readStatement();
				}
		        while (sql != null) {
		            try {
		    			
		            	ResultsFuture<Integer> results = new ResultsFuture<Integer>();
		    			results.addCompletionListener(new ResultsFuture.CompletionListener<Integer>() {
		                	public void onCompletion(ResultsFuture<Integer> future) {
		                		try {		                			
		    						future.get();
		    		                sql = reader.readStatement();
		    					} catch (InterruptedException e) {
		    						throw new AssertionError(e);
		    					} catch (IOException e) {
		    						done(e);
		    						return;
		    					} catch (ExecutionException e) {
		    						Throwable cause = e;
		    						while (cause instanceof ExecutionException && cause.getCause() != null && cause != cause.getCause()) {
		    							cause = cause.getCause();
		    						}
		    						done(cause);
		    						return;
		    					}
		            			QueryWorkItem.this.run(); //continue processing
		                	};
		    			});	
		    			
		    			if (isErrorOccurred()) {
		    				if (!connection.getAutoCommit()) {
		    					connection.rollback(false);
		    				}
		    				break;
		    			}
		    			
		            	Matcher m = null;
		    	        if ((m = cursorSelectPattern.matcher(sql)).matches()){
				            boolean scroll = false;
				            if (m.group(2) != null && m.group(3) == null ) {
					            scroll = true;
				            }
		    				cursorExecute(m.group(1), fixSQL(m.group(4)), results, scroll);
		    			}
		    	        // EXTENSION
						// ISSUE: DV-70, DVCORE-2978
						// FIXED BY: Salvatore Raunich, Dmitry Pogorelov
						// DATE: 2013.01.16, 2016.02.20
		    	        else if ((m = declareCursorPGNPPattern.matcher(sql)).matches()){
		    	        	cursorExecute(m.group(1), fixSQL(m.group(4)), results, false);
		    	        }
						// END EXTENSION
		    			else if ((m = fetchPattern.matcher(sql)).matches()){
		    				cursorFetch(m.group(2), Integer.parseInt(m.group(1)), results);
		    			}
		    	        // EXTENSION
						// ISSUE: DV-70
						// FIXED BY: Salvatore Raunich
						// DATE: 2013.01.16
		    			else if ((m = fetchPatternPGNPNoQuotes.matcher(fixSQL(sql))).matches()){
		    				cursorFetch(m.group(2), Integer.parseInt(m.group(1)), results);
		    			}
						// END EXTENSION
		    			else if ((m = movePattern.matcher(sql)).matches()){
		    				cursorMove(m.group(2), Integer.parseInt(m.group(1)), results);
		    			}
		    			else if ((m = closePattern.matcher(sql)).matches()){
		    				cursorClose(m.group(1));
		    				results.getResultsReceiver().receiveResults(1);
		    			}
		    			else if ((m = deallocatePattern.matcher(sql)).matches()) { 
		    				String plan_name = m.group(1);
		    				plan_name = SQLParserUtil.normalizeId(plan_name);
		    				closePreparedStatement(plan_name);
		    				client.sendCommandComplete("DEALLOCATE", null); //$NON-NLS-1$
		    				results.getResultsReceiver().receiveResults(1);
		    			}
		    			else {
		    				sqlExecute(sql, results);
		    			}
		                return; //wait for the execution to finish
		            } catch (SQLException e) {
		            	done(e);
		            	return;
		            } 
		        }
			} catch(IOException e) {
				done(e);
				return;
			}
			done(null);
		}
	}

	/**
	 * @see PgCatalogMetadataStore add_pg_attribute for mod calculation
	 */
	private List<PgColInfo> getPgColInfo(ResultSetMetaData meta)
			throws SQLException {
		if (meta == null) {
			return null;
		}
		int columns = meta.getColumnCount();
		final ArrayList<PgColInfo> result = new ArrayList<PgColInfo>(columns);
		for (int i = 1; i <= columns; i++) {
			final PgColInfo info = new PgColInfo();
			// EXTENSION
			// ISSUE: DV-70
			// FIXED BY: Salvatore Raunich
			// DATE: 2013.01.16
//			info.name = meta.getColumnLabel(i).toLowerCase();
			info.name = meta.getColumnLabel(i);
			// END EXTENSION			
			info.type = meta.getColumnType(i);
			String typeName = meta.getColumnTypeName(i);
			info.type = convertType(info.type, typeName);
			info.precision = meta.getColumnDisplaySize(i);
			if (info.type == PG_TYPE_NUMERIC) {
				info.mod = 4+ 65536*Math.min(32767, meta.getPrecision(i))+Math.min(32767, meta.getScale(i));
			} else if (info.type == PG_TYPE_BPCHAR || info.type == PG_TYPE_VARCHAR){
				info.mod = (int) Math.min(Integer.MAX_VALUE, 4+(long)meta.getColumnDisplaySize(i));
			} else {
				info.mod = -1;
			}
			String name = meta.getColumnName(i);
			String table = meta.getTableName(i);
			String schema = meta.getSchemaName(i);
			if (schema != null) {
				final PreparedStatementImpl ps = this.connection.prepareStatement("select attrelid, attnum, typoid " +
						"from (" + PgCatalogMetadataStore.MATPG_RELATT + ") as m where attname = ? and relname = ? and nspname = ?");
				/*final PreparedStatementImpl ps = this.connection.prepareStatement("select " //$NON-NLS-1$
						+ "pg_catalog.getOid(SYS.Columns.TableUID), " //$NON-NLS-1$
						+ "cast(SYS.Columns.Position as short), " //$NON-NLS-1$
						+ "cast((select p.value from SYS.Properties p where p.name = 'pg_type:oid' and p.uid = SYS.Columns.uid) as integer) " //$NON-NLS-1$
						+ "from SYS.Columns where Name = ? and TableName = ? and SchemaName = ?");*/ //$NON-NLS-1$
				try {
					ps.setString(1, name);
					ps.setString(2, table);
					ps.setString(3, schema);
					ResultSet rs = ps.executeQuery();
					if (rs.next()) {
						info.reloid = rs.getInt(1);
						info.attnum = rs.getShort(2);
						int specificType = rs.getInt(3);
						if (!rs.wasNull()) {
							info.type = specificType;
						}
					}
				} finally {
					ps.close();
				}
			}
			result.add(info);
		}
		return result;
	}  
    
	/**
     * Represents a PostgreSQL Prepared object.
     */
    static class Prepared {

    	public Prepared (String name, String sql, PreparedStatementImpl stmt, int[] paramType, List<PgColInfo> columnMetadata) {
    		this.name = name;
    		this.sql = sql;
    		this.stmt = stmt;
    		this.paramType = paramType;
		    this.columnMetadata = columnMetadata;
    	}
    	
        /**
         * The object name.
         */
        String name;

        /**
         * The SQL statement.
         */
        String sql;

        /**
         * The prepared statement.
         */
        PreparedStatementImpl stmt;

        /**
         * The list of parameter types (if set).
         */
        int[] paramType;

		/**
		 * calculated column metadata
		 */
		final List<PgColInfo> columnMetadata;
    }

    /**
     * Represents a PostgreSQL Portal object.
     */
    static class Portal {

    	public Portal(String name, Prepared prepared, String sql, PreparedStatementImpl stmt, int[] resultColumnformat) {
    		this.name = name;
		    this.prepared = prepared;
    		this.sql = sql;
    		this.stmt = stmt;
    		this.resultColumnFormat = resultColumnformat;
    	}
        /**
         * The portal name.
         */
        String name;

        /**
         * The SQL statement.
         */
        String sql;

        /**
         * The format used in the result set columns (if set).
         */
        int[] resultColumnFormat;

	    final Prepared prepared;

        /**
         * The prepared statement.
         */
        PreparedStatementImpl stmt;
    }

    static class Cursor extends Prepared {
    	ResultSetImpl rs;
    	int fetchSize = 1000;

    	public Cursor (String name, String sql, PreparedStatementImpl stmt, int[] paramType, ResultSetImpl rs, List<PgColInfo> colMetadata) {
    		super(name, sql, stmt, paramType, colMetadata);
    		this.rs = rs;
    	}
    }    

}
