Move database functions to model modules

5 files changed, 129 insertions, 109 deletions

diff --git a/src/controller/routes.phel b/src/controller/routes.phel
index d891ec4..980cf48 100644
--- a/src/controller/routes.phel
+++ b/src/controller/routes.phel
@@ -6,105 +6,27 @@
   (: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 []
-  (let [[query _] (sql/format {:select [:*]
-                               :from [:books]})]
-    (pdo/select conn query)))
-
-(defn- get-authors-by-book [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- get-sessions-by-book [book]
-  (let [[query params] (sql/format
-                        {:select [:*]
-                         :from [:books_sessions]
-                         :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]
-  (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]
-  (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))
-
-(defn- create-session-progress [fields]
-  (pdo/insert conn :sessions_progress fields))
-
-(defn- get-book-by-id [id]
-  (let [[query params] (sql/format {:select [:*]
-                                    :from [:books]
-                                    :where [:= :id id]})]
-    (-> (pdo/prepare conn query)
-        (pdo/execute params)
-        (pdo/fetch))))
-
-(defn- get-author-by-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]
-  (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)}))
+  (:require lezer.db :refer [conn])
+  (:require lezer.model.books)
+  (:require lezer.model.authors)
+  (:require lezer.model.sessions)
+  (:require lezer.model.progress))
 
 (defn index-handler [req router]
-  (let [books (for [b :in (get-books)]
+  (let [books (for [b :in (books/all)]
                 {:book b
-                 :authors (get-authors-by-book b)})]
+                 :authors (authors/for-book b)})]
     (h/response-from-map {:status 200
                           :body (index-html books router)})))
 
 (defn book-view-handler [request]
   (let [id (get-in request [:attributes :match :path-params :id])
-        book (get-book-by-id id)]
+        book (books/find id)]
     (h/response-from-map {:status 200
                           :body (book-html (conj book
-                                                 {:authors (get-authors-by-book book)
-                                                  :sessions (get-sessions-by-book book)}))})))
+                                                 {:authors (authors/for-book book)
+                                                  :sessions (sessions/for-book book)}))})))
 
 (defn book-create-handler [request]
   (let [title (get-in request [:parsed-body "title"])
@@ -113,36 +35,36 @@
         pages (get-in request [:parsed-body "pages"])]
     (pdo/with-transaction conn
       (let [time (php/time)
-            book-id (create-book {:title title
-                                  :subtitle subtitle
-                                  :pages pages
-                                  :created_at time
-                                  :updated_at time})]
+            book-id (books/create {:title title
+                                   :subtitle subtitle
+                                   :pages pages
+                                   :created_at time
+                                   :updated_at time})]
         (for [a :in authors]
-          (add-author-to-book book-id {:name (str/trim a)
-                                       :created_at time
-                                       :updated_at time})))))
+          (books/add-author book-id {:name (str/trim a)
+                                     :created_at time
+                                     :updated_at time})))))
   (h/response-from-map {:status 301 :headers {:location "/"}}))
 
 (defn book-session-create-handler [request]
   (let [id (get-in request [:attributes :match :path-params :id])
-        pages (get-book-pages-by-id id)
+        pages (books/pages id)
         time (php/time)]
     (pdo/with-transaction conn
-      (let [session-id (create-book-session {:book_id id
-                                             :status "started"
-                                             :started_at time
-                                             :created_at time
-                                             :updated_at time})]
-        (create-session-progress {:session_id session-id
-                                  :progress 0
-                                  :total pages
-                                  :created_at time
-                                  :updated_at time}))))
+      (let [session-id (sessions/create {:book_id id
+                                         :status "started"
+                                         :started_at time
+                                         :created_at time
+                                         :updated_at time})]
+        (progress/create {:session_id session-id
+                          :progress 0
+                          :total pages
+                          :created_at time
+                          :updated_at time}))))
   (h/response-from-map {:status 301 :headers {:location "/"}}))
 
 (defn author-view-handler [req]
   (let [id (get-in req [:attributes :match :path-params :id])
-        author (get-author-by-id id)
-        books (get-books-by-author-id id)]
+        author (authors/find id)
+        books (books/for-author id)]
     (h/response-from-map {:status 200 :body (author-html author books)})))
diff --git a/src/model/authors.phel b/src/model/authors.phel
new file mode 100644
index 0000000..518c13e
--- /dev/null
+++ b/src/model/authors.phel
@@ -0,0 +1,32 @@
+(ns lezer.model.authors
+  (:require lezer.db :refer [conn])
+  (:require phel.pdo)
+  (:require phel.sql))
+
+(defn for-book [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 find-or-create [name fields]
+  (let [[query params] (sql/format {:select [:id]
+                                    :from [:authors]
+                                    :where [:= :name name]})]
+    (or (-> (pdo/prepare conn query)
+            (pdo/execute params)
+            (pdo/fetch-column))
+        (pdo/insert conn :authors (conj {:name name} fields)))))
+
+(defn find [id]
+  (let [[query params] (sql/format {:select [:*]
+                                    :from [:authors]
+                                    :where [:= :id id]})]
+    (-> (pdo/prepare conn query)
+        (pdo/execute params)
+        (pdo/fetch))))
diff --git a/src/model/books.phel b/src/model/books.phel
new file mode 100644
index 0000000..26dbe6f
--- /dev/null
+++ b/src/model/books.phel
@@ -0,0 +1,43 @@
+(ns lezer.model.books
+  (:require lezer.db :refer [conn])
+  (:require phel.pdo)
+  (:require phel.sql)
+  (:require lezer.model.authors))
+
+(defn all []
+  (let [[query _] (sql/format {:select [:*]
+                               :from [:books]})]
+    (pdo/select conn query)))
+
+(defn create [fields]
+  (pdo/insert conn :books fields))
+
+(defn pages [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 find [id]
+  (let [[query params] (sql/format {:select [:*]
+                                    :from [:books]
+                                    :where [:= :id id]})]
+    (-> (pdo/prepare conn query)
+        (pdo/execute params)
+        (pdo/fetch))))
+
+(defn for-author [author-id]
+  (let [[query params] (sql/format {:select [:*]
+                                    :from [:books]
+                                    :where [:in :id {:select [:book_id]
+                                                     :from [:authors_books]
+                                                     :where [:= :author_id author-id]}]})]
+    (-> (pdo/prepare conn query)
+        (pdo/execute params)
+        (pdo/fetch-all))))
+
+(defn add-author [id fields]
+  (pdo/insert conn :authors_books {:book_id id
+                                   :author_id (authors/find-or-create (:name fields) fields)}))
diff --git a/src/model/progress.phel b/src/model/progress.phel
new file mode 100644
index 0000000..9f5c166
--- /dev/null
+++ b/src/model/progress.phel
@@ -0,0 +1,7 @@
+(ns lezer.model.progress
+  (:require lezer.db :refer [conn])
+  (:require phel.pdo)
+  (:require phel.sql))
+
+(defn create [fields]
+  (pdo/insert conn :sessions_progress fields))
diff --git a/src/model/sessions.phel b/src/model/sessions.phel
new file mode 100644
index 0000000..6ada967
--- /dev/null
+++ b/src/model/sessions.phel
@@ -0,0 +1,16 @@
+(ns lezer.model.sessions
+  (:require lezer.db :refer [conn])
+  (:require phel.pdo)
+  (:require phel.sql))
+
+(defn for-book [book]
+  (let [[query params] (sql/format
+                        {:select [:*]
+                         :from [:books_sessions]
+                         :where [:= :book_id (:id book)]})]
+    (-> (pdo/prepare conn query)
+        (pdo/execute params)
+        (pdo/fetch-all))))
+
+(defn create [fields]
+  (pdo/insert conn :books_sessions fields))