63 lines
2.0 KiB
Fennel
63 lines
2.0 KiB
Fennel
(local sqlite (require :lsqlite3))
|
|
(local lume (require :lume))
|
|
(local { : view} (require :fennel))
|
|
|
|
(fn table-exists? [db]
|
|
(accumulate [ret false
|
|
r (db:nrows "select name from sqlite_master where type = 'table' and name= 'db_version'")]
|
|
(or ret true)))
|
|
|
|
(fn migrate [db serial statement]
|
|
(if (= (db:exec statement) 0)
|
|
(db:exec (.. "update db_version set serial = " serial))
|
|
(assert false (.. "db failed: " statement))))
|
|
|
|
(fn migrate-all [db]
|
|
(when (not (table-exists? db "db_version"))
|
|
(db:exec "create table db_version (serial integer)")
|
|
(db:exec "insert into db_version values (1)"))
|
|
(let [version (accumulate [v 0
|
|
r (db:nrows "select serial from db_version")]
|
|
r.serial)]
|
|
(when (< version 2)
|
|
(migrate db 2 "create table visits (url text, timestamp integer)"))
|
|
(when (< version 3)
|
|
(migrate db 3 "create table page_titles (url text primary key, title text)"))))
|
|
|
|
(fn visit [self url timestamp]
|
|
(let [s (self.db:prepare "insert into visits (url, timestamp) values (?,?)")]
|
|
(assert (= 0 (s:bind_values url timestamp)) (view [url timestamp]))
|
|
(s:step)
|
|
(s:reset)))
|
|
|
|
(fn title [self url title]
|
|
(let [s (self.db:prepare "insert into page_titles (url, title) values (?,?) on conflict(url) do update set title = excluded.title")]
|
|
(assert (= 0 (s:bind_values url title)) url)
|
|
(s:step)
|
|
(s:reset)))
|
|
|
|
(fn find [self term]
|
|
(let [s (self.db:prepare "select v.url,pt.title,v.timestamp from visits v left join page_titles pt on v.url = pt.url where instr(v.url, ?) >0")]
|
|
(assert (= 0 (s:bind_values term)))
|
|
(s:nrows)))
|
|
|
|
(fn find-distinct [self term]
|
|
(let [s (self.db:prepare "select distinct v.url,pt.title from visits v left join page_titles pt on v.url = pt.url where instr(v.url, ?) >0")]
|
|
(assert (= 0 (s:bind_values term)))
|
|
(s:nrows)))
|
|
|
|
(fn open [pathname]
|
|
(let [db (if pathname (sqlite.open pathname) (sqlite.open_memory))]
|
|
(migrate-all db)
|
|
{
|
|
: db
|
|
: visit
|
|
: title
|
|
: find
|
|
: find-distinct
|
|
}))
|
|
|
|
{
|
|
: open
|
|
}
|