<commit_before>package main

import (
	"crypto/x509"
	"encoding/base64"
	"fmt"
	"log"
	"os"
	"strconv"

	"github.com/mozilla/tls-observatory/certificate"
	"github.com/mozilla/tls-observatory/database"
)

func main() {
	db, err := database.RegisterConnection(
		os.Getenv("TLSOBS_POSTGRESDB"),
		os.Getenv("TLSOBS_POSTGRESUSER"),
		os.Getenv("TLSOBS_POSTGRESPASS"),
		os.Getenv("TLSOBS_POSTGRES"),
		"require")
	defer db.Close()
	if err != nil {
		panic(err)
	}
	offset := 0
	limit := 100
	if len(os.Args) > 1 {
		offset, err = strconv.Atoi(os.Args[1])
		if err != nil {
			log.Fatal(err)
		}
	}
	for {
		fmt.Printf("\nProcessing offset %d to %d: ", offset, offset+limit)
		rows, err := db.Query(`SELECT id, raw_cert
					FROM certificates
					WHERE id > $1
					ORDER BY id ASC LIMIT $2`, offset, limit)
		if rows != nil {
			defer rows.Close()
		}
		if err != nil {
			panic(fmt.Errorf("Error while retrieving certs: '%v'", err))
		}
		i := 0
		for rows.Next() {
			i++
			var raw string
			var id int64
			err = rows.Scan(&id, &raw)
			if err != nil {
				fmt.Println("error while parsing cert", id, ":", err)
				continue
			}
			certdata, err := base64.StdEncoding.DecodeString(raw)
			if err != nil {
				fmt.Println("error decoding base64 of cert", id, ":", err)
				continue
			}
			c, err := x509.ParseCertificate(certdata)
			if err != nil {
				fmt.Println("error while x509 parsing cert", id, ":", err)
				continue
			}
			_, err = db.Exec(`UPDATE certificates SET sha256_subject_spki=$1 WHERE id=$2`,
				certificate.SHA256SubjectSPKI(c), id)
			if err != nil {
				fmt.Println("error while updating cert", id, "in database:", err)
			}
		}
		if i == 0 {
			fmt.Println("done!")
			break
		}
		//offset += limit
		offset += limit
	}
}<commit_msg>tooling: use batch updates in fix SPKI script<commit_after>package main

import (
	"crypto/x509"
	"encoding/base64"
	"fmt"
	"io/ioutil"
	"log"
	"os"
	"strconv"

	"github.com/mozilla/tls-observatory/certificate"
	"github.com/mozilla/tls-observatory/database"
)

func main() {
	db, err := database.RegisterConnection(
		os.Getenv("TLSOBS_POSTGRESDB"),
		os.Getenv("TLSOBS_POSTGRESUSER"),
		os.Getenv("TLSOBS_POSTGRESPASS"),
		os.Getenv("TLSOBS_POSTGRES"),
		"require")
	defer db.Close()
	if err != nil {
		panic(err)
	}
	offset := 0
	limit := 100
	if len(os.Args) > 1 {
		offset, err = strconv.Atoi(os.Args[1])
		if err != nil {
			log.Fatal(err)
		}
	}

	for {
		fmt.Printf("\nProcessing offset %d to %d: ", offset, offset+limit)
		rows, err := db.Query(`SELECT id, raw_cert
					FROM certificates
					WHERE id > $1
					ORDER BY id ASC LIMIT $2`, offset, limit)
		if rows != nil {
			defer rows.Close()
		}
		if err != nil {
			panic(fmt.Errorf("Error while retrieving certs: '%v'", err))
		}
		i := 0
		updates := make(map[int64]string)
		for rows.Next() {
			i++
			var raw string
			var id int64
			err = rows.Scan(&id, &raw)
			if err != nil {
				fmt.Println("error while parsing cert", id, ":", err)
				continue
			}
			certdata, err := base64.StdEncoding.DecodeString(raw)
			if err != nil {
				fmt.Println("error decoding base64 of cert", id, ":", err)
				continue
			}
			c, err := x509.ParseCertificate(certdata)
			if err != nil {
				fmt.Println("error while x509 parsing cert", id, ":", err)
				continue
			}
			updates[id] = certificate.SPKISHA256(c)
		}
		if i == 0 {
			fmt.Println("done!")
			break
		}
		// batch update
		sql := "UPDATE certificates SET sha256_subject_spki = newvalues.spki FROM ( VALUES "
		first := true
		for id, spki := range updates {
			if !first {
				sql += ","
			}
			sql += fmt.Sprintf("(%d, '%s')", id, spki)
			first = false
		}
		sql += ") AS newvalues (id, spki) WHERE certificates.id = newvalues.id"
		_, err = db.Exec(sql)
		if err != nil {
			fmt.Printf("error while updating certificates in database: %v\nSQL statement was:\n%s", err, sql)
		}
		offset += limit
		ioutil.WriteFile("/tmp/fixSHA256SubjectSPKI_offset", []byte(fmt.Sprintf("%d", offset)), 0700)
	}
}
{'fraction_non_alphanumeric': 0.15699572971615172, 'fraction_numerical': 0.019341873901029892, 'mean_word_length': 3.1349948078920042, 'pattern_counts': {'":': 6, '<': 3, '<?xml version=': 0, '>': 7, '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': '6175744', 'n_tokens_mistral': 1767, 'n_tokens_neox': 1482, 'n_words': 369}