quotes/app.py

#!/usr/bin/env python3

import sqlite3, random

from flask import *
from pagination import Pagination

PER_PAGE = 15

app = Flask(__name__)

DATABASE = "quotes.db"


# util methods


def get_db():
    db = getattr(g, "_database", None)
    if db is None:
        db = g._database = sqlite3.connect(DATABASE)
    db.row_factory = sqlite3.Row
    return db


def query_db(query, args=(), one=False):
    cur = get_db().execute(query, args)
    rv = cur.fetchall()
    cur.close()
    return (rv[0] if rv else None) if one else rv


@app.teardown_request
def teardown(exc):
    db = getattr(g, "_database", None)
    if db is not None:
        db.close()


def url_for_other_page(page):
    args = request.view_args.copy()
    args["page"] = page
    return url_for(request.endpoint, **args)


app.jinja_env.globals["url_for_other_page"] = url_for_other_page


# db helper methods


def get_post(id=1):
    row = query_db("SELECT * FROM quotes WHERE ID = ?", (id,))
    if not row:
        return False, []
    return True, row[0]


def get_random_post():
    return query_db("SELECT * FROM quotes ORDER BY RANDOM() LIMIT 1", one=True)


def get_next_post_id():
    return query_db("SELECT MAX(id) FROM quotes", one=True)[0] + 1


def insert_new_post(id, text):
    query_db("INSERT INTO quotes VALUES (?, ?, ?)", (id, text, 0))
    get_db().commit()


def vote(id, d=1):
    query_db("UPDATE quotes SET score = score + ? WHERE id = ?", (d, id))
    get_db().commit()


def count_all_quotes():
    return query_db("SELECT COUNT(*) FROM quotes", one=True)[0]


def get_quotes_for_page(page):
    offset = count_all_quotes() - ((page - 1) * PER_PAGE) + 1
    return query_db(
        "SELECT * FROM quotes WHERE id < ? ORDER BY id DESC LIMIT ?", (offset, PER_PAGE)
    )


def get_top_quotes_for_page(page):
    offset = (page - 1) * PER_PAGE
    return query_db(
        """
        SELECT * FROM quotes WHERE id NOT IN
            (SELECT id FROM quotes ORDER BY score DESC, id DESC LIMIT ?)
        ORDER BY score DESC, id DESC LIMIT ?
        """,
        (offset, PER_PAGE),
    )


def search_quotes_db(searchterm):
    return query_db(
        """
        SELECT rowid, HIGHLIGHT(quotes_idx, 0, '[', ']') 
        FROM quotes_idx WHERE quotes_idx = ?
        ORDER BY bm25(quotes_idx)
        """,
        [searchterm],
    )


# routes


@app.route("/", defaults={"page": 1})
@app.route("/page/<int:page>")
def home(page):
    quotes = get_quotes_for_page(page)
    if not quotes and page != 1:
        return render_template("oops.html", page=page)
    return render_template(
        "index.html",
        pagination=Pagination(page, PER_PAGE, count_all_quotes()),
        quotes=quotes,
    )


@app.route("/top", defaults={"page": 1})
@app.route("/top/<int:page>")
def top(page):
    quotes = get_top_quotes_for_page(page)
    if not quotes and page != 1:
        return render_template("oops.html", page=page)
    return render_template(
        "top.html",
        pagination=Pagination(page, PER_PAGE, count_all_quotes()),
        quotes=quotes,
    )


@app.route("/quote/<int:id>")
def quote(id):
    ok, row = get_post(id)
    if not ok:
        return render_template("oops.html", id=id)
    return render_template("quote.html", row=row)


@app.route("/upvote/<int:id>")
def upvote(id):
    ok, row = get_post(id)
    if not ok:
        return render_template("oops.html", id=id)
    vote(id, 1)
    return redirect(url_for("quote", id=id), code=307)


@app.route("/downvote/<int:id>")
def downvote(id):
    ok, row = get_post(id)
    if not ok:
        return render_template("oops.html", id=id)
    vote(id, -1)
    return redirect(url_for("quote", id=id), code=307)


@app.route("/quote/submit")
def submitform():
    return render_template("submit.html")


@app.route("/submit", methods=["POST"])
def submit():
    if request.form["quote"]:
        nid = get_next_post_id()
        insert_new_post(nid, request.form["quote"])
        return redirect(url_for("quote", id=nid), code=302)
    else:
        return redirect(url_for("submitform"), code=307)


@app.route("/random")
def random():
    return render_template("quote.html", row=get_random_post())


@app.route("/search", methods=["GET", "POST"])
def search():
    query = request.args.get("q")
    return render_template(
        "search-results.html", results=search_quotes_db(query), query=query
    )