def purge_datasets( app, cutoff_time, remove_from_disk, info_only=False, force_retry=False ):
# Purges deleted datasets whose update_time is older than cutoff_time. Files may or may
# not be removed from disk.
dataset_count = 0
disk_space = 0
start = time.time()
if force_retry:
datasets = app.sa_session.query( app.model.Dataset ) \
.filter( and_( app.model.Dataset.table.c.deleted == true(),
app.model.Dataset.table.c.purgable == true(),
app.model.Dataset.table.c.update_time < cutoff_time ) )
else:
datasets = app.sa_session.query( app.model.Dataset ) \
.filter( and_( app.model.Dataset.table.c.deleted == true(),
app.model.Dataset.table.c.purgable == true(),
app.model.Dataset.table.c.purged == false(),
app.model.Dataset.table.c.update_time < cutoff_time ) )
for dataset in datasets:
file_size = dataset.file_size
_purge_dataset( app, dataset, remove_from_disk, info_only=info_only )
dataset_count += 1
try:
disk_space += file_size
except:
pass
stop = time.time()
print 'Purged %d datasets' % dataset_count
if remove_from_disk:
print 'Freed disk space: ', disk_space
print "Elapsed time: ", stop - start
print "##########################################"
def get_users(
number: int=10,
offset: int=0,
matches_all: Dict[str, Any]={},
matches_any: Dict[str, Any]={},
store: Store=None,
) -> Tuple[List[User], int]:
query = store.session.query(User)
query = query.outerjoin(InstitutionAssociation)
query = query.outerjoin(Institution)
query = query.order_by(User.id.asc())
searches = {
'bio': lambda a: User.bio.ilike('%{}%'.format(a)),
'created_after': lambda a: User.date_created > a,
'created_before': lambda a: User.date_created < a,
'institution': lambda a: Institution.name.ilike('%{}%'.format(a)),
'name': lambda a: User.name.ilike('%{}%'.format(a)),
}
filter_all = true()
filter_any = false()
for name, value in matches_all.items():
filter_all = filter_all & searches.get(name, lambda _: true())(value)
for name, value in matches_any.items():
filter_any = filter_any | searches.get(name, lambda _: false())(value)
query = query.filter(filter_all & filter_any if filter_any is not false() else filter_all)
query = query.distinct()
count = query.count()
query = query.limit(number).offset(offset)
return query, count
def purge_libraries(app, cutoff_time, remove_from_disk, info_only=False, force_retry=False):
# Purges deleted libraries whose update_time is older than the cutoff_time.
# The dataset associations of each library are also marked as deleted.
# The Purge Dataset method will purge each Dataset as necessary
# library.purged == True simply means that it can no longer be undeleted
# i.e. all associated LibraryDatasets/folders are marked as deleted
library_count = 0
start = time.time()
if force_retry:
libraries = app.sa_session.query(app.model.Library) \
.filter(and_(app.model.Library.table.c.deleted == true(),
app.model.Library.table.c.update_time < cutoff_time))
else:
libraries = app.sa_session.query(app.model.Library) \
.filter(and_(app.model.Library.table.c.deleted == true(),
app.model.Library.table.c.purged == false(),
app.model.Library.table.c.update_time < cutoff_time))
for library in libraries:
_purge_folder(library.root_folder, app, remove_from_disk, info_only=info_only)
if not info_only:
print("Purging library id ", library.id)
library.purged = True
app.sa_session.add(library)
app.sa_session.flush()
library_count += 1
stop = time.time()
print('# Purged %d libraries .' % library_count)
print("Elapsed time: ", stop - start)
print("##########################################")
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 stats():
"""Show some devices stats."""
sq_nt = session.query(Device.address) \
.filter(and_(Device.tracked == false(), Device.identified == true())) \
.subquery()
sq_ni = session.query(Device.address) \
.filter(and_(Device.tracked == true(), Device.identified == false())) \
.subquery()
sq_ntni = session.query(Device.address) \
.filter(and_(Device.tracked == false(), Device.identified == false())) \
.subquery()
query = session.query(Device.address_origin, func.count(Device.id), func.count(sq_nt.c.address), func.count(sq_ni.c.address), func.count(sq_ntni.c.address)) \
.outerjoin(sq_nt, sq_nt.c.address == Device.address) \
.outerjoin(sq_ni, sq_ni.c.address == Device.address) \
.outerjoin(sq_ntni, sq_ntni.c.address == Device.address) \
.group_by(Device.address_origin)
print('--- Devices ---')
for [address_origin, device_count, nt_count, ni_count, ntni_count] in query.all():
print('{:12s} Total:{:5d} - not tracked:{:3d}, not identified:{:3d}, not tracked & not identified: {:3d}'
.format(AddressOrigin(address_origin).name,
device_count,
nt_count,
ni_count,
ntni_count))
def test_is_boolean_symbols_despite_no_native(self):
is_(
testing.db.scalar(select([cast(true().is_(true()), Boolean)])),
True,
)
is_(
testing.db.scalar(select([cast(true().isnot(true()), Boolean)])),
False,
)
is_(
testing.db.scalar(select([cast(false().is_(false()), Boolean)])),
True,
)
def invalid_query(self, session, **kw):
return session.query(Languoid)\
.filter_by(active=True, level=LanguoidLevel.family)\
.filter(~Languoid.children.any(and_(
Languoid.active == true(),
Languoid.level.in_([LanguoidLevel.family, LanguoidLevel.language]))))\
.order_by(Languoid.id)
def macroareas(args, languages, stats):
ma_map = get_map(Macroarea)
# we store references to languages to make computation of cumulated macroareas for
# families easier
lang_map = {}
for hid, info in get_lginfo(args, lambda x: x.macro_area):
if hid not in languages:
languages[hid] = Languoid.get(hid, key='hid', default=None)
if not languages[hid]:
continue
lang_map[languages[hid].pk] = languages[hid]
a, r = update_relationship(languages[hid].macroareas, [ma_map[info.macro_area]])
if a or r:
stats.update(['macroarea'])
for family in DBSession.query(Languoid)\
.filter(Languoid.level == LanguoidLevel.family)\
.filter(Language.active == true()):
mas = []
for lang in DBSession.query(TreeClosureTable.child_pk)\
.filter(TreeClosureTable.parent_pk == family.pk):
if lang[0] in lang_map:
mas.extend(lang_map[lang[0]].macroareas)
a, r = update_relationship(family.macroareas, mas)
if a or r:
stats.update(['macroarea'])
args.log.info('macroareas done')
def index( self, trans, deleted='False', f_email=None, **kwd ):
"""
GET /api/users
GET /api/users/deleted
Displays a collection (list) of users.
"""
rval = []
query = trans.sa_session.query( trans.app.model.User )
deleted = util.string_as_bool( deleted )
if f_email:
query = query.filter(trans.app.model.User.email.like("%%%s%%" % f_email))
if deleted:
query = query.filter( trans.app.model.User.table.c.deleted == true() )
# only admins can see deleted users
if not trans.user_is_admin():
return []
else:
query = query.filter( trans.app.model.User.table.c.deleted == false() )
# special case: user can see only their own user
# special case2: if the galaxy admin has specified that other user email/names are
# exposed, we don't want special case #1
if not trans.user_is_admin() and not trans.app.config.expose_user_name and not trans.app.config.expose_user_email:
item = trans.user.to_dict( value_mapper={ 'id': trans.security.encode_id } )
return [item]
for user in query:
item = user.to_dict( value_mapper={ 'id': trans.security.encode_id } )
# If NOT configured to expose_email, do not expose email UNLESS the user is self, or
# the user is an admin
if not trans.app.config.expose_user_name and user is not trans.user and not trans.user_is_admin():
del item['username']
if not trans.app.config.expose_user_email and user is not trans.user and not trans.user_is_admin():
del item['email']
# TODO: move into api_values
rval.append( item )
return rval
def glottologmeta(request):
q = DBSession.query(Languoid)\
.filter(Language.active == true())\
.filter(Languoid.status.in_(
(LanguoidStatus.established, LanguoidStatus.unattested)))
qt = q.filter(Languoid.father_pk == null())
res = {
'last_update': DBSession.query(Language.updated)
.order_by(Language.updated.desc()).first()[0],
'number_of_families': qt.filter(Languoid.level == LanguoidLevel.family).count(),
'number_of_isolates': qt.filter(Languoid.level == LanguoidLevel.language).count(),
}
ql = q.filter(Languoid.hid != null())
res['number_of_languages'] = {'all': ql.count()}
res['special_families'] = OrderedDict()
for name in SPECIAL_FAMILIES:
l = qt.filter(Language.name == name).one()
res['special_families'][name] = l
res['number_of_languages'][name] = l.child_language_count
res['number_of_languages']['l1'] = res['number_of_languages']['all'] \
- res['number_of_languages']['Pidgin']\
- res['number_of_languages']['Artificial Language']\
- res['number_of_languages']['Sign Language']
return res
def _get_query_condition(self, model, fields, values):
from sqlalchemy import true, and_
condition = true()
for v in fields:
if isinstance(v, (tuple, list)):
v = {'name':v[0]}
elif not isinstance(v, dict):
v = {'name':v}
name = v['name']
if name in values:
render = v.get('render')
value = values[name]
if not value:
continue
_cond = None
if render:
_cond = render(model, name, value, values)
else:
column = model.c[name]
if 'like' in v:
_cond = self._make_like(column, v['like'], value)
elif 'op' in v:
_cond = self._make_op(column, v['op'], value)
else:
if isinstance(value, (tuple, list)):
_cond = column.in_(value)
else:
_cond = column==value
if _cond is not None:
condition = and_(_cond, condition)
log.debug("condition=%s", condition)
return condition
def list_published( self, filters=None, **kwargs ):
"""
Return a list of all published items.
"""
published_filter = self.model_class.published == true()
filters = self._munge_filters( published_filter, filters )
return self.list( filters=filters, **kwargs )
def deleted_histories( self, trans, **kwd ):
"""
The number of histories that were deleted more than the specified number of days ago, but have not yet been purged.
Also included is the number of datasets associated with the histories.
"""
params = util.Params( kwd )
message = ''
if params.deleted_histories_days:
deleted_histories_days = int( params.deleted_histories_days )
cutoff_time = datetime.utcnow() - timedelta( days=deleted_histories_days )
history_count = 0
dataset_count = 0
disk_space = 0
histories = trans.sa_session.query( model.History ) \
.filter( and_( model.History.table.c.deleted == true(),
model.History.table.c.purged == false(),
model.History.table.c.update_time < cutoff_time ) ) \
.options( eagerload( 'datasets' ) )
for history in histories:
for hda in history.datasets:
if not hda.dataset.purged:
dataset_count += 1
try:
disk_space += hda.dataset.file_size
except:
pass
history_count += 1
message = "%d histories ( including a total of %d datasets ) were deleted more than %d days ago, but have not yet been purged, " \
"disk space: %s." % ( history_count, dataset_count, deleted_histories_days, nice_size( disk_space, True ) )
else:
message = "Enter the number of days."
return str( deleted_histories_days ), message
def _query_published( self, filters=None, **kwargs ):
"""
Return a query for all published items.
"""
published_filter = self.model_class.published == true()
filters = self._munge_filters( published_filter, filters )
return self.query( filters=filters, **kwargs )
def __init__(self, model, taxon_col, *args, **kw):
self.taxon_col = taxon_col
kw['choices'] = [
(o.id, '%s %s' % (o.id, o.name)) for o in
DBSession.query(model).filter(model.active == true()).order_by(model.id)]
kw['model_col'] = getattr(Taxon, self.taxon_col)
Col.__init__(self, *args, **kw)
def _get_tenant_provider_security_groups(self, context, tenant_id):
res = context.session.query(
NsxExtendedSecurityGroupProperties.security_group_id
).join(securitygroups_db.SecurityGroup).filter(
securitygroups_db.SecurityGroup.tenant_id == tenant_id,
NsxExtendedSecurityGroupProperties.provider == sa.true()).all()
return [r[0] for r in res]
def list(self, trans, deleted=False):
"""
Return a list of libraries from the DB.
:param deleted: if True, show only ``deleted`` libraries, if False show only ``non-deleted``
:type deleted: boolean (optional)
:returns: query that will emit all accessible libraries
:rtype: sqlalchemy query
:returns: dict of 3 sets with available actions for user's accessible
libraries and a set of ids of all public libraries. These are
used for limiting the number of queries when dictifying the
libraries later on.
:rtype: dict
"""
is_admin = trans.user_is_admin()
query = trans.sa_session.query(trans.app.model.Library)
library_access_action = trans.app.security_agent.permitted_actions.LIBRARY_ACCESS.action
restricted_library_ids = {lp.library_id for lp in (
trans.sa_session.query(trans.model.LibraryPermissions).filter(
trans.model.LibraryPermissions.table.c.action == library_access_action
).distinct())}
prefetched_ids = {'restricted_library_ids': restricted_library_ids}
if is_admin:
if deleted is None:
# Flag is not specified, do not filter on it.
pass
elif deleted:
query = query.filter(trans.app.model.Library.table.c.deleted == true())
else:
query = query.filter(trans.app.model.Library.table.c.deleted == false())
else:
# Nonadmins can't see deleted libraries
query = query.filter(trans.app.model.Library.table.c.deleted == false())
current_user_role_ids = [role.id for role in trans.get_current_user_roles()]
all_actions = trans.sa_session.query(trans.model.LibraryPermissions).filter(trans.model.LibraryPermissions.table.c.role_id.in_(current_user_role_ids))
library_add_action = trans.app.security_agent.permitted_actions.LIBRARY_ADD.action
library_modify_action = trans.app.security_agent.permitted_actions.LIBRARY_MODIFY.action
library_manage_action = trans.app.security_agent.permitted_actions.LIBRARY_MANAGE.action
accessible_restricted_library_ids = set()
allowed_library_add_ids = set()
allowed_library_modify_ids = set()
allowed_library_manage_ids = set()
for action in all_actions:
if action.action == library_access_action:
accessible_restricted_library_ids.add(action.library_id)
if action.action == library_add_action:
allowed_library_add_ids.add(action.library_id)
if action.action == library_modify_action:
allowed_library_modify_ids.add(action.library_id)
if action.action == library_manage_action:
allowed_library_manage_ids.add(action.library_id)
query = query.filter(or_(
not_(trans.model.Library.table.c.id.in_(restricted_library_ids)),
trans.model.Library.table.c.id.in_(accessible_restricted_library_ids)
))
prefetched_ids['allowed_library_add_ids'] = allowed_library_add_ids
prefetched_ids['allowed_library_modify_ids'] = allowed_library_modify_ids
prefetched_ids['allowed_library_manage_ids'] = allowed_library_manage_ids
return query, prefetched_ids
def apply_query_filter( self, trans, query, **kwargs ):
if self.available_tracks is None:
self.available_tracks = trans.app.datatypes_registry.get_available_tracks()
return query.filter( model.HistoryDatasetAssociation.extension.in_(self.available_tracks) ) \
.filter( model.Dataset.state == model.Dataset.states.OK ) \
.filter( model.HistoryDatasetAssociation.deleted == false() ) \
.filter( model.HistoryDatasetAssociation.visible == true() )
def index(self, trans, **kwd):
"""
GET /api/workflows
Displays a collection of workflows.
:param show_published: if True, show also published workflows
:type show_published: boolean
"""
show_published = util.string_as_bool( kwd.get( 'show_published', 'False' ) )
rval = []
filter1 = ( trans.app.model.StoredWorkflow.user == trans.user )
if show_published:
filter1 = or_( filter1, ( trans.app.model.StoredWorkflow.published == true() ) )
for wf in trans.sa_session.query( trans.app.model.StoredWorkflow ).filter(
filter1, trans.app.model.StoredWorkflow.table.c.deleted == false() ).order_by(
desc( trans.app.model.StoredWorkflow.table.c.update_time ) ).all():
item = wf.to_dict( value_mapper={ 'id': trans.security.encode_id } )
encoded_id = trans.security.encode_id(wf.id)
item['url'] = url_for('workflow', id=encoded_id)
item['owner'] = wf.user.username
rval.append(item)
for wf_sa in trans.sa_session.query( trans.app.model.StoredWorkflowUserShareAssociation ).filter_by(
user=trans.user ).join( 'stored_workflow' ).filter(
trans.app.model.StoredWorkflow.deleted == false() ).order_by(
desc( trans.app.model.StoredWorkflow.update_time ) ).all():
item = wf_sa.stored_workflow.to_dict( value_mapper={ 'id': trans.security.encode_id } )
encoded_id = trans.security.encode_id(wf_sa.stored_workflow.id)
item['url'] = url_for( 'workflow', id=encoded_id )
item['owner'] = wf_sa.stored_workflow.user.username
rval.append(item)
return rval
def apply(self, query):
if self.op == self.ops.all:
return query
if self.op == self.ops.yes:
return query.filter(self.sa_col == sa.true())
if self.op == self.ops.no:
return query.filter(self.sa_col == sa.false())
return FilterBase.apply(self, query)
def _get_custom_roles():
"""Objects for which the user is an 'owner'."""
custom_roles_query = db.session.query(
all_models.AccessControlList.object_id.label('id'),
all_models.AccessControlList.object_type.label('type'),
literal(None).label('context_id')
).join(
all_models.AccessControlRole,
all_models.AccessControlList.ac_role_id ==
all_models.AccessControlRole.id
).filter(
and_(
all_models.AccessControlList.person_id == contact_id,
all_models.AccessControlList.object_type.in_(model_names),
all_models.AccessControlRole.my_work == true(),
all_models.AccessControlRole.read == true()
)
)
return custom_roles_query
def upgrade():
table = op.create_table(
RESOURCE_TABLE,
sa.Column("one_row_id", sa.Boolean, server_default=sa.true(), primary_key=True),
sa.Column("resource_version", sa.String(255)),
sa.CheckConstraint("one_row_id", name="kube_resource_version_one_row_id")
)
op.bulk_insert(table, [
{"resource_version": ""}
])
def upgrade():
op.add_column('writeup_posts',
sa.Column('published', sa.Boolean(), nullable=False, server_default=sa.true()))
op.add_column('writeup_post_versions',
sa.Column('active', sa.Boolean(), nullable=False, server_default=sa.true()))
op.add_column('writeup_post_versions',
sa.Column('version', sa.Integer(), nullable=False, server_default='1'))
op.alter_column('writeup_posts', 'published', server_default=None)
op.alter_column('writeup_post_versions', 'active', server_default=None)
op.alter_column('writeup_post_versions', 'version', server_default=None)
def get_condition(self, values=None):
from sqlalchemy import true, and_
values = values or self.result
condition = true()
model = self.model
for v in self.fields:
if isinstance(v, (tuple, list)):
v = {'name':v[0]}
elif not isinstance(v, dict):
v = {'name':v}
name = v['name']
if name in values:
render = v.get('condition')
value = values[name]
if not value:
continue
_cond = None
if render:
_cond = render(model, name, value, values)
else:
if name not in model.c:
log.debug("Can't found {} in model {}".format(name, model.__name__))
continue
column = model.c.get(name)
if column is None:
continue
if 'like' in v:
_cond = self._make_like(column, v['like'], value)
elif 'op' in v:
_cond = self._make_op(column, v['op'], value)
else:
if isinstance(value, (tuple, list)):
if v.get('range'):
_cond = None
if (len(value) > 0 and value[0]):
if 'op' in v:
_cond = self._make_op(column, v['op'][0], value[0])
else:
_cond = (column >= value[0]) & _cond
if (len(value) > 1 and value[1]):
if 'op' in v:
_cond = self._make_op(column, v['op'][1], value[1])
else:
_cond = (column <= value[1]) & _cond
else:
_cond = column.in_(value)
else:
_cond = column==value
if _cond is not None:
condition = and_(_cond, condition)
log.debug("condition=%s", condition)
return condition
def get_internal_roles_json():
"""Get a list of all access control roles"""
with benchmark("Get access roles JSON"):
attrs = all_models.AccessControlRole.query.options(
sqlalchemy.orm.undefer_group("AccessControlRole_complete")
).filter(all_models.AccessControlRole.internal == true()).all()
published = []
for attr in attrs:
published.append(publish(attr))
published = publish_representation(published)
return as_json(published)
def downgrade():
"""Downgrade database schema and/or data back to the previous revision."""
op.alter_column(
"evidence",
"send_by_default",
nullable=True,
server_default=None,
existing_type=sa.Boolean(),
existing_nullable=False,
existing_server_default=sa.true()
)
def alter_evidence():
"""Make send_by_default column not nullable and True by default"""
op.alter_column(
"evidence",
"send_by_default",
nullable=False,
server_default=sa.true(),
existing_type=sa.Boolean(),
existing_nullable=True,
existing_server_default=None,
)
def get_alteration(self, op, user, item_type, model_class):
if user is None:
roles = {WBRoleModel.anonymous_role_name}
else:
user = WBUserModel.query.get(user)
roles = {r.rolename for r in user.roles}
if roles & self.roles:
return {'outerjoin': [], 'filter': true()}
else:
return {'outerjoin': [], 'filter': false()}
def get_languages(self, req, language_url_pattern):
q = DBSession.query(Language).filter(Language.active == true()).options(
joinedload(Language.languageidentifier, LanguageIdentifier.identifier))
for l in page_query(q):
yield {
'@id': language_url_pattern.format(l.id),
'dc:title': l.name,
'dc:identifier': [
{'@id': i.url(), 'schema:name': i.name}
for i in l.identifiers if i.url()],
}
def test_from_function(self):
bookcases = self.tables.bookcases
srf = lateral(func.generate_series(1, bookcases.c.bookcase_shelves))
self.assert_compile(
select([bookcases]).select_from(bookcases.join(srf, true())),
"SELECT bookcases.bookcase_id, bookcases.bookcase_owner_id, "
"bookcases.bookcase_shelves, bookcases.bookcase_width "
"FROM bookcases JOIN "
"LATERAL generate_series(:generate_series_1, "
"bookcases.bookcase_shelves) AS anon_1 ON true"
)
def upgrade():
columns_and_constraints = [
sa.Column("one_row_id", sa.Boolean, server_default=sa.true(), primary_key=True),
sa.Column("worker_uuid", sa.String(255)),
]
conn = op.get_bind()
# alembic creates an invalid SQL for mssql and mysql dialects
if conn.dialect.name in {"mysql"}:
columns_and_constraints.append(sa.CheckConstraint("one_row_id<>0", name="kube_worker_one_row_id"))
elif conn.dialect.name not in {"mssql"}:
columns_and_constraints.append(sa.CheckConstraint("one_row_id", name="kube_worker_one_row_id"))
table = op.create_table(RESOURCE_TABLE, *columns_and_constraints)
op.bulk_insert(table, [{"worker_uuid": ""}])
def index(self,
trans: ProvidesUserContext,
deleted: bool = False) -> QuotaSummaryList:
"""Displays a collection (list) of quotas."""
rval = []
query = trans.sa_session.query(model.Quota)
if deleted:
route = 'deleted_quota'
query = query.filter(model.Quota.deleted == true())
else:
route = 'quota'
query = query.filter(model.Quota.deleted == false())
for quota in query:
item = quota.to_dict(value_mapper={'id': trans.security.encode_id})
encoded_id = trans.security.encode_id(quota.id)
item['url'] = self._url_for(route, id=encoded_id)
rval.append(item)
return QuotaSummaryList.parse_obj(rval)
class Dataset(Model):
"""Git commit loaded into the database."""
__tablename__ = 'dataset'
id = sa.Column(sa.Boolean,
sa.CheckConstraint('id'),
primary_key=True,
server_default=sa.true())
git_commit = sa.Column(sa.String(40),
sa.CheckConstraint('length(git_commit) = 40'),
nullable=False,
unique=True)
git_describe = sa.Column(sa.Text,
sa.CheckConstraint("git_describe != ''"),
nullable=False,
unique=True)
clean = sa.Column(sa.Boolean, nullable=False)
def compute_filter(cls, **kwargs):
f = sa.true()
for key, value in kwargs.items():
if '.' in key:
rel_name, key = key.split('.', 1)
try:
rel = getattr(cls, rel_name)
except AttributeError:
continue
remote_cls = rel.property.mapper.class_
if not hasattr(remote_cls, 'compute_filter'):
continue
_f = remote_cls.compute_filter(**{key: value})
if rel.property.uselist:
f &= rel.any(_f)
else:
f &= rel.has(_f)
return f
def list_challenge_matches(challenge_id):
offset, limit = api_util.get_offset_limit()
where_clause, order_clause, manual_sort = api_util.get_sort_filter(
{
"game_id": model.games.c.id,
"time_played": model.games.c.time_played,
}, ["timed_out"])
participant_clause = sqlalchemy.true()
where_clause &= model.games.c.challenge_id == challenge_id
for (field, _, _) in manual_sort:
if field == "timed_out":
participant_clause &= model.game_participants.c.timed_out
result = match_api.list_matches_helper(offset, limit, participant_clause,
where_clause, order_clause)
return flask.jsonify(result)
def test_join_lateral_w_select_implicit_subquery(self):
table1 = self.tables.people
table2 = self.tables.books
subq = (select(table2.c.book_id).correlate(table1).where(
table1.c.people_id == table2.c.book_owner_id).lateral())
stmt = select(table1,
subq.c.book_id).select_from(table1.join(subq, true()))
self.assert_compile(
stmt,
"SELECT people.people_id, people.age, people.name, "
"anon_1.book_id "
"FROM people JOIN LATERAL (SELECT books.book_id AS book_id "
"FROM books "
"WHERE people.people_id = books.book_owner_id) "
"AS anon_1 ON true",
)
async def query_simplified_user_actions(playback_id,
*,
conn=None) -> List[dict]:
"""Return the final output of user actions for a given playback.
We no longer delete entries, instead we support many being inserted, and we just have into account the last vote
and the skip if any.
:param str playback_id: The playback id we want to get the actions for
:param conn: A connection if any open
:return: A list of the records
"""
sub_query = sa.select([
db.UserAction.c.user_id,
saf.max(db.UserAction.c.ts).label('ts'),
db.UserAction.c.playback_id,
]).where(db.UserAction.c.playback_id == playback_id).group_by(
db.UserAction.c.user_id, db.UserAction.c.playback_id,
sa.case([
(db.UserAction.c.user_id.is_(None), db.UserAction.c.id),
],
else_=0)).alias()
query = sa.select([
sa.distinct(db.UserAction.c.id),
db.UserAction.c.action,
db.UserAction.c.playback_id,
db.UserAction.c.ts,
db.UserAction.c.user_id,
]).select_from(
db.UserAction.join(
sub_query,
sa.and_(
sub_query.c.ts == db.UserAction.c.ts,
db.UserAction.c.playback_id == sub_query.c.playback_id,
sa.case(
[(sa.and_(db.UserAction.c.user_id.is_(None),
sub_query.c.user_id.is_(None)), sa.true())],
else_=db.UserAction.c.user_id == sub_query.c.user_id))))
async with ensure_connection(conn) as conn:
result = []
async for user_action in await conn.execute(query):
result.append(dict(user_action))
return result
def test_filtered_counting(self):
parse_filter = self.history_contents_filters.parse_filter
user2 = self.user_manager.create(**user2_data)
history = self.history_manager.create(name='history', user=user2)
contents = []
contents.extend([
self.add_hda_to_history(history, name=('hda-' + str(x)))
for x in range(3)
])
contents.append(
self.add_list_collection_to_history(history, contents[:3]))
contents.extend([
self.add_hda_to_history(history, name=('hda-' + str(x)))
for x in range(4, 6)
])
contents.append(
self.add_list_collection_to_history(history, contents[4:6]))
self.log("should show correct count with filters")
self.hda_manager.delete(contents[1])
self.hda_manager.delete(contents[4])
contents[6].deleted = True
self.app.model.context.flush()
contents[2].visible = False
contents[5].visible = False
contents[6].visible = False
self.app.model.context.flush()
HDA = self.hda_manager.model_class
self.assertEqual(
self.contents_manager.contents_count(
history, filters=[parsed_filter("orm",
HDA.deleted == true())]), 3)
filters = [parse_filter('visible', 'eq', 'False')]
self.assertEqual(
self.contents_manager.contents_count(history, filters=filters), 3)
filters = [
parse_filter('deleted', 'eq', 'True'),
parse_filter('visible', 'eq', 'False')
]
self.assertEqual(
self.contents_manager.contents_count(history, filters=filters), 1)
def state_counts(self, history):
"""
Return a dictionary containing the counts of all contents in each state
keyed by the distinct states.
Note: does not include deleted/hidden contents.
"""
filters = [
sql.column('deleted') == false(),
sql.column('visible') == true()
]
contents_subquery = self._union_of_contents_query(
history, filters=filters).subquery()
statement = (sql.select([sql.column('state'),
func.count('*')
]).select_from(contents_subquery).group_by(
sql.column('state')))
counts = self.app.model.context.execute(statement).fetchall()
return dict(counts)
def get_streaks(s: sqlalchemy.orm.session.Session,
active: Optional[bool]=None,
limit: Optional[int]=None,
max_age: Optional[int]=None,
) \
-> Sequence[Streak]:
"""Get streaks, ordered by length (longest first).
Parameters:
active: only return streaks with this active flag
limit: only return (up to) limit results
max_age: only return streaks with a win less than this many days old
Returns:
List of active streaks.
"""
# The following code is a translation of this basic SQL:
# SELECT streaks.*, count(games.streak_id) as streak_length
# FROM streaks
# JOIN games ON (streaks.id = games.streak_id)
# GROUP BY streaks.id
# HAVING streak_length > 1
# ORDER BY streak_length DESC
streak_length = func.count(Game.streak_id).label('streak_length')
streak_last_activity = func.max(Game.end).label('streak_last_activity')
q = s.query(Streak, streak_length).join(Streak.games)
q = q.group_by(Streak.id)
q = q.having(streak_length > 1)
if max_age is not None:
q = q.having(
streak_last_activity > func.date('now', '-%s day' % max_age))
q = q.order_by(streak_length.desc())
if active is not None:
q = q.filter(Streak.active == (
sqlalchemy.true() if active else sqlalchemy.false()))
if limit is not None:
q = q.limit(limit)
streaks = q.all()
# Since we added a column to the query, the result format is:
# ((Streak, length), (Streak, length), ...)
# It's annoying to deal with a custom format, and recalculating the streak
# length for a few streaks is NBD, so just return a list of Streaks
return [t.Streak for t in streaks]
def get_similar_email_users(self, similar: str, **kwargs):
sort = kwargs.get('sort', None)
order = kwargs.get('order', None)
offset_number = kwargs.get('offset', None)
limit_number = kwargs.get('limit', 5)
pattern = '{}%'.format(similar)
query = self.session.query(User) \
.filter(User.is_activated == true(), User.email.like(pattern))
if sort:
if order == 'desc':
from sqlalchemy import desc
query = query.order_by(desc(sort))
query = query.order_by(sort)
if offset_number:
query = query.offset(offset_number)
return query.limit(limit_number).all()
def get_filter_operation(model: DeclarativeMeta,
where: Dict[str, Any]) -> ClauseElement:
partial_filter = partial(get_filter_operation, model)
for name, exprs in where.items():
if name == "_or":
return or_(*map(partial_filter, exprs))
if name == "_not":
return not_(partial_filter(exprs))
if name == "_and":
return and_(*map(partial_filter, exprs))
model_property = getattr(model, name)
partial_bool = partial(get_bool_operation, model_property)
return and_(*(starmap(partial_bool, exprs.items())))
return true()
def set_next_schedule_time(ctx, *, db, model):
if not ctx._scheduler_queues:
ctx._next_schedule_time = datetime.max
return
ctx._next_schedule_time = (db.query(
sa.func.min(model.JobSchedule.next_execution_time)).filter(
model.JobSchedule.queue.in_(ctx._scheduler_queues),
model.JobSchedule.is_enabled == sa.true(),
).scalar())
if ctx._next_schedule_time is None:
log.debug('no pending schedules')
ctx._next_schedule_time = datetime.max
else:
when = (ctx._next_schedule_time - ctx._now()).total_seconds()
if when > 0:
log.debug('tracking next schedule in %.3f seconds', when)
def childnodes(request):
if request.params.get('t') == 'select2':
query = DBSession.query(Languoid.id, Languoid.name, Languoid.level)\
.filter(icontains(Languoid.name, request.params.get('q')))
total = query.count()
ms = LanguoidsMultiSelect(request, None, None, url='x')
return dict(
results=[ms.format_result(l) for l in query.limit(100)],
context={},
more=total > 500)
query = DBSession.query(
Languoid.pk,
Languoid.id,
Languoid.name,
Languoid.level,
func.count(TreeClosureTable.child_pk).label('children'))\
.filter(Language.pk == TreeClosureTable.parent_pk)\
.filter(Language.active == true())
if request.params.get('node'):
query = query.filter(Languoid.father_pk == int(request.params['node']))
else:
# narrow down selection of top-level nodes in the tree:
query = query.filter(Languoid.father_pk == null())
if request.params.get('q'):
query = query.filter(Language.name.contains(request.params.get('q')))
query = query.group_by(
Languoid.pk,
Languoid.id,
Languoid.name,
Languoid.level).order_by(Language.name)
return [{
'label': ('%s (%s)' % (l.name, l.children - 1))
if l.children > 1 else l.name,
'glottocode': l.id,
'lname': l.name,
'id': l.pk,
'level': l.level.value,
#'children': l.children
'load_on_demand': l.children > 1} for l in query]
def upgrade(migrate_engine):
metadata.bind = migrate_engine
log.debug( "Fixing a discrepancy concerning deleted shared history items." )
affected_items = 0
start_time = time.time()
for dataset in context.query( Dataset ).filter( and_( Dataset.deleted == true(), Dataset.purged == false() ) ):
for dataset_instance in dataset.history_associations + dataset.library_associations:
if not dataset_instance.deleted:
dataset.deleted = False
if dataset.file_size in [ None, 0 ]:
dataset.set_size() #Restore filesize
affected_items += 1
break
context.flush()
log.debug( "%i items affected, and restored." % ( affected_items ) )
log.debug( "Time elapsed: %s" % ( time.time() - start_time ) )
#fix share before hda
log.debug( "Fixing a discrepancy concerning cleaning up deleted history items shared before HDAs." )
dataset_by_filename = {}
changed_associations = 0
start_time = time.time()
for dataset in context.query( Dataset ).filter( Dataset.external_filename.like( '%dataset_%.dat' ) ):
if dataset.file_name in dataset_by_filename:
guessed_dataset = dataset_by_filename[ dataset.file_name ]
else:
guessed_dataset = __guess_dataset_by_filename( dataset.file_name )
if guessed_dataset and dataset.file_name != guessed_dataset.file_name:#not os.path.samefile( dataset.file_name, guessed_dataset.file_name ):
guessed_dataset = None
dataset_by_filename[ dataset.file_name ] = guessed_dataset
if guessed_dataset is not None and guessed_dataset.id != dataset.id: #could we have a self referential dataset?
for dataset_instance in dataset.history_associations + dataset.library_associations:
dataset_instance.dataset = guessed_dataset
changed_associations += 1
#mark original Dataset as deleted and purged, it is no longer in use, but do not delete file_name contents
dataset.deleted = True
dataset.external_filename = "Dataset was result of share before HDA, and has been replaced: %s mapped to Dataset %s" % ( dataset.external_filename, guessed_dataset.id )
dataset.purged = True #we don't really purge the file here, but we mark it as purged, since this dataset is now defunct
context.flush()
log.debug( "%i items affected, and restored." % ( changed_associations ) )
log.debug( "Time elapsed: %s" % ( time.time() - start_time ) )
def index(self, trans, deleted='False', f_email=None, **kwd):
"""
GET /api/users
GET /api/users/deleted
Displays a collection (list) of users.
"""
rval = []
query = trans.sa_session.query(trans.app.model.User)
deleted = util.string_as_bool(deleted)
if f_email:
query = query.filter(
trans.app.model.User.email.like("%%%s%%" % f_email))
if deleted:
query = query.filter(
trans.app.model.User.table.c.deleted == true())
# only admins can see deleted users
if not trans.user_is_admin():
return []
else:
query = query.filter(
trans.app.model.User.table.c.deleted == false())
# special case: user can see only their own user
# special case2: if the galaxy admin has specified that other user email/names are
# exposed, we don't want special case #1
if not trans.user_is_admin(
) and not trans.app.config.expose_user_name and not trans.app.config.expose_user_email:
item = trans.user.to_dict(
value_mapper={'id': trans.security.encode_id})
return [item]
for user in query:
item = user.to_dict(value_mapper={'id': trans.security.encode_id})
# If NOT configured to expose_email, do not expose email UNLESS the user is self, or
# the user is an admin
if not trans.app.config.expose_user_name and user is not trans.user and not trans.user_is_admin(
):
del item['username']
if not trans.app.config.expose_user_email and user is not trans.user and not trans.user_is_admin(
):
del item['email']
# TODO: move into api_values
rval.append(item)
return rval
def _query_chunks(query, model, orderings, chunk_size):
"""Paginate query by chunks of specific size.
The main difference here from util functions generating chunkyfied query
provided in utils module is that here no offset is used. Chunks are queried
here using filters which is faster comparing to offsets in case of large
number of records in query.
Args:
query: Query to be paginated.
model: Model used in `query`.
orderings: Orderings used in `query`.
chunk_size: Size of chunks.
Yields:
Objects in chunks from query query.
"""
filters = [sa.true() for _ in orderings]
count = query.count()
for _ in range(0, count, chunk_size):
# Pagination is performed by filtering here insted of using offset since
# using offset with large values is much slower than plain filtering.
paginated_q = query.from_self().filter(*filters).limit(chunk_size)
chunk = paginated_q.all()
yield chunk
if chunk:
# Filters should be recalculated here to return new chunk on next iter.
# New filters should be in form "ordering field >= last in chunk" except
# for the last field in orderings - this one should be > last in chunk.
ge_filter_fields, gt_filter_field = orderings[:-1], orderings[-1]
last_in_chunk = chunk[-1]
filters = [
op.ge(getattr(model, field), getattr(last_in_chunk, field))
for field in ge_filter_fields
]
filters.append(
op.gt(
getattr(model, gt_filter_field),
getattr(last_in_chunk, gt_filter_field),
)
)
def upgrade():
columns_and_constraints = [
sa.Column("one_row_id",
sa.Boolean,
server_default=sa.true(),
primary_key=True),
sa.Column("resource_version", sa.String(255))
]
conn = op.get_bind()
# alembic creates an invalid SQL for mssql dialect
if conn.dialect.name not in ('mssql'):
columns_and_constraints.append(
sa.CheckConstraint("one_row_id",
name="kube_resource_version_one_row_id"))
table = op.create_table(RESOURCE_TABLE, *columns_and_constraints)
op.bulk_insert(table, [{"resource_version": ""}])
def _get_chain_hops_for_port_by_network_side(context, port_id, side):
reverse_side = constants.REVERSE_PORT_SIDE[side]
query = context.session.query(bagpipe_db.BaGPipeChainHop)
query = query.join(
models_v2.Network,
sa.or_(
sa.and_(
models_v2.Network.id == getattr(bagpipe_db.BaGPipeChainHop,
side + '_network'),
bagpipe_db.BaGPipeChainHop.reverse_hop == false()),
sa.and_(
models_v2.Network.id == getattr(bagpipe_db.BaGPipeChainHop,
reverse_side + '_network'),
bagpipe_db.BaGPipeChainHop.reverse_hop == true())))
query = query.join(models_v2.Port,
models_v2.Port.network_id == models_v2.Network.id)
query = query.filter(models_v2.Port.id == port_id)
return query.all()
def list(self, trans, deleted=False):
"""
Return a list of groups from the DB.
:returns: query that will emit all groups
:rtype: sqlalchemy query
"""
is_admin = trans.user_is_admin()
query = trans.sa_session.query(trans.app.model.Group)
if is_admin:
if deleted is None:
# Flag is not specified, do not filter on it.
pass
elif deleted:
query = query.filter(trans.app.model.Group.table.c.deleted == true())
else:
query = query.filter(trans.app.model.Group.table.c.deleted == false())
else:
query = query.filter(trans.app.model.Group.table.c.deleted == false())
return query
async def db_worker():
logging.info('Идет работа с базой данных...')
db: Session = pool()
for event in db.query(Event).all():
delta = datetime.now().date() - datetime.strptime(event.date, "%d.%m.%Y").date()
if delta.days > 1:
logging.info(f'Удалено событие {event.title}')
db.delete(event)
db.commit()
continue
hour = datetime.now().hour
for user in db.query(User).filter(User.banned == false(), User.subscribed == true()).all():
if user.mailing_time == f'{hour}:00':
await send_mailing(user.user_id)
await asyncio.sleep(.5)
db.close()
class BaseModel(db.Model):
"""Base model's class"""
__abstract__ = True
id = db.Column(db.Integer, primary_key=True)
is_active = db.Column(
db.Boolean,
default=True,
nullable=False,
server_default=sa.true(),
info={"label": "Is active?"},
)
created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow)
updated_at = db.Column(
db.DateTime,
default=datetime.datetime.utcnow,
onupdate=datetime.datetime.utcnow,
)
@property
def is_authenticated(self):
return True
@property
def is_anonymous(self):
return False
def __eq__(self, other):
if isinstance(other, BaseModel):
return self.id == other.id
return NotImplemented
def __ne__(self, other):
equal = self.__eq__(other)
if equal is NotImplemented:
return NotImplemented
return not equal
def get_id(self):
return str(self.id)
def lock_scheduler_queues(ctx, *, db, model, now=None):
if now is None:
now = ctx._now()
Lock = model.Lock
new_queues = set()
for queue in (q for q in ctx._queues if q not in ctx._scheduler_queues):
result = db.execute(
insert(Lock.__table__).values({
Lock.queue: queue,
Lock.key: 'scheduler',
Lock.lock_id: ctx._lock_id,
Lock.worker: ctx._name,
}).on_conflict_do_nothing().returning(Lock.queue)).scalar()
if result is not None:
new_queues.add(queue)
if new_queues:
log.info('scheduler started for queues=%s',
','.join(sorted(new_queues)))
ctx._scheduler_queues.update(new_queues)
stale_cutoff_time = now - ctx._timeout
stale_schedules = (db.query(model.JobSchedule).with_for_update().filter(
model.JobSchedule.queue.in_(ctx._scheduler_queues),
model.JobSchedule.is_enabled == sa.true(),
model.JobSchedule.next_execution_time < stale_cutoff_time,
).all())
for s in stale_schedules:
next_execution_time = get_next_rrule_time(s.rrule, s.created_time,
stale_cutoff_time)
log.warning(
'execution time on schedule=%s is very old (%s seconds), skipping '
'to next time=%s',
s.id,
(now - s.next_execution_time).total_seconds(),
next_execution_time,
)
s.next_execution_time = next_execution_time
set_next_schedule_time(ctx, db=db, model=model)
def leave_gig():
response = request.get_json()
user_id = response['id']
# Removes user from the gig
user = User.query.filter(User.spotify_id == user_id).first()
gig_id = user.gig_id
if user.is_host:
gig = Gig.query.filter(Gig.id == gig_id).first()
users_in_gig = User.query.filter(User.gig_id == gig_id).all()
[db.session.delete(user) for user in users_in_gig]
db.session.delete(gig)
db.session.commit()
else:
db.session.delete(user)
db.session.commit()
host = User.query.filter(User.gig_id == gig_id, User.is_host == true())
update_playlist(host, gig_id)
return jsonify({'message': 'User left successfully'})
def _get_chain_hops_for_port_by_ppg_side(context, port_id, side):
reverse_side = constants.REVERSE_PORT_SIDE[side]
query = context.session.query(bagpipe_db.BaGPipeChainHop)
query = query.join(
sfc_db.PortPairGroup,
sfc_db.PortPairGroup.id == getattr(bagpipe_db.BaGPipeChainHop,
side + '_ppg'))
query = query.join(
sfc_db.PortPair,
sfc_db.PortPair.portpairgroup_id == sfc_db.PortPairGroup.id)
query = query.filter(
sa.or_(
sa.and_(
getattr(sfc_db.PortPair, reverse_side) == port_id,
bagpipe_db.BaGPipeChainHop.reverse_hop == false()),
sa.and_(
getattr(sfc_db.PortPair, side) == port_id,
bagpipe_db.BaGPipeChainHop.reverse_hop == true())))
return query.all()
def list_challenges():
offset, limit = api_util.get_offset_limit()
where_clause, order_clause, manual_sort = api_util.get_sort_filter({
"issuer": model.challenges.c.issuer,
"created": model.challenges.c.created,
"finished": model.challenges.c.finished,
"num_games": model.challenges.c.num_games,
"winner": model.challenges.c.winner,
}, ["finished", "participant"])
participant_clause = sqlalchemy.true()
for (field, op, val) in manual_sort:
if field == "finished":
where_clause &= model.challenges.c.status == "finished"
elif field == "participant":
participant_clause &= op(model.challenge_participants.c.user_id, val)
result = list_challenges_helper(offset, limit,
participant_clause,
where_clause, order_clause)
return flask.jsonify(result)
def fetch_lent_list(self, owner_id: str) -> List[LendingEntity]:
result = db.session.query(
Lending.id,
Lending.content,
Lending.deadline,
User.name.label('borrower_name')
) \
.outerjoin(User, Lending.borrower_id == User.id) \
.filter(Lending.owner_id == owner_id) \
.filter(Lending.is_returned == false()) \
.filter(Lending.is_sent_url == true()) \
.all()
lent_list = [
LendingEntity(r.id,
r.content,
r.deadline,
borrower_name=r.borrower_name) for r in result
]
return lent_list
def to_filter(self, value=Empty):
"""
:param value: 需要查询的值, 如果没有设置值则使用default, 如果default没有设置则使用true来代替?
"""
field = self.column
# 如果没有传值但是设置了默认值, 使用默认值代替value, 否则使用true来作为占位符
if value is Empty:
if self.default is not Empty:
value = self.default
else:
return true()
if self.value_process:
if callable(self.value_process):
value = self.value_process(value)
value = self._value_process(value)
return self.operator(field, value)
def userless_histories( self, trans, **kwd ):
"""The number of userless histories and associated datasets that have not been updated for the specified number of days."""
params = util.Params( kwd )
message = ''
if params.userless_histories_days:
userless_histories_days = int( params.userless_histories_days )
cutoff_time = datetime.utcnow() - timedelta( days=userless_histories_days )
history_count = 0
dataset_count = 0
for history in trans.sa_session.query( model.History ) \
.filter( and_( model.History.table.c.user_id == null(),
model.History.table.c.deleted == true(),
model.History.table.c.update_time < cutoff_time ) ):
for dataset in history.datasets:
if not dataset.deleted:
dataset_count += 1
history_count += 1
message = "%d userless histories ( including a total of %d datasets ) have not been updated for at least %d days." % ( history_count, dataset_count, userless_histories_days )
else:
message = "Enter the number of days."
return str( userless_histories_days ), message
def index(self, trans, deleted='False', **kwd):
"""
GET /api/quotas
GET /api/quotas/deleted
Displays a collection (list) of quotas.
"""
rval = []
deleted = util.string_as_bool(deleted)
query = trans.sa_session.query(trans.app.model.Quota)
if deleted:
route = 'deleted_quota'
query = query.filter(trans.app.model.Quota.deleted == true())
else:
route = 'quota'
query = query.filter(trans.app.model.Quota.deleted == false())
for quota in query:
item = quota.to_dict(value_mapper={'id': trans.security.encode_id})
encoded_id = trans.security.encode_id(quota.id)
item['url'] = url_for(route, id=encoded_id)
rval.append(item)
return rval
