The mailing list for listmasters using Sympa

[Olivier Salaun - CRU <address@concealed>]

Hi Eric,

Just a reminder for those who don't know the ability of Sympa to use
additional DB fields :
http://www.sympa.org/doc/sympa/node8.html#SECTION00860000000000000000

We have added the feature you asked for, a patch is attached.
The DB fields listed as 'db_additional_subscriber_fields' can now be edited in
editsubscriber page. If field type is ENUM, a drop-down menu is proposed.
This will only work with MySQL though (since I don't know how to access
fields types with other RDBMS).

Eric Sandquist wrote:
> In order to facilitate individual user moderation on specific lists, I 
> added subscriber_moderation enum(yes<default>, no, ban) to the 
> subscriber_table. I then added the new field to /etc/sympa.conf.
>  
> In the send.public scenarii I added this rule to the top of the rules:
>  
> equal([subscriber->subscriber_moderated],'yes') smtp,smime,md5    -> 
> editorkey
> equal([subscriber->subscriber_moderated],'ban') smtp,smime,md5    -> reject
> This seems to be working great... but there is no web-based 
> administration from sympa...
>  
> Is there a way to modify the editsubscriber.tpl to include a radio 
> button or drop down list for modifying individual users?  Or even 
> better, can we incorporate a new page which lists all users for that 
> list with the ability to modify that field for that list.... maybe even 
> include it in the page that lists all the users ( since the field shows 
> up there anyway).  Perhaps, I should be developing this mod myself, but 
> time is not permitting me right now...  Any help?  Else, the rest of 
> this mod will have to wait a few more weeks... :(


--
Olivier Salaun
Comite Reseau des Universites
? Makefile
? config.cache
? config.log
? config.status
? doc/sympa.aux
? doc/sympa.cb
? doc/sympa.dvi
? doc/sympa.idx
? doc/sympa.ilg
? doc/sympa.ind
? doc/sympa.log
? doc/sympa.out
? doc/sympa.toc
? doc/man8/alias_manager.8
? doc/man8/archived.8
? doc/man8/bounced.8
? doc/sympa/node21.html
? nls/cn.cat
? nls/cz.cat
? nls/de.cat
? nls/es.cat
? nls/et.cat
? nls/fi.cat
? nls/fr.cat
? nls/hu.cat
? nls/it.cat
? nls/nl.cat
? nls/pl.cat
? nls/ro.cat
? nls/tw.cat
? nls/us.cat
? src/aliaswrapper
? src/bouncequeue
? src/queue
? src/virtualwrapper
Index: src/List.pm
===================================================================
RCS file: /home/sympa/cvsroot/sympa/src/List.pm,v
retrieving revision 1.341
diff -c -r1.341 List.pm
*** src/List.pm 15 Nov 2002 08:05:25 -0000      1.341
--- src/List.pm 15 Nov 2002 09:33:04 -0000
***************
*** 3547,3552 ****
--- 3547,3568 ----
                          included => 'subscriber_table',
                          id => 'subscriber_table'
                          );
+ 
+       ## additional DB fields
+       if (defined $Conf{'db_additional_subscriber_fields'}) {
+           foreach my $f (split ',', 
$Conf{'db_additional_subscriber_fields'}) {
+               $map_table{$f} = 'subscriber_table';
+               $map_field{$f} = $f;
+           }
+       }
+ 
+       if (defined $Conf{'db_additional_user_fields'}) {
+           foreach my $f (split ',', $Conf{'db_additional_user_fields'}) {
+               $map_table{$f} = 'user_table';
+               $map_field{$f} = $f;
+           }
+       }
+       
        
        ## Check database connection
        unless ($dbh and $dbh->ping) {
***************
*** 6420,6425 ****
--- 6436,6476 ----
  
      closedir SPOOL;
      return ($#msg + 1);
+ }
+ 
+ ## Get the type of a DB field
+ sub get_db_field_type {
+     my ($table, $field) = @_;
+ 
+     return undef unless ($Conf{'db_type'} eq 'mysql');
+ 
+     ## Is the Database defined
+     unless ($Conf{'db_name'}) {
+       &do_log('info', 'No db_name defined in configuration file');
+       return undef;
+     }
+ 
+     unless ($dbh and $dbh->ping) {
+       return undef unless &db_connect();
+     }
+       
+     unless ($sth = $dbh->prepare("SHOW FIELDS FROM $table")) {
+       do_log('err','Unable to prepare SQL query : %s', $dbh->errstr);
+       return undef;
+     }
+     
+     unless ($sth->execute) {
+       do_log('err','Unable to execute SQL query : %s', $dbh->errstr);
+       return undef;
+     }
+           
+     while (my $ref = $sth->fetchrow_hashref()) {
+       next unless ($ref->{'Field'} eq $field);
+ 
+       return $ref->{'Type'};
+     }
+ 
+     return undef;
  }
  
  sub probe_db {
Index: wwsympa/wwsympa.fcgi
===================================================================
RCS file: /home/sympa/cvsroot/sympa/wwsympa/wwsympa.fcgi,v
retrieving revision 1.305
diff -c -r1.305 wwsympa.fcgi
*** wwsympa/wwsympa.fcgi        14 Nov 2002 09:26:20 -0000      1.305
--- wwsympa/wwsympa.fcgi        15 Nov 2002 09:33:05 -0000
***************
*** 2374,2379 ****
--- 2374,2386 ----
        
        $update->{'email'} = $in{'new_email'};
      }
+     
+     ## Get additional DB fields
+     foreach my $v (keys %in) {
+       if ($v =~ /^additional_field_(\w+)$/) {
+           $update->{$1} = $in{$v};
+       }
+     }
  
      $update->{'gecos'} = $in{'gecos'} if $in{'gecos'};
      
***************
*** 4732,4737 ****
--- 4739,4778 ----
        }       
  
        $param->{'previous_action'} = $in{'previous_action'};
+     }
+ 
+     ## Additional DB fields
+     if (defined ($Conf{'db_additional_subscriber_fields'})) {
+       my @additional_fields = split ',', 
$Conf{'db_additional_subscriber_fields'};
+       
+       my %data;
+ 
+       foreach my $field (@additional_fields) {
+ 
+           ## Is the Database defined
+           unless ($Conf{'db_name'}) {
+               &do_log('info', 'No db_name defined in configuration file');
+               return undef;
+           }
+ 
+           ## Check field type (enum or not) with MySQL
+           $data{$field}{'type'} = 
&List::get_db_field_type('subscriber_table', $field);
+           if ($data{$field}{'type'} =~ /^enum\((\S+)\)$/) {
+               my @enum = split /,/,$1;
+               foreach my $e (@enum) {
+                   $e =~ s/^\'([^\']+)\'$/$1/;
+                   $data{$field}{'enum'}{$e} = '';
+               }
+               $data{$field}{'type'} = 'enum';
+               
+               $data{$field}{'enum'}{$user->{$field}} = 'SELECTED'
+                   if (defined $user->{$field});
+           }else {
+               $data{$field}{'type'} = 'string';
+               $data{$field}{'value'} = $user->{$field};
+           } 
+       }
+       $param->{'additional_fields'} = \%data;
      }
  
      return 1;
Index: wwsympa/wws_templates/fr/editsubscriber.fr.tpl
===================================================================
RCS file: 
/home/sympa/cvsroot/sympa/wwsympa/wws_templates/fr/editsubscriber.fr.tpl,v
retrieving revision 1.8
diff -c -r1.8 editsubscriber.fr.tpl
*** wwsympa/wws_templates/fr/editsubscriber.fr.tpl      30 May 2002 09:12:16 
-0000      1.8
--- wwsympa/wws_templates/fr/editsubscriber.fr.tpl      15 Nov 2002 09:33:05 
-0000
***************
*** 21,26 ****
--- 21,38 ----
  
  <DD>Visibilité : [subscriber->visibility]
  <DD>Langue : [subscriber->lang]
+ [FOREACH field IN additional_fields]
+  [IF field->type=enum]
+     <DD>[field->NAME] :  <SELECT NAME="additional_field_[field->NAME]">
+                         <OPTION VALUE="">
+       [FOREACH e IN field->enum]
+                         <OPTION VALUE="[e->NAME]" [e]>[e->NAME]
+         [END]
+                        </SELECT>
+  [ELSE]
+     <DD>[field->NAME] : <INPUT NAME="additional_field_[field->NAME]" 
VALUE="[field->value]" SIZE="25">
+  [ENDIF]
+ [END]
  <DD><INPUT TYPE="submit" NAME="action_set" VALUE="Mise à jour">
  <INPUT TYPE="submit" NAME="action_del" VALUE="Désabonner l'usager">
  <INPUT TYPE="checkbox" NAME="quiet"> sans prévenir
Index: wwsympa/wws_templates/us/editsubscriber.us.tpl
===================================================================
RCS file: 
/home/sympa/cvsroot/sympa/wwsympa/wws_templates/us/editsubscriber.us.tpl,v
retrieving revision 1.7
diff -c -r1.7 editsubscriber.us.tpl
*** wwsympa/wws_templates/us/editsubscriber.us.tpl      30 May 2002 09:12:03 
-0000      1.7
--- wwsympa/wws_templates/us/editsubscriber.us.tpl      15 Nov 2002 09:33:05 
-0000
***************
*** 21,26 ****
--- 21,38 ----
  
  <DD>Visibility : [subscriber->visibility]
  <DD>Language : [subscriber->lang]
+ [FOREACH field IN additional_fields]
+  [IF field->type=enum]
+     <DD>[field->NAME] :  <SELECT NAME="additional_field_[field->NAME]">
+                         <OPTION VALUE="">
+       [FOREACH e IN field->enum]
+                         <OPTION VALUE="[e->NAME]" [e]>[e->NAME]
+         [END]
+                        </SELECT>
+  [ELSE]
+     <DD>[field->NAME] : <INPUT NAME="additional_field_[field->NAME]" 
VALUE="[field->value]" SIZE="25">
+  [ENDIF]
+ [END]
  <DD><INPUT TYPE="submit" NAME="action_set" VALUE="Update">
  <INPUT TYPE="submit" NAME="action_del" VALUE="Unsubscribe the User">
  <INPUT TYPE="checkbox" NAME="quiet"> quiet