Provided by: libgda-5.0-bin_5.2.9-2ubuntu1_amd64 
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".
ldap_attributes
This option (see the .option command) defines or list the attributes handled by
LDAP commands; it is only useful if the current connection is an LDAP connection.
Its values must have the following format: <attribute>[,<attribute>...].
For multi valued attributes (such as "objectClass"), it is possible to specify how
multiple values are handled by appending ::csv (values are listed in a CVS syntax),
::* (each row is duplicated with each value of the attribute), ::1 (only the 1st
value of the attribute is shown), ::concat (all the values are made into a string,
separated by newlines) or ::null (a NULL value is used). The default is an error
value.
.ldap_descr
Describes an LDAP entry; this command only works if the current connection is an
LDAP connection. Full syntax is: .ldap_descr <DN> ["all"|"set"|"unset"].
If the set option is passed, then all the set attributes are shown, if the all
option is passed, then all attributes are shown, and if the unset option is passed,
then only attributes which don't have a value are shown. The default is to show
only the set attributes specified by the ldap_attributes option.
ldap_dn
This option (see the .option command) defines how the DN column is handled for LDAP
searched entries; it is useful only if the current connection is an LDAP
connection. Its values must be among: dn (use the full DN), rdn (use only the RDN),
or none (don't use the DN at all).
.ldap_mod
Modifies an LDAP entry's attributes; this command only works if the current
connection is an LDAP connection. Full syntax is: .ldap_mod <DN> <OPERATION>
[<ATTR>[=<VALUE>]] [<ATTR>=<VALUE> ...].
The .<OPERATION> argument specifies which operation must be performed, among
DELETE, REPLACE and ADD.
.ldap_mv
Renames an LDAP entry; this command only works if the current connection is an LDAP
connection. Full syntax is: .ldap_mv <DN> <NEW DN>.
.ldap_search
Searches the LDAP directory for entries; this command only works if the current
connection is an LDAP connection. Full syntax is: .ldap_search <filter>
["base"|"onelevel"|"subtree" [<base DN>]].
Filter must be a valid LDAP filter expression (outer most parenthesis are optional
though), "base", "onelevel" or "subtree" can optionally specify the search scope
(default is subtree), and .<base DN> can be used to specify a different DN to
search from (the default is to use the base DN specified when opening the
connection).
.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>.
.option
Defines options shared among all the connections. Full syntax is: .option [<OPTION
NAME> [<VALUE>]].
If no option name is given, then all the available options and their current values
are shown. If an option name is given without any value, its current value is
shown, and to define the value of an option, give its name and new value.
.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)