MotorsportQL: Motorsport Query Language

Martin Galpin (m@66laps.com)

Introduction

Motorsport Query Language (known as MotorsportQL) is a small query language for retrieving motorsport telemetry stored in a database. It has a simple, SQL-like syntax.

The primary purpose of MotorsportQL is to quickly perform relational queries on a database of motorsport telemetry without the need to understand its particular storage implementation.

For example, MotorsportQL makes it easy to perform simple queries such as:

select fastest where venue.name = "Silverstone"
    and vehicle.category = "Formula One" and datetime < '2010-01-01'
        and (user = "Michael Schumacher" or user = "Nico Rosberg")

A reference MotorsportQL parser is provided under the terms of the GNU General Public License v3. For more information see python-motorsportql.

The examples shown below give examples of valid MotorsportQL statements. The response format is beyond the specification of this document and it is left to the implementation to decide on the most appropriate format.

For example, a RESTful web service would probably return responses as JSON formatted arrays. Other implementations may prefer to return results in another format, such as domain specific objects (see python-openmotorsport).

select statement

A select statement retrieves laps from the database.

Synopsis

select_command ::=
    "select"
    ("*" | context_clause | comparison_clause)
    [where_clause]
    [order_clause]
    [limit_clause]
    ;

Description

A select statement returns candidate laps from a database that satisfy an optional where clause. If a where clause is omitted then all laps are considered candidates.

Results can be further reduced using either a context or comparison clause. Alternatively, if an asterisk (*) is given, all matching candidate laps will be returned.

The context and comparison clauses provide a shorthand notation to common queries.

Context clause

context_clause ::=
    ("fastest" | "slowest") [ "," context_clause ]
    ;

The context clause provides a shorthand notation to retrieve the fastest or slowest lap within a list of candidate laps.

Multiple superlatives may be given and the order of which is not significant. However, a context is considered definitive and therefore a maximum of one lap will be returned for each context.

Examples

Find the fastest lap by Michael Schumacher:

select fastest where user = "Michael Schumacher"

Find the slowest lap at Silverstone by Nico Rosberg:

select slowest where venue.name = "Silverstone"
    and user = 'Nico Rosberg'

Find the fastest and slowest lap at Silverstone in a Formula One car:

select fastest, slowest where venue.name = "Silverstone"
    and vehicle.category = "Formula One"

Comparison clause

comparison_clause ::=
    ( "faster" | "slower" )
    "than" lap_def
    ;

The comparison clause provides a shorthand notation to further reduce candidate laps relative to a given lap.

Only a single comparative can be given in each query and must be followed by than and lap_def.

lap_def is a integral session identifier followed a period and an integral lap index (starting at 0). It is defined as:

lap_def ::= session_id "." lap_index ;

session_id ::= digit_def+ ;

lap_index ::= digit_def+ ;

digit_def ::= [0-9]+

It could be said that lap_def is coupling MotorsportQL to a particular storage implementation (one that relies of a integral session identifier). Indeed, this seemingly violates the original raison d'être of the language. It is therefore suggested that any MotorsportQL implementation abstract lap_def in a manor consistent with its own API.

Examples

Find laps faster than the second lap in session 66 by Michael Schumacher:

select faster than 66.1 where user = 'Michael Schumacher'

Find laps slower than the eleventh lap in session 1066 in a vehicle before the year 2010:

select slower than 1066.10 where vehicle.year < 2010

where clause

where_clause ::= "where" condition_def { logical_set condition_def } ;

condition_def ::=
    (
      (comparator_set binary_operator_set quoted_string) |
      (function_name "(" delimeted_quoted_string ")")
    )
    ;

quoted_string_def ::= [a-zA-Z0-9]+ ;

delimeted_quoted_string_def ::=
    quoted_string_def { "," delimeted_quoted_string_def }

The optional where clause provides a familiar notation for reducing candidate laps by matching a sequence of boolean expressions.

The most common form of expression is:

comparator binary_operator quoted_string

A comparator is defined for every metadata field declared in the OpenMotorsport file format. See the OpenMotorsport specification for more information.

A binary_operator can be any of: = != < > <= => or a conditional operator such as like. The right hand side of an expression should be properly enclosed in quotes using either " or '.

Multiple expressions may be separated by the logical operators and or and expressions can be nested within parentheses. The traditional order of precedence is applied (and is evaluated before or).

Comparators

A list of valid comparators is as follows:

Examples

Find the fastest lap in a Formula One car made before 2010, driven by Nico Rosberg.

