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

@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);
 

@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);
 

@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();
 

@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);
 

@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);
 

@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);
 

@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
);
 

@SqlQuery(SQL_GET_NODE_METRICS_BY_NODE)
@Mapper(NodeMetricsMapper.class)
NodeMetrics getNodeMetricsByNode(
    @Bind("runId") long runId,
    @Bind("expirationTime") Instant expirationTime,
    @Bind("node") String node
);
 

@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);
 

@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();
 

@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);
 

@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
);
 

@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);
 

@SqlQuery("select * from rufususer left outer join articles on rufususer.userid = articles.userid where rufususer.userid = :id")
Set<Article> getBookmarked(@Bind("id") long id);
 

@SqlQuery("SELECT R.COLLECTED_TIME, R.TR2 AS VALUE " +
        "FROM SNM_NIPING_RESULT R " +
        "WHERE R.ACCOUNT_ID = :accountId AND R.MONITOR_ID = :monitorId AND R.TASK_ID = :taskId AND TYPE = :type AND R.COLLECTED_TIME <ge> :time AND R.TR2 IS NOT NULL ORDER BY R.COLLECTED_TIME")
@RegisterMapper(MetricsMapper.class)
List<Metrics> selectTRMetrics(@Bind("accountId") String accountId, @Bind("taskId") String taskId, @Bind("monitorId") String monitorId, @Bind("type") int type, @Bind("time") long time, @Define("ge") String ge);
 

@SqlQuery("SELECT CITY FROM SNM_MONITOR M INNER JOIN SNM_NIPING_RESULT R ON R.MONITOR_ID = M.MONITOR_ID WHERE R.ACCOUNT_ID = :accountId AND COUNTRY = :country AND PROVINCE = :province  GROUP BY CITY")
List<String> selectCities(@Bind("accountId") String accountId, @Bind("country") String country, @Bind("province") String province);
 

@SqlQuery(SQL_GET_REPAIR_RUNS_FOR_CLUSTER)
@Mapper(RepairRunMapper.class)
Collection<RepairRun> getRepairRunsForCluster(
    @Bind("clusterName") String clusterName,
    @Bind("limit") int limit);
 

@SqlQuery(SQL_GET_REPAIR_SCHEDULES_FOR_KEYSPACE)
@Mapper(RepairScheduleMapper.class)
Collection<RepairSchedule> getRepairSchedulesForKeyspace(
    @Bind("keyspaceName") String keyspaceName);
 

@SqlQuery("SELECT COUNTRY FROM SNM_MONITOR M INNER JOIN SNM_NIPING_RESULT R ON R.MONITOR_ID = M.MONITOR_ID WHERE R.ACCOUNT_ID = :accountId GROUP BY COUNTRY")
List<String> selectCountries(@Bind("accountId") String accountId);
 

@SqlQuery("SELECT M.MONITOR_ID, M.NAME, M.IP FROM (SELECT MONITOR_ID FROM SNM_NIPING_RESULT WHERE ACCOUNT_ID = :accountId AND TASK_ID = :taskId GROUP BY MONITOR_ID) TMP " +
        " INNER JOIN SNM_MONITOR M ON M.MONITOR_ID = TMP.MONITOR_ID <condition>")
@RegisterMapper(ResultMonitorMapper.class)
List<Monitor> selectMonitors(@Bind("accountId") String accountId, @Bind("taskId") String taskId, @Define("condition") String condition);
 

@SqlQuery(TABLE_INFORMATION_SELECT +
        "WHERE t.table_id = :tableId")
@Mapper(TableMapper.class)
Table getTableInformation(@Bind("tableId") long tableId);
 

@SqlQuery("SELECT R.TASK_ID, NAME FROM SNM_NIPING_RESULT R INNER JOIN SNM_TASK T ON T.TASK_ID = R.TASK_ID WHERE T.ACCOUNT_ID = " +
        ":accountId GROUP BY R.TASK_ID")
@RegisterMapper(ResultTaskMapper.class)
List<Task> selectTasks(@Bind("accountId") String accountId);
 

@SqlQuery("SELECT TASK_ID, ERRNO, ERRNO, TR2, AV2, M.MONITOR_ID, M.NAME, M.IP, M.COUNTRY, M.PROVINCE, M.CITY, M.ISP FROM SNM_NIPING_RESULT R " +
        "INNER JOIN SNM_MONITOR M ON M.MONITOR_ID = R.MONITOR_ID " +
        "WHERE R.ACCOUNT_ID = :accountId AND R.TASK_ID = :taskId AND TYPE = :type AND collected_Time <ge> :startDate")
@RegisterMapper(MonitorNiPingResultMapper.class)
List<MonitorNiPingResult> selectByTaskId(@Bind("accountId") String accountId, @Bind("taskId") String taskId, @Bind("startDate") long startTime, @Bind("type") int type, @Define("ge") String ge);
 

@SqlQuery("SELECT TOKEN FROM SNM_ACCESS_CREDENTIALS WHERE ACCOUNT_ID = :accountId")
String getTokenByAccountId(@Bind("accountId") String accountId);
 

@SqlQuery(SQL_GET_REPAIR_SCHEDULES_FOR_CLUSTER_AND_KEYSPACE)
@Mapper(RepairScheduleMapper.class)
Collection<RepairSchedule> getRepairSchedulesForClusterAndKeySpace(
    @Bind("clusterName") String clusterName,
    @Bind("keyspaceName") String keyspaceName);
 

@SqlQuery(SQL_COUNT_RUNNING_REAPERS)
int countRunningReapers(
    @Bind("expirationTime") Instant expirationTime
);
 

@SqlQuery("SELECT * FROM (SELECT SNM_TASK.TASK_ID, MONITOR_ID, ACCOUNT_ID, NAME, TASK_INTERVAL AS 'INTERVAL', CONFIG_JSON, STATUS, CREATION_TIME, MODIFIED_TIME FROM SNM_TASK LEFT JOIN SNM_MONITOR_TASK ON SNM_MONITOR_TASK.TASK_ID = SNM_TASK.TASK_ID " +
        " WHERE ACCOUNT_ID = :accountId AND SNM_TASK.TASK_ID = :taskId AND SNM_TASK.STATUS <ne> :taskDeleteStatus) AS TMP GROUP BY MONITOR_ID")
@RegisterMapper(TaskMapper.class)
List<Task> get(@Bind("accountId") String accountId, @Bind("taskId") String taskId, @Bind("taskDeleteStatus") int taskDeleteStatus, @Define("ne") String notEqual);
 

@SqlQuery("SELECT TASK_ID, ACCOUNT_ID, NAME, TASK_INTERVAL AS 'INTERVAL', " +
        " CONFIG_JSON, STATUS, CREATION_TIME, MODIFIED_TIME FROM SNM_TASK " +
        "WHERE ACCOUNT_ID = :accountId")
@RegisterMapper(TaskMapper.class)
List<Task> selectByAccountId(@Bind("accountId") String accountId);
 

@SqlQuery(SQL_GET_REPAIR_RUNS_WITH_STATE)
@Mapper(RepairRunMapper.class)
Collection<RepairRun> getRepairRunsWithState(
    @Bind("state") RepairRun.RunState state);
 

@SqlQuery("SELECT R.COLLECTED_TIME, R.AV2 AS VALUE " +
        "FROM SNM_NIPING_RESULT R " +
        "WHERE R.ACCOUNT_ID = :accountId AND R.MONITOR_ID = :monitorId AND R.TASK_ID = :taskId AND TYPE = :type AND R.COLLECTED_TIME <ge> :time AND R.AV2 IS NOT NULL <condition> ORDER BY R.COLLECTED_TIME")
@RegisterMapper(MetricsMapper.class)
List<Metrics> selectAVMetrics(@Bind("accountId") String accountId, @Bind("taskId") String taskId, @Bind("monitorId") String monitorId, @Bind("type") int type, @Bind("time") long time, @Define("ge") String ge);