def punchlist(self, ignored = False):
"""
Return all valid 'normal' punches ordered by punchtime
@param ignored: Return punches normally ignored
@rtype: (Punch, Control) tuples
"""
# Do a direct search in the store for Punch, Control tuples. This is much faster
# than first fetching punches from self.punches and then getting their controls via
# punch.sistation.control
punch_cond = And(Punch.ignore != True,
Func('COALESCE', Punch.manual_punchtime, Punch.card_punchtime)
> (self.start_time or datetime.min),
Func('COALESCE', Punch.manual_punchtime, Punch.card_punchtime)
< (self.finish_time or datetime.max),
Not(SIStation.control == None),
)
if ignored is True:
punch_cond = Not(punch_cond)
return list(self._store.using(Join(Punch, SIStation, Punch.sistation == SIStation.id),
LeftJoin(Control, SIStation.control == Control.id)
).find((Punch, Control),
punch_cond,
Punch.run == self.id,
).order_by(Func('COALESCE',
Punch.manual_punchtime,
Punch.card_punchtime))
)
def extractTrendingHashtags(store, limit=10, duration=None):
"""Extract information about trending hashtags and store it in FluidDB.
@param store: The storm store to query and to save our result to.
@param limit: Optionally, the number of objects to retrieve.
@param duration: Optionally, the recent time period to look at when
determining which hashtags are trending. Default is 28 days.
The storm query below results in SQL like:
SELECT COUNT(DISTINCT comments.object_id) AS count,
about_tag_values.value,
array_agg(ROW(comments.username, comments.creation_time))
FROM about_tag_values, comment_object_link, comments
WHERE about_tag_values.value LIKE '#%' AND
about_tag_values.object_id = comment_object_link.object_id AND
comments.object_id = comment_object_link.comment_id AND
comments.creation_time >= '2012-11-09 07:42:40'::TIMESTAMP AND
CHAR_LENGTH(about_tag_values.value) >= 2
GROUP BY about_tag_values.value
ORDER BY count DESC
LIMIT 10
"""
duration = timedelta(days=28) if duration is None else duration
startTime = datetime.utcnow() - duration
count = Alias(Count(Comment.objectID, distinct=True))
result = store.find(
(count, AboutTagValue.value,
Func('array_agg', Row(Comment.username, Comment.creationTime))),
Like(AboutTagValue.value,
u'#%'), AboutTagValue.objectID == CommentObjectLink.objectID,
Comment.objectID == CommentObjectLink.commentID,
Comment.creationTime >= startTime,
Func('CHAR_LENGTH', AboutTagValue.value) >= 2)
result.group_by(AboutTagValue.value)
result.order_by(Desc(count))
result.config(limit=limit)
data = [{
'count': count,
'usernames': _sortUsernames(usernames),
'value': hashtag
} for count, hashtag, usernames in result]
user = getUser(u'fluidinfo.com')
tagValues = TagValueAPI(user)
objectID = ObjectAPI(user).create(u'fluidinfo.com')
tagValues.set(
{objectID: {
u'fluidinfo.com/trending-hashtags': json.dumps(data)
}})
store.commit()
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_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 authorCount(self):
"""See `IRevisionCollection`."""
# Revision authors that are linked to Launchpad people are only
# counted once even if the revision text that they use in the commit
# is different.
author = Func('coalesce', RevisionAuthor.personID,
SQL(0) - RevisionAuthor.id)
expressions = [RevisionCache.revision_author == RevisionAuthor.id]
expressions.extend(self._filter_expressions)
result_set = self.store.find(author, expressions)
result_set.config(distinct=True)
return result_set.count()
def consecutive_failure_count(self):
"""See `ICodeImport`."""
# This SQL translates as "how many code import results have there been
# for this code import since the last successful one".
# This is not very efficient for long lists of code imports.
last_success = Func(
"coalesce",
Select(CodeImportResult.id,
And(
CodeImportResult.status.is_in(
CodeImportResultStatus.successes),
CodeImportResult.code_import == self),
order_by=Desc(CodeImportResult.id),
limit=1), 0)
return Store.of(self).find(CodeImportResult,
CodeImportResult.code_import == self,
CodeImportResult.id > last_success).count()
def getProductsWithInfo(num_products=None):
"""See `IBranchCloud`."""
distinct_revision_author = Func("distinct",
RevisionCache.revision_author_id)
commits = Alias(Count(RevisionCache.revision_id))
epoch = datetime.now(pytz.UTC) - timedelta(days=30)
# It doesn't matter if this query is even a whole day out of date, so
# use the slave store.
result = ISlaveStore(RevisionCache).find(
(Product.name, commits, Count(distinct_revision_author),
Max(RevisionCache.revision_date)),
RevisionCache.product == Product.id, Not(RevisionCache.private),
RevisionCache.revision_date >= epoch)
result = result.group_by(Product.name)
result = result.order_by(Desc(commits))
if num_products:
result.config(limit=num_products)
return result
def getBestMirrorsForCountry(self, country, mirror_type):
"""See IDistributionMirrorSet"""
# As per mvo's request we only return mirrors which have an
# http_base_url.
country_id = None
if country is not None:
country_id = country.id
base_query = And(DistributionMirror.content == mirror_type,
DistributionMirror.enabled == True,
DistributionMirror.http_base_url != None,
DistributionMirror.official_candidate == True,
DistributionMirror.status == MirrorStatus.OFFICIAL)
query = And(DistributionMirror.countryID == country_id, base_query)
# The list of mirrors returned by this method is fed to apt through
# launchpad.net, so we order the results randomly in a lame attempt to
# balance the load on the mirrors.
order_by = [Func('random')]
mirrors = shortlist(DistributionMirror.select(query, orderBy=order_by),
longest_expected=200)
if not mirrors and country is not None:
continent = country.continent
query = And(Country.q.continentID == continent.id,
DistributionMirror.countryID == Country.q.id,
base_query)
mirrors.extend(
shortlist(DistributionMirror.select(query, orderBy=order_by),
longest_expected=300))
if mirror_type == MirrorContent.ARCHIVE:
main_mirror = getUtility(
ILaunchpadCelebrities).ubuntu_archive_mirror
elif mirror_type == MirrorContent.RELEASE:
main_mirror = getUtility(
ILaunchpadCelebrities).ubuntu_cdimage_mirror
else:
raise AssertionError("Unknown mirror type: %s" % mirror_type)
assert main_mirror is not None, 'Main mirror was not found'
if main_mirror not in mirrors:
mirrors.append(main_mirror)
return mirrors