content: package org.sql2o.issues;
  
  import org.hsqldb.jdbcDriver;
  import org.joda.time.DateTime;
  import org.junit.Test;
  import org.junit.runner.RunWith;
  import org.junit.runners.Parameterized;
  import org.sql2o.Connection;
  import org.sql2o.Sql2o;
  import org.sql2o.Sql2oException;
  import org.sql2o.data.Row;
  import org.sql2o.data.Table;
  import org.sql2o.issues.pojos.Issue1Pojo;
  import org.sql2o.issues.pojos.KeyValueEntity;
  
  import java.sql.Driver;
  import java.sql.DriverManager;
  import java.sql.SQLException;
  import java.util.Arrays;
  import java.util.Collection;
  import java.util.List;
  
  import static org.junit.Assert.*;
  
  /**
   * Created by IntelliJ IDEA.
   * User: lars
   * Date: 10/17/11
   * Time: 9:02 PM
   * This class is to test for reported issues.
   */
  @RunWith(Parameterized.class)
  public class IssuesTest {
  
      @Parameterized.Parameters(name = "{index} - {4}")
      public static Collection<Object[]> getData(){
          return Arrays.asList(new Object[][]{
                  {null, "jdbc:h2:mem:test;MODE=MSSQLServer;DB_CLOSE_DELAY=-1","sa", "", "H2 test" },
                  {new jdbcDriver(), "jdbc:hsqldb:mem:testmemdb", "SA", "", "HyperSQL DB test"}
          });
      }
  
      private Sql2o sql2o;
      private String url;
      private String user;
      private String pass;
  
      public IssuesTest(Driver driverToRegister, String url, String user, String pass, String testName){
          if (driverToRegister != null) {
              try {
                  DriverManager.registerDriver(driverToRegister);
              } catch (SQLException e) {
                  throw new RuntimeException("could not register driver '" + driverToRegister.getClass().getName() + "'", e);
              }
          }
  
          this.sql2o = new Sql2o(url, user, pass);
  
          this.url = url;
          this.user = user;
          this.pass = pass;
  
          if ("HyperSQL DB test".equals( testName )) {
              sql2o.createQuery("set database sql syntax MSS true").executeUpdate();
          }
      }
  
      /**
       * Tests for issue #1 https://github.com/aaberg/sql2o/issues/1
       *
       * Issue:
       * I have a case where I need to override/modify the value loaded from db.
       * I want to do this in a setter but the current version of sql2o modifies the property directly.
       *
       * Comment:
       * The priority was wrong. Sql2o would try to set the field first, and afterwards the setter. The priority should be
       * the setter first and the field after.
       */
      @Test public void testSetterPriority(){
          Sql2o sql2o = new Sql2o(url, user, pass);
          Issue1Pojo pojo = sql2o.createQuery("select 1 val from (values(0))").executeAndFetchFirst(Issue1Pojo.class);
  
          assertEquals(2, pojo.val);
  
      }
  
      /**
       *  Tests for issue #2 https://github.com/aaberg/sql2o/issues/2
       *
       *  Issue: NPE - should instead tell what the problem is
       *
       */
      @Test public void testForFieldDoesNotExistException(){
          Sql2o sql2o = new Sql2o(url, user, pass);
  
  
          try{
              KeyValueEntity pojo = sql2o.createQuery("select 1 id, 'something' foo from (values(0))").executeAndFetchFirst(KeyValueEntity.class);
          }
          catch(Sql2oException ex){
              assertTrue(ex.getMessage().contains("Could not map"));
          }
      }
  
      /**
       *  Tests for issue #4 https://github.com/aaberg/sql2o/issues/4
       *
       *  NPE when typing wrong column name in row.get(...)
       *  Also, column name should not be case sensitive, if sql2o not is in casesensitive property is false.
       */
      @Test public void testForNpeInRowGet(){
          sql2o.createQuery("create table issue4table(id integer identity primary key, val varchar(20))").executeUpdate();
          
          sql2o.createQuery("insert into issue4table (val) values (:val)")
              .addParameter("val", "something").addToBatch()
              .addParameter("val", "something else").addToBatch()
              .addParameter("val", "hello").addToBatch()
              .executeBatch();
          
          Table table = sql2o.createQuery("select * from issue4table").executeAndFetchTable();
  
          Row row0 = table.rows().get(0);
          String row0Val = row0.getString("vAl");
          
          assertEquals("something", row0Val);
          
          Row row1 = table.rows().get(1);
          boolean failed = false;
          
          try{
              String row1Value = row1.getString("ahsHashah"); // Should fail with an sql2o exception
          }
          catch(Sql2oException ex){
              failed = true;
  
              assertTrue(ex.getMessage().startsWith("Column with name 'ahsHashah' does not exist"));
          }
  
          assertTrue("assert that exception occurred", failed);
                  
      }
      
      public static class Issue5POJO{
          public int id;
          public int val;
      }
      
      public static class Issue5POJO2{
          public int id;
          public int val;
  
          public int getVal() {
              return val;
          }
  
          public void setVal(int val) {
              this.val = val;
          }
      }
  
      /**
       *  Tests for issue #5 https://github.com/aaberg/sql2o/issues/5
       *  crashes if the POJO has a int field where we try to set a null value
       */
      @Test public void testForNullToSimpeType(){
          sql2o.createQuery("create table issue5table(id int identity primary key, val integer)").executeUpdate();
  
          sql2o.createQuery("insert into issue5table(val) values (:val)").addParameter("val", (Object)null).executeUpdate();
  
          List<Issue5POJO> list1 = sql2o.createQuery("select * from issue5table").executeAndFetch(Issue5POJO.class);
          
          List<Issue5POJO2> list2 = sql2o.createQuery("select * from issue5table").executeAndFetch(Issue5POJO2.class);
          
          assertEquals(1, list1.size());
          assertEquals(1, list2.size());
          assertEquals(0, list1.get(0).val);
          assertEquals(0, list2.get(0).getVal());
      }
  
      /**
       * Tests for issue #9 https://github.com/aaberg/sql2o/issues/9
       * When running a select query with column labels (aliases) in HSQLDB, sql2o is still trying to use column names
       * wheWhen running a select query with column labels (aliases) in HSQLDB, sql2o is still trying to use column names when mapping to java classes. This is caused by a behavior in HSQLDB, that is different from most other databases. the ResultSet.getColumnName() method will still return the real column name, even though a label was used. To get the label with HSQLDB, ResultSet.getColumnLabel().n mapping to java classes. This is caused by a behavior in HSQLDB, that is different from most other databases.
       * the ResultSet.getColumnName() method will still return the real column name, even though a label was used. To get
       * the label with HSQLDB, ResultSet.getColumnLabel().
       */
      @Test public void testForLabelErrorInHsqlDb(){
          sql2o.createQuery("create table issue9test (id integer identity primary key, val varchar(50))").executeUpdate();
  
          String insertSql = "insert into issue9test(val) values (:val)";
          sql2o.createQuery(insertSql).addParameter("val", "something").executeUpdate();
          sql2o.createQuery(insertSql).addParameter("val", "something else").executeUpdate();
          sql2o.createQuery(insertSql).addParameter("val", "something third").executeUpdate();
  
          List<Issue9Pojo> pojos = sql2o.createQuery("select id, val theVal from issue9Test").executeAndFetch(Issue9Pojo.class);
  
          assertEquals(3, pojos.size());
          assertEquals("something", pojos.get(0).theVal);
  
      }
  
      public static enum WhatEverEnum{
          VAL, ANOTHER_VAL;
      }
  
      @Test public void testForNullPointerExceptionInAddParameterMethod() {
          sql2o.createQuery("create table issue11test (id integer identity primary key, val varchar(50), adate datetime)").executeUpdate();
  
          String insertSql = "insert into issue11test (val, adate) values (:val, :date)";
          sql2o.createQuery(insertSql).addParameter("val", WhatEverEnum.VAL).addParameter("date", new DateTime()).executeUpdate();
          DateTime dtNull = null;
          WhatEverEnum enumNull = null;
  
          sql2o.createQuery(insertSql).addParameter("val", enumNull).addParameter("date", dtNull).executeUpdate();
      }
  
      /**
       * Test for issue #132 ( https://github.com/aaberg/sql2o/issues/132 )
       * Ref change done in pull request #75
       * Also see comment on google groups
       * https://groups.google.com/forum/#!topic/sql2o/3H4XJIv-i04
  
       * If a column cannot be mapped to a property, an exception should be thrown. Today it is silently ignored.
       */
      @Test public void testErrorWhenFieldDoesntExist() {
  
          class LocalPojo {
              private long id;
              private String strVal;
  
              public long getId() {
                  return id;
              }
  
              public String getStrVal() {
                  return strVal;
              }
          }
  
          String createQuery = "create table testErrorWhenFieldDoesntExist(id_val integer primary key, str_val varchar(100))";
  
          try (Connection connection = sql2o.open()) {
              connection.createQuery(createQuery).executeUpdate();
  
              String insertSql = "insert into testErrorWhenFieldDoesntExist(id_val, str_val) values (:val1, :val2)";
              connection.createQuery(insertSql)
                      .addParameter("val1", 1)
                      .addParameter("val2", "test")
                      .executeUpdate();
  
              Exception ex = null;
              try {
                  // This is expected to fail to map columns and throw an exception.
                  LocalPojo p = connection.createQuery("select * from testErrorWhenFieldDoesntExist")
                          .executeAndFetchFirst(LocalPojo.class);
              } catch(Exception e) {
                  ex = e;
              }
              assertNotNull(ex);
  
          }
      }
  
      public static class Issue9Pojo {
          public int id;
          public String theVal;
      }
  
      static class ThePojo148 {
          public int id;
          public String name;
      }
  
      /**
       * Test for issue #148 (https://github.com/aaberg/sql2o/issues/148)
       * ## IndexOutOfRange exception
       * When a resultset has multiple columns with the same name, sql2o 1.5.1 will throw an IndexOutOfRange exception when calling executeAndFetchTable() method.
       */
      @Test
      public void testIndexOutOfRangeExceptionWithMultipleColumnsWithSameName() {
  
          String sql = "select 11 id, 'something' name, 'something else' name from (values(0))";
  
          ThePojo148 p;
          Table t;
          try (Connection connection = sql2o.open()) {
              p = connection.createQuery(sql).executeAndFetchFirst(ThePojo148.class);
  
              t = connection.createQuery(sql).executeAndFetchTable();
          }
  
  
  
          assertEquals(11, p.id);
          assertEquals("something else", p.name);
  
          assertEquals(11, (int)t.rows().get(0).getInteger("id"));
          assertEquals("something else", t.rows().get(0).getString("name"));
      }
  
      static class ThePojo {
          public int id;
          public int intval;
          public String strval;
      }
  
      /**
       * Reproduce issue #142 (https://github.com/aaberg/sql2o/issues/142)
       */
      @Test
      public void testIgnoreSqlComments() {
  
  
  
          String createSql = "create table testIgnoreSqlComments(id integer primary key, intval integer, strval varchar(100))";
  
          String insertQuery =
                  "insert into testIgnoreSqlComments (id, intval, strval)\n " +
                  "-- It's a comment!\n" +
                  "values (:id, :intval, :strval);";
  
          String fetchQuery =
                  "select id, intval, strval\n" +
                  "-- a 'comment'\n" +
                  "from testIgnoreSqlComments\n" +
                  "/* and, it's another type of comment!*/" +
                  "where intval = :param";
  
          try (Connection connection = sql2o.open()) {
              connection.createQuery(createSql).executeUpdate();
  
              for (int idx = 0; idx < 100; idx++) {
                  int intval = idx % 10;
                  connection.createQuery(insertQuery)
                          .addParameter("id", idx)
                          .addParameter("intval", intval)
                          .addParameter("strval", "teststring" + idx)
                          .executeUpdate();
              }
  
              List<ThePojo> resultList = connection.createQuery(fetchQuery)
                      .addParameter("param", 5)
                      .executeAndFetch(ThePojo.class);
  
              assertEquals(10, resultList.size());
          }
      }
  
      static class Pojo {
          public int id;
          public String val1;
      }
  
      /**
       * Testing for github issue #134.
       * Add option to ignore mapping errors
       */
      @Test
      public void testIssue134ThrowOnMappingErrorProperty() {
          String sql = "select 1 id, 'foo' val1, 'bar' val2 from (values(0))";
  
          try (Connection connection = sql2o.open()) {
  
              try {
                  Pojo pojo = connection.createQuery(sql).executeAndFetchFirst(Pojo.class);
                  fail("Expeced an exception to be thrown");
              } catch(Sql2oException e) {
                  assertEquals("Could not map VAL2 to any property.", e.getMessage());
              }
  
              Pojo pojo = connection.createQuery(sql).throwOnMappingFailure(false).executeAndFetchFirst(Pojo.class);
  
              assertEquals(1, pojo.id);
              assertEquals("foo", pojo.val1);
          }
      }
  
      @Test
      public void testIssue166OneCharacterParameterFail() {
          try (Connection connection = sql2o.open()) {
              connection.createQuery("create table testIssue166OneCharacterParameterFail(id integer, val varchar(10))")
                      .executeUpdate();
  
              // This because of the :v parameter.
              connection.createQuery("insert into testIssue166OneCharacterParameterFail(id, val) values(:id, :v)")
                      .addParameter("id", 1)
                      .addParameter("v", "foobar")
                      .executeUpdate();
  
  
              int cnt = connection.createQuery("select count(*) from testIssue166OneCharacterParameterFail where id = :p")
                      .addParameter("p", 1)
                      .executeScalar(Integer.class);
  
              assertEquals(1, cnt);
          }
      }
  
      @Test
      public void testIssue149NullPointerWhenUsingWrongParameterName() {
  
          try(Connection connection = sql2o.open()) {
              connection.createQuery("create table issue149 (id integer primary key, val varchar(20))").executeUpdate();
              connection.createQuery("insert into issue149(id, val) values (:id, :val)")
                      .addParameter("id", 1)
                      .addParameter("asdsa", "something") // spell-error in parameter name
                      .executeUpdate();
  
              fail("Expected exception!!");
          } catch(Sql2oException ex) {
              // awesome!
          } catch(Throwable t) {
              fail("A " + t.getClass().getName() + " was thrown, but An " + Sql2oException.class.getName() + " was expected");
          }
      }
  }
  