blob: 274f4b9c7cf63b9f0c7a622edd8ce8eb95e3dc2c [file] [log] [blame]
#!/bin/bash
# Copyright 2020 The Chromium OS Authors. All rights reserved.
# Use of this source code is governed by a BSD-style license that can be
# found in the LICENSE file.
# A helper tool to query for relative rootfs size differences detected during
# CQ runs.
CL="$1"
[[ -z "$CL" ]] && {
echo "You must supply a CL."
exit 1
}
# Make sure we have gcert access so we don't fail without getting any work done.
gcertstatus >&/dev/null || gcert
f1-sql \
-csv_output=1 \
<< SQLtoHERE
-- Note that some data is missing from the chromeos_ci_eng.buildbucket_builds
-- table. https://crbug.com/1038752 is tracking that issue.
-- select * from chromeos_ci_eng.buildbucket_builds where id = 8892492747888114240;
WITH bb_ids AS (
SELECT
bb.id as ids
FROM
chromeos_ci_eng.buildbucket_builds AS bb
CROSS JOIN
UNNEST(input.gerrit_changes) AS changes
WHERE
changes.change=${CL}
)
, builds AS (
SELECT
bb.id AS bb_id,
bb.builder,
bb.input.gitiles_commit.id AS snapshot_hash,
JSON_EXTRACT_SCALAR(bb.input.properties, '$.build_target.name') AS build_target,
ARRAY_TO_STRING(ARRAY(SELECT
CONCAT('https://', c.host, '/c/', c.project, '/+/',
CAST(c.change AS STRING), '/',
CAST(c.patchset AS STRING))
FROM
UNNEST(bb.input.gerrit_changes) AS c), ' ') as patches
FROM
chromeos_ci_eng.buildbucket_builds AS bb
WHERE
bb.id IN (SELECT ids from bb_ids)
)
SELECT
build_target,
patches AS patch_urls,
snapshot_hash,
current_base_rootfs_size - baseline_base_rootfs_size AS rootfs_size_diff,
current_build_id AS build_id,
current_base_rootfs_size,
baseline_build_id,
baseline_base_rootfs_size
FROM (
SELECT
build_target,
current_build_id,
patches,
snapshot_hash,
e1.gauge AS current_base_rootfs_size,
baseline_build_id,
e2.gauge AS baseline_base_rootfs_size
FROM (
SELECT
builds.build_target,
builds.bb_id AS current_build_id,
builds.patches AS patches,
builds.snapshot_hash AS snapshot_hash,
bb_parent.id AS baseline_build_id
FROM
builds
JOIN
chromeos_ci_eng.buildbucket_builds AS bb_parent
ON
bb_parent.input.gitiles_commit.id = builds.snapshot_hash AND
bb_parent.builder.builder LIKE CONCAT(builds.build_target, '-snapshot') AND
JSON_EXTRACT_SCALAR(bb_parent.input.properties, '$.build_target.name') = builds.build_target
)
JOIN
chromeos_ci_eng.analysis_event_log.\`all\` AS ael1
ON
ael1.build_id = current_build_id
CROSS JOIN
UNNEST(ael1.create_image_result.events) AS e1
JOIN
chromeos_ci_eng.analysis_event_log.\`all\` AS ael2
ON
ael2.build_id = baseline_build_id
CROSS JOIN
UNNEST(ael2.create_image_result.events) AS e2
WHERE
e1.name LIKE '%.total_size.%' AND
e2.name LIKE '%.total_size.%'
)
WHERE
build_target IS NOT NULL AND
current_base_rootfs_size IS NOT NULL AND
baseline_base_rootfs_size IS NOT NULL
ORDER BY
rootfs_size_diff DESC;
SQLtoHERE