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
40 gda-sql accepts the following options:
41 --help Show command-line options.
43 -o, --output-file <filename>
45 Specifies a file to which outputs are redirected.
46 -C, --command
48 Run only single command (SQL or internal) and exit.
49 -f, --commands-file <filename>
51 Execute commands from <filename>, then exit (except if -i spec‐
52 ified).
53 -i, --interactive
55 Keep the console opened after executing a file (used with the
56 -f option).
57 -l, --list-dsn
59 List configured data sources and exit.
60 -L, --list-providers
62 List installed database providers and exit
63 -s, --http-port <port>
65 Starts the embedded HTTP server on port <port>
66 -t, --http-token <token phrase>
68 Requires HTTP clients to authenticate by providing the <token
69 phrase> (empty phrase by default)
70 --data-files-list
72 Lists all the files used to hold information related to each
73 connection (ie. information gathered by the tool about the
74 connection such as meta data, defined statements,...)
75 --data-files-purge <criteria>
77 Removes file used to hold information related to each connec‐
78 tion for the criteria passed as argument (note that adding
79 "list-only" to the criteria, either before or after it using a
80 comma, will not actually remove the file):
81 "non-dsn": remove all the files which do not correspond to a
83 DSN (data source name). These are the files created when a con‐
84 nection is specified using connection parameters instead of
85 using a DSN
86 "non-exist-dsn": same as "non-dsn" except it also removes the
88 files which were for DSN which don't exist anymore
89 "all": remove all the files, for a complete cleanup
91 For example: --data-files-purge all,list-only lists all the
93 files (which would be removed if the command was
94 --data-files-purge all).
95
98 gda-sql can be configured through some environment variables:
99 GDA_SQL_CNC
101 to define a connection to systematically be opened when the
102 program starts.
103 PAGER to define a text pager program to use (by default determined by
105 the system).
106 GDA_NO_PAGER
108 to specify that no text pager should be used.
109 GDA_SQL_EDITOR EDITOR VISUAL
111 to define a text editor to be used (variables are examined in
112 this order).
113 GDA_SQL_VIEWER_PNG
115 to define a PNG viewer.
116 GDA_SQL_VIEWER_PDF
118 to define a PDF viewer.
119 GDA_SQL_HISTFILE
121 to define the history file name to use (by default .gdasql_his‐
122 tory), set to NO_HISTORY to disable history logging.
123 GDA_DATA_MODEL_DUMP_ROW_NUMBERS
125 if set, the first column of the output will contain row numbers
126 GDA_DATA_MODEL_DUMP_ATTRIBUTES
128 if set, also dump the data model's columns' types and value's
129 attributes
130 GDA_DATA_MODEL_DUMP_TITLE
132 if set, also dump the data model's title
133 GDA_DATA_MODEL_NULL_AS_EMPTY
135 if set, replace the 'NULL' string with an empty string for NULL
136 values
137 GDA_DATA_MODEL_DUMP_TRUNCATE
139 if set to a numeric value, truncates the output to the width
140 specified by the value. If the value is -1 then the actual
141 terminal size (if it can be determined) is used
142 gda-sql can be compiled with support for binary relocatibility.
145 This will cause data, plug-ins and configuration files to be searched
146 relative to the location of the gda-sql executable file.
147
151 gda-sql stores data source definitions (DSN) in Libgda defined files
152 ($HOME/.local/share/libgda and /etc/libgda-4.0/config where ${prefix}
153 is typically /usr).
154 For each connection defined by a DSN, all the information regarding the
156 connection (such as the meta data) is stored in a
157 $HOME/.local/share/libgda/gda-sql-<DSN>.db file.
158
162 You can run any SQL understood by the database engine of the current
163 connection. Additionally SQL statement can contain variables expressed
164 as ##<name>::<type> where <name> is the variable's name and <type> is
165 its declared type (which can be "int", "string", "boolean", "time",
166 "date", "timestamp" (and other types defined by GLib's syntax).
167 Use the .set internal command to set variable's values.
169
172 In addition to SQL commands, gda-sql supports internal commands which
173 differ from SQL commands because they start with the "." or "\" charac‐
174 ter. These commands are:
175 .? Lists all internal commands
178 .bind Bind two or more connections into a single new one (allowing SQL
180 commands to be executed across multiple connections). .bind
181 <CNC_NAME> <CNC_NAME1> <CNC_NAME2> [<CNC_NAME> ...] creates a
182 new connection named <CNC_NAME> which binds the tables of the
183 <CNC_NAME1>, <CNC_NAME2> and any other connection specified.
184 .c Opens a connection or sets the current connection. Username and
186 password can pe specified using the <USERNAME>[:<PASS‐
187 WORD>]@<DSN_NAME> or <USERNAME>[:<PASSWORD>]@<CNC_DEFINITION>
188 syntax, and if a username or a password is required but not
189 specified, it will ba asked interactively.
190 .c <CNC_NAME> <DSN_NAME> opens a connection internally known as
192 <CNC_NAME>, using the specified DSN.
193 .c <CNC_NAME> <CNC_DEFINITION> opens a connection internally
195 known as <CNC_NAME>, using a connection specified by <CNC_DEFI‐
196 NITION> which is similar to the <DSN_DEFINITION> parameter of
197 the .lc command.
198 .c <CNC_NAME> sets the current connection to the connection
200 known as <CNC_NAME>.
201 .c ~ or .c ~<CNC_NAME> set the current connection to the meta
203 data corresponding to the current connection (for the first
204 notation) or to the meta data corresponding to the <CNC_NAME>
205 connection.
206 .close Closes a connection. Full syntax is: .close <CNC_NAME>.
208 .cd Changes the current working directory. Full syntax is: .cd
210 <DIR_NAME>.
211 .copyright
213 Displays copyright information.
214 .d Lists all database objects if no argument is provided. .d
216 <OBJ_NAME> gives details about the specified object and .d
217 <SCHEMA>.* lists all objects in specified schema.
218 .dn Lists all schemas if no argument is provided. .d <SCHEMA_NAME>
220 lists specified schema.
221 .dt Lists all tables if no argument is provided. .d <TABLE_NAME>
223 lists specified table.
224 .dv Lists all views if no argument is provided. .d <VIEW_NAME> lists
226 specified view.
227 .fkdeclare
229 Declares a new foreign key (no constraint is added to the data‐
230 base). The meta data is modified to take into account a foreign
231 key constraint. The foreign key specification is <fkname>
232 <tableA>(<colA>,...) <tableB>(<colB>,...) where <fkname> is the
233 name given to the foreign key constraint and <tableA> references
234 <tableB> using the columns mentionned between the parenthesis.
235 Note that the (<fkname>, <tableA>, <tableB>) triplet uniquely
236 identifies a declared foreign key (declaring a new foreign key
237 with the same triplet will remove any previously declared one).
238 Note: any actual foreign key constraint will always have prece‐
239 dence over any declared foreign key.
240 .fkundeclare
242 Un-declares a foreign key (does the opposite of .fkdeclare).
243 .e Edits the query buffer with external editor, if no argument is
245 provided. .e <FILE_NAME> edits the specified file name. The
246 external editor can be specified using environment variables.
247 .echo Sends output to stdout, full command is: .echo [<TEXT>].
249 .export
251 Exports internal parameter or table's value to the FILE file.
252 Internal parameters are named values used when SQL statement
253 containing variables are executed.
254 .export <NAME> <FILE_NAME> exports the contents of the <NAME>
256 parameter to the specified file.
257 .export <TABLE> <COLUMN> <ROW_CONDITION> <FILE_NAME> exports the
259 value of the <TABLE> table, column <COLUMN> for the row selected
260 by <ROW_CONDITION> to the specified file. This is most useful to
261 export BLOBs.
262 .g Executes the contents of the query buffer, if no parameter is
264 provided. .g <QUERY_BUFFER_NAME> Executes the contents of the
265 specified query buffer. A named query buffer is created using
266 the .qs command.
267 .graph Creates a graph of tables showing their relations (based on for‐
269 eign key constraints). If no argument is provided, the graph
270 lists all tables. .graph <TABLE_NAME> [<TABLE_NAME>...] creates
271 a graph listing the specified tables.
272 The generated graph is created as the "gdaph.dot" file. If the
274 GDA_SQL_VIEWER_PNG or GDA_SQL_VIEWER_PDF environment variables
275 are set and if the "dot" program (from GraphViz) is found, then
276 the graph is displayed (if a display is available).
277 .H Set output format. Full syntax is: .H [HTML|XML|CSV|DEFAULT].
279 .http Starts/stops the embedded HTTP server. Full syntax is .http
281 [<port> [<authentication_token>]], where <authentication_token>
282 is an optional token phrase which HTTP clients are required to
283 send to authenticate.
284 .i Executes commands from file the specified file: .i <FILE_NAME>.
286 .l Lists all data sources if no argument is provided. .l <DSN>
288 lists information about the specified DSN.
289 .lp Lists all available database providers if no argument is pro‐
291 vided. .lp <provider> lists information about the specified
292 provider.
293 .lc Declares a DSN. Full syntax is: .lc <DSN_NAME> <DSN_DEFINITION>
295 [<DESCRIPTION>]. The <DSN_DEFINITION> format is:
296 <provider>://[<username>[:<password>]@]<connection_params> where
297 <connection_params> is a semi-colon (";") separated list of
298 <key>=<value> pairs where <key> is defined when using .lp
299 <provider> (if <value> contains non alphanumeric characters,
300 they should be represented as specified by the RFC 1738).
301 If a DSN with a similar name already exists, it is first
303 removed.
304 For example: ".lc mydsn PostgreSQL://HOST=moon;DB_NAME=mydb".
306 .lr Removes a DSN declaration. Full syntax is: .lc <DSN_NAME>.
308 .meta Updates the current connection's meta data (use this command
310 after having modified the database's schema).
311 .o Sends output to a file or |pipe. Full syntax is: .o <FILE_NAME>
313 or .o |<COMMAND>.
314 .q Quits the application.
316 .qecho Sends output to the output stream (stdout). Full syntax is:
318 .qecho <TEXT>.
319 .qa Lists all saved query buffers in dictionary.
321 .qd Deletes a query buffer from the dictionary. Full syntax is: .qd
323 <QUERY_BUFFER_NAME>
324 .ql Loads query buffer from dictionary into the current query buf‐
326 fer. Full syntax is: .ql <QUERY_BUFFER_NAME>.
327 .qp Shows the contents of the current query buffer.
329 .qr Resets the query buffer to empty if no argument is provided. .qr
331 <FILE _NAME> loads the specified file into the query buffer.
332 .qs Saves query buffer to dictionary, full syntax is .qs <QUERY_BUF‐
334 FER_NAME>. This creates a new query buffer with the specified
335 name in the dictionary, containing the current query buffer.
336 .qw Writes the query buffer to the specified file, full syntax is
338 .qw <FILE_NAME>.
339 .s Show commands history. .s <FILE_NAME> saves command history to
341 specified file.
342 .set Sets, shows or lists internal parameters.
344 .set lists all the defined internal parameters.
346 .set <NAME> <VALUE> (re)defines the internal parameter named
348 <NAME> to the specified value (which can be the _null_ literal
349 to set it to NULL).
350 .set <NAME> shows the contents of the internal parameter named
352 <NAME>.
353 .setex Set internal parameter as the contents of the FILE file or from
355 an existing table's value.
356 .setex <NAME> <FILE_NAME> (re)defines the the internal parameter
358 named <NAME> with the contents of the specified file name.
359 .setex <NAME> <TABLE> <COLUMN> <ROW_CONDITION> (re)defines the
361 the internal parameter named <NAME> with the value of the <TA‐
362 BLE> table, column <COLUMN> for the row selected by <ROW_CONDI‐
363 TION>.This is most useful to export BLOBs.
364 .unset Unset (delete) internal parameter.
366 .unset unsets all the internal parameters.
368 .unset <NAME> unsets the internal parameter named <NAME>.
370
372 Any bugs found should be reported to the online bug-tracking system
373 available on the web at http://bugzilla.gnome.org/. Before reporting
374 bugs, please check to see if the bug has already been reported.
375 When reporting bugs, it is important to include a reliable way to
377 reproduce the bug, version number of gda-sql, OS name and version, and
378 any relevant hardware specs. If a bug is causing a crash, it is very
379 useful if a stack trace can be provided. And of course, patches to rec‐
380 tify the bug are even better.
381
385 Consult the Libgda's home page at http://www.gnome-db.org/.
386
389 Vivien Malerba (for Libgda's authors, please consult the AUTORS file
390 within the Libgda's sources)
391
395 psql(1), mysql(1), sqlite3(1)
396Version 4.2.4 2011-02-25 gda-sql(1)