Files
2026-11-15 05:47:14 +09:00

635 lines
26 KiB
Go

package store
import (
"context"
"database/sql"
"encoding/json"
"errors"
"net/url"
"strings"
"time"
"unicode/utf8"
"github.com/google/uuid"
)
var ErrTaskInvalid = errors.New("invalid task input")
var ErrTaskConflict = errors.New("task changed; reload before saving")
// TaskRecord is a typed, private organizer record, not a semantic Wiki Object.
// A project uses the same identity/revision envelope with task fields empty.
type TaskRecord struct {
DAVCollection string `json:"dav_collection,omitempty"`
CalDAV *TaskDAVData `json:"caldav,omitempty"`
UUID string `json:"uuid"`
Kind string `json:"kind"`
Title string `json:"title"`
Notes string `json:"notes"`
References []string `json:"references,omitempty"`
NotesSubpath string `json:"notes_subpath,omitempty"`
ProjectUUID string `json:"project_uuid"`
ParentUUID string `json:"parent_uuid,omitempty"`
StartOn string `json:"start_on,omitempty"`
EndOn string `json:"end_on,omitempty"`
Status string `json:"status"`
Bucket string `json:"bucket"`
Priority int `json:"priority"`
Estimate int `json:"estimate_minutes"`
DueOn string `json:"due_on"`
PlannedOn string `json:"planned_on"`
ReviewOn string `json:"review_on"`
AssigneeUUID string `json:"assignee_uuid,omitempty"`
Complexity int `json:"complexity,omitempty"`
PageUUID string `json:"page_uuid"`
Archived bool `json:"archived"`
Version int64 `json:"version"`
CreatedMS int64 `json:"created_at_ms"`
UpdatedMS int64 `json:"updated_at_ms"`
CompletedMS int64 `json:"completed_at_ms"`
NotifyDue bool `json:"notify_due,omitempty"`
NotifyPlanned bool `json:"notify_planned,omitempty"`
ReminderTime string `json:"reminder_time,omitempty"`
ReminderTimezone string `json:"reminder_timezone,omitempty"`
}
type TaskQuery struct {
Filter, Sort, Order string
Kind, View, Project, Search, Today, Before string
Parent string
Hierarchy bool
Limit int
}
func (s *Store) EnsureTasksSchema() error {
if s.ReadOnly {
return ErrReadOnly
}
if s.sqlBackend == storePostgreSQL {
ctx, cancel := context.WithTimeout(context.Background(), 15*time.Second)
defer cancel()
if err := s.ensurePostgreSQLTasksFoundation(ctx); err != nil {
return err
}
return s.ensurePostgreSQLTaskProductivity(ctx)
}
for _, q := range []string{
`CREATE TABLE IF NOT EXISTS organizer_items (
domain_uuid TEXT NOT NULL,owner_uuid TEXT NOT NULL,uuid TEXT NOT NULL,
kind TEXT NOT NULL CHECK(kind IN ('task','project')),project_uuid TEXT NOT NULL,
title TEXT NOT NULL,status TEXT NOT NULL,bucket TEXT NOT NULL,
due_on TEXT NOT NULL,planned_on TEXT NOT NULL,review_on TEXT NOT NULL,
archived INTEGER NOT NULL,version INTEGER NOT NULL,created_at_ms INTEGER NOT NULL,
updated_at_ms INTEGER NOT NULL,data TEXT NOT NULL,document_hash TEXT NOT NULL,
PRIMARY KEY(domain_uuid,uuid))`,
`CREATE INDEX IF NOT EXISTS organizer_list ON organizer_items(domain_uuid,owner_uuid,kind,archived,created_at_ms DESC,uuid DESC)`,
`CREATE INDEX IF NOT EXISTS organizer_project ON organizer_items(domain_uuid,owner_uuid,project_uuid,archived)`,
`CREATE INDEX IF NOT EXISTS organizer_plan ON organizer_items(domain_uuid,owner_uuid,kind,archived,planned_on)`,
`CREATE INDEX IF NOT EXISTS organizer_due ON organizer_items(domain_uuid,owner_uuid,kind,archived,due_on)`,
`CREATE TABLE IF NOT EXISTS organizer_revisions (
domain_uuid TEXT NOT NULL,item_uuid TEXT NOT NULL,version INTEGER NOT NULL,
actor_uuid TEXT NOT NULL,document_hash TEXT NOT NULL,created_at_ms INTEGER NOT NULL,
PRIMARY KEY(domain_uuid,item_uuid,version))`,
`CREATE TABLE IF NOT EXISTS organizer_changes (
sequence INTEGER PRIMARY KEY AUTOINCREMENT,domain_uuid TEXT NOT NULL,item_uuid TEXT NOT NULL,
version INTEGER NOT NULL,actor_uuid TEXT NOT NULL,created_at_ms INTEGER NOT NULL,
UNIQUE(domain_uuid,item_uuid,version))`,
`CREATE TABLE IF NOT EXISTS organizer_activity_bridge(id INTEGER PRIMARY KEY CHECK(id=1),last_sequence INTEGER NOT NULL DEFAULT 0)`,
`INSERT OR IGNORE INTO organizer_activity_bridge(id,last_sequence) VALUES(1,0)`,
`CREATE TABLE IF NOT EXISTS organizer_task_deletions (
domain_uuid TEXT NOT NULL,owner_uuid TEXT NOT NULL,item_uuid TEXT NOT NULL,
dav_name TEXT NOT NULL,dav_uid TEXT NOT NULL,version INTEGER NOT NULL,deleted_at_ms INTEGER NOT NULL,
PRIMARY KEY(domain_uuid,item_uuid))`,
} {
if _, err := s.DB.Exec(q); err != nil {
return err
}
}
if err := s.ensureTaskHierarchySchema(); err != nil {
return err
}
if err := s.ensureTaskDAVSchema(); err != nil {
return err
}
if err := s.ensureTaskReminderSchema(); err != nil {
return err
}
if err := s.ensureTaskBlocksSchema(); err != nil {
return err
}
if err := s.ensureTaskFocusSchema(); err != nil {
return err
}
return s.ensureSchemaComponent("task-productivity")
}
func validTaskDate(v string) bool {
if v == "" {
return true
}
t, err := time.Parse("2006-01-02", v)
return err == nil && t.Year() >= 1 && t.Format("2006-01-02") == v
}
func validateTask(item *TaskRecord) error {
if len(item.References) > 20 {
return ErrTaskInvalid
}
for _, ref := range item.References {
u, err := url.Parse(ref)
if err != nil || len(ref) > 4096 || strings.ContainsAny(ref, "\\\r\n\x00\t ") || u.User != nil || !utf8.ValidString(ref) {
return ErrTaskInvalid
}
if u.Scheme == "http" || u.Scheme == "https" {
if u.Hostname() == "" {
return ErrTaskInvalid
}
} else if u.Scheme != "" || u.Host != "" || !strings.HasPrefix(ref, "/") || strings.HasPrefix(ref, "//") {
return ErrTaskInvalid
}
}
if item.Complexity < 0 || item.Complexity > 5 {
return ErrTaskInvalid
}
if !validTaskNotesSubpath(item.NotesSubpath) || (item.Kind != "project" && item.NotesSubpath != "") {
return ErrTaskInvalid
}
if err := validateTaskHierarchy(item); err != nil {
return err
}
if item.NotifyDue || item.NotifyPlanned {
if item.ReminderTime == "" {
item.ReminderTime = "09:00"
}
if item.Kind != "task" || (item.NotifyDue && item.DueOn == "") || (item.NotifyPlanned && item.PlannedOn == "") {
return ErrTaskInvalid
}
}
if item.ReminderTime != "" {
v, e := time.Parse("15:04", item.ReminderTime)
if e != nil || v.Format("15:04") != item.ReminderTime {
return ErrTaskInvalid
}
}
item.Title = strings.TrimSpace(item.Title)
if id, err := uuid.Parse(item.UUID); err != nil || id.String() != item.UUID {
return ErrTaskInvalid
}
if item.Kind != "task" && item.Kind != "project" {
return ErrTaskInvalid
}
if item.Title == "" || len(item.Title) > 300 || strings.ContainsAny(item.Title, "\r\n\x00") || !utf8.ValidString(item.Title) || !utf8.ValidString(item.Notes) || len(item.Notes) > 16384 || strings.ContainsRune(item.Notes, 0) {
return ErrTaskInvalid
}
if item.Status != "todo" && item.Status != "doing" && item.Status != "done" && item.Status != "cancelled" {
return ErrTaskInvalid
}
if item.Bucket != "inbox" && item.Bucket != "backlog" && item.Bucket != "someday" {
return ErrTaskInvalid
}
if item.Priority < 0 || item.Priority > 3 || item.Estimate < 0 || item.Estimate > 10080 {
return ErrTaskInvalid
}
if !validTaskDate(item.DueOn) || !validTaskDate(item.PlannedOn) || !validTaskDate(item.ReviewOn) {
return ErrTaskInvalid
}
if item.ProjectUUID != "" {
if id, err := uuid.Parse(item.ProjectUUID); err != nil || id.String() != item.ProjectUUID {
return ErrTaskInvalid
}
}
if item.PageUUID != "" {
if id, err := uuid.Parse(item.PageUUID); err != nil || id.String() != item.PageUUID {
return ErrTaskInvalid
}
}
if item.Kind == "project" && (item.ProjectUUID != "" || item.DueOn != "" || item.PlannedOn != "" || item.ReviewOn != "" || item.Estimate != 0) {
return ErrTaskInvalid
}
return nil
}
// The source of authority is checked again in the same writer transaction
// as the conditional head, revision and change-journal commit.
func (s *Store) taskActor(ctx context.Context, q interface {
QueryRowContext(context.Context, string, ...any) *sql.Row
}, domain, actor string, write bool) error {
var allowed bool
campusJoin := ""
if s.sqlBackend == storePostgreSQL {
campusJoin = " JOIN campuses c ON c.uuid=d.campus_uuid AND c.enabled=1"
}
err := q.QueryRowContext(ctx, `SELECT EXISTS(SELECT 1 FROM principals p JOIN domain_memberships m ON m.principal_uuid=p.uuid JOIN domains d ON d.uuid=m.domain_uuid`+campusJoin+` WHERE p.uuid=`+s.sqlParam(1)+` AND m.domain_uuid=`+s.sqlParam(2)+` AND m.state='active' AND d.enabled=1 AND COALESCE(d.placement,'local')='local' AND (p.state='active' OR (`+s.sqlParam(3)+`=0 AND p.state='readonly')))`, actor, domain, boolToInt(write)).Scan(&allowed)
if err != nil {
return err
}
if !allowed {
return ErrAccessDenied
}
return nil
}
func (s *Store) SaveTask(ctx context.Context, domain, actor string, item TaskRecord, expected int64) (TaskRecord, error) {
return s.saveTask(ctx, domain, actor, item, expected, nil)
}
// prepare participates in the same writer transaction as the task head.
func (s *Store) saveTask(ctx context.Context, domain, actor string, item TaskRecord, expected int64, prepare func(*sql.Tx, *TaskRecord, TaskRecord) error) (result TaskRecord, resultErr error) {
defer func() { resultErr = s.sqlError("task repository", resultErr) }()
if s.ReadOnly {
return TaskRecord{}, ErrReadOnly
}
if err := validateTask(&item); err != nil {
return TaskRecord{}, err
}
ctx, cancel := context.WithTimeout(ctx, 5*time.Second)
defer cancel()
tx, err := s.DB.BeginTx(ctx, nil)
if err != nil {
return TaskRecord{}, err
}
defer tx.Rollback()
if err = s.lockTaskWriter(ctx, tx); err != nil {
return TaskRecord{}, err
}
if _, err = tx.ExecContext(ctx, `UPDATE domain_memberships SET state=state WHERE domain_uuid=`+s.sqlParam(1)+` AND principal_uuid=`+s.sqlParam(2)+``, domain, actor); err != nil {
return TaskRecord{}, err
}
if err = s.taskActor(ctx, tx, domain, actor, true); err != nil {
return TaskRecord{}, err
}
var rawConfig string
err = tx.QueryRowContext(ctx, `SELECT value FROM config WHERE domain_uuid=`+s.sqlParam(1)+` AND key='system_extension_states'`, domain).Scan(&rawConfig)
if err != nil && !errors.Is(err, sql.ErrNoRows) {
return TaskRecord{}, err
}
states := map[string]bool{}
if rawConfig != "" && json.Unmarshal([]byte(rawConfig), &states) != nil {
return TaskRecord{}, ErrAccessDenied
}
if enabled, exists := states["organizer.tasks"]; exists && !enabled {
return TaskRecord{}, ErrAccessDenied
}
var deletedOwner string
err = tx.QueryRowContext(ctx, `SELECT owner_uuid FROM organizer_task_deletions WHERE domain_uuid=`+s.sqlParam(1)+` AND item_uuid=`+s.sqlParam(2)+``, domain, item.UUID).Scan(&deletedOwner)
if err == nil {
if deletedOwner != actor {
return TaskRecord{}, sql.ErrNoRows
}
return TaskRecord{}, ErrTaskDeleted
}
if !errors.Is(err, sql.ErrNoRows) {
return TaskRecord{}, err
}
if item.NotifyDue || item.NotifyPlanned {
item.ReminderTimezone = "UTC"
err = tx.QueryRowContext(ctx, `SELECT value FROM config WHERE domain_uuid=`+s.sqlParam(1)+` AND key='timezone'`, domain).Scan(&item.ReminderTimezone)
if err != nil && !errors.Is(err, sql.ErrNoRows) {
return TaskRecord{}, err
}
if _, e := time.LoadLocation(item.ReminderTimezone); e != nil || item.ReminderTimezone == "" {
item.ReminderTimezone = "UTC"
}
}
var oldJSON, owner string
err = tx.QueryRowContext(ctx, `SELECT data,owner_uuid FROM organizer_items WHERE domain_uuid=`+s.sqlParam(1)+` AND uuid=`+s.sqlParam(2)+``, domain, item.UUID).Scan(&oldJSON, &owner)
var old TaskRecord
if err == nil {
if owner != actor {
return TaskRecord{}, sql.ErrNoRows
}
if err = json.Unmarshal([]byte(oldJSON), &old); err != nil {
return TaskRecord{}, err
}
// Older CLI/DAV clients omit this optional field. An explicit [] clears
// references; omission must not erase attachments during unrelated edits.
if expected > 0 && item.References == nil {
item.References = old.References
}
if expected == 0 { // Safe retry of a create with the same client-generated UUID.
candidate := item
candidate.Version, candidate.CreatedMS, candidate.UpdatedMS, candidate.CompletedMS = old.Version, old.CreatedMS, old.UpdatedMS, old.CompletedMS
b, _ := json.Marshal(candidate)
if string(b) == oldJSON {
return old, nil
}
return TaskRecord{}, ErrTaskConflict
}
if old.Version != expected || old.Kind != item.Kind {
return TaskRecord{}, ErrTaskConflict
}
} else if !errors.Is(err, sql.ErrNoRows) {
return TaskRecord{}, err
} else if expected != 0 {
return TaskRecord{}, sql.ErrNoRows
}
if item.ProjectUUID != "" && item.ProjectUUID != old.ProjectUUID {
var exists bool
err = tx.QueryRowContext(ctx, `SELECT EXISTS(SELECT 1 FROM organizer_items WHERE domain_uuid=`+s.sqlParam(1)+` AND owner_uuid=`+s.sqlParam(2)+` AND uuid=`+s.sqlParam(3)+` AND kind='project' AND archived=0)`, domain, actor, item.ProjectUUID).Scan(&exists)
if err != nil {
return TaskRecord{}, err
}
if !exists {
return TaskRecord{}, ErrTaskInvalid
}
}
if err = s.validateTaskParentTx(ctx, tx, domain, actor, item, old); err != nil {
return TaskRecord{}, err
}
if item.AssigneeUUID != old.AssigneeUUID {
if err = s.validateTaskAssignee(ctx, tx, domain, item.AssigneeUUID); err != nil {
return TaskRecord{}, err
}
}
// DAV identity and round-trip metadata are writable only through the DAV
// transaction callback, never through a generic JSON/UI update.
item.CalDAV = old.CalDAV
item.DAVCollection = old.DAVCollection
if prepare != nil {
if err = prepare(tx, &item, old); err != nil {
return TaskRecord{}, err
}
}
if err = s.syncTaskDAVTx(ctx, tx, domain, actor, item); err != nil {
return TaskRecord{}, err
}
if item.PageUUID != "" && item.PageUUID != old.PageUUID {
var exists bool
err = tx.QueryRowContext(ctx, `SELECT EXISTS(SELECT 1 FROM paths WHERE domain_uuid=`+s.sqlParam(1)+` AND object_uuid=`+s.sqlParam(2)+`)`, domain, item.PageUUID).Scan(&exists)
if err != nil {
return TaskRecord{}, err
}
if !exists {
return TaskRecord{}, ErrTaskInvalid
}
}
now := time.Now().UnixMilli()
item.Version = old.Version + 1
item.CreatedMS = old.CreatedMS
item.UpdatedMS = now
if item.CreatedMS == 0 {
item.CreatedMS = now
}
item.CompletedMS = old.CompletedMS
if item.Status == "done" && old.Status != "done" {
item.CompletedMS = now
} else if item.Status != "done" {
item.CompletedMS = 0
}
b, err := json.Marshal(item)
if err != nil {
return TaskRecord{}, err
}
hash := revisionHash(string(b))
if err = s.persistDocument(hash, string(b)); err != nil {
return TaskRecord{}, err
}
_, err = tx.ExecContext(ctx, `INSERT INTO organizer_items(domain_uuid,owner_uuid,uuid,kind,project_uuid,title,status,bucket,due_on,planned_on,review_on,archived,version,created_at_ms,updated_at_ms,data,document_hash) VALUES(`+s.sqlParam(1)+`,`+s.sqlParam(2)+`,`+s.sqlParam(3)+`,`+s.sqlParam(4)+`,`+s.sqlParam(5)+`,`+s.sqlParam(6)+`,`+s.sqlParam(7)+`,`+s.sqlParam(8)+`,`+s.sqlParam(9)+`,`+s.sqlParam(10)+`,`+s.sqlParam(11)+`,`+s.sqlParam(12)+`,`+s.sqlParam(13)+`,`+s.sqlParam(14)+`,`+s.sqlParam(15)+`,`+s.sqlParam(16)+`,`+s.sqlParam(17)+`) ON CONFLICT(domain_uuid,uuid) DO UPDATE SET project_uuid=excluded.project_uuid,title=excluded.title,status=excluded.status,bucket=excluded.bucket,due_on=excluded.due_on,planned_on=excluded.planned_on,review_on=excluded.review_on,archived=excluded.archived,version=excluded.version,updated_at_ms=excluded.updated_at_ms,data=excluded.data,document_hash=excluded.document_hash`, domain, actor, item.UUID, item.Kind, item.ProjectUUID, item.Title, item.Status, item.Bucket, item.DueOn, item.PlannedOn, item.ReviewOn, boolToInt(item.Archived), item.Version, item.CreatedMS, item.UpdatedMS, string(b), hash)
if err != nil {
return TaskRecord{}, err
}
if _, err = tx.ExecContext(ctx, `UPDATE organizer_items SET parent_uuid=`+s.sqlParam(1)+`,start_on=`+s.sqlParam(2)+`,end_on=`+s.sqlParam(3)+` WHERE domain_uuid=`+s.sqlParam(4)+` AND uuid=`+s.sqlParam(5)+``, item.ParentUUID, item.StartOn, item.EndOn, domain, item.UUID); err != nil {
return TaskRecord{}, err
}
_, err = tx.ExecContext(ctx, `INSERT INTO organizer_revisions(domain_uuid,item_uuid,version,actor_uuid,document_hash,created_at_ms) VALUES(`+s.sqlParam(1)+`,`+s.sqlParam(2)+`,`+s.sqlParam(3)+`,`+s.sqlParam(4)+`,`+s.sqlParam(5)+`,`+s.sqlParam(6)+`)`, domain, item.UUID, item.Version, actor, hash, now)
if err != nil {
return TaskRecord{}, err
}
_, err = tx.ExecContext(ctx, `INSERT INTO organizer_changes(domain_uuid,item_uuid,version,actor_uuid,created_at_ms) VALUES(`+s.sqlParam(1)+`,`+s.sqlParam(2)+`,`+s.sqlParam(3)+`,`+s.sqlParam(4)+`,`+s.sqlParam(5)+`)`, domain, item.UUID, item.Version, actor, now)
if err != nil {
return TaskRecord{}, err
}
if _, err = s.appendTaskActivityTx(tx, domain, actor, item.UUID, item.Kind, item.Version, now); err != nil {
return TaskRecord{}, err
}
if err = s.syncTaskRemindersTx(ctx, tx, domain, actor, item); err != nil {
return TaskRecord{}, err
}
if item.Kind == "task" {
if _, err = tx.ExecContext(ctx, `INSERT INTO organizer_task_metrics VALUES(`+s.sqlParam(1)+`,`+s.sqlParam(2)+`,`+s.sqlParam(3)+`,`+s.sqlParam(4)+`,`+s.sqlParam(5)+`,`+s.sqlParam(6)+`) ON CONFLICT(domain_uuid,task_uuid) DO UPDATE SET completed_ms=excluded.completed_ms,estimate=excluded.estimate,complexity=excluded.complexity`, domain, actor, item.UUID, item.CompletedMS, item.Estimate, max(1, item.Complexity)); err != nil {
return TaskRecord{}, err
}
}
if err = tx.Commit(); err != nil {
return TaskRecord{}, err
}
return item, nil
}
func (s *Store) GetTask(ctx context.Context, domain, actor, id string) (result TaskRecord, resultErr error) {
defer func() { resultErr = s.sqlError("task repository", resultErr) }()
if err := s.taskActor(ctx, s.DB, domain, actor, false); err != nil {
return TaskRecord{}, err
}
var data, hash string
err := s.DB.QueryRowContext(ctx, `SELECT data,document_hash FROM organizer_items WHERE domain_uuid=`+s.sqlParam(1)+` AND owner_uuid=`+s.sqlParam(2)+` AND uuid=`+s.sqlParam(3)+``, domain, actor, id).Scan(&data, &hash)
if err != nil {
return TaskRecord{}, err
}
if s.DocumentRoot != "" {
data, err = s.readDocument(hash)
if err != nil {
return TaskRecord{}, err
}
}
if revisionHash(data) != hash {
return TaskRecord{}, ErrDocumentHashMismatch
}
var item TaskRecord
err = json.Unmarshal([]byte(data), &item)
return item, err
}
func (s *Store) ListTasks(ctx context.Context, domain, actor string, query TaskQuery) (result []TaskRecord, cursor string, resultErr error) {
defer func() { resultErr = s.sqlError("task repository", resultErr) }()
if err := s.taskActor(ctx, s.DB, domain, actor, false); err != nil {
return nil, "", err
}
if query.Kind != "task" && query.Kind != "project" {
return nil, "", ErrTaskInvalid
}
if !validTaskDate(query.Today) || query.Today == "" || len(query.Search) > 200 {
return nil, "", ErrTaskInvalid
}
if query.Limit < 1 || query.Limit > 100 {
query.Limit = 60
}
index := query.View == "index"
if index && (query.Kind != "task" || query.Hierarchy || s.taskIndexOrder(query.Sort) == "" || (query.Order != "" && query.Order != "asc" && query.Order != "desc")) {
return nil, "", ErrTaskInvalid
}
if !index && (query.Filter != "" || query.Sort != "" || query.Order != "") {
return nil, "", ErrTaskInvalid
}
q := `SELECT data FROM organizer_items WHERE domain_uuid=` + s.sqlParam(1) + ` AND owner_uuid=` + s.sqlParam(2) + ` AND kind=` + s.sqlParam(3) + ``
args := []any{domain, actor, query.Kind}
hierarchy := query.Hierarchy && query.Kind == "task" && query.View != "family"
if hierarchy {
q = `WITH task_rows AS (
SELECT i.*,COALESCE(p.created_at_ms,i.created_at_ms) AS family_created,
COALESCE(p.uuid,i.uuid) AS family_uuid,CASE WHEN i.parent_uuid='' THEN 1 ELSE 0 END AS family_head
FROM organizer_items i LEFT JOIN organizer_items p
ON p.domain_uuid=i.domain_uuid AND p.owner_uuid=i.owner_uuid AND p.uuid=i.parent_uuid
WHERE i.domain_uuid=` + s.sqlParam(1) + ` AND i.owner_uuid=` + s.sqlParam(2) + `
) SELECT data FROM task_rows WHERE kind=` + s.sqlParam(3) + ``
}
if index {
filter, values, err := s.taskIndexFilter(query.Filter, query.Today, len(args))
if err != nil {
return nil, "", err
}
q += " AND " + filter
args = append(args, values...)
} else if query.View == "family" {
if query.Parent == "" || query.Kind != "task" {
return nil, "", ErrTaskInvalid
}
} else if query.View == "archived" {
q += ` AND archived=1`
} else {
q += ` AND archived=0`
}
if query.Parent != "" {
if _, err := s.GetTask(ctx, domain, actor, query.Parent); err != nil {
return nil, "", err
}
q += ` AND parent_uuid=` + s.sqlParam(len(args)+1) + ``
args = append(args, query.Parent)
}
if query.Project != "" {
q += ` AND project_uuid=` + s.sqlParam(len(args)+1) + ``
project := query.Project
if index && project == "none" {
project = ""
}
args = append(args, project)
}
if query.Kind == "task" {
switch query.View {
case "today":
q += ` AND status NOT IN ('done','cancelled') AND (planned_on=` + s.sqlParam(len(args)+1) + ` OR (due_on!='' AND due_on<=` + s.sqlParam(len(args)+2) + `))`
args = append(args, query.Today, query.Today)
case "day":
q += ` AND status NOT IN ('done','cancelled') AND (planned_on=` + s.sqlParam(len(args)+1) + ` OR due_on=` + s.sqlParam(len(args)+2) + `)`
args = append(args, query.Today, query.Today)
case "carryover":
q += ` AND status NOT IN ('done','cancelled') AND ((planned_on!='' AND planned_on<` + s.sqlParam(len(args)+1) + `) OR (due_on!='' AND due_on<` + s.sqlParam(len(args)+2) + `))`
args = append(args, query.Today, query.Today)
case "inbox", "backlog", "someday":
q += ` AND status NOT IN ('done','cancelled') AND bucket=` + s.sqlParam(len(args)+1) + ``
args = append(args, query.View)
case "done":
q += ` AND status IN ('done','cancelled')`
case "review":
q += ` AND status NOT IN ('done','cancelled') AND ((review_on!='' AND review_on<=` + s.sqlParam(len(args)+1) + `) OR updated_at_ms<` + s.sqlParam(len(args)+2) + `)`
args = append(args, query.Today, time.Now().AddDate(0, 0, -30).UnixMilli())
case "all":
q += ` AND status NOT IN ('done','cancelled')`
case "archived":
case "family", "index":
default:
return nil, "", ErrTaskInvalid
}
}
if query.Search != "" {
q += " AND " + s.taskTitleContains(s.sqlParam(len(args)+1))
args = append(args, query.Search)
}
if query.Before != "" {
if index {
comparison := ">"
if query.Order == "desc" {
comparison = "<"
}
key := s.taskIndexOrder(query.Sort)
q += " AND (" + key + ",uuid)" + comparison + "(SELECT " + key + ",uuid FROM organizer_items WHERE domain_uuid=" + s.sqlParam(len(args)+1) + " AND owner_uuid=" + s.sqlParam(len(args)+2) + " AND kind='task' AND uuid=" + s.sqlParam(len(args)+3) + ")"
args = append(args, domain, actor, query.Before)
} else if hierarchy {
// A separate indexed cursor lookup keeps the wide task_rows CTE
// single-use, avoiding materialization of every private JSON body.
var familyCreated, created int64
var familyUUID, id string
var head int
err := s.DB.QueryRowContext(ctx, `SELECT COALESCE(p.created_at_ms,i.created_at_ms),COALESCE(p.uuid,i.uuid),CASE WHEN i.parent_uuid='' THEN 1 ELSE 0 END,i.created_at_ms,i.uuid
FROM organizer_items i LEFT JOIN organizer_items p ON p.domain_uuid=i.domain_uuid AND p.owner_uuid=i.owner_uuid AND p.uuid=i.parent_uuid
WHERE i.domain_uuid=`+s.sqlParam(1)+` AND i.owner_uuid=`+s.sqlParam(2)+` AND i.uuid=`+s.sqlParam(3)+``, domain, actor, query.Before).Scan(&familyCreated, &familyUUID, &head, &created, &id)
if errors.Is(err, sql.ErrNoRows) {
return []TaskRecord{}, "", nil
}
if err != nil {
return nil, "", err
}
q += ` AND (family_created,family_uuid,family_head,created_at_ms,uuid)<(` + s.sqlParam(len(args)+1) + `,` + s.sqlParam(len(args)+2) + `,` + s.sqlParam(len(args)+3) + `,` + s.sqlParam(len(args)+4) + `,` + s.sqlParam(len(args)+5) + `)`
args = append(args, familyCreated, familyUUID, head, created, id)
} else {
q += ` AND (created_at_ms,uuid)<(SELECT created_at_ms,uuid FROM organizer_items WHERE domain_uuid=` + s.sqlParam(len(args)+1) + ` AND owner_uuid=` + s.sqlParam(len(args)+2) + ` AND uuid=` + s.sqlParam(len(args)+3) + `)`
args = append(args, domain, actor, query.Before)
}
}
if index {
direction := "ASC"
if query.Order == "desc" {
direction = "DESC"
}
q += " ORDER BY " + s.taskIndexOrder(query.Sort) + " " + direction + ",uuid " + direction + " LIMIT " + s.sqlParam(len(args)+1)
} else if hierarchy {
q += ` ORDER BY family_created DESC,family_uuid DESC,family_head DESC,created_at_ms DESC,uuid DESC LIMIT ` + s.sqlParam(len(args)+1) + ``
} else {
q += ` ORDER BY created_at_ms DESC,uuid DESC LIMIT ` + s.sqlParam(len(args)+1) + ``
}
args = append(args, query.Limit+1)
rows, err := s.DB.QueryContext(ctx, q, args...)
if err != nil {
return nil, "", err
}
defer rows.Close()
items := []TaskRecord{}
for rows.Next() {
var raw string
var item TaskRecord
if err = rows.Scan(&raw); err != nil {
return nil, "", err
}
if err = json.Unmarshal([]byte(raw), &item); err != nil {
return nil, "", err
}
item.Notes = ""
item.CalDAV = nil
items = append(items, item)
}
if err = rows.Err(); err != nil {
return nil, "", err
}
next := ""
if len(items) > query.Limit {
items = items[:query.Limit]
next = items[len(items)-1].UUID
}
return items, next, nil
}
type TaskRevision struct {
Version int64 `json:"version"`
CreatedMS int64 `json:"created_at_ms"`
}
func (s *Store) TaskHistory(ctx context.Context, domain, actor, id string) (result []TaskRevision, resultErr error) {
defer func() { resultErr = s.sqlError("task repository", resultErr) }()
if _, err := s.GetTask(ctx, domain, actor, id); err != nil {
return nil, err
}
rows, err := s.DB.QueryContext(ctx, `SELECT version,created_at_ms FROM organizer_revisions WHERE domain_uuid=`+s.sqlParam(1)+` AND item_uuid=`+s.sqlParam(2)+` ORDER BY version DESC LIMIT 50`, domain, id)
if err != nil {
return nil, err
}
defer rows.Close()
items := []TaskRevision{}
for rows.Next() {
var v TaskRevision
if err = rows.Scan(&v.Version, &v.CreatedMS); err != nil {
return nil, err
}
items = append(items, v)
}
return items, rows.Err()
}