<commit_before>{
    "deletes":{
        "delete_bug_job_map":{
            "sql":"DELETE FROM bug_job_map WHERE job_id = ? and bug_id = ?",
            "host_type": "master_host"
        },
        "delete_note":{
            "sql":"DELETE FROM job_note WHERE id = ?",
            "host_type": "master_host"
        },
        "cycle_job_artifact":{

            "sql":"DELETE FROM job_artifact WHERE job_id IN (REP0)",
            "host_type": "master_host"
        },
        "cycle_job_log_url":{

            "sql":"DELETE FROM job_log_url WHERE job_id IN (REP0)",
            "host_type": "master_host"
        },
        "cycle_job_note":{

            "sql":"DELETE FROM job_note WHERE job_id IN (REP0)",
            "host_type": "master_host"
        },
        "cycle_bug_job_map":{

            "sql":"DELETE FROM bug_job_map WHERE job_id IN (REP0)",
            "host_type": "master_host"
        },
        "cycle_job":{

            "sql":"DELETE FROM job WHERE id IN (REP0)",
            "host_type": "master_host"
        },
        "cycle_revision":{

            "sql":"DELETE FROM revision WHERE id IN (REP0)",
            "host_type": "master_host"
        },
        "cycle_revision_map":{

            "sql":"DELETE FROM revision_map WHERE result_set_id IN (REP0)",
            "host_type": "master_host"
        },
        "cycle_result_set":{

            "sql":"DELETE FROM result_set WHERE id IN (REP0)",
            "host_type": "master_host"
        },
        "cycle_performance_artifact":{

            "sql":"DELETE FROM performance_artifact WHERE job_id IN (REP0)",
            "host_type": "master_host"
        }
    },
    "inserts":{
        "create_job_data":{
            "sql":"INSERT INTO `job` (
                `job_guid`,
                `signature`,
                `job_coalesced_to_guid`,
                `result_set_id`,
                `build_platform_id`,
                `machine_platform_id`,
                `machine_id`,
                `device_id`,
                `option_collection_hash`,
                `job_type_id`,
                `product_id`,
                `who`,
                `reason`,
                `result`,
                `state`,
                `submit_timestamp`,
                `start_timestamp`,
                `end_timestamp`,
                `pending_eta`,
                `running_eta`)
                SELECT ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?
                FROM DUAL WHERE NOT EXISTS (
                    SELECT `job_guid`
                    FROM `job`
                    WHERE `job_guid` = ?
                    OR `job_guid` = ?
                )",

            "host_type":"master_host"
        },
        "set_performance_artifact":{
            "sql":"INSERT INTO `performance_artifact` (`job_id`, `series_signature`, `name`, `type`, `blob`)
                   VALUES (?,?,?,?,?)",

            "host_type":"master_host"
        },
        "set_series_signature":{
            "sql":"INSERT INTO `series_signature` (`signature`, `property`, `value`)
                   SELECT ?,?,?
                   FROM DUAL WHERE NOT EXISTS (
                        SELECT `signature`, `property`, `value`
                        FROM `series_signature`
                        WHERE `signature` = ? AND `property` = ? AND `value` = ?
                   )",
            "host_type":"master_host"
        },
        "set_performance_series":{
            "sql":"INSERT INTO `performance_series` (`interval_seconds`, `series_signature`, `type`, `last_updated`, `blob`)
                   SELECT ?,?,?,?,?
                   FROM DUAL WHERE NOT EXISTS (
                        SELECT `interval_seconds`, `series_signature`
                        FROM `performance_series`
                        WHERE `interval_seconds` = ? AND `series_signature` = ?
                   )",
            "host_type":"master_host"
        },
        "set_job_eta":{
            "sql":"INSERT INTO `job_eta` (
                    `signature`,
                    `state`,
                    `avg_sec`,
                    `median_sec`,
                    `min_sec`,
                    `max_sec`,
                    `std`,
                    `sample_count`,
                    `submit_timestamp`)
                   VALUES (?,?,?,?,?,?,?,?,?)",

            "host_type":"master_host"
        },
        "set_result_set":{

            "sql":"INSERT INTO `result_set` (`author`, `revision_hash`,`push_timestamp`, `active_status`)
                SELECT ?,?,?,?
                FROM DUAL
                WHERE NOT EXISTS (
                    SELECT `revision_hash`, `push_timestamp`
                    FROM `result_set`
                    WHERE `revision_hash` = ?
                )",

            "host_type":"master_host"
        },
        "set_revision":{

            "sql":"INSERT INTO `revision` (
                    `revision`,
                    `author`,
                    `comments`,
                    `files`,
                    `commit_timestamp`,
                    `repository_id`
                    )
                SELECT ?,?,?,?,?,?
                FROM DUAL
                WHERE NOT EXISTS (
                    SELECT `revision`
                    FROM `revision`
                    WHERE `revision` = ? AND `repository_id` = ?
                )",

            "host_type":"master_host"
        },
        "set_revision_map":{

            "sql":"INSERT INTO `revision_map` (
                `revision_id`,
                `result_set_id`
                )
                SELECT ?,?
                FROM DUAL
                WHERE NOT EXISTS (
                    SELECT `revision_id`, `result_set_id`
                    FROM `revision_map`
                    WHERE `revision_id` = ? AND `result_set_id` = ?
                )",

            "host_type":"master_host"
        },
        "set_job_log_url":{

            "sql":"INSERT INTO `job_log_url` (
                `job_id`,
                `name`,
                `url`,
                `parse_timestamp`
                )
            VALUES (?,?,?,?)",

            "host_type":"master_host"
        },
        "set_job_artifact":{

            "sql":"INSERT INTO `job_artifact` (
                `job_id`,
                `name`,
                `type`,
                `blob`
                )
                SELECT ?,?,?,?
                FROM DUAL
                WHERE NOT EXISTS (
                    SELECT `job_id`
                    FROM `job_artifact`
                    WHERE `job_id` = ? and `name` = ?
                )",

            "host_type":"master_host"
        },
        "insert_note":{

            "sql":"INSERT INTO `job_note` (
                `job_id`,
                `failure_classification_id`,
                `who`,
                `note`,
                `note_timestamp`)
            VALUES (?,?,?,?,?)",

            "host_type":"master_host"
        },
        "insert_bug_job_map":{
            "sql":"INSERT INTO `bug_job_map` (
                `job_id`,
                `bug_id`,
                `type`,
                `submit_timestamp`,
                `who`,
                `active_status`)
            VALUES (?,?,?,?,?,'active')",
            "host_type":"master_host"
        }


    },

    "updates": {
        "update_performance_series":{
            "sql":"UPDATE `performance_series`
                   SET `last_updated` = ?, `blob` = ?
                   WHERE `interval_seconds` = ? AND `series_signature` = ?",
            "host_type":"master_host"
        },
        "set_state":{

            "sql":"UPDATE `job`
                SET    `state` = ?
                WHERE  `id` = ?
                AND `state` <> 'completed'",

                "host_type":"master_host"
        },
        "cancel_all":{

            "sql":"UPDATE `job`
                   SET    `state` = 'completed', `result` = 'usercancel'
                   WHERE  `result_set_id` = ?
                   AND `state` <> 'completed'",

                "host_type":"master_host"
        },
        "cancel_job":{

            "sql":"UPDATE `job`
                   SET    `state` = 'completed', `result` = 'usercancel'
                   WHERE  `job_guid` = ?
                   AND `state` <> 'completed'",

                "host_type":"master_host"
        },
        "update_job_data":{
            "sql":"
                UPDATE `job`
                SET
                `job_guid` = ?,
                `job_coalesced_to_guid` = ?,
                `result_set_id` = ?,
                `machine_id` = ?,
                `option_collection_hash` = ?,
                `job_type_id` = ?,
                `product_id` = ?,
                `who` = ?,
                `reason` = ?,
                `result` = ?,
                `state` = ?,
                `start_timestamp` = ?,
                `end_timestamp` = ?
                WHERE
                    `state` != 'completed'
                AND ? <> 'pending'
                AND `id` = ?",

            "host_type":"master_host"
        },
        "update_coalesced_guids":{
            "sql":"UPDATE `job`
                   SET `job_coalesced_to_guid` = ?
                   WHERE `job_guid` = ?",

            "host_type":"master_host"
        },
        "update_last_job_classification":{
            "sql":"update job j
                    set failure_classification_id = (
	                    select
	                        IF(failure_classification_id IS NOT NULL, failure_classification_id, 1)
	                    from
	                        job_note jn
	                    where
	                        jn.job_id = j.id
	                    order by
	                        note_timestamp DESC
	                    limit 0,1
	                )
	                where j.id = ?
            ",

            "host_type":"master_host"
        },
        "update_job_log_url":{

            "sql":"UPDATE `job_log_url`
                SET    `parse_status` = ?, `parse_timestamp` = ?
                WHERE  `id` = ?
                AND `active_status` = 'active'",
            "host_type":"master_host"
        }
    },
    "selects":{
        "get_signatures_from_properties":{

            "sql": "SELECT `signature` FROM `series_signature`
                    WHERE (REP0)
                    GROUP BY `signature`
                    HAVING (REP1)",

             "host_type":"read_host"

        },
        "get_all_properties_of_signatures":{

            "sql": "SELECT `property`, `value`, `signature` FROM `series_signature`
                    WHERE `signature` IN (REP0)",

             "host_type":"read_host"

        },
        "get_flat_perf_series_properties":{

            "sql":"SELECT s.`signature`,
                    GROUP_CONCAT(
                        CONCAT_WS('=', s.`property`, s.`value`) SEPARATOR ',')
                     AS 'properties'
                   FROM series_signature AS s
                   LEFT JOIN `performance_series` AS p
                    ON s.`signature` = p.`series_signature`
                   WHERE p.`last_updated` >= ? AND p.`interval_seconds` = ?
                   GROUP BY s.`signature`",

            "host_type":"read_host"
        },
        "get_performance_series_from_signatures":{

            "sql": "SELECT `series_signature`, `blob` FROM `performance_series`
                    WHERE `series_signature` IN (REP0) AND `interval_seconds` = ?",

             "host_type":"read_host"

        },
        "get_performance_artifact":{

            "sql":"SELECT `id`, `job_id`, `series_signature`, `name`, `type`, `blob`
                   FROM `performance_artifact`
                   WHERE `job_id` IN (REP0) AND `active_status` = 'active'",

            "host_type":"read_host"

        },
        "get_max_performance_artifact_id":{
            "sql":"SELECT max(`id`) as max_id from `performance_artifact`",
            "host_type": "read_host"
        },
        "get_performance_artifact_list": {

            "sql":"SELECT `id`, `job_id`, `series_signature`, `name`, `type`, `blob`
                   FROM `performance_artifact`
                   WHERE `active_status` = 'active'
                   REP0
                   ORDER BY id DESC, name",

            "host_type":"read_host"

        },
        "get_performance_series": {

            "sql":"SELECT `interval_seconds`, `series_signature`, `type`, `last_updated`, `blob`
                   FROM `performance_series`
                   WHERE `interval_seconds` = ? AND `series_signature` = ?",

            "host_type":"master_host"

        },
        "get_all_series_signatures": {

            "sql":"SELECT `signature`,
                    GROUP_CONCAT(
                        CONCAT_WS('=', `property`, `value`) SEPARATOR ', '
                    ) AS 'properties'
                   FROM `series_signature`
                   GROUP BY `signature`",

            "host_type":"read_host"

        },
        "get_max_job_submit_timestamp":{

            "sql": "SELECT MAX(`submit_timestamp`) AS 'submit_timestamp' FROM `job`",

            "host_type":"master_host"

        },
        "get_eta_groups":{

            "sql":"SELECT signature,
                          CAST(
                            ROUND( AVG(start_timestamp - submit_timestamp) )
                            AS UNSIGNED) AS 'pending_avg_sec',

                          MIN(start_timestamp - submit_timestamp) AS 'pending_min_sec',
                          MAX(start_timestamp - submit_timestamp) AS 'pending_max_sec',

                          CAST(
                            ROUND( STD(start_timestamp - submit_timestamp))
                            AS UNSIGNED) AS 'pending_std',

                          CAST(
                            ROUND( AVG(end_timestamp - start_timestamp) )
                            AS UNSIGNED) AS 'running_avg_sec',

                          MIN(end_timestamp - start_timestamp) AS 'running_min_sec',
                          MAX(end_timestamp - start_timestamp) AS 'running_max_sec',

                          CAST(
                            ROUND( STD(end_timestamp - start_timestamp) )
                            AS SIGNED) AS 'running_std',

                          GROUP_CONCAT(start_timestamp - submit_timestamp SEPARATOR ',')
                            AS 'pending_samples',

                          GROUP_CONCAT(end_timestamp - start_timestamp SEPARATOR ',')
                            AS 'running_samples'
                   FROM job
                   WHERE submit_timestamp >= ? AND
                         start_timestamp >= submit_timestamp AND
                         end_timestamp >= start_timestamp AND
                         start_timestamp > 0 AND end_timestamp > 0
                   GROUP BY signature",

            "host_type":"read_host"

        },
        "get_signature_list_from_job_ids":{

            "sql":"SELECT j.id,
                          j.result_set_id,
                          j.job_guid,
                          j.signature,
                          r.push_timestamp
                   FROM job AS j
                   JOIN result_set AS r
                    ON j.result_set_id = r.id
                   WHERE j.id IN (REP0)",
            "host_type":"master_host"

        },
        "get_last_eta_by_signatures":{
            "sql":"SELECT signature,
                          state,
                          avg_sec,
                          median_sec,
                          min_sec,
                          max_sec,
                          std,
                          sample_count,
                          MAX(submit_timestamp)
                    FROM job_eta
                    WHERE signature IN (REP0)
                    GROUP BY signature, state
                    ORDER BY submit_timestamp DESC",
            "host_type":"read_host"
        },
        "get_revision_ids_to_cycle":{
            "sql":"SELECT revision_id FROM revision_map WHERE result_set_id IN (REP0)",
            "host_type":"master_host"
        },

        "get_jobs_to_cycle":{
            "sql":"SELECT id, job_guid FROM job WHERE result_set_id IN (REP0)",
            "host_type":"master_host"
        },

        "get_result_sets_to_cycle":{
            "sql":"SELECT id FROM result_set WHERE push_timestamp < ?",
            "host_type":"master_host"
        },

        "get_incomplete_job_guids": {
            "sql": "SELECT j.job_guid, j.result_set_id, rs.push_timestamp as result_set_push_timestamp
                    FROM job as j
                    LEFT JOIN result_set as rs
                      ON j.result_set_id = rs.id
                    WHERE j.state <> 'completed'
                    AND j.result_set_id = ?",
            "host_type":"read_host"
        },

        "get_job":{
            "sql":"SELECT
                    j.id,
                    j.`job_guid`,
                    j.`signature`,
                    j.`job_coalesced_to_guid`,
                    j.`build_platform_id`,
                    j.`option_collection_hash`,
                    mp.`platform` as platform,
                    mp.`os_name` as machine_platform_os,
                    mp.`architecture` as machine_platform_architecture,
                    bp.`platform` as build_platform,
                    bp.`os_name` as build_os,
                    bp.`architecture` as build_architecture,
                    m.`name` as machine_name,
                    m.`id` as machine_id,
                    jt.`name` as job_type_name,
                    jt.`symbol` as job_type_symbol,
                    jt.`description` as job_type_description,
					jt.id as job_type_id,
					jg.`name` as job_group_name,
                    jg.`symbol` as job_group_symbol,
                    jg.`description` as job_group_description,
                    j.`who`,
                    j.failure_classification_id,
                    j.`result_set_id`,
                    j.`result`,
                    j.`state`,
                    j.`reason`,
                    j.`start_timestamp`,
                    j.`end_timestamp`,
                    j.`submit_timestamp`,
                    j.`pending_eta`,
                    j.`running_eta`,
                    rds.`name` as ref_data_name
                  FROM `job` as j
                  LEFT JOIN `REP0`.`machine` as m
                    ON j.`machine_id` = m.id
                  LEFT JOIN `REP0`.`machine_platform` as mp
                    ON j.`machine_platform_id` = mp.id
                  LEFT JOIN `REP0`.`build_platform` as bp
                    ON j.`build_platform_id` = bp.id
                  LEFT JOIN `REP0`.`job_type` as jt
                    ON j.`job_type_id` = jt.id
				  LEFT JOIN `REP0`.`job_group` as jg
                    ON jt.`job_group_id` = jg.id
                  LEFT JOIN `REP0`.reference_data_signatures rds
                    ON j.signature = rds.signature
                  WHERE j.id = ?",

            "host_type":"read_host"
        },
        "get_job_list":{
            "sql":"SELECT
                    j.id,
                    j.`job_guid`,
                    j.`job_coalesced_to_guid`,
                    j.`build_platform_id`,
                    j.`option_collection_hash`,
                    j.failure_classification_id,
                    m.`name` as machine_name,
                    d.`name` as device_name,
                    mp.`platform` as platform,
                    mp.`os_name` as machine_platform_os,
                    mp.`architecture` as machine_platform_architecture,
                    bp.`platform` as build_platform,
                    bp.`os_name` as build_os,
                    bp.`architecture` as build_architecture,
                    jt.`name` as job_type_name,
                    jt.`symbol` as job_type_symbol,
                    jt.`description` as job_type_description,
					jg.`name` as job_group_name,
                    jg.`symbol` as job_group_symbol,
                    jg.`description` as job_group_description,
                    j.`who`,
                    j.`result_set_id`,
                    j.`result`,
                    j.`state`,
                    j.`reason`,
                    j.`start_timestamp`,
                    j.`end_timestamp`,
                    j.`submit_timestamp`,
                    j.`pending_eta`,
                    j.`running_eta`,
                    j.`last_modified`,
                    rds.`name` as ref_data_name
                  FROM `job` as j
                  LEFT JOIN `REP0`.`machine` as m
                    ON j.`machine_id` = m.id
                  LEFT JOIN `REP0`.`machine_platform` as mp
                    ON j.`machine_platform_id` = mp.id
                  LEFT JOIN `REP0`.`build_platform` as bp
                    ON j.`build_platform_id` = bp.id
                  LEFT JOIN `REP0`.`job_type` as jt
                    ON j.`job_type_id` = jt.id
				  LEFT JOIN `REP0`.`job_group` as jg
                    ON jt.`job_group_id` = jg.id
                  LEFT JOIN `REP0`.`device` as d
                    ON j.device_id = d.id
                  LEFT JOIN `REP0`.reference_data_signatures rds
                    ON j.signature = rds.signature
                  WHERE 1
                  REP1",

            "host_type":"read_host"
        },
        "get_log_references":{
            "sql":"SELECT `name`, `url`
                   FROM `job_log_url`
                   WHERE job_id = ?",

            "host_type":"read_host"
        },
        "get_job_log_url_detail":{
            "sql": "SELECT `id`, `job_id`, `name`, `url`, `parse_status`, `parse_timestamp`
                    FROM job_log_url
                    WHERE id = ? and active_status = 'active'",
            "host_type":"read_host"
        },
        "get_job_log_url_list":{
            "sql": "SELECT `id`, `job_id`, `name`, `url`, `parse_status`, `parse_timestamp`
                    FROM job_log_url
                    WHERE job_id in (REP0)
                    and active_status='active'",
            "host_type": "master_host"
        },
        "get_job_artifact":{
            "sql":"SELECT
                      id,
                      job_id,<commit_msg>Bug 1131133 - group jobs by id<commit_after>{
    "deletes":{
        "delete_bug_job_map":{
            "sql":"DELETE FROM bug_job_map WHERE job_id = ? and bug_id = ?",
            "host_type": "master_host"
        },
        "delete_note":{
            "sql":"DELETE FROM job_note WHERE id = ?",
            "host_type": "master_host"
        },
        "cycle_job_artifact":{

            "sql":"DELETE FROM job_artifact WHERE job_id IN (REP0)",
            "host_type": "master_host"
        },
        "cycle_job_log_url":{

            "sql":"DELETE FROM job_log_url WHERE job_id IN (REP0)",
            "host_type": "master_host"
        },
        "cycle_job_note":{

            "sql":"DELETE FROM job_note WHERE job_id IN (REP0)",
            "host_type": "master_host"
        },
        "cycle_bug_job_map":{

            "sql":"DELETE FROM bug_job_map WHERE job_id IN (REP0)",
            "host_type": "master_host"
        },
        "cycle_job":{

            "sql":"DELETE FROM job WHERE id IN (REP0)",
            "host_type": "master_host"
        },
        "cycle_revision":{

            "sql":"DELETE FROM revision WHERE id IN (REP0)",
            "host_type": "master_host"
        },
        "cycle_revision_map":{

            "sql":"DELETE FROM revision_map WHERE result_set_id IN (REP0)",
            "host_type": "master_host"
        },
        "cycle_result_set":{

            "sql":"DELETE FROM result_set WHERE id IN (REP0)",
            "host_type": "master_host"
        },
        "cycle_performance_artifact":{

            "sql":"DELETE FROM performance_artifact WHERE job_id IN (REP0)",
            "host_type": "master_host"
        }
    },
    "inserts":{
        "create_job_data":{
            "sql":"INSERT INTO `job` (
                `job_guid`,
                `signature`,
                `job_coalesced_to_guid`,
                `result_set_id`,
                `build_platform_id`,
                `machine_platform_id`,
                `machine_id`,
                `device_id`,
                `option_collection_hash`,
                `job_type_id`,
                `product_id`,
                `who`,
                `reason`,
                `result`,
                `state`,
                `submit_timestamp`,
                `start_timestamp`,
                `end_timestamp`,
                `pending_eta`,
                `running_eta`)
                SELECT ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?
                FROM DUAL WHERE NOT EXISTS (
                    SELECT `job_guid`
                    FROM `job`
                    WHERE `job_guid` = ?
                    OR `job_guid` = ?
                )",

            "host_type":"master_host"
        },
        "set_performance_artifact":{
            "sql":"INSERT INTO `performance_artifact` (`job_id`, `series_signature`, `name`, `type`, `blob`)
                   VALUES (?,?,?,?,?)",

            "host_type":"master_host"
        },
        "set_series_signature":{
            "sql":"INSERT INTO `series_signature` (`signature`, `property`, `value`)
                   SELECT ?,?,?
                   FROM DUAL WHERE NOT EXISTS (
                        SELECT `signature`, `property`, `value`
                        FROM `series_signature`
                        WHERE `signature` = ? AND `property` = ? AND `value` = ?
                   )",
            "host_type":"master_host"
        },
        "set_performance_series":{
            "sql":"INSERT INTO `performance_series` (`interval_seconds`, `series_signature`, `type`, `last_updated`, `blob`)
                   SELECT ?,?,?,?,?
                   FROM DUAL WHERE NOT EXISTS (
                        SELECT `interval_seconds`, `series_signature`
                        FROM `performance_series`
                        WHERE `interval_seconds` = ? AND `series_signature` = ?
                   )",
            "host_type":"master_host"
        },
        "set_job_eta":{
            "sql":"INSERT INTO `job_eta` (
                    `signature`,
                    `state`,
                    `avg_sec`,
                    `median_sec`,
                    `min_sec`,
                    `max_sec`,
                    `std`,
                    `sample_count`,
                    `submit_timestamp`)
                   VALUES (?,?,?,?,?,?,?,?,?)",

            "host_type":"master_host"
        },
        "set_result_set":{

            "sql":"INSERT INTO `result_set` (`author`, `revision_hash`,`push_timestamp`, `active_status`)
                SELECT ?,?,?,?
                FROM DUAL
                WHERE NOT EXISTS (
                    SELECT `revision_hash`, `push_timestamp`
                    FROM `result_set`
                    WHERE `revision_hash` = ?
                )",

            "host_type":"master_host"
        },
        "set_revision":{

            "sql":"INSERT INTO `revision` (
                    `revision`,
                    `author`,
                    `comments`,
                    `files`,
                    `commit_timestamp`,
                    `repository_id`
                    )
                SELECT ?,?,?,?,?,?
                FROM DUAL
                WHERE NOT EXISTS (
                    SELECT `revision`
                    FROM `revision`
                    WHERE `revision` = ? AND `repository_id` = ?
                )",

            "host_type":"master_host"
        },
        "set_revision_map":{

            "sql":"INSERT INTO `revision_map` (
                `revision_id`,
                `result_set_id`
                )
                SELECT ?,?
                FROM DUAL
                WHERE NOT EXISTS (
                    SELECT `revision_id`, `result_set_id`
                    FROM `revision_map`
                    WHERE `revision_id` = ? AND `result_set_id` = ?
                )",

            "host_type":"master_host"
        },
        "set_job_log_url":{

            "sql":"INSERT INTO `job_log_url` (
                `job_id`,
                `name`,
                `url`,
                `parse_timestamp`
                )
            VALUES (?,?,?,?)",

            "host_type":"master_host"
        },
        "set_job_artifact":{

            "sql":"INSERT INTO `job_artifact` (
                `job_id`,
                `name`,
                `type`,
                `blob`
                )
                SELECT ?,?,?,?
                FROM DUAL
                WHERE NOT EXISTS (
                    SELECT `job_id`
                    FROM `job_artifact`
                    WHERE `job_id` = ? and `name` = ?
                )",

            "host_type":"master_host"
        },
        "insert_note":{

            "sql":"INSERT INTO `job_note` (
                `job_id`,
                `failure_classification_id`,
                `who`,
                `note`,
                `note_timestamp`)
            VALUES (?,?,?,?,?)",

            "host_type":"master_host"
        },
        "insert_bug_job_map":{
            "sql":"INSERT INTO `bug_job_map` (
                `job_id`,
                `bug_id`,
                `type`,
                `submit_timestamp`,
                `who`,
                `active_status`)
            VALUES (?,?,?,?,?,'active')",
            "host_type":"master_host"
        }


    },

    "updates": {
        "update_performance_series":{
            "sql":"UPDATE `performance_series`
                   SET `last_updated` = ?, `blob` = ?
                   WHERE `interval_seconds` = ? AND `series_signature` = ?",
            "host_type":"master_host"
        },
        "set_state":{

            "sql":"UPDATE `job`
                SET    `state` = ?
                WHERE  `id` = ?
                AND `state` <> 'completed'",

                "host_type":"master_host"
        },
        "cancel_all":{

            "sql":"UPDATE `job`
                   SET    `state` = 'completed', `result` = 'usercancel'
                   WHERE  `result_set_id` = ?
                   AND `state` <> 'completed'",

                "host_type":"master_host"
        },
        "cancel_job":{

            "sql":"UPDATE `job`
                   SET    `state` = 'completed', `result` = 'usercancel'
                   WHERE  `job_guid` = ?
                   AND `state` <> 'completed'",

                "host_type":"master_host"
        },
        "update_job_data":{
            "sql":"
                UPDATE `job`
                SET
                `job_guid` = ?,
                `job_coalesced_to_guid` = ?,
                `result_set_id` = ?,
                `machine_id` = ?,
                `option_collection_hash` = ?,
                `job_type_id` = ?,
                `product_id` = ?,
                `who` = ?,
                `reason` = ?,
                `result` = ?,
                `state` = ?,
                `start_timestamp` = ?,
                `end_timestamp` = ?
                WHERE
                    `state` != 'completed'
                AND ? <> 'pending'
                AND `id` = ?",

            "host_type":"master_host"
        },
        "update_coalesced_guids":{
            "sql":"UPDATE `job`
                   SET `job_coalesced_to_guid` = ?
                   WHERE `job_guid` = ?",

            "host_type":"master_host"
        },
        "update_last_job_classification":{
            "sql":"update job j
                    set failure_classification_id = (
	                    select
	                        IF(failure_classification_id IS NOT NULL, failure_classification_id, 1)
	                    from
	                        job_note jn
	                    where
	                        jn.job_id = j.id
	                    order by
	                        note_timestamp DESC
	                    limit 0,1
	                )
	                where j.id = ?
            ",

            "host_type":"master_host"
        },
        "update_job_log_url":{

            "sql":"UPDATE `job_log_url`
                SET    `parse_status` = ?, `parse_timestamp` = ?
                WHERE  `id` = ?
                AND `active_status` = 'active'",
            "host_type":"master_host"
        }
    },
    "selects":{
        "get_signatures_from_properties":{

            "sql": "SELECT `signature` FROM `series_signature`
                    WHERE (REP0)
                    GROUP BY `signature`
                    HAVING (REP1)",

             "host_type":"read_host"

        },
        "get_all_properties_of_signatures":{

            "sql": "SELECT `property`, `value`, `signature` FROM `series_signature`
                    WHERE `signature` IN (REP0)",

             "host_type":"read_host"

        },
        "get_flat_perf_series_properties":{

            "sql":"SELECT s.`signature`,
                    GROUP_CONCAT(
                        CONCAT_WS('=', s.`property`, s.`value`) SEPARATOR ',')
                     AS 'properties'
                   FROM series_signature AS s
                   LEFT JOIN `performance_series` AS p
                    ON s.`signature` = p.`series_signature`
                   WHERE p.`last_updated` >= ? AND p.`interval_seconds` = ?
                   GROUP BY s.`signature`",

            "host_type":"read_host"
        },
        "get_performance_series_from_signatures":{

            "sql": "SELECT `series_signature`, `blob` FROM `performance_series`
                    WHERE `series_signature` IN (REP0) AND `interval_seconds` = ?",

             "host_type":"read_host"

        },
        "get_performance_artifact":{

            "sql":"SELECT `id`, `job_id`, `series_signature`, `name`, `type`, `blob`
                   FROM `performance_artifact`
                   WHERE `job_id` IN (REP0) AND `active_status` = 'active'",

            "host_type":"read_host"

        },
        "get_max_performance_artifact_id":{
            "sql":"SELECT max(`id`) as max_id from `performance_artifact`",
            "host_type": "read_host"
        },
        "get_performance_artifact_list": {

            "sql":"SELECT `id`, `job_id`, `series_signature`, `name`, `type`, `blob`
                   FROM `performance_artifact`
                   WHERE `active_status` = 'active'
                   REP0
                   ORDER BY id DESC, name",

            "host_type":"read_host"

        },
        "get_performance_series": {

            "sql":"SELECT `interval_seconds`, `series_signature`, `type`, `last_updated`, `blob`
                   FROM `performance_series`
                   WHERE `interval_seconds` = ? AND `series_signature` = ?",

            "host_type":"master_host"

        },
        "get_all_series_signatures": {

            "sql":"SELECT `signature`,
                    GROUP_CONCAT(
                        CONCAT_WS('=', `property`, `value`) SEPARATOR ', '
                    ) AS 'properties'
                   FROM `series_signature`
                   GROUP BY `signature`",

            "host_type":"read_host"

        },
        "get_max_job_submit_timestamp":{

            "sql": "SELECT MAX(`submit_timestamp`) AS 'submit_timestamp' FROM `job`",

            "host_type":"master_host"

        },
        "get_eta_groups":{

            "sql":"SELECT signature,
                          CAST(
                            ROUND( AVG(start_timestamp - submit_timestamp) )
                            AS UNSIGNED) AS 'pending_avg_sec',

                          MIN(start_timestamp - submit_timestamp) AS 'pending_min_sec',
                          MAX(start_timestamp - submit_timestamp) AS 'pending_max_sec',

                          CAST(
                            ROUND( STD(start_timestamp - submit_timestamp))
                            AS UNSIGNED) AS 'pending_std',

                          CAST(
                            ROUND( AVG(end_timestamp - start_timestamp) )
                            AS UNSIGNED) AS 'running_avg_sec',

                          MIN(end_timestamp - start_timestamp) AS 'running_min_sec',
                          MAX(end_timestamp - start_timestamp) AS 'running_max_sec',

                          CAST(
                            ROUND( STD(end_timestamp - start_timestamp) )
                            AS SIGNED) AS 'running_std',

                          GROUP_CONCAT(start_timestamp - submit_timestamp SEPARATOR ',')
                            AS 'pending_samples',

                          GROUP_CONCAT(end_timestamp - start_timestamp SEPARATOR ',')
                            AS 'running_samples'
                   FROM job
                   WHERE submit_timestamp >= ? AND
                         start_timestamp >= submit_timestamp AND
                         end_timestamp >= start_timestamp AND
                         start_timestamp > 0 AND end_timestamp > 0
                   GROUP BY signature",

            "host_type":"read_host"

        },
        "get_signature_list_from_job_ids":{

            "sql":"SELECT j.id,
                          j.result_set_id,
                          j.job_guid,
                          j.signature,
                          r.push_timestamp
                   FROM job AS j
                   JOIN result_set AS r
                    ON j.result_set_id = r.id
                   WHERE j.id IN (REP0)",
            "host_type":"master_host"

        },
        "get_last_eta_by_signatures":{
            "sql":"SELECT signature,
                          state,
                          avg_sec,
                          median_sec,
                          min_sec,
                          max_sec,
                          std,
                          sample_count,
                          MAX(submit_timestamp)
                    FROM job_eta
                    WHERE signature IN (REP0)
                    GROUP BY signature, state
                    ORDER BY submit_timestamp DESC",
            "host_type":"read_host"
        },
        "get_revision_ids_to_cycle":{
            "sql":"SELECT revision_id FROM revision_map WHERE result_set_id IN (REP0)",
            "host_type":"master_host"
        },

        "get_jobs_to_cycle":{
            "sql":"SELECT id, job_guid FROM job WHERE result_set_id IN (REP0)",
            "host_type":"master_host"
        },

        "get_result_sets_to_cycle":{
            "sql":"SELECT id FROM result_set WHERE push_timestamp < ?",
            "host_type":"master_host"
        },

        "get_incomplete_job_guids": {
            "sql": "SELECT j.job_guid, j.result_set_id, rs.push_timestamp as result_set_push_timestamp
                    FROM job as j
                    LEFT JOIN result_set as rs
                      ON j.result_set_id = rs.id
                    WHERE j.state <> 'completed'
                    AND j.result_set_id = ?",
            "host_type":"read_host"
        },

        "get_job":{
            "sql":"SELECT
                    j.id,
                    j.`job_guid`,
                    j.`signature`,
                    j.`job_coalesced_to_guid`,
                    j.`build_platform_id`,
                    j.`option_collection_hash`,
                    mp.`platform` as platform,
                    mp.`os_name` as machine_platform_os,
                    mp.`architecture` as machine_platform_architecture,
                    bp.`platform` as build_platform,
                    bp.`os_name` as build_os,
                    bp.`architecture` as build_architecture,
                    m.`name` as machine_name,
                    m.`id` as machine_id,
                    jt.`name` as job_type_name,
                    jt.`symbol` as job_type_symbol,
                    jt.`description` as job_type_description,
					jt.id as job_type_id,
					jg.`name` as job_group_name,
                    jg.`symbol` as job_group_symbol,
                    jg.`description` as job_group_description,
                    j.`who`,
                    j.failure_classification_id,
                    j.`result_set_id`,
                    j.`result`,
                    j.`state`,
                    j.`reason`,
                    j.`start_timestamp`,
                    j.`end_timestamp`,
                    j.`submit_timestamp`,
                    j.`pending_eta`,
                    j.`running_eta`,
                    rds.`name` as ref_data_name
                  FROM `job` as j
                  LEFT JOIN `REP0`.`machine` as m
                    ON j.`machine_id` = m.id
                  LEFT JOIN `REP0`.`machine_platform` as mp
                    ON j.`machine_platform_id` = mp.id
                  LEFT JOIN `REP0`.`build_platform` as bp
                    ON j.`build_platform_id` = bp.id
                  LEFT JOIN `REP0`.`job_type` as jt
                    ON j.`job_type_id` = jt.id
				  LEFT JOIN `REP0`.`job_group` as jg
                    ON jt.`job_group_id` = jg.id
                  LEFT JOIN `REP0`.reference_data_signatures rds
                    ON j.signature = rds.signature
                  WHERE j.id = ?
                  GROUP BY j.id",

            "host_type":"read_host"
        },
        "get_job_list":{
            "sql":"SELECT
                    j.id,
                    j.`job_guid`,
                    j.`job_coalesced_to_guid`,
                    j.`build_platform_id`,
                    j.`option_collection_hash`,
                    j.failure_classification_id,
                    m.`name` as machine_name,
                    d.`name` as device_name,
                    mp.`platform` as platform,
                    mp.`os_name` as machine_platform_os,
                    mp.`architecture` as machine_platform_architecture,
                    bp.`platform` as build_platform,
                    bp.`os_name` as build_os,
                    bp.`architecture` as build_architecture,
                    jt.`name` as job_type_name,
                    jt.`symbol` as job_type_symbol,
                    jt.`description` as job_type_description,
					jg.`name` as job_group_name,
                    jg.`symbol` as job_group_symbol,
                    jg.`description` as job_group_description,
                    j.`who`,
                    j.`result_set_id`,
                    j.`result`,
                    j.`state`,
                    j.`reason`,
                    j.`start_timestamp`,
                    j.`end_timestamp`,
                    j.`submit_timestamp`,
                    j.`pending_eta`,
                    j.`running_eta`,
                    j.`last_modified`,
                    rds.`name` as ref_data_name
                  FROM `job` as j
                  LEFT JOIN `REP0`.`machine` as m
                    ON j.`machine_id` = m.id
                  LEFT JOIN `REP0`.`machine_platform` as mp
                    ON j.`machine_platform_id` = mp.id
                  LEFT JOIN `REP0`.`build_platform` as bp
                    ON j.`build_platform_id` = bp.id
                  LEFT JOIN `REP0`.`job_type` as jt
                    ON j.`job_type_id` = jt.id
				  LEFT JOIN `REP0`.`job_group` as jg
                    ON jt.`job_group_id` = jg.id
                  LEFT JOIN `REP0`.`device` as d
                    ON j.device_id = d.id
                  LEFT JOIN `REP0`.reference_data_signatures rds
                    ON j.signature = rds.signature
                  WHERE 1
                  REP1
                  GROUP BY j.id
                  ",

            "host_type":"read_host"
        },
        "get_log_references":{
            "sql":"SELECT `name`, `url`
                   FROM `job_log_url`
                   WHERE job_id = ?",

            "host_type":"read_host"
        },
        "get_job_log_url_detail":{
            "sql": "SELECT `id`, `job_id`, `name`, `url`, `parse_status`, `parse_timestamp`
                    FROM job_log_url
                    WHERE id = ? and active_status = 'active'",
            "host_type":"read_host"
        },
        "get_job_log_url_list":{
            "sql": "SELECT `id`, `job_id`, `name`, `url`, `parse_status`, `parse_timestamp`
                    FROM job_log_url
                    WHERE job_id in (REP0)
                    and active_status='active'",
            "host_type": "master_host"
        },
        "get_job_artifact":{
            "sql":"SELECT
                      id,
                      job_id,
{'fraction_non_alphanumeric': 0.1094853006880529, 'fraction_numerical': 0.0019435260477169154, 'mean_word_length': 1.1434043571941586, 'pattern_counts': {'":': 330, '<': 15, '<?xml version=': 0, '>': 25, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6250746', 'n_tokens_mistral': 12888, 'n_tokens_neox': 11552, 'n_words': 2244}