def filter_stories(worklist, filters, user_id):
filter_queries = []
for filter in filters:
subquery = api_base.model_query(models.Story.id).distinct().subquery()
query = api_base.model_query(models.StorySummary)
query = query.join(subquery, models.StorySummary.id == subquery.c.id)
query = query.outerjoin(models.Task,
models.Project,
models.project_group_mapping,
models.ProjectGroup)
for criterion in filter.criteria:
attr = translate_criterion_to_field(criterion)
if hasattr(models.StorySummary, attr):
model = models.StorySummary
else:
if attr in ('assignee_id', 'project_id'):
model = models.Task
elif attr == 'project_group_id':
model = models.ProjectGroup
attr = 'id'
else:
continue
if attr == 'tags':
if criterion.negative:
query = query.filter(
~models.StorySummary.tags.any(
models.StoryTag.name.in_([criterion.value])))
else:
query = query.filter(
models.StorySummary.tags.any(
models.StoryTag.name.in_([criterion.value])))
continue
if criterion.negative:
query = query.filter(
getattr(model, attr) != criterion.value)
else:
query = query.filter(
getattr(model, attr) == criterion.value)
filter_queries.append(query)
if len(filter_queries) > 1:
query = filter_queries[0]
query = query.union(*filter_queries[1:])
query = api_base.filter_private_stories(
query, user_id, models.StorySummary)
return query.all()
elif len(filter_queries) == 1:
query = filter_queries[0]
query = api_base.filter_private_stories(
query, user_id, models.StorySummary)
return query.all()
else:
return []
def get_visible_items(worklist, current_user=None):
stories = worklist.items.filter(models.WorklistItem.item_type == 'story')
stories = stories.join(
(models.Story, models.Story.id == models.WorklistItem.item_id))
stories = api_base.filter_private_stories(stories, current_user)
tasks = worklist.items.filter(models.WorklistItem.item_type == 'task')
tasks = tasks.join(
(models.Task, models.Task.id == models.WorklistItem.item_id))
tasks = tasks.outerjoin(models.Story)
tasks = api_base.filter_private_stories(tasks, current_user)
return stories.union(tasks)
def comments_query(self,
q,
marker=None,
offset=None,
limit=None,
current_user=None,
**kwargs):
session = api_base.get_session()
clean_query = api_base.model_query(models.Comment, session)
clean_query = clean_query.outerjoin(models.Story)
clean_query = api_base.filter_private_stories(clean_query,
current_user)
try:
query = self._build_fulltext_search(models.Comment, clean_query, q)
query = self._apply_pagination(models.Comment, query, marker,
offset, limit)
return query.all()
except DBError:
query = self._build_fulltext_search(models.Comment,
clean_query,
q,
mode=FullTextMode.NATURAL)
query = self._apply_pagination(models.Comment, query, marker,
offset, limit)
return query.all()
def story_get_simple(story_id,
session=None,
current_user=None,
no_permissions=False):
"""Return a story with the given ID.
The `no_permissions` parameter should only ever be used in situations
where the permissions have already been checked. For example, when
updating the "updated_at" field when comments are made on stories.
:param story_id: ID of the story to return.
:param session: DB session to use.
:param current_user: The ID of the user making the request.
:param no_permissions: Skip filtering stories by permission.
:return: The story being searched for, or None if nothing found.
"""
query = api_base.model_query(models.Story, session) \
.options(subqueryload(models.Story.tags)) \
.filter_by(id=story_id)
if not no_permissions:
# Filter out stories that the current user can't see
query = api_base.filter_private_stories(query, current_user)
return query.first()
def task_get(task_id, session=None, current_user=None):
query = api_base.model_query(models.Task, session)
query = query.filter(models.Task.id == task_id)
# Filter out tasks or stories that the current user can't see
query = query.outerjoin(models.Story)
query = api_base.filter_private_stories(query, current_user)
return query.first()
def story_get(story_id, session=None, current_user=None):
query = api_base.model_query(models.StorySummary, session)
query = query.options(subqueryload(models.StorySummary.tags))\
.filter_by(id=story_id)
# Filter out stories that the current user can't see
query = api_base.filter_private_stories(query, current_user,
story_model=models.StorySummary)
return query.first()
def story_get(story_id, session=None, current_user=None):
query = api_base.model_query(models.StorySummary, session)
query = query.options(subqueryload(models.StorySummary.tags))\
.filter_by(id=story_id)
# Filter out stories that the current user can't see
query = api_base.filter_private_stories(query,
current_user,
story_model=models.StorySummary)
return query.first()
def task_build_query(project_group_id=None,
board_id=None,
worklist_id=None,
current_user=None,
session=None,
**kwargs):
# Construct the query
query = api_base.model_query(models.Task, session=session)
if project_group_id:
query = query.join(models.Project,
models.project_group_mapping,
models.ProjectGroup) \
.filter(models.ProjectGroup.id == project_group_id)
# Sanity check on input parameters
query = api_base.apply_query_filters(query=query,
model=models.Task,
**kwargs)
# Filter out tasks or stories that the current user can't see
query = query.outerjoin(models.Story)
query = api_base.filter_private_stories(query, current_user)
if worklist_id or board_id:
query = query.outerjoin(
(models.WorklistItem,
models.WorklistItem.item_id == models.Task.id))
query = query.filter(models.WorklistItem.item_type == "task")
query = query.outerjoin(models.Worklist)
# Filter by worklist
if worklist_id:
query = query.filter(models.Worklist.id == worklist_id)
query = api_base.filter_private_worklists(query,
current_user,
hide_lanes=False)
# Filter by board
if board_id:
query = query.outerjoin(models.BoardWorklist, models.Board)
query = api_base.filter_private_boards(query, current_user)
query = query.filter(models.Board.id == board_id)
query = api_base.filter_private_worklists(query,
current_user,
hide_lanes=False)
return query
def event_get(event_id, session=None, current_user=None):
query = (api_base.model_query(models.TimeLineEvent,
session).filter_by(id=event_id))
query = query.outerjoin(models.Story)
query = api_base.filter_private_stories(query, current_user)
query = query.outerjoin(
(models.Worklist,
models.Worklist.id == models.TimeLineEvent.worklist_id))
query = api_base.filter_private_worklists(query,
current_user,
hide_lanes=False)
query = query.outerjoin(
(models.Board, models.Board.id == models.TimeLineEvent.board_id))
query = api_base.filter_private_boards(query, current_user)
return query.first()
def event_get(event_id, session=None, current_user=None):
query = (api_base.model_query(models.TimeLineEvent, session)
.filter_by(id=event_id))
query = query.outerjoin(models.Story)
query = api_base.filter_private_stories(query, current_user)
query = query.outerjoin((
models.Worklist,
models.Worklist.id == models.TimeLineEvent.worklist_id))
query = api_base.filter_private_worklists(
query, current_user, hide_lanes=False)
query = query.outerjoin((
models.Board,
models.Board.id == models.TimeLineEvent.board_id))
query = api_base.filter_private_boards(query, current_user)
return query.first()
def task_build_query(project_group_id=None, board_id=None, worklist_id=None,
current_user=None, session=None, **kwargs):
# Construct the query
query = api_base.model_query(models.Task, session=session)
if project_group_id:
query = query.join(models.Project,
models.project_group_mapping,
models.ProjectGroup) \
.filter(models.ProjectGroup.id == project_group_id)
# Sanity check on input parameters
query = api_base.apply_query_filters(query=query,
model=models.Task,
**kwargs)
# Filter out tasks or stories that the current user can't see
query = query.outerjoin(models.Story)
query = api_base.filter_private_stories(query, current_user)
if worklist_id or board_id:
query = query.outerjoin(
(models.WorklistItem,
models.WorklistItem.item_id == models.Task.id))
query = query.filter(models.WorklistItem.item_type == "task")
query = query.outerjoin(models.Worklist)
# Filter by worklist
if worklist_id:
query = query.filter(models.Worklist.id == worklist_id)
query = api_base.filter_private_worklists(
query, current_user, hide_lanes=False)
# Filter by board
if board_id:
query = query.outerjoin(models.BoardWorklist, models.Board)
query = api_base.filter_private_boards(query, current_user)
query = query.filter(models.Board.id == board_id)
query = api_base.filter_private_worklists(
query, current_user, hide_lanes=False)
return query
def _events_build_query(current_user=None, **kwargs):
query = api_base.model_query(models.TimeLineEvent).distinct()
query = api_base.apply_query_filters(query=query,
model=models.TimeLineEvent,
**kwargs)
query = query.outerjoin(models.Story)
query = api_base.filter_private_stories(query, current_user)
query = query.outerjoin(
(models.Worklist,
models.Worklist.id == models.TimeLineEvent.worklist_id))
query = api_base.filter_private_worklists(query,
current_user,
hide_lanes=False)
query = query.outerjoin(
(models.Board, models.Board.id == models.TimeLineEvent.board_id))
query = api_base.filter_private_boards(query, current_user)
return query
def comments_query(self, q, marker=None, offset=None, limit=None,
current_user=None, **kwargs):
session = api_base.get_session()
clean_query = api_base.model_query(models.Comment, session)
clean_query = clean_query.outerjoin(models.Story)
clean_query = api_base.filter_private_stories(
clean_query, current_user)
try:
query = self._build_fulltext_search(
models.Comment, clean_query, q)
query = self._apply_pagination(
models.Comment, query, marker, offset, limit)
return query.all()
except DBError:
query = self._build_fulltext_search(
models.Comment, clean_query, q, mode=FullTextMode.NATURAL)
query = self._apply_pagination(
models.Comment, query, marker, offset, limit)
return query.all()
def _events_build_query(current_user=None, **kwargs):
query = api_base.model_query(models.TimeLineEvent).distinct()
query = api_base.apply_query_filters(query=query,
model=models.TimeLineEvent,
**kwargs)
query = query.outerjoin(models.Story)
query = api_base.filter_private_stories(query, current_user)
query = query.outerjoin((
models.Worklist,
models.Worklist.id == models.TimeLineEvent.worklist_id))
query = api_base.filter_private_worklists(
query, current_user, hide_lanes=False)
query = query.outerjoin((
models.Board,
models.Board.id == models.TimeLineEvent.board_id))
query = api_base.filter_private_boards(query, current_user)
return query
def story_get_simple(story_id, session=None, current_user=None,
no_permissions=False):
"""Return a story with the given ID.
The `no_permissions` parameter should only ever be used in situations
where the permissions have already been checked. For example, when
updating the "updated_at" field when comments are made on stories.
:param story_id: ID of the story to return.
:param session: DB session to use.
:param current_user: The ID of the user making the request.
:param no_permissions: Skip filtering stories by permission.
:return: The story being searched for, or None if nothing found.
"""
query = api_base.model_query(models.Story, session) \
.options(subqueryload(models.Story.tags)) \
.filter_by(id=story_id)
if not no_permissions:
# Filter out stories that the current user can't see
query = api_base.filter_private_stories(query, current_user)
return query.first()
def get_visible_items(due_date, current_user=None):
stories = api_base.filter_private_stories(due_date.stories, current_user)
tasks = due_date.tasks.outerjoin(models.Story)
tasks = api_base.filter_private_stories(tasks, current_user)
return stories, tasks
def _story_build_query(title=None,
description=None,
assignee_id=None,
creator_id=None,
project_group_id=None,
project_id=None,
updated_since=None,
tags=None,
board_id=None,
worklist_id=None,
tags_filter_type='all',
current_user=None,
session=None):
# First build a standard story query.
query = api_base.model_query(models.Story.id, session=session).distinct()
# Apply basic filters
query = api_base.apply_query_filters(query=query,
model=models.Story,
title=title,
description=description,
creator_id=creator_id)
if updated_since:
query = query.filter(models.Story.updated_at > updated_since)
# Filter out stories that the current user can't see
query = api_base.filter_private_stories(query, current_user)
# Filtering by tags
if tags:
if tags_filter_type == 'all':
for tag in tags:
query = query.filter(models.Story.tags.any(name=tag))
elif tags_filter_type == 'any':
query = query.filter(
models.Story.tags.any(models.StoryTag.name.in_(tags)))
else:
raise exc.NotFound("Tags filter not found.")
# Are we filtering by project group?
if project_group_id:
query = query.join(
(models.Task, models.Task.story_id == models.Story.id))
query = query.join(models.Project, models.project_group_mapping,
models.ProjectGroup)
query = query.filter(models.ProjectGroup.id == project_group_id)
# Are we filtering by task?
if assignee_id or project_id:
if not project_group_id: # We may already have joined this table
query = query.join(
(models.Task, models.Task.story_id == models.Story.id))
if assignee_id:
query = query.filter(models.Task.assignee_id == assignee_id)
if project_id:
query = query.filter(models.Task.project_id == project_id)
if worklist_id or board_id:
query = query.outerjoin(
(models.WorklistItem,
models.WorklistItem.item_id == models.Story.id))
query = query.filter(models.WorklistItem.item_type == "story")
query = query.outerjoin(models.Worklist)
# Filter by worklist
if worklist_id:
query = query.filter(models.Worklist.id == worklist_id)
query = api_base.filter_private_worklists(query,
current_user,
hide_lanes=False)
# Filter by board
if board_id:
query = query.outerjoin(models.BoardWorklist, models.Board)
query = api_base.filter_private_boards(query, current_user)
query = query.filter(models.Board.id == board_id)
query = api_base.filter_private_worklists(query,
current_user,
hide_lanes=False)
return query.distinct()
def _story_build_query(title=None, description=None, assignee_id=None,
creator_id=None, project_group_id=None,
project_id=None, updated_since=None, tags=None,
board_id=None, worklist_id=None, tags_filter_type='all',
current_user=None, session=None):
# First build a standard story query.
query = api_base.model_query(models.Story.id, session=session).distinct()
# Apply basic filters
query = api_base.apply_query_filters(query=query,
model=models.Story,
title=title,
description=description,
creator_id=creator_id)
if updated_since:
query = query.filter(models.Story.updated_at > updated_since)
# Filter out stories that the current user can't see
query = api_base.filter_private_stories(query, current_user)
# Filtering by tags
if tags:
if tags_filter_type == 'all':
for tag in tags:
query = query.filter(models.Story.tags.any(name=tag))
elif tags_filter_type == 'any':
query = query.filter(models.Story.tags.any
(models.StoryTag.name.in_(tags)))
else:
raise exc.NotFound("Tags filter not found.")
# Are we filtering by project group?
if project_group_id:
query = query.join(
(models.Task, models.Task.story_id == models.Story.id))
query = query.join(models.Project,
models.project_group_mapping,
models.ProjectGroup)
query = query.filter(models.ProjectGroup.id == project_group_id)
# Are we filtering by task?
if assignee_id or project_id:
if not project_group_id: # We may already have joined this table
query = query.join(
(models.Task, models.Task.story_id == models.Story.id))
if assignee_id:
query = query.filter(models.Task.assignee_id == assignee_id)
if project_id:
query = query.filter(models.Task.project_id == project_id)
if worklist_id or board_id:
query = query.outerjoin(
(models.WorklistItem,
models.WorklistItem.item_id == models.Story.id))
query = query.filter(models.WorklistItem.item_type == "story")
query = query.outerjoin(models.Worklist)
# Filter by worklist
if worklist_id:
query = query.filter(models.Worklist.id == worklist_id)
query = api_base.filter_private_worklists(
query, current_user, hide_lanes=False)
# Filter by board
if board_id:
query = query.outerjoin(models.BoardWorklist, models.Board)
query = api_base.filter_private_boards(query, current_user)
query = query.filter(models.Board.id == board_id)
query = api_base.filter_private_worklists(
query, current_user, hide_lanes=False)
return query.distinct()
