def test_positional_binds_2(self): orders = table('orders', column('order'), ) s = select([orders.c.order, literal("x")]).cte("regional_sales") s = select([s.c.order, literal("y")]) dialect = default.DefaultDialect() dialect.positional = True dialect.paramstyle = 'numeric' s1 = select([orders.c.order]).where(orders.c.order == 'x').\ cte("regional_sales_1") s1a = s1.alias() s2 = select([orders.c.order == 'y', s1a.c.order, orders.c.order, s1.c.order]).\ where(orders.c.order == 'z').\ cte("regional_sales_2") s3 = select([s2]) self.assert_compile( s3, 'WITH regional_sales_1 AS (SELECT orders."order" AS "order" ' 'FROM orders WHERE orders."order" = :1), regional_sales_2 AS ' '(SELECT orders."order" = :2 AS anon_1, ' 'anon_2."order" AS "order", ' 'orders."order" AS "order", ' 'regional_sales_1."order" AS "order" FROM orders, ' 'regional_sales_1 ' 'AS anon_2, regional_sales_1 ' 'WHERE orders."order" = :3) SELECT regional_sales_2.anon_1, ' 'regional_sales_2."order" FROM regional_sales_2', checkpositional=('x', 'y', 'z'), dialect=dialect)
def create_mapper(rack_specs_tbl): "Mapper factory." rs = rack_specs_tbl polymorphic_select = select([ rs, (case([(rs.c.has_movable_subitems, literal(RACK_SPECS_TYPES.TUBE_RACK_SPECS))], else_=literal(RACK_SPECS_TYPES.PLATE_SPECS))).label( 'rackspecs_type') ], ).alias('rackspecs') m = mapper(RackSpecs, polymorphic_select, id_attribute='rack_specs_id', slug_expression=lambda cls: as_slug_expression(cls.name), properties=dict( manufacturer=relationship(Organization), shape=relationship(RackShape, uselist=False, back_populates='specs'), rack_specs_type= column_property(polymorphic_select.c.rackspecs_type), ), polymorphic_on=polymorphic_select.c.rackspecs_type, polymorphic_identity=RACK_SPECS_TYPES.RACK_SPECS, ) RackSpecs.has_tubes = synonym('has_movable_subitems') return m
def _by_search_tsearch(self, query, operand, maybe_negate): # type: (Query, str, ConditionTransform) -> Query tsquery = func.plainto_tsquery(literal("zulip.english_us_search"), literal(operand)) ts_locs_array = func.ts_match_locs_array query = query.column(ts_locs_array(literal("zulip.english_us_search"), column("rendered_content"), tsquery).label("content_matches")) # We HTML-escape the subject in Postgres to avoid doing a server round-trip query = query.column(ts_locs_array(literal("zulip.english_us_search"), func.escape_html(column("subject")), tsquery).label("subject_matches")) # Do quoted string matching. We really want phrase # search here so we can ignore punctuation and do # stemming, but there isn't a standard phrase search # mechanism in Postgres for term in re.findall('"[^"]+"|\S+', operand): if term[0] == '"' and term[-1] == '"': term = term[1:-1] term = '%' + connection.ops.prep_for_like_query(term) + '%' cond = or_(column("content").ilike(term), column("subject").ilike(term)) query = query.where(maybe_negate(cond)) cond = column("search_tsvector").op("@@")(tsquery) return query.where(maybe_negate(cond))
def limit_clause(self, select): text = "" if select._limit is not None: text += " \n LIMIT " + self.process(sql.literal(select._limit)) if select._offset is not None: text += " OFFSET " + self.process(sql.literal(select._offset)) if select._limit is None: text += " ROWS" # OFFSET n ROW[S] return text
def user_requests_aggregate(session, user): """Returns all pending requests for this user to approve across groups.""" members = session.query( label("type", literal(1)), label("id", Group.id), label("name", Group.groupname), ).union(session.query( label("type", literal(0)), label("id", User.id), label("name", User.username), )).subquery() now = datetime.utcnow() groups = session.query( label("id", Group.id), label("name", Group.groupname), ).filter( GroupEdge.group_id == Group.id, GroupEdge.member_pk == user.id, GroupEdge.active == True, GroupEdge._role.in_(APPROVER_ROLE_INDICES), user.enabled == True, Group.enabled == True, or_( GroupEdge.expiration > now, GroupEdge.expiration == None, ) ).subquery() requests = session.query( Request.id, Request.requested_at, GroupEdge.expiration, label("role", GroupEdge._role), Request.status, label("requester", User.username), label("type", members.c.type), label("requesting", members.c.name), label("reason", Comment.comment), label("group_id", groups.c.id), label("groupname", groups.c.name), ).filter( Request.on_behalf_obj_pk == members.c.id, Request.on_behalf_obj_type == members.c.type, Request.requesting_id == groups.c.id, Request.requester_id == User.id, Request.status == "pending", Request.id == RequestStatusChange.request_id, RequestStatusChange.from_status == None, GroupEdge.id == Request.edge_id, Comment.obj_type == 3, Comment.obj_pk == RequestStatusChange.id, ) return requests
def limit_clause(self, select): text = "" if select._limit is not None: text += "\n LIMIT " + self.process(sql.literal(select._limit)) if select._offset is not None: if select._limit is None: text += "\n LIMIT " + self.process(sql.literal(-1)) text += " OFFSET " + self.process(sql.literal(select._offset)) else: text += " OFFSET " + self.process(sql.literal(0)) return text
def limit_clause(self, select): # CUBRID supports: LIMIT <limit> and LIMIT <offset>, <limit> limit, offset = select._limit, select._offset if (limit, offset) == (None, None): return "" elif limit is None and offset is not None: return " \n LIMIT %s, 1073741823" % (self.process(sql.literal(offset))) elif offset is not None: return " \n LIMIT %s, %s" % (self.process(sql.literal(offset)), self.process(sql.literal(limit))) else: return " \n LIMIT %s" % (self.process(sql.literal(limit)),)
def get(self): query = self.get_argument("query", "") offset = int(self.get_argument("offset", 0)) limit = int(self.get_argument("limit", 100)) if limit > 9000: limit = 9000 groups = ( self.session.query( label("type", literal("Group")), label("id", Group.id), label("name", Group.groupname), ) .filter(Group.enabled == True, Group.groupname.like("%{}%".format(query))) .subquery() ) permissions = ( self.session.query( label("type", literal("Permission")), label("id", Permission.id), label("name", Permission.name), ) .filter(Permission.enabled == True, Permission.name.like("%{}%".format(query))) .subquery() ) users = ( self.session.query( label("type", literal("User")), label("id", User.id), label("name", User.username) ) .filter(User.enabled == True, User.username.like("%{}%".format(query))) .subquery() ) results_query = self.session.query("type", "id", "name").select_entity_from( union_all(users.select(), permissions.select(), groups.select()) ) total = results_query.count() results = results_query.offset(offset).limit(limit).all() if len(results) == 1: result = results[0] return self.redirect("/{}s/{}".format(result.type.lower(), result.name)) self.render( "search.html", results=results, search_query=query, offset=offset, limit=limit, total=total, )
def test_notin(self): left = column('left') assert left.comparator.operate(operators.notin_op, [1, 2, 3]).compare( BinaryExpression( left, Grouping(ClauseList( literal(1), literal(2), literal(3) )), operators.notin_op ) ) self._loop_test(operators.notin_op, [1, 2, 3])
def _get_nodes_from_db(session): return session.query( label("type", literal("User")), label("name", User.username) ).filter( User.enabled == True ).union(session.query( label("type", literal("Group")), label("name", Group.groupname)) ).filter( Group.enabled == True ).all()
def _get_edges_from_db(session): parent = aliased(Group) group_member = aliased(Group) user_member = aliased(User) edges = [] now = datetime.utcnow() query = session.query( label("groupname", parent.groupname), label("type", literal("Group")), label("name", group_member.groupname), label("role", GroupEdge._role) ).filter( parent.id == GroupEdge.group_id, group_member.id == GroupEdge.member_pk, GroupEdge.active == True, parent.enabled == True, group_member.enabled == True, or_( GroupEdge.expiration > now, GroupEdge.expiration == None ), GroupEdge.member_type == 1 ).union(session.query( label("groupname", parent.groupname), label("type", literal("User")), label("name", user_member.username), label("role", GroupEdge._role) ).filter( parent.id == GroupEdge.group_id, user_member.id == GroupEdge.member_pk, GroupEdge.active == True, parent.enabled == True, user_member.enabled == True, or_( GroupEdge.expiration > now, GroupEdge.expiration == None ), GroupEdge.member_type == 0 )) for record in query.all(): edges.append(( ("Group", record.groupname), (record.type, record.name), {"role": record.role}, )) return edges
def get_select_precolumns(self, select): """Called when building a ``SELECT`` statement, position is just before column list Firebird puts the limit and offset right after the ``SELECT``... """ result = "" if select._limit: result += "FIRST %s " % self.process(sql.literal(select._limit)) if select._offset: result += "SKIP %s " % self.process(sql.literal(select._offset)) if select._distinct: result += "DISTINCT " return result
def _test_comparison_op(self, py_op, fwd_op, rev_op): dt = datetime.datetime(2012, 5, 10, 15, 27, 18) for (lhs, rhs, l_sql, r_sql) in ( ('a', self.table1.c.myid, ':myid_1', 'mytable.myid'), ('a', literal('b'), ':param_2', ':param_1'), # note swap! (self.table1.c.myid, 'b', 'mytable.myid', ':myid_1'), (self.table1.c.myid, literal('b'), 'mytable.myid', ':param_1'), (self.table1.c.myid, self.table1.c.myid, 'mytable.myid', 'mytable.myid'), (literal('a'), 'b', ':param_1', ':param_2'), (literal('a'), self.table1.c.myid, ':param_1', 'mytable.myid'), (literal('a'), literal('b'), ':param_1', ':param_2'), (dt, literal('b'), ':param_2', ':param_1'), (literal('b'), dt, ':param_1', ':param_2'), ): # the compiled clause should match either (e.g.): # 'a' < 'b' -or- 'b' > 'a'. compiled = str(py_op(lhs, rhs)) fwd_sql = "%s %s %s" % (l_sql, fwd_op, r_sql) rev_sql = "%s %s %s" % (r_sql, rev_op, l_sql) self.assert_(compiled == fwd_sql or compiled == rev_sql, "\n'" + compiled + "'\n does not match\n'" + fwd_sql + "'\n or\n'" + rev_sql + "'")
def test_positional_binds_2_asliteral(self): orders = table("orders", column("order")) s = select([orders.c.order, literal("x")]).cte("regional_sales") s = select([s.c.order, literal("y")]) dialect = default.DefaultDialect() dialect.positional = True dialect.paramstyle = "numeric" s1 = ( select([orders.c.order]) .where(orders.c.order == "x") .cte("regional_sales_1") ) s1a = s1.alias() s2 = ( select( [ orders.c.order == "y", s1a.c.order, orders.c.order, s1.c.order, ] ) .where(orders.c.order == "z") .cte("regional_sales_2") ) s3 = select([s2]) self.assert_compile( s3, "WITH regional_sales_1 AS " '(SELECT orders."order" AS "order" ' "FROM orders " "WHERE orders.\"order\" = 'x'), " "regional_sales_2 AS " "(SELECT orders.\"order\" = 'y' AS anon_1, " 'anon_2."order" AS "order", orders."order" AS "order", ' 'regional_sales_1."order" AS "order" ' "FROM orders, regional_sales_1 AS anon_2, regional_sales_1 " "WHERE orders.\"order\" = 'z') " 'SELECT regional_sales_2.anon_1, regional_sales_2."order" ' "FROM regional_sales_2", checkpositional=(), dialect=dialect, literal_binds=True, )
def visibility_horizon_query(self): """Get a query object that returns the highest category this one is visible from.""" cte_query = ( select( [ Category.id, Category.parent_id, db.case([(Category.visibility.is_(None), None)], else_=(Category.visibility - 1)).label("n"), literal(0).label("level"), ] ) .where(Category.id == self.id) .cte("visibility_horizon", recursive=True) ) parent_query = select( [ Category.id, Category.parent_id, db.case( [(Category.visibility.is_(None) & cte_query.c.n.is_(None), None)], else_=db.func.least(Category.visibility, cte_query.c.n) - 1, ), cte_query.c.level + 1, ] ).where(db.and_(Category.id == cte_query.c.parent_id, (cte_query.c.n > 0) | cte_query.c.n.is_(None))) cte_query = cte_query.union_all(parent_query) return db.session.query(cte_query.c.id, cte_query.c.n).order_by(cte_query.c.level.desc()).limit(1)
def messages_in_narrow_backend(request, user_profile, msg_ids = REQ(validator=check_list(check_int)), narrow = REQ(converter=narrow_parameter)): # type: (HttpRequest, UserProfile, List[int], List[Dict[str, Any]]) -> HttpResponse # Note that this function will only work on messages the user # actually received # TODO: We assume that the narrow is a search. For now this works because # the browser only ever calls this function for searches, since it can't # apply that narrow operator itself. query = select([column("message_id"), column("subject"), column("rendered_content")], and_(column("user_profile_id") == literal(user_profile.id), column("message_id").in_(msg_ids)), join(table("zerver_usermessage"), table("zerver_message"), literal_column("zerver_usermessage.message_id") == literal_column("zerver_message.id"))) builder = NarrowBuilder(user_profile, column("message_id")) for term in narrow: query = builder.add_term(query, term) sa_conn = get_sqlalchemy_connection() query_result = list(sa_conn.execute(query).fetchall()) search_fields = dict() for row in query_result: (message_id, subject, rendered_content, content_matches, subject_matches) = row search_fields[message_id] = get_search_fields(rendered_content, subject, content_matches, subject_matches) return json_success({"messages": search_fields})
def visit_tag_query(self, q): tags = self.session.query(Tag).filter_by(name=q.name) if tags.count() == 1: return ItemVersion.tags.any(id=tags[0].id) else: from sqlalchemy.sql import literal return literal(False)
def test_noorderby_parameters_insubquery(self): """test that the ms-sql dialect does not include ORDER BY positional parameters in subqueries""" table1 = table( "mytable", column("myid", Integer), column("name", String), column("description", String), ) q = select( [table1.c.myid, sql.literal('bar').label('c1')], order_by=[table1.c.name + '-'] ).alias("foo") crit = q.c.myid == table1.c.myid dialect = mssql.dialect() dialect.paramstyle = "qmark" dialect.positional = True self.assert_compile( select(["*"], crit), "SELECT * FROM (SELECT mytable.myid AS " "myid, ? AS c1 FROM mytable) AS foo, mytable WHERE " "foo.myid = mytable.myid", dialect=dialect, checkparams={'param_1': 'bar'}, # if name_1 is included, too many parameters are passed to dbapi checkpositional=('bar', ) )
def access_list_paths(self, member, prefix=None, include_owned=False, include_containers=True): """Return the list of paths granted to member. Keyword arguments: prefix -- return only paths starting with prefix (default None) include_owned -- return also paths owned by member (default False) include_containers -- return also container paths owned by member (default True) """ xfeatures_xfeaturevals = self.xfeatures.join(self.xfeaturevals) selectable = (self.groups.c.owner + ':' + self.groups.c.name) member_groups = select([selectable.label('value')], self.groups.c.member == member) members = select([literal(member).label('value')]) any = select([literal('*').label('value')]) u = union(member_groups, members, any).alias() inner_join = join(xfeatures_xfeaturevals, u, self.xfeaturevals.c.value == u.c.value) s = select([self.xfeatures.c.path], from_obj=[inner_join]).distinct() if prefix: like = lambda p: self.xfeatures.c.path.like( self.escape_like(p) + '%', escape=ESCAPE_CHAR) s = s.where(or_(*map(like, self.access_inherit(prefix) or [prefix]))) r = self.conn.execute(s) l = [row[0] for row in r.fetchall()] r.close() if include_owned: container_nodes = select( [self.nodes.c.node], self.nodes.c.parent == self.node_lookup(member)) condition = self.nodes.c.parent.in_(container_nodes) if include_containers: condition = or_(condition, self.nodes.c.node.in_(container_nodes)) s = select([self.nodes.c.path], condition) r = self.conn.execute(s) l += [row[0] for row in r.fetchall() if row[0] not in l] r.close() return l
def by_sender(self, query, operand, maybe_negate): try: sender = get_user_profile_by_email(operand) except UserProfile.DoesNotExist: raise BadNarrowOperator('unknown user ' + operand) cond = column("sender_id") == literal(sender.id) return query.where(maybe_negate(cond))
def _get_chain_query(start_criterion): cte_query = (select([Category.id, Category.parent_id, literal(0).label('level')]) .where(start_criterion) .cte('category_chain', recursive=True)) parent_query = (select([Category.id, Category.parent_id, cte_query.c.level + 1]) .where(Category.id == cte_query.c.parent_id)) cte_query = cte_query.union_all(parent_query) return Category.query.join(cte_query, Category.id == cte_query.c.id).order_by(cte_query.c.level.desc())
def my_requests(self, status=None, user=None): members = self.session.query( label("type", literal(1)), label("id", Group.id), label("name", Group.groupname) ).union(self.session.query( label("type", literal(0)), label("id", User.id), label("name", User.username) )).subquery() requests = self.session.query( Request.id, Request.requested_at, GroupEdge.expiration, label("role", GroupEdge._role), Request.status, label("requester", User.username), label("type", members.c.type), label("requesting", members.c.name), label("reason", Comment.comment) ).filter( Request.on_behalf_obj_pk == members.c.id, Request.on_behalf_obj_type == members.c.type, Request.requesting_id == self.id, Request.requester_id == User.id, Request.id == RequestStatusChange.request_id, RequestStatusChange.from_status == None, GroupEdge.id == Request.edge_id, Comment.obj_type == 3, Comment.obj_pk == RequestStatusChange.id ) if status: requests = requests.filter( Request.status == status ) if user: requests = requests.filter( Request.on_behalf_obj_pk == user.id, Request.on_behalf_obj_type == 0 ) return requests
def test_positional_binds_2(self): orders = table("orders", column("order")) s = select([orders.c.order, literal("x")]).cte("regional_sales") s = select([s.c.order, literal("y")]) dialect = default.DefaultDialect() dialect.positional = True dialect.paramstyle = "numeric" s1 = ( select([orders.c.order]) .where(orders.c.order == "x") .cte("regional_sales_1") ) s1a = s1.alias() s2 = ( select( [ orders.c.order == "y", s1a.c.order, orders.c.order, s1.c.order, ] ) .where(orders.c.order == "z") .cte("regional_sales_2") ) s3 = select([s2]) self.assert_compile( s3, 'WITH regional_sales_1 AS (SELECT orders."order" AS "order" ' 'FROM orders WHERE orders."order" = :1), regional_sales_2 AS ' '(SELECT orders."order" = :2 AS anon_1, ' 'anon_2."order" AS "order", ' 'orders."order" AS "order", ' 'regional_sales_1."order" AS "order" FROM orders, ' "regional_sales_1 " "AS anon_2, regional_sales_1 " 'WHERE orders."order" = :3) SELECT regional_sales_2.anon_1, ' 'regional_sales_2."order" FROM regional_sales_2', checkpositional=("x", "y", "z"), dialect=dialect, )
def by_sender(self, query, operand, maybe_negate): # type: (Query, str, ConditionTransform) -> Query try: sender = get_user_profile_by_email(operand) except UserProfile.DoesNotExist: raise BadNarrowOperator("unknown user " + operand) cond = column("sender_id") == literal(sender.id) return query.where(maybe_negate(cond))
def test_unary_both_ops(self): assert_raises_message( exc.CompileError, "Unary expression does not support operator and " "modifier simultaneously", UnaryExpression(literal("x"), operator=operators.custom_op("x"), modifier=operators.custom_op("y")).compile )
def create_view(metadata, molecule_design_pool_tbl, stock_sample_tbl, sample_tbl, container_tbl): """ stock_info_view factory. """ mdp = molecule_design_pool_tbl ss = stock_sample_tbl c = container_tbl s = sample_tbl stock = ( select( [ ( literal("mdp") + cast(mdp.c.molecule_design_set_id, String) + literal("c") + cast(coalesce(ss.c.concentration * 1e6, 0), String) ).label("stock_info_id"), mdp.c.molecule_design_set_id, # We need to set the label explicitly here because # mdp.c.molecule_type_id is really mdp.c.molecule_type. mdp.c.molecule_type_id.label("molecule_type_id"), # pylint: disable=E1101 coalesce(ss.c.concentration, 0).label("concentration"), coalesce(func.count(c.c.container_id), 0).label("total_tubes"), coalesce(func.sum(s.c.volume), 0).label("total_volume"), coalesce(func.min(s.c.volume), 0).label("minimum_volume"), coalesce(func.max(s.c.volume), 0).label("maximum_volume") # pylint: enable=E1101 ], from_obj=mdp.outerjoin(ss, ss.c.molecule_design_set_id == mdp.c.molecule_design_set_id) .outerjoin(s, s.c.sample_id == ss.c.sample_id) .outerjoin(c, and_(c.c.container_id == s.c.container_id, c.c.item_status == _STOCK_CONTAINER_ITEM_STATUS)), ) .group_by(mdp.c.molecule_design_set_id, ss.c.concentration) .alias("ssi") ) fkey_mds = ForeignKey(mdp.c.molecule_design_set_id) fkey_mds.parent = stock.c.molecule_design_set_id stock.c.molecule_design_set_id.foreign_keys.add(fkey_mds) fkey_mt = ForeignKey(mdp.c.molecule_type_id) fkey_mt.parent = stock.c.molecule_type_id stock.c.molecule_type_id.foreign_keys.add(fkey_mt) return view_factory(VIEW_NAME, metadata, stock)
def _go(conn): assert_raises_message( tsa.exc.StatementError, r"nope \(original cause: Exception: nope\) 'SELECT 1 ", conn.execute, select([1]).\ where( column('foo') == literal('bar', MyType()) ) )
def get_protection_parent_cte(cls): cat_alias = db.aliased(cls) cte_query = (select([cat_alias.id, db.cast(literal(None), db.Integer).label('protection_parent')]) .where(cat_alias.parent_id.is_(None)) .cte(recursive=True)) rec_query = (select([cat_alias.id, db.case({ProtectionMode.inheriting.value: func.coalesce(cte_query.c.protection_parent, 0)}, else_=cat_alias.id, value=cat_alias.protection_mode)]) .where(cat_alias.parent_id == cte_query.c.id)) return cte_query.union_all(rec_query)
def test_negate_operators_1(self): for (py_op, op) in ( (operator.neg, '-'), (operator.inv, 'NOT '), ): for expr, expected in ( (self.table1.c.myid, "mytable.myid"), (literal("foo"), ":param_1"), ): self.assert_compile(py_op(expr), "%s%s" % (op, expected))
def _go(conn): assert_raises_message( MyException, "nope", conn.execute, select([1]).\ where( column('foo') == literal('bar', MyType()) ) )
def test_unary_no_ops(self): assert_raises_message( exc.CompileError, "Unary expression has no operator or modifier", UnaryExpression(literal("x")).compile )
def test_in_13(self): self.assert_compile(self.table1.c.myid.in_([literal('a') < 'b']), "mytable.myid IN (:param_1 < :param_2)")
def topic_match_sa(topic_name: str) -> "ColumnElement[Boolean]": # _sa is short for SQLAlchemy, which we use mostly for # queries that search messages topic_cond = func.upper(column("subject", Text)) == func.upper(literal(topic_name)) return topic_cond
def add_types(engine: Engine, metadata: Metadata) -> None: """ Tag every object according to its type: INSERT INTO tagged_object (tag_id, object_id, object_type) SELECT tag.id AS tag_id, slices.id AS object_id, 'chart' AS object_type FROM slices JOIN tag ON tag.name = 'type:chart' LEFT OUTER JOIN tagged_object ON tagged_object.tag_id = tag.id AND tagged_object.object_id = slices.id AND tagged_object.object_type = 'chart' WHERE tagged_object.tag_id IS NULL; INSERT INTO tagged_object (tag_id, object_id, object_type) SELECT tag.id AS tag_id, dashboards.id AS object_id, 'dashboard' AS object_type FROM dashboards JOIN tag ON tag.name = 'type:dashboard' LEFT OUTER JOIN tagged_object ON tagged_object.tag_id = tag.id AND tagged_object.object_id = dashboards.id AND tagged_object.object_type = 'dashboard' WHERE tagged_object.tag_id IS NULL; INSERT INTO tagged_object (tag_id, object_id, object_type) SELECT tag.id AS tag_id, saved_query.id AS object_id, 'query' AS object_type FROM saved_query JOIN tag ON tag.name = 'type:query'; LEFT OUTER JOIN tagged_object ON tagged_object.tag_id = tag.id AND tagged_object.object_id = saved_query.id AND tagged_object.object_type = 'query' WHERE tagged_object.tag_id IS NULL; """ tag = metadata.tables["tag"] tagged_object = metadata.tables["tagged_object"] slices = metadata.tables["slices"] dashboards = metadata.tables["dashboards"] saved_query = metadata.tables["saved_query"] columns = ["tag_id", "object_id", "object_type"] # add a tag for each object type insert = tag.insert() for type_ in ObjectTypes.__members__: # pylint: disable=not-an-iterable try: engine.execute(insert, name=f"type:{type_}", type=TagTypes.type) except IntegrityError: pass # already exists charts = (select([ tag.c.id.label("tag_id"), slices.c.id.label("object_id"), literal(ObjectTypes.chart.name).label("object_type"), ]).select_from( join( join(slices, tag, tag.c.name == "type:chart"), tagged_object, and_( tagged_object.c.tag_id == tag.c.id, tagged_object.c.object_id == slices.c.id, tagged_object.c.object_type == "chart", ), isouter=True, full=False, )).where(tagged_object.c.tag_id.is_(None))) query = tagged_object.insert().from_select(columns, charts) engine.execute(query) dashboards = (select([ tag.c.id.label("tag_id"), dashboards.c.id.label("object_id"), literal(ObjectTypes.dashboard.name).label("object_type"), ]).select_from( join( join(dashboards, tag, tag.c.name == "type:dashboard"), tagged_object, and_( tagged_object.c.tag_id == tag.c.id, tagged_object.c.object_id == dashboards.c.id, tagged_object.c.object_type == "dashboard", ), isouter=True, full=False, )).where(tagged_object.c.tag_id.is_(None))) query = tagged_object.insert().from_select(columns, dashboards) engine.execute(query) saved_queries = (select([ tag.c.id.label("tag_id"), saved_query.c.id.label("object_id"), literal(ObjectTypes.query.name).label("object_type"), ]).select_from( join( join(saved_query, tag, tag.c.name == "type:query"), tagged_object, and_( tagged_object.c.tag_id == tag.c.id, tagged_object.c.object_id == saved_query.c.id, tagged_object.c.object_type == "query", ), isouter=True, full=False, )).where(tagged_object.c.tag_id.is_(None))) query = tagged_object.insert().from_select(columns, saved_queries) engine.execute(query)
def test_in_16(self): self.assert_compile(self.table1.c.myid.in_([literal('a'), self.table1.c.myid]), "mytable.myid IN (:param_1, mytable.myid)")
def test_in_9(self): self.assert_compile(self.table1.c.myid.in_([literal(1) + 'a']), "mytable.myid IN (:param_1 + :param_2)")
def test_in_12(self): self.assert_compile(self.table1.c.myid.in_([1, literal(3) + 4]), "mytable.myid IN (:myid_1, :param_1 + :param_2)")
def common_path(request, db, where): dd = db.metadata.tables["device_data"] devices = db.metadata.tables["devices"] legs = db.metadata.tables["leg_modes"] users = db.metadata.tables["users"] # get data for specified date, or last 12h if unspecified date = request.args.get("date") # passed on to simplify_geometry maxpts = int(request.args.get("maxpts") or 0) mindist = int(request.args.get("mindist") or 0) # Exclude given comma-separated modes in processed path of path, stops by # default. Blank argument removes excludes exarg = request.args.get("exclude") exclude = True if exarg == "" else not_( legs.c.mode.in_((exarg or "STILL").split(","))) if date: start = datetime.strptime(date, '%Y-%m-%d').replace(hour=0, minute=0, second=0, microsecond=0) else: start = datetime.now() - timedelta(hours=12) end = start + timedelta(hours=24) # in the export link case, we get a date range firstday = request.args.get("firstday") firstday = firstday and datetime.strptime(firstday, '%Y-%m-%d') firstday = firstday or datetime.now() lastday = request.args.get("lastday") lastday = lastday and datetime.strptime(lastday, '%Y-%m-%d') lastday = lastday or firstday date_start = firstday.replace(hour=0, minute=0, second=0, microsecond=0) date_end = lastday.replace(hour=0, minute=0, second=0, microsecond=0) \ + timedelta(hours=24) if request.args.get("firstday") or request.args.get("lastday"): start, end = date_start, date_end # find end of user legs legsend = select([func.max(legs.c.time_end).label("time_end")], where, devices.join(users).join(legs)).alias("legsend") # use user legs if available legsed = select( [ func.ST_AsGeoJSON(dd.c.coordinate).label("geojson"), cast(legs.c.mode, String).label("activity"), legs.c.line_name, legs.c.time_start.label("legstart"), cast(legs.c.time_start, String).label("time_start"), cast(legs.c.time_end, String).label("time_end"), legs.c.id, dd.c.time], and_( where, legs.c.activity != None, exclude, dd.c.time >= start, dd.c.time < end), devices \ .join(users) \ .join(legs) \ .join(dd, and_( legs.c.device_id == dd.c.device_id, between(dd.c.time, legs.c.time_start, legs.c.time_end)))) # fall back on raw trace beyond end of user legs unlegsed = select([ func.ST_AsGeoJSON(dd.c.coordinate).label("geojson"), cast(dd.c.activity_1, String).label("activity"), literal(None).label("line_name"), literal(None).label("legstart"), literal(None).label("time_start"), literal(None).label("time_end"), literal(None).label("id"), dd.c.time ], and_( where, dd.c.time >= start, dd.c.time < end, or_(legsend.c.time_end.is_(None), dd.c.time > legsend.c.time_end)), dd.join(devices).join(legsend, literal(True))) # Sort also by leg start time so join point repeats adjacent to correct leg query = legsed.union_all(unlegsed).order_by(text("time, legstart")) query = query.limit(35000) # sanity limit vs date range points = db.engine.execute(query) # re-split into legs, and the raw part segments = (legpts for (legid, legpts) in dict_groups(points, ["legstart"])) features = [] for points in segments: # discard the less credible location points points = trace_discard_sidesteps(points, BAD_LOCATION_RADIUS) # simplify the path geometry by dropping redundant points points = simplify_geometry(points, maxpts=maxpts, mindist=mindist, keep_activity=True) features += trace_linestrings( points, ('id', 'activity', 'line_name', 'time_start', 'time_end')) return jsonify({'type': 'FeatureCollection', 'features': features})
def by_id(self, query, operand, maybe_negate): cond = self.msg_id_column == literal(operand) return query.where(maybe_negate(cond))
def test_commutative_operators(self): self.assert_compile( literal("a") + literal("b") * literal("c"), ":param_1 || :param_2 * :param_3" )
def topic_match_sa(topic_name: str) -> Any: # _sa is short for Sql Alchemy, which we use mostly for # queries that search messages topic_cond = func.upper(column("subject")) == func.upper( literal(topic_name)) return topic_cond
Column('current_replication_sequence', Integer), Column('last_replication_date', DateTime(timezone=True)), ) replication_control = create_replication_control_table('replication_control') acoustid_mb_replication_control = create_replication_control_table('acoustid_mb_replication_control') account = Table('account', metadata, Column('id', Integer, primary_key=True), Column('name', String, nullable=False), Column('apikey', String, nullable=False), Column('mbuser', String), Column('anonymous', Boolean, default=False, server_default=sql.false()), Column('created', DateTime(timezone=True), server_default=sql.func.current_timestamp(), nullable=False), Column('lastlogin', DateTime(timezone=True)), Column('submission_count', Integer, nullable=False, server_default=sql.literal(0)), Column('application_id', Integer, ForeignKey('application.id')), Column('application_version', String), Column('created_from', INET), Column('is_admin', Boolean, default=False, server_default=sql.false(), nullable=False), Index('account_idx_mbuser', 'mbuser', unique=True), Index('account_idx_apikey', 'apikey', unique=True), ) account_stats_control = Table('account_stats_control', metadata, Column('id', Integer, primary_key=True), Column('last_updated', DateTime(timezone=True), nullable=False), ) account_openid = Table('account_openid', metadata, Column('openid', String, primary_key=True),
def test_in_11(self): self.assert_compile(self.table1.c.myid.in_([literal('a') + \ literal('a'), literal('b')]), "mytable.myid IN (:param_1 || :param_2, :param_3)")
def test_in_18(self): self.assert_compile(self.table1.c.myid.in_([literal(1), 'a' + \ self.table1.c.myid]), "mytable.myid IN (:param_1, :myid_1 + mytable.myid)")
def test_in_10(self): self.assert_compile(self.table1.c.myid.in_([literal('a') + 'a', 'b']), "mytable.myid IN (:param_1 || :param_2, :myid_1)")
def by_id(self, query, operand, maybe_negate): # type: (Query, str, ConditionTransform) -> Query cond = self.msg_id_column == literal(operand) return query.where(maybe_negate(cond))
def by_topic(self, query, operand, maybe_negate): # type: (Query, str, ConditionTransform) -> Query if self.user_profile.realm.is_zephyr_mirror_realm: # MIT users expect narrowing to topic "foo" to also show messages to /^foo(.d)*$/ # (foo, foo.d, foo.d.d, etc) m = re.search(r'^(.*?)(?:\.d)*$', operand, re.IGNORECASE) # Since the regex has a `.*` in it, this will always match assert(m is not None) base_topic = m.group(1) # Additionally, MIT users expect the empty instance and # instance "personal" to be the same. if base_topic in ('', 'personal', '(instance "")'): cond = or_( func.upper(column("subject")) == func.upper(literal("")), func.upper(column("subject")) == func.upper(literal(".d")), func.upper(column("subject")) == func.upper(literal(".d.d")), func.upper(column("subject")) == func.upper(literal(".d.d.d")), func.upper(column("subject")) == func.upper(literal(".d.d.d.d")), func.upper(column("subject")) == func.upper(literal("personal")), func.upper(column("subject")) == func.upper(literal("personal.d")), func.upper(column("subject")) == func.upper(literal("personal.d.d")), func.upper(column("subject")) == func.upper(literal("personal.d.d.d")), func.upper(column("subject")) == func.upper(literal("personal.d.d.d.d")), func.upper(column("subject")) == func.upper(literal('(instance "")')), func.upper(column("subject")) == func.upper(literal('(instance "").d')), func.upper(column("subject")) == func.upper(literal('(instance "").d.d')), func.upper(column("subject")) == func.upper(literal('(instance "").d.d.d')), func.upper(column("subject")) == func.upper(literal('(instance "").d.d.d.d')), ) else: # We limit `.d` counts, since postgres has much better # query planning for this than they do for a regular # expression (which would sometimes table scan). cond = or_( func.upper(column("subject")) == func.upper(literal(base_topic)), func.upper(column("subject")) == func.upper(literal(base_topic + ".d")), func.upper(column("subject")) == func.upper(literal(base_topic + ".d.d")), func.upper(column("subject")) == func.upper(literal(base_topic + ".d.d.d")), func.upper(column("subject")) == func.upper(literal(base_topic + ".d.d.d.d")), ) return query.where(maybe_negate(cond)) cond = func.upper(column("subject")) == func.upper(literal(operand)) return query.where(maybe_negate(cond))
def add_owners(engine: Engine, metadata: Metadata) -> None: """ Tag every object according to its owner: INSERT INTO tagged_object (tag_id, object_id, object_type) SELECT tag.id AS tag_id, slices.id AS object_id, 'chart' AS object_type FROM slices JOIN tag ON tag.name = CONCAT('owner:', slices.created_by_fk) LEFT OUTER JOIN tagged_object ON tagged_object.tag_id = tag.id AND tagged_object.object_id = slices.id AND tagged_object.object_type = 'chart' WHERE tagged_object.tag_id IS NULL; SELECT tag.id AS tag_id, dashboards.id AS object_id, 'dashboard' AS object_type FROM dashboards JOIN tag ON tag.name = CONCAT('owner:', dashboards.created_by_fk) LEFT OUTER JOIN tagged_object ON tagged_object.tag_id = tag.id AND tagged_object.object_id = dashboards.id AND tagged_object.object_type = 'dashboard' WHERE tagged_object.tag_id IS NULL; SELECT tag.id AS tag_id, saved_query.id AS object_id, 'query' AS object_type FROM saved_query JOIN tag ON tag.name = CONCAT('owner:', saved_query.created_by_fk) LEFT OUTER JOIN tagged_object ON tagged_object.tag_id = tag.id AND tagged_object.object_id = saved_query.id AND tagged_object.object_type = 'query' WHERE tagged_object.tag_id IS NULL; """ tag = metadata.tables["tag"] tagged_object = metadata.tables["tagged_object"] users = metadata.tables["ab_user"] slices = metadata.tables["slices"] dashboards = metadata.tables["dashboards"] saved_query = metadata.tables["saved_query"] columns = ["tag_id", "object_id", "object_type"] # create a custom tag for each user ids = select([users.c.id]) insert = tag.insert() for (id_, ) in engine.execute(ids): try: engine.execute(insert, name=f"owner:{id_}", type=TagTypes.owner) except IntegrityError: pass # already exists charts = (select([ tag.c.id.label("tag_id"), slices.c.id.label("object_id"), literal(ObjectTypes.chart.name).label("object_type"), ]).select_from( join( join( slices, tag, tag.c.name == functions.concat("owner:", slices.c.created_by_fk), ), tagged_object, and_( tagged_object.c.tag_id == tag.c.id, tagged_object.c.object_id == slices.c.id, tagged_object.c.object_type == "chart", ), isouter=True, full=False, )).where(tagged_object.c.tag_id.is_(None))) query = tagged_object.insert().from_select(columns, charts) engine.execute(query) dashboards = (select([ tag.c.id.label("tag_id"), dashboards.c.id.label("object_id"), literal(ObjectTypes.dashboard.name).label("object_type"), ]).select_from( join( join( dashboards, tag, tag.c.name == functions.concat("owner:", dashboards.c.created_by_fk), ), tagged_object, and_( tagged_object.c.tag_id == tag.c.id, tagged_object.c.object_id == dashboards.c.id, tagged_object.c.object_type == "dashboard", ), isouter=True, full=False, )).where(tagged_object.c.tag_id.is_(None))) query = tagged_object.insert().from_select(columns, dashboards) engine.execute(query) saved_queries = (select([ tag.c.id.label("tag_id"), saved_query.c.id.label("object_id"), literal(ObjectTypes.query.name).label("object_type"), ]).select_from( join( join( saved_query, tag, tag.c.name == functions.concat("owner:", saved_query.c.created_by_fk), ), tagged_object, and_( tagged_object.c.tag_id == tag.c.id, tagged_object.c.object_id == saved_query.c.id, tagged_object.c.object_type == "query", ), isouter=True, full=False, )).where(tagged_object.c.tag_id.is_(None))) query = tagged_object.insert().from_select(columns, saved_queries) engine.execute(query)
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 test_in_8(self): self.assert_compile(self.table1.c.myid.in_(['a', literal('b')]), "mytable.myid IN (:myid_1, :param_1)")
def eval_integer(self): return literal(self.expr.value)
def test_in_17(self): self.assert_compile(self.table1.c.myid.in_([literal('a'), \ self.table1.c.myid + 'a']), "mytable.myid IN (:param_1, mytable.myid + :myid_1)")
def eval_float(self): return literal(self.expr.value)
def eval_string(self): return literal(self.expr.value)
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
'account', metadata, Column('id', Integer, primary_key=True), Column('name', String, nullable=False), Column('apikey', String, nullable=False), Column('mbuser', String), Column('anonymous', Boolean, default=False, server_default=sql.false()), Column('created', DateTime(timezone=True), server_default=sql.func.current_timestamp(), nullable=False), Column('lastlogin', DateTime(timezone=True)), Column('submission_count', Integer, nullable=False, server_default=sql.literal(0)), Column('application_id', Integer, ForeignKey('application.id')), Column('application_version', String), Column('created_from', INET), Column('is_admin', Boolean, default=False, server_default=sql.false(), nullable=False), Index('account_idx_mbuser', 'mbuser', unique=True), Index('account_idx_apikey', 'apikey', unique=True), ) account_stats_control = Table( 'account_stats_control', metadata,
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
def get_old_messages_backend(request, user_profile, anchor=REQ(converter=int), num_before=REQ(converter=to_non_negative_int), num_after=REQ(converter=to_non_negative_int), narrow=REQ('narrow', converter=narrow_parameter, default=None), use_first_unread_anchor=REQ( default=False, converter=ujson.loads), apply_markdown=REQ(default=True, converter=ujson.loads)): # type: (HttpRequest, UserProfile, int, int, int, Optional[List[Dict[str, Any]]], bool, bool) -> HttpResponse include_history = ok_to_include_history(narrow, user_profile.realm) if include_history and not use_first_unread_anchor: query = select([column("id").label("message_id")], None, "zerver_message") inner_msg_id_col = literal_column("zerver_message.id") elif narrow is None: query = select( [column("message_id"), column("flags")], column("user_profile_id") == literal(user_profile.id), "zerver_usermessage") inner_msg_id_col = column("message_id") else: # TODO: Don't do this join if we're not doing a search query = select( [column("message_id"), column("flags")], column("user_profile_id") == literal(user_profile.id), join( "zerver_usermessage", "zerver_message", literal_column("zerver_usermessage.message_id") == literal_column("zerver_message.id"))) inner_msg_id_col = column("message_id") num_extra_messages = 1 is_search = False if narrow is not None: # Add some metadata to our logging data for narrows verbose_operators = [] for term in narrow: if term['operator'] == "is": verbose_operators.append("is:" + term['operand']) else: verbose_operators.append(term['operator']) request._log_data['extra'] = "[%s]" % (",".join(verbose_operators), ) # Build the query for the narrow num_extra_messages = 0 builder = NarrowBuilder(user_profile, inner_msg_id_col) search_term = None # type: Optional[Dict[str, Any]] for term in narrow: if term['operator'] == 'search': if not is_search: search_term = term query = query.column("subject").column("rendered_content") is_search = True else: # Join the search operators if there are multiple of them search_term['operand'] += ' ' + term['operand'] else: query = builder.add_term(query, term) if is_search: query = builder.add_term(query, search_term) # We add 1 to the number of messages requested if no narrow was # specified to ensure that the resulting list always contains the # anchor message. If a narrow was specified, the anchor message # might not match the narrow anyway. if num_after != 0: num_after += num_extra_messages else: num_before += num_extra_messages sa_conn = get_sqlalchemy_connection() if use_first_unread_anchor: condition = column("flags").op("&")(UserMessage.flags.read.mask) == 0 # We exclude messages on muted topics when finding the first unread # message in this narrow muting_conditions = exclude_muting_conditions(user_profile, narrow) if muting_conditions: condition = and_(condition, *muting_conditions) first_unread_query = query.where(condition) first_unread_query = first_unread_query.order_by( inner_msg_id_col.asc()).limit(1) first_unread_result = list( sa_conn.execute(first_unread_query).fetchall()) if len(first_unread_result) > 0: anchor = first_unread_result[0][0] else: anchor = 10000000000000000 before_query = None after_query = None if num_before != 0: before_anchor = anchor if num_after != 0: # Don't include the anchor in both the before query and the after query before_anchor = anchor - 1 before_query = query.where(inner_msg_id_col <= before_anchor) \ .order_by(inner_msg_id_col.desc()).limit(num_before) if num_after != 0: after_query = query.where(inner_msg_id_col >= anchor) \ .order_by(inner_msg_id_col.asc()).limit(num_after) if num_before == 0 and num_after == 0: # This can happen when a narrow is specified. after_query = query.where(inner_msg_id_col == anchor) if before_query is not None: if after_query is not None: query = union_all(before_query.self_group(), after_query.self_group()) else: query = before_query else: query = after_query main_query = alias(query) query = select(main_query.c, None, main_query).order_by(column("message_id").asc()) # This is a hack to tag the query we use for testing query = query.prefix_with("/* get_old_messages */") query_result = list(sa_conn.execute(query).fetchall()) # The following is a little messy, but ensures that the code paths # are similar regardless of the value of include_history. The # 'user_messages' dictionary maps each message to the user's # UserMessage object for that message, which we will attach to the # rendered message dict before returning it. We attempt to # bulk-fetch rendered message dicts from remote cache using the # 'messages' list. search_fields = dict() # type: Dict[int, Dict[str, Text]] message_ids = [] # type: List[int] user_message_flags = {} # type: Dict[int, List[str]] if include_history: message_ids = [row[0] for row in query_result] # TODO: This could be done with an outer join instead of two queries user_message_flags = dict( (user_message.message_id, user_message.flags_list()) for user_message in UserMessage.objects.filter( user_profile=user_profile, message__id__in=message_ids)) for row in query_result: message_id = row[0] if user_message_flags.get(message_id) is None: user_message_flags[message_id] = ["read", "historical"] if is_search: (_, subject, rendered_content, content_matches, subject_matches) = row search_fields[message_id] = get_search_fields( rendered_content, subject, content_matches, subject_matches) else: for row in query_result: message_id = row[0] flags = row[1] user_message_flags[message_id] = parse_usermessage_flags(flags) message_ids.append(message_id) if is_search: (_, _, subject, rendered_content, content_matches, subject_matches) = row search_fields[message_id] = get_search_fields( rendered_content, subject, content_matches, subject_matches) cache_transformer = lambda row: MessageDict.build_dict_from_raw_db_row( row, apply_markdown) id_fetcher = lambda row: row['id'] message_dicts = generic_bulk_cached_fetch( lambda message_id: to_dict_cache_key_id(message_id, apply_markdown), Message.get_raw_db_rows, message_ids, id_fetcher=id_fetcher, cache_transformer=cache_transformer, extractor=extract_message_dict, setter=stringify_message_dict) message_list = [] for message_id in message_ids: msg_dict = message_dicts[message_id] msg_dict.update({"flags": user_message_flags[message_id]}) msg_dict.update(search_fields.get(message_id, {})) message_list.append(msg_dict) statsd.incr('loaded_old_messages', len(message_list)) ret = {'messages': message_list, "result": "success", "msg": ""} return json_success(ret)
def visitStringLiteral(self, value, node): # Docstring inherited from TreeVisitor.visitStringLiteral return literal(value)
def test_in_7(self): self.assert_compile( self.table1.c.myid.in_([literal('a'), literal('b')]), "mytable.myid IN (:param_1, :param_2)")