2 patches

[Dominique ROUSSEAU <adresse@cachée>]

Ci-joint des aptches pour 2 modif que j'ai faite sur la version 2.2.7 de 
Sympa :

- Dans la version distribuee, Sympa n'est pas compatible avec postgres (en 
tout cas la
  v6.3.2) en ce qui concerne les requetes SQL qu'il effectue (utilisation de 
fonctions
  specifiques a MySQL) pour les ajouts/selections d'utilisateurs dans les 
tables.
  J'ai modifie les requetes en question pour qu'elles soient compatibles (et 
en principe
  devraient tjs fonctionner sur MySQL)

  Le patch est dans pg_compatibility-List_pm.patch qui aptche le fichier 
List.pm



- La deuxieme modif concerne l'utilisation d'une table de la base pour lenvoi 
des messages 
  (bienvenue, quit, ...) Le patch permet d'extraire les messages d'une table 
appelee file,
  avec la structure suivante :
  
mysql> describe file;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| list    | varchar(50) | YES  |     | NULL    |       |
| action  | varchar(50) | YES  |     | NULL    |       |
| content | text        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

  Lorsque l'info n'est pas trouvee dans la table, il se rabat sur le fichier 
(fonctionnement
  classique)

  Le patch est dans :
  files_from_db-List_pm.patch qui aptche List.pm
  files_from_db-mail_pl.patch qui patche mail.pl


Dites moi ce que vous en pensez...

Dom

--
Dominique Rousseau <adresse@cachée>          
Neuronnexion - http://www.neuronnexion.com            
--- /nnx/sympa/inst/sympa-2.2.7/src/List.pm     Mon Jun 21 17:57:13 1999
+++ bin/List.pm Tue Aug 17 15:52:23 1999
@@ -199,6 +199,10 @@
        return undef;
     }
 
+    if ($Conf{'db_type'} eq 'Pg') { # Configure Postgres to use ISO format 
dates
+       $dbh->do ("SET DATESTYLE TO 'ISO';");
+    }
+
     do_log('debug','Connected to Database %s',$Conf{'db_name'});
 
     return 1;
@@ -942,7 +947,8 @@
            return undef unless db_connect();
        }
 
