def get_by_unique_key(self, unique_key, name, default=None):
pkey = (unique_key, name)
session = DBSession()
# Eager load related resources here.
key = session.query(Key).options(
orm.joinedload_all(
Key.resource,
Resource.data,
CurrentPropertySheet.propsheet,
innerjoin=True,
),
orm.joinedload_all(
Key.resource,
Resource.rels,
Link.target,
Resource.data,
CurrentPropertySheet.propsheet,
),
).get(pkey)
if key is None:
return default
model = key.resource
if model.item_type != self.item_type:
return default
return self.Item(self, model)
def show_ballot_room_editor(ballot_id, ballot_type_name, db):
ballot_type = db.query(m.BallotType).filter(func.lower(m.BallotType.name) == ballot_type_name.lower()).one()
ballot_eventsq = (db
.query(m.BallotEvent)
.join(m.BallotSeason)
.filter(m.BallotEvent.type == ballot_type)
.filter(m.BallotSeason.year <= ballot_id)
.order_by(m.BallotSeason.year.desc())
.limit(2)
)
ballot_events = ballot_eventsq.all()
if ballot_events[0].season.year != ballot_id:
raise HTTPError(404, "No {} ballot for the {} season {} {}".format(ballot_type.name, ballot_id, ballot_eventsq, ballot_events))
else:
ballot = ballot_events[0]
if len(ballot_events) == 2:
last_ballot = ballot_events[1]
else:
last_ballot = None
root = db.query(m.Place).options(
joinedload_all('children.rooms.listing_for'),
joinedload_all('children.children.rooms.listing_for'),
joinedload_all('children.children.children.rooms.listing_for'),
joinedload_all('children.children.children.children.rooms.listing_for'),
).filter(m.Place.parent == None).one()
return template('ballot-event-edit-rooms',
ballot_event=ballot,
last_ballot_event=last_ballot,
root=root)
def __call__(self, model, req):
query = req.db.query(model).filter(model.id == req.matchdict['id'])
custom_query = self.refined_query(query, model, req)
if query == custom_query:
# no customizations done, apply the defaults
if model == common.Contribution:
query = query.options(
joinedload_all(
common.Contribution.valuesets,
common.ValueSet.parameter,
),
joinedload_all(
common.Contribution.valuesets,
common.ValueSet.values,
common.Value.domainelement),
joinedload_all(
common.Contribution.references,
common.ContributionReference.source),
joinedload(common.Contribution.data),
)
if model == common.ValueSet:
query = query.options(
joinedload(common.ValueSet.values),
joinedload(common.ValueSet.parameter),
joinedload(common.ValueSet.language),
)
else:
query = custom_query # pragma: no cover
return query.one()
def get_attribute_data(attr_ids, node_ids, **kwargs):
"""
For a given attribute or set of attributes, return all the resources and
resource scenarios in the network
"""
node_attrs = db.DBSession.query(ResourceAttr).\
options(joinedload_all('attr')).\
filter(ResourceAttr.node_id.in_(node_ids),
ResourceAttr.attr_id.in_(attr_ids)).all()
ra_ids = []
for ra in node_attrs:
ra_ids.append(ra.resource_attr_id)
resource_scenarios = db.DBSession.query(ResourceScenario).filter(
ResourceScenario.resource_attr_id.in_(ra_ids)).options(
joinedload('resourceattr')).options(
joinedload_all('dataset.metadata')).order_by(
ResourceScenario.scenario_id).all()
for rs in resource_scenarios:
if rs.dataset.hidden == 'Y':
try:
rs.dataset.check_read_permission(kwargs.get('user_id'))
except:
rs.dataset.value = None
rs.dataset.frequency = None
rs.dataset.start_time = None
db.DBSession.expunge(rs)
return node_attrs, resource_scenarios
def __call__(self, model, req):
query = req.db.query(model).filter(model.id == req.matchdict['id'])
custom_query = self.refined_query(query, model, req)
if query == custom_query:
# no customizations done, apply the defaults
if model == common.Contribution:
query = query.options(
joinedload_all(
common.Contribution.valuesets,
common.ValueSet.parameter,
),
joinedload_all(common.Contribution.valuesets,
common.ValueSet.values,
common.Value.domainelement),
joinedload_all(common.Contribution.references,
common.ContributionReference.source),
joinedload(common.Contribution.data),
)
if model == common.ValueSet:
query = query.options(
joinedload(common.ValueSet.values),
joinedload(common.ValueSet.parameter),
joinedload(common.ValueSet.language),
)
else:
query = custom_query # pragma: no cover
return query.one()
def query(self, req):
self._domainelements = DBSession.query(DomainElement).all()
return DBSession.query(Language)\
.order_by(Language.id)\
.options(
joinedload_all(Language.valuesets, ValueSet.values),
joinedload_all(WalsLanguage.genus, Genus.family))
def by_well_tag(self):
well_tag_field = fl.well_tag_field(str(self.form_result['well_tag']))
c.group_by_plate = self.form_result['group_by_plate']
c.tag_id = self.form_result['well_tag']
c.tag_name = Session.query(WellTag).get(c.tag_id).name
c.form = h.LiteralFormSelectPatch(
value = {'well_tag': well_tag_field['value'],
'group_by_plate': [u'1' if c.group_by_plate else u'0']},
option = {'well_tag': [('--','--')]+well_tag_field['options'],
'group_by_plate': [(u'1', '')]}
)
well_tags = Session.query(WellTag).\
filter_by(id=c.tag_id).\
options(joinedload_all(WellTag.tag_wells, QLBWell.plate, QLBPlate.file, innerjoin=True),
joinedload_all(WellTag.tag_wells, QLBWell.plate, QLBPlate.plate, innerjoin=True)).\
all()
c.label_names = []
if not len(well_tags):
c.wells = []
c.well_groups = []
elif c.group_by_plate:
wells = sorted(well_tags[0].wells, key=lambda well: (well.plate_id, well.well_name))
well_groups = [(plate, list(wells)) for plate, wells in itertools.groupby(wells, lambda well: well.plate)]
c.well_groups = sorted(well_groups, key=lambda tup: tup[0].host_datetime)
c.well_groups.reverse()
else:
c.wells = sorted(well_tags[0].wells, key=lambda well: well.host_datetime)
c.wells.reverse()
return render('/box2/by_well_tag.html')
def user(request, info, session, userid):
u = session\
.query(User)\
.get(int(userid))
if not u:
raise Http404
sales = session\
.query(Transline)\
.filter(Transline.user == u)\
.options(joinedload('transaction'),
joinedload_all('stockref.stockitem.stocktype.unit'))\
.order_by(desc(Transline.time))[:50]
payments = session\
.query(Payment)\
.filter(Payment.user == u)\
.options(joinedload('transaction'),
joinedload('paytype'))\
.order_by(desc(Payment.time))[:50]
annotations = session\
.query(StockAnnotation)\
.options(joinedload_all('stockitem.stocktype'),
joinedload('type'))\
.filter(StockAnnotation.user == u)\
.order_by(desc(StockAnnotation.time))[:50]
return ('user.html', {
'tillobject': u,
'tuser': u,
'sales': sales,
'payments': payments,
'annotations': annotations,
})
def index(self, *args, **kw):
query = Watchdog.by_status(status=u'active')\
.join(Watchdog.recipe).join(Recipe.recipeset).join(RecipeSet.job)\
.order_by(Job.id)\
.options(
joinedload_all(Watchdog.recipe, Recipe.recipeset, RecipeSet.job),
joinedload_all(Watchdog.recipe, Recipe.recipeset, RecipeSet.lab_controller),
joinedload_all(Watchdog.recipetask, RecipeTask.task))
col = myPaginateDataGrid.Column
fields = [
col(name='job_id',
getter=lambda x: x.recipe.recipeset.job.link,
title="Job ID"),
col(name='system_name',
getter=lambda x: x.recipe.resource.link,
title="System"),
col(name='lab_controller',
getter=lambda x: x.recipe.recipeset.lab_controller,
title="Lab Controller"),
col(name='task_name',
getter=lambda x: x.recipetask.name_markup
if x.recipetask is not None else None,
title="Task Name"),
col(name='kill_time',
getter=lambda x: x.kill_time,
title="Kill Time",
options=dict(datetime=True))
]
watchdog_grid = myPaginateDataGrid(fields=fields)
return dict(title="Watchdogs",
grid=watchdog_grid,
search_bar=None,
list=query)
def show_ballot_room_editor(ballot_id, ballot_type_name, db):
ballot_type = db.query(m.BallotType).filter(
func.lower(m.BallotType.name) == ballot_type_name.lower()).one()
ballot_eventsq = (db.query(m.BallotEvent).join(
m.BallotSeason).filter(m.BallotEvent.type == ballot_type).filter(
m.BallotSeason.year <= ballot_id).order_by(
m.BallotSeason.year.desc()).limit(2))
ballot_events = ballot_eventsq.all()
if ballot_events[0].season.year != ballot_id:
raise HTTPError(
404, "No {} ballot for the {} season {} {}".format(
ballot_type.name, ballot_id, ballot_eventsq,
ballot_events))
else:
ballot = ballot_events[0]
if len(ballot_events) == 2:
last_ballot = ballot_events[1]
else:
last_ballot = None
root = db.query(m.Place).options(
joinedload_all('children.rooms.listing_for'),
joinedload_all('children.children.rooms.listing_for'),
joinedload_all('children.children.children.rooms.listing_for'),
joinedload_all(
'children.children.children.children.rooms.listing_for'),
).filter(m.Place.parent == None).one()
return template('ballot-event-edit-rooms',
ballot_event=ballot,
last_ballot_event=last_ballot,
root=root)
def user(request, info, session, userid):
u = session\
.query(User)\
.get(int(userid))
if not u:
raise Http404
sales = session\
.query(Transline)\
.filter(Transline.user == u)\
.options(joinedload('transaction'),
joinedload_all('stockref.stockitem.stocktype.unit'))\
.order_by(desc(Transline.time))[:50]
payments = session\
.query(Payment)\
.filter(Payment.user == u)\
.options(joinedload('transaction'),
joinedload('paytype'))\
.order_by(desc(Payment.time))[:50]
annotations = session\
.query(StockAnnotation)\
.options(joinedload_all('stockitem.stocktype'),
joinedload('type'))\
.filter(StockAnnotation.user == u)\
.order_by(desc(StockAnnotation.time))[:50]
return ('user.html',
{'tillobject': u,
'tuser': u,
'sales': sales,
'payments': payments,
'annotations': annotations,
})
def admin_search_candidates(self):
params = self.request.params
status = params.get('status')
order = params.get('order')
q = params.get('q')
tags = split_strip(params.get('tags'))
basequery = DBSession.query(SearchResultCandidate) \
.options(joinedload_all('languages.language'), joinedload_all('languages.proficiency'),
joinedload_all('skills.skill'), joinedload_all('skills.level'),
joinedload('preferred_locations'),
joinedload('target_position'))
if status:
status = get_by_name_or_raise(CandidateStatus, status)
basequery = basequery.filter(Candidate.status == status)
if q:
q = q.lower()
basequery = basequery.filter(
or_(func.lower(Candidate.first_name).startswith(q),
func.lower(Candidate.last_name).startswith(q),
func.lower(func.concat(Candidate.first_name, " ", Candidate.last_name)).startswith(q),
func.lower(Candidate.email).startswith(q)))
if tags:
basequery = basequery.outerjoin(CandidateSkill).join(Skill).filter(Skill.name.in_(tags))
if order:
basequery = add_sorting(basequery, order, CANDIDATE_SORTABLES)
return run_paginated_query(self.request, basequery, counter=distinct_counter(SearchResultCandidate.id))
def refined_query(self, query, model, req):
if model == common.Contribution:
query = query.options(
joinedload_all(
common.Contribution.valuesets,
common.ValueSet.parameter,
),
joinedload_all(
common.Contribution.valuesets,
common.ValueSet.values,
common.Value.domainelement),
joinedload_all(
common.Contribution.valuesets,
common.ValueSet.values,
common.Value.sentence_assocs,
common.ValueSentence.sentence),
joinedload(ApicsContribution.language),
)
if model == common.Parameter:
query = query.options(
joinedload_all(
common.Parameter.valuesets,
common.ValueSet.values,
),
joinedload_all(
common.Parameter.valuesets,
common.ValueSet.language,
),
)
return query
def user(request, info, session, userid):
try:
u = session.query(User).options(joinedload("permissions")).options(joinedload("tokens")).get(int(userid))
except NoResultFound:
raise Http404
sales = (
session.query(Transline)
.filter(Transline.user == u)
.options(joinedload("transaction"))
.options(joinedload_all("stockref.stockitem.stocktype.unit"))
.order_by(desc(Transline.time))[:50]
)
payments = (
session.query(Payment)
.filter(Payment.user == u)
.options(joinedload("transaction"))
.options(joinedload("paytype"))
.order_by(desc(Payment.time))[:50]
)
annotations = (
session.query(StockAnnotation)
.options(joinedload_all("stockitem.stocktype"))
.options(joinedload("type"))
.filter(StockAnnotation.user == u)
.order_by(desc(StockAnnotation.time))[:50]
)
return ("user.html", {"tuser": u, "sales": sales, "payments": payments, "annotations": annotations})
def base_query(self, query):
query = super(Phonemes, self).base_query(query)
if self.parameter:
query = query.join(ValueSet.contribution).options(
joinedload_all(Value.valueset, ValueSet.language),
joinedload_all(Value.valueset, ValueSet.contribution))
return query
def get_one(cls, id_):
query = cls.query.options(joinedload(cls.pool),
joinedload(cls.storage),
joinedload_all('jobs.status'),
joinedload_all('jobs.client'),
).get(int(id_))
return super(Media, cls).get_one(query=query)
def list(self):
offers = DBSession.query(EmployerOffer).filter(EmployerOffer.employer_id == self.employer.id) \
.options(joinedload_all('candidate.skills.skill'),
joinedload_all('candidate.skills.level'))
if self.request.params.get('status') == 'active':
offers = offers.filter(Offer.by_active(False))
return offers.all()
def reader_history(self, id=None, admin=True):
box2 = self.__setup_box2_context_by_code(id)
c.admin = admin != 'False'
logs = Session.query(Box2Log).filter_by(box2_id=box2.id)\
.order_by('time_effective desc')\
.options(joinedload_all(Box2Log.circuit))\
.all()
statuses = Session.query(DRStatusLog).filter_by(box2_id=box2.id)\
.order_by('time_effective desc')\
.options(joinedload_all(DRStatusLog.reporter))\
.all()
fixes = Session.query(DRFixLog).filter_by(box2_id=box2.id)\
.order_by('time_effective desc')\
.all()
log_pairs = [(logs[i].time_effective, [logs[i],(logs[i+1] if i < len(logs)-1 else None)]) for i in range(len(logs))]
for pair in log_pairs:
pair[1].append((sorted(box2log_mv.labeleditems(pair[1][0]).items()),
sorted(box2log_mv.labeleditems(pair[1][1]).items())))
status_pairs = [(status.time_effective, status) for status in statuses]
fix_pairs = [(fix.time_effective, fix) for fix in fixes]
changes = log_pairs + status_pairs + fix_pairs
c.changes = sorted(changes, key=operator.itemgetter(0))
c.changes.reverse()
return render('/admin/reader_history.html')
def _get_ideas_real(discussion, view_def=None, ids=None, user_id=None):
user_id = user_id or Everyone
# optimization: Recursive widget links.
from assembl.models import (
Widget, IdeaWidgetLink, IdeaDescendantsShowingWidgetLink)
universal_widget_links = []
by_idea_widget_links = defaultdict(list)
widget_links = discussion.db.query(IdeaWidgetLink
).join(Widget).join(Discussion).filter(
Widget.test_active(), Discussion.id == discussion.id,
IdeaDescendantsShowingWidgetLink.polymorphic_filter()
).options(joinedload_all(IdeaWidgetLink.idea)).all()
for wlink in widget_links:
if isinstance(wlink.idea, RootIdea):
universal_widget_links.append({
'@type': wlink.external_typename(),
'widget': Widget.uri_generic(wlink.widget_id)})
else:
for id in wlink.idea.get_all_descendants(True):
by_idea_widget_links[Idea.uri_generic(id)].append({
'@type': wlink.external_typename(),
'widget': Widget.uri_generic(wlink.widget_id)})
next_synthesis = discussion.get_next_synthesis()
ideas = discussion.db.query(Idea).filter_by(
discussion_id=discussion.id
)
ideas = ideas.outerjoin(SubGraphIdeaAssociation,
and_(SubGraphIdeaAssociation.sub_graph_id==next_synthesis.id, SubGraphIdeaAssociation.idea_id==Idea.id)
)
ideas = ideas.outerjoin(IdeaLink,
and_(IdeaLink.target_id==Idea.id)
)
ideas = ideas.order_by(IdeaLink.order, Idea.creation_date)
if ids:
ids = [get_database_id("Idea", id) for id in ids]
ideas = ideas.filter(Idea.id.in_(ids))
# remove tombstones
ideas = ideas.filter(and_(*Idea.base_conditions()))
ideas = ideas.options(
joinedload_all(Idea.source_links),
joinedload_all(Idea.has_showing_widget_links),
undefer(Idea.num_children))
permissions = get_permissions(user_id, discussion.id)
Idea.prepare_counters(discussion.id, True)
retval = [idea.generic_json(view_def, user_id, permissions)
for idea in ideas]
retval = [x for x in retval if x is not None]
for r in retval:
if r.get('widget_links', None) is not None:
links = r['widget_links'][:]
links.extend(universal_widget_links)
links.extend(by_idea_widget_links[r['@id']])
r['active_widget_links'] = links
return retval
def home(fmt=None, page=1):
flags = (
g.db.query(SpamFlag)
.order_by(SpamFlag.id.desc())
.options(
joinedload_all(SpamFlag.message, Message.chat),
joinedload_all(SpamFlag.message, Message.chat_user),
joinedload_all(SpamFlag.message, Message.user),
)
.offset((page - 1) * 50).limit(50).all()
)
if not flags and page != 1:
abort(404)
flag_count = g.db.query(func.count("*")).select_from(SpamFlag).scalar()
if fmt == "json":
return jsonify({
"flags": [_.to_dict() for _ in flags],
})
paginator = paginate.Page(
[],
page=page,
items_per_page=50,
item_count=flag_count,
url_maker=lambda page: url_for("spamless2_home", page=page, **request.args),
)
return render_template(
"admin/spamless2/home.html",
flags=flags,
paginator=paginator,
)
def get_one(cls, id_):
query = cls.query.options(joinedload(cls.pool),
joinedload(cls.storage),
joinedload_all('jobs.status'),
joinedload_all('jobs.client'),
).get(int(id_))
return super(Media, cls).get_one(query=query)
def query(self, req):
self._domainelements = DBSession.query(DomainElement).all()
return DBSession.query(Language)\
.order_by(Language.id)\
.options(
joinedload_all(Language.valuesets, ValueSet.values),
joinedload_all(WalsLanguage.genus, Genus.family))
def base_query(self, query):
query = query.join(ValueSet).options(
joinedload(Value.valueset), joinedload_all(Counterpart.references, CounterpartReference.source)
)
if self.language:
query = (
query.join(ValueSet.parameter)
.join(ValueSet.contribution)
.options(
joinedload(Value.valueset, ValueSet.contribution), joinedload(Value.valueset, ValueSet.parameter)
)
)
return query.filter(ValueSet.language_pk == self.language.pk)
if self.parameter:
query = (
query.join(ValueSet.language)
.outerjoin(LexibankLanguage.family)
.options(joinedload_all(Value.valueset, ValueSet.language, LexibankLanguage.family))
)
return query.filter(ValueSet.parameter_pk == self.parameter.pk)
if self.contribution:
query = query.join(ValueSet.parameter)
return query.filter(ValueSet.contribution_pk == self.contribution.pk)
return (
query.join(ValueSet.parameter)
.join(ValueSet.language)
.options(joinedload(Value.valueset, ValueSet.parameter), joinedload(Value.valueset, ValueSet.language))
)
def _floating_ip_get_by_address(context, address, session,
load_instances=True, use_first=True,
use_baked=False):
"""This is a port of nova.db.sqlalchemy.api._floating_ip_get_by_address.
It includes conditionals which select for the behaviors that are currently
within the function vs. alternate behaviors that feature better
optimization.
"""
if use_baked:
result = model_query_baked(context, models.FloatingIp, session=session)
result.bake(lambda query:
query.filter_by(
address=bindparam('address'))).params(address=address)
else:
result = model_query_ordinary(
context, models.FloatingIp, session=session).\
filter_by(address=address)
if load_instances:
# the current source for _floating_ip_get_by_address includes
# an unconditional joinedload two levels deep. In the case
# of floating_ip_update and most likely floating_ip_fixed_ip_associate,
# the rows returned and processed by these joins are not used.
#
# The overhead of this joinedload is by far the biggest hindrance
# to the performance of these two API functions - it multiplies the
# measured function call count / time spent by a factor of twelve.
# So getting rid of eager loads that aren't needed is a very easy
# and effective way to regain significant speed. In this case,
# _floating_ip_get_by_address should accept a flag as to whether
# the extended load of ->fixed_ip->instance is needed or not.
if use_baked:
result.bake(lambda query:
query.options(joinedload_all('fixed_ip.instance')))
else:
result = result.options(joinedload_all('fixed_ip.instance'))
if use_baked:
result = result.all()
if not result:
raise Exception("floating ip not found: %s" % address)
else:
result = result[0]
elif use_first:
result = result.first()
if not result:
raise Exception("floating ip not found: %s" % address)
else:
try:
result = result.one()
except orm_exc.NoResultFound:
raise Exception("floating ip not found: %s" % address)
return result
def query(self, req):
self._domainelements = DBSession.query(DomainElement).all()
return DBSession.query(Language)\
.order_by(Language.id)\
.options(
subqueryload_all('languageidentifier', 'identifier'),
subqueryload_all('countries'),
joinedload_all(Language.valuesets, ValueSet.values),
joinedload_all(WalsLanguage.genus, Genus.family))
def query(self, req):
self._domainelements = DBSession.query(DomainElement).all()
return DBSession.query(Language)\
.order_by(Language.id)\
.options(
subqueryload_all('languageidentifier', 'identifier'),
subqueryload_all('countries'),
joinedload_all(Language.valuesets, ValueSet.values),
joinedload_all(WalsLanguage.genus, Genus.family))
def changes(request):
"""
select vs.id, v.updated, h.domainelement_pk, v.domainelement_pk from value_history \
as h, value as v, valueset as vs where h.pk = v.pk and v.valueset_pk = vs.pk;
"""
# changes in the 2011 edition: check values with an updated date after 2011 and
# before 2013
E2009 = utc.localize(datetime(2009, 1, 1))
E2012 = utc.localize(datetime(2012, 1, 1))
E2014 = utc.localize(datetime(2014, 6, 30))
E2015 = utc.localize(datetime(2015, 6, 30))
history = inspect(Value.__history_mapper__).class_
query = DBSession.query(Value)\
.outerjoin(history, Value.pk == history.pk)\
.join(ValueSet)\
.order_by(ValueSet.parameter_pk, ValueSet.language_pk)\
.options(joinedload_all(Value.valueset, ValueSet.language),
joinedload_all(Value.valueset, ValueSet.parameter))
changes2011 = query.join(ValueSet.parameter)\
.filter(Parameter.id.contains('A'))\
.filter(Parameter.id != '143A')\
.filter(Parameter.id != '144A')\
.filter(or_(
and_(E2009 < Value.updated, Value.updated < E2012),
and_(history.updated != None,
E2009 < history.updated, history.updated < E2012)))
changes2013 = query.filter(
or_(and_(E2012 < Value.updated, Value.updated < E2014),
and_(E2012 < history.updated, history.updated < E2014)))
changes2014 = query.filter(
or_(and_(E2014 < Value.updated, Value.updated < E2015),
and_(E2014 < history.updated, history.updated < E2015)))
#
# TODO:
#
# history = inspect(ValueSet.__history_mapper__).class_
# current = DBSession.query(ValueSet.pk).subquery()
# removals2013 = DBSession.query(Parameter.id, Parameter.name, count(history.pk))\
# .filter(Parameter.pk == history.parameter_pk)\
# .filter(not_(history.pk.in_(current)))\
# .group_by(Parameter.pk, Parameter.id, Parameter.name)\
# .order_by(Parameter.pk)
grouped = lambda changes: groupby([v.valueset for v in changes2011], lambda
vs: vs.parameter)
return {
'changes2011': grouped(changes2011),
'changes2013': grouped(changes2013),
'changes2014': grouped(changes2014),
'removals2013': []
}
def stockline(request, info, session, stocklineid):
s = session\
.query(StockLine)\
.options(joinedload_all('stockonsale.stocktype.unit'),
joinedload_all('stockonsale.delivery'),
undefer_qtys('stockonsale'))\
.get(int(stocklineid))
if not s:
raise Http404
return ('stockline.html', {'stockline': s})
def delivery(request, info, session, deliveryid):
d = session\
.query(Delivery)\
.options(joinedload_all('items.stocktype.unit'),
joinedload_all('items.stockline'),
undefer_qtys('items'))\
.get(int(deliveryid))
if not d:
raise Http404
return ('delivery.html', {'delivery': d})
def get_test_template_by_id(self, id_):
# type: (int) -> TestTemplate
res = self._db.query(TestTemplate).options(
joinedload_all(TestTemplate.questions, TestQuestionTemplate.solution_code_snippet, CodeSnippet.function,
Function.arguments),
joinedload_all(TestTemplate.questions, TestQuestionTemplate.solution_code_snippet, CodeSnippet.function,
Function.testing_input)) \
.filter(TestTemplate.id == id_) \
.first()
return res
def pubroot(request, info, session):
date = datetime.date.today()
# If it's the early hours of the morning, it's more useful for us
# to consider it still to be yesterday.
if datetime.datetime.now().hour < 4:
date = date - datetime.timedelta(1)
thisweek_start = date - datetime.timedelta(date.weekday())
thisweek_end = thisweek_start + datetime.timedelta(6)
lastweek_start = thisweek_start - datetime.timedelta(7)
lastweek_end = thisweek_end - datetime.timedelta(7)
weekbefore_start = lastweek_start - datetime.timedelta(7)
weekbefore_end = lastweek_end - datetime.timedelta(7)
weeks = [
("Current week", thisweek_start, thisweek_end, business_totals(session, thisweek_start, thisweek_end)),
("Last week", lastweek_start, lastweek_end, business_totals(session, lastweek_start, lastweek_end)),
(
"The week before last",
weekbefore_start,
weekbefore_end,
business_totals(session, weekbefore_start, weekbefore_end),
),
]
currentsession = Session.current(session)
barsummary = (
session.query(StockLine)
.filter(StockLine.location == "Bar")
.order_by(StockLine.dept_id, StockLine.name)
.options(joinedload_all("stockonsale.stocktype.unit"))
.options(undefer_group("qtys"))
.all()
)
stillage = (
session.query(StockAnnotation)
.join(StockItem)
.outerjoin(StockLine)
.filter(
tuple_(StockAnnotation.text, StockAnnotation.time).in_(
select(
[StockAnnotation.text, func.max(StockAnnotation.time)], StockAnnotation.atype == "location"
).group_by(StockAnnotation.text)
)
)
.filter(StockItem.finished == None)
.order_by(StockLine.name != null(), StockAnnotation.time)
.options(joinedload_all("stockitem.stocktype.unit"))
.options(joinedload_all("stockitem.stockline"))
.options(undefer_group("qtys"))
.all()
)
return (
"index.html",
{"currentsession": currentsession, "barsummary": barsummary, "stillage": stillage, "weeks": weeks},
)
def changes(request):
"""
select vs.id, v.updated, h.domainelement_pk, v.domainelement_pk from value_history \
as h, value as v, valueset as vs where h.pk = v.pk and v.valueset_pk = vs.pk;
"""
# changes in the 2011 edition: check values with an updated date after 2011 and
# before 2013
E2009 = utc.localize(datetime(2009, 1, 1))
E2012 = utc.localize(datetime(2012, 1, 1))
E2014 = utc.localize(datetime(2014, 6, 30))
E2015 = utc.localize(datetime(2015, 6, 30))
history = inspect(Value.__history_mapper__).class_
query = DBSession.query(Value)\
.outerjoin(history, Value.pk == history.pk)\
.join(ValueSet)\
.order_by(ValueSet.parameter_pk, ValueSet.language_pk)\
.options(joinedload_all(Value.valueset, ValueSet.language),
joinedload_all(Value.valueset, ValueSet.parameter))
changes2011 = query.join(ValueSet.parameter)\
.filter(Parameter.id.contains('A'))\
.filter(Parameter.id != '143A')\
.filter(Parameter.id != '144A')\
.filter(or_(
and_(E2009 < Value.updated, Value.updated < E2012),
and_(history.updated != None,
E2009 < history.updated, history.updated < E2012)))
changes2013 = query.filter(or_(
and_(E2012 < Value.updated, Value.updated < E2014),
and_(E2012 < history.updated, history.updated < E2014)))
changes2014 = query.filter(or_(
and_(E2014 < Value.updated, Value.updated < E2015),
and_(E2014 < history.updated, history.updated < E2015)))
#
# TODO:
#
# history = inspect(ValueSet.__history_mapper__).class_
# current = DBSession.query(ValueSet.pk).subquery()
# removals2013 = DBSession.query(Parameter.id, Parameter.name, count(history.pk))\
# .filter(Parameter.pk == history.parameter_pk)\
# .filter(not_(history.pk.in_(current)))\
# .group_by(Parameter.pk, Parameter.id, Parameter.name)\
# .order_by(Parameter.pk)
grouped = lambda changes: groupby([v.valueset for v in changes2011],
lambda vs: vs.parameter)
return {
'changes2011': grouped(changes2011),
'changes2013': grouped(changes2013),
'changes2014': grouped(changes2014),
'removals2013': []}
def battles(clan):
"""
Table of all battles of a clan.
:param clan:
:return:
"""
if not clan in config.CLAN_NAMES:
abort(404)
battles = Battle.query.options(joinedload_all('battle_group.battles')).options(
joinedload_all('attendances.player')).filter_by(clan=clan).all()
return render_template('battles/battles.html', clan=clan, battles=battles)
def _get_scenario(scenario_id, include_data=True, include_items=True):
try:
scenario_qry = DBSession.query(Scenario).filter(Scenario.scenario_id==scenario_id)
if include_data is True:
scenario_qry = scenario_qry.options(joinedload_all('resourcescenarios'))
if include_items is True:
scenario_qry = scenario_qry.options(joinedload_all('resourcegroupitems'))
scenario = scenario_qry.one()
return scenario
except NoResultFound:
raise ResourceNotFoundError("Scenario %s does not exist."%(scenario_id))
def get_quality_wells(weeks_ago=0, channels=False):
exclude_quality_names = ('3nM FAM, 3nM VIC','3 nM FAM, 0 nM VIC','Stealth','0 nM FAM, 3 nM VIC')
# exclude beta apps plates (not sure what they are, not labeled systematically)
beta_app_setup = Session.query(PlateType).filter_by(code='bapp').first()
wells = analyzed_well_query(
setup_filter(weeks_ago_filter(weeks_ago)).all(), channels=channels
).filter(Plate.plate_type_id != beta_app_setup.id).\
options(joinedload_all(QLBWell.plate, QLBPlate.plate, Plate.plate_type, innerjoin=True),
joinedload_all(QLBWell.plate, QLBPlate.plate, Plate.box2, innerjoin=True)).all()
return [w for w in wells if w.sample_name not in exclude_quality_names]
def _modify_details_query(self, cursor, params):
fields = self._get_show_fields(params)
if not fields:
return cursor
if fields and 'data_fields' in fields:
cursor = cursor.options(joinedload_all(Model.datasets))
if fields and 'features' in fields:
cursor = cursor.options(joinedload_all(Model.features_set))
cursor = cursor.options(undefer(Model.classifier))
return cursor
def _execute(self, uow):
self._session = uow.session
item = self._session.query(db.Item)\
.options(joinedload_all('data_ref'))\
.options(joinedload_all('item_tags.tag'))\
.options(joinedload_all('item_fields.field'))\
.get(self.__itemId)
if item is None:
raise err.NotFoundError()
self._session.expunge(item)
return item
def _execute(self, uow):
self._session = uow.session
item = self._session.query(db.Item)\
.options(joinedload_all('data_ref'))\
.options(joinedload_all('item_tags.tag'))\
.options(joinedload_all('item_fields.field'))\
.get(self.__itemId)
if item is None:
raise err.NotFoundError()
self._session.expunge(item)
return item
def delivery(request, info, session, deliveryid):
try:
d = (
session.query(Delivery)
.filter_by(id=int(deliveryid))
.options(joinedload_all("items.stocktype.unit"))
.options(joinedload_all("items.stockline"))
.options(undefer_group("qtys"))
.one()
)
except NoResultFound:
raise Http404
return ("delivery.html", {"delivery": d})
def by_number(cls, number, eager=True):
q = cls.query.filter_by(number=number)
if eager:
q = q.options(
orm.joinedload_all('items.image'),
orm.joinedload_all('templates.images'),
orm.joinedload_all('items.category.image'),
orm.joinedload_all('items.category.subcategories'),
)
try:
return q.one()
except NoResultFound:
return None
def prime_cache(args):
"""If data needs to be denormalized for lookup, do that here.
This procedure should be separate from the db initialization, because
it will have to be run periodiucally whenever data has been updated.
"""
q = DBSession.query(models.Doculect)\
.order_by(models.Doculect.pk)\
.options(
joinedload_all(common.Language.valuesets, common.ValueSet.values),
joinedload_all(common.Language.valuesets, common.ValueSet.parameter)
)
for doculect in q:
doculect.txt = doculect.to_txt()
def delivery(request, info, session, deliveryid):
d = session\
.query(Delivery)\
.options(joinedload_all('items.stocktype.unit'),
joinedload_all('items.stockline'),
undefer_qtys('items'))\
.get(int(deliveryid))
if not d:
raise Http404
return ('delivery.html', {
'tillobject': d,
'delivery': d,
})
def __batch(self, id=None):
if id is None:
return None
return (
Session.query(ConsumableBatch)
.filter(ConsumableBatch.id == int(id))
.options(
joinedload_all(ConsumableBatch.consumable_molding_style),
joinedload_all(ConsumableBatch.consumable_bonding_style),
)
.first()
)
def prime_cache(args):
"""If data needs to be denormalized for lookup, do that here.
This procedure should be separate from the db initialization, because
it will have to be run periodiucally whenever data has been updated.
"""
q = DBSession.query(models.Doculect)\
.order_by(models.Doculect.pk)\
.options(
joinedload_all(common.Language.valuesets, common.ValueSet.values),
joinedload_all(common.Language.valuesets, common.ValueSet.parameter)
)
for doculect in q:
doculect.txt = doculect.to_txt()
def stockline(request, info, session, stocklineid):
s = session\
.query(StockLine)\
.options(joinedload_all('stockonsale.stocktype.unit'),
joinedload_all('stockonsale.delivery'),
undefer_qtys('stockonsale'))\
.get(int(stocklineid))
if not s:
raise Http404
return ('stockline.html', {
'tillobject': s,
'stockline': s,
})
def forMovie(self, id = None):
db = get_session()
releases_raw = db.query(Relea) \
.options(joinedload_all('info')) \
.options(joinedload_all('files')) \
.filter(Relea.movie_id == id) \
.all()
releases = [r.to_dict({'info':{}, 'files':{}}) for r in releases_raw]
releases = sorted(releases, key = lambda k: k['info'].get('score', 0), reverse = True)
return releases
def stock(request, info, session, stockid):
s = session\
.query(StockItem)\
.options(joinedload_all('stocktype.department'),
joinedload_all('stocktype.stockline_log.stockline'),
joinedload_all('delivery.supplier'),
joinedload_all('stockunit.unit'),
joinedload_all('annotations.type'),
subqueryload_all('out.transline.transaction'),
undefer_group('qtys'))\
.get(int(stockid))
if not s:
raise Http404
return ('stock.html', {'stock': s})
def _get_domain(self, domainid):
"utility to return domain"
try:
cachekey = 'domain-%s' % domainid
qry = Session.query(Domain).filter(Domain.id == domainid)\
.options(joinedload_all(Domain.servers),
joinedload_all(Domain.aliases),
joinedload_all(Domain.authservers))\
.options(FromCache('sql_cache_med', cachekey))
if self.invalidate:
qry.invalidate()
domain = qry.one()
except NoResultFound:
domain = None
return domain
def _get_scenario(scenario_id, include_data=True, include_items=True):
try:
scenario_qry = DBSession.query(Scenario).filter(
Scenario.scenario_id == scenario_id)
if include_data is True:
scenario_qry = scenario_qry.options(
joinedload_all('resourcescenarios'))
if include_items is True:
scenario_qry = scenario_qry.options(
joinedload_all('resourcegroupitems'))
scenario = scenario_qry.one()
return scenario
except NoResultFound:
raise ResourceNotFoundError("Scenario %s does not exist." %
(scenario_id))
def department(request, info, session, departmentid, as_spreadsheet=False):
d = session\
.query(Department)\
.get(int(departmentid))
if d is None:
raise Http404
include_finished = request.GET.get("show_finished", "off") == "on"
items = session\
.query(StockItem)\
.join(StockType)\
.filter(StockType.department == d)\
.order_by(desc(StockItem.id))\
.options(joinedload_all('stocktype.unit'),
undefer_group('qtys'),
joinedload('stockline'),
joinedload('delivery'),
joinedload('finishcode'))
if not include_finished:
items = items.filter(StockItem.finished == None)
if as_spreadsheet:
return spreadsheets.stock(session,
items.all(),
tillname=info['tillname'],
filename="{}-dept{}-stock.ods".format(
info['tillname'], departmentid))
pager = Pager(request, items)
return ('department.html', {
'department': d,
'pager': pager,
'include_finished': include_finished
})
def get_scenario_data(scenario_id, **kwargs):
"""
Get all the datasets from the group with the specified name
@returns a list of dictionaries
"""
user_id = kwargs.get('user_id')
scenario_data = DBSession.query(Dataset).filter(
Dataset.dataset_id == ResourceScenario.dataset_id,
ResourceScenario.scenario_id == scenario_id).options(
joinedload_all('metadata')).distinct().all()
for sd in scenario_data:
if sd.hidden == 'Y':
try:
sd.check_read_permission(user_id)
except:
sd.value = None
sd.frequency = None
sd.start_time = None
sd.metadata = []
DBSession.expunge_all()
log.info("Retrieved %s datasets", len(scenario_data))
return scenario_data
def test_option_against_multi_nonexistent_basestring(self):
Item = self.classes.Item
Keyword = self.classes.Keyword
self._assert_eager_with_entity_exception(
[Keyword, Item], (joinedload_all("description"), ),
r"Can't find property named 'description' on the mapped "
r"entity Mapper\|Keyword\|keywords in this Query.")
def test_option_against_multi_non_relation_twolevel_basestring(self):
Item = self.classes.Item
Keyword = self.classes.Keyword
self._assert_eager_with_entity_exception(
[Keyword, Item], (joinedload_all("id", "keywords"), ),
r"Attribute 'id' of entity 'Mapper\|Keyword\|keywords' "
"does not refer to a mapped entity")
def geo(args):
with_session(args)
fname = args.pkg_dir.joinpath('static', 'download',
'languages-and-dialects-geo.csv')
with transaction.manager, UnicodeWriter(fname) as writer:
writer.writerow([
'glottocode', 'name', 'isocodes', 'level', 'macroarea', 'latitude',
'longitude'
])
for l in DBSession.query(models.Languoid)\
.filter(or_(
models.Languoid.level == models.LanguoidLevel.dialect,
models.Languoid.level == models.LanguoidLevel.language))\
.options(
joinedload(models.Languoid.macroareas),
joinedload_all(
common.Language.languageidentifier,
common.LanguageIdentifier.identifier))\
.order_by(common.Language.name):
writer.writerow([
l.id, l.name, ' '.join(
i.name
for i in l.get_identifier_objs(common.IdentifierType.iso)),
l.level, l.macroareas[0].name if l.macroareas else '',
l.latitude if l.latitude is not None else '',
l.longitude if l.longitude is not None else ''
])
args.log.info('{0} written'.format(fname))
def test_option_against_multi_no_entities_basestring(self):
Item = self.classes.Item
Keyword = self.classes.Keyword
self._assert_eager_with_entity_exception(
[Keyword.id, Item.id], (joinedload_all("keywords"), ),
r"Query has only expression-based entities - can't find property "
"named 'keywords'.")
def base_query(self, query):
query = DBSession.query(self.model)\
.join(ValueSet)\
.options(joinedload_all(
Value.valueset, ValueSet.references, ValueSetReference.source
)).distinct()
if not self.parameter:
query = query.join(ValueSet.parameter).filter(Parameter.id != '0')
if self.ftype:
query = query.filter(Feature.feature_type == self.ftype)
if self.language:
return query\
.options(joinedload(Value.domainelement))\
.filter(ValueSet.language_pk.in_(
[l.pk for l in [self.language] + self.language.lects]))
if self.parameter:
query = query.join(ValueSet.contribution)\
.join(self.vs_lang, ValueSet.language_pk == self.vs_lang.pk)\
.join(self.vs_lect, ValueSet.language_pk == self.vs_lect.pk)\
.join(DomainElement)\
.options(joinedload(Value.domainelement))
return query.filter(ValueSet.parameter_pk == self.parameter.pk)
return query
def test_option_against_wrong_multi_entity_type_attr_one(self):
Item = self.classes.Item
Keyword = self.classes.Keyword
self._assert_eager_with_entity_exception(
[Keyword, Item], (joinedload_all(Keyword.id, Item.keywords), ),
r"Attribute 'id' of entity 'Mapper\|Keyword\|keywords' "
"does not refer to a mapped entity")
def get_departure_schedule(cls, session, stop_id, date=None, route_id=None, limit=None):
''' helper routine which returns the stop schedule for a give date
'''
from gtfsdb.model.trip import Trip
# step 0: make sure we have a valid date
if date is None:
date = datetime.date.today()
# step 1: get stop times based on date
log.info("QUERY StopTime")
q = session.query(StopTime)
q = q.filter_by(stop_id=stop_id)
q = q.filter(StopTime.departure_time != None)
q = q.filter(StopTime.trip.has(Trip.universal_calendar.any(date=date)))
# step 2: apply an optional route filter
if route_id:
q = q.filter(StopTime.trip.has(Trip.route_id == route_id))
# step 3: order the stop times
q = q.order_by(StopTime.departure_time)
# step 4: options to speed up /q
q = q.options(joinedload_all('trip'))
if limit:
q = q.limit(limit)
ret_val = q.all()
return ret_val
def base_query(self, query):
return query\
.join(ContributionContributor)\
.join(Contributor)\
.distinct()\
.options(joinedload_all(
Contribution.contributor_assocs, ContributionContributor.contributor))
def _build_list_query(self, params, **kwargs):
# TODO: What about joins?
cursor = self._set_list_query_opts(self.Model.query, params)
filter_params = self._prepare_filter_params(params)
filter_params.update(kwargs)
fields = self._get_show_fields(params)
if fields:
opts = []
for field in self.Model.__table__.columns.keys():
if field in fields or field in ('id', ):
opts.append(undefer(getattr(self.Model, field)))
else:
opts.append(defer(getattr(self.Model, field)))
relation_properties = filter(
lambda p: isinstance(p, properties.RelationshipProperty),
self.Model.__mapper__.iterate_properties)
for field in relation_properties:
if field.key in fields:
cursor = cursor.options(
joinedload_all(getattr(self.Model, field.key)))
if opts:
cursor = cursor.options(*opts)
for name, val in filter_params.iteritems():
fltr = self._build_query_item(name, val)
if fltr is not None:
cursor = cursor.filter(fltr)
return cursor
