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 "createdAt" integer NOT NULL,
108 UNIQUE(authorDid,rkey)
109 );`
110
111 slog.Info("Create comments table...")
112 statement, err := db.Prepare(createTableSQL)
113 if err != nil {
114 return fmt.Errorf("prepare DB statement to create comments table: %w", err)
115 }
116 _, err = statement.Exec()
117 if err != nil {
118 return fmt.Errorf("exec sql statement to create comments table: %w", err)
119 }
120 slog.Info("comments table created")
121
122 return nil
123}
124
125// CreateIssue will insert a issue into a database
126func (d *Database) CreateIssue(issue Issue) error {
127 sql := `REPLACE INTO issues (authorDid, rkey, title, body, repo, createdAt) VALUES (?, ?, ?, ?, ?, ?);`
128 _, err := d.db.Exec(sql, issue.AuthorDID, issue.RKey, issue.Title, issue.Body, issue.Repo, issue.CreatedAt)
129 if err != nil {
130 return fmt.Errorf("exec insert issue: %w", err)
131 }
132 return nil
133}
134
135// CreateComment will insert a comment into a database
136func (d *Database) CreateComment(comment Comment) error {
137 sql := `REPLACE INTO comments (authorDid, rkey, body, issue, createdAt) VALUES (?, ?, ?, ?, ?);`
138 _, err := d.db.Exec(sql, comment.AuthorDID, comment.RKey, comment.Body, comment.Issue, comment.CreatedAt)
139 if err != nil {
140 return fmt.Errorf("exec insert comment: %w", err)
141 }
142 return nil
143}
144
145func (d *Database) GetIssues() ([]Issue, error) {
146 sql := "SELECT authorDid, rkey, title, body, repo, createdAt FROM issues;"
147 rows, err := d.db.Query(sql)
148 if err != nil {
149 return nil, fmt.Errorf("run query to get issues: %w", err)
150 }
151 defer rows.Close()
152
153 var results []Issue
154 for rows.Next() {
155 var issue Issue
156 if err := rows.Scan(&issue.AuthorDID, &issue.RKey, &issue.Title, &issue.Body, &issue.Repo, &issue.CreatedAt); err != nil {
157 return nil, fmt.Errorf("scan row: %w", err)
158 }
159
160 results = append(results, issue)
161 }
162 return results, nil
163}
164
165func (d *Database) GetComments() ([]Comment, error) {
166 sql := "SELECT authorDid, rkey, body, issue, createdAt FROM comments;"
167 rows, err := d.db.Query(sql)
168 if err != nil {
169 return nil, fmt.Errorf("run query to get comments: %w", err)
170 }
171 defer rows.Close()
172
173 var results []Comment
174 for rows.Next() {
175 var comment Comment
176 if err := rows.Scan(&comment.AuthorDID, &comment.RKey, &comment.Body, &comment.Issue, &comment.CreatedAt); err != nil {
177 return nil, fmt.Errorf("scan row: %w", err)
178 }
179
180 results = append(results, comment)
181 }
182 return results, nil
183}
184
185func (d *Database) DeleteIssue(did, rkey string) error {
186 sql := "DELETE FROM issues WHERE authorDid = ? AND rkey = ?;"
187 _, err := d.db.Exec(sql, did, rkey)
188 if err != nil {
189 return fmt.Errorf("exec delete issue: %w", err)
190 }
191 return nil
192}
193
194func (d *Database) DeleteComment(did, rkey string) error {
195 sql := "DELETE FROM comments WHERE authorDid = ? AND rkey = ?;"
196 _, err := d.db.Exec(sql, did, rkey)
197 if err != nil {
198 return fmt.Errorf("exec delete issue: %w", err)
199 }
200 return nil
201}
202
203func (d *Database) DeleteCommentsForIssue(issueURI string) error {
204 sql := "DELETE FROM comments WHERE issue = ?;"
205 _, err := d.db.Exec(sql, issueURI)
206 if err != nil {
207 return fmt.Errorf("exec delete comments for issue")
208 }
209 return nil
210}