<commit_before>// Copyright 2014 ISRG.  All rights reserved
// This Source Code Form is subject to the terms of the Mozilla Public
// License, v. 2.0. If a copy of the MPL was not distributed with this
// file, You can obtain one at http://mozilla.org/MPL/2.0/.

package sa

import (
	"crypto/sha256"
	"crypto/x509"
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"time"

	gorp "github.com/letsencrypt/boulder/Godeps/_workspace/src/gopkg.in/gorp.v1"

	"github.com/letsencrypt/boulder/core"
	"github.com/letsencrypt/boulder/jose"
	blog "github.com/letsencrypt/boulder/log"
)

type SQLStorageAuthority struct {
	dbMap  *gorp.DbMap
	bucket map[string]interface{} // XXX included only for backward compat
	log    *blog.AuditLogger
}

func digest256(data []byte) []byte {
	d := sha256.New()
	_, _ = d.Write(data) // Never returns an error
	return d.Sum(nil)
}

var dialectMap map[string]interface{} = map[string]interface{}{
	"sqlite3":  gorp.SqliteDialect{},
	"mysql":    gorp.MySQLDialect{Engine: "InnoDB", Encoding: "UTF8"},
	"postgres": gorp.PostgresDialect{},
}

// Models
type Registration struct {
	Thumbprint        string `db:"thumbprint"`
	core.Registration

	Version int64 // Lock column
}

type Pending_auth struct {
	core.Authorization

	Version int64 // Lock column
}

type Auth struct {
	Sequence           int64 `db:"sequence"`
	Digest             string `db:"digest"`
	core.Authorization
}

type Certificate struct {
	Serial   string `db:"serial"`
	Digest   string `db:"digest"`
	Content  []byte `db:"content"`
	Issued   time.Time `db:"issued"`
}

type CertificateStatus struct {
	Serial                 string `db:"serial"`
	RevokedDate            time.Time `db:"revokedDate"`
	RevokedReason          int `db:"revokedReason"`
	core.CertificateStatus

	Version int64 // Lock column
}

type OcspResponse struct {
	ID        int `db:"id"`
	Serial    string `db:"serial"`
	CreatedAt time.Time `db:"createdAt"`
	Response  []byte `db:"response"`
}

type Crl struct {
	Serial    string `db:"serial"`
	CreatedAt time.Time `db:"createdAt"`
	Crl       string `db:"crl"`
}

// Type converter
type boulderTypeConverter struct{}

func (tc boulderTypeConverter) ToDb(val interface{}) (interface{}, error) {
	switch t := val.(type) {
	case core.AcmeIdentifier, jose.JsonWebKey, []core.Challenge, []core.AcmeURL, [][]int:
		jsonBytes, err := json.Marshal(t)
		if err != nil {
			return nil, err
		}
		return string(jsonBytes), nil
	case core.AcmeStatus:
		return string(t), nil
	case core.OCSPStatus:
		return string(t), nil
	default:
		return val, nil
	}
}

func (tc boulderTypeConverter) FromDb(target interface{}) (gorp.CustomScanner, bool) {
	switch target.(type) {
	case *core.AcmeIdentifier, *jose.JsonWebKey, *[]core.Challenge, *[]core.AcmeURL, *[][]int:
		binder := func(holder, target interface{}) error {
			s, ok := holder.(*string)
			if !ok {
				return errors.New("FromDb: Unable to convert *string")
			}
			b := []byte(*s)
			return json.Unmarshal(b, target)
		}
		return gorp.CustomScanner{Holder: new(string), Target: target, Binder: binder}, true
	case *core.AcmeStatus:
		binder := func(holder, target interface{}) error {
			s := holder.(*string)
			st := target.(*core.AcmeStatus)
			*st = core.AcmeStatus(*s)
			return nil
		}
		return gorp.CustomScanner{Holder: new(string), Target: target, Binder: binder}, true
	case *core.OCSPStatus:
		binder := func(holder, target interface{}) error {
			s := holder.(*string)
			st := target.(*core.OCSPStatus)
			*st = core.OCSPStatus(*s)
			return nil
		}
		return gorp.CustomScanner{Holder: new(string), Target: target, Binder: binder}, true
	default:
		return gorp.CustomScanner{}, false
	}
}

