    .

    .

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.

    .bind attached c0 c1

creates the "attached" connection where all the tables from connection "c1" are mapped to the "c1" schema and all the tables from connection "c2" are mapped to the "c2" schema.
Opens a new connection or lists opened connections

    .c [@CNC_NAME@ [@DSN@|@CONNECTION STRING@]]

Opens a new connection, named @CNC_NAME@ using either a DSN name (use the ".l" command to list all declared DSN), or a connection string (use the ".lp" command to list database providers and see which argument their connection string accepts).
If only a connection name is passed as argument, then the named connection becomes the current active connection. As a side note, using "~" as a connection name will switch to a (new or already opened) connection which contains the meta data for the active connection.
If no connection name is specified, then this command lists all the opened connections

    .c st SalesTest

    .c ht htsql_demo.db

    .c

List of opened connections Name | Provider | DSN or connection string | Username -----+----------+--------------------------------------------------+--------- ht | SQLite | DB_DIR=.;EXTRA_FUNCTIONS=TRUE;DB_NAME=htsql_demo | st | SQLite | SalesTest | (2 rows)
This example opens two SQLite connections, named "ht" and "st"

    .c ~

    .d

Schema | Name | Type | Owner -------+----------------------------------+------------+------ main | __declared_fk | BASE TABLE | main | _all_types | VIEW | main | _attributes | BASE TABLE | [...] (32 rows)
This command switches to the connection containing the currently active connection's meta data, the ".d" command displays all the tables and views of the current active connection.

    .c

This command lists all opened connections (including the meta data connections)
List of opened connections Name | Provider | DSN or connection string -----+----------+-------------------------------------------------------------------------------------------- ht | SQLite | DB_DIR=.;EXTRA_FUNCTIONS=TRUE;DB_NAME=htsql_demo st | SQLite | SalesTest ~st | SQLite | DB_DIR=/home/vivien/.local/share/libgda;DB_NAME=gda-sql-SalesTest ~ht | SQLite | DB_DIR=/home/vivien/.local/share/libgda;DB_NAME=gda-sql-ab1b5c41e024d87d739797e820dadb05d26 (4 rows)
List all defined data source (DSN), or named DSN's attributes.

    .l [@DSN@]

If no @DSN@ argument is specified, then lists all the DSN, otherwise gives information about the specified DSN

    .l

DSN list DSN | Description | Provider ----------+--------------------------------------+----------- mysales | | MySQL pgsales | | PostgreSQL SalesTest | Test database for a sales department | SQLite (3 rows)

    .l mysales

