
  lnav - A fancy log file viewer

DESCRIPTION
===========

The log file navigator, lnav, is an enhanced log file viewer that
takes advantage of any semantic information that can be gleaned from
the files being viewed, such as timestamps and log levels.  Using this
extra semantic information, lnav can do things like interleaving
messages from different files, generate histograms of messages over
time, and providing hotkeys for navigating through the file.  It is
hoped that these features will allow the user to quickly and
efficiently zero in on problems.


OPENING PATHS/URLs
==================

The main arguments to lnav are the files, directories, glob patterns,
or URLs to be viewed.  If no arguments are given, the default syslog
file for your system will be opened.  These arguments will be polled
periodically so that any new data or files will be automatically
loaded.  If a previously loaded file is removed or replaced, it will
be closed and the replacement opened.

Note: When opening SFTP URLs, if the password is not provided for the
host, the SSH agent can be used to do authentication.


OPTIONS
=======

Lnav takes a list of files to view and/or you can use the flag
arguments to load well-known log files, such as the syslog or apache
log files.  The flag arguments are:

  -a         Load all of the most recent log file types.
  -r         Recursively load files from the given directory hierarchies.
  -R         Load older rotated log files as well.

When using the flag arguments, lnav will look for the files relative
to the current directory and its parent directories.  In other words,
if you are working within a directory that has the well-known log
files, those will be preferred over any others.

Any files given on the command-line are scanned to determine their log
file format and to create an index for each line in the file.  You do
not have to manually specify the log file format.  The currently
supported formats are: syslog, apache, strace, tcsh history, and
generic log files with timestamps.

Lnav will also display data piped in on the standard input.  The
following options are available when doing so:

  -t         Prepend timestamps to the lines of data being read in
             on the standard input.
  -w file    Write the contents of the standard input to this file.

To automatically execute queries or lnav commands after the files
have been loaded, you can use the following options:

  -c cmd     A command, query, or file to execute.  The first character
             determines the type of operation: a colon is used for the
             built-in commands; a semi-colon for SQL queries; and a
             pipe symbol (|) for executing a file containing other
             commands.  For example, to open the file "foo.log" and go
             to the tenth line in the file, you can do:

               lnav -c ':goto 10' foo.log

             This option can be given multiple times to execute multiple
             operations in sequence.

  -f file    A file that contains commands, queries, or files to execute.
             This option is a shortcut for "-c '|file'".  You can use a
             dash (-) to execute commands from the standard input.

To execute commands/queries without the opening the interactive text UI,
you can pass the '-n' option.  This combination of options allows you to
write scripts for processing logs with lnav.  For example, to get a list
of IP addresses that dhclient has bound to in CSV format:

  #! /usr/bin/lnav -nf

  # Usage: dhcp_ip.lnav /var/log/messages

  # Only include lines that look like:
  #    Apr 29 00:31:56 example-centos5 dhclient: bound to 10.1.10.103 -- renewal in 9938 seconds.
  :filter-in dhclient: bound to

  # The log message parser will extract the IP address as col_0, so we
  # select that and alias it to "dhcp_ip".
  ;select distinct col_0 as dhcp_ip from logline;

  # Finally, write the results of the query to stdout.
  :write-csv-to -


DISPLAY
=======

The main part of the display shows the log lines from the files interleaved
based on time-of-day.  New lines are automatically loaded as they are appended
to the files and, if you are viewing the bottom of the files, lnav will scroll
down to display the new lines, much like 'tail -f'.

On color displays, the lines will be highlighted as follows:

  * Errors will be colored in red;
  * warnings will be yellow;
  * boundaries between days will be underlined; and
  * various color highlights will be applied to: IP addresses, SQL keywords,
    XML tags, file and line numbers in Java backtraces, and quoted strings.

To give you an idea of where you are spatially, the right side of the
display has a proportionally sized 'scroll bar' that indicates your
current position in the files.  The scroll bar will also show areas of
the file where warnings or errors are detected by coloring the bar
yellow or red, respectively.  Tick marks will also be added to the
left and right hand side of the bar, for search hits and bookmarks.

A bar on the left side is color coded and broken up to indicate which
messages are from the same file.  Pressing the left-arrow or 'h' will
reveal the source file names for each message and pressing again will
show the full paths.

When at the bottom of the log view, a summary line will be displayed on the
right-hand-side to give you some more information about your logs, including:
how long ago the last message was generated, the number of log files, the
error rate, and how much time the logs cover.  The error rate display shows
the errors-per-minute over the last five minutes.  A bar chart is also
overlaid on the "Error rate" label to show the error rate over the past ten
seconds.  For example, if there have not been many errors in the past five
minutes and there is a sudden spike, the bar chart will fill up completely.
But, if there has been a steady stream of errors, then the chart will only
partially fill based on the recent error frequency.

Above and below the main body are status lines that display:

  * the current time;
  * the name of the file the top line was pulled from;
  * the log format for the top line;
  * the current view;
  * the line number for the top line in the display;
  * the current search hit, the total number of hits, and the search term;

If the view supports filtering, there will be a status line showing the
following:

  * the number of enabled filters and the total number of filters;
  * the number of lines not displayed because of filtering.

To edit the filters, you can press TAB to change the focus from the main
view to the filter editor.  The editor allows you to create, enable/disable,
and delete filters easily.

Finally, the last line on the display is where you can enter search
patterns and execute internal commands, such as converting a
unix-timestamp into a human-readable date.  The command-line is
implemented using the readline library, so the usual set of keyboard
shortcuts are available.  Most commands and searches also support
tab-completion.


The body of the display is also used to display other content, such
as: the help file, histograms of the log messages over time, and
SQL results.  The views are organized into a stack so that any time
you activate a new view with a key press or command, the new view
is pushed onto the stack.  Pressing the same key again will pop the
view off of the stack and return you to the previous view.  Note
that you can always use 'q' to pop the top view off of the stack.


KEY BINDINGS
============

Views
-----

  ?                 View/leave this help message.
  q                 Leave the current view or quit the program when in
                    the log file view.
  Q                 Similar to 'q', except it will try to sync the top time
                    between the current and former views.  For example, when
                    leaving the spectrogram view with 'Q', the top time in that
                    view will be matched to the top time in the log view.

  TAB               Toggle focusing on the filter editor or the main view.

  a/A               Restore the view that was previously popped with 'q/Q'.
                    The 'A' hotkey will try to match the top times between the
                    two views.

  X                 Close the current text file or log file.

Spatial Navigation
------------------

  g/home            Move to the top of the file.
  G/end             Move to the end of the file.  If the view is already
                    at the end, it will move to the last line.
  space/pgdn        Move down a page.
  b/bs/pgup         Move up a page.
  j/cr/down-arrow   Move down a line.
  k/up-arrow        Move up a line.
  h/left-arrow      Move to the left.  In the log view, moving left will reveal
                    the source log file names for each line.  Pressing again
                    will reveal the full path.
  l/right-arrow     Move to the right.
  H/Shift+left      Move to the left by a smaller increment.
  L/Shift+right     Move to the right by a smaller increment.

  e/E               Move to the next/previous error.
  w/W               Move to the next/previous warning.
  n/N               Move to the next/previous search hit.  When pressed
                    repeatedly within a short time, the view will move at
                    least a full page at a time instead of moving to the
                    next hit.
  f/F               Move to the next/previous file.  In the log view, this
                    moves to the next line from a different file.  In the
                    text view, this rotates the view to the next file.

  >/<               Move horizontally to the next/previous search hit.

  o/O               Move forward/backward to the log message with a matching
                    'operation ID' (opid) field.

  u/U               Move forward/backward through any user bookmarks
                    you have added using the 'm' key.  This hotkey will
                    also jump to the start of any log partitions that have
                    been created with the 'partition-name' command.

  y/Y               Move forward/backward through the log view based on the
                    "log_line" column in the SQL result view.

  s/S               Move to the next/previous "slow down" in the log message
                    rate.  A slow down is detected by measuring how quickly
                    the message rate has changed over the previous several
                    messages.  For example, if one message is logged every
                    second for five seconds and then the last message arrives
                    five seconds later, the last message will be highlighted
                    as a slow down.

  {/}               Move to the previous/next location in history.  Whenever
                    you jump to a new location in the view, the location will
                    be added to the history.  The history is not updated when
                    using only the arrow keys.


Chronological Navigation
------------------------

  d/D               Move forward/backward 24 hours from the current
                    position in the log file.

  1-6/Shift 1-6     Move to the next/previous n'th ten minute of the
                    hour.  For example, '4' would move to the first
                    log line in the fortieth minute of the current
                    hour in the log.  And, '6' would move to the next
                    hour boundary.

  7/8               Move to the previous/next minute.

  0/Shift 0         Move to the next/previous day boundary.

  r/R               Move forward/backward based on the relative time that
                    was last used with the 'goto' command.  For example,
                    executing ':goto a minute later' will move the log view
                    forward a minute and then pressing 'r' will move it
                    forward a minute again.  Pressing 'R' will then move the
                    view in the opposite direction, so backwards a minute.

Bookmarks
---------

  m                 Mark/unmark the line at the top of the display.
                    The line will be highlighted with reverse video to
                    indicate that it is a user bookmark.  You can use
                    the 'u' hotkey to iterate through marks you have
                    added.

  M                 Mark/unmark all the lines between the top of the
                    display and the last line marked/unmarked.

  J                 Mark/unmark the next line after the previously
                    marked line.

  K                 Like 'J' except it toggles the mark on the
                    previous line.

  c                 Copy the marked text to the X11 selection buffer or OS X
                    clipboard.

  C                 Clear all marked lines.

Display options
---------------

  P                 Switch to/from the pretty-printed view of the log or text
                    files currently displayed.  In this view, structured data,
                    such as XML, will be reformatted to make it easier to read.

  t                 Switch to/from the text file view.  The text file view is
                    for any files that are not recognized as log files.

  =                 Pause/unpause loading of new file data.

  Ctrl-L            (Lo-fi mode) Exit screen-mode and write the
                    displayed log lines in plain text to the terminal
                    until a key is pressed.  Useful for copying long lines
                    from the terminal without picking up any of the extra
                    decorations.

  T                 Toggle the display of the "elapsed time" column that shows
                    the time elapsed since the beginning of the logs or the
                    offset from the previous bookmark.  Sharp changes in the
                    message rate are highlighted by coloring the separator
                    between the time column and the log message.  A red
                    highlight means the message rate has slowed down and green
                    means it has sped up.  You can use the "s/S" hotkeys to
                    scan through the slow downs.

  i                 View/leave a histogram of the log messages over
                    time.  The histogram counts the number of
                    displayed log lines for each bucket of time.  The
                    bars are layed out horizontally with colored
                    segments representing the different log levels.
                    You can use the 'z' hotkey to change the size of
                    the time buckets (e.g. ten minutes, one hour, one
                    day).

  I                 Switch between the log and histogram views while
                    keeping the time displayed at the top of each view
                    in sync.  For example, if the top line in the log
                    view is "11:40", hitting 'I' will switch to the
                    histogram view and scrolled to display "11:00" at
                    the top (if the zoom level is hours).

  z/Shift Z         Zoom in or out one step in the histogram view.

  v                 Switch to/from the SQL result view.

  V                 Switch between the log and SQL result views while
                    keeping the top line number in the log view in
                    sync with the log_line column in the SQL view.
                    For example, doing a query that selects for
                    "log_idle_msecs" and "log_line", you can move the
                    top of the SQL view to a line and hit 'V' to switch
                    to the log view and move to the line number that was
                    selected in the "log_line" column.  If there is no
                    "log_line" column, lnav will find the first column with
                    a timestamp and move to corresponding time in the log
                    view.

  TAB/Shift+TAB     In the SQL result view, cycle through the columns that
                    are graphed.  Initially, all number values are displayed
                    in a stacked graph.  Pressing TAB will change the display
                    to only graph the first column.  Repeatedly pressing TAB
                    will cycle through the columns until they are all graphed
                    again.

  p                 In the log view: enable or disable the display of the
                    fields that the log message parser knows about or has
                    discovered.  This overlay is temporarily enabled when the
                    semicolon key (;) is pressed so that it is easier to write
                    queries.

                    In the DB view: enable or disable the display of values
                    in columns containing JSON-encoded values in the top row.
                    The overlay will display the JSON-Pointer reference and
                    value for all fields in the JSON data.

  CTRL-W            Toggle word-wrapping.

  CTRL-P            Show/hide the data preview panel that may be opened when
                    entering commands or SQL queries.

  CTRL-F            Toggle the enabled/disabled state of all filters in the
                    current view.

  x                 Toggle the hiding of log message fields.  The hidden fields
                    will be replaced with three bullets and highlighted in
                    yellow.

  F2                Toggle mouse support.

