Moving to GitHub.
[platal.git] / 0.9.4 / 20_register.sql
1 -- MySQL dump 9.11
2
3 --
4 -- Table structure for table register_pending
5 --
6
7 CREATE TABLE register_pending (
8 uid int NOT NULL default '0',
9 forlife varchar(255) NOT NULL default '',
10 bestalias varchar(255) NOT NULL default '',
11 mailorg2 varchar(255) default null,
12 password varchar(32) NOT NULL default '',
13 email varchar(255) NOT NULL default '',
14 date date NOT NULL default '0000-00-00',
15 relance date NOT NULL default '0000-00-00',
16 naissance date NOT NULL default '0000-00-00',
17 hash varchar(12) NOT NULL default '',
18 PRIMARY KEY (uid),
19 UNIQUE KEY (forlife),
20 UNIQUE KEY (bestalias),
21 INDEX (hash)
22 );
23
24 create table register_marketing (/*{{{*/
25 uid int not null default 0,
26 sender int not null default 0,
27 email varchar(255) not null default '',
28 date date not null default '0000-00-00',
29 last date not null default '0000-00-00',
30 nb tinyint not null default 0,
31 type enum('user', 'staff') not null default 'user',
32 hash char(32) not null,
33 PRIMARY KEY (uid, email),
34 INDEX (date),
35 INDEX (last),
36 INDEX (nb)
37 );
38
39 create table register_mstats (
40 uid int not null default 0,
41 sender int not null default 0,
42 success date not null default '0000-00-00',
43 PRIMARY KEY (uid)
44 );
45
46
47 --
48 -- envoidirect -> register_mstats
49 --
50
51 insert into register_mstats (uid, sender, success)
52 select distinct u.user_id, e.sender, u.date_ins
53 from envoidirect as e
54 inner join auth_user_md5 as u using(matricule)
55 where u.date_ins != 0 group by user_id;
56
57 insert into register_marketing (uid, sender, email, date, last, nb, type, hash)
58 select u.user_id, m.expe, m.email, 0, 0, FIND_IN_SET('envoye', m.flags), IF(FIND_IN_SET('mail_perso', m.flags), 'user', 'staff'), ''
59 from marketing as m
60 inner join auth_user_md5 as u on u.matricule = m.dest
61 where date_ins = 0 and deces = 0
62 group by user_id, m.email;
63
64 replace into register_marketing (uid, sender, email, date, last, nb, type, hash)
65 select u.user_id, e.sender, e.email, date_envoi, date_envoi, 1, 'staff', e.uid
66 from envoidirect as e
67 inner join auth_user_md5 as u using(matricule)
68 where date_ins = 0 and deces = 0;
69
70 drop table envoidirect;
71 drop table marketing;/*}}}*/
72
73 insert into register_pending (uid, forlife, bestalias, mailorg2, password, email, date, relance, naissance, hash)
74 select u.user_id, e.username,
75 IF(e.loginbis, e.loginbis, REPLACE(REPLACE(e.username, '.19', ''), '.20', '')),
76 IF(e.loginbis, REPLACE(REPLACE(e.username, '.19', ''), '.20', ''), NULL),
77 e.password, email, e.date, relance, e.naissance, ins_id
78 from en_cours AS e
79 inner join auth_user_md5 AS u USING( matricule )
80 where u.perms = 'pending';
81
82 drop table en_cours;