1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

package main

import (
    "database/sql"
    "fmt"

    _ "modernc.org/sqlite"
)

func saveRequest(db *sql.DB, message string) (string, error) {
    id := newUUID()

    _, err := db.Exec(
        `INSERT INTO requests (id, sha1, message) VALUES (?, ?, ?)`,
        id, hashSHA1(message), encodeB64(message),
    )

    if err != nil {
        return "", fmt.Errorf("saveRequest: %w", err)
    }
    return id, nil
}

func saveResponse(db *sql.DB, requestID, response string) error {
    _, err := db.Exec(
        `INSERT INTO responses (id, id_request, response) VALUES (?, ?, ?)`,
        newUUID(), requestID, encodeB64(response),
    )
    if err != nil {
        return fmt.Errorf("saveResponse: %w", err)
    }
    return nil
}

func initSchema(db *sql.DB) error {
    _, err := db.Exec(`
		CREATE TABLE IF NOT EXISTS requests (
			id         TEXT PRIMARY KEY,
			sha1       TEXT NOT NULL,
			message    TEXT NOT NULL,
			created_at TEXT DEFAULT (datetime('now'))
		);
		CREATE TABLE IF NOT EXISTS responses (
			id         TEXT PRIMARY KEY,
			id_request TEXT NOT NULL,
			response   TEXT NOT NULL,
			created_at TEXT DEFAULT (datetime('now')),
			FOREIGN KEY (id_request) REFERENCES requests(id)
		);
	`)
    return err
}

func getRequestsHistory(db *sql.DB) (*sql.Rows, error) {
    rows, err := db.Query(`
		SELECT r.message, rs.response, r.created_at
		FROM   requests r
		JOIN   responses rs ON rs.id_request = r.id
		ORDER  BY r.created_at ASC
		LIMIT  10
	`)
    return rows, err
}