-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathdatabase_recette_base_MySQL.sql
144 lines (116 loc) · 5.16 KB
/
database_recette_base_MySQL.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
CREATE DATABASE `recette` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
use `recette`;
CREATE USER 'u_recette'@'localhost' IDENTIFIED BY 'SJzEeqLb2HHeNYVV';
GRANT USAGE ON * . * TO 'u_recette'@'localhost' IDENTIFIED BY 'SJzEeqLb2HHeNYVV' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
GRANT ALL PRIVILEGES ON `recette` . * TO 'u_recette'@'localhost';
-- Table des utilisateurs
-- DROP TABLE IF EXISTS `T_UTILISATEUR_UTI`;
CREATE TABLE IF NOT EXISTS `t_utilisateur_uti` (
`UTI_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`UTI_LOGIN` varchar(255) NOT NULL,
`UTI_MAIL` varchar(255) NOT NULL,
`UTI_NOM` varchar(255) NOT NULL,
`UTI_PRENOM` varchar(255) NOT NULL,
`UTI_PASS` varchar(40) NOT NULL,
`UTI_ADMIN` tinyint(1) NOT NULL DEFAULT '0',
`UTI_AVATAR` varchar(80) DEFAULT NULL,
PRIMARY KEY (`UTI_ID`),
UNIQUE KEY `UTI_LOGIN` (`UTI_LOGIN`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-- Table des unités
-- DROP TABLE IF EXISTS T_UNITE_UNI;
CREATE TABLE T_UNITE_UNI (
UNI_LABEL varchar(30) NOT NULL,
UNI_SHORT_LABEL varchar(15) NOT NULL,
UNI_DESCRIPTION mediumtext NOT NULL,
UNI_VALIDE bool NOT NULL DEFAULT 0,
UTI_ID bigint(20) unsigned NOT NULL,
KEY (UTI_ID),
PRIMARY KEY (UNI_LABEL)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-- Table des recettes
-- DROP TABLE IF EXISTS T_RECETTE_RCT;
CREATE TABLE T_RECETTE_RCT (
RCT_ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
RCT_DATE timestamp NOT NULL DEFAULT NOW(),
RCT_TITRE varchar(80) NOT NULL,
RCT_DESCRIPTION longtext NOT NULL,
RCT_TEMPS_PREPARATION time NOT NULL,
RCT_TEMPS_CUISSON time NULL DEFAULT '00:00:00',
RCT_TEMPS_REPOS time NULL DEFAULT '00:00:00',
RCT_DIFFICULTE ENUM("facile","moyen","difficile") NOT NULL DEFAULT 'facile',
RCT_COUT ENUM("faible","moyen","eleve") NOT NULL DEFAULT 'faible',
RCT_STATUT ENUM("brouillon","soumise","finale") NOT NULL DEFAULT 'brouillon',
RCT_ILLUSTRATION varchar(80) NOT NULL,
UTI_ID bigint(20) unsigned NOT NULL,
RCT_NBPERSONNE int NOT NULL,
PRIMARY KEY (RCT_ID),
KEY (UTI_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-- Table des catégories
-- DROP TABLE IF EXISTS T_CATEGORIE_CAT;
CREATE TABLE T_CATEGORIE_CAT (
CAT_ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
CAT_LABEL varchar(255) NOT NULL,
CAT_DESCRIPTION mediumtext NOT NULL,
CAT_ILLUSTRATION varchar(80) NOT NULL,
PRIMARY KEY (CAT_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
-- Table d'association entre recettes et catégories
-- DROP TABLE IF EXISTS TJ_CAT_RCT;
CREATE TABLE TJ_CAT_RCT (
CAT_ID bigint(20) unsigned NOT NULL,
RCT_ID bigint(20) unsigned NOT NULL,
PRIMARY KEY (CAT_ID, RCT_ID),
KEY (CAT_ID),
KEY (RCT_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
-- Table des ingrédients
-- DROP TABLE IF EXISTS T_INGREDIENT_IGD;
CREATE TABLE T_INGREDIENT_IGD (
IGD_LABEL varchar(255) NOT NULL,
IGD_DESCRIPTION mediumtext NOT NULL,
IGD_ILLUSTRATION varchar(80) NULL DEFAULT NULL,
IGD_VALIDE bool NOT NULL DEFAULT 0,
UTI_ID bigint(20) unsigned NULL DEFAULT NULL,
PRIMARY KEY (IGD_LABEL),
KEY (IGD_LABEL)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
-- Table d'association (composer)des recettes, ingédients et unités
-- DROP TABLE IF EXISTS TJ_IGD_RCT_UNI;
CREATE TABLE TJ_IGD_RCT_UNI (
RCT_ID bigint(20) unsigned NOT NULL,
IGD_LABEL varchar(255) NOT NULL,
UNI_LABEL varchar(30) NOT NULL,
IGD_RCT_UNI_QUANTITE int(11) NOT NULL,
PRIMARY KEY (RCT_ID, IGD_LABEL, UNI_LABEL)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
-- Table de commentaires
-- DROP TABLE IF EXISTS T_COMMENTAIRE_COM;
CREATE TABLE T_COMMENTAIRE_COM (
COM_ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
COM_TEXTE mediumtext NOT NULL,
COM_DATE timestamp NOT NULL DEFAULT NOW(),
UTI_ID bigint(20) unsigned NULL,
RCT_ID bigint(20) unsigned NOT NULL,
PRIMARY KEY (COM_ID),
KEY (RCT_ID),
KEY (UTI_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
-- création des clés étrangéres
ALTER TABLE TJ_CAT_RCT
ADD CONSTRAINT C_FK_CAT_CAT_RCT FOREIGN KEY (CAT_ID) REFERENCES T_CATEGORIE_CAT (CAT_ID) ,
ADD CONSTRAINT C_FK_RCT_CAT_RCT FOREIGN KEY (RCT_ID) REFERENCES T_RECETTE_RCT (RCT_ID) ;
ALTER TABLE T_UNITE_UNI
ADD CONSTRAINT C_FK_UNI_UTI FOREIGN KEY (UTI_ID) REFERENCES T_UTILISATEUR_UTI (UTI_ID) ;
ALTER TABLE T_COMMENTAIRE_COM
ADD CONSTRAINT C_FK_UTI_COM FOREIGN KEY (UTI_ID) REFERENCES T_UTILISATEUR_UTI (UTI_ID) ,
ADD CONSTRAINT C_FK_RCT_COM FOREIGN KEY (RCT_ID) REFERENCES T_RECETTE_RCT (RCT_ID) ;
ALTER TABLE TJ_IGD_RCT_UNI
ADD CONSTRAINT C_FK_RCT_IGD_RCT_UNI FOREIGN KEY (RCT_ID) REFERENCES T_RECETTE_RCT (RCT_ID) ,
ADD CONSTRAINT C_FK_IGD_IGD_RCT_UNI FOREIGN KEY (IGD_LABEL) REFERENCES T_INGREDIENT_IGD(IGD_LABEL),
ADD CONSTRAINT C_FK_UNI_IGD_RCT_UNI FOREIGN KEY (UNI_LABEL) REFERENCES T_UNITE_UNI (UNI_LABEL) ;
ALTER TABLE T_INGREDIENT_IGD
ADD CONSTRAINT C_FK_UTI_IGD FOREIGN KEY (UTI_ID) REFERENCES T_UTILISATEUR_UTI (UTI_ID) ;
ALTER TABLE T_RECETTE_RCT
ADD CONSTRAINT C_FK_UTI_RCT FOREIGN KEY (UTI_ID) REFERENCES T_UTILISATEUR_UTI (UTI_ID) ;