- commit
- ad568e9
- parent
- e445a79
- author
- Eric Bower
- date
- 2023-03-13 03:20:20 +0000 UTC
feat(prose): post aliases (#13)
Posts can now have alias routes as well as the default filename as the
slug.
This is an important feature to support migrating from a self-hosted
hugo blog to one hosted by pico.
```
---
title: My post
aliases:
- my_awesome_post
- 2023/03/10/my-post
---
```
9 files changed,
+161,
-17
M
Makefile
+2,
-1
1@@ -74,10 +74,11 @@ migrate:
2 docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20220811_add_feature.sql
3 docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20221108_add_expires_at_to_posts.sql
4 docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20221112_add_feeds_space.sql
5+ docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20230310_add_aliases_table.sql
6 .PHONY: migrate
7
8 latest:
9- docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20221112_add_feeds_space.sql
10+ docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20230310_add_aliases_table.sql
11 .PHONY: latest
12
13 psql:
M
db/db.go
+2,
-1
1@@ -146,7 +146,6 @@ type DB interface {
2 FindPostWithSlug(slug string, userID string, space string) (*Post, error)
3 FindAllPosts(pager *Pager, space string) (*Paginate[*Post], error)
4 FindAllUpdatedPosts(pager *Pager, space string) (*Paginate[*Post], error)
5- // FindPostsWithTagsForUser(userID, space string) ([]*Post, error)
6 InsertPost(post *Post) (*Post, error)
7 UpdatePost(post *Post) (*Post, error)
8 RemovePosts(postIDs []string) error
9@@ -157,6 +156,8 @@ type DB interface {
10 FindPopularTags(space string) ([]string, error)
11 FindTagsForPost(postID string) ([]string, error)
12
13+ ReplaceAliasesForPost(aliases []string, postID string) error
14+
15 AddViewCount(postID string) (int, error)
16
17 HasFeatureForUser(userID string, feature string) bool
+87,
-8
1@@ -151,7 +151,8 @@ const (
2 sqlSelectFeatureForUser = `SELECT id FROM feature_flags WHERE user_id = $1 AND name = $2`
3 sqlSelectSizeForUser = `SELECT sum(file_size) FROM posts WHERE user_id = $1`
4
5- sqlSelectTagPostCount = `
6+ sqlSelectPostIdByAliasSlug = `SELECT post_id FROM post_aliases WHERE slug = $1`
7+ sqlSelectTagPostCount = `
8 SELECT count(posts.id)
9 FROM posts
10 LEFT OUTER JOIN post_tags ON post_tags.post_id = posts.id
11@@ -222,8 +223,9 @@ const (
12 file_size, mime_type, shasum, data, expires_at)
13 VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
14 RETURNING id`
15- sqlInsertUser = `INSERT INTO app_users DEFAULT VALUES returning id`
16- sqlInsertTag = `INSERT INTO post_tags (post_id, name) VALUES($1, $2) RETURNING id;`
17+ sqlInsertUser = `INSERT INTO app_users DEFAULT VALUES returning id`
18+ sqlInsertTag = `INSERT INTO post_tags (post_id, name) VALUES($1, $2) RETURNING id;`
19+ sqlInsertAliases = `INSERT INTO post_aliases (post_id, slug) VALUES($1, $2) RETURNING id;`
20
21 sqlUpdatePost = `
22 UPDATE posts
23@@ -233,10 +235,11 @@ const (
24 sqlUpdateUserName = `UPDATE app_users SET name = $1 WHERE id = $2`
25 sqlIncrementViews = `UPDATE posts SET views = views + 1 WHERE id = $1 RETURNING views`
26
27- sqlRemoveTagsByPost = `DELETE FROM post_tags WHERE post_id = $1`
28- sqlRemovePosts = `DELETE FROM posts WHERE id = ANY($1::uuid[])`
29- sqlRemoveKeys = `DELETE FROM public_keys WHERE id = ANY($1::uuid[])`
30- sqlRemoveUsers = `DELETE FROM app_users WHERE id = ANY($1::uuid[])`
31+ sqlRemoveAliasesByPost = `DELETE FROM post_aliases WHERE post_id = $1`
32+ sqlRemoveTagsByPost = `DELETE FROM post_tags WHERE post_id = $1`
33+ sqlRemovePosts = `DELETE FROM posts WHERE id = ANY($1::uuid[])`
34+ sqlRemoveKeys = `DELETE FROM public_keys WHERE id = ANY($1::uuid[])`
35+ sqlRemoveUsers = `DELETE FROM app_users WHERE id = ANY($1::uuid[])`
36 )
37
38 type PsqlDB struct {
39@@ -583,7 +586,15 @@ func (me *PsqlDB) FindPostWithSlug(slug string, user_id string, space string) (*
40 r := me.Db.QueryRow(sqlSelectPostWithSlug, slug, user_id, space)
41 post, err := CreatePostWithTagsFromRow(r)
42 if err != nil {
43- return nil, err
44+ // attempt to find post inside post_aliases
45+ alias := me.Db.QueryRow(sqlSelectPostIdByAliasSlug, slug)
46+ postID := ""
47+ err := alias.Scan(&postID)
48+ if err != nil {
49+ return nil, err
50+ }
51+
52+ return me.FindPost(postID)
53 }
54
55 return post, nil
56@@ -915,6 +926,74 @@ func (me *PsqlDB) ReplaceTagsForPost(tags []string, postID string) error {
57 return err
58 }
59
60+func (me *PsqlDB) removeAliasesForPost(tx *sql.Tx, postID string) error {
61+ _, err := tx.Exec(sqlRemoveAliasesByPost, postID)
62+ return err
63+}
64+
65+func (me *PsqlDB) insertAliasesForPost(tx *sql.Tx, aliases []string, postID string) ([]string, error) {
66+ // hardcoded
67+ denyList := []string{
68+ "rss",
69+ "rss.xml",
70+ "atom.xml",
71+ "feed.xml",
72+ "styles.css",
73+ "main.css",
74+ "prose.css",
75+ "syntax.css",
76+ "card.png",
77+ "favicon-16x16.png",
78+ "favicon-32x32.png",
79+ "apple-touch-icon.png",
80+ "favicon.ico",
81+ "robots.txt",
82+ }
83+
84+ ids := make([]string, 0)
85+ for _, alias := range aliases {
86+ if slices.Contains(denyList, alias) {
87+ me.Logger.Infof(
88+ "(%s) is in the deny list for aliases because it conflicts with a static route, skipping",
89+ alias,
90+ )
91+ continue
92+ }
93+ id := ""
94+ err := tx.QueryRow(sqlInsertAliases, postID, alias).Scan(&id)
95+ if err != nil {
96+ return nil, err
97+ }
98+ ids = append(ids, id)
99+ }
100+
101+ return ids, nil
102+}
103+
104+func (me *PsqlDB) ReplaceAliasesForPost(aliases []string, postID string) error {
105+ ctx := context.Background()
106+ tx, err := me.Db.BeginTx(ctx, nil)
107+ if err != nil {
108+ return err
109+ }
110+ defer func() {
111+ err = tx.Rollback()
112+ }()
113+
114+ err = me.removeAliasesForPost(tx, postID)
115+ if err != nil {
116+ return err
117+ }
118+
119+ _, err = me.insertAliasesForPost(tx, aliases, postID)
120+ if err != nil {
121+ return err
122+ }
123+
124+ err = tx.Commit()
125+ return err
126+}
127+
128 func (me *PsqlDB) FindUserPostsByTag(page *db.Pager, tag, userID, space string) (*db.Paginate[*db.Post], error) {
129 var posts []*db.Post
130 rs, err := me.Db.Query(
+23,
-0
1@@ -35,6 +35,7 @@ type PostMetaData struct {
2 Tags []string
3 User *db.User
4 FileEntry *utils.FileEntry
5+ Aliases []string
6 }
7
8 type ScpFileHooks interface {
9@@ -262,6 +263,18 @@ func (h *ScpUploadHandler) Write(s ssh.Session, entry *utils.FileEntry) (string,
10 return "", fmt.Errorf("error for %s: %v", filename, err)
11 }
12
13+ if len(metadata.Aliases) > 0 {
14+ logger.Infof(
15+ "Found (%s) post aliases, replacing with old aliases",
16+ strings.Join(metadata.Aliases, ","),
17+ )
18+ err = h.DBPool.ReplaceAliasesForPost(metadata.Aliases, post.ID)
19+ if err != nil {
20+ logger.Errorf("error for %s: %v", filename, err)
21+ return "", fmt.Errorf("error for %s: %v", filename, err)
22+ }
23+ }
24+
25 if len(metadata.Tags) > 0 {
26 logger.Infof(
27 "Found (%s) post tags, replacing with old tags",
28@@ -308,6 +321,16 @@ func (h *ScpUploadHandler) Write(s ssh.Session, entry *utils.FileEntry) (string,
29 logger.Errorf("error for %s: %v", filename, err)
30 return "", fmt.Errorf("error for %s: %v", filename, err)
31 }
32+
33+ logger.Infof(
34+ "Found (%s) post aliases, replacing with old aliases",
35+ strings.Join(metadata.Aliases, ","),
36+ )
37+ err = h.DBPool.ReplaceAliasesForPost(metadata.Aliases, post.ID)
38+ if err != nil {
39+ logger.Errorf("error for %s: %v", filename, err)
40+ return "", fmt.Errorf("error for %s: %v", filename, err)
41+ }
42 }
43
44 curl := shared.NewCreateURL(h.Cfg)
+5,
-5
1@@ -845,9 +845,9 @@ func createMainRoutes(staticRoutes []shared.Route) []shared.Route {
2 shared.NewRoute("GET", "/([^/]+)/rss.xml", rssBlogHandler),
3 shared.NewRoute("GET", "/([^/]+)/atom.xml", rssBlogHandler),
4 shared.NewRoute("GET", "/([^/]+)/feed.xml", rssBlogHandler),
5- shared.NewRoute("GET", "/([^/]+)/styles.css", blogStyleHandler),
6- shared.NewRoute("GET", "/([^/]+)/([^/]+)", postHandler),
7- shared.NewRoute("GET", "/raw/([^/]+)/([^/]+)", postRawHandler),
8+ shared.NewRoute("GET", "/([^/]+)/_styles.css", blogStyleHandler),
9+ shared.NewRoute("GET", "/([^/]+)/(.+)", postHandler),
10+ shared.NewRoute("GET", "/raw/([^/]+)/(.+)", postRawHandler),
11 )
12
13 return routes
14@@ -870,8 +870,8 @@ func createSubdomainRoutes(staticRoutes []shared.Route) []shared.Route {
15
16 routes = append(
17 routes,
18- shared.NewRoute("GET", "/([^/]+)", postHandler),
19- shared.NewRoute("GET", "/raw/([^/]+)", postRawHandler),
20+ shared.NewRoute("GET", "/(.+)", postHandler),
21+ shared.NewRoute("GET", "/raw/(.+)", postRawHandler),
22 )
23
24 return routes
+2,
-1
1@@ -204,6 +204,7 @@ This will show up on the blog landing page.
2 <li>image (og image)</li>
3 <li>card (og image twitter card: summary, summary_large_image, etc.)</li>
4 <li>favicon</li>
5+ <li>aliases (list of alternate routes e.g. <code>2023/03/10/my-post</code>)</li>
6 </ul>
7 </p>
8 </section>
9@@ -369,8 +370,8 @@ This will show up on the blog landing page.
10 <tbody>
11 <tr>
12 <td>aliases</td>
13- <td>no</td>
14 <td>yes</td>
15+ <td>-</td>
16 </tr>
17
18 <tr>
+1,
-0
1@@ -59,6 +59,7 @@ func (p *MarkdownHooks) FileMeta(data *filehandlers.PostMetaData) error {
2 data.Title = parsedText.Title
3 }
4
5+ data.Aliases = parsedText.Aliases
6 data.Tags = parsedText.Tags
7 data.Description = parsedText.Description
8
1@@ -31,6 +31,7 @@ type MetaData struct {
2 Description string
3 Nav []Link
4 Tags []string
5+ Aliases []string
6 Layout string
7 Image string
8 ImageCard string
9@@ -100,6 +101,24 @@ func toLinks(obj interface{}) ([]Link, error) {
10 return links, nil
11 }
12
13+func toAliases(obj interface{}) ([]string, error) {
14+ arr := make([]string, 0)
15+ if obj == nil {
16+ return arr, nil
17+ }
18+
19+ switch raw := obj.(type) {
20+ case []interface{}:
21+ for _, alias := range raw {
22+ arr = append(arr, alias.(string))
23+ }
24+ default:
25+ return arr, fmt.Errorf("unsupported type for `aliases` variable: %T", raw)
26+ }
27+
28+ return arr, nil
29+}
30+
31 func toTags(obj interface{}) ([]string, error) {
32 arr := make([]string, 0)
33 if obj == nil {
34@@ -171,7 +190,8 @@ func (r *ImgRender) renderImage(w util.BufWriter, source []byte, node ast.Node,
35 func ParseText(text string, linkify Linkify) (*ParsedText, error) {
36 parsed := ParsedText{
37 MetaData: &MetaData{
38- Tags: []string{},
39+ Tags: []string{},
40+ Aliases: []string{},
41 },
42 }
43 var buf bytes.Buffer
44@@ -243,6 +263,12 @@ func ParseText(text string, linkify Linkify) (*ParsedText, error) {
45 }
46 parsed.MetaData.Nav = nav
47
48+ aliases, err := toAliases(metaData["aliases"])
49+ if err != nil {
50+ return &parsed, err
51+ }
52+ parsed.MetaData.Aliases = aliases
53+
54 tags, err := toTags(metaData["tags"])
55 if err != nil {
56 return &parsed, err
1@@ -0,0 +1,12 @@
2+CREATE TABLE IF NOT EXISTS post_aliases (
3+ id uuid NOT NULL DEFAULT uuid_generate_v4(),
4+ post_id uuid NOT NULL,
5+ slug character varying(255) NOT NULL,
6+ created_at timestamp without time zone NOT NULL DEFAULT NOW(),
7+ CONSTRAINT post_aliases_pkey PRIMARY KEY (id),
8+ CONSTRAINT fk_post_aliases_posts
9+ FOREIGN KEY(post_id)
10+ REFERENCES posts(id)
11+ ON DELETE CASCADE
12+ ON UPDATE CASCADE
13+);