soup-tracker/sql.go

package main

import (
	"database/sql"
	_ "github.com/go-sql-driver/mysql"
)

type Life struct {
	Apgcode string
	Population int
	FirstFound int
	LastFound int
}

type Sample struct {
	Apgcode string
	Id int
	Hash string
}

// Connect to database
func dbConnect() (db *sql.DB) {
	if err != nil {
		panic(err)
	}

	return db
}

// Get Life record from census
func SQLGetLife(code string) (*Life, error) {
	db := dbConnect()
	defer db.Close()

	var apgcode string
	var population, firstFound, lastFound int
	row := db.QueryRow("SELECT * FROM census WHERE apgcode=?", code)
	err := row.Scan(&apgcode, &population, &firstFound, &lastFound)

	if err != nil {
		return nil, err
	}

	var life = Life {
		apgcode,
		population,
		firstFound,
		lastFound,
	}

	return &life, nil
}

// Get array of Sample records order by id
func SQLGetSamples(code string) ([]Sample, error) {
	db := dbConnect()
	defer db.Close()

	rows, err := db.Query("SELECT * FROM samples WHERE apgcode=? order by id", code)
	defer rows.Close()

	if err != nil {
		return nil, err
	}

	var samples []Sample
	var sample = Sample{}

	// Iterate over rows
	for rows.Next() {
		err = rows.Scan(&sample.Apgcode, &sample.Id, &sample.Hash)
		if err != nil {
			return nil, err
		}
		samples = append(samples, sample)
	}

	return samples, nil
}

type Genus struct {
	Name string
	Population int
	Species []Life
}

// Get array of lifes of a given genus
func SQLGetGenus(code string) (*Genus, error) {
	db := dbConnect()
	defer db.Close()

	rows, err := db.Query("SELECT * FROM census WHERE apgcode LIKE ? ORDER BY population DESC, firstFound", code + "%")
	defer rows.Close()
	if err != nil {
		return nil, err
	}

	var genus = Genus {
		Name: code,
	}

	// Iterate over rows
	for rows.Next() {
		var life = Life{}

		err = rows.Scan(&life.Apgcode, &life.Population, &life.FirstFound, &life.LastFound)
		if err != nil {
			panic(err)
		}

		genus.Species = append(genus.Species, life)
	}

	// Get population of genus
	genus.Population = getPopulationOfCode(code + "%")

	return &genus, nil
}

type GenusProxy struct {
	Name string
	Count int
}

type Family struct {
	Name string
	Genera []GenusProxy
}

// Get array of genus in the family, each genus should have the count of unique species
func SQLGetFamily(code string) (*Family, error) {
	db := dbConnect()
	defer db.Close()

	// Returns list of genera and their count of species below them, ordered by genus and formatted nicely
	// TLDR I'm a freaking god and here is my SQL to prove it
	rows, err := db.Query("SELECT SUBSTRING_INDEX(apgcode, \"_\", 1) AS a, COUNT(apgcode) FROM census WHERE apgcode LIKE ? GROUP BY a ORDER BY SUBSTR(a, LENGTH(?)-1, LENGTH(a)-(LENGTH(?)-1))", code + "%", code, code)
	defer rows.Close()

	if err != nil {
		panic(err)
	}

	var family = Family {
		Name: code,
	}

	for rows.Next() {
		var proxy GenusProxy
		rows.Scan(&proxy.Name, &proxy.Count)
		family.Genera = append(family.Genera, proxy)
	}

	return &family, nil
}

// Totals the population of species matching code
func getPopulationOfCode(code string) (pop int) {
	db := dbConnect()
	defer db.Close()

	err := db.QueryRow("SELECT SUM(population) FROM census WHERE apgcode LIKE ?", code).Scan(&pop)

	if err != nil {
		return 0
	}

	return pop
}

// Get the count of unique species of matching code
func getCountOfCode(code string) (count int) {
	db := dbConnect()
	defer db.Close()

	err := db.QueryRow("SELECT COUNT(population) FROM census WHERE apgcode LIKE ?", code).Scan(&count)

	if err != nil {
		return 0
	}

	return count
}