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
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
|
package db
import (
"database/sql"
"encoding/json"
"strings"
"time"
_ "modernc.org/sqlite"
)
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);
CREATE TABLE IF NOT EXISTS posts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
slug TEXT NOT NULL UNIQUE,
title TEXT NOT NULL DEFAULT '',
date TEXT DEFAULT '',
tags TEXT DEFAULT '[]',
draft INTEGER NOT NULL DEFAULT 0,
blocks TEXT NOT NULL DEFAULT '[]',
updated_at INTEGER NOT NULL DEFAULT (cast(strftime('%s','now') * 1000000 as integer))
);
CREATE INDEX IF NOT EXISTS idx_posts_slug ON posts(slug);
CREATE INDEX IF NOT EXISTS idx_posts_date ON posts(date);
CREATE TABLE IF NOT EXISTS redirects (
from_slug TEXT PRIMARY KEY,
to_slug TEXT NOT NULL
);
`)
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()
}
|
