Java Code Examples for org.skife.jdbi.v2.sqlobject.SqlQuery

The following examples show how to use org.skife.jdbi.v2.sqlobject.SqlQuery. These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source Project: presto   Source File: ShardDao.java    License: Apache License 2.0 6 votes vote down vote up
@SqlQuery("SELECT " + SHARD_METADATA_COLUMNS + "\n" +
        "FROM (\n" +
        "    SELECT s.*\n" +
        "    FROM shards s\n" +
        "    JOIN shard_nodes sn ON (s.shard_id = sn.shard_id)\n" +
        "    JOIN nodes n ON (sn.node_id = n.node_id)\n" +
        "    WHERE n.node_identifier = :nodeIdentifier\n" +
        "      AND s.bucket_number IS NULL\n" +
        "      AND (s.table_id = :tableId OR :tableId IS NULL)\n" +
        "  UNION ALL\n" +
        "    SELECT s.*\n" +
        "    FROM shards s\n" +
        "    JOIN tables t ON (s.table_id = t.table_id)\n" +
        "    JOIN distributions d ON (t.distribution_id = d.distribution_id)\n" +
        "    JOIN buckets b ON (\n" +
        "      d.distribution_id = b.distribution_id AND\n" +
        "      s.bucket_number = b.bucket_number)\n" +
        "    JOIN nodes n ON (b.node_id = n.node_id)\n" +
        "    WHERE n.node_identifier = :nodeIdentifier\n" +
        "      AND (s.table_id = :tableId OR :tableId IS NULL)\n" +
        ") x")
@Mapper(ShardMetadata.Mapper.class)
Set<ShardMetadata> getNodeShards(@Bind("nodeIdentifier") String nodeIdentifier, @Bind("tableId") Long tableId);
 
Example 2
Source Project: presto   Source File: ShardDao.java    License: Apache License 2.0 6 votes vote down vote up
@SqlQuery("SELECT n.node_identifier, x.bytes\n" +
        "FROM (\n" +
        "  SELECT node_id, sum(compressed_size) bytes\n" +
        "  FROM (\n" +
        "      SELECT sn.node_id, s.compressed_size\n" +
        "      FROM shards s\n" +
        "      JOIN shard_nodes sn ON (s.shard_id = sn.shard_id)\n" +
        "      WHERE s.bucket_number IS NULL\n" +
        "    UNION ALL\n" +
        "      SELECT b.node_id, s.compressed_size\n" +
        "      FROM shards s\n" +
        "      JOIN tables t ON (s.table_id = t.table_id)\n" +
        "      JOIN distributions d ON (t.distribution_id = d.distribution_id)\n" +
        "      JOIN buckets b ON (\n" +
        "        d.distribution_id = b.distribution_id AND\n" +
        "        s.bucket_number = b.bucket_number)\n" +
        "  ) x\n" +
        "  GROUP BY node_id\n" +
        ") x\n" +
        "JOIN nodes n ON (x.node_id = n.node_id)")
@Mapper(NodeSize.Mapper.class)
Set<NodeSize> getNodeSizes();
 
