1 package database
2
3 import (
4 "context"
5 "log"
6 "time"
7
8 . "codeberg.org/tslocum/sriracha/model"
9 "github.com/jackc/pgx/v5"
10 )
11
12 func (db *DB) AddNews(n *News) {
13 var accountID *int
14 if n.Account != nil {
15 accountID = &n.Account.ID
16 }
17 var share int
18 if n.Share {
19 share = 1
20 }
21 err := db.conn.QueryRow(context.Background(), "INSERT INTO news VALUES (DEFAULT, $1, $2, $3, $4, $5, $6, $7) RETURNING id",
22 accountID,
23 n.Timestamp,
24 time.Now().Unix(),
25 share,
26 n.Name,
27 n.Subject,
28 n.Message).Scan(&n.ID)
29 if err != nil {
30 log.Fatalf("failed to insert news: %s", err)
31 }
32 }
33
34 func (db *DB) NewsByID(id int) *News {
35 n := &News{}
36 accountID, err := scanNews(n, db.conn.QueryRow(context.Background(), "SELECT * FROM news WHERE id = $1", id))
37 if err == pgx.ErrNoRows {
38 return nil
39 } else if err != nil {
40 log.Fatalf("failed to select news: %s", err)
41 } else if accountID != 0 {
42 n.Account = db.AccountByID(accountID)
43 }
44 return n
45 }
46
47 func (db *DB) AllNews(onlyPublished bool) []*News {
48 var rows pgx.Rows
49 var err error
50 if onlyPublished {
51 rows, err = db.conn.Query(context.Background(), "SELECT * FROM news WHERE timestamp != 0 AND timestamp <= $1 ORDER BY timestamp DESC", time.Now().Unix())
52 } else {
53 rows, err = db.conn.Query(context.Background(), "SELECT * FROM news ORDER BY timestamp = 0, timestamp DESC")
54 }
55 if err != nil {
56 log.Fatalf("failed to select all news: %s", err)
57 }
58 var news []*News
59 var accountIDs []int
60 for rows.Next() {
61 n := &News{}
62 accountID, err := scanNews(n, rows)
63 if err != nil {
64 log.Fatalf("failed to select all news: %s", err)
65 }
66 news = append(news, n)
67 accountIDs = append(accountIDs, accountID)
68 }
69 for i, n := range news {
70 if accountIDs[i] == 0 {
71 continue
72 }
73 n.Account = db.AccountByID(accountIDs[i])
74 }
75 return news
76 }
77
78 func (db *DB) UpdateNews(n *News) {
79 if n.ID <= 0 {
80 log.Fatalf("invalid news ID %d", n.ID)
81 }
82 var share int
83 if n.Share {
84 share = 1
85 }
86 _, err := db.conn.Exec(context.Background(), "UPDATE news SET timestamp = $1, modified = $2, share = $3, name = $4, subject = $5, message = $6 WHERE id = $7",
87 n.Timestamp,
88 time.Now().Unix(),
89 share,
90 n.Name,
91 n.Subject,
92 n.Message,
93 n.ID)
94 if err != nil {
95 log.Fatalf("failed to update news: %s", err)
96 }
97 }
98
99 func (db *DB) DeleteNews(id int) {
100 if id == 0 {
101 return
102 }
103 _, err := db.conn.Exec(context.Background(), "DELETE FROM news WHERE id = $1", id)
104 if err != nil {
105 log.Fatalf("failed to delete news: %s", err)
106 }
107 }
108
109 func scanNews(n *News, row pgx.Row) (int, error) {
110 var accountID int
111 var share int
112 err := row.Scan(
113 &n.ID,
114 &accountID,
115 &n.Timestamp,
116 &n.Modified,
117 &share,
118 &n.Name,
119 &n.Subject,
120 &n.Message,
121 )
122 if share == 1 {
123 n.Share = true
124 }
125 return accountID, err
126 }
127
View as plain text