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 := `REPLACE INTO issues (authorDid, rkey, title, body, repo, createdAt) VALUES (?, ?, ?, ?, ?, ?);`
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 := `REPLACE INTO comments (authorDid, rkey, body, issue, replyTo, createdAt) VALUES (?, ?, ?, ?, ?, ?);`
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}
145
146func (d *Database) GetIssues() ([]Issue, error) {
147 sql := "SELECT authorDid, rkey, title, body, repo, createdAt FROM issues;"
148 rows, err := d.db.Query(sql)
149 if err != nil {
150 return nil, fmt.Errorf("run query to get issues: %w", err)
151 }
152 defer rows.Close()
153
154 var results []Issue
155 for rows.Next() {
156 var issue Issue
157 if err := rows.Scan(&issue.AuthorDID, &issue.RKey, &issue.Title, &issue.Body, &issue.Repo, &issue.CreatedAt); err != nil {
158 return nil, fmt.Errorf("scan row: %w", err)
159 }
160
161 results = append(results, issue)
162 }
163 return results, nil
164}
165
166func (d *Database) GetComments() ([]Comment, error) {
167 sql := "SELECT authorDid, rkey, body, issue, replyTo, createdAt FROM comments;"
168 rows, err := d.db.Query(sql)
169 if err != nil {
170 return nil, fmt.Errorf("run query to get comments: %w", err)
171 }
172 defer rows.Close()
173
174 var results []Comment
175 for rows.Next() {
176 var comment Comment
177 if err := rows.Scan(&comment.AuthorDID, &comment.RKey, &comment.Body, &comment.Issue, &comment.ReplyTo, &comment.CreatedAt); err != nil {
178 return nil, fmt.Errorf("scan row: %w", err)
179 }
180
181 results = append(results, comment)
182 }
183 return results, nil
184}