Something went wrong. Try again.
Monorepo for Tangled
Something went wrong. Try again.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606package db
import ( "database/sql" "fmt" "maps" "slices" "sort" "strings" "time"
"github.com/bluesky-social/indigo/atproto/syntax" "tangled.org/core/api/tangled" "tangled.org/core/appview/models" "tangled.org/core/appview/pagination" "tangled.org/core/orm")
func PutIssue(tx *sql.Tx, issue *models.Issue) error { // ensure sequence exists _, err := tx.Exec(` insert or ignore into repo_issue_seqs (repo_at, next_issue_id) values (?, 1) `, issue.RepoAt) if err != nil { return err }
issues, err := GetIssues( tx, orm.FilterEq("did", issue.Did), orm.FilterEq("rkey", issue.Rkey), ) switch { case err != nil: return err case len(issues) == 0: return createNewIssue(tx, issue) case len(issues) != 1: // should be unreachable return fmt.Errorf("invalid number of issues returned: %d", len(issues)) default: // if content is identical, do not edit existingIssue := issues[0] if existingIssue.Title == issue.Title && existingIssue.Body == issue.Body { return nil }
issue.Id = existingIssue.Id issue.IssueId = existingIssue.IssueId return updateIssue(tx, issue) }}
func createNewIssue(tx *sql.Tx, issue *models.Issue) error { // get next issue_id var newIssueId int err := tx.QueryRow(` update repo_issue_seqs set next_issue_id = next_issue_id + 1 where repo_at = ? returning next_issue_id - 1 `, issue.RepoAt).Scan(&newIssueId) if err != nil { return err }
// insert new issue row := tx.QueryRow(` insert into issues (repo_at, did, rkey, issue_id, title, body) values (?, ?, ?, ?, ?, ?) returning rowid, issue_id `, issue.RepoAt, issue.Did, issue.Rkey, newIssueId, issue.Title, issue.Body)
err = row.Scan(&issue.Id, &issue.IssueId) if err != nil { return fmt.Errorf("scan row: %w", err) }
if err := putReferences(tx, issue.AtUri(), issue.References); err != nil { return fmt.Errorf("put reference_links: %w", err) } return nil}
func updateIssue(tx *sql.Tx, issue *models.Issue) error { // update existing issue _, err := tx.Exec(` update issues set title = ?, body = ?, edited = ? where did = ? and rkey = ? `, issue.Title, issue.Body, time.Now().Format(time.RFC3339), issue.Did, issue.Rkey) if err != nil { return err }
if err := putReferences(tx, issue.AtUri(), issue.References); err != nil { return fmt.Errorf("put reference_links: %w", err) } return nil}
func GetIssuesPaginated(e Execer, page pagination.Page, filters ...orm.Filter) ([]models.Issue, error) { issueMap := make(map[string]*models.Issue) // at-uri -> issue
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 ") }
pLower := orm.FilterGte("row_num", page.Offset+1) pUpper := orm.FilterLte("row_num", page.Offset+page.Limit)
pageClause := "" if page.Limit > 0 { args = append(args, pLower.Arg()...) args = append(args, pUpper.Arg()...) pageClause = " where " + pLower.Condition() + " and " + pUpper.Condition() }
query := fmt.Sprintf( ` select * from ( select id, did, rkey, repo_at, issue_id, title, body, open, created, edited, deleted, row_number() over (order by created desc) as row_num from issues %s ) ranked_issues %s `, whereClause, pageClause, )
rows, err := e.Query(query, args...) if err != nil { return nil, fmt.Errorf("failed to query issues table: %w", err) } defer rows.Close()
for rows.Next() { var issue models.Issue var createdAt string var editedAt, deletedAt sql.Null[string] var rowNum int64 err := rows.Scan( &issue.Id, &issue.Did, &issue.Rkey, &issue.RepoAt, &issue.IssueId, &issue.Title, &issue.Body, &issue.Open, &createdAt, &editedAt, &deletedAt, &rowNum, ) if err != nil { return nil, fmt.Errorf("failed to scan issue: %w", err) }
if t, err := time.Parse(time.RFC3339, createdAt); err == nil { issue.Created = t }
if editedAt.Valid { if t, err := time.Parse(time.RFC3339, editedAt.V); err == nil { issue.Edited = &t } }
if deletedAt.Valid { if t, err := time.Parse(time.RFC3339, deletedAt.V); err == nil { issue.Deleted = &t } }
atUri := issue.AtUri().String() issueMap[atUri] = &issue }
// collect reverse repos repoAts := make([]string, 0, len(issueMap)) // or just []string{} for _, issue := range issueMap { repoAts = append(repoAts, string(issue.RepoAt)) }
repos, err := GetRepos(e, 0, orm.FilterIn("at_uri", repoAts)) if err != nil { return nil, fmt.Errorf("failed to build repo mappings: %w", err) }
repoMap := make(map[string]*models.Repo) for i := range repos { repoMap[string(repos[i].RepoAt())] = &repos[i] }
for issueAt, i := range issueMap { if r, ok := repoMap[string(i.RepoAt)]; ok { i.Repo = r } else { // do not show up the issue if the repo is deleted // TODO: foreign key where? delete(issueMap, issueAt) } }
// collect comments issueAts := slices.Collect(maps.Keys(issueMap))
comments, err := GetIssueComments(e, orm.FilterIn("issue_at", issueAts)) if err != nil { return nil, fmt.Errorf("failed to query comments: %w", err) } for i := range comments { issueAt := comments[i].IssueAt if issue, ok := issueMap[issueAt]; ok { issue.Comments = append(issue.Comments, comments[i]) } }
// collect allLabels for each issue allLabels, err := GetLabels(e, orm.FilterIn("subject", issueAts)) if err != nil { return nil, fmt.Errorf("failed to query labels: %w", err) } for issueAt, labels := range allLabels { if issue, ok := issueMap[issueAt.String()]; ok { issue.Labels = labels } }
// collect references for each issue allReferencs, err := GetReferencesAll(e, orm.FilterIn("from_at", issueAts)) if err != nil { return nil, fmt.Errorf("failed to query reference_links: %w", err) } for issueAt, references := range allReferencs { if issue, ok := issueMap[issueAt.String()]; ok { issue.References = references } }
var issues []models.Issue for _, i := range issueMap { issues = append(issues, *i) }
sort.Slice(issues, func(i, j int) bool { return issues[i].Created.After(issues[j].Created) })
return issues, nil}
func GetIssue(e Execer, repoAt syntax.ATURI, issueId int) (*models.Issue, error) { issues, err := GetIssuesPaginated( e, pagination.Page{}, orm.FilterEq("repo_at", repoAt), orm.FilterEq("issue_id", issueId), ) if err != nil { return nil, err } if len(issues) != 1 { return nil, sql.ErrNoRows }
return &issues[0], nil}
func GetIssues(e Execer, filters ...orm.Filter) ([]models.Issue, error) { return GetIssuesPaginated(e, pagination.Page{}, filters...)}
// GetIssueIDs gets list of all existing issue's IDsfunc GetIssueIDs(e Execer, opts models.IssueSearchOptions) ([]int64, error) { var ids []int64
var filters []orm.Filter openValue := 0 if opts.IsOpen { openValue = 1 } filters = append(filters, orm.FilterEq("open", openValue)) if opts.RepoAt != "" { filters = append(filters, orm.FilterEq("repo_at", opts.RepoAt)) }
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 ") } query := fmt.Sprintf( ` select id from issues %s limit ? offset ?`, whereClause, ) args = append(args, opts.Page.Limit, opts.Page.Offset) rows, err := e.Query(query, args...) if err != nil { return nil, err } defer rows.Close()
for rows.Next() { var id int64 err := rows.Scan(&id) if err != nil { return nil, err }
ids = append(ids, id) }
return ids, nil}
func AddIssueComment(tx *sql.Tx, c models.IssueComment) (int64, error) { result, err := tx.Exec( `insert into issue_comments ( did, rkey, issue_at, body, reply_to, created, edited ) values (?, ?, ?, ?, ?, ?, null) on conflict(did, rkey) do update set issue_at = excluded.issue_at, body = excluded.body, edited = case when issue_comments.issue_at != excluded.issue_at or issue_comments.body != excluded.body or issue_comments.reply_to != excluded.reply_to then ? else issue_comments.edited end`, c.Did, c.Rkey, c.IssueAt, c.Body, c.ReplyTo, c.Created.Format(time.RFC3339), time.Now().Format(time.RFC3339), ) if err != nil { return 0, err }
id, err := result.LastInsertId() if err != nil { return 0, err }
if err := putReferences(tx, c.AtUri(), c.References); err != nil { return 0, fmt.Errorf("put reference_links: %w", err) }
return id, nil}
func DeleteIssueComments(e Execer, filters ...orm.Filter) 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 ") }
query := fmt.Sprintf(`update issue_comments set body = "", deleted = strftime('%%Y-%%m-%%dT%%H:%%M:%%SZ', 'now') %s`, whereClause)
_, err := e.Exec(query, args...) return err}
func GetIssueComments(e Execer, filters ...orm.Filter) ([]models.IssueComment, error) { commentMap := make(map[string]*models.IssueComment)
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 ") }
query := fmt.Sprintf(` select id, did, rkey, issue_at, reply_to, body, created, edited, deleted from issue_comments %s `, whereClause)
rows, err := e.Query(query, args...) if err != nil { return nil, err } defer rows.Close()
for rows.Next() { var comment models.IssueComment var created string var rkey, edited, deleted, replyTo sql.Null[string] err := rows.Scan( &comment.Id, &comment.Did, &rkey, &comment.IssueAt, &replyTo, &comment.Body, &created, &edited, &deleted, ) if err != nil { return nil, err }
// this is a remnant from old times, newer comments always have rkey if rkey.Valid { comment.Rkey = rkey.V }
if t, err := time.Parse(time.RFC3339, created); err == nil { comment.Created = t }
if edited.Valid { if t, err := time.Parse(time.RFC3339, edited.V); err == nil { comment.Edited = &t } }
if deleted.Valid { if t, err := time.Parse(time.RFC3339, deleted.V); err == nil { comment.Deleted = &t } }
if replyTo.Valid { comment.ReplyTo = &replyTo.V }
atUri := comment.AtUri().String() commentMap[atUri] = &comment }
if err = rows.Err(); err != nil { return nil, err }
// collect references for each comments commentAts := slices.Collect(maps.Keys(commentMap)) allReferencs, err := GetReferencesAll(e, orm.FilterIn("from_at", commentAts)) if err != nil { return nil, fmt.Errorf("failed to query reference_links: %w", err) } for commentAt, references := range allReferencs { if comment, ok := commentMap[commentAt.String()]; ok { comment.References = references } }
var comments []models.IssueComment for _, c := range commentMap { comments = append(comments, *c) }
sort.Slice(comments, func(i, j int) bool { return comments[i].Created.After(comments[j].Created) })
return comments, nil}
func DeleteIssues(tx *sql.Tx, did, rkey string) error { _, err := tx.Exec( `delete from issues where did = ? and rkey = ?`, did, rkey, ) if err != nil { return fmt.Errorf("delete issue: %w", err) }
uri := syntax.ATURI(fmt.Sprintf("at://%s/%s/%s", did, tangled.RepoIssueNSID, rkey)) err = deleteReferences(tx, uri) if err != nil { return fmt.Errorf("delete reference_links: %w", err) }
return nil}
func CloseIssues(e Execer, filters ...orm.Filter) 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 ") }
query := fmt.Sprintf(`update issues set open = 0 %s`, whereClause) _, err := e.Exec(query, args...) return err}
func ReopenIssues(e Execer, filters ...orm.Filter) 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 ") }
query := fmt.Sprintf(`update issues set open = 1 %s`, whereClause) _, err := e.Exec(query, args...) return err}
func GetIssueCount(e Execer, repoAt syntax.ATURI) (models.IssueCount, error) { row := e.QueryRow(` select count(case when open = 1 then 1 end) as open_count, count(case when open = 0 then 1 end) as closed_count from issues where repo_at = ?`, repoAt, )
var count models.IssueCount if err := row.Scan(&count.Open, &count.Closed); err != nil { return models.IssueCount{}, err }
return count, nil}