@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 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 dbclose() throws SQLException {
conn.prepareStatement("select ?;").setString(1, "Hello World");
conn.prepareStatement("select null;").close();
conn.prepareStatement("select null;").executeQuery().close();
conn.prepareStatement("create table t (c);").executeUpdate();
conn.prepareStatement("select null;");
}
@Test
public void timestamp() throws SQLException {
connectWithJulianDayModeActivated();
long now = System.currentTimeMillis();
Timestamp d1 = new Timestamp(now);
Date d2 = new Date(now);
Time d3 = new Time(now);
stat.execute("create table t (c1);");
PreparedStatement prep = conn.prepareStatement("insert into t values (?);");
prep.setTimestamp(1, d1);
prep.executeUpdate();
ResultSet rs = stat.executeQuery("select c1 from t;");
assertTrue(rs.next());
assertEquals(d1, rs.getTimestamp(1));
rs = stat.executeQuery("select date(c1, 'localtime') from t;");
assertTrue(rs.next());
assertEquals(d2.toString(), rs.getString(1));
rs = stat.executeQuery("select time(c1, 'localtime') from t;");
assertTrue(rs.next());
assertEquals(d3.toString(), rs.getString(1));
rs = stat.executeQuery("select strftime('%Y-%m-%d %H:%M:%f', c1, 'localtime') from t;");
assertTrue(rs.next());
// assertEquals(d1.toString(), rs.getString(1)); // ms are not occurate...
}
@Test
public void testCompareLongGTEDecimal() throws Exception {
long ts = nextTimestamp();
initTableValues(null, ts);
String query = "SELECT l FROM LongInKeyTest where l >= 1.5";
Properties props = new Properties();
props.setProperty(
PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
try {
PreparedStatement statement = conn.prepareStatement(query);
ResultSet rs = statement.executeQuery();
/*
* Failing because we're not converting the constant to the type of the RHS
* when forming the start/stop key.
* For this case, 1.5 -> 1L
* if where l < 1.5 then 1.5 -> 1L and then to 2L because it's not inclusive
*
*/
assertTrue(rs.next());
assertEquals(2, rs.getLong(1));
assertFalse(rs.next());
} finally {
conn.close();
}
}
@edu.umd.cs.findbugs.annotations.SuppressWarnings(
value = "RV_RETURN_VALUE_IGNORED",
justification = "Test code.")
private void executeQuery(Connection conn, String query) throws SQLException {
PreparedStatement st = conn.prepareStatement(query);
st.executeQuery();
}
@Test
public void dblock() throws SQLException {
stat.executeUpdate("create table test (c1);");
stat.executeUpdate("insert into test values (1);");
conn.prepareStatement("select * from test;").executeQuery().close();
stat.executeUpdate("drop table test;");
}
@Test
public void retainKeysInBatch() throws SQLException {
stat.executeUpdate("create table test (c1, c2);");
PreparedStatement prep = conn.prepareStatement("insert into test values (?, ?);");
prep.setInt(1, 10);
prep.setString(2, "ten");
prep.addBatch();
prep.setInt(1, 100);
prep.setString(2, "hundred");
prep.addBatch();
prep.setString(2, "one hundred");
prep.addBatch();
prep.setInt(1, 1000);
prep.setString(2, "thousand");
prep.addBatch();
prep.executeBatch();
prep.close();
ResultSet rs = stat.executeQuery("select * from test;");
assertTrue(rs.next());
assertEquals(10, rs.getInt(1));
assertEquals("ten", rs.getString(2));
assertTrue(rs.next());
assertEquals(100, rs.getInt(1));
assertEquals("hundred", rs.getString(2));
assertTrue(rs.next());
assertEquals(100, rs.getInt(1));
assertEquals("one hundred", rs.getString(2));
assertTrue(rs.next());
assertEquals(1000, rs.getInt(1));
assertEquals("thousand", rs.getString(2));
assertFalse(rs.next());
rs.close();
}
@Test
public void batch() throws SQLException {
ResultSet rs;
stat.executeUpdate("create table test (c1, c2, c3, c4);");
PreparedStatement prep = conn.prepareStatement("insert into test values (?,?,?,?);");
for (int i = 0; i < 10; i++) {
prep.setInt(1, Integer.MIN_VALUE + i);
prep.setFloat(2, Float.MIN_VALUE + i);
prep.setString(3, "Hello " + i);
prep.setDouble(4, Double.MAX_VALUE + i);
prep.addBatch();
}
assertArrayEq(prep.executeBatch(), new int[] {1, 1, 1, 1, 1, 1, 1, 1, 1, 1});
assertEquals(0, prep.executeBatch().length);
prep.close();
rs = stat.executeQuery("select * from test;");
for (int i = 0; i < 10; i++) {
assertTrue(rs.next());
assertEquals(Integer.MIN_VALUE + i, rs.getInt(1));
assertEquals(Float.MIN_VALUE + i, rs.getFloat(2));
assertEquals("Hello " + i, rs.getString(3));
assertEquals(Double.MAX_VALUE + i, rs.getDouble(4));
}
rs.close();
stat.executeUpdate("drop table test;");
}
@Test
public void emptyRS() throws SQLException {
PreparedStatement prep = conn.prepareStatement("select null limit 0;");
ResultSet rs = prep.executeQuery();
assertFalse(rs.next());
rs.close();
prep.close();
}
@Test
public void doubleclose() throws SQLException {
PreparedStatement prep = conn.prepareStatement("select null;");
ResultSet rs = prep.executeQuery();
rs.close();
prep.close();
prep.close();
}
@Test
public void changeSchema() throws SQLException {
stat.execute("create table t (c1);");
PreparedStatement prep = conn.prepareStatement("insert into t values (?);");
conn.createStatement().execute("create table t2 (c2);");
prep.setInt(1, 1000);
prep.execute();
prep.executeUpdate();
}
@Test
public void update() throws SQLException {
assertEquals(conn.prepareStatement("create table s1 (c1);").executeUpdate(), 0);
PreparedStatement prep = conn.prepareStatement("insert into s1 values (?);");
prep.setInt(1, 3);
assertEquals(1, prep.executeUpdate());
prep.setInt(1, 5);
assertEquals(1, prep.executeUpdate());
prep.setInt(1, 7);
assertEquals(1, prep.executeUpdate());
prep.close();
// check results with normal statement
ResultSet rs = stat.executeQuery("select sum(c1) from s1;");
assertTrue(rs.next());
assertEquals(15, rs.getInt(1));
rs.close();
}
@Test
public void testPrepareStatement() throws SQLException {
PreparedStatement ps = connection.prepareStatement(SELECT);
assertNotNull(ps);
ps = connection.prepareStatement(INSERT, Statement.NO_GENERATED_KEYS);
assertNotNull(ps);
ps = connection.prepareStatement(INSERT, Statement.RETURN_GENERATED_KEYS);
assertNotNull(ps);
// Ignored, despite this being a select
ps = connection.prepareStatement(SELECT, Statement.NO_GENERATED_KEYS);
assertNotNull(ps);
connection.enableStrictMode(true);
try {
connection.prepareStatement(SELECT, Statement.NO_GENERATED_KEYS);
fail("expected exception");
} catch (IllegalArgumentException e) {
}
}
private void insertRow(Connection conn, String uri, int appcpu) throws SQLException {
PreparedStatement statement =
conn.prepareStatement(
"UPSERT INTO " + GROUPBYTEST_NAME + "(id, uri, appcpu) values (?,?,?)");
statement.setString(1, "id" + id);
statement.setString(2, uri);
statement.setInt(3, appcpu);
statement.executeUpdate();
id++;
}
@Test
public void setmaxrows() throws SQLException {
PreparedStatement prep = conn.prepareStatement("select 1 union select 2;");
prep.setMaxRows(1);
ResultSet rs = prep.executeQuery();
assertTrue(rs.next());
assertEquals(1, rs.getInt(1));
assertFalse(rs.next());
prep.close();
}
@Test
public void stringRS() throws SQLException {
String name = "Gandhi";
PreparedStatement prep = conn.prepareStatement("select ?;");
prep.setString(1, name);
ResultSet rs = prep.executeQuery();
assertTrue(rs.next());
assertEquals(name, rs.getString(1));
assertFalse(rs.next());
rs.close();
}
protected static void initTableValues(byte[][] splits, long ts) throws Exception {
ensureTableCreated(getUrl(), "LongInKeyTest", splits, ts - 2);
// Insert all rows at ts
String url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + ts;
Connection conn = DriverManager.getConnection(url);
conn.setAutoCommit(true);
PreparedStatement stmt = conn.prepareStatement("upsert into " + "LongInKeyTest VALUES(?)");
stmt.setLong(1, 2);
stmt.execute();
conn.close();
}
@Test
public void utf() throws SQLException {
ResultSet rs =
stat.executeQuery(
"select '"
+ utf01
+ "','"
+ utf02
+ "','"
+ utf03
+ "','"
+ utf04
+ "','"
+ utf05
+ "','"
+ utf06
+ "','"
+ utf07
+ "','"
+ utf08
+ "';");
assertEquals(utf01, rs.getString(1));
assertEquals(utf02, rs.getString(2));
assertEquals(utf03, rs.getString(3));
assertEquals(utf04, rs.getString(4));
assertEquals(utf05, rs.getString(5));
assertEquals(utf06, rs.getString(6));
assertEquals(utf07, rs.getString(7));
assertEquals(utf08, rs.getString(8));
rs.close();
PreparedStatement prep = conn.prepareStatement("select ?,?,?,?,?,?,?,?;");
prep.setString(1, utf01);
prep.setString(2, utf02);
prep.setString(3, utf03);
prep.setString(4, utf04);
prep.setString(5, utf05);
prep.setString(6, utf06);
prep.setString(7, utf07);
prep.setString(8, utf08);
rs = prep.executeQuery();
assertTrue(rs.next());
assertEquals(utf01, rs.getString(1));
assertEquals(utf02, rs.getString(2));
assertEquals(utf03, rs.getString(3));
assertEquals(utf04, rs.getString(4));
assertEquals(utf05, rs.getString(5));
assertEquals(utf06, rs.getString(6));
assertEquals(utf07, rs.getString(7));
assertEquals(utf08, rs.getString(8));
rs.close();
}
public void assertObjectExistenceInSQLIteMasterTable(
final String name,
final String type,
boolean exists,
final ConnectionManager connectionManager) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = connectionManager.getConnection(null);
statement = connection.prepareStatement("SELECT name FROM sqlite_master WHERE type=?");
statement.setString(1, type);
java.sql.ResultSet indices = statement.executeQuery();
boolean found = false;
StringBuilder objectsFound = new StringBuilder();
String next;
while (indices.next()) {
next = indices.getString(1);
objectsFound.append("'").append(next).append("' ");
if (name.equals(next)) {
found = true;
}
}
if (exists)
Assert.assertTrue(
"Object '"
+ name
+ "' must exists in 'sqlite_master' but it doesn't. found: "
+ found
+ ". Objects found: "
+ objectsFound,
found);
else
Assert.assertFalse(
"Object '"
+ name
+ "' must NOT exists in 'sqlite_master' but it does. found: "
+ found
+ " Objects found: "
+ objectsFound,
found);
} catch (Exception e) {
throw new IllegalStateException(
"Unable to verify existence of the object '" + name + "' in the 'sqlite_master' table",
e);
} finally {
DBUtils.closeQuietly(connection);
DBUtils.closeQuietly(statement);
}
}
@Test
public void singleRowRS() throws SQLException {
PreparedStatement prep = conn.prepareStatement("select ?;");
prep.setInt(1, Integer.MAX_VALUE);
ResultSet rs = prep.executeQuery();
assertTrue(rs.next());
assertEquals(Integer.MAX_VALUE, rs.getInt(1));
assertEquals(Integer.toString(Integer.MAX_VALUE), rs.getString(1));
assertEquals(new Integer(Integer.MAX_VALUE).doubleValue(), rs.getDouble(1));
assertFalse(rs.next());
rs.close();
prep.close();
}
@Test
public void twoRowRS() throws SQLException {
PreparedStatement prep = conn.prepareStatement("select ? union all select ?;");
prep.setDouble(1, Double.MAX_VALUE);
prep.setDouble(2, Double.MIN_VALUE);
ResultSet rs = prep.executeQuery();
assertTrue(rs.next());
assertEquals(Double.MAX_VALUE, rs.getDouble(1));
assertTrue(rs.next());
assertEquals(Double.MIN_VALUE, rs.getDouble(1));
assertFalse(rs.next());
rs.close();
}
@Test
public void set() throws SQLException {
ResultSet rs;
PreparedStatement prep = conn.prepareStatement("select ?, ?, ?;");
// integers
prep.setInt(1, Integer.MIN_VALUE);
prep.setInt(2, Integer.MAX_VALUE);
prep.setInt(3, 0);
rs = prep.executeQuery();
assertTrue(rs.next());
assertEquals(Integer.MIN_VALUE, rs.getInt(1));
assertEquals(Integer.MAX_VALUE, rs.getInt(2));
assertEquals(0, rs.getInt(3));
// strings
String name = "Winston Leonard Churchill";
String fn = name.substring(0, 7), mn = name.substring(8, 15), sn = name.substring(16, 25);
prep.clearParameters();
prep.setString(1, fn);
prep.setString(2, mn);
prep.setString(3, sn);
prep.executeQuery();
assertTrue(rs.next());
assertEquals(fn, rs.getString(1));
assertEquals(mn, rs.getString(2));
assertEquals(sn, rs.getString(3));
// mixed
prep.setString(1, name);
prep.setString(2, null);
prep.setLong(3, Long.MAX_VALUE);
prep.executeQuery();
assertTrue(rs.next());
assertEquals(name, rs.getString(1));
assertNull(rs.getString(2));
assertTrue(rs.wasNull());
assertEquals(Long.MAX_VALUE, rs.getLong(3));
// bytes
prep.setBytes(1, b1);
prep.setBytes(2, b2);
prep.setBytes(3, b3);
prep.executeQuery();
assertTrue(rs.next());
assertArrayEq(rs.getBytes(1), b1);
assertArrayEq(rs.getBytes(2), b2);
assertArrayEq(rs.getBytes(3), b3);
assertFalse(rs.next());
rs.close();
}
@Test
public void date2() throws SQLException {
Date d1 = new Date(1092941466000L);
stat.execute("create table t (c1);");
PreparedStatement prep =
conn.prepareStatement("insert into t values (datetime(?/1000, 'unixepoch'));");
prep.setDate(1, d1);
prep.executeUpdate();
ResultSet rs = stat.executeQuery("select strftime('%s', c1) * 1000 from t;");
assertTrue(rs.next());
assertEquals(d1.getTime(), rs.getLong(1));
assertTrue(rs.getDate(1).equals(d1));
}
@Test
public void multiUpdate() throws SQLException {
stat.executeUpdate("create table test (c1);");
PreparedStatement prep = conn.prepareStatement("insert into test values (?);");
for (int i = 0; i < 10; i++) {
prep.setInt(1, i);
prep.executeUpdate();
prep.execute();
}
prep.close();
stat.executeUpdate("drop table test;");
}
@Test
public void tokens() throws SQLException {
/* checks for a bug where a substring is read by the driver as the
* full original string, caused by my idiocyin assuming the
* pascal-style string was null terminated. Thanks Oliver Randschau. */
StringTokenizer st = new StringTokenizer("one two three");
st.nextToken();
String substr = st.nextToken();
PreparedStatement prep = conn.prepareStatement("select ?;");
prep.setString(1, substr);
ResultSet rs = prep.executeQuery();
assertTrue(rs.next());
assertEquals(substr, rs.getString(1));
}
@Test
public void batchOneParam() throws SQLException {
stat.executeUpdate("create table test (c1);");
PreparedStatement prep = conn.prepareStatement("insert into test values (?);");
for (int i = 0; i < 10; i++) {
prep.setInt(1, Integer.MIN_VALUE + i);
prep.addBatch();
}
assertArrayEq(prep.executeBatch(), new int[] {1, 1, 1, 1, 1, 1, 1, 1, 1, 1});
prep.close();
ResultSet rs = stat.executeQuery("select count(*) from test;");
assertTrue(rs.next());
assertEquals(10, rs.getInt(1));
rs.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 metaData() throws SQLException {
PreparedStatement prep = conn.prepareStatement("select ? as col1, ? as col2, ? as delta;");
ResultSetMetaData meta = prep.getMetaData();
assertEquals(3, meta.getColumnCount());
assertEquals("col1", meta.getColumnName(1));
assertEquals("col2", meta.getColumnName(2));
assertEquals("delta", meta.getColumnName(3));
/*assertEquals(Types.INTEGER, meta.getColumnType(1));
assertEquals(Types.INTEGER, meta.getColumnType(2));
assertEquals(Types.INTEGER, meta.getColumnType(3));*/
meta = prep.executeQuery().getMetaData();
assertEquals(3, meta.getColumnCount());
prep.close();
}
@Test
public void colNameAccess() throws SQLException {
PreparedStatement prep = conn.prepareStatement("select ? as col1, ? as col2, ? as bingo;");
prep.setNull(1, 0);
prep.setFloat(2, Float.MIN_VALUE);
prep.setShort(3, Short.MIN_VALUE);
prep.executeQuery();
ResultSet rs = prep.executeQuery();
assertTrue(rs.next());
assertNull(rs.getString("col1"));
assertTrue(rs.wasNull());
assertEquals(Float.MIN_VALUE, rs.getFloat("col2"));
assertEquals(Short.MIN_VALUE, rs.getShort("bingo"));
rs.close();
prep.close();
}
