/*
 * 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-3707
// https://issues.jboss.org/browse/TEIID-3781
// https://issues.jboss.org/browse/TEIID-3654
// https://issues.jboss.org/browse/TEIID-2205
// https://issues.jboss.org/browse/TEIID-2086
// https://issues.jboss.org/browse/TEIID-5146
// https://issues.jboss.org/browse/TEIID-4507
// https://issues.jboss.org/browse/TEIID-4504
// https://issues.jboss.org/browse/TEIID-5474
// https://issues.jboss.org/browse/TEIID-3515
// https://issues.jboss.org/browse/TEIID-2471
// https://issues.jboss.org/browse/TEIID-2423 (partially, only the first commit)
// https://issues.jboss.org/browse/TEIID-3271
// https://issues.jboss.org/browse/TEIID-2507
// https://issues.redhat.com/browse/TEIID-5969
// https://issues.redhat.com/browse/TEIID-2384
// https://issues.redhat.com/browse/TEIID-2439
// https://issues.redhat.com/browse/TEIID-2577
package org.teiid.runtime;

import com.datavirtuality.dv.core.services.DVServices;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.mockito.Mockito;
import org.postgresql.Driver;
import org.teiid.adminapi.Model.Type;
import org.teiid.adminapi.impl.ModelMetaData;
import org.teiid.core.types.ClobImpl;
import org.teiid.core.types.ClobType;
import org.teiid.core.types.DataTypeManager;
import org.teiid.core.types.InputStreamFactory;
import org.teiid.core.util.ObjectConverterUtil;
import org.teiid.core.util.UnitTestUtil;
import org.teiid.deployers.VirtualDatabaseException;
import org.teiid.jdbc.TeiidDriver;
import org.teiid.jdbc.TeiidSQLException;
import org.teiid.language.Command;
import org.teiid.language.QueryExpression;
import org.teiid.metadata.Column;
import org.teiid.metadata.MetadataFactory;
import org.teiid.metadata.RuntimeMetadata;
import org.teiid.metadata.Table;
import org.teiid.query.processor.FakeUsersHandler;
import org.teiid.query.util.DVUsersHandlerIntf;
import org.teiid.runtime.EmbeddedServer.ConnectionFactoryProvider;
import org.teiid.translator.*;
import org.teiid.transport.SocketConfiguration;
import org.teiid.transport.WireProtocol;

import javax.transaction.*;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.InetSocketAddress;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Properties;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.logging.Level;

import static org.junit.jupiter.api.Assertions.*;

@SuppressWarnings("nls")
public class TestEmbeddedServer {
	@BeforeEach
	void setUp() {
		DVServices.register(DVUsersHandlerIntf.class, new FakeUsersHandler());
	}
	
	private static final class FakeTranslator extends
 	    ExecutionFactory<AtomicInteger, Object> {

			private boolean batch;

			public FakeTranslator(boolean batch) {
				this.batch = batch;
			}

			@Override
			public Object getConnection(AtomicInteger factory)
							throws TranslatorException {
				return factory.incrementAndGet();
			}

			@Override
			public void closeConnection(Object connection, AtomicInteger factory) {

			}

			@Override
			public boolean supportsBulkUpdate() {
				return true;
			}

			@Override
			public void getMetadata(MetadataFactory metadataFactory, Object conn)
							throws TranslatorException {
				assertEquals(conn, Integer.valueOf(1));
				Table t = metadataFactory.addTable("my-table");
				t.setSupportsUpdate(true);
				Column c = metadataFactory.addColumn("my-column", TypeFacility.RUNTIME_NAMES.STRING, t);
				c.setUpdatable(true);
			}

			@Override
			public ResultSetExecution createResultSetExecution(
							QueryExpression command, ExecutionContext executionContext,
							RuntimeMetadata metadata, Object connection)
							throws TranslatorException {
				ResultSetExecution rse = new ResultSetExecution() {

					@Override
	                public void execute() throws TranslatorException {

			        }

			        @Override
	                public void close() {

			        }

			        @Override
	                public void cancel() throws TranslatorException {

			        }

			        @Override
			        public List<?> next() throws TranslatorException, DataNotAvailableException {
						return null;
					}
 	            };
				return rse;
			}

			@Override
			public UpdateExecution createUpdateExecution(Command command,
							ExecutionContext executionContext,
							RuntimeMetadata metadata, Object connection)
							throws TranslatorException {
				UpdateExecution ue = new UpdateExecution() {

			    @Override
	            public void execute() throws TranslatorException {

			    }

			    @Override
	            public void close() {

			    }

			    @Override
	            public void cancel() throws TranslatorException {

			    }

			    @Override
	            public int[] getUpdateCounts() throws DataNotAvailableException,
	                            TranslatorException {
			        if (!batch) {
			            return new int[] {2};
			        }
					return new int[] {1, 1, -1, 1, 1, 1, -1, 1, 1, 1, -1, 1, 1, 1, -1, 1};
			    }
 	        };
			return ue;
		}
	}

	public static final class MockTransactionManager implements TransactionManager {
		ThreadLocal<Transaction> txns = new ThreadLocal<Transaction>();
		List<Transaction> txnHistory = new ArrayList<Transaction>();

		@Override
		public Transaction suspend() throws SystemException {
			Transaction result = txns.get();
			txns.remove();
			return result;
		}

		@Override
		public void setTransactionTimeout(int seconds) throws SystemException {
		}

		@Override
		public void setRollbackOnly() throws IllegalStateException, SystemException {
			Transaction result = txns.get();
			if (result == null) {
				throw new IllegalStateException();
			}
			result.setRollbackOnly();
		}

		@Override
		public void rollback() throws IllegalStateException, SecurityException,
				SystemException {
			Transaction t = checkNull(false);
			txns.remove();
			t.rollback();
		}

		@Override
		public void resume(Transaction tobj) throws InvalidTransactionException,
				IllegalStateException, SystemException {
			checkNull(true);
			txns.set(tobj);
		}

		private Transaction checkNull(boolean isNull) {
			Transaction t = txns.get();
			if ((!isNull && t == null) || (isNull && t != null)) {
				throw new IllegalStateException();
			}
			return t;
		}

		@Override
		public Transaction getTransaction() throws SystemException {
			return txns.get();
		}

		@Override
		public int getStatus() throws SystemException {
			Transaction t = txns.get();
			if (t == null) {
				return Status.STATUS_NO_TRANSACTION;
			}
			return t.getStatus();
		}

		@Override
		public void commit() throws RollbackException, HeuristicMixedException,
				HeuristicRollbackException, SecurityException,
				IllegalStateException, SystemException {
			Transaction t = checkNull(false);
			txns.remove();
			t.commit();
		}

		@Override
		public void begin() throws NotSupportedException, SystemException {
			checkNull(true);
			Transaction t = Mockito.mock(Transaction.class);
			txnHistory.add(t);
			txns.set(t);
		}
	}

	EmbeddedServer es;
	
	@BeforeEach public void setup() {
		es = new EmbeddedServer();
	}
	
	@AfterEach public void teardown() {
		es.stop();
	}
	
	@Test public void testDeploy() throws Exception {
		EmbeddedConfiguration ec = new EmbeddedConfiguration();
		ec.setUseDisk(false);
		es.start(ec);

		es.addTranslator("y", new FakeTranslator(false));
		final AtomicInteger counter = new AtomicInteger();
		ConnectionFactoryProvider<AtomicInteger> cfp = new ConnectionFactoryProvider<AtomicInteger>() {
			@Override
			public AtomicInteger getConnectionFactory()
					throws TranslatorException {
				return counter;
			}
		};
		
		es.addConnectionFactoryProvider("z", cfp);
		
		ModelMetaData mmd = new ModelMetaData();
		mmd.setName("my-schema");
		mmd.addSourceMapping("x", "y", "z");

		ModelMetaData mmd1 = new ModelMetaData();
		mmd1.setName("virt");
		mmd1.setModelType(Type.VIRTUAL);
		mmd1.setSchemaSourceType("ddl");
		mmd1.setSchemaText("create view \"my-view\" OPTIONS (UPDATABLE 'true') as select * from \"my-table\"");

		es.deployVDB("test", mmd, mmd1);
		
		TeiidDriver td = es.getDriver();
		Connection c = td.connect("jdbc:datavirtuality:test", null);
		Statement s = c.createStatement();
		ResultSet rs = s.executeQuery("select * from \"my-view\"");
		assertFalse(rs.next());
		assertEquals("my-column", rs.getMetaData().getColumnLabel(1));
		
		s.execute("update \"my-view\" set \"my-column\" = 'a'");
		assertEquals(2, s.getUpdateCount());
	}

	@Test public void testBatchedUpdate() throws Exception {
		EmbeddedConfiguration ec = new EmbeddedConfiguration();

		ec.setUseDisk(false);
		es.bufferService.setProcessorBatchSize(1);
		es.start(ec);

		es.addTranslator("y", new FakeTranslator(true));
		final AtomicInteger counter = new AtomicInteger();
		ConnectionFactoryProvider<AtomicInteger> cfp = new EmbeddedServer.SimpleConnectionFactoryProvider<AtomicInteger>(counter);

		es.addConnectionFactoryProvider("z", cfp);

		ModelMetaData mmd = new ModelMetaData();
		mmd.setName("my-schema");
		mmd.addSourceMapping("x", "y", "z");
		es.deployVDB("test", mmd);

		Connection c = es.getDriver().connect("jdbc:datavirtuality:test", null);
		PreparedStatement ps = c.prepareStatement("insert into \"my-table\" values (?)");
		for (int i = 0; i < 16; i++) {
			ps.setString(1, "a");
			ps.addBatch();
		}
		int[] result = ps.executeBatch();
		assertArrayEquals(new int[] {1, 1, -1, 1, 1, 1, -1, 1, 1, 1, -1, 1, 1, 1, -1, 1}, result);
	}

	// TODO the test will be introduced in scope of TEIID-3097 issue
	/*@Test(expected=VirtualDatabaseException.class)
 	public void testInvalidName() throws Exception {
		es.start(new EmbeddedConfiguration());
		ModelMetaData mmd1 = new ModelMetaData();
		mmd1.setName("virt.1");
		mmd1.setModelType(Type.VIRTUAL);
		mmd1.setSchemaSourceType("ddl");
		mmd1.setSchemaText("create view \"my-view\" as select 1");
		es.deployVDB("x", mmd1);
	}*/

	@Test public void testXMLDeploy() throws Exception {
		es.start(new EmbeddedConfiguration());
		es.deployVDB(new ByteArrayInputStream("<vdb name=\"test\" version=\"1\"><model name=\"test\" type=\"VIRTUAL\"><metadata type=\"DDL\"><![CDATA[CREATE VIEW helloworld as SELECT 'HELLO WORLD';]]> </metadata></model></vdb>".getBytes()));
		ResultSet rs =es.getDriver().connect("jdbc:datavirtuality:test", null).createStatement().executeQuery("select * from helloworld");
		rs.next();
		assertEquals("HELLO WORLD", rs.getString(1));
	}

	@Test public void testXMLDeployFails() throws Exception {
		assertThrows(VirtualDatabaseException.class, () -> {
			es.start(new EmbeddedConfiguration());
			es.deployVDB(new ByteArrayInputStream("<vdb name=\"test\" version=\"1\"><model name=\"test\" type=\"VIRTUAL\"><metadata type=\"DDL\"><![CDATA[CREATE VIEW helloworld as SELECT 'HELLO WORLD';]]> </metadata></model><translator name=\"foo\" type=\"h2\"></translator></vdb>".getBytes()));
		});
	}

	@Test public void testXMLDeployWithVDBImport() throws Exception {
		es.start(new EmbeddedConfiguration());
		es.deployVDB(new ByteArrayInputStream("<vdb name=\"test\" version=\"1\"><model name=\"test\" type=\"VIRTUAL\"><metadata type=\"DDL\"><![CDATA[CREATE VIEW helloworld as SELECT 'HELLO WORLD';]]> </metadata></model></vdb>".getBytes()));
		es.deployVDB(new ByteArrayInputStream("<vdb name=\"importer\" version=\"1\"><import-vdb name=\"test\" version=\"1\"/></vdb>".getBytes()));
		ResultSet rs =es.getDriver().connect("jdbc:datavirtuality:importer", null).createStatement().executeQuery("select * from helloworld");
		rs.next();
		assertEquals("HELLO WORLD", rs.getString(1));
		es.deployVDB(new ByteArrayInputStream("<vdb name=\"importer1\" version=\"1\"><import-vdb name=\"importer\" version=\"1\"/></vdb>".getBytes()));
		rs =es.getDriver().connect("jdbc:datavirtuality:importer1", null).createStatement().executeQuery("select * from helloworld");
		rs.next();
		assertEquals("HELLO WORLD", rs.getString(1));
	}

	@Test public void testRemoteJDBCTrasport() throws Exception {
		SocketConfiguration s = new SocketConfiguration();
		InetSocketAddress addr = new InetSocketAddress(0);
		s.setBindAddress(addr.getHostName());
		s.setPortNumber(addr.getPort());
		s.setProtocol(WireProtocol.teiid);
		EmbeddedConfiguration config = new EmbeddedConfiguration();
		config.addTransport(s);
		es.start(config);
		es.deployVDB(new ByteArrayInputStream("<vdb name=\"test\" version=\"1\"><model name=\"test\" type=\"VIRTUAL\"><metadata type=\"DDL\"><![CDATA[CREATE VIEW helloworld as SELECT 'HELLO WORLD';]]> </metadata></model></vdb>".getBytes()));
		Connection conn = null;
		try {
			TeiidDriver driver = new TeiidDriver();
			conn = driver.connect("jdbc:datavirtuality:test@mm://"+addr.getHostName()+":"+es.transports.get(0).getPort(), null);
			ResultSet rs = conn.createStatement().executeQuery("select * from helloworld");
			rs.next();
			assertEquals("HELLO WORLD", rs.getString(1));
		} finally {
			conn.close();
			if (conn != null) {
				conn.close();
			}
		}
	}

	@Test public void testRemoteODBCTrasport() throws Exception {
		SocketConfiguration s = new SocketConfiguration();
		InetSocketAddress addr = new InetSocketAddress(0);
		s.setBindAddress(addr.getHostName());
		s.setPortNumber(addr.getPort());
		s.setProtocol(WireProtocol.pg);
		EmbeddedConfiguration config = new EmbeddedConfiguration();
		config.addTransport(s);
		es.start(config);
		es.deployVDB(new ByteArrayInputStream("<vdb name=\"test\" version=\"1\"><model name=\"test\" type=\"VIRTUAL\"><metadata type=\"DDL\"><![CDATA[CREATE VIEW helloworld as SELECT 'HELLO WORLD';]]> </metadata></model></vdb>".getBytes()));
		Connection conn = null;
		try {
			Driver d = new Driver();
			Properties p = new Properties();
			p.setProperty("user", "testuser");
			p.setProperty("password", "testpassword");

			conn = d.connect("jdbc:postgresql://"+addr.getHostName()+":"+es.transports.get(0).getPort()+"/test", p);
			ResultSet rs = conn.createStatement().executeQuery("select * from helloworld");
			rs.next();
			assertEquals("HELLO WORLD", rs.getString(1));
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}
	
	@Test public void testDeploymentError() throws Exception {
		assertThrows(VirtualDatabaseException.class, () -> {
			EmbeddedConfiguration ec = new EmbeddedConfiguration();
			ec.setUseDisk(false);
			es.start(ec);

			ModelMetaData mmd1 = new ModelMetaData();
			mmd1.setName("virt");
			mmd1.setModelType(Type.VIRTUAL);
			mmd1.setSchemaSourceType("ddl");
			mmd1.setSchemaText("create view \"my-view\" OPTIONS (UPDATABLE 'true') as select * from \"my-table\"");

			es.deployVDB("test", mmd1);
		});
	}
	
	@Test public void testValidationOrder() throws Exception {
		EmbeddedConfiguration ec = new EmbeddedConfiguration();
		ec.setUseDisk(false);
		es.start(ec);
		
		ModelMetaData mmd1 = new ModelMetaData();
		mmd1.setName("b");
		mmd1.setModelType(Type.VIRTUAL);
		mmd1.setSchemaSourceType("ddl");
		mmd1.setSchemaText("create view v as select 1");

		ModelMetaData mmd2 = new ModelMetaData();
		mmd2.setName("a");
		mmd2.setModelType(Type.VIRTUAL);
		mmd2.setSchemaSourceType("ddl");
		mmd2.setSchemaText("create view v1 as select * from v");

		//We need mmd1 to validate before mmd2, reversing the order will result in an exception
		es.deployVDB("test", mmd1, mmd2);
		
		try {
			es.deployVDB("test2", mmd2, mmd1);
			fail();
		} catch (VirtualDatabaseException e) {
			
		}
	}

	@Test public void testTransactions() throws Exception {
		EmbeddedConfiguration ec = new EmbeddedConfiguration();
		MockTransactionManager tm = new MockTransactionManager();
		ec.setTransactionManager(tm);
		ec.setUseDisk(false);
		es.start(ec);

		ModelMetaData mmd1 = new ModelMetaData();
		mmd1.setName("b");
		mmd1.setModelType(Type.VIRTUAL);
		mmd1.setSchemaSourceType("ddl");
		mmd1.setSchemaText("create view v as select 1; " +
				"create virtual procedure proc () options (updatecount 2) as begin select * from v; end; " +
				"create virtual procedure proc0 () as begin atomic select * from v; end; " +
				"create virtual procedure proc1 () as begin atomic insert into #temp values (1); insert into #temp values (1); end; ");

		es.deployVDB("test", mmd1);

		TeiidDriver td = es.getDriver();
		Connection c = td.connect("jdbc:datavirtuality:test", null);
		//local txn
		c.setAutoCommit(false);
		Statement s = c.createStatement();
		s.execute("select 1");
		c.setAutoCommit(true);
		assertEquals(1, tm.txnHistory.size());
		Transaction txn = tm.txnHistory.remove(0);
		Mockito.verify(txn).commit();

		//should be an auto-commit txn (could also force with autoCommitTxn=true)
		s.execute("call proc ()");

		assertEquals(1, tm.txnHistory.size());
		txn = tm.txnHistory.remove(0);
		Mockito.verify(txn).commit();

		//no txn needed
		s.execute("call proc0()");

		assertEquals(0, tm.txnHistory.size());

		//block txn
		s.execute("call proc1()");

		assertEquals(1, tm.txnHistory.size());
		txn = tm.txnHistory.remove(0);
		Mockito.verify(txn).commit();

		s.execute("set autoCommitTxn on");
		s.execute("set noexec on");
		s.execute("select 1");
		assertFalse(s.getResultSet().next());
	}

	@Test public void testGeneratedKeysVirtual() throws Exception {
		EmbeddedConfiguration ec = new EmbeddedConfiguration();
		MockTransactionManager tm = new MockTransactionManager();
		ec.setTransactionManager(tm);
		ec.setUseDisk(false);
		es.start(ec);

		es.addTranslator("t", new ExecutionFactory<Void, Void>());

		ModelMetaData mmd1 = new ModelMetaData();
		mmd1.setName("b");
		mmd1.setModelType(Type.VIRTUAL);
		mmd1.addSourceMetadata("ddl", "create view v (i integer, k integer auto_increment) OPTIONS (UPDATABLE true) as select 1, 2; " +
				"create trigger on v instead of insert as for each row begin atomic " +
				"\ncreate local temporary table x (y serial, z integer, primary key (y));"
				+ "\ninsert into x (z) values (1);" +
				"end; ");

		es.deployVDB("vdb", mmd1);

		Connection c = es.getDriver().connect("jdbc:datavirtuality:vdb", null);
		PreparedStatement ps = c.prepareStatement("insert into v (i) values (1)", Statement.RETURN_GENERATED_KEYS);
		assertEquals(1, ps.executeUpdate());
		ResultSet rs = ps.getGeneratedKeys();
		assertFalse(rs.next());
	}

	// creation of temp tables is implemented in the scope of the TEIID-196 issue
	/*@Test public void testGeneratedKeysTemp() throws Exception {
		EmbeddedConfiguration ec = new EmbeddedConfiguration();
		MockTransactionManager tm = new MockTransactionManager();
		ec.setTransactionManager(tm);
		ec.setUseDisk(false);
		es.start(ec);

		ModelMetaData mmd1 = new ModelMetaData();
		mmd1.setName("b");
		mmd1.setModelType(Type.VIRTUAL);
		mmd1.addSourceMetadata("ddl", "create view v as select 1");

		es.deployVDB("vdb", mmd1);

		Connection c = es.getDriver().connect("jdbc:datavirtuality:vdb", null);
		Statement s = c.createStatement();
		s.execute("create temporary table t (x serial, y string, primary key (x))");
		PreparedStatement ps = c.prepareStatement("insert into t (y) values ('a')", Statement.RETURN_GENERATED_KEYS);
		assertFalse(ps.execute());
		assertEquals(1, ps.getUpdateCount());
		ResultSet rs = ps.getGeneratedKeys();
		assertTrue(rs.next());
		assertEquals(1, rs.getInt(1));
		//should just be the default, rather than an exception
		assertEquals(11, rs.getMetaData().getColumnDisplaySize(1));
	}

	@Test public void testGeneratedKeyComposite() throws Exception {
		EmbeddedConfiguration ec = new EmbeddedConfiguration();
		MockTransactionManager tm = new MockTransactionManager();
		ec.setTransactionManager(tm);
		ec.setUseDisk(false);
		es.start(ec);

		ModelMetaData mmd1 = new ModelMetaData();
		mmd1.setName("b");
		mmd1.setModelType(Type.VIRTUAL);
		mmd1.addSourceMetadata("ddl", "create view v as select 1");

		es.deployVDB("vdb", mmd1);

		Connection c = es.getDriver().connect("jdbc:datavirtuality:vdb", null);
		Statement s = c.createStatement();
		s.execute("create temporary table t (x string, y serial, z string, primary key (x, y))");
		PreparedStatement ps = c.prepareStatement("insert into t (x, z) values ('a', 'b')", Statement.RETURN_GENERATED_KEYS);
		assertFalse(ps.execute());
		assertEquals(1, ps.getUpdateCount());
		ResultSet rs = ps.getGeneratedKeys();
		assertTrue(rs.next());
		assertEquals(1, rs.getInt(1));
		//should just be the default, rather than an exception
		assertEquals(11, rs.getMetaData().getColumnDisplaySize(1));

		//test in a procedure without the statement directive
		ps = c.prepareStatement("begin insert into t (z) values ('b'); select generated_key(); end");
		rs = ps.executeQuery();
		assertTrue(rs.next());
		assertEquals(2, rs.getInt(1));

		//make sure the session scope works as well
		rs = s.executeQuery("select generated_key()");
		assertTrue(rs.next());
		assertEquals(2, rs.getInt(1));
	}*/

	@Test public void testSubqueryCache() throws Exception {
		UnitTestUtil.enableLogging(Level.WARNING, "org.teiid");
		EmbeddedConfiguration ec = new EmbeddedConfiguration();
		es.start(ec);
		ModelMetaData mmd = new ModelMetaData();
		mmd.setName("x");
		mmd.setModelType(Type.VIRTUAL);
		// TODO addSourceMetadata should be used instead of setSchemaSourceType and setSchemaText methods after implementing TEIID-3372
		//mmd.addSourceMetadata("ddl", ObjectConverterUtil.convertFileToString(UnitTestUtil.getTestDataFile("proc.sql")));
		mmd.setSchemaSourceType("ddl");
		mmd.setSchemaText(ObjectConverterUtil.convertFileToString(UnitTestUtil.getTestDataFile("proc.sql")));
		es.deployVDB("x", mmd);
		Connection c = es.getDriver().connect("jdbc:datavirtuality:x", null);
		Statement s = c.createStatement();
		String[] statements = ObjectConverterUtil.convertFileToString(UnitTestUtil.getTestDataFile("data.sql")).split(";");
		for (String statement:statements) {
			s.execute(statement);
		}
		s.execute("set autoCommitTxn off");
		s.execute("SELECT \"citmp.KoordX\" ,\"citmp.KoordY\" ,( SELECT \"store.insideFence\" FROM ( EXEC point_inside_store ( CAST ( \"citmp.KoordX\" AS float ) ,CAST ( \"citmp.KoordY\" AS float ) ) ) as \"store\" ) as \"insideStore\" ,( SELECT \"firstsection.insideFence\" FROM ( EXEC point_inside_store ( CAST ( \"citmp.KoordX\" AS float ) ,CAST ( \"citmp.KoordY\" AS float ) ) ) as \"firstsection\" ) as \"insideFirstsection\" FROM sample_coords as \"citmp\" ORDER BY insideStore ASC ,insideFirstsection DESC");
		ResultSet rs = s.getResultSet();
		while (rs.next()) {
			//the last two columns should be identical
			assertEquals(rs.getInt(3), rs.getInt(4));
		}
	}

	@Test public void testCancelSystemQuery() throws Exception {
		assertThrows(TeiidSQLException.class, () -> {
			EmbeddedConfiguration ec = new EmbeddedConfiguration();
			es.start(ec);
			ModelMetaData mmd = new ModelMetaData();
			mmd.setName("x");
			mmd.setModelType(Type.VIRTUAL);
//        mmd.addSourceMetadata("ddl", "create view v as select 1;");
			mmd.setSchemaSourceType("ddl");
			mmd.setSchemaText("create view v as select 1;");
			es.deployVDB("x", mmd);
			Connection c = es.getDriver().connect("jdbc:datavirtuality:x", null);
			final Statement s = c.createStatement();
			Thread t = new Thread() {
				@Override
				public void run() {
					try {
						Thread.sleep(1000);
						s.cancel();
					} catch (InterruptedException e) {
					} catch (SQLException e) {
					}
				}
			};
			t.start();
			ResultSet rs = s.executeQuery("select count(*) from columns c, columns c1, columns c2, columns c3, columns c4");
			rs.next();
			fail();
		});
	}
    
    @Test public void testSystemSubquery() throws Exception {
            //already working
            String query = "SELECT t.Name FROM (select * from SYS.Tables AS t limit 10) as t, (SELECT DISTINCT c.TableName FROM SYS.Columns AS c where c.Name > 'A') AS X__1 WHERE t.Name = X__1.TableName";
            EmbeddedConfiguration ec = new EmbeddedConfiguration();
            es.start(ec);
            ModelMetaData mmd = new ModelMetaData();
            mmd.setName("x");
            mmd.setModelType(Type.VIRTUAL);
//            mmd.addSourceMetadata("ddl", "create view v as select 1 as c");
            mmd.setSchemaSourceType("ddl");
            mmd.setSchemaText("create view v as select 1 as c");
            es.deployVDB("x", mmd);
            Connection c = es.getDriver().connect("jdbc:datavirtuality:x", null);
            Statement s = c.createStatement();
            extractRowCount(query, s, 10);

            //was throwing exception
            query = "SELECT t.Name FROM (select * from SYS.Tables AS t limit 10) as t, (SELECT DISTINCT c.TableName FROM SYS.Columns AS c) AS X__1 WHERE t.Name = X__1.TableName";
            extractRowCount(query, s, 10);
    }

    private void extractRowCount(String query, Statement s, int count)
                    throws SQLException {
            s.execute(query);
            ResultSet rs = s.getResultSet();
            int i = 0;
            while (rs.next()) {
                    i++;
            }
            assertEquals(count, i);
    }

    @Test public void testLateralTupleSourceReuse() throws Exception { 
    	es.start(new EmbeddedConfiguration());
    	int rows = 20;
    	ModelMetaData mmd = new ModelMetaData();
    	mmd.setName("y");
//    	mmd.addSourceMetadata("ddl", "CREATE VIRTUAL PROCEDURE pr0(arg1 string) returns (res1 string) AS\n" + 
    	mmd.setSchemaSourceType("ddl");
    	mmd.setSchemaText("CREATE VIRTUAL PROCEDURE pr0(arg1 string) returns (res1 string) AS\n" + 
    			"    BEGIN\n" + 
    			"        SELECT '2017-01-01';\n" + 
    			"    END;"
    			+ "create foreign table test_t1(col_t1 varchar) options (cardinality 20); create foreign table test_t2(col_t2 integer) options (cardinality 20);");
    	mmd.addSourceMapping("y", "y", null);

    	HardCodedExecutionFactory hcef = new HardCodedExecutionFactory();
    	es.addTranslator("y", hcef);

    	es.deployVDB("x", mmd);

    	String sql = "SELECT d.col_t2 FROM \"test_t1\", table(CALL pr0(\"arg1\" => col_t1)) x\n" + 
    			"     join table(select * from \"test_t2\") d \n" +
    			" on true " +
    			"UNION all\n" + 
    			"SELECT d.col_t2 FROM \"test_t1\", table(CALL pr0(\"arg1\" => col_t1)) x\n" + 
    			"    join table(select * from \"test_t2\") d \n" +
    			" on true " + 
    			"        limit 100";

    	List<?>[] vals = new List<?>[rows];
    	Arrays.fill(vals, Arrays.asList("1"));
    	List<?>[] vals1 = new List<?>[rows];
    	Arrays.fill(vals1, Arrays.asList(1));
    	hcef.addData("SELECT test_t1.col_t1 FROM test_t1", Arrays.asList(vals));
    	hcef.addData("SELECT test_t2.col_t2 FROM test_t2", Arrays.asList(vals1));

    	Connection c = es.getDriver().connect("jdbc:datavirtuality:x;", null);
    	Statement s = c.createStatement();

    	s.executeQuery(sql);

    	ResultSet rs = s.getResultSet();
    	int count = 0;
    	while (rs.next()) {
    		count++;
    	}
    	rs.close();
    	assertEquals(100, count);
    }

	@Test public void testUpdateCountAnonProc() throws Exception {
		EmbeddedConfiguration ec = new EmbeddedConfiguration();
		ec.setUseDisk(false);
		es.start(ec);

		ModelMetaData mmd1 = new ModelMetaData();
		mmd1.setName("b");
		mmd1.setModelType(Type.VIRTUAL);
		//mmd1.addSourceMetadata("ddl", "create view v as select 1");
		mmd1.setSchemaSourceType("ddl");
		mmd1.setSchemaText("create view v as select 1");

		es.deployVDB("vdb", mmd1);

		Connection c = es.getDriver().connect("jdbc:datavirtuality:vdb", null);
		Statement s = c.createStatement();
		s.execute("set autoCommitTxn off");
		PreparedStatement ps = c.prepareStatement("begin select 1 without return; end");
		assertNull(ps.getMetaData());
		ps.execute();
		assertEquals(0, ps.getUpdateCount());
		assertNull(ps.getMetaData());
	}

	@Test public void testSomething() throws Exception {
		es.start(new EmbeddedConfiguration());
		String sql = "SELECT \n" +
				"(exec \"SYSADMIN.logMsg\"(\n" +
				"    \"level\" => 'INFO',\n" +
				"    \"context\" => 'DEBUG.FOO.BAR',\n" +
				"    \"msg\" => tccd.bank_account_holder_name)) as something\n" +
				"FROM \n" +
				"(\n" +
				"    SELECT c.city AS \"bank_account_holder_name\"\n" +
				"    FROM \"address\" c\n" +
				"    JOIN \n" +
				"    (\n" +
				"        select ca.addressid\n" +
				"        from \"customeraddress\" ca\n" +
				"     cross JOIN \n" +
				"    (\n" +
				"        WITH latest_exchange_rates AS \n" +
				"        (\n" +
				"            SELECT exchange_rate_date AS month_begin\n" +
				"            FROM (\n" +
				"                SELECT  CURDATE() as exchange_rate_date\n" +
				"                )t\n" +
				"        )\n" +
				"        SELECT DISTINCT dt.month_start AS month_begin\n" +
				"        FROM (\n" +
				"            CALL createDateDimensionsTable(\n" +
				"                \"startdate\" => TIMESTAMPADD(SQL_TSI_MONTH, 1, (SELECT month_begin FROM latest_exchange_rates)),\n" +
				"                \"enddate\" => TIMESTAMPADD(SQL_TSI_YEAR, 15, (SELECT month_begin FROM latest_exchange_rates))\n" +
				"            ) ) AS dt\n" +
				"    ) fx\n" +
				"    ) ci ON ci.addressid = c.addressid\n" +
				") tccd ;";

		String ddl = "CREATE PROCEDURE createDateDimensionsTable(\n" +
				"         IN startdate date NOT NULL OPTIONS (ANNOTATION 'Start date for table.'),\n" +
				"         IN enddate date OPTIONS (ANNOTATION 'End date for table. If NULL, uses current date.')\n" +
				"        )\n" +
				"        RETURNS \n" +
				"        (\n" +
				"         \"month_start\" date\n" +
				"        ) AS\n" +
				"        BEGIN\n" +
				"            select CURDATE() as month_start;\n" +
				"        END;"
				+ "create foreign table address (addressid integer, city string);"
				+ "create foreign table customeraddress (addressid integer);";

		ModelMetaData mmd = new ModelMetaData();
		mmd.setName("y");
		//mmd.addSourceMetadata("ddl", ddl);
		mmd.setSchemaSourceType("ddl");
		mmd.setSchemaText(ddl);
		mmd.addSourceMapping("y", "y", null);
		HardCodedExecutionFactory hcef = new HardCodedExecutionFactory();
		hcef.addData("SELECT address.addressid, address.city FROM address", Arrays.asList(Arrays.asList(1, "baltimore")));
		hcef.addData("SELECT customeraddress.addressid FROM customeraddress", Arrays.asList(Arrays.asList(1)));
		es.addTranslator("y", hcef);

		es.deployVDB("x", mmd);

		Connection c = es.getDriver().connect("jdbc:datavirtuality:x;", null);
		Statement s = c.createStatement();
		s.execute("set showplan debug");
		try {
			s.executeQuery(sql);
		} finally {
			ResultSet rs = s.executeQuery("show plan");
			rs.next();
			System.out.println(rs.getString(1));
			System.out.println(rs.getString(2));
			System.out.println(rs.getString(3));
		}
	}

	@Test public void testTurnOffLobCleaning() throws Exception {
		EmbeddedConfiguration ec = new EmbeddedConfiguration();
		es.start(ec);
		ModelMetaData mmd = new ModelMetaData();
		mmd.setName("y");
		mmd.setModelType(Type.VIRTUAL);
		mmd.setSchemaSourceType("ddl");
		mmd.setSchemaText("create view dummy as select 1;");
		//mmd.addSourceMetadata("ddl", "create view dummy as select 1;");
		es.deployVDB("x", mmd);

		Connection c = es.getDriver().connect("jdbc:datavirtuality:x", null);
		Statement s = c.createStatement();
		s.execute("select dv_session_set('clean_lobs_onclose', false)");
		ResultSet rs = s.executeQuery("WITH t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 5000 ) SELECT xmlelement(root, xmlagg(xmlelement(val, n))) FROM t");
		assertTrue(rs.next());
		SQLXML val = rs.getSQLXML(1);
		rs.close();
		assertEquals(73906, val.getString().length());
	}

	@Test public void testLargeCopyLobs() throws Exception {
		EmbeddedConfiguration ec = new EmbeddedConfiguration();
		es.start(ec);
		HardCodedExecutionFactory hcef = new HardCodedExecutionFactory();
		hcef.addData("SELECT t.col FROM t", Arrays.asList(Arrays.asList(new ClobType(new ClobImpl(new InputStreamFactory() {

		    @Override
 	        public InputStream getInputStream() throws IOException {
				return new ByteArrayInputStream(new byte[DataTypeManager.MAX_LOB_MEMORY_BYTES*2]);
		    }

		}, DataTypeManager.MAX_LOB_MEMORY_BYTES)))));
		hcef.setCopyLobs(true);

		es.addTranslator("y", hcef);

		ModelMetaData mmd = new ModelMetaData();
		mmd.setName("my-schema");
		mmd.addSourceMapping("x", "y", null);
		//mmd.addSourceMetadata("ddl", "create foreign table t (col clob);");
		mmd.setSchemaSourceType("ddl");
		mmd.setSchemaText("create foreign table t (col clob);");

		es.deployVDB("test", mmd);

		TeiidDriver td = es.getDriver();
		Connection c = td.connect("jdbc:datavirtuality:test", null);
		Statement s = c.createStatement();
		ResultSet rs = s.executeQuery("select cast(t.col as string) from t");
		assertTrue(rs.next());
		//assertEquals(DataTypeManager.MAX_STRING_LENGTH, rs.getString(1).length());
		assertEquals(65536, rs.getString(1).length());
	}

	@Test public void testGeometrySelect() throws Exception {
		EmbeddedConfiguration ec = new EmbeddedConfiguration();
		ec.setUseDisk(false);
		es.start(ec);

		ModelMetaData mmd1 = new ModelMetaData();
		mmd1.setName("b");
		mmd1.setModelType(Type.VIRTUAL);
		mmd1.setSchemaSourceType("ddl");
		mmd1.setSchemaText("create view v (i geometry) as select ST_GeomFromText('POLYGON ((100 100, 200 200, 75 75, 100 100))')");

		es.deployVDB("vdb", mmd1);

		Connection c = es.getDriver().connect("jdbc:datavirtuality:vdb", null);
		Statement s = c.createStatement();
		ResultSet rs = s.executeQuery("select * from v");
		assertEquals("geometry", rs.getMetaData().getColumnTypeName(1));
		assertEquals(Types.BLOB, rs.getMetaData().getColumnType(1));
		assertEquals("java.sql.Blob", rs.getMetaData().getColumnClassName(1));
		rs.next();
		assertEquals(77, rs.getBlob(1).length());
	}

	@Test
	public void testJsonPath() throws Exception {
		EmbeddedConfiguration ec = new EmbeddedConfiguration();
		ec.setUseDisk(false);
		es.start(ec);

		ModelMetaData mmd1 = new ModelMetaData();
		mmd1.setName("b");
		mmd1.setModelType(Type.VIRTUAL);
		mmd1.setSchemaSourceType("ddl");
		mmd1.setSchemaText("create view v (i geometry) as select ST_GeomFromText('POLYGON ((100 100, 200 200, 75 75, 100 100))')");

		es.deployVDB("abc", mmd1);

		Connection connection = es.getDriver().connect("jdbc:datavirtuality:abc", null);
		Statement stmt = connection.createStatement();

		String sql = "select jsonpathvalue(jsonparse('{\"a\":1, \"b\":[2,3]}', false), '$.b')";

		ResultSet rs = stmt.executeQuery(sql);
		rs.next();
		assertEquals("[2,3]", rs.getString(1));
	}

	private String createVDB(String name, String version) {
		return "<vdb name=\""+ name +"\" version=\""+version+"\"><model name=\"x\" type=\"VIRTUAL\"><metadata type=\"ddl\">create view v as select 1</metadata></model></vdb>";
	}

	@Test public void testTempVisibilityToExecuteImmediate() throws Exception {
		EmbeddedConfiguration ec = new EmbeddedConfiguration();
		es.start(ec);
		ModelMetaData mmd = new ModelMetaData();
		mmd.setName("x");
		mmd.setModelType(Type.VIRTUAL);
		mmd.addSourceMetadata("ddl", "create procedure p () as execute immediate 'select 1' as x integer into #temp;");
		es.deployVDB("x", mmd);
		Connection c = es.getDriver().connect("jdbc:datavirtuality:x", null);
		Statement s = c.createStatement();
		s.execute("create local temporary table #temp (x integer)");
		s.execute("exec p()");
		extractRowCount("select * from #temp", s, 0);
	}	
}
