def get_notes(project_id):
return dbkit.query("""
SELECT note_id, note, created
FROM notes
WHERE project_id = ?
ORDER BY created DESC
""", (project_id,))
def get_projects():
return dbkit.query("""
SELECT slug, project, COUNT(note_id) AS notes
FROM projects
LEFT JOIN notes USING (project_id)
GROUP BY project_id
ORDER BY project
""")
def get_paste_list(page):
"""
"""
start = (page - 1) * constants.PASTES_PER_PAGE
return dbkit.query("""
SELECT paste_id, title, poster, created
FROM pastes
ORDER BY created
LIMIT ?, ?
""", (start, constants.PASTES_PER_PAGE))
def get_comments(paste_id):
"""
Get the comments attached to a paste.
"""
return dbkit.query("""
SELECT comment_id, paste_id, created, poster, body
FROM comments
WHERE paste_id = ?
ORDER BY created ASC
""", (paste_id,))
def get_latest_pastes():
"""
Get the most recently posted pastes.
"""
return dbkit.query("""
SELECT paste_id, title, created, poster
FROM pastes
ORDER BY created DESC
LIMIT ?
""", (utils.get_page_length(),))
def get_paste_list(page):
"""
Get the list of pastes for the given page.
"""
start = (page - 1) * utils.get_page_length()
return dbkit.query("""
SELECT paste_id, title, pastes.poster, pastes.created,
COUNT(comment_id) AS comments
FROM pastes
LEFT JOIN comments USING (paste_id)
GROUP BY paste_id
ORDER BY pastes.created DESC
LIMIT ?, ?
""", (start, utils.get_page_length()))
def get_paste(paste_id):
"""
Get a paste by ID.
"""
paste = dbkit.query_row("""
SELECT paste_id, created, poster, title
FROM pastes
WHERE paste_id = ?
""", (paste_id,))
if paste is not None:
paste['chunks'] = dbkit.query("""
SELECT chunk_id, body, syntax
FROM chunks
WHERE paste_id = ?
ORDER BY chunk_id
""", (paste_id,))
return paste
def get_names():
return query("SELECT name, n FROM greeted ORDER BY n", factory=dict_set)
def dump_counters():
"""
Query the database for all counters and their values.
"""
return query('SELECT counter, value FROM counters')
