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.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 {
private Sql2o sql2o;
@Parameterized.Parameters(name = "{index} - {4}")
public static Collection<Object[]> getData(){
return Arrays.asList(new Object[][]{
{"jdbc:postgresql:testdb", "test", "testtest", "Official postgres driver"},
// {"jdbc:pgsql://localhost/testdb", "test", "testtest", "Impossibl postgres driver"}
});
}
private Logger logger = LoggerFactory.getLogger(PostgresTest.class);
public PostgresTest(String url, String user, String pass, String testName) {
logger.info(testName);
sql2o = new Sql2o(url, user, pass, new PostgresQuirks(){
{
// make sure we use default UUID converter.
converters.put(UUID.class, new UUIDConverter());
}
});
}
@Test
public void testIssue10StatementsOnPostgres_noTransaction(){
try {
String createTableSql = "create table test_table(id SERIAL, val varchar(20))";
sql2o.createQuery(createTableSql).executeUpdate();
String insertSql = "insert into test_table (val) values(:val)";
Long key = (Long)sql2o.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 = sql2o.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 {
String dropTableSql = "drop table if exists test_table";
sql2o.createQuery(dropTableSql).executeUpdate();
}
}
@Test
public void testIssue10_StatementsOnPostgres_withTransaction() {
Connection connection = null;
try{
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 = (Long)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"));
} finally {
// always rollback, as this is only for tesing purposes.
if (connection != null) {
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 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();
}
}
}
}