upgrade/account/30_insertion.sql

   1 # Create a type 'X' with all permissions
   2 insert into account_types
   3      values ('x', 'mail,groups,forums,list,search,portal'),
   4             ('xnet', 'groups');
   5
   6
   7 # Insert all existing accounts
   8 insert into 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,
  18             NULL as email,
  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,
  23             IF(q.skin = 0, NULL, q.skin) AS skin,
  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;
  28
  29 # Insert carnet-relative data
  30 insert into watch
  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,
  36             q.watch_last as last
  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);
  42
  43 # Insert carvas
  44 insert into carvas
  45      select user_id, redirecturl
  46        from #x4dat#.auth_user_quick
  47       where LENGTH(redirecturl) > 0;
  48
  49 # Insert all existing profiles
  50 insert into 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 or u.nationalite = '00' or u.nationalite = '0', NULL, u.nationalite) AS nationality1,
  64             NULL AS nationality2, NULL AS nationality3, NULL AS email_directory, 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;
  68
  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;
  74
  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;
  80
  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);
  85
  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;
  90
  91 # vim:set syntax=mysql: