Antonio Mika
·
2026-10-25
1package postgres
2
3import (
4 "database/sql"
5 "fmt"
6 "strings"
7
8 "github.com/picosh/pico/pkg/db"
9)
10
11var SelectPost = `
12 posts.id, user_id, app_users.name, filename, slug, title, text, description,
13 posts.created_at, publish_at, posts.updated_at, hidden, file_size, mime_type, shasum, data, expires_at, views`
14
15func CreatePostWithTagsByRow(r RowScanner) (*db.Post, error) {
16 post := &db.Post{}
17 tagStr := ""
18 err := r.Scan(
19 &post.ID,
20 &post.UserID,
21 &post.Username,
22 &post.Filename,
23 &post.Slug,
24 &post.Title,
25 &post.Text,
26 &post.Description,
27 &post.CreatedAt,
28 &post.PublishAt,
29 &post.UpdatedAt,
30 &post.Hidden,
31 &post.FileSize,
32 &post.MimeType,
33 &post.Shasum,
34 &post.Data,
35 &post.ExpiresAt,
36 &post.Views,
37 &tagStr,
38 )
39 if err != nil {
40 return nil, err
41 }
42
43 tags := strings.Split(tagStr, ",")
44 for _, tag := range tags {
45 tg := strings.TrimSpace(tag)
46 if tg == "" {
47 continue
48 }
49 post.Tags = append(post.Tags, tg)
50 }
51
52 return post, nil
53}
54
55func (me *PsqlDB) FindPostWithFilename(filename string, persona_id string, space string) (*db.Post, error) {
56 query := fmt.Sprintf(`
57 SELECT %s, STRING_AGG(coalesce(post_tags.name, ''), ',') tags
58 FROM posts
59 LEFT JOIN app_users ON app_users.id = posts.user_id
60 LEFT JOIN post_tags ON post_tags.post_id = posts.id
61 WHERE filename = $1 AND user_id = $2 AND cur_space = $3
62 GROUP BY %s`, SelectPost, SelectPost)
63 r := me.Db.QueryRow(query, filename, persona_id, space)
64 post, err := CreatePostWithTagsByRow(r)
65 if err != nil {
66 return nil, err
67 }
68
69 return post, nil
70}
71
72func (me *PsqlDB) FindPostWithSlug(slug string, user_id string, space string) (*db.Post, error) {
73 query := fmt.Sprintf(`
74 SELECT %s, STRING_AGG(coalesce(post_tags.name, ''), ',') tags
75 FROM posts
76 LEFT JOIN app_users ON app_users.id = posts.user_id
77 LEFT JOIN post_tags ON post_tags.post_id = posts.id
78 WHERE slug = $1 AND user_id = $2 AND cur_space = $3
79 GROUP BY %s`, SelectPost, SelectPost)
80 r := me.Db.QueryRow(query, slug, user_id, space)
81 post, err := CreatePostWithTagsByRow(r)
82 if err != nil {
83 // attempt to find post inside post_aliases
84 alias := me.Db.QueryRow(
85 `SELECT post_aliases.post_id FROM post_aliases
86 INNER JOIN posts ON posts.id = post_aliases.post_id
87 WHERE post_aliases.slug = $1 AND posts.user_id = $2`,
88 slug, user_id,
89 )
90 postID := ""
91 err := alias.Scan(&postID)
92 if err != nil {
93 return nil, err
94 }
95
96 return me.FindPost(postID)
97 }
98
99 return post, nil
100}
101
102func (me *PsqlDB) FindPost(postID string) (*db.Post, error) {
103 post := &db.Post{}
104 query := fmt.Sprintf(`
105 SELECT %s
106 FROM posts
107 LEFT JOIN app_users ON app_users.id = posts.user_id
108 WHERE posts.id = $1`, SelectPost)
109 err := me.Db.Get(post, query, postID)
110 if err != nil {
111 return nil, err
112 }
113 return post, nil
114}
115
116func (me *PsqlDB) InsertPost(post *db.Post) (*db.Post, error) {
117 var id string
118 query := `
119 INSERT INTO posts
120 (user_id, filename, slug, title, text, description, publish_at, hidden, cur_space,
121 file_size, mime_type, shasum, data, expires_at, updated_at)
122 VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)
123 RETURNING id`
124 err := me.Db.QueryRow(
125 query,
126 post.UserID,
127 post.Filename,
128 post.Slug,
129 post.Title,
130 post.Text,
131 post.Description,
132 post.PublishAt,
133 post.Hidden,
134 post.Space,
135 post.FileSize,
136 post.MimeType,
137 post.Shasum,
138 post.Data,
139 post.ExpiresAt,
140 post.UpdatedAt,
141 ).Scan(&id)
142 if err != nil {
143 return nil, err
144 }
145
146 return me.FindPost(id)
147}
148
149func (me *PsqlDB) UpdatePost(post *db.Post) (*db.Post, error) {
150 query := `
151 UPDATE posts
152 SET slug = $1, title = $2, text = $3, description = $4, updated_at = $5, publish_at = $6,
153 file_size = $7, shasum = $8, data = $9, hidden = $11, expires_at = $12
154 WHERE id = $10`
155 _, err := me.Db.Exec(
156 query,
157 post.Slug,
158 post.Title,
159 post.Text,
160 post.Description,
161 post.UpdatedAt,
162 post.PublishAt,
163 post.FileSize,
164 post.Shasum,
165 post.Data,
166 post.ID,
167 post.Hidden,
168 post.ExpiresAt,
169 )
170 if err != nil {
171 return nil, err
172 }
173
174 return me.FindPost(post.ID)
175}
176
177func (me *PsqlDB) RenamePost(cur, next, replaced *db.Post, tags, aliases []string) (*db.Post, error) {
178 tx, err := me.Db.Beginx()
179 if err != nil {
180 return nil, err
181 }
182 defer func() {
183 _ = tx.Rollback()
184 }()
185
186 affected := func(res sql.Result, err error) error {
187 if err != nil {
188 return err
189 }
190 n, err := res.RowsAffected()
191 if err != nil {
192 return err
193 }
194 if n == 0 {
195 return db.ErrPostChanged
196 }
197 return nil
198 }
199
200 // A save of the replaced post since it was read is kept.
201 if replaced != nil {
202 err := affected(tx.Exec(
203 `DELETE FROM posts WHERE id = $1 AND filename = $2 AND text = $3 AND updated_at IS NOT DISTINCT FROM $4`,
204 replaced.ID, replaced.Filename, replaced.Text, replaced.UpdatedAt,
205 ))
206 if err != nil {
207 return nil, err
208 }
209 }
210
211 // The update only applies to the post as the caller read it, so a save
212 // in between is never overwritten. It sets only what a new name can
213 // change, which leaves data that other writers keep, like feed digests.
214 query := `
215 UPDATE posts
216 SET filename = $1, slug = $2, title = $3, description = $4, publish_at = $5, hidden = $6,
217 expires_at = $7, mime_type = $8
218 WHERE id = $9 AND filename = $10 AND slug = $11 AND title = $12 AND text = $13
219 AND description = $14 AND updated_at IS NOT DISTINCT FROM $15
220 AND publish_at IS NOT DISTINCT FROM $16 AND hidden = $17
221 AND expires_at IS NOT DISTINCT FROM $18`
222 err = affected(tx.Exec(
223 query,
224 next.Filename,
225 next.Slug,
226 next.Title,
227 next.Description,
228 next.PublishAt,
229 next.Hidden,
230 next.ExpiresAt,
231 next.MimeType,
232 cur.ID,
233 cur.Filename,
234 cur.Slug,
235 cur.Title,
236 cur.Text,
237 cur.Description,
238 cur.UpdatedAt,
239 cur.PublishAt,
240 cur.Hidden,
241 cur.ExpiresAt,
242 ))
243 if err != nil {
244 return nil, err
245 }
246
247 if err := me.removeTagsByPost(tx, cur.ID); err != nil {
248 return nil, err
249 }
250 if _, err := me.insertTagsByPost(tx, tags, cur.ID); err != nil {
251 return nil, err
252 }
253 if err := me.removeAliasesByPost(tx, cur.ID); err != nil {
254 return nil, err
255 }
256 if _, err := me.insertAliasesByPost(tx, aliases, cur.ID); err != nil {
257 return nil, err
258 }
259 if err := tx.Commit(); err != nil {
260 return nil, err
261 }
262
263 return me.FindPost(cur.ID)
264}
265
266func (me *PsqlDB) RemovePosts(postIDs []string) error {
267 param := "{" + strings.Join(postIDs, ",") + "}"
268 _, err := me.Db.Exec(`DELETE FROM posts WHERE id = ANY($1::uuid[])`, param)
269 return err
270}
271
272func (me *PsqlDB) FindPostsByUser(page *db.Pager, userID string, space string) (*db.Paginate[*db.Post], error) {
273 var posts []*db.Post
274 query := fmt.Sprintf(`
275 SELECT %s, STRING_AGG(coalesce(post_tags.name, ''), ',') tags
276 FROM posts
277 LEFT JOIN app_users ON app_users.id = posts.user_id
278 LEFT JOIN post_tags ON post_tags.post_id = posts.id
279 WHERE
280 hidden = FALSE AND
281 user_id = $1 AND
282 publish_at::date <= CURRENT_DATE AND
283 cur_space = $2
284 GROUP BY %s
285 ORDER BY publish_at DESC, slug DESC
286 LIMIT $3 OFFSET $4`, SelectPost, SelectPost)
287 rs, err := me.Db.Queryx(
288 query,
289 userID,
290 space,
291 page.Num,
292 page.Num*page.Page,
293 )
294 if err != nil {
295 return nil, err
296 }
297 defer func() { _ = rs.Close() }()
298 for rs.Next() {
299 post, err := CreatePostWithTagsByRow(rs)
300 if err != nil {
301 return nil, err
302 }
303
304 posts = append(posts, post)
305 }
306
307 if rs.Err() != nil {
308 return nil, rs.Err()
309 }
310
311 return &db.Paginate[*db.Post]{Data: posts}, nil
312}
313
314func (me *PsqlDB) FindAllPostsByUser(userID string, space string) ([]*db.Post, error) {
315 var posts []*db.Post
316 query := fmt.Sprintf(`
317 SELECT %s
318 FROM posts
319 LEFT JOIN app_users ON app_users.id = posts.user_id
320 WHERE
321 user_id = $1 AND
322 cur_space = $2
323 ORDER BY publish_at DESC`, SelectPost)
324 err := me.Db.Select(&posts, query, userID, space)
325 if err != nil {
326 return nil, err
327 }
328 return posts, nil
329}
330
331func (me *PsqlDB) FindPosts() ([]*db.Post, error) {
332 var posts []*db.Post
333 query := fmt.Sprintf(`
334 SELECT %s
335 FROM posts
336 LEFT JOIN app_users ON app_users.id = posts.user_id`, SelectPost)
337 err := me.Db.Select(&posts, query)
338 if err != nil {
339 return nil, err
340 }
341 return posts, nil
342}