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