85 lines
2.7 KiB
Go
85 lines
2.7 KiB
Go
package db
|
|
|
|
import "database/sql"
|
|
import "strings"
|
|
|
|
import "codit/internal/models"
|
|
|
|
const projectSummaryRecentLimit = 10
|
|
|
|
// GetProjectSummary returns aggregate counts plus a few recent repos and boards
|
|
// for a project's dashboard, in one round trip of cheap queries.
|
|
func (s *Store) GetProjectSummary(projectID string) (models.ProjectSummary, error) {
|
|
var summary models.ProjectSummary
|
|
var pid int64
|
|
var err error
|
|
var rows *sql.Rows
|
|
|
|
err = s.QueryRow(`SELECT id FROM projects WHERE public_id = ?`, strings.TrimSpace(projectID)).Scan(&pid)
|
|
if err != nil {
|
|
return summary, err
|
|
}
|
|
|
|
// Repos = local repos plus foreign repos attached to this project (matching
|
|
// the repo list). Members counts user role bindings (group roles are listed
|
|
// separately on the page).
|
|
err = s.QueryRow(`SELECT
|
|
(SELECT COUNT(*) FROM repos WHERE project_id = ?)
|
|
+ (SELECT COUNT(*) FROM project_repos pr JOIN repos r ON r.id = pr.repo_id WHERE pr.project_id = ? AND r.project_id <> ?),
|
|
(SELECT COUNT(*) FROM boards WHERE project_id = ? AND delete_at = 0),
|
|
(SELECT COUNT(*) FROM issues WHERE project_id = ?),
|
|
(SELECT COUNT(*) FROM wiki_pages WHERE project_id = ?),
|
|
(SELECT COUNT(*) FROM project_role_bindings WHERE project_id = ? AND subject_type = 'user')`,
|
|
pid, pid, pid, pid, pid, pid, pid).Scan(
|
|
&summary.Counts.Repos, &summary.Counts.Boards, &summary.Counts.Issues, &summary.Counts.Wiki, &summary.Counts.Members)
|
|
if err != nil {
|
|
return summary, err
|
|
}
|
|
|
|
rows, err = s.Query(`SELECT public_id, name, type, created_at
|
|
FROM (
|
|
SELECT r.public_id, r.name, r.type, r.created_at
|
|
FROM repos r
|
|
WHERE r.project_id = ?
|
|
UNION ALL
|
|
SELECT r.public_id, r.name, r.type, r.created_at
|
|
FROM project_repos pr
|
|
JOIN repos r ON r.id = pr.repo_id
|
|
WHERE pr.project_id = ? AND r.project_id <> ?
|
|
)
|
|
ORDER BY created_at DESC, name LIMIT ?`, pid, pid, pid, projectSummaryRecentLimit)
|
|
if err != nil {
|
|
return summary, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
var item models.ProjectSummaryRepo
|
|
var createdAt int64
|
|
err = rows.Scan(&item.ID, &item.Name, &item.Type, &createdAt)
|
|
if err != nil {
|
|
return summary, err
|
|
}
|
|
summary.RecentRepos = append(summary.RecentRepos, item)
|
|
}
|
|
err = rows.Err()
|
|
if err != nil {
|
|
return summary, err
|
|
}
|
|
|
|
rows, err = s.Query(`SELECT public_id, title FROM boards
|
|
WHERE project_id = ? AND delete_at = 0 ORDER BY created_at DESC, title LIMIT ?`, pid, projectSummaryRecentLimit)
|
|
if err != nil {
|
|
return summary, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
var item models.ProjectSummaryBoard
|
|
err = rows.Scan(&item.ID, &item.Name)
|
|
if err != nil {
|
|
return summary, err
|
|
}
|
|
summary.RecentBoards = append(summary.RecentBoards, item)
|
|
}
|
|
return summary, rows.Err()
|
|
}
|