cmd/export_to_sheets/main.go

// Copyright 2019 The SwiftShader Authors. All Rights Reserved.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//    http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

// export_to_sheets updates a Google sheets document with the latest test
// results
package main

import (
	"bufio"
	"bytes"
	"context"
	"encoding/json"
	"flag"
	"fmt"
	"io/ioutil"
	"log"
	"net/http"
	"os"
	"path/filepath"
	"strings"

	"swiftshader.googlesource.com/SwiftShader/tests/regres/consts"
	"swiftshader.googlesource.com/SwiftShader/tests/regres/git"
	"swiftshader.googlesource.com/SwiftShader/tests/regres/testlist"

	"golang.org/x/oauth2"
	"golang.org/x/oauth2/google"
	"google.golang.org/api/sheets/v4"
)

var (
	authdir       = flag.String("authdir", "~/.regres-auth", "directory to hold credentials.json and generated token")
	projectPath   = flag.String("projpath", ".", "project path")
	testListPath  = flag.String("testlist", "tests/regres/full-tests.json", "project relative path to the test list .json file")
	spreadsheetID = flag.String("spreadsheet", "1RCxbqtKNDG9rVMe_xHMapMBgzOCp24mumab73SbHtfw", "identifier of the spreadsheet to update")
)

const (
	columnGitHash = "GIT_HASH"
	columnGitDate = "GIT_DATE"
)

func main() {
	flag.Parse()

	if err := run(); err != nil {
		log.Fatalln(err)
	}
}

func run() error {
	// Load the full test list. We use this to find the test file names.
	lists, err := testlist.Load(".", *testListPath)
	if err != nil {
		return fmt.Errorf("failed to load test list: %w", err)
	}

	// Load the creditials used for editing the Google Sheets spreadsheet.
	srv, err := createSheetsService(*authdir)
	if err != nil {
		return fmt.Errorf("failed to authenticate: %w", err)
	}

	// Ensure that there is a sheet for each of the test lists.
	if err := createTestListSheets(srv, lists); err != nil {
		return fmt.Errorf("failed to create sheets: %w", err)
	}

	spreadsheet, err := srv.Spreadsheets.Get(*spreadsheetID).Do()
	if err != nil {
		return fmt.Errorf("failed to get spreadsheet: %w", err)
	}

	req := sheets.BatchUpdateValuesRequest{
		ValueInputOption: "RAW",
	}

	testListDir := filepath.Dir(filepath.Join(*projectPath, *testListPath))
	changes, err := git.Log(testListDir, 100)
	if err != nil {
		return fmt.Errorf("failed to get git changes for '%v': %w", testListDir, err)
	}

	for _, group := range lists {
		sheetName := group.Name
		fmt.Println("Processing sheet", sheetName)
		sheet := getSheet(spreadsheet, sheetName)
		if sheet == nil {
			return fmt.Errorf("sheet '%v' not found: %w", sheetName, err)
		}

		columnHeaders, err := fetchRow(srv, spreadsheet, sheet, 0)
		if err != nil {
			return fmt.Errorf("failed to get sheet '%v' column headers: %w", sheetName, err)
		}

		columnIndices := listToMap(columnHeaders)

		hashColumnIndex, found := columnIndices[columnGitHash]
		if !found {
			return fmt.Errorf("failed to find sheet '%v' column header '%v': %w", sheetName, columnGitHash, err)
		}

		hashValues, err := fetchColumn(srv, spreadsheet, sheet, hashColumnIndex)
		if err != nil {
			return fmt.Errorf("failed to get sheet '%v' column headers: %w", sheetName, err)
		}
		hashValues = hashValues[1:] // Skip header

		hashIndices := listToMap(hashValues)
		rowValues := map[string]interface{}{}

		rowInsertionPoint := 1 + len(hashValues)

		for i := len(changes) - 1; i >= 0; i-- {
			change := changes[i]
			if !strings.HasPrefix(change.Subject, consts.TestListUpdateCommitSubjectPrefix) {
				continue
			}

			hash := change.Hash.String()
			if _, found := hashIndices[hash]; found {
				continue // Already in the sheet
			}

			rowValues[columnGitHash] = change.Hash.String()
			rowValues[columnGitDate] = change.Date.Format("2006-01-02")

			path := filepath.Join(*projectPath, group.File)
			hasData := false
			for _, status := range testlist.Statuses {
				path := testlist.FilePathWithStatus(path, status)
				data, err := git.Show(path, hash)
				if err != nil {
					continue
				}
				lines, err := countLines(data)
				if err != nil {
					return fmt.Errorf("failed to count lines in file '%s': %w", path, err)
				}

				rowValues[string(status)] = lines
				hasData = true
			}

			if !hasData {
				continue
			}

			data, err := mapToList(columnIndices, rowValues)
			if err != nil {
				return fmt.Errorf("failed to map row values to column for sheet %v. Column headers: [%+v]: %w", sheetName, columnHeaders, err)
			}

			req.Data = append(req.Data, &sheets.ValueRange{
				Range:  rowRange(rowInsertionPoint, sheet),
				Values: [][]interface{}{data},
			})
			rowInsertionPoint++

			fmt.Printf("Adding test data at %v to %v\n", hash[:8], sheetName)
		}
	}

	if _, err := srv.Spreadsheets.Values.BatchUpdate(*spreadsheetID, &req).Do(); err != nil {
		return fmt.Errorf("Values.BatchUpdate() failed: %w", err)
	}

	return nil
}

// listToMap returns the list l as a map where the key is the stringification
// of the element, and the value is the element index.
func listToMap(l []interface{}) map[string]int {
	out := map[string]int{}
	for i, v := range l {
		out[fmt.Sprint(v)] = i
	}
	return out
}

// mapToList transforms the two maps into a single slice of values.
// indices is a map of identifier to output slice element index.
// values is a map of identifier to value.
func mapToList(indices map[string]int, values map[string]interface{}) ([]interface{}, error) {
	out := []interface{}{}
	for name, value := range values {
		index, ok := indices[name]
		if !ok {
			return nil, fmt.Errorf("No index for '%v'", name)
		}
		for len(out) <= index {
			out = append(out, nil)
		}
		out[index] = value
	}
	return out, nil
}

// countLines returns the number of new lines in the byte slice data.
func countLines(data []byte) (int, error) {
	scanner := bufio.NewScanner(bytes.NewReader(data))
	lines := 0
	for scanner.Scan() {
		lines++
	}
	return lines, nil
}

// getSheet returns the sheet with the given title name, or nil if the sheet
// cannot be found.
func getSheet(spreadsheet *sheets.Spreadsheet, name string) *sheets.Sheet {
	for _, sheet := range spreadsheet.Sheets {
		if sheet.Properties.Title == name {
			return sheet
		}
	}
	return nil
}

// rowRange returns a sheets range ("name!Ai:i") for the entire row with the
// given index.
func rowRange(index int, sheet *sheets.Sheet) string {
	return fmt.Sprintf("%v!A%v:%v", sheet.Properties.Title, index+1, index+1)
}

// columnRange returns a sheets range ("name!i1:i") for the entire column with
// the given index.
func columnRange(index int, sheet *sheets.Sheet) string {
	col := 'A' + index
	if index > 25 {
		panic("UNIMPLEMENTED")
	}
	return fmt.Sprintf("%v!%c1:%c", sheet.Properties.Title, col, col)
}

// fetchRow returns all the values in the given sheet's row.
func fetchRow(srv *sheets.Service, spreadsheet *sheets.Spreadsheet, sheet *sheets.Sheet, row int) ([]interface{}, error) {
	rng := rowRange(row, sheet)
	data, err := srv.Spreadsheets.Values.Get(spreadsheet.SpreadsheetId, rng).Do()
	if err != nil {
		return nil, fmt.Errorf("failed to fetch %v: %w", rng, err)
	}
	return data.Values[0], nil
}

// fetchColumn returns all the values in the given sheet's column.
func fetchColumn(srv *sheets.Service, spreadsheet *sheets.Spreadsheet, sheet *sheets.Sheet, row int) ([]interface{}, error) {
	rng := columnRange(row, sheet)
	data, err := srv.Spreadsheets.Values.Get(spreadsheet.SpreadsheetId, rng).Do()
	if err != nil {
		return nil, fmt.Errorf("failed to fetch %v: %w", rng, err)
	}
	out := make([]interface{}, len(data.Values))
	for i, l := range data.Values {
		if len(l) > 0 {
			out[i] = l[0]
		}
	}
	return out, nil
}

// insertRows inserts blank rows into the given sheet.
func insertRows(srv *sheets.Service, spreadsheet *sheets.Spreadsheet, sheet *sheets.Sheet, aboveRow, count int) error {
	req := sheets.BatchUpdateSpreadsheetRequest{
		Requests: []*sheets.Request{{
			InsertRange: &sheets.InsertRangeRequest{
				Range: &sheets.GridRange{
					SheetId:       sheet.Properties.SheetId,
					StartRowIndex: int64(aboveRow),
					EndRowIndex:   int64(aboveRow + count),
				},
				ShiftDimension: "ROWS",
			}},
		},
	}
	if _, err := srv.Spreadsheets.BatchUpdate(*spreadsheetID, &req).Do(); err != nil {
		return fmt.Errorf("Spreadsheets.BatchUpdate() failed: %w", err)
	}
	return nil
}

