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)")
