def select_tales_other_creator(user_id, viewer_id, rows = None): return Tale._construct_tale_objects( DAO.select_by( ''' SELECT DISTINCT tale_id, tale_title, tale_description, tale_category, tale_creator, tale_license, tale_star_count, tale_follow_count, tale_contribution_request_count, tale_creation_datetime FROM anaddventure.tale INNER JOIN anaddventure.contribution_request ON contribution_request_system_user_id = (%s) AND tale_creator != contribution_request_system_user_id AND tale_id = contribution_request_tale_id AND ( tale_category = 1 OR ( ( SELECT COUNT(*) FROM anaddventure.invitation WHERE invitation_creator = tale_creator AND invitation_invited = contribution_request_system_user_id ) > 0 AND ( SELECT COUNT(*) FROM anaddventure.invitation WHERE invitation_creator = tale_creator AND invitation_invited = (%s) ) > 0 ) ) ORDER BY tale_star_count DESC ''', (user_id, viewer_id), rows ) )
def select_top_ten_order_by_star_count(): return Tale._construct_tale_objects( DAO.select_by( "SELECT * FROM anaddventure.tale WHERE tale_category = 1 ORDER BY tale_star_count DESC LIMIT 5", () ) )
def select_viewable_by_creator_id_and_viewer_id( creator_id, viewer_id, rows = None ): return Tale._construct_tale_objects( DAO.select_by( ''' SELECT DISTINCT tale_id, tale_title, tale_description, tale_category, tale_creator, tale_license, tale_star_count, tale_follow_count, tale_contribution_request_count, tale_creation_datetime FROM anaddventure.tale INNER JOIN anaddventure.invitation ON tale_creator = (%s) AND ( tale_category = 1 OR ( invitation_creator = tale_creator AND invitation_invited = (%s) AND invitation_tale_id = tale_id ) OR tale_creator = (%s) ) ORDER BY tale_star_count DESC ''', (creator_id, viewer_id, viewer_id), rows ) )
def select_tales_other_creator(user_id, viewer_id, rows=None): return Tale._construct_tale_objects( DAO.select_by( ''' SELECT DISTINCT tale_id, tale_title, tale_description, tale_category, tale_creator, tale_license, tale_star_count, tale_follow_count, tale_contribution_request_count, tale_creation_datetime FROM anaddventure.tale INNER JOIN anaddventure.contribution_request ON contribution_request_system_user_id = (%s) AND tale_creator != contribution_request_system_user_id AND tale_id = contribution_request_tale_id AND ( tale_category = 1 OR ( ( SELECT COUNT(*) FROM anaddventure.invitation WHERE invitation_creator = tale_creator AND invitation_invited = contribution_request_system_user_id ) > 0 AND ( SELECT COUNT(*) FROM anaddventure.invitation WHERE invitation_creator = tale_creator AND invitation_invited = (%s) ) > 0 ) ) ORDER BY tale_star_count DESC ''', (user_id, viewer_id), rows))
def select_by_tale_id_and_previous_chapter_id(tale_id, previous_chapter_id, rows=None): return Chapter._construct_chapter_objects( DAO.select_by( "SELECT * FROM anaddventure.chapter WHERE chapter_tale_id = (%s) AND chapter_previous_chapter = (%s)", (tale_id, previous_chapter_id), rows))
def select_all(rows = None): return License._construct_license_objects( DAO.select_by( "SELECT * FROM anaddventure.license", (), rows ) )
def select_by_name(name, rows = None): return License._construct_license_objects( DAO.select_by( "SELECT * FROM anaddventure.license WHERE license_name ILIKE (%s)", ('%' + name + '%', ), rows ) )
def select_by_id(id, rows = None): return Password_Change_Requests._construct_password_change_request_objects( DAO.select_by( "SELECT * FROM anaddventure.password_change_requests WHERE password_change_requests_id LIKE (%s)", (hashlib.sha256(id.encode('utf-8')).hexdigest(), ), rows ) )
def select_top_ten(rows = None): return Genre._construct_genre_objects( DAO.select_by( "SELECT * FROM anaddventure.genre ORDER BY genre_tale_count DESC, genre_type ASC LIMIT 10", (), rows ) )
def select_by_title_and_genre_id(title, genre_id, rows=None): return DAO.select_by( ''' SELECT * FROM anaddventure.tale INNER JOIN anaddventure.tale_genre ON tale_genre_genre_id = (%s) AND tale_id = tale_genre_tale_id AND tale_title ILIKE (%s) ''', (genre_id, '%' + title + '%'), rows)
def select_count_by_title(title = '', rows = None): return Tale._construct_tale_objects( DAO.select_by( "SELECT COUNT(tale_id) FROM anaddventure.tale WHERE tale_title ILIKE (%s)", ('%' + title + '%', ), rows ) )
def select_by_number(number, rows = None): return Chapter._construct_chapter_objects( DAO.select_by( "SELECT * FROM anaddventure.chapter WHERE chapter_number = (%s)", (number, ), rows ) )
def select_by_tale_id_order_by_datetime(tale_id, rows=None): return Contribution_Request._construct_contribution_request_objects( DAO.select_by( ''' SELECT * FROM anaddventure.contribution_request WHERE contribution_request_tale_id = (%s) ORDER BY contribution_request_datetime DESC ''', (tale_id, ), rows))
def select_by_id(genre_id, rows = None): return Genre._construct_genre_objects( DAO.select_by( "SELECT * FROM anaddventure.genre WHERE genre_id = (%s)", (genre_id, ), rows ) )
def select_all(rows = None): return Genre._construct_genre_objects( DAO.select_by( "SELECT * FROM anaddventure.genre", (), rows ) )
def select_by_type(category, rows = None): return Genre._construct_genre_objects( DAO.select_by( "SELECT * FROM anaddventure.genre WHERE genre_type ILIKE (%s)", ('%' + category + '%', ), rows ) )
def select_last_update(tale_id, rows=None): return DAO.select_by( ''' SELECT MAX(chapter_datetime) FROM anaddventure.chapter INNER JOIN anaddventure.tale ON tale_id = (%s) AND chapter_tale_id = tale_id ''', (tale_id, ), rows)
def select_contributors_id(tale_id, rows=None): return DAO.select_by( ''' SELECT DISTINCT chapter_system_user_id FROM anaddventure.tale INNER JOIN anaddventure.chapter ON tale_id = (%s) AND tale_id = chapter_tale_id ''', (tale_id, ), rows)
def select_count_viewable_by_title_and_creator_id(title, creator_id, rows=None): return DAO.select_by( ''' SELECT COUNT(tale_id) FROM anaddventure.tale WHERE tale_title ILIKE (%s) AND (tale_category = 1 OR tale_creator = (%s)) ''', ('%' + title + '%', creator_id), rows)
def select_by_was_closed(was_closed, rows = None): return Contribution_Request._construct_contribution_request_objects( DAO.select_by( "SELECT * FROM anaddventure.contribution_request WHERE contribution_request_was_closed = (%s)", (was_closed, ), rows ) )
def select_by_creator_id(creator_id, rows = None): return Tale._construct_tale_objects( DAO.select_by( "SELECT * FROM anaddventure.tale WHERE tale_creator = (%s)", (creator_id, ), rows ) )
def select_by_license_id(license_id, rows = None): return Tale._construct_tale_objects( DAO.select_by( "SELECT * FROM anaddventure.tale WHERE tale_license = (%s)", (license_id, ), rows ) )
def select_by_user_id(user_id, rows = None): return Chapter._construct_chapter_objects( DAO.select_by( "SELECT * FROM anaddventure.chapter WHERE chapter_system_user_id = (%s)", (user_id, ), rows ) )
def select_by_creator_id_and_full_title(creator_id, title, rows = None): return Tale._construct_tale_objects( DAO.select_by( "SELECT * FROM anaddventure.tale WHERE tale_creator = (%s) AND tale_title ILIKE (%s)", (creator_id, title), rows ) )
def select_by_previous_chapter_id(previous_chapter_id, rows = None): return Chapter._construct_chapter_objects( DAO.select_by( "SELECT * FROM anaddventure.chapter WHERE chapter_previous_chapter = (%s)", (previous_chapter_id, ), rows ) )
def select_by_tale_id_order_by_date(tale_id, rows = None): return Chapter._construct_chapter_objects( DAO.select_by( "SELECT * FROM anaddventure.chapter WHERE chapter_tale_id = (%s) ORDER BY chapter_datetime DESC", (tale_id, ), rows ) )
def select_by_id(id, rows = None): return License._construct_license_objects( DAO.select_by( "SELECT * FROM anaddventure.license WHERE license_id = (%s)", (id, ), rows ) )
def select_by_category(category = '', rows = None): return Tale._construct_tale_objects( DAO.select_by( "SELECT * FROM anaddventure.tale WHERE tale_category = (%s)", (category, ), rows ) )
def select_by_title(title, rows = None): return Chapter._construct_chapter_objects( DAO.select_by( "SELECT * FROM anaddventure.chapter WHERE chapter_title ILIKE (%s)", ('%' + title + '%', ), rows ) )
def select_by_full_title(title = '', rows = None): return Tale._construct_tale_objects( DAO.select_by( "SELECT * FROM anaddventure.tale WHERE tale_title ILIKE (%s)", (title, ), rows ) )
def select_by_tale_id(tale_id, rows = None): return Contribution_Request._construct_contribution_request_objects( DAO.select_by( "SELECT * FROM anaddventure.contribution_request WHERE contribution_request_tale_id = (%s)", (tale_id, ), rows ) )
def select_all(rows = None): return Tale._construct_tale_objects( DAO.select_by( "SELECT * FROM anaddventure.tale", (), rows ) )
def select_all(rows = None): return Chapter._construct_chapter_objects( DAO.select_by( "SELECT * FROM anaddventure.chapter", (), rows ) )
def select_by_date(date, rows = None): return Chapter._construct_chapter_objects( DAO.select_by( "SELECT * FROM anaddventure.chapter WHERE chapter_datetime = (%s)", (date, ), rows ) )
def select_by_tale_id_and_previous_chapter_id(tale_id, previous_chapter_id, rows = None): return Chapter._construct_chapter_objects( DAO.select_by( "SELECT * FROM anaddventure.chapter WHERE chapter_tale_id = (%s) AND chapter_previous_chapter = (%s)", (tale_id, previous_chapter_id), rows ) )
def select_count_viewable_by_title_and_creator_id(title, creator_id, rows = None): return DAO.select_by( ''' SELECT COUNT(tale_id) FROM anaddventure.tale WHERE tale_title ILIKE (%s) AND (tale_category = 1 OR tale_creator = (%s)) ''', ('%' + title + '%', creator_id), rows )
def select_last_update(tale_id, rows = None): return DAO.select_by( ''' SELECT MAX(chapter_datetime) FROM anaddventure.chapter INNER JOIN anaddventure.tale ON tale_id = (%s) AND chapter_tale_id = tale_id ''', (tale_id, ), rows )
def select_contributors_id(tale_id, rows = None): return DAO.select_by( ''' SELECT DISTINCT chapter_system_user_id FROM anaddventure.tale INNER JOIN anaddventure.chapter ON tale_id = (%s) AND tale_id = chapter_tale_id ''', (tale_id, ), rows )
def select_by_title_and_genre_id(title, genre_id, rows = None): return DAO.select_by( ''' SELECT * FROM anaddventure.tale INNER JOIN anaddventure.tale_genre ON tale_genre_genre_id = (%s) AND tale_id = tale_genre_tale_id AND tale_title ILIKE (%s) ''', (genre_id, '%' + title + '%'), rows )
def select_viewable_by_title_and_creator_id(title, creator_id, rows=None): return Tale._construct_tale_objects( DAO.select_by( ''' SELECT * FROM anaddventure.tale WHERE tale_title ILIKE (%s) AND ( tale_category = 1 OR tale_creator = (%s) ) ''', ('%' + title + '%', creator_id), rows))
def select_viewable_by_title_creator_id_and_genre_id( title, creator_id, genre_id, rows=None): tales_aux = DAO.select_by( ''' SELECT * FROM anaddventure.tale INNER JOIN anaddventure.tale_genre ON tale_genre_genre_id = (%s) AND tale_id = tale_genre_tale_id AND tale_title ILIKE (%s) AND (tale_category = 1 OR tale_creator = (%s)) ''', (genre_id, '%' + title + '%', creator_id), rows) return Tale._construct_tale_objects([tale[:10] for tale in tales_aux])
def select_by_tale_id_order_by_datetime(tale_id, rows = None): return Contribution_Request._construct_contribution_request_objects( DAO.select_by( ''' SELECT * FROM anaddventure.contribution_request WHERE contribution_request_tale_id = (%s) ORDER BY contribution_request_datetime DESC ''', (tale_id, ), rows ) )
def select_viewable_by_title_creator_id_and_genre_id(title, creator_id, genre_id, rows = None): tales_aux = DAO.select_by( ''' SELECT * FROM anaddventure.tale INNER JOIN anaddventure.tale_genre ON tale_genre_genre_id = (%s) AND tale_id = tale_genre_tale_id AND tale_title ILIKE (%s) AND (tale_category = 1 OR tale_creator = (%s)) ''', (genre_id, '%' + title + '%', creator_id), rows ) return Tale._construct_tale_objects([tale[:10] for tale in tales_aux])
def select_top_ten_order_by_star_count_today(): tales_aux = DAO.select_by( ''' SELECT *, ( SELECT COUNT(*) AS star_count_today FROM anaddventure.star WHERE star_tale_id = tale_id AND star_datetime >= CURRENT_DATE + interval '1 second' ) FROM anaddventure.tale WHERE tale_category = 1 ORDER BY star_count_today DESC LIMIT 5 ''', ()) tales = Tale._construct_tale_objects([tale[:10] for tale in tales_aux]) for i in range(0, len(tales)): tales[i]['stars_today'] = tales_aux[i][10] return tales
def select_viewable_by_creator_id_and_viewer_id(creator_id, viewer_id, rows=None): return Tale._construct_tale_objects( DAO.select_by( ''' SELECT DISTINCT tale_id, tale_title, tale_description, tale_category, tale_creator, tale_license, tale_star_count, tale_follow_count, tale_contribution_request_count, tale_creation_datetime FROM anaddventure.tale INNER JOIN anaddventure.invitation ON tale_creator = (%s) AND ( tale_category = 1 OR ( invitation_creator = tale_creator AND invitation_invited = (%s) AND invitation_tale_id = tale_id ) OR tale_creator = (%s) ) ORDER BY tale_star_count DESC ''', (creator_id, viewer_id, viewer_id), rows))
def select_by_creator_id_and_full_title(creator_id, title, rows=None): return Tale._construct_tale_objects( DAO.select_by( "SELECT * FROM anaddventure.tale WHERE tale_creator = (%s) AND tale_title ILIKE (%s)", (creator_id, title), rows))
def select_by_id(tale_id, rows=None): return Tale._construct_tale_objects( DAO.select_by( "SELECT * FROM anaddventure.tale WHERE tale_id = (%s)", (tale_id, ), rows))
def select_chapters_count(tale_id, rows=None): return DAO.select_by( "SELECT COUNT(*) FROM anaddventure.chapter WHERE chapter_tale_id = (%s)", (tale_id, ), rows)
def select_by_user_id(user_id, rows=None): return Contribution_Request._construct_contribution_request_objects( DAO.select_by( "SELECT * FROM anaddventure.contribution_request WHERE contribution_request_system_user_id = (%s)", (user_id, ), rows))
def select_by_was_closed(was_closed, rows=None): return Contribution_Request._construct_contribution_request_objects( DAO.select_by( "SELECT * FROM anaddventure.contribution_request WHERE contribution_request_was_closed = (%s)", (was_closed, ), rows))
def select_by_user_id_and_tale_id(user_id, tale_id, rows=None): return DAO.select_by( "SELECT * FROM anaddventure.follow WHERE follow_system_user_id = (%s) AND follow_tale_id = (%s)", (user_id, tale_id), rows)
def select_by_tale_id(tale_id, rows=None): return DAO.select_by( "SELECT * FROM anaddventure.follow WHERE follow_tale_id = (%s)", (tale_id, ), rows)
def select_all(rows=None): return Tale._construct_tale_objects( DAO.select_by("SELECT * FROM anaddventure.tale", (), rows))
def select_top_ten_order_by_star_count(): return Tale._construct_tale_objects( DAO.select_by( "SELECT * FROM anaddventure.tale WHERE tale_category = 1 ORDER BY tale_star_count DESC LIMIT 5", ()))
def select_by_category(category='', rows=None): return Tale._construct_tale_objects( DAO.select_by( "SELECT * FROM anaddventure.tale WHERE tale_category = (%s)", (category, ), rows))
def select_by_genre_id(genre_id, rows=None): return DAO.select_by( "SELECT * FROM anaddventure.tale_genre WHERE tale_genre_genre_id = (%s)", (genre_id, ), rows)
def select_by_tale_id_and_genre_id(tale_id, genre_id, rows=None): return DAO.select_by( "SELECT * FROM anaddventure.tale_genre WHERE tale_genre_tale_id = (%s) AND tale_genre_genre_id = (%s)", (tale_id, genre_id), rows)