package test;

import java.sql.*;

import org.junit.*;

import static org.junit.Assert.*;

/** These tests are designed to stress Statements on memory databases. */

public class StatementTest

{

private Connection conn;

private Statement stat;

@BeforeClass public static void forName() throws Exception {

Class.forName("org.sqlite.JDBC");

}

@Before public void connect() throws Exception {

conn = DriverManager.getConnection("jdbc:sqlite:");

stat = conn.createStatement();

}

@After public void close() throws SQLException {

stat.close();

conn.close();

}

@Test public void stmtUpdate() throws SQLException {

assertEquals(stat.executeUpdate("create table s1 (c1);"), 0);

assertEquals(stat.executeUpdate("insert into s1 values (0);"), 1);

assertEquals(stat.executeUpdate("insert into s1 values (1);"), 1);

assertEquals(stat.executeUpdate("insert into s1 values (2);"), 1);

ResultSet rs = stat.executeQuery("select count(c1) from s1;");

assertTrue(rs.next());

assertEquals(rs.getInt(1), 3);

rs.close();

assertEquals(stat.executeUpdate("update s1 set c1 = 5;"), 3);

assertEquals(stat.executeUpdate("delete from s1;"), 3);

// TODO: assertEquals(stat.executeUpdate("drop table s1;"), 0);

}

@Test public void emptyRS() throws SQLException {

ResultSet rs = stat.executeQuery("select null limit 0;");

assertFalse(rs.next());

rs.close();

}

@Test public void singleRowRS() throws SQLException {

ResultSet rs = stat.executeQuery("select " + Integer.MAX_VALUE + ";");

assertTrue(rs.next());

assertEquals(rs.getInt(1), Integer.MAX_VALUE);

assertEquals(rs.getString(1), Integer.toString(Integer.MAX_VALUE));

assertEquals(rs.getDouble(1),

new Integer(Integer.MAX_VALUE).doubleValue());

assertFalse(rs.next());

rs.close();

}

@Test public void twoRowRS() throws SQLException {

ResultSet rs = stat.executeQuery("select 9 union all select 7;");

assertTrue(rs.next());

assertEquals(rs.getInt(1), 9);

assertTrue(rs.next());

assertEquals(rs.getInt(1), 7);

assertFalse(rs.next());

rs.close();

}

@Test public void autoClose() throws SQLException {

conn.createStatement().executeQuery("select 1;");

}

@Test public void stringRS() throws SQLException {

ResultSet rs = stat.executeQuery("select \"Russell\";");

assertTrue(rs.next());

assertEquals(rs.getString(1), "Russell");

assertFalse(rs.next());

rs.close();

}

@Test public void execute() throws SQLException {

assertTrue(stat.execute("select null;"));

ResultSet rs = stat.getResultSet();

assertNotNull(rs);

assertTrue(rs.next());

assertNull(rs.getString(1));

assertTrue(rs.wasNull());

assertFalse(stat.getMoreResults());

assertEquals(stat.getUpdateCount(), -1);

assertTrue(stat.execute("select null;"));

assertFalse(stat.getMoreResults());

assertEquals(stat.getUpdateCount(), -1);

assertFalse(stat.execute("create table test (c1);"));

assertEquals(stat.getUpdateCount(), 0);

assertFalse(stat.getMoreResults());

assertEquals(stat.getUpdateCount(), -1);

}

@Test public void colNameAccess() throws SQLException {

assertEquals(stat.executeUpdate(

"create table tab (id, firstname, surname);"), 0);

assertEquals(stat.executeUpdate(

"insert into tab values (0, 'Bob', 'Builder');"), 1);

assertEquals(stat.executeUpdate(

"insert into tab values (1, 'Fred', 'Blogs');"), 1);

assertEquals(stat.executeUpdate(

"insert into tab values (2, 'John', 'Smith');"), 1);

ResultSet rs = stat.executeQuery("select * from tab;");

assertTrue(rs.next());

assertEquals(rs.getInt("id"), 0);

assertEquals(rs.getString("firstname"), "Bob");

assertEquals(rs.getString("surname"), "Builder");

assertTrue(rs.next());

assertEquals(rs.getInt("id"), 1);

assertEquals(rs.getString("firstname"), "Fred");

assertEquals(rs.getString("surname"), "Blogs");

assertTrue(rs.next());

assertEquals(rs.getInt("id"), 2);

assertEquals( rs.getString("id"), "2");

assertEquals(rs.getString("firstname"), "John");

assertEquals(rs.getString("surname"), "Smith");

assertFalse(rs.next());

rs.close();

assertEquals(stat.executeUpdate("drop table tab;"), 1);

}

@Test public void nulls() throws SQLException {

ResultSet rs = stat.executeQuery("select null union all select null;");

assertTrue(rs.next());

assertNull(rs.getString(1));

assertTrue(rs.wasNull());

assertTrue(rs.next());

assertNull(rs.getString(1));

assertTrue(rs.wasNull());

assertFalse(rs.next());

rs.close();

}

@Test public void tempTable() throws SQLException {

assertEquals(stat.executeUpdate("create temp table myTemp (a);"), 0);

assertEquals(stat.executeUpdate("insert into myTemp values (2);"), 1);

}

@Test public void insert1000() throws SQLException {

assertEquals(stat.executeUpdate("create table in1000 (a);"), 0);

conn.setAutoCommit(false);

for (int i=0; i < 1000; i++)

assertEquals(stat.executeUpdate(

"insert into in1000 values ("+i+");"), 1);

conn.commit();

ResultSet rs = stat.executeQuery("select count(a) from in1000;");

assertTrue(rs.next());

assertEquals(rs.getInt(1), 1000);

rs.close();

assertEquals(stat.executeUpdate("drop table in1000;"), 1);

}

private void assertArrayEq(int[] a, int[] b) {

assertNotNull(a);

assertNotNull(b);

assertEquals(a.length, b.length);

for (int i=0; i < a.length; i++)

assertEquals(a[i], b[i]);

}

@Test public void batch() throws SQLException {

stat.addBatch("create table batch (c1);");

stat.addBatch("insert into batch values (1);");

stat.addBatch("insert into batch values (1);");

stat.addBatch("insert into batch values (2);");

stat.addBatch("insert into batch values (3);");

stat.addBatch("insert into batch values (4);");

stat.addBatch("insert into batch values (5);");

stat.addBatch("insert into batch values (6);");

stat.addBatch("insert into batch values (7);");

stat.addBatch("insert into batch values (8);");

stat.addBatch("insert into batch values (9);");

stat.addBatch("insert into batch values (10);");

stat.addBatch("insert into batch values (11);");

stat.addBatch("insert into batch values (12);");

assertArrayEq(new int[] { 0,1,1,1,1,1,1,1,1,1,1,1,1,1 },

stat.executeBatch());

assertArrayEq(new int[] { }, stat.executeBatch());

stat.clearBatch();

stat.addBatch("insert into batch values (9);");

assertArrayEq(new int[] { 1 }, stat.executeBatch());

assertArrayEq(new int[] {}, stat.executeBatch());

stat.clearBatch();

stat.addBatch("insert into batch values (7);");

stat.addBatch("insert into batch values (7);");

assertArrayEq(new int[] { 1, 1 }, stat.executeBatch());

stat.clearBatch();

ResultSet rs = stat.executeQuery("select count(*) from batch;");

assertTrue(rs.next());

assertEquals(16, rs.getInt(1));

rs.close();

}

@Test public void closeOnFalseNext() throws SQLException {

stat.executeUpdate("create table t1 (c1);");

conn.createStatement().executeQuery("select * from t1;").next();

stat.executeUpdate("drop table t1;");

}

@Test public void getGeneratedKeys() throws SQLException {

ResultSet rs;

stat.executeUpdate("create table t1 (c1 integer primary key, v);");

stat.executeUpdate("insert into t1 (v) values ('red');");

rs = stat.getGeneratedKeys();

assertTrue(rs.next());

assertEquals(rs.getInt(1), 1);

rs.close();

stat.executeUpdate("insert into t1 (v) values ('blue');");

rs = stat.getGeneratedKeys();

assertTrue(rs.next());

assertEquals(rs.getInt(1), 2);

rs.close();

}

@Test public void isBeforeFirst() throws SQLException {

ResultSet rs = stat.executeQuery("select 1 union all select 2;");

assertTrue(rs.isBeforeFirst());

assertTrue(rs.next());

assertTrue(rs.isFirst());

assertEquals(rs.getInt(1), 1);

assertTrue(rs.next());

assertFalse(rs.isBeforeFirst());

assertFalse(rs.isFirst());

assertEquals(rs.getInt(1), 2);

assertFalse(rs.next());

assertFalse(rs.isBeforeFirst());

rs.close();

}

@Test public void columnNaming() throws SQLException {

stat.executeUpdate("create table t1 (c1 integer);");

stat.executeUpdate("create table t2 (c1 integer);");

stat.executeUpdate("insert into t1 values (1);");

stat.executeUpdate("insert into t2 values (1);");

ResultSet rs = stat.executeQuery(

"select a.c1 AS c1 from t1 a, t2 where a.c1=t2.c1;");

assertTrue(rs.next());

assertEquals(rs.getInt("c1"), 1);

rs.close();

}

@Test public void maxRows() throws SQLException {

stat.setMaxRows(1);

assertEquals(stat.getMaxRows(), 1);

ResultSet rs = stat.executeQuery("select 1 union select 2;");

assertTrue(rs.next());

assertEquals(rs.getInt(1), 1);

assertFalse(rs.next());

rs.close();

}

@Test public void nullDate() throws SQLException {

ResultSet rs = stat.executeQuery("select null;");

assertTrue(rs.next());

assertEquals(rs.getDate(1), null);

assertEquals(rs.getTime(1), null);

assertEquals(rs.getTimestamp(1), null);

rs.close();

}

@Test(expected= SQLException.class)

public void ambiguousColumnNaming() throws SQLException {

stat.executeUpdate("create table t1 (c1 int);");

stat.executeUpdate("create table t2 (c1 int, c2 int);");

stat.executeUpdate("insert into t1 values (1);");

stat.executeUpdate("insert into t2 values (2, 1);");

ResultSet rs = stat.executeQuery(

"select a.c1, b.c1 from t1 a, t2 b where a.c1=b.c2;");

assertTrue(rs.next());

assertEquals(rs.getInt("c1"), 1);

rs.close();

}

@Test(expected= SQLException.class)

public void failToDropWhenRSOpen() throws SQLException {

stat.executeUpdate("create table t1 (c1);");

stat.executeUpdate("insert into t1 values (4);");

stat.executeUpdate("insert into t1 values (4);");

conn.createStatement().executeQuery("select * from t1;").next();

stat.executeUpdate("drop table t1;");

}

@Test(expected= SQLException.class)

public void executeNoRS() throws SQLException {

assertFalse(stat.execute("insert into test values (8);"));

stat.getResultSet();

}

@Test(expected= SQLException.class)

public void executeClearRS() throws SQLException {

assertTrue(stat.execute("select null;"));

assertNotNull(stat.getResultSet());

assertFalse(stat.getMoreResults());

stat.getResultSet();

}

@Test(expected= BatchUpdateException.class)

public void batchReturnsResults() throws SQLException {

stat.addBatch("select null;");

stat.executeBatch();

}

@Test(expected= SQLException.class)

public void noSuchTable() throws SQLException {

stat.executeQuery("select * from doesnotexist;");

}

@Test(expected= SQLException.class)

public void noSuchCol() throws SQLException {

stat.executeQuery("select notacol from (select 1);");

}

@Test(expected= SQLException.class)

public void noSuchColName() throws SQLException {

ResultSet rs = stat.executeQuery("select 1;");

assertTrue(rs.next());

rs.getInt("noSuchColName");

}

}