[Savannah-cvs] [SCM] Savane-cleanup framework branch, master, updated. 1be10a21d01dca8cddfe539fa485794f2781180b

[Sylvain Beucler]

This is an automated email from the git hooks/post-receive script. It was
generated because a ref change was pushed to the repository containing
the project "Savane-cleanup framework".

The branch, master has been updated
       via  1be10a21d01dca8cddfe539fa485794f2781180b (commit)
      from  13f44c2d03f645cb906dd1feaefdb890c7b57e77 (commit)

Those revisions listed above that are new to this repository have
not appeared on any other notification email; so we list those
revisions in full, below.

- Log -----------------------------------------------------------------
http://git.savannah.gnu.org/cgit/savane-cleanup/framework.git/commit/?id=1be10a21d01dca8cddfe539fa485794f2781180b

commit 1be10a21d01dca8cddfe539fa485794f2781180b
Author: Sylvain Beucler <address@hidden>
Date:   Thu Jul 30 21:36:30 2009 +0200

    Improve migration script, takes better care of duplicates and dangling FKs

diff --git a/MIGRATION.txt b/MIGRATION.txt
index ec89cc7..4b3c584 100644
--- a/MIGRATION.txt
+++ b/MIGRATION.txt
@@ -16,10 +16,6 @@
 Users
 =====
 
-To remove duplicate users *FOR TESTING*:
-(TODO: need to remove only the duplicate, not both)
-DELETE FROM user WHERE user_name IN 
('adiebald','alkana','andersgh','andrenix','animalfarm','antigerme','baravalle','Bassslinger','blitzkerk','bolek11','brawil1','brijesh_gadhiya','caribe','caterkiller2001','cce','chawil','conan','cux221','dave_san','ddavies','doudou61','dungeon','elektroland','emil5','ericclaassen','espahr','fcattoen','gauntlet','Gerardoco','Gonzalvez','GOOS','grubthosch','hans-peter','heikorah','helen','jaguaryou','jezdikm','JonGretar','kodion','kpc82','kumud','lamborghini0102','markm','mbaruchel','mdopheide','moocha','Netsnipe','osia','otello','pepex82','phgbest007','planetstar','plijnzaad','plomo00','Progenator','pthom','puyo','qemm','quaxter','rafaelrt','rdunphy','realshitok','roeles','rubenperez','scorbeau','seb_cante','SeeksTheMoon','shezAbrat','shiau','shyguy','siman','sjh0320','sorenoid','Steve_Stanfield','surfnix54','suydam','Sweethrt758','taroo','tdp','The_WABBIT','thresher','tklein','tontonraoul','tony-rs','treker','tt3','vdemart','vinod','vka3','wbrown','Webb','wertzu','willou','wutzkem');
-
 Warnings during import:
 
 TODO: I don't really understand why three are illegal UTF-8 strings,