Query
-----

  /<regexp>         Start a search for the given regular expression.
                    The search is live, so when there is a pause in
                    typing, the currently running search will be
                    canceled and a new one started.  The first ten lines
                    that match the search will be displayed in the preview
                    window at the bottom of the view.  History is
                    maintained for your searches so you can rerun them
                    easily.  Words that are currently displayed are also
                    available for tab-completion, so you can easily
                    search for values without needing to copy-and-paste
                    the string.  If there is an error encountered while
                    trying to interpret the expression, the error will
                    be displayed in red on the status line.  While the
                    search is active, the 'hits' field in the status
                    line will be green, when finished it will turn
                    back to black.

                    Note: The regular expression format used by is PCRE
                    (Perl-Compatible Regular Expressions).  For example,
                    if you wanted to search for ethernet device names,
                    regardless of their ID number, you can type:

                      eth\d+

                    You can find more information about Perl regular
                    expressions at:

                      http://perldoc.perl.org/perlre.html

                    If the search string is not valid PCRE, a search
                    is done for the exact string instead of doing a
                    regex search.

  :<command>        Execute an internal command.  The commands are
                    listed below.  History is also supported in this
                    context as well as tab-completion for commands and
                    some arguments.  The result of the command
                    replaces the command you typed.

  ;<sql>            Execute an SQL query.  Most supported log file
                    formats provide a sqlite virtual table backend
                    that can be used in queries.  See the SQL section
                    below for more information.

  |<script> [arg1 .. argN]
                    Execute an lnav script contained in a format directory
                    (e.g. ~/.lnav/formats/default).  The script can contain
                    lines starting with ':', ';', or '|' to execute commands,
                    SQL queries or execute other files in lnav.  Any values
                    after the script name are treated as arguments can be
                    referenced in the script using '$1', '$2', and so on, like
                    in a shell script.

  CTRL+], ESCAPE    Abort command-line entry started with '/', ':', ';', or '|'.

Session
-------

  CTRL-R            Reset the session state.  This will save the current
                    session state (filters, highlights) and then reset the
                    state to the factory default.

Filter Editor
-------------

The following hotkeys are only available when the focus is on the filter
editor.  You can change the focus by pressing TAB.

  q                 Switch the focus back to the main view.
  j/down-arrow      Select the next filter.
  k/up-arrow        Select the previous filter.
  o                 Create a new "out" filter.
  i                 Create a new "in" filter .
  SPACE             Toggle the enabled/disabled state of the currently
                    selected filter.
  t                 Toggle the type of filter between "in" and "out".
  ENTER             Edit the selected filter.
  D                 Delete the selected filter.


MOUSE SUPPORT (experimental)
============================

If you are using Xterm, or a compatible terminal, you can use the mouse to
mark lines of text and move the view by grabbing the scrollbar.

NOTE: You need to manually enable this feature by setting the LNAV_EXP
environment variable to "mouse".  F2 toggles mouse support.


COMMANDS
========

  help              Switch to this help text view.

  adjust-log-time <date|relative-time>
                    Change the time of the top log line to the given time or
                    adjusted by the relative time.  All other log lines in the
                    same file will also be adjusted using the same offset.
                    After the adjustment, the displayed timestamp will be
                    rewritten to the new time and highlighted with a magenta
                    color.

                    This command is useful for lining up log files that
                    have timestamps from different machines.

  unix-time <secs-or-date>
                    Convert a unix-timestamp in seconds to a
                    human-readable form or vice-versa.
                    BEWARE OF TIMEZONE DIFFERENCES.

  current-time      Print the current time in human-readable form and
                    as a unix-timestamp.

  goto <line#|N%|abs-time|relative-time>
                    Go to the given line number, N percent into the
                    file, or the given timestamp in the log view.  If the
                    line number is negative, it is considered an offset
                    from the last line.  Relative time values, like
                    'a minute ago', 'an hour later', and many other formats
                    are supported.  When using a relative time, the 'r/R'
                    hotkeys can be used to move the same amount again or in
                    the same amount in the opposite direction.

  relative-goto <line#|N%>
                    Move the current view up or down by the given amount.

  comment <message>
                    Add a comment to the top line in the log view.  The
                    comment will be saved in the session and will be available
                    the next time the file is loaded.  Searches will also scan
                    the comment for any matches.

  clear-comment     Clear the comment that is attached to the top line in the
                    log view.

  tag <tag1> [<tag2> [... <tagN>]]
                    Attach a tag to the top line in the log view.  The tags are
                    prefixed with a '#', if they don't have one already.  And,
                    like comments, they are saved in the session and
                    searchable.

  untag <tag1> [<tag2> [... <tagN>]]
                    Detach a tag from the top line in the log view.

  delete-tags <tag1> [<tag2> [... <tagN>]]
                    Detach the tags from all log lines.

  next-mark error|warning|search|user|file|meta
                    Move to the next bookmark of the given type in the
                    current view.

  prev-mark error|warning|search|user|file|meta
                    Move to the previous bookmark of the given type in the
                    current view.

  hide-lines-before <abs-time|relative-time>
                    Hide lines that are before the given time.  The given
                    time can be absolute (e.g. 2015-10-11)
                    The hidden lines can be shown again with the
                    'show-lines-before-and-after' command.

  hide-lines-after <abs-time|relative-time>
                    Hide lines that are after the given time.  The time can
                    The hidden lines can be shown again with the
                    'show-lines-before-and-after' command.

  show-lines-before-and-after
                    Show lines that were hidden by the 'hide-lines' commands.

  hide-unmarked-lines
                    Hide lines that have not been bookmarked.

  show-unmarked-lines
                    Show lines that have not been bookmarked.

  hide-fields <field-name> [<field-name2> ... <field-nameN>]
                    Hide large log message fields by replacing them with an
                    ellipsis.  You can quickly switching between showing and
                    hiding hidden fields using the 'x' hotkey.

  show-fields <field-name> [<field-name2> ... <field-nameN>]
                    Show log messages fields that were previously hidden with
                    the ':hide-fields' command.

  highlight <regex> Highlight strings that match the given regular
                    expression.

  clear-highlight <regex>
                    Clear an existing highlight created with the 'highlight'
                    command.

  filter-in <regex> Only display lines that match the given regular
                    expression.  This command can be used multiple
                    times to add more lines to the display.  The number
                    of lines that are filtered out will be shown in the
                    bottom status bar as 'Not Shown'.  Note that filtering
                    only works in the log and plain text views.  There is also
                    a limit of 32 filters per-view at any one time.

  filter-out <regex>
                    Do not display lines that match the given regular
                    expression.  This command can be used multiple
                    times to remove more lines from the display.  If a
                    'filter-in' expression is also active, it takes
                    priority and the filter-out will remove lines that
                    were matched by the 'filter-in'.  The number
                    of lines that are filtered out will be shown in the
                    bottom status bar as 'Not Shown'.  Note that filtering
                    only works in the log and plain text views.  There is also
                    a limit of 32 filters per-view at any one time.  While
                    entering the regular expression at the command-prompt, the
                    matches in the current text view will be highlighted in red
                    after a short delay.

  disable-filter <regex>
                    Disable an active 'filter-in' or 'filter-out'
                    expression.

  enable-filter <regex>
                    Enable a inactive 'filter-in' or 'filter-out'
                    expression.

  delete-filter <regex>
                    Permanently delete a filter.

  set-min-log-level <level>
                    Set the minimum level to display in the log view.
                    You can use TAB to view the possible values.

  disable-word-wrap Disable word wrapping in the log and text file views.
  enable-word-wrap  Enable word wrapping in the log and text file views.

  open <filename>[:<line>]
                    Open the given file within lnav and, if it is a
                    text file, switch to the text view and jump to
                    the given line number.

  close             Close the current text file or log file.  You can also
                    close the current file by pressing 'X'.

  spectrogram <field-name>
                    Generate a spectrogram for a numeric log message field or
                    SQL result column. The spectrogram view displays the range
                    of possible values of the field on the horizontal axis and
                    time on the vertical axis.  The horizontal axis is split
                    into buckets where each bucket counts how many log messages
                    contained the field with a value in that range.  The buckets
                    are colored based on the count in the bucket: green means
                    low, yellow means medium, and red means high.  The exact
                    ranges for the colors is computed automatically and
                    displayed in the middle of the top line of the view.  The
                    minimum and maximum values for the field are displayed in
                    the top left and right sides of the view, respectively.

  append-to <file>  Append any marked lines to the given file.

  write-to <file>   Write any marked lines to the given file.  Use '-' to
                    write the lines to the terminal.

  write-csv-to <file>
                    Write the results of a SQL query to a CSV-formatted file.
                    When running in non-interactive mode, a dash can be used
                    to write to standard out.  Use '-' to write the data to
                    the terminal.

  write-json-to <file>
                    Write the results of a SQL query to a JSON-formatted file.
                    The contents of the file will be an array of objects with
                    each column in the query being a field in the objects.
                    When running in non-interactive mode, a dash can be used
                    to write to standard out.  Use '-' to write the data to
                    the terminal.

  write-jsonlines-to <file>
                    Write the results of a SQL query to a JSON-Lines-formatted
                    file.  Each row in the result set will be a single line of
                    JSON in the output and each column will be a property in
                    that object.  Use '-' to write the data to the terminal.

  pipe-to <shell-cmd>
                    Send the currently marked lines to the given shell command
                    for processing and open the resulting file for viewing.

  pipe-line-to <shell-cmd>
                    Send the top log message to the given shell command
                    for processing and open the resulting file for viewing.
                    The known and discovered message fields are available as
                    environment variables.  For example, log_procname in a
                    syslog message.

  redirect-to <path>
                    If a path is given, all output from commands, like
                    ":echo" and when writing to stdout (e.g. :write-to -), will
                    be sent to the given file.  If no path is specified, the
                    current redirect will be cleared and output will be
                    captured as it was before the redirect was done.

  session <cmd>     Add the given command to the session file
                    (~/.lnav/session).  Any commands listed in the session file
                    are executed on startup.  Only the highlight, word-wrap, and
                    filter-related commands can be added to the session file.

  create-logline-table <table-name>
                    Create an SQL table using the top line of the log view
                    as a template.  See the "SQL QUERIES" and "DYNAMIC LOG
                    LINE TABLE" sections below for more information.

  delete-logline-table <table-name>
                    Delete an SQL table created by the 'create-logline-table'
                    command.

  create-search-table <table-name> [<regex>]
                    Create an SQL table that extracts information from logs
                    using the provided regular expression or the last search
                    that was done.  Any captures in the expression will be
                    used as columns in the SQL table.  If the capture is named,
                    that name will be used as the column name, otherwise the
                    column name will be of the form 'col_N'.

  delete-search-table <table-name>
                    Delete a table that was created with create-search-table.

  switch-to-view <view-name>
                    Switch the display to the given view, which can be one of:
                    help, log, text, histogram, db, and schema.

  zoom-to <zoom-level>
                    Change the histogram zoom level to the given value, which
                    can be one of: day, 4-hour, hour, 10-minute, minute

  redraw            Force redraw the window.

  partition-name <name>
                    Mark the top line in the log view as the start of a new
                    partition with the given name.  The current partition name
                    will be reflected in the top status bar next to the current
                    time as well as being available in the 'log_part' column
                    of the SQL log tables.  Partitions can be used to make it
                    easier to query subsections of log messages.

  clear-partition
                    Clear the partition the top line is a part of.

  echo [-n] <msg>   Display the given message.  Useful for scripts to message
                    the user.  The '-n' option leaves out the new line at the
                    end of the message.

  eval <cmd|query|file>
                    Execute the given command, query, or file after doing
                    environment variable substitution.  The argument to this
                    command should start with a ':', ';', or '|' signify the
                    type of action to perform (command, SQL query, execute
                    script).

  pt-min-time [<date>|<relative-time>]
                    Set/get the minimum time range for any papertrail queries.
                    Absolute or relative time values can be specified.

  pt-max-time [<date>|<relative-time>]
                    Set/get the maximum time range for any papertrail queries.
                    Absolute or relative time values can be specified.

  config <option> [value]
                    Set/get the value of a configuration option.

  reset-config <option>
                    Reset a configuration option to the default value.  Use
                    '*' to reset all options.

  quit              Quit lnav.

