Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages


home | help
gda-sql(1)		      LIBGDA Manual Pages		    gda-sql(1)

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

       gda-sql is an SQL console based on the Libgda library.

       It  enables you to type in queries interactively, issue them to be exe-
       cuted 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  exe-
       cuting  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	behav-
       iour of arguments passed	on the line after the '#!'  command  is	 unde-
       fined.  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 spec-

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

       --data-files-purge _criteria_
	       Removes file used to hold information related to	 each  connec-
	       tion  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 con-
	       nection is specified using connection parameters	instead	of us-
	       ing 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_his-
	       tory), 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

	       if set, also dump the data model's title

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

	       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	${prefix}/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/.lo-
       cal/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 "\" charac-
       ter. 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>[:<PASS-
	      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_DEFI-
	      NITION_  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  no-
	      tation) or to the	meta data corresponding	to the _CNC_NAME_ con-

       .close Closes a connection. Full	syntax is: .close <CNC_NAME>.

       .cd    Changes the current  working  directory.	Full  syntax  is:  .cd

	      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.

	      Declares a new foreign key (no constraint	is added to the	 data-
	      base).  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	prece-
	      dence 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 ex-
	      ternal 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 for-
	      eign 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 pro-
	      vided. .lp <provider>  lists  information	 about	the  specified

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

	      For example: ".lc	mydsn PostgreSQL://HOST=moon;DB_NAME=mydb".

	      This option (see the .option command) defines or	list  the  at-
	      tributes handled by LDAP commands; it is only useful if the cur-
	      rent connection is an LDAP connection. Its values	must have  the
	      following	format:	<attribute>[,<attribute>...].

	      For  multi valued	attributes (such as "objectClass"), it is pos-
	      sible to specify how multiple values are	handled	 by  appending
	      ::csv  (values are listed	in a CVS syntax), ::* (each row	is du-
	      plicated 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.

	      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 per-
	      formed, 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	paren-
	      thesis 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 af-
	      ter 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 with-
	      out  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:

	      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 com-
	      mas (forming a valid SQL expression).  In	this case a  row  will
	      be created for each combination of values	of each	of the expres-

	      The _COLUMN_FIELDS_ defines the fields from the  data  set  from
	      which each individual value will yield to	a column in the	analy-
	      sis. Its syntax is similar to the	_ROW_FIELDS_ one. If not spec-
	      ified  (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 expres-
	      sion  is	a  valid  selectable  SQL expression of	the data set's


	      .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: .qe-
	      cho <TEXT>.

       .qa    Lists all	saved query buffers in dictionary.

       .qd    Deletes a	query buffer from the dictionary. Full syntax is:  .qd

       .ql    Loads  query  buffer from	dictionary into	the current query buf-
	      fer.  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_BUF-
	      FER_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

       .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 _TA-
	      BLE_ table, column _COLUMN_ for the row selected by  _ROW_CONDI-
	      TION_.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 re-
       produce 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 rec-
       tify 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 sources)

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

Version	5.2.5			  2018-09-27			    gda-sql(1)


Want to link to this manual page? Use this URL:

home | help