database.sql

-- MySQL Script generated by MySQL Workbench
-- Fri Nov 24 21:39:31 2023
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema HPMS
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema HPMS
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `HPMS` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;
USE `HPMS` ;

-- -----------------------------------------------------
-- Table `HPMS`.`User`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `HPMS`.`User` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NULL DEFAULT NULL,
  `dob` DATE NULL DEFAULT NULL,
  `profile_image` BLOB NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `email` (`email` ASC) VISIBLE)
ENGINE = InnoDB
AUTO_INCREMENT = 109
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `HPMS`.`artist`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `HPMS`.`artist` (
  `artist_id` INT NOT NULL AUTO_INCREMENT,
  `artist_name` VARCHAR(255) NULL DEFAULT NULL,
  PRIMARY KEY (`artist_id`))
ENGINE = InnoDB
AUTO_INCREMENT = 4213
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `HPMS`.`album`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `HPMS`.`album` (
  `album_id` INT NOT NULL AUTO_INCREMENT,
  `artist_id` INT NULL DEFAULT NULL,
  `doc` DATE NULL DEFAULT NULL,
  `album_name` VARCHAR(255) NULL DEFAULT NULL,
  PRIMARY KEY (`album_id`),
  INDEX `artist_id` (`artist_id` ASC) VISIBLE,
  INDEX `idx_album_name` (`album_name` ASC) VISIBLE,
  CONSTRAINT `album_ibfk_1`
    FOREIGN KEY (`artist_id`)
    REFERENCES `HPMS`.`artist` (`artist_id`))
