path: root/rcirc-sqlite.texi

\input texinfo    @c -*- texinfo -*-
@c %**start of header
@setfilename rcirc-sqlite.info
@settitle rcirc logging in SQLite
@documentencoding UTF-8
@documentlanguage en
@c %**end of header

@dircategory Emacs misc features
@direntry
* rcirc-sqlite: (rcirc-sqlite). rcirc logging in SQLite.
@end direntry

@finalout
@titlepage
@title rcirc logging in SQLite
@author Matto Fransen
@end titlepage

@contents

@ifnottex
@node Top
@top rcirc logging in SQLite
@end ifnottex

@menu
* Introduction::
* Requirements::
* Installation and activation::
* Customization::
* Commands::
* The buffer @code{*rcirc log*}::
* Inner workings::
* Database schema::
* GNU Free Documentation License::
* Command and Function Index::
* Variable Index::
* Key bindings::

@detailmenu
--- The Detailed Node Listing ---

Installation and activation

* Installation::
* Activation::

Commands

* Summery of the commands::
* View the logs::
* Full text search and load the result in a buffer::
* Stats::

Inner workings

* Delay::

@end detailmenu
@end menu

@node Introduction
@chapter Introduction

@code{rcirc} is a default, simple IRC client in Emacs. @code{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.

@node Requirements
@chapter Requirements

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

To use rcirc-sqlite:

@itemize
@item
Emacs must be compiled with the SQLite capability.
@item
SQLite 3 must be installed on the system.
@item
rcirc must have the option to set the variable @code{rcirc-log-time-format}.
@end itemize

@node Installation and activation
@chapter Installation and activation

@menu
* Installation::
* Activation::
@end menu

@node Installation
@section Installation

@node Install from GNU ELPA
@subsection Install from GNU ELPA
The GNU ELPA package name is: @code{rcirc-sqlite}.

To install the package:

@itemize
@item
@code{M-x package-refresh-contents}
@item
@code{M-x package-install}
@end itemize

and search for @code{rcirc-sqlite}.

Or use @code{use-package}:

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

@node Manual installation
@subsection Manual installation
Create a directory for the package.

@code{mkdir ~/.emacs.d/manualpackages}

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

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

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

@ref{Requirements} describes the requirements.

@node Activation
@section Activation
@findex rcirc-sqlite-log-mode

@deffn Command rcirc-sqlite-log-mode
Activates or deactivates @code{rcirc-sqlite}.
@end deffn

Issue the command @code{rcirc-sqlite-log-mode} to manually start
@code{rcirc-sqlite}.  This command toggles between activation
and deactivation of @code{rcirc-sqlite}. 

To start @code{rcirc-sqlite} automatically when @code{rcirc}
is started, add the following to your init file:

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

While @code{rcirc-sqlite} is activated, @code{rcirc} will no longer
write the logs to files, until @code{rcirc-sqlite} is deactivated.
Deactivate @code{rcirc-sqlite} using the command
@code{rcirc-sqlite-log-mode} again.

The logging must be enabled in @code{rcirc}.
To do this, add for example the following to your init file:

@lisp
(setq rcirc-log-flag t)
@end lisp

@node Customization
@chapter Customization

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

@lisp
(setopt rcirc-sqlite-rows 100)
@end lisp

The user may customize the following options.

@defopt rcirc-sqlite-database
The file in which SQLite stores the database.
@end defopt

@vindex rcirc-sqlite-database

The file in which SQLite stores the database can be customized.

The default file is @file{ricirc-log.db}, located in the default Emacs
directory (e.g., @file{~/.emacs.d/ricirc-log.db}).

Set this option to use a different file.

@defopt rcirc-sqlite-time-format
The format for the date and time in the buffer @code{*rcirc log*}.
@end defopt

@vindex rcirc-sqlite-time-format

The variable @code{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 @code{*rcirc log*}.

The default format is @code{%Y-%m-%d %H:%M}.

Set this option to use a different time format in the buffer
@code{*rcirc log*}.

@defopt rcirc-sqlite-rows
The default maximum number of rows when viewing logs.
@end defopt
@vindex rcirc-sqlite-rows

The variable @code{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.

@ref{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.

@defopt rcirc-sqlite-channel-column-width
The default column width of the channel column in the buffer
@code{*rcirc log*}.
@end defopt
@vindex rcirc-sqlite-channel-column-width

The variable @code{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 @code{*rcirc log*}.

@ref{The buffer @code{*rcirc log*}} has more information on this buffer.

@defopt rcirc-sqlite-register
The register to store messages.
@end defopt

@vindex rcirc-sqlite-register

The variable @code{rcirc-sqlite-register} assigns the register
to store chat messages in.

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

@ref{Collect one or more messages in a register}

The default register is @code{r}.

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

@node Commands
@chapter Commands

@menu
* Summery of the commands::
* View the logs::
* Full text search and load the result in a buffer::
* 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::
* Logs from a specific nick::
* Browse URL in the message::
* Stats::
@end menu

@node Summery of the commands
@section Summery of the commands

@itemize
@item
@code{M-x rcirc-sqlite-view-log}: display the logs.
@item
@code{M-x rcirc-sqlite-text-search}: perform full text search in the logs.
@item
@code{M-x rcirc-sqlite-logs-from-nick}: display the logs from a specific nick.
@item
@code{M-x rcirc-sqlite-stats}: displays some stats.
@end itemize

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

@itemize
@item @ref{Toggle display of the server in the channel name}
@item @ref{Change the selected logs in the log-buffer}
@item @ref{Copy a message to the kill-ring}
@item @ref{Collect one or more messages in a register}
@item @ref{Browse URL in the message}
@end itemize

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

@node View the logs
@section View the logs
@findex rcirc-sqlite-view-log

@defun rcirc-sqlite-view-log channel &optional unlimited offset limit
Display the logs in a new buffer.
@end defun

Issue the command @code{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
@code{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 @code{All channels} to show the last 200 lines of the
chat log of @strong{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 @code{Anytime} or the
last 90, 60, 30, or 7 days to get the most recent 200 lines.

The buffer @code{*rcirc log*} displays the chat logs.

@subheading Optional arguments

This function has three optional arguments, @code{unlimited},
@code{offset}, and @code{limit}.

@itemize
@item
@code{unlimited}
When non nil, @code{rcirc-sqlite-view-log} will show @strong{all}
log lines in the database of the channel.
@item
@code{offset} and @code{limit}
Use @code{offset} and @code{limit} to select a number of lines from
the log lines in the database of the channel.
@end itemize

@node Full text search and load the result in a buffer
@section Full text search and load the result in a buffer
@findex rcirc-sqlite-text-search

@defun rcirc-sqlite-text-search query channel nick
Perform full text search.
@end defun

Issue the command @code{M-x rcirc-sqlite-text-search} to perform
full text search in the logs.

When this command is issued:

@itemize
@item
The user is prompted for a search string.
@item
The user is prompted to choose a channel (through completion).
@item
The user is prompted to choose a time range (through completion).
@item
The user is prompted to choose a nick (through completion).
@end itemize

When a channel is chosen, the search is performed within the
chat logs of that specific channel.  Choose @code{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
@code{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 @code{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 @code{foo}, chat messages
containing the word @code{foo} will be found, but chat messages
containing the word @code{foobar} will not be found.

To search for both @code{foo} and @code{foobar}, use the search
string @code{foo*}.

Likewise, to search for URLs, use something like @code{"http*"}
to search for @code{"http://"} as well as for @code{"https://"}.

For more formatting of the search see the chapter
@code{Full-text Query Syntax} of the SQLite documentation (see
@uref{https://www.sqlite.org/fts5.html}).

The buffer @code{*rcirc log*} displays the search results.

@node Toggle display of the server in the channel name
@section Toggle display of the server in the channel name
@kindex (
@kindex )

The buffer @code{*rcirc log*} shows the results from the latest query.

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

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

@node Change the selected logs in the log-buffer
@section Change the selected logs in the log-buffer

The buffer @code{*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.

@node Change the view to a specific day
@subsection Change the view to a specific day
@findex rcirc-sqlite-single-day
@kindex RET

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

Activate the command `rcirc-sqlite-single-day' with the key @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.

@node Change the view to a specific day and the next day
@subsection Change the view to a specific day and the next day
@findex rcirc-sqlite-next-day
@kindex >

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

Activate the command `rcirc-sqlite-next-day' with the key @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.

@node Change the view to a specific day and the previous day
@subsection Change the view to a specific day and the previous day
@findex rcirc-sqlite-previous-day
@kindex ^

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

Activate the command `rcirc-sqlite-previous-day' with the key @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.

@node Change the view to a specific day and the next days
@subsection Change the view to a specific day and the next days
@findex rcirc-sqlite-all-next-days
@kindex a

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

Activate the command `rcirc-sqlite-all-next-days' with
the key @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.

@node Change the view to a specific day and a specific nick
@subsection Change the view to a specific day and a specific nick
@findex rcirc-sqlite-single-nick
@kindex <

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

Activate the command `rcirc-sqlite-single-nick' with
the key @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.

@node Copy a message to the kill-ring
@section Copy a message to the kill-ring
@findex rcirc-sqlite-kill-insert
@kindex c

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

Activate the command `rcirc-sqlite-kill-insert' with the key
@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.

@node Collect one or more messages in a register
@section Collect one or more messages in a register 
@findex rcirc-sqlite-register-insert
@findex rcirc-sqlite-register-append'
@kindex R
@kindex r

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

Activate the command `rcirc-sqlite-register-insert' with the key
@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
@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.

@ref{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.

@xref{Registers,,,emacs,Emacs manual}

@subheading 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: @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: @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>').

@node Logs from a specific nick
@section  Logs from a specific nick
@findex rcirc-sqlite-logs-from-nick

@defun rcirc-sqlite-logs-from-nick
Display the logs from a specific nick
@end defun

Issue the command @code{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:

@itemize
@item
Anytime
@item
Last 90 days
@item
Last 60 days
@item
Last 30 days
@item
Last 7 days
@item
Manually select range
@end itemize

When the option @code{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 @code{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.


@node Browse URL in the message
@section Browse URL in the message
@findex rcirc-sqlite-browse-url-from-message
@kindex b

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

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

@node Stats
@section Stats
@findex rcirc-sqlite-stats
@findex rcirc-sqlite-view-drill-down
@findex rcirc-sqlite-stats-per-month

@defun rcirc-sqlite-stats nick
Create overview with some stats
@end defun

Issue the command @code{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 @code{*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 @code{All nicks} is chosen, the buffer shows the row count
for each channel in the database.

When @code{Nicks per channel} is chosen, the buffer shows for
each channel the number of unique nicks.

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

@defun rcirc-sqlite-view-drill-down
Drill-down to more details
@end defun

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

@table @asis
@item @kbd{RET}
@kindex RET
Drill-down

@item @kbd{mouse-1}
@kindex mouse-1
Drill-down
@end table

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

@defun rcirc-sqlite-stats-per-month
Create overview of the number of rows per month.
@end defun

Issue the command @code{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.

@node The buffer @code{*rcirc log*}
@chapter The buffer @code{*rcirc log*}

The buffer @code{*rcirc log*} is used to show the output of the
database queries. This buffer uses a derived mode from the
@code{tabulated-list-mode}.

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

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

@table @asis
@item @kbd{RET} @code{rcirc-sqlite-single-day}
@item @kbd{>} @code{rcirc-sqlite-next-day}
@item @kbd{^} @code{rcirc-sqlite-previous-day}
@item @kbd{a} @code{rcirc-sqlite-all-next-days}
@item @kbd{b} @code{rcirc-sqlite-browse-url-from-message}
@item @kbd{<} @code{rcirc-sqlite-single-nick}
@item @kbd{c} @code{rcirc-sqlite-kill-insert}
@item @kbd{R} @code{rcirc-sqlite-register-insert}
@item @kbd{r} @code{rcirc-sqlite-register-append}
@end table

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

@ref{Change the selected logs in the log-buffer}

@ref{Copy a message to the kill-ring}

@ref{Collect one or more messages in a register}

@ref{Browse URL in the message}

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

Some examples of these key bindings:

@table @asis
@item @kbd{S} (@code{Sort})
@kindex S
@findex Sort
Sort the buffer according to the values of the column of point.

Use a numeric prefix argument @var{n} to sort the buffer according to
the values of the @var{n}-th column from point.  Repeat to sort in the
alternate order (ascending or descending).

@item @kbd{@}}
@kindex @}
Widen the current column by @var{n} (the prefix numeric
argument) characters,

@item @kbd{@{}
@kindex @{
Narrow the current column by @var{n} (the prefix numeric
argument) characters.

@item @kbd{SPACE}
@kindex SPACE
Scroll the buffer up.

@item @kbd{BACKSPACE}
@kindex BACKSPACE
Scroll the buffer down.

@item @kbd{n}
@kindex n
Move down one screen line (next line).

@item @kbd{p}
@kindex p
Move up one screen line (previous line).

@item @kbd{q}
@kindex q
Close the buffer.
@end table

@node Inner workings
@chapter Inner workings

@findex rcirc-log-write
@findex rcirc-sqlite-store-log

@code{rcirc} caches the IRC messages in a list, and periodically
writes the contents of this cache to the log files.
@code{rcirc-sqlite} collects the contents of this cache.

@code{rcirc-sqlite} overrides the @code{rcirc}-function
@code{rcirc-log-write} with the @code{rcirc-sqlite}-function
@code{rcirc-sqlite-store-log} for this.

To be able to easy parse the timestamp, @code{rcirc-sqlite} changes
the @code{rcirc-log-time-format}. It does this by advising around the
@code{rcirc}-function rcirc-log.

@menu
* Delay::
@end menu

@node Delay
@section Delay

There is some delay between the arrival of chat messages in the chat
buffer and the storage of the logs in the database.

@code{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 @code{rcirc-sqlite} is also triggered by the
auto-save functionality.

@node Database schema
@chapter Database schema

The SQLite database is created at the first time @code{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.

@itemize
@item
channel:
This is the channel name in the format @code{rcirc} uses to
determine the log file. This format is
@code{channelname@@servername.log}, for example
@code{#rcirc@@LiberaChat.log}.
@item
time:
The timestamp, stored in the unix timestamp format.
@item
nick:
The nick name of the sender.
@item
message:
The actual chat message.
@end itemize


@node GNU Free Documentation License
@appendix GNU Free Documentation License

@cindex FDL, GNU Free Documentation License
@include fdl.texi

@node Command and Function Index
@chapter Command and Function Index

@printindex fn

@node Variable Index
@chapter Variable Index

@printindex vr

@node Key bindings
@chapter Key bindings

@printindex ky

@bye