auto_submit/lib/service/bigquery.dart - mirrors/cocoon - Git at Google

 // Copyright 2022 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:auto_submit/exception/bigquery_exception.dart';
 import 'package:auto_submit/model/big_query_pull_request_record.dart';
 import 'package:auto_submit/model/big_query_revert_request_record.dart';
 import 'package:googleapis/bigquery/v2.dart';
 import 'package:http/http.dart';

 import 'access_client_provider.dart';

 const String selectRevertRequestDml = r'''
 SELECT organization,
        repository,
        reverting_pr_author,
        reverting_pr_number,
        reverting_pr_commit,
        reverting_pr_created_timestamp,
        reverting_pr_landed_timestamp,
        original_pr_author,
        original_pr_number,
        original_pr_commit,
        original_pr_created_timestamp,
        original_pr_landed_timestamp,
        review_issue_assignee,
        review_issue_number,
        review_issue_created_timestamp,
        review_issue_landed_timestamp,
        review_issue_closed_by
 FROM `flutter-dashboard.revert.revert_requests`
 WHERE reverting_pr_number=@REVERTING_PR_NUMBER AND repository=@REPOSITORY
 ''';

 /// Query to select all of the open revert review issues for update. Note that
 /// the review_issue_landed_timestamp is 0 for open issues. 0 is the default
 /// value instead of null since it is safer to process.
 const String selectRevertRequestReviewIssuesDml = r'''
 SELECT review_issue_assignee,
        review_issue_number,
        review_issue_created_timestamp,
        review_issue_landed_timestamp,
        review_issue_closed_by
 FROM `flutter-dashboard.revert.revert_requests`
 WHERE review_issue_landed_timestamp=0
 ''';

 const String updateRevertRequestRecordReviewDml = '''
 UPDATE `flutter-dashboard.revert.revert_requests`
 SET review_issue_landed_timestamp=@REVIEW_ISSUE_LANDED_TIMESTAMP,
     review_issue_closed_by=@REVIEW_ISSUE_CLOSED_BY
 WHERE review_issue_number=@REVIEW_ISSUE_NUMBER
 ''';

 const String insertRevertRequestDml = r'''
 INSERT INTO `flutter-dashboard.revert.revert_requests` (
   organization,
   repository,
   reverting_pr_author,
   reverting_pr_number,
   reverting_pr_commit,
   reverting_pr_created_timestamp,
   reverting_pr_landed_timestamp,
   original_pr_author,
   original_pr_number,
   original_pr_commit,
   original_pr_created_timestamp,
   original_pr_landed_timestamp,
   review_issue_assignee,
   review_issue_number,
   review_issue_created_timestamp,
   review_issue_landed_timestamp,
   review_issue_closed_by
 ) VALUES (
   @ORGANIZATION,
   @REPOSITORY,
   @REVERTING_PR_AUTHOR,
   @REVERTING_PR_NUMBER,
   @REVERTING_PR_COMMIT,
   @REVERTING_PR_CREATED_TIMESTAMP,
   @REVERTING_PR_LANDED_TIMESTAMP,
   @ORIGINAL_PR_AUTHOR,
   @ORIGINAL_PR_NUMBER,
   @ORIGINAL_PR_COMMIT,
   @ORIGINAL_PR_CREATED_TIMESTAMP,
   @ORIGINAL_PR_LANDED_TIMESTAMP,
   @REVIEW_ISSUE_ASSIGNEE,
   @REVIEW_ISSUE_NUMBER,
   @REVIEW_ISSUE_CREATED_TIMESTAMP,
   @REVIEW_ISSUE_LANDED_TIMESTAMP,
   @REVIEW_ISSUE_CLOSED_BY
 )
 ''';

 const String deleteRevertRequestDml = r'''
 DELETE FROM `flutter-dashboard.revert.revert_requests`
 WHERE reverting_pr_number=@REVERTING_PR_NUMBER AND repository=@REPOSITORY
 ''';

 const String insertPullRequestDml = r'''
 INSERT INTO `flutter-dashboard.autosubmit.pull_requests` (
   pr_created_timestamp,
   pr_landed_timestamp,
   organization,
   repository,
   author,
   pr_number,
   pr_commit,
   pr_request_type
 ) VALUES (
   @PR_CREATED_TIMESTAMP,
   @PR_LANDED_TIMESTAMP,
   @ORGANIZATION,
   @REPOSITORY,
   @AUTHOR,
   @PR_NUMBER,
   @PR_COMMIT,
   @PR_REQUEST_TYPE
 )
 ''';

 const String selectPullRequestDml = r'''
 SELECT pr_created_timestamp,
        pr_landed_timestamp,
        organization,
        repository,
        author,
        pr_number,
        pr_commit,
        pr_request_type
 FROM `flutter-dashboard.autosubmit.pull_requests`
 WHERE pr_number=@PR_NUMBER AND repository=@REPOSITORY
 ''';

 const String deletePullRequestDml = r'''
 DELETE FROM `flutter-dashboard.autosubmit.pull_requests`
 WHERE pr_number=@PR_NUMBER AND repository=@REPOSITORY
 ''';

 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;
   }

   /// Insert a new revert request into the database.
   Future<void> insertRevertRequestRecord({
     required String projectId,
     required RevertRequestRecord revertRequestRecord,
   }) async {
     final JobsResource jobsResource = await defaultJobs();

     final QueryRequest queryRequest = QueryRequest(
       query: insertRevertRequestDml,
       queryParameters: <QueryParameter>[
         _createStringQueryParameter(
           'ORGANIZATION',
           revertRequestRecord.organization,
         ),
         _createStringQueryParameter(
           'REPOSITORY',
           revertRequestRecord.repository,
         ),
         _createStringQueryParameter(
           'REVERTING_PR_AUTHOR',
           revertRequestRecord.author,
         ),
         _createIntegerQueryParameter(
           'REVERTING_PR_NUMBER',
           revertRequestRecord.prNumber!,
         ),
         _createStringQueryParameter(
           'REVERTING_PR_COMMIT',
           revertRequestRecord.prCommit,
         ),
         _createIntegerQueryParameter(
           'REVERTING_PR_CREATED_TIMESTAMP',
           revertRequestRecord.prCreatedTimestamp!.millisecondsSinceEpoch,
         ),
         _createIntegerQueryParameter(
           'REVERTING_PR_LANDED_TIMESTAMP',
           revertRequestRecord.prLandedTimestamp!.millisecondsSinceEpoch,
         ),
         _createStringQueryParameter(
           'ORIGINAL_PR_AUTHOR',
           revertRequestRecord.originalPrAuthor,
         ),
         _createIntegerQueryParameter(
           'ORIGINAL_PR_NUMBER',
           revertRequestRecord.originalPrNumber!,
         ),
         _createStringQueryParameter(
           'ORIGINAL_PR_COMMIT',
           revertRequestRecord.originalPrCommit,
         ),
         _createIntegerQueryParameter(
           'ORIGINAL_PR_CREATED_TIMESTAMP',
           revertRequestRecord.originalPrCreatedTimestamp!.millisecondsSinceEpoch,
         ),
         _createIntegerQueryParameter(
           'ORIGINAL_PR_LANDED_TIMESTAMP',
           revertRequestRecord.originalPrLandedTimestamp!.millisecondsSinceEpoch,
         ),
         _createStringQueryParameter(
           'REVIEW_ISSUE_ASSIGNEE',
           revertRequestRecord.reviewIssueAssignee,
         ),
         _createIntegerQueryParameter(
           'REVIEW_ISSUE_NUMBER',
           revertRequestRecord.reviewIssueNumber,
         ),
         _createIntegerQueryParameter(
           'REVIEW_ISSUE_CREATED_TIMESTAMP',
           revertRequestRecord.reviewIssueCreatedTimestamp!.millisecondsSinceEpoch,
         ),
         // This could not possibly be landed at the time of entry into the database but we should check for null.
         _createIntegerQueryParameter(
           'REVIEW_ISSUE_LANDED_TIMESTAMP',
           (revertRequestRecord.reviewIssueLandedTimestamp != null)
               ? revertRequestRecord.reviewIssueLandedTimestamp!.millisecondsSinceEpoch
               : 0,
         ),
         _createStringQueryParameter(
           'REVIEW_ISSUE_CLOSED_BY',
           '',
         ),
       ],
       useLegacySql: false,
     );

     final QueryResponse queryResponse = await jobsResource.query(queryRequest, projectId);
     if (!queryResponse.jobComplete!) {
       throw BigQueryException(
         'Insert revert request $revertRequestRecord did not complete.',
       );
     }

     if (queryResponse.numDmlAffectedRows != null && int.parse(queryResponse.numDmlAffectedRows!) != 1) {
       throw BigQueryException(
         'There was an error inserting $revertRequestRecord into the table.',
       );
     }
   }

   /// Select a specific revert request from the database.
   Future<RevertRequestRecord> selectRevertRequestByRevertPrNumber({
     required String projectId,
     required int prNumber,
     required String repository,
   }) async {
     final JobsResource jobsResource = await defaultJobs();

     final QueryRequest queryRequest = QueryRequest(
       query: selectRevertRequestDml,
       queryParameters: <QueryParameter>[
         _createIntegerQueryParameter('REVERTING_PR_NUMBER', prNumber),
         _createStringQueryParameter('REPOSITORY', repository),
       ],
       useLegacySql: false,
     );

     final QueryResponse queryResponse = await jobsResource.query(queryRequest, projectId);
     if (!queryResponse.jobComplete!) {
       throw BigQueryException(
         'Get revert request with pr# $prNumber in repository $repository did not complete.',
       );
     }

     final List<TableRow>? tableRows = queryResponse.rows;
     if (tableRows == null || tableRows.isEmpty) {
       throw BigQueryException(
         'Could not find an entry for revert request with pr# $prNumber in repository $repository.',
       );
     }

     if (tableRows.length != 1) {
       throw BigQueryException(
         'More than one record was returned for revert request with pr# $prNumber in repository $repository.',
       );
     }

     final TableRow tableRow = tableRows.first;

     return RevertRequestRecord(
       organization: tableRow.f![0].v as String,
       repository: tableRow.f![1].v as String,
       author: tableRow.f![2].v as String,
       prNumber: int.parse(tableRow.f![3].v as String),
       prCommit: tableRow.f![4].v as String,
       prCreatedTimestamp: (tableRow.f![5].v != null)
           ? DateTime.fromMillisecondsSinceEpoch(int.parse(tableRow.f![5].v as String))
           : null,
       prLandedTimestamp: (tableRow.f![6].v != null)
           ? DateTime.fromMillisecondsSinceEpoch(int.parse(tableRow.f![6].v as String))
           : null,
       originalPrAuthor: tableRow.f![7].v as String,
       originalPrNumber: int.parse(tableRow.f![8].v as String),
       originalPrCommit: tableRow.f![9].v as String,
       originalPrCreatedTimestamp: (tableRow.f![10].v != null)
           ? DateTime.fromMillisecondsSinceEpoch(int.parse(tableRow.f![10].v as String))
           : null,
       originalPrLandedTimestamp: (tableRow.f![11].v != null)
           ? DateTime.fromMillisecondsSinceEpoch(int.parse(tableRow.f![11].v as String))
           : null,
       reviewIssueAssignee: tableRow.f![12].v as String,
       reviewIssueNumber: int.parse(tableRow.f![13].v as String),
       reviewIssueCreatedTimestamp: (tableRow.f![14].v != null)
           ? DateTime.fromMillisecondsSinceEpoch(int.parse(tableRow.f![14].v as String))
           : null,
       reviewIssueLandedTimestamp: (tableRow.f![15].v != null)
           ? DateTime.fromMillisecondsSinceEpoch(int.parse(tableRow.f![15].v as String))
           : null,
       reviewIssueClosedBy: tableRow.f![16].v as String,
     );
   }

   /// Query to select the open review issues for update.
   ///
   /// Issues are open if the review_issue_landed_timestamp is equal to 0.
   Future<List<RevertRequestRecord>> selectOpenReviewRequestIssueRecordsList({
     required String projectId,
   }) async {
     final JobsResource jobsResource = await defaultJobs();

     final QueryRequest queryRequest = QueryRequest(
       query: selectRevertRequestReviewIssuesDml,
       useLegacySql: false,
     );

     final QueryResponse queryResponse = await jobsResource.query(queryRequest, projectId);
     if (!queryResponse.jobComplete!) {
       throw BigQueryException(
         'Get open review request issues records did not complete.',
       );
     }

     final List<TableRow>? tableRows = queryResponse.rows;
     if (tableRows == null || tableRows.isEmpty) {
       return <RevertRequestRecord>[];
     }

     List<RevertRequestRecord> openReviewRequestIssues = [];
     for (TableRow tableRow in tableRows) {
       openReviewRequestIssues.add(
         RevertRequestRecord(
           reviewIssueAssignee: tableRow.f![0].v as String,
           reviewIssueNumber: int.parse(tableRow.f![1].v as String),
           reviewIssueCreatedTimestamp: DateTime.fromMillisecondsSinceEpoch(int.parse(tableRow.f![2].v as String)),
           reviewIssueLandedTimestamp: DateTime.fromMillisecondsSinceEpoch(int.parse(tableRow.f![3].v as String)),
           reviewIssueClosedBy: tableRow.f![4].v as String,
         ),
       );
     }

     return openReviewRequestIssues;
   }

   /// Query the database to update a revert review issue with the timestamp the
   /// issue was closed at and the person who closed the issue.
   Future<void> updateReviewRequestIssue({
     required String projectId,
     required DateTime reviewIssueLandedTimestamp,
     required int reviewIssueNumber,
     required String reviewIssueClosedBy,
   }) async {
     final JobsResource jobsResource = await defaultJobs();

     final QueryRequest queryRequest = QueryRequest(
       query: updateRevertRequestRecordReviewDml,
       queryParameters: <QueryParameter>[
         _createIntegerQueryParameter(
           'REVIEW_ISSUE_LANDED_TIMESTAMP',
           reviewIssueLandedTimestamp.millisecondsSinceEpoch,
         ),
         _createIntegerQueryParameter(
           'REVIEW_ISSUE_NUMBER',
           reviewIssueNumber,
         ),
         _createStringQueryParameter(
           'REVIEW_ISSUE_CLOSED_BY',
           reviewIssueClosedBy,
         ),
       ],
       useLegacySql: false,
     );

     final QueryResponse queryResponse = await jobsResource.query(queryRequest, projectId);
     if (!queryResponse.jobComplete!) {
       throw BigQueryException(
         'Update of review issue $reviewIssueNumber did not complete.',
       );
     }

     if (queryResponse.numDmlAffectedRows != null && int.parse(queryResponse.numDmlAffectedRows!) != 1) {
       throw BigQueryException(
         'There was an error updating revert request record review issue landed timestamp with review issue number $reviewIssueNumber.',
       );
     }
   }

   Future<void> deleteRevertRequestRecord({
     required String projectId,
     required int prNumber,
     required String repository,
   }) async {
     final JobsResource jobsResource = await defaultJobs();

     final QueryRequest queryRequest = QueryRequest(
       query: deleteRevertRequestDml,
       queryParameters: <QueryParameter>[
         _createIntegerQueryParameter('REVERTING_PR_NUMBER', prNumber),
         _createStringQueryParameter('REPOSITORY', repository),
       ],
       useLegacySql: false,
     );

     final QueryResponse queryResponse = await jobsResource.query(queryRequest, projectId);
     if (!queryResponse.jobComplete!) {
       throw BigQueryException(
         'Delete revert request with pr# $prNumber in repository $repository did not complete.',
       );
     }

     if (queryResponse.numDmlAffectedRows == null || int.parse(queryResponse.numDmlAffectedRows!) == 0) {
       throw BigQueryException(
         'Could not find revert request with pr# $prNumber in repository $repository to delete.',
       );
     }

     if (int.parse(queryResponse.numDmlAffectedRows!) != 1) {
       throw BigQueryException(
         'More than one row was deleted from the database for revert request with pr# $prNumber in repository $repository.',
       );
     }
   }

   /// Insert a new pull request record into the database.
   Future<void> insertPullRequestRecord({
     required String projectId,
     required PullRequestRecord pullRequestRecord,
   }) async {
     final JobsResource jobsResource = await defaultJobs();

     final QueryRequest queryRequest = QueryRequest(
       query: insertPullRequestDml,
       queryParameters: <QueryParameter>[
         _createIntegerQueryParameter(
           'PR_CREATED_TIMESTAMP',
           pullRequestRecord.prCreatedTimestamp!.millisecondsSinceEpoch,
         ),
         _createIntegerQueryParameter(
           'PR_LANDED_TIMESTAMP',
           pullRequestRecord.prLandedTimestamp!.millisecondsSinceEpoch,
         ),
         _createStringQueryParameter(
           'ORGANIZATION',
           pullRequestRecord.organization,
         ),
         _createStringQueryParameter(
           'REPOSITORY',
           pullRequestRecord.repository,
         ),
         _createStringQueryParameter(
           'AUTHOR',
           pullRequestRecord.author,
         ),
         _createIntegerQueryParameter(
           'PR_NUMBER',
           pullRequestRecord.prNumber,
         ),
         _createStringQueryParameter(
           'PR_COMMIT',
           pullRequestRecord.prCommit,
         ),
         _createStringQueryParameter(
           'PR_REQUEST_TYPE',
           pullRequestRecord.prRequestType,
         ),
       ],
       useLegacySql: false,
     );

     final QueryResponse queryResponse = await jobsResource.query(queryRequest, projectId);
     if (!queryResponse.jobComplete!) {
       throw BigQueryException(
         'Insert pull request $pullRequestRecord did not complete.',
       );
     }

     if (queryResponse.numDmlAffectedRows != null && int.parse(queryResponse.numDmlAffectedRows!) != 1) {
       throw BigQueryException(
         'There was an error inserting $pullRequestRecord into the table.',
       );
     }
   }

   /// Select a specific pull request form the database.
   Future<PullRequestRecord> selectPullRequestRecordByPrNumber({
     required String projectId,
     required int prNumber,
     required String repository,
   }) async {
     final JobsResource jobsResource = await defaultJobs();

     final QueryRequest queryRequest = QueryRequest(
       query: selectPullRequestDml,
       queryParameters: <QueryParameter>[
         _createIntegerQueryParameter('PR_NUMBER', prNumber),
         _createStringQueryParameter('REPOSITORY', repository),
       ],
       useLegacySql: false,
     );

     final QueryResponse queryResponse = await jobsResource.query(queryRequest, projectId);
     if (!queryResponse.jobComplete!) {
       throw BigQueryException(
         'Get pull request by pr# $prNumber in repository $repository did not complete.',
       );
     }

     final List<TableRow>? tableRows = queryResponse.rows;
     if (tableRows == null || tableRows.isEmpty) {
       throw BigQueryException(
         'Could not find an entry for pull request with pr# $prNumber in repository $repository.',
       );
     }

     if (tableRows.length != 1) {
       throw BigQueryException(
         'More than one record was returned for pull request with pr# $prNumber in repository $repository.',
       );
     }

     final TableRow tableRow = tableRows.first;

     return PullRequestRecord(
       prCreatedTimestamp: (tableRow.f![0].v != null)
           ? DateTime.fromMillisecondsSinceEpoch(int.parse(tableRow.f![0].v as String))
           : null,
       prLandedTimestamp: (tableRow.f![1].v != null)
           ? DateTime.fromMillisecondsSinceEpoch(int.parse(tableRow.f![1].v as String))
           : null,
       organization: tableRow.f![2].v as String,
       repository: tableRow.f![3].v as String,
       author: tableRow.f![4].v as String,
       prNumber: int.parse(tableRow.f![5].v as String),
       prCommit: tableRow.f![6].v as String,
       prRequestType: tableRow.f![7].v as String,
     );
   }

   Future<void> deletePullRequestRecord({
     required String projectId,
     required int prNumber,
     required String repository,
   }) async {
     final JobsResource jobsResource = await defaultJobs();

     final QueryRequest queryRequest = QueryRequest(
       query: deletePullRequestDml,
       queryParameters: <QueryParameter>[
         _createIntegerQueryParameter('PR_NUMBER', prNumber),
         _createStringQueryParameter('REPOSITORY', repository),
       ],
       useLegacySql: false,
     );

     final QueryResponse queryResponse = await jobsResource.query(queryRequest, projectId);
     if (!queryResponse.jobComplete!) {
       throw BigQueryException(
         'Delete pull request with pr# $prNumber in repository $repository did not complete.',
       );
     }

     if (queryResponse.numDmlAffectedRows == null || int.parse(queryResponse.numDmlAffectedRows!) == 0) {
       throw BigQueryException(
         'Could not find pull request with pr# $prNumber in repository $repository to delete.',
       );
     }

     if (int.parse(queryResponse.numDmlAffectedRows!) != 1) {
       throw BigQueryException(
         'More than one row was deleted from the database for pull request with pr# $prNumber in repository $repository.',
       );
     }
   }

   /// Create an int parameter for query substitution.
   QueryParameter _createIntegerQueryParameter(String name, int? value) {
     return QueryParameter(
       name: name,
       parameterType: QueryParameterType(type: 'INT64'),
       parameterValue: QueryParameterValue(value: value.toString()),
     );
   }

   /// Create a String parameter for query substitution.
   QueryParameter _createStringQueryParameter(String name, String? value) {
     return QueryParameter(
       name: name,
       parameterType: QueryParameterType(type: 'STRING'),
       parameterValue: QueryParameterValue(value: value),
     );
   }
 }
	// Copyright 2022 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:auto_submit/exception/bigquery_exception.dart';
	import 'package:auto_submit/model/big_query_pull_request_record.dart';
	import 'package:auto_submit/model/big_query_revert_request_record.dart';
	import 'package:googleapis/bigquery/v2.dart';
	import 'package:http/http.dart';

	import 'access_client_provider.dart';

	const String selectRevertRequestDml = r'''
	SELECT organization,
	repository,
	reverting_pr_author,
	reverting_pr_number,
	reverting_pr_commit,
	reverting_pr_created_timestamp,
	reverting_pr_landed_timestamp,
	original_pr_author,
	original_pr_number,
	original_pr_commit,
	original_pr_created_timestamp,
	original_pr_landed_timestamp,
	review_issue_assignee,
	review_issue_number,
	review_issue_created_timestamp,
	review_issue_landed_timestamp,
	review_issue_closed_by
	FROM `flutter-dashboard.revert.revert_requests`
	WHERE reverting_pr_number=@REVERTING_PR_NUMBER AND repository=@REPOSITORY
	''';

	/// Query to select all of the open revert review issues for update. Note that
	/// the review_issue_landed_timestamp is 0 for open issues. 0 is the default
	/// value instead of null since it is safer to process.
	const String selectRevertRequestReviewIssuesDml = r'''
	SELECT review_issue_assignee,
	review_issue_number,
	review_issue_created_timestamp,
	review_issue_landed_timestamp,
	review_issue_closed_by
	FROM `flutter-dashboard.revert.revert_requests`
	WHERE review_issue_landed_timestamp=0
	''';

	const String updateRevertRequestRecordReviewDml = '''
	UPDATE `flutter-dashboard.revert.revert_requests`
	SET review_issue_landed_timestamp=@REVIEW_ISSUE_LANDED_TIMESTAMP,
	review_issue_closed_by=@REVIEW_ISSUE_CLOSED_BY
	WHERE review_issue_number=@REVIEW_ISSUE_NUMBER
	''';

	const String insertRevertRequestDml = r'''
	INSERT INTO `flutter-dashboard.revert.revert_requests` (
	organization,
	repository,
	reverting_pr_author,
	reverting_pr_number,
	reverting_pr_commit,
	reverting_pr_created_timestamp,
	reverting_pr_landed_timestamp,
	original_pr_author,
	original_pr_number,
	original_pr_commit,
	original_pr_created_timestamp,
	original_pr_landed_timestamp,
	review_issue_assignee,
	review_issue_number,
	review_issue_created_timestamp,
	review_issue_landed_timestamp,
	review_issue_closed_by
	) VALUES (
	@ORGANIZATION,
	@REPOSITORY,
	@REVERTING_PR_AUTHOR,
	@REVERTING_PR_NUMBER,
	@REVERTING_PR_COMMIT,
	@REVERTING_PR_CREATED_TIMESTAMP,
	@REVERTING_PR_LANDED_TIMESTAMP,
	@ORIGINAL_PR_AUTHOR,
	@ORIGINAL_PR_NUMBER,
	@ORIGINAL_PR_COMMIT,
	@ORIGINAL_PR_CREATED_TIMESTAMP,
	@ORIGINAL_PR_LANDED_TIMESTAMP,
	@REVIEW_ISSUE_ASSIGNEE,
	@REVIEW_ISSUE_NUMBER,
	@REVIEW_ISSUE_CREATED_TIMESTAMP,
	@REVIEW_ISSUE_LANDED_TIMESTAMP,
	@REVIEW_ISSUE_CLOSED_BY
	)
	''';

	const String deleteRevertRequestDml = r'''
	DELETE FROM `flutter-dashboard.revert.revert_requests`
	WHERE reverting_pr_number=@REVERTING_PR_NUMBER AND repository=@REPOSITORY
	''';

	const String insertPullRequestDml = r'''
	INSERT INTO `flutter-dashboard.autosubmit.pull_requests` (
	pr_created_timestamp,
	pr_landed_timestamp,
	organization,
	repository,
	author,
	pr_number,
	pr_commit,
	pr_request_type
	) VALUES (
	@PR_CREATED_TIMESTAMP,
	@PR_LANDED_TIMESTAMP,
	@ORGANIZATION,
	@REPOSITORY,
	@AUTHOR,
	@PR_NUMBER,
	@PR_COMMIT,
	@PR_REQUEST_TYPE
	)
	''';

	const String selectPullRequestDml = r'''
	SELECT pr_created_timestamp,
	pr_landed_timestamp,
	organization,
	repository,
	author,
	pr_number,
	pr_commit,
	pr_request_type
	FROM `flutter-dashboard.autosubmit.pull_requests`
	WHERE pr_number=@PR_NUMBER AND repository=@REPOSITORY
	''';

	const String deletePullRequestDml = r'''
	DELETE FROM `flutter-dashboard.autosubmit.pull_requests`
	WHERE pr_number=@PR_NUMBER AND repository=@REPOSITORY
	''';

	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;
	}

	/// Insert a new revert request into the database.
	Future<void> insertRevertRequestRecord({
	required String projectId,
	required RevertRequestRecord revertRequestRecord,
	}) async {
	final JobsResource jobsResource = await defaultJobs();

	final QueryRequest queryRequest = QueryRequest(
	query: insertRevertRequestDml,
	queryParameters: <QueryParameter>[
	_createStringQueryParameter(
	'ORGANIZATION',
	revertRequestRecord.organization,
	),
	_createStringQueryParameter(
	'REPOSITORY',
	revertRequestRecord.repository,
	),
	_createStringQueryParameter(
	'REVERTING_PR_AUTHOR',
	revertRequestRecord.author,
	),
	_createIntegerQueryParameter(
	'REVERTING_PR_NUMBER',
	revertRequestRecord.prNumber!,
	),
	_createStringQueryParameter(
	'REVERTING_PR_COMMIT',
	revertRequestRecord.prCommit,
	),
	_createIntegerQueryParameter(
	'REVERTING_PR_CREATED_TIMESTAMP',
	revertRequestRecord.prCreatedTimestamp!.millisecondsSinceEpoch,
	),
	_createIntegerQueryParameter(
	'REVERTING_PR_LANDED_TIMESTAMP',
	revertRequestRecord.prLandedTimestamp!.millisecondsSinceEpoch,
	),
	_createStringQueryParameter(
	'ORIGINAL_PR_AUTHOR',
	revertRequestRecord.originalPrAuthor,
	),
	_createIntegerQueryParameter(
	'ORIGINAL_PR_NUMBER',
	revertRequestRecord.originalPrNumber!,
	),
	_createStringQueryParameter(
	'ORIGINAL_PR_COMMIT',
	revertRequestRecord.originalPrCommit,
	),
	_createIntegerQueryParameter(
	'ORIGINAL_PR_CREATED_TIMESTAMP',
	revertRequestRecord.originalPrCreatedTimestamp!.millisecondsSinceEpoch,
	),
	_createIntegerQueryParameter(
	'ORIGINAL_PR_LANDED_TIMESTAMP',
	revertRequestRecord.originalPrLandedTimestamp!.millisecondsSinceEpoch,
	),
	_createStringQueryParameter(
	'REVIEW_ISSUE_ASSIGNEE',
	revertRequestRecord.reviewIssueAssignee,
	),
	_createIntegerQueryParameter(
	'REVIEW_ISSUE_NUMBER',
	revertRequestRecord.reviewIssueNumber,
	),
	_createIntegerQueryParameter(
	'REVIEW_ISSUE_CREATED_TIMESTAMP',
	revertRequestRecord.reviewIssueCreatedTimestamp!.millisecondsSinceEpoch,
	),
	// This could not possibly be landed at the time of entry into the database but we should check for null.
	_createIntegerQueryParameter(
	'REVIEW_ISSUE_LANDED_TIMESTAMP',
	(revertRequestRecord.reviewIssueLandedTimestamp != null)
	? revertRequestRecord.reviewIssueLandedTimestamp!.millisecondsSinceEpoch
	: 0,
	),
	_createStringQueryParameter(
	'REVIEW_ISSUE_CLOSED_BY',
	'',
	),
	],
	useLegacySql: false,
	);

	final QueryResponse queryResponse = await jobsResource.query(queryRequest, projectId);
	if (!queryResponse.jobComplete!) {
	throw BigQueryException(
	'Insert revert request $revertRequestRecord did not complete.',
	);
	}

	if (queryResponse.numDmlAffectedRows != null && int.parse(queryResponse.numDmlAffectedRows!) != 1) {
	throw BigQueryException(
	'There was an error inserting $revertRequestRecord into the table.',
	);
	}
	}

	/// Select a specific revert request from the database.
	Future<RevertRequestRecord> selectRevertRequestByRevertPrNumber({
	required String projectId,
	required int prNumber,
	required String repository,
	}) async {
	final JobsResource jobsResource = await defaultJobs();

	final QueryRequest queryRequest = QueryRequest(
	query: selectRevertRequestDml,
	queryParameters: <QueryParameter>[
	_createIntegerQueryParameter('REVERTING_PR_NUMBER', prNumber),
	_createStringQueryParameter('REPOSITORY', repository),
	],
	useLegacySql: false,
	);

	final QueryResponse queryResponse = await jobsResource.query(queryRequest, projectId);
	if (!queryResponse.jobComplete!) {
	throw BigQueryException(
	'Get revert request with pr# $prNumber in repository $repository did not complete.',
	);
	}

	final List<TableRow>? tableRows = queryResponse.rows;
	if (tableRows == null \|\| tableRows.isEmpty) {
	throw BigQueryException(
	'Could not find an entry for revert request with pr# $prNumber in repository $repository.',
	);
	}

	if (tableRows.length != 1) {
	throw BigQueryException(
	'More than one record was returned for revert request with pr# $prNumber in repository $repository.',
	);
	}

	final TableRow tableRow = tableRows.first;

	return RevertRequestRecord(
	organization: tableRow.f![0].v as String,
	repository: tableRow.f![1].v as String,
	author: tableRow.f![2].v as String,
	prNumber: int.parse(tableRow.f![3].v as String),
	prCommit: tableRow.f![4].v as String,
	prCreatedTimestamp: (tableRow.f![5].v != null)
	? DateTime.fromMillisecondsSinceEpoch(int.parse(tableRow.f![5].v as String))
	: null,
	prLandedTimestamp: (tableRow.f![6].v != null)
	? DateTime.fromMillisecondsSinceEpoch(int.parse(tableRow.f![6].v as String))
	: null,
	originalPrAuthor: tableRow.f![7].v as String,
	originalPrNumber: int.parse(tableRow.f![8].v as String),
	originalPrCommit: tableRow.f![9].v as String,
	originalPrCreatedTimestamp: (tableRow.f![10].v != null)
	? DateTime.fromMillisecondsSinceEpoch(int.parse(tableRow.f![10].v as String))
	: null,
	originalPrLandedTimestamp: (tableRow.f![11].v != null)
	? DateTime.fromMillisecondsSinceEpoch(int.parse(tableRow.f![11].v as String))
	: null,
	reviewIssueAssignee: tableRow.f![12].v as String,
	reviewIssueNumber: int.parse(tableRow.f![13].v as String),
	reviewIssueCreatedTimestamp: (tableRow.f![14].v != null)
	? DateTime.fromMillisecondsSinceEpoch(int.parse(tableRow.f![14].v as String))
	: null,
	reviewIssueLandedTimestamp: (tableRow.f![15].v != null)
	? DateTime.fromMillisecondsSinceEpoch(int.parse(tableRow.f![15].v as String))
	: null,
	reviewIssueClosedBy: tableRow.f![16].v as String,
	);
	}

	/// Query to select the open review issues for update.
	///
	/// Issues are open if the review_issue_landed_timestamp is equal to 0.
	Future<List<RevertRequestRecord>> selectOpenReviewRequestIssueRecordsList({
	required String projectId,
	}) async {
	final JobsResource jobsResource = await defaultJobs();

	final QueryRequest queryRequest = QueryRequest(
	query: selectRevertRequestReviewIssuesDml,
	useLegacySql: false,
	);

	final QueryResponse queryResponse = await jobsResource.query(queryRequest, projectId);
	if (!queryResponse.jobComplete!) {
	throw BigQueryException(
	'Get open review request issues records did not complete.',
	);
	}

	final List<TableRow>? tableRows = queryResponse.rows;
	if (tableRows == null \|\| tableRows.isEmpty) {
	return <RevertRequestRecord>[];
	}

	List<RevertRequestRecord> openReviewRequestIssues = [];
	for (TableRow tableRow in tableRows) {
	openReviewRequestIssues.add(
	RevertRequestRecord(
	reviewIssueAssignee: tableRow.f![0].v as String,
	reviewIssueNumber: int.parse(tableRow.f![1].v as String),
	reviewIssueCreatedTimestamp: DateTime.fromMillisecondsSinceEpoch(int.parse(tableRow.f![2].v as String)),
	reviewIssueLandedTimestamp: DateTime.fromMillisecondsSinceEpoch(int.parse(tableRow.f![3].v as String)),
	reviewIssueClosedBy: tableRow.f![4].v as String,
	),
	);
	}

	return openReviewRequestIssues;
	}

	/// Query the database to update a revert review issue with the timestamp the
	/// issue was closed at and the person who closed the issue.
	Future<void> updateReviewRequestIssue({
	required String projectId,
	required DateTime reviewIssueLandedTimestamp,
	required int reviewIssueNumber,
	required String reviewIssueClosedBy,
	}) async {
	final JobsResource jobsResource = await defaultJobs();

	final QueryRequest queryRequest = QueryRequest(
	query: updateRevertRequestRecordReviewDml,
	queryParameters: <QueryParameter>[
	_createIntegerQueryParameter(
	'REVIEW_ISSUE_LANDED_TIMESTAMP',
	reviewIssueLandedTimestamp.millisecondsSinceEpoch,
	),
	_createIntegerQueryParameter(
	'REVIEW_ISSUE_NUMBER',
	reviewIssueNumber,
	),
	_createStringQueryParameter(
	'REVIEW_ISSUE_CLOSED_BY',
	reviewIssueClosedBy,
	),
	],
	useLegacySql: false,
	);

	final QueryResponse queryResponse = await jobsResource.query(queryRequest, projectId);
	if (!queryResponse.jobComplete!) {
	throw BigQueryException(
	'Update of review issue $reviewIssueNumber did not complete.',
	);
	}

	if (queryResponse.numDmlAffectedRows != null && int.parse(queryResponse.numDmlAffectedRows!) != 1) {
	throw BigQueryException(
	'There was an error updating revert request record review issue landed timestamp with review issue number $reviewIssueNumber.',
	);
	}
	}

	Future<void> deleteRevertRequestRecord({
	required String projectId,
	required int prNumber,
	required String repository,
	}) async {
	final JobsResource jobsResource = await defaultJobs();

	final QueryRequest queryRequest = QueryRequest(
	query: deleteRevertRequestDml,
	queryParameters: <QueryParameter>[
	_createIntegerQueryParameter('REVERTING_PR_NUMBER', prNumber),
	_createStringQueryParameter('REPOSITORY', repository),
	],
	useLegacySql: false,
	);

	final QueryResponse queryResponse = await jobsResource.query(queryRequest, projectId);
	if (!queryResponse.jobComplete!) {
	throw BigQueryException(
	'Delete revert request with pr# $prNumber in repository $repository did not complete.',
	);
	}

	if (queryResponse.numDmlAffectedRows == null \|\| int.parse(queryResponse.numDmlAffectedRows!) == 0) {
	throw BigQueryException(
	'Could not find revert request with pr# $prNumber in repository $repository to delete.',
	);
	}

	if (int.parse(queryResponse.numDmlAffectedRows!) != 1) {
	throw BigQueryException(
	'More than one row was deleted from the database for revert request with pr# $prNumber in repository $repository.',
	);
	}
	}

	/// Insert a new pull request record into the database.
	Future<void> insertPullRequestRecord({
	required String projectId,
	required PullRequestRecord pullRequestRecord,
	}) async {
	final JobsResource jobsResource = await defaultJobs();

	final QueryRequest queryRequest = QueryRequest(
	query: insertPullRequestDml,
	queryParameters: <QueryParameter>[
	_createIntegerQueryParameter(
	'PR_CREATED_TIMESTAMP',
	pullRequestRecord.prCreatedTimestamp!.millisecondsSinceEpoch,
	),
	_createIntegerQueryParameter(
	'PR_LANDED_TIMESTAMP',
	pullRequestRecord.prLandedTimestamp!.millisecondsSinceEpoch,
	),
	_createStringQueryParameter(
	'ORGANIZATION',
	pullRequestRecord.organization,
	),
	_createStringQueryParameter(
	'REPOSITORY',
	pullRequestRecord.repository,
	),
	_createStringQueryParameter(
	'AUTHOR',
	pullRequestRecord.author,
	),
	_createIntegerQueryParameter(
	'PR_NUMBER',
	pullRequestRecord.prNumber,
	),
	_createStringQueryParameter(
	'PR_COMMIT',
	pullRequestRecord.prCommit,
	),
	_createStringQueryParameter(
	'PR_REQUEST_TYPE',
	pullRequestRecord.prRequestType,
	),
	],
	useLegacySql: false,
	);

	final QueryResponse queryResponse = await jobsResource.query(queryRequest, projectId);
	if (!queryResponse.jobComplete!) {
	throw BigQueryException(
	'Insert pull request $pullRequestRecord did not complete.',
	);
	}

	if (queryResponse.numDmlAffectedRows != null && int.parse(queryResponse.numDmlAffectedRows!) != 1) {
	throw BigQueryException(
	'There was an error inserting $pullRequestRecord into the table.',
	);
	}
	}

	/// Select a specific pull request form the database.
	Future<PullRequestRecord> selectPullRequestRecordByPrNumber({
	required String projectId,
	required int prNumber,
	required String repository,
	}) async {
	final JobsResource jobsResource = await defaultJobs();

	final QueryRequest queryRequest = QueryRequest(
	query: selectPullRequestDml,
	queryParameters: <QueryParameter>[
	_createIntegerQueryParameter('PR_NUMBER', prNumber),
	_createStringQueryParameter('REPOSITORY', repository),
	],
	useLegacySql: false,
	);

	final QueryResponse queryResponse = await jobsResource.query(queryRequest, projectId);
	if (!queryResponse.jobComplete!) {
	throw BigQueryException(
	'Get pull request by pr# $prNumber in repository $repository did not complete.',
	);
	}

	final List<TableRow>? tableRows = queryResponse.rows;
	if (tableRows == null \|\| tableRows.isEmpty) {
	throw BigQueryException(
	'Could not find an entry for pull request with pr# $prNumber in repository $repository.',
	);
	}

	if (tableRows.length != 1) {
	throw BigQueryException(
	'More than one record was returned for pull request with pr# $prNumber in repository $repository.',
	);
	}

	final TableRow tableRow = tableRows.first;

	return PullRequestRecord(
	prCreatedTimestamp: (tableRow.f![0].v != null)
	? DateTime.fromMillisecondsSinceEpoch(int.parse(tableRow.f![0].v as String))
	: null,
	prLandedTimestamp: (tableRow.f![1].v != null)
	? DateTime.fromMillisecondsSinceEpoch(int.parse(tableRow.f![1].v as String))
	: null,
	organization: tableRow.f![2].v as String,
	repository: tableRow.f![3].v as String,
	author: tableRow.f![4].v as String,
	prNumber: int.parse(tableRow.f![5].v as String),
	prCommit: tableRow.f![6].v as String,
	prRequestType: tableRow.f![7].v as String,
	);
	}

	Future<void> deletePullRequestRecord({
	required String projectId,
	required int prNumber,
	required String repository,
	}) async {
	final JobsResource jobsResource = await defaultJobs();

	final QueryRequest queryRequest = QueryRequest(
	query: deletePullRequestDml,
	queryParameters: <QueryParameter>[
	_createIntegerQueryParameter('PR_NUMBER', prNumber),
	_createStringQueryParameter('REPOSITORY', repository),
	],
	useLegacySql: false,
	);

	final QueryResponse queryResponse = await jobsResource.query(queryRequest, projectId);
	if (!queryResponse.jobComplete!) {
	throw BigQueryException(
	'Delete pull request with pr# $prNumber in repository $repository did not complete.',
	);
	}

	if (queryResponse.numDmlAffectedRows == null \|\| int.parse(queryResponse.numDmlAffectedRows!) == 0) {
	throw BigQueryException(
	'Could not find pull request with pr# $prNumber in repository $repository to delete.',
	);
	}

	if (int.parse(queryResponse.numDmlAffectedRows!) != 1) {
	throw BigQueryException(
	'More than one row was deleted from the database for pull request with pr# $prNumber in repository $repository.',
	);
	}
	}

	/// Create an int parameter for query substitution.
	QueryParameter _createIntegerQueryParameter(String name, int? value) {
	return QueryParameter(
	name: name,
	parameterType: QueryParameterType(type: 'INT64'),
	parameterValue: QueryParameterValue(value: value.toString()),
	);
	}

	/// Create a String parameter for query substitution.
	QueryParameter _createStringQueryParameter(String name, String? value) {
	return QueryParameter(
	name: name,
	parameterType: QueryParameterType(type: 'STRING'),
	parameterValue: QueryParameterValue(value: value),
	);
	}
	}