QifFY41f

1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
6
7-- -----------------------------------------------------
8-- Schema mydb
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema mydb
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
15USE `mydb` ;
16
17-- -----------------------------------------------------
18-- Table `mydb`.`user`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `mydb`.`user` (
21  `id` INT NOT NULL AUTO_INCREMENT,
22  `first_name` VARCHAR(30) NULL,
23  `last_name` VARCHAR(30) NULL,
24  `email` VARCHAR(50) NULL,
25  `deleted_at` TIMESTAMP NULL,
26  `modified_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
27  `username` VARCHAR(40) NOT NULL,
28  `password` VARCHAR(260) NOT NULL,
29  PRIMARY KEY (`id`),
30  UNIQUE INDEX `username_UNIQUE` (`username` ASC))
31ENGINE = InnoDB;
32
33
34-- -----------------------------------------------------
35-- Table `mydb`.`playlist`
36-- -----------------------------------------------------
37CREATE TABLE IF NOT EXISTS `mydb`.`playlist` (
38  `id` INT NOT NULL AUTO_INCREMENT,
39  `name` VARCHAR(60) NOT NULL,
40  `user_id` INT NOT NULL,
41  `modified_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
42  `deleted_at` TIMESTAMP NULL,
43  PRIMARY KEY (`id`, `user_id`),
44  INDEX `fk_Playlist_User_idx` (`user_id` ASC),
45  CONSTRAINT `fk_Playlist_User`
46    FOREIGN KEY (`user_id`)
47    REFERENCES `mydb`.`user` (`id`)
48    ON DELETE NO ACTION
49    ON UPDATE NO ACTION)
50ENGINE = InnoDB;
51
52
53-- -----------------------------------------------------
54-- Table `mydb`.`song`
55-- -----------------------------------------------------
56CREATE TABLE IF NOT EXISTS `mydb`.`song` (
57  `id` INT NOT NULL AUTO_INCREMENT,
58  `title` VARCHAR(50) NULL,
59  `author` VARCHAR(50) NULL,
60  `year` INT NULL,
61  `file_url` VARCHAR(300) NOT NULL,
62  PRIMARY KEY (`id`))
63ENGINE = InnoDB;
64
65
66-- -----------------------------------------------------
67-- Table `mydb`.`playlist_song`
68-- -----------------------------------------------------
69CREATE TABLE IF NOT EXISTS `mydb`.`playlist_song` (
70  `playlist_id` INT NOT NULL,
71  `song_id` INT NOT NULL,
72  `modified_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
73  `deleted_at` TIMESTAMP NULL,
74  PRIMARY KEY (`playlist_id`, `song_id`),
75  INDEX `fk_PlaylistSong_Song1_idx` (`song_id` ASC),
76  CONSTRAINT `fk_PlaylistSong_Playlist1`
77    FOREIGN KEY (`playlist_id`)
78    REFERENCES `mydb`.`playlist` (`id`)
79    ON DELETE NO ACTION
80    ON UPDATE NO ACTION,
81  CONSTRAINT `fk_PlaylistSong_Song1`
82    FOREIGN KEY (`song_id`)
83    REFERENCES `mydb`.`song` (`id`)
84    ON DELETE NO ACTION
85    ON UPDATE NO ACTION)
86ENGINE = InnoDB;
87
88
89-- -----------------------------------------------------
90-- Table `mydb`.`favorite`
91-- -----------------------------------------------------
92CREATE TABLE IF NOT EXISTS `mydb`.`favorite` (
93  `user_id` INT NOT NULL,
94  `song_id` INT NOT NULL,
95  `deleted_at` TIMESTAMP NULL,
96  `modified_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
97  PRIMARY KEY (`user_id`, `song_id`),
98  INDEX `fk_Favorite_Song1_idx` (`song_id` ASC),
99  CONSTRAINT `fk_Favorite_User1`
100    FOREIGN KEY (`user_id`)
101    REFERENCES `mydb`.`user` (`id`)
102    ON DELETE NO ACTION
103    ON UPDATE NO ACTION,
104  CONSTRAINT `fk_Favorite_Song1`
105    FOREIGN KEY (`song_id`)
106    REFERENCES `mydb`.`song` (`id`)
107    ON DELETE NO ACTION
108    ON UPDATE NO ACTION)
109ENGINE = InnoDB;
110
111
112SET SQL_MODE=@OLD_SQL_MODE;
113SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
114SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;