Cleanup forum_ tables:

[platal.git] / upgrade / account / 99_insertion.sql

diff --git a/upgrade/account/99_insertion.sql b/upgrade/account/99_insertion.sql
new file mode 100644 (file)
index 0000000..f4512d5
--- /dev/null
+++ b/upgrade/account/99_insertion.sql
@@ -0,0 +1,48 @@
+# Create a type 'X' with all permissions
+insert into account_types
+     values ('x', 'mail,groups,forums,list,search,portal');
+
+# Insert all existing accounts
+insert into accounts
+     select u.user_id AS uid, hruid AS hruid, 'x' AS type,
+            perms = 'admin' AS is_admin,
+            IF(perms = 'admin' or perms = 'user', 'active', perms) AS state,
+            IF(LENGTH(password) = 40, password, NULL) AS password,
+            IF(LENGTH(q.core_rss_hash) > 0, q.core_rss_hash, NULL) AS token,
+            date_ins AS registration_date,
+            IF(FIND_IN_SET('watch', flags), 'watch', '') AS flags,
+            IF(LENGTH(comment) > 0, comment, NULL) AS comment,
+            CONCAT(prenom, ' ', IF (nom_usage != '' and nom_usage IS NOT NULL, nom_usage, nom)) AS full_name,
+            prenom AS display_name,
+            IF(FIND_IN_SET('femme', flags), 'female', 'male') AS sex,
+            IF(q.core_mail_fmt = 'html', 'html', 'text') AS email_format,
+            q.skin AS skin
+       from auth_user_md5 as u
+  left join auth_user_quick as q on (q.user_id = u.user_id)
+      where hruid is not null;
+
+# Insert all existing profiles
+insert into profiles
+     select user_id AS pid, hruid AS hrpid, matricule AS xorg_id,
+            matricule_ax AS ax_id, naissance AS birthdate, naissance_ini AS birthdate_ref
+       from auth_user_md5
+      where hruid is not null;
+
+# Add associations account <-> profile
+insert into account_profiles
+     select user_id AS uid, user_id AS pid, 'owner' AS perms
+       from auth_user_md5
+      where hruid is not null;
+
+# Update banana last_seen timetamp
+    update  forum_profiles as fp
+inner join  auth_user_quick as q ON (q.user_id = fp.uid)
+       set  fp.uid = fp.uid, fp.tree_unread = fp.tree_unread, fp.tree_read = fp.tree_read,
+            fp.last_seen = q.banana_last;
+
+insert ignore into  forum_profiles (uid, last_seen)
+            select  user_id as uid, banana_last as last_seen
+              from  auth_user_quick
+             where  banana_last >= DATE_SUB(NOW(), INTERVAL 6 MONTH);
+
+# vim:set syntax=mysql: