def raw_insert_posts(db_conn, posts):
for post in posts:
query = """
INSERT INTO posts
(id, created, modified,
kind, body, replies_to_id,
entity_versions, response, user_id,
topic_id)
VALUES
(%(id)s, %(created)s, %(modified)s,
%(kind)s, %(body)s, %(replies_to_id)s,
%(entity_versions)s, %(response)s, %(user_id)s,
%(topic_id)s)
RETURNING *;
"""
params = {
'id': post.get('id', uuid.uuid4()),
'created': post.get('created', datetime.utcnow()),
'modified': post.get('modified', datetime.utcnow()),
'kind': post.get('kind'),
'body': post.get('body'),
'replies_to_id': post.get('replies_to_id'),
'entity_versions': psycopg2.extras.Json(
post.get('entity_versions', [])
),
'response': post.get('response'),
'user_id': post.get('user_id'),
'topic_id': post.get('topic_id'),
}
save_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 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 raw_insert_users(db_conn, users):
for user in users:
query = """
INSERT INTO users
( id , created , modified ,
name , email , password , settings)
VALUES
(%(id)s, %(created)s, %(modified)s,
%(name)s, %(email)s, %(password)s, %(settings)s)
RETURNING *;
"""
params = {
'id': user.get('id', uuid.uuid4()),
'created': user.get('created', datetime.utcnow()),
'modified': user.get('modified', datetime.utcnow()),
'name': user.get('name'),
'email': user.get('email'),
# NOTE do not set rounds this low in production!
'password': bcrypt.encrypt(user.get('password'), rounds=4),
'settings': psycopg2.extras.Json(user.get('settings', {
'email_frequency': 'daily',
'view_subjects': 'public',
'view_follows': 'public',
})),
}
save_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 raw_insert_user_subjects(db_conn, user_subjects):
for user_subject in user_subjects:
query = """
INSERT INTO users_subjects
(id, created, modified,
user_id, subject_id)
VALUES
(%(id)s, %(created)s, %(modified)s,
%(user_id)s, %(subject_id)s)
RETURNING *;
"""
params = {
'id': user_subject.get('id', uuid.uuid4()),
'created': user_subject.get('created', datetime.utcnow()),
'modified': user_subject.get('modified', datetime.utcnow()),
'user_id': user_subject.get('user_id'),
'subject_id': user_subject.get('subject_id'),
}
save_row(db_conn, query, params)
def raw_insert_follows(db_conn, follows):
for follow in follows:
query = """
INSERT INTO follows
(id, created, modified,
user_id, entity_id, entity_kind)
VALUES
(%(id)s, %(created)s, %(modified)s,
%(user_id)s, %(entity_id)s, %(entity_kind)s)
RETURNING *;
"""
params = {
'id': follow.get('id', uuid.uuid4()),
'created': follow.get('created', datetime.utcnow()),
'modified': follow.get('modified', datetime.utcnow()),
'user_id': follow.get('user_id'),
'entity_id': follow.get('entity_id'),
'entity_kind': follow.get('entity_kind'),
}
save_row(db_conn, query, params)
def raw_insert_topics(db_conn, topics):
for topic in topics:
query = """
INSERT INTO topics
( id, created, modified,
user_id, name, entity_id, entity_kind)
VALUES
(%(id)s, %(created)s, %(modified)s,
%(user_id)s, %(name)s, %(entity_id)s, %(entity_kind)s)
RETURNING *;
"""
params = {
'id': topic.get('id', uuid.uuid4()),
'created': topic.get('created', datetime.utcnow()),
'modified': topic.get('modified', datetime.utcnow()),
'user_id': topic.get('user_id'),
'name': topic.get('name'),
'entity_id': topic.get('entity_id'),
'entity_kind': topic.get('entity_kind'),
}
save_row(db_conn, query, params)
def raw_insert_notices(db_conn, notices):
for notice in notices:
query = """
INSERT INTO notices
(id, created, modified,
user_id, kind, data, read, tags)
VALUES
(%(id)s, %(created)s, %(modified)s,
%(user_id)s, %(kind)s, %(data)s, %(read)s, %(tags)s)
RETURNING *;
"""
params = {
'id': notice.get('id', uuid.uuid4()),
'created': notice.get('created', datetime.utcnow()),
'modified': notice.get('modified', datetime.utcnow()),
'user_id': notice.get('user_id'),
'kind': notice.get('kind'),
'data': psycopg2.extras.Json(
notice.get('data', {})
),
'read': notice.get('read', False),
'tags': notice.get('tags', []),
}
save_row(db_conn, query, params)
def mark_notice_as_read(db_conn, notice):
"""
Marks the notice as read.
"""
query = """
UPDATE notices
SET read = TRUE
WHERE id = %(id)s
RETURNING *;
"""
data = {
'id': convert_slug_to_uuid(notice['id']),
}
# Skipping validation here... there's only one field changing.
data, errors = save_row(db_conn, query, data)
return data, errors
def update_subject(db_conn, version_id, status):
"""
Update a subject version's status and available. [hidden]
"""
query = """
UPDATE subjects
SET status = %(status)s
WHERE version_id = %(version_id)s
RETURNING *;
"""
data = {
'version_id': convert_slug_to_uuid(version_id),
'status': status,
}
data, errors = save_row(db_conn, query, data)
if not errors:
save_entity_to_es('subject', deliver_subject(data, access='view'))
return data, errors
def test_save_row_error(db_conn):
query = """
INSERT INTO users
( id , created , modified ,
name , email , password , settings)
VALUES
(%(id)s, %(created)s, %(modified)s,
%(name)s, %(email)s, %(password)s, %(settings)s)
RETURNING *;
"""
params = {
'id': uuid.uuid4(),
'created': datetime.utcnow(),
'modified': datetime.utcnow(),
'name': 'Hi',
'email': '*****@*****.**',
'password': '',
'settings': psycopg2.extras.Json({}),
}
result, errors = save_row(db_conn, query, params)
assert errors
assert not result