film-03-11

-- phpMyAdmin SQL Dump
-- version 4.9.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Creato il: Nov 03, 2019 alle 22:41
-- Versione del server: 10.4.8-MariaDB
-- Versione PHP: 7.3.10

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `chiabre`
--

-- --------------------------------------------------------

--
-- Struttura della tabella `film`
--

CREATE TABLE `film` (
  `film_id` smallint(5) UNSIGNED NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `release_year` year(4) DEFAULT NULL,
  `image` varchar(50) NOT NULL,
  `category` varchar(30) NOT NULL,
  `category_id` tinyint(3) UNSIGNED NOT NULL,
  `director` varchar(50) NOT NULL,
  `director_id` smallint(5) NOT NULL,
  `language_id` tinyint(3) UNSIGNED NOT NULL,
  `original_language_id` tinyint(3) UNSIGNED DEFAULT NULL,
  `last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dump dei dati per la tabella `film`
--

INSERT INTO `film` (`film_id`, `title`, `description`, `release_year`, `image`, `category`, `category_id`, `director`, `director_id`, `language_id`, `original_language_id`, `last_update`) VALUES
(1, 'Batman', '<p>Batman</p>', 1989, 'batman89_poster.jpg', 'Action', 1, 'Tim Burton', 2, 2, 1, '2019-10-28 19:41:10'),
(2, 'Exorcist', '<p>Primo film della trilogia horror</p>', 1973, 'exorcist73_poster.jpg', 'Horror', 11, 'William Friedkin', 1, 2, 1, '2019-10-29 23:27:36'),
(3, 'Blade Runner', '<p>Ambientato nel 2019 in una Los Angeles distopica, dove replicanti dalle stesse sembianze dell\'uomo vengono abitualmente fabbricati e utilizzati come forza lavoro nelle colonie extra-terrestri.</p>', 1982, 'bladerunner82_poster.jpg', 'Sci-Fi', 14, 'Ridley Scott', 3, 2, 1, '2019-10-28 22:11:14'),
(4, 'Solomon Kane', '<p>La storia narra le origini del mercenario Solomon Kane.</p>', 2009, 'solomonkane_poster.jpg', 'Fantastic-Horror', 21, 'Michael J Bassett', 4, 2, 1, '2019-10-28 22:11:14'),
(5, 'Exorcist II: the Heretic', '<p>Secondo film della trilogia horror</p>', 1977, 'exorcist_eretic_poster.jpg', 'Horror', 11, 'John Boorman', 5, 2, 1, '2019-10-29 00:50:45');

--
-- Trigger `film`
--
DELIMITER $$
CREATE TRIGGER `del_film` AFTER DELETE ON `film` FOR EACH ROW BEGIN
    DELETE FROM film_text WHERE film_id = old.film_id;
  END
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW BEGIN
    INSERT INTO film_text (film_id, title, description)
        VALUES (new.film_id, new.title, new.description);
  END
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `upd_film` AFTER UPDATE ON `film` FOR EACH ROW BEGIN
    IF (old.title != new.title) OR (old.description != new.description) OR (old.film_id != new.film_id)
    THEN
        UPDATE film_text
            SET title=new.title,
                description=new.description,
                film_id=new.film_id
        WHERE film_id=old.film_id;
    END IF;
  END
$$
DELIMITER ;

--
-- Indici per le tabelle scaricate
--

--
-- Indici per le tabelle `film`
--
ALTER TABLE `film`
  ADD PRIMARY KEY (`film_id`),
  ADD KEY `idx_title` (`title`),
  ADD KEY `idx_fk_language_id` (`language_id`),
  ADD KEY `idx_fk_original_language_id` (`original_language_id`),
  ADD KEY `fk_director` (`director_id`);

--
-- AUTO_INCREMENT per le tabelle scaricate
--

--
-- AUTO_INCREMENT per la tabella `film`
--
ALTER TABLE `film`
  MODIFY `film_id` smallint(5) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- Limiti per le tabelle scaricate
--

--
-- Limiti per la tabella `film`
--
ALTER TABLE `film`
  ADD CONSTRAINT `fk_director` FOREIGN KEY (`director_id`) REFERENCES `director` (`director_id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;