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
|
(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 ozchai (require :parser.ozchai))
(local ipuer (require :parser.ipuer))
(local artoftea (require :parser.artoftea))
(local clubcha (require :parser.clubcha))
(local chaekshop (require :parser.chaekshop))
(local {: must} (require :lib.utils))
(local db (must (luna.db.open "file:var/db.sqlite?_journal=WAL&_sync=NORMAL")))
(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,
price_per REAL 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
);
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]
(when (< 0 (# products))
(local sql
(.. "INSERT OR IGNORE INTO products VALUES "
(array.join
(map (fn [_ _] "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
products)
", ")))
(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.price-per 0)
false
(now)]))
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)))
(fn populate-search-table []
(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)))
;; replace with with-tx
(local tx (must (luna.db.begin db)))
(must (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)))
(cache.clear-tx tx "page:")
(must (luna.db.commit tx))
(populate-search-table)
|
