class Student(db.Model):
__tablename__ = 'student'
id = Column(Integer, primary_key=True)
contextual_id = column_property(
func.md5(
bindparam('context', value='', type_=String) +
func.cast(id, String)))
name = Column(String(64), index=True, nullable=False)
address = Column(String(128), index=False, nullable=True)
phone = Column(String(35), nullable=True)
home_phone = Column(String(35), nullable=True)
email = Column(CIText(64, True), nullable=True)
created = Column(DateTime, server_default='now()')
guardian_number = MapColumn('home_phone')
phone_numbers = MapColumn(['phone', 'home_phone'])
contact_info = MapColumn({
'phone': 'phone',
'home_phone': 'home_phone',
'email': 'email'
})
teachers = relationship(
"Teacher",
secondary='teacher_to_student',
primaryjoin="Student.id == teacher_to_student.c.student_id",
secondaryjoin="teacher_to_student.c.teacher_id == Teacher.id")
first_name = column_property(func.split_part(func.trim(name), " ", 1))
def get_profiles():
error, verification_token = get_token(request.headers)
if error:
return error
query = request.args.get('query')
verification_email_id = VerificationToken.query.filter(
VerificationToken.token == verification_token.token).value(
VerificationToken.email_id)
return jsonify(
profiles_schema.dump(
matching_profiles(query).order_by(
# Is this the logged-in user's profile? If so, return it first (false)
Profile.verification_email_id != verification_email_id,
# Get the last word in the name.
# Won't work with suffixes.
func.split_part(
Profile.name,
' ',
func.array_length(
func.string_to_array(
func.regexp_replace(
Profile.name, '(,|MD).*',
''), # Remove suffixes after comma and MD
' '),
1 # How many words in the name
)))))
def rollup_test(request):
id = request.params['job']
one = func.split_part(Run.test_id, '/', 4).alias('one')
two = func.split_part(Run.test_id, '/', 5).alias('two')
passed = (Run.result == 'PASS').alias('PASS')
failed = (Run.result == 'FAIL').alias('FAIL')
abort = (Run.result == 'ABORT').alias('ABORT')
timeout = (Run.result == 'TIMEOUT').alias('TIMEOUT')
rollup_cols = [one, two]
q = DBSession.query(passed, failed, abort, timeout, *rollup_cols) \
.where(Run.job_id == id)
q = rollup(DBSession, q, *rollup_cols)
columns = [c['name'] for c in q.column_descriptions]
return dict(cols=columns, rows=q.all())
def rollup_test(request):
id = request.params['job']
one = func.split_part(Run.test_id, '/', 4).alias('one')
two = func.split_part(Run.test_id, '/', 5).alias('two')
passed = (Run.result == 'PASS').alias('PASS')
failed = (Run.result == 'FAIL').alias('FAIL')
abort = (Run.result == 'ABORT').alias('ABORT')
timeout = (Run.result == 'TIMEOUT').alias('TIMEOUT')
rollup_cols = [one, two]
q = DBSession.query(passed, failed, abort, timeout, *rollup_cols) \
.where(Run.job_id == id)
q = rollup(DBSession, q, *rollup_cols)
columns = [c['name'] for c in q.column_descriptions]
return dict(cols=columns, rows=q.all())
async def count_routes(db, operator_id: int) -> int:
""" Count how many routes an operator has """
# This query uses route_desc, which contains the official route license number
# this field is separated by minus, and the first item is the route's
# unique license number (after that it's "direction code" and "alternative".
# Using PostgreSQL's split_part function, count, and distinct we can use
# this to quickly count the routes per operator.
query = select([func.count(func.distinct(func.split_part(Route.route_desc, '-', 1)))])
return await db.scalar(query.where(Route.agency_id == str(operator_id)))
def split_vo(dialect, column, return_vo=False):
"""
Utility script for extracting the name and VO from InternalAccount/Scope entries in the DB.
:param dialect: The dialct of the DB.
:param column: The column to perform the operation on.
:param return_vo: If True, return the 3 characters after the '@' symbol, else return everything before it.
"""
if dialect == 'postgresql':
if return_vo:
return func.split_part(column, bindparam('split_character'), bindparam('int_2'))
else:
return func.split_part(column, bindparam('split_character'), bindparam('int_1'))
else:
# Dialects other than postgresql haven't been tested
i = func.INSTR(column, bindparam('split_character'))
if return_vo:
return func.SUBSTR(column, i + 1)
else:
return func.SUBSTR(column, bindparam('int_1'), i - 1)
def upgrade():
# Add new nullable columns
op.add_column('users', Column('floor', SmallInteger(), nullable=True))
op.add_column('users', Column('seat', SmallInteger(), nullable=True))
# Migrate data
query = users_table.update().values(floor=cast(
func.split_part(users_table.c.location, LOCATION_DELIMETER, 1),
Integer),
seat=cast(
func.split_part(
users_table.c.location,
LOCATION_DELIMETER, 2),
Integer))
op.execute(query)
# Make new columns non-nullable
op.alter_column('users', 'floor', nullable=False)
op.alter_column('users', 'seat', nullable=False)
# Drop legacy column
op.drop_column('users', 'location')
def get_profiles():
error, verification_token = get_token(request.headers)
if error:
return error
query = request.args.get('query')
degrees = request.args.get('degrees', '')
affiliations = request.args.get('affiliations', '')
page = int(request.args.get('page', 1))
start, end = pagination(page)
verification_email_id = VerificationToken.query.filter(
VerificationToken.token == verification_token.token).value(
VerificationToken.email_id)
queryset = (
matching_profiles(query, degrees, affiliations).order_by(
# Is this the logged-in user's profile? If so, return it first (false)
Profile.verification_email_id != verification_email_id,
# Get the last word in the name.
# Won't work with suffixes.
func.split_part(
Profile.name,
' ',
func.array_length(
func.string_to_array(
func.regexp_replace(
Profile.name, '(,|MD).*',
''), # Remove suffixes after comma and MD
' ',
),
1, # How many words in the name
),
),
).group_by(Profile.id))
return jsonify({
'profileCount': queryset.count(),
'profiles': profiles_schema.dump(queryset[start:end]),
})
def get_ordering(sorting):
last_name_sorting = func.split_part(
profile_class.name,
" ",
func.array_length(
func.string_to_array(profile_class.name, " "),
1, # Length in the 1st dimension
),
)
sort_options = {
"starred": [
desc(text("profile_star_count")),
desc(profile_class.date_updated),
],
"last_name_alphabetical": [asc(last_name_sorting)],
"last_name_reverse_alphabetical": [desc(last_name_sorting)],
"date_updated": [desc(profile_class.date_updated)],
}
if sorting not in sort_options:
raise InvalidPayloadError({"sorting": ["invalid"]})
return sort_options[sorting]
def username(cls):
return func.split_part(cls.email, '@', 1)
def name(cls):
return func.reverse(func.split_part(func.reverse(cls.location), '/',
1))
def get_dataset_srid_alchemy_expression(md: MetadataType,
default_crs: str = None):
doc = md.dataset_fields['metadata_doc'].alchemy_expression
if 'grid_spatial' not in md.definition['dataset']:
# Non-spatial product
return None
projection_offset = md.definition['dataset']['grid_spatial']
# Most have a spatial_reference field we can use directly.
spatial_reference_offset = projection_offset + ['spatial_reference']
spatial_ref = doc[spatial_reference_offset].astext
# When datasets have no CRS, optionally use this as default.
default_crs_expression = None
if default_crs:
if not default_crs.lower().startswith('epsg:'):
raise NotImplementedError(
"CRS expected in form of 'EPSG:1234'. Got: %r" % default_crs)
auth_name, auth_srid = default_crs.split(':')
default_crs_expression = select([
SPATIAL_REF_SYS.c.srid
]).where(func.lower(SPATIAL_REF_SYS.c.auth_name) == auth_name.lower(
)).where(SPATIAL_REF_SYS.c.auth_srid == int(auth_srid)).as_scalar()
expression = func.coalesce(
case(
[(
# If matches shorthand code: eg. "epsg:1234"
spatial_ref.op("~")(r"^[A-Za-z0-9]+:[0-9]+$"),
select([SPATIAL_REF_SYS.c.srid]).where(
func.lower(SPATIAL_REF_SYS.c.auth_name) ==
func.lower(func.split_part(spatial_ref, ':', 1))).where(
SPATIAL_REF_SYS.c.auth_srid == func.split_part(
spatial_ref, ':', 2).cast(Integer)).as_scalar())],
else_=None),
case(
[(
# Plain WKT that ends in an authority code.
# Extract the authority name and code using regexp. Yuck!
# Eg: ".... AUTHORITY["EPSG","32756"]]"
spatial_ref.op("~")
(r'AUTHORITY\["[a-zA-Z0-9]+", *"[0-9]+"\]\]$'),
select([SPATIAL_REF_SYS.c.srid]).where(
func.lower(SPATIAL_REF_SYS.c.auth_name) == func.lower(
func.substring(
spatial_ref,
r'AUTHORITY\["([a-zA-Z0-9]+)", *"[0-9]+"\]\]$'))
).where(SPATIAL_REF_SYS.c.auth_srid == func.substring(
spatial_ref, r'AUTHORITY\["[a-zA-Z0-9]+", *"([0-9]+)"\]\]$'
).cast(Integer)).as_scalar())],
else_=None),
# Some older datasets have datum/zone fields instead.
# The only remaining ones in DEA are 'GDA94'.
case([
(doc[(projection_offset + ['datum'])].astext
== 'GDA94', select([
SPATIAL_REF_SYS.c.srid
]).where(func.lower(SPATIAL_REF_SYS.c.auth_name) == 'epsg').where(
SPATIAL_REF_SYS.c.auth_srid == ('283' + func.abs(doc[
(projection_offset + ['zone'])].astext.cast(Integer))
).cast(Integer)).as_scalar())
],
else_=None),
default_crs_expression,
# TODO: Handle arbitrary WKT strings (?)
)
# print(as_sql(expression))
return expression
