コード例 #1
0
ファイル: MySQLUtils.java プロジェクト: nilwyh/monasca-api
  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;
  }
コード例 #2
0
  @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();
  }
コード例 #3
0
  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();
  }
コード例 #4
0
ファイル: H2QueryRunner.java プロジェクト: Rokum/presto
  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()));
  }
コード例 #5
0
ファイル: MySqlLock.java プロジェクト: ning/collector
 @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;
   }
 }
コード例 #6
0
  @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));
  }
コード例 #7
0
 @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);
   }
 }
コード例 #8
0
  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();
    }
  }
コード例 #9
0
 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();
   }
 }
コード例 #11
0
 @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")));
 }
コード例 #12
0
 @AfterMethod(alwaysRun = true)
 public void tearDown() throws Exception {
   if (dummyHandle != null) {
     dummyHandle.close();
   }
   deleteRecursively(temporary);
 }
コード例 #13
0
 /** 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);
   }
 }
コード例 #14
0
 @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")));
 }
コード例 #15
0
  @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")));
  }
コード例 #16
0
  @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))");
  }
コード例 #17
0
 @Override
 public boolean tableExists(Handle handle, String tableName) {
   return !handle
       .createQuery("select * from SYS.SYSTABLES where tablename = :tableName")
       .bind("tableName", tableName.toUpperCase())
       .list()
       .isEmpty();
 }
コード例 #18
0
  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))");
  }
コード例 #19
0
  @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))");
  }
コード例 #20
0
ファイル: SQLServerConnector.java プロジェクト: jon-wei/druid
 @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();
 }
コード例 #21
0
  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();
  }
コード例 #22
0
  @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);
  }
コード例 #23
0
  @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());
  }
コード例 #24
0
 @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");
 }
コード例 #25
0
    @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();
      }
    }
コード例 #26
0
ファイル: MySqlLock.java プロジェクト: ning/collector
 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;
   }
 }
コード例 #27
0
ファイル: H2QueryRunner.java プロジェクト: Rokum/presto
  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;
  }
コード例 #28
0
ファイル: H2QueryRunner.java プロジェクト: Rokum/presto
  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();
    }
  }
コード例 #29
0
ファイル: TeamDaoJdbi.java プロジェクト: jd73/jdbi-examples
  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);
    }
  }
コード例 #30
0
  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);
  }