DSN 'mysales' description Attribute | Value ---------------+------------------ DSN name | mysales Provider | MySQL Description | Parameters | HOST: gda DB_NAME: test1 Authentication | USERNAME: gdauser System DSN? | No (6 rows)
The output above shows that the "mysales" DSN uses a MySQL server, hosted on the "gda" machine, for the database "test1", and that the username will be "gdauser"
List all installed database providers (or named one's attributes)

    .lp [@PROVIDER@]

Shows information about database providers installed, or about a specific database provider if specified. Use this information to create connection strings

    .lp

Installed providers list Provider | Description --------------+------------------------------------------------------------------------------------ BDBSql | Provider for BDB SQL databases Berkeley-DB | Provider for Berkeley databases Firebird | Provider for Firebird databases FirebirdEmbed | Provider for embedded Firebird databases Ldap | Provider for database where tables are based on data contained in an LDAP directory MSAccess | Provider for Microsoft Access files MySQL | Provider for MySQL databases Oracle | Provider for Oracle databases PostgreSQL | Provider for PostgreSQL databases SQLCipher | Provider for SQLCipher SQLite | Provider for SQLite databases Web | Provider for web server proxies (12 rows)

    .lp MySQL

Provider 'MySQL' description Attribute | Value ---------------+----------------------------------------------------------------------------------------------- Provider | MySQL Description | Provider for MySQL databases DSN parameters | DB_NAME: Database name. The name of a database to connect to (string), HOST: Database server. Host on which the database server is running (string), PORT: Port. Database server port (int), UNIX_SOCKET: UNIX Socket. Full path of the UNIX socket to use when connecting locally (string) USE_SSL: Require SSL. Whether or not to use SSL to establish the connection (boolean), COMPRESS: Compress. Use compression protocol (boolean) Authentication | USERNAME (string), PASSWORD (string) File | /usrlib/libgda-5.0/providers/libgda-mysql.so (5 rows)
This output shows all the arguments accepted by the MySQL provider (DB_NAME, HOST, PORT, ...)
Performs a statistical analysis on the data from SELECT, using ROW_FIELDS and COLUMN_FIELDS criteria and optionally DATA_FIELDS for the data.

    .@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 pro‐ vided.
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.
Suppose for these example that we have a table named "student" where students of schools are are listed (this example comes from the HTSQL tutorial database, see http://htsql.org/doc/tutorial.html). Part of this table's contents is the following:
name | gender | dob | school_code | program_code | start_date --------------------+--------+------------+-------------+--------------+----------- Linda Wright | f | 1988-10-03 | ns | umth | 2007-08-15 Beth Thompson | f | 1988-01-24 | ns | gmth | 2007-08-15 Sheri Sanchez | f | 1985-05-14 | edu | gedu | 2007-08-15 John Stone | m | 1984-01-28 | bus | pbusad | 2007-08-15 Helen Johnson | f | 1986-02-03 | eng | ucompsci | 2007-08-15 Anna Carroll | f | 1985-03-29 | edu | gedu | 2007-08-15 Carol Walden | f | 1988-04-09 | ns | uphys | 2007-08-15

    .pivot "select * from student" gender

The output will show one line per gender and will show the number of students for each gender
gender | -------+---- f | 284 m | 172 (2 rows)

    pivot "select name, gender, strftime ('%Y', dob) as year_of_birth from student order by year_of_birth" gender year_of_birth

This example displays, by gender of students, the number of students for each year where at least one student is born
gender | 1982 | 1983 | 1984 | 1985 | 1986 | 1987 | 1988 | 1989 | 1990 | 1991 | 1992 -------+------+------+------+------+------+------+------+------+------+------+----- m | 11 | 26 | 39 | 50 | 30 | 13 | 3 | | | | f | | | | 12 | 38 | 36 | 56 | 61 | 33 | 37 | 11 (2 rows)

    .pivot "select name, gender, strftime ('%Y', dob) as year_of_birth from student order by year_of_birth" year_of_birth gender

This example displays the same information as the one above, but with one row per year
year_of_birth | m | f --------------+----+--- 1982 | 11 | 1983 | 26 | 1984 | 39 | 1985 | 50 | 12 1986 | 30 | 38 1987 | 13 | 36 1988 | 3 | 56 1989 | | 61 1990 | | 33 1991 | | 37 1992 | | 11 (11 rows)

    .pivot "select name, gender, strftime ('%Y', dob) as year_of_birth, school_code from student order by year_of_birth" year_of_birth school_code

This example is similar to the one above, but replaces the gender by the school_code for the columns
year_of_birth | art | bus | edu | eng | la | ns | ph --------------+-----+-----+-----+-----+----+----+--- 1982 | 11 | | | | | | 1983 | 18 | 8 | | | | | 1984 | 15 | 18 | 6 | | | | 1985 | 16 | 18 | 18 | 10 | | | 1986 | 2 | 12 | 23 | 17 | 14 | | 1987 | | 2 | 16 | 16 | 12 | 3 | 1988 | | | 6 | 14 | 14 | 22 | 3 1989 | | | | 5 | 22 | 13 | 21 1990 | | | | | 10 | 7 | 16 1991 | | | | | | 16 | 21 1992 | | | | | | | 11 (11 rows)

    .pivot "select school_code, program_code, julianday(Date('now')) - julianday(start_date) as days from vs;" program_code - [avg]days

This example displays, for each program code, the average number of days since the course was started
program_code | avg_days -------------+------------ umth | 1163.153846 gmth | 1106.923077 gedu | 1180.909091 pbusad | 1134.846154 ucompsci | 1186.583333 uphys | 1206.777778 psci | 1156.125000 uspan | 1137.800000 [...] (39 rows)

    .pivot "select school_code, program_code, (julianday(Date('now')) - julianday(start_date))::int as days from vs;" program_code school_code [max]days

This example is similar to the one above, but it shows the maximum number of days since the course was started, and is split in columns by school code
program_code | ns[max_days] | edu[max_days] | bus[max_days] | eng[max_days] | la[max_days] | ph[max_days] | art[max_days] -------------+--------------+---------------+---------------+---------------+--------------+--------------+-------------- umth | 1613 | | | | | | gmth | 1613 | | | | | | gedu | | 1613 | | | | | pbusad | | | 1613 | | | | ucompsci | | | | 1613 | | | uphys | 1613 | | | | | | psci | | 1613 | | | | | uspan | | | | | 1613 | | [...] (39 rows)
Set or show internal parameter, or list all if no parameter specified

    .set [@NAME@ [@VALUE@|_null_]]

Use this command to define or re-define variables which can later be used in stataments. Use the ".unset" command to undefine a variable. If you want set a variable to the SQL NULL, then use the "_null_" constant value.

    .set ID 23

    .set ID

This example defines the ID variabe (beware, case sensitive) ID to 23. Later you can then run a query like "SELECT * FROM customers WHERE id=##ID::int"