func NewSQLStorageAuthority(driver string, name string) (ssa *SQLStorageAuthority, err error) {
	logger := blog.GetAuditLogger()
	logger.Notice("Storage Authority Starting")

	db, err := sql.Open(driver, name)
	if err != nil {
		return
	}
	if err = db.Ping(); err != nil {
		return
	}

	dialect, ok := dialectMap[driver].(gorp.Dialect)
	if !ok {
		err = fmt.Errorf("Couldn't find dialect for %s", driver)
		return
	}

	dbmap := &gorp.DbMap{Db: db, Dialect: dialect, TypeConverter: boulderTypeConverter{}}

	ssa = &SQLStorageAuthority{
		dbMap:  dbmap,
		log:    logger,
		bucket: make(map[string]interface{}),
	}

	err = ssa.InitTables()
	if err != nil {
		return
	}

	return
}

func (ssa *SQLStorageAuthority) InitTables() (err error) {
	ssa.dbMap.AddTableWithName(Registration{}, "registrations").SetKeys(false, "ID")
	ssa.dbMap.AddTableWithName(Pending_auth{}, "pending_authz").SetKeys(false, "ID")
	ssa.dbMap.AddTableWithName(Auth{}, "authz").SetKeys(false, "ID")
	ssa.dbMap.AddTableWithName(Certificate{}, "certificates").SetKeys(false, "Serial")
	ssa.dbMap.AddTableWithName(CertificateStatus{}, "certificateStatus").SetKeys(false, "Serial")
	ssa.dbMap.AddTableWithName(OcspResponse{}, "ocspResponses").SetKeys(true, "ID")
	ssa.dbMap.AddTableWithName(Crl{}, "crls").SetKeys(false, "CreatedAt")

	err = ssa.dbMap.CreateTablesIfNotExists()
	return
}