select fastest where vehicle.year < 2010
    and user = 'Nico Rosberg'
        and vehicle.category = "Formula One"

Find a lap faster and slower than the second lap of session id 66, before June 22th 2009.

select faster than 66.1 where datetime < '2009-06-22'
    and (user = 'Rubens Barrichello' or 'Jenson Button')

Find laps with a lap time faster or equal to 137.121 seconds (analogous to a faster clause).

select * where lap.length < 137121

Functions

MotorsportQL also exposes the following functions for use as boolean conditions in a where clause:

Examples

Find all laps with a channel called Acceleration X.

select * where haschannel('Acceleration X')

Find all laps with the channels Throttle Position and Brake Position, at Donington Park.

select * where (venue.name = 'Donington Park' and
    haschannel("Throttle Position", "Brake Position"))

order by clause

order_clause ::= "order" "by" sort_by_def { "," sort_by_def } ;

sort_by_def ::= comparator_set [ "asc" | "desc " ] ;

The order by clause sorts results by an arbitrary number of comparators in either ascending (lowest to highest) or descending order (highest to lowest).

If no explicit order is given, results will be ordered according to the following logic.

For faster queries, results will be ordered in ascending lap.length, such that the fastest lap is returned first. Conversely, slower comparisons will be ordered in descending lap.length such that the slowest lap is returned first.

For queries that include a fastest and a slowest context, the order of the results will correspond to the order of the contexts.

For all other queries, if no explicit order is given, results will be in ascending lap.length such that the fastest lap is returned first.

Examples

Find all laps from Brands Hatch, ordered by descending lap time (slowest first).

select * where venue.name = "Brands Hatch" order by lap.length desc

Find all laps in lexicographically ascending user order.

select * order by user

limit clause

limit_clause ::= "limit" digit_def+ ["," digit_def+] ;

The limit clause allows queries to return results up to a given maximum, starting at an optional offset.

When a single argument is given, it denotes the maximum number of results to return. Otherwise, if two arguments are given (separated by a comma), the first specifies the offset of the first lap whilst the second continues to denote the maximum number of results.

Examples

Find all laps up to a maximum of 100.

select * limit 100

Find a maximum of 25 laps by a Schumacher starting at lap 66.

select * limit 66, 25

Appendix A: EBNF Grammar

The following represents a complete Extended Backus-Naur Form (EBNF) definition of MotorsportQL.

NB: MotorsportQL is not case sensitive but for the sake of simplicity, the following grammar only presents rules in lower case.

select_command ::=
    "select"
    ("*" | context_clause | comparison_clause)
    [where_clause]
    [order_clause]
    [limit_clause]
    ;

context_clause ::=
    ("fastest" | "slowest") [ "," context_clause ]
    ;

comparison_clause ::=
    ( "faster" | "slower" )
    "than" lap_def
    ;

where_clause ::= "where" condition_def { logical_set condition_def } ;

limit_clause ::= "limit" digit_def ["," digit_def] ;

order_clause ::= "order" "by" sort_by_def { "," sort_by_def } ;

sort_by_def ::= comparator_set [ "asc" | "desc " ] ;

condition_def ::=
    (
      (comparator_set binary_operator_set quoted_string) |
      (function_name "(" delimeted_quoted_string ")")
    )
    ;

binary_operator_set ::=
    "=" |
    "!=" |
    "<" |
    "<=" |
    ">" |
    ">="
    ;

logical_set ::= "and" | "or" ;

(* A comparator for every field in the OpenMotorsport "metadata" element. *)
comparator_set ::=
    "lap.length" |
    "lap.index" |
    "user" |
    "vehicle.name" |
    "vehicle.category" |
    "vehicle.year" |
    "vehicle.comments" |
    "venue.name" |
    "venue.configuration" |
    "datetime" |
    "duration" |
    "comments" |
    "datasource"
    ;

lap_def ::= session_id "." lap_index ;

session_id ::= digit_def ;

lap_index ::= digit_def ;

quoted_string_def ::= [a-zA-Z0-9]+ ;

delimeted_quoted_string_def ::=
    quoted_string_def { "," delimeted_quoted_string_def }

function_name_def ::= [a-zA-Z]+ ;

digit_def ::= [0-9]+ ;

Appendix B: Reference implementations

The following libraries implements the MotorsportQL specification:

• Python (Apache License Version 2.0)

Appendix C: Version History

Appendix D: License

Copyright © 2010 66laps Limited.