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