TSQL: Test Suite Query Language

TSQL is a query language for selecting data from [incr tsdb()] test suites partially described in TSNLP User Manual (Volume 2).

TSQL is generally used for selecting rows or columns of data from test suites themselves, although it also has faculties for inspecting or setting [incr tsdb()] variables and for inserting data into test suites. Some examples of select queries include:

Find the item IDs and grammatical sentences that didn’t parse:

select i-id i-input where i-wf = 1 and readings = 0

Pair input sentences with their MRS outputs:

select i-input mrs

Find sentences with fewer than 10 words:

select i-input where i-length < 10

TSQL Syntax

   1 Query        := ( Info | Set | Retrieve | Insert ) ( '.' | $ )
   2 Info         := 'info' ( 'all' | 'relations' | Relation | TsdbConstant | TsdbVariable )
   3 Set          := 'set' TsdbVariable ( Integer | String | ':on' | ':off' )
   4 Retrieve     := ( 'retrieve' | 'select' ) SelectBody
   5 SelectBody   := ( Attribute+ | '*' )
   6                 [ 'from' Relation+ ]
   7                 [ 'where' Disjunction ]
   8                 [ 'report' FormatString ]
   9 Insert       := 'insert' 'into' Relation [ Attribute+ ]
  10                          'values' ( Integer | String | DateTime )+
  11 
  12 TsdbConstant := 'home'
  13               | 'tsdb_home'
  14               | 'relations-file'
  15               | 'tsdb_relations_file'
  16               | 'data-path'
  17               | 'tsdb_data_path'
  18 TsdbVariable := 'result-path'
  19               | 'tsdb_result_path'
  20               | 'result-prefix'
  21               | 'tsdb_result_prefix'
  22               | 'max-results'
  23               | 'tsdb_max_results'
  24               | 'uniquely-project'
  25               | 'tsdb_uniquely_project'
  26 
  27 Relation     := Identifier
  28 Attribute    := Identifier
  29 
  30 Disjunction  := Conjunction ( '|' | '||' | 'or'  Conjunction )*
  31 Conjunction  := Condition ( '&' | '&&' | 'and' Condition)*
  32 Condition    := Attribute ( '=' | '==' | '!=' | '~' | '!~' ) String
  33               | Attribute ( '=' | '==' | '!=' | '<' | '>' | '<=' | '>=' ) ( Integer | DateTime )
  34               | ( '!' | 'not' ) Condition
  35               | '(' Disjunction ')'
  36 
  37 FormatString := String
  38 
  39 Integer      := /[+-]?[0-9]+/
  40 Digit        := /[0-9]+]
  41 
  42 String       := /"([^"\\]|\\.)*"/"
  43               | /'([^'\\]|\\.)*'/
  44 
  45 DateTime     := Date [ ( Time | '(' Time ')' ) ]
  46               | [ ':' ] 'today'
  47               | [ ':' ] 'now'
  48 Date         := FullYear Month [ '-' Day ]
  49               | [ Day '-' ] Month '-' Year
  50 FullYear     := Digit Digit Digit Digit
  51 Year         := [ Digit Digit ] Digit Digit
  52 Month        := [ Digit ] Digit
  53               | MonthName
  54 MonthName    := 'jan' | 'feb' | 'mar' | 'apr' | 'may' | 'jun'
  55               | 'jul' | 'aug' | 'sep' | 'oct' | 'nov' | 'dec'
  56 Day          := [ Digit ] Digit
  57 Time         := Digit Digit ':' Digit Digit [ ':' Digit Digit ]
  58 
  59 Identifier   := Character ( Character | Digit | '-' | '_' )+
  60 Character    := /[a-zA-Z]+/

Notes on the Syntax

Different publications and implementations have some differences in the syntax. Some of those differences are listed here, excepting obvious bugs in the BNFs, as well as some general notes about the query language.

Case Sensitivity

Some literals in the syntax (e.g., select, info, or from) are case-insensitive, but relation (table) names, attributes (column names), and some other literals (e.g., all, relations, tsdb_home, etc.) are case-sensitive. The names of months in dates are case-insensitive.

Date Formats

The TSNLP version of TSQL does not define or accept YYYY-MM-DD dates, nor does it define MonthName forms (although it appears to accept them). Note that MonthName may be locale-dependent.

Report Formatting

FormatString does not appear to be defined or even described anywhere in prior literature. From my experiments, it seems to be a printf string with format specifiers (%s, %d, or %i) used to insert column values (though it does not appear to matter which is used; e.g., %d could be used for a :string value, etc.). For instance, a query like:

select i-id i-input i-date

…could use up to three format specifiers (any more may trigger an error), such as:

report "ID=%s,Input=%s,Date=%s"

If fewer than three format specifiers are used, the remaining data is output in [incr tsdb()] table format.

Order of Operations in Conditions

The TSNLP definition of TSQL syntax does not specify an order of operations for boolean operations on conditions. In the BNF above, I specifically scoped disjunctions over conjunctions, which seems to follow the behaviour of LOGON’s tsdb -query utility. Thus, the following query would match items with i-id = 10 whether or not its i-input matched [Dd]og:

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

Parentheses can be used to override this order:

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

Implementation Differences

The PyDelphin implementation of TSQL as of 2018-10-05 only includes select queries without report clauses. It differs from the LOGON tsdb -query utility in some ways:

  • select * requires a from clause
  • select * from item result does not also include columns from parse (the “intervening” table joined in order to join item and result)
  • select * from item where readings > 0 does not include columns from parse (where readings is defined)

PyDelphin also adds a couple features:

  • Attributes in the projection (column list) and where clause of the query may optionally use qualified names to specify both the table and column (e.g., result.parse-id)
  • Multiple where clauses may be used which act as a conjunction scoped over disjunctions. Thus, the following is the same as the parenthesized form in the “Order of Operations in Conditions” section above.

    • select i-input where i-id = 10 or i-id = 20 where i-input ~ “[Dd]og”

Multiple where clauses are useful for assembling queries. A user may add an additional global constraint by appending a new where clause without having to worry about altering the order of operations of existing conditions.

Feature Requests

1. adding support for left/right/inner joins. See https://github.com/delph-in/pydelphin/issues/321.

Last update: 2020-11-04 by AlexandreRademaker [edit]