SQL QUERIES (experimental)
===========

Lnav has support for performing SQL queries on log files using the
Sqlite3 "virtual" table feature.  For all supported log file types,
lnav will create tables that can be queried using the subset of SQL
that is supported by Sqlite3.  For example, to get the top ten URLs
being accessed in any loaded Apache log files, you can execute:

  ;select cs_uri_stem, count(*) as total from access_log 
     group by cs_uri_stem order by total desc limit 10;

The query result view shows the results and graphs any numeric
values found in the result, much like the histogram view.

The builtin set of log tables are listed below.  Note that only the
log messages that match a particular format can be queried by a
particular table.  You can find the file format and table name for
the top log message by looking in the upper right hand corner of the
log file view.

Some commonly used format tables are:

  access_log       Apache common access log format
  syslog_log       Syslog format
  strace_log       Strace log format
  generic_log      'Generic' log format.  This table contains messages
                   from files that have a very simple format with a
                   leading timestamp followed by the message.

NOTE: You can get a dump of the schema for the internal tables, and
any attached databases, by running the '.schema' SQL command.

The columns available for the top log line in the view will
automatically be displayed after pressing the semicolon (;) key.
All log tables contain at least the following columns:

    log_line        The line number in the file, starting at zero.
    log_part        The name of the partition.  You can change this
                    column using an UPDATE SQL statement or with the
                    'partition-name' command.  After a value is set,
                    the following log messages will have the same
                    partition name up until another name is set.
    log_time        The time of the log entry.
    log_idle_msecs  The amount of time, in milliseconds, between the
                    current log message and the previous one.
    log_level       The log level (e.g. info, error, etc...).
    log_mark        The bookmark status for the line.  This column
                    can be written to using an UPDATE query.
    log_path        The full path to the file.
    log_text        The raw line of text.  Note that this column is
                    not included in the result of a 'select *', but
                    it does exist.

The following tables include the basic columns as listed above and
include a few more columns since the log file format is more
structured.

  syslog_log

    log_hostname   The hostname the message was received from.
    log_procname   The name of the process that sent the message.
    log_pid        The process ID of the process that sent the message.

  access_log       (The column names are the same as those in the
                    Microsoft LogParser tool.)

    c_ip           The client IP address.
    cs_username    The client user name.
    cs_method      The HTTP method.
    cs_uri_stem    The stem portion of the URI.
    cs_uri_query   The query portion of the URI.
    cs_version     The HTTP version string.
    sc_status      The status number returned to the client.
    sc_bytes       The number of bytes sent to the client.
    cs_referrer    The URL of the referring page.
    cs_user_agent  The user agent string.

  strace_log       (Currently, you need to run strace with the
                    "-tt -T" options so there are timestamps for
                    each function call.)

    funcname       The name of the syscall.
    result         The result code.
    duration       The amount of time spent in the syscall.
    arg0 - arg9    The arguments passed to the syscall.

These tables are created dynamically and not stored in memory or on
disk.  If you would like to persist some information from the tables,
you can attach another database and create tables in that database.
For example, if you wanted to save the results from the earlier
example of a top ten query into the "/tmp/topten.db" file, you can do:

  ;attach database "/tmp/topten.db" as topten;
  ;create table topten.foo as select cs_uri_stem, count(*) as total
     from access_log group by cs_uri_stem order by total desc
     limit 10;


DYNAMIC LOG LINE TABLE (experimental)
======================

(NOTE: This feature is still very new and not completely reliable yet,
 use with care.)

For log formats that lack message structure, lnav can parse the log
message and attempt to extract any data fields that it finds.  This
feature is available through the "logline" log table.  This table is
dynamically created and defined based on the message at the top of
the log view.  For example, given the following log message from "sudo",
lnav will create the "logline" table with columns for "TTY", "PWD",
"USER", and "COMMAND":

  May 24 06:48:38 Tim-Stacks-iMac.local sudo[76387]: stack : TTY=ttys003 ; 
    PWD=/Users/stack/github/lbuild ; USER=root ;
    COMMAND=/bin/echo Hello, World!

Queries executed against this table will then only return results for
other log messages that have the same format.  So, if you were to
execute the following query while viewing the above line, you might
get the following results:

  ;select USER,COMMAND from logline;

  USER | COMMAND
  ---- | -------------------------
  root | /bin/echo Hello, World!
  mal  | /bin/echo Goodbye, World!


The log parser works by examining each message for key/value pairs
separated by an equal sign (=) or a colon (:).  For example, in the
previous example of a "sudo" message, the parser sees the "USER=root"
string as a pair where the key is "USER" and the value is "root".
If no pairs can be found, then anything that looks like a value is
extracted and assigned a numbered column.  For example, the following
line is from "dhcpd":

  Sep 16 22:35:57 drill dhcpd: DHCPDISCOVER from 00:16:ce:54:4e:f3 via hme3

In this case, the lnav parser recognizes that "DHCPDISCOVER", the MAC
address and the "hme3" device name are values and not normal words.  So,
it builds a table with three columns for each of these values.  The
regular words in the message, like "from" and "via", are then used to
find other messages with a similar format.

If you would like to execute queries against log messages of different
formats at the same time, you can use the 'create-logline-table' command
to permanently create a table using the top line of the log view as a
template.


OTHER SQL FEATURES
==================

Environment variables can be used in SQL statements by prefixing the
variable name with a dollar-sign ($).  For example, to read the value of
the HOME variable, you can do:

  ;SELECT $HOME;

To select the syslog messages that have a hostname field that is equal
to the HOSTNAME variable:

  ;SELECT * FROM syslog_log WHERE log_hostname = $HOSTNAME;

NOTE: Variable substitution is done for fields in the query and is not
a plain text substitution.  For example, the following statement
WILL NOT WORK:

  ;SELECT * FROM $TABLE_NAME; -- Syntax error


Access to lnav's environment variables is also available via the "environ"
table.  The table has two columns (name, value) and can be read and written
to using SQL SELECT, INSERT, UPDATE, and DELETE statements.  For example,
to set the "FOO" variable to the value "BAR":

  ;INSERT INTO environ SELECT 'FOO', 'BAR';

As a more complex example, you can set the variable "LAST" to the last
syslog line number by doing:

  ;INSERT INTO environ SELECT 'LAST', (SELECT max(log_line) FROM syslog_log);

A delete will unset the environment variable:

  ;DELETE FROM environ WHERE name='LAST';

The table allows you to easily use the results of a SQL query in lnav
commands, which is especially useful when scripting lnav.


PAPERTRAIL INTEGRATION
======================

Papertrail is a log management service with free and paid plans at:

  http://papertrailapp.com

To configure lnav to communicate with the papertrail service, you will
need to set the PAPERTRAIL_API_TOKEN environment variable.  You can
get your API token from your user profile, available here:

  https://papertrailapp.com/user/edit

Searching papertrail using lnav can be done by prefixing the search terms
with "pt:" and passing the value as a file name.  For example, to search
for log messages with the string 'Critical Error' when starting lnav you
can do the following:

  $ setenv PAPERTRAIL_API_TOKEN xxxxxxxxx
  $ lnav "pt:'Critical Error'"

If lnav is already started, you can use the ':open' command like so:

  :open pt:'Critical Error'

If you just want to tail your logs in papertrail, you can pass an empty
search string (i.e. "pt:").

Only one papertrail search can be active at a time.  So, if an ':open'
is done with a new query, the previous query will be closed first.


CONTACT
=======

For more information, visit the lnav website at:

  http://lnav.org

For support questions, email:

  lnav@googlegroups.com


REFERENCE
=========


Synopsis
  CAST(expr AS type-name) -- Convert the value of the given expression to a 
    different storage class specified by type-name.
Parameters
  expr        The value to convert.
  type-name   The name of the type to convert to.

Example
#1 To cast the value 1.23 as an integer:
   ;SELECT CAST(1.23 AS INTEGER)
   


Synopsis
  OVER([base-window-name] PARTITION BY expr, ... ORDER BY expr, ..., 
       [frame-spec]) -- 
       Executes the preceding function over a window
Parameters
  base-window-name   The name of the window definition
  expr               The values to use for partitioning
  expr               The values used to order the rows in the window
  frame-spec         Determines which output rows are read by an aggregate 
                     window function


Synopsis
  abs(x) -- Return the absolute value of the argument
Parameter
  x   The number to convert
See Also
  acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(), atn2(), avg(), 
  ceil(), degrees(), exp(), floor(), log(), log10(), max(), min(), pi(), power(), 
  radians(), round(), sign(), square(), sum(), total()
Example
#1 To get the absolute value of -1:
   ;SELECT abs(-1)
   


Synopsis
  acos(num) -- Returns the arccosine of a number, in radians
Parameter
  num   A cosine value that is between -1 and 1
See Also
  abs(), acosh(), asin(), asinh(), atan(), atan2(), atanh(), atn2(), avg(), 
  ceil(), degrees(), exp(), floor(), log(), log10(), max(), min(), pi(), power(), 
  radians(), round(), sign(), square(), sum(), total()
Example
#1 To get the arccosine of 0.2:
   ;SELECT acos(0.2)
   


Synopsis
  acosh(num) -- Returns the hyperbolic arccosine of a number
Parameter
  num   A number that is one or more
See Also
  abs(), acos(), asin(), asinh(), atan(), atan2(), atanh(), atn2(), avg(), ceil(), 
  degrees(), exp(), floor(), log(), log10(), max(), min(), pi(), power(), 
  radians(), round(), sign(), square(), sum(), total()
Example
#1 To get the hyperbolic arccosine of 1.2:
   ;SELECT acosh(1.2)
   


Synopsis
  asin(num) -- Returns the arcsine of a number, in radians
Parameter
  num   A sine value that is between -1 and 1
See Also
  abs(), acos(), acosh(), asinh(), atan(), atan2(), atanh(), atn2(), avg(), 
  ceil(), degrees(), exp(), floor(), log(), log10(), max(), min(), pi(), power(), 
  radians(), round(), sign(), square(), sum(), total()
Example
#1 To get the arcsine of 0.2:
   ;SELECT asin(0.2)
   


Synopsis
  asinh(num) -- Returns the hyperbolic arcsine of a number
Parameter
  num   The number
See Also
  abs(), acos(), acosh(), asin(), atan(), atan2(), atanh(), atn2(), avg(), ceil(), 
  degrees(), exp(), floor(), log(), log10(), max(), min(), pi(), power(), 
  radians(), round(), sign(), square(), sum(), total()
Example
#1 To get the hyperbolic arcsine of 0.2:
   ;SELECT asinh(0.2)
   


Synopsis
  atan(num) -- Returns the arctangent of a number, in radians
Parameter
  num   The number
See Also
  abs(), acos(), acosh(), asin(), asinh(), atan2(), atanh(), atn2(), avg(), 
  ceil(), degrees(), exp(), floor(), log(), log10(), max(), min(), pi(), power(), 
  radians(), round(), sign(), square(), sum(), total()
