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.

sql.go 3.4KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183
  1. package main
  2. import (
  3. "database/sql"
  4. _ "github.com/go-sql-driver/mysql"
  5. )
  6. type Life struct {
  7. Apgcode string
  8. Population int
  9. FirstFound int
  10. LastFound int
  11. }
  12. type Sample struct {
  13. Apgcode string
  14. Id int
  15. Hash string
  16. }
  17. // Connect to database
  18. func dbConnect() (db *sql.DB) {
  19. if err != nil {
  20. panic(err)
  21. }
  22. return db
  23. }
  24. // Get Life record from census
  25. func SQLGetLife(code string) (*Life, error) {
  26. db := dbConnect()
  27. defer db.Close()
  28. var apgcode string
  29. var population, firstFound, lastFound int
  30. row := db.QueryRow("SELECT * FROM census WHERE apgcode=?", code)
  31. err := row.Scan(&apgcode, &population, &firstFound, &lastFound)
  32. if err != nil {
  33. return nil, err
  34. }
  35. var life = Life {
  36. apgcode,
  37. population,
  38. firstFound,
  39. lastFound,
  40. }
  41. return &life, nil
  42. }
  43. // Get array of Sample records order by id
  44. func SQLGetSamples(code string) ([]Sample, error) {
  45. db := dbConnect()
  46. defer db.Close()
  47. rows, err := db.Query("SELECT * FROM samples WHERE apgcode=? order by id", code)
  48. defer rows.Close()
  49. if err != nil {
  50. return nil, err
  51. }
  52. var samples []Sample
  53. var sample = Sample{}
  54. // Iterate over rows
  55. for rows.Next() {
  56. err = rows.Scan(&sample.Apgcode, &sample.Id, &sample.Hash)
  57. if err != nil {
  58. return nil, err
  59. }
  60. samples = append(samples, sample)
  61. }
  62. return samples, nil
  63. }
  64. type Genus struct {
  65. Name string
  66. Population int
  67. Species []Life
  68. }
  69. // Get array of lifes of a given genus
  70. func SQLGetGenus(code string) (*Genus, error) {
  71. db := dbConnect()
  72. defer db.Close()
  73. rows, err := db.Query("SELECT * FROM census WHERE apgcode LIKE ? ORDER BY population DESC, firstFound", code + "%")
  74. defer rows.Close()
  75. if err != nil {
  76. return nil, err
  77. }
  78. var genus = Genus {
  79. Name: code,
  80. }
  81. // Iterate over rows
  82. for rows.Next() {
  83. var life = Life{}
  84. err = rows.Scan(&life.Apgcode, &life.Population, &life.FirstFound, &life.LastFound)
  85. if err != nil {
  86. panic(err)
  87. }
  88. genus.Species = append(genus.Species, life)
  89. }
  90. // Get population of genus
  91. genus.Population = getPopulationOfCode(code + "%")
  92. return &genus, nil
  93. }
  94. type GenusProxy struct {
  95. Name string
  96. Count int
  97. }
  98. type Family struct {
  99. Name string
  100. Genera []GenusProxy
  101. }
  102. // Get array of genus in the family, each genus should have the count of unique species
  103. func SQLGetFamily(code string) (*Family, error) {
  104. db := dbConnect()
  105. defer db.Close()
  106. // Returns list of genera and their count of species below them, ordered by genus and formatted nicely
  107. // TLDR I'm a freaking god and here is my SQL to prove it
  108. 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)
  109. defer rows.Close()
  110. if err != nil {
  111. panic(err)
  112. }
  113. var family = Family {
  114. Name: code,
  115. }
  116. for rows.Next() {
  117. var proxy GenusProxy
  118. rows.Scan(&proxy.Name, &proxy.Count)
  119. family.Genera = append(family.Genera, proxy)
  120. }
  121. return &family, nil
  122. }
  123. // Totals the population of species matching code
  124. func getPopulationOfCode(code string) (pop int) {
  125. db := dbConnect()
  126. defer db.Close()
  127. err := db.QueryRow("SELECT SUM(population) FROM census WHERE apgcode LIKE ?", code).Scan(&pop)
  128. if err != nil {
  129. return 0
  130. }
  131. return pop
  132. }
  133. // Get the count of unique species of matching code
  134. func getCountOfCode(code string) (count int) {
  135. db := dbConnect()
  136. defer db.Close()
  137. err := db.QueryRow("SELECT COUNT(population) FROM census WHERE apgcode LIKE ?", code).Scan(&count)
  138. if err != nil {
  139. return 0
  140. }
  141. return count
  142. }