def raw_insert_responses(db_conn, responses): for response in responses: query = """ INSERT INTO responses (id, created, modified, user_id, card_id, unit_id, response, score, learned) VALUES (%(id)s, %(created)s, %(modified)s, %(user_id)s, %(card_id)s, %(unit_id)s, %(response)s, %(score)s, %(learned)s) RETURNING *; """ params = { 'id': response.get('id', uuid.uuid4()), 'created': response.get('created', datetime.utcnow()), 'modified': response.get('modified', datetime.utcnow()), 'user_id': convert_slug_to_uuid(response.get('user_id')), 'card_id': convert_slug_to_uuid(response.get('card_id')), 'unit_id': convert_slug_to_uuid(response.get('unit_id')), 'response': convert_uuid_to_slug(response.get('response')), 'score': response.get('score'), 'learned': response.get('learned'), } save_row(db_conn, query, params)
def insert_vote(db_conn, data): """ Create a new vote. """ schema = vote_schema query = """ INSERT INTO posts ( user_id , topic_id , kind , body , replies_to_id , response ) VALUES (%(user_id)s, %(topic_id)s, %(kind)s, %(body)s, %(replies_to_id)s, %(response)s) RETURNING *; """ data = { 'user_id': convert_slug_to_uuid(data['user_id']), 'topic_id': convert_slug_to_uuid(data['topic_id']), 'kind': 'vote', 'body': data.get('body'), 'replies_to_id': data.get('replies_to_id'), 'response': data['response'], } errors = is_valid_reply(db_conn, data) if errors: return None, errors errors = is_valid_reply_kind(db_conn, data) if errors: return None, errors data, errors = insert_row(db_conn, schema, query, data) if not errors: add_post_to_es(db_conn, data) return data, errors
def insert_proposal(db_conn, data): """ Create a new proposal. """ schema = proposal_schema query = """ INSERT INTO posts ( user_id , topic_id , kind , body , replies_to_id , entity_versions ) VALUES (%(user_id)s, %(topic_id)s, %(kind)s, %(body)s, %(replies_to_id)s, %(entity_versions)s) RETURNING *; """ data = { 'user_id': convert_slug_to_uuid(data['user_id']), 'topic_id': convert_slug_to_uuid(data['topic_id']), 'kind': 'proposal', 'body': data.get('body'), 'replies_to_id': data.get('replies_to_id'), 'entity_versions': data['entity_versions'], } errors = is_valid_reply(db_conn, data) if errors: return None, errors errors = validate_entity_versions(db_conn, data) if errors: return None, errors data, errors = insert_row(db_conn, schema, query, data) if not errors: add_post_to_es(db_conn, data) return data, errors
def remove_subject_route(request, user_id, subject_id): """ Remove a subject from the learner's list of subjects. """ db_conn = request['db_conn'] current_user = get_current_user(request) if not current_user: return abort(401, 'DHlX2XTsTO-1Hhfr9GXkJw') if user_id != current_user['id']: return abort(403, '8yt2d8K1RNKidGIVU1CaOA') user_subjects = list_user_subjects(db_conn, user_id) if not user_subjects: return 404, { 'errors': [{ 'name': 'user_id', 'message': 'User does not have subjects.', 'ref': 'nttevgwMRsOwiT_ul0SmHQ', }], } matches = [ us for us in user_subjects if (convert_slug_to_uuid(us['subject_id']) == convert_slug_to_uuid(subject_id)) ] if not matches: return abort(404, 'AQV0c9qfSdO7Ql2IC8l0bw') errors = remove_user_subject(db_conn, user_id, subject_id) if errors: return 400, { 'errors': errors, 'ref': 'h1BKySSTT0SgH2OTTnSVlA' } return 200, {}
def test_get_row(db_conn, session): query = """ SELECT * FROM users WHERE id = %(id)s LIMIT 1; """ params = { 'id': convert_slug_to_uuid(user_id), } result = get_row(db_conn, query, params) assert result['id'] == convert_slug_to_uuid(user_id)
def remove_user_subject(db_conn, user_id, subject_id): """ Remove a subject from a user's list of subjects. """ query = """ DELETE FROM users_subjects WHERE user_id = %(user_id)s AND subject_id = %(subject_id)s; """ params = { 'user_id': convert_slug_to_uuid(user_id), 'subject_id': convert_slug_to_uuid(subject_id), } errors = delete_row(db_conn, query, params) return errors
def insert_subject(db_conn, data): """ Create a new version of a new a subject, saving to ES. """ schema = subject_schema query = """ INSERT INTO subjects_entity_id (entity_id) VALUES (%(entity_id)s); INSERT INTO subjects ( entity_id , name , user_id , body , members ) VALUES (%(entity_id)s, %(name)s, %(user_id)s, %(body)s, %(members)s) RETURNING *; """ data = { 'entity_id': uuid.uuid4(), 'name': data['name'], 'user_id': convert_slug_to_uuid(data['user_id']), 'body': data['body'], 'members': data.get('members', []), } errors = is_valid_members(db_conn, data) + ensure_no_cycles(db_conn, data) if errors: return None, errors data, errors = insert_row(db_conn, schema, query, data) if not errors: save_entity_to_es('subject', deliver_subject(data, access='view')) return data, errors
def get_follow(db_conn, user_id, entity_id): """ Find a specific follow (entity <-> user). """ query = """ SELECT * FROM follows WHERE user_id = %(user_id)s AND entity_id = %(entity_id)s LIMIT 1; """ params = { 'user_id': convert_slug_to_uuid(user_id), 'entity_id': convert_slug_to_uuid(entity_id), } return get_row(db_conn, query, params)
def raw_insert_units(db_conn, units): for unit in units: query = """ INSERT INTO units_entity_id (entity_id) VALUES (%(entity_id)s) ON CONFLICT DO NOTHING; INSERT INTO units ( version_id , created , modified , entity_id , previous_id , language , status , available , tags , name , user_id , body , require_ids ) VALUES (%(version_id)s, %(created)s, %(modified)s, %(entity_id)s, %(previous_id)s, %(language)s, %(status)s, %(available)s, %(tags)s, %(name)s, %(user_id)s, %(body)s, %(require_ids)s) RETURNING *; """ params = { 'version_id': unit.get('version_id', uuid.uuid4()), 'created': unit.get('created', datetime.utcnow()), 'modified': unit.get('modified', datetime.utcnow()), 'entity_id': unit.get('entity_id'), 'previous_id': unit.get('previous_id'), 'language': unit.get('language', 'en'), 'status': unit.get('status', 'accepted'), 'available': unit.get('available', True), 'tags': unit.get('tags', []), 'name': unit.get('name'), 'user_id': unit.get('user_id', convert_slug_to_uuid(user_id)), 'body': unit.get('body'), 'require_ids': unit.get('require_ids', []), } save_row(db_conn, query, params)
def insert_subject_version(db_conn, current_data, next_data): """ Create a new version of an existing subject. """ schema = subject_schema query = """ INSERT INTO subjects ( entity_id , previous_id , name , user_id , body , members ) VALUES (%(entity_id)s, %(previous_id)s, %(name)s, %(user_id)s, %(body)s, %(members)s) RETURNING *; """ data = { 'entity_id': current_data['entity_id'], 'previous_id': current_data['version_id'], 'user_id': convert_slug_to_uuid(next_data['user_id']), 'name': next_data.get('name') or current_data.get('name'), 'body': next_data.get('body') or current_data.get('body'), 'members': (next_data.get('members') or current_data.get('members') or []), } errors = is_valid_members(db_conn, data) + ensure_no_cycles(db_conn, data) if errors: return None, errors data, errors = insert_row(db_conn, schema, query, data) if not errors: save_entity_to_es('subject', deliver_subject(data, access='view')) return data, errors
def create_user_in_db(db_conn): from raw_insert import raw_insert_users raw_insert_users(db_conn, [{ 'id': convert_slug_to_uuid(user_id), 'name': 'test', 'email': '*****@*****.**', 'password': '******', }])
def get_latest_response(db_conn, user_id, unit_id): """ Get the latest response given a user ID and a unit ID. """ query = """ SELECT * FROM responses WHERE user_id = %(user_id)s AND unit_id = %(unit_id)s ORDER BY created DESC LIMIT 1; """ params = { 'user_id': convert_slug_to_uuid(user_id), 'unit_id': convert_slug_to_uuid(unit_id), } return get_row(db_conn, query, params)
def raw_insert_cards(db_conn, cards): for card in cards: query = """ INSERT INTO cards_entity_id (entity_id) VALUES (%(entity_id)s) ON CONFLICT DO NOTHING; INSERT INTO cards ( version_id , created , modified , entity_id , previous_id , language , status , available , tags , name , user_id , unit_id , require_ids , kind , data ) VALUES (%(version_id)s, %(created)s, %(modified)s, %(entity_id)s, %(previous_id)s, %(language)s, %(status)s, %(available)s, %(tags)s, %(name)s, %(user_id)s, %(unit_id)s, %(require_ids)s, %(kind)s, %(data)s) RETURNING *; """ params = { 'version_id': card.get('version_id', uuid.uuid4()), 'created': card.get('created', datetime.utcnow()), 'modified': card.get('modified', datetime.utcnow()), 'entity_id': card.get('entity_id'), 'previous_id': card.get('previous_id'), 'language': card.get('language', 'en'), 'status': card.get('status', 'accepted'), 'available': card.get('available', True), 'tags': card.get('tags', []), 'name': card.get('name'), 'user_id': card.get('user_id', convert_slug_to_uuid(user_id)), 'unit_id': card.get('unit_id'), 'require_ids': card.get('require_ids', []), 'kind': card.get('kind'), 'data': psycopg2.extras.Json(card.get('data', {})), } save_row(db_conn, query, params) if card.get('kind') == 'choice': query = """ INSERT INTO cards_parameters ( entity_id , guess_distribution , slip_distribution ) VALUES (%(entity_id)s, %(guess_distribution)s, %(slip_distribution)s) RETURNING *; """ params = { 'entity_id': card.get('entity_id'), 'guess_distribution': psycopg2.extras.Json({ str(h): 1 - (0.5 - h) ** 2 for h in [h / precision for h in range(1, precision)] }), 'slip_distribution': psycopg2.extras.Json({ str(h): 1 - (0.25 - h) ** 2 for h in [h / precision for h in range(1, precision)] }), } save_row(db_conn, query, params)
def test_list_rows(db_conn, session): query = """ SELECT id FROM users ORDER BY created DESC; /* TODO OFFSET LIMIT */ """ params = {} result = list_rows(db_conn, query, params) assert result[0]['id'] == convert_slug_to_uuid(user_id)
def insert_user_subject(db_conn, user_id, subject_id): """ Add a new user subjects entry to the database. """ schema = user_subjects_schema query = """ INSERT INTO users_subjects ( user_id , subject_id ) VALUES (%(user_id)s, %(subject_id)s) RETURNING *; """ data = { 'user_id': convert_slug_to_uuid(user_id), 'subject_id': convert_slug_to_uuid(subject_id), } data, errors = insert_row(db_conn, schema, query, data) return data, errors
def insert_unit_version(db_conn, current_data, next_data): """ Create a new version of a existing unit, saving to ES. """ schema = unit_schema query = """ INSERT INTO units ( entity_id , previous_id , name , user_id , body , require_ids ) VALUES (%(entity_id)s, %(previous_id)s, %(name)s, %(user_id)s, %(body)s, %(require_ids)s) RETURNING *; """ data = { 'entity_id': current_data['entity_id'], 'previous_id': current_data['version_id'], 'user_id': convert_slug_to_uuid(next_data['user_id']), 'name': next_data.get('name') or current_data.get('name'), 'body': next_data.get('body') or current_data.get('body'), 'require_ids': [ convert_slug_to_uuid(require_id) for require_id in next_data.get('require_ids') or current_data.get('require_ids') or [] ], } errors = ensure_requires(db_conn, data) + ensure_no_cycles(db_conn, data) if errors: return None, errors data, errors = insert_row(db_conn, schema, query, data) if not errors: save_entity_to_es('unit', deliver_unit(data, access='view')) return data, errors
def delete_follow(db_conn, id_): """ Remove a follow from the database. """ query = """ DELETE FROM follows WHERE id = %(id)s; """ params = { 'id': convert_slug_to_uuid(id_), } return delete_row(db_conn, query, params)
def _(members): entity_ids = [ convert_slug_to_uuid(member['id']) for member in members if member['kind'] == 'subject' ] entities = list_latest_accepted_subjects(db_conn, entity_ids) for entity in entities: if entity['entity_id'] in seen: found['cycle'] = True break seen.add(entity['entity_id']) _(entity['members'])
def insert_follow(db_conn, data): """ Create a new follow (user <-> entity). """ already_has = get_follow(db_conn, convert_slug_to_uuid(data.get('user_id')), convert_slug_to_uuid(data.get('entity_id'))) if already_has: return {}, [{ 'name': 'entity_id', 'message': 'You already followed this entity.', 'ref': 'EqvXy6p0TneEnB1qD__fdg', }] schema = follow_schema query = """ INSERT INTO follows ( user_id , entity_id , entity_kind ) VALUES (%(user_id)s, %(entity_id)s, %(entity_kind)s) RETURNING *; """ data = pick(data, ('user_id', 'entity_id', 'entity_kind')) data = { 'user_id': convert_slug_to_uuid(data.get('user_id')), 'entity_id': convert_slug_to_uuid(data.get('entity_id')), 'entity_kind': data.get('entity_kind'), } errors = is_valid_entity(db_conn, data) if errors: return None, [{ 'name': 'entity_id', 'message': 'invalid entity', 'ref': 'ph-XrElITuyixCzqu_OTTA', }] data, errors = insert_row(db_conn, schema, query, data) return data, errors
def insert_unit(db_conn, data): """ Create a new version of a new unit, saving to ES. """ schema = unit_schema query = """ INSERT INTO units_entity_id (entity_id) VALUES (%(entity_id)s); INSERT INTO units ( entity_id , name , user_id , body , require_ids ) VALUES (%(entity_id)s , %(name)s, %(user_id)s, %(body)s, %(require_ids)s) RETURNING *; """ data = { 'entity_id': uuid.uuid4(), 'name': data['name'], 'user_id': convert_slug_to_uuid(data['user_id']), 'body': data['body'], 'require_ids': [ convert_slug_to_uuid(require_id) for require_id in data.get('require_ids', []) ], } errors = ensure_requires(db_conn, data) + ensure_no_cycles(db_conn, data) if errors: return None, errors data, errors = insert_row(db_conn, schema, query, data) if not errors: save_entity_to_es('unit', deliver_unit(data, access='view')) return data, errors
def get_card_parameters(db_conn, params): """ Get Card Parameters """ query = """ SELECT * FROM cards_parameters WHERE entity_id = %(entity_id)s LIMIT 1; """ params = { 'entity_id': convert_slug_to_uuid(params['entity_id']), } return get_row(db_conn, query, params)
def get_subject_version(db_conn, version_id): """ Get a subject version. """ query = """ SELECT * FROM subjects WHERE version_id = %(version_id)s ORDER BY created DESC; /* TODO LIMIT OFFSET */ """ params = {'version_id': convert_slug_to_uuid(version_id)} return get_row(db_conn, query, params)
def list_one_subject_versions(db_conn, entity_id): """ List Subjects Versions by EID """ query = """ SELECT * FROM subjects WHERE entity_id = %(entity_id)s ORDER BY created DESC; /* TODO LIMIT OFFSET */ """ params = {'entity_id': convert_slug_to_uuid(entity_id)} return list_rows(db_conn, query, params)
def list_user_subjects(db_conn, user_id): """ List the user subjects for a user from the database. """ query = """ SELECT * FROM users_subjects WHERE user_id = %(user_id)s ORDER BY created DESC; /* TODO OFFSET LIMIT */ """ params = {'user_id': convert_slug_to_uuid(user_id)} return list_rows(db_conn, query, params)
def get_user_by_id(db_conn, params): """ Get the user by ID. """ query = """ SELECT * FROM users WHERE id = %(id)s LIMIT 1; """ params = { 'id': convert_slug_to_uuid(params['id']), } return get_row(db_conn, query, params)
def test_get_current_user(db_conn): """ Expect to get the current user given session info. """ create_user_in_db(db_conn) token = log_in() user = get_current_user({ 'cookies': { 'session_id': token }, 'db_conn': db_conn, }) assert user assert user['id'] == convert_slug_to_uuid(user_id)
def does_subject_exist(db_conn, entity_id): """ Just... is this a valid subject entity_id. """ query = """ SELECT entity_id FROM subjects_entity_id WHERE entity_id = %(entity_id)s LIMIT 1; """ params = { 'entity_id': convert_slug_to_uuid(entity_id), } return get_row(db_conn, query, params)
def get_notice(db_conn, params): """ Get the user matching the parameters. """ query = """ SELECT * FROM notices WHERE id = %(id)s LIMIT 1; """ params = { 'id': convert_slug_to_uuid(params['id']), } return get_row(db_conn, query, params)
def get_follow_by_id(db_conn, follow_id): """ Find a specific follow (entity <-> user). """ query = """ SELECT * FROM follows WHERE id = %(id)s LIMIT 1; """ params = { 'id': convert_slug_to_uuid(follow_id), } return get_row(db_conn, query, params)
def get_latest_accepted_subject(db_conn, entity_id): """ Get Latest Accepted Subject Version by EID """ query = """ SELECT DISTINCT ON (entity_id) * FROM subjects WHERE status = 'accepted' AND entity_id = %(entity_id)s ORDER BY entity_id, created DESC; /* TODO LIMIT */ """ params = { 'entity_id': convert_slug_to_uuid(entity_id), } return get_row(db_conn, query, params)