order products by creation time

2 files changed, 57 insertions, 32 deletions

diff --git a/bin/fetch.fnl b/bin/fetch.fnl
index d3bbef9..b8d1334 100644
--- a/bin/fetch.fnl
+++ b/bin/fetch.fnl
@@ -46,6 +46,11 @@
     volume REAL NOT NULL,
     price_per REAL NOT NULL,
     archived BOOL NOT NULL,
+    update_time DATETIME NOT NULL
+  );
+
+  CREATE TABLE IF NOT EXISTS permanent_products(
+    url TEXT NOT NULL PRIMARY KEY,
     creation_time DATETIME NOT NULL
   );
 
@@ -115,33 +120,51 @@
     (must (luna.db.exec-tx tx sql vars))))
 
 (fn store-products [tx products]
-  (local sql
-    (.. "INSERT OR REPLACE INTO products"
-        "(url, site, title, description, year, image, price, weight, volume, price_per, archived, creation_time)"
-        " VALUES "
-        (array.join
-          (map (fn [_ _] "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
-               products)
-          ", ")))
-  (local creation-time (now))
-  (local vars
-    (reduce
-      (fn [_ product rest]
-        (array.concat rest
-          [(or product.url "")
-           (or product.site "")
-           (or product.title "")
-           (or product.description "")
-           (or product.year 0)
-           (or product.image "")
-           (or product.price 0)
-           (or product.weight 0)
-           (or product.volume 0)
-           (or product.price-per 0)
-           (or product.archived false)
-           creation-time]))
-      products []))
-  (must (luna.db.exec-tx tx sql vars))
+  (local update-time (now))
+
+  ;; store product
+  (let
+    [sql (.. "INSERT OR REPLACE INTO products"
+             "(url, site, title, description, year, image, price, weight, volume, price_per, archived, update_time)"
+             " VALUES "
+             (array.join
+               (map (fn [_ _] "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
+                    products)
+               ", "))
+     vars (reduce
+            (fn [_ product rest]
+              (array.concat rest
+                [(or product.url "")
+                 (or product.site "")
+                 (or product.title "")
+                 (or product.description "")
+                 (or product.year 0)
+                 (or product.image "")
+                 (or product.price 0)
+                 (or product.weight 0)
+                 (or product.volume 0)
+                 (or product.price-per 0)
+                 (or product.archived false)
+                 update-time]))
+            products
+            [])]
+    (must (luna.db.exec-tx tx sql vars)))
+
+  ;; store permenanent product data
+  (let
+    [sql (.. "INSERT OR IGNORE INTO permanent_products"
+             "(url, creation_time)"
+             " VALUES "
+             (array.join
+               (map (fn [_ _] "(?, ?)") products)
+               ", "))
+     vars (reduce
+            (fn [_ product rest]
+              (array.concat rest
+                [(or product.url "") update-time]))
+            products
+            [])]
+    (must (luna.db.exec-tx tx sql vars)))
 
   ;; store tags
   (store-tags tx (array.unique
@@ -157,8 +180,8 @@
            "UPDATE products
             SET    archived = true
             WHERE  site = ?
-            AND    creation_time < ?"
-           [site creation-time]))))
+            AND    update_time < ?"
+           [site update-time]))))
 
 (fn populate-search-table [db]
   (local tx (must (luna.db.begin db)))
diff --git a/bin/serve.fnl b/bin/serve.fnl
index 8793537..b05eaa5 100644
--- a/bin/serve.fnl
+++ b/bin/serve.fnl
@@ -210,9 +210,9 @@
 
 (fn latest-update-date []
   (. (must (luna.db.query db
-             "SELECT creation_time
+             "SELECT update_time
               FROM products
-              ORDER BY creation_TIME DESC
+              ORDER BY update_time DESC
               LIMIT 1" []))
      1 1))
 
@@ -309,13 +309,15 @@
        FROM search
        INNER JOIN products ON search.fid = products.url
        LEFT JOIN product_tags ON product_tags.product = products.url
+       LEFT JOIN permanent_products ON permanent_products.url = products.url
        WHERE search.`table` = 'products'
        AND   products.archived = false
        %s
        GROUP BY products.url
        ORDER BY %s
                 ROW_NUMBER() over (PARTITION BY products.site ORDER BY products.ROWID),
-                rank
+                rank,
+                permanent_products.creation_time DESC
        LIMIT 24 OFFSET ?" where-sql sort-sql)
      (array.concat query-args [(* (- page 1) 24)])))
    :total (if (< 0 (# total))