def collaborator_users(account_id=None, user_id=None, video_id=None):
assert account_id or user_id or video_id
if user_id:
# Select all users from accounts than own the videos this user is
# collaborating on: user -> collab -> video -> account -> user.
username = AccountUser.query.filter_by(id=user_id).value('username')
query = VideoCollaborator.query.join(Video).join(
AccountUser,
(AccountUser.account_id == Video.account_id)
).filter(
(user_id == VideoCollaborator.account_user_id) |
(func.lower(username) == func.lower(VideoCollaborator.email))
)
else:
if account_id:
# Select collaborators on all videos owned by this account:
# account -> video -> collab (-> user)
query = VideoCollaborator.query.join(
Video,
(Video.id == VideoCollaborator.video_id) &
(Video.account_id == account_id)
)
else:
# Select collaborators for a specific video:
# video -> collab (-> user)
query = VideoCollaborator.query.filter_by(video_id=video_id)
query = query.outerjoin(
AccountUser,
((AccountUser.id == VideoCollaborator.account_user_id) |
(func.lower(AccountUser.username) == func.lower(VideoCollaborator.email)))
)
return query.with_entities(VideoCollaborator, AccountUser)
def users(user_id=None):
"""Manage users of PyBossa"""
try:
form = SearchForm(request.form)
users = db.session.query(model.User)\
.filter(model.User.admin == True)\
.filter(model.User.id != current_user.id)\
.all()
if request.method == 'POST' and form.user.data:
query = '%' + form.user.data.lower() + '%'
found = db.session.query(model.User)\
.filter(or_(func.lower(model.User.name).like(query),
func.lower(model.User.fullname).like(query)))\
.filter(model.User.id != current_user.id)\
.all()
require.user.update(found)
if not found:
flash("<strong>Ooops!</strong> We didn't find a user "
"matching your query: <strong>%s</strong>" % form.user.data)
return render_template('/admin/users.html', found=found, users=users,
title=gettext("Manage Admin Users"),
form=form)
return render_template('/admin/users.html', found=[], users=users,
title=gettext("Manage Admin Users"), form=form)
except HTTPException:
return abort(403)
except Exception as e:
current_app.logger.error(e)
return abort(500)
def queue_forget(title=None, imdb_id=None, tmdb_id=None, session=None, movie_id=None, queue_name='default'):
"""
Forget movie download from the queue.
:param title: Movie title
:param imdb_id: Imdb id
:param tmdb_id: Tmdb id
:param session: Optional session to use, new session used otherwise
:param queue_name: Name of movie queue to get items from
:return: Title of forgotten movie
:raises QueueError: If queued item could not be found with given arguments
"""
log.debug('queue_forget - title=%s, imdb_id=%s, tmdb_id=%s, movie_id=%s, queue_name=%s', title, imdb_id, tmdb_id,
movie_id, queue_name)
query = session.query(QueuedMovie).filter(func.lower(QueuedMovie.queue_name) == queue_name.lower())
if imdb_id:
query = query.filter(QueuedMovie.imdb_id == imdb_id)
elif tmdb_id:
query = query.filter(QueuedMovie.tmdb_id == tmdb_id)
elif title:
query = query.filter(func.lower(QueuedMovie.title) == func.lower(title))
elif movie_id:
query = query.filter(QueuedMovie.id == movie_id)
try:
item = query.one()
title = item.title
if not item.downloaded:
raise QueueError(message=('%s is not marked as downloaded' % title), errno=1)
item.downloaded = None
return item.to_dict()
except NoResultFound as e:
raise QueueError(message=('title=%s, imdb_id=%s, tmdb_id=%s, movie_id=%s, queue_name=%s not found in queue' %
(title, imdb_id, tmdb_id, movie_id, queue_name)), errno=2)
def create_user_and_sample_Items(name, phone_num):
customer = Customer(name, phone_num)
cookie = Item(name + "_Cookie", 1.50)
muffin = Item(name + "_Muffin", 2.00, "Carby stuff")
latte = Item(name + "_Latte", 4.35, "Steamed milk over expresso")
mocha = Item(name + "_Mocha", 5.00,
"Steamed milk over expresso and chocolate syrup")
session.add(customer)
session.add(cookie)
session.add(muffin)
session.add(latte)
session.add(mocha)
session.commit()
result = {"customer": customer}
item_names = ['cookie', 'muffin', 'latte', 'mocha']
for name in item_names:
item = session.query(Item).\
filter(func.lower(Item.name).like('%' + name + '%')).\
filter(func.lower(Item.name).like('%' + customer_name + '%')).\
scalar()
result[name] = item
return result
def select(q, **kwargs):
if kwargs.get('ids'):
print("ids == " + str(kwargs.get('ids')))
q = q.filter(Flight.id.in_(kwargs.get('ids')))
if kwargs.get('date_from'):
try:
date_from = datetime.strptime(kwargs.get('date_from'), "%Y-%m-%d")
q = q.filter(Flight.takeoff_time >= date_from)
print("takeoff_time >= " + str(date_from))
except:
print("Cannot parse date-from")
return None
if kwargs.get('date_to'):
try:
date_to = datetime.strptime(kwargs.get('date_to'), "%Y-%m-%d")
q = q.filter(Flight.takeoff_time < date_to)
print("takeoff_time < " + str(date_to))
except:
print("Cannot parse date-to")
return None
if kwargs.get('uploaded_from'):
try:
uploaded_from = datetime.strptime(kwargs.get('uploaded_from'), "%Y-%m-%d")
q = q.filter(Flight.time_created >= uploaded_from)
print("time_created >= " + str(uploaded_from))
except:
print("Cannot parse uploaded-from")
return None
if kwargs.get('uploaded_to'):
try:
uploaded_to = datetime.strptime(kwargs.get('uploaded_to'), "%Y-%m-%d")
q = q.filter(Flight.time_created < uploaded_to)
print("time_created < " + str(uploaded_to))
except:
print("Cannot parse uploaded-to")
return None
if not kwargs.get('private'):
print("privacy_level == PUBLIC")
q = q.filter(Flight.privacy_level == Flight.PrivacyLevel.PUBLIC)
if kwargs.get('country_code'):
country_code = kwargs.get('country_code')
q = q.join(Flight.takeoff_airport)
q = q.filter(func.lower(Airport.country_code) == func.lower(country_code))
print("takeoff_airport country code: " + country_code)
if kwargs.get('airport_name'):
airport_name = kwargs.get('airport_name')
q = q.join(Flight.takeoff_airport)
q = q.filter(func.lower(Airport.name) == func.lower(airport_name))
print("takeoff_airport name: " + airport_name)
return q
def get_monkey_by_email(email):
"""Get monkey by email."""
try:
return Monkey.query.filter(
func.lower(Monkey.email) == func.lower(email)).first()
except Exception:
return None
def queue_del(title=None, imdb_id=None, tmdb_id=None, session=None, movie_id=None, queue_name='default'):
"""
Delete the given item from the queue.
:param title: Movie title
:param imdb_id: Imdb id
:param tmdb_id: Tmdb id
:param session: Optional session to use, new session used otherwise
:param queue_name: Name of movie queue to get items from
:return: Title of forgotten movie
:raises QueueError: If queued item could not be found with given arguments
"""
log.debug('queue_del - title=%s, imdb_id=%s, tmdb_id=%s, movie_id=%s, queue_name=%s',
title, imdb_id, tmdb_id, movie_id, queue_name)
query = session.query(QueuedMovie).filter(func.lower(QueuedMovie.queue_name) == queue_name.lower())
if imdb_id:
query = query.filter(QueuedMovie.imdb_id == imdb_id)
elif tmdb_id:
query = query.filter(QueuedMovie.tmdb_id == tmdb_id)
elif title:
query = query.filter(func.lower(QueuedMovie.title) == func.lower(title))
elif movie_id:
query = query.filter(QueuedMovie.id == movie_id)
try:
item = query.one()
title = item.title
session.delete(item)
return title
except NoResultFound as e:
raise QueueError(
'title=%s, imdb_id=%s, tmdb_id=%s, movie_id=%s not found in queue %s' % (
title, imdb_id, tmdb_id, movie_id, queue_name))
except MultipleResultsFound:
raise QueueError('title=%s, imdb_id=%s, tmdb_id=%s, movie_id=%s matches multiple results in queue %s' %
(title, imdb_id, tmdb_id, movie_id, queue_name))
def group(id):
group = Group.query.filter_by(id=id).first_or_404()
if not current_user in group.users:
abort(404)
form = None
if group.changeable:
form = GroupAddMemberForm()
if current_user == group.admin and form.validate_on_submit():
user = User.query.filter(func.lower(User.username) == func.lower(form.member.data)).first()
if not user:
flash(gettext("User not found."), "error")
return redirect(url_for("group", id=group.id))
if user in group.users:
flash(gettext("User is already a member of the group."), "info")
return redirect(url_for("group", id=group.id))
group.users.append(user)
db.session.commit()
flash(gettext("The user was added to the group."), "success")
return redirect(url_for("group", id=group.id))
return render_template("user/group.jade", group=group, form=form)
def _by_name(session, gene_id, chromosome_ids):
gene_db = (session
.query(Gene)
.filter(func.lower(Gene.name) == func.lower(gene_id))
.filter(Gene.chromosome_id.in_(chromosome_ids))
.all())
return gene_db, False
def createGroup(self, name, description='', visible=True, automatic_approval=False):
created = False
try:
session = self.Session()
group = session.query(ESGFGroup).filter( func.lower(ESGFGroup.name) == func.lower(name) ).one()
print "Group with name=%s already exists" % group.name
created = False
return group
except NoResultFound:
group = ESGFGroup(name=name, description=description, visible=visible, automatic_approval=automatic_approval)
session.add(group)
session.commit()
created = True
return group
finally:
session.close()
return created
def login(self):
request = self.request
params = request.params
if self.logged_in:
return HTTPForbidden()
login_url = request.route_url('login')
referrer = request.referrer
if referrer == login_url:
referrer = '/'
came_from = request.params.get('came_from', referrer)
if 'form.submitted' in params:
user = DBSession.query(User).filter(func.lower(User.email) == func.lower(params['email'])).first()
if user is not None:
if user.confirmed is False:
request.session.flash('Account must be activated before logging in.',
queue='notifications')
elif check_password(params['email'], params['password']):
userid = user.id
print("userid: " + str(userid))
print("email: " + str(user.email))
headers = remember(request, str(userid))
self.gibs['current_user'] = user.email
request.session.flash('Successfully logged in as ' + str(user.email),
queue='successes')
return HTTPFound(location=came_from,
headers=headers)
request.session.flash('Email or password was incorrect, or user does not exist.',
queue='warnings')
return {'gibs': self.gibs,
'came_from': came_from,
}
def get_user_by_name_or_email(name_or_email):
return db.session \
.query(db.User) \
.filter(
(func.lower(db.User.name) == func.lower(name_or_email))
| (func.lower(db.User.email) == func.lower(name_or_email))) \
.first()
def event_contains(value, query):
value = value.lower()
q = query.filter(or_(
func.lower(Event.name).contains(value),
func.lower(Event.description).contains(value)
))
return q
def find_user_by_twitter(cls, twitter):
""" Finds user by EMAIL, making sure to look in all lowercase
Returns Query object not user! Then can use first_or_404.
"""
user_query = cls.query.filter(
func.lower(cls.twitter_handle) == func.lower(twitter))
return user_query
def _search_video(self, query, location, size, start=0):
videos = Video.query.filter_by(deleted=False, status="published").outerjoin(
VideoLocaleMeta, (VideoLocaleMeta.video_id == Video.id) & (VideoLocaleMeta.locale == "")
)
owner_filter = VIDEO_OWNER_USER_QUERY_RE.match(query.strip())
if owner_filter:
videos = videos.join(
AccountUser, (AccountUser.account_id == Video.account_id) & (AccountUser.id == owner_filter.group(1))
).distinct()
query = ""
collab_filter = VIDEO_COLLABORATOR_QUERY_RE.match(query.strip())
if collab_filter:
videos = (
videos.join(VideoCollaborator, VideoCollaborator.video_id == Video.id)
.join(
AccountUser,
(func.lower(AccountUser.username) == func.lower(VideoCollaborator.email))
& (AccountUser.id == collab_filter.group(1)),
)
.distinct()
)
query = ""
videos, total = self._db_match(videos, size, start, query, Video.search_vector)
items = [self._video_item(v) for v in videos]
return dict(items=items, total=total)
def find_user_by_name(cls, name):
""" Finds user by name, making sure to look in all lowercase
Returns Query object not user! Then can use first_or_404.
"""
user_query = cls.query.filter(
func.lower(cls.name) == func.lower(name))
return user_query
def find_user_by_email(cls, email):
""" Finds user by EMAIL, making sure to look in all lowercase
Returns Query object not user! Then can use first_or_404.
"""
user_query = cls.query.filter(
func.lower(cls.email) == func.lower(email))
return user_query
def get_active_subscriptions_for_a_type(cls, ttype):
return cls.query.filter(
and_(
cls.active,
func.lower(cls.type) == func.lower(ttype)
)
).all()
def get_templates_for_uri_and_type(cls, uri, ttype):
return cls.query.filter(
and_(
func.lower(cls.uri) == func.lower(uri),
func.lower(cls.type) == func.lower(ttype)
)
).all()
def get_badges_from_tags(self, tags, match_all=False):
"""
Return badges matching tags.
:type tags: list
:param tags: A list of string badge tags
:type match_all: boolean
:param match_all: Returned badges must have all tags in list
"""
badges = list()
if match_all:
# Return badges matching all tags
# ... by doing argument-expansion on a list comprehension
badges.extend(self.session.query(Badge).filter(and_(*[
func.lower(Badge.tags).contains(str(tag + ',').lower())
for tag in tags])))
else:
# Return badges matching any of the tags
for tag in tags:
badges.extend(self.session.query(Badge).filter(
func.lower(Badge.tags).contains(
str(tag + ',').lower())).all())
# Eliminate any duplicates.
unique_badges = list()
for badge in badges:
if badge not in unique_badges:
unique_badges.append(badge)
return unique_badges
def get_profile_from_id(id, id_type="url_slug", show_secrets=False, include_products=True, include_product_relationships=True):
if include_products:
if include_product_relationships:
query_base = Profile.query
else:
query_base = db.session.query(Profile).options(orm.noload('*'), orm.subqueryload(Profile.products))
else:
query_base = db.session.query(Profile).options(orm.noload('*'))
if id_type == "id":
try:
profile = query_base.get(id)
except DataError: # id has to be an int
logger.debug(u"get_profile_from_id no profile found from profile id {id}".format(
id=id))
profile = None
elif id_type == "email":
profile = query_base.filter(func.lower(Profile.email) == func.lower(id)).first()
elif id_type == "url_slug":
profile = query_base.filter(func.lower(Profile.url_slug) == func.lower(id)).first()
if not show_secrets:
profile = hide_profile_secrets(profile)
return profile
def person_exists(self, email=None, id=None, nickname=None):
"""
Check if a Person with this email is stored in the database
:type email: str
:param email: An email address to search the database for
:type id: str
:param id: A user id to search for.
:type nickname: str
:param nickname: A nickname to search for.
"""
query = self.session.query(Person)
if email:
return query.filter(
func.lower(Person.email) == func.lower(email)).count() != 0
elif id:
return query.filter_by(id=id).count() != 0
elif nickname:
return query.filter(
func.lower(Person.nickname) == func.lower(
nickname)).count() != 0
else:
return False
def modifyNotesEntry(session, teamnum, comp, old_notes, new_notes, notestag):
notes = session.query(NotesEntry).filter(NotesEntry.team==teamnum).\
filter(func.lower(NotesEntry.competition)==func.lower(comp)).\
filter(NotesEntry.data==old_notes)
if notes:
notes.data = new_notes
notes.tag = notestag
def get_quadbounds(location):
try:
quads = DBSession.query(
Quadrant.latitude,
Quadrant.longitude,
Quadrant.name,
).filter(
func.lower(Quadrant.location)==
func.lower(location)
).all()
center = DBSession.query(
Location.latitude,
Location.longitude,
).filter(
func.lower(location)==
func.lower(Location.name),
).first()
except DBAPIError as e:
print e
return []
bounds = []
for i in quads:
if i[1] <= center[1]:
bounds.append([i[0], i[1], center[0], center[1], i[2]])
else:
bounds.append([center[0], center[1], i[0], i[1], i[2]])
return bounds
def recommended_books_by_publisher(books_read_by_user, recommended_books):
read_books_isbn_list = [book.book_isbn for book in books_read_by_user]
# Get publishers and the number of books read by the user of the publisher
# in descending order
publisher_count = db.session.query(
func.lower(Books.publisher),
func.count(Books.publisher)
).filter(
Books.isbn.in_(read_books_isbn_list)
).group_by(
func.lower(Books.publisher)
).order_by(
func.count(Books.publisher).desc()
).all()
publisher_count_dict = {str(publisher): count for (
publisher, count) in publisher_count}
unread_books_by_same_publishers = db.session.query(
Books.isbn,
func.lower(Books.publisher)
).filter(
and_(
func.lower(Books.publisher).in_([x[0] for x in publisher_count]),
~Books.isbn.in_(read_books_isbn_list)
)
).all()
# Gets the books of the above publishers which are not read by the user
for unread_book in unread_books_by_same_publishers:
isbn = unread_book[0]
publisher = unread_book[1]
weight = g.user.publisher_weight * publisher_count_dict[publisher]
if isbn not in recommended_books:
recommended_books.update({isbn: weight})
else:
recommended_books[isbn] += weight
def get_rainData(start_date, end_date, location):
data = {}
try:
readings = DBSession.query(
Gauge.quadrant,
RainReading.date,
func.avg(RainReading.amount),
).filter(
Gauge.gauge_id==RainReading.gauge_id,
func.lower(Gauge.location)==func.lower(location),
func.lower(RainReading.location)==
func.lower(location),
RainReading.date.between(start_date, end_date),
).group_by(
Gauge.quadrant,
RainReading.date,
).all()
except DBAPIError as e:
print e
return {}
for i in readings:
new_item = [str(i[1]), int(i[2])]
if i[0] in data:
data[i[0]].append(new_item)
else:
data[i[0]] = [new_item]
return data
def get_recentTrapCount(location, date):
try:
dates = DBSession.query(
func.max(Trap.date).label('date'),
Region.quadrant.label('quad'),
Region.name.label('region'),
).filter(
Region.name==Trap.region,
Trap.date <= date,
func.lower(Region.location)==func.lower(location),
).group_by(Trap.region).subquery()
counts = DBSession.query(
dates.c.date,
dates.c.quad,
Trap.count,
).filter(
Region.name==Trap.region,
Trap.date==dates.c.date,
Trap.region==dates.c.region,
).all()
except DBAPIError as e:
print e
return []
counts = [[str(i[0]), str(i[1]), int(i[2])] for i in counts]
return counts
def get_asset_type_model_user_name_num(self, asset_type,
model_id, user_name, store_state):
return self.session.query(AssetConsumeInfoModel).filter(
and_(AssetConsumeInfoModel.asset_type_id == asset_type,
AssetConsumeInfoModel.model_id == model_id,
AssetConsumeInfoModel.store_state_id == store_state,
func.lower(AssetConsumeInfoModel.user_name) == func.lower(user_name))).first()
def search_sample(self, sample_code):
""" return a single Sample from the current batch with sample_code """
try:
return self.samples.filter( func.lower(Sample.code) == func.lower(sample_code),
Sample.batch_id == self.id ).one()
except NoResultFound:
return None
def get_person(self, person_email=None, id=None, nickname=None):
"""
Convenience function to retrieve a person object from an email,
id, or nickname.
:type person_email: str
:param person_email: The email address of a Person in the database
:type id: str
:param id: The id of a Person in the database
:type nickname: str
:param nickname: The nickname of a Person in the database
"""
query = self.session.query(Person)
if person_email and self.person_exists(email=person_email):
return query.filter(
func.lower(Person.email) == \
func.lower(person_email)).one()
elif id and self.person_exists(id=id):
return query.filter_by(id=id).one()
elif nickname and self.person_exists(nickname=nickname):
return query.filter(
func.lower(Person.nickname) == \
func.lower(nickname)).one()
else:
return None
def get_by_email(cls, email):
return User.query.filter(
func.lower(User.email) == func.lower(email)).one_or_none()
def lowercase_name(cls):
"The SQLAlchemy expression for the property above."
return func.lower(cls.name)
def query_definition(term):
''' Query the definition for a term from the database
'''
return Definition.query.filter(
func.lower(Definition.term) == func.lower(term)).first()
def county_cases(state):
result = db.session.query(County).join(State).filter(
func.lower(State.name) == func.lower(state)).all()
print(result)
return str(result)
def airport_filter_min_helper(num_cases, airport_entry):
name = airport_entry["properties"]["name"]
airport = db.session.query(Airport).filter(
func.lower(name) == func.lower(Airport.code)).first()
return airport.county.new_cases >= int(num_cases)
def get(self):
try:
filters = []
# Validate the request
if len(request.args) == 0:
raise InvalidInputError(
message='No query parameters were specified in the request'
)
nr_num_query_str = get_query_param_str('nrNum')
email_address_query_str = get_query_param_str('emailAddress')
phone_number_query_str = get_query_param_str('phoneNumber')
if not nr_num_query_str:
raise InvalidInputError(message='An nrNum must be provided')
else:
if not email_address_query_str and not phone_number_query_str:
raise InvalidInputError(
message=
'Either an emailAddress or phoneNumber must be provided'
)
# Continue
nr_num = parse_nr_num(nr_num_query_str)
email_address = email_address_query_str
phone_number = get_query_param_str('phoneNumber')
# Filter on addresses
# address_line = get_query_param_str('addrLine1')
if nr_num:
filters.append(func.lower(Request.nrNum) == nr_num.lower())
if phone_number:
strip_phone_number_chars_regex = r"[^0-9]"
filters.append(
Request.applicants.any(
func.regexp_replace(
Applicant.phoneNumber,
strip_phone_number_chars_regex, '', 'g').contains(
re.sub(strip_phone_number_chars_regex, '',
phone_number))))
if email_address:
filters.append(
Request.applicants.any(
func.lower(Applicant.emailAddress).startswith(
email_address.lower())))
'''
Filter on addresses
if address_line:
filters.append(
Request.applicants.any(
func.lower(Applicant.addrLine1).startswith(address_line.lower())
)
)
'''
criteria = RequestQueryCriteria(nr_num=nr_num, filters=filters)
results = Request.find_by_criteria(criteria)
if not results:
results = []
except InvalidInputError as err:
return handle_exception(err, err.message, 400)
except Exception as err:
return handle_exception(err,
'Error retrieving the NR from the db.',
500)
if nr_num and len(results) == 1:
nr_model = results[0]
if nr_model.requestTypeCd and (not nr_model.entity_type_cd
or not nr_model.request_action_cd):
# If requestTypeCd is set, but a request_entity (entity_type_cd) and a request_action (request_action_cd)
# are not, use get_mapped_entity_and_action_code to map the values from the requestTypeCd
entity_type, request_action = get_mapped_entity_and_action_code(
nr_model.requestTypeCd)
nr_model.entity_type_cd = entity_type
nr_model.request_action_cd = request_action
response_data = nr_model.json()
# Add the list of valid Name Request actions for the given state to the response
response_data['actions'] = get_nr_state_actions(
results[0].stateCd, results[0])
return jsonify(response_data), 200
elif len(results) > 0:
# We won't add the list of valid Name Request actions for the given state to the response if we're sending back a list
# If the user / client accessing this data needs the Name Request actions, GET the individual record using NameRequest.get
# This method, NameRequests.get is for Existing NR Search
return jsonify(list(map(lambda result: result.json(),
results))), 200
# We won't add the list of valid Name Request actions for the given state to the response if we're sending back a list
# If the user / client accessing this data needs the Name Request actions, GET the individual record using NameRequest.get
# This method, NameRequests.get is for Existing NR Search
return jsonify(results), 200
def normalized_name(cls):
return func.replace(func.replace(func.lower(cls.name), '_', ''), ' ', '')
async def get_by_email(self, email: str) -> Optional[UD]:
query = self.users.select().where(
func.lower(self.users.c.email) == func.lower(email))
user = await self.database.fetch_one(query)
return await self._make_user(user) if user else None
def admin_booze():
form = BoozeForm(request.form)
boozes = Booze.query.order_by(asc(func.lower(Booze.name)))
return render_template("admin/booze", options=app.options, boozes=boozes, form=form, title="Booze")
def get_by_bar_training_token(cls, code):
uid = verify_bar_training_token(app.config["SECRET_KEY"], code)
if uid is None:
raise ValueError("Invalid token")
return User.query.filter_by(id=uid).one()
@property
def is_cfp_accepted(self):
for proposal in self.proposals:
if proposal.state in {"accepted", "finished"}:
return True
return False
Index("ix_user_email_lower", func.lower(User.email), unique=True)
Index(
"ix_user_email_tsearch",
text("to_tsvector('simple', replace(email, '@', ' '))"),
postgresql_using="gin",
)
Index("ix_user_name_tsearch",
text("to_tsvector('simple', name)"),
postgresql_using="gin")
class UserDiversity(db.Model):
__tablename__ = "diversity"
user_id = db.Column(db.Integer,
db.ForeignKey("user.id"),
nullable=False,
def search_datasets(dataset_id=None,
dataset_name=None,
collection_name=None,
data_type=None,
dimension=None,
unit=None,
scenario_id=None,
metadata_name=None,
metadata_val=None,
attr_id=None,
type_id=None,
unconnected=None,
inc_metadata='N',
inc_val='N',
page_start=0,
page_size=2000,
**kwargs):
"""
Get multiple datasets, based on several
filters. If all filters are set to None, all
datasets in the DB (that the user is allowe to see)
will be returned.
"""
log.info("Searching datasets: \ndatset_id: %s,\n"
"datset_name: %s,\n"
"collection_name: %s,\n"
"data_type: %s,\n"
"dimension: %s,\n"
"unit: %s,\n"
"scenario_id: %s,\n"
"metadata_name: %s,\n"
"metadata_val: %s,\n"
"attr_id: %s,\n"
"type_id: %s,\n"
"unconnected: %s,\n"
"inc_metadata: %s,\n"
"inc_val: %s,\n"
"page_start: %s,\n"
"page_size: %s" %
(dataset_id, dataset_name, collection_name, data_type, dimension,
unit, scenario_id, metadata_name, metadata_val, attr_id, type_id,
unconnected, inc_metadata, inc_val, page_start, page_size))
if page_size is None:
page_size = config.get('SEARCH', 'page_size', 2000)
user_id = int(kwargs.get('user_id'))
dataset_qry = db.DBSession.query(
Dataset.dataset_id, Dataset.data_type, Dataset.data_units,
Dataset.data_dimen, Dataset.data_name, Dataset.hidden, Dataset.cr_date,
Dataset.created_by, DatasetOwner.user_id,
null().label('metadata'), Dataset.start_time, Dataset.frequency,
Dataset.value)
#Dataset ID is unique, so there's no point using the other filters.
#Only use other filters if the datset ID is not specified.
if dataset_id is not None:
dataset_qry = dataset_qry.filter(Dataset.dataset_id == dataset_id)
else:
if dataset_name is not None:
dataset_qry = dataset_qry.filter(
func.lower(Dataset.data_name).like("%%%s%%" %
dataset_name.lower()))
if collection_name is not None:
dc = aliased(DatasetCollection)
dci = aliased(DatasetCollectionItem)
dataset_qry = dataset_qry.join(
dc,
func.lower(dc.collection_name).like(
"%%%s%%" % collection_name.lower())).join(
dci,
and_(dci.collection_id == dc.collection_id,
dci.dataset_id == Dataset.dataset_id))
if data_type is not None:
dataset_qry = dataset_qry.filter(
func.lower(Dataset.data_type) == data_type.lower())
#null is a valid dimension, so we need a way for the searcher
#to specify that they want to search for datasets with a null dimension
#rather than ignoring the dimension in the filter. We use 'null' to do this.
if dimension is not None:
dimension = dimension.lower()
if dimension == 'null':
dimension = None
if dimension is not None:
dataset_qry = dataset_qry.filter(
func.lower(Dataset.data_dimen) == dimension)
else:
dataset_qry = dataset_qry.filter(
Dataset.data_dimen == dimension)
#null is a valid unit, so we need a way for the searcher
#to specify that they want to search for datasets with a null unit
#rather than ignoring the unit. We use 'null' to do this.
if unit is not None:
unit = unit.lower()
if unit == 'null':
unit = None
if unit is not None:
dataset_qry = dataset_qry.filter(
func.lower(Dataset.data_units) == unit)
else:
dataset_qry = dataset_qry.filter(Dataset.data_units == unit)
if scenario_id is not None:
dataset_qry = dataset_qry.join(
ResourceScenario,
and_(ResourceScenario.dataset_id == Dataset.dataset_id,
ResourceScenario.scenario_id == scenario_id))
if attr_id is not None:
dataset_qry = dataset_qry.join(
ResourceScenario,
ResourceScenario.dataset_id == Dataset.dataset_id).join(
ResourceAttr,
and_(
ResourceAttr.resource_attr_id ==
ResourceScenario.resource_attr_id,
ResourceAttr.attr_id == attr_id))
if type_id is not None:
dataset_qry = dataset_qry.join(
ResourceScenario,
ResourceScenario.dataset_id == Dataset.dataset_id).join(
ResourceAttr, ResourceAttr.resource_attr_id ==
ResourceScenario.resource_attr_id).join(
TypeAttr,
and_(TypeAttr.attr_id == ResourceAttr.attr_id,
TypeAttr.type_id == type_id))
if unconnected == 'Y':
stmt = db.DBSession.query(
distinct(ResourceScenario.dataset_id).label('dataset_id'),
literal_column("0").label('col')).subquery()
dataset_qry = dataset_qry.outerjoin(
stmt, stmt.c.dataset_id == Dataset.dataset_id)
dataset_qry = dataset_qry.filter(stmt.c.col == None)
elif unconnected == 'N':
#The dataset has to be connected to something
stmt = db.DBSession.query(
distinct(ResourceScenario.dataset_id).label('dataset_id'),
literal_column("0").label('col')).subquery()
dataset_qry = dataset_qry.join(
stmt, stmt.c.dataset_id == Dataset.dataset_id)
if metadata_name is not None and metadata_val is not None:
dataset_qry = dataset_qry.join(
Metadata,
and_(
Metadata.dataset_id == Dataset.dataset_id,
func.lower(Metadata.metadata_name).like(
"%%%s%%" % metadata_name.lower()),
func.lower(Metadata.metadata_val).like(
"%%%s%%" % metadata_val.lower())))
elif metadata_name is not None and metadata_val is None:
dataset_qry = dataset_qry.join(
Metadata,
and_(
Metadata.dataset_id == Dataset.dataset_id,
func.lower(Metadata.metadata_name).like(
"%%%s%%" % metadata_name.lower())))
elif metadata_name is None and metadata_val is not None:
dataset_qry = dataset_qry.join(
Metadata,
and_(
Metadata.dataset_id == Dataset.dataset_id,
func.lower(Metadata.metadata_val).like(
"%%%s%%" % metadata_val.lower())))
#All datasets must be joined on dataset owner so only datasets that the
#user can see are retrieved.
dataset_qry = dataset_qry.outerjoin(
DatasetOwner,
and_(DatasetOwner.dataset_id == Dataset.dataset_id,
DatasetOwner.user_id == user_id))
dataset_qry = dataset_qry.filter(
or_(Dataset.hidden == 'N',
and_(DatasetOwner.user_id is not None, Dataset.hidden == 'Y')))
log.info(str(dataset_qry))
datasets = dataset_qry.all()
log.info("Retrieved %s datasets", len(datasets))
#page the datasets:
if page_start + page_size > len(datasets):
page_end = None
else:
page_end = page_start + page_size
datasets = datasets[page_start:page_end]
log.info("Datasets paged from result %s to %s", page_start, page_end)
datasets_to_return = []
for dataset_row in datasets:
dataset_dict = dataset_row._asdict()
if inc_val == 'N':
dataset_dict['value'] = None
else:
#convert the value row into a string as it is returned as a binary
if dataset_row.value is not None:
dataset_dict['value'] = str(dataset_row.value)
if inc_metadata == 'Y':
metadata = db.DBSession.query(Metadata).filter(
Metadata.dataset_id == dataset_row.dataset_id).all()
dataset_dict['metadata'] = metadata
else:
dataset_dict['metadata'] = []
dataset = namedtuple('Dataset', dataset_dict.keys())(**dataset_dict)
datasets_to_return.append(dataset)
return datasets_to_return
def admin_booze_edit(id):
saved = int(request.args.get('saved', "0"))
booze = Booze.query.filter_by(id=int(id)).first()
form = BoozeForm(obj=booze)
boozes = Booze.query.order_by(asc(func.lower(Booze.name)))
return render_template("admin/booze", options=app.options, booze=booze, boozes=boozes, form=form, title="Booze", saved=saved)
def get_bucketlist(user, id):
"""
Returns a bucket along with a list of its items
---
tags:
- bucketlists
consumes:
- "application/json"
produces:
- "application/json"
parameters:
- name: X-Token
in: header
description: The user's token
required: true
type: string
- name: id
in: path
type: integer
required: true
description: The id of the bucket
- name: limit
in: query
required: false
description: The number of results that should be returned
type: integer
- name: page
in: query
required: false
description: The current page. Pages start from zero.
type: integer
- name: q
in: query
description: The keywords to search based on item title
required: false
type: string
responses:
200:
description: Operation was successful
schema:
type: object
properties:
id:
type: integer
name:
type: string
description:
type: string
items:
schema:
type: object
properties:
id:
type: integer
title:
type: string
description:
type: string
due_date:
type: string
is_complete:
type: boolean
created_at:
type: string
400:
description: Parameter error
schema:
type: object
properties:
message:
type: string
401:
description: Invalid token error
schema:
type: object
properties:
message:
type: string
"""
if request.method == 'OPTIONS' or not user:
return jsonify()
bucketlist = BucketList.query.filter_by(user_id=user.id, id=id).first()
if bucketlist == None:
return jsonify(message='The bucket you requested does not exist'), 404
bucket_result = bucketlist.dict()
bucket_result['items'] = list()
limit, page = get_pagination_params(request)
offset = limit * page
query = request.args.get('q')
items = BucketItem.query.filter(BucketItem.bucketlist_id == BucketList.id)
if query:
query = query.replace(' ', '%')
items = items.filter(
func.lower(BucketItem.title).like('%' + func.lower(query) + '%'))
items = items.order_by(desc(
BucketItem.created_at)).limit(limit).offset(offset)
for item in items:
bucket_result['items'].append(item.dict())
previous = '/bucketlists/{}?limit={}'.format(id, limit)
next = '/bucketlists/{}?limit={}'.format(id, limit)
if page >= 0:
next = next + '&page={}'.format(page + 1)
previous = previous + '&page={}'.format(page - 1)
if page == 0:
previous = ''
return jsonify(bucketlist=bucket_result,
paging=dict(previous=previous, next=next))
def lookup(title=None, year=None, only_cached=False, session=None):
if not title:
raise LookupError('No criteria specified for blu-ray.com lookup')
title_year = title + ' ({})'.format(year) if year else title
movie_filter = session.query(BlurayMovie).filter(
func.lower(BlurayMovie.name) == title.lower())
if year:
movie_filter = movie_filter.filter(BlurayMovie.year == year)
movie = movie_filter.first()
if not movie:
found = session.query(BluraySearchResult). \
filter(BluraySearchResult.search == title_year.lower()).first()
if found and found.movie:
movie = found.movie
if movie:
# Movie found in cache, check if cache has expired. Shamefully stolen from api_tmdb
refresh_time = timedelta(days=2)
if movie.release_date:
if movie.release_date > datetime.now().date() - timedelta(
days=7):
# Movie is less than a week old, expire after 1 day
refresh_time = timedelta(days=1)
else:
age_in_years = (datetime.now().date() -
movie.release_date).days / 365
refresh_time += timedelta(days=age_in_years * 5)
if movie.updated < datetime.now(
) - refresh_time and not only_cached:
log.debug(
'Cache has expired for %s, attempting to refresh from blu-ray.com.',
movie.name)
try:
updated_movie = BlurayMovie(title=title, year=year)
except LookupError:
log.error(
'Error refreshing movie details for %s from blu-ray.com, cached info being used.',
title)
else:
movie = session.merge(updated_movie)
else:
log.debug('Movie %s information restored from cache.',
movie.name)
else:
if only_cached:
raise LookupError('Movie %s not found from cache' % title_year)
# There was no movie found in the cache, do a lookup from blu-ray.com
log.verbose('Searching from blu-ray.com `%s`', title)
# Add/merge movie to db
movie = BlurayMovie(title=title, year=year)
# Add to search results table if necessary
if title.lower() != movie.name.lower():
session.add(
BluraySearchResult(search=title_year.lower(),
movie_id=movie.id))
session.merge(movie)
if not movie:
raise LookupError(
'Unable to find movie on blu-ray: {}'.format(title_year))
return movie
def get_bucketlists(user):
"""
Returns a list of bucketlists
---
tags:
- bucketlists
consumes:
- "application/json"
produces:
- "application/json"
parameters:
- name: X-Token
in: header
description: The user's token
required: true
type: string
- name: limit
in: query
required: false
description: The number of results that should be returned
type: integer
- name: page
in: query
required: false
description: The current page. Pages start from zero.
type: integer
- name: q
in: query
required: false
description: The keywords to search.
type: string
security:
- X-Token
responses:
200:
description: Operation was successful
schema:
type: object
properties:
id:
type: integer
name:
type: string
description:
type: string
400:
description: Parameter error
schema:
type: object
properties:
message:
type: string
401:
description: Invalid token error
schema:
type: object
properties:
message:
type: string
"""
if request.method == 'OPTIONS' or not user:
return jsonify()
limit, page = get_pagination_params(request)
query = request.args.get('q')
offset = page * limit
bucketlists = BucketList.query.filter(BucketList.user_id == user.id)
if query:
query = query.replace(' ', '%')
bucketlists = bucketlists.filter(
func.lower(BucketList.name).like('%' + func.lower(query) + '%'))
bucketlists = bucketlists.order_by(desc(
BucketList.created_at)).limit(limit).offset(offset)
bucket_list = list()
for bucketlist in bucketlists:
_bucketlist = bucketlist.dict()
bucket_list.append(_bucketlist)
previous = '/bucketlists?limit={}'.format(limit)
next = '/bucketlists?limit={}'.format(limit)
if page >= 0:
next = next + '&page={}'.format(page + 1)
previous = previous + '&page={}'.format(page - 1)
if page == 0:
previous = ''
return jsonify(bucketlists=bucket_list,
paging=dict(previous=previous, next=next))
def edit_bucketlist(user, id):
"""
Edits a bucket
---
tags:
- bucketlists
consumes:
- "application/json"
produces:
- "application/json"
parameters:
- name: X-Token
in: header
description: The user's token
required: true
type: string
- name: id
in: path
type: integer
required: true
description: The id of the bucket
- name: payload
in: body
required: true
description: The name and title of the new Bucket
type: object
schema:
properties:
name:
type: string
required: false
description:
type: string
required: false
responses:
200:
description: Operation was successful
schema:
type: object
properties:
id:
type: integer
name:
type: string
description:
type: string
created_at:
type: string
400:
description: Parameter error
schema:
type: object
properties:
message:
type: string
401:
description: Invalid token error
schema:
type: object
properties:
message:
type: string
"""
if request.method == 'OPTIONS':
return jsonify()
body = get_request_body(request)
name = body.get('name')
description = body.get('description')
bucketlist = BucketList.query.filter(
and_(func.lower(BucketList.name) == func.lower(name)),
BucketList.id != id).first()
if bucketlist:
return jsonify(message='A bucket with that name already exists',
parameter='name'), 400
bucketlist = BucketList.query.filter_by(user_id=user.id, id=id).first()
if bucketlist == None:
return jsonify(message='The bucket you requested does not exist'), 404
if not body:
return jsonify(message='At least one parameter is required',
parameter=list("name", 'description')), 400
if name:
bucketlist.name = name
if description:
bucketlist.description = description
db.session.commit()
return jsonify(bucketlist.dict())
def all_codes_for(cls, email):
return (cls.query.filter(func.lower(cls.email) == email.lower())).all()
def create_bucketlist(user):
"""
Creates a new bucket
---
tags:
- bucketlists
consumes:
- "application/json"
produces:
- "application/json"
parameters:
- name: X-Token
in: header
description: The user's token
required: true
type: string
- name: payload
in: body
required: true
description: The name and title of the new Bucket
type: object
schema:
properties:
name:
type: string
description:
type: string
security:
- X-Token
responses:
201:
description: Operation was successful
schema:
type: object
properties:
id:
type: integer
name:
type: string
description:
type: string
created_at:
type: string
400:
description: Parameter error
schema:
type: object
properties:
message:
type: string
401:
description: Invalid token error
schema:
type: object
properties:
message:
type: string
"""
if request.method == 'OPTIONS':
return jsonify()
body = get_request_body(request)
name = body.get('name')
description = body.get('description')
if not name:
return jsonify(message='Bucket name is missing', parameter='name'), 400
if not description:
return jsonify(message='Bucket description is missing',
parameter='description'), 400
bucketlist = BucketList.query.filter(
func.lower(BucketList.name) == func.lower(name)).first()
if bucketlist:
return jsonify(message='A bucket with that name already exists',
parameter='name'), 400
bucketlist = BucketList(name, description, owner=user)
db.session.add(bucketlist)
db.session.commit()
return jsonify(bucketlist.dict()), 201
def bind_expression(self, bindvalue):
return func.lower(bindvalue)
def __eq__(self, other):
return func.lower(self.__clause_element__()) == func.lower(other)
def getmname(name):
return Medicines.query.filter(
func.lower(Medicines.name) == func.lower(name)).first().mid
def column_expression(self, col):
return func.lower(col)
def invite(chat, pm_user, url, fmt):
if request.form["username"] == g.user.username:
abort(404)
if chat.type != "group" or chat.publicity != "private":
abort(404)
try:
own_chat_user = g.db.query(ChatUser).filter(
and_(
ChatUser.chat_id == chat.id,
ChatUser.user_id == g.user.id,
)).one()
except NoResultFound:
abort(404)
if not own_chat_user.can("invite"):
abort(403)
try:
invite_user = g.db.query(User).filter(
func.lower(
User.username) == request.form["username"].lower(), ).one()
except NoResultFound:
if ("X-Requested-With" in request.headers
and request.headers["X-Requested-With"] == "XMLHttpRequest"):
abort(404)
return redirect((request.headers.get("Referer")
or url_for("rp_invites", url=url)).split("?")[0] +
"?invite_error=no_user")
if g.db.query(func.count("*")).select_from(Invite).filter(
and_(
Invite.chat_id == chat.id,
Invite.user_id == invite_user.id,
)).scalar() == 0:
g.db.add(
Invite(chat_id=chat.id,
user_id=invite_user.id,
creator_id=g.user.id))
# Subscribe them to the chat and make it unread so they get a notification about it.
try:
invite_chat_user = g.db.query(ChatUser).filter(
and_(
ChatUser.chat_id == chat.id,
ChatUser.user_id == invite_user.id,
)).one()
except NoResultFound:
new_number = (g.db.query(func.max(ChatUser.number)).filter(
ChatUser.chat_id == chat.id).scalar() or 0) + 1
invite_chat_user = ChatUser.from_user(user=invite_user,
chat_id=chat.id,
number=new_number)
# Disable typing notifications by default in group chats.
invite_chat_user.typing_notifications = False
g.db.add(invite_chat_user)
invite_chat_user.subscribed = True
invite_chat_user.last_online = chat.last_message - datetime.timedelta(
0, 1)
send_message(
g.db, g.redis,
Message(
chat_id=chat.id,
user_id=invite_user.id,
type="user_action",
name=own_chat_user.name,
text="%s [%s] invited %s to the chat." % (
own_chat_user.name,
own_chat_user.acronym,
invite_user.username,
),
), g.user_list)
if ("X-Requested-With" in request.headers
and request.headers["X-Requested-With"] == "XMLHttpRequest"):
return "", 204
if "Referer" in request.headers:
return redirect(request.headers["Referer"].split("?")[0])
return redirect(url_for("rp_invites", url=url))
def ordered_query(self, request, query=None):
"""Order query according to request args.
If query is None, the query is generated according to request
args with self.query(request)
"""
if query is None:
query = self.query(request)
engine = query.session.get_bind(self.managed_class.__mapper__)
args = request.args
sort_col = int(args.get("iSortCol_0", 1))
sort_dir = args.get("sSortDir_0", "asc")
sort_col_def = self.list_view_columns[sort_col]
sort_col_name = sort_col_def['name']
rel_sort_names = sort_col_def.get('sort_on', (sort_col_name, ))
sort_cols = []
for rel_col in rel_sort_names:
sort_col = getattr(self.managed_class, rel_col)
if hasattr(sort_col, 'property') and isinstance(
sort_col.property,
orm.properties.RelationshipProperty,
):
# this is a related model: find attribute to filter on
query = query.outerjoin(sort_col_name, aliased=True)
rel_model = sort_col.property.mapper.class_
default_sort_name = 'name'
if issubclass(rel_model, BaseVocabulary):
default_sort_name = 'label'
rel_sort_name = sort_col_def.get('relationship_sort_on', None)
if rel_sort_name is None:
rel_sort_name = sort_col_def.get(
'sort_on',
default_sort_name,
)
sort_col = getattr(rel_model, rel_sort_name)
# XXX: Big hack, date are sorted in reverse order by default
if isinstance(sort_col, _DateAffinity):
sort_dir = 'asc' if sort_dir == 'desc' else 'desc'
elif isinstance(sort_col, sa.types.String) or \
hasattr(sort_col, 'property') and \
isinstance(sort_col.property.columns[0].type, sa.types.String):
sort_col = func.lower(sort_col)
try:
direction = desc if sort_dir == 'desc' else asc
sort_col = direction(sort_col)
except BaseException:
# FIXME
pass
# sqlite does not support 'NULLS FIRST|LAST' in ORDER BY clauses
if engine.name != 'sqlite':
nullsorder = nullslast if sort_dir == 'desc' else nullsfirst
try:
sort_col = nullsorder(sort_col)
except BaseException:
# FIXME
pass
sort_cols.append(sort_col)
try:
query = query.order_by(*sort_cols)
except BaseException:
# FIXME
pass
query.reset_joinpoint()
return query
def like_domain_query(term):
domain_query = database.session_query(Domain.id)
domain_query = domain_query.filter(func.lower(Domain.name).like(term.lower()))
assoc_query = database.session_query(certificate_associations.c.certificate_id)
assoc_query = assoc_query.filter(certificate_associations.c.domain_id.in_(domain_query))
return assoc_query
def uninvite(chat, pm_user, url, fmt):
if request.form["username"] == g.user.username:
abort(404)
if chat.type != "group" or chat.publicity != "private":
abort(404)
try:
own_chat_user = g.db.query(ChatUser).filter(
and_(
ChatUser.chat_id == chat.id,
ChatUser.user_id == g.user.id,
)).one()
except NoResultFound:
abort(404)
if not own_chat_user.can("invite"):
abort(403)
try:
invite_user = g.db.query(User).filter(
func.lower(
User.username) == request.form["username"].lower(), ).one()
except NoResultFound:
abort(404)
if g.db.query(func.count("*")).select_from(Invite).filter(
and_(
Invite.chat_id == chat.id,
Invite.user_id == invite_user.id,
)).scalar() != 0:
g.db.query(Invite).filter(
and_(
Invite.chat_id == chat.id,
Invite.user_id == invite_user.id,
)).delete()
# Unsubscribing is impossible if they don't have access to the chat, so
# we need to force-unsubscribe them here.
try:
invite_chat_user = g.db.query(ChatUser).filter(
and_(
ChatUser.chat_id == chat.id,
ChatUser.user_id == invite_user.id,
)).one()
invite_chat_user.subscribed = False
except NoResultFound:
pass
send_message(
g.db, g.redis,
Message(
chat_id=chat.id,
user_id=invite_user.id,
type="user_action",
name=own_chat_user.name,
text="%s [%s] un-invited %s from the chat." % (
own_chat_user.name,
own_chat_user.acronym,
invite_user.username,
),
), g.user_list)
if ("X-Requested-With" in request.headers
and request.headers["X-Requested-With"] == "XMLHttpRequest"):
return "", 204
if "Referer" in request.headers:
return redirect(request.headers["Referer"].split("?")[0])
return redirect(url_for("rp_invites", url=url))
def find(cls, text):
"""Find users whose name matches with the given text"""
return cls.query.filter(func.lower(cls.name).contains(text.lower()))
def decorated_function(url, fmt=None, *args, **kwargs):
# Helper for doing some special URL stuff with PM chats.
# Normally we just query for a Chat object with the url. However, PM chat
# URLs take the form "pm/<username>", so we have to look up the username,
# find the User it belongs to, and use our URL and theirs to create a
# special URL.
if url == "pm":
abort(404)
elif url.startswith("pm/"):
username = url[3:]
if username == "":
abort(404)
# You can't PM yourself.
if g.user is None or username.lower() == g.user.username.lower():
abort(404)
try:
pm_user = g.db.query(User).filter(
func.lower(User.username) == username.lower()).one()
except NoResultFound:
abort(404)
# Fix case if necessary.
if pm_user.username != username:
if request.method != "GET":
abort(404)
return redirect(
url_for(request.endpoint,
url="pm/" + pm_user.username,
fmt=fmt))
# Generate URL from our user ID and their user ID.
# Sort so they're always in the same order.
pm_url = "pm/" + ("/".join(
sorted([str(g.user.id), str(pm_user.id)])))
try:
chat = g.db.query(PMChat).filter(PMChat.url == pm_url, ).one()
except NoResultFound:
# Only create a new PMChat on the main chat page.
if request.endpoint != "rp_chat":
abort(404)
# Reject if age-restricted.
if (not g.user.is_admin and pm_user.pm_age_restriction
and pm_user.age_group == AgeGroup.under_18
and g.user.age_group != AgeGroup.under_18):
abort(404)
chat = PMChat(url=pm_url)
g.db.add(chat)
g.db.flush()
# Create ChatUser for the other user.
pm_chat_user = ChatUser.from_user(pm_user,
chat_id=chat.id,
number=1,
subscribed=True)
g.db.add(pm_chat_user)
g.db.flush()
return f(chat, pm_user, url, fmt, *args, **kwargs)
# Force lower case.
if url != url.lower():
if request.method != "GET":
abort(404)
return redirect(url_for(request.endpoint, url=url.lower(),
fmt=fmt))
try:
chat = g.db.query(AnyChat).filter(AnyChat.url == url).one()
except NoResultFound:
abort(404)
g.chat = chat
g.chat_id = chat.id
g.user_list = UserListStore(
NewparpRedis(connection_pool=redis_chat_pool), chat.id)
try:
authorize_joining(g.db, g)
except BannedException:
if request.endpoint != "rp_chat" or chat.url == "theoubliette":
abort(403)
if request.method != "GET":
abort(404)
return redirect(
url_for(request.endpoint, url="theoubliette", fmt=fmt))
except UnauthorizedException:
if request.endpoint != "rp_chat_unsubscribe":
return render_template("chat/private.html", chat=chat), 403
except BadAgeException:
if request.endpoint != "rp_chat_unsubscribe":
if g.user is None:
return render_template("chat/log_in_required.html",
chat=chat)
elif g.user.age_group == AgeGroup.under_18:
return render_template("chat/age_under_18.html", chat=chat)
return render_template("chat/age_check.html", chat=chat), 403
except TooManyPeopleException:
if request.endpoint == "rp_chat":
return render_template("chat/too_many_people.html",
chat=chat), 403
return f(chat, None, url, fmt, *args, **kwargs)
class Foo(AbstractFoo):
__tablename__ = "foo"
column_prop = column_property(
func.lower(AbstractFoo.unrelated_data))
def render(args):
"""
Helper function that allows use to render our REST Api.
:param args:
:return:
"""
query = database.session_query(Certificate)
show_expired = args.pop("showExpired")
if show_expired != 1:
one_month_old = (
arrow.now()
.shift(months=current_app.config.get("HIDE_EXPIRED_CERTS_AFTER_MONTHS", -1))
.format("YYYY-MM-DD")
)
query = query.filter(Certificate.not_after > one_month_old)
time_range = args.pop("time_range")
destination_id = args.pop("destination_id")
notification_id = args.pop("notification_id", None)
show = args.pop("show")
# owner = args.pop('owner')
# creator = args.pop('creator') # TODO we should enabling filtering by owner
filt = args.pop("filter")
if filt:
terms = filt.split(";")
term = "%{0}%".format(terms[1])
# Exact matches for quotes. Only applies to name, issuer, and cn
if terms[1].startswith('"') and terms[1].endswith('"'):
term = terms[1][1:-1]
if "issuer" in terms:
# we can't rely on issuer being correct in the cert directly so we combine queries
sub_query = (
database.session_query(Authority.id)
.filter(Authority.name.ilike(term))
.subquery()
)
query = query.filter(
or_(
Certificate.issuer.ilike(term),
Certificate.authority_id.in_(sub_query),
)
)
elif "destination" in terms:
query = query.filter(
Certificate.destinations.any(Destination.id == terms[1])
)
elif "notify" in filt:
query = query.filter(Certificate.notify == truthiness(terms[1]))
elif "active" in filt:
query = query.filter(Certificate.active == truthiness(terms[1]))
elif "cn" in terms:
query = query.filter(
or_(
func.lower(Certificate.cn).like(term.lower()),
Certificate.id.in_(like_domain_query(term)),
)
)
elif "id" in terms:
query = query.filter(Certificate.id == cast(terms[1], Integer))
elif "name" in terms:
query = query.filter(
or_(
func.lower(Certificate.name).like(term.lower()),
Certificate.id.in_(like_domain_query(term)),
func.lower(Certificate.cn).like(term.lower()),
)
)
elif "fixedName" in terms:
# only what matches the fixed name directly if a fixedname is provided
query = query.filter(Certificate.name == terms[1])
else:
query = database.filter(query, Certificate, terms)
if show:
sub_query = (
database.session_query(Role.name)
.filter(Role.user_id == args["user"].id)
.subquery()
)
query = query.filter(
or_(
Certificate.user_id == args["user"].id, Certificate.owner.in_(sub_query)
)
)
if destination_id:
query = query.filter(
Certificate.destinations.any(Destination.id == destination_id)
)
if notification_id:
query = query.filter(
Certificate.notifications.any(Notification.id == notification_id)
)
if time_range:
to = arrow.now().shift(weeks=+time_range).format("YYYY-MM-DD")
now = arrow.now().format("YYYY-MM-DD")
query = query.filter(Certificate.not_after <= to).filter(
Certificate.not_after >= now
)
if current_app.config.get("ALLOW_CERT_DELETION", False):
query = query.filter(Certificate.deleted == false())
result = database.sort_and_page(query, Certificate, args)
return result
