Eric Bower
·
08 Sep 24
storage.go
1package postgres
2
3import (
4 "context"
5 "database/sql"
6 "errors"
7 "fmt"
8 "log/slog"
9 "math"
10 "strings"
11 "time"
12
13 "slices"
14
15 _ "github.com/lib/pq"
16 "github.com/picosh/pico/db"
17 "github.com/picosh/pico/shared"
18)
19
20var PAGER_SIZE = 15
21
22var SelectPost = `
23 posts.id, user_id, app_users.name, filename, slug, title, text, description,
24 posts.created_at, publish_at, posts.updated_at, hidden, file_size, mime_type, shasum, data, expires_at, views`
25
26var (
27 sqlSelectPosts = fmt.Sprintf(`
28 SELECT %s
29 FROM posts
30 LEFT JOIN app_users ON app_users.id = posts.user_id`, SelectPost)
31
32 sqlSelectPostsBeforeDate = fmt.Sprintf(`
33 SELECT %s
34 FROM posts
35 LEFT JOIN app_users ON app_users.id = posts.user_id
36 WHERE publish_at::date <= $1 AND cur_space = $2`, SelectPost)
37
38 sqlSelectPostWithFilename = fmt.Sprintf(`
39 SELECT %s, STRING_AGG(coalesce(post_tags.name, ''), ',') tags
40 FROM posts
41 LEFT JOIN app_users ON app_users.id = posts.user_id
42 LEFT JOIN post_tags ON post_tags.post_id = posts.id
43 WHERE filename = $1 AND user_id = $2 AND cur_space = $3
44 GROUP BY %s`, SelectPost, SelectPost)
45
46 sqlSelectPostWithSlug = fmt.Sprintf(`
47 SELECT %s, STRING_AGG(coalesce(post_tags.name, ''), ',') tags
48 FROM posts
49 LEFT JOIN app_users ON app_users.id = posts.user_id
50 LEFT JOIN post_tags ON post_tags.post_id = posts.id
51 WHERE slug = $1 AND user_id = $2 AND cur_space = $3
52 GROUP BY %s`, SelectPost, SelectPost)
53
54 sqlSelectPost = fmt.Sprintf(`
55 SELECT %s
56 FROM posts
57 LEFT JOIN app_users ON app_users.id = posts.user_id
58 WHERE posts.id = $1`, SelectPost)
59
60 sqlSelectUpdatedPostsForUser = fmt.Sprintf(`
61 SELECT %s
62 FROM posts
63 LEFT JOIN app_users ON app_users.id = posts.user_id
64 WHERE user_id = $1 AND publish_at::date <= CURRENT_DATE AND cur_space = $2
65 ORDER BY posts.updated_at DESC`, SelectPost)
66
67 sqlSelectExpiredPosts = fmt.Sprintf(`
68 SELECT %s
69 FROM posts
70 LEFT JOIN app_users ON app_users.id = posts.user_id
71 WHERE
72 cur_space = $1 AND
73 expires_at <= now();
74 `, SelectPost)
75
76 sqlSelectPostsForUser = fmt.Sprintf(`
77 SELECT %s, STRING_AGG(coalesce(post_tags.name, ''), ',') tags
78 FROM posts
79 LEFT JOIN app_users ON app_users.id = posts.user_id
80 LEFT JOIN post_tags ON post_tags.post_id = posts.id
81 WHERE
82 hidden = FALSE AND
83 user_id = $1 AND
84 publish_at::date <= CURRENT_DATE AND
85 cur_space = $2
86 GROUP BY %s
87 ORDER BY publish_at DESC, slug DESC
88 LIMIT $3 OFFSET $4`, SelectPost, SelectPost)
89
90 sqlSelectAllPostsForUser = fmt.Sprintf(`
91 SELECT %s
92 FROM posts
93 LEFT JOIN app_users ON app_users.id = posts.user_id
94 WHERE
95 user_id = $1 AND
96 cur_space = $2
97 ORDER BY publish_at DESC`, SelectPost)
98
99 sqlSelectPostsByTag = `
100 SELECT
101 posts.id,
102 user_id,
103 filename,
104 slug,
105 title,
106 text,
107 description,
108 publish_at,
109 app_users.name as username,
110 posts.updated_at,
111 posts.mime_type
112 FROM posts
113 LEFT JOIN app_users ON app_users.id = posts.user_id
114 LEFT JOIN post_tags ON post_tags.post_id = posts.id
115 WHERE
116 post_tags.name = $3 AND
117 publish_at::date <= CURRENT_DATE AND
118 cur_space = $4
119 ORDER BY publish_at DESC
120 LIMIT $1 OFFSET $2`
121
122 sqlSelectUserPostsByTag = fmt.Sprintf(`
123 SELECT %s
124 FROM posts
125 LEFT JOIN app_users ON app_users.id = posts.user_id
126 LEFT JOIN post_tags ON post_tags.post_id = posts.id
127 WHERE
128 hidden = FALSE AND
129 user_id = $1 AND
130 (post_tags.name = $2 OR hidden = true) AND
131 publish_at::date <= CURRENT_DATE AND
132 cur_space = $3
133 ORDER BY publish_at DESC
134 LIMIT $4 OFFSET $5`, SelectPost)
135)
136
137const (
138 sqlSelectPublicKey = `SELECT id, user_id, name, public_key, created_at FROM public_keys WHERE public_key = $1`
139 sqlSelectPublicKeys = `SELECT id, user_id, name, public_key, created_at FROM public_keys WHERE user_id = $1 ORDER BY created_at ASC`
140 sqlSelectUser = `SELECT id, name, created_at FROM app_users WHERE id = $1`
141 sqlSelectUserForName = `SELECT id, name, created_at FROM app_users WHERE name = $1`
142 sqlSelectUserForNameAndKey = `SELECT app_users.id, app_users.name, app_users.created_at, public_keys.id as pk_id, public_keys.public_key, public_keys.created_at as pk_created_at FROM app_users LEFT JOIN public_keys ON public_keys.user_id = app_users.id WHERE app_users.name = $1 AND public_keys.public_key = $2`
143 sqlSelectUsers = `SELECT id, name, created_at FROM app_users ORDER BY name ASC`
144
145 sqlSelectUserForToken = `
146 SELECT app_users.id, app_users.name, app_users.created_at
147 FROM app_users
148 LEFT JOIN tokens ON tokens.user_id = app_users.id
149 WHERE tokens.token = $1 AND tokens.expires_at > NOW()`
150 sqlInsertToken = `INSERT INTO tokens (user_id, name) VALUES($1, $2) RETURNING token;`
151 sqlRemoveToken = `DELETE FROM tokens WHERE id = $1`
152 sqlSelectTokensForUser = `SELECT id, user_id, name, created_at, expires_at FROM tokens WHERE user_id = $1`
153 sqlSelectTokenByNameForUser = `SELECT token FROM tokens WHERE user_id = $1 AND name = $2`
154
155 sqlSelectTotalUsers = `SELECT count(id) FROM app_users`
156 sqlSelectUsersAfterDate = `SELECT count(id) FROM app_users WHERE created_at >= $1`
157 sqlSelectTotalPosts = `SELECT count(id) FROM posts WHERE cur_space = $1`
158 sqlSelectTotalPostsAfterDate = `SELECT count(id) FROM posts WHERE created_at >= $1 AND cur_space = $2`
159 sqlSelectUsersWithPost = `SELECT count(app_users.id) FROM app_users WHERE EXISTS (SELECT 1 FROM posts WHERE user_id = app_users.id AND cur_space = $1);`
160
161 sqlSelectFeatureForUser = `SELECT id, user_id, payment_history_id, name, data, created_at, expires_at FROM feature_flags WHERE user_id = $1 AND name = $2 ORDER BY expires_at DESC LIMIT 1`
162 sqlSelectSizeForUser = `SELECT COALESCE(sum(file_size), 0) FROM posts WHERE user_id = $1`
163
164 sqlSelectPostIdByAliasSlug = `SELECT post_id FROM post_aliases WHERE slug = $1`
165 sqlSelectTagPostCount = `
166 SELECT count(posts.id)
167 FROM posts
168 LEFT JOIN post_tags ON post_tags.post_id = posts.id
169 WHERE hidden = FALSE AND cur_space=$1 and post_tags.name = $2`
170 sqlSelectPostCount = `SELECT count(id) FROM posts WHERE hidden = FALSE AND cur_space=$1`
171 sqlSelectAllUpdatedPosts = `
172 SELECT
173 posts.id,
174 user_id,
175 filename,
176 slug,
177 title,
178 text,
179 description,
180 publish_at,
181 app_users.name as username,
182 posts.updated_at,
183 posts.mime_type
184 FROM posts
185 LEFT JOIN app_users ON app_users.id = posts.user_id
186 WHERE hidden = FALSE AND publish_at::date <= CURRENT_DATE AND cur_space = $3
187 ORDER BY updated_at DESC
188 LIMIT $1 OFFSET $2`
189 // add some users to deny list since they are robogenerating a bunch of posts
190 // per day and are creating a lot of noise.
191 sqlSelectPostsByRank = `
192 SELECT
193 posts.id,
194 user_id,
195 filename,
196 slug,
197 title,
198 text,
199 description,
200 publish_at,
201 app_users.name as username,
202 posts.updated_at,
203 posts.mime_type
204 FROM posts
205 LEFT JOIN app_users ON app_users.id = posts.user_id
206 WHERE
207 hidden = FALSE AND
208 publish_at::date <= CURRENT_DATE AND
209 cur_space = $3 AND
210 app_users.name NOT IN ('algiegray', 'mrrccc')
211 ORDER BY publish_at DESC
212 LIMIT $1 OFFSET $2`
213
214 sqlSelectPopularTags = `
215 SELECT name, count(post_id) as "tally"
216 FROM post_tags
217 LEFT JOIN posts ON posts.id = post_id
218 WHERE posts.cur_space = $1
219 GROUP BY name
220 ORDER BY tally DESC
221 LIMIT 5`
222 sqlSelectTagsForPost = `SELECT name FROM post_tags WHERE post_id=$1`
223 sqlSelectFeedItemsByPost = `SELECT id, post_id, guid, data, created_at FROM feed_items WHERE post_id=$1`
224
225 sqlInsertPublicKey = `INSERT INTO public_keys (user_id, public_key) VALUES ($1, $2)`
226 sqlInsertPost = `
227 INSERT INTO posts
228 (user_id, filename, slug, title, text, description, publish_at, hidden, cur_space,
229 file_size, mime_type, shasum, data, expires_at, updated_at)
230 VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)
231 RETURNING id`
232 sqlInsertUser = `INSERT INTO app_users (name) VALUES($1) returning id`
233 sqlInsertTag = `INSERT INTO post_tags (post_id, name) VALUES($1, $2) RETURNING id;`
234 sqlInsertAliases = `INSERT INTO post_aliases (post_id, slug) VALUES($1, $2) RETURNING id;`
235 sqlInsertFeedItems = `INSERT INTO feed_items (post_id, guid, data) VALUES ($1, $2, $3) RETURNING id;`
236
237 sqlUpdatePost = `
238 UPDATE posts
239 SET slug = $1, title = $2, text = $3, description = $4, updated_at = $5, publish_at = $6,
240 file_size = $7, shasum = $8, data = $9, hidden = $11, expires_at = $12
241 WHERE id = $10`
242 sqlUpdateUserName = `UPDATE app_users SET name = $1 WHERE id = $2`
243 sqlIncrementViews = `UPDATE posts SET views = views + 1 WHERE id = $1 RETURNING views`
244
245 sqlRemoveAliasesByPost = `DELETE FROM post_aliases WHERE post_id = $1`
246 sqlRemoveTagsByPost = `DELETE FROM post_tags WHERE post_id = $1`
247 sqlRemovePosts = `DELETE FROM posts WHERE id = ANY($1::uuid[])`
248 sqlRemoveKeys = `DELETE FROM public_keys WHERE id = ANY($1::uuid[])`
249 sqlRemoveUsers = `DELETE FROM app_users WHERE id = ANY($1::uuid[])`
250
251 sqlInsertProject = `INSERT INTO projects (user_id, name, project_dir) VALUES ($1, $2, $3) RETURNING id;`
252 sqlUpdateProject = `UPDATE projects SET updated_at = $3 WHERE user_id = $1 AND name = $2;`
253 sqlUpdateProjectAcl = `UPDATE projects SET acl = $3, updated_at = $4 WHERE user_id = $1 AND name = $2;`
254 sqlFindProjectByName = `SELECT id, user_id, name, project_dir, acl, blocked, created_at, updated_at FROM projects WHERE user_id = $1 AND name = $2;`
255 sqlSelectProjectCount = `SELECT count(id) FROM projects`
256 sqlFindProjectsByUser = `SELECT id, user_id, name, project_dir, acl, blocked, created_at, updated_at FROM projects WHERE user_id = $1 ORDER BY name ASC, updated_at DESC;`
257 sqlFindProjectsByPrefix = `SELECT id, user_id, name, project_dir, acl, blocked, created_at, updated_at FROM projects WHERE user_id = $1 AND name = project_dir AND name ILIKE $2 ORDER BY updated_at ASC, name ASC;`
258 sqlFindProjectLinks = `SELECT id, user_id, name, project_dir, acl, blocked, created_at, updated_at FROM projects WHERE user_id = $1 AND name != project_dir AND project_dir = $2 ORDER BY name ASC;`
259 sqlLinkToProject = `UPDATE projects SET project_dir = $1, updated_at = $2 WHERE id = $3;`
260 sqlRemoveProject = `DELETE FROM projects WHERE id = $1;`
261)
262
263type PsqlDB struct {
264 Logger *slog.Logger
265 Db *sql.DB
266}
267
268type RowScanner interface {
269 Scan(dest ...any) error
270}
271
272func CreatePostFromRow(r RowScanner) (*db.Post, error) {
273 post := &db.Post{}
274 err := r.Scan(
275 &post.ID,
276 &post.UserID,
277 &post.Username,
278 &post.Filename,
279 &post.Slug,
280 &post.Title,
281 &post.Text,
282 &post.Description,
283 &post.CreatedAt,
284 &post.PublishAt,
285 &post.UpdatedAt,
286 &post.Hidden,
287 &post.FileSize,
288 &post.MimeType,
289 &post.Shasum,
290 &post.Data,
291 &post.ExpiresAt,
292 &post.Views,
293 )
294 if err != nil {
295 return nil, err
296 }
297 return post, nil
298}
299
300func CreatePostWithTagsFromRow(r RowScanner) (*db.Post, error) {
301 post := &db.Post{}
302 tagStr := ""
303 err := r.Scan(
304 &post.ID,
305 &post.UserID,
306 &post.Username,
307 &post.Filename,
308 &post.Slug,
309 &post.Title,
310 &post.Text,
311 &post.Description,
312 &post.CreatedAt,
313 &post.PublishAt,
314 &post.UpdatedAt,
315 &post.Hidden,
316 &post.FileSize,
317 &post.MimeType,
318 &post.Shasum,
319 &post.Data,
320 &post.ExpiresAt,
321 &post.Views,
322 &tagStr,
323 )
324 if err != nil {
325 return nil, err
326 }
327
328 tags := strings.Split(tagStr, ",")
329 for _, tag := range tags {
330 tg := strings.TrimSpace(tag)
331 if tg == "" {
332 continue
333 }
334 post.Tags = append(post.Tags, tg)
335 }
336
337 return post, nil
338}
339
340func NewDB(databaseUrl string, logger *slog.Logger) *PsqlDB {
341 var err error
342 d := &PsqlDB{
343 Logger: logger,
344 }
345 d.Logger.Info("Connecting to postgres", "databaseUrl", databaseUrl)
346
347 db, err := sql.Open("postgres", databaseUrl)
348 if err != nil {
349 d.Logger.Error(err.Error())
350 }
351 d.Db = db
352 return d
353}
354
355func (me *PsqlDB) RegisterUser(username, pubkey, comment string) (*db.User, error) {
356 lowerName := strings.ToLower(username)
357 valid, err := me.ValidateName(lowerName)
358 if !valid {
359 return nil, err
360 }
361
362 ctx := context.Background()
363 tx, err := me.Db.BeginTx(ctx, nil)
364 if err != nil {
365 return nil, err
366 }
367 defer func() {
368 err = tx.Rollback()
369 }()
370
371 stmt, err := tx.Prepare(sqlInsertUser)
372 if err != nil {
373 return nil, err
374 }
375 defer stmt.Close()
376
377 var id string
378 err = stmt.QueryRow(lowerName).Scan(&id)
379 if err != nil {
380 return nil, err
381 }
382
383 err = me.InsertPublicKey(id, pubkey, comment, tx)
384 if err != nil {
385 return nil, err
386 }
387
388 err = tx.Commit()
389 if err != nil {
390 return nil, err
391 }
392
393 return me.FindUserForKey(username, pubkey)
394}
395
396func (me *PsqlDB) RemoveUsers(userIDs []string) error {
397 param := "{" + strings.Join(userIDs, ",") + "}"
398 _, err := me.Db.Exec(sqlRemoveUsers, param)
399 return err
400}
401
402func (me *PsqlDB) InsertPublicKey(userID, key, name string, tx *sql.Tx) error {
403 pk, _ := me.FindPublicKeyForKey(key)
404 if pk != nil {
405 return db.ErrPublicKeyTaken
406 }
407 query := `INSERT INTO public_keys (user_id, public_key, name) VALUES ($1, $2, $3)`
408 var err error
409 if tx != nil {
410 _, err = tx.Exec(query, userID, key, name)
411 } else {
412 _, err = me.Db.Exec(query, userID, key, name)
413 }
414 if err != nil {
415 return err
416 }
417
418 return nil
419}
420
421func (me *PsqlDB) UpdatePublicKey(pubkeyID, name string) (*db.PublicKey, error) {
422 pk, err := me.FindPublicKey(pubkeyID)
423 if err != nil {
424 return nil, err
425 }
426
427 query := `UPDATE public_keys SET name=$1 WHERE id=$2;`
428 _, err = me.Db.Exec(query, name, pk.ID)
429 if err != nil {
430 return nil, err
431 }
432
433 pk, err = me.FindPublicKey(pubkeyID)
434 if err != nil {
435 return nil, err
436 }
437 return pk, nil
438}
439
440func (me *PsqlDB) FindPublicKeyForKey(key string) (*db.PublicKey, error) {
441 var keys []*db.PublicKey
442 rs, err := me.Db.Query(sqlSelectPublicKey, key)
443 if err != nil {
444 return nil, err
445 }
446
447 for rs.Next() {
448 pk := &db.PublicKey{}
449 err := rs.Scan(&pk.ID, &pk.UserID, &pk.Name, &pk.Key, &pk.CreatedAt)
450 if err != nil {
451 return nil, err
452 }
453
454 keys = append(keys, pk)
455 }
456
457 if rs.Err() != nil {
458 return nil, rs.Err()
459 }
460
461 if len(keys) == 0 {
462 return nil, fmt.Errorf("pubkey not found in our database: [%s]", key)
463 }
464
465 // When we run PublicKeyForKey and there are multiple public keys returned from the database
466 // that should mean that we don't have the correct username for this public key.
467 // When that happens we need to reject the authentication and ask the user to provide the correct
468 // username when using ssh. So instead of `ssh <domain>` it should be `ssh user@<domain>`
469 if len(keys) > 1 {
470 return nil, &db.ErrMultiplePublicKeys{}
471 }
472
473 return keys[0], nil
474}
475
476func (me *PsqlDB) FindPublicKey(pubkeyID string) (*db.PublicKey, error) {
477 var keys []*db.PublicKey
478 rs, err := me.Db.Query(`SELECT id, user_id, name, public_key, created_at FROM public_keys WHERE id = $1`, pubkeyID)
479 if err != nil {
480 return nil, err
481 }
482
483 for rs.Next() {
484 pk := &db.PublicKey{}
485 err := rs.Scan(&pk.ID, &pk.UserID, &pk.Name, &pk.Key, &pk.CreatedAt)
486 if err != nil {
487 return nil, err
488 }
489
490 keys = append(keys, pk)
491 }
492
493 if rs.Err() != nil {
494 return nil, rs.Err()
495 }
496
497 if len(keys) == 0 {
498 return nil, errors.New("no public keys found for key provided")
499 }
500
501 return keys[0], nil
502}
503
504func (me *PsqlDB) FindKeysForUser(user *db.User) ([]*db.PublicKey, error) {
505 var keys []*db.PublicKey
506 rs, err := me.Db.Query(sqlSelectPublicKeys, user.ID)
507 if err != nil {
508 return keys, err
509 }
510 for rs.Next() {
511 pk := &db.PublicKey{}
512 err := rs.Scan(&pk.ID, &pk.UserID, &pk.Name, &pk.Key, &pk.CreatedAt)
513 if err != nil {
514 return keys, err
515 }
516
517 keys = append(keys, pk)
518 }
519 if rs.Err() != nil {
520 return keys, rs.Err()
521 }
522 return keys, nil
523}
524
525func (me *PsqlDB) RemoveKeys(keyIDs []string) error {
526 param := "{" + strings.Join(keyIDs, ",") + "}"
527 _, err := me.Db.Exec(sqlRemoveKeys, param)
528 return err
529}
530
531func (me *PsqlDB) FindSiteAnalytics(space string) (*db.Analytics, error) {
532 analytics := &db.Analytics{}
533 r := me.Db.QueryRow(sqlSelectTotalUsers)
534 err := r.Scan(&analytics.TotalUsers)
535 if err != nil {
536 return nil, err
537 }
538
539 r = me.Db.QueryRow(sqlSelectTotalPosts, space)
540 err = r.Scan(&analytics.TotalPosts)
541 if err != nil {
542 return nil, err
543 }
544
545 now := time.Now()
546 year, month, _ := now.Date()
547 begMonth := time.Date(year, month, 1, 0, 0, 0, 0, now.Location())
548
549 r = me.Db.QueryRow(sqlSelectTotalPostsAfterDate, begMonth, space)
550 err = r.Scan(&analytics.PostsLastMonth)
551 if err != nil {
552 return nil, err
553 }
554
555 r = me.Db.QueryRow(sqlSelectUsersAfterDate, begMonth)
556 err = r.Scan(&analytics.UsersLastMonth)
557 if err != nil {
558 return nil, err
559 }
560
561 r = me.Db.QueryRow(sqlSelectUsersWithPost, space)
562 err = r.Scan(&analytics.UsersWithPost)
563 if err != nil {
564 return nil, err
565 }
566
567 return analytics, nil
568}
569
570func (me *PsqlDB) FindPostsBeforeDate(date *time.Time, space string) ([]*db.Post, error) {
571 // now := time.Now()
572 // expired := now.AddDate(0, 0, -3)
573 var posts []*db.Post
574 rs, err := me.Db.Query(sqlSelectPostsBeforeDate, date, space)
575 if err != nil {
576 return posts, err
577 }
578 for rs.Next() {
579 post, err := CreatePostFromRow(rs)
580 if err != nil {
581 return nil, err
582 }
583
584 posts = append(posts, post)
585 }
586 if rs.Err() != nil {
587 return posts, rs.Err()
588 }
589 return posts, nil
590}
591
592func (me *PsqlDB) FindUserForKey(username string, key string) (*db.User, error) {
593 me.Logger.Info("attempting to find user with only public key", "key", key)
594 pk, err := me.FindPublicKeyForKey(key)
595 if err == nil {
596 me.Logger.Info("found pubkey, looking for user", "key", key, "userID", pk.UserID)
597 user, err := me.FindUser(pk.UserID)
598 if err != nil {
599 return nil, err
600 }
601 user.PublicKey = pk
602 return user, nil
603 }
604
605 if errors.Is(err, &db.ErrMultiplePublicKeys{}) {
606 me.Logger.Info("detected multiple users with same public key", "user", username)
607 user, err := me.FindUserForNameAndKey(username, key)
608 if err != nil {
609 me.Logger.Info("could not find user by username and public key", "user", username, "key", key)
610 // this is a little hacky but if we cannot find a user by name and public key
611 // then we return the multiple keys detected error so the user knows to specify their
612 // when logging in
613 return nil, &db.ErrMultiplePublicKeys{}
614 }
615 return user, nil
616 }
617
618 return nil, err
619}
620
621func (me *PsqlDB) FindUser(userID string) (*db.User, error) {
622 user := &db.User{}
623 var un sql.NullString
624 r := me.Db.QueryRow(sqlSelectUser, userID)
625 err := r.Scan(&user.ID, &un, &user.CreatedAt)
626 if err != nil {
627 return nil, err
628 }
629 if un.Valid {
630 user.Name = un.String
631 }
632 return user, nil
633}
634
635func (me *PsqlDB) ValidateName(name string) (bool, error) {
636 lower := strings.ToLower(name)
637 if slices.Contains(db.DenyList, lower) {
638 return false, fmt.Errorf("%s is on deny list: %w", lower, db.ErrNameDenied)
639 }
640 v := db.NameValidator.MatchString(lower)
641 if !v {
642 return false, fmt.Errorf("%s is invalid: %w", lower, db.ErrNameInvalid)
643 }
644 user, _ := me.FindUserForName(lower)
645 if user == nil {
646 return true, nil
647 }
648 return false, fmt.Errorf("%s already taken: %w", lower, db.ErrNameTaken)
649}
650
651func (me *PsqlDB) FindUserForName(name string) (*db.User, error) {
652 user := &db.User{}
653 r := me.Db.QueryRow(sqlSelectUserForName, strings.ToLower(name))
654 err := r.Scan(&user.ID, &user.Name, &user.CreatedAt)
655 if err != nil {
656 return nil, err
657 }
658 return user, nil
659}
660
661func (me *PsqlDB) FindUserForNameAndKey(name string, key string) (*db.User, error) {
662 user := &db.User{}
663 pk := &db.PublicKey{}
664
665 r := me.Db.QueryRow(sqlSelectUserForNameAndKey, strings.ToLower(name), key)
666 err := r.Scan(&user.ID, &user.Name, &user.CreatedAt, &pk.ID, &pk.Key, &pk.CreatedAt)
667 if err != nil {
668 return nil, err
669 }
670
671 user.PublicKey = pk
672 return user, nil
673}
674
675func (me *PsqlDB) FindUserForToken(token string) (*db.User, error) {
676 user := &db.User{}
677
678 r := me.Db.QueryRow(sqlSelectUserForToken, token)
679 err := r.Scan(&user.ID, &user.Name, &user.CreatedAt)
680 if err != nil {
681 return nil, err
682 }
683
684 return user, nil
685}
686
687func (me *PsqlDB) SetUserName(userID string, name string) error {
688 lowerName := strings.ToLower(name)
689 valid, err := me.ValidateName(lowerName)
690 if !valid {
691 return err
692 }
693
694 _, err = me.Db.Exec(sqlUpdateUserName, lowerName, userID)
695 return err
696}
697
698func (me *PsqlDB) FindPostWithFilename(filename string, persona_id string, space string) (*db.Post, error) {
699 r := me.Db.QueryRow(sqlSelectPostWithFilename, filename, persona_id, space)
700 post, err := CreatePostWithTagsFromRow(r)
701 if err != nil {
702 return nil, err
703 }
704
705 return post, nil
706}
707
708func (me *PsqlDB) FindPostWithSlug(slug string, user_id string, space string) (*db.Post, error) {
709 r := me.Db.QueryRow(sqlSelectPostWithSlug, slug, user_id, space)
710 post, err := CreatePostWithTagsFromRow(r)
711 if err != nil {
712 // attempt to find post inside post_aliases
713 alias := me.Db.QueryRow(sqlSelectPostIdByAliasSlug, slug)
714 postID := ""
715 err := alias.Scan(&postID)
716 if err != nil {
717 return nil, err
718 }
719
720 return me.FindPost(postID)
721 }
722
723 return post, nil
724}
725
726func (me *PsqlDB) FindPost(postID string) (*db.Post, error) {
727 r := me.Db.QueryRow(sqlSelectPost, postID)
728 post, err := CreatePostFromRow(r)
729 if err != nil {
730 return nil, err
731 }
732
733 return post, nil
734}
735
736func (me *PsqlDB) postPager(rs *sql.Rows, pageNum int, space string, tag string) (*db.Paginate[*db.Post], error) {
737 var posts []*db.Post
738 for rs.Next() {
739 post := &db.Post{}
740 err := rs.Scan(
741 &post.ID,
742 &post.UserID,
743 &post.Filename,
744 &post.Slug,
745 &post.Title,
746 &post.Text,
747 &post.Description,
748 &post.PublishAt,
749 &post.Username,
750 &post.UpdatedAt,
751 &post.MimeType,
752 )
753 if err != nil {
754 return nil, err
755 }
756
757 posts = append(posts, post)
758 }
759 if rs.Err() != nil {
760 return nil, rs.Err()
761 }
762
763 var count int
764 var err error
765 if tag == "" {
766 err = me.Db.QueryRow(sqlSelectPostCount, space).Scan(&count)
767 } else {
768 err = me.Db.QueryRow(sqlSelectTagPostCount, space, tag).Scan(&count)
769 }
770 if err != nil {
771 return nil, err
772 }
773
774 pager := &db.Paginate[*db.Post]{
775 Data: posts,
776 Total: int(math.Ceil(float64(count) / float64(pageNum))),
777 }
778
779 return pager, nil
780}
781
782func (me *PsqlDB) FindAllPosts(page *db.Pager, space string) (*db.Paginate[*db.Post], error) {
783 rs, err := me.Db.Query(sqlSelectPostsByRank, page.Num, page.Num*page.Page, space)
784 if err != nil {
785 return nil, err
786 }
787 return me.postPager(rs, page.Num, space, "")
788}
789
790func (me *PsqlDB) FindAllUpdatedPosts(page *db.Pager, space string) (*db.Paginate[*db.Post], error) {
791 rs, err := me.Db.Query(sqlSelectAllUpdatedPosts, page.Num, page.Num*page.Page, space)
792 if err != nil {
793 return nil, err
794 }
795 return me.postPager(rs, page.Num, space, "")
796}
797
798func (me *PsqlDB) InsertPost(post *db.Post) (*db.Post, error) {
799 var id string
800 err := me.Db.QueryRow(
801 sqlInsertPost,
802 post.UserID,
803 post.Filename,
804 post.Slug,
805 post.Title,
806 post.Text,
807 post.Description,
808 post.PublishAt,
809 post.Hidden,
810 post.Space,
811 post.FileSize,
812 post.MimeType,
813 post.Shasum,
814 post.Data,
815 post.ExpiresAt,
816 post.UpdatedAt,
817 ).Scan(&id)
818 if err != nil {
819 return nil, err
820 }
821
822 return me.FindPost(id)
823}
824
825func (me *PsqlDB) UpdatePost(post *db.Post) (*db.Post, error) {
826 _, err := me.Db.Exec(
827 sqlUpdatePost,
828 post.Slug,
829 post.Title,
830 post.Text,
831 post.Description,
832 post.UpdatedAt,
833 post.PublishAt,
834 post.FileSize,
835 post.Shasum,
836 post.Data,
837 post.ID,
838 post.Hidden,
839 post.ExpiresAt,
840 )
841 if err != nil {
842 return nil, err
843 }
844
845 return me.FindPost(post.ID)
846}
847
848func (me *PsqlDB) RemovePosts(postIDs []string) error {
849 param := "{" + strings.Join(postIDs, ",") + "}"
850 _, err := me.Db.Exec(sqlRemovePosts, param)
851 return err
852}
853
854func (me *PsqlDB) FindPostsForUser(page *db.Pager, userID string, space string) (*db.Paginate[*db.Post], error) {
855 var posts []*db.Post
856 rs, err := me.Db.Query(
857 sqlSelectPostsForUser,
858 userID,
859 space,
860 page.Num,
861 page.Num*page.Page,
862 )
863 if err != nil {
864 return nil, err
865 }
866 for rs.Next() {
867 post, err := CreatePostWithTagsFromRow(rs)
868 if err != nil {
869 return nil, err
870 }
871
872 posts = append(posts, post)
873 }
874
875 if rs.Err() != nil {
876 return nil, rs.Err()
877 }
878
879 var count int
880 err = me.Db.QueryRow(sqlSelectPostCount, space).Scan(&count)
881 if err != nil {
882 return nil, err
883 }
884
885 pager := &db.Paginate[*db.Post]{
886 Data: posts,
887 Total: int(math.Ceil(float64(count) / float64(page.Num))),
888 }
889 return pager, nil
890}
891
892func (me *PsqlDB) FindAllPostsForUser(userID string, space string) ([]*db.Post, error) {
893 var posts []*db.Post
894 rs, err := me.Db.Query(sqlSelectAllPostsForUser, userID, space)
895 if err != nil {
896 return posts, err
897 }
898 for rs.Next() {
899 post, err := CreatePostFromRow(rs)
900 if err != nil {
901 return nil, err
902 }
903
904 posts = append(posts, post)
905 }
906 if rs.Err() != nil {
907 return posts, rs.Err()
908 }
909 return posts, nil
910}
911
912func (me *PsqlDB) FindPosts() ([]*db.Post, error) {
913 var posts []*db.Post
914 rs, err := me.Db.Query(sqlSelectPosts)
915 if err != nil {
916 return posts, err
917 }
918 for rs.Next() {
919 post, err := CreatePostFromRow(rs)
920 if err != nil {
921 return nil, err
922 }
923
924 posts = append(posts, post)
925 }
926 if rs.Err() != nil {
927 return posts, rs.Err()
928 }
929 return posts, nil
930}
931
932func (me *PsqlDB) FindExpiredPosts(space string) ([]*db.Post, error) {
933 var posts []*db.Post
934 rs, err := me.Db.Query(sqlSelectExpiredPosts, space)
935 if err != nil {
936 return posts, err
937 }
938 for rs.Next() {
939 post, err := CreatePostFromRow(rs)
940 if err != nil {
941 return nil, err
942 }
943
944 posts = append(posts, post)
945 }
946 if rs.Err() != nil {
947 return posts, rs.Err()
948 }
949 return posts, nil
950}
951
952func (me *PsqlDB) FindUpdatedPostsForUser(userID string, space string) ([]*db.Post, error) {
953 var posts []*db.Post
954 rs, err := me.Db.Query(sqlSelectUpdatedPostsForUser, userID, space)
955 if err != nil {
956 return posts, err
957 }
958 for rs.Next() {
959 post, err := CreatePostFromRow(rs)
960 if err != nil {
961 return nil, err
962 }
963
964 posts = append(posts, post)
965 }
966 if rs.Err() != nil {
967 return posts, rs.Err()
968 }
969 return posts, nil
970}
971
972func (me *PsqlDB) Close() error {
973 me.Logger.Info("Closing db")
974 return me.Db.Close()
975}
976
977func newNullString(s string) sql.NullString {
978 if len(s) == 0 {
979 return sql.NullString{}
980 }
981 return sql.NullString{
982 String: s,
983 Valid: true,
984 }
985}
986
987func (me *PsqlDB) InsertVisit(view *db.AnalyticsVisits) error {
988 _, err := me.Db.Exec(
989 `INSERT INTO analytics_visits (user_id, project_id, post_id, host, path, ip_address, user_agent, referer, status) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9);`,
990 view.UserID,
991 newNullString(view.ProjectID),
992 newNullString(view.PostID),
993 view.Host,
994 view.Path,
995 view.IpAddress,
996 view.UserAgent,
997 view.Referer,
998 view.Status,
999 )
1000 return err
1001}
1002
1003func (me *PsqlDB) visitUniqueBlog(opts *db.SummaryOpts) ([]*db.VisitInterval, error) {
1004 uniqueVisitors := fmt.Sprintf(`SELECT
1005 date_trunc('%s', created_at) as interval_start,
1006 count(DISTINCT ip_address) as unique_visitors
1007 FROM analytics_visits
1008 WHERE %s=$1 AND created_at >= $2 AND status <> 404 %s
1009 GROUP BY interval_start`, opts.Interval, opts.By, opts.Where)
1010
1011 intervals := []*db.VisitInterval{}
1012 rs, err := me.Db.Query(uniqueVisitors, opts.FkID, opts.Origin)
1013 if err != nil {
1014 return nil, err
1015 }
1016
1017 for rs.Next() {
1018 interval := &db.VisitInterval{}
1019 err := rs.Scan(
1020 &interval.Interval,
1021 &interval.Visitors,
1022 )
1023 if err != nil {
1024 return nil, err
1025 }
1026
1027 intervals = append(intervals, interval)
1028 }
1029 if rs.Err() != nil {
1030 return nil, rs.Err()
1031 }
1032 return intervals, nil
1033}
1034
1035func (me *PsqlDB) visitUnique(opts *db.SummaryOpts) ([]*db.VisitInterval, error) {
1036 uniqueVisitors := fmt.Sprintf(`SELECT
1037 post_id,
1038 project_id,
1039 date_trunc('%s', created_at) as interval_start,
1040 count(DISTINCT ip_address) as unique_visitors
1041 FROM analytics_visits
1042 WHERE %s=$1 AND created_at >= $2 AND status <> 404 %s
1043 GROUP BY post_id, project_id, interval_start`, opts.Interval, opts.By, opts.Where)
1044
1045 intervals := []*db.VisitInterval{}
1046 rs, err := me.Db.Query(uniqueVisitors, opts.FkID, opts.Origin)
1047 if err != nil {
1048 return nil, err
1049 }
1050
1051 for rs.Next() {
1052 interval := &db.VisitInterval{}
1053 var postID sql.NullString
1054 var projectID sql.NullString
1055 err := rs.Scan(
1056 &postID,
1057 &projectID,
1058 &interval.Interval,
1059 &interval.Visitors,
1060 )
1061 if err != nil {
1062 return nil, err
1063 }
1064 interval.PostID = postID.String
1065 interval.ProjectID = projectID.String
1066
1067 intervals = append(intervals, interval)
1068 }
1069 if rs.Err() != nil {
1070 return nil, rs.Err()
1071 }
1072 return intervals, nil
1073}
1074
1075func (me *PsqlDB) visitReferer(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
1076 topUrls := fmt.Sprintf(`SELECT
1077 referer,
1078 count(DISTINCT ip_address) as referer_count
1079 FROM analytics_visits
1080 WHERE %s=$1 AND created_at >= $2 AND referer <> '' AND status <> 404 %s
1081 GROUP BY referer
1082 ORDER BY referer_count DESC
1083 LIMIT 10`, opts.By, opts.Where)
1084
1085 intervals := []*db.VisitUrl{}
1086 rs, err := me.Db.Query(topUrls, opts.FkID, opts.Origin)
1087 if err != nil {
1088 return nil, err
1089 }
1090
1091 for rs.Next() {
1092 interval := &db.VisitUrl{}
1093 err := rs.Scan(
1094 &interval.Url,
1095 &interval.Count,
1096 )
1097 if err != nil {
1098 return nil, err
1099 }
1100
1101 intervals = append(intervals, interval)
1102 }
1103 if rs.Err() != nil {
1104 return nil, rs.Err()
1105 }
1106 return intervals, nil
1107}
1108
1109func (me *PsqlDB) visitUrl(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
1110 topUrls := fmt.Sprintf(`SELECT
1111 path,
1112 count(DISTINCT ip_address) as path_count,
1113 post_id,
1114 project_id
1115 FROM analytics_visits
1116 WHERE %s=$1 AND created_at >= $2 AND path <> '' AND status <> 404 %s
1117 GROUP BY path, post_id, project_id
1118 ORDER BY path_count DESC
1119 LIMIT 10`, opts.By, opts.Where)
1120
1121 intervals := []*db.VisitUrl{}
1122 rs, err := me.Db.Query(topUrls, opts.FkID, opts.Origin)
1123 if err != nil {
1124 return nil, err
1125 }
1126
1127 for rs.Next() {
1128 interval := &db.VisitUrl{}
1129 var postID sql.NullString
1130 var projectID sql.NullString
1131 err := rs.Scan(
1132 &interval.Url,
1133 &interval.Count,
1134 &postID,
1135 &projectID,
1136 )
1137 if err != nil {
1138 return nil, err
1139 }
1140 interval.PostID = postID.String
1141 interval.ProjectID = projectID.String
1142
1143 intervals = append(intervals, interval)
1144 }
1145 if rs.Err() != nil {
1146 return nil, rs.Err()
1147 }
1148 return intervals, nil
1149}
1150
1151func (me *PsqlDB) VisitSummary(opts *db.SummaryOpts) (*db.SummaryVisits, error) {
1152 var visitors []*db.VisitInterval
1153 var err error
1154 if opts.Where == "" {
1155 visitors, err = me.visitUnique(opts)
1156 if err != nil {
1157 return nil, err
1158 }
1159 } else {
1160 visitors, err = me.visitUniqueBlog(opts)
1161 if err != nil {
1162 return nil, err
1163 }
1164 }
1165
1166 urls, err := me.visitUrl(opts)
1167 if err != nil {
1168 return nil, err
1169 }
1170 refs, err := me.visitReferer(opts)
1171 if err != nil {
1172 return nil, err
1173 }
1174 return &db.SummaryVisits{
1175 Intervals: visitors,
1176 TopUrls: urls,
1177 TopReferers: refs,
1178 }, nil
1179}
1180
1181func (me *PsqlDB) FindUsers() ([]*db.User, error) {
1182 var users []*db.User
1183 rs, err := me.Db.Query(sqlSelectUsers)
1184 if err != nil {
1185 return users, err
1186 }
1187 for rs.Next() {
1188 var name sql.NullString
1189 user := &db.User{}
1190 err := rs.Scan(
1191 &user.ID,
1192 &name,
1193 &user.CreatedAt,
1194 )
1195 if err != nil {
1196 return users, err
1197 }
1198 user.Name = name.String
1199
1200 users = append(users, user)
1201 }
1202 if rs.Err() != nil {
1203 return users, rs.Err()
1204 }
1205 return users, nil
1206}
1207
1208func (me *PsqlDB) removeTagsForPost(tx *sql.Tx, postID string) error {
1209 _, err := tx.Exec(sqlRemoveTagsByPost, postID)
1210 return err
1211}
1212
1213func (me *PsqlDB) insertTagsForPost(tx *sql.Tx, tags []string, postID string) ([]string, error) {
1214 ids := make([]string, 0)
1215 for _, tag := range tags {
1216 id := ""
1217 err := tx.QueryRow(sqlInsertTag, postID, tag).Scan(&id)
1218 if err != nil {
1219 return nil, err
1220 }
1221 ids = append(ids, id)
1222 }
1223
1224 return ids, nil
1225}
1226
1227func (me *PsqlDB) ReplaceTagsForPost(tags []string, postID string) error {
1228 ctx := context.Background()
1229 tx, err := me.Db.BeginTx(ctx, nil)
1230 if err != nil {
1231 return err
1232 }
1233 defer func() {
1234 err = tx.Rollback()
1235 }()
1236
1237 err = me.removeTagsForPost(tx, postID)
1238 if err != nil {
1239 return err
1240 }
1241
1242 _, err = me.insertTagsForPost(tx, tags, postID)
1243 if err != nil {
1244 return err
1245 }
1246
1247 err = tx.Commit()
1248 return err
1249}
1250
1251func (me *PsqlDB) removeAliasesForPost(tx *sql.Tx, postID string) error {
1252 _, err := tx.Exec(sqlRemoveAliasesByPost, postID)
1253 return err
1254}
1255
1256func (me *PsqlDB) insertAliasesForPost(tx *sql.Tx, aliases []string, postID string) ([]string, error) {
1257 // hardcoded
1258 denyList := []string{
1259 "rss",
1260 "rss.xml",
1261 "atom.xml",
1262 "feed.xml",
1263 "smol.css",
1264 "main.css",
1265 "syntax.css",
1266 "card.png",
1267 "favicon-16x16.png",
1268 "favicon-32x32.png",
1269 "apple-touch-icon.png",
1270 "favicon.ico",
1271 "robots.txt",
1272 "atom",
1273 "blog/index.xml",
1274 }
1275
1276 ids := make([]string, 0)
1277 for _, alias := range aliases {
1278 if slices.Contains(denyList, alias) {
1279 me.Logger.Info(
1280 "name is in the deny list for aliases because it conflicts with a static route, skipping",
1281 "alias", alias,
1282 )
1283 continue
1284 }
1285 id := ""
1286 err := tx.QueryRow(sqlInsertAliases, postID, alias).Scan(&id)
1287 if err != nil {
1288 return nil, err
1289 }
1290 ids = append(ids, id)
1291 }
1292
1293 return ids, nil
1294}
1295
1296func (me *PsqlDB) ReplaceAliasesForPost(aliases []string, postID string) error {
1297 ctx := context.Background()
1298 tx, err := me.Db.BeginTx(ctx, nil)
1299 if err != nil {
1300 return err
1301 }
1302 defer func() {
1303 err = tx.Rollback()
1304 }()
1305
1306 err = me.removeAliasesForPost(tx, postID)
1307 if err != nil {
1308 return err
1309 }
1310
1311 _, err = me.insertAliasesForPost(tx, aliases, postID)
1312 if err != nil {
1313 return err
1314 }
1315
1316 err = tx.Commit()
1317 return err
1318}
1319
1320func (me *PsqlDB) FindUserPostsByTag(page *db.Pager, tag, userID, space string) (*db.Paginate[*db.Post], error) {
1321 var posts []*db.Post
1322 rs, err := me.Db.Query(
1323 sqlSelectUserPostsByTag,
1324 userID,
1325 tag,
1326 space,
1327 page.Num,
1328 page.Num*page.Page,
1329 )
1330 if err != nil {
1331 return nil, err
1332 }
1333 for rs.Next() {
1334 post, err := CreatePostFromRow(rs)
1335 if err != nil {
1336 return nil, err
1337 }
1338
1339 posts = append(posts, post)
1340 }
1341
1342 if rs.Err() != nil {
1343 return nil, rs.Err()
1344 }
1345
1346 var count int
1347 err = me.Db.QueryRow(sqlSelectPostCount, space).Scan(&count)
1348 if err != nil {
1349 return nil, err
1350 }
1351
1352 pager := &db.Paginate[*db.Post]{
1353 Data: posts,
1354 Total: int(math.Ceil(float64(count) / float64(page.Num))),
1355 }
1356 return pager, nil
1357}
1358
1359func (me *PsqlDB) FindPostsByTag(pager *db.Pager, tag, space string) (*db.Paginate[*db.Post], error) {
1360 rs, err := me.Db.Query(
1361 sqlSelectPostsByTag,
1362 pager.Num,
1363 pager.Num*pager.Page,
1364 tag,
1365 space,
1366 )
1367 if err != nil {
1368 return nil, err
1369 }
1370
1371 return me.postPager(rs, pager.Num, space, tag)
1372}
1373
1374func (me *PsqlDB) FindPopularTags(space string) ([]string, error) {
1375 tags := make([]string, 0)
1376 rs, err := me.Db.Query(sqlSelectPopularTags, space)
1377 if err != nil {
1378 return tags, err
1379 }
1380 for rs.Next() {
1381 name := ""
1382 tally := 0
1383 err := rs.Scan(&name, &tally)
1384 if err != nil {
1385 return tags, err
1386 }
1387
1388 tags = append(tags, name)
1389 }
1390 if rs.Err() != nil {
1391 return tags, rs.Err()
1392 }
1393 return tags, nil
1394}
1395
1396func (me *PsqlDB) FindTagsForPost(postID string) ([]string, error) {
1397 tags := make([]string, 0)
1398 rs, err := me.Db.Query(sqlSelectTagsForPost, postID)
1399 if err != nil {
1400 return tags, err
1401 }
1402
1403 for rs.Next() {
1404 name := ""
1405 err := rs.Scan(&name)
1406 if err != nil {
1407 return tags, err
1408 }
1409
1410 tags = append(tags, name)
1411 }
1412
1413 if rs.Err() != nil {
1414 return tags, rs.Err()
1415 }
1416
1417 return tags, nil
1418}
1419
1420func (me *PsqlDB) FindFeatureForUser(userID string, feature string) (*db.FeatureFlag, error) {
1421 ff := &db.FeatureFlag{}
1422 // payment history is allowed to be null
1423 // https://devtidbits.com/2020/08/03/go-sql-error-converting-null-to-string-is-unsupported/
1424 var paymentHistoryID sql.NullString
1425 err := me.Db.QueryRow(sqlSelectFeatureForUser, userID, feature).Scan(
1426 &ff.ID,
1427 &ff.UserID,
1428 &paymentHistoryID,
1429 &ff.Name,
1430 &ff.Data,
1431 &ff.CreatedAt,
1432 &ff.ExpiresAt,
1433 )
1434 if err != nil {
1435 return nil, err
1436 }
1437
1438 ff.PaymentHistoryID = paymentHistoryID.String
1439
1440 return ff, nil
1441}
1442
1443func (me *PsqlDB) FindFeaturesForUser(userID string) ([]*db.FeatureFlag, error) {
1444 var features []*db.FeatureFlag
1445 // https://stackoverflow.com/a/16920077
1446 query := `SELECT DISTINCT ON (name)
1447 id, user_id, payment_history_id, name, data, created_at, expires_at
1448 FROM feature_flags
1449 WHERE user_id=$1
1450 ORDER BY name, expires_at DESC;`
1451 rs, err := me.Db.Query(query, userID)
1452 if err != nil {
1453 return features, err
1454 }
1455 for rs.Next() {
1456 var paymentHistoryID sql.NullString
1457 ff := &db.FeatureFlag{}
1458 err := rs.Scan(
1459 &ff.ID,
1460 &ff.UserID,
1461 &paymentHistoryID,
1462 &ff.Name,
1463 &ff.Data,
1464 &ff.CreatedAt,
1465 &ff.ExpiresAt,
1466 )
1467 if err != nil {
1468 return features, err
1469 }
1470 ff.PaymentHistoryID = paymentHistoryID.String
1471
1472 features = append(features, ff)
1473 }
1474 if rs.Err() != nil {
1475 return features, rs.Err()
1476 }
1477 return features, nil
1478}
1479
1480func (me *PsqlDB) HasFeatureForUser(userID string, feature string) bool {
1481 ff, err := me.FindFeatureForUser(userID, feature)
1482 if err != nil {
1483 return false
1484 }
1485 return ff.IsValid()
1486}
1487
1488func (me *PsqlDB) FindTotalSizeForUser(userID string) (int, error) {
1489 var fileSize int
1490 err := me.Db.QueryRow(sqlSelectSizeForUser, userID).Scan(&fileSize)
1491 if err != nil {
1492 return 0, err
1493 }
1494 return fileSize, nil
1495}
1496
1497func (me *PsqlDB) InsertFeedItems(postID string, items []*db.FeedItem) error {
1498 ctx := context.Background()
1499 tx, err := me.Db.BeginTx(ctx, nil)
1500 if err != nil {
1501 return err
1502 }
1503 defer func() {
1504 err = tx.Rollback()
1505 }()
1506
1507 for _, item := range items {
1508 _, err := tx.Exec(
1509 sqlInsertFeedItems,
1510 item.PostID,
1511 item.GUID,
1512 item.Data,
1513 )
1514 if err != nil {
1515 return err
1516 }
1517 }
1518
1519 err = tx.Commit()
1520 return err
1521}
1522
1523func (me *PsqlDB) FindFeedItemsByPostID(postID string) ([]*db.FeedItem, error) {
1524 // sqlSelectFeedItemsByPost
1525 items := make([]*db.FeedItem, 0)
1526 rs, err := me.Db.Query(sqlSelectFeedItemsByPost, postID)
1527 if err != nil {
1528 return items, err
1529 }
1530
1531 for rs.Next() {
1532 item := &db.FeedItem{}
1533 err := rs.Scan(
1534 &item.ID,
1535 &item.PostID,
1536 &item.GUID,
1537 &item.Data,
1538 &item.CreatedAt,
1539 )
1540 if err != nil {
1541 return items, err
1542 }
1543
1544 items = append(items, item)
1545 }
1546
1547 if rs.Err() != nil {
1548 return items, rs.Err()
1549 }
1550
1551 return items, nil
1552}
1553
1554func (me *PsqlDB) InsertProject(userID, name, projectDir string) (string, error) {
1555 if !shared.IsValidSubdomain(name) {
1556 return "", fmt.Errorf("'%s' is not a valid project name, must match /^[a-z0-9-]+$/", name)
1557 }
1558
1559 var id string
1560 err := me.Db.QueryRow(sqlInsertProject, userID, name, projectDir).Scan(&id)
1561 if err != nil {
1562 return "", err
1563 }
1564 return id, nil
1565}
1566
1567func (me *PsqlDB) UpdateProject(userID, name string) error {
1568 _, err := me.Db.Exec(sqlUpdateProject, userID, name, time.Now())
1569 return err
1570}
1571
1572func (me *PsqlDB) UpdateProjectAcl(userID, name string, acl db.ProjectAcl) error {
1573 _, err := me.Db.Exec(sqlUpdateProjectAcl, userID, name, acl, time.Now())
1574 return err
1575}
1576
1577func (me *PsqlDB) LinkToProject(userID, projectID, projectDir string, commit bool) error {
1578 linkToProject, err := me.FindProjectByName(userID, projectDir)
1579 if err != nil {
1580 return err
1581 }
1582 isAlreadyLinked := linkToProject.Name != linkToProject.ProjectDir
1583 sameProject := linkToProject.ID == projectID
1584
1585 /*
1586 A project linked to another project which is also linked to a
1587 project is forbidden. CI/CD Example:
1588 - ProjectProd links to ProjectStaging
1589 - ProjectStaging links to ProjectMain
1590 - We merge `main` and trigger a deploy which uploads to ProjectMain
1591 - All three get updated immediately
1592 This scenario was not the intent of our CI/CD. What we actually
1593 wanted was to create a snapshot of ProjectMain and have ProjectStaging
1594 link to the snapshot, but that's not the intended design of pgs.
1595
1596 So we want to close that gap here.
1597
1598 We ensure that `project.Name` and `project.ProjectDir` are identical
1599 when there is no aliasing.
1600 */
1601 if !sameProject && isAlreadyLinked {
1602 return fmt.Errorf(
1603 "cannot link (%s) to (%s) because it is also a link to (%s)",
1604 projectID,
1605 projectDir,
1606 linkToProject.ProjectDir,
1607 )
1608 }
1609
1610 if commit {
1611 _, err = me.Db.Exec(
1612 sqlLinkToProject,
1613 projectDir,
1614 time.Now(),
1615 projectID,
1616 )
1617 }
1618 return err
1619}
1620
1621func (me *PsqlDB) RemoveProject(projectID string) error {
1622 _, err := me.Db.Exec(sqlRemoveProject, projectID)
1623 return err
1624}
1625
1626func (me *PsqlDB) FindProjectByName(userID, name string) (*db.Project, error) {
1627 project := &db.Project{}
1628 r := me.Db.QueryRow(sqlFindProjectByName, userID, name)
1629 err := r.Scan(
1630 &project.ID,
1631 &project.UserID,
1632 &project.Name,
1633 &project.ProjectDir,
1634 &project.Acl,
1635 &project.Blocked,
1636 &project.CreatedAt,
1637 &project.UpdatedAt,
1638 )
1639 if err != nil {
1640 return nil, err
1641 }
1642
1643 return project, nil
1644}
1645
1646func (me *PsqlDB) FindProjectLinks(userID, name string) ([]*db.Project, error) {
1647 var projects []*db.Project
1648 rs, err := me.Db.Query(sqlFindProjectLinks, userID, name)
1649 if err != nil {
1650 return nil, err
1651 }
1652 for rs.Next() {
1653 project := &db.Project{}
1654 err := rs.Scan(
1655 &project.ID,
1656 &project.UserID,
1657 &project.Name,
1658 &project.ProjectDir,
1659 &project.Acl,
1660 &project.Blocked,
1661 &project.CreatedAt,
1662 &project.UpdatedAt,
1663 )
1664 if err != nil {
1665 return nil, err
1666 }
1667
1668 projects = append(projects, project)
1669 }
1670
1671 if rs.Err() != nil {
1672 return nil, rs.Err()
1673 }
1674
1675 return projects, nil
1676}
1677
1678func (me *PsqlDB) FindProjectsByPrefix(userID, prefix string) ([]*db.Project, error) {
1679 var projects []*db.Project
1680 rs, err := me.Db.Query(sqlFindProjectsByPrefix, userID, prefix+"%")
1681 if err != nil {
1682 return nil, err
1683 }
1684 for rs.Next() {
1685 project := &db.Project{}
1686 err := rs.Scan(
1687 &project.ID,
1688 &project.UserID,
1689 &project.Name,
1690 &project.ProjectDir,
1691 &project.Acl,
1692 &project.Blocked,
1693 &project.CreatedAt,
1694 &project.UpdatedAt,
1695 )
1696 if err != nil {
1697 return nil, err
1698 }
1699
1700 projects = append(projects, project)
1701 }
1702
1703 if rs.Err() != nil {
1704 return nil, rs.Err()
1705 }
1706
1707 return projects, nil
1708}
1709
1710func (me *PsqlDB) FindProjectsByUser(userID string) ([]*db.Project, error) {
1711 var projects []*db.Project
1712 rs, err := me.Db.Query(sqlFindProjectsByUser, userID)
1713 if err != nil {
1714 return nil, err
1715 }
1716 for rs.Next() {
1717 project := &db.Project{}
1718 err := rs.Scan(
1719 &project.ID,
1720 &project.UserID,
1721 &project.Name,
1722 &project.ProjectDir,
1723 &project.Acl,
1724 &project.Blocked,
1725 &project.CreatedAt,
1726 &project.UpdatedAt,
1727 )
1728 if err != nil {
1729 return nil, err
1730 }
1731
1732 projects = append(projects, project)
1733 }
1734
1735 if rs.Err() != nil {
1736 return nil, rs.Err()
1737 }
1738
1739 return projects, nil
1740}
1741
1742func (me *PsqlDB) FindAllProjects(page *db.Pager, by string) (*db.Paginate[*db.Project], error) {
1743 var projects []*db.Project
1744 sqlFindAllProjects := fmt.Sprintf(`
1745 SELECT projects.id, user_id, app_users.name as username, projects.name, project_dir, projects.acl, projects.blocked, projects.created_at, projects.updated_at
1746 FROM projects
1747 LEFT JOIN app_users ON app_users.id = projects.user_id
1748 ORDER BY %s DESC
1749 LIMIT $1 OFFSET $2`, by)
1750 rs, err := me.Db.Query(sqlFindAllProjects, page.Num, page.Num*page.Page)
1751 if err != nil {
1752 return nil, err
1753 }
1754 for rs.Next() {
1755 project := &db.Project{}
1756 err := rs.Scan(
1757 &project.ID,
1758 &project.UserID,
1759 &project.Username,
1760 &project.Name,
1761 &project.ProjectDir,
1762 &project.Acl,
1763 &project.Blocked,
1764 &project.CreatedAt,
1765 &project.UpdatedAt,
1766 )
1767 if err != nil {
1768 return nil, err
1769 }
1770
1771 projects = append(projects, project)
1772 }
1773
1774 if rs.Err() != nil {
1775 return nil, rs.Err()
1776 }
1777
1778 var count int
1779 err = me.Db.QueryRow(sqlSelectProjectCount).Scan(&count)
1780 if err != nil {
1781 return nil, err
1782 }
1783
1784 pager := &db.Paginate[*db.Project]{
1785 Data: projects,
1786 Total: int(math.Ceil(float64(count) / float64(page.Num))),
1787 }
1788
1789 return pager, nil
1790}
1791
1792func (me *PsqlDB) InsertToken(userID, name string) (string, error) {
1793 var token string
1794 err := me.Db.QueryRow(sqlInsertToken, userID, name).Scan(&token)
1795 if err != nil {
1796 return "", err
1797 }
1798 return token, nil
1799}
1800
1801func (me *PsqlDB) UpsertToken(userID, name string) (string, error) {
1802 token, _ := me.FindTokenByName(userID, name)
1803 if token != "" {
1804 return token, nil
1805 }
1806
1807 token, err := me.InsertToken(userID, name)
1808 return token, err
1809}
1810
1811func (me *PsqlDB) FindTokenByName(userID, name string) (string, error) {
1812 var token string
1813 err := me.Db.QueryRow(sqlSelectTokenByNameForUser, userID, name).Scan(&token)
1814 if err != nil {
1815 return "", err
1816 }
1817 return token, nil
1818}
1819
1820func (me *PsqlDB) RemoveToken(tokenID string) error {
1821 _, err := me.Db.Exec(sqlRemoveToken, tokenID)
1822 return err
1823}
1824
1825func (me *PsqlDB) FindTokensForUser(userID string) ([]*db.Token, error) {
1826 var keys []*db.Token
1827 rs, err := me.Db.Query(sqlSelectTokensForUser, userID)
1828 if err != nil {
1829 return keys, err
1830 }
1831 for rs.Next() {
1832 pk := &db.Token{}
1833 err := rs.Scan(&pk.ID, &pk.UserID, &pk.Name, &pk.CreatedAt, &pk.ExpiresAt)
1834 if err != nil {
1835 return keys, err
1836 }
1837
1838 keys = append(keys, pk)
1839 }
1840 if rs.Err() != nil {
1841 return keys, rs.Err()
1842 }
1843 return keys, nil
1844}
1845
1846func (me *PsqlDB) InsertFeature(userID, name string, expiresAt time.Time) (*db.FeatureFlag, error) {
1847 var featureID string
1848 err := me.Db.QueryRow(
1849 `INSERT INTO feature_flags (user_id, name, expires_at) VALUES ($1, $2, $3) RETURNING id;`,
1850 userID,
1851 name,
1852 expiresAt,
1853 ).Scan(&featureID)
1854 if err != nil {
1855 return nil, err
1856 }
1857
1858 feature, err := me.FindFeatureForUser(userID, name)
1859 if err != nil {
1860 return nil, err
1861 }
1862
1863 return feature, nil
1864}
1865
1866func (me *PsqlDB) RemoveFeature(userID string, name string) error {
1867 _, err := me.Db.Exec(`DELETE FROM feature_flags WHERE user_id = $1 AND name = $2`, userID, name)
1868 return err
1869}
1870
1871func (me *PsqlDB) createFeatureExpiresAt(userID, name string) time.Time {
1872 ff, _ := me.FindFeatureForUser(userID, name)
1873 if ff == nil {
1874 t := time.Now()
1875 return t.AddDate(1, 0, 0)
1876 }
1877 return ff.ExpiresAt.AddDate(1, 0, 0)
1878}
1879
1880func (me *PsqlDB) AddPicoPlusUser(username, paymentType, txId string) error {
1881 user, err := me.FindUserForName(username)
1882 if err != nil {
1883 return err
1884 }
1885
1886 ctx := context.Background()
1887 tx, err := me.Db.BeginTx(ctx, nil)
1888 if err != nil {
1889 return err
1890 }
1891 defer func() {
1892 err = tx.Rollback()
1893 }()
1894
1895 if paymentType != "" {
1896 data := db.PaymentHistoryData{
1897 Notes: "",
1898 TxID: txId,
1899 }
1900 _, err := tx.Exec(
1901 `INSERT INTO payment_history (user_id, payment_type, amount, data) VALUES ($1, $2, 24 * 1000000, $3);`,
1902 user.ID,
1903 paymentType,
1904 data,
1905 )
1906 if err != nil {
1907 return err
1908 }
1909 }
1910
1911 plus := me.createFeatureExpiresAt(user.ID, "plus")
1912 plusQuery := `INSERT INTO feature_flags (user_id, name, data, expires_at)
1913 VALUES ($1, 'plus', '{"storage_max":20000000000, "file_max":50000000}'::jsonb, $2);`
1914 _, err = tx.Exec(plusQuery, user.ID, plus)
1915 if err != nil {
1916 return err
1917 }
1918
1919 return tx.Commit()
1920}