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 list_all_unit_entity_ids(db_conn):
"""
List all unit entity ids.
"""
query = """
SELECT entity_id
FROM units;
"""
params = {}
return [row['entity_id'] for row in list_rows(db_conn, query, params)]
def list_users(db_conn, params):
"""
Get a list of _all_ users of Sagefy.
"""
query = """
SELECT *
FROM users
ORDER BY created DESC;
/* TODO OFFSET LIMIT */
"""
params = {}
return list_rows(db_conn, query, params)
def list_posts_by_user(db_conn, params):
"""
Get a list of posts in Sagefy.
"""
query = """
SELECT *
FROM posts
WHERE user_id = %(user_id)s
ORDER BY created ASC;
/* TODO OFFSET LIMIT */
"""
params = pick(params, ('user_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 list_my_recently_created_subjects(db_conn, user_id):
"""
List My Recently Created Subjects (by User ID)
"""
query = """
SELECT DISTINCT ON (entity_id) *
FROM subjects
WHERE user_id = %(user_id)s
ORDER BY entity_id, created DESC;
/* TODO LIMIT OFFSET */
"""
params = {'user_id': user_id}
return list_rows(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 get_recommended_subjects(db_conn):
"""
list recommended subjects
"""
query = """
SELECT DISTINCT ON (entity_id) *
FROM subjects
WHERE status = 'accepted' AND name = %(name)s
ORDER BY entity_id, created DESC;
/* TODO LIMIT OFFSET */
"""
params = {
'name': 'An Introduction to Electronic Music',
}
return list_rows(db_conn, query, params)
def list_votes_by_proposal(db_conn, proposal_id):
"""
List votes for a given proposal.
"""
query = """
SELECT *
FROM posts
WHERE kind = 'vote' AND replies_to_id = %(proposal_id)s
ORDER BY created DESC;
/* TODO OFFSET LIMIT */
"""
params = {
'proposal_id': convert_slug_to_uuid(proposal_id),
}
return list_rows(db_conn, query, params)
def list_topics_by_entity_id(db_conn, entity_id, params):
"""
Get a list of models matching the provided keyword arguments.
Return empty array when no models match.
"""
query = """
SELECT *
FROM topics
WHERE entity_id = %(entity_id)s
ORDER BY created DESC;
/* TODO OFFSET LIMIT */
"""
params = {
'entity_id': convert_slug_to_uuid(entity_id),
}
return list_rows(db_conn, query, params)
def list_users_by_user_ids(db_conn, user_ids):
"""
Get a list of users by their user id.
"""
query = """
SELECT *
FROM users
WHERE id in %(user_ids)s
ORDER BY created DESC;
/* TODO OFFSET LIMIT */
"""
user_ids = tuple([convert_slug_to_uuid(user_id) for user_id in user_ids])
params = {
'user_ids': user_ids,
}
return list_rows(db_conn, query, params)
def list_many_unit_versions(db_conn, version_ids):
"""
List Unit Versions by VIDs
"""
if not version_ids:
return []
query = """
SELECT *
FROM units
WHERE version_id in %(version_ids)s
ORDER BY created DESC;
/* TODO LIMIT OFFSET */
"""
params = {
'version_ids': tuple(convert_slug_to_uuid(vid) for vid in version_ids)
}
return list_rows(db_conn, query, params)
def list_latest_accepted_subjects(db_conn, entity_ids):
"""
List Latest Accepted Subject Versions by EIDs
"""
if not entity_ids:
return []
query = """
SELECT DISTINCT ON (entity_id) *
FROM subjects
WHERE status = 'accepted' AND entity_id in %(entity_ids)s
ORDER BY entity_id, created DESC;
/* TODO LIMIT OFFSET */
"""
params = {'entity_ids': tuple([
convert_slug_to_uuid(entity_id)
for entity_id in entity_ids
])}
return list_rows(db_conn, query, params)
def list_posts_by_topic(db_conn, params):
"""
Get a list of posts in Sagefy.
"""
query = """
SELECT *
FROM posts
WHERE topic_id = %(topic_id)s
ORDER BY created ASC
OFFSET %(offset)s
LIMIT %(limit)s;
"""
params = {
'topic_id': convert_slug_to_uuid(params['topic_id']),
'offset': params.get('offset') or 0,
'limit': params.get('limit') or 10,
}
return list_rows(db_conn, query, params)
def list_follows_by_user(db_conn, params):
"""
Get a list of models matching the provided arguments.
Also adds pagination capabilities.
Returns empty array when no models match.
TODO-3 filter by entity kind as well
"""
query = """
SELECT *
FROM follows
WHERE user_id = %(user_id)s
ORDER BY created DESC;
/* TODO OFFSET LIMIT */
"""
params = {
'user_id': convert_slug_to_uuid(params['user_id']),
}
return list_rows(db_conn, query, params)
def list_required_by_units(db_conn, entity_id):
"""
List Latest Version of Required By Units by EID
"""
query = """
WITH temp as (
SELECT DISTINCT ON (entity_id) *
FROM units
WHERE status = 'accepted'
ORDER BY entity_id, created DESC
)
SELECT *
FROM temp
WHERE %(entity_id)s = ANY(require_ids)
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_notices(db_conn, params):
"""
Get a list of models matching the provided arguments.
Also adds pagination capabilities.
Returns empty array when no models match.
TODO-2 add filters for kind, tags, read
"""
query = """
SELECT *
FROM notices
WHERE user_id = %(user_id)s
ORDER BY created DESC
LIMIT %(limit)s
OFFSET %(offset)s;
"""
params = {
'user_id': convert_slug_to_uuid(params['user_id']),
'limit': params.get('limit') or 10,
'offset': params.get('offset') or 0,
}
return list_rows(db_conn, query, params)
def list_subject_parents(db_conn, subject_id):
"""
List the direct parents of the subject specified.
"""
subject_id = convert_uuid_to_slug(subject_id)
# ENSURE THIS IS SQL SAFE
subject_id = re.sub(r'[^a-zA-Z0-9\-\_]', '', subject_id)
query = """
WITH temp AS (
SELECT DISTINCT ON (entity_id) *
FROM subjects
WHERE status = 'accepted'
ORDER BY entity_id, created DESC
)
SELECT *
FROM temp
WHERE members @> '[{"id":"%(subject_id)s"}]'
ORDER BY created DESC;
/* TODO limit offset */
""" % {'subject_id': subject_id}
params = {}
return list_rows(db_conn, query, params)
def list_subjects_by_unit_flat(db_conn, unit_id):
"""
List Subjects by Unit EID
"""
unit_id = convert_uuid_to_slug(unit_id)
# ENSURE THIS IS SQL SAFE
unit_id = re.sub(r'[^a-zA-Z0-9\-\_]', '', unit_id)
query = """
WITH temp AS (
SELECT DISTINCT ON (entity_id) *
FROM subjects
WHERE status = 'accepted'
ORDER BY entity_id, created DESC
)
SELECT *
FROM temp
WHERE members @> '[{"id":"%(unit_id)s"}]'
ORDER BY created DESC;
/* TODO limit offset */
""" % {'unit_id': unit_id}
params = {}
return list_rows(db_conn, query, params)
def test_list_rows_error(db_conn):
query = "a;"
params = {}
result = list_rows(db_conn, query, params)
assert result == []
