1 --- libgda-5.2.0/tools/help/C/gda-sql-help.xml.orig 1970-01-01 01:00:00.000000000 +0100
2 +++ libgda-5.2.0/tools/help/C/gda-sql-help.xml 2013-11-16 10:27:41.669203270 +0100
11 + <synopsis>.</synopsis>
15 + <synopsis>.</synopsis>
20 + <!-- ".bind" command -->
21 + <command name="bind">
22 + <shortdescription>Bind connections or datasets into a single new one (allowing SQL commands to be executed across multiple connections and/or datasets).
25 + <synopsis>.bind @CNC NAME@ @OBJ NAME@ [@OBJ NAME@ ...]</synopsis>
26 + <comment>creates a new connection named @CNC_NAME@, which binds data from each of the named object
27 + (@OBJ NAME@). If a named object is a connection, then all the tables in the named connection
28 + are bound (in a schema named after the connection name) and if a named object is a dataset,
29 + the dataset is bound as a table (named after the dataset's name).</comment>
32 + <synopsis>.bind attached c0 c1 ds1</synopsis>
33 + <comment>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"
34 + dataset is mapped to the "ds1" table.</comment>
38 + <!-- ".c" command -->
40 + <shortdescription>Opens a new connection or lists opened connections</shortdescription>
42 + <synopsis>.c [[@CNC_NAME@] [@DSN@|@CONNECTION STRING@]]</synopsis>
43 + <comment>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).</comment>
44 + <comment>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.</comment>
45 + <comment>If no connection name is specified, then this command lists all the opened connections</comment>
48 + <synopsis>.c SalesTest</synopsis>
49 + <synopsis>.c ht htsql_demo.db</synopsis>
50 + <synopsis>.c</synopsis>
51 + <raw> List of opened connections
52 +Name | Provider | DSN or connection string | Username
53 +----------+----------+--------------------------------------------------+---------
54 +ht | SQLite | DB_DIR=.;EXTRA_FUNCTIONS=TRUE;DB_NAME=htsql_demo |
55 +SalesTest | SQLite | SalesTest |
57 + <comment>This example opens two SQLite connections, named "ht" and "st"</comment>
61 + <synopsis>.c ~</synopsis>
62 + <synopsis>.d</synopsis>
63 + <raw>Schema | Name | Type | Owner
64 +-------+----------------------------------+------------+------
65 +main | __declared_fk | BASE TABLE |
66 +main | _all_types | VIEW |
67 +main | _attributes | BASE TABLE |
70 + <comment>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.</comment>
74 + <synopsis>.c</synopsis>
75 + <comment>This command lists all opened connections (including the meta data connections)</comment>
76 + <raw> List of opened connections
77 +Name | Provider | DSN or connection string
78 +-----+----------+--------------------------------------------------------------------------------------------
79 +ht | SQLite | DB_DIR=.;EXTRA_FUNCTIONS=TRUE;DB_NAME=htsql_demo
80 +st | SQLite | SalesTest
81 +~st | SQLite | DB_DIR=/home/vivien/.local/share/libgda;DB_NAME=gda-sql-SalesTest
82 +~ht | SQLite | DB_DIR=/home/vivien/.local/share/libgda;DB_NAME=gda-sql-ab1b5c41e024d87d739797e820dadb05d26
87 + <!-- ".l" command -->
89 + <shortdescription>List all defined data source (DSN), or named DSN's attributes.
92 + <synopsis>.l [@DSN@]</synopsis>
93 + <comment>If no @DSN@ argument is specified, then lists all the DSN, otherwise gives information about the specified DSN</comment>
96 + <synopsis>.l</synopsis>
98 +DSN | Description | Provider
99 +----------+--------------------------------------+-----------
101 +pgsales | | PostgreSQL
102 +SalesTest | Test database for a sales department | SQLite
107 + <synopsis>.l mysales</synopsis>
108 + <raw> DSN 'mysales' description
110 +---------------+------------------
114 +Parameters | HOST: gda
116 +Authentication | USERNAME: gdauser
119 + <comment>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"</comment>
123 + <!-- ".lp" command -->
124 + <command name="lp">
125 + <shortdescription>List all installed database providers (or named one's attributes)
126 + </shortdescription>
128 + <synopsis>.lp [@PROVIDER@]</synopsis>
129 + <comment>Shows information about database providers installed, or about a specific database provider if specified. Use this information to create connection strings</comment>
132 + <synopsis>.lp</synopsis>
133 + <raw> Installed providers list
134 +Provider | Description
135 +--------------+------------------------------------------------------------------------------------
136 +BDBSql | Provider for BDB SQL databases
137 +Berkeley-DB | Provider for Berkeley databases
138 +Firebird | Provider for Firebird databases
139 +FirebirdEmbed | Provider for embedded Firebird databases
140 +Ldap | Provider for database where tables are based on data contained in an LDAP directory
141 +MSAccess | Provider for Microsoft Access files
142 +MySQL | Provider for MySQL databases
143 +Oracle | Provider for Oracle databases
144 +PostgreSQL | Provider for PostgreSQL databases
145 +SQLCipher | Provider for SQLCipher
146 +SQLite | Provider for SQLite databases
147 +Web | Provider for web server proxies
149 + <synopsis>.lp MySQL</synopsis>
150 + <raw> Provider 'MySQL' description
152 +---------------+-----------------------------------------------------------------------------------------------
154 +Description | Provider for MySQL databases
155 +DSN parameters | DB_NAME: Database name. The name of a database to connect to (string),
156 + HOST: Database server. Host on which the database server is running (string),
157 + PORT: Port. Database server port (int),
158 + UNIX_SOCKET: UNIX Socket. Full path of the UNIX socket to use when connecting locally (string)
159 + USE_SSL: Require SSL. Whether or not to use SSL to establish the connection (boolean),
160 + COMPRESS: Compress. Use compression protocol (boolean)
161 +Authentication | USERNAME (string),
163 +File | /usrlib/libgda-5.0/providers/libgda-mysql.so
165 + <comment>This output shows all the arguments accepted by the MySQL provider (DB_NAME, HOST, PORT, ...)</comment>
169 + <!-- ".pivot" command -->
170 + <command name="pivot">
171 + <shortdescription>Performs a statistical analysis on the data from SELECT, using ROW_FIELDS and COLUMN_FIELDS criteria and optionally DATA_FIELDS for the data.</shortdescription>
173 + <synopsis>.@SELECT@ @ROW_FIELDS@ [@COLUMN_FIELDS@ [@DATA_FIELDS@ ...]]</synopsis>
174 + <comment>The @SELECT@ defines the data set to perform summarization on.</comment>
175 + <comment></comment>
176 + <comment>The @ROW_FIELDS@ defines the fields from the data set from which each individual value will
177 + yield to a row in the analysis (it can be any valid selectable SQL expression on the data
178 + set's fields); multiple expressions can be provided, separated by commas (forming a valid SQL
179 + expression). In this case a row will be created for each combination of values of each of
182 + <comment></comment>
183 + <comment>The @COLUMN_FIELDS@ defines the fields from the data set from which each individual value
184 + will yield to a column in the analysis. Its syntax is similar to the @ROW_FIELDS@ one. If not
185 + specified (or if specified as a single dash ("-") caracter), then only one column will be
186 + created. Note that, if the @DATA_FIELDS@ argument is specified each column created from the
187 + @COLUMN_FIELDS@ will in fact lead to the creation of as many @DATA_FIELDS@ arguments pro‐
190 + <comment></comment>
191 + <comment>The @DATA_FIELDS@ arguments are entirely optional and indicates the way data summarization is
192 + done for each pair of (row,column) values (the default is to count occurrences). The syntax
193 + for each @DATA_FIELDS@ argument is: [aggregate]@SQL_expression@, where the aggregate part is
194 + optional and, if present must be among [SUM], [COUNT], [AVG], [MIN] or [MAX], and the SQL
195 + expression is a valid selectable SQL expression of the data set's fields.
199 + <comment>Suppose for these example that we have a table named "student" where students of schools are
200 + are listed (this example comes from the HTSQL tutorial database, see http://htsql.org/doc/tutorial.html).
201 + Part of this table's contents is the following:
203 + <raw>name | gender | dob | school_code | program_code | start_date
204 +--------------------+--------+------------+-------------+--------------+-----------
205 +Linda Wright | f | 1988-10-03 | ns | umth | 2007-08-15
206 +Beth Thompson | f | 1988-01-24 | ns | gmth | 2007-08-15
207 +Sheri Sanchez | f | 1985-05-14 | edu | gedu | 2007-08-15
208 +John Stone | m | 1984-01-28 | bus | pbusad | 2007-08-15
209 +Helen Johnson | f | 1986-02-03 | eng | ucompsci | 2007-08-15
210 +Anna Carroll | f | 1985-03-29 | edu | gedu | 2007-08-15
211 +Carol Walden | f | 1988-04-09 | ns | uphys | 2007-08-15</raw>
213 + <synopsis>.pivot "select * from student" gender</synopsis>
214 + <comment>The output will show one line per gender and will show the number of students for each gender
224 + <synopsis>pivot "select name, gender, strftime ('%Y', dob) as year_of_birth from student order by year_of_birth" gender year_of_birth</synopsis>
225 + <comment>This example displays, by gender of students, the number of students for each year where
226 + at least one student is born</comment>
227 + <raw>gender | 1982 | 1983 | 1984 | 1985 | 1986 | 1987 | 1988 | 1989 | 1990 | 1991 | 1992
228 +-------+------+------+------+------+------+------+------+------+------+------+-----
229 +m | 11 | 26 | 39 | 50 | 30 | 13 | 3 | | | |
230 +f | | | | 12 | 38 | 36 | 56 | 61 | 33 | 37 | 11
235 + <synopsis>.pivot "select name, gender, strftime ('%Y', dob) as year_of_birth from student order by year_of_birth" year_of_birth gender</synopsis>
236 + <comment>This example displays the same information as the one above, but with one row per year</comment>
237 + <raw>year_of_birth | m | f
238 +--------------+----+---
254 + <synopsis>.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</synopsis>
255 + <comment>This example is similar to the one above, but replaces the gender by the school_code for the columns</comment>
256 + <raw>year_of_birth | art | bus | edu | eng | la | ns | ph
257 +--------------+-----+-----+-----+-----+----+----+---
258 +1982 | 11 | | | | | |
259 +1983 | 18 | 8 | | | | |
260 +1984 | 15 | 18 | 6 | | | |
261 +1985 | 16 | 18 | 18 | 10 | | |
262 +1986 | 2 | 12 | 23 | 17 | 14 | |
263 +1987 | | 2 | 16 | 16 | 12 | 3 |
264 +1988 | | | 6 | 14 | 14 | 22 | 3
265 +1989 | | | | 5 | 22 | 13 | 21
266 +1990 | | | | | 10 | 7 | 16
267 +1991 | | | | | | 16 | 21
268 +1992 | | | | | | | 11
273 + <synopsis>.pivot "select school_code, program_code, julianday(Date('now')) - julianday(start_date) as days from vs;" program_code - [avg]days</synopsis>
274 + <comment>This example displays, for each program code, the average number of days since the course
275 + was started</comment>
276 + <raw>program_code | avg_days
277 +-------------+------------
281 +pbusad | 1134.846154
282 +ucompsci | 1186.583333
291 + <synopsis>.pivot "select school_code, program_code, (julianday(Date('now')) - julianday(start_date))::int as days from vs;" program_code school_code [max]days</synopsis>
292 + <comment>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</comment>
293 + <raw>program_code | ns[max_days] | edu[max_days] | bus[max_days] | eng[max_days] | la[max_days] | ph[max_days] | art[max_days]
294 +-------------+--------------+---------------+---------------+---------------+--------------+--------------+--------------
295 +umth | 1613 | | | | | |
296 +gmth | 1613 | | | | | |
297 +gedu | | 1613 | | | | |
298 +pbusad | | | 1613 | | | |
299 +ucompsci | | | | 1613 | | |
300 +uphys | 1613 | | | | | |
301 +psci | | 1613 | | | | |
302 +uspan | | | | | 1613 | |
308 + <!-- ".set" command -->
309 + <command name="set">
310 + <shortdescription>Set or show internal parameter, or list all if no parameter specified
311 + </shortdescription>
313 + <synopsis>.set [@NAME@ [@VALUE@|_null_]]</synopsis>
314 + <comment>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.</comment>
317 + <synopsis>.set ID 23</synopsis>
318 + <synopsis>.set ID</synopsis>
319 + <comment>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"</comment>
323 + <!-- LDAP section -->
324 + <section name="LDAP">
327 + The following commands allow you to manipulate LDAP entries directly, and are thus only available for LDAP
328 + connections. Once you have a search result you are interested into keeping, you can create a table out of it
329 + using the syntax below, and then access to the data like any normal other table using SQL commands on that table.
331 + <synopsis>CREATE LDAP TABLE @table name@ [BASE=@base DN@] [FILTER=@LDAP filter@] [ATTRIBUTES=@LDAP attributes@] [SCOPE=@search scope@]</synopsis>
334 + <synopsis>CREATE LDAP TABLE someusers FILTER='(cn=*le*)' ATTRIBUTES='cn,mail' SCOPE='SUBTREE';</synopsis>
335 + <comment>This example defines a table named "someusers" for the "(cn=*le*)" search filter, using a subtree scope;
336 + the table will have 3 columns: the DN, cn and mail</comment>
339 + <comment>Other commands to manipulate LDAP tables are DROP and DESCRIBE commands:</comment>
340 + <synopsis>DROP [LDAP] TABLE @table name@;</synopsis>
341 + <synopsis>DESCRIBE LDAP TABLE @table name@;</synopsis>
344 + <synopsis>DESCRIBE LDAP TABLE someusers;</synopsis>
345 + <raw>Paramètre │ Valeur
346 +───────────┼──────────
349 +ATTRIBUTES │ cn,mail
355 + <!-- ".ldap_attributes" command -->
356 + <option name="ldap_attributes">
357 + <shortdescription>Defines the list of attributes taken into account by LDAP commands
358 + </shortdescription>
360 + <synopsis>.option ldap_attributes [attribute,[attribute],...]</synopsis>
361 + <comment>Use this option to specify or display which attributes the LDAP commands operate on</comment>
362 + <comment>The attributes list is a CVS separated list of individual LDAP attributes, for example "cn,uid,email"</comment>
363 + <comment>LDAP allows some attributes to be multi valued (e.g. the "objectClass" attribute). When dealing with multi
364 + valued attributes, the default behaviour is to return an error for the corresponding value. This default behaviour can
365 + be changed by specifying a modifier among "CVS" (values are listed in a CVS syntax), "*" (each row is duplicated with
366 + each value of the attribute), "1" (only the 1st value of the attribute is shown), "CONCAT" (all the values are
367 + made into a string, separated by newlines) or "NULL" (a NULL value is used).
371 + <synopsis>.option ldap_attributes cn,objectClass::*</synopsis>
372 + <comment>Define the attibutes as "cn" and "objectClass", and because "objectClass" is generally multi valued,
373 + specify that a new row be created in the output for each value of "objectClass" for each entry</comment>
377 + <!-- ".ldap_search" command -->
378 + <command name="ldap_search">
379 + <shortdescription>Search LDAP entries using a filter and various criteria
380 + </shortdescription>
382 + <synopsis>.ldap_search @filter@ [@base|onelevel|subtree@ [@base DN@]]</synopsis>
383 + <comment>The @filter@ argument is the only mandatory and has to be a valid LDAP filter expression. The
384 + other arguments, if present are in order the search scope (among "base", "onelevel" and "subtree"), and the
385 + base DN (which is the starting point of the search)</comment>
386 + <comment>The results of the search will be a table where the first column is the DN of the entry, and
387 + the other columns are mapped to the LDAP attributes (see the "ldap_attributes" option)</comment>
388 + <comment>LDAP filters must respect the RFC 2254 (see http://www.faqs.org/rfcs/rfc2254.html) and what the
389 + actual LDAP server understands. Note that if the outer most opening and cloding parenthesis are omitted, they
390 + will be automatically added. Examples of LDAP search filter are: "(objectClass=*)" to fetch all objects,
391 + "(&(objectClass=inetOrgPerson)(!cn=tom))" to fetch all persons except "tom". Operators are: "=" for equal,
392 + "~=" for approximately equal "<=" ">=" for lexicographically less than or equal and greater than or equal
393 + respectively, "&", "|" and "!" for the AND, OR and NOT logical operators respectively.</comment>
394 + <comment>Note that the "ldap_dn" option defines the behaviour of the "Distinguished name" column
395 + (use the ".option" command to change the option value). The possible values for this option are: "dn" (default
396 + behaviour), "rdn" (the DN is replaced by the RND) and "none" (the DN column is not shown).</comment>
399 + <synopsis>.option ldap_attributes cn,uid,mail</synopsis>
400 + <synopsis>.ldap_search cn=* onelevel "ou=People,dc=example,dc=gnome-db"</synopsis>
401 + <comment></comment>
402 + <raw>Distinguished name | cn | uid | mail
403 +------------------------------------------------+--------------------+------------+-----------------------
404 +uid=abarnes,ou=People,dc=example,dc=gnome-db | Anne-Louise Barnes | abarnes | abarnes@example.com
405 +uid=abergin,ou=People,dc=example,dc=gnome-db | Andy Bergin | abergin | abergin@example.com
406 +uid=achassin,ou=People,dc=example,dc=gnome-db | Ashley Chassin | achassin | achassin@example.com
412 + <!-- DATA section -->
413 + <section name="DATA">
416 + The following commands allow you to keep dataset results you have obtained, and to reuse them at a later time.
417 + Everytime a resultset is returned by a command, it can be imediately used as a dataset named "_".
projects / packages / libgda6.git / blob