bgpwtf/cccampix/verifier/model/peer_routers.go

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()
}