Example 3
Source Project: SAPNetworkMonitor   Source File: TaskDao.java    License: GNU General Public License v3.0 6 votes vote down vote up
@SqlQuery("SELECT *, RESULT.ID AS RESULT_ID, RESULT.ERRNO FROM (SELECT T.TASK_ID, MT.MONITOR_ID, M.NAME AS MONITOR_NAME, T.ACCOUNT_ID, T.NAME, TASK_INTERVAL AS 'INTERVAL', CONFIG_JSON, T.STATUS, T.CREATION_TIME, T.MODIFIED_TIME FROM SNM_TASK T " +
"LEFT JOIN SNM_MONITOR_TASK MT ON MT.TASK_ID = T.TASK_ID " +
"LEFT JOIN SNM_MONITOR M ON M.MONITOR_ID = MT.MONITOR_ID " +
"WHERE T.ACCOUNT_ID = :accountId AND T.STATUS <ne> :taskDeleteStatus GROUP BY T.TASK_ID, MONITOR_ID) AS TMP " +
"LEFT JOIN ( " +
        "SELECT R.ID, R.COLLECTED_TIME, R.MONITOR_ID, R.TASK_ID, R.ERRNO " +
        "FROM SNM_NIPING_RESULT AS R " +
        "INNER JOIN  " +
        "( " +
        "SELECT MAX(RES2.ID) AS ID " +
        "FROM SNM_NIPING_RESULT RES2 " +
        "INNER JOIN ( " +
        "SELECT MAX(COLLECTED_TIME) AS T1, MONITOR_ID, TASK_ID FROM SNM_NIPING_RESULT WHERE COLLECTED_TIME <ge> :lasthour AND TYPE = :type GROUP BY TASK_ID, MONITOR_ID " +
        ") AS RES1 " +
        "ON RES2.COLLECTED_TIME = RES1.T1 AND RES1.MONITOR_ID = RES2.MONITOR_ID AND RES1.TASK_ID = RES2.TASK_ID " +
        "GROUP BY RES2.COLLECTED_TIME, RES2.TASK_ID, RES2.MONITOR_ID\n" +
        ") AS RES3 " +
        "ON R.ID = RES3.ID "+
") AS RESULT ON TMP.MONITOR_ID = RESULT.MONITOR_ID AND TMP.TASK_ID = RESULT.TASK_ID " +
" ORDER BY TMP.CREATION_TIME DESC")
@RegisterMapper(TaskMapper.class)
List<Task> selectByAccountId(@Bind("accountId") String accountId, @Bind("taskDeleteStatus") int taskDeleteStatus, @Bind("lasthour") Date lasthour, @Bind("type") int type, @Define("ne") String notEqual, @Define("ge") String greaterThan);
 
Example 4
@SqlQuery("SELECT R.TASK_ID, R.AV2, R.TR2, R.ERRNO, R.MONITOR_ID, M.MONITOR_ID, M.NAME, M.IP, M.COUNTRY, M.PROVINCE, M.CITY, M.ISP " +
        "FROM SNM_NIPING_RESULT AS R " +
        "INNER JOIN " +
        "( " +
        "SELECT MAX(RES2.ID) AS ID " +
        "FROM SNM_NIPING_RESULT RES2 " +
        "INNER JOIN ( " +
        "SELECT MAX(COLLECTED_TIME) AS T1, MONITOR_ID, TASK_ID FROM SNM_NIPING_RESULT WHERE ACCOUNT_ID = :accountId AND TASK_ID = :taskId AND TYPE = :type GROUP BY TASK_ID, MONITOR_ID " +
        ") AS RES1 " +
        "ON RES2.COLLECTED_TIME = RES1.T1 AND RES1.MONITOR_ID = RES2.MONITOR_ID AND RES1.TASK_ID = RES2.TASK_ID " +
        "GROUP BY RES2.COLLECTED_TIME, RES2.TASK_ID, RES2.MONITOR_ID " +
        ") AS RES3 " +
        "ON R.ID = RES3.ID " +
        "INNER JOIN SNM_MONITOR M ON M.MONITOR_ID = R.MONITOR_ID")
@RegisterMapper(MonitorNiPingResultMapper.class)
List<MonitorNiPingResult> selectByTaskId(@Bind("accountId") String accountId, @Bind("taskId") String taskId, @Bind("type") int type);
 
Example 5
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 5 votes vote down vote up
@SqlQuery(TABLE_INFORMATION_SELECT +
        "WHERE t.schema_name = :schemaName\n" +
        "  AND t.table_name = :tableName")
@Mapper(TableMapper.class)
Table getTableInformation(
        @Bind("schemaName") String schemaName,
        @Bind("tableName") String tableName);
 
Example 6
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 5 votes vote down vote up
@SqlQuery(TABLE_COLUMN_SELECT +
        "WHERE t.table_id = :tableId\n" +
        "  AND c.column_id = :columnId\n" +
        "ORDER BY c.ordinal_position\n")
TableColumn getTableColumn(
        @Bind("tableId") long tableId,
        @Bind("columnId") long columnId);
 
Example 7
Source Project: cassandra-reaper   Source File: IStoragePostgreSql.java    License: Apache License 2.0 5 votes vote down vote up
@SqlQuery(SQL_GET_NODE_METRICS_BY_NODE)
@Mapper(NodeMetricsMapper.class)
NodeMetrics getNodeMetricsByNode(
    @Bind("runId") long runId,
    @Bind("expirationTime") Instant expirationTime,
    @Bind("node") String node
);
 
