main.go 2.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133
  1. package main
  2. import (
  3. "database/sql"
  4. "fmt"
  5. "log"
  6. "math"
  7. "math/rand"
  8. sqlite "github.com/mattn/go-sqlite3"
  9. )
  10. // Computes x^y
  11. func pow(x, y int64) int64 {
  12. return int64(math.Pow(float64(x), float64(y)))
  13. }
  14. // Computes the bitwise exclusive-or of all its arguments
  15. func xor(xs ...int64) int64 {
  16. var ret int64
  17. for _, x := range xs {
  18. ret ^= x
  19. }
  20. return ret
  21. }
  22. // Returns a random number. It's actually deterministic here because
  23. // we don't seed the RNG, but it's an example of a non-pure function
  24. // from SQLite's POV.
  25. func getrand() int64 {
  26. return rand.Int63()
  27. }
  28. // Computes the standard deviation of a GROUPed BY set of values
  29. type stddev struct {
  30. xs []int64
  31. // Running average calculation
  32. sum int64
  33. n int64
  34. }
  35. func newStddev() *stddev { return &stddev{} }
  36. func (s *stddev) Step(x int64) {
  37. s.xs = append(s.xs, x)
  38. s.sum += x
  39. s.n++
  40. }
  41. func (s *stddev) Done() float64 {
  42. mean := float64(s.sum) / float64(s.n)
  43. var sqDiff []float64
  44. for _, x := range s.xs {
  45. sqDiff = append(sqDiff, math.Pow(float64(x)-mean, 2))
  46. }
  47. var dev float64
  48. for _, x := range sqDiff {
  49. dev += x
  50. }
  51. dev /= float64(len(sqDiff))
  52. return math.Sqrt(dev)
  53. }
  54. func main() {
  55. sql.Register("sqlite3_custom", &sqlite.SQLiteDriver{
  56. ConnectHook: func(conn *sqlite.SQLiteConn) error {
  57. if err := conn.RegisterFunc("pow", pow, true); err != nil {
  58. return err
  59. }
  60. if err := conn.RegisterFunc("xor", xor, true); err != nil {
  61. return err
  62. }
  63. if err := conn.RegisterFunc("rand", getrand, false); err != nil {
  64. return err
  65. }
  66. if err := conn.RegisterAggregator("stddev", newStddev, true); err != nil {
  67. return err
  68. }
  69. return nil
  70. },
  71. })
  72. db, err := sql.Open("sqlite3_custom", ":memory:")
  73. if err != nil {
  74. log.Fatal("Failed to open database:", err)
  75. }
  76. defer db.Close()
  77. var i int64
  78. err = db.QueryRow("SELECT pow(2,3)").Scan(&i)
  79. if err != nil {
  80. log.Fatal("POW query error:", err)
  81. }
  82. fmt.Println("pow(2,3) =", i) // 8
  83. err = db.QueryRow("SELECT xor(1,2,3,4,5,6)").Scan(&i)
  84. if err != nil {
  85. log.Fatal("XOR query error:", err)
  86. }
  87. fmt.Println("xor(1,2,3,4,5) =", i) // 7
  88. err = db.QueryRow("SELECT rand()").Scan(&i)
  89. if err != nil {
  90. log.Fatal("RAND query error:", err)
  91. }
  92. fmt.Println("rand() =", i) // pseudorandom
  93. _, err = db.Exec("create table foo (department integer, profits integer)")
  94. if err != nil {
  95. log.Fatal("Failed to create table:", err)
  96. }
  97. _, err = db.Exec("insert into foo values (1, 10), (1, 20), (1, 45), (2, 42), (2, 115)")
  98. if err != nil {
  99. log.Fatal("Failed to insert records:", err)
  100. }
  101. rows, err := db.Query("select department, stddev(profits) from foo group by department")
  102. if err != nil {
  103. log.Fatal("STDDEV query error:", err)
  104. }
  105. defer rows.Close()
  106. for rows.Next() {
  107. var dept int64
  108. var dev float64
  109. if err := rows.Scan(&dept, &dev); err != nil {
  110. log.Fatal(err)
  111. }
  112. fmt.Printf("dept=%d stddev=%f\n", dept, dev)
  113. }
  114. if err := rows.Err(); err != nil {
  115. log.Fatal(err)
  116. }
  117. }