scratch/igc 573dbff123d: Add storing igc stats in SQLite

[Gerd Moellmann]

branch: scratch/igc
commit 573dbff123dd48d38af983495e9ffbd10a47f5a7
Author: Gerd Möllmann <gerd@gnu.org>
Commit: Gerd Möllmann <gerd@gnu.org>

    Add storing igc stats in SQLite
    
    * lisp/emacs-lisp/igc.el (toplevel): Require sqlite.
    (igc--sqlite): New var containing SQLite database.
    (igc-stop-collecting-stats): Handle sqlite case.
    (igc-stats-time-format): Change to include milliseconds.
    (igc--collect-stats-csv): Renamed from igc--collection-stats.
    (igc--collect-stats-sqlite):New function inserting into DB.
    (igc--open-sqlite): New function.
    (igc-start-collecting-stats): Allow choosing CSV or SQLite.
---
 lisp/emacs-lisp/igc.el | 64 +++++++++++++++++++++++++++++++++++++++++++-------
 1 file changed, 55 insertions(+), 9 deletions(-)

diff --git a/lisp/emacs-lisp/igc.el b/lisp/emacs-lisp/igc.el
index 392e8831548..4e7e14244e9 100644
--- a/lisp/emacs-lisp/igc.el
+++ b/lisp/emacs-lisp/igc.el
@@ -24,6 +24,7 @@
 ;;; Code:
 
 (require 'cl-lib)
+(require 'sqlite)
 
 (declare-function igc-info "igc.c")
 (declare-function igc--collect "igc.c")
@@ -249,17 +250,21 @@ Type \\`?' to see the mode's help."
 
 (defvar igc--collect-timer nil)
 (defvar igc--collect-file nil)
+(defvar igc--sqlite nil)
 
 ;;;###autoload
 (defun igc-stop-collecting-stats ()
   (interactive)
   (when igc--collect-timer
     (cancel-timer igc--collect-timer)
-    (setq igc--collect-timer nil)))
+    (setq igc--collect-timer nil)
+    (when (sqlitep igc--sqlite)
+      (sqlite-close igc--sqlite)
+      (setq igc--sqlite nil))))
 
-(defvar igc-stats-time-format "%T")
+(defvar igc-stats-time-format "%T.%3N")
 
-(defun igc--collect-stats ()
+(defun igc--collect-stats-csv ()
   (let ((buffer (get-file-buffer igc--collect-file)))
     (when buffer
       (with-current-buffer buffer
@@ -273,14 +278,55 @@ Type \\`?' to see the mode's help."
                                  time title n bytes))))
       (save-buffer))))
 
+(defun igc--collect-stats-sqlite ()
+  (let ((values (cl-loop with time = (format-time-string igc-stats-time-format)
+                         for (title n bytes) in (igc-info)
+                         collect (format "(\"%s\",\"%s\",\"%s\",\"%s\")"
+                                         time title n bytes))))
+    (with-sqlite-transaction igc--sqlite
+      (sqlite-execute igc--sqlite
+                      (format "INSERT INTO igc VALUES %s"
+                              (mapconcat #'identity values ","))))))
+
+(defun igc--open-sqlite (db)
+  (let ((con (or (sqlite-open db)
+                 (error "Error opening sqlite database %s" db))))
+    (with-sqlite-transaction con
+      (sqlite-execute con (concat "CREATE TABLE IF NOT EXISTS igc "
+                                  "(time, type, n, bytes)"))
+      ;; Which indices we need depends, and has to be seen with time.
+      (sqlite-execute con (concat "CREATE INDEX IF NOT EXISTS igc_time_index "
+                                  "ON igc (time)"))
+      (sqlite-execute con (concat "CREATE INDEX IF NOT EXISTS igc_type_index "
+                                  "ON igc (type)")))
+    con))
+
 ;;;###autoload
-(defun igc-start-collecting-stats (file secs)
-  "Start collecting statistics every SECS seconds."
-  (interactive "FOutput file: \nnInterval (seconds): ")
+(defun igc-start-collecting-stats (type file secs)
+  "Start collecting IGC statistics."
+  (interactive
+   (let* ((completion-ignore-case t)
+          (type (completing-read "Output to: " '("SQLite DB" "CSV") nil t))
+          (file (if (string-equal-ignore-case type "CSV")
+                    (read-file-name "CSV file: ")
+                  (unless (sqlite-available-p)
+                    (error "Your Emacs is not built with SQLite support"))
+                  (read-file-name "Sqlite database file: ")))
+          (secs (read-number "Interval seconds: " 10)))
+     (list (if (string-equal-ignore-case type "CSV") 'csv 'sqlite)
+           file secs)))
+  (unless (> secs 0)
+    (error "Invalid interval seconds"))
   (igc-stop-collecting-stats)
-  (setq igc--collect-file file)
-  (find-file-noselect file)
-  (setq igc--collect-timer (run-at-time nil secs #'igc--collect-stats)))
+  (cond ((eq type 'csv)
+         (setq igc--collect-file file)
+         (find-file-noselect file)
+         (setq igc--collect-timer
+               (run-at-time nil secs #'igc--collect-stats-csv)))
+        (t
+         (setq igc--sqlite (igc--open-sqlite file))
+         (setq igc--collect-timer
+               (run-at-time nil secs #'igc--collect-stats-sqlite)))))
 
 (provide 'igc)