@Test
public void testEmptyCommit() throws SQLException {
// Empty auto commit commit
connection.setAutoCommit(true);
connection.commit();
// Empty commit
connection.setAutoCommit(false);
connection.commit();
}
@Test
public void testUpsertValuesWithExpression() throws Exception {
long ts = nextTimestamp();
ensureTableCreated(getUrl(), "IntKeyTest", null, ts - 2);
Properties props = new Properties();
props.setProperty(
PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1)); // Execute at timestamp 1
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String upsert = "UPSERT INTO IntKeyTest VALUES(-1)";
PreparedStatement upsertStmt = conn.prepareStatement(upsert);
int rowsInserted = upsertStmt.executeUpdate();
assertEquals(1, rowsInserted);
upsert = "UPSERT INTO IntKeyTest VALUES(1+2)";
upsertStmt = conn.prepareStatement(upsert);
rowsInserted = upsertStmt.executeUpdate();
assertEquals(1, rowsInserted);
conn.commit();
conn.close();
props.setProperty(
PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 1
conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String select = "SELECT i FROM IntKeyTest";
ResultSet rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(-1, rs.getInt(1));
assertTrue(rs.next());
assertEquals(3, rs.getInt(1));
assertFalse(rs.next());
}
@Test
public void testUpsertDateValues() throws Exception {
long ts = nextTimestamp();
Date now = new Date(System.currentTimeMillis());
ensureTableCreated(getUrl(), TestUtil.PTSDB_NAME, null, ts - 2);
Properties props = new Properties();
props.setProperty(
PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1)); // Execute at timestamp 1
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String dateString = "1999-01-01 02:00:00";
PreparedStatement upsertStmt =
conn.prepareStatement(
"upsert into ptsdb(inst,host,date) values('aaa','bbb',to_date('" + dateString + "'))");
int rowsInserted = upsertStmt.executeUpdate();
assertEquals(1, rowsInserted);
upsertStmt =
conn.prepareStatement(
"upsert into ptsdb(inst,host,date) values('ccc','ddd',current_date())");
rowsInserted = upsertStmt.executeUpdate();
assertEquals(1, rowsInserted);
conn.commit();
props.setProperty(
PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 1
conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String select = "SELECT date,current_date() FROM ptsdb";
ResultSet rs = conn.createStatement().executeQuery(select);
Date then = new Date(System.currentTimeMillis());
assertTrue(rs.next());
Date date = DateUtil.parseDate(dateString);
assertEquals(date, rs.getDate(1));
assertTrue(rs.next());
assertTrue(rs.getDate(1).after(now) && rs.getDate(1).before(then));
assertFalse(rs.next());
}
@Test
public void testUpsertValuesWithDate() throws Exception {
long ts = nextTimestamp();
Properties props = new Properties();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts));
Connection conn = DriverManager.getConnection(getUrl(), props);
conn.createStatement()
.execute("create table UpsertDateTest (k VARCHAR not null primary key,date DATE)");
conn.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 5));
conn = DriverManager.getConnection(getUrl(), props);
conn.createStatement()
.execute("upsert into UpsertDateTest values ('a',to_date('2013-06-08 00:00:00'))");
conn.commit();
conn.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
conn = DriverManager.getConnection(getUrl(), props);
ResultSet rs =
conn.createStatement().executeQuery("select k,to_char(date) from UpsertDateTest");
assertTrue(rs.next());
assertEquals("a", rs.getString(1));
assertEquals("2013-06-08 00:00:00", rs.getString(2));
}
@Test
public void testExecuteAndFetchWithNulls() {
String sql =
"create table testExecWithNullsTbl ("
+ "id int identity primary key, "
+ "text varchar(255), "
+ "aNumber int, "
+ "aLongNumber bigint)";
sql2o.createQuery(sql, "testExecuteAndFetchWithNulls").executeUpdate();
Connection connection = sql2o.beginTransaction();
Query insQuery =
connection.createQuery(
"insert into testExecWithNullsTbl (text, aNumber, aLongNumber) values(:text, :number, :lnum)");
insQuery
.addParameter("text", "some text")
.addParameter("number", 2)
.addParameter("lnum", 10L)
.executeUpdate();
insQuery
.addParameter("text", "some text")
.addParameter("number", (Integer) null)
.addParameter("lnum", 10L)
.executeUpdate();
insQuery
.addParameter("text", (String) null)
.addParameter("number", 21)
.addParameter("lnum", (Long) null)
.executeUpdate();
insQuery
.addParameter("text", "some text")
.addParameter("number", 1221)
.addParameter("lnum", 10)
.executeUpdate();
insQuery
.addParameter("text", "some text")
.addParameter("number", 2311)
.addParameter("lnum", 12)
.executeUpdate();
connection.commit();
List<Entity> fetched =
sql2o.createQuery("select * from testExecWithNullsTbl").executeAndFetch(Entity.class);
assertTrue(fetched.size() == 5);
assertNull(fetched.get(2).text);
assertNotNull(fetched.get(3).text);
assertNull(fetched.get(1).aNumber);
assertNotNull(fetched.get(2).aNumber);
assertNull(fetched.get(2).aLongNumber);
assertNotNull(fetched.get(3).aLongNumber);
}
@Test
public void testUpsertVarCharWithMaxLength() throws Exception {
long ts = nextTimestamp();
Properties props = new Properties();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts));
Connection conn = DriverManager.getConnection(getUrl(), props);
conn.createStatement()
.execute(
"create table phoenix_uuid_mac (mac_md5 VARCHAR not null primary key,raw_mac VARCHAR)");
conn.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 5));
conn = DriverManager.getConnection(getUrl(), props);
conn.createStatement().execute("upsert into phoenix_uuid_mac values ('00000000591','a')");
conn.createStatement().execute("upsert into phoenix_uuid_mac values ('000000005919','b')");
conn.commit();
conn.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
conn = DriverManager.getConnection(getUrl(), props);
ResultSet rs = conn.createStatement().executeQuery("select max(mac_md5) from phoenix_uuid_mac");
assertTrue(rs.next());
assertEquals("000000005919", rs.getString(1));
conn.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 15));
conn = DriverManager.getConnection(getUrl(), props);
conn.createStatement()
.execute(
"upsert into phoenix_uuid_mac values ('000000005919adfasfasfsafdasdfasfdasdfdasfdsafaxxf1','b')");
conn.commit();
conn.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 20));
conn = DriverManager.getConnection(getUrl(), props);
rs = conn.createStatement().executeQuery("select max(mac_md5) from phoenix_uuid_mac");
assertTrue(rs.next());
assertEquals("000000005919adfasfasfsafdasdfasfdasdfdasfdsafaxxf1", rs.getString(1));
conn.close();
}
private void loadData(long ts) throws SQLException {
Properties props = new Properties(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts));
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
insertRow(conn, "Report1", 10);
insertRow(conn, "Report2", 10);
insertRow(conn, "Report3", 30);
insertRow(conn, "Report4", 30);
insertRow(conn, "SOQL1", 10);
insertRow(conn, "SOQL2", 10);
insertRow(conn, "SOQL3", 30);
insertRow(conn, "SOQL4", 30);
conn.commit();
conn.close();
}
@Test
public void insert1000() throws SQLException {
stat.executeUpdate("create table in1000 (a);");
PreparedStatement prep = conn.prepareStatement("insert into in1000 values (?);");
conn.setAutoCommit(false);
for (int i = 0; i < 1000; i++) {
prep.setInt(1, i);
prep.executeUpdate();
}
conn.commit();
ResultSet rs = stat.executeQuery("select count(a) from in1000;");
assertTrue(rs.next());
assertEquals(1000, rs.getInt(1));
rs.close();
}
@Test
public void testFailedTransactionRollback() throws SQLException {
connection.setAutoCommit(false);
// Failed statement
mockConnection.setError(42, "bad");
Statement s = connection.createStatement();
try {
s.executeUpdate("DELETE bad");
fail("expected exception");
} catch (SQLException e) {
}
assertNull(mockConnection.lastFinish);
// All future statements should also fail
mockConnection.setUpdateCount(42);
try {
s.executeUpdate("DELETE good");
fail("expected exception");
} catch (SQLException e) {
}
connection.enableStrictMode(true);
try {
connection.commit();
fail("expected exception");
} catch (SQLException e) {
}
// rollback this transaction
connection.rollback();
assertNull(mockConnection.lastFinish);
try {
// Can't rollback: no transaction!
connection.rollback();
fail("expected exception");
} catch (SQLException e) {
}
assertEquals(42, s.executeUpdate("DELETE good"));
}
@Test
public void createEncrypted() throws SQLException, IOException {
File tmp = File.createTempFile("sqlitetest", ".db");
tmp.deleteOnExit();
String url = "jdbc:sqlite:" + tmp.getAbsolutePath();
Properties props = new Properties();
final String password = "******"dog";
props.put("key", password);
Connection conn = DriverManager.getConnection(url, props);
conn.setAutoCommit(false);
Statement st = conn.createStatement();
st.executeUpdate("create table ants (col int)");
st.executeUpdate("insert into ants values( 300 )");
st.executeUpdate("insert into ants values( 400 )");
st.close();
conn.commit();
conn.close();
// Try reading without key.
props.remove("key");
conn = DriverManager.getConnection(url, props);
try {
st = conn.createStatement();
ResultSet rs = st.executeQuery("select count(*) from ants");
fail("Database not encrypted.");
} catch (SQLException ignore) {
}
conn.close();
props.put("key", password);
conn = DriverManager.getConnection(url, props);
st = conn.createStatement();
ResultSet rs = st.executeQuery("select count(*) from ants");
assertTrue(rs.next());
assertEquals(2, rs.getInt(1));
conn.close();
}
@Test
public void testBatch() {
sql2o
.createQuery(
"create table User(\n"
+ "id int identity primary key,\n"
+ "name varchar(20),\n"
+ "email varchar(255),\n"
+ "text varchar(100))")
.executeUpdate();
String insQuery = "insert into User(name, email, text) values (:name, :email, :text)";
Connection con = sql2o.beginTransaction();
int[] inserted =
con.createQuery(insQuery)
.addParameter("name", "test")
.addParameter("email", "*****@*****.**")
.addParameter("text", "something exciting")
.addToBatch()
.addParameter("name", "test2")
.addParameter("email", "*****@*****.**")
.addParameter("text", "something exciting too")
.addToBatch()
.addParameter("name", "test3")
.addParameter("email", "*****@*****.**")
.addParameter("text", "blablabla")
.addToBatch()
.executeBatch()
.getBatchResult();
con.commit();
assertEquals(3, inserted.length);
for (int i : inserted) {
assertEquals(1, i);
}
deleteUserTable();
}
@Test
public void testIntegerPK() throws Exception {
int[] testNumbers = {
Integer.MIN_VALUE,
Integer.MIN_VALUE + 1,
-2,
-1,
0,
1,
2,
Integer.MAX_VALUE - 1,
Integer.MAX_VALUE
};
ensureTableCreated(getUrl(), "PKIntValueTest");
Properties props = new Properties();
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String upsert = "UPSERT INTO PKIntValueTest VALUES(?)";
PreparedStatement stmt = conn.prepareStatement(upsert);
for (int i = 0; i < testNumbers.length; i++) {
stmt.setInt(1, testNumbers[i]);
stmt.execute();
}
conn.commit();
conn.close();
String select = "SELECT COUNT(*) from PKIntValueTest";
ResultSet rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select = "SELECT count(*) FROM PKIntValueTest where pk >= " + Integer.MIN_VALUE;
rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select =
"SELECT pk FROM PKIntValueTest where pk >= "
+ Integer.MIN_VALUE
+ " GROUP BY pk ORDER BY pk ASC NULLS LAST";
rs = conn.createStatement().executeQuery(select);
for (int i = 0; i < testNumbers.length; i++) {
assertTrue(rs.next());
assertEquals(testNumbers[i], rs.getInt(1));
}
assertFalse(rs.next());
// NOTE: This case currently fails with an error message:
// "Overflow trying to get next key for [-1, -1, -1, -1]"
select = "SELECT count(*) FROM PKIntValueTest where pk <= " + Integer.MAX_VALUE;
rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select =
"SELECT pk FROM PKIntValueTest where pk <= "
+ Integer.MAX_VALUE
+ " GROUP BY pk ORDER BY pk DESC NULLS LAST";
rs = conn.createStatement().executeQuery(select);
for (int i = testNumbers.length - 1; i >= 0; i--) {
assertTrue(rs.next());
assertEquals(testNumbers[i], rs.getInt(1));
}
assertFalse(rs.next());
// NOTE: This case currently fails since it is not retrieving the negative values.
select = "SELECT count(*) FROM PKIntValueTest where pk >= " + INTEGER_MIN_MINUS_ONE;
rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select =
"SELECT pk FROM PKIntValueTest where pk >= "
+ INTEGER_MIN_MINUS_ONE
+ " GROUP BY pk ORDER BY pk ASC NULLS LAST ";
rs = conn.createStatement().executeQuery(select);
for (int i = 0; i < testNumbers.length; i++) {
assertTrue(rs.next());
assertEquals(testNumbers[i], rs.getInt(1));
}
assertFalse(rs.next());
// NOTE: This test case fails because it is not retrieving positive values.
select = "SELECT count(*) FROM PKIntValueTest where pk <= " + INTEGER_MAX_PLUS_ONE;
rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select =
"SELECT pk FROM PKIntValueTest where pk <= "
+ INTEGER_MAX_PLUS_ONE
+ " GROUP BY pk ORDER BY pk DESC NULLS LAST";
rs = conn.createStatement().executeQuery(select);
for (int i = testNumbers.length - 1; i >= 0; i--) {
assertTrue(rs.next());
assertEquals(testNumbers[i], rs.getInt(1));
}
assertFalse(rs.next());
}
@Test
public void testBigIntKV() throws Exception {
// NOTE: Due to how we parse negative long, -9223372036854775808L, the minimum value of
// bigint is not recognizable in the current version. As a result, we start with
// Long.MIN_VALUE+1 as the smallest value.
long[] testNumbers = {
Long.MIN_VALUE + 1,
Long.MIN_VALUE + 2,
-2L,
-1L,
0L,
1L,
2L,
Long.MAX_VALUE - 1,
Long.MAX_VALUE
};
ensureTableCreated(getUrl(), "KVBigIntValueTest");
Properties props = new Properties();
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String upsert = "UPSERT INTO KVBigIntValueTest VALUES(?,?)";
PreparedStatement stmt = conn.prepareStatement(upsert);
for (int i = 0; i < testNumbers.length; i++) {
stmt.setLong(1, i);
stmt.setLong(2, testNumbers[i]);
stmt.execute();
}
conn.commit();
conn.close();
String select = "SELECT COUNT(*) from KVBigIntValueTest";
ResultSet rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select = "SELECT count(*) FROM KVBigIntValueTest where kv >= " + (Long.MIN_VALUE + 1);
rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select =
"SELECT kv FROM KVBigIntValueTest WHERE kv >= "
+ (Long.MIN_VALUE + 1)
+ " GROUP BY kv ORDER BY kv ASC NULLS LAST";
rs = conn.createStatement().executeQuery(select);
for (int i = 0; i < testNumbers.length; i++) {
assertTrue(rs.next());
assertEquals(testNumbers[i], rs.getLong(1));
}
assertFalse(rs.next());
select = "SELECT count(*) FROM KVBigIntValueTest where kv <= " + Long.MAX_VALUE;
rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select =
"SELECT kv FROM KVBigIntValueTest WHERE kv <= "
+ Long.MAX_VALUE
+ " GROUP BY kv ORDER BY kv DESC NULLS LAST";
rs = conn.createStatement().executeQuery(select);
for (int i = testNumbers.length - 1; i >= 0; i--) {
assertTrue(rs.next());
assertEquals(testNumbers[i], rs.getLong(1));
}
assertFalse(rs.next());
/* NOTE: This section currently fails due to the fact that we cannot parse literal values
that are bigger than Long.MAX_VALUE and Long.MIN_VALUE. We will need to fix the parse
before enabling this section of the test.
select = "SELECT count(*) FROM KVBigIntValueTest where kv >= " + LONG_MIN_MINUS_ONE;
rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select = "SELECT kv FROM KVBigIntValueTest WHERE kv >= " + LONG_MIN_MINUS_ONE +
" GROUP BY kv ORDER BY kv ASC NULLS LAST ";
rs = conn.createStatement().executeQuery(select);
for (int i = 0; i < testNumbers.length; i++) {
assertTrue(rs.next());
assertEquals(testNumbers[i], rs.getInt(1));
}
assertFalse(rs.next());
select = "SELECT count(*) FROM KVBigIntValueTest where kv <= " + LONG_MAX_PLUS_ONE;
rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select = "SELECT kv FROM KVBigIntValueTest WHERE kv <= " + LONG_MAX_PLUS_ONE +
" GROUP BY kv ORDER BY kv DESC NULLS LAST";
rs = conn.createStatement().executeQuery(select);
for (int i = testNumbers.length-1; i >= 0; i--) {
assertTrue(rs.next());
assertEquals(testNumbers[i], rs.getInt(1));
}
assertFalse(rs.next());
*/
}
@Test
public void testIntegerKV() throws Exception {
int[] testNumbers = {
Integer.MIN_VALUE,
Integer.MIN_VALUE + 1,
-2,
-1,
0,
1,
2,
Integer.MAX_VALUE - 1,
Integer.MAX_VALUE
};
ensureTableCreated(getUrl(), "KVIntValueTest");
Properties props = new Properties();
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String upsert = "UPSERT INTO KVIntValueTest VALUES(?, ?)";
PreparedStatement stmt = conn.prepareStatement(upsert);
for (int i = 0; i < testNumbers.length; i++) {
stmt.setInt(1, i);
stmt.setInt(2, testNumbers[i]);
stmt.execute();
}
conn.commit();
conn.close();
String select = "SELECT COUNT(*) from KVIntValueTest";
ResultSet rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select = "SELECT count(*) FROM KVIntValueTest where kv >= " + Integer.MIN_VALUE;
rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select =
"SELECT kv FROM KVIntValueTest WHERE kv >= "
+ Integer.MIN_VALUE
+ " GROUP BY kv ORDER BY kv ASC NULLS LAST";
rs = conn.createStatement().executeQuery(select);
for (int i = 0; i < testNumbers.length; i++) {
assertTrue(rs.next());
assertEquals(testNumbers[i], rs.getInt(1));
}
assertFalse(rs.next());
select = "SELECT count(*) FROM KVIntValueTest where kv <= " + Integer.MAX_VALUE;
rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select =
"SELECT kv FROM KVIntValueTest WHERE kv <= "
+ Integer.MAX_VALUE
+ " GROUP BY kv ORDER BY kv DESC NULLS LAST";
rs = conn.createStatement().executeQuery(select);
for (int i = testNumbers.length - 1; i >= 0; i--) {
assertTrue(rs.next());
assertEquals(testNumbers[i], rs.getInt(1));
}
assertFalse(rs.next());
select = "SELECT count(*) FROM KVIntValueTest where kv >= " + INTEGER_MIN_MINUS_ONE;
rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select =
"SELECT kv FROM KVIntValueTest WHERE kv >= "
+ INTEGER_MIN_MINUS_ONE
+ " GROUP BY kv ORDER BY kv ASC NULLS LAST ";
rs = conn.createStatement().executeQuery(select);
for (int i = 0; i < testNumbers.length; i++) {
assertTrue(rs.next());
assertEquals(testNumbers[i], rs.getInt(1));
}
assertFalse(rs.next());
select = "SELECT count(*) FROM KVIntValueTest where kv <= " + INTEGER_MAX_PLUS_ONE;
rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(testNumbers.length, rs.getInt(1));
assertFalse(rs.next());
select =
"SELECT kv FROM KVIntValueTest WHERE kv <= "
+ INTEGER_MAX_PLUS_ONE
+ " GROUP BY kv ORDER BY kv DESC NULLS LAST";
rs = conn.createStatement().executeQuery(select);
for (int i = testNumbers.length - 1; i >= 0; i--) {
assertTrue(rs.next());
assertEquals(testNumbers[i], rs.getInt(1));
}
assertFalse(rs.next());
}