// createTestListSheets adds a new sheet for each of the test lists, if they
// do not already exist. These new sheets are populated with column headers.
func createTestListSheets(srv *sheets.Service, testlists testlist.Lists) error {
	spreadsheet, err := srv.Spreadsheets.Get(*spreadsheetID).Do()
	if err != nil {
		return fmt.Errorf("failed to get spreadsheet: %w", err)
	}

	spreadsheetReq := sheets.BatchUpdateSpreadsheetRequest{}
	updateReq := sheets.BatchUpdateValuesRequest{ValueInputOption: "RAW"}
	headers := []interface{}{columnGitHash, columnGitDate}
	for _, s := range testlist.Statuses {
		headers = append(headers, string(s))
	}

	for _, group := range testlists {
		name := group.Name
		if getSheet(spreadsheet, name) == nil {
			spreadsheetReq.Requests = append(spreadsheetReq.Requests, &sheets.Request{
				AddSheet: &sheets.AddSheetRequest{
					Properties: &sheets.SheetProperties{
						Title: name,
					},
				},
			})
			updateReq.Data = append(updateReq.Data,
				&sheets.ValueRange{
					Range:  name + "!A1:Z",
					Values: [][]interface{}{headers},
				},
			)
		}
	}

	if len(spreadsheetReq.Requests) > 0 {
		if _, err := srv.Spreadsheets.BatchUpdate(*spreadsheetID, &spreadsheetReq).Do(); err != nil {
			return fmt.Errorf("Spreadsheets.BatchUpdate() failed: %w", err)
		}
	}
	if len(updateReq.Data) > 0 {
		if _, err := srv.Spreadsheets.Values.BatchUpdate(*spreadsheetID, &updateReq).Do(); err != nil {
			return fmt.Errorf("Values.BatchUpdate() failed: %w", err)
		}
	}

	return nil
}

// createSheetsService creates a new Google Sheets service using the credentials
// in the credentials.json file.
func createSheetsService(authdir string) (*sheets.Service, error) {
	authdir = os.ExpandEnv(authdir)
	if home, err := os.UserHomeDir(); err == nil {
		authdir = strings.ReplaceAll(authdir, "~", home)
	}

	os.MkdirAll(authdir, 0777)

	credentialsPath := filepath.Join(authdir, "credentials.json")
	b, err := ioutil.ReadFile(credentialsPath)
	if err != nil {
		return nil, fmt.Errorf("Unable to read client secret file '%v'\n"+
			"Obtain this file from: https://console.developers.google.com/apis/credentials", credentialsPath)
	}

	config, err := google.ConfigFromJSON(b, "https://www.googleapis.com/auth/spreadsheets")
	if err != nil {
		return nil, fmt.Errorf("failed to parse client secret file to config: %w", err)
	}

	client, err := getClient(authdir, config)
	if err != nil {
		return nil, fmt.Errorf("failed to obtain client: %w", err)
	}

	srv, err := sheets.New(client)
	if err != nil {
		return nil, fmt.Errorf("failed to to retrieve Sheets client: %w", err)
	}
	return srv, nil
}

// Retrieve a token, saves the token, then returns the generated client.
func getClient(authdir string, config *oauth2.Config) (*http.Client, error) {
	// The file token.json stores the user's access and refresh tokens, and is
	// created automatically when the authorization flow completes for the first
	// time.
	tokFile := filepath.Join(authdir, "token.json")
	tok, err := tokenFromFile(tokFile)
	if err != nil {
		tok, err = getTokenFromWeb(config)
		if err != nil {
			return nil, fmt.Errorf("failed to get token from web: %w", err)
		}
		if err := saveToken(tokFile, tok); err != nil {
			log.Printf("Warning: failed to write token: %v", err)
		}
	}
	return config.Client(context.Background(), tok), nil
}

// Request a token from the web, then returns the retrieved token.
func getTokenFromWeb(config *oauth2.Config) (*oauth2.Token, error) {
	authURL := config.AuthCodeURL("state-token", oauth2.AccessTypeOffline)
	fmt.Printf("Go to the following link in your browser then type the "+
		"authorization code: \n%v\n", authURL)

	var authCode string
	if _, err := fmt.Scan(&authCode); err != nil {
		return nil, fmt.Errorf("failed to read authorization code: %w", err)
	}

	tok, err := config.Exchange(context.TODO(), authCode)
	if err != nil {
		return nil, fmt.Errorf("failed to retrieve token from web: %w", err)
	}
	return tok, nil
}

// Retrieves a token from a local file.
func tokenFromFile(path string) (*oauth2.Token, error) {
	f, err := os.Open(path)
	if err != nil {
		return nil, err
	}
	defer f.Close()
	tok := &oauth2.Token{}
	err = json.NewDecoder(f).Decode(tok)
	return tok, err
}

// Saves a token to a file path.
func saveToken(path string, token *oauth2.Token) error {
	fmt.Printf("Saving credential file to: %s\n", path)
	f, err := os.OpenFile(path, os.O_RDWR|os.O_CREATE|os.O_TRUNC, 0600)
	if err != nil {
		return fmt.Errorf("failed to cache oauth token: %w", err)
	}
	defer f.Close()
	json.NewEncoder(f).Encode(token)
	return nil
}