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) AddCategory(c *Category) {
12 var parent *int
13 if c.Parent != nil {
14 parent = &c.Parent.ID
15 }
16 err := db.conn.QueryRow(context.Background(), "INSERT INTO category VALUES (DEFAULT, $1, $2, $3, $4) RETURNING id",
17 parent,
18 c.Sort,
19 c.Name,
20 c.Description,
21 ).Scan(&c.ID)
22 if err != nil {
23 log.Fatalf("failed to insert category: %s", err)
24 }
25 db.fetchCategoryData(c, parent)
26 }
27
28 func (db *DB) fetchCategoryData(c *Category, parent *int) {
29 c.Parent = nil
30 if parent != nil && *parent != 0 {
31 c.Parent = db.CategoryByID(*parent)
32 }
33
34 c.Boards = nil
35 rows, err := db.conn.Query(context.Background(), "SELECT board FROM category_board WHERE category = $1 ORDER BY sort ASC", c.ID)
36 if err != nil {
37 log.Fatalf("failed to select category boards: %s", err)
38 }
39 var ids []int
40 for rows.Next() {
41 var id int
42 err := rows.Scan(&id)
43 if err != nil {
44 log.Fatalf("failed to select category boards: %s", err)
45 }
46 ids = append(ids, id)
47 }
48 for _, id := range ids {
49 b := db.BoardByID(id)
50 c.Boards = append(c.Boards, b)
51 }
52 }
53
54 func (db *DB) updateCategoryBoards(c *Category) {
55 _, err := db.conn.Exec(context.Background(), "DELETE FROM category_board WHERE category = $1", c.ID)
56 if err != nil {
57 log.Fatalf("failed to update category boards: %s", err)
58 }
59 for i, b := range c.Boards {
60 _, err = db.conn.Exec(context.Background(), "INSERT INTO category_board VALUES ($1, $2, $3)", c.ID, b.ID, i)
61 if err != nil {
62 log.Fatalf("failed to update category boards: %s", err)
63 }
64 }
65 }
66
67 func (db *DB) CategoryByID(id int) *Category {
68 c := &Category{}
69 err, parent := scanCategory(c, db.conn.QueryRow(context.Background(), "SELECT * FROM category WHERE id = $1", id))
70 if err == pgx.ErrNoRows {
71 return nil
72 } else if err != nil {
73 log.Fatalf("failed to select category: %s", err)
74 }
75 db.fetchCategoryData(c, parent)
76 return c
77 }
78
79 func (db *DB) ChildCategories(id int) []*Category {
80 rows, err := db.conn.Query(context.Background(), "SELECT * FROM category WHERE parent = $1 ORDER BY parent ASC NULLS FIRST, sort ASC", id)
81 if err != nil {
82 log.Fatalf("failed to select all categories: %s", err)
83 }
84 var categories []*Category
85 var parents []*int
86 for rows.Next() {
87 c := &Category{}
88 err, parent := scanCategory(c, rows)
89 if err != nil {
90 log.Fatalf("failed to select child categories: %s", err)
91 }
92 categories = append(categories, c)
93 parents = append(parents, parent)
94 }
95 for i, c := range categories {
96 db.fetchCategoryData(c, parents[i])
97 c.Categories = db.ChildCategories(c.ID)
98 }
99 return categories
100 }
101
102 func (db *DB) AllCategories() []*Category {
103 rows, err := db.conn.Query(context.Background(), "SELECT * FROM category ORDER BY parent ASC NULLS FIRST, sort ASC")
104 if err != nil {
105 log.Fatalf("failed to select all categories: %s", err)
106 }
107 var categories []*Category
108 var parents []*int
109 for rows.Next() {
110 c := &Category{}
111 err, parent := scanCategory(c, rows)
112 if err != nil {
113 log.Fatalf("failed to select all categories: %s", err)
114 }
115 categories = append(categories, c)
116 parents = append(parents, parent)
117 }
118 for i, c := range categories {
119 db.fetchCategoryData(c, parents[i])
120 c.Categories = db.ChildCategories(c.ID)
121 }
122 return categories
123 }
124
125 func (db *DB) UpdateCategory(c *Category) {
126 if c.ID <= 0 {
127 log.Fatalf("invalid category ID %d", c.ID)
128 }
129 var parent *int
130 if c.Parent != nil {
131 parent = &c.Parent.ID
132 }
133 _, err := db.conn.Exec(context.Background(), "UPDATE category SET parent = $1, sort = $2, name = $3, description = $4 WHERE id = $5",
134 parent,
135 c.Sort,
136 c.Name,
137 c.Description,
138 c.ID,
139 )
140 if err != nil {
141 log.Fatalf("failed to update category: %s", err)
142 }
143 db.updateCategoryBoards(c)
144 }
145
146 func (db *DB) DeleteCategory(id int) {
147 if id == 0 {
148 return
149 }
150 _, err := db.conn.Exec(context.Background(), "DELETE FROM category WHERE id = $1", id)
151 if err != nil {
152 log.Fatalf("failed to delete category: %s", err)
153 }
154 }
155
156 func scanCategory(c *Category, row pgx.Row) (error, *int) {
157 var parent *int
158 err := row.Scan(
159 &c.ID,
160 &parent,
161 &c.Sort,
162 &c.Name,
163 &c.Description,
164 )
165 return err, parent
166 }
167
View as plain text