delphin.tsql

See also

The select command is a quick way to query test suites with TSQL queries.

TSQL – Test Suite Query Language

This module implements a subset of TSQL, namely the ‘select’ (or ‘retrieve’) queries for extracting data from test suites. The general form of a select query is:

[select] <projection> [from <tables>] [where <condition>]*

For example, the following selects item identifiers that took more than half a second to parse:

select i-id from item where total > 500

The select string is necessary when querying with the generic query() function, but is implied and thus disallowed when using the select() function.

The <projection> is a list of space-separated field names (e.g., i-id i-input mrs), or the special string * which selects all columns from the joined tables.

The optional from clause provides a list of table names (e.g., item parse result) that are joined on shared keys. The from clause is required when * is used for the projection, but it can also be used to select columns from non-standard tables (e.g., i-id from output). Alternatively, delphin.itsdb-style data specifiers (see delphin.itsdb.get_data_specifier()) may be used to specify the table on the column name (e.g., item:i-id).

The where clause provide conditions for filtering the list of results. Conditions are binary operations that take a column or data specifier on the left side and an integer (e.g., 10), a date (e.g., 2018-10-07), or a string (e.g., “sleep”) on the right side of the operator. The allowed conditions are:

Condition	Form
Regex match	<field> ~ "regex"
Regex fail	<field> !~ "regex"
Equality	<field> = (integer|date|"string")
Inequality	<field> != (integer|date|"string")
Less-than	<field> < (integer|date)
Less-or-equal	<field> <= (integer|date)
Greater-than	<field> > (integer|date)
Greater-or-equal	<field> >= (integer|date)

Boolean operators can be used to join multiple conditions or for negation:

Operation	Form
Disjunction	X | Y, X || Y, or X or Y
Conjunction	X & Y, X && Y, or X and Y
Negation	!X or not X

Normally, disjunction scopes over conjunction, but parentheses may be used to group clauses, so the following are equivalent:

... where i-id = 10 or i-id = 20 and i-input ~ "[Dd]og"
... where i-id = 10 or (i-id = 20 and i-input ~ "[Dd]og")

Multiple where clauses may also be used as a conjunction that scopes over disjunction, so the following are equivalent:

... where (i-id = 10 or i-id = 20) and i-input ~ "[Dd]og"
... where i-id = 10 or i-id = 20 where i-input ~ "[Dd]og"

This facilitates query construction, where a user may want to apply additional global constraints by appending new conditions to the query string.

PyDelphin has several differences to standard TSQL:

• select * requires a from clause
• select * from item result does not also include columns from the intervening parse table
• select i-input from result returns a matching i-input for every row in result, rather than only the unique rows

PyDelphin also adds some features to standard TSQL:

• optional table specifications on columns (e.g., item:i-id)
• multiple where clauses (as described above)

delphin.tsql.inspect_query(query)

Parse query and return the interpreted query object.

Example

>>> from delphin import tsql
>>> from pprint import pprint
>>> pprint(tsql.inspect_query(
...     'select i-input from item where i-id < 100'))
{'querytype': 'select',
 'projection': ['i-input'],
 'tables': ['item'],
 'where': ('<', ('i-id', 100))}

delphin.tsql.query(query, ts, **kwargs)

Perform query on the testsuite ts.

Note: currently only ‘select’ queries are supported.

Parameters:

• query (str) – TSQL query string
• ts (delphin.itsdb.TestSuite) – testsuite to query over
• kwargs – keyword arguments passed to the more specific query function (e.g., select())

Example

>>> list(tsql.query('select i-id where i-length < 4', ts))
[[142], [1061]]

delphin.tsql.select(query, ts, mode='list', cast=True)

Perform the TSQL selection query query on testsuite ts.

Note: The select/retrieve part of the query is not included.

Parameters:

• query (str) – TSQL select query
• ts (delphin.itsdb.TestSuite) – testsuite to query over
• mode (str) – how to return the results (see delphin.itsdb.select_rows() for more information about the mode parameter; default: list)
• cast (bool) – if True, values will be cast to their datatype according to the testsuite’s relations (default: True)

Example

>>> list(tsql.select('i-id where i-length < 4', ts))
[[142], [1061]]