blob: 2b3287011bf12392137d350b3c9beab015baadb3 [file] [log] [blame]
// Copyright 2020 The Flutter Authors. All rights reserved.
// Use of this source code is governed by a BSD-style license that can be
// found in the LICENSE file.
import 'dart:async';
import 'package:googleapis/bigquery/v2.dart';
import 'package:http/http.dart';
import 'access_client_provider.dart';
/// The sql query to query the build statistic from the
/// `flutter-dashboard.datasite.luci_prod_build_status`.
///
/// The schema of the `luci_prod_build_status` table:
/// time TIMESTAMP
/// date DATE
/// sha STRING
/// flaky_builds STRING
/// succeeded_builds STRING
/// branch STRING
/// device_os STRING
/// pool STRING
/// repo STRING
/// builder_name STRING
/// success_count INTEGER
/// failure_count INTEGER
/// is_flaky INTEGER
///
/// This returns latest [LIMIT] number of build stats for each builder.
const String getBuilderStatisticQuery = r'''
select builder_name,
sum(is_flaky) as flaky_number,
count(*) as total_number,
string_agg(case when is_flaky = 1 then flaky_builds end, ', ') as flaky_builds,
string_agg(succeeded_builds, ', ') as succeeded_builds,
array_agg(case when is_flaky = 1 then sha end IGNORE NULLS ORDER BY date DESC)[ordinal(1)] as recent_flaky_commit,
array_agg(case when is_flaky = 1 then flaky_builds end IGNORE NULLS ORDER BY date DESC)[ordinal(1)] as flaky_build_of_recent_flaky_commit,
sum(is_flaky)/count(*) as flaky_ratio,
min(date) as from_date,
max(date) as to_date
from (select *, row_number() over (partition by builder_name order by time desc) as rank from `flutter-dashboard.datasite.luci_prod_build_status`)
where date>=date_sub(current_date(), interval 30 day) and
builder_name not like '%Drone' and
repo='flutter' and
branch='master' and
pool = 'luci.flutter.prod' and
builder_name not like '%Beta%' and
builder_name not like '% beta %' and
builder_name not like '%Stable%' and
builder_name not like '% stable %' and
builder_name not like '%Dev%' and
builder_name not like '% dev %' and
rank<=@LIMIT
group by builder_name;
''';
const String getStagingBuilderStatisticQuery = r'''
select builder_name,
sum(is_flaky) as flaky_number,
count(*) as total_number,
string_agg(case when is_flaky = 1 then flaky_builds end, ', ') as flaky_builds,
string_agg(succeeded_builds, ', ') as succeeded_builds,
array_agg(case when is_flaky = 1 then sha end IGNORE NULLS ORDER BY date DESC)[ordinal(1)] as recent_flaky_commit,
array_agg(case when is_flaky = 1 then flaky_builds end IGNORE NULLS ORDER BY date DESC)[ordinal(1)] as flaky_build_of_recent_flaky_commit,
sum(is_flaky)/count(*) as flaky_ratio,
min(date) as from_date,
max(date) as to_date
from (select *, row_number() over (partition by builder_name order by time desc) as rank from `flutter-dashboard.datasite.luci_staging_build_status`)
where date>=date_sub(current_date(), interval 30 day) and
builder_name not like '%Drone' and
repo='flutter' and
branch='master' and
pool = 'luci.flutter.staging' and
builder_name not like '%Beta%' and
builder_name not like '% beta %' and
rank<=@LIMIT
group by builder_name;
''';
// Returns builds in the past 30 days to exclude obsolete historical data.
const String getRecordsQuery = r'''
select sha, is_flaky, failure_count from `flutter-dashboard.datasite.luci_staging_build_status`
where builder_name=@BUILDER_NAME and date>=date_sub(current_date(), interval 30 day)
order by time desc
limit @LIMIT
''';
class BigqueryService {
const BigqueryService(this.accessClientProvider);
/// AccessClientProvider for OAuth 2.0 authenticated access client
final AccessClientProvider accessClientProvider;
/// Return a [TabledataResource] with an authenticated [client]
Future<TabledataResource> defaultTabledata() async {
final Client client = await accessClientProvider.createAccessClient(
scopes: const <String>[BigqueryApi.bigqueryScope],
);
return BigqueryApi(client).tabledata;
}
/// Return a [JobsResource] with an authenticated [client]
Future<JobsResource> defaultJobs() async {
final Client client = await accessClientProvider.createAccessClient(
scopes: const <String>[BigqueryApi.bigqueryScope],
);
return BigqueryApi(client).jobs;
}
/// Return the top [limit] number of current builder statistic.
///
/// See getBuilderStatisticQuery to get the detail information about the table
/// schema
Future<List<BuilderStatistic>> listBuilderStatistic(
String projectId, {
int limit = 100,
String bucket = 'prod',
}) async {
final JobsResource jobsResource = await defaultJobs();
final QueryRequest query = QueryRequest.fromJson(<String, Object>{
'query': bucket == 'staging' ? getStagingBuilderStatisticQuery : getBuilderStatisticQuery,
'queryParameters': <Map<String, Object>>[
<String, Object>{
'name': 'LIMIT',
'parameterType': <String, Object>{'type': 'INT64'},
'parameterValue': <String, Object>{'value': '$limit'},
},
],
'useLegacySql': false,
});
final QueryResponse response = await jobsResource.query(query, projectId);
if (!response.jobComplete!) {
throw 'job does not complete';
}
final List<BuilderStatistic> result = <BuilderStatistic>[];
for (final TableRow row in response.rows!) {
final String builder = row.f![0].v as String;
List<String>? flakyBuilds = (row.f![3].v as String?)?.split(', ');
flakyBuilds?.sort();
flakyBuilds = flakyBuilds?.reversed.toList();
List<String>? succeededBuilds = (row.f![4].v as String?)?.split(', ');
succeededBuilds?.sort();
succeededBuilds = succeededBuilds?.reversed.toList();
result.add(
BuilderStatistic(
name: builder,
flakyRate: double.parse(row.f![7].v as String),
flakyBuilds: flakyBuilds ?? const <String>[],
succeededBuilds: succeededBuilds ?? const <String>[],
recentCommit: row.f![5].v as String?,
flakyBuildOfRecentCommit: row.f![6].v as String?,
flakyNumber: int.parse(row.f![1].v as String),
totalNumber: int.parse(row.f![2].v as String),
fromDate: row.f![8].v as String?,
toDate: row.f![9].v as String?,
),
);
}
return result;
}
/// Return the list of current builder statistic.
///
/// See getBuilderStatisticQuery to get the detail information about the table
/// schema
Future<List<BuilderRecord>> listRecentBuildRecordsForBuilder(
String projectId, {
String? builder,
int? limit,
}) async {
final JobsResource jobsResource = await defaultJobs();
final QueryRequest query = QueryRequest.fromJson(<String, Object>{
'query': getRecordsQuery,
'parameterMode': 'NAMED',
'queryParameters': <Map<String, Object>>[
<String, Object>{
'name': 'BUILDER_NAME',
'parameterType': <String, Object>{'type': 'STRING'},
'parameterValue': <String, Object?>{'value': builder},
},
<String, Object>{
'name': 'LIMIT',
'parameterType': <String, Object>{'type': 'INT64'},
'parameterValue': <String, Object>{'value': '$limit'},
},
],
'useLegacySql': false,
});
final QueryResponse response = await jobsResource.query(query, projectId);
if (!response.jobComplete!) {
throw 'job does not complete';
}
final List<BuilderRecord> result = <BuilderRecord>[];
// When a test is newly marked as flaky, it is possible no execution exists.
if (response.rows == null) {
return result;
}
for (final TableRow row in response.rows!) {
result.add(
BuilderRecord(
commit: row.f![0].v as String,
isFlaky: row.f![1].v as String != '0',
isFailed: row.f![2].v as String != '0',
),
);
}
return result;
}
}
class BuilderRecord {
BuilderRecord({
required this.commit,
required this.isFlaky,
required this.isFailed,
});
final String commit;
final bool isFlaky;
final bool isFailed;
}
class BuilderStatistic {
BuilderStatistic({
required this.name,
required this.flakyRate,
required this.flakyNumber,
required this.totalNumber,
this.flakyBuilds,
this.succeededBuilds,
this.recentCommit,
this.flakyBuildOfRecentCommit,
this.fromDate,
this.toDate,
});
final String name;
final double flakyRate;
final List<String>? flakyBuilds;
final List<String>? succeededBuilds;
final String? recentCommit;
final String? flakyBuildOfRecentCommit;
final int flakyNumber;
final int totalNumber;
final String? fromDate;
final String? toDate;
}