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 == []