1 files changed, 47 insertions, 19 deletions
diff --git a/src/controller/routes.phel b/src/controller/routes.phel
index 31398e7..096e23e 100644
--- a/src/controller/routes.phel
+++ b/src/controller/routes.phel
@@ -5,29 +5,45 @@
   (:require phel.router :as r)
   (:require phel.http :as h)
   (:require phel.pdo)
+  (:require phel.sql)
   (:require phel.string :as str)
   (:require lezer.db :refer [conn]))
 
 (defn get-books []
-  (pdo/select conn "select * from books"))
+  (let [[query _] (sql/format {:select [:*]
+                               :from [:books]})]
+    (pdo/select conn query)))
 
 (defn get-authors-by-book [book]
-  (pdo/select
-   conn
-   "select * from authors where id in (select author_id from authors_books where book_id = :book_id)"
-   {:book_id (:id book)}))
+  (let [[query params] (sql/format
+                        {:select [:*]
+                         :from [:authors]
+                         :where [:in :id {:select [:author_id]
+                                          :from [:authors_books]
+                                          :where [:= :book_id (:id book)]}]})]
+    (-> (pdo/prepare conn query)
+        (pdo/execute params)
+        (pdo/fetch-all))))
 
 (defn create-book [fields]
   (pdo/insert conn :books fields))
 
 (defn get-or-create-author-by-name [fields]
-  (or (pdo/fetch-column
-       (pdo/query conn "select id from authors where name = :name"
-                  {:name (:name fields)}))
-      (pdo/insert conn :authors fields)))
+  (let [[query params] (sql/format {:select [:id]
+                                    :from [:authors]
+                                    :where [:= :name (:name fields)]})]
+    (or (-> (pdo/prepare conn query)
+            (pdo/execute params)
+            (pdo/fetch-column))
+        (pdo/insert conn :authors fields))))
 
 (defn get-book-pages-by-id [book-id]
-  (pdo/fetch-column (pdo/query conn "select pages from books where id = :id" {:id book-id})))
+  (let [[query params] (sql/format {:select [:pages]
+                                    :from [:books]
+                                    :where [:= :id book-id]})]
+    (-> (pdo/prepare conn query)
+        (pdo/execute params)
+        (pdo/fetch-column))))
 
 (defn create-book-session [fields]
   (pdo/insert conn :books_sessions fields))
@@ -35,23 +51,35 @@
 (defn create-session-progress [fields]
   (pdo/insert conn :sessions_progress fields))
 
-(defn index-handler [req router]
-  (let [books (for [b :in (get-books)]
-                {:book b
-                 :authors (get-authors-by-book b)})]
-    (h/response-from-map {:status 200
-                          :body (index-html books router)})))
-
 (defn get-author-by-id [id]
-  (first (pdo/select conn "select * from authors where id = :author_id" {:author_id id})))
+  (let [[query params] (sql/format {:select [:*]
+                                    :from [:authors]
+                                    :where [:= :id id]})]
+    (-> (pdo/prepare conn query)
+        (pdo/execute params)
+        (pdo/fetch))))
 
 (defn get-books-by-author-id [id]
-  (pdo/select conn "select * from books where id in (select book_id from authors_books where author_id = :author_id)" {:author_id id}))
+  (let [[query params] (sql/format {:select [:*]
+                                    :from [:books]
+                                    :where [:in :id {:select [:book_id]
+                                                     :from [:authors_books]
+                                                     :where [:= :author_id id]}]})]
+    (-> (pdo/prepare conn query)
+        (pdo/execute params)
+        (pdo/fetch-all))))
 
 (defn add-author-to-book [book-id fields]
   (pdo/insert conn :authors_books {:book_id book-id
                                    :author_id (get-or-create-author-by-name fields)}))
 
+(defn index-handler [req router]
+  (let [books (for [b :in (get-books)]
+                {:book b
+                 :authors (get-authors-by-book b)})]
+    (h/response-from-map {:status 200
+                          :body (index-html books router)})))
+
 (defn book-create-handler [request]
   (let [title (get-in request [:parsed-body "title"])
         subtitle (get-in request [:parsed-body "subtitle"])