logo

phpMyResa

Installation de la version V4.0 - Création de la structure de la base de données



Modèle physique de la base de données (MySQL)

modèle physique




Script de création des tables de la base de données

MySQLPostgreSQLOracle
CREATE TABLE classe (
    id int(2) unsigned NOT NULL auto_increment,
    nom varchar(50) NOT NULL,
    jour_meme int(1) unsigned NOT NULL default '1',
    PRIMARY KEY (id),
    UNIQUE KEY nom (nom)
);


CREATE TABLE administrateur (
    id_classe int(2) unsigned NOT NULL,
    nom varchar(40) NOT NULL,
    prenom varchar(40) NOT NULL,
    pass varchar(32) NOT NULL,
    mail varchar(128) NOT NULL,
    telephone varchar(20) NOT NULL,
    default_language varchar(30) NOT NULL default 'english',
    PRIMARY KEY (id_classe)
);


CREATE TABLE objet (
    id int(2) unsigned NOT NULL auto_increment,
    id_classe int(2) unsigned NOT NULL,
    nom varchar(128) NOT NULL,
    capacite varchar(30) NOT NULL default '0',
    status int(1) unsigned NOT NULL default '0',
    libelle varchar(255) NOT NULL,
    priority int(1) unsigned NOT NULL default '0',
    pubical int(1) unsigned NOT NULL default '1',
    available int(1) unsigned NOT NULL default '1',
    visible int(1) unsigned NOT NULL default '1',
    wifi int(1) unsigned NOT NULL default '0',
    PRIMARY KEY (id),
    UNIQUE KEY nom (nom),
    KEY id_classe (id_classe)
);


CREATE TABLE reservation (
    id int(16) unsigned NOT NULL auto_increment,
    idmulti int(16) unsigned NOT NULL default '0',
    idobjet int(2) unsigned NOT NULL,
    titre varchar(64) NOT NULL,
    jour varchar(10) NOT NULL,
    debut varchar(8) NOT NULL,
    duree varchar(8) NOT NULL,
    email varchar(128) NOT NULL,
    commentaire varchar(255) NOT NULL,
    pass varchar(20) NOT NULL,
    valide int(1) unsigned NOT NULL default '0',
    priority int(1) unsigned NOT NULL default '0',
    wifi int(1) unsigned NOT NULL default '0',
    state int(1) unsigned NOT NULL default '0',
    diffusion int(1) unsigned NOT NULL default '1',
    PRIMARY KEY (id),
    KEY idmulti (idmulti),
    KEY idobjet (idobjet),
    KEY jour (jour),
    KEY titre (titre),
    KEY email (email),
    KEY state (state)
);


CREATE TABLE languages (
    name varchar(50) NOT NULL,
    french text NOT NULL,
    english text NOT NULL,
    spanish text NOT NULL,
    german text NOT NULL,
    PRIMARY KEY (name)
);


CREATE TABLE classe (
    id serial NOT NULL,
    nom varchar(50) NOT NULL,
    jour_meme numeric(1) DEFAULT 1 NOT NULL,
    CONSTRAINT pk_classe_id PRIMARY KEY (id),
    CONSTRAINT un_classe_nom UNIQUE (nom)
);


CREATE TABLE administrateur (
    id_classe numeric(2) NOT NULL,
    nom varchar(40) NOT NULL,
    prenom varchar(40) NOT NULL,
    pass varchar(32) NOT NULL,
    mail varchar(128) NOT NULL,
    telephone varchar(20) NOT NULL,
    default_language varchar(30) DEFAULT 'english' NOT NULL,
    CONSTRAINT pk_administrateur_id_classe PRIMARY KEY (id_classe),
    CONSTRAINT fk_administrateur_id_classe FOREIGN KEY (id_classe) REFERENCES classe(id)
);


CREATE TABLE objet (
    id serial NOT NULL,
    id_classe numeric(2) NOT NULL,
    nom varchar(128) NOT NULL,
    capacite varchar(30) DEFAULT 0 NOT NULL,
    status numeric(1) DEFAULT 0 NOT NULL,
    libelle varchar(255) NOT NULL,
    priority numeric(1) DEFAULT 0 NOT NULL,
    pubical numeric(1) DEFAULT 1 NOT NULL,
    available numeric(1) DEFAULT 1 NOT NULL,
    visible numeric(1) DEFAULT 1 NOT NULL,
    wifi numeric(1) DEFAULT 0 NOT NULL,
    CONSTRAINT pk_objet_id PRIMARY KEY (id),
    CONSTRAINT un_objet_nom UNIQUE (nom),
    CONSTRAINT fk_objet_id_classe FOREIGN KEY (id_classe) REFERENCES classe(id)
);
CREATE INDEX in_objet_id_classe ON objet (id_classe);


CREATE TABLE reservation (
    id serial NOT NULL,
    idmulti numeric(16) DEFAULT 0 NOT NULL,
    idobjet numeric(2) NOT NULL,
    titre varchar(64) NOT NULL,
    jour varchar(10) NOT NULL,
    debut varchar(8) NOT NULL,
    duree varchar(8) NOT NULL,
    email varchar(128) NOT NULL,
    commentaire varchar(255) NOT NULL,
    pass varchar(20) NOT NULL,
    valide numeric(1) DEFAULT 0 NOT NULL,
    priority numeric(1) DEFAULT 0 NOT NULL,
    wifi numeric(1) DEFAULT 0 NOT NULL,
    state numeric(1) DEFAULT 0 NOT NULL,
    diffusion numeric(1) DEFAULT 1 NOT NULL,
    CONSTRAINT pk_reservation_id PRIMARY KEY (id),
    CONSTRAINT fk_reservation_idobjet FOREIGN KEY (idobjet) REFERENCES objet(id)
);
CREATE INDEX in_reservation_idobjet ON reservation (idobjet);
CREATE INDEX in_reservation_idmulti ON reservation (idmulti);
CREATE INDEX in_reservation_jour ON reservation (jour);
CREATE INDEX in_reservation_titre ON reservation (titre);
CREATE INDEX in_reservation_email ON reservation (email);
CREATE INDEX in_reservation_state ON reservation (state);


