fbsql - a command-line client for Firebird

fbsql is a simple command-line client for the Firebird database, inspired by PostgreSQL's psql. It is very much a proof-of-concept serving primarily as a testbed for libfq (itself an experimental partial port of PostgreSQL's libpq created to facilitate the development of firebird_fdw).

fbsql provides some advantages over Firebird's native client isql, including:

• libreadline-based tab completion for both SQL and database objects
• some useful meta commands ("slash commands")
• autocommit mode (default: on)
• indicates transaction status in the prompt

Note that by default, fbsql assumes a UTF-8 environment. An alternative encoding can be provided with the -C/--client-encoding command line option, however it is not possible to switch encoding during a session.

fbsql is far from complete. USE AT YOUR OWN RISK.

Installation

RPM packages for CentOS 6/7 are available via the Fedora "copr" build system; for details see here: https://copr.fedorainfracloud.org/coprs/ibarwick/libfq/

fbsql requires the Firebird C API wrapper libfq; RPM packages are also available (see the libfq GitHub page for details.

If installing from source, as well as fbsql, the Firebird client library and header files are required. Dev packages for libreadline must also be present.

Build with the usual:

./configure
make install

You may need to specify the location of ibase.h in CFLAGS.

Usage

$ fbsql --help
fbsql is an interactive terminal for Firebird.

Usage:
  fbsql [OPTION]... [DBNAME [USERNAME]]

General options:
  -V, --version            output version information, then exit
  -?, --help               show this help, then exit

Connection options:
  -d, --dbname=DBNAME      database to connect to
  -u, --username=USERNAME  database user name
  -p, --password           password
  -C, --client-encoding    client encoding (default: UTF-8)

Display options:
  -E, --echo-internal      display queries generated by internal commands

e.g.:

fbsql -d localhost:employee.fdb -u sysdba -p masterke

The environment variables ISC_DATABASE, ISC_USER and ISC_PASSWORD are also recognized.

A non-default port can be provided as part of the connection string, e.g.

fbsql -d localhost/3051:employee.fdb -u sysdba -p masterke

After connecting to Firebird, queries can be executed. Enter help for some very basic help, or \? for details on available slash commands:

fbsql 0.3.0
Connected to Firebird v4.0.1 (libfq version 0.6.0)
SQL> \?
General
  \copyright             Show fbsql copyright information
  \g or ;                execute query
  \q                     quit fbsql

Display
  \a                     Toggle aligned mode (currently on)
  \format OPTION [VALUE] Set or show table output formatting option:
                           {alignment|border|null}
  \plan [SETTING]        Display plan {off|on|only} (currently off)
  \timing                Toggle execution timing (currently on)
  \tznames               Toggle display of time zone names (currently on)

Environment
  \activity              Show information about current database activity
  \conninfo              Show information about the current connection

Database
  (options: S = show system objects, + = additional detail)
  \l                     List information about the current database
  \autocommit            Toggle autocommit (currently on)
  \d      NAME           List information about the specified object
  \df     [PATTERN]      List information about functions matching [PATTERN]
  \di[S+] [PATTERN]      List information about indexes matching [PATTERN]
  \dp     [PATTERN]      List information about procedures matching [PATTERN]
  \ds[S]  [PATTERN]      List information about sequences (generators) matching [PATTERN]
  \dt[S]  [PATTERN]      List information about tables matching [PATTERN]
  \du                    List users granted privileges on this database
  \dv     [PATTERN]      List information about views matching [PATTERN]
  \util   [COMMAND]      execute utility command
                            {set_index_statistics}

List all tables:

SQL> \dt
              List of tables
 Name             | Owner  | Description
------------------+--------+-------------
 country          | sysdba |
 customer         | sysdba |
 department       | sysdba |
 employee         | sysdba |
 employee_project | sysdba |
 job              | sysdba |
 proj_dept_budget | sysdba |
 project          | sysdba |
 salary_history   | sysdba |
 sales            | sysdba |

Get detailed information about a table:

SQL> \d job
                                 Table "job"
 Column          | Field type    | Modifiers | Default value | Description
-----------------+---------------+-----------+---------------+-------------
 job_code        | VARCHAR(5)    | NOT NULL  |               |
 job_grade       | SMALLINT      | NOT NULL  |               |
 job_country     | VARCHAR(15)   | NOT NULL  |               |
 job_title       | VARCHAR(25)   | NOT NULL  |               |
 min_salary      | NUMERIC(10,2) | NOT NULL  |               |
 max_salary      | NUMERIC(10,2) | NOT NULL  |               |
 job_requirement | BLOB          |           |               |
 language_req    | VARCHAR(15)   |           |               |

Indexes:
  rdb$primary2 PRIMARY KEY (job_code, job_grade, job_country)
  minsalx (job_country, min_salary)
  maxsalx (job_country, max_salary)
Foreign keys:
  rdb$foreign3 FOREIGN KEY (job_country) REFERENCES country (country) ON UPDATE RESTRICT ON DELETE RESTRICT

Limitations

Many, in particular:

• folds all object names to lower case by default and does not properly handle object names with mixed-case
• command terminator is limited to a semicolon; it is currently not possible to create functions/procedures with fbsql
• no BLOB/ARRAY support (due to missing support in libfq)
• does not properly align column data containing non-ASCII characters
• no configuration file support