-       unless ($statement = sprintf "SELECT email_user as email, gecos_user 
as gecos, reception_subscriber as reception, visibility_subscriber as 
visibility, cookie_delay_user as cookie_delay, lang_user as lang, 
UNIX_TIMESTAMP(date_subscriber) as date, substring_index(email_user,'.',-1) 
AS top, substring_index(replace(email_user,'\@','.'),'.',-2) AS dom FROM 
user,subscriber WHERE (list_subscriber = %s AND email_user = user_subscriber) 
ORDER BY top, dom",$dbh->quote($name)) {
+       unless ($statement = sprintf "SELECT email_user AS email, gecos_user 
AS gecos, reception_subscriber AS reception, visibility_subscriber AS 
visibility, cookie_delay_user AS cookie_delay, lang_user AS lang, 
date_subscriber AS date, SUBSTRING(user_subscriber FROM position('\@' IN 
user_subscriber) FOR 50) AS dom FROM user,subscriber WHERE (list_subscriber = 
%s AND email_user = user_subscriber) ORDER BY dom",$dbh->quote($name)) {
+#      unless ($statement = sprintf "SELECT email_user as email, gecos_user 
as gecos, reception_subscriber as reception, visibility_subscriber as 
visibility, cookie_delay_user as cookie_delay, lang_user as lang, 
UNIX_TIMESTAMP(date_subscriber) as date, substring_index(email_user,'.',-1) 
AS top, substring_index(replace(email_user,'\@','.'),'.',-2) AS dom FROM 
user,subscriber WHERE (list_subscriber = %s AND email_user = user_subscriber) 
ORDER BY top, dom",$dbh->quote($name)) {
 
            do_log('debug','Unable to define SQL statement : %s', 
$dbh->errstr);
            return undef;
@@ -960,6 +966,10 @@
        
        my $user = $sth->fetchrow_hashref;
 
+       my $formatted_date = $user->{'date'};
+       $formatted_date =~ /^(\d+)-(\d+)-(\d+) (\d+):(\d+):(\d+)/;
+       $user->{'date'} = POSIX::mktime ($6, $5, $4, $3, $2, $1);
+
        $self->{'total'} = 1 if $user;
 
        return $user;
@@ -1114,7 +1124,8 @@
               next unless ($map_field{$field} and $map_table{$field});
               if ($map_table{$field} eq $table) {
                   if ($field eq 'date') {
-                      $value = "FROM_UNIXTIME($value)";
+#                     $value = "FROM_UNIXTIME($value)";
+                       $value = $dbh->quote(POSIX::strftime ("%Y-%m-%d 
%H:%M:%S", localtime ($value)));
                   }else {
                       $value = $dbh->quote($value);
                   }
@@ -1290,7 +1301,8 @@
        }
 
        ## Update Subscriber Table
-       unless ( $statement = sprintf "REPLACE INTO subscriber 
(user_subscriber, list_subscriber, date_subscriber, reception_subscriber, 
visibility_subscriber) VALUES (%s, %s, FROM_UNIXTIME(%s), %s, 
%s)",$dbh->quote($who), $dbh->quote($name), $dbh->quote($values->{'date'}), 
$dbh->quote($values->{'reception'}), $dbh->quote($values->{'visibility'}) ) {
+#       unless ( $statement = sprintf "REPLACE INTO subscriber 
(user_subscriber, list_subscriber, date_subscriber, reception_subscriber, 
visibility_subscriber) VALUES (%s, %s, FROM_UNIXTIME(%s), %s, 
%s)",$dbh->quote($who), $dbh->quote($name), $dbh->quote($values->{'date'}), 
$dbh->quote($values->{'reception'}), $dbh->quote($values->{'visibility'}) ) {
+       unless ( $statement = sprintf "DELETE FROM subscriber WHERE 
(user_subscriber=%s) AND (list_subscriber=%s); INSERT INTO subscriber 
(user_subscriber, list_subscriber, date_subscriber, reception_subscriber, 
visibility_subscriber) VALUES (%s, %s, %s, %s, %s)", $dbh->quote($who), 
$dbh->quote($name), $dbh->quote($who), $dbh->quote($name), 
$dbh->quote(POSIX::strftime ("%Y-%m-%d %H:%M:%S", 
localtime($values->{'date'}))), $dbh->quote($values->{'reception'}), 
$dbh->quote($values->{'visibility'}) ) {
           do_log('debug','Unable to define SQL statement : %s', 
$dbh->errstr);
           return undef;
        }
--- /nnx/sympa/inst/sympa-2.2.7/src/mail.pl     Wed Jun  9 15:08:08 1999
+++ bin/mail.pl Fri Jul 23 17:01:03 1999
@@ -57,9 +57,12 @@
 
 ## send welcome,  bye, expire removed or reminder message 
 sub mailfile {
-   my($action, $listname, $default_subject, $default_body, @rcpt) = @_;
+   my($action, $self, $dbh, $default_subject, $default_body, @rcpt) = @_;
 
-   my ($is_mime,$filename,$return_path);
+   my ($is_mime,$filename,$return_path, $listname, $sql, $req, $msg_body);
+
+   $listname = $self->{'name'};
+   $msg_body = '';
 
    ## Return-path may be owner or unique
    if ($Conf{'return_path'} eq 'unique') {
@@ -70,6 +73,25 @@
    
    my($fh) = &smtp::smtpto($return_path, \@rcpt);
 
+   ## If list uses database, get the messgae from db
+   if ($self->{'admin'}->{'user_data_source'} eq 'database') {
+      unless ($sql = sprintf "SELECT content FROM file WHERE (list=%s) AND 
(action=%s);", $dbh->quote($listname), $dbh->quote($action)) {
+         do_log('debug','Unable to define SQL statement : %s', $dbh->errstr);
+        last;
+      }
+      unless ($req = $dbh->prepare ($sql)) {
+         do_log('debug','Unable to prepare SQL statement : %s', 
$dbh->errstr);
+        last;
+      }
+      unless ($req->execute) {
+         do_log('debug','Unable to execute SQL statement : %s', 
$dbh->errstr);
+        last;
+      }
+      $row = $req->fetchrow_hashref;
+      $msg_body = $row->{'content'};
+   }
+
+   ## mime message or not ?
    if (open(IN, "$listname/$action.mime")) {
        $filename="$listname/$action.mime";
        $is_mime=1;
@@ -82,29 +104,42 @@
        $filename="$action";
    }
 
+   ## Send mail headers
    printf $fh "To: %s\n", join(",\n   ", @rcpt);
 
    unless ($is_mime){
-       print $fh "From: $listname-request\@$Conf{'host'}\n"; 
-       print $fh "Subject: $default_subject\n";
-       print $fh $_ if ($_ = Msg(12, 1, undef));
-       print $fh $_ if ($_ = Msg(12, 2, undef));
-       print $fh $_ if ($_ = Msg(12, 3, undef));
-       print $fh "\n";
-   }
-
-   if ($filename) {
-       while (<IN>){
-          s/\[listname\]/$listname/g;
-          s/\[subscriber_email\]/$rcpt[0]/g;
-          s/\[sympa_email\]/$Conf{'sympa'}/g;
-          s/\[sympa_host\]/$Conf{'host'}/g;
-          print $fh $_ ;
-       }
-       close IN;
-   }else{
-       print $fh $default_body;
+      print $fh "From: $listname-request\@$Conf{'host'}\n"; 
+      print $fh "Subject: $default_subject\n";
+      print $fh $_ if ($_ = Msg(12, 1, undef));
+      print $fh $_ if ($_ = Msg(12, 2, undef));
+      print $fh $_ if ($_ = Msg(12, 3, undef));
+      print $fh "\n";
+   }
+
+   ## If nothing from the db, fallback on getting from file
+   if (!length ($msg_body)) {
+
+      if ($filename) {
+          while (<IN>){
+             $msg_body .= $_;
+          }
+          close IN;
+      }else{
+          $msg_body = $default_body;
+      }
    }
+   
+   ## Substitute message strings
+   unless ($is_mime) {
+      $msg_body =~ s/\[listname\]/$listname/g;
+      $msg_body =~ s/\[subscriber_email\]/$rcpt[0]/g;
+      $msg_body =~ s/\[sympa_email\]/$Conf{'sympa'}/g;
+      $msg_body =~ s/\[sympa_host\]/$Conf{'host'}/g;
+   }
+   
+   print $fh $msg_body;
+
+#   print $fh $sql;
        
    close ($fh);
    return 1
--- /nnx/sympa/inst/sympa-2.2.7/src/List.pm     Mon Jun 21 17:57:13 1999
+++ bin/List.pm Tue Aug 17 15:52:23 1999
@@ -817,7 +821,8 @@
        $alt_text = sprintf(Msg(6, 31, "You have been removed from list 
%s.\nThanks for being with us.\n"), $name);
    }
 
-    mail::mailfile("$Conf{$action}", $name, $subject, $alt_text, $who);
+#    mail::mailfile("$Conf{$action}", $name, $subject, $alt_text, $who);
+    mail::mailfile("$Conf{$action}", $self, $dbh, $subject, $alt_text, $who);
 }
 
 ## Delete the indicate users from the list.