1 #
Create a
type 'X' with all permissions
2 insert into account_types
3 values ('x', 'mail,groups,forums,list,search,portal'),
7 #
Insert all existing accounts
9 select u.user_id
AS uid
, hruid
AS hruid
, 'x' AS type,
10 perms
= 'admin' AS is_admin
,
11 IF(perms
= 'admin' or perms
= 'user', 'active', perms
) AS state,
12 IF(LENGTH(password) = 40, password, NULL) AS password,
13 IF(LENGTH(q.core_rss_hash
) > 0, q.core_rss_hash
, NULL) AS token,
14 IF(LENGTH(smtppass
) = 0, NULL, smtppass
) AS weak_password
,
15 date_ins
AS registration_date
,
16 IF(FIND_IN_SET('watch', flags
), 'watch', '') AS flags
,
17 IF(LENGTH(comment) > 0, comment, NULL) AS comment,
19 CONCAT(prenom
, ' ', IF (nom_usage
!= '' and nom_usage
IS NOT NULL, nom_usage
, nom
)) AS full_name
,
20 prenom
AS display_name
,
21 IF(FIND_IN_SET('femme', flags
), 'female', 'male') AS sex
,
22 IF(q.core_mail_fmt
= 'html', 'html', 'text') AS email_format
,
24 q.last_version
AS last_version
25 from #x4dat#.auth_user_md5
as u
26 left join #x4dat#.auth_user_quick
as q
on (q.user_id
= u.user_id
)
27 where hruid
is not null;
29 #
Insert carnet
-relative data
31 select q.user_id
as uid
, q.watch_flags
as flags
,
32 CONCAT(IF(ws1.cid
IS NULL, '', 'profile'), ',',
33 IF(ws2.cid
IS NULL, '', 'registration'), ',',
34 IF(ws3.cid
IS NULL, '', 'death'), ',',
35 IF(ws4.cid
IS NULL, '', 'birthday')) AS actions
,
37 from #x4dat#.auth_user_quick
as q
38 left join #x4dat#.watch_sub
as ws1
on (ws1.uid
= q.user_id
and ws1.cid
= 1)
39 left join #x4dat#.watch_sub
as ws2
on (ws2.uid
= q.user_id
and ws2.cid
= 2)
40 left join #x4dat#.watch_sub
as ws3
on (ws3.uid
= q.user_id
and ws3.cid
= 3)
41 left join #x4dat#.watch_sub
as ws4
on (ws4.uid
= q.user_id
and ws4.cid
= 4);
45 select user_id
, redirecturl
46 from #x4dat#.auth_user_quick
47 where LENGTH(redirecturl
) > 0;
49 #
Insert all existing profiles
51 select u.user_id
AS pid
, u.hruid
AS hrpid
, u.matricule
AS xorg_id
,
52 u.matricule_ax
AS ax_id
, u.naissance
AS birthdate
, u.naissance_ini
AS birthdate_ref
,
53 u.naissance
AS next_birthday
,
54 IF(u.deces
= 0, NULL, u.deces
) AS deathdate
,
55 IF(u.deces
= 0, NULL, u.deces
) AS deathdate_rec
,
56 IF(FIND_IN_SET('femme', flags
), 'female', 'male') AS sex
,
57 IF(u.
section = 0, NULL, u.
section) AS section,
58 IF(LENGTH(u.cv
) > 0, u.cv
, NULL) AS cv
,
59 IF(LENGTH(q.profile_freetext
) > 0, q.profile_freetext
, NULL) AS freetext
,
60 IF(q.profile_freetext_pub
= 'public', 'public', 'private') AS freetext_pub
,
61 IF(q.profile_medals_pub
= 'public', 'public', 'private') AS medals_pub
,
62 IF(q.emails_alias_pub
= 'public', 'public', 'private') AS alias_pub
,
63 IF(u.nationalite
= '' OR u.nationalite
IS NULL, NULL, u.nationalite
) AS nationality1
,
64 NULL AS nationality2
, NULL AS nationality3
, u.
date AS last_change
65 from #x4dat#.auth_user_md5
AS u
66 left join #x4dat#.auth_user_quick
AS q
ON (u.user_id
= q.user_id
)
67 where u.hruid
is not null;
69 #
Add associations account
<-> profile
70 insert into account_profiles
71 select user_id
AS uid
, user_id
AS pid
, 'owner' AS perms
72 from #x4dat#.auth_user_md5
73 where hruid
is not null;
75 #
Update banana last_seen timetamp
76 update forum_profiles
as fp
77 inner join #x4dat#.auth_user_quick
as q
ON (q.user_id
= fp.uid
)
78 set fp.uid
= fp.uid
, fp.tree_unread
= fp.tree_unread
, fp.tree_read
= fp.tree_read
,
79 fp.last_seen
= q.banana_last
;
81 insert ignore into forum_profiles (uid
, last_seen
)
82 select user_id
as uid
, banana_last
as last_seen
83 from #x4dat#.auth_user_quick
84 where banana_last
>= DATE_SUB(NOW(), INTERVAL 6 MONTH);
86 # Mail
storage has been moved
out of account settings
87 insert into email_options
88 select user_id
as uid
, mail_storage
as storage
89 from #x4dat#.auth_user_md5
;
91 # vim
:set syntax
=mysql
: