Golang v1.DbMap类代码示例

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")
}