1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
|
(import-macros {: map : reduce} :lib.macro)
(tset package :path (.. package.path ";./vendor/lpeglj/?.lua"))
(local fennel (require :vendor.fennel))
(local array (require :lib.array))
(local cache (require :lib.cache))
(local {: must} (require :lib.utils))
(local artoftea (require :parser.artoftea))
(local batatcha (require :parser.batatcha))
(local chaekshop (require :parser.chaekshop))
(local clubcha (require :parser.clubcha))
(local daochai (require :parser.daochai))
(local gorkovchay (require :parser.gorkovchay))
(local ipuer (require :parser.ipuer))
(local kolokolnikovchai (require :parser.kolokolnikovchai))
(local moychay (require :parser.moychay))
(local ozchai (require :parser.ozchai))
(local suhexuan (require :parser.suhexuan))
(local tea108 (require :parser.tea108))
(local teaworkshop (require :parser.teaworkshop))
(local yoceramics (require :parser.yoceramics))
(when _G.unpack
(tset table :unpack _G.unpack))
(local db
(must (luna.db.open "file:var/db.sqlite?_journal=WAL&_sync=NORMAL&_txlock=immediate")))
(must
(luna.db.exec db "
PRAGMA foreign_keys=ON;
PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
CREATE VIRTUAL TABLE IF NOT EXISTS search USING fts5(title, fid, `table`);
CREATE TABLE IF NOT EXISTS products(
url TEXT NOT NULL PRIMARY KEY,
site TEXT NOT NULL,
title TEXT NOT NULL,
description TEXT NOT NULL,
year INT NOT NULL,
image TEXT NOT NULL,
price REAL NOT NULL,
weight REAL NOT NULL,
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
);
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
);
CREATE TABLE IF NOT EXISTS metrics(
fingerprint TEXT NOT NULL,
url TEXT NOT NULL,
creation_time DATETIME NOT NULL
);
CREATE TABLE IF NOT EXISTS cache(
key TEXT NOT NULL PRIMARY KEY,
value TEXT
);" []))
(fn now []
(os.date "%Y-%m-%d %H:%M:%S"))
(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 []))
(must (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 []))
(must (luna.db.exec-tx tx sql vars))))
(fn store-products [tx products]
(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
(array.flatten
(map (fn [_ v] v.tags) products))))
(store-product-tags tx products)
;; archive previous products
(local site (. products 1 :site))
(when site
(must (luna.db.exec-tx
tx
"UPDATE products
SET archived = true
WHERE site = ?
AND update_time < ?"
[site update-time]))))
(fn populate-search-table [db]
(local tx (must (luna.db.begin db)))
(must (luna.db.exec-tx tx "DELETE FROM search" []))
(must (luna.db.exec-tx tx "INSERT INTO search
SELECT title, url, 'products'
FROM products;" []))
(must (luna.db.commit tx)))
(each [_ parser (pairs [batatcha yoceramics daochai gorkovchay ozchai suhexuan
ipuer artoftea clubcha chaekshop kolokolnikovchai
tea108 teaworkshop moychay])]
(local products (parser.products))
(when (< 0 (# products))
;; replace with with-tx
(local tx (must (luna.db.begin db)))
(local (ok? err) (pcall store-products tx products))
(must (luna.db.commit tx))
(when (not ok?)
(print (string.format "Error fetching %s:\n%s"
parser.title err)))))
(cache.clear db "page:")
(populate-search-table db)
|
