def list(cls):
return cls.query\
.options(joinedload('house'),
joinedload('province'),
joinedload('memberships.committee'))\
.filter(Member.current == True)\
.order_by(Member.name) # noqa
def get_top_display(self, **kwargs):
'''
Returns all concepts or collections that form the top-level of a display
hierarchy.
As opposed to the :meth:`get_top_concepts`, this method can possibly
return both concepts and collections.
:rtype: Returns a list of concepts and collections. For each an
id is present and a label. The label is determined by looking at
the `**kwargs` parameter, the default language of the provider
and falls back to `en` if nothing is present.
'''
tco = self.session\
.query(ConceptModel)\
.options(joinedload('labels'))\
.filter(
ConceptModel.conceptscheme_id == self.conceptscheme_id,
ConceptModel.broader_concepts == None,
ConceptModel.member_of == None
).all()
tcl = self.session\
.query(CollectionModel)\
.options(joinedload('labels'))\
.filter(
CollectionModel.conceptscheme_id == self.conceptscheme_id,
CollectionModel.broader_concepts == None,
CollectionModel.member_of == None
).all()
res = tco + tcl
lan = self._get_language(**kwargs)
sort = self._get_sort(**kwargs)
sort_order = self._get_sort_order(**kwargs)
return [self._get_id_and_label(c, lan) for c in self._sort(res, sort, lan, sort_order=='desc')]
def _get_concept_scheme(self):
'''
Find a :class:`skosprovider.skos.ConceptScheme` for this provider.
:param id: Id of a conceptscheme.
:rtype: :class:`skosprovider.skos.ConceptScheme`
'''
csm = self.session\
.query(ConceptSchemeModel)\
.options(joinedload('labels'))\
.options(joinedload('notes'))\
.options(joinedload('languages'))\
.options(joinedload('sources'))\
.get(self.conceptscheme_id)
return ConceptScheme(
uri=csm.uri,
labels=[
Label(l.label, l.labeltype_id, l.language_id)
for l in csm.labels
],
notes=[
Note(n.note, n.notetype_id, n.language_id, n.markup)
for n in csm.notes
],
languages=[
l.id for l in csm.languages
],
sources=[
Source(s.citation, s.markup) for s in csm.sources
]
)
def _process(self):
if self.event_new.type == 'conference':
reviewing_status = get_reviewing_status(self.contrib, self._conf)
show_paper = ((self._conf.getConfPaperReview().hasReviewing() and
self.contrib.can_manage(session.user, 'submit')) or reviewing_status == 'Accept')
paper_upload_form = PaperUploadForm()
paper_file_data = self.contrib.paper_files.filter_by(revision_id=None) if show_paper else None
else:
reviewing_status = paper_upload_form = paper_file_data = None
show_paper = False
ical_params = get_base_ical_parameters(session.user, 'contributions',
'/export/event/{0}.ics'.format(self.event_new.id))
contrib = (Contribution.query
.filter_by(id=self.contrib.id)
.options(joinedload('type'),
joinedload('session'),
joinedload('subcontributions'),
joinedload('timetable_entry').lazyload('*'))
.one())
return self.view_class.render_template('display/contribution_display.html', self._conf,
contribution=contrib,
event=self.event_new,
reviewing_status=reviewing_status,
show_paper=show_paper,
can_submit_paper=self.contrib.can_manage(session.user, 'submit'),
paper_files=paper_file_data,
paper_upload_form=paper_upload_form,
**ical_params)
def get_by_uri(self, uri):
'''Get all information on a concept or collection, based on a
:term:`URI`.
This method will only find concepts or collections whose :term:`URI` is
actually stored in the database. It will not find anything that has
no :term:`URI` in the database, but does have a matching :term:`URI`
after generation.
:rtype: :class:`skosprovider.skos.Concept` or
:class:`skosprovider.skos.Collection` or `False` if the concept or
collection is unknown to the provider.
'''
try:
thing = self.session\
.query(Thing)\
.options(joinedload('labels'))\
.options(joinedload('notes'))\
.options(joinedload('sources'))\
.filter(
Thing.uri == uri,
Thing.conceptscheme_id == self.conceptscheme_id
).one()
except NoResultFound:
return False
return self._from_thing(thing)
def get_edges(db_session, edges, order=desc(Edge.updated_at), page=0, page_size=10, partial_matching=False):
edges = [('%%%s%%' % u, '%%%s%%' % v) for u, v in edges] if partial_matching else edges
filter_group = [and_(Edge.head_node_id.ilike(u), Edge.tail_node_id.ilike(v)) for u, v in edges]
filter_group.append(
[and_(Edge.head_node.has(Node.label.ilike(u)), Edge.tail_node.has(Node.label.ilike(v))) for u, v in edges])
return db_session.query(Graph).options(joinedload('head_node'), joinedload('tail_node')).filter(
or_(*filter_group)).order_by(order).limit(page_size).offset(page * page_size)
def _process(self):
self.user.settings.set('suggest_categories', True)
tz = session.tzinfo
hours, minutes = timedelta_split(tz.utcoffset(datetime.now()))[:2]
categories = get_related_categories(self.user)
categories_events = []
if categories:
category_ids = {c['categ'].id for c in categories.itervalues()}
today = now_utc(False).astimezone(tz).date()
query = (Event.query
.filter(~Event.is_deleted,
Event.category_chain_overlaps(category_ids),
Event.start_dt.astimezone(session.tzinfo) >= today)
.options(joinedload('category').load_only('id', 'title'),
joinedload('series'),
subqueryload('acl_entries'),
load_only('id', 'category_id', 'start_dt', 'end_dt', 'title', 'access_key',
'protection_mode', 'series_id', 'series_pos', 'series_count'))
.order_by(Event.start_dt, Event.id))
categories_events = get_n_matching(query, 10, lambda x: x.can_access(self.user))
from_dt = now_utc(False) - relativedelta(weeks=1, hour=0, minute=0, second=0)
linked_events = [(event, {'management': bool(roles & self.management_roles),
'reviewing': bool(roles & self.reviewer_roles),
'attendance': bool(roles & self.attendance_roles)})
for event, roles in get_linked_events(self.user, from_dt, 10).iteritems()]
return WPUser.render_template('dashboard.html', 'dashboard',
offset='{:+03d}:{:02d}'.format(hours, minutes), user=self.user,
categories=categories,
categories_events=categories_events,
suggested_categories=get_suggested_categories(self.user),
linked_events=linked_events)
def _queue_items_dict_with_track_dict(queue_query):
queue_dicts = [queue_item.to_dict('full') for queue_item in queue_query]
# Fetch all tracks with id's in the queue
trackids = [queue_item['track_id'] for queue_item in queue_dicts]
tracks = {}
if trackids:
tracks = DBSession.query(Track).\
filter(Track.id.in_(trackids)).\
options(\
joinedload(Track.tags),\
joinedload(Track.attachments),\
joinedload('tags.parent'),\
#defer(Track.lyrics),\
)
tracks = {track['id']:track for track in [track.to_dict('full', exclude_fields='lyrics') for track in tracks]}
# HACK
# AllanC - Hack to overlay title on API return.
# This technically cant be part of the model because the title rendering in 'helpers' uses the dict version of a track object rather than the DB object
# This is half the best place for it. We want the model to be as clean as possible
# But we need the 'title' field to be consistant for all API returns for tracks ... more consideration needed
#
# Solution: Setup SQLAlchemy event to render the title before commiting a track to the DB - like a DB trigger by handled Python size for cross db compatibility
# Stub created in model_track.py
# This is to be removed ...
for track in tracks.values():
track['title'] = track_title(track['tags'])
# Attach track to queue_item
for queue_item in queue_dicts:
queue_item['track'] = tracks.get(queue_item['track_id'], {})
return queue_dicts
def get_graphs_by_edges_and_nodes_and_names(db_session, group_ids=None, names=None, nodes=None, edges=None, tags=None,
order=desc(Graph.updated_at), page=0, page_size=10, partial_matching=False,
owner_email=None, is_public=None):
query = db_session.query(Graph)
edges = [] if edges is None else edges
nodes = [] if nodes is None else nodes
names = [] if names is None else names
tags = [] if tags is None else tags
edges = [('%%%s%%' % u, '%%%s%%' % v) for u, v in edges] if partial_matching else edges
nodes = ['%%%s%%' % node for node in nodes] if partial_matching else nodes
names = ['%%%s%%' % name for name in names] if partial_matching else names
tags = ['%%%s%%' % tag for tag in tags]
graph_filter_group = []
if is_public is not None:
graph_filter_group.append(Graph.is_public == is_public)
if owner_email is not None:
graph_filter_group.append(Graph.owner_email == owner_email)
if group_ids is not None:
query = query.filter(Graph.shared_with_groups.any(Group.id.in_(group_ids)))
if len(graph_filter_group) > 0:
query = query.filter(*graph_filter_group)
names_filter_group = [Graph.name.ilike(name) for name in names]
tags_filter_group = [GraphTag.name.ilike(tag) for tag in tags]
nodes_filter_group = [Node.label.ilike(node) for node in nodes]
nodes_filter_group.extend([Node.name.ilike(node) for node in nodes])
edges_filter_group = [and_(Edge.head_node.has(Node.name.ilike(u)), Edge.tail_node.has(Node.name.ilike(v))) for u, v
in edges]
edges_filter_group.extend(
[and_(Edge.tail_node.has(Node.name.ilike(u)), Edge.head_node.has(Node.name.ilike(v))) for u, v in edges])
edges_filter_group.extend(
[and_(Edge.head_node.has(Node.label.ilike(u)), Edge.tail_node.has(Node.label.ilike(v))) for u, v in edges])
edges_filter_group.extend(
[and_(Edge.tail_node.has(Node.label.ilike(u)), Edge.head_node.has(Node.label.ilike(v))) for u, v in edges])
options_group = []
if len(nodes_filter_group) > 0:
options_group.append(joinedload('nodes'))
if len(edges_filter_group) > 0:
options_group.append(joinedload('edges'))
if len(options_group) > 0:
query = query.options(*options_group)
combined_filter_group = []
if len(nodes_filter_group) > 0:
combined_filter_group.append(Graph.nodes.any(or_(*nodes_filter_group)))
if len(edges_filter_group) > 0:
combined_filter_group.append(Graph.edges.any(or_(*edges_filter_group)))
if len(names_filter_group) > 0:
combined_filter_group.append(*names_filter_group)
if len(tags_filter_group) > 0:
combined_filter_group.append(*tags_filter_group)
if len(combined_filter_group) > 0:
query = query.filter(or_(*combined_filter_group))
return query.order_by(order).limit(page_size).offset(page * page_size).all()
def list_tasks(self, limit=None, details=False, category=None,
offset=None, status=None, sample_id=None, not_status=None):
"""Retrieve list of task.
@param limit: specify a limit of entries.
@param details: if details about must be included
@param category: filter by category
@param offset: list offset
@param status: filter by task status
@param sample_id: filter tasks for a sample
@param not_status: exclude this task status from filter
@return: list of tasks.
"""
session = self.Session()
try:
search = session.query(Task)
if status:
search = search.filter_by(status=status)
if not_status:
search = search.filter(Task.status != not_status)
if category:
search = search.filter_by(category=category)
if details:
search = search.options(joinedload("guest"), joinedload("errors"), joinedload("tags"))
if sample_id is not None:
search = search.filter_by(sample_id=sample_id)
tasks = search.order_by("added_on desc").limit(limit).offset(offset).all()
except SQLAlchemyError as e:
log.debug("Database error listing tasks: {0}".format(e))
return []
finally:
session.close()
return tasks
def test_weakref_with_cycles_o2o(self):
Address, addresses, users, User = (self.classes.Address,
self.tables.addresses,
self.tables.users,
self.classes.User)
s = sessionmaker()()
mapper(User, users, properties={
"address": relationship(Address, backref="user", uselist=False)
})
mapper(Address, addresses)
s.add(User(name="ed", address=Address(email_address="ed1")))
s.commit()
user = s.query(User).options(joinedload(User.address)).one()
user.address.user
eq_(user, User(name="ed", address=Address(email_address="ed1")))
del user
gc_collect()
assert len(s.identity_map) == 0
user = s.query(User).options(joinedload(User.address)).one()
user.address.email_address = 'ed2'
user.address.user # lazyload
del user
gc_collect()
assert len(s.identity_map) == 2
s.commit()
user = s.query(User).options(joinedload(User.address)).one()
eq_(user, User(name="ed", address=Address(email_address="ed2")))
def drawlines(self, h):
sl = (
td.s.query(StockLine)
.filter(StockLine.location.in_(self.locations))
.filter(StockLine.capacity == None)
.order_by(StockLine.name)
.options(joinedload("stockonsale"))
.options(joinedload("stockonsale.stocktype"))
.options(undefer_group("qtys"))
.all()
)
f = ui.tableformatter("pl l L r rp")
header = f("Line", "StockID", "Stock", "Used", "Remaining")
def fl(line):
if line.stockonsale:
sos = line.stockonsale[0]
return (line.name, sos.id, sos.stocktype.format(), sos.used, sos.remaining)
return (line.name, "", "", "", "")
ml = [header] + [f(*fl(line)) for line in sl]
y = 0
for l in ml:
for line in l.display(self.w):
self.addstr(y, 0, line)
y = y + 1
if y >= h:
break
def image_get(context, image_id, session=None):
"""Get an image or raise if it does not exist."""
session = session or get_session()
try:
#NOTE(bcwaldon): this is to prevent false matches when mysql compares
# an integer to a string that begins with that integer
image_id = int(image_id)
except (TypeError, ValueError):
raise exception.NotFound("No image found")
try:
image = session.query(models.Image).\
options(joinedload(models.Image.properties)).\
options(joinedload(models.Image.members)).\
filter_by(deleted=_deleted(context)).\
filter_by(id=image_id).\
one()
except exc.NoResultFound:
raise exception.NotFound("No image found with ID %s" % image_id)
# Make sure they can look at it
if not context.is_image_visible(image):
raise exception.NotAuthorized("Image not visible to you")
return image
def _paginate_offset(self, clazz, schema, adapt_schema):
"""Return a batch of documents with the given `offset` and `limit`.
"""
validated = self.request.validated
offset = validated['offset'] if 'offset' in validated else 0
limit = min(
validated['limit'] if 'limit' in validated else LIMIT_DEFAULT,
LIMIT_MAX)
base_query = DBSession.query(clazz)
documents = base_query. \
options(joinedload(getattr(clazz, 'locales'))). \
options(joinedload(getattr(clazz, 'geometry'))). \
order_by(clazz.document_id.desc()). \
slice(offset, offset + limit). \
all()
set_available_cultures(documents, loaded=True)
if validated.get('lang') is not None:
set_best_locale(documents, validated.get('lang'))
total = base_query.count()
return {
'documents': [
to_json_dict(
doc,
schema if not adapt_schema else adapt_schema(schema, doc)
) for doc in documents
],
'total': total
}
def _get_ips_except_admin(self, node_id=None,
network_id=None, joined=False):
"""Method for receiving IP addresses for node or network
excluding Admin Network IP address.
:param node_id: Node database ID.
:type node_id: int
:param network_id: Network database ID.
:type network_id: int
:returns: List of free IP addresses as SQLAlchemy objects.
"""
ips = db().query(IPAddr).order_by(IPAddr.id)
if joined:
ips = ips.options(
joinedload('network_data'),
joinedload('network_data.network_group'))
if node_id:
ips = ips.filter_by(node=node_id)
if network_id:
ips = ips.filter_by(network=network_id)
admin_net_id = self.get_admin_network_id(False)
if admin_net_id:
ips = ips.filter(
not_(IPAddr.network == admin_net_id)
)
return ips.all()
def drafts(namespace_id, thread_public_id, limit, offset, db_session):
query = db_session.query(Message).filter(Message.is_draft == True)
thread_criteria = [Thread.namespace_id == namespace_id]
if thread_public_id is not None:
# TODO(emfree) this is a common case that we should handle
# separately by just fetching the thread's messages and only
# filtering more if needed.
thread_criteria.append(Thread.public_id == thread_public_id)
thread_predicate = and_(*thread_criteria)
thread_query = db_session.query(Thread).filter(thread_predicate)
query = query.join(thread_query.subquery())
# Eager-load some objects in order to make constructing API
# representations faster.
query = query.options(
joinedload(Message.parts).load_only("public_id", "content_disposition"),
joinedload(Message.thread).load_only("public_id", "discriminator"),
)
query = query.limit(limit)
if offset:
query = query.offset(offset)
return [m for m in query.all() if m.is_latest]
def _load_data(self, id):
c.users_group.permissions = {
'repositories': {},
'repositories_groups': {}
}
ugroup_repo_perms = UsersGroupRepoToPerm.query()\
.options(joinedload(UsersGroupRepoToPerm.permission))\
.options(joinedload(UsersGroupRepoToPerm.repository))\
.filter(UsersGroupRepoToPerm.users_group_id == id)\
.all()
for gr in ugroup_repo_perms:
c.users_group.permissions['repositories'][gr.repository.repo_name] \
= gr.permission.permission_name
ugroup_group_perms = UsersGroupRepoGroupToPerm.query()\
.options(joinedload(UsersGroupRepoGroupToPerm.permission))\
.options(joinedload(UsersGroupRepoGroupToPerm.group))\
.filter(UsersGroupRepoGroupToPerm.users_group_id == id)\
.all()
for gr in ugroup_group_perms:
c.users_group.permissions['repositories_groups'][gr.group.group_name] \
= gr.permission.permission_name
c.group_members_obj = [x.user for x in c.users_group.members]
c.group_members = [(x.user_id, x.username) for x in
c.group_members_obj]
c.available_members = [(x.user_id, x.username) for x in
User.query().all()]
def port_find(context, limit=None, sorts=None, marker_obj=None, fields=None,
**filters):
query = context.session.query(models.Port).options(
orm.joinedload(models.Port.ip_addresses))
model_filters = _model_query(context, models.Port, filters)
if filters.get("ip_address_id"):
model_filters.append(models.Port.ip_addresses.any(
models.IPAddress.id.in_(filters["ip_address_id"])))
if filters.get("device_id"):
model_filters.append(models.Port.device_id.in_(filters["device_id"]))
if filters.get("service"):
model_filters.append(models.Port.associations.any(
models.PortIpAssociation.service == filters["service"]))
if "join_security_groups" in filters:
query = query.options(orm.joinedload(models.Port.security_groups))
if fields and "port_subnets" in fields:
query = query.options(orm.joinedload("ip_addresses.subnet"))
query = query.options(
orm.joinedload("ip_addresses.subnet.dns_nameservers"))
query = query.options(
orm.joinedload("ip_addresses.subnet.routes"))
return paginate_query(query.filter(*model_filters), models.Port, limit,
sorts, marker_obj)
def _subnet_find(context, limit, sorts, marker, page_reverse, fields,
defaults=None, provider_query=False, **filters):
query = context.session.query(models.Subnet)
model_filters = _model_query(context, models.Subnet, filters, query)
if defaults:
invert_defaults = False
if INVERT_DEFAULTS in defaults:
invert_defaults = True
defaults.pop(0)
if filters and invert_defaults:
query = query.filter(and_(not_(models.Subnet.id.in_(defaults)),
and_(*model_filters)))
elif not provider_query and filters and not invert_defaults:
query = query.filter(or_(models.Subnet.id.in_(defaults),
and_(*model_filters)))
elif not invert_defaults:
query = query.filter(models.Subnet.id.in_(defaults))
else:
query = query.filter(*model_filters)
if "join_dns" in filters:
query = query.options(orm.joinedload(models.Subnet.dns_nameservers))
if "join_routes" in filters:
query = query.options(orm.joinedload(models.Subnet.routes))
if "join_pool" in filters:
query = query.options(orm.undefer('_allocation_pool_cache'))
return paginate_query(query, models.Subnet, limit, sorts, marker)
def render(self, session, **arguments):
q = session.query(Chassis)
q = q.options(subqueryload('model'),
joinedload('model.machine_specs'),
subqueryload('location'),
joinedload('slots'),
subqueryload('slots.machine'),
# A rare case when we don't need primary name/host
lazyload('slots.machine.primary_name'),
lazyload('slots.machine.host'),
subqueryload('interfaces'),
joinedload('interfaces.assignments'),
joinedload('interfaces.assignments.network'),
joinedload('interfaces.assignments.dns_records'))
# Prefer the primary name for ordering
q = q.outerjoin(DnsRecord, (Fqdn, DnsRecord.fqdn_id == Fqdn.id),
DnsDomain)
q = q.options(contains_eager('primary_name'),
contains_eager('primary_name.fqdn'),
contains_eager('primary_name.fqdn.dns_domain'))
q = q.order_by(Fqdn.name, DnsDomain.name, Chassis.label)
return q.all()
def get_indexable_contents(session):
from assembl.models import AgentProfile, Idea, Post
from assembl.models.post import PublicationStates
query = session.query(Idea
).filter(Idea.tombstone_condition()
).filter(Idea.hidden==False
).options(
joinedload(Idea.title).joinedload("entries"),
joinedload(Idea.synthesis_title).joinedload("entries"),
joinedload(Idea.description).joinedload("entries")
)
for idea in query:
yield idea
query = session.query(AgentProfile)
for user in query:
yield user
AllPost = with_polymorphic(Post, '*')
query = session.query(AllPost
).filter(AllPost.tombstone_condition()
).filter(AllPost.hidden==False
).filter(AllPost.publication_state == PublicationStates.PUBLISHED
).options(
joinedload(AllPost.subject).joinedload("entries"),
joinedload(AllPost.body).joinedload("entries")
)
for post in query:
for extract in post.extracts:
yield extract
yield post
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 cycle_tasks_cache(notifications):
"""Compile and return Task instances related to given notification records.
Args:
notifications: a list of Notification instances for which to fetch the
corresponding CycleTaskGroupObjectTask instances
accessible by their ID as a key
Returns:
Dictionary containing task instances with their IDs used as keys.
"""
task_ids = [n.object_id for n in notifications
if n.object_type == "CycleTaskGroupObjectTask"]
if not task_ids:
return {}
results = db.session\
.query(CycleTaskGroupObjectTask)\
.options(
orm.joinedload("related_sources"),
orm.joinedload("related_destinations")
)\
.filter(CycleTaskGroupObjectTask.id.in_(task_ids))
return {task.id: task for task in results}
def get_for_linked_object(cls, linked_object, preload_event=True):
"""Gets the note for the given object.
This only returns a note that hasn't been deleted.
:param linked_object: An event, session, contribution or
subcontribution.
:param preload_event: If all notes for the same event should
be pre-loaded and cached in the app
context.
"""
event = linked_object.event
try:
return g.event_notes[event].get(linked_object)
except (AttributeError, KeyError):
if not preload_event:
return linked_object.note if linked_object.note and not linked_object.note.is_deleted else None
if 'event_notes' not in g:
g.event_notes = {}
query = (event.all_notes
.filter_by(is_deleted=False)
.options(joinedload(EventNote.linked_event),
joinedload(EventNote.session),
joinedload(EventNote.contribution),
joinedload(EventNote.subcontribution)))
g.event_notes[event] = {n.object: n for n in query}
return g.event_notes[event].get(linked_object)
def test_get_node_networks_optimization(self):
self.env.create(
cluster_kwargs={},
nodes_kwargs=[
{"pending_addition": True, "api": True},
{"pending_addition": True, "api": True}
]
)
self.env.network_manager.assign_ips(
[n.id for n in self.env.nodes],
"management"
)
nodes = self.db.query(Node).options(
joinedload('cluster'),
joinedload('interfaces'),
joinedload('interfaces.assigned_networks')).all()
ips_mapped = self.env.network_manager.get_grouped_ips_by_node()
networks_grouped = self.env.network_manager.\
get_networks_grouped_by_cluster()
full_results = []
for node in nodes:
result = self.env.network_manager.get_node_networks_optimized(
node, ips_mapped.get(node.id, []),
networks_grouped.get(node.cluster_id, []))
full_results.append(result)
self.assertEqual(len(full_results), 2)
def question_dump():
cols = ['name', 'legislature', 'date', 'title', 'score']
ask_query = (
models.Ask.query
.join(models.Ask.question)
.options(
joinedload('question'),
joinedload('mandate'),
joinedload('mandate.person'),
joinedload('match_row'),
)
.order_by(models.Question.date.desc())
)
def make_row(ask):
score = ask.match.score
return {
'name': ask.mandate.person.name,
'legislature': str(ask.mandate.year),
'date': str(ask.question.date),
'title': str(ask.question.title),
'score': '' if score is None else str(score),
}
rows = (make_row(ask) for ask in ask_query.yield_per(10))
data = buffer_on_disk(csv_lines(cols, rows))
return flask.Response(data, mimetype='text/csv')
def _get(context, artifact_id, session, type_name=None, type_version=None,
show_level=ga.Showlevel.BASIC):
values = dict(id=artifact_id)
if type_name is not None:
values['type_name'] = type_name
if type_version is not None:
values['type_version'] = type_version
_set_version_fields(values)
try:
if show_level == ga.Showlevel.NONE:
query = (
session.query(models.Artifact).
options(joinedload(models.Artifact.tags)).
filter_by(**values))
else:
query = (
session.query(models.Artifact).
options(joinedload(models.Artifact.properties)).
options(joinedload(models.Artifact.tags)).
options(joinedload(models.Artifact.blobs).
joinedload(models.ArtifactBlob.locations)).
filter_by(**values))
artifact = query.one()
except orm.exc.NoResultFound:
LOG.warn(_LW("Artifact with id=%s not found") % artifact_id)
raise exception.ArtifactNotFound(id=artifact_id)
if not _check_visibility(context, artifact):
LOG.warn(_LW("Artifact with id=%s is not accessible") % artifact_id)
raise exception.ArtifactForbidden(id=artifact_id)
return artifact
def index(request):
project_ids = [
r[0]
for r in (
request.db.query(Project.id)
.order_by(Project.zscore.desc().nullslast(), func.random())
.limit(5)
.all()
)
]
release_a = aliased(
Release,
request.db.query(Release)
.distinct(Release.project_id)
.filter(Release.project_id.in_(project_ids))
.order_by(
Release.project_id,
Release.is_prerelease.nullslast(),
Release._pypi_ordering.desc(),
)
.subquery(),
)
trending_projects = (
request.db.query(release_a)
.options(joinedload(release_a.project))
.order_by(func.array_idx(project_ids, release_a.project_id))
.all()
)
latest_releases = (
request.db.query(Release)
.options(joinedload(Release.project))
.order_by(Release.created.desc())
.limit(5)
.all()
)
counts = dict(
request.db.query(RowCount.table_name, RowCount.count)
.filter(
RowCount.table_name.in_(
[
Project.__tablename__,
Release.__tablename__,
File.__tablename__,
User.__tablename__,
]
)
)
.all()
)
return {
"latest_releases": latest_releases,
"trending_projects": trending_projects,
"num_projects": counts.get(Project.__tablename__, 0),
"num_releases": counts.get(Release.__tablename__, 0),
"num_files": counts.get(File.__tablename__, 0),
"num_users": counts.get(User.__tablename__, 0),
}
def unit_update_stat():
dbsession = DBSession()
units = dbsession.query(Unit).options(joinedload(Unit.protocol_o), joinedload(Unit.protocol_i))
dbsession.query(UnitStat).delete()
for unit in units:
stat = UnitStat(unit_id=unit.id)
stat.official = unit.protocol_o != None
stat.independent = unit.protocol_i != None
stat.diff = False
stat.diff_value = 0
if unit.protocol_i and unit.protocol_o:
check = dict()
for i in unit.protocol_o.vote:
check[i.party_id] = i.vote_c
for i in unit.protocol_i.vote:
if check[i.party_id] != i.vote_c:
stat.diff_value += abs(i.vote_c - i.party_id)
stat.diff = True
dbsession.add(stat)
dbsession.add(stat)
def _image_get(context, image_id, session=None, force_show_deleted=False):
"""Get an image or raise if it does not exist."""
_check_image_id(image_id)
session = session or get_session()
try:
query = session.query(models.Image)\
.options(sa_orm.joinedload(models.Image.properties))\
.options(sa_orm.joinedload(models.Image.locations))\
.filter_by(id=image_id)
# filter out deleted images if context disallows it
if not force_show_deleted and not _can_show_deleted(context):
query = query.filter_by(deleted=False)
image = query.one()
except sa_orm.exc.NoResultFound:
msg = "No image found with ID %s" % image_id
LOG.debug(msg)
raise exception.NotFound(msg)
# Make sure they can look at it
if not is_image_visible(context, image):
msg = "Forbidding request, image %s not visible" % image_id
LOG.debug(msg)
raise exception.Forbidden(msg)
return image
def get(self, obj_id=None):
"""
---
single:
description: Retrieve a candidate
tags:
- candidates
parameters:
- in: path
name: obj_id
required: true
schema:
type: string
responses:
200:
content:
application/json:
schema: SingleObj
400:
content:
application/json:
schema: Error
multiple:
tags:
- candidates
description: Retrieve all candidates
parameters:
- in: query
name: numPerPage
nullable: true
schema:
type: integer
description: |
Number of candidates to return per paginated request. Defaults to 25
- in: query
name: pageNumber
nullable: true
schema:
type: integer
description: Page number for paginated query results. Defaults to 1
- in: query
name: totalMatches
nullable: true
schema:
type: integer
description: |
Used only in the case of paginating query results - if provided, this
allows for avoiding a potentially expensive query.count() call.
- in: query
name: savedStatus
nullable: true
schema:
type: string
enum: [all, savedToAllSelected, savedToAnySelected, savedToAnyAccessible, notSavedToAnyAccessible, notSavedToAnySelected, notSavedToAllSelected]
description: |
String indicating the saved status to filter candidate results for. Must be one of the enumerated values.
- in: query
name: startDate
nullable: true
schema:
type: string
description: |
Arrow-parseable date string (e.g. 2020-01-01). If provided, filter by
Candidate.passed_at >= startDate
- in: query
name: endDate
nullable: true
schema:
type: string
description: |
Arrow-parseable date string (e.g. 2020-01-01). If provided, filter by
Candidate.passed_at <= endDate
- in: query
name: groupIDs
nullable: true
schema:
type: array
items:
type: integer
explode: false
style: simple
description: |
Comma-separated string of group IDs (e.g. "1,2"). Defaults to all of user's
groups if filterIDs is not provided.
- in: query
name: filterIDs
nullable: true
schema:
type: array
items:
type: integer
explode: false
style: simple
description: |
Comma-separated string of filter IDs (e.g. "1,2"). Defaults to all of user's
groups' filters if groupIDs is not provided.
- in: query
name: annotationExcludeOrigin
nullable: true
schema:
type: string
description: |
Only load objects that do not have annotations from this origin.
If the annotationsExcludeOutdatedDate is also given, then annotations with
this origin will still be loaded if they were modified before that date.
- in: query
name: annotationExcludeOutdatedDate
nullable: true
schema:
type: string
description: |
An Arrow parseable string designating when an existing annotation is outdated.
Only relevant if giving the annotationExcludeOrigin argument.
Will treat objects with outdated annotations as if they did not have that annotation,
so it will load an object if it doesn't have an annotation with the origin specified or
if it does have it but the annotation modified date < annotationsExcludeOutdatedDate
- in: query
name: sortByAnnotationOrigin
nullable: true
schema:
type: string
description: |
The origin of the Annotation to sort by
- in: query
name: sortByAnnotationKey
nullable: true
schema:
type: string
description: |
The key of the Annotation data value to sort by
- in: query
name: sortByAnnotationOrder
nullable: true
schema:
type: string
description: |
The sort order for annotations - either "asc" or "desc".
Defaults to "asc".
- in: query
name: annotationFilterList
nullable: true
schema:
type: array
items:
type: string
explode: false
style: simple
description: |
Comma-separated string of JSON objects representing annotation filters.
Filter objects are expected to have keys { origin, key, value } for
non-numeric value types, or { origin, key, min, max } for numeric values.
- in: query
name: includePhotometry
nullable: true
schema:
type: boolean
description: |
Boolean indicating whether to include associated photometry. Defaults to
false.
- in: query
name: includeSpectra
nullable: true
schema:
type: boolean
description: |
Boolean indicating whether to include associated spectra. Defaults to false.
- in: query
name: classifications
nullable: true
schema:
type: array
items:
type: string
explode: false
style: simple
description: |
Comma-separated string of classification(s) to filter for candidates matching
that/those classification(s).
- in: query
name: redshiftRange
nullable: True
schema:
type: list
description: |
lowest and highest redshift to return, e.g. "(0,0.5)"
responses:
200:
content:
application/json:
schema:
allOf:
- $ref: '#/components/schemas/Success'
- type: object
properties:
data:
type: object
properties:
candidates:
type: array
items:
allOf:
- $ref: '#/components/schemas/Obj'
- type: object
properties:
is_source:
type: boolean
totalMatches:
type: integer
pageNumber:
type: integer
numPerPage:
type: integer
400:
content:
application/json:
schema: Error
"""
user_accessible_group_ids = [
g.id for g in self.current_user.accessible_groups
]
include_photometry = self.get_query_argument("includePhotometry",
False)
include_spectra = self.get_query_argument("includeSpectra", False)
if obj_id is not None:
query_options = [
joinedload(Candidate.obj).joinedload(Obj.thumbnails)
]
if include_photometry:
query_options.append(
joinedload(Candidate.obj).joinedload(
Obj.photometry).joinedload(Photometry.instrument))
if include_spectra:
query_options.append(
joinedload(Candidate.obj).joinedload(
Obj.spectra).joinedload(Spectrum.instrument))
c = Candidate.get_obj_if_readable_by(
obj_id,
self.current_user,
options=query_options,
)
if c is None:
return self.error("Invalid ID")
accessible_candidates = (
DBSession().query(Candidate).join(Filter).filter(
Candidate.obj_id == obj_id,
Filter.group_id.in_(
[g.id for g in self.current_user.accessible_groups]),
).all())
filter_ids = [cand.filter_id for cand in accessible_candidates]
passing_alerts = [{
"filter_id": cand.filter_id,
"passing_alert_id": cand.passing_alert_id,
"passed_at": cand.passed_at,
} for cand in accessible_candidates]
candidate_info = c.to_dict()
candidate_info["filter_ids"] = filter_ids
candidate_info["passing_alerts"] = passing_alerts
candidate_info["comments"] = sorted(
[
cmt.to_dict()
for cmt in c.get_comments_readable_by(self.current_user)
],
key=lambda x: x["created_at"],
reverse=True,
)
candidate_info["annotations"] = sorted(
c.get_annotations_readable_by(self.current_user),
key=lambda x: x.origin,
)
candidate_info["is_source"] = len(c.sources) > 0
if candidate_info["is_source"]:
candidate_info["saved_groups"] = (
DBSession().query(Group).join(Source).filter(
Source.obj_id == obj_id).filter(
Source.active.is_(True)).filter(
Group.id.in_(user_accessible_group_ids)).all())
candidate_info[
"classifications"] = c.get_classifications_readable_by(
self.current_user)
candidate_info["last_detected_at"] = c.last_detected_at
candidate_info["gal_lon"] = c.gal_lon_deg
candidate_info["gal_lat"] = c.gal_lat_deg
candidate_info["luminosity_distance"] = c.luminosity_distance
candidate_info["dm"] = c.dm
candidate_info[
"angular_diameter_distance"] = c.angular_diameter_distance
self.verify_permissions()
return self.success(data=candidate_info)
page_number = self.get_query_argument("pageNumber", None) or 1
n_per_page = self.get_query_argument("numPerPage", None) or 25
saved_status = self.get_query_argument("savedStatus", "all")
total_matches = self.get_query_argument("totalMatches", None)
start_date = self.get_query_argument("startDate", None)
end_date = self.get_query_argument("endDate", None)
group_ids = self.get_query_argument("groupIDs", None)
filter_ids = self.get_query_argument("filterIDs", None)
annotation_exclude_origin = self.get_query_argument(
'annotationExcludeOrigin', None)
annotation_exclude_date = self.get_query_argument(
'annotationExcludeOutdatedDate', None)
sort_by_origin = self.get_query_argument("sortByAnnotationOrigin",
None)
annotation_filter_list = self.get_query_argument(
"annotationFilterList", None)
classifications = self.get_query_argument("classifications", None)
redshift_range_str = self.get_query_argument("redshiftRange", None)
user_accessible_group_ids = [
g.id for g in self.current_user.accessible_groups
]
user_accessible_filter_ids = [
filtr.id for g in self.current_user.accessible_groups
for filtr in g.filters if g.filters is not None
]
if group_ids is not None:
if isinstance(group_ids, str) and "," in group_ids:
group_ids = [int(g_id) for g_id in group_ids.split(",")]
elif isinstance(group_ids, str) and group_ids.isdigit():
group_ids = [int(group_ids)]
else:
return self.error(
"Invalid groupIDs value -- select at least one group")
filter_ids = [
f.id
for f in Filter.query.filter(Filter.group_id.in_(group_ids))
]
elif filter_ids is not None:
if "," in filter_ids:
filter_ids = [int(f_id) for f_id in filter_ids.split(",")]
elif filter_ids.isdigit():
filter_ids = [int(filter_ids)]
else:
return self.error("Invalid filterIDs paramter value.")
group_ids = [
f.group_id
for f in Filter.query.filter(Filter.id.in_(filter_ids))
]
else:
# If 'groupIDs' & 'filterIDs' params not present in request, use all user groups
group_ids = user_accessible_group_ids
filter_ids = user_accessible_filter_ids
# Ensure user has access to specified groups/filters
if not (all([gid in user_accessible_group_ids
for gid in group_ids]) and
all([fid in user_accessible_filter_ids
for fid in filter_ids])):
return self.error(
"Insufficient permissions - you must only specify "
"groups/filters that you have access to.")
try:
page = int(page_number)
except ValueError:
return self.error("Invalid page number value.")
try:
n_per_page = int(n_per_page)
except ValueError:
return self.error("Invalid numPerPage value.")
# We'll join in the nested data for Obj (like photometry) later
q = (DBSession().query(Obj).join(Candidate).filter(
Obj.id.in_(DBSession().query(Candidate.obj_id).filter(
Candidate.filter_id.in_(filter_ids)))).outerjoin(Annotation)
) # Join in annotations info for sort/filter
if classifications is not None:
if isinstance(classifications, str) and "," in classifications:
classifications = [
c.strip() for c in classifications.split(",")
]
elif isinstance(classifications, str):
classifications = [classifications]
else:
return self.error(
"Invalid classifications value -- must provide at least one string value"
)
q = q.join(Classification).filter(
Classification.classification.in_(classifications))
if sort_by_origin is None:
# Don't apply the order by just yet. Save it so we can pass it to
# the LIMT/OFFSET helper function down the line once other query
# params are set.
order_by = [Candidate.passed_at.desc().nullslast(), Obj.id]
if saved_status in [
"savedToAllSelected",
"savedToAnySelected",
"savedToAnyAccessible",
"notSavedToAnyAccessible",
"notSavedToAnySelected",
"notSavedToAllSelected",
]:
notin = False
active_sources = (DBSession().query(Source.obj_id).filter(
Source.active.is_(True)))
if saved_status == "savedToAllSelected":
# Retrieve objects that have as many active saved groups that are
# in 'group_ids' as there are items in 'group_ids'
subquery = (active_sources.filter(
Source.group_id.in_(group_ids)).group_by(
Source.obj_id).having(
func.count(Source.group_id) == len(group_ids)))
elif saved_status == "savedToAnySelected":
subquery = active_sources.filter(
Source.group_id.in_(group_ids))
elif saved_status == "savedToAnyAccessible":
subquery = active_sources.filter(
Source.group_id.in_(user_accessible_group_ids))
elif saved_status == "notSavedToAnyAccessible":
subquery = active_sources.filter(
Source.group_id.in_(user_accessible_group_ids))
notin = True
elif saved_status == "notSavedToAnySelected":
subquery = active_sources.filter(
Source.group_id.in_(group_ids))
notin = True
elif saved_status == "notSavedToAllSelected":
# Retrieve objects that have as many active saved groups that are
# in 'group_ids' as there are items in 'group_ids', and select
# the objects not in that set
subquery = (active_sources.filter(
Source.group_id.in_(group_ids)).group_by(
Source.obj_id).having(
func.count(Source.group_id) == len(group_ids)))
notin = True
q = (q.filter(Obj.id.notin_(subquery)) if notin else q.filter(
Obj.id.in_(subquery)))
elif saved_status != "all":
return self.error(
f"Invalid savedStatus: {saved_status}. Must be one of the enumerated options."
)
if start_date is not None and start_date.strip() not in [
"",
"null",
"undefined",
]:
start_date = arrow.get(start_date).datetime
q = q.filter(Candidate.passed_at >= start_date)
if end_date is not None and end_date.strip() not in [
"", "null", "undefined"
]:
end_date = arrow.get(end_date).datetime
q = q.filter(Candidate.passed_at <= end_date)
if redshift_range_str is not None:
redshift_range = ast.literal_eval(redshift_range_str)
if not (isinstance(redshift_range,
(list, tuple)) and len(redshift_range) == 2):
return self.error('Invalid argument for `redshiftRange`')
if not (isinstance(redshift_range[0], (float, int))
and isinstance(redshift_range[1], (float, int))):
return self.error('Invalid arguments in `redshiftRange`')
q = q.filter(Obj.redshift >= redshift_range[0],
Obj.redshift <= redshift_range[1])
if annotation_exclude_origin is not None:
if annotation_exclude_date is None:
right = (DBSession().query(Obj.id).join(Annotation).filter(
Annotation.origin == annotation_exclude_origin).subquery())
else:
expire_date = arrow.get(annotation_exclude_date).datetime
right = (DBSession().query(Obj.id).join(Annotation).filter(
Annotation.origin == annotation_exclude_origin,
Annotation.modified >= expire_date,
).subquery())
q = q.outerjoin(right,
Obj.id == right.c.id).filter(right.c.id.is_(None))
if annotation_filter_list is not None:
# Parse annotation filter list objects from the query string
# and apply the filters to the query
for item in re.split(r",(?={)", annotation_filter_list):
try:
new_filter = json.loads(item)
except json.decoder.JSONDecodeError:
return self.error(
"Could not parse JSON objects for annotation filtering"
)
if "origin" not in new_filter:
self.error(
f"Invalid annotation filter list item {item}: \"origin\" is required."
)
if "key" not in new_filter:
self.error(
f"Invalid annotation filter list item {item}: \"key\" is required."
)
if "value" in new_filter:
value = new_filter["value"]
if isinstance(value, bool):
q = q.filter(
Annotation.origin == new_filter["origin"],
Annotation.data[new_filter["key"]].astext.cast(
Boolean) == value,
)
else:
# Test if the value is a nested object
try:
value = json.loads(value)
# If a nested object, we put the value through the
# JSON loads/dumps pipeline to get a string formatted
# like Postgres will for its JSONB ->> text operation
# For some reason, for example, not doing this will
# have value = { "key": "value" } (with the extra
# spaces around the braces) and cause the filter to
# fail.
value = json.dumps(value)
except json.decoder.JSONDecodeError:
# If not, this is just a string field and we don't
# need the string formatting above
pass
q = q.filter(
Annotation.origin == new_filter["origin"],
Annotation.data[new_filter["key"]].astext == value,
)
elif "min" in new_filter and "max" in new_filter:
try:
min_value = float(new_filter["min"])
max_value = float(new_filter["max"])
q = q.filter(
Annotation.origin == new_filter["origin"],
Annotation.data[new_filter["key"]].cast(Float) >=
min_value,
Annotation.data[new_filter["key"]].cast(Float) <=
max_value,
)
except ValueError:
return self.error(
f"Invalid annotation filter list item: {item}. The min/max provided is not a valid number."
)
else:
return self.error(
f"Invalid annotation filter list item: {item}. Should have either \"value\" or \"min\" and \"max\""
)
if sort_by_origin is not None:
sort_by_key = self.get_query_argument("sortByAnnotationKey", None)
sort_by_order = self.get_query_argument("sortByAnnotationOrder",
None)
# Define a custom sort order to have annotations from the correct
# origin first, all others afterwards
origin_sort_order = case(
value=Annotation.origin,
whens={sort_by_origin: 1},
else_=None,
)
annotation_sort_criterion = (
Annotation.data[sort_by_key].desc().nullslast()
if sort_by_order == "desc" else
Annotation.data[sort_by_key].nullslast())
# Don't apply the order by just yet. Save it so we can pass it to
# the LIMT/OFFSET helper function.
order_by = [
origin_sort_order.nullslast(),
annotation_sort_criterion,
Candidate.passed_at.desc().nullslast(),
Obj.id,
]
try:
query_results = grab_query_results(
q,
total_matches,
page,
n_per_page,
"candidates",
order_by=order_by,
include_photometry=include_photometry,
include_spectra=include_spectra,
)
except ValueError as e:
if "Page number out of range" in str(e):
return self.error("Page number out of range.")
raise
matching_source_ids = (DBSession().query(Source.obj_id).filter(
Source.group_id.in_(user_accessible_group_ids)).filter(
Source.obj_id.in_(
[obj.id for obj in query_results["candidates"]])).all())
candidate_list = []
for obj in query_results["candidates"]:
with DBSession().no_autoflush:
obj.is_source = (obj.id, ) in matching_source_ids
if obj.is_source:
obj.saved_groups = (DBSession().query(Group).join(
Source).filter(Source.obj_id == obj.id).filter(
Source.active.is_(True)).filter(
Group.id.in_(user_accessible_group_ids)).all())
obj.classifications = obj.get_classifications_readable_by(
self.current_user)
obj.passing_group_ids = [
f.group_id for f in (DBSession().query(Filter).filter(
Filter.id.in_(user_accessible_filter_ids)).filter(
Filter.id.in_(DBSession().query(
Candidate.filter_id).filter(
Candidate.obj_id == obj.id))).all())
]
candidate_list.append(obj.to_dict())
candidate_list[-1]["comments"] = sorted(
[
cmt.to_dict() for cmt in obj.get_comments_readable_by(
self.current_user)
],
key=lambda x: x["created_at"],
reverse=True,
)
candidate_list[-1]["annotations"] = sorted(
obj.get_annotations_readable_by(self.current_user),
key=lambda x: x.origin,
)
candidate_list[-1]["last_detected_at"] = obj.last_detected_at
candidate_list[-1]["gal_lat"] = obj.gal_lat_deg
candidate_list[-1]["gal_lon"] = obj.gal_lon_deg
candidate_list[-1][
"luminosity_distance"] = obj.luminosity_distance
candidate_list[-1]["dm"] = obj.dm
candidate_list[-1][
"angular_diameter_distance"] = obj.angular_diameter_distance
query_results["candidates"] = candidate_list
self.verify_permissions()
return self.success(data=query_results)
def get_comments(cids, v=None, nSession=None, sort_type="new",
load_parent=False, **kwargs):
if not cids:
return []
cids=tuple(cids)
nSession = nSession or kwargs.get('session') or g.db
exile=nSession.query(ModAction
).options(
lazyload('*')
).filter(
ModAction.kind=="exile_user",
ModAction.target_comment_id.in_(cids)
).subquery()
if v:
vt = nSession.query(CommentVote).filter(
CommentVote.comment_id.in_(cids),
CommentVote.user_id==v.id
).subquery()
mod=nSession.query(ModRelationship
).filter_by(
user_id=v.id,
accepted=True
).subquery()
query = nSession.query(
Comment,
aliased(CommentVote, alias=vt),
aliased(ModRelationship, alias=mod),
aliased(ModAction, alias=exile)
).options(
joinedload(Comment.author).joinedload(User.title)
)
if v.admin_level >=4:
query=query.options(joinedload(Comment.oauth_app))
if load_parent:
query = query.options(
joinedload(
Comment.parent_comment
).joinedload(
Comment.author
).joinedload(
User.title
)
)
query = query.join(
vt,
vt.c.comment_id == Comment.id,
isouter=True
).join(
Comment.post,
isouter=True
).join(
mod,
mod.c.board_id==Submission.board_id,
isouter=True
).join(
exile,
and_(exile.c.target_comment_id==Comment.id, exile.c.board_id==Comment.original_board_id),
isouter=True
).filter(
Comment.id.in_(cids)
)
query=query.options(
# contains_eager(Comment.post).contains_eager(Submission.board)
).order_by(None).all()
comments=[x for x in query]
output = [x[0] for x in comments]
for i in range(len(output)):
output[i]._voted = comments[i][1].vote_type if comments[i][1] else 0
output[i]._is_guildmaster = comments[i][2]
output[i]._is_exiled_for = comments[i][3]
else:
query = nSession.query(
Comment,
aliased(ModAction, alias=exile)
).options(
joinedload(Comment.author).joinedload(User.title),
joinedload(Comment.post).joinedload(Submission.board)
).filter(
Comment.id.in_(cids)
).join(
exile,
and_(exile.c.target_comment_id==Comment.id, exile.c.board_id==Comment.original_board_id),
isouter=True
).order_by(None).all()
comments=[x for x in query]
output=[x[0] for x in comments]
for i in range(len(output)):
output[i]._is_exiled_for=comments[i][1]
output = sorted(output, key=lambda x: cids.index(x.id))
return output
def get_comment(cid, nSession=None, v=None, graceful=False, **kwargs):
if isinstance(cid, str):
i = base36decode(cid)
else:
i = cid
nSession = nSession or kwargs.get('session') or g.db
exile = nSession.query(ModAction
).options(
lazyload('*')
).filter_by(
kind="exile_user"
).subquery()
if v:
blocking = v.blocking.subquery()
blocked = v.blocked.subquery()
vt = nSession.query(CommentVote).filter(
CommentVote.user_id == v.id,
CommentVote.comment_id == i).subquery()
mod=nSession.query(ModRelationship
).filter_by(
user_id=v.id,
accepted=True
).subquery()
items = nSession.query(
Comment,
vt.c.vote_type,
aliased(ModRelationship, alias=mod),
aliased(ModAction, alias=exile)
).options(
joinedload(Comment.author).joinedload(User.title)
)
if v.admin_level >=4:
items=items.options(joinedload(Comment.oauth_app))
items=items.filter(
Comment.id == i
).join(
vt,
vt.c.comment_id == Comment.id,
isouter=True
).join(
Comment.post,
isouter=True
).join(
mod,
mod.c.board_id==Submission.board_id,
isouter=True
).join(
exile,
and_(exile.c.target_comment_id==Comment.id, exile.c.board_id==Comment.original_board_id),
isouter=True
).first()
if not items and not graceful:
abort(404)
x = items[0]
x._voted = items[1] or 0
x._is_guildmaster=items[2] or 0
x._is_exiled_for=items[3] or 0
block = nSession.query(UserBlock).filter(
or_(
and_(
UserBlock.user_id == v.id,
UserBlock.target_id == x.author_id
),
and_(UserBlock.user_id == x.author_id,
UserBlock.target_id == v.id
)
)
).first()
x._is_blocking = block and block.user_id == v.id
x._is_blocked = block and block.target_id == v.id
else:
q = nSession.query(
Comment,
aliased(ModAction, alias=exile)
).options(
joinedload(Comment.author).joinedload(User.title)
).join(
exile,
and_(exile.c.target_comment_id==Comment.id, exile.c.board_id==Comment.original_board_id),
isouter=True
).filter(Comment.id == i).first()
if not q and not graceful:
abort(404)
x=q[0]
x._is_exiled_for=q[1]
return x
def get_post_with_comments(pid, sort_type="top", v=None):
post = get_post(pid, v=v)
exile=g.db.query(ModAction
).options(
lazyload('*')
).filter_by(
kind="exile_user"
).subquery()
if v:
votes = g.db.query(CommentVote).filter_by(user_id=v.id).subquery()
blocking = v.blocking.subquery()
blocked = v.blocked.subquery()
comms = g.db.query(
Comment,
votes.c.vote_type,
blocking.c.id,
blocked.c.id,
aliased(ModAction, alias=exile)
).options(
joinedload(Comment.author).joinedload(User.title)
)
if v.admin_level >=4:
comms=comms.options(joinedload(Comment.oauth_app))
comms=comms.filter(
Comment.parent_submission == post.id,
Comment.level <= 6
).join(
votes,
votes.c.comment_id == Comment.id,
isouter=True
).join(
blocking,
blocking.c.target_id == Comment.author_id,
isouter=True
).join(
blocked,
blocked.c.user_id == Comment.author_id,
isouter=True
).join(
exile,
and_(exile.c.target_comment_id==Comment.id, exile.c.board_id==Comment.original_board_id),
isouter=True
)
if sort_type == "hot":
comments = comms.order_by(Comment.score_hot.desc()).all()
elif sort_type == "top":
comments = comms.order_by(Comment.score_top.desc()).all()
elif sort_type == "new":
comments = comms.order_by(Comment.created_utc.desc()).all()
elif sort_type == "disputed":
comments = comms.order_by(Comment.score_disputed.desc()).all()
elif sort_type == "random":
c = comms.all()
comments = random.sample(c, k=len(c))
else:
abort(422)
output = []
for c in comments:
comment = c[0]
comment._voted = c[1] or 0
comment._is_blocking = c[2] or 0
comment._is_blocked = c[3] or 0
comment._is_guildmaster=post._is_guildmaster
comment._is_exiled_for=c[4]
output.append(comment)
post._preloaded_comments = output
else:
comms = g.db.query(
Comment,
aliased(ModAction, alias=exile)
).options(
joinedload(Comment.author).joinedload(User.title)
).filter(
Comment.parent_submission == post.id,
Comment.level <= 6
).join(
exile,
and_(exile.c.target_comment_id==Comment.id, exile.c.board_id==Comment.original_board_id),
isouter=True
)
if sort_type == "hot":
comments = comms.order_by(Comment.score_hot.desc()).all()
elif sort_type == "top":
comments = comms.order_by(Comment.score_top.desc()).all()
elif sort_type == "new":
comments = comms.order_by(Comment.created_utc.desc()).all()
elif sort_type == "disputed":
comments = comms.order_by(Comment.score_disputed.desc()).all()
elif sort_type == "random":
c = comms.all()
comments = random.sample(c, k=len(c))
else:
abort(422)
output = []
for c in comments:
comment=c[0]
comment._is_exiled_for=c[1]
output.append(comment)
# output=[x for x in comments]
post._preloaded_comments = output
return post
def get_posts(pids, sort="hot", v=None):
if not pids:
return []
pids=tuple(pids)
# exile=g.db.query(ModAction).options(
# lazyload('*')
# ).filter(
# ModAction.kind=="exile_user",
# ModAction.target_submission_id.in_(pids)
# ).subquery()
if v:
vt = g.db.query(Vote).filter(
Vote.submission_id.in_(pids),
Vote.user_id==v.id
).subquery()
mod = g.db.query(ModRelationship).filter_by(
user_id=v.id, accepted=True, invite_rescinded=False).subquery()
boardblocks = g.db.query(BoardBlock).filter_by(
user_id=v.id).subquery()
blocking = v.blocking.subquery()
blocked = v.blocked.subquery()
subs = g.db.query(Subscription).filter_by(user_id=v.id, is_active=True).subquery()
query = g.db.query(
Submission,
vt.c.vote_type,
aliased(ModRelationship, alias=mod),
boardblocks.c.id,
blocking.c.id,
blocked.c.id,
subs.c.id,
# aliased(ModAction, alias=exile)
).options(
joinedload(Submission.author).joinedload(User.title)
).filter(
Submission.id.in_(pids)
).join(
vt, vt.c.submission_id==Submission.id, isouter=True
).join(
mod,
mod.c.board_id == Submission.board_id,
isouter=True
).join(
boardblocks,
boardblocks.c.board_id == Submission.board_id,
isouter=True
).join(
blocking,
blocking.c.target_id == Submission.author_id,
isouter=True
).join(
blocked,
blocked.c.user_id == Submission.author_id,
isouter=True
).join(
subs,
subs.c.board_id == Submission.board_id,
isouter=True
# ).join(
# exile,
# and_(exile.c.target_submission_id==Submission.id, exile.c.board_id==Submission.original_board_id),
# isouter=True
).order_by(None).all()
posts=[x for x in query]
output = [p[0] for p in query]
for i in range(len(output)):
output[i]._voted = posts[i][1] or 0
output[i]._is_guildmaster = posts[i][2] or 0
output[i]._is_blocking_guild = posts[i][3] or 0
output[i]._is_blocking = posts[i][4] or 0
output[i]._is_blocked = posts[i][5] or 0
output[i]._is_subscribed = posts[i][6] or 0
# output[i]._is_exiled_for=posts[i][7] or 0
else:
query = g.db.query(
Submission,
# aliased(ModAction, alias=exile)
).options(
joinedload(Submission.author).joinedload(User.title)
).filter(Submission.id.in_(pids)
# ).join(
# exile,
# and_(exile.c.target_submission_id==Submission.id, exile.c.board_id==Submission.original_board_id),
# isouter=True
).order_by(None).all()
output=[x for x in query]
# output=[]
# for post in posts:
# p=post[0]
# p._is_exiled_for=post[1] or 0
# output.append(p)
return sorted(output, key=lambda x: pids.index(x.id))
def content(sess, start_date, finish_date, site_id, user):
sess = None
try:
sess = Session()
running_name, finished_name = chellow.dloads.make_names(
"site_hh_data.csv", user)
f = open(running_name, mode="w", newline="")
writer = csv.writer(f, lineterminator="\n")
write_row(
writer,
"Site Id",
"Site Name",
"Associated Site Ids",
"Sources",
"Generator Types",
"HH Start Clock-Time",
"Imported kWh",
"Displaced kWh",
"Exported kWh",
"Used kWh",
"Parasitic kWh",
"Generated kWh",
"3rd Party Import",
"3rd Party Export",
"Meter Type",
)
sites = sess.query(Site).order_by(Site.code)
if site_id is not None:
sites = sites.filter(Site.id == site_id)
for site in sites:
sources = set()
generator_types = set()
assoc = site.find_linked_sites(sess, start_date, finish_date)
metering_type = ""
for era in (sess.query(Era).join(SiteEra).filter(
SiteEra.site == site,
SiteEra.is_physical == true(),
Era.start_date <= finish_date,
or_(Era.finish_date == null(),
Era.finish_date >= start_date),
Source.code != "sub",
).options(
joinedload(Era.supply).joinedload(Supply.source),
joinedload(Era.supply).joinedload(Supply.generator_type),
)):
mtype = era.meter_category
if metering_type == "" or TYPE_ORDER[mtype] < TYPE_ORDER[
metering_type]:
metering_type = mtype
sources.add(era.supply.source.code)
generator_type = era.supply.generator_type
if generator_type is not None:
generator_types.add(generator_type.code)
assoc_str = ",".join(sorted(s.code for s in assoc))
sources_str = ",".join(sorted(list(sources)))
generators_str = ",".join(sorted(list(generator_types)))
for hh in site.hh_data(sess, start_date, finish_date):
write_row(
writer,
site.code,
site.name,
assoc_str,
sources_str,
generators_str,
hh["start_date"],
hh["imp_net"],
hh["displaced"],
hh["exp_net"],
hh["used"],
hh["exp_gen"],
hh["imp_gen"],
hh["imp_3p"],
hh["exp_3p"],
metering_type,
)
except BaseException:
msg = traceback.format_exc()
sys.stderr.write(msg)
writer.writerow([msg])
finally:
if sess is not None:
sess.close()
if f is not None:
f.close()
os.rename(running_name, finished_name)
def grab_query_results(
q,
total_matches,
page,
n_items_per_page,
items_name,
order_by=None,
include_photometry=False,
include_spectra=False,
):
# The query will return multiple rows per candidate object if it has multiple
# annotations associated with it, with rows appearing at the end of the query
# for any annotations with origins not equal to the one being sorted on (if applicable).
# We want to essentially grab only the candidate objects as they first appear
# in the query results, and ignore these other irrelevant annotation entries.
# Add a "row_num" column to the desire query that explicitly encodes the ordering
# of the query results - remember that these query rows are essentially
# (Obj, Annotation) tuples, so the earliest row numbers for a given Obj is
# the one we want to adhere to (and the later ones are annotation records for
# the candidate that are not being sorted/filtered on right now)
#
# The row number must be preserved like this in order to remember the desired
# ordering info even while using the passed in query as a subquery to select
# from. This is because subqueries provide a set of results to query from,
# losing any order_by information.
row = func.row_number().over(order_by=order_by).label("row_num")
full_query = q.add_column(row)
info = {}
full_query = full_query.subquery()
# Using the PostgreSQL DISTINCT ON keyword, we grab the candidate Obj ids
# in the order that they first appear in the query (per the row_num values)
# NOTE: It is probably possible to grab the full Obj records here instead of
# just the ID values, but querying "full_query" here means we lost the original
# ORM mappings, so we would have to explicity re-label the columns here.
# It is much more straightforward to just get an ordered list of Obj ID
# values here and get the corresponding n_items_per_page full Obj objects
# at the end, I think, for minimal additional overhead.
ids_with_row_nums = (DBSession().query(
full_query.c.id,
full_query.c.row_num).distinct(full_query.c.id).order_by(
full_query.c.id, full_query.c.row_num).subquery())
# Grouping and getting the first distinct obj_id above messed up the order
# in the query set, so re-order by the row_num we used to remember the
# original ordering
ordered_ids = (DBSession().query(ids_with_row_nums.c.id).order_by(
ids_with_row_nums.c.row_num))
if total_matches:
info["totalMatches"] = int(total_matches)
else:
info["totalMatches"] = ordered_ids.count()
if page:
if (((info["totalMatches"] < (page - 1) * n_items_per_page
and info["totalMatches"] % n_items_per_page != 0) or
(info["totalMatches"] < page * n_items_per_page
and info["totalMatches"] % n_items_per_page == 0)
and info["totalMatches"] != 0) or page <= 0
or (info["totalMatches"] == 0 and page != 1)):
raise ValueError("Page number out of range.")
# Now bring in the full Obj info for the candidates
page_ids = (ordered_ids.limit(n_items_per_page).offset(
(page - 1) * n_items_per_page).all())
info["pageNumber"] = page
info["numPerPage"] = n_items_per_page
else:
page_ids = ordered_ids.all()
items = []
query_options = [joinedload(Obj.thumbnails)]
if include_photometry:
query_options.append(
joinedload(Obj.photometry).joinedload(Photometry.instrument))
if include_spectra:
query_options.append(
joinedload(Obj.spectra).joinedload(Spectrum.instrument))
for item_id in page_ids:
items.append(Obj.query.options(query_options).get(item_id))
info[items_name] = items
return info
def get_role_list(
session, ):
return session.query(Roles).options(joinedload(Roles.groups)).order_by(
Roles.role_name).all()
def get_post_with_comments(pid, sort_type="top", v=None):
post = get_post(pid, v=v)
if v:
votes = g.db.query(CommentVote).filter_by(user_id=v.id).subquery()
blocking = v.blocking.subquery()
blocked = v.blocked.subquery()
comms = g.db.query(
Comment, votes.c.vote_type, blocking.c.id, blocked.c.id).options(
joinedload(Comment.author).joinedload(User.title)).filter(
Comment.parent_submission == post.id,
Comment.level <= 6).join(
votes, votes.c.comment_id == Comment.id,
isouter=True).join(
blocking,
blocking.c.target_id == Comment.author_id,
isouter=True).join(
blocked,
blocked.c.user_id == Comment.author_id,
isouter=True)
if sort_type == "hot":
comments = comms.order_by(Comment.score_hot.desc()).all()
elif sort_type == "top":
comments = comms.order_by(Comment.score_top.desc()).all()
elif sort_type == "new":
comments = comms.order_by(Comment.created_utc.desc()).all()
elif sort_type == "disputed":
comments = comms.order_by(Comment.score_disputed.desc()).all()
elif sort_type == "random":
c = comms.all()
comments = random.sample(c, k=len(c))
else:
abort(422)
output = []
for c in comments:
comment = c[0]
comment._voted = c[1] or 0
comment._is_blocking = c[2] or 0
comment._is_blocked = c[3] or 0
output.append(comment)
post._preloaded_comments = output
else:
comms = g.db.query(Comment).options(
joinedload(Comment.author).joinedload(User.title)).filter(
Comment.parent_submission == post.id, Comment.level <= 6)
if sort_type == "hot":
comments = comms.order_by(Comment.score_hot.desc()).all()
elif sort_type == "top":
comments = comms.order_by(Comment.score_top.desc()).all()
elif sort_type == "new":
comments = comms.order_by(Comment.created_utc.desc()).all()
elif sort_type == "disputed":
comments = comms.order_by(Comment.score_disputed.desc()).all()
elif sort_type == "random":
c = comms.all()
comments = random.sample(c, k=len(c))
else:
abort(422)
output = [c for c in comments]
post._preloaded_comments = output
return post
def get_instance_list(
session, ):
return session.query(Instances).options(
joinedload(Instances.groups),
joinedload(Instances.plugins)).order_by(Instances.hostname).all()
## That was relationships
# Eager loading
from sqlalchemy.orm import subqueryload
jack = session.query(User).\
options(subqueryload(User.addresses)).\
filter_by(name='jack').one()
jack
jack.addresses
from sqlalchemy.orm import joinedload
jack = session.query(User).\
options(joinedload(User.addresses)).\
filter_by(name='jack').one()
jack
jack.addresses
from sqlalchemy.orm import contains_eager
jacks_addresses = session.query(Address).\
join(Address.user).\
filter(User.name=='jack').\
options(contains_eager(Address.user)).\
all()
jacks_addresses
jacks_addresses[0].user
def get_repository_in_tool_shed(app, id, eagerload_columns=None):
"""Get a repository on the tool shed side from the database via id."""
q = get_repository_query(app)
if eagerload_columns:
q = q.options(joinedload(*eagerload_columns))
return q.get(app.security.decode_id(id))
def go():
eq_(
sess.query(Parent).options(joinedload(Parent.children)).all(),
[p1, p2])
def _ensure_valid_session(self, session_cookie, create=True):
"""
Ensure that a valid Galaxy session exists and is available as
trans.session (part of initialization)
Support for universe_session and universe_user cookies has been
removed as of 31 Oct 2008.
"""
# Try to load an existing session
secure_id = self.get_cookie(name=session_cookie)
galaxy_session = None
prev_galaxy_session = None
user_for_new_session = None
invalidate_existing_session = False
# Track whether the session has changed so we can avoid calling flush
# in the most common case (session exists and is valid).
galaxy_session_requires_flush = False
if secure_id:
# Decode the cookie value to get the session_key
try:
session_key = self.security.decode_guid(secure_id)
if session_key:
# Retrieve the galaxy_session id via the unique session_key
galaxy_session = self.sa_session.query(self.app.model.GalaxySession) \
.filter(and_(self.app.model.GalaxySession.table.c.session_key == session_key,
self.app.model.GalaxySession.table.c.is_valid == true())).options(joinedload("user")).first()
except Exception:
# We'll end up creating a new galaxy_session
session_key = None
# If remote user is in use it can invalidate the session and in some
# cases won't have a cookie set above, so we need to to check some
# things now.
if self.app.config.use_remote_user:
remote_user_email = self.environ.get(self.app.config.remote_user_header, None)
if galaxy_session:
if remote_user_email and galaxy_session.user is None:
# No user, associate
galaxy_session.user = self.get_or_create_remote_user(remote_user_email)
galaxy_session_requires_flush = True
elif (remote_user_email and
(galaxy_session.user.email != remote_user_email) and
((not self.app.config.allow_user_impersonation) or
(remote_user_email not in self.app.config.admin_users_list))):
# Session exists but is not associated with the correct
# remote user, and the currently set remote_user is not a
# potentially impersonating admin.
invalidate_existing_session = True
user_for_new_session = self.get_or_create_remote_user(remote_user_email)
log.warning("User logged in as '%s' externally, but has a cookie as '%s' invalidating session",
remote_user_email, galaxy_session.user.email)
elif remote_user_email:
# No session exists, get/create user for new session
user_for_new_session = self.get_or_create_remote_user(remote_user_email)
if ((galaxy_session and galaxy_session.user is None) and user_for_new_session is None):
raise Exception("Remote Authentication Failure - user is unknown and/or not supplied.")
else:
if galaxy_session is not None and galaxy_session.user and galaxy_session.user.external:
# Remote user support is not enabled, but there is an existing
# session with an external user, invalidate
invalidate_existing_session = True
log.warning("User '%s' is an external user with an existing session, invalidating session since external auth is disabled",
galaxy_session.user.email)
elif galaxy_session is not None and galaxy_session.user is not None and galaxy_session.user.deleted:
invalidate_existing_session = True
log.warning("User '%s' is marked deleted, invalidating session" % galaxy_session.user.email)
# Do we need to invalidate the session for some reason?
if invalidate_existing_session:
prev_galaxy_session = galaxy_session
prev_galaxy_session.is_valid = False
galaxy_session = None
# No relevant cookies, or couldn't find, or invalid, so create a new session
if galaxy_session is None:
galaxy_session = self.__create_new_session(prev_galaxy_session, user_for_new_session)
galaxy_session_requires_flush = True
self.galaxy_session = galaxy_session
self.__update_session_cookie(name=session_cookie)
else:
self.galaxy_session = galaxy_session
# Do we need to flush the session?
if galaxy_session_requires_flush:
self.sa_session.add(galaxy_session)
# FIXME: If prev_session is a proper relation this would not
# be needed.
if prev_galaxy_session:
self.sa_session.add(prev_galaxy_session)
self.sa_session.flush()
# If the old session was invalid, get a new (or existing default,
# unused) history with our new session
if invalidate_existing_session:
self.get_or_create_default_history()
def go():
for a in session.query(a_poly).\
options(
joinedload(a_poly.B.related),
joinedload(a_poly.C.related)):
eq_(a.related, [d])
def go():
eq_(
sess.query(Subparent).options(joinedload("children")).all(),
[p1, p2])
def main(client_id, epoch_start, rends_per_epoch, dry_run, max_dist, host,
port, required_substances):
loop = asyncio.get_event_loop()
http_sess = loop.run_until_complete(make_http_client())
app = brender.Brender()
collector_ctx = {
'host': host,
'port': port,
'sess': http_sess,
'client_id': client_id,
}
if required_substances:
required_substances = set(required_substances.split(','))
assert all(s in SUBSTANCES for s in required_substances)
for epoch in itertools.count(start=epoch_start):
logger.info('Starting epoch %d', epoch)
with session_scope() as sess:
pairs = (sess.query(ExemplarShapePair).join(Shape).join(
Exemplar).filter(
sa.and_(
ExemplarShapePair.distance < max_dist,
sa.not_(Shape.exclude),
sa.not_(Exemplar.exclude),
Shape.split_set.isnot(None),
)).options(orm.joinedload(ExemplarShapePair.exemplar),
orm.joinedload(
ExemplarShapePair.shape)).order_by(
Shape.id.asc()).all())
materials = sess.query(
models.Material).filter_by(enabled=True).all()
envmaps = sess.query(models.Envmap).filter_by(enabled=True).all()
cam_angles = [(p.azimuth, p.elevation) for p in pairs]
logger.info('Loaded %d pairs and %d camera angles', len(pairs),
len(cam_angles))
mats_by_subst = collections.defaultdict(list)
for material in materials:
mats_by_subst[material.substance].append(material)
envmaps_by_split = {
'train': [e for e in envmaps if e.split_set == 'train'],
'validation': [e for e in envmaps if e.split_set == 'validation'],
}
for i in range(1000):
pair = random.choice(pairs)
if not pair.data_exists(config.PAIR_SHAPE_CLEAN_SEGMENT_MAP_NAME):
continue
app.init()
logger.info('pair %d, shape %s, exemplar %d', pair.id,
pair.shape.id, pair.exemplar.id)
try:
loop.run_until_complete(
process_pair(app,
pair,
cam_angles,
mats_by_subst,
envmaps_by_split=envmaps_by_split,
num_rends=rends_per_epoch,
is_dry_run=dry_run,
epoch=epoch,
collector_ctx=collector_ctx,
required_substances=required_substances))
except Exception as e:
logger.exception('Uncaught exception', exc_info=True)
continue
def go():
for a in session.query(A).with_polymorphic([B, C]).\
options(joinedload(B.related), joinedload(C.related)):
eq_(a.related, [d])
def get(self, group_id, slug=None, discipline_id=None):
group = Group.query.get_or_404(group_id)
if current_user_is_logged():
disciplines = Discipline.query
else:
disciplines = group.disciplines
if not discipline_id:
discipline_id = request.cookies.get('discipline_id', None)
if not discipline_id:
discipline = disciplines.first()
if discipline:
discipline_id = discipline.id
else:
return redirect(url_for("university.index"))
discipline = disciplines.filter(Discipline.id == discipline_id) \
.options(joinedload('labs'), joinedload('files'), joinedload('articles')).first()
if discipline is None:
discipline = disciplines.first()
# if not discipline and user is not logged then redirect
if not discipline and not current_user_is_logged():
return redirect(url_for('security.login', next=request.path))
if group.year not in Group.current_year(
) and not current_user_is_logged():
return redirect(url_for('security.login', next=request.path))
def make_cache_key():
return cache_key_for_students_marks(group.id, discipline.id)
def get_all_data(group, discipline):
lessons = Lesson.query.filter(Lesson.group_id == group.id) \
.filter(Lesson.discipline_id == discipline.id) \
.order_by(Lesson.date, Lesson.id).all()
students = group.students \
.outerjoin(Student.marks) \
.options(contains_eager(Student.marks)) \
.filter(or_(Mark.lesson_id == None, Mark.lesson_id.in_([i.id for i in lessons]))).all()
labs = discipline.labs
students_info = {}
for student in students:
student_info = {
'marks': {},
'tasks': {},
'points': 0,
'percents': 0,
}
students_info[student.id] = student_info
for mark in student.marks:
student_info['marks'][mark.lesson_id] = mark
for task in student.tasks:
student_info['tasks'][task.task_id] = task
student_info['points'], student_info['percents'] \
= student.points(student_info['marks'],
lessons,
sum([len([t for t in lab.tasks if not t.ignore]) for lab in labs if lab.regular and lab.visible]),
len(student_info['tasks']))
return {
'students': students,
'lessons': lessons,
'labs': labs,
'students_info': students_info
}
data = get_all_data(group, discipline)
template = "university/group.html"
if request.headers.get('X-Pjax', None):
template = "university/_marks.html"
response = make_response(
render_template(
template,
group=group,
discipline=discipline,
students=data['students'],
lessons=data['lessons'],
labs=data['labs'],
articles=[
a for a in discipline.articles
if a.visible or current_user_is_logged()
],
disciplines=disciplines.order_by(Discipline.title).all(),
has_visible_labs=len(
[lab for lab in data['labs'] if lab.visible]) > 0,
students_info=data['students_info'],
lesson_types=Lesson.LESSON_TYPES,
marks_types=Mark.MARKS,
))
response.set_cookie('discipline_id', str(discipline_id))
return response
def fetch_multiple_recordings(mbids, includes=None):
""" Fetch multiple recordings with MusicBrainz IDs.
Args:
mbids (list): list of uuid (MBID(gid)) of the recordings.
includes (list): List of values to be included.
For list of possible values visit https://bitbucket.org/lalinsky/mbdata/wiki/API/v1/includes#!recording
Returns:
Dictionary containing the recording information with MBIDs as keys.
- id: Recording mbid
- name: Name of the recording
- length: length of the recording
- artists:
- artist information: id, name, credited_name and join_phrase
"""
if includes is None:
includes = []
includes_data = defaultdict(dict)
check_includes('recording', includes)
with mb_session() as db:
query = db.query(Recording)
if 'artist' in includes or 'artists' in includes:
query = query.options(joinedload("artist_credit", innerjoin=True))
if 'artists' in includes:
query = query.\
options(subqueryload("artist_credit.artists")).\
options(joinedload("artist_credit.artists.artist", innerjoin=True))
recordings = get_entities_by_gids(
query=query,
entity_type='recording',
mbids=mbids,
)
recording_ids = [recording.id for recording in recordings.values()]
if 'artist' in includes:
for recording in recordings.values():
includes_data[recording.id]['artist'] = recording.artist_credit
if 'artists' in includes:
for recording in recordings.values():
includes_data[recording.id]['artists'] = recording.artist_credit.artists
if 'url-rels' in includes:
get_relationship_info(
db=db,
target_type='url',
source_type='recording',
source_entity_ids=recording_ids,
includes_data=includes_data,
)
if 'work-rels' in includes:
get_relationship_info(
db=db,
target_type='work',
source_type='recording',
source_entity_ids=recording_ids,
includes_data=includes_data,
)
serial_recordings = {str(mbid): serialize_recording(recordings[mbid], includes_data[recordings[mbid].id]) for mbid in mbids}
return serial_recordings
def toggle_order_shipped(self):
"""
toggle Shipped Date, to trigger
* balance adjustment
* cascade to OrderDetails
* and Product adjustment
also test join
"""
# fails on post_cust with TypeError: my_load_listener() got an unexpected keyword argument 'attrs'
# @sqlalchemy.event.listens_for(sqlalchemy.orm.Mapper, 'refresh', named=True)
def my_load_listener(target, context):
print("on load!")
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
print(f'type(Base): {type(Base)}'
) # sqlalchemy.ext.declarative.api.DeclarativeMeta
do_listen = False
if do_listen:
event.listen(
sqlalchemy.ext.declarative.api.
DeclarativeMeta, # models.Customer is fine
'load',
my_load_listener)
# , propagate=True) # TypeError: descriptor '__subclasses__' of 'type' object needs an argument
pre_cust = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
supers = pre_cust.__class__.__bases__
for each_super in supers:
print(f'pre_cust super: {type(each_super)}')
session.expunge(pre_cust)
print("")
test_order = session.query(models.Order).filter(models.Order.Id == 11011).\
join(models.Employee).options(joinedload(models.Order.SalesRep)).\
one()
if test_order.ShippedDate is None or test_order.ShippedDate == "":
test_order.ShippedDate = str(datetime.now())
print(
prt("Shipping order - ShippedDate: ['' -> " +
test_order.ShippedDate + "]"))
else:
test_order.ShippedDate = None
print(prt("Returning order - ShippedDate: [ -> None]"))
session.commit()
print("")
post_cust = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
logic_row = LogicRow(row=post_cust,
old_row=pre_cust,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
if abs(post_cust.Balance - pre_cust.Balance) == 960:
logic_row.log("Correct adjusted Customer Result")
assert True
else:
self.fail(
logic_row.log(
"ERROR - incorrect adjusted Customer Result (not 960 delta)"
))
if post_cust.Balance == 56:
pass
else:
self.fail(logic_row.log("ERROR - balance should be 56"))
if post_cust.UnpaidOrderCount == 3 and pre_cust.UnpaidOrderCount == 4:
pass
else:
self.fail(logic_row.log("Error - UnpaidOrderCount should be 3"))
def eager_query(cls):
query = super(Directive, cls).eager_query()
return cls.eager_inclusions(query, Directive._include_links).options(
orm.joinedload('audit_frequency'),
orm.joinedload('audit_duration'), orm.subqueryload('controls'))
| Subquery Load - loading all collections at once
+-------------------------------------------------------------------------+
"""
from sqlalchemy.orm import subqueryload
for user in session.query(User).options(subqueryload(User.addresses)):
print(user, user.addresses)
pass
"""
+-------------------------------------------------------------------------+
| 12. Joined Load - users LEFT OUTER JOIN to load parent + child in on query
+-------------------------------------------------------------------------+
"""
from sqlalchemy.orm import joinedload
for user in session.query(User).options(joinedload(User.addresses)):
print(user, user.addresses)
pass
"""
+-------------------------------------------------------------------------+
| 13. Join and a subquery load - use contains_eager
+-------------------------------------------------------------------------+
"""
from sqlalchemy.orm import contains_eager
for address in (session.query(Address).join(Address.user).options(
joinedload(Address.user))):
print(address, address.user)
pass
# we can go like this instead
def test_unbound_options(self):
User, Address, Keyword, Order, Item = self.classes(
"User", "Address", "Keyword", "Order", "Item"
)
self._run_cache_key_fixture(
lambda: (
joinedload(User.addresses),
joinedload(User.addresses.of_type(aliased(Address))),
joinedload("addresses"),
joinedload(User.orders),
joinedload(User.orders.and_(Order.id != 5)),
joinedload(User.orders.and_(Order.id == 5)),
joinedload(User.orders.and_(Order.description != "somename")),
joinedload(User.orders).selectinload("items"),
joinedload(User.orders).selectinload(Order.items),
defer(User.id),
defer("id"),
defer("*"),
defer(Address.id),
subqueryload(User.orders),
selectinload(User.orders),
joinedload(User.addresses).defer(Address.id),
joinedload(aliased(User).addresses).defer(Address.id),
joinedload(User.addresses).defer("id"),
joinedload(User.orders).joinedload(Order.items),
joinedload(User.orders).subqueryload(Order.items),
subqueryload(User.orders).subqueryload(Order.items),
subqueryload(User.orders)
.subqueryload(Order.items)
.defer(Item.description),
defaultload(User.orders).defaultload(Order.items),
defaultload(User.orders),
),
compare_values=True,
)
def base_query(self, query):
return query.join(Cognateset.contribution)\
.options(joinedload(Cognateset.contribution))
def budget_index():
budgets = Budget.query.options(joinedload("user")).all()
return jsonify(budgets_schema.dump(budgets))
def test_i18n_table_creation():
"""Creates and manipulates a magical i18n table, completely independent of
the existing schema and data. Makes sure that the expected behavior of the
various proxies and columns works.
"""
Base = declarative_base()
engine = create_engine("sqlite:///:memory:", echo=True)
Base.metadata.bind = engine
# Need this for the foreign keys to work!
class Language(Base):
__tablename__ = 'languages'
id = Column(Integer, primary_key=True, nullable=False)
identifier = Column(String(2), nullable=False, unique=True)
class Foo(Base):
__tablename__ = 'foos'
__singlename__ = 'foo'
id = Column(Integer, primary_key=True, nullable=False)
translation_classes = []
FooText = create_translation_table(
'foo_text',
Foo,
'texts',
language_class=Language,
name=Column(String(100)),
)
# OK, create all the tables and gimme a session
Base.metadata.create_all()
sm = sessionmaker(class_=MultilangSession)
sess = MultilangScopedSession(sm)
# Create some languages and foos to bind together
lang_en = Language(identifier='en')
sess.add(lang_en)
lang_jp = Language(identifier='jp')
sess.add(lang_jp)
lang_ru = Language(identifier='ru')
sess.add(lang_ru)
foo = Foo()
sess.add(foo)
# Commit so the above get primary keys filled in, then give the
# session the language id
sess.commit()
# Note that this won't apply to sessions created in other threads, but that
# ought not be a problem!
sess.default_language_id = lang_en.id
# Give our foo some names, as directly as possible
foo_text = FooText()
foo_text.foreign_id = foo.id
foo_text.local_language_id = lang_en.id
foo_text.name = 'english'
sess.add(foo_text)
foo_text = FooText()
foo_text.foo_id = foo.id
foo_text.local_language_id = lang_jp.id
foo_text.name = 'nihongo'
sess.add(foo_text)
# Commit! This will expire all of the above.
sess.commit()
### Test 1: re-fetch foo and check its attributes
foo = sess.query(Foo).params(_default_language='en').one()
# Dictionary of language identifiers => names
assert foo.name_map[lang_en] == 'english'
assert foo.name_map[lang_jp] == 'nihongo'
# Default language, currently English
assert foo.name == 'english'
sess.expire_all()
### Test 2: querying by default language name should work
foo = sess.query(Foo).filter_by(name='english').one()
assert foo.name == 'english'
sess.expire_all()
### Test 3: joinedload on the default name should appear to work
# THIS SHOULD WORK SOMEDAY
# .options(joinedload(Foo.name)) \
foo = sess.query(Foo) \
.options(joinedload(Foo.texts_local)) \
.one()
assert foo.name == 'english'
sess.expire_all()
### Test 4: joinedload on all the names should appear to work
# THIS SHOULD ALSO WORK SOMEDAY
# .options(joinedload(Foo.name_map)) \
foo = sess.query(Foo) \
.options(joinedload(Foo.texts)) \
.one()
assert foo.name_map[lang_en] == 'english'
assert foo.name_map[lang_jp] == 'nihongo'
sess.expire_all()
### Test 5: Mutating the dict collection should work
foo = sess.query(Foo).one()
foo.name_map[lang_en] = 'different english'
foo.name_map[lang_ru] = 'new russian'
sess.commit()
assert foo.name_map[lang_en] == 'different english'
assert foo.name_map[lang_ru] == 'new russian'
def base_query(self, query):
query = Sources.base_query(self, query)
query = query.join(LexibankSource.provider).options(
joinedload(LexibankSource.provider))
return query
def _test_options_dont_pollute(self, enable_baked):
Parent, ChildSubclass1, Other = self.classes("Parent",
"ChildSubclass1", "Other")
session = fixture_session(enable_baked_queries=enable_baked)
def no_opt():
q = session.query(Parent).options(
joinedload(Parent.children.of_type(ChildSubclass1)))
return self.assert_sql_execution(
testing.db,
q.all,
CompiledSQL(
"SELECT parent.id AS parent_id, "
"anon_1.child_id AS anon_1_child_id, "
"anon_1.child_parent_id AS anon_1_child_parent_id, "
"anon_1.child_type AS anon_1_child_type, "
"anon_1.child_subclass1_id AS anon_1_child_subclass1_id "
"FROM parent "
"LEFT OUTER JOIN (SELECT child.id AS child_id, "
"child.parent_id AS child_parent_id, "
"child.type AS child_type, "
"child_subclass1.id AS child_subclass1_id "
"FROM child "
"LEFT OUTER JOIN child_subclass1 "
"ON child.id = child_subclass1.id) AS anon_1 "
"ON parent.id = anon_1.child_parent_id",
{},
),
CompiledSQL(
"SELECT child_subclass1.id AS child_subclass1_id, "
"child.id AS child_id, "
"child.parent_id AS child_parent_id, "
"child.type AS child_type "
"FROM child JOIN child_subclass1 "
"ON child.id = child_subclass1.id "
"WHERE child.id IN ([POSTCOMPILE_primary_keys]) "
"ORDER BY child.id",
[{
"primary_keys": [1]
}],
),
)
result = no_opt()
with self.assert_statement_count(testing.db, 1):
eq_(result, [Parent(children=[ChildSubclass1(others=[Other()])])])
session.expunge_all()
q = session.query(Parent).options(
joinedload(Parent.children.of_type(ChildSubclass1)).joinedload(
ChildSubclass1.others))
result = self.assert_sql_execution(
testing.db,
q.all,
CompiledSQL(
"SELECT parent.id AS parent_id, "
"anon_1.child_id AS anon_1_child_id, "
"anon_1.child_parent_id AS anon_1_child_parent_id, "
"anon_1.child_type AS anon_1_child_type, "
"anon_1.child_subclass1_id AS anon_1_child_subclass1_id, "
"other_1.id AS other_1_id, "
"other_1.child_subclass_id AS other_1_child_subclass_id "
"FROM parent LEFT OUTER JOIN "
"(SELECT child.id AS child_id, "
"child.parent_id AS child_parent_id, "
"child.type AS child_type, "
"child_subclass1.id AS child_subclass1_id "
"FROM child LEFT OUTER JOIN child_subclass1 "
"ON child.id = child_subclass1.id) AS anon_1 "
"ON parent.id = anon_1.child_parent_id "
"LEFT OUTER JOIN other AS other_1 "
"ON anon_1.child_subclass1_id = other_1.child_subclass_id",
{},
),
CompiledSQL(
"SELECT child_subclass1.id AS child_subclass1_id, "
"child.id AS child_id, child.parent_id AS child_parent_id, "
"child.type AS child_type, other_1.id AS other_1_id, "
"other_1.child_subclass_id AS other_1_child_subclass_id "
"FROM child JOIN child_subclass1 "
"ON child.id = child_subclass1.id "
"LEFT OUTER JOIN other AS other_1 "
"ON child_subclass1.id = other_1.child_subclass_id "
"WHERE child.id IN ([POSTCOMPILE_primary_keys]) "
"ORDER BY child.id",
[{
"primary_keys": [1]
}],
),
)
with self.assert_statement_count(testing.db, 0):
eq_(result, [Parent(children=[ChildSubclass1(others=[Other()])])])
session.expunge_all()
result = no_opt()
with self.assert_statement_count(testing.db, 1):
eq_(result, [Parent(children=[ChildSubclass1(others=[Other()])])])
def banphrases_create(**options):
session.pop("banphrase_created_id", None)
session.pop("banphrase_edited_id", None)
if request.method == "POST":
id = None
try:
if "id" in request.form:
id = int(request.form["id"])
name = request.form["name"].strip()
permanent = request.form.get("permanent", "off")
warning = request.form.get("warning", "off")
notify = request.form.get("notify", "off")
case_sensitive = request.form.get("case_sensitive", "off")
sub_immunity = request.form.get("sub_immunity", "off")
remove_accents = request.form.get("remove_accents", "off")
length = int(request.form["length"])
phrase = request.form["phrase"]
operator = request.form["operator"].strip().lower()
except (KeyError, ValueError):
abort(403)
permanent = permanent == "on"
warning = warning == "on"
notify = notify == "on"
case_sensitive = case_sensitive == "on"
sub_immunity = sub_immunity == "on"
remove_accents = remove_accents == "on"
if not name:
abort(403)
if not phrase:
abort(403)
if length < 0 or length > 1209600:
abort(403)
valid_operators = [
"contains", "startswith", "endswith", "exact", "regex"
]
if operator not in valid_operators:
abort(403)
user = options.get("user", None)
if user is None:
abort(403)
options = {
"name": name,
"phrase": phrase,
"permanent": permanent,
"warning": warning,
"notify": notify,
"case_sensitive": case_sensitive,
"sub_immunity": sub_immunity,
"remove_accents": remove_accents,
"length": length,
"added_by": user.discord_id,
"edited_by": user.discord_id,
"operator": operator,
}
if id is None:
banphrase = Banphrase(**options)
banphrase.data = BanphraseData(banphrase.id,
added_by=options["added_by"])
with DBManager.create_session_scope(
expire_on_commit=False) as db_session:
if id is not None:
banphrase = (db_session.query(Banphrase).options(
joinedload(
Banphrase.data)).filter_by(id=id).one_or_none())
if banphrase is None:
return redirect("/admin/banphrases/", 303)
banphrase.set(**options)
banphrase.data.set(edited_by=options["edited_by"])
log.info(
f"Updated banphrase ID {banphrase.id} by user ID {options['edited_by']}"
)
AdminLogManager.post("Banphrase edited", user.discord_id,
banphrase.id, banphrase.phrase)
else:
db_session.add(banphrase)
db_session.add(banphrase.data)
db_session.flush()
log.info(
f"Added a new banphrase by user ID {options['added_by']}"
)
AdminLogManager.post("Banphrase added", user.discord_id,
banphrase.id, banphrase.phrase)
SocketClientManager.send("banphrase.update", {"id": banphrase.id})
if id is None:
session["banphrase_created_id"] = banphrase.id
else:
session["banphrase_edited_id"] = banphrase.id
return redirect("/admin/banphrases/", 303)
else:
return render_template("admin/create_banphrase.html")
