Sphery App Datenbank
Miro Visualisierung
Datenbanktabellen
List of relations
Schema | Name | Type | Owner
--------+-----------------+-------+----------------
public | HealthData | table | bydblzeeogvrzo
public | HrValues | table | bydblzeeogvrzo
public | RaceConfigs | table | bydblzeeogvrzo
public | SequelizeMeta | table | bydblzeeogvrzo
public | Sessions | table | bydblzeeogvrzo
public | TimelineMarkers | table | bydblzeeogvrzo
public | Users | table | bydblzeeogvrzo
public | WorkoutPresets | table | bydblzeeogvrzo
public | Workouts | table | bydblzeeogvrzo
Sessions
| Key | Typ | Beschreibung |
|---|---|---|
| sessionId | int | ID der Session (DB index) |
| loginAt | string | Der Zeitstempel des Logins |
| logoutAt | string | Der Zeitstempel des Logouts |
| exercube | string | Die ID des ExerCubes |
| netPromoterScore | int | Bewertung der Session |
Challenges
| Key | Typ | Beschreibung |
|---|---|---|
| challengeId | int | ID der Challenge (DB index) |
| userIdInitiator | int | ID des Herausforderers |
| userIdOpponent | int | ID des Kontrahenten |
| isChallengeComplete | bool | Zeigt an ob die Challenge beendet wurde. |
| challengeWinnerId | int | Die User ID des Gewinners |
| challengeBestOf | int | Maximale Anzahl der Spielrunden |
| challengeWorkouts | [...{<workout>,<user>,int}] | Ein Array, welches das Workout, die ID des Gewinners und die aktuelle Spielrunde abbildet. |
HR Values
| Key | Typ | Beschreibung |
|---|---|---|
| hrValuesId | int | ID der hrValues (DB index) |
| time | float | Sekunden seit beginn des Workouts |
| value | int | HR Value |
WorkoutPresets
| Key | Typ | Beschreibung |
|---|---|---|
| workoutPresetId | int | ID des WorkoutPresets (DB index) |
| workoutPresetName | string | Name des WorkoutPresets (“DualFlow”, “UpperBody”, “LegDay”) |
| isCustomizable | bool | Kann der Spieler im ExerCube Optionen (Dauer, Schwierigkeit, etc.) anpassen? |
| fixedRaceConfigId | int | ID des RaceConfig, nicht bearbeitbar im ExerCube. (z.B. bei Turnier-Formaten) |
TimelineMarkers
| Key | Typ | Beschreibung |
|---|---|---|
| timelineMarkerId | int | Die ID des timelineMarkers (DB index) |
| name | string | Die String-ID (unique) des Markers |
| readableName | string | Der (human readable) Name des Markers |
| time | float | Die Zeit nach Start des Workouts nachdem die Übung passiert wurde |
| tier | int | Der Combo-Tier mit welchem die Übung passiert wurde |
| physicalPrecision | float? | Der Präzisionswert einer eher körperlich anstrengenden Übung (normalisiert, 0 falls verpasst/falsch) |
| physicalRating | int | Der Evaluierungswert der Übung (Anzahl Sterne; 0 falls fehlerhaft) |
| cognitivePrecision | float? | Der Präzisionswert einer eher kognitiv anstrengenden Übung (normalisiert, 0 falls verpasst/falsch) |
| cognitiveRating | int | Der Evaluierungswert der Übung (Anzahl Sterne; 0 falls fehlerhaft) |
| score | int | Die erhaltene Punktzahl für diese Übung |
Prefixes
| Name Prefixes | Beispiele | Beschreibung |
|---|---|---|
| exercise_ | exercise_midPunchLeft | Die String-ID (unique) des Exercises |
| pitStop_ | pitStop_enter, pitStop_exit | Bei Einfahrt und Ausfahrt in den Boxenstopp |
| pause_ | pause_on, pause_off | Beim manuellen pausieren/unpausieren |
| workout_ | workout_abort | Beim manuellen Abbruch eines Workouts. |
Workouts
| Key | Typ | Beschreibung |
|---|---|---|
| workoutId | int | ID des Workouts (DB index) |
| userId | int | ID des Users, der das Workout erstellt hat |
| absolvedWorkout | bool | Zeigt, ob das Workout schon im Exercube absolviert wurde. |
| sessionsId | int | ID der Session, wird nach Absolvierung im ExerCube hinzugefügt. |
| hrTracking | bool | Wurde die Herzrate getracked? |
| hrValues | [float,int] | Array von allen gemessenen HR-Werten; in der angegebenen Struktur
– time = Zeit nach start des Workouts (in Sekunden)
– value = HR-Wert |
| hrAverage | int | Die Durchschnittliche Herzrate über das ganze Workout gemessen |
| hrMax | int | Die tatsächlich erreichte maximale Herzrate |
| timelineMarkers | [] | Array, welches geordnet alle Events auf der Rennstrecke fasst (siehe TimelineMarkers) |
| score | int | Die erreichte Punktzahl in diesem Workout |
| maxCombo | int | Maximum Combo, welcher erreicht wurde |
| bodyScore | float | Workout BodyScore (prozentualer Wert [0.0-1.0]) |
| brainScore | float | Workout BrainScore (prozentualer Wert [0.0-1.0]) |
| dualflowScore | float | Workout DualflowScore (prozentualer Wert [0.0-1.0]) |
| timelineMarkers | [] | Array, welches geordnet alle Events auf der Rennstrecke fasst (siehe TimelineMarkers) |
RaceConfig
| Key | Typ | Beschreibung |
|---|---|---|
| raceConficId | int | ID der RaceConfig (DB index) |
| seed | int | Der generierte Seed für die Erzeugung der Übungsreihenfolge auf dem Track |
| type | string | Unterscheidet zwischen Konfigurationen im Racer, die Sphery kuratiert. Z.B. “workout”, “competition”, “studie_bern” |
| difficulty | int | Schwierigkeit des Rennens [0-2] |
| duration | int | Dauer des Rennens (in Sekunden) |
| tutorial | bool | Boxenstopp Tutorials verwendet? |
| startSpeed | float | Startgeschwindigkeit des Rennens [0.0-1.0] |
| hrTarget | float | Die Ziel-HR für HR-adaptives Spiel |
Beispiele
Login
Das Spiel wird ein Loginformular beinhalten. Die Registrierung wird direkt über die WebApp vorgenommen. Die gesendeten Requests der Formulare beinhalten folgende Daten:
Login
{
"email": "stophel@blocher.ch",
"password": "schlumpfgate"
}
Response
{
"token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOjMsImVtYWlsIjoibWljaGVsLmtpbnplbEBtb3J0eS5jb20iLCJpYXQiOjE2MTE1NzYxMzksImV4cCI6MTYxMTY2MjUzOX0.ZjvJxYY0FhUSveOoGlLQic2syf-_eBGZnVahicQFj4k",
"user": {
"userId": 1,
"email": "stophel@blocher.ch",
"username": "Christoph"
}
}
Start der Session
Direkt nach dem Login und dem Erhalt des Authentifizierungs-Tokens und der Benutzer ID wird eine neue Spiel-Session gestartet.
Request
Das Spiel sendet folgenden Request:
Type: POST
URI: https://sphery-1.herokuapp.com/api/v1/session
Header: Authorization: Bearer <token>
{
"loginAt": "2021-01-25T12:26:40.244Z",
"exercube": "SpheryCube1"
}
Response
Als Antwort werden vom Server Benutzerspezifische Daten erwartet, welche für das Spiel erforderlich sind.
{
"session": {
"sessionId": 1,
"healthData": <health-data>,
"workouts": [...<workout>]
}
}
Ende der Session
Zum Beenden der Session sendet das Spiel folgenden Request:
Type: PATCH
URI: https://sphery-1.herokuapp.com/api/v1/session
Header: Authorization: Bearer <token>
Request
{
"sessionId": 1,
"logoutAt": "2021-01-25T12:57:38.184Z",
"sessionTime": 1547.23,
"netPromoterScore": 8
}
Response
{
"session": {
"sessionId": 1,
"loginAt": "2021-01-25T12:26:40.244Z",
"logoutAt": "2021-01-25T12:57:38.184Z",
"sessionTime": 1547.23,
"netPromoterScore": 8,
"userId": 1,
"createdAt": "2021-01-25T12:26:40.244Z",
"updatedAt": "2021-01-25T12:57:38.184Z"
}
}
Abgeschlossenes Workout
Ein Workout beschreibt eigentlich immer ein einzelnes Rennen im Spiel. Nachdem der Spieler das Rennen beendet hat (sprich das Ziel erreicht hat), wird ein Request mit den gesamten Workout-Daten erstellt.
Request
Type: POST
URI: https://sphery-1.herokuapp.com/api/v1/workout
Header: Authorization: Bearer <token>
{
"completedWorkout":"true", // BOOLEAN
"hrTracking":"true", // BOOLEAN
"hrAverage":120, // INTEGER
"hrMax":200, // INTEGER
"score":600'000, // INTEGER
"maxCombo":81, // INTEGER
"bodyScore":0.89, // FLOAT
"brainScore":0.67, // FLOAT
"dualflowScore":0.78, // FLOAT
"measuredDuration":900.1, // FLOAT
"raceConfig": { // OBJECT
"seed": 45667567, // INTEGER
"adaptivityType": "hrTracking", // STRING
"difficulty": 2, // INTEGER
"duration": 900.00, // FLOAT
"tutorial": false, // BOOLEAN
"startSpeed": 5, // INTEGER
"hrTarget": 0.9 // FLOAT
},
"hrValues": [ // ARRAY (OPTIONAL)
{
"time": 3.02, // FLOAT
"value": 82 // INTEGER
},
{
"time": 6.13, // FLOAT
"value": 83 // INTEGER
},
...
],
"timelineMarkers": [ // ARRAY
{
"name": "exercise_midPunchLeft", // STRING
"readableName": "Punch Left", // STRING
"time": 2.14, // FLOAT
"tier": 2, // INTEGER
"physicalPrecision": 0.8, // FLOAT
"physicalRating": 3, // INTEGER
"cognitivePrecision": 0.6, // FLOAT
"cognitiveRating": 0, // INTEGER
"score": 230 // INTEGER
}
],
"workoutId": null, // NULL OR INTEGER (OPTIONAL) -> API CREATES NEW WORKOUT WHEN NO INTEGER (VALID ID OF EXISTING, NOT-COMPLETED WORKOUT) IS PROVIDED
"sessionId": 1, // INTEGER
"workoutPresetId": 4, // STRING
"challengeId": null // NULL OR INTEGER (OPTIONAL)
}
Response Success
{
"Workout transfer successful! En schöne!"
}
Response Error
{
"Öppis isch voll falsch gloffe!"
}
Allgemeine Datensätze
Hier werden alle Datensätze aufgeschlüsselt welche oft Inhalt der einzelnen Requests sein können und als eigene "Objekte" oder "Typen" gehandhabt werden.
HealthData
{
"hrRestingPulse": 80,
"hrReserve": 116,
"hrMax": 196,
"weight": 75,
"age": 30
}
| Key | Typ | Beschreibung |
|---|---|---|
| hrRestingPulse | int | Ruhepuls der HR; null falls undefiniert |
| hrReserve | int | Reservepuls der HR; null falls undefiniert |
| hrMax | int | HR Max; null falls undefiniert |
| weight | int | Gewicht (in kg); null falls undefiniert |
| age | int | Alter; null falls undefiniert |
WorkoutPresets
{
"name": "MyCustomWorkout",
"race": {
"seed": 6545684,
"type": "workout",
"difficulty": 2,
"duration": 900.00,
"tutorial": false,
"startSpeed": 5,
"hrTarget": 0.9
},
"exercises": [
"touches",
"punches"
],
"tiers": [
{
"cognitiveChallenges": [
"mirror",
"lights"
],
"cognitiveMin": 0.0,
"cognitiveMax": 1.0,
"physicalMin": 0.0,
"physicalMax": 1.0
},
...
]
}
| Key | Typ | Beschreibung |
|---|---|---|
| name | string | Der Name (und String-ID; unique) des Workouts |
| race | {} | Renn-spezifische Daten |
| → seed | int | Der generierte Seed für die Erzeugung der Übungsreihenfolge auf dem Track |
| → type | string | Unterscheidet zwischen Konfigurationen im Racer, die Sphery kuratiert. Z.B. "workout", "competition", "studie_bern" |
| → difficulty | int | Schwierigkeit des Rennens [0-2] |
| → duration | int | Dauer des Rennens (in Sekunden) |
| → tutorial | bool | Boxenstopp Tutorials verwendet? |
| → startSpeed | float | Startgeschwindigkeit des Rennens [0.0-1.0] |
| → hrTarget | float | Die Ziel-HR für HR-adaptives Spiel |
| exercises | [...string] | Array an Übungs-Typen welche für das Spiel ausgewählt wurden |
| tiers | [...{}] | Alle Combo-Tier informationen als Array; Länge des Arrays ist die Anzahl der Tiers |
| → cognitiveChallenges | [...string] | Array an kognitiven Challenge-Typen welche für das Spiel ausgewählt wurden |
| → cognitiveMin | float? | Minimale kognitive Adaptivität [0.0-1.0] |
| → cognitiveMax | float? | Maximale kognitive Adaptivität [0.0-1.0] |
| → physicalMin | float? | Minimale physische Adaptivität [0.0-1.0] |
| → physicalMax | float? | Maximale physische Adaptivität [0.0-1.0] |
TimelineMarkers
{
"name": "exercise_midPunchLeft",
"readableName": "Punch Left",
"time": 2.14,
"tier": 2,
"physicalPrecision": 0.8,
"physicalRating": 3,
"cognitivePrecision": -1,
"cognitiveRating": 0,
"score": 230
}
Beispiele
Diese Beispiele zeigen wie jeweils ein voller Request für die oben beschriebenen Sync-Points am Ende strukturiert ist und aussehen könnte.
Start der Session
{
"loginAt": "2021-01-25T12:26:40.244Z",
"exercube": "SpheryCube1"
}
Ende der Session
{
"sessionId": 1,
"logoutAt": "2021-01-25T12:57:38.184Z",
"sessionTime": 1800,
"netPromoterScore": 8
}
Abgeschlossenes Workout
{
"workoutPreset": {
"name": "MyCustomWorkout",
"race": {
"seed": 45667567,
"type": "workout",
"difficulty": 2,
"duration": 900,
"tutorial": false,
"startSpeed": 5,
"hrTarget": 0.9
},
"exercises": [
"touches",
"punches"
],
"tiers": [
{
"cognitiveChallenges": [
"mirror",
"lights"
],
"cognitiveMin": 0.0,
"cognitiveMax": 1.0,
"physicalMin": 0.0,
"physicalMax": 1.0
},
...
]
},
"sessionId": 1,
"challenge": -1,
"duration": 600,
"hrTracking": true,
"hrAdaptivity": true,
"hrValues": [
{
"time": 3.02,
"value": 82
},
{
"time": 6.13,
"value": 83
},
...
],
"hrAverage": 163,
"hrMax": 199,
"score": 756388,
"maxCombo": 123,
"bodyScore": 0.9,
"brainScore": 0.8,
"dualflowScore": 0.85,
"timelineMarkers": [
{
"name": "exercise_midPunchLeft",
"readableName": "Punch Left",
"time": 2.14,
"tier": 2,
"physicalPrecision": 0.8,
"physicalRating": 3,
"cognitivePrecision": -1,
"cognitiveRating": 0,
"score": 230
},
...
]
}
Table "public.Workouts"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------------+--------------------------+-----------+----------+----------------------------------------+---------+--------------+-------------
id | integer | | not null | nextval('"Workouts_id_seq"'::regclass) | plain | |
completedWorkout | boolean | | not null | false | plain | |
hrTracking | boolean | | | | plain | |
hrAverage | integer | | | | plain | |
hrMax | integer | | | | plain | |
score | integer | | | | plain | |
maxCombo | integer | | | | plain | |
bodyScore | double precision | | | | plain | |
brainScore | double precision | | | | plain | |
dualflowScore | double precision | | | | plain | |
userId | integer | | | | plain | |
sessionId | integer | | | | plain | |
workoutPresetId | integer | | | | plain | |
createdAt | timestamp with time zone | | not null | | plain | |
updatedAt | timestamp with time zone | | not null | | plain | |
measuredDuration | double precision | | | | plain | |
Indexes:
"Workouts_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"Workouts_sessionId_fkey" FOREIGN KEY ("sessionId") REFERENCES "Sessions"(id) ON DELETE SET NULL
"Workouts_userId_fkey" FOREIGN KEY ("userId") REFERENCES "Users"(id) ON DELETE CASCADE
"Workouts_workoutPresetId_fkey" FOREIGN KEY ("workoutPresetId") REFERENCES "WorkoutPresets"(id) ON DELETE SET NULL
Referenced by:
TABLE ""HrValues"" CONSTRAINT "HrValues_workoutId_fkey" FOREIGN KEY ("workoutId") REFERENCES "Workouts"(id) ON DELETE CASCADE
TABLE ""RaceConfigs"" CONSTRAINT "RaceConfigs_workoutId_fkey" FOREIGN KEY ("workoutId") REFERENCES "Workouts"(id) ON DELETE CASCADE
TABLE ""TimelineMarkers"" CONSTRAINT "TimelineMarkers_workoutId_fkey" FOREIGN KEY ("workoutId") REFERENCES "Workouts"(id) ON DELETE CASCADE
Access method: heap
Table "public.HealthData"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------------+--------------------------+-----------+----------+------------------------------------------+---------+--------------+-------------
id | integer | | not null | nextval('"HealthData_id_seq"'::regclass) | plain | |
hrRestingPulse | integer | | | | plain | |
hrReserve | integer | | | | plain | |
hrMax | integer | | | | plain | |
weight | integer | | | | plain | |
age | integer | | | | plain | |
userId | integer | | | | plain | |
createdAt | timestamp with time zone | | not null | | plain | |
updatedAt | timestamp with time zone | | not null | | plain | |
Indexes:
"HealthData_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"HealthData_userId_fkey" FOREIGN KEY ("userId") REFERENCES "Users"(id) ON DELETE CASCADE
Access method: heap
Table "public.HrValues"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------+--------------------------+-----------+----------+----------------------------------------+---------+--------------+-------------
id | integer | | not null | nextval('"HrValues_id_seq"'::regclass) | plain | |
time | double precision | | | | plain | |
value | integer | | | | plain | |
workoutId | integer | | | | plain | |
createdAt | timestamp with time zone | | not null | | plain | |
updatedAt | timestamp with time zone | | not null | | plain | |
Indexes:
"HrValues_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"HrValues_workoutId_fkey" FOREIGN KEY ("workoutId") REFERENCES "Workouts"(id) ON DELETE CASCADE
Access method: heap
Table "public.RaceConfigs"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------------+--------------------------+-----------+----------+-------------------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('"RaceConfigs_id_seq"'::regclass) | plain | |
seed | integer | | | | plain | |
adaptivityType | character varying(255) | | | | extended | |
difficulty | integer | | | | plain | |
duration | double precision | | | | plain | |
tutorial | boolean | | | | plain | |
startSpeed | integer | | | | plain | |
hrTarget | double precision | | | | plain | |
workoutId | integer | | | | plain | |
createdAt | timestamp with time zone | | not null | | plain | |
updatedAt | timestamp with time zone | | not null | | plain | |
Indexes:
"RaceConfigs_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"RaceConfigs_workoutId_fkey" FOREIGN KEY ("workoutId") REFERENCES "Workouts"(id) ON DELETE CASCADE
Access method: heap
Table "public.Sessions"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------------+--------------------------+-----------+----------+----------------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('"Sessions_id_seq"'::regclass) | plain | |
exercube | character varying(255) | | | | extended | |
loginAt | timestamp with time zone | | | | plain | |
logoutAt | timestamp with time zone | | | | plain | |
sessionTime | double precision | | | | plain | |
netPromoterScore | integer | | | | plain | |
userId | integer | | | | plain | |
createdAt | timestamp with time zone | | not null | | plain | |
updatedAt | timestamp with time zone | | not null | | plain | |
Indexes:
"Sessions_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"Sessions_userId_fkey" FOREIGN KEY ("userId") REFERENCES "Users"(id) ON DELETE CASCADE
Referenced by:
TABLE ""Workouts"" CONSTRAINT "Workouts_sessionId_fkey" FOREIGN KEY ("sessionId") REFERENCES "Sessions"(id) ON DELETE SET NULL
Access method: heap
Table "public.TimelineMarkers"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------------------+--------------------------+-----------+----------+-----------------------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('"TimelineMarkers_id_seq"'::regclass) | plain | |
name | character varying(255) | | | | extended | |
readableName | character varying(255) | | | | extended | |
time | double precision | | | | plain | |
tier | integer | | | | plain | |
physicalPrecision | double precision | | | | plain | |
physicalRating | integer | | | | plain | |
cognitivePrecision | double precision | | | | plain | |
cognitiveRating | integer | | | | plain | |
score | integer | | | | plain | |
workoutId | integer | | | | plain | |
createdAt | timestamp with time zone | | not null | | plain | |
updatedAt | timestamp with time zone | | not null | | plain | |
Indexes:
"TimelineMarkers_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"TimelineMarkers_workoutId_fkey" FOREIGN KEY ("workoutId") REFERENCES "Workouts"(id) ON DELETE CASCADE
Access method: heap
Table "public.Users"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------------+--------------------------+-----------+----------+-------------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('"Users_id_seq"'::regclass) | plain | |
email | character varying(255) | | | | extended | |
username | character varying(255) | | | | extended | |
password | character varying(255) | | | | extended | |
verified | boolean | | not null | false | plain | |
verificationCode | character varying(255) | | | | extended | |
createdAt | timestamp with time zone | | not null | | plain | |
updatedAt | timestamp with time zone | | not null | | plain | |
Indexes:
"Users_pkey" PRIMARY KEY, btree (id)
"Users_email_key" UNIQUE CONSTRAINT, btree (email)
Referenced by:
TABLE ""HealthData"" CONSTRAINT "HealthData_userId_fkey" FOREIGN KEY ("userId") REFERENCES "Users"(id) ON DELETE CASCADE
TABLE ""Sessions"" CONSTRAINT "Sessions_userId_fkey" FOREIGN KEY ("userId") REFERENCES "Users"(id) ON DELETE CASCADE
TABLE ""Workouts"" CONSTRAINT "Workouts_userId_fkey" FOREIGN KEY ("userId") REFERENCES "Users"(id) ON DELETE CASCADE
Access method: heap
Table "public.WorkoutPresets"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------------+--------------------------+-----------+----------+----------------------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('"WorkoutPresets_id_seq"'::regclass) | plain | |
name | character varying(255) | | | | extended | |
customizable | boolean | | | | plain | |
descriptionText | character varying(255) | | | | extended | |
fixedRaceConfigId | integer | | | | plain | |
exercisePool | character varying(255) | | | | extended | |
createdAt | timestamp with time zone | | not null | | plain | |
updatedAt | timestamp with time zone | | not null | | plain | |
Indexes:
"WorkoutPresets_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE ""Workouts"" CONSTRAINT "Workouts_workoutPresetId_fkey" FOREIGN KEY ("workoutPresetId") REFERENCES "WorkoutPresets"(id) ON DELETE SET NULL
Access method: heap