gda-sql - an SQL console based on Libgda

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

       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/envgda-sql

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

       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.

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

       .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"[<baseDN>]].

              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
              .<baseDN> 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[<OPTIONNAME>[<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*FROMfood"personfood.pivot"SELECT*FROMproducts"category"CASEWHENprice<15THEN'low'ELSE'high'END"[AVG]price.pivot"SELECT*FROMsales"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>.

       gda-sql - an SQL console based on Libgda

       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<tokenphrase>
               Requires HTTP clients to authenticate by providing the <tokenphrase> (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-purgeall,list-only lists all the files (which would be removed if the
               command was --data-files-purgeall).

       Consult the Libgda's home page at http://www.gnome-db.org/.

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

Version 5.2.10                                                                                        gda-sql(1)

       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.

       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.

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 <tokenphrase>] [--data-files-list]  [--data-files-purge  <criteria>]  [connection's  spec]
       [connection's spec...]