From dfcdaab3a84389e610a57fa82ee0d3a216f4821d Mon Sep 17 00:00:00 2001 From: unwox Date: Thu, 10 Oct 2024 11:51:21 +0600 Subject: allow to specify several tags for a product (instead of one category) --- bin/fetch.fnl | 106 +++++++++++++++++++++++++++++++++++++++++++++------------- bin/serve.fnl | 56 ++++++++++++++++--------------- 2 files changed, 112 insertions(+), 50 deletions(-) (limited to 'bin') 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, '', '') AS \"title\", + "SELECT highlight(search, 0, '', '') 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"))) -- cgit v1.2.3