db_readers.go

package main

import (
	"database/sql"
	"fmt"
	"iter"

	"github.com/monoidic/dns"
)

func getDbFieldData(qs string, db *sql.DB) iter.Seq[fieldData] {
	return func(yield func(fieldData) bool) {
		tx := check1(db.Begin())
		defer func() { check(tx.Commit()) }()
		rows := check1(tx.Query(qs))
		defer rows.Close()

		for rows.Next() {
			var fd fieldData
			check(rows.Scan(&fd.name, &fd.id))
			if !yield(fd) {
				return
			}
		}
	}
}

func getDbNameData(qs string, db *sql.DB) iter.Seq[nameData] {
	return func(yield func(nameData) bool) {
		for v := range getDbFieldData(qs, db) {
			e := nameData{name: mustParseName(v.name), id: v.id}
			if !yield(e) {
				return
			}
		}
	}
}

// wrapper that prepends `_dmarc.` to the name while keeping the id the same
func getUnqueriedDMARC(db *sql.DB) iter.Seq[nameData] {
	return func(yield func(nameData) bool) {
		var err error
		for fd := range getDbNameData(`
		SELECT DISTINCT zone.name, zone.id
		FROM name AS zone
		WHERE zone.dmarc_tried=FALSE
		AND zone.is_zone=TRUE AND zone.registered=TRUE AND zone.valid=TRUE
	`, db) {
			fd.name, err = dns.NameFromLabels(append([]string{"_dmarc"}, fd.name.SplitRaw()...))
			if err == nil && !yield(fd) {
				return
			}
		}
	}
}

func getUnqueriedChaosTXT(db *sql.DB) iter.Seq[fieldData] {
	var v4Filter string
	if !v6 {
		v4Filter = `AND ip.address LIKE '%.%'`
	}
	filter := fmt.Sprintf(`
		SELECT address, id
		FROM ip
		WHERE ch_resolved=FALSE
		AND ip.responsive=TRUE
		%s
	`, v4Filter)

	return getDbFieldData(filter, db)
}

func netZoneReader(db *sql.DB, extraFilter string) iter.Seq[nameData] {
	qs := fmt.Sprintf(`
		SELECT zone.name, zone.id
		FROM name AS zone
		WHERE zone.is_zone=TRUE AND zone.registered=TRUE AND zone.valid=TRUE
		%s
	`, extraFilter)
	return getDbNameData(qs, db)
}

func zoneNsIpReader(db *sql.DB) iter.Seq[zoneIP] {
	return func(yield func(zoneIP) bool) {
		qs := `
		SELECT DISTINCT zone.name, ip.address, zone.id, ip.id
		FROM zone_ns
		INNER JOIN name AS zone ON zone_ns.zone_id = zone.id
		INNER JOIN name_ip ON zone_ns.ns_id = name_ip.name_id
		INNER JOIN ip ON name_ip.ip_id = ip.id
		WHERE zone.is_zone=TRUE AND zone.registered=TRUE AND zone.valid=TRUE
	`

		tx := check1(db.Begin())
		defer func() { check(tx.Commit()) }()
		rows := check1(tx.Query(qs))
		defer rows.Close()

		for rows.Next() {
			var zip zoneIP
			var zoneName string
			check(rows.Scan(&zoneName, &zip.ip.name, &zip.zone.id, &zip.ip.id))
			zip.zone.name = mustParseName(zoneName)
			if !yield(zip) {
				return
			}
		}
	}
}

func zoneNsIpParentReader(db *sql.DB) iter.Seq[zoneIP] {
	return func(yield func(zoneIP) bool) {
		qs := `
		SELECT DISTINCT zone.name, ip.address, zone.id, ip.id
		FROM name AS zone
		INNER JOIN name AS parent ON zone.parent_id=parent.id
		INNER JOIN zone_ns ON zone_ns.zone_id=parent.id
		INNER JOIN name_ip ON name_ip.name_id=zone_ns.ns_id 
		INNER JOIN ip ON name_ip.ip_id = ip.id
		WHERE zone.is_zone=TRUE AND parent.is_zone=TRUE
		AND ip.responsive=TRUE
		AND zone.registered=TRUE AND zone.valid=TRUE
		AND parent.registered=TRUE AND parent.valid=TRUE
	`

		tx := check1(db.Begin())
		defer func() { check(tx.Commit()) }()
		rows := check1(tx.Query(qs))
		defer rows.Close()

		for rows.Next() {
			var zip zoneIP
			var zoneName string
			check(rows.Scan(&zoneName, &zip.ip.name, &zip.zone.id, &zip.ip.id))
			zip.zone.name = mustParseName(zoneName)
			if !yield(zip) {
				return
			}
		}
	}
}

func parentNSReader(db *sql.DB) iter.Seq[zoneIP] {
	return func(yield func(zoneIP) bool) {
		qs := `
		SELECT DISTINCT zone.name, ip.address, zone.id, ip.id
		FROM zone_ns_ip_glue
		INNER JOIN name AS zone ON zone_ns_ip_glue.zone_id=zone.id
		INNER JOIN ip ON zone_ns_ip_glue.ip_id=ip.id
		WHERE zone_ns_ip_glue.fetched=FALSE
		AND ip.responsive=TRUE
		AND zone.is_zone=TRUE AND zone.registered=TRUE AND zone.valid=TRUE
	`

		tx := check1(db.Begin())
		defer func() { check(tx.Commit()) }()
		rows := check1(tx.Query(qs))
		defer rows.Close()

		for rows.Next() {
			var zip zoneIP
			var zoneName string
			check(rows.Scan(&zoneName, &zip.ip.name, &zip.zone.id, &zip.ip.id))
			zip.zone.name = mustParseName(zoneName)
			if !yield(zip) {
				return
			}
		}
	}
}