Move database functions to model modules

4 files changed, 98 insertions, 0 deletions

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))