试试用 stream 处理 sqlite 数据

Emacs-Lisp
1

Emacs 29 提供了 sqlite-select 的 set 参数处理数据行很多的情况，比如：

(let ((db (sqlite-open)))
  (sqlite-execute db "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
  (sqlite-execute db "INSERT INTO users (name) VALUES (?), (?), (?)"
                  (list "alice" "bob" "calb"))
  (let ((statement (sqlite-select db "SELECT * FROM users" nil 'set))
        rows)
    (while-let ((row (sqlite-next statement)))
      (push row rows))
    (nreverse rows)))
;; => ((1 "alice") (2 "bob") (3 "calb"))

除了这种用 loop 的方式，还可以尝试用 stream 的方式，比如试用 seq.el 作者的 stream.el 库，然后用 Emacs 内置的 seq-* 来操作，对熟悉 seq.el 的用户可能比较方便。比如上面的例子：

(defun my-stream-of-sqlite (statement)
  (stream-make
   (let ((row (sqlite-next statement)))
     (when row
       (cons row (my-stream-of-sqlite statement))))))

(let ((db (sqlite-open)))
  (sqlite-execute db "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
  (sqlite-execute db "INSERT INTO users (name) VALUES (?), (?), (?)"
                  (list "alice" "bob" "calb"))
  (let ((statement (sqlite-select db "SELECT * FROM users" nil 'set)))
    (seq-into (my-stream-of-sqlite statement) 'list)))
;; => ((1 "alice") (2 "bob") (3 "calb"))

注意 stream 会存储已经被求过值的元素，比如下面试用了 2 次 stream，也只有一次 SQL 查询。

(let ((db (sqlite-open)))
  ;; 创建 packages 表
  (sqlite-execute db "CREATE TABLE packages (id INTEGER PRIMARY KEY, name TEXT UNIQUE)")

  ;; 录入数据
  (mapc (lambda (pkg)
          (sqlite-execute
           db
           "INSERT INTO packages (name) VALUES (?)"
           (vector (symbol-name (car pkg)))))
        package-alist)

  ;; 查询表
  (let ((stream (my-stream-of-sqlite (sqlite-select db "SELECT * FROM packages" nil 'set))))
    (list :seq
          (seq-find (pcase-lambda (`(,_ ,name)) (string= name "seq"))
                    stream)
          :one-or-two
          (seq-into
           (seq-map #'cadr
                    (seq-filter (pcase-lambda (`(,_ ,name)) (<= (length name) 2))
                                stream))
           'vector))))
;; => (:seq (60 "seq") :one-or-two ["xr" "sx" "sl" "s" "ov" "lv" "kv" "ht" "f"])
3 个赞