Go backend for tracking results from conway game of life soup search.
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

183 lines
3.4 KiB

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
}