Admin can subscribe users regarding of their groups

Hi Tom,
Sorry for the long time no see, I'm not sure you received my mail last week, but I finally started to work on user subscriptions through administration.

Here are the descriptions of what does my patch :
- put a little more help (still a lot of work to do about making it clearer)
- add an administer subscriptions to allow some roles to subscribe others roles. It's a functionality, that I would need, because with the power of taxonomy acces, I would be able to give to some users create/edit node in some vocabulary and also give them the power to subscribe users to receive modifications.
- there's a lack of Doxygen comment, it's still pending
- subscriptions_mailvars is changed and split with a new one : _subscriptions_mailvars. For taxa subscriptions, it looks if a role is subscribed and sent mail to all users who accept it (see later on user management).
- subscriptions_menu is modified to take care of new user access rules and new functions.
- subscriptions_get_taxa_count is changed to count roles subscriptions
- subscriptions_page was split to add subscriptions_admin to manage admin task.
Admin task are :
+ subscribe roles to categories
+ manage exceptions (default, refuse, subscribe)
Boths task are for the moment limited to category, but I would extend this to blog & node.
- user management : in the admin of subscriptions, you can see the idea, dealing with exceptions : I mean a user can manage categories in 3 states :
+ default : if a role he belongs to will be subscribed he will receive notifications.
+ refuse : he would never get subscriptions for this category
+ accept : he would always get notifications even if his roles aren't subscribed anymore.
This should also apply for individual subscriptions management "my subscriptions", but it's a pending state, but patch will be very easy too do.

So this patch is basically a first draft, there is still problems that I have to work around: better help, better coding (I don't like the way I handle role subscriptions and exceptions. It would be so easy with a single query (look in subscriptions_mailvars ) but, without UNION clause, I don't see how to make it.
Etc...

I hope it would help you, and if anyone has any suggestion to make, feel free.

Of course I add to add a DB modifications :

#
# Table structure for table `subscriptions`
#

CREATE TABLE subscriptions (
  sid int(10) unsigned NOT NULL default '0',
  uid int(10) unsigned NOT NULL default '0',
  stype varchar(4) NOT NULL default '',
  status tinyint(1) NOT NULL default '1'
) TYPE=MyISAM;

# --------------------------------------------------------

#
# Table structure for table `subscriptions_role`
#

CREATE TABLE subscriptions_role (
  sid int(10) unsigned NOT NULL default '0',
  rid int(10) unsigned NOT NULL default '0',
  stype varchar(4) NOT NULL default ''
) TYPE=MyISAM;

I would let this as it is for the moment, as I would start using it for my intranet, just a few thoughts :

You remove "maintain subscriptions" in the _perm hook but still reference it in the _menu hook. I think you meant to have both a "maintain subscriptions" and "administer subscriptions" item in there.

- I didnt, the patch don't put a "-" in front of "maintain subscriptions"
- If you can improve help, it should be fine, I made it a little fast and it's not very clear.

You seem to remove the "return $strsent" line from the end of _subscriptions_mailvars... is this right?

- It's also a confusion from patch, but it's still there.

The fact that we have a _subscriptions_mailvars() and subscriptions_mailvars() is confusing. Feel free to rename the functions.

- I will.

db_queryd -> db_query

- That's a debugging bug, thanks for the catch.

Does IFNULL() work in postgresql?

- I thought it would work :(, well we can change by COALESCE

Btw, there's still a lot a things missing :
- let user manage its subscriptions the same way as admin does (subscribe/default/refuse), but that will led me to the problem of how would it be handled in the hook_links ?
- let blog and thread be managed by admin (option) ... Problem : how do we deals with this ? Basically, threads and blogs appear in admin page only if there's one subscriber. So if nobody subscribed, admin can't subscribed people. So is it a needed issue to subscribe role to thread or not ? I would say no, but it worth ask the question.
If yes, how do we display all node/blog...
- how manage be lists of users ? In my test site, I only add very few user, so when I go to admin/subscriptions and would like to change a category exceptions (adding or leaving inidivual subscribers), I only get a few line... If there's thousand of users, it would lead to get a huge page... not a god UI I guess.
So, I was thinking in put like 10 lines of input text to capture username, and at the side, there would have the same item select box (subscribe/refuse/default) to change current username subscriptions exception. The little problem, is that it could be a little difficult to display the current status if there already have a special one. But maybe we can do the trick with some special javascript option list ( ) but it's not my favourite option... Maybe display the user with not a default status, and the provide the list downside. For such list, I was aslo thinking about some AJAX trick to get the user list (see : AJAX form_autocomplete() field).

Well a lot of thoughts, I hope we could resolve this, and I'm waiting to code some ideas if anyone leave some more comments.

ups, my post was shortened :(
Ok so :
So, I was thinking in put like 10 lines of input text to capture username, and at the side, there would have the same item select box (subscribe/refuse/default) to change current username subscriptions exception. The little problem, is that it could be a little difficult to display the current status if there already have a special one. But maybe we can do the trick with some special javascript option list (Dynamic Option List) but I'm not sure it's so Drupal friendly...
Another way, should be to display only users with a special status (subscribe/refuse) and then the fields for modifying other users. Also providing an AJAX trick to help selecting users, should help (see : AJAX form_autocomplete() field).

Well, that's a lot of thoughts, if anyone has some more comment, feel free.
I'm waiting a little, and should retake this patch later.