apps/cellar/db.go 5.6 K raw
1
package main
2
3
import (
4
	"database/sql"
5
	"errors"
6
7
	"github.com/stevedylandev/andromeda/pkg/auth"
8
)
9
10
const cellarSchema = `
11
CREATE TABLE IF NOT EXISTS wines (
12
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
13
    short_id        TEXT NOT NULL UNIQUE,
14
    name            TEXT NOT NULL,
15
    origin          TEXT NOT NULL,
16
    grape           TEXT NOT NULL,
17
    notes           TEXT NOT NULL,
18
    image           BLOB,
19
    image_mime      TEXT,
20
    sweetness       INTEGER NOT NULL CHECK(sweetness BETWEEN 1 AND 5),
21
    acidity         INTEGER NOT NULL CHECK(acidity BETWEEN 1 AND 5),
22
    tannin          INTEGER NOT NULL CHECK(tannin BETWEEN 1 AND 5),
23
    alcohol         INTEGER NOT NULL CHECK(alcohol BETWEEN 1 AND 5),
24
    body            INTEGER NOT NULL CHECK(body BETWEEN 1 AND 5),
25
    clarity         INTEGER NOT NULL DEFAULT 3,
26
    color_intensity INTEGER NOT NULL DEFAULT 3,
27
    aroma_intensity INTEGER NOT NULL DEFAULT 3,
28
    nose_complexity INTEGER NOT NULL DEFAULT 3,
29
    background      TEXT NOT NULL DEFAULT '',
30
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
31
    wishlist        INTEGER NOT NULL DEFAULT 0
32
);
33
`
34
35
const wineCols = `id, short_id, name, origin, grape, notes, (image IS NOT NULL) AS has_image, COALESCE(image_mime, ''), sweetness, acidity, tannin, alcohol, body, clarity, color_intensity, aroma_intensity, nose_complexity, background, created_at, wishlist`
36
37
func scanWine(s interface{ Scan(...any) error }) (*Wine, error) {
38
	var w Wine
39
	var hasImage int
40
	err := s.Scan(&w.ID, &w.ShortID, &w.Name, &w.Origin, &w.Grape, &w.Notes,
41
		&hasImage, &w.ImageMime,
42
		&w.Sweetness, &w.Acidity, &w.Tannin, &w.Alcohol, &w.Body,
43
		&w.Clarity, &w.ColorIntensity, &w.AromaIntensity, &w.NoseComplexity,
44
		&w.Background, &w.CreatedAt, &w.Wishlist)
45
	if errors.Is(err, sql.ErrNoRows) {
46
		return nil, nil
47
	}
48
	if err != nil {
49
		return nil, err
50
	}
51
	w.HasImage = hasImage != 0
52
	return &w, nil
53
}
54
55
func createWine(db *sql.DB, in WineInput, wishlist bool) (*Wine, error) {
56
	shortID, err := auth.GenerateShortID(10)
57
	if err != nil {
58
		return nil, err
59
	}
60
	res, err := db.Exec(
61
		`INSERT INTO wines (short_id, name, origin, grape, notes, sweetness, acidity, tannin, alcohol, body, clarity, color_intensity, aroma_intensity, nose_complexity, background, wishlist)
62
		 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
63
		shortID, in.Name, in.Origin, in.Grape, in.Notes,
64
		in.Sweetness, in.Acidity, in.Tannin, in.Alcohol, in.Body,
65
		in.Clarity, in.ColorIntensity, in.AromaIntensity, in.NoseComplexity,
66
		in.Background, boolToInt(wishlist),
67
	)
68
	if err != nil {
69
		return nil, err
70
	}
71
	id, _ := res.LastInsertId()
72
	return scanWine(db.QueryRow(`SELECT `+wineCols+` FROM wines WHERE id = ?`, id))
73
}
74
75
func getCellarWines(db *sql.DB) ([]Wine, error) {
76
	rows, err := db.Query(`SELECT ` + wineCols + ` FROM wines WHERE wishlist = 0 ORDER BY id DESC`)
77
	if err != nil {
78
		return nil, err
79
	}
80
	defer rows.Close()
81
	out := []Wine{}
82
	for rows.Next() {
83
		w, err := scanWine(rows)
84
		if err != nil {
85
			return nil, err
86
		}
87
		out = append(out, *w)
88
	}
89
	return out, rows.Err()
90
}
91
92
func getWishlistWines(db *sql.DB) ([]Wine, error) {
93
	rows, err := db.Query(`SELECT ` + wineCols + ` FROM wines WHERE wishlist = 1 ORDER BY id DESC`)
94
	if err != nil {
95
		return nil, err
96
	}
97
	defer rows.Close()
98
	out := []Wine{}
99
	for rows.Next() {
100
		w, err := scanWine(rows)
101
		if err != nil {
102
			return nil, err
103
		}
104
		out = append(out, *w)
105
	}
106
	return out, rows.Err()
107
}
108
109
func getWineByShortID(db *sql.DB, shortID string) (*Wine, error) {
110
	return scanWine(db.QueryRow(`SELECT `+wineCols+` FROM wines WHERE short_id = ?`, shortID))
111
}
112
113
func getWineImage(db *sql.DB, shortID string) ([]byte, string, error) {
114
	var img []byte
115
	var mime string
116
	err := db.QueryRow(`SELECT image, COALESCE(image_mime, '') FROM wines WHERE short_id = ? AND image IS NOT NULL`, shortID).Scan(&img, &mime)
117
	if errors.Is(err, sql.ErrNoRows) {
118
		return nil, "", nil
119
	}
120
	if err != nil {
121
		return nil, "", err
122
	}
123
	return img, mime, nil
124
}
125
126
func updateWine(db *sql.DB, shortID string, in WineInput) (*Wine, error) {
127
	res, err := db.Exec(
128
		`UPDATE wines SET name = ?, origin = ?, grape = ?, notes = ?,
129
		 sweetness = ?, acidity = ?, tannin = ?, alcohol = ?, body = ?,
130
		 clarity = ?, color_intensity = ?, aroma_intensity = ?, nose_complexity = ?,
131
		 background = ? WHERE short_id = ?`,
132
		in.Name, in.Origin, in.Grape, in.Notes,
133
		in.Sweetness, in.Acidity, in.Tannin, in.Alcohol, in.Body,
134
		in.Clarity, in.ColorIntensity, in.AromaIntensity, in.NoseComplexity,
135
		in.Background, shortID,
136
	)
137
	if err != nil {
138
		return nil, err
139
	}
140
	if n, _ := res.RowsAffected(); n == 0 {
141
		return nil, nil
142
	}
143
	return getWineByShortID(db, shortID)
144
}
145
146
func updateWishlistWine(db *sql.DB, shortID, name, origin, grape, notes, background string) (*Wine, error) {
147
	res, err := db.Exec(
148
		`UPDATE wines SET name = ?, origin = ?, grape = ?, notes = ?, background = ? WHERE short_id = ? AND wishlist = 1`,
149
		name, origin, grape, notes, background, shortID,
150
	)
151
	if err != nil {
152
		return nil, err
153
	}
154
	if n, _ := res.RowsAffected(); n == 0 {
155
		return nil, nil
156
	}
157
	return getWineByShortID(db, shortID)
158
}
159
160
func promoteWine(db *sql.DB, shortID string) (bool, error) {
161
	res, err := db.Exec(`UPDATE wines SET wishlist = 0 WHERE short_id = ? AND wishlist = 1`, shortID)
162
	if err != nil {
163
		return false, err
164
	}
165
	n, _ := res.RowsAffected()
166
	return n > 0, nil
167
}
168
169
func updateWineImage(db *sql.DB, shortID string, image []byte, mime string) error {
170
	_, err := db.Exec(`UPDATE wines SET image = ?, image_mime = ? WHERE short_id = ?`, image, mime, shortID)
171
	return err
172
}
173
174
func deleteWine(db *sql.DB, shortID string) error {
175
	_, err := db.Exec(`DELETE FROM wines WHERE short_id = ?`, shortID)
176
	return err
177
}
178
179
func boolToInt(b bool) int {
180
	if b {
181
		return 1
182
	}
183
	return 0
184
}