database/kbcategory.go at master · TicketsBot-cloud/database · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
package database

import (
	"context"

	"github.com/jackc/pgx/v4"
	"github.com/jackc/pgx/v4/pgxpool"
)

type KBCategory struct {
	Id       int     `json:"id"`
	GuildId  uint64  `json:"guild_id,string"`
	Name     string  `json:"name"`
	Emoji    *string `json:"emoji"`
	Position int     `json:"position"`
}

type KBCategoriesTable struct {
	*pgxpool.Pool
}

func newKBCategories(db *pgxpool.Pool) *KBCategoriesTable {
	return &KBCategoriesTable{
		Pool: db,
	}
}

func (t KBCategoriesTable) Schema() string {
	return `
CREATE TABLE IF NOT EXISTS kb_categories(
	"id" SERIAL,
	"guild_id" int8 NOT NULL,
	"name" varchar(50) NOT NULL,
	"emoji" varchar(64) DEFAULT NULL,
	"position" int4 NOT NULL DEFAULT 0,
	PRIMARY KEY("id"),
	UNIQUE("guild_id", "name")
);
CREATE INDEX IF NOT EXISTS kb_categories_guild_id_idx ON kb_categories("guild_id");
`
}

func (t *KBCategoriesTable) GetByGuild(ctx context.Context, guildId uint64) ([]KBCategory, error) {
	query := `
SELECT "id", "guild_id", "name", "emoji", "position"
FROM kb_categories
WHERE "guild_id" = $1
ORDER BY "position" ASC, "id" ASC;
`

	rows, err := t.Query(ctx, query, guildId)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var categories []KBCategory
	for rows.Next() {
		var cat KBCategory
		if err := rows.Scan(&cat.Id, &cat.GuildId, &cat.Name, &cat.Emoji, &cat.Position); err != nil {
			return nil, err
		}

		categories = append(categories, cat)
	}

	return categories, nil
}

func (t *KBCategoriesTable) Get(ctx context.Context, id int) (KBCategory, bool, error) {
	query := `
SELECT "id", "guild_id", "name", "emoji", "position"
FROM kb_categories
WHERE "id" = $1;
`

	var cat KBCategory
	err := t.QueryRow(ctx, query, id).Scan(&cat.Id, &cat.GuildId, &cat.Name, &cat.Emoji, &cat.Position)
	if err != nil {
		if err == pgx.ErrNoRows {
			return KBCategory{}, false, nil
		}
		return KBCategory{}, false, err
	}

	return cat, true, nil
}

func (t *KBCategoriesTable) Create(ctx context.Context, cat KBCategory) (int, error) {
	query := `
INSERT INTO kb_categories("guild_id", "name", "emoji", "position")
VALUES($1, $2, $3, $4)
RETURNING "id";
`

	var id int
	err := t.QueryRow(ctx, query, cat.GuildId, cat.Name, cat.Emoji, cat.Position).Scan(&id)
	return id, err
}

func (t *KBCategoriesTable) Update(ctx context.Context, cat KBCategory) error {
	query := `
UPDATE kb_categories
SET "name" = $2, "emoji" = $3, "position" = $4
WHERE "id" = $1 AND "guild_id" = $5;
`

	_, err := t.Exec(ctx, query, cat.Id, cat.Name, cat.Emoji, cat.Position, cat.GuildId)
	return err
}

func (t *KBCategoriesTable) Delete(ctx context.Context, guildId uint64, id int) error {
	query := `DELETE FROM kb_categories WHERE "id" = $1 AND "guild_id" = $2;`
	_, err := t.Exec(ctx, query, id, guildId)
	return err
}