func (ssa *SQLStorageAuthority) DumpTables() {
	fmt.Printf("===== TABLE DUMP =====\n")

	fmt.Printf("\n----- registrations -----\n")
	var registrations []Registration
	_, err := ssa.dbMap.Select(&registrations, "SELECT * FROM registrations ")<commit_msg>v2 compatible lock column setting<commit_after>// Copyright 2014 ISRG.  All rights reserved
// This Source Code Form is subject to the terms of the Mozilla Public
// License, v. 2.0. If a copy of the MPL was not distributed with this
// file, You can obtain one at http://mozilla.org/MPL/2.0/.

package sa

import (
	"crypto/sha256"
	"crypto/x509"
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"time"

	gorp "github.com/letsencrypt/boulder/Godeps/_workspace/src/gopkg.in/gorp.v1"

	"github.com/letsencrypt/boulder/core"
	"github.com/letsencrypt/boulder/jose"
	blog "github.com/letsencrypt/boulder/log"
)

type SQLStorageAuthority struct {
	dbMap  *gorp.DbMap
	bucket map[string]interface{} // XXX included only for backward compat
	log    *blog.AuditLogger
}

func digest256(data []byte) []byte {
	d := sha256.New()
	_, _ = d.Write(data) // Never returns an error
	return d.Sum(nil)
}

var dialectMap map[string]interface{} = map[string]interface{}{
	"sqlite3":  gorp.SqliteDialect{},
	"mysql":    gorp.MySQLDialect{Engine: "InnoDB", Encoding: "UTF8"},
	"postgres": gorp.PostgresDialect{},
}

// Models
type Registration struct {
	Thumbprint        string `db:"thumbprint"`
	core.Registration

	LockCol int64
}

type Pending_auth struct {
	core.Authorization

	LockCol int64
}

type Auth struct {
	Sequence           int64 `db:"sequence"`
	Digest             string `db:"digest"`
	core.Authorization
}

type Certificate struct {
	Serial   string `db:"serial"`
	Digest   string `db:"digest"`
	Content  []byte `db:"content"`
	Issued   time.Time `db:"issued"`
}

type CertificateStatus struct {
	Serial                 string `db:"serial"`
	RevokedDate            time.Time `db:"revokedDate"`
	RevokedReason          int `db:"revokedReason"`
	core.CertificateStatus

	LockCol int64
}

type OcspResponse struct {
	ID        int `db:"id"`
	Serial    string `db:"serial"`
	CreatedAt time.Time `db:"createdAt"`
	Response  []byte `db:"response"`
}

type Crl struct {
	Serial    string `db:"serial"`
	CreatedAt time.Time `db:"createdAt"`
	Crl       string `db:"crl"`
}

// Type converter
type boulderTypeConverter struct{}

func (tc boulderTypeConverter) ToDb(val interface{}) (interface{}, error) {
	switch t := val.(type) {
	case core.AcmeIdentifier, jose.JsonWebKey, []core.Challenge, []core.AcmeURL, [][]int:
		jsonBytes, err := json.Marshal(t)
		if err != nil {
			return nil, err
		}
		return string(jsonBytes), nil
	case core.AcmeStatus:
		return string(t), nil
	case core.OCSPStatus:
		return string(t), nil
	default:
		return val, nil
	}
}

func (tc boulderTypeConverter) FromDb(target interface{}) (gorp.CustomScanner, bool) {
	switch target.(type) {
	case *core.AcmeIdentifier, *jose.JsonWebKey, *[]core.Challenge, *[]core.AcmeURL, *[][]int:
		binder := func(holder, target interface{}) error {
			s, ok := holder.(*string)
			if !ok {
				return errors.New("FromDb: Unable to convert *string")
			}
			b := []byte(*s)
			return json.Unmarshal(b, target)
		}
		return gorp.CustomScanner{Holder: new(string), Target: target, Binder: binder}, true
	case *core.AcmeStatus:
		binder := func(holder, target interface{}) error {
			s := holder.(*string)
			st := target.(*core.AcmeStatus)
			*st = core.AcmeStatus(*s)
			return nil
		}
		return gorp.CustomScanner{Holder: new(string), Target: target, Binder: binder}, true
	case *core.OCSPStatus:
		binder := func(holder, target interface{}) error {
			s := holder.(*string)
			st := target.(*core.OCSPStatus)
			*st = core.OCSPStatus(*s)
			return nil
		}
		return gorp.CustomScanner{Holder: new(string), Target: target, Binder: binder}, true
	default:
		return gorp.CustomScanner{}, false
	}
}

func NewSQLStorageAuthority(driver string, name string) (ssa *SQLStorageAuthority, err error) {
	logger := blog.GetAuditLogger()
	logger.Notice("Storage Authority Starting")

	db, err := sql.Open(driver, name)
	if err != nil {
		return
	}
	if err = db.Ping(); err != nil {
		return
	}

	dialect, ok := dialectMap[driver].(gorp.Dialect)
	if !ok {
		err = fmt.Errorf("Couldn't find dialect for %s", driver)
		return
	}

	dbmap := &gorp.DbMap{Db: db, Dialect: dialect, TypeConverter: boulderTypeConverter{}}

	ssa = &SQLStorageAuthority{
		dbMap:  dbmap,
		log:    logger,
		bucket: make(map[string]interface{}),
	}

	err = ssa.InitTables()
	if err != nil {
		return
	}

	return
}

func (ssa *SQLStorageAuthority) InitTables() (err error) {
	ssa.dbMap.AddTableWithName(Registration{}, "registrations").SetKeys(false, "ID").SetVersionCol("LockCol")
	ssa.dbMap.AddTableWithName(Pending_auth{}, "pending_authz").SetKeys(false, "ID").SetVersionCol("LockCol")
	ssa.dbMap.AddTableWithName(Auth{}, "authz").SetKeys(false, "ID")
	ssa.dbMap.AddTableWithName(Certificate{}, "certificates").SetKeys(false, "Serial")
	ssa.dbMap.AddTableWithName(CertificateStatus{}, "certificateStatus").SetKeys(false, "Serial").SetVersionCol("LockCol")
	ssa.dbMap.AddTableWithName(OcspResponse{}, "ocspResponses").SetKeys(true, "ID")
	ssa.dbMap.AddTableWithName(Crl{}, "crls").SetKeys(false, "CreatedAt")

	err = ssa.dbMap.CreateTablesIfNotExists()
	return
}

func (ssa *SQLStorageAuthority) DumpTables() {
	fmt.Printf("===== TABLE DUMP =====\n")

	fmt.Printf("\n----- registrations -----\n")
	var registrations []Registration
	_, err := ssa.dbMap.Select(&registrations, "SELECT * FROM registrations ")
{'fraction_non_alphanumeric': 0.1483755194559879, 'fraction_numerical': 0.005950132225160559, 'mean_word_length': 4.255086848635235, 'pattern_counts': {'":': 6, '<': 3, '<?xml version=': 0, '>': 3, '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': '1424857', 'n_tokens_mistral': 3897, 'n_tokens_neox': 3666, 'n_words': 878}