/*
 * Copyright 2014 The Board of Trustees of The Leland Stanford Junior University.
 *
 * Licensed 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 com.github.susom.database.test;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.io.StringReader;
import java.math.BigDecimal;
import java.sql.ResultSetMetaData;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.Month;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.TimeZone;

import org.apache.log4j.xml.DOMConfigurator;
import org.junit.After;
import org.junit.Before;
import org.junit.Rule;
import org.junit.Test;

import com.github.susom.database.ConstraintViolationException;
import com.github.susom.database.Database;
import com.github.susom.database.DatabaseException;
import com.github.susom.database.DatabaseProvider;
import com.github.susom.database.OptionsDefault;
import com.github.susom.database.OptionsOverride;
import com.github.susom.database.Row;
import com.github.susom.database.RowHandler;
import com.github.susom.database.Rows;
import com.github.susom.database.RowsHandler;
import com.github.susom.database.Schema;
import com.github.susom.database.Sql;
import com.github.susom.database.SqlArgs;
import com.github.susom.database.StatementAdaptor;

import static org.hamcrest.CoreMatchers.equalTo;
import static org.junit.Assert.*;

/**
 * Exercise Database functionality with a real databases.
 *
 * @author garricko
 */
public abstract class CommonTest {
  final static String TEST_TABLE_NAME = "dbtest";

  static {
    // Initialize logging
    String log4jConfig = new File("log4j.xml").getAbsolutePath();
    DOMConfigurator.configure(log4jConfig);
    org.apache.log4j.Logger log = org.apache.log4j.Logger.getLogger(CommonTest.class);
    log.info("Initialized log4j using file: " + log4jConfig);
  }

  protected DatabaseProvider dbp;
  protected Database db;
  protected Date now = new Date();
  protected LocalDate localDateNow = LocalDate.now();

  @Before
  public void setupJdbc() throws Exception {
    dbp = createDatabaseProvider(new OptionsOverride() {
      @Override
      public Date currentDate() {
        return now;
      }

      @Override
      public Calendar calendarForTimestamps() {
        return Calendar.getInstance(TimeZone.getTimeZone("America/Los_Angeles"));
      }
    });
    db = dbp.get();
    db.dropTableQuietly(TEST_TABLE_NAME);
  }

  protected abstract DatabaseProvider createDatabaseProvider(OptionsOverride options) throws Exception;

  @After
  public void closeJdbc() throws Exception {
    if (dbp != null) {
      dbp.commitAndClose();
    }
  }

  @Test
  public void tableExists() {
    // Verify dbtest table does not exist
    String lowercaseTable = TEST_TABLE_NAME.toLowerCase();
    testTableLookup(lowercaseTable);
    db.dropTableQuietly(lowercaseTable);

    // Let's try creating a table with an upper case name and verify it works
    String uppercaseTable = TEST_TABLE_NAME.toUpperCase();
    testTableLookup( uppercaseTable );
    db.dropTableQuietly(uppercaseTable);

    // Verify that null or empty name is handled gracefully
    assertFalse(db.tableExists(null));
    assertFalse(db.tableExists(""));
  }

  private void testTableLookup(String tableName) {
    // Verify test table does not exist
    assertFalse(db.tableExists(tableName));

    // Create and verify it exists.
    new Schema().addTable(tableName).addColumn("pk").primaryKey().schema().execute(db);
    assertTrue(db.tableExists(tableName));
  }

  @Test
  public void normalizeTableName() {
    // Verify that null and empty cases are handled gracefully
    assertNull(db.normalizeTableName(null));
    assertEquals("", db.normalizeTableName(""));

    // Verify a quoted table name is returned in exactly the same case, with quotes removed.
    String camelCaseTableName = "\"DbTest\"";
    assertEquals(camelCaseTableName.substring(1, camelCaseTableName.length()-1),
      db.normalizeTableName(camelCaseTableName));

    // Verify that the database flavor gets the expected normalized case
    boolean isUpperCase = db.flavor().isNormalizedUpperCase();
    if (isUpperCase) {
      assertEquals(TEST_TABLE_NAME.toUpperCase(), db.normalizeTableName(TEST_TABLE_NAME));
    } else {
      assertEquals(TEST_TABLE_NAME.toLowerCase(), db.normalizeTableName(TEST_TABLE_NAME));
    }
  }

  @Test
  public void selectNewTable() {
    new Schema()
        .addTable("dbtest")
          .addColumn("nbr_integer").asInteger().primaryKey().table()
          .addColumn("nbr_long").asLong().table()
          .addColumn("nbr_float").asFloat().table()
          .addColumn("nbr_double").asDouble().table()
          .addColumn("nbr_big_decimal").asBigDecimal(19, 9).table()
          .addColumn("str_varchar").asString(80).table()
          .addColumn("str_fixed").asStringFixed(1).table()
          .addColumn("str_lob").asClob().table()
          .addColumn("bin_blob").asBlob().table()
          .addColumn("date_millis").asDate().table()
          .addColumn("local_date").asLocalDate().table().schema().execute(db);

    BigDecimal bigDecimal = new BigDecimal("5.3");
    db.toInsert("insert into dbtest values (?,?,?,?,?,?,?,?,?,?,?)").argInteger(1).argLong(2L).argFloat(3.2f).argDouble(4.2)
        .argBigDecimal(bigDecimal).argString("Hello").argString("T").argClobString("World")
        .argBlobBytes("More".getBytes()).argDate(now).argLocalDate(localDateNow).insert(1);

    db.toSelect(
        "select nbr_integer, nbr_long, nbr_float, nbr_double, nbr_big_decimal, str_varchar, str_fixed, str_lob, "
            + "bin_blob, date_millis, local_date from dbtest").query(new RowsHandler<Void>() {
      @Override
      public Void process(Rows rs) throws Exception {
        assertTrue(rs.next());
        assertEquals(new Integer(1), rs.getIntegerOrNull(1));
        assertEquals(new Integer(1), rs.getIntegerOrNull("nbr_integer"));
        assertEquals(1, rs.getIntegerOrZero(1));
        assertEquals(1, rs.getIntegerOrZero("nbr_integer"));
        assertEquals(new Long(2), rs.getLongOrNull(2));
        assertEquals(new Long(2), rs.getLongOrNull("nbr_long"));
        assertEquals(2, rs.getLongOrZero(2));
        assertEquals(2, rs.getLongOrZero("nbr_long"));
        assertEquals(new Float(3.2f), rs.getFloatOrNull(3));
        assertEquals(new Float(3.2f), rs.getFloatOrNull("nbr_float"));
        assertEquals(3.2, rs.getFloatOrZero(3), 0.01);
        assertEquals(3.2, rs.getFloatOrZero("nbr_float"), 0.01);
        assertEquals(new Double(4.2), rs.getDoubleOrNull(4));
        assertEquals(new Double(4.2), rs.getDoubleOrNull("nbr_double"));
        assertEquals(4.2, rs.getDoubleOrZero(4), 0.01);
        assertEquals(4.2, rs.getDoubleOrZero("nbr_double"), 0.01);
        assertEquals(new BigDecimal("5.3"), rs.getBigDecimalOrNull(5));
        assertEquals(new BigDecimal("5.3"), rs.getBigDecimalOrNull("nbr_big_decimal"));
        assertEquals(new BigDecimal("5.3"), rs.getBigDecimalOrZero(5));
        assertEquals(new BigDecimal("5.3"), rs.getBigDecimalOrZero("nbr_big_decimal"));
        assertEquals("Hello", rs.getStringOrNull(6));
        assertEquals("Hello", rs.getStringOrNull("str_varchar"));
        assertEquals("Hello", rs.getStringOrEmpty(6));
        assertEquals("Hello", rs.getStringOrEmpty("str_varchar"));
        assertEquals("T", rs.getStringOrNull(7));
        assertEquals("T", rs.getStringOrNull("str_fixed"));
        assertEquals("T", rs.getStringOrEmpty(7));
        assertEquals("T", rs.getStringOrEmpty("str_fixed"));
        assertEquals("World", rs.getClobStringOrNull(8));
        assertEquals("World", rs.getClobStringOrNull("str_lob"));
        assertEquals("World", rs.getClobStringOrEmpty(8));
        assertEquals("World", rs.getClobStringOrEmpty("str_lob"));
        assertArrayEquals("More".getBytes(), rs.getBlobBytesOrNull(9));
        assertArrayEquals("More".getBytes(), rs.getBlobBytesOrNull("bin_blob"));
        assertArrayEquals("More".getBytes(), rs.getBlobBytesOrZeroLen(9));
        assertArrayEquals("More".getBytes(), rs.getBlobBytesOrZeroLen("bin_blob"));
        assertEquals(now, rs.getDateOrNull(10));
        assertEquals(now, rs.getDateOrNull("date_millis"));
        assertEquals(localDateNow, rs.getLocalDateOrNull(11));
        assertEquals(localDateNow, rs.getLocalDateOrNull("local_date"));
        return null;
      }
    });
    // Repeat the above query, using the various methods that automatically infer the column
    db.toSelect(
        "select nbr_integer, nbr_long, nbr_float, nbr_double, nbr_big_decimal, str_varchar, str_fixed, str_lob, "
            + "bin_blob, date_millis, local_date from dbtest").query(new RowsHandler<Void>() {
      @Override
      public Void process(Rows rs) throws Exception {
        assertTrue(rs.next());
        assertEquals(new Integer(1), rs.getIntegerOrNull());
        assertEquals(new Long(2), rs.getLongOrNull());
        assertEquals(new Float(3.2f), rs.getFloatOrNull());
        assertEquals(new Double(4.2), rs.getDoubleOrNull());
        assertEquals(new BigDecimal("5.3"), rs.getBigDecimalOrNull());
        assertEquals("Hello", rs.getStringOrNull());
        assertEquals("T", rs.getStringOrNull());
        assertEquals("World", rs.getClobStringOrNull());
        assertArrayEquals("More".getBytes(), rs.getBlobBytesOrNull());
        assertEquals(now, rs.getDateOrNull());
        assertEquals(localDateNow, rs.getLocalDateOrNull());

        return null;
      }
    });
    db.toSelect(
        "select nbr_integer, nbr_long, nbr_float, nbr_double, nbr_big_decimal, str_varchar, str_fixed, str_lob, "
            + "bin_blob, date_millis, local_date from dbtest").query(new RowsHandler<Void>() {
      @Override
      public Void process(Rows rs) throws Exception {
        assertTrue(rs.next());
        assertEquals(1, rs.getIntegerOrZero());
        assertEquals(2, rs.getLongOrZero());
        assertEquals(3.2, rs.getFloatOrZero(), 0.01);
        assertEquals(4.2, rs.getDoubleOrZero(), 0.01);
        assertEquals(new BigDecimal("5.3"), rs.getBigDecimalOrZero());
        assertEquals("Hello", rs.getStringOrEmpty());
        assertEquals("T", rs.getStringOrEmpty());
        assertEquals("World", rs.getClobStringOrEmpty());
        assertArrayEquals("More".getBytes(), rs.getBlobBytesOrZeroLen());
        return null;
      }
    });
    db.toSelect("select str_lob, bin_blob from dbtest").query(new RowsHandler<Void>() {
      @Override
      public Void process(Rows rs) throws Exception {
        assertTrue(rs.next());
        assertEquals("World", readerToString(rs.getClobReaderOrNull(1)));
        assertArrayEquals("More".getBytes(), inputStreamToString(rs.getBlobInputStreamOrNull(2)));
        return null;
      }
    });
    db.toSelect("select str_lob, bin_blob from dbtest").query(new RowsHandler<Void>() {
      @Override
      public Void process(Rows rs) throws Exception {
        assertTrue(rs.next());
        assertEquals("World", readerToString(rs.getClobReaderOrEmpty(1)));
        assertArrayEquals("More".getBytes(), inputStreamToString(rs.getBlobInputStreamOrEmpty(2)));
        return null;
      }
    });
    db.toSelect("select str_lob, bin_blob from dbtest").query(new RowsHandler<Void>() {
      @Override
      public Void process(Rows rs) throws Exception {
        assertTrue(rs.next());
        assertEquals("World", readerToString(rs.getClobReaderOrNull()));
        assertArrayEquals("More".getBytes(), inputStreamToString(rs.getBlobInputStreamOrNull()));
        return null;
      }
    });
    db.toSelect("select str_lob, bin_blob from dbtest").query(new RowsHandler<Void>() {
      @Override
      public Void process(Rows rs) throws Exception {
        assertTrue(rs.next());
        assertEquals("World", readerToString(rs.getClobReaderOrEmpty()));
        assertArrayEquals("More".getBytes(), inputStreamToString(rs.getBlobInputStreamOrEmpty()));
        return null;
      }
    });
    db.toSelect("select str_lob, bin_blob from dbtest").query(new RowsHandler<Void>() {
      @Override
      public Void process(Rows rs) throws Exception {
        assertTrue(rs.next());
        assertEquals("World", readerToString(rs.getClobReaderOrNull("str_lob")));
        assertArrayEquals("More".getBytes(), inputStreamToString(rs.getBlobInputStreamOrNull("bin_blob")));
        return null;
      }
    });
    db.toSelect("select str_lob, bin_blob from dbtest").query(new RowsHandler<Void>() {
      @Override
      public Void process(Rows rs) throws Exception {
        assertTrue(rs.next());
        assertEquals("World", readerToString(rs.getClobReaderOrEmpty("str_lob")));
        assertArrayEquals("More".getBytes(), inputStreamToString(rs.getBlobInputStreamOrEmpty("bin_blob")));
        return null;
      }
    });

    assertEquals(new Long(1), db.toSelect("select count(*) from dbtest where nbr_integer=:i and nbr_long=:l and "
        + "abs(nbr_float-:f)<0.01 and abs(nbr_double-:d)<0.01 and nbr_big_decimal=:bd and str_varchar=:s "
        + "and str_fixed=:sf and date_millis=:date and local_date=:local_date").argInteger("i", 1).argLong("l", 2L).argFloat("f", 3.2f)
        .argDouble("d", 4.2).argBigDecimal("bd", bigDecimal).argString("s", "Hello").argString("sf", "T")
        .argDate("date", now).argLocalDate( "local_date", localDateNow).queryLongOrNull());
    List<Long> result = db.toSelect("select count(*) from dbtest where nbr_integer=:i and nbr_long=:l and "
        + "abs(nbr_float-:f)<0.01 and abs(nbr_double-:d)<0.01 and nbr_big_decimal=:bd and str_varchar=:s "
        + "and str_fixed=:sf and date_millis=:date and local_date=:local_date").argInteger("i", 1).argLong("l", 2L).argFloat("f", 3.2f)
        .argDouble("d", 4.2).argBigDecimal("bd", bigDecimal).argString("s", "Hello").argString("sf", "T")
        .argDate("date", now).argLocalDate("local_date", localDateNow).queryLongs();
    assertEquals(1, result.size());
    assertEquals(new Long(1), result.get(0));
  }

