Antonio Mika
·
17 Nov 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/utils"
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(visit *db.AnalyticsVisits) error {
988 _, err := me.Db.Exec(
989 `INSERT INTO analytics_visits (user_id, project_id, post_id, namespace, host, path, ip_address, user_agent, referer, status) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10);`,
990 visit.UserID,
991 newNullString(visit.ProjectID),
992 newNullString(visit.PostID),
993 newNullString(visit.Namespace),
994 visit.Host,
995 visit.Path,
996 visit.IpAddress,
997 visit.UserAgent,
998 visit.Referer,
999 visit.Status,
1000 )
1001 return err
1002}
1003
1004func (me *PsqlDB) visitUniqueBlog(opts *db.SummaryOpts) ([]*db.VisitInterval, error) {
1005 uniqueVisitors := fmt.Sprintf(`SELECT
1006 date_trunc('%s', created_at) as interval_start,
1007 count(DISTINCT ip_address) as unique_visitors
1008 FROM analytics_visits
1009 WHERE %s=$1 AND created_at >= $2 AND status <> 404 %s
1010 GROUP BY interval_start`, opts.Interval, opts.By, opts.Where)
1011
1012 intervals := []*db.VisitInterval{}
1013 rs, err := me.Db.Query(uniqueVisitors, opts.FkID, opts.Origin)
1014 if err != nil {
1015 return nil, err
1016 }
1017
1018 for rs.Next() {
1019 interval := &db.VisitInterval{}
1020 err := rs.Scan(
1021 &interval.Interval,
1022 &interval.Visitors,
1023 )
1024 if err != nil {
1025 return nil, err
1026 }
1027
1028 intervals = append(intervals, interval)
1029 }
1030 if rs.Err() != nil {
1031 return nil, rs.Err()
1032 }
1033 return intervals, nil
1034}
1035
1036func (me *PsqlDB) visitUnique(opts *db.SummaryOpts) ([]*db.VisitInterval, error) {
1037 uniqueVisitors := fmt.Sprintf(`SELECT
1038 post_id,
1039 project_id,
1040 date_trunc('%s', created_at) as interval_start,
1041 count(DISTINCT ip_address) as unique_visitors
1042 FROM analytics_visits
1043 WHERE %s=$1 AND created_at >= $2 AND status <> 404 %s
1044 GROUP BY post_id, project_id, interval_start`, opts.Interval, opts.By, opts.Where)
1045
1046 intervals := []*db.VisitInterval{}
1047 rs, err := me.Db.Query(uniqueVisitors, opts.FkID, opts.Origin)
1048 if err != nil {
1049 return nil, err
1050 }
1051
1052 for rs.Next() {
1053 interval := &db.VisitInterval{}
1054 var postID sql.NullString
1055 var projectID sql.NullString
1056 err := rs.Scan(
1057 &postID,
1058 &projectID,
1059 &interval.Interval,
1060 &interval.Visitors,
1061 )
1062 if err != nil {
1063 return nil, err
1064 }
1065 interval.PostID = postID.String
1066 interval.ProjectID = projectID.String
1067
1068 intervals = append(intervals, interval)
1069 }
1070 if rs.Err() != nil {
1071 return nil, rs.Err()
1072 }
1073 return intervals, nil
1074}
1075
1076func (me *PsqlDB) visitReferer(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
1077 topUrls := fmt.Sprintf(`SELECT
1078 referer,
1079 count(DISTINCT ip_address) as referer_count
1080 FROM analytics_visits
1081 WHERE %s=$1 AND created_at >= $2 AND referer <> '' AND status <> 404 %s
1082 GROUP BY referer
1083 ORDER BY referer_count DESC
1084 LIMIT 10`, opts.By, opts.Where)
1085
1086 intervals := []*db.VisitUrl{}
1087 rs, err := me.Db.Query(topUrls, opts.FkID, opts.Origin)
1088 if err != nil {
1089 return nil, err
1090 }
1091
1092 for rs.Next() {
1093 interval := &db.VisitUrl{}
1094 err := rs.Scan(
1095 &interval.Url,
1096 &interval.Count,
1097 )
1098 if err != nil {
1099 return nil, err
1100 }
1101
1102 intervals = append(intervals, interval)
1103 }
1104 if rs.Err() != nil {
1105 return nil, rs.Err()
1106 }
1107 return intervals, nil
1108}
1109
1110func (me *PsqlDB) visitUrl(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
1111 topUrls := fmt.Sprintf(`SELECT
1112 path,
1113 count(DISTINCT ip_address) as path_count,
1114 post_id,
1115 project_id
1116 FROM analytics_visits
1117 WHERE %s=$1 AND created_at >= $2 AND path <> '' AND status <> 404 %s
1118 GROUP BY path, post_id, project_id
1119 ORDER BY path_count DESC
1120 LIMIT 10`, opts.By, opts.Where)
1121
1122 intervals := []*db.VisitUrl{}
1123 rs, err := me.Db.Query(topUrls, opts.FkID, opts.Origin)
1124 if err != nil {
1125 return nil, err
1126 }
1127
1128 for rs.Next() {
1129 interval := &db.VisitUrl{}
1130 var postID sql.NullString
1131 var projectID sql.NullString
1132 err := rs.Scan(
1133 &interval.Url,
1134 &interval.Count,
1135 &postID,
1136 &projectID,
1137 )
1138 if err != nil {
1139 return nil, err
1140 }
1141 interval.PostID = postID.String
1142 interval.ProjectID = projectID.String
1143
1144 intervals = append(intervals, interval)
1145 }
1146 if rs.Err() != nil {
1147 return nil, rs.Err()
1148 }
1149 return intervals, nil
1150}
1151
1152func (me *PsqlDB) VisitSummary(opts *db.SummaryOpts) (*db.SummaryVisits, error) {
1153 var visitors []*db.VisitInterval
1154 var err error
1155 if opts.Where == "" {
1156 visitors, err = me.visitUnique(opts)
1157 if err != nil {
1158 return nil, err
1159 }
1160 } else {
1161 visitors, err = me.visitUniqueBlog(opts)
1162 if err != nil {
1163 return nil, err
1164 }
1165 }
1166
1167 urls, err := me.visitUrl(opts)
1168 if err != nil {
1169 return nil, err
1170 }
1171 refs, err := me.visitReferer(opts)
1172 if err != nil {
1173 return nil, err
1174 }
1175 return &db.SummaryVisits{
1176 Intervals: visitors,
1177 TopUrls: urls,
1178 TopReferers: refs,
1179 }, nil
1180}
1181
1182func (me *PsqlDB) FindUsers() ([]*db.User, error) {
1183 var users []*db.User
1184 rs, err := me.Db.Query(sqlSelectUsers)
1185 if err != nil {
1186 return users, err
1187 }
1188 for rs.Next() {
1189 var name sql.NullString
1190 user := &db.User{}
1191 err := rs.Scan(
1192 &user.ID,
1193 &name,
1194 &user.CreatedAt,
1195 )
1196 if err != nil {
1197 return users, err
1198 }
1199 user.Name = name.String
1200
1201 users = append(users, user)
1202 }
1203 if rs.Err() != nil {
1204 return users, rs.Err()
1205 }
1206 return users, nil
1207}
1208
1209func (me *PsqlDB) removeTagsForPost(tx *sql.Tx, postID string) error {
1210 _, err := tx.Exec(sqlRemoveTagsByPost, postID)
1211 return err
1212}
1213
1214func (me *PsqlDB) insertTagsForPost(tx *sql.Tx, tags []string, postID string) ([]string, error) {
1215 ids := make([]string, 0)
1216 for _, tag := range tags {
1217 id := ""
1218 err := tx.QueryRow(sqlInsertTag, postID, tag).Scan(&id)
1219 if err != nil {
1220 return nil, err
1221 }
1222 ids = append(ids, id)
1223 }
1224
1225 return ids, nil
1226}
1227
1228func (me *PsqlDB) ReplaceTagsForPost(tags []string, postID string) error {
1229 ctx := context.Background()
1230 tx, err := me.Db.BeginTx(ctx, nil)
1231 if err != nil {
1232 return err
1233 }
1234 defer func() {
1235 err = tx.Rollback()
1236 }()
1237
1238 err = me.removeTagsForPost(tx, postID)
1239 if err != nil {
1240 return err
1241 }
1242
1243 _, err = me.insertTagsForPost(tx, tags, postID)
1244 if err != nil {
1245 return err
1246 }
1247
1248 err = tx.Commit()
1249 return err
1250}
1251
1252func (me *PsqlDB) removeAliasesForPost(tx *sql.Tx, postID string) error {
1253 _, err := tx.Exec(sqlRemoveAliasesByPost, postID)
1254 return err
1255}
1256
1257func (me *PsqlDB) insertAliasesForPost(tx *sql.Tx, aliases []string, postID string) ([]string, error) {
1258 // hardcoded
1259 denyList := []string{
1260 "rss",
1261 "rss.xml",
1262 "atom.xml",
1263 "feed.xml",
1264 "smol.css",
1265 "main.css",
1266 "syntax.css",
1267 "card.png",
1268 "favicon-16x16.png",
1269 "favicon-32x32.png",
1270 "apple-touch-icon.png",
1271 "favicon.ico",
1272 "robots.txt",
1273 "atom",
1274 "blog/index.xml",
1275 }
1276
1277 ids := make([]string, 0)
1278 for _, alias := range aliases {
1279 if slices.Contains(denyList, alias) {
1280 me.Logger.Info(
1281 "name is in the deny list for aliases because it conflicts with a static route, skipping",
1282 "alias", alias,
1283 )
1284 continue
1285 }
1286 id := ""
1287 err := tx.QueryRow(sqlInsertAliases, postID, alias).Scan(&id)
1288 if err != nil {
1289 return nil, err
1290 }
1291 ids = append(ids, id)
1292 }
1293
1294 return ids, nil
1295}
1296
1297func (me *PsqlDB) ReplaceAliasesForPost(aliases []string, postID string) error {
1298 ctx := context.Background()
1299 tx, err := me.Db.BeginTx(ctx, nil)
1300 if err != nil {
1301 return err
1302 }
1303 defer func() {
1304 err = tx.Rollback()
1305 }()
1306
1307 err = me.removeAliasesForPost(tx, postID)
1308 if err != nil {
1309 return err
1310 }
1311
1312 _, err = me.insertAliasesForPost(tx, aliases, postID)
1313 if err != nil {
1314 return err
1315 }
1316
1317 err = tx.Commit()
1318 return err
1319}
1320
1321func (me *PsqlDB) FindUserPostsByTag(page *db.Pager, tag, userID, space string) (*db.Paginate[*db.Post], error) {
1322 var posts []*db.Post
1323 rs, err := me.Db.Query(
1324 sqlSelectUserPostsByTag,
1325 userID,
1326 tag,
1327 space,
1328 page.Num,
1329 page.Num*page.Page,
1330 )
1331 if err != nil {
1332 return nil, err
1333 }
1334 for rs.Next() {
1335 post, err := CreatePostFromRow(rs)
1336 if err != nil {
1337 return nil, err
1338 }
1339
1340 posts = append(posts, post)
1341 }
1342
1343 if rs.Err() != nil {
1344 return nil, rs.Err()
1345 }
1346
1347 var count int
1348 err = me.Db.QueryRow(sqlSelectPostCount, space).Scan(&count)
1349 if err != nil {
1350 return nil, err
1351 }
1352
1353 pager := &db.Paginate[*db.Post]{
1354 Data: posts,
1355 Total: int(math.Ceil(float64(count) / float64(page.Num))),
1356 }
1357 return pager, nil
1358}
1359
1360func (me *PsqlDB) FindPostsByTag(pager *db.Pager, tag, space string) (*db.Paginate[*db.Post], error) {
1361 rs, err := me.Db.Query(
1362 sqlSelectPostsByTag,
1363 pager.Num,
1364 pager.Num*pager.Page,
1365 tag,
1366 space,
1367 )
1368 if err != nil {
1369 return nil, err
1370 }
1371
1372 return me.postPager(rs, pager.Num, space, tag)
1373}
1374
1375func (me *PsqlDB) FindPopularTags(space string) ([]string, error) {
1376 tags := make([]string, 0)
1377 rs, err := me.Db.Query(sqlSelectPopularTags, space)
1378 if err != nil {
1379 return tags, err
1380 }
1381 for rs.Next() {
1382 name := ""
1383 tally := 0
1384 err := rs.Scan(&name, &tally)
1385 if err != nil {
1386 return tags, err
1387 }
1388
1389 tags = append(tags, name)
1390 }
1391 if rs.Err() != nil {
1392 return tags, rs.Err()
1393 }
1394 return tags, nil
1395}
1396
1397func (me *PsqlDB) FindTagsForPost(postID string) ([]string, error) {
1398 tags := make([]string, 0)
1399 rs, err := me.Db.Query(sqlSelectTagsForPost, postID)
1400 if err != nil {
1401 return tags, err
1402 }
1403
1404 for rs.Next() {
1405 name := ""
1406 err := rs.Scan(&name)
1407 if err != nil {
1408 return tags, err
1409 }
1410
1411 tags = append(tags, name)
1412 }
1413
1414 if rs.Err() != nil {
1415 return tags, rs.Err()
1416 }
1417
1418 return tags, nil
1419}
1420
1421func (me *PsqlDB) FindFeatureForUser(userID string, feature string) (*db.FeatureFlag, error) {
1422 ff := &db.FeatureFlag{}
1423 // payment history is allowed to be null
1424 // https://devtidbits.com/2020/08/03/go-sql-error-converting-null-to-string-is-unsupported/
1425 var paymentHistoryID sql.NullString
1426 err := me.Db.QueryRow(sqlSelectFeatureForUser, userID, feature).Scan(
1427 &ff.ID,
1428 &ff.UserID,
1429 &paymentHistoryID,
1430 &ff.Name,
1431 &ff.Data,
1432 &ff.CreatedAt,
1433 &ff.ExpiresAt,
1434 )
1435 if err != nil {
1436 return nil, err
1437 }
1438
1439 ff.PaymentHistoryID = paymentHistoryID.String
1440
1441 return ff, nil
1442}
1443
1444func (me *PsqlDB) FindFeaturesForUser(userID string) ([]*db.FeatureFlag, error) {
1445 var features []*db.FeatureFlag
1446 // https://stackoverflow.com/a/16920077
1447 query := `SELECT DISTINCT ON (name)
1448 id, user_id, payment_history_id, name, data, created_at, expires_at
1449 FROM feature_flags
1450 WHERE user_id=$1
1451 ORDER BY name, expires_at DESC;`
1452 rs, err := me.Db.Query(query, userID)
1453 if err != nil {
1454 return features, err
1455 }
1456 for rs.Next() {
1457 var paymentHistoryID sql.NullString
1458 ff := &db.FeatureFlag{}
1459 err := rs.Scan(
1460 &ff.ID,
1461 &ff.UserID,
1462 &paymentHistoryID,
1463 &ff.Name,
1464 &ff.Data,
1465 &ff.CreatedAt,
1466 &ff.ExpiresAt,
1467 )
1468 if err != nil {
1469 return features, err
1470 }
1471 ff.PaymentHistoryID = paymentHistoryID.String
1472
1473 features = append(features, ff)
1474 }
1475 if rs.Err() != nil {
1476 return features, rs.Err()
1477 }
1478 return features, nil
1479}
1480
1481func (me *PsqlDB) HasFeatureForUser(userID string, feature string) bool {
1482 ff, err := me.FindFeatureForUser(userID, feature)
1483 if err != nil {
1484 return false
1485 }
1486 return ff.IsValid()
1487}
1488
1489func (me *PsqlDB) FindTotalSizeForUser(userID string) (int, error) {
1490 var fileSize int
1491 err := me.Db.QueryRow(sqlSelectSizeForUser, userID).Scan(&fileSize)
1492 if err != nil {
1493 return 0, err
1494 }
1495 return fileSize, nil
1496}
1497
1498func (me *PsqlDB) InsertFeedItems(postID string, items []*db.FeedItem) error {
1499 ctx := context.Background()
1500 tx, err := me.Db.BeginTx(ctx, nil)
1501 if err != nil {
1502 return err
1503 }
1504 defer func() {
1505 err = tx.Rollback()
1506 }()
1507
1508 for _, item := range items {
1509 _, err := tx.Exec(
1510 sqlInsertFeedItems,
1511 item.PostID,
1512 item.GUID,
1513 item.Data,
1514 )
1515 if err != nil {
1516 return err
1517 }
1518 }
1519
1520 err = tx.Commit()
1521 return err
1522}
1523
1524func (me *PsqlDB) FindFeedItemsByPostID(postID string) ([]*db.FeedItem, error) {
1525 // sqlSelectFeedItemsByPost
1526 items := make([]*db.FeedItem, 0)
1527 rs, err := me.Db.Query(sqlSelectFeedItemsByPost, postID)
1528 if err != nil {
1529 return items, err
1530 }
1531
1532 for rs.Next() {
1533 item := &db.FeedItem{}
1534 err := rs.Scan(
1535 &item.ID,
1536 &item.PostID,
1537 &item.GUID,
1538 &item.Data,
1539 &item.CreatedAt,
1540 )
1541 if err != nil {
1542 return items, err
1543 }
1544
1545 items = append(items, item)
1546 }
1547
1548 if rs.Err() != nil {
1549 return items, rs.Err()
1550 }
1551
1552 return items, nil
1553}
1554
1555func (me *PsqlDB) InsertProject(userID, name, projectDir string) (string, error) {
1556 if !utils.IsValidSubdomain(name) {
1557 return "", fmt.Errorf("'%s' is not a valid project name, must match /^[a-z0-9-]+$/", name)
1558 }
1559
1560 var id string
1561 err := me.Db.QueryRow(sqlInsertProject, userID, name, projectDir).Scan(&id)
1562 if err != nil {
1563 return "", err
1564 }
1565 return id, nil
1566}
1567
1568func (me *PsqlDB) UpdateProject(userID, name string) error {
1569 _, err := me.Db.Exec(sqlUpdateProject, userID, name, time.Now())
1570 return err
1571}
1572
1573func (me *PsqlDB) UpdateProjectAcl(userID, name string, acl db.ProjectAcl) error {
1574 _, err := me.Db.Exec(sqlUpdateProjectAcl, userID, name, acl, time.Now())
1575 return err
1576}
1577
1578func (me *PsqlDB) LinkToProject(userID, projectID, projectDir string, commit bool) error {
1579 linkToProject, err := me.FindProjectByName(userID, projectDir)
1580 if err != nil {
1581 return err
1582 }
1583 isAlreadyLinked := linkToProject.Name != linkToProject.ProjectDir
1584 sameProject := linkToProject.ID == projectID
1585
1586 /*
1587 A project linked to another project which is also linked to a
1588 project is forbidden. CI/CD Example:
1589 - ProjectProd links to ProjectStaging
1590 - ProjectStaging links to ProjectMain
1591 - We merge `main` and trigger a deploy which uploads to ProjectMain
1592 - All three get updated immediately
1593 This scenario was not the intent of our CI/CD. What we actually
1594 wanted was to create a snapshot of ProjectMain and have ProjectStaging
1595 link to the snapshot, but that's not the intended design of pgs.
1596
1597 So we want to close that gap here.
1598
1599 We ensure that `project.Name` and `project.ProjectDir` are identical
1600 when there is no aliasing.
1601 */
1602 if !sameProject && isAlreadyLinked {
1603 return fmt.Errorf(
1604 "cannot link (%s) to (%s) because it is also a link to (%s)",
1605 projectID,
1606 projectDir,
1607 linkToProject.ProjectDir,
1608 )
1609 }
1610
1611 if commit {
1612 _, err = me.Db.Exec(
1613 sqlLinkToProject,
1614 projectDir,
1615 time.Now(),
1616 projectID,
1617 )
1618 }
1619 return err
1620}
1621
1622func (me *PsqlDB) RemoveProject(projectID string) error {
1623 _, err := me.Db.Exec(sqlRemoveProject, projectID)
1624 return err
1625}
1626
1627func (me *PsqlDB) FindProjectByName(userID, name string) (*db.Project, error) {
1628 project := &db.Project{}
1629 r := me.Db.QueryRow(sqlFindProjectByName, userID, name)
1630 err := r.Scan(
1631 &project.ID,
1632 &project.UserID,
1633 &project.Name,
1634 &project.ProjectDir,
1635 &project.Acl,
1636 &project.Blocked,
1637 &project.CreatedAt,
1638 &project.UpdatedAt,
1639 )
1640 if err != nil {
1641 return nil, err
1642 }
1643
1644 return project, nil
1645}
1646
1647func (me *PsqlDB) FindProjectLinks(userID, name string) ([]*db.Project, error) {
1648 var projects []*db.Project
1649 rs, err := me.Db.Query(sqlFindProjectLinks, userID, name)
1650 if err != nil {
1651 return nil, err
1652 }
1653 for rs.Next() {
1654 project := &db.Project{}
1655 err := rs.Scan(
1656 &project.ID,
1657 &project.UserID,
1658 &project.Name,
1659 &project.ProjectDir,
1660 &project.Acl,
1661 &project.Blocked,
1662 &project.CreatedAt,
1663 &project.UpdatedAt,
1664 )
1665 if err != nil {
1666 return nil, err
1667 }
1668
1669 projects = append(projects, project)
1670 }
1671
1672 if rs.Err() != nil {
1673 return nil, rs.Err()
1674 }
1675
1676 return projects, nil
1677}
1678
1679func (me *PsqlDB) FindProjectsByPrefix(userID, prefix string) ([]*db.Project, error) {
1680 var projects []*db.Project
1681 rs, err := me.Db.Query(sqlFindProjectsByPrefix, userID, prefix+"%")
1682 if err != nil {
1683 return nil, err
1684 }
1685 for rs.Next() {
1686 project := &db.Project{}
1687 err := rs.Scan(
1688 &project.ID,
1689 &project.UserID,
1690 &project.Name,
1691 &project.ProjectDir,
1692 &project.Acl,
1693 &project.Blocked,
1694 &project.CreatedAt,
1695 &project.UpdatedAt,
1696 )
1697 if err != nil {
1698 return nil, err
1699 }
1700
1701 projects = append(projects, project)
1702 }
1703
1704 if rs.Err() != nil {
1705 return nil, rs.Err()
1706 }
1707
1708 return projects, nil
1709}
1710
1711func (me *PsqlDB) FindProjectsByUser(userID string) ([]*db.Project, error) {
1712 var projects []*db.Project
1713 rs, err := me.Db.Query(sqlFindProjectsByUser, userID)
1714 if err != nil {
1715 return nil, err
1716 }
1717 for rs.Next() {
1718 project := &db.Project{}
1719 err := rs.Scan(
1720 &project.ID,
1721 &project.UserID,
1722 &project.Name,
1723 &project.ProjectDir,
1724 &project.Acl,
1725 &project.Blocked,
1726 &project.CreatedAt,
1727 &project.UpdatedAt,
1728 )
1729 if err != nil {
1730 return nil, err
1731 }
1732
1733 projects = append(projects, project)
1734 }
1735
1736 if rs.Err() != nil {
1737 return nil, rs.Err()
1738 }
1739
1740 return projects, nil
1741}
1742
1743func (me *PsqlDB) FindAllProjects(page *db.Pager, by string) (*db.Paginate[*db.Project], error) {
1744 var projects []*db.Project
1745 sqlFindAllProjects := fmt.Sprintf(`
1746 SELECT projects.id, user_id, app_users.name as username, projects.name, project_dir, projects.acl, projects.blocked, projects.created_at, projects.updated_at
1747 FROM projects
1748 LEFT JOIN app_users ON app_users.id = projects.user_id
1749 ORDER BY %s DESC
1750 LIMIT $1 OFFSET $2`, by)
1751 rs, err := me.Db.Query(sqlFindAllProjects, page.Num, page.Num*page.Page)
1752 if err != nil {
1753 return nil, err
1754 }
1755 for rs.Next() {
1756 project := &db.Project{}
1757 err := rs.Scan(
1758 &project.ID,
1759 &project.UserID,
1760 &project.Username,
1761 &project.Name,
1762 &project.ProjectDir,
1763 &project.Acl,
1764 &project.Blocked,
1765 &project.CreatedAt,
1766 &project.UpdatedAt,
1767 )
1768 if err != nil {
1769 return nil, err
1770 }
1771
1772 projects = append(projects, project)
1773 }
1774
1775 if rs.Err() != nil {
1776 return nil, rs.Err()
1777 }
1778
1779 var count int
1780 err = me.Db.QueryRow(sqlSelectProjectCount).Scan(&count)
1781 if err != nil {
1782 return nil, err
1783 }
1784
1785 pager := &db.Paginate[*db.Project]{
1786 Data: projects,
1787 Total: int(math.Ceil(float64(count) / float64(page.Num))),
1788 }
1789
1790 return pager, nil
1791}
1792
1793func (me *PsqlDB) InsertToken(userID, name string) (string, error) {
1794 var token string
1795 err := me.Db.QueryRow(sqlInsertToken, userID, name).Scan(&token)
1796 if err != nil {
1797 return "", err
1798 }
1799 return token, nil
1800}
1801
1802func (me *PsqlDB) UpsertToken(userID, name string) (string, error) {
1803 token, _ := me.FindTokenByName(userID, name)
1804 if token != "" {
1805 return token, nil
1806 }
1807
1808 token, err := me.InsertToken(userID, name)
1809 return token, err
1810}
1811
1812func (me *PsqlDB) FindTokenByName(userID, name string) (string, error) {
1813 var token string
1814 err := me.Db.QueryRow(sqlSelectTokenByNameForUser, userID, name).Scan(&token)
1815 if err != nil {
1816 return "", err
1817 }
1818 return token, nil
1819}
1820
1821func (me *PsqlDB) RemoveToken(tokenID string) error {
1822 _, err := me.Db.Exec(sqlRemoveToken, tokenID)
1823 return err
1824}
1825
1826func (me *PsqlDB) FindTokensForUser(userID string) ([]*db.Token, error) {
1827 var keys []*db.Token
1828 rs, err := me.Db.Query(sqlSelectTokensForUser, userID)
1829 if err != nil {
1830 return keys, err
1831 }
1832 for rs.Next() {
1833 pk := &db.Token{}
1834 err := rs.Scan(&pk.ID, &pk.UserID, &pk.Name, &pk.CreatedAt, &pk.ExpiresAt)
1835 if err != nil {
1836 return keys, err
1837 }
1838
1839 keys = append(keys, pk)
1840 }
1841 if rs.Err() != nil {
1842 return keys, rs.Err()
1843 }
1844 return keys, nil
1845}
1846
1847func (me *PsqlDB) InsertFeature(userID, name string, expiresAt time.Time) (*db.FeatureFlag, error) {
1848 var featureID string
1849 err := me.Db.QueryRow(
1850 `INSERT INTO feature_flags (user_id, name, expires_at) VALUES ($1, $2, $3) RETURNING id;`,
1851 userID,
1852 name,
1853 expiresAt,
1854 ).Scan(&featureID)
1855 if err != nil {
1856 return nil, err
1857 }
1858
1859 feature, err := me.FindFeatureForUser(userID, name)
1860 if err != nil {
1861 return nil, err
1862 }
1863
1864 return feature, nil
1865}
1866
1867func (me *PsqlDB) RemoveFeature(userID string, name string) error {
1868 _, err := me.Db.Exec(`DELETE FROM feature_flags WHERE user_id = $1 AND name = $2`, userID, name)
1869 return err
1870}
1871
1872func (me *PsqlDB) createFeatureExpiresAt(userID, name string) time.Time {
1873 ff, _ := me.FindFeatureForUser(userID, name)
1874 if ff == nil {
1875 t := time.Now()
1876 return t.AddDate(1, 0, 0)
1877 }
1878 return ff.ExpiresAt.AddDate(1, 0, 0)
1879}
1880
1881func (me *PsqlDB) AddPicoPlusUser(username, paymentType, txId string) error {
1882 user, err := me.FindUserForName(username)
1883 if err != nil {
1884 return err
1885 }
1886
1887 ctx := context.Background()
1888 tx, err := me.Db.BeginTx(ctx, nil)
1889 if err != nil {
1890 return err
1891 }
1892 defer func() {
1893 err = tx.Rollback()
1894 }()
1895
1896 var paymentHistoryId sql.NullString
1897 if paymentType != "" {
1898 data := db.PaymentHistoryData{
1899 Notes: "",
1900 TxID: txId,
1901 }
1902
1903 err := tx.QueryRow(
1904 `INSERT INTO payment_history (user_id, payment_type, amount, data) VALUES ($1, $2, 24 * 1000000, $3) RETURNING id;`,
1905 user.ID,
1906 paymentType,
1907 data,
1908 ).Scan(&paymentHistoryId)
1909 if err != nil {
1910 return err
1911 }
1912 }
1913
1914 plus := me.createFeatureExpiresAt(user.ID, "plus")
1915 plusQuery := `INSERT INTO feature_flags (user_id, name, data, expires_at, payment_history_id)
1916 VALUES ($1, 'plus', '{"storage_max":10000000000, "file_max":50000000}'::jsonb, $2, $3);`
1917 _, err = tx.Exec(plusQuery, user.ID, plus, paymentHistoryId)
1918 if err != nil {
1919 return err
1920 }
1921
1922 return tx.Commit()
1923}