NAME¶
gda-sql - an SQL console based on Libgda
SYNOPSIS¶
gda-sql [--help] [-v] [--version] [-o] [--output-file
<filename>] [-C] [--command] [-f] [--commands-file
<filename>] [-i] [--interactive] [-l] [--list-dsn] [-L]
[--list-providers] [-s] [--http-port
<port>] [-t] [--http-token
<token phrase>] [--data-files-list] [--data-files-purge
<criteria>] [connection's spec] [connection's spec...]
DESCRIPTION¶
gda-sql is an SQL console based on the
Libgda library.
It enables you to type in queries interactively, issue them to be executed by a
connection, and see the query results.
Several connections can be opened at the same time, allowing you to switch the
active connection to any opened connection. When starting, gda-sql opens a
connection for each connection specified on the command line (plus optionally
one corresponding to the
GDA_SQL_CNC environment variable). The prompt
indicates the current connection used when executing commands. See the
.c internal command for an explanation about the syntax to specify a
connection on the command line.
Alternatively, input can be from a file. In addition, it provides a number of
meta-commands and various shell-like features to facilitate writing scripts
and automating a wide variety of tasks.
It is also possible to run the tool as a script using the classic '#!' string at
the start of a script file, with the limitation that behaviour of arguments
passed on the line after the '#!' command is undefined. Example:
#!/bin/path/to/gda-sql
#!/usr/bin/env gda-sql
OPTIONS¶
gda-sql accepts the following options:
- --help
- Show command-line options.
- -o, --output-file <filename>
- Specifies a file to which outputs are redirected.
- -C, --command
- Run only single command (SQL or internal) and exit.
- -f, --commands-file <filename>
- Execute commands from <filename>, then exit
(except if -i specified).
- -i, --interactive
- Keep the console opened after executing a file (used with
the -f option).
- -l, --list-dsn
- List configured data sources and exit.
- -L, --list-providers
- List installed database providers and exit
- -s, --http-port <port>
- Starts the embedded HTTP server on port
<port>
- -t, --http-token <token phrase>
- Requires HTTP clients to authenticate by providing the
<token phrase> (empty phrase by default)
- --data-files-list
- Lists all the files used to hold information related to
each connection (ie. information gathered by the tool about the connection
such as meta data, defined statements,...)
- --data-files-purge <criteria>
- Removes file used to hold information related to each
connection for the criteria passed as argument (note that adding
"list-only" to the criteria, either before or after it
using a comma, will not actually remove the file):
"non-dsn": remove all the files which do not correspond to
a DSN (data source name). These are the files created when a connection is
specified using connection parameters instead of using a DSN
"non-exist-dsn": same as "non-dsn" except
it also removes the files which were for DSN which don't exist anymore
"all": remove all the files, for a complete cleanup
For example: --data-files-purge all,list-only lists all the files
(which would be removed if the command was --data-files-purge all).
ENVIRONMENT¶
gda-sql can be configured through some environment variables:
- GDA_SQL_CNC
- to define a connection to systematically be opened when the
program starts.
- PAGER
- to define a text pager program to use (by default
determined by the system).
- GDA_NO_PAGER
- to specify that no text pager should be used.
- GDA_SQL_EDITOR EDITOR VISUAL
- to define a text editor to be used (variables are examined
in this order).
- GDA_SQL_VIEWER_PNG
- to define a PNG viewer.
- GDA_SQL_VIEWER_PDF
- to define a PDF viewer.
- GDA_SQL_HISTFILE
- to define the history file name to use (by default
.gdasql_history), set to NO_HISTORY to disable history logging.
- GDA_DATA_MODEL_DUMP_ROW_NUMBERS
- if set, the first column of the output will contain row
numbers
- GDA_DATA_MODEL_DUMP_ATTRIBUTES
- if set, also dump the data model's columns' types and
value's attributes
- GDA_DATA_MODEL_DUMP_TITLE
- if set, also dump the data model's title
- GDA_DATA_MODEL_NULL_AS_EMPTY
- if set, replace the 'NULL' string with an empty string for
NULL values
- GDA_DATA_MODEL_DUMP_TRUNCATE
- if set to a numeric value, truncates the output to the
width specified by the value. If the value is -1 then the actual terminal
size (if it can be determined) is used
- gda-sql can be compiled with support for binary
relocatibility.
- This will cause data, plug-ins and configuration files to
be searched relative to the location of the gda-sql executable file.
FILES¶
gda-sql stores data source definitions (DSN) in Libgda defined files
($HOME/.local/share/libgda and /etc/libgda-5.0/config where ${prefix} is
typically /usr).
For each connection defined by a DSN, all the information regarding the
connection (such as the meta data) is stored in a
$HOME/.local/share/libgda/gda-sql-<DSN>.db file.
SQL commands¶
You can run any SQL understood by the database engine of the current connection.
Additionally SQL statement can contain variables expressed as
##<name>::<type> where
<name> is the
variable's name and
<type> is its declared type (which can be
"int", "string", "boolean", "time",
"date", "timestamp" (and other types defined by GLib's
syntax).
Use the
.set internal command to set variable's values.
Internal commands¶
In addition to SQL commands, gda-sql supports internal commands which differ
from SQL commands because they start with the "." or "\"
character. These commands are:
- .?
- Lists all internal commands
- .bind
- Bind two or more connections into a single new one
(allowing SQL commands to be executed across multiple connections).
.bind <CNC_NAME> <CNC_NAME1> <CNC_NAME2>
[<CNC_NAME> ...] creates a new connection named
<CNC_NAME> which binds the tables of the
<CNC_NAME1>, <CNC_NAME2> and any other
connection specified.
- .c
- Opens a connection or sets the current connection. Username
and password can pe specified using the
<USERNAME>[:<PASSWORD>]@<DSN_NAME> or
<USERNAME>[:<PASSWORD>]@<CNC_DEFINITION> syntax,
and if a username or a password is required but not specified, it will ba
asked interactively.
.c <CNC_NAME> <DSN_NAME> opens a connection internally
known as <CNC_NAME>, using the specified DSN.
.c <CNC_NAME> <CNC_DEFINITION> opens a connection
internally known as <CNC_NAME>, using a connection specified
by <CNC_DEFINITION> which is similar to the
<DSN_DEFINITION> parameter of the .lc command.
.c <CNC_NAME> sets the current connection to the connection
known as <CNC_NAME>.
.c ~ or .c ~<CNC_NAME> set the current connection to
the meta data corresponding to the current connection (for the first
notation) or to the meta data corresponding to the <CNC_NAME>
connection.
- .close
- Closes a connection. Full syntax is: .close
<CNC_NAME>.
- .cd
- Changes the current working directory. Full syntax is:
.cd <DIR_NAME>.
- .copyright
- Displays copyright information.
- .d
- Lists all database objects if no argument is provided.
.d <OBJ_NAME> gives details about the specified object and
.d <SCHEMA>.* lists all objects in specified schema.
- .dn
- Lists all schemas if no argument is provided. .d
<SCHEMA_NAME> lists specified schema.
- .dt
- Lists all tables if no argument is provided. .d
<TABLE_NAME> lists specified table.
- .dv
- Lists all views if no argument is provided. .d
<VIEW_NAME> lists specified view.
- .fkdeclare
- Declares a new foreign key (no constraint is added to the
database). The meta data is modified to take into account a foreign key
constraint. The foreign key specification is <fkname>
<tableA>(<colA>,...) <tableB>(<colB>,...)
where <fkname> is the name given to the foreign key
constraint and <tableA> references <tableB>
using the columns mentionned between the parenthesis. Note that the (
<fkname>, <tableA>, <tableB>)
triplet uniquely identifies a declared foreign key (declaring a new
foreign key with the same triplet will remove any previously declared
one). Note: any actual foreign key constraint will always have
precedence over any declared foreign key.
- .fkundeclare
- Un-declares a foreign key (does the opposite of
.fkdeclare).
- .e
- Edits the query buffer with external editor, if no argument
is provided. .e <FILE_NAME> edits the specified file name.
The external editor can be specified using environment variables.
- .echo
- Sends output to stdout, full command is: .echo
[<TEXT>].
- .export
- Exports internal parameter or table's value to the FILE
file. Internal parameters are named values used when SQL statement
containing variables are executed.
.export <NAME> <FILE_NAME> exports the contents of the
<NAME> parameter to the specified file.
.export <TABLE> <COLUMN> <ROW_CONDITION>
<FILE_NAME> exports the value of the <TABLE> table,
column <COLUMN> for the row selected by
<ROW_CONDITION> to the specified file. This is most useful to
export BLOBs.
- .g
- Executes the contents of the query buffer, if no parameter
is provided. .g <QUERY_BUFFER_NAME> Executes the contents of
the specified query buffer. A named query buffer is created using the
.qs command.
- .graph
- Creates a graph of tables showing their relations (based on
foreign key constraints). If no argument is provided, the graph lists all
tables. .graph <TABLE_NAME> [<TABLE_NAME>...] creates a
graph listing the specified tables.
The generated graph is created as the "gdaph.dot" file. If the
GDA_SQL_VIEWER_PNG or GDA_SQL_VIEWER_PDF environment
variables are set and if the "dot" program (from GraphViz) is
found, then the graph is displayed (if a display is available).
- .H
- Set output format. Full syntax is: .H
[HTML|XML|CSV|DEFAULT].
- .http
- Starts/stops the embedded HTTP server. Full syntax is
.http [<port> [<authentication_token>]], where
<authentication_token> is an optional token phrase which HTTP
clients are required to send to authenticate.
- .i
- Executes commands from file the specified file: .i
<FILE_NAME>.
- .l
- Lists all data sources if no argument is provided. .l
<DSN> lists information about the specified DSN.
- .lp
- Lists all available database providers if no argument is
provided. .lp <provider> lists information about the
specified provider.
- .lc
- Declares a DSN. Full syntax is: .lc <DSN_NAME>
<DSN_DEFINITION> [<DESCRIPTION>]. The
<DSN_DEFINITION> format is:
<provider>://[<username>[:<password>]@]<connection_params>
where <connection_params> is a semi-colon (";")
separated list of <key>=<value> pairs where <key>
is defined when using .lp <provider> (if <value>
contains non alphanumeric characters, they should be represented as
specified by the RFC 1738).
If a DSN with a similar name already exists, it is first removed.
For example: ".lc mydsn PostgreSQL://HOST=moon;DB_NAME=mydb".
- .lr
- Removes a DSN declaration. Full syntax is: .lc
<DSN_NAME>.
- .meta
- Updates the current connection's meta data (use this
command after having modified the database's schema).
- .o
- Sends output to a file or |pipe. Full syntax is: .o
<FILE_NAME> or .o |<COMMAND>.
- .pivot
- Performs data summarization on a data set. Full syntax is:
.pivot <SELECT> <ROW_FIELDS> [<COLUMN_FIELDS>
[<DATA_FIELDS> [...]]].
The <SELECT> defines the data set to perform summarization on.
The <ROW_FIELDS> defines the fields from the data set from
which each individual value will yield to a row in the analysis (it can be
any valid selectable SQL expression on the data set's fields); multiple
expressions can be provided, separated by commas (forming a valid SQL
expression). In this case a row will be created for each combination of
values of each of the expression.
The <COLUMN_FIELDS> defines the fields from the data set from
which each individual value will yield to a column in the analysis. Its
syntax is similar to the <ROW_FIELDS> one. If not specified
(or if specified as a single dash ("-") caracter), then only one
column will be created. Note that, if the <DATA_FIELDS>
argument is specified each column created from the
<COLUMN_FIELDS> will in fact lead to the creation of as many
<DATA_FIELDS> arguments provided.
The <DATA_FIELDS> arguments are entirely optional and indicates
the way data summarization is done for each pair of (row,column) values
(the default is to count occurrences). The syntax for each
<DATA_FIELDS> argument is:
[aggregate]<SQL_expression>, where the aggregate part is
optional and, if present must be among [SUM], [COUNT], [AVG], [MIN] or
[MAX], and the SQL expression is a valid selectable SQL expression of the
data set's fields.
Examples:
.pivot "SELECT * FROM food" person food
.pivot "SELECT * FROM products" category "CASE WHEN price
< 15 THEN 'low' ELSE 'high' END" [AVG]price
.pivot "SELECT * FROM sales" category,product -
[AVG]quantity
- .q
- Quits the application.
- .qecho
- Sends output to the output stream (stdout). Full syntax is:
.qecho <TEXT>.
- .qa
- Lists all saved query buffers in dictionary.
- .qd
- Deletes a query buffer from the dictionary. Full syntax is:
.qd <QUERY_BUFFER_NAME>
- .ql
- Loads query buffer from dictionary into the current query
buffer. Full syntax is: .ql <QUERY_BUFFER_NAME>.
- .qp
- Shows the contents of the current query buffer.
- .qr
- Resets the query buffer to empty if no argument is
provided. .qr <FILE _NAME> loads the specified file into the
query buffer.
- .qs
- Saves query buffer to dictionary, full syntax is .qs
<QUERY_BUFFER_NAME>. This creates a new query buffer with the
specified name in the dictionary, containing the current query
buffer.
- .qw
- Writes the query buffer to the specified file, full syntax
is .qw <FILE_NAME>.
- .s
- Show commands history. .s <FILE_NAME> saves
command history to specified file.
- .set
- Sets, shows or lists internal parameters.
.set lists all the defined internal parameters.
.set <NAME> <VALUE> (re)defines the internal parameter
named <NAME> to the specified value (which can be the
_null_ literal to set it to NULL).
.set <NAME> shows the contents of the internal parameter named
<NAME>.
- .setex
- Set internal parameter as the contents of the FILE file or
from an existing table's value.
.setex <NAME> <FILE_NAME> (re)defines the the internal
parameter named <NAME> with the contents of the specified
file name.
.setex <NAME> <TABLE> <COLUMN>
<ROW_CONDITION> (re)defines the the internal parameter named
<NAME> with the value of the <TABLE> table,
column <COLUMN> for the row selected by
<ROW_CONDITION>.This is most useful to export BLOBs.
- .unset
- Unset (delete) internal parameter.
.unset unsets all the internal parameters.
.unset <NAME> unsets the internal parameter named
<NAME>.
SUGGESTIONS AND BUG REPORTS¶
Any bugs found should be reported to the online bug-tracking system available on
the web at
http://bugzilla.gnome.org/. Before reporting bugs, please check to
see if the bug has already been reported.
When reporting bugs, it is important to include a reliable way to reproduce the
bug, version number of gda-sql, OS name and version, and any relevant hardware
specs. If a bug is causing a crash, it is very useful if a stack trace can be
provided. And of course, patches to rectify the bug are even better.
OTHER INFO¶
Consult the Libgda's home page at
http://www.gnome-db.org/.
AUTHORS¶
Vivien Malerba (for Libgda's authors, please consult the AUTORS file within the
Libgda's sources)
SEE ALSO¶
psql(1),
mysql(1),
sqlite3(1)