  @Test
  public void updatePositionalArgs() {
    new Schema()
        .addTable("dbtest")
        .addColumn("pk").primaryKey().table()
        .addColumn("nbr_integer").asInteger().table()
        .addColumn("nbr_long").asLong().table()
        .addColumn("nbr_float").asFloat().table()
        .addColumn("nbr_double").asDouble().table()
        .addColumn("nbr_big_decimal").asBigDecimal(19, 9).table()
        .addColumn("str_varchar").asString(80).table()
        .addColumn("str_fixed").asStringFixed(1).table()
        .addColumn("str_lob").asClob().table()
        .addColumn("bin_blob").asBlob().table()
        .addColumn("date_millis").asDate().table()
        .addColumn("local_date").asLocalDate().table().schema().execute(db);

    BigDecimal bigDecimal = new BigDecimal("5.3");
    assertEquals(1, db.toInsert("insert into dbtest values (?,?,?,?,?,?,?,?,?,?,?,?)")
        .argLong(1L)
        .argInteger(1)
        .argLong(2L)
        .argFloat(3.2f)
        .argDouble(4.2)
        .argBigDecimal(bigDecimal)
        .argString("Hello")
        .argString("T")
        .argClobString("World")
        .argBlobBytes("More".getBytes())
        .argDate(now)
        .argLocalDate(localDateNow).insert());

    db.toUpdate("update dbtest set nbr_integer=?, nbr_long=?, nbr_float=?, nbr_double=?, nbr_big_decimal=?, "
        + "str_varchar=?, str_fixed=?, str_lob=?, bin_blob=?, date_millis=?, local_date=?").argInteger(null).argLong(null)
        .argFloat(null).argDouble(null).argBigDecimal(null).argString(null).argString(null).argClobString(null)
        .argBlobBytes(null).argDate(null).argLocalDate(null).update(1);
    db.toSelect(
        "select nbr_integer, nbr_long, nbr_float, nbr_double, nbr_big_decimal, str_varchar, str_fixed, str_lob, "
            + "bin_blob, date_millis, local_date from dbtest").query(new RowsHandler<Void>() {
      @Override
      public Void process(Rows rs) throws Exception {
        assertTrue(rs.next());
        assertNull(rs.getIntegerOrNull(1));
        assertNull(rs.getIntegerOrNull("nbr_integer"));
        assertNull(rs.getLongOrNull(2));
        assertNull(rs.getLongOrNull("nbr_long"));
        assertNull(rs.getFloatOrNull(3));
        assertNull(rs.getFloatOrNull("nbr_float"));
        assertNull(rs.getDoubleOrNull(4));
        assertNull(rs.getDoubleOrNull("nbr_double"));
        assertNull(rs.getBigDecimalOrNull(5));
        assertNull(rs.getBigDecimalOrNull("nbr_big_decimal"));
        assertNull(rs.getStringOrNull(6));
        assertNull(rs.getStringOrNull("str_varchar"));
        assertNull(rs.getStringOrNull(7));
        assertNull(rs.getStringOrNull("str_fixed"));
        assertNull(rs.getClobStringOrNull(8));
        assertNull(rs.getClobStringOrNull("str_lob"));
        assertNull(rs.getBlobBytesOrNull(9));
        assertNull(rs.getBlobBytesOrNull("bin_blob"));
        assertNull(rs.getDateOrNull(10));
        assertNull(rs.getDateOrNull("date_millis"));
        assertNull(rs.getLocalDateOrNull(11));
        assertNull(rs.getLocalDateOrNull("local_date"));
        return null;
      }
    });
    assertEquals(1, db.toUpdate("update dbtest set nbr_integer=?, nbr_long=?, nbr_float=?, nbr_double=?, "
        + "nbr_big_decimal=?, str_varchar=?, str_fixed=?, str_lob=?, bin_blob=?, date_millis=?, local_date=?").argInteger(1)
        .argLong(2L).argFloat(3.2f).argDouble(4.2).argBigDecimal(bigDecimal).argString("Hello").argString("T")
        .argClobString("World").argBlobBytes("More".getBytes()).argDate(now).argLocalDate(localDateNow).update());
    db.toSelect(
        "select nbr_integer, nbr_long, nbr_float, nbr_double, nbr_big_decimal, str_varchar, str_fixed, str_lob, "
            + "bin_blob, date_millis, local_date from dbtest").query(new RowsHandler<Void>() {
      @Override
      public Void process(Rows rs) throws Exception {
        assertTrue(rs.next());
        assertEquals(new Integer(1), rs.getIntegerOrNull(1));
        assertEquals(new Integer(1), rs.getIntegerOrNull("nbr_integer"));
        assertEquals(new Long(2), rs.getLongOrNull(2));
        assertEquals(new Long(2), rs.getLongOrNull("nbr_long"));
        assertEquals(new Float(3.2f), rs.getFloatOrNull(3));
        assertEquals(new Float(3.2f), rs.getFloatOrNull("nbr_float"));
        assertEquals(new Double(4.2), rs.getDoubleOrNull(4));
        assertEquals(new Double(4.2), rs.getDoubleOrNull("nbr_double"));
        assertEquals(new BigDecimal("5.3"), rs.getBigDecimalOrNull(5));
        assertEquals(new BigDecimal("5.3"), rs.getBigDecimalOrNull("nbr_big_decimal"));
        assertEquals("Hello", rs.getStringOrNull(6));
        assertEquals("Hello", rs.getStringOrNull("str_varchar"));
        assertEquals("T", rs.getStringOrNull(7));
        assertEquals("T", rs.getStringOrNull("str_fixed"));
        assertEquals("World", rs.getClobStringOrNull(8));
        assertEquals("World", rs.getClobStringOrNull("str_lob"));
        assertArrayEquals("More".getBytes(), rs.getBlobBytesOrNull(9));
        assertArrayEquals("More".getBytes(), rs.getBlobBytesOrNull("bin_blob"));
        assertEquals(now, rs.getDateOrNull(10));
        assertEquals(now, rs.getDateOrNull("date_millis"));
        assertEquals(localDateNow, rs.getLocalDateOrNull(11));
        assertEquals(localDateNow, rs.getLocalDateOrNull("local_date"));
        return null;
      }
    });

    db.toUpdate("update dbtest set str_lob=?, bin_blob=?").argClobReader(null).argBlobStream(null).update(1);
    db.toSelect("select str_lob, bin_blob from dbtest").query(new RowsHandler<Void>() {
      @Override
      public Void process(Rows rs) throws Exception {
        assertTrue(rs.next());
        assertNull(rs.getClobStringOrNull(1));
        assertNull(rs.getClobStringOrNull("str_lob"));
        assertNull(rs.getBlobBytesOrNull(2));
        assertNull(rs.getBlobBytesOrNull("bin_blob"));
        return null;
      }
    });
    db.toUpdate("update dbtest set str_lob=?, bin_blob=?").argClobReader(new StringReader("World"))
        .argBlobStream(new ByteArrayInputStream("More".getBytes())).update(1);
    db.toSelect("select str_lob, bin_blob from dbtest").query(new RowsHandler<Void>() {
      @Override
      public Void process(Rows rs) throws Exception {
        assertTrue(rs.next());
        assertEquals("World", rs.getClobStringOrNull(1));
        assertEquals("World", rs.getClobStringOrNull("str_lob"));
        assertArrayEquals("More".getBytes(), rs.getBlobBytesOrNull(2));
        assertArrayEquals("More".getBytes(), rs.getBlobBytesOrNull("bin_blob"));
        return null;
      }
    });
  }