diff --git a/migrate_old_savane.sql b/migrate_old_savane.sql
index 6a3f8a5..6b7a079 100644
--- a/migrate_old_savane.sql
+++ b/migrate_old_savane.sql
@@ -1,6 +1,22 @@
--- USE savane;
+-- Some clean-up is done on the savane_old database.  It may sound
+-- better to leave savane_old read-only, but at the same time this
+-- means we can experiment the clean-ups on live "old savane" installs
+-- before the migration.
+
 
 -- Import all users except for the 'None' user (#100)
+-- Get rid of duplicates (old mysql/php/savane bug?)
+USE savane_old;
+DELETE FROM user
+  WHERE user_id IN (
+    SELECT user_id FROM (
+      SELECT B.user_id FROM user A, user B
+        WHERE A.user_id < B.user_id AND A.user_name = B.user_name
+      ) AS temp
+    );
+USE savane;
+-- Actual import
+TRUNCATE auth_user;
 INSERT INTO auth_user
     (id, username, first_name, last_name, email,
      password, last_login, date_joined, is_active,
@@ -13,6 +29,7 @@ INSERT INTO auth_user
 
 -- Import all extended information except for the 'None' user (#100)
 -- (X or 0) means 'if V==NULL then 0 else V'
+TRUNCATE svmain_extendeduser;
 INSERT INTO svmain_extendeduser
     (user_ptr_id, status, spamscore, authorized_keys,
      authorized_keys_count, people_view_skills, people_resume,
@@ -26,6 +43,7 @@ INSERT INTO svmain_extendeduser
 
 -- Import group configurations
 -- type_id -> id
+TRUNCATE svmain_groupconfiguration;
 INSERT INTO svmain_groupconfiguration
     (id, name, description, base_host,
      mailing_list_address, mailing_list_virtual_host, mailing_list_format,
@@ -153,6 +171,7 @@ INSERT INTO svmain_groupconfiguration
 -- Import groups
 -- id <- group_id
 -- name <- unix_group_name
+TRUNCATE auth_group;
 INSERT INTO auth_group
     (id, name)
   SELECT group_id, unix_group_name
@@ -272,15 +291,48 @@ INSERT INTO svmain_extendedgroup
     WHERE savane_old.groups.group_id != 100;
 
 -- Import users<->groups relationships
--- Get rid of duplicates (long: several minutes):
-DELETE FROM savane_old.user_group
+-- Get rid of duplicates
+USE savane_old;
+-- Give priority to non-pending memberships
+DELETE FROM user_group
+  WHERE user_group_id IN (
+    SELECT user_group_id FROM (
+      SELECT B.user_group_id FROM user_group A, user_group B
+        WHERE A.admin_flags <> 'P' AND B.admin_flags = 'P'
+          AND A.user_id = B.user_id AND A.group_id = B.group_id
+      ) AS temp
+    );
+-- Delete other duplicates, give priority to the first one
+DELETE FROM user_group
   WHERE user_group_id IN (
-    SELECT A.user_group_id
-      FROM savane_old.user_group A, savane_old.user_group B
-      WHERE A.user_id = B.user_id AND A.group_id = B.group_id
-      GROUP BY A.user_id, A.group_id HAVING count(*) > 1
+    SELECT user_group_id FROM (
+      SELECT B.user_group_id FROM user_group A, user_group B
+        WHERE A.user_group_id < B.user_group_id
+          AND A.user_id = B.user_id AND A.group_id = B.group_id
+      ) AS temp
+    );
+-- Get rid of ghost relationships (deleted group)
+DELETE FROM user_group
+  WHERE group_id IN (
+    SELECT group_id FROM (
+      SELECT user_group.group_id
+        FROM user_group
+          LEFT JOIN groups ON user_group.group_id = groups.group_id
+        WHERE groups.group_id IS NULL
+      ) AS temp
+    );
+-- Get rid of ghost relationships (deleted user)
+DELETE FROM user_group WHERE user_id IN (
+  SELECT user_id FROM (
+    SELECT user_group.user_id
+      FROM user_group
+        LEFT JOIN user ON user_group.user_id = user.user_id
+      WHERE user.user_id IS NULL
+    ) AS temp
   );
+USE savane;
 -- Actual import
+TRUNCATE auth_user_groups;
 INSERT INTO auth_user_groups
     (user_id, group_id)
   SELECT user_id, group_id
@@ -289,29 +341,11 @@ INSERT INTO svmain_membership
     (user_id, group_id, admin_flags, onduty)
   SELECT user_id, group_id, admin_flags, onduty
     FROM savane_old.user_group;
--- Get rid of ghost relationships (deleted group)
-DELETE FROM svmain_membership
-  WHERE group_id IN (
-    SELECT group_id FROM (
-      SELECT group_id
-        FROM svmain_membership
-          LEFT JOIN svmain_extendedgroup ON svmain_membership.group_id = 
svmain_extendedgroup.group_ptr_id
-        WHERE group_ptr_id IS NULL
-      ) AS temp
-    );
--- Get rid of ghost relationships (deleted user)
-DELETE FROM svmain_membership WHERE user_id IN (
-  SELECT user_id FROM (
-    SELECT user_id
-      FROM svmain_membership
-        LEFT JOIN svmain_extendeduser ON svmain_membership.user_id = 
svmain_extendeduser.user_ptr_id
-      WHERE user_ptr_id IS NULL
-    ) AS temp
-  );
 -- Set members of 'administration' as superusers
 -- TODO: get the supergroup name from the old Savane configuration
 UPDATE auth_user SET is_staff=1, is_superuser=1
   WHERE id IN (
     SELECT user_id
     FROM auth_user_groups JOIN auth_group ON auth_user_groups.group_id = 
auth_group.id
-    WHERE auth_group.name='administration');
+    WHERE auth_group.name='administration'
+  );

-----------------------------------------------------------------------

Summary of changes:
 MIGRATION.txt          |    4 --
 migrate_old_savane.sql |   88 +++++++++++++++++++++++++++++++++---------------
 2 files changed, 61 insertions(+), 31 deletions(-)


hooks/post-receive
-- 
Savane-cleanup framework