Example
#1 To get the arctangent of 0.2:
   ;SELECT atan(0.2)
   


Synopsis
  atan2(y, x) -- Returns the angle in the plane between the positive X axis and
    the ray from (0, 0) to the point (x, y)
Parameters
  y   The y coordinate of the point
  x   The x coordinate of the point
See Also
  abs(), acos(), acosh(), asin(), asinh(), atan(), atanh(), atn2(), avg(), ceil(), 
  degrees(), exp(), floor(), log(), log10(), max(), min(), pi(), power(), 
  radians(), round(), sign(), square(), sum(), total()
Example
#1 To get the angle, in degrees, for the point at (5, 5):
   ;SELECT degrees(atan2(5, 5))
   


Synopsis
  atanh(num) -- Returns the hyperbolic arctangent of a number
Parameter
  num   The number
See Also
  abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atn2(), avg(), ceil(), 
  degrees(), exp(), floor(), log(), log10(), max(), min(), pi(), power(), 
  radians(), round(), sign(), square(), sum(), total()
Example
#1 To get the hyperbolic arctangent of 0.2:
   ;SELECT atanh(0.2)
   


Synopsis
  atn2(y, x) -- Returns the angle in the plane between the positive X axis and 
    the ray from (0, 0) to the point (x, y)
Parameters
  y   The y coordinate of the point
  x   The x coordinate of the point
See Also
  abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(), avg(), 
  ceil(), degrees(), exp(), floor(), log(), log10(), max(), min(), pi(), power(), 
  radians(), round(), sign(), square(), sum(), total()
Example
#1 To get the angle, in degrees, for the point at (5, 5):
   ;SELECT degrees(atn2(5, 5))
   


Synopsis
  avg(X) -- Returns the average value of all non-NULL numbers within a group.
Parameter
  X   The value to compute the average of.
See Also
  abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(), atn2(), 
  ceil(), degrees(), exp(), floor(), log(), log10(), max(), min(), pi(), power(), 
  radians(), round(), sign(), square(), sum(), total()
Examples
#1 To get the average of the column 'ex_duration' from the table 'lnav_example_log':
   ;SELECT avg(ex_duration) FROM lnav_example_log
   

#2 To get the average of the column 'ex_duration' from the table 'lnav_example_log' when grouped by 'ex_procname':
   ;SELECT ex_procname, avg(ex_duration) FROM lnav_example_log GROUP BY ex_procname
   


Synopsis
  basename(path) -- Extract the base portion of a pathname.
Parameter
  path   The path
See Also
  dirname(), joinpath(), readlink(), realpath()
Examples
#1 To get the base of a plain file name:
   ;SELECT basename('foobar')
   

#2 To get the base of a path:
   ;SELECT basename('foo/bar')
   

#3 To get the base of a directory:
   ;SELECT basename('foo/bar/')
   

#4 To get the base of an empty string:
   ;SELECT basename('')
   

#5 To get the base of a Windows path:
   ;SELECT basename('foo\bar')
   

#6 To get the base of the root directory:
   ;SELECT basename('/')
   


Synopsis
  ceil(num) -- Returns the smallest integer that is not less than the argument
Parameter
  num   The number to raise to the ceiling
See Also
  abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(), atn2(), 
  avg(), degrees(), exp(), floor(), log(), log10(), max(), min(), pi(), power(), 
  radians(), round(), sign(), square(), sum(), total()
Example
#1 To get the ceiling of 1.23:
   ;SELECT ceil(1.23)
   


Synopsis
  changes() -- The number of database rows that were changed, inserted, or 
    deleted by the most recent statement.


Synopsis
  char(X, ...) -- Returns a string composed of characters having the given 
    unicode code point values
Parameter
  X   The unicode code point values
See Also
  charindex(), endswith(), extract(), group_concat(), group_spooky_hash(), 
  instr(), leftstr(), length(), lower(), ltrim(), padc(), padl(), padr(), 
  printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(), 
  replace(), replicate(), reverse(), rightstr(), rtrim(), spooky_hash(), 
  startswith(), strfilter(), substr(), trim(), unicode(), upper()
Example
#1 To get a string with the code points 0x48 and 0x49:
   ;SELECT char(0x48, 0x49)
   


Synopsis
  charindex(needle, haystack, [start]) -- Finds the first occurrence of the 
    needle within the haystack and returns the number of prior characters plus 
    1, or 0 if Y is nowhere found within X
Parameters
  needle     The string to look for in the haystack
  haystack   The string to search within
  start      The one-based index within the haystack to start the search
See Also
  char(), endswith(), extract(), group_concat(), group_spooky_hash(), instr(), 
  leftstr(), length(), lower(), ltrim(), padc(), padl(), padr(), printf(), 
  proper(), regexp_capture(), regexp_match(), regexp_replace(), replace(), 
  replicate(), reverse(), rightstr(), rtrim(), spooky_hash(), startswith(), 
  strfilter(), substr(), trim(), unicode(), upper()
Examples
#1 To search for the string 'abc' within 'abcabc' and starting at position 2:
   ;SELECT charindex('abc', 'abcabc', 2)
   

#2 To search for the string 'abc' within 'abcdef' and starting at position 2:
   ;SELECT charindex('abc', 'abcdef', 2)
   


Synopsis
  coalesce(X, Y, ...) -- Returns a copy of its first non-NULL argument, or NULL
    if all arguments are NULL
Parameters
  X   A value to check for NULL-ness
  Y   A value to check for NULL-ness

Example
#1 To get the first non-null value from three parameters:
   ;SELECT coalesce(null, 0, null)
   


Synopsis
  count(X) -- If the argument is '*', the total number of rows in the group is 
    returned.  Otherwise, the number of times the argument is non-NULL.
Parameter
  X   The value to count.

Examples
#1 To get the count of the non-NULL rows of 'lnav_example_log':
   ;SELECT count(*) FROM lnav_example_log
   

#2 To get the count of the non-NULL values of 'log_part' from 'lnav_example_log':
   ;SELECT count(log_part) FROM lnav_example_log
   


Synopsis
  cume_dist() -- Returns the cumulative distribution
See Also
  dense_rank(), first_value(), lag(), last_value(), lead(), nth_value(), ntile(), 
  percent_rank(), rank(), row_number()

Synopsis
  date(timestring, modifier, ...) -- Returns the date in this format: YYYY-MM-
    DD.
Parameters
  timestring   The string to convert to a date.
  modifier     A transformation that is applied to the value to the left.
See Also
  datetime(), julianday(), strftime(), time(), timediff(), timeslice()
Examples
#1 To get the date portion of the timestamp '2017-01-02T03:04:05':
   ;SELECT date('2017-01-02T03:04:05')
   

#2 To get the date portion of the timestamp '2017-01-02T03:04:05' plus one day:
   ;SELECT date('2017-01-02T03:04:05', '+1 day')
   

#3 To get the date portion of the epoch timestamp 1491341842:
   ;SELECT date(1491341842, 'unixepoch')
   


Synopsis
  datetime(timestring, modifier, ...) -- Returns the date and time in this 
    format: YYYY-MM-DD HH:MM:SS.
Parameters
  timestring   The string to convert to a date with time.
  modifier     A transformation that is applied to the value to the left.
See Also
  date(), julianday(), strftime(), time(), timediff(), timeslice()
Examples
#1 To get the date and time portion of the timestamp '2017-01-02T03:04:05':
   ;SELECT datetime('2017-01-02T03:04:05')
   

#2 To get the date and time portion of the timestamp '2017-01-02T03:04:05' plus one minute:
   ;SELECT datetime('2017-01-02T03:04:05', '+1 minute')
   

#3 To get the date and time portion of the epoch timestamp 1491341842:
   ;SELECT datetime(1491341842, 'unixepoch')
   


Synopsis
  degrees(radians) -- Converts radians to degrees
Parameter
  radians   The radians value to convert to degrees
See Also
  abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(), atn2(), 
  avg(), ceil(), exp(), floor(), log(), log10(), max(), min(), pi(), power(), 
  radians(), round(), sign(), square(), sum(), total()
Example
#1 To convert PI to degrees:
   ;SELECT degrees(pi())
   


Synopsis
  dense_rank() -- Returns the row_number() of the first peer in each group 
    without gaps
See Also
  cume_dist(), first_value(), lag(), last_value(), lead(), nth_value(), ntile(), 
  percent_rank(), rank(), row_number()

Synopsis
  dirname(path) -- Extract the directory portion of a pathname.
Parameter
  path   The path
See Also
  basename(), joinpath(), readlink(), realpath()
Examples
#1 To get the directory of a relative file path:
   ;SELECT dirname('foo/bar')
   

#2 To get the directory of an absolute file path:
   ;SELECT dirname('/foo/bar')
   

#3 To get the directory of a file in the root directory:
   ;SELECT dirname('/bar')
   

#4 To get the directory of a Windows path:
   ;SELECT dirname('foo\bar')
   

#5 To get the directory of an empty path:
   ;SELECT dirname('')
   


Synopsis
  endswith(str, suffix) -- Test if a string ends with the given suffix
Parameters
  str      The string to test
  suffix   The suffix to check in the string
See Also
  char(), charindex(), extract(), group_concat(), group_spooky_hash(), instr(), 
  leftstr(), length(), lower(), ltrim(), padc(), padl(), padr(), printf(), 
  proper(), regexp_capture(), regexp_match(), regexp_replace(), replace(), 
  replicate(), reverse(), rightstr(), rtrim(), spooky_hash(), startswith(), 
  strfilter(), substr(), trim(), unicode(), upper()
Examples
#1 To test if the string 'notbad.jpg' ends with '.jpg':
   ;SELECT endswith('notbad.jpg', '.jpg')
   

#2 To test if the string 'notbad.png' starts with '.jpg':
   ;SELECT endswith('notbad.png', '.jpg')
   


Synopsis
  exp(x) -- Returns the value of e raised to the power of x
Parameter
  x   The exponent
See Also
  abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(), atn2(), 
  avg(), ceil(), degrees(), floor(), log(), log10(), max(), min(), pi(), power(), 
  radians(), round(), sign(), square(), sum(), total()
Example
#1 To raise e to 2:
   ;SELECT exp(2)
   


Synopsis
  extract(str) -- Automatically Parse and extract data from a string
Parameter
  str   The string to parse
See Also
  char(), charindex(), endswith(), group_concat(), group_spooky_hash(), instr(), 
  leftstr(), length(), lower(), ltrim(), padc(), padl(), padr(), printf(), 
  proper(), regexp_capture(), regexp_match(), regexp_replace(), replace(), 
  replicate(), reverse(), rightstr(), rtrim(), spooky_hash(), startswith(), 
  strfilter(), substr(), trim(), unicode(), upper()
Examples
#1 To extract key/value pairs from a string:
   ;SELECT extract('foo=1 bar=2 name="Rolo Tomassi"')
   

#2 To extract columnar data from a string:
   ;SELECT extract('1.0 abc 2.0')
   


Synopsis
  first_value(expr) -- Returns the result of evaluating the expression against 
    the first row in the window frame.
Parameter
  expr   The expression to execute over the first row
See Also
  cume_dist(), dense_rank(), lag(), last_value(), lead(), nth_value(), ntile(), 
  percent_rank(), rank(), row_number()

Synopsis
  floor(num) -- Returns the largest integer that is not greater than the 
    argument
Parameter
  num   The number to lower to the floor
See Also
  abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(), atn2(), 
  avg(), ceil(), degrees(), exp(), log(), log10(), max(), min(), pi(), power(), 
  radians(), round(), sign(), square(), sum(), total()
Example
#1 To get the floor of 1.23:
   ;SELECT floor(1.23)
   


Synopsis
  gethostbyaddr(hostname) -- Get the hostname for the given IP address
Parameter
  hostname   The IP address to lookup.
See Also
  gethostbyname()
Example
#1 To get the hostname for the IP '127.0.0.1':
   ;SELECT gethostbyaddr('127.0.0.1')
   


