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)
