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 err = createUsersTable(db)
48 if err != nil {
49 return nil, fmt.Errorf("creating users table: %w", err)
50 }
51
52 return &Database{db: db}, nil
53}
54
55// Close will cleanly stop the database connection
56func (d *Database) Close() {
57 err := d.db.Close()
58 if err != nil {
59 slog.Error("failed to close db", "error", err)
60 }
61}
62
63func createDbFile(dbFilename string) error {
64 if _, err := os.Stat(dbFilename); !errors.Is(err, os.ErrNotExist) {
65 return nil
66 }
67
68 f, err := os.Create(dbFilename)
69 if err != nil {
70 return fmt.Errorf("create db file : %w", err)
71 }
72 err = f.Close()
73 if err != nil {
74 return fmt.Errorf("failed to close DB file: %w", err)
75 }
76 return nil
77}
78
79func createIssuesTable(db *sql.DB) error {
80 createTableSQL := `CREATE TABLE IF NOT EXISTS issues (
81 "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
82 "authorDid" TEXT,
83 "rkey" TEXT,
84 "title" TEXT,
85 "body" TEXT,
86 "repo" TEXT,
87 "createdAt" integer NOT NULL,
88 UNIQUE(authorDid,rkey)
89 );`
90
91 slog.Info("Create issues table...")
92 statement, err := db.Prepare(createTableSQL)
93 if err != nil {
94 return fmt.Errorf("prepare DB statement to create issues table: %w", err)
95 }
96 _, err = statement.Exec()
97 if err != nil {
98 return fmt.Errorf("exec sql statement to create issues table: %w", err)
99 }
100 slog.Info("issues table created")
101
102 return nil
103}
104
105func createCommentsTable(db *sql.DB) error {
106 createTableSQL := `CREATE TABLE IF NOT EXISTS comments (
107 "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
108 "authorDid" TEXT,
109 "rkey" TEXT,
110 "body" TEXT,
111 "issue" TEXT,
112 "createdAt" integer NOT NULL,
113 UNIQUE(authorDid,rkey)
114 );`
115
116 slog.Info("Create comments table...")
117 statement, err := db.Prepare(createTableSQL)
118 if err != nil {
119 return fmt.Errorf("prepare DB statement to create comments table: %w", err)
120 }
121 _, err = statement.Exec()
122 if err != nil {
123 return fmt.Errorf("exec sql statement to create comments table: %w", err)
124 }
125 slog.Info("comments table created")
126
127 return nil
128}
129
130func createUsersTable(db *sql.DB) error {
131 createTableSQL := `CREATE TABLE IF NOT EXISTS users (
132 "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
133 "did" TEXT,
134 "handle" TEXT,
135 "convoId" TEXT,
136 "createdAt" integer NOT NULL,
137 UNIQUE(did)
138 );`
139
140 slog.Info("Create users table...")
141 statement, err := db.Prepare(createTableSQL)
142 if err != nil {
143 return fmt.Errorf("prepare DB statement to create users table: %w", err)
144 }
145 _, err = statement.Exec()
146 if err != nil {
147 return fmt.Errorf("exec sql statement to create users table: %w", err)
148 }
149 slog.Info("users table created")
150
151 return nil
152}
153
154// CreateIssue will insert a issue into a database
155func (d *Database) CreateIssue(issue Issue) error {
156 sql := `REPLACE INTO issues (authorDid, rkey, title, body, repo, createdAt) VALUES (?, ?, ?, ?, ?, ?);`
157 _, err := d.db.Exec(sql, issue.AuthorDID, issue.RKey, issue.Title, issue.Body, issue.Repo, issue.CreatedAt)
158 if err != nil {
159 return fmt.Errorf("exec insert issue: %w", err)
160 }
161 return nil
162}
163
164// CreateComment will insert a comment into a database
165func (d *Database) CreateComment(comment Comment) error {
166 sql := `REPLACE INTO comments (authorDid, rkey, body, issue, createdAt) VALUES (?, ?, ?, ?, ?);`
167 _, err := d.db.Exec(sql, comment.AuthorDID, comment.RKey, comment.Body, comment.Issue, comment.CreatedAt)
168 if err != nil {
169 return fmt.Errorf("exec insert comment: %w", err)
170 }
171 return nil
172}
173
174// CreateUser will insert a user into a database
175func (d *Database) CreateUser(user User) error {
176 sql := `REPLACE INTO users (did, handle, convoId, createdAt) VALUES (?, ?, ?, ?);`
177 _, err := d.db.Exec(sql, user.DID, user.Handle, user.ConvoID, user.CreatedAt)
178 if err != nil {
179 return fmt.Errorf("exec insert user: %w", err)
180 }
181 return nil
182}
183
184func (d *Database) GetIssues() ([]Issue, error) {
185 sql := "SELECT authorDid, rkey, title, body, repo, createdAt FROM issues;"
186 rows, err := d.db.Query(sql)
187 if err != nil {
188 return nil, fmt.Errorf("run query to get issues: %w", err)
189 }
190 defer rows.Close()
191
192 var results []Issue
193 for rows.Next() {
194 var issue Issue
195 if err := rows.Scan(&issue.AuthorDID, &issue.RKey, &issue.Title, &issue.Body, &issue.Repo, &issue.CreatedAt); err != nil {
196 return nil, fmt.Errorf("scan row: %w", err)
197 }
198
199 results = append(results, issue)
200 }
201 return results, nil
202}
203
204func (d *Database) GetComments() ([]Comment, error) {
205 sql := "SELECT authorDid, rkey, body, issue, createdAt FROM comments;"
206 rows, err := d.db.Query(sql)
207 if err != nil {
208 return nil, fmt.Errorf("run query to get comments: %w", err)
209 }
210 defer rows.Close()
211
212 var results []Comment
213 for rows.Next() {
214 var comment Comment
215 if err := rows.Scan(&comment.AuthorDID, &comment.RKey, &comment.Body, &comment.Issue, &comment.CreatedAt); err != nil {
216 return nil, fmt.Errorf("scan row: %w", err)
217 }
218
219 results = append(results, comment)
220 }
221 return results, nil
222}
223
224func (d *Database) GetUser(did string) (User, error) {
225 sql := "SELECT did, handle, convoId, createdAt FROM users WHERE did = ?;"
226 rows, err := d.db.Query(sql, did)
227 if err != nil {
228 return User{}, fmt.Errorf("run query to get user: %w", err)
229 }
230 defer rows.Close()
231
232 for rows.Next() {
233 var user User
234 if err := rows.Scan(&user.DID, &user.Handle, &user.ConvoID, &user.CreatedAt); err != nil {
235 return User{}, fmt.Errorf("scan row: %w", err)
236 }
237
238 return user, nil
239 }
240 return User{}, fmt.Errorf("user not found")
241}
242
243func (d *Database) GetUsers() ([]User, error) {
244 sql := "SELECT did, handle, convoId, createdAt FROM users;"
245 rows, err := d.db.Query(sql)
246 if err != nil {
247 return nil, fmt.Errorf("run query to get user: %w", err)
248 }
249 defer rows.Close()
250
251 var results []User
252 for rows.Next() {
253 var user User
254 if err := rows.Scan(&user.DID, &user.Handle, &user.ConvoID, &user.CreatedAt); err != nil {
255 return nil, fmt.Errorf("scan row: %w", err)
256 }
257 results = append(results, user)
258 }
259 return results, nil
260}
261
262func (d *Database) DeleteIssue(did, rkey string) error {
263 sql := "DELETE FROM issues WHERE authorDid = ? AND rkey = ?;"
264 _, err := d.db.Exec(sql, did, rkey)
265 if err != nil {
266 return fmt.Errorf("exec delete issue: %w", err)
267 }
268 return nil
269}
270
271func (d *Database) DeleteComment(did, rkey string) error {
272 sql := "DELETE FROM comments WHERE authorDid = ? AND rkey = ?;"
273 _, err := d.db.Exec(sql, did, rkey)
274 if err != nil {
275 return fmt.Errorf("exec delete issue: %w", err)
276 }
277 return nil
278}
279
280func (d *Database) DeleteCommentsForIssue(issueURI string) error {
281 sql := "DELETE FROM comments WHERE issue = ?;"
282 _, err := d.db.Exec(sql, issueURI)
283 if err != nil {
284 return fmt.Errorf("exec delete comments for issue")
285 }
286 return nil
287}
288
289func (d *Database) DeleteUser(did string) error {
290 sql := "DELETE FROM users WHERE did = ?;"
291 _, err := d.db.Exec(sql, did)
292 if err != nil {
293 return fmt.Errorf("exec delete user")
294 }
295 return nil
296}