Synopsis
  gethostbyname(hostname) -- Get the IP address for the given hostname
Parameter
  hostname   The DNS hostname to lookup.
See Also
  gethostbyaddr()
Example
#1 To get the IP address for 'localhost':
   ;SELECT gethostbyname('localhost')
   


Synopsis
  glob(pattern, str) -- Match a string against Unix glob pattern
Parameters
  pattern   The glob pattern
  str       The string to match

Example
#1 To test if the string 'abc' matches the glob 'a*':
   ;SELECT glob('a*', 'abc')
   


Synopsis
  group_concat(X, [sep]) -- Returns a string which is the concatenation of all 
    non-NULL values of X separated by a comma or the given separator.
Parameters
  X     The value to concatenate.
  sep   The separator to place between the values.
See Also
  char(), charindex(), endswith(), extract(), group_spooky_hash(), instr(), 
  leftstr(), length(), lower(), ltrim(), padc(), padl(), padr(), printf(), 
  proper(), regexp_capture(), regexp_match(), regexp_replace(), replace(), 
  replicate(), reverse(), rightstr(), rtrim(), spooky_hash(), startswith(), 
  strfilter(), substr(), trim(), unicode(), upper()
Examples
#1 To concatenate the values of the column 'ex_procname' from the table 'lnav_example_log':
   ;SELECT group_concat(ex_procname) FROM lnav_example_log
   

#2 To join the values of the column 'ex_procname' using the string ', ':
   ;SELECT group_concat(ex_procname, ', ') FROM lnav_example_log
   

#3 To concatenate the distinct values of the column 'ex_procname' from the table 'lnav_example_log':
   ;SELECT group_concat(DISTINCT ex_procname) FROM lnav_example_log
   


Synopsis
  group_spooky_hash(str, ...) -- Compute the hash value for the given arguments
    
Parameter
  str   The string to hash
See Also
  char(), charindex(), endswith(), extract(), group_concat(), instr(), leftstr(), 
  length(), lower(), ltrim(), padc(), padl(), padr(), printf(), proper(), 
  regexp_capture(), regexp_match(), regexp_replace(), replace(), replicate(), 
  reverse(), rightstr(), rtrim(), spooky_hash(), startswith(), strfilter(), 
  substr(), trim(), unicode(), upper()
Example
#1 To produce a hash of all of the values of 'column1':
   ;SELECT group_spooky_hash(column1) FROM (VALUES ('abc'), ('123'))
   


Synopsis
  hex(X) -- Returns a string which is the upper-case hexadecimal rendering of 
    the content of its argument.
Parameter
  X   The blob to convert to hexadecimal

Example
#1 To get the hexadecimal rendering of the string 'abc':
   ;SELECT hex('abc')
   


Synopsis
  ifnull(X, Y) -- Returns a copy of its first non-NULL argument, or NULL if 
    both arguments are NULL
Parameters
  X   A value to check for NULL-ness
  Y   A value to check for NULL-ness

Example
#1 To get the first non-null value between null and zero:
   ;SELECT ifnull(null, 0)
   


Synopsis
  instr(haystack, needle) -- Finds the first occurrence of the needle within 
    the haystack and returns the number of prior characters plus 1, or 0 if the
    needle was not found
Parameters
  haystack   The string to search within
  needle     The string to look for in the haystack
See Also
  char(), charindex(), endswith(), extract(), group_concat(), group_spooky_hash(), 
  leftstr(), length(), lower(), ltrim(), padc(), padl(), padr(), printf(), 
  proper(), regexp_capture(), regexp_match(), regexp_replace(), replace(), 
  replicate(), reverse(), rightstr(), rtrim(), spooky_hash(), startswith(), 
  strfilter(), substr(), trim(), unicode(), upper()
Example
#1 To test get the position of 'b' in the string 'abc':
   ;SELECT instr('abc', 'b')
   


Synopsis
  jget(json, ptr, [default]) -- Get the value from a JSON object using a JSON-
    Pointer.
Parameters
  json      The JSON object to query.
  ptr       The JSON-Pointer to lookup in the object.
  default   The default value if the value was not found
See Also
  json_concat(), json_contains(), json_group_array(), json_group_object()
Examples
#1 To get the root of a JSON value:
   ;SELECT jget('1', '')
   

#2 To get the property named 'b' in a JSON object:
   ;SELECT jget('{ "a": 1, "b": 2 }', '/b')
   

#3 To get the 'msg' property and return a default if it does not exist:
   ;SELECT jget(null, '/msg', 'Hello')
   


Synopsis
  joinpath(path, ...) -- Join components of a path together.
Parameter
  path   One or more path components to join together.  If an argument starts 
         with a forward or backward slash, it will be considered an absolute 
         path and any preceding elements will be ignored.
See Also
  basename(), dirname(), readlink(), realpath()
Examples
#1 To join a directory and file name into a relative path:
   ;SELECT joinpath('foo', 'bar')
   

#2 To join an empty component with other names into a relative path:
   ;SELECT joinpath('', 'foo', 'bar')
   

#3 To create an absolute path with two path components:
   ;SELECT joinpath('/', 'foo', 'bar')
   

#4 To create an absolute path from a path component that starts with a forward slash:
   ;SELECT joinpath('/', 'foo', '/bar')
   


Synopsis
  json_concat(json, value, ...) -- Returns an array with the given values 
    concatenated onto the end.  If the initial value is null, the result will 
    be an array with the given elements.  If the initial value is an array, the
    result will be an array with the given values at the end.  If the initial 
    value is not null or an array, the result will be an array with two 
    elements: the initial value and the given value.
Parameters
  json    The initial JSON value.
  value   The value(s) to add to the end of the array.
See Also
  jget(), json_contains(), json_group_array(), json_group_object()
Examples
#1 To append the number 4 to null:
   ;SELECT json_concat(NULL, 4)
   

#2 To append 4 and 5 to the array [1, 2, 3]:
   ;SELECT json_concat('[1, 2, 3]', 4, 5)
   

#3 To concatenate two arrays together:
   ;SELECT json_concat('[1, 2, 3]', json('[4, 5]'))
   


Synopsis
  json_contains(json, value) -- Check if a JSON value contains the given 
    element.
Parameters
  json    The JSON value to query.
  value   The value to look for in the first argument
See Also
  jget(), json_concat(), json_group_array(), json_group_object()
Examples
#1 To test if a JSON array contains the number 4:
   ;SELECT json_contains('[1, 2, 3]', 4)
   

#2 To test if a JSON array contains the string 'def':
   ;SELECT json_contains('["abc", "def"]', 'def')
   


Synopsis
  json_group_array(value, ...) -- Collect the given values from a query into a 
    JSON array
Parameter
  value   The values to append to the array
See Also
  jget(), json_concat(), json_contains(), json_group_object()
Examples
#1 To create an array from arguments:
   ;SELECT json_group_array('one', 2, 3.4)
   

#2 To create an array from a column of values:
   ;SELECT json_group_array(column1) FROM (VALUES (1), (2), (3))
   


Synopsis
  json_group_object(name, value, ...) -- Collect the given values from a query 
    into a JSON object
Parameters
  name    The property name for the value
  value   The value to add to the object
See Also
  jget(), json_concat(), json_contains(), json_group_array()
Examples
#1 To create an object from arguments:
   ;SELECT json_group_object('a', 1, 'b', 2)
   

#2 To create an object from a pair of columns:
   ;SELECT json_group_object(column1, column2) FROM (VALUES ('a', 1), ('b', 2))
   


Synopsis
  julianday(timestring, modifier, ...) -- Returns the number of days since noon
    in Greenwich on November 24, 4714 B.C.
Parameters
  timestring   The string to convert to a date with time.
  modifier     A transformation that is applied to the value to the left.
See Also
  date(), datetime(), strftime(), time(), timediff(), timeslice()
Examples
#1 To get the julian day from the timestamp '2017-01-02T03:04:05':
   ;SELECT julianday('2017-01-02T03:04:05')
   

#2 To get the julian day from the timestamp '2017-01-02T03:04:05' plus one minute:
   ;SELECT julianday('2017-01-02T03:04:05', '+1 minute')
   

#3 To get the julian day from the timestamp 1491341842:
   ;SELECT julianday(1491341842, 'unixepoch')
   


Synopsis
  lag(expr, [offset], [default]) -- Returns the result of evaluating the 
    expression against the previous row in the partition.
Parameters
  expr      The expression to execute over the previous row
  offset    The offset from the current row in the partition
  default   The default value if the previous row does not exist instead of 
            NULL
See Also
  cume_dist(), dense_rank(), first_value(), last_value(), lead(), nth_value(), 
  ntile(), percent_rank(), rank(), row_number()

Synopsis
  last_insert_rowid() -- Returns the ROWID of the last row insert from the 
    database connection which invoked the function


Synopsis
  last_value(expr) -- Returns the result of evaluating the expression against 
    the last row in the window frame.
Parameter
  expr   The expression to execute over the last row
See Also
  cume_dist(), dense_rank(), first_value(), lag(), lead(), nth_value(), ntile(), 
  percent_rank(), rank(), row_number()

Synopsis
  lead(expr, [offset], [default]) -- Returns the result of evaluating the 
    expression against the next row in the partition.
Parameters
  expr      The expression to execute over the next row
  offset    The offset from the current row in the partition
  default   The default value if the next row does not exist instead of NULL
See Also
  cume_dist(), dense_rank(), first_value(), lag(), last_value(), nth_value(), 
  ntile(), percent_rank(), rank(), row_number()

Synopsis
  leftstr(str, N) -- Returns the N leftmost (UTF-8) characters in the given 
    string.
Parameters
  str   The string to return subset.
  N     The number of characters from the left side of the string to return.
See Also
  char(), charindex(), endswith(), extract(), group_concat(), group_spooky_hash(), 
  instr(), length(), lower(), ltrim(), padc(), padl(), padr(), printf(), proper(), 
  regexp_capture(), regexp_match(), regexp_replace(), replace(), replicate(), 
  reverse(), rightstr(), rtrim(), spooky_hash(), startswith(), strfilter(), 
  substr(), trim(), unicode(), upper()
Examples
#1 To get the first character of the string 'abc':
   ;SELECT leftstr('abc', 1)
   

#2 To get the first ten characters of a string, regardless of size:
   ;SELECT leftstr('abc', 10)
   


Synopsis
  length(str) -- Returns the number of characters (not bytes) in the given 
    string prior to the first NUL character
Parameter
  str   The string to determine the length of
See Also
  char(), charindex(), endswith(), extract(), group_concat(), group_spooky_hash(), 
  instr(), leftstr(), lower(), ltrim(), padc(), padl(), padr(), printf(), 
  proper(), regexp_capture(), regexp_match(), regexp_replace(), replace(), 
  replicate(), reverse(), rightstr(), rtrim(), spooky_hash(), startswith(), 
  strfilter(), substr(), trim(), unicode(), upper()
Example
#1 To get the length of the string 'abc':
   ;SELECT length('abc')
   


Synopsis
  like(pattern, str, [escape]) -- Match a string against a pattern
Parameters
  pattern   The pattern to match.  A percent symbol (%) will match zero or more
            characters and an underscore (_) will match a single character.
  str       The string to match
  escape    The escape character that can be used to prefix a literal percent 
            or underscore in the pattern.

Examples
#1 To test if the string 'aabcc' contains the letter 'b':
   ;SELECT like('%b%', 'aabcc')
   

#2 To test if the string 'aab%' ends with 'b%':
   ;SELECT like('%b:%', 'aab%', ':')
   


Synopsis
  likelihood(value, probability) -- Provides a hint to the query planner that 
    the first argument is a boolean that is true with the given probability
Parameters
  value         The boolean value to return
  probability   A floating point constant between 0.0 and 1.0


Synopsis
  likely(value) -- Short-hand for likelihood(X,0.9375)
Parameter
  value   The boolean value to return


