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
|
package db
import (
"database/sql"
"encoding/json"
"strings"
"time"
)
type PageMeta struct {
Path string
HTMLPath string
Title string
Date string
Tags []string
UpdatedAt time.Time
}
func (d *DB) UpsertPage(p PageMeta) error {
tags, _ := json.Marshal(p.Tags)
_, err := d.db.Exec(`
INSERT INTO pages (path, html_path, title, date, tags, updated_at)
VALUES (?, ?, ?, ?, ?, ?)
ON CONFLICT(path) DO UPDATE SET
html_path = excluded.html_path,
title = excluded.title,
date = excluded.date,
tags = excluded.tags,
updated_at = excluded.updated_at
`, p.Path, p.HTMLPath, p.Title, p.Date, string(tags), p.UpdatedAt.UTC())
return err
}
func (d *DB) DeletePage(path string) error {
_, err := d.db.Exec(`DELETE FROM pages WHERE path = ?`, path)
return err
}
func (d *DB) GetPage(path string) (*PageMeta, error) {
row := d.db.QueryRow(
`SELECT path, html_path, title, date, tags FROM pages WHERE path = ?`, path)
var p PageMeta
var tagsJSON string
if err := row.Scan(&p.Path, &p.HTMLPath, &p.Title, &p.Date, &tagsJSON); err != nil {
return nil, err
}
_ = json.Unmarshal([]byte(tagsJSON), &p.Tags)
return &p, nil
}
func (d *DB) ListPages() ([]PageMeta, error) {
rows, err := d.db.Query(
`SELECT path, html_path, title, date, tags FROM pages ORDER BY date DESC, path`)
if err != nil {
return nil, err
}
defer rows.Close()
return scanPages(rows)
}
func (d *DB) ListByTag(tag string) ([]PageMeta, error) {
needle := `%"` + strings.ReplaceAll(tag, `"`, `\"`) + `"%`
rows, err := d.db.Query(
`SELECT path, html_path, title, date, tags FROM pages WHERE tags LIKE ? ORDER BY date DESC`, needle)
if err != nil {
return nil, err
}
defer rows.Close()
return scanPages(rows)
}
func scanPages(rows *sql.Rows) ([]PageMeta, error) {
var pages []PageMeta
for rows.Next() {
var p PageMeta
var tagsJSON string
if err := rows.Scan(&p.Path, &p.HTMLPath, &p.Title, &p.Date, &tagsJSON); err != nil {
return nil, err
}
_ = json.Unmarshal([]byte(tagsJSON), &p.Tags)
pages = append(pages, p)
}
return pages, rows.Err()
}
|
