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 4bd37ed

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
+19 -6 pkg/db/db.go #
......@@ -205,13 +205,25 @@ type AnalyticsVisits struct {
205205 ContentType string `json:"content_type" db:"content_type"`
206206 }
207207
208+type AccessLogData struct{}
209+
210+func (p *AccessLogData) Scan(value any) error {
211+ b, err := tcast(value)
212+ if err != nil {
213+ return err
214+ }
215+
216+ return json.Unmarshal(b, &p)
217+}
218+
208219 type AccessLog struct {
209- ID string `json:"id" db:"id"`
210- UserID string `json:"user_id" db:"user_id"`
211- Service string `json:"service" db:"service"`
212- Pubkey string `json:"pubkey" db:"pubkey"`
213- Identity string `json:"identity" db:"identity"`
214- CreatedAt *time.Time `json:"created_at" db:"created_at"`
220+ ID string `json:"id" db:"id"`
221+ UserID string `json:"user_id" db:"user_id"`
222+ Service string `json:"service" db:"service"`
223+ Pubkey string `json:"pubkey" db:"pubkey"`
224+ Identity string `json:"identity" db:"identity"`
225+ Data AccessLogData `json:"data" db:"data"`
226+ CreatedAt *time.Time `json:"created_at" db:"created_at"`
215227 }
216228
217229 type Pager struct {
......@@ -231,6 +243,7 @@ type Token struct {
231243 ID string `json:"id" db:"id"`
232244 UserID string `json:"user_id" db:"user_id"`
233245 Name string `json:"name" db:"name"`
246+ Token string `json:"token" db:"token"`
234247 CreatedAt *time.Time `json:"created_at" db:"created_at"`
235248 ExpiresAt *time.Time `json:"expires_at" db:"expires_at"`
236249 }
+12 -15 pkg/db/postgres/storage.go #
......@@ -199,7 +199,7 @@ func (me *PsqlDB) findPublicKeyForKey(key string) (*db.PublicKey, error) {
199199
200200 func (me *PsqlDB) findPublicKey(pubkeyID string) (*db.PublicKey, error) {
201201 pk := &db.PublicKey{}
202- err := me.Db.Get(pk, `SELECT id, user_id, name, public_key, created_at FROM public_keys WHERE id = $1`, pubkeyID)
202+ err := me.Db.Get(pk, `SELECT * FROM public_keys WHERE id = $1`, pubkeyID)
203203 if err != nil {
204204 return nil, err
205205 }
......@@ -208,7 +208,7 @@ func (me *PsqlDB) findPublicKey(pubkeyID string) (*db.PublicKey, error) {
208208
209209 func (me *PsqlDB) FindKeysForUser(user *db.User) ([]*db.PublicKey, error) {
210210 var keys []*db.PublicKey
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)
211+ err := me.Db.Select(&keys, `SELECT * FROM public_keys WHERE user_id = $1 ORDER BY created_at ASC`, user.ID)
212212 if err != nil {
213213 return nil, err
214214 }
......@@ -328,7 +328,7 @@ func (me *PsqlDB) validateName(name string) (bool, error) {
328328
329329 func (me *PsqlDB) FindUserByName(name string) (*db.User, error) {
330330 user := &db.User{}
331- err := me.Db.Get(user, `SELECT id, name, created_at FROM app_users WHERE name = $1`, strings.ToLower(name))
331+ err := me.Db.Get(user, `SELECT * FROM app_users WHERE name = $1`, strings.ToLower(name))
332332 if err != nil {
333333 return nil, err
334334 }
......@@ -1160,7 +1160,7 @@ func (me *PsqlDB) FindPopularTags(space string) ([]string, error) {
11601160
11611161 func (me *PsqlDB) FindFeature(userID string, feature string) (*db.FeatureFlag, error) {
11621162 ff := &db.FeatureFlag{}
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)
1163+ err := me.Db.Get(ff, `SELECT * FROM feature_flags WHERE user_id = $1 AND name = $2 ORDER BY expires_at DESC LIMIT 1`, userID, feature)
11641164 if err != nil {
11651165 return nil, err
11661166 }
......@@ -1170,8 +1170,7 @@ func (me *PsqlDB) FindFeature(userID string, feature string) (*db.FeatureFlag, e
11701170 func (me *PsqlDB) FindFeaturesForUser(userID string) ([]*db.FeatureFlag, error) {
11711171 var features []*db.FeatureFlag
11721172 // https://stackoverflow.com/a/16920077
1173- query := `SELECT DISTINCT ON (name)
1174- id, user_id, payment_history_id, name, data, created_at, expires_at
1173+ query := `SELECT DISTINCT ON (name) *
11751174 FROM feature_flags
11761175 WHERE user_id=$1
11771176 ORDER BY name, expires_at DESC;`
......@@ -1220,7 +1219,7 @@ func (me *PsqlDB) InsertFeedItems(postID string, items []*db.FeedItem) error {
12201219
12211220 func (me *PsqlDB) FindFeedItemsByPostID(postID string) ([]*db.FeedItem, error) {
12221221 var items []*db.FeedItem
1223- err := me.Db.Select(&items, `SELECT id, post_id, guid, data, created_at FROM feed_items WHERE post_id=$1`, postID)
1222+ err := me.Db.Select(&items, `SELECT * FROM feed_items WHERE post_id=$1`, postID)
12241223 if err != nil {
12251224 return nil, err
12261225 }
......@@ -1247,7 +1246,7 @@ func (me *PsqlDB) UpdateProject(userID, name string) error {
12471246
12481247 func (me *PsqlDB) FindProjectByName(userID, name string) (*db.Project, error) {
12491248 project := &db.Project{}
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)
1249+ err := me.Db.Get(project, `SELECT * FROM projects WHERE user_id = $1 AND name = $2;`, userID, name)
12511250 if err != nil {
12521251 return nil, err
12531252 }
......@@ -1289,7 +1288,7 @@ func (me *PsqlDB) RemoveToken(tokenID string) error {
12891288
12901289 func (me *PsqlDB) FindTokensForUser(userID string) ([]*db.Token, error) {
12911290 var tokens []*db.Token
1292- err := me.Db.Select(&tokens, `SELECT id, user_id, name, created_at, expires_at FROM tokens WHERE user_id = $1`, userID)
1291+ err := me.Db.Select(&tokens, `SELECT * FROM tokens WHERE user_id = $1`, userID)
12931292 if err != nil {
12941293 return nil, err
12951294 }
......@@ -1425,8 +1424,7 @@ func (me *PsqlDB) InsertTunsEventLog(log *db.TunsEventLog) error {
14251424 func (me *PsqlDB) FindTunsEventLogsByAddr(userID, addr string) ([]*db.TunsEventLog, error) {
14261425 var logs []*db.TunsEventLog
14271426 err := me.Db.Select(&logs,
1428- `SELECT id, user_id, server_id, remote_addr, event_type, tunnel_type, connection_type, tunnel_id, created_at
1429- FROM tuns_event_logs WHERE user_id=$1 AND tunnel_id=$2 ORDER BY created_at DESC`, userID, addr)
1427+ `SELECT * FROM tuns_event_logs WHERE user_id=$1 AND tunnel_id=$2 ORDER BY created_at DESC`, userID, addr)
14301428 if err != nil {
14311429 return nil, err
14321430 }
......@@ -1436,8 +1434,7 @@ func (me *PsqlDB) FindTunsEventLogsByAddr(userID, addr string) ([]*db.TunsEventL
14361434 func (me *PsqlDB) FindTunsEventLogs(userID string) ([]*db.TunsEventLog, error) {
14371435 var logs []*db.TunsEventLog
14381436 err := me.Db.Select(&logs,
1439- `SELECT id, user_id, server_id, remote_addr, event_type, tunnel_type, connection_type, tunnel_id, created_at
1440- FROM tuns_event_logs WHERE user_id=$1 ORDER BY created_at DESC`, userID)
1437+ `SELECT * FROM tuns_event_logs WHERE user_id=$1 ORDER BY created_at DESC`, userID)
14411438 if err != nil {
14421439 return nil, err
14431440 }
......@@ -1482,7 +1479,7 @@ func (me *PsqlDB) FindUserStats(userID string) (*db.UserStats, error) {
14821479
14831480 func (me *PsqlDB) FindAccessLogs(userID string, fromDate *time.Time) ([]*db.AccessLog, error) {
14841481 var logs []*db.AccessLog
1485- err := me.Db.Select(&logs, `SELECT id, user_id, service, pubkey, identity, created_at FROM access_logs WHERE user_id=$1 AND created_at >= $2 ORDER BY created_at DESC`, userID, fromDate)
1482+ err := me.Db.Select(&logs, `SELECT * FROM access_logs WHERE user_id=$1 AND created_at >= $2 ORDER BY created_at DESC`, userID, fromDate)
14861483 if err != nil {
14871484 return nil, err
14881485 }
......@@ -1491,7 +1488,7 @@ func (me *PsqlDB) FindAccessLogs(userID string, fromDate *time.Time) ([]*db.Acce
14911488
14921489 func (me *PsqlDB) FindAccessLogsByPubkey(pubkey string, fromDate *time.Time) ([]*db.AccessLog, error) {
14931490 var logs []*db.AccessLog
1494- err := me.Db.Select(&logs, `SELECT id, user_id, service, pubkey, identity, created_at FROM access_logs WHERE pubkey=$1 AND created_at >= $2 ORDER BY created_at DESC`, pubkey, fromDate)
1491+ err := me.Db.Select(&logs, `SELECT * FROM access_logs WHERE pubkey=$1 AND created_at >= $2 ORDER BY created_at DESC`, pubkey, fromDate)
14951492 if err != nil {
14961493 return nil, err
14971494 }
Back to top