CREATE TABLE languages (
    name varchar(50) NOT NULL,
    french text NOT NULL,
    english text NOT NULL,
    spanish text NOT NULL,
    german text NOT NULL,
    CONSTRAINT pk_languages_name PRIMARY KEY (name)
);


CREATE TABLE classe(
    id NUMBER(2) NOT NULL,
    nom VARCHAR2(50) NOT NULL,
    jour_meme NUMBER(1) DEFAULT 1 NOT NULL,
    CONSTRAINT pk_classe_id PRIMARY KEY(id),
    CONSTRAINT un_classe_nom UNIQUE(nom)
);


CREATE TABLE administrateur(
    id_classe NUMBER(2) NOT NULL,
    nom VARCHAR2(40) NOT NULL,
    prenom VARCHAR2(40) NOT NULL,
    pass VARCHAR2(32) NOT NULL,
    mail VARCHAR2(128) NOT NULL,
    telephone VARCHAR2(20) NOT NULL,
    default_language VARCHAR2(30) DEFAULT 'english' NOT NULL,
    CONSTRAINT pk_administrateur_id_classe PRIMARY KEY (id_classe),
    CONSTRAINT fk_administrateur_id_classe FOREIGN KEY (id_classe) REFERENCES classe(id)
);


CREATE TABLE objet(
    id NUMBER(2) NOT NULL,
    id_classe NUMBER(2) NOT NULL,
    nom VARCHAR2(128) NOT NULL,
    capacite VARCHAR2(30) DEFAULT 0 NOT NULL,
    status NUMBER(1) DEFAULT 0 NOT NULL,
    libelle VARCHAR2(255) NOT NULL,
    priority NUMBER(1) DEFAULT 0 NOT NULL,
    pubical NUMBER(1) DEFAULT 1 NOT NULL,
    available NUMBER(1) DEFAULT 1 NOT NULL,
    visible NUMBER(1) DEFAULT 1 NOT NULL,
    wifi NUMBER(1) DEFAULT 0 NOT NULL,
    CONSTRAINT pk_objet_id PRIMARY KEY (id),
    CONSTRAINT un_objet_nom UNIQUE (nom),
    CONSTRAINT fk_objet_id_classe FOREIGN KEY (id_classe) REFERENCES classe(id)
);
CREATE INDEX in_objet_id_classe ON objet (id_classe);


CREATE TABLE reservation(
    id NUMBER(16) NOT NULL,
    idmulti NUMBER(16) DEFAULT 0 NOT NULL,
    idobjet NUMBER(2) NOT NULL,
    titre VARCHAR2(64) NOT NULL,
    jour VARCHAR2(10) NOT NULL,
    debut VARCHAR2(8) NOT NULL,
    duree VARCHAR2(8) NOT NULL,
    email VARCHAR2(128) NOT NULL,
    commentaire VARCHAR2(255),
    pass VARCHAR2(20) NOT NULL,
    valide NUMBER(1) DEFAULT 0 NOT NULL,
    priority NUMBER(1) DEFAULT 0 NOT NULL,
    wifi NUMBER(1) DEFAULT 0 NOT NULL,
    state NUMBER(1) DEFAULT 0 NOT NULL,
    diffusion NUMBER(1) DEFAULT 1 NOT NULL,
    CONSTRAINT pk_reservation_id PRIMARY KEY (id),
    CONSTRAINT fk_reservation_idobjet FOREIGN KEY (idobjet) REFERENCES objet(id)
);
CREATE INDEX in_reservation_idobjet ON reservation (idobjet);
CREATE INDEX in_reservation_idmulti ON reservation (idmulti);
CREATE INDEX in_reservation_jour ON reservation (jour);
CREATE INDEX in_reservation_titre ON reservation (titre);
CREATE INDEX in_reservation_email ON reservation (email);
CREATE INDEX in_reservation_state ON reservation (state);


CREATE TABLE languages(
    name VARCHAR2(50) NOT NULL,
    french VARCHAR2(2000) NOT NULL,
    english VARCHAR2(2000) NOT NULL,
    spanish VARCHAR2(2000) NOT NULL,
    german VARCHAR2(2000) NOT NULL,
    CONSTRAINT pk_languages_name PRIMARY KEY (name)
);


CREATE SEQUENCE reservation_id_seq
    start with 1
    increment by 1
    nomaxvalue;


CREATE OR REPLACE TRIGGER reservation_id_trigger
    before insert on reservation
    for each row
    begin
    select reservation_id_seq.nextval into :new.id from dual;
    end;


CREATE SEQUENCE classe_id_seq
    start with 1
    increment by 1
    nomaxvalue;


CREATE OR REPLACE TRIGGER classe_id_trigger
    before insert on classe
    for each row
    begin
    select classe_id_seq.nextval into :new.id from dual;
    end;


CREATE SEQUENCE object_id_seq
    start with 1
    increment by 1
    nomaxvalue;


CREATE OR REPLACE TRIGGER object_id_trigger
    before insert on objet
    for each row
    begin
    select object_id_seq.nextval into :new.id from dual;
    end;



Script d'ajout des données dans la base de données

Pour remplir la table languages, copiez collez le contenu du fichier doc/INSTALL_data_db.sql dans votre base de données.
Si vous utilisez Oracle, n'oubliez pas le commit après.