apps/feeds/db_categories.go 1.8 K raw
1
package main
2
3
import (
4
	"database/sql"
5
	"errors"
6
	"strings"
7
)
8
9
type Category struct {
10
	ID        int64
11
	Name      string
12
	CreatedAt string
13
}
14
15
func listCategories(db *sql.DB) ([]Category, error) {
16
	rows, err := db.Query(`SELECT id, name, created_at FROM categories ORDER BY name ASC`)
17
	if err != nil {
18
		return nil, err
19
	}
20
	defer rows.Close()
21
	var out []Category
22
	for rows.Next() {
23
		var c Category
24
		if err := rows.Scan(&c.ID, &c.Name, &c.CreatedAt); err != nil {
25
			return nil, err
26
		}
27
		out = append(out, c)
28
	}
29
	return out, rows.Err()
30
}
31
32
func getOrCreateCategory(db *sql.DB, name string) (*Category, error) {
33
	name = strings.TrimSpace(name)
34
	if name == "" {
35
		return nil, nil
36
	}
37
	var c Category
38
	err := db.QueryRow(`SELECT id, name, created_at FROM categories WHERE name = ?`, name).Scan(&c.ID, &c.Name, &c.CreatedAt)
39
	if err == nil {
40
		return &c, nil
41
	}
42
	if !errors.Is(err, sql.ErrNoRows) {
43
		return nil, err
44
	}
45
	res, err := db.Exec(`INSERT INTO categories (name) VALUES (?)`, name)
46
	if err != nil {
47
		var existing Category
48
		if err2 := db.QueryRow(`SELECT id, name, created_at FROM categories WHERE name = ?`, name).Scan(&existing.ID, &existing.Name, &existing.CreatedAt); err2 == nil {
49
			return &existing, nil
50
		}
51
		return nil, err
52
	}
53
	id, _ := res.LastInsertId()
54
	return getCategory(db, id)
55
}
56
57
func getCategory(db *sql.DB, id int64) (*Category, error) {
58
	var c Category
59
	err := db.QueryRow(`SELECT id, name, created_at FROM categories WHERE id = ?`, id).Scan(&c.ID, &c.Name, &c.CreatedAt)
60
	if errors.Is(err, sql.ErrNoRows) {
61
		return nil, nil
62
	}
63
	if err != nil {
64
		return nil, err
65
	}
66
	return &c, nil
67
}
68
69
func deleteCategory(db *sql.DB, id int64) (bool, error) {
70
	res, err := db.Exec(`DELETE FROM categories WHERE id = ?`, id)
71
	if err != nil {
72
		return false, err
73
	}
74
	n, _ := res.RowsAffected()
75
	return n > 0, nil
76
}