Details
-
Type:
New Feature
-
Status:
Open
-
Priority:
Minor
-
Resolution: Unresolved
-
Affects Version/s: 1.8
-
Fix Version/s: None
-
Component/s: Administration
-
Labels:None
-
Affected Branches:MOODLE_18_STABLE
Description
This is step one to implementing true deletion of users from the database: add a new required function to activity modules.
Moodle decision not to truly delete users is correct one. We don't want any user-relating activities dangling without having an owner.
However, as time progresses, our user tables (at least those not using external authentication methods like extDB or LDAP), keep swelling. It is my observation that after a few semesters, as the courses are being recycled for new students (admittedly not all sites do that), data for many inactive/deleted students is removed from Moodle. THat means we have a number of users in the database who have no data related to them and could be cleanly and safely removed.
The problem is to identify those users. This has to be done individually by each activity module since only they know how their data is related to specific users. Hence, I suggest to required all modules to add a function which reports whether a given user has any data for a specific activity. I don't mean a specific instance of an activity in a specificcourse but any instance of a given activity in any course.
This function can be then used for user overview and for checking whether a given user has any data.
Issue Links
Activity
Recently I tried to build an ad hoc function that removes all deleted users from all tables containing columns like “userid”. It works against Postgresql database (tested on Moodle 2.0.1 databases). It isn’t a plugin and may be run independently in a query environment like pgAdmin. I believe it can be easily adapted into MySQL.
Advantages of the function: an easy way to clean up database without removing courses, groups, etc.
Disadvantages: lack of removing users’ files, loosing threads on forums and emails.
The use of it is preferred in a strictly restricted Moodle configuration like mine where I have only lessons and quizzes, and haven’t neither emails nor forums. Otherwise one can use it when awares of results.
-------- sweeping...
DROP FUNCTION IF EXISTS remove_users();
DROP TABLE IF EXISTS tabnames;
DROP TABLE IF EXISTS results CASCADE;
DROP TABLE IF EXISTS ideleted CASCADE;
---------------------------------------
CREATE temporary TABLE tabnames (tabname varchar); – temporary store of tables names
CREATE temporary TABLE ideleted (id bigint); – temporary store of users' ids
CREATE temporary TABLE results (tabname text, counts bigint); – where to keep results log
INSERT INTO ideleted SELECT id from mdl_user WHERE deleted=1;
INSERT INTO tabnames
SELECT DISTINCT table_name FROM information_schema.columns WHERE column_name ='userid' ORDER BY table_name;
CREATE FUNCTION remove_users() RETURNS void AS $$
DECLARE tabname text;
counter bigint;
BEGIN
FOR tabname IN SELECT * FROM tabnames
LOOP
EXECUTE 'select count
as counts from ' || tabname || ' where userid in (select id from ideleted)' INTO counter;
IF counter IS NOT NULL AND counter > 0 THEN
– Removes users from 'tabname' table
EXECUTE 'delete from ' || tabname || ' where userid in (select id from ideleted)';
– Inserts into the 'results' log table
EXECUTE 'insert into results values (''' || tabname || ''',' || counter || ')';
END IF;
END LOOP;
– Removes users from 'mdl_user' table
DELETE FROM mdl_user WHERE deleted=1;
--additional removing from other tables (aren't shown in results log)
DELETE FROM mdl_tag_instance WHERE tiuserid IN (SELECT id FROM ideleted);
DELETE FROM mdl_message WHERE useridfrom IN (SELECT id FROM ideleted) OR useridto IN (SELECT id FROM ideleted);
DELETE FROM mdl_message_read WHERE useridfrom IN (SELECT id FROM ideleted) OR useridto IN (SELECT id FROM ideleted);
RETURN;
END;
$$ LANGUAGE plpgsql;
– Run the procedure
SELECT remove_users();
– View how many users have been removed from 'tabname' tables
SELECT * FROM results ORDER BY tabname;
as counts from ' || tabname || ' where userid in (select id from ideleted)' INTO counter;
IF counter IS NOT NULL AND counter > 0 THEN
– Removes users from 'tabname' table
EXECUTE 'delete from ' || tabname || ' where userid in (select id from ideleted)';
– Inserts into the 'results' log table
EXECUTE 'insert into results values (''' || tabname || ''',' || counter || ')';
END IF;
END LOOP;
– Removes users from 'mdl_user' table
DELETE FROM mdl_user WHERE deleted=1;
--additional removing from other tables (aren't shown in results log)
DELETE FROM mdl_tag_instance WHERE tiuserid IN (SELECT id FROM ideleted);
DELETE FROM mdl_message WHERE useridfrom IN (SELECT id FROM ideleted) OR useridto IN (SELECT id FROM ideleted);
DELETE FROM mdl_message_read WHERE useridfrom IN (SELECT id FROM ideleted) OR useridto IN (SELECT id FROM ideleted);
RETURN;
END;
$$ LANGUAGE plpgsql;
– Run the procedure
SELECT remove_users();
– View how many users have been removed from 'tabname' tables
SELECT * FROM results ORDER BY tabname; 
Hi,
I've made a little addon to clean up the database from all informations relative to some users.
It does this, preserving some data (like forums threads...) to keep the coherence of exchanges, with the goal of taking advantage of the knowledge.
A thread was created on the Using Moodle General Developper Forum to keep a track of comments, ideas, criticism.
The link is [url=http://moodle.org/mod/forum/discuss.php?d=94496]http://moodle.org/mod/forum/discuss.php?d=94496[/url]
I think the goals of what you describe and the addon I've made are similar but their ways seem to be different.
Maybe can we use the thread mentioned before to centralize the need of every body and use this issue to formalize the feature, what do you think about ?
Eric