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)
