[sork] sqlprocmail forwards driver (2)
Carlos Alberto Barcenilla
barce at dearriba.com
Mon Apr 7 23:16:39 PDT 2003
continued from previuos message
*_NEW File_: forwards/lib/Driver/sqlprocmail.php*
this driver updates an sql table, it has been developed and tested with
PostgreSQL, but it should work with other database systems with minimal,
if any, changes.
--- cvs/forwards/lib/Driver/sqlprocmail.php 1969-12-31
21:00:00.000000000 -0300
+++ forwards/lib/Driver/sqlprocmail.php 2003-04-05 10:44:44.000000000 -0300
@@ -0,0 +1,365 @@
+<?php
+/**
+ * $Horde: forwards/lib/Driver/sql.php,v 1.2.2.3 2003/01/20 05:17:57
ericr Exp $
+ *
+ * Copyright 2001-2003 Ilya Krel and Mike Cochrane
+ *
+ * See the enclosed file LICENSE for license information (BSD). If you
+ * did not receive this file, see http://www.horde.org/bsdl.php.
+ *
+ * Forwards_Driver_sqlprocmail:: implements the Forwards_Driver API for
SQL servers for use with procmail.
+ *
+ * @author Carlos Barcenilla <barce at frlp.utn.edu.ar>
+ * @author Ilya Krel <mail at krel.org.org>
+ * @author Mike Cochrane <mike at graftonhall.co.nz>
+ * @version $Revision: 1.2.2.3 $
+ * @since Forwards 2.1
+ * @package forwards
+ */
+
+require_once HORDE_BASE . '/lib/Auth.php';
+
+class Forwards_Driver_sqlprocmail extends Forwards_Driver {
+
+ /** file pointer to the sql connection. */
+ var $db;
+
+ /** error string returned to user if an eror occurs. */
+ var $err_str;
+
+ /** boolean which contains state of sql connection */
+ var $connected=false;
+
+ var $params;
+
+ /** boolean this driver does not ask for a password */
+ var $noPassword = true;
+
+ /**
+ * Constructs a new sql Forwards_Driver object.
+ *
+ * @param array $params A hash containing connection
parameters.
+ */
+ function Forwards_Driver_sqlprocmail($params = array())
+ {
+
+ $this->params = $params;
+
+ }
+
+
+ /**
+ * Do an sql connect and login as user with privilege to change
passwd.
+ *
+ * @return boolean True or False based on success of connect
+ *
+ */
+
+ function _connect()
+ {
+
+ if (!$this->connected) {
+ if (!is_array($this->params)) {
+ Horde::fatal(new PEAR_Error(
+ _("No configuration information
specified for SQL authentication.")),
+ __FILE__, __LINE__);
+ }
+ if (!isset($this->params['phptype'])) {
+ Horde::fatal(new PEAR_Error(
+ _("Required 'phptype' not specified in
authentication configuration.")),
+ __FILE__, __LINE__);
+ }
+ if (!isset($this->params['hostspec'])) {
+ Horde::fatal(new PEAR_Error(
+ _("Required 'hostspec' not specified in
authentication configuration.")),
+ __FILE__, __LINE__);
+ }
+ if (!isset($this->params['username'])) {
+ Horde::fatal(new PEAR_Error(
+ _("Required 'username' not specified in
authentication configuration.")),
+ __FILE__, __LINE__);
+ }
+ if (!isset($this->params['password'])) {
+ Horde::fatal(new PEAR_Error(
+ _("Required 'password' not specified in
authentication configuration.")),
+ __FILE__, __LINE__);
+ }
+ if (!isset($this->params['database'])) {
+ Horde::fatal(new PEAR_Error(
+ _("Required 'database' not specified in
authentication configuration.")),
+ __FILE__, __LINE__);
+ }
+ if (!isset($this->params['table'])) {
+ Horde::fatal(new PEAR_Error(
+ _("Required 'table' not specified in
authentication configuration.")),
+ __FILE__, __LINE__);
+ }
+
+ /* Connect to the SQL server using the supplied
parameters. */
+ include_once 'DB.php';
+ $this->db = &DB::connect($this->params, true);
+ if (DB::isError($this->db)) {
+ Horde::fatal(new PEAR_Error(_("Unable to
connect to SQL server.")), __FILE__, __LINE__);
+ }
+
+ /* Enable the "portability" option. */
+ $this->db->setOption('optimize', 'portability');
+ $this->connected = true;
+ }
+
+ return true;
+ }
+
+ /**
+ * Disconnect from the SQL server and clean up the connection.
+ *
+ * @return boolean true on success, false on failure.
+ */
+ function _disconnect()
+ {
+
+ if ($this->connected) {
+ $this->connected = false;
+ return $this->db->disconnect();
+ }
+
+ return true;
+
+ }
+
+
+ /**
+ * Begins forwarding of mail for a user.
+ *
+ * @param string $user The username to enable
forwarding for.
+ * @param string $realm The realm of the user.
+ * @param string $pass The password of the user.
+ *
+ * @param string $target The email address that mail
should be forwarded to.
+ *
+ * @param optional boolean $keeplocal A flag that if true causes
a copy of
+
*
forwarded email to be kept in the
+
*
local mailbox.
+ *
+ * @return boolean Returns true on success, false on error.
+ */
+ function enableForwarding($user, $realm, $password, $target,
$keeplocal)
+ {
+ // set forwarding flag
+ /* _connect() will die with Horde::fatal() upon failure. */
+ $this->_connect();
+ $local_target=$user . '@' . $realm;
+
+
+ /* Build the SQL query. Checks if there is a row for
this user*/
+ $query = 'SELECT * FROM ' . $this->params['table'];
+ $query .= ' WHERE ' . $this->params['username_col'] . '
= ' . $this->db->quote($local_target);
+
+ /* Execute the query. */
+ $result = $this->db->query($query);
+ if (!DB::isError($result)) {
+ $update_query = ($result->numRows() == '1');
+ } else {
+ $this->_disconnect();
+ return false;
+ }
+
+ if ($update_query) {
+ /* Build the SQL query that updates an existing row. */
+ $query = 'UPDATE ' . $this->params['table'];
+ $query .= ' set ' . $this->params['forward_col'] . ' = '
. $this->db->quote("y");
+ $query .= ' , ' . $this->params['forwardto_col'] . ' =
' . $this->db->quote($target);
+ $query .= ' , ' . $this->params['keeplocal_col'] . ' =
' . $this->db->quote($keeplocal == "off" ? 'n':'y');
+ $query .= ' , ' . $this->params['newentry_col'] . ' = '
. $this->db->quote("y");
+ $query .= ' WHERE ' . $this->params['username_col'] . '
= ' . $this->db->quote($local_target);
+ } else {
+ $query = 'INSERT INTO ' . $this->params['table'];
+ $query .= '
('.$this->params['username_col'].','.$this->params['forwardto_col'];
+ $query .=
','.$this->params['keeplocal_col'].','.$this->params['forward_col'];
+ $query .= ','.$this->params['newentry_col'].') ';
+ $query .= ' VALUES (' . $this->db->quote($local_target);
+ $query .= ',' . $this->db->quote($target);
+ $query .= ',' . $this->db->quote($keeplocal == "off" ?
'n':'y');
+ $query .= ',' . $this->db->quote('y');
+ $query .= ',' . $this->db->quote('y').')';
+ }
+
+ /* Execute the query. */
+ $result = $this->db->query($query);
+ if (!DB::isError($result)) {
+ if ($result === DB_OK) {
+ $this->_disconnect();
+ return true;
+ } else {
+ $this->_disconnect();
+ return false;
+ }
+ } else {
+ $this->_disconnect();
+ return false;
+ }
+ $this->_disconnect();
+ return false;
+
+ }
+
+ /**
+ * Stops forwarding of mail for a user.
+ *
+ * @param string $user The username of the user.
+ * @param string $realm The realm of the user.
+ * @param string $pass The password of the user.
+ *
+ * @return boolean Returns true on success, false on error.
+ */
+ function disableForwarding($user, $realm, $password)
+ {
+
+ // set forwarding flag
+ /* _connect() will die with Horde::fatal() upon failure. */
+ $this->_connect();
+ $local_target=$user . '@' . $realm;
+
+ /* Build the SQL query that updates the row */
+ $query = 'UPDATE ' . $this->params['table'];
+ $query .= ' set ' . $this->params['forward_col'] . ' = '
. $this->db->quote("n");
+ $query .= ' , ' . $this->params['newentry_col'] . ' = '
. $this->db->quote("y");
+ $query .= ' , ' . $this->params['forwardto_col'] . ' =
' . $this->db->quote("");
+ $query .= ' WHERE ' . $this->params['username_col'] . '
= ' . $this->db->quote($local_target);
+
+ /* Execute the query. */
+ $result = $this->db->query($query);
+
+ if (!DB::isError($result)) {
+ if ($result === DB_OK) {
+ $this->_disconnect();
+ return true;
+ } else {
+ $this->_disconnect();
+ return false;
+ }
+ } else {
+ $this->_disconnect();
+ return false;
+ }
+ $this->_disconnect();
+ return false;
+
+ }
+
+ /**
+ * Retrieves status of mail redirection for a user
+ *
+ * @param string $user The username of the user to
check.
+ *
+ * @param string $realm The realm of the user to check.
+ *
+ * @return boolean Returns true if forwarding is enabled
for the user or false if
+ * forwarding is currently disabled.
+ */
+ function isEnabledForwarding($user, $realm, $password)
+ {
+
+ // get current details
+ $current_details = $this->_getUserDetails($user, $realm,
$password);
+ if ($current_details === false) {
+ //$this->err_str = _("Not able to retrieve current
details.");
+ return false;
+ }
+
+ // check forwarding flag
+ if ( $current_details[$this->params['forward_col']] ===
'y' ||
+ $current_details[$this->params['forward_col']]
=== 'Y') {
+ return true;
+ } else { return false; }
+
+ }
+
+ /**
+ * Returns true if a local copy of forwarded messages is being kept
+ *
+ * @param string $user The username of the user to
check.
+ *
+ * @param string $realm The realm of the user to check.
+ *
+ * @return boolean Returns true if retain local copy is
enabled else false.
+ *
+ */
+ function isKeepLocal($user, $realm, $password)
+ {
+
+ // get current details
+ $current_details = $this->_getUserDetails($user, $realm,
$password);
+ if ($current_details === false) {
+ //$this->err_str = _("Not able to retrieve current
details.");
+ return false;
+ }
+
+ if ( $current_details[$this->params['keeplocal_col']]
=== 'y' ||
+ $current_details[$this->params['keeplocal_col']]
=== 'Y') {
+ return true;
+ } else { return false; }
+
+ }
+
+ /**
+ * Retrieves current target of mail redirection
+ *
+ * @param string $user The username of the user.
+ * @param string $realm The realm of the user.
+ *
+ * @return string A string of current forwarding mail address
or false.
+ */
+ function currentTarget($user, $realm, $password)
+ {
+
+ $searchString = ",$user@$realm";
+ $current_details = $this->_getUserDetails($user, $realm,
$password);
+
+ // check current forwarding mail address
+ $targets = $current_details[$this->params['forwardto_col']];
+ return
str_replace($searchString,"",$current_details[$this->params['forwardto_col']]);
+
+ }
+
+ /**
+ * Retreive relevant line from sql server
+ *
+ * @param $user The username for which to
retrieve detals..
+ * @param $realm The realm (domain) for the user.
+ * @param $password The password for user.
+ *
+ * @return Mixed Mysql result resource or
(boolean) False
+ */
+ function _getUserDetails($user, $realm, $password)
+ {
+
+ /* _connect() will die with Horde::fatal() upon failure. */
+ $this->_connect();
+ $local_target=$user . '@' . $realm;
+
+ /* Build the SQL query. */
+ $query = 'SELECT * FROM ' . $this->params['table'];
+ $query .= ' WHERE ' . $this->params['username_col'] . '
= ' . $this->db->quote($local_target);
+ /* Execute the query. */
+ $result = $this->db->query($query);
+
+
+ if (!DB::isError($result)) {
+ $row = $result->fetchRow(DB_FETCHMODE_ASSOC);
+
+ if (is_array($row)) {
+ $this->_disconnect();
+ return $row;
+ } else {
+ $this->_disconnect();
+ $result->free();
+ return false;
+ }
+ }
+ $this->_disconnect();
+ return false;
+
+ }
+
+}
-------------- next part --------------
#!/usr/bin/perl
use POSIX;
use Pg;
use Sys::Syslog qw(:DEFAULT setlogsock);
# CONFIGURACION
*REALM = \'frlp.utn.edu.ar';
# PROGRAMA PRINCIPAL #
setlogsock('unix');
openlog("forwards","cons,pid","user");
# conecta a la base de datos
$conn = Pg::connectdb("dbname=horde user=horde password=mypassword host=database.frlp.utn.edu.ar port=5432");
if ($conn->status != PGRES_CONNECTION_OK) {
print "Conexion Fallida\n";
syslog("err","Error: No fue posible abrir la base de datos");
exit 1;
}
$query_quotas = "BEGIN WORK";
$result = $conn->exec($query_quotas);
if ($result->resultStatus!=PGRES_COMMAND_OK) {
syslog("err","Error: Fallo en BEGIN WORK");
exit 1;
}
$query_quotas = "SELECT * FROM horde_forwards WHERE newentry='y' ".
"AND username LIKE '%@".$REALM."'";
$result = $conn->exec($query_quotas);
if ($result->resultStatus!=PGRES_TUPLES_OK) {
syslog("err","Error: Fallo en SELECT");
exit 1;
}
$cantidad_usuarios = $result->ntuples;
for ($i=0 ; $i<$cantidad_usuarios; $i++) {
$username = $result->getvalue($i, $result->fnumber('username'));
$forwardto = $result->getvalue($i, $result->fnumber('forwardto'));
$keeplocal = $result->getvalue($i, $result->fnumber('keeplocal')) eq 'y';
$forward = $result->getvalue($i, $result->fnumber('forward')) eq 'y';
$username =~ /(.+)@(.+)/ ;
$user=$1;
$domain=$2;
($name,$passwd,$uid,$gid, $quota,
$comment,$gcos,$dir,$shell,$expire) = getpwnam($user);
open PROCMAILRC, '>'.$dir.'/.procmailrc';
if ($forward) {
print PROCMAILRC ":0 ".($keeplocal ? 'c':'')."\n";
print PROCMAILRC "*\n";
print PROCMAILRC "!$forwardto\n";
}
close PROCMAILRC;
}
$query_quotas = "UPDATE horde_forwards SET newentry='n' ".
"WHERE username LIKE '%@".$REALM."'";
$result = $conn->exec($query_quotas);
if ($result->resultStatus!=PGRES_COMMAND_OK) {
syslog("err","Error: Fallo en UPDATE");
exit 1;
}
$query_quotas = "COMMIT";
$result = $conn->exec($query_quotas);
if ($result->resultStatus!=PGRES_COMMAND_OK) {
syslog("err","Error: Fallo en COMMIT");
exit 1;
}
closelog(); # cierra la conexion con syslog
More information about the sork
mailing list