sqlplus_fzf

Some enhancements to sqlplus UI based on rlwrap and fzf

Introduction

There are couple of fuzzy search tools available, to mention a few -

• skim (written in rust)
• peco (written in go)
• fzf (written in go) They have very similar functionality as well - user interface, however, fzf is probably most popular (at least - according to the github stars) and it was the first one i stumbled upon among similar utilities, so in this setup i'll focus only on fzf, though - it can be done with each other fuzzy search tool from mentioned above in the exact the same or similar way. Fzf provides very impressive out of the box features, which increase enormously speed of working in the linux terminal (though - it is available for all major platforms - also, mac and windows either) To mention a few:
• reverse history search (bound by default to Ctrl-R)
• file search, optionally with preview (bound by default to Ctrl-T)

The goal of this setup is to have the same user experience which fzf provides to shell - in another cli applications, for instance in sqlplus. One of possible solutons is to use rlwrap (readline wrapper) as the intermediate layer between cli and fzf. Using an alias like alias sqlplus="rlwrap sqlplus" is a well known approach to bring readline editing capabilities to sqlplus, however, till recently i was not aware, that rlwrap (at least recent versions) brings very powerful capability of using filters and provides even set of predefined filters - one of them is called hande_hotkeys and there is even defined hotkey mapping for fzf, so very little things are remaining to do.

sqlplus_fzf_screencast.mp4

Implementation

• add hotkey handler for finding sql scripts - for example - all sql scripts from the home directory, or the set of directories defined in ORACLE_PATH or SQLPATH
• add a preview option with a pager with syntax highlighting capabilities
• add the chosen hotkeys to local .inputrc

Keybindings

• Ctrl-R is bound to history search
• Ctrl-T - to search sql script with preview, in fzf window Enter brings the selection (with @ sign prepended) back to sqlplus ESC or Ctrl-C exits the fzf window and goes back to sqlpus without selection, ALT-e loads selection into sqlplus editor (as defined with define _editor or $VISUAL env variable
• Alt-down bound to scroll down preview
• Alt-up to scroll up preview, the mouse is activated (to disable - call fzf with --no-mouse option) and preview can be scrolled with the mouse wheel either
• Shift-left-mouse does linewise selection
• Alt-left-mouse does the blockwise selection - as can be seen in the screencast, If using in a putty session - selection is automatically copied to the clipboard, but many other terminals (e.g. - kitty, iterm2, wezterm, etc) can be configured in similar way. Keybindings for linewise/blockwise selection are dependent on the terminal used - the mentioned above are for putty, in kde konsole the modifiers are different - for example, blockwise selection is done with Ctrl-alt-left-mouse

Multiline statements

Unfortunately, i haven't found a way to deal with multiline statements in history search - like

select count(*), deptno
from emp
group by deptno
/

in fzf it will be still shown as multiple independent lines - both , rlwrap and fzf support to certain degree multi line input/output by fzf it is --read0 --print0 (delitmiter is ascii(0)), by rlwrap it is -m "newline-substitute", by default " \ ", but i don't see a possibility to give an ascii(0) as argument to this switch, besides that - rlwrap documentation states that -m is not working well with filters. However, fzf with -m switch (multiline) allows multiple lines selection with Tab or Shift-Tab and rlwrap can handle the multiline selection pretty well, so, i'll leave it at this point with fzf -m switch.

Prerequisites:

• fzf installation is pretty simple - i prefer to just clone the github repository
• rlwrap - in OEL 8 is provided via ol8_developer_EPEL repository and can be installed with dnf, i prefer however the local installation (compile from source) - because often user who uses sqlplus (typically - oracle) doesn't have root rights, it makes as well easier placement of rlwrap filter, though - if directory of rlwrap filter is not accessible, custom filter can be placed in any directory if this directory is configured as RLWRAP_FILTERDIR env variable, additionally RlwrapFilter.pm has to be copied or symlinked there.
• pager with syntax highlighting - i have found not too many alternatives, the simplest one is bat, another one - less with gnu source-highlight, moar with chroma and finally vim/neovim. If syntax hihlighting is not required - then cat, less, more, most can be used. Anyway, this setup uses bat which can be downloaded as prebuilt binary into a directory on the path, or just cargo install bat if rust toolchain is installed.

setup_oel8.sh shell script tries to perform automated installation of whole toolchain from scratch, however, it is very difficult to test all possible configurations, therefore - intention is rather not to execute this script as is, but copy/paste single statements and modify them accordingly to local configuratin if required. Assumption is - the oracle client setup is already available and the set of sql scripts either, if not - one of popular public sql script repositories can be cloned - for example from Tanel Poder, Sayan Malakshinov or Carlos Sieerra