mysql.sql 30.9 KB
Newer Older
Alexis Lahouze's avatar
Alexis Lahouze committed
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
--
-- ----------------------------------------------------------------------
-- AlternC - Web Hosting System
-- Copyright (C) 2000-2012 by the AlternC Development Team.
-- https://alternc.org/
-- ----------------------------------------------------------------------
-- LICENSE
--
-- This program is free software; you can redistribute it and/or
-- modify it under the terms of the GNU General Public License (GPL)
-- as published by the Free Software Foundation; either version 2
-- of the License, or (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
--
-- To read the license please visit http://www.gnu.org/copyleft/gpl.html
-- ----------------------------------------------------------------------
-- Purpose of file: Create the basic structure for the mysql system db
-- ----------------------------------------------------------------------
--

-- ----------------------------------------------------------------------
-- STRUCTURE DES TABLES D`ALTERNC
--
Alexis Lahouze's avatar
Alexis Lahouze committed
28
29
30
31
32
-- IMPORTANT: lorsque la structure de ces tables est modifiée, le
-- fichier upgrades/<version>.sql doit être modifié (ou créé!) pour que
-- les installations courantes soient mises à jour. <version> est ici
-- le prochain numéro de version d`AlternC. Voir upgrades/README pour
-- plus de détails.
Alexis Lahouze's avatar
Alexis Lahouze committed
33
-- ----------------------------------------------------------------------
34
35

CREATE TABLE IF NOT EXISTS `slaveip` (
36
`ip` VARCHAR( 40 ) NOT NULL ,
37
38
`class` TINYINT NOT NULL ,
PRIMARY KEY ( `ip` , `class` )
39
) ENGINE=MyISAM COMMENT = 'Allowed ip for slave dns managment';
40
41
42
43
44

CREATE TABLE IF NOT EXISTS `slaveaccount` (
`login` VARCHAR( 64 ) NOT NULL ,
`pass`  VARCHAR( 64 ) NOT NULL ,
PRIMARY KEY ( `login` )
45
) ENGINE=MyISAM COMMENT = 'Allowed account for slave dns managment';
46
47


Alexis Lahouze's avatar
Alexis Lahouze committed
48
49
50
--
-- Structure de la table `browser`
--
Alexis Lahouze's avatar
Alexis Lahouze committed
51
-- Cette table contient les préférences des utilisateurs dans le gestionnaire de fichiers
52
53
54


CREATE TABLE IF NOT EXISTS browser (
Alexis Lahouze's avatar
Alexis Lahouze committed
55
  uid int(10) unsigned NOT NULL default '0',		-- Numéro de l`utilisateur
Alexis Lahouze's avatar
Alexis Lahouze committed
56
57
58
59
  editsizex int(10) unsigned NOT NULL default '0',	-- Largeur de la zone d`edition du brouteur
  editsizey int(10) unsigned NOT NULL default '0',	-- Hauteur de la zone d`edition du brouteur
  listmode tinyint(3) unsigned NOT NULL default '0',	-- Mode de listing (1 colonne, 2 colonne, 3 colonne)
  showicons tinyint(4) NOT NULL default '0',		-- Faut-il afficher les icones (1/0)
Alexis Lahouze's avatar
Alexis Lahouze committed
60
61
  downfmt tinyint(4) NOT NULL default '0',		-- Format de téléchargement (zip/bz2/tgz/tar.Z)
  createfile tinyint(4) NOT NULL default '0',		-- Que fait-on après création d`un fichier (1/0)
Alexis Lahouze's avatar
Alexis Lahouze committed
62
  showtype tinyint(4) NOT NULL default '0',		-- Affiche-t-on le type mime ? 
Alexis Lahouze's avatar
Alexis Lahouze committed
63
64
65
  editor_font varchar(64) NOT NULL default '',		-- Nom de la police dans l`éditeur de fichiers
  editor_size varchar(8) NOT NULL default '',		-- Taille de la police dans l`éditeur de fichiers
  crff tinyint(4) NOT NULL default '0',			-- mémorise le dernier fichier/dossier créé (pour le bouton radio)
Alexis Lahouze's avatar
Alexis Lahouze committed
66
  golastdir tinyint(4) NOT NULL default '0',		-- Faut-il aller au dernier dossier ou au dossier racine dans le brouteur ?
Alexis Lahouze's avatar
Alexis Lahouze committed
67
  lastdir varchar(255) NOT NULL default '',		-- Dernier dossier visité.
68
  PRIMARY KEY  (uid)
69
) ENGINE=MyISAM COMMENT='Préférences du gestionnaire de fichiers';
70
71


Alexis Lahouze's avatar
Alexis Lahouze committed
72
73
74
75
--
-- Structure de la table `chgmail`
--
-- Cette table contient les demandes de changements de mail pour les membres
76
77

CREATE TABLE IF NOT EXISTS chgmail (
Alexis Lahouze's avatar
Alexis Lahouze committed
78
  uid int(10) unsigned NOT NULL default '0',		-- Numéro de l`utilisateur
Alexis Lahouze's avatar
Alexis Lahouze committed
79
  cookie varchar(20) NOT NULL default '',		-- Cookie du mail
Alexis Lahouze's avatar
Alexis Lahouze committed
80
  ckey varchar(6) NOT NULL default '',			-- Clé de vérif
Alexis Lahouze's avatar
Alexis Lahouze committed
81
  mail varchar(128) NOT NULL default '',		-- Nouvel Email
82
  ts int(10) unsigned NOT NULL default '0',		-- Timestamp de la demande 
83
  PRIMARY KEY  (uid)
84
) ENGINE=MyISAM COMMENT='Demandes de changements de mail en cours';
85

Alexis Lahouze's avatar
Alexis Lahouze committed
86
87
88
89
--
-- Structure de la table `db`
--
-- Contient les bases mysql des membres, + login / pass en clair
90
91

CREATE TABLE IF NOT EXISTS db (
92
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
Alexis Lahouze's avatar
Alexis Lahouze committed
93
  uid int(10) unsigned NOT NULL default '0',		-- Numéro de l`utilisateur
Alexis Lahouze's avatar
Alexis Lahouze committed
94
95
  login varchar(16) NOT NULL default '',		-- Nom d`utilisateur mysql
  pass varchar(16) NOT NULL default '',			-- Mot de passe mysql
Alexis Lahouze's avatar
Alexis Lahouze committed
96
  db varchar(64) NOT NULL default '',			-- Base de données concernée
Alexis Lahouze's avatar
Alexis Lahouze committed
97
  bck_mode tinyint(3) unsigned NOT NULL default '0',	-- Mode de backup (0/non 1/Daily 2/Weekly)
Alexis Lahouze's avatar
Alexis Lahouze committed
98
  bck_history tinyint(3) unsigned NOT NULL default '0',	-- Nombre de backup à conserver ?
Alexis Lahouze's avatar
Alexis Lahouze committed
99
  bck_gzip tinyint(3) unsigned NOT NULL default '0',	-- Faut-il compresser les backups ?
Alexis Lahouze's avatar
Alexis Lahouze committed
100
  bck_dir varchar(255) NOT NULL default '',		-- Où stocke-t-on les backups sql ?
101
  PRIMARY KEY id (id)
102
) ENGINE=MyISAM COMMENT='Bases MySQL des membres';
103

104
105
106
--
-- Structure de la table `domaines`
--
Alexis Lahouze's avatar
Alexis Lahouze committed
107
-- Liste des domaines hébergés
108
109

CREATE TABLE IF NOT EXISTS domaines (
110
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
111
112
113
114
115
  compte int(10) unsigned NOT NULL default '0',
  domaine varchar(64) NOT NULL default '',
  gesdns int(1) NOT NULL default '1',
  gesmx int(1) NOT NULL default '1',
  noerase tinyint(4) NOT NULL default '0',
Alan Garcia's avatar
Alan Garcia committed
116
117
  dns_action enum ('OK','UPDATE','DELETE') NOT NULL default 'UPDATE',
  dns_result varchar(255) not null default '',
118
  zonettl int(10) unsigned NOT NULL default '86400',
119
120
  PRIMARY KEY (id),
  UNIQUE KEY (domaine)
121
) ENGINE=MyISAM;
122

Alexis Lahouze's avatar
Alexis Lahouze committed
123
124
125
126
--
-- Structure de la table `ftpusers`
--
-- Comptes ftp des membres
127
128
129
130
131

CREATE TABLE IF NOT EXISTS ftpusers (
  id int(10) unsigned NOT NULL auto_increment,
  name varchar(64) NOT NULL default '',
  password varchar(32) NOT NULL default '',
132
  encrypted_password VARCHAR(32) default NULL,
133
134
  homedir varchar(128) NOT NULL default '',
  uid int(10) unsigned NOT NULL default '0',
135
  enabled boolean NOT NULL DEFAULT TRUE,
136
137
138
139
  PRIMARY KEY  (id),
  UNIQUE KEY name (name),
  KEY homedir (homedir),
  KEY mid (uid)
140
) ENGINE=MyISAM;
141

Alexis Lahouze's avatar
Alexis Lahouze committed
142
143
144
--
-- Structure de la table `local`
--
Alexis Lahouze's avatar
Alexis Lahouze committed
145
-- Champs utilisables par l`hébergeur pour associer des données locales aux membres.
146
147
148
149
150
151

CREATE TABLE IF NOT EXISTS local (
  uid int(10) unsigned NOT NULL default '0',
  nom varchar(128) NOT NULL default '',
  prenom varchar(128) NOT NULL default '',
  PRIMARY KEY  (uid)
152
) ENGINE=MyISAM COMMENT='Parametres Locaux des membres';
153

Alexis Lahouze's avatar
Alexis Lahouze committed
154
155
156
157
--
-- Structure de la table `membres`
--
-- Liste des membres
158
159

CREATE TABLE IF NOT EXISTS membres (
Alexis Lahouze's avatar
Alexis Lahouze committed
160
  uid int(10) unsigned NOT NULL auto_increment,		-- Numéro du membre (GID)
Alexis Lahouze's avatar
Alexis Lahouze committed
161
162
163
164
165
  login varchar(128) NOT NULL default '',		-- Nom d`utilisateur
  pass varchar(64) NOT NULL default '',			-- Mot de passe
  enabled tinyint(4) NOT NULL default '1',		-- Le compte est-il actif ?
  su tinyint(4) NOT NULL default '0',			-- Le compte est-il super-admin ?
  mail varchar(128) NOT NULL default '',		-- Adresse email du possesseur
166
  lastaskpass int(10) unsigned default '0',		-- Date de dernière demande du pass par mail
Alexis Lahouze's avatar
Alexis Lahouze committed
167
168
  show_help tinyint(4) NOT NULL default '1',		-- Faut-il afficher l`aide dans le bureau
  lastlogin datetime NOT NULL default '0000-00-00 00:00:00',	-- Date du dernier login
Alexis Lahouze's avatar
Alexis Lahouze committed
169
  lastfail tinyint(4) NOT NULL default '0',		-- Nombre d`échecs depuis le dernier login
Alexis Lahouze's avatar
Alexis Lahouze committed
170
  lastip varchar(255) NOT NULL default '',		-- Nom DNS du client au dernier login
Alexis Lahouze's avatar
Alexis Lahouze committed
171
  creator int(10) unsigned default '0',			-- Qui a créé le compte (quel uid admin)
Alexis Lahouze's avatar
Alexis Lahouze committed
172
173
174
175
  canpass tinyint(4) default '1',			-- L`utilisateur peut-il changer son pass.
  warnlogin tinyint(4) default '0',			-- TODO L`utilisateur veut-il recevoir un mail quand on se loggue sur son compte ?
  warnfailed tinyint(4) default '0',			-- TODO L`utilisateur veut-il recevoir un mail quand on tente de se logguer sur son compte ?
  admlist tinyint(4) default '0',			-- Mode d`affichage de la liste des membres pour les super admins
176
  type varchar(128) default 'default',
177
  db_server_id int(10) DEFAULT NULL,
Antoine Beaupré's avatar
Antoine Beaupré committed
178
  notes TEXT NOT NULL,
179
180
181
182
183
  created datetime default NULL, 
  renewed datetime default NULL, 
  duration int(4) default NULL,
  PRIMARY KEY  (uid),
  UNIQUE KEY k_login (login)
184
) ENGINE=MyISAM COMMENT='Liste des membres du serveur';
185

Alexis Lahouze's avatar
Alexis Lahouze committed
186
187
188
189
--
-- Structure de la table `quotas`
--
-- Listes des quotas des membres
190
191

CREATE TABLE IF NOT EXISTS quotas (
Alexis Lahouze's avatar
Alexis Lahouze committed
192
  uid int(10) unsigned NOT NULL default '0',		-- Numéro GID du membre concerné
Alexis Lahouze's avatar
Alexis Lahouze committed
193
  name varchar(64) NOT NULL default '',			-- Nom du quota
194
  total int(10) unsigned NOT NULL default '0',	-- Quota total (maximum autorisé)
195
  PRIMARY KEY  (uid,name)
196
) ENGINE=MyISAM COMMENT='Quotas des Membres';
197

Alexis Lahouze's avatar
Alexis Lahouze committed
198
199
200
201
--
-- Structure de la table `sessions`
--
-- Sessions actives sur le bureau
202
203

CREATE TABLE IF NOT EXISTS sessions (
Alexis Lahouze's avatar
Alexis Lahouze committed
204
  sid varchar(32) NOT NULL default '',			-- Cookie de session (md5)
Alexis Lahouze's avatar
Alexis Lahouze committed
205
  uid int(10) unsigned NOT NULL default '0',		-- UID du membre concerné
Alexis Lahouze's avatar
Alexis Lahouze committed
206
  ip varchar(40) NOT NULL default '',		-- Adresse IP de la connexion
207
  ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
208
) ENGINE=MyISAM COMMENT='Session actives sur le bureau';
209

Alexis Lahouze's avatar
Alexis Lahouze committed
210
211
212
213
--
-- Structure de la table `sub_domaines`
--
-- Sous-domaines des membres
214
215

CREATE TABLE IF NOT EXISTS sub_domaines (
216
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
217
  compte int(10) unsigned NOT NULL default '0',
218
  domaine varchar(255) NOT NULL default '',
219
220
  sub varchar(100) NOT NULL default '',
  valeur varchar(255) default NULL,
Alan Garcia's avatar
Alan Garcia committed
221
222
  type varchar(30) NOT NULL default 'LOCAL',
  web_action enum ('OK','UPDATE','DELETE') NOT NULL default 'UPDATE',
Alan Garcia's avatar
Alan Garcia committed
223
  web_result varchar(255) not null default '',
Alan Garcia's avatar
Alan Garcia committed
224
  enable enum ('ENABLED', 'ENABLE', 'DISABLED', 'DISABLE') NOT NULL DEFAULT 'ENABLED',
225
  PRIMARY KEY (id)
226
--  ,FOREIGN KEY (type) REFERENCES (domaines_type)
227
) ENGINE=MyISAM;
228

Alexis Lahouze's avatar
Alexis Lahouze committed
229
230
231
232
233
--
-- Main address table.
--
-- Addresses for domain.

234
CREATE TABLE IF NOT EXISTS `address` (
235
236
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT, -- Technical id.
  `domain_id` int(10) unsigned NOT NULL REFERENCES `domaines`(`id`), -- FK to domaines.
Alexis Lahouze's avatar
Alexis Lahouze committed
237
  `address` varchar(255) NOT NULL, -- The address.
238
  `type` char(8) NOT NULL, -- standard emails are '', other may be 'mailman' or 'sympa' ...
Alexis Lahouze's avatar
Alexis Lahouze committed
239
240
241
242
  `password` varchar(255) DEFAULT NULL, -- The password associated to the address.
  `enabled` int(1) unsigned NOT NULL DEFAULT '1', -- Enabled flag.
  `expire_date` datetime DEFAULT NULL, -- Expiration date, used for temporary addresses.
  `update_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Update date, for technical usage only.
Alan Garcia's avatar
Alan Garcia committed
243
  `mail_action` enum('OK','DELETE','DELETING') NOT NULL default 'OK', -- mail_action is DELETE or DELETING when deleting a mailbox by cron
Alexis Lahouze's avatar
Alexis Lahouze committed
244
  PRIMARY KEY (`id`),
245
  UNIQUE INDEX `fk_domain_id` (`domain_id`,`address`)
246
) ENGINE=MyISAM COMMENT = 'This is the main address table. It represents an address as in RFC2822';
Alexis Lahouze's avatar
Alexis Lahouze committed
247
248
249
250
251
252

--
-- Mailbox table.
-- 
-- Local delivered mailboxes.

253
CREATE TABLE IF NOT EXISTS `mailbox` (
254
255
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT, -- Technical id.
  `address_id` int(10) unsigned NOT NULL REFERENCES `address`(`id`), -- Reference to address.
Alexis Lahouze's avatar
Alexis Lahouze committed
256
  `path` varchar(255) NOT NULL, -- Relative path to the mailbox.
257
  `quota` int(10) unsigned DEFAULT NULL, -- Quota for this mailbox.
Alexis Lahouze's avatar
Alexis Lahouze committed
258
259
  `delivery` varchar(255) NOT NULL, -- Delivery transport.
  `update_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Update date, for technical usage only.
260
  `bytes` int(10) NOT NULL DEFAULT '0', -- number of bytes in the mailbox, filled by dovecot
261
262
  `messages` int(11) NOT NULL DEFAULT '0', -- number of messages in the mailbox, filled by dovecot 
  `lastlogin` datetime NOT NULL, -- Last login, filled by dovecot
Alan Garcia's avatar
Alan Garcia committed
263
  `mail_action` enum('OK','DELETE','DELETING') NOT NULL default 'OK', -- mail_action is DELETE or DELETING when deleting a mailbox by cron
Alexis Lahouze's avatar
Alexis Lahouze committed
264
265
  PRIMARY KEY (`id`),
  UNIQUE KEY `address_id` (`address_id`)
266
) ENGINE=MyISAM COMMENT = 'Table containing local deliverd mailboxes.';
Alexis Lahouze's avatar
Alexis Lahouze committed
267
268
269
270
271
272

--
-- Other recipients.
--
-- Other recipients for an address (aliases)

273
CREATE TABLE IF NOT EXISTS `recipient` (
274
275
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT, -- Technical id.
  `address_id` int(10) unsigned NOT NULL REFERENCES `address`(`id`), -- Reference to address
Alexis Lahouze's avatar
Alexis Lahouze committed
276
277
278
  `recipients` text NOT NULL, -- Recipients
  `update_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Update date, for technical usage only.
  PRIMARY KEY (`id`),
279
  UNIQUE KEY `key_id` (`id`,`address_id`)
280
) ENGINE=MyISAM COMMENT = 'Table containing other recipients (aliases) for an address.';
Alexis Lahouze's avatar
Alexis Lahouze committed
281
282


Alexis Lahouze's avatar
Alexis Lahouze committed
283
284
285
--
-- Structure de la table `defquotas`
--
Alexis Lahouze's avatar
Alexis Lahouze committed
286
-- Quotas par défaut pour les services
287
288

CREATE TABLE IF NOT EXISTS defquotas (
Alexis Lahouze's avatar
Alexis Lahouze committed
289
  quota varchar(128),				-- Nom du quota
290
  value int(10) unsigned default '0',	-- Valeur du quota
Alexis Lahouze's avatar
Alexis Lahouze committed
291
  type  varchar(128) default 'default',		-- Type de compte associée à ce quota
292
  PRIMARY KEY (quota,type)
293
) ENGINE=MyISAM;
294

Alexis Lahouze's avatar
Alexis Lahouze committed
295
296
297
298
--
-- Quotas par defaut pour les nouveaux membres
--
-- Ces quotas par defaut sont redefinissables dans l`interface web
299
300

INSERT IGNORE INTO defquotas (quota,value) VALUES ('dom',1);
301
INSERT IGNORE INTO defquotas (quota,value) VALUES ('web',51200);
302
303
304
305
306
307
INSERT IGNORE INTO defquotas (quota,value) VALUES ('mail',10);
INSERT IGNORE INTO defquotas (quota,value) VALUES ('ftp',2);
INSERT IGNORE INTO defquotas (quota,value) VALUES ('stats',1);
INSERT IGNORE INTO defquotas (quota,value) VALUES ('mysql',1);


Alexis Lahouze's avatar
Alexis Lahouze committed
308
309
310
311
--
-- Structure de la table `forbidden_domains`
--
-- Liste des domaines explicitement interdits sur le serveur :
312
313
314
315

CREATE TABLE IF NOT EXISTS forbidden_domains (
  domain varchar(255) NOT NULL default '',
  PRIMARY KEY  (domain)
316
) ENGINE=MyISAM COMMENT='forbidden domains to install';
317

Alexis Lahouze's avatar
Alexis Lahouze committed
318
319
320
--
-- Contenu de la table `forbidden_domains`
--
321

Alexis Lahouze's avatar
Alexis Lahouze committed
322
-- Registries : 
323
324
325
326
327
328
329
330
331
332
333
334
INSERT IGNORE INTO forbidden_domains VALUES ('afilias.net');
INSERT IGNORE INTO forbidden_domains VALUES ('afnic.fr');
INSERT IGNORE INTO forbidden_domains VALUES ('dns.be');
INSERT IGNORE INTO forbidden_domains VALUES ('internic.net');
INSERT IGNORE INTO forbidden_domains VALUES ('netsol.com');
INSERT IGNORE INTO forbidden_domains VALUES ('nic.biz');
INSERT IGNORE INTO forbidden_domains VALUES ('nic.cx');
INSERT IGNORE INTO forbidden_domains VALUES ('nic.fr');
INSERT IGNORE INTO forbidden_domains VALUES ('verisign.com');
INSERT IGNORE INTO forbidden_domains VALUES ('octopuce.com');
INSERT IGNORE INTO forbidden_domains VALUES ('pir.org');
INSERT IGNORE INTO forbidden_domains VALUES ('cira.ca');
Alexis Lahouze's avatar
Alexis Lahouze committed
335
-- big isp :
336
337
338
339
340
341
342
343
344
INSERT IGNORE INTO forbidden_domains VALUES ('aol.com');
INSERT IGNORE INTO forbidden_domains VALUES ('hotmail.com');
INSERT IGNORE INTO forbidden_domains VALUES ('microsoft.com');
INSERT IGNORE INTO forbidden_domains VALUES ('sympatico.ca');
INSERT IGNORE INTO forbidden_domains VALUES ('tiscali.fr');
INSERT IGNORE INTO forbidden_domains VALUES ('voila.fr');
INSERT IGNORE INTO forbidden_domains VALUES ('wanadoo.fr');
INSERT IGNORE INTO forbidden_domains VALUES ('yahoo.com');
INSERT IGNORE INTO forbidden_domains VALUES ('yahoo.fr');
345
346
347
INSERT IGNORE INTO forbidden_domains VALUES ('gmail.com');
INSERT IGNORE INTO forbidden_domains VALUES ('orange.fr');
INSERT IGNORE INTO forbidden_domains VALUES ('sfr.fr');
348

Alexis Lahouze's avatar
Alexis Lahouze committed
349
350
351
--
-- Structure de la table `tld`
--
Alexis Lahouze's avatar
Alexis Lahouze committed
352
-- Liste des tld autorisés sur ce serveur : 
353
354

CREATE TABLE IF NOT EXISTS tld (
Alexis Lahouze's avatar
Alexis Lahouze committed
355
  tld varchar(128) NOT NULL default '',		-- lettres du tld (sans le .)
Alexis Lahouze's avatar
Alexis Lahouze committed
356
  mode tinyint(4) NOT NULL default '0',		-- Comment est-il autorisé ?
357
358
  PRIMARY KEY  (tld),
  KEY mode (mode)
359
) ENGINE=MyISAM COMMENT='TLD autorises et comment sont-ils autorises ? ';
360

Alexis Lahouze's avatar
Alexis Lahouze committed
361
362
363
--
-- Contenu de la table `tld`
--
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378

INSERT IGNORE INTO tld VALUES ('fr', 4);
INSERT IGNORE INTO tld VALUES ('com', 1);
INSERT IGNORE INTO tld VALUES ('org', 1);
INSERT IGNORE INTO tld VALUES ('net', 1);
INSERT IGNORE INTO tld VALUES ('biz', 1);
INSERT IGNORE INTO tld VALUES ('info', 1);
INSERT IGNORE INTO tld VALUES ('name', 1);
INSERT IGNORE INTO tld VALUES ('ca', 1);
INSERT IGNORE INTO tld VALUES ('it', 1);
INSERT IGNORE INTO tld VALUES ('ws', 1);
INSERT IGNORE INTO tld VALUES ('be', 1);
INSERT IGNORE INTO tld VALUES ('eu.org', 4);
INSERT IGNORE INTO tld VALUES ('cjb.net', 4);
INSERT IGNORE INTO tld VALUES ('asso.fr', 4);
379
380
381
INSERT IGNORE INTO tld VALUES ('eu', 1);
INSERT IGNORE INTO tld VALUES ('coop', 1);
INSERT IGNORE INTO tld VALUES ('asia', 1);
382
383
384
385
386
387

--
-- Table structure for table 'variable'
--
-- if comment is null, then the variable is internal and will not show
-- up in the generic configuration panel
388
CREATE TABLE `variable` (
389
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
390
391
392
393
  `name` varchar(48) NOT NULL DEFAULT '',
  `value` longtext NOT NULL,
  `comment` mediumtext,
  `strata` enum('DEFAULT','GLOBAL','FQDN','FQDN_CREATOR','CREATOR','MEMBER','DOMAIN') NOT NULL DEFAULT 'DEFAULT',
394
  `strata_id` int(10) DEFAULT NULL,
395
  `type` text,
396
397
398
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_2` (`name`,`strata`,`strata_id`),
  KEY `name` (`name`)
399
) ENGINE=MyISAM;
400
401
402
403
404
405
406
407
408
409
410

-- hosting_tld: only used, for now, in bureau/admin/adm_*add.php
INSERT IGNORE INTO `variable` (name, value, comment) VALUES ('hosting_tld', 0,
'This is a FQDN that designates the main hostname of the service.

For example, hosting_tld determines in what TLD the "free" user domain
is created. If this is set to "example.com", a checkbox will appear in
the user creation dialog requesting the creator if he wants to create
the domain "username.example.com".

If this is set to 0 or a "false" string, it will be ignored.');
411
412
413
414
415

--
-- Table structure for table `dbusers`
--

416
CREATE TABLE IF NOT EXISTS `dbusers` (
417
418
419
  `id` int(10) unsigned NOT NULL auto_increment,
  `uid` int(10) unsigned NOT NULL default '0',
  `name` varchar(16) NOT NULL default '',
420
421
  `password`  varchar( 64 ),
  `enable` enum ('ACTIVATED', 'HIDDEN', 'ADMIN') NOT NULL DEFAULT 'ACTIVATED', 
422
  KEY `id` (`id`)
423
) ENGINE=MyISAM COMMENT='Utilisateurs MySQL des membres';
424

425
426
427
428
429

CREATE TABLE IF NOT EXISTS `mxaccount` (
`login` VARCHAR( 64 ) NOT NULL ,
`pass`  VARCHAR( 64 ) NOT NULL ,
PRIMARY KEY ( `login` )
430
) ENGINE=MyISAM COMMENT = 'Allowed account for secondary mx managment';
431
432


433
434
435
436
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `size_web` (
  `uid` int(10) unsigned NOT NULL default '0',
  `size` int(10) unsigned NOT NULL default '0',
437
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
438
439
  PRIMARY KEY  (`uid`),
  KEY `ts` (`ts`)
440
) ENGINE=MyISAM COMMENT='Web space used by accounts.';
441

442
443
444
445
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `size_db` (
  `db` varchar(255) NOT NULL default '',
  `size` int(10) unsigned NOT NULL default '0',
446
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
447
448
  PRIMARY KEY  (`db`),
  KEY `ts` (`ts`)
449
) ENGINE=MyISAM COMMENT='MySQL Database used space';
450
451
452
453
454
455
456
457
458
459
460

-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `size_mailman` (
  `list` varchar(255) NOT NULL default '',
  `uid` int(11) NOT NULL default '0',
  `size` int(10) unsigned NOT NULL default '0',
  `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`list`),
  KEY `ts` (`ts`),
  KEY `uid` (`uid`)
) ENGINE=MyISAM COMMENT='Mailman Lists used space';
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479

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


CREATE TABLE IF NOT EXISTS `policy` (
  `name` varchar(64) NOT NULL,
  `minsize` tinyint(3) unsigned NOT NULL,
  `maxsize` tinyint(3) unsigned NOT NULL,
  `classcount` tinyint(3) unsigned NOT NULL,
  `allowlogin` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY  (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='The password policies for services';


INSERT IGNORE INTO `variable` (`name` ,`value` ,`comment`)
VALUES (
'subadmin_restriction', '', 
'This variable set the way the account list works for accounts other than "admin" (2000). 0 (default) = admin other than admin/2000 can see their own account, but not the other one 1 = admin other than admin/2000 can see any account by clicking the ''show all accounts'' link. '
);
Alan Garcia's avatar
Alan Garcia committed
480
481
482
483
484
485
486
487

-- Domains type
CREATE TABLE IF NOT EXISTS `domaines_type` (
    `name` VARCHAR (255) NOT NULL, -- Uniq name
    `description` TEXT, -- Human description
    `target` enum ('NONE', 'URL', 'DIRECTORY', 'IP', 'IPV6', 'DOMAIN', 'TXT') NOT NULL DEFAULT 'NONE', -- Target type
    `entry` VARCHAR (255) DEFAULT '', -- BIND entry
    `compatibility` VARCHAR (255) DEFAULT '', -- Which type can be on the same subdomains
Alan Garcia's avatar
Alan Garcia committed
488
    `enable` enum ('ALL', 'NONE', 'ADMIN') NOT NULL DEFAULT 'ALL', -- Show this option to who ?
Alan Garcia's avatar
Alan Garcia committed
489
490
    `only_dns` BOOLEAN DEFAULT FALSE, -- Update_domains modify just the dns, no web configuration
    `need_dns` BOOLEAN DEFAULT TRUE, -- The server need to be the DNS to allow this service
Alan Garcia's avatar
Alan Garcia committed
491
    `advanced` BOOLEAN DEFAULT TRUE, -- It's an advanced option
Alan Garcia's avatar
Alan Garcia committed
492
493
    `create_tmpdir` BOOLEAN NOT NULL DEFAULT FALSE, -- do we create tmp dir ?
    `create_targetdir` BOOLEAN NOT NULL DEFAULT FALSE, -- do we create target dir ?
Alan Garcia's avatar
Alan Garcia committed
494
PRIMARY KEY ( `name` )
495
) ENGINE=MyISAM COMMENT = 'Type of domains allowed';
Alan Garcia's avatar
Alan Garcia committed
496

497
498
499
500
501
502
503
504
505
506
507
508
INSERT IGNORE INTO `domaines_type` (name, description, target, entry,                             compatibility,                               only_dns, need_dns, advanced, enable) values
('vhost',  'Locally hosted',             'DIRECTORY', '%SUB% IN A @@PUBLIC_IP@@',                 'txt,defmx,defmx2,mx,mx2',                   false,    false,    false, 'ALL'),
('url',    'URL redirection',            'URL',       '%SUB% IN A @@PUBLIC_IP@@',                 'txt,defmx,defmx2',                          false,    false,    false, 'ALL'),
('ip',     'IPv4 redirect',              'IP',        '%SUB% IN A %TARGET%',                      'url,ip,ipv6,txt,mx,mx2,defmx,defmx2',       true,     true,     false, 'ALL'),
('ipv6',   'IPv6 redirect',              'IPV6',      '%SUB% IN AAAA %TARGET%',                   'ip,ipv6,txt,mx,mx2,defmx,defmx2',           true,     true,     true,  'ALL'),
('cname',  'CNAME DNS entry',            'DOMAIN',    '%SUB% CNAME %TARGET%',                     '',                                          true,     true,     true,  'ALL'),
('txt',    'TXT DNS entry',              'TXT',       '%SUB% IN TXT "%TARGET%"',                  'vhost,url,ip,ipv6,txt,mx,mx2,defmx,defmx2', true,     true,     true,  'ALL'),
('mx',     'MX DNS entry',               'DOMAIN',    '%SUB% IN MX 5 %TARGET%',                   'vhost,url,ip,ipv6,txt,mx,mx2',              true,     true,     true,  'ALL'),
('mx2',    'secondary MX DNS entry',     'DOMAIN',    '%SUB% IN MX 10 %TARGET%',                  'vhost,url,ip,ipv6,txt,mx,mx2',              true,     true,     true,  'ALL'),
('defmx',  'Default mail server',        'NONE',      '%SUB% IN MX 5 @@DEFAULT_MX@@.',            'vhost,url,ip,ipv6,txt,defmx2',              true,     true,     true,  'ADMIN'),
('defmx2', 'Default backup mail server', 'NONE',      '%SUB% IN MX 10 @@DEFAULT_SECONDARY_MX@@.', 'vhost,url,ip,ipv6,txt,defmx',               true,     true,     true,  'ADMIN'),
('panel',  'AlternC panel access',       'NONE',      '%SUB% IN A @@PUBLIC_IP@@',                 'vhost,url,ip,ipv6,txt,mx,mx2,defmx,defmx2', false,    false,    true,  'ALL')
Alan Garcia's avatar
Alan Garcia committed
509
;
510
UPDATE domaines_type SET create_tmpdir=true, create_targetdir=true WHERE target='DIRECTORY';
Alan Garcia's avatar
Alan Garcia committed
511
512
513

-- Add function who are not in mysql 5 to be able ton convert ipv6 to decimal (and reverse it)
DELIMITER //
514
DROP FUNCTION IF EXISTS INET_ATON6;//
Alan Garcia's avatar
Alan Garcia committed
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
CREATE FUNCTION INET_ATON6(n CHAR(39))
RETURNS DECIMAL(39) UNSIGNED
DETERMINISTIC
BEGIN
    RETURN CAST(CONV(SUBSTRING(n FROM  1 FOR 4), 16, 10) AS DECIMAL(39))
                       * 5192296858534827628530496329220096 -- 65536 ^ 7
         + CAST(CONV(SUBSTRING(n FROM  6 FOR 4), 16, 10) AS DECIMAL(39))
                       *      79228162514264337593543950336 -- 65536 ^ 6
         + CAST(CONV(SUBSTRING(n FROM 11 FOR 4), 16, 10) AS DECIMAL(39))
                       *          1208925819614629174706176 -- 65536 ^ 5
         + CAST(CONV(SUBSTRING(n FROM 16 FOR 4), 16, 10) AS DECIMAL(39)) 
                       *               18446744073709551616 -- 65536 ^ 4
         + CAST(CONV(SUBSTRING(n FROM 21 FOR 4), 16, 10) AS DECIMAL(39))
                       *                    281474976710656 -- 65536 ^ 3
         + CAST(CONV(SUBSTRING(n FROM 26 FOR 4), 16, 10) AS DECIMAL(39))
                       *                         4294967296 -- 65536 ^ 2
         + CAST(CONV(SUBSTRING(n FROM 31 FOR 4), 16, 10) AS DECIMAL(39))
                       *                              65536 -- 65536 ^ 1
         + CAST(CONV(SUBSTRING(n FROM 36 FOR 4), 16, 10) AS DECIMAL(39))
         ;
END;
//
DELIMITER ;
DELIMITER //
539
DROP FUNCTION IF EXISTS INET_NTOA6;//
Alan Garcia's avatar
Alan Garcia committed
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
CREATE FUNCTION INET_NTOA6(n DECIMAL(39) UNSIGNED)
RETURNS CHAR(39)
DETERMINISTIC
BEGIN
  DECLARE a CHAR(39)             DEFAULT '';
  DECLARE i INT                  DEFAULT 7;
  DECLARE q DECIMAL(39) UNSIGNED DEFAULT 0;
  DECLARE r INT                  DEFAULT 0;
  WHILE i DO
    -- DIV doesn't work with nubers > bigint
    SET q := FLOOR(n / 65536);
    SET r := n MOD 65536;
    SET n := q;
    SET a := CONCAT_WS(':', LPAD(CONV(r, 10, 16), 4, '0'), a);

    SET i := i - 1;
  END WHILE;

  SET a := TRIM(TRAILING ':' FROM CONCAT_WS(':',
                                            LPAD(CONV(n, 10, 16), 4, '0'),
                                            a));

  RETURN a;

END;
//
DELIMITER ;

-- New table for the authorised IP
CREATE TABLE IF NOT EXISTS `authorised_ip` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `uid` int(11) unsigned NOT NULL default '0',
  `ip` varchar(40) not null,
  `subnet` integer(3) not null default 32,
  `infos` varchar(255) not null default '',
  PRIMARY KEY  (`id`),
  KEY `uid` (`uid`)
) ENGINE=MyISAM COMMENT='Table with list of authorised ip and subnet';

-- Who have authorised IP ?
CREATE TABLE IF NOT EXISTS `authorised_ip_affected` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `authorised_ip_id` int(10) unsigned not null,
  `protocol` varchar(15) not null,
  `parameters` varchar(30) default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM COMMENT='Table with list of protocol <-> authorised ip and subnet';

INSERT IGNORE INTO `variable` (`name` ,`value` ,`comment`)
VALUES (
'auth_ip_ftp_default_yes', '1',
'This variable set if you want to allow all IP address to access FTP by default. If the user start to define some IP or subnet in the allow list, only those he defined will be allowed. This variable can take two value : 0 or 1.'
);

594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610

--
-- Structure de la table `cron`
--

CREATE TABLE IF NOT EXISTS `cron` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  `url` varchar(2100) NOT NULL,
  `user` varchar(64) NOT NULL,
  `password` varchar(64) NOT NULL,
  `schedule` int(11) NOT NULL,
  `email` varchar(255) NOT NULL,
  `next_execution` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `uid` (`uid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;
611
612
613
614



--
615
-- Structure de la vue `dovecot_view`
616
617
--

618
CREATE OR REPLACE VIEW `dovecot_view` AS
619
620
621
622
623
624
625
626
627
628
SELECT concat(`address`.`address`,'@',`domaines`.`domaine`) AS `user`,
concat('*:storage=',cast(`mailbox`.`quota` as char charset latin1),'M') AS `userdb_quota_rule`,
`address`.`password` AS `password`,
`mailbox`.`path` AS `userdb_home`,
`domaines`.`compte` AS `userdb_uid`,
`domaines`.`compte` AS `userdb_gid`,
`mailbox`.`bytes` AS `quota_dovecot`,
`mailbox`.`messages` AS `nb_messages` 
from ((`mailbox`
join `address` on((`address`.`id` = `mailbox`.`address_id`))) 
629
630
631
join `domaines` on((`domaines`.`id` = `address`.`domain_id`)))
where `address`.`enabled` = 1
;
632

633
634
635
636
--
-- Structure de la vue `alias_view`
--

637
CREATE OR REPLACE VIEW `alias_view` AS
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665

-- Generate all the alias configured by the users
select 
  concat(`address`.`address`,'@',`domaines`.`domaine`) AS `mail`,
  concat(if(isnull(`mailbox`.`id`),
  '',
  concat(concat(`address`.`address`,'@',`domaines`.`domaine`),'\n')),
  `recipient`.`recipients`) AS `alias`
from 
  (
    ((`recipient` join `address` on((`address`.`id` = `recipient`.`address_id`)))
    left join `mailbox` on((`mailbox`.`address_id` = `address`.`id`))
    )
    join `domaines` on((`domaines`.`id` = `address`.`domain_id`))
  )
where 
  `address`.`enabled` = 1

UNION

-- Generate the alias for all the account
-- Example : account gaylord will have gaylord@FQDN
-- as an alias to his email account. FQDN can be
-- changed in variable mailname_bounce
select 
  distinct concat(`m`.`login`,'@',`v`.`value`) AS `mail`,
  `m`.`mail` AS `alias`
from 
666
667
  `membres` `m`,
  `variable` `v`
668
669
670
671
672
673
674
675
676
677
where 
  `v`.`name` = 'mailname_bounce'

UNION

-- Generate an alias alterncpanel@FQDN to admin mail
select 
  distinct concat('alterncpanel','@',`v`.`value`) AS `mail`,
  `m`.`mail` AS `alias`
from 
678
679
  `membres` `m`,
  `variable` `v`
680
where 
681
  (`v`.`name` = 'mailname_bounce' AND `m`.`uid`=2000)
682

683
;
684
685
686
687
688

--
-- Structure de la table `piwik_users`
--

689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
CREATE TABLE IF NOT EXISTS `piwik_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  `login` varchar(255) NOT NULL,
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_user` (`login`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ;

--
-- Structure de la table `piwik_sites`
--

CREATE TABLE IF NOT EXISTS `piwik_sites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  `piwik_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_site_per_user` (`uid`,`piwik_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

710
711
-- Defaults subdomains to create when a domain is added
CREATE TABLE IF NOT EXISTS `default_subdomains` (
712
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
713
714
715
716
717
  `sub` varchar(255) NOT NULL,
  `domain_type` varchar(255) NOT NULL,
  `domain_type_parameter` varchar(255) NOT NULL,
  `concerned` enum('BOTH','MAIN','SLAVE') NOT NULL DEFAULT 'MAIN',
  `enabled` boolean not null default true,
718
  PRIMARY KEY  (`id`)
719
) ENGINE=MyISAM COMMENT='Contains the defaults subdomains created on domains creation';
720

721
INSERT IGNORE INTO `default_subdomains` (`sub`, `domain_type`, `domain_type_parameter`, `concerned`) VALUES
722
723
('www', 'VHOST', '%%DOMAINDIR%%', 'MAIN'),
('mail', 'WEBMAIL', '', 'MAIN'),
724
725
726
727
('', 'URL', 'http://www.%%DOMAIN%%', 'MAIN'),
('www', 'URL', 'http://www.%%TARGETDOM%%', 'SLAVE'),
('mail', 'URL', 'http://mail.%%TARGETDOM%%', 'SLAVE'),
('', 'URL', 'http://%%TARGETDOM%%', 'SLAVE');
728

729

730
731
732
733
734
735
736
737
738
739
740
-- Table for the MySQL servers
CREATE TABLE IF NOT EXISTS `db_servers` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `host` varchar(255) NOT NULL,
  `login` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `client` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM COMMENT='List of the databases servers';

François Serman's avatar
François Serman committed
741
742
743
744
745
746
-- Table for VM requests
CREATE TABLE IF NOT EXISTS `vm_history` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ip` varchar(256) NOT NULL,
  `date_start` datetime NOT NULL,
  `date_end` datetime DEFAULT NULL,
747
  `uid` int(10) unsigned NOT NULL,
François Serman's avatar
François Serman committed
748
749
750
  `serialized_object` TEXT NOT NULL,
  PRIMARY KEY (`id`),
  KEY `date_end` (`date_end`),
751
  KEY `uid` (`uid`)
François Serman's avatar
François Serman committed
752
753
754
) ENGINE=MyISAM COMMENT='VM Allocation requests';


755
CREATE TABLE IF NOT EXISTS `actions` (
756
 id int(10) unsigned NOT NULL AUTO_INCREMENT,
757
 type enum ('CREATE_FILE','FIX_USER','CREATE_DIR','DELETE','MOVE','FIX_DIR','FIX_FILE'),
758
759
760
761
762
763
764
765
 parameters longtext default NULL,
 creation timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 begin timestamp,
 end timestamp,
 user varchar(255) default NULL,
 status int(8) unsigned default NULL,
 PRIMARY KEY ( `id` )
) ENGINE=MyISAM COMMENT = 'generic actions';