pico

refactor: remove unused db methods rss
created pr with 97.1 on 2025-12-18T04:19:09Z · by c8ef7d19
added 97.2 on 2025-12-18T04:36:52Z · by c8ef7d19
1: fdc255e = 1: fdc255e refactor: remove unused db methods
2: 3f855f6 = 2: 3f855f6 chore: add tests for postgres db impl
3: 7cd29a6 = 3: 7cd29a6 refactor: use sqlx interface
4: acd449e = 4: acd449e chore: add db tags
5: f6ed0d7 = 5: f6ed0d7 refactor: use sqlx
6: 29035c6 = 6: 29035c6 refactor: replace custom sql functions with sqlx
7: d4ea6d9 = 7: d4ea6d9 refactor: inline all sql queries
-: ------- > 8: 4bd37ed refactor: use `select * from` where possible
cmds
checkout latest patchset: 
ssh pr.pico.sh print 97 | git am -3
checkout any patchset in a patch request: 
ssh pr.pico.sh print 97.[rev] | git am -3
add changes to patch request: 
git format-patch main --stdout | ssh pr.pico.sh pr add 97

Patchset 97.2 on 2025-12-18T04:36:52Z · commit d4ea6d9

refactor: remove unused db methods
Eric Bower 2025-12-18T03:03:10Z
 chore: add tests for postgres db impl
Eric Bower 2025-12-18T03:17:57Z
 refactor: use sqlx interface
Eric Bower 2025-12-18T03:27:57Z
 chore: add db tags
Eric Bower 2025-12-18T03:31:25Z
 refactor: use sqlx
Eric Bower 2025-12-18T03:43:01Z
 refactor: replace custom sql functions with sqlx
Eric Bower 2025-12-18T03:57:00Z
 → refactor: inline all sql queries
Eric Bower 2025-12-18T04:02:50Z
 refactor: use `select * from` where possible
