51 lines
1.5 KiB
SQL
51 lines
1.5 KiB
SQL
CREATE TABLE
|
|
users (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
bike_id INTEGER NOT NULL REFERENCES bikes (id) ON DELETE CASCADE DEFAULT '1',
|
|
);
|
|
|
|
CREATE TABLE
|
|
workouts (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW ()
|
|
);
|
|
|
|
CREATE TABLE
|
|
cadence_readings (
|
|
id SERIAL PRIMARY KEY,
|
|
workout_id INTEGER NOT NULL REFERENCES workouts (id) ON DELETE CASCADE,
|
|
created_at TIMESTAMP NOT NULL,
|
|
rpm INTEGER NOT NULL,
|
|
distance NUMERIC NOT NULL DEFAULT '0',
|
|
power NUMERIC NOT NULL DEFAULT '0',
|
|
speed NUMERIC NOT NULL DEFAULT '0',
|
|
calories NUMERIC NOT NULL DEFAULT '0',
|
|
CONSTRAINT unique_cadence_reading_per_workout_time UNIQUE (workout_id, created_at)
|
|
);
|
|
|
|
CREATE TABLE
|
|
heartrate_readings (
|
|
id SERIAL PRIMARY KEY,
|
|
workout_id INTEGER NOT NULL REFERENCES workouts (id) ON DELETE CASCADE,
|
|
created_at TIMESTAMP NOT NULL,
|
|
bpm INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE
|
|
bikes (
|
|
id SERIAL PRIMARY KEY,
|
|
display_name VARCHAR(255) NOT NULL,
|
|
code_name VARCHAR(255) NOT NULL
|
|
);
|
|
|
|
INSERT INTO
|
|
bikes (code_name, display_name)
|
|
VALUES
|
|
('ad6', 'AirDyne6'),
|
|
('aab', 'Assault Air Bike'),
|
|
('echo', 'Echo Bike'),
|
|
('bikeergDamper10', 'BikeErg D10'),
|
|
('bikeergDamper5', 'BikeErg D5'),
|
|
('bikeergDamper1', 'BikeErg D1'); |