| package model |
| |
| import ( |
| "context" |
| "database/sql" |
| "fmt" |
| "strconv" |
| "time" |
| ) |
| |
| func (s *sqlModel) UpdatePGPKey(ctx context.Context, key *PeerPGPKey) error { |
| q := ` |
| INSERT INTO peer_pgp_keys |
| (peer_id, fingerprint, time_created, state) |
| SELECT |
| peers.id, :fingerprint, :time_created, 'unchecked' |
| FROM peers |
| WHERE peers.asn = :asn |
| ON CONFLICT (peer_id) |
| DO UPDATE SET |
| fingerprint = :fingerprint, |
| time_created = :time_created, |
| state = 'unchecked' |
| WHERE peer_pgp_keys.fingerprint != excluded.fingerprint |
| ` |
| data := &sqlPeerPGPKey{ |
| Fingerprint: key.Fingerprint, |
| ASN: fmt.Sprintf("%d", key.PeerASN), |
| TimeCreated: time.Now().UnixNano(), |
| } |
| if _, err := s.db.NamedExecContext(ctx, q, data); err != nil { |
| return fmt.Errorf("INSERT peer_pgp_keys: %v", err) |
| } |
| return nil |
| } |
| |
| func (s *sqlModel) GetPGPKeysRequiringAttention(ctx context.Context) ([]*PeerPGPKey, error) { |
| q := ` |
| SELECT |
| peer_pgp_keys.fingerprint "fingerprint", |
| peer_pgp_keys.state "state", |
| peers.asn "asn" |
| FROM peer_pgp_keys |
| LEFT JOIN peers |
| ON peers.id = peer_pgp_keys.peer_id |
| WHERE |
| peer_pgp_keys.state = 'unchecked' |
| OR |
| peer_pgp_keys.state = 'known' |
| OR ( |
| peer_pgp_keys.state = 'unknown' AND |
| peer_pgp_keys.time_created > $1 |
| ) |
| ` |
| |
| data := []sqlPeerPGPKey{} |
| timestamp := time.Now().Add(-time.Hour).UnixNano() |
| if err := s.db.SelectContext(ctx, &data, q, timestamp); err != nil { |
| return nil, fmt.Errorf("SELECT peer_pgp_keys: %v", err) |
| } |
| |
| res := make([]*PeerPGPKey, len(data)) |
| for i, datum := range data { |
| asn, err := strconv.ParseInt(datum.ASN, 10, 64) |
| if err != nil { |
| return nil, fmt.Errorf("data corruption: peer_pgp_keys as ASN %q", datum.ASN) |
| } |
| res[i] = &PeerPGPKey{ |
| Fingerprint: datum.Fingerprint, |
| State: datum.State, |
| PeerASN: asn, |
| } |
| } |
| |
| return res, nil |
| } |
| |
| func (s *sqlModel) ValidatePGPKeys(ctx context.Context, positive, negative []string) error { |
| tx := s.db.MustBeginTx(ctx, &sql.TxOptions{}) |
| defer tx.Rollback() |
| |
| timestamp := time.Now().UnixNano() |
| |
| for _, p := range positive { |
| q := ` |
| UPDATE |
| peer_pgp_keys |
| SET |
| state = 'known', |
| time_created = $2 |
| WHERE |
| fingerprint = $1 |
| ` |
| |
| if _, err := tx.ExecContext(ctx, q, p, timestamp); err != nil { |
| return fmt.Errorf("UPDATE peer_pgp_keys: %v", err) |
| } |
| } |
| |
| for _, n := range negative { |
| q := ` |
| UPDATE |
| peer_pgp_keys |
| SET |
| state = 'unknown', |
| time_created = $2 |
| WHERE |
| fingerprint = $1 |
| ` |
| |
| if _, err := tx.ExecContext(ctx, q, n, timestamp); err != nil { |
| return fmt.Errorf("UPDATE peer_pgp_keys: %v", err) |
| } |
| } |
| |
| return tx.Commit() |
| } |
| |
| func (s *sqlModel) GetPeerPGPKey(ctx context.Context, asn int64) (*PeerPGPKey, error) { |
| q := ` |
| SELECT peer_pgp_keys.fingerprint |
| FROM peer_pgp_keys |
| LEFT JOIN peers |
| ON peers.id = peer_pgp_keys.peer_id |
| WHERE peers.asn = $1 |
| ` |
| data := []*PeerPGPKey{} |
| if err := s.db.SelectContext(ctx, &data, q, asn); err != nil { |
| return nil, fmt.Errorf("SELECT peer_pgp_keys: %v", err) |
| } |
| |
| if len(data) != 1 { |
| return nil, fmt.Errorf("wrong number of peer_pgp_keys (%d)", len(data)) |
| } |
| |
| return data[0], nil |
| } |