AIRS Database Tables

AIRS uses an internal SQlite database for storing various tables, most notably the actual recordings made through AIRS. This page details the tables created by AIRS and their content:

airs_values

AIRS_table1

 

This is the central table, holding all recordings that are made through AIRS with each row holding the following values:

  • Timestamp: the time in millisecond since the epoch. This time is taken when the local recording service acquired the current sensor value.
  • Symbol: the sensor symbol that has been recorded. For a list of all supported sensor symbols, refer to this list on this site. For efficiency reasons, we limit the symbols to 2 characters.
  • Value: All recordings are stored as TEXT values. Some sensors, such as the BT sensor, will add ‘\n’ to their readings to form multi-line values. If inserting values into the database that hold the character, you will need to escape this character (best by simply replacing it with a standard quote ).

AIRS allows for indexing this database in DB Admin menu in the Recording tab of AIRS (see here). The index is created over the Timestamp entry in the airs_values table. It is highly advisable to perform this indexing since it greatly improves the performance of retrieving values!

airs_dates

AIRS_table2

 

In order to more quickly determine which days hold any valid recording, AIRS maintains the airs_dates table. Each row holds the following entries:

  • Year: the year of the recording
  • Month: the month of the recording
  • Day: the day of recording
  • Types: the type of recording. This is always 1 for a local AIRS recording. It can be used for other indications that something interesting exists for this day (for instance, our Storica app uses this field to indicate that there are annotations for this day).

airs_sensors_used

AIRS_table3

 

In order to retrieve more quickly that a particular sensor has been used in a particular time range, AIRS also maintains the airs_sensors_used table, where each row holds the following entries:

  • Timestamp: the time in millisecond since the epoch. This time is taken the first time when the sensor is recorded in the current recording (i.e., there is only one entry done per recording). If the current day rolls over midnight, however, AIRS adds another entry into the database with the timestamp after midnight. 
  • Symbol: the sensor symbol that has been recorded. For a list of all supported sensor symbols, refer to this list on this site. For efficiency reasons, we limit the symbols to 2 characters.

Other applications, such as our Storica app, can use this table to determine the sensors used per day (by querying for a timestamp range of a particular day) without needing to mine the much larger airs_values table. This significantly improves day-based querying for, e.g., diaries.

To further increase efficiency, this table is indexed over the Timestamp entry.