RTQL File Format

You are generally not going to create an RTQL file by hand (unless you are very technical and familiar with RaceTab and the format). More likely, you will be using one generated from a web site or another program.

There are various services that currently support RTQL downloads.

Here are some of the known services supporting this download and the procedure to get these files. Online Entry Systems ASPI Meetz [1]

ASPI announced their plans to offer an RTQL download option in January 2010. It is not known for sure whether they have released this feature or not.
Athletic.net [2]
You create the meet there and coaches can enter athletes. Then under meet host tools, you download the RTQL file. It imports directly into RaceTab. You can find a screen cast of this import process here: screencast of RTQL import
Coach O [3]
It should be one of the download options .... need instructions to download here....
Direct Athletics [4]
They do not currently offer an RTQL download.
MileSplit [5]
MileSplit (ironically) does not currently offer an RTQL download, but this is because it has an integrated download option within RaceTab that should be used instead.
RunnerCard [6]
They do not currently offer an RTQL download.
Frequently Asked Questions What is RTQL?

It is a custom query language of sorts that vaguely resembles CSS and acts more or less like SQL statements. It is made to be relatively easy to read and create.

RTQL files can be used to create a new meet in RaceTab from scratch by importing it or it can be run against an existing meet file to update and insert data. So RTQL files are just for importing or making changes to a RaceTab meet?

Not entirely. That is the main point, but RaceTab also has the option to export in this format. So you could make your web site or application read it as well.

Typically within RaceTab an RTQL file will be used to set up a new meet from scratch, importing all settings and data. However, it is possible to apply an RTQL file to an existing meet to make batch changes to it. How does it work technically?

RaceTab parses the RTQL-formatted text file and turns it into SQL commands that are executed against the meet database. The SQL is executed in batches or transactions for efficiency and rollback protection in case of failures.

Typically each table is processed together. So each time the table name is changed in the RTQL, RaceTab will process all of the previous commands in its queue. How can I see the RaceTab database format to know what can go into the RTQL?

You can view an .rtab file in any SQLite database viewer. It is not locked or proprietary. Also, the complete database schema is distributed with RaceTab in a text file under its application folder. This file is used by RaceTab to update the schema, but is easily human readable. Language Structural Notes General Information

  • All times should be formatted as units (seconds, inches, meters, points) times 1000 so that they are an INTEGER... so 11.15 is stored as 11150
  • All lines beginning with # are comments and the rest is ignored
  • A blank line tells indicates that the queue of commands in the buffer should be executed. If nothing is in the buffer, a blank line is completely meaningless, except for readability. They could be completely left out and all SQL would be processed together at the end of file.
  • Lines that start with a letter define the table name that will apply by default to the proceeding lines
  • Lines that start with { and end with } will be considered a row that you desire to "insert or replace" into the table
  • These { } lines should have each field separated by ; and within each field the column name is set, then an equal sign, and then an ESCAPED string (with surrounding quotes) or integer value.
  • Though not shown in this example lines that start with [ and end with ] can contain a raw SQL statement that will be executed... such as a DELETE, UPDATE, CREATE TABLE, etc.
  • Keep in mind this is a SQLITE database, so any queries or formats should be done in accordance with that specification and within its limits.
  • You can insert tabs at the beginning of any line that you want for structural reasons... it will be stripped out and ignored in processing.
  • The order of the tables does not matter, except for logical reasons... i.e. create the teams before you create the athletes on the team.
  • The ids could technically be left out and it would just auto-increment. They are recommended though just for clarity of foreign keys within the document.
  • Boolean fields are stored as 0 or 1
  • There are no null values and no blank values. So if there is no value for a field, leave the field off completely or use a zero or empty string (with quotes)... whatever is appropriate.
Using with TFRRS [7]
  • The athletes and teams table has association_name and association_no fields
  • association_name="TFRRS"
  • association_no should be set to a string value that is the TFRRS ID or temporary ID
  • The meta table has by default rows for "Association Name" and "Association No" that should be used to define the meet identification info
Setting Import Source

Though not shown in the sample below, we have added import_source (string) and import_id (string) fields to these tables: divisions, events, athletes, teams, and entries. You can use import_id to store your id # or other identification string that makes sense to your system. Use import_source to define your system so you know the import_id is your own.

We need to be sure to be consistent here, so use the following (use this document as the official reference for all valid sources):

  • MileSplit
  • Athletic.net
  • DirectAthletics
  • RunnerCard
  • CoachO
  • Meetz
  • RBJ
  • Hytek
  • RunScore
  • Lynx
  • TrackMate
  • EZMeets
  • Sydex
Sample File

