Spaces:
Running on CPU Upgrade
Running on CPU Upgrade
Download platform/core/amazon_project_tasks.py from fsanyoto/runloopable: direct link, hf CLI and curl.
- Browser
- Download file 37.1 kB
-
https://huggingface.co/spaces/fsanyoto/runloopable/resolve/main/platform/core/amazon_project_tasks.py
- Command line
-
hf download hf://spaces/fsanyoto/runloopable/platform/core/amazon_project_tasks.py
-
curl -L -o amazon_project_tasks.py https://huggingface.co/spaces/fsanyoto/runloopable/resolve/main/platform/core/amazon_project_tasks.py
37.1 kB
| """Tenant-bound task trees and leaf progress for Amazon projects.""" | |
| from collections import defaultdict, deque | |
| from contextlib import contextmanager | |
| from datetime import date, timedelta | |
| from decimal import Decimal, InvalidOperation, localcontext | |
| import json | |
| import uuid | |
| from core import amazon_projects as projects | |
| TASKS = 'amazon_project_tasks' | |
| DEPENDENCIES = 'amazon_project_dependencies' | |
| FILTERS = 'amazon_project_task_filters' | |
| TABLES = (TASKS, DEPENDENCIES, FILTERS) | |
| TASK_COLUMNS = ('id', 'project_id', 'parent_id', 'position', 'name', 'assignee', 'start_on', | |
| 'due_on', 'done_at', 'contingency_for', 'active', 'created_by') | |
| DEPENDENCY_COLUMNS = ('id', 'project_id', 'from_task', 'to_task', 'type', 'lag_days') | |
| DEPENDENCY_ENDPOINTS = {'FS': ('due_on', 'start_on'), 'SS': ('start_on', 'start_on'), | |
| 'FF': ('due_on', 'due_on'), 'SF': ('start_on', 'due_on')} | |
| TARGET_FIELDS = ('targets', 'target_filter', 'completion_filter') | |
| FILTER_STATE = ('filter_status', 'filter_closed_at', 'closed_targets', 'filter_missing') | |
| EDITABLE = frozenset(('name', 'assignee', 'start_on', 'due_on', 'done', 'contingency_for', *TARGET_FIELDS)) | |
| _UNCHANGED = object() | |
| def _tree(value, *, empty=True): | |
| nodes = value if isinstance(value, list) else value.get('nodes') if isinstance(value, dict) else None | |
| if not isinstance(nodes, list) or (not empty and not nodes): | |
| raise ValueError('Send a saved filter tree with conditions.') | |
| try: | |
| return json.loads(json.dumps(value, allow_nan=False)) | |
| except (TypeError, ValueError): | |
| raise ValueError('Send a saved filter tree with JSON values.') from None | |
| def _target_fields(current): | |
| values = current.get('targets') | |
| if values is not None: | |
| if not isinstance(values, list): | |
| raise ValueError('Send targets as a list of grain and key pairs.') | |
| pairs, seen = [], set() | |
| for value in values: | |
| if not isinstance(value, dict) or set(value) != {'grain', 'key'}: | |
| raise ValueError('Send each task target as a grain and key.') | |
| pair = projects.target(value['grain'], value['key']) | |
| if pair[0] not in ('sku', 'asin'): | |
| raise ValueError('Task targets use sku or asin.') | |
| if pair not in seen: | |
| seen.add(pair) | |
| pairs.append(pair) | |
| current['targets'] = projects._pair_list(pairs) | |
| saved = current.get('target_filter') | |
| if saved is not None: | |
| if not isinstance(saved, dict) or set(saved) != {'grain', 'filter'} or saved['grain'] not in ('sku', 'asin'): | |
| raise ValueError('Send targetFilter as a sku or asin grain and filter tree.') | |
| current['target_filter'] = dict(grain=saved['grain'], filter=_tree(saved['filter'])) | |
| if current.get('targets') and saved is not None: | |
| raise ValueError('Send either a target list or one saved target filter.') | |
| if current.get('completion_filter') is not None: | |
| current['completion_filter'] = _tree(current['completion_filter'], empty=False) | |
| if not current.get('targets') and saved is None: | |
| raise ValueError('A completion filter needs task targets.') | |
| return current | |
| def _position(value): | |
| try: | |
| number = Decimal(str(value)) | |
| except (InvalidOperation, ValueError): | |
| raise ValueError('position must be a finite number.') from None | |
| if isinstance(value, bool) or not number.is_finite(): | |
| raise ValueError('position must be a finite number.') | |
| return number | |
| def _fields(values, previous=None): | |
| current = dict(previous or {}) | |
| current.update(values) | |
| current['name'] = projects._text(current.get('name'), 'name') | |
| current['assignee'] = projects._text(current.get('assignee'), 'assignee', optional=True) | |
| for field in ('start_on', 'due_on'): | |
| current[field] = projects._date(current.get(field), field) | |
| if current['start_on'] and current['due_on'] and current['due_on'] < current['start_on']: | |
| raise ValueError('due_on must be on or after start_on.') | |
| current['contingency_for'] = projects._text(current.get('contingency_for'), 'contingency_for', optional=True) | |
| if 'done' in values: | |
| if type(values['done']) is not bool: | |
| raise ValueError('done must be true or false.') | |
| current['done_at'] = (current.get('done_at') or projects._now()) if values['done'] else None | |
| else: | |
| current.setdefault('done_at', None) | |
| current.pop('done', None) | |
| return _target_fields(current) | |
| def _record(values): | |
| row = dict(zip(TASK_COLUMNS, values)) | |
| row['position'] = str(_position(row['position'])) | |
| row['active'] = bool(row['active']) | |
| row['done'] = row['done_at'] is not None | |
| for field in ('start_on', 'due_on'): | |
| row[field] = projects._date(row[field], field) | |
| return row | |
| def _check_parent(rows, task_id, parent_id): | |
| """Walk ancestors without a Python call stack or a fixed depth ceiling.""" | |
| visited = {task_id} | |
| while parent_id is not None: | |
| if parent_id in visited: | |
| raise ValueError('A task cannot move under itself or its descendant.') | |
| if parent_id not in rows: | |
| raise ValueError('The parent task must belong to this project.') | |
| visited.add(parent_id) | |
| parent_id = rows[parent_id]['parent_id'] | |
| def _check_contingency(rows, task_id, contingency_for): | |
| if contingency_for is not None and (contingency_for == task_id or contingency_for not in rows): | |
| raise ValueError('The backup task must name another task in this project.') | |
| def _dependency_fields(kind, lag_days): | |
| if not isinstance(kind, str) or kind not in DEPENDENCY_ENDPOINTS: | |
| raise ValueError('Dependency type must be FS, SS, FF or SF.') | |
| if type(lag_days) is not int or not -2147483648 <= lag_days <= 2147483647: | |
| raise ValueError('lag_days must be an integer number of days.') | |
| return kind, lag_days | |
| def _check_dependency(rows, dependencies, from_task, to_task): | |
| if from_task not in rows or to_task not in rows: | |
| raise ValueError('Both dependency tasks must belong to this project.') | |
| if from_task == to_task: | |
| raise ValueError('A task cannot depend on itself.') | |
| outgoing = defaultdict(list) | |
| for dependency in dependencies: | |
| outgoing[dependency['from_task']].append(dependency['to_task']) | |
| pending, seen = [to_task], set() | |
| while pending: | |
| task_id = pending.pop() | |
| if task_id == from_task: | |
| source, target = rows[from_task], rows[to_task] | |
| raise ValueError(f'Dependency cycle between "{source["name"]}" ({from_task}) ' | |
| f'and "{target["name"]}" ({to_task}).') | |
| if task_id not in seen: | |
| seen.add(task_id) | |
| pending.extend(outgoing[task_id]) | |
| def _constraint_gap(rows, dependency): | |
| source, target = rows[dependency['from_task']], rows[dependency['to_task']] | |
| source_field, target_field = DEPENDENCY_ENDPOINTS[dependency['type']] | |
| if source[source_field] is None or target[target_field] is None: | |
| raise ValueError(f'Dependency "{source["name"]}" to "{target["name"]}" ' | |
| f'requires predecessor {source_field} and successor {target_field}.') | |
| return ((date.fromisoformat(source[source_field]) - date.fromisoformat(target[target_field])).days | |
| + dependency['lag_days']) | |
| def _propagate(rows, dependencies, task_id): | |
| """Shift each affected task once after all its predecessors, preserving its duration.""" | |
| incoming, outgoing, indegree = defaultdict(list), defaultdict(list), dict.fromkeys(rows, 0) | |
| for dependency in dependencies: | |
| incoming[dependency['to_task']].append(dependency) | |
| outgoing[dependency['from_task']].append(dependency['to_task']) | |
| indegree[dependency['to_task']] += 1 | |
| pending, affected = [task_id], set() | |
| while pending: | |
| current = pending.pop() | |
| if current not in affected: | |
| affected.add(current) | |
| pending.extend(outgoing[current]) | |
| ready, shifted, visited = deque(key for key, count in indegree.items() if count == 0), [], 0 | |
| while ready: | |
| current = ready.popleft() | |
| visited += 1 | |
| if current in affected: | |
| days = max([0] + [_constraint_gap(rows, dependency) for dependency in incoming[current]]) | |
| if days: | |
| changed = dict(rows[current]) | |
| try: | |
| for field in ('start_on', 'due_on'): | |
| if changed[field] is not None: | |
| changed[field] = (date.fromisoformat(changed[field]) + timedelta(days=days)).isoformat() | |
| except OverflowError: | |
| raise ValueError(f'Dependency shift exceeds the calendar for task "{changed["name"]}".') from None | |
| rows[current] = changed | |
| shifted.append(changed) | |
| for successor in outgoing[current]: | |
| indegree[successor] -= 1 | |
| if indegree[successor] == 0: | |
| ready.append(successor) | |
| if visited != len(rows): | |
| raise ValueError('The stored task dependencies contain a cycle.') | |
| return shifted | |
| def _subtree(rows, task_id): | |
| children = defaultdict(list) | |
| for row in rows.values(): | |
| children[row['parent_id']].append(row['id']) | |
| pending, found = [task_id], [] | |
| seen = set() | |
| while pending: | |
| current = pending.pop() | |
| if current not in seen: | |
| seen.add(current) | |
| found.append(current) | |
| pending.extend(children[current]) | |
| return found | |
| def _leaf_progress(rows, fallback): | |
| """Count completed active leaves, excluding every dormant backup subtree.""" | |
| children = defaultdict(list) | |
| for row in rows: | |
| children[row['parent_id']].append(row) | |
| pending, leaves = list(children[None]), [] | |
| while pending: | |
| row = pending.pop() | |
| if not row['active']: | |
| continue | |
| active_children = [child for child in children[row['id']] if child['active']] | |
| if active_children: | |
| pending.extend(active_children) | |
| else: | |
| leaves.append(row) | |
| done, total = sum(row['done'] for row in leaves), len(leaves) | |
| return {'done': done, 'total': total, | |
| 'progress': round(100 * done / total) if total else (0 if rows else fallback)} | |
| def _backend(rt): | |
| return PgTaskStore(projects._backend(rt)) | |
| def list_tasks(rt, project_id, *, matcher=None, today=None, by=None): | |
| """Read every task as flat rows with parent ids and exact fractional positions.""" | |
| backend = _backend(rt) | |
| project_id = projects._text(project_id, 'project_id') | |
| if matcher is not None: | |
| _evaluate_project(rt, backend, project_id, matcher, today=today, by=by) | |
| return backend.list_tasks(project_id) | |
| def get(rt, project_id, task_id): | |
| """Read one task within its tenant and project, or raise KeyError.""" | |
| task_id = projects._text(task_id, 'task_id') | |
| for row in list_tasks(rt, project_id): | |
| if row['id'] == task_id: | |
| return row | |
| raise KeyError('Task not found in this project.') | |
| def progress(rt, project_id): | |
| """Return completed leaf counts and their percentage, retaining manual progress without tasks.""" | |
| project_id = projects._text(project_id, 'project_id') | |
| return _backend(rt).progress(project_id) | |
| def _mutate(rt, project_id, task_id, action, values, by): | |
| project_id = projects._text(project_id, 'project_id') | |
| task_id = projects._text(task_id, 'task_id') | |
| by = projects._text(by, 'by') | |
| before, after, result = _backend(rt).mutate(project_id, task_id, action, values, by) | |
| projects._log(rt, project_id, by, 'tasks', before, after) | |
| return result | |
| def create(rt, project_id, *, name, by, parent_id=None, position=None, assignee=None, | |
| start_on=None, due_on=None, done=False, contingency_for=None, created_by=None, | |
| targets=None, target_filter=None, completion_filter=None): | |
| """Create one task with at most one assignee and an optional dormant backup link.""" | |
| values = _fields(dict(name=name, assignee=assignee, start_on=start_on, due_on=due_on, | |
| done=done, contingency_for=contingency_for)) | |
| if targets is not None or target_filter is not None or completion_filter is not None: | |
| values.update(targets=targets, target_filter=target_filter, completion_filter=completion_filter) | |
| _target_fields(values) | |
| values.update(parent_id=projects._text(parent_id, 'parent_id', optional=True), | |
| position=None if position is None else str(_position(position)), | |
| created_by=projects._text(created_by or by, 'created_by')) | |
| return _mutate(rt, project_id, uuid.uuid4().hex, 'create', values, by) | |
| def update(rt, project_id, task_id, changes, *, by): | |
| """Change task fields without allowing a second tree or activation write door.""" | |
| if not isinstance(changes, dict) or not changes or set(changes) - EDITABLE: | |
| raise ValueError(f'Update only: {", ".join(sorted(EDITABLE))}.') | |
| return _mutate(rt, project_id, task_id, 'update', dict(changes), by) | |
| def move(rt, project_id, task_id, *, by, parent_id=_UNCHANGED, position=None): | |
| """Reparent or reorder one task after checking the full ancestor chain.""" | |
| values = dict(position=None if position is None else str(_position(position))) | |
| if parent_id is not _UNCHANGED: | |
| values['parent_id'] = projects._text(parent_id, 'parent_id', optional=True) | |
| return _mutate(rt, project_id, task_id, 'move', values, by) | |
| def activate(rt, project_id, task_id, *, by): | |
| """Include a backup task and its descendants after an explicit human activation.""" | |
| return _mutate(rt, project_id, task_id, 'activate', {}, by) | |
| def delete(rt, project_id, task_id, *, by): | |
| """Delete one task and its full subtree in the project's write transaction.""" | |
| return _mutate(rt, project_id, task_id, 'delete', {}, by) | |
| def list_dependencies(rt, project_id): | |
| """Read all dependency edges within one tenant and project without creating storage.""" | |
| return _backend(rt).list_dependencies(projects._text(project_id, 'project_id')) | |
| def add_dependency(rt, project_id, from_task, to_task, *, type='FS', lag_days=0, by): | |
| """Link two tasks and shift the successor and its dependents to satisfy the new constraint.""" | |
| project_id = projects._text(project_id, 'project_id') | |
| by = projects._text(by, 'by') | |
| kind, lag_days = _dependency_fields(type, lag_days) | |
| record = dict(id=uuid.uuid4().hex, project_id=project_id, | |
| from_task=projects._text(from_task, 'from_task'), | |
| to_task=projects._text(to_task, 'to_task'), type=kind, lag_days=lag_days) | |
| result = _backend(rt).add_dependency(record) | |
| projects._log(rt, project_id, by, 'dependencies', None, result) | |
| return result | |
| def delete_dependency(rt, project_id, dependency_id, *, by): | |
| """Remove one project dependency without moving either task earlier.""" | |
| project_id = projects._text(project_id, 'project_id') | |
| by = projects._text(by, 'by') | |
| record = _backend(rt).delete_dependency(project_id, projects._text(dependency_id, 'dependency_id')) | |
| projects._log(rt, project_id, by, 'dependencies', record, None) | |
| return record | |
| def reschedule(rt, task_id, start_on, due_on, by): | |
| """Reschedule one tenant task and return every dependent shifted to satisfy its constraints.""" | |
| task_id, by = projects._text(task_id, 'task_id'), projects._text(by, 'by') | |
| values = {field: projects._date(value, field) for field, value in | |
| (('start_on', start_on), ('due_on', due_on))} | |
| before, result = _backend(rt).reschedule(task_id, values) | |
| projects._log(rt, result['task']['project_id'], by, 'tasks', before, result) | |
| return result | |
| def validate(rt, project_id, candidate=None): | |
| """Reconcile progress against a separate authored-task read without the display calculator.""" | |
| backend = _backend(rt) | |
| project_id = projects._text(project_id, 'project_id') | |
| with backend.projects._connection() as con: | |
| project = backend._project(con, project_id) | |
| rows = (con.execute(f'SELECT id, parent_id, active, done_at FROM {backend.t(TASKS)} ' | |
| 'WHERE tenant = %s AND project_id = %s', | |
| (backend.tenant_slug, project_id)).fetchall() | |
| if backend._exists(con) else []) | |
| children, completed = defaultdict(list), {} | |
| for task_id, parent_id, active, done_at in rows: | |
| if active: | |
| children[parent_id].append(task_id) | |
| completed[task_id] = done_at is not None | |
| pending, leaves = deque(children[None]), [] | |
| while pending: | |
| task_id = pending.popleft() | |
| if children[task_id]: | |
| pending.extend(children[task_id]) | |
| else: | |
| leaves.append(task_id) | |
| done, total = sum(completed[task_id] for task_id in leaves), len(leaves) | |
| expected = round(100 * done / total) if total else (0 if rows else project['progress']) | |
| actual = project['progress'] if candidate is None else candidate | |
| return {'check': 'project progress ties to active leaf tasks', 'ok': actual == expected, | |
| 'expected': expected, 'actual': actual, 'done': done, 'total': total} | |
| def _evaluate_project(rt, backend, project_id, matcher, *, today, by): | |
| day = projects._date(today or projects._today(), 'today') | |
| by = projects._text(by, 'by') | |
| rows = backend.list_tasks(project_id) | |
| if not any(row.get('completion_filter') is not None for row in rows): | |
| return [] | |
| events = [] | |
| with backend._write(project_id) as (con, project): | |
| rows = backend._rows(con, project_id) | |
| ancestors = {item['id']: item for item in rows} | |
| for row in rows: | |
| if row.get('completion_filter') is None: | |
| continue | |
| # A dormant backup and its subtree need human activation before evaluation. | |
| current = row | |
| while current['active'] and current['parent_id'] is not None: | |
| current = ancestors[current['parent_id']] | |
| if not current['active']: | |
| continue | |
| principal = (backend.tenant_slug, row.get('filter_by', row['created_by'])) | |
| if row.get('filter_closed_at'): | |
| pairs = row['closed_targets'] | |
| elif row.get('target_filter') is not None: | |
| saved = row['target_filter'] | |
| pairs = projects._pair_list((saved['grain'], key) for key in | |
| matcher(rt, saved['grain'], saved['filter'], user=principal)) | |
| else: | |
| pairs = row.get('targets') or [] | |
| met = set() | |
| for grain in dict.fromkeys(pair['grain'] for pair in pairs): | |
| keys = set(matcher(rt, grain, row['completion_filter'], user=principal)) | |
| met.update((grain, pair['key']) for pair in pairs if pair['grain'] == grain and pair['key'] in keys) | |
| missing = [pair for pair in pairs if (pair['grain'], pair['key']) not in met] | |
| after = dict(row, filter_status={'met': len(met), 'total': len(pairs), | |
| 'message': f'met {len(met)} of {len(pairs)}', 'evaluated_on': day}) | |
| if row.get('filter_closed_at'): | |
| previous = {(pair['grain'], pair['key']) for pair in row.get('filter_missing', [])} | |
| for pair in missing: | |
| if (pair['grain'], pair['key']) not in previous: | |
| events.append((row['id'], f'{projects.GRAIN_LABELS[pair["grain"]]} {pair["key"]} ' | |
| 'no longer meets the filter', row, dict(after, target=pair))) | |
| elif not row['done'] and pairs and not missing: | |
| after.update(done=True, done_at=projects._now(), filter_closed_at=projects._now(), | |
| closed_targets=pairs) | |
| events.append((row['id'], 'Done by filter', row, after)) | |
| after['filter_missing'] = missing | |
| backend._save(con, after) | |
| backend._sync_progress(con, project_id, project['progress']) | |
| for _task_id, action, before, after in events: | |
| projects._log(rt, project_id, by, action, before, after) | |
| return [dict(task_id=task_id, action=action) for task_id, action, _before, _after in events] | |
| def evaluate_filter_tasks(rt, matcher, *, today, by): | |
| """Evaluate every saved task criterion on refresh through the injected tenant matcher.""" | |
| backend = _backend(rt) | |
| events = [] | |
| for project_id in backend.filter_projects(): | |
| events.extend(_evaluate_project(rt, backend, project_id, matcher, today=today, by=by)) | |
| return events | |
| def validate_filter_tasks(rt, project_id, matcher): | |
| """Reconcile stored completion counts against a separate target and matcher read.""" | |
| backend = _backend(rt) | |
| checks = [] | |
| for row in backend.list_tasks(projects._text(project_id, 'project_id')): | |
| if row.get('filter_status') is None: | |
| continue | |
| user = (backend.tenant_slug, row.get('filter_by', row['created_by'])) | |
| saved = row.get('target_filter') | |
| if row.get('filter_closed_at'): | |
| targets = row['closed_targets'] | |
| elif saved is not None: | |
| targets = [{'grain': saved['grain'], 'key': key} | |
| for key in matcher(rt, saved['grain'], saved['filter'], user=user)] | |
| else: | |
| targets = row.get('targets') or [] | |
| matches = {grain: set(matcher(rt, grain, row['completion_filter'], user=user)) | |
| for grain in {pair['grain'] for pair in targets}} | |
| met = sum(pair['key'] in matches[pair['grain']] for pair in targets) | |
| actual = row['filter_status'] | |
| checks.append({'task': row['id'], 'expected': {'met': met, 'total': len(targets)}, | |
| 'actual': {key: actual[key] for key in ('met', 'total')}, | |
| 'ok': actual['met'] == met and actual['total'] == len(targets)}) | |
| return {'check': 'task filter counts tie to target rows', 'ok': all(row['ok'] for row in checks), | |
| 'tasks': checks} | |
| class PgTaskStore: | |
| """Use the existing tenant backend, write guard and project row lock for task authorship.""" | |
| def __init__(self, backend): | |
| self.projects = backend | |
| self.tenant_slug = backend.tenant_slug | |
| self.t = backend.t | |
| def _exists(self, con, table=TASKS): | |
| return con.execute('SELECT to_regclass(%s)', (self.t(table),)).fetchone()[0] is not None | |
| def _project(self, con, project_id, lock=False): | |
| if not self.projects._exists(con): | |
| raise KeyError('Project not found.') | |
| return self.projects._one(con, project_id, lock=lock) | |
| def ensure_schema(self, con): | |
| con.execute(f'''CREATE TABLE IF NOT EXISTS {self.t(TASKS)} ( | |
| tenant TEXT NOT NULL, id TEXT NOT NULL, project_id TEXT NOT NULL, | |
| parent_id TEXT, position NUMERIC NOT NULL, name TEXT NOT NULL, | |
| assignee TEXT, start_on DATE, due_on DATE, done_at TEXT, | |
| contingency_for TEXT, active BOOLEAN NOT NULL DEFAULT TRUE, created_by TEXT NOT NULL, | |
| PRIMARY KEY (tenant, id), UNIQUE (tenant, project_id, id), | |
| FOREIGN KEY (tenant, project_id) REFERENCES {self.t(projects.PROJECTS)} (tenant, id) | |
| ON DELETE CASCADE, | |
| FOREIGN KEY (tenant, project_id, parent_id) REFERENCES {self.t(TASKS)} (tenant, project_id, id) | |
| DEFERRABLE INITIALLY DEFERRED, | |
| CHECK (parent_id IS NULL OR parent_id <> id), | |
| CHECK (contingency_for IS NULL OR contingency_for <> id), | |
| CHECK (start_on IS NULL OR due_on IS NULL OR start_on <= due_on))''') | |
| con.execute(f'CREATE INDEX IF NOT EXISTS "amazon_project_tasks_siblings" ' | |
| f'ON {self.t(TASKS)} (tenant, project_id, parent_id, position, id)') | |
| con.execute(f'''CREATE TABLE IF NOT EXISTS {self.t(DEPENDENCIES)} ( | |
| tenant TEXT NOT NULL, id TEXT NOT NULL, project_id TEXT NOT NULL, | |
| from_task TEXT NOT NULL, to_task TEXT NOT NULL, type TEXT NOT NULL, | |
| lag_days INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (tenant, id), | |
| UNIQUE (tenant, project_id, from_task, to_task, type), | |
| FOREIGN KEY (tenant, project_id) REFERENCES {self.t(projects.PROJECTS)} (tenant, id) | |
| ON DELETE CASCADE, | |
| FOREIGN KEY (tenant, project_id, from_task) REFERENCES {self.t(TASKS)} (tenant, project_id, id) | |
| ON DELETE CASCADE, | |
| FOREIGN KEY (tenant, project_id, to_task) REFERENCES {self.t(TASKS)} (tenant, project_id, id) | |
| ON DELETE CASCADE, | |
| CHECK (from_task <> to_task), CHECK (type IN ('FS', 'SS', 'FF', 'SF')))''') | |
| con.execute(f'CREATE INDEX IF NOT EXISTS "amazon_project_dependencies_target" ' | |
| f'ON {self.t(DEPENDENCIES)} (tenant, project_id, to_task)') | |
| con.execute(f'''CREATE TABLE IF NOT EXISTS {self.t(FILTERS)} ( | |
| tenant TEXT NOT NULL, id TEXT NOT NULL, project_id TEXT NOT NULL, config TEXT NOT NULL, | |
| PRIMARY KEY (tenant, id), | |
| FOREIGN KEY (tenant, project_id, id) REFERENCES {self.t(TASKS)} (tenant, project_id, id) | |
| ON DELETE CASCADE)''') | |
| def _write(self, project_id): | |
| self.projects._write_guard() | |
| with self.projects._connection() as con: | |
| project = self._project(con, project_id, lock=True) | |
| self.ensure_schema(con) | |
| yield con, project | |
| def _rows(self, con, project_id): | |
| rows = con.execute(f'SELECT {", ".join(TASK_COLUMNS)} FROM {self.t(TASKS)} ' | |
| 'WHERE tenant = %s AND project_id = %s ' | |
| 'ORDER BY parent_id NULLS FIRST, position, id', | |
| (self.tenant_slug, project_id)).fetchall() | |
| records = [_record(row) for row in rows] | |
| configs = {} | |
| if self._exists(con, FILTERS): | |
| configs = {task_id: json.loads(value) for task_id, value in con.execute( | |
| f'SELECT id, config FROM {self.t(FILTERS)} WHERE tenant = %s AND project_id = %s', | |
| (self.tenant_slug, project_id)).fetchall()} | |
| return [dict(row, **configs.get(row['id'], {})) for row in records] | |
| def filter_projects(self): | |
| with self.projects._connection() as con: | |
| if not self._exists(con, FILTERS): | |
| return [] | |
| return [row[0] for row in con.execute( | |
| f'SELECT DISTINCT project_id FROM {self.t(FILTERS)} WHERE tenant = %s ORDER BY project_id', | |
| (self.tenant_slug,)).fetchall()] | |
| def list_tasks(self, project_id): | |
| with self.projects._connection() as con: | |
| self._project(con, project_id) | |
| return self._rows(con, project_id) if self._exists(con) else [] | |
| def _dependencies(self, con, project_id): | |
| if not self._exists(con, DEPENDENCIES): | |
| return [] | |
| rows = con.execute(f'SELECT {", ".join(DEPENDENCY_COLUMNS)} FROM {self.t(DEPENDENCIES)} ' | |
| 'WHERE tenant = %s AND project_id = %s ORDER BY id', | |
| (self.tenant_slug, project_id)).fetchall() | |
| return [dict(zip(DEPENDENCY_COLUMNS, row)) for row in rows] | |
| def list_dependencies(self, project_id): | |
| with self.projects._connection() as con: | |
| self._project(con, project_id) | |
| return self._dependencies(con, project_id) | |
| def add_dependency(self, record): | |
| with self._write(record['project_id']) as (con, project): | |
| rows = {row['id']: row for row in self._rows(con, record['project_id'])} | |
| dependencies = self._dependencies(con, record['project_id']) | |
| _check_dependency(rows, dependencies, record['from_task'], record['to_task']) | |
| if any(all(edge[field] == record[field] for field in ('from_task', 'to_task', 'type')) | |
| for edge in dependencies): | |
| raise ValueError('This task dependency already exists.') | |
| shifted = _propagate(rows, [*dependencies, record], record['to_task']) | |
| columns = ('tenant', *DEPENDENCY_COLUMNS) | |
| con.execute(f'INSERT INTO {self.t(DEPENDENCIES)} ({", ".join(columns)}) VALUES ' | |
| f'({", ".join("%s" for _ in columns)})', | |
| (self.tenant_slug, *(record[field] for field in DEPENDENCY_COLUMNS))) | |
| for row in shifted: | |
| self._save(con, row) | |
| self._sync_progress(con, record['project_id'], project['progress']) | |
| return dict(record, shifted=shifted) | |
| def delete_dependency(self, project_id, dependency_id): | |
| with self._write(project_id) as (con, _project): | |
| record = next((row for row in self._dependencies(con, project_id) | |
| if row['id'] == dependency_id), None) | |
| if record is None: | |
| raise KeyError('Dependency not found in this project.') | |
| con.execute(f'DELETE FROM {self.t(DEPENDENCIES)} ' | |
| 'WHERE tenant = %s AND project_id = %s AND id = %s', | |
| (self.tenant_slug, project_id, dependency_id)) | |
| return record | |
| def reschedule(self, task_id, values): | |
| with self.projects._connection() as con: | |
| found = (con.execute(f'SELECT project_id FROM {self.t(TASKS)} WHERE tenant = %s AND id = %s', | |
| (self.tenant_slug, task_id)).fetchone() if self._exists(con) else None) | |
| if found is None: | |
| raise KeyError('Task not found in this tenant.') | |
| with self._write(found[0]) as (con, project): | |
| rows = {row['id']: row for row in self._rows(con, found[0])} | |
| if task_id not in rows: | |
| raise KeyError('Task not found in this project.') | |
| old = dict(rows) | |
| rows[task_id] = _fields(values, rows[task_id]) | |
| rows[task_id]['done'] = rows[task_id]['done_at'] is not None | |
| shifted = _propagate(rows, self._dependencies(con, found[0]), task_id) | |
| shifted = [row for row in shifted if row['id'] != task_id] | |
| for row in [rows[task_id], *shifted]: | |
| self._save(con, row) | |
| self._sync_progress(con, found[0], project['progress']) | |
| before = {'task': old[task_id], 'shifted': [old[row['id']] for row in shifted]} | |
| return before, {'task': rows[task_id], 'shifted': shifted} | |
| def progress(self, project_id): | |
| with self.projects._connection() as con: | |
| project = self._project(con, project_id) | |
| rows = self._rows(con, project_id) if self._exists(con) else [] | |
| return _leaf_progress(rows, project['progress']) | |
| def _next_position(self, rows, parent_id, task_id): | |
| positions = [_position(row['position']) for row in rows.values() | |
| if row['parent_id'] == parent_id and row['id'] != task_id] | |
| last = max(positions, default=Decimal(0)) | |
| with localcontext() as context: | |
| context.prec = max(28, len(last.as_tuple().digits) + abs(last.as_tuple().exponent) + 2) | |
| return str(last + 1) | |
| def _save(self, con, record, *, insert=False): | |
| if insert: | |
| columns = ('tenant', *TASK_COLUMNS) | |
| con.execute(f'INSERT INTO {self.t(TASKS)} ({", ".join(columns)}) VALUES ' | |
| f'({", ".join("%s" for _ in columns)})', | |
| (self.tenant_slug, *(record[field] for field in TASK_COLUMNS))) | |
| else: | |
| fields = tuple(field for field in TASK_COLUMNS if field not in ('id', 'project_id', 'created_by')) | |
| con.execute(f'UPDATE {self.t(TASKS)} SET ' | |
| + ', '.join(f'{field} = %s' for field in fields) | |
| + ' WHERE tenant = %s AND project_id = %s AND id = %s', | |
| (*(record[field] for field in fields), self.tenant_slug, record['project_id'], record['id'])) | |
| if any(field in record for field in TARGET_FIELDS): | |
| config = {field: record[field] for field in (*TARGET_FIELDS, *FILTER_STATE, 'filter_by') if field in record} | |
| con.execute(f'INSERT INTO {self.t(FILTERS)} (tenant, id, project_id, config) VALUES (%s, %s, %s, %s) ' | |
| 'ON CONFLICT (tenant, id) DO UPDATE SET config = excluded.config', | |
| (self.tenant_slug, record['id'], record['project_id'], json.dumps(config, allow_nan=False))) | |
| def _delete(self, con, project_id, ids): | |
| # Delete descendants first so database cascade triggers add no depth ceiling. | |
| for part in projects._chunks(list(reversed(ids))): | |
| marks = ', '.join('%s' for _ in part) | |
| args = (self.tenant_slug, project_id, *part) | |
| self._delete_dependencies(con, project_id, part) | |
| con.execute(f'DELETE FROM {self.t(FILTERS)} ' | |
| f'WHERE tenant = %s AND project_id = %s AND id IN ({marks})', args) | |
| con.execute(f'UPDATE {self.t(TASKS)} SET contingency_for = NULL ' | |
| f'WHERE tenant = %s AND project_id = %s AND contingency_for IN ({marks})', args) | |
| con.execute(f'DELETE FROM {self.t(TASKS)} ' | |
| f'WHERE tenant = %s AND project_id = %s AND id IN ({marks})', args) | |
| def _delete_dependencies(self, con, project_id, ids): | |
| marks = ', '.join('%s' for _ in ids) | |
| con.execute(f'DELETE FROM {self.t(DEPENDENCIES)} ' | |
| f'WHERE tenant = %s AND project_id = %s ' | |
| f'AND (from_task IN ({marks}) OR to_task IN ({marks}))', | |
| (self.tenant_slug, project_id, *ids, *ids)) | |
| def _sync_progress(self, con, project_id, fallback): | |
| result = _leaf_progress(self._rows(con, project_id), fallback) | |
| con.execute(f'UPDATE {self.t(projects.PROJECTS)} SET progress = %s, updated_at = %s ' | |
| 'WHERE tenant = %s AND id = %s', | |
| (result['progress'], projects._now(), self.tenant_slug, project_id)) | |
| return result | |
| def mutate(self, project_id, task_id, action, values, by): | |
| with self._write(project_id) as (con, project): | |
| rows = {row['id']: row for row in self._rows(con, project_id)} | |
| before = rows.get(task_id) | |
| if action != 'create' and before is None: | |
| raise KeyError('Task not found in this project.') | |
| if action == 'delete': | |
| ids = _subtree(rows, task_id) | |
| self._delete(con, project_id, ids) | |
| counts = self._sync_progress(con, project_id, project['progress']) | |
| return before, None, {'deleted': task_id, 'task_ids': ids, 'progress': counts['progress']} | |
| if action == 'create': | |
| after = dict(values, id=task_id, project_id=project_id, | |
| active=values['contingency_for'] is None) | |
| elif action == 'update': | |
| after = _fields(values, before) | |
| if after['contingency_for'] != before['contingency_for'] and after['contingency_for'] is not None: | |
| after['active'] = False | |
| elif action == 'move': | |
| after = dict(before, **values) | |
| elif action == 'activate': | |
| after = dict(before, active=True) | |
| else: | |
| raise ValueError('Unknown task change.') | |
| if set(TARGET_FIELDS) & values.keys(): | |
| if before and before.get('filter_closed_at') and values.get('done') is not False: | |
| raise ValueError('Reopen and reset the task before changing its targets or filter.') | |
| after['filter_by'] = by | |
| for field in FILTER_STATE: | |
| after.pop(field, None) | |
| if values.get('done') is False: | |
| for field in FILTER_STATE: | |
| after.pop(field, None) | |
| if after.get('completion_filter') is not None: | |
| after['filter_by'] = by | |
| _check_parent(rows, task_id, after['parent_id']) | |
| _check_contingency(rows, task_id, after['contingency_for']) | |
| if after['position'] is None: | |
| after['position'] = self._next_position(rows, after['parent_id'], task_id) | |
| shifted = [] | |
| if action == 'update' and {'start_on', 'due_on'} & values.keys(): | |
| rows[task_id] = after | |
| shifted = _propagate(rows, self._dependencies(con, project_id), task_id) | |
| after = rows[task_id] | |
| self._save(con, after, insert=action == 'create') | |
| for row in shifted: | |
| if row['id'] != task_id: | |
| self._save(con, row) | |
| self._sync_progress(con, project_id, project['progress']) | |
| saved = next(row for row in self._rows(con, project_id) if row['id'] == task_id) | |
| return before, saved, saved | |