def getItems(self, category_id):
"""Convenience method to get all items for the given category.
"""
return self.db_session.query(Item) \
.filter_by(category_id=category_id) \
.order_by(collate(Item.name, 'NOCASE')) \
.all()
def get(self, query_args):
query = Category.query
filters = []
if "search" in query_args and query_args["search"]:
filters.append(Category.name.like("%{filter}%".format(filter=query_args["search"])))
if "name" in query_args:
filters.append(Category.name.like("%{filter}%".format(filter=query_args["name"])))
if "slug" in query_args:
filters.append(Category.slug.like("%{filter}%".format(filter=query_args["slug"])))
if filters:
query = query.filter(*filters)
# Apply sorting
order_by = Category.id
if "sort" in query_args and query_args["sort"]:
column, direction = PaginationHelper.decode_sort(query_args["sort"])
if column in set(Category.__table__.columns.keys()):
order_by = getattr(Category, column)
order_by = collate(order_by, "NOCASE")
if direction == PaginationHelper.SORT_DESCENDING:
order_by = desc(order_by)
query = query.order_by(order_by)
pagination_helper = PaginationHelper(
request,
query=query,
resource_for_url="api.categories",
key_name="results",
schema=category_schema,
query_args=query_args,
)
result = pagination_helper.paginate_query()
return result
def all_tags(
user: orm_users.User = None,
feed: orm_feeds.Feed = None,
flag: bool = True,
display: bool = True,
) -> typing.List[orm_feeds.Tag]:
q = sqla.select(orm_feeds.Tag, ).order_by(
sqla.collate(orm_feeds.Tag.Name, "NOCASE"), )
if user:
q = q.where(orm_feeds.Tag.UserID == user.UserID)
if feed:
q_where = orm_feeds.Tag.feeds.any(
orm_feeds.Feed.FeedID == feed.FeedID, )
if not flag:
q_where = ~q_where
q = q.where(q_where)
if display:
q = q.join(orm_feeds.Tag.feeds).join(
orm_feeds.Feed.users.and_(
orm_users.User.UserID == user.UserID, )).distinct()
with model.get_session() as session:
return [e[0] for e in session.execute(q)]
class CitationForm(Form):
date = TextField('Date', validators=[DataRequired()])
circa = BooleanField('Circa')
author = TextField('Author', validators=[DataRequired(), Length(max=50)])
# note: the source selection has had ".filter_by(archived=False)" removed
# as on editing the citation, the source will become the first option
# on the drop down list. Not an acceptable solution.
# Would need a more sophisticated sql query to remove
# the archived sources and still leave the already selected option.
source = QuerySelectField(query_factory=lambda: db.session.query(
Source).order_by(asc(collate(Source.name, 'NOCASE'))).all(),
get_pk=lambda a: a.id,
get_label=lambda a: a.name)
vol_page = TextField('Volume/Page', validators=[Length(max=10)])
edition = TextField('Edition', validators=[Length(max=10)])
quote = TextAreaField('Quote', validators=[])
notes = TextAreaField('Notes', validators=[])
archived = BooleanField('Archived')
def getattr(self, name):
return getattr(self, name)
def __init__(self, *args, **kwargs):
super(CitationForm, self).__init__(*args, **kwargs)
self.user = None
def validate(self):
initial_validation = super(CitationForm, self).validate()
if not initial_validation:
return False
return True
def on_search(args):
# TODO: Decode via actual tty encoding
try:
q = args.q[0].decode("utf-8")
except AttributeError:
q = args.q[0]
pkg_names = set()
# First, check for exact case-insensitive name matches
for pkg in session.query(Package).filter(collate(Package.name,"NOCASE")==q).all():
pkg_names.add(pkg.name)
# Check for substring name matches
for pkg in session.query(Package).filter(Package.name.like(u('%{0}%').format(q))).all():
pkg_names.add(pkg.name)
# Check for description matches
for pkg in session.query(Package).filter(Package.description.like(u('%{0}%').format(q))).all():
pkg_names.add(pkg.name)
if len(pkg_names) == 0:
print_(u('No matching packages found.'))
return
# Nice column formatting
max_len_name = max( len(name) for name in pkg_names )
for pkg_name in sorted(pkg_names):
pkg = session.query(Package).get(pkg_name)
print_(u('{name:{max_len_name}} {version:10} {desc}'.format(name=pkg.name, version=pkg.version, desc=pkg.description, max_len_name=max_len_name)))
def showSportsItem(sports_id):
"""Shows sports items and who created them"""
sports = session.query(Sports).filter_by(id=sports_id).one()
creator = getUserInfo(sports.user_id)
items = session.query(
User, SportsItem).filter(User.id == SportsItem.user_id).filter(
SportsItem.sports_id == sports_id).order_by(
asc(collate(SportsItem.name, 'NOCASE'))).all()
if 'username' not in login_session:
return render_template('sportsitems.html',
items=items,
sports=sports,
creator=creator,
loggedin="no")
if 'username' not in login_session != login_session['user_id']:
return render_template('sportsitems.html',
items=items,
sports=sports,
creator=creator,
loggedin="no")
else:
return render_template('sportsitems.html',
items=items,
sports=sports,
creator=creator,
loggedin="yes")
def get(self, query_args):
query = Image.query
# Apply filters
filters = []
if "search" in query_args and query_args["search"]:
filters.append(
or_(
Image.title.like(
"%{filter}%".format(filter=query_args["search"])),
Image.original_filename.like(
"%{filter}%".format(filter=query_args["search"])),
))
if "title" in query_args:
filters.append(
Image.title.like(
"%{filter}%".format(filter=query_args["title"])))
if "original_filename" in query_args:
filters.append(
Image.original_filename.like("%{filter}%".format(
filter=query_args["original_filename"])))
if "author_id" in query_args:
filters.append(Post.author_id == query_args["author_id"])
if "post_id" in query_args:
filters.append(Post.post_id == query_args["post_id"])
if "created_at" in query_args:
filters.append(Post.created_at == query_args["created_at"])
if filters:
query = query.filter(*filters)
# Apply sorting
order_by = Image.id
if "sort" in query_args and query_args["sort"]:
column, direction = PaginationHelper.decode_sort(
query_args["sort"])
if column == "post.name":
query = query.join(Post, Image.post)
order_by = Post.name
elif column == "author.name":
query = query.join(User, Image.author)
order_by = User.name
elif column in set(Image.__table__.columns.keys()):
order_by = getattr(Image, column)
order_by = collate(order_by, "NOCASE")
if direction == PaginationHelper.SORT_DESCENDING:
order_by = desc(order_by)
query = query.order_by(order_by)
pagination_helper = PaginationHelper(
request,
query=query,
resource_for_url="api.images",
key_name="results",
schema=image_schema,
query_args=query_args,
)
result = pagination_helper.paginate_query()
return result
def get_projects():
data = []
for p in db_session.query(Project).order_by(
collate(Project.sname, 'NOCASE')).all():
d = dict(p)
d['project_start'] = date2dat(d['project_start'])
d['project_end'] = date2dat(d['project_end'])
if d['budget'] and d['budget'] > 0:
d['fte'] = d['budget'] / 1680.0
data.append(d)
return flask_response(data)
def get(cls, domain, name):
"""
Get the requested site entry
@param domain: Domain name
@type domain: Domain
@param name: Site name
@type name: str
@rtype: Domain
"""
Site = cls
return Session.query(Site).filter(Site.domain == domain).filter(collate(Site.name, 'NOCASE') == name).first()
def showMenu(restaurant_id):
"""Show all the menu items for a restaurant.
Args:
restaurant_id: (integer) Id of the restaurant in the database.
"""
restaurant = session.query(Restaurant).filter_by(id=restaurant_id).one()
items = session.query(MenuItem).filter_by(restaurant_id=restaurant_id).order_by(collate(MenuItem.name, 'NOCASE')).all()
if len(items) == 0:
flash('There are no menu items for this restaurant.')
return render_template('menu.html', restaurant=restaurant, items=items)
def showRestaurants():
"""Show all the restuarants in the database."""
'''
restaurant = session.query(Restaurant).filter_by(id=restaurant_id).one()
items = session.query(MenuItem).filter_by(restaurant_id=restaurant_id)
return render_template('menu.html', restaurant=restaurant, items=items)
'''
restaurants = session.query(Restaurant).order_by(collate(Restaurant.name, 'NOCASE')).all()
if len(restaurants) == 0:
flash('There are no restaurants in the database.')
return render_template('restaurants.html', restaurants=restaurants)
def get(cls, domain, name):
"""
Get the requested site entry
@param domain: Domain name
@type domain: Domain
@param name: Site name
@type name: str
@rtype: Domain
"""
Site = cls
return Session.query(Site).filter(Site.domain == domain).filter(
collate(Site.name, 'NOCASE') == name).first()
def get(self, query_args):
query = User.query
# Apply filters
filters = []
if "search" in query_args and query_args["search"]:
filters.append(
or_(
User.name.like("%{filter}%".format(filter=query_args["search"])),
User.email.like("%{filter}%".format(filter=query_args["search"])),
)
)
if "name" in query_args and query_args["name"]:
filters.append(User.name.like("%{filter}%".format(filter=query_args["name"])))
if "email" in query_args and query_args["email"]:
filters.append(User.email.like("%{filter}%".format(filter=query_args["email"])))
if "location" in query_args and query_args["location"]:
filters.append(User.location.like("%{filter}%".format(filter=query_args["location"])))
if "confirmed" in query_args:
filters.append(User.confirmed == query_args["confirmed"])
if "role_id" in query_args and query_args["role_id"]:
filters.append(User.role_id == query_args["role_id"])
if "created_at" in query_args and query_args["created_at"]:
filters.append(User.created_at == query_args["created_at"])
if filters:
query = query.filter(*filters)
# Apply sorting
order_by = User.id
if "sort" in query_args and query_args["sort"]:
column, direction = PaginationHelper.decode_sort(query_args["sort"])
if column == "role.name":
query = query.join(Role, User.role)
order_by = Role.name
elif column in set(User.__table__.columns.keys()):
order_by = getattr(User, column)
order_by = collate(order_by, "NOCASE")
if direction == PaginationHelper.SORT_DESCENDING:
order_by = desc(order_by)
query = query.order_by(order_by)
pagination_helper = PaginationHelper(
request,
query=query,
resource_for_url="api.users",
key_name="results",
schema=user_schema,
query_args=query_args,
)
result = pagination_helper.paginate_query()
return result
def all_langs(
user: orm_users.User = None,
) -> typing.List[schema_feeds.Language]:
q = sqla.select(
orm_feeds.Feed.Language,
).order_by(
sqla.collate(orm_feeds.Feed.Language, "NOCASE"),
).distinct()
if user:
q = feeds_filter.filter_display(q, user)
with model.get_session() as session:
return [schema_feeds.Language[e["Language"]] for e in session.execute(q)]
def get(self, query_args):
query = Role.query
filters = []
if "search" in query_args and query_args["search"]:
filters.append(
Role.name.like(
"%{filter}%".format(filter=query_args["search"])))
if "name" in query_args:
filters.append(
Role.name.like("%{filter}%".format(filter=query_args["name"])))
if "default" in query_args:
filters.append(Role.default == query_args["default"])
if "permissions" in query_args:
filters.append(Role.permissions == query_args["permissions"])
if filters:
query = query.filter(*filters)
# Apply sorting
order_by = Role.id
if "sort" in query_args and query_args["sort"]:
column, direction = PaginationHelper.decode_sort(
query_args["sort"])
if column in set(
Role.__table__.columns.keys() +
["follow", "comment", "write", "moderate", "admin"]):
order_by = getattr(Role, column)
order_by = collate(order_by, "NOCASE")
if direction == PaginationHelper.SORT_DESCENDING:
order_by = desc(order_by)
query = query.order_by(order_by)
pagination_helper = PaginationHelper(
request,
query=query,
resource_for_url="api.roles",
key_name="results",
schema=role_schema,
query_args=query_args,
)
result = pagination_helper.paginate_query()
return result
class HeadwordForm(Form):
headword = TextField('Headword', validators=[DataRequired(),
Length(max=50)])
definition = TextAreaField('Definition', validators=[DataRequired()])
see = TextField('See', validators=[])
pronunciation = TextField('Pronunciation', validators=[])
notes = TextAreaField('Notes', validators=[])
archived = BooleanField('Archived')
word_class = QuerySelectField(
query_factory=lambda: db.session.query(Word_class)
.order_by(asc(collate(Word_class.name, 'NOCASE'))).all(),
get_pk =lambda a: a.id,
get_label =lambda a: a.name,
allow_blank=True,
blank_text="none" )
data_set = QuerySelectField(
query_factory=lambda: db.session.query(Data_set)
.order_by(asc(collate(Data_set.name, 'NOCASE'))).all(),
get_pk =lambda a: a.id,
get_label =lambda a: a.name,
allow_blank=True,
blank_text="none")
homonym_number = QuerySelectField(
query_factory=lambda: db.session.query(Homonym_number)
.order_by(asc(collate(Homonym_number.name, 'NOCASE'))).all(),
get_pk =lambda a: a.id,
get_label =lambda a: a.name,
allow_blank=True,
blank_text="none" )
sense_number = QuerySelectField(
query_factory=lambda: db.session.query(Sense_number)
.order_by(asc(collate(Sense_number.name, 'NOCASE'))).all(),
get_pk =lambda a: a.id,
get_label =lambda a: a.name,
allow_blank=True,
blank_text="none" )
origin = QuerySelectField(
query_factory=lambda: db.session.query(Origin)
.order_by(asc(collate(Origin.name, 'NOCASE'))).all(),
get_pk =lambda a: a.id,
get_label =lambda a: a.name,
allow_blank=True,
blank_text="none" )
register = QuerySelectField(
query_factory=lambda: db.session.query(Register)
.order_by(asc(collate(Register.name, 'NOCASE'))).all(),
get_pk =lambda a: a.id,
get_label =lambda a: a.name,
allow_blank=True,
blank_text="none" )
domain = QuerySelectField(
query_factory=lambda: db.session.query(Domain)
.order_by(asc(collate(Domain.name, 'NOCASE'))).all(),
get_pk =lambda a: a.id,
get_label =lambda a: a.name,
allow_blank=True,
blank_text="none" )
region = QuerySelectField(
query_factory=lambda: db.session.query(Region).filter_by(archived=False)
.order_by(asc(collate(Region.name, 'NOCASE'))).all(),
get_pk =lambda a: a.id,
get_label =lambda a: a.name,
allow_blank=True,
blank_text="none" )
flag = QuerySelectField(
query_factory=lambda: db.session.query(Flag).filter_by(archived=False)
.order_by(asc(collate(Flag.name, 'NOCASE'))).all(),
get_pk =lambda a: a.id,
get_label =lambda a: a.name,
allow_blank=True,
blank_text="none" )
def getattr(self, name):
return getattr(self, name)
def __init__(self, *args, **kwargs):
super(HeadwordForm, self).__init__(*args, **kwargs)
self.user = None
def validate(self):
initial_validation = super(HeadwordForm, self).validate()
if not initial_validation:
return False
return True
def case_insensitive_sqlite(element, compiler, **kw):
arg1, = list(element.clauses)
return compiler.process(collate(arg1, 'NOCASE'), **kw)
def all():
return Category.query.order_by(collate(Category.name, 'NOCASE'))
def getAllItems(self):
"""Convenience method to get all items regardless of category.
"""
return self.db_session.query(Item) \
.order_by(collate(Item.name, 'NOCASE')).all()
def order_title(q: sqla.sql.Select, ) -> sqla.sql.Select:
q = q.order_by(sqla.collate(orm_feeds.Feed.Title, "NOCASE"), )
return q
def get(self, query_args):
query = Post.query
# Apply filters
filters = []
if "search" in query_args and query_args["search"]:
filters.append(
Post.title.like(
"%{filter}%".format(filter=query_args["search"])))
if "title" in query_args:
filters.append(
Post.title.like(
"%{filter}%".format(filter=query_args["title"])))
if "sub_title" in query_args:
filters.append(
Post.title.like(
"%{filter}%".format(filter=query_args["sub_title"])))
if "slug" in query_args:
filters.append(
Post.slug.like("%{filter}%".format(filter=query_args["slug"])))
if "category_id" in query_args:
filters.append(
PostCategory.category_id == query_args["category_id"])
if "author_id" in query_args:
filters.append(PostAuthor.user_id == query_args["author_id"])
if "created_at" in query_args:
filters.append(Post.created_at == query_args["created_at"])
if "excluded_ids" in query_args:
filters.append(Post.id.notin_(query_args["excluded_ids"]))
if filters:
query = query.filter(*filters)
# Apply sorting
order_by = Post.id
if "sort" in query_args and query_args["sort"]:
column, direction = PaginationHelper.decode_sort(
query_args["sort"])
if column == "category.name":
query = (query.join(PostCategory, Post.categories).join(
Category, PostCategory.category).filter(
PostCategory.primary.is_(True)))
order_by = Category.name
elif column == "author.name":
query = (query.join(PostAuthor, Post.authors).join(
User,
PostAuthor.user).filter(PostAuthor.primary.is_(True)))
order_by = User.name
elif column in set(Post.__table__.columns.keys()):
order_by = getattr(Post, column)
order_by = collate(order_by, "NOCASE")
if direction == PaginationHelper.SORT_DESCENDING:
order_by = desc(order_by)
query = query.order_by(order_by)
pagination_helper = PaginationHelper(
request,
query=query,
resource_for_url="api.posts",
key_name="results",
schema=post_schema,
query_args=query_args,
)
result = pagination_helper.paginate_query()
return result
def attendants(request):
attendants = Session.DBSession.query(Attendant).order_by(
collate(Attendant.lname, 'NOCASE'))
return {'attendants': attendants, 'path': request.path_info}
def apply_custom_query(search_type, db, keywords, filters):
keyword_conditions = []
negation_argument = None
for i, each_keyword in enumerate(keywords):
if each_keyword == 'NOT':
negation_argument = keywords[i + 1]
if search_type == 'package':
each_keyword_condition = Package.name.notlike(
f'%{negation_argument}%')
elif search_type == 'channel':
each_keyword_condition = collate(
Channel.name,
"und-x-icu").notlike(f'%{negation_argument}%')
else:
if each_keyword != negation_argument:
if search_type == 'package':
each_keyword_condition = Package.name.ilike(
f'%{each_keyword}%')
elif search_type == 'channel':
each_keyword_condition = collate(
Channel.name, "und-x-icu").ilike(f'%{each_keyword}%')
keyword_conditions.append(each_keyword_condition)
query = db.filter(and_(*keyword_conditions))
for each_filter in filters:
key, values = each_filter
negate = False
if key.startswith('-'):
key = key[1:]
negate = True
each_filter_conditions = []
for each_val in values:
each_val = each_val.strip('"').strip("'")
if search_type == 'package':
if key == 'channel':
each_val_condition = collate(
Channel.name, "und-x-icu").ilike(f'%{(each_val)}%')
elif key == 'description':
each_val_condition = Package.description.contains(each_val)
elif key == 'summary':
each_val_condition = Package.summary.contains(each_val)
elif key == 'format':
each_val_condition = cast(PackageVersion.package_format,
String).ilike(f'%{(each_val)}%')
elif key == 'platform':
each_val_condition = Package.platforms.ilike(
f'%{(each_val)}%')
elif key == 'version':
each_val_condition = PackageVersion.version.ilike(
f'%{(each_val)}%')
elif key == 'uploader':
each_val_condition = User.username.ilike(f'%{(each_val)}%')
elif search_type == 'channel':
if key == 'description':
each_val_condition = Channel.description.contains(each_val)
elif key == 'private':
each_val_condition = Channel.private.is_(
bool(distutils.util.strtobool(each_val)))
each_filter_conditions.append(each_val_condition)
if negate:
query = query.filter(not_(or_(*each_filter_conditions)))
else:
query = query.filter(or_(*each_filter_conditions))
return query
def getCategories(self):
"""Convenience method to get all categories.
"""
return self.db_session.query(Category) \
.order_by(collate(Category.name, 'NOCASE')) \
.all()
def showSports():
"""This query will show all sports in asc order"""
sports = session.query(Sports).order_by(asc(collate(Sports.name,
'NOCASE')))
return render_template(TEMPLATE_PATH + 'sports.html', sports=sports)
