publicdns.go

// Package publicdns allows the user to obtain data from public-dns.info, query and manage the data
package publicdns

/*
 * The MIT License (MIT)
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in all
 * copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
 * SOFTWARE.
 */
import (
	"database/sql"
	"errors"
	"fmt"
	"github.com/gocarina/gocsv"
	"io"
	"net/http"
	"os"
	"strings"
	"time"
)

// NameserverCountryTally is a structure that will hold the result of the GetTotalServersPerCountry func
type NameserverCountryTally struct {
	Country string
	Total   int
}

// Nameserver is the structure that mimics the fields that belong CSV file that can be obtained from public-dns.info.
type Nameserver struct {
	// IPAddress is the ipv4 address of the server
	IPAddress string `csv:"ip"`

	// Name is the hostname of the server if the server has a hostname
	Name string `csv:"name"`

	// Country is the two-letter ISO 3166-1 alpha-2 code of the country
	Country string `csv:"country_id"`

	// City specifies the city that the server is hosted on
	City string `csv:"city"`

	// Version is the software version of the dns daemon that the server is using
	Version string `csv:"version"`

	// Error is the error that the server returned. Probably will be empty if you use the valid nameserver dataset
	Error string `csv:"error"`

	// DNSSec is a boolean to indicate if the server supports DNSSec or not
	DNSSec bool `csv:"dnssec"`

	// Realiability is a normalized value - from 0.0 - 1.0 - to indicate how stable the server is
	Reliability string `csv:"reliability"`

	// CheckedAt is a timestamp to indicate the date that the server was last checked
	CheckedAt time.Time `csv:"checked_at"`

	// CreatedAt is a timestamp to indicate when the server was inserted in the database
	CreatedAt time.Time `csv:"created_at"`
}

// LoadFromFile takes a filename (assumed to be a CSV) and loads the server data contained in that file.
func LoadFromFile(filename string) ([]*Nameserver, error) {
	file, err := os.Open(filename)

	if err != nil {
		return nil, err
	}

	defer file.Close()

	servers := []*Nameserver{}
	err = gocsv.UnmarshalFile(file, &servers)

	if err != nil {
		return nil, err
	}

	return servers, nil
}

// LoadFromURL takes a URL with a CSV file, downloads the file and attempts to load the file contents using the
// previously refered LoadFromFile. A filename called nameservers.temp.csv will be created.
func LoadFromURL(url string, filename string) ([]*Nameserver, error) {
	out, err := os.Create(filename)

	if err != nil {
		return nil, err
	}

	resp, err := http.Get(url)

	if err != nil {
		return nil, err
	}

	defer resp.Body.Close()
	written, err := io.Copy(out, resp.Body)

	if err != nil {
		return nil, err
	}

	if written == 0 {
		return nil, errors.New("No bytes written")
	}

	err = out.Sync()
	if err != nil {
		return nil, err
	}

	out.Close()

	return LoadFromFile(out.Name())
}

// DumpToDatabase dumps a complete server dataset into the selected database instance. It will create the database
// if it does not exist and insert all records present in the 'servers' variable. This function will insert all records
// in a single transaction. The test data indicates about 40000 records but the performance seems perfectly fine. Also
// consider that the table will be dropped.
//
// The database schema amounts to the same fields as the CSV value that you can find at public-dns.info.
// - IP (the ipv4 address of the server)
// - Name (the hostname of the server if the server has a hostname)
// - Country (two-letter ISO 3166-1 alpha-2 code. probably an IP location lookup by public-dns.info)
// - City (the city name that the server is hosted on. probably an IP location lookup by public-dns.info)
// - Version (the software version of the dns daemon that the server is using)
// - Error (the error that the server returned. probably will be empty if you are using the valid nameserver dataset)
// - DNSSec (boolean to indicate if the server supports DNSSec or not)
// - Reliability (a reliability value - normalized from 0.0 - 1.0 - to indicate how stable the server is)
// - CheckedAt (a timestamp to indicate the date that the server was last checked)
// - CreatedAt (a timestamp to indicate when the server was inserted in the database)
//
// TODO Create an index for Country, Reliability and IP
// TODO Fix the schema and the data types of each field to be something meaningful instead of 100% varchar
func DumpToDatabase(db *sql.DB, servers []*Nameserver) (int64, error) {
	var total int64
	var query string
	var fields []string

	// It's safe to ignore the execution error that my occur.
	// If there is an problem with the deletion it will be thrown in the table creation query
	db.Exec(`DROP TABLE 'nameservers'`)

	fields = []string{
		`'ip' VARCHAR(45) PRIMARY KEY`,
		`'name' VARCHAR(64) NULL`,
		`'country' VARCHAR(2) NULL`,
		`'city' VARCHAR(64) NULL`,
		`'version' VARCHAR(16) NULL`,
		`'error' VARCHAR(256) NULL`,
		`'dnssec' TINYINT NULL`,
		`'reliability' FLOAT NULL`,
		`'checked_at' DATETIME NULL`,
		`'created_at' DATETIME NULL`,
	}

	query = `CREATE TABLE IF NOT EXISTS 'nameservers' (` + strings.Join(fields, ",") + `);`
	_, errCreateTable := db.Exec(query)

	if errCreateTable != nil {
		return total, errCreateTable
	}

	indexes := []string{
		"CREATE INDEX nameservers_country_index ON nameservers(country);",
		"CREATE INDEX nameservers_country_reliability_index ON nameservers(country,reliability);",
		"CREATE INDEX nameservers_reliability_index ON nameservers(reliability);",
	}

	_, errCreateIndexes := db.Exec(strings.Join(indexes, ""))

	if errCreateIndexes != nil {
		return total, errCreateIndexes
	}

	tx, err := db.Begin()

	if err != nil {
		return total, err
	}

	fields = []string{
		"ip",
		"name",
		"country",
		"city",
		"version",
		"error",
		"dnssec",
		"reliability",
		"checked_at",
		"created_at",
	}

	query = "INSERT INTO nameservers(" + strings.Join(fields, ",") + ") VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
	stmt, prepareErr := tx.Prepare(query)

	if prepareErr != nil {
		return total, prepareErr
	}

	// TODO Should we check for an error while creating the statement or just count on the transaction to fail?
	for _, client := range servers {
		r, _ := stmt.Exec(
			client.IPAddress,
			client.Name,
			client.Country,
			client.City,
			client.Version,
			client.Error,
			client.DNSSec,
			client.Reliability,
			client.CheckedAt,
			client.CreatedAt,
		)

		n, _ := r.RowsAffected()
		total = total + n
	}

	if txErr := tx.Commit(); txErr != nil {
		tx.Rollback()
		return 0, txErr
	}

	return total, nil
}

