Use a preprared statement

2 files changed, 45 insertions, 46 deletions

diff --git a/PKGBUILD b/PKGBUILD
index c9efae8..91de46d 100644
--- a/PKGBUILD
+++ b/PKGBUILD
@@ -11,7 +11,7 @@ groups=()
 depends=(sqlite3 fcgi)
 source=("m2.c"
 "init.sql")
-sha256sums=('ef3cb6972e8b164411628a62dc274b0ff4eef84f86bad8641b7513fbaa94e098'
+sha256sums=('01d7b97355488e7505db26e6825f0bebec8451294a644de17d771992aabf4cae'
             'e71a00dfb9a2744f482de80e33c02341d6cae19a1f54e53a5346d972bfeff054')
 
 build() {
diff --git a/m2.c b/m2.c
index c54b4a3..1f7d962 100644
--- a/m2.c
+++ b/m2.c
@@ -8,44 +8,18 @@
 #include <stdlib.h>
 #include <assert.h>
 
-struct fcgi_env {
-    FCGX_Stream *in;
-    FCGX_Stream *out;
-    FCGX_Stream *err;
-    FCGX_ParamArray *envp;
-};
-
 static sqlite3 *db = NULL;
+static sqlite3_stmt *stmt = NULL;
 
 static void cleanup(void) {
+    if (stmt) {
+        sqlite3_finalize(stmt);
+    }
     if (db) {
         sqlite3_close(db);
     }
 }
 
-static int cb(void *dat, int count, char **data, char **columns) {
-    struct fcgi_env *env = dat;
-    if (count != 4) {
-        FCGX_FPrintF(env->out,
-                "Status: 500 Internal Server Error\r\n"
-                "Content-type: text/plain\r\n"
-                "\r\n"
-                "The database returned %d columns.\n",
-                count);
-        return 1;
-    }
-    FCGX_FPrintF(env->out,
-            "Content-type: application/json\r\n"
-            "\r\n"
-            "{\"title\":\"%s\",\"title_zh\":\"%s\",\"text\":\"%s\",\"text_zh\":\"%s\"}\n",
-            data[0],
-            data[1] == NULL ? "" : data[1],
-            data[2],
-            data[3]);
-
-    return 0;
-}
-
 int main(int argc, char **argv) {
     if (argc != 2) {
         fprintf(stderr, "Usage: %s /path/to/data.db\n", argv[0]);
@@ -57,24 +31,49 @@ int main(int argc, char **argv) {
         fprintf(stderr, "Cannot open database: %s\n", sqlite3_errstr(r));
         return r;
     }
+    if ((r = sqlite3_prepare(db, "SELECT title,title_zh,text,text_zh FROM v_random_quote", -1, &stmt, NULL))) {
+        fprintf(stderr, "Cannot prepare statement: %s\n", sqlite3_errstr(r));
+        return r;
+    }
     FCGX_Stream *in, *out, *err;
     FCGX_ParamArray envp;
-    struct fcgi_env env;
-    char *errmsg;
     while(FCGX_Accept(&in, &out, &err, &envp) >= 0) {
-        env.in = in;
-        env.out = out;
-        env.err = err;
-        env.envp = &envp;
-        if ((r = sqlite3_exec(db, "SELECT title,title_zh,text,text_zh FROM v_random_quote",
-                        cb, &env, &errmsg))) {
-            FCGX_FPrintF(out,
-                    "Status: 500 Internal Server Error\r\n"
-                    "Content-type: text/plain\r\n"
-                    "\r\n"
-                    "%s\n",
-                    errmsg);
-            sqlite3_free(errmsg);
+        switch (r = sqlite3_step(stmt)) {
+            case SQLITE_DONE:
+                FCGX_FPrintF(out,
+                        "Status: 500 Internal Server Error\r\n"
+                        "Content-type: text/plain\r\n"
+                        "\r\n"
+                        "No data is available.\n");
+                break;
+            case SQLITE_BUSY:
+            case SQLITE_MISUSE:
+            case SQLITE_ERROR:
+                fprintf(stderr, "Cannot query: %d\n", r);
+                FCGX_FPrintF(out,
+                        "Status: 500 Internal Server Error\r\n"
+                        "Content-type: text/plain\r\n"
+                        "\r\n"
+                        "%s\n",
+                        sqlite3_errstr(r));
+                break;
+            case SQLITE_ROW:
+                char *title = (char *) sqlite3_column_text(stmt, 0);
+                char *title_zh = (char *) sqlite3_column_text(stmt, 1);
+                char *text = (char *) sqlite3_column_text(stmt, 2);
+                char *text_zh = (char *) sqlite3_column_text(stmt, 3);
+                FCGX_FPrintF(out,
+                        "Content-type: application/json\r\n"
+                        "\r\n"
+                        "{\"title\":\"%s\",\"title_zh\":\"%s\",\"text\":\"%s\",\"text_zh\":\"%s\"}\n",
+                        title,
+                        title_zh ? title_zh : "",
+                        text,
+                        text_zh);
+                break;
+        }
+        if ((r = sqlite3_reset(stmt))) {
+            fprintf(stderr, "sqlite3_reset: %d\n", r);
         }
     }
 }