  @Test
  public void updateNamedArgs() {
    new Schema()
        .addTable("dbtest")
        .addColumn("pk").primaryKey().table()
        .addColumn("nbr_integer").asInteger().table()
        .addColumn("nbr_long").asLong().table()
        .addColumn("nbr_float").asFloat().table()
        .addColumn("nbr_double").asDouble().table()
        .addColumn("nbr_big_decimal").asBigDecimal(19, 9).table()
        .addColumn("str_varchar").asString(80).table()
        .addColumn("str_fixed").asStringFixed(1).table()
        .addColumn("str_lob").asClob().table()
        .addColumn("bin_blob").asBlob().table()
        .addColumn("date_millis").asDate().table()
        .addColumn("local_date").asLocalDate().table().schema().execute(db);

    BigDecimal bigDecimal = new BigDecimal("5.3");
    db.toInsert("insert into dbtest values (:pk,:a,:b,:c,:d,:e,:f,:sf,:g,:h,:i,:j)").argLong(":pk", 1L).argInteger(":a", 1)
        .argLong(":b", 2L).argFloat(":c", 3.2f).argDouble(":d", 4.2).argBigDecimal(":e", bigDecimal)
        .argString(":f", "Hello").argString(":sf", "T")
        .argClobString(":g", "World").argBlobBytes(":h", "More".getBytes())
        .argDate(":i", now).argLocalDate(":j", localDateNow).insert(1);
    db.toUpdate("update dbtest set nbr_integer=:a, nbr_long=:b, nbr_float=:c, nbr_double=:d, nbr_big_decimal=:e, "
        + "str_varchar=:f, str_fixed=:sf, str_lob=:g, bin_blob=:h, date_millis=:i, local_date=:j").argInteger(":a", null)
        .argLong(":b", null).argFloat(":c", null).argDouble(":d", null).argBigDecimal(":e", null)
        .argString(":f", null).argString(":sf", null)
        .argClobString(":g", null).argBlobBytes(":h", null)
        .argDate(":i", null).argLocalDate(":j", null).update(1);
    db.toSelect(
        "select nbr_integer, nbr_long, nbr_float, nbr_double, nbr_big_decimal, str_varchar, str_fixed, str_lob, "
            + "bin_blob, date_millis, local_date from dbtest").query(new RowsHandler<Void>() {
      @Override
      public Void process(Rows rs) throws Exception {
        assertTrue(rs.next());
        assertNull(rs.getIntegerOrNull(1));
        assertNull(rs.getIntegerOrNull("nbr_integer"));
        assertNull(rs.getLongOrNull(2));
        assertNull(rs.getLongOrNull("nbr_long"));
        assertNull(rs.getFloatOrNull(3));
        assertNull(rs.getFloatOrNull("nbr_float"));
        assertNull(rs.getDoubleOrNull(4));
        assertNull(rs.getDoubleOrNull("nbr_double"));
        assertNull(rs.getBigDecimalOrNull(5));
        assertNull(rs.getBigDecimalOrNull("nbr_big_decimal"));
        assertNull(rs.getStringOrNull(6));
        assertNull(rs.getStringOrNull("str_varchar"));
        assertNull(rs.getStringOrNull(7));
        assertNull(rs.getStringOrNull("str_fixed"));
        assertNull(rs.getClobStringOrNull(8));
        assertNull(rs.getClobStringOrNull("str_lob"));
        assertNull(rs.getBlobBytesOrNull(9));
        assertNull(rs.getBlobBytesOrNull("bin_blob"));
        assertNull(rs.getDateOrNull(10));
        assertNull(rs.getDateOrNull("date_millis"));
        assertNull(rs.getLocalDateOrNull(11));
        assertNull(rs.getLocalDateOrNull("local_date"));
        return null;
      }
    });
    db.toUpdate("update dbtest set nbr_integer=:a, nbr_long=:b, nbr_float=:c, nbr_double=:d, nbr_big_decimal=:e, "
        + "str_varchar=:f, str_fixed=:sf, str_lob=:g, bin_blob=:h, date_millis=:i, local_date=:j").argInteger(":a", 1)
        .argLong(":b", 2L).argFloat(":c", 3.2f).argDouble(":d", 4.2).argBigDecimal(":e", bigDecimal)
        .argString(":f", "Hello").argString(":sf", "T")
        .argClobString(":g", "World").argBlobBytes(":h", "More".getBytes())
        .argDate(":i", now).argLocalDate(":j", localDateNow).update(1);
    db.toSelect(
        "select nbr_integer, nbr_long, nbr_float, nbr_double, nbr_big_decimal, str_varchar, str_fixed, str_lob, "
            + "bin_blob, date_millis, local_date from dbtest").query(new RowsHandler<Void>() {
      @Override
      public Void process(Rows rs) throws Exception {
        assertTrue(rs.next());
        assertEquals(new Integer(1), rs.getIntegerOrNull(1));
        assertEquals(new Integer(1), rs.getIntegerOrNull("nbr_integer"));
        assertEquals(new Long(2), rs.getLongOrNull(2));
        assertEquals(new Long(2), rs.getLongOrNull("nbr_long"));
        assertEquals(new Float(3.2f), rs.getFloatOrNull(3));
        assertEquals(new Float(3.2f), rs.getFloatOrNull("nbr_float"));
        assertEquals(new Double(4.2), rs.getDoubleOrNull(4));
        assertEquals(new Double(4.2), rs.getDoubleOrNull("nbr_double"));
        assertEquals(new BigDecimal("5.3"), rs.getBigDecimalOrNull(5));
        assertEquals(new BigDecimal("5.3"), rs.getBigDecimalOrNull("nbr_big_decimal"));
        assertEquals("Hello", rs.getStringOrNull(6));
        assertEquals("Hello", rs.getStringOrNull("str_varchar"));
        assertEquals("T", rs.getStringOrNull(7));
        assertEquals("T", rs.getStringOrNull("str_fixed"));
        assertEquals("World", rs.getClobStringOrNull(8));
        assertEquals("World", rs.getClobStringOrNull("str_lob"));
        assertArrayEquals("More".getBytes(), rs.getBlobBytesOrNull(9));
        assertArrayEquals("More".getBytes(), rs.getBlobBytesOrNull("bin_blob"));
        assertEquals(now, rs.getDateOrNull(10));
        assertEquals(now, rs.getDateOrNull("date_millis"));
        assertEquals(localDateNow, rs.getLocalDateOrNull(11));
        assertEquals(localDateNow, rs.getLocalDateOrNull("local_date"));
        return null;
      }
    });

    db.toUpdate("update dbtest set str_lob=:a, bin_blob=:b").argClobReader(":a", null).argBlobStream(":b", null).update(1);
    db.toSelect("select str_lob, bin_blob from dbtest").query(new RowsHandler<Void>() {
      @Override
      public Void process(Rows rs) throws Exception {
        assertTrue(rs.next());
        assertNull(rs.getClobStringOrNull(1));
        assertNull(rs.getClobStringOrNull("str_lob"));
        assertNull(rs.getBlobBytesOrNull(2));
        assertNull(rs.getBlobBytesOrNull("bin_blob"));
        return null;
      }
    });
    db.toUpdate("update dbtest set str_lob=:a, bin_blob=:b").argClobReader(":a", new StringReader("World"))
        .argBlobStream(":b", new ByteArrayInputStream("More".getBytes())).update(1);
    db.toSelect("select str_lob, bin_blob from dbtest").query(new RowsHandler<Void>() {
      @Override
      public Void process(Rows rs) throws Exception {
        assertTrue(rs.next());
        assertEquals("World", rs.getClobStringOrNull(1));
        assertEquals("World", rs.getClobStringOrNull("str_lob"));
        assertArrayEquals("More".getBytes(), rs.getBlobBytesOrNull(2));
        assertArrayEquals("More".getBytes(), rs.getBlobBytesOrNull("bin_blob"));
        return null;
      }
    });
  }

  @Test
  public void nullValues() {
    new Schema()
        .addTable("dbtest")
        .addColumn("pk").primaryKey().table()
        .addColumn("nbr_integer").asInteger().table()
        .addColumn("nbr_long").asLong().table()
        .addColumn("nbr_float").asFloat().table()
        .addColumn("nbr_double").asDouble().table()
        .addColumn("nbr_big_decimal").asBigDecimal(19, 9).table()
        .addColumn("str_varchar").asString(80).table()
        .addColumn("str_fixed").asStringFixed(1).table()
        .addColumn("str_lob").asClob().table()
        .addColumn("bin_blob").asBlob().table()
        .addColumn("date_millis").asDate().table()
        .addColumn("local_date").asLocalDate().table().schema().execute(db);

    db.toInsert("insert into dbtest values (?,?,?,?,?,?,?,?,?,?,?,?)").argLong(1L).argInteger(null).argLong(null)
        .argFloat(null).argDouble(null).argBigDecimal(null).argString(null).argString(null).argClobString(null)
        .argBlobBytes(null).argDate(null).argLocalDate(null).insert(1);
    db.toSelect(
        "select nbr_integer, nbr_long, nbr_float, nbr_double, nbr_big_decimal, str_varchar, str_fixed, str_lob, "
            + "bin_blob, date_millis, local_date from dbtest").query(new RowsHandler<Void>() {
      @Override
      public Void process(Rows rs) throws Exception {
        assertTrue(rs.next());
        assertNull(rs.getIntegerOrNull(1));
        assertNull(rs.getIntegerOrNull("nbr_integer"));
        assertNull(rs.getLongOrNull(2));
        assertNull(rs.getLongOrNull("nbr_long"));
        assertNull(rs.getFloatOrNull(3));
        assertNull(rs.getFloatOrNull("nbr_float"));
        assertNull(rs.getDoubleOrNull(4));
        assertNull(rs.getDoubleOrNull("nbr_double"));
        assertNull(rs.getBigDecimalOrNull(5));
        assertNull(rs.getBigDecimalOrNull("nbr_big_decimal"));
        assertNull(rs.getStringOrNull(6));
        assertNull(rs.getStringOrNull("str_varchar"));
        assertNull(rs.getStringOrNull(7));
        assertNull(rs.getStringOrNull("str_fixed"));
        assertNull(rs.getClobStringOrNull(8));
        assertNull(rs.getClobStringOrNull("str_lob"));
        assertNull(rs.getBlobBytesOrNull(9));
        assertNull(rs.getBlobBytesOrNull("bin_blob"));
        assertNull(rs.getDateOrNull(10));
        assertNull(rs.getDateOrNull("date_millis"));
        assertNull(rs.getLocalDateOrNull(11));
        assertNull(rs.getLocalDateOrNull("local_date"));
        return null;
      }
    });
    db.toSelect("select str_lob, bin_blob from dbtest").query(new RowsHandler<Void>() {
      @Override
      public Void process(Rows rs) throws Exception {
        assertTrue(rs.next());
        assertNull(rs.getClobReaderOrNull(1));
        assertNull(rs.getBlobInputStreamOrNull(2));
        return null;
      }
    });
    db.toSelect("select str_lob, bin_blob from dbtest").query(new RowsHandler<Void>() {
      @Override
      public Void process(Rows rs) throws Exception {
        assertTrue(rs.next());
        assertNull(rs.getClobReaderOrNull("str_lob"));
        assertNull(rs.getBlobInputStreamOrNull("bin_blob"));
        return null;
      }
    });
  }

  @Test
  public void fromAny() {
    assertEquals(db.toSelect("select 1" + db.flavor().fromAny()).queryIntegerOrZero(), 1);
  }

  @Test
  public void metadataColumnNames() {
    new Schema().addTable("dbtest").addColumn("pk").primaryKey().schema().execute(db);

    db.toSelect("select Pk, Pk as Foo, Pk as \"Foo\" from dbtest").query(rs -> {
      assertArrayEquals(new String[] { "PK", "FOO", "Foo" }, rs.getColumnLabels());
      return null;
    });
  }