Example 8
Source Project: cassandra-reaper   Source File: IStoragePostgreSql.java    License: Apache License 2.0 5 votes vote down vote up
@SqlQuery(SQL_GET_METRICS_FOR_CLUSTER)
@Mapper(GenericMetricMapper.class)
Collection<GenericMetric> getMetricsForCluster(
    @Bind("cluster") String cluster,
    @Bind("metricDomain") String metricDomain,
    @Bind("metricType") String metricType,
    @Bind("since") Instant since
);
 
Example 9
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 5 votes vote down vote up
@SqlQuery("SELECT schema_name, table_name, create_time, update_time, table_version,\n" +
        "  shard_count, row_count, compressed_size, uncompressed_size\n" +
        "FROM tables\n" +
        "WHERE (schema_name = :schemaName OR :schemaName IS NULL)\n" +
        "  AND (table_name = :tableName OR :tableName IS NULL)\n" +
        "ORDER BY schema_name, table_name")
@Mapper(TableStatsRow.Mapper.class)
List<TableStatsRow> getTableStatsRows(
        @Bind("schemaName") String schemaName,
        @Bind("tableName") String tableName);
 
Example 10
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 5 votes vote down vote up
@SqlQuery("SELECT table_id\n" +
        "FROM tables\n" +
        "WHERE organization_enabled\n" +
        "  AND maintenance_blocked IS NULL\n" +
        "  AND table_id IN\n" +
        "       (SELECT table_id\n" +
        "        FROM columns\n" +
        "        WHERE sort_ordinal_position IS NOT NULL)")
Set<Long> getOrganizationEligibleTables();
 
Example 11
Source Project: presto   Source File: ShardDao.java    License: Apache License 2.0 5 votes vote down vote up
@SqlQuery("SELECT b.bucket_number, n.node_identifier\n" +
        "FROM buckets b\n" +
        "JOIN nodes n ON (b.node_id = n.node_id)\n" +
        "WHERE b.distribution_id = :distributionId\n" +
        "ORDER BY b.bucket_number")
@Mapper(BucketNode.Mapper.class)
List<BucketNode> getBucketNodes(@Bind("distributionId") long distributionId);
 
Example 12
Source Project: cassandra-reaper   Source File: IStoragePostgreSql.java    License: Apache License 2.0 5 votes vote down vote up
@SqlQuery(SQL_GET_METRICS_FOR_HOST)
@Mapper(GenericMetricMapper.class)
Collection<GenericMetric> getMetricsForHost(
    @Bind("cluster") String cluster,
    @Bind("host") String host,
    @Bind("metricDomain") String metricDomain,
    @Bind("metricType") String metricType,
    @Bind("since") Instant since
);
 
Example 13
Source Project: presto   Source File: TestingShardDao.java    License: Apache License 2.0 5 votes vote down vote up
@SqlQuery("SELECT s.shard_uuid, n.node_identifier\n" +
        "FROM shards s\n" +
        "JOIN shard_nodes sn ON (s.shard_id = sn.shard_id)\n" +
        "JOIN nodes n ON (sn.node_id = n.node_id)\n" +
        "WHERE s.table_id = :tableId")
@Mapper(ShardNode.Mapper.class)
Set<ShardNode> getShardNodes(@Bind("tableId") long tableId);
 
Example 14
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 4 votes vote down vote up
@SqlQuery(TABLE_INFORMATION_SELECT +
        "WHERE t.table_id = :tableId")
@Mapper(TableMapper.class)
Table getTableInformation(@Bind("tableId") long tableId);
 
Example 15
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 4 votes vote down vote up
@SqlQuery("SELECT schema_name, table_name\n" +
        "FROM tables\n" +
        "WHERE (schema_name = :schemaName OR :schemaName IS NULL)")
@Mapper(SchemaTableNameMapper.class)
List<SchemaTableName> listTables(
        @Bind("schemaName") String schemaName);
 
Example 16
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 4 votes vote down vote up
@SqlQuery("SELECT DISTINCT schema_name FROM tables")
List<String> listSchemaNames();
 
Example 17
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 4 votes vote down vote up
@SqlQuery(TABLE_COLUMN_SELECT +
        "WHERE t.table_id = :tableId\n" +
        "ORDER BY c.ordinal_position")
List<TableColumn> listTableColumns(@Bind("tableId") long tableId);
 