ENGINE = InnoDB
AUTO_INCREMENT = 2620
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `HPMS`.`track`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `HPMS`.`track` (
  `track_id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `duration` INT NULL DEFAULT NULL,
  `audio_blob` MEDIUMBLOB NULL DEFAULT NULL,
  `audio_url` VARCHAR(255) NULL DEFAULT NULL,
  `image_url` VARCHAR(255) NULL DEFAULT NULL,
  `image_blob` BLOB NULL DEFAULT NULL,
  PRIMARY KEY (`track_id`),
  INDEX `idx_track_name` (`name` ASC) VISIBLE)
ENGINE = InnoDB
AUTO_INCREMENT = 4180
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `HPMS`.`album_songs`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `HPMS`.`album_songs` (
  `album_id` INT NULL DEFAULT NULL,
  `track_id` INT NULL DEFAULT NULL,
  INDEX `album_id` (`album_id` ASC) VISIBLE,
  INDEX `track_id` (`track_id` ASC) VISIBLE,
  CONSTRAINT `album_songs_ibfk_1`
    FOREIGN KEY (`album_id`)
    REFERENCES `HPMS`.`album` (`album_id`),
  CONSTRAINT `album_songs_ibfk_2`
    FOREIGN KEY (`track_id`)
    REFERENCES `HPMS`.`track` (`track_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `HPMS`.`composed`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `HPMS`.`composed` (
  `track_id` INT NULL DEFAULT NULL,
  `artist_id` INT NULL DEFAULT NULL,
  INDEX `track_id` (`track_id` ASC) VISIBLE,
  INDEX `artist_id` (`artist_id` ASC) VISIBLE,
  CONSTRAINT `composed_ibfk_1`
    FOREIGN KEY (`track_id`)
    REFERENCES `HPMS`.`track` (`track_id`),
  CONSTRAINT `composed_ibfk_2`
    FOREIGN KEY (`artist_id`)
    REFERENCES `HPMS`.`artist` (`artist_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `HPMS`.`playlist`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `HPMS`.`playlist` (
  `playlist_id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL DEFAULT NULL,
  `image_blob` MEDIUMBLOB NULL DEFAULT NULL,
  `doc` DATE NULL DEFAULT NULL,
  `owner_id` INT NULL DEFAULT NULL,
  PRIMARY KEY (`playlist_id`),
  INDEX `owner_id` (`owner_id` ASC) VISIBLE,
  INDEX `idx_playlist_name` (`name` ASC) VISIBLE,
  CONSTRAINT `playlist_ibfk_1`
    FOREIGN KEY (`owner_id`)
    REFERENCES `HPMS`.`User` (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 21
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `HPMS`.`playlist_songs`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `HPMS`.`playlist_songs` (
  `playlist_id` INT NULL DEFAULT NULL,
  `track_id` INT NULL DEFAULT NULL,
  `sno` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`sno`),
  INDEX `track_id` (`track_id` ASC) VISIBLE,
  INDEX `track_fk` (`playlist_id` ASC) VISIBLE,
  CONSTRAINT `playlist_songs_ibfk_2`
    FOREIGN KEY (`track_id`)
    REFERENCES `HPMS`.`track` (`track_id`),
  CONSTRAINT `track_fk`
    FOREIGN KEY (`playlist_id`)
    REFERENCES `HPMS`.`playlist` (`playlist_id`)
    ON DELETE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 2193
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `HPMS`.`podcast`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `HPMS`.`podcast` (
  `podcast_id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `doc` DATE NULL DEFAULT NULL,
  `duration` TIME NULL DEFAULT NULL,
  `user_id` INT NULL DEFAULT NULL,
  `image_blob` BLOB NULL DEFAULT NULL,
  `audio_blob` LONGBLOB NULL DEFAULT NULL,
  PRIMARY KEY (`podcast_id`),
  INDEX `user_id` (`user_id` ASC) VISIBLE,
  INDEX `idx_podcast_name` (`name` ASC) VISIBLE,
  CONSTRAINT `podcast_ibfk_1`
    FOREIGN KEY (`user_id`)
    REFERENCES `HPMS`.`User` (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 5
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `HPMS`.`popular_tracks`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `HPMS`.`popular_tracks` (
  `track_id` INT NOT NULL,
  `stream_count` INT NOT NULL DEFAULT '1',
  PRIMARY KEY (`track_id`),
  CONSTRAINT `FK`
    FOREIGN KEY (`track_id`)
    REFERENCES `HPMS`.`track` (`track_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `HPMS`.`recently_played`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `HPMS`.`recently_played` (
  `user_id` INT NULL DEFAULT NULL,
  `order` INT NULL DEFAULT NULL,
  `track_id` INT NULL DEFAULT NULL,
  INDEX `user_id` (`user_id` ASC) VISIBLE,
  INDEX `track_id` (`track_id` ASC) VISIBLE,
  CONSTRAINT `recently_played_ibfk_1`
    FOREIGN KEY (`user_id`)
    REFERENCES `HPMS`.`User` (`id`),
  CONSTRAINT `recently_played_ibfk_2`
    FOREIGN KEY (`track_id`)
    REFERENCES `HPMS`.`track` (`track_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `HPMS`.`track_stream_event`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `HPMS`.`track_stream_event` (
  `track_id` INT NOT NULL,
  `stream_date` DATE NULL DEFAULT curdate(),
  INDEX `track_id` (`track_id` ASC) VISIBLE,
  CONSTRAINT `track_stream_event_ibfk_1`
    FOREIGN KEY (`track_id`)
    REFERENCES `HPMS`.`track` (`track_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `HPMS`.`user_playlist`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `HPMS`.`user_playlist` (
  `playlist_id` INT NULL DEFAULT NULL,
  `user_id` INT NULL DEFAULT NULL,
  UNIQUE INDEX `unique_combination` (`user_id` ASC, `playlist_id` ASC) VISIBLE,
  INDEX `playlist_id` (`playlist_id` ASC) VISIBLE,
  CONSTRAINT `user_playlist_ibfk_1`
    FOREIGN KEY (`playlist_id`)
    REFERENCES `HPMS`.`playlist` (`playlist_id`),
  CONSTRAINT `user_playlist_ibfk_2`
    FOREIGN KEY (`user_id`)
    REFERENCES `HPMS`.`User` (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

USE `HPMS` ;

-- -----------------------------------------------------
-- procedure DeleteRows
-- -----------------------------------------------------

DELIMITER $$
USE `HPMS`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `DeleteRows`()
BEGIN
        DELETE FROM recently_played
        WHERE `order`>10;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure InsertPlaylistTrack
-- -----------------------------------------------------

DELIMITER $$
USE `HPMS`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertPlaylistTrack`(
    IN PID INT,
    IN OID INT,
    IN TID INT
)
BEGIN
    DECLARE OWNER INT;
    SELECT owner_id INTO OWNER FROM playlist WHERE playlist_id=PID;
    IF OWNER=OID THEN
            INSERT INTO playlist_songs (playlist_id,track_id) VALUE(PID,TID);
    

    ELSE
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT ="USER NOT AUTHORIZED TO INSERT SONGS IN THIS PLAYLIST";
    END IF;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure RetrieveItemDetailsByID
-- -----------------------------------------------------

DELIMITER $$
USE `HPMS`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `RetrieveItemDetailsByID`(
    IN itemType ENUM('track', 'album', 'podcast', 'playlist'),
    IN itemID INT
)
BEGIN
    CASE itemType
        WHEN 'track' THEN
            SELECT track.audio_url, track.audio_blob
            FROM track
            WHERE track.track_id = itemID;

        WHEN 'album' THEN
            SELECT track.track_id, track.name, track.image_blob, track.image_url
            FROM album
            LEFT JOIN album_songs ON album.album_id = album_songs.album_id
            LEFT JOIN track ON album_songs.track_id = track.track_id
            WHERE album.album_id = itemID;

        WHEN 'podcast' THEN
            SELECT podcast.audio_blob
            FROM podcast
            WHERE podcast.podcast_id = itemID;

        WHEN 'playlist' THEN
            SELECT track.track_id, track.name, track.image_blob, track.image_url
            FROM playlist
            LEFT JOIN playlist_songs ON playlist.playlist_id = playlist_songs.playlist_id
            LEFT JOIN track ON playlist_songs.track_id = track.track_id
            WHERE playlist.playlist_id = itemID;
    END CASE;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure SearchItemsByTypeAndName
-- -----------------------------------------------------

DELIMITER $$
USE `HPMS`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `SearchItemsByTypeAndName`(
    IN itemType ENUM('track', 'album', 'podcast', 'playlist'),
    IN partialName VARCHAR(255)
)
BEGIN
    SET @partialName = CONCAT('%', partialName, '%');

    IF itemType = 'track' THEN
        SET @query = CONCAT(
            'SELECT track_id, name, image_url, image_blob, artist_name ',
            'FROM (SELECT t.track_id, t.name, t.image_url, t.image_blob, a.artist_name ',
            'FROM track t ',
            'LEFT JOIN composed c ON t.track_id = c.track_id ',
            'LEFT JOIN artist a ON c.artist_id = a.artist_id ',
            'WHERE t.name LIKE ? LIMIT 10) AS result;'
        );
    ELSEIF itemType = 'album' THEN
        SET @query = CONCAT(
            'SELECT album_id, album_name, artist_name ',
            'FROM (SELECT a.album_id, a.album_name, ar.artist_name ',
            'FROM album a ',
            'INNER JOIN artist ar ON a.artist_id = ar.artist_id ' ,
            'WHERE a.album_name LIKE ? LIMIT 10) AS result;'
        );
    ELSEIF itemType = 'podcast' THEN
        SET @query = CONCAT(
            'SELECT podcast_id, name, image_blob, username ',
            'FROM (SELECT p.podcast_id, p.name, p.image_blob, u.username ',
            'FROM podcast p ',
            'INNER JOIN User u ON p.user_id = u.id ',
            'WHERE p.name LIKE ? LIMIT 10) AS result;'
        );
    ELSEIF itemType = 'playlist' THEN
        SET @query = CONCAT(
            'SELECT playlist_id, name, username, image_blob ',
            'FROM (SELECT pl.playlist_id, pl.name, u.username, pl.image_blob ',
            'FROM playlist pl ',
            'INNER JOIN User u ON pl.owner_id = u.id ',
            'WHERE pl.name LIKE ? LIMIT 10) AS result;'
        );
    ELSE
        SET @query = CONCAT(
            'SELECT * FROM ', itemType,
            ' WHERE name LIKE ? LIMIT 10;'
        );
    END IF;
    PREPARE stmt FROM @query;
    EXECUTE stmt USING @partialName;
    DEALLOCATE PREPARE stmt;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure UpdateRecentlyPlayed
-- -----------------------------------------------------

DELIMITER $$
USE `HPMS`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateRecentlyPlayed`(
    IN uid INT,
    IN tid INT
)
BEGIN
    DECLARE track_order INT;

    SELECT `order` INTO track_order
    FROM recently_played
    WHERE user_id = uid AND track_id = tid LIMIT 1;

    IF track_order IS NOT NULL THEN
        UPDATE recently_played
        SET `order` = 1
        WHERE user_id = uid AND track_id = tid;

        UPDATE recently_played
        SET `order` = `order` + 1
        WHERE user_id = uid AND track_id <> tid AND `order`<track_order;
    ELSE
        INSERT INTO recently_played (user_id, track_id, `order`)
        VALUES (uid, tid, 1);

        UPDATE recently_played
        SET `order` = `order` + 1
        WHERE user_id = uid and track_id!=tid;

        DELETE FROM recently_played
        WHERE user_id = uid AND `order` > 10;
    END IF;
END$$

DELIMITER ;
USE `HPMS`;

DELIMITER $$
USE `HPMS`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `HPMS`.`checkPlaylist`
BEFORE INSERT ON `HPMS`.`playlist`
FOR EACH ROW
BEGIN
    DECLARE ROWCOUNT INT;
    SELECT COUNT(*) INTO ROWCOUNT FROM playlist WHERE name = NEW.name AND owner_id = NEW.owner_id;

    IF ROWCOUNT > 0 THEN 
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Duplicate record: This playlist_id already exists';
    END IF;
END$$
DELIMITER ;

DELIMITER $$
USE `HPMS`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `HPMS`.`checkSong`
BEFORE INSERT ON `HPMS`.`playlist_songs`
FOR EACH ROW
BEGIN
    DECLARE ROWCOUNT INT;
    SELECT COUNT(*) INTO ROWCOUNT FROM playlist_songs WHERE track_id = NEW.track_id AND playlist_id = NEW.playlist_id;

    IF ROWCOUNT > 0 THEN 
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Duplicate record: This combination of playlist_id and track_id already exists';
    END IF;
END$$

DELIMITER ;

DELIMITER $$

USE `HPMS`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `HPMS`.`UpdatePopular`
AFTER INSERT ON `HPMS`.`track_stream_event`
FOR EACH ROW
BEGIN 

    DECLARE sc INT;
    SELECT COUNT(*) INTO sc FROM popular_tracks WHERE track_id=NEW.track_id;

    IF sc>0 THEN
        UPDATE popular_tracks
        SET stream_count=stream_count+1
        WHERE track_id=NEW.track_id;
    ELSE
        INSERT INTO popular_tracks VALUES (NEW.track_id,1);
    END IF;

END$$


DELIMITER ;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;