We use postfixadmin and have a mailbox_postdeletion_script which does the job of "removing" the actual maildir (though we don't delete, we move it aside for a couple of weeks, so we can help folks restore when they've made mistakes.) That script also does some cleanup on the roundcubemail database -- and I want to enhance it to similarly not actually delete users roundcube data, but just mark the del columns -- Then we can periodically use the roundcube/bin/cleandb.sh script to make the removal permanent.
My question is, not all tables have the del column, like the root of all tables: the users table. (Also the contactgroups table -- but it looks like the cleandb.sh script might deal with that??) So what exactly is recommend db maintenance when removing a mailbox?
I was looking at doing something like::::::
SELECT user_id FROM users WHERE username LIKE '$ARGV' UPDATE identities SET del=1 WHERE user_id = '$user_id' DELETE FROM cache WHERE user_id = '$user_id' UPDATE contacts SET del=1 WHERE user_id = '$user_id' UPDATE contactgroups SET del=1 WHERE user_id = '$user_id' DELETE FROM messages WHERE user_id = '$user_id'
??DELETE FROM users WHERE username = '$ARGV' LIMIT 1??
This last line is where I get stuck because I don't really want to delete it, but there is no del column to mark. . .
Any input much appreciated ;)
Ben _______________________________________________ List info: http://lists.roundcube.net/dev/ BT/aba52c80
On 18.02.2011 03:24, ben@electricembers.net wrote:
SELECT user_id FROM users WHERE username LIKE '$ARGV' UPDATE identities SET del=1 WHERE user_id = '$user_id' DELETE FROM cache WHERE user_id = '$user_id' UPDATE contacts SET del=1 WHERE user_id = '$user_id' UPDATE contactgroups SET del=1 WHERE user_id = '$user_id' DELETE FROM messages WHERE user_id = '$user_id'
??DELETE FROM users WHERE username = '$ARGV' LIMIT 1??
This last line is where I get stuck because I don't really want to delete it, but there is no del column to mark. . .
When you delete a user record all related records in other tables will be auto-removed because of foreign key constraints.
We would probably need to add users.del column, but there will be a problem with uniqueness constraint (or creation of the same user will fail until you remove the original record). I don't know if this is a real problem. Then all you need will be just to set this column.