def as_slug_expression(attr):
"""
Converts the given instrumented string attribute into an SQL expression
that can be used as a slug.
Slugs are identifiers for members in a collection that can be used in an
URL. We create slug columns by replacing non-URL characters with dashes
and lower casing the result. We need this at the ORM level so that we can
use the slug in a query expression.
"""
slug_expr = sa_func.replace(attr, ' ', '-')
slug_expr = sa_func.replace(slug_expr, '_', '-')
slug_expr = sa_func.lower(slug_expr)
return slug_expr
def as_slug_expression(attr):
"""
Converts the given instrumented string attribute into an SQL expression
that can be used as a slug.
Slugs are identifiers for members in a collection that can be used in an
URL. We create slug columns by replacing non-URL characters with dashes
and lower casing the result. We need this at the ORM level so that we can
use the slug in a query expression.
"""
slug_expr = sa_func.replace(attr, " ", "-")
slug_expr = sa_func.replace(slug_expr, "_", "-")
slug_expr = sa_func.lower(slug_expr)
return slug_expr
def find_track_instance_from_item(item, session):
if 'code' in item:
return session.query(Tracks).filter(
Tracks.code == item['code']).first()
elif 'name' in item:
return session.query(Tracks).filter(
Tracks.name == item['name']).first()
elif 'equibase_chart_name' in item:
return session.query(Tracks).filter(
func.replace(func.replace(
Tracks.equibase_chart_name, "'", ''), '&', '') == func.replace(
func.replace(item['equibase_chart_name'], "'", ''), '&',
'')).first()
else:
return
def update_templates_definitions(connection, migrator_id):
"""Updates assessment templates default_people value."""
template_table = sa.sql.table(
"assessment_templates",
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('template_object_type', sa.String(length=250), nullable=True),
sa.Column('default_people', sa.Text(), nullable=False),
sa.Column('updated_at', sa.DateTime, nullable=False),
sa.Column('modified_by_id', sa.Integer, nullable=True)
)
template_entities = connection.execute(
template_table.select().where(
template_table.c.template_object_type == "Control"
).where(template_table.c.default_people.ilike("%Primary Contacts%") |
template_table.c.default_people.ilike("%Secondary Contacts%"))
).fetchall()
if template_entities:
for old_role_name, new_role_name in ROLE_MAPPING.iteritems():
connection.execute(template_table.update().where(
template_table.c.template_object_type == "Control"
).values(
default_people=func.replace(
template_table.c.default_people, old_role_name, new_role_name
),
updated_at=datetime.datetime.utcnow(),
modified_by_id=migrator_id
))
if template_entities:
template_ids = [entity.id for entity in template_entities]
utils.add_to_objects_without_revisions_bulk(
connection, template_ids, "AssessmentTemplate", action="modified"
)
def search():
q = request.args['q']
email_exact = User.query.filter(User.email == q).one_or_none()
if email_exact:
return redirect(url_for('.user', user_id=email_exact.id))
gc_exact = GoCardlessPayment.query.filter(
(GoCardlessPayment.gcid == q) | (GoCardlessPayment.mandate == q)).one_or_none()
if gc_exact:
return redirect(url_for('.payment', payment_id=gc_exact.id))
stripe_exact = StripePayment.query.filter(StripePayment.chargeid == q).one_or_none()
if stripe_exact:
return redirect(url_for('.payment', payment_id=stripe_exact.id))
bank_exact = BankPayment.query.filter(BankPayment.bankref == q).one_or_none()
if bank_exact:
return redirect(url_for('.payment', payment_id=bank_exact.id))
email_query = to_query(q.replace('@', ' '))
# Careful with the following query. It'll stop using the indexes if you change the
# functions applied to the indexed columns. Which isn't really the end of the world given
# how small our dataset is, but I spent ages trying to work out how to get Alembic to add
# those indexes. So humour me.
results = User.query.filter(
func.to_tsvector('simple', User.name).match(to_query(q)) |
(func.to_tsvector('simple', func.replace(User.email, '@', ' ')).match(email_query))
)
return render_template('admin/search-results.html', q=q, results=results)
def update_recipients(connection, migrator_id):
"""Updates recipients field for Control model. """
control_table = sa.sql.table(
"controls",
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('recipients', sa.String(length=250), nullable=True),
sa.Column('updated_at', sa.DateTime, nullable=False),
sa.Column('modified_by_id', sa.Integer, nullable=True)
)
# replace all None data with empty string for recipients field
for old_role_name, new_role_name in ROLE_MAPPING.iteritems():
connection.execute(control_table.update().values(
recipients=func.replace(control_table.c.recipients,
old_role_name, new_role_name),
updated_at=datetime.datetime.utcnow(),
modified_by_id=migrator_id
))
control_entities = connection.execute(
control_table.select().where(control_table.c.recipients != '')
).fetchall()
if control_entities:
control_ids = [entity.id for entity in control_entities]
utils.add_to_objects_without_revisions_bulk(connection, control_ids,
"Control", action="modified")
def sample_metadata(v_sample):
"""MetaData for a given sample.
Args: Sample in the format: `BB_940`
Returns a json dictionary of sample metadata in the format
{
AGE: 24,
BBTYPE: "I",
ETHNICITY: "Caucasian",
GENDER: "F",
LOCATION: "Beaufort/NC",
SAMPLEID: 940
}
"""
list1 = sess.query(
c_samples_metadata.AGE,
c_samples_metadata.BBTYPE,
c_samples_metadata.ETHNICITY,
c_samples_metadata.GENDER,
c_samples_metadata.LOCATION, c_samples_metadata.SAMPLEID). \
filter(c_samples_metadata.SAMPLEID == func.replace(v_sample, 'BB_', ''))
d1 = {
'AGE': list1[0][0],
'BBTYPE': list1[0][1],
'ETHNICITY': list1[0][2],
'GENDER': list1[0][3],
'LOCATION': list1[0][4],
'SAMPLEID': list1[0][5]
}
return jsonify(d1)
def update_templates_definitions(connection, migrator_id):
"""Updates assessment templates default_people value."""
template_table = all_models.AssessmentTemplate.__table__
template_entities = connection.execute(template_table.select().where(
template_table.c.template_object_type == "Control").where(
template_table.c.default_people.ilike("%Primary Contacts%")
| template_table.c.default_people.ilike("%Secondary Contacts%"))
).fetchall()
if template_entities:
for old_role_name, new_role_name in ROLE_MAPPING.iteritems():
connection.execute(template_table.update().where(
template_table.c.template_object_type == "Control").values(
default_people=func.replace(
template_table.c.default_people, old_role_name,
new_role_name),
updated_at=datetime.datetime.utcnow(),
modified_by_id=migrator_id))
if template_entities:
template_ids = [entity.id for entity in template_entities]
utils.add_to_objects_without_revisions_bulk(connection,
template_ids,
"AssessmentTemplate",
action="modified")
def _update_domains_on_server_delete(self, server):
"""
For performance, manually prepare a bulk update query to
update all NS records for all existing domains that need
updating of their corresponding NS record in Record table
"""
# find a replacement server
replacement_server_name = None
servers = self.central_service.find_servers(self.admin_context)
for replacement in servers:
if replacement["id"] != server["id"]:
replacement_server_name = replacement["name"]
break
LOG.debug(
"This existing server name will be used to update existing"
" SOA records upon server delete: %s " % replacement_server_name
)
# NOTE: because replacement_server_name came from central storage
# it has the trailing period
# Execute the manually prepared query
# A TX is required for, at the least, SQLite.
try:
self.session.begin()
# first delete affected NS records
self.session.query(models.Record).filter_by(type="NS", designate_id=server["id"]).delete()
# then update all SOA records as necessary
# Do the SOA last, ensuring we don't trigger a
# NOTIFY before the NS records are in place.
#
# Update the content field of every SOA record that
# has the deleted server name as part of its
# 'content' field to reflect the name of another
# server that exists
# both server['name'] and replacement_server_name
# have trailing period so we are fine just doing the
# substitution without striping trailing period
self.session.execute(
models.Record.__table__.update()
.where(
and_(
models.Record.__table__.c.type == "SOA",
models.Record.__table__.c.content.like("%s%%" % server["name"]),
)
)
.values(
content=func.replace(models.Record.__table__.c.content, server["name"], replacement_server_name)
)
)
except Exception:
with excutils.save_and_reraise_exception():
self.session.rollback()
else:
self.session.commit()
def update_templates_definitions(connection, migrator_id):
"""Updates assessment templates default_people value."""
template_table = sa.sql.table(
"assessment_templates", sa.Column('id', sa.Integer(), nullable=False),
sa.Column('template_object_type', sa.String(length=250),
nullable=True),
sa.Column('default_people', sa.Text(), nullable=False),
sa.Column('updated_at', sa.DateTime, nullable=False),
sa.Column('modified_by_id', sa.Integer, nullable=True))
template_entities = connection.execute(template_table.select().where(
template_table.c.template_object_type == "Control").where(
template_table.c.default_people.ilike("%Primary Contacts%")
| template_table.c.default_people.ilike("%Secondary Contacts%"))
).fetchall()
if template_entities:
for old_role_name, new_role_name in ROLE_MAPPING.iteritems():
connection.execute(template_table.update().where(
template_table.c.template_object_type == "Control").values(
default_people=func.replace(
template_table.c.default_people, old_role_name,
new_role_name),
updated_at=datetime.datetime.utcnow(),
modified_by_id=migrator_id))
if template_entities:
template_ids = [entity.id for entity in template_entities]
utils.add_to_objects_without_revisions_bulk(connection,
template_ids,
"AssessmentTemplate",
action="modified")
def get_by_username(cls, request, username):
session = get_session(request)
lhs = func.replace(cls.username, '.', '')
rhs = username.replace('.', '')
return session.query(cls).filter(
func.lower(lhs) == rhs.lower()).first()
def get_by_username_or_email(cls, request, username, email):
session = get_session(request)
lhs = func.replace(cls.username, '.', '')
rhs = username.replace('.', '')
return session.query(cls).filter(
or_(func.lower(lhs) == rhs.lower(), cls.email == email)).first()
def get_by_username(cls, request, username):
session = get_session(request)
lhs = func.replace(cls.username, '.', '')
rhs = username.replace('.', '')
return session.query(cls).filter(
func.lower(lhs) == rhs.lower()
).first()
def edit_project(project_id):
project = Project.query.get(project_id)
if not authorize.update(project):
return render_template("401.html")
form = EditProject()
# Build the url ourselves.
base_url = url_for("main.index")
project_url = base_url + f"#projects/{project_id}/jobs"
if form.validate_on_submit():
# Rename project path
path = project.path
new_path = os.path.join(os.path.dirname(path), form.name.data)
# Make sure project name doesn't exist
exists = Project.query.filter(
Project.name == form.name.data).one_or_none()
if exists is not None:
flash(f"A project with the name {form.name.data} already exists.")
return redirect(
url_for("projects.edit_project", project_id=project_id))
# Need to change this to query, but this will work
# without modifying the DB for now.
job_ids = [x.id for x in project.jobs]
# Update job paths
Job.query.filter(Job.id.in_(job_ids)).update(
{Job.path: func.replace(Job.path, path, new_path)},
synchronize_session=False,
)
# Perform rename
os.rename(path, new_path)
# Update project information
project.path = new_path
project.name = form.name.data
project.description = form.notes.data
db.session.commit()
flash("Project updated successfully.", "successs")
return redirect(project_url)
elif request.method == "GET":
form.name.data = project.name
form.notes.data = project.description
return render_template(
"jobs/edit_job.html",
title=f"Edit Project {project_id}",
form=form,
back_url=project_url,
)
def _update_domains_on_server_delete(self, server):
"""
For performance, manually prepare a bulk update query to
update all NS records for all existing domains that need
updating of their corresponding NS record in Record table
"""
# find a replacement server
replacement_server_name = None
servers = self.central_service.find_servers(self.admin_context)
for replacement in servers:
if replacement['id'] != server['id']:
replacement_server_name = replacement['name']
break
LOG.debug("This existing server name will be used to update existing"
" SOA records upon server delete: %s "
% replacement_server_name)
# NOTE: because replacement_server_name came from central storage
# it has the trailing period
# Execute the manually prepared query
# A TX is required for, at the least, SQLite.
try:
self.session.begin()
# first delete affected NS records
self.session.query(models.Record)\
.filter_by(type='NS', designate_id=server['id'])\
.delete()
# then update all SOA records as necessary
# Do the SOA last, ensuring we don't trigger a
# NOTIFY before the NS records are in place.
#
# Update the content field of every SOA record that
# has the deleted server name as part of its
# 'content' field to reflect the name of another
# server that exists
# both server['name'] and replacement_server_name
# have trailing period so we are fine just doing the
# substitution without striping trailing period
self.session.execute(models.Record.__table__
.update()
.where(and_(models.Record.__table__.c.type == "SOA",
models.Record.__table__.c.content.like
("%s%%" % server['name'])))
.values(content=func.replace(
models.Record.__table__.c.content,
server['name'],
replacement_server_name)))
except Exception:
with excutils.save_and_reraise_exception():
self.session.rollback()
else:
self.session.commit()
def music(artist, album):
title = "Music"
IPStore(title)
pagetype = "Album"
songs = Music.query.filter(
func.lower(func.replace(Music.artist, ' ', '')) == func.lower(
func.replace(artist, '-', ''))).filter(
func.lower(func.replace(Music.album, ' ', '')) == func.lower(
func.replace(album, '-', ''))).order_by(
Music.trackno.asc()).all()
header = (songs[0].artist).replace('-', ' ')
album = (songs[0].album).replace('-', ' ')
return render_template('pages/music.html',
title=title,
songs=songs,
header=header,
album=album,
pagetype=pagetype)
def __alterBatchEntries(self, jobID, old, new, flag, nselector):
"""
Replace within one JobID all entries matching old with new, if they do
not match the negative selector.
This is used to alter batch entries that would otherwise take a long
time to process. E.g. a batch job with a lot of the same accession
numbers without version numbers would take a long time because
mutalyzer would fetch the file from the NCBI for each entry. A
database update over all entries with the same accession number speeds
up the job considerably.
Example:
NM_002001(FCER1A_v001):c.1A>C ; this would result in the continuous
fetching of the reference because no version number is given.
In this case the arguments would be:
- old ; NM_002001
- new ; NM_002001.2
- nselector ; NM_002001.
The nselector is used to prevent the replacement of
false positives. e.g. NM_002001.1(FCER1A_v001):c.1A>C should not
be replaced. For this reason, any items starting with the nselector
value are ignored.
@arg jobID:
@type jobID:
@arg old:
@type old:
@arg new:
@type new:
@arg flag:
@type flag:
@arg nselector:
@type nselector:
"""
#query = '''UPDATE batch_queue_items
# SET item = REPLACE(item, :old, :new),
# flags = flags || :flag
# WHERE batch_job_id = :batch_job_id
# AND NOT item LIKE :nselector%'''
#parameters = {'batch_job_id': jobID,
# 'old': old,
# 'new': new,
# 'flag': flag,
# 'nselector': nselector}
#session.execute(query, parameters)
BatchQueueItem.query \
.filter_by(batch_job_id=jobID) \
.filter(BatchQueueItem.item.startswith(old),
~BatchQueueItem.item.startswith(nselector)) \
.update({'item': func.replace(BatchQueueItem.item, old, new),
'flags': BatchQueueItem.flags + flag},
synchronize_session=False)
session.commit()
def __alterBatchEntries(self, jobID, old, new, flag, nselector) :
"""
Replace within one JobID all entries matching old with new, if they do
not match the negative selector.
This is used to alter batch entries that would otherwise take a long
time to process. E.g. a batch job with a lot of the same accession
numbers without version numbers would take a long time because
mutalyzer would fetch the file from the NCBI for each entry. A
database update over all entries with the same accession number speeds
up the job considerably.
Example:
NM_002001(FCER1A_v001):c.1A>C ; this would result in the continuous
fetching of the reference because no version number is given.
In this case the arguments would be:
- old ; NM_002001
- new ; NM_002001.2
- nselector ; NM_002001.
The nselector is used to prevent the replacement of
false positives. e.g. NM_002001.1(FCER1A_v001):c.1A>C should not
be replaced. For this reason, any items starting with the nselector
value are ignored.
@arg jobID:
@type jobID:
@arg old:
@type old:
@arg new:
@type new:
@arg flag:
@type flag:
@arg nselector:
@type nselector:
"""
#query = '''UPDATE batch_queue_items
# SET item = REPLACE(item, :old, :new),
# flags = flags || :flag
# WHERE batch_job_id = :batch_job_id
# AND NOT item LIKE :nselector%'''
#parameters = {'batch_job_id': jobID,
# 'old': old,
# 'new': new,
# 'flag': flag,
# 'nselector': nselector}
#session.execute(query, parameters)
BatchQueueItem.query \
.filter_by(batch_job_id=jobID) \
.filter(BatchQueueItem.item.startswith(old),
~BatchQueueItem.item.startswith(nselector)) \
.update({'item': func.replace(BatchQueueItem.item, old, new),
'flags': BatchQueueItem.flags + flag},
synchronize_session=False)
session.commit()
def wfreq(v_sample):
"""Weekly Washing Frequency as a number.
Args: Sample in the format: `BB_940`
Returns an integer value for the weekly washing frequency `WFREQ`
"""
r1 = sess.query(c_samples_metadata.WFREQ). \
filter(c_samples_metadata.SAMPLEID == func.replace(v_sample, 'BB_', ''))
r2 = str(r1[0][0])
return r2
def get_by_username_or_email(cls, request, username, email):
session = get_session(request)
lhs = func.replace(cls.username, '.', '')
rhs = username.replace('.', '')
return session.query(cls).filter(
or_(
func.lower(lhs) == rhs.lower(),
cls.email == email
)
).first()
def get_applications_by_abn(self, abn):
return (
db
.session
.query(
Application
)
.filter(func.replace(Application.data['abn'].astext.label('abn'), ' ', '') == abn)
.filter(Application.status != 'deleted')
.all()
)
def download_data_table_by_recid(*args, **kwargs):
"""
Record ID download.
Downloads the latest data file given the url ``/download/submission/1231/Table 1/yaml`` or
by a particular version given ``/download/submission/1231/Table 1/1/yaml``. Routes:
``/table/<int:recid>/<path:table_name>/<file_format>``\n
``/table/<int:recid>/<path:table_name>/<int:version>/<file_format>``\n
``/table/<int:recid>/<path:table_name>/<int:version>/<file_format>/<rivet>``\n
:param args:
:param kwargs: inspire_id, table_name, version (optional), and file_format
:return: display_error or download_datatable depending on success of conversion
"""
recid = kwargs.pop('recid')
table_name = kwargs.pop('table_name')
rivet = kwargs.pop('rivet', '')
version_count, version_count_all = get_version_count(recid)
if 'version' in kwargs:
version = kwargs.pop('version')
else:
# If version not given explicitly, take to be latest allowed version (or 1 if there are no allowed versions).
version = version_count if version_count else 1
# Check for a user trying to access a version of a publication record where they don't have permissions.
if version_count < version_count_all and version == version_count_all:
abort(403)
datasubmission = None
original_table_name = table_name
try:
datasubmission = DataSubmission.query.filter_by(publication_recid=recid, version=version, name=table_name).one()
except NoResultFound:
if ' ' not in table_name:
try:
# Allow spaces in table_name to be omitted from URL.
datasubmission = DataSubmission.query.filter(
DataSubmission.publication_recid == recid,
DataSubmission.version == version,
func.replace(DataSubmission.name, ' ', '') == table_name
).one()
except NoResultFound:
pass
if not datasubmission:
return display_error(
title="No data submission found",
description="A data submission with record ID {0}, version {1} and table name '{2}' does not exist"
.format(recid, version, original_table_name)
)
return download_datatable(datasubmission, kwargs.pop('file_format'),
submission_id='{0}'.format(recid), table_name=datasubmission.name,
rivet_analysis_name=rivet)
def get_molecule_choices(session):
molecule_transition = session.query(
Acsis.molecule + ';' + func.replace(Acsis.transiti, " - ", " - "))
molecule_transition = molecule_transition.distinct()
molecule_transition = molecule_transition.filter(
Acsis.molecule != None).filter(Acsis.transiti != None).all()
molecule_transition.sort()
choices = []
for i in molecule_transition:
molecule, transition = i[0].split(';')
label = molecule + ' ' + transition.replace(' - ', '-')
choices += [(i[0], label)]
return choices
def downgrade():
"""Downgrade database schema and/or data back to the previous revision."""
for name in COMMENTABLE_SCOPING_TABLES:
commentable_table = sa.sql.table(
name, sa.Column('recipients', sa.String(length=250)))
op.execute(commentable_table.update().values(recipients=func.replace(
commentable_table.c.recipients, "Product Managers,Technical Leads,"
"Technical / Program Managers,"
"Legal Counsels,System Owners", "")))
connection = op.get_bind()
connection.execute(
sa.text(
"UPDATE {} SET recipients=TRIM(',' FROM recipients)".format(
commentable_table.name)))
def downgrade():
"""Downgrade database schema and/or data back to the previous revision."""
for name in COMMENTABLE_SCOPING_TABLES:
commentable_table = sa.sql.table(
name, sa.Column('recipients', sa.String(length=250)))
# remove new roles
op.execute(commentable_table.update().values(recipients=func.replace(
commentable_table.c.recipients,
"Assignee,Verifier,Compliance Contacts", "")))
# trim commas from all recipients lists
connection = op.get_bind()
connection.execute(
sa.text(
"UPDATE {} SET recipients=TRIM(',' FROM recipients)".format(
commentable_table.name)))
def get_context():
urlParsed = urlparse(request.json.get('url'))
urlSegments = urlParsed.path.split('/')
urlSegments = list(filter(None, urlSegments))
# print(urlSegments)
profile_id = ''
brand_id = ''
model_id_list = []
if urlSegments:
if urlSegments[0] == 'shoes':
# @app.route('/shoes/<shoe_brand>/<shoe_model>/')
if len(urlSegments) > 1:
brand_url_segment = urlSegments[1].replace('-', ' ')
brand = Brand.query.filter_by(name=brand_url_segment).first()
if brand:
brand_id = brand.id
if len(urlSegments) > 2:
model_url_segment = urlSegments[2].replace('-', ' ')
model_url_segment = model_url_segment.lower()
model = Item.query.filter(Item.brand_id == brand_id).filter(
func.replace(func.lower(Item.model), '-', ' ') ==
model_url_segment).all()
model.sort(reverse=False,
key=lambda x: getattr(x, 'gender_id'))
model_id_list = [x.id for x in model]
if urlSegments[0] == 'profile':
# @app.route('/profile/<user_username>/')
if len(urlSegments) > 1:
profile_url_segment = urlSegments[1].replace('-', ' ')
user = User.query.filter(
func.lower(User.username) ==
profile_url_segment.lower()).first()
if user:
profile_id = user.id
return jsonify({
'status': 'success',
'message': '',
'profile_id': profile_id,
'brand_id': brand_id,
'model_id_list': model_id_list,
})
def catch_all(path):
meta = {
"title":
"Climbing shoe sizing, recommendations, and deals | SizeSquirrel",
"url": "https://www.sizesquirrel.com/" + path,
"image":
"https://www.sizesquirrel.com/static/images/OGImage1200x1200.jpg",
"width": "1200",
"height": "1200",
}
urlSegments = path.split('/')
urlSegments = list(filter(None, urlSegments)) # fastest
brand_id = ''
if urlSegments:
if urlSegments[0] == 'shoes':
# @app.route('/shoes/<shoe_brand>/<shoe_model>/')
if len(urlSegments) > 1:
brand_url_segment = urlSegments[1].replace('-', ' ')
brand = Brand.query.filter_by(name=brand_url_segment).first()
if brand:
brand_id = brand.id
meta["title"] = brand_url_segment.title() + \
" | SizeSquirrel"
if len(urlSegments) > 2:
model_url_segment = urlSegments[2].replace('-', ' ')
model_url_segment = model_url_segment.lower()
model = Item.query.filter(Item.brand_id == brand_id).filter(
func.replace(func.lower(Item.model), '-', ' ') ==
model_url_segment).first()
if model:
meta["width"] = "300"
meta["height"] = "300"
meta["image"] = model.shoe_image
meta["title"] = brand_url_segment.title(
) + ' ' + model.model.title() + " | SizeSquirrel"
if app.debug:
if 'svg' in path:
# svgs need to be returned with a specific mimetype to work
return send_file('../frontend/public/' + path,
mimetype='image/svg+xml')
return requests.get('http://localhost:8080/{}'.format(path)).content
return render_template("index.html", meta=meta)
def update_recipients(connection, migrator_id):
"""Updates recipients field for Control model. """
control_table = all_models.Control.__table__
for old_role_name, new_role_name in ROLE_MAPPING.iteritems():
connection.execute(control_table.update().values(
recipients=func.replace(control_table.c.recipients, old_role_name,
new_role_name),
updated_at=datetime.datetime.utcnow(),
modified_by_id=migrator_id))
control_entities = connection.execute(control_table.select().where(
control_table.c.recipients != '')).fetchall()
if control_entities:
control_ids = [entity.id for entity in control_entities]
utils.add_to_objects_without_revisions_bulk(connection,
control_ids,
"Control",
action="modified")
def downgrade():
service = table(
'services',
sa.Column('url', sa.UnicodeText()),
sa.Column('type', sa.UnicodeText()),
sa.Column('sync_type', sa.UnicodeText()),
)
# transfer 'api' service types
op.execute(service.update().where(
service.c.sync_type == op.inline_literal('project-api')).values({
'type':
op.inline_literal('project-api'),
'url':
func.replace(service.c.url, '/api', ''),
}))
op.execute(service.update().where(
service.c.sync_type == op.inline_literal('geoserver-api')).values({
'type':
op.inline_literal('geoserver-api'),
}))
op.drop_column('services', 'sync_type')
def query_unique_tag(self):
"""
Grabs a session and queries the database to find the starting tag for the application.
this tag is used so it does not overlap existing shape tags previously generated
and stored into the database
"""
session = self.__Session()
# Grab db objects sorted by a tags NUMERIC portion, not the shape portion
db_objects = session.query(DatabasePolygon).order_by(
func.cast(func.replace(DatabasePolygon.tag, 'shape', ''),
NUMERIC).desc())
# If database is empty, set tag to 0, otherwise get the number potion of
# the shape with the highest numeric value and start there
if db_objects.count() == 0:
logger.info('No tags found, setting base case')
tag = 0
else:
logger.info('Tag found')
tag = int(re.search('(\d+)$', db_objects.first().tag).group(0)) + 1
session.close()
logger.info('Found unique tag %s' % tag)
return tag
def downgrade():
service = table(
"services",
sa.Column("url", sa.UnicodeText()),
sa.Column("type", sa.UnicodeText()),
sa.Column("sync_type", sa.UnicodeText()),
)
# transfer "api" service types
op.execute(service.update().where(
service.c.sync_type == op.inline_literal("project-api")).values({
"type":
op.inline_literal("project-api"),
"url":
func.replace(service.c.url, "/api", ""),
}))
op.execute(service.update().where(
service.c.sync_type == op.inline_literal("geoserver-api")).values({
"type":
op.inline_literal("geoserver-api"),
}))
op.drop_column("services", "sync_type")
def employee_search():
arg_keyword = request.args.get("keyword")
arg_thin = request.args.get("thin")
if arg_keyword is not None and arg_keyword != "":
arg_keyword = arg_keyword
employee_data = Employees.query.filter(or_(Employees.first_name.contains(arg_keyword),
Employees.last_name.contains(arg_keyword), (func.replace(Employees.first_name+Employees.last_name, ' ', '')) == arg_keyword.replace(" ", "")))
print(arg_keyword)
filtered_employee = employee_sqlalchemy_to_list(employee_data)
else:
filtered_employee = employee_sqlalchemy_to_list(Employees.query.all())
if arg_thin is not None:
temp_list = []
for emp_dict in filtered_employee:
if emp_dict['last_name'] is None:
temp_list.append({"id" : emp_dict['id'], "name" : emp_dict['first_name'], "designation" : emp_dict['designation']})
else:
temp_list.append({"id" : emp_dict['id'], "name" : emp_dict['first_name'] + " " + emp_dict['last_name'], "designation" : emp_dict['designation']})
filtered_employee = temp_list
return jsonify(filtered_employee)
def update_recipients(connection, migrator_id):
"""Updates recipients field for Control model. """
control_table = sa.sql.table(
"controls", sa.Column('id', sa.Integer(), nullable=False),
sa.Column('recipients', sa.String(length=250), nullable=True),
sa.Column('updated_at', sa.DateTime, nullable=False),
sa.Column('modified_by_id', sa.Integer, nullable=True))
# replace all None data with empty string for recipients field
for old_role_name, new_role_name in ROLE_MAPPING.iteritems():
connection.execute(control_table.update().values(
recipients=func.replace(control_table.c.recipients, old_role_name,
new_role_name),
updated_at=datetime.datetime.utcnow(),
modified_by_id=migrator_id))
control_entities = connection.execute(control_table.select().where(
control_table.c.recipients != '')).fetchall()
if control_entities:
control_ids = [entity.id for entity in control_entities]
utils.add_to_objects_without_revisions_bulk(connection,
control_ids,
"Control",
action="modified")
def normalized_code(cls):
return func.replace(func.replace(func.lower(cls.code), '-', ''), ' ', '')
def operate(self, op, other):
return op(func.replace(self, ' ', ''), func.replace(other, ' ', ''))
def build_replace_func(chars, attr, replace_with=''):
for character in chars:
attr = func.replace(attr, character, replace_with)
return attr
def normalized_code(cls):
return func.replace(func.replace(func.lower(cls.code), '-', ''), ' ',
'')
class PromoCode(MagModel):
"""
Promo codes used by attendees to purchase badges at discounted prices.
Attributes:
code (str): The actual textual representation of the promo code. This
is what the attendee would have to type in during registration to
receive a discount. `code` may not be an empty string or a string
consisting entirely of whitespace.
discount (int): The discount amount that should be applied to the
purchase price of a badge. The interpretation of this value
depends on the value of `discount_type`. In any case, a value of
0 equates to a full discount, i.e. a free badge.
discount_str (str): A human readable description of the discount.
discount_type (int): The type of discount this promo code will apply.
Valid values are:
* 0 `_FIXED_DISCOUNT`: `discount` is interpreted as a fixed
dollar amount by which the badge price should be reduced. If
`discount` is 49 and the badge price is normally $100, then
the discounted badge price would be $51.
* 1 `_FIXED_PRICE`: `discount` is interpreted as the actual badge
price. If `discount` is 49, then the discounted badge price
would be $49.
* 2 `_PERCENT_DISCOUNT`: `discount` is interpreted as a percentage
by which the badge price should be reduced. If `discount` is
20 and the badge price is normally $50, then the discounted
badge price would $40 ($50 reduced by 20%). If `discount` is
100, then the price would be 100% off, i.e. a free badge.
group (relationship): An optional relationship to a PromoCodeGroup
object, which groups sets of promo codes to make attendee-facing
"groups"
cost (int): The cost of this promo code if and when it was bought
as part of a PromoCodeGroup.
expiration_date (datetime): The date & time upon which this promo code
expires. An expired promo code may no longer be used to receive
discounted badges.
is_free (bool): True if this promo code will always cause a badge to
be free. False if this promo code may not cause a badge to be free.
Note:
It's possible for this value to be False for a promo code that
still reduces a badge's price to zero. If there are some other
discounts that also reduce a badge price (like an age discount)
then the price may be pushed down to zero.
is_expired (bool): True if this promo code is expired, False otherwise.
is_unlimited (bool): True if this promo code may be used an unlimited
number of times, False otherwise.
is_valid (bool): True if this promo code is still valid and may be
used again, False otherwise.
normalized_code (str): A normalized version of `code` suitable for
database queries. Normalization converts `code` to all lowercase
and removes dashes ("-").
used_by (list): List of attendees that have used this promo code.
Note:
This property is declared as a backref in the Attendee class.
uses_allowed (int): The total number of times this promo code may be
used. A value of None means this promo code may be used an
unlimited number of times.
uses_allowed_str (str): A human readable description of
uses_allowed.
uses_count (int): The number of times this promo code has already
been used.
uses_count_str (str): A human readable description of uses_count.
uses_remaining (int): Remaining number of times this promo code may
be used.
uses_remaining_str (str): A human readable description of
uses_remaining.
"""
_FIXED_DISCOUNT = 0
_FIXED_PRICE = 1
_PERCENT_DISCOUNT = 2
_DISCOUNT_TYPE_OPTS = [(_FIXED_DISCOUNT, 'Fixed Discount'),
(_FIXED_PRICE, 'Fixed Price'),
(_PERCENT_DISCOUNT, 'Percent Discount')]
_AMBIGUOUS_CHARS = {
'0': 'OQD',
'1': 'IL',
'2': 'Z',
'5': 'S',
'6': 'G',
'8': 'B'
}
_UNAMBIGUOUS_CHARS = string.digits + string.ascii_uppercase
for _, s in _AMBIGUOUS_CHARS.items():
_UNAMBIGUOUS_CHARS = re.sub('[{}]'.format(s), '', _UNAMBIGUOUS_CHARS)
code = Column(UnicodeText)
discount = Column(Integer, nullable=True, default=None)
discount_type = Column(Choice(_DISCOUNT_TYPE_OPTS),
default=_FIXED_DISCOUNT)
expiration_date = Column(UTCDateTime, default=c.ESCHATON)
uses_allowed = Column(Integer, nullable=True, default=None)
cost = Column(Integer, nullable=True, default=None)
group_id = Column(UUID,
ForeignKey('promo_code_group.id', ondelete='SET NULL'),
nullable=True)
group = relationship(PromoCodeGroup,
backref='promo_codes',
foreign_keys=group_id,
cascade='save-update,merge,refresh-expire,expunge')
__table_args__ = (Index('uq_promo_code_normalized_code',
func.replace(
func.replace(func.lower(code), '-', ''), ' ',
''),
unique=True),
CheckConstraint(func.trim(code) != '',
name='ck_promo_code_non_empty_code'))
_repr_attr_names = ('code', )
@classmethod
def normalize_expiration_date(cls, dt):
"""
Converts the given datetime to 11:59pm local in the event timezone.
"""
if isinstance(dt, six.string_types):
if dt.strip():
dt = dateparser.parse(dt)
else:
dt = c.ESCHATON
if dt.tzinfo:
dt = dt.astimezone(c.EVENT_TIMEZONE)
return c.EVENT_TIMEZONE.localize(
dt.replace(hour=23, minute=59, second=59, tzinfo=None))
@property
def discount_str(self):
if self.discount_type == self._FIXED_DISCOUNT and self.discount == 0:
# This is done to account for Art Show Agent codes, which use the PromoCode class
return 'No discount'
elif not self.discount:
return 'Free badge'
if self.discount_type == self._FIXED_DISCOUNT:
return '${} discount'.format(self.discount)
elif self.discount_type == self._FIXED_PRICE:
return '${} badge'.format(self.discount)
else:
return '%{} discount'.format(self.discount)
@hybrid_property
def is_expired(self):
return self.expiration_date < localized_now()
@is_expired.expression
def is_expired(cls):
return cls.expiration_date < localized_now()
@property
def is_free(self):
return not self.discount or (
self.discount_type == self._PERCENT_DISCOUNT
and self.discount >= 100) or (self.discount_type
== self._FIXED_DISCOUNT
and self.discount >= c.BADGE_PRICE)
@hybrid_property
def is_unlimited(self):
return not self.uses_allowed
@is_unlimited.expression
def is_unlimited(cls):
return cls.uses_allowed == None # noqa: E711
@hybrid_property
def is_valid(self):
return not self.is_expired and (self.is_unlimited
or self.uses_remaining > 0)
@is_valid.expression
def is_valid(cls):
return (cls.expiration_date >= localized_now()) \
& ((cls.uses_allowed == None) | (cls.uses_remaining > 0)) # noqa: E711
@hybrid_property
def normalized_code(self):
return self.normalize_code(self.code)
@normalized_code.expression
def normalized_code(cls):
return func.replace(func.replace(func.lower(cls.code), '-', ''), ' ',
'')
@property
def uses_allowed_str(self):
uses = self.uses_allowed
return 'Unlimited uses' if uses is None else '{} use{} allowed'.format(
uses, '' if uses == 1 else 's')
@hybrid_property
def uses_count(self):
return len(self.used_by)
@uses_count.expression
def uses_count(cls):
from uber.models.attendee import Attendee
return select([
func.count(Attendee.id)
]).where(Attendee.promo_code_id == cls.id).label('uses_count')
@property
def uses_count_str(self):
uses = self.uses_count
return 'Used by {} attendee{}'.format(uses, '' if uses == 1 else 's')
@hybrid_property
def uses_remaining(self):
return None if self.is_unlimited else self.uses_allowed - self.uses_count
@uses_remaining.expression
def uses_remaining(cls):
return cls.uses_allowed - cls.uses_count
@property
def uses_remaining_str(self):
uses = self.uses_remaining
return 'Unlimited uses' if uses is None else '{} use{} remaining'.format(
uses, '' if uses == 1 else 's')
@presave_adjustment
def _attribute_adjustments(self):
# If 'uses_allowed' is empty, then this is an unlimited use code
if not self.uses_allowed:
self.uses_allowed = None
# If 'discount' is empty, then this is a full discount, free badge
if self.discount == '':
self.discount = None
self.code = self.code.strip() if self.code else ''
if not self.code:
# If 'code' is empty, then generate a random code
self.code = self.generate_random_code()
else:
# Replace multiple whitespace characters with a single space
self.code = re.sub(r'\s+', ' ', self.code)
# Always make expiration_date 11:59pm of the given date
self.expiration_date = self.normalize_expiration_date(
self.expiration_date)
def calculate_discounted_price(self, price):
"""
Returns the discounted price based on the promo code's `discount_type`.
Args:
price (int): The badge price in whole dollars.
Returns:
int: The discounted price. The returned number will never be
less than zero or greater than `price`. If `price` is None
or a negative number, then the return value will always be 0.
"""
if not self.discount or not price or price < 0:
return 0
discounted_price = price
if self.discount_type == self._FIXED_DISCOUNT:
discounted_price = price - self.discount
elif self.discount_type == self._FIXED_PRICE:
discounted_price = self.discount
elif self.discount_type == self._PERCENT_DISCOUNT:
discounted_price = int(price * ((100.0 - self.discount) / 100.0))
return min(max(discounted_price, 0), price)
@classmethod
def _generate_code(cls, generator, count=None):
"""
Helper method to limit collisions for the other generate() methods.
Arguments:
generator (callable): Function that returns a newly generated code.
count (int): The number of codes to generate. If `count` is `None`,
then a single code will be generated. Defaults to `None`.
Returns:
If an `int` value was passed for `count`, then a `list` of newly
generated codes is returned. If `count` is `None`, then a single
`str` is returned.
"""
from uber.models import Session
with Session() as session:
# Kind of inefficient, but doing one big query for all the existing
# codes will be faster than a separate query for each new code.
old_codes = set(s for (s, ) in session.query(cls.code).all())
# Set an upper limit on the number of collisions we'll allow,
# otherwise this loop could potentially run forever.
max_collisions = 100
collisions = 0
codes = set()
while len(codes) < (1 if count is None else count):
code = generator().strip()
if not code:
break
if code in codes or code in old_codes:
collisions += 1
if collisions >= max_collisions:
break
else:
codes.add(code)
return (codes.pop() if codes else None) if count is None else codes
@classmethod
def generate_random_code(cls, count=None, length=9, segment_length=3):
"""
Generates a random promo code.
With `length` = 12 and `segment_length` = 3::
XXX-XXX-XXX-XXX
With `length` = 6 and `segment_length` = 2::
XX-XX-XX
Arguments:
count (int): The number of codes to generate. If `count` is `None`,
then a single code will be generated. Defaults to `None`.
length (int): The number of characters to use for the code.
segment_length (int): The length of each segment within the code.
Returns:
If an `int` value was passed for `count`, then a `list` of newly
generated codes is returned. If `count` is `None`, then a single
`str` is returned.
"""
# The actual generator function, called repeatedly by `_generate_code`
def _generate_random_code():
letters = ''.join(
random.choice(cls._UNAMBIGUOUS_CHARS) for _ in range(length))
return '-'.join(textwrap.wrap(letters, segment_length))
return cls._generate_code(_generate_random_code, count=count)
@classmethod
def generate_word_code(cls, count=None):
"""
Generates a promo code consisting of words from `PromoCodeWord`.
Arguments:
count (int): The number of codes to generate. If `count` is `None`,
then a single code will be generated. Defaults to `None`.
Returns:
If an `int` value was passed for `count`, then a `list` of newly
generated codes is returned. If `count` is `None`, then a single
`str` is returned.
"""
from uber.models import Session
with Session() as session:
words = PromoCodeWord.group_by_parts_of_speech(
session.query(PromoCodeWord).order_by(
PromoCodeWord.normalized_word).all())
# The actual generator function, called repeatedly by `_generate_code`
def _generate_word_code():
code_words = []
for part_of_speech, _ in PromoCodeWord._PART_OF_SPEECH_OPTS:
if words[part_of_speech]:
code_words.append(random.choice(words[part_of_speech]))
return ' '.join(code_words)
return cls._generate_code(_generate_word_code, count=count)
@classmethod
def disambiguate_code(cls, code):
"""
Removes ambiguous characters in a promo code supplied by an attendee.
Arguments:
code (str): A promo code as typed by an attendee.
Returns:
str: A copy of `code` with all ambiguous characters replaced by
their unambiguous equivalent.
"""
code = cls.normalize_code(code)
if not code:
return ''
for unambiguous, ambiguous in cls._AMBIGUOUS_CHARS.items():
ambiguous_pattern = '[{}]'.format(ambiguous.lower())
code = re.sub(ambiguous_pattern, unambiguous.lower(), code)
return code
@classmethod
def normalize_code(cls, code):
"""
Normalizes a promo code supplied by an attendee.
Arguments:
code (str): A promo code as typed by an attendee.
Returns:
str: A copy of `code` converted to all lowercase, with dashes ("-")
and whitespace characters removed.
"""
if not code:
return ''
return re.sub(r'[\s\-]+', '', code.lower())
def _update_domains_on_server_update(self, server):
"""
For performance, manually prepare a bulk update query to
update all NS records for all existing domains that need
updating of their corresponding NS record in Record table
"""
ns_rec_content = self._sanitize_content("NS", server['name'])
LOG.debug("Content field of existing NS records will be updated"
" to the following upon server update: %s" % ns_rec_content)
try:
# Execute the manually prepared query
# A TX is required for, at the least, SQLite.
#
self.session.begin()
# first determine the old name of the server
# before making the updates. Since the value
# is coming from an NS record, the server name
# will not have a trailing period (.)
old_ns_rec = self.session.query(models.Record)\
.filter_by(type='NS', designate_id=server['id'])\
.first()
if old_ns_rec is not None:
old_server_name = old_ns_rec.content
LOG.debug("old server name read from a backend NS record:"
" %s" % old_server_name)
LOG.debug("new server name: %s" % server['name'])
# Then update all NS records that need updating
# Only the name of a server has changed when we are here
self.session.query(models.Record)\
.filter_by(type='NS', designate_id=server['id'])\
.update({"content": ns_rec_content})
# Then update all SOA records as necessary
# Do the SOA last, ensuring we don't trigger a NOTIFY
# before the NS records are in place.
#
# Update the content field of every SOA record that has the
# old server name as part of its 'content' field to reflect
# the new server name.
# Need to strip the trailing period from the server['name']
# before using it to replace the old_server_name in the SOA
# record since the SOA record already has a trailing period
# and we want to keep it
self.session.execute(models.Record.__table__
.update()
.where(and_(models.Record.__table__.c.type == "SOA",
models.Record.__table__.c.content.like
("%s%%" % old_server_name)))
.values(content=func.replace(
models.Record.__table__.c.content,
old_server_name,
server['name'].rstrip('.'))
)
)
except Exception:
with excutils.save_and_reraise_exception():
self.session.rollback()
# now commit
else:
self.session.commit()
def cond_func(lang):
return func.to_tsvector(lang, func.replace(target, ";", " ")).op("@@")(func.to_tsquery(lang, prepared_searchstring))
def _sanitized_string(value: str) -> Function:
sanitized = func.replace(value, "-", "")
sanitized = func.replace(sanitized, " ", "")
sanitized = func.unaccent(sanitized)
sanitized = func.lower(sanitized)
return sanitized
