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
, orX or Y
Conjunction X & Y
,X && Y
, orX and Y
Negation !X
ornot 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 afrom
clauseselect * from item result
does not also include columns from the interveningparse
tableselect i-input from result
returns a matchingi-input
for every row inresult
, 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)[source]¶ 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)[source]¶ 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)[source]¶ 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]]