1gda-sql(1)                    LIBGDA Manual Pages                   gda-sql(1)
2
3
4

NAME

6       gda-sql - an SQL console based on Libgda
7
8

SYNOPSIS

10       gda-sql  [--help] [-v] [--version] [-o] [--output-file <filename>] [-C]
11       [--command] [-f] [--commands-file <filename>] [-i] [--interactive] [-l]
12       [--list-dsn]  [-L]  [--list-providers]  [-s]  [--http-port <port>] [-t]
13       [--http-token <token phrase>]  [--data-files-list]  [--data-files-purge
14       <criteria>] [connection's spec] [connection's spec...]
15
16

DESCRIPTION

18       gda-sql is an SQL console based on the Libgda library.
19
20       It  enables you to type in queries interactively, issue them to be exe‐
21       cuted by a connection, and see the query results.
22
23       Several connections can be opened at the same  time,  allowing  you  to
24       switch  the  active connection to any opened connection. When starting,
25       gda-sql opens a connection for each connection specified on the command
26       line  (plus optionally one corresponding to the GDA_SQL_CNC environment
27       variable). The prompt indicates the current connection used  when  exe‐
28       cuting  commands.  See the .c internal command for an explanation about
29       the syntax to specify a connection on the command line.
30
31
32       Alternatively, input can be from a file.  In addition,  it  provides  a
33       number  of  meta-commands and various shell-like features to facilitate
34       writing scripts and automating a wide variety of tasks.
35
36
37       It is also possible to run the tool as a script using the classic  '#!'
38       string  at  the start of a script file, with the limitation that behav‐
39       iour of arguments passed on the line after the '#!'  command  is  unde‐
40       fined.  Example:
41
42       #!/bin/path/to/gda-sql
43
44       #!/usr/bin/env gda-sql
45
46
47

OPTIONS

49       gda-sql accepts the following options:
50
51       --help  Show command-line options.
52
53       -o, --output-file <filename>
54               Specifies a file to which outputs are redirected.
55
56       -C, --command
57               Run only single command (SQL or internal) and exit.
58
59       -f, --commands-file <filename>
60               Execute commands from <filename>, then exit (except if -i spec‐
61               ified).
62
63       -i, --interactive
64               Keep the console opened after executing a file (used  with  the
65               -f option).
66
67       -l, --list-dsn
68               List configured data sources and exit.
69
70       -L, --list-providers
71               List installed database providers and exit
72
73       -s, --http-port <port>
74               Starts the embedded HTTP server on port <port>
75
76       -t, --http-token <token phrase>
77               Requires  HTTP  clients to authenticate by providing the <token
78               phrase> (empty phrase by default)
79
80       --data-files-list
81               Lists all the files used to hold information  related  to  each
82               connection  (ie.   information  gathered  by the tool about the
83               connection such as meta data, defined statements,...)
84
85       --data-files-purge <criteria>
86               Removes file used to hold information related to  each  connec‐
87               tion  for  the  criteria  passed  as argument (note that adding
88               "list-only" to the criteria, either before or after it using  a
89               comma, will not actually remove the file):
90
91               "non-dsn":  remove  all  the files which do not correspond to a
92               DSN (data source name). These are the files created when a con‐
93               nection is specified using connection parameters instead of us‐
94               ing a DSN
95
96               "non-exist-dsn": same as "non-dsn" except it also  removes  the
97               files which were for DSN which don't exist anymore
98
99               "all": remove all the files, for a complete cleanup
100
101               For  example:  --data-files-purge  all,list-only  lists all the
102               files  (which   would   be   removed   if   the   command   was
103               --data-files-purge all).
104
105

ENVIRONMENT

