def getFieldL(self):
sh = self.wb.sheet_by_name(u'terrains')
typeL, _type2idx = self.getType(sh)
fieldSet = set()
for i in range(1, sh.nrows):
valL = self.getRow(sh, i, True)
action = str(valL[0]).strip()
fieldPropLL = [
self.expand(val, type_)
for val, type_ in zip(valL[1:], typeL[1:])
]
subSet = set(
[fieldProp for fieldProp in itertools.product(*fieldPropLL)])
if action == '+':
fieldSet.update(subSet)
elif action == '-':
fieldSet.difference_update(subSet)
fieldL = list(fieldSet)
fieldL.sort()
dateL = list(set(list(zip(*fieldL))[1]))
dateL.sort()
self.fieldL = []
for stadium, date, time, fName in fieldL:
self.fieldL.append(
Field(stadium, fName, time, date, self.matchDuration))
def choose(initialField, piece, next_piece, offsetX, weights, parent,
do_forseen):
field = Field(len(initialField[0]), len(initialField))
field.updateField(copy.deepcopy(initialField))
if do_forseen:
pieces_set = [piece, next_piece]
else:
pieces_set = [piece]
offset, rotation, _ = Ai.best(field, pieces_set, 0, weights, 1)
moves = []
offset = offset - offsetX
for _ in range(0, rotation):
moves.append("UP")
for _ in range(0, abs(offset)):
if offset > 0:
moves.append("RIGHT")
else:
moves.append("LEFT")
# moves.append('RETURN')
parent.executes_moves(moves)
class Metric(SqlModel):
table = 'metric'
py_table_definition = {
'table_name':
table,
'fields': [
# name, type, length, unsigned, null, default, on_update
Field('uid', 'varchar', 50, None, False, None, None),
Field('short_uid', 'varchar', 50, None, False, None, None),
Field('created', 'datetime', None, None, False,
SqlModel.sql_current_timestamp, None),
Field('modified', 'datetime', None, None, False,
SqlModel.sql_current_timestamp,
SqlModel.sql_current_timestamp),
Field('name', 'varchar', 200, None, False, None, None),
# Computer-friendly version of the name, to ultimately be used as
# embedded data names in Qualtrics, convention is lower-dash-case.
Field('label', 'varchar', 50, None, False, None, None),
Field('description', 'text', None, None, True, SqlModel.sql_null,
None),
# JSON field with structure:
# [
# {
# "type": "reading",
# "text": "Research Background",
# "url": "http://www.psychresearch.org/coolpaper"}
# },
# ...
# ]
# Current UI plan is that there will be no metric details page. A
# listed metric will just have an anchor to it's first link in this
# structure. Not bothering to change the db in case the spec shifts
# again later.
Field('links', 'varchar', 2000, None, False, r'[]', None),
],
'primary_key': ['uid'],
'indices': [],
'engine':
'InnoDB',
'charset':
'utf8',
}
json_props = ['links']
def createField(df, i):
f = field.Field(utils.clearData(df.columns[i], ''), [], i, [], '', '')
f.values = df[df.columns[i]].sample(n=sample, random_state=1).tolist()
col = df.iloc[:, i]
if col.dtype == 'float64':
f.db_meta = 'decimal(10,8)'
f.entity_meta = 'sys.number'
elif col.dtype == 'int64':
f.db_meta = 'integer'
f.entity_meta = 'sys.number'
elif col.dtype in ('object', 'string_', 'unicode_'):
col = col.astype('unicode')
maxlen = max(col.apply(len))
f.db_meta = 'varchar(' + str(maxlen) + ')'
f.entity_meta = 'None'
elif col.dtype == 'datetime64':
f.db_meta = 'date'
f.entity_meta = 'sys.date'
return f
class Notification(SqlModel):
table = 'notification'
py_table_definition = {
'table_name':
table,
'fields': [
# name, type, length, unsigned, null, default, on_update
Field('uid', 'varchar', 50, None, False, None, None),
Field('short_uid', 'varchar', 50, None, False, None, None),
Field('created', 'datetime', None, None, False,
SqlModel.sql_current_timestamp, None),
Field('modified', 'datetime', None, None, False,
SqlModel.sql_current_timestamp,
SqlModel.sql_current_timestamp),
Field('user_id', 'varchar', 50, None, False, None, None),
# 'generic', 'survey', or 'report' as of 2017-07-26
Field('type', 'varchar', 50, None, False, None, None),
Field('template_params', 'varchar', 2000, None, False, r'{}',
None),
],
'primary_key': ['uid'],
'indices': [
{
'name': 'user-created',
'fields': ['user_id', 'created'],
},
],
'engine':
'InnoDB',
'charset':
'utf8',
}
json_props = ['template_params']
@classmethod
def create(klass, **kwargs):
# Check lengths
for fieldName in ('template_params', ):
field = next(f for f in klass.py_table_definition['fields']
if f.name == fieldName)
if len(kwargs[field.name]) > field.length:
raise Exception("Value for {} too long (max {}): {}".format(
field.name, field.length, kwargs[field.name]))
return super(klass, klass).create(**kwargs)
def get_template(self):
template_path = 'notifications/digest_item_{}.html'.format(self.type)
return jinja_env.get_template(template_path)
def render(self):
"""Interpolate data into an html string.
Ignores `{items}` b/c that's for digest().
"""
# Add environment and user details to params.
protocol = 'http' if util.is_localhost() else 'https'
domain = ('localhost:3000'
if util.is_localhost() else os.environ['HOSTING_DOMAIN'])
params = dict(
self.template_params,
triton_domain='{}://{}'.format(protocol, domain),
user_id=self.user_id,
short_user_id=Notification.convert_uid(self.user_id),
)
return self.get_template().render(**params)
@classmethod
def users_with_notifications(klass, start, end):
"""What users have notifications? Returns ids."""
query = '''
SELECT DISTINCT `user_id`
FROM `{table}`
WHERE `created` > %s
AND `created` <= %s
'''.format(table=Notification.table)
# MySQLDb doesn't like our ISO format, change to SQL format.
start = datetime.datetime.strptime(start, config.iso_datetime_format)
end = datetime.datetime.strptime(end, config.iso_datetime_format)
start = start.strftime('%Y-%m-%d %H:%M:%S')
end = end.strftime('%Y-%m-%d %H:%M:%S')
params = (start, end)
with mysql_connection.connect() as sql:
row_dicts = sql.select_query(query, params)
return [d['user_id'] for d in row_dicts]
@classmethod
def get_period_for_user(klass, user, start, end):
# SqlModel.get() doesn't support WHERE clauses that aren't = or IN(),
# so we have to work around it.
query = '''
SELECT *
FROM `{table}`
WHERE `user_id` = %s
AND `created` > %s
AND `created` <= %s
'''.format(table=klass.table)
# MySQLDb doesn't like our ISO format, change to SQL format.
start = datetime.datetime.strptime(start, config.iso_datetime_format)
end = datetime.datetime.strptime(end, config.iso_datetime_format)
start = start.strftime('%Y-%m-%d %H:%M:%S')
end = end.strftime('%Y-%m-%d %H:%M:%S')
params = (user.uid, start, end)
with mysql_connection.connect() as sql:
row_dicts = sql.select_query(query, params)
return [klass.row_dict_to_obj(d) for d in row_dicts]
class Cycle(SqlModel):
table = 'cycle'
py_table_definition = {
'table_name':
table,
'fields': [
# name, type, length, unsigned, null, default, on_update
Field('uid', 'varchar', 50, None, False, None, None),
Field('short_uid', 'varchar', 50, None, False, None, None),
Field('created', 'datetime', None, None, False,
SqlModel.sql_current_timestamp, None),
Field('modified', 'datetime', None, None, False,
SqlModel.sql_current_timestamp,
SqlModel.sql_current_timestamp),
Field('team_id', 'varchar', 50, None, False, None, None),
Field('ordinal', 'tinyint', 4, True, False, 1, None),
Field('start_date', 'date', None, None, True, SqlModel.sql_null,
None),
Field('end_date', 'date', None, None, True, SqlModel.sql_null,
None),
Field('extended_end_date', 'date', None, None, True,
SqlModel.sql_null, None),
Field('meeting_datetime', 'datetime', None, None, True,
SqlModel.sql_null, None),
Field('meeting_location', 'varchar', 200, None, True, None, None),
Field('resolution_date', 'date', None, None, True,
SqlModel.sql_null, None),
# Represents _current_ participation, based on the active cycle.
Field('students_completed', 'smallint', 5, True, True,
SqlModel.sql_null, None),
],
'primary_key': ['uid'],
'indices': [
{
'name': 'team',
'fields': ['team_id'],
},
],
'engine':
'InnoDB',
'charset':
'utf8',
}
@classmethod
def cycleless_start_date(klass, current_date=None):
# Returns the previous July 1
if current_date is None:
current_date = datetime.date.today()
current_year = current_date.year
current_month = current_date.month
july_month = 7
start_date_month = july_month
start_date_day = 1
if current_month < july_month:
start_date_year = current_year - 1
else:
start_date_year = current_year
return datetime.date(start_date_year, start_date_month, start_date_day)
@classmethod
def cycleless_end_date(klass, current_date=None):
# Returns the next June 30
if current_date is None:
current_date = datetime.date.today()
current_year = current_date.year
current_month = current_date.month
june_month = 6
end_date_month = june_month
end_date_day = 30
if current_month > june_month:
end_date_year = current_year + 1
else:
end_date_year = current_year
return datetime.date(end_date_year, end_date_month, end_date_day)
@classmethod
def create_for_team(klass, team):
program = Program.get_by_id(team.program_id)
if not program:
return []
if not program.use_cycles:
# https://github.com/PERTS/triton/issues/1632
# The program is in cycleless mode. Generate a single cycle with
# date range from previous July 1 through the next June 30.
today = datetime.date.today()
start_date = klass.cycleless_start_date(today)
end_date = klass.cycleless_end_date(today)
return [
Cycle.create(team_id=team.uid,
ordinal=1,
start_date=start_date,
end_date=end_date)
]
return [
Cycle.create(team_id=team.uid, ordinal=x + 1)
for x in range(program.min_cycles or 0)
]
@classmethod
def get_current_for_team(klass, team_id, today=None):
"""Returns current cycle or None."""
if today is None:
today = datetime.date.today()
today_str = today.strftime(config.sql_datetime_format)
query = '''
SELECT *
FROM `{table}`
WHERE `team_id` = %s
AND `start_date` <= %s
AND (
`end_date` >= %s OR
`extended_end_date` >= %s
)
LIMIT 1
'''.format(table=klass.table)
params = (team_id, today_str, today_str, today_str)
with mysql_connection.connect() as sql:
row_dicts = sql.select_query(query, params)
return klass.row_dict_to_obj(row_dicts[0]) if row_dicts else None
@classmethod
def query_by_teams(klass, team_ids):
query = '''
SELECT *
FROM `{table}`
WHERE `team_id` IN ({interps})
'''.format(table=klass.table,
interps=', '.join(['%s'] * len(team_ids)))
params = tuple(team_ids)
with mysql_connection.connect() as sql:
row_dicts = sql.select_query(query, params)
return [klass.row_dict_to_obj(r) for r in row_dicts]
@classmethod
def reorder_and_extend(klass, team_cycles):
"""Order cycles within a team by date and adds extended_end_date.
Cycles without dates are placed at the end ordered by ordinal.
Raises if dates overlap.
Returns cycles, likely modified/mutated. N.B. non-pure!
"""
if len(team_cycles) == 0:
return []
if not len(set(c.team_id for c in team_cycles)) == 1:
raise Exception(
"Got cycles from multiple teams: {}".format(team_cycles))
# Sort and apply ordinals.
dated = [c for c in team_cycles if c.start_date]
undated = [c for c in team_cycles if not c.start_date]
ordered_dated = sorted(dated, key=lambda c: c.start_date)
ordered_undated = sorted(undated, key=lambda c: c.ordinal)
ordered = ordered_dated + ordered_undated
for i, cycle in enumerate(ordered):
new_ordinal = i + 1
if cycle.ordinal != new_ordinal:
cycle.ordinal = new_ordinal
# Sanity-check all but the last for date overlap.
if i == len(ordered) - 1:
break
next_cycle = ordered[i + 1]
dates_set = bool(cycle.end_date and next_cycle.start_date)
if dates_set and cycle.end_date >= next_cycle.start_date:
raise Exception("Cycle dates overlap: {}, {}".format(
cycle, next_cycle))
ordered = klass.extend_dates(ordered)
return ordered
@classmethod
def extend_dates(klass, team_cycles):
# When we need to figure out the end of the current program, do it
# relative to the beginning of the first cycle.
program_end_date = klass.cycleless_end_date(team_cycles[0].start_date)
for i, cycle in enumerate(team_cycles):
if not cycle.start_date or not cycle.end_date:
# Cycles should come in order, and if dates aren't set on this
# one then none of the later cycles have dates set either.
# Don't add any extended dates to this or any later cycles.
cycle.extended_end_date = None
continue
if i + 1 < len(team_cycles):
# There's a next cycle. Attempt to extend the end date to the
# that next cycle.
next_cycle = team_cycles[i + 1]
if next_cycle.start_date:
cycle.extended_end_date = (next_cycle.start_date -
datetime.timedelta(days=1))
else:
# The next cycle doesn't have dates defined; extend the
# end date to the latest possible day.
cycle.extended_end_date = program_end_date
else:
# This is the last cycle; extend the end date to latest
# possible day.
cycle.extended_end_date = program_end_date
return team_cycles
class Report(SqlModel):
"""Similar to StorageObject, but SQL-backed."""
table = 'report'
py_table_definition = {
'table_name':
table,
'fields': [
# name, type, length, unsigned, null, default, on_update
Field('uid', 'varchar', 50, None, False, None, None),
Field('short_uid', 'varchar', 50, None, False, None, None),
Field('created', 'datetime', None, None, False,
SqlModel.sql_current_timestamp, None),
Field('modified', 'datetime', None, None, False,
SqlModel.sql_current_timestamp,
SqlModel.sql_current_timestamp),
Field('parent_id', 'varchar', 50, None, False, None, None),
Field('network_id', 'varchar', 50, None, True, SqlModel.sql_null,
None),
Field('organization_id', 'varchar', 50, None, True,
SqlModel.sql_null, None),
Field('team_id', 'varchar', 50, None, True, SqlModel.sql_null,
None),
Field('classroom_id', 'varchar', 50, None, True, SqlModel.sql_null,
None),
# Dataset hosted on Neptune, e.g. available at
# neptune.perts.net/api/datasets/:dataset_id
Field('dataset_id', 'varchar', 50, None, True, SqlModel.sql_null,
None),
# Html template on Neptune with which this report should be viewed,
# e.g. neptune.perts.net/datasets/:dataset_id/:template/:filename
Field('template', 'varchar', 200, None, True, SqlModel.sql_null,
None),
# Name as uploaded, set in header when downloaded.
Field('filename', 'varchar', 200, None, False, None, None),
# Path to file on Google Cloud Storage:
# r'^/(?P<bucket>.+)/data_tables/(?P<object>.+)$'
# Stored file names are md5 hashes, e.g.
# '04605dbc5fd9d50836712b436dbb5dbd'
Field('issue_date', 'date', None, None, True, SqlModel.sql_null,
None),
Field('gcs_path', 'varchar', 200, None, True, SqlModel.sql_null,
None),
# Size of the file, in bytes, can be up to 4,294,967,295 ie 4.3 GB
Field('size', 'int', 4, True, True, SqlModel.sql_null, None),
# Content type (example: 'text/csv')
Field('content_type', 'varchar', 200, None, True,
SqlModel.sql_null, None),
# Reports with preview true can only be seen by super admins, and
# are automatically switched to preview false under certain
# conditions, see cron_handlers.
Field('preview', 'bool', None, None, False, 1, None),
Field('notes', 'text', None, None, True, None, None),
],
'primary_key': ['uid'],
'indices': [
{
'unique': True,
'name': 'parent-file',
'fields': ['parent_id', 'filename'],
},
],
'engine':
'InnoDB',
'charset':
'utf8',
}
def to_client_dict(self):
d = super(Report, self).to_client_dict()
parts = self.filename.split('.')
d['week'] = parts[0] if len(parts) > 1 else None
return d
@classmethod
def create(klass, **kwargs):
# The parent should be the _smallest_ scope available, because
# typically a given reporting unit (e.g. classroom) will have a
# large scope (e.g. team) displayed in it as well for context.
if kwargs.get('classroom_id', None):
kwargs['parent_id'] = kwargs['classroom_id']
elif kwargs.get('team_id', None):
kwargs['parent_id'] = kwargs['team_id']
elif kwargs.get('organization_id', None):
kwargs['parent_id'] = kwargs['organization_id']
elif kwargs.get('network_id', None):
kwargs['parent_id'] = kwargs['network_id']
if not kwargs.get('parent_id', None):
raise Exception(
u"Could not create report w/o parent; kwargs: {}".format(
kwargs))
return super(klass, klass).create(**kwargs)
@classmethod
def get_previews(klass, week):
return klass.get(
filename='{}.html'.format(week),
preview=True,
n=float('inf'),
)
@classmethod
def release_previews(klass, week):
"""Set preview to False for all reports in a given week.
Args:
week - str, YYYY-MM-DD
"""
# Parse the week to make sure it's valid.
datetime.datetime.strptime(week, config.iso_date_format)
query = '''
UPDATE {table}
SET `preview` = 0
WHERE `filename` = %s
'''.format(table=klass.table)
params = ('{}.html'.format(week), )
with mysql_connection.connect() as sql:
sql.query(query, params)
affected_rows = sql.connection.affected_rows()
return int(affected_rows)
@classmethod
def get_for_team(klass, team_id, include_preview, n=1000):
preview_clause = '' if include_preview else '`preview` = 0 AND'
query = '''
SELECT *
FROM `{table}`
WHERE
`team_id` = %s AND
{preview_clause}
# GCS-based reports have a NULL template; these should not
# be excluded, but "empty" reports should be.
(`template` != 'empty' OR `template` IS NULL)
ORDER BY `issue_date`, `filename`
LIMIT %s
'''.format(table=klass.table, preview_clause=preview_clause)
params = (team_id, n)
with mysql_connection.connect() as sql:
row_dicts = sql.select_query(query, params)
return [klass.row_dict_to_obj(d) for d in row_dicts]
@classmethod
def get_for_network(klass, network_id, include_preview, n=1000):
preview_clause = '' if include_preview else '`preview` = 0 AND'
query = '''
SELECT *
FROM `{table}`
WHERE
`parent_id` = %s AND
{preview_clause}
# GCS-based reports have a NULL template; these should not
# be excluded, but "empty" reports should be.
(`template` != 'empty' OR `template` IS NULL)
ORDER BY `issue_date`, `filename`
LIMIT %s
'''.format(table=klass.table, preview_clause=preview_clause)
params = (network_id, n)
with mysql_connection.connect() as sql:
row_dicts = sql.select_query(query, params)
return [klass.row_dict_to_obj(d) for d in row_dicts]
@classmethod
def get_for_organization(klass, org_id, include_preview, n=1000):
preview_clause = '' if include_preview else '`preview` = 0 AND'
query = '''
SELECT *
FROM `{table}`
WHERE
`parent_id` = %s AND
{preview_clause}
# GCS-based reports have a NULL template; these should not
# be excluded, but "empty" reports should be.
(`template` != 'empty' OR `template` IS NULL)
ORDER BY `issue_date`, `filename`
LIMIT %s
'''.format(table=klass.table, preview_clause=preview_clause)
params = (org_id, n)
with mysql_connection.connect() as sql:
row_dicts = sql.select_query(query, params)
return [klass.row_dict_to_obj(d) for d in row_dicts]
def test_field(self):
fname = 'login'
ftype = 'String(100)'
field = Field(fname, ftype)
self.assertEqual(
" login = db.Column( db.String(100), nullable=True )",
field.definition())
funique = True
fnull = False
field = Field(fname, ftype, unique=funique, nullable=fnull)
self.assertEqual(
" login = db.Column( db.String(100), unique=True, nullable=False )",
field.definition())
fpk = True
field = Field(fname, ftype, pkey=fpk)
res1 = field.definition()
self.assertEqual(
" login = db.Column( db.String(100), primary_key=True, autoincrement=True )",
res1)
field = Field(fname, ftype, unique=funique, nullable=fnull, pkey=fpk)
res2 = field.definition()
self.assertEqual(res1, res2)
fautoinc = False
field = Field(fname, ftype, pkey=fpk, autoinc=fautoinc)
self.assertEqual(
" login = db.Column( db.String(100), primary_key=True, autoincrement=False )",
field.definition())
f = field_assembler(('login', 'String(100)'))
self.assertEqual(
" login = db.Column( db.String(100), nullable=True )",
f.definition())
f = field_assembler(('login', 'String(100)', ['unique']))
self.assertEqual(
" login = db.Column( db.String(100), unique=True, nullable=True )",
f.definition())
f = field_assembler(('login', 'String(100)', ['unique', 'notnull']))
self.assertEqual(
" login = db.Column( db.String(100), unique=True, nullable=False )",
f.definition())
f = field_assembler(('login', 'String(100)', ['pkey', 'autoinc']))
self.assertEqual(
" login = db.Column( db.String(100), primary_key=True, autoincrement=True )",
f.definition())
f = field_assembler(('login', 'String(100)', ['pkey']))
self.assertEqual(
" login = db.Column( db.String(100), primary_key=True, autoincrement=False )",
f.definition())
class Program(SqlModel):
table = 'program'
py_table_definition = {
'table_name':
table,
'fields': [
# name, type, length, unsigned, null, default, on_update
Field('uid', 'varchar', 50, None, False, None, None),
Field('short_uid', 'varchar', 50, None, False, None, None),
Field('created', 'datetime', None, None, False,
SqlModel.sql_current_timestamp, None),
Field('modified', 'datetime', None, None, False,
SqlModel.sql_current_timestamp,
SqlModel.sql_current_timestamp),
Field('name', 'varchar', 200, None, False, None, None),
Field('label', 'varchar', 50, None, False, None, None),
# Lists metric uids.
Field('metrics', 'varchar', 3500, None, False, '[]', None),
# Whether the route/view for survey configuration is available
# for this team. Also restricts access to PUT /api/surveys/:id.
Field('survey_config_enabled', 'bool', None, None, False, 1, None),
Field('target_group_enabled', 'bool', None, None, False, 1, None),
# See https://github.com/PERTS/triton/issues/1632
# Set to False to enable cycleless mode. Teams won't create cycles,
# but there will be single cycle that is invisible to the end user.
# This cycle will still exist to set survey participation start/end
# dates.
Field('use_cycles', 'bool', None, None, False, 1, None),
# See https://github.com/PERTS/triton/issues/1641
# The specs in the linked LucidChart require the ability to hide
# the classrooms UI. Set to False to hide classrooms.
Field('use_classrooms', 'bool', None, None, False, 1, None),
# If there are already this many cycles, shouldn't be allowed to
# add more to the team. -1 means unlimited; translated in js to
# Infinity.
Field('max_cycles', 'tinyint', 3, None, False, -1, None),
# This many cycles will be automatically created when the team is
# created, can't if there are only this many cycles can't be
# deleted.
Field('min_cycles', 'tinyint', 3, True, False, 3, None),
Field('min_cycle_weekdays', 'tinyint', 3, True, False, 5, None),
Field('send_cycle_email', 'bool', None, None, False, 1, None),
# If there are already this many users, shouldn't be allowed to add
# more to the team. -1 means unlimited; translated in js to
# Infinity.
Field('max_team_members', 'tinyint', 3, None, False, -1, None),
Field('team_term', 'varchar', 50, None, False, 'Team', None),
Field('classroom_term', 'varchar', 50, None, False, 'Class', None),
Field('captain_term', 'varchar', 50, None, False, 'Captain', None),
Field('contact_term', 'varchar', 50, None, False, 'Main Contact',
None),
Field('member_term', 'varchar', 50, None, False, 'Teacher', None),
Field('organization_term', 'varchar', 50, None, False, 'Community',
None),
Field('preview_url', 'varchar', 1000, None, False, None, None),
# If this is false, teams can't be created with this program.
Field('active', 'bool', None, None, False, 1, None),
],
'primary_key': ['uid'],
'indices': [
{
'unique': True,
'name': 'label',
'fields': ['label'],
},
],
'engine':
'InnoDB',
'charset':
'utf8',
}
json_props = ['metrics']
@classmethod
def create(klass, **kwargs):
# Cycleless mode programs should only have a single cycle.
if 'use_cycles' in kwargs:
cycleless_mode = not kwargs['use_cycles']
invalid_cycles = (kwargs['min_cycles'] != 1
or kwargs['max_cycles'] != 1)
if cycleless_mode and invalid_cycles:
raise Exception(
"Programs set to cycleless mode can only have 1 cycle.")
if 'label' in kwargs:
kwargs['label'] = kwargs['label'].lower()
return super(klass, klass).create(**kwargs)
@classmethod
def get_by_label(klass, label):
programs = Program.get(label=label)
return programs[0] if len(programs) > 0 else None
class Survey(SqlModel):
table = 'survey'
py_table_definition = {
'table_name':
table,
'fields': [
# name, type, length, unsigned, null, default, on_update
Field('uid', 'varchar', 50, None, False, None, None),
Field('short_uid', 'varchar', 50, None, False, None, None),
Field('created', 'datetime', None, None, False,
SqlModel.sql_current_timestamp, None),
Field('modified', 'datetime', None, None, False,
SqlModel.sql_current_timestamp,
SqlModel.sql_current_timestamp),
Field('team_id', 'varchar', 50, None, False, None, None),
# Also stored on Team.
# Optional message to display above portal name field, e.g. "Please
# enter your student ID."
Field('portal_message', 'varchar', 2000, None, True,
SqlModel.sql_null, None),
# Enough for over a hundred different relationships.
# @todo(chris): enforce some limit in the Survey class.
Field('metrics', 'varchar', 3500, None, False, '[]', None),
Field('open_responses', 'varchar', 3500, None, False, '[]', None),
Field('meta', 'text', None, None, False, None, None),
Field('interval', 'int', 1, True, False, 2, None),
Field('notified', 'datetime', None, None, True, SqlModel.sql_null,
None),
],
'primary_key': ['uid'],
'indices': [
{
'unique': True,
'name': 'team',
'fields': ['team_id'],
},
],
'engine':
'InnoDB',
'charset':
'utf8',
}
json_props = ['metrics', 'open_responses', 'meta']
@classmethod
def create(klass, **kwargs):
"""Surveys have all metrics active by default.
This is so teams find it easy to run their initial "scan" survey.
"""
if 'metrics' not in kwargs:
# Default: all available metrics are active.
kwargs['metrics'] = [m.uid for m in Metric.get()]
if 'open_responses' not in kwargs:
# Default: all active metrics have open_responses active.
kwargs['open_responses'] = kwargs['metrics']
if 'meta' not in kwargs:
kwargs['meta'] = {}
return super(klass, klass).create(**kwargs)
@classmethod
def create_for_team(klass, team):
program = Program.get_by_id(team.program_id)
if not program:
raise Exception(
"Survey.create_for_team program not found: {}".format(
team.program_id))
program_metric_ids = [
m['uid'] for m in program.metrics if m['default_active']
]
return klass.create(
team_id=team.uid,
metrics=program_metric_ids,
open_responses=program_metric_ids,
)
def after_put(self, init_kwargs, *args, **kwargs):
is_using = self.meta.get('artifact_use', None) == 'true'
url = self.meta.get('artifact_url', None)
is_different = init_kwargs['meta'].get('artifact_url', None) != url
if is_using and url and is_different:
team = Team.get_by_id(self.team_id)
team_url = 'https://copilot.perts.net/teams/{}'.format(
team.short_uid)
Email.create(
to_address=config.from_server_email_address,
subject=(u"Message IT artifact URL needs review: {}".format(
team.name)),
html=u'''
<p>Team name: <a href="{team_url}">{team_name}</a></p>
<p>
Artifact URL:
<a href="{artifact_url}">{artifact_url}</a>
</p>
'''.format(
team_name=team.name,
artifact_url=self.meta['artifact_url'],
team_url=team_url,
),
).put()
def get_metrics(self):
if len(self.metrics) == 0:
return []
query = '''
SELECT *
FROM {table}
WHERE `uid` IN ({interps})
'''.format(table=Metric.table,
interps=','.join(['%s'] * len(self.metrics)))
params = tuple(self.metrics) # list of uids
with mysql_connection.connect() as sql:
results = sql.select_query(query, params)
return [Metric.row_dict_to_obj(d) for d in results]
def get_classrooms(self):
return Classroom.get(team_id=self.team_id)
@classmethod
def config_enabled(self, survey_id):
query = '''
SELECT p.`survey_config_enabled` as survey_config_enabled
FROM `survey` s
JOIN `team` t
ON s.`team_id` = t.`uid`
JOIN `program` p
ON t.`program_id` = p.`uid`
WHERE s.`uid` = %s
'''
params = (survey_id, )
with mysql_connection.connect() as sql:
results = sql.select_query(query, params)
if len(results) == 0:
# No program for this survey/team, default to enabled.
return True
return results[0]['survey_config_enabled'] == 1
def should_notify(self, today):
"""Should cron running this week actually send notifications?"""
cycle = Cycle.get_current_for_team(self.team_id, today)
if not cycle:
# The team hasn't scheduled a cycle for this week.
return False
if not self.notified or self.notified < date_to_dt(cycle.start_date):
# Either we've never notified this team, or we've notified them in
# past cycles. They're due for a notification.
self.notified = date_to_dt(today)
self.put()
return cycle
# Else the team has been notified during the current cycle; don't
# notify them again.
return False
def to_client_dict(self):
"""Add codes; add metric labels for survey params in Qualtrics."""
client_dict = super(Survey, self).to_client_dict()
if len(self.metrics) == 0:
metric_labels = {}
else:
metric_labels = {m.uid: m.label for m in self.get_metrics()}
client_dict['metric_labels'] = metric_labels
client_dict['codes'] = [c.code for c in self.get_classrooms()]
return client_dict
class Digest(SqlModel):
table = 'digest'
py_table_definition = {
'table_name':
table,
'fields': [
# name, type, length, unsigned, null, default, on_update
Field('uid', 'varchar', 50, None, False, None, None),
Field('short_uid', 'varchar', 50, None, False, None, None),
Field('created', 'datetime', None, None, False,
SqlModel.sql_current_timestamp, None),
Field('modified', 'datetime', None, None, False,
SqlModel.sql_current_timestamp,
SqlModel.sql_current_timestamp),
Field('user_id', 'varchar', 50, None, False, None, None),
# 'generic', 'survey', or 'report' as of 2017-07-26
Field('type', 'varchar', 50, None, False, None, None),
# Fully-rendered HTML with a <div> as root element.
# N.B. text fields aren't searchable.
Field('body', 'text', None, None, False, None, None),
Field('read', 'bool', None, None, False, 0, None),
],
'primary_key': ['uid'],
'indices': [
{
'name': 'user',
'fields': ['user_id'],
},
],
'engine':
'InnoDB',
'charset':
'utf8',
}
@property
def subject(self):
return {
'generic': ("You have notifications on Copilot."),
'survey': (u"It\x27s time to survey your students for Copilot!"),
'report': ("You have new reports in Copilot!"),
'main-contact': ("The main contact for your class has been "
"updated."),
}[self.type]
@classmethod
def create(klass, notifications):
if len({n.type for n in notifications}) > 1:
raise Exception("Mixed types: {}".format(notifications))
if len({n.user_id for n in notifications}) > 1:
raise Exception("Mixed users: {}".format(notifications))
prototype_note = notifications[0]
digest = super(klass, klass).create(
user_id=prototype_note.user_id,
type=prototype_note.type,
body='', # not finished yet, but we'll use the instance to render
)
# Three rendering steps:
# 1. Interpolating data in each notification via `n.render()` which
# becomes an "item". Data varies by notification.
# 2. Inserting all rendered items (html) in a digest body, which does
# not use escaping, leaving any potential data interpolation in the
# digest body alone (different bracket syntax).
# 3. Interpolating data in the digest body, assuming that the prototype
# notification has the necessary values.
items = ''.join(n.render() for n in notifications)
body_with_items = (jinja_items.get_template(
digest.get_template_path()).render(items=items))
digest.body = (jinja_env.from_string(body_with_items).render(
**prototype_note.template_params))
return digest
def get_template_path(self):
return 'notifications/digest_body_{}.html'.format(self.type)
@classmethod
def process_user(klass, user, start, end):
"""Query, digest, save, and send messages to a user."""
# Render separate notifications into digests, grouping by type.
user_notes = Notification.get_period_for_user(user, start, end)
digests = [
Digest.create(notes)
for notes in util.list_by(user_notes, 'type').values()
]
# Send messages to users about their new digests, based on preferences.
emails = []
smss = []
for d in digests:
if user.receive_email:
emails.append(d.get_email(user.email))
if user.receive_sms:
# @todo: install Twilio, create a sending queue
# smss.append(d.get_sms(user.phone_number))
pass
return digests, emails, smss
def get_email(self, to_address):
template = jinja_env.get_template('notifications/digest_email.html')
return Email.create(to_address=to_address,
subject=self.subject,
html=template.render(body=self.body))
def to_client_dict(self):
return dict(super(Digest, self).to_client_dict(), subject=self.subject)
class Network(SqlModel):
table = 'network'
py_table_definition = {
'table_name': table,
'fields': [
# name, type, length, unsigned, null, default, on_update
Field('uid', 'varchar', 50, None, False, None, None),
Field('short_uid', 'varchar', 50, None, False, None, None),
Field('created', 'datetime',None, None, False, SqlModel.sql_current_timestamp, None),
Field('modified', 'datetime',None, None, False, SqlModel.sql_current_timestamp, SqlModel.sql_current_timestamp),
Field('name', 'varchar', 200, None, False, None, None),
Field('program_id', 'varchar', 50, None, False, None, None),
Field('association_ids','varchar',3500, None, False, '[]', None),
Field('code', 'varchar', 50, None, False, None, None),
],
'primary_key': ['uid'],
'indices': [
{
'unique': True,
'name': 'code',
'fields': ['code'],
},
],
'engine': 'InnoDB',
'charset': 'utf8mb4',
'collate': 'utf8mb4_unicode_ci',
}
json_props = ['association_ids']
@classmethod
def create(klass, **kwargs):
if 'code' not in kwargs:
kwargs['code'] = klass.generate_unique_code()
# else the code is specified, and if it's a duplicate, MySQL will raise
# an exception b/c there's a unique index on that field.
return super(klass, klass).create(**kwargs)
@classmethod
def generate_unique_code(klass):
chars = string.ascii_uppercase + string.digits
for x in range(5):
code = ''.join(os_random.choice(chars) for x in range(6))
matches = klass.get(code=code)
if len(matches) == 0:
break
if len(matches) > 0:
raise Exception("After five tries, could not generate a unique"
"network invitation code.")
return code
@classmethod
def query_by_user(klass, user, program_id=None):
if len(user.owned_networks) == 0:
return []
query = '''
SELECT *
FROM `{table}`
WHERE `uid` IN ({ids}) {program_clause}
ORDER BY `name`
'''.format(
table=klass.table,
ids=','.join('%s' for uid in user.owned_networks),
program_clause='AND `program_id` = %s' if program_id else ''
)
params = tuple(user.owned_networks +
([program_id] if program_id else []))
with mysql_connection.connect() as sql:
row_dicts = sql.select_query(query, params)
return [klass.row_dict_to_obj(d) for d in row_dicts]
def before_put(self, init_kwargs, *args, **kwargs):
# Allow this to raise an exception to prevent bad associations from
# being saved.
self.associated_organization_ids(pending_network=self)
if self.uid in self.association_ids:
raise InvalidNetworkAssociation(
"Networks can't reference themselves: {}".format(self.uid)
)
def associated_organization_ids(self, depth=0, pending_network=None):
"""Traverse all network-to-network relationships to associated orgs.
Returns a flat and unique list of org ids.
"""
# While we support network-to-network, this recursive function could
# generate many inefficient db calls if we get carried away.
if depth >= 4:
raise InvalidNetworkAssociation(
"Too much depth in network associations: {}"
.format(self.uid)
)
org_ids = set()
for assc_id in self.association_ids:
kind = SqlModel.get_kind(assc_id)
if kind == 'Network':
# Note! This function is often run as a before_put check that
# the associations are valid. This means we have to consider
# the as-of-yet-unsaved "root" network (the `pending_network`)
# and not any version of it we might fetch from the db in order
# to catch the introduction of circular references.
if pending_network and assc_id == pending_network.uid:
child_network = pending_network
else:
child_network = Network.get_by_id(assc_id)
if child_network:
child_org_ids = child_network.associated_organization_ids(
depth=depth + 1,
pending_network=pending_network,
)
org_ids.update(child_org_ids)
else:
# No exception here because we don't want Networks to
# become unusable if an associated thing gets deleted.
# @todo: consider having this actually remove the
# association ids from the list.
logging.warning(
"Bad reference in {}: association {} doesn't exist."
.format(self.uid, assc_id)
)
elif kind == 'Organization':
org_ids.add(assc_id)
else:
raise InvalidNetworkAssociation(
"Invalid association kind: {}".format(kind))
return org_ids
class User(SqlModel):
"""Triton users.
To search among related ids, like `owned teams`, this is of interest:
SELECT * FROM `user` WHERE `owned_teams` LIKE BINARY '%Team_abcXYZ%'
https://stackoverflow.com/questions/2602252/mysql-query-string-contains
"""
table = 'user'
py_table_definition = {
'table_name': table,
'fields': [
# name, type, length, unsigned, null, default, on_update
Field('uid', 'varchar', 50, None, False, None, None),
Field('short_uid', 'varchar', 50, None, False, None, None),
Field('created', 'datetime',None, None, False, SqlModel.sql_current_timestamp, None),
Field('modified', 'datetime',None, None, False, SqlModel.sql_current_timestamp, SqlModel.sql_current_timestamp),
# 200 is good enough for Queen Elizabeth II:
# Elizabeth the Second, by the Grace of God, of the United Kingdom
# of Great Britain and Northern Ireland and of Her other Realms and
# Territories Queen, Head of the Commonwealth, Defender of the
# Faith.
Field('name', 'varchar', 200, None, True, SqlModel.sql_null, None),
Field('email', 'varchar', 200, None, False, None, None),
Field('phone_number', 'varchar', 50, None, True, SqlModel.sql_null, None),
# Options are 'super_admin' and 'user'
Field('user_type', 'varchar', 50, None, False, 'user', None),
# Not a text field because those aren't searchable, and we want to
# be able to find users who own a given team.
# Enough for over a hundred different relationships.
# @todo(chris): enforce some limit in the User class.
Field('owned_teams', 'varchar', 3500, None, False, '[]', None),
Field('owned_organizations','varchar',3500,None, False, '[]', None),
Field('owned_networks','varchar', 3500, None, False, '[]', None),
Field('receive_email', 'bool', None, None, False, 1, None),
Field('receive_sms', 'bool', None, None, False, 1, None),
# This is an int instead of a boolean because it needs to be
# nullable.
Field('consent', 'tinyint', 1, True, True, SqlModel.sql_null, None),
Field('recent_program_id', 'varchar', 50, None, True, SqlModel.sql_null, None),
],
'primary_key': ['uid'],
'indices': [
{
'unique': True,
'name': 'email',
'fields': ['email'],
},
],
'engine': 'InnoDB',
'charset': 'utf8',
}
json_props = ['owned_teams', 'owned_organizations', 'owned_networks']
@classmethod
def create(klass, **kwargs):
# Check lengths
for fieldName in ('owned_teams',):
field = next(f for f in klass.py_table_definition['fields']
if f.name == fieldName)
val = kwargs.get(field.name, [])
if len(val) > field.length:
raise Exception(
"Value for {} too long (max {}): {}"
.format(field.name, field.length, val)
)
return super(klass, klass).create(**kwargs)
@classmethod
def create_public(klass):
return super(klass, klass).create(
id='public',
name='public',
email='public',
user_type='public',
)
@classmethod
def query_by_name_or_email(klass, search_str):
# N.B. **Not** using LIKE BINARY allows case-insensitive search.
query = '''
SELECT *
FROM `{table}`
WHERE
`name` LIKE %s OR
`email` LIKE %s
ORDER BY `email`
'''.format(table=klass.table)
search_wildcard = '%{}%'.format(search_str)
params = (search_wildcard, search_wildcard)
with mysql_connection.connect() as sql:
row_dicts = sql.select_query(query, params)
return [klass.row_dict_to_obj(d) for d in row_dicts]
@classmethod
def query_by_team(klass, team_id_or_ids):
"""Warning: using many team ids is very inefficient. See
https://www.brentozar.com/archive/2010/06/sargable-why-string-is-slow/
"""
if type(team_id_or_ids) in (tuple, list):
team_ids = team_id_or_ids
else:
team_ids = [team_id_or_ids]
like_clause = ' OR '.join(
'`owned_teams` LIKE BINARY %s' for id in team_ids
)
query = '''
SELECT *
FROM `{table}`
WHERE {like_clause}
'''.format(table=klass.table, like_clause=like_clause)
params = tuple('%{}%'.format(id) for id in team_ids)
with mysql_connection.connect() as sql:
row_dicts = sql.select_query(query, params)
return [klass.row_dict_to_obj(d) for d in row_dicts]
@classmethod
def query_by_organization(klass, org_id):
query = '''
SELECT *
FROM `{table}`
WHERE `owned_organizations` LIKE BINARY %s
'''.format(table=klass.table)
params = ('%{}%'.format(org_id),)
with mysql_connection.connect() as sql:
row_dicts = sql.select_query(query, params)
return [klass.row_dict_to_obj(d) for d in row_dicts]
@classmethod
def query_by_network(klass, network_id):
query = '''
SELECT *
FROM `{table}`
WHERE `owned_networks` LIKE BINARY %s
'''.format(table=klass.table)
params = ('%{}%'.format(network_id),)
with mysql_connection.connect() as sql:
row_dicts = sql.select_query(query, params)
return [klass.row_dict_to_obj(d) for d in row_dicts]
@classmethod
def resolve_id_mismatch(klass, user, new_id):
"""Change all references to user's id to a new id.
N.B. this is obviously brittle; when the relationship schema changes,
this will also have to change.
"""
# The auth server has a different id for this user; defer to it.
teams = Team.get(captain_id=user.uid)
for t in teams:
t.captain_id = new_id
Team.put_multi(teams)
classrooms = Classroom.get(contact_id=user.uid)
for c in classrooms:
c.contact_id = new_id
Classroom.put_multi(classrooms)
params = {'uid': new_id, 'short_uid': SqlModel.convert_uid(new_id)}
with mysql_connection.connect() as sql:
sql.update_row(klass.table, 'uid', user.uid, **params)
for k, v in params.items():
setattr(user, k, v)
return user
@classmethod
def get_by_auth(klass, auth_type, auth_id):
"""Exists for parity with Neptune user methods."""
if auth_type != 'email':
raise Exception("Triton only accepts auth_type 'email', given {}."
.format(auth_type))
email = auth_id.lower()
matches = User.get(email=auth_id.lower(), order='created')
if len(matches) == 0:
return None
elif len(matches) == 1:
return matches[0]
elif len(matches) > 1:
logging.error(u"More than one user matches auth info: {}, {}."
.format(auth_type, auth_id))
# We'll let the function pass on and take the first of multiple
# duplicate users, which will be the earliest-created one.
return matches[0]
@classmethod
def email_exists(self, email):
"""Exists for parity with Neptune user methods."""
return len(User.get(email=email.lower())) > 0
@classmethod
def get_main_contacts(klass, classrooms):
if len(classrooms) == 0:
return []
query = '''
SELECT *
FROM `{table}`
WHERE `uid` IN ({interps})
'''.format(
table=klass.table,
interps=','.join(['%s'] * len(classrooms))
)
params = tuple(c.contact_id for c in classrooms)
with mysql_connection.connect() as sql:
row_dicts = sql.select_query(query, params)
return [klass.row_dict_to_obj(d) for d in row_dicts]
@property
def super_admin(self):
return self.user_type == 'super_admin'
@property
def first_name(self):
if self.name and ' ' in self.name:
return self.name.split(' ')[0]
else:
return self.name
def get_owner_property(self, id_or_entity):
owner_props = {
'Team': 'owned_teams',
'Organization': 'owned_organizations',
}
kind = SqlModel.get_kind(id_or_entity)
return getattr(self, owner_props[kind])if kind in owner_props else None
def get_networked_organization_ids(self):
networked_org_ids = set()
for network in Network.get_by_id(self.owned_networks):
networked_org_ids.update(network.associated_organization_ids())
if len(networked_org_ids) >= 100:
logging.error(
"100 or more orgs associated to user via network: {}"
.format(self.uid)
)
return networked_org_ids
def after_put(self, init_kwargs, *args, **kwargs):
"""Reset memcache for related objects
* Include _all_ those the user is joining (e.g. on creation) as well as
any the user is leaving.
* Include name changes stored elsewhere
"""
rels = ((Team, 'owned_teams'), (Organization, 'owned_organizations'))
for model, attr in rels:
original_ids = set(init_kwargs[attr])
new_ids = set(getattr(self, attr))
leaving_ids = original_ids.difference(new_ids)
for uid in set(new_ids).union(leaving_ids):
model.update_cached_properties(uid)
# If this user is the contact for any classrooms, and their name has
# changed, update the name of the classroom.
if init_kwargs['name'] != self.name:
for c in Classroom.get(contact_id=self.uid):
key = util.cached_properties_key(c.uid)
cached_props = memcache.get(key) or {}
memcache.set(key, dict(cached_props, contact_name=self.name))
def to_client_dict(self, is_self=True):
d = super(User, self).to_client_dict()
# Compile network-to-org associations to avoid duplicating logic on the
# client.
d['networked_organizations'] = list(
self.get_networked_organization_ids())
# When not your own user, strip sensitive properties.
safe_properties = (
'uid',
'short_uid',
'email',
'name',
'networked_organizations',
'owned_organizations',
'owned_teams',
'phone_number',
)
if not is_self:
for k in d.keys():
if k not in safe_properties:
del d[k]
return d
def __init__(self):
self.ui = UI()
self.field = Field()
self.heatmap = False
self.color = Console.colors["blue"]
class Classroom(SqlModel):
table = 'classroom'
py_table_definition = {
'table_name':
table,
'fields': [
# name, type, length, unsigned, null, default, on_update
Field('uid', 'varchar', 50, None, False, None, None),
Field('short_uid', 'varchar', 50, None, False, None, None),
Field('created', 'datetime', None, None, False,
SqlModel.sql_current_timestamp, None),
Field('modified', 'datetime', None, None, False,
SqlModel.sql_current_timestamp,
SqlModel.sql_current_timestamp),
Field('name', 'varchar', 200, None, True, SqlModel.sql_null, None),
Field('team_id', 'varchar', 50, None, False, None, None),
Field('code', 'varchar', 50, None, False, None, None),
Field('contact_id', 'varchar', 50, None, False, None, None),
Field('num_students', 'smallint', 5, True, False, 0, None),
Field('grade_level', 'varchar', 50, None, True, SqlModel.sql_null,
None),
],
'primary_key': ['uid'],
'indices': [],
'engine':
'InnoDB',
'charset':
'utf8',
}
default_cached_properties = {
'contact_name': '',
'contact_email': '',
}
@property
def url_code(self):
return self.code.replace(' ', '-')
@classmethod
def query_by_name(klass, name, program_id=None):
# N.B. **Not** using LIKE BINARY allows case-insensitive search.
query = '''
SELECT c.*, t.`name` as team_name
FROM classroom c
JOIN team t
ON c.`team_id` = t.`uid`
WHERE c.`name` LIKE %s
{program_clause}
ORDER BY c.`name`
'''.format(
program_clause='AND t.`program_id` = %s' if program_id else '')
name_wildcard = '%{}%'.format(name)
params = (name_wildcard,
program_id) if program_id else (name_wildcard, )
with mysql_connection.connect() as sql:
row_dicts = sql.select_query(query, params)
return [klass.row_dict_to_obj(d) for d in row_dicts]
@classmethod
def query_by_contact(klass, user, program_id=None):
query = '''
SELECT c.*, t.`name` as team_name
FROM classroom c
JOIN team t
ON c.`team_id` = t.`uid`
WHERE c.`contact_id` = %s
{program_clause}
ORDER BY c.`name`
'''.format(
program_clause='AND t.`program_id` = %s' if program_id else '')
params = (user.uid, program_id) if program_id else (user.uid, )
with mysql_connection.connect() as sql:
row_dicts = sql.select_query(query, params)
return [klass.row_dict_to_obj(d) for d in row_dicts]
@classmethod
def get_by_program(klass, program_id):
"""Db query to get classrooms on teams in a program, uses a JOIN."""
query = '''
SELECT c.*
FROM `classroom` c
JOIN `team` t
ON c.`team_id` = t.`uid`
WHERE t.`program_id` = %s
'''
params = (program_id, )
with mysql_connection.connect() as sql:
results = sql.select_query(query, params)
return [klass.row_dict_to_obj(d) for d in results]
@classmethod
def query_by_teams(klass, team_ids):
query = '''
SELECT *
FROM `{table}`
WHERE `team_id` IN ({interps})
'''.format(table=klass.table,
interps=', '.join(['%s'] * len(team_ids)))
params = tuple(team_ids)
with mysql_connection.connect() as sql:
row_dicts = sql.select_query(query, params)
return [klass.row_dict_to_obj(r) for r in row_dicts]
@classmethod
def get_cached_properties_from_db(self, classroom_id):
"""What's the name of the main contact?"""
query = '''
SELECT
u.`name` as name,
u.`email` as email
FROM `classroom` c
JOIN `user` u
ON c.`contact_id` = u.`uid`
WHERE c.`uid` = %s
'''
params = (classroom_id, )
with mysql_connection.connect() as sql:
rows = sql.select_query(query, params)
if len(rows) == 1:
return {
'contact_name': rows[0]['name'],
'contact_email': rows[0]['email'],
}
return {}
@classmethod
def update_cached_properties(klass, classroom_id):
from_db = Classroom.get_cached_properties_from_db(classroom_id)
if from_db:
memcache.set(util.cached_properties_key(classroom_id), from_db)
return from_db
@classmethod
def get_by_code(klass, code):
classrooms = Classroom.get(code=code)
return classrooms[0] if classrooms else None
def after_put(self, init_kwargs):
"""Update cached properties in response to classrooms changing.
* Team's number of classrooms
* Classroom's contact name and email
"""
original_contact_id = init_kwargs['contact_id']
if self.contact_id != original_contact_id:
Classroom.update_cached_properties(self.uid)
Team.update_cached_properties(self.team_id)
def to_client_dict(self):
"""Decorate the team with counts of related objects; cached."""
d = super(Classroom, self).to_client_dict()
# If the team name is available (returned by some custom queries),
# ndb's to_dict() will exclude it. Put it back so we can use it.
if hasattr(self, 'team_name'):
d['team_name'] = self.team_name
d.update(self.default_cached_properties)
cached = memcache.get(util.cached_properties_key(self.uid))
if cached:
d.update(cached)
else:
d.update(Classroom.update_cached_properties(self.uid))
return d
class Participant(SqlModel):
table = 'participant'
py_table_definition = {
'table_name':
table,
'fields': [
# name, type, length, unsigned, null, default, on_update
Field('uid', 'varchar', 50, None, False, None, None),
Field('short_uid', 'varchar', 50, None, False, None, None),
Field('created', 'datetime', None, None, False,
SqlModel.sql_current_timestamp, None),
Field('modified', 'datetime', None, None, False,
SqlModel.sql_current_timestamp,
SqlModel.sql_current_timestamp),
# School's internal, unique, ID
Field('student_id', 'varchar', 100, None, False, None, None),
Field('stripped_student_id', 'varchar', 100, None, False, None,
None),
Field('team_id', 'varchar', 50, None, False, None, None),
Field('classroom_ids', 'varchar', 3500, None, False, None, None),
Field('in_target_group', 'bool', None, None, False, 0, None),
],
'primary_key': ['uid'],
'indices': [
# Student ID should be unique to a Team
{
'name': 'team-student',
'fields': ['team_id', 'stripped_student_id'],
'unique': True,
},
],
'engine':
'InnoDB',
'charset':
'utf8',
}
json_props = ['classroom_ids']
@classmethod
def create(klass, **kwargs):
kwargs['stripped_student_id'] = klass.strip_token(kwargs['student_id'])
return super(klass, klass).create(**kwargs)
@classmethod
def strip_name(klass, s):
"""Returns lowercase-ified str, without special chars.
See var f for only allowable chars to return.
"""
# *Replace* unicode special chars with closest related char, decode to
# string from unicode.
if isinstance(s, unicode):
s = unicodedata.normalize('NFKD', s).encode('ascii', 'ignore')
s = s.lower()
f = 'abcdefghijklmnopqrstuvwxyz0123456789'
return str(filter(lambda x: x in f, s))
@classmethod
def strip_token(self, raw_token):
# Must match the behavior of `stripToken()` in the neptune portal.
# Currently in app_participate/name_or_id/name_or_id.component.js
# function stripToken(rawToken) {
# return typeof rawToken !== 'string'
# ? undefined
# : rawToken.toLowerCase().replace(/[^a-z0-9]/g, '');
# }
if not isinstance(raw_token, basestring):
return None
return re.sub(r'[^a-z0-9]', '', unicode(raw_token).lower())
@classmethod
def get_for_team(klass, team_id, student_ids=None):
if not student_ids:
return klass.get(team_id=team_id)
stripped_ids = [klass.strip_token(id) for id in student_ids]
query = '''
SELECT *
FROM `{table}`
WHERE `team_id` = %s
AND `stripped_student_id` IN({interps})
'''.format(
table=klass.table,
interps=','.join(['%s'] * len(stripped_ids)),
)
params = tuple([team_id] + stripped_ids)
with mysql_connection.connect() as sql:
row_dicts = sql.select_query(query, params)
return [klass.row_dict_to_obj(d) for d in row_dicts]
@classmethod
def get_for_classroom(klass, team_id, classroom_id, student_ids=None):
if student_ids is None:
student_ids = []
stripped_ids = [klass.strip_token(id) for id in student_ids]
student_id_clause = 'AND `stripped_student_id` IN({})'.format(','.join(
['%s'] * len(stripped_ids)))
query = '''
SELECT *
FROM `{table}`
WHERE
# While the query would run without the team id (b/c classroom
# would constrain it), including it is useful because when a
# student id is included, the engine can then use the table's
# team-student_id index. Try this query with EXPLAIN to see more.
`team_id` = %s AND
`classroom_ids` LIKE BINARY %s
{student_id_clause}
'''.format(
table=klass.table,
student_id_clause=student_id_clause if stripped_ids else '',
)
params = [team_id, '%{}%'.format(classroom_id)]
if stripped_ids:
params.append(stripped_ids)
with mysql_connection.connect() as sql:
row_dicts = sql.select_query(query, tuple(params))
return [klass.row_dict_to_obj(d) for d in row_dicts]
@classmethod
def count_for_classroom(klass, classroom_id):
query = '''
SELECT COUNT(`uid`)
FROM `{table}`
WHERE `classroom_ids` LIKE BINARY %s
'''.format(table=klass.table)
params = ['%{}%'.format(classroom_id)]
with mysql_connection.connect() as sql:
num_students = sql.select_single_value(query, tuple(params))
return num_students
class Response(SqlModel):
table = 'response'
USER_LEVEL_SYMBOL = 'User'
TEAM_LEVEL_SYMBOL = 'Team'
CYCLE_LEVEL_SYMBOL = 'Cycle'
py_table_definition = {
'table_name': table,
'fields': [
# name, type, length, unsigned, null, default, on_update
Field('uid', 'varchar', 50, None, False, None, None),
Field('short_uid', 'varchar', 50, None, False, None, None),
Field('created', 'datetime',None, None, False, SqlModel.sql_current_timestamp, None),
Field('modified', 'datetime',None, None, False, SqlModel.sql_current_timestamp, SqlModel.sql_current_timestamp),
# Can be 'User', 'Team', or 'Cycle'
Field('type', 'varchar', 20, None, False, None, None),
# The default here is to keep responses private, just for safety,
# b/c in practice the default is set in Response.create().
Field('private', 'bool', None, None, False, 1, None),
# Empty string if this is a 'Team' or 'Cycle' type
Field('user_id', 'varchar', 50, None, False, None, None),
Field('team_id', 'varchar', 50, None, False, None, None),
# Most often a cycle id.
Field('parent_id', 'varchar', 50, None, True, SqlModel.sql_null, None),
# Name of the module within the cycle that recorded this
# response.
Field('module_label', 'varchar', 50, None, False, None, None),
# Tinyint unsigned allows 0 to 255. Normal operation stores progress
# values within 0 to 100.
Field('progress', 'tinyint', 4, True, False, 0, None),
Field('page', 'tinyint', 4, True, True, SqlModel.sql_null, None),
Field('body', 'text', None, None, True, None, None),
],
'primary_key': ['uid'],
'indices': [
{
'name': 'parent',
'fields': ['parent_id'],
},
{
'name': 'user',
'fields': ['user_id'],
},
{
'name': 'type-user-team-parent-module',
'unique': True,
'fields': [
'type',
'user_id',
'team_id',
'parent_id',
'module_label'
],
},
],
'engine': 'InnoDB',
'charset': 'utf8',
}
json_props = ['body']
@classmethod
def create(klass, **kwargs):
is_user_id = klass.get_kind(kwargs['user_id']) == 'User'
kwargs['type'] = kwargs.get('type', 'User')
is_team_level = kwargs['type'] == klass.TEAM_LEVEL_SYMBOL
is_cycle_level = kwargs['type'] == klass.CYCLE_LEVEL_SYMBOL
is_user_level = kwargs['type'] == klass.USER_LEVEL_SYMBOL
if (not is_user_id and not (is_team_level or is_cycle_level)):
raise Exception(
"Invalid `user_id` for response: {}"
.format(kwargs['user_id'])
)
if 'private' not in kwargs:
# By default, user-level responses are private, others are not.
kwargs['private'] = is_user_level
return super(klass, klass).create(**kwargs)
@classmethod
def insert_or_conflict(klass, params):
"""Put a new entity to the db, or raise if it exists.
Returns the inserted response.
Raises ResponseIndexConflict, JsonTextValueLengthError, or
JsonTextDictLengthError.
"""
# The tactic is to skip the normal insert-or-update check and do a
# blind insert, then catch any duplicate index errors.
# Make sure to pick up any defaults and make any checks in the create
# method, since this is a new entity.
response = klass.create(**params)
# Note this does NOT use the typical .put() interface, so we have to be
# careful to re-create all the normal features, like the before_put and
# after_put hooks.
# In this case, before_put has sanity checks.
response.before_put(response._init_kwargs)
row_to_insert = klass.coerce_row_dict(response.to_dict()) # py -> sql
try:
with mysql_connection.connect() as sql:
sql.insert_row_dicts(klass.table, [row_to_insert])
row = sql.select_star_where(klass.table, uid=response.uid)[0]
except MySQLdb.IntegrityError as e:
# Expected when there's already a respond matching these params in
# the type-user-team-parent-module index.
raise ResponseIndexConflict()
inserted_response = klass.row_dict_to_obj(row) # sql -> py
# Responses are backed up to other tables with this hook.
inserted_response.after_put(response._init_kwargs)
return inserted_response
@classmethod
def update_or_conflict(klass, response_id, params, force):
"""Update an existing response in the db, and raise if any updated
fields are out of date.
Args:
response_id: str, uid of the response to update
params: dict, new data to save to the response
force: bool, whether the user acknowledged a conflict on this
update previously and has chosen to write to it anyway.
See Response.mix_body()
Returns the updated response.
Raises ResponseNotFound, ResponseBodyKeyConflict,
JsonTextValueLengthError, or JsonTextDictLengthError.
MySQLdb uses transactions by default, and waits for you to commit or
or roll back. That means to transactionalize this operation, we just
need to
1. Use a SELECT... FOR UPDATE query, which locks the row.
2. Pack all of the transactions' queries into the same with: block.
http://mysql-python.sourceforge.net/MySQLdb.html
https://riptutorial.com/mysql/example/24166/row-level-locking
"""
# Avoid retries, which close and re-open the connection, which might
# interrupt our transaction.
with mysql_connection.connect(retry_on_error=False) as sql:
# Locks the row until commit (when this block exits).
rows = sql.select_row_for_update(klass.table, 'uid', response_id)
if not rows:
raise ResponseNotFound()
row = klass.coerce_row_dict(rows[0]) # sql -> python
# Update any new values in the body. Assume any other parameters
# have been approved by the api handler.
params['body'] = Response.mix_body(
row['body'], params['body'], force)
# Run the checks in before_put(). Don't just do entity.put()
# since that would commit the transaction. Instead mock just enough
# of an entity to run the sanity checks.
temp_entity = klass(**params)
temp_entity.before_put(temp_entity._init_kwargs)
new_row = klass.coerce_row_dict(params) # python -> sql
sql.update_row(klass.table, 'uid', response_id, **new_row)
# w/ updated modified time
row = sql.select_star_where(klass.table, uid=response_id)[0]
updated_response = klass.row_dict_to_obj(row) # sql -> py
# Responses are backed up to other tables with this hook.
updated_response.after_put(temp_entity._init_kwargs)
return updated_response
@classmethod
def get_for_teams_unsafe(klass, team_ids, parent_id=None):
"""Does NOT strip the body property of any responses."""
if parent_id:
where = 'AND `parent_id` = %s'
params = tuple(team_ids) + (parent_id,)
else:
where = ''
params = tuple(team_ids)
query = '''
SELECT *
FROM `{table}`
WHERE `team_id` IN({interps})
{where}
'''.format(
table=klass.table,
interps=', '.join(['%s'] * len(team_ids)),
where=where,
)
with mysql_connection.connect() as sql:
row_dicts = sql.select_query(query, params)
unsafe_responses = [klass.row_dict_to_obj(d) for d in row_dicts]
return unsafe_responses
@classmethod
def get_for_teams(klass, user, team_ids, parent_id=None):
unsafe = klass.get_for_teams_unsafe(team_ids, parent_id)
return klass.redact_private_responses(unsafe, user)
@classmethod
def redact_private_responses(klass, unsafe_responses, user):
"""Clears body for private responses except own user-level."""
if user.super_admin:
return unsafe_responses
responses = []
for r in unsafe_responses:
if r.private:
if r.type == klass.USER_LEVEL_SYMBOL and r.user_id == user.uid:
# Although this is private, the given user owns it; let
# them see the data in the body.
pass
else:
# This response is private and it doesn't belong to the
# given user. Redact.
r.body = {}
else:
# This response is not private. Don't redact.
pass
responses.append(r)
return responses
@classmethod
def mix_body(self, old_body, new_body, force=False):
"""Updates a response body intelligently, mixing new and modified keys
with existing ones.
Returns dict of the new body mixed into the old.
Raises ResponseBodyKeyConflict
"""
current_timestamp = datetime.datetime.now()
body = copy.deepcopy(old_body)
conflicted_keys = []
for k, info in new_body.items():
# Incoming data to mix with the old body.
value = info.get('value', None)
mod = info.get('modified', None)
if force:
# Client has seen the warning and wants to override data.
should_write = True
elif k not in body:
# New key, just add it.
should_write = True
elif k in body and value == body[k]['value']:
# Unchanged key, don't update timestamp.
should_write = False
elif k in body and mod == body[k]['modified']:
# New value for this key, client has fresh data.
should_write = True
elif k in body and mod < body[k]['modified']:
# Client has stale data. Will raise after loop is done.
should_write = False
conflicted_keys.append(k)
elif k in body and mod > body[k]['modified']:
raise Exception(
"Got modified time newer than db: {}: {}".format(k, mod))
else:
raise Exception("Unknown conflict case.")
if should_write:
body[k] = {
'value': value,
'modified': current_timestamp,
}
if len(conflicted_keys) > 0:
# Don't commit any of the incoming changes. Tell the client which
# keys have conflicts.
raise ResponseBodyKeyConflict(conflicted_keys)
return body
def before_put(self, init_kwargs):
if self.body is None:
return
if not isinstance(self.body, dict):
raise Exception(
u"Expected dictionary for response body, got {}: {}"
.format(type(self.body), self.body)
)
if len(self.body) >= JSON_TEXT_DICT_MAX:
raise JsonTextDictLengthError()
for k, v in self.body.items():
value_text = json.dumps(v, default=util.json_dumps_default)
if len(value_text) >= JSON_TEXT_VALUE_MAX:
raise JsonTextValueLengthError()
body_text = json.dumps(self.body, default=util.json_dumps_default)
if len(body_text) >= JSON_TEXT_MAX:
raise JsonTextLengthError()
def after_put(self, *args, **kwargs):
"""Save a copy of this response to the backup table.
Taskqueue docs:
https://cloud.google.com/appengine/docs/standard/python/refdocs/google.appengine.api.taskqueue#google.appengine.api.taskqueue.add
"""
taskqueue.add(
url='/task/backup_response',
payload=json.dumps(self.to_dict(), default=util.json_dumps_default),
headers={'Content-Type': 'application/json'},
# default retry_options makes 5 attempts
)
class Organization(SqlModel):
table = 'organization'
py_table_definition = {
'table_name':
table,
'fields': [
# name, type, length, unsigned, null, default, on_update
Field('uid', 'varchar', 50, None, False, None, None),
Field('short_uid', 'varchar', 50, None, False, None, None),
Field('created', 'datetime', None, None, False,
SqlModel.sql_current_timestamp, None),
Field('modified', 'datetime', None, None, False,
SqlModel.sql_current_timestamp,
SqlModel.sql_current_timestamp),
Field('name', 'varchar', 200, None, True, SqlModel.sql_null, None),
Field('code', 'varchar', 50, None, False, None, None),
Field('phone_number', 'varchar', 50, None, True, SqlModel.sql_null,
None),
Field('mailing_address', 'varchar', 500, None, True,
SqlModel.sql_null, None),
# Teams created with this org get this program association.
Field('program_id', 'varchar', 50, None, False, None, None),
],
'primary_key': ['uid'],
'indices': [
{
'name': 'name',
'fields': ['name'],
},
{
'unique': True,
'name': 'code',
'fields': ['code'],
},
],
'engine':
'InnoDB',
'charset':
'utf8',
}
default_cached_properties = {
'num_teams': 0,
'num_users': 0,
}
@classmethod
def create(klass, **kwargs):
if 'code' not in kwargs:
kwargs['code'] = klass.generate_unique_code()
# else the code is specified, and if it's a duplicate, MySQL will raise
# an exception b/c there's a unique index on that field.
return super(klass, klass).create(**kwargs)
@classmethod
def generate_unique_code(klass):
chars = string.ascii_uppercase + string.digits
for x in range(5):
code = ''.join(os_random.choice(chars) for x in range(6))
matches = klass.get(code=code)
if len(matches) == 0:
break
if len(matches) > 0:
raise Exception("After five tries, could not generate a unique"
"organization invitation code.")
return code
@classmethod
def query_by_name(klass, name, program_id=None):
# N.B. **Not** using LIKE BINARY allows case-insensitive search.
query = '''
SELECT *
FROM `{table}`
WHERE `name` LIKE %s
{program_clause}
ORDER BY `name`
'''.format(
table=klass.table,
program_clause='AND `program_id` = %s' if program_id else '')
name_wildcard = '%{}%'.format(name)
params = (name_wildcard,
program_id) if program_id else (name_wildcard, )
with mysql_connection.connect() as sql:
row_dicts = sql.select_query(query, params)
return [klass.row_dict_to_obj(d) for d in row_dicts]
@classmethod
def query_by_user(klass, user, program_id=None):
if len(user.owned_organizations) == 0:
return []
query = '''
SELECT *
FROM `{table}`
WHERE `uid` IN ({ids}) {program_clause}
ORDER BY `name`
'''.format(
table=klass.table,
ids=','.join('%s' for uid in user.owned_organizations),
program_clause='AND `program_id` = %s' if program_id else '')
params = tuple(user.owned_organizations +
([program_id] if program_id else []))
with mysql_connection.connect() as sql:
row_dicts = sql.select_query(query, params)
return [klass.row_dict_to_obj(d) for d in row_dicts]
@classmethod
def query_by_team(klass, team_id):
query = '''
SELECT o.*
FROM `organization` o
JOIN `team` t
ON t.`organization_ids` LIKE BINARY CONCAT('%%', o.`uid`, '%%')
WHERE t.`uid` = %s
ORDER BY o.`name`
'''
params = (team_id, )
with mysql_connection.connect() as sql:
row_dicts = sql.select_query(query, params)
return [klass.row_dict_to_obj(d) for d in row_dicts]
@classmethod
def get_cached_properties_from_db(self, org_id):
"""Count how many related users and teams there are."""
query = '''
SELECT COUNT(DISTINCT(t.`uid`)) as num_teams
, COUNT(DISTINCT(u.`uid`)) as num_users
FROM `organization` o
LEFT OUTER JOIN `user` u
ON u.`owned_organizations` LIKE BINARY %s
LEFT OUTER JOIN `team` t
ON t.`organization_ids` LIKE BINARY %s
WHERE o.`uid` = %s
'''
params = (
'%{}%'.format(org_id),
'%{}%'.format(org_id),
org_id,
)
with mysql_connection.connect() as sql:
row_dicts = sql.select_query(query, params)
if len(row_dicts) == 0:
return {}
elif len(row_dicts) == 1:
# {'num_users': int, 'num_teams': int}
return {k: int(v) for k, v in row_dicts[0].items()}
else:
raise Exception(
"Multiple results for organization cached properties.")
@classmethod
def update_cached_properties(klass, org_id):
"""If we find the org in the db, query for rel counts and cache."""
from_db = Organization.get_cached_properties_from_db(org_id)
if from_db:
memcache.set(util.cached_properties_key(org_id), from_db)
return from_db
def to_client_dict(self):
"""Decorate the org with counts of related objects; cached."""
d = super(Organization, self).to_client_dict()
# Decorate the team with counts of related objects; cached.
d.update(self.default_cached_properties)
cached = memcache.get(util.cached_properties_key(self.uid))
if cached:
d.update(cached)
else:
d.update(Organization.update_cached_properties(self.uid))
return d
class Team(SqlModel):
table = 'team'
py_table_definition = {
'table_name':
table,
'fields': [
# name, type, length, unsigned, null, default, on_update
Field('uid', 'varchar', 50, None, False, None, None),
Field('short_uid', 'varchar', 50, None, False, None, None),
Field('created', 'datetime', None, None, False,
SqlModel.sql_current_timestamp, None),
Field('modified', 'datetime', None, None, False,
SqlModel.sql_current_timestamp,
SqlModel.sql_current_timestamp),
Field('name', 'varchar', 200, None, True, SqlModel.sql_null, None),
Field('captain_id', 'varchar', 50, None, False, None, None),
# Enough for over a hundred different relationships.
# @todo(chris): enforce some limit in the Organization class.
Field('organization_ids', 'varchar', 3500, None, False, '[]',
None),
Field('program_id', 'varchar', 50, None, False, None, None),
# Also stored on Survey.
Field('survey_reminders', 'bool', None, None, False, 1, None),
Field('report_reminders', 'bool', None, None, False, 1, None),
Field('target_group_name', 'varchar', 200, None, True,
SqlModel.sql_null, None),
# N.B. text fields aren't searchable.
# Also note this field defaults to a json dictionary in create().
# Aside: why not use a varchar and set a default in the db?
# Because this field may accept user text input, which might be
# quite long. Still, overly long input to one key in this dict
# could erase _other_ data saved in the structure, so
# before_put() checks it before storing.
Field('task_data', 'text', None, None, False, None, None),
],
'primary_key': ['uid'],
'indices': [],
'engine':
'InnoDB',
'charset':
'utf8',
}
json_props = ['organization_ids', 'task_data']
default_cached_properties = {
'num_classrooms': 0,
'num_users': 0,
'participation_base': 0,
}
@classmethod
def create(klass, *args, **kwargs):
team = super(klass, klass).create(*args, **kwargs)
if team.task_data is None:
team.task_data = {}
return team
@classmethod
def query_by_name(klass, name, program_id=None):
# N.B. **Not** using LIKE BINARY allows case-insensitive search.
query = '''
SELECT *
FROM `{table}`
WHERE `name` LIKE %s
{program_clause}
ORDER BY `name`
'''.format(
table=klass.table,
program_clause='AND `program_id` = %s' if program_id else '')
name_wildcard = '%{}%'.format(name)
params = (name_wildcard,
program_id) if program_id else (name_wildcard, )
with mysql_connection.connect() as sql:
row_dicts = sql.select_query(query, params)
return [klass.row_dict_to_obj(d) for d in row_dicts]
@classmethod
def query_by_user(klass, user, program_id=None):
if len(user.owned_teams) == 0:
return []
query = '''
SELECT *
FROM `{table}`
WHERE `uid` IN ({ids}) {program_clause}
ORDER BY `name`
'''.format(
table=klass.table,
ids=','.join('%s' for uid in user.owned_teams),
program_clause='AND `program_id` = %s' if program_id else '')
params = tuple(user.owned_teams + ([program_id] if program_id else []))
with mysql_connection.connect() as sql:
row_dicts = sql.select_query(query, params)
return [klass.row_dict_to_obj(d) for d in row_dicts]
@classmethod
def query_by_organization(klass, org_id):
query = '''
SELECT *
FROM `{table}`
WHERE `organization_ids` LIKE BINARY %s
ORDER BY `name`
'''.format(table=klass.table)
params = ('%{}%'.format(org_id), )
with mysql_connection.connect() as sql:
row_dicts = sql.select_query(query, params)
return [klass.row_dict_to_obj(d) for d in row_dicts]
@classmethod
def get_cached_properties_from_db(self, team_id):
"""Count how many related users and classrooms there are.
N.B. participation_base is not the number of unique students on the
team. Rather it is the number of times we expect the survey to completed
by students to reach 100% participation. Students who are in multiple
classrooms are expected to complete the survey multiple times.
"""
classroom_query = '''
SELECT COUNT(`uid`) as num_classrooms
, IFNULL(SUM(`num_students`), 0) as participation_base
FROM `classroom`
WHERE `team_id` = %s;
'''
classroom_params = (team_id, )
user_query = '''
SELECT COUNT(`uid`) as num_users
FROM `user`
WHERE `owned_teams` LIKE BINARY %s;
'''
user_params = ('%{}%'.format(team_id), )
with mysql_connection.connect() as sql:
classroom_rows = sql.select_query(classroom_query,
classroom_params)
user_rows = sql.select_query(user_query, user_params)
if len(classroom_rows) > 1 or len(user_rows) > 1:
raise Exception("Multiple results for team cached properties.")
return {
'num_classrooms': int(classroom_rows[0]['num_classrooms']),
'participation_base': int(classroom_rows[0]['participation_base']),
'num_users': int(user_rows[0]['num_users']),
}
@classmethod
def update_cached_properties(klass, team_id):
"""If we find the team in the db, query for rel counts and cache."""
from_db = Team.get_cached_properties_from_db(team_id)
if from_db:
memcache.set(util.cached_properties_key(team_id), from_db)
return from_db
def before_put(self, init_kwargs):
if len(self.task_data) >= JSON_TEXT_DICT_MAX:
raise JsonTextDictLengthError()
for k, v in self.task_data.items():
if len(json.dumps(v)) >= JSON_TEXT_VALUE_MAX:
raise JsonTextValueLengthError()
if len(json.dumps(self.task_data)) >= JSON_TEXT_MAX:
raise JsonTextLengthError()
def after_put(self, init_kwargs):
"""Reset memcache for related objects
Include _all_ those the team is joining (e.g. on creation) as well as
any the team is leaving.
"""
rels = ((Organization, 'organization_ids'), )
for model, attr in rels:
original_ids = set(init_kwargs[attr])
new_ids = set(getattr(self, attr))
leaving_ids = original_ids.difference(new_ids)
for uid in set(new_ids).union(leaving_ids):
model.update_cached_properties(uid)
def to_client_dict(self):
d = super(Team, self).to_client_dict()
# Decorate the team with counts of related objects; cached.
d.update(self.default_cached_properties)
cached = memcache.get(util.cached_properties_key(self.uid))
if cached:
d.update(cached)
else:
d.update(Team.update_cached_properties(self.uid))
return d