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) fetchBannerBoards(b *Banner) {
12 b.Boards = nil
13
14 rows, err := db.conn.Query(context.Background(), "SELECT board FROM banner_board WHERE banner = $1", b.ID)
15 if err != nil {
16 log.Fatalf("failed to select banner boards: %s", err)
17 }
18 var ids []int
19 for rows.Next() {
20 var id int
21 err := rows.Scan(&id)
22 if err != nil {
23 log.Fatalf("failed to select banner boards: %s", err)
24 }
25 ids = append(ids, id)
26 }
27
28 for _, id := range ids {
29 board := db.BoardByID(id)
30 b.Boards = append(b.Boards, board)
31 }
32 }
33
34 func (db *DB) updateBannerBoards(b *Banner) {
35 _, err := db.conn.Exec(context.Background(), "DELETE FROM banner_board WHERE banner = $1", b.ID)
36 if err != nil {
37 log.Fatalf("failed to update banner boards: %s", err)
38 }
39 for _, board := range b.Boards {
40 _, err = db.conn.Exec(context.Background(), "INSERT INTO banner_board VALUES ($1, $2)", b.ID, board.ID)
41 if err != nil {
42 log.Fatalf("failed to update banner boards: %s", err)
43 }
44 }
45 }
46
47 func (db *DB) AddBanner(b *Banner) {
48 var overboard int
49 if b.Overboard {
50 overboard = 1
51 }
52 var news int
53 if b.News {
54 news = 1
55 }
56 var pages int
57 if b.Pages {
58 pages = 1
59 }
60 _, err := db.conn.Exec(context.Background(), "INSERT INTO banner VALUES (DEFAULT, $1, $2, $3, $4, $5, $6)",
61 b.Name,
62 b.Width,
63 b.Height,
64 overboard,
65 news,
66 pages,
67 )
68 if err != nil {
69 log.Fatalf("failed to insert banner: %s", err)
70 }
71 err = db.conn.QueryRow(context.Background(), "SELECT id FROM banner WHERE name = $1", b.Name).Scan(&b.ID)
72 if err != nil {
73 log.Fatalf("failed to select id of added banner: %s", err)
74 } else if b.ID == 0 {
75 log.Fatal("failed to select id of added banner")
76 }
77 db.updateBannerBoards(b)
78 }
79
80 func (db *DB) BannerByID(id int) *Banner {
81 b := &Banner{}
82 err := scanBanner(b, db.conn.QueryRow(context.Background(), "SELECT * FROM banner WHERE id = $1", id))
83 if err == pgx.ErrNoRows {
84 return nil
85 } else if err != nil {
86 log.Fatalf("failed to select banner: %s", err)
87 }
88 db.fetchBannerBoards(b)
89 return b
90 }
91
92 func (db *DB) BannerByName(name string) *Banner {
93 b := &Banner{}
94 err := scanBanner(b, db.conn.QueryRow(context.Background(), "SELECT * FROM banner WHERE name = $1", name))
95 if err == pgx.ErrNoRows {
96 return nil
97 } else if err != nil {
98 log.Fatalf("failed to select banner: %s", err)
99 }
100 db.fetchBannerBoards(b)
101 return b
102 }
103
104 func (db *DB) AllBanners() []*Banner {
105 rows, err := db.conn.Query(context.Background(), "SELECT * FROM banner ORDER BY name ASC")
106 if err != nil {
107 log.Fatalf("failed to select all banners: %s", err)
108 }
109 var banners []*Banner
110 for rows.Next() {
111 b := &Banner{}
112 err := scanBanner(b, rows)
113 if err != nil {
114 log.Fatalf("failed to select all banners: %s", err)
115 }
116 banners = append(banners, b)
117 }
118 for _, b := range banners {
119 db.fetchBannerBoards(b)
120 }
121 return banners
122 }
123
124 func (db *DB) UpdateBanner(b *Banner) {
125 if b.ID <= 0 {
126 log.Fatalf("invalid banner ID %d", b.ID)
127 }
128 var overboard int
129 if b.Overboard {
130 overboard = 1
131 }
132 var news int
133 if b.News {
134 news = 1
135 }
136 var pages int
137 if b.Pages {
138 pages = 1
139 }
140 _, err := db.conn.Exec(context.Background(), "UPDATE banner SET name = $1, width = $2, height = $3, overboard = $4, news = $5, pages = $6 WHERE id = $7",
141 b.Name,
142 b.Width,
143 b.Height,
144 overboard,
145 news,
146 pages,
147 b.ID,
148 )
149 if err != nil {
150 log.Fatalf("failed to update banner: %s", err)
151 }
152 db.updateBannerBoards(b)
153 }
154
155 func (db *DB) DeleteBanner(id int) {
156 if id == 0 {
157 return
158 }
159 _, err := db.conn.Exec(context.Background(), "DELETE FROM banner WHERE id = $1", id)
160 if err != nil {
161 log.Fatalf("failed to delete banner: %s", err)
162 }
163 }
164
165 func scanBanner(b *Banner, row pgx.Row) error {
166 var overboard, news, pages int
167 err := row.Scan(
168 &b.ID,
169 &b.Name,
170 &b.Width,
171 &b.Height,
172 &overboard,
173 &news,
174 &pages,
175 )
176 if err != nil {
177 return err
178 }
179 b.Overboard = overboard == 1
180 b.News = news == 1
181 b.Pages = pages == 1
182 return nil
183 }
184
View as plain text