225 lines
9.2 KiB
SQL
225 lines
9.2 KiB
SQL
-- ============================================================
|
|
-- Gym Tracker — SQLite schema v2
|
|
-- Гибкие справочники + мультиклиентская схема
|
|
-- ============================================================
|
|
|
|
PRAGMA foreign_keys = ON;
|
|
PRAGMA journal_mode = WAL;
|
|
|
|
-- ============================================================
|
|
-- 1. КЛИЕНТЫ (люди, чьи тренировки ведём)
|
|
-- ============================================================
|
|
CREATE TABLE clients (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
sync_id TEXT NOT NULL UNIQUE, -- UUID для синхронизации
|
|
full_name TEXT NOT NULL,
|
|
birth_date TEXT, -- ISO8601 (YYYY-MM-DD)
|
|
goal TEXT DEFAULT '', -- цель тренировок
|
|
notes TEXT DEFAULT '',
|
|
is_active INTEGER NOT NULL DEFAULT 1,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
|
|
);
|
|
|
|
CREATE INDEX idx_clients_name ON clients(full_name);
|
|
CREATE INDEX idx_clients_active ON clients(is_active);
|
|
|
|
-- ============================================================
|
|
-- 2. СПРАВОЧНИК ТИПОВ ТРЕНИРОВОК
|
|
-- ============================================================
|
|
CREATE TABLE workout_types (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
sync_id TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL UNIQUE,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
|
|
);
|
|
|
|
-- ============================================================
|
|
-- 3. СПРАВОЧНИК УПРАЖНЕНИЙ
|
|
-- ============================================================
|
|
CREATE TABLE exercises (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
sync_id TEXT NOT NULL UNIQUE,
|
|
workout_type_id INTEGER, -- FK на workout_types
|
|
name TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
|
|
FOREIGN KEY (workout_type_id) REFERENCES workout_types(id) ON DELETE SET NULL
|
|
);
|
|
|
|
CREATE INDEX idx_exercises_name ON exercises(name);
|
|
CREATE INDEX idx_exercises_type ON exercises(workout_type_id);
|
|
|
|
-- ============================================================
|
|
-- 4. ТРЕНИРОВКИ
|
|
-- ============================================================
|
|
CREATE TABLE workouts (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
sync_id TEXT NOT NULL UNIQUE,
|
|
client_id INTEGER, -- FK на clients
|
|
workout_type_id INTEGER, -- FK на workout_types
|
|
start_time TEXT NOT NULL, -- ISO8601
|
|
end_time TEXT, -- NULL = активная
|
|
notes TEXT DEFAULT '',
|
|
synced INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
|
|
FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (workout_type_id) REFERENCES workout_types(id) ON DELETE SET NULL
|
|
);
|
|
|
|
CREATE INDEX idx_workouts_client ON workouts(client_id);
|
|
CREATE INDEX idx_workouts_start ON workouts(start_time);
|
|
CREATE INDEX idx_workouts_synced ON workouts(synced);
|
|
|
|
-- ============================================================
|
|
-- 5. УПРАЖНЕНИЯ ВНУТРИ ТРЕНИРОВКИ
|
|
-- ============================================================
|
|
CREATE TABLE workout_exercises (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
sync_id TEXT NOT NULL UNIQUE,
|
|
workout_id INTEGER NOT NULL,
|
|
exercise_id INTEGER, -- FK на exercises (может быть NULL)
|
|
exercise_name TEXT NOT NULL, -- дублируем для истории
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
|
|
FOREIGN KEY (workout_id) REFERENCES workouts(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (exercise_id) REFERENCES exercises(id) ON DELETE SET NULL
|
|
);
|
|
|
|
CREATE INDEX idx_wex_workout ON workout_exercises(workout_id);
|
|
CREATE INDEX idx_wex_exercise ON workout_exercises(exercise_id);
|
|
CREATE INDEX idx_wex_name ON workout_exercises(exercise_name);
|
|
|
|
-- ============================================================
|
|
-- 6. ПОДХОДЫ
|
|
-- ============================================================
|
|
CREATE TABLE sets (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
sync_id TEXT NOT NULL UNIQUE,
|
|
workout_exercise_id INTEGER NOT NULL,
|
|
is_dropset INTEGER NOT NULL DEFAULT 0,
|
|
weight REAL,
|
|
reps INTEGER,
|
|
rating INTEGER CHECK (rating BETWEEN 1 AND 5),
|
|
notes TEXT DEFAULT '',
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
timestamp TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
|
|
FOREIGN KEY (workout_exercise_id) REFERENCES workout_exercises(id) ON DELETE CASCADE,
|
|
|
|
CHECK (
|
|
(is_dropset = 0 AND weight IS NOT NULL AND reps IS NOT NULL) OR
|
|
(is_dropset = 1 AND weight IS NULL AND reps IS NULL)
|
|
)
|
|
);
|
|
|
|
CREATE INDEX idx_sets_wex ON sets(workout_exercise_id);
|
|
CREATE INDEX idx_sets_timestamp ON sets(timestamp);
|
|
|
|
-- ============================================================
|
|
-- 7. ЭЛЕМЕНТЫ ДРОП-СЕТА
|
|
-- ============================================================
|
|
CREATE TABLE dropset_items (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
set_id INTEGER NOT NULL,
|
|
weight REAL NOT NULL,
|
|
reps INTEGER NOT NULL,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
|
|
FOREIGN KEY (set_id) REFERENCES sets(id) ON DELETE CASCADE,
|
|
CHECK (weight > 0 AND reps > 0)
|
|
);
|
|
|
|
CREATE INDEX idx_dropset_set ON dropset_items(set_id);
|
|
|
|
-- ============================================================
|
|
-- 8. КАРДИО
|
|
-- ============================================================
|
|
CREATE TABLE cardio (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
sync_id TEXT NOT NULL UNIQUE,
|
|
workout_id INTEGER NOT NULL,
|
|
cardio_type TEXT NOT NULL CHECK (cardio_type IN (
|
|
'вело', 'ходьба', 'бег', 'эллипс', 'гребля'
|
|
)),
|
|
purpose TEXT NOT NULL CHECK (purpose IN (
|
|
'warmup', 'cooldown', 'separate'
|
|
)),
|
|
duration INTEGER,
|
|
distance REAL,
|
|
speed REAL,
|
|
incline REAL,
|
|
resistance INTEGER,
|
|
notes TEXT DEFAULT '',
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
|
|
FOREIGN KEY (workout_id) REFERENCES workouts(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX idx_cardio_workout ON cardio(workout_id);
|
|
CREATE INDEX idx_cardio_purpose ON cardio(purpose);
|
|
|
|
-- ============================================================
|
|
-- 9. СПРАВОЧНИК ТИПОВ ЗАМЕРОВ ТЕЛА
|
|
-- ============================================================
|
|
CREATE TABLE metric_types (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
sync_id TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL UNIQUE, -- "Вес", "Грудь", "Бицепс", "Предплечье"...
|
|
unit TEXT NOT NULL DEFAULT 'см', -- единица измерения
|
|
category TEXT DEFAULT 'body', -- для группировки: body, strength, etc.
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
|
|
);
|
|
|
|
-- ============================================================
|
|
-- 10. ЗАМЕРЫ ТЕЛА (факт замера)
|
|
-- ============================================================
|
|
CREATE TABLE body_metrics (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
sync_id TEXT NOT NULL UNIQUE,
|
|
client_id INTEGER, -- FK на clients
|
|
metric_date TEXT NOT NULL, -- ISO8601
|
|
notes TEXT DEFAULT '',
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
|
|
FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL
|
|
);
|
|
|
|
CREATE INDEX idx_body_metrics_client ON body_metrics(client_id);
|
|
CREATE INDEX idx_body_metrics_date ON body_metrics(metric_date);
|
|
|
|
-- ============================================================
|
|
-- 11. ЗНАЧЕНИЯ ЗАМЕРОВ (нормализованно)
|
|
-- ============================================================
|
|
CREATE TABLE body_metric_values (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
body_metric_id INTEGER NOT NULL,
|
|
metric_type_id INTEGER NOT NULL,
|
|
value REAL NOT NULL,
|
|
|
|
FOREIGN KEY (body_metric_id) REFERENCES body_metrics(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (metric_type_id) REFERENCES metric_types(id) ON DELETE CASCADE,
|
|
|
|
UNIQUE (body_metric_id, metric_type_id) -- один тип замера — одно значение в замере
|
|
);
|
|
|
|
CREATE INDEX idx_bmv_metric ON body_metric_values(body_metric_id);
|
|
CREATE INDEX idx_bmv_type ON body_metric_values(metric_type_id);
|
|
|
|
-- ============================================================
|
|
-- 12. ЛОГ СИНХРОНИЗАЦИИ
|
|
-- ============================================================
|
|
CREATE TABLE sync_log (
|
|
device_id TEXT PRIMARY KEY,
|
|
last_sync TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
|
|
);
|