1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
|
CREATE TABLE IF NOT EXISTS "schema_migrations" (version varchar(255) primary key);
CREATE TABLE IF NOT EXISTS "mountain_passes" (
id integer primary key,
osm_id integer unique not null,
name text,
elevation integer,
latitude real not null,
longitude real not null
, category TEXT NOT NULL DEFAULT 'unknown' CHECK (category IN ('road', 'track', 'path', 'hiking', 'unknown')));
CREATE TABLE IF NOT EXISTS "rides" (
id integer primary key,
external_id text unique not null,
gpx_path text not null,
name text not null,
external_sport_type text not null,
start_date text not null,
distance_m real not null,
external_moving_time_s integer not null,
elapsed_time_s integer not null,
total_elevation_gain_m real not null,
average_speed_mps real not null,
created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
cotacol_score real,
cotacol_algo_version text,
moving_time_s integer,
moving_time_algo_version text,
bike_type TEXT NOT NULL DEFAULT '' CHECK (bike_type IN ('', 'road', 'mtb')),
ride_type TEXT NOT NULL DEFAULT '' CHECK (ride_type IN ('', 'training', 'indoor', 'race', 'transport', 'touring')),
fit_path TEXT NOT NULL DEFAULT ''
);
CREATE TABLE ride_official_climb_attempts (
ride_id integer not null references rides(id),
climb_id integer not null references official_climbs(id),
occurrence integer not null,
distance_m real not null,
elevation_gain_m real not null,
slope_percent real not null,
cotacol real not null,
cotacol_algo_version text not null,
started_at text not null,
ended_at text not null,
created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')), average_power_w real,
primary key (ride_id, climb_id, occurrence)
);
CREATE TABLE IF NOT EXISTS "official_climbs" (
id integer primary key,
name text not null,
start_latitude real not null,
start_longitude real not null,
end_latitude real not null,
end_longitude real not null,
created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
sample_distance_m real not null,
sample_elevation_gain_m real not null
);
-- Dbmate schema migrations
INSERT INTO "schema_migrations" (version) VALUES
('20250802140659'),
('20260802090000'),
('20260804000000'),
('20260807000000'),
('20260813000000'),
('20260820184307'),
('20260821000000'),
('20260823000000'),
('20260827000000'),
('20260828000000'),
('20260829000000'),
('20260831000000'),
('20260913000000'),
('20260924000000'),
('20260925000000'),
('20260925061236');
|
