Something went wrong. Try again.
Something went wrong. Try again.
tangled-alert-bot database.go
5.4 kB · 210 lines
Go
at main
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211package tangledalertbot
import ( "database/sql" "errors" "fmt" "log/slog" "os"
_ "github.com/glebarez/go-sqlite")
// Database is a sqlite databasetype Database struct { db *sql.DB}
// NewDatabase will open a new database. It will ping the database to ensure it is available and error if notfunc NewDatabase(dbPath string) (*Database, error) { if dbPath != ":memory:" { err := createDbFile(dbPath) if err != nil { return nil, fmt.Errorf("create db file: %w", err) } }
db, err := sql.Open("sqlite", dbPath) if err != nil { return nil, fmt.Errorf("open database: %w", err) }
err = db.Ping() if err != nil { return nil, fmt.Errorf("ping db: %w", err) }
err = createIssuesTable(db) if err != nil { return nil, fmt.Errorf("creating issues table: %w", err) }
err = createCommentsTable(db) if err != nil { return nil, fmt.Errorf("creating comments table: %w", err) }
return &Database{db: db}, nil}
// Close will cleanly stop the database connectionfunc (d *Database) Close() { err := d.db.Close() if err != nil { slog.Error("failed to close db", "error", err) }}
func createDbFile(dbFilename string) error { if _, err := os.Stat(dbFilename); !errors.Is(err, os.ErrNotExist) { return nil }
f, err := os.Create(dbFilename) if err != nil { return fmt.Errorf("create db file : %w", err) } err = f.Close() if err != nil { return fmt.Errorf("failed to close DB file: %w", err) } return nil}
func createIssuesTable(db *sql.DB) error { createTableSQL := `CREATE TABLE IF NOT EXISTS issues ( "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "authorDid" TEXT, "rkey" TEXT, "title" TEXT, "body" TEXT, "repo" TEXT, "createdAt" integer NOT NULL, UNIQUE(authorDid,rkey) );`
slog.Info("Create issues table...") statement, err := db.Prepare(createTableSQL) if err != nil { return fmt.Errorf("prepare DB statement to create issues table: %w", err) } _, err = statement.Exec() if err != nil { return fmt.Errorf("exec sql statement to create issues table: %w", err) } slog.Info("issues table created")
return nil}
func createCommentsTable(db *sql.DB) error { createTableSQL := `CREATE TABLE IF NOT EXISTS comments ( "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "authorDid" TEXT, "rkey" TEXT, "body" TEXT, "issue" TEXT, "createdAt" integer NOT NULL, UNIQUE(authorDid,rkey) );`
slog.Info("Create comments table...") statement, err := db.Prepare(createTableSQL) if err != nil { return fmt.Errorf("prepare DB statement to create comments table: %w", err) } _, err = statement.Exec() if err != nil { return fmt.Errorf("exec sql statement to create comments table: %w", err) } slog.Info("comments table created")
return nil}
// CreateIssue will insert a issue into a databasefunc (d *Database) CreateIssue(issue Issue) error { sql := `REPLACE INTO issues (authorDid, rkey, title, body, repo, createdAt) VALUES (?, ?, ?, ?, ?, ?);` _, err := d.db.Exec(sql, issue.AuthorDID, issue.RKey, issue.Title, issue.Body, issue.Repo, issue.CreatedAt) if err != nil { return fmt.Errorf("exec insert issue: %w", err) } return nil}
// CreateComment will insert a comment into a databasefunc (d *Database) CreateComment(comment Comment) error { sql := `REPLACE INTO comments (authorDid, rkey, body, issue, createdAt) VALUES (?, ?, ?, ?, ?);` _, err := d.db.Exec(sql, comment.AuthorDID, comment.RKey, comment.Body, comment.Issue, comment.CreatedAt) if err != nil { return fmt.Errorf("exec insert comment: %w", err) } return nil}
func (d *Database) GetIssues() ([]Issue, error) { sql := "SELECT authorDid, rkey, title, body, repo, createdAt FROM issues;" rows, err := d.db.Query(sql) if err != nil { return nil, fmt.Errorf("run query to get issues: %w", err) } defer rows.Close()
var results []Issue for rows.Next() { var issue Issue if err := rows.Scan(&issue.AuthorDID, &issue.RKey, &issue.Title, &issue.Body, &issue.Repo, &issue.CreatedAt); err != nil { return nil, fmt.Errorf("scan row: %w", err) }
results = append(results, issue) } return results, nil}
func (d *Database) GetComments() ([]Comment, error) { sql := "SELECT authorDid, rkey, body, issue, createdAt FROM comments;" rows, err := d.db.Query(sql) if err != nil { return nil, fmt.Errorf("run query to get comments: %w", err) } defer rows.Close()
var results []Comment for rows.Next() { var comment Comment if err := rows.Scan(&comment.AuthorDID, &comment.RKey, &comment.Body, &comment.Issue, &comment.CreatedAt); err != nil { return nil, fmt.Errorf("scan row: %w", err) }
results = append(results, comment) } return results, nil}
func (d *Database) DeleteIssue(did, rkey string) error { sql := "DELETE FROM issues WHERE authorDid = ? AND rkey = ?;" _, err := d.db.Exec(sql, did, rkey) if err != nil { return fmt.Errorf("exec delete issue: %w", err) } return nil}
func (d *Database) DeleteComment(did, rkey string) error { sql := "DELETE FROM comments WHERE authorDid = ? AND rkey = ?;" _, err := d.db.Exec(sql, did, rkey) if err != nil { return fmt.Errorf("exec delete issue: %w", err) } return nil}
func (d *Database) DeleteCommentsForIssue(issueURI string) error { sql := "DELETE FROM comments WHERE issue = ?;" _, err := d.db.Exec(sql, issueURI) if err != nil { return fmt.Errorf("exec delete comments for issue") } return nil}