def _generate_stmt(self, id) : font_name = self.font_name font_size = self.font_size dots = self.dots glyph_box = self.glyph_box dot_padding = self.dot_padding dot_width = self.dot_width dot_padding_default = dot_padding.alias(name='dot_padding_default') dots_to_dot_widths = select([ dots.c.id.label('id'), case([(dots.c.val == 0, 0.0)], else_ = ((glyph_box.c.width * font_size.c.val * dots.c.val / 20.0) + case([(dot_padding.c.val != None, dot_padding.c.val)] , else_ = dot_padding_default.c.val) * (dots.c.val - 1) )).label('val') ]).select_from(dots.outerjoin(dot_padding, onclause = dots.c.id == dot_padding.c.id)).\ where(safe_eq_comp(dots.c.id, id)).\ where(and_(dot_padding_default.c.id == -1, dots.c.id == font_name.c.id, dots.c.id == font_size.c.id, font_name.c.val == glyph_box.c.name, glyph_box.c.unicode == "U+E1E7")).\ cte(name='dots_to_dot_widths') self.register_stmt(dots_to_dot_widths) self.insert = simple_insert(dot_width, dots_to_dot_widths)
def hourly_query_split(*stations): q, dt = hourly_base_query() for s in stations: gage_name = s.station_name_web navd88correction = s.convert_to_navd88_feet dry_value = s.dry_elevation flag, val = hourly_columns(gage_name, dry_value, navd88correction=navd88correction) # split to 3 columns: Observed, Estimated, Dry. Missing is just missing. q = q.column( expression.case(value=flag, whens={ 'O': val }, else_=None).label(gage_name)) q = q.column( expression.case(value=flag, whens={ 'E': val }, else_=None).label(gage_name + " est")) q = q.column( expression.case(value=flag, whens={ 'D': val }, else_=None).label(gage_name + " dry")) return q, dt
def birth_time(cls): hour = cast(func.extract("hour", cls.birth_datetime), String) minute = cast(func.extract("minute", cls.birth_datetime), String) hour = case([(func.length(hour) == 1, "0" + hour)], else_=hour) minute = case([(func.length(minute) == 1, "0" + minute)], else_=minute) return hour + ":" + minute
def _generate_stmt(self, id) : dynamic = self.dynamic anchor_x = self.anchor_x note_box = self.note_box dynamic_direction = self.dynamic_direction dynamic_padding = self.dynamic_padding staff_position = self.staff_position from_anchor_x = self.from_anchor_x dynamic_padding_default = dynamic_padding.alias(name='dynamic_padding_default') dynamic_to_staff_position = select([ dynamic.c.id.label('id'), # ughh...2.0 magic number for staff ((dynamic_direction.c.val * case([(dynamic_padding.c.val != None, dynamic_padding.c.val)], else_=dynamic_padding_default.c.val)) + case([(dynamic_direction.c.val == 1, sql_min_max([note_box.c.y + note_box.c.height, 2.0], True)), (dynamic_direction.c.val == -1, sql_min_max([note_box.c.y, -2.0], False))])).label('val') ]).select_from(dynamic.outerjoin(dynamic_padding, onclause = dynamic.c.id == dynamic_padding.c.id)).\ where(safe_eq_comp(note_box.c.id if from_anchor_x else dynamic.c.id, id)).\ where(anchor_x.c.id == dynamic.c.id).\ where(note_box.c.id == anchor_x.c.val).\ where(dynamic_padding_default.c.id == -1).\ where(dynamic.c.id == dynamic_direction.c.id).\ cte(name='dynamic_to_staff_position') self.register_stmt(dynamic_to_staff_position) self.insert = simple_insert(staff_position, dynamic_to_staff_position)
def _generate_stmt(self, id) : font_name = self.font_name font_size = self.font_size unicode = self.unicode glyph_box = self.glyph_box glyph_stencil = self.glyph_stencil alignment_directive = self.alignment_directive writer = self.writer extra_eq = self.extra_eq generics_to_stencils = select([ font_name.c.id.label('id'), literal(writer).label('writer'), literal(0).label('sub_id'), font_name.c.val.label('font_name'), font_size.c.val.label('font_size'), unicode.c.val.label('unicode'), case([(alignment_directive.c.x != None, glyph_box.c.x + (alignment_directive.c.x * glyph_box.c.width))], else_=0).label('x'), case([(alignment_directive.c.y != None, glyph_box.c.y + glyph_box.c.height - (alignment_directive.c.y * glyph_box.c.height))], else_=0).label('y'), ]).select_from(font_name.outerjoin(alignment_directive, onclause = alignment_directive.c.id == font_name.c.id)).\ where(safe_eq_comp(font_name.c.id, id)).\ where(and_(glyph_box.c.name == font_name.c.val, glyph_box.c.unicode == unicode.c.val, font_name.c.id == font_size.c.id, font_name.c.id == unicode.c.id, *extra_eq )).\ cte(name='generics_to_stencils') self.register_stmt(generics_to_stencils) self.insert = simple_insert(glyph_stencil, generics_to_stencils)
def pushDB(self, db, sensor_id, parent_domain_id): q = db.query(Domain.domain_id) q = q.filter(and_(Domain.parent_domain_id == parent_domain_id, Domain.domain_name == self.name)) res = q.all() if len(res) == 0: o = Domain() o.domain_name = self.name o.parent_domain_id = parent_domain_id db.add(o) db.flush() self.oid = o.domain_id sd = Sensor_Domain() sd.domain_id = self.oid sd.sensor_id = sensor_id sd.first_seen = self.fs sd.last_seen = self.ls db.add(sd) db.flush() else: self.oid = res[0][0] q = db.query(Sensor_Domain) q = q.filter(and_(Sensor_Domain.domain_id == self.oid, Sensor_Domain.sensor_id == sensor_id)) q.update({Sensor_Domain.first_seen: case([(Sensor_Domain.first_seen > self.fs, self.fs)], else_=Sensor_Domain.first_seen), Sensor_Domain.last_seen: case([(Sensor_Domain.last_seen < self.ls, self.ls)], else_=Sensor_Domain.last_seen)}, synchronize_session=False) return self.oid
def _get_orderby_clauses(order_by_list, session): """Sorts a set of runs based on their natural ordering and an overriding set of order_bys. Runs are naturally ordered first by start time descending, then by run id for tie-breaking. """ clauses = [] ordering_joins = [] clause_id = 0 # contrary to filters, it is not easily feasible to separately handle sorting # on attributes and on joined tables as we must keep all clauses in the same order if order_by_list: for order_by_clause in order_by_list: clause_id += 1 (key_type, key, ascending) = SearchUtils.parse_order_by_for_search_runs(order_by_clause) if SearchUtils.is_attribute(key_type, "="): order_value = getattr(SqlRun, SqlRun.get_attribute_name(key)) else: if SearchUtils.is_metric(key_type, "="): # any valid comparator entity = SqlLatestMetric elif SearchUtils.is_tag(key_type, "="): entity = SqlTag elif SearchUtils.is_param(key_type, "="): entity = SqlParam else: raise MlflowException( "Invalid identifier type '%s'" % key_type, error_code=INVALID_PARAMETER_VALUE, ) # build a subquery first because we will join it in the main request so that the # metric we want to sort on is available when we apply the sorting clause subquery = session.query(entity).filter(entity.key == key).subquery() ordering_joins.append(subquery) order_value = subquery.c.value # sqlite does not support NULLS LAST expression, so we sort first by # presence of the field (and is_nan for metrics), then by actual value # As the subqueries are created independently and used later in the # same main query, the CASE WHEN columns need to have unique names to # avoid ambiguity if SearchUtils.is_metric(key_type, "="): clauses.append( sql.case( [(subquery.c.is_nan.is_(True), 1), (order_value.is_(None), 1)], else_=0 ).label("clause_%s" % clause_id) ) else: # other entities do not have an 'is_nan' field clauses.append( sql.case([(order_value.is_(None), 1)], else_=0).label("clause_%s" % clause_id) ) if ascending: clauses.append(order_value) else: clauses.append(order_value.desc()) clauses.append(SqlRun.start_time.desc()) clauses.append(SqlRun.run_uuid) return clauses, ordering_joins
def sort(self, builder): column = self.column translationClass = column.translationClass attr = column.attr whens = [] for language in column.languages: key = ('translation', translationClass, language) onFactory = lambda aliasedTable: and_( aliasedTable.foreign_id == builder.mappedClass.id, aliasedTable.local_language == language, ) aliasedTable = builder.joinOn(key, onFactory, translationClass) aliasedColumn = getattr(aliasedTable, attr) whens.append((aliasedColumn != None, aliasedColumn)) if attr == 'name': default = builder.mappedClass.identifier else: default = None query = builder.query if self.descending: order = case(whens, else_=default).desc().nullslast() else: order = case(whens, else_=default).asc().nullsfirst() query = query.order_by(order) builder.query = query
def _http_soap(session, cluster_id): return session.query( HTTPSOAP.id, HTTPSOAP.name, HTTPSOAP.is_active, HTTPSOAP.is_internal, HTTPSOAP.transport, HTTPSOAP.host, HTTPSOAP.url_path, HTTPSOAP.method, HTTPSOAP.soap_action, HTTPSOAP.soap_version, HTTPSOAP.data_format, HTTPSOAP.security_id, HTTPSOAP.connection, case([(HTTPSOAP.ping_method != None, HTTPSOAP.ping_method)], else_=DEFAULT_HTTP_PING_METHOD).label('ping_method'), # noqa case([(HTTPSOAP.pool_size != None, HTTPSOAP.pool_size)], else_=DEFAULT_HTTP_POOL_SIZE).label('pool_size'), case([(HTTPSOAP.merge_url_params_req != None, HTTPSOAP.merge_url_params_req)], else_=True).label('merge_url_params_req'), case([(HTTPSOAP.url_params_pri != None, HTTPSOAP.url_params_pri)], else_=URL_PARAMS_PRIORITY.DEFAULT).label('url_params_pri'), case([(HTTPSOAP.params_pri != None, HTTPSOAP.params_pri)], else_=PARAMS_PRIORITY.DEFAULT).label('params_pri'), SecurityBase.sec_type, Service.name.label('service_name'), Service.id.label('service_id'), Service.impl_name.label('service_impl_name'), SecurityBase.name.label('security_name'), SecurityBase.username.label('username'), SecurityBase.password.label('password'), SecurityBase.password_type.label('password_type'),).\ outerjoin(Service, Service.id==HTTPSOAP.service_id).\ outerjoin(SecurityBase, HTTPSOAP.security_id==SecurityBase.id).\ filter(Cluster.id==HTTPSOAP.cluster_id).\ filter(Cluster.id==cluster_id).\ order_by(HTTPSOAP.name)
def demographic_etl(config): # set up connection = get_connection(config) pedsnet_session = init_pedsnet(connection) init_pcornet(connection) # multiple aliases for pedsnet_pcornet_valueset_map # to allow the three named joins gender_value_map = aliased(ValueSetMap) ethnicity_value_map = aliased(ValueSetMap) race_value_map = aliased(ValueSetMap) # extract the data from the person table person = pedsnet_session.query(Person.person_id, Person.birth_date, Person.birth_time, coalesce(gender_value_map.target_concept, 'OT'), coalesce(ethnicity_value_map.target_concept, 'OT'), coalesce(race_value_map.target_concept, 'OT'), bindparam("biobank_flag", "N"), Person.gender_source_value, Person.ethnicity_source_value, Person.race_source_value, Person.site, bindparam("gender_identity", None), bindparam("raw_gender_identity", None), bindparam("sexual_orientation", None), bindparam("raw_sexual_orientation", None) ). \ outerjoin(gender_value_map, and_(gender_value_map.source_concept_class == 'Gender', case([(and_(Person.gender_concept_id == None, gender_value_map.source_concept_id == None), True)], else_=cast(Person.gender_concept_id, String(200)) == gender_value_map.source_concept_id))). \ outerjoin(ethnicity_value_map, and_(ethnicity_value_map.source_concept_class == 'Hispanic', case([(and_(Person.ethnicity_concept_id == None, ethnicity_value_map.source_concept_id == None), True)], else_=cast(Person.ethnicity_concept_id, String(200)) == ethnicity_value_map.source_concept_id))). \ outerjoin(race_value_map, and_(race_value_map.source_concept_class == 'Race', case([(and_(Person.race_concept_id == None, race_value_map.source_concept_id == None), True)], else_=cast(Person.race_concept_id, String(200)) == race_value_map.source_concept_id))).all() # transform data to pcornet names and types # load to demographic table odo(person, Demographic.__table__, dshape='var * {patid: string, birth_date: date, birth_time: string, sex: string,' 'hispanic: string, race: string, biobank_flag: string, raw_sex: string,' 'raw_hispanic: string, raw_race:string, site: string, gender_identity: string,' 'raw_gender_identity: string, sexual_orientation: string, raw_sexual_orientation: string}' ) # close session pedsnet_session.close()
def _generate_stmt(self, id) : #print "@@ON ID", id ## ugggh for y_position ledger_line = self.ledger_line n_lines = self.n_lines staff_space = self.staff_space staff_symbol = self.staff_symbol rhythmic_head_width = self.rhythmic_head_width y_position = self.y_position line_stencil = self.line_stencil ledger_line_to_line_stencil = select([ ledger_line.c.id.label('id'), literal('ledger_line_to_line_stencil').label('writer'), literal(0).label('sub_id'), literal(-0.6).label('x0'), (case([(ledger_line.c.val < 0, staff_space.c.val * n_lines.c.val)], else_ = - staff_space.c.val) - y_position.c.val).label('y0'), (rhythmic_head_width.c.val + 1.0).label('x1'), (case([(ledger_line.c.val < 0, staff_space.c.val * n_lines.c.val)], else_ = - staff_space.c.val) - y_position.c.val).label('y1'), literal(0.13).label('thickness') ]).\ where(safe_eq_comp(ledger_line.c.id, id)).\ where(func.abs(ledger_line.c.val) > 0).\ where(n_lines.c.id == staff_symbol.c.val).\ where(staff_space.c.id == staff_symbol.c.val).\ where(y_position.c.id == ledger_line.c.id).\ where(staff_symbol.c.id == ledger_line.c.id).\ where(rhythmic_head_width.c.id == staff_symbol.c.id).\ cte(name="ledger_line_to_line_stencil", recursive = True) #where(safe_eq_comp(ledger_line.c.id, id)) self.register_stmt(ledger_line_to_line_stencil) ledger_line_to_line_stencil_prev = ledger_line_to_line_stencil.alias(name="ledger_line_to_line_stencil_prev") ledger_line_to_line_stencil = ledger_line_to_line_stencil.union_all( select([ ledger_line_to_line_stencil_prev.c.id, literal('ledger_line_to_line_stencil'), ledger_line_to_line_stencil_prev.c.sub_id + 1, ledger_line_to_line_stencil_prev.c.x0, ledger_line_to_line_stencil_prev.c.y0 + (staff_space.c.val * - 1.0 * ledger_line.c.val / func.abs(ledger_line.c.val)), ledger_line_to_line_stencil_prev.c.x1, ledger_line_to_line_stencil_prev.c.y1 + (staff_space.c.val * -1.0 * ledger_line.c.val / func.abs(ledger_line.c.val)), ledger_line_to_line_stencil_prev.c.thickness ]).\ where(staff_space.c.id == staff_symbol.c.val).\ where(staff_symbol.c.id == ledger_line_to_line_stencil_prev.c.id).\ where(ledger_line_to_line_stencil_prev.c.id == ledger_line.c.id).\ where(ledger_line_to_line_stencil_prev.c.sub_id < func.abs(ledger_line.c.val) - 1) ) self.register_stmt(ledger_line_to_line_stencil) self.insert = simple_insert(line_stencil, ledger_line_to_line_stencil)
def pushDB(self, db, domain_id, rr, value): rr = filter_rrtype[rr] for ttl, v in self.ttls.items(): oid = None try: q = db.query(Entry) q = q.filter(and_(Entry.domain_id == domain_id, Entry.type == rr, Entry.ttl == ttl, Entry.value == value)) q.update({Entry.first_seen: case([(Entry.first_seen > v[1], v[1])], else_=Entry.first_seen), Entry.last_seen: case([(Entry.last_seen < v[2], v[2])], else_=Entry.last_seen), Entry.count: Entry.count + v[0]}, synchronize_session=False) q = db.query(Entry.entry_id) o = q.filter(and_(Entry.domain_id == domain_id, Entry.type == rr, Entry.ttl == ttl, Entry.value == value)).one() oid = o[0] except NoResultFound: entry = Entry() entry.domain_id = domain_id entry.type = rr entry.ttl = ttl entry.value = value entry.first_seen = v[1] entry.last_seen = v[2] entry.count = v[0] db.add(entry) db.flush() oid = entry.entry_id for s, sv in v[3].items(): try: q = db.query(exists().where((and_(DNS_Server.entry_id == oid, DNS_Server.ip == s)))).scalar() if not q: raise NoResultFound('') q = db.query(DNS_Server) q = q.filter(and_(DNS_Server.entry_id == oid, DNS_Server.ip == s)) q.update({DNS_Server.first_seen: case([(DNS_Server.first_seen > sv[1], sv[1])], else_=DNS_Server.first_seen), DNS_Server.last_seen: case([(DNS_Server.last_seen < sv[2], sv[2])], else_=DNS_Server.last_seen), DNS_Server.count: DNS_Server.count + sv[0]}, synchronize_session=False) except NoResultFound: dns_server = DNS_Server() dns_server.entry_id = oid dns_server.ip = s dns_server.first_seen = sv[1] dns_server.last_seen = sv[2] dns_server.count = sv[0] db.add(dns_server) db.flush() db.flush()
def _generate_stmt(self, id) : natural_stem_direction = self.natural_stem_direction beam = self.beam stem_direction = self.stem_direction beam_specialize = self.beam_specialize my_beam = select([ beam.c.val.label('beam') ]).where(safe_eq_comp(beam.c.id, id)).cte(name = 'my_beam') self.register_stmt(my_beam) others_beamed_with_me = select([ beam.c.id.label('id'), beam.c.val.label('val') ]).where(beam.c.val == my_beam.c.beam).\ cte(name = 'others_beamed_with_me') self.register_stmt(others_beamed_with_me) prevailing_direction = select([ func.sum(natural_stem_direction.c.val).label('val'), ]).where(natural_stem_direction.c.id == others_beamed_with_me.c.id).\ cte(name="prevailing_direction") self.register_stmt(prevailing_direction) stem_direction_for_beams = select([ others_beamed_with_me.c.id.label('id'), prevailing_direction.c.val.label('val'), ]).\ cte(name="stem_direction_for_beams") self.register_stmt(stem_direction_for_beams) natural_stem_direction_to_stem_direction = select([ natural_stem_direction.c.id.label('id'), case([(stem_direction_for_beams.c.val != None, case([(stem_direction_for_beams.c.val > 0, 1)], else_ = -1))], else_ = natural_stem_direction.c.val).label('val') ]).select_from(natural_stem_direction.\ outerjoin(stem_direction_for_beams, onclause=natural_stem_direction.c.id == stem_direction_for_beams.c.id)).\ where(safe_eq_comp(natural_stem_direction.c.id, id)) natural_stem_direction_to_stem_direction = natural_stem_direction_to_stem_direction.union( select([ stem_direction_for_beams.c.id.label('id'), case([(stem_direction_for_beams.c.val > 0, 1)], else_ = -1).label('val') ])).\ cte(name='natural_stem_direction_to_stem_direction') self.register_stmt(natural_stem_direction_to_stem_direction) self.insert = simple_insert(stem_direction, natural_stem_direction_to_stem_direction)
def birth_date(cls): year = cast(cls.year_of_birth, String) month = cast(cls.month_of_birth, String) day = cast(cls.day_of_birth, String) month = case([(month == "", "01")], else_=case([(func.length(month) == 1, "0" + month)], else_=month)) day = case([(day == "", "01")], else_=case([(func.length(day) == 1, "0" + day)], else_=day)) return year + "-" + month + "-" + day
def view_index(session, request, lang=None): """ Does the main index page for DDTSS, with list of languages and stats """ user = get_user(request, session) if lang is None: lang = user.lastlanguage_ref if lang is None: lang = 'xx' if lang != 'xx': return redirect('ddtss_index_lang', lang) pending_translations = session.query(Languages, \ func.sum(expression.case([(PendingTranslation.state == PendingTranslation.STATE_PENDING_TRANSLATION, 1)], else_=0)), \ func.sum(expression.case([(PendingTranslation.state == PendingTranslation.STATE_PENDING_REVIEW, 1)], else_=0))) \ .outerjoin(PendingTranslation) \ .group_by(Languages) \ .all() translated = session.query(Languages.language, func.count(Translation.description_id)) \ .join(Translation, Translation.language == Languages.language) \ .group_by(Languages.language) \ .all() # Convert (lang,count) pairs to dict translated = dict(translated) # Combine into one resultset languages = [] total_pending_translation = 0 total_pending_review = 0 total_translated = 0 for row in pending_translations: languages.append(dict(language=row[0].language, fullname=row[0].fullname, enabled=row[0].enabled_ddtss, pending_translation=row[1], pending_review=row[2], translated=translated.get(row[0].language, 0))) total_pending_translation += row[1] total_pending_review += row[2] total_translated += translated.get(row[0].language, 0) # Sort by translated descending #languages.sort(key=lambda x:x['translated'], reverse=True) return render_to_response("ddtss/index.html", {'languages': languages, 'user': user, 'total_pending_translation': total_pending_translation, 'total_pending_review': total_pending_review, 'total_translated': total_translated}, context_instance=RequestContext(request))
def get_datasets(dataset_ids,**kwargs): """ Get a single dataset, by ID """ user_id = int(kwargs.get('user_id')) datasets = [] if len(dataset_ids) == 0: return [] try: dataset_rs = DBSession.query(Dataset.dataset_id, Dataset.data_type, Dataset.data_units, Dataset.data_dimen, Dataset.data_name, Dataset.hidden, Dataset.cr_date, Dataset.created_by, DatasetOwner.user_id, null().label('metadata'), case([(and_(Dataset.hidden=='Y', DatasetOwner.user_id is not None), None)], else_=Dataset.start_time).label('start_time'), case([(and_(Dataset.hidden=='Y', DatasetOwner.user_id is not None), None)], else_=Dataset.frequency).label('frequency'), case([(and_(Dataset.hidden=='Y', DatasetOwner.user_id is not None), None)], else_=Dataset.value).label('value')).filter( Dataset.dataset_id.in_(dataset_ids)).outerjoin(DatasetOwner, and_(DatasetOwner.dataset_id==Dataset.dataset_id, DatasetOwner.user_id==user_id)).all() #convert the value row into a string as it is returned as a binary for dataset_row in dataset_rs: dataset_dict = dataset_row._asdict() if dataset_row.value is not None: dataset_dict['value'] = str(dataset_row.value) if dataset_row.hidden == 'N' or (dataset_row.hidden == 'Y' and dataset_row.user_id is not None): metadata = DBSession.query(Metadata).filter(Metadata.dataset_id == dataset_row.dataset_id).all() dataset_dict['metadata'] = metadata else: dataset_dict['metadata'] = [] datasets.append(namedtuple('Dataset', dataset_dict.keys())(**dataset_dict)) except NoResultFound: raise ResourceNotFoundError("Datasets not found.") return datasets
def daily_data_expr(flag_expr, value_expr, dry_elev): if dry_elev is None: dry_elev = -1000 flag_expr = expression.case([ (flag_expr == _M, _M), (flag_expr == None, _O), ], else_=_E) flag_expr = expression.case([ (func.avg(value_expr) == None, _M), (func.avg(value_expr) < dry_elev, _D), ], else_=func.max(flag_expr)) return (flag_expr, func.avg(value_expr))
def get_dataset(dataset_id,**kwargs): """ Get a single dataset, by ID """ user_id = int(kwargs.get('user_id')) if dataset_id is None: return None try: dataset_rs = DBSession.query(Dataset.dataset_id, Dataset.data_type, Dataset.data_units, Dataset.data_dimen, Dataset.data_name, Dataset.hidden, Dataset.cr_date, Dataset.created_by, DatasetOwner.user_id, null().label('metadata'), case([(and_(Dataset.hidden=='Y', DatasetOwner.user_id is not None), None)], else_=Dataset.start_time).label('start_time'), case([(and_(Dataset.hidden=='Y', DatasetOwner.user_id is not None), None)], else_=Dataset.frequency).label('frequency'), case([(and_(Dataset.hidden=='Y', DatasetOwner.user_id is not None), None)], else_=Dataset.value).label('value')).filter( Dataset.dataset_id==dataset_id).outerjoin(DatasetOwner, and_(DatasetOwner.dataset_id==Dataset.dataset_id, DatasetOwner.user_id==user_id)).one() rs_dict = dataset_rs._asdict() #convert the value row into a string as it is returned as a binary if dataset_rs.value is not None: rs_dict['value'] = str(dataset_rs.value) if dataset_rs.hidden == 'N' or (dataset_rs.hidden == 'Y' and dataset_rs.user_id is not None): metadata = DBSession.query(Metadata).filter(Metadata.dataset_id==dataset_id).all() rs_dict['metadata'] = metadata else: rs_dict['metadata'] = [] except NoResultFound: raise HydraError("Dataset %s does not exist."%(dataset_id)) dataset = namedtuple('Dataset', rs_dict.keys())(**rs_dict) return dataset
def leaderboard_query(session, start_date, until_date): """ This is, admittedly, a really ugly sql query. Query optimization has not been performed, but it shouldn't be anything more complicated than a few indices. Good luck. """ #start_date = datetime.strptime(start_date, '%Y-%m-%d') #until_date = datetime.strptime(until_date_str, '%Y-%m-%d') subq = session\ .query( Instance, InstanceType, User, case([(Instance.end_date != None, Instance.end_date)], else_=now()).label('stop_date'))\ .join(Instance.user)\ .join(Instance.type)\ .subquery() uptime_column = case( [ (subq.c.created_date > until_date, 0), (subq.c.stop_date < start_date, 0) ], else_=extract('epoch', func.LEAST(subq.c.stop_date, cast(until_date, DateTime)) - func.GREATEST(subq.c.created_date, cast(start_date, DateTime)) ) ) print subq.c subq2 = session.query( subq.c.user_id, sum(case([(uptime_column == 0, 0)], else_=1)).label('instance_count'), #func.count(subq.c.instance_id).label('instance_count'), sum(uptime_column).label('uptime'), sum(uptime_column * subq.c.cpu).label('cpu_seconds') ).group_by(subq.c.user_id).order_by(desc('cpu_seconds')).subquery() q = session.query( subq2.c.user_id, subq2.c.uptime, subq2.c.cpu_seconds, subq2.c.instance_count, User.username, User.is_staff, User.name ).join(User) return q
def daily_data_expr(flag_expr, value_expr, dry_elev): if dry_elev is None: dry_elev = -1000 flag_expr = expression.case([ (flag_expr == _M, _M), (flag_expr == None, _O), ], else_=_E) flag_expr = expression.case([ (func.avg(value_expr) == None, _M), (func.avg(value_expr) < dry_elev, _D), ], else_=func.max(flag_expr) ) return (flag_expr, func.avg(value_expr))
def expired(cls): return case( [ (cls.not_after <= arrow.utcnow(), True) ], else_=False )
def deprecated(cls): return case( [ (cls.name in BAD_CIPHERS, True) ], else_=False )
def expired(cls): return case( [ (cls.now_after <= datetime.datetime.now(), True) ], else_=False )
def pivot(query, column_expressions, column_labels): query = query.group_by(query.statement) columns = [] for i in range(0, len(column_expressions)): column_expressions[i] = func.count(case([(column_expressions[i], 1)])) columns.append(column_expressions[i].label(column_labels[i])) return query.add_columns(*columns)
def revoked(cls): return case( [ (cls.status == 'revoked', True) ], else_=False )
def _story_build_summary_query(): # first create a subquery for task statuses select_items = [] select_items.append(Story) select_items.append( expr.case( [(func.sum(Task.status.in_( ['todo', 'inprogress', 'review'])) > 0, 'active'), ((func.sum(Task.status == 'merged')) > 0, 'merged')], else_='invalid' ).label('status') ) for task_status in Task.TASK_STATUSES: select_items.append(expr.cast( func.sum(Task.status == task_status), Integer ).label(task_status)) select_items.append(expr.null().label('task_statuses')) result = select(select_items, None, expr.Join(Story, Task, onclause=Story.id == Task.story_id, isouter=True)) \ .group_by(Story.id) \ .alias('story_summary') return result
def _generate_stmt(self, id) : font_name = self.font_name font_size = self.font_size accidental = self.accidental glyph_box = self.glyph_box accidental_width = self.accidental_width accidental_to_accidental_widths = select([ accidental.c.id.label('id'), (glyph_box.c.width * font_size.c.val / 20.0).label('val') ]).select_from(accidental.join(font_name, onclause = accidental.c.id == font_name.c.id).\ join(font_size, onclause = accidental.c.id == font_size.c.id).\ join(glyph_box, onclause = font_name.c.val == glyph_box.c.name)).where( and_(glyph_box.c.unicode == case([(accidental.c.val == -1, "U+E260"), (accidental.c.val == 0, "U+E261"), (accidental.c.val == 1, "U+E262")], ))).\ where(safe_eq_comp(accidental.c.id, id)).\ cte(name='accidental_to_accidental_widths') self.register_stmt(accidental_to_accidental_widths) #uggghhhh.... #real_accidental_to_accidental_widths = realize(accidental_to_accidental_widths, accidental_width, 'val') #self.register_stmt(real_accidental_to_accidental_widths) #self.insert = simple_insert(accidental_width, real_accidental_to_accidental_widths) self.insert = simple_insert(accidental_width, accidental_to_accidental_widths)
def _generate_stmt(self, id) : name = self.name font_name = self.font_name font_size = self.font_size time_signature = self.time_signature glyph_box = self.glyph_box time_signature_inter_number_padding = self.time_signature_inter_number_padding height = self.height glyph_box_a_1 = glyph_box.alias(name='glyph_box_a_1') glyph_box_a_2 = glyph_box.alias(name='glyph_box_a_2') time_signature_inter_number_padding_default = time_signature_inter_number_padding.alias(name="time_signature_inter_number_padding_default") time_signatures_to_heights = select([ name.c.id.label('id'), (glyph_box_a_1.c.height * font_size.c.val / 20.0) + case([(time_signature_inter_number_padding.c.val != None, time_signature_inter_number_padding.c.val)], else_ = time_signature_inter_number_padding_default.c.val) + ((glyph_box_a_2.c.height * font_size.c.val / 20.0)).label('val') ]).select_from(name.outerjoin(time_signature_inter_number_padding, onclause = name.c.id == time_signature_inter_number_padding.c.id)).\ where(and_(name.c.val == 'time_signature', name.c.id == font_name.c.id, name.c.id == font_size.c.id, name.c.id == time_signature.c.id, time_signature_inter_number_padding_default.c.id == -1, font_name.c.val == glyph_box_a_1.c.name, font_name.c.val == glyph_box_a_2.c.name, conversion_tools.int_to_unicode(time_signature.c.num) == glyph_box_a_1.c.unicode, conversion_tools.int_to_unicode(time_signature.c.den) == glyph_box_a_2.c.unicode)).\ where(safe_eq_comp(name.c.id, id)).\ cte(name='time_signatures_to_heights') self.register_stmt(time_signatures_to_heights) self.insert = simple_insert(height, time_signatures_to_heights)
def _generate_stmt(self, id) : font_name = self.font_name font_size = self.font_size duration_log = self.duration_log glyph_box = self.glyph_box name = self.name rhythmic_event_dimension = self.rhythmic_event_dimension dimension = self.dimension duration_log_to_rhythmic_event_dimensions = select([ duration_log.c.id.label('id'), (glyph_box.c[dimension] * font_size.c.val / 20.0).label('val') ]).select_from(duration_log.join(font_name, onclause = duration_log.c.id == font_name.c.id).\ join(name, onclause = duration_log.c.id == name.c.id).\ join(font_size, onclause = duration_log.c.id == font_size.c.id).\ join(glyph_box, onclause = font_name.c.val == glyph_box.c.name)).\ where(safe_eq_comp(duration_log.c.id, id)).\ where( and_(glyph_box.c.unicode == case([(and_(duration_log.c.val == -1, name.c.val == 'note'), "U+E0A3"), (and_(duration_log.c.val == 0, name.c.val == 'note'), "U+E0A2"), (and_(duration_log.c.val == 0, name.c.val == 'rest'), "U+E4E3"), (and_(duration_log.c.val == -1, name.c.val == 'rest'), "U+E4E4"), (and_(duration_log.c.val == -2, name.c.val == 'rest'), "U+E4E5"), (and_(duration_log.c.val == -3, name.c.val == 'rest'), "U+E4E6"), (and_(duration_log.c.val == -4, name.c.val == 'rest'), "U+E4E7"), (and_(duration_log.c.val == -5, name.c.val == 'rest'), "U+E4E8"), (and_(duration_log.c.val == -6, name.c.val == 'rest'), "U+E4E9"), (and_(duration_log.c.val == -7, name.c.val == 'rest'), "U+E4EA"), (name.c.val == 'note', "U+E0A4")], else_ = 0))).\ cte(name='duration_log_to_rhythmic_event_dimensions') self.register_stmt(duration_log_to_rhythmic_event_dimensions) self.insert = simple_insert(rhythmic_event_dimension, duration_log_to_rhythmic_event_dimensions)
def _generate_stmt(self, id) : dynamic = self.dynamic unicode = self.unicode dynamic_to_unicode = select([ dynamic.c.id.label('id'), case([(dynamic.c.val == 'pppppp', "U+E527"), (dynamic.c.val == 'ppppp', "U+E528"), (dynamic.c.val == 'pppp', "U+E529"), (dynamic.c.val == 'ppp', "U+E52A"), (dynamic.c.val == 'pp', "U+E52B"), (dynamic.c.val == 'p', "U+E520"), (dynamic.c.val == 'mp', "U+E52C"), (dynamic.c.val == 'mf', "U+E52D"), (dynamic.c.val == 'p', "U+E522"), (dynamic.c.val == 'pf', "U+E52E"), (dynamic.c.val == 'f', "U+E522"), (dynamic.c.val == 'ff', "U+E52F"), (dynamic.c.val == 'fff', "U+E530"), (dynamic.c.val == 'ffff', "U+E531"), (dynamic.c.val == 'fffff', "U+E532"), (dynamic.c.val == 'ffffff', "U+E533"), (dynamic.c.val == 'fp', "U+E534"), (dynamic.c.val == 'fz', "U+E535"), (dynamic.c.val == 'sf', "U+E536"), (dynamic.c.val == 'sfp', "U+E537"), (dynamic.c.val == 'sfpp', "U+E538"), (dynamic.c.val == 'sfz', "U+E539"), ]) ]).where(safe_eq_comp(dynamic.c.id, id)).\ cte(name='dynamic_to_unicode') self.register_stmt(dynamic_to_unicode) self.insert = simple_insert(unicode, dynamic_to_unicode)
async def create_conversion_batch(entity_name, entity_id, format, user_id): entity_name = entity_name.upper() if entity_name == 'AUTHOR': author = model.Author.__table__ q = select([case([(author.c.first_name == None, author.c.last_name)], else_ = author.c.first_name + ' ' + author.c.last_name)])\ .where(author.c.id == entity_id) elif entity_name == 'SERIES': series = model.Series.__table__ q = select([series.c.title]).where(series.c.id == entity_id) elif entity_name == 'BOOKSHELF': shelf = model.Bookshelf.__table__ q = select([shelf.c.name]).where(shelf.c.id == entity_id) else: raise ValueError('Invalid entity name') format_id = await get_format_id(format) async with engine.acquire() as conn: batch = model.ConversionBatch.__table__ res = await conn.execute(q) name = await res.scalar() name = "Books for %s %s" % (entity_name.lower(), name) res = await conn.execute(batch.insert()\ .values(name=name, for_entity=entity_name, entity_id=entity_id, format_id=format_id, created_by_id = user_id, modified_by_id = user_id, version_id =1 )\ .returning(batch.c.id)) return await res.scalar()
def _add_ordering(sql_query, table, column_type, column_name, order): # Special case for this column, which sorts contigs correctly: if column_name == 'contig': get_contig_num = cast( text("SUBSTRING({} FROM '\d+')".format(table.c.contig)), type_=Integer) starts_with_chr = (text("SUBSTRING({} FROM '^chr(\d+)')" .format(table.c.contig)) != literal('')) starts_with_number = (text("SUBSTRING({} FROM '^\d+')" .format(table.c.contig)) != literal('')) # 10000 used here to mean "should be at the end of all the numbers", # assuming we never hit a chromosome number >= 10000. contig_num_col = case( [(starts_with_chr, get_contig_num), (starts_with_number, get_contig_num)], else_=literal(10000) ) contig_len_col = func.length(table.c.contig) contig_col = table.c.contig if order == 'desc': contig_len_col = desc(contig_len_col) contig_col = desc(contig_col) return sql_query.order_by(contig_num_col, contig_len_col, contig_col) sqla_type = vcf_type_to_sqla_type(column_type) column = cast(table.c[column_name], type_=sqla_type) column = {'asc': asc(column), 'desc': desc(column)}.get(order) return sql_query.order_by(column)
def get_proxy_address( self, user_id, ip_address=None, best=4, conn_factor=0.2 ): """Get a usable proxy address for audio resource of user by user_id. If there is no available server, None will be returned We sort the connection by user_rate - (have_conn*conn_factor) then res_rate - (have_conn*conn_factor) Which means, less user proxy will be selected first, also, if there is already a proxy connection there, they will have higher priority (introduced by the conn_factor). """ from sqlalchemy.sql.expression import or_, and_, cast, case from sqlalchemy.types import Float Port = tables.Port Proxy = tables.Proxy ProxyConnection = tables.ProxyConnection # calculate the connection factor factor_case = case([ (ProxyConnection.server_id, conn_factor) ], else_=0) # Cast the type to make sure the result will be float res_rate = (Proxy.resource_count / cast(Proxy.resource_limit, Float)) res_rate -= factor_case user_rate = (Proxy.user_count / cast(Proxy.user_limit, Float)) user_rate -= factor_case query = self.session \ .query(Port) \ .join((Proxy, Proxy.id == Port.server_id)) \ .outerjoin((ProxyConnection, and_(ProxyConnection.server_id == Proxy.id, ProxyConnection.user_id == user_id))) \ .order_by(user_rate) \ .order_by(res_rate) \ .filter(or_(Proxy.user_count < Proxy.user_limit, Proxy.user_limit == 0)) \ .filter(Proxy.alive) \ .filter(Proxy.active) \ .filter(Port.name == 'web') # find a random proxy ports = query.limit(best).all() if not ports: return None port = random.choice(ports) return port.address
def find_user_authorization_by_org_id_and_corp_type(cls, org_id: int, corp_type: str): """Return authorization view object.""" return db.session.query(Authorization).filter( and_(Authorization.org_id == org_id, or_(Authorization.corp_type_code == corp_type, Authorization.corp_type_code.is_(None)))).order_by( expression.case(((Authorization.org_membership == ADMIN, 1), (Authorization.org_membership == COORDINATOR, 2), (Authorization.org_membership == USER, 3)))).first()
def hourly_data_expr(flag_expr, value_expr, dry_elev): if dry_elev is None: dry_elev = -1000 flag_expr = expression.case([(flag_expr == _M, _M), (value_expr == None, _M), (value_expr < dry_elev, _D), (flag_expr == None, _O)], else_=_E) return (flag_expr, value_expr)
def column_expression(self, col): if geometry_support == 'sde-char': else_expression = ToChar(STAsText(col)) else: else_expression = STAsText(col) case_expression = expression.case([(STIsEmpty(col) == 1, None), (STIsEmpty(col) == 0, else_expression)]) return case_expression
def upgrade(): # ### commands auto generated by Alembic - please adjust! ### op.add_column('accounts', sa.Column('reserve_type', sa.SmallInteger(), nullable=True, default=0)) op.create_index('ix_accounts_acquisition', 'accounts', ['reserve_type', 'instance', 'hibernated', 'created'], unique=False) query = Account.__table__.update().\ values({'reserve_type':expression.case([ (Account.level < 30,RESERVE_TYPE_SLAVE), ], else_=RESERVE_TYPE_CAPTAIN)}) op.execute(query)
def find_user_authorization_by_business_number_and_corp_type(cls, business_identifier: str, corp_type: str): """Return authorization view object using corp type and business identifier. Mainly used for service accounts.Sorted using the membership since service accounts gets all access """ return cls.query.filter_by(corp_type_code=corp_type, business_identifier=business_identifier) \ .order_by(expression.case(((Authorization.org_membership == OWNER, 1), (Authorization.org_membership == ADMIN, 2), (Authorization.org_membership == MEMBER, 3))))\ .first()
def find_user_authorization_by_business_number_and_product(cls, business_identifier: str, product_code: str): """Return authorization view object using corp type and business identifier. Mainly used for service accounts.Sorted using the membership since service accounts gets all access """ return cls.query.filter_by(product_code=product_code, business_identifier=business_identifier) \ .order_by(expression.case(((Authorization.org_membership == ADMIN, 1), (Authorization.org_membership == COORDINATOR, 2), (Authorization.org_membership == USER, 3)))) \ .first()
def normalize_amount_damage(cls) -> sase.Update: """ Apply feature normalization on the amount_damage in the table. :return: A sql statement to update the amount_damage_norm column with normalized amount_damage per country/car """ # First compute the minimum and maximum amount damage per country min_max = sase.select([ cls.country, sase.case([(sase.func.max(sase.cast(cls.amount_damage, Numeric(14, 2))) == 0, 1)], else_=sase.func.max(sase.cast(cls.amount_damage, Numeric(14, 2)))).label("max_dmg"), sase.func.min(sase.cast(cls.amount_damage, Numeric(14, 2))).label("min_dmg") ]).group_by(cls.country).alias("min_max") # Second, use the min and max damage to normalize the damage per car per country and store in separate column norm = sase.update(cls). \ where(min_max.c.country == cls.country). \ values(amount_damage_norm=( (sase.cast(cls.amount_damage, Numeric(14, 2)) - min_max.c.min_dmg) / (sase.case([(min_max.c.max_dmg == 0, 1)], else_=min_max.c.max_dmg) - min_max.c.min_dmg) )) return norm
def upgrade(): # re-size existing data if necessary identifier_map = table('cisco_csr_identifier_map', column('ipsec_site_conn_id', sa.String(36))) ipsec_site_conn_id = identifier_map.columns['ipsec_site_conn_id'] op.execute(identifier_map.update(values={ ipsec_site_conn_id: expr.case([(func.length(ipsec_site_conn_id) > 36, func.substr(ipsec_site_conn_id, 1, 36))], else_=ipsec_site_conn_id)})) # Need to drop foreign key constraint before mysql will allow changes with migration.remove_fks_from_table('cisco_csr_identifier_map'): op.alter_column(table_name='cisco_csr_identifier_map', column_name='ipsec_site_conn_id', type_=sa.String(36), existing_nullable=False)
def upgrade(): # re-size existing data if necessary identifier_map = table('cisco_csr_identifier_map', column('ipsec_site_conn_id', sa.String(36))) ipsec_site_conn_id = identifier_map.columns['ipsec_site_conn_id'] op.execute( identifier_map.update( values={ ipsec_site_conn_id: expr.case([(func.length(ipsec_site_conn_id) > 36, func.substr(ipsec_site_conn_id, 1, 36))], else_=ipsec_site_conn_id) })) op.alter_column(table_name='cisco_csr_identifier_map', column_name='ipsec_site_conn_id', type_=sa.String(36), existing_nullable=True)
def getAnswerSummary(lectureId, student): """Fetch answerSummary row for student""" try: dbAnsSummary = (Session.query( db.AnswerSummary).with_lockmode('update').filter( db.AnswerSummary.lectureId == lectureId).filter( db.AnswerSummary.studentId == student.studentId).one()) except NoResultFound: dbAnsSummary = db.AnswerSummary( lectureId=lectureId, studentId=student.studentId, grade=0, ) Session.add(dbAnsSummary) # Update based on answer table ( dbAnsSummary.lecAnswered, dbAnsSummary.lecCorrect, dbAnsSummary.practiceAnswered, dbAnsSummary.practiceCorrect, maxTimeEnd, ) = Session.query( func.count(), func.ifnull(func.sum(db.Answer.correct), 0), func.ifnull(func.sum(db.Answer.practice), 0), func.ifnull( func.sum( expression.case([(db.Answer.practice & db.Answer.correct, 1)], else_=0)), 0), func.max(db.Answer.timeEnd), ).filter(db.Answer.lectureId == lectureId).filter( db.Answer.studentId == student.studentId).one() dbAnsSummary.lecAnswered = int(dbAnsSummary.lecAnswered) dbAnsSummary.lecCorrect = int(dbAnsSummary.lecCorrect) dbAnsSummary.practiceAnswered = int(dbAnsSummary.practiceAnswered) dbAnsSummary.practiceCorrect = int(dbAnsSummary.practiceCorrect) if not maxTimeEnd: maxTimeEnd = datetime.datetime.utcfromtimestamp(0) return (dbAnsSummary, maxTimeEnd)
def number_of_solved_instances_ranking(db, experiment, instances, solver_configs, cost='resultTime', fixed_limit=None): """ Ranking by the number of instances correctly solved. This is determined by an resultCode that starts with '1' and a 'finished' status of a job. """ instance_ids = [i.idInstance for i in instances] solver_config_ids = [i.idSolverConfig for i in solver_configs] if not solver_config_ids: return [] table = db.metadata.tables['ExperimentResults'] table_has_prop = db.metadata.tables['ExperimentResult_has_Property'] table_has_prop_value = db.metadata.tables[ 'ExperimentResult_has_PropertyValue'] c_solver_config_id = table.c['SolverConfig_idSolverConfig'] c_result_time = table.c['resultTime'] c_experiment_id = table.c['Experiment_idExperiment'] c_result_code = table.c['resultCode'] c_status = table.c['status'] c_instance_id = table.c['Instances_idInstance'] c_solver_config_id = table.c['SolverConfig_idSolverConfig'] if cost == 'resultTime': cost_column = table.c['resultTime'] cost_limit_column = table.c['CPUTimeLimit'] if fixed_limit: cost_column = expression.case( [(table.c['resultTime'] > fixed_limit, fixed_limit)], else_=table.c['resultTime']) cost_limit_column = literal(fixed_limit) c_result_code = expression.case( [(table.c['resultTime'] > fixed_limit, literal(-21))], else_=table.c['resultCode']) c_status = expression.case( [(table.c['resultTime'] > fixed_limit, literal(21))], else_=table.c['status']) elif cost == 'wallTime': cost_column = table.c['wallTime'] cost_limit_column = table.c['wallClockTimeLimit'] if fixed_limit: cost_column = expression.case( [(table.c['wallTime'] > fixed_limit, fixed_limit)], else_=table.c['wallTime']) cost_limit_column = literal(fixed_limit) c_result_code = expression.case( [(table.c['wallTime'] > fixed_limit, literal(-22))], else_=table.c['resultCode']) c_status = expression.case( [(table.c['wallTime'] > fixed_limit, literal(22))], else_=table.c['status']) elif cost == 'cost': cost_column = table.c['cost'] inf = float('inf') cost_limit_column = table.c['CPUTimeLimit'] else: cost_column = table_has_prop_value.c['value'] inf = float('inf') cost_limit_column = table.c['CPUTimeLimit'] results = {} if cost in ('resultTime', 'wallTime', 'cost'): s = select([c_solver_config_id, functions.sum(cost_column), functions.count()], and_(c_experiment_id == experiment.idExperiment, c_result_code.like(u'1%'), c_status == 1, c_instance_id.in_(instance_ids), c_solver_config_id.in_(solver_config_ids))) \ .select_from(table) \ .group_by(c_solver_config_id) query_results = db.session.connection().execute(s) for row in query_results: results[row[0]] = (row[1], row[2]) else: table = table.join( table_has_prop, and_(table_has_prop.c['idProperty'] == int(cost), table_has_prop.c['idExperimentResults'] == table.c['idJob'])).join(table_has_prop_value) s = select([c_solver_config_id, cost_column], and_(c_experiment_id == experiment.idExperiment, c_result_code.like(u'1%'), c_status == 1, c_instance_id.in_(instance_ids), c_solver_config_id.in_(solver_config_ids))) \ .select_from(table) sum_by_sc_id = dict((i, 0) for i in solver_config_ids) count_by_sc_id = dict((i, 0) for i in solver_config_ids) query_results = db.session.connection().execute(s) for row in query_results: sum_by_sc_id[row[0]] += float(row[1]) count_by_sc_id[row[0]] += 1 for i in solver_config_ids: results[i] = (sum_by_sc_id[i], count_by_sc_id[i]) def sgn(x): if x > 0: return 1 elif x < 0: return -1 else: return 0 def comp(s1, s2): num_solved_s1, num_solved_s2 = 0, 0 if results.has_key(s1.idSolverConfig): num_solved_s1 = results[s1.idSolverConfig][1] if results.has_key(s2.idSolverConfig): num_solved_s2 = results[s2.idSolverConfig][1] if num_solved_s1 > num_solved_s2: return 1 elif num_solved_s1 < num_solved_s2: return -1 else: # break ties by cumulative cost over all solved instances if results.has_key(s1.idSolverConfig) and results.has_key( s2.idSolverConfig): return sgn((results[s2.idSolverConfig][0] or 0.0) - (results[s1.idSolverConfig][0] or 0.0)) else: return 0 return list(sorted(solver_configs, cmp=comp, reverse=True))
def get_ranking_data(db, experiment, ranked_solvers, instances, calculate_par10, calculate_avg_stddev, cost, par_factor=1, fixed_limit=None): instance_ids = [i.idInstance for i in instances] solver_config_ids = [s.idSolverConfig for s in ranked_solvers] if not solver_config_ids: return [], None max_num_runs = experiment.get_max_num_runs(db) max_num_runs_per_solver = max_num_runs * len(instance_ids) table = db.metadata.tables['ExperimentResults'] from_table = table table_has_prop = db.metadata.tables['ExperimentResult_has_Property'] table_has_prop_value = db.metadata.tables[ 'ExperimentResult_has_PropertyValue'] status_column = table.c['status'] result_code_column = table.c['resultCode'] if cost == 'resultTime': cost_column = table.c['resultTime'] cost_property = db.ExperimentResult.resultTime cost_limit_column = table.c['CPUTimeLimit'] if fixed_limit: cost_column = expression.case( [(table.c['resultTime'] > fixed_limit, fixed_limit)], else_=table.c['resultTime']) cost_limit_column = literal(fixed_limit) status_column = expression.case( [(table.c['resultTime'] > fixed_limit, literal(21))], else_=table.c['status']) result_code_column = expression.case( [(table.c['resultTime'] > fixed_limit, literal(-21))], else_=table.c['resultCode']) elif cost == 'wallTime': cost_column = table.c['wallTime'] cost_property = db.ExperimentResult.wallTime cost_limit_column = table.c['wallClockTimeLimit'] if fixed_limit: cost_column = expression.case( [(table.c['wallTime'] > fixed_limit, fixed_limit)], else_=table.c['wallTime']) cost_limit_column = literal(fixed_limit) status_column = expression.case( [(table.c['wallTime'] > fixed_limit, literal(22))], else_=table.c['status']) result_code_column = expression.case( [(table.c['wallTime'] > fixed_limit, literal(-22))], else_=table.c['resultCode']) elif cost == 'cost': cost_column = table.c['cost'] cost_property = db.ExperimentResult.cost inf = float('inf') cost_limit_column = table.c['CPUTimeLimit'] # doesnt matter else: cost_column = table_has_prop_value.c['value'] cost_property = db.ResultPropertyValue.value inf = float('inf') cost_limit_column = table.c['CPUTimeLimit'] from_table = table.join( table_has_prop, and_(table_has_prop.c['idProperty'] == int(cost), table_has_prop.c['idExperimentResults'] == table.c['idJob'])).join(table_has_prop_value) vbs_num_solved = 0 vbs_cumulated_cpu = 0 from sqlalchemy import func, or_, not_ property_limit = 0 if cost in ('resultTime', 'wallTime', 'cost'): best_instance_runtimes = db.session.query(func.min(cost_property), db.ExperimentResult.Instances_idInstance) \ .filter(db.ExperimentResult.Experiment_idExperiment == experiment.idExperiment) \ .filter(result_code_column.like(u'1%')) \ .filter(db.ExperimentResult.Instances_idInstance.in_(instance_ids)) \ .filter(db.ExperimentResult.SolverConfig_idSolverConfig.in_(solver_config_ids)) \ .group_by(db.ExperimentResult.Instances_idInstance).all() else: s = select( [cost_property, table.c['Instances_idInstance']], and_(table.c['Experiment_idExperiment'] == experiment.idExperiment, table.c['resultCode'].like(u'1%'), table.c['Instances_idInstance'].in_(instance_ids), table.c['SolverConfig_idSolverConfig'].in_( solver_config_ids))).select_from(from_table) min_by_instance = dict((i, float("inf")) for i in instance_ids) for row in db.session.connection().execute(s): property_limit = max(property_limit, float(row[0])) min_by_instance[row[1]] = min(min_by_instance[row[1]], float(row[0])) best_instance_runtimes = [] for i in instance_ids: best_instance_runtimes.append((min_by_instance[i], i)) vbs_num_solved = len(best_instance_runtimes) * max_num_runs vbs_cumulated_cpu = sum(r[0] for r in best_instance_runtimes if r[0] is not None) * max_num_runs vbs_median = numpy.median( [r[0] for r in best_instance_runtimes if r[0] is not None]) vbs_average = numpy.average( [r[0] for r in best_instance_runtimes if r[0] is not None]) best_runtime_by_instance = dict() for bir in best_instance_runtimes: best_runtime_by_instance[bir[1]] = float( bir[0]) if bir[0] is not None else None #num_unsolved_instances = len(instances) - len(best_instance_runtimes) vbs_parX = 0.0 # Virtual best solver data data = [( 'Virtual Best Solver (VBS)', # name of the solver vbs_num_solved, # number of successful runs 0.0 if max_num_runs_per_solver == 0 else vbs_num_solved / float(max_num_runs_per_solver), # % of all runs 1.0, # % of vbs runs vbs_cumulated_cpu, # cumulated CPU time (0.0 if vbs_num_solved == 0 else vbs_average), (0.0 if vbs_num_solved == 0 else vbs_median), 0.0, # avg stddev 0.0, 0.0, vbs_parX)] # single query fetch of all/most required data s = select( [ expression.label( 'cost', cost_column), table.c['SolverConfig_idSolverConfig'], table.c['Instances_idInstance'] ], and_(result_code_column.like(u'1%'), table.c['Instances_idInstance'].in_(instance_ids), table.c['SolverConfig_idSolverConfig'].in_(solver_config_ids), table.c['Experiment_idExperiment'] == experiment.idExperiment, status_column == 1)).select_from(from_table) successful_runs = db.session.connection().execute(s) vbs_uses_solver_count = dict((id, 0) for id in solver_config_ids) runs_by_solver_and_instance = {} for run in successful_runs: if not runs_by_solver_and_instance.has_key( run.SolverConfig_idSolverConfig): runs_by_solver_and_instance[run.SolverConfig_idSolverConfig] = {} if not runs_by_solver_and_instance[ run.SolverConfig_idSolverConfig].has_key( run.Instances_idInstance): runs_by_solver_and_instance[run.SolverConfig_idSolverConfig][ run.Instances_idInstance] = [] runs_by_solver_and_instance[run.SolverConfig_idSolverConfig][ run.Instances_idInstance].append(run) if (float(run.cost) if run.cost is not None else None) == best_runtime_by_instance[run.Instances_idInstance]: vbs_uses_solver_count[run.SolverConfig_idSolverConfig] += 1 if calculate_avg_stddev: finished_runs_by_solver_and_instance = {} s = select( [ expression.label('cost', cost_column), table.c['SolverConfig_idSolverConfig'], table.c['Instances_idInstance'] ], and_(table.c['Instances_idInstance'].in_(instance_ids), table.c['SolverConfig_idSolverConfig'].in_(solver_config_ids), table.c['Experiment_idExperiment'] == experiment.idExperiment, not_(status_column.in_((-1, 0))))).select_from(from_table) finished_runs = db.session.connection().execute(s) for run in finished_runs: if not finished_runs_by_solver_and_instance.has_key( run.SolverConfig_idSolverConfig): finished_runs_by_solver_and_instance[ run.SolverConfig_idSolverConfig] = {} if not finished_runs_by_solver_and_instance[ run.SolverConfig_idSolverConfig].has_key( run.Instances_idInstance): finished_runs_by_solver_and_instance[ run.SolverConfig_idSolverConfig][ run.Instances_idInstance] = [] finished_runs_by_solver_and_instance[ run.SolverConfig_idSolverConfig][ run.Instances_idInstance].append(run) failed_runs_by_solver = dict( (sc.idSolverConfig, list()) for sc in ranked_solvers) s = select( [ expression.label('cost', cost_column), expression.label('cost_limit', cost_limit_column), table.c['SolverConfig_idSolverConfig'] ], and_( table.c['Experiment_idExperiment'] == experiment.idExperiment, table.c['Instances_idInstance'].in_(instance_ids), table.c['SolverConfig_idSolverConfig'].in_(solver_config_ids), and_(or_(status_column != 1, not_(result_code_column.like(u'1%'))), not_(status_column.in_([-1, 0]))))).select_from(from_table) failed_runs = db.session.connection().execute(s) for run in failed_runs: failed_runs_by_solver[run.SolverConfig_idSolverConfig].append(run) for solver in ranked_solvers: if runs_by_solver_and_instance.has_key(solver.idSolverConfig): successful_runs = [run for ilist in runs_by_solver_and_instance[solver.idSolverConfig].values() \ for run in ilist] else: successful_runs = [] successful_runs_sum = sum(float(j.cost) for j in successful_runs) penalized_average_runtime = 0.0 if calculate_par10: if len(successful_runs) + len( failed_runs_by_solver[solver.idSolverConfig]) == 0: # this should mean there are no jobs of this solver yet penalized_average_runtime = 0.0 else: penalized_average_runtime = (sum([j.cost_limit * par_factor if cost in ('resultTime', 'wallTime') else experiment.costPenalty * par_factor if cost == 'cost' else property_limit * par_factor for j in failed_runs_by_solver[solver.idSolverConfig]]) + successful_runs_sum) \ / (len(successful_runs) + len(failed_runs_by_solver[solver.idSolverConfig])) par1_median_runtime = numpy.median([j.cost_limit if cost in ('resultTime', 'wallTime') else \ experiment.costPenalty if cost == 'cost' else property_limit for j in failed_runs_by_solver[solver.idSolverConfig]] + [float(j.cost) for j in successful_runs]) #average_runtime = numpy.average([float(j.cost) for j in successful_runs]) cumulated_par1 = sum([j.cost_limit if cost in ('resultTime', 'wallTime') else \ experiment.costPenalty if cost == 'cost' else \ property_limit for j in failed_runs_by_solver[solver.idSolverConfig]]) + successful_runs_sum if len(successful_runs) + len( failed_runs_by_solver[solver.idSolverConfig]) == 0: par1 = 0.0 else: par1 = cumulated_par1 / float( (len(successful_runs) + len(failed_runs_by_solver[solver.idSolverConfig]))) avg_stddev_runtime = 0.0 avg_cv = 0.0 avg_qcd = 0.0 if calculate_avg_stddev: count = 0 for instance in instance_ids: if solver.idSolverConfig in finished_runs_by_solver_and_instance and \ finished_runs_by_solver_and_instance[solver.idSolverConfig].has_key(instance): instance_runtimes = finished_runs_by_solver_and_instance[ solver.idSolverConfig][instance] runtimes = [j[0] or 0.0 for j in instance_runtimes] stddev = numpy.std(runtimes) avg_stddev_runtime += stddev avg_cv += stddev / numpy.average(runtimes) quantiles = mquantiles(runtimes, [0.25, 0.5, 0.75]) avg_qcd += (quantiles[2] - quantiles[0]) / quantiles[1] count += 1 if count > 0: avg_stddev_runtime /= float(count) avg_cv /= float(count) avg_qcd /= float(count) data.append(( solver, len(successful_runs), 0 if len(successful_runs) == 0 else len(successful_runs) / float(max_num_runs_per_solver), 0 if vbs_num_solved == 0 else len(successful_runs) / float(vbs_num_solved), cumulated_par1, par1, par1_median_runtime, avg_stddev_runtime, avg_cv, avg_qcd, penalized_average_runtime, )) #if calculate_par10: data.sort(key=lambda x: x[7]) return data, vbs_uses_solver_count
class Role(Base, PermissionsMixin): __tablename__ = "role" __table_args__ = ( sql_schema.UniqueConstraint("id", "community_id"), sql_schema.ForeignKeyConstraint( ["parent_id", "community_id"], ["role.id", "role.community_id"], ), ) id = Column(sql_types.Integer, primary_key=True) community_id = Column(sql_types.Integer, ForeignKey("community.id"), nullable=False) parent_id = Column(sql_types.Integer, nullable=False) name = Column(sql_types.String(80), nullable=False) @hybrid_property def permissions(self): return reduce(lambda a, b: a | b, [getattr(self, m.name) for m in Permissions]) @permissions.setter def permissions(self, value): for member in Permissions: setattr(self, member.name, member & value) @orm.validates(*[m.name for m in Permissions]) def validate_permissions(self, key, value): parent_val = getattr(self.parent, key) if self.parent.CAN_DELEGATE and value & parent_val == value: return value else: raise Exception( f"{self.name}.{key} cannot be changed to {value} because parent has {parent_val}" ) groups = orm.relationship( "Group", lazy=True, backref=orm.backref("role", lazy=True), ) children = orm.relationship( "Role", lazy=True, overlaps="abilities,community", backref=orm.backref("parent", lazy=True, remote_side=[id], overlaps="abilities,community"), ) __mapper_args__ = { "polymorphic_on": sql_expr.case( (parent_id == id, "root_role"), else_="role", ), "polymorphic_identity": "role", } def __repr__(self): return f"Role(" f"name={self.name}" f")" def permits(self, request): return self.permissions & request == request def print_permissions(self): out = "" for member in Permissions: if self.permits(member): out += f"{member.name}, " return out[:-2]
def view_index_lang(session, request, language): """ Does the main index page for a single language in DDTSS """ lang = session.query(Languages).get(language) if not lang or not lang.enabled_ddtss: raise Http404() user = get_user(request, session) if user.lastlanguage != lang: user.lastlanguage = lang user.lastseen = int(time.time()) if request.method == 'POST': form = FetchForm(request.POST) if not form.is_valid(): # Maybe return HTTP 400 - Bad request? return show_message_screen(request, 'Bad request %r' % form.errors, 'ddtss_index_lang', language) if not lang.translation_model.user_allowed(user, language, lang.translation_model.ACTION_TRANSLATE): return show_message_screen(request, 'User is not permitted to translate', 'ddtss_index_lang', language) pack = form.cleaned_data['package'].strip() force = form.cleaned_data['force'] if pack == '': description_id = lang.get_next_to_translate(session) elif re.match('^\d+$', pack): description_id = int(pack) else: packageversion = session.query(PackageVersion).filter(PackageVersion.package == pack). \ join(ActiveDescription, ActiveDescription.description_id == PackageVersion.description_id).first() if not packageversion: return show_message_screen(request, 'No Package %s found' % pack, 'ddtss_index_lang', language) description_id = packageversion.description_id description = session.query(Description).filter(Description.description_id == description_id).first() if not description: return show_message_screen(request, 'No description-id %s found' % str(description_id), 'ddtss_index_lang', language) if language not in description.translation or force: trans = session.query(PendingTranslation).filter_by(language=lang, description_id=description_id).with_lockmode('update').first() if not trans: message = Messages( message="", actionstring="fetched", to_user=None, language=language, for_description=description_id, from_user=user.username, in_reply_to=None, timestamp=int(time.time())) session.add(message) trans = PendingTranslation( description_id=description_id, language=lang, firstupdate=int(time.time()), lastupdate=int(time.time()), owner_username=user.username, owner_locktime=int(time.time()), iteration=0, state=0) trans.short, trans.long = PendingTranslation.make_suggestion(description, language) session.add(trans) session.commit() return show_message_screen(request, 'Fetched package %s (%s)' % (description.package, str(description_id)), 'ddtss_translate', language, str(description_id)) return show_message_screen(request, 'Package %s already translated (and not forced)' % (pack), 'ddtss_index_lang', language) session.commit() # TODO: Don't load actual descriptions translations = session.query(PendingTranslation, func.sum(expression.case([(PendingTranslationReview.username == user.username, 1)], else_=0)).label('reviewed'), func.count().label('reviews')) \ .outerjoin(PendingTranslationReview) \ .filter(PendingTranslation.language_ref == language) \ .group_by(PendingTranslation) \ .options(subqueryload(PendingTranslation.reviews)) \ .options(subqueryload(PendingTranslation.description)) \ .options(subqueryload('description.milestones')) \ .all() pending_translations = [] pending_review = [] reviewed = [] for trans, reviewed_by_me, reviews in translations: if trans.state == PendingTranslation.STATE_PENDING_REVIEW: if reviewed_by_me or trans.owner_username == user.username: reviewed.append(trans) else: pending_review.append(trans) elif trans.state == PendingTranslation.STATE_PENDING_TRANSLATION: pending_translations.append(trans) reviewed.sort(key=lambda t: t.lastupdate, reverse=True) pending_review.sort(key=lambda t: t.lastupdate, reverse=False) pending_translations.sort(key=lambda t: t.firstupdate, reverse=False) global_messages = Messages.global_messages(session) \ .order_by(Messages.timestamp.desc()) \ .limit(20) \ .all() team_messages = Messages.team_messages(session, language) \ .order_by(Messages.timestamp.desc()) \ .limit(20) \ .all() user_messages = Messages.user_messages(session, user.username) \ .order_by(Messages.timestamp.desc()) \ .limit(20) \ .all() milestones = [] for type, name, milestone in (('user_milestone', 'User', user.milestone), ('lang_milestone_high', 'Team high', lang.milestone_high), ('lang_milestone_medium', 'Team medium', lang.milestone_medium), ('lang_milestone_low', 'Team low', lang.milestone_low)): if not milestone: continue m = session.query(DescriptionMilestone).filter(DescriptionMilestone.milestone == milestone).first() if not m: continue info = m.info_language(lang) info['type'] = type info['typename'] = name milestones.append(info) involveddescriptions = [x for x, in Messages.involveddescriptions(session, user.username).all()] recently_translated = Messages.recently_translated(session, language).limit(10).all() response = render_to_response("ddtss/index_lang.html", dict( lang=lang, user=user, auth=user.get_authority(language), pending_translations=pending_translations, pending_review=pending_review, reviewed=reviewed, involveddescriptions=involveddescriptions, milestones=milestones, recently_translated=recently_translated, global_messages=global_messages, team_messages=team_messages, user_messages=user_messages), context_instance=RequestContext(request)) return save_user(response, user)
def get_results_by_instance(self, db, solver_configs, instance, penalize_incorrect, penalize_factor=1, result_property='resultTime'): results_by_instance = dict((sc.idSolverConfig, dict()) for sc in solver_configs) if not solver_configs: return results_by_instance solver_config_ids = [sc.idSolverConfig for sc in solver_configs] table = db.metadata.tables['ExperimentResults'] table_result_codes = db.metadata.tables['ResultCodes'] table_status_codes = db.metadata.tables['StatusCodes'] c = table.c c_rc = table_result_codes.c c_sc = table_status_codes.c if result_property == 'resultTime': c_cost = c['resultTime'] c_limit = c['CPUTimeLimit'] pass elif result_property == 'wallTime': c_cost = c['wallTime'] c_limit = c['wallClockTimeLimit'] pass elif result_property == 'cost': c_cost = c['cost'] c_limit = -1 pass else: # result property table c_cost = 0 c_limit = -1 pass join_expression = table.join(table_result_codes).join(table_status_codes) select_statement = select([ c['idJob'], c['status'], c['resultCode'], expression.label('result_code_description', c_rc['description']), expression.label('status_code_description', c_sc['description']), c['Instances_idInstance'], c['SolverConfig_idSolverConfig'], c['run'], expression.label('cost', expression.case([( c['status'] > 0, # only consider cost of "finished" jobs c_cost if not penalize_incorrect else \ expression.case( [( table.c['resultCode'].like( '1%'), c_cost)], else_=c_limit * penalize_factor ) )], else_=None) ), expression.label('limit', c_limit), ], and_( c['Experiment_idExperiment'] == self.idExperiment, c['Instances_idInstance'] == instance.idInstance, c['SolverConfig_idSolverConfig'].in_(solver_config_ids), ), from_obj=join_expression, ) results = db.session.connection().execute(select_statement) for row in results: results_by_instance[row.SolverConfig_idSolverConfig][row.run] = row return results_by_instance
def _get_orderby_clauses(order_by_list, session): """Sorts a set of runs based on their natural ordering and an overriding set of order_bys. Runs are naturally ordered first by start time descending, then by run id for tie-breaking. """ clauses = [] ordering_joins = [] clause_id = 0 observed_order_by_clauses = set() # contrary to filters, it is not easily feasible to separately handle sorting # on attributes and on joined tables as we must keep all clauses in the same order if order_by_list: for order_by_clause in order_by_list: clause_id += 1 (key_type, key, ascending ) = SearchUtils.parse_order_by_for_search_runs(order_by_clause) if SearchUtils.is_string_attribute( key_type, key, "=") or SearchUtils.is_numeric_attribute( key_type, key, "="): order_value = getattr(SqlRun, SqlRun.get_attribute_name(key)) else: if SearchUtils.is_metric(key_type, "="): # any valid comparator entity = SqlLatestMetric elif SearchUtils.is_tag(key_type, "="): entity = SqlTag elif SearchUtils.is_param(key_type, "="): entity = SqlParam else: raise MlflowException( "Invalid identifier type '%s'" % key_type, error_code=INVALID_PARAMETER_VALUE, ) # build a subquery first because we will join it in the main request so that the # metric we want to sort on is available when we apply the sorting clause subquery = session.query(entity).filter( entity.key == key).subquery() ordering_joins.append(subquery) order_value = subquery.c.value # sqlite does not support NULLS LAST expression, so we sort first by # presence of the field (and is_nan for metrics), then by actual value # As the subqueries are created independently and used later in the # same main query, the CASE WHEN columns need to have unique names to # avoid ambiguity if SearchUtils.is_metric(key_type, "="): clauses.append( sql.case( [ # Ideally the use of "IS" is preferred here but owing to sqlalchemy # translation in MSSQL we are forced to use "=" instead. # These 2 options are functionally identical / unchanged because # the column (is_nan) is not nullable. However it could become an issue # if this precondition changes in the future. (subquery.c.is_nan == sqlalchemy.true(), 1), (order_value.is_(None), 1), ], else_=0, ).label("clause_%s" % clause_id)) else: # other entities do not have an 'is_nan' field clauses.append( sql.case([(order_value.is_(None), 1)], else_=0).label("clause_%s" % clause_id)) if (key_type, key) in observed_order_by_clauses: raise MlflowException( "`order_by` contains duplicate fields: {}".format( order_by_list)) observed_order_by_clauses.add((key_type, key)) if ascending: clauses.append(order_value) else: clauses.append(order_value.desc()) if (SearchUtils._ATTRIBUTE_IDENTIFIER, SqlRun.start_time.key) not in observed_order_by_clauses: clauses.append(SqlRun.start_time.desc()) clauses.append(SqlRun.run_uuid) return clauses, ordering_joins
class Release(db.Model): __tablename__ = "releases" @declared_attr def __table_args__(cls): # noqa return ( Index("release_created_idx", cls.created.desc()), Index("release_project_created_idx", cls.project_id, cls.created.desc()), Index("release_version_idx", cls.version), UniqueConstraint("project_id", "version"), ) __repr__ = make_repr("project", "version") __parent__ = dotted_navigator("project") __name__ = dotted_navigator("version") project_id = Column( ForeignKey("projects.id", onupdate="CASCADE", ondelete="CASCADE"), nullable=False, ) version = Column(Text, nullable=False) canonical_version = Column(Text, nullable=False) is_prerelease = orm.column_property(func.pep440_is_prerelease(version)) author = Column(Text) author_email = Column(Text) maintainer = Column(Text) maintainer_email = Column(Text) home_page = Column(Text) license = Column(Text) summary = Column(Text) keywords = Column(Text) platform = Column(Text) download_url = Column(Text) _pypi_ordering = Column(Integer) requires_python = Column(Text) created = Column(DateTime(timezone=False), nullable=False, server_default=sql.func.now()) description_id = Column( ForeignKey("release_descriptions.id", onupdate="CASCADE", ondelete="CASCADE"), nullable=False, ) description = orm.relationship( "Description", backref=orm.backref( "release", cascade="all, delete-orphan", passive_deletes=True, passive_updates=True, single_parent=True, uselist=False, ), ) yanked = Column(Boolean, nullable=False, server_default=sql.false()) yanked_reason = Column(Text, nullable=False, server_default="") _classifiers = orm.relationship( Classifier, backref="project_releases", secondary=lambda: release_classifiers, order_by=expression.case( {c: i for i, c in enumerate(sorted_classifiers)}, value=Classifier.classifier, ), passive_deletes=True, ) classifiers = association_proxy("_classifiers", "classifier") files = orm.relationship( "File", backref="release", cascade="all, delete-orphan", lazy="dynamic", order_by=lambda: File.filename, passive_deletes=True, ) dependencies = orm.relationship( "Dependency", backref="release", cascade="all, delete-orphan", passive_deletes=True, ) vulnerabilities = orm.relationship( VulnerabilityRecord, back_populates="releases", secondary="release_vulnerabilities", passive_deletes=True, ) _requires = _dependency_relation(DependencyKind.requires) requires = association_proxy("_requires", "specifier") _provides = _dependency_relation(DependencyKind.provides) provides = association_proxy("_provides", "specifier") _obsoletes = _dependency_relation(DependencyKind.obsoletes) obsoletes = association_proxy("_obsoletes", "specifier") _requires_dist = _dependency_relation(DependencyKind.requires_dist) requires_dist = association_proxy("_requires_dist", "specifier") _provides_dist = _dependency_relation(DependencyKind.provides_dist) provides_dist = association_proxy("_provides_dist", "specifier") _obsoletes_dist = _dependency_relation(DependencyKind.obsoletes_dist) obsoletes_dist = association_proxy("_obsoletes_dist", "specifier") _requires_external = _dependency_relation(DependencyKind.requires_external) requires_external = association_proxy("_requires_external", "specifier") _project_urls = _dependency_relation(DependencyKind.project_url) project_urls = association_proxy("_project_urls", "specifier") uploader_id = Column( ForeignKey("users.id", onupdate="CASCADE", ondelete="SET NULL"), nullable=True, index=True, ) uploader = orm.relationship(User) uploaded_via = Column(Text) @property def urls(self): _urls = OrderedDict() if self.home_page: _urls["Homepage"] = self.home_page if self.download_url: _urls["Download"] = self.download_url for urlspec in self.project_urls: name, _, url = urlspec.partition(",") name = name.strip() url = url.strip() if name and url: _urls[name] = url return _urls @property def github_repo_info_url(self): for url in self.urls.values(): parsed = urlparse(url) segments = parsed.path.strip("/").split("/") if parsed.netloc in {"github.com", "www.github.com" } and len(segments) >= 2: user_name, repo_name = segments[:2] return f"https://api.github.com/repos/{user_name}/{repo_name}" @property def has_meta(self): return any([ self.license, self.keywords, self.author, self.author_email, self.maintainer, self.maintainer_email, self.requires_python, ])
def msg_count(self): return expression.case([(self.msg_id == 0, 0)], else_=self.msg_id - self.start_msg_id)
def unsynced_count(self): return expression.case([(self.msg_id == 0, 0), (self.sync_msg_id == 0, self.msg_id - self.start_msg_id)], else_=self.msg_id - self.sync_msg_id)
def unhandled_count(self): return expression.case([(self.msg_id == 0, 0), (self.handler_msg_id == 0, self.msg_id - self.start_msg_id)], else_=self.msg_id - self.handler_msg_id)
def _get_paid_not_approved_query(cls, query_type, start_date, end_date): """ Gets the query for paid but not approved shares between and including start and end date. Args: query_type: The type of the query to be build. 'data' for retrieving rows and 'shares_count' for an aggregate count query. start_date: The first date of which paid and not approved shares are considered. end_date: The last date of which paid and not approved shares are considered. Returns: A query according to the specified query_type. For 'data' the query is build to retrieve rows with attributes 'id' for member id, 'lastname' for the member's lastname, 'firstname' for the member's firstname, 'shares_count' for the number of shares and 'payment_received_date' for the date on which the payment was received For 'shares_count' an aggregate count query is returned to retrieve the number of shares of all relevant shares packages. """ # Shares which of the day of the request have not been approved are not # yet stored in Shares but only available on the C3sMember. shares_count = expression.case( # "== None" for SqlAlchemy instead of Python "is None" # pylint: disable=singleton-comparison [(Shares.id == None, C3sMember.num_shares)], else_=Shares.number ) payment_received_date = expression.case( [( # "== None" for SqlAlchemy instead of Python "is None" # pylint: disable=singleton-comparison Shares.id == None, # C3sMember.payment_received_date has the data type DateTime # but Date is required as it is used in # Shares.payment_received_date. As CAST on DateTime '2017-01-02 # 12:23:34.456789' returns '2017' in SQLite and therefore # cannot be used substring is used instead and then SQLAlchemy # is forced by type_coerce to parse it as a Date column. expression.type_coerce( func.substr(C3sMember.payment_received_date, 1, 10), Date) )], else_=Shares.payment_received_date ) # SqlAlchemy equality to None must be used as "== None" instead of # Python "is not None". date_of_acquisition = expression.case( # "== None" for SqlAlchemy instead of Python "is None" # pylint: disable=singleton-comparison [(Shares.id == None, C3sMember.membership_date)], else_=Shares.date_of_acquisition ) if query_type == 'data': # pylint: disable=no-member query = DBSession.query( C3sMember.id, C3sMember.lastname, C3sMember.firstname, shares_count.label('shares_count'), payment_received_date.label('payment_received_date'), ) if query_type == 'shares_count': # pylint: disable=no-member query = DBSession.query( func.sum(shares_count) ) # Use outer joins as Shares do not have to exist yet. return query.select_from(C3sMember) \ .outerjoin(members_shares) \ .outerjoin(Shares) \ .filter( expression.and_( # membership not approved in time period expression.or_( # membership or share approved later than end date date_of_acquisition > end_date, # or membership or share not approved yet (default # date) date_of_acquisition == date(1970, 1, 1), ), # payment received in time period payment_received_date >= start_date, payment_received_date <= end_date, ) )
def search(request): metrics = request.find_service(IMetricsService, context=None) querystring = request.params.get("q", "").replace("'", '"') order = request.params.get("o", "") classifiers = request.params.getall("c") query = get_es_query(request.es, querystring, order, classifiers) try: page_num = int(request.params.get("page", 1)) except ValueError: raise HTTPBadRequest("'page' must be an integer.") try: page = ElasticsearchPage(query, page=page_num, url_maker=paginate_url_factory(request)) except elasticsearch.TransportError: metrics.increment("warehouse.views.search.error") raise HTTPServiceUnavailable if page.page_count and page_num > page.page_count: raise HTTPNotFound available_filters = collections.defaultdict(list) classifiers_q = (request.db.query(Classifier).with_entities( Classifier.classifier).filter( exists([release_classifiers.c.trove_id ]).where(release_classifiers.c.trove_id == Classifier.id), Classifier.classifier.notin_(deprecated_classifiers.keys()), ).order_by( expression.case( {c: i for i, c in enumerate(sorted_classifiers)}, value=Classifier.classifier, ))) for cls in classifiers_q: first, *_ = cls.classifier.split(" :: ") available_filters[first].append(cls.classifier) def filter_key(item): try: return 0, SEARCH_FILTER_ORDER.index(item[0]), item[0] except ValueError: return 1, 0, item[0] def form_filters_tree(split_list): """ Takes a list of lists, each of them containing a filter and one of its children. Returns a dictionary, each key being a filter and each value being the filter's children. """ d = {} for list_ in split_list: current_level = d for part in list_: if part not in current_level: current_level[part] = {} current_level = current_level[part] return d def process_available_filters(): """ Processes available filters and returns a list of dictionaries. The value of a key in the dictionary represents its children """ sorted_filters = sorted(available_filters.items(), key=filter_key) output = [] for f in sorted_filters: classifier_list = f[1] split_list = [i.split(" :: ") for i in classifier_list] tree = form_filters_tree(split_list) output.append(tree) return output metrics = request.find_service(IMetricsService, context=None) metrics.histogram("warehouse.views.search.results", page.item_count) return { "page": page, "term": querystring, "order": order, "available_filters": process_available_filters(), "applied_filters": request.params.getall("c"), }
def community_search(json): PER_PAGE = 20 portals_ids = [] for p in g.user.active_portals_subscribed: portals_ids.append(p.id) companies_ids = [] for c in g.user.companies_employer_active: companies_ids.append(c.id) # use load_for_infinite_scroll # TODO: OZ by OZ: use load_for_infinite_scroll query = g.db.query(User). \ outerjoin(UserPortalReader, and_(UserPortalReader.user_id == User.id, UserPortalReader.status == 'active', UserPortalReader.portal_id.in_(portals_ids))). \ outerjoin(Portal, and_(UserPortalReader.portal_id == Portal.id, Portal.status == Portal.STATUSES['PORTAL_ACTIVE'])). \ outerjoin(UserCompany, and_(UserCompany.user_id == User.id, UserCompany.status == UserCompany.STATUSES['EMPLOYMENT_ACTIVE'], UserCompany.company_id.in_(companies_ids))). \ outerjoin(Company, and_(UserCompany.company_id == Company.id, Company.status == Company.STATUSES['COMPANY_ACTIVE'])). \ outerjoin(Contact, or_(and_(Contact.user1_id == User.id, Contact.user2_id == g.user.id), and_(Contact.user2_id == User.id, Contact.user1_id == g.user.id))). \ filter(and_(User.full_name.ilike("%" + json['text'] + "%"), User.id != g.user.id, or_(Portal.id != None, Company.id != None))). \ group_by(User.id, Contact.status). \ order_by(expression.case([ (or_( and_(Contact.status == Contact.STATUSES['REQUESTED_UNCONFIRMED'], User.id == g.user.id), and_(Contact.status == Contact.STATUSES['UNCONFIRMED_REQUESTED'], User.id != g.user.id)), '1'), (or_( and_(Contact.status == Contact.STATUSES['REQUESTED_UNCONFIRMED'], User.id != g.user.id), and_(Contact.status == Contact.STATUSES['UNCONFIRMED_REQUESTED'], User.id == g.user.id)), '2'), (or_( and_(Contact.status == Contact.STATUSES['ACTIVE_BANNED'], User.id == g.user.id), and_(Contact.status == Contact.STATUSES['BANNED_ACTIVE'], User.id != g.user.id)), '3'), (or_( and_(Contact.status == Contact.STATUSES['ACTIVE_BANNED'], User.id != g.user.id), and_(Contact.status == Contact.STATUSES['BANNED_ACTIVE'], User.id == g.user.id)), '4'), (or_( and_(Contact.status == Contact.STATUSES['ANY_REVOKED'], User.id == g.user.id), and_(Contact.status == Contact.STATUSES['REVOKED_ANY'], User.id != g.user.id)), 'X'), (or_( and_(Contact.status == Contact.STATUSES['ANY_REVOKED'], User.id != g.user.id), and_(Contact.status == Contact.STATUSES['REVOKED_ANY'], User.id == g.user.id)), 'X'), (Contact.status == Contact.STATUSES['ACTIVE_ACTIVE'], '5') ], else_='X'), User.full_name, User.id). \ limit(PER_PAGE + 1).offset((json['page'] - 1) * PER_PAGE) users = query.all() next_page = (json['page'] + 1) if len(users) > PER_PAGE else False users = users[0:PER_PAGE] ret = [] for u in users: user_dict = u.get_client_side_dict( fields='id,full_name,avatar.url,address_email') user_dict['common_portals_subscribed'] = [ p.get_client_side_dict(fields='id,logo.url,host,name') for p in u.active_portals_subscribed if p.id in portals_ids ] user_dict['common_companies_employers'] = [ c.get_client_side_dict(fields='id,logo.url,name') for c in u.companies_employer_active if c.id in companies_ids ] contact = g.db().query(Contact).filter_by(user1_id=min([u.id, g.user.id])).filter_by( user2_id=max([u.id, g.user.id])) \ .first() if contact: user_dict['contact_status'] = contact.get_status_for_user( g.user.id) else: user_dict['contact_status'] = False ret.append(user_dict) return {'community': ret, 'next_page': next_page}
def find_user_authorization_by_org_id_and_corp_type(cls, org_id: int, corp_type: str): """Return authorization view object.""" return cls.query.filter_by(corp_type_code=corp_type, org_id=org_id).order_by( expression.case(((Authorization.org_membership == OWNER, 1), (Authorization.org_membership == ADMIN, 2), (Authorization.org_membership == MEMBER, 3)))).first()
def get_result_matrix(self, db, solver_configs, instances, cost='resultTime', fixed_limit=None): """ Returns the results as matrix of lists of result tuples, i.e. Dict<idInstance, Dict<idSolverConfig, List of runs>> """ num_successful = dict( (i.idInstance, dict((sc.idSolverConfig, 0) for sc in solver_configs)) for i in instances) num_completed = dict( (i.idInstance, dict((sc.idSolverConfig, 0) for sc in solver_configs)) for i in instances) M = dict((i.idInstance, dict((sc.idSolverConfig, list()) for sc in solver_configs)) for i in instances) solver_config_ids = [sc.idSolverConfig for sc in solver_configs] instance_ids = [i.idInstance for i in instances] if not solver_config_ids or not instance_ids: return M, 0, 0 table = db.metadata.tables['ExperimentResults'] table_result_codes = db.metadata.tables['ResultCodes'] from_table = table table_has_prop = db.metadata.tables['ExperimentResult_has_Property'] table_has_prop_value = db.metadata.tables['ExperimentResult_has_PropertyValue'] status_column = table.c['status'] result_code_column = table.c['resultCode'] if cost == 'resultTime': cost_column = table.c['resultTime'] cost_property = db.ExperimentResult.resultTime cost_limit_column = table.c['CPUTimeLimit'] if fixed_limit: cost_column = expression.case([(table.c['resultTime'] > fixed_limit, fixed_limit)], else_=table.c['resultTime']) cost_limit_column = literal(fixed_limit) status_column = expression.case([(table.c['resultTime'] > fixed_limit, literal(21))], else_=table.c['status']) result_code_column = expression.case([(table.c['resultTime'] > fixed_limit, literal(-21))], else_=table.c['resultCode']) elif cost == 'wallTime': cost_column = table.c['wallTime'] cost_property = db.ExperimentResult.wallTime cost_limit_column = table.c['wallClockTimeLimit'] if fixed_limit: cost_column = expression.case([(table.c['wallTime'] > fixed_limit, fixed_limit)], else_=table.c['wallTime']) cost_limit_column = literal(fixed_limit) status_column = expression.case([(table.c['wallTime'] > fixed_limit, literal(22))], else_=table.c['status']) result_code_column = expression.case([(table.c['wallTime'] > fixed_limit, literal(-22))], else_=table.c['resultCode']) elif cost == 'cost': cost_column = table.c['cost'] cost_property = db.ExperimentResult.cost inf = float('inf') cost_limit_column = table.c['CPUTimeLimit'] # doesnt matter else: cost_column = table_has_prop_value.c['value'] cost_property = db.ResultPropertyValue.value inf = float('inf') cost_limit_column = table.c['CPUTimeLimit'] from_table = table.join(table_has_prop, and_(table_has_prop.c['idProperty'] == int(cost), table_has_prop.c['idExperimentResults'] == table.c[ 'idJob'])).join(table_has_prop_value) s = select([table.c['idJob'], expression.label('resultCode', result_code_column), expression.label('cost', cost_column), expression.label('status', status_column), table.c['SolverConfig_idSolverConfig'], table.c['Instances_idInstance'], table_result_codes.c['description'], expression.label('limit', cost_limit_column)], and_(table.c['Experiment_idExperiment'] == self.idExperiment, table.c['SolverConfig_idSolverConfig'].in_(solver_config_ids), table.c['Instances_idInstance'].in_(instance_ids)), from_obj=from_table.join(table_result_codes)) Run = namedtuple('Run', ['idJob', 'status', 'result_code_description', 'resultCode', 'resultTime', 'successful', 'penalized_time10', 'idSolverConfig', 'idInstance', 'penalized_time1', 'censored']) for r in db.session.connection().execute(s): if r.Instances_idInstance not in M: continue if r.SolverConfig_idSolverConfig not in M[r.Instances_idInstance]: continue if str(r.resultCode).startswith('1'): num_successful[r.Instances_idInstance][ r.SolverConfig_idSolverConfig] += 1 if r.status not in STATUS_PROCESSING: num_completed[r.Instances_idInstance][ r.SolverConfig_idSolverConfig] += 1 M[r.Instances_idInstance][r.SolverConfig_idSolverConfig].append( Run(r.idJob, int(r.status), r[6], int(r.resultCode), None if int(r.status) <= 0 else float(r.cost), str(r.resultCode).startswith('1'), float(r.cost) if str(r.resultCode).startswith('1') else (inf if cost not in ( 'resultTime', 'wallTime') else float(r.limit)) * 10, r.SolverConfig_idSolverConfig, r.Instances_idInstance, float(r.cost) if str(r.resultCode).startswith('1') else ( inf if cost not in ('resultTime', 'wallTime') else float(r.limit)), not str(r.resultCode).startswith('1'))) return M, num_successful, num_completed