Synopsis
  load_extension(path, [entry-point]) -- Loads SQLite extensions out of the 
    given shared library file using the given entry point.
Parameters
  path          The path to the shared library containing the extension.
  entry-point   


Synopsis
  log(x) -- Returns the natural logarithm of x
Parameter
  x   The number
See Also
  abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(), atn2(), 
  avg(), ceil(), degrees(), exp(), floor(), log10(), max(), min(), pi(), power(), 
  radians(), round(), sign(), square(), sum(), total()
Example
#1 To get the natual logarithm of 8:
   ;SELECT log(8)
   


Synopsis
  log10(x) -- Returns the base-10 logarithm of X
Parameter
  x   The number
See Also
  abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(), atn2(), 
  avg(), ceil(), degrees(), exp(), floor(), log(), max(), min(), pi(), power(), 
  radians(), round(), sign(), square(), sum(), total()
Example
#1 To get the logarithm of 100:
   ;SELECT log10(100)
   


Synopsis
  log_top_datetime() -- Return the timestamp of the line at the top of the log 
    view.


Synopsis
  log_top_line() -- Return the line number at the top of the log view.


Synopsis
  lower(str) -- Returns a copy of the given string with all ASCII characters 
    converted to lower case.
Parameter
  str   The string to convert.
See Also
  char(), charindex(), endswith(), extract(), group_concat(), group_spooky_hash(), 
  instr(), leftstr(), length(), ltrim(), padc(), padl(), padr(), printf(), 
  proper(), regexp_capture(), regexp_match(), regexp_replace(), replace(), 
  replicate(), reverse(), rightstr(), rtrim(), spooky_hash(), startswith(), 
  strfilter(), substr(), trim(), unicode(), upper()
Example
#1 To lowercase the string 'AbC':
   ;SELECT lower('AbC')
   


Synopsis
  ltrim(str, [chars]) -- Returns a string formed by removing any and all 
    characters that appear in the second argument from the left side of the 
    first.
Parameters
  str     The string to trim characters from the left side
  chars   The characters to trim.  Defaults to spaces.
See Also
  char(), charindex(), endswith(), extract(), group_concat(), group_spooky_hash(), 
  instr(), leftstr(), length(), lower(), padc(), padl(), padr(), printf(), 
  proper(), regexp_capture(), regexp_match(), regexp_replace(), replace(), 
  replicate(), reverse(), rightstr(), rtrim(), spooky_hash(), startswith(), 
  strfilter(), substr(), trim(), unicode(), upper()
Examples
#1 To trim the leading whitespace from the string '   abc':
   ;SELECT ltrim('   abc')
   

#2 To trim the characters 'a' or 'b' from the left side of the string 'aaaabbbc':
   ;SELECT ltrim('aaaabbbc', 'ab')
   


Synopsis
  max(X, ...) -- Returns the argument with the maximum value, or return NULL if
    any argument is NULL.
Parameter
  X   The numbers to find the maximum of.  If only one argument is given, this 
      function operates as an aggregate.
See Also
  abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(), atn2(), 
  avg(), ceil(), degrees(), exp(), floor(), log(), log10(), min(), pi(), power(), 
  radians(), round(), sign(), square(), sum(), total()
Examples
#1 To get the largest value from the parameters:
   ;SELECT max(2, 1, 3)
   

#2 To get the largest value from an aggregate:
   ;SELECT max(status) FROM http_status_codes
   


Synopsis
  min(X, ...) -- Returns the argument with the minimum value, or return NULL if
    any argument is NULL.
Parameter
  X   The numbers to find the minimum of.  If only one argument is given, this 
      function operates as an aggregate.
See Also
  abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(), atn2(), 
  avg(), ceil(), degrees(), exp(), floor(), log(), log10(), max(), pi(), power(), 
  radians(), round(), sign(), square(), sum(), total()
Examples
#1 To get the smallest value from the parameters:
   ;SELECT min(2, 1, 3)
   

#2 To get the smallest value from an aggregate:
   ;SELECT min(status) FROM http_status_codes
   


Synopsis
  nth_value(expr, N) -- Returns the result of evaluating the expression against
    the nth row in the window frame.
Parameters
  expr   The expression to execute over the nth row
  N      The row number
See Also
  cume_dist(), dense_rank(), first_value(), lag(), last_value(), lead(), ntile(), 
  percent_rank(), rank(), row_number()

Synopsis
  ntile(groups) -- Returns the number of the group that the current row is a 
    part of
Parameter
  groups   The number of groups
See Also
  cume_dist(), dense_rank(), first_value(), lag(), last_value(), lead(), 
  nth_value(), percent_rank(), rank(), row_number()

Synopsis
  nullif(X, Y) -- Returns its first argument if the arguments are different and
    NULL if the arguments are the same.
Parameters
  X   The first argument to compare.
  Y   The argument to compare against the first.

Examples
#1 To test if 1 is different from 1:
   ;SELECT nullif(1, 1)
   

#2 To test if 1 is different from 2:
   ;SELECT nullif(1, 2)
   


Synopsis
  padc(str, len) -- Pad the given string with enough spaces to make it centered
    within the given length
Parameters
  str   The string to pad
  len   The minimum desired length of the output string
See Also
  char(), charindex(), endswith(), extract(), group_concat(), group_spooky_hash(), 
  instr(), leftstr(), length(), lower(), ltrim(), padl(), padr(), printf(), 
  proper(), regexp_capture(), regexp_match(), regexp_replace(), replace(), 
  replicate(), reverse(), rightstr(), rtrim(), spooky_hash(), startswith(), 
  strfilter(), substr(), trim(), unicode(), upper()
Examples
#1 To pad the string 'abc' to a length of six characters:
   ;SELECT padc('abc', 6) || 'def'
   

#2 To pad the string 'abcdef' to a length of eight characters:
   ;SELECT padc('abcdef', 8) || 'ghi'
   


Synopsis
  padl(str, len) -- Pad the given string with leading spaces until it reaches 
    the desired length
Parameters
  str   The string to pad
  len   The minimum desired length of the output string
See Also
  char(), charindex(), endswith(), extract(), group_concat(), group_spooky_hash(), 
  instr(), leftstr(), length(), lower(), ltrim(), padc(), padr(), printf(), 
  proper(), regexp_capture(), regexp_match(), regexp_replace(), replace(), 
  replicate(), reverse(), rightstr(), rtrim(), spooky_hash(), startswith(), 
  strfilter(), substr(), trim(), unicode(), upper()
Examples
#1 To pad the string 'abc' to a length of six characters:
   ;SELECT padl('abc', 6)
   

#2 To pad the string 'abcdef' to a length of four characters:
   ;SELECT padl('abcdef', 4)
   


Synopsis
  padr(str, len) -- Pad the given string with trailing spaces until it reaches 
    the desired length
Parameters
  str   The string to pad
  len   The minimum desired length of the output string
See Also
  char(), charindex(), endswith(), extract(), group_concat(), group_spooky_hash(), 
  instr(), leftstr(), length(), lower(), ltrim(), padc(), padl(), printf(), 
  proper(), regexp_capture(), regexp_match(), regexp_replace(), replace(), 
  replicate(), reverse(), rightstr(), rtrim(), spooky_hash(), startswith(), 
  strfilter(), substr(), trim(), unicode(), upper()
Examples
#1 To pad the string 'abc' to a length of six characters:
   ;SELECT padr('abc', 6) || 'def'
   

#2 To pad the string 'abcdef' to a length of four characters:
   ;SELECT padr('abcdef', 4) || 'ghi'
   


Synopsis
  percent_rank() -- Returns (rank - 1) / (partition-rows - 1)
See Also
  cume_dist(), dense_rank(), first_value(), lag(), last_value(), lead(), 
  nth_value(), ntile(), rank(), row_number()

Synopsis
  pi() -- Returns the value of PI
See Also
  abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(), atn2(), 
  avg(), ceil(), degrees(), exp(), floor(), log(), log10(), max(), min(), power(), 
  radians(), round(), sign(), square(), sum(), total()
Example
#1 To get the value of PI:
   ;SELECT pi()
   


Synopsis
  power(base, exp) -- Returns the base to the given exponent
Parameters
  base   The base number
  exp    The exponent
See Also
  abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(), atn2(), 
  avg(), ceil(), degrees(), exp(), floor(), log(), log10(), max(), min(), pi(), 
  radians(), round(), sign(), square(), sum(), total()
Example
#1 To raise two to the power of three:
   ;SELECT power(2, 3)
   


Synopsis
  printf(format, X) -- Returns a string with this functions arguments 
    substituted into the given format.  Substitution points are specified using
    percent (%) options, much like the standard C printf() function.
Parameters
  format   The format of the string to return.
  X        The argument to substitute at a given position in the format.
See Also
  char(), charindex(), endswith(), extract(), group_concat(), group_spooky_hash(), 
  instr(), leftstr(), length(), lower(), ltrim(), padc(), padl(), padr(), 
  proper(), regexp_capture(), regexp_match(), regexp_replace(), replace(), 
  replicate(), reverse(), rightstr(), rtrim(), spooky_hash(), startswith(), 
  strfilter(), substr(), trim(), unicode(), upper()
Examples
#1 To substitute 'World' into the string 'Hello, %s!':
   ;SELECT printf('Hello, %s!', 'World')
   

#2 To right-align 'small' in the string 'align:' with a column width of 10:
   ;SELECT printf('align: % 10s', 'small')
   

#3 To format 11 with a width of five characters and leading zeroes:
   ;SELECT printf('value: %05d', 11)
   


Synopsis
  proper(str) -- Capitalize the first character of words in the given string
Parameter
  str   The string to capitalize.
See Also
  char(), charindex(), endswith(), extract(), group_concat(), group_spooky_hash(), 
  instr(), leftstr(), length(), lower(), ltrim(), padc(), padl(), padr(), 
  printf(), regexp_capture(), regexp_match(), regexp_replace(), replace(), 
  replicate(), reverse(), rightstr(), rtrim(), spooky_hash(), startswith(), 
  strfilter(), substr(), trim(), unicode(), upper()
Example
#1 To capitalize the words in the string 'hello, world!':
   ;SELECT proper('hello, world!')
   


Synopsis
  quote(X) -- Returns the text of an SQL literal which is the value of its 
    argument suitable for inclusion into an SQL statement.
Parameter
  X   The string to quote.

Examples
#1 To quote the string 'abc':
   ;SELECT quote('abc')
   

#2 To quote the string 'abc'123':
   ;SELECT quote('abc''123')
   


Synopsis
  radians(degrees) -- Converts degrees to radians
Parameter
  degrees   The degrees value to convert to radians
See Also
  abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(), atn2(), 
  avg(), ceil(), degrees(), exp(), floor(), log(), log10(), max(), min(), pi(), 
  power(), round(), sign(), square(), sum(), total()
Example
#1 To convert 180 degrees to radians:
   ;SELECT radians(180)
   


Synopsis
  raise_error(msg) -- Raises an error with the given message when executed
Parameter
  msg   The error message


Synopsis
  random() -- Returns a pseudo-random integer between -9223372036854775808 and 
    +9223372036854775807.


Synopsis
  randomblob(N) -- Return an N-byte blob containing pseudo-random bytes.
Parameter
  N   The size of the blob in bytes.


Synopsis
  rank() -- Returns the row_number() of the first peer in each group with gaps
See Also
  cume_dist(), dense_rank(), first_value(), lag(), last_value(), lead(), 
  nth_value(), ntile(), percent_rank(), row_number()

Synopsis
  readlink(path) -- Read the target of a symbolic link.
Parameter
  path   The path to the symbolic link.
See Also
  basename(), dirname(), joinpath(), realpath()

Synopsis
  realpath(path) -- Returns the resolved version of the given path, expanding 
    symbolic links and resolving '.' and '..' references.
Parameter
  path   The path to resolve.
See Also
  basename(), dirname(), joinpath(), readlink()

Synopsis
  regexp(re, str) -- Test if a string matches a regular expression
Parameters
  re    The regular expression to use
  str   The string to test against the regular expression


