gda-sql-5.0 man page on Mageia

Man page or keyword search:  
man Server   17783 pages
apropos Keyword Search (all sections)
Output format
Mageia logo
[printable version]

gda-sql(1)		      LIBGDA Manual Pages		    gda-sql(1)

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

       #!/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 spec‐
	       ified).

       -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  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
	       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_his‐
	       tory), 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 "\" 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‐
	      WORD>]@<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_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
	      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	 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.

       .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 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 "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 pro‐
	      vided. .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 pos‐
	      sible 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 per‐
	      formed, 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 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
	      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 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:
	      .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 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‐
	      sion.

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

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 rec‐
       tify 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)

Version 5.1.2			  2013-10-19			    gda-sql(1)
[top]

List of man pages available for Mageia

Copyright (c) for man pages and the logo by the respective OS vendor.

For those who want to learn more, the polarhome community provides shell access and support.

[legal] [privacy] [GNU] [policy] [cookies] [netiquette] [sponsors] [FAQ]
Tweet
Polarhome, production since 1999.
Member of Polarhome portal.
Based on Fawad Halim's script.
....................................................................
Vote for polarhome
Free Shell Accounts :: the biggest list on the net