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 get_specification_privacy_filter(user):
# Circular imports.
from lp.registry.model.accesspolicy import AccessPolicyGrant
public_spec_filter = (
Specification.information_type.is_in(PUBLIC_INFORMATION_TYPES))
if user is None:
return [public_spec_filter]
elif IPersonRoles.providedBy(user):
user = user.person
artifact_grant_query = Coalesce(
ArrayIntersects(
SQL('Specification.access_grants'),
Select(ArrayAgg(TeamParticipation.teamID),
tables=TeamParticipation,
where=(TeamParticipation.person == user))), False)
policy_grant_query = Coalesce(
ArrayIntersects(
Array(SQL('Specification.access_policy')),
Select(ArrayAgg(AccessPolicyGrant.policy_id),
tables=(AccessPolicyGrant,
Join(
TeamParticipation, TeamParticipation.teamID ==
AccessPolicyGrant.grantee_id)),
where=(TeamParticipation.person == user))), False)
return [Or(public_spec_filter, artifact_grant_query, policy_grant_query)]
def test_json_text_element(self):
"JSONTextElement returns an element from a json field as text."
connection = self.database.connect()
json_value = Cast(u'{"a": 1}', "json")
expr = JSONTextElement(json_value, u"a")
result = connection.execute(Select(expr))
self.assertEqual("1", result.get_one()[0])
result = connection.execute(Select(Func("pg_typeof", expr)))
self.assertEqual("text", result.get_one()[0])
def test_quoting(self):
# FIXME "with'quote" should be in the list below, but it doesn't
# work because it breaks the parameter mark translation.
for reserved_name in ["with space", 'with`"escape', "SELECT"]:
reserved_name = SQLToken(reserved_name)
expr = Select(reserved_name,
tables=Alias(Select(Alias(1, reserved_name))))
result = self.connection.execute(expr)
self.assertEquals(result.get_one(), (1, ))
def test_json_element(self):
"JSONElement returns an element from a json field."
connection = self.database.connect()
json_value = Cast(u'{"a": 1}', "json")
expr = JSONElement(json_value, u"a")
# Need to cast as text since newer psycopg versions decode JSON
# automatically.
result = connection.execute(Select(Cast(expr, "text")))
self.assertEqual("1", result.get_one()[0])
result = connection.execute(Select(Func("pg_typeof", expr)))
self.assertEqual("json", result.get_one()[0])
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 test_case_insensitive_like(self):
like = Like(SQLRaw("description"), u"%hullah%", case_sensitive=False)
expr = Select(SQLRaw("id"),
like,
tables=["like_case_insensitive_test"])
result = self.connection.execute(expr)
self.assertEquals(result.get_all(), [(1, ), (2, )])
like = Like(SQLRaw("description"), u"%HULLAH%", case_sensitive=False)
expr = Select(SQLRaw("id"),
like,
tables=["like_case_insensitive_test"])
result = self.connection.execute(expr)
self.assertEquals(result.get_all(), [(1, ), (2, )])
def _milestone_ids_expr(self, user):
tag_constraints = And(*[
Exists(
Select(1,
tables=[MilestoneTag],
where=And(MilestoneTag.milestone_id == Milestone.id,
MilestoneTag.tag == tag)))
for tag in self.tags
])
return Select(Milestone.id,
tables=[Milestone, Product],
where=And(Milestone.productID == Product.id,
Product.project == self.target,
tag_constraints))
def test_case_default_like(self):
like = Like(SQLRaw("description"), u"%hullah%")
expr = Select(SQLRaw("id"),
like,
tables=["like_case_insensitive_test"])
result = self.connection.execute(expr)
assert result.get_all() == [(1, )]
like = Like(SQLRaw("description"), u"%HULLAH%")
expr = Select(SQLRaw("id"),
like,
tables=["like_case_insensitive_test"])
result = self.connection.execute(expr)
assert result.get_all() == [(2, )]
def test_compile_in_select(self):
expr = Select(self.ClassAlias.prop1, self.ClassAlias.prop1 == 1,
self.ClassAlias)
statement = compile(expr)
self.assertEqual(
statement, 'SELECT alias.column1 FROM "table" AS alias '
'WHERE alias.column1 = ?')
def findBinariesForDomination(self, distroarchseries, pocket):
"""Find binary publications that need dominating.
This is only for traditional domination, where the latest published
publication is always kept published. It will ignore publications
that have no other publications competing for the same binary package.
"""
BPPH = BinaryPackagePublishingHistory
BPR = BinaryPackageRelease
bpph_location_clauses = [
BPPH.status == PackagePublishingStatus.PUBLISHED,
BPPH.distroarchseries == distroarchseries,
BPPH.archive == self.archive,
BPPH.pocket == pocket,
]
candidate_binary_names = Select(BPPH.binarypackagenameID,
And(*bpph_location_clauses),
group_by=BPPH.binarypackagenameID,
having=(Count() > 1))
main_clauses = bpph_location_clauses + [
BPR.id == BPPH.binarypackagereleaseID,
BPR.binarypackagenameID.is_in(candidate_binary_names),
BPR.binpackageformat != BinaryPackageFormat.DDEB,
]
# We're going to access the BPRs as well. Since we make the
# database look them up anyway, and since there won't be many
# duplications among them, load them alongside the publications.
# We'll also want their BinaryPackageNames, but adding those to
# the join would complicate the query.
query = IStore(BPPH).find((BPPH, BPR), *main_clauses)
bpphs = list(DecoratedResultSet(query, itemgetter(0)))
load_related(BinaryPackageName, bpphs, ['binarypackagenameID'])
return bpphs
def getMergeProposalsForReviewer(self, reviewer, status=None):
"""See `IGitCollection`."""
tables = [
BranchMergeProposal,
Join(CodeReviewVoteReference,
CodeReviewVoteReference.branch_merge_proposalID == \
BranchMergeProposal.id),
LeftJoin(CodeReviewComment,
CodeReviewVoteReference.commentID == CodeReviewComment.id)]
expressions = [
CodeReviewVoteReference.reviewer == reviewer,
BranchMergeProposal.source_git_repositoryID.is_in(
self._getRepositorySelect())
]
visibility = self._getRepositoryVisibilityExpression()
if visibility:
expressions.append(
BranchMergeProposal.target_git_repositoryID.is_in(
Select(GitRepository.id, visibility)))
if status is not None:
expressions.append(BranchMergeProposal.queue_status.is_in(status))
proposals = self.store.using(*tables).find(BranchMergeProposal,
*expressions)
# Apply sorting here as we can't do it in the browser code. We need to
# think carefully about the best places to do this, but not here nor
# now.
proposals.order_by(Desc(CodeReviewComment.vote))
return proposals
def _scopedGetMergeProposals(self, statuses, eager_load=False):
expressions = (self._filter_expressions +
self._getRepositoryVisibilityExpression())
with_expr = With(
"candidate_repositories",
Select(GitRepository.id,
tables=[GitRepository] + self._tables.values(),
where=And(*expressions) if expressions else True))
expressions = [
SQL("""
source_git_repository IN
(SELECT id FROM candidate_repositories) AND
target_git_repository IN
(SELECT id FROM candidate_repositories)""")
]
tables = [BranchMergeProposal]
if self._asymmetric_filter_expressions:
# Need to filter on GitRepository beyond the with constraints.
expressions += self._asymmetric_filter_expressions
expressions.append(BranchMergeProposal.source_git_repositoryID ==
GitRepository.id)
tables.append(GitRepository)
tables.extend(self._asymmetric_tables.values())
if statuses is not None:
expressions.append(
BranchMergeProposal.queue_status.is_in(statuses))
resultset = self.store.with_(with_expr).using(*tables).find(
BranchMergeProposal, *expressions)
if not eager_load:
return resultset
else:
loader = partial(BranchMergeProposal.preloadDataForBMPs,
user=self._user)
return DecoratedResultSet(resultset, pre_iter_hook=loader)
def test_compile_in_select_with_reserved_keyword(self):
Alias = ClassAlias(self.Class, "select")
expr = Select(Alias.prop1, Alias.prop1 == 1, Alias)
statement = compile(expr)
self.assertEqual(
statement, 'SELECT "select".column1 FROM "table" AS "select" '
'WHERE "select".column1 = ?')
def deleteMultiple(cls, ids):
from lp.bugs.model.structuralsubscription import StructuralSubscription
store = IStore(BugSubscriptionFilter)
structsub_ids = list(
store.find(BugSubscriptionFilter.structural_subscription_id,
BugSubscriptionFilter.id.is_in(ids)))
kinds = [
BugSubscriptionFilterImportance, BugSubscriptionFilterStatus,
BugSubscriptionFilterTag, BugSubscriptionFilterInformationType
]
for kind in kinds:
store.find(kind, kind.filter_id.is_in(ids)).remove()
store.find(BugSubscriptionFilter,
BugSubscriptionFilter.id.is_in(ids)).remove()
# Now delete any structural subscriptions that have no filters.
# Take out a SHARE lock on the filters that we use as evidence
# for keeping structsubs, to ensure that they haven't been
# deleted under us.
filter_expr = Select(
1,
tables=[BugSubscriptionFilter],
where=(BugSubscriptionFilter.structural_subscription_id ==
StructuralSubscription.id))
locked_filter_expr = SQL(
convert_storm_clause_to_string(filter_expr) + ' FOR SHARE')
store.find(StructuralSubscription,
StructuralSubscription.id.is_in(structsub_ids),
Not(Exists(locked_filter_expr))).remove()
def _updateTranslationMessages(self, tm_ids):
# Unset imported messages that might be in the way.
PreviousImported = ClassAlias(TranslationMessage, 'PreviousImported')
CurrentTranslation = ClassAlias(TranslationMessage,
'CurrentTranslation')
previous_imported_select = Select(
PreviousImported.id,
tables=[PreviousImported, CurrentTranslation],
where=And(
PreviousImported.is_current_upstream == True,
(PreviousImported.potmsgsetID
== CurrentTranslation.potmsgsetID),
Or(
And(PreviousImported.potemplateID == None,
CurrentTranslation.potemplateID == None),
(PreviousImported.potemplateID
== CurrentTranslation.potemplateID)),
PreviousImported.languageID == CurrentTranslation.languageID,
CurrentTranslation.id.is_in(tm_ids)))
previous_imported = self.store.find(
TranslationMessage,
TranslationMessage.id.is_in(previous_imported_select))
previous_imported.set(is_current_upstream=False)
translations = self.store.find(TranslationMessage,
TranslationMessage.id.is_in(tm_ids))
translations.set(is_current_upstream=True)
def findSourcesForDomination(self, distroseries, pocket):
"""Find binary publications that need dominating.
This is only for traditional domination, where the latest published
publication is always kept published. See `find_live_source_versions`
for this logic.
To optimize for that logic, `findSourcesForDomination` will ignore
publications that have no other publications competing for the same
binary package. There'd be nothing to do for those cases.
"""
SPPH = SourcePackagePublishingHistory
SPR = SourcePackageRelease
spph_location_clauses = self._composeActiveSourcePubsCondition(
distroseries, pocket)
candidate_source_names = Select(SPPH.sourcepackagenameID,
And(join_spph_spr(),
spph_location_clauses),
group_by=SPPH.sourcepackagenameID,
having=(Count() > 1))
# We'll also access the SourcePackageReleases associated with
# the publications we find. Since they're in the join anyway,
# load them alongside the publications.
# Actually we'll also want the SourcePackageNames, but adding
# those to the (outer) query would complicate it, and
# potentially slow it down.
query = IStore(SPPH).find(
(SPPH, SPR), join_spph_spr(),
SPPH.sourcepackagenameID.is_in(candidate_source_names),
spph_location_clauses)
spphs = DecoratedResultSet(query, itemgetter(0))
load_related(SourcePackageName, spphs, ['sourcepackagenameID'])
return spphs
def get_max_value(cls, store, attr):
"""Get the maximum value for a given attr
On text columns, trying to find the max value for them using MAX()
on postgres would result in some problems, like '9' being considered
greater than '10' (because the comparison is done from left to right).
This will 0-"pad" the values for the comparison, making it compare
the way we want. Note that because of that, in the example above,
it would return '09' instead of '9'
:para store: a store
:param attr: the attribute to find the max value for
:returns: the maximum value for the attr
"""
cls.validate_attr(attr, expected_type=UnicodeCol)
max_length = Alias(
Select(columns=[Alias(Max(CharLength(attr)), 'max_length')],
tables=[cls]), '_max_length')
# Using LPad with max_length will workaround most of the cases where
# the string comparison fails. For example, the common case would
# consider '9' to be greater than '10'. We could test just strings
# with length equal to max_length, but than '010' would be greater
# than '001' would be greater than '10' (that would be excluded from
# the comparison). By doing lpad, '09' is lesser than '10' and '001'
# is lesser than '010', working around those cases
max_batch = store.using(cls, max_length).find(cls).max(
LPad(attr, Field('_max_length', 'max_length'), u'0'))
# Make the api consistent and return an ampty string instead of None
# if there's no batch registered on the database
return max_batch or u''
def get_product_brands(self, product_id, **options):
'''
Returns product brands.
:param product_id:
:return:
'''
store = get_current_transaction_store()
statement = \
Select(columns=[ProductsBrandsEntity.product_brand,
ProductsBrandsEntity.product_brand_id],
where=And(ProductsBrandsEntity.product_id == product_id),
tables=[ProductsBrandsEntity])
results = []
for (product_brand, product_brand_id) in store.execute(statement):
results.append(
DynamicObject(product_brand=product_brand,
product_brand_id=product_brand_id))
concat_results = options.get('concat_results')
if concat_results is None:
concat_results = False
if concat_results:
return ','.join([b.product_brand for b in results])
return results
def can_remove(self, skip=None):
"""Check if this object can be removed from the database
This will check if there's any object referencing self
:param skip: an itarable containing the (table, column) to skip
the check. Use this to avoid false positives when you will
delete those skipped by hand before self.
"""
skip = skip or set()
selects = []
refs = self.store.list_references(self.__class__.id)
for t_name, c_name, ot_name, oc_name, u, d in refs:
if (t_name, c_name) in skip:
continue
column = Field(t_name, c_name)
selects.append(
Select(columns=[1], tables=[t_name],
where=column == self.id, limit=1))
# If everything was skipped, there's no query to execute
if not len(selects):
return True
# We can only pass limit=1 to UnionAll if there's more than one select.
# If not, storm will put the limit anyway and it will join with the
# select's limit producing an error: multiple LIMIT clauses not allowed
if len(selects) > 1:
extra = {'limit': 1}
else:
extra = {}
return not any(self.store.execute(UnionAll(*selects, **extra)))
def _query_server_time(store):
# Be careful, this opens up a new connection, queries the server
# and closes the connection. That takes ~150ms
date = store.execute(Select([StatementTimestamp()])).get_one()[0]
# Storm removes tzinfo on it's datetime columns. Do the same here
# or the comparison on testTimestamp will fail.
return date.replace(tzinfo=None)
def test_sequence(self):
expr1 = Select(Sequence("test_id_seq"))
expr2 = "SELECT currval('test_id_seq')"
value1 = self.connection.execute(expr1).get_one()[0]
value2 = self.connection.execute(expr2).get_one()[0]
value3 = self.connection.execute(expr1).get_one()[0]
self.assertEquals(value1, value2)
self.assertEquals(value3 - value1, 1)
def _submissionsSubmitterSelects(
self, target_column, bus, vendor_id, product_id, driver_name,
package_name, distro_target):
"""Return Select objects for statistical queries.
:return: A tuple
(select_device_related_records, select_all_records)
where select_device_related_records is a Select instance
returning target_column matching all other method
parameters, and where select_all_records is a Select
instance returning target_column and matching distro_target,
:param target_column: The records returned by the Select instance.
:param bus: The `HWBus` of the device.
:param vendor_id: The vendor ID of the device.
:param product_id: The product ID of the device.
:param driver_name: The name of the driver used for the device
(optional).
:param package_name: The name of the package the driver is a part of.
(optional).
:param distro_target: Limit the result to submissions made for the
given distribution, distroseries or distroarchseries.
(optional).
"""
tables, clauses = make_distro_target_clause(distro_target)
if HWSubmission not in tables:
tables.append(HWSubmission)
clauses.append(
HWSubmission.status == HWSubmissionProcessingStatus.PROCESSED)
all_submissions = Select(
columns=[target_column], tables=tables, where=And(*clauses),
distinct=True)
device_tables, device_clauses = (
make_submission_device_statistics_clause(
bus, vendor_id, product_id, driver_name, package_name, False))
submission_ids = Select(
columns=[HWSubmissionDevice.submissionID],
tables=device_tables, where=And(*device_clauses))
clauses.append(HWSubmission.id.is_in(submission_ids))
submissions_with_device = Select(
columns=[target_column], tables=tables, where=And(*clauses),
distinct=True)
return (submissions_with_device, all_submissions)
def test_expr_contains_string(self):
self.connection.execute("INSERT INTO test VALUES (30, 'blah_%!!x')")
self.connection.execute("INSERT INTO test VALUES (40, 'blah!!x')")
id = Column("id", SQLToken("test"))
title = Column("title", SQLToken("test"))
expr = Select(id, title.contains_string(u"_%!!"))
result = list(self.connection.execute(expr))
self.assertEquals(result, [(30, )])
def test_expr_startswith(self):
self.connection.execute("INSERT INTO test VALUES (30, '!!_%blah')")
self.connection.execute("INSERT INTO test VALUES (40, '!!blah')")
id = Column("id", SQLToken("test"))
title = Column("title", SQLToken("test"))
expr = Select(id, title.startswith(u"!!_%"))
result = list(self.connection.execute(expr))
self.assertEquals(result, [(30, )])
def db_get_public_context_list(store, language):
subselect = Select(models.ReceiverContext.context_id, distinct=True)
contexts = store.find(models.Context, models.Context.id.is_in(subselect))
data = db_prepare_contexts_serialization(store, contexts)
return [serialize_context(store, context, language, data) for context in contexts]
def test_order_by_group_by(self):
self.connection.execute("INSERT INTO test VALUES (100, 'Title 10')")
self.connection.execute("INSERT INTO test VALUES (101, 'Title 10')")
id = Column("id", "test")
title = Column("title", "test")
expr = Select(Count(id), group_by=title, order_by=Count(id))
result = self.connection.execute(expr)
self.assertEquals(result.get_all(), [(1, ), (3, )])
def getSharedProjects(self, person, user):
"""See `ISharingService`."""
commercial_filter = None
if user and IPersonRoles(user).in_commercial_admin:
commercial_filter = Exists(Select(
1, tables=CommercialSubscription,
where=CommercialSubscription.product == Product.id))
return self._getSharedPillars(person, user, Product, commercial_filter)
def test_none_on_string_variable(self):
"""
Verify that the logic to enforce fix E''-styled strings isn't
breaking on NULL values.
"""
variable = RawStrVariable(value=None)
result = self.connection.execute(Select(variable))
self.assertEquals(result.get_one(), (None, ))
