content: /*
   * Licensed to the Apache Software Foundation (ASF) under one or more
   * contributor license agreements.  See the NOTICE file distributed with
   * this work for additional information regarding copyright ownership.
   * The ASF licenses this file to you under the Apache License, Version 2.0
   * (the "License"); you may not use this file except in compliance with
   * the License.  You may obtain a copy of the License at
   *
   * http://www.apache.org/licenses/LICENSE-2.0
   *
   * Unless required by applicable law or agreed to in writing, software
   * distributed under the License is distributed on an "AS IS" BASIS,
   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   * See the License for the specific language governing permissions and
   * limitations under the License.
   */
  package org.apache.calcite.test;
  
  import org.apache.calcite.adapter.java.JavaTypeFactory;
  import org.apache.calcite.jdbc.CalciteConnection;
  import org.apache.calcite.jdbc.JavaRecordType;
  import org.apache.calcite.linq4j.Enumerator;
  import org.apache.calcite.linq4j.Linq4j;
  import org.apache.calcite.linq4j.QueryProvider;
  import org.apache.calcite.linq4j.Queryable;
  import org.apache.calcite.linq4j.tree.Expression;
  import org.apache.calcite.rel.type.RelDataType;
  import org.apache.calcite.rel.type.RelDataTypeFactory;
  import org.apache.calcite.rel.type.RelDataTypeField;
  import org.apache.calcite.rel.type.RelDataTypeFieldImpl;
  import org.apache.calcite.schema.SchemaPlus;
  import org.apache.calcite.schema.Schemas;
  import org.apache.calcite.schema.impl.AbstractSchema;
  import org.apache.calcite.schema.impl.AbstractTableQueryable;
  import org.apache.calcite.sql.type.MeasureSqlType;
  import org.apache.calcite.sql.type.SqlTypeName;
  import org.apache.calcite.test.schemata.hr.Employee;
  import org.apache.calcite.util.TestUtil;
  
  import org.junit.jupiter.api.Disabled;
  import org.junit.jupiter.api.Test;
  
  import java.lang.reflect.Field;
  import java.lang.reflect.Type;
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
  import java.util.ArrayList;
  import java.util.Collection;
  import java.util.List;
  import java.util.Properties;
  
  import static org.apache.calcite.test.CalciteAssert.hr;
  import static org.apache.calcite.test.CalciteAssert.that;
  import static org.apache.calcite.util.ReflectUtil.isStatic;
  
  import static org.hamcrest.CoreMatchers.is;
  import static org.hamcrest.CoreMatchers.nullValue;
  import static org.hamcrest.MatcherAssert.assertThat;
  import static org.junit.jupiter.api.Assertions.assertFalse;
  
  /**
   * Tests for a JDBC front-end (with some quite complex SQL) and Linq4j back-end
   * (based on in-memory collections).
   */
  public class JdbcFrontLinqBackTest {
    /**
     * Runs a simple query that reads from a table in an in-memory schema.
     */
    @Test void testSelect() {
      hr()
          .query("select *\n"
              + "from \"foodmart\".\"sales_fact_1997\" as s\n"
              + "where s.\"cust_id\" = 100")
          .returns("cust_id=100; prod_id=10\n");
    }
  
    /**
     * Runs a simple query that joins between two in-memory schemas.
     */
    @Test void testJoin() {
      hr()
          .query("select *\n"
              + "from \"foodmart\".\"sales_fact_1997\" as s\n"
              + "join \"hr\".\"emps\" as e\n"
              + "on e.\"empid\" = s.\"cust_id\"")
          .returnsUnordered(
              "cust_id=100; prod_id=10; empid=100; deptno=10; name=Bill; salary=10000.0; commission=1000",
              "cust_id=150; prod_id=20; empid=150; deptno=10; name=Sebastian; salary=7000.0; commission=null");
    }
  
    /**
     * Simple GROUP BY.
     */
    @Test void testGroupBy() {
      hr()
          .query("select \"deptno\", sum(\"empid\") as s, count(*) as c\n"
              + "from \"hr\".\"emps\" as e\n"
              + "group by \"deptno\"")
          .returns("deptno=20; S=200; C=1\n"
              + "deptno=10; S=360; C=3\n");
    }
  
    /**
     * Simple ORDER BY.
     */
    @Test void testOrderBy() {
      hr()
          .query("select upper(\"name\") as un, \"deptno\"\n"
              + "from \"hr\".\"emps\" as e\n"
              + "order by \"deptno\", \"name\" desc")
          .explainContains(""
              + "EnumerableSort(sort0=[$1], sort1=[$2], dir0=[ASC], dir1=[DESC])\n"
              + "  EnumerableCalc(expr#0..4=[{inputs}], expr#5=[UPPER($t2)], UN=[$t5], deptno=[$t1], name=[$t2])\n"
              + "    EnumerableTableScan(table=[[hr, emps]])")
          .returns("UN=THEODORE; deptno=10\n"
              + "UN=SEBASTIAN; deptno=10\n"
              + "UN=BILL; deptno=10\n"
              + "UN=ERIC; deptno=20\n");
    }
  
    /**
     * Simple UNION, plus ORDER BY.
     *
     * <p>Also tests a query that returns a single column. We optimize this case
     * internally, using non-array representations for rows.
     */
    @Test void testUnionAllOrderBy() {
      hr()
          .query("select \"name\"\n"
              + "from \"hr\".\"emps\" as e\n"
              + "union all\n"
              + "select \"name\"\n"
              + "from \"hr\".\"depts\"\n"
              + "order by 1 desc")
          .returns("name=Theodore\n"
              + "name=Sebastian\n"
              + "name=Sales\n"
              + "name=Marketing\n"
              + "name=HR\n"
              + "name=Eric\n"
              + "name=Bill\n");
    }
  
    /**
     * Tests UNION.
     */
    @Test void testUnion() {
      hr()
          .query("select substring(\"name\" from 1 for 1) as x\n"
              + "from \"hr\".\"emps\" as e\n"
              + "union\n"
              + "select substring(\"name\" from 1 for 1) as y\n"
              + "from \"hr\".\"depts\"")
          .returnsUnordered(
              "X=T",
              "X=E",
              "X=S",
              "X=B",
              "X=M",
              "X=H");
    }
  
    /**
     * Tests INTERSECT.
     */
    @Test void testIntersect() {
      hr()
          .query("select substring(\"name\" from 1 for 1) as x\n"
              + "from \"hr\".\"emps\" as e\n"
              + "intersect\n"
              + "select substring(\"name\" from 1 for 1) as y\n"
              + "from \"hr\".\"depts\"")
          .returns("X=S\n");
    }
  
    /**
     * Tests EXCEPT.
     */
    @Disabled
    @Test void testExcept() {
      hr()
          .query("select substring(\"name\" from 1 for 1) as x\n"
              + "from \"hr\".\"emps\" as e\n"
              + "except\n"
              + "select substring(\"name\" from 1 for 1) as y\n"
              + "from \"hr\".\"depts\"")
          .returnsUnordered(
              "X=T",
              "X=E",
              "X=B");
    }
  
    @Test void testWhereBad() {
      hr()
          .query("select *\n"
              + "from \"foodmart\".\"sales_fact_1997\" as s\n"
              + "where empid > 120")
          .throws_("Column 'EMPID' not found in any table");
    }
  
    /** Test case for
     * <a href="https://issues.apache.org/jira/browse/CALCITE-9">[CALCITE-9]
     * RexToLixTranslator not incrementing local variable name counter</a>. */
    @Test void testWhereOr() {
      hr()
          .query("select * from \"hr\".\"emps\"\n"
              + "where (\"empid\" = 100 or \"empid\" = 200)\n"
              + "and \"deptno\" = 10")
          .returns(
              "empid=100; deptno=10; name=Bill; salary=10000.0; commission=1000\n");
    }
  
    @Test void testWhereLike() {
      hr()
          .query("select *\n"
              + "from \"hr\".\"emps\" as e\n"
              + "where e.\"empid\" < 120 or e.\"name\" like 'S%'")
          .returns(""
              + "empid=100; deptno=10; name=Bill; salary=10000.0; commission=1000\n"
              + "empid=150; deptno=10; name=Sebastian; salary=7000.0; commission=null\n"
              + "empid=110; deptno=10; name=Theodore; salary=11500.0; commission=250\n");
    }
  
    @Test void testInsert() {
      final List<Employee> employees = new ArrayList<>();
      CalciteAssert.AssertThat with = mutable(employees);
      with.query("select * from \"foo\".\"bar\"")
          .returns(
              "empid=0; deptno=0; name=first; salary=0.0; commission=null\n");
      with.query("insert into \"foo\".\"bar\" select * from \"hr\".\"emps\"")
          .updates(4);
      with.query("select count(*) as c from \"foo\".\"bar\"")
          .returns("C=5\n");
      with.query("insert into \"foo\".\"bar\" "
          + "select * from \"hr\".\"emps\" where \"deptno\" = 10")
          .updates(3);
      with.query("select \"name\", count(*) as c from \"foo\".\"bar\" "
          + "group by \"name\"")
          .returnsUnordered(
              "name=Bill; C=2",
              "name=Eric; C=1",
              "name=Theodore; C=2",
              "name=first; C=1",
              "name=Sebastian; C=2");
    }
  
    @Test void testInsertBind() throws Exception {
      final List<Employee> employees = new ArrayList<>();
      CalciteAssert.AssertThat with = mutable(employees);
      with.query("select count(*) as c from \"foo\".\"bar\"")
          .returns("C=1\n");
      with.doWithConnection(c -> {
        try {
          final String sql = "insert into \"foo\".\"bar\"\n"
              + "values (?, 0, ?, 10.0, null)";
          try (PreparedStatement p = c.prepareStatement(sql)) {
            p.setInt(1, 1);
            p.setString(2, "foo");
            final int count = p.executeUpdate();
            assertThat(count, is(1));
          }
        } catch (SQLException e) {
          throw TestUtil.rethrow(e);
        }
      });
      with.query("select count(*) as c from \"foo\".\"bar\"")
          .returns("C=2\n");
      with.query("select * from \"foo\".\"bar\"")
          .returnsUnordered("empid=0; deptno=0; name=first; salary=0.0; commission=null",
              "empid=1; deptno=0; name=foo; salary=10.0; commission=null");
    }
  
    @Test void testDelete() {
      final List<Employee> employees = new ArrayList<>();
      CalciteAssert.AssertThat with = mutable(employees);
      with.query("select * from \"foo\".\"bar\"")
          .returnsUnordered(
              "empid=0; deptno=0; name=first; salary=0.0; commission=null");
      with.query("insert into \"foo\".\"bar\" select * from \"hr\".\"emps\"")
          .updates(4);
      with.query("select count(*) as c from \"foo\".\"bar\"")
          .returnsUnordered("C=5");
      final String deleteSql = "delete from \"foo\".\"bar\" "
          + "where \"deptno\" = 10";
      with.query(deleteSql)
          .updates(3);
      final String sql = "select \"name\", count(*) as c\n"
          + "from \"foo\".\"bar\"\n"
          + "group by \"name\"";
      with.query(sql)
          .returnsUnordered(
              "name=Eric; C=1",
              "name=first; C=1");
    }
  
    /**
     * Creates the post processor routine to be applied against a Connection.
     *
     * <p>Table schema is based on JdbcTest#Employee
     * (refer to {@link JdbcFrontLinqBackTest#mutable}).
     *
     * @param initialData records to be presented in table
     * @return a connection post-processor
     */
    private static CalciteAssert.ConnectionPostProcessor makePostProcessor(
        List<Employee> initialData, boolean withMeasures) {
      return connection -> {
        CalciteConnection calciteConnection =
            connection.unwrap(CalciteConnection.class);
        SchemaPlus rootSchema = calciteConnection.getRootSchema();
        SchemaPlus mapSchema = rootSchema.add("foo", new AbstractSchema());
        final String tableName = "bar";
        final AbstractModifiableTable table =
            mutable(tableName, initialData, withMeasures);
        mapSchema.add(tableName, table);
        return calciteConnection;
      };
    }
  
    /**
     * Method to be shared with {@code RemoteDriverTest}.
     *
     * @param initialData record to be presented in table
     */
    public static Connection makeConnection(final List<Employee> initialData,
        final boolean withMeasures) throws Exception {
      Properties info = new Properties();
      Connection connection = DriverManager.getConnection("jdbc:calcite:", info);
      connection = makePostProcessor(initialData, withMeasures).apply(connection);
      return connection;
    }
  
    /**
     * Creates a connection with an empty modifiable table with
     * {@link Employee} schema.
     */
    public static Connection makeConnection() throws Exception {
      return makeConnection(new ArrayList<>(), false);
    }
  
    private CalciteAssert.AssertThat mutable(
        final List<Employee> employees) {
      employees.add(new Employee(0, 0, "first", 0f, null));
      return that()
          .with(CalciteAssert.Config.REGULAR)
          .with(makePostProcessor(employees, false));
    }
  
    /** Creates an AbstractModifiableTable based on {@link Employee} class.
     *
     * <p>If {@code withMeasures} is true, treats {@link Employee#salary} field
     * as a {@link MeasureSqlType} of type {@link SqlTypeName#FLOAT}. */
    static AbstractModifiableTable mutable(String tableName,
        List<Employee> employees, boolean withMeasures) {
      return new AbstractModifiableTable(tableName) {
        public RelDataType getRowType(RelDataTypeFactory typeFactory) {
          final JavaTypeFactory javaTypeFactory = (JavaTypeFactory) typeFactory;
          final List<RelDataTypeField> list = new ArrayList<>();
          for (Field field : Employee.class.getFields()) {
            if (!isStatic(field)) {
              // FIXME: watch out for recursion
              final Type fieldType = field.getType();
              final RelDataType relType =
                  withMeasures && field.getName().equals("salary")
                      ? javaTypeFactory.createMeasureType(
                          javaTypeFactory.createSqlType(SqlTypeName.FLOAT))
                      : javaTypeFactory.createType(fieldType);
              list.add(
                  new RelDataTypeFieldImpl(field.getName(), list.size(),
                      relType));
            }
          }
          return new JavaRecordType(list, Employee.class);
        }
  
        public <T> Queryable<T> asQueryable(QueryProvider queryProvider,
            SchemaPlus schema, String tableName) {
          return new AbstractTableQueryable<T>(queryProvider, schema, this,
              tableName) {
            public Enumerator<T> enumerator() {
              //noinspection unchecked
              return (Enumerator<T>) Linq4j.enumerator(employees);
            }
          };
        }
  
        public Type getElementType() {
          return Employee.class;
        }
  
        public Expression getExpression(SchemaPlus schema, String tableName,
            Class clazz) {
          return Schemas.tableExpression(schema, getElementType(), tableName,
              clazz);
        }
  
        public Collection getModifiableCollection() {
          return employees;
        }
      };
    }
  
    @Test void testInsert2() {
      final List<Employee> employees = new ArrayList<>();
      CalciteAssert.AssertThat with = mutable(employees);
      with.query("insert into \"foo\".\"bar\" values (1, 1, 'second', 2, 2)")
          .updates(1);
      with.query("insert into \"foo\".\"bar\"\n"
          + "values (1, 3, 'third', 0, 3), (1, 4, 'fourth', 0, 4), (1, 5, 'fifth ', 0, 3)")
          .updates(3);
      with.query("select count(*) as c from \"foo\".\"bar\"")
          .returns("C=5\n");
      with.query("insert into \"foo\".\"bar\" values (1, 6, null, 0, null)")
          .updates(1);
      with.query("select count(*) as c from \"foo\".\"bar\"")
          .returns("C=6\n");
    }
  
    /** Local Statement insert. */
    @Test void testInsert3() throws Exception {
      Connection connection = makeConnection();
      String sql = "insert into \"foo\".\"bar\" values (1, 1, 'second', 2, 2)";
  
      Statement statement = connection.createStatement();
      boolean status = statement.execute(sql);
      assertFalse(status);
      ResultSet resultSet = statement.getResultSet();
      assertThat(resultSet, nullValue());
      int updateCount = statement.getUpdateCount();
      assertThat(updateCount, is(1));
    }
  
    /** Local PreparedStatement insert WITHOUT bind variables. */
    @Test void testPreparedStatementInsert() throws Exception {
      Connection connection = makeConnection();
      assertFalse(connection.isClosed());
  
      String sql = "insert into \"foo\".\"bar\" values (1, 1, 'second', 2, 2)";
      PreparedStatement preparedStatement = connection.prepareStatement(sql);
      assertFalse(preparedStatement.isClosed());
  
      boolean status = preparedStatement.execute();
      assertFalse(status);
      ResultSet resultSet = preparedStatement.getResultSet();
      assertThat(resultSet, nullValue());
      int updateCount = preparedStatement.getUpdateCount();
      assertThat(updateCount, is(1));
    }
  
    /** Local PreparedStatement insert WITH bind variables. */
    @Test void testPreparedStatementInsert2() {
    }
  
    /** Some of the rows have the wrong number of columns. */
    @Test void testInsertMultipleRowMismatch() {
      final List<Employee> employees = new ArrayList<>();
      CalciteAssert.AssertThat with = mutable(employees);
      with.query("insert into \"foo\".\"bar\" values\n"
          + " (1, 3, 'third'),\n"
          + " (1, 4, 'fourth'),\n"
          + " (1, 5, 'fifth ', 3)")
          .throws_("Incompatible types");
    }
  }
  