Database - nduvieilh/fleet-tracker GitHub Wiki
Structure
Vehicles
The vehicles table contains each vehicle your app is synced with.
| name | required | type (traditional) | comments |
|---|---|---|---|
| id | yes | INTEGER (INT) | Primary Key |
| name | yes | TEXT (VARCHAR) | User Friendly name associated with vehicle |
| vin | no | TEXT (VARCHAR) | VIN can be used to auto grab data (Mode 09, PID 02, bytes 17-20) |
| make | no | TEXT (VARCHAR) | Vehicle make |
| model | no | TEXT (VARCHAR) | Vehicle model |
| year | no | TEXT (VARCHAR) | Vehicle year |
| color | no | TEXT (VARCHAR) | Vehicle color |
| plate | no | TEXT (VARCHAR) | Used to easily identify car from dispatch |
| state | no | TEXT (VARCHAR) | State associated with plate registration |
| active | yes | NUMERIC (BOOLEAN) | Determines if a vehicle has been soft-deleted |
| created | yes | NUMERIC (DATETIME) | Date and time of created |
| modified | yes | NUMERIC (DATETIME) | Date and time of last modified |
| deleted | no | NUMERIC (DATETIME) | Date and time of deleted |
Trips
The trips table is used to join with position, temps, and other associated tables.
| name | required | type (traditional) | comments |
|---|---|---|---|
| id | yes | INTEGER (INT) | Primary key |
| vehicle_id | yes | INTEGER (INT) | Foreign key linking to vehicles table |
| start_time | no | NUMERIC (DATETIME) | Date and time when the trip started |
| end_time | no | NUMERIC (DATETIME) | Date and time when the trip ended |
| synced | no | NUMERIC (BOOLEAN) | Has this trip data been synced with a remote server |
| view_count | yes | INTEGER (INT) | How many times has this trip been reviewed (locally) |
| active | yes | NUMERIC (BOOLEAN) | Determines if a trip has been soft-deleted |
| created | yes | NUMERIC (DATETIME) | Date and time of created |
| modified | yes | NUMERIC (DATETIME) | Date and time of last modified |
| deleted | no | NUMERIC (DATETIME) | Date and time of deleted |
Position
The position table stores the current position of the device using the android location API. This table is updated most frequently to get the most resolution out of the position, gyro, and accelerometer data.
Note: If data did not change since the last created date then a new entry will be ignored to save device storage space.
| name | required | type (traditional) | comments |
|---|---|---|---|
| id | yes | INTEGER (INT) | Primary key |
| trip_id | yes | INTEGER (INT) | Foreign key to trips table |
| lat | no | REAL (DOUBLE) | Latitudinal position on earth from GPS in degrees |
| lon | no | REAL (DOUBLE) | Longitudinal position on earth from GPS in degrees |
| bearing | no | REAL (FLOAT) | Bearing (direction) from GPS in degrees |
| altitude | no | REAL (DOUBLE) | Altitude (height) in from GPS in meters |
| accuracy | no | REAL (FLOAT) | Estimated accuracy from GPS in meters |
| speed_gps | no | REAL (FLOAT) | Speed over ground from GPS in meters/second |
| satellites | no | INTEGER (INT) | Number of currently connected to satellites |
| accel_x | no | REAL (FLOAT) | Acceleration in the x dimension in m/s2 |
| accel_y | no | REAL (FLOAT) | Acceleration in the y dimension in m/s2 |
| accel_z | no | REAL (FLOAT) | Acceleration in the z dimension in m/s2 |
| gyro_x | no | REAL (FLOAT) | Vector of rotation in the x direction in rad/s |
| gyro_y | no | REAL (FLOAT) | Vector of rotation in the y direction in rad/s |
| gyro_z | no | REAL (FLOAT) | Vector of rotation in the z direction in rad/s |
| created | yes | NUMERIC (DATETIME) | Date and time of created |
Temps
The temps table stores different temperatures of the vehicle (if available) to keep track of temperature spikes and to diagnose issues with proper cooling. For an entry to be made at least one of the temperatures must be set.
Note: If data did not change since the last created date then a new entry will be ignored to save device storage space.
| name | required | type (traditional) | comments | PID (mode 01) | Bytes |
|---|---|---|---|---|---|
| id | yes | INTEGER (INT) | Primary key | ||
| trip_id | yes | INTEGER (INT) | Foreign key to trips table | ||
| engine_coolant | no | REAL (FLOAT) | -40 - 215 * C | 05 (67) | 1 (3) |
| intake_air | no | REAL (FLOAT) | -40 - 215 * C | 0F (68) | 1 (7) |
| catalyst_temp | no | REAL (FLOAT) | Average of all sensors (max 4) | 3C, 3D, 3E, 3F | 2 each |
| ambient_air | no | REAL (FLOAT) | -40 - 215 * C | 46 | 1 |
| engine_oil | no | REAL (FLOAT) | -40 - 210 * C | 5C | 1 |
| turbocharger | no | REAL (FLOAT) | Average of all sensors (max 2) | 75 (76) | 7 (7) |
| created | yes | NUMERIC (DATETIME) | Date and time of created |
Vitals
The vitals table stores various information on engine vitals such as torque, load, and horsepower. For an entry to be made one of the values must be non-zero.
Note: If data did not change since the last created date then a new entry will be ignored to save device storage space.
| name | required | type (traditional) | comments | PID (mode 01) | Bytes |
|---|---|---|---|---|---|
| id | yes | INTEGER (INT) | Primary key | ||
| trip_id | yes | INTEGER (INT) | Foreign key to trips table | ||
| engine_rpm | no | REAL (FLOAT) | 0 - 16,383.75 | 0C | 2 |
| speed_obd | no | REAL (FLOAT) | 0 - 255 km/h | 0D | 1 |
| air_flow_rate | no | REAL (FLOAT) | 0 - 655.35 gram/sec | 10 | 2 |
| throttle_position | no | INTEGER (INT) | 0 - 100 % | 11 | 1 |
| load | no | REAL (FLOAT) | 0 - 25,700 % | 43 | 2 |
| torque | no | REAL (FLOAT) | 0 - 65,535 Nm | 63 | 2 |
| boost_pressure | no | REAL (FLOAT) | kPa | 70 | 9 |
| turbo_rpm | no | REAL (FLOAT) | rpm | 74 | 5 |
| horsepower | no | REAL (FLOAT) | (torque * rpm) / 5252 | ||
| fuel_rate | no | REAL (FLOAT) | 0 - 3212.75 L/h | 5E | 2 |
| created | yes | NUMERIC (DATETIME) | Date and time of created |
Tables creation SQL:
CREATE TABLE "vehicles" (
`id` INTEGER PRIMARY KEY,
`name` TEXT NOT NULL,
`vin` TEXT UNIQUE,
`make` TEXT,
`model` TEXT,
`year` TEXT,
`color` TEXT,
`plate` TEXT,
`state` TEXT,
`active` NUMERIC NOT NULL DEFAULT 1,
`created` NUMERIC NOT NULL,
`modified` NUMERIC NOT NULL,
`deleted` NUMERIC
);
CREATE TABLE "trips" (
`id` INTEGER PRIMARY KEY,
`vehicle_id` INTEGER NOT NULL,
`start_time` NUMERIC,
`end_time` NUMERIC,
`synced` NUMERIC NOT NULL DEFAULT 0,
`view_count` INTEGER DEFAULT 0,
`active` NUMERIC NOT NULL DEFAULT 1,
`created` NUMERIC NOT NULL,
`modified` NUMERIC NOT NULL,
`deleted` NUMERIC,
FOREIGN KEY(`vehicle_id`) REFERENCES `vehicle`(`id`)
);
CREATE TABLE "vitals" (
`id` INTEGER PRIMARY KEY,
`trip_id` INTEGER NOT NULL,
`engine_rpm` REAL,
`speed_obd` REAL,
`air_flow_rate` REAL,
`throttle_position` INTEGER,
`load` REAL,
`torque` REAL,
`boost_pressure` REAL,
`turbo_rpm` BLOB,
`horsepower` REAL,
`fuel_rate` REAL,
`created` NUMERIC NOT NULL,
FOREIGN KEY(`trip_id`) REFERENCES `trips`(`id`)
);
CREATE TABLE "positions" (
`id` INTEGER PRIMARY KEY,
`trip_id` INTEGER NOT NULL,
`lat` REAL,
`lon` REAL,
`bearing` REAL,
`altitude` REAL,
`accuracy` REAL,
`speed` REAL,
`satellites` INTEGER,
`accel_x` REAL,
`accel_y` REAL,
`accel_z` REAL,
`gyro_x` REAL,
`gyro_y` REAL,
`gyro_z` REAL,
`created` NUMERIC NOT NULL,
FOREIGN KEY(`trip_id`) REFERENCES `trips`(`id`)
);
CREATE TABLE "temps" (
`id` INTEGER PRIMARY KEY,
`trip_id` INTEGER NOT NULL,
`engine_coolant` REAL,
`intake_air` REAL,
`catalyst` REAL,
`ambient_air` REAL,
`engine_oil` REAL,
`turbocharger` REAL,
`created` NUMERIC NOT NULL,
FOREIGN KEY(`trip_id`) REFERENCES `trips`(`id`)
);