LMT SQLite DataBase Documentation - fdechaumont/micecraft GitHub Wiki
This document is focused on the SQLite database created by a LMT experiment. It includes table purposes, field descriptions, data types, and relationships.
Table of Contents
Database tables
The database consists of seven main tables:
- ANIMAL: stores information about animals
- DETECTION: contains frame-by-frame detection data for each animal
- EVENT: represents events involving one or more animals over frame ranges
- FRAME: stores metadata for individual frames
- LOG: tracks processing logs and database rebuild
- RFIDEVENT: not currently used by LMT
- sqlite_sequence: automatically created for any SQLite database
ANIMAL
This table stores the global information for each animals like database identification (ID), RFID number, genotype and name. ID is used in DETECTION and EVENT tables to reference specific animals.
ANIMAL table structure
| Column | Type | Description |
|---|---|---|
| ID | INTEGER | primary key, autoincrement |
| RFID | TEXT | RFID number of the animal |
| GENOTYPE | TEXT | genotype of the animal |
| NAME | TEXT | name or label of the animal |
Other column can be add by users to store more information about their animals and experiment.
DETECTION
This table stores detection data for each animal and for each frame. It includes 3D position of the mass center (MASS), of the head (FRONT), and of the tail (BACK). The BACK fields do not indicate precise position of a specific tail parts, the detection is not sufficient for a specific tail part consistent detection. REARING, LOOK_UP and LOOK_DOWN indicate the behaviour of the animal but it is preferable to use information from EVENT table than this one. DATA field store those informations with additional ones that are encoded in xml format.
[!CAUTION] TODO explanation of xml format reading
DETECTION table structure
| Column | Type | Description |
|---|---|---|
| ID | INTEGER | primary key, autoincrement |
| FRAMENUMBER | INTEGER | frame ID |
| ANIMALID | INTEGER | detected animal ID |
| MASS_X / Y / Z | REAL | mass-center coordinates |
| FRONT_X / Y / Z | REAL | animal's head coordinates |
| BACK_X / Y / Z | REAL | animal's tail coordinates |
| REARING | INTEGER | boolean flag for rearing |
| LOOK_UP | INTEGER | boolean flag for looking up |
| LOOK_DOWN | INTEGER | boolean flag for looking down |
| DATA | TEXT | additional xml encoded data |
EVENT
The main tables of the LMT system. It stores all the events involving one or multiple animals (e.g. interactions, behaviours, RFID reading). IDANIMALA is the main animal of this event. If it is a single event, only this column is filled, the other 3 (B, C and D) are set to None. If it is a 3 animals event, the column IDANIMALA, B and C will have an animal ID (all different obviously). As an example: if an animal is breaking a group of 3. The ID of the animal that breaks the group is in IDANIMALA. The other two that are still together as a group are in IDANIMALB and IDANIMALC. In addition, those ID are in the order of the event name. If you have contact oral genital it means it is the oral of IDANIMALA and the genital of IDANIMALB, same for follow and other similar events.
EVENT table structure
| Column | Type | Description |
|---|---|---|
| ID | INTEGER | primary key, autoincrement |
| NAME | TEXT | event name |
| DESCRIPTION | TEXT | detailed description |
| STARTFRAME | INTEGER | first frame ID of the event |
| ENDFRAME | INTEGER | last frame ID of the event |
| IDANIMALA | INTEGER | ID of main animal |
| IDANIMALB | INTEGER | ID of next animal involved (if any) |
| IDANIMALC | INTEGER | ID of next animal involved (if any) |
| IDANIMALD | INTEGER | ID of next animal involved (if any) |
| METADATA | TEXT | additional metadata |
FRAME
This table stores the time and the number of detected animals for each frame. LMT works at 30 frames per second (30 fps). PAUSED indicate if the system was paused by the user at this frame.
[!IMPORTANT] Timestamps (in milliseconds) are stored as reference time (so as UTC+0 time). You must convert it to your local time zone to get the correct experiment time. Example: if you are in Paris during the winter (winter time), you must convert the timestamp UTC+0 to UTC+1, so you must add 1 hour.
[!CAUTION] If you do not have 30 fps for your LMT experiment, it might affect your analysis.
FRAME table structure
| Column | Type | Description |
|---|---|---|
| ID | INTEGER | primary key, autoincrement |
| FRAMENUMBER | INTEGER | frame ID |
| TIMESTAMP | INTEGER | timestamp (in Epoch) associated with the frame (UTC+0) |
| NUMPARTICLE | INTEGER | number of animals detected |
| PAUSED | INTEGER | boolean flag for paused/active state (user action on system) |
[!TIP] Here are some ways to convert TIMESTAMP in milliseconds to a DATE format in the reference time zone (UTC+0).
Excel formula:
= (TIMESTAMP / 1000) / (24*60*60) + DATE(1970,1,1)SQL query:
SELECT DATETIME('1970-01-01', '+' || (TIMESTAMP / 1000) || ' seconds') FROM FRAME ORDER BY FRAMENUMBER ASC
LOG
Contains lot of information. Those informations are not relevant for the common user, but it might become handy to check them for debugging.
LOG table structure
| Column | Type | Description |
|---|---|---|
| ID | INTEGER | primary key, autoincrement |
| PROCESS | TEXT | name of the process executed |
| VERSION | TEXT | LMT version when log was recorded |
| DATE | TEXT | ISO 8601 date-time format (YYYY-MM-DD HH:MM:SS) |
| TMIN | INTEGER | Frame number if relevant |
| TMAX | INTEGER | Frame number if relevant |
RFIDEVENT
This tables is currently not used by LMT.
RFIDEVENT table structure
| Column | Type | Description |
|---|---|---|
| ID | INTEGER | primary key, autoincrement |
| RFID | - | not used |
| TIME | - | not used |
| X | - | not used |
| Y | - | not used |
sqlite_sequence
This table is automatically created by SQLite to keep track of the largest ROWID for tables with AUTOINCREMENT primary keys. It is not directly manipulated by LMT.
sqlite_sequence table structure
| Column | Type | Description |
|---|---|---|
| name | TEXT | name of the table |
| seq | INTEGER | largest ROWID used in that table |
Relationships Summary
DETECTION.ANIMALID→ANIMAL.IDDETECTION.FRAMENUMBER→FRAME.FRAMENUMBER(many detections per frame)EVENT.STARTFRAME/ENDFRAME→FRAME.FRAMENUMBER(events span multiple frames)EVENT.IDANIMALA/B/C/D→ANIMAL.ID(events can involve multiple animals)
SQL queries examples
Here you can find some queries asked by users and their results (if provided) using the example dataset available on the LMT website.
Count the number of a specific EVENT
This count the number of "Oral-oral Contact" event inside the database.
SELECT COUNT(NAME) AS number_of_events
FROM EVENT
WHERE NAME = 'Oral-oral Contact';
Distinct EVENT NAME
You can get the list of event names stored in the EVENT table by executing the following SQL query:
SELECT DISTINCT NAME FROM EVENT ORDER BY NAME;
NAME of all EVENT in example dataset
| Centered rendering | Centered rendering | Centered rendering | Centered rendering |
|---|---|---|---|
| Approach | Approach contact | Approach rear | Behind |
| Break contact | Center Zone | Contact | Detection |
| Escape | Follow | FollowZone | Get away |
| Group 3 break | Group 3 make | Group 4 break | Group 4 make |
| Group2 | Group3 | Group4 | Head detected |
| Look down | MACHINE LEARNING ASSOCIATION | Move in contact | Move isolated |
| Nest3_ | Nest4_ | Oral-genital Contact | Oral-oral Contact |
| Periphery Zone | RFID ASSIGN ANONYMOUS TRACK | RFID MATCH | RFID MISMATCH |
| Rear at periphery | Rear in centerWindow | Rear in contact | Rear isolated |
| Rearing | SAP | Side by side Contact | Side by side Contact, opposite way |
| Social approach | Social escape | Stop | Stop in contact |
| Stop isolated | Train2 | WallJump | Water Stop |
| Water Zone | badIdentity | badOrientation | badSegmentation |
| coucou | event | manualContact | manualOralGenital |
| manualOralOralContact | manualSideSideOpposite | manualSideSideSame | seq oral geni - oral oral |
| seq oral oral - oral genital |