--- libgda-5.2.0/tools/help/C/gda-sql-help.xml.orig 1970-01-01 01:00:00.000000000 +0100 +++ libgda-5.2.0/tools/help/C/gda-sql-help.xml 2013-11-16 10:27:41.669203270 +0100 @@ -0,0 +1,419 @@ + + + + + + + + . + + + + . + + + + + + + Bind connections or datasets into a single new one (allowing SQL commands to be executed across multiple connections and/or datasets). + + + .bind @CNC NAME@ @OBJ NAME@ [@OBJ NAME@ ...] + creates a new connection named @CNC_NAME@, which binds data from each of the named object + (@OBJ NAME@). If a named object is a connection, then all the tables in the named connection + are bound (in a schema named after the connection name) and if a named object is a dataset, + the dataset is bound as a table (named after the dataset's name). + + + .bind attached c0 c1 ds1 + creates the "attached" connection where all the tables from connection "c1" are mapped to the "c1" schema, all the tables from connection "c2" are mapped to the "c2" schema, and the "ds1" + dataset is mapped to the "ds1" table. + + + + + + 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 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 | +SalesTest | 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" + + + + +
+ + + The following commands allow you to manipulate LDAP entries directly, and are thus only available for LDAP + connections. Once you have a search result you are interested into keeping, you can create a table out of it + using the syntax below, and then access to the data like any normal other table using SQL commands on that table. + + CREATE LDAP TABLE @table name@ [BASE=@base DN@] [FILTER=@LDAP filter@] [ATTRIBUTES=@LDAP attributes@] [SCOPE=@search scope@] + + + CREATE LDAP TABLE someusers FILTER='(cn=*le*)' ATTRIBUTES='cn,mail' SCOPE='SUBTREE'; + This example defines a table named "someusers" for the "(cn=*le*)" search filter, using a subtree scope; + the table will have 3 columns: the DN, cn and mail + + + Other commands to manipulate LDAP tables are DROP and DESCRIBE commands: + DROP [LDAP] TABLE @table name@; + DESCRIBE LDAP TABLE @table name@; + + + DESCRIBE LDAP TABLE someusers; + Paramètre │ Valeur +───────────┼────────── +BASE │ +FILTER │ (cn=*le*) +ATTRIBUTES │ cn,mail +SCOPE │ SUBTREE +(4 rows) + +
+ + + + + + + Search LDAP entries using a filter and various criteria + + + .ldap_search @filter@ [@base|onelevel|subtree@ [@base DN@]] + The @filter@ argument is the only mandatory and has to be a valid LDAP filter expression. The + other arguments, if present are in order the search scope (among "base", "onelevel" and "subtree"), and the + base DN (which is the starting point of the search) + The results of the search will be a table where the first column is the DN of the entry, and + the other columns are mapped to the LDAP attributes (see the "ldap_attributes" option) + LDAP filters must respect the RFC 2254 (see http://www.faqs.org/rfcs/rfc2254.html) and what the + actual LDAP server understands. Note that if the outer most opening and cloding parenthesis are omitted, they + will be automatically added. Examples of LDAP search filter are: "(objectClass=*)" to fetch all objects, + "(&(objectClass=inetOrgPerson)(!cn=tom))" to fetch all persons except "tom". Operators are: "=" for equal, + "~=" for approximately equal "<=" ">=" for lexicographically less than or equal and greater than or equal + respectively, "&", "|" and "!" for the AND, OR and NOT logical operators respectively. + Note that the "ldap_dn" option defines the behaviour of the "Distinguished name" column + (use the ".option" command to change the option value). The possible values for this option are: "dn" (default + behaviour), "rdn" (the DN is replaced by the RND) and "none" (the DN column is not shown). + + + .option ldap_attributes cn,uid,mail + .ldap_search cn=* onelevel "ou=People,dc=example,dc=gnome-db" + + Distinguished name | cn | uid | mail +------------------------------------------------+--------------------+------------+----------------------- +uid=abarnes,ou=People,dc=example,dc=gnome-db | Anne-Louise Barnes | abarnes | abarnes@example.com +uid=abergin,ou=People,dc=example,dc=gnome-db | Andy Bergin | abergin | abergin@example.com +uid=achassin,ou=People,dc=example,dc=gnome-db | Ashley Chassin | achassin | achassin@example.com +[...] +(150 rows) + + + + +
+ + + The following commands allow you to keep dataset results you have obtained, and to reuse them at a later time. + Everytime a resultset is returned by a command, it can be imediately used as a dataset named "_". + + +
+ +