  @Test
  public void metadataColumnTypes() {
    String timestampColumnName = "data_millis";
    String dateColumnName = "local_date";
    new Schema()
        .addTable("dbtest")
        .addColumn(timestampColumnName).asDate().table()
        .addColumn(dateColumnName).asLocalDate().table().schema().execute(db);
    db.toSelect("select * from dbtest").query(new RowsHandler<Void>() {
      @Override
      public Void process(Rows rs) throws Exception {

        ResultSetMetaData metadata = rs.getMetadata();
        for (int i=1; i <= metadata.getColumnCount(); i++) {
          String columnName = metadata.getColumnName(i);
          String columnType = metadata.getColumnTypeName(i);

          if (columnName.equalsIgnoreCase(timestampColumnName)) {
            if ("sqlserver".equals(db.flavor().toString())) {
              assertEquals("DATETIME2", columnType.toUpperCase());
            } else{
              assertEquals("TIMESTAMP", columnType.toUpperCase());
            }
          } else if (columnName.equalsIgnoreCase(dateColumnName)) {
            assertEquals("DATE", columnType.toUpperCase());
          } else {
            fail("Unexpected column " + columnName + " of type " + columnType);
          }
        }
        return null;
      }
    });
  }

  @Test
  public void intervals() {
    new Schema().addTable("dbtest").addColumn("d").asDate().schema().execute(db);

    db.toInsert("insert into dbtest (d) values (?)").argDate(now).insert(1);

    assertEquals(1, db.toSelect("select count(1) from dbtest where d - interval '1' hour * ? < ?")
        .argInteger(2)
        .argDate(now)
        .queryIntegerOrZero());
  }

  @Test
  public void saveResultAsTable() {
    new Schema().addTable("dbtest")
        .addColumn("nbr_integer").asInteger().primaryKey().table()
        .addColumn("nbr_long").asLong().table()
        .addColumn("nbr_float").asFloat().table()
        .addColumn("nbr_double").asDouble().table()
        .addColumn("nbr_big_decimal").asBigDecimal(19, 9).table()
        .addColumn("str_varchar").asString(80).table()
        .addColumn("str_fixed").asStringFixed(1).table()
        .addColumn("str_lob").asClob().table()
        .addColumn("bin_blob").asBlob().table()
        .addColumn("boolean_flag").asBoolean().table()
        .addColumn("date_millis").asDate().table()
        .addColumn("local_date").asLocalDate().schema().execute(db);

    db.toInsert("insert into dbtest (nbr_integer, nbr_long, nbr_float, nbr_double, nbr_big_decimal, str_varchar,"
            + " str_fixed, str_lob, bin_blob, boolean_flag, date_millis, local_date) values (?,?,?,?,?,?,?,?,?,?,?,?)")
        .argInteger(Integer.MAX_VALUE).argLong(Long.MAX_VALUE).argFloat(Float.MAX_VALUE)
        .argDouble(Double.MAX_VALUE).argBigDecimal(new BigDecimal("123.456"))
        .argString("hello").argString("Z").argClobString("hello again")
        .argBlobBytes(new byte[] { '1', '2' }).argBoolean(true)
        .argDateNowPerApp().argLocalDate(localDateNow).insert(1);

    db.toInsert("insert into dbtest (nbr_integer, nbr_long, nbr_float, nbr_double, nbr_big_decimal, str_varchar,"
            + " str_fixed, str_lob, bin_blob, boolean_flag, date_millis, local_date) values (?,?,?,?,?,?,?,?,?,?,?,?)")
        .argInteger(Integer.MIN_VALUE).argLong(Long.MIN_VALUE).argFloat(0.000001f)
        .argDouble(Double.MIN_VALUE).argBigDecimal(new BigDecimal("-123.456"))
        .argString("goodbye").argString("A").argClobString("bye again")
        .argBlobBytes(new byte[] { '3', '4' }).argBoolean(false)
        .argDateNowPerApp().argLocalDate(localDateNow).insert(1);

    String expectedSchema = new Schema().addTable("dbtest2")
        .addColumn("nbr_integer").asInteger().table()
        .addColumn("nbr_long").asLong().table()
        .addColumn("nbr_float").asFloat().table()
        .addColumn("nbr_double").asDouble().table()
        .addColumn("nbr_big_decimal").asBigDecimal(19, 9).table()
        .addColumn("str_varchar").asString(80).table()
        .addColumn("str_fixed").asStringFixed(1).table()
        .addColumn("str_lob").asClob().table()
        .addColumn("bin_blob").asBlob().table()
        .addColumn("boolean_flag").asBoolean().table()
        .addColumn("date_millis").asDate().table()
        .addColumn("local_date").asLocalDate().schema().print(db.flavor());

    List<SqlArgs> args = db.toSelect("select nbr_integer, nbr_long, nbr_float, nbr_double, nbr_big_decimal,"
        + " str_varchar, str_fixed, str_lob, bin_blob, boolean_flag, date_millis, local_date from dbtest")
        .query(rs -> {
          List<SqlArgs> result = new ArrayList<>();
          while (rs.next()) {
            if (result.size() == 0) {
              db.dropTableQuietly("dbtest2");
              Schema schema = new Schema().addTableFromRow("dbtest2", rs).schema();
              assertEquals(expectedSchema, schema.print(db.flavor()));
              schema.execute(db);
            }
            result.add(SqlArgs.readRow(rs));
          }
          return result;
        });

    db.toInsert(Sql.insert("dbtest2", args)).insertBatch();

    assertEquals(2, db.toSelect("select count(*) from dbtest2").queryIntegerOrZero());

    assertEquals(
        db.toSelect("select nbr_integer, nbr_long, nbr_float, nbr_double, nbr_big_decimal,"
          + " str_varchar, str_fixed, str_lob, bin_blob, boolean_flag, date_millis, local_date from dbtest order by 1")
          .queryMany(SqlArgs::readRow),
        db.toSelect("select nbr_integer, nbr_long, nbr_float, nbr_double, nbr_big_decimal,"
          + " str_varchar, str_fixed, str_lob, bin_blob, boolean_flag, date_millis, local_date from dbtest2 order by 1")
        .  queryMany(SqlArgs::readRow));

    assertEquals(
        Arrays.asList(
            new SqlArgs().argInteger("nbr_integer", Integer.MIN_VALUE)
                    .argLong("nbr_long", Long.MIN_VALUE)
                    .argFloat("nbr_float", 0.000001f)
                    .argDouble("nbr_double", Double.MIN_VALUE)
                    .argBigDecimal("nbr_big_decimal", new BigDecimal("-123.456"))
                    .argString("str_varchar", "goodbye")
                    .argString("str_fixed", "A")
                    .argClobString("str_lob", "bye again")
                    .argBlobBytes("bin_blob", new byte[] { '3', '4' })
                    .argString("boolean_flag", "N")//.argBoolean("boolean_flag", false)
                    .argDate("date_millis", now)
                    .argLocalDate("local_date", localDateNow),
            new SqlArgs().argInteger("nbr_integer", Integer.MAX_VALUE)
                    .argLong("nbr_long", Long.MAX_VALUE)
                    .argFloat("nbr_float", Float.MAX_VALUE)
                    .argDouble("nbr_double", Double.MAX_VALUE)
                    .argBigDecimal("nbr_big_decimal", new BigDecimal("123.456"))
                    .argString("str_varchar", "hello")
                    .argString("str_fixed", "Z")
                    .argClobString("str_lob", "hello again")
                    .argBlobBytes("bin_blob", new byte[] { '1', '2' })
                    .argString("boolean_flag", "Y")//.argBoolean("boolean_flag", true)
                    .argDate("date_millis", now)
                    .argLocalDate("local_date", localDateNow)),
            db.toSelect("select nbr_integer, nbr_long, nbr_float, nbr_double, nbr_big_decimal,"
                    + " str_varchar, str_fixed, str_lob, bin_blob, boolean_flag, date_millis, local_date from dbtest2 order by 1")
                    .queryMany(SqlArgs::readRow));
  }


  @Test
  public void readSqlArgs() {
    new Schema().addTable("dbtest").addColumn("pk").primaryKey().schema().execute(db);

    db.toInsert("insert into dbtest (pk) values (?)").argInteger(1).insert(1);

    SqlArgs args = db.toSelect("select Pk, Pk as Foo, Pk as \"Foo\", pk as \"g arB#G!\","
        + " pk as \"TitleCase\" from dbtest")
        .queryOneOrThrow(SqlArgs::readRow);

    assertEquals(Arrays.asList("pk", "foo", "foo_2", "g_ar_b_g", "title_case"), args.names());
  }

  @Test
  public void clockSync() {
    db.assertTimeSynchronized();
  }

