3 CREATE FUNCTION INET_ATON6(n
CHAR(
39))
4 RETURNS DECIMAL(
39) UNSIGNED
7 RETURN CAST(CONV(SUBSTRING(n
FROM 1 FOR 4),
16,
10)
AS DECIMAL(
39))
8 *
5192296858534827628530496329220096
9 +
CAST(CONV(SUBSTRING(n
FROM 6 FOR 4),
16,
10)
AS DECIMAL(
39))
10 *
79228162514264337593543950336
11 +
CAST(CONV(SUBSTRING(n
FROM 11 FOR 4),
16,
10)
AS DECIMAL(
39))
12 *
1208925819614629174706176
13 +
CAST(CONV(SUBSTRING(n
FROM 16 FOR 4),
16,
10)
AS DECIMAL(
39))
14 *
18446744073709551616
15 +
CAST(CONV(SUBSTRING(n
FROM 21 FOR 4),
16,
10)
AS DECIMAL(
39))
17 +
CAST(CONV(SUBSTRING(n
FROM 26 FOR 4),
16,
10)
AS DECIMAL(
39))
19 +
CAST(CONV(SUBSTRING(n
FROM 31 FOR 4),
16,
10)
AS DECIMAL(
39))
21 +
CAST(CONV(SUBSTRING(n
FROM 36 FOR 4),
16,
10)
AS DECIMAL(
39))
27 CREATE FUNCTION INET_NTOA6(n
DECIMAL(
39) UNSIGNED)
31 DECLARE a
CHAR(
39)
DEFAULT '';
32 DECLARE i
INT DEFAULT 7;
33 DECLARE q
DECIMAL(
39) UNSIGNED
DEFAULT 0;
34 DECLARE r
INT DEFAULT 0;
37 SET q := FLOOR(n /
65536);
40 SET a := CONCAT_WS(
':', LPAD(CONV(r,
10,
16),
4,
'0'), a);
45 SET a := TRIM(
TRAILING ':' FROM CONCAT_WS(
':',
46 LPAD(CONV(n,
10,
16),
4,
'0'),
56 CREATE TABLE IF NOT EXISTS `authorised_ip` (
57 `id`
int(
10) unsigned
NOT NULL auto_increment,
58 `uid`
int(
11) unsigned
NOT NULL default '0',
59 `ip`
varchar(
40)
not null,
60 `subnet`
integer(
3)
not null default 32,
61 `infos`
varchar(
255)
not null default '',
64 ) ENGINE=MyISAM COMMENT=
'Table with list of authorised ip and subnet';
67 CREATE TABLE IF NOT EXISTS `authorised_ip_affected` (
68 `id`
int(
10) unsigned
NOT NULL auto_increment,
69 `authorised_ip_id`
int(
10) unsigned
not null,
70 `protocol`
varchar(
15)
not null,
71 `parameters`
varchar(
30)
default '',
73 ) ENGINE=MyISAM COMMENT=
'Table with list of protocol <-> authorised ip and subnet';
75 INSERT IGNORE
INTO `variable` (`name` ,`
value` ,`comment`)
77 'auth_ip_ftp_default_yes',
'1',
78 '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.'
85 CREATE TABLE IF NOT EXISTS `address` (
86 `id`
bigint(
20) unsigned
NOT NULL AUTO_INCREMENT,
87 `domain_id`
bigint(
20) unsigned
NOT NULL REFERENCES `domaines`(`id`),
88 `address`
varchar(
255)
NOT NULL,
89 `type`
char(
8)
NOT NULL,
90 `password`
varchar(
255)
DEFAULT NULL,
91 `enabled`
int(
1) unsigned
NOT NULL DEFAULT '1',
92 `expire_date` datetime
DEFAULT NULL,
93 `update_date`
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
94 `mail_action` enum(
'OK',
'DELETE',
'DELETING')
NOT NULL default 'OK',
96 UNIQUE INDEX `fk_domain_id` (`domain_id`,`address`)
97 ) ENGINE=MyISAM COMMENT =
'This is the main address table. It represents an address as in RFC2822';
104 CREATE TABLE IF NOT EXISTS `mailbox` (
105 `id`
bigint(
20) unsigned
NOT NULL AUTO_INCREMENT,
106 `address_id`
bigint(
20) unsigned
NOT NULL REFERENCES `address`(`id`),
107 `path`
varchar(
255)
NOT NULL,
108 `quota`
bigint(
20) unsigned
DEFAULT NULL,
109 `delivery`
varchar(
255)
NOT NULL,
110 `update_date`
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
111 `bytes`
bigint(
20)
NOT NULL DEFAULT '0',
112 `messages`
int(
11)
NOT NULL DEFAULT '0',
113 `lastlogin` datetime
NOT NULL,
114 `mail_action` enum(
'OK',
'DELETE',
'DELETING')
NOT NULL default 'OK',
116 UNIQUE KEY `address_id` (`address_id`)
117 ) ENGINE=MyISAM COMMENT =
'Table containing local deliverd mailboxes.';
124 CREATE TABLE IF NOT EXISTS `recipient` (
125 `id`
bigint(
20) unsigned
NOT NULL AUTO_INCREMENT,
126 `address_id`
bigint(
20) unsigned
NOT NULL REFERENCES `address`(`id`),
127 `recipients` text
NOT NULL,
128 `update_date`
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
130 UNIQUE KEY `address_id` (`address_id`)
131 ) ENGINE=MyISAM COMMENT =
'Table containing other recipients (aliases) for an address.';
135 update sub_domaines
set web_action =
'UPDATE';
136 update domaines
set dns_action =
'UPDATE';
141 CREATE TABLE IF NOT EXISTS `cron` (
142 `id`
int(
11)
NOT NULL AUTO_INCREMENT,
143 `uid`
int(
11)
NOT NULL,
144 `url`
varchar(
2100)
NOT NULL,
145 `
user`
varchar(
64)
NOT NULL,
146 `password`
varchar(
64)
NOT NULL,
147 `schedule`
int(
11)
NOT NULL,
148 `email`
varchar(
255)
NOT NULL,
149 `next_execution`
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
152 ) ENGINE=MyISAM
DEFAULT CHARSET=latin1;
160 CREATE TABLE IF NOT EXISTS `piwik_users` (
161 `id`
int(
11)
NOT NULL AUTO_INCREMENT,
162 `uid`
int(
11)
NOT NULL,
163 `login`
varchar(
255)
NOT NULL,
164 `created_date`
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
166 UNIQUE KEY `uniq_user` (`login`)
167 ) ENGINE=MyISAM
DEFAULT CHARSET=latin1 AUTO_INCREMENT=
18 ;
173 CREATE TABLE IF NOT EXISTS `piwik_sites` (
174 `id`
int(
11)
NOT NULL AUTO_INCREMENT,
175 `uid`
int(
11)
NOT NULL,
176 `piwik_id`
int(
11)
NOT NULL,
178 UNIQUE KEY `unique_site_per_user` (`uid`,`piwik_id`)
179 ) ENGINE=MyISAM
DEFAULT CHARSET=latin1 AUTO_INCREMENT=
1 ;
183 DELETE FROM defquotas
WHERE quota =
'mysql_users';
184 DELETE FROM quotas
WHERE name =
'mysql_users';
187 DELETE FROM quotas
WHERE name =
'sta2';
188 DELETE FROM defquotas
WHERE quota =
'sta2';
192 DROP TABLE size_mail;
195 DELETE FROM domaines_type
WHERE name=
'webmail';
196 UPDATE domaines_type
SET compatibility=REPLACE(compatibility,
'webmail,',
'');
199 ALTER TABLE `domaines_type`
ADD create_tmpdir
BOOLEAN NOT NULL DEFAULT FALSE ;
200 ALTER TABLE `domaines_type`
ADD create_targetdir
BOOLEAN NOT NULL DEFAULT FALSE ;
201 UPDATE domaines_type
SET create_tmpdir=
true, create_targetdir=
true WHERE target=
'DIRECTORY';
204 ALTER IGNORE
TABLE domaines MODIFY domaine
VARCHAR(
255);
205 ALTER IGNORE
TABLE sub_domaines MODIFY domaine
VARCHAR(
255);
208 CREATE TABLE IF NOT EXISTS `default_subdomains` (
209 `id`
bigint(
20) unsigned
NOT NULL AUTO_INCREMENT,
210 `sub`
varchar(
255)
NOT NULL,
211 `domain_type`
varchar(
255)
NOT NULL,
212 `domain_type_parameter`
varchar(
255)
NOT NULL,
213 `concerned` enum(
'BOTH',
'MAIN',
'SLAVE')
NOT NULL DEFAULT 'MAIN',
214 `enabled`
boolean not null default true,
216 UNIQUE KEY `unique_row` (`sub`,`domain_type`,`domain_type_parameter`,`concerned`)
217 ) ENGINE=MyISAM COMMENT=
'Contains the defaults subdomains created on domains creation';
219 INSERT IGNORE
INTO `default_subdomains` (`sub`, `domain_type`, `domain_type_parameter`, `concerned`)
VALUES
220 (
'www',
'VHOST',
'%%DOMAINDIR%%',
'MAIN'),
221 (
'mail',
'WEBMAIL',
'',
'MAIN'),
222 (
'',
'URL',
'http://www.%%DOMAIN%%',
'MAIN'),
223 (
'www',
'URL',
'http://www.%%TARGETDOM%%',
'SLAVE'),
224 (
'mail',
'URL',
'http://mail.%%TARGETDOM%%',
'SLAVE'),
225 (
'',
'URL',
'http://%%TARGETDOM%%',
'SLAVE');
234 add column id
bigint(
20) unsigned
NOT NULL AUTO_INCREMENT
primary key;
237 add unique key(domaine);
243 alter table mail_domain
244 add column migration_state enum (
'UNMIGRATED',
'WONT BE MIGRATED',
'DB MIGRATED',
'FS MIGRATED')
not null default 'UNMIGRATED';
246 alter table mail_users
247 add column migration_state enum (
'UNMIGRATED',
'WONT BE MIGRATED',
'DB MIGRATED',
'FS MIGRATED')
not null default 'UNMIGRATED';
249 alter table mail_alias
250 add column migration_state enum (
'UNMIGRATED',
'WONT BE MIGRATED',
'DB MIGRATED',
'FS MIGRATED')
not null default 'UNMIGRATED';
255 set mail_domain.migration_state=
'WONT BE MIGRATED'
256 where locate(
'@', mail_domain.mail) =
0;
260 set mail_domain.migration_state=
'WONT BE MIGRATED'
261 where locate(
'@', mail_domain.mail) >
0
262 and substring_index(mail_domain.mail,
'@', -1)
not in (
select domaines.domaine
from domaines);
266 set mail_domain.migration_state=
'WONT BE MIGRATED'
267 where locate(
'@', mail_domain.mail) >
0
268 and mail_domain.mail
like '%@%@%';
272 set mail_users.migration_state=
'WONT BE MIGRATED'
273 where alias
in (
select replace(mail_domain.mail,
'@',
'_')
275 where mail_domain.migration_state=
'WONT BE MIGRATED');
278 set mail_users.migration_state=
'WONT BE MIGRATED'
279 where alias
in (
select mail_domain.mail
281 where mail_domain.migration_state=
'WONT BE MIGRATED');
295 insert into address (domain_id, address, type, password)
296 select domaines.id
as domain_id,
297 substring_index(mail_domain.mail,
'@',
1)
as address,
302 on domaines.domaine = substring_index(mail_domain.mail,
'@', -1)
304 on locate(concat(trim(mail_users.alias),
' '), concat(trim(replace(mail_domain.alias,
'\n',
' ')),
' ')) =
1
305 where mail_domain.migration_state=
'UNMIGRATED'
306 and mail_domain.mail
like '%@%'
307 and mail_domain.type =
0
308 and mail_domain.pop =
1;
310 insert into mailbox (address_id, path, delivery, lastlogin)
313 'dovecot' as delivery,
317 on domaines.id = address.domain_id
319 on mail_domain.mail = concat(address.address,
'@', domaines.domaine)
321 on locate(concat(trim(mail_users.alias),
' '), concat(trim(replace(mail_domain.alias,
'\n',
' ')),
' ')) =
1
322 where mail_domain.migration_state=
'UNMIGRATED'
323 and mail_domain.mail
like '%@%'
324 and mail_domain.type =
0
325 and mail_domain.pop =
1;
327 insert into recipient (address_id, recipients)
328 select address.id
as address_id,
329 recipients.recipients
332 on domaines.id = address.domain_id
334 mail_domain.mail
as mail,
335 trim(
both '\n' from trim(replace(mail_domain.alias, replace(mail_domain.mail,
'@',
'_'),
'')))
as recipients
337 where mail_domain.migration_state=
'UNMIGRATED'
338 and mail_domain.mail
like '%@%'
339 and mail_domain.type =
0
340 and mail_domain.pop =
1)
as recipients
341 on recipients.recipients !=
''
342 and recipients.mail = concat(address.address,
'@', domaines.domaine);
344 update mail_domain, domaines, mail_users
345 set mail_domain.migration_state =
'DB MIGRATED',
346 mail_users.migration_state =
'DB MIGRATED'
347 where mail_domain.migration_state=
'UNMIGRATED'
348 and domaines.domaine = substring_index(mail_domain.mail,
'@', -1)
349 and locate(concat(trim(mail_users.alias),
' '), concat(trim(replace(mail_domain.alias,
'\n',
' ')),
' ')) =
1
350 and mail_domain.mail
like '%@%'
351 and mail_domain.type =
0
352 and mail_domain.pop =
1;
354 update mail_domain, domaines, mail_users
355 set mail_users.migration_state =
'DB MIGRATED'
356 where mail_domain.migration_state=
'UNMIGRATED'
357 and domaines.domaine = substring_index(mail_domain.mail,
'@', -1)
358 and mail_users.alias = mail_domain.mail
359 and mail_domain.mail
like '%@%'
360 and mail_domain.type =
0
361 and mail_domain.pop =
1;
365 insert into address (domain_id, address, type, password)
366 select domaines.id
as domain_id,
367 substring_index(mail_domain.mail,
'@',
1)
as address,
372 on domaines.domaine = substring_index(mail_domain.mail,
'@', -1)
373 where mail_domain.mail
like '@%'
374 and mail_domain.type =
0
375 and mail_domain.pop =
0
376 and substring_index(mail_domain.mail,
'@',
1) =
'';
379 insert into address (domain_id, address, type, password)
380 select domaines.id
as domain_id,
381 substring_index(mail_domain.mail,
'@',
1)
as address,
386 on domaines.domaine = substring_index(mail_domain.mail,
'@', -1)
387 where mail_domain.mail
like '%@%'
388 and mail_domain.type =
0
389 and mail_domain.pop =
0
390 and substring_index(mail_domain.mail,
'@',
1) !=
'';
392 insert into recipient (address_id, recipients)
393 select address.id
as address_id,
394 trim(
both '\n' from recipients.recipients)
397 on domaines.id = address.domain_id
399 mail_domain.mail
as mail,
400 mail_domain.alias
as recipients
402 where mail_domain.migration_state=
'UNMIGRATED'
403 and mail_domain.mail
like '%@%'
404 and mail_domain.type =
0
405 and mail_domain.pop =
0)
as recipients
406 on recipients.recipients !=
''
407 and recipients.mail = concat(address.address,
'@', domaines.domaine);
409 update mail_domain, domaines
410 set mail_domain.migration_state =
'DB MIGRATED'
411 where mail_domain.migration_state=
'UNMIGRATED'
412 and domaines.domaine = substring_index(mail_domain.mail,
'@', -1)
413 and mail_domain.mail
like '%@%'
414 and mail_domain.type =
0
415 and mail_domain.pop =
0;
421 insert into address (domain_id, address, type, password)
422 select domaines.id
as domain_id,
423 substring_index(mail_domain.mail,
'@',
1)
as address,
428 on domaines.domaine = substring_index(mail_domain.mail,
'@', -1)
430 on mail_alias.mail = mail_domain.alias
431 where mail_domain.migration_state=
'UNMIGRATED'
432 and mail_domain.type =
1
433 and mail_domain.pop =
0
434 and mail_alias.alias
like '"| /var/lib/mailman/mail/mailman %';
436 update mail_domain, domaines, mail_alias
437 set mail_domain.migration_state =
'DB MIGRATED'
438 where mail_domain.migration_state=
'UNMIGRATED'
439 and domaines.domaine = substring_index(mail_domain.mail,
'@', -1)
440 and mail_alias.mail = mail_domain.alias
441 and mail_domain.type =
1
442 and mail_alias.alias
like '"| /var/lib/mailman/mail/mailman %';
445 insert into address (domain_id, address, type, password)
446 select domaines.id
as domain_id,
447 substring_index(mail_domain.mail,
'@',
1)
as address,
452 on domaines.domaine = substring_index(mail_domain.mail,
'@', -1)
454 on mail_alias.mail = mail_domain.alias
455 where mail_domain.migration_state=
'UNMIGRATED'
456 and mail_domain.type =
1
457 and mail_domain.pop =
0
458 and mail_alias.alias
like '"|/usr/lib/sympa/bin/bouncequeue %';
460 update mail_domain, domaines, mail_alias
461 set mail_domain.migration_state =
'DB MIGRATED'
462 where mail_domain.migration_state=
'UNMIGRATED'
463 and domaines.domaine = substring_index(mail_domain.mail,
'@', -1)
464 and mail_alias.mail = mail_domain.alias
465 and mail_domain.type =
1
466 and mail_alias.alias
like '"|/usr/lib/sympa/bin/bouncequeue %';
473 CREATE OR REPLACE VIEW `dovecot_view`
AS
474 SELECT concat(`address`.`address`,
'@',`domaines`.`domaine`)
AS `
user`,
475 concat(
'*:storage=',
cast(`mailbox`.`quota`
as char charset latin1),
'M')
AS `userdb_quota_rule`,
476 `address`.`password`
AS `password`,
477 `mailbox`.`path`
AS `userdb_home`,
478 `domaines`.`compte`
AS `userdb_uid`,
479 `domaines`.`compte`
AS `userdb_gid`,
480 `mailbox`.`bytes`
AS `quota_dovecot`,
481 `mailbox`.`messages`
AS `nb_messages`
483 join `address`
on((`address`.`id` = `mailbox`.`address_id`)))
484 join `domaines`
on((`domaines`.`id` = `address`.`domain_id`)))
485 where `address`.`enabled` =
1
492 CREATE OR REPLACE VIEW `alias_view`
AS
493 select concat(`address`.`address`,
'@',`domaines`.`domaine`)
AS `mail`,
494 concat(
if(isnull(`mailbox`.`id`),
'',concat(concat(`address`.`address`,
'@',`domaines`.`domaine`),
'\n')),`recipient`.`recipients`)
AS `alias`
495 from (((`recipient`
join `address`
on((`address`.`id` = `recipient`.`address_id`)))
496 left join `mailbox`
on((`mailbox`.`address_id` = `address`.`id`)))
497 join `domaines`
on((`domaines`.`id` = `address`.`domain_id`)))
498 where `address`.`enabled` =
1
500 select distinct concat(`m`.`login`,
'@',`v`.`
value`)
AS `mail`,
501 `m`.`mail`
AS `alias`
502 from ((`membres` `m`
join `variable` `v`)
join `domaines` `d`)
503 where (`v`.`name` =
'mailname_bounce');