The results database

Each tracking experiment produces one SQLite file (.db). Current ethoscopes write it directly; on ethoscopes with the older MariaDB images, the node built the SQLite file from the ethoscope’s database. The files are what ethoscopy and rethomics read for analysis.

On the node, a result file sits at

/ethoscope_data/results/<machine_id>/<ETHOSCOPE_NNN>/<date_time>/<date_time>_<machine_id>.db

Browsing a file

1. Get the .db file onto your computer, for example from the node’s FTP server with a browser or an FTP client such as FileZilla.
2. Install DB Browser for SQLite (on Arch-based systems, sudo pacman -S sqlitebrowser).
3. Open the file, select the Browse Data tab and pick a table from the list below it.

The main tables:

• METADATA: the settings of the experiment: user, location, and the parameters given to the stimulator (for example for sleep deprivation).
• ROI_1, ROI_2, …: the tracking data of each region of interest: time t, position x, y, size w, h and angle phi. ROI_MAP gives the position of each region in the image and VAR_MAP describes the variables.
• CSV_DAM_ACTIVITY: a copy of the data in the format of DAM activity monitors, with human-readable times.
• IMG_SNAPSHOTS: the periodic camera snapshots. Double-click a BLOB to see the image.

Except in CSV_DAM_ACTIVITY, times are in milliseconds from the start of the experiment: 5250666 is 5250.666 seconds, or 87.5 minutes.

To print the metadata of a file from the command line, where the results are stored:

cd /ethoscope_data/results
# metadata of ethoscope 115 for 21 August 2020
sqlite3 "$(find 115* -type f -name '*.db' | grep 2020-08-21)" "select * from metadata;"

The snapshots can be turned into a time-lapse video: see Videos.

Removing the snapshots before sharing

IMG_SNAPSHOTS takes a lot of space, so you may want to drop it before sharing files:

sqlite3 2018-06-05_15-38-26_004c6ba04e534be486069c3db7b10827.db "DROP TABLE IMG_SNAPSHOTS; VACUUM;"

VACUUM is needed to shrink the file after the DROP. To do this for every .db file in a folder and below:

find . -name "*.db" -exec sh -c 'echo {} && sqlite3 {} "DROP TABLE IMG_SNAPSHOTS; VACUUM;"' \;

Repairs

Caution

Work on a copy of the file, or keep a backup, before changing a database.

Times that restart after appending

In issue #139, the times of data appended to an existing file jump backwards. This finds the jump in each of the 20 regions of interest and adds the last time before it to every later row:

cd /ethoscope_data/results
DB=$(find . -name "*.db" | grep 2021-08-02 | grep ETHOSCOPE_203)   # the affected file
for i in $(seq 1 20); do
  T="ROI_$i"
  J="SELECT g1.id as FID, g1.t as FIT, (g2.t - g1.t) as delta FROM $T g1 INNER JOIN $T g2 on g2.id = g1.id + 1 WHERE delta < 0"
  echo "UPDATE $T SET t = t + (SELECT FIT from ($J)) WHERE id > (SELECT FID FROM ($J));"
done | sqlite3 "$DB"

A missing VAR_MAP table

If the VAR_MAP table is missing, recreate it with:

CREATE TABLE VAR_MAP (
    var_name CHAR(100),
    sql_type CHAR(100),
    functional_type CHAR(100)
);
INSERT INTO "main"."VAR_MAP" ("var_name", "sql_type", "functional_type") VALUES ('h', 'SMALLINT', 'distance');
INSERT INTO "main"."VAR_MAP" ("var_name", "sql_type", "functional_type") VALUES ('has_interacted', 'SMALLINT', 'interaction');
INSERT INTO "main"."VAR_MAP" ("var_name", "sql_type", "functional_type") VALUES ('is_inferred', 'BOOLEAN', 'bool');
INSERT INTO "main"."VAR_MAP" ("var_name", "sql_type", "functional_type") VALUES ('phi', 'SMALLINT', 'angle');
INSERT INTO "main"."VAR_MAP" ("var_name", "sql_type", "functional_type") VALUES ('w', 'SMALLINT', 'distance');
INSERT INTO "main"."VAR_MAP" ("var_name", "sql_type", "functional_type") VALUES ('x', 'SMALLINT', 'distance');
INSERT INTO "main"."VAR_MAP" ("var_name", "sql_type", "functional_type") VALUES ('xy_dist_log10x1000', 'SMALLINT', 'relative_distance_1e6');
INSERT INTO "main"."VAR_MAP" ("var_name", "sql_type", "functional_type") VALUES ('y', 'SMALLINT', 'distance');

For recovering results from the card of an ethoscope that no longer works, see Recovering data from an SD card.