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.1 on 2025-12-18T04:19:09Z · commit 29035c6

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
+35 -96 pkg/db/postgres/storage.go #
......@@ -222,34 +222,6 @@ type RowScanner interface {
222222 Scan(dest ...any) error
223223 }
224224
225-func CreatePostFromRow(r RowScanner) (*db.Post, error) {
226- post := &db.Post{}
227- err := r.Scan(
228- &post.ID,
229- &post.UserID,
230- &post.Username,
231- &post.Filename,
232- &post.Slug,
233- &post.Title,
234- &post.Text,
235- &post.Description,
236- &post.CreatedAt,
237- &post.PublishAt,
238- &post.UpdatedAt,
239- &post.Hidden,
240- &post.FileSize,
241- &post.MimeType,
242- &post.Shasum,
243- &post.Data,
244- &post.ExpiresAt,
245- &post.Views,
246- )
247- if err != nil {
248- return nil, err
249- }
250- return post, nil
251-}
252-
253225 func CreatePostWithTagsFromRow(r RowScanner) (*db.Post, error) {
254226 post := &db.Post{}
255227 tagStr := ""
......@@ -380,10 +352,11 @@ func (me *PsqlDB) UpdatePublicKey(pubkeyID, name string) (*db.PublicKey, error)
380352
381353 func (me *PsqlDB) findPublicKeyForKey(key string) (*db.PublicKey, error) {
382354 var keys []*db.PublicKey
383- rs, err := me.Db.Query(sqlSelectPublicKey, key)
355+ rs, err := me.Db.Queryx(sqlSelectPublicKey, key)
384356 if err != nil {
385357 return nil, err
386358 }
359+ defer func() { _ = rs.Close() }()
387360
388361 for rs.Next() {
389362 pk := &db.PublicKey{}
......@@ -440,7 +413,7 @@ func (me *PsqlDB) RemoveKeys(keyIDs []string) error {
440413
441414 func (me *PsqlDB) FindUsersWithPost(space string) ([]*db.User, error) {
442415 var users []*db.User
443- rs, err := me.Db.Query(
416+ rs, err := me.Db.Queryx(
444417 `SELECT u.id, u.name, u.created_at
445418 FROM app_users u
446419 INNER JOIN posts ON u.id=posts.user_id
......@@ -451,6 +424,7 @@ func (me *PsqlDB) FindUsersWithPost(space string) ([]*db.User, error) {
451424 if err != nil {
452425 return users, err
453426 }
427+ defer func() { _ = rs.Close() }()
454428 for rs.Next() {
455429 var name sql.NullString
456430 user := &db.User{}
......@@ -603,16 +577,15 @@ func (me *PsqlDB) FindPostWithSlug(slug string, user_id string, space string) (*
603577 }
604578
605579 func (me *PsqlDB) FindPost(postID string) (*db.Post, error) {
606- r := me.Db.QueryRow(sqlSelectPost, postID)
607- post, err := CreatePostFromRow(r)
580+ post := &db.Post{}
581+ err := me.Db.Get(post, sqlSelectPost, postID)
608582 if err != nil {
609583 return nil, err
610584 }
611-
612585 return post, nil
613586 }
614587
615-func (me *PsqlDB) postPager(rs *sql.Rows, pageNum int, space string, tag string) (*db.Paginate[*db.Post], error) {
588+func (me *PsqlDB) postPager(rs *sqlx.Rows, pageNum int, space string, tag string) (*db.Paginate[*db.Post], error) {
616589 var posts []*db.Post
617590 for rs.Next() {
618591 post := &db.Post{}
......@@ -659,10 +632,11 @@ func (me *PsqlDB) postPager(rs *sql.Rows, pageNum int, space string, tag string)
659632 }
660633
661634 func (me *PsqlDB) FindPostsForFeed(page *db.Pager, space string) (*db.Paginate[*db.Post], error) {
662- rs, err := me.Db.Query(sqlSelectPostsByRank, page.Num, page.Num*page.Page, space)
635+ rs, err := me.Db.Queryx(sqlSelectPostsByRank, page.Num, page.Num*page.Page, space)
663636 if err != nil {
664637 return nil, err
665638 }
639+ defer func() { _ = rs.Close() }()
666640 return me.postPager(rs, page.Num, space, "")
667641 }
668642
......@@ -724,7 +698,7 @@ func (me *PsqlDB) RemovePosts(postIDs []string) error {
724698
725699 func (me *PsqlDB) FindPostsForUser(page *db.Pager, userID string, space string) (*db.Paginate[*db.Post], error) {
726700 var posts []*db.Post
727- rs, err := me.Db.Query(
701+ rs, err := me.Db.Queryx(
728702 sqlSelectPostsForUser,
729703 userID,
730704 space,
......@@ -734,6 +708,7 @@ func (me *PsqlDB) FindPostsForUser(page *db.Pager, userID string, space string)
734708 if err != nil {
735709 return nil, err
736710 }
711+ defer func() { _ = rs.Close() }()
737712 for rs.Next() {
738713 post, err := CreatePostWithTagsFromRow(rs)
739714 if err != nil {
......@@ -762,60 +737,27 @@ func (me *PsqlDB) FindPostsForUser(page *db.Pager, userID string, space string)
762737
763738 func (me *PsqlDB) FindAllPostsForUser(userID string, space string) ([]*db.Post, error) {
764739 var posts []*db.Post
765- rs, err := me.Db.Query(sqlSelectAllPostsForUser, userID, space)
740+ err := me.Db.Select(&posts, sqlSelectAllPostsForUser, userID, space)
766741 if err != nil {
767- return posts, err
768- }
769- for rs.Next() {
770- post, err := CreatePostFromRow(rs)
771- if err != nil {
772- return nil, err
773- }
774-
775- posts = append(posts, post)
776- }
777- if rs.Err() != nil {
778- return posts, rs.Err()
742+ return nil, err
779743 }
780744 return posts, nil
781745 }
782746
783747 func (me *PsqlDB) FindPosts() ([]*db.Post, error) {
784748 var posts []*db.Post
785- rs, err := me.Db.Query(sqlSelectPosts)
749+ err := me.Db.Select(&posts, sqlSelectPosts)
786750 if err != nil {
787- return posts, err
788- }
789- for rs.Next() {
790- post, err := CreatePostFromRow(rs)
791- if err != nil {
792- return nil, err
793- }
794-
795- posts = append(posts, post)
796- }
797- if rs.Err() != nil {
798- return posts, rs.Err()
751+ return nil, err
799752 }
800753 return posts, nil
801754 }
802755
803756 func (me *PsqlDB) FindExpiredPosts(space string) ([]*db.Post, error) {
804757 var posts []*db.Post
805- rs, err := me.Db.Query(sqlSelectExpiredPosts, space)
758+ err := me.Db.Select(&posts, sqlSelectExpiredPosts, space)
806759 if err != nil {
807- return posts, err
808- }
809- for rs.Next() {
810- post, err := CreatePostFromRow(rs)
811- if err != nil {
812- return nil, err
813- }
814-
815- posts = append(posts, post)
816- }
817- if rs.Err() != nil {
818- return posts, rs.Err()
760+ return nil, err
819761 }
820762 return posts, nil
821763 }
......@@ -877,10 +819,11 @@ func (me *PsqlDB) visitUnique(opts *db.SummaryOpts) ([]*db.VisitInterval, error)
877819 GROUP BY interval_start`, opts.Interval, where)
878820
879821 intervals := []*db.VisitInterval{}
880- rs, err := me.Db.Query(uniqueVisitors, opts.Origin, with, opts.UserID)
822+ rs, err := me.Db.Queryx(uniqueVisitors, opts.Origin, with, opts.UserID)
881823 if err != nil {
882824 return nil, err
883825 }
826+ defer func() { _ = rs.Close() }()
884827
885828 for rs.Next() {
886829 interval := &db.VisitInterval{}
......@@ -912,10 +855,11 @@ func (me *PsqlDB) visitReferer(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
912855 LIMIT 10`, where)
913856
914857 intervals := []*db.VisitUrl{}
915- rs, err := me.Db.Query(topUrls, opts.Origin, with, opts.UserID)
858+ rs, err := me.Db.Queryx(topUrls, opts.Origin, with, opts.UserID)
916859 if err != nil {
917860 return nil, err
918861 }
862+ defer func() { _ = rs.Close() }()
919863
920864 for rs.Next() {
921865 interval := &db.VisitUrl{}
......@@ -947,10 +891,11 @@ func (me *PsqlDB) visitUrl(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
947891 LIMIT 10`, where)
948892
949893 intervals := []*db.VisitUrl{}
950- rs, err := me.Db.Query(topUrls, opts.Origin, with, opts.UserID)
894+ rs, err := me.Db.Queryx(topUrls, opts.Origin, with, opts.UserID)
951895 if err != nil {
952896 return nil, err
953897 }
898+ defer func() { _ = rs.Close() }()
954899
955900 for rs.Next() {
956901 interval := &db.VisitUrl{}
......@@ -986,10 +931,11 @@ func (me *PsqlDB) VisitUrlNotFound(opts *db.SummaryOpts) ([]*db.VisitUrl, error)
986931 LIMIT %d`, where, limit)
987932
988933 intervals := []*db.VisitUrl{}
989- rs, err := me.Db.Query(topUrls, opts.Origin, with, opts.UserID)
934+ rs, err := me.Db.Queryx(topUrls, opts.Origin, with, opts.UserID)
990935 if err != nil {
991936 return nil, err
992937 }
938+ defer func() { _ = rs.Close() }()
993939
994940 for rs.Next() {
995941 interval := &db.VisitUrl{}
......@@ -1019,10 +965,11 @@ func (me *PsqlDB) visitHost(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
1019965 ORDER BY host_count DESC`
1020966
1021967 intervals := []*db.VisitUrl{}
1022- rs, err := me.Db.Query(topUrls, opts.UserID)
968+ rs, err := me.Db.Queryx(topUrls, opts.UserID)
1023969 if err != nil {
1024970 return nil, err
1025971 }
972+ defer func() { _ = rs.Close() }()
1026973
1027974 for rs.Next() {
1028975 interval := &db.VisitUrl{}
......@@ -1197,7 +1144,8 @@ func (me *PsqlDB) ReplaceAliasesForPost(aliases []string, postID string) error {
11971144
11981145 func (me *PsqlDB) FindUserPostsByTag(page *db.Pager, tag, userID, space string) (*db.Paginate[*db.Post], error) {
11991146 var posts []*db.Post
1200- rs, err := me.Db.Query(
1147+ err := me.Db.Select(
1148+ &posts,
12011149 sqlSelectUserPostsByTag,
12021150 userID,
12031151 tag,
......@@ -1208,18 +1156,6 @@ func (me *PsqlDB) FindUserPostsByTag(page *db.Pager, tag, userID, space string)
12081156 if err != nil {
12091157 return nil, err
12101158 }
1211- for rs.Next() {
1212- post, err := CreatePostFromRow(rs)
1213- if err != nil {
1214- return nil, err
1215- }
1216-
1217- posts = append(posts, post)
1218- }
1219-
1220- if rs.Err() != nil {
1221- return nil, rs.Err()
1222- }
12231159
12241160 var count int
12251161 err = me.Db.QueryRow(sqlSelectPostCount, space).Scan(&count)
......@@ -1235,7 +1171,7 @@ func (me *PsqlDB) FindUserPostsByTag(page *db.Pager, tag, userID, space string)
12351171 }
12361172
12371173 func (me *PsqlDB) FindPostsByTag(pager *db.Pager, tag, space string) (*db.Paginate[*db.Post], error) {
1238- rs, err := me.Db.Query(
1174+ rs, err := me.Db.Queryx(
12391175 sqlSelectPostsByTag,
12401176 pager.Num,
12411177 pager.Num*pager.Page,
......@@ -1245,16 +1181,18 @@ func (me *PsqlDB) FindPostsByTag(pager *db.Pager, tag, space string) (*db.Pagina
12451181 if err != nil {
12461182 return nil, err
12471183 }
1184+ defer func() { _ = rs.Close() }()
12481185
12491186 return me.postPager(rs, pager.Num, space, tag)
12501187 }
12511188
12521189 func (me *PsqlDB) FindPopularTags(space string) ([]string, error) {
12531190 tags := make([]string, 0)
1254- rs, err := me.Db.Query(sqlSelectPopularTags, space)
1191+ rs, err := me.Db.Queryx(sqlSelectPopularTags, space)
12551192 if err != nil {
12561193 return tags, err
12571194 }
1195+ defer func() { _ = rs.Close() }()
12581196 for rs.Next() {
12591197 name := ""
12601198 tally := 0
......@@ -1559,10 +1497,11 @@ func (me *PsqlDB) FindTunsEventLogs(userID string) ([]*db.TunsEventLog, error) {
15591497
15601498 func (me *PsqlDB) FindUserStats(userID string) (*db.UserStats, error) {
15611499 stats := db.UserStats{}
1562- rs, err := me.Db.Query(`SELECT cur_space, count(id), min(created_at), max(created_at), max(updated_at) FROM posts WHERE user_id=$1 GROUP BY cur_space`, userID)
1500+ rs, err := me.Db.Queryx(`SELECT cur_space, count(id), min(created_at), max(created_at), max(updated_at) FROM posts WHERE user_id=$1 GROUP BY cur_space`, userID)
15631501 if err != nil {
15641502 return nil, err
15651503 }
1504+ defer func() { _ = rs.Close() }()
15661505
15671506 for rs.Next() {
15681507 stat := db.UserServiceStats{}
Back to top