The document below is an actual working file that could be imported into RaceTab if you wanted to. But it's main purpose is to be a working, well-documented example file to help you implement this format to download from your web site or application into RaceTab.
Any table and field in the RaceTab database strucutre is accessible via this same format... not just the ones in the sample document below. All fields shown below are required for that table, unless otherwise noted.
--------------------------------
# RACETAB RTQL FORMAT
#
# This working import file serves as the documentation for developers who seek to create a program or web site that integrates with RaceTab 3.
#
# Created by Jason Byrne, 12/30/2009
# Last Updated 3/29/2010
#
# ***** REQUIRED TABLES ********
# These tables should pretty much always be included when importing a new meet.
# There are fields not listed, but probably you won't ever need them. See the full database structure document for those.
meta
# This table defines the properties of the meet, RaceTab is looking for specific property names... so it does no good to invent them, except if intended for processing by another app
# name and value fields are required and are the only ones applicable to this table.
# Defining a full list of these properties is out of the scope of this document (at least for now).
{ name="Meet Name"; value="Anywhere Invitational" }
{ name="Start Date"; value="12/30/2009" }
{ name="End Date"; value="12/30/2009" }
{ name="Sport"; value="Outdoor" }
# For Scoring Style, in general, use 1 for standard Cross Country, 7 for standard Track & Field, and 0 for Road Racing or no team scoring
{ name="Scoring Style"; value="7" }
{ name="Venue Name"; value="Showalter Field" }
{ name="City"; value="Winter Park" }
{ name="State"; value="FL" }
{ name="Country"; value="USA" }
divisions
# Divisions are used to classify events and at least one is required since events MUST be in a division
# Additional fields: male_name (string), female_name (string), mixed_name (string) <-- This define the default for what the genders will be referred to, like "Boys" or "Men" # Additional fields: age_min (integer), age_max (integer) <-- Default is 0 and 99 { id=1; name="Varsity" } { id=2; name="Junior Varsity" } events # You may leave out division_id, gender, and is_relay as I did for a couple of the events below... if you do it will "guess" at these from the event's name and use division_id=1
# Additional fields: number (integer) <-- The event number (default is the same as id) # Additional fields: group_id (integer), parent_event_id (integer) # Additional fields: genre (integer) <-- By default it guesses by the event name; Run=1, Dash=2, Hurdles=3, Relay=4, Field=5, Steeplechase=6, Multi=7, Racewalk=8, Other=9 # Additional fields: is_scored (boolean), is_metric (boolean) # Additional fields: time_precision (integer) <-- Number of decimals, 2 is default # Additional fields: note (string) # For a mixed/co-ed event, use "C" as the gender { id=1; name="Varsity Boys 100 Meter Dash"; division_id=1; gender="M"; is_relay=0 } { id=2; name="Varsity Girls 100 Meter Dash" } { id=3; name="Varsity Boys 4x100 Meter Relay" } { id=4; name="Varsity Girls 4x100 Meter Relay"; division_id=1; gender="M"; is_relay=1 } teams # Additional fields: address (string), city (string), state (string), zip (string), country (string), county (string) # Additional fields: contact_name (string), phone (string), fax (string), email (string) # Additional fields: is_paid (boolean), paid_note (string) # Additional fields: is_scored (boolean) # Additional fields: association_name (string), association_no (string) <-- Used for USATF, TFRRS or other ids { id=1; name="Unattached"; abbreviation="UNA" } { id=2; name="Sebring High School"; abbreviation="SEB" } athletes # Additional fields: bib_no (integer) <-- Should be unique, but NOT enforced... 0 is default, which would mean undefined to RaceTab # Additional fields: mname (string) <-- Middle name or initial # Additional fields: group_id (integer), wave_id (integer) # Additional fields: grade (string) <-- Can be numeric or text... your preference # Additional fields: birthdate (string) <-- Please use a standard format such as MM/DD/YYYY # Additional fields: age (integer) <-- 0 is default which means undefined to RaceTab # Additional fields: is_paid (boolean), paid_note (string) # Additional fields: tshirt_size (string, tshirt_color (string) # Additional fields: address (string), city (string), state (string), zip (string), country (string), phone (string), fax (string), email (string), coach_name (string) # Additional fields: note (string) # Additional fields: association_name (string), association_no (string) <-- Used for USATF, TFRRS or other ids { id=1; fname="Jason"; lname="Byrne"; gender="M"; team_id=2 } { id=2; fname="Jonathan"; lname="Doe"; gender="M"; team_id=1 } { id=3; fname="Keith"; lname="Stahl"; gender="M"; team_id=2 } { id=4; fname="Tray"; lname="Hill"; gender="M"; team_id=2 } { id=5; fname="Ryan"; lname="Snodgrass"; gender="M"; team_id=2 } entries # For individual events, athlete_id is required... team_id and team_letter should be left out # For running relays, team_id and team_letter are required... athlete_id should be left out # For purposes of this table, treat field relays as individual events # seed_time is listed below but is OPTIONAL # Additional fields: round (integer) <-- If left off, defaults to 1 # Additional fields: heat (integer) <-- If seeding in your program, 0 is default, which means undefined # Additional fields: lane (integer) <-- If seeding in your program, 0 is default, which means undefined # Additional fields: prediction_time (integer), start_time (integer) # Additional fields: note (string) { id=1; event_id=1; athlete_id=1; seed_time=11150 } { id=2; event_id=3; team_id=2; team_letter="A"; seed_time=43500 } # ***** OPTIONAL TABLES ******** # These tables are optional. # Note all but relay_legs are commented out in this document. # There are additional tables (and fields) not listed, but probably you won't ever need them. See the full database structure document for those.
relay_legs
# All fields below should be considred required
# number is the number on their relay team... so typically 1-4... but you can also specify higher numbers for alternates (as many as you want)
{ entry_id=2; number=1; athlete_id=1 }
{ entry_id=2; number=2; athlete_id=3 }
{ entry_id=2; number=3; athlete_id=4 }
{ entry_id=2; number=4; athlete_id=5 }
# classifications
# - Used to classify athletes... an athlete can be in multiple classifications... this is typically for disabilities like paralympics, but could also be used for other things
# Fields: id (integer), category (string), type (string), name (string) REQUIRED, description (string)
# athlete_to_classification
# - Grouping an athlete to its classifications
# Fields: athlete_id (integer), classification_id (integer)
# event_to_classification
# - Grouping an event to its classifications... not currently used but might be later
# Fields: event_id (integer), classification_id (integer)
# groups
# - Used to classify athletes... an athlete can be in ONE group... this is typically for things like age group (especially for road races)
# Fields: id (integer), category (string), type (string), name (string) REQUIRED, description (string)
# leagues
# - Used to classify teams... a team can be in multiple leagues, this could be NCAA D2, Conferences, etc to separate these teams into their own "sub meet"
# Fields: id (integer), name (string)
# team_to_league
# - Grouping a team to its leagues
# Fields: team_id (integer), league_id (integer)
# waves
# - If this is a road race with multiple waves... athletes are put into their wave
# Fields: id (integer), name (string), time_offset (integer)
# chips
# - Assigning chips to athletes OR just defining what chips we have
# Fields: athlete_id (integer) <-- Assigns a chip to a given athlete # Fields: bib_no (integer) <-- Defines the chip... this is the visible number on the chip... typically this is the same as the athlete's bib no
# Fields: chip_rfid (string) <-- Defines the chip... used if there is some internal complex id code used to identify the chip by the reader, rather than the visible number # Additional Fields: is_returned (boolean) <-- Default is false (0)... has the chip been returned (after the race) # distribution_list # - Sets who wants to receive copies of the results or updates from the meet when the meet director chooses to send them out # Required Fields: name (string), email_address (string) # Additional Fields: send_results (boolean), send_summary (boolean), send_updates (boolean), alert_type (integer) # heat_data # Fields: event_id (integer), round (integer), heat (integer), wind_reading (string), wind_units (string), bar_heights (string), is_fat (boolean), note (string) # Fields: start_time (string) <-- this is a datetime # photos (not yet used) # Fields: name (string), caption (string), data (blob), width (integer), height (integer), event_id (integer), round (integer), heat (integer), timestamp (string) # Fields: filetype (string), athlete_id (integer) # rounds # This is used to define how many rounds there are for an event... by default it will create one round for an event if you leave this out # Fields: event_id (integer) <-- REQUIRED!!! # Fields: number (integer) <-- 1, 2, 3, 4 are accepable; should only be one round with a given number per event and if there is a 4 there better be a 1,2,3 also! # Fields: num_heats (integer) <-- Number of heats in this round # Fields: start_time (string) <-- datetime # Additional Fields: is_seeded (boolean), is_complete (boolean) # splits # Used to record split times recorded within an event # Fields: event_id (integer), round (integer), heat (integer), bib_no (integer), split_time (integer) # Optional Field: reader_id (string) <-- a value defining which scanning device, in the case of chips, picked up the time # timing_sessions # These are raw times recorded by a timing device (or in our case the built-in stopwatch) that can be later applied to a certain event/round/heat # Fields: name (string) <-- Give a name to identify it... maybe just the timestamp when it was recorded # Fields: times (string) <-- one time per line... times can be recorded in a readable format such as 5:34.50 and it will be turned into the appropriate integer value by RaceTab

 

 

 

 

 

 

 

Comment

You need to be a member of RaceTab to add comments!

Join RaceTab

© 2017   Created by Jason Byrne.   Powered by

Badges  |  Report an Issue  |  Terms of Service