Build-Database.sql

-- Admin
-- Warning: Running This Script Will Change Root Password!
-- Warning: Running This Script Will Reset Database.MediaCollection!
-- Warning: All Users Has Execute Rights In This Schema!

-- Reset the schema.
DROP SCHEMA IF EXISTS mediacollection;

-- Create DB
CREATE SCHEMA IF NOT EXISTS mediacollection;
USE mediacollection;

-- Generate tables

-- Create User, Contains password and username for use in the post-review/post-rating procedures.
CREATE TABLE IF NOT EXISTS Account(
		Id INTEGER AUTO_INCREMENT PRIMARY KEY,
		Name VARCHAR(24) NOT NULL, 			-- Allowed read, not required in logins, displayed in reviews etc.
        User VARCHAR(32) NOT NULL UNIQUE,	-- Disallow read of this column.
        Pass VARCHAR(32) NOT NULL 			-- Disallow read of this column. Password stored in cleartext. :'(
);

-- Account used when values are added to the database from "admin" (in Build-TestData)
INSERT INTO Account(Name, User, Pass) VALUES ('Admin', 'Admin-MediaCollection', 'zuAZpOpvPasB4JD7ka7GW8DCXGiAN4');

-- Create Director/Artist
CREATE TABLE IF NOT EXISTS Creator(
		Id INTEGER AUTO_INCREMENT, 
		Name VARCHAR(24) NOT NULL, 
    PRIMARY KEY (Id)
);
    
CREATE INDEX NameIndex ON Creator (Name);

-- Create Genre
CREATE TABLE IF NOT EXISTS Genre(
		Id INTEGER AUTO_INCREMENT, 
        Name VARCHAR(24) NOT NULL, 
	PRIMARY KEY (Id)
);

-- Create Media Types
CREATE TABLE IF NOT EXISTS Mediatype(
		Id INTEGER AUTO_INCREMENT,
        Name VARCHAR(16) NOT NULL,
	PRIMARY KEY (Id)
);

-- Base types.
INSERT INTO Mediatype (Name) VALUES ("Album");
INSERT INTO Mediatype (Name) VALUES ("Movie");
INSERT INTO Mediatype (Name) VALUES ("E-Book");
INSERT INTO Genre (Name) VALUES ("Undefined"); -- Genre defaults to undefined.

-- Create Movie/Album
CREATE TABLE IF NOT EXISTS Media(
		Id INTEGER AUTO_INCREMENT, 
        Mediatype_Id INTEGER, 
        Genre_Id INTEGER DEFAULT 1, 
        Title VARCHAR(42) NOT NULL, 
        Year VARCHAR(16), 
        Duration INTEGER, 
        Account_Id INTEGER DEFAULT 1,
	PRIMARY KEY (Id), 
	FOREIGN KEY (Mediatype_Id) REFERENCES Mediatype(Id),
    FOREIGN KEY (Account_Id) REFERENCES Account(Id) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (Genre_Id) REFERENCES Genre(Id) ON DELETE CASCADE ON UPDATE CASCADE
);
    
-- Add Indexing Media on Media Type and Title
CREATE INDEX MediaIndex ON Media (Mediatype_Id);
CREATE INDEX TitleIndex ON Media (Title);

-- N:M Creator <-> Media
CREATE TABLE IF NOT EXISTS Contributor(
		Creator_Id INTEGER,
        Media_Id INTEGER, 
	FOREIGN KEY (Creator_Id) REFERENCES Creator(Id) ON DELETE CASCADE ON UPDATE CASCADE, 
	FOREIGN KEY (Media_Id) REFERENCES Media(Id) ON DELETE CASCADE ON UPDATE CASCADE 
);

