Suppression des droits d'exécution des fichiers.
[auf_paf.git] / pafauf_bdd.sql
CommitLineData
98ff4c8e 1# SQL Manager 2005 for MySQL 3.7.5.1\r
2# ---------------------------------------\r
3# Host : localhost\r
4# Port : 3306\r
5# Database : pafauf_bdd\r
6\r
7\r
8DROP DATABASE IF EXISTS `pafauf_bdd`;\r
9\r
10CREATE DATABASE `pafauf_bdd`\r
11 CHARACTER SET 'utf8'\r
12 COLLATE 'utf8_general_ci';\r
13\r
14USE `pafauf_bdd`;\r
15\r
16#\r
17# Structure for the `tblaccountaccess` table : \r
18#\r
19\r
20DROP TABLE IF EXISTS `tblaccountaccess`;\r
21\r
22CREATE TABLE `tblaccountaccess` (\r
23 `ID` int(15) NOT NULL AUTO_INCREMENT,\r
24 `username` varchar(300) DEFAULT NULL,\r
25 `accesstime` datetime DEFAULT NULL,\r
26 PRIMARY KEY (`ID`)\r
27) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=latin1;\r
28\r
29#\r
30# Structure for the `tbldomaine` table : \r
31#\r
32\r
33DROP TABLE IF EXISTS `tbldomaine`;\r
34\r
35CREATE TABLE `tbldomaine` (\r
36 `id` int(11) NOT NULL AUTO_INCREMENT,\r
37 `nom` varchar(500) NOT NULL,\r
38 `other` varchar(500) DEFAULT NULL,\r
39 PRIMARY KEY (`id`)\r
40) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;\r
41\r
42#\r
43# Structure for the `tblinscrit` table : \r
44#\r
45\r
46DROP TABLE IF EXISTS `tblinscrit`;\r
47\r
48CREATE TABLE `tblinscrit` (\r
49 `id` int(11) NOT NULL AUTO_INCREMENT,\r
50 `nom` varchar(200) NOT NULL,\r
51 `prenom` varchar(200) NOT NULL,\r
52 `sexe` varchar(1) NOT NULL,\r
53 `datedenaissance` date NOT NULL DEFAULT '0000-00-00',\r
54 `nationalite` varchar(100) DEFAULT NULL,\r
55 `login` varchar(1) NOT NULL,\r
56 `motdepasse` varchar(300) DEFAULT NULL,\r
57 `universite` varchar(300) DEFAULT NULL,\r
58 `faculte` varchar(300) DEFAULT NULL,\r
59 `promotion` int(11) DEFAULT NULL,\r
60 `anneediplome` int(11) DEFAULT NULL,\r
61 `delf` varchar(20) DEFAULT NULL,\r
62 `anneedelf` int(11) DEFAULT NULL,\r
63 `langueautre` varchar(1000) DEFAULT NULL,\r
64 `email` varchar(100) DEFAULT NULL,\r
65 `telephonepersonnelle` varchar(20) DEFAULT NULL,\r
66 `adressepersonnelle` text,\r
67 `telephoneparents` varchar(20) DEFAULT NULL,\r
68 `adresseparents` text,\r
69 `titretravail` varchar(1000) DEFAULT NULL,\r
70 `adressetravail` text,\r
71 `divers` text,\r
72 `dateinlogin` date DEFAULT NULL,\r
73 `datein` date DEFAULT NULL,\r
74 `supprimer` int(1) DEFAULT '0',\r
75 PRIMARY KEY (`id`)\r
76) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;\r
77\r
78#\r
79# Structure for the `tblnationalite` table : \r
80#\r
81\r
82DROP TABLE IF EXISTS `tblnationalite`;\r
83\r
84CREATE TABLE `tblnationalite` (\r
85 `id` int(11) NOT NULL AUTO_INCREMENT,\r
86 `nom` varchar(500) NOT NULL,\r
87 `other` varchar(500) DEFAULT NULL,\r
88 PRIMARY KEY (`id`)\r
89) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;\r
90\r
91#\r
92# Structure for the `tbluniversite` table : \r
93#\r
94\r
95DROP TABLE IF EXISTS `tbluniversite`;\r
96\r
97CREATE TABLE `tbluniversite` (\r
98 `id` int(11) NOT NULL AUTO_INCREMENT,\r
99 `nom` varchar(500) DEFAULT NULL,\r
100 `other` varchar(500) DEFAULT NULL,\r
101 PRIMARY KEY (`id`)\r
102) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;\r
103\r
104#\r
105# Definition for the `v_excel` view : \r
106#\r
107\r
108DROP VIEW IF EXISTS `v_excel`;\r
109\r
110CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_excel` AS \r
111 select \r
112 `tblinscrit`.`id` AS `Numéro`,\r
113 `tblinscrit`.`nom` AS `Nom`,\r
114 `tblinscrit`.`prenom` AS `Prénom`,\r
115 `tblinscrit`.`sexe` AS `Genre`,\r
116 `tblinscrit`.`datedenaissance` AS `Date de Naissance`,\r
117 `tblinscrit`.`nationalite` AS `Nationalité`,\r
118 `tblinscrit`.`universite` AS `Université`,\r
119 `tblinscrit`.`faculte` AS `Faculté`,\r
120 `tblinscrit`.`promotion` AS `Promotion`,\r
121 `tblinscrit`.`anneediplome` AS `Année Obtention de Diplôme`,\r
122 `tblinscrit`.`delf` AS `DELF / DALF`,\r
123 `tblinscrit`.`anneedelf` AS `Année Obtention DELF / DALF`,\r
124 `tblinscrit`.`langueautre` AS `Autres Langues`,\r
125 `tblinscrit`.`email` AS `E-mail`,\r
126 `tblinscrit`.`telephonepersonnelle` AS `Téléphone Personnel`,\r
127 `tblinscrit`.`adressepersonnelle` AS `Adresse Personnelle`,\r
128 `tblinscrit`.`telephoneparents` AS `Téléphone de Parents`,\r
129 `tblinscrit`.`adresseparents` AS `Adresse de Parents`,\r
130 `tblinscrit`.`titretravail` AS `Titre de Travail ou Stage`,\r
131 `tblinscrit`.`adressetravail` AS `Adresse de Travail ou Stage Actuel` \r
132 from \r
133 `tblinscrit`;\r
134\r
135#\r
136# Data for the `tblaccountaccess` table (LIMIT 0,500)\r
137#\r
138\r
139INSERT INTO `tblaccountaccess` (`ID`, `username`, `accesstime`) VALUES \r
140 (1,'C1','2011-01-15 11:09:32'),\r
141 (2,'C1','2011-01-15 17:10:10'),\r
142 (3,'C1','2011-01-15 21:12:24'),\r
143 (4,'C3','2011-01-15 22:05:24'),\r
144 (5,'C2','2011-01-15 22:05:58'),\r
145 (6,'C1','2011-01-15 23:29:42'),\r
146 (7,'C1','2011-01-17 20:38:46'),\r
147 (8,'C1','2011-01-17 23:12:04'),\r
148 (9,'C1','2011-01-17 23:15:38'),\r
149 (10,'C1','2011-01-17 23:45:36'),\r
150 (11,'C1','2011-01-17 23:46:33'),\r
151 (12,'C1','2011-01-17 23:57:33'),\r
152 (13,'C1','2011-01-18 00:06:57'),\r
153 (14,'C1','2011-01-18 21:43:54'),\r
154 (15,'C3','2011-01-18 22:09:37'),\r
155 (16,'C4','2011-01-18 22:12:15'),\r
156 (17,'C5','2011-01-18 22:50:17'),\r
157 (18,'C1','2011-01-25 21:17:41'),\r
158 (19,'C1','2011-01-25 21:32:00'),\r
159 (20,'C1','2011-01-25 23:51:31'),\r
160 (21,'C1','2011-01-26 00:25:01'),\r
161 (22,'C1','2011-01-26 00:28:20'),\r
162 (23,'c1','2011-04-29 22:14:33'),\r
163 (24,'c1','2011-04-29 22:15:14'),\r
164 (25,'c1','2011-05-01 21:10:18'),\r
165 (26,'c1','2011-05-01 21:50:34'),\r
166 (27,'c1','2011-05-01 21:50:48'),\r
167 (28,'c1','2011-05-04 21:22:38'),\r
168 (29,'c1','2011-05-04 21:30:58'),\r
169 (30,'c6','2011-05-04 22:29:03'),\r
170 (31,'c7','2011-05-04 23:14:15'),\r
171 (32,'c8','2011-05-04 23:18:22'),\r
172 (33,'c6','2011-05-04 23:48:32'),\r
173 (34,'c7','2011-05-04 23:49:02');\r
174\r
175COMMIT;\r
176\r
177#\r
178# Data for the `tbldomaine` table (LIMIT 0,500)\r
179#\r
180\r
181INSERT INTO `tbldomaine` (`id`, `nom`, `other`) VALUES \r
182 (1,'Agronomie',NULL),\r
183 (2,'Anglais',NULL),\r
184 (3,'Archéologie',NULL),\r
185 (4,'Architecture',NULL),\r
186 (5,'Biologie',NULL),\r
187 (6,'Chimie',NULL),\r
188 (7,'Commerce',NULL),\r
189 (8,'Droit',NULL),\r
190 (9,'Français',NULL),\r
191 (10,'Génie Civil',NULL),\r
192 (11,'Génie Electrique',NULL),\r
193 (12,'Génie Industriel',NULL),\r
194 (13,'Géographie',NULL),\r
195 (14,'Géologie',NULL),\r
196 (15,'Gestion',NULL),\r
197 (16,'Histoire',NULL),\r
198 (17,'Hydraulique',NULL),\r
199 (18,'Informatique',NULL),\r
200 (19,'Ingénieur',NULL),\r
201 (20,'Littérature',NULL),\r
202 (21,'Linguistique',NULL),\r
203 (22,'Mathématique',NULL),\r
204 (23,'Médecine',NULL),\r
205 (24,'Odontostomatologie',NULL),\r
206 (25,'Pharmacie',NULL),\r
207 (26,'Philosophie',NULL),\r
208 (27,'Technicien',NULL),\r
209 (28,'Tourisme',NULL),\r
210 (29,'Autre',NULL);\r
211\r
212COMMIT;\r
213\r
214#\r
215# Data for the `tblinscrit` table (LIMIT 0,500)\r
216#\r
217\r
218INSERT INTO `tblinscrit` (`id`, `nom`, `prenom`, `sexe`, `datedenaissance`, `nationalite`, `login`, `motdepasse`, `universite`, `faculte`, `promotion`, `anneediplome`, `delf`, `anneedelf`, `langueautre`, `email`, `telephonepersonnelle`, `adressepersonnelle`, `telephoneparents`, `adresseparents`, `titretravail`, `adressetravail`, `divers`, `dateinlogin`, `datein`, `supprimer`) VALUES \r
219 (1,'TIV','Chhunly','M','1980-03-07','Laotienne','C','4OONmNDO1w==','??','??',1950,1950,'DALF',1954,'Anglais','tclyit@gmail.com','012343854899 ? ?','PP`s','0123854358','SRédiction','Responsable de Formation au CNF','ITC','Bopha','2011-01-12','2011-05-04',0),\r
220 (2,'Ong','Bopha','F','1981-12-02','Cambodgien','C','4OONmNDO1w==','Univ. Royale de Phnom Penh','Linguistique',2009,2009,'DALF',2007,'Anglais','ong_bopha@yahoo.com','01245545577','Battambang','012457788','Battambang','PhD','Canada','Toronto','2011-01-12','2011-01-15',0),\r
221 (3,'Ong','Bopha','F','2011-02-02','Cambodgien','C','3+qVi8M=','ITC','Génie Civil',1950,1948,'DALF',1950,'Anglais','ong_bopha@yahoo.com','012343854899','PP','012457788','BB','PhD','Toronto','Chhunly','2011-01-13','2011-01-18',0),\r
222 (4,'TIV','Poteang','F','2001-02-01','Cambodgien','C','rq1YV5eY','Univ. des Sciences de la Santé','Ingénieur',2003,2007,'DALF',1978,'Anglais','ong_bopha@yahoo.com','012343854899','SR','012457788','PP','PhD','Manager','company','2011-01-18','2011-01-18',0),\r
223 (5,'TIV','Potin','F','2003-06-05','Cambodgien','C','rq1YV5eY','Univ. Royale de Phnom Penh','Commerce',1987,2004,'DALF',1994,'Anglais','tivpotin@yahoo.com','012343854899','','','','','','','2011-01-18','2011-01-18',0),\r
224 (6,'Tong','Vong','M','1980-01-01','Française','C','rq1Y','Ecole Royale d','Littérature',0,0,'',0,'','','','','','','','','','2011-05-04','2011-05-04',0),\r
225 (7,'Voila','Pos','F','1980-02-02','Chinoise','C','rq1Y','Univ. des Sciences de la Santé (USS)','Génie Industriel',0,0,'',0,'','','','','','','','','','2011-05-04','2011-05-04',0),\r
226 (8,'Pily','Site','F','1980-03-03','Suisse','C','rq1Y','Lycée','Génie Civil',1986,0,'',0,'','','','','','','','','','2011-05-04','2011-05-04',0);\r
227\r
228COMMIT;\r
229\r
230#\r
231# Data for the `tblnationalite` table (LIMIT 0,500)\r
232#\r
233\r
234INSERT INTO `tblnationalite` (`id`, `nom`, `other`) VALUES \r
235 (1,'Cambodgienne',NULL),\r
236 (2,'Belge',NULL),\r
237 (3,'Canadienne',NULL),\r
238 (4,'Chinoise',NULL),\r
239 (5,'Française',NULL),\r
240 (6,'Laotienne',NULL),\r
241 (7,'Suisse',NULL),\r
242 (8,'Vietnamienne',NULL),\r
243 (9,'Malagasy',NULL),\r
244 (10,'Autre',NULL);\r
245\r
246COMMIT;\r
247\r
248#\r
249# Data for the `tbluniversite` table (LIMIT 0,500)\r
250#\r
251\r
252INSERT INTO `tbluniversite` (`id`, `nom`, `other`) VALUES \r
253 (1,'AUF',NULL),\r
254 (2,'Ecole Royale Administration',NULL),\r
255 (3,'Faculty of Business',NULL),\r
256 (4,'ITC',NULL),\r
257 (5,'Lycée',NULL),\r
258 (6,'Univ. Royale de Phnom Penh (URPP)',NULL),\r
259 (7,'Univ. Royale des Beaux-Arts (URBA)',NULL),\r
260 (8,'Univ. Royale Agronomie (URA)',NULL),\r
261 (9,'Univ. des Sciences de la Santé (USS)',NULL),\r
262 (10,'Univ. de Droit et Science-Eco (URDSE)',NULL),\r
263 (11,'Autre',NULL);\r
264\r
265COMMIT;\r
266\r