apps/easel/db.go 3.3 K raw
1
package main
2
3
import (
4
	"database/sql"
5
	"errors"
6
)
7
8
const easelSchema = `
9
CREATE TABLE IF NOT EXISTS daily_artworks (
10
    date              TEXT PRIMARY KEY,
11
    artwork_id        INTEGER NOT NULL,
12
    title             TEXT NOT NULL,
13
    artist_display    TEXT,
14
    artist_title      TEXT,
15
    date_display      TEXT,
16
    medium_display    TEXT,
17
    dimensions        TEXT,
18
    place_of_origin   TEXT,
19
    credit_line       TEXT,
20
    description       TEXT,
21
    short_description TEXT,
22
    image_id          TEXT NOT NULL,
23
    fetched_at        TEXT NOT NULL DEFAULT (datetime('now'))
24
);
25
CREATE INDEX IF NOT EXISTS idx_daily_artworks_artwork_id ON daily_artworks(artwork_id);
26
`
27
28
type DailyArtwork struct {
29
	Date             string
30
	ArtworkID        int64
31
	Title            string
32
	ArtistDisplay    sql.NullString
33
	ArtistTitle      sql.NullString
34
	DateDisplay      sql.NullString
35
	MediumDisplay    sql.NullString
36
	Dimensions       sql.NullString
37
	PlaceOfOrigin    sql.NullString
38
	CreditLine       sql.NullString
39
	Description      sql.NullString
40
	ShortDescription sql.NullString
41
	ImageID          string
42
	FetchedAt        string
43
}
44
45
const dailyCols = `date, artwork_id, title, artist_display, artist_title, date_display, medium_display, dimensions, place_of_origin, credit_line, description, short_description, image_id, fetched_at`
46
47
func scanDaily(s interface{ Scan(...any) error }) (*DailyArtwork, error) {
48
	var d DailyArtwork
49
	err := s.Scan(&d.Date, &d.ArtworkID, &d.Title, &d.ArtistDisplay, &d.ArtistTitle,
50
		&d.DateDisplay, &d.MediumDisplay, &d.Dimensions, &d.PlaceOfOrigin, &d.CreditLine,
51
		&d.Description, &d.ShortDescription, &d.ImageID, &d.FetchedAt)
52
	if errors.Is(err, sql.ErrNoRows) {
53
		return nil, nil
54
	}
55
	if err != nil {
56
		return nil, err
57
	}
58
	return &d, nil
59
}
60
61
func insertDaily(db *sql.DB, a *DailyArtwork) (bool, error) {
62
	res, err := db.Exec(
63
		`INSERT OR IGNORE INTO daily_artworks (`+dailyCols+`)
64
		 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,
65
		a.Date, a.ArtworkID, a.Title, a.ArtistDisplay, a.ArtistTitle,
66
		a.DateDisplay, a.MediumDisplay, a.Dimensions, a.PlaceOfOrigin, a.CreditLine,
67
		a.Description, a.ShortDescription, a.ImageID, a.FetchedAt,
68
	)
69
	if err != nil {
70
		return false, err
71
	}
72
	n, _ := res.RowsAffected()
73
	return n > 0, nil
74
}
75
76
func getDaily(db *sql.DB, date string) (*DailyArtwork, error) {
77
	return scanDaily(db.QueryRow(`SELECT `+dailyCols+` FROM daily_artworks WHERE date = ?`, date))
78
}
79
80
func listDaily(db *sql.DB, limit int) ([]DailyArtwork, error) {
81
	rows, err := db.Query(`SELECT `+dailyCols+` FROM daily_artworks ORDER BY date DESC LIMIT ?`, limit)
82
	if err != nil {
83
		return nil, err
84
	}
85
	defer rows.Close()
86
	var out []DailyArtwork
87
	for rows.Next() {
88
		d, err := scanDaily(rows)
89
		if err != nil {
90
			return nil, err
91
		}
92
		out = append(out, *d)
93
	}
94
	return out, rows.Err()
95
}
96
97
func artworkIDExists(db *sql.DB, id int64) (bool, error) {
98
	var n int64
99
	err := db.QueryRow(`SELECT COUNT(*) FROM daily_artworks WHERE artwork_id = ?`, id).Scan(&n)
100
	if err != nil {
101
		return false, err
102
	}
103
	return n > 0, nil
104
}
105
106
func missingDates(db *sql.DB, dates []string) ([]string, error) {
107
	out := []string{}
108
	for _, d := range dates {
109
		var n int64
110
		if err := db.QueryRow(`SELECT COUNT(*) FROM daily_artworks WHERE date = ?`, d).Scan(&n); err != nil {
111
			return nil, err
112
		}
113
		if n == 0 {
114
			out = append(out, d)
115
		}
116
	}
117
	return out, nil
118
}