1gda-sql(1) LIBGDA Manual Pages gda-sql(1)
2
6 gda-sql - an SQL console based on Libgda
7
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
18 gda-sql is an SQL console based on the Libgda library.
19 It enables you to type in queries interactively, issue them to be exe‐
21 cuted by a connection, and see the query results.
22 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 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 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 #!/bin/path/to/gda-sql
43 #!/usr/bin/env gda-sql
45
49 gda-sql accepts the following options:
50 --help Show command-line options.
52 -o, --output-file <filename>
54 Specifies a file to which outputs are redirected.
55 -C, --command
57 Run only single command (SQL or internal) and exit.
58 -f, --commands-file <filename>
60 Execute commands from <filename>, then exit (except if -i spec‐
61 ified).
62 -i, --interactive
64 Keep the console opened after executing a file (used with the
65 -f option).
66 -l, --list-dsn
68 List configured data sources and exit.
69 -L, --list-providers
71 List installed database providers and exit
72 -s, --http-port <port>
74 Starts the embedded HTTP server on port <port>
75 -t, --http-token <token phrase>
77 Requires HTTP clients to authenticate by providing the <token
78 phrase> (empty phrase by default)
79 --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 --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 "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 "non-exist-dsn": same as "non-dsn" except it also removes the
97 files which were for DSN which don't exist anymore
98 "all": remove all the files, for a complete cleanup
100 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
107 gda-sql can be configured through some environment variables:
108 GDA_SQL_CNC
110 to define a connection to systematically be opened when the
111 program starts.
112 PAGER to define a text pager program to use (by default determined by
114 the system).
115 GDA_NO_PAGER
117 to specify that no text pager should be used.
118 GDA_SQL_EDITOR EDITOR VISUAL
120 to define a text editor to be used (variables are examined in
121 this order).
122 GDA_SQL_VIEWER_PNG
124 to define a PNG viewer.
125 GDA_SQL_VIEWER_PDF
127 to define a PDF viewer.
128 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 GDA_DATA_MODEL_DUMP_ROW_NUMBERS
134 if set, the first column of the output will contain row numbers
135 GDA_DATA_MODEL_DUMP_ATTRIBUTES
137 if set, also dump the data model's columns' types and value's
138 attributes
139 GDA_DATA_MODEL_DUMP_TITLE
141 if set, also dump the data model's title
142 GDA_DATA_MODEL_NULL_AS_EMPTY
144 if set, replace the 'NULL' string with an empty string for NULL
145 values
146 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 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
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 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
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 Use the .set internal command to set variable's values.
178
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 .? Lists all internal commands
187 .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 .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 .c <CNC_NAME> <DSN_NAME> opens a connection internally known as
201 <CNC_NAME>, using the specified DSN.
202 .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 .c <CNC_NAME> sets the current connection to the connection
209 known as <CNC_NAME>.
210 .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 .close Closes a connection. Full syntax is: .close <CNC_NAME>.
217 .cd Changes the current working directory. Full syntax is: .cd
219 <DIR_NAME>.
220 .copyright
222 Displays copyright information.
223 .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 .dn Lists all schemas if no argument is provided. .d <SCHEMA_NAME>
229 lists specified schema.
230 .dt Lists all tables if no argument is provided. .d <TABLE_NAME>
232 lists specified table.
233 .dv Lists all views if no argument is provided. .d <VIEW_NAME> lists
235 specified view.
236 .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 .fkundeclare
251 Un-declares a foreign key (does the opposite of .fkdeclare).
252 .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 .echo Sends output to stdout, full command is: .echo [<TEXT>].
258 .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 .export <NAME> <FILE_NAME> exports the contents of the <NAME>
265 parameter to the specified file.
266 .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 .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 .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 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 .H Set output format. Full syntax is: .H [HTML|XML|CSV|DEFAULT].
288 .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 .i Executes commands from file the specified file: .i <FILE_NAME>.
295 .l Lists all data sources if no argument is provided. .l <DSN>
297 lists information about the specified DSN.
298 .lp Lists all available database providers if no argument is pro‐
300 vided. .lp <provider> lists information about the specified
301 provider.
302 .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 If a DSN with a similar name already exists, it is first re‐
312 moved.
313 For example: ".lc mydsn PostgreSQL://HOST=moon;DB_NAME=mydb".
315 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 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 .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 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 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 .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 The .<OPERATION> argument specifies which operation must be per‐
355 formed, among DELETE, REPLACE and ADD.
356 .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 .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 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 .lr Removes a DSN declaration. Full syntax is: .lc <DSN_NAME>.
375 .meta Updates the current connection's meta data (use this command af‐
377 ter having modified the database's schema).
378 .o Sends output to a file or |pipe. Full syntax is: .o <FILE_NAME>
380 or .o |<COMMAND>.
381 .option
383 Defines options shared among all the connections. Full syntax
384 is: .option [<OPTION NAME> [<VALUE>]].
385 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 .pivot Performs data summarization on a data set. Full syntax is:
392 .pivot <SELECT> <ROW_FIELDS> [<COLUMN_FIELDS> [<DATA_FIELDS>
393 [...]]].
394 The <SELECT> defines the data set to perform summarization on.
396 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 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 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 Examples:
424 .pivot "SELECT * FROM food" person food
426 .pivot "SELECT * FROM products" category "CASE WHEN price < 15
428 THEN 'low' ELSE 'high' END" [AVG]price
429 .pivot "SELECT * FROM sales" category,product - [AVG]quantity
431 .q Quits the application.
435 .qecho Sends output to the output stream (stdout). Full syntax is: .qe‐
437 cho <TEXT>.
438 .qa Lists all saved query buffers in dictionary.
440 .qd Deletes a query buffer from the dictionary. Full syntax is: .qd
442 <QUERY_BUFFER_NAME>
443 .ql Loads query buffer from dictionary into the current query buf‐
445 fer. Full syntax is: .ql <QUERY_BUFFER_NAME>.
446 .qp Shows the contents of the current query buffer.
448 .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 .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 .qw Writes the query buffer to the specified file, full syntax is
457 .qw <FILE_NAME>.
458 .s Show commands history. .s <FILE_NAME> saves command history to
460 specified file.
461 .set Sets, shows or lists internal parameters.
463 .set lists all the defined internal parameters.
465 .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 .set <NAME> shows the contents of the internal parameter named
471 <NAME>.
472 .setex Set internal parameter as the contents of the FILE file or from
474 an existing table's value.
475 .setex <NAME> <FILE_NAME> (re)defines the the internal parameter
477 named <NAME> with the contents of the specified file name.
478 .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 .unset Unset (delete) internal parameter.
485 .unset unsets all the internal parameters.
487 .unset <NAME> unsets the internal parameter named <NAME>.
489
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 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
504 Consult the Libgda's home page at http://www.gnome-db.org/.
505
508 Vivien Malerba (for Libgda's authors, please consult the AUTORS file
509 within the Libgda's sources)
510
514 psql(1), mysql(1), sqlite3(1)
515Version 5.2.10 gda-sql(1)