107       gda-sql can be configured through some environment variables:
108
109       GDA_SQL_CNC
110               to  define  a  connection  to systematically be opened when the
111               program starts.
112
113       PAGER   to define a text pager program to use (by default determined by
114               the system).
115
116       GDA_NO_PAGER
117               to specify that no text pager should be used.
118
119       GDA_SQL_EDITOR EDITOR VISUAL
120               to  define  a text editor to be used (variables are examined in
121               this order).
122
123       GDA_SQL_VIEWER_PNG
124               to define a PNG viewer.
125
126       GDA_SQL_VIEWER_PDF
127               to define a PDF viewer.
128
129       GDA_SQL_HISTFILE
130               to define the history file name to use (by default .gdasql_his‐
131               tory), set to NO_HISTORY to disable history logging.
132
133       GDA_DATA_MODEL_DUMP_ROW_NUMBERS
134               if set, the first column of the output will contain row numbers
135
136       GDA_DATA_MODEL_DUMP_ATTRIBUTES
137               if  set,  also dump the data model's columns' types and value's
138               attributes
139
140       GDA_DATA_MODEL_DUMP_TITLE
141               if set, also dump the data model's title
142
143       GDA_DATA_MODEL_NULL_AS_EMPTY
144               if set, replace the 'NULL' string with an empty string for NULL
145               values
146
147       GDA_DATA_MODEL_DUMP_TRUNCATE
148               if  set  to  a numeric value, truncates the output to the width
149               specified by the value.  If the value is  -1  then  the  actual
150               terminal size (if it can be determined) is used
151
152
153       gda-sql can be compiled with support for binary relocatibility.
154       This  will  cause data, plug-ins and configuration files to be searched
155       relative to the location of the gda-sql executable file.
156
157
158

FILES

160       gda-sql stores data source definitions (DSN) in  Libgda  defined  files
161       ($HOME/.local/share/libgda  and  /etc/libgda-5.0/config where ${prefix}
162       is typically /usr).
163
164       For each connection defined by a DSN, all the information regarding the
165       connection   (such  as  the  meta  data)  is  stored  in  a  $HOME/.lo‐
166       cal/share/libgda/gda-sql-<DSN>.db file.
167
168
169

SQL commands

