rcirc logging in SQLite
***********************

Introduction
************

rcirc is a default, simple IRC client in Emacs. rcirc can be enabled 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
************

rcirc-sqlite relies on the capability of Emacs to work together with SQLite. It makes use of the variable rcirc-log-time-format, which is introduced in Emacs version 30.1.

To use rcirc-sqlite:

  * Emacs must be compiled with the SQLite capability.

  * SQLite 3 must be installed on the system.

  * rcirc must have the option to set the variable rcirc-log-time-format.

Installation and activation
***************************

Installation
============

Install from GNU ELPA
---------------------

The GNU ELPA package name is: rcirc-sqlite.

To install the package:

  * M-x package-refresh-contents

  * M-x package-install

and search for rcirc-sqlite.

Or use use-package:

     (use-package rcirc-sqlite
         :ensure t
         :config
         (add-hook 'rcirc-mode-hook #'rcirc-sqlite-log-mode))

Manual installation
-------------------

Create a directory for the package.

mkdir ~/.emacs.d/manualpackages

Add this directory as a load path to your init file:

     (add-to-list 'load-path "~/.emacs.d/manualpackages")

Re-evaluate your init file or restart Emacs, whatever you prefer.

Requirements describes the requirements.

Activation
==========

 -- 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:

     (require 'rcirc-sqlite)
     (add-hook 'rcirc-mode-hook #'rcirc-sqlite-log-mode)

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:

     (setq rcirc-log-flag t)

Customization
*************

To customize rcirc-sqlite run M-x customize-group rcirc-sqlite, or use (setopt ...) in your init file, like /.emacs. For example:

     (setopt rcirc-sqlite-rows 100)

The user may customize the following options.

 -- User Option: rcirc-sqlite-database
     The file in which SQLite stores the 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*.

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.

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.

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*.

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*.

The buffer *rcirc log* has more information on this buffer.

 -- User Option: rcirc-sqlite-register
     The register to store messages.

The variable rcirc-sqlite-register assigns the register to store chat messages in.

The user selects a message from the list of messages in the buffer *rcirc log*. Depending on the command, the chat message either overwrites the current contents of the register, or is appended to it.

Collect one or more messages in a register

The default register is r.

Change this option when you have assigned this register for a different use.

Commands
********

Summery of the commands
=======================

  * M-x rcirc-sqlite-view-log: display the logs.

  * M-x rcirc-sqlite-text-search: perform full text search in the logs.

  * M-x rcirc-sqlite-logs-from-nick: display the logs from a specific nick.

  * M-x rcirc-sqlite-stats: displays some stats.

These commands result in logs displayed in the buffer *rcirc log*. In that buffer you can use the key bindings for more actions. These actions are described in the sections:

  * Toggle display of the server in the channel name

  * Change the selected logs in the log-buffer

  * Copy a message to the kill-ring

  * Collect one or more messages in a register

  * Browse URL in the message

The stats view offer some drill-down options and can be a good starting point to explore your logs.

View the logs
=============

 -- 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 to choose a time range, using completion.  When a range is chosen, the last 200 lines the chat log from that range are shown.  Choose the option Anytime or the last 90, 60, 30, or 7 days to get the most recent 200 lines.

The buffer *rcirc log* displays the chat logs.

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
================================================

 -- 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 time range (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 independent of the channel.

When a time range is chosen, the search is performed within the chat messages that were send during the chosen range.  When Anytime is chosen, no filtering on date/time takes place.

When a nick is chosen, the search is performed within the chats send by a 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*" to search for "http://" as well as for "https://".

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.

Toggle display of the server in the channel name
================================================

The buffer *rcirc log* shows the results from the latest query.

To suprress the display of the server name in the channel name, use the key (.

To activate the display of the server name in the channel name, use the key ).

Change the selected logs in the log-buffer
==========================================

The buffer *rcirc log* shows the results from the latest query. Move the point on this buffer up or down to select a chat message.

Use one of the key bindings to change the selected logs.

Change the view to a specific day
---------------------------------

Move the point in the buffer *rcirc log* up or down to select a chat message.

Activate the command `rcirc-sqlite-single-day' with the key RET or mouse button 1.

The command switches the view to the logs with the same channel and the same date as the selected chat message.

This can be useful to get some context around a specific search result.

Change the view to a specific day and the next day
--------------------------------------------------

Move the point in the buffer *rcirc log* up or down to select a chat message.

Activate the command `rcirc-sqlite-next-day' with the key >.

The command switches the view to the logs with the same channel and the same date and the next day as the selected chat message.

Change the view to a specific day and the previous day
------------------------------------------------------

Move the point in the buffer *rcirc log* up or down to select a chat message.

Activate the command `rcirc-sqlite-previous-day' with the key ^.

The command switches the view to the logs with the same channel and the same date and the previous day as the selected chat message.

Change the view to a specific day and the next days
---------------------------------------------------

Move the point in the buffer *rcirc log* up or down to select a chat message.

Activate the command `rcirc-sqlite-all-next-days' with the key a.

The command switches the view to the logs with the same channel and the same date or later as the selected chat message.

Change the view to a specific day and a specific nick
-----------------------------------------------------

Move the point in the buffer *rcirc log* up or down to select a chat message.

Activate the command `rcirc-sqlite-single-nick' with the key <.

The command switches the view to the logs with the same channel, the same nick, and the same date as the selected chat message.

Copy a message to the kill-ring
===============================

Move the point in the buffer *rcirc log* up or down to select a chat message.

Activate the command `rcirc-sqlite-kill-insert' with the key c to insert the selected message in the kill-ring.

This command copies the chat message with a some markup, fit to use in e.g., org mode.

Collect one or more messages in a register
==========================================

Move the point in the buffer *rcirc log* up or down to select a chat message.

Activate the command `rcirc-sqlite-register-insert' with the key R to insert the selected message in a register. This overwrites the current contents of the register.

Activate the command `rcirc-sqlite-register-append' with the key r to append the selected message to a register.

The commands insert the chat message with a some markup, fit to use in e.g., org mode.

The default register that is used for this is register `r'. Change the default register by setting the variable `rcirc-sqlite-register' to a different register.

Customization

You can insert the contents of the register into any buffer with `insert-register' (`C-x r i <register>'). For the default register `r' this is: `C-x r i r'.

Whatever you store in a register remains there until you store something else in that register, or until you close Emacs.

See Registers (Emacs manual)

Example workflow
----------------

Explore the logs, for example with the search option, and start collecting messages.

Store the first message of your interest with the command `rcirc-sqlite-register-insert' (key: R), overwriting anything that was in this register.

Search for the next message of your interest, and issue the command `rcirc-sqlite-register-append' (key: r).

Repeat appending until done.

Open a note, for example in denote, org, or org-roam, and insert your collected chat messages with `insert-register' (`C-x r i <register>').

Logs from a specific nick
=========================

 -- Function: rcirc-sqlite-logs-from-nick
     Display the logs from a specific nick

Issue the command rcirc-sqlite-logs-from-nick to request the logs of a specific nick.

The user is prompted to select a nick, using completion.

Next, the user is prompted to select a time range, using completion. The options are:

  * Anytime

  * Last 90 days

  * Last 60 days

  * Last 30 days

  * Last 7 days

  * Manually select range

When the option Anytime is chosen, no filtering takes place.

When the latest 90, 60, 30, or 7 days is chosen, the logs from that time range are shown.

The option manually select range prompts the user to select a start and a end of a time range, using the org date picker.  Use the org calendar commands to select a date and time.

Browse URL in the message
=========================

Move the point in the buffer *rcirc log* up or down to select a chat message.

Activate the command `rcirc-sqlite-browse-url-from-message' with the key b to browse to the first URL in the selected message.

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 (messages) 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 and lists each channel with one or more chat messages from that nick, 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 unique nicks.

When Channels per nick is chosen, the buffer shows for each nick the number of channels with messages from this nick.

 -- Function: rcirc-sqlite-view-drill-down
     Drill-down to more details

Use drill-down in the buffer showing the stats.  This will show more details.

RET
     Drill-down

mouse-1
     Drill-down

The drill-down options make the command rcirc-sqlite-stats a nice starting point to explore your logs.

 -- Function: rcirc-sqlite-stats-per-month
     Create overview of the number of rows per month.

Issue the command M-x rcirc-sqlite-stats-per-month to get an overview of the number of rows (messages) in the database, summerized per month.

Drill down to the number of rows summerized per day of the selected month, and from there drill down to all messages of the selected day.

The buffer *rcirc log*
**********************

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.

Suprress the display of the server name in the channel name with the key (, or activate the display of the server name with the key ).

Move up and down in this buffer to select a chat message. Then you can use the following key bindings to activate some commands.

RET rcirc-sqlite-single-day

> rcirc-sqlite-next-day

^ rcirc-sqlite-previous-day

a rcirc-sqlite-all-next-days

b rcirc-sqlite-browse-url-from-message

< rcirc-sqlite-single-nick

c rcirc-sqlite-kill-insert

R rcirc-sqlite-register-insert

r rcirc-sqlite-register-append

For a description of these commands, see the following chapters.

Change the selected logs in the log-buffer

Copy a message to the kill-ring

Collect one or more messages in a register

Browse URL in the message

The default key bindings of the tabulated-list-mode are also available in this buffer.

Some examples of these key bindings:

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).

}
     Widen the current column by N (the prefix numeric argument) characters,

{
     Narrow the current column by N (the prefix numeric argument) characters.

SPACE
     Scroll the buffer up.

BACKSPACE
     Scroll the buffer down.

n
     Move down one screen line (next line).

p
     Move up one screen line (previous line).

q
     Close the buffer.

Inner workings
**************

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
***************

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
******************************

     [file fdl.texi not included]