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 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159
| DROP TABLE IF EXISTS T_USER_USR ;
CREATE TABLE T_USER_USR (USR_ID INT AUTO_INCREMENT NOT NULL,
GNR_ID INT NOT NULL,
USR_NAME VARCHAR(36) NOT NULL,
USR_SURNAME VARCHAR(32),
USR_EMAIL VARCHAR(64),
USR_PWD VARCHAR(40),
PRIMARY KEY (USR_ID)) ENGINE=InnoDB;
DROP TABLE IF EXISTS T_PHYSIOLOGY_PHY ;
CREATE TABLE T_PHYSIOLOGY_PHY (USR_ID INT NOT NULL,
PHY_BIRTH_YEAR SMALLINT,
PHY_VO2MAX SMALLINT,
PHY_FTP SMALLINT,
PHY_AMP SMALLINT,
PHY_MAX_HR SMALLINT,
PRIMARY KEY (USR_ID)) ENGINE=InnoDB;
DROP TABLE IF EXISTS T_WORKOUT_WO ;
CREATE TABLE T_WORKOUT_WO (WO_ID INT AUTO_INCREMENT NOT NULL,
SES_ID INT NOT NULL,
USR_ID INT NOT NULL,
WO_DATETIME DATETIME NOT NULL, -- DEFAULT GETDATE(),
WO_DURATION TIME,
WO_DISTANCE INT,
WO_SPEED_AVG SMALLINT,
WO_FORCE_AVG SMALLINT,
WO_FORCE_PEAK SMALLINT,
PRIMARY KEY (WO_ID)) ENGINE=InnoDB;
DROP TABLE IF EXISTS T_MEASURE_MES ;
CREATE TABLE T_MEASURE_MES (MES_ID INT AUTO_INCREMENT NOT NULL,
WO_ID INT NOT NULL,
MES_TIME TIME NOT NULL,
MES_SPEED SMALLINT NOT NULL,
MES_INTENSITY_PERCENT TINYINT,
MES_FORCE SMALLINT,
MES_HEART_RATE SMALLINT,
PRIMARY KEY (MES_ID)) ENGINE=InnoDB;
DROP TABLE IF EXISTS T_STEP_STP ;
CREATE TABLE T_STEP_STP (STP_ID INT AUTO_INCREMENT NOT NULL,
SES_ID INT NOT NULL,
COG_ID INT,
STP_RANK_ORDER TINYINT NOT NULL,
STP_RESISTANCE SMALLINT NOT NULL,
STP_SPEED SMALLINT NOT NULL,
STP_MESSAGE VARCHAR(200),
PRIMARY KEY (STP_ID)) ENGINE=InnoDB;
DROP TABLE IF EXISTS T_STIMULATION_STM ;
CREATE TABLE T_STIMULATION_STM (STM_ID INT AUTO_INCREMENT NOT NULL,
STP_ID INT NOT NULL,
USR_ID INT NOT NULL,
MSL_ID INT NOT NULL,
STM_ACTIVE BOOL NOT NULL,
STM_MIN_INTENSITY TINYINT NOT NULL DEFAULT 0,
STM_MAX_INTENSITY TINYINT NOT NULL,
STM_FREQUENCY TINYINT NOT NULL,
STM_PULSEWIDTH SMALLINT NOT NULL,
STM_START_ANGLE SMALLINT NOT NULL,
STM_STOP_ANGLE SMALLINT NOT NULL,
STM_RAMP_UP TINYINT NOT NULL,
STM_RAMP_DOWN TINYINT NOT NULL,
STM_MAX_INTENSITY_INC TINYINT NOT NULL,
STM_SESS_MAX_INTENSITY INT,
PRIMARY KEY (STM_ID)) ENGINE=InnoDB;
DROP TABLE IF EXISTS TR_MUSCLE_MSL ;
CREATE TABLE TR_MUSCLE_MSL (MSL_ID INT AUTO_INCREMENT NOT NULL,
MSL_NAME VARCHAR(20),
MSL_CHANNEL TINYINT,
PRIMARY KEY (MSL_ID)) ENGINE=InnoDB;
DROP TABLE IF EXISTS TR_COGGAN_COG ;
CREATE TABLE TR_COGGAN_COG (COG_ID INT AUTO_INCREMENT NOT NULL,
COG_NUMBER INT NOT NULL,
COG_NAME VARCHAR(20),
COG_DESCRIPTION VARCHAR(200),
COG_FTP_PC_MIN TINYINT NOT NULL,
COG_FTP_PC_MAX TINYINT NOT NULL,
COG_HR_PC_MIN TINYINT NOT NULL,
COG_HR_PC_MAX TINYINT NOT NULL,
PRIMARY KEY (COG_ID)) ENGINE=InnoDB;
DROP TABLE IF EXISTS T_SEQUENCE_SEQ ;
CREATE TABLE T_SEQUENCE_SEQ (SEQ_ID INT AUTO_INCREMENT NOT NULL,
WOT_ID INT NOT NULL,
SEQ_NAME VARCHAR(20) NOT NULL,
SEQ_LOCKED_X_SESS_NEXT_SEQ TINYINT, -- Sequence locked if x sessions of next sequence are done
SEQ_X_SESSIONS_CARRYON TINYINT, -- Minimum number of sessions to execute to carry on with next sequence
SEQ_RANK_ORDER INT,
PRIMARY KEY (SEQ_ID)) ENGINE=InnoDB;
DROP TABLE IF EXISTS TR_WORKOUT_TYPE_WOT ;
CREATE TABLE TR_WORKOUT_TYPE_WOT (WOT_ID INT AUTO_INCREMENT NOT NULL,
ERG_ID TINYINT NOT NULL,
WOT_NAME VARCHAR(20) NOT NULL,
PRIMARY KEY (WOT_ID)) ENGINE=InnoDB;
DROP TABLE IF EXISTS T_SESSION_SES ;
CREATE TABLE T_SESSION_SES (SES_ID INT AUTO_INCREMENT NOT NULL,
SES_START_MESSAGE VARCHAR(200),
SES_END_MESSAGE VARCHAR(200),
SES_ASSIST_POWER SMALLINT,
SES_MOTOR_SPEED_OFFSET TINYINT,
SES_WARMUP_RAMP TINYINT,
SES_LINKED_TO_PREV BOOL, -- Defines if the session can be executed alone or if previous sessions must be done to allow this one
PRIMARY KEY (SES_ID)) ENGINE=InnoDB;
DROP TABLE IF EXISTS TR_ERGOMETER_ERG ;
CREATE TABLE TR_ERGOMETER_ERG (ERG_ID TINYINT AUTO_INCREMENT NOT NULL,
ERG_NAME VARCHAR(20) NOT NULL,
PRIMARY KEY (ERG_ID)) ENGINE=InnoDB;
DROP TABLE IF EXISTS TR_GENDER_GNR ;
CREATE TABLE TR_GENDER_GNR (GNR_ID INT AUTO_INCREMENT NOT NULL,
GNR_NAME CHAR(1) NOT NULL,
PRIMARY KEY (GNR_ID)) ENGINE=InnoDB;
DROP TABLE IF EXISTS TJ_USER_SESSION_USS ;
CREATE TABLE TJ_USER_SESSION_USS (USR_ID INT NOT NULL,
SES_ID INT NOT NULL,
USS_DONE BOOL,
PRIMARY KEY (USR_ID,
SES_ID)) ENGINE=InnoDB;
DROP TABLE IF EXISTS TJ_SEQ_COMPOSITION_SCP ;
CREATE TABLE TJ_SEQ_COMPOSITION_SCP (SEQ_ID INT NOT NULL,
SES_ID INT NOT NULL,
SCP_RANK_ORDER TINYINT NOT NULL,
PRIMARY KEY (SEQ_ID,
SES_ID)) ENGINE=InnoDB;
DROP TABLE IF EXISTS TJ_PRACTICE_SEQ_PSQ ;
CREATE TABLE TJ_PRACTICE_SEQ_PSQ (SEQ_ID INT NOT NULL,
USR_ID INT NOT NULL,
PSQ_DONE BOOL NOT NULL,
PRIMARY KEY (SEQ_ID,
USR_ID)) ENGINE=InnoDB;
ALTER TABLE T_USER_USR ADD CONSTRAINT FK_USR_GNR FOREIGN KEY (GNR_ID) REFERENCES TR_GENDER_GNR (GNR_ID);
ALTER TABLE T_PHYSIOLOGY_PHY ADD CONSTRAINT FK_USR_PHY FOREIGN KEY (USR_ID) REFERENCES T_USER_USR (USR_ID);
ALTER TABLE T_WORKOUT_WO ADD CONSTRAINT FK_WO_SES FOREIGN KEY (SES_ID) REFERENCES T_SESSION_SES (SES_ID);
ALTER TABLE T_WORKOUT_WO ADD CONSTRAINT FK_WO_USR FOREIGN KEY (USR_ID) REFERENCES T_USER_USR (USR_ID);
ALTER TABLE T_MEASURE_MES ADD CONSTRAINT FK_MES_WO FOREIGN KEY (WO_ID) REFERENCES T_WORKOUT_WO (WO_ID);
ALTER TABLE T_STEP_STP ADD CONSTRAINT FK_STP_SES FOREIGN KEY (SES_ID) REFERENCES T_SESSION_SES (SES_ID);
ALTER TABLE T_STEP_STP ADD CONSTRAINT FK_STP_COG FOREIGN KEY (COG_ID) REFERENCES TR_COGGAN_COG (COG_ID);
ALTER TABLE T_STIMULATION_STM ADD CONSTRAINT FK_STM_STP FOREIGN KEY (STP_ID) REFERENCES T_STEP_STP (STP_ID);
ALTER TABLE T_STIMULATION_STM ADD CONSTRAINT FK_STM_MSL FOREIGN KEY (MSL_ID) REFERENCES TR_MUSCLE_MSL (MSL_ID);
ALTER TABLE T_STIMULATION_STM ADD CONSTRAINT FK_STM_USR FOREIGN KEY (USR_ID) REFERENCES T_USER_USR (USR_ID);
ALTER TABLE T_SEQUENCE_SEQ ADD CONSTRAINT FK_SEQ_WOT FOREIGN KEY (WOT_ID) REFERENCES TR_WORKOUT_TYPE_WOT (WOT_ID);
ALTER TABLE TR_WORKOUT_TYPE_WOT ADD CONSTRAINT FK_WOT_ERG FOREIGN KEY (ERG_ID) REFERENCES TR_ERGOMETER_ERG (ERG_ID);
ALTER TABLE TJ_USER_SESSION_USS ADD CONSTRAINT FK_USS_USR FOREIGN KEY (USR_ID) REFERENCES T_USER_USR (USR_ID);
ALTER TABLE TJ_USER_SESSION_USS ADD CONSTRAINT FK_USS_SES FOREIGN KEY (SES_ID) REFERENCES T_SESSION_SES (SES_ID);
ALTER TABLE TJ_SEQ_COMPOSITION_SCP ADD CONSTRAINT FK_SCP_SEQ FOREIGN KEY (SEQ_ID) REFERENCES T_SEQUENCE_SEQ (SEQ_ID);
ALTER TABLE TJ_SEQ_COMPOSITION_SCP ADD CONSTRAINT FK_SCP_SES FOREIGN KEY (SES_ID) REFERENCES T_SESSION_SES (SES_ID);
ALTER TABLE TJ_PRACTICE_SEQ_PSQ ADD CONSTRAINT FK_PSQ_SEQ FOREIGN KEY (SEQ_ID) REFERENCES T_SEQUENCE_SEQ (SEQ_ID);
ALTER TABLE TJ_PRACTICE_SEQ_PSQ ADD CONSTRAINT FK_PSQ_USR FOREIGN KEY (USR_ID) REFERENCES T_USER_USR (USR_ID); |
Partager