protected static final String |
ADD_BLACKLISTED_CHIP |
"INSERT INTO blacklisted_chips(board_id, coord_id, notes) WITH args(board_id, x, y) AS (SELECT :board_id, :x, :y),m(model) AS (SELECT board_model FROM machines JOIN boards USING (machine_id) JOIN args USING (board_id)) SELECT args.board_id, coord_id, NULL FROM board_model_coords JOIN m USING (model) JOIN args WHERE chip_x = args.x AND chip_y = args.y" |
protected static final String |
ADD_BLACKLISTED_CORE |
"INSERT INTO blacklisted_cores(board_id, coord_id, physical_core, notes) WITH args(board_id, x, y, p) AS (SELECT :board_id, :x, :y, :p), m(model) AS (SELECT board_model FROM machines JOIN boards USING (machine_id) JOIN args USING (board_id)) SELECT args.board_id, coord_id, p, NULL FROM board_model_coords JOIN m USING (model) JOIN args WHERE chip_x = args.x AND chip_y = args.y" |
protected static final String |
ADD_BLACKLISTED_LINK |
"INSERT INTO blacklisted_links(board_id, coord_id, direction, notes) WITH args(board_id, x, y, dir) AS (SELECT :board_id, :x, :y, :direction), m(model) AS (SELECT board_model FROM machines JOIN boards USING (machine_id) JOIN args USING (board_id)) SELECT args.board_id, coord_id, dir, NULL FROM board_model_coords JOIN m USING (model) JOIN args WHERE chip_x = args.x AND chip_y = args.y" |
protected static final String |
ADD_USER_TO_GROUP |
"INSERT INTO group_memberships(user_id, group_id) VALUES (:user_id, :group_id) ON DUPLICATE KEY UPDATE user_id=user_id;" |
protected static final String |
ADJUST_QUOTA |
"UPDATE user_groups SET quota = GREATEST(0, quota + :delta) WHERE group_id = :group_id AND quota IS NOT NULL" |
protected static final String |
ALLOCATE_BOARDS_BOARD |
"UPDATE boards SET allocated_job = :job_id WHERE board_id = :board_id" |
protected static final String |
ALLOCATE_BOARDS_JOB |
"UPDATE jobs SET width = :width, height = :height, depth = :depth, root_id = :board_id, allocation_size = :num_boards, allocation_timestamp = UNIX_TIMESTAMP(), allocated_root = :allocated_board_id WHERE job_id = :job_id" |
protected static final String |
BUMP_IMPORTANCE |
"UPDATE job_request SET importance = importance + priority" |
protected static final String |
CHECK_LOCATION |
"SELECT x, y, z FROM boards WHERE boards.machine_id = :machine_id AND boards.board_id = :board_id AND (boards.functioning is NULL or boards.functioning != 0)" |
protected static final String |
CLEAR_BLACKLISTED_CHIPS |
"DELETE FROM blacklisted_chips WHERE board_id = :board_id" |
protected static final String |
CLEAR_BLACKLISTED_CORES |
"DELETE FROM blacklisted_cores WHERE board_id = :board_id" |
protected static final String |
CLEAR_BLACKLISTED_LINKS |
"DELETE FROM blacklisted_links WHERE board_id = :board_id" |
protected static final String |
COMPLETED_BLACKLIST_WRITE |
"UPDATE blacklist_ops SET completed = 1 WHERE op_id = :op_id" |
protected static final String |
COMPLETED_BOARD_INFO_READ |
"UPDATE blacklist_ops SET data = :data, completed = 1 WHERE op_id = :op_id" |
protected static final String |
COMPLETED_GET_SERIAL_REQ |
"UPDATE blacklist_ops SET completed = 1 WHERE op_id = :op_id" |
protected static final String |
COUNT_CHANGES_FOR_JOB |
"SELECT COUNT(change_id) as n_changes, COALESCE(SUM(is_error), 0) as n_errors FROM pending_changes WHERE job_id = :job_id AND from_state = :from_state AND to_state = :to_state" |
protected static final String |
COUNT_FUNCTIONING_BOARDS |
"SELECT COUNT(*) as c FROM boards WHERE machine_id = :machine_id AND (functioning IS NULL or functioning != 0) ORDER BY power_off_timestamp ASC LIMIT 1" |
protected static final String |
COUNT_MACHINE_THINGS |
"WITH args(m) AS (SELECT :machine_id), b AS (SELECT * from boards,args WHERE machine_id = m), bc AS (SELECT COUNT(*) AS c FROM b), iu AS (SELECT COUNT(*) AS c FROM b WHERE allocated_job IS NOT NULL), jc AS (SELECT COUNT(*) AS c FROM jobs,args WHERE machine_id = m AND job_state != 4) SELECT bc.c AS board_count, iu.c AS in_use, jc.c AS num_jobs FROM bc, iu, jc" |
protected static final String |
COUNT_POWERED_BOARDS |
"SELECT COUNT(*) AS c FROM boards WHERE allocated_job = :job_id AND board_power" |
protected static final String |
CREATE_BLACKLIST_READ |
"INSERT INTO blacklist_ops(board_id, op, completed) VALUES(:board_id, 0, 0)" |
protected static final String |
CREATE_BLACKLIST_WRITE |
"INSERT INTO blacklist_ops(board_id, op, completed, data) VALUES(:board_id, 1, 0, :blacklist)" |
protected static final String |
CREATE_GROUP |
"INSERT INTO user_groups(group_name, quota, group_type) VALUES(:group_name, :quota, :group_type)" |
protected static final String |
CREATE_GROUP_IF_NOT_EXISTS |
"INSERT IGNORE INTO user_groups(group_name, quota, group_type) VALUES(:group_name, :quota, :group_type)" |
protected static final String |
CREATE_SERIAL_READ_REQ |
"INSERT INTO blacklist_ops(board_id, op, completed) VALUES(:board_id, 2, 0)" |
protected static final String |
CREATE_TEMP_READ_REQ |
"INSERT INTO blacklist_ops(board_id, op, completed) VALUES(:board_id, 3, 0)" |
protected static final String |
CREATE_USER |
"INSERT IGNORE INTO user_info(user_name, encrypted_password, trust_level, disabled, openid_subject) VALUES(:user_name, :encoded_password, :trust_level, :disabled, :openid_subject)" |
protected static final String |
DEALLOCATE_BMP_BOARDS_JOB |
"UPDATE boards SET allocated_job = NULL WHERE allocated_job = :job_id AND bmp_id = :bmp_id" |
protected static final String |
DECREMENT_QUOTA |
"UPDATE user_groups SET quota = quota - :usage WHERE group_id = :group_id AND quota IS NOT NULL" |
protected static final String |
DELETE_ALLOC_RECORD |
"DELETE FROM old_board_allocations WHERE alloc_id = :alloc_id" |
protected static final String |
DELETE_BLACKLIST_OP |
"DELETE FROM blacklist_ops WHERE op_id = :op_id" |
protected static final String |
DELETE_GROUP |
"DELETE FROM user_groups WHERE group_id = :group_id " |
protected static final String |
DELETE_JOB_RECORD |
"DELETE FROM jobs WHERE job_id = :job_id" |
protected static final String |
DELETE_MACHINE_TAGS |
"DELETE FROM tags WHERE machine_id = :machine_id" |
protected static final String |
DELETE_NMPI_JOB |
"DELETE FROM job_nmpi_job WHERE nmpi_job_id = :nmpi_job_id" |
protected static final String |
DELETE_NMPI_SESSION |
"DELETE FROM job_nmpi_session WHERE session_id = :session_id" |
protected static final String |
DELETE_PENDING |
"DELETE FROM pending_changes WHERE job_id = :job_id AND from_state = :from_state AND to_state = :to_state" |
protected static final String |
DELETE_TASK |
"DELETE FROM job_request WHERE job_id = :job_id" |
protected static final String |
DELETE_USER |
"DELETE FROM user_info WHERE user_id = :user_id" |
protected static final String |
DESTROY_JOB |
"UPDATE jobs SET job_state = 4, death_reason = :death_reason, death_timestamp = UNIX_TIMESTAMP() WHERE job_id = :job_id AND job_state != 4" |
protected static final String |
ERROR_PENDING |
"UPDATE pending_changes SET is_error=1 WHERE change_id = :change_id" |
protected static final String |
FAILED_BLACKLIST_OP |
"UPDATE blacklist_ops SET failure = :failure, completed = 1 WHERE op_id = :op_id" |
protected static final String |
FIND_BOARD_BY_ID |
"SELECT boards.board_id, boards.x, boards.y, boards.z, bmp.cabinet, bmp.frame, board_num, boards.address, machines.machine_name, bmp_serial_id, physical_serial_id, bmp_id, boards.machine_id FROM boards JOIN machines USING (machine_id) JOIN bmp USING (bmp_id) LEFT JOIN board_serial USING (board_id) WHERE boards.board_id = :board_id LIMIT 1" |
protected static final String |
FIND_BOARD_BY_NAME_AND_CFB |
"SELECT boards.board_id, boards.x, boards.y, boards.z, bmp.cabinet, bmp.frame, board_num, boards.address, machines.machine_name, bmp_serial_id, physical_serial_id, bmp_id, boards.machine_id FROM boards JOIN machines USING (machine_id) JOIN bmp USING (bmp_id) LEFT JOIN board_serial USING (board_id) WHERE machine_name = :machine_name AND bmp.cabinet = :cabinet AND bmp.frame = :frame AND boards.board_num IS NOT NULL AND boards.board_num = :board LIMIT 1" |
protected static final String |
FIND_BOARD_BY_NAME_AND_IP_ADDRESS |
"SELECT boards.board_id, boards.x, boards.y, boards.z, bmp.cabinet, bmp.frame, board_num, boards.address, machines.machine_name, bmp_serial_id, physical_serial_id, bmp_id, boards.machine_id FROM boards JOIN machines USING (machine_id) JOIN bmp USING (bmp_id) LEFT JOIN board_serial USING (board_id) WHERE machine_name = :machine_name AND boards.address IS NOT NULL AND boards.address = :address LIMIT 1" |
protected static final String |
FIND_BOARD_BY_NAME_AND_XYZ |
"SELECT boards.board_id, boards.x, boards.y, boards.z, bmp.cabinet, bmp.frame, board_num, boards.address, machines.machine_name, bmp_serial_id, physical_serial_id, bmp_id, boards.machine_id FROM boards JOIN machines USING (machine_id) JOIN bmp USING (bmp_id) LEFT JOIN board_serial USING (board_id) WHERE machine_name = :machine_name AND x = :x AND y = :y AND z = :z LIMIT 1" |
protected static final String |
FIND_EXPIRED_JOBS |
"SELECT job_id FROM jobs WHERE job_state != 4 AND keepalive_timestamp + keepalive_interval < UNIX_TIMESTAMP()" |
protected static final String |
FIND_FREE_BOARD |
"SELECT x, y, z FROM boards WHERE machine_id = :machine_id AND may_be_allocated ORDER BY power_off_timestamp ASC LIMIT 1" |
protected static final String |
FIND_LOCATION |
"SELECT x, y, z FROM boards WHERE boards.machine_id = :machine_id AND boards.board_id = :board_id AND boards.may_be_allocated" |
protected static final String |
FINISHED_PENDING |
"DELETE FROM pending_changes WHERE change_id = :change_id" |
protected static final String |
GET_ALL_BMP_BOARDS |
"SELECT board_id, board_num, address FROM boards WHERE bmp_id = :bmp_id" |
protected static final String |
GET_ALL_BMPS |
"SELECT bmp_id, machine_name, address, cabinet, frame FROM bmp JOIN machines on bmp.machine_id = machines.machine_id" |
protected static final String |
GET_ALL_BOARDS |
"SELECT board_id, x, y, z, bmp.cabinet, bmp.frame, board_num, boards.address, bmp_id, boards.machine_id FROM boards JOIN bmp USING (bmp_id) WHERE boards.machine_id = :machine_id AND board_num IS NOT NULL ORDER BY z ASC, x ASC, y ASC" |
protected static final String |
GET_ALL_BOARDS_OF_ALL_MACHINES |
"SELECT board_id, x, y, z, bmp.cabinet, bmp.frame, board_num, boards.address, bmp_id, boards.machine_id FROM boards JOIN bmp USING (bmp_id) WHERE board_num IS NOT NULL ORDER BY z ASC, x ASC, y ASC" |
protected static final String |
GET_ALL_MACHINES |
"SELECT machine_id, machine_name, width, height, in_service FROM machines WHERE in_service OR :allow_out_of_service ORDER BY machine_name ASC" |
protected static final String |
GET_AVAILABLE_BOARD_NUMBERS |
"SELECT board_num FROM boards WHERE machine_id = :machine_id AND may_be_allocated ORDER BY board_num ASC" |
protected static final String |
GET_BLACKLIST_READS |
"SELECT op_id, board_id, board_serial.bmp_serial_id, board_num, cabinet, frame, boards.machine_id FROM blacklist_ops JOIN boards USING (board_id) JOIN bmp USING (bmp_id) LEFT JOIN board_serial USING (board_id) WHERE op = 0 AND NOT completed AND boards.bmp_id = :bmp_id" |
protected static final String |
GET_BLACKLIST_WRITES |
"SELECT op_id, board_id, board_serial.bmp_serial_id, board_num, cabinet, frame, data, boards.machine_id FROM blacklist_ops JOIN boards USING (board_id) JOIN bmp USING (bmp_id) LEFT JOIN board_serial USING (board_id) WHERE op = 1 AND NOT completed AND boards.bmp_id = :bmp_id" |
protected static final String |
GET_BLACKLISTED_CHIPS |
"SELECT chip_x AS x, chip_y AS y, notes FROM blacklisted_chips JOIN board_model_coords USING (coord_id) WHERE board_id = :board_id" |
protected static final String |
GET_BLACKLISTED_CORES |
"SELECT chip_x AS x, chip_y AS y, physical_core AS p, notes FROM blacklisted_cores JOIN board_model_coords USING (coord_id) WHERE board_id = :board_id" |
protected static final String |
GET_BLACKLISTED_LINKS |
"SELECT chip_x AS x, chip_y AS y, direction, notes FROM blacklisted_links JOIN board_model_coords USING (coord_id) WHERE board_id = :board_id" |
protected static final String |
GET_BMP_ADDRESS |
"SELECT address FROM bmp WHERE machine_id = :machine_id AND cabinet = :cabinet AND frame = :frame LIMIT 1" |
protected static final String |
GET_BMP_BOARD_NUMBERS |
"SELECT board_num FROM boards JOIN bmp USING (bmp_id) WHERE boards.machine_id = :machine_id AND cabinet = :cabinet AND frame = :frame AND board_num IS NOT NULL AND (functioning IS NULL OR functioning != 0) ORDER BY board_num ASC" |
protected static final String |
GET_BOARD_ADDRESS |
"SELECT address FROM boards WHERE board_id = :board_id LIMIT 1" |
protected static final String |
GET_BOARD_BY_COORDS |
"SELECT board_id FROM boards WHERE machine_id = :machine_id AND x = :x AND y = :y AND z = :z AND may_be_allocated LIMIT 1" |
protected static final String |
GET_BOARD_CONNECT_INFO |
"SELECT board_id, address, x, y, z, root_x, root_y FROM boards JOIN jobs ON boards.allocated_job = jobs.job_id WHERE allocated_job = :job_id AND jobs.job_state != 4 ORDER BY x ASC, y ASC" |
protected static final String |
GET_BOARD_JOB |
"SELECT allocated_job FROM boards WHERE board_id = :board_id LIMIT 1" |
protected static final String |
GET_BOARD_NUMBERS |
"SELECT board_num FROM boards WHERE machine_id = :machine_id AND board_num IS NOT NULL AND (functioning IS NULL OR functioning != 0) ORDER BY board_num ASC" |
protected static final String |
GET_BOARD_POWER_INFO |
"SELECT board_power, power_off_timestamp, power_on_timestamp FROM boards WHERE board_id = :board_id LIMIT 1" |
protected static final String |
GET_BOARD_REPORTS |
"SELECT board_id, report_id, reported_issue, report_timestamp, user_name AS reporter_name FROM board_reports JOIN user_info ON reporter = user_id WHERE board_id = :board_id" |
protected static final String |
GET_CHANGES |
"SELECT change_id, job_id, pending_changes.board_id, power, fpga_n, fpga_s, fpga_e, fpga_w, fpga_se, fpga_nw, from_state, to_state, board_num, bmp_id, power_off_timestamp FROM pending_changes JOIN boards USING (board_id) WHERE bmp_id = :bmp_id ORDER BY change_id" |
protected static final String |
GET_COMPLETED_BLACKLIST_OP |
"SELECT board_id, op, data, failure, failure IS NOT NULL AS failed FROM blacklist_ops WHERE op_id = :op_id AND completed LIMIT 1" |
protected static final String |
GET_CONSOLIDATION_TARGETS |
"SELECT job_id, group_id, quota_used FROM jobs_usage WHERE complete AND quota IS NOT NULL" |
protected static final String |
GET_CURRENT_USAGE |
"SELECT COALESCE(SUM(quota_used), 0) AS current_usage FROM jobs_usage WHERE group_id = :group_id" |
protected static final String |
GET_DEAD_BOARDS |
"SELECT board_id, x, y, z, bmp.cabinet, bmp.frame, board_num, boards.address, bmp_id, boards.machine_id FROM boards JOIN bmp USING (bmp_id) WHERE boards.machine_id = :machine_id AND (board_num IS NULL OR functioning = 0) ORDER BY z ASC, x ASC, y ASC" |
protected static final String |
GET_FUNCTIONING_FIELD |
"SELECT functioning FROM boards WHERE board_id = :board_id LIMIT 1" |
protected static final String |
GET_GROUP_BY_ID |
"SELECT group_id, group_name, quota, group_type FROM user_groups WHERE group_id = :group_id LIMIT 1" |
protected static final String |
GET_GROUP_BY_NAME |
"SELECT group_id, group_name, quota, group_type FROM user_groups WHERE group_name = :group_name LIMIT 1" |
protected static final String |
GET_GROUP_BY_NAME_AND_MEMBER |
"SELECT user_groups.group_id FROM user_groups JOIN group_memberships USING (group_id) JOIN user_info USING (user_id) WHERE user_name = :user_name AND group_name = :group_name LIMIT 1" |
protected static final String |
GET_GROUP_NAMES_OF_USER |
"SELECT user_groups.group_name FROM group_memberships JOIN user_info USING (user_id) JOIN user_groups USING (group_id) WHERE user_name = :user_name" |
protected static final String |
GET_GROUP_QUOTA |
"SELECT quota FROM user_groups WHERE group_id = :group_id LIMIT 1" |
protected static final String |
GET_JOB |
"SELECT job_id, jobs.machine_id, machines.machine_name, jobs.width, jobs.height, jobs.depth, root_id, job_state, keepalive_timestamp, keepalive_host, keepalive_interval, create_timestamp, death_reason, death_timestamp, original_request, user_info.user_name AS owner FROM jobs JOIN user_info ON jobs.owner = user_info.user_id JOIN machines USING (machine_id) WHERE job_id = :job_id LIMIT 1" |
protected static final String |
GET_JOB_BOARD_COORDS |
"SELECT board_id, x, y, z, bmp.cabinet, bmp.frame, board_num, boards.address, bmp_id, boards.machine_id FROM boards JOIN bmp USING (bmp_id) WHERE boards.allocated_job = :job_id ORDER BY z ASC, x ASC, y ASC" |
protected static final String |
GET_JOB_BOARDS |
"SELECT board_id, bmp_id, boards.machine_id FROM boards JOIN jobs ON boards.allocated_job = jobs.job_id WHERE boards.allocated_job = :job_id" |
protected static final String |
GET_JOB_CHIP_DIMENSIONS |
"WITH b AS (SELECT * FROM boards WHERE allocated_job = :job_id), c AS (SELECT root_x + chip_x AS x, root_y + chip_y AS y FROM b JOIN machines USING (machine_id) JOIN board_model_coords ON machines.board_model = board_model_coords.model) SELECT MAX(x) - MIN(x) + 1 AS width, MAX(y) - MIN(y) + 1 AS height FROM c LIMIT 1" |
protected static final String |
GET_JOB_IDS |
"SELECT job_id, machine_id, job_state, keepalive_timestamp FROM jobs ORDER BY job_id DESC LIMIT :limit OFFSET :offset" |
protected static final String |
GET_JOB_NMPI_JOB |
"SELECT nmpi_job_id, quota_units FROM job_nmpi_job WHERE job_id=:job_id" |
protected static final String |
GET_JOB_SESSION |
"SELECT session_id, quota_units FROM job_nmpi_session WHERE job_id=:job_id" |
protected static final String |
GET_JOB_USAGE_AND_QUOTA |
"SELECT quota_used, quota FROM jobs_usage WHERE job_id = :job_id AND quota_used IS NOT NULL AND quota IS NOT NULL LIMIT 1" |
protected static final String |
GET_LIVE_BOARDS |
"SELECT board_id, x, y, z, bmp.cabinet, bmp.frame, board_num, boards.address, bmp_id, boards.machine_id FROM boards JOIN bmp USING (bmp_id) WHERE boards.machine_id = :machine_id AND board_num IS NOT NULL AND functioning = 1 ORDER BY z ASC, x ASC, y ASC" |
protected static final String |
GET_LIVE_JOB_IDS |
"SELECT job_id, machine_id, job_state, keepalive_timestamp FROM jobs WHERE job_state != 4 ORDER BY job_id DESC LIMIT :limit OFFSET :offset" |
protected static final String |
GET_LOCAL_USER_DETAILS |
"SELECT user_id, user_name, encrypted_password FROM user_info WHERE user_name = :user_name AND is_internal LIMIT 1" |
protected static final String |
GET_MACHINE_BY_ID |
"SELECT machine_id, machine_name, width, height, in_service FROM machines WHERE machine_id = :machine_id AND (in_service OR :allow_out_of_service) LIMIT 1" |
protected static final String |
GET_MACHINE_JOBS |
"SELECT job_id, user_info.user_name AS owner_name FROM jobs JOIN user_info ON jobs.owner = user_info.user_id WHERE machine_id = :machine_id AND job_state != 4 ORDER BY job_id ASC" |
protected static final String |
GET_MACHINE_REPORTS |
"SELECT board_id, report_id, reported_issue, report_timestamp, user_name AS reporter_name FROM board_reports JOIN user_info ON reporter = user_id JOIN boards USING (board_id) WHERE machine_id = :machine_id ORDER BY board_id, report_id" |
protected static final String |
GET_MACHINE_WRAPS |
"WITH linked AS (SELECT b1.machine_id, b1.x AS x1, b1.y AS y1, b2.x AS x2, b2.y AS y2 FROM links JOIN boards AS b1 ON links.board_1 = b1.board_id JOIN boards AS b2 ON links.board_2 = b2.board_id) SELECT EXISTS (SELECT 1 FROM linked WHERE linked.machine_id = machines.machine_id AND ((linked.x1 = 0 AND linked.x2 = machines.width - 1) OR (linked.x2 = 0 AND linked.x1 = machines.width - 1))) AS horizontal_wrap, EXISTS (SELECT 1 FROM linked WHERE linked.machine_id = machines.machine_id AND ((linked.y1 = 0 AND linked.y2 = machines.height - 1) OR (linked.y2 = 0 AND linked.y1 = machines.height - 1))) AS vertical_wrap FROM machines WHERE machine_id = :machine_id LIMIT 1" |
protected static final String |
GET_MEMBERSHIP |
"SELECT membership_id, user_info.user_id, user_groups.group_id, user_name, group_name FROM group_memberships JOIN user_info USING (user_id) JOIN user_groups USING (group_id) WHERE membership_id = :membership_id" |
protected static final String |
GET_MEMBERSHIPS_OF_USER |
"SELECT membership_id, user_info.user_id, user_groups.group_id, user_name, group_name FROM group_memberships JOIN user_info USING (user_id) JOIN user_groups USING (group_id) WHERE group_memberships.user_id = :user_id" |
protected static final String |
GET_NAMED_MACHINE |
"SELECT machine_id, machine_name, width, height, in_service FROM machines WHERE machine_name = :machine_name AND (in_service OR :allow_out_of_service) LIMIT 1" |
protected static final String |
GET_ROOT_BMP_ADDRESS |
"SELECT bmp.address FROM bmp JOIN boards USING (bmp_id) WHERE boards.machine_id = :machine_id AND boards.x = 0 AND boards.y = 0 LIMIT 1" |
protected static final String |
GET_ROOT_COORDS |
"SELECT x, y, z, root_x, root_y FROM boards WHERE board_id = :board_id LIMIT 1" |
protected static final String |
GET_ROOT_OF_BOARD |
"SELECT root_x, root_y FROM boards WHERE board_id = :board_id LIMIT 1" |
protected static final String |
GET_SERIAL_INFO_REQS |
"SELECT op_id, board_id, board_serial.bmp_serial_id, board_num, cabinet, frame, boards.machine_id FROM blacklist_ops JOIN boards USING (board_id) JOIN bmp USING (bmp_id) LEFT JOIN board_serial USING (board_id) WHERE op = 2 AND NOT completed AND boards.bmp_id = :bmp_id" |
protected static final String |
GET_SUM_BOARDS_POWERED |
"SELECT COALESCE(sum(board_power), 0) AS total_on FROM boards WHERE allocated_job = :job_id" |
protected static final String |
GET_TAGS |
"SELECT tag FROM tags WHERE machine_id = :machine_id" |
protected static final String |
GET_TEMP_INFO_REQS |
"SELECT op_id, board_id, board_serial.bmp_serial_id, board_num, cabinet, frame, boards.machine_id FROM blacklist_ops JOIN boards USING (board_id) JOIN bmp USING (bmp_id) LEFT JOIN board_serial USING (board_id) WHERE op = 3 AND NOT completed AND boards.bmp_id = :bmp_id" |
protected static final String |
GET_USER_AUTHORITIES |
"SELECT trust_level, encrypted_password, openid_subject FROM user_info WHERE user_id = :user_id LIMIT 1" |
protected static final String |
GET_USER_DETAILS |
"SELECT user_id, user_name, encrypted_password IS NOT NULL AS has_password, trust_level, locked, disabled, last_successful_login_timestamp, last_fail_timestamp, openid_subject, is_internal FROM user_info WHERE user_id = :user_id LIMIT 1" |
protected static final String |
GET_USER_DETAILS_BY_NAME |
"SELECT user_id, user_name, encrypted_password IS NOT NULL AS has_password, trust_level, locked, disabled, last_successful_login_timestamp, last_fail_timestamp, openid_subject, is_internal FROM user_info WHERE user_name = :user_name LIMIT 1" |
protected static final String |
GET_USER_DETAILS_BY_SUBJECT |
"SELECT user_id, user_name, encrypted_password IS NOT NULL AS has_password, trust_level, locked, disabled, last_successful_login_timestamp, last_fail_timestamp, openid_subject, is_internal FROM user_info WHERE openid_subject = :openid_subject LIMIT 1" |
protected static final String |
GET_USER_ID |
"SELECT user_id FROM user_info WHERE user_name = :user_name LIMIT 1" |
protected static final String |
GET_USER_QUOTA |
"SELECT SUM(quota) AS quota_total, quotas.user_id FROM quotas JOIN user_info USING (user_id) WHERE user_info.user_name = :user_name AND user_id IS NOT NULL" |
protected static final String |
GET_USERS_OF_GROUP |
"SELECT membership_id, user_groups.group_id, user_groups.group_name, user_info.user_id, user_info.user_name FROM group_memberships JOIN user_info USING (user_id) JOIN user_groups USING (group_id) WHERE group_id = :group_id" |
protected static final String |
GROUP_SYNC_ADD_GROUPS |
"INSERT IGNORE INTO group_memberships(user_id, group_id) SELECT :user_id AS user_id, group_id FROM usergroupids" |
protected static final String |
GROUP_SYNC_DROP_TEMP_TABLE |
"DROP TEMPORARY TABLE usergroupids" |
protected static final String |
GROUP_SYNC_INSERT_TEMP_ROW |
"INSERT INTO usergroupids SELECT group_id FROM user_groups WHERE group_name = :group_name AND group_type = :group_type" |
protected static final String |
GROUP_SYNC_MAKE_TEMP_TABLE |
"CREATE TEMPORARY TABLE usergroupids(group_id INTEGER)" |
protected static final String |
GROUP_SYNC_REMOVE_GROUPS |
"DELETE FROM group_memberships WHERE user_id = :user_id AND group_id NOT IN (SELECT group_id FROM usergroupids)" |
protected static final String |
INSERT_BMP |
"INSERT INTO bmp(machine_id, address, cabinet, frame) VALUES(:machine_id, :address, :cabinet, :frame)" |
protected static final String |
INSERT_BOARD |
"INSERT INTO boards(machine_id, address, bmp_id, board_num, x, y, z, root_x, root_y, functioning) VALUES(:machine_id, :address, :bmp_id, :board_num, :x, :y, :z, :root_x, :root_y, :enabled)" |
protected static final String |
INSERT_BOARD_REPORT |
"INSERT INTO board_reports(board_id, job_id, reported_issue, reporter, report_timestamp) VALUES(:board_id, :job_id, :issue, :user_id, UNIX_TIMESTAMP())" |
protected static final String |
INSERT_JOB |
"INSERT INTO jobs(machine_id, owner, group_id, keepalive_interval, original_request, keepalive_timestamp, create_timestamp, job_state) VALUES(:machine_id, :user_id, :group_id, :keepalive_interval, :original_request, UNIX_TIMESTAMP(), UNIX_TIMESTAMP(), 1)" |
protected static final String |
INSERT_LINK |
"INSERT IGNORE INTO links(board_1, dir_1, board_2, dir_2, live) VALUES (:board_1, :dir_1, :board_2, :dir_2, :live)" |
protected static final String |
INSERT_MACHINE_SPINN_5 |
"INSERT INTO machines(machine_name, width, height, depth, board_model) VALUES(:name, :width, :height, :depth, 5)" |
protected static final String |
INSERT_REQ_BOARD |
"INSERT INTO job_request(job_id, board_id, priority) VALUES (:job_id, :board_id, :priority)" |
protected static final String |
INSERT_REQ_N_BOARDS |
"INSERT INTO job_request(job_id, num_boards, max_dead_boards, priority) VALUES (:job_id, :num_boards, :max_dead_boards, :priority)" |
protected static final String |
INSERT_REQ_SIZE |
"INSERT INTO job_request(job_id, width, height, max_dead_boards, priority) VALUES (:job_id, :width, :height, :max_dead_boards, :priority)" |
protected static final String |
INSERT_REQ_SIZE_BOARD |
"INSERT INTO job_request(job_id, board_id, width, height, max_dead_boards, priority) VALUES(:job_id, :board_id, :width, :height, :max_dead_boards,:priority)" |
protected static final String |
INSERT_TAG |
"INSERT INTO tags(machine_id, tag) VALUES(:machine_id, :tag)" |
protected static final String |
IS_BOARD_BLACKLIST_CURRENT |
"SELECT blacklist_sync_timestamp >= blacklist_set_timestamp AS current FROM boards WHERE board_id = :board_id LIMIT 1" |
protected static final String |
IS_USER_LOCKED |
"SELECT user_id, locked, disabled FROM user_info WHERE user_name = :username" |
protected static final String |
KILL_JOB_ALLOC_TASK |
"DELETE FROM job_request WHERE job_id = :job_id" |
protected static final String |
LIST_ALL_GROUPS |
"SELECT group_id, group_name, quota, group_type FROM user_groups" |
protected static final String |
LIST_ALL_GROUPS_OF_TYPE |
"SELECT group_id, group_name, quota, group_type FROM user_groups WHERE group_type = :type" |
protected static final String |
LIST_ALL_USERS |
"SELECT user_id, user_name, openid_subject FROM user_info" |
protected static final String |
LIST_ALL_USERS_OF_TYPE |
"SELECT user_id, user_name, openid_subject FROM user_info WHERE is_internal = :internal" |
protected static final String |
LIST_LIVE_JOBS |
"SELECT job_id, jobs.machine_id, create_timestamp, keepalive_interval, job_state, allocation_size, keepalive_host, user_name, machines.machine_name, original_request FROM jobs JOIN machines USING (machine_id) JOIN user_info ON jobs.owner = user_info.user_id WHERE job_state != 4" |
protected static final String |
LIST_MACHINE_NAMES |
"SELECT machine_name, in_service FROM machines WHERE in_service OR :allow_out_of_service ORDER BY machine_name ASC" |
protected static final String |
LOAD_DIR_INFO |
"SELECT z, direction, dx, dy, dz FROM movement_directions" |
protected static final String |
MARK_BOARD_BLACKLIST_CHANGED |
"UPDATE boards SET blacklist_set_timestamp = UNIX_TIMESTAMP() WHERE board_id = :board_id" |
protected static final String |
MARK_BOARD_BLACKLIST_SYNCHED |
"UPDATE boards SET blacklist_sync_timestamp = UNIX_TIMESTAMP() WHERE board_id = :board_id" |
protected static final String |
MARK_CONSOLIDATED |
"UPDATE jobs SET accounted_for = 1 WHERE job_id = :job_id" |
protected static final String |
MARK_LOGIN_FAILURE |
"UPDATE user_info SET failure_count = failure_count + 1, last_fail_timestamp = UNIX_TIMESTAMP(), locked = (failure_count + 1 >= :failure_limit) WHERE user_id = :user_id" |
protected static final String |
MARK_LOGIN_SUCCESS |
"UPDATE user_info SET last_successful_login_timestamp = UNIX_TIMESTAMP(), failure_count = 0, openid_subject = :openid_subject WHERE user_id = :user_id" |
protected static final String |
NOTE_DESTROY_REASON |
"UPDATE jobs SET death_reason = :death_reason WHERE job_id = :job_id" |
protected static final String |
READ_HISTORICAL_ALLOCS |
"SELECT\talloc_id, job_id, board_id, alloc_timestamp FROM old_board_allocations JOIN jobs USING (job_id) WHERE jobs.death_timestamp + :grace_period < UNIX_TIMESTAMP()" |
protected static final String |
READ_HISTORICAL_JOBS |
"SELECT job_id, machine_id, owner, create_timestamp, jobs.width as width, jobs.height as height, jobs.depth as depth,allocated_root, keepalive_interval, keepalive_host, death_reason, death_timestamp, original_request, allocation_timestamp, allocation_size, machine_name, user_name, group_id, group_name, job_nmpi_job.nmpi_job_id as nmpi_job_id, job_nmpi_session.session_id as nmpi_session_id FROM jobs JOIN user_groups USING (group_id) JOIN machines USING (machine_id) JOIN user_info ON jobs.owner = user_info.user_id LEFT JOIN job_nmpi_job USING (job_id) LEFT JOIN job_nmpi_session USING (job_id) WHERE death_timestamp + :grace_period < UNIX_TIMESTAMP()" |
protected static final String |
REMOVE_USER_FROM_GROUP |
"DELETE FROM group_memberships WHERE user_id = :user_id AND group_id = :group_id" |
protected static final String |
SET_BOARD_POWER_OFF |
"UPDATE boards SET board_power = 0, power_off_timestamp = UNIX_TIMESTAMP() WHERE board_id = :board_id" |
protected static final String |
SET_BOARD_POWER_ON |
"UPDATE boards SET board_power = 1, power_on_timestamp = UNIX_TIMESTAMP() WHERE board_id = :board_id" |
protected static final String |
SET_BOARD_SERIAL_IDS |
"INSERT INTO board_serial(board_id, bmp_serial_id, physical_serial_id) VALUES(:board_id, :bmp_serial_id, :physical_serial_id) ON DUPLICATE KEY UPDATE bmp_serial_id = VALUES(bmp_serial_id), physical_serial_id = VALUES(physical_serial_id)" |
protected static final String |
SET_COLLAB_QUOTA |
"UPDATE user_groups SET quota = GREATEST(0, :new_quota) WHERE group_name = :group_name AND quota IS NOT NULL" |
protected static final String |
SET_FUNCTIONING_FIELD |
"UPDATE boards SET functioning = :enabled WHERE board_id = :board_id" |
protected static final String |
SET_JOB_NMPI_JOB |
"INSERT IGNORE INTO job_nmpi_job ( job_id, nmpi_job_id, quota_units) VALUES(:job_id, :nmpi_job_id, :quota_units)" |
protected static final String |
SET_JOB_SESSION |
"INSERT IGNORE INTO job_nmpi_session ( job_id, session_id, quota_units) VALUES(:job_id, :session_id, :quota_units)" |
protected static final String |
SET_MACHINE_STATE |
"UPDATE machines SET in_service = :in_service WHERE machine_name = :machine_name" |
protected static final String |
SET_MAX_COORDS |
"UPDATE machines SET max_chip_x = :max_x, max_chip_y = :max_y WHERE machine_id = :machine_id" |
protected static final String |
SET_STATE_DESTROYED |
"UPDATE jobs SET job_state = 4, death_timestamp = UNIX_TIMESTAMP() WHERE job_id = :job_id" |
protected static final String |
SET_STATE_PENDING |
"UPDATE jobs SET job_state = :job_state WHERE job_id = :job_id" |
protected static final String |
SET_USER_DISABLED |
"UPDATE user_info SET disabled = :disabled WHERE user_id = :user_id" |
protected static final String |
SET_USER_LOCKED |
"UPDATE user_info SET locked = :locked WHERE user_id = :user_id" |
protected static final String |
SET_USER_NAME |
"UPDATE user_info SET user_name = :user_name WHERE user_id = :user_id" |
protected static final String |
SET_USER_PASS |
"UPDATE user_info SET encrypted_password = :password WHERE user_id = :user_id" |
protected static final String |
SET_USER_TRUST |
"UPDATE user_info SET trust_level = :trust WHERE user_id = :user_id" |
protected static final String |
UNLOCK_LOCKED_USERS |
"UPDATE user_info SET failure_count = 0, last_fail_timestamp = 0, locked = 0 WHERE last_fail_timestamp + :lock_interval < UNIX_TIMESTAMP() AND locked" |
protected static final String |
UPDATE_GROUP |
"UPDATE user_groups SET group_name = COALESCE(:group_name, group_name), quota = :quota WHERE group_id = :group_id " |
protected static final String |
UPDATE_KEEPALIVE |
"UPDATE jobs SET keepalive_timestamp = UNIX_TIMESTAMP(), keepalive_host = :keepalive_host WHERE job_id = :job_id AND job_state != 4" |
protected static final String |
WRITE_HISTORICAL_ALLOCS |
"INSERT IGNORE INTO board_allocations( alloc_id, job_id, board_id, allocation_timestamp) VALUES(:alloc_id, :job_id, :board_id, :allocation_timestamp)" |
protected static final String |
WRITE_HISTORICAL_JOBS |
"INSERT IGNORE INTO jobs( job_id, machine_id, owner, create_timestamp, width, height, depth, root_id, keepalive_interval, keepalive_host, death_reason, death_timestamp, original_request, allocation_timestamp, allocation_size, machine_name, owner_name, group_id, group_name, nmpi_job_id, nmpi_session_id) VALUES(:job_id, :machine_id, :owner, :create_timestamp, :width, :height, :depth, :root_id, :keepalive_interval, :keepalive_host, :death_reason, :death_timestamp, :original_request, :allocation_timestamp, :allocation_size, :machine_name, :owner_name, :group_id, :group_name, :nmpi_job_id, :nmpi_session_id)" |