Example 18
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 4 votes vote down vote up
@SqlQuery(TABLE_COLUMN_SELECT +
        "WHERE t.table_id = :tableId\n" +
        "  AND c.bucket_ordinal_position IS NOT NULL\n" +
        "ORDER BY c.bucket_ordinal_position")
List<TableColumn> listBucketColumns(@Bind("tableId") long tableId);
 
Example 19
Source Project: cassandra-reaper   Source File: IStoragePostgreSql.java    License: Apache License 2.0 4 votes vote down vote up
@SqlQuery(SQL_GET_REPAIR_SEGMENTS_FOR_RUN_WITH_STATE)
@Mapper(RepairSegmentMapper.class)
Collection<RepairSegment> getRepairSegmentsForRunWithState(
    @Bind("runId") long runId,
    @Bind("state") RepairSegment.State state);
 
Example 20
Source Project: cassandra-reaper   Source File: IStoragePostgreSql.java    License: Apache License 2.0 4 votes vote down vote up
@SqlQuery(SQL_GET_EVENT_SUBSCRIPTIONS)
@Mapper(DiagEventSubscriptionMapper.class)
Collection<DiagEventSubscription> getEventSubscriptions();
 
Example 21
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 4 votes vote down vote up
@SqlQuery("SELECT temporal_column_id\n" +
        "FROM tables\n" +
        "WHERE table_id = :tableId\n" +
        "  AND temporal_column_id IS NOT NULL")
Long getTemporalColumnId(@Bind("tableId") long tableId);
 
Example 22
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 4 votes vote down vote up
@SqlQuery("SELECT compaction_enabled AND maintenance_blocked IS NULL\n" +
        "FROM tables\n" +
        "WHERE table_id = :tableId")
boolean isCompactionEligible(@Bind("tableId") long tableId);
 
Example 23
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 4 votes vote down vote up
@SqlQuery("SELECT table_id FROM tables WHERE table_id = :tableId FOR UPDATE")
Long getLockedTableId(@Bind("tableId") long tableId);
 
Example 24
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 4 votes vote down vote up
@SqlQuery("SELECT distribution_id, distribution_name, column_types, bucket_count\n" +
        "FROM distributions\n" +
        "WHERE distribution_id = :distributionId")
Distribution getDistribution(@Bind("distributionId") long distributionId);
 
Example 25
Source Project: cassandra-reaper   Source File: IStoragePostgreSql.java    License: Apache License 2.0 4 votes vote down vote up
@SqlQuery(SQL_GET_NODE_METRICS)
@Mapper(NodeMetricsMapper.class)
Collection<NodeMetrics> getNodeMetrics(
    @Bind("runId") long runId,
    @Bind("expirationTime") Instant expirationTime
);
 
Example 26
Source Project: cassandra-reaper   Source File: IStoragePostgreSql.java    License: Apache License 2.0 4 votes vote down vote up
@SqlQuery(SQL_SELECT_ACTIVE_LEADERS)
List<Long> getLeaders(
    @Bind("expirationTime") Instant expirationTime
);
 
Example 27
Source Project: cassandra-reaper   Source File: IStoragePostgreSql.java    License: Apache License 2.0 4 votes vote down vote up
@SqlQuery(SQL_GET_ALL_REPAIR_SCHEDULES)
@Mapper(RepairScheduleMapper.class)
Collection<RepairSchedule> getAllRepairSchedules();
 
Example 28
Source Project: presto   Source File: MetadataDao.java    License: Apache License 2.0 4 votes vote down vote up
@SqlQuery("SELECT maintenance_blocked IS NOT NULL\n" +
        "FROM tables\n" +
        "WHERE table_id = :tableId\n" +
        "FOR UPDATE")
boolean isMaintenanceBlockedLocked(@Bind("tableId") long tableId);
 
Example 29
Source Project: cassandra-reaper   Source File: IStoragePostgreSql.java    License: Apache License 2.0 4 votes vote down vote up
@SqlQuery(SQL_SEGMENT_AMOUNT_FOR_REPAIR_RUN_WITH_STATE)
int getSegmentAmountForRepairRunWithState(
    @Bind("runId") long runId,
    @Bind("state") RepairSegment.State state);
 
Example 30
Source Project: presto   Source File: ShardDao.java    License: Apache License 2.0 4 votes vote down vote up
@SqlQuery("SELECT node_identifier FROM nodes WHERE node_id = :nodeId")
String getNodeIdentifier(@Bind("nodeId") int nodeId);