this repo has no description
willdot.net
1package tangledalertbot
2
3import (
4 "database/sql"
5 "errors"
6 "fmt"
7 "log/slog"
8 "os"
9
10 _ "github.com/glebarez/go-sqlite"
11)
12
13// Database is a sqlite database
14type Database struct {
15 db *sql.DB
16}
17
18// NewDatabase will open a new database. It will ping the database to ensure it is available and error if not
19func NewDatabase(dbPath string) (*Database, error) {
20 if dbPath != ":memory:" {
21 err := createDbFile(dbPath)
22 if err != nil {
23 return nil, fmt.Errorf("create db file: %w", err)
24 }
25 }
26
27 db, err := sql.Open("sqlite", dbPath)
28 if err != nil {
29 return nil, fmt.Errorf("open database: %w", err)
30 }
31
32 err = db.Ping()
33 if err != nil {
34 return nil, fmt.Errorf("ping db: %w", err)
35 }
36
37 err = createIssuesTable(db)
38 if err != nil {
39 return nil, fmt.Errorf("creating issues table: %w", err)
40 }
41
42 err = createCommentsTable(db)
43 if err != nil {
44 return nil, fmt.Errorf("creating comments table: %w", err)
45 }
46
47 return &Database{db: db}, nil
48}
49
50// Close will cleanly stop the database connection
51func (d *Database) Close() {
52 err := d.db.Close()
53 if err != nil {
54 slog.Error("failed to close db", "error", err)
55 }
56}
57
58func createDbFile(dbFilename string) error {
59 if _, err := os.Stat(dbFilename); !errors.Is(err, os.ErrNotExist) {
60 return nil
61 }
62
63 f, err := os.Create(dbFilename)
64 if err != nil {
65 return fmt.Errorf("create db file : %w", err)
66 }
67 err = f.Close()
68 if err != nil {
69 return fmt.Errorf("failed to close DB file: %w", err)
70 }
71 return nil
72}
73
74func createIssuesTable(db *sql.DB) error {
75 createTableSQL := `CREATE TABLE IF NOT EXISTS issues (
76 "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
77 "authorDid" TEXT,
78 "rkey" TEXT,
79 "title" TEXT,
80 "body" TEXT,
81 "repo" TEXT,
82 "createdAt" integer NOT NULL,
83 UNIQUE(authorDid,rkey)
84 );`
85
86 slog.Info("Create issues table...")
87 statement, err := db.Prepare(createTableSQL)
88 if err != nil {
89 return fmt.Errorf("prepare DB statement to create issues table: %w", err)
90 }
91 _, err = statement.Exec()
92 if err != nil {
93 return fmt.Errorf("exec sql statement to create issues table: %w", err)
94 }
95 slog.Info("issues table created")
96
97 return nil
98}
99
100func createCommentsTable(db *sql.DB) error {
101 createTableSQL := `CREATE TABLE IF NOT EXISTS comments (
102 "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
103 "authorDid" TEXT,
104 "rkey" TEXT,
105 "body" TEXT,
106 "issue" TEXT,
107 "replyTo" TEXT,
108 "createdAt" integer NOT NULL,
109 UNIQUE(authorDid,rkey)
110 );`
111
112 slog.Info("Create comments table...")
113 statement, err := db.Prepare(createTableSQL)
114 if err != nil {
115 return fmt.Errorf("prepare DB statement to create comments table: %w", err)
116 }
117 _, err = statement.Exec()
118 if err != nil {
119 return fmt.Errorf("exec sql statement to create comments table: %w", err)
120 }
121 slog.Info("comments table created")
122
123 return nil
124}
125
126// CreateIssue will insert a issue into a database
127func (d *Database) CreateIssue(issue Issue) error {
128 sql := `INSERT INTO issues (authorDid, rkey, title, body, repo, createdAt) VALUES (?, ?, ?, ?, ?, ?) ON CONFLICT(authorDid, rkey) DO NOTHING;`
129 _, err := d.db.Exec(sql, issue.AuthorDID, issue.RKey, issue.Title, issue.Body, issue.Repo, issue.CreatedAt)
130 if err != nil {
131 return fmt.Errorf("exec insert issue: %w", err)
132 }
133 return nil
134}
135
136// CreateComment will insert a comment into a database
137func (d *Database) CreateComment(comment Comment) error {
138 sql := `INSERT INTO comments (authorDid, rkey, body, issue, replyTo, createdAt) VALUES (?, ?, ?, ?, ?, ?) ON CONFLICT(authorDid, rkey) DO NOTHING;`
139 _, err := d.db.Exec(sql, comment.AuthorDID, comment.RKey, comment.Body, comment.Issue, comment.ReplyTo, comment.CreatedAt)
140 if err != nil {
141 return fmt.Errorf("exec insert comment: %w", err)
142 }
143 return nil
144}