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 _calc(): stats = {} if day_from is not None and day_to is not None: # fill in gaps for day in util.daterange(day_from,day_to): stats[day] = dict(total=0,done=0,help=0) # TODO: do the work in the database. q = session.query(cast(Article.pubdate,Date), Article) if day_from is not None: q = q.filter(cast(Article.pubdate, Date) >= day_from) if day_to is not None: q = q.filter(cast(Article.pubdate, Date) <= day_to) for day,art in q: if day not in stats: stats[day] = dict(total=0,done=0,help=0) stats[day]['total'] += 1 if not art.needs_sourcing: stats[day]['done'] += 1 stats = sorted([(day,row) for day,row in stats.iteritems()], key=lambda x: x[0], reverse=True ) return [DailyStats(x[0], x[1]['total'], x[1]['done']) for x in stats]
def get(self, country_id): """ Gather specified country from the database with its data country_id a non-zero, positive int return a json object representing the country """ session = db.loadSession() assert type(country_id) == int # Make the sql query result = session.query( # What to select # outerjoin defaults to a LEFT outer join, NOT full outer join db.Country.id, db.Country.name, func.array_agg_cust(array([cast(db.Olympics.id, String), cast(db.Olympics.year, String), db.Olympics.season, db.City.name])) )\ .select_from(db.Country)\ .outerjoin(db.City)\ .outerjoin(db.Olympics)\ .filter( # What to filter by (where clause) db.Country.id==country_id)\ .group_by(db.Country.id, db.Country.name)\ .first() # Actually executes the query and returns a tuple session.close() keys = ('id', 'name', ('olympics-hosted', ('id', 'year', 'season', 'city'))) country_dict = add_keys(keys, result) return jsonify(country_dict)
def selectables(cls, bag, agg_spec): """ Create a list of statements from spec :type bag: mongosql.bag.ModelPropertyBags :rtype: list[sqlalchemy.sql.elements.ColumnElement] """ # TODO: calculation expressions for selection: http://docs.mongodb.org/manual/meta/aggregation-quick-reference/ selectables = [] for comp_field, comp_expression in agg_spec.items(): # Column reference if isinstance(comp_expression, basestring): selectables.append(bag.columns[comp_expression].label(comp_field)) continue # Computed expression assert isinstance(comp_expression, dict), 'Aggregate: Expression should be either a column name, or an object' assert len(comp_expression) == 1, 'Aggregate: expression can only contain a single operator' operator, expression = comp_expression.popitem() # Expression statement if isinstance(expression, int) and operator == '$sum': # Special case for count expression_stmt = expression elif isinstance(expression, basestring): # Column name expression_stmt = bag.columns[expression] # Json column? if bag.columns.is_column_json(expression): # PostgreSQL always returns text values from it, and for aggregation we usually need numbers :) expression_stmt = cast(expression_stmt, Float) elif isinstance(expression, dict): # Boolean expression expression_stmt = MongoCriteria.statement(bag, expression) # Need to cast it to int expression_stmt = cast(expression_stmt, Integer) else: raise AssertionError('Aggregate: expression should be either a column name, or an object') # Operator if operator == '$max': comp_stmt = func.max(expression_stmt) elif operator == '$min': comp_stmt = func.min(expression_stmt) elif operator == '$avg': comp_stmt = func.avg(expression_stmt) elif operator == '$sum': if isinstance(expression_stmt, int): # Special case for count comp_stmt = func.count() if expression_stmt != 1: comp_stmt *= expression_stmt else: comp_stmt = func.sum(expression_stmt) else: raise AssertionError('Aggregate: unsupported operator "{}"'.format(operator)) # Append selectables.append(comp_stmt.label(comp_field)) return selectables
def get(self): """ Gathers all events from the database with their data return a json object representing the events """ session = db.loadSession() # Make the sql query result = session.query( # What to select # distinct because of multiple medals per event distinct(db.Event.id), db.Event.name, db.Sport.name, func.array_agg_cust(distinct(array([cast(db.Olympics.id, String), cast(db.Olympics.year, String), db.Olympics.season]))) )\ .select_from(db.Event)\ .join(db.Sport)\ .join(db.Medal)\ .join(db.Olympics)\ .group_by(db.Event.id, db.Event.name, db.Sport.name)\ .all() # Actually executes the query and returns a list of tuples session.close() keys = ('id', 'name', 'sport', ('olympics', ('id', 'year', 'season'))) all_events_dict = list_of_dict_to_dict_of_dict(add_keys(keys, row) for row in result) return jsonify(all_events_dict)
def filtering(self): search_value = self.request_values.get('sSearch') condition = None def search(idx, col): tmp_column_name = col.column_name.split('.') for tmp_name in tmp_column_name: if tmp_column_name.index(tmp_name) == 0: obj = getattr(self.sqla_object, tmp_name) parent = self.sqla_object elif isinstance(obj.property, RelationshipProperty): parent = obj.property.mapper.class_ obj = getattr(parent, tmp_name) if not hasattr(obj, 'property'): sqla_obj = parent column_name = tmp_name elif isinstance(obj.property, RelationshipProperty): sqla_obj = obj.mapper.class_ column_name = tmp_name if not column_name: column_name = obj.property.table.primary_key.columns \ .values()[0].name else: sqla_obj = parent column_name = tmp_name return sqla_obj, column_name if search_value: search_value_list = str(search_value).split() for search_val in search_value_list: conditions = [] for idx, col in enumerate(self.columns): if self.request_values.get('bSearchable_%s' % idx) in ( True, 'true') and col.searchable: sqla_obj, column_name = search(idx, col) conditions.append( cast(get_attr(sqla_obj, column_name), String).ilike('%%%s%%' % search_val)) condition = or_(*conditions) if condition is not None: self.query = self.query.filter(condition) conditions = [] for idx, col in enumerate(self.columns): search_value2 = self.request_values.get('sSearch_%s' % idx) if search_value2: sqla_obj, column_name = search(idx, col) if col.search_like: conditions.append( cast(get_attr(sqla_obj, column_name), String).ilike('%%%s%%' % search_value2)) else: conditions.append( cast(get_attr(sqla_obj, column_name), String).__eq__(search_value2)) if condition is not None: condition = and_(condition, and_(*conditions)) else: condition = and_(*conditions) if condition is not None: self.query = self.query.filter(condition) self.cardinality_filtered = self.query.count() else: self.cardinality_filtered = self.cardinality
def get(self): """ Gathers all countries from the database with their data return a json object representing the countries """ session = db.loadSession() # Make the sql query result = session.query( # What to select # outerjoin defaults to a LEFT outer join, NOT full outer join db.Country.id, db.Country.name, func.array_agg_cust(array([cast(db.Olympics.id, String), cast(db.Olympics.year, String), db.Olympics.season, db.City.name])) )\ .select_from(db.Country)\ .outerjoin(db.City)\ .outerjoin(db.Olympics)\ .group_by(db.Country.id, db.Country.name)\ .all() # Actually executes the query and returns a list of tuples session.close() keys = ('id', 'name', ('olympics-hosted', ('id', 'year', 'season', 'city'))) all_countries_dict = list_of_dict_to_dict_of_dict(add_keys(keys, row) for row in result) return jsonify(all_countries_dict)
def get_clustered_locations(location_column, threshold_radius=1000, filter=None): """ SELECT ST_Centroid( (ST_Dump( ST_Union( ST_Buffer( takeoff_location_wkt::geography, 1000 )::geometry ) ) ).geom) FROM flights WHERE pilot_id=31; """ # Cast the takeoff_location_wkt column to Geography geography = cast(location_column, Geography) # Add a metric buffer zone around the locations buffer = cast(geography.ST_Buffer(threshold_radius), Geometry) # Join the locations into one MultiPolygon union = buffer.ST_Union() # Split the MultiPolygon into separate polygons dump = union.ST_Dump().geom # Calculate center points of each polygon locations = func.ST_Centroid(dump) query = db.session.query(locations.label('location')) if filter is not None: query = query.filter(filter) return [Location.from_wkb(row.location) for row in query]
def lookup(self): q = request.params["q"] # Assume that the SQL library handles the SQL attack vectors person_query = meta.Session.query( Person.id, sa.func.concat(Person.fullname, " - ", Person.email_address).label("pretty") ).filter( sa.or_(Person.lastname.ilike(q + "%"), Person.fullname.ilike(q + "%"), Person.email_address.ilike(q + "%")) ) personid_query = meta.Session.query(Person.id, cast(Person.id, sa.String).label("pretty")).filter( cast(Person.id, sa.String).like(q + "%") ) boarding_query = meta.Session.query(FulfilmentGroup.person_id, FulfilmentGroup.code.label("pretty")).filter( FulfilmentGroup.code.ilike(q + "%") ) badge_query = meta.Session.query(Fulfilment.person_id, Fulfilment.code.label("pretty")).filter( Fulfilment.code.ilike(q + "%") ) union_query = person_query.union(personid_query, boarding_query, badge_query).order_by("pretty").limit(5) return dict(r=list(union_query.all()))
def get_info(cls, location): '''Returns a query object of mountain waves around the location''' return DBSession.query(cls) \ .filter(func.ST_DWithin( cast(WKTElement(location.to_wkt(), srid=4326), Geography), cast(cls.location, Geography), 5000))
def by_location(cls, location): '''Returns a query object of mountain waves around the location''' return cls.query() \ .filter(db.func.ST_DWithin( cast(location.make_point(), Geography), cast(cls.location, Geography), 5000))
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 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 _build_query(self, table, filter_values): having = [] filter_cols = [] external_cols = _get_grouping(filter_values) for fil in self.filters: if isinstance(fil, ANDFilter): filter_cols.append(fil.filters[0].column_name) having.append(fil) elif isinstance(fil, RawFilter): having.append(fil) elif fil.column_name not in ['group', 'gender', 'group_leadership', 'disaggregate_by', 'table_card_group_by']: if fil.column_name not in external_cols and fil.column_name != 'maxmin': filter_cols.append(fil.column_name) having.append(fil) group_having = '' having_group_by = [] if ('disaggregate_by' in filter_values and filter_values['disaggregate_by'] == 'group') or \ (filter_values.get('table_card_group_by') == 'group_leadership'): having_group_by.append('group_leadership') elif 'group_leadership' in filter_values and filter_values['group_leadership']: group_having = "(MAX(CAST(gender as int4)) + MIN(CAST(gender as int4))) " \ "= :group_leadership and group_leadership=\'Y\'" having_group_by.append('group_leadership') filter_cols.append('group_leadership') elif 'gender' in filter_values and filter_values['gender']: group_having = "(MAX(CAST(gender as int4)) + MIN(CAST(gender as int4))) = :gender" table_card_group = [] if 'group_name' in self.group_by: table_card_group.append('group_name') s1 = alias(select([table.c.doc_id, table.c.group_case_id, table.c.group_name, table.c.group_id, (sqlalchemy.func.max(table.c.prop_value) + sqlalchemy.func.min(table.c.prop_value)).label('maxmin')] + filter_cols + external_cols, from_obj=table, group_by=([table.c.doc_id, table.c.group_case_id, table.c.group_name, table.c.group_id] + filter_cols + external_cols)), name='x') s2 = alias( select( [table.c.group_case_id, sqlalchemy.cast( cast(func.max(table.c.gender), Integer) + cast(func.min(table.c.gender), Integer), VARCHAR ).label('gender')] + table_card_group, from_obj=table, group_by=[table.c.group_case_id] + table_card_group + having_group_by, having=group_having ), name='y' ) group_by = list(self.group_by) if 'group_case_id' in group_by: group_by[group_by.index('group_case_id')] = s1.c.group_case_id group_by[group_by.index('group_name')] = s1.c.group_name return select( [sqlalchemy.func.count(s1.c.doc_id).label(self.key)] + group_by, group_by=[s1.c.maxmin] + filter_cols + group_by, having=AND(having).build_expression(s1), from_obj=join(s1, s2, s1.c.group_case_id == s2.c.group_case_id) ).params(filter_values)
def filtering(self): """Construct the query, by adding filtering(LIKE) on all columns when the datatable's search box is used """ def resolve_column(column): tmp_name = column.data.split('.') obj = getattr(self.sqla_object, tmp_name[0], None) if obj is None: raise DataTablesException('Invalid column data: ' + tmp_name[0]) if not hasattr(obj, "property"): # Ex: hybrid_property or property sqla_obj = self.sqla_object column_name = "".join(tmp_name[1:]) elif isinstance(obj.property, RelationshipProperty): # Ex: ForeignKey # Ex: address.description sqla_obj = obj.mapper.class_ column_name = "".join(tmp_name[1:]) if not column_name: # Find first primary key column_name = obj.property.table.primary_key.columns.values()[0].name else: #-> ColumnProperty sqla_obj = self.sqla_object column_name = column.data return sqla_obj, column_name condition = None search_value = self.request_values.get('search[value]') if search_value != "": conditions = [] for column in self.columns: # ignore null columns (javascript placeholder) or unsearchable if column.data != "" and column.searchable: sqla_obj, column_name = resolve_column(column) conditions.append(cast(get_attr(sqla_obj, column_name), String).ilike('%%%s%%' % search_value)) condition = or_(*conditions) conditions = [] for column in self.columns: # ignore null columns (javascript placeholder) or unsearchable if column.data != "" and column.search_value != "" and column.searchable: sqla_obj, column_name = resolve_column(column) #if col.search_like: # conditions.append(cast(get_attr(sqla_obj, column_name), String).like(col.search_like % search_value2)) #else: # conditions.append(cast(get_attr(sqla_obj, column_name), String).__eq__(search_value2)) conditions.append(cast(get_attr(sqla_obj, column_name), String).__eq__(column.search_value)) if condition is not None: condition = and_(condition, and_(*conditions)) else: condition= and_(*conditions) if condition is not None: self.query = self.query.filter(condition) # count after filtering self.cardinality_filtered = self.query.count() else: self.cardinality_filtered = self.cardinality
def refine_with_user_area(query): """Takes a query and refines it with a spatial constraint based on user setting""" if 'lon' and 'lat' and 'radius' in session: return query.filter(ST_DWithin( cast(Task.location, Geography), cast(from_shape(Point(session["lon"], session["lat"])), Geography), session["radius"])) else: return query
def by_location(cls, location): """Returns a query object of mountain waves around the location""" if not isinstance(location, Location): raise TypeError('Invalid `location` parameter.') return cls.query() \ .filter(db.func.ST_DWithin( cast(location.make_point(), Geography), cast(cls.location, Geography), 5000))
def search(self, keywords): criteria = [] for keyword in keywords_split(keywords): if keyword: keyword = '%{0}%'.format(keyword) criteria.append(cast(Article.title, Unicode).ilike(keyword)) criteria.append(cast(Article.keywords, Unicode).ilike(keyword)) return self.public().filter(db.or_(*criteria))
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 preprocess_value_and_column(cls, column, value): value_array = is_array(value) # Coerce operand if column.is_array and value_array: value = cast(pg.array(value), pg.ARRAY(column.sql_col.type.item_type)) if column.is_json: coerce_type = column.sql_col.type.coerce_compared_value('=', value) # HACKY: use sqlalchemy type coercion column.sql_col = cast(column.sql_col, coerce_type) return column, value
def filtering(self): """Construct the query, by adding filtering(LIKE) on all columns when the datatable's search box is used """ search_value = self.request_values.get('sSearch') condition = None def search(idx, col): tmp_column_name = col.column_name.split('.') obj = getattr(self.sqla_object, tmp_column_name[0]) if not hasattr(obj, "property"): # Ex: hybrid_property or property sqla_obj = self.sqla_object column_name = col.column_name elif isinstance(obj.property, RelationshipProperty): #Ex: ForeignKey # Ex: address.description sqla_obj = obj.mapper.class_ column_name = "".join(tmp_column_name[1:]) if not column_name: # find first primary key column_name = obj.property.table.primary_key.columns \ .values()[0].name else: sqla_obj = self.sqla_object column_name = col.column_name return sqla_obj, column_name if search_value: conditions = [] for idx, col in enumerate(self.columns): if self.request_values.get('bSearchable_%s' % idx) in ( True, 'true'): sqla_obj, column_name = search(idx, col) conditions.append(cast(get_attr(sqla_obj, column_name), String).ilike('%%%s%%' % search_value)) condition = or_(*conditions) conditions = [] for idx, col in enumerate(self.columns): if self.request_values.get('sSearch_%s' % idx) in (True, 'true'): search_value2 = self.request_values.get('sSearch_%s' % idx) sqla_obj, column_name = search(idx, col) if col.search_like: conditions.append(cast(get_attr(sqla_obj, column_name), String).like(col.search_like % search_value2)) else: conditions.append(cast(get_attr(sqla_obj, column_name), String).__eq__(search_value2)) if condition is not None: condition = and_(condition, and_(*conditions)) else: condition= and_(*conditions) if condition is not None: self.query = self.query.filter(condition) # count after filtering self.cardinality_filtered = self.query.count() else: self.cardinality_filtered = self.cardinality
def all(self, *args, **kwargs): (_u, _p, _e, _l) = [getattr(self.db, options)._table for options in ('userfeatures', 'phonefunckey', 'extenumbers', 'linefeatures')] conds = [ _l.c.iduserfeatures == _p.c.iduserfeatures, _p.c.typeextenumbers != None, _p.c.typevalextenumbers != None, _p.c.supervision == 1, _p.c.progfunckey == 1, cast(_p.c.typeextenumbers, VARCHAR(255)) == cast(_e.c.type, VARCHAR(255)), _p.c.typevalextenumbers != 'user', _p.c.typevalextenumbers == _e.c.typeval ] if 'context' in kwargs: conds.append(_l.c.context == kwargs['context']) q = select( [_p.c.iduserfeatures, _p.c.exten, _p.c.typeextenumbers, _p.c.typevalextenumbers, _p.c.typeextenumbersright, _p.c.typevalextenumbersright, _e.c.exten.label('leftexten')], and_(*conds) ) """ _l2 = alias(_l) conds = [ _l.c.iduserfeatures == _p.c.iduserfeatures, _p.c.typeextenumbers != None, _p.c.typevalextenumbers != None, _p.c.supervision == 1, _p.c.progfunckey == 1, cast(_p.c.typeextenumbers,VARCHAR(255)) == cast(_e.c.type,VARCHAR(255)), _p.c.typevalextenumbers == 'user', _p.c.typevalextenumbers == cast(_l2.c.iduserfeatures,VARCHAR(255)), _e.c.typeval == cast(_l2.c.id,VARCHAR(255)) ] if 'context' in kwargs: conds.append(_l.c.context == kwargs['context']) q2 = select( [_p.c.iduserfeatures, _p.c.exten, _p.c.typeextenumbers, _p.c.typevalextenumbers, _p.c.typeextenumbersright, _p.c.typevalextenumbersright, _e.c.exten.label('leftexten')], and_(*conds) ) return self.execute(q1.union(q2)).fetchall() """ return self.execute(q).fetchall()
def upgrade_severity_levels(session, severity_map): """ Updates the potentially changed severities at the reports. """ LOG.debug("Upgrading severity levels started...") # Create a sql query from the severity map. severity_map_q = union_all(*[ select([cast(bindparam('checker_id' + str(i), str(checker_id)) .label('checker_id'), sqlalchemy.String), cast(bindparam('severity' + str(i), Severity._NAMES_TO_VALUES[ severity_map[checker_id]]) .label('severity'), sqlalchemy.Integer)]) for i, checker_id in enumerate(severity_map)]) \ .alias('new_severities') checker_ids = severity_map.keys() # Get checkers which has been changed. changed_checker_q = select([Report.checker_id, Report.severity]) \ .group_by(Report.checker_id, Report.severity) \ .where(Report.checker_id.in_(checker_ids)) \ .except_(session.query(severity_map_q)).alias('changed_severites') changed_checkers = session.query(changed_checker_q.c.checker_id, changed_checker_q.c.severity) # Update severity levels of checkers. if changed_checkers: updated_checker_ids = set() for checker_id, severity_old in changed_checkers: severity_new = severity_map.get(checker_id, 'UNSPECIFIED') severity_id = Severity._NAMES_TO_VALUES[severity_new] LOG.info("Upgrading severity level of '%s' checker from %s to %s", checker_id, Severity._VALUES_TO_NAMES[severity_old], severity_new) if checker_id in updated_checker_ids: continue session.query(Report) \ .filter(Report.checker_id == checker_id) \ .update({Report.severity: severity_id}) updated_checker_ids.add(checker_id) session.commit() LOG.debug("Upgrading of severity levels finished...")
def top_n(session, day_from, day_to, action_kinds=['src_add',], num_results=5): """ helper for league tables """ cnts = session.query(Action.user_id, func.count('*').label('cnt')).\ filter(Action.what.in_(action_kinds)).\ filter(cast(Action.performed, Date) >= day_from).\ filter(cast(Action.performed, Date) <= day_to).\ group_by(Action.user_id).\ subquery() return session.query(UserAccount, cnts.c.cnt).\ join(cnts, UserAccount.id==cnts.c.user_id).\ order_by(cnts.c.cnt.desc()).\ limit(num_results).\ all()
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 handle(self, *args, **options): # set up config = get_config() if config is None: raise CommandError('Unable to process configuration file p_to_p.yml') connection = get_connection(config) pedsnet_session = init_pedsnet(connection) init_pcornet(connection) init_vocab(connection) pedsnet_pcornet_valueset_map = aliased(ValueSetMap) # extract the data from the death table death_cause = pedsnet_session.query(DeathPedsnet.person_id, func.left(DeathPedsnet.cause_source_value, 8), coalesce(pedsnet_pcornet_valueset_map.target_concept, 'OT'), bindparam("death_cause_type", "NI"), bindparam("death_cause_source", "L"), bindparam("death_cause_confidence", None), min(DeathPedsnet.site) ) \ .join(Demographic, Demographic.patid == cast(DeathPedsnet.person_id, String(256)), ) \ .join(VocabularyConcept, VocabularyConcept.concept_id == DeathPedsnet.cause_concept_id) \ .outerjoin(pedsnet_pcornet_valueset_map, and_(pedsnet_pcornet_valueset_map.source_concept_class == 'death cause code', cast(VocabularyConcept.vocabulary_id, String(200)) == pedsnet_pcornet_valueset_map.source_concept_id)) \ .filter(and_(DeathPedsnet.cause_source_value != None, DeathPedsnet.cause_source_concept_id != 44814650)) \ .group_by(DeathPedsnet.person_id, func.left(DeathPedsnet.cause_source_value, 8), coalesce(pedsnet_pcornet_valueset_map.target_concept, 'OT')) \ .all() # transform data to pcornet names and types # load to demographic table odo(death_cause, DeathCause.__table__, dshape='var * {patid: string, death_cause: string, death_cause_code: string,' 'death_cause_type: string, death_cause_source:string, ' 'death_cause_confidence: string, site: string}' ) # close session pedsnet_session.close() # output result self.stdout.ending = '' print('Death Cause ETL completed successfully', end='', file=self.stdout)
def extend_query_with_textfilter(self, query, text): """Extends the given `query` with text filters. This is only done when config's `filter_text` is set. """ if len(text): if isinstance(text, str): text = text.decode('utf-8') # remove trailing asterisk if text.endswith('*'): text = text[:-1] # lets split up the search term into words, extend them with # the default wildcards and then search for every word # seperately for word in text.strip().split(' '): term = '%%%s%%' % word # XXX check if the following hack is still necessary # Fixed Problems with the collation with the Oracle DB # the case insensitive worked just every second time # now it works fine # Issue #759 query.session query = query.filter(or_( *[cast(field, String).ilike(term) for field in self.searchable_columns])) return query
def getSpectraBinID(freq_bins = None, dir_bins = None): spectra = _session.query(SpectraRecord).filter(and_( SpectraRecord.spcfreq == cast(freq_bins, ARRAY(DOUBLE_PRECISION)), SpectraRecord.spcdir == cast(dir_bins, ARRAY(DOUBLE_PRECISION)) )).first() if spectra: return spectra.id else: # Create a record for the spectra. spectra = SpectraRecord(spcFreq = freq_bins, spcDir = dir_bins) _session.add(spectra) _session.commit() return spectra.id
def find_sccp_speeddial_settings(session): query = (session.query(FuncKeyMapping.position.label('fknum'), FuncKeyMapping.label.label('label'), cast(FuncKeyMapping.blf, Integer).label('supervision'), FuncKeyDestCustom.exten.label('exten'), UserFeatures.id.label('user_id'), SCCPDevice.device.label('device')) .join(UserFeatures, FuncKeyMapping.template_id == UserFeatures.func_key_private_template_id) .join(FuncKeyDestCustom, FuncKeyDestCustom.func_key_id == FuncKeyMapping.func_key_id) .join(UserLine, and_( UserLine.user_id == UserFeatures.id, UserLine.main_user == True)) .join(LineFeatures, UserLine.line_id == LineFeatures.id) .join(SCCPLine, and_( LineFeatures.protocol == 'sccp', LineFeatures.protocolid == SCCPLine.id)) .join(SCCPDevice, SCCPLine.name == SCCPDevice.line) .filter(LineFeatures.commented == 0)) keys = [{'exten': row.exten, 'fknum': row.fknum, 'label': row.label, 'supervision': row.supervision, 'user_id': row.user_id, 'device': row.device} for row in query] return keys
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 paginate_query(query, model, limit, sort_keys, marker=None, sort_dir=None, sort_dirs=None): """Returns a query with sorting / pagination criteria added. Pagination works by requiring a unique sort_key, specified by sort_keys. (If sort_keys is not unique, then we risk looping through values.) We use the last row in the previous page as the 'marker' for pagination. So we must return values that follow the passed marker in the order. With a single-valued sort_key, this would be easy: sort_key > X. With a compound-values sort_key, (k1, k2, k3) we must do this to repeat the lexicographical ordering: (k1 > X1) or (k1 == X1 && k2 > X2) or (k1 == X1 && k2 == X2 && k3 > X3) We also have to cope with different sort_directions and cases where k2, k3, ... are nullable. Typically, the id of the last row is used as the client-facing pagination marker, then the actual marker object must be fetched from the db and passed in to us as marker. The "offset" parameter is intentionally avoided. As offset requires a full scan through the preceding results each time, criteria-based pagination is preferred. See http://use-the-index-luke.com/no-offset for further background. :param query: the query object to which we should add paging/sorting :param model: the ORM model class :param limit: maximum number of items to return :param sort_keys: array of attributes by which results should be sorted :param marker: the last item of the previous page; we returns the next results after this value. :param sort_dir: direction in which results should be sorted (asc, desc) suffix -nullsfirst, -nullslast can be added to defined the ordering of null values :param sort_dirs: per-column array of sort_dirs, corresponding to sort_keys :rtype: sqlalchemy.orm.query.Query :return: The query with sorting/pagination added. """ if _stable_sorting_order(model, sort_keys) is False: LOG.warning('Unique keys not in sort_keys. ' 'The sorting order may be unstable.') assert (not (sort_dir and sort_dirs)) # Default the sort direction to ascending if sort_dirs is None and sort_dir is None: sort_dir = 'asc' # Ensure a per-column sort direction if sort_dirs is None: sort_dirs = [sort_dir for _sort_key in sort_keys] assert (len(sort_dirs) == len(sort_keys)) # Add sorting for current_sort_key, current_sort_dir in zip(sort_keys, sort_dirs): try: inspect(model).all_orm_descriptors[current_sort_key] except KeyError: raise exception.InvalidSortKey(current_sort_key) else: sort_key_attr = getattr(model, current_sort_key) try: main_sort_dir, __, null_sort_dir = current_sort_dir.partition("-") sort_dir_func = { 'asc': sqlalchemy.asc, 'desc': sqlalchemy.desc, }[main_sort_dir] null_order_by_stmt = { "": None, "nullsfirst": sort_key_attr.is_(None), "nullslast": sort_key_attr.isnot(None), }[null_sort_dir] except KeyError: raise ValueError( _("Unknown sort direction, " "must be one of: %s") % ", ".join(_VALID_SORT_DIR)) if null_order_by_stmt is not None: query = query.order_by(sqlalchemy.desc(null_order_by_stmt)) query = query.order_by(sort_dir_func(sort_key_attr)) # Add pagination if marker is not None: marker_values = [] for sort_key in sort_keys: v = getattr(marker, sort_key) marker_values.append(v) # Build up an array of sort criteria as in the docstring criteria_list = [] for i in range(len(sort_keys)): crit_attrs = [] # NOTE: We skip the marker value comparison if marker_values[i] is # None, for two reasons: 1) the comparison operators below # ('<', '>') are not applicable on None value; 2) this is # safe because we can assume the primary key is included in # sort_key, thus checked as (one of) marker values. if marker_values[i] is not None: for j in range(i): model_attr = getattr(model, sort_keys[j]) crit_attrs.append((model_attr == marker_values[j])) model_attr = getattr(model, sort_keys[i]) val = marker_values[i] # sqlalchemy doesn't like booleans in < >. bug/1656947 if isinstance(model_attr.type, Boolean): val = int(val) model_attr = cast(model_attr, Integer) if sort_dirs[i].startswith('desc'): crit_attrs.append((model_attr < val)) else: crit_attrs.append((model_attr > val)) criteria = sqlalchemy.sql.and_(*crit_attrs) criteria_list.append(criteria) f = sqlalchemy.sql.or_(*criteria_list) query = query.filter(f) if limit is not None: query = query.limit(limit) return query
def perm(cls) -> str: # pylint: disable=no-self-argument return ("[" + cls.database_name + "].(id:" + expression.cast(cls.id, String) + ")")
def chat_list(request): current_page = int(request.GET.get("page", 1)) if request.matched_route.name.startswith("chat_list_unanswered"): current_status = "unanswered" elif request.matched_route.name.startswith("chat_list_ongoing"): current_status = "ongoing" elif request.matched_route.name.startswith("chat_list_ended"): current_status = "ended" else: current_status = None if request.matched_route.name.startswith("chat_list_label"): current_label = request.matchdict["label"].lower().strip().replace( " ", "_") if current_label != request.matchdict["label"]: raise HTTPFound( request.route_path("chat_list_label", label=current_label)) else: current_label = None chats = Session.query(ChatUser, Chat, Message).join(Chat).outerjoin( Message, Message.id == Session.query(func.min(Message.id), ).filter( Message.chat_id == Chat.id, ).correlate(Chat), ).filter(ChatUser.user_id == request.user.id, ) chat_count = Session.query(func.count('*')).select_from(ChatUser).filter( ChatUser.user_id == request.user.id, ) if current_status == "unanswered": chats = chats.filter( and_( Chat.last_user_id is not None, Chat.last_user_id != request.user.id, )) chat_count = chat_count.join(Chat).filter( and_( Chat.last_user_id is not None, Chat.last_user_id != request.user.id, )) elif current_status is not None: chats = chats.filter(Chat.status == current_status) chat_count = chat_count.join(Chat).filter( Chat.status == current_status) if current_label is not None: label_array = cast([current_label], ARRAY(Unicode(500))) chats = chats.filter(ChatUser.labels.contains(label_array)) chat_count = chat_count.filter(ChatUser.labels.contains(label_array)) chats = chats.order_by(Chat.updated.desc()).limit(25).offset( (current_page - 1) * 25).all() # 404 on empty pages, unless it's the first page. if current_page != 1 and len(chats) == 0: raise HTTPNotFound chat_count = chat_count.scalar() if request.matchdict.get("fmt") == "json": return render_to_response("json", { "chats": [{ "chat_user": chat_user, "chat": chat, "prompt": prompt, } for chat_user, chat, prompt in chats], "chat_count": chat_count, }, request=request) paginator = paginate.Page( [], page=current_page, items_per_page=25, item_count=chat_count, url=paginate.PageURL( request.route_path(request.matched_route.name, label=current_label), {"page": current_page}), ) labels = (Session.query( func.unnest(ChatUser.labels), func.count("*")).filter(ChatUser.user_id == request.user.id).group_by( func.unnest(ChatUser.labels)).order_by( func.count("*").desc(), func.unnest(ChatUser.labels).asc()).all()) template = "layout2/chat_list.mako" if request.user.layout_version == 2 else "chat_list.mako" return render_to_response(template, { "chats": chats, "paginator": paginator, "labels": labels, "current_status": current_status, "current_label": current_label, "symbols": symbols, }, request=request)
def tracking_delay_interval(cls): return cast(cast(cls.tracking_delay, String) + ' minutes', Interval)
def __eq__(self, other): return self._case() == cast(other, String)
def user_name(cls): decrypted = func.aes_decrypt(func.unhex(cls.enc_user_name), current_app.config['AES_KEY']) return cast(decrypted, db.String(64))
class User(BaseModel, DatedModel): """Holds users' data""" __tablename__ = "users" username = Column(String, nullable=False, unique=True, comment="User's identifier") active = Column( "is_active", BOOLEAN(), nullable=False, server_default=cast(1, BOOLEAN), comment="Denotes active users", ) _password = Column("password", String, nullable=False, comment="Password hash") # User identifiers email = Column( String, nullable=True, unique=True, comment="User's personal unique email" ) # meta data _photo = Column("photo", String, nullable=True, comment="User's avatar url") phone = Column(String, nullable=True, comment="Contact number") # User information first_name = Column(String, nullable=False, comment="First Name") last_name = Column(String, nullable=False, server_default="", comment="Last Name") manager_id = Column(Integer, ForeignKey("users.id"), nullable=True) manager: "User" = relationship( "User", foreign_keys=[manager_id], lazy=True, uselist=False ) # Relationships # Define the relationship to Role via UserRoles roles = relationship("Role", secondary="user_roles") # user sessions sessions = relationship( "Session", order_by="Session.created_at.asc()", uselist=True ) affiliation: "UserAffiliation" = relationship("UserAffiliation", uselist=False) token = None def set_password(self, val: str): regx = re.compile(current_app.config["PASSWORD_RULE"]) if not regx.match(val): raise UserExceptions.password_check_invalid() self._password = generate_password_hash(val) def get_password(self): return PasswordHelper(self._password) password = property(get_password, set_password) def get_photo(self): return ( self.__photo_handler if getattr(self, "__photo_handler", None) is not None else FileHandler(url=self._photo) if self._photo else None ) def set_photo(self, val: FileHandler): self.__photo_handler = val self._photo = getattr(val, "url", None) photo = property(get_photo, set_photo) def __init__( self, username: str, password: str, password_check: str, active: bool = True, email: str = None, photo: "FileHandler" = None, phone: str = None, first_name: str = "", last_name: str = "", **kwargs, ) -> None: if password != password_check: raise UserExceptions.password_check_invalid() self.username = username self.password = password self.active = active self.email = email self.photo = photo self.phone = phone self.first_name = first_name self.last_name = last_name @hybrid_property def name(self) -> str: """concatenates user's name""" return f"{self.first_name} {self.last_name}" def add_roles(self, roles: Union[List["Role"], "Role"]): """add roles to user Args: roles: A list of or a single role instances """ from ._UserRoles import UserRoles new_roles = [ UserRoles(user=self, role=role) for role in (roles if isinstance(roles, list) else [roles]) ] db.session.add_all(new_roles) def delete(self, persist=False): """Delete user's record""" if self.photo: self.photo.delete() super().delete(persist=persist) def add_entity(self, entity: "Entity", create: bool = False, edit: bool = False): from ._UserEntityPermission import UserEntityPermission permission = UserEntityPermission( entity=entity, user=self, create=create, edit=edit ) db.session.add(permission) db.session.commit() @hybrid_property def employees(self) -> List["User"]: return User.query.filter(User.manager_id == User.id).all() @hybrid_property def assets(self) -> List[AssetStorage]: return AssetStorage.query.filter(AssetStorage.added_by_id == self.id).all()
def get(self): """ .. http:get:: /api/1/items Get a list of items matching the given criteria. **Example Request**: .. sourcecode:: http GET /api/1/items HTTP/1.1 Host: example.com Accept: application/json **Example Response**: .. sourcecode:: http HTTP/1.1 200 OK Vary: Accept Content-Type: application/json { "items": [ { "account": "example_account", "region": "us-east-1", "technology": "sqs", "id": 14414, "name": "example_name", "num_issues": 3, "issue_score": 9, "unjustified_issue_score": 3, "active" true, "first_seen": "2014-06-17 19:47:07.299760", "last_seen": "2014-06-18 11:53:16.467709" } ], "total": 144, "page": 1, "auth": { "authenticated": true, "user": "******" } } :statuscode 200: no error :statuscode 401: Authenciation Error. Please Login. """ (auth, retval) = __check_auth__(self.auth_dict) if auth: return retval self.reqparse.add_argument('count', type=int, default=30, location='args') self.reqparse.add_argument('page', type=int, default=1, location='args') self.reqparse.add_argument('regions', type=str, default=None, location='args') self.reqparse.add_argument('accounts', type=str, default=None, location='args') self.reqparse.add_argument('active', type=str, default=None, location='args') self.reqparse.add_argument('names', type=str, default=None, location='args') self.reqparse.add_argument('technologies', type=str, default=None, location='args') self.reqparse.add_argument('searchconfig', type=str, default=None, location='args') self.reqparse.add_argument('ids', type=int, default=None, location='args') args = self.reqparse.parse_args() page = args.pop('page', None) count = args.pop('count', None) for k, v in args.items(): if not v: del args[k] # Read more about filtering: # http://docs.sqlalchemy.org/en/rel_0_7/orm/query.html query = Item.query.join((ItemRevision, Item.latest_revision_id == ItemRevision.id)) if 'regions' in args: regions = args['regions'].split(',') query = query.filter(Item.region.in_(regions)) if 'accounts' in args: accounts = args['accounts'].split(',') query = query.join((Account, Account.id == Item.account_id)) query = query.filter(Account.name.in_(accounts)) if 'technologies' in args: technologies = args['technologies'].split(',') query = query.join((Technology, Technology.id == Item.tech_id)) query = query.filter(Technology.name.in_(technologies)) if 'names' in args: names = args['names'].split(',') query = query.filter(Item.name.in_(names)) if 'ids' in args: ids = args['ids'].split(',') query = query.filter(Item.id.in_(ids)) if 'active' in args: active = args['active'].lower() == "true" query = query.filter(ItemRevision.active == active) if 'searchconfig' in args: searchconfig = args['searchconfig'] query = query.filter(cast(ItemRevision.config, String).ilike('%{}%'.format(searchconfig))) query = query.order_by(ItemRevision.date_created.desc()) items = query.paginate(page, count) marshaled_dict = {} marshaled_dict['page'] = items.page marshaled_dict['total'] = items.total marshaled_dict['auth'] = self.auth_dict marshaled_items = [] for item in items.items: num_issues = len(item.issues) issue_score = 0 unjustified_issue_score = 0 for issue in item.issues: issue_score = issue_score + issue.score if not issue.justified: unjustified_issue_score += issue.score first_seen = str(item.revisions[-1].date_created) last_seen = str(item.revisions[0].date_created) active = item.revisions[0].active item_marshaled = {} item_marshaled = marshal(item.__dict__, ITEM_FIELDS) item_marshaled = dict(item_marshaled.items() + { 'account': item.account.name, 'technology': item.technology.name, 'num_issues': num_issues, 'issue_score': issue_score, 'unjustified_issue_score': unjustified_issue_score, 'active': active, 'first_seen': first_seen, 'last_seen': last_seen #'last_rev': item.revisions[0].config, }.items()) marshaled_items.append(item_marshaled) marshaled_dict['items'] = marshaled_items marshaled_dict['count'] = len(marshaled_items) return marshaled_dict, 200
def read(self, query, data=None, order=None, limit=None, offset=0): """ Get the model objects matching the supplied query parameters, optionally setting which part of the objects are in the returned dictionary using the supplied data parameter Arguments: session (sqlalchemy.Session): SQLAlchemy session to use. query: one or more queries (as c{dict} or [c{dict}]), corresponding to the format of the query parameter described in the module-level docstrings. This query parameter will be normalized data: one or more data specification (as c{dict} or [c{dict}]), corresponding to the format of the data specification parameter described in the module-level docstrings. The length of the data parameter should either be 1 which will be the spec for each query specified, OR of length N, where N is the number of queries after normalization. If not provided the _data parameter will be expected in each query limit: The limit parameter, when provided with positive integer "L" at most "L" results will be returned. Defaults to no limit offset: The offset parameter, when provided with positive integer "F", at most "L" results will be returned after skipping the first "F" results (first based on ordering) Returns: list: one or more data specification dictionaries with models that match the provided queries including all readable fields without following foreign keys (the default if no data parameter is included), OR the key/values specified by the data specification parameter. The number of items returned and the order in which they appear are controlled by the limit, offset and order parameters. Represented as:: return { total: <int> # count of ALL matching objects, separate from <limit> results: [c{dict}, c{dict}, ... , c{dict}] # subject to <limit> } """ with Session() as session: filters = normalize_query(query) data = normalize_data(data, len(filters)) if len(filters) == 1: filter = filters[0] model = Session.resolve_model(filter['_model']) total = 0 results = [] if getattr(model, '_crud_perms', {}).get('read', True): total = CrudApi._filter_query(session.query(model), model, filter).count() results = CrudApi._filter_query( session.query(model), model, filter, limit, offset, order).all() return { 'total': total, 'results': [r.crud_read(data[0]) for r in results] } elif len(filters) > 1: queries = [] count_queries = [] queried_models = [] sort_field_types = {} for filter_index, filter in enumerate(filters): model = Session.resolve_model(filter['_model']) if getattr(model, '_crud_perms', {}).get('read', True): queried_models.append(model) query_fields = [ model.id, cast(literal(model.__name__), Text).label("_table_name"), cast(literal(filter_index), Integer) ] for sort_index, sort in enumerate( normalize_sort(model, order)): sort_field = getattr(model, sort['field']) sort_field_types[sort_index] = type( sort_field.__clause_element__().type) query_fields.append( sort_field.label( 'anon_sort_{}'.format(sort_index))) queries.append( CrudApi._filter_query( session.query(*query_fields), model, filter)) count_queries.append( CrudApi._filter_query(session.query(model.id), model, filter)) total = count_queries[0].union( *(count_queries[1:])).count() query = queries[0].union(*(queries[1:])) normalized_sort_fields = normalize_sort(None, order) for sort_index, sort in enumerate(normalized_sort_fields): dir = {'asc': asc, 'desc': desc}[sort['dir']] sort_field = 'anon_sort_{}'.format(sort_index) if issubclass(sort_field_types[sort_index], String): sort_field = 'lower({})'.format(sort_field) query = query.order_by(dir(sort_field)) if normalized_sort_fields: query = query.order_by("_table_name") rows = CrudApi._limit_query(query, limit, offset).all() result_table = {} result_order = {} query_index_table = {} for i, row in enumerate(rows): id = str(row[0]) model = Session.resolve_model(row[1]) query_index = row[2] result_table.setdefault(model, []).append(id) result_order[id] = i query_index_table[id] = query_index for model, ids in result_table.items(): result_table[model] = session.query(model).filter( model.id.in_(ids)).all() ordered_results = len(result_order) * [None] for model, instances in result_table.items(): for instance in instances: ordered_results[result_order[ instance.id]] = instance results = [r for r in ordered_results if r is not None] return { 'total': total, 'results': [ r.crud_read(data[query_index_table[r.id]]) for r in results ] } else: return {'total': 0, 'results': []}
def get(self): """ .. http:get:: /api/1/revisions Get a list of revisions **Example Request**: .. sourcecode:: http GET /api/1/revisions?count=1 HTTP/1.1 Host: example.com Accept: application/json **Example Response**: .. sourcecode:: http HTTP/1.1 200 OK Vary: Accept Content-Type: application/json { "items": [ { "account": "example_account", "accounttype": "AWS", "name": "Example Name", "region": "us-east-1", "item_id": 144, "active": false, "date_created": "2014-06-19 20:54:12.962951", "technology": "sqs", "id": 223757 } ], "total": 1, "page": 1, "auth": { "authenticated": true, "user": "******" } } :statuscode 200: no error :statuscode 401: Authentication Error. Please Login. """ self.reqparse.add_argument('count', type=int, default=30, location='args') self.reqparse.add_argument('page', type=int, default=1, location='args') self.reqparse.add_argument('active', type=str, default=None, location='args') self.reqparse.add_argument('regions', type=str, default=None, location='args') self.reqparse.add_argument('accounts', type=str, default=None, location='args') self.reqparse.add_argument('accounttypes', type=str, default=None, location='args') self.reqparse.add_argument('names', type=str, default=None, location='args') self.reqparse.add_argument('arns', type=str, default=None, location='args') self.reqparse.add_argument('technologies', type=str, default=None, location='args') self.reqparse.add_argument('searchconfig', type=str, default=None, location='args') args = self.reqparse.parse_args() page = args.pop('page', None) count = args.pop('count', None) for k, v in args.items(): if not v: del args[k] query = ItemRevision.query.join("item") if 'regions' in args: regions = args['regions'].split(',') query = query.filter(Item.region.in_(regions)) if 'accounts' in args: accounts = args['accounts'].split(',') query = query.join((Account, Account.id == Item.account_id)) query = query.filter(Account.name.in_(accounts)) if 'accounttypes' in args: accounttypes = args['accounttypes'].split(',') query = query.join((Account, Account.id == Item.account_id)) query = query.join( (AccountType, AccountType.id == Account.account_type_id)) query = query.filter(AccountType.name.in_(accounttypes)) if 'technologies' in args: technologies = args['technologies'].split(',') query = query.join((Technology, Technology.id == Item.tech_id)) query = query.filter(Technology.name.in_(technologies)) if 'names' in args: names = args['names'].split(',') query = query.filter(Item.name.in_(names)) if 'arns' in args: arns = args['arns'].split(',') query = query.filter(Item.arn.in_(arns)) if 'active' in args: active = args['active'].lower() == "true" query = query.filter(ItemRevision.active == active) if 'searchconfig' in args: searchconfig = args['searchconfig'] query = query.filter( cast(ItemRevision.config, String).ilike('%{}%'.format(searchconfig))) query = query.order_by(ItemRevision.date_created.desc()) revisions = query.paginate(page, count) marshaled_dict = { 'page': revisions.page, 'total': revisions.total, 'auth': self.auth_dict } items_marshaled = [] for revision in revisions.items: item_marshaled = marshal(revision.item.__dict__, ITEM_FIELDS) revision_marshaled = marshal(revision.__dict__, REVISION_FIELDS) account_marshaled = {'account': revision.item.account.name} accounttype_marshaled = { 'account_type': revision.item.account.account_type.name } technology_marshaled = { 'technology': revision.item.technology.name } merged_marshaled = dict(item_marshaled.items() + revision_marshaled.items() + account_marshaled.items() + accounttype_marshaled.items() + technology_marshaled.items()) items_marshaled.append(merged_marshaled) marshaled_dict['items'] = items_marshaled marshaled_dict['count'] = len(items_marshaled) return marshaled_dict, 200
def search(self, qs): return filter_number(cast(self.dt.model.pk, Integer), qs, type_=int)
def as_int(col): return cast(col, Integer)
db.Enum(ThingType), db.CheckConstraint( "\"type\" <> 'Book' OR \"extraData\"->>'prix_livre' SIMILAR TO '[0-9]+(.[0-9]*|)'", name='check_thing_book_has_price'), db.CheckConstraint( "\"type\" <> 'Book' OR NOT \"extraData\"->'author' IS NULL", name='check_thing_book_has_author'), db.CheckConstraint( "\"type\" <> 'Book' OR \"idAtProviders\" SIMILAR TO '[0-9]{13}'", name='check_thing_book_has_ean13'), nullable=False) name = db.Column(db.String(140), nullable=False) description = db.Column(db.Text, nullable=True) mediaUrls = db.Column(ARRAY(db.String(120)), nullable=False, default=[]) Thing.__ts_vector__ = create_tsvector( cast(coalesce(Thing.name, ''), TEXT), coalesce(Thing.extraData['author'].cast(TEXT), ''), coalesce(Thing.extraData['byArtist'].cast(TEXT), ''), ) Thing.__table_args__ = (Index('idx_thing_fts', Thing.__ts_vector__, postgresql_using='gin'), ) app.model.Thing = Thing
def get_project_stats(self) -> ProjectStatsDTO: """ Create Project Stats model for postgis project object""" project_stats = ProjectStatsDTO() project_stats.project_id = self.id project_area_sql = "select ST_Area(geometry, true)/1000000 as area from public.projects where id = :id" project_area_result = db.engine.execute(text(project_area_sql), id=self.id) project_stats.area = project_area_result.fetchone()["area"] project_stats.total_mappers = (db.session.query(User).filter( User.projects_mapped.any(self.id)).count()) project_stats.total_tasks = self.total_tasks project_stats.total_comments = (db.session.query(ProjectChat).filter( ProjectChat.project_id == self.id).count()) project_stats.percent_mapped = Project.calculate_tasks_percent( "mapped", self.total_tasks, self.tasks_mapped, self.tasks_validated, self.tasks_bad_imagery, ) project_stats.percent_validated = Project.calculate_tasks_percent( "validated", self.total_tasks, self.tasks_mapped, self.tasks_validated, self.tasks_bad_imagery, ) project_stats.percent_bad_imagery = Project.calculate_tasks_percent( "bad_imagery", self.total_tasks, self.tasks_mapped, self.tasks_validated, self.tasks_bad_imagery, ) centroid_geojson = db.session.scalar(self.centroid.ST_AsGeoJSON()) project_stats.aoi_centroid = geojson.loads(centroid_geojson) project_stats.total_time_spent = 0 project_stats.total_mapping_time = 0 project_stats.total_validation_time = 0 project_stats.average_mapping_time = 0 project_stats.average_validation_time = 0 total_mapping_time, total_mapping_tasks = (db.session.query( func.sum( cast(func.to_timestamp(TaskHistory.action_text, "HH24:MI:SS"), Time)), func.count(TaskHistory.action), ).filter( or_( TaskHistory.action == "LOCKED_FOR_MAPPING", TaskHistory.action == "AUTO_UNLOCKED_FOR_MAPPING", )).filter(TaskHistory.project_id == self.id).one()) if total_mapping_tasks > 0: total_mapping_time = total_mapping_time.total_seconds() project_stats.total_mapping_time = total_mapping_time project_stats.average_mapping_time = (total_mapping_time / total_mapping_tasks) project_stats.total_time_spent += total_mapping_time total_validation_time, total_validation_tasks = (db.session.query( func.sum( cast(func.to_timestamp(TaskHistory.action_text, "HH24:MI:SS"), Time)), func.count(TaskHistory.action), ).filter( or_( TaskHistory.action == "LOCKED_FOR_VALIDATION", TaskHistory.action == "AUTO_UNLOCKED_FOR_VALIDATION", )).filter(TaskHistory.project_id == self.id).one()) if total_validation_tasks > 0: total_validation_time = total_validation_time.total_seconds() project_stats.total_validation_time = total_validation_time project_stats.average_validation_time = (total_validation_time / total_validation_tasks) project_stats.total_time_spent += total_validation_time actions = [] if project_stats.average_mapping_time <= 0: actions.append(TaskStatus.LOCKED_FOR_MAPPING.name) if project_stats.average_validation_time <= 0: actions.append(TaskStatus.LOCKED_FOR_VALIDATION.name) zoom_levels = [] # Check that averages are non-zero. if len(actions) != 0: zoom_levels = (Task.query.with_entities( Task.zoom.distinct()).filter(Task.project_id == self.id).all()) zoom_levels = [z[0] for z in zoom_levels] # Validate project has arbitrary tasks. is_square = True if None in zoom_levels: is_square = False sq = (TaskHistory.query.with_entities( Task.zoom, TaskHistory.action, (cast(func.to_timestamp(TaskHistory.action_text, "HH24:MI:SS"), Time)).label("ts"), ).filter(Task.is_square == is_square).filter( TaskHistory.project_id == Task.project_id).filter( TaskHistory.task_id == Task.id).filter( TaskHistory.action.in_(actions))) if is_square is True: sq = sq.filter(Task.zoom.in_(zoom_levels)) sq = sq.subquery() nz = (db.session.query(sq.c.zoom, sq.c.action, sq.c.ts).filter( sq.c.ts > datetime.time(0)).limit(10000).subquery()) if project_stats.average_mapping_time <= 0: mapped_avg = (db.session.query(nz.c.zoom, (func.avg( nz.c.ts)).label("avg")).filter( nz.c.action == TaskStatus.LOCKED_FOR_MAPPING.name).group_by( nz.c.zoom).all()) mapping_time = sum([t.avg.total_seconds() for t in mapped_avg]) / len(mapped_avg) project_stats.average_mapping_time = mapping_time if project_stats.average_validation_time <= 0: val_avg = (db.session.query(nz.c.zoom, (func.avg( nz.c.ts)).label("avg")).filter( nz.c.action == TaskStatus.LOCKED_FOR_VALIDATION.name).group_by( nz.c.zoom).all()) validation_time = sum([t.avg.total_seconds() for t in val_avg]) / len(val_avg) project_stats.average_validation_time = validation_time time_to_finish_mapping = ( self.total_tasks - (self.tasks_mapped + self.tasks_bad_imagery + self.tasks_validated)) * project_stats.average_mapping_time project_stats.time_to_finish_mapping = time_to_finish_mapping project_stats.time_to_finish_validating = ( self.total_tasks - (self.tasks_validated + self.tasks_bad_imagery) ) * project_stats.average_validation_time return project_stats
def distance(self, location): loc1 = cast(self.location_wkt, Geography) loc2 = func.ST_GeographyFromText(location.to_wkt()) return db.session.scalar(func.ST_Distance(loc1, loc2))
def score(self): """Make score queryable.""" return cast(self.property3, Integer)
siren = db.Column( db.String(9), nullable=True, unique=True ) # FIXME: should not be nullable, is until we have all SIRENs filled in the DB def make_admin(self, admin): if admin: user_offerer = app.model.UserOfferer() user_offerer.offerer = self user_offerer.user = admin user_offerer.rights = app.model.RightsType.admin return user_offerer def errors(self): errors = super(Offerer, self).errors() if self.siren is not None\ and not len(self.siren) == 9: errors.addError('siren', 'Ce code SIREN est invalide') return errors Offerer.__ts_vector__ = create_tsvector( cast(coalesce(Offerer.name, ''), TEXT), cast(coalesce(Offerer.address, ''), TEXT), cast(coalesce(Offerer.siren, ''), TEXT)) Offerer.__table_args__ = (Index('idx_offerer_fts', Offerer.__ts_vector__, postgresql_using='gin'), ) app.model.Offerer = Offerer
def proportion(self): """Make proportion queryable.""" return cast(self.property4, Float)
def score_per_meaning_query(type_, filter_=None): # pragma: no cover """ select a.id, a.label, a.semantic_category, a.semantic_field_id, sum(a.borrowed_score)/sum(a.representation) as borrowed_score, count(distinct a.word_id) from ( -- tabulate (word_id, meaning_id) pairs against a word's discounted score select x.word_id, x.meaning_id as id, x.label as label, x.semantic_field_id as semantic_field_id, x.semantic_category as semantic_category, y.borrowed_score, y.representation from ( select wm.word_id as word_id, m.id as meaning_id, m.label as label, m.semantic_field_id as semantic_field_id, m.semantic_category as semantic_category from word_meaning as w+ ) as x, -- -- tabulate word ids against score discounted by number of meanings -- ( select w.pk as word_pk, w.id as word_id, cast(w.borrowed_score as float)/count(*) as borrowed_score, cast(1 as float)/count(*) as representation from word as w, counterpart as wm where w.pk = wm.word_pk group by w.id, w.borrowed_score ) as y -- --------------------------------------------------------------------------- where x.word_id = y.word_id ) as a --, -- --------------------------------------------------------------------------- -- select words we are interested in -- group by a.label, a.id, a.semantic_category, a.semantic_field_id order by a.id """ assert type_ in ['borrowed', 'age', 'simplicity'] attr = '%s_score' % type_ word, counterpart, parameter, meaning, valueset, value = [ m.__table__ for m in [Word, Counterpart, Parameter, Meaning, ValueSet, Value] ] x = alias(select([ counterpart.c.word_pk.label('word_pk'), parameter.c.pk.label('meaning_pk'), meaning.c.semantic_field_pk.label('semantic_field_pk'), ], from_obj=value, whereclause=and_( value.c.valueset_pk == valueset.c.pk, valueset.c.parameter_pk == parameter.c.pk, parameter.c.pk == meaning.c.pk, value.c.pk == counterpart.c.pk)), name='x') y = alias(select( [ word.c.pk.label('word_pk'), (cast(getattr(word.c, attr), Float) / func.count('*')).label(attr), (cast(1, Float) / func.count('*')).label('representation'), ], from_obj=counterpart, whereclause=word.c.pk == counterpart.c.word_pk, group_by=[word.c.pk, getattr(word.c, attr)], ), name='y') a = alias(select([ x.c.meaning_pk, x.c.semantic_field_pk, getattr(y.c, attr), y.c.representation ], whereclause=x.c.word_pk == y.c.word_pk), name='a') query = select([ a.c.meaning_pk, a.c.semantic_field_pk, (func.sum(getattr(a.c, attr)) / func.sum(a.c.representation)).label(attr), func.count(distinct(a.c.meaning_pk)), ], group_by=[a.c.meaning_pk, a.c.semantic_field_pk], order_by=a.c.meaning_pk) if isinstance(filter_, Meaning): query = query.where(a.c.meaning_pk == filter_.pk) if isinstance(filter_, SemanticField): query = query.where(a.c.semantic_field_pk == filter_.pk) return query
def fitness(self): """Retrieve fitness via property1.""" return cast(self.property1, Integer)
def get(self): """ .. http:get:: /api/1/items Get a list of items matching the given criteria. **Example Request**: .. sourcecode:: http GET /api/1/items HTTP/1.1 Host: example.com Accept: application/json **Example Response**: .. sourcecode:: http HTTP/1.1 200 OK Vary: Accept Content-Type: application/json { "items": [ { "account": "example_account", "account_type": "AWS", "region": AWS_DEFAULT_REGION, "technology": "sqs", "id": 14414, "name": "example_name", "num_issues": 3, "issue_score": 9, "unjustified_issue_score": 3, "active" true, "first_seen": "2014-06-17 19:47:07.299760", "last_seen": "2014-06-18 11:53:16.467709" } ], "total": 144, "page": 1, "auth": { "authenticated": true, "user": "******" } } :statuscode 200: no error :statuscode 401: Authenciation Error. Please Login. """ self.reqparse.add_argument('count', type=int, default=30, location='args') self.reqparse.add_argument('page', type=int, default=1, location='args') self.reqparse.add_argument('regions', type=str, default=None, location='args') self.reqparse.add_argument('accounts', type=str, default=None, location='args') self.reqparse.add_argument('accounttypes', type=str, default=None, location='args') self.reqparse.add_argument('active', type=str, default=None, location='args') self.reqparse.add_argument('names', type=str, default=None, location='args') self.reqparse.add_argument('arns', type=str, default=None, location='args') self.reqparse.add_argument('technologies', type=str, default=None, location='args') self.reqparse.add_argument('searchconfig', type=str, default=None, location='args') self.reqparse.add_argument('ids', type=int, default=None, location='args') self.reqparse.add_argument('summary', type=bool, default=False, location='args') self.reqparse.add_argument('min_score', type=int, default=False, location='args') self.reqparse.add_argument('min_unjustified_score', type=int, default=False, location='args') args = self.reqparse.parse_args() page = args.pop('page', None) count = args.pop('count', None) for k, v in list(args.items()): if not v: del args[k] # Read more about filtering: # https://docs.sqlalchemy.org/en/latest/orm/query.html query = Item.query.join( (ItemRevision, Item.latest_revision_id == ItemRevision.id)) # Fix for issue https://github.com/Netflix/security_monkey/issues/1150 # PR https://github.com/Netflix/security_monkey/pull/1153 join_account = False if 'regions' in args: regions = args['regions'].split(',') query = query.filter(Item.region.in_(regions)) if 'accounts' in args: accounts = args['accounts'].split(',') query = query.filter(Account.name.in_(accounts)) join_account = True if 'accounttypes' in args: accounttypes = args['accounttypes'].split(',') query = query.join( (AccountType, AccountType.id == Account.account_type_id)) query = query.filter(AccountType.name.in_(accounttypes)) join_account = True if 'technologies' in args: technologies = args['technologies'].split(',') query = query.join((Technology, Technology.id == Item.tech_id)) query = query.filter(Technology.name.in_(technologies)) if 'names' in args: names = args['names'].split(',') query = query.filter(Item.name.in_(names)) if 'arns' in args: arns = args['arns'].split(',') query = query.filter(Item.arn.in_(arns)) if 'ids' in args: ids = args['ids'].split(',') query = query.filter(Item.id.in_(ids)) if 'active' in args: active = args['active'].lower() == "true" query = query.filter(ItemRevision.active == active) query = query.filter(Account.active == True) join_account = True if 'searchconfig' in args: searchconfig = args['searchconfig'] query = query.filter( cast(ItemRevision.config, String).ilike('%{}%'.format(searchconfig))) if 'min_score' in args: min_score = args['min_score'] query = query.filter(Item.score >= min_score) if 'min_unjustified_score' in args: min_unjustified_score = args['min_unjustified_score'] query = query.filter( Item.unjustified_score >= min_unjustified_score) if join_account == True: query = query.join((Account, Account.id == Item.account_id)) # Eager load the joins except for the revisions because of the dynamic lazy relationship query = query.options(joinedload('issues')) query = query.options(joinedload('account')) query = query.options(joinedload('technology')) query = query.order_by(ItemRevision.date_created.desc()) items = query.paginate(page, count) marshaled_dict = { 'page': items.page, 'total': items.total, 'auth': self.auth_dict } marshaled_items = [] for item in items.items: item_marshaled = marshal(item.__dict__, ITEM_FIELDS) if 'summary' in args and args['summary']: item_marshaled = dict( list(item_marshaled.items()) + list({ 'account': item.account.name, 'account_type': item.account.account_type.name, 'technology': item.technology.name, 'num_issues': item.issue_count, 'issue_score': item.score, 'unjustified_issue_score': item.unjustified_score, 'active': active, #'last_rev': item.revisions[0].config, }.items())) else: first_seen_query = ItemRevision.query.filter( ItemRevision.item_id == item.id).order_by( ItemRevision.date_created.asc()) first_seen = str(first_seen_query.first().date_created) last_seen = str(item.revisions.first().date_created) active = item.revisions.first().active item_marshaled = dict( list(item_marshaled.items()) + list({ 'account': item.account.name, 'account_type': item.account.account_type.name, 'technology': item.technology.name, 'num_issues': item.issue_count, 'issue_score': item.score, 'unjustified_issue_score': item.unjustified_score, 'active': active, 'first_seen': first_seen, 'last_seen': last_seen # 'last_rev': item.revisions[0].config, }.items())) marshaled_items.append(item_marshaled) marshaled_dict['items'] = marshaled_items marshaled_dict['count'] = len(marshaled_items) return marshaled_dict, 200
def rename_vo(old_vo, new_vo, insert_new_vo=False, description=None, email=None, commit_changes=False, skip_history=False, echo=True): """ Updates rows so that entries associated with `old_vo` are now associated with `new_vo` as part of multi-VO migration. :param old_vo: The 3 character string for the current VO (for a single-VO instance this will be 'def'). :param new_vo: The 3 character string for the new VO. :param insert_new_vo: If True then an entry for `new_vo` is created in the database. :param description: Full description of the new VO, unused if `insert_new_vo` is False. :param email: Admin email for the new VO, unused if `insert_new_vo` is False. :param commit_changes: If True then changes are made against the database directly. If False, then nothing is commited and the commands needed are dumped to be run later. :param skip_history: If True then tables without FKC containing historical data will not be converted to save time. """ success = True engine = session.get_engine(echo=echo) conn = engine.connect() trans = conn.begin() inspector = reflection.Inspector.from_engine(engine) metadata = MetaData(bind=conn, reflect=True) dialect = engine.dialect.name # Gather all the columns that need updating and all relevant foreign key constraints all_fks = [] tables_and_columns = [] for table_name in inspector.get_table_names(): if skip_history and ('_history' in table_name or '_hist_recent' in table_name): continue fks = [] table = Table(table_name, metadata) for column in table.c: if 'scope' in column.name or column.name == 'account': tables_and_columns.append((table, column)) for fk in inspector.get_foreign_keys(table_name): if not fk['name']: continue if 'scope' in fk['referred_columns'] or 'account' in fk[ 'referred_columns']: fks.append( ForeignKeyConstraint(fk['constrained_columns'], [ fk['referred_table'] + '.' + r for r in fk['referred_columns'] ], name=fk['name'], table=table, **fk['options'])) all_fks.extend(fks) try: bound_params = { 'old_vo': old_vo, 'new_vo': new_vo, 'old_vo_suffix': '' if old_vo == 'def' else old_vo, 'new_vo_suffix': '' if new_vo == 'def' else '@%s' % new_vo, 'split_character': '@', 'int_1': 1, 'int_2': 2, 'new_description': description, 'new_email': email, 'datetime': datetime.utcnow() } bound_params_text = {} for key in bound_params: if isinstance(bound_params[key], int): bound_params_text[key] = bound_params[key] else: bound_params_text[key] = "'%s'" % bound_params[key] if insert_new_vo: table = Table('vos', metadata) insert_command = table.insert().values( vo=bindparam('new_vo'), description=bindparam('new_description'), email=bindparam('new_email'), updated_at=bindparam('datetime'), created_at=bindparam('datetime')) print(str(insert_command) % bound_params_text + ';') if commit_changes: conn.execute(insert_command, bound_params) # Drop all FKCs affecting InternalAccounts/Scopes for fk in all_fks: print(str(DropConstraint(fk)) + ';') if commit_changes: conn.execute(DropConstraint(fk)) # Update columns for table, column in tables_and_columns: update_command = table.update().where( split_vo(dialect, column, return_vo=True) == bindparam( 'old_vo_suffix')) if new_vo == 'def': update_command = update_command.values( {column.name: split_vo(dialect, column)}) else: update_command = update_command.values({ column.name: split_vo(dialect, column) + cast(bindparam('new_vo_suffix'), CHAR(4)) }) print(str(update_command) % bound_params_text + ';') if commit_changes: conn.execute(update_command, bound_params) table = Table('rses', metadata) update_command = table.update().where( table.c.vo == bindparam('old_vo')).values(vo=bindparam('new_vo')) print(str(update_command) % bound_params_text + ';') if commit_changes: conn.execute(update_command, bound_params) # Re-add the FKCs we dropped for fkc in all_fks: print(str(AddConstraint(fkc)) + ';') if commit_changes: conn.execute(AddConstraint(fkc)) except: success = False print(format_exc()) print('Exception occured, changes not committed to DB.') if commit_changes and success: trans.commit() trans.close() return success
def get_project_stats(self) -> ProjectStatsDTO: """ Create Project Stats model for postgis project object""" project_stats = ProjectStatsDTO() project_stats.project_id = self.id project_area_sql = "select ST_Area(geometry, true)/1000000 as area from public.projects where id = :id" project_area_result = db.engine.execute(text(project_area_sql), id=self.id) project_stats.area = project_area_result.fetchone()["area"] project_stats.total_mappers = ( db.session.query(User).filter(User.projects_mapped.any(self.id)).count() ) project_stats.total_tasks = self.total_tasks project_stats.total_comments = ( db.session.query(ProjectChat) .filter(ProjectChat.project_id == self.id) .count() ) project_stats.percent_mapped = Project.calculate_tasks_percent( "mapped", self.total_tasks, self.tasks_mapped, self.tasks_validated, self.tasks_bad_imagery, ) project_stats.percent_validated = Project.calculate_tasks_percent( "validated", self.total_tasks, self.tasks_mapped, self.tasks_validated, self.tasks_bad_imagery, ) project_stats.percent_bad_imagery = Project.calculate_tasks_percent( "bad_imagery", self.total_tasks, self.tasks_mapped, self.tasks_validated, self.tasks_bad_imagery, ) centroid_geojson = db.session.scalar(self.centroid.ST_AsGeoJSON()) project_stats.aoi_centroid = geojson.loads(centroid_geojson) unique_mappers = ( TaskHistory.query.filter( TaskHistory.action == "LOCKED_FOR_MAPPING", TaskHistory.project_id == self.id, ) .distinct(TaskHistory.user_id) .count() ) unique_validators = ( TaskHistory.query.filter( TaskHistory.action == "LOCKED_FOR_VALIDATION", TaskHistory.project_id == self.id, ) .distinct(TaskHistory.user_id) .count() ) project_stats.total_time_spent = 0 project_stats.total_mapping_time = 0 project_stats.total_validation_time = 0 project_stats.average_mapping_time = 0 project_stats.average_validation_time = 0 total_mapping_time = ( db.session.query( func.sum( cast(func.to_timestamp(TaskHistory.action_text, "HH24:MI:SS"), Time) ) ) .filter( or_( TaskHistory.action == "LOCKED_FOR_MAPPING", TaskHistory.action == "AUTO_UNLOCKED_FOR_MAPPING", ) ) .filter(TaskHistory.project_id == self.id) ) for row in total_mapping_time: total_mapping_time = row[0] if total_mapping_time: total_mapping_seconds = total_mapping_time.total_seconds() project_stats.total_mapping_time = total_mapping_seconds project_stats.total_time_spent += project_stats.total_mapping_time if unique_mappers: average_mapping_time = total_mapping_seconds / unique_mappers project_stats.average_mapping_time = average_mapping_time query = ( TaskHistory.query.with_entities( func.date_trunc("minute", TaskHistory.action_date).label("trn"), func.max(TaskHistory.action_text).label("tm"), ) .filter(TaskHistory.project_id == self.id) .filter(TaskHistory.action == "LOCKED_FOR_VALIDATION") .group_by("trn") .subquery() ) total_validation_time = db.session.query( func.sum(cast(func.to_timestamp(query.c.tm, "HH24:MI:SS"), Time)) ).all() for row in total_validation_time: total_validation_time = row[0] if total_validation_time: total_validation_seconds = total_validation_time.total_seconds() project_stats.total_validation_time = total_validation_seconds project_stats.total_time_spent += project_stats.total_validation_time if unique_validators: average_validation_time = ( total_validation_seconds / unique_validators ) project_stats.average_validation_time = average_validation_time return project_stats
def ancestor_of(self, other): if isinstance(other, list): return self.op('@>')(expression.cast(other, ARRAY(LtreeType))) else: return self.op('@>')(other)
def __ne__(self, other): return self._case() != cast(other, String)
def descendant_of(self, other): if isinstance(other, list): return self.op('<@')(expression.cast(other, ARRAY(LtreeType))) else: return self.op('<@')(other)
def lquery(self, other): if isinstance(other, list): return self.op('?')(expression.cast(other, ARRAY(LQUERY))) else: return self.op('~')(other)
def _summarize_issues_sql(self, include_filename=False): diff_issue_occurrence_table = self.backend.get_table(DiffIssueOccurrence) issue_occurrence_table = self.backend.get_table(IssueOccurrence) issue_table = self.backend.get_table(Issue) file_revision_table = self.backend.get_table(FileRevision) project_issue_class_table = self.backend.get_table(ProjectIssueClass) issue_class_table = self.backend.get_table(self.project.IssueClass) #we group by file revision path, issue code and analyzer group_columns = [file_revision_table.c.language, file_revision_table.c.path, diff_issue_occurrence_table.c['key'], #we should not group by pk # diff_issue_occurrence_table.c['pk'], issue_table.c.code, issue_table.c.analyzer] project_pk_type = self.backend.get_field_type(self.project.fields['pk']) #here we make sure that the given issue class is enabled for the project subselect = select([issue_class_table.c.pk])\ .select_from(issue_class_table.join(project_issue_class_table))\ .where(and_( issue_table.c.analyzer == issue_class_table.c.analyzer, issue_table.c.code == issue_class_table.c.code, project_issue_class_table.c.project == expression.cast(self.project.pk,project_pk_type), project_issue_class_table.c.enabled == True))\ #we perform a JOIN of the file revision table to the issue tables table = diff_issue_occurrence_table\ .join(issue_occurrence_table, issue_occurrence_table.c.pk == diff_issue_occurrence_table.c.issue_occurrence)\ .join(issue_table)\ .join(file_revision_table) #we select the aggregated issues for all file revisions in this snapshot s = select(group_columns+[func.count().label('count')])\ .select_from(table)\ .where(and_(exists(subselect),diff_issue_occurrence_table.c.diff == self.pk))\ .group_by(*group_columns)\ .order_by(file_revision_table.c.path) #we fetch the result with self.backend.transaction(): result = self.backend.connection.execute(s).fetchall() #we aggregate the issues by path fragments aggregator = lambda f: directory_splitter(f['path'],include_filename = include_filename) added_issues = [] fixed_issues = [] for row in result: if row['key'] == 'added': added_issues.append(row) else: fixed_issues.append(row) #we perform a map/reduce on the result map_reducer = IssuesMapReducer(aggregators = [aggregator], group_by = ['language','analyzer','code']) return {'added': map_reducer.mapreduce(added_issues), 'fixed': map_reducer.mapreduce(fixed_issues)}
def get_periodic_stats_quarter_hour(session, start, end): quarter_hour_step = func.date_trunc(literal('hour'), StatCallOnQueue.time) + \ (cast(extract('minute', StatCallOnQueue.time), Integer) / 15) * timedelta(minutes=15) return _get_periodic_stat_by_step(session, start, end, quarter_hour_step)