def test_expressions_in_union_in_union_order_by(self):
column = SQLRaw("1")
alias = Alias(column, "id")
expr = Union(Select(alias),
Select(column),
order_by=alias + 1,
limit=1,
offset=1,
all=True)
expr = Union(expr, expr, order_by=alias + 1, all=True)
result = self.connection.execute(expr)
self.assertEquals(result.get_all(), [(1, ), (1, )])
def getSpecifications(self, user):
"""See `IMilestoneData`"""
from lp.registry.model.person import Person
origin = [Specification]
product_origin, clauses = get_specification_active_product_filter(self)
origin.extend(product_origin)
clauses.extend(get_specification_privacy_filter(user))
origin.append(LeftJoin(Person, Specification._assigneeID == Person.id))
milestones = self._milestone_ids_expr(user)
results = Store.of(self.target).using(*origin).find(
(Specification, Person),
Specification.id.is_in(
Union(Select(
Specification.id,
tables=[Specification],
where=(Specification.milestoneID.is_in(milestones))),
Select(SpecificationWorkItem.specification_id,
tables=[SpecificationWorkItem],
where=And(
SpecificationWorkItem.milestone_id.is_in(
milestones),
SpecificationWorkItem.deleted == False)),
all=True)), *clauses)
ordered_results = results.order_by(Desc(Specification.priority),
Specification.definition_status,
Specification.implementation_status,
Specification.title)
ordered_results.config(distinct=True)
return DecoratedResultSet(ordered_results, itemgetter(0))
def test_expressions_in_union_order_by(self):
# The following statement breaks in postgres:
# SELECT 1 AS id UNION SELECT 1 ORDER BY id+1;
# With the error:
# ORDER BY on a UNION/INTERSECT/EXCEPT result must
# be on one of the result columns
column = SQLRaw("1")
Alias.auto_counter = 0
alias = Alias(column, "id")
expr = Union(Select(alias),
Select(column),
order_by=alias + 1,
limit=1,
offset=1,
all=True)
state = State()
statement = compile(expr, state)
self.assertEquals(
statement, 'SELECT * FROM '
'((SELECT 1 AS id) UNION ALL (SELECT 1)) AS "_1" '
'ORDER BY id+? LIMIT 1 OFFSET 1')
self.assertVariablesEqual(state.parameters, [Variable(1)])
result = self.connection.execute(expr)
self.assertEquals(result.get_one(), (1, ))
def findRecipes(branch_or_repository, revspecs=None):
"""Find recipes for a given branch or repository.
:param branch_or_repository: The branch or repository to search for.
:param revspecs: If not None, return only recipes whose `revspec` is
in this sequence.
:return: a collection of `ISourcePackageRecipe`s.
"""
from lp.code.model.sourcepackagerecipe import SourcePackageRecipe
store = Store.of(branch_or_repository)
if IGitRepository.providedBy(branch_or_repository):
data_clause = (SourcePackageRecipeData.base_git_repository ==
branch_or_repository)
insn_clause = (_SourcePackageRecipeDataInstruction.git_repository
== branch_or_repository)
elif IBranch.providedBy(branch_or_repository):
data_clause = (
SourcePackageRecipeData.base_branch == branch_or_repository)
insn_clause = (_SourcePackageRecipeDataInstruction.branch ==
branch_or_repository)
else:
raise AssertionError("Unsupported source: %r" %
(branch_or_repository, ))
if revspecs is not None:
concrete_revspecs = [
revspec for revspec in revspecs if revspec is not None
]
data_revspec_clause = In(SourcePackageRecipeData.revspec,
concrete_revspecs)
insn_revspec_clause = In(
_SourcePackageRecipeDataInstruction.revspec, concrete_revspecs)
if None in revspecs:
data_revspec_clause = Or(
data_revspec_clause,
SourcePackageRecipeData.revspec == None)
insn_revspec_clause = Or(
insn_revspec_clause,
_SourcePackageRecipeDataInstruction.revspec == None)
data_clause = And(data_clause, data_revspec_clause)
insn_clause = And(insn_clause, insn_revspec_clause)
return store.find(
SourcePackageRecipe,
SourcePackageRecipe.id.is_in(
Union(
Select(SourcePackageRecipeData.sourcepackage_recipe_id,
data_clause),
Select(
SourcePackageRecipeData.sourcepackage_recipe_id,
And(
_SourcePackageRecipeDataInstruction.recipe_data_id
== SourcePackageRecipeData.id, insn_clause)))))
def findRecipes(branch):
from lp.code.model.sourcepackagerecipe import SourcePackageRecipe
store = Store.of(branch)
return store.find(
SourcePackageRecipe,
SourcePackageRecipe.id.is_in(Union(
Select(
SourcePackageRecipeData.sourcepackage_recipe_id,
SourcePackageRecipeData.base_branch == branch),
Select(
SourcePackageRecipeData.sourcepackage_recipe_id,
And(
_SourcePackageRecipeDataInstruction.recipe_data_id ==
SourcePackageRecipeData.id,
_SourcePackageRecipeDataInstruction.branch == branch)
)
))
)
def calculate_bugsummary_rows(target):
"""Calculate BugSummary row fragments for the given `IBugTarget`.
The data is re-aggregated from BugTaskFlat, BugTag and BugSubscription.
"""
# Use a CTE to prepare a subset of BugTaskFlat, filtered to the
# relevant target and to exclude duplicates, and with has_patch
# calculated.
relevant_tasks = With(
'relevant_task',
Select((BugTaskFlat.bug_id, BugTaskFlat.information_type,
BugTaskFlat.status, BugTaskFlat.milestone_id,
BugTaskFlat.importance,
Alias(BugTaskFlat.latest_patch_uploaded != None, 'has_patch'),
BugTaskFlat.access_grants, BugTaskFlat.access_policies),
tables=[BugTaskFlat],
where=And(BugTaskFlat.duplicateof_id == None,
*get_bugtaskflat_constraint(target))))
# Storm class to reference the CTE.
class RelevantTask(BugTaskFlat):
__storm_table__ = 'relevant_task'
has_patch = Bool()
# Storm class to reference the union.
class BugSummaryPrototype(RawBugSummary):
__storm_table__ = 'bugsummary_prototype'
# Prepare a union for all combination of privacy and taggedness.
# It'll return a full set of
# (status, milestone, importance, has_patch, tag, viewed_by, access_policy)
# rows.
common_cols = (RelevantTask.status, RelevantTask.milestone_id,
RelevantTask.importance, RelevantTask.has_patch)
null_tag = Alias(Cast(None, 'text'), 'tag')
null_viewed_by = Alias(Cast(None, 'integer'), 'viewed_by')
null_policy = Alias(Cast(None, 'integer'), 'access_policy')
tag_join = Join(BugTag, BugTag.bugID == RelevantTask.bug_id)
public_constraint = RelevantTask.information_type.is_in(
PUBLIC_INFORMATION_TYPES)
private_constraint = RelevantTask.information_type.is_in(
PRIVATE_INFORMATION_TYPES)
unions = Union(
# Public, tagless
Select(common_cols + (null_tag, null_viewed_by, null_policy),
tables=[RelevantTask],
where=public_constraint),
# Public, tagged
Select(common_cols + (BugTag.tag, null_viewed_by, null_policy),
tables=[RelevantTask, tag_join],
where=public_constraint),
# Private, access grant, tagless
Select(common_cols +
(null_tag, Unnest(RelevantTask.access_grants), null_policy),
tables=[RelevantTask],
where=private_constraint),
# Private, access grant, tagged
Select(common_cols +
(BugTag.tag, Unnest(RelevantTask.access_grants), null_policy),
tables=[RelevantTask, tag_join],
where=private_constraint),
# Private, access policy, tagless
Select(
common_cols +
(null_tag, null_viewed_by, Unnest(RelevantTask.access_policies)),
tables=[RelevantTask],
where=private_constraint),
# Private, access policy, tagged
Select(
common_cols +
(BugTag.tag, null_viewed_by, Unnest(RelevantTask.access_policies)),
tables=[RelevantTask, tag_join],
where=private_constraint),
all=True)
# Select the relevant bits of the prototype rows and aggregate them.
proto_key_cols = (BugSummaryPrototype.status,
BugSummaryPrototype.milestone_id,
BugSummaryPrototype.importance,
BugSummaryPrototype.has_patch, BugSummaryPrototype.tag,
BugSummaryPrototype.viewed_by_id,
BugSummaryPrototype.access_policy_id)
origin = IStore(BugTaskFlat).with_(relevant_tasks).using(
Alias(unions, 'bugsummary_prototype'))
results = origin.find(proto_key_cols + (Count(), ))
results = results.group_by(*proto_key_cols).order_by(*proto_key_cols)
return results
def _calculate_tag_query(conditions, tags):
"""Determine tag-related conditions and assemble a query.
:param conditions: the other conditions that constrain the query.
:param tags: the list of tags that the bug has.
"""
# These are tables and joins we will want. We leave out the tag join
# because that needs to be added conditionally.
tables = [
StructuralSubscription,
Join(BugSubscriptionFilter,
BugSubscriptionFilter.structural_subscription_id ==
StructuralSubscription.id),
LeftJoin(BugSubscriptionFilterStatus,
BugSubscriptionFilterStatus.filter_id ==
BugSubscriptionFilter.id),
LeftJoin(BugSubscriptionFilterImportance,
BugSubscriptionFilterImportance.filter_id ==
BugSubscriptionFilter.id),
LeftJoin(BugSubscriptionFilterInformationType,
BugSubscriptionFilterInformationType.filter_id ==
BugSubscriptionFilter.id)]
tag_join = LeftJoin(
BugSubscriptionFilterTag,
BugSubscriptionFilterTag.filter_id == BugSubscriptionFilter.id)
# If the bug has no tags, this is relatively easy. Otherwise, not so
# much.
if len(tags) == 0:
# The bug has no tags. We should leave out filters that
# require any generic non-empty set of tags
# (BugSubscriptionFilter.include_any_tags), which we do with
# the conditions.
conditions.append(Not(BugSubscriptionFilter.include_any_tags))
tables.append(tag_join)
return Select(
BugSubscriptionFilter.id,
tables=tables,
where=And(*conditions),
# We have to make sure that the filter does not require
# any *specific* tags. We do that with a GROUP BY on the
# filters, and then a HAVING clause that aggregates the
# BugSubscriptionFilterTags that are set to "include" the
# tag. (If it is not an include, that is an exclude, and a
# bug without tags will not have a particular tag, so we can
# ignore those in this case.) This requires a CASE
# statement within the COUNT.
group_by=(BugSubscriptionFilter.id,),
having=Count(
SQL('CASE WHEN BugSubscriptionFilterTag.include '
'THEN BugSubscriptionFilterTag.tag END')) == 0)
else:
# The bug has some tags. This will require a bit of fancy
# footwork. First, though, we will simply want to leave out
# filters that should only match bugs without tags.
conditions.append(Not(BugSubscriptionFilter.exclude_any_tags))
# We're going to have to do a union with another query. One
# query will handle filters that are marked to include *any*
# of the filter's selected tags, and the other query will
# handle filters that include *all* of the filter's selected
# tags (as determined by BugSubscriptionFilter.find_all_tags).
# Every aspect of the unioned queries' WHERE clauses *other
# than tags* will need to be the same, and so we perform that
# separately, first. When Storm supports the WITH statement
# (bug 729134), we can consider folding this back into a single
# query.
candidates = list(
IStore(BugSubscriptionFilter).using(*tables).find(
BugSubscriptionFilter.id, *conditions))
if not candidates:
return None
# As mentioned, in this first SELECT we handle filters that
# match any of the filter's tags. This can be a relatively
# straightforward query--we just need a bit more added to
# our WHERE clause, and we don't need a GROUP BY/HAVING.
first_select = Select(
BugSubscriptionFilter.id,
tables=[BugSubscriptionFilter, tag_join],
where=And(
Or( # We want filters that proclaim they simply want any tags.
BugSubscriptionFilter.include_any_tags,
# Also include filters that match any tag...
And(Not(BugSubscriptionFilter.find_all_tags),
Or( # ...with a positive match...
And(BugSubscriptionFilterTag.include,
In(BugSubscriptionFilterTag.tag, tags)),
# ...or with a negative match...
And(Not(BugSubscriptionFilterTag.include),
Not(In(BugSubscriptionFilterTag.tag, tags))),
# ...or if the filter does not specify any tags.
BugSubscriptionFilterTag.tag == None))),
In(BugSubscriptionFilter.id, candidates)))
# We have our first clause. Now we start on the second one:
# handling filters that match *all* tags.
# This second query will have a HAVING clause, which is where some
# tricky bits happen. We first make a SQL snippet that
# represents the tags on this bug. It is straightforward
# except for one subtle hack: the addition of the empty
# space in the array. This is because we are going to be
# aggregating the tags on the filters using ARRAY_AGG, which
# includes NULLs (unlike most other aggregators). That
# is an issue here because we use CASE statements to divide
# up the set of tags that are supposed to be included and
# supposed to be excluded. This means that if we aggregate
# "CASE WHEN BugSubscriptionFilterTag.include THEN
# BugSubscriptionFilterTag.tag END" then that array will
# include NULL. SQL treats NULLs as unknowns that can never
# be matched, so the array of ['foo', 'bar', NULL] does not
# contain the array of ['foo', NULL] ("SELECT
# ARRAY['foo','bar',NULL]::TEXT[] @>
# ARRAY['foo',NULL]::TEXT[];" is false). Therefore, so we
# can make the HAVING statement we want to make without
# defining a custom Postgres aggregator, we use a single
# space as, effectively, NULL. This is safe because a
# single space is not an acceptable tag. Again, the
# clearest alternative is defining a custom Postgres aggregator.
tags_array = "ARRAY[%s,' ']::TEXT[]" % ",".join(
quote(tag) for tag in tags)
# Now let's build the select itself.
second_select = Select(
BugSubscriptionFilter.id,
tables=[BugSubscriptionFilter, tag_join],
# Our WHERE clause is straightforward. We are simply
# focusing on BugSubscriptionFilter.find_all_tags, when the
# first SELECT did not consider it.
where=And(BugSubscriptionFilter.find_all_tags,
In(BugSubscriptionFilter.id, candidates)),
# The GROUP BY collects the filters together.
group_by=(BugSubscriptionFilter.id,),
having=And(
# The list of tags should be a superset of the filter tags to
# be included.
ArrayContains(
SQL(tags_array),
# This next line gives us an array of the tags that the
# filter wants to include. Notice that it includes the
# empty string when the condition does not match, per the
# discussion above.
ArrayAgg(
SQL("CASE WHEN BugSubscriptionFilterTag.include "
"THEN BugSubscriptionFilterTag.tag "
"ELSE ' '::TEXT END"))),
# The list of tags should also not intersect with the
# tags that the filter wants to exclude.
Not(
ArrayIntersects(
SQL(tags_array),
# This next line gives us an array of the tags
# that the filter wants to exclude. We do not bother
# with the empty string, and therefore allow NULLs
# into the array, because in this case we are
# determining whether the sets intersect, not if the
# first set subsumes the second.
ArrayAgg(
SQL('CASE WHEN '
'NOT BugSubscriptionFilterTag.include '
'THEN BugSubscriptionFilterTag.tag END'))))))
# Everything is ready. Return the union.
return Union(first_select, second_select)