func removeInvalidCerts(csvFilename string, dbMap *gorp.DbMap, stats metrics.Statter, statsRate float32) {
file, err := os.Open(csvFilename)
cmd.FailOnError(err, "Could not open the file for reading")
csvReader := csv.NewReader(file)
for {
record, err := csvReader.Read()
if err == io.EOF {
break
} else if err != nil {
fmt.Println("Error:", err)
return
}
identifierData := core.IdentifierData{
CertSHA1: record[0],
}
externalCert := core.ExternalCert{
SHA1: record[0],
}
deleteStart := time.Now()
_, err = dbMap.Delete(&identifierData)
stats.TimingDuration("ExistingCert.Domains.DeleteLatency", time.Since(deleteStart), statsRate)
_, err = dbMap.Delete(&externalCert)
stats.TimingDuration("ExistingCert.Certs.DeleteLatency", time.Since(deleteStart), statsRate)
stats.Inc("ExistingCert.Removed", 1, statsRate)
}
}
func addCerts(csvFilename string, dbMap *gorp.DbMap, stats metrics.Statter, statsRate float32) {
file, err := os.Open(csvFilename)
cmd.FailOnError(err, "Could not open the file for reading")
csvReader := csv.NewReader(file)
for {
record, err := csvReader.Read()
if err == io.EOF {
break
} else if err != nil {
fmt.Println("Error:", err)
return
}
notAfter, err := time.Parse(datestampFormat, record[3])
spkiBytes, err := hex.DecodeString(record[4])
certDER, err := hex.DecodeString(record[7])
externalCert := core.ExternalCert{
SHA1: record[0],
Issuer: record[1],
Subject: record[2],
NotAfter: notAfter,
SPKI: spkiBytes,
Valid: record[5] == "1",
EV: record[6] == "1",
CertDER: certDER,
}
importStart := time.Now()
err = dbMap.Insert(&externalCert)
stats.TimingDuration("ExistingCert.Certs.ImportLatency", time.Since(importStart), statsRate)
stats.Inc("ExistingCert.Certs.Imported", 1, statsRate)
}
}
func addIdentifiers(csvFilename string, dbMap *gorp.DbMap, stats metrics.Statter, statsRate float32) {
file, err := os.Open(csvFilename)
cmd.FailOnError(err, "Could not open the file for reading")
csvReader := csv.NewReader(file)
for {
record, err := csvReader.Read()
if err == io.EOF {
break
} else if err != nil {
fmt.Println("Error:", err)
return
}
identifierData := core.IdentifierData{
ReversedName: record[1],
CertSHA1: record[0],
}
importStart := time.Now()
err = dbMap.Insert(&identifierData)
stats.TimingDuration("ExistingCert.Domains.ImportLatency", time.Since(importStart), statsRate)
stats.Inc("ExistingCert.Domains.Imported", 1, statsRate)
}
}
func revokeAuthorizations(db *gorp.DbMap, tableName string, authIDs []string) (int64, error) {
stmtArgs := []interface{}{string(core.StatusRevoked)}
qmarks := []string{}
for _, id := range authIDs {
stmtArgs = append(stmtArgs, id)
qmarks = append(qmarks, "?")
}
idStmt := fmt.Sprintf("(%s)", strings.Join(qmarks, ", "))
result, err := db.Exec(
fmt.Sprintf(
`UPDATE %s
SET status = ?
WHERE id IN %s`,
tableName,
idStmt,
),
stmtArgs...,
)
if err != nil {
return 0, err
}
batchSize, err := result.RowsAffected()
if err != nil {
return 0, err
}
return batchSize, nil
}
func getAuthorizationIDsByDomain(db *gorp.DbMap, tableName string, ident string, now time.Time) ([]string, error) {
var allIDs []string
_, err := db.Select(
&allIDs,
fmt.Sprintf(
`SELECT id FROM %s
WHERE identifier = :ident AND
status != :invalid AND
status != :revoked AND
expires > :now
LIMIT :limit`,
tableName,
),
map[string]interface{}{
"ident": ident,
"invalid": string(core.StatusInvalid),
"revoked": string(core.StatusRevoked),
"now": now,
"limit": getAuthorizationIDsMax,
},
)
if err != nil {
return nil, err
}
return allIDs, nil
}
// initTables constructs the table map for the ORM.
// NOTE: For tables with an auto-increment primary key (SetKeys(true, ...)),
// it is very important to declare them as a such here. It produces a side
// effect in Insert() where the inserted object has its id field set to the
// autoincremented value that resulted from the insert. See
// https://godoc.org/github.com/coopernurse/gorp#DbMap.Insert
func initTables(dbMap *gorp.DbMap) {
var regTable *gorp.TableMap
if features.Enabled(features.AllowAccountDeactivation) {
regTable = dbMap.AddTableWithName(regModelv2{}, "registrations").SetKeys(true, "ID")
} else {
regTable = dbMap.AddTableWithName(regModelv1{}, "registrations").SetKeys(true, "ID")
}
regTable.SetVersionCol("LockCol")
regTable.ColMap("Key").SetNotNull(true)
regTable.ColMap("KeySHA256").SetNotNull(true).SetUnique(true)
pendingAuthzTable := dbMap.AddTableWithName(pendingauthzModel{}, "pendingAuthorizations").SetKeys(false, "ID")
pendingAuthzTable.SetVersionCol("LockCol")
dbMap.AddTableWithName(authzModel{}, "authz").SetKeys(false, "ID")
dbMap.AddTableWithName(challModel{}, "challenges").SetKeys(true, "ID").SetVersionCol("LockCol")
dbMap.AddTableWithName(issuedNameModel{}, "issuedNames").SetKeys(true, "ID")
dbMap.AddTableWithName(core.Certificate{}, "certificates").SetKeys(false, "Serial")
dbMap.AddTableWithName(core.CertificateStatus{}, "certificateStatus").SetKeys(false, "Serial").SetVersionCol("LockCol")
dbMap.AddTableWithName(core.CRL{}, "crls").SetKeys(false, "Serial")
dbMap.AddTableWithName(core.SignedCertificateTimestamp{}, "sctReceipts").SetKeys(true, "ID").SetVersionCol("LockCol")
dbMap.AddTableWithName(core.FQDNSet{}, "fqdnSets").SetKeys(true, "ID")
// TODO(@cpu): Delete these table maps when the `CertStatusOptimizationsMigrated` feature flag is removed
if features.Enabled(features.CertStatusOptimizationsMigrated) {
dbMap.AddTableWithName(certStatusModelv2{}, "certificateStatus").SetKeys(false, "Serial").SetVersionCol("LockCol")
} else {
dbMap.AddTableWithName(certStatusModelv1{}, "certificateStatus").SetKeys(false, "Serial").SetVersionCol("LockCol")
}
}
// SetSQLDebug enables GORP SQL-level Debugging
func SetSQLDebug(dbMap *gorp.DbMap, log blog.Logger) {
dbMap.TraceOn("SQL: ", &SQLLogger{log})
}
// initTables constructs the table map for the ORM.
// NOTE: For tables with an auto-increment primary key (SetKeys(true, ...)),
// it is very important to declare them as a such here. It produces a side
// effect in Insert() where the inserted object has its id field set to the
// autoincremented value that resulted from the insert. See
// https://godoc.org/github.com/coopernurse/gorp#DbMap.Insert
func initTables(dbMap *gorp.DbMap) {
regTable := dbMap.AddTableWithName(regModel{}, "registrations").SetKeys(true, "ID")
regTable.SetVersionCol("LockCol")
regTable.ColMap("Key").SetNotNull(true)
regTable.ColMap("KeySHA256").SetNotNull(true).SetUnique(true)
pendingAuthzTable := dbMap.AddTableWithName(pendingauthzModel{}, "pendingAuthorizations").SetKeys(false, "ID")
pendingAuthzTable.SetVersionCol("LockCol")
dbMap.AddTableWithName(authzModel{}, "authz").SetKeys(false, "ID")
dbMap.AddTableWithName(challModel{}, "challenges").SetKeys(true, "ID").SetVersionCol("LockCol")
dbMap.AddTableWithName(issuedNameModel{}, "issuedNames").SetKeys(true, "ID")
dbMap.AddTableWithName(core.Certificate{}, "certificates").SetKeys(false, "Serial")
dbMap.AddTableWithName(core.CertificateStatus{}, "certificateStatus").SetKeys(false, "Serial").SetVersionCol("LockCol")
dbMap.AddTableWithName(core.CRL{}, "crls").SetKeys(false, "Serial")
dbMap.AddTableWithName(core.DeniedCSR{}, "deniedCSRs").SetKeys(true, "ID")
dbMap.AddTableWithName(core.SignedCertificateTimestamp{}, "sctReceipts").SetKeys(true, "ID").SetVersionCol("LockCol")
dbMap.AddTableWithName(core.FQDNSet{}, "fqdnSets").SetKeys(true, "ID")
}