// PublicDNS is the structure that is used to perform queries on the nameservers dataset that was stored in a database.
// The only parameter is an SQL connection instance. You can use any server that is supported by Golang.
type PublicDNS struct {
	DB *sql.DB
}

// GetAllFromCountry obtains all the DNS servers registered in the database for a specific country. The country letter
// must be a two-letter ISO 3166-1 alpha-2 code i.e. US, PT, JP.
// TODO Do we really need to count the amount of records?
func (p *PublicDNS) GetAllFromCountry(country string) ([]*Nameserver, error) {
	count := 0
	p.DB.QueryRow("SELECT COUNT(ip) FROM nameservers as n WHERE n.country = ?", country).Scan(&count)

	result, err := p.DB.Query("SELECT ip, country, city FROM nameservers as n WHERE n.country = ?", country)

	if err != nil {
		return nil, err
	}

	defer result.Close()

	var dnsinfo []*Nameserver

	for result.Next() {
		info := &Nameserver{}
		result.Scan(&info.IPAddress, &info.Country, &info.City)
		dnsinfo = append(dnsinfo, info)
	}

	return dnsinfo, nil

}

// GetBestFromCountry obtains the best DNS server from a specific country. This is measured by the reliability
// parameter so for many countries it will always return the same server (for the US it's always Google's DNS server).
// For countries that have less reliable DNS servers (such as those located in Africa) this could be more useful.
func (p *PublicDNS) GetBestFromCountry(country string) (*Nameserver, error) {
	result := p.DB.QueryRow("SELECT ip, country, city FROM nameservers WHERE country = ? ORDER BY reliability DESC LIMIT 1", country)

	info := &Nameserver{}
	err := result.Scan(&info.IPAddress, &info.Country, &info.City)

	if err != nil {
		return nil, err
	}

	return info, nil
}

// GetBestFromCountries takes a list of countries (two-letter ISO 3166-1 alpha-2 code) and obtains the best servers
// for each of the requested countries.
func (p *PublicDNS) GetBestFromCountries(countries []interface{}) ([]*Nameserver, error) {
	// This will create someting like IN(?, ?, ?) (depending on the number of countries)
	placeholders := "?" + strings.Repeat(", ?", len(countries)-1)

	subquery := "SELECT n.ip, n.country, n.city, n.reliability " +
		"FROM nameservers AS n " +
		"WHERE n.country IN (" + placeholders + ")  and name != '' and city != '' " +
		"ORDER BY reliability DESC, n.checked_at DESC"
	query := fmt.Sprintf("SELECT * FROM (%s) as nn GROUP BY nn.country;", subquery)

	stmt, err1 := p.DB.Prepare(query)

	if err1 != nil {
		return nil, err1
	}

	defer stmt.Close()

	// Then, using the variadic operator, we expand the list of countries into the placeholders
	result, err2 := stmt.Query(countries...)

	if err2 != nil {
		return nil, err2
	}

	defer result.Close()

	var dnsinfo []*Nameserver

	for result.Next() {
		info := &Nameserver{}
		result.Scan(&info.IPAddress, &info.Country, &info.City, &info.Reliability)
		dnsinfo = append(dnsinfo, info)
	}

	return dnsinfo, nil
}

// GetNameserverPerCountryTally obtains a list the total of "good" nameservers that exist per country. In this context
// "good" means that the server as a hostname (reverse lookup) has city name and its reliability score is 1 (maximum).
func (p *PublicDNS) GetNameserverPerCountryTally() ([]*NameserverCountryTally, error) {
	query := "SELECT n.country AS Country, COUNT(n.ip) AS Total " +
		"FROM nameservers AS n " +
		"WHERE n.name != '' AND n.city != '' AND n.reliability = 1 " +
		"GROUP BY n.country"

	rows, err := p.DB.Query(query)
	if err != nil {
		return nil, err
	}

	defer rows.Close()

	var nameserverTally []*NameserverCountryTally

	for rows.Next() {
		tally := &NameserverCountryTally{}
		rows.Scan(&tally.Country, &tally.Total)
		nameserverTally = append(nameserverTally, tally)
	}

	return nameserverTally, nil
}