-- Create Rating, Username is unique so that every user may only create one rating.  
CREATE TABLE IF NOT EXISTS Rating(
		Media_Id INTEGER, 
		Account_Id INTEGER, 
		Rating INTEGER, 
	FOREIGN KEY (Account_Id) REFERENCES Account(Id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (Media_Id) REFERENCES Media(Id) ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY (Account_Id, Media_Id)
);

-- Create Review, Username is unique so that every user may only create one review.
CREATE TABLE IF NOT EXISTS Review(
		Media_Id INTEGER, 
        Account_Id INTEGER, 
        Title VARCHAR(24) NOT NULL, 
		Text VARCHAR(500) NOT NULL,
	 FOREIGN KEY (Account_Id)  REFERENCES Account(Id) ON DELETE CASCADE ON UPDATE CASCADE,
	 FOREIGN KEY (Media_Id)    REFERENCES Media(Id) ON DELETE CASCADE ON UPDATE CASCADE,
     PRIMARY KEY (Account_Id, Media_Id)
);



-- Create a database user with read access for every normal user, the user does not need to supply
-- Login credentials when reading from the database. Login information will be sent when required by the 
-- Database, when creating a review or rating. 
DROP USER 'clientapp'@'localhost';
CREATE USER 'clientapp'@'localhost' IDENTIFIED BY 'qwerty'; -- the password is on github and in the application.

-- Grant select on all columns in the following tables 
GRANT EXECUTE ON mediacollection.* TO 'clientapp'@'localhost'; 
GRANT SELECT ON mediacollection.Mediatype TO 'clientapp'@'localhost';	
GRANT SELECT ON mediacollection.Media TO 'clientapp'@'localhost';	
GRANT SELECT ON mediacollection.Contributor TO 'clientapp'@'localhost';	
GRANT SELECT ON mediacollection.Creator TO 'clientapp'@'localhost';	
GRANT SELECT ON mediacollection.Genre TO 'clientapp'@'localhost';	
GRANT SELECT ON mediacollection.Rating TO 'clientapp'@'localhost';	
GRANT SELECT ON mediacollection.Review TO 'clientapp'@'localhost';	

-- Do not allow access to Account passwords. Accountname is exposed, add nick-name?
GRANT SELECT (Name, Id) ON mediacollection.Account TO 'clientapp'@'localhost'; 
-- The admin logs on with write access, enabling the creation of accounts in User table.
-- Media.

-- ----------------------- STORED PROCEDURES ----------------------------------
DROP PROCEDURE IF EXISTS AddCreator;

DELIMITER $$
CREATE PROCEDURE VerifyAccount(
in p_user varchar(32),
in p_pass varchar(32),
out s_accountId int)
BEGIN
	SET s_accountId := -1;
    SELECT Account.Id INTO s_accountId FROM Account WHERE (Account.User = p_user AND Account.pass = p_pass);
END$$

DELIMITER $$
CREATE PROCEDURE AddCreator(
in p_user varchar(32),
in p_pass varchar(32),
in p_creator varchar(32),
in p_mediaId int)
BEGIN
	DECLARE MediaId int; 
    DECLARE AccountId int;
    DECLARE CreatorId int;
    
    SELECT Account.Id INTO AccountId FROM Account WHERE (Account.User = p_user AND Account.pass = p_pass);
    
    IF AccountId IS NOT NULL THEN
		-- if not exist creator then create
        IF (NOT EXISTS (SELECT * FROM Creator WHERE Creator.Name = p_creator)) THEN 
			INSERT INTO Creator(Name) VALUES (p_creator); 
        END IF; 
        
		SELECT Id INTO CreatorId FROM Creator WHERE Name = p_creator;
		INSERT INTO Contributor(Media_Id, Creator_Id) VALUES (p_mediaId, CreatorId);
	END IF;
END$$


DROP PROCEDURE IF EXISTS AddMedia;

DELIMITER $$
CREATE PROCEDURE AddMedia(
in p_user varchar(32),
in p_pass varchar(32),
in p_title varchar(42),
in p_year varchar(16),
in p_genre varchar(16),
in p_duration int,
in p_mediatype int,
out r_mediaId int) 
BEGIN
	DECLARE MediaId int;
    DECLARE AccountId int;
    DECLARE GenreId int;
    
    SELECT Account.Id INTO AccountId FROM Account WHERE (Account.User = p_user AND Account.pass = p_pass);
    
    IF AccountId IS NOT NULL THEN
		-- if not exist genre then create
        IF (NOT EXISTS (SELECT * FROM Genre WHERE Genre.Name = p_genre)) THEN 
			INSERT INTO Genre(Name) VALUES (p_genre); 
        END IF; 
        
		SELECT Id INTO GenreId FROM Genre WHERE Name = p_genre;
		INSERT INTO Media(Title, Year, Duration, MediaType_Id, Genre_Id, Account_Id) VALUES (p_title, p_year, p_duration, p_mediatype, GenreId, AccountId);
        SELECT LAST_INSERT_ID() INTO r_mediaId;
	END IF;
END$$

DROP PROCEDURE IF EXISTS MakeReview;

DELIMITER $$
CREATE PROCEDURE MakeReview(
    in p_user varchar(32), 
    in p_pass  varchar(32),
    in p_title varchar(32),  
    in p_text varchar(500),
    in p_media int)
BEGIN
    DECLARE AccountId int;
 
    SELECT Account.Id INTO AccountId FROM Account WHERE (Account.User = p_user AND Account.pass = p_pass);
 
    IF AccountId > 0 THEN
		INSERT INTO Review (Media_Id, Account_Id, Title, Text) VALUES (p_media, AccountId, p_title, p_text);
    END IF;
 
END$$

DROP PROCEDURE IF EXISTS Rate;

DELIMITER $$
-- CREATE DEFINER=`root`@`localhost` PROCEDURE `Rate`(mediaId integer, user varchar(32), pass varchar(32), rating integer)
 --    MODIFIES SQL DATA
CREATE PROCEDURE Rate(mediaId integer, user varchar(32), pass varchar(32), rating integer)
BEGIN
	declare YES integer;

SELECT 
    Id
INTO YES FROM
    Account
WHERE
    user = Account.user
        AND pass = Account.pass;
		
            -- username & pwd == entry in Account
    if (YES is not null) then 
		insert into Rating (Media_Id, Account_Id, Rating) VALUES (MediaId, YES, rating);
	end if;

END$$