Adding upstream version 0.11.1.upstream/0.11.1upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 184 insertions, 0 deletions

diff --git a/docs/source/sqlext.rst b/docs/source/sqlext.rst
new file mode 100644
index 0000000..e0d7543
--- /dev/null
+++ b/docs/source/sqlext.rst
@@ -0,0 +1,184 @@
+
+.. _sql-ext:
+
+SQLite Interface
+================
+
+Log analysis in **lnav** can be done using the SQLite interface.  Log messages
+can be accessed via `virtual tables <https://www.sqlite.org/vtab.html>`_ that
+are created for each file format.  The tables have the same name as the log
+format and each message is its own row in the table.  For example, given the
+following log message from an Apache access log::
+
+    127.0.0.1 - frank [10/Oct/2000:13:55:36 -0700] "GET /apache_pb.gif HTTP/1.0" 200 2326
+
+These columns would be available for its row in the :code:`access_log` table:
+
+.. csv-table::
+    :class: query-results
+    :header-rows: 1
+
+    log_line,log_part,log_time,log_idle_msecs,log_level,log_mark,log_comment,log_tags,log_filters,c_ip,cs_method,cs_referer,cs_uri_query,cs_uri_stem,cs_user_agent,cs_username,cs_version,sc_bytes,sc_status
+    0,<NULL>,2000-10-10 13:55:36.000,0,info,1,<NULL>,<NULL>,<NULL>,127.0.0.1,GET,<NULL>,<NULL>,/apache_pb.gif,<NULL>,frank,HTTP/1.0,2326,200
+
+.. note:: Some columns are hidden by default to reduce the amount of noise in
+   results, but they can still be accessed when explicitly used.  The hidden
+   columns are: :code:`log_path`, :code:`log_text`, :code:`log_body`, and
+   :code:`log_raw_text`.
+
+You can activate the SQL prompt by pressing the :kbd:`;` key.  At the
+prompt, you can start typing in the desired SQL statement and/or double-tap
+:kbd:`TAB` to activate auto-completion.  A help window will appear above
+the prompt to guide you in the usage of SQL keywords and functions.
+
+.. figure:: sql-help.png
+   :align: center
+
+   Screenshot of the online help for the SQL prompt.
+
+.. figure:: group_concat-help.png
+   :align: center
+
+   Screenshot of the online help for the :code:`group_concat()` function.
+
+A simple query to perform on an Apache access log might be to get the average
+and maximum number of bytes returned by the server, grouped by IP address:
+
+.. code-block:: custsqlite
+
+    ;SELECT c_ip, avg(sc_bytes), max(sc_bytes) FROM access_log GROUP BY c_ip
+
+After pressing :kbd:`Enter`, SQLite will execute the query using **lnav**'s
+virtual table implementation to extract the data directly from the log files.
+Once the query has finished, the main window will switch to the DB view to
+show the results.  Press :kbd:`q` to return to the log view and press :kbd:`v`
+to return to the log view.  If the SQL results contain a
+:code:`log_line` column, you can press to :kbd:`Shift` + :kbd:`V` to
+switch between the DB view and the log
+
+.. figure:: query-results.png
+   :align: center
+
+   Screenshot of the SQL results view.
+
+The DB view has the following display features:
+
+* Column headers stick to the top of the view when scrolling.
+* A stacked bar chart of the numeric column values is displayed underneath the
+  rows.  Pressing :kbd:`TAB` will cycle through displaying no columns, each
+  individual column, or all columns.
+* JSON columns in the top row can be pretty-printed by pressing :kbd:`p`.
+  The display will show the value and JSON-Pointer path that can be passed to
+  the `jget`_ function.
+
+
+Log Tables
+----------
+
+Each log format has its own database table that can be used to access log
+messages that match that format.  The table name is the same as the format
+name, for example, the :code:`syslog_log` format will have a table that is
+also named :code:`syslog_log`.  There is also an :code:`all_logs` table
+that provides access to all messages from all formats.
+
+.. note:: Only the displayed log messages are reflected in the SQLite
+   interface.  Any log messages that have been filtered out are not
+   accessible.
+
+The columns in the log tables are made up of several builtins along with
+the values captured by the log format specification.  Use the :code:`.schema`
+command in the SQL prompt to examine a dump of the current database schema.
+
+The following columns are builtin and included in a :code:`SELECT *`:
+
+  :log_line: The line number for the message in the log view.
+  :log_part: The partition the message is in.  This column can be changed by
+    an :code:`UPDATE` or the :ref:`:parition-name<partition_name>` command.
+  :log_time: The adjusted timestamp for the log message.  This time can differ
+    from the log message's time stamp if it arrived out-of-order and the log
+    format expects log files to be time-ordered.
+  :log_actual_time: The log messages original timestamp in the file.
+  :log_idle_msecs: The difference in time between this messages and the
+    previous.  The unit of time is milliseconds.
+  :log_level: The log message level.
+  :log_mark: True if the log message was marked by the user.
+  :log_comment: The comment for the message.  This column can be changed by
+    an :code:`UPDATE` or the :ref:`:comment<comment>` command.
+  :log_tags: A JSON list of tags for the message.  This column can be changed
+    by an :code:`UPDATE` or the :ref:`:tag<tag>` command.
+  :log_filters: A JSON list of filter IDs that matched this message
+
+The following columns are builtin and are hidden, so they will *not* be
+included in a :code:`SELECT *`:
+
+  :log_time_msecs: The adjusted timestamp for the log message as the number of
+    milliseconds from the epoch.  This column can be more efficient to use for
+    time-related operations, like :ref:`timeslice()<timeslice>`.
+  :log_path: The path to the log file this message is from.
+  :log_text: The full text of the log message.
+  :log_body: The body of the log message.
+  :log_raw_text: The raw text of this message from the log file.  In this case
+    of JSON and CSV logs, this will be the exact line of JSON-Line and CSV
+    text from the file.
+
+Extensions
+----------
+
+To make it easier to analyze log data from within **lnav**, there are several
+built-in extensions that provide extra functions and collators beyond those
+`provided by SQLite <http://www.sqlite.org/lang_corefunc.html>`_.  The majority
+of the functions are from the
+`extensions-functions.c <http://www.sqlite.org/contrib>`_ file available from
+the `sqlite.org <http://sqlite.org>`_ web site.
+
+.. tip:: You can include a SQLite database file on the command-line and use
+  **lnav**'s interface to perform queries.  The database will be attached with
+  a name based on the database file name.
+
+Commands
+--------
+
+A SQL command is an internal macro implemented by lnav.
+
+* .schema - Open the schema view.  This view contains a dump of the schema
+  for the internal tables and any tables in attached databases.
+* .msgformats - Executes a canned query that groups and counts log messages by
+  the format of their message bodies.  This command can be useful for quickly
+  finding out the types of messages that are most common in a log file.
+
+Variables
+---------
+
+The following variables are available in SQL statements:
+
+* $LINES - The number of lines in the terminal window.
+* $COLS - The number of columns in the terminal window.
+
+Environment
+-----------
+
+Environment variables can be accessed in queries using the usual syntax of
+:code:`$VAR_NAME`.  For example, to read the value of the "USER" variable, you
+can write:
+
+.. code-block:: custsqlite
+
+    ;SELECT $USER
+
+.. _collators:
+
+Collators
+---------
+
+* **naturalcase** - Compare strings "naturally" so that number values in the
+  string are compared based on their numeric value and not their character
+  values.  For example, "foo10" would be considered greater than "foo2".
+* **naturalnocase** - The same as naturalcase, but case-insensitive.
+* **ipaddress** - Compare IPv4/IPv6 addresses.
+
+Reference
+---------
+
+The following is a reference of the SQL syntax and functions that are available:
+
+.. include:: ../../src/internals/sql-ref.rst