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

1 files changed, 83 insertions, 23 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)