def test_put_for_index_update(self): """Forcing an overwite into a unique index changes timestamps and coerces as expected.""" # Initial save. We'll overwite this user's email and expect the uid to # remain the same. Don't do it the normal way so that we can # intentionally put a modified time in the past (the other option is to # make the tests slow enough for the second-resolution timestamps to # differ). user = User.create(email='foo') user.created = str_to_dt('1970-01-01T00:00:00Z') user.modified = user.created row_dict = user.coerce_row_dict(user.to_dict()) with mysql_connection.connect() as sql: sql.insert_or_update(User.table, row_dict) # UPDATE should advance modified timestamps, but not created. duplicate_user = User.create(email='foo') # new uid, should be ignored on put duplicate_user.created = str_to_dt('1982-01-01T00:00:00Z') # Make sure boolean and json attributes coerced correctly. duplicate_user.receive_sms = False duplicate_user.owned_teams = ['Team_X'] fetched_user = User.put_for_index(duplicate_user, 'email') self.assertEqual(user.created, fetched_user.created) self.assertGreater(fetched_user.modified, user.modified) self.assertEqual(fetched_user.receive_sms, False) self.assertEqual(fetched_user.owned_teams, ['Team_X']) # uid of duplicate user is gone self.assertEqual(fetched_user.uid, user.uid)
def set_up(self): # Let ConsistencyTestCase set up the datastore testing stub. super(TestApiNetworks, self).set_up() application = webapp2.WSGIApplication(api_routes, config={ 'webapp2_extras.sessions': { 'secret_key': self.cookie_key } }, debug=True) self.testapp = webtest.TestApp(application) with mysql_connection.connect() as sql: sql.reset({ 'network': Network.get_table_definition(), 'organization': Organization.get_table_definition(), 'program': Program.get_table_definition(), 'user': User.get_table_definition(), }) self.program = Program.create( name="Engagement Project", label='ep18', min_cycles=3, active=True, preview_url='foo.com', ) self.program.put()
def set_up(self): # Let ConsistencyTestCase set up the datastore testing stub. super(TestApiParticipants, self).set_up() application = self.patch_webapp(webapp2.WSGIApplication)( api_routes, config={ 'webapp2_extras.sessions': { 'secret_key': self.cookie_key } }, debug=True) self.testapp = webtest.TestApp(application) with mysql_connection.connect() as sql: sql.reset({ 'classroom': Classroom.get_table_definition(), 'cycle': Cycle.get_table_definition(), 'participant': Participant.get_table_definition(), 'program': Program.get_table_definition(), 'team': Team.get_table_definition(), 'user': User.get_table_definition(), }) self.program = Program.create( name="Engagement Project", label='ep18', min_cycles=3, active=True, preview_url='foo.com', ) self.program.put()
def test_task_creates_backup_for_user(self): user_response_params = dict( self.response_params, type=Response.USER_LEVEL_SYMBOL, private=True, user_id='User_foo', ) r = Response.create(**user_response_params) r.put() payload = json.dumps( r.to_dict(), default=util.json_dumps_default, ) self.testapp.post( '/task/backup_response', payload, headers={'Content-Type': 'application/json'}, ) with mysql_connection.connect() as sql: rows = sql.select_star_where(ResponseBackup.table) backup = rows[0] backup['body'] = json.loads(backup['body']) expected = dict(r.to_dict(), backup_id=1), self.assertEqual(len(rows), 1) self.assertEqual( rows[0], dict(r.to_dict(), backup_id=1), )
def post(self): content_types = self.request.headers.get('Content-Type', []) if 'application/json' not in content_types: raise Exception("/task/backup_response requires JSON content type") # This results from a known sane internal call, so we can skip most of # the layers we normally use (get_params(), creating objects). First, a # dictionary of python-friendly values: response_params = json.loads(self.request.body) # Then convert values to their MySQL-friendly versions. row_dict = ResponseBackup.coerce_row_dict(response_params) # The data provided should come from Response.after_put(), so the # created and modified times should be correct, they'll need converting # to sql format. for key in ('created', 'modified'): row_dict[key] = util.iso_datetime_to_sql(row_dict[key]) # Then just insert, with no choice to update anything that might look # similar. with mysql_connection.connect() as sql: affected_rows = sql.insert_row_dicts(ResponseBackup.table, [row_dict]) # There's no unique index or primary key on this table, so we should # _always_ get a new row. if affected_rows != 1: raise Exception("BackupResponses failed to insert a new row.")
def get_by_participant(klass, participant_id, project_cohort_id=None): """Get whitelisted pd for a participant, optionally scoped by pc.""" pc_clause = 'AND `project_cohort_id` = %s' if project_cohort_id else '' # Only certain types of participant data are readable by anyone without # direct access to the database. This keeps participant responses # secure. query = """ SELECT * FROM `participant_data` WHERE `key` IN ('progress', 'link', 'condition', 'ep_assent', 'last_login', 'saw_baseline', 'saw_demographics', 'saw_validation') AND `participant_id` = %s {pc_clause} """.format(pc_clause=pc_clause) params = [participant_id] if project_cohort_id: params.append(project_cohort_id) 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]
def test_update_one(self): """Updating an object with .put() changes timestamps and coerces as expected.""" # Initial save. Don't do it the normal way so that we can intentionally # put a modified time in the past (the other option is to make the # tests slow enough for the second-resolution timestamps to differ). user = User.create(email='foo') user.created = str_to_dt('1970-01-01T00:00:00Z') user.modified = user.created row_dict = user.coerce_row_dict(user.to_dict()) with mysql_connection.connect() as sql: sql.insert_or_update(User.table, row_dict) # Check that timestamps saved correctly. fetched_user = User.get_by_id(user.uid) self.assertEqual(user.created, fetched_user.created) self.assertEqual(user.modified, fetched_user.modified) # UPDATE should advance modified timestamps, but not created. user.name = 'newly named'; user.created = str_to_dt('1982-01-01T00:00:00Z') # Make sure boolean and json attributes coerced correctly. user.receive_sms = False user.owned_teams = ['Team_X'] user.put() updated_user = user self.assertEqual(updated_user.created, fetched_user.created) self.assertGreater(updated_user.modified, fetched_user.modified) self.assertEqual(updated_user.receive_sms, False) self.assertEqual(updated_user.owned_teams, ['Team_X'])
def set_up(self): super(TestParticipants, self).set_up() with mysql_connection.connect() as sql: sql.reset({ 'participant': Participant.get_table_definition(), })
def set_up(self): # Let ConsistencyTestCase set up the datastore testing stub. super(TestApiParticipation, self).set_up() application = webapp2.WSGIApplication(api_routes, config={ 'webapp2_extras.sessions': { 'secret_key': self.cookie_key } }, debug=True) self.testapp = webtest.TestApp(application) # Successful download of completion ids triggers a notification, which # requires a cohort name. Program.mock_program_config( self.program_label, {'cohorts': { self.cohort_label: { 'name': self.cohort_label } }}, ) with mysql_connection.connect() as sql: sql.reset({ 'participant': Participant.get_table_definition(), 'participant_data': ParticipantData.get_table_definition(), })
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.")
def completion_ids_anonymous(klass, project_cohort_id, start, end): """Get list of anonymous participant ids having entered the survey, whether or not they finished it. Args: project_cohort_id: str applicable pc start: datetime filters for pd modified after this date end: datetime filters for pd modified before this date Returns: list of rows with 'participant_id', 'survey_ordinal', and 'value' (which is progress). """ # Optionally add a time range. Note that SQL stores and compares # datetime strings in a different format. query = """ SELECT `participant_id` , `survey_ordinal` , `value` FROM `participant_data` pd WHERE `key` = 'progress' AND `testing` = 0 AND `project_cohort_id` = %s AND `modified` >= %s AND `modified` < %s ORDER BY `survey_ordinal`, `value`, `participant_id` """ query_params = ( project_cohort_id, start.strftime(config.sql_datetime_format), end.strftime(config.sql_datetime_format), ) with mysql_connection.connect() as sql: result = sql.select_query(query, tuple(query_params)) return result
def test_get_day(self): """Get all user's notifications for the last 24 hours.""" user = User.create(name='superdude', email='*****@*****.**') old = self.test_create(user=user) old.created = datetime.datetime(2017, 1, 1) # Simulate a put, but without stripping timestamps at this step, which # SqlModel.put() would normally do. This lets us post-date the # notification. row_dict = Notification.coerce_row_dict(old.to_dict()) with mysql_connection.connect() as sql: sql.insert_or_update(Notification.table, row_dict) # This one gets CURRENT_TIMESTAMP, as normal. new = self.test_create(user=user) new.put() # Only the new one should be returned. start = datetime.datetime.now() - datetime.timedelta(hours=12) end = datetime.datetime.now() + datetime.timedelta(hours=12) notes = Notification.get_period_for_user( user, util.datelike_to_iso_string(start), util.datelike_to_iso_string(end), ) self.assertEquals(len(notes), 1)
def set_up(self): # Let ConsistencyTestCase set up the datastore testing stub. super(TestApiSurveys, self).set_up() application = webapp2.WSGIApplication(api_routes, config={ 'webapp2_extras.sessions': { 'secret_key': self.cookie_key } }, debug=True) self.testapp = webtest.TestApp(application) with mysql_connection.connect() as sql: sql.reset({ 'classroom': Classroom.get_table_definition(), 'metric': Metric.get_table_definition(), 'program': Program.get_table_definition(), 'survey': Survey.get_table_definition(), 'team': Team.get_table_definition(), 'user': User.get_table_definition(), }) self.ep_program = Program.create( name="Engagement Project", label='ep18', active=True, preview_url='foo.com', ) self.ep_program.put()
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
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]
def registrar_evento(self, rfid_user): self.id_user = rfid_user self.date = datetime.date.today() self.time = datetime.datetime.now().strftime("%H:%M:%S") self.msqlConn = msqlConn.connect() try: self.msqlConn.execute("""SELECT * FROM events WHERE iduser='******' AND date='{}' ORDER BY idevents DESC LIMIT 1""" .format(self.id_user, self.date)) row = msqlConn.fetchall() if not row or row[0][4] == 2: self.msqlConn.execute(""" INSERT INTO events VALUES ('', '{}', '{}', '{}', '{}') """ .format(self.id_user, self.date, self.time, 1)) msqlConn.commit() else: self.msqlConn.execute(""" INSERT INTO events VALUES ('', '{}', '{}', '{}', '{}') """ .format(self.id_user, self.date, self.time, 2)) msqlConn.commit() msqlConn.close() #1 = entrada #2 = saida except mysql.connector.Error as err: print (err)
def set_up(self): """Clear relevant tables from testing SQL database.""" # Let ConsistencyTestCase set up the datastore testing stub. super(TestGraphQLProjectCohort, self).set_up() with mysql_connection.connect() as sql: sql.reset({ 'checkpoint': Checkpoint.get_table_definition(), }) Program.mock_program_config( self.program_label, { 'name': self.program_name, 'default_portal_type': 'name_or_id', 'description': self.program_description, 'cohorts': { self.cohort_label: self.cohort } }) application = webapp2.WSGIApplication(api_routes, config={ 'webapp2_extras.sessions': { 'secret_key': self.cookie_key } }, debug=True) self.testapp = webtest.TestApp(application)
def set_up(self): # Let ConsistencyTestCase set up the datastore testing stub. super(TestModel, self).set_up() with mysql_connection.connect() as sql: sql.reset({ 'user': User.get_table_definition(), })
def set_up(self): """Clear relevant tables from testing SQL database.""" # Let ConsistencyTestCase set up the datastore testing stub. super(TestParticipantData, self).set_up() with mysql_connection.connect() as sql: sql.reset({ 'participant_data': ParticipantData.get_table_definition(), })
def set_up(self): # Let ConsistencyTestCase set up the datastore testing stub. super(TestNotifications, self).set_up() with mysql_connection.connect() as sql: sql.reset({ 'digest': Digest.get_table_definition(), 'notification': Notification.get_table_definition(), 'user': User.get_table_definition(), })
def set_up(self): # Let ConsistencyTestCase set up the datastore testing stub. super(TestSurveys, self).set_up() with mysql_connection.connect() as sql: sql.reset({ 'cycle': Cycle.get_table_definition(), 'metric': Metric.get_table_definition(), 'program': Program.get_table_definition(), 'survey': Survey.get_table_definition(), 'team': Team.get_table_definition(), })
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
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]
def set_up(self): # Let ConsistencyTestCase set up the datastore testing stub. super(TestCycles, self).set_up() with mysql_connection.connect() as sql: sql.reset({ 'classroom': Classroom.get_table_definition(), 'cycle': Cycle.get_table_definition(), 'program': Program.get_table_definition(), 'team': Team.get_table_definition(), 'user': User.get_table_definition(), })
def completion_ids(klass, **kwargs): """Get list of identifiable names/ids having entered the survey, whether or not they finished it. Args: project_cohort_id: str applicable pc survey_id: str applicable survey start: datetime filters for pd modified after this date end: datetime filters for pd modified before this date Returns: List of dictionaries, each with keys 'participant_id' and 'survey_ordinal'. """ # All kwargs default to None. kwargs = collections.defaultdict(lambda: None, kwargs) # Determine and check for a single scope. scope_keys = ('project_cohort_id', 'survey_id') scope_filters = {k: kwargs[k] for k in scope_keys if kwargs[k]} if len(scope_filters) != 1: raise Exception("Invalid scope filters: {}".format(scope_filters)) query_params = scope_filters.values() # Optionally add a time range. Note that SQL stores and compares # datetime strings in a different format. query_params += [ kwargs[k].strftime(config.sql_datetime_format) for k in ('start', 'end') if kwargs[k] ] query = """ SELECT p.`name` as token , pd.`value` as percent_progress , pd.`survey_ordinal` as module FROM `participant_data` pd JOIN `participant` p ON pd.`participant_id` = p.`uid` WHERE pd.`key` = 'progress' AND pd.`testing` = 0 AND pd.`{scope_key}` = %s {start} {end} ORDER BY module, percent_progress * 1, token """.format( scope_key=scope_filters.keys()[0], start='AND pd.`modified` > %s' if kwargs['start'] else '', end='AND pd.`modified` < %s' if kwargs['end'] else '', ) with mysql_connection.connect() as sql: result = sql.select_query(query, tuple(query_params)) return result
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]
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]
def participation_by_project_cohort_from_sql(klass, ids_or_codes, using_codes=False, start=None, end=None): """Like participation() but for many project cohorts at once. Args: ids_or_codes: list applicable pc ids or pc codes using_codes: bool default False, True if first argument is codes start: datetime optional, filters for pd modified after this date end: datetime optional, filters for pd modified before this date Returns: List of dictionaries, each representing a distinct value of progress (e.g. 33, 100) and associated counts. """ if len(ids_or_codes) == 0: return [] query = """ SELECT `project_cohort_id` , MAX(`code`) as code , `value` , `survey_ordinal` , COUNT(`uid`) as n FROM `participant_data` WHERE `key` = 'progress' AND `testing` = 0 AND `{match_field}` IN({interps}) {start} {end} GROUP BY `project_cohort_id`, `survey_ordinal`, `value` # Cast value to number before ordering, otherwise string sorting # gives you things like: 1, 100, 33. # http://stackoverflow.com/questions/5417381/mysql-sort-string-number ORDER BY `project_cohort_id`, `survey_ordinal`, `value` * 1 """.format( match_field='code' if using_codes else 'project_cohort_id', interps=','.join(['%s'] * len(ids_or_codes)), start='AND `modified` >= %s' if start else '', end='AND `modified` < %s' if end else '', ) query_params = list(ids_or_codes) if start: query_params.append(start.strftime(config.sql_datetime_format)) if end: query_params.append(end.strftime(config.sql_datetime_format)) with mysql_connection.connect() as sql: result = sql.select_query(query, tuple(query_params)) return result
def registrar_usuario(self, nome, rg, cpf, hora_entrada, hora_saida, rfid): self.msqlConn = msqlConn.connect() try: self.msqlConn.execute(""" INSERT INTO users (nome,cpf,rg,entrada,saida,rfid) VALUES ('{}','{}','{}','{}','{}','{}') """ .format(nome, rg, cpf, hora_entrada, hora_saida, rfid)) msqlConn.commit() self.msqlConn.close() except mysql.connector.Error as err: print (err)
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]