From 7c8ff8076a9e8925f093310bb520be83f04c7617 Mon Sep 17 00:00:00 2001
From: Simon Morgan <sjm@spamcop.net>
Date: Wed, 18 Feb 2009 00:15:03 +0000
Subject: [PATCH] More refactoring. This time post.cgi.

---
 common.py | 55 ++++++++++++++++++++++++++++++++++++++++---------------
 index.cgi |  4 ++--
 post.cgi  | 16 ++++++----------
 3 files changed, 48 insertions(+), 27 deletions(-)

diff --git a/common.py b/common.py
index 3906b68..69960f0 100644
--- a/common.py
+++ b/common.py
@@ -33,23 +33,19 @@ def connect():
         sys.exit(1)
     return conn
 
-def getposts(conn, offset=0, numposts=config.NUMPOSTS):
-    posts = []
-    for row in conn.execute("SELECT * FROM entries ORDER BY date DESC LIMIT ? OFFSET ?",
-            (numposts, offset)):
-        posts.append(row)
-    return posts
-
-def getpost(conn, postid):
-    row = conn.execute("SELECT * FROM entries WHERE id = ?",
-            (postid,)).fetchone()
-    return row
+def addpost(conn, title, body):
+    curtime = int(time.time())
+    # Convert hours to seconds.
+    curtime = curtime + (config.TOFFSET * 60 * 60)
+    conn.execute("INSERT INTO entries VALUES (NULL, ?, ?, ?)",
+            (curtime, title, body))
 
 def getnumposts(conn, postid=None):
-    """Enumerate the number of posts in the database. If an ID is
-    specified then enumerate the number of posts with that ID. The
-    result of the latter should be 0 or 1 as essentially this is a check
-    for whether the specified post exists.
+    """Enumerate the number of posts in the database.
+
+    If an ID is specified then enumerate the number of posts with that
+    ID. The result of the latter should be 0 or 1 as essentially this is
+    a check for whether the specified post exists.
     """
     if postid:
         numposts = conn.execute("SELECT count(id) FROM entries WHERE id = ?",
@@ -58,6 +54,35 @@ def getnumposts(conn, postid=None):
         numposts = conn.execute("SELECT count(id) FROM entries").fetchone()
     return int(numposts[0])
 
+def getposts(conn, offset=0, numposts=0):
+    """Return a list of numposts posts beginning at offset.
+
+    SQLite doesn't seem to support the use of OFFSET without LIMIT so
+    whenever numposts is ommited, we return every post.
+    """
+    posts = []
+    if numposts == 0:
+        for row in conn.execute("SELECT * FROM entries ORDER BY date DESC"):
+            posts.append(row)
+    else:
+        for row in conn.execute("SELECT * FROM entries ORDER BY date DESC LIMIT ? OFFSET ?",
+                (numposts, offset)):
+            posts.append(row)
+    return posts
+
+def getpost(conn, postid):
+    row = conn.execute("SELECT * FROM entries WHERE id = ?",
+            (postid,)).fetchone()
+    # No need to return the ID as it's one of the parameters.
+    return row[1:]
+
+def updatepost(conn, postid, title, body):
+    conn.execute("UPDATE entries SET title = ?, text = ? WHERE id = ?",
+            (title, body, postid))
+
+def deletepost(conn, postid):
+    conn.execute("DELETE FROM entries WHERE id = ?", (postid,))
+
 # Output formatting.
 
 def print_headers(title):
diff --git a/index.cgi b/index.cgi
index e993f66..13cb379 100755
--- a/index.cgi
+++ b/index.cgi
@@ -39,7 +39,7 @@ else:
     if form.has_key("id"):
         post = common.getpost(conn, form.getvalue("id"))
         if post:
-            (_, date, title, text) = post
+            (date, title, text) = post
             common.print_post(title, text, date)
         else:
             common.print_msg("No such post.")
@@ -47,7 +47,7 @@ else:
         offset = 0
         if form.has_key("offset"):
             offset = int(form.getvalue("offset"))
-        posts = common.getposts(conn, offset)
+        posts = common.getposts(conn, offset, config.NUMPOSTS)
         for (postid, date, title, text) in posts:
             title = '<a href="index.cgi?id=%s">%s</a>' % (postid, title)
             common.print_post(title, text, date)
diff --git a/post.cgi b/post.cgi
index 55cb6b2..e7614bd 100755
--- a/post.cgi
+++ b/post.cgi
@@ -16,7 +16,6 @@
 
 import cgi
 #import cgitb; cgitb.enable()
-import sqlite3
 import time
 
 import common
@@ -38,21 +37,18 @@ common.print_headers(config.TITLE + " - Post")
 
 if form.has_key("delete"):
     for postid in form.getlist("delete"):
-        conn.execute("DELETE FROM entries WHERE id = ?", (postid,))
+        common.deletepost(conn, postid)
 elif form.has_key("preview"):
     edit_title = form.getvalue("title")
     edit_text = form.getvalue("body")
 elif form.has_key("edit"):
-    (edit_title, edit_text) = conn.execute("SELECT title, text FROM entries WHERE id = ?", (form.getvalue("edit"),)).fetchone()
+    (_, edit_title, edit_text) = common.getpost(conn, form.getvalue("edit"))
 elif form.has_key("title") and form.has_key("body"):
     if form.has_key("update"):
-        conn.execute("UPDATE entries SET title = ?, text = ? WHERE id = ?", (form.getvalue("title"), form.getvalue("body"), form.getvalue("update")))
+        common.updatepost(conn, form.getvalue("update"),
+                form.getvalue("title"), form.getvalue("body"))
     else:
-        curtime = int(time.time())
-        # Convert hours to seconds.
-        curtime = curtime + (config.TOFFSET * 60 * 60)
-        conn.execute("INSERT INTO entries VALUES (NULL, ?, ?, ?)",
-                (curtime, form.getvalue("title"), form.getvalue("body")))
+        common.addpost(conn, form.getvalue("title"), form.getvalue("body"))
 
 print '<form action="post.cgi" method="post">'
 
@@ -61,7 +57,7 @@ if common.getnumposts(conn) == 0:
 else:
     print '<table id="postlist">'
     print '<tr><th>ID</th><th>Date</th><th>Title</th><th>Delete</th></tr>'
-    for (postid, date, title) in conn.execute("SELECT id, date, title FROM entries ORDER BY date DESC"):
+    for (postid, date, title, _) in common.getposts(conn):
         print '<tr>'
         print '<td>%s</td>' % postid
         print '<td>%s</td>' % time.strftime("%y/%m/%d %H:%M:%S", time.gmtime(date))
-- 
2.11.4.GIT