  @Test
  public void booleanColumn() {
    new Schema().addTable("dbtest")
        .addColumn("t").asBoolean().table()
        .addColumn("f").asBoolean().table()
        .addColumn("n").asBoolean().schema().execute(db);

    db.toInsert("insert into dbtest (t,f,n) values (?,:f,?)")
        .argBoolean(true).argBoolean("f", false).argBoolean(null).insert(1);
    db.toSelect("select t,f,n from dbtest")
        .query(new RowsHandler<Object>() {
          @Override
          public Object process(Rows rs) throws Exception {
            assertTrue(rs.next());
            assertTrue(rs.getBooleanOrNull() == Boolean.TRUE);
            assertTrue(rs.getBooleanOrNull() == Boolean.FALSE);
            assertNull(rs.getBooleanOrNull());
            return null;
          }
        });
    // Verify use of getBooleanOrNull(int) followed by default getBooleanOrNull() tracks
    // the current column index correctly (picks up where the explicit one left off)
    db.toSelect("select t,f,n from dbtest")
        .query(new RowsHandler<Object>() {
          @Override
          public Object process(Rows rs) throws Exception {
            assertTrue(rs.next());
            assertTrue(rs.getBooleanOrNull(2) == Boolean.FALSE);
            assertNull(rs.getBooleanOrNull());
            return null;
          }
        });
    // Verify use of getBooleanOrNull(String) followed by default getBooleanOrNull() tracks
    // the current column index correctly (picks up where the explicit one left off)
    db.toSelect("select t,f,n from dbtest")
        .query(new RowsHandler<Object>() {
          @Override
          public Object process(Rows rs) throws Exception {
            assertTrue(rs.next());
            assertTrue(rs.getBooleanOrNull("f") == Boolean.FALSE);
            assertNull(rs.getBooleanOrNull());
            return null;
          }
        });
    db.toSelect("select t,f,n from dbtest")
        .query(new RowsHandler<Object>() {
          @Override
          public Object process(Rows rs) throws Exception {
            assertTrue(rs.next());
            assertTrue(rs.getBooleanOrFalse());
            assertFalse(rs.getBooleanOrFalse());
            assertFalse(rs.getBooleanOrFalse());
            return null;
          }
        });
    // Verify use of getBooleanOrFalse(int) followed by default getBooleanOrFalse() tracks
    // the current column index correctly (picks up where the explicit one left off)
    db.toSelect("select t,f,n from dbtest")
        .query(new RowsHandler<Object>() {
          @Override
          public Object process(Rows rs) throws Exception {
            assertTrue(rs.next());
            assertFalse(rs.getBooleanOrFalse(2));
            assertFalse(rs.getBooleanOrFalse());
            return null;
          }
        });
    // Verify use of getBooleanOrFalse(String) followed by default getBooleanOrFalse() tracks
    // the current column index correctly (picks up where the explicit one left off)
    db.toSelect("select t,f,n from dbtest")
        .query(new RowsHandler<Object>() {
          @Override
          public Object process(Rows rs) throws Exception {
            assertTrue(rs.next());
            assertFalse(rs.getBooleanOrFalse("f"));
            assertFalse(rs.getBooleanOrFalse());
            return null;
          }
        });
    db.toSelect("select t,f,n from dbtest")
        .query(new RowsHandler<Object>() {
          @Override
          public Object process(Rows rs) throws Exception {
            assertTrue(rs.next());
            assertTrue(rs.getBooleanOrTrue());
            assertFalse(rs.getBooleanOrTrue());
            assertTrue(rs.getBooleanOrTrue());
            return null;
          }
        });
    // Verify use of getBooleanOrTrue(int) followed by default getBooleanOrTrue() tracks
    // the current column index correctly (picks up where the explicit one left off)
    db.toSelect("select t,f,n from dbtest")
        .query(new RowsHandler<Object>() {
          @Override
          public Object process(Rows rs) throws Exception {
            assertTrue(rs.next());
            assertFalse(rs.getBooleanOrTrue(2));
            assertTrue(rs.getBooleanOrTrue());
            return null;
          }
        });
    // Verify use of getBooleanOrTrue(String) followed by default getBooleanOrTrue() tracks
    // the current column index correctly (picks up where the explicit one left off)
    db.toSelect("select t,f,n from dbtest")
        .query(new RowsHandler<Object>() {
          @Override
          public Object process(Rows rs) throws Exception {
            assertTrue(rs.next());
            assertFalse(rs.getBooleanOrTrue("f"));
            assertTrue(rs.getBooleanOrTrue());
            return null;
          }
        });
    db.toDelete("delete from dbtest where t=? and f=?")
        .argBoolean(true).argBoolean(false).update(1);
    // Really should do this, but it seems Derby and PostgreSQL don't support it
//    db.toDelete("delete from dbtest where t=? and f=? and n=?")
//    .argBoolean(true).argBoolean(false).argBoolean(null).update(1);

    db.toInsert("insert into dbtest (t,f,n) values (:t,:f,:n)")
        .argBoolean("t", true).argBoolean("f", false).argBoolean("n", null).insert(1);
    db.toSelect("select t,f,n from dbtest")
        .query(new RowsHandler<Object>() {
          @Override
          public Object process(Rows rs) throws Exception {
            assertTrue(rs.next());
            assertTrue(rs.getBooleanOrNull(1) == Boolean.TRUE);
            assertTrue(rs.getBooleanOrNull(2) == Boolean.FALSE);
            assertNull(rs.getBooleanOrNull(3));
            assertTrue(rs.getBooleanOrFalse(1) == Boolean.TRUE);
            assertTrue(rs.getBooleanOrFalse(2) == Boolean.FALSE);
            assertTrue(rs.getBooleanOrFalse(3) == Boolean.FALSE);
            assertTrue(rs.getBooleanOrTrue(1) == Boolean.TRUE);
            assertTrue(rs.getBooleanOrTrue(2) == Boolean.FALSE);
            assertTrue(rs.getBooleanOrTrue(3) == Boolean.TRUE);
            assertTrue(rs.getBooleanOrNull("t") == Boolean.TRUE);
            assertTrue(rs.getBooleanOrNull("f") == Boolean.FALSE);
            assertNull(rs.getBooleanOrNull("n"));
            assertTrue(rs.getBooleanOrFalse("t") == Boolean.TRUE);
            assertTrue(rs.getBooleanOrFalse("f") == Boolean.FALSE);
            assertTrue(rs.getBooleanOrFalse("n") == Boolean.FALSE);
            assertTrue(rs.getBooleanOrTrue("t") == Boolean.TRUE);
            assertTrue(rs.getBooleanOrTrue("f") == Boolean.FALSE);
            assertTrue(rs.getBooleanOrTrue("n") == Boolean.TRUE);
            return null;
          }
        });
    assertTrue(db.toSelect("select t from dbtest").queryBooleanOrNull() == Boolean.TRUE);
    assertTrue(db.toSelect("select t from dbtest").queryBooleanOrFalse());
    assertTrue(db.toSelect("select t from dbtest").queryBooleanOrTrue());
    assertTrue(db.toSelect("select f from dbtest").queryBooleanOrNull() == Boolean.FALSE);
    assertFalse(db.toSelect("select f from dbtest").queryBooleanOrFalse());
    assertFalse(db.toSelect("select f from dbtest").queryBooleanOrTrue());
    assertNull(db.toSelect("select n from dbtest").queryBooleanOrNull());
    assertFalse(db.toSelect("select n from dbtest").queryBooleanOrFalse());
    assertTrue(db.toSelect("select n from dbtest").queryBooleanOrTrue());
  }

  @Test
  public void batchInsert() {
    new Schema().addTable("dbtest")
        .addColumn("pk").primaryKey().schema().execute(db);

    db.toInsert("insert into dbtest (pk) values (?)")
        .argInteger(1).batch()
        .argInteger(2).batch()
        .argInteger(3).batch().insertBatch();

    assertEquals(3, db.toSelect("select count(*) from dbtest").queryIntegerOrZero());
  }

  @Test
  public void batchInsertPkLong() {
    new Schema().addTable("dbtest")
        .addColumn("pk").primaryKey().table()
        .addColumn("s").asString(10).schema().execute(db);

    db.toInsert("insert into dbtest (pk,s) values (?,?)")
        .argPkLong(1L).argString("hi").batch()
        .argPkLong(2L).argString("hello").batch()
        .argPkLong(3L).argString("howdy").batch().insertBatch();

    assertEquals(3, db.toSelect("select count(*) from dbtest").queryIntegerOrZero());

    try {
      db.toInsert("insert into dbtest (pk,s) values (?,?)")
          .argPkLong(1L).argString("hi").batch()
          // argPkLong in different position ==> error
          .argString("hello").argPkLong(2L).batch().insertBatch();
      fail("Expecting an exception to be thrown");
    } catch (DatabaseException e) {
      assertEquals("The argPkLong() calls must be in the same position across batch records", e.getMessage());
    }

    try {
      db.toInsert("insert into dbtest (pk,s) values (?,?)")
          // multiple pk calls ==> error
          .argPkLong(1L).argPkLong(1L).batch()
          .argPkLong(2L).argString("hello").batch().insertBatch();
      fail("Expecting an exception to be thrown");
    } catch (DatabaseException e) {
      assertEquals("Only call one argPk*() method", e.getMessage());
    }
  }

  @Test
  public void batchInsertPkLongNamed() {
    new Schema().addTable("dbtest")
        .addColumn("pk").primaryKey().table()
        .addColumn("s").asString(10).schema().execute(db);

    db.toInsert("insert into dbtest (pk,s) values (:pk,?)")
        .argPkLong("pk", 1L).argString("hi").batch()
        .argPkLong("pk", 2L).argString("hello").batch()
        .argPkLong("pk", 3L).argString("howdy").batch().insertBatch();

    assertEquals(3, db.toSelect("select count(*) from dbtest").queryIntegerOrZero());

    db.toInsert("insert into dbtest (pk,s) values (:pk,?)")
        .batch().argPkLong("pk", 4L).argString("hi").batch()
        .argString("hello").argPkLong("pk", 5L).insertBatch();

    assertEquals(5, db.toSelect("select count(*) from dbtest").queryIntegerOrZero());

    try {
      db.toInsert("insert into dbtest (pk,s) values (:pk,?)")
          // multiple pk calls ==> error
          .argPkLong("pk", 1L).argPkLong(1L).batch().insertBatch();
      fail("Expecting an exception to be thrown");
    } catch (DatabaseException e) {
      assertEquals("Only call one argPk*() method", e.getMessage());
    }

    try {
      db.toInsert("insert into dbtest (pk,s) values (?,?)")
          .argPkLong("pk", 1L).argString("howdy").batch()
          // different name for pk on second batch ==> error
          .argPkLong("na", 2L).argString("hello").batch().insertBatch();
      fail("Expecting an exception to be thrown");
    } catch (DatabaseException e) {
      assertEquals("The primary key argument name must match across batch rows", e.getMessage());
    }
  }

  @Test
  public void batchInsertPkSeq() {
    db.dropSequenceQuietly("seq");
    new Schema().addTable("dbtest")
        .addColumn("pk").primaryKey().table()
        .addColumn("s").asString(10).schema()
        .addSequence("seq").schema().execute(db);

    db.toInsert("insert into dbtest (pk,s) values (?,?)")
        .argPkSeq("seq").argString("hi").batch()
        .argPkSeq("seq").argString("hello").batch()
        .argPkSeq("seq").argString("howdy").batch().insertBatch();

    assertEquals(3, db.toSelect("select count(*) from dbtest").queryIntegerOrZero());

    try {
      db.toInsert("insert into dbtest (pk,s) values (?,?)")
          .argPkSeq("seq").argString("hi").batch()
          // argPkLong in different position ==> error
          .argString("hello").argPkSeq("seq").batch().insertBatch();
      fail("Expecting an exception to be thrown");
    } catch (DatabaseException e) {
      assertEquals("The argPkSeq() calls must be in the same position across batch records", e.getMessage());
    }

    try {
      db.toInsert("insert into dbtest (pk,s) values (?,?)")
          // multiple pk calls ==> error
          .argPkSeq("seq").argPkSeq("seq").batch().insertBatch();
      fail("Expecting an exception to be thrown");
    } catch (DatabaseException e) {
      assertEquals("Only call one argPk*() method", e.getMessage());
    }
  }

  @Test
  public void batchInsertPkSeqNamed() {
    db.dropSequenceQuietly("seq");
    new Schema().addTable("dbtest")
        .addColumn("pk").primaryKey().table()
        .addColumn("s").asString(10).schema()
        .addSequence("seq").schema().execute(db);

    db.toInsert("insert into dbtest (pk,s) values (:pk,?)")
        .argPkSeq("pk", "seq").argString("hi").batch()
        .argPkSeq("pk", "seq").argString("hello").batch()
        .argPkSeq("pk", "seq").argString("howdy").batch().insertBatch();

    assertEquals(3, db.toSelect("select count(*) from dbtest").queryIntegerOrZero());

    db.toInsert("insert into dbtest (pk,s) values (:pk,?)")
        .batch().argPkSeq("pk", "seq").argString("hi").batch()
        .argString("hello").argPkSeq("pk", "seq").insertBatch();

    assertEquals(5, db.toSelect("select count(*) from dbtest").queryIntegerOrZero());

    try {
      db.toInsert("insert into dbtest (pk,s) values (:pk,?)")
          // multiple pk calls ==> error
          .argPkSeq("pk", "seq").argPkSeq("pk", "seq").batch().insertBatch();
      fail("Expecting an exception to be thrown");
    } catch (DatabaseException e) {
      assertEquals("Only call one argPk*() method", e.getMessage());
    }

    try {
      db.toInsert("insert into dbtest (pk,s) values (?,?)")
          .argPkSeq("pk", "seq").argString("howdy").batch()
          // different name for pk on second batch ==> error
          .argPkSeq("na", "seq").argString("hello").batch().insertBatch();
      fail("Expecting an exception to be thrown");
    } catch (DatabaseException e) {
      assertEquals("The primary key argument name must match across batch rows", e.getMessage());
    }
  }

