Database.sql

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
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 utf8 */;

DROP DATABASE `projet`;

CREATE TABLE IF NOT EXISTS `Album` (
`Id` int(11) NOT NULL,
  `Nom` text NOT NULL,
  `Jaquette` varchar(100) NOT NULL,
  `Annee` int(11) NOT NULL,
  `ArtisteId` int(11) NOT NULL,
  `Compositeur` text NOT NULL,
  `Redacteur` text NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `Artiste` (
`Id` int(11) NOT NULL,
  `Nom` varchar(40) NOT NULL,
  `Prenom` varchar(40) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `Commentaire` (
`Id` int(11) NOT NULL,
  `Text` text NOT NULL,
  `TitreId` int(11) NOT NULL,
  `Auteur` text NOT NULL,
  `Date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `Titre` (
`Id` int(11) NOT NULL,
  `Nom` text,
  `Duree` int(11) NOT NULL,
  `Date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `AlbumId` int(11) NOT NULL,
  `AvisF` int(11) NOT NULL,
  `AvisD` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `Utilisateur` (
`Id` int(11) NOT NULL,
  `login` varchar(50) NOT NULL,
  `motdepasse` varchar(128) NOT NULL,
  `role` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;


ALTER TABLE `Album`
 ADD PRIMARY KEY (`Id`), ADD KEY `ArtisteId` (`ArtisteId`);

ALTER TABLE `Artiste`
 ADD PRIMARY KEY (`Id`);

ALTER TABLE `Commentaire`
 ADD PRIMARY KEY (`Id`), ADD KEY `TitreId_2` (`TitreId`);

ALTER TABLE `Titre`
 ADD PRIMARY KEY (`Id`), ADD KEY `AlbumId` (`AlbumId`);

ALTER TABLE `Utilisateur`
 ADD PRIMARY KEY (`Id`), ADD UNIQUE KEY `login` (`login`);


ALTER TABLE `Album`
MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=4;
ALTER TABLE `Artiste`
MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=3;
ALTER TABLE `Commentaire`
MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;
ALTER TABLE `Titre`
MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=13;
ALTER TABLE `Utilisateur`
MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=9;

ALTER TABLE `Album`
ADD CONSTRAINT `AlbumArtisteId` FOREIGN KEY (`ArtisteId`) REFERENCES `Artiste` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `Commentaire`
ADD CONSTRAINT `CommTitreId` FOREIGN KEY (`TitreId`) REFERENCES `Titre` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `Titre`
ADD CONSTRAINT `TitreAlbumId` FOREIGN KEY (`AlbumId`) REFERENCES `Album` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE;

/*!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 */;