repos / pico

commit

2755bd8

parent

5979c6d

author

Eric Bower

date

2022-11-09 02:59:19 +0000 UTC

refactor(pastes): add col `expires_at` to posts

Currently we figure out how to delete pastes by having a cron goroutine
check on an interval whether or not posts are older than 3 days.
Instead, we want to provide the ability for that delete time to be
determined by individual records.

So we added `expires_at` column to our `posts` table.

I also extended the default expiration from 3 to 7 days.

 1@@ -71,11 +71,11 @@ migrate:
 2 	docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20220801_add_post_tags.sql
 3 	docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20220811_add_data_to_post.sql
 4 	docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20220811_add_feature.sql
 5+	docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20221108_add_expires_at_to_posts.sql
 6 .PHONY: migrate
 7 
 8 latest:
 9-	docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20220811_add_data_to_post.sql
10-	docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20220811_add_feature.sql
11+	docker exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20221108_add_expires_at_to_posts.sql
12 .PHONY: latest
13 
14 psql:

 1@@ -59,6 +59,7 @@ type Post struct {
 2 	PublishAt   *time.Time `json:"publish_at"`
 3 	Username    string     `json:"username"`
 4 	UpdatedAt   *time.Time `json:"updated_at"`
 5+	ExpiresAt   *time.Time `json:"expires_at"`
 6 	Hidden      bool       `json:"hidden"`
 7 	Views       int        `json:"views"`
 8 	Space       string     `json:"space"`
 9@@ -138,6 +139,7 @@ type DB interface {
10 	FindPostsForUser(pager *Pager, userID string, space string) (*Paginate[*Post], error)
11 	FindAllPostsForUser(userID string, space string) ([]*Post, error)
12 	FindPostsBeforeDate(date *time.Time, space string) ([]*Post, error)
13+	FindExpiredPosts(space string) ([]*Post, error)
14 	FindUpdatedPostsForUser(userID string, space string) ([]*Post, error)
15 	FindPostWithFilename(filename string, userID string, space string) (*Post, error)
16 	FindPostWithSlug(slug string, userID string, space string) (*Post, error)

 1@@ -20,7 +20,7 @@ var PAGER_SIZE = 15
 2 
 3 var SelectPost = `
 4 	posts.id, user_id, app_users.name, filename, slug, title, text, description,
 5-	posts.created_at, publish_at, posts.updated_at, hidden, file_size, mime_type, shasum, data`
 6+	posts.created_at, publish_at, posts.updated_at, hidden, file_size, mime_type, shasum, data, expires_at`
 7 
 8 var (
 9 	sqlSelectPosts = fmt.Sprintf(`
10@@ -63,6 +63,15 @@ var (
11 	WHERE user_id = $1 AND publish_at::date <= CURRENT_DATE AND cur_space = $2
12 	ORDER BY posts.updated_at DESC`, SelectPost)
13 
14+	sqlSelectExpiredPosts = fmt.Sprintf(`
15+		SELECT %s
16+		FROM posts
17+		LEFT OUTER JOIN app_users ON app_users.id = posts.user_id
18+		WHERE
19+			cur_space = $1 AND
20+			expires_at <= now();
21+	`, SelectPost)
22+
23 	sqlSelectPostsForUser = fmt.Sprintf(`
24 	SELECT %s, STRING_AGG(coalesce(post_tags.name, ''), ',') tags
25 	FROM posts
26@@ -222,8 +231,8 @@ const (
27 	sqlInsertPost      = `
28 	INSERT INTO posts
29 		(user_id, filename, slug, title, text, description, publish_at, hidden, cur_space,
30-		file_size, mime_type, shasum, data)
31-	VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
32+		file_size, mime_type, shasum, data, expires_at)
33+	VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
34 	RETURNING id`
35 	sqlInsertUser = `INSERT INTO app_users DEFAULT VALUES returning id`
36 	sqlInsertTag  = `INSERT INTO post_tags (post_id, name) VALUES($1, $2) RETURNING id;`
37@@ -270,6 +279,7 @@ func CreatePostFromRow(r RowScanner) (*db.Post, error) {
38 		&post.MimeType,
39 		&post.Shasum,
40 		&post.Data,
41+		&post.ExpiresAt,
42 	)
43 	if err != nil {
44 		return nil, err
45@@ -297,6 +307,7 @@ func CreatePostWithTagsFromRow(r RowScanner) (*db.Post, error) {
46 		&post.MimeType,
47 		&post.Shasum,
48 		&post.Data,
49+		&post.ExpiresAt,
50 		&tagStr,
51 	)
52 	if err != nil {
53@@ -678,6 +689,7 @@ func (me *PsqlDB) InsertPost(post *db.Post) (*db.Post, error) {
54 		post.MimeType,
55 		post.Shasum,
56 		post.Data,
57+		post.ExpiresAt,
58 	).Scan(&id)
59 	if err != nil {
60 		return nil, err
61@@ -791,6 +803,27 @@ func (me *PsqlDB) FindPosts() ([]*db.Post, error) {
62 	return posts, nil
63 }
64 
65+func (me *PsqlDB) FindExpiredPosts(space string) ([]*db.Post, error) {
66+	var posts []*db.Post
67+	fmt.Println(sqlSelectExpiredPosts)
68+	rs, err := me.Db.Query(sqlSelectExpiredPosts, space)
69+	if err != nil {
70+		return posts, err
71+	}
72+	for rs.Next() {
73+		post, err := CreatePostFromRow(rs)
74+		if err != nil {
75+			return nil, err
76+		}
77+
78+		posts = append(posts, post)
79+	}
80+	if rs.Err() != nil {
81+		return posts, rs.Err()
82+	}
83+	return posts, nil
84+}
85+
86 func (me *PsqlDB) FindUpdatedPostsForUser(userID string, space string) ([]*db.Post, error) {
87 	var posts []*db.Post
88 	rs, err := me.Db.Query(sqlSelectUpdatedPostsForUser, userID, space)

1@@ -254,6 +254,7 @@ func (h *ScpUploadHandler) Write(s ssh.Session, entry *utils.FileEntry) (string,
2 			Slug:        metadata.Slug,
3 			Text:        metadata.Text,
4 			Title:       metadata.Title,
5+			ExpiresAt:   metadata.ExpiresAt,
6 		}
7 		post, err = h.DBPool.InsertPost(&insertPost)
8 		if err != nil {

 1@@ -9,10 +9,7 @@ import (
 2 
 3 func deleteExpiredPosts(cfg *shared.ConfigSite, dbpool db.DB) error {
 4 	cfg.Logger.Infof("checking for expired posts")
 5-	now := time.Now()
 6-	// delete posts that are older than three days
 7-	expired := now.AddDate(0, 0, -3)
 8-	posts, err := dbpool.FindPostsBeforeDate(&expired, cfg.Space)
 9+	posts, err := dbpool.FindExpiredPosts(cfg.Space)
10 	if err != nil {
11 		return err
12 	}
13@@ -22,7 +19,7 @@ func deleteExpiredPosts(cfg *shared.ConfigSite, dbpool db.DB) error {
14 		postIds = append(postIds, post.ID)
15 	}
16 
17-	cfg.Logger.Infof("deleteing (%d) expired posts", len(postIds))
18+	cfg.Logger.Infof("deleting (%d) expired posts", len(postIds))
19 	err = dbpool.RemovePosts(postIds)
20 	if err != nil {
21 		return err

 1@@ -76,7 +76,7 @@
 2     <section>
 3         <h2 class="text-lg font-bold">Features</h2>
 4         <ul>
 5-            <li>Pastes last 3 days</li>
 6+            <li>Pastes last 7 days</li>
 7             <li>Bring your own editor</li>
 8             <li>You control the source files</li>
 9             <li>Terminal workflow with no installation</li>
10@@ -89,6 +89,13 @@
11         </ul>
12     </section>
13 
14+    <section>
15+        <h2 class="text-lg font-bold">Read the docs</h2>
16+        <p>
17+            <a href="/help">docs</a>
18+        </p>
19+    </section>
20+
21     <section>
22         <h2 class="text-lg font-bold">Roadmap</h2>
23         <ol>

 1@@ -2,6 +2,7 @@ package pastes
 2 
 3 import (
 4 	"fmt"
 5+	"time"
 6 
 7 	"git.sr.ht/~erock/pico/db"
 8 	"git.sr.ht/~erock/pico/filehandlers"
 9@@ -29,5 +30,10 @@ func (p *FileHooks) FileMeta(data *filehandlers.PostMetaData) error {
10 	data.Title = shared.ToUpper(data.Slug)
11 	// we want the slug to be the filename for pastes
12 	data.Slug = data.Filename
13+	if data.Post.ExpiresAt == nil || data.Post.ExpiresAt.IsZero() {
14+		// mark posts for deletion a week after creation
15+		expiresAt := time.Now().AddDate(0, 0, 7)
16+		data.ExpiresAt = &expiresAt
17+	}
18 	return nil
19 }

1@@ -0,0 +1,3 @@
2+ALTER TABLE posts ADD expires_at timestamp without time zone;
3+
4+UPDATE posts SET expires_at = NOW() + INTERVAL '7 day' WHERE cur_space='pastes';