Data are initially saved on the ethoscope in a mariadb SQL database. After every stop, a database dump is also created and stored on the SD card. On the background, the node will interrogate the running SQL database and transfer the data to a SQLlite3 db file which can then be used by rethomics or pythomics for analysis.

To browse through the data:

  1. Identify and download the db file to your computer. If you installed the FTP client on the node, you should use FTP to access those data. In our lab, the FTP is accessible through VPN at ftp://turing.lab.gilest.ro - Firefox can be used to navigate the site or alternatively you can install a proper FTP client like Filezilla.
  2. Make sure your computer has a working copy of the sqlite browser. For windows, you can install it from here. In archlinux, install with sudo pacman -S sqlitebrowswer
  3. Open the db file you've downloaded at point 1. Select the tab named "Browse Data" and then from the dropbox underneath pick the table you want to analyse. To check images, click on IMG_SNAPSHOT. To open the image double click on BLOB.
  4. The table METADATA contains information regarding the experiment settings: user, location, any parameter passed to the interactor modules to control (e.g.) sleep deprivation.
  5. The table CSV_DAM_ACTIVITY contains a DAM data replica with human readable times. For all other tables, times are counted in milliseconds from the start of the experiment. So, if time reads 5250666 that would be 5250.666 seconds or 87.5 minutes.

A full video tutorial of sqlite3 browswer is available in the video below:

https://www.youtube.com/watch?v=2pbL7gTHMRw

A convenient one-liner to interrogate the metadata on the computer where the SQLlite3 db files are stored

#move the where the db files are stored
cd /mnt/data/results

#Example that interrogates ethoscope 115 for date 2020-08-21
sqlite3 `find 115* -type f -name '*.db' | grep 2020-08-21` "select * from metadata;"

Delete the IMG_SNAPSHOTS table

The IMG_SNAPSHOTS table contains the actual images saved during tracking. It takes considerable space so it may be useful to delete this table when sharing the db files with the external world.

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

Note that the VACUUM command is necessary to resize the file after DROP

It is possible to do this in a batch format for all the db files in a folder issuing the following command