@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 testDropIndexOnTable() throws SQLException {
Connection connection = null;
Statement statement = null;
try {
ConnectionManager connectionManager = temporaryFileDatabase.getConnectionManager(true);
connection = spy(connectionManager.getConnection(null));
statement = spy(connection.createStatement());
when(connection.createStatement()).thenReturn(statement);
DBQueries.createIndexTable(NAME, Integer.class, String.class, connection);
DBQueries.createIndexOnTable(NAME, connection);
assertObjectExistenceInSQLIteMasterTable(TABLE_NAME, "table", true, connectionManager);
assertObjectExistenceInSQLIteMasterTable(INDEX_NAME, "index", true, connectionManager);
DBQueries.dropIndexOnTable(NAME, connection);
assertObjectExistenceInSQLIteMasterTable(TABLE_NAME, "table", true, connectionManager);
assertObjectExistenceInSQLIteMasterTable(INDEX_NAME, "index", false, connectionManager);
verify(statement, times(3)).close();
} finally {
DBUtils.closeQuietly(connection);
DBUtils.closeQuietly(statement);
}
}
@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());
}
private void connectWithJulianDayModeActivated() throws SQLException {
close();
final Properties info = new Properties();
info.setProperty("julian_day", "true");
conn = DriverManager.getConnection("jdbc:sqlite:", info);
stat = conn.createStatement();
}
@Test
public void testScanUri() throws Exception {
GroupByCaseTest gbt = new GroupByCaseTest();
long ts = gbt.createTable();
gbt.loadData(ts);
Properties props = new Properties(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1));
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select uri from " + GROUPBYTEST_NAME);
assertTrue(rs.next());
assertEquals("Report1", rs.getString(1));
assertTrue(rs.next());
assertEquals("Report2", rs.getString(1));
assertTrue(rs.next());
assertEquals("Report3", rs.getString(1));
assertTrue(rs.next());
assertEquals("Report4", rs.getString(1));
assertTrue(rs.next());
assertEquals("SOQL1", rs.getString(1));
assertTrue(rs.next());
assertEquals("SOQL2", rs.getString(1));
assertTrue(rs.next());
assertEquals("SOQL3", rs.getString(1));
assertTrue(rs.next());
assertEquals("SOQL4", rs.getString(1));
assertFalse(rs.next());
conn.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 testClientIds() throws SQLException {
mockConnection.setEmptyResults();
connection.createStatement().executeQuery(SELECT);
int id1 = mockConnection.lastRequest.getTransactionId();
new Connection(mockConnection, "test").createStatement().executeQuery(SELECT);
int id2 = mockConnection.lastRequest.getTransactionId();
assertTrue(id1 != id2);
}
@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();
}
@After
public void dispose() throws SQLException, IOException, ClassNotFoundException {
Connection connection = BddConnecteur.getConnection();
Statement statement = connection.createStatement();
statement.execute("DROP TABLE Creneau");
statement.execute("DROP TABLE Atelier");
statement.execute("DROP TABLE Labo");
statement.execute("DROP TABLE Utilisateur");
statement.close();
connection.close();
BddConnecteur.dispose();
}
@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();
}
@After
public void tearDown() throws Exception {
if (em != null) {
em.close();
}
if (emFactory != null) {
emFactory.close();
}
try {
connection.createStatement().execute("SHUTDOWN");
} catch (Exception ex) {
}
}
@Test
public void testAjoutCreneau() throws SQLException, IOException, ClassNotFoundException {
Connection connection = BddConnecteur.getConnection();
BddUtilisateur.ajout("*****@*****.**", "labri");
BddLabo.ajout(1, "Labri");
BddAtelier.ajoutAtelier(
1,
"A la poursuite d'ennemis invisibles",
"Sciences de la vie ",
"Campus Carreire (Hôpital Pellegrin)",
"Labo MFP",
"",
"",
"",
"");
// Insertion
int idAtelier = 1;
String jour = "2015-12-23";
String heure = "13:00";
int capacite = 20;
BddCreneau.ajoutCreneau(1, jour, heure, capacite);
// Vérification
String sql = "SELECT * FROM Creneau WHERE idAtelier='" + idAtelier + "'";
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery(sql);
int count = 0;
Date jourRecup;
Time heureRecup;
int capaciteRecup = 0;
while (rs.next()) {
jourRecup = rs.getDate("jour");
heureRecup = rs.getTime("heure");
capaciteRecup = rs.getInt("capacite");
count++;
}
assertEquals(count, 1);
// assertEquals(jourRecup, jour);
// assertEquals(heureRecup, heure);
assertEquals(capaciteRecup, capacite);
// Fermeture
rs.close();
statement.close();
connection.close();
}
@Test
public void testClearIndexTable() throws SQLException {
Connection connection = null;
Statement statement = null;
try {
ConnectionManager connectionManager = temporaryFileDatabase.getConnectionManager(true);
createSchema(connectionManager);
assertObjectExistenceInSQLIteMasterTable(TABLE_NAME, "table", true, connectionManager);
assertObjectExistenceInSQLIteMasterTable(INDEX_NAME, "index", true, connectionManager);
connection = spy(connectionManager.getConnection(null));
statement = spy(connection.createStatement());
when(connection.createStatement()).thenReturn(statement);
DBQueries.clearIndexTable(NAME, connection);
List<DBQueries.Row<Integer, String>> expectedRows = Collections.emptyList();
assertQueryResultSet("SELECT * FROM " + TABLE_NAME, expectedRows, connectionManager);
verify(statement, times(1)).close();
} finally {
DBUtils.closeQuietly(connection);
DBUtils.closeQuietly(statement);
}
}
@Test
public void testDeadlockTransactionRollback() throws SQLException {
connection.setAutoCommit(false);
// Failed statement
mockConnection.setError(Jdbc.ErrorCode.DEADLOCK.getNumber(), "deadlock");
Statement s = connection.createStatement();
try {
s.executeUpdate("DELETE bad");
fail("expected exception");
} catch (SQLException e) {
assertEquals(Jdbc.ErrorCode.DEADLOCK.getNumber(), e.getErrorCode());
assertEquals("40001", e.getSQLState());
}
assertNull(mockConnection.lastFinish);
}
@Test
public void testSupprCreneauxByIdAtelier()
throws SQLException, IOException, ClassNotFoundException {
Connection connection = BddConnecteur.getConnection();
BddUtilisateur.ajout("*****@*****.**", "labri");
BddLabo.ajout(1, "Labri");
BddAtelier.ajoutAtelier(
1,
"A la poursuite d'ennemis invisibles",
"Sciences de la vie ",
"Campus Carreire (Hôpital Pellegrin)",
"Labo MFP",
"",
"",
"",
"");
// Insertion
int idAtelier = 1;
String jour = "2015-12-23";
String heure = "13:00";
int oldCapacite = 20;
int newCapacite = 40;
BddCreneau.ajoutCreneau(1, jour, heure, oldCapacite);
// Suppression
BddCreneau.supprCreneauxByIdAtelier(1);
// Vérification
String sql = "SELECT * FROM Creneau WHERE idAtelier='" + idAtelier + "'";
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery(sql);
int count = 0;
while (rs.next()) {
count++;
}
assertEquals(count, 0);
// Fermeture
rs.close();
statement.close();
connection.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"));
}
void initWithTestData(final ConnectionManager connectionManager) {
createSchema(connectionManager);
Connection connection = null;
Statement statement = null;
try {
connection = connectionManager.getConnection(null);
statement = connection.createStatement();
assertEquals(statement.executeUpdate("INSERT INTO " + TABLE_NAME + " values (1, 'abs')"), 1);
assertEquals(statement.executeUpdate("INSERT INTO " + TABLE_NAME + " values (1, 'gps')"), 1);
assertEquals(
statement.executeUpdate("INSERT INTO " + TABLE_NAME + " values (2, 'airbags')"), 1);
assertEquals(statement.executeUpdate("INSERT INTO " + TABLE_NAME + " values (3, 'abs')"), 1);
} catch (Exception e) {
throw new IllegalStateException("Unable to initialize test database", e);
} finally {
DBUtils.closeQuietly(connection);
DBUtils.closeQuietly(statement);
}
}
void createSchema(final ConnectionManager connectionManager) {
Connection connection = null;
Statement statement = null;
try {
connection = connectionManager.getConnection(null);
statement = connection.createStatement();
assertEquals(
statement.executeUpdate(
"CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (objectKey INTEGER, value TEXT)"),
0);
assertEquals(
statement.executeUpdate(
"CREATE INDEX IF NOT EXISTS " + INDEX_NAME + " ON " + TABLE_NAME + "(value)"),
0);
} catch (Exception e) {
throw new IllegalStateException("Unable to create test database schema", e);
} finally {
DBUtils.closeQuietly(connection);
DBUtils.closeQuietly(statement);
}
}
public void assertQueryResultSet(
final String query,
final List<DBQueries.Row<Integer, String>> rows,
final ConnectionManager connectionManager)
throws SQLException {
Connection connection = null;
Statement statement = null;
try {
connection = connectionManager.getConnection(null);
statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(query);
assertResultSetOrderAgnostic(resultSet, rows);
} catch (Exception e) {
throw new IllegalStateException("Unable to verify resultSet", e);
} finally {
DBUtils.closeQuietly(connection);
DBUtils.closeQuietly(statement);
}
}
@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());
}
@Before
public void connect() throws Exception {
conn = DriverManager.getConnection("jdbc:sqlite:");
stat = conn.createStatement();
}