Provided by: libgda-5.0-bin_5.2.9-2ubuntu1_amd64 bug


       gda-sql - an SQL console based on Libgda


       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


       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:


       #!/usr/bin/env gda-sql


       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)

               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

       --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).


       gda-sql can be configured through some environment variables:

               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).

               to specify that no text pager should be used.

               to define a text editor to be used (variables are examined in this order).

               to define a PNG viewer.

               to define a PDF viewer.

               to define the history file name  to  use  (by  default  .gdasql_history),  set  to
               NO_HISTORY to disable history logging.

               if set, the first column of the output will contain row numbers

               if set, also dump the data model's columns' types and value's attributes

               if set, also dump the data model's title

               if set, replace the 'NULL' string with an empty string for NULL values

               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.


       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

       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>.

              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

       .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.

              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.

              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>].

              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 "" 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".

              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

              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.

              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).

              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.

              Renames an LDAP entry; this command only works if the current connection is an LDAP
              connection. Full syntax is: .ldap_mv <DN> <NEW DN>.

              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

       .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>.

              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.


              .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

              .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>.


       Any bugs found should be reported to the online bug-tracking system available on  the  web
       at  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.


       Consult the Libgda's home page at


       Vivien  Malerba  (for Libgda's authors, please consult the AUTORS file within the Libgda's


       psql(1), mysql(1), sqlite3(1)