Sergiusz Bazanski | 1fad2e5 | 2019-08-01 20:16:27 +0200 | [diff] [blame] | 1 | package model |
| 2 | |
| 3 | import ( |
| 4 | "context" |
| 5 | "database/sql" |
| 6 | "fmt" |
| 7 | "strconv" |
| 8 | "strings" |
| 9 | |
| 10 | pb "code.hackerspace.pl/hscloud/bgpwtf/cccampix/proto" |
| 11 | "github.com/golang/glog" |
| 12 | "github.com/jmoiron/sqlx" |
| 13 | ) |
| 14 | |
| 15 | func (m *sqlModel) RecordPeeringDBPeers(ctx context.Context, members []*pb.PeeringDBMember) error { |
| 16 | tx := m.db.MustBeginTx(ctx, &sql.TxOptions{}) |
| 17 | defer tx.Rollback() |
| 18 | |
| 19 | wanted := make(map[string]*pb.PeeringDBMember) |
| 20 | for _, member := range members { |
| 21 | wanted[fmt.Sprintf("%d", member.Asn)] = member |
| 22 | } |
| 23 | |
| 24 | toDelete := make(map[string]bool) |
| 25 | toAdd := make(map[string]bool) |
| 26 | toUpdate := make(map[string]bool) |
| 27 | |
| 28 | existing := []sqlPeer{} |
| 29 | existingMap := make(map[string]*sqlPeer) |
| 30 | |
| 31 | q := ` |
| 32 | SELECT peers.id, peers.asn, peers.name, peers.source |
| 33 | FROM peers |
| 34 | ` |
| 35 | if err := tx.SelectContext(ctx, &existing, q); err != nil { |
| 36 | return fmt.Errorf("SELECT peers: %v", err) |
| 37 | } |
| 38 | |
| 39 | // Mark ASs to delete and note existing ASs |
| 40 | for _, ex := range existing { |
| 41 | ex := ex |
| 42 | if wanted[ex.ASN] == nil && ex.Source == "from-peeringdb" { |
| 43 | toDelete[ex.ASN] = true |
| 44 | } |
| 45 | existingMap[ex.ASN] = &ex |
| 46 | } |
| 47 | |
| 48 | // Mark ASs to add |
| 49 | for k, _ := range wanted { |
| 50 | if existingMap[k] == nil { |
| 51 | toAdd[k] = true |
| 52 | } |
| 53 | } |
| 54 | |
| 55 | // Mark ASs to update |
| 56 | for k, wd := range wanted { |
| 57 | if existingMap[k] == nil { |
| 58 | continue |
| 59 | } |
| 60 | if existingMap[k].Source != "from-peeringdb" { |
| 61 | continue |
| 62 | } |
| 63 | if wd.Name != existingMap[k].Name { |
| 64 | toUpdate[k] = true |
| 65 | continue |
| 66 | } |
| 67 | } |
| 68 | |
| 69 | if len(toAdd) > 0 { |
| 70 | glog.Infof("RecordPeeringDBPeers: adding %v", toAdd) |
| 71 | } |
| 72 | if len(toDelete) > 0 { |
| 73 | glog.Infof("RecordPeeringDBPeers: deleting %v", toDelete) |
| 74 | } |
| 75 | if len(toUpdate) > 0 { |
| 76 | glog.Infof("RecordPeeringDBPeers: updating %v", toUpdate) |
| 77 | } |
| 78 | |
| 79 | // Run INSERT to add new ASNs |
| 80 | if len(toAdd) > 0 { |
| 81 | q = ` |
| 82 | INSERT INTO peers |
| 83 | (asn, name, source) |
| 84 | VALUES |
| 85 | (:asn, :name, :source) |
| 86 | ` |
| 87 | |
| 88 | add := make([]*sqlPeer, len(toAdd)) |
| 89 | i := 0 |
| 90 | for ta, _ := range toAdd { |
| 91 | add[i] = &sqlPeer{ |
| 92 | ASN: ta, |
| 93 | Name: wanted[ta].Name, |
| 94 | Source: "from-peeringdb", |
| 95 | } |
| 96 | i += 1 |
| 97 | } |
| 98 | |
| 99 | if _, err := tx.NamedExecContext(ctx, q, add); err != nil { |
| 100 | return fmt.Errorf("INSERT peers: %v", err) |
| 101 | } |
| 102 | } |
| 103 | |
| 104 | // Run DELETE to remove nonexistent ASNs |
| 105 | if len(toDelete) > 0 { |
| 106 | deleteIds := make([]string, len(toDelete)) |
| 107 | i := 0 |
| 108 | for td, _ := range toDelete { |
| 109 | deleteIds[i] = existingMap[td].ID |
| 110 | i += 1 |
| 111 | } |
| 112 | query, args, err := sqlx.In("DELETE FROM peers WHERE id IN (?)", deleteIds) |
| 113 | if err != nil { |
| 114 | return fmt.Errorf("DELETE peers: %v", err) |
| 115 | } |
| 116 | query = tx.Rebind(query) |
| 117 | _, err = tx.ExecContext(ctx, query, args...) |
| 118 | if err != nil { |
| 119 | return fmt.Errorf("DELETE peers: %v", err) |
| 120 | } |
| 121 | } |
| 122 | |
| 123 | // Run UPDATE to update existing ASNs |
| 124 | for k, _ := range toUpdate { |
| 125 | want := wanted[k] |
| 126 | got := existingMap[k] |
| 127 | |
| 128 | fields := []string{} |
| 129 | args := []interface{}{} |
| 130 | if want.Name != got.Name { |
| 131 | fields = append(fields, "name = ?") |
| 132 | args = append(args, want.Name) |
| 133 | } |
| 134 | |
| 135 | q = fmt.Sprintf(` |
| 136 | UPDATE peers |
| 137 | SET |
| 138 | %s |
| 139 | WHERE |
| 140 | id = ? |
| 141 | `, strings.Join(fields, ",\n")) |
| 142 | q = tx.Rebind(q) |
| 143 | args = append(args, got.ID) |
| 144 | _, err := tx.ExecContext(ctx, q, args...) |
| 145 | if err != nil { |
| 146 | return fmt.Errorf("UPDATE peers: %v", err) |
| 147 | } |
| 148 | } |
| 149 | |
| 150 | return tx.Commit() |
| 151 | } |
| 152 | |
| 153 | func (s *sqlModel) GetPeeringDBPeer(ctx context.Context, asn int64) (*pb.PeeringDBMember, error) { |
| 154 | data := []struct { |
| 155 | sqlPeer `db:"peers"` |
| 156 | sqlPeerRouter `db:"peer_routers"` |
| 157 | }{} |
| 158 | q := ` |
| 159 | SELECT |
| 160 | peers.id "peers.id", |
| 161 | peers.asn "peers.asn", |
| 162 | peers.name "peers.name", |
| 163 | |
| 164 | peer_routers.peer_id "peer_routers.peer_id", |
| 165 | peer_routers.v6 "peer_routers.v6", |
| 166 | peer_routers.v4 "peer_routers.v4" |
| 167 | FROM peers |
| 168 | LEFT JOIN peer_routers |
| 169 | ON peer_routers.peer_id = peers.id |
| 170 | WHERE peers.asn = $1 |
| 171 | ` |
| 172 | if err := s.db.SelectContext(ctx, &data, q, asn); err != nil { |
| 173 | return nil, fmt.Errorf("SELECT peers/peerRouters: %v", err) |
| 174 | } |
| 175 | |
| 176 | res := &pb.PeeringDBMember{} |
| 177 | |
| 178 | for i, row := range data { |
| 179 | if res.Routers == nil { |
| 180 | asn, err := strconv.ParseInt(row.sqlPeer.ASN, 10, 64) |
| 181 | if err != nil { |
| 182 | return nil, fmt.Errorf("data corruption: invalid ASN %q", row.sqlPeer.ASN) |
| 183 | } |
| 184 | res.Asn = asn |
| 185 | res.Name = row.sqlPeer.Name |
| 186 | res.Routers = make([]*pb.PeeringDBMember_Router, len(data)) |
| 187 | } |
| 188 | |
| 189 | res.Routers[i] = &pb.PeeringDBMember_Router{} |
| 190 | if row.sqlPeerRouter.V6.Valid { |
| 191 | res.Routers[i].Ipv6 = row.sqlPeerRouter.V6.String |
| 192 | } |
| 193 | if row.sqlPeerRouter.V4.Valid { |
| 194 | res.Routers[i].Ipv4 = row.sqlPeerRouter.V4.String |
| 195 | } |
| 196 | } |
| 197 | |
| 198 | return res, nil |
| 199 | } |