bgpwtf/cccampix: draw the rest of the fucking owl
Change-Id: I49fd5906e69512e8f2d414f406edc0179522f225
diff --git a/bgpwtf/cccampix/verifier/model/peer_routers.go b/bgpwtf/cccampix/verifier/model/peer_routers.go
new file mode 100644
index 0000000..853b9e5
--- /dev/null
+++ b/bgpwtf/cccampix/verifier/model/peer_routers.go
@@ -0,0 +1,128 @@
+package model
+
+import (
+ "context"
+ "database/sql"
+ "fmt"
+
+ pb "code.hackerspace.pl/hscloud/bgpwtf/cccampix/proto"
+ "github.com/golang/glog"
+ "github.com/jmoiron/sqlx"
+)
+
+func (m *sqlModel) RecordPeeringDBPeerRouters(ctx context.Context, members []*pb.PeeringDBMember) error {
+ tx := m.db.MustBeginTx(ctx, &sql.TxOptions{})
+ defer tx.Rollback()
+
+ for _, member := range members {
+ // Get existing routers for peer.
+ q := `
+ SELECT peer_routers.id, peer_routers.v4, peer_routers.v6
+ FROM peer_routers
+ LEFT JOIN peers ON (peer_routers.peer_id = peers.id)
+ WHERE peer_routers.source = 'from-peeringdb'
+ AND peers.asn = ?
+ `
+ q = tx.Rebind(q)
+ existing := []sqlPeerRouter{}
+ if err := tx.SelectContext(ctx, &existing, q, fmt.Sprintf("%d", member.Asn)); err != nil {
+ return fmt.Errorf("SELECT peerRouters: %v", err)
+ }
+
+ // Find all routers that need to be deleted because they're outdated.
+ // We do not attempt updates, only removals/recreations.
+
+ // UUID -> bool
+ toDelete := make(map[string]bool)
+
+ for _, ex := range existing {
+ // Try to find a requested router with same IP addresses.
+ found := false
+ for _, router := range member.Routers {
+ if router.Ipv4 == ex.V4.String && router.Ipv6 == ex.V6.String {
+ found = true
+ break
+ }
+ }
+
+ // Not found, mark for deletion.
+ if !found {
+ toDelete[ex.ID] = true
+ }
+ }
+
+ // Find all routers that need to be created.
+ toAdd := []sqlPeerRouter{}
+ for _, router := range member.Routers {
+ // Try to find an existing router with same IP addresses.
+ found := false
+ for _, ex := range existing {
+ if router.Ipv4 == ex.V4.String && router.Ipv6 == ex.V6.String {
+ found = true
+ break
+ }
+ }
+ // Not found, mark for creation.
+ if !found {
+ ta := sqlPeerRouter{
+ Source: "from-peeringdb",
+ ASN: fmt.Sprintf("%d", member.Asn),
+ }
+ if router.Ipv6 != "" {
+ ta.V6.String = router.Ipv6
+ ta.V6.Valid = true
+ }
+ if router.Ipv4 != "" {
+ ta.V4.String = router.Ipv4
+ ta.V4.Valid = true
+ }
+ toAdd = append(toAdd, ta)
+ }
+ }
+
+ if len(toDelete) > 0 {
+ glog.Infof("RecordPeeringDBPeers: deleting %v", toDelete)
+ }
+ if len(toAdd) > 0 {
+ glog.Infof("RecordPeeringDBPeers: adding %+v", toAdd)
+ }
+
+ // Delete any routers, if needed.
+ if len(toDelete) > 0 {
+ // Get list of IDs to delete.
+ deleteIds := make([]string, len(toDelete))
+ i := 0
+ for id, _ := range toDelete {
+ deleteIds[i] = id
+ i += 1
+ }
+ query, args, err := sqlx.In("DELETE FROM peer_Routers WHERE id IN (?)", deleteIds)
+ if err != nil {
+ return fmt.Errorf("DELETE peerRouters: %v", err)
+ }
+ query = tx.Rebind(query)
+ _, err = tx.ExecContext(ctx, query, args...)
+ if err != nil {
+ return fmt.Errorf("DELETE peerRouters: %v", err)
+ }
+ }
+
+ // Add any routers, if needed.
+ for _, ta := range toAdd {
+ q := `
+ INSERT INTO peer_routers
+ (peer_id, v6, v4, source)
+ SELECT
+ peers.id, :v6, :v4, :source
+ FROM
+ peers
+ WHERE peers.asn = :asn
+ `
+ if _, err := tx.NamedExecContext(ctx, q, ta); err != nil {
+ return fmt.Errorf("INSERT peerRouters: %v", err)
+ }
+ }
+ }
+
+ return tx.Commit()
+}