Synopsis
  regexp_capture(string, pattern) -- A table-valued function that executes a 
    regular-expression over a string and returns the captured values.  If the 
    regex only matches a subset of the input string, it will be rerun on the 
    remaining parts of the string until no more matches are found.
Parameters
  string    The string to match against the given pattern.
  pattern   The regular expression to match.
Results
  match_index     The match iteration.  This value will increase each time a 
                  new match is found in the input string.
  capture_index   The index of the capture in the regex.
  capture_name    The name of the capture in the regex.
  capture_count   The total number of captures in the regex.
  range_start     The start of the capture in the input string.
  range_stop      The stop of the capture in the input string.
  content         The captured value from the string.
See Also
  char(), charindex(), endswith(), extract(), group_concat(), group_spooky_hash(), 
  instr(), leftstr(), length(), lower(), ltrim(), padc(), padl(), padr(), 
  printf(), proper(), regexp_match(), regexp_replace(), replace(), replicate(), 
  reverse(), rightstr(), rtrim(), spooky_hash(), startswith(), strfilter(), 
  substr(), trim(), unicode(), upper()
Example
#1 To extract the key/value pairs 'a'/1 and 'b'/2 from the string 'a=1; b=2':
   ;SELECT * FROM regexp_capture('a=1; b=2', '(\w+)=(\d+)')
   


Synopsis
  regexp_match(re, str) -- Match a string against a regular expression and 
    return the capture groups as JSON.
Parameters
  re    The regular expression to use
  str   The string to test against the regular expression
See Also
  char(), charindex(), endswith(), extract(), group_concat(), group_spooky_hash(), 
  instr(), leftstr(), length(), lower(), ltrim(), padc(), padl(), padr(), 
  printf(), proper(), regexp_capture(), regexp_replace(), regexp_replace(), 
  replace(), replicate(), reverse(), rightstr(), rtrim(), spooky_hash(), 
  startswith(), strfilter(), substr(), trim(), unicode(), upper()
Examples
#1 To capture the digits from the string '123':
   ;SELECT regexp_match('(\d+)', '123')
   

#2 To capture a number and word into a JSON object with the properties 'col_0' and 'col_1':
   ;SELECT regexp_match('(\d+) (\w+)', '123 four')
   

#3 To capture a number and word into a JSON object with the named properties 'num' and 'str':
   ;SELECT regexp_match('(?<num>\d+) (?<str>\w+)', '123 four')
   


Synopsis
  regexp_replace(str, re, repl) -- Replace the parts of a string that match a 
    regular expression.
Parameters
  str    The string to perform replacements on
  re     The regular expression to match
  repl   The replacement string.  You can reference capture groups with a 
         backslash followed by the number of the group, starting with 1.
See Also
  char(), charindex(), endswith(), extract(), group_concat(), group_spooky_hash(), 
  instr(), leftstr(), length(), lower(), ltrim(), padc(), padl(), padr(), 
  printf(), proper(), regexp_capture(), regexp_match(), regexp_match(), replace(), 
  replicate(), reverse(), rightstr(), rtrim(), spooky_hash(), startswith(), 
  strfilter(), substr(), trim(), unicode(), upper()
Examples
#1 To replace the word at the start of the string 'Hello, World!' with 'Goodbye':
   ;SELECT regexp_replace('Hello, World!', '^(\w+)', 'Goodbye')
   

#2 To wrap alphanumeric words with angle brackets:
   ;SELECT regexp_replace('123 abc', '(\w+)', '<\1>')
   


Synopsis
  replace(str, old, replacement) -- Returns a string formed by substituting the
    replacement string for every occurrence of the old string in the given 
    string.
Parameters
  str           The string to perform substitutions on.
  old           The string to be replaced.
  replacement   The string to replace any occurrences of the old string with.
See Also
  char(), charindex(), endswith(), extract(), group_concat(), group_spooky_hash(), 
  instr(), leftstr(), length(), lower(), ltrim(), padc(), padl(), padr(), 
  printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(), 
  replicate(), reverse(), rightstr(), rtrim(), spooky_hash(), startswith(), 
  strfilter(), substr(), trim(), unicode(), upper()
Examples
#1 To replace the string 'x' with 'z' in 'abc':
   ;SELECT replace('abc', 'x', 'z')
   

#2 To replace the string 'a' with 'z' in 'abc':
   ;SELECT replace('abc', 'a', 'z')
   


Synopsis
  replicate(str, N) -- Returns the given string concatenated N times.
Parameters
  str   The string to replicate.
  N     The number of times to replicate the string.
See Also
  char(), charindex(), endswith(), extract(), group_concat(), group_spooky_hash(), 
  instr(), leftstr(), length(), lower(), ltrim(), padc(), padl(), padr(), 
  printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(), 
  replace(), reverse(), rightstr(), rtrim(), spooky_hash(), startswith(), 
  strfilter(), substr(), trim(), unicode(), upper()
Example
#1 To repeat the string 'abc' three times:
   ;SELECT replicate('abc', 3)
   


Synopsis
  reverse(str) -- Returns the reverse of the given string.
Parameter
  str   The string to reverse.
See Also
  char(), charindex(), endswith(), extract(), group_concat(), group_spooky_hash(), 
  instr(), leftstr(), length(), lower(), ltrim(), padc(), padl(), padr(), 
  printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(), 
  replace(), replicate(), rightstr(), rtrim(), spooky_hash(), startswith(), 
  strfilter(), substr(), trim(), unicode(), upper()
Example
#1 To reverse the string 'abc':
   ;SELECT reverse('abc')
   


Synopsis
  rightstr(str, N) -- Returns the N rightmost (UTF-8) characters in the given 
    string.
Parameters
  str   The string to return subset.
  N     The number of characters from the right side of the string to return.
See Also
  char(), charindex(), endswith(), extract(), group_concat(), group_spooky_hash(), 
  instr(), leftstr(), length(), lower(), ltrim(), padc(), padl(), padr(), 
  printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(), 
  replace(), replicate(), reverse(), rtrim(), spooky_hash(), startswith(), 
  strfilter(), substr(), trim(), unicode(), upper()
Examples
#1 To get the last character of the string 'abc':
   ;SELECT rightstr('abc', 1)
   

#2 To get the last ten characters of a string, regardless of size:
   ;SELECT rightstr('abc', 10)
   


Synopsis
  round(num, [digits]) -- Returns a floating-point value rounded to the given 
    number of digits to the right of the decimal point.
Parameters
  num      The value to round.
  digits   The number of digits to the right of the decimal to round to.
See Also
  abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(), atn2(), 
  avg(), ceil(), degrees(), exp(), floor(), log(), log10(), max(), min(), pi(), 
  power(), radians(), sign(), square(), sum(), total()
Examples
#1 To round the number 123.456 to an integer:
   ;SELECT round(123.456)
   

#2 To round the number 123.456 to a precision of 1:
   ;SELECT round(123.456, 1)
   

#3 To round the number 123.456 to a precision of 5:
   ;SELECT round(123.456, 5)
   


Synopsis
  row_number() -- Returns the number of the row within the current partition, 
    starting from 1.
See Also
  cume_dist(), dense_rank(), first_value(), lag(), last_value(), lead(), 
  nth_value(), ntile(), percent_rank(), rank()
Example
#1 To number messages from a process:
   ;SELECT row_number() OVER (PARTITION BY ex_procname ORDER BY log_line) AS msg_num, 
          ex_procname, log_body FROM lnav_example_log
   


Synopsis
  rtrim(str, [chars]) -- Returns a string formed by removing any and all 
    characters that appear in the second argument from the right side of the 
    first.
Parameters
  str     The string to trim characters from the right side
  chars   The characters to trim.  Defaults to spaces.
See Also
  char(), charindex(), endswith(), extract(), group_concat(), group_spooky_hash(), 
  instr(), leftstr(), length(), lower(), ltrim(), padc(), padl(), padr(), 
  printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(), 
  replace(), replicate(), reverse(), rightstr(), spooky_hash(), startswith(), 
  strfilter(), substr(), trim(), unicode(), upper()
Examples
#1 To trim the whitespace from the end of the string 'abc   ':
   ;SELECT rtrim('abc   ')
   

#2 To trim the characters 'b' and 'c' from the string 'abbbbcccc':
   ;SELECT rtrim('abbbbcccc', 'bc')
   


Synopsis
  sign(num) -- Returns the sign of the given number as -1, 0, or 1
Parameter
  num   The number
See Also
  abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(), atn2(), 
  avg(), ceil(), degrees(), exp(), floor(), log(), log10(), max(), min(), pi(), 
  power(), radians(), round(), square(), sum(), total()
Examples
#1 To get the sign of 10:
   ;SELECT sign(10)
   

#2 To get the sign of 0:
   ;SELECT sign(0)
   

#3 To get the sign of -10:
   ;SELECT sign(-10)
   


Synopsis
  spooky_hash(str, ...) -- Compute the hash value for the given arguments.
Parameter
  str   The string to hash
See Also
  char(), charindex(), endswith(), extract(), group_concat(), group_spooky_hash(), 
  instr(), leftstr(), length(), lower(), ltrim(), padc(), padl(), padr(), 
  printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(), 
  replace(), replicate(), reverse(), rightstr(), rtrim(), startswith(), 
  strfilter(), substr(), trim(), unicode(), upper()
Examples
#1 To produce a hash for the string 'Hello, World!':
   ;SELECT spooky_hash('Hello, World!')
   

#2 To produce a hash for the parameters where one is NULL:
   ;SELECT spooky_hash('Hello, World!', NULL)
   

#3 To produce a hash for the parameters where one is an empty string:
   ;SELECT spooky_hash('Hello, World!', '')
   

#4 To produce a hash for the parameters where one is a number:
   ;SELECT spooky_hash('Hello, World!', 123)
   


Synopsis
  sqlite_compileoption_get(N) -- Returns the N-th compile-time option used to 
    build SQLite or NULL if N is out of range.
Parameter
  N   The option number to get


Synopsis
  sqlite_compileoption_used(option) -- Returns true (1) or false (0) depending 
    on whether or not that compile-time option was used during the build.
Parameter
  option   The name of the compile-time option.

Example
#1 To check if the SQLite library was compiled with ENABLE_FTS3:
   ;SELECT sqlite_compileoption_used('ENABLE_FTS3')
   


Synopsis
  sqlite_source_id() -- Returns a string that identifies the specific version 
    of the source code that was used to build the SQLite library.


Synopsis
  sqlite_version() -- Returns the version string for the SQLite library that is
    running.


Synopsis
  square(num) -- Returns the square of the argument
Parameter
  num   The number to square
See Also
  abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(), atn2(), 
  avg(), ceil(), degrees(), exp(), floor(), log(), log10(), max(), min(), pi(), 
  power(), radians(), round(), sign(), sum(), total()
Example
#1 To get the square of two:
   ;SELECT square(2)
   


Synopsis
  startswith(str, prefix) -- Test if a string begins with the given prefix
Parameters
  str      The string to test
  prefix   The prefix to check in the string
See Also
  char(), charindex(), endswith(), extract(), group_concat(), group_spooky_hash(), 
  instr(), leftstr(), length(), lower(), ltrim(), padc(), padl(), padr(), 
  printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(), 
  replace(), replicate(), reverse(), rightstr(), rtrim(), spooky_hash(), 
  strfilter(), substr(), trim(), unicode(), upper()
Examples
#1 To test if the string 'foobar' starts with 'foo':
   ;SELECT startswith('foobar', 'foo')
   

#2 To test if the string 'foobar' starts with 'bar':
   ;SELECT startswith('foobar', 'bar')
   


Synopsis
  strfilter(source, include) -- Returns the source string with only the 
    characters given in the second parameter
Parameters
  source    The string to filter
  include   The characters to include in the result
See Also
  char(), charindex(), endswith(), extract(), group_concat(), group_spooky_hash(), 
  instr(), leftstr(), length(), lower(), ltrim(), padc(), padl(), padr(), 
  printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(), 
  replace(), replicate(), reverse(), rightstr(), rtrim(), spooky_hash(), 
  startswith(), substr(), trim(), unicode(), upper()
