gViBnTr2

1CREATE TABLE IF NOT EXISTS `users` (
2  `id` INT NOT NULL AUTO_INCREMENT,
3  `username` VARCHAR(255) NOT NULL,
4  `email` VARCHAR(255) NOT NULL,
5  `password` VARCHAR(255) NOT NULL,
6  `active` TINYINT(1) NOT NULL DEFAULT 0,
7  `key` VARCHAR(255) NULL,
8  `key_type` TINYINT(2) NULL,
9  `key_expire` TIMESTAMP NULL,
10  `level` TINYINT(10) NULL DEFAULT 0,
11  `created` TIMESTAMP NOT NULL DEFAULT NOW(),
12  `banned` TINYINT(1) NOT NULL DEFAULT 0,
13  PRIMARY KEY (`id`),
14  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
15  UNIQUE INDEX `key_UNIQUE` (`key` ASC) )
16ENGINE = InnoDB;
17
18CREATE TABLE IF NOT EXISTS `trainers` ( `id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL, `description` TEXT NOT NULL, `instagram` VARCHAR(255) NULL, `facebook` VARCHAR(255) NULL, `website` VARCHAR(255) NULL, `img` BLOB NOT NULL, `account` INT NULL, PRIMARY KEY (`id`), INDEX `fk_userid_idx` (`account` ASC) , CONSTRAINT `fk_userid` FOREIGN KEY (`account`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;
19
20CREATE TABLE IF NOT EXISTS `plans` (
21  `id` INT NOT NULL AUTO_INCREMENT,
22  `title` VARCHAR(255) NOT NULL,
23  `description` TEXT NOT NULL,
24  `price` FLOAT UNSIGNED NOT NULL,
25  `promotion` FLOAT NULL,
26  `image` BLOB NOT NULL,
27  `trainer` INT NULL,
28  `duration` INT UNSIGNED NOT NULL,
29  PRIMARY KEY (`id`),
30  INDEX `fk_trainerid_idx` (`trainer` ASC) ,
31  CONSTRAINT `fk_trainerid`
32    FOREIGN KEY (`trainer`)
33    REFERENCES `trainers` (`id`)
34    ON DELETE NO ACTION
35    ON UPDATE CASCADE)
36ENGINE = InnoDB;
37
38CREATE TABLE IF NOT EXISTS `users_plans` (
39  `id` INT NOT NULL AUTO_INCREMENT,
40  `userid` INT NOT NULL,
41  `planid` INT NOT NULL,
42  `expire` DATETIME NOT NULL,
43  PRIMARY KEY (`id`),
44  INDEX `fk_user_idx` (`userid` ASC) ,
45  INDEX `fk_plan_idx` (`planid` ASC) ,
46  CONSTRAINT `fk_user`
47    FOREIGN KEY (`userid`)
48    REFERENCES `users` (`id`)
49    ON DELETE NO ACTION
50    ON UPDATE CASCADE,
51  CONSTRAINT `fk_plan`
52    FOREIGN KEY (`planid`)
53    REFERENCES `plans` (`id`)
54    ON DELETE NO ACTION
55    ON UPDATE CASCADE)
56ENGINE = InnoDB;
57
58CREATE TABLE IF NOT EXISTS `exercise` ( `id` INT NOT NULL, `date` DATE NOT NULL, `content` TEXT NOT NULL, `info` TEXT NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB;
59
60CREATE TABLE IF NOT EXISTS `exercises_to_plans` ( `id` INT NOT NULL AUTO_INCREMENT, `planid` INT NOT NULL, `exerciseis` INT NOT NULL, PRIMARY KEY (`id`), INDEX `fk_to_plan_idx` (`planid` ASC) , INDEX `fk_to_exercice_idx` (`exerciseis` ASC) , CONSTRAINT `fk_to_plan` FOREIGN KEY (`planid`) REFERENCES `plans` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_to_exercice` FOREIGN KEY (`exerciseis`) REFERENCES `exercise` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB;