allow to specify several tags for a product (instead of one category)

2 files changed, 112 insertions, 50 deletions

diff --git a/bin/fetch.fnl b/bin/fetch.fnl
index 3dcaded..c0f07c6 100644
--- a/bin/fetch.fnl
+++ b/bin/fetch.fnl
@@ -2,6 +2,8 @@
 
 (tset package :path (.. package.path ";./vendor/lpeglj/?.lua"))
 
+(local fennel (require :vendor.fennel))
+
 (local array (require :lib.array))
 (local ozchai (require :parser.ozchai))
 (local ipuer (require :parser.ipuer))
@@ -15,67 +17,125 @@
   PRAGMA journal_mode=WAL;
   PRAGMA synchronous=NORMAL;
 
-  CREATE VIRTUAL TABLE IF NOT EXISTS search USING fts5(name, fid, `table`);
+  CREATE VIRTUAL TABLE IF NOT EXISTS search USING fts5(title, fid, `table`);
 
-  CREATE TABLE IF NOT EXISTS products (
-    id TEXT NOT NULL PRIMARY KEY,
+  CREATE TABLE IF NOT EXISTS products(
+    url TEXT NOT NULL PRIMARY KEY,
     site TEXT NOT NULL,
-    category TEXT NOT NULL,
     title TEXT NOT NULL,
     description TEXT NOT NULL,
     year INT NOT NULL,
     image TEXT NOT NULL,
-    url TEXT NOT NULL,
     price REAL NOT NULL,
     weight REAL NOT NULL,
     price_per REAL NOT NULL,
-    misc TEXT NOT NULL,
+    archived BOOL NOT NULL,
+    creation_time DATETIME NOT NULL
+  );
+
+  CREATE TABLE IF NOT EXISTS product_tags(
+    product TEXT NOT NULL REFERENCES products(url),
+    tag DATETIME NOT NULL REFERENCES tags(title)
+  );
+
+  CREATE UNIQUE INDEX IF NOT EXISTS product_tags_idx ON product_tags(product, tag);
+
+  CREATE TABLE IF NOT EXISTS tags(
+    title TEXT NOT NULL PRIMARY KEY,
     creation_time DATETIME NOT NULL
   );" [])
 
 (fn now []
   (os.date "%Y-%m-%d %H:%M:%S"))
 
-(fn store-products [products]
+(fn store-tags [tx tags]
+  (when (< 0 (# tags))
+    (local sql
+      (.. "INSERT OR IGNORE INTO tags VALUES "
+          (array.join
+            (map (fn [_ _] "(?, ?)") tags)
+            ", ")))
+    (local vars
+      (reduce
+        (fn [_ tag rest] (array.concat rest [tag (now)]))
+        tags []))
+    (luna.db.exec-tx tx sql vars)))
+
+(fn store-product-tags [tx products]
+  (when (< 0 (# products))
+    ;; flatten product tags
+    (local tags
+      (reduce
+        (fn [_ product res]
+          (array.concat
+            res
+            (map
+              (fn [_ tag] [product.url tag])
+              product.tags)))
+        products
+        []))
+    (local sql
+      (.. "INSERT OR IGNORE INTO product_tags VALUES "
+          (array.join
+            (map (fn [_ _] "(?, ?)")
+                  tags)
+            ", ")))
+    (local vars
+     (reduce
+       (fn [_ tags rest]
+         (array.concat rest tags))
+       tags []))
+    (luna.db.exec-tx tx sql vars)))
+
+(fn store-products [tx products]
   (when (< 0 (# products))
     (local sql
-      (.. "INSERT OR REPLACE INTO products VALUES "
-          (table.concat
-            (map (fn [_ _]
-                    "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
+      (.. "INSERT OR IGNORE INTO products VALUES "
+          (array.join
+            (map (fn [_ _] "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
                   products)
-            ",")))
+            ", ")))
     (local vars
       (reduce
         (fn [_ product rest]
           (array.concat rest
-            [(or product.id "")
+            [(or product.url "")
              (or product.site "")
-             (or product.category "")
              (or product.title "")
              (or product.description "")
              (or product.year 0)
              (or product.image "")
-             (or product.url "")
              (or product.price 0)
              (or product.weight 0)
              (or product.price-per 0)
-             (or product.misc "")
+             false
              (now)]))
         products []))
-    (luna.db.exec db sql vars)))
+    (luna.db.exec-tx tx sql vars)
+
+    ;; store tags
+    (store-tags tx (array.unique
+                    (array.flatten
+                     (map (fn [_ v] v.tags) products))))
+    (store-product-tags tx products)))
 
 (fn populate-search-table []
   (local tx (luna.db.begin db))
   (luna.db.exec-tx tx "DELETE FROM search" [])
   (luna.db.exec-tx tx "INSERT INTO search
-                       SELECT title, id, 'products'
+                       SELECT title, url, 'products'
                        FROM products;" [])
   (luna.db.commit tx))
 
-(store-products (chaekshop.products))
-(store-products (clubcha.products))
-(store-products (artoftea.products))
-(store-products (ipuer.products))
-(store-products (ozchai.products))
+;; replace with with-tx
+(local tx (luna.db.begin db))
+(luna.db.exec-tx tx "DELETE FROM product_tags;" [])
+(each [_ products (pairs [chaekshop.products
+                          clubcha.products
+                          artoftea.products
+                          ipuer.products
+                          ozchai.products])]
+  (store-products tx (products)))
+(luna.db.commit tx)
+
 (populate-search-table)
diff --git a/bin/serve.fnl b/bin/serve.fnl
index bec5a94..d4dbf4c 100644
--- a/bin/serve.fnl
+++ b/bin/serve.fnl
@@ -105,9 +105,23 @@
      [:div {} "Всего результатов: " [:strong {} (string.format "%d" total)]]]
     ""))
 
-(fn base-template [query sort page total ...]
+(fn aside-template [query tags paginator]
+  [:aside {:class "aside"}
+   [:div {:class "aside-content"}
+    (if (~= query "")
+      [:a {:href "/" :style "display: block;"}
+       [:img {:class "logo" :src "static/logo.svg"
+              :alt "Логотип meicha.ru" :title "Логотип meicha.ru"}]]
+       [:img {:class "logo" :src "static/logo.svg"
+              :alt "Логотип meicha.ru" :title "Логотип meicha.ru"}])
+    [:form {:class "form"}
+     [:input {:type :search :name :query :value query
+              :autofocus true :placeholder "Поисковый запрос"}]
+     [:button {:type :submit} "Искать"]]
+    paginator]])
+
+(fn base-template [query tags page total ...]
   (local paginator (paginator-template query page 48 total))
-
   [:html {:lang "en"}
    [:head {}
     [:meta {:charset "UTF-8"}]
@@ -116,19 +130,7 @@
    [:body {}
     [:div {:class "container"}
      [:div {:class "content"}
-      [:aside {:class "aside"}
-       [:div {:class "aside-content"}
-        (if (~= query "")
-          [:a {:href "/" :style "display: block;"}
-           [:img {:class "logo" :src "static/logo.svg"
-                  :alt "Логотип meicha.ru" :title "Логотип meicha.ru"}]]
-           [:img {:class "logo" :src "static/logo.svg"
-                  :alt "Логотип meicha.ru" :title "Логотип meicha.ru"}])
-        [:form {:class "form"}
-         [:input {:type :search :name :query :value query
-                  :autofocus true :placeholder "Поисковый запрос"}]
-         [:button {:type :submit} "Искать"]]
-        paginator]]
+      (aside-template query tags paginator)
       [:section {}
        [:div {:class "list"} ...]
        [:footer {} paginator]]]]]])
@@ -137,8 +139,7 @@
   (assert (< 0 limit) "limit must be > 0")
   (luna.db.query*
     db
-    "SELECT id,
-            site,
+    "SELECT site,
             title,
             description,
             image,
@@ -167,14 +168,13 @@
     "SELECT count(*)
      FROM search
      WHERE search.`table` = 'products'
-     AND search.name MATCH ?"
+     AND search.title MATCH ?"
     [query]))
 
   {:results
     (luna.db.query*
         db
-        "SELECT products.id,
-                highlight(search, 0, '<i>', '</i>') AS \"title\",
+        "SELECT highlight(search, 0, '<i>', '</i>') AS \"title\",
                 products.site,
                 products.description,
                 products.image,
@@ -182,11 +182,13 @@
                 products.price,
                 products.weight,
                 products.price_per AS \"price-per\",
-                products.year
+                products.year,
+                products.archived,
+                products.creation_time AS \"creation-time\"
          FROM search
-         INNER JOIN products ON search.fid = products.id
+         INNER JOIN products ON search.fid = products.url
          WHERE search.`table` = 'products'
-         AND search.name MATCH ?
+         AND search.title MATCH ?
          ORDER BY rank
          LIMIT 48 OFFSET ?"
         [query (* (- page 1) 48)])
@@ -198,17 +200,17 @@
   (if (= path "/")
     (let [headers {:content-type "text/html"}
           page (or (tonumber query.page) 1)
-          search (or query.query "")
-          sort "ASC"
+          search (str.trim (or query.query ""))
+          tags (or query.tags [])
           {: results : total}
             (if (~= "" search)
-              (query-products page search sort)
+              (query-products page search)
               {:total 48 :results (random-products 48)})]
       (values
         200 headers
         (html.render
           (base-template
-            search sort page total
+            search tags page total
             (table.unpack (map #(item-template $2) results)))
           true)))
     (values 404 {} "not found")))