Example
#1 To get the 'b', 'c', and 'd' characters from the string 'abcabc':
   ;SELECT strfilter('abcabc', 'bcd')
   


Synopsis
  strftime(format, timestring, modifier, ...) -- Returns the date formatted 
    according to the format string specified as the first argument.
Parameters
  format       A format string with substitutions similar to those found in the
               strftime() standard C library.
  timestring   The string to convert to a date with time.
  modifier     A transformation that is applied to the value to the left.
See Also
  date(), datetime(), julianday(), time(), timediff(), timeslice()
Examples
#1 To get the year from the timestamp '2017-01-02T03:04:05':
   ;SELECT strftime('%Y', '2017-01-02T03:04:05')
   

#2 To create a string with the time from the timestamp '2017-01-02T03:04:05' plus one minute:
   ;SELECT strftime('The time is: %H:%M:%S', '2017-01-02T03:04:05', '+1 minute')
   

#3 To create a string with the Julian day from the epoch timestamp 1491341842:
   ;SELECT strftime('Julian day: %J', 1491341842, 'unixepoch')
   


Synopsis
  substr(str, start, [size]) -- Returns a substring of input string X that 
    begins with the Y-th character and which is Z characters long.
Parameters
  str     The string to extract a substring from.
  start   The index within 'str' that is the start of the substring.  Indexes 
          begin at 1.  A negative value means that the substring is found by 
          counting from the right rather than the left.  
  size    The size of the substring.  If not given, then all characters through
          the end of the string are returned.  If the value is negative, then 
          the characters before the start are returned.
See Also
  char(), charindex(), endswith(), extract(), group_concat(), group_spooky_hash(), 
  instr(), leftstr(), length(), lower(), ltrim(), padc(), padl(), padr(), 
  printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(), 
  replace(), replicate(), reverse(), rightstr(), rtrim(), spooky_hash(), 
  startswith(), strfilter(), trim(), unicode(), upper()
Examples
#1 To get the substring starting at the second character until the end of the string 'abc':
   ;SELECT substr('abc', 2)
   

#2 To get the substring of size one starting at the second character of the string 'abc':
   ;SELECT substr('abc', 2, 1)
   

#3 To get the substring starting at the last character until the end of the string 'abc':
   ;SELECT substr('abc', -1)
   

#4 To get the substring starting at the last character and going backwards one step of the string 'abc':
   ;SELECT substr('abc', -1, -1)
   


Synopsis
  sum(X) -- Returns the sum of the values in the group as an integer.
Parameter
  X   The values to add.
See Also
  abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(), atn2(), 
  avg(), ceil(), degrees(), exp(), floor(), log(), log10(), max(), min(), pi(), 
  power(), radians(), round(), sign(), square(), total()
Example
#1 To sum all of the values in the column 'ex_duration' from the table 'lnav_example_log':
   ;SELECT sum(ex_duration) FROM lnav_example_log
   


Synopsis
  time(timestring, modifier, ...) -- Returns the time in this format: HH:MM:SS.
    
Parameters
  timestring   The string to convert to a time.
  modifier     A transformation that is applied to the value to the left.
See Also
  date(), datetime(), julianday(), strftime(), timediff(), timeslice()
Examples
#1 To get the time portion of the timestamp '2017-01-02T03:04:05':
   ;SELECT time('2017-01-02T03:04:05')
   

#2 To get the time portion of the timestamp '2017-01-02T03:04:05' plus one minute:
   ;SELECT time('2017-01-02T03:04:05', '+1 minute')
   

#3 To get the time portion of the epoch timestamp 1491341842:
   ;SELECT time(1491341842, 'unixepoch')
   


Synopsis
  timediff(time1, time2) -- Compute the difference between two timestamps in 
    seconds
Parameters
  time1   The first timestamp
  time2   The timestamp to subtract from the first
See Also
  date(), datetime(), julianday(), strftime(), time(), timeslice()
Examples
#1 To get the difference between two timestamps:
   ;SELECT timediff('2017-02-03T04:05:06', '2017-02-03T04:05:00')
   

#2 To get the difference between relative timestamps:
   ;SELECT timediff('today', 'yesterday')
   


Synopsis
  timeslice(time, slice) -- Return the start of the slice of time that the 
    given timestamp falls in.
Parameters
  time    The timestamp to get the time slice for.
  slice   The size of the time slices
See Also
  date(), datetime(), julianday(), strftime(), time(), timediff()
Examples
#1 To get the timestamp rounded down to the start of the ten minute slice:
   ;SELECT timeslice('2017-01-01T05:05:00', '10m')
   

#2 To group log messages into five minute buckets and count them:
   ;SELECT timeslice(log_time, '5m') AS slice, count(*) FROM lnav_example_log GROUP BY 
          slice
   


Synopsis
  total(X) -- Returns the sum of the values in the group as a floating-point.
Parameter
  X   The values to add.
See Also
  abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(), atn2(), 
  avg(), ceil(), degrees(), exp(), floor(), log(), log10(), max(), min(), pi(), 
  power(), radians(), round(), sign(), square(), sum()
Example
#1 To total all of the values in the column 'ex_duration' from the table 'lnav_example_log':
   ;SELECT total(ex_duration) FROM lnav_example_log
   


Synopsis
  total_changes() -- Returns the number of row changes caused by INSERT, UPDATE
    or DELETE statements since the current database connection was opened.


Synopsis
  trim(str, [chars]) -- Returns a string formed by removing any and all 
    characters that appear in the second argument from the left and right sides
    of the first.
Parameters
  str     The string to trim characters from the left and right sides.
  chars   The characters to trim.  Defaults to spaces.
See Also
  char(), charindex(), endswith(), extract(), group_concat(), group_spooky_hash(), 
  instr(), leftstr(), length(), lower(), ltrim(), padc(), padl(), padr(), 
  printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(), 
  replace(), replicate(), reverse(), rightstr(), rtrim(), spooky_hash(), 
  startswith(), strfilter(), substr(), unicode(), upper()
Examples
#1 To trim whitespace from the start and end of the string '    abc   ':
   ;SELECT trim('    abc   ')
   

#2 To trim the characters '-' and '+' from the string '-+abc+-':
   ;SELECT trim('-+abc+-', '-+')
   


Synopsis
  typeof(X) -- Returns a string that indicates the datatype of the expression X
    : "null", "integer", "real", "text", or "blob".
Parameter
  X   The expression to check.

Examples
#1 To get the type of the number 1:
   ;SELECT typeof(1)
   

#2 To get the type of the string 'abc':
   ;SELECT typeof('abc')
   


Synopsis
  unicode(X) -- Returns the numeric unicode code point corresponding to the 
    first character of the string X.
Parameter
  X   The string to examine.
See Also
  char(), charindex(), endswith(), extract(), group_concat(), group_spooky_hash(), 
  instr(), leftstr(), length(), lower(), ltrim(), padc(), padl(), padr(), 
  printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(), 
  replace(), replicate(), reverse(), rightstr(), rtrim(), spooky_hash(), 
  startswith(), strfilter(), substr(), trim(), upper()
Example
#1 To get the unicode code point for the first character of 'abc':
   ;SELECT unicode('abc')
   


Synopsis
  unlikely(value) -- Short-hand for likelihood(X, 0.0625)
Parameter
  value   The boolean value to return


Synopsis
  upper(str) -- Returns a copy of the given string with all ASCII characters 
    converted to upper case.
Parameter
  str   The string to convert.
See Also
  char(), charindex(), endswith(), extract(), group_concat(), group_spooky_hash(), 
  instr(), leftstr(), length(), lower(), ltrim(), padc(), padl(), padr(), 
  printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(), 
  replace(), replicate(), reverse(), rightstr(), rtrim(), spooky_hash(), 
  startswith(), strfilter(), substr(), trim(), unicode()
Example
#1 To uppercase the string 'aBc':
   ;SELECT upper('aBc')
   


Synopsis
  zeroblob(N) -- Returns a BLOB consisting of N bytes of 0x00.
Parameter
  N   The size of the BLOB.


Synopsis
  ATTACH DATABASE filename AS schema-name

  Attach a database file to the current connection.
Parameters
  filename      The path to the database file.
  schema-name   The prefix for tables in this database.

Example
#1 To attach the database file '/tmp/customers.db' with the name customers:
   ;ATTACH DATABASE '/tmp/customers.db' AS customers
   


Synopsis
  CASE [base-expr] WHEN cmp-expr1 THEN then-expr1 [... WHEN cmp-exprN THEN then-exprN]
       [ELSE else-expr]
       END

  Evaluate a series of expressions in order until one evaluates to true and 
  then return it's result.  Similar to an IF-THEN-ELSE construct in other 
  languages.
Parameters
  base-expr   The base expression that is used for comparison in the branches
  cmp-expr    The expression to test if this branch should be taken
  else-expr   The result of this CASE if no branches matched.

Example
#1 To evaluate the number one and return the string 'one':
   ;SELECT CASE 1 WHEN 0 THEN 'zero' WHEN 1 THEN 'one' END
   


Synopsis
  CREATE [TEMP] VIEW [IF NOT EXISTS] [schema-name.] view-name AS select-stmt

  Assign a name to a SELECT statement
Parameters
  IF NOT EXISTS   Do not create the view if it already exists
  schema-name.    The database to create the view in
  view-name       The name of the view
  select-stmt     The SELECT statement the view represents


Synopsis
  DELETE FROM table-name [WHERE cond]

  Delete rows from a table
Parameters
  table-name   The name of the table
  cond         The conditions used to delete the rows.


Synopsis
  DETACH DATABASE schema-name

  Detach a database from the current connection.
Parameter
  schema-name   The prefix for tables in this database.

Example
#1 To detach the database named 'customers':
   ;DETACH DATABASE customers
   


Synopsis
  DROP VIEW [IF EXISTS] [schema-name.] view-name

  Drop a view
Parameters


Synopsis
  INSERT INTO [schema-name.] table-name [( column-name1 [, ... column-nameN] )]
         VALUES ( expr1 [, ... exprN] )

  Insert rows into a table
Parameters

Example
#1 To insert the pair containing 'MSG' and 'HELLO, WORLD!' into the 'environ' table:
   ;INSERT INTO environ VALUES ('MSG', 'HELLO, WORLD!')
   


Synopsis
  OVER window-name

  Executes the preceding function over a window
Parameter
  window-name   The name of the window definition


Synopsis
  SELECT result-column1 [, ... result-columnN] [FROM table1 [, ... tableN]]
         [WHERE cond]
         [GROUP BY grouping-expr1 [, ... grouping-exprN]]
         [ORDER BY ordering-term1 [, ... ordering-termN]]
         [LIMIT limit-expr1 [, ... limit-exprN]]

  Query the database and return zero or more rows of data.
Parameters
  result-column   
  table           The table(s) to query for data
  cond            The conditions used to select the rows to return.
  grouping-expr   The expression to use when grouping rows.
  ordering-term   The values to use when ordering the result set.
  limit-expr      The maximum number of rows to return

Example
#1 To select all of the columns from the table 'syslog_log':
   ;SELECT * FROM syslog_log
   


Synopsis
  UPDATE table SET column-name1 = expr1 [, ... column-nameN = exprN]
         [WHERE cond]

  Modify a subset of values in zero or more rows of the given table
Parameters
  table         The table to update
  column-name   The columns in the table to update.
  cond          The condition used to determine whether a row should be 
                updated.

Example
#1 To mark the syslog message at line 40:
   ;UPDATE syslog_log SET log_mark = 1 WHERE log_line = 40
   


Synopsis
  WITH [RECURSIVE] cte-table-name AS select-stmt

  Create a temporary view that exists only for the duration of a SQL statement.
  
Parameters
  cte-table-name   The name for the temporary table.
  select-stmt      The SELECT statement used to populate the temporary table.

