Sergiusz Bazanski | 1fad2e5 | 2019-08-01 20:16:27 +0200 | [diff] [blame] | 1 | package model |
| 2 | |
| 3 | import ( |
| 4 | "context" |
Serge Bazanski | ec71cb5 | 2019-08-22 18:13:13 +0200 | [diff] [blame] | 5 | "database/sql" |
Sergiusz Bazanski | 1fad2e5 | 2019-08-01 20:16:27 +0200 | [diff] [blame] | 6 | "fmt" |
Serge Bazanski | ec71cb5 | 2019-08-22 18:13:13 +0200 | [diff] [blame] | 7 | "strconv" |
Sergiusz Bazanski | 1fad2e5 | 2019-08-01 20:16:27 +0200 | [diff] [blame] | 8 | "time" |
| 9 | ) |
| 10 | |
| 11 | func (s *sqlModel) UpdatePGPKey(ctx context.Context, key *PeerPGPKey) error { |
| 12 | q := ` |
| 13 | INSERT INTO peer_pgp_keys |
Serge Bazanski | ec71cb5 | 2019-08-22 18:13:13 +0200 | [diff] [blame] | 14 | (peer_id, fingerprint, time_created, state) |
Sergiusz Bazanski | 1fad2e5 | 2019-08-01 20:16:27 +0200 | [diff] [blame] | 15 | SELECT |
Serge Bazanski | ec71cb5 | 2019-08-22 18:13:13 +0200 | [diff] [blame] | 16 | peers.id, :fingerprint, :time_created, 'unchecked' |
Sergiusz Bazanski | 1fad2e5 | 2019-08-01 20:16:27 +0200 | [diff] [blame] | 17 | FROM peers |
| 18 | WHERE peers.asn = :asn |
| 19 | ON CONFLICT (peer_id) |
| 20 | DO UPDATE SET |
| 21 | fingerprint = :fingerprint, |
Serge Bazanski | ec71cb5 | 2019-08-22 18:13:13 +0200 | [diff] [blame] | 22 | time_created = :time_created, |
| 23 | state = 'unchecked' |
| 24 | WHERE peer_pgp_keys.fingerprint != excluded.fingerprint |
Sergiusz Bazanski | 1fad2e5 | 2019-08-01 20:16:27 +0200 | [diff] [blame] | 25 | ` |
| 26 | data := &sqlPeerPGPKey{ |
| 27 | Fingerprint: key.Fingerprint, |
| 28 | ASN: fmt.Sprintf("%d", key.PeerASN), |
| 29 | TimeCreated: time.Now().UnixNano(), |
| 30 | } |
| 31 | if _, err := s.db.NamedExecContext(ctx, q, data); err != nil { |
| 32 | return fmt.Errorf("INSERT peer_pgp_keys: %v", err) |
| 33 | } |
| 34 | return nil |
| 35 | } |
Serge Bazanski | ec71cb5 | 2019-08-22 18:13:13 +0200 | [diff] [blame] | 36 | |
| 37 | func (s *sqlModel) GetPGPKeysRequiringAttention(ctx context.Context) ([]*PeerPGPKey, error) { |
| 38 | q := ` |
| 39 | SELECT |
| 40 | peer_pgp_keys.fingerprint "fingerprint", |
| 41 | peer_pgp_keys.state "state", |
| 42 | peers.asn "asn" |
| 43 | FROM peer_pgp_keys |
| 44 | LEFT JOIN peers |
| 45 | ON peers.id = peer_pgp_keys.peer_id |
| 46 | WHERE |
| 47 | peer_pgp_keys.state = 'unchecked' |
| 48 | OR |
| 49 | peer_pgp_keys.state = 'known' |
| 50 | OR ( |
| 51 | peer_pgp_keys.state = 'unknown' AND |
| 52 | peer_pgp_keys.time_created > $1 |
| 53 | ) |
| 54 | ` |
| 55 | |
| 56 | data := []sqlPeerPGPKey{} |
| 57 | timestamp := time.Now().Add(-time.Hour).UnixNano() |
| 58 | if err := s.db.SelectContext(ctx, &data, q, timestamp); err != nil { |
| 59 | return nil, fmt.Errorf("SELECT peer_pgp_keys: %v", err) |
| 60 | } |
| 61 | |
| 62 | res := make([]*PeerPGPKey, len(data)) |
| 63 | for i, datum := range data { |
| 64 | asn, err := strconv.ParseInt(datum.ASN, 10, 64) |
| 65 | if err != nil { |
| 66 | return nil, fmt.Errorf("data corruption: peer_pgp_keys as ASN %q", datum.ASN) |
| 67 | } |
| 68 | res[i] = &PeerPGPKey{ |
| 69 | Fingerprint: datum.Fingerprint, |
| 70 | State: datum.State, |
| 71 | PeerASN: asn, |
| 72 | } |
| 73 | } |
| 74 | |
| 75 | return res, nil |
| 76 | } |
| 77 | |
| 78 | func (s *sqlModel) ValidatePGPKeys(ctx context.Context, positive, negative []string) error { |
| 79 | tx := s.db.MustBeginTx(ctx, &sql.TxOptions{}) |
| 80 | defer tx.Rollback() |
| 81 | |
| 82 | timestamp := time.Now().UnixNano() |
| 83 | |
| 84 | for _, p := range positive { |
| 85 | q := ` |
| 86 | UPDATE |
| 87 | peer_pgp_keys |
| 88 | SET |
| 89 | state = 'known', |
| 90 | time_created = $2 |
| 91 | WHERE |
| 92 | fingerprint = $1 |
| 93 | ` |
| 94 | |
| 95 | if _, err := tx.ExecContext(ctx, q, p, timestamp); err != nil { |
| 96 | return fmt.Errorf("UPDATE peer_pgp_keys: %v", err) |
| 97 | } |
| 98 | } |
| 99 | |
| 100 | for _, n := range negative { |
| 101 | q := ` |
| 102 | UPDATE |
| 103 | peer_pgp_keys |
| 104 | SET |
| 105 | state = 'unknown', |
| 106 | time_created = $2 |
| 107 | WHERE |
| 108 | fingerprint = $1 |
| 109 | ` |
| 110 | |
| 111 | if _, err := tx.ExecContext(ctx, q, n, timestamp); err != nil { |
| 112 | return fmt.Errorf("UPDATE peer_pgp_keys: %v", err) |
| 113 | } |
| 114 | } |
| 115 | |
| 116 | return tx.Commit() |
| 117 | } |
| 118 | |
| 119 | func (s *sqlModel) GetPeerPGPKey(ctx context.Context, asn int64) (*PeerPGPKey, error) { |
| 120 | q := ` |
| 121 | SELECT peer_pgp_keys.fingerprint |
| 122 | FROM peer_pgp_keys |
| 123 | LEFT JOIN peers |
| 124 | ON peers.id = peer_pgp_keys.peer_id |
| 125 | WHERE peers.asn = $1 |
| 126 | ` |
| 127 | data := []*PeerPGPKey{} |
| 128 | if err := s.db.SelectContext(ctx, &data, q, asn); err != nil { |
| 129 | return nil, fmt.Errorf("SELECT peer_pgp_keys: %v", err) |
| 130 | } |
| 131 | |
| 132 | if len(data) != 1 { |
| 133 | return nil, fmt.Errorf("wrong number of peer_pgp_keys (%d)", len(data)) |
| 134 | } |
| 135 | |
| 136 | return data[0], nil |
| 137 | } |