BDBDEC Documentation

Ceci est la documentation du code source du logiciel de la BDBDEC.

Ci-dessous le schéma UML de la base de données utilisée par le logiciel.

En découle l'implementation MySQL suivante :

-- phpMyAdmin SQL Dump
-- version 3.3.7deb5build0.10.10.1
-- http://www.phpmyadmin.net
--
-- Serveur: localhost
-- Généré le : Jeu 16 Juin 2011 à 18:25
-- Version du serveur: 5.1.49
-- Version de PHP: 5.3.3-1ubuntu9.5

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


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

--
-- Base de données: `bdbdec`
--

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

--
-- Doublure de structure pour la vue `actual_borrows`
--
CREATE TABLE IF NOT EXISTS `actual_borrows` (
`logb_id` int(11)
,`logb_dateBorrow` datetime
,`logb_idBook` int(11)
,`logb_idUser` int(11)
);
-- --------------------------------------------------------

--
-- Structure de la table `album`
--

CREATE TABLE IF NOT EXISTS `album` (
`al_id` int(11) NOT NULL AUTO_INCREMENT,
`al_idSerie` int(11) NOT NULL,
`al_name` varchar(65) NOT NULL,
`al_tome` int(3) DEFAULT NULL,
`al_description` text,
`al_pages` int(5) DEFAULT NULL,
PRIMARY KEY (`al_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Structure de la table `author`
--

CREATE TABLE IF NOT EXISTS `author` (
`au_id` int(11) NOT NULL AUTO_INCREMENT,
`au_name` varchar(64) DEFAULT NULL,
PRIMARY KEY (`au_id`),
UNIQUE KEY `author_au_name` (`au_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Structure de la table `book`
--

CREATE TABLE IF NOT EXISTS `book` (
`bo_id` int(11) NOT NULL AUTO_INCREMENT,
`bo_idEdition` int(11) NOT NULL,
`bo_cotation` varchar(20) NOT NULL,
`bo_location` enum('cave','butc','sortie','couvrage','reparation','remplacement','inconnu') DEFAULT 'inconnu',
`bo_condition` enum('neuf','bon','reparer','remplacer','inconnu') DEFAULT 'inconnu',
`bo_seen` datetime DEFAULT NULL,
PRIMARY KEY (`bo_id`),
UNIQUE KEY `bo_cotation` (`bo_cotation`),
KEY `bo_location` (`bo_location`),
KEY `bo_condition` (`bo_condition`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Doublure de structure pour la vue `books_out`
--
CREATE TABLE IF NOT EXISTS `books_out` (
`bo_id` int(11)
,`bo_cotation` varchar(20)
);
-- --------------------------------------------------------

--
-- Structure de la table `edition`
--

CREATE TABLE IF NOT EXISTS `edition` (
`ed_id` int(11) NOT NULL AUTO_INCREMENT,
`ed_idAlbum` int(11) NOT NULL,
`ed_idPublisher` int(11) NOT NULL,
`ed_EAN13` varchar(13) DEFAULT NULL,
`ed_price` varchar(64) DEFAULT NULL,
`ed_year` varchar(7) DEFAULT NULL,
`temp_coverOK` int(3) DEFAULT '0',
PRIMARY KEY (`ed_id`),
UNIQUE KEY `edition_ed_idAlbum_2` (`ed_idAlbum`,`ed_idPublisher`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Structure de la table `grades`
--

CREATE TABLE IF NOT EXISTS `grades` (
`gr_id` int(11) NOT NULL AUTO_INCREMENT,
`gr_name` varchar(32) NOT NULL,
`gr_type` int(3) NOT NULL,
`gr_nb_books_allowed` int(3) NOT NULL DEFAULT '0',
PRIMARY KEY (`gr_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Structure de la table `inventory`
--

CREATE TABLE IF NOT EXISTS `inventory` (
`in_id` int(11) NOT NULL AUTO_INCREMENT,
`in_startDate` datetime NOT NULL,
`in_endDate` datetime DEFAULT NULL,
`in_commentary` text,
PRIMARY KEY (`in_id`),
KEY `inventory_in_startDate` (`in_startDate`,`in_endDate`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Structure de la table `join_authors_albums`
--

CREATE TABLE IF NOT EXISTS `join_authors_albums` (
`jaut_idAlbum` int(11) NOT NULL,
`jaut_idAuthor` int(11) NOT NULL,
`jaut_typeAuthor` enum('scriptWritter','drawer','unknown') NOT NULL DEFAULT 'unknown',
PRIMARY KEY (`jaut_idAlbum`,`jaut_idAuthor`,`jaut_typeAuthor`),
KEY `jaut_idAlbum` (`jaut_idAlbum`),
KEY `jaut_typeAuthor` (`jaut_typeAuthor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Structure de la table `logs_borrows`
--

CREATE TABLE IF NOT EXISTS `logs_borrows` (
`logb_id` int(11) NOT NULL AUTO_INCREMENT,
`logb_idUser` int(11) DEFAULT NULL,
`logb_idBook` int(11) NOT NULL,
`logb_dateBorrow` datetime NOT NULL,
`logb_dateRetour` datetime DEFAULT NULL,
PRIMARY KEY (`logb_id`),
KEY `logs_borrows_logb_idBook` (`logb_idBook`),
KEY `logs_borrows_logb_idUser` (`logb_idUser`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Doublure de structure pour la vue `out_books_info`
--
CREATE TABLE IF NOT EXISTS `out_books_info` (
`bo_id` int(11)
,`bo_cotation` varchar(20)
,`logb_dateBorrow` datetime
,`us_lastname` varchar(20)
,`us_firstname` varchar(20)
,`us_email` varchar(45)
);
-- --------------------------------------------------------

--
-- Doublure de structure pour la vue `ouvrage_liaison`
--
CREATE TABLE IF NOT EXISTS `ouvrage_liaison` (
`se_id` int(11)
,`al_id` int(11)
,`ed_id` int(11)
,`pu_id` int(11)
,`bo_id` int(11)
,`bo_cotation` varchar(20)
);
-- --------------------------------------------------------

--
-- Doublure de structure pour la vue `ouvrage_liaison_auteurs`
--
CREATE TABLE IF NOT EXISTS `ouvrage_liaison_auteurs` (
`se_id` int(11)
,`al_id` int(11)
,`ed_id` int(11)
,`pu_id` int(11)
,`bo_id` int(11)
,`bo_cotation` varchar(20)
,`jaut_idAuthor` int(11)
,`jaut_typeAuthor` enum('scriptWritter','drawer','unknown')
);
-- --------------------------------------------------------

--
-- Structure de la table `password`
--

CREATE TABLE IF NOT EXISTS `password` (
`pwd` varchar(50) NOT NULL,
PRIMARY KEY (`pwd`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Structure de la table `publisher`
--

CREATE TABLE IF NOT EXISTS `publisher` (
`pu_id` int(11) NOT NULL AUTO_INCREMENT,
`pu_name` varchar(64) DEFAULT NULL,
PRIMARY KEY (`pu_id`),
UNIQUE KEY `pu_name` (`pu_name`),
KEY `publisher_pu_name` (`pu_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Structure de la table `serie`
--

CREATE TABLE IF NOT EXISTS `serie` (
`se_id` int(11) NOT NULL AUTO_INCREMENT,
`se_name` varchar(64) NOT NULL,
`se_genre` varchar(20) NOT NULL DEFAULT 'inconnu',
`se_stateBDBDEC` enum('finie','en_cours','oneshot','inconnu') DEFAULT 'inconnu',
`se_statePublisher` enum('finie','en_cours','oneshot','inconnu') NOT NULL DEFAULT 'inconnu',
`se_keywords` varchar(255) DEFAULT NULL,
`se_type` enum('bd','comic','classique','manga','inconnu') DEFAULT 'inconnu',
`se_website` varchar(64) DEFAULT NULL,
`se_totalBooks` int(5) DEFAULT NULL,
PRIMARY KEY (`se_id`),
UNIQUE KEY `se_name` (`se_name`),
KEY `se_stateBDBDEC` (`se_stateBDBDEC`),
KEY `se_statePublisher` (`se_statePublisher`),
KEY `se_type` (`se_type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Structure de la table `user`
--

CREATE TABLE IF NOT EXISTS `user` (
`us_id` int(11) NOT NULL AUTO_INCREMENT,
`us_lastname` varchar(20) NOT NULL,
`us_firstname` varchar(20) NOT NULL,
`us_email` varchar(45) NOT NULL,
`us_gradeId` int(11) NOT NULL,
`us_semester` int(3) NOT NULL,
`us_paid` int(1) NOT NULL,
`us_deposit` int(1) NOT NULL,
`us_comment` text,
PRIMARY KEY (`us_id`),
UNIQUE KEY `us_email` (`us_email`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Structure de la vue `actual_borrows`
--
DROP TABLE IF EXISTS `actual_borrows`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `actual_borrows` AS select `logs_borrows`.`logb_id` AS `logb_id`,`logs_borrows`.`logb_dateBorrow` AS `logb_dateBorrow`,`logs_borrows`.`logb_idBook` AS `logb_idBook`,`logs_borrows`.`logb_idUser` AS `logb_idUser` from `logs_borrows` where (isnull(`logs_borrows`.`logb_dateRetour`) or (`logs_borrows`.`logb_dateRetour` = '0000-00-00 00:00:00'));

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

--
-- Structure de la vue `books_out`
--
DROP TABLE IF EXISTS `books_out`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `books_out` AS select `book`.`bo_id` AS `bo_id`,`book`.`bo_cotation` AS `bo_cotation` from `book` where (`book`.`bo_location` = 'sortie');

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

--
-- Structure de la vue `out_books_info`
--
DROP TABLE IF EXISTS `out_books_info`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `out_books_info` AS select distinct `books_out`.`bo_id` AS `bo_id`,`books_out`.`bo_cotation` AS `bo_cotation`,`actual_borrows`.`logb_dateBorrow` AS `logb_dateBorrow`,`user`.`us_lastname` AS `us_lastname`,`user`.`us_firstname` AS `us_firstname`,`user`.`us_email` AS `us_email` from ((`books_out` left join `actual_borrows` on((`books_out`.`bo_id` = `actual_borrows`.`logb_idBook`))) left join `user` on((`user`.`us_id` = `actual_borrows`.`logb_idUser`)));

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

--
-- Structure de la vue `ouvrage_liaison`
--
DROP TABLE IF EXISTS `ouvrage_liaison`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `ouvrage_liaison` AS select `serie`.`se_id` AS `se_id`,`album`.`al_id` AS `al_id`,`edition`.`ed_id` AS `ed_id`,`publisher`.`pu_id` AS `pu_id`,`book`.`bo_id` AS `bo_id`,`book`.`bo_cotation` AS `bo_cotation` from ((((`book` join `edition` on((`book`.`bo_idEdition` = `edition`.`ed_id`))) join `album` on((`album`.`al_id` = `edition`.`ed_idAlbum`))) join `serie` on((`serie`.`se_id` = `album`.`al_idSerie`))) join `publisher` on((`publisher`.`pu_id` = `edition`.`ed_idPublisher`)));

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

--
-- Structure de la vue `ouvrage_liaison_auteurs`
--
DROP TABLE IF EXISTS `ouvrage_liaison_auteurs`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `ouvrage_liaison_auteurs` AS select `O`.`se_id` AS `se_id`,`O`.`al_id` AS `al_id`,`O`.`ed_id` AS `ed_id`,`O`.`pu_id` AS `pu_id`,`O`.`bo_id` AS `bo_id`,`O`.`bo_cotation` AS `bo_cotation`,`J`.`jaut_idAuthor` AS `jaut_idAuthor`,`J`.`jaut_typeAuthor` AS `jaut_typeAuthor` from (`ouvrage_liaison` `O` join `join_authors_albums` `J`) where (`O`.`al_id` = `J`.`jaut_idAlbum`);