1 package database
2
3 import (
4 "context"
5 "log"
6
7 . "codeberg.org/tslocum/sriracha/model"
8 "github.com/jackc/pgx/v5"
9 )
10
11 func (db *DB) AddKeyword(k *Keyword) {
12 _, err := db.conn.Exec(context.Background(), "INSERT INTO keyword VALUES (DEFAULT, $1, $2)",
13 k.Text,
14 k.Action,
15 )
16 if err != nil {
17 log.Fatalf("failed to insert keyword: %s", err)
18 }
19 err = db.conn.QueryRow(context.Background(), "SELECT id FROM keyword WHERE text = $1", k.Text).Scan(&k.ID)
20 if err != nil {
21 log.Fatalf("failed to select number of super-administrator accounts: %s", err)
22 } else if k.ID == 0 {
23 log.Fatal("failed to select id of added keyword")
24 }
25 db.updateKeywordBoards(k)
26 }
27
28 func (db *DB) fetchKeywordBoards(k *Keyword) {
29 k.Boards = nil
30
31 rows, err := db.conn.Query(context.Background(), "SELECT board FROM keyword_board WHERE keyword = $1", k.ID)
32 if err != nil {
33 log.Fatalf("failed to select keyword boards: %s", err)
34 }
35 var ids []int
36 for rows.Next() {
37 var id int
38 err := rows.Scan(&id)
39 if err != nil {
40 log.Fatalf("failed to select keyword boards: %s", err)
41 }
42 ids = append(ids, id)
43 }
44
45 for _, id := range ids {
46 b := db.BoardByID(id)
47 k.Boards = append(k.Boards, b)
48 }
49 }
50
51 func (db *DB) updateKeywordBoards(k *Keyword) {
52 _, err := db.conn.Exec(context.Background(), "DELETE FROM keyword_board WHERE keyword = $1", k.ID)
53 if err != nil {
54 log.Fatalf("failed to update keyword boards: %s", err)
55 }
56 for _, b := range k.Boards {
57 _, err = db.conn.Exec(context.Background(), "INSERT INTO keyword_board VALUES ($1, $2)", k.ID, b.ID)
58 if err != nil {
59 log.Fatalf("failed to update keyword boards: %s", err)
60 }
61 }
62 }
63
64 func (db *DB) KeywordByID(id int) *Keyword {
65 k := &Keyword{}
66 err := scanKeyword(k, db.conn.QueryRow(context.Background(), "SELECT * FROM keyword WHERE id = $1", id))
67 if err == pgx.ErrNoRows {
68 return nil
69 } else if err != nil {
70 log.Fatalf("failed to select keyword: %s", err)
71 }
72 db.fetchKeywordBoards(k)
73 return k
74 }
75
76 func (db *DB) KeywordByText(text string) *Keyword {
77 k := &Keyword{}
78 err := scanKeyword(k, db.conn.QueryRow(context.Background(), "SELECT * FROM keyword WHERE text = $1", text))
79 if err == pgx.ErrNoRows {
80 return nil
81 } else if err != nil {
82 log.Fatalf("failed to select keyword: %s", err)
83 }
84 db.fetchKeywordBoards(k)
85 return k
86 }
87
88 func (db *DB) AllKeywords() []*Keyword {
89 rows, err := db.conn.Query(context.Background(), "SELECT * FROM keyword ORDER BY text ASC")
90 if err != nil {
91 log.Fatalf("failed to select all keywords: %s", err)
92 }
93 var keywords []*Keyword
94 for rows.Next() {
95 k := &Keyword{}
96 err := scanKeyword(k, rows)
97 if err != nil {
98 log.Fatalf("failed to select all keywords: %s", err)
99 }
100 keywords = append(keywords, k)
101 }
102 for _, k := range keywords {
103 db.fetchKeywordBoards(k)
104 }
105 return keywords
106 }
107
108 func (db *DB) UpdateKeyword(k *Keyword) {
109 if k.ID <= 0 {
110 log.Fatalf("invalid keyword ID %d", k.ID)
111 }
112 _, err := db.conn.Exec(context.Background(), "UPDATE keyword SET text = $1, action = $2 WHERE id = $3",
113 k.Text,
114 k.Action,
115 k.ID,
116 )
117 if err != nil {
118 log.Fatalf("failed to update keyword: %s", err)
119 }
120 db.updateKeywordBoards(k)
121 }
122
123 func (db *DB) DeleteKeyword(id int) {
124 if id == 0 {
125 return
126 }
127 _, err := db.conn.Exec(context.Background(), "DELETE FROM keyword WHERE id = $1", id)
128 if err != nil {
129 log.Fatalf("failed to delete keyword: %s", err)
130 }
131 }
132
133 func scanKeyword(k *Keyword, row pgx.Row) error {
134 return row.Scan(
135 &k.ID,
136 &k.Text,
137 &k.Action,
138 )
139 }
140
View as plain text