Something went wrong. Try again.
Monorepo for Tangled
Something went wrong. Try again.
8.2 kB · 353 lines
Go
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354package db
import ( "database/sql" "fmt" "slices" "strings" "time"
"github.com/bluesky-social/indigo/atproto/syntax" "tangled.org/core/appview/models" "tangled.org/core/appview/pagination" "tangled.org/core/orm")
func UpsertStar(e Execer, star models.Star) error { _, err := e.Exec( `insert into stars (did, rkey, subject_type, subject, created) values (?, ?, ?, ?, ?) on conflict(did, rkey) do update set subject_type = excluded.subject_type, subject = excluded.subject, created = excluded.created`, star.Did, star.Rkey, string(star.SubjectType), star.Subject, star.Created.Format(time.RFC3339), ) return err}
func GetStars(e Execer, subject string, page pagination.Page) ([]models.Star, error) { query := ` select did, subject_type, subject, created, rkey from stars where subject = ? order by created desc limit ? offset ? ` rows, err := e.Query(query, subject, page.Limit, page.Offset) if err != nil { return nil, err } defer rows.Close()
var stars []models.Star for rows.Next() { var star models.Star var created string if err := rows.Scan(&star.Did, &star.SubjectType, &star.Subject, &created, &star.Rkey); err != nil { return nil, err }
star.Created = time.Now() if t, err := time.Parse(time.RFC3339, created); err == nil { star.Created = t } stars = append(stars, star) }
return stars, rows.Err()}
// Remove all stars from given user to subjectfunc DeleteStars(tx *sql.Tx, did syntax.DID, subject string) ([]syntax.ATURI, error) { var deleted []syntax.ATURI rows, err := tx.Query( `delete from stars where did = ? and subject = ? returning at_uri`, did, subject, ) if err != nil { return nil, fmt.Errorf("deleting stars: %w", err) } defer rows.Close()
for rows.Next() { var aturi syntax.ATURI if err := rows.Scan(&aturi); err != nil { return nil, fmt.Errorf("scanning at_uri: %w", err) } deleted = append(deleted, aturi) }
return deleted, nil}
// Remove a starfunc DeleteStarByRkey(e Execer, did string, rkey string) error { _, err := e.Exec(`delete from stars where did = ? and rkey = ?`, did, rkey) return err}
func GetStarCount(e Execer, subjectType models.StarSubjectType, subject string) (int, error) { stars := 0 err := e.QueryRow( `select count(did) from stars where subject_type = ? and subject = ?`, string(subjectType), subject, ).Scan(&stars) if err != nil { return 0, err } return stars, nil}
// getStarStatuses returns a map of subjects to star status for a given user// This is an internal helper function to avoid N+1 queriesfunc getStarStatuses(e Execer, userDid string, subjects []string) (map[string]bool, error) { if len(subjects) == 0 || userDid == "" { return make(map[string]bool), nil }
placeholders := make([]string, len(subjects)) args := make([]any, len(subjects)+1) args[0] = userDid
for i, subj := range subjects { placeholders[i] = "?" args[i+1] = subj }
query := fmt.Sprintf(` SELECT subject FROM stars WHERE did = ? AND subject IN (%s) `, strings.Join(placeholders, ","))
rows, err := e.Query(query, args...) if err != nil { return nil, err } defer rows.Close()
result := make(map[string]bool) // Initialize all subjects as not starred for _, subj := range subjects { result[subj] = false }
// Mark starred subjects as true for rows.Next() { var subj string if err := rows.Scan(&subj); err != nil { return nil, err } result[subj] = true }
return result, nil}
func GetStarStatus(e Execer, userDid string, subject string) bool { statuses, err := getStarStatuses(e, userDid, []string{subject}) if err != nil { return false } return statuses[subject]}
// GetStarStatuses returns a map of subjects to star status for a given userfunc GetStarStatuses(e Execer, userDid string, subjects []string) (map[string]bool, error) { return getStarStatuses(e, userDid, subjects)}
// GetRepoStars return a list of stars each holding target repository.// If there isn't known repo with starred at-uri, those stars will be ignored.func GetRepoStars(e Execer, page pagination.Page, filters ...orm.Filter) ([]models.RepoStar, error) { var conditions []string var args []any for _, filter := range filters { conditions = append(conditions, filter.Condition()) args = append(args, filter.Arg()...) }
conditions = append(conditions, "subject_type = 'repo'")
whereClause := " where " + strings.Join(conditions, " and ")
pageClause := "" if page.Limit != 0 { pageClause = fmt.Sprintf(" limit %d offset %d", page.Limit, page.Offset) }
repoQuery := fmt.Sprintf( `select did, subject_type, subject, created, rkey from stars %s order by created desc %s`, whereClause, pageClause, ) rows, err := e.Query(repoQuery, args...) if err != nil { return nil, err } defer rows.Close()
starMap := make(map[string][]models.Star) for rows.Next() { var star models.Star var created string err := rows.Scan(&star.Did, &star.SubjectType, &star.Subject, &created, &star.Rkey) if err != nil { return nil, err }
star.Created = time.Now() if t, err := time.Parse(time.RFC3339, created); err == nil { star.Created = t }
starMap[star.Subject] = append(starMap[star.Subject], star) }
// populate *Repo in each star args = make([]any, len(starMap)) i := 0 for r := range starMap { args[i] = r i++ }
if len(args) == 0 { return nil, nil }
repos, err := GetRepos(e, orm.FilterIn("repo_did", args)) if err != nil { return nil, err }
var repoStars []models.RepoStar for _, r := range repos { if stars, ok := starMap[r.RepoDid]; ok { for _, star := range stars { repoStars = append(repoStars, models.RepoStar{ Star: star, Repo: &r, }) } } }
slices.SortFunc(repoStars, func(a, b models.RepoStar) int { if a.Created.After(b.Created) { return -1 } if b.Created.After(a.Created) { return 1 } return 0 })
return repoStars, nil}
func CountStars(e Execer, filters ...orm.Filter) (int64, error) { var conditions []string var args []any for _, filter := range filters { conditions = append(conditions, filter.Condition()) args = append(args, filter.Arg()...) }
whereClause := "" if conditions != nil { whereClause = " where " + strings.Join(conditions, " and ") }
repoQuery := fmt.Sprintf(`select count(1) from stars %s`, whereClause) var count int64 if err := e.QueryRow(repoQuery, args...).Scan(&count); err != nil { return 0, err }
return count, nil}
// GetTopStarredReposLastWeek returns the top 8 most starred repositories from the last weekfunc GetTopStarredReposLastWeek(e Execer) ([]models.Repo, error) { // first, get the top repo DIDs by star count from the last week query := ` with recent_starred_repos as ( select distinct subject from stars where created >= datetime('now', '-7 days') and subject_type = 'repo' ), repo_star_counts as ( select s.subject, count(*) as stars_gained_last_week from stars s join recent_starred_repos rsr on s.subject = rsr.subject where s.created >= datetime('now', '-7 days') and s.subject_type = 'repo' group by s.subject ) select rsc.subject from repo_star_counts rsc order by rsc.stars_gained_last_week desc limit 5 `
rows, err := e.Query(query) if err != nil { return nil, err } defer rows.Close()
var repoDids []string for rows.Next() { var repoDid string err := rows.Scan(&repoDid) if err != nil { return nil, err } repoDids = append(repoDids, repoDid) }
if err := rows.Err(); err != nil { return nil, err }
if len(repoDids) == 0 { return []models.Repo{}, nil }
// get full repo data repos, err := GetRepos(e, orm.FilterIn("repo_did", repoDids)) if err != nil { return nil, err }
// sort repos by the original trending order repoMap := make(map[string]models.Repo) for _, repo := range repos { repoMap[repo.RepoDid] = repo }
orderedRepos := make([]models.Repo, 0, len(repoDids)) for _, did := range repoDids { if repo, exists := repoMap[did]; exists { orderedRepos = append(orderedRepos, repo) } }
return orderedRepos, nil}