Eric Bower 2025-12-18T04:33:58Z
refactor: inline all sql queries
Eric Bower 2025-12-18T04:02:50Z
Semantic diff summary
0 added, 39 modified, 0 signature changed, 0 removed across 1 analyzed file
pkg/db/postgres/storage.go
+181 -232 pkg/db/postgres/storage.go #
......@@ -23,196 +23,6 @@ var SelectPost = `
2323 posts.id, user_id, app_users.name, filename, slug, title, text, description,
2424 posts.created_at, publish_at, posts.updated_at, hidden, file_size, mime_type, shasum, data, expires_at, views`
2525
26-var (
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- sqlSelectPostWithFilename = fmt.Sprintf(`
33- SELECT %s, STRING_AGG(coalesce(post_tags.name, ''), ',') tags
34- FROM posts
35- LEFT JOIN app_users ON app_users.id = posts.user_id
36- LEFT JOIN post_tags ON post_tags.post_id = posts.id
37- WHERE filename = $1 AND user_id = $2 AND cur_space = $3
38- GROUP BY %s`, SelectPost, SelectPost)
39-
40- sqlSelectPostWithSlug = fmt.Sprintf(`
41- SELECT %s, STRING_AGG(coalesce(post_tags.name, ''), ',') tags
42- FROM posts
43- LEFT JOIN app_users ON app_users.id = posts.user_id
44- LEFT JOIN post_tags ON post_tags.post_id = posts.id
45- WHERE slug = $1 AND user_id = $2 AND cur_space = $3
46- GROUP BY %s`, SelectPost, SelectPost)
47-
48- sqlSelectPost = fmt.Sprintf(`
49- SELECT %s
50- FROM posts
51- LEFT JOIN app_users ON app_users.id = posts.user_id
52- WHERE posts.id = $1`, SelectPost)
53-
54- sqlSelectExpiredPosts = fmt.Sprintf(`
55- SELECT %s
56- FROM posts
57- LEFT JOIN app_users ON app_users.id = posts.user_id
58- WHERE
59- cur_space = $1 AND
60- expires_at <= now();
61- `, SelectPost)
62-
63- sqlSelectPostsForUser = fmt.Sprintf(`
64- SELECT %s, STRING_AGG(coalesce(post_tags.name, ''), ',') tags
65- FROM posts
66- LEFT JOIN app_users ON app_users.id = posts.user_id
67- LEFT JOIN post_tags ON post_tags.post_id = posts.id
68- WHERE
69- hidden = FALSE AND
70- user_id = $1 AND
71- publish_at::date <= CURRENT_DATE AND
72- cur_space = $2
73- GROUP BY %s
74- ORDER BY publish_at DESC, slug DESC
75- LIMIT $3 OFFSET $4`, SelectPost, SelectPost)
76-
77- sqlSelectAllPostsForUser = fmt.Sprintf(`
78- SELECT %s
79- FROM posts
80- LEFT JOIN app_users ON app_users.id = posts.user_id
81- WHERE
82- user_id = $1 AND
83- cur_space = $2
84- ORDER BY publish_at DESC`, SelectPost)
85-
86- sqlSelectPostsByTag = `
87- SELECT
88- posts.id,
89- user_id,
90- filename,
91- slug,
92- title,
93- text,
94- description,
95- publish_at,
96- app_users.name as username,
97- posts.updated_at,
98- posts.mime_type
99- FROM posts
100- LEFT JOIN app_users ON app_users.id = posts.user_id
101- LEFT JOIN post_tags ON post_tags.post_id = posts.id
102- WHERE
103- post_tags.name = $3 AND
104- publish_at::date <= CURRENT_DATE AND
105- cur_space = $4
106- ORDER BY publish_at DESC
107- LIMIT $1 OFFSET $2`
108-
109- sqlSelectUserPostsByTag = fmt.Sprintf(`
110- SELECT %s
111- FROM posts
112- LEFT JOIN app_users ON app_users.id = posts.user_id
113- LEFT JOIN post_tags ON post_tags.post_id = posts.id
114- WHERE
115- hidden = FALSE AND
116- user_id = $1 AND
117- (post_tags.name = $2 OR hidden = true) AND
118- publish_at::date <= CURRENT_DATE AND
119- cur_space = $3
120- ORDER BY publish_at DESC
121- LIMIT $4 OFFSET $5`, SelectPost)
122-)
123-
124-const (
125- sqlSelectPublicKey = `SELECT id, user_id, name, public_key, created_at FROM public_keys WHERE public_key = $1`
126- sqlSelectPublicKeys = `SELECT id, user_id, name, public_key, created_at FROM public_keys WHERE user_id = $1 ORDER BY created_at ASC`
127- sqlSelectUser = `SELECT id, COALESCE(name, '') as name, created_at FROM app_users WHERE id = $1`
128- sqlSelectUserForName = `SELECT id, name, created_at FROM app_users WHERE name = $1`
129- 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`
130- sqlSelectUsers = `SELECT id, COALESCE(name, '') as name, created_at FROM app_users ORDER BY name ASC`
131-
132- sqlSelectUserForToken = `
133- SELECT app_users.id, app_users.name, app_users.created_at
134- FROM app_users
135- LEFT JOIN tokens ON tokens.user_id = app_users.id
136- WHERE tokens.token = $1 AND tokens.expires_at > NOW()`
137- sqlInsertToken = `INSERT INTO tokens (user_id, name) VALUES($1, $2) RETURNING token;`
138- sqlRemoveToken = `DELETE FROM tokens WHERE id = $1`
139- sqlSelectTokensForUser = `SELECT id, user_id, name, created_at, expires_at FROM tokens WHERE user_id = $1`
140- sqlSelectTokenByNameForUser = `SELECT token FROM tokens WHERE user_id = $1 AND name = $2`
141-
142- 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`
143-
144- sqlSelectPostIdByAliasSlug = `SELECT post_id FROM post_aliases WHERE slug = $1`
145- sqlSelectTagPostCount = `
146- SELECT count(posts.id)
147- FROM posts
148- LEFT JOIN post_tags ON post_tags.post_id = posts.id
149- WHERE hidden = FALSE AND cur_space=$1 and post_tags.name = $2`
150- sqlSelectPostCount = `SELECT count(id) FROM posts WHERE hidden = FALSE AND cur_space=$1`
151- // add some users to deny list since they are robogenerating a bunch of posts
152- // per day and are creating a lot of noise.
153- sqlSelectPostsByRank = `
154- SELECT *
155- FROM (
156- SELECT DISTINCT ON (posts.user_id)
157- posts.id,
158- posts.user_id,
159- posts.filename,
160- posts.slug,
161- posts.title,
162- posts.text,
163- posts.description,
164- posts.publish_at,
165- app_users.name AS username,
166- posts.updated_at,
167- posts.mime_type
168- FROM posts
169- LEFT JOIN app_users ON app_users.id = posts.user_id
170- WHERE
171- hidden = FALSE
172- AND publish_at::date <= CURRENT_DATE
173- AND cur_space = $3
174- ORDER BY posts.user_id, publish_at DESC
175- ) AS latest_posts
176- ORDER BY publish_at DESC
177- LIMIT $1 OFFSET $2`
178-
179- sqlSelectPopularTags = `
180- SELECT name, count(post_id) as "tally"
181- FROM post_tags
182- LEFT JOIN posts ON posts.id = post_id
183- WHERE posts.cur_space = $1
184- GROUP BY name
185- ORDER BY tally DESC
186- LIMIT 5`
187- sqlSelectFeedItemsByPost = `SELECT id, post_id, guid, data, created_at FROM feed_items WHERE post_id=$1`
188-
189- sqlInsertPost = `
190- INSERT INTO posts
191- (user_id, filename, slug, title, text, description, publish_at, hidden, cur_space,
192- file_size, mime_type, shasum, data, expires_at, updated_at)
193- VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)
194- RETURNING id`
195- sqlInsertUser = `INSERT INTO app_users (name) VALUES($1) returning id`
196- sqlInsertTag = `INSERT INTO post_tags (post_id, name) VALUES($1, $2) RETURNING id;`
197- sqlInsertAliases = `INSERT INTO post_aliases (post_id, slug) VALUES($1, $2) RETURNING id;`
198- sqlInsertFeedItems = `INSERT INTO feed_items (post_id, guid, data) VALUES ($1, $2, $3) RETURNING id;`
199-
200- sqlUpdatePost = `
201- UPDATE posts
202- SET slug = $1, title = $2, text = $3, description = $4, updated_at = $5, publish_at = $6,
203- file_size = $7, shasum = $8, data = $9, hidden = $11, expires_at = $12
204- WHERE id = $10`
205-
206- sqlRemoveAliasesByPost = `DELETE FROM post_aliases WHERE post_id = $1`
207- sqlRemoveTagsByPost = `DELETE FROM post_tags WHERE post_id = $1`
208- sqlRemovePosts = `DELETE FROM posts WHERE id = ANY($1::uuid[])`
209- sqlRemoveKeys = `DELETE FROM public_keys WHERE id = ANY($1::uuid[])`
210-
211- sqlInsertProject = `INSERT INTO projects (user_id, name, project_dir) VALUES ($1, $2, $3) RETURNING id;`
212- sqlUpdateProject = `UPDATE projects SET updated_at = $3 WHERE user_id = $1 AND name = $2;`
213- sqlFindProjectByName = `SELECT id, user_id, name, project_dir, acl, blocked, created_at, updated_at FROM projects WHERE user_id = $1 AND name = $2;`
214-)
215-
21626 type PsqlDB struct {
21727 Logger *slog.Logger
21828 Db *sqlx.DB
......@@ -293,7 +103,7 @@ func (me *PsqlDB) RegisterUser(username, pubkey, comment string) (*db.User, erro
293103 }()
294104
295105 var id string
296- err = tx.QueryRow(sqlInsertUser, lowerName).Scan(&id)
106+ err = tx.QueryRow(`INSERT INTO app_users (name) VALUES($1) returning id`, lowerName).Scan(&id)
297107 if err != nil {
298108 return nil, err
299109 }
......@@ -352,7 +162,7 @@ func (me *PsqlDB) UpdatePublicKey(pubkeyID, name string) (*db.PublicKey, error)
352162
353163 func (me *PsqlDB) findPublicKeyForKey(key string) (*db.PublicKey, error) {
354164 var keys []*db.PublicKey
355- rs, err := me.Db.Queryx(sqlSelectPublicKey, key)
165+ rs, err := me.Db.Queryx(`SELECT id, user_id, name, public_key, created_at FROM public_keys WHERE public_key = $1`, key)
356166 if err != nil {
357167 return nil, err
358168 }
......@@ -398,7 +208,7 @@ func (me *PsqlDB) findPublicKey(pubkeyID string) (*db.PublicKey, error) {
398208
399209 func (me *PsqlDB) FindKeysForUser(user *db.User) ([]*db.PublicKey, error) {
400210 var keys []*db.PublicKey
401- err := me.Db.Select(&keys, sqlSelectPublicKeys, user.ID)
211+ err := me.Db.Select(&keys, `SELECT id, user_id, name, public_key, created_at FROM public_keys WHERE user_id = $1 ORDER BY created_at ASC`, user.ID)
402212 if err != nil {
403213 return nil, err
404214 }
......@@ -407,7 +217,7 @@ func (me *PsqlDB) FindKeysForUser(user *db.User) ([]*db.PublicKey, error) {
407217
408218 func (me *PsqlDB) RemoveKeys(keyIDs []string) error {
409219 param := "{" + strings.Join(keyIDs, ",") + "}"
410- _, err := me.Db.Exec(sqlRemoveKeys, param)
220+ _, err := me.Db.Exec(`DELETE FROM public_keys WHERE id = ANY($1::uuid[])`, param)
411221 return err
412222 }
413223
......@@ -493,7 +303,7 @@ func (me *PsqlDB) FindUserByPubkey(key string) (*db.User, error) {
493303
494304 func (me *PsqlDB) FindUser(userID string) (*db.User, error) {
495305 user := &db.User{}
496- err := me.Db.Get(user, sqlSelectUser, userID)
306+ err := me.Db.Get(user, `SELECT id, COALESCE(name, '') as name, created_at FROM app_users WHERE id = $1`, userID)
497307 if err != nil {
498308 return nil, err
499309 }
......@@ -518,7 +328,7 @@ func (me *PsqlDB) validateName(name string) (bool, error) {
518328
519329 func (me *PsqlDB) FindUserByName(name string) (*db.User, error) {
520330 user := &db.User{}
521- err := me.Db.Get(user, sqlSelectUserForName, strings.ToLower(name))
331+ err := me.Db.Get(user, `SELECT id, name, created_at FROM app_users WHERE name = $1`, strings.ToLower(name))
522332 if err != nil {
523333 return nil, err
524334 }
......@@ -529,7 +339,7 @@ func (me *PsqlDB) findUserForNameAndKey(name string, key string) (*db.User, erro
529339 user := &db.User{}
530340 pk := &db.PublicKey{}
531341
532- r := me.Db.QueryRow(sqlSelectUserForNameAndKey, strings.ToLower(name), key)
342+ r := me.Db.QueryRow(`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`, strings.ToLower(name), key)
533343 err := r.Scan(&user.ID, &user.Name, &user.CreatedAt, &pk.ID, &pk.Key, &pk.CreatedAt)
534344 if err != nil {
535345 return nil, err
......@@ -541,7 +351,11 @@ func (me *PsqlDB) findUserForNameAndKey(name string, key string) (*db.User, erro
541351
542352 func (me *PsqlDB) FindUserForToken(token string) (*db.User, error) {
543353 user := &db.User{}
544- err := me.Db.Get(user, sqlSelectUserForToken, token)
354+ err := me.Db.Get(user, `
355+ SELECT app_users.id, app_users.name, app_users.created_at
356+ FROM app_users
357+ LEFT JOIN tokens ON tokens.user_id = app_users.id
358+ WHERE tokens.token = $1 AND tokens.expires_at > NOW()`, token)
545359 if err != nil {
546360 return nil, err
547361 }
......@@ -549,7 +363,14 @@ func (me *PsqlDB) FindUserForToken(token string) (*db.User, error) {
549363 }
550364
551365 func (me *PsqlDB) FindPostWithFilename(filename string, persona_id string, space string) (*db.Post, error) {
552- r := me.Db.QueryRow(sqlSelectPostWithFilename, filename, persona_id, space)
366+ query := fmt.Sprintf(`
367+ SELECT %s, STRING_AGG(coalesce(post_tags.name, ''), ',') tags
368+ FROM posts
369+ LEFT JOIN app_users ON app_users.id = posts.user_id
370+ LEFT JOIN post_tags ON post_tags.post_id = posts.id
371+ WHERE filename = $1 AND user_id = $2 AND cur_space = $3
372+ GROUP BY %s`, SelectPost, SelectPost)
373+ r := me.Db.QueryRow(query, filename, persona_id, space)
553374 post, err := CreatePostWithTagsFromRow(r)
554375 if err != nil {
555376 return nil, err
......@@ -559,11 +380,18 @@ func (me *PsqlDB) FindPostWithFilename(filename string, persona_id string, space
559380 }
560381
561382 func (me *PsqlDB) FindPostWithSlug(slug string, user_id string, space string) (*db.Post, error) {
562- r := me.Db.QueryRow(sqlSelectPostWithSlug, slug, user_id, space)
383+ query := fmt.Sprintf(`
384+ SELECT %s, STRING_AGG(coalesce(post_tags.name, ''), ',') tags
385+ FROM posts
386+ LEFT JOIN app_users ON app_users.id = posts.user_id
387+ LEFT JOIN post_tags ON post_tags.post_id = posts.id
388+ WHERE slug = $1 AND user_id = $2 AND cur_space = $3
389+ GROUP BY %s`, SelectPost, SelectPost)
390+ r := me.Db.QueryRow(query, slug, user_id, space)
563391 post, err := CreatePostWithTagsFromRow(r)
564392 if err != nil {
565393 // attempt to find post inside post_aliases
566- alias := me.Db.QueryRow(sqlSelectPostIdByAliasSlug, slug)
394+ alias := me.Db.QueryRow(`SELECT post_id FROM post_aliases WHERE slug = $1`, slug)
567395 postID := ""
568396 err := alias.Scan(&postID)
569397 if err != nil {
......@@ -578,7 +406,12 @@ func (me *PsqlDB) FindPostWithSlug(slug string, user_id string, space string) (*
578406
579407 func (me *PsqlDB) FindPost(postID string) (*db.Post, error) {
580408 post := &db.Post{}
581- err := me.Db.Get(post, sqlSelectPost, postID)
409+ query := fmt.Sprintf(`
410+ SELECT %s
411+ FROM posts
412+ LEFT JOIN app_users ON app_users.id = posts.user_id
413+ WHERE posts.id = $1`, SelectPost)
414+ err := me.Db.Get(post, query, postID)
582415 if err != nil {
583416 return nil, err
584417 }
......@@ -615,9 +448,13 @@ func (me *PsqlDB) postPager(rs *sqlx.Rows, pageNum int, space string, tag string
615448 var count int
616449 var err error
617450 if tag == "" {
618- err = me.Db.QueryRow(sqlSelectPostCount, space).Scan(&count)
451+ err = me.Db.QueryRow(`SELECT count(id) FROM posts WHERE hidden = FALSE AND cur_space=$1`, space).Scan(&count)
619452 } else {
620- err = me.Db.QueryRow(sqlSelectTagPostCount, space, tag).Scan(&count)
453+ err = me.Db.QueryRow(`
454+ SELECT count(posts.id)
455+ FROM posts
456+ LEFT JOIN post_tags ON post_tags.post_id = posts.id
457+ WHERE hidden = FALSE AND cur_space=$1 and post_tags.name = $2`, space, tag).Scan(&count)
621458 }
622459 if err != nil {
623460 return nil, err
......@@ -632,7 +469,32 @@ func (me *PsqlDB) postPager(rs *sqlx.Rows, pageNum int, space string, tag string
632469 }
633470
634471 func (me *PsqlDB) FindPostsForFeed(page *db.Pager, space string) (*db.Paginate[*db.Post], error) {
635- rs, err := me.Db.Queryx(sqlSelectPostsByRank, page.Num, page.Num*page.Page, space)
472+ query := `
473+ SELECT *
474+ FROM (
475+ SELECT DISTINCT ON (posts.user_id)
476+ posts.id,
477+ posts.user_id,
478+ posts.filename,
479+ posts.slug,
480+ posts.title,
481+ posts.text,
482+ posts.description,
483+ posts.publish_at,
484+ app_users.name AS username,
485+ posts.updated_at,
486+ posts.mime_type
487+ FROM posts
488+ LEFT JOIN app_users ON app_users.id = posts.user_id
489+ WHERE
490+ hidden = FALSE
491+ AND publish_at::date <= CURRENT_DATE
492+ AND cur_space = $3
493+ ORDER BY posts.user_id, publish_at DESC
494+ ) AS latest_posts
495+ ORDER BY publish_at DESC
496+ LIMIT $1 OFFSET $2`
497+ rs, err := me.Db.Queryx(query, page.Num, page.Num*page.Page, space)
636498 if err != nil {
637499 return nil, err
638500 }
......@@ -642,8 +504,14 @@ func (me *PsqlDB) FindPostsForFeed(page *db.Pager, space string) (*db.Paginate[*
642504
643505 func (me *PsqlDB) InsertPost(post *db.Post) (*db.Post, error) {
644506 var id string
507+ query := `
508+ INSERT INTO posts
509+ (user_id, filename, slug, title, text, description, publish_at, hidden, cur_space,
510+ file_size, mime_type, shasum, data, expires_at, updated_at)
511+ VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)
512+ RETURNING id`
645513 err := me.Db.QueryRow(
646- sqlInsertPost,
514+ query,
647515 post.UserID,
648516 post.Filename,
649517 post.Slug,
......@@ -668,8 +536,13 @@ func (me *PsqlDB) InsertPost(post *db.Post) (*db.Post, error) {
668536 }
669537
670538 func (me *PsqlDB) UpdatePost(post *db.Post) (*db.Post, error) {
539+ query := `
540+ UPDATE posts
541+ SET slug = $1, title = $2, text = $3, description = $4, updated_at = $5, publish_at = $6,
542+ file_size = $7, shasum = $8, data = $9, hidden = $11, expires_at = $12
543+ WHERE id = $10`
671544 _, err := me.Db.Exec(
672- sqlUpdatePost,
545+ query,
673546 post.Slug,
674547 post.Title,
675548 post.Text,
......@@ -692,14 +565,27 @@ func (me *PsqlDB) UpdatePost(post *db.Post) (*db.Post, error) {
692565
693566 func (me *PsqlDB) RemovePosts(postIDs []string) error {
694567 param := "{" + strings.Join(postIDs, ",") + "}"
695- _, err := me.Db.Exec(sqlRemovePosts, param)
568+ _, err := me.Db.Exec(`DELETE FROM posts WHERE id = ANY($1::uuid[])`, param)
696569 return err
697570 }
698571
699572 func (me *PsqlDB) FindPostsForUser(page *db.Pager, userID string, space string) (*db.Paginate[*db.Post], error) {
700573 var posts []*db.Post
574+ query := fmt.Sprintf(`
575+ SELECT %s, STRING_AGG(coalesce(post_tags.name, ''), ',') tags
576+ FROM posts
577+ LEFT JOIN app_users ON app_users.id = posts.user_id
578+ LEFT JOIN post_tags ON post_tags.post_id = posts.id
579+ WHERE
580+ hidden = FALSE AND
581+ user_id = $1 AND
582+ publish_at::date <= CURRENT_DATE AND
583+ cur_space = $2
584+ GROUP BY %s
585+ ORDER BY publish_at DESC, slug DESC
586+ LIMIT $3 OFFSET $4`, SelectPost, SelectPost)
701587 rs, err := me.Db.Queryx(
702- sqlSelectPostsForUser,
588+ query,
703589 userID,
704590 space,
705591 page.Num,
......@@ -723,7 +609,7 @@ func (me *PsqlDB) FindPostsForUser(page *db.Pager, userID string, space string)
723609 }
724610
725611 var count int
726- err = me.Db.QueryRow(sqlSelectPostCount, space).Scan(&count)
612+ err = me.Db.QueryRow(`SELECT count(id) FROM posts WHERE hidden = FALSE AND cur_space=$1`, space).Scan(&count)
727613 if err != nil {
728614 return nil, err
729615 }
......@@ -737,7 +623,15 @@ func (me *PsqlDB) FindPostsForUser(page *db.Pager, userID string, space string)
737623
738624 func (me *PsqlDB) FindAllPostsForUser(userID string, space string) ([]*db.Post, error) {
739625 var posts []*db.Post
740- err := me.Db.Select(&posts, sqlSelectAllPostsForUser, userID, space)
626+ query := fmt.Sprintf(`
627+ SELECT %s
628+ FROM posts
629+ LEFT JOIN app_users ON app_users.id = posts.user_id
630+ WHERE
631+ user_id = $1 AND
632+ cur_space = $2
633+ ORDER BY publish_at DESC`, SelectPost)
634+ err := me.Db.Select(&posts, query, userID, space)
741635 if err != nil {
742636 return nil, err
743637 }
......@@ -746,7 +640,11 @@ func (me *PsqlDB) FindAllPostsForUser(userID string, space string) ([]*db.Post,
746640
747641 func (me *PsqlDB) FindPosts() ([]*db.Post, error) {
748642 var posts []*db.Post
749- err := me.Db.Select(&posts, sqlSelectPosts)
643+ query := fmt.Sprintf(`
644+ SELECT %s
645+ FROM posts
646+ LEFT JOIN app_users ON app_users.id = posts.user_id`, SelectPost)
647+ err := me.Db.Select(&posts, query)
750648 if err != nil {
751649 return nil, err
752650 }
......@@ -755,7 +653,15 @@ func (me *PsqlDB) FindPosts() ([]*db.Post, error) {
755653
756654 func (me *PsqlDB) FindExpiredPosts(space string) ([]*db.Post, error) {
757655 var posts []*db.Post
758- err := me.Db.Select(&posts, sqlSelectExpiredPosts, space)
656+ query := fmt.Sprintf(`
657+ SELECT %s
658+ FROM posts
659+ LEFT JOIN app_users ON app_users.id = posts.user_id
660+ WHERE
661+ cur_space = $1 AND
662+ expires_at <= now();
663+ `, SelectPost)
664+ err := me.Db.Select(&posts, query, space)
759665 if err != nil {
760666 return nil, err
761667 }
......@@ -1024,7 +930,7 @@ func (me *PsqlDB) FindVisitSiteList(opts *db.SummaryOpts) ([]*db.VisitUrl, error
1024930
1025931 func (me *PsqlDB) FindUsers() ([]*db.User, error) {
1026932 var users []*db.User
1027- err := me.Db.Select(&users, sqlSelectUsers)
933+ err := me.Db.Select(&users, `SELECT id, COALESCE(name, '') as name, created_at FROM app_users ORDER BY name ASC`)
1028934 if err != nil {
1029935 return nil, err
1030936 }
......@@ -1032,7 +938,7 @@ func (me *PsqlDB) FindUsers() ([]*db.User, error) {
1032938 }
1033939
1034940 func (me *PsqlDB) removeTagsForPost(tx *sqlx.Tx, postID string) error {
1035- _, err := tx.Exec(sqlRemoveTagsByPost, postID)
941+ _, err := tx.Exec(`DELETE FROM post_tags WHERE post_id = $1`, postID)
1036942 return err
1037943 }
1038944
......@@ -1040,7 +946,7 @@ func (me *PsqlDB) insertTagsForPost(tx *sqlx.Tx, tags []string, postID string) (
1040946 ids := make([]string, 0)
1041947 for _, tag := range tags {
1042948 id := ""
1043- err := tx.QueryRow(sqlInsertTag, postID, tag).Scan(&id)
949+ err := tx.QueryRow(`INSERT INTO post_tags (post_id, name) VALUES($1, $2) RETURNING id;`, postID, tag).Scan(&id)
1044950 if err != nil {
1045951 return nil, err
1046952 }
......@@ -1074,7 +980,7 @@ func (me *PsqlDB) ReplaceTagsForPost(tags []string, postID string) error {
1074980 }
1075981
1076982 func (me *PsqlDB) removeAliasesForPost(tx *sqlx.Tx, postID string) error {
1077- _, err := tx.Exec(sqlRemoveAliasesByPost, postID)
983+ _, err := tx.Exec(`DELETE FROM post_aliases WHERE post_id = $1`, postID)
1078984 return err
1079985 }
1080986
......@@ -1109,7 +1015,7 @@ func (me *PsqlDB) insertAliasesForPost(tx *sqlx.Tx, aliases []string, postID str
11091015 continue
11101016 }
11111017 id := ""
1112- err := tx.QueryRow(sqlInsertAliases, postID, alias).Scan(&id)
1018+ err := tx.QueryRow(`INSERT INTO post_aliases (post_id, slug) VALUES($1, $2) RETURNING id;`, postID, alias).Scan(&id)
11131019 if err != nil {
11141020 return nil, err
11151021 }
......@@ -1144,9 +1050,22 @@ func (me *PsqlDB) ReplaceAliasesForPost(aliases []string, postID string) error {
11441050
11451051 func (me *PsqlDB) FindUserPostsByTag(page *db.Pager, tag, userID, space string) (*db.Paginate[*db.Post], error) {
11461052 var posts []*db.Post
1053+ query := fmt.Sprintf(`
1054+ SELECT %s
1055+ FROM posts
1056+ LEFT JOIN app_users ON app_users.id = posts.user_id
1057+ LEFT JOIN post_tags ON post_tags.post_id = posts.id
1058+ WHERE
1059+ hidden = FALSE AND
1060+ user_id = $1 AND
1061+ (post_tags.name = $2 OR hidden = true) AND
1062+ publish_at::date <= CURRENT_DATE AND
1063+ cur_space = $3
1064+ ORDER BY publish_at DESC
1065+ LIMIT $4 OFFSET $5`, SelectPost)
11471066 err := me.Db.Select(
11481067 &posts,
1149- sqlSelectUserPostsByTag,
1068+ query,
11501069 userID,
11511070 tag,
11521071 space,
......@@ -1158,7 +1077,7 @@ func (me *PsqlDB) FindUserPostsByTag(page *db.Pager, tag, userID, space string)
11581077 }
11591078
11601079 var count int
1161- err = me.Db.QueryRow(sqlSelectPostCount, space).Scan(&count)
1080+ err = me.Db.QueryRow(`SELECT count(id) FROM posts WHERE hidden = FALSE AND cur_space=$1`, space).Scan(&count)
11621081 if err != nil {
11631082 return nil, err
11641083 }
......@@ -1171,8 +1090,30 @@ func (me *PsqlDB) FindUserPostsByTag(page *db.Pager, tag, userID, space string)
11711090 }
11721091
11731092 func (me *PsqlDB) FindPostsByTag(pager *db.Pager, tag, space string) (*db.Paginate[*db.Post], error) {
1093+ query := `
1094+ SELECT
1095+ posts.id,
1096+ user_id,
1097+ filename,
1098+ slug,
1099+ title,
1100+ text,
1101+ description,
1102+ publish_at,
1103+ app_users.name as username,
1104+ posts.updated_at,
1105+ posts.mime_type
1106+ FROM posts
1107+ LEFT JOIN app_users ON app_users.id = posts.user_id
1108+ LEFT JOIN post_tags ON post_tags.post_id = posts.id
1109+ WHERE
1110+ post_tags.name = $3 AND
1111+ publish_at::date <= CURRENT_DATE AND
1112+ cur_space = $4
1113+ ORDER BY publish_at DESC
1114+ LIMIT $1 OFFSET $2`
11741115 rs, err := me.Db.Queryx(
1175- sqlSelectPostsByTag,
1116+ query,
11761117 pager.Num,
11771118 pager.Num*pager.Page,
11781119 tag,
......@@ -1188,7 +1129,15 @@ func (me *PsqlDB) FindPostsByTag(pager *db.Pager, tag, space string) (*db.Pagina
11881129
11891130 func (me *PsqlDB) FindPopularTags(space string) ([]string, error) {
11901131 tags := make([]string, 0)
1191- rs, err := me.Db.Queryx(sqlSelectPopularTags, space)
1132+ query := `
1133+ SELECT name, count(post_id) as "tally"
1134+ FROM post_tags
1135+ LEFT JOIN posts ON posts.id = post_id
1136+ WHERE posts.cur_space = $1
1137+ GROUP BY name
1138+ ORDER BY tally DESC
1139+ LIMIT 5`
1140+ rs, err := me.Db.Queryx(query, space)
11921141 if err != nil {
11931142 return tags, err
11941143 }
......@@ -1211,7 +1160,7 @@ func (me *PsqlDB) FindPopularTags(space string) ([]string, error) {
12111160
12121161 func (me *PsqlDB) FindFeature(userID string, feature string) (*db.FeatureFlag, error) {
12131162 ff := &db.FeatureFlag{}
1214- err := me.Db.Get(ff, sqlSelectFeatureForUser, userID, feature)
1163+ err := me.Db.Get(ff, `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`, userID, feature)
12151164 if err != nil {
12161165 return nil, err
12171166 }
......@@ -1252,7 +1201,7 @@ func (me *PsqlDB) InsertFeedItems(postID string, items []*db.FeedItem) error {
12521201
12531202 for _, item := range items {
12541203 _, err := tx.Exec(
1255- sqlInsertFeedItems,
1204+ `INSERT INTO feed_items (post_id, guid, data) VALUES ($1, $2, $3) RETURNING id;`,
12561205 item.PostID,
12571206 item.GUID,
12581207 item.Data,
......@@ -1271,7 +1220,7 @@ func (me *PsqlDB) InsertFeedItems(postID string, items []*db.FeedItem) error {
12711220
12721221 func (me *PsqlDB) FindFeedItemsByPostID(postID string) ([]*db.FeedItem, error) {
12731222 var items []*db.FeedItem
1274- err := me.Db.Select(&items, sqlSelectFeedItemsByPost, postID)
1223+ err := me.Db.Select(&items, `SELECT id, post_id, guid, data, created_at FROM feed_items WHERE post_id=$1`, postID)
12751224 if err != nil {
12761225 return nil, err
12771226 }
......@@ -1284,7 +1233,7 @@ func (me *PsqlDB) InsertProject(userID, name, projectDir string) (string, error)
12841233 }
12851234
12861235 var id string
1287- err := me.Db.QueryRow(sqlInsertProject, userID, name, projectDir).Scan(&id)
1236+ err := me.Db.QueryRow(`INSERT INTO projects (user_id, name, project_dir) VALUES ($1, $2, $3) RETURNING id;`, userID, name, projectDir).Scan(&id)
12881237 if err != nil {
12891238 return "", err
12901239 }
......@@ -1292,13 +1241,13 @@ func (me *PsqlDB) InsertProject(userID, name, projectDir string) (string, error)
12921241 }
12931242
12941243 func (me *PsqlDB) UpdateProject(userID, name string) error {
1295- _, err := me.Db.Exec(sqlUpdateProject, userID, name, time.Now())
1244+ _, err := me.Db.Exec(`UPDATE projects SET updated_at = $3 WHERE user_id = $1 AND name = $2;`, userID, name, time.Now())
12961245 return err
12971246 }
12981247
12991248 func (me *PsqlDB) FindProjectByName(userID, name string) (*db.Project, error) {
13001249 project := &db.Project{}
1301- err := me.Db.Get(project, sqlFindProjectByName, userID, name)
1250+ err := me.Db.Get(project, `SELECT id, user_id, name, project_dir, acl, blocked, created_at, updated_at FROM projects WHERE user_id = $1 AND name = $2;`, userID, name)
13021251 if err != nil {
13031252 return nil, err
13041253 }
......@@ -1307,7 +1256,7 @@ func (me *PsqlDB) FindProjectByName(userID, name string) (*db.Project, error) {
13071256
13081257 func (me *PsqlDB) InsertToken(userID, name string) (string, error) {
13091258 var token string
1310- err := me.Db.QueryRow(sqlInsertToken, userID, name).Scan(&token)
1259+ err := me.Db.QueryRow(`INSERT INTO tokens (user_id, name) VALUES($1, $2) RETURNING token;`, userID, name).Scan(&token)
13111260 if err != nil {
13121261 return "", err
13131262 }
......@@ -1326,7 +1275,7 @@ func (me *PsqlDB) UpsertToken(userID, name string) (string, error) {
13261275
13271276 func (me *PsqlDB) findTokenByName(userID, name string) (string, error) {
13281277 var token string
1329- err := me.Db.QueryRow(sqlSelectTokenByNameForUser, userID, name).Scan(&token)
1278+ err := me.Db.QueryRow(`SELECT token FROM tokens WHERE user_id = $1 AND name = $2`, userID, name).Scan(&token)
13301279 if err != nil {
13311280 return "", err
13321281 }
......@@ -1334,13 +1283,13 @@ func (me *PsqlDB) findTokenByName(userID, name string) (string, error) {
13341283 }
13351284
13361285 func (me *PsqlDB) RemoveToken(tokenID string) error {
1337- _, err := me.Db.Exec(sqlRemoveToken, tokenID)
1286+ _, err := me.Db.Exec(`DELETE FROM tokens WHERE id = $1`, tokenID)
13381287 return err
13391288 }
13401289
13411290 func (me *PsqlDB) FindTokensForUser(userID string) ([]*db.Token, error) {
13421291 var tokens []*db.Token
1343- err := me.Db.Select(&tokens, sqlSelectTokensForUser, userID)
1292+ err := me.Db.Select(&tokens, `SELECT id, user_id, name, created_at, expires_at FROM tokens WHERE user_id = $1`, userID)
13441293 if err != nil {
13451294 return nil, err
13461295 }
Back to top