colmi_r02_client/tests/database_schema.sql at a16cd13dd6a9a4bd477e10a41db6afd22e5a69e4 · tahnok/colmi_r02_client · GitHub

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
CREATE TABLE rings (
	ring_id INTEGER NOT NULL,
	address VARCHAR NOT NULL,
	PRIMARY KEY (ring_id),
	UNIQUE (address)
)

CREATE TABLE syncs (
	sync_id INTEGER NOT NULL,
	comment VARCHAR,
	ring_id INTEGER NOT NULL,
	timestamp DATETIME NOT NULL,
	PRIMARY KEY (sync_id),
	FOREIGN KEY(ring_id) REFERENCES rings (ring_id)
)

CREATE TABLE sleep_logs (
	sleep_log_id INTEGER NOT NULL,
	date DATETIME NOT NULL,
	ring_id INTEGER NOT NULL,
	sync_id INTEGER NOT NULL,
	sleep_start INTEGER NOT NULL,
	sleep_end INTEGER NOT NULL,
	periods VARCHAR NOT NULL,
	PRIMARY KEY (sleep_log_id),
	UNIQUE (ring_id, date),
	FOREIGN KEY(ring_id) REFERENCES rings (ring_id),
	FOREIGN KEY(sync_id) REFERENCES syncs (sync_id)
)

CREATE TABLE heart_rates (
	heart_rate_id INTEGER NOT NULL,
	reading INTEGER NOT NULL,
	timestamp DATETIME NOT NULL,
	ring_id INTEGER NOT NULL,
	sync_id INTEGER NOT NULL,
	PRIMARY KEY (heart_rate_id),
	UNIQUE (ring_id, timestamp),
	FOREIGN KEY(ring_id) REFERENCES rings (ring_id),
	FOREIGN KEY(sync_id) REFERENCES syncs (sync_id)
)

CREATE TABLE sport_details (
	sport_detail_id INTEGER NOT NULL,
	calories INTEGER NOT NULL,
	steps INTEGER NOT NULL,
	distance INTEGER NOT NULL,
	timestamp DATETIME NOT NULL,
	ring_id INTEGER NOT NULL,
	sync_id INTEGER NOT NULL,
	PRIMARY KEY (sport_detail_id),
	UNIQUE (ring_id, timestamp),
	FOREIGN KEY(ring_id) REFERENCES rings (ring_id),
	FOREIGN KEY(sync_id) REFERENCES syncs (sync_id)
)