Working in the turborel Command
The turborel command (The turborel Command) reads
lines of SQL and lines of its own commands. Each command begins with a
backslash and is done by the client, not sent to the server. The
commands set how results are written, describe what the database holds,
run files, keep variables and decide which lines run. This chapter
describes them, and the line editor that reads typed lines.
Backslash commands
A backslash outside a literal, a delimited identifier or a comment
begins a command, which runs to the end of its line. A command may
follow the text of a statement on the same line, so SELECT 1 \g runs
the statement begun. Arguments are split at spaces. One in single
quotes may hold spaces, with a quote inside written twice. One in
double quotes keeps its quotes. \? lists every command.
\echo 'Hello, world' again
Hello, world again
A typed session reads each statement and command after a prompt. A
run of a file or of -c reads the same lines without a prompt.
The examples of this chapter are of this table:
CREATE TABLE STAFF (ID INTEGER PRIMARY KEY, NAME VARCHAR(20) NOT NULL, SALARY DECIMAL(9,2) DEFAULT 0);
INSERT INTO STAFF VALUES (1, 'Ada', 52000), (2, 'Grace', 61000), (3, 'Edsger', NULL);
COMMIT
OK
OK
OK
Typing
At a terminal, lines are read by the client's own line editor. Left and Right move the cursor. Home and End, or Ctrl-A and Ctrl-E, move it to the ends of the line. Ctrl-Left and Ctrl-Right, or Alt-B and Alt-F, move it a word at a time. Backspace and Delete delete a character. Ctrl-U cuts what is before the cursor, Ctrl-K what is after it, and Ctrl-W the word before it. Ctrl-L redraws the line, Ctrl-C throws it away, and Ctrl-D on an empty line ends the session.
Up and Down walk the history of statements and commands typed. A
statement typed over several lines is one entry, its lines shown
together. Ctrl-R searches the history backwards for the text typed
after it, and Ctrl-R again finds an older entry. Enter runs the entry
found, another key takes it to edit, and Escape leaves the line as it
was. An entry that begins with a space is not kept, nor one the same as
the entry before. The history is kept in the file HISTORY_FILE names.
By default that is the environment's TURBOREL_HISTORY, else
.turborel_history in the home directory. At most HISTORY_SIZE entries
are kept, 500 by default.
Tab completes the word before the cursor. What it offers depends on the word before:
| After | Tab offers |
|---|---|
| a backslash | a command's name |
| FROM, JOIN, INTO, UPDATE, TABLE or a describe command | a table's name |
| SCHEMA | a schema's name |
| USER, ROLE, AUTHORIZATION or TO | a user's or role's name |
| SELECT, WHERE, BY, SET or a comparison | a column's name or a key word |
| anything else | a key word |
A word begun in lower case is completed in lower case. A single candidate replaces the word. With several, Tab adds what they share, or lists them if that adds nothing. The names are read from the Information Schema the first time they are needed, and again after a statement that may change them, as CREATE, ALTER or DROP does.
The line editor puts the terminal in raw mode with the stty program.
It restores the terminal after each line and when the client ends.
Where standard input or output is not a terminal, or stty cannot set
its modes, lines are read whole and edited by the terminal alone.
Variables
\set NAME VALUE sets a variable, joining the words of VALUE.
\set NAME sets it empty, and \set alone lists every variable.
\unset NAME unsets one. -v NAME=VALUE on the command line sets one
before the first statement. A name is letters, digits and underscores.
Within a statement, outside its literals, delimited identifiers and
comments, :NAME is the variable's value as written. :'NAME' is the
value as a character string literal, and :"NAME" is the value as a
delimited identifier. :{?NAME} is true if the variable is set and
false if not. A variable not set is left as written, as is the second
colon of ::. The arguments of commands read variables the same way.
\set low 55000
\set name NAME
SELECT :"name" FROM STAFF WHERE SALARY < :low ORDER BY ID;
\echo :ROW_COUNT rows, :{?low} :{?high}
NAME
----------------------
Ada
(1 row)
1 rows, true false
These variables are the client's own:
| Variable | Means |
|---|---|
| AUTOCOMMIT | on makes each statement a transaction of its own, committed as it ends; off by default |
| STOP_ON_ERROR | on ends a run that is not typed at the first statement that fails; --stop-on-error sets it |
| ECHO | queries writes each statement before it runs; errors writes a failed statement with its failure; -e sets queries |
| QUIET | on writes no OK after a statement that is not a query; -q sets it |
| SAFE_UPDATES | on refuses an UPDATE or DELETE without WHERE before it is sent; --safe-updates sets it |
| FETCH_COUNT | a number of rows; a query's rows are fetched that many at a time by a cursor, rather than all at once |
| VERBOSITY | terse shows a failure's codes and message; default adds its rule and character; verbose also shows the statement's line, with the place marked |
| ROW_COUNT | the rows the last query gave |
| ERROR | true if the last statement failed, else false |
| SQLSTATE | the SQLSTATE of the last statement |
| LAST_ERROR_MESSAGE, LAST_ERROR_SQLSTATE | the message and SQLSTATE of the last statement that failed |
| PROMPT, PROMPT_MORE | the prompts (Prompts) |
| HISTORY_FILE, HISTORY_SIZE | where the history is kept, and how many entries |
| USER, SERVER | the session's user, and the server's product and release |
Conditionals
\if VALUE begins a block, \elif VALUE and \else begin a branch
of it, and \endif ends it. The lines of the first branch whose value
is true run. The other branches are read only for the commands that
begin and end blocks. A value is true, on, yes or 1, or false,
off, no or 0, in any case, and is usually a variable. Blocks may
be nested.
SELECT COUNT(*) AS N FROM STAFF \gset
\if :ERROR
\echo cannot count
\elif :{?N}
\echo :N people
\else
\echo nobody
\endif
3 people
Output formats
\format OPTION VALUE sets how rows are written. \format OPTION
shows one option, and \format alone shows every one.
| Option | Values |
|---|---|
| style | aligned, the default, makes columns as wide as their types; wrapped is aligned with the widest columns narrowed to fit the screen and their values wrapped; unaligned puts values between the separator; csv is RFC 4180's; html is a table; json is an array with one object a row; tabs is tab-separated, after a line of the columns' names |
| expanded | on writes a heading line for each row, then a line for each column with its name and value; auto does so only where a row is wider than the screen; off |
| border | 0 draws no lines between aligned columns, 1 draws them, and 2 adds a frame; 1 by default |
| null | what a null is shown as |
| separator | what is between unaligned values; | by default |
| tuples | on writes the rows alone, without the names of the columns or the count of rows |
| footer | off writes no count of rows after them |
| pager | on, off or always (The pager) |
\x toggles expanded rows, and \x on, \x off or \x auto sets
them. \a toggles aligned and unaligned rows. \t toggles the rows
alone. A statement ended by \G rather than a semicolon has its rows
expanded, for that statement only. The options -A, --csv, --html,
--json, -B, --style, --border, -F, -t, -x and
--auto-expanded set these on the command line.
\format border 2
SELECT ID, NAME FROM STAFF WHERE ID < 3 ORDER BY ID;
+--------------+----------------------+
| ID | NAME |
+--------------+----------------------+
| 1 | Ada |
| 2 | Grace |
+--------------+----------------------+
(2 rows)
SELECT ID, NAME, SALARY FROM STAFF WHERE ID = 3\G
-[ ROW 1 ]-----
ID | 3
NAME | Edsger
SALARY | NULL
(1 row)
\format style json
SELECT ID, NAME, SALARY FROM STAFF ORDER BY ID;
[
{"ID":1,"NAME":"Ada","SALARY":52000.00},
{"ID":2,"NAME":"Grace","SALARY":61000.00},
{"ID":3,"NAME":"Edsger","SALARY":null}
]
\format style csv
SELECT ID, NAME FROM STAFF ORDER BY ID;
ID,NAME
1,Ada
2,Grace
3,Edsger
A value's width is the number of columns a terminal gives it. A combining mark, a format character or a control character takes none. A character of the wide scripts of East Asia takes two, and any other takes one.
The pager
At a terminal, rows taller than the screen are shown through a pager.
It is the program TURBOREL_PAGER names, else PAGER, else less -S if a
less is found in the directories of PATH. An empty value names no
pager. Output is held until it fills the screen, so a short result is
written as it is, and a long one is paged from its first screen while
it streams. \format pager off writes every result directly, and
\format pager always pages every one. Output sent elsewhere by \o
is never paged.
Describing the database
The describe commands read the Information Schema, so they show what any session of the user may see.
| Command | Shows |
|---|---|
\d |
the tables, views and sequences |
\d PATTERN |
each table or view it matches, with its columns and their types, nullability and defaults, then its indexes, constraints, foreign keys, the tables that reference it, and its triggers, or a view's definition |
\dt, \dv |
tables, views |
\di, \ds |
indexes, sequences |
\df |
routines |
\dn |
schemas |
\du |
users, and the roles granted |
\dT, \dD |
user-defined types, domains |
\dx |
the extension modules the server runs |
\dG |
property graphs |
\dp |
privileges on tables |
\l |
the catalog |
A pattern is NAME or SCHEMA.NAME, where * matches any text and
? one character. A part not in double quotes is taken in upper case,
as SQL takes a name. + after a command shows more columns, and S
adds the system's own schemas, as in \dtS+ INFORMATION_SCHEMA.T*. A
section with no rows is left out.
\d staff
Table ANN.STAFF
Column | Type | Nullable | Default
--------+-------------------+----------+---------
ID | INTEGER | NO | NULL
NAME | CHARACTER VARYING | NO | NULL
SALARY | DECIMAL | YES | 0.00
(3 rows)
Indexes
Index | Method | Unique | Columns
-------------------+--------+--------+---------
ANN.STAFF.UNIQUE1 | btree | YES | ID
(1 row)
Constraints
Constraint | Type | Check
----------------+-------------+------------------
STAFF_NOTNULL1 | CHECK | NAME IS NOT NULL
STAFF_PRIMARY1 | PRIMARY KEY | NULL
(2 rows)
\dt
Schema | Name | Type
--------+-------+------------
ANN | STAFF | BASE TABLE
(1 row)
Scripts
A run of -f, -c or piped input runs its statements and commands in
turn. Its exit status tells how it ended. It is 0 if every statement
succeeded, 1 if one failed and the run went on or the run could not
begin, 2 if the connection was lost, and 3 if STOP_ON_ERROR ended it at
a statement that failed.
$ cat check.sql
\set STOP_ON_ERROR on
SELECT COUNT(*) AS N FROM STAFF;
SELECT NOPE FROM STAFF;
\echo never
$ turborel -U ann -f check.sql
N
----------------------
3
(1 row)
turborel: SQLCODE -103 (SQLSTATE 2A000): no table in the FROM clause has a column NOPE [X3.135-1986 §5.7 SR3b] (at character 7)
-1, --single-transaction, runs every statement in one transaction.
It is committed as the run ends if all succeeded, and rolled back if
any failed. Without it, a statement that fails leaves no effect and the
transaction goes on, so nothing is needed to undo a failed statement
alone.
\i FILE runs a file's lines where it stands. \ir FILE finds the
file from the directory of the file being run, so files that include
one another may be moved together. Files are included at most 16 deep.
$ cat main.sql
\ir part.sql
\echo main done
$ cat part.sql
\echo part done
$ turborel -U ann -f main.sql
part done
main done
\o FILE sends results to a file from then on, \o |PROGRAM sends
them to a program's input, and \o alone sends them back to the
output. \g FILE and \g |PROGRAM do the same for one statement's
results. \tee FILE copies all that is written, failures too, to a
file until \notee.
\g runs the statement begun, or runs the last statement again if
none is begun. \gset runs it and stores the columns of its one row as
variables, each named by the prefix given and the column's name. A null
unsets its variable. \gexec runs it, then runs each value of its
result as a statement, row by row.
SELECT 'DELETE FROM STAFF WHERE ID = ' || CAST(ID AS VARCHAR(9)) FROM STAFF WHERE SALARY IS NULL \gexec
SELECT MAX(SALARY) AS TOP, MIN(SALARY) AS BOTTOM FROM STAFF \gset pay_
\echo from :pay_BOTTOM to :pay_TOP
ROLLBACK
OK
from 52000.00 to 61000.00
OK
\watch SECONDS COUNT runs the statement again every SECONDS, 2 by
default, COUNT times or until Ctrl-C or a failure. It writes a line
before each run.
\e edits the statement begun, or the last, in the editor
TURBOREL_EDITOR or EDITOR names, vi by default. What is saved is read
as if typed. \e FILE edits a file the same way. \p writes the
statement begun and \r throws it away.
\delimiter TEXT ends statements with TEXT rather than a semicolon, so
a statement may hold semicolons outside literals, as a routine's body
does; \delimiter ; goes back to the semicolon.
\delimiter //
VALUES 1; //
\delimiter ;
-----
1
(1 row)
Importing and exporting
\import TABLE FILE inserts a file's rows into a table, as --import
does (Importing). Its options are words
after the file, which are csv or tsv, header, delimiter C,
null TEXT, batch N and skip. Where the server takes a bulk copy,
the rows go a batch to a copy, each committed as it ends; with skip
they go through one prepared INSERT, so a bad row is passed over.
\export TABLE FILE, or \export (QUERY) FILE, writes a table's or a
query's rows to a file as they come. It writes CSV, or tab-separated
text with text, and takes the options header, delimiter C and
null TEXT. In CSV a value that would read back as a null, as an empty
string does, is quoted, so \import reads back what \export wrote.
With native it writes the protocol's own frames, which keep each
value's type, for a program to read back with the client library.
turborel> \export (SELECT ID, NAME FROM STAFF WHERE ID < 3) two.csv header
OK, 2 rows exported
turborel> \import STAFF_COPY two.csv header
OK, 2 rows imported
Prompts
PROMPT is written before a statement, turborel> by default.
PROMPT_MORE is written before a line that continues one, turborel-
by default. They may hold these escapes:
| Escape | Shows |
|---|---|
%n |
the session's user |
%c |
the catalog |
%s |
the default schema |
%# |
# for the database's owner, > for anyone else |
%x |
* while a transaction is active |
%R |
> before a statement, - within one |
%S |
the server's product and release |
%% |
a percent sign |
turborel> \set PROMPT '%n@%c.%s%x%# '
ANN@TURBOREL.ANN> INSERT INTO STAFF VALUES (4, 'Barbara', 70000);
OK
ANN@TURBOREL.ANN*> COMMIT;
OK
ANN@TURBOREL.ANN>
The connection
\conninfo names the server, the user, and where they are connected.
\c SERVER USER connects anew. SERVER is HOST:PORT, or - for the
one the command line named. USER defaults to the command line's user.
The old connection ends once the new one is made.
\password USER sets a user's password, typed twice. The client makes
the SCRAM-SHA-256 verifier and sends that, never the password, to a
server that takes one. Another server is sent the password only over
TLS or a local socket, and never over plain TCP.
turborel> \conninfo
Connected to turborel 0.0.0 (alpha) as ANN, at 127.0.0.1:7422.
turborel> \c - BOB
Connected to turborel 0.0.0 (alpha) as BOB, by the connection TURBOREL_1.
Statement help
\h lists the statements, and \h NAME writes one statement's
syntax. Both read the pages of the
SQL Reference Manual the client was
built with.
\h release savepoint
RELEASE SAVEPOINT: destroy a savepoint, keeping the changes since
<release savepoint statement> ::= RELEASE SAVEPOINT <savepoint name>
See doc/sql/statements/release-savepoint.md.
Administration
turborel admin COMMAND administers a server from the shell, over a
session the connection options open. Each command is one or two
statements the server runs. What it shows comes from the Information
Schema, and what it changes goes through the operator's statements.
| Command | Does |
|---|---|
ping |
whether the server answers, and how soon |
status |
the server's state, with its workers, the statements waiting and its connections |
processlist |
the connections the server holds |
kill ID |
ends the connection ID, END CONNECTION |
cancel ID |
cancels the statement the connection ID runs, CANCEL CONNECTION |
variables PATTERN |
the settings whose names match PATTERN, or every one |
password USER |
sets a user's password, as \password does |
create-schema NAME |
creates a schema |
drop-schema NAME |
drops a schema, after asking; --yes drops it unasked, and --cascade with all it holds |
version |
the server's product and release |
tls CERTIFICATE KEY |
names the server's certificate chain and key, SET DATABASE, taken for the connections after |
$ turborel admin -U ann variables server.tls*
SETTING_NAME | SETTING_VALUE
------------------------+---------------
SERVER.TLS | OFF
SERVER.TLS_CERTIFICATE |
SERVER.TLS_CLIENT_CA |
SERVER.TLS_KEY |
(4 rows)
The files tls names are taken for later connections. Files changed
in place are taken again when turboreld is sent SIGHUP. The program
exits with 0 if the command succeeded, 1 if it failed, and 2 if the
connection was lost.