  @Test
  public void bigClob() {
    new Schema().addTable("dbtest").addColumn("str_lob").asClob().schema().execute(db);

    StringBuilder buf = new StringBuilder();
    for (int i = 0; i < 40000; i++) {
      buf.append("0123456789");
    }
    final String longString = buf.toString();

    db.toInsert("insert into dbtest values (?)").argClobString(longString).insert(1);
    db.toSelect("select str_lob from dbtest").query(new RowsHandler<Void>() {
      @Override
      public Void process(Rows rs) throws Exception {
        assertTrue(rs.next());
        assertEquals(longString, rs.getClobStringOrNull(1));
        assertEquals(longString, rs.getClobStringOrNull("str_lob"));
        assertEquals(longString, readerToString(rs.getClobReaderOrNull(1)));
        return null;
      }
    });
    // Intentional slight variation here to test get()
    db.get().toSelect("select str_lob from dbtest").query(new RowsHandler<Void>() {
      @Override
      public Void process(Rows rs) throws Exception {
        assertTrue(rs.next());
        assertEquals(longString, readerToString(rs.getClobReaderOrNull("str_lob")));
        return null;
      }
    });
    db.toDelete("delete from dbtest").update(1);
    db.toInsert("insert into dbtest values (?)").argClobReader(new StringReader(longString)).insert(1);
    db.toSelect("select str_lob from dbtest").query(new RowsHandler<Void>() {
      @Override
      public Void process(Rows rs) throws Exception {
        assertTrue(rs.next());
        assertEquals(longString, rs.getClobStringOrNull(1));
        assertEquals(longString, rs.getClobStringOrNull("str_lob"));
        assertEquals(longString, readerToString(rs.getClobReaderOrNull(1)));
        return null;
      }
    });
    db.toSelect("select str_lob from dbtest").query(new RowsHandler<Void>() {
      @Override
      public Void process(Rows rs) throws Exception {
        assertTrue(rs.next());
        assertEquals(longString, readerToString(rs.getClobReaderOrNull("str_lob")));
        return null;
      }
    });
  }

  @Test
  public void bigBlob() {
    new Schema().addTable("dbtest").addColumn("bin_blob").asBlob().schema().execute(db);

    StringBuilder buf = new StringBuilder();
    for (int i = 0; i < 40000; i++) {
      buf.append("0123456789");
    }
    final byte[] bigBytes = buf.toString().getBytes();

    db.toInsert("insert into dbtest values (?)").argBlobBytes(bigBytes).insert(1);
    db.toSelect("select bin_blob from dbtest").query(new RowsHandler<Void>() {
      @Override
      public Void process(Rows rs) throws Exception {
        assertTrue(rs.next());
        assertArrayEquals(bigBytes, rs.getBlobBytesOrNull(1));
        assertArrayEquals(bigBytes, rs.getBlobBytesOrNull("bin_blob"));
        assertArrayEquals(bigBytes, inputStreamToString(rs.getBlobInputStreamOrNull(1)));
        return null;
      }
    });
    db.toSelect("select bin_blob from dbtest").query(new RowsHandler<Void>() {
      @Override
      public Void process(Rows rs) throws Exception {
        assertTrue(rs.next());
        assertArrayEquals(bigBytes, inputStreamToString(rs.getBlobInputStreamOrNull("bin_blob")));
        return null;
      }
    });
    db.toDelete("delete from dbtest").update(1);
    db.toInsert("insert into dbtest values (?)").argBlobStream(new ByteArrayInputStream(bigBytes)).insert(1);
    db.toSelect("select bin_blob from dbtest").query(new RowsHandler<Void>() {
      @Override
      public Void process(Rows rs) throws Exception {
        assertTrue(rs.next());
        assertArrayEquals(bigBytes, rs.getBlobBytesOrNull(1));
        assertArrayEquals(bigBytes, rs.getBlobBytesOrNull("bin_blob"));
        assertArrayEquals(bigBytes, inputStreamToString(rs.getBlobInputStreamOrNull(1)));
        return null;
      }
    });
    db.toSelect("select bin_blob from dbtest").query(new RowsHandler<Void>() {
      @Override
      public Void process(Rows rs) throws Exception {
        assertTrue(rs.next());
        assertArrayEquals(bigBytes, inputStreamToString(rs.getBlobInputStreamOrNull("bin_blob")));
        return null;
      }
    });
  }

  @Test
  public void argLocalDateTimeZones() {
    LocalDate januaryOne2000 = LocalDate.of(2000, Month.JANUARY, 1);

    // Verify we always get the same LocalDate regardless of time zone and DB across all drivers
    new Schema().addTable("dbtest").addColumn("i").asLocalDate().schema().execute(db);
    db.toInsert("insert into dbtest (i) values (?)").argLocalDate(januaryOne2000).insert(1);

    // Query without specifying a zone
    assertEquals(januaryOne2000,
        db.toSelect("select i from dbtest where i=?").argLocalDate(januaryOne2000).queryLocalDateOrNull());

    TimeZone defaultTZ = TimeZone.getDefault();

    String[] availableTZs = TimeZone.getAvailableIDs();
    for (String tz : availableTZs) {
      TimeZone.setDefault(TimeZone.getTimeZone(tz));
      LocalDate result =
        db.toSelect("select i from dbtest where i=?").argLocalDate(januaryOne2000).queryLocalDateOrNull();
      assertEquals(januaryOne2000, result);
    }
    TimeZone.setDefault(defaultTZ);
  }

  @Test
  public void argLocalDateLeapYear() {
    new Schema().addTable("dbtest").addColumn("testdate").asLocalDate().schema().execute(db);

    // Start by adding Febriary 28 and March 1 of 1900.  This was not a leap year.
    LocalDate feb1900 = LocalDate.of(1900, Month.FEBRUARY, 28);
    db.toInsert("insert into dbtest (testdate) values (?)").argLocalDate(feb1900).insert(1);
    assertEquals(feb1900,
        db.toSelect("select testdate from dbtest where testdate=?").argLocalDate(feb1900).queryLocalDateOrNull());

    LocalDate mar1900 = LocalDate.of(1900, Month.MARCH, 1);
    db.toInsert("insert into dbtest (testdate) values (?)").argLocalDate(mar1900).insert(1);
    assertEquals(mar1900,
        db.toSelect("select testdate from dbtest where testdate=?").argLocalDate(mar1900).queryLocalDateOrNull());

    // Now try Feb 28, 29, and March 1 of 2000.  This was a leap year
    LocalDate feb2000 = LocalDate.of(2000, Month.FEBRUARY, 28);
    db.toInsert("insert into dbtest (testdate) values (?)").argLocalDate(feb2000).insert(1);
    assertEquals(feb2000,
        db.toSelect("select testdate from dbtest where testdate=?").argLocalDate(feb2000).queryLocalDateOrNull());

    LocalDate febLeap2000 = LocalDate.of(2000, Month.FEBRUARY, 29);
    db.toInsert("insert into dbtest (testdate) values (?)").argLocalDate(febLeap2000).insert(1);
    assertEquals(febLeap2000,
        db.toSelect("select testdate from dbtest where testdate=?").argLocalDate(febLeap2000).queryLocalDateOrNull());

    LocalDate mar2000 = LocalDate.of(2000, Month.MARCH, 1);
    db.toInsert("insert into dbtest (testdate) values (?)").argLocalDate(mar2000).insert(1);
    assertEquals(mar2000,
        db.toSelect("select testdate from dbtest where testdate=?").argLocalDate(mar2000).queryLocalDateOrNull());
  }
  
  @Test
  public void argIntegerMinMax() {
    new Schema().addTable("dbtest").addColumn("i").asInteger().schema().execute(db);

    db.toInsert("insert into dbtest (i) values (?)").argInteger(Integer.MIN_VALUE).insert(1);
    assertEquals(new Integer(Integer.MIN_VALUE),
        db.toSelect("select i from dbtest where i=?").argInteger(Integer.MIN_VALUE).queryIntegerOrNull());

    db.toInsert("insert into dbtest (i) values (?)").argInteger(Integer.MAX_VALUE).insert(1);
    assertEquals(new Integer(Integer.MAX_VALUE),
        db.toSelect("select i from dbtest where i=?").argInteger(Integer.MAX_VALUE).queryIntegerOrNull());
  }

  @Test
  public void argLongMinMax() {
    new Schema().addTable("dbtest").addColumn("i").asLong().schema().execute(db);

    db.toInsert("insert into dbtest (i) values (?)").argLong(Long.MIN_VALUE).insert(1);
    assertEquals(new Long(Long.MIN_VALUE),
        db.toSelect("select i from dbtest where i=?").argLong(Long.MIN_VALUE).queryLongOrNull());

    db.toInsert("insert into dbtest (i) values (?)").argLong(Long.MAX_VALUE).insert(1);
    assertEquals(new Long(Long.MAX_VALUE),
        db.toSelect("select i from dbtest where i=?").argLong(Long.MAX_VALUE).queryLongOrNull());
  }

  @Test
  public void argFloatMinMax() {
    new Schema().addTable("dbtest").addColumn("i").asFloat().schema().execute(db);

    db.toInsert("insert into dbtest (i) values (?)").argFloat(Float.MIN_VALUE).insert(1);
    assertEquals(new Float(Float.MIN_VALUE),
        db.toSelect("select i from dbtest where i=?").argFloat(Float.MIN_VALUE).queryFloatOrNull());

    db.toInsert("insert into dbtest (i) values (?)").argFloat(Float.MAX_VALUE).insert(1);
    assertEquals(new Float(Float.MAX_VALUE),
        db.toSelect("select i from dbtest where i=?").argFloat(Float.MAX_VALUE).queryFloatOrNull());
  }

  @Test
  public void argFloatNaN() {
    new Schema().addTable("dbtest").addColumn("i").asFloat().schema().execute(db);

    db.toInsert("insert into dbtest (i) values (?)").argFloat(Float.NaN).insert(1);
    assertEquals(new Float(Float.NaN),
        db.toSelect("select i from dbtest where i=?").argFloat(Float.NaN).queryFloatOrNull());
  }

  @Test
  public void argFloatInfinity() {
    new Schema().addTable("dbtest").addColumn("i").asFloat().schema().execute(db);

    db.toInsert("insert into dbtest (i) values (?)").argFloat(Float.NEGATIVE_INFINITY).insert(1);
    assertEquals(new Float(Float.NEGATIVE_INFINITY),
        db.toSelect("select i from dbtest where i=?").argFloat(Float.NEGATIVE_INFINITY).queryFloatOrNull());

    db.toInsert("insert into dbtest (i) values (?)").argFloat(Float.POSITIVE_INFINITY).insert(1);
    assertEquals(new Float(Float.POSITIVE_INFINITY),
        db.toSelect("select i from dbtest where i=?").argFloat(Float.POSITIVE_INFINITY).queryFloatOrNull());
  }

  @Test
  public void argFloatZero() {
    new Schema().addTable("dbtest").addColumn("i").asFloat().schema().execute(db);

    db.toInsert("insert into dbtest (i) values (?)").argFloat(0f).insert(1);
    assertEquals(new Float(0f),
        db.toSelect("select i from dbtest where i=?").argFloat(0f).queryFloatOrNull());
  }

  @Test
  public void argFloatNegativeZero() {
    new Schema().addTable("dbtest").addColumn("i").asFloat().schema().execute(db);

    db.toInsert("insert into dbtest (i) values (?)").argFloat(-0f).insert(1);
    assertEquals(new Float(-0f),
        db.toSelect("select i from dbtest where i=?").argFloat(-0f).queryFloatOrNull());
  }

  @Test
  public void argDoubleMinMax() {
    new Schema().addTable("dbtest").addColumn("i").asDouble().schema().execute(db);

    db.toInsert("insert into dbtest (i) values (?)").argDouble(Double.MIN_VALUE).insert(1);
    assertEquals(new Double(Double.MIN_VALUE),
        db.toSelect("select i from dbtest where i=?").argDouble(Double.MIN_VALUE).queryDoubleOrNull());

    db.toInsert("insert into dbtest (i) values (?)").argDouble(Double.MAX_VALUE).insert(1);
    assertEquals(new Double(Double.MAX_VALUE),
        db.toSelect("select i from dbtest where i=?").argDouble(Double.MAX_VALUE).queryDoubleOrNull());
  }

  @Test
  public void argDoubleNaN() {
    new Schema().addTable("dbtest").addColumn("i").asDouble().schema().execute(db);

    db.toInsert("insert into dbtest (i) values (?)").argDouble(Double.NaN).insert(1);
    assertEquals(new Double(Double.NaN),
        db.toSelect("select i from dbtest where i=?").argDouble(Double.NaN).queryDoubleOrNull());
  }

  @Test
  public void argDoubleInfinity() {
    new Schema().addTable("dbtest").addColumn("i").asDouble().schema().execute(db);

    db.toInsert("insert into dbtest (i) values (?)").argDouble(Double.NEGATIVE_INFINITY).insert(1);
    assertEquals(new Double(Double.NEGATIVE_INFINITY),
        db.toSelect("select i from dbtest where i=?").argDouble(Double.NEGATIVE_INFINITY).queryDoubleOrNull());

    db.toInsert("insert into dbtest (i) values (?)").argDouble(Double.POSITIVE_INFINITY).insert(1);
    assertEquals(new Double(Double.POSITIVE_INFINITY),
        db.toSelect("select i from dbtest where i=?").argDouble(Double.POSITIVE_INFINITY).queryDoubleOrNull());
  }

  @Test
  public void argDoubleZero() {
    new Schema().addTable("dbtest").addColumn("i").asDouble().schema().execute(db);

    db.toInsert("insert into dbtest (i) values (?)").argDouble(0d).insert(1);
    assertEquals(new Double(0d),
        db.toSelect("select i from dbtest where i=?").argDouble(0d).queryDoubleOrNull());
  }

  @Test
  public void argDoubleNegativeZero() {
    new Schema().addTable("dbtest").addColumn("i").asDouble().schema().execute(db);

    db.toInsert("insert into dbtest (i) values (?)").argDouble(-0d).insert(1);
    assertEquals(new Double(-0d),
        db.toSelect("select i from dbtest where i=?").argDouble(-0d).queryDoubleOrNull());
  }

  @Test
  public void argBigDecimal38Precision0() {
    new Schema().addTable("dbtest").addColumn("i").asBigDecimal(38, 0).schema().execute(db);

    BigDecimal value = new BigDecimal("99999999999999999999999999999999999999"); // 38 digits
    db.toInsert("insert into dbtest (i) values (?)").argBigDecimal(value).insert(1);
    assertEquals(value,
        db.toSelect("select i from dbtest where i=?").argBigDecimal(value).queryBigDecimalOrNull());
  }

  @Test
  public void argBigDecimal38Precision1() {
    new Schema().addTable("dbtest").addColumn("i").asBigDecimal(38, 1).schema().execute(db);

    BigDecimal value = new BigDecimal("9999999999999999999999999999999999999.9"); // 38 digits
    db.toInsert("insert into dbtest (i) values (?)").argBigDecimal(value).insert(1);
    assertEquals(value,
        db.toSelect("select i from dbtest where i=?").argBigDecimal(value).queryBigDecimalOrNull());
  }

  @Test
  public void argBigDecimal38Precision37() {
    new Schema().addTable("dbtest").addColumn("i").asBigDecimal(38, 37).schema().execute(db);

    BigDecimal value = new BigDecimal("9.9999999999999999999999999999999999999"); // 38 digits
    db.toInsert("insert into dbtest (i) values (?)").argBigDecimal(value).insert(1);
    assertEquals(value,
        db.toSelect("select i from dbtest where i=?").argBigDecimal(value).queryBigDecimalOrNull());
  }

  @Test
  public void argBigDecimal38Precision38() {
    new Schema().addTable("dbtest").addColumn("i").asBigDecimal(38, 38).schema().execute(db);

    BigDecimal value = new BigDecimal("0.99999999999999999999999999999999999999"); // 38 digits
    db.toInsert("insert into dbtest (i) values (?)").argBigDecimal(value).insert(1);
    System.out.println(db.toSelect("select i from dbtest").queryBigDecimalOrNull());
    assertEquals(value,
        db.toSelect("select i from dbtest where i=?").argBigDecimal(value).queryBigDecimalOrNull());
  }

  @Test
  public void dropTableQuietly() {
    db.dropTableQuietly("dbtest");
    new Schema().addTable("dbtest").addColumn("pk").primaryKey().schema().execute(db);
    db.dropTableQuietly("dbtest");
    // Verify the quietly part really kicks in, since the table might have existed above
    db.dropTableQuietly("dbtest");
    new Schema().addTable("dbtest").addColumn("pk").primaryKey().schema().execute(db);
  }

  @Test
  public void dropSequenceQuietly() {
    db.dropSequenceQuietly("dbtest_seq");
    // Verify the quietly part really kicks in, since the sequence might have existed above
    db.dropSequenceQuietly("dbtest_seq");
  }

  @Test
  public void insertReturningPkSeq() {
    db.dropSequenceQuietly("dbtest_seq");

    db.ddl("create table dbtest (pk numeric)").execute();
    db.ddl("create sequence dbtest_seq start with 1").execute();

    assertEquals(new Long(1L), db.toInsert("insert into dbtest (pk) values (:seq)")
        .argPkSeq(":seq", "dbtest_seq").insertReturningPkSeq("pk"));
    assertEquals(new Long(2L), db.toInsert("insert into dbtest (pk) values (:seq)")
        .argPkSeq(":seq", "dbtest_seq").insertReturningPkSeq("pk"));
  }

  @Test
  public void insertReturningAppDate() {
    db.dropSequenceQuietly("dbtest_seq");

    new Schema()
        .addTable("dbtest")
          .addColumn("pk").primaryKey().table()
          .addColumn("d").asDate().table().schema()
        .addSequence("dbtest_seq").schema()
        .execute(db);

    db.toInsert("insert into dbtest (pk, d) values (:seq, :d)")
        .argPkSeq(":seq", "dbtest_seq")
        .argDateNowPerApp(":d")
        .insertReturning("dbtest", "pk", new RowsHandler<Object>() {
          @Override
          public Object process(Rows rs) throws Exception {
            assertTrue(rs.next());
            assertEquals(new Long(1L), rs.getLongOrNull(1));
            assertThat(rs.getDateOrNull(2), equalTo(now));
            assertFalse(rs.next());
            return null;
          }
        }, "d");
    assertEquals(new Long(1L), db.toSelect("select count(*) from dbtest where d=?").argDate(now).queryLongOrNull());
  }

  @Test
  public void quickQueries() {
    new Schema()
        .addTable("dbtest")
        .addColumn("pk").primaryKey().table()
        .addColumn("d").asDate().table()
        .addColumn("d2").asDate().table()
        .addColumn("d3").asLocalDate().table()
        .addColumn("d4").asLocalDate().table()
        .addColumn("s").asString(5).table()
        .addColumn("s2").asString(5).table()
        .addColumn("i").asInteger().table().schema()
        .execute(db);

    db.toInsert("insert into dbtest (pk, d, d3, s) values (?,?,?,?)")
        .argLong(1L).argDateNowPerApp().argLocalDate(localDateNow).argString("foo").insert(1);

    assertEquals(new Long(1L), db.toSelect("select pk from dbtest").queryLongOrNull());
    assertNull(db.toSelect("select pk from dbtest where 1=0").queryLongOrNull());
    assertNull(db.toSelect("select i from dbtest").queryLongOrNull());
    assertEquals(1L, db.toSelect("select pk from dbtest").queryLongOrZero());
    assertEquals(0L, db.toSelect("select pk from dbtest where 1=0").queryLongOrZero());
    assertEquals(0L, db.toSelect("select i from dbtest").queryLongOrZero());
    assertTrue(db.toSelect("select pk from dbtest").queryLongs().get(0) == 1L);
    assertTrue(db.toSelect("select pk from dbtest where 1=0").queryLongs().isEmpty());
    assertTrue(db.toSelect("select i from dbtest").queryLongs().isEmpty());

    assertEquals(new Integer(1), db.toSelect("select pk from dbtest").queryIntegerOrNull());
    assertNull(db.toSelect("select pk from dbtest where 1=0").queryIntegerOrNull());
    assertNull(db.toSelect("select i from dbtest").queryIntegerOrNull());
    assertEquals(1, db.toSelect("select pk from dbtest").queryIntegerOrZero());
    assertEquals(0, db.toSelect("select pk from dbtest where 1=0").queryIntegerOrZero());
    assertEquals(0, db.toSelect("select i from dbtest").queryIntegerOrZero());
    assertTrue(db.toSelect("select pk from dbtest").queryIntegers().get(0) == 1L);
    assertTrue(db.toSelect("select pk from dbtest where 1=0").queryIntegers().isEmpty());
    assertTrue(db.toSelect("select i from dbtest").queryIntegers().isEmpty());

    assertEquals("foo", db.toSelect("select s from dbtest").queryStringOrNull());
    assertNull(db.toSelect("select s from dbtest where 1=0").queryStringOrNull());
    assertNull(db.toSelect("select s2 from dbtest").queryStringOrNull());
    assertEquals("foo", db.toSelect("select s from dbtest").queryStringOrEmpty());
    assertEquals("", db.toSelect("select s from dbtest where 1=0").queryStringOrEmpty());
    assertEquals("", db.toSelect("select s2 from dbtest").queryStringOrEmpty());
    assertTrue(db.toSelect("select s from dbtest").queryStrings().get(0).equals("foo"));
    assertTrue(db.toSelect("select s from dbtest where 1=0").queryStrings().isEmpty());
    assertTrue(db.toSelect("select s2 from dbtest").queryStrings().isEmpty());

    assertEquals(now, db.toSelect("select d from dbtest").queryDateOrNull());
    assertNull(db.toSelect("select d from dbtest where 1=0").queryDateOrNull());
    assertNull(db.toSelect("select d2 from dbtest").queryDateOrNull());
    assertTrue(db.toSelect("select d from dbtest").queryDates().get(0).equals(now));
    assertTrue(db.toSelect("select d from dbtest where 1=0").queryDates().isEmpty());
    assertTrue(db.toSelect("select d2 from dbtest").queryDates().isEmpty());

    assertEquals(localDateNow, db.toSelect("select d3 from dbtest").queryLocalDateOrNull());
    assertNull(db.toSelect("select d3 from dbtest where 1=0").queryLocalDateOrNull());
    assertTrue(db.toSelect("select d3 from dbtest").queryLocalDates().get(0).equals(localDateNow));
    assertEquals(new Long(1L),
        db.toSelect("select count(*) from dbtest where d3=?").argLocalDate(localDateNow).queryLongOrNull());

    assertNull(db.toSelect("select d4 from dbtest").queryLocalDateOrNull());
    assertNull(db.toSelect("select d4 from dbtest where 1=0").queryLocalDateOrNull());
    assertTrue(db.toSelect("select d4 from dbtest").queryLocalDates().isEmpty());
  }

