@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();
}
}
@Test
public void testInsertLongRecord() {
Connection db = getConnection();
long app = getAppId();
try {
Record record;
record = new Record();
record.setString("Single_line_text", "foo");
record.setLong("Number", 999);
record.setString("Number_0", "999.99");
db.insert(app, record);
ResultSet rs = db.select(app, "");
if (rs.size() != 1) {
fail("invalid count");
}
rs.next();
assert (rs.getString("Single_line_text") == "foo");
assert (rs.getLong("Number") == 999);
assert (rs.getString("Number_0") == "999.99");
} catch (Exception e) {
fail("db exception:" + e.getMessage());
}
}
@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 testDeleteByRecord() {
Connection db = getConnection();
long app = getAppId();
try {
Record record = new Record();
List<Long> ids = insertRecords();
long id = ids.get(1);
ResultSet rs = db.select(app, "Record_number = " + id);
if (rs.size() != 1) {
fail("invalid count");
}
rs.next();
record.setId(rs.getId());
record.setRevision(rs.getRevision());
db.deleteByRecord(app, record);
rs = db.select(app, "");
if (rs.size() != 2) {
fail("failed to delete");
}
} catch (Exception e) {
fail("db exception:" + e.getMessage());
}
}
@Test
public void testSetIsolation() throws SQLException {
// this is acceptable
connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
try {
connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
fail("expected exception");
} catch (UnsupportedOperationException e) {
}
try {
connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
fail("expected exception");
} catch (UnsupportedOperationException e) {
}
try {
connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
fail("expected exception");
} catch (UnsupportedOperationException e) {
}
try {
connection.setTransactionIsolation(Connection.TRANSACTION_NONE);
fail("expected exception");
} catch (IllegalArgumentException e) {
}
}
@Test
public void testSelect() {
Connection db = getConnection();
long app = getAppId();
try {
ArrayList<Record> records = new ArrayList<Record>();
Record record;
record = new Record();
record.setString("Single_line_text", "foo");
records.add(record);
record = new Record();
record.setString("Single_line_text", "bar");
records.add(record);
record = new Record();
record.setString("Single_line_text", "foo");
records.add(record);
record = new Record();
record.setString("Single_line_text", "bar");
records.add(record);
record = new Record();
record.setString("Single_line_text", "foo");
records.add(record);
db.insert(app, records);
ResultSet rs = db.select(app, "Single_line_text = \"foo\"");
if (rs.size() != 3) {
fail("invalid count " + rs.size());
}
} catch (Exception e) {
fail("failed to select");
}
}
@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 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 testDeleteByRecords() {
Connection db = getConnection();
long app = getAppId();
try {
Record record;
List<Record> records = new ArrayList<Record>();
List<Long> ids = insertRecords();
ResultSet rs = db.select(app, "");
if (rs.size() != 3) {
fail("invalid count");
}
while (rs.next()) {
record = new Record();
record.setId(rs.getId());
record.setRevision(rs.getRevision());
records.add(record);
}
db.deleteByRecords(app, records);
rs = db.select(app, "");
if (rs.size() != 0) {
fail("failed to delete");
}
} catch (Exception e) {
fail("db exception:" + e.getMessage());
}
}
@Test
public void testUpdateByRecord() {
Connection db = getConnection();
long app = getAppId();
try {
Record record = new Record();
List<Long> ids = insertRecords();
long id = ids.get(1);
ResultSet rs = db.select(app, "Record_number = " + id);
if (rs.size() != 1) {
fail("invalid count");
}
rs.next();
record.setId(rs.getId());
record.setRevision(rs.getRevision());
record.setString("Single_line_text", "hoge");
db.updateByRecord(app, record);
rs = db.select(app, "Single_line_text = \"hoge\"");
if (rs.size() != 1) {
fail("failed to update");
}
rs.next();
if (rs.getId() != id) {
fail("invalid id");
}
} catch (Exception e) {
fail("db exception:" + e.getMessage());
}
}
@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 testBulkRequest() {
Connection db = getConnection();
long app = getAppId();
try {
Record record;
List<Record> records = new ArrayList<Record>();
List<Long> ids = insertRecords();
ResultSet rs = db.select(app, "order by Record_number asc");
if (rs.size() != 3) {
fail("invalid count");
}
int i = 0;
while (rs.next()) {
record = new Record();
record.setId(rs.getId());
record.setRevision(rs.getRevision());
record.setString("Single_line_text", "hoge" + i);
records.add(record);
i++;
}
BulkRequest bulk = new BulkRequest();
bulk.updateByRecords(app, records);
records = new ArrayList<Record>();
record = new Record();
record.setString("Single_line_text", "fuga");
records.add(record);
record = new Record();
record.setString("Single_line_text", "piyo");
records.add(record);
bulk.insert(app, records);
bulk.delete(app, ids.get(1));
db.bulkRequest(bulk);
rs = db.select(app, "order by Record_number asc");
if (rs.size() != 4) {
fail("invalid count");
}
i = 0;
String[] values = {"hoge0", "hoge2", "fuga", "piyo"};
while (rs.next()) {
if (!rs.getString("Single_line_text").equals(values[i])) {
fail("invalid value:" + values[i]);
}
i++;
}
} catch (Exception e) {
fail("db exception:" + e.getMessage());
}
}
@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 testEmptyCommit() throws SQLException {
// Empty auto commit commit
connection.setAutoCommit(true);
connection.commit();
// Empty commit
connection.setAutoCommit(false);
connection.commit();
}
@Before
public void initialize() {
Connection db = getConnection();
long app = getAppId();
try {
db.deleteByQuery(app, "Record_number > 0");
} catch (Exception e) {
fail("Can not initialize the app");
}
}
@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);
}
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();
}
@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();
}
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 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();
}
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 testApiToken() {
String domain = System.getenv("DOMAIN");
String apiToken = "lzrxAeavY4RI99c9146xzZq4U7gkOj3uvMTMt5QS";
Connection db = new Connection(domain, apiToken);
// db.setProxy("127.0.0.1", 8888); // for fiddler2
// db.setTrustAllHosts(true);
long app = getAppId();
try {
db.select(app, "Record_number > 0");
} catch (Exception e) {
fail("Can not use api token");
}
}
private void assertFacebookConnection(Connection<TestFacebookApi> facebook) {
assertEquals(new ConnectionKey("facebook", "9"), facebook.getKey());
assertEquals(null, facebook.getDisplayName());
assertEquals(null, facebook.getProfileUrl());
assertEquals(null, facebook.getImageUrl());
TestFacebookApi facebookApi = facebook.getApi();
assertEquals("234567890", facebookApi.getAccessToken());
facebook.sync();
assertEquals("Keith Donald", facebook.getDisplayName());
assertEquals("http://facebook.com/keith.donald", facebook.getProfileUrl());
assertEquals("http://facebook.com/keith.donald/picture", facebook.getImageUrl());
}
@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);
}
@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 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;");
}
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 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 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...
}