content: package org.sql2o.extensions.postgres;
  
  import org.junit.Test;
  import org.junit.runner.RunWith;
  import org.junit.runners.Parameterized;
  import org.slf4j.Logger;
  import org.slf4j.LoggerFactory;
  import org.sql2o.Connection;
  import org.sql2o.Query;
  import org.sql2o.Sql2o;
  import org.sql2o.converters.UUIDConverter;
  import org.sql2o.data.Row;
  import org.sql2o.data.Table;
  import org.sql2o.quirks.PostgresQuirks;
  
  import java.util.Arrays;
  import java.util.Collection;
  import java.util.UUID;
  
  import static org.hamcrest.CoreMatchers.equalTo;
  import static org.hamcrest.CoreMatchers.is;
  import static org.junit.Assert.*;
  
  /**
   * Created with IntelliJ IDEA.
   * User: Lars Aaberg
   * Date: 1/19/13
   * Time: 10:58 PM
   * Test dedicated for postgres issues. Seems like the postgres jdbc driver behaves somewhat different from other jdbc drivers.
   * This test assumes that there is a local PostgreSQL server with a testdb database which can be accessed by user: test, pass: testtest
   */
  @RunWith(Parameterized.class)
  public class PostgresTest extends PostgresTestSupport {
  
      public PostgresTest(String url, String user, String pass, String testName) {
          super(url, user, pass, testName);
          logger.info("starting PostgresTest");
      }
  
      @Test
      public void testIssue10StatementsOnPostgres_noTransaction(){
  
          try {
              try (Connection connection = sql2o.open()) {
                  connection.createQuery("create table test_table(id SERIAL, val varchar(20))").executeUpdate();
              }
  
              try (Connection connection = sql2o.open()) {
                  Long key = connection.createQuery("insert into test_table (val) values(:val)", true)
                                       .addParameter("val", "something").executeUpdate().getKey(Long.class);
                  assertNotNull(key);
                  assertTrue(key > 0);
  
                  String selectSql = "select id, val from test_table";
                  Table resultTable = connection.createQuery(selectSql).executeAndFetchTable();
  
                  assertThat(resultTable.rows().size(), is(1));
                  Row resultRow = resultTable.rows().get(0);
                  assertThat(resultRow.getLong("id"), equalTo(key));
                  assertThat(resultRow.getString("val"), is("something"));
  
              }
  
          } finally {
              try (final Connection connection = sql2o.open();
                   final Query query = connection.createQuery("drop table if exists test_table")) {
                  query.executeUpdate();
              }
          }
      }
  
      @Test
      public void testIssue10_StatementsOnPostgres_withTransaction() {
  
  
          try(final Connection connection = sql2o.beginTransaction()){
  
              String createTableSql = "create table test_table(id SERIAL, val varchar(20))";
              connection.createQuery(createTableSql).executeUpdate();
  
              String insertSql = "insert into test_table (val) values(:val)";
              Long key = connection.createQuery(insertSql, true).addParameter("val", "something").executeUpdate().getKey(Long.class);
              assertNotNull(key);
              assertTrue(key > 0);
  
              String selectSql = "select id, val from test_table";
              Table resultTable = connection.createQuery(selectSql).executeAndFetchTable();
  
              assertThat(resultTable.rows().size(), is(1));
              Row resultRow = resultTable.rows().get(0);
              assertThat(resultRow.getLong("id"), equalTo(key));
              assertThat(resultRow.getString("val"), is("something"));
              // always rollback, as this is only for tesing purposes.
              connection.rollback();
  
          }
  
      }
  
      @Test
      public void testGetKeyOnSequence(){
          Connection connection = null;
  
          try {
              connection = sql2o.beginTransaction();
  
              String createSequenceSql = "create sequence testseq";
              connection.createQuery(createSequenceSql).executeUpdate();
  
              String createTableSql = "create table test_seq_table (id integer primary key, val varchar(20))";
              connection.createQuery(createTableSql).executeUpdate();
  
              String insertSql = "insert into test_seq_table(id, val) values (nextval('testseq'), 'something')";
              Long key = connection.createQuery(insertSql, true).executeUpdate().getKey(Long.class);
  
              assertThat(key, equalTo(1L));
  
              key = connection.createQuery(insertSql, true).executeUpdate().getKey(Long.class);
              assertThat(key, equalTo(2L));
          } finally {
              if (connection != null) {
                  connection.rollback();
              }
          }
      }
  
      @Test
      public void testKeyKeyOnSerial() {
          Connection connection = null;
  
          try {
              connection = sql2o.beginTransaction();
  
              String createTableSql = "create table test_serial_table (id serial primary key, val varchar(20))";
              connection.createQuery(createTableSql).executeUpdate();
  
              String insertSql = "insert into test_serial_table(val) values ('something')";
              Long key = connection.createQuery(insertSql, true).executeUpdate().getKey(Long.class);
  
              assertThat(key, equalTo(1L));
  
              key = connection.createQuery(insertSql, true).executeUpdate().getKey(Long.class);
              assertThat(key, equalTo(2L));
          } finally {
              if (connection != null) {
                  connection.rollback();
              }
          }
      }
  
      @Test
      public void testKeysKeyOnSerial() {
          Connection connection = null;
  
          try {
              connection = sql2o.beginTransaction();
  
              String createTableSql = "create table test_serial_table (val varchar(20), id serial primary key)";
              connection.createQuery(createTableSql).executeUpdate();
  
              String insertSql = "insert into test_serial_table(val) values ('something')";
              Object[] key = connection.createQuery(insertSql, true).executeUpdate().getKeys();
  
              assertThat((String) key[0], equalTo("something"));
              assertThat((Integer) key[1], equalTo(1));
  
              key = connection.createQuery(insertSql, true).executeUpdate().getKeys();
              assertThat((String) key[0], equalTo("something"));
              assertThat((Integer) key[1], equalTo(2));
          } finally {
              if (connection != null) {
                  connection.rollback();
              }
          }
      }
  
      @Test
      public void testUUID() {
  
          Connection connection = null;
  
          try {
              connection = sql2o.beginTransaction();
  
              String createSql = "create table uuidtable(id serial primary key, data uuid)";
              connection.createQuery(createSql).executeUpdate();
  
              UUID uuid = UUID.randomUUID();
  
              String insertSql = "insert into uuidtable(data) values (:data)";
              connection.createQuery(insertSql).addParameter("data", uuid).executeUpdate();
  
              String selectSql = "select data from uuidtable";
  
              UUID fetchedUuid = connection.createQuery(selectSql).executeScalar(UUID.class);
  
              assertThat(fetchedUuid, is(equalTo(uuid)));
          } finally {
              if (connection != null) {
                  connection.rollback();
              }
          }
  
      }
  
  //    @Test
  //    public void testArray() {
  //        try (Connection con = sql2o.open()) {
  //            con.createQuery("create table arraytest (id serial primary key, data text[])").executeUpdate();
  //
  //            final var data = new String[]{"foo", "bar", "baz"};
  //
  //            con.createQuery("insert into arraytest(data) values (:data)")
  //                    .addParameter("data", data)
  //                    .executeUpdate();
  //
  //            var fetchedData = con.createQuery("select data from arraytest")
  //                    .executeScalar(String[].class);
  //
  //            assertThat(fetchedData, is(equalTo(data)));
  //        }
  //    }
  
  
  
  }
  