- commit
- ab67bde
- parent
- 866c2f8
- author
- Eric Bower
- date
- 2022-08-02 01:17:25 +0000 UTC
feat: ability to tag posts for lists and prose Now users can tag posts and then filter posts on their blog by a single tag. We could have supported the ability to filter by multiple tags but I felt like that was feature creep. If we get enough requests to filter by multiple tags then we can reconsider that decision. I also made it so the blog rss feed will filter based on tag. This allows readers to subscribe and read posts specific to a tag. In a follow up commit we will make it so the `/read` endpoint can also be filtered by tag, but again I felt like that was feature creep and probably not something many people will end up using. Implements: https://todo.sr.ht/~erock/pico.sh/31
18 files changed,
+343,
-26
M
Makefile
M
Makefile
+2,
-1
1@@ -60,10 +60,11 @@ migrate:
2 docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./db/migrations/20220722_post_hidden.sql
3 docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./db/migrations/20220727_post_change_post_contraints.sql
4 docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./db/migrations/20220730_post_change_filename_to_slug.sql
5+ docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./db/migrations/20220801_add_post_tags.sql
6 .PHONY: migrate
7
8 latest:
9- docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./db/migrations/20220730_post_change_filename_to_slug.sql
10+ docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./db/migrations/20220801_add_post_tags.sql
11 .PHONY: latest
12
13 psql:
1@@ -0,0 +1,13 @@
2+CREATE TABLE IF NOT EXISTS post_tags (
3+ id uuid NOT NULL DEFAULT uuid_generate_v4(),
4+ post_id uuid NOT NULL,
5+ name character varying(50),
6+ created_at timestamp without time zone NOT NULL DEFAULT NOW(),
7+ CONSTRAINT post_tags_unique_name UNIQUE (post_id, name),
8+ CONSTRAINT post_tags_pkey PRIMARY KEY (id),
9+ CONSTRAINT fk_post_tags_post
10+ FOREIGN KEY(post_id)
11+ REFERENCES posts(id)
12+ ON DELETE CASCADE
13+ ON UPDATE CASCADE
14+);
+25,
-1
1@@ -3,6 +3,7 @@ package filehandlers
2 import (
3 "fmt"
4 "io"
5+ "strings"
6 "time"
7
8 "git.sr.ht/~erock/pico/shared"
9@@ -20,6 +21,7 @@ type PostMetaData struct {
10 Description string
11 PublishAt *time.Time
12 Hidden bool
13+ Tags []string
14 }
15
16 type ScpFileHooks interface {
17@@ -122,7 +124,7 @@ func (h *ScpUploadHandler) Write(s ssh.Session, entry *utils.FileEntry) (string,
18 }
19 } else if post == nil {
20 logger.Infof("(%s) not found, adding record", filename)
21- _, err = h.DBPool.InsertPost(
22+ post, err = h.DBPool.InsertPost(
23 userID,
24 filename,
25 metadata.Slug,
26@@ -137,6 +139,18 @@ func (h *ScpUploadHandler) Write(s ssh.Session, entry *utils.FileEntry) (string,
27 logger.Errorf("error for %s: %v", filename, err)
28 return "", fmt.Errorf("error for %s: %v", filename, err)
29 }
30+
31+ if len(metadata.Tags) > 0 {
32+ logger.Infof(
33+ "Found (%s) post tags, replacing with old tags",
34+ strings.Join(metadata.Tags, ","),
35+ )
36+ err = h.DBPool.ReplaceTagsForPost(metadata.Tags, post.ID)
37+ if err != nil {
38+ logger.Errorf("error for %s: %v", filename, err)
39+ return "", fmt.Errorf("error for %s: %v", filename, err)
40+ }
41+ }
42 } else {
43 if text == post.Text {
44 logger.Infof("(%s) found, but text is identical, skipping", filename)
45@@ -156,6 +170,16 @@ func (h *ScpUploadHandler) Write(s ssh.Session, entry *utils.FileEntry) (string,
46 logger.Errorf("error for %s: %v", filename, err)
47 return "", fmt.Errorf("error for %s: %v", filename, err)
48 }
49+
50+ logger.Infof(
51+ "Found (%s) post tags, replacing with old tags",
52+ strings.Join(metadata.Tags, ","),
53+ )
54+ err = h.DBPool.ReplaceTagsForPost(metadata.Tags, post.ID)
55+ if err != nil {
56+ logger.Errorf("error for %s: %v", filename, err)
57+ return "", fmt.Errorf("error for %s: %v", filename, err)
58+ }
59 }
60
61 return h.Cfg.FullPostURL(user.Name, metadata.Slug, h.Cfg.IsSubdomains(), true), nil
+36,
-4
1@@ -6,6 +6,7 @@ import (
2 "html/template"
3 "net/http"
4 "net/url"
5+ "sort"
6 "strconv"
7 "strings"
8 "time"
9@@ -40,6 +41,7 @@ type BlogPageData struct {
10 Readme *ReadmeTxt
11 Header *HeaderTxt
12 Posts []PostItemData
13+ HasFilter bool
14 }
15
16 type ReadPageData struct {
17@@ -62,6 +64,7 @@ type PostPageData struct {
18 Items []*pkg.ListItem
19 PublishAtISO string
20 PublishAt string
21+ Tags []string
22 }
23
24 type TransparencyPageData struct {
25@@ -82,6 +85,29 @@ type ReadmeTxt struct {
26 Items []*pkg.ListItem
27 }
28
29+func getPostsForUser(r *http.Request, user *db.User, tag string) ([]*db.Post, error) {
30+ dbpool := shared.GetDB(r)
31+ cfg := shared.GetCfg(r)
32+ var err error
33+
34+ posts := make([]*db.Post, 0)
35+ if tag == "" {
36+ posts, err = dbpool.FindPostsForUser(user.ID, cfg.Space)
37+ } else {
38+ posts, err = dbpool.FindUserPostsByTag(tag, user.ID, cfg.Space)
39+ }
40+
41+ if err != nil {
42+ return posts, err
43+ }
44+
45+ sort.Slice(posts, func(i, j int) bool {
46+ return posts[i].UpdatedAt.After(*posts[j].UpdatedAt)
47+ })
48+
49+ return posts, nil
50+}
51+
52 func isRequestTrackable(r *http.Request) bool {
53 return true
54 }
55@@ -98,7 +124,9 @@ func blogHandler(w http.ResponseWriter, r *http.Request) {
56 http.Error(w, "blog not found", http.StatusNotFound)
57 return
58 }
59- posts, err := dbpool.FindUpdatedPostsForUser(user.ID, cfg.Space)
60+
61+ tag := r.URL.Query().Get("tag")
62+ posts, err := getPostsForUser(r, user, tag)
63 if err != nil {
64 logger.Error(err)
65 http.Error(w, "could not fetch posts for blog", http.StatusInternalServerError)
66@@ -169,11 +197,12 @@ func blogHandler(w http.ResponseWriter, r *http.Request) {
67 Site: *cfg.GetSiteData(),
68 PageTitle: headerTxt.Title,
69 URL: template.URL(cfg.FullBlogURL(username, onSubdomain, withUserName)),
70- RSSURL: template.URL(cfg.RssBlogURL(username, onSubdomain, withUserName)),
71+ RSSURL: template.URL(cfg.RssBlogURL(username, onSubdomain, withUserName, tag)),
72 Readme: readmeTxt,
73 Header: headerTxt,
74 Username: username,
75 Posts: postCollection,
76+ HasFilter: tag != "",
77 }
78
79 err = ts.Execute(w, data)
80@@ -261,6 +290,7 @@ func postHandler(w http.ResponseWriter, r *http.Request) {
81 Username: username,
82 BlogName: blogName,
83 Items: parsedText.Items,
84+ Tags: parsedText.MetaData.Tags,
85 }
86 } else {
87 logger.Infof("post not found %s/%s", username, slug)
88@@ -404,10 +434,12 @@ func rssBlogHandler(w http.ResponseWriter, r *http.Request) {
89 http.Error(w, "rss feed not found", http.StatusNotFound)
90 return
91 }
92- posts, err := dbpool.FindUpdatedPostsForUser(user.ID, cfg.Space)
93+
94+ tag := r.URL.Query().Get("tag")
95+ posts, err := getPostsForUser(r, user, tag)
96 if err != nil {
97 logger.Error(err)
98- http.Error(w, err.Error(), http.StatusInternalServerError)
99+ http.Error(w, "could not fetch posts for blog", http.StatusInternalServerError)
100 return
101 }
102
+1,
-1
1@@ -147,7 +147,7 @@ func blogHandler(ctx context.Context, w gemini.ResponseWriter, r *gemini.Request
2 Site: *cfg.GetSiteData(),
3 PageTitle: headerTxt.Title,
4 URL: html.URL(cfg.FullBlogURL(username, onSubdomain, withUserName)),
5- RSSURL: html.URL(cfg.RssBlogURL(username, onSubdomain, withUserName)),
6+ RSSURL: html.URL(cfg.RssBlogURL(username, onSubdomain, withUserName, "")),
7 Readme: readmeTxt,
8 Header: headerTxt,
9 Username: username,
+3,
-0
1@@ -48,6 +48,9 @@
2 {{end}}
3
4 <section class="posts">
5+ {{if .HasFilter}}
6+ <a href="{{.URL}}">clear filters</a>
7+ {{end}}
8 {{range .Posts}}
9 <article>
10 <div class="flex items-center">
+5,
-0
1@@ -31,6 +31,11 @@
2 <span> on </span>
3 <a href="{{.BlogURL}}">{{.BlogName}}</a></p>
4 {{if .Description}}<div class="my font-italic">{{.Description}}</div>{{end}}
5+ <div class="tags">
6+ {{range .Tags}}
7+ <a class="tag" href="{{$.BlogURL}}?tag={{.}}">#{{.}}</a>
8+ {{end}}
9+ </div>
10 </header>
11 <main>
12 <article>
+13,
-5
1@@ -29,6 +29,7 @@ type MetaData struct {
2 PublishAt *time.Time
3 Title string
4 Description string
5+ Tags []string
6 ListType string // https://developer.mozilla.org/en-US/docs/Web/CSS/list-style-type
7 }
8
9@@ -88,6 +89,12 @@ func TokenToMetaField(meta *MetaData, token *SplitToken) {
10 meta.Description = token.Value
11 } else if token.Key == "list_type" {
12 meta.ListType = token.Value
13+ } else if token.Key == "tags" {
14+ tags := strings.Split(token.Value, ",")
15+ meta.Tags = make([]string, 0)
16+ for _, tag := range tags {
17+ meta.Tags = append(meta.Tags, strings.TrimSpace(tag))
18+ }
19 }
20 }
21
22@@ -101,8 +108,9 @@ func KeyAsValue(token *SplitToken) string {
23 func ParseText(text string) *ParsedText {
24 textItems := SplitByNewline(text)
25 items := []*ListItem{}
26- meta := &MetaData{
27+ meta := MetaData{
28 ListType: "disc",
29+ Tags: []string{},
30 }
31 pre := false
32 skip := false
33@@ -115,7 +123,7 @@ func ParseText(text string) *ParsedText {
34 prevItem = items[len(items)-1]
35 }
36
37- li := &ListItem{
38+ li := ListItem{
39 Value: strings.Trim(t, " "),
40 }
41
42@@ -147,7 +155,7 @@ func ParseText(text string) *ParsedText {
43 li.Value = KeyAsValue(split)
44 } else if strings.HasPrefix(li.Value, varToken) {
45 split := TextToSplitToken(strings.Replace(li.Value, varToken, "", 1))
46- TokenToMetaField(meta, split)
47+ TokenToMetaField(&meta, split)
48 continue
49 } else if strings.HasPrefix(li.Value, headerTwoToken) {
50 li.IsHeaderTwo = true
51@@ -164,12 +172,12 @@ func ParseText(text string) *ParsedText {
52 }
53
54 if !skip {
55- items = append(items, li)
56+ items = append(items, &li)
57 }
58 }
59
60 return &ParsedText{
61 Items: items,
62- MetaData: meta,
63+ MetaData: &meta,
64 }
65 }
+1,
-0
1@@ -44,6 +44,7 @@ func (p *ListHooks) FileMeta(text string, data *filehandlers.PostMetaData) error
2 }
3
4 data.Description = parsedText.MetaData.Description
5+ data.Tags = parsedText.MetaData.Tags
6
7 if parsedText.MetaData.PublishAt != nil && !parsedText.MetaData.PublishAt.IsZero() {
8 data.PublishAt = parsedText.MetaData.PublishAt
+1,
-1
1@@ -131,7 +131,7 @@ func blogHandler(w http.ResponseWriter, r *http.Request) {
2 Site: *cfg.GetSiteData(),
3 PageTitle: headerTxt.Title,
4 URL: template.URL(cfg.FullBlogURL(username, onSubdomain, withUserName)),
5- RSSURL: template.URL(cfg.RssBlogURL(username, onSubdomain, withUserName)),
6+ RSSURL: template.URL(cfg.RssBlogURL(username, onSubdomain, withUserName, "")),
7 Header: headerTxt,
8 Username: username,
9 Posts: postCollection,
+23,
-3
1@@ -47,6 +47,7 @@ type BlogPageData struct {
2 Posts []PostItemData
3 HasCSS bool
4 CssURL template.URL
5+ HasFilter bool
6 }
7
8 type ReadPageData struct {
9@@ -70,6 +71,7 @@ type PostPageData struct {
10 PublishAt string
11 HasCSS bool
12 CssURL template.URL
13+ Tags []string
14 }
15
16 type TransparencyPageData struct {
17@@ -150,7 +152,15 @@ func blogHandler(w http.ResponseWriter, r *http.Request) {
18 http.Error(w, "blog not found", http.StatusNotFound)
19 return
20 }
21- posts, err := dbpool.FindPostsForUser(user.ID, cfg.Space)
22+
23+ tag := r.URL.Query().Get("tag")
24+ var posts []*db.Post
25+ if tag == "" {
26+ posts, err = dbpool.FindPostsForUser(user.ID, cfg.Space)
27+ } else {
28+ posts, err = dbpool.FindUserPostsByTag(tag, user.ID, cfg.Space)
29+ }
30+
31 if err != nil {
32 logger.Error(err)
33 http.Error(w, "could not fetch posts for blog", http.StatusInternalServerError)
34@@ -216,13 +226,14 @@ func blogHandler(w http.ResponseWriter, r *http.Request) {
35 Site: *cfg.GetSiteData(),
36 PageTitle: headerTxt.Title,
37 URL: template.URL(cfg.FullBlogURL(username, onSubdomain, withUserName)),
38- RSSURL: template.URL(cfg.RssBlogURL(username, onSubdomain, withUserName)),
39+ RSSURL: template.URL(cfg.RssBlogURL(username, onSubdomain, withUserName, tag)),
40 Readme: readmeTxt,
41 Header: headerTxt,
42 Username: username,
43 Posts: postCollection,
44 HasCSS: hasCSS,
45 CssURL: template.URL(cfg.CssURL(username)),
46+ HasFilter: tag != "",
47 }
48
49 err = ts.Execute(w, data)
50@@ -350,6 +361,7 @@ func postHandler(w http.ResponseWriter, r *http.Request) {
51 Contents: template.HTML(parsedText.Html),
52 HasCSS: hasCSS,
53 CssURL: template.URL(cfg.CssURL(username)),
54+ Tags: parsedText.Tags,
55 }
56 } else {
57 data = PostPageData{
58@@ -487,7 +499,15 @@ func rssBlogHandler(w http.ResponseWriter, r *http.Request) {
59 http.Error(w, "rss feed not found", http.StatusNotFound)
60 return
61 }
62- posts, err := dbpool.FindPostsForUser(user.ID, cfg.Space)
63+
64+ tag := r.URL.Query().Get("tag")
65+ var posts []*db.Post
66+ if tag == "" {
67+ posts, err = dbpool.FindPostsForUser(user.ID, cfg.Space)
68+ } else {
69+ posts, err = dbpool.FindUserPostsByTag(tag, user.ID, cfg.Space)
70+ }
71+
72 if err != nil {
73 logger.Error(err)
74 http.Error(w, err.Error(), http.StatusInternalServerError)
+3,
-0
1@@ -51,6 +51,9 @@
2 {{end}}
3
4 <section class="posts">
5+ {{if .HasFilter}}
6+ <a href={{.URL}}>clear filters</a>
7+ {{end}}
8 {{range .Posts}}
9 <article>
10 <div class="flex items-center">
+5,
-0
1@@ -36,6 +36,11 @@
2 <span> on </span>
3 <a href="{{.BlogURL}}">{{.BlogName}}</a></p>
4 {{if .Description}}<div class="my font-italic">{{.Description}}</div>{{end}}
5+ <div class="tags">
6+ {{range .Tags}}
7+ <a class="tag" href="{{$.BlogURL}}?tag={{.}}">#{{.}}</a>
8+ {{end}}
9+ </div>
10 </header>
11 <main>
12 <article class="md">
+25,
-0
1@@ -20,6 +20,7 @@ type MetaData struct {
2 Title string
3 Description string
4 Nav []Link
5+ Tags []string
6 }
7
8 type ParsedText struct {
9@@ -68,6 +69,24 @@ func toLinks(obj interface{}) ([]Link, error) {
10 return links, nil
11 }
12
13+func toTags(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 _, tag := range raw {
22+ arr = append(arr, tag.(string))
23+ }
24+ default:
25+ return arr, fmt.Errorf("unsupported type for `tags` variable: %T", raw)
26+ }
27+
28+ return arr, nil
29+}
30+
31 var reTimestamp = regexp.MustCompile(`T.+`)
32
33 func ParseText(text string) (*ParsedText, error) {
34@@ -111,6 +130,11 @@ func ParseText(text string) (*ParsedText, error) {
35 return &ParsedText{}, err
36 }
37
38+ tags, err := toTags(metaData["tags"])
39+ if err != nil {
40+ return &ParsedText{}, err
41+ }
42+
43 return &ParsedText{
44 Html: buf.String(),
45 MetaData: &MetaData{
46@@ -118,6 +142,7 @@ func ParseText(text string) (*ParsedText, error) {
47 Title: toString(metaData["title"]),
48 Description: toString(metaData["description"]),
49 Nav: nav,
50+ Tags: tags,
51 },
52 }, nil
53 }
+1,
-0
1@@ -52,6 +52,7 @@ func (p *MarkdownHooks) FileMeta(text string, data *filehandlers.PostMetaData) e
2 data.Title = parsedText.Title
3 }
4
5+ data.Tags = parsedText.Tags
6 data.Description = parsedText.Description
7
8 if parsedText.PublishAt != nil && !parsedText.PublishAt.IsZero() {
1@@ -87,16 +87,21 @@ func (c *ConfigSite) IsCustomdomains() bool {
2 return c.CustomdomainsEnabled
3 }
4
5-func (c *ConfigSite) RssBlogURL(username string, onSubdomain bool, withUserName bool) string {
6+func (c *ConfigSite) RssBlogURL(username string, onSubdomain bool, withUserName bool, tag string) string {
7+ url := ""
8 if c.IsSubdomains() && onSubdomain {
9- return fmt.Sprintf("%s://%s.%s/rss", c.Protocol, username, c.Domain)
10+ url = fmt.Sprintf("%s://%s.%s/rss", c.Protocol, username, c.Domain)
11+ } else if withUserName {
12+ url = fmt.Sprintf("/%s/rss", username)
13+ } else {
14+ url = "/rss"
15 }
16
17- if withUserName {
18- return fmt.Sprintf("/%s/rss", username)
19+ if tag != "" {
20+ return fmt.Sprintf("%s?tag=%s", url, tag)
21 }
22
23- return "/rss"
24+ return url
25 }
26
27 func (c *ConfigSite) HomeURL() string {
+5,
-0
1@@ -116,6 +116,11 @@ type DB interface {
2 UpdatePost(postID string, slug string, title string, text string, description string, publishAt *time.Time) (*Post, error)
3 RemovePosts(postIDs []string) error
4
5+ ReplaceTagsForPost(tags []string, postID string) error
6+ FindUserPostsByTag(tag, userID, space string) ([]*Post, error)
7+ FindPostsByTag(tag, space string) ([]*Post, error)
8+ FindPopularTags() ([]string, error)
9+
10 AddViewCount(postID string) (int, error)
11
12 Close() error
+171,
-5
1@@ -1,6 +1,7 @@
2 package postgres
3
4 import (
5+ "context"
6 "database/sql"
7 "errors"
8 "math"
9@@ -35,11 +36,44 @@ const (
10 sqlSelectPostWithFilename = `SELECT posts.id, user_id, filename, slug, title, text, description, publish_at, app_users.name as username, posts.updated_at FROM posts LEFT OUTER JOIN app_users ON app_users.id = posts.user_id WHERE filename = $1 AND user_id = $2 AND cur_space = $3`
11 sqlSelectPostWithSlug = `SELECT posts.id, user_id, filename, slug, title, text, description, publish_at, app_users.name as username, posts.updated_at FROM posts LEFT OUTER JOIN app_users ON app_users.id = posts.user_id WHERE slug = $1 AND user_id = $2 AND cur_space = $3`
12 sqlSelectPost = `SELECT posts.id, user_id, filename, slug, title, text, description, publish_at, app_users.name as username, posts.updated_at FROM posts LEFT OUTER JOIN app_users ON app_users.id = posts.user_id WHERE posts.id = $1`
13- sqlSelectPostsForUser = `SELECT posts.id, user_id, filename, slug, title, text, description, publish_at, app_users.name as username, posts.updated_at FROM posts LEFT OUTER JOIN app_users ON app_users.id = posts.user_id WHERE user_id = $1 AND publish_at::date <= CURRENT_DATE AND cur_space = $2 ORDER BY publish_at DESC`
14 sqlSelectUpdatedPostsForUser = `SELECT posts.id, user_id, filename, slug, title, text, description, publish_at, app_users.name as username, posts.updated_at FROM posts LEFT OUTER JOIN app_users ON app_users.id = posts.user_id WHERE user_id = $1 AND publish_at::date <= CURRENT_DATE AND cur_space = $2 ORDER BY updated_at DESC`
15 sqlSelectAllUpdatedPosts = `SELECT posts.id, user_id, filename, slug, title, text, description, publish_at, app_users.name as username, posts.updated_at, 0 as score FROM posts LEFT OUTER JOIN app_users ON app_users.id = posts.user_id WHERE hidden = FALSE AND publish_at::date <= CURRENT_DATE AND cur_space = $3 ORDER BY updated_at DESC LIMIT $1 OFFSET $2`
16 sqlSelectPostCount = `SELECT count(id) FROM posts WHERE hidden = FALSE AND cur_space=$1`
17- sqlSelectPostsByRank = `
18+ sqlSelectPostsForUser = `
19+ SELECT posts.id, user_id, filename, slug, title, text, description, publish_at,
20+ app_users.name as username, posts.updated_at
21+ FROM posts
22+ LEFT OUTER JOIN app_users ON app_users.id = posts.user_id
23+ WHERE
24+ user_id = $1 AND
25+ publish_at::date <= CURRENT_DATE AND
26+ cur_space = $2
27+ ORDER BY publish_at DESC`
28+ sqlSelectPostsByTag = `
29+ SELECT posts.id, user_id, filename, slug, title, text, description, publish_at,
30+ app_users.name as username, posts.updated_at
31+ FROM posts
32+ LEFT OUTER JOIN app_users ON app_users.id = posts.user_id
33+ LEFT OUTER JOIN post_tags ON post_tags.post_id = posts.id
34+ WHERE
35+ post_tags.name = '$1' AND
36+ publish_at::date <= CURRENT_DATE AND
37+ cur_space = $2
38+ ORDER BY publish_at DESC`
39+ sqlSelectUserPostsByTag = `
40+ SELECT
41+ posts.id, user_id, filename, slug, title, text, description, publish_at,
42+ app_users.name as username, posts.updated_at
43+ FROM posts
44+ LEFT OUTER JOIN app_users ON app_users.id = posts.user_id
45+ LEFT OUTER JOIN post_tags ON post_tags.post_id = posts.id
46+ WHERE
47+ user_id = $1 AND
48+ (post_tags.name = $2 OR hidden = true) AND
49+ publish_at::date <= CURRENT_DATE AND
50+ cur_space = $3
51+ ORDER BY publish_at DESC`
52+ sqlSelectPostsByRank = `
53 SELECT
54 posts.id,
55 user_id,
56@@ -67,17 +101,21 @@ const (
57 ORDER BY score DESC
58 LIMIT $1 OFFSET $2`
59
60+ sqlSelectPopularTags = `SELECT name, count(post_id) as tally FROM post_tags GROUP_BY name, post_id ORDER BY tally DESC LIMIT 10`
61+
62 sqlInsertPublicKey = `INSERT INTO public_keys (user_id, public_key) VALUES ($1, $2)`
63 sqlInsertPost = `INSERT INTO posts (user_id, filename, slug, title, text, description, publish_at, hidden, cur_space) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING id`
64 sqlInsertUser = `INSERT INTO app_users DEFAULT VALUES returning id`
65+ sqlInsertTag = `INSERT INTO post_tags (post_id, name) VALUES($1, $2) RETURNING id;`
66
67 sqlUpdatePost = `UPDATE posts SET slug = $1, title = $2, text = $3, description = $4, updated_at = $5, publish_at = $6 WHERE id = $7`
68 sqlUpdateUserName = `UPDATE app_users SET name = $1 WHERE id = $2`
69 sqlIncrementViews = `UPDATE posts SET views = views + 1 WHERE id = $1 RETURNING views`
70
71- sqlRemovePosts = `DELETE FROM posts WHERE id = ANY($1::uuid[])`
72- sqlRemoveKeys = `DELETE FROM public_keys WHERE id = ANY($1::uuid[])`
73- sqlRemoveUsers = `DELETE FROM app_users WHERE id = ANY($1::uuid[])`
74+ sqlRemoveTagsByPost = `DELETE FROM post_tags WHERE post_id = $1`
75+ sqlRemovePosts = `DELETE FROM posts WHERE id = ANY($1::uuid[])`
76+ sqlRemoveKeys = `DELETE FROM public_keys WHERE id = ANY($1::uuid[])`
77+ sqlRemoveUsers = `DELETE FROM app_users WHERE id = ANY($1::uuid[])`
78 )
79
80 type PsqlDB struct {
81@@ -629,3 +667,131 @@ func (me *PsqlDB) FindUsers() ([]*db.User, error) {
82 }
83 return users, nil
84 }
85+
86+func (me *PsqlDB) removeTagsForPost(tx *sql.Tx, postID string) error {
87+ _, err := tx.Exec(sqlRemoveTagsByPost, postID)
88+ return err
89+}
90+
91+func (me *PsqlDB) insertTagsForPost(tx *sql.Tx, tags []string, postID string) ([]string, error) {
92+ ids := make([]string, 0)
93+ for _, tag := range tags {
94+ id := ""
95+ err := tx.QueryRow(sqlInsertTag, postID, tag).Scan(&id)
96+ if err != nil {
97+ return nil, err
98+ }
99+ ids = append(ids, id)
100+ }
101+
102+ return ids, nil
103+}
104+
105+func (me *PsqlDB) ReplaceTagsForPost(tags []string, postID string) error {
106+ ctx := context.Background()
107+ tx, err := me.Db.BeginTx(ctx, nil)
108+ if err != nil {
109+ return err
110+ }
111+ defer func() {
112+ err = tx.Rollback()
113+ }()
114+
115+ err = me.removeTagsForPost(tx, postID)
116+ if err != nil {
117+ return err
118+ }
119+
120+ _, err = me.insertTagsForPost(tx, tags, postID)
121+ if err != nil {
122+ return err
123+ }
124+
125+ err = tx.Commit()
126+ return err
127+}
128+
129+func (me *PsqlDB) FindUserPostsByTag(tag, userID, space string) ([]*db.Post, error) {
130+ var posts []*db.Post
131+ rs, err := me.Db.Query(sqlSelectUserPostsByTag, userID, tag, space)
132+ if err != nil {
133+ return posts, err
134+ }
135+ for rs.Next() {
136+ post := &db.Post{}
137+ err := rs.Scan(
138+ &post.ID,
139+ &post.UserID,
140+ &post.Filename,
141+ &post.Slug,
142+ &post.Title,
143+ &post.Text,
144+ &post.Description,
145+ &post.PublishAt,
146+ &post.Username,
147+ &post.UpdatedAt,
148+ )
149+ if err != nil {
150+ return posts, err
151+ }
152+
153+ posts = append(posts, post)
154+ }
155+ if rs.Err() != nil {
156+ return posts, rs.Err()
157+ }
158+ return posts, nil
159+}
160+
161+func (me *PsqlDB) FindPostsByTag(tag, space string) ([]*db.Post, error) {
162+ var posts []*db.Post
163+ rs, err := me.Db.Query(sqlSelectPostsByTag, tag, space)
164+ if err != nil {
165+ return posts, err
166+ }
167+ for rs.Next() {
168+ post := &db.Post{}
169+ err := rs.Scan(
170+ &post.ID,
171+ &post.UserID,
172+ &post.Filename,
173+ &post.Slug,
174+ &post.Title,
175+ &post.Text,
176+ &post.Description,
177+ &post.PublishAt,
178+ &post.Username,
179+ &post.UpdatedAt,
180+ )
181+ if err != nil {
182+ return posts, err
183+ }
184+
185+ posts = append(posts, post)
186+ }
187+ if rs.Err() != nil {
188+ return posts, rs.Err()
189+ }
190+ return posts, nil
191+}
192+
193+func (me *PsqlDB) FindPopularTags() ([]string, error) {
194+ tags := make([]string, 0)
195+ rs, err := me.Db.Query(sqlSelectPopularTags)
196+ if err != nil {
197+ return tags, err
198+ }
199+ for rs.Next() {
200+ name := ""
201+ err := rs.Scan(name)
202+ if err != nil {
203+ return tags, err
204+ }
205+
206+ tags = append(tags, name)
207+ }
208+ if rs.Err() != nil {
209+ return tags, rs.Err()
210+ }
211+ return tags, nil
212+}