diff options
Diffstat (limited to 'test/expected_help.txt')
| -rw-r--r-- | test/expected_help.txt | 4016 |
1 files changed, 4016 insertions, 0 deletions
diff --git a/test/expected_help.txt b/test/expected_help.txt new file mode 100644 index 0000000..27f9c8a --- /dev/null +++ b/test/expected_help.txt @@ -0,0 +1,4016 @@ + +lnav + +A fancy log file viewer for the terminal. + +Overview + +The Logfile 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 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. + +If you do not want the default syslog file to be loaded when no files +are specified, you can pass the -N flag. + +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. + +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. + +Default Key Bindings + +Views + + Key(s) Action +═══════════════════════════════════════════════════════════ + ? 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 + + Key(s) Action +═══════════════════════════════════════════════════════════════ + 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/PgUp Move up a page. + j/↓ Move down a line. + k/↑ Move up a line. + h/← 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/→ Move to the right. + H/Shift ← Move to the left by a smaller increment. + L/Shift → 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. + 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 + + Key(s) Action +══════════════════════════════════════════════════════════════════ + 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 + + Key(s) Action +═══════════════════════════════════════════════════════════ + 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 + + Key(s) Action +══════════════════════════════════════════════════════════════════ + 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. + CTRL-X Toggle the cursor mode. Allows moving the selected + line instead of keeping it fixed at the top of the + current screen. + F2 Toggle mouse support. + +Query + + Key(s) Action +═════════════════════════════════════════════════════════════════════════════ + /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. + :<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 | . + + ┃ 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. + +Session + + Key(s) Action +═══════════════════════════════════════════════════════════ + 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. + + Key(s) Action +═══════════════════════════════════════════════════════════ + q Switch the focus back to the main view. + j/↓ Select the next filter. + k/↑ 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. + +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: + + Name Description +════════════════════════════════════════════════════════════════ + 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: + + Column Description +═══════════════════════════════════════════════════════════════════ + 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 + + Column Description + ═════════════════════════════════════════════════════════════════ + 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.) + + Column Description + ══════════════════════════════════════════════════════════ + 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.) + + Column Description + ═══════════════════════════════════════════════════════ + 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 logline 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. + +Contact + +For more information, visit the lnav website at: + +http://lnav.org[1] + + ┃[1] - http://lnav.org + +For support questions, email: + +lnav@googlegroups.com[1] support@lnav.org[2] + + ┃[1] - mailto:lnav@googlegroups.com + ┃[2] - mailto:support@lnav.org + +Command Reference + +:adjust-log-time timestamp +══════════════════════════════════════════════════════════════════════ + Change the timestamps of the top file to be relative to the given + date +Parameter + timestamp The new timestamp for the top line in the view + +Examples +#1 To set the top timestamp to a given date: + :adjust-log-time 2017-01-02T05:33:00 + + +#2 To set the top timestamp back an hour: + :adjust-log-time -1h + + + +:alt-msg msg +══════════════════════════════════════════════════════════════════════ + Display a message in the alternate command position +Parameter + msg The message to display +See Also + :echo, :eval, :redirect-to, :write-csv-to, :write-json-to, + :write-jsonlines-to, :write-raw-to, :write-screen-to, :write-table-to, + :write-to, :write-view-to +Example +#1 To display 'Press t to switch to the text view' on the bottom right: + :alt-msg Press t to switch to the text view + + + +:append-to path +══════════════════════════════════════════════════════════════════════ + Append marked lines in the current view to the given file +Parameter + path The path to the file to append to +See Also + :echo, :pipe-line-to, :pipe-to, :redirect-to, :write-csv-to, + :write-json-to, :write-jsonlines-to, :write-raw-to, :write-screen-to, + :write-table-to, :write-to, :write-view-to +Example +#1 To append marked lines to the file /tmp/interesting-lines.txt: + :append-to /tmp/interesting-lines.txt + + + +:clear-comment +══════════════════════════════════════════════════════════════════════ + Clear the comment attached to the top log line +See Also + :comment, :tag + +:clear-filter-expr +══════════════════════════════════════════════════════════════════════ + Clear the filter expression +See Also + :filter-expr, :filter-in, :filter-out, :hide-lines-after, + :hide-lines-before, :hide-unmarked-lines, :toggle-filtering + +:clear-highlight pattern +══════════════════════════════════════════════════════════════════════ + Remove a previously set highlight regular expression +Parameter + pattern The regular expression previously used with :highlight +See Also + :enable-word-wrap, :hide-fields, :highlight +Example +#1 To clear the highlight with the pattern 'foobar': + :clear-highlight foobar + + + +:clear-mark-expr +══════════════════════════════════════════════════════════════════════ + Clear the mark expression +See Also + :hide-unmarked-lines, :mark, :mark-expr, :next-mark, :prev-mark + +:clear-partition +══════════════════════════════════════════════════════════════════════ + Clear the partition the top line is a part of + + +:close +══════════════════════════════════════════════════════════════════════ + Close the top file in the view + + +:comment text +══════════════════════════════════════════════════════════════════════ + Attach a comment to the top log line +Parameter + text The comment text +See Also + :clear-comment, :tag +Example +#1 To add the comment 'This is where it all went wrong' to the top line: + :comment This is where it all went wrong + + + +:config option [value] +══════════════════════════════════════════════════════════════════════ + Read or write a configuration option +Parameters + option The path to the option to read or write + value The value to write. If not given, the current value is + returned +See Also + :reset-config +Examples +#1 To read the configuration of the '/ui/clock-format' option: + :config /ui/clock-format + + +#2 To set the '/ui/dim-text' option to 'false': + :config /ui/dim-text false + + + +:create-logline-table table-name +══════════════════════════════════════════════════════════════════════ + Create an SQL table using the top line of the log view as a template + +Parameter + table-name The name for the new table +See Also + :create-search-table, :create-search-table, :write-csv-to, + :write-json-to, :write-jsonlines-to, :write-raw-to, :write-screen-to, + :write-table-to, :write-view-to +Example +#1 To create a logline-style table named 'task_durations': + :create-logline-table task_durations + + + +:create-search-table table-name [pattern] +══════════════════════════════════════════════════════════════════════ + Create an SQL table based on a regex search +Parameters + table-name The name of the table to create + pattern The regular expression used to capture the table + columns. If not given, the current search pattern is + used. +See Also + :create-logline-table, :create-logline-table, :delete-search-table, + :delete-search-table, :write-csv-to, :write-json-to, + :write-jsonlines-to, :write-raw-to, :write-screen-to, :write-table-to, + :write-view-to +Example +#1 To create a table named 'task_durations' that matches log messages with the pattern ' + duration=(?<duration>\d+)': + :create-search-table task_durations duration=(?<duration>\d+) + + + +:current-time +══════════════════════════════════════════════════════════════════════ + Print the current time in human-readable form and seconds since the + epoch + + +:delete-filter pattern +══════════════════════════════════════════════════════════════════════ + Delete the filter created with :filter-in or :filter-out +Parameter + pattern The regular expression to match +See Also + :filter-in, :filter-out, :hide-lines-after, :hide-lines-before, + :hide-unmarked-lines, :toggle-filtering +Example +#1 To delete the filter with the pattern 'last message repeated': + :delete-filter last message repeated + + + +:delete-logline-table table-name +══════════════════════════════════════════════════════════════════════ + Delete a table created with create-logline-table +Parameter + table-name The name of the table to delete +See Also + :create-logline-table, :create-logline-table, :create-search-table, + :create-search-table, :write-csv-to, :write-json-to, + :write-jsonlines-to, :write-raw-to, :write-screen-to, :write-table-to, + :write-view-to +Example +#1 To delete the logline-style table named 'task_durations': + :delete-logline-table task_durations + + + +:delete-search-table table-name +══════════════════════════════════════════════════════════════════════ + Create an SQL table based on a regex search +Parameter + table-name The name of the table to create +See Also + :create-logline-table, :create-logline-table, :create-search-table, + :create-search-table, :write-csv-to, :write-json-to, + :write-jsonlines-to, :write-raw-to, :write-screen-to, :write-table-to, + :write-view-to +Example +#1 To delete the search table named 'task_durations': + :delete-search-table task_durations + + + +:delete-tags tag1 [... tagN] +══════════════════════════════════════════════════════════════════════ + Remove the given tags from all log lines +Parameter + tag The tags to delete +See Also + :comment, :tag +Example +#1 To remove the tags '#BUG123' and '#needs-review' from all log lines: + :delete-tags #BUG123 #needs-review + + + +:disable-filter pattern +══════════════════════════════════════════════════════════════════════ + Disable a filter created with filter-in/filter-out +Parameter + pattern The regular expression used in the filter command +See Also + :enable-filter, :filter-in, :filter-out, :hide-lines-after, + :hide-lines-before, :hide-unmarked-lines, :toggle-filtering +Example +#1 To disable the filter with the pattern 'last message repeated': + :disable-filter last message repeated + + + +:disable-word-wrap +══════════════════════════════════════════════════════════════════════ + Disable word-wrapping for the current view +See Also + :enable-word-wrap, :hide-fields, :highlight + +:echo msg +══════════════════════════════════════════════════════════════════════ + Echo the given message to the screen or, if :redirect-to has been + called, to output file specified in the redirect. Variable + substitution is performed on the message. Use a backslash to escape + any special characters, like '$' +Parameter + msg The message to display +See Also + :alt-msg, :append-to, :eval, :pipe-line-to, :pipe-to, :redirect-to, + :redirect-to, :write-csv-to, :write-csv-to, :write-json-to, + :write-json-to, :write-jsonlines-to, :write-jsonlines-to, + :write-raw-to, :write-raw-to, :write-screen-to, :write-screen-to, + :write-table-to, :write-table-to, :write-to, :write-to, :write-view-to, + :write-view-to +Example +#1 To output 'Hello, World!': + :echo Hello, World! + + + +:enable-filter pattern +══════════════════════════════════════════════════════════════════════ + Enable a previously created and disabled filter +Parameter + pattern The regular expression used in the filter command +See Also + :filter-in, :filter-out, :hide-lines-after, :hide-lines-before, + :hide-unmarked-lines, :toggle-filtering +Example +#1 To enable the disabled filter with the pattern 'last message repeated': + :enable-filter last message repeated + + + +:enable-word-wrap +══════════════════════════════════════════════════════════════════════ + Enable word-wrapping for the current view +See Also + :disable-word-wrap, :hide-fields, :highlight + +:eval command +══════════════════════════════════════════════════════════════════════ + Evaluate the given command/query after doing environment variable + substitution +Parameter + command The command or query to perform substitution on. +See Also + :alt-msg, :echo, :redirect-to, :write-csv-to, :write-json-to, + :write-jsonlines-to, :write-raw-to, :write-screen-to, :write-table-to, + :write-to, :write-view-to +Example +#1 To substitute the table name from a variable: + :eval ;SELECT * FROM ${table} + + + +:filter-expr expr +══════════════════════════════════════════════════════════════════════ + Set the filter expression +Parameter + expr The SQL expression to evaluate for each log message. The + message values can be accessed using column names prefixed + with a colon +See Also + :clear-filter-expr, :filter-in, :filter-out, :hide-lines-after, + :hide-lines-before, :hide-unmarked-lines, :toggle-filtering +Examples +#1 To set a filter expression that matched syslog messages from 'syslogd': + :filter-expr :log_procname = 'syslogd' + + +#2 To set a filter expression that matches log messages where 'id' is followed by a number + and contains the string 'foo': + :filter-expr :log_body REGEXP 'id\d+' AND :log_body REGEXP 'foo' + + + +:filter-in pattern +══════════════════════════════════════════════════════════════════════ + Only show lines that match the given regular expression in the + current view +Parameter + pattern The regular expression to match +See Also + :delete-filter, :disable-filter, :filter-out, :hide-lines-after, + :hide-lines-before, :hide-unmarked-lines, :toggle-filtering +Example +#1 To filter out log messages that do not have the string 'dhclient': + :filter-in dhclient + + + +:filter-out pattern +══════════════════════════════════════════════════════════════════════ + Remove lines that match the given regular expression in the current + view +Parameter + pattern The regular expression to match +See Also + :delete-filter, :disable-filter, :filter-in, :hide-lines-after, + :hide-lines-before, :hide-unmarked-lines, :toggle-filtering +Example +#1 To filter out log messages that contain the string 'last message repeated': + :filter-out last message repeated + + + +:goto line#|N%|date +══════════════════════════════════════════════════════════════════════ + Go to the given location in the top view +Parameter + line#|N%|date A line number, percent into the file, or a timestamp + +See Also + :next-location, :next-mark, :prev-location, :prev-mark, :relative-goto +Examples +#1 To go to line 22: + :goto 22 + + +#2 To go to the line 75% of the way into the view: + :goto 75% + + +#3 To go to the first message on the first day of 2017: + :goto 2017-01-01 + + + +:help +══════════════════════════════════════════════════════════════════════ + Open the help text view + + +:hide-fields field-name1 [... field-nameN] +══════════════════════════════════════════════════════════════════════ + Hide log message fields by replacing them with an ellipsis +Parameter + field-name The name of the field to hide in the format for the top + log line. A qualified name can be used where the field + name is prefixed by the format name and a dot to hide + any field. +See Also + :enable-word-wrap, :highlight, :show-fields +Examples +#1 To hide the log_procname fields in all formats: + :hide-fields log_procname + + +#2 To hide only the log_procname field in the syslog format: + :hide-fields syslog_log.log_procname + + + +:hide-file path +══════════════════════════════════════════════════════════════════════ + Hide the given file(s) and skip indexing until it is shown again. + If no path is given, the current file in the view is hidden +Parameter + path A path or glob pattern that specifies the files to hide + + +:hide-lines-after date +══════════════════════════════════════════════════════════════════════ + Hide lines that come after the given date +Parameter + date An absolute or relative date +See Also + :filter-in, :filter-out, :hide-lines-before, :hide-unmarked-lines, + :show-lines-before-and-after, :toggle-filtering +Examples +#1 To hide the lines after the top line in the view: + :hide-lines-after here + + +#2 To hide the lines after 6 AM today: + :hide-lines-after 6am + + + +:hide-lines-before date +══════════════════════════════════════════════════════════════════════ + Hide lines that come before the given date +Parameter + date An absolute or relative date +See Also + :filter-in, :filter-out, :hide-lines-after, :hide-unmarked-lines, + :show-lines-before-and-after, :toggle-filtering +Examples +#1 To hide the lines before the top line in the view: + :hide-lines-before here + + +#2 To hide the log messages before 6 AM today: + :hide-lines-before 6am + + + +:hide-unmarked-lines +══════════════════════════════════════════════════════════════════════ + Hide lines that have not been bookmarked +See Also + :filter-in, :filter-out, :hide-lines-after, :hide-lines-before, :mark, + :next-mark, :prev-mark, :toggle-filtering + +:highlight pattern +══════════════════════════════════════════════════════════════════════ + Add coloring to log messages fragments that match the given regular + expression +Parameter + pattern The regular expression to match +See Also + :clear-highlight, :enable-word-wrap, :hide-fields +Example +#1 To highlight numbers with three or more digits: + :highlight \d{3,} + + + +:load-session +══════════════════════════════════════════════════════════════════════ + Load the latest session state + + +:mark +══════════════════════════════════════════════════════════════════════ + Toggle the bookmark state for the top line in the current view +See Also + :hide-unmarked-lines, :next-mark, :prev-mark + +:mark-expr expr +══════════════════════════════════════════════════════════════════════ + Set the bookmark expression +Parameter + expr The SQL expression to evaluate for each log message. The + message values can be accessed using column names prefixed + with a colon +See Also + :clear-mark-expr, :hide-unmarked-lines, :mark, :next-mark, :prev-mark +Example +#1 To mark lines from 'dhclient' that mention 'eth0': + :mark-expr :log_procname = 'dhclient' AND :log_body LIKE '%eth0%' + + + +:next-location +══════════════════════════════════════════════════════════════════════ + Move to the next position in the location history +See Also + :goto, :next-mark, :prev-location, :prev-mark, :relative-goto + +:next-mark type1 [... typeN] +══════════════════════════════════════════════════════════════════════ + Move to the next bookmark of the given type in the current view +Parameter + type The type of bookmark -- error, warning, search, user, file, + meta +See Also + :goto, :hide-unmarked-lines, :mark, :next-location, :prev-location, + :prev-mark, :prev-mark, :relative-goto +Example +#1 To go to the next error: + :next-mark error + + + +:open path1 [... pathN] +══════════════════════════════════════════════════════════════════════ + Open the given file(s) in lnav. Opening files on machines + accessible via SSH can be done using the syntax: [user@]host:/path/ + to/logs +Parameter + path The path to the file to open + +Examples +#1 To open the file '/path/to/file': + :open /path/to/file + + +#2 To open the remote file '/var/log/syslog.log': + :open dean@host1.example.com:/var/log/syslog.log + + + +:partition-name name +══════════════════════════════════════════════════════════════════════ + Mark the top line in the log view as the start of a new partition + with the given name +Parameter + name The name for the new partition + +Example +#1 To mark the top line as the start of the partition named 'boot #1': + :partition-name boot #1 + + + +:pipe-line-to shell-cmd +══════════════════════════════════════════════════════════════════════ + Pipe the top line to the given shell command +Parameter + shell-cmd The shell command-line to execute +See Also + :append-to, :echo, :pipe-to, :redirect-to, :write-csv-to, + :write-json-to, :write-jsonlines-to, :write-raw-to, :write-screen-to, + :write-table-to, :write-to, :write-view-to +Example +#1 To write the top line to 'sed' for processing: + :pipe-line-to sed -e 's/foo/bar/g' + + + +:pipe-to shell-cmd +══════════════════════════════════════════════════════════════════════ + Pipe the marked lines to the given shell command +Parameter + shell-cmd The shell command-line to execute +See Also + :append-to, :echo, :pipe-line-to, :redirect-to, :write-csv-to, + :write-json-to, :write-jsonlines-to, :write-raw-to, :write-screen-to, + :write-table-to, :write-to, :write-view-to +Example +#1 To write marked lines to 'sed' for processing: + :pipe-to sed -e s/foo/bar/g + + + +:prev-location +══════════════════════════════════════════════════════════════════════ + Move to the previous position in the location history +See Also + :goto, :next-location, :next-mark, :prev-mark, :relative-goto + +:prev-mark type1 [... typeN] +══════════════════════════════════════════════════════════════════════ + Move to the previous bookmark of the given type in the current view +Parameter + type The type of bookmark -- error, warning, search, user, file, + meta +See Also + :goto, :hide-unmarked-lines, :mark, :next-location, :next-mark, + :next-mark, :prev-location, :relative-goto +Example +#1 To go to the previous error: + :prev-mark error + + + +:prompt type [--alt] [prompt] [initial-value] +══════════════════════════════════════════════════════════════════════ + Open the given prompt +Parameters + type The type of prompt -- command, script, search, sql, + user + --alt Perform the alternate action for this prompt by + default + prompt The prompt to display + initial-value The initial value to fill in for the prompt + +Examples +#1 To open the command prompt with 'filter-in' already filled in: + :prompt command : 'filter-in ' + + +#2 To ask the user a question: + :prompt user 'Are you sure? ' + + + +:quit +══════════════════════════════════════════════════════════════════════ + Quit lnav + + +:quit +══════════════════════════════════════════════════════════════════════ + Quit lnav + + +:quit +══════════════════════════════════════════════════════════════════════ + Quit lnav + + +:redirect-to [path] +══════════════════════════════════════════════════════════════════════ + Redirect the output of commands that write to stdout to the given + file +Parameter + path The path to the file to write. If not specified, the current + redirect will be cleared +See Also + :alt-msg, :append-to, :echo, :echo, :eval, :pipe-line-to, :pipe-to, + :write-csv-to, :write-csv-to, :write-json-to, :write-json-to, + :write-jsonlines-to, :write-jsonlines-to, :write-raw-to, :write-raw-to, + :write-screen-to, :write-screen-to, :write-table-to, :write-table-to, + :write-to, :write-to, :write-view-to, :write-view-to +Example +#1 To write the output of lnav commands to the file /tmp/script-output.txt: + :redirect-to /tmp/script-output.txt + + + +:redraw +══════════════════════════════════════════════════════════════════════ + Do a full redraw of the screen + + +:relative-goto line-count|N% +══════════════════════════════════════════════════════════════════════ + Move the current view up or down by the given amount +Parameter + line-count|N% The amount to move the view by. +See Also + :goto, :next-location, :next-mark, :prev-location, :prev-mark +Examples +#1 To move 22 lines down in the view: + :relative-goto +22 + + +#2 To move 10 percent back in the view: + :relative-goto -10% + + + +:reset-config option +══════════════════════════════════════════════════════════════════════ + Reset the configuration option to its default value +Parameter + option The path to the option to reset +See Also + :config +Example +#1 To reset the '/ui/clock-format' option back to the builtin default: + :reset-config /ui/clock-format + + + +:reset-session +══════════════════════════════════════════════════════════════════════ + Reset the session state, clearing all filters, highlights, and + bookmarks + + +:save-session +══════════════════════════════════════════════════════════════════════ + Save the current state as a session + + +:session lnav-command +══════════════════════════════════════════════════════════════════════ + Add the given command to the session file (~/.lnav/session) +Parameter + lnav-command The lnav command to save. + +Example +#1 To add the command ':highlight foobar' to the session file: + :session :highlight foobar + + + +:set-min-log-level log-level +══════════════════════════════════════════════════════════════════════ + Set the minimum log level to display in the log view +Parameter + log-level The new minimum log level + +Example +#1 To set the minimum log level displayed to error: + :set-min-log-level error + + + +:show-fields field-name1 [... field-nameN] +══════════════════════════════════════════════════════════════════════ + Show log message fields that were previously hidden +Parameter + field-name The name of the field to show +See Also + :enable-word-wrap, :hide-fields, :highlight +Example +#1 To show all the log_procname fields in all formats: + :show-fields log_procname + + + +:show-file path +══════════════════════════════════════════════════════════════════════ + Show the given file(s) and resume indexing. +Parameter + path The path or glob pattern that specifies the files to show + + +:show-lines-before-and-after +══════════════════════════════════════════════════════════════════════ + Show lines that were hidden by the 'hide-lines' commands +See Also + :filter-in, :filter-out, :hide-lines-after, :hide-lines-before, + :hide-unmarked-lines, :toggle-filtering + +:show-only-this-file +══════════════════════════════════════════════════════════════════════ + Show only the file for the top line in the view + + +:show-unmarked-lines +══════════════════════════════════════════════════════════════════════ + Show lines that have not been bookmarked +See Also + :filter-in, :filter-out, :hide-lines-after, :hide-lines-before, + :hide-unmarked-lines, :hide-unmarked-lines, :mark, :next-mark, + :prev-mark, :toggle-filtering + +:spectrogram field-name +══════════════════════════════════════════════════════════════════════ + Visualize the given message field using a spectrogram +Parameter + field-name The name of the numeric field to visualize. + +Example +#1 To visualize the sc_bytes field in the access_log format: + :spectrogram sc_bytes + + + +:summarize column-name +══════════════════════════════════════════════════════════════════════ + Execute a SQL query that computes the characteristics of the values + in the given column +Parameter + column-name The name of the column to analyze. + +Example +#1 To get a summary of the sc_bytes column in the access_log table: + :summarize sc_bytes + + + +:switch-to-view view-name +══════════════════════════════════════════════════════════════════════ + Switch to the given view +Parameter + view-name The name of the view to switch to. + +Example +#1 To switch to the 'schema' view: + :switch-to-view schema + + + +:tag tag1 [... tagN] +══════════════════════════════════════════════════════════════════════ + Attach tags to the top log line +Parameter + tag The tags to attach +See Also + :comment, :delete-tags, :untag +Example +#1 To add the tags '#BUG123' and '#needs-review' to the top line: + :tag #BUG123 #needs-review + + + +:toggle-filtering +══════════════════════════════════════════════════════════════════════ + Toggle the filtering flag for the current view +See Also + :filter-in, :filter-out, :hide-lines-after, :hide-lines-before, + :hide-unmarked-lines + +:toggle-view view-name +══════════════════════════════════════════════════════════════════════ + Switch to the given view or, if it is already displayed, switch to + the previous view +Parameter + view-name The name of the view to toggle the display of. + +Example +#1 To switch to the 'schema' view if it is not displayed or switch back to the previous + view: + :toggle-view schema + + + +:unix-time seconds +══════════════════════════════════════════════════════════════════════ + Convert epoch time to a human-readable form +Parameter + seconds The epoch timestamp to convert + +Example +#1 To convert the epoch time 1490191111: + :unix-time 1490191111 + + + +:untag tag1 [... tagN] +══════════════════════════════════════════════════════════════════════ + Detach tags from the top log line +Parameter + tag The tags to detach +See Also + :comment, :tag +Example +#1 To remove the tags '#BUG123' and '#needs-review' from the top line: + :untag #BUG123 #needs-review + + + +:write-table-to path +══════════════════════════════════════════════════════════════════════ + Write SQL results to the given file in a tabular format +Parameter + path The path to the file to write +See Also + :alt-msg, :append-to, :create-logline-table, :create-search-table, + :echo, :echo, :eval, :pipe-line-to, :pipe-to, :redirect-to, + :redirect-to, :write-csv-to, :write-csv-to, :write-csv-to, + :write-json-to, :write-json-to, :write-json-to, :write-jsonlines-to, + :write-jsonlines-to, :write-jsonlines-to, :write-raw-to, :write-raw-to, + :write-raw-to, :write-screen-to, :write-screen-to, :write-screen-to, + :write-to, :write-to, :write-view-to, :write-view-to, :write-view-to +Example +#1 To write SQL results as text to /tmp/table.txt: + :write-table-to /tmp/table.txt + + + +:write-csv-to path +══════════════════════════════════════════════════════════════════════ + Write SQL results to the given file in CSV format +Parameter + path The path to the file to write +See Also + :alt-msg, :append-to, :create-logline-table, :create-search-table, + :echo, :echo, :eval, :pipe-line-to, :pipe-to, :redirect-to, + :redirect-to, :write-json-to, :write-json-to, :write-json-to, + :write-jsonlines-to, :write-jsonlines-to, :write-jsonlines-to, + :write-raw-to, :write-raw-to, :write-raw-to, :write-screen-to, + :write-screen-to, :write-screen-to, :write-table-to, :write-table-to, + :write-table-to, :write-to, :write-to, :write-view-to, :write-view-to, + :write-view-to +Example +#1 To write SQL results as CSV to /tmp/table.csv: + :write-csv-to /tmp/table.csv + + + +:write-json-to path +══════════════════════════════════════════════════════════════════════ + Write SQL results to the given file in JSON format +Parameter + path The path to the file to write +See Also + :alt-msg, :append-to, :create-logline-table, :create-search-table, + :echo, :echo, :eval, :pipe-line-to, :pipe-to, :redirect-to, + :redirect-to, :write-csv-to, :write-csv-to, :write-csv-to, + :write-jsonlines-to, :write-jsonlines-to, :write-jsonlines-to, + :write-raw-to, :write-raw-to, :write-raw-to, :write-screen-to, + :write-screen-to, :write-screen-to, :write-table-to, :write-table-to, + :write-table-to, :write-to, :write-to, :write-view-to, :write-view-to, + :write-view-to +Example +#1 To write SQL results as JSON to /tmp/table.json: + :write-json-to /tmp/table.json + + + +:write-jsonlines-to path +══════════════════════════════════════════════════════════════════════ + Write SQL results to the given file in JSON Lines format +Parameter + path The path to the file to write +See Also + :alt-msg, :append-to, :create-logline-table, :create-search-table, + :echo, :echo, :eval, :pipe-line-to, :pipe-to, :redirect-to, + :redirect-to, :write-csv-to, :write-csv-to, :write-csv-to, + :write-json-to, :write-json-to, :write-json-to, :write-raw-to, + :write-raw-to, :write-raw-to, :write-screen-to, :write-screen-to, + :write-screen-to, :write-table-to, :write-table-to, :write-table-to, + :write-to, :write-to, :write-view-to, :write-view-to, :write-view-to +Example +#1 To write SQL results as JSON Lines to /tmp/table.json: + :write-jsonlines-to /tmp/table.json + + + +:write-raw-to [--view={log,db}] path +══════════════════════════════════════════════════════════════════════ + In the log view, write the original log file content of the marked + messages to the file. In the DB view, the contents of the cells are + written to the output file. +Parameters + --view={log,db} The view to use as the source of data + path The path to the file to write +See Also + :alt-msg, :append-to, :create-logline-table, :create-search-table, + :echo, :echo, :eval, :pipe-line-to, :pipe-to, :redirect-to, + :redirect-to, :write-csv-to, :write-csv-to, :write-csv-to, + :write-json-to, :write-json-to, :write-json-to, :write-jsonlines-to, + :write-jsonlines-to, :write-jsonlines-to, :write-screen-to, + :write-screen-to, :write-screen-to, :write-table-to, :write-table-to, + :write-table-to, :write-to, :write-to, :write-view-to, :write-view-to, + :write-view-to +Example +#1 To write the marked lines in the log view to /tmp/table.txt: + :write-raw-to /tmp/table.txt + + + +:write-screen-to path +══════════════════════════════════════════════════════════════════════ + Write the displayed text or SQL results to the given file without + any formatting +Parameter + path The path to the file to write +See Also + :alt-msg, :append-to, :create-logline-table, :create-search-table, + :echo, :echo, :eval, :pipe-line-to, :pipe-to, :redirect-to, + :redirect-to, :write-csv-to, :write-csv-to, :write-csv-to, + :write-json-to, :write-json-to, :write-json-to, :write-jsonlines-to, + :write-jsonlines-to, :write-jsonlines-to, :write-raw-to, :write-raw-to, + :write-raw-to, :write-table-to, :write-table-to, :write-table-to, + :write-to, :write-to, :write-view-to, :write-view-to, :write-view-to +Example +#1 To write only the displayed text to /tmp/table.txt: + :write-screen-to /tmp/table.txt + + + +:write-table-to path +══════════════════════════════════════════════════════════════════════ + Write SQL results to the given file in a tabular format +Parameter + path The path to the file to write +See Also + :alt-msg, :append-to, :create-logline-table, :create-search-table, + :echo, :echo, :eval, :pipe-line-to, :pipe-to, :redirect-to, + :redirect-to, :write-csv-to, :write-csv-to, :write-csv-to, + :write-json-to, :write-json-to, :write-json-to, :write-jsonlines-to, + :write-jsonlines-to, :write-jsonlines-to, :write-raw-to, :write-raw-to, + :write-raw-to, :write-screen-to, :write-screen-to, :write-screen-to, + :write-to, :write-to, :write-view-to, :write-view-to, :write-view-to +Example +#1 To write SQL results as text to /tmp/table.txt: + :write-table-to /tmp/table.txt + + + +:write-to path +══════════════════════════════════════════════════════════════════════ + Overwrite the given file with any marked lines in the current view +Parameter + path The path to the file to write +See Also + :alt-msg, :append-to, :echo, :echo, :eval, :pipe-line-to, :pipe-to, + :redirect-to, :redirect-to, :write-csv-to, :write-csv-to, + :write-json-to, :write-json-to, :write-jsonlines-to, + :write-jsonlines-to, :write-raw-to, :write-raw-to, :write-screen-to, + :write-screen-to, :write-table-to, :write-table-to, :write-view-to, + :write-view-to +Example +#1 To write marked lines to the file /tmp/interesting-lines.txt: + :write-to /tmp/interesting-lines.txt + + + +:write-view-to path +══════════════════════════════════════════════════════════════════════ + Write the text in the top view to the given file without any + formatting +Parameter + path The path to the file to write +See Also + :alt-msg, :append-to, :create-logline-table, :create-search-table, + :echo, :echo, :eval, :pipe-line-to, :pipe-to, :redirect-to, + :redirect-to, :write-csv-to, :write-csv-to, :write-csv-to, + :write-json-to, :write-json-to, :write-json-to, :write-jsonlines-to, + :write-jsonlines-to, :write-jsonlines-to, :write-raw-to, :write-raw-to, + :write-raw-to, :write-screen-to, :write-screen-to, :write-screen-to, + :write-table-to, :write-table-to, :write-table-to, :write-to, + :write-to +Example +#1 To write the top view to /tmp/table.txt: + :write-view-to /tmp/table.txt + + + +:zoom-to zoom-level +══════════════════════════════════════════════════════════════════════ + Zoom the histogram view to the given level +Parameter + zoom-level The zoom level + +Example +#1 To set the zoom level to '1-week': + :zoom-to 1-week + + +SQL Reference + +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) + + + +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 + + +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) + + + +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) + + + +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) + + + +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) + + + +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) + + + +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) + + + +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)) + + + +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) + + + +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)) + + + +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 + + + +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('/') + + + +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) + + + +changes() +══════════════════════════════════════════════════════════════════════ + The number of database rows that were changed, inserted, or deleted + by the most recent statement. + + +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(), gunzip(), gzip(), humanize_file_size(), instr(), + leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(), + padr(), printf(), proper(), regexp_capture(), regexp_match(), + regexp_replace(), replace(), replicate(), reverse(), rightstr(), + rtrim(), sparkline(), spooky_hash(), startswith(), strfilter(), + substr(), trim(), unicode(), upper(), xpath() +Example +#1 To get a string with the code points 0x48 and 0x49: + ;SELECT char(0x48, 0x49) + + + +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(), + gunzip(), gzip(), humanize_file_size(), instr(), leftstr(), length(), + logfmt2json(), lower(), ltrim(), padc(), padl(), padr(), printf(), + proper(), regexp_capture(), regexp_match(), regexp_replace(), + replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(), + spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(), + upper(), xpath() +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) + + + +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) + + + +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 + + + +cume_dist() +══════════════════════════════════════════════════════════════════════ + Returns the cumulative distribution +See Also + dense_rank(), first_value(), lag(), last_value(), lead(), nth_value(), + ntile(), percent_rank(), rank(), row_number() + +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') + + + +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') + + + +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()) + + + +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() + +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('') + + + +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(), + gunzip(), gzip(), humanize_file_size(), instr(), leftstr(), length(), + logfmt2json(), lower(), ltrim(), padc(), padl(), padr(), printf(), + proper(), regexp_capture(), regexp_match(), regexp_replace(), + replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(), + spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(), + upper(), xpath() +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') + + + +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) + + + +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(), + gunzip(), gzip(), humanize_file_size(), instr(), leftstr(), length(), + logfmt2json(), lower(), ltrim(), padc(), padl(), padr(), printf(), + proper(), regexp_capture(), regexp_match(), regexp_replace(), + replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(), + spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(), + upper(), xpath() +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') + + + +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() + +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) + + + +generate_series(start, stop, [step]) +══════════════════════════════════════════════════════════════════════ + A table-valued-function that returns the whole numbers between a + lower and upper bound, inclusive +Parameters + start The starting point of the series + stop The stopping point of the series + step The increment between each value +Result + value The number in the series + +Examples +#1 To generate the numbers in the range [10, 14]: + ;SELECT value FROM generate_series(10, 14) + + +#2 To generate every other number in the range [10, 14]: + ;SELECT value FROM generate_series(10, 14, 2) + + +#3 To count down from five to 1: + ;SELECT value FROM generate_series(1, 5, -1) + + + +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') + + + +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') + + + +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') + + + +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(), + gunzip(), gzip(), humanize_file_size(), instr(), leftstr(), length(), + logfmt2json(), lower(), ltrim(), padc(), padl(), padr(), printf(), + proper(), regexp_capture(), regexp_match(), regexp_replace(), + replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(), + spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(), + upper(), xpath() +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 + + + +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(), gunzip(), + gzip(), humanize_file_size(), instr(), leftstr(), length(), + logfmt2json(), lower(), ltrim(), padc(), padl(), padr(), printf(), + proper(), regexp_capture(), regexp_match(), regexp_replace(), + replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(), + spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(), + upper(), xpath() +Example +#1 To produce a hash of all of the values of 'column1': + ;SELECT group_spooky_hash(column1) FROM (VALUES ('abc'), ('123')) + + + +gunzip(b, ...) +══════════════════════════════════════════════════════════════════════ + Decompress a gzip file +Parameter + b The blob to decompress +See Also + char(), charindex(), endswith(), extract(), group_concat(), + group_spooky_hash(), gzip(), humanize_file_size(), instr(), leftstr(), + length(), logfmt2json(), lower(), ltrim(), padc(), padl(), padr(), + printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(), + replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(), + spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(), + upper(), xpath() + +gzip(value, ...) +══════════════════════════════════════════════════════════════════════ + Compress a string into a gzip file +Parameter + value The value to compress +See Also + char(), charindex(), endswith(), extract(), group_concat(), + group_spooky_hash(), gunzip(), humanize_file_size(), instr(), + leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(), + padr(), printf(), proper(), regexp_capture(), regexp_match(), + regexp_replace(), replace(), replicate(), reverse(), rightstr(), + rtrim(), sparkline(), spooky_hash(), startswith(), strfilter(), + substr(), trim(), unicode(), upper(), xpath() + +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') + + + +humanize_file_size(value) +══════════════════════════════════════════════════════════════════════ + Format the given file size as a human-friendly string +Parameter + value The file size to format +See Also + char(), charindex(), endswith(), extract(), group_concat(), + group_spooky_hash(), gunzip(), gzip(), instr(), leftstr(), length(), + logfmt2json(), lower(), ltrim(), padc(), padl(), padr(), printf(), + proper(), regexp_capture(), regexp_match(), regexp_replace(), + replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(), + spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(), + upper(), xpath() +Example +#1 To format an amount: + ;SELECT humanize_file_size(10 * 1024 * 1024) + + + +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) + + + +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(), gunzip(), gzip(), humanize_file_size(), leftstr(), + length(), logfmt2json(), lower(), ltrim(), padc(), padl(), padr(), + printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(), + replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(), + spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(), + upper(), xpath() +Example +#1 To test get the position of 'b' in the string 'abc': + ;SELECT instr('abc', 'b') + + + +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') + + + +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') + + + +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]')) + + + +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') + + + +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)) + + + +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)) + + + +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') + + + +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() + +last_insert_rowid() +══════════════════════════════════════════════════════════════════════ + Returns the ROWID of the last row insert from the database + connection which invoked the function + + +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() + +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() + +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(), gunzip(), gzip(), humanize_file_size(), instr(), + length(), logfmt2json(), lower(), ltrim(), padc(), padl(), padr(), + printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(), + replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(), + spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(), + upper(), xpath() +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) + + + +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(), gunzip(), gzip(), humanize_file_size(), instr(), + leftstr(), logfmt2json(), lower(), ltrim(), padc(), padl(), padr(), + printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(), + replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(), + spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(), + upper(), xpath() +Example +#1 To get the length of the string 'abc': + ;SELECT length('abc') + + + +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%', ':') + + + +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 + + +likely(value) +══════════════════════════════════════════════════════════════════════ + Short-hand for likelihood(X,0.9375) +Parameter + value The boolean value to return + + +lnav_top_file() +══════════════════════════════════════════════════════════════════════ + Return the name of the file that the top line in the current view + came from. + + +lnav_version() +══════════════════════════════════════════════════════════════════════ + Return the current version of lnav + + +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 + + +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) + + + +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) + + + +log_top_datetime() +══════════════════════════════════════════════════════════════════════ + Return the timestamp of the line at the top of the log view. + + +log_top_line() +══════════════════════════════════════════════════════════════════════ + Return the line number at the top of the log view. + + +logfmt2json(str) +══════════════════════════════════════════════════════════════════════ + Convert a logfmt-encoded string into JSON +Parameter + str The logfmt message to parse +See Also + char(), charindex(), endswith(), extract(), group_concat(), + group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(), + leftstr(), length(), lower(), ltrim(), padc(), padl(), padr(), + printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(), + replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(), + spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(), + upper(), xpath() +Example +#1 To extract key/value pairs from a log message: + ;SELECT logfmt2json('foo=1 bar=2 name="Rolo Tomassi"') + + + +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(), gunzip(), gzip(), humanize_file_size(), instr(), + leftstr(), length(), logfmt2json(), ltrim(), padc(), padl(), padr(), + printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(), + replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(), + spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(), + upper(), xpath() +Example +#1 To lowercase the string 'AbC': + ;SELECT lower('AbC') + + + +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(), gunzip(), gzip(), humanize_file_size(), instr(), + leftstr(), length(), logfmt2json(), lower(), padc(), padl(), padr(), + printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(), + replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(), + spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(), + upper(), xpath() +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') + + + +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 + + + +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 + + + +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() + +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() + +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) + + + +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(), gunzip(), gzip(), humanize_file_size(), instr(), + leftstr(), length(), logfmt2json(), lower(), ltrim(), padl(), padr(), + printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(), + replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(), + spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(), + upper(), xpath() +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' + + + +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(), gunzip(), gzip(), humanize_file_size(), instr(), + leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padr(), + printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(), + replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(), + spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(), + upper(), xpath() +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) + + + +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(), gunzip(), gzip(), humanize_file_size(), instr(), + leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(), + printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(), + replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(), + spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(), + upper(), xpath() +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' + + + +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() + +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() + + + +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) + + + +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(), gunzip(), gzip(), humanize_file_size(), instr(), + leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(), + padr(), proper(), regexp_capture(), regexp_match(), regexp_replace(), + replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(), + spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(), + upper(), xpath() +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) + + + +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(), gunzip(), gzip(), humanize_file_size(), instr(), + leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(), + padr(), printf(), regexp_capture(), regexp_match(), regexp_replace(), + replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(), + spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(), + upper(), xpath() +Example +#1 To capitalize the words in the string 'hello, world!': + ;SELECT proper('hello, world!') + + + +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') + + + +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) + + + +raise_error(msg) +══════════════════════════════════════════════════════════════════════ + Raises an error with the given message when executed +Parameter + msg The error message + + +random() +══════════════════════════════════════════════════════════════════════ + Returns a pseudo-random integer between -9223372036854775808 and + + 9223372036854775807. + + +randomblob(N) +══════════════════════════════════════════════════════════════════════ + Return an N-byte blob containing pseudo-random bytes. +Parameter + N The size of the blob in bytes. + + +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() + +readlink(path) +══════════════════════════════════════════════════════════════════════ + Read the target of a symbolic link. +Parameter + path The path to the symbolic link. +See Also + basename(), dirname(), joinpath(), realpath() + +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() + +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 + + +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(), gunzip(), gzip(), humanize_file_size(), instr(), + leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(), + padr(), printf(), proper(), regexp_match(), regexp_replace(), + replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(), + spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(), + upper(), xpath() +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+)') + + + +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(), gunzip(), gzip(), humanize_file_size(), instr(), + leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(), + padr(), printf(), proper(), regexp_capture(), regexp_replace(), + regexp_replace(), replace(), replicate(), reverse(), rightstr(), + rtrim(), sparkline(), spooky_hash(), startswith(), strfilter(), + substr(), trim(), unicode(), upper(), xpath() +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') + + + +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(), gunzip(), gzip(), humanize_file_size(), instr(), + leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(), + padr(), printf(), proper(), regexp_capture(), regexp_match(), + regexp_match(), replace(), replicate(), reverse(), rightstr(), rtrim(), + sparkline(), spooky_hash(), startswith(), strfilter(), substr(), + trim(), unicode(), upper(), xpath() +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>') + + + +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(), gunzip(), gzip(), humanize_file_size(), instr(), + leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(), + padr(), printf(), proper(), regexp_capture(), regexp_match(), + regexp_replace(), replicate(), reverse(), rightstr(), rtrim(), + sparkline(), spooky_hash(), startswith(), strfilter(), substr(), + trim(), unicode(), upper(), xpath() +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') + + + +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(), gunzip(), gzip(), humanize_file_size(), instr(), + leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(), + padr(), printf(), proper(), regexp_capture(), regexp_match(), + regexp_replace(), replace(), reverse(), rightstr(), rtrim(), + sparkline(), spooky_hash(), startswith(), strfilter(), substr(), + trim(), unicode(), upper(), xpath() +Example +#1 To repeat the string 'abc' three times: + ;SELECT replicate('abc', 3) + + + +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(), gunzip(), gzip(), humanize_file_size(), instr(), + leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(), + padr(), printf(), proper(), regexp_capture(), regexp_match(), + regexp_replace(), replace(), replicate(), rightstr(), rtrim(), + sparkline(), spooky_hash(), startswith(), strfilter(), substr(), + trim(), unicode(), upper(), xpath() +Example +#1 To reverse the string 'abc': + ;SELECT reverse('abc') + + + +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(), gunzip(), gzip(), humanize_file_size(), instr(), + leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(), + padr(), printf(), proper(), regexp_capture(), regexp_match(), + regexp_replace(), replace(), replicate(), reverse(), rtrim(), + sparkline(), spooky_hash(), startswith(), strfilter(), substr(), + trim(), unicode(), upper(), xpath() +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) + + + +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) + + + +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 + + + +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(), gunzip(), gzip(), humanize_file_size(), instr(), + leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(), + padr(), printf(), proper(), regexp_capture(), regexp_match(), + regexp_replace(), replace(), replicate(), reverse(), rightstr(), + sparkline(), spooky_hash(), startswith(), strfilter(), substr(), + trim(), unicode(), upper(), xpath() +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') + + + +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) + + + +sparkline(value, [upper]) +══════════════════════════════════════════════════════════════════════ + Function used to generate a sparkline bar chart. The non-aggregate + version converts a single numeric value on a range to a bar chart + character. The aggregate version returns a string with a bar + character for every numeric input +Parameters + value The numeric value to convert + upper The upper bound of the numeric range. The non-aggregate + version defaults to 100. The aggregate version uses the + largest value in the inputs. +See Also + char(), charindex(), endswith(), extract(), group_concat(), + group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(), + leftstr(), length(), logfmt2json(), 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(), xpath() +Examples +#1 To get the unicode block element for the value 32 in the range of 0-128: + ;SELECT sparkline(32, 128) + + +#2 To chart the values in a JSON array: + ;SELECT sparkline(value) FROM json_each('[0, 1, 2, 3, 4, 5, 6, 7, 8]') + + + +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(), gunzip(), gzip(), humanize_file_size(), instr(), + leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(), + padr(), printf(), proper(), regexp_capture(), regexp_match(), + regexp_replace(), replace(), replicate(), reverse(), rightstr(), + rtrim(), sparkline(), startswith(), strfilter(), substr(), trim(), + unicode(), upper(), xpath() +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) + + + +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 + + +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') + + + +sqlite_source_id() +══════════════════════════════════════════════════════════════════════ + Returns a string that identifies the specific version of the source + code that was used to build the SQLite library. + + +sqlite_version() +══════════════════════════════════════════════════════════════════════ + Returns the version string for the SQLite library that is running. + + +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) + + + +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(), gunzip(), gzip(), humanize_file_size(), instr(), + leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(), + padr(), printf(), proper(), regexp_capture(), regexp_match(), + regexp_replace(), replace(), replicate(), reverse(), rightstr(), + rtrim(), sparkline(), spooky_hash(), strfilter(), substr(), trim(), + unicode(), upper(), xpath() +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') + + + +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(), gunzip(), gzip(), humanize_file_size(), instr(), + leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(), + padr(), printf(), proper(), regexp_capture(), regexp_match(), + regexp_replace(), replace(), replicate(), reverse(), rightstr(), + rtrim(), sparkline(), spooky_hash(), startswith(), substr(), trim(), + unicode(), upper(), xpath() +Example +#1 To get the 'b', 'c', and 'd' characters from the string 'abcabc': + ;SELECT strfilter('abcabc', 'bcd') + + + +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') + + + +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(), gunzip(), gzip(), humanize_file_size(), instr(), + leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(), + padr(), printf(), proper(), regexp_capture(), regexp_match(), + regexp_replace(), replace(), replicate(), reverse(), rightstr(), + rtrim(), sparkline(), spooky_hash(), startswith(), strfilter(), trim(), + unicode(), upper(), xpath() +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) + + + +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 + + + +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') + + + +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') + + + +timeslice(time, slice) +══════════════════════════════════════════════════════════════════════ + Return the start of the slice of time that the given timestamp falls + in. If the time falls outside of the slice, NULL is returned. +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_msecs, '5m') AS slice, count(1) + FROM lnav_example_log GROUP BY slice + + +#3 To group log messages by those before 4:30am and after: + ;SELECT timeslice(log_time_msecs, 'before 4:30am') AS slice, count(1) FROM + lnav_example_log GROUP BY slice + + + +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 + + + +total_changes() +══════════════════════════════════════════════════════════════════════ + Returns the number of row changes caused by INSERT, UPDATE or DELETE + statements since the current database connection was opened. + + +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(), gunzip(), gzip(), humanize_file_size(), instr(), + leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(), + padr(), printf(), proper(), regexp_capture(), regexp_match(), + regexp_replace(), replace(), replicate(), reverse(), rightstr(), + rtrim(), sparkline(), spooky_hash(), startswith(), strfilter(), + substr(), unicode(), upper(), xpath() +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+-', '-+') + + + +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') + + + +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(), gunzip(), gzip(), humanize_file_size(), instr(), + leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(), + padr(), printf(), proper(), regexp_capture(), regexp_match(), + regexp_replace(), replace(), replicate(), reverse(), rightstr(), + rtrim(), sparkline(), spooky_hash(), startswith(), strfilter(), + substr(), trim(), upper(), xpath() +Example +#1 To get the unicode code point for the first character of 'abc': + ;SELECT unicode('abc') + + + +unlikely(value) +══════════════════════════════════════════════════════════════════════ + Short-hand for likelihood(X, 0.0625) +Parameter + value The boolean value to return + + +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(), gunzip(), gzip(), humanize_file_size(), instr(), + leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(), + padr(), printf(), proper(), regexp_capture(), regexp_match(), + regexp_replace(), replace(), replicate(), reverse(), rightstr(), + rtrim(), sparkline(), spooky_hash(), startswith(), strfilter(), + substr(), trim(), unicode(), xpath() +Example +#1 To uppercase the string 'aBc': + ;SELECT upper('aBc') + + + +xpath(xpath, xmldoc) +══════════════════════════════════════════════════════════════════════ + A table-valued function that executes an xpath expression over an + XML string and returns the selected values. +Parameters + xpath The XPATH expression to evaluate over the XML document. + xmldoc The XML document as a string. +Results + result The result of the XPATH expression. + node_path The absolute path to the node containing the result. + node_attr The node's attributes stored in JSON object. + node_text The node's text value. +See Also + char(), charindex(), endswith(), extract(), group_concat(), + group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(), + leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(), + padr(), printf(), proper(), regexp_capture(), regexp_match(), + regexp_replace(), replace(), replicate(), reverse(), rightstr(), + rtrim(), sparkline(), spooky_hash(), startswith(), strfilter(), + substr(), trim(), unicode(), upper() +Examples +#1 To select the XML nodes on the path '/abc/def': + ;SELECT * FROM xpath('/abc/def', '<abc><def a="b">Hello</def><def>Bye</def></abc>') + + +#2 To select all 'a' attributes on the path '/abc/def': + ;SELECT * FROM xpath('/abc/def/@a', '<abc><def a="b">Hello</def><def>Bye</def></abc>') + + +#3 To select the text nodes on the path '/abc/def': + ;SELECT * FROM xpath('/abc/def/text()', '<abc><def a="b">Hello ★</def></abc>') + + + +zeroblob(N) +══════════════════════════════════════════════════════════════════════ + Returns a BLOB consisting of N bytes of 0x00. +Parameter + N The size of the BLOB. + + +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 + + + +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 + + + +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 + + +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. + + +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 + + + +DROP VIEW [IF EXISTS] [schema-name.] view-name +══════════════════════════════════════════════════════════════════════ + Drop a view +Parameters + + +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!') + + + +OVER window-name +══════════════════════════════════════════════════════════════════════ + Executes the preceding function over a window +Parameter + window-name The name of the window definition + + +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 The expression used to generate a result for this + 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 + + + +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 + + + +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. + |