-
-
Notifications
You must be signed in to change notification settings - Fork 3
/
Copy pathwallabag-db.el
254 lines (233 loc) · 11.4 KB
/
wallabag-db.el
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
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
;;; wallabag-db.el --- Emacs wallabag client db realted operations -*- lexical-binding: t; -*-
(require 'emacsql)
(require 'emacsql-sqlite)
(defcustom wallabag-db-connector (if (and (progn
(require 'emacsql-sqlite-builtin nil t)
(functionp 'emacsql-sqlite-builtin))
(functionp 'sqlite-open))
'sqlite-builtin
'sqlite)
"The database connector used by wallabag.
This must be set before `wallabag' is loaded. To use an alternative
connector you must install the respective package explicitly.
The default is `sqlite', which uses the `emacsql-sqlite' library
that is being maintained in the same repository as `emacsql'
itself.
If you are using Emacs 29, then the recommended connector is
`sqlite-builtin', which uses the new builtin support for SQLite.
You need to install the `emacsql-sqlite-builtin' package to use
this connector.
If you are using an older Emacs release, then the recommended
connector is `sqlite-module', which uses the module provided by
the `sqlite3' package. This is very similar to the previous
connector and the built-in support in Emacs 29 derives from this
module. You need to install the `emacsql-sqlite-module' package
to use this connector.
For the time being `libsqlite3' is still supported. Do not use
this, it is an older version of the `sqlite-module' connector
from before the connector and the package were renamed.
For the time being `sqlite3' is also supported. Do not use this.
This uses the third-party `emacsql-sqlite3' package, which uses
the official `sqlite3' cli tool, which is not intended
to be used like this. See https://nullprogram.com/blog/2014/02/06/."
:group 'wallabag
:type '(choice (const sqlite)
(const sqlite-builtin)
(const sqlite-module)
(const :tag "libsqlite3 (OBSOLETE)" libsqlite3)
(const :tag "sqlite3 (BROKEN)" sqlite3)))
(defcustom wallabag-db-file
(expand-file-name (concat user-emacs-directory ".cache/wallabag.sqlite"))
"Wallabag sqlite file for all entries."
:group 'wallabag
:type 'file)
(defvar wallabag-db-connection nil
"The EmacSQL database connection.")
(defconst wallabag-db-version 1)
(defvar wallabag-db-newp nil)
(defun wallabag-db--conn-fn ()
"Return the function for creating the database connection."
(cl-case wallabag-db-connector
(sqlite
(progn
(require 'emacsql-sqlite)
#'emacsql-sqlite))
(sqlite-builtin
(progn
(require 'emacsql-sqlite-builtin)
#'emacsql-sqlite-builtin))
(sqlite-module
(progn
(require 'emacsql-sqlite-module)
#'emacsql-sqlite-module))
(libsqlite3
(progn
(require 'emacsql-libsqlite3)
#'emacsql-libsqlite3))
(sqlite3
(progn
(require 'emacsql-sqlite3)
#'emacsql-sqlite3))))
(defun wallabag-db ()
"Connect or create database."
(unless (and wallabag-db-connection (emacsql-live-p wallabag-db-connection))
(unless (file-exists-p (concat user-emacs-directory ".cache/"))
(make-directory (concat user-emacs-directory ".cache/")))
(setq wallabag-db-connection (funcall (wallabag-db--conn-fn) wallabag-db-file))
;; create items table
(emacsql wallabag-db-connection [:create-table :if-not-exists items ([tag
is_archived
is_starred
user_name
user_email
user_id
tags
is_public
(id integer :primary-key)
uid
title
url
hashed_url
origin_url
given_url
hashed_given_url
archived_at
content
created_at
updated_at
published_at
published_by
starred_at
annotations
mimetype
language
reading_time
domain_name
preview_picture
http_status
headers
_links])])
;; create version table
(emacsql wallabag-db-connection [:create-table :if-not-exists version ([user-version])])
(let* ((db wallabag-db-connection)
(version (wallabag-db-maybe-update db wallabag-db-version)))
(cond
((> version wallabag-db-version)
(emacsql-close db)
(user-error
"The wallabag database was created with a newer wallabag version. %s"
"You need to update the Anki package."))
((< version wallabag-db-version)
(emacsql-close db)
(error "BUG: The wallabag database scheme changed %s"
"and there is no upgrade path")))))
wallabag-db-connection)
(defun wallabag-db-maybe-update (db version)
(if (emacsql-live-p db)
(cond ((eq version 1)
(wallabag-db-set-version db (setq version 1))
(message "Wallabag database is version 1...done"))
((eq version 2)
(message "Upgrading Wallabag database from version 2 to 3...")
(wallabag-db-set-version db (setq version 3))
(message "Upgrading Wallabag database from version 2 to 3...done"))
(t (setq version wallabag-db-version))))
version)
(defun wallabag-db-get-version (db)
(caar (emacsql db [:select user-version :from version])))
(defun wallabag-db-set-version (db dbv)
"Insert user-version if not exists."
(cl-assert (integerp dbv))
(if (wallabag-db-get-version db)
(emacsql db `[:update version :set (= user-version ,dbv)])
(emacsql db `[:insert :into version :values ([(,@wallabag-db-version)])])))
(defun wallabag-db-sql (sql &rest args)
(if (stringp sql)
(emacsql (wallabag-db) (apply #'format sql args))
(apply #'emacsql (wallabag-db) sql args)))
;;; database operation
;; select
(defun wallabag-db-select (&rest properties)
(let ((candidates)
(sql (plist-get properties :sql))
(id (plist-get properties :id))
(title (plist-get properties :title))
(url (plist-get properties :url))
(next-entry (plist-get properties :next-entry))
(previous-entry (plist-get properties :previous-entry)))
(setq candidates (mapcar (lambda(x)
(cl-pairlis
'(tag
is_archived
is_starred
user_name
user_email
user_id
tags
is_public
id
uid
title
url
hashed_url
origin_url
given_url
hashed_given_url
archived_at
content
created_at
updated_at
published_at
published_by
starred_at
annotations
mimetype
language
reading_time
domain_name
preview_picture
http_status
headers
_links)
x))
(cond
(id (wallabag-db-sql `[:select * :from items :where (= id ,id)]))
(title (wallabag-db-sql `[:select * :from items :where (= title ,title)]))
(url (wallabag-db-sql `[:select * :from items :where (= url ,url)]))
(previous-entry (wallabag-db-sql `[:select * :from items :where (> id ,previous-entry) :limit 1]) )
(next-entry (wallabag-db-sql `[:select * :from items :where (< id ,next-entry) :order-by (desc id) :limit 1]) )
(t (wallabag-db-sql (or sql [:select * :from items]))))))
(if candidates
candidates
;; (message "No items in wallabag database, try to update with 'u'.")
(setq wallabag-db-newp t)
nil)))
;; insert
(defun wallabag-db-insert (entries)
(let ((entries
(cl-loop for entry in entries collect
(cl-map 'array #'identity (mapcar 'cdr entry)))))
(wallabag-db-sql `[:insert :or :ignore :into items
:values ,entries])))
;; delete
(defun wallabag-db-delete (ids)
(cond ((vectorp ids)
(wallabag-db-sql `[:delete :from items
:where (in id ,ids)]) )
((numberp ids) (wallabag-db-sql `[:delete :from items
:where (= id ,ids)]))
(t nil)))
;; update
(defmacro wallabag-db-update (field)
`(defun ,(intern (format "wallabag-db-update-%s" field)) (id new)
(wallabag-db-sql (vector ':update 'items
':set (list '= ',(intern field) (vector new ))
':where (list '= 'id id) ))))
(wallabag-db-update "title")
(wallabag-db-update "tag")
(wallabag-db-update "tags")
(wallabag-db-update "content")
(wallabag-db-update "is_archived")
(wallabag-db-update "is_starred")
(wallabag-db-update "origin_url")
(provide 'wallabag-db)