Re: [xwiki-users] Forum system that integrates with XWiki?

17 Jul 2006

      My procmail mailing list does not require any modification to XWiki.  It 
uses the existing capability of the mail plug-in to display messages 
from an IMAP store. When messages arrive, I chose to access the database 
directly using JDBC rather than invoke something like "wget 
http://www.mywiki.com/..." for performance and reliability. I have seen 
other people are interested in how to access the database from outside 
of XWiki, so maybe this would be a useful example.

The command line java program is invoked with this shell script:
 #!/bin/sh
 java -cp 
/usr/local/tomcat/common/lib/mysql-connector-java-3.1.7-bin.jar:. \
    XWikiMail $1 $2 $3 $4

The command-line java program:

//
// A command-line program to extract the list of email addresses for an 
XWiki Group.
// Trevor Cox
// Nov 9, 2005
// www.skahasoftware.com
//

import java.net.*;
import java.io.*;
import java.util.*;
import java.sql.*;

public class XWikiMail {

    public XWikiMail() {}

    public static void main(String[] params)
        throws java.lang.Exception
    {
        if(params.length != 2) {
            System.out.println(
                "Usage: xwikimail <database> <group>\n");
                throw new java.lang.Exception("Incorrect parameters");
        }

        String site=params[0];
        String group=params[1];

        String url   = "jdbc:mysql://localhost/" + site;
        String dbuser = "xwiki";
        String dbpwd =  "xwiki";

        String query =
            "select xws_value from xwikiobjects join xwikistrings on 
xwo_id=xws_id "
            + " where xwo_name='XWiki." + group + "' and "
            + " xwo_classname='XWiki.XWikiGroups';";

        Class.forName  ("com.mysql.jdbc.Driver");

        Connection con = DriverManager.getConnection(url, dbuser, dbpwd);

        Statement stmt = con.createStatement ();
        ResultSet rs = stmt.executeQuery (query);

        while ( rs.next() ) {
            String member = rs.getString(1);

            String query2 =
        "select xws_value from xwikiobjects join xwikistrings on xwo_id 
= xws_id"
        + " where xwo_name='" + member + "' and 
xwo_classname='XWiki.XWikiUsers' and"
        + " xws_name='email';";

            Statement stmt2 = con.createStatement ();
            ResultSet rs2 = stmt2.executeQuery (query2);
            while ( rs2.next() ) {
                System.out.print(rs2.getString(1)+" ");
            }
        }

        rs.close();
        stmt.close();
        con.close();
    }
} 

Here's a procmail rule that distributes mail sent to the mailing list
<group>@<wiki>.xwiki.com (I've simplified my original script for clarity).

:0
* ^(To|Cc):.*\/[a-z]+@[a-z]+\.xwiki\.com
{
        GROUP=`echo $MATCH | sed s/@.*//`
        SITE=`echo $MATCH | perl -pe "s/.*@([^\.]*).*/\1/;"`
        XWIKISENDMAIL=`xwikimail $SITE $GROUP`

        :0 c
        $SITE.$GROUP           # Store messages in folder like 
mywiki.mygroup

        :0
        ! $XWIKISENDMAIL
}

Trevor Cox
Skaha Software

jeremi joslin wrote:
...
Hi,
Nice, can you send us also a diff with your modification on the mail
plugin. Can we integrate them in Xwiki?
Jeremi
On 7/17/06, Trevor Cox <[email protected]> wrote:
...
I have integrated a mailing list with XWiki. The mailing list is built
with procmail, which invokes a command line Java program to extract the
email addresses from the XWiki database and forward each message. All
the emails are archived in a IMAP folder which is viewed by this
template code:
<h3 class="heading-1">Mail Archive</h3>
#set($user = "xxx")
#set($pass = "xxx")
#set($fldr = "${context.database}.listname")
#set($store = $xwiki.mail.getStore("imap"))
$store.connect("localhost", $user, $pass)
#set($fldr = $store.getFolder($fldr))
$fldr.open(1)
#set($msgnum = $parameterParser.getInt("msg",0))
#if($msgnum != 0)
<a href="?">Message Index</a>
\\
#set($mail = $fldr.getMessage($msgnum))
From:
#foreach($from in $mail.getHeader("from")) 
$xwiki.getFormEncoded($from) #end
\\
To:
#foreach($from in $mail.getHeader("to")) $xwiki.getFormEncoded($from) 
#end
\\
Subject: $xwiki.getFormEncoded($mail.subject) \\
Message:
#if($mail.isMimeType("text/plain") || $mail.isMimeType("text/html"))
  $mail.getContent()
 #else
  (Unable to display)
 #end
#else
The following mail was sent to the list
during the past year:
#set($count = $fldr.messageCount)
Showing $count of $count messages:
<table class="wiki-table" cellpadding="0" cellspacing="0" border="0">
<tr class="table-odd">
<th>Subject</th><th>From</th><th>Date</th>
</tr>
#foreach ($i in [$fldr.messageCount..1])
#set($mail = $fldr.getMessage($i))
<tr>
<td>
<a
href="?msg=$mail.getMessageNumber()">$xwiki.getFormEncoded($mail.subject)</a>
</td><td>
#set($froms = $mail.getHeader("from"))
#foreach($from in $froms) $xwiki.getFormEncoded($from) #end
</td><td>
#if($!mail.sentDate)
$formatter.formatShortDateTime($!mail.sentDate)
#end
</td>
</tr>
#end
</table>
#end
$store.close()
Adrian Hall wrote:
...
Would you care to share the code?
Thanks,
-Adrian
-- 
Trevor Cox
skahasoftware.com
Vancouver, Canada
-- 
You receive this message as a subscriber of the 
[email protected] mailing list.
To unsubscribe: mailto:[email protected]
For general help: mailto:[email protected]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
------------------------------------------------------------------------
--
You receive this message as a subscriber of the [email protected] mailing list.
To unsubscribe: mailto:[email protected]
For general help: mailto:[email protected]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
-- 
Trevor Cox
skahasoftware.com
Vancouver, Canada