[elpa] externals/rcirc-sqlite 1e2aa4324d 2/6: Initial import

[ELPA Syncer]

branch: externals/rcirc-sqlite
commit 1e2aa4324dd4bdb443c6122537d4adc3ec1346f8
Author: Matto Fransen <matto@matto.nl>
Commit: Matto Fransen <matto@matto.nl>

    Initial import
---
 README.md       |   7 -
 README.org      | 400 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 rcirc-sqlite.el | 363 ++++++++++++++++++++++++++++++++++++++++++++++++++
 3 files changed, 763 insertions(+), 7 deletions(-)

diff --git a/README.md b/README.md
deleted file mode 100644
index db646d19ed..0000000000
--- a/README.md
+++ /dev/null
@@ -1,7 +0,0 @@
-# rcirc-sqlite
-
-rcirc logging in SQLite
-
-rcirc is a default, simple IRC client in Emacs. rcirc can be enable to log the 
IRC chats, it logs to files.
-
-This minor mode, when activated, diverts the rcirc logs to a SQLite database.
\ No newline at end of file
diff --git a/README.org b/README.org
new file mode 100644
index 0000000000..192fed3688
--- /dev/null
+++ b/README.org
@@ -0,0 +1,400 @@
+#+TITLE:rcirc logging in SQLite
+#+AUTHOR: Matto Fransen
+#+TEXINFO_DIR_CATEGORY: Emacs misc features
+#+TEXINFO_DIR_TITLE: rcirc-sqlite: (rcirc-sqlite) 
+#+TEXINFO_DIR_DESC: rcirc logging in SQLite
+#+EXPORT_FILE_NAME: rcirc-sqlite.texi
+#+TEXINFO_FILENAME: rcirc-sqlite.info
+
+* Introduction
+:PROPERTIES:
+:ID:       c8cd5551-bf7c-11ee-aa8a-5cff350aad6a
+:END:
+
+~rcirc~ is a default, simple IRC client in Emacs. rcirc can be enable to
+log the IRC chats, it logs to files.
+
+This minor mode, when activated, diverts the rcirc logs to a SQLite
+database.
+
+It also comes with some functionality to query the SQLite database.
+
+* Requirements
+:PROPERTIES:
+:ID:       f1a62fea-bc80-11ee-a315-5cff350aad6a
+:END:
+
+~rcirc-sqlite~ relies on the capability of Emacs to work together
+with SQLite. 
+
+- Emacs must be compiled with this capability.
+- SQLite 3 must be installed on the system.
+
+* Installation and activation
+:PROPERTIES:
+:ID:       fac7e092-bc80-11ee-a315-5cff350aad6a
+:END:
+#+findex: Installation
+
+** Installation
+Create a directory for the package.
+
+#+begin_src sh
+mkdir ~/.emacs.d/manualpackages
+#+end_src
+
+At this directory as a load path to your init file:
+
+#+begin_src emacs-lisp
+(add-to-list 'load-path "~/.emacs.d/manualpackages")
+#+end_src
+
+Re-evaluate your init file or restart Emacs, whatever you prefer.
+
+[[id:f1a62fea-bc80-11ee-a315-5cff350aad6a][Requirements]] describes the 
requirements.
+
+** Activation
+:PROPERTIES:
+:ID:       fcca2f42-bc80-11ee-a315-5cff350aad6a
+:END:
+#+findex: Activation
+#+findex: rcirc-sqlite-log-mode
+
+- Command: rcirc-sqlite-log-mode ::
+  Activates or deactivates ~rcirc-sqlite~.
+
+Issue the command ~rcirc-sqlite-log-mode~ to manually start
+~rcirc-sqlite~. This command toggles between activation
+and deactivation of ~rcirc-sqlite~. 
+
+To start ~rcirc-sqlite~ automatically when ~rcirc~
+is started, add the following to your init file:
+
+#+begin_src emacs-lisp
+(require 'rcirc-sqlite)
+(add-hook 'rcirc-mode-hook #'rcirc-sqlite-log-mode)
+#+end_src
+
+While ~rcirc-sqlite~ is activated, ~rcirc~ will no longer
+write the logs to files, until ~rcirc-sqlite~ is deactivated.
+Deactivate ~rcirc-sqlite~ using the command
+~rcirc-sqlite-log-mode~ again.
+
+The logging must be enabled in ~rcirc~.
+To do this, add for example the following to your init file:
+
+#+begin_src emacs-lisp
+(setq rcirc-log-flag t)
+#+end_src
+
+* Customization
+:PROPERTIES:
+:ID:       f4517d9c-bc80-11ee-a315-5cff350aad6a
+:END:
+
+To customize ~rcirc-sqlite~ run ~M-x customize-group rcirc-sqlite~,
+or use ~(setopt ...)~ in your init file, like ~/.emacs~.
+For example:
+
+#+begin_src emacs-lisp
+(setopt rcirc-sqlite-rows 100)
+#+end_src
+
+The user may custimize the following options.
+
+- User Option: rcirc-sqlite-database ::
+  The file in which SQLite stores the database.
+
+#+vindex: rcirc-sqlite-database
+
+The file in which SQLite stores the database can be customized.
+
+The default file is ~ricirc-log.db~, located in the default Emacs
+directory (e.g., ~~/.emacs.d/ricirc-log.db~).
+
+Set this option to use a different file.
+
+- User Option: rcirc-sqlite-time-format ::
+  The format for the date and time in the buffer ~*rcirc log*~.
+
+#+vindex: rcirc-sqlite-time-format
+
+The variable ~rcirc-sqlite-time-format~ describes the date and
+time format which is used when displaying the chat messages.
+
+This variable only influences how the date and time is formatted
+in the buffer ~*rcirc log*~.
+
+The default format is ~%Y-%m-%d %H:%M"~.
+
+Set this option to use a different time format in the buffer
+~*rcirc log*~.
+
+- User Option: rcirc-sqlite-rows ::
+  The default maximum number of rows when viewing logs.
+
+#+vindex: rcirc-sqlite-rows
+
+The variable ~rcirc-sqlite-rows~ describes the maximum number
+of rows to display when viewing the chat logs.
+
+This only affects when viewing the chat logs. Search results are
+always shown unabridged.
+
+[[id:03ec6364-bc81-11ee-a315-5cff350aad6a][View the logs]] describes the 
command to view the logs.
+
+The default value is 200 rows.
+
+Set this option to change the default number of lines.
+ 
+- User Option: rcirc-sqlite-channel-column-width ::
+  The default column width of the channel column in the buffer
+  ~*rcirc log*~.
+
+#+vindex: rcirc-sqlite-channel-column-width
+
+The variable ~rcirc-sqlite-channel-column-width~ describes the
+default width of the column that displays the channel names.
+
+The default value is 40 chars.
+
+Change this option to let the channel names be shown in a smaller or
+wider column in the buffer ~*rcirc log*~.
+
+[[id:126b1bec-bc81-11ee-a315-5cff350aad6a][The buffer ~*rcirc log*~]] has more 
information on this buffer.
+
+* Commands
+:PROPERTIES:
+:ID:       016dc6b9-bc81-11ee-a315-5cff350aad6a
+:END:
+** Summery of the commands
+#+vindex: command summary
+
+- ~M-x circ-sqlite-view-log~: display the logs.
+- ~M-x rcirc-sqlite-text-search~ perform full text search in the logs.
+- ~M-x rcirc-sqlite-stats~ displays some stats.
+
+** View the logs
+:PROPERTIES:
+:ID:       03ec6364-bc81-11ee-a315-5cff350aad6a
+:END:
+#+findex: rcirc-sqlite-view-log
+
+- Function: rcirc-sqlite-view-log channel &optional unlimited offset limit ::
+  Display the logs in a new buffer.
+
+Issue the command ~M-x rcirc-sqlite-view-log~ to view the logs
+of a specific channel. Default this command shows the last 200
+lines. This number can be changed by setting the variable
+~rcirc-sqlite-rows~. 
+
+This command prompts the user for the channel and provides a list
+of available channels. Choose a channel using completion. 
+Choose the option ~All channels~ to show the last 200 lines of the
+chat log of *all* channels.
+
+Next, the user is prompted for a month, choose a month using
+completion.  When a month is choosen, the last 200 lines the chat log
+from that month are shown.  Choose the option ~Anytime~ or the
+latest month to get the most recent 200 lines.
+
+The buffer ~*rcirc log*~ displays the chat logs.
+
+@subheading Optional arguments
+
+This function has three optional arguments, ~unlimited~,
+~offset~, and ~limit~.
+
+- ~unlimited~
+  When non nil, ~rcirc-sqlite-view-log~ will show *all*
+  log lines in the database of the channel.
+- ~offset~ and ~limit~
+  Use ~offset~ and ~limit~ to select a number of lines from
+  the log lines in the database of the channel.
+
+** Full text search and load the result in a buffer
+#+findex: rcirc-sqlite-text-search
+
+- Function: rcirc-sqlite-text-search query channel nick ::
+  Perform full text search.
+
+Issue the command ~M-x rcirc-sqlite-text-search~ to perform
+full text search in the logs.
+
+When this command is issued:
+
+- The user is prompted for a search string.
+- The user is prompted to choose a channel (through completion).
+- The user is prompted to choose a specific month (through completion).
+- The user is prompted to choose a nick (through completion).
+
+When a channel is chosen, the search is performed within the
+chat logs of that specific channel. Choose ~All channels~ to
+search everywhere.
+
+When a month is chosen, the search is performed within the
+messages that were send in that specific month. Choose ~Anytime~
+to search everywhere.
+
+When a nick is chosen, the search is performed within the
+chats of that specific nick. Choose ~All nicks~ to search
+independent of the sender.
+
+The search string is used to do a full text search in the SQLite
+database. When the search string is ~foo~, chat messages
+containing the word ~foo~ will be found, but chat messages
+containing the word ~foobar~ will not be found.
+
+To search for both ~foo~ and ~foobar~, use the search
+string ~foo*~.
+
+Likewise, to search for URLs, use something like ~"http://*"~ or
+~"https://*"~ as search string, or for example
+~"gopher://*"~. Because of the colon (~:~), the double
+quotes (~"~) here are required.
+
+For more formatting of the search see the chapter ~Full-text
+Query Syntax} of the SQLite documentation (see
+~https://www.sqlite.org/fts5.html~).
+
+The buffer ~*rcirc log*~ displays the search results.
+
+** Stats
+:PROPERTIES:
+:ID:       0d1bc484-bc81-11ee-a315-5cff350aad6a
+:END:
+#+findex: rcirc-sqlite-stats
+
+- Function: rcirc-sqlite-stats nick ::
+  Create overview with some stats
+
+Issue the command ~M-x rcirc-sqlite-stats~ to get an overview
+of the number of rows (lines) in the database.
+
+The user is prompted for a nick. Choose a nick through completion.
+
+When a nick is chosen, the buffer ~*rcirc log*~ is opened where
+each channel with one or more chat messages from that nick is listed,
+together with the number of chat messages from that nick.
+
+When ~All nicks~ is chosen, the buffer shows the row count
+for each channel in the database.
+
+When ~Nicks per channel~ is chosen, the buffer shows for
+each channel the number of uniq nicks.
+
+When ~Channels per nick~ is chosen, the buffer shows for
+each nick the number of channels with messages from this nick.
+
+* The buffer ~*rcirc log*~
+:PROPERTIES:
+:ID:       126b1bec-bc81-11ee-a315-5cff350aad6a
+:END:
+
+The buffer ~*rcirc log*~ is used to show the output of the
+database queries. This buffer uses a derived mode from the
+~tabulated-list-mode~.  The default key bindings of the
+~tabulated-list-mode~ are available in this buffer.
+
+Some key binding examples:
+
+- Key: S (Sort) ::
+  Sort the buffer according to the values of the column of point.
+  
+  Use a numeric prefix argument N to sort the buffer according to the
+  values of the N-th column from point. Repeat to sort in the alternate
+  order (ascending or descending).
+
+- Key: @} ::
+  Widen the current column by N (the prefix numeric
+  argument) characters,
+
+- Key: @{ ::  
+  Narrow the current column by N (the prefix numeric
+  argument) characters.
+
+- Key: SPACE ::
+  Scroll the buffer up.
+
+- Key: BACKSPACE ::
+  Scroll the buffer down.
+
+- Key: n ::
+  Move down one screen line (next line).
+
+- Key: p ::
+  Move up one screen line (previous line).
+
+- Key: q ::
+  Close the buffer.
+
+* Inner workings
+:PROPERTIES:
+:ID:       179764f3-bc81-11ee-a315-5cff350aad6a
+:END:
+#+findex: rcirc-log-write
+#+findex: rcirc-sqlite-store-log
+
+~rcirc~ caches the IRC messages in a list, and periodically
+writes the contents of this cache to the log files.
+~rcirc-sqlite~ collects the contents of this cache.
+
+~rcirc-sqlite~ overrides the ~rcirc~-function
+~rcirc-log-write~ with the ~rcirc-sqlite~-function
+~rcirc-sqlite-store-log~ for this.
+
+To be able to easy parse the timestamp, ~rcirc-sqlite~ changes
+the ~rcirc-log-time-format~. It does this by advising around the
+~rcirc~-function rcirc-log.
+
+** Delay
+
+There is some delay between the arrival of chat messages in the chat
+buffer and the storage of the logs in the database.
+
+~rcirc~ uses the auto-save functionality to trigger the flushing
+of the cache to the log file. Hence, the storage of the chat logs to
+the SQLite database by ~rcirc-sqlite~ is also triggered by the
+auto-save functionality.
+
+* Database schema
+:PROPERTIES:
+:ID:       1a4a9a49-bc81-11ee-a315-5cff350aad6a
+:END:
+
+The SQLite database is created at the first time ~rcirc-sqlite~
+flushes the cache. The SQLite database is populated with a virtual
+table, using the SQLite FTS5 Extension. The schema of this table has
+the following fields.
+
+- channel:
+  This is the channel name in the format ~rcirc~ uses to
+  determine the log file. This format is
+  ~channelname~@@~servername~.log, for example
+  ~#rcirc@@LiberaChat.log~.
+- time:
+  The timestamp, stored in the unix timestamp format.
+- nick:
+  The nick name of the sender.
+- message:
+  The actual chat message.
+
+
+* GNU Free Documentation License
+:PROPERTIES:
+  :APPENDIX: t
+  :END:
+
+  #+INCLUDE: fdl.org
+
+* Command and Function Index
+:PROPERTIES:
+:DESCRIPTION: Command names and some internal functions.
+:INDEX:    fn
+:END:
+
+* Variable Index
+:PROPERTIES:
+:DESCRIPTION: Variables mentioned in the manual.
+:INDEX:    vr
+:END:
diff --git a/rcirc-sqlite.el b/rcirc-sqlite.el
new file mode 100644
index 0000000000..dec08ec7da
--- /dev/null
+++ b/rcirc-sqlite.el
@@ -0,0 +1,363 @@
+;;; rcirc-sqlite.el --- rcirc logging in SQLite      -*- lexical-binding: t; 
-*-
+
+;; Copyright (C) 2024  Matto Fransen
+
+;; Author: Matto Fransen <matto@matto.nl>
+;; Version: 0.1
+;; Keywords: comm
+;; Package-Requires: ((emacs "30.0"))
+
+;; This program is free software; you can redistribute it and/or modify
+;; it under the terms of the GNU General Public License as published by
+;; the Free Software Foundation, either version 3 of the License, or
+;; (at your option) any later version.
+
+;; This program is distributed in the hope that it will be useful,
+;; but WITHOUT ANY WARRANTY; without even the implied warranty of
+;; MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+;; GNU General Public License for more details.
+
+;; You should have received a copy of the GNU General Public License
+;; along with this program.  If not, see <http://www.gnu.org/licenses/>.
+
+;;; Commentary:
+
+;; Stores the logging from rcirc into a SQLite database.
+;; rcirc-sqlite overrides the rcirc-log-write function.
+;;
+;; rcirc is a default, simple IRC client in Emacs.
+;; rcirc can be enable to log the IRC chats, it logs to files.
+;; This minor mode, when activated, diverts the rcirc
+;; logs to a SQLite database.
+;;
+;;;; Usage:
+;;
+;; To toggle the mode:
+;;   M-x rcirc-sqlite-log-mode
+;; To start rcirc-sqlite automatically in `rcirc', add the following
+;; to your init file:
+;;   (add-hook 'rcirc-mode-hook #'rcirc-sqlite-log-mode)
+;;
+;;;; Customization:
+;;
+;; To customize the file to hold the SQLite database:
+;;   M-x customize-group rcirc-sqlite RET
+
+;;; Code:
+
+(defgroup rcirc-sqlite nil
+  "Rcirc logging in SQLite."
+  :prefix "rcirc-sqlite-"
+  :link '(custom-manual "(rcirc-sqlite)Top")
+  :group 'rcirc)
+
+(defcustom rcirc-sqlite-database
+  (locate-user-emacs-file "rcirc-log.db")
+  "Path to the SQLite database used for logging messages."
+  :type 'file)
+(defcustom rcirc-sqlite-time-format "%Y-%m-%d %H:%M"
+  "Describes how timestamps are displayed in the log buffer."
+  :type 'string)
+
+(defcustom rcirc-sqlite-rows "200"
+  "Default maximum number of lines displayed in view log."
+  :type 'string)
+
+(defcustom rcirc-sqlite-channel-column-width 30
+  "Default width of the column displaying the channelname."
+  :type 'natnum)
+
+(defvar rcirc-sqlite--conn nil
+  "Variable to store the current database connection.
+If non-nil, it will designate a object satisfying `sqlitep',
+otherwise no connection has been opened.")
+
+(defun rcirc-sqlite--conn ()
+  "Return an open database connection, or open one up."
+  (or rcirc-sqlite--conn
+      (setq rcirc-sqlite--conn
+            (let ((conn (sqlite-open rcirc-sqlite-database)))
+              (and (sqlitep conn) conn)))
+      (error "Failed to open a database connection")))
+
+(defun rcirc-sqlite-create-db (db)
+  "Create the table in SQLite database DB, if needed."
+  (sqlite-execute db
+                  "CREATE VIRTUAL TABLE IF NOT EXISTS 'rcirclogs' USING
+fts5(channel, time, nick, message)"))
+
+(defun rcirc-sqlite-set-log-time-format (fn &rest args)
+  "Advise to create parsable time format.
+This is advised to run around rcirc-log (FN) in rcirc.  After setting
+the time format, FN (rcirc-log) is called with ARGS as it's arguments.
+This changes the `rcirc-log-time-format' into the Unix timestamp format.
+?\x0 is used as delimiter after the timestamp."
+  (let ((rcirc-log-time-format "%s\x0"))
+    (apply fn args)))
+
+(defun rcirc-sqlite-store-log ()
+  "Save the loglines into SQLite.
+Fetches the loglines cached in `rcirc-log-alist'.
+This function overrides `rcirc-log-write' from rcirc.el.
+?\x0 is used as delimiter after the timestamp."
+  (let ((db (rcirc-sqlite--conn)))
+    (rcirc-sqlite-create-db db)
+    (dolist (cell rcirc-log-alist)
+      (dolist (logline (split-string (cdr cell) "\n"))
+       (unless (string-empty-p logline)
+         (save-match-data
+           (and (string-match "\\`\\([0-9]+?\\)?\x0<\\([^@]+?\\)> 
\\([^@]+\\)\\'"
+                              logline)
+                (sqlite-execute db
+                                "INSERT INTO rcirclogs (channel, time, nick, 
message)
+VALUES (?,?,?,?)"
+                                (list (car cell)
+                                      (match-string 1 logline)
+                                      (match-string 2 logline)
+                                      (match-string 3 logline))))))))
+    (setq rcirc-log-alist nil)))
+
+(defun rcirc-sqlite-db-query-channels ()
+  "List the channels from the SQLite database."
+  (let ((db (rcirc-sqlite--conn)))
+    (sqlite-select db "SELECT DISTINCT channel FROM rcirclogs")))
+
+(defun rcirc-sqlite-db-query-nicks ()
+  "List the nicks from the SQLite database."
+  (let ((db (rcirc-sqlite--conn)))
+    (sqlite-select db "SELECT DISTINCT nick FROM rcirclogs")))
+
+(defun rcirc-sqlite-db-query-months ()
+  "List the year/months from the SQLite database."
+  (let ((db (rcirc-sqlite--conn)))
+    (sqlite-select db "SELECT DISTINCT strftime('%Y-%m', time, 'unixepoch')
+FROM rcirclogs")))
+
+(defun rcirc-sqlite-db-query-stats (arg-list)
+  "List the number of rows per channel.
+ARG-LIST is a list with the requested nick."
+  (let ((db (rcirc-sqlite--conn))
+       (column "channel")
+       (dimension "")
+       (from "")
+       (dbdata ()))
+    (cond ((string= (car arg-list) "Nicks per channel")
+          (setq dimension "nicks")
+          (setq from "(SELECT channel, nick FROM rcirclogs GROUP BY 
channel,nick)"))
+         ((string= (car arg-list) "Channels per nick")
+          (setq column "nick")
+          (setq dimension "channels")
+          (setq from "(SELECT nick, channel FROM rcirclogs GROUP BY nick, 
channel)"))
+         ((string= (car arg-list) "All nicks")
+          (setq from "rcirclogs")
+          (setq dimension "lines"))
+         (t
+          (setq from "rcirclogs where nick = ?")
+          (push (car arg-list) dbdata)
+          (setq dimension "lines")))
+    (let ((dbquery (format "SELECT %s, COUNT() || ' %s' FROM %s GROUP BY %s 
ORDER BY %s"
+                          column dimension from column column)))
+      (sqlite-select db dbquery dbdata))))
+
+(defun rcirc-sqlite-db-query-log (arg-list)
+  "Fetch the last N rows of the logs from a specific channel.
+N is defined in `rcirc-sqlite-rows' and is default 200.
+The user can opt for no limit, or a different limit and offset.
+ARG-LIST is a list build from channel, period (year-month), unlimited,
+offset and limit."
+  (let ((db (rcirc-sqlite--conn))
+       (dbquery "SELECT * FROM rcirclogs")
+       (dbdata ()))
+    (pcase-let ((`(,channel ,when ,unlimited ,offset ,limit) arg-list))
+      (unless (string= channel "All channels")
+       (setq dbquery (concat dbquery " WHERE channel=?"))
+       (push channel dbdata))
+      (unless (string= when "Anytime")
+       (if (string= channel "All channels")
+           (setq dbquery (concat dbquery " WHERE "))
+         (setq dbquery (concat dbquery " AND ")))
+       (setq dbquery (concat dbquery "strftime('%Y-%m', time, 'unixepoch')=?"))
+       (push when dbdata))
+      (unless unlimited
+       (if limit
+           (progn
+             (setq dbquery (concat dbquery " ORDER BY time ASC LIMIT ?,?"))
+             (push offset dbdata)
+             (push limit dbdata))
+         (setq dbquery (concat "SELECT * FROM (" dbquery
+                               (format " ORDER BY time DESC LIMIT %s) ORDER BY 
time ASC"
+                                       rcirc-sqlite-rows)))))
+      (sqlite-execute db dbquery (reverse dbdata)))))
+
+(defun rcirc-sqlite-db-search-log (arg-list)
+  "Perform a full text search.
+ARG-LIST describes the search argument and possibly a specific
+channel, month and/or nick to narrow the search to."
+  (let ((db (rcirc-sqlite--conn))
+       (dbquery "SELECT * FROM rcirclogs WHERE rcirclogs=?"))
+    (pcase-let ((`(,query ,channel ,when ,nick) arg-list))
+      (let ((dbdata (list query)))
+       (unless (string= channel "All channels")
+         (setq dbquery (concat dbquery " AND channel=?"))
+         (push channel dbdata))
+       (unless (string= when "Anytime")
+         (setq dbquery (concat dbquery " AND strftime('%Y-%m', time, 
'unixepoch')=?"))
+         (push when dbdata))
+       (unless (string= nick "All nicks")
+         (setq dbquery (concat dbquery " AND nick=?"))
+         (push nick dbdata))
+       (setq dbquery (concat dbquery " ORDER BY rank"))
+       (sqlite-execute db dbquery
+                       (reverse dbdata))))))
+
+(defun rcirc-sqlite-convert-tabulation-list (list-to-convert)
+  "Convert LIST-TO-CONVERT to format for `tabulated-list-mode'.
+Build a vector from the data in LIST-TO-CONVERT and format the
+timestamp."
+  (unless (listp list-to-convert)
+    (error "No data available"))
+      (mapcar (lambda (cell)
+               (list  nil (vector (nth 0 cell)
+                                  (format-time-string rcirc-sqlite-time-format
+                                                      (string-to-number (nth 1 
cell)))
+                                  (nth 2 cell)
+                                  (nth 3 cell))))
+              list-to-convert))
+
+(defun rcirc-sqlite-convert-two-column-tabulation-list (list-to-convert)
+  "Convert LIST-TO-CONVERT to format for `tabulated-list-mode'.
+Build a vector from the data, converting numbers to text, because
+`tabulated-list-mode' can't handle numbers."
+  (unless (listp list-to-convert)
+    (error "No data available"))
+   (if (numberp (cadr (car list-to-convert)))
+  (mapcar (lambda (cell)
+            (list  nil (vector (car cell)
+                               (number-to-string (cadr cell)))))
+          list-to-convert)
+  (mapcar (lambda (cell)
+                    (list  nil (vector (car cell) (cadr cell))))
+          list-to-convert)))
+
+(define-derived-mode rcirc-sqlite-list-mode tabulated-list-mode
+  "rcirc-sqlite-list-mode"
+  "Major mode Rcirc-SQLite, to display tabulated db queries."
+  (setq tabulated-list-format
+       (vector (list "Channel" rcirc-sqlite-channel-column-width t)
+               (list "Time"
+                     (length (format (format-time-string 
rcirc-sqlite-time-format
+                                                         (current-time)))) t)
+               (list "Nick" 16 t)  ;; max length on Libera Chat,
+               ;; see also https://modern.ircdocs.horse/#userlen-parameter
+               (list "Message" 0 t)))
+  (tabulated-list-init-header))
+
+(define-derived-mode rcirc-sqlite-two-column-mode tabulated-list-mode
+  "rcirc-sqlite-two-column-mode"
+  "Major mode Rcirc-SQLite, to display two-column tabulated db queries."
+  (setq tabulated-list-format
+       (vector (list "Channel/Nick" rcirc-sqlite-channel-column-width t)
+               (list "Data" 0 t)))
+  (tabulated-list-init-header))
+
+(defun rcirc-sqlite-display-tabulation-list (with-function &optional arg-list)
+  "Display data in tabulated format in a new buffer.
+Retreive data using WITH-FUNCTION, optionally with additional
+arguments in ARG-LIST,"
+  (with-current-buffer (get-buffer-create "*rcirc log*")
+  (rcirc-sqlite-list-mode)
+  (setq tabulated-list-entries
+          (rcirc-sqlite-convert-tabulation-list (funcall with-function 
arg-list)))
+  (tabulated-list-print t)
+  (display-buffer (current-buffer))))
+
+(defun rcirc-sqlite-display-two-column-tabulation-list
+    (with-function &optional arg-list)
+  "Display data in tabulated format in a new buffer.
+Retreive data using WITH-FUNCTION, optionally with additional
+arguments in ARG-LIST,"
+  (with-current-buffer (get-buffer-create "*rcirc log*")
+    (rcirc-sqlite-two-column-mode)
+    (setq tabulated-list-entries
+          (rcirc-sqlite-convert-two-column-tabulation-list
+          (funcall with-function arg-list)))
+    (tabulated-list-print t)
+    (display-buffer (current-buffer))))
+
+(defun rcirc-sqlite-select-channel ()
+  "Provide completion to select a channel."
+  (completing-read
+   "Select a channel (use completion): "
+   (append (list "All channels") (rcirc-sqlite-db-query-channels))))
+
+(defun rcirc-sqlite-select-nick (wild-card-values)
+  "Provide completion to select a nick.
+Extend the list of nicks with WILD-CARD-VALUES to offer the
+user more choices."
+  (completing-read
+   "Select a nick (use completion): "
+   (append wild-card-values (rcirc-sqlite-db-query-nicks))))
+
+(defun rcirc-sqlite-select-month (wild-card-values)
+  "Provide completion to select a year and month.
+Extend the list of months with WILD-CARD-VALUES to offer the
+user more choices."
+  (completing-read
+   "Select a month (use completion): "
+   (append wild-card-values (rcirc-sqlite-db-query-months))))
+
+(defun rcirc-sqlite-view-log (channel when &optional unlimited offset limit)
+  "View the logs of a specific CHANNEL.
+WHEN is either `Anytime' or a specific month.
+Shows the result in a new buffer.
+When called without OFFSET and LIMIT, show the last 200 rows.
+When called with non-nil UNLIMITED, show all the rows.
+Otherwise offset and limit are used; in that case  both offset
+and limit have to be provided."
+  (interactive (list (rcirc-sqlite-select-channel)
+                    (rcirc-sqlite-select-month (list "Anytime"))))
+  (let ((searcharg-list (list channel when unlimited offset limit)))
+    (rcirc-sqlite-display-tabulation-list
+     #'rcirc-sqlite-db-query-log searcharg-list)))
+
+(defun rcirc-sqlite-text-search (query channel when nick)
+  "Perform full text search for QUERY.
+WHEN is either `Anytime' or a specific month, to narrow the search.
+Optional narrow search in a specific CHANNEL and/or with a specific NICK.
+The results are displayed a new buffer."
+  (interactive (list (read-string "Search for: ")
+                     (rcirc-sqlite-select-channel)
+                    (rcirc-sqlite-select-month (list "Anytime"))
+                    (rcirc-sqlite-select-nick (list "All nicks"))))
+  (let ((searcharg-list (list query channel when nick)))
+    (rcirc-sqlite-display-tabulation-list
+     #'rcirc-sqlite-db-search-log searcharg-list)))
+
+(defun rcirc-sqlite-stats (nick)
+  "Display overview of the number of rows per channel.
+Optionally narrow to a specific NICK.
+The results are displayed a new buffer."
+  (interactive (list (rcirc-sqlite-select-nick (list "All nicks"
+                                                    "Nicks per channel"
+                                                    "Channels per nick"))))
+  (let ((searcharg-list (list nick)))
+    (rcirc-sqlite-display-two-column-tabulation-list
+     #'rcirc-sqlite-db-query-stats searcharg-list)))
+
+;;;###autoload
+(define-minor-mode rcirc-sqlite-log-mode
+  "Log messages to a SQLite database."
+  :global t
+  (unless (sqlite-available-p)
+    (error "SQLite support not available"))
+  (if rcirc-sqlite-log-mode
+      (progn
+       (advice-add 'rcirc-log :around #'rcirc-sqlite-set-log-time-format)
+       (advice-add 'rcirc-log-write :override #'rcirc-sqlite-store-log))
+    (progn
+      (advice-remove 'rcirc-log-write #'rcirc-sqlite-store-log)
+      (advice-remove 'rcirc-log #'rcirc-sqlite-set-log-time-format))))
+  
+(provide 'rcirc-sqlite)
+
+;;; rcirc-sqlite.el ends here