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
117
|
package db
import (
"database/sql"
"encoding/json"
"strings"
"time"
_ "nebbet.no/internal/sqlitedrv"
)
type MetaDB struct {
db *sql.DB
}
type PageMeta struct {
Path string
HTMLPath string
Title string
Date string
Tags []string
UpdatedAt time.Time
}
func OpenMeta(path string) (*MetaDB, error) {
db, err := sql.Open("sqlite", path)
if err != nil {
return nil, err
}
_, err = db.Exec(`
CREATE TABLE IF NOT EXISTS pages (
id INTEGER PRIMARY KEY AUTOINCREMENT,
path TEXT NOT NULL UNIQUE,
html_path TEXT NOT NULL,
title TEXT NOT NULL DEFAULT '',
date TEXT DEFAULT '',
tags TEXT DEFAULT '[]',
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_pages_path ON pages(path);
CREATE INDEX IF NOT EXISTS idx_pages_date ON pages(date);
`)
if err != nil {
return nil, err
}
return &MetaDB{db: db}, nil
}
func (m *MetaDB) Close() error { return m.db.Close() }
func (m *MetaDB) UpsertPage(p PageMeta) error {
tags, _ := json.Marshal(p.Tags)
_, err := m.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 (m *MetaDB) DeletePage(path string) error {
_, err := m.db.Exec(`DELETE FROM pages WHERE path = ?`, path)
return err
}
func (m *MetaDB) GetPage(path string) (*PageMeta, error) {
row := m.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 (m *MetaDB) ListPages() ([]PageMeta, error) {
rows, err := m.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 (m *MetaDB) ListByTag(tag string) ([]PageMeta, error) {
// JSON array contains check via LIKE — sufficient for simple tag strings.
needle := `%"` + strings.ReplaceAll(tag, `"`, `\"`) + `"%`
rows, err := m.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()
}
|
