· 9 years ago · Oct 11, 2016, 09:16 PM
1package main
2
3import (
4 "database/sql"
5 "fmt"
6 "log"
7 "runtime"
8 "strconv"
9 "time"
10 _ "github.com/mattn/go-sqlite3"
11)
12
13// Default params, multi-part texts and photo texts might have other fields...
14type plivoSms struct {
15 To string `json:"to"`
16 From string `json:"from"`
17 Text string `json:"message"`
18 MessageUUID string `json:"id"`
19 SentOn time.Time
20}
21
22// connection pool for SQLite logging
23var db *sql.DB
24
25func main() {
26
27 var err error
28
29 // Open up our logging database to record activity
30 db, err = sql.Open("sqlite3", "./sms.db")
31 if err != nil {
32 log.Fatal(err)
33 }
34
35 createTables(db)
36
37 // Get POST data
38 sms := plivoSms{
39 From: "8005550000",
40 Text: "hello world",
41 MessageUUID: strconv.FormatInt(time.Now().UnixNano(), 10),
42 }
43
44 storeText(db, sms)
45
46 texts := readTexts(db, 500)
47 for k, text := range texts {
48 fmt.Printf("<p>%d: %v</p>\n", k, text)
49 }
50
51}
52
53func createTables(db *sql.DB) {
54 // create table if not exists
55 sqlTable := `
56 CREATE TABLE IF NOT EXISTS txt (
57 "MessageUUID" TEXT NOT NULL PRIMARY KEY,
58 "From" TEXT,
59 "Text" TEXT,
60 "To" TEXT,
61 SentOn DATETIME
62 );
63 `
64
65 _, err := db.Exec(sqlTable)
66 if err != nil {
67 panic(err)
68 }
69}
70
71func storeText(db *sql.DB, text plivoSms) {
72 sqlAddText := `
73 INSERT OR REPLACE INTO txt (
74 "MessageUUID",
75 "To",
76 "From",
77 "Text",
78 SentOn
79 ) values(?, ?, ?, ?, CURRENT_TIMESTAMP)
80 `
81
82 _, err := db.Exec(sqlAddText, text.MessageUUID, text.To, text.From, text.Text)
83 if err != nil {
84 log.Fatal(err)
85 panic(err)
86 }
87}
88
89func readTexts(db *sql.DB, limit int) []plivoSms {
90 sqlReadall := `
91 SELECT MessageUUID, "To", "From", "Text", SentOn FROM txt
92 ORDER BY datetime(SentOn) DESC LIMIT ?
93 `
94
95 rows, err := db.Query(sqlReadall, limit)
96 if err != nil {
97 panic(err)
98 }
99 defer rows.Close()
100
101 var result []plivoSms
102 for rows.Next() {
103 text := plivoSms{}
104 err2 := rows.Scan(&text.MessageUUID, &text.To, &text.From, &text.Text, &text.SentOn)
105 if err2 != nil {
106 panic(err2)
107 }
108 result = append(result, text)
109 }
110 return result
111}