diff options
Diffstat (limited to 'bin')
| -rw-r--r-- | bin/fetch.fnl | 81 | ||||
| -rw-r--r-- | bin/serve.fnl | 8 |
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)) |