  @Test
  public void rowHandlerQueries() {
    new Schema()
        .addTable("dbtest")
        .addColumn("pk").primaryKey().schema()
        .execute(db);

    db.toInsert("insert into dbtest (pk) values (?)").argLong(1L).insert(1);
    db.toInsert("insert into dbtest (pk) values (?)").argLong(2L).insert(1);

    RowHandler<Long> rowHandler = new RowHandler<Long>() {
      @Override
      public Long process(Row r) throws Exception {
        return r.getLongOrNull();
      }
    };

    List<Long> many = db.toSelect("select pk from dbtest").queryMany(rowHandler);
    assertEquals(2, many.size());

    assertEquals(new Long(1), db.toSelect("select pk from dbtest where pk=1").queryOneOrNull(rowHandler));
    assertNull(db.toSelect("select pk from dbtest where pk=9").queryOneOrNull(rowHandler));
    try {
      db.toSelect("select pk from dbtest").queryOneOrNull(rowHandler);
      fail("Should have thrown an exception");
    } catch (ConstraintViolationException e) {
      assertEquals("Expected exactly one row to be returned but found multiple", e.getCause().getMessage());
    }
    try {
      db.toSelect("select pk from dbtest where pk=9").queryOneOrThrow(rowHandler);
      fail("Should have thrown an exception");
    } catch (ConstraintViolationException e) {
      assertEquals("Expected exactly one row to be returned but found none", e.getMessage());
    }

    assertEquals(new Long(1), db.toSelect("select pk from dbtest where pk=1").queryFirstOrNull(rowHandler));
    assertEquals(new Long(1), db.toSelect("select pk from dbtest order by 1").queryFirstOrNull(rowHandler));
    assertNull(db.toSelect("select pk from dbtest where pk=9").queryFirstOrNull(rowHandler));
    try {
      db.toSelect("select pk from dbtest where pk=9").queryFirstOrThrow(rowHandler);
      fail("Should have thrown an exception");
    } catch (ConstraintViolationException e) {
      assertEquals("Expected one or more rows to be returned but found none", e.getMessage());
    }
  }

  @Test
  public void nextSequenceValue() {
    db.dropSequenceQuietly("dbtest_seq");

    new Schema()
        .addSequence("dbtest_seq").schema()
        .execute(db);

    assertEquals(new Long(1L), db.nextSequenceValue("dbtest_seq"));
  }

  @Test
  public void insertReturningDbDate() {
    db.dropSequenceQuietly("dbtest_seq");

    new Schema()
        .addTable("dbtest")
          .addColumn("pk").primaryKey().table()
          .addColumn("d").asDate().table().schema()
        .addSequence("dbtest_seq").schema()
        .execute(db);

    Date dbNow = db.toInsert("insert into dbtest (pk, d) values (:seq, :d)")
        .argPkSeq(":seq", "dbtest_seq")
        .argDateNowPerDb(":d")
        .insertReturning("dbtest", "pk", new RowsHandler<Date>() {
          @Override
          public Date process(Rows rs) throws Exception {
            assertTrue(rs.next());
            assertEquals(new Long(1L), rs.getLongOrNull(1));
            Date dbDate = rs.getDateOrNull(2);
            assertFalse(rs.next());
            return dbDate;
          }
        }, "d");
//    System.err.println("***** d: " + db.select("select d from dbtest").queryString());
//    System.err.println("***** n: " + dbNow.getTime());
    assertEquals(new Long(1L), db.toSelect("select count(*) from dbtest where d=?").argDate(dbNow).queryLongOrNull());
  }

  @Test
  public void daylightSavings() {
    LocalDate lastStdDateSpring = LocalDate.of(2019, Month.MARCH, 9);
    LocalDate firstDSTDateSpring = LocalDate.of(2019, Month.MARCH, 10);

    // Verify that the original LocalDate matches the driver SQL LocalDate generated.
    StatementAdaptor adaptor = new StatementAdaptor(new OptionsDefault(db.flavor()));
    assertEquals(lastStdDateSpring.toString(), adaptor.nullLocalDate(lastStdDateSpring).toString());
    assertEquals(firstDSTDateSpring.toString(), adaptor.nullLocalDate(firstDSTDateSpring).toString());
  }

  @Test
  public void insertLocalDate() {
    // Date without time
    new Schema()
            .addTable("dbtest")
            .addColumn("d").asLocalDate().table().schema()
            .execute(db);

    LocalDate dateOfBirth = LocalDate.of(1951, Month.AUGUST, 9);
    db.toInsert("insert into dbtest (d) values (?)")
            .argLocalDate(dateOfBirth)
            .insert(1);

    LocalDate testDate = db.toSelect("select d from dbtest").queryLocalDateOrNull();
    assertEquals(dateOfBirth, testDate);
  }


  @Test
  public void localDateRoundTrip() {
    new Schema()
            .addTable("dbtest")
            .addColumn("d1").asLocalDate().table()
            .addColumn("d2").asLocalDate().table().schema()
            .execute(db);

    // Store current time as per the database
    db.toInsert("insert into dbtest (d1) values (?)")
            .argLocalDate(localDateNow)
            .insert(1);

    // Now pull it out, put it back in, and verify it matches in the database
    LocalDate queryRsDate = db.toSelect("select d1 from dbtest").queryLocalDateOrNull();

    db.toUpdate("update dbtest set d2=?")
            .argLocalDate(queryRsDate)
            .update(1);

    assertEquals(new Long(1L), db.toSelect("select count(*) from dbtest where d1=d2").queryLongOrNull());
  }

  /**
   * Enable retrying failed tests if they have the @Retry annotation.
   */
  @Rule
  public Retryable retry = new Retryable();


  /**
   * Make sure database times are inserted with at least millisecond precision.
   * This test is non-deterministic since it is checking the timestamp provided
   * by the database, so we use a retry mechanism to give it three attempts.
   */
  @Test @Retry
  public void dateMillis() {
    new Schema()
        .addTable("dbtest")
        .addColumn("d").asDate().table().schema()
        .execute(db);

    db.toInsert("insert into dbtest (d) values (?)")
        .argDateNowPerDb()
        .insert(1);

    Date dbNow = db.toSelect("select d from dbtest").queryDateOrNull();
    assertTrue("Timestamp had zero in the least significant digit", dbNow != null && dbNow.getTime() % 10 != 0);
  }

  @Test
  public void dateRoundTrip() {
    new Schema()
        .addTable("dbtest")
        .addColumn("d1").asDate().table()
        .addColumn("d2").asDate().table().schema()
        .execute(db);

    // Store current time as per the database
    db.toInsert("insert into dbtest (d1) values (?)")
        .argDateNowPerDb()
        .insert(1);

    // Now pull it out, put it back in, and verify it matches in the database
    Date dbNow = db.toSelect("select d1 from dbtest").queryDateOrNull();

    db.toUpdate("update dbtest set d2=?")
        .argDate(dbNow)
        .update(1);

//    System.err.println("***** d1: " + db.select("select to_char(d1) from dbtest").queryStringOrNull());
//    System.err.println("***** d2: " + db.select("select to_char(d2) from dbtest").queryStringOrNull());

    assertEquals(new Long(1L), db.toSelect("select count(*) from dbtest where d1=d2").queryLongOrNull());
  }

  @Test
  public void dateRoundTripTimezones() {
    new Schema()
        .addTable("dbtest")
        .addColumn("d").asDate().table().schema()
        .execute(db);

    Date date = new Date(166656789L);

    TimeZone.setDefault(TimeZone.getTimeZone("GMT-4:00"));

    db.toInsert("insert into dbtest (d) values (?)").argDate(date).insert(1);
    assertEquals(date, db.toSelect("select d from dbtest").queryDateOrNull());
    assertEquals("1970-01-02 18:17:36.789000-0400", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS000Z").format(
        db.toSelect("select d from dbtest").queryDateOrNull()));
    db.toDelete("delete from dbtest where d=?").argDate(date).update(1);

    TimeZone.setDefault(TimeZone.getTimeZone("GMT+4:00"));

    db.toInsert("insert into dbtest (d) values (?)").argDate(date).insert(1);
    assertEquals(date, db.toSelect("select d from dbtest").queryDateOrNull());
    assertEquals("1970-01-03 02:17:36.789000+0400", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS000Z").format(
        db.toSelect("select d from dbtest").queryDateOrNull()));
    db.toDelete("delete from dbtest where d=?").argDate(date).update(1);
  }

  /**
   * Verify the appropriate database flavor can correctly convert a {@code Date}
   * into a SQL function representing a conversion from string to timestamp. This
   * function is used to write debug SQL to the log in a way that could be manually
   * executed if desired.
   */
  @Test
  public void stringDateFunctions() {
    Date date = new Date(166656789L);
    System.out.println("Date: " + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS000Z").format(date));

    TimeZone.setDefault(TimeZone.getTimeZone("GMT-4:00"));

    new Schema()
        .addTable("dbtest")
        .addColumn("d").asDate().schema().execute(db);

    db.toInsert("insert into dbtest (d) values ("
        + db.flavor().dateAsSqlFunction(date, db.options().calendarForTimestamps()).replace(":", "::") + ")")
        .insert(1);

    assertEquals("1970-01-02 18:17:36.789000-0400", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS000Z").format(
        db.toSelect("select d from dbtest").queryDateOrNull()));

    // Now do some client operations in a different time zone
    TimeZone.setDefault(TimeZone.getTimeZone("GMT+4:00"));

    // Verify regular arg maps date the same way even though our TimeZone is now different
    db.toDelete("delete from dbtest where d=?").argDate(date).update(1);

    db.toInsert("insert into dbtest (d) values ("
        + db.flavor().dateAsSqlFunction(date, db.options().calendarForTimestamps()).replace(":", "::") + ")")
        .insert(1);

    assertEquals("1970-01-03 02:17:36.789000+0400", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS000Z").format(
        db.toSelect("select d from dbtest").queryDateOrNull()));

    // Verify the function maps correctly for equals operations as well
    db.toDelete("delete from dbtest where d=" + db.flavor().dateAsSqlFunction(date,
        db.options().calendarForTimestamps()).replace(":", "::")).update(1);
  }

  @Test
  public void mixPositionalAndNamedParameters() {
    new Schema()
        .addTable("dbtest")
        .addColumn("pk").primaryKey().table()
        .addColumn("d").asDate().table()
        .addColumn("a").asInteger().table().schema()
        .execute(db);

    db.toSelect("select pk as \"time:: now??\" from dbtest where a=? and d=:now")
        .argInteger(1).argDateNowPerDb("now").query(new RowsHandler<Object>() {
      @Override
      public Object process(Rows rs) throws Exception {
        assertFalse(rs.next());
        return null;
      }
    });
  }

  public String readerToString(Reader reader) throws IOException {
    char[] buffer = new char[1024];
    StringBuilder out = new StringBuilder();
    int byteCount;
    while ((byteCount = reader.read(buffer, 0, buffer.length)) >= 0) {
      out.append(buffer, 0, byteCount);
    }
    return out.toString();
  }

  public byte[] inputStreamToString(InputStream inputStream) throws IOException {
    byte[] buffer = new byte[1024];
    ByteArrayOutputStream out = new ByteArrayOutputStream();
    int byteCount;
    while ((byteCount = inputStream.read(buffer, 0, buffer.length)) >= 0) {
      out.write(buffer, 0, byteCount);
    }
    return out.toByteArray();
  }
}