public List<String> findAlarmIds(String tenantId, Map<String, String> dimensions) {
final String FIND_ALARM_IDS_SQL =
"select distinct a.id "
+ "from alarm as a "
+ "join alarm_definition as ad on a.alarm_definition_id = ad.id "
+ "%s "
+ "where ad.tenant_id = :tenantId and ad.deleted_at is NULL "
+ "order by ad.created_at";
List<String> alarmIdList;
try (Handle h = this.mysql.open()) {
final String sql = String.format(FIND_ALARM_IDS_SQL, buildJoinClauseFor(dimensions));
Query<Map<String, Object>> query = h.createQuery(sql).bind("tenantId", tenantId);
logger.debug("mysql sql: {}", sql);
DimensionQueries.bindDimensionsToQuery(query, dimensions);
alarmIdList = query.map(StringMapper.FIRST).list();
}
return alarmIdList;
}
@Before
public void setUp() throws InterruptedException {
jdbcDataFetcher =
new JdbcDataFetcher(
derbyConnectorRule.getMetadataConnectorConfig(),
"tableName",
"keyColumn",
"valueColumn",
100);
handle = derbyConnectorRule.getConnector().getDBI().open();
Assert.assertEquals(
0,
handle
.createStatement(
String.format(
"CREATE TABLE %s (%s VARCHAR(64), %s VARCHAR(64))",
tableName, keyColumn, valueColumn))
.setQueryTimeout(1)
.execute());
handle
.createStatement(String.format("TRUNCATE TABLE %s", tableName))
.setQueryTimeout(1)
.execute();
for (Map.Entry<String, String> entry : lookupMap.entrySet()) {
insertValues(entry.getKey(), entry.getValue(), handle);
}
handle.commit();
}
private void verifyAccountEmailAuditAndHistoryCount(
final UUID accountId, final int expectedCount) {
final Handle handle = dbi.open();
// verify audit
StringBuilder sb = new StringBuilder();
sb.append("select * from audit_log a ");
sb.append("inner join account_email_history aeh on a.record_id = aeh.history_record_id ");
sb.append("where a.table_name = 'account_email_history' ");
sb.append(String.format("and aeh.account_id='%s'", accountId.toString()));
List<Map<String, Object>> result = handle.select(sb.toString());
assertEquals(result.size(), expectedCount);
// ***** NOT IDEAL
// ... but this works after the email record has been deleted; will likely fail when multiple
// emails exist for the same account
// verify history table
sb = new StringBuilder();
sb.append("select * from account_email_history aeh ");
sb.append(String.format("where aeh.account_id='%s'", accountId.toString()));
result = handle.select(sb.toString());
assertEquals(result.size(), expectedCount);
handle.close();
}
public H2QueryRunner() {
handle = DBI.open("jdbc:h2:mem:test" + System.nanoTime());
TpchMetadata tpchMetadata = new TpchMetadata("");
handle.execute(
"CREATE TABLE orders (\n"
+ " orderkey BIGINT PRIMARY KEY,\n"
+ " custkey BIGINT NOT NULL,\n"
+ " orderstatus CHAR(1) NOT NULL,\n"
+ " totalprice DOUBLE NOT NULL,\n"
+ " orderdate DATE NOT NULL,\n"
+ " orderpriority CHAR(15) NOT NULL,\n"
+ " clerk CHAR(15) NOT NULL,\n"
+ " shippriority BIGINT NOT NULL,\n"
+ " comment VARCHAR(79) NOT NULL\n"
+ ")");
handle.execute("CREATE INDEX custkey_index ON orders (custkey)");
TpchTableHandle ordersHandle =
tpchMetadata.getTableHandle(
null, new SchemaTableName(TINY_SCHEMA_NAME, ORDERS.getTableName()));
insertRows(
tpchMetadata.getTableMetadata(ordersHandle),
handle,
createTpchRecordSet(ORDERS, ordersHandle.getScaleFactor()));
handle.execute(
"CREATE TABLE lineitem (\n"
+ " orderkey BIGINT,\n"
+ " partkey BIGINT NOT NULL,\n"
+ " suppkey BIGINT NOT NULL,\n"
+ " linenumber BIGINT,\n"
+ " quantity BIGINT NOT NULL,\n"
+ " extendedprice DOUBLE NOT NULL,\n"
+ " discount DOUBLE NOT NULL,\n"
+ " tax DOUBLE NOT NULL,\n"
+ " returnflag CHAR(1) NOT NULL,\n"
+ " linestatus CHAR(1) NOT NULL,\n"
+ " shipdate DATE NOT NULL,\n"
+ " commitdate DATE NOT NULL,\n"
+ " receiptdate DATE NOT NULL,\n"
+ " shipinstruct VARCHAR(25) NOT NULL,\n"
+ " shipmode VARCHAR(10) NOT NULL,\n"
+ " comment VARCHAR(44) NOT NULL,\n"
+ " PRIMARY KEY (orderkey, linenumber)"
+ ")");
TpchTableHandle lineItemHandle =
tpchMetadata.getTableHandle(
null, new SchemaTableName(TINY_SCHEMA_NAME, LINE_ITEM.getTableName()));
insertRows(
tpchMetadata.getTableMetadata(lineItemHandle),
handle,
createTpchRecordSet(LINE_ITEM, lineItemHandle.getScaleFactor()));
}
@Override
public synchronized void unlock() {
if (handle != null) {
handle
.createQuery("select release_lock(:name)")
.bind("name", name)
.map(IntegerMapper.FIRST)
.first();
handle.close();
handle = null;
}
}
@Test
public void testTxActuallyCommits() throws Exception {
Handle h2 = dbi.open();
Dao one = handle.attach(Dao.class);
Dao two = h2.attach(Dao.class);
// insert in @Transaction method
Something inserted = one.insertAndFetch(1, "Brian");
// fetch from another connection
Something fetched = two.findById(1);
assertThat(fetched, equalTo(inserted));
}
@Override
public <ReturnType> ReturnType inTransaction(
final Handle handle,
final TransactionIsolationLevel level,
final TransactionCallback<ReturnType> callback) {
final TransactionIsolationLevel initial = handle.getTransactionIsolationLevel();
try {
handle.setTransactionIsolation(level);
return inTransaction(handle, callback);
} finally {
handle.setTransactionIsolation(initial);
}
}
public boolean insert(Template template) {
if (template == null) {
return false;
}
Handle handle = dbi.open();
try {
TemplateDao db = handle.attach(TemplateDao.class);
return db.insertBean(template) > 0;
} finally {
handle.close();
}
}
private void insertValues(final String key, final String val, Handle handle) {
final String query;
handle
.createStatement(String.format("DELETE FROM %s WHERE %s='%s'", tableName, keyColumn, key))
.setQueryTimeout(1)
.execute();
query =
String.format(
"INSERT INTO %s (%s, %s) VALUES ('%s', '%s')",
tableName, keyColumn, valueColumn, key, val);
Assert.assertEquals(1, handle.createStatement(query).setQueryTimeout(1).execute());
handle.commit();
}
/**
* @param entitySqlDaoTransactionWrapper transaction to execute
* @param <ReturnType> object type to return from the transaction
* @return result from the transaction fo type ReturnType
*/
public <ReturnType> ReturnType execute(
final EntitySqlDaoTransactionWrapper<ReturnType> entitySqlDaoTransactionWrapper) {
final Handle handle = dbi.open();
try {
final EntitySqlDao<EntityModelDao<Entity>, Entity> entitySqlDao =
handle.attach(InitialEntitySqlDao.class);
return entitySqlDao.inTransaction(
TransactionIsolationLevel.READ_COMMITTED,
new JdbiTransaction<ReturnType, EntityModelDao<Entity>, Entity>(
handle, entitySqlDaoTransactionWrapper));
} finally {
handle.close();
}
}
@Test
public void testSimpleCreate() throws Exception {
Foo foo = handle.attach(Foo.class);
foo.insert(1, "Stephane");
Something s = foo.createBar().findById(1);
assertThat(s, equalTo(new Something(1, "Stephane")));
}
@AfterMethod(alwaysRun = true)
public void tearDown() throws Exception {
if (dummyHandle != null) {
dummyHandle.close();
}
deleteRecursively(temporary);
}
/** Called to test if a handle is in a transaction */
public boolean isInTransaction(Handle handle) {
try {
return !handle.getConnection().getAutoCommit();
} catch (SQLException e) {
throw new TransactionException("Failed to check status of transaction", e);
}
}
@Test
public void testTransmogrifiable() throws Exception {
Hobbsian h = handle.attach(Hobbsian.class);
h.insert(2, "Cora");
Something s = h.become(TransactionStuff.class).byId(2);
assertThat(s, equalTo(new Something(2, "Cora")));
}
@Test
public void testNestedTransactions() throws Exception {
Dao dao = handle.attach(Dao.class);
Something s = dao.insertAndFetchWithNestedTransaction(1, "Ian");
assertThat(s, equalTo(new Something(1, "Ian")));
}
@Before
public void setUp() throws Exception {
dbi = new DBI("jdbc:h2:mem:" + UUID.randomUUID());
dbi.registerMapper(new SomethingMapper());
handle = dbi.open();
handle.execute("create table something (id int primary key, name varchar(100))");
}
@Override
public boolean tableExists(Handle handle, String tableName) {
return !handle
.createQuery("select * from SYS.SYSTABLES where tablename = :tableName")
.bind("tableName", tableName.toUpperCase())
.list()
.isEmpty();
}
public void setUp() throws Exception {
JdbcDataSource ds = new JdbcDataSource();
ds.setURL("jdbc:h2:mem:test;MVCC=TRUE");
dbi = new DBI(ds);
handle = dbi.open();
handle.execute("create table something (id int primary key, name varchar(100))");
}
@Before
public void setUp() throws Exception {
UUID uuid = UUID.randomUUID();
dbi = new DBI("jdbc:h2:mem:" + uuid);
handle = dbi.open();
handle.execute("create table something (id int primary key, name varchar(100))");
}
@Override
public boolean tableExists(final Handle handle, final String tableName) {
return !handle
.createQuery("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = :tableName")
.bind("tableName", tableName)
.map(StringMapper.FIRST)
.list()
.isEmpty();
}
public void testJustJdbiTransactions() throws Exception {
Handle h1 = dbi.open();
Handle h2 = dbi.open();
h1.execute("insert into something (id, name) values (8, 'Mike')");
h1.begin();
h1.execute("update something set name = 'Miker' where id = 8");
assertEquals(
"Mike",
h2.createQuery("select name from something where id = 8").map(StringMapper.FIRST).first());
h1.commit();
h1.close();
h2.close();
}
@BeforeMethod
public void setup() throws Exception {
H2EmbeddedDataSourceConfig dataSourceConfig =
new H2EmbeddedDataSourceConfig().setFilename("mem:");
DataSource dataSource = new H2EmbeddedDataSource(dataSourceConfig);
DBI h2Dbi = new DBI(dataSource);
handle = h2Dbi.open();
dao = handle.attach(ShardManagerDao.class);
ShardManagerDao.Utils.createShardTablesWithRetry(dao);
}
@Test
public void testTxFail() throws Exception {
Dao dao = handle.attach(Dao.class);
try {
dao.failed(1, "Ian");
fail("should have raised exception");
} catch (TransactionFailedException e) {
assertThat(e.getCause().getMessage(), equalTo("woof"));
}
assertThat(dao.findById(1), nullValue());
}
@Override
public Long withHandle(final Handle handle) throws Exception {
return (Long)
handle
.select("select count(distinct record_id) count from bus_events")
.get(0)
.get("count")
+ (Long)
handle
.select(
"select count(distinct record_id) count from notifications where effective_date < ?",
clock.getUTCNow().toDate())
.get(0)
.get("count")
+ (Long)
handle
.select(
"select count(distinct record_id) count from notifications where processing_state = 'IN_PROCESSING'")
.get(0)
.get("count");
}
@Override
public <T extends Schedule> void updateSchedules(
int projId, List<T> schedules, ScheduleUpdateAction<T> func)
throws ResourceConflictException {
Map<String, Integer> oldScheduleNames = idNameListToHashMap(dao.getScheduleNames(projId));
// Concurrent call of updateSchedules doesn't happen because having
// ProjectControlStore means that the project is locked.
//
// However, ScheduleExecutor modifies schedules without locking the
// project. Instead, ScheduleExecutor locks schedules. To avoid
// concurrent update of schedules, here needs to lock schedules
// before UPDATE.
for (T schedule : schedules) {
Integer matchedSchedId = oldScheduleNames.get(schedule.getWorkflowName());
if (matchedSchedId != null) {
// found the same name. lock it and update
ScheduleStatus status = dao.lockScheduleById(matchedSchedId);
if (status != null) {
ScheduleTime newSchedule = func.apply(status, schedule);
dao.updateScheduleById(
matchedSchedId,
schedule.getWorkflowDefinitionId(),
newSchedule.getRunTime().getEpochSecond(),
newSchedule.getTime().getEpochSecond());
oldScheduleNames.remove(schedule.getWorkflowName());
}
} else {
// not found this name. inserting a new entry.
catchConflict(
() ->
dao.insertSchedule(
projId,
schedule.getWorkflowDefinitionId(),
schedule.getNextRunTime().getEpochSecond(),
schedule.getNextScheduleTime().getEpochSecond()),
"workflow_definition_id=%d",
schedule.getWorkflowDefinitionId());
}
}
// delete unused schedules
if (!oldScheduleNames.isEmpty()) {
// those names don exist any more.
handle
.createStatement(
"delete from schedules"
+ " where id "
+ inLargeIdListExpression(oldScheduleNames.values()))
.execute();
}
}
private boolean _lock(long duration, TimeUnit unit) {
if (handle == null) {
handle = dbi.open();
int got_lock =
handle
.createQuery("select get_lock(:name, :time)")
.bind("name", name)
.bind("time", unit.toSeconds(duration))
.map(IntegerMapper.FIRST)
.first();
if (got_lock == 1) {
return true;
} else {
handle.close();
handle = null;
return false;
}
} else {
// we already have the lock!
return true;
}
}
public MaterializedResult execute(
Session session, @Language("SQL") String sql, List<? extends Type> resultTypes) {
MaterializedResult materializedRows =
new MaterializedResult(
handle.createQuery(sql).map(rowMapper(resultTypes)).list(), resultTypes);
// H2 produces dates in the JVM time zone instead of the session timezone
materializedRows =
materializedRows.toTimeZone(
DateTimeZone.getDefault(), getDateTimeZone(session.getTimeZoneKey()));
return materializedRows;
}
private static void insertRows(
ConnectorTableMetadata tableMetadata, Handle handle, RecordSet data) {
List<ColumnMetadata> columns =
ImmutableList.copyOf(
Iterables.filter(
tableMetadata.getColumns(),
new Predicate<ColumnMetadata>() {
@Override
public boolean apply(ColumnMetadata columnMetadata) {
return !columnMetadata.isHidden();
}
}));
String vars = Joiner.on(',').join(nCopies(columns.size(), "?"));
String sql =
format("INSERT INTO %s VALUES (%s)", tableMetadata.getTable().getTableName(), vars);
RecordCursor cursor = data.cursor();
while (true) {
// insert 1000 rows at a time
PreparedBatch batch = handle.prepareBatch(sql);
for (int row = 0; row < 1000; row++) {
if (!cursor.advanceNextPosition()) {
batch.execute();
return;
}
PreparedBatchPart part = batch.add();
for (int column = 0; column < columns.size(); column++) {
Type type = columns.get(column).getType();
if (BOOLEAN.equals(type)) {
part.bind(column, cursor.getBoolean(column));
} else if (BIGINT.equals(type)) {
part.bind(column, cursor.getLong(column));
} else if (DOUBLE.equals(type)) {
part.bind(column, cursor.getDouble(column));
} else if (VARCHAR.equals(type)) {
part.bind(column, cursor.getSlice(column).toStringUtf8());
} else if (DATE.equals(type)) {
long millisUtc = TimeUnit.DAYS.toMillis(cursor.getLong(column));
// H2 expects dates in to be millis at midnight in the JVM timezone
long localMillis =
DateTimeZone.UTC.getMillisKeepLocal(DateTimeZone.getDefault(), millisUtc);
part.bind(column, new Date(localMillis));
} else {
throw new IllegalArgumentException("Unsupported type " + type);
}
}
}
batch.execute();
}
}
public Team insertWithTxHandle(final Team team) {
// in this case we use an explicit handle, and attach the dao's to the same handle (connection)
try (Handle handle = jdbiHelper.getTxHandle()) {
handle.begin();
TeamDao teamDao = handle.attach(TeamDao.class);
TeamPersonDao teamPersonDao =
handle.attach(TeamPersonDao.class); // team->person mapping table
long teamId;
if (team.getPointOfContact() != null) {
teamId = teamDao.insertWithPoC(team);
} else {
teamId = teamDao.insertWithoutPoC(team);
}
for (Person p : team.getMembers()) {
// update the team->person mapping table
teamPersonDao.insert(new TeamPerson(teamId, p.getId()));
}
// add test code for checking that TX is handled correctly
checkIfTxShouldBeRolledBack(team);
handle.commit();
return get(teamId);
}
}
public byte[] lookupWithHandle(
final Handle handle,
final String tableName,
final String keyColumn,
final String valueColumn,
final String key) {
final String selectStatement =
String.format("SELECT %s FROM %s WHERE %s = :key", valueColumn, tableName, keyColumn);
List<byte[]> matched =
handle.createQuery(selectStatement).bind("key", key).map(ByteArrayMapper.FIRST).list();
if (matched.isEmpty()) {
return null;
}
if (matched.size() > 1) {
throw new ISE(
"Error! More than one matching entry[%d] found for [%s]?!", matched.size(), key);
}
return matched.get(0);
}
