def add_score(self, mod, mem, full, score):
# Update the cache.
s = str(mod) + str(mem)
if full:
s += ':full'
score_hash = self.get_hash(s)
self.scores[score_hash] = score
# Update the database.
mod_id = self._get_model_id(mod)
stmt = scores_table.insert().from_select([
scores_table.c.model_id,
scores_table.c.score_hash,
scores_table.c.score,
], select([
literal(mod_id),
literal(score_hash),
literal(score),
]).where(
~exists([scores_table.c.score]).where(
scores_table.c.score_hash == score_hash
)
)
)
self._execute(stmt)
return True
def test_strict_binds(self):
"""test the 'strict' compiler binds."""
from sqlalchemy.dialects.mssql.base import MSSQLStrictCompiler
mxodbc_dialect = mxodbc.dialect()
mxodbc_dialect.statement_compiler = MSSQLStrictCompiler
t = table('sometable', column('foo'))
for expr, compile in [
(
select([literal("x"), literal("y")]),
"SELECT 'x' AS anon_1, 'y' AS anon_2",
),
(
select([t]).where(t.c.foo.in_(['x', 'y', 'z'])),
"SELECT sometable.foo FROM sometable WHERE sometable.foo "
"IN ('x', 'y', 'z')",
),
(
t.c.foo.in_([None]),
"sometable.foo IN (NULL)"
)
]:
self.assert_compile(expr, compile, dialect=mxodbc_dialect)
def add_cacti_result(self, name, access_time, cycle_time, area):
"""Add a CACTI result."""
# Insert into the local cache.
name_hash = self.get_hash(name)
self.cacti_results[name_hash] = (access_time, cycle_time, area)
# Insert into the database.
stmt = cacti_results_table.insert().from_select([
cacti_results_table.c.name_hash,
cacti_results_table.c.name,
cacti_results_table.c.area,
cacti_results_table.c.access_time,
cacti_results_table.c.cycle_time,
], select([
literal(name_hash),
literal(str(name)),
literal(area),
literal(access_time),
literal(cycle_time),
]).where(
~exists([cacti_results_table.c.name_hash]).where(
cacti_results_table.c.name_hash == name_hash
)
)
)
self._execute(stmt)
return True
def find_matching_subscriptions(s3_path):
subscription_daos = SubscriptionDao.query\
.join(DatasetDao, DatasetDao.id == SubscriptionDao.data['dataset_id'].astext)\
.filter(SubscriptionDao.data['state'].astext == SubscriptionState.ACTIVE)\
.filter(
or_(
SubscriptionDao.data['s3_path_start_prefix_inclusive'].astext <= s3_path,
SubscriptionDao.data['s3_path_start_prefix_inclusive'] == 'null',
not_(SubscriptionDao.data.has_key('s3_path_start_prefix_inclusive')),
).self_group()
)\
.filter(
or_(
SubscriptionDao.data['s3_path_end_prefix_exclusive'].astext > s3_path,
SubscriptionDao.data['s3_path_end_prefix_exclusive'] == 'null',
not_(SubscriptionDao.data.has_key('s3_path_end_prefix_exclusive')),
).self_group()
)\
.filter(
or_(
literal(s3_path).op('~')(cast(SubscriptionDao.data['s3_path_regex_filter'].astext, String)),
SubscriptionDao.data['s3_path_regex_filter'] == 'null',
not_(SubscriptionDao.data.has_key('s3_path_regex_filter')),
).self_group()
)\
.filter(literal(s3_path).like(DatasetDao.data['location'].astext + '%'))\
.all()
return [s.to_model() for s in subscription_daos]
def test_update_ordereddict(self):
table1 = self.tables.mytable
# Confirm that ordered dicts are treated as normal dicts,
# columns sorted in table order
values = util.OrderedDict(
(
(table1.c.name, table1.c.name + "lala"),
(table1.c.myid, func.do_stuff(table1.c.myid, literal("hoho"))),
)
)
self.assert_compile(
update(
table1,
(table1.c.myid == func.hoho(4))
& (
table1.c.name
== literal("foo") + table1.c.name + literal("lala")
),
values=values,
),
"UPDATE mytable "
"SET "
"myid=do_stuff(mytable.myid, :param_1), "
"name=(mytable.name || :name_1) "
"WHERE "
"mytable.myid = hoho(:hoho_1) AND "
"mytable.name = :param_2 || mytable.name || :param_3",
)
def _get_trace_id(self, trace):
# Get the hash.
trace_hash = self.get_hash(trace)
# Check the database.
stmt = select([traces_table.c.id]).where(
traces_table.c.trace_hash == trace_hash
)
row = self._execute(stmt).first()
if row:
return row.id
# Attempt to insert a new trace.
compressed = zlib.compress(str(trace))
stmt = traces_table.insert().from_select([
traces_table.c.trace_hash,
traces_table.c.data,
], select([
literal(trace_hash),
literal(compressed, type_=Binary),
]).where(
~exists([traces_table.c.id]).where(
traces_table.c.trace_hash == trace_hash
)
))
self._execute(stmt)
return self._get_trace_id(trace)
def _get_objects_user_assigned():
"""Objects for which the user is assigned."""
dst_assignee_query = db.session.query(
Relationship.destination_id.label('id'),
Relationship.destination_type.label('type'),
literal(None).label('context_id'),
).filter(
and_(
Relationship.source_type == "Person",
Relationship.source_id == contact_id,
Relationship.destination_type.in_(model_names)
),
)
src_assignee_query = db.session.query(
Relationship.source_id.label('id'),
Relationship.source_type.label('type'),
literal(None).label('context_id'),
).filter(
and_(
Relationship.destination_type == "Person",
Relationship.destination_id == contact_id,
Relationship.source_type.in_(model_names)
),
)
return dst_assignee_query.union(src_assignee_query)
def _get_tasks_in_cycle(model):
"""Filter tasks with particular statuses and cycle.
Filtering tasks with statuses "Assigned", "InProgress" and "Finished".
Where the task is in current users cycle.
"""
task_query = db.session.query(
model.id.label('id'),
literal(model.__name__).label('type'),
literal(None).label('context_id'),
).join(
Cycle,
Cycle.id == model.cycle_id
).filter(
Cycle.is_current == true(),
model.contact_id == contact_id
)
return task_query.filter(
Cycle.is_verification_needed == true(),
model.status.in_([
all_models.CycleTaskGroupObjectTask.ASSIGNED,
all_models.CycleTaskGroupObjectTask.IN_PROGRESS,
all_models.CycleTaskGroupObjectTask.FINISHED,
all_models.CycleTaskGroupObjectTask.DECLINED,
])
).union_all(
task_query.filter(
Cycle.is_verification_needed == false(),
model.status.in_([
all_models.CycleTaskGroupObjectTask.ASSIGNED,
all_models.CycleTaskGroupObjectTask.IN_PROGRESS,
])
)
)
def _get_memory_id(self, mem):
# Check the local cache.
mem_hash = self.get_hash(mem)
if mem_hash in self.memories:
return self.memories[mem_hash]
# Attempt to insert a new memory.
# This is the expected case.
stmt = memories_table.insert().from_select([
memories_table.c.name_hash,
memories_table.c.name,
], select([
literal(mem_hash),
literal(mem),
]).where(
~exists([memories_table.c.id]).where(
memories_table.c.name_hash == mem_hash
)
)
)
self._execute(stmt)
# Check the database.
stmt = select([memories_table.c.id]).where(
memories_table.c.name_hash == mem_hash
)
row = self._execute(stmt).first()
ident = row.id
self.memories[mem_hash] = ident
return ident
def test_match_compile_kw(self):
expr = literal('x').match(literal('y'))
self.assert_compile(
expr,
"MATCH ('x') AGAINST ('y' IN BOOLEAN MODE)",
literal_binds=True
)
def test_update_11(self):
table1 = self.tables.mytable
values = {
table1.c.name: table1.c.name + "lala",
table1.c.myid: func.do_stuff(table1.c.myid, literal("hoho")),
}
self.assert_compile(
update(
table1,
(table1.c.myid == func.hoho(4))
& (
table1.c.name
== literal("foo") + table1.c.name + literal("lala")
),
values=values,
),
"UPDATE mytable "
"SET "
"myid=do_stuff(mytable.myid, :param_1), "
"name=(mytable.name || :name_1) "
"WHERE "
"mytable.myid = hoho(:hoho_1) AND "
"mytable.name = :param_2 || mytable.name || :param_3",
)
def parse_clause(clause, compiler, translate_string=None):
"""This method is used to translate a clause element (molecules,
functions, ..).
According to the type of the clause, a conversion to the database
molecule type is added or the column clause (column name) or the cascaded
clause element is returned.
"""
from razi.chemtypes import Molecule
from razi.expression import MoleculeElement, TxtMoleculeElement
from razi.expression import PersistentMoleculeElement
from razi.expression import QMoleculeElement, TxtQMoleculeElement
if hasattr(clause, '__clause_element__'):
# for example a column name
return clause.__clause_element__()
elif isinstance(clause, ClauseElement):
# for cascaded clause elements, like other functions
return clause
elif isinstance(clause, MoleculeElement):
if isinstance(clause, TxtMoleculeElement):
return clause
elif isinstance(clause, PersistentMoleculeElement):
return literal(clause.desc, Molecule)
raise TypeError
elif isinstance(clause, QMoleculeElement):
if isinstance(clause, TxtQMoleculeElement):
return clause
raise TypeError
elif isinstance(clause, basestring) and translate_string:
return translate_string(clause)
# for raw parameters
return literal(clause)
def _get_context_relationships():
"""Load list of objects related on contexts and objects types.
This code handles the case when user is added as `Auditor` and should be
able to see objects mapped to the `Program` on `My Work` page.
Returns:
objects (list((id, type, None))): Related objects
"""
user_role_query = db.session.query(UserRole.context_id).join(
Role, UserRole.role_id == Role.id).filter(and_(
UserRole.person_id == contact_id, Role.name == 'Auditor')
)
_ct = aliased(all_models.Context, name="c")
_rl = aliased(all_models.Relationship, name="rl")
context_query = db.session.query(
_rl.source_id.label('id'),
_rl.source_type.label('type'),
literal(None)).join(_ct, and_(
_ct.id.in_(user_role_query),
_rl.destination_id == _ct.related_object_id,
_rl.destination_type == _ct.related_object_type,
_rl.source_type.in_(model_names),
)).union(db.session.query(
_rl.destination_id.label('id'),
_rl.destination_type.label('type'),
literal(None)).join(_ct, and_(
_ct.id.in_(user_role_query),
_rl.source_id == _ct.related_object_id,
_rl.source_type == _ct.related_object_type,
_rl.destination_type.in_(model_names),)))
return context_query
def test_concat_compile_kw(self):
expr = literal('x', type_=String) + literal('y', type_=String)
self.assert_compile(
expr,
"concat('x', 'y')",
literal_binds=True
)
def get_next_to_translate(self, session):
""" Use the milestones and priority to find the next description to translate """
# This is the query we want:
# select description_id from (
# select description_id, 50 as score from languages_tb join description_milestone_tb on (milestone_high=milestone) where language = 'nl'
# union all
# select description_id, 30 from languages_tb join description_milestone_tb on (milestone_medium=milestone) where language = 'nl'
# union all
# select description_id, 10 from languages_tb join description_milestone_tb on (milestone_low=milestone) where language = 'nl'
# union all
# select description_id, prioritize from description_tb
# ) x
# where not exists (select 1 from translation_tb where translation_tb.description_id = x.description_id)
# group by description_id order by sum(score) desc
# limit 1;
lang_cte = session.query(Languages).filter_by(language=self.language).cte("language")
prio = session.query(Description.description_id, Description.prioritize)
high = session.query(DescriptionMilestone.description_id, literal(50).label("prioritize")).join(lang_cte, lang_cte.c.milestone_high==DescriptionMilestone.milestone)
medium = session.query(DescriptionMilestone.description_id, literal(30).label("prioritize")).join(lang_cte, lang_cte.c.milestone_medium==DescriptionMilestone.milestone)
low = session.query(DescriptionMilestone.description_id, literal(10).label("prioritize")).join(lang_cte, lang_cte.c.milestone_low==DescriptionMilestone.milestone)
prio_cte = union_all(prio, high, medium, low).cte()
q = session.query(prio_cte.c.description_tb_description_id). \
filter(~exists([1], Translation.description_id == prio_cte.c.description_tb_description_id)). \
filter(~exists([1], PendingTranslation.description_id == prio_cte.c.description_tb_description_id)). \
group_by(prio_cte.c.description_tb_description_id). \
order_by(func.sum(prio_cte.c.description_tb_prioritize).desc())
row = q.first()
if row:
return row[0]
def permissions(cls, instance, db_session=None):
""" returns all non-resource permissions based on what groups user
belongs and directly set ones for this user"""
db_session = get_db_session(db_session, instance)
query = db_session.query(
cls.models_proxy.GroupPermission.group_id.label('owner_id'),
cls.models_proxy.GroupPermission.perm_name.label('perm_name'),
sa.literal('group').label('type'))
query = query.filter(cls.models_proxy.GroupPermission.group_id ==
cls.models_proxy.UserGroup.group_id)
query = query.filter(cls.models_proxy.User.id == cls.models_proxy.UserGroup.user_id)
query = query.filter(cls.models_proxy.User.id == instance.id)
query2 = db_session.query(
cls.models_proxy.UserPermission.user_id.label('owner_id'),
cls.models_proxy.UserPermission.perm_name.label('perm_name'),
sa.literal('user').label('type'))
query2 = query2.filter(cls.models_proxy.UserPermission.user_id == instance.id)
query = query.union(query2)
groups_dict = dict([(g.id, g) for g in instance.groups])
return [PermissionTuple(instance,
row.perm_name,
row.type,
groups_dict.get(
row.owner_id) if row.type == 'group' else None,
None, False, True) for row in query]
def parse_clause(clause, compiler):
"""This method is used to translate a clause element (geometries, functions, ..).
According to the type of the clause, a conversion to the database geometry type is added or
the column clause (column name) or the cascaded clause element is returned.
"""
from geoalchemy.base import SpatialElement, WKTSpatialElement, WKBSpatialElement, DBSpatialElement, GeometryBase
if hasattr(clause, '__clause_element__'):
# for example a column name
return clause.__clause_element__()
elif isinstance(clause, ClauseElement):
# for cascaded clause elements, like other functions
return clause
elif isinstance(clause, SpatialElement):
if isinstance(clause, (WKTSpatialElement, WKBSpatialElement)):
return clause
if isinstance(clause, DBSpatialElement):
return literal(clause.desc, GeometryBase)
return clause.desc
elif isinstance(clause, basestring) and WKT_REGEX.match(clause):
return WKTSpatialElement(clause)
# for raw parameters
return literal(clause)
def test_update_ordered_parameters_2(self):
table1 = self.tables.mytable
# Confirm that we can pass values as list value pairs
# note these are ordered *differently* from table.c
values = [
(table1.c.name, table1.c.name + 'lala'),
('description', 'some desc'),
(table1.c.myid, func.do_stuff(table1.c.myid, literal('hoho')))
]
self.assert_compile(
update(
table1,
(table1.c.myid == func.hoho(4)) & (
table1.c.name == literal('foo') +
table1.c.name +
literal('lala')),
preserve_parameter_order=True).values(values),
'UPDATE mytable '
'SET '
'name=(mytable.name || :name_1), '
'description=:description, '
'myid=do_stuff(mytable.myid, :param_1) '
'WHERE '
'mytable.myid = hoho(:hoho_1) AND '
'mytable.name = :param_2 || mytable.name || :param_3')
def test_update_ordered_parameters_2(self):
table1 = self.tables.mytable
# Confirm that we can pass values as list value pairs
# note these are ordered *differently* from table.c
values = [
(table1.c.name, table1.c.name + "lala"),
("description", "some desc"),
(table1.c.myid, func.do_stuff(table1.c.myid, literal("hoho"))),
]
self.assert_compile(
update(
table1,
(table1.c.myid == func.hoho(4))
& (
table1.c.name
== literal("foo") + table1.c.name + literal("lala")
),
preserve_parameter_order=True,
).values(values),
"UPDATE mytable "
"SET "
"name=(mytable.name || :name_1), "
"description=:description, "
"myid=do_stuff(mytable.myid, :param_1) "
"WHERE "
"mytable.myid = hoho(:hoho_1) AND "
"mytable.name = :param_2 || mytable.name || :param_3",
)
def counts(self, terms, types=None, contact_id=None,
extra_params=None, extra_columns=None):
"""Prepare the search query, but return only count for each of
the requested objects."""
extra_params = extra_params or {}
extra_columns = extra_columns or {}
model_names = self._get_grouped_types(types, extra_params)
query = db.session.query(
self.record_type.type, func.count(distinct(
self.record_type.key)), literal(""))
query = query.filter(self.get_permissions_query(model_names))
query = query.filter(self._get_filter_query(terms))
query = self.search_get_owner_query(query, types, contact_id)
query = query.group_by(self.record_type.type)
all_extra_columns = dict(extra_columns.items() +
[(p, p) for p in extra_params
if p not in extra_columns])
if not all_extra_columns:
return query.all()
# Add extra_params and extra_colums:
for key, value in all_extra_columns.iteritems():
extra_q = db.session.query(self.record_type.type,
func.count(distinct(self.record_type.key)),
literal(key))
extra_q = extra_q.filter(self.get_permissions_query([value]))
extra_q = extra_q.filter(self._get_filter_query(terms))
extra_q = self.search_get_owner_query(extra_q, [value], contact_id)
extra_q = self._add_extra_params_query(extra_q,
value,
extra_params.get(key, None))
extra_q = extra_q.group_by(self.record_type.type)
query = query.union(extra_q)
return query.all()
def _get_people():
"""Get all the people w/o any restrictions."""
all_people = db.session.query(
all_models.Person.id.label('id'),
literal(all_models.Person.__name__).label('type'),
literal(None).label('context_id')
)
return all_people
def perms_for_user(cls, instance, user, db_session=None):
"""
returns all permissions that given user has for this resource
from groups and directly set ones too
:param instance:
:param user:
:param db_session:
:return:
"""
db_session = get_db_session(db_session, instance)
query = db_session.query(
cls.models_proxy.GroupResourcePermission.group_id.label(
'owner_id'),
cls.models_proxy.GroupResourcePermission.perm_name,
sa.literal('group').label('type'))
query = query.filter(
cls.models_proxy.GroupResourcePermission.group_id.in_(
[gr.id for gr in user.groups]
)
)
query = query.filter(
cls.models_proxy.GroupResourcePermission.resource_id ==
instance.resource_id)
query2 = db_session.query(
cls.models_proxy.UserResourcePermission.user_id.label('owner_id'),
cls.models_proxy.UserResourcePermission.perm_name,
sa.literal('user').label('type'))
query2 = query2.filter(
cls.models_proxy.UserResourcePermission.user_id ==
user.id)
query2 = query2.filter(
cls.models_proxy.UserResourcePermission.resource_id ==
instance.resource_id)
query = query.union(query2)
groups_dict = dict([(g.id, g) for g in user.groups])
perms = [PermissionTuple(user,
row.perm_name,
row.type,
groups_dict.get(row.owner_id) if
row.type == 'group' else None,
instance, False, True) for row in query]
# include all perms if user is the owner of this resource
if instance.owner_user_id == user.id:
perms.append(PermissionTuple(user, ALL_PERMISSIONS, 'user',
None, instance, True, True))
groups_dict = dict([(g.id, g) for g in user.groups])
if instance.owner_group_id in groups_dict:
perms.append(PermissionTuple(user, ALL_PERMISSIONS, 'group',
groups_dict.get(
instance.owner_group_id),
instance, True, True))
return perms
def load_packages(db, report_id, package_type):
build_fn = lambda prefix, column: (
db.session.query(ReportPackage.id.label('%sid' % (prefix)),
Package.id.label('%spackage_id' % (prefix)),
Package.name.label('%sname' % (prefix)),
Build.version.label('%sversion' % (prefix)),
Build.release.label('%srelease' % (prefix)),
Build.epoch.label('%sepoch' % (prefix)))
.filter(Build.id == Package.build_id)
.filter(ReportPackage.report_id == report_id)
.filter(Package.id == column)
.filter(ReportPackage.type == package_type)
.subquery())
installed_packages = build_fn("i", ReportPackage.installed_package_id)
running_packages = build_fn("r", ReportPackage.running_package_id)
known_packages = (
db.session.query(ReportPackage.id,
installed_packages.c.ipackage_id,
running_packages.c.rpackage_id,
installed_packages.c.iname,
running_packages.c.rname,
installed_packages.c.iversion,
running_packages.c.rversion,
installed_packages.c.irelease,
running_packages.c.rrelease,
installed_packages.c.iepoch,
running_packages.c.repoch,
ReportPackage.count)
.outerjoin(installed_packages, ReportPackage.id ==
installed_packages.c.iid)
.outerjoin(running_packages, ReportPackage.id ==
running_packages.c.rid)
.filter(ReportPackage.report_id == report_id)
.filter((installed_packages.c.iid != None) |
(running_packages.c.rid != None)))
unknown_packages = (
db.session.query(
ReportUnknownPackage.id,
literal(None).label("ipackage_id"),
literal(None).label("rpackage_id"),
ReportUnknownPackage.name.label("iname"),
ReportUnknownPackage.name.label("rname"),
ReportUnknownPackage.installed_version.label("iversion"),
ReportUnknownPackage.running_version.label("rversion"),
ReportUnknownPackage.installed_release.label("irelease"),
ReportUnknownPackage.running_release.label("rrelease"),
ReportUnknownPackage.installed_epoch.label("iepoch"),
ReportUnknownPackage.running_epoch.label("repoch"),
ReportUnknownPackage.count)
.filter(ReportUnknownPackage.type == package_type)
.filter(ReportUnknownPackage.report_id == report_id))
return known_packages.union(unknown_packages).all()
def test_literal_binds_plain(self):
m = MetaData()
t = Table('t', m, Column(
'x', Integer,
server_default=literal('a') + literal('b'))
)
self.assert_compile(
CreateTable(t),
"CREATE TABLE t (x INTEGER DEFAULT 'a' || 'b')"
)
def _inserted_before(timestamp):
'''To be used inside filter().
'''
if timestamp is None:
# XXX: Returning None does not get optimized (skipped) by SQLAlchemy,
# and returning True does not work in Oracle (generates "and 1"
# which breaks Oracle but not SQLite). For the moment just use
# this dummy condition.
return sqlalchemy.literal(True) == sqlalchemy.literal(True)
return conddb.IOV.insertion_time <= _parse_timestamp(timestamp)
def _create_q(h, l, join_clause):
if h == lends:
cols = [literal('lend').label('form_name'), l.c.quantity, (l.c.quantity * l.c.a_price).label('amount'), l.c.return_quantity, (l.c.return_quantity * l.c.a_price).label('return_amount'), (l.c.quantity - l.c.return_quantity).label('un_return_quantity'), ((l.c.quantity - l.c.return_quantity) * l.c.a_price).label('un_return_amount'), h.c.return_at]
else:
cols = [literal('lend_return').label('form_name'), null().label('quantity'), null().label('amount'), l.c.quantity.label('return_quantity'), (l.c.quantity * l.c.a_price).label('return_amount'), null().label('un_return_quantity'), null().label('un_return_amount'), null().label('return_at')]
fields = [h.c.memo, l.c.a_price, l.c.id.label('form_line_id'), h.c.id.label('form_id'), h.c.code.label('form_code'), h.c.created_at, h.c.partner_id, h.c.store_id, h.c.employee_id, h.c.department_id, l.c.product_id] + cols
q = select(fields, from_obj=[l.join(h, join_clause).join(stores_, h.c.store_id == stores_.c.id).join(products, l.c.product_id == products.c.id).join(
product_types, products.c.product_type_id == product_types.c.id).join(partners, h.c.partner_id == partners.c.id).join(areas, partners.c.area_id == areas.c.id).join(departments, h.c.department_id == departments.c.id).join(brands, products.c.brand_id == brands.c.id)]).where(h.c.status == 6)
if h == lends:
if not show_zero:
q = q.where((l.c.quantity - l.c.return_quantity) > 0)
return apply_filter(h, l, q)
def _propagate_to_wf_children(new_wf_acls, child_class):
"""Propagate newly added roles to workflow objects.
Args:
wf_new_acl: list of all newly created acl entries for workflows
Returns:
list of newly created acl entries for task groups.
"""
child_table = child_class.__table__
acl_table = all_models.AccessControlList.__table__
acr_table = all_models.AccessControlRole.__table__.alias("parent_acr")
acr_mapped_table = all_models.AccessControlRole.__table__.alias("mapped")
current_user_id = login.get_current_user_id()
select_statement = sa.select([
acl_table.c.person_id,
acr_mapped_table.c.id,
child_table.c.id,
sa.literal(child_class.__name__),
sa.func.now(),
sa.literal(current_user_id),
sa.func.now(),
acl_table.c.id.label("parent_id"),
acl_table.c.id.label("parent_id_nn"),
]).select_from(
sa.join(
sa.join(
sa.join(
child_table,
acl_table,
sa.and_(
acl_table.c.object_id == child_table.c.workflow_id,
acl_table.c.object_type == all_models.Workflow.__name__,
)
),
acr_table,
),
acr_mapped_table,
acr_mapped_table.c.name == sa.func.concat(
acr_table.c.name, " Mapped")
)
).where(
acl_table.c.id.in_(new_wf_acls)
)
acl_utils.insert_select_acls(select_statement)
return _get_child_ids(new_wf_acls, child_class)
def test_seven(self):
self._test(
literal(datetime.timedelta(seconds=10))
- literal(datetime.timedelta(seconds=10)),
"all",
overrides={
"hour": 0,
"minute": 0,
"month": 0,
"year": 0,
"day": 0,
"epoch": 0,
},
)
def _contents_common_query_for_contained(self, history_id):
component_class = self.contained_class
# TODO: and now a join with Dataset - this is getting sad
columns = self._contents_common_columns(component_class,
history_content_type=literal('dataset'),
state=model.Dataset.state,
# do not have inner collections
collection_id=literal(None)
)
subquery = self._session().query(*columns)
# for the HDA's we need to join the Dataset since it has an actual state column
subquery = subquery.join(model.Dataset, model.Dataset.id == component_class.dataset_id)
subquery = subquery.filter(component_class.history_id == history_id)
return subquery
def _create_audit_relationships(self):
"""Create relationships between snapshot objects and audits.
Generally snapshots are related to audits by default, but we also duplicate
this data in relationships table for ACL propagation.
"""
relationships_table = all_models.Relationship.__table__
snapshot_table = all_models.Snapshot.__table__
inserter = relationships_table.insert().prefix_with("IGNORE")
audit_ids = {parent.id for parent in self.parents}
if not audit_ids:
return
old_ids = self._get_audit_relationships(audit_ids)
select_statement = sa.select([
sa.literal(get_current_user_id()),
sa.func.now(),
sa.func.now(),
snapshot_table.c.parent_id,
snapshot_table.c.parent_type,
snapshot_table.c.id,
sa.literal(all_models.Snapshot.__name__),
]).select_from(
snapshot_table
).where(
snapshot_table.c.parent_id.in_(audit_ids)
)
db.session.execute(
inserter.from_select(
[
relationships_table.c.modified_by_id,
relationships_table.c.created_at,
relationships_table.c.updated_at,
relationships_table.c.source_id,
relationships_table.c.source_type,
relationships_table.c.destination_id,
relationships_table.c.destination_type,
],
select_statement
)
)
new_ids = self._get_audit_relationships(audit_ids)
created_ids = new_ids.difference(old_ids)
acl.add_relationships(created_ids)
def _rel_parent(parent_acl_ids=None,
relationship_ids=None,
source=True,
user_id=None):
"""Get left side of relationships mappings through source."""
rel_table = all_models.Relationship.__table__
acl_table = all_models.AccessControlList.__table__
parent_acr = all_models.AccessControlRole.__table__.alias(
"parent_acr_{}".format(source))
child_acr = all_models.AccessControlRole.__table__.alias(
"child_acr_{}".format(source))
# The grandchild is only used to check the child part of the relationship. We
# might want to check if it would be more efficient to store grandchild info
# in the child ACR entry instead of making an extra join on our tables.
grandchild_acr = all_models.AccessControlRole.__table__.alias(
"grandchild_acr_{}".format(source))
where_conditions = []
if relationship_ids is not None:
where_conditions.append(rel_table.c.id.in_(relationship_ids))
if parent_acl_ids:
where_conditions.append(~acl_table.c.id.in_(parent_acl_ids))
elif parent_acl_ids is not None:
where_conditions.append(acl_table.c.id.in_(parent_acl_ids))
if source:
parent_object_id = rel_table.c.source_id
parent_object_type = rel_table.c.source_type
grandchild_object_type = rel_table.c.destination_type
else:
parent_object_id = rel_table.c.destination_id
parent_object_type = rel_table.c.destination_type
grandchild_object_type = rel_table.c.source_type
select_statement = sa.select([
child_acr.c.id.label("ac_role_id"),
rel_table.c.id.label("object_id"),
sa.literal(all_models.Relationship.__name__).label("object_type"),
sa.func.now().label("created_at"),
sa.literal(user_id).label("modified_by_id"),
sa.func.now().label("updated_at"),
acl_table.c.id.label("parent_id"),
acl_table.c.id.label("parent_id_nn"),
acl_table.c.base_id.label("base_id"),
]).select_from(
sa.join(
sa.join(
sa.join(
sa.join(
rel_table, acl_table,
sa.and_(
acl_table.c.object_id == parent_object_id,
acl_table.c.object_type == parent_object_type,
)), parent_acr,
parent_acr.c.id == acl_table.c.ac_role_id),
child_acr,
sa.and_(
child_acr.c.parent_id == parent_acr.c.id,
child_acr.c.object_type ==
all_models.Relationship.__name__,
),
), grandchild_acr,
sa.and_(
grandchild_acr.c.parent_id == child_acr.c.id,
grandchild_acr.c.object_type == grandchild_object_type,
))).where(sa.and_(*where_conditions))
return select_statement
def _do_always_false(self, clause_name, payload):
"""We just ignore the payload."""
from sqlalchemy import literal
return literal(False)
def api_comment(v):
parent_submission = base36decode(request.form.get("submission"))
parent_fullname = request.form.get("parent_fullname")
# get parent item info
parent_id = parent_fullname.split("_")[1]
if parent_fullname.startswith("t2"):
parent_post = get_post(parent_id)
parent = parent_post
parent_comment_id = None
level = 1
parent_submission = base36decode(parent_id)
elif parent_fullname.startswith("t3"):
parent = get_comment(parent_id, v=v)
parent_comment_id = parent.id
level = parent.level + 1
parent_id = parent.parent_submission
parent_submission = parent_id
parent_post = get_post(base36encode(parent_id))
else:
abort(400)
#process and sanitize
body = request.form.get("body", "")[0:10000]
body = body.lstrip().rstrip()
with CustomRenderer(post_id=parent_id) as renderer:
body_md = renderer.render(mistletoe.Document(body))
body_html = sanitize(body_md, linkgen=True)
# Run safety filter
bans = filter_comment_html(body_html)
if bans:
ban = bans[0]
reason = f"Remove the {ban.domain} link from your comment and try again."
if ban.reason:
reason += f" {ban.reason_text}"
return jsonify({"error": reason}), 401
# check existing
existing = g.db.query(Comment).join(CommentAux).filter(
Comment.author_id == v.id, Comment.is_deleted == False,
Comment.parent_comment_id == parent_comment_id,
Comment.parent_submission == parent_submission,
CommentAux.body == body).options(contains_eager(
Comment.comment_aux)).first()
if existing:
return jsonify(
{"error":
f"You already made that comment: {existing.permalink}"}), 409
# No commenting on deleted/removed things
if parent.is_banned or parent.is_deleted:
return jsonify(
{"error":
"You can't comment on things that have been deleted."}), 403
if parent.author.any_block_exists(v):
return jsonify({
"error":
"You can't reply to users who have blocked you, or users you have blocked."
}), 403
# check for archive and ban state
post = get_post(parent_id)
if post.is_archived or not post.board.can_comment(v):
return jsonify({"error": "You can't comment on this."}), 403
# get bot status
is_bot = request.headers.get("X-User-Type", "") == "Bot"
# check spam - this should hopefully be faster
if not is_bot:
now = int(time.time())
cutoff = now - 60 * 60 * 24
similar_comments = g.db.query(Comment).options(lazyload('*')).join(
Comment.comment_aux).filter(
Comment.author_id == v.id,
CommentAux.body.op('<->')(body) <
app.config["SPAM_SIMILARITY_THRESHOLD"],
Comment.created_utc > cutoff).options(
contains_eager(Comment.comment_aux)).all()
threshold = app.config["SPAM_SIMILAR_COUNT_THRESHOLD"]
if v.age >= (60 * 60 * 24 * 30):
threshold *= 4
elif v.age >= (60 * 60 * 24 * 7):
threshold *= 3
elif v.age >= (60 * 60 * 24):
threshold *= 2
if len(similar_comments) > threshold:
text = "Your Ruqqus account has been suspended for 1 day for the following reason:\n\n> Too much spam!"
send_notification(v, text)
v.ban(reason="Spamming.", include_alts=True, days=1)
for comment in similar_comments:
comment.is_banned = True
comment.ban_reason = "Automatic spam removal. This happened because the post's creator submitted too much similar content too quickly."
g.db.add(comment)
g.db.commit()
return jsonify({"error": "Too much spam!"}), 403
badwords = g.db.query(BadWord).all()
if badwords:
for x in badwords:
if x.check(body):
is_offensive = True
break
else:
is_offensive = False
else:
is_offensive = False
# check badlinks
soup = BeautifulSoup(body_html, features="html.parser")
links = [x['href'] for x in soup.find_all('a') if x.get('href')]
for link in links:
parse_link = urlparse(link)
check_url = ParseResult(scheme="https",
netloc=parse_link.netloc,
path=parse_link.path,
params=parse_link.params,
query=parse_link.query,
fragment='')
check_url = urlunparse(check_url)
badlink = g.db.query(BadLink).filter(
literal(check_url).contains(BadLink.link)).first()
if badlink:
return jsonify({
"error":
f"Remove the following link and try again: `{check_url}`. Reason: {badlink.reason_text}"
}), 403
# create comment
c = Comment(author_id=v.id,
parent_submission=parent_submission,
parent_fullname=parent.fullname,
parent_comment_id=parent_comment_id,
level=level,
over_18=post.over_18,
is_nsfl=post.is_nsfl,
is_op=(v.id == post.author_id),
is_offensive=is_offensive,
original_board_id=parent_post.board_id,
is_bot=is_bot)
g.db.add(c)
g.db.flush()
c_aux = CommentAux(id=c.id, body_html=body_html, body=body)
g.db.add(c_aux)
g.db.flush()
notify_users = set()
# queue up notification for parent author
if parent.author.id != v.id:
notify_users.add(parent.author.id)
# queue up notifications for username mentions
soup = BeautifulSoup(body_html, features="html.parser")
mentions = soup.find_all("a", href=re.compile("^/@(\w+)"), limit=3)
for mention in mentions:
username = mention["href"].split("@")[1]
user = g.db.query(User).filter_by(username=username).first()
if user:
if v.any_block_exists(user):
continue
if user.id != v.id:
notify_users.add(user.id)
for x in notify_users:
n = Notification(comment_id=c.id, user_id=x)
g.db.add(n)
# create auto upvote
vote = CommentVote(user_id=v.id, comment_id=c.id, vote_type=1)
g.db.add(vote)
c.post.score_activity = c.post.rank_activity
g.db.add(c.post)
g.db.commit()
# print(f"Content Event: @{v.username} comment {c.base36id}")
return {
"html":
lambda: jsonify({
"html":
render_template("comments.html",
v=v,
comments=[c],
render_replies=False,
is_allowed_to_comment=True)
}),
"api":
lambda: c.json
}
def test_float_coerce_round_trip(self):
expr = 15.7563
val = testing.db.scalar(select([literal(expr)]))
eq_(val, expr)
def test_literal_binds_w_quotes(self):
m = MetaData()
t = Table("t", m, Column("x", Integer,
server_default=literal("5 ' 8")))
self.assert_compile(CreateTable(t),
"""CREATE TABLE t (x INTEGER DEFAULT '5 '' 8')""")
def inner_wrapper(self, column, mostly=None, result_format=None, *args, **kwargs):
if result_format is None:
result_format = self.default_expectation_args["result_format"]
result_format = parse_result_format(result_format)
if result_format['result_format'] == 'COMPLETE':
warnings.warn("Setting result format to COMPLETE for a SqlAlchemyDataset can be dangerous because it will not limit the number of returned results.")
unexpected_count_limit = None
else:
unexpected_count_limit = result_format['partial_unexpected_count']
expected_condition = func(self, column, *args, **kwargs)
# Added to prepare for when an ignore_values argument is added to the expectation
ignore_values = [None]
if func.__name__ in ['expect_column_values_to_not_be_null', 'expect_column_values_to_be_null']:
ignore_values = []
# Counting the number of unexpected values can be expensive when there is a large
# number of np.nan values.
# This only happens on expect_column_values_to_not_be_null expectations.
# Since there is no reason to look for most common unexpected values in this case,
# we will instruct the result formatting method to skip this step.
result_format['partial_unexpected_count'] = 0
ignore_values_conditions = []
if len(ignore_values) > 0 and None not in ignore_values or len(ignore_values) > 1 and None in ignore_values:
ignore_values_conditions += [
sa.column(column).in_([val for val in ignore_values if val is not None])
]
if None in ignore_values:
ignore_values_conditions += [sa.column(column).is_(None)]
if len(ignore_values_conditions) > 1:
ignore_values_condition = sa.or_(*ignore_values_conditions)
elif len(ignore_values_conditions) == 1:
ignore_values_condition = ignore_values_conditions[0]
else:
ignore_values_condition = sa.literal(False)
count_query = sa.select([
sa.func.count().label('element_count'),
sa.func.sum(
sa.case([(ignore_values_condition, 1)], else_=0)
).label('null_count'),
sa.func.sum(
sa.case([
(
sa.and_(
sa.not_(expected_condition),
sa.not_(ignore_values_condition)
),
1
)
], else_=0)
).label('unexpected_count')
]).select_from(self._table)
count_results = dict(self.engine.execute(count_query).fetchone())
# Handle case of empty table gracefully:
if "element_count" not in count_results or count_results["element_count"] is None:
count_results["element_count"] = 0
if "null_count" not in count_results or count_results["null_count"] is None:
count_results["null_count"] = 0
if "unexpected_count" not in count_results or count_results["unexpected_count"] is None:
count_results["unexpected_count"] = 0
# Retrieve unexpected values
unexpected_query_results = self.engine.execute(
sa.select([sa.column(column)]).select_from(self._table).where(
sa.and_(sa.not_(expected_condition),
sa.not_(ignore_values_condition)
)
).limit(unexpected_count_limit)
)
nonnull_count = count_results['element_count'] - \
count_results['null_count']
if "output_strftime_format" in kwargs:
output_strftime_format = kwargs["output_strftime_format"]
maybe_limited_unexpected_list = []
for x in unexpected_query_results.fetchall():
if isinstance(x[column], string_types):
col = parse(x[column])
else:
col = x[column]
maybe_limited_unexpected_list.append(datetime.strftime(col, output_strftime_format))
else:
maybe_limited_unexpected_list = [x[column] for x in unexpected_query_results.fetchall()]
success_count = nonnull_count - count_results['unexpected_count']
success, percent_success = self._calc_map_expectation_success(
success_count, nonnull_count, mostly)
return_obj = self._format_map_output(
result_format,
success,
count_results['element_count'],
nonnull_count,
count_results['unexpected_count'],
maybe_limited_unexpected_list,
None,
)
if func.__name__ in ['expect_column_values_to_not_be_null', 'expect_column_values_to_be_null']:
# These results are unnecessary for the above expectations
del return_obj['result']['unexpected_percent_nonmissing']
del return_obj['result']['missing_count']
del return_obj['result']['missing_percent']
try:
del return_obj['result']['partial_unexpected_counts']
del return_obj['result']['partial_unexpected_list']
except KeyError:
pass
return return_obj
