search

hyee / dbcli

Windows/Linux/OSX/MSYS command line utility for Oracle/MySQL/PostgreSQL
MIT License
59 stars 16 forks source link

readme

DBCLI : A CLI utility for DBA (Oracle/MySQL/DB2)

MIT License HELP sshot-13

For more detailed usage, please refer to the PowerPoint file under the docs directory

Description

DBCLI is a portable database command line utility, for DBA or application support to easily manage and run their common used scripts, similar to database built-in CLI tools but is more flexible.

It is mainly developed by Lua language, and bridges to JDBC to access database, many of the files are SQL scripts that easy to maintain.
It is designed to support multiple databases, and includes 2 types of modules, one is the public module whose functions are visible across all database platforms, and another one is the platform-specific module.

It is not intended to support all features of the existing CLI utilities that embedded by the specific database(Oracle SQL*Plus, DB2 CLP, etc), but to provide the features other than those tools:

Download

DBCLI does not require compile or installation, but depends on JRE 1.8+. Click Download ZIP,extract and use. Or you can also download from the published releases
Branch With-JRE contains the shrinked JRE bundle, if you don't have JRE installed, you may switch to that branch to download. Due to the region and charset differences, this JRE bundle is not guaranteed to work well.

Windows: Configure terminal window

For Windows OS, to avoid the word wrap in the terminal window which affects the print layout, following settings are recommended in Windows command window:

Linux: Pre-Requisitions

Linux must be the 64-bit version, and make sure glibc >= v2.4(use ldd --version to verify) are installed. You can use command <command> | more to vertically/horizontally scroll the command output, or more last or scroll the last command output.

OSX: Pre-Requisitions

Only tested on OSX 10.12, lower version should be fine. Java 1.8+ is required. It's recommended to add entry <hostname>.local into /etc/hosts to speed up the JVM performance. For Example:

127.0.0.1  localhost mac-pc.local
::1        localhost mac-pc.local

And sometimes you may run into below exception when try to execute dbcli.sh: "luajit" cannot be opened because the developer cannot be verified The solution is to disable the restriction: sudo spctl --master-disable

Configure environment

Before running dbcli, make sure that you have installed JRE 1.8+ in your local PC. If you are using the version of "With-JRE" branch, this step can be skipped.

Create file init.cfg(or init.conf for non-Windows) under the "data" directory with following content(for non-Windows OS use export instead):

SET JRE_HOME=<JRE HOME>
SET TNS_ADM=<location of tnsnames.ora>

Of which TNS_ADM is optional unless you need to connect Oracle via tnsnames.ora, or may also refer to init_sample.cfg for more settings. An example is:

SET JRE_HOME=d:\soft\java
SET TNS_ADM=d:\Soft\InstanceClient\network\admin

The default charset is UTF-8, to use another charset, just change the DBCLI_ENCODING environment variable globally or in the mentioned config file. For example:

Launch DBCLI Console

After the above configurations, you are able to start DBCLI by executing dbcli.bat or bin\ConsoleZ\Console.exe.

dbcli.bat also supports arguments in "<command>=<args>" format, all available commands can be found by typing help or help -a.

For example: dbcli.bat "connect=tiger/scott@orcl"

Switch Database Platform

DBCLI supports multiple database platforms(oracle,mysql,db2,etc), default is Oracle.

To permanently change the preferred platform other than Oracle, run set -p platform <platform> after launching the console. For example, set -p platform db2

Without the -p option, the change only takes affect on current console window. Type set platform to see all available options.

Command Overview

Command Types

The command rules are similar to SQL*Plus:

Line Wrap

For the output lines that wider than the buffer width(in Posix System it's the screen width), the overflow part will be trimmed. To view the complete information:

Color Solutions

dbcli has defined some colors that used in different scenario, those colors can be found by set color.
You are able to change the colors by executing set [-p] <color name> <new code>, of which the code can be referenced by command ansi, and -p means permanently.
For example, set promptcolor BLU;BWHT;UDL is to change the prompt color as Blue Foreground + White Background + Underlined, and set promptcolor default to change as default.
It's possible to turn of using colors, which is done by executing set ansicolor off

Releases

There are 5 zip files in the releases page that used in different scenario, all those releases don't include the src directory:

References

The utility depends on some binary files of open-source projects, the authors own the rights. The binaries have been included into this project. Below is the list:

JLine3      : (BSD)    https://github.com/jline/jline3
JSch        : (BSD)    http://www.jcraft.com/jsch/
ConsoleZ    : (GNU)    https://github.com/cbucher/console
luajit v2.1 : (MIT)    https://github.com/LuaJIT/LuaJIT
jnlua       : (MIT)    https://github.com/hyee/JNLuaJIT(revised from jnlua.googlecode.com)
OpenCSV     : (MIT)    https://github.com/hyee/OpenCSV(revised from opencsv.sourceforge.net)
MessagePack : (MIT)    https://github.com/fperrad/lua-MessagePack (pure lua)
dygraphs    : (MIT)    https://github.com/danvk/dygraphs
JsonForLua  : (MIT)    https://github.com/craigmj/json4lua 
PSCP        : (MIT)    http://www.putty.org/ 
LPeg        : (MIT)    http://www.inf.puc-rio.br/~roberto/lpeg
xml2lua     : (MIT)    https://github.com/manoelcampos/xml2lua
JNA         : (LGPL)   https://github.com/java-native-access/jna
NuProcess   : (Apache) https://github.com/brettwooldridge/NuProcess
Luv(libuv)  : (Apache) https://github.com/luvit/luv
ConEmu      : (BSD)    https://github.com/Maximus5/ConEmu
juniversalchardet :    https://github.com/albfernandez/juniversalchardet

Besides, some SQL scripts come from internet.

Customize Commands

Customize new simple commands

You are able to use command alias to define the shortcut of the simple command, for instance:
alias sf select * from (select * from $*) where rownum<=50;

In this case, you can execute sf dba_objects where object_id<1000 to simplify the input.

The alias command supports the $1-$9 and $* wildcard characters, of which $n corresponds to the nth parameter, and $* means the concatenation of $n+1-$9 via space. Type alias to see more usage.

Customize new sub-commands

Similar to SQLPlus script, a sub-command script is the combination of one or more existing commands that supports user-input parameters. Take command ora for example, to define a sub-command xxx, create file oracle\ora\xxx.sql and fill with following content:
`select from (select * from &V1) where rownum<=50;`

After that, run ora -r to take effect, then you can run ora xxx dba_objects to query the view.

The utility has created some pre-defined commands, if you want to modify the those commands without concerning of overriding back by the new version, just create a sub-folder under the ora directory, and put the updated file into it, because for the scripts with same name, the one in the sub directory will be treated as higher priority. Or you may also use ora -l <path> to link to another work dir.

Commands ora/show/sys/snap/chart/sql/shell/etc follow the similar rules:

Customize new root commands

Different from sub-commands, the root command must be a lua script. To plug a user-define lua script into the utility, just add its path in data\plugin.cfg, you may refer to data\plugin_sample.cfg for the example.

Below are the common interfaces that can be used in the script:

You may also: