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

@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 *, 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 " + 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("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("SELECT schema_name, table_name, data\n" +
        "FROM views\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\n")
@Mapper(ViewResult.Mapper.class)
List<ViewResult> getViews(
        @Bind("schemaName") String schemaName,
        @Bind("tableName") String tableName);
 

@SqlUpdate("UPDATE tables SET\n" +
        "  shard_count = shard_count + :shardCount \n" +
        ", row_count = row_count + :rowCount\n" +
        ", compressed_size = compressed_size + :compressedSize\n" +
        ", uncompressed_size = uncompressed_size + :uncompressedSize\n" +
        "WHERE table_id = :tableId")
void updateTableStats(
        @Bind("tableId") long tableId,
        @Bind("shardCount") long shardCount,
        @Bind("rowCount") long rowCount,
        @Bind("compressedSize") long compressedSize,
        @Bind("uncompressedSize") long uncompressedSize);
 

@SqlQuery(TABLE_COLUMN_SELECT +
        "WHERE (schema_name = :schemaName OR :schemaName IS NULL)\n" +
        "  AND (table_name = :tableName OR :tableName IS NULL)\n" +
        "ORDER BY schema_name, table_name, ordinal_position")
List<TableColumn> listTableColumns(
        @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(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);
 

@SqlUpdate("UPDATE tables SET\n" +
        "  update_time = :updateTime\n" +
        ", table_version = table_version + 1\n" +
        "WHERE table_id = :tableId")
void updateTableVersion(
        @Bind("tableId") long tableId,
        @Bind("updateTime") long updateTime);
 

@SqlUpdate("INSERT INTO columns (table_id, column_id, column_name, ordinal_position, data_type, sort_ordinal_position, bucket_ordinal_position)\n" +
        "VALUES (:tableId, :columnId, :columnName, :ordinalPosition, :dataType, :sortOrdinalPosition, :bucketOrdinalPosition)")
void insertColumn(
        @Bind("tableId") long tableId,
        @Bind("columnId") long columnId,
        @Bind("columnName") String columnName,
        @Bind("ordinalPosition") int ordinalPosition,
        @Bind("dataType") String dataType,
        @Bind("sortOrdinalPosition") Integer sortOrdinalPosition,
        @Bind("bucketOrdinalPosition") Integer bucketOrdinalPosition);
 

@SqlUpdate("INSERT INTO shards (shard_uuid, table_id, bucket_number, create_time, row_count, compressed_size, uncompressed_size, xxhash64)\n" +
        "VALUES (:shardUuid, :tableId, :bucketNumber, CURRENT_TIMESTAMP, :rowCount, :compressedSize, :uncompressedSize, :xxhash64)")
@GetGeneratedKeys
long insertShard(
        @Bind("shardUuid") UUID shardUuid,
        @Bind("tableId") long tableId,
        @Bind("bucketNumber") Integer bucketNumber,
        @Bind("rowCount") long rowCount,
        @Bind("compressedSize") long compressedSize,
        @Bind("uncompressedSize") long uncompressedSize,
        @Bind("xxhash64") long xxhash64);
 

@Override
// 'order by' is needed in this statement in order to make it compatible with statement-based replication
@SqlUpdate("DELETE FROM transactions\n" +
        "WHERE end_time < :maxEndTime\n" +
        "  AND successful IN (TRUE, FALSE)\n" +
        "  AND transaction_id NOT IN (SELECT transaction_id FROM created_shards)\n" +
        "ORDER BY end_time, transaction_id\n" +
        "LIMIT " + CLEANUP_TRANSACTIONS_BATCH_SIZE)
int deleteOldCompletedTransactions(@Bind("maxEndTime") Timestamp maxEndTime);
 

@Override
@SqlUpdate("DELETE FROM transactions\n" +
        "WHERE end_time < :maxEndTime\n" +
        "  AND successful IN (TRUE, FALSE)\n" +
        "  AND transaction_id NOT IN (SELECT transaction_id FROM created_shards)\n" +
        "LIMIT " + CLEANUP_TRANSACTIONS_BATCH_SIZE)
int deleteOldCompletedTransactions(@Bind("maxEndTime") Timestamp maxEndTime);
 

@SqlUpdate("UPDATE shard_organizer_jobs SET last_start_time = :lastStartTime\n" +
        "   WHERE node_identifier = :nodeIdentifier\n" +
        "     AND table_id = :tableId")
void updateLastStartTime(
        @Bind("nodeIdentifier") String nodeIdentifier,
        @Bind("tableId") long tableId,
        @Bind("lastStartTime") long lastStartTime);
 

@SqlUpdate("UPDATE tables SET\n" +
        "  schema_name = :newSchemaName\n" +
        ", table_name = :newTableName\n" +
        "WHERE table_id = :tableId")
void renameTable(
        @Bind("tableId") long tableId,
        @Bind("newSchemaName") String newSchemaName,
        @Bind("newTableName") String newTableName);
 

@SqlUpdate("INSERT INTO distributions (distribution_name, column_types, bucket_count)\n" +
        "VALUES (:distributionName, :columnTypes, :bucketCount)")
@GetGeneratedKeys
long insertDistribution(
        @Bind("distributionName") String distributionName,
        @Bind("columnTypes") String columnTypes,
        @Bind("bucketCount") int bucketCount);
 

@SqlUpdate("UPDATE columns SET column_name = :target\n" +
        "WHERE table_id = :tableId\n" +
        "  AND column_id = :columnId")
void renameColumn(
        @Bind("tableId") long tableId,
        @Bind("columnId") long columnId,
        @Bind("target") String target);
 

@SqlUpdate("INSERT INTO deleted_shards (shard_uuid, delete_time)\n" +
        "SELECT shard_uuid, CURRENT_TIMESTAMP\n" +
        "FROM shards\n" +
        "WHERE table_id = :tableId")
void insertDeletedShards(@Bind("tableId") long tableId);
 

@SqlUpdate("update sources set frontpage = FALSE where source = :source")
void removeFrontpage(@Bind("id") long id, @BindSource Source source);
 

@SqlQuery("SELECT successful FROM transactions WHERE transaction_id = :transactionId")
Boolean transactionSuccessful(@Bind("transactionId") long transactionId);
 

@SqlUpdate("DELETE FROM created_shards WHERE transaction_id = :transactionId")
void deleteCreatedShards(@Bind("transactionId") long transactionId);
 

@SqlUpdate("INSERT INTO created_shards (shard_uuid, transaction_id)\n" +
        "VALUES (:shardUuid, :transactionId)")
void insertCreatedShard(
        @Bind("shardUuid") UUID shardUuid,
        @Bind("transactionId") long transactionId);
 

@SqlUpdate("UPDATE SNM_MONITOR_TASK SET REDISPATCHER = :needRedispatcher WHERE TASK_ID = :taskId")
void updateMonitorTaskRedispatcher(@Bind("taskId") String taskId, @Bind("needRedispatcher") int needRedispatcher);
 

@SqlBatch("DELETE FROM deleted_shards WHERE shard_uuid = :shardUuid")
void deleteCleanedShards(@Bind("shardUuid") Iterable<UUID> shardUuids);
 

@SqlUpdate("insert into sources(userid, source, frontpage) values((select userid from rufususer where userid = :id), :source, true)")
void addFrontpageSource(@Bind("id") long id, @Bind("source") String source);
 

@SqlUpdate("update sources set frontpage = TRUE where source = :source")
void setFrontpage(@Bind("id") long id, @BindSource Source source);
 

@SqlQuery("SELECT MONITOR_ID, VERSION, ACCOUNT_ID, NAME, COUNTRY, PROVINCE, CITY, ISP, AREA, IP, NIPING_T, STATUS, CREATION_TIME, MODIFIED_TIME FROM SNM_MONITOR " +
        " WHERE ACCOUNT_ID = :accountId AND STATUS = :monitorEnableStatus")
@RegisterMapper(MonitorMapper.class)
List<Monitor> selectByAccountId( @Bind("accountId") String accountId,  @Bind("monitorEnableStatus") int status);
 

@SqlUpdate("UPDATE transactions SET\n" +
        "  successful = FALSE\n" +
        ", end_time = CURRENT_TIMESTAMP\n" +
        "WHERE successful IS NULL\n" +
        "  AND start_time < :maxStartTime")
void abortOldTransactions(@Bind("maxStartTime") Timestamp maxStartTime);
 

@SqlQuery("SELECT shard_uuid\n" +
        "FROM deleted_shards\n" +
        "WHERE delete_time < :maxDeleteTime\n" +
        "LIMIT " + CLEANABLE_SHARDS_BATCH_SIZE)
Set<UUID> getCleanableShardsBatch(@Bind("maxDeleteTime") Timestamp maxDeleteTime);