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