@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 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 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 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 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();
}
@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 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 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 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 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 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();
}
@SuppressWarnings("deprecation")
@Test
public void time() throws SQLException {
connectWithJulianDayModeActivated();
Time d1 = new Time(System.currentTimeMillis());
stat.execute("create table t (c1);");
PreparedStatement prep = conn.prepareStatement("insert into t values (?);");
prep.setTime(1, d1);
prep.executeUpdate();
ResultSet rs = stat.executeQuery("select c1 from t;");
assertTrue(rs.next());
assertEquals(d1.getHours(), rs.getTime(1).getHours());
assertEquals(d1.getMinutes(), rs.getTime(1).getMinutes());
assertEquals(d1.getSeconds(), rs.getTime(1).getSeconds());
}
@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();
}
@Ignore
@Test
public void getObject() throws SQLException {
stat.executeUpdate(
"create table testobj (" + "c1 integer, c2 float, c3, c4 varchar, c5 bit, c6, c7);");
PreparedStatement prep = conn.prepareStatement("insert into testobj values (?,?,?,?,?,?,?);");
prep.setInt(1, Integer.MAX_VALUE);
prep.setFloat(2, Float.MAX_VALUE);
prep.setDouble(3, Double.MAX_VALUE);
prep.setLong(4, Long.MAX_VALUE);
prep.setBoolean(5, false);
prep.setByte(6, (byte) 7);
prep.setBytes(7, b1);
prep.executeUpdate();
ResultSet rs = stat.executeQuery("select c1,c2,c3,c4,c5,c6,c7 from testobj;");
assertTrue(rs.next());
assertEquals(Integer.MAX_VALUE, rs.getInt(1));
assertEquals(Integer.MAX_VALUE, (int) rs.getLong(1));
assertEquals(Float.MAX_VALUE, rs.getFloat(2));
assertEquals(Double.MAX_VALUE, rs.getDouble(3));
assertEquals(Long.MAX_VALUE, rs.getLong(4));
assertFalse(rs.getBoolean(5));
assertEquals((byte) 7, rs.getByte(6));
assertArrayEq(rs.getBytes(7), b1);
assertNotNull(rs.getObject(1));
assertNotNull(rs.getObject(2));
assertNotNull(rs.getObject(3));
assertNotNull(rs.getObject(4));
assertNotNull(rs.getObject(5));
assertNotNull(rs.getObject(6));
assertNotNull(rs.getObject(7));
assertTrue(rs.getObject(1) instanceof Integer);
assertTrue(rs.getObject(2) instanceof Double);
assertTrue(rs.getObject(3) instanceof Double);
assertTrue(rs.getObject(4) instanceof String);
assertTrue(rs.getObject(5) instanceof Integer);
assertTrue(rs.getObject(6) instanceof Integer);
assertTrue(rs.getObject(7) instanceof byte[]);
rs.close();
}
@Test
public void date1() throws SQLException {
Date d1 = new Date(987654321);
stat.execute("create table t (c1);");
PreparedStatement prep = conn.prepareStatement("insert into t values(?);");
prep.setDate(1, d1);
prep.executeUpdate();
prep.setDate(1, null);
prep.executeUpdate();
ResultSet rs = stat.executeQuery("select c1 from t;");
assertTrue(rs.next());
assertEquals(d1.getTime(), rs.getLong(1));
assertTrue(rs.getDate(1).equals(d1));
assertTrue(rs.next());
assertEquals(null, rs.getDate(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"));
}
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);
}
}
@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 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);
}
}
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);
}
}
@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 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 close() throws SQLException {
stat.close();
conn.close();
}