gda-sql(1)

📦
version 5.2.10 · 2 versions
, in debian bookworm, debian trixie, fedora 43, fedora 44, ubuntu jammy, ubuntu noble, ubuntu resolute, ubuntu stonking

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

okay bestie, buckle up, because this is gda-sql(1) from the LIBGDA manual pages and it's genuinely iconic 💖

✨ NAME ✨

gda-sql — an SQL console based on Libgda. that's it, that's the whole vibe 🎀

🎀 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 💜

So gda-sql is an SQL console built on the Libgda library, and honestly it's giving main character energy 🌟

It lets you type in queries interactively, fire them off to be executed by a connection, and then see the query results. literally the whole loop. obsessed.

You can have several connections open at the same time 😌 which means you can switch the active connection to any of the opened ones whenever you want. When it starts up, gda-sql opens a connection for each connection you specified on the command line — plus optionally one that corresponds to the GDA_SQL_CNC environment variable. The prompt shows you which connection is currently being used when commands get executed. Want the tea on the syntax for specifying a connection on the command line? Check the .c internal command, it explains everything 🫶

Alternatively, input can come from a file. And on top of that, it comes with a bunch of meta-commands and various shell-like features that make writing scripts and automating a whole variety of tasks so much easier ✨

You can also run this tool as a script using the classic '#!' string at the very start of a script file — with the caveat that the behaviour of arguments passed on the line after the '#!' command is undefined 🙈. Example:

#!/bin/path/to/gda-sql
#!/usr/bin/env gda-sql

🌸 OPTIONS 🌸

gda-sql accepts the following options:

  • --help — shows the command-line options ✨
  • -o, --output-file <filename> — picks a file that outputs get redirected to 💾
  • -C, --command — runs just a single command (SQL or internal) and then dips out 👋
  • -f, --commands-file <filename> — executes commands from <filename>, then exits (unless -i was specified, bestie)
  • -i, --interactive — keeps the console open after executing a file (this one is used together with the -f option) 🫶
  • -l, --list-dsn — lists the configured data sources and exits
  • -L, --list-providers — lists the installed database providers and exits
  • -s, --http-port <port> — starts the embedded HTTP server on port <port> 🌐
  • -t, --http-token <token phrase> — makes HTTP clients authenticate by providing the <token phrase> (it's an empty phrase by default 🔐)
  • --data-files-list — lists all the files used to hold information related to each connection. that's stuff like the info the tool gathered about the connection: meta data, defined statements, all of it 📋
  • --data-files-purge <criteria> — removes the file used to hold information related to each connection, for whatever criteria you pass as the argument 🧹
heads up: adding "list-only" to the criteria — either before or after it, separated with a comma — means it will not actually remove the file. it just looks cute.

The criteria are:

  • "non-dsn" — removes all the files which do not correspond to a DSN (data source name). these are the files created when a connection is specified using connection parameters instead of using a DSN
  • "non-exist-dsn" — same as "non-dsn", except it also removes the files which belonged to a DSN that doesn't exist anymore 💀
  • "all" — removes all the files, for a complete cleanup

For example: --data-files-purge all,list-only lists all the files (the ones that would get removed if the command were --data-files-purge all).

🌈 ENVIRONMENT 🌈

gda-sql can be configured through some environment variables:

  • GDA_SQL_CNC — to define a connection that should systematically be opened when the program starts
  • PAGER — to define a text pager program to use (by default this is determined by the system)
  • GDA_NO_PAGER — to specify that no text pager should be used at all
  • GDA_SQL_EDITOR, EDITOR, VISUAL — to define a text editor to be used (these variables get examined in exactly 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 it's .gdasql_history); set it 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, it also dumps the data model's columns' types and the values' attributes
  • GDA_DATA_MODEL_DUMP_TITLE — if set, it also dumps the data model's title
  • GDA_DATA_MODEL_NULL_AS_EMPTY — if set, it replaces 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 that value. If the value is -1, then the actual terminal size is used (if it can be determined) 📏

gda-sql can also 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 that connection (like the meta data) is stored in a $HOME/.local/share/libgda/gda-sql-<DSN>.db file 💾

💅 SQL commands 💅

You can run any SQL that the database engine of the current connection understands 😌

On top of that, an SQL statement can contain variables expressed as ##<name>::<type> where <name> is the variable's name and <type> is its declared type. That type 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 🧚

Besides SQL commands, gda-sql also supports internal commands. These are different from SQL commands because they start with the . or \ character. Here's the full roster, bestie:

  • .? — Lists all internal commands
  • .bind — Binds two or more connections into a single new one (which lets SQL commands 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 be specified using the <USERNAME>[:<PASSWORD>]@<DSN_NAME> or <USERNAME>[:<PASSWORD>]@<CNC_DEFINITION> syntax, and if a username or a password is required but not specified, it will be 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_DEFINITION> 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 the specified schema.
  • .dn — Lists all schemas if no argument is provided. .d <SCHEMA_NAME> lists the specified schema.
  • .dt — Lists all tables if no argument is provided. .d <TABLE_NAME> lists the specified table.
  • .dv — Lists all views if no argument is provided. .d <VIEW_NAME> lists the specified view.
  • .fkdeclare — Declares a new foreign key (no constraint is added to the database, jsyk). 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 mentioned 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 precedence over any declared foreign key. no cap.
  • .fkundeclare — Un-declares a foreign key (it does the exact opposite of .fkdeclare) ↩️
  • .e — Edits the query buffer with an 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 an internal parameter or a table's value to the FILE file. Internal parameters are named values used when SQL statements 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 foreign 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 provided. .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 possible 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 performed, 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 parenthesis 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 without 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 commas (forming a valid SQL expression). In this case a row will be created for each combination of values of each of the expression.

The <COLUMN_FIELDS> defines the fields from the data set from which each individual value will yield to a column in the analysis. Its syntax is similar to the <ROW_FIELDS> one. If not specified (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 expression 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 buffer. 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_BUFFER_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 <TABLE> table, column <COLUMN> for the row selected by <ROW_CONDITION>.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 rectify 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 🔥

That's the whole vibe, bestie! This page is from version 5.2.10 of gda-sql(1). Now go slay those queries! 💅✨🦋

📦 where this page comes from