171       You can run any SQL understood by the database engine  of  the  current
172       connection.  Additionally SQL statement can contain variables expressed
173       as ##<name>::<type> where <name> is the variable's name and  <type>  is
174       its  declared  type  (which  can be "int", "string", "boolean", "time",
175       "date", "timestamp" (and other types defined by GLib's syntax).
176
177       Use the .set internal command to set variable's values.
178
179

Internal commands

181       In addition to SQL commands, gda-sql supports internal  commands  which
182       differ from SQL commands because they start with the "." or "\" charac‐
183       ter. These commands are:
184
185
186       .?     Lists all internal commands
187
188       .bind  Bind two or more connections into a single new one (allowing SQL
189              commands  to  be  executed  across  multiple connections). .bind
190              <CNC_NAME> <CNC_NAME1> <CNC_NAME2> [<CNC_NAME>  ...]  creates  a
191              new  connection  named  <CNC_NAME> which binds the tables of the
192              <CNC_NAME1>, <CNC_NAME2> and any other connection specified.
193
194       .c     Opens a connection or sets the current connection. Username  and
195              password   can   pe   specified   using  the  <USERNAME>[:<PASS‐
196              WORD>]@<DSN_NAME>  or   <USERNAME>[:<PASSWORD>]@<CNC_DEFINITION>
197              syntax,  and  if  a  username  or a password is required but not
198              specified, it will ba asked interactively.
199
200              .c <CNC_NAME> <DSN_NAME> opens a connection internally known  as
201              <CNC_NAME>, using the specified DSN.
202
203              .c  <CNC_NAME>  <CNC_DEFINITION>   opens a connection internally
204              known as <CNC_NAME>, using a connection specified by  <CNC_DEFI‐
205              NITION>  which  is  similar to the <DSN_DEFINITION> parameter of
206              the .lc command.
207
208              .c <CNC_NAME> sets the  current  connection  to  the  connection
209              known as <CNC_NAME>.
210
211              .c  ~  or  .c ~<CNC_NAME> set the current connection to the meta
212              data corresponding to the current connection (for the first  no‐
213              tation) or to the meta data corresponding to the <CNC_NAME> con‐
214              nection.
215
216       .close Closes a connection. Full syntax is: .close <CNC_NAME>.
217
218       .cd    Changes the current  working  directory.  Full  syntax  is:  .cd
219              <DIR_NAME>.
220
221       .copyright
222              Displays copyright information.
223
224       .d     Lists  all  database  objects  if  no  argument  is provided. .d
225              <OBJ_NAME> gives details  about  the  specified  object  and  .d
226              <SCHEMA>.* lists all objects in specified schema.
227
228       .dn    Lists  all  schemas if no argument is provided. .d <SCHEMA_NAME>
229              lists specified schema.
230
231       .dt    Lists all tables if no argument  is  provided.  .d  <TABLE_NAME>
232              lists specified table.
233
234       .dv    Lists all views if no argument is provided. .d <VIEW_NAME> lists
235              specified view.
236
237       .fkdeclare
238              Declares a new foreign key (no constraint is added to the  data‐
239              base).  The meta data is modified to take into account a foreign
240              key constraint.  The  foreign  key  specification  is   <fkname>
241              <tableA>(<colA>,...)  <tableB>(<colB>,...) where <fkname> is the
242              name given to the foreign key constraint and <tableA> references
243              <tableB>  using  the columns mentionned between the parenthesis.
244              Note that the (<fkname>, <tableA>,  <tableB>)  triplet  uniquely
245              identifies  a  declared foreign key (declaring a new foreign key
246              with the same triplet will remove any previously declared  one).
247              Note:  any actual foreign key constraint will always have prece‐
248              dence over any declared foreign key.
249
250       .fkundeclare
251              Un-declares a foreign key (does the opposite of .fkdeclare).
252
253       .e     Edits the query buffer with external editor, if no  argument  is
254              provided.  .e <FILE_NAME> edits the specified file name. The ex‐
255              ternal editor can be specified using environment variables.
256
257       .echo  Sends output to stdout, full command is: .echo [<TEXT>].
258
259       .export
260              Exports internal parameter or table's value to  the  FILE  file.
261              Internal  parameters  are  named  values used when SQL statement
262              containing variables are executed.
263
264              .export <NAME> <FILE_NAME> exports the contents  of  the  <NAME>
265              parameter to the specified file.
266
267              .export <TABLE> <COLUMN> <ROW_CONDITION> <FILE_NAME> exports the
268              value of the <TABLE> table, column <COLUMN> for the row selected
269              by <ROW_CONDITION> to the specified file. This is most useful to
270              export BLOBs.
271
272       .g     Executes the contents of the query buffer, if  no  parameter  is
273              provided.  .g  <QUERY_BUFFER_NAME>  Executes the contents of the
274              specified query buffer. A named query buffer  is  created  using
275              the .qs command.
276
277       .graph Creates a graph of tables showing their relations (based on for‐
278              eign key constraints). If no argument  is  provided,  the  graph
279              lists  all tables. .graph <TABLE_NAME> [<TABLE_NAME>...] creates
280              a graph listing the specified tables.
281
282              The generated graph is created as the "gdaph.dot" file.  If  the
283              GDA_SQL_VIEWER_PNG  or  GDA_SQL_VIEWER_PDF environment variables
284              are set and if the "dot" program (from GraphViz) is found,  then
285              the graph is displayed (if a display is available).
286
287       .H     Set output format. Full syntax is: .H [HTML|XML|CSV|DEFAULT].
288
289       .http  Starts/stops  the  embedded  HTTP  server.  Full syntax is .http
290              [<port> [<authentication_token>]], where  <authentication_token>
291              is  an  optional token phrase which HTTP clients are required to
292              send to authenticate.
293
294       .i     Executes commands from file the specified file: .i <FILE_NAME>.
295
296       .l     Lists all data sources if no  argument  is  provided.  .l  <DSN>
297              lists information about the specified DSN.
298
299       .lp    Lists  all  available  database providers if no argument is pro‐
300              vided. .lp <provider>  lists  information  about  the  specified
301              provider.
302
303       .lc    Declares  a DSN. Full syntax is: .lc <DSN_NAME> <DSN_DEFINITION>
304              [<DESCRIPTION>].     The     <DSN_DEFINITION>     format     is:
305              <provider>://[<username>[:<password>]@]<connection_params> where
306              <connection_params> is a  semi-colon  (";")  separated  list  of
307              <key>=<value>  pairs  where  <key>  is  defined  when  using .lp
308              <provider> (if <value>  contains  non  alphanumeric  characters,
309              they should be represented as specified by the RFC 1738).
310
311              If  a  DSN  with  a similar name already exists, it is first re‐
312              moved.
313
314              For example: ".lc mydsn PostgreSQL://HOST=moon;DB_NAME=mydb".
315
316       ldap_attributes
317              This option (see the .option command) defines or  list  the  at‐
318              tributes handled by LDAP commands; it is only useful if the cur‐
319              rent connection is an LDAP connection. Its values must have  the
320              following format: <attribute>[,<attribute>...].
321
322              For  multi valued attributes (such as "objectClass"), it is pos‐
323              sible to specify how multiple values are  handled  by  appending
324              ::csv  (values are listed in a CVS syntax), ::* (each row is du‐
325              plicated with each value of the attribute), ::1  (only  the  1st
326              value  of  the attribute is shown), ::concat (all the values are
327              made into a string, separated by newlines)  or  ::null  (a  NULL
328              value is used). The default is an error value.
329
330       .ldap_descr
331              Describes  an LDAP entry; this command only works if the current
332              connection is an LDAP connection.  Full syntax  is:  .ldap_descr
333              <DN> ["all"|"set"|"unset"].
334
335              If  the  set  option  is passed, then all the set attributes are
336              shown, if the all option is  passed,  then  all  attributes  are
337              shown,  and  if the unset option is passed, then only attributes
338              which don't have a value are shown. The default is to show  only
339              the set attributes specified by the ldap_attributes option.
340
341       ldap_dn
342              This  option (see the .option command) defines how the DN column
343              is handled for LDAP searched entries; it is useful only  if  the
344              current  connection  is  an  LDAP connection. Its values must be
345              among: dn (use the full DN), rdn (use only  the  RDN),  or  none
346              (don't use the DN at all).
347
348       .ldap_mod
349              Modifies  an LDAP entry's attributes; this command only works if
350              the current connection is an LDAP connection.  Full  syntax  is:
351              .ldap_mod  <DN>  <OPERATION>  [<ATTR>[=<VALUE>]] [<ATTR>=<VALUE>
352              ...].
353
354              The .<OPERATION> argument specifies which operation must be per‐
355              formed, among DELETE, REPLACE and ADD.
356
357       .ldap_mv
358              Renames  an  LDAP  entry; this command only works if the current
359              connection is an LDAP connection. Full syntax is: .ldap_mv  <DN>
360              <NEW DN>.
361
362       .ldap_search
363              Searches the LDAP directory for entries; this command only works
364              if the current connection is an LDAP connection. Full syntax is:
365              .ldap_search <filter> ["base"|"onelevel"|"subtree" [<base DN>]].
366
367              Filter must be a valid LDAP filter expression (outer most paren‐
368              thesis are optional though), "base", "onelevel" or "subtree" can
369              optionally  specify  the  search scope (default is subtree), and
370              .<base DN> can be used to specify a different DN to search  from
371              (the  default  is  to use the base DN specified when opening the
372              connection).
373
374       .lr    Removes a DSN declaration. Full syntax is: .lc <DSN_NAME>.
375
376       .meta  Updates the current connection's meta data (use this command af‐
377              ter having modified the database's schema).
378
379       .o     Sends  output to a file or |pipe. Full syntax is: .o <FILE_NAME>
380              or .o |<COMMAND>.
381
382       .option
383              Defines options shared among all the  connections.  Full  syntax
384              is: .option [<OPTION NAME> [<VALUE>]].
385
386              If  no  option name is given, then all the available options and
387              their current values are shown. If an option name is given with‐
388              out  any  value,  its  current value is shown, and to define the
389              value of an option, give its name and new value.
390
391       .pivot Performs data summarization on  a  data  set.  Full  syntax  is:
392              .pivot  <SELECT>  <ROW_FIELDS>  [<COLUMN_FIELDS>  [<DATA_FIELDS>
393              [...]]].
394
395              The <SELECT> defines the data set to perform summarization on.
396
397              The <ROW_FIELDS> defines the fields from the data set from which
398              each  individual  value  will yield to a row in the analysis (it
399              can be any valid selectable SQL expression  on  the  data  set's
400              fields); multiple expressions can be provided, separated by com‐
401              mas (forming a valid SQL expression).  In this case a  row  will
402              be created for each combination of values of each of the expres‐
403              sion.
404
405              The <COLUMN_FIELDS> defines the fields from the  data  set  from
406              which each individual value will yield to a column in the analy‐
407              sis. Its syntax is similar to the <ROW_FIELDS> one. If not spec‐
408              ified  (or  if  specified as a single dash ("-") caracter), then
409              only  one  column  will  be  created.    Note   that,   if   the
410              <DATA_FIELDS> argument is specified each column created from the
411              <COLUMN_FIELDS> will in fact lead to the  creation  of  as  many
412              <DATA_FIELDS> arguments provided.
413
414              The  <DATA_FIELDS> arguments are entirely optional and indicates
415              the way data summarization is done for each pair of (row,column)
416              values  (the  default  is  to count occurrences). The syntax for
417              each  <DATA_FIELDS>  argument  is:  [aggregate]<SQL_expression>,
418              where  the  aggregate  part  is optional and, if present must be
419              among [SUM], [COUNT], [AVG], [MIN] or [MAX], and the SQL expres‐
420              sion  is  a  valid  selectable  SQL expression of the data set's
421              fields.
422
423              Examples:
424
425              .pivot "SELECT * FROM food" person food
426
427              .pivot "SELECT * FROM products" category "CASE WHEN price  <  15
428              THEN 'low' ELSE 'high' END" [AVG]price
429
430              .pivot "SELECT * FROM sales" category,product - [AVG]quantity
431
432
433
434       .q     Quits the application.
435
436       .qecho Sends output to the output stream (stdout). Full syntax is: .qe‐
437              cho <TEXT>.
438
439       .qa    Lists all saved query buffers in dictionary.
440
441       .qd    Deletes a query buffer from the dictionary. Full syntax is:  .qd
442              <QUERY_BUFFER_NAME>
443
444       .ql    Loads  query  buffer from dictionary into the current query buf‐
445              fer.  Full syntax is: .ql <QUERY_BUFFER_NAME>.
446
447       .qp    Shows the contents of the current query buffer.
448
449       .qr    Resets the query buffer to empty if no argument is provided. .qr
450              <FILE _NAME> loads the specified file into the query buffer.
451
452       .qs    Saves query buffer to dictionary, full syntax is .qs <QUERY_BUF‐
453              FER_NAME>. This creates a new query buffer  with  the  specified
454              name in the dictionary, containing the current query buffer.
455
456       .qw    Writes  the  query  buffer to the specified file, full syntax is
457              .qw <FILE_NAME>.
458
459       .s     Show commands history. .s <FILE_NAME> saves command  history  to
460              specified file.
461
462       .set   Sets, shows or lists internal parameters.
463
464              .set lists all the defined internal parameters.
465
466              .set  <NAME>  <VALUE>  (re)defines  the internal parameter named
467              <NAME> to the specified value (which can be the  _null_  literal
468              to set it to NULL).
469
470              .set  <NAME>  shows the contents of the internal parameter named
471              <NAME>.
472
473       .setex Set internal parameter as the contents of the FILE file or  from
474              an existing table's value.
475
476              .setex <NAME> <FILE_NAME> (re)defines the the internal parameter
477              named <NAME> with the contents of the specified file name.
478
479              .setex <NAME> <TABLE> <COLUMN> <ROW_CONDITION>  (re)defines  the
480              the  internal  parameter named <NAME> with the value of the <TA‐
481              BLE> table, column <COLUMN> for the row selected by  <ROW_CONDI‐
482              TION>.This is most useful to export BLOBs.
483
484       .unset Unset (delete) internal parameter.
485
486              .unset unsets all the internal parameters.
487
488              .unset <NAME> unsets the internal parameter named <NAME>.
489

SUGGESTIONS AND BUG REPORTS

491       Any  bugs  found  should  be reported to the online bug-tracking system
492       available on the web at  http://bugzilla.gnome.org/.  Before  reporting
493       bugs, please check to see if the bug has already been reported.
494
495       When  reporting  bugs, it is important to include a reliable way to re‐
496       produce the bug, version number of gda-sql, OS name  and  version,  and
497       any  relevant  hardware  specs. If a bug is causing a crash, it is very
498       useful if a stack trace can be provided. And of course, patches to rec‐
499       tify the bug are even better.
500
501
502

OTHER INFO

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

AUTHORS

508       Vivien  Malerba  (for  Libgda's authors, please consult the AUTORS file
509       within the Libgda's sources)
510
511
512

SEE ALSO

514       psql(1), mysql(1), sqlite3(1)
515
516
517
518Version 5.2.10                                                      gda-sql(1)
Impressum