· 9 years ago · Oct 07, 2016, 10:22 AM
1-- MySQL Script generated by MySQL Workbench
2-- 08/22/16 14:54:51
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
9
10-- -----------------------------------------------------
11-- Schema ariondb
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema ariondb
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `ariondb` DEFAULT CHARACTER SET utf8 ;
18USE `ariondb` ;
19
20-- -----------------------------------------------------
21-- Table `ariondb`.`userLogin`
22-- -----------------------------------------------------
23DROP TABLE IF EXISTS `ariondb`.`userLogin` ;
24
25CREATE TABLE IF NOT EXISTS `ariondb`.`userLogin` (
26 `email` VARCHAR(75) NOT NULL,
27 `password` VARCHAR(50) NOT NULL,
28 `restKey1` BIGINT(20) NOT NULL,
29 `restKey2` BIGINT(20) NOT NULL,
30 `enabled` TINYINT(1) NOT NULL,
31 PRIMARY KEY (`email`),
32 UNIQUE INDEX `email_UNIQUE` (`email` ASC))
33ENGINE = InnoDB;
34
35
36-- -----------------------------------------------------
37-- Table `ariondb`.`stable`
38-- -----------------------------------------------------
39DROP TABLE IF EXISTS `ariondb`.`stable` ;
40
41CREATE TABLE IF NOT EXISTS `ariondb`.`stable` (
42 `ID` INT NOT NULL AUTO_INCREMENT,
43 `ownerID` VARCHAR(75) NULL,
44 `street` VARCHAR(45) NOT NULL,
45 `city` VARCHAR(45) NOT NULL,
46 `country` VARCHAR(45) NOT NULL,
47 `phoneNumber` VARCHAR(45) NULL,
48 PRIMARY KEY (`ID`),
49 UNIQUE INDEX `ID_UNIQUE` (`ID` ASC),
50 INDEX `email_idx` (`ownerID` ASC),
51 CONSTRAINT `email`
52 FOREIGN KEY (`ownerID`)
53 REFERENCES `ariondb`.`userLogin` (`email`)
54 ON DELETE NO ACTION
55 ON UPDATE NO ACTION)
56ENGINE = InnoDB;
57
58
59-- -----------------------------------------------------
60-- Table `ariondb`.`userRegular`
61-- -----------------------------------------------------
62DROP TABLE IF EXISTS `ariondb`.`userRegular` ;
63
64CREATE TABLE IF NOT EXISTS `ariondb`.`userRegular` (
65 `email` VARCHAR(75) NOT NULL,
66 `newsletterKey1` BIGINT(20) NOT NULL,
67 `newsletterKey2` BIGINT(20) NOT NULL,
68 `newsletterEnabled` TINYINT(1) NOT NULL,
69 `firstName` VARCHAR(45) NOT NULL,
70 `lastName` VARCHAR(45) NOT NULL,
71 `stableID` INT NULL,
72 `phoneNumber` VARCHAR(45) NULL,
73 `profilePicture` VARCHAR(100) NULL,
74 PRIMARY KEY (`email`),
75 UNIQUE INDEX `email_UNIQUE` (`email` ASC),
76 INDEX `stableID_idx` (`stableID` ASC),
77 CONSTRAINT `emailReg`
78 FOREIGN KEY (`email`)
79 REFERENCES `ariondb`.`userLogin` (`email`)
80 ON DELETE NO ACTION
81 ON UPDATE NO ACTION,
82 CONSTRAINT `stableID`
83 FOREIGN KEY (`stableID`)
84 REFERENCES `ariondb`.`stable` (`ID`)
85 ON DELETE NO ACTION
86 ON UPDATE NO ACTION)
87ENGINE = InnoDB;
88
89
90-- -----------------------------------------------------
91-- Table `ariondb`.`horse`
92-- -----------------------------------------------------
93DROP TABLE IF EXISTS `ariondb`.`horse` ;
94
95CREATE TABLE IF NOT EXISTS `ariondb`.`horse` (
96 `ID` INT NOT NULL AUTO_INCREMENT,
97 `name` VARCHAR(45) NOT NULL,
98 `horsePassport` INT NULL,
99 `birthday` DATE NULL,
100 `colorID` INT NULL,
101 `genderID` INT NULL,
102 `height` INT NULL,
103 `breedID` INT NULL,
104 `profilePicture` VARCHAR(100) NULL,
105 `enabled` TINYINT(1) NOT NULL,
106 `lastEnabledDate` DATETIME NULL,
107 PRIMARY KEY (`ID`),
108 UNIQUE INDEX `ID_UNIQUE` (`ID` ASC),
109 UNIQUE INDEX `horsePassport_UNIQUE` (`horsePassport` ASC))
110ENGINE = InnoDB;
111
112
113-- -----------------------------------------------------
114-- Table `ariondb`.`horsePictures`
115-- -----------------------------------------------------
116DROP TABLE IF EXISTS `ariondb`.`horsePictures` ;
117
118CREATE TABLE IF NOT EXISTS `ariondb`.`horsePictures` (
119 `horseID` INT NOT NULL,
120 `pictureID` VARCHAR(100) NOT NULL,
121 PRIMARY KEY (`horseID`, `pictureID`),
122 CONSTRAINT `horse`
123 FOREIGN KEY (`horseID`)
124 REFERENCES `ariondb`.`horse` (`ID`)
125 ON DELETE NO ACTION
126 ON UPDATE NO ACTION)
127ENGINE = InnoDB;
128
129
130-- -----------------------------------------------------
131-- Table `ariondb`.`placementSensor`
132-- -----------------------------------------------------
133DROP TABLE IF EXISTS `ariondb`.`placementSensor` ;
134
135CREATE TABLE IF NOT EXISTS `ariondb`.`placementSensor` (
136 `ID` INT NOT NULL AUTO_INCREMENT,
137 `sensor` VARCHAR(45) NOT NULL,
138 `placement` VARCHAR(45) NOT NULL,
139 PRIMARY KEY (`ID`),
140 UNIQUE INDEX `ID_UNIQUE` (`ID` ASC))
141ENGINE = InnoDB;
142
143
144-- -----------------------------------------------------
145-- Table `ariondb`.`trainingSession`
146-- -----------------------------------------------------
147DROP TABLE IF EXISTS `ariondb`.`trainingSession` ;
148
149CREATE TABLE IF NOT EXISTS `ariondb`.`trainingSession` (
150 `ID` INT NOT NULL AUTO_INCREMENT,
151 `userID` VARCHAR(75) NOT NULL,
152 `horseID` INT NOT NULL,
153 `placementID` INT NOT NULL,
154 `sensorID` VARCHAR(75) NOT NULL,
155 `startTime` DATETIME NULL,
156 `endTime` DATETIME NULL,
157 `hertz` DOUBLE NOT NULL,
158 PRIMARY KEY (`ID`),
159 UNIQUE INDEX `ID_UNIQUE` (`ID` ASC),
160 INDEX `user_idx` (`userID` ASC),
161 INDEX `hors_idx` (`horseID` ASC),
162 INDEX `placement_idx` (`placementID` ASC),
163 CONSTRAINT `usertrain`
164 FOREIGN KEY (`userID`)
165 REFERENCES `ariondb`.`userLogin` (`email`)
166 ON DELETE NO ACTION
167 ON UPDATE NO ACTION,
168 CONSTRAINT `horsetrain`
169 FOREIGN KEY (`horseID`)
170 REFERENCES `ariondb`.`horse` (`ID`)
171 ON DELETE NO ACTION
172 ON UPDATE NO ACTION,
173 CONSTRAINT `placementtrain`
174 FOREIGN KEY (`placementID`)
175 REFERENCES `ariondb`.`placementSensor` (`ID`)
176 ON DELETE NO ACTION
177 ON UPDATE NO ACTION)
178ENGINE = InnoDB;
179
180
181-- -----------------------------------------------------
182-- Table `ariondb`.`results`
183-- -----------------------------------------------------
184DROP TABLE IF EXISTS `ariondb`.`results` ;
185
186CREATE TABLE IF NOT EXISTS `ariondb`.`results` (
187 `ID` INT NOT NULL AUTO_INCREMENT,
188 `trainingSessionID` INT NOT NULL,
189 `gaitWalk` DECIMAL NULL,
190 `gaitTrot` DECIMAL NULL,
191 `gaitCanter` DECIMAL NULL,
192 `gaitStanding` DECIMAL NULL,
193 `gaitUnidentified` DECIMAL NULL,
194 `rotationLeft` DECIMAL NULL,
195 `rotationRight` DECIMAL NULL,
196 `rotationWalkLeft` DECIMAL NULL,
197 `rotationWalkRight` DECIMAL NULL,
198 `rotationTrotLeft` DECIMAL NULL,
199 `rotationTrotRight` DECIMAL NULL,
200 `rotationCanterLeft` DECIMAL NULL,
201 `rotationCanterRight` DECIMAL NULL,
202 `transitionWT` INT NULL,
203 `transitionWC` INT NULL,
204 `transitionTC` INT NULL,
205 `transitionCT` INT NULL,
206 `transitionCW` INT NULL,
207 `transitionTW` INT NULL,
208 `jump` INT NULL,
209 `symmetryWalkStepForceRight` DECIMAL NULL,
210 `symmetryWalkStepForceLeft` DECIMAL NULL,
211 `symmetryWalkForwardAccelerationRight` DECIMAL NULL,
212 `symmetryWalkForwardAccelerationLeft` DECIMAL NULL,
213 `symmetryWalkDurationRight` DECIMAL NULL,
214 `symmetryWalkDurationLeft` DECIMAL NULL,
215 `symmetryWalkPvalueStepForce` DECIMAL NULL,
216 `symmetryWalkPvalueForwardAcceleration` DECIMAL NULL,
217 `symmetryWalkPvalueWalkDuration` DECIMAL NULL,
218 `symmetryTrotStepForceRight` DECIMAL NULL,
219 `symmetryTrotStepForceLeft` DECIMAL NULL,
220 `symmetryTrotForwardAccelerationRight` DECIMAL NULL,
221 `symmetryTrotForwardAccelerationLeft` DECIMAL NULL,
222 `symmetryTrotDurationRight` DECIMAL NULL,
223 `symmetryTrotDurationLeft` DECIMAL NULL,
224 `symmetryTrotPvalueStepForce` DECIMAL NULL,
225 `symmetryTrotPvalueForwardAcceleration` DECIMAL NULL,
226 `symmetryTrotPvalueWalkDuration` DECIMAL NULL,
227 `intensitySum` DECIMAL NULL,
228 `gait` INT NULL,
229 `stepsCanterLeft` DECIMAL NULL,
230 `stepsCanterRight` DECIMAL NULL,
231 `customValuesID` INT NULL,
232 `timestamp` DATETIME NULL,
233 PRIMARY KEY (`ID`),
234 INDEX `ID_idx` (`trainingSessionID` ASC),
235 CONSTRAINT `ID`
236 FOREIGN KEY (`trainingSessionID`)
237 REFERENCES `ariondb`.`trainingSession` (`ID`)
238 ON DELETE NO ACTION
239 ON UPDATE NO ACTION)
240ENGINE = InnoDB;
241
242
243-- -----------------------------------------------------
244-- Table `ariondb`.`gps`
245-- -----------------------------------------------------
246DROP TABLE IF EXISTS `ariondb`.`gps` ;
247
248CREATE TABLE IF NOT EXISTS `ariondb`.`gps` (
249 `resultsID` INT NOT NULL,
250 `velocity` DECIMAL NOT NULL,
251 `longitude` DECIMAL NOT NULL,
252 `latitude` DECIMAL NOT NULL,
253 `elevation` DECIMAL NOT NULL,
254 PRIMARY KEY (`resultsID`),
255 CONSTRAINT `IDresults`
256 FOREIGN KEY (`resultsID`)
257 REFERENCES `ariondb`.`results` (`ID`)
258 ON DELETE NO ACTION
259 ON UPDATE NO ACTION)
260ENGINE = InnoDB;
261
262
263-- -----------------------------------------------------
264-- Table `ariondb`.`intensity`
265-- -----------------------------------------------------
266DROP TABLE IF EXISTS `ariondb`.`intensity` ;
267
268CREATE TABLE IF NOT EXISTS `ariondb`.`intensity` (
269 `resultsID` INT NOT NULL,
270 `value` DECIMAL NOT NULL,
271 PRIMARY KEY (`resultsID`),
272 CONSTRAINT `ID2`
273 FOREIGN KEY (`resultsID`)
274 REFERENCES `ariondb`.`results` (`ID`)
275 ON DELETE NO ACTION
276 ON UPDATE NO ACTION)
277ENGINE = InnoDB;
278
279
280-- -----------------------------------------------------
281-- Table `ariondb`.`touchdown`
282-- -----------------------------------------------------
283DROP TABLE IF EXISTS `ariondb`.`touchdown` ;
284
285CREATE TABLE IF NOT EXISTS `ariondb`.`touchdown` (
286 `resultsID` INT NOT NULL,
287 `value` DECIMAL NOT NULL,
288 PRIMARY KEY (`resultsID`),
289 CONSTRAINT `ID3`
290 FOREIGN KEY (`resultsID`)
291 REFERENCES `ariondb`.`results` (`ID`)
292 ON DELETE NO ACTION
293 ON UPDATE NO ACTION)
294ENGINE = InnoDB;
295
296
297-- -----------------------------------------------------
298-- Table `ariondb`.`strideFrequency`
299-- -----------------------------------------------------
300DROP TABLE IF EXISTS `ariondb`.`strideFrequency` ;
301
302CREATE TABLE IF NOT EXISTS `ariondb`.`strideFrequency` (
303 `resultsID` INT NOT NULL,
304 `value` DECIMAL NULL,
305 PRIMARY KEY (`resultsID`),
306 CONSTRAINT `ID4`
307 FOREIGN KEY (`resultsID`)
308 REFERENCES `ariondb`.`results` (`ID`)
309 ON DELETE NO ACTION
310 ON UPDATE NO ACTION)
311ENGINE = InnoDB;
312
313
314-- -----------------------------------------------------
315-- Table `ariondb`.`customValues`
316-- -----------------------------------------------------
317DROP TABLE IF EXISTS `ariondb`.`customValues` ;
318
319CREATE TABLE IF NOT EXISTS `ariondb`.`customValues` (
320 `ID` INT NOT NULL AUTO_INCREMENT,
321 `resultsID` INT NOT NULL,
322 `MPD` DECIMAL NULL,
323 `MPH` DECIMAL NULL,
324 `WalkMaxTime` DECIMAL NULL,
325 `WalkMinTime` DECIMAL NULL,
326 `WalkminPKS` DECIMAL NULL,
327 `WalkMaxPKS` DECIMAL NULL,
328 `WalkPeakNum` DECIMAL NULL,
329 `WalkBreakdiff` DECIMAL NULL,
330 `WalkIntegral` DECIMAL NULL,
331 `WalkPSD` DECIMAL NULL,
332 `WalkHorizontalThust` DECIMAL NULL,
333 `TrotMaxTime` DECIMAL NULL,
334 `TrotminPKS` DECIMAL NULL,
335 `TrotMinIntegral` DECIMAL NULL,
336 `TrotMaxIntegral` DECIMAL NULL,
337 `TrotPeakNum` DECIMAL NULL,
338 `TrotPSDMin` DECIMAL NULL,
339 `TrotPSDMax` DECIMAL NULL,
340 `TrotHorizontalThust` DECIMAL NULL,
341 `CanterMaxTime` DECIMAL NULL,
342 `CanterMinTime` DECIMAL NULL,
343 `CanterminPKS` DECIMAL NULL,
344 `CanterMaxPKS` DECIMAL NULL,
345 `CanterMinIntegral` DECIMAL NULL,
346 `CanterPeakNum` DECIMAL NULL,
347 `CanterBreakdiff` DECIMAL NULL,
348 `CanterPSDMin` DECIMAL NULL,
349 `CanterPSDMax` DECIMAL NULL,
350 `CanterHorizontalThust` DECIMAL NULL,
351 `CanterMaxdata` DECIMAL NULL,
352 `JumpBreakdiff` DECIMAL NULL,
353 `JumpAcceldiff` DECIMAL NULL,
354 `JumpGyroXInt` DECIMAL NULL,
355 PRIMARY KEY (`ID`),
356 INDEX `ID_idx` (`resultsID` ASC),
357 CONSTRAINT `ID1`
358 FOREIGN KEY (`resultsID`)
359 REFERENCES `ariondb`.`results` (`ID`)
360 ON DELETE NO ACTION
361 ON UPDATE NO ACTION)
362ENGINE = InnoDB;
363
364
365SET SQL_